1 PACKAGE BODY AHL_FMP_PVT AS
2 /* $Header: AHLVFMPB.pls 120.19.12020000.4 2013/05/10 20:50:45 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 /* perf fix for bug# 9620276
10 SELECT serial_number, item_number, inventory_item_id,
11 location_description, status, owner_name, condition,
12 mfg_date, 'm' manufacturer_id, 'c' country_code
13 FROM ahl_unit_installed_details_v
14 WHERE csi_item_instance_id = c_item_instance_id;
15 */
16
17 SELECT ii.serial_number,
18 (select kfv.concatenated_segments from mtl_system_items_kfv kfv
19 where kfv.inventory_item_id = ii.inventory_item_id
20 AND kfv.organization_id = ii.inv_master_organization_id) item_number,
21 ii.inventory_item_id,
22 ahl_util_uc_pkg.getcsi_locationDesc(ii.location_id, ii.location_type_code,
23 ii.inv_organization_id, ii.inv_subinventory_name,
24 ii.inv_locator_id, ii.wip_job_id) Location_description,
25 (select f.meaning from csi_lookups f where ii.instance_usage_code = f.lookup_code
26 AND f.lookup_type = 'CSI_INSTANCE_USAGE_CODE') Status,
27 (select p.party_name from csi_inst_party_details_v p
28 where p.instance_id = ii.instance_id and p.relationship_type_code = 'OWNER') owner_name,
29 (select mat.description from mtl_material_statuses mat where ii.INSTANCE_CONDITION_ID = mat.status_id) condition,
30 (select to_date(ciea.attribute_value, 'DD/MM/YYYY')
31 from csi_inst_extend_attrib_v ciea
32 where ciea.instance_id = ii.instance_id
33 AND ciea.attribute_code = 'AHL_MFG_DATE'
34 AND ciea.attribute_level = 'GLOBAL') mfg_date,
35 'm' manufacturer_id, 'c' country_code
36 FROM csi_item_instances ii
37 WHERE ii.instance_id = c_item_instance_id;
38
39 --Check whether the given item instance exists
40 CURSOR check_instance_exists(c_item_instance_id NUMBER) IS
41 SELECT instance_id
42 FROM csi_item_instances
43 WHERE instance_id = c_item_instance_id
44 AND SYSDATE between nvl(active_start_date,sysdate) and NVL(active_end_date,sysdate+1);
45
46
47 --Get Inventory Item ID for a given item instance
48 CURSOR get_inventory_item(c_item_instance_id NUMBER) IS
49 SELECT inventory_item_id
50 FROM csi_item_instances
51 WHERE instance_id = c_item_instance_id
52 AND SYSDATE between nvl(active_start_date,sysdate) and NVL(active_end_date,sysdate+1);
53
54 -- Declare Local Function CHECK_SN_INSIDE --
55
56 FUNCTION CHECK_SN_INSIDE(
57 p_sn IN VARCHAR2,
58 p_sn1 IN VARCHAR2,
59 p_sn2 IN VARCHAR2
60 ) RETURN BOOLEAN;
61
62 -- Declare Local Function CHECK_SN_OUTSIDE --
63 FUNCTION CHECK_SN_OUTSIDE(
64 p_sn IN VARCHAR2,
65 p_sn1 IN VARCHAR2,
66 p_sn2 IN VARCHAR2
67 ) RETURN BOOLEAN;
68
69 -- Declare Local Function CHECK_EFFECTIVITY_DETAILS --
70 FUNCTION CHECK_EFFECTIVITY_DETAILS(
71 p_item_instance_id IN NUMBER,
72 p_mr_effectivity_id IN NUMBER
73 ) RETURN BOOLEAN;
74
75 -- Declare Local Function CHECK_EFFECTIVITY_DETAILS --
76 FUNCTION CHECK_EFFECTIVITY_EXT_DETAILS(
77 p_item_instance_id IN NUMBER,
78 p_mr_effectivity_id IN NUMBER
79 ) RETURN BOOLEAN;
80
81 -- Declare local procedure GET_UCHEADER --
82 PROCEDURE get_ucHeader(
83 p_item_instance_id IN NUMBER,
84 x_ucHeaderID OUT NOCOPY NUMBER,
85 x_unitName OUT NOCOPY VARCHAR2);
86
87 FUNCTION get_topInstanceID(p_item_instance_id IN NUMBER) RETURN NUMBER;
88
89 -- added for performance fix 9434441
90 PROCEDURE Populate_Config_Components (p_item_instance_id IN NUMBER);
91
92 -- code moved from local procedure to global definition.
93 TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
94 TYPE vchar_tbl_type IS TABLE OF VARCHAR2(600) INDEX BY BINARY_INTEGER;
95 TYPE date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
96
97 -- Define Procedure GET_MR_AFFECTED_ITEMS --
98 PROCEDURE GET_MR_AFFECTED_ITEMS(
99 p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
101 p_commit IN VARCHAR2 := FND_API.G_FALSE,
102 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2,
106 p_mr_header_id IN NUMBER,
107 p_mr_effectivity_id IN NUMBER := NULL,
108 p_top_node_flag IN VARCHAR2 := 'N',
109 p_unique_inst_flag IN VARCHAR2 := 'N',
110 p_sort_flag IN VARCHAR2 := 'N',
111 -- JKJain, NR Analysis and Forecasting
112 p_consider_fleet_flag IN VARCHAR2 := 'Y',
113 p_flt_range_calc_date IN DATE := sysdate ,
114 x_mr_item_inst_tbl OUT NOCOPY MR_ITEM_INSTANCE_TBL_TYPE
115 ) IS
116 l_api_name CONSTANT VARCHAR2(30) := 'GET_MR_AFFECTED_ITEMS';
117 l_api_version CONSTANT NUMBER := 1.0;
118 l_msg_count NUMBER;
119 l_return_status VARCHAR2(1);
120 l_msg_data VARCHAR2(30);
121 l_mr_header_id NUMBER;
122 l_index NUMBER;
123 i NUMBER;
124 l_inst_attri get_inst_attri%ROWTYPE;
125 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
126 l_error_flag VARCHAR2(1) :='N';
127 --check whether the given mr exists
128 CURSOR check_mr_exists(c_mr_header_id number)
129 IS
130 SELECT mr_header_id
131 FROM ahl_mr_headers_app_v
132 WHERE mr_header_id = c_mr_header_id;
133 --check whether the given mr_effecitivity_id exists
134 CURSOR check_mr_effect(c_mr_effectivity_id number ,c_mr_header_id number)
135 IS
136 SELECT mr_header_id, mr_effectivity_id, inventory_item_id,relationship_id, pc_node_id
137 FROM ahl_mr_effectivities_app_v
138 WHERE mr_effectivity_id = NVL(c_mr_effectivity_id,mr_effectivity_id)
139 AND mr_header_id = c_mr_header_id;
140 l_mr_effect check_mr_effect%ROWTYPE;
141
142 --get all the MR effectivity definitions for a given MR(Actually this cursor returns
143 --the only specified mr_effecitivity_id if it is not null, otherwise it returns
144 --all the mr_effectivity_id's for the given MR).
145 CURSOR get_mr_effect(c_mr_header_id NUMBER, c_mr_effectivity_id NUMBER)
146 IS
147 SELECT mr_header_id, mr_effectivity_id, inventory_item_id,relationship_id, pc_node_id
148 FROM ahl_mr_effectivities_app_v
149 WHERE mr_header_id = c_mr_header_id
150 AND mr_effectivity_id = NVL(c_mr_effectivity_id, mr_effectivity_id);
151 --get distinct item instances from the global temporary table
152 CURSOR get_dist_inst
153 IS
154 SELECT DISTINCT item_instance_id, serial_number, item_number,
155 inventory_item_id, location, status, owner, condition,unit_name,uc_header_id -- JKJain, NR Analysis and Forecasting
156 FROM ahl_mr_instances_temp;
157 --get all item instances from the global temporary table
158 CURSOR get_all_inst
159 IS
160 SELECT mr_effectivity_id, item_instance_id, serial_number, item_number,
161 inventory_item_id, location, status, owner, condition,unit_name,uc_header_id -- JKJain, NR Analysis and Forecasting
162 FROM ahl_mr_instances_temp;
163 --get distinct item instances from the global temporary table and sort them.
164 CURSOR get_dist_sort_inst
165 IS
166 SELECT DISTINCT item_instance_id, serial_number, item_number,
167 inventory_item_id, location, status, owner, condition,unit_name,uc_header_id -- JKJain, NR Analysis and Forecasting
168 FROM ahl_mr_instances_temp
169 ORDER BY item_number, serial_number;
170 --get unit_name and uc_header_id for an item instance
171 /*
172 CURSOR get_uc_header(c_instance_id NUMBER) IS
173 SELECT unit_config_header_id, name
174 FROM ahl_unit_config_headers A
175 WHERE csi_item_instance_id = c_instance_id
176 OR EXISTS (SELECT 'X'
177 FROM csi_ii_relationships B
178 WHERE B.object_id = A.csi_item_instance_id
179 START WITH subject_id = c_instance_id
180 CONNECT BY subject_id = PRIOR object_id);
181 l_get_uc_header get_uc_header%ROWTYPE;
182 */
183 --only inventory item defined in MR effectivity definition, just top node
184 /*CURSOR get_top_inst1(c_inventory_item_id NUMBER)
185 IS
186 SELECT instance_id
187 FROM csi_item_instances A
188 WHERE inventory_item_id = c_inventory_item_id
189 AND SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
190 AND NOT EXISTS (SELECT 'X'
191 FROM csi_ii_relationships B
192 WHERE B.subject_id = A.instance_id
193 AND relationship_type_code = 'COMPONENT-OF'
194 AND SYSDATE between trunc(nvl(B.active_start_date,sysdate)) and trunc(NVL(b.active_end_date,sysdate+1))
195 );*/
196 --same as before, but include all nodes, not only top node
197
198 CURSOR get_inst1(c_inventory_item_id NUMBER)
199 IS
200 SELECT instance_id
201 FROM csi_item_instances
202 WHERE inventory_item_id = c_inventory_item_id
203 AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(nvl(active_end_date,sysdate+1));
204
205 --only position in MC defined in MR effectivity definition, just UC top node
206
207 /*CURSOR get_top_inst2(c_relationship_id NUMBER)
208 IS
209 SELECT a.csi_item_instance_id instance_id
210 FROM ahl_unit_config_headers a,ahl_applicable_instances b
211 WHERE a.csi_item_instance_id=b.csi_item_instance_id
212 and a.master_config_id=b.position_id
213 AND b.position_id=c_relationship_id
214 and SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
215 and not exists (SELECT 'X'
216 FROM csi_ii_relationships
217 WHERE subject_id=b.csi_item_instance_id
218 AND relationship_type_code='COMPONENT_OF');*/
219
220
221 --same as before, but include all nodes, not only top node
222 CURSOR get_inst2(c_relationship_id NUMBER)
223 IS
224 SELECT a.csi_item_instance_id instance_id
225 FROM ahl_unit_config_headers a,ahl_applicable_instances b
226 WHERE a.csi_item_instance_id=b.csi_item_instance_id
227 and b.position_id= c_relationship_id
228 AND SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
229 UNION
230 SELECT a.subject_id instance_id
231 FROM csi_ii_relationships a,ahl_applicable_instances b
232 WHERE a.subject_id=b.csi_item_instance_id
233 and b.position_id=c_relationship_id
234 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
235 AND a.relationship_type_code = 'COMPONENT-OF';
236
237
238 --position in MC and inventory item defined in MR effectivity definition, just
239 --top node
240 /*CURSOR get_top_inst3(c_relationship_id NUMBER, c_inventory_item_id NUMBER)
241 IS
242 SELECT a.csi_item_instance_id instance_id
243 FROM ahl_unit_config_headers A,ahl_applicable_instances api
244 --WHERE A.master_config_id=api.position_id
245 --AND A.csi_item_instance_id=api.csi_item_instance_id
246 WHERE A.csi_item_instance_id=api.csi_item_instance_id
247 AND api.position_id= C_RELATIONSHIP_ID
248 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
249 AND trunc(nvl(a.active_end_date,sysdate+1))
250 AND EXISTS (SELECT 'X'
251 FROM csi_item_instances B
252 WHERE B.instance_id = api.csi_item_instance_id
253 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
254 AND inventory_item_id = c_inventory_item_id);*/
255
256 --same as before, but include all nodes, not only top node
257 CURSOR get_inst3(c_relationship_id NUMBER, c_inventory_item_id NUMBER)
258 IS
259 SELECT a.csi_item_instance_id instance_id
260 FROM ahl_unit_config_headers A,ahl_applicable_instances api
261 WHERE A.csi_item_instance_id=api.csi_item_instance_id
262 AND api.position_id=c_relationship_id
263 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
264 and trunc(nvl(a.active_end_date,sysdate+1))
265 AND EXISTS (SELECT 'X'
266 FROM csi_item_instances B
267 WHERE B.instance_id = api.csi_item_instance_id
268 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate))
269 and trunc(nvl(b.active_end_date,sysdate+1))
270 AND inventory_item_id = c_inventory_item_id)
271 UNION
272 SELECT subject_id instance_id
273 FROM csi_ii_relationships A,ahl_applicable_instances api
274 WHERE api.position_id=c_relationship_id
275 and api.csi_item_instance_id=a.subject_id
276 AND relationship_type_code = 'COMPONENT-OF'
277 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
278 and trunc(nvl(a.active_end_date,sysdate+1))
279 AND EXISTS (SELECT 'X'
280 FROM csi_item_instances B
281 WHERE B.instance_id = api.csi_item_instance_id
282 AND sysdate between trunc(nvl(b.active_start_date,sysdate))
283 and trunc(nvl(b.active_end_date,sysdate+1))
284 AND inventory_item_id = c_inventory_item_id);
285 --inventory item and PC node defined in MR effectivity definition, just top node
286 /*CURSOR get_top_inst4(c_inventory_item_id NUMBER, c_pc_node_id NUMBER)
287 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 -- same as before, but include all nodes, not only top node
326
327 /*CURSOR get_inst4(c_inventory_item_id NUMBER, c_pc_node_id NUMBER) IS
328 SELECT a.csi_item_instance_id instance_id
329 FROM ahl_unit_config_headers A
330 WHERE EXISTS (SELECT 'X'
331 FROM csi_item_instances B
332 WHERE B.instance_id = A.csi_item_instance_id
333 AND sysdate between trunc(nvl(b.active_start_date,sysdate))
334 and trunc(nvl(b.active_end_date,sysdate+1))
335 AND B.inventory_item_id = c_inventory_item_id)
336 AND EXISTS (SELECT 'X'
337 FROM ahl_pc_associations C
338 WHERE C.unit_item_id = A.unit_config_header_id
339 AND C.association_type_flag = 'U'
340 AND EXISTS (SELECT 'X'
341 FROM ahl_pc_nodes_b D
342 WHERE D.pc_node_id = C.pc_node_id
343 START WITH D.pc_node_id = c_pc_node_id
344 CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
345 UNION
346 SELECT instance_id
347 FROM csi_item_instances A
348 WHERE A.inventory_item_id = c_inventory_item_id
349 AND sysdate between trunc(nvl(a.active_start_date,sysdate))
350 and trunc(nvl(a.active_end_date,sysdate+1))
351 AND NOT EXISTS (SELECT 'X'
352 FROM csi_ii_relationships B
353 WHERE B.subject_id = A.instance_id
354 AND sysdate between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
355 AND B.relationship_type_code = 'COMPONENT-OF')
356 AND EXISTS (SELECT 'X'
357 FROM ahl_pc_associations C
358 WHERE C.unit_item_id = A.inventory_item_id
359 AND C.association_type_flag = 'I'
360 AND EXISTS (SELECT 'X'
361 FROM ahl_pc_nodes_b D
362 WHERE D.pc_node_id = C.pc_node_id
363 START WITH D.pc_node_id = c_pc_node_id
364 CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
365 UNION -- aobe query added to fix bug number 5448015
366 SELECT a.csi_item_instance_id instance_id
367 FROM ahl_unit_config_headers A,ahl_applicable_instances api
368 WHERE SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
369 AND a.csi_item_instance_id=api.csi_item_instance_id
370 AND EXISTS (SELECT 'X'
371 FROM csi_item_instances B
372 WHERE B.instance_id = Api.csi_item_instance_id
373 AND sysdate between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
374 AND B.inventory_item_id = c_inventory_item_id)
375 AND EXISTS (SELECT 'X'
376 FROM ahl_pc_associations C
377 WHERE C.unit_item_id = A.unit_config_header_id
378 AND C.association_type_flag = 'U'
379 AND EXISTS (SELECT 'X'
380 FROM ahl_pc_nodes_b D
381 WHERE D.pc_node_id = C.pc_node_id
382 START WITH D.pc_node_id = c_pc_node_id
383 CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
384 UNION
385 SELECT a.subject_id instance_id
386 FROM csi_ii_relationships A
387 WHERE relationship_type_code = 'COMPONENT-OF'
388 AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
389 AND A.SUBJECT_id IN (SELECT csi_item_instance_id from ahl_applicable_instances
390 where csi_item_instance_id=a.subject_id )
391 AND EXISTS (SELECT 'X'
392 FROM csi_item_instances B
393 WHERE B.instance_id = A.subject_id
394 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
395 AND B.inventory_item_id = c_inventory_item_id)
396 START WITH object_id IN (SELECT csi_item_instance_id
397 FROM ahl_unit_config_headers C
398 WHERE EXISTS (SELECT 'X'
399 FROM ahl_pc_associations D
400 WHERE D.unit_item_id = C.unit_config_header_id
401 AND D.association_type_flag = 'U'
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 UNION
412 SELECT a.instance_id
413 FROM csi_item_instances A
414 WHERE A.inventory_item_id = c_inventory_item_id
415 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
416 AND EXISTS (SELECT 'X'
417 FROM ahl_pc_associations B
418 WHERE B.unit_item_id = A.inventory_item_id
419 AND B.association_type_flag = 'I'
420 AND EXISTS (SELECT 'X'
421 FROM ahl_pc_nodes_b C
422 WHERE C.pc_node_id = B.pc_node_id
423 START WITH C.pc_node_id = c_pc_node_id
424 CONNECT BY C.parent_node_id= PRIOR C.pc_node_id))
425 UNION
426 SELECT A.subject_id instance_id
427 FROM csi_ii_relationships A
428 WHERE relationship_type_code = 'COMPONENT-OF'
429 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
430 AND EXISTS (SELECT 'X'
431 FROM csi_item_instances B
432 WHERE B.instance_id = A.subject_id
433 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
434 AND B.inventory_item_id = c_inventory_item_id
435 )
436 START WITH object_id IN (SELECT C.instance_id
437 FROM csi_item_instances C
438 WHERE EXISTS (SELECT 'X'
439 FROM ahl_pc_associations D
440 WHERE D.unit_item_id = C.inventory_item_id
441 AND D.association_type_flag = 'I'
442 AND EXISTS (SELECT 'X'
443 FROM ahl_pc_nodes_b E
444 WHERE E.pc_node_id = D.pc_node_id
445 START WITH E.pc_node_id = c_pc_node_id
446 CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)))
447 CONNECT BY object_id = PRIOR subject_id
448 -- sunil- fix for bug7411016
449 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
450 AND a.relationship_type_code = 'COMPONENT-OF';*/
451 --position in MC and PC node defined in MR effectivity definition, just top node
452 /*CURSOR get_top_inst5(c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
453 SELECT a.csi_item_instance_id instance_id
454 FROM ahl_unit_config_headers A,ahl_applicable_instances api
455 WHERE api.position_id = c_relationship_id
456 and a.csi_item_instance_id=api.csi_item_instance_id
457 --and A.master_config_id=api.position_id
458 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
459 AND EXISTS (SELECT 'X'
460 FROM ahl_pc_associations B
461 WHERE B.unit_item_id = A.unit_config_header_id
462 AND B.association_type_flag = 'U'
463 AND EXISTS (SELECT 'X'
464 FROM ahl_pc_nodes_b C
465 WHERE C.pc_node_id = B.pc_node_id
466 START WITH C.pc_node_id = c_pc_node_id
467 CONNECT BY C.parent_node_id = PRIOR C.pc_node_id))
468 UNION
469 SELECT a.csi_item_instance_id instance_id
470 FROM ahl_unit_config_headers A,ahl_applicable_instances api
471 WHERE api.position_id = c_relationship_id
472 --and A.master_config_id=api.position_id
473 and api.csi_item_instance_id=a.csi_item_instance_id
474 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
475 AND EXISTS (SELECT 'X'
476 FROM csi_item_instances B
477 WHERE B.instance_id = A.csi_item_instance_id
478 AND EXISTS (SELECT 'X'
479 FROM ahl_pc_associations C
480 WHERE C.unit_item_id = B.inventory_item_id
481
482 AND C.association_type_flag = 'I'
483 AND EXISTS (SELECT 'X'
484 FROM ahl_pc_nodes_b D
485 WHERE D.pc_node_id = C.pc_node_id
486 START WITH D.pc_node_id = c_pc_node_id
487 CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)
488 )
489 );*/
490 --same as before, but include all node, not only top node
491 /*CURSOR get_inst5(c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
492 SELECT a.csi_item_instance_id instance_id
493 FROM ahl_unit_config_headers A,ahl_applicable_instances api
494 WHERE api.csi_item_instance_id=a.csi_item_instance_id
495 and api.position_id=c_relationship_id
496 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
497 AND EXISTS (SELECT 'X'
498 FROM ahl_pc_associations B
499 WHERE B.unit_item_id = A.unit_config_header_id
500 AND B.association_type_flag = 'U'
501 AND EXISTS (SELECT 'X'
502 FROM ahl_pc_nodes_b C
503 WHERE C.pc_node_id = B.pc_node_id
504 START WITH C.pc_node_id = c_pc_node_id
505 CONNECT BY C.parent_node_id= PRIOR C.pc_node_id))
506 UNION
507 SELECT a.subject_id instance_id
508 FROM csi_ii_relationships a
509 WHERE relationship_type_code = 'COMPONENT-OF'
510 AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
511 AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
512 START WITH object_id IN (SELECT csi_item_instance_id
513 FROM ahl_unit_config_headers Ax
514 WHERE ax.csi_item_instance_id=a.subject_id AND
515 EXISTS (SELECT 'X'
516 FROM ahl_pc_associations B
517 WHERE B.unit_item_id = Ax.unit_config_header_id
518 AND B.association_type_flag = 'U'
519 AND EXISTS (SELECT 'X'
520 FROM ahl_pc_nodes_b C
521 WHERE C.pc_node_id = B.pc_node_id
522 START WITH C.pc_node_id = c_pc_node_id
523 CONNECT BY C.parent_node_id= PRIOR C.pc_node_id)
524 )
525 )
526 CONNECT BY object_id = PRIOR subject_id
527 -- sunil- fix for bug7411016
528 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
529 AND a.relationship_type_code = 'COMPONENT-OF'
530 UNION
531 SELECT a.csi_item_instance_id instance_id
532 FROM ahl_unit_config_headers A,ahl_applicable_instances api
533 WHERE api.position_id = c_relationship_id
534 and api.csi_item_instance_id= a.csi_item_instance_id
535 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
536 AND EXISTS (SELECT 'X'
537 FROM csi_item_instances B
538 WHERE B.instance_id = api.csi_item_instance_id
539 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
540 AND EXISTS (SELECT 'X'
541 FROM ahl_pc_associations C
542 WHERE C.unit_item_id = B.inventory_item_id
543 AND C.association_type_flag = 'I'
544 AND EXISTS (SELECT 'X'
545 FROM ahl_pc_nodes_b D
546 WHERE D.pc_node_id = C.pc_node_id
547 START WITH D.pc_node_id = c_pc_node_id
548 CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)))
549 UNION
550 SELECT a.subject_id instance_id
551 FROM csi_ii_relationships a
552 WHERE a.relationship_type_code = 'COMPONENT-OF'
553 AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
554 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
555 AND subject_id in (select csi_item_instance_id from ahl_applicable_instances)
556 START WITH object_id IN (SELECT ax.instance_id
557 FROM csi_item_instances Ax
558 WHERE SYSDATE between trunc(nvl(ax.active_start_date,sysdate)) and trunc(nvl(ax.active_end_date,sysdate+1))
559 AND instance_id=a.subject_id
560 AND EXISTS (SELECT 'X'
561 FROM ahl_pc_associations B
562 WHERE B.unit_item_id = Ax.inventory_item_id
563 AND B.association_type_flag = 'I'
564 AND EXISTS (SELECT 'X'
565 FROM ahl_pc_nodes_b C
566 WHERE C.pc_node_id = B.pc_node_id
567 START WITH C.pc_node_id = c_pc_node_id
568 CONNECT BY C.parent_node_id= PRIOR C.pc_node_id)
569 )
570 )
571 CONNECT BY object_id = PRIOR subject_id
572 -- sunil- fix for bug7411016
573 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
574 AND a.relationship_type_code = 'COMPONENT-OF';*/
575 --all inventory item, position in MC and PC node defined in MR effectivity
576 --definition, just top node
577 /*CURSOR get_top_inst6(c_inventory_item_id NUMBER, c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
578 SELECT a.csi_item_instance_id instance_id
579 FROM ahl_unit_config_headers A,ahl_applicable_instances api
580 WHERE api.position_id = c_relationship_id
581 AND api.csi_item_instance_id=A.csi_item_instance_id
582 --and A.master_config_id=api.position_id
583 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
584 AND EXISTS (SELECT 'X'
585 FROM csi_item_instances B
586 WHERE B.instance_id = Api.csi_item_instance_id
587 AND B.inventory_item_id = c_inventory_item_id)
588 AND EXISTS (SELECT 'X'
589 FROM ahl_pc_associations C
590 WHERE C.unit_item_id = A.unit_config_header_id
591 AND C.association_type_flag = 'U'
592 AND EXISTS (SELECT 'X'
593 FROM ahl_pc_nodes_b D
594 WHERE D.pc_node_id = C.pc_node_id
595 START WITH D.pc_node_id = c_pc_node_id
596 CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
597 UNION
598 SELECT a.csi_item_instance_id instance_id
599 FROM ahl_unit_config_headers A,ahl_applicable_instances api
600 WHERE Api.position_id = c_relationship_id
601 AND A.csi_item_instance_id=API.csi_item_instance_id
602 --and A.master_config_id=api.position_id
603 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
604 AND EXISTS (SELECT 'X'
605 FROM csi_item_instances B
606 WHERE B.instance_id = A.csi_item_instance_id
607 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
608 AND B.inventory_item_id = c_inventory_item_id
609 AND EXISTS (SELECT 'X'
610 FROM ahl_pc_associations C
611 WHERE C.unit_item_id = B.inventory_item_id
612 AND C.association_type_flag = 'I'
613 AND EXISTS (SELECT 'X'
614 FROM ahl_pc_nodes_b D
615 WHERE D.pc_node_id = C.pc_node_id
616 START WITH D.pc_node_id = c_pc_node_id
617 CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)));*/
618 --same as before, but include all nodes, not only top node
619 /*CURSOR get_inst6(c_inventory_item_id NUMBER, c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
620 SELECT a.csi_item_instance_id instance_id
621 FROM ahl_unit_config_headers A,ahl_applicable_instances api
622 WHERE api.position_id = c_relationship_id
623 AND api.csi_item_instance_id=A.csi_item_instance_id
624 -- AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
625 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
626 AND EXISTS (SELECT 'X'
627 FROM csi_item_instances B
628 WHERE B.instance_id = api.csi_item_instance_id
629 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
630 AND B.inventory_item_id = c_inventory_item_id)
631 AND EXISTS (SELECT 'X'
632 FROM ahl_pc_associations C
633 WHERE C.unit_item_id = A.unit_config_header_id
634 AND C.association_type_flag = 'U'
635 AND EXISTS (SELECT 'X'
636 FROM ahl_pc_nodes_b D
637 WHERE D.pc_node_id = C.pc_node_id
638 START WITH D.pc_node_id = c_pc_node_id
639 CONNECT BY D.parent_node_id= PRIOR D.pc_node_id))
640 UNION
641 SELECT a.subject_id instance_id
642 FROM csi_ii_relationships A
643 WHERE relationship_type_code = 'COMPONENT-OF'
644 AND a.relationship_id in (Select position_id from AHL_APPLICABLE_INSTANCES)
645 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
646 AND EXISTS (SELECT 'X'
647 FROM csi_item_instances B
648 WHERE B.instance_id = A.subject_id
649 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
650 AND B.inventory_item_id = c_inventory_item_id)
651 START WITH object_id IN (SELECT c.csi_item_instance_id
652 FROM ahl_unit_config_headers C,ahl_applicable_instances api
653 WHERE c.csi_item_instance_id=api.csi_item_instance_id
654 and api.position_id=c_relationship_id and
655 EXISTS (SELECT 'X'
656 FROM ahl_pc_associations D
657 WHERE D.unit_item_id = C.unit_config_header_id
658 AND D.association_type_flag = 'U'
659 AND EXISTS (SELECT 'X'
660 FROM ahl_pc_nodes_b E
661 WHERE E.pc_node_id = D.pc_node_id
662 START WITH E.pc_node_id = c_pc_node_id
663 CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)))
664 CONNECT BY object_id = PRIOR subject_id
665 -- sunil- fix for bug7411016
666 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
667 AND a.relationship_type_code = 'COMPONENT-OF'
668 UNION
669 SELECT a.csi_item_instance_id instance_id
670 FROM ahl_unit_config_headers A,ahl_applicable_instances api
671 WHERE api.position_id = c_relationship_id
672 AND A.csi_item_instance_id=API.csi_item_instance_id
673 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
674 AND EXISTS (SELECT 'X'
675 FROM csi_item_instances B
676 WHERE B.instance_id = api.csi_item_instance_id
677 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
678 AND B.inventory_item_id = c_inventory_item_id
679 AND EXISTS (SELECT 'X'
680 FROM ahl_pc_associations C
681 WHERE C.unit_item_id = B.inventory_item_id
682 AND C.association_type_flag = 'I'
683 AND EXISTS (SELECT 'X'
684 FROM ahl_pc_nodes_b D
685 WHERE D.pc_node_id = C.pc_node_id
686 START WITH D.pc_node_id = c_pc_node_id
687 CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)))
688 UNION
689 SELECT subject_id instance_id
690 FROM csi_ii_relationships A
691 WHERE relationship_type_code = 'COMPONENT-OF'
692 AND a.subject_id in (Select csi_item_instance_id from AHL_APPLICABLE_INSTANCES )
693 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
694 AND EXISTS (SELECT 'X'
695 FROM csi_item_instances B
696 WHERE B.instance_id = A.subject_id
697 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
698 AND B.inventory_item_id = c_inventory_item_id)
699 START WITH object_id IN (SELECT instance_id
700 FROM csi_item_instances C
701 WHERE SYSDATE between trunc(nvl(c.active_start_date,sysdate)) and trunc(nvl(C.active_end_date,sysdate+1))
702 AND EXISTS (SELECT 'X'
703 FROM ahl_pc_associations D
704 WHERE D.unit_item_id = C.inventory_item_id
705 AND D.association_type_flag = 'I'
706 AND EXISTS (SELECT 'X'
707 FROM ahl_pc_nodes_b E
708 WHERE E.pc_node_id = D.pc_node_id
709 START WITH E.pc_node_id = c_pc_node_id
710 CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)
711 )
712 )
713 CONNECT BY object_id = PRIOR subject_id
714 -- sunil- fix for bug7411016
715 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
716 AND a.relationship_type_code = 'COMPONENT-OF';*/
717
718 CURSOR get_inst4(c_inventory_item_id NUMBER, c_pc_node_id NUMBER) IS
719 SELECT A.instance_id
720 FROM csi_item_instances A
721 WHERE A.inventory_item_id = c_inventory_item_id
722 AND sysdate between trunc(nvl(A.active_start_date,sysdate))
723 and trunc(nvl(A.active_end_date,sysdate+1))
724 AND AHL_FMP_PVT.is_pc_assoc_valid(A.instance_id,c_pc_node_id) = FND_API.G_TRUE;
725
726 CURSOR get_inst5(c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
727 SELECT A.instance_id
728 FROM csi_item_instances A,ahl_applicable_instances api
729 WHERE A.instance_id = api.csi_item_instance_id
730 AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
731 AND api.position_id=c_relationship_id
732 AND AHL_FMP_PVT.is_pc_assoc_valid(A.instance_id,c_pc_node_id) = FND_API.G_TRUE;
733
734 CURSOR get_inst6(c_inventory_item_id NUMBER, c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
735 SELECT A.instance_id
736 FROM csi_item_instances A,ahl_applicable_instances api
737 WHERE A.instance_id = api.csi_item_instance_id
738 AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
739 AND api.position_id=c_relationship_id
740 AND A.inventory_item_id = c_inventory_item_id
741 AND AHL_FMP_PVT.is_pc_assoc_valid(A.instance_id,c_pc_node_id) = FND_API.G_TRUE;
742
743 --added for SB effectivity changes
744 CURSOR get_terminating_mr_csr(c_mr_header_id IN NUMBER) IS
745 SELECT mr_header_id
746 FROM ahl_mr_relationships
747 WHERE relationship_code = 'TERMINATES'
748 AND related_mr_header_id = c_mr_header_id;
749
750
751
752 get_terminating_mr_tbl nbr_tbl_type;
753 l_accom_ue_id NUMBER;
754 l_dummy_date DATE;
755 l_dummy_boolean BOOLEAN;
756 l_dummy_varchar VARCHAR2(30);
757 --SB effectivity changes end
758
759 -- JKJain, NR Analysis and Forecasting : Fetch rows having fleet effectivity
760 CURSOR get_fleet_header_id(c_mr_effectivity_id NUMBER)
761 IS
762 SELECT fleet_header_id
763 FROM ahl_mr_effectivities MR
764 WHERE MR.mr_effectivity_id = c_mr_effectivity_id ;
765 l_fleet_header_id NUMBER;
766 l_var VARCHAR2(1);
767
768
769 l_counter number;
770 BEGIN
771 SAVEPOINT GET_MR_AFFECTED_ITEMS_PVT;
772 IF l_debug = 'Y' THEN
773 AHL_DEBUG_PUB.enable_debug;
774 AHL_DEBUG_PUB.debug('Begin private API: AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS');
775 END IF;
776 IF FND_API.to_boolean(p_init_msg_list) THEN
777 FND_MSG_PUB.initialize;
778 END IF;
779 x_return_status := 'S';
780 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
781 p_api_version,
782 l_api_name,
783 G_PKG_NAME)
784 THEN
785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786 END IF;
787 -- Check whether the mr_header_id exists --
788 AHL_DEBUG_PUB.debug(' Phase 1');
789
790 DELETE FROM ahl_mr_instances_temp;
791 DELETE FROM ahl_applicable_instances;
792
793 OPEN check_mr_exists(p_mr_header_id);
794 FETCH check_mr_exists INTO l_mr_header_id;
795 IF check_mr_exists%NOTFOUND THEN
796 CLOSE check_mr_exists;
797 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_MR');
798 FND_MSG_PUB.ADD;
799 RAISE FND_API.G_EXC_ERROR;
800 END IF;
801 CLOSE check_mr_exists;
802 AHL_DEBUG_PUB.debug(' Phase 2');
803
804 -- Check whether the mr_effectivity_id exists and if it does, check whether it
805 -- belongs to the mr_header_id
806 OPEN check_mr_effect(p_mr_effectivity_id,p_mr_header_id);
807 LOOP
808 FETCH check_mr_effect INTO l_mr_effect;
809 EXIT WHEN check_mr_effect%NOTFOUND ;
810
811 IF l_mr_effect.relationship_id IS NOT NULL
812 THEN
813 AHL_MC_PATH_POSITION_PVT.map_position_to_instances
814 (
815 p_api_version =>p_api_version,
816 p_init_msg_list =>FND_API.G_FALSE,
817 p_commit =>FND_API.G_FALSE,
818 p_validation_level=>p_validation_level,
819 p_position_id =>l_mr_effect.relationship_id,
820 x_return_status =>l_return_Status,
821 x_msg_count =>l_msg_count,
822 x_msg_data =>l_msg_data
823 );
824
825 IF l_debug = 'Y' THEN
826 AHL_DEBUG_PUB.debug(' After Call to MC path positions');
827 Select count(*) into l_counter
828 from ahl_applicable_instances;
829 AHL_DEBUG_PUB.debug('Number of Recs found in ahl_applicable_instances are ...'||l_counter);
830 END IF;
831 END IF;
832 l_mr_header_id := l_mr_effect.mr_header_id;
833
834 IF (l_mr_header_id <> p_mr_header_id) THEN
835 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_MR_EFFECTIVITY');
836 FND_MSG_PUB.ADD;
837 l_error_flag:='Y';
838 END IF;
839 --END IF;
840 END LOOP;
841 CLOSE check_mr_effect;
842 IF L_ERROR_FLAG='Y'
843 THEN
844 RAISE FND_API.G_EXC_ERROR;
845 END IF;
846 AHL_DEBUG_PUB.debug(' Phase 3 relation...' ||l_mr_effect.relationship_id);
847
848 -- To avoid being in the same database session when refreshing or table navigating
849 -- the same page due to using the global temporary table.
850 AHL_DEBUG_PUB.debug(' Phase 4');
851 l_index :=1;
852
853 -- apattark changes for SB start
854 OPEN get_terminating_mr_csr(p_mr_header_id);
855 FETCH get_terminating_mr_csr BULK COLLECT INTO get_terminating_mr_tbl;
856 CLOSE get_terminating_mr_csr;
857 -- apattark changes for SB end
858
859 FOR l_mr_effect IN get_mr_effect(p_mr_header_id, p_mr_effectivity_id)
860 LOOP
861 IF (l_mr_effect.inventory_item_id IS NOT NULL AND l_mr_effect.relationship_id IS NULL
862 AND l_mr_effect.pc_node_id IS NULL) THEN
863 AHL_DEBUG_PUB.debug(' Phase 8');
864 --DBMS_OUTPUT.put_line('API1: Come here in case 1B and l_index is: '||l_index);
865 FOR l_get_inst1 IN get_inst1(l_mr_effect.inventory_item_id)
866 LOOP
867 AHL_DEBUG_PUB.debug(' Phase 9, l_mr_effect.inventory_item_id = '||l_mr_effect.inventory_item_id);
868 IF (check_effectivity_details(l_get_inst1.instance_id, l_mr_effect.mr_effectivity_id )
869 AND check_effectivity_ext_details(l_get_inst1.instance_id, l_mr_effect.mr_effectivity_id )) THEN
870 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst1.instance_id;
871 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
872 l_index := l_index+1;
873 END IF;
874 AHL_DEBUG_PUB.debug(' Phase 10, l_get_inst1.instance_id = '||l_get_inst1.instance_id);
875 END LOOP;
876 ELSIF (l_mr_effect.relationship_id IS NOT NULL
877 AND l_mr_effect.inventory_item_id IS NULL
878 AND l_mr_effect.pc_node_id IS NULL)
879 THEN
880 --DBMS_OUTPUT.put_line('API1: Come here in case 2A and l_index is: '||l_index);
881 --DBMS_OUTPUT.put_line('API1: Come here in case 2B and l_index is: '||l_index);
882 FOR l_get_inst2 IN get_inst2(l_mr_effect.relationship_id) LOOP
883 IF (check_effectivity_details(l_get_inst2.instance_id, l_mr_effect.mr_effectivity_id )
884 AND check_effectivity_ext_details(l_get_inst2.instance_id, l_mr_effect.mr_effectivity_id )) THEN
885 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst2.instance_id;
886 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
887 l_index := l_index+1;
888 END IF;
889 END LOOP;
890 ELSIF (l_mr_effect.relationship_id IS NOT NULL AND l_mr_effect.inventory_item_id IS NOT NULL
891 AND l_mr_effect.pc_node_id IS NULL) THEN
892 --DBMS_OUTPUT.put_line('API1: Come here in case 3B and l_index is: '||l_index);
893 FOR l_get_inst3 IN get_inst3(l_mr_effect.relationship_id, l_mr_effect.inventory_item_id) LOOP
894 IF (check_effectivity_details(l_get_inst3.instance_id, l_mr_effect.mr_effectivity_id )
895 AND check_effectivity_ext_details(l_get_inst3.instance_id, l_mr_effect.mr_effectivity_id )) THEN
896 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst3.instance_id;
897 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
898 l_index := l_index+1;
899 END IF;
900 END LOOP;
901 ELSIF (l_mr_effect.inventory_item_id IS NOT NULL AND l_mr_effect.pc_node_id IS NOT NULL
902 AND l_mr_effect.relationship_id IS NULL) THEN
903 --DBMS_OUTPUT.put_line('API1: Come here in case 4B and l_index is: '||l_index);
904 FOR l_get_inst4 IN get_inst4(l_mr_effect.inventory_item_id, l_mr_effect.pc_node_id) LOOP
905 --DBMS_OUTPUT.put_line('API1: Come here in case 4B after open cursor and l_index is: '||l_index);
906 IF (check_effectivity_details(l_get_inst4.instance_id, l_mr_effect.mr_effectivity_id )
907 AND check_effectivity_ext_details(l_get_inst4.instance_id, l_mr_effect.mr_effectivity_id )) THEN
908 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst4.instance_id;
909 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
910 l_index := l_index+1;
911 END IF;
912 END LOOP;
913 ELSIF (l_mr_effect.relationship_id IS NOT NULL AND l_mr_effect.pc_node_id IS NOT NULL
914 AND l_mr_effect.inventory_item_id IS NULL) THEN
915 --DBMS_OUTPUT.put_line('API1: Come here in case 5B and l_index is: '||l_index);
916 FOR l_get_inst5 IN get_inst5(l_mr_effect.relationship_id, l_mr_effect.pc_node_id) LOOP
917 IF (check_effectivity_details(l_get_inst5.instance_id, l_mr_effect.mr_effectivity_id )
918 AND check_effectivity_ext_details(l_get_inst5.instance_id, l_mr_effect.mr_effectivity_id )) THEN
919 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst5.instance_id;
920 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
921 l_index := l_index+1;
922 END IF;
923 END LOOP;
924
925 ELSIF (l_mr_effect.inventory_item_id IS NOT NULL AND l_mr_effect.relationship_id IS NOT NULL
926 AND l_mr_effect.pc_node_id IS NOT NULL) THEN
927
928 --DBMS_OUTPUT.put_line('API1: Come here in case 6B and l_index is: '||l_index);
929 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
930 IF (check_effectivity_details(l_get_inst6.instance_id, l_mr_effect.mr_effectivity_id )
931 AND check_effectivity_ext_details(l_get_inst6.instance_id, l_mr_effect.mr_effectivity_id )) THEN
932 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst6.instance_id;
933 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
934 l_index := l_index+1;
935 END IF;
936 END LOOP;
937
938 END IF;
939
940 -- pdoki modified l_index to l_index-1 inorder to refer the current record for Bug 12798707, Start.
941 -- apattark changes for SB start
942 x_mr_item_inst_tbl(l_index-1).terminate_flag := 'N';
943 IF get_terminating_mr_tbl.COUNT > 0 THEN
944 FOR j in get_terminating_mr_tbl.FIRST..get_terminating_mr_tbl.LAST
945 LOOP
946 ahl_ump_util_pkg.get_last_accomplishment(p_csi_item_instance_id => x_mr_item_inst_tbl(l_index-1).item_instance_id,
947 p_mr_header_id => get_terminating_mr_tbl(j),
948 x_unit_effectivity_id => l_accom_ue_id,
949 x_accomplishment_date => l_dummy_date,
950 x_deferral_flag => l_dummy_boolean,
951 x_status_code => l_dummy_varchar,
952 x_return_val => l_dummy_boolean
953 );
954 IF (l_accom_ue_id IS NULL) THEN
955 x_mr_item_inst_tbl(l_index-1).terminate_flag := 'N';
956 EXIT;
957 ELSE
958 x_mr_item_inst_tbl(l_index-1).terminate_flag := 'Y';
959 END IF;
960
961 END LOOP;
962 END IF;
963 -- apattark changes for SB end
964 -- pdoki modified for Bug 12798707, End.
965
966 END LOOP;
967 --DBMS_OUTPUT.put_line('API1: Come here after six cases and l_index is: '||l_index);
968 IF x_mr_item_inst_tbl.COUNT > 0 THEN
969 FOR i IN x_mr_item_inst_tbl.FIRST..x_mr_item_inst_tbl.LAST LOOP
970 --DBMS_OUTPUT.put_line('API1: Before checking details in for loop: i= '||i);
971 --Also filter OUT all of the items which are not job items
972 IF(p_top_node_flag = 'Y') THEN
973 x_mr_item_inst_tbl(i).item_instance_id := get_topInstanceID(x_mr_item_inst_tbl(i).item_instance_id);
974 END IF;
975 OPEN get_inst_attri(x_mr_item_inst_tbl(i).item_instance_id);
976 FETCH get_inst_attri INTO l_inst_attri;
977 x_mr_item_inst_tbl(i).serial_number := l_inst_attri.serial_number;
978 x_mr_item_inst_tbl(i).item_number := l_inst_attri.item_number;
979 x_mr_item_inst_tbl(i).inventory_item_id := l_inst_attri.inventory_item_id;
980 x_mr_item_inst_tbl(i).location := l_inst_attri.location_description;
981 x_mr_item_inst_tbl(i).status := l_inst_attri.status;
982 x_mr_item_inst_tbl(i).owner := l_inst_attri.owner_name;
983 x_mr_item_inst_tbl(i).condition := l_inst_attri.condition;
984 CLOSE get_inst_attri;
985
986 AHL_FMP_COMMON_PVT.validate_item(x_return_status => l_return_status,
987 x_msg_data => l_msg_data,
988 p_item_number => NULL,
989 p_x_inventory_item_id => x_mr_item_inst_tbl(i).inventory_item_id);
990 --DBMS_OUTPUT.put_line('PL/SQL table count= '||x_mr_item_inst_tbl.COUNT||' and last= '||x_mr_item_inst_tbl.LAST);
991 IF (l_return_status <> 'S' OR x_mr_item_inst_tbl(i).terminate_flag = 'Y')
992 THEN
993 --DBMS_OUTPUT.put_line('Deleting recored i= '||i);
994 x_mr_item_inst_tbl.DELETE(i);
995 END IF;
996 END LOOP;
997
998 -- JKJain, NR Analysis and Forecasting : Further filter based on Fleet effectivity
999 IF(p_consider_fleet_flag = 'Y') THEN --Bug 13636759
1000 IF( x_mr_item_inst_tbl.COUNT > 0 ) THEN
1001 FOR i IN x_mr_item_inst_tbl.FIRST..x_mr_item_inst_tbl.LAST LOOP
1002 IF x_mr_item_inst_tbl.EXISTS(i) THEN
1003 l_fleet_header_id := null;
1004 get_ucHeader(p_item_instance_id => x_mr_item_inst_tbl(i).item_instance_id,
1005 x_ucHeaderID => x_mr_item_inst_tbl(i).uc_header_id,
1006 x_unitName => x_mr_item_inst_tbl(i).unit_name);
1007 OPEN get_fleet_header_id(x_mr_item_inst_tbl(i).mr_effectivity_id);
1008 FETCH get_fleet_header_id into l_fleet_header_id;
1009 CLOSE get_fleet_header_id;
1010
1011 IF(l_fleet_header_id IS NOT NULL) THEN
1012 AHL_DEBUG_PUB.debug(' l_fleet_header_id = ' || l_fleet_header_id|| ' and uc_header_id = '||x_mr_item_inst_tbl(i).uc_header_id || ' and item_instance_id = ' || x_mr_item_inst_tbl(i).item_instance_id);
1013 IF(x_mr_item_inst_tbl(i).uc_header_id IS NOT NULL) THEN
1014
1015 IF(l_fleet_header_id <> nvl(AHL_UMP_ProcessUnit_PVT.get_fleet_from_unit_asso(x_mr_item_inst_tbl(i).uc_header_id,p_flt_range_calc_date,null),-1)) THEN
1016 AHL_DEBUG_PUB.debug(' Not Found , x_mr_item_inst_tbl(i).mr_effectivity_id = ' ||x_mr_item_inst_tbl(i).mr_effectivity_id);
1017 x_mr_item_inst_tbl.DELETE(i);
1018 END IF;
1019
1020 ELSE
1021 x_mr_item_inst_tbl.DELETE(i);
1022 END IF;
1023 END IF;
1024
1025
1026 END IF;
1027 END LOOP;
1028 END IF;
1029 END IF;
1030
1031 --DBMS_OUTPUT.put_line('PL/SQL table count= '||x_mr_item_inst_tbl.COUNT||' and last= '||x_mr_item_inst_tbl.LAST);
1032 IF x_mr_item_inst_tbl.COUNT > 0 THEN
1033 FOR i IN x_mr_item_inst_tbl.FIRST..x_mr_item_inst_tbl.LAST LOOP
1034 IF x_mr_item_inst_tbl.EXISTS(i) THEN
1035 INSERT INTO ahl_mr_instances_temp
1036 (
1037 MR_INSTANCE_TEMP_ID,
1038 MR_EFFECTIVITY_ID,
1039 ITEM_INSTANCE_ID,
1040 SERIAL_NUMBER,
1041 ITEM_NUMBER,
1042 INVENTORY_ITEM_ID,
1043 LOCATION,
1044 STATUS,
1045 OWNER,
1046 CONDITION,
1047 UNIT_NAME,
1048 UC_HEADER_ID
1049 )
1050 VALUES
1051 (
1052 i,
1053 x_mr_item_inst_tbl(i).mr_effectivity_id,
1054 x_mr_item_inst_tbl(i).item_instance_id,
1055 x_mr_item_inst_tbl(i).serial_number,
1056 x_mr_item_inst_tbl(i).item_number,
1057 x_mr_item_inst_tbl(i).inventory_item_id,
1058 x_mr_item_inst_tbl(i).location,
1059 x_mr_item_inst_tbl(i).status,
1060 x_mr_item_inst_tbl(i).owner,
1061 x_mr_item_inst_tbl(i).condition,
1062 -- JKJain, NR Analysis and Forecasting
1063 x_mr_item_inst_tbl(i).unit_name,
1064 x_mr_item_inst_tbl(i).uc_header_id
1065 );
1066 END IF;
1067 END LOOP;
1068 i := 1;
1069 IF p_unique_inst_flag = 'Y' THEN
1070 IF p_sort_flag = 'Y' THEN
1071 FOR l_get_dist_inst IN get_dist_sort_inst LOOP
1072 x_mr_item_inst_tbl(i).mr_effectivity_id := NULL;
1073 x_mr_item_inst_tbl(i).item_instance_id := l_get_dist_inst.item_instance_id;
1074 x_mr_item_inst_tbl(i).serial_number := l_get_dist_inst.serial_number;
1075 x_mr_item_inst_tbl(i).item_number := l_get_dist_inst.item_number;
1076 x_mr_item_inst_tbl(i).inventory_item_id := l_get_dist_inst.inventory_item_id;
1077 x_mr_item_inst_tbl(i).location := l_get_dist_inst.location;
1078 x_mr_item_inst_tbl(i).status := l_get_dist_inst.status;
1079 x_mr_item_inst_tbl(i).owner := l_get_dist_inst.owner;
1080 x_mr_item_inst_tbl(i).condition := l_get_dist_inst.condition;
1081
1082 -- JKJain, NR Analysis and Forecasting
1083 IF(p_consider_fleet_flag <> 'Y') THEN
1084 get_ucHeader(p_item_instance_id => x_mr_item_inst_tbl(i).item_instance_id,
1085 x_ucHeaderID => x_mr_item_inst_tbl(i).uc_header_id,
1086 x_unitName => x_mr_item_inst_tbl(i).unit_name);
1087 ELSE
1088 x_mr_item_inst_tbl(i).uc_header_id := l_get_dist_inst.uc_header_id;
1089 x_mr_item_inst_tbl(i).unit_name := l_get_dist_inst.unit_name;
1090 END IF;
1091 i := i+1;
1092
1093 END LOOP;
1094 ELSE
1095
1096 FOR l_get_dist_inst IN get_dist_inst LOOP
1097 x_mr_item_inst_tbl(i).mr_effectivity_id := NULL;
1098 x_mr_item_inst_tbl(i).item_instance_id := l_get_dist_inst.item_instance_id;
1099 x_mr_item_inst_tbl(i).serial_number := l_get_dist_inst.serial_number;
1100 x_mr_item_inst_tbl(i).item_number := l_get_dist_inst.item_number;
1101 x_mr_item_inst_tbl(i).inventory_item_id := l_get_dist_inst.inventory_item_id;
1102 x_mr_item_inst_tbl(i).location := l_get_dist_inst.location;
1103 x_mr_item_inst_tbl(i).status := l_get_dist_inst.status;
1104 x_mr_item_inst_tbl(i).owner := l_get_dist_inst.owner;
1105 x_mr_item_inst_tbl(i).condition := l_get_dist_inst.condition;
1106 /*
1107 OPEN get_uc_header(x_mr_item_inst_tbl(i).item_instance_id);
1108 FETCH get_uc_header INTO l_get_uc_header;
1109 IF get_uc_header%NOTFOUND THEN
1110 x_mr_item_inst_tbl(i).unit_name := NULL;
1111 x_mr_item_inst_tbl(i).uc_header_id := NULL;
1112 CLOSE get_uc_header;
1113 ELSE
1114 x_mr_item_inst_tbl(i).unit_name := l_get_uc_header.name;
1115 x_mr_item_inst_tbl(i).uc_header_id := l_get_uc_header.unit_config_header_id;
1116 CLOSE get_uc_header;
1117 END IF;
1118 */
1119 -- JKJain, NR Analysis and Forecasting
1120 IF(p_consider_fleet_flag <> 'Y') THEN
1121 get_ucHeader(p_item_instance_id => x_mr_item_inst_tbl(i).item_instance_id,
1122 x_ucHeaderID => x_mr_item_inst_tbl(i).uc_header_id,
1123 x_unitName => x_mr_item_inst_tbl(i).unit_name);
1124 ELSE
1125 x_mr_item_inst_tbl(i).uc_header_id := l_get_dist_inst.uc_header_id;
1126 x_mr_item_inst_tbl(i).unit_name := l_get_dist_inst.unit_name;
1127 END IF;
1128 i := i+1;
1129 END LOOP;
1130 END IF; -- p_sort_flag
1131 ELSE
1132 FOR l_get_all_inst IN get_all_inst LOOP
1133 x_mr_item_inst_tbl(i).mr_effectivity_id := l_get_all_inst.mr_effectivity_id;
1134 x_mr_item_inst_tbl(i).item_instance_id := l_get_all_inst.item_instance_id;
1135 x_mr_item_inst_tbl(i).serial_number := l_get_all_inst.serial_number;
1136 x_mr_item_inst_tbl(i).item_number := l_get_all_inst.item_number;
1137 x_mr_item_inst_tbl(i).inventory_item_id := l_get_all_inst.inventory_item_id;
1138 x_mr_item_inst_tbl(i).location := l_get_all_inst.location;
1139 x_mr_item_inst_tbl(i).status := l_get_all_inst.status;
1140 x_mr_item_inst_tbl(i).owner := l_get_all_inst.owner;
1141 x_mr_item_inst_tbl(i).condition := l_get_all_inst.condition;
1142 /*
1143 OPEN get_uc_header(x_mr_item_inst_tbl(i).item_instance_id);
1144 FETCH get_uc_header INTO l_get_uc_header;
1145 IF get_uc_header%NOTFOUND THEN
1146 x_mr_item_inst_tbl(i).unit_name := NULL;
1147 x_mr_item_inst_tbl(i).uc_header_id := NULL;
1148 CLOSE get_uc_header;
1149 ELSE
1150 x_mr_item_inst_tbl(i).unit_name := l_get_uc_header.name;
1151 x_mr_item_inst_tbl(i).uc_header_id := l_get_uc_header.unit_config_header_id;
1152 CLOSE get_uc_header;
1153 END IF;
1154 */
1155 -- JKJain, NR Analysis and Forecasting
1156 IF(p_consider_fleet_flag <> 'Y') THEN
1157 get_ucHeader(p_item_instance_id => x_mr_item_inst_tbl(i).item_instance_id,
1158 x_ucHeaderID => x_mr_item_inst_tbl(i).uc_header_id,
1159 x_unitName => x_mr_item_inst_tbl(i).unit_name);
1160 ELSE
1161 x_mr_item_inst_tbl(i).uc_header_id := l_get_all_inst.uc_header_id;
1162 x_mr_item_inst_tbl(i).unit_name := l_get_all_inst.unit_name;
1163 END IF;
1164 i := i+1;
1165 END LOOP;
1166 END IF;
1167 IF x_mr_item_inst_tbl.COUNT > i-1 THEN
1168 FOR j IN i..x_mr_item_inst_tbl.LAST LOOP
1169 IF x_mr_item_inst_tbl.EXISTS(j) THEN
1170 x_mr_item_inst_tbl.DELETE(j);
1171 END IF;
1172 END LOOP;
1173 END IF;
1174 END IF;
1175 END IF;
1176
1177 --DBMS_OUTPUT.put_line('API1: Just before commit and l_index= '||l_index);
1178 --IF FND_API.TO_BOOLEAN(p_commit) THEN
1179 -- COMMIT;
1180 --END IF;
1181 IF l_debug = 'Y' THEN
1182 AHL_DEBUG_PUB.debug('End private API: AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS');
1183 AHL_DEBUG_PUB.disable_debug;
1184 END IF;
1185 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1186 ROLLBACK TO GET_MR_AFFECTED_ITEMS_PVT;
1187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1188 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1189 p_count => x_msg_count,
1190 p_data => x_msg_data);
1191 IF l_debug = 'Y' THEN
1192 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1193 'UNEXPECTED ERROR IN PRIVATE:' );
1194 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS');
1195 AHL_DEBUG_PUB.disable_debug;
1196 END IF;
1197 WHEN FND_API.G_EXC_ERROR THEN
1198 ROLLBACK TO GET_MR_AFFECTED_ITEMS_PVT;
1199 x_return_status := FND_API.G_RET_STS_ERROR;
1200 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1201 p_count => x_msg_count,
1202 p_data => x_msg_data);
1203 IF l_debug = 'Y' THEN
1204 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1205 'ERROR IN PRIVATE:' );
1206 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS');
1207 AHL_DEBUG_PUB.disable_debug;
1208 END IF;
1209 WHEN OTHERS THEN
1210 ROLLBACK TO GET_MR_AFFECTED_ITEMS_PVT;
1211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1212 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1213 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_FMP_PVT',
1214 p_procedure_name => 'GET_MR_AFFECTED_ITEMS',
1215 p_error_text => SUBSTR(SQLERRM,1,240));
1216 END IF;
1217 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1218 p_count => x_msg_count,
1219 p_data => x_msg_data);
1220 IF l_debug = 'Y' THEN
1221 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
1222 'OTHER ERROR IN PRIVATE:' );
1223 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS');
1224 AHL_DEBUG_PUB.disable_debug;
1225 END IF;
1226 END GET_MR_AFFECTED_ITEMS;
1227
1228 -- This API is revamped to fix Bug 6266738
1229 -- Define procedure GET_APPLICABLE_MRS --
1230 -- JKJain, NR Analysis and Forecasting
1231 PROCEDURE GET_APPLICABLE_MRS(
1232 p_api_version IN NUMBER,
1233 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1234 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1235 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1236 x_return_status OUT NOCOPY VARCHAR2,
1237 x_msg_count OUT NOCOPY NUMBER,
1238 x_msg_data OUT NOCOPY VARCHAR2,
1239 p_item_instance_id IN NUMBER,
1240 p_mr_header_id IN NUMBER := NULL,
1241 p_components_flag IN VARCHAR2 := 'Y',
1242 p_include_doNotImplmt IN VARCHAR2 := 'Y',
1243 p_visit_type_code IN VARCHAR2 :=NULL,
1244 -- JKJain, NR Analysis and Forecasting
1245 p_consider_fleet_flag IN VARCHAR2 := 'N',
1246 p_flt_range_calc_date IN DATE := sysdate ,
1247 -- added parameter p_insert_into_tmp_tbl to fix perf bug# 13629335
1248 p_insert_into_tmp_tbl IN VARCHAR2 := 'N',
1249 x_applicable_mr_tbl OUT NOCOPY APPLICABLE_MR_TBL_TYPE
1250 ) IS
1251
1252
1253 l_api_name CONSTANT VARCHAR2(30) := 'GET_APPLICABLE_MRS';
1254 l_api_version CONSTANT NUMBER := 1.0;
1255 l_msg_count NUMBER;
1256 l_return_status VARCHAR2(1);
1257 l_item_instance_id NUMBER;
1258 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
1259 l_debug_prefix VARCHAR2(30) := 'AHL_FMP_PVT.GET_APPLICABLE_MRS';
1260
1261 --Get inst_relation_rec for top node of UC
1262 CURSOR uc_top_inst(c_item_instance_id NUMBER) IS
1263 SELECT --NULL parent_item_instance_id,
1264 --a.csi_item_instance_id,
1265 --a.master_config_id,
1266 a.unit_config_header_id
1267 FROM ahl_unit_config_headers a
1268 WHERE a.csi_item_instance_id = c_item_instance_id
1269 /* Fix for bug#4052646
1270 * AND a.parent_uc_header_id is not null -- Line commented
1271 */
1272 AND a.parent_uc_header_id is null
1273 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(NVL(a.active_end_date,sysdate+1))
1274 ;
1275 l_uc_top_inst uc_top_inst%ROWTYPE;
1276
1277 /* rewrote query for performance
1278 CURSOR validate_pc_node_csr (c_instance_id NUMBER) --amsriniv
1279 IS
1280 SELECT pc_node_id --amsriniv
1281 FROM ahl_pc_nodes_b B
1282 --WHERE B.pc_node_id = c_pc_node_id
1283 START WITH B.pc_node_id IN (select pc_node_id
1284 from ahl_pc_associations itm, csi_item_instances csi,
1285 (SELECT object_id
1286 FROM csi_ii_relationships E
1287 START WITH E.subject_id = c_instance_id
1288 AND E.relationship_type_code = 'COMPONENT-OF'
1289 CONNECT BY E.subject_id = PRIOR E.object_id
1290 AND E.relationship_type_code = 'COMPONENT-OF'
1291 union all
1292 select c_instance_id
1293 from dual) ii
1294 where itm.association_type_flag = 'I'
1295 and itm.unit_item_id = csi.inventory_item_id
1296 and csi.instance_id = ii.object_id
1297 UNION ALL
1298 select pc_node_id
1299 from ahl_pc_associations unit, ahl_unit_config_headers uc,
1300 (SELECT object_id
1301 FROM csi_ii_relationships E
1302 START WITH E.subject_id = c_instance_id
1303 AND E.relationship_type_code = 'COMPONENT-OF'
1304 CONNECT BY E.subject_id = PRIOR E.object_id
1305 AND E.relationship_type_code = 'COMPONENT-OF'
1306 union
1307 select c_instance_id
1308 from dual) ii
1309 where unit.association_type_flag = 'U'
1310 and unit.unit_item_id = uc.unit_config_header_id
1311 and uc.csi_item_instance_id = ii.object_id)
1312 CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
1313 */
1314
1315 -- Get valid pc nodes for an instance
1316 -- modified for perf fix 9434441
1317 -- split cursor into 2 parts.
1318 /*
1319 CURSOR validate_pc_node_csr (c_instance_id NUMBER,
1320 c_pc_node_id NUMBER)
1321 IS
1322 WITH ii AS (SELECT object_id
1323 FROM csi_ii_relationships E
1324 START WITH E.subject_id = c_instance_id
1325 -- sunil- fix for bug7411016
1326 AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
1327 AND E.relationship_type_code = 'COMPONENT-OF'
1328 CONNECT BY E.subject_id = PRIOR E.object_id
1329 -- sunil- fix for bug7411016
1330 AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
1331 AND E.relationship_type_code = 'COMPONENT-OF'
1332 UNION ALL
1333 SELECT c_instance_id
1334 FROM DUAL)
1335 SELECT 'x' --pc_node_id --amsriniv
1336 FROM ahl_pc_nodes_b B
1337 WHERE B.pc_node_id = c_pc_node_id
1338 START WITH B.pc_node_id IN (select pc_node_id
1339 from ahl_pc_associations itm, csi_item_instances csi,ii
1340 where itm.association_type_flag = 'I'
1341 and itm.unit_item_id = csi.inventory_item_id
1342 and csi.instance_id = ii.object_id
1343 UNION ALL
1344 select pc_node_id
1345 from ahl_pc_associations unit, ahl_unit_config_headers uc, ii
1346 where unit.association_type_flag = 'U'
1347 and unit.unit_item_id = uc.unit_config_header_id
1348 and uc.csi_item_instance_id = ii.object_id)
1349 CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
1350 */
1351
1352 -- Query to get valid pc nodes for an instance was split into 2 SQLs (item and unit)
1353 -- modified for perf fix 9434441
1354 -- check pc_node against items.
1355 -- Added hints recommended by appsperf to fix bug# 16003176
1356 CURSOR validate_itm_node_csr (c_instance_id NUMBER,
1357 c_pc_node_id NUMBER)
1358 IS
1359 SELECT /*+ CONNECT_BY_FILTERING */ 'x'
1360 FROM ahl_pc_nodes_b B
1361 WHERE B.pc_node_id = c_pc_node_id
1362 START WITH B.pc_node_id IN (select /*+ UNNEST */ pc_node_id
1363 from ahl_pc_associations itm, csi_item_instances csi,
1364 (select /*+ INDEX(E) */ object_id
1365 FROM ahl_config_components E
1366 START WITH E.subject_id = c_instance_id
1367 CONNECT BY E.subject_id = PRIOR E.object_id
1368 UNION ALL
1369 SELECT c_instance_id
1370 FROM DUAL) ii
1371 where itm.association_type_flag = 'I'
1372 and itm.unit_item_id = csi.inventory_item_id
1373 and csi.instance_id = ii.object_id)
1374 CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
1375
1376 -- check pc_node against units.
1377 -- Added hints recommended by appsperf to fix bug# 16003176
1378 CURSOR validate_unit_node_csr (c_instance_id NUMBER,
1379 c_pc_node_id NUMBER)
1380 IS
1381 SELECT /*+ CONNECT_BY_FILTERING */ 'x'
1382 FROM ahl_pc_nodes_b B
1383 WHERE B.pc_node_id = c_pc_node_id
1384 START WITH B.pc_node_id IN (select /*+ UNNEST */ pc_node_id
1385 from ahl_pc_associations unit, ahl_unit_config_headers uc,
1386 (select /*+ INDEX(E) */ object_id
1387 FROM ahl_config_components E
1388 START WITH E.subject_id = c_instance_id
1389 CONNECT BY E.subject_id = PRIOR E.object_id
1390 UNION ALL
1391 SELECT c_instance_id
1392 FROM DUAL) ii
1393 where unit.association_type_flag = 'U'
1394 and unit.unit_item_id = uc.unit_config_header_id
1395 and uc.csi_item_instance_id = ii.object_id)
1396 CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
1397
1398 -- Get attributes of a given MR
1399 -- Changes for SB effectivity
1400 CURSOR get_mr_attri(c_mr_header_id NUMBER) IS
1401 SELECT distinct mrb.mr_header_id, mrb.repetitive_flag,
1402 mrb.show_repetitive_code,
1403 --mrb.preceding_mr_header_id,
1404 mrb.copy_accomplishment_flag,
1405 mrb.implement_status_code,
1406 count_mr_descendents(c_mr_header_id) descendent_count,
1407 decode (nvl(lcr.relationship_code, mrr.relationship_code ), 'INITIATES', 'INITIATED_BY', lcr.relationship_code) relationship_code,
1408 (select MLC.mr_header_id
1409 from ahl_mr_loop_chain_relns MLC
1410 where MLC.mr_relationship_id = lcr.start_mr_relationship_id ) start_mr_header_id,
1411 lcr.sequence_number
1412 FROM ahl_mr_headers_b mrb,
1413 ahl_mr_loop_chain_relns lcr,
1414 ahl_mr_relationships mrr
1415 WHERE mrb.mr_header_id = c_mr_header_id
1416 AND lcr.mr_header_id(+) = mrb.mr_header_id
1417 AND mrr.related_mr_header_id(+) = mrb.mr_header_id
1418 AND mrr.relationship_code(+) = 'INITIATES';
1419
1420 /*CURSOR get_mr_attri(c_mr_header_id NUMBER) IS
1421 SELECT repetitive_flag,
1422 show_repetitive_code,
1423 preceding_mr_header_id,
1424 copy_accomplishment_flag,
1425 implement_status_code,
1426 count_mr_descendents(c_mr_header_id) descendent_count
1427 FROM ahl_mr_headers_b --perf bug 6266738. using base tables.
1428 WHERE mr_header_id = c_mr_header_id;
1429 */
1430 -- End changes for SB effectivity
1431
1432 l_get_mr_attri get_mr_attri%ROWTYPE;
1433
1434 /* not used
1435 -- Added for performance bug - 6138653
1436 CURSOR csi_root_instance_csr (p_instance_id IN NUMBER) IS
1437 SELECT root.object_id
1438 FROM csi_ii_relationships root
1439 WHERE NOT EXISTS (SELECT 'x'
1440 FROM csi_ii_relationships
1441 WHERE subject_id = root.object_id
1442 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1443 )
1444 START WITH root.subject_id = p_instance_id
1445 AND root.relationship_type_code = 'COMPONENT-OF'
1446 AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
1447 AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1))
1448 CONNECT BY PRIOR root.object_id = root.subject_id
1449 AND root.relationship_type_code = 'COMPONENT-OF'
1450 AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
1451 AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1));
1452 */
1453
1454 /* 12 Jul 08: rewrote for performance
1455 --- Performance Changes bug - 6138653
1456 CURSOR get_mr_details_csr(c_instance_id NUMBER, c_mr_header_id NUMBER, c_components_flag VARCHAR2) IS
1457 SELECT A.mr_header_id, A.mr_effectivity_id, A.relationship_id, A.pc_node_id, A.inventory_item_id,
1458 cir.object_id, cir.subject_id, cir.position_reference
1459 FROM ahl_mr_effectivities A,
1460 ahl_mr_headers_app_v MR,
1461 (select cir2.object_id,
1462 cii2.instance_id subject_id,
1463 nvl(uc.master_config_id, cir2.position_reference) position_reference,
1464 0 depth
1465 from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc
1466 where cii2.instance_id = c_instance_id
1467 and cii2.instance_id = cir2.subject_id(+)
1468 and cii2.instance_id = uc.csi_item_instance_id(+)
1469 and uc.parent_uc_header_id(+) is null
1470 and SYSDATE between trunc(nvl(uc.active_start_date,sysdate)) and trunc(NVL(uc.active_end_date,sysdate+1))
1471 UNION ALL
1472 SELECT a.object_id,
1473 a.subject_id,
1474 to_number(a.position_reference), level depth
1475 FROM csi_ii_relationships a
1476 WHERE c_components_flag = 'Y'
1477 START WITH object_id = c_instance_id
1478 AND relationship_type_code = 'COMPONENT-OF'
1479 AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(NVL(active_end_date,sysdate+1))
1480 CONNECT BY object_id = PRIOR subject_id
1481 AND relationship_type_code = 'COMPONENT-OF'
1482 AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(NVL(active_end_date,sysdate+1))) cir,
1483 csi_item_instances cii
1484 WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1485 AND MR.mr_header_id = A.mr_header_id
1486 AND cir.subject_id = cii.instance_id
1487 AND MR.mr_status_code = 'COMPLETE'
1488 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1489 AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
1490 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)
1491 AND (A.inventory_item_id = cii.inventory_item_id OR
1492 (A.inventory_item_id IS NULL AND A.relationship_id IS NOT NULL AND
1493 AHL_FMP_PVT.Instance_Matches_Path_Pos(cii.instance_id,A.relationship_id) = 'T'))
1494 ORDER BY cir.depth, cir.subject_id; -- depth, subject_id
1495 */
1496
1497 /* 15-Sept 08: Modified logic to seperate processing based Inventory Items, MC positions
1498 CURSOR get_mr_details_csr(c_instance_id NUMBER, c_mr_header_id NUMBER, c_components_flag VARCHAR2) IS
1499 WITH cir AS (select --cir2.object_id,
1500 cii2.instance_id subject_id,
1501 --nvl(uc.master_config_id, cir2.position_reference) position_reference,
1502 0 depth,
1503 cii2.inventory_item_id
1504 from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc
1505 where cii2.instance_id = c_instance_id
1506 --and cii2.instance_id = cir2.subject_id(+)
1507 --and cii2.instance_id = uc.csi_item_instance_id(+)
1508 --and uc.parent_uc_header_id(+) is null
1509 --and SYSDATE between trunc(nvl(uc.active_start_date,sysdate))
1510 --and trunc(NVL(uc.active_end_date,sysdate+1))
1511 UNION ALL
1512 SELECT --a.object_id,
1513 a.subject_id,
1514 --to_number(a.position_reference),
1515 level depth,
1516 (select inventory_item_id
1517 from csi_item_instances
1518 where instance_id = a.subject_id) inventory_item_id
1519 FROM csi_ii_relationships a
1520 WHERE c_components_flag = 'Y'
1521 START WITH object_id = c_instance_id
1522 AND relationship_type_code = 'COMPONENT-OF'
1523 AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1524 AND trunc(NVL(active_end_date,sysdate+1))
1525 CONNECT BY object_id = PRIOR subject_id
1526 AND relationship_type_code = 'COMPONENT-OF'
1527 AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1528 AND trunc(NVL(active_end_date,sysdate+1))
1529 )
1530 SELECT * FROM (-- first query will match based on inventory items
1531 SELECT A.mr_header_id, A.mr_effectivity_id,
1532 -- A.relationship_id,
1533 A.pc_node_id,
1534 -- A.inventory_item_id,
1535 -- cir.object_id,
1536 cir.subject_id,
1537 --cir.position_reference ,
1538 cir.depth
1539 FROM ahl_mr_headers_app_v MR, cir,
1540 ahl_mr_effectivities A
1541 WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1542 AND MR.mr_header_id = A.mr_header_id
1543 AND MR.mr_status_code = 'COMPLETE'
1544 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1545 AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
1546 AND MR.version_number in (SELECT max(MRM.version_number)
1547 from ahl_mr_headers_app_v MRM
1548 where SYSDATE between trunc(MR.effective_from)
1549 and trunc(nvl(MR.effective_to,SYSDATE+1))
1550 and title=mr.title and mr_status_code='COMPLETE'
1551 group by MRM.title)
1552 AND A.inventory_item_id = cir.inventory_item_id
1553
1554 UNION ALL
1555
1556 -- query will match based on path position
1557 SELECT A.mr_header_id, A.mr_effectivity_id, --A.relationship_id,
1558 A.pc_node_id, --A.inventory_item_id,
1559 --cir.object_id,
1560 cir.subject_id,
1561 --cir.position_reference,
1562 cir.depth
1563 FROM ahl_mr_headers_app_v MR, ahl_mc_path_positions mcp,
1564 --ahl_mc_headers_b hdr, ahl_mc_relationships rel,
1565 cir, ahl_mr_effectivities A
1566 WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1567 AND MR.mr_header_id = A.mr_header_id
1568 AND MR.mr_status_code = 'COMPLETE'
1569 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1570 AND SYSDATE between trunc(MR.effective_from)
1571 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1572 AND MR.version_number in (SELECT max(MRM.version_number)
1573 from ahl_mr_headers_app_v MRM
1574 where SYSDATE between trunc(MR.effective_from)
1575 and trunc(nvl(MR.effective_to,SYSDATE+1))
1576 and title=mr.title and mr_status_code='COMPLETE'
1577 group by MRM.title)
1578 AND a.relationship_id = mcp.path_position_id
1579 AND (a.inventory_item_id IS NULL OR a.inventory_item_id = cir.inventory_item_id)
1580 -- AND TO_NUMBER(cir.POSITION_REFERENCE) = rel.RELATIONSHIP_ID
1581 -- AND REL.mc_header_id = HDR.mc_header_id
1582 AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id) = 'T'
1583 -- AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id,
1584 -- mcp.ENCODED_PATH_POSITION, hdr.mc_id,
1585 -- hdr.version_number, rel.position_key) = 'T'
1586
1587 ) appl_mr
1588 ORDER BY appl_mr.depth, appl_mr.subject_id; -- depth, subject_id
1589 */
1590
1591 -- for the configuration components, get valid mr effectivities based on inventory items.
1592 -- replaced csi_ii_relationships with ahl_config_components to fix bug# 9434441
1593 -- performance fix for bug# 13629335: split cursor to do away with nvl(c_mr_header_id, A.mr_header_id)
1594 -- and removed one level of inner query
1595 CURSOR get_comp_mr_inv_csr(c_instance_id IN NUMBER, c_appln_usg_code IN VARCHAR2) IS
1596 SELECT A.mr_header_id, A.mr_effectivity_id,
1597 A.pc_node_id,
1598 -- JKJain, NR Analysis and Forecasting
1599 A.fleet_header_id,
1600 ii.instance_id subject_id,
1601 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1602 WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1603 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1604 ii.serial_number --,
1605 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1606 FROM ( SELECT a.subject_id
1607 --FROM csi_ii_relationships a
1608 FROM ahl_config_components a
1609 START WITH object_id = c_instance_id
1610 --AND relationship_type_code = 'COMPONENT-OF'
1611 --AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1612 --AND trunc(NVL(active_end_date,sysdate+1))
1613 CONNECT BY object_id = PRIOR subject_id
1614 --AND relationship_type_code = 'COMPONENT-OF'
1615 --AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1616 --AND trunc(NVL(active_end_date,sysdate+1))
1617 ) cir, csi_item_instances ii,
1618 ahl_mr_effectivities A, ahl_mr_headers_b MR
1619 WHERE --A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1620 ii.instance_id = cir.subject_id
1621 AND A.inventory_item_id = ii.inventory_item_id
1622 AND A.relationship_id is null
1623 AND A.mr_header_id = MR.mr_header_id
1624 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1625 AND SYSDATE between trunc(MR.effective_from) AND trunc(nvl(MR.effective_to,SYSDATE+1))
1626 AND MR.mr_status_code='COMPLETE'
1627 AND MR.application_usg_code = c_appln_usg_code
1628 AND MR.version_number in (SELECT /*+ push_subq no_unnest */ max(MRM.version_number)
1629 FROM ahl_mr_headers_b MRM
1630 WHERE mrm.title = MR.title
1631 AND SYSDATE between trunc(MRM.effective_from)
1632 AND trunc(nvl(MRM.effective_to,SYSDATE+1))
1633 AND mr_status_code='COMPLETE'
1634 AND MRM.application_usg_code = c_appln_usg_code
1635 )
1636 -- ORDER BY ii.instance_id;
1637 ORDER BY A.mr_effectivity_id, A.mr_header_id;
1638
1639 -- added for perf bug#13629335
1640 -- for the configuration components, get valid mr effectivities based on inventory items for a given MR.
1641 -- removed one level of inner query to fix perf bug# 13629335
1642 CURSOR get_comp_mrID_inv_csr(c_instance_id IN NUMBER, c_mr_header_id IN NUMBER, c_appln_usg_code IN VARCHAR2) IS
1643 SELECT A.mr_header_id, A.mr_effectivity_id,
1644 A.pc_node_id,
1645 -- JKJain, NR Analysis and Forecasting
1646 A.fleet_header_id,
1647 ii.instance_id subject_id,
1648 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1649 WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1650 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1651 ii.serial_number
1652 FROM ( SELECT a.subject_id
1653 FROM ahl_config_components a
1654 START WITH object_id = c_instance_id
1655 CONNECT BY object_id = PRIOR subject_id
1656 ) cir, csi_item_instances ii,
1657 ahl_mr_effectivities A, ahl_mr_headers_b MR
1658 WHERE A.mr_header_id = c_mr_header_id
1659 AND ii.instance_id = cir.subject_id
1660 AND A.inventory_item_id = ii.inventory_item_id
1661 AND A.relationship_id is null
1662 AND MR.mr_header_id = A.mr_header_id
1663 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1664 AND MR.application_usg_code = c_appln_usg_code
1665 AND SYSDATE between trunc(MR.effective_from)
1666 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1667 AND MR.mr_status_code='COMPLETE'
1668 AND MR.version_number in (SELECT max(MRM.version_number)
1669 FROM ahl_mr_headers_b MRM
1670 WHERE mrm.title = mr.title
1671 AND SYSDATE between trunc(MRM.effective_from)
1672 AND trunc(nvl(MRM.effective_to,SYSDATE+1))
1673 AND MRM.mr_status_code='COMPLETE'
1674 AND MRM.application_usg_code = c_appln_usg_code
1675 )
1676 ORDER BY A.mr_effectivity_id, A.mr_header_id;
1677
1678 -- for input instance, get valid mr effectivities based on inventory items.
1679 -- performance fix for bug# 13629335: split cursor to do away with nvl(c_mr_header_id, A.mr_header_id)
1680 -- and removed one level of inner subquery.
1681 CURSOR get_inst_mr_inv_csr(c_instance_id NUMBER, c_appln_usg_code IN VARCHAR2) IS
1682 SELECT A.mr_header_id, A.mr_effectivity_id,
1683 A.pc_node_id,
1684 -- JKJain, NR Analysis and Forecasting
1685 A.fleet_header_id,
1686 ii.instance_id subject_id,
1687 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1688 WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1689 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1690 ii.serial_number --,
1691 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1692 FROM csi_item_instances ii,
1693 ahl_mr_effectivities A, ahl_mr_headers_b MR
1694 WHERE ii.instance_id = c_instance_id
1695 --AND A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1696 AND A.inventory_item_id = ii.inventory_item_id
1697 AND A.relationship_id is null
1698 AND MR.mr_header_id = A.mr_header_id
1699 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1700 AND MR.application_usg_code = c_appln_usg_code
1701 AND SYSDATE between trunc(MR.effective_from)
1702 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1703 AND MR.mr_status_code='COMPLETE'
1704 AND MR.version_number in (SELECT /*+ push_subq no_unnest */ max(MRM.version_number)
1705 FROM ahl_mr_headers_b MRM
1706 WHERE mrm.title = mr.title
1707 AND SYSDATE between trunc(MRM.effective_from)
1708 AND trunc(nvl(MRM.effective_to,SYSDATE+1))
1709 AND MRM.mr_status_code='COMPLETE'
1710 AND MRM.application_usg_code = c_appln_usg_code
1711 )
1712 --ORDER BY ii.instance_id;
1713 ORDER BY A.mr_effectivity_id, A.mr_header_id;
1714
1715 -- added for perf fix 13629335
1716 -- for input instance, get valid mr effectivities based on inventory items.
1717 CURSOR get_inst_mrID_inv_csr(c_instance_id NUMBER, c_mr_header_id NUMBER, c_appln_usg_code IN VARCHAR2) IS
1718 SELECT A.mr_header_id, A.mr_effectivity_id,
1719 A.pc_node_id,
1720 -- JKJain, NR Analysis and Forecasting
1721 A.fleet_header_id,
1722 ii.instance_id subject_id,
1723 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1724 WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1725 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1726 ii.serial_number --,
1727 FROM csi_item_instances ii,
1728 ahl_mr_effectivities A
1729 WHERE ii.instance_id = c_instance_id
1730 AND A.mr_header_id = c_mr_header_id
1731 AND A.inventory_item_id = ii.inventory_item_id
1732 AND A.relationship_id is null
1733 AND exists (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 'x' from ahl_mr_headers_b MR
1734 WHERE MR.mr_header_id = A.mr_header_id
1735 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1736 AND MR.application_usg_code = c_appln_usg_code
1737 AND SYSDATE between trunc(MR.effective_from)
1738 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1739 AND MR.mr_status_code='COMPLETE'
1740 AND MR.version_number in (SELECT max(MRM.version_number)
1741 FROM ahl_mr_headers_b MRM
1742 WHERE mrm.title = mr.title
1743 AND SYSDATE between trunc(MRM.effective_from)
1744 AND trunc(nvl(MRM.effective_to,SYSDATE+1))
1745 AND MRM.mr_status_code='COMPLETE'
1746 AND MRM.application_usg_code = c_appln_usg_code
1747 )
1748 )
1749 ORDER BY A.mr_effectivity_id, A.mr_header_id;
1750
1751 -- get valid mr effectivities based on path position ID.
1752 -- performance fix for bug# 13629335: split cursor to do away with nvl(c_mr_header_id, A.mr_header_id)
1753 CURSOR get_posn_mr_csr(c_appln_usg_code IN VARCHAR2) IS
1754 SELECT A.mr_header_id, A.mr_effectivity_id,
1755 A.pc_node_id,
1756 -- JKJain, NR Analysis and Forecasting
1757 A.fleet_header_id,
1758 cii.instance_id subject_id,
1759 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1760 WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1761 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1762 cii.serial_number --,
1763 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1764 FROM ahl_applicable_instances aai,csi_item_instances cii,
1765 ahl_mr_effectivities A
1766 WHERE
1767 --A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1768 --AND
1769 A.relationship_id IS NOT NULL
1770 AND aai.position_id = A.relationship_id
1771 AND aai.csi_item_instance_id = cii.instance_id
1772 AND nvl(A.inventory_item_id,cii.inventory_item_id) = cii.inventory_item_id
1773 AND exists (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 'x' from ahl_mr_headers_b MR
1774 WHERE MR.mr_header_id = A.mr_header_id
1775 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1776 AND MR.application_usg_code = c_appln_usg_code
1777 AND MR.version_number in (SELECT max(MRM.version_number)
1778 FROM ahl_mr_headers_b MRM
1779 WHERE mrm.title = mr.title
1780 AND SYSDATE between trunc(MRM.effective_from)
1781 AND trunc(nvl(MRM.effective_to,SYSDATE+1))
1782 AND mr_status_code='COMPLETE'
1783 AND MRM.application_usg_code = c_appln_usg_code
1784 )
1785 )
1786 --ORDER BY cii.instance_id;
1787 ORDER BY A.mr_effectivity_id, A.mr_header_id;
1788
1789 -- added for performance fix for bug# 13629335
1790 -- get valid mr effectivities based on path position ID.
1791 CURSOR get_posn_mrID_csr(c_mr_header_id NUMBER, c_appln_usg_code IN VARCHAR2) IS
1792 SELECT A.mr_header_id, A.mr_effectivity_id,
1793 A.pc_node_id,
1794 -- JKJain, NR Analysis and Forecasting
1795 A.fleet_header_id,
1796 cii.instance_id subject_id,
1797 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1798 WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1799 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1800 cii.serial_number --,
1801 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1802 FROM ahl_applicable_instances aai,csi_item_instances cii,
1803 ahl_mr_effectivities A
1804 WHERE A.mr_header_id = c_mr_header_id
1805 AND A.relationship_id IS NOT NULL
1806 AND aai.position_id = A.relationship_id
1807 AND aai.csi_item_instance_id = cii.instance_id
1808 AND nvl(A.inventory_item_id,cii.inventory_item_id) = cii.inventory_item_id
1809 AND exists (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 'x' from ahl_mr_headers_b MR
1810 WHERE MR.mr_header_id = A.mr_header_id
1811 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1812 AND MR.application_usg_code = c_appln_usg_code
1813 AND MR.version_number in (SELECT max(MRM.version_number)
1814 FROM ahl_mr_headers_b MRM
1815 WHERE mrm.title = mr.title
1816 AND SYSDATE between trunc(MRM.effective_from)
1817 AND trunc(nvl(MRM.effective_to,SYSDATE+1))
1818 AND mr_status_code='COMPLETE'
1819 AND MRM.application_usg_code = c_appln_usg_code
1820 )
1821 )
1822 --ORDER BY cii.instance_id;
1823 ORDER BY A.mr_effectivity_id, A.mr_header_id;
1824
1825 /* 12 Jul 08: Modified for performance.
1826 CURSOR get_visit_mr_details_csr(c_instance_id NUMBER, c_visit_type_code VARCHAR2) IS
1827 SELECT A.mr_header_id, A.mr_effectivity_id, A.relationship_id, A.pc_node_id, A.inventory_item_id,
1828 cir.object_id, cir.subject_id, cir.position_reference
1829 FROM ahl_mr_effectivities A, ahl_mr_headers_app_v MR, ahl_mr_visit_types vis,
1830 (select cir2.object_id,
1831 cii2.instance_id subject_id,
1832 nvl(uc.master_config_id, cir2.position_reference) position_reference,
1833 0 depth
1834 from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc
1835 where cii2.instance_id = c_instance_id
1836 and cii2.instance_id = cir2.subject_id(+)
1837 and cii2.instance_id = uc.csi_item_instance_id(+)
1838 and uc.parent_uc_header_id(+) is null
1839 and SYSDATE between trunc(nvl(uc.active_start_date,sysdate)) and trunc(NVL(uc.active_end_date,sysdate+1))
1840 ) cir,
1841 csi_item_instances cii
1842 WHERE MR.mr_header_id = A.mr_header_id
1843 AND A.mr_header_id = vis.mr_header_id
1844 AND vis.mr_visit_type_code = c_visit_type_code
1845 AND cir.subject_id = cii.instance_id
1846 AND MR.mr_status_code = 'COMPLETE'
1847 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1848 AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
1849 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)
1850 AND (A.inventory_item_id = cii.inventory_item_id OR
1851 (A.inventory_item_id IS NULL AND A.relationship_id IS NOT NULL AND
1852 AHL_FMP_PVT.Instance_Matches_Path_Pos(cii.instance_id,A.relationship_id) = 'T'))
1853 ORDER BY cir.depth, cir.subject_id; -- depth, subject_id
1854 */
1855
1856 /* 15-Sept 08: Modified logic to seperate processing based Inventory Items, MC positions
1857 CURSOR get_visit_mr_details_csr(c_instance_id NUMBER, c_visit_type_code VARCHAR2) IS
1858 WITH cir AS (select --cir2.object_id,
1859 cii2.instance_id subject_id,
1860 --nvl(uc.master_config_id, cir2.position_reference) position_reference,
1861 0 depth,
1862 cii2.inventory_item_id
1863 from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc
1864 where cii2.instance_id = c_instance_id
1865 --and cii2.instance_id = cir2.subject_id(+)
1866 --and cii2.instance_id = uc.csi_item_instance_id(+)
1867 --and uc.parent_uc_header_id(+) is null
1868 --and SYSDATE between trunc(nvl(uc.active_start_date,sysdate))
1869 --and trunc(NVL(uc.active_end_date,sysdate+1))
1870 )
1871 SELECT * FROM (-- first query will match based on inventory items
1872 SELECT A.mr_header_id, A.mr_effectivity_id,
1873 -- A.relationship_id,
1874 A.pc_node_id,
1875 -- A.inventory_item_id,
1876 --cir.object_id,
1877 cir.subject_id,
1878 --cir.position_reference ,
1879 cir.depth
1880 FROM ahl_mr_headers_app_v MR, ahl_mr_visit_types vis, cir,
1881 ahl_mr_effectivities A
1882 WHERE MR.mr_header_id = A.mr_header_id
1883 AND A.mr_header_id = vis.mr_header_id
1884 AND vis.mr_visit_type_code = c_visit_type_code
1885 AND MR.mr_status_code = 'COMPLETE'
1886 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1887 AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
1888 AND MR.version_number in (SELECT max(MRM.version_number)
1889 from ahl_mr_headers_app_v MRM
1890 where SYSDATE between trunc(MR.effective_from)
1891 and trunc(nvl(MR.effective_to,SYSDATE+1))
1892 and title=mr.title and mr_status_code='COMPLETE'
1893 group by MRM.title)
1894 AND A.inventory_item_id = cir.inventory_item_id
1895
1896 UNION ALL
1897
1898 -- query will match based on path position
1899 SELECT A.mr_header_id, A.mr_effectivity_id, --A.relationship_id,
1900 A.pc_node_id, --A.inventory_item_id,
1901 --cir.object_id,
1902 cir.subject_id,
1903 --cir.position_reference,
1904 cir.depth
1905 FROM ahl_mr_headers_app_v MR, ahl_mr_visit_types vis, ahl_mc_path_positions mcp,
1906 --ahl_mc_headers_b hdr, ahl_mc_relationships rel,
1907 cir, ahl_mr_effectivities A
1908 WHERE MR.mr_header_id = A.mr_header_id
1909 AND A.mr_header_id = vis.mr_header_id
1910 AND vis.mr_visit_type_code = c_visit_type_code
1911 AND MR.mr_status_code = 'COMPLETE'
1912 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1913 AND SYSDATE between trunc(MR.effective_from)
1914 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1915 AND MR.version_number in (SELECT max(MRM.version_number)
1916 from ahl_mr_headers_app_v MRM
1917 where SYSDATE between trunc(MR.effective_from)
1918 and trunc(nvl(MR.effective_to,SYSDATE+1))
1919 and title=mr.title and mr_status_code='COMPLETE'
1920 group by MRM.title)
1921 AND a.relationship_id = mcp.path_position_id
1922 AND (a.inventory_item_id IS NULL OR a.inventory_item_id = cir.inventory_item_id)
1923 -- AND TO_NUMBER(cir.POSITION_REFERENCE) = rel.RELATIONSHIP_ID
1924 -- AND REL.mc_header_id = HDR.mc_header_id
1925 AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id) = 'T'
1926 -- AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id,
1927 -- mcp.ENCODED_PATH_POSITION, hdr.mc_id,
1928 -- hdr.version_number, rel.position_key) = 'T'
1929
1930 ) appl_mr
1931 ORDER BY appl_mr.depth, appl_mr.subject_id; -- depth, subject_id
1932 */
1933
1934 -- for the configuration components, get valid mr effectivities based on inventory items.
1935 -- replaced csi_ii_relationships with ahl_config_components to fix bug# 9434441
1936 CURSOR get_comp_vst_inv_csr(c_instance_id NUMBER, c_visit_type_code VARCHAR2) IS
1937 SELECT A.mr_header_id, A.mr_effectivity_id,
1938 A.pc_node_id,
1939 -- JKJain, NR Analysis and Forecasting
1940 A.fleet_header_id,
1941 cii.instance_id subject_id,
1942 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1943 WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1944 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1945 cii.serial_number --,
1946 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1947 FROM ( SELECT a.subject_id
1948 --FROM csi_ii_relationships a
1949 FROM ahl_config_components a
1950 START WITH object_id = c_instance_id
1951 --AND relationship_type_code = 'COMPONENT-OF'
1952 --AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1953 --AND trunc(NVL(active_end_date,sysdate+1))
1954 CONNECT BY object_id = PRIOR subject_id
1955 --AND relationship_type_code = 'COMPONENT-OF'
1956 --AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1957 --AND trunc(NVL(active_end_date,sysdate+1))
1958 ) cir, csi_item_instances cii, ahl_mr_visit_types vis,
1959 ahl_mr_effectivities A
1960 WHERE A.mr_header_id = vis.mr_header_id
1961 AND vis.mr_visit_type_code = c_visit_type_code
1962 AND cir.subject_id = cii.instance_id
1963 AND A.inventory_item_id = cii.inventory_item_id
1964 AND A.relationship_id is null
1965 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
1966 WHERE MR.mr_header_id = A.mr_header_id
1967 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1968 AND MR.version_number in (SELECT max(MRM.version_number)
1969 FROM ahl_mr_headers_app_v MRM
1970 WHERE mrm.title = mr.title
1971 AND SYSDATE between trunc(MRM.effective_from)
1972 AND trunc(nvl(MRM.effective_to,SYSDATE+1))
1973 AND mr_status_code='COMPLETE'
1974 )
1975 )
1976 -- ORDER BY cii.instance_id;
1977 ORDER BY A.mr_effectivity_id,A.mr_header_id;
1978
1979
1980 -- for input instance, get valid mr effectivities based on inventory items.
1981 CURSOR get_inst_vst_inv_csr(c_instance_id NUMBER, c_visit_type_code VARCHAR2) IS
1982 SELECT A.mr_header_id, A.mr_effectivity_id,
1983 A.pc_node_id,
1984 -- JKJain, NR Analysis and Forecasting
1985 A.fleet_header_id,
1986 cii.instance_id subject_id,
1987 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1988 WHERE cii.instance_id = ciea1.instance_id(+) AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
1989 AND ciea1.attribute_level(+) = 'GLOBAL') mfg_date,
1990 cii.serial_number --,
1991 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1992 FROM csi_item_instances cii,ahl_mr_visit_types vis,
1993 ahl_mr_effectivities A
1994 WHERE A.mr_header_id = vis.mr_header_id
1995 AND vis.mr_visit_type_code = c_visit_type_code
1996 AND cii.instance_id = c_instance_id
1997 AND A.inventory_item_id = cii.inventory_item_id
1998 AND A.relationship_id is null
1999 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
2000 WHERE MR.mr_header_id = A.mr_header_id
2001 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
2002 AND MR.version_number in (SELECT max(MRM.version_number)
2003 FROM ahl_mr_headers_app_v MRM
2004 WHERE mrm.title = mr.title
2005 AND SYSDATE between trunc(MRM.effective_from)
2006 AND trunc(nvl(MRM.effective_to,SYSDATE+1))
2007 AND mr_status_code='COMPLETE'
2008 )
2009 )
2010 --ORDER BY cii.instance_id;
2011 ORDER BY A.mr_effectivity_id,A.mr_header_id;
2012
2013 -- get valid mr effectivities based on path position ID.
2014 CURSOR get_posn_vst_csr(c_visit_type_code VARCHAR2) IS
2015 SELECT A.mr_header_id, A.mr_effectivity_id,
2016 A.pc_node_id,
2017 -- JKJain, NR Analysis and Forecasting
2018 A.fleet_header_id,
2019 cii.instance_id subject_id,
2020 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
2021 WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
2022 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
2023 cii.serial_number --,
2024 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
2025 FROM ahl_applicable_instances aai,csi_item_instances cii,ahl_mr_visit_types vis,
2026 ahl_mr_effectivities A
2027 WHERE A.mr_header_id = vis.mr_header_id
2028 AND vis.mr_visit_type_code = c_visit_type_code
2029 AND A.relationship_id IS NOT NULL
2030 AND aai.position_id = A.relationship_id
2031 AND nvl(A.inventory_item_id, cii.inventory_item_id) = cii.inventory_item_id
2032 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
2033 WHERE MR.mr_header_id = A.mr_header_id
2034 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
2035 AND MR.version_number in (SELECT max(MRM.version_number)
2036 FROM ahl_mr_headers_app_v MRM
2037 WHERE mrm.title = mr.title
2038 AND SYSDATE between trunc(MRM.effective_from)
2039 AND trunc(nvl(MRM.effective_to,SYSDATE+1))
2040 AND mr_status_code='COMPLETE'
2041 )
2042 )
2043 --ORDER BY cii.instance_id;
2044 ORDER BY A.mr_effectivity_id, A.mr_header_id;
2045
2046
2047 -- check for path position based effectivities.
2048 CURSOR relationship_csr IS
2049 SELECT 'x' from dual
2050 WHERE exists (select 'x'
2051 from ahl_mr_effectivities mre, ahl_mr_headers_b mr
2052 where mre.relationship_id is not null
2053 and mre.mr_header_id = mr.mr_header_id
2054 and SYSDATE between trunc(mr.effective_from) AND trunc(nvl(mr.effective_to,SYSDATE+1))
2055 -- commented for performance bug# 9434441 and added effective date chk directly.
2056 --and exists (SELECT 'x' from ahl_mr_headers_app_v MR
2057 -- WHERE MR.mr_header_id = mre.mr_header_id
2058 -- AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
2059 -- AND MR.version_number in (SELECT max(MRM.version_number)
2060 -- FROM ahl_mr_headers_app_v MRM
2061 -- WHERE mrm.title = mr.title
2062 -- AND SYSDATE between trunc(MRM.effective_from)
2063 -- AND trunc(nvl(MRM.effective_to,SYSDATE+1))
2064 -- AND mr_status_code='COMPLETE'
2065 -- )
2066 -- )
2067 );
2068
2069
2070 -- check for path position based effectivities for visit type
2071 CURSOR relationship_vtype_csr(p_visit_type_code IN VARCHAR2) IS
2072 SELECT 'x' from dual
2073 WHERE exists (select 'x'
2074 from ahl_mr_effectivities mre, ahl_mr_visit_types vis, ahl_mr_headers_b mr
2075 where vis.mr_visit_type_code = p_visit_type_code
2076 and mre.mr_header_id = vis.mr_header_id
2077 and mre.mr_header_id = mr.mr_header_id
2078 and mre.relationship_id is not null
2079 and SYSDATE between trunc(mr.effective_from) AND trunc(nvl(mr.effective_to,SYSDATE+1))
2080 -- commented for performance bug# 9434441 and added effective date chk directly.
2081 --and exists (SELECT 'x' from ahl_mr_headers_app_v MR
2082 -- WHERE MR.mr_header_id = vis.mr_header_id
2083 -- AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
2084 -- AND MR.version_number in (SELECT max(MRM.version_number)
2085 -- FROM ahl_mr_headers_app_v MRM
2086 -- WHERE mrm.title = mr.title
2087 -- AND SYSDATE between trunc(MRM.effective_from)
2088 -- AND trunc(nvl(MRM.effective_to,SYSDATE+1))
2089 -- AND mr_status_code='COMPLETE'
2090 -- )
2091 -- )
2092 );
2093
2094 -- check for path position based effectivities for MR
2095 CURSOR relationship_mr_csr(p_mr_header_id IN NUMBER) IS
2096 SELECT 'x' from dual
2097 WHERE exists (select 'x'
2098 from ahl_mr_effectivities
2099 where mr_header_id = p_mr_header_id
2100 and relationship_id is not null);
2101
2102
2103 /* Not used
2104 amsriniv Bug 6971165 : To improve performance, instead of calling the cursor for every combination of
2105 c_instance_id and c_position_id, we call the below cursor only when instance changes. Then, to validate
2106 position_id, we iterate through the output of the cursor for the corresponding instance.
2107
2108 -- validate patch position Id.
2109 CURSOR relationship_csr(c_instance_id IN NUMBER) IS
2110 select position_id
2111 from ahl_applicable_instances
2112 where csi_item_instance_id = c_instance_id;
2113 */
2114
2115 /*
2116 amsriniv Bug 6971165 : To improve performance, replicating the logic of CHECK_EFFECTIVITY_DETAILS in this
2117 procedure so that the below two cursors are invoked only when required. Previously, the function
2118 CHECK_EFFECTIVITY_DETAILS was being called for every combination of mr_effectivity and instance_id. Now, the
2119 get_inst_att cursor is called once for an instance and get_effect_details is called only if the MR validity still
2120 holds.
2121 */
2122 CURSOR get_effect_details(c_mr_effectivity_id NUMBER) IS
2123 SELECT exclude_flag, serial_number_from, serial_number_to, manufacturer_id,
2124 manufacture_date_from, manufacture_date_to, country_code
2125 FROM ahl_mr_effectivity_dtls
2126 WHERE mr_effectivity_id = c_mr_effectivity_id
2127 ORDER BY exclude_flag ASC;
2128
2129 -- added for perf fix 9434441
2130 CURSOR get_effect_ext_details(c_mr_effectivity_id NUMBER) IS
2131 SELECT 'Y'
2132 FROM ahl_mr_effectivity_ext_dtls a
2133 WHERE a.mr_effectivity_id = c_mr_effectivity_id
2134 AND rownum < 2;
2135
2136 /* not used
2137 -- get instance details.
2138 CURSOR get_inst_att(c_item_instance_id NUMBER) IS
2139 SELECT csi.serial_number serial_number ,
2140 to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date ,
2141 'm' manufacturer_id ,
2142 'c' country_code
2143 FROM csi_item_instances csi,
2144 csi_inst_extend_attrib_v ciea1
2145 WHERE csi.instance_id = ciea1.instance_id(+)
2146 AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
2147 AND ciea1.attribute_level(+) = 'GLOBAL'
2148 AND csi.instance_id = c_item_instance_id;
2149 */
2150
2151 /* not used
2152 CURSOR is_position_check_req(c_mr_effectivity_id NUMBER) IS
2153 SELECT 'X'
2154 FROM ahl_mr_effectivities
2155 WHERE mr_effectivity_id = c_mr_effectivity_id
2156 AND inventory_item_id IS NOT NULL;
2157 */
2158
2159
2160 -- to get configuration nodes.
2161 CURSOR get_config_tree_csr ( p_csi_instance_id IN NUMBER) IS
2162 SELECT subject_id
2163 FROM csi_ii_relationships
2164 START WITH object_id = p_csi_instance_id
2165 AND relationship_type_code = 'COMPONENT-OF'
2166 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
2167 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
2168 CONNECT BY PRIOR subject_id = object_id
2169 AND relationship_type_code = 'COMPONENT-OF'
2170 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
2171 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
2172 ORDER BY level;
2173
2174 -- JKJain, NR Analysis and Forecasting
2175 l_fleet_id_tbl nbr_tbl_type;
2176 l_uc_header_id NUMBER;
2177 l_uc_name VARCHAR2(80);
2178
2179 l_mr_header_id_tbl nbr_tbl_type;
2180 l_mr_effectivity_id_tbl nbr_tbl_type;
2181 l_mr_pc_node_id_tbl nbr_tbl_type;
2182 l_instance_id_tbl nbr_tbl_type;
2183 l_position_id_tbl nbr_tbl_type;
2184 l_pc_node_id_tbl nbr_tbl_type; --amsriniv
2185
2186 l_mfg_date_tbl date_tbl_type;
2187 l_serial_num_tbl vchar_tbl_type;
2188 --l_eff_exists_tbl vchar_tbl_type;
2189 l_subj_id_tbl nbr_tbl_type;
2190
2191 l_index number;
2192 --l_pc_node_inst_id number; --amsriniv
2193 l_valid_mr_flag varchar2(1);
2194 l_junk varchar2(1);
2195 l_rows_count number := 0; --amsriniv Bug 6971165
2196
2197 l_buffer_limit number := 1000;
2198
2199 -- dummy values as manufacturer and country code are not supported by
2200 -- the application.
2201 l_inst_manufacturer_id VARCHAR2(1) := 'm';
2202 l_inst_country_code VARCHAR2(1) := 'c';
2203
2204 l_path_posn_flag BOOLEAN;
2205
2206 l_process_loop NUMBER := 0;
2207 -- l_process_loop indicates the processing stage
2208 -- = 1: processing effectivities based on inventory items for input instance
2209 -- = 2: processing effectivities based on inventory items for config components.
2210 -- = 3: processing effectivities based on MC positions for input instance and
2211 -- components. Note component level details are populated in temp table
2212 -- only if the p_components_flag = 'Y'
2213
2214 l_prev_effectivity_id NUMBER;
2215 l_prev_mr_header_id NUMBER;
2216
2217
2218 --define record type to hold effectivity details.
2219 TYPE eff_dtl_rectype IS RECORD (
2220 eflag_tbl vchar_tbl_type,
2221 srl_from_tbl vchar_tbl_type,
2222 srl_to_tbl vchar_tbl_type,
2223 mID_tbl nbr_tbl_type,
2224 mdate_from_tbl date_tbl_type,
2225 mdate_to_tbl date_tbl_type,
2226 c_code_tbl vchar_tbl_type
2227 );
2228
2229 eff_dtl_rec eff_dtl_rectype;
2230
2231 l_extn_exists_flag VARCHAR2(3);
2232
2233 -- added to fix perf bug# 9434441
2234 CURSOR appl_inst_exists_csr IS
2235 SELECT 'x'
2236 FROM dual
2237 WHERE EXISTS (select 'x' from ahl_applicable_instances);
2238
2239 l_appl_inst_exists_flag BOOLEAN;
2240
2241 -- added to fix perf bug# 13629335 to support bulk insert into ahl_applicable_mrs.
2242 l_bulk_mr_hdr_id_tbl nbr_tbl_type;
2243 l_bulk_mr_eff_id_tbl nbr_tbl_type;
2244 l_bulk_csi_ii_id_tbl nbr_tbl_type;
2245 l_bulk_repetitive_flag_tbl vchar_tbl_type;
2246 l_bulk_show_repetitive_tbl vchar_tbl_type;
2247 --l_bulk_preceding_mr_hdr_id_tbl nbr_tbl_type;
2248 l_bulk_copy_accpsh_flag_tbl vchar_tbl_type;
2249 l_bulk_implement_status_tbl vchar_tbl_type;
2250 l_bulk_descendent_count_tbl nbr_tbl_type;
2251 l_bulk_reln_code_tbl vchar_tbl_type;
2252 l_bulk_start_mr_hdr_tbl nbr_tbl_type;
2253 l_bulk_seq_num_tbl nbr_tbl_type;
2254
2255 l_bulk_insert_idx NUMBER := 0;
2256
2257 -- Added for SB effectivity enh.
2258 -- Get all parent terminating MRs
2259 CURSOR get_terminating_mr_csr(c_mr_header_id IN NUMBER) IS
2260 SELECT mr_header_id
2261 FROM ahl_mr_relationships
2262 WHERE related_mr_header_id = c_mr_header_id
2263 AND relationship_code = 'TERMINATES'; -- effective date and complete validation
2264
2265 l_terminate_flag VARCHAR2(1) := 'N';
2266 l_accom_ue_id NUMBER;
2267 get_terminating_mr_rec get_terminating_mr_csr%ROWTYPE;
2268 l_dummy_date DATE;
2269 l_dummy_boolean BOOLEAN;
2270 l_dummy_varchar VARCHAR2(30);
2271 -- END for SB Effectivity
2272
2273 BEGIN
2274 SAVEPOINT GET_APPLICABLE_MRS_PVT;
2275
2276 IF l_debug = 'Y' THEN
2277 AHL_DEBUG_PUB.enable_debug;
2278 AHL_DEBUG_PUB.debug('Begin private API: AHL_FMP_PVT.GET_APPLICABLE_MRS', l_debug_prefix);
2279 AHL_DEBUG_PUB.debug('Input p_item_instance_id:' || p_item_instance_id, l_debug_prefix);
2280 AHL_DEBUG_PUB.debug('Input p_mr_header_id:' || p_mr_header_id, l_debug_prefix);
2281 AHL_DEBUG_PUB.debug('Input p_components_flag:' || p_components_flag, l_debug_prefix);
2282 AHL_DEBUG_PUB.debug('Input p_include_doNotImplmt:' || p_include_doNotImplmt, l_debug_prefix);
2283 AHL_DEBUG_PUB.debug('Input p_visit_type_code:' || p_visit_type_code, l_debug_prefix);
2284 END IF;
2285
2286 IF FND_API.to_boolean(p_init_msg_list) THEN
2287 FND_MSG_PUB.initialize;
2288 END IF;
2289
2290 x_return_status := 'S';
2291
2292 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version,
2293 l_api_name, G_PKG_NAME)
2294 THEN
2295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2296 END IF;
2297
2298 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL)
2299 THEN
2300 -- validate input instance.
2301 OPEN check_instance_exists(p_item_instance_id);
2302 FETCH check_instance_exists INTO l_item_instance_id;
2303 IF check_instance_exists%NOTFOUND THEN
2304 CLOSE check_instance_exists;
2305 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_ITEM_INSTANCE');
2306 FND_MESSAGE.SET_TOKEN('INSTANCE',p_item_instance_id);
2307 FND_MSG_PUB.ADD;
2308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2309 END IF;
2310 CLOSE check_instance_exists;
2311 END IF;
2312
2313 IF l_debug = 'Y' THEN
2314 AHL_DEBUG_PUB.debug('After instance validation:' || p_item_instance_id, l_debug_prefix);
2315 END IF;
2316
2317 /* -- commenting as this is not required.
2318 -- get root instance.
2319 OPEN csi_root_instance_csr(p_item_instance_id);
2320 FETCH csi_root_instance_csr INTO l_root_instance_id;
2321 IF (csi_root_instance_csr%NOTFOUND) THEN
2322 l_root_instance_id := p_item_instance_id;
2323 END IF;
2324 CLOSE csi_root_instance_csr;
2325
2326 -- get uc header ID.
2327 OPEN uc_top_inst(l_root_instance_id);
2328 FETCH uc_top_inst INTO l_uc_header_id;
2329 CLOSE uc_top_inst;
2330 */
2331
2332
2333 -- start processing. First populate temp table ahl_applicable_instances by calling
2334 -- MC api to map instance (and components) to path positions.
2335
2336 IF l_debug = 'Y' THEN
2337 AHL_DEBUG_PUB.debug('Start Processing..', l_debug_prefix);
2338 END IF;
2339
2340 -- initialize temp table.
2341 -- added to fix bug# 9434441.
2342 DELETE FROM AHL_CONFIG_COMPONENTS;
2343
2344 -- populate temp table with config components
2345 --IF (p_components_flag = 'Y') THEN -- commented out as part of fix for bug# 9434441 as the pc_node_id validation needs the configuration to be populated.
2346 IF l_debug = 'Y' THEN
2347 AHL_DEBUG_PUB.debug('populating Config components..', l_debug_prefix);
2348 END IF;
2349 Populate_Config_Components(p_item_instance_id);
2350 --END IF;
2351
2352 l_path_posn_flag := FALSE;
2353
2354 IF (p_visit_type_code IS NOT NULL) THEN
2355 -- check if effectivities exist with path positions for visit type.
2356 OPEN relationship_vtype_csr(p_visit_type_code);
2357 FETCH relationship_vtype_csr INTO l_junk;
2358 IF (relationship_vtype_csr%FOUND) THEN
2359 l_path_posn_flag := TRUE;
2360 END IF;
2361 CLOSE relationship_vtype_csr;
2362
2363 ELSIF (p_mr_header_id IS NOT NULL) THEN
2364 -- check if effectivities exist with path positions for mr_header_id.
2365 OPEN relationship_mr_csr(p_mr_header_id);
2366 FETCH relationship_mr_csr INTO l_junk;
2367 IF (relationship_mr_csr%FOUND) THEN
2368 l_path_posn_flag := TRUE;
2369 END IF;
2370 CLOSE relationship_mr_csr;
2371
2372 ELSE
2373 /* commented to fix perf bug# 10415043
2374 -- check if any effectivites exist with path positions.
2375 OPEN relationship_csr;
2376 FETCH relationship_csr INTO l_junk;
2377 IF (relationship_csr%FOUND) THEN
2378 l_path_posn_flag := TRUE;
2379 END IF;
2380 CLOSE relationship_csr;
2381 */
2382 -- set l_path_posn_flag to TRUE always - added fix for perf bug# 10415043
2383 l_path_posn_flag := TRUE;
2384 END IF;
2385
2386 -- fix for perf bug# 9434441
2387 -- initialize flag that indicate that ahl_applicable_instances has at least one row.
2388 l_appl_inst_exists_flag := FALSE;
2389
2390
2391 IF (l_path_posn_flag) THEN
2392
2393 IF l_debug = 'Y' THEN
2394 AHL_DEBUG_PUB.debug('Processing MC Relationships..', l_debug_prefix);
2395 END IF;
2396
2397 DELETE FROM ahl_applicable_instances;
2398 -- for input instance.
2399 AHL_MC_PATH_POSITION_PVT.map_instance_to_positions
2400 (
2401 p_api_version => 1.0,
2402 p_init_msg_list => fnd_api.g_false,
2403 p_commit => fnd_api.g_false,
2404 p_validation_level => p_validation_level,
2405 p_csi_item_instance_id => p_item_instance_id,
2406 x_return_status => l_return_status,
2407 x_msg_count => l_msg_count,
2408 x_msg_data => x_msg_data
2409 );
2410
2411 -- Raise errors if exceptions occur
2412 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2413 RAISE FND_API.G_EXC_ERROR;
2414 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2416 END IF;
2417
2418 IF (p_components_flag = 'Y') THEN
2419 IF l_debug = 'Y' THEN
2420 AHL_DEBUG_PUB.debug('Processing Component MC Relationships..', l_debug_prefix);
2421 END IF;
2422
2423 OPEN get_config_tree_csr(p_item_instance_id);
2424 LOOP
2425 FETCH get_config_tree_csr BULK COLLECT INTO l_subj_id_tbl LIMIT l_buffer_limit;
2426 EXIT WHEN l_subj_id_tbl.COUNT = 0;
2427
2428 FOR j IN l_subj_id_tbl.FIRST..l_subj_id_tbl.LAST LOOP
2429 AHL_MC_PATH_POSITION_PVT.map_instance_to_positions
2430 (
2431 p_api_version => 1.0,
2432 p_init_msg_list => fnd_api.g_false,
2433 p_commit => fnd_api.g_false,
2434 p_validation_level => p_validation_level,
2435 p_csi_item_instance_id => l_subj_id_tbl(j),
2436 x_return_status => l_return_status,
2437 x_msg_count => l_msg_count,
2438 x_msg_data => x_msg_data
2439 );
2440 -- Raise errors if exceptions occur
2441 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2442 RAISE FND_API.G_EXC_ERROR;
2443 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2445 END IF;
2446
2447 END LOOP;
2448 l_subj_id_tbl.DELETE;
2449 END LOOP;
2450 CLOSE get_config_tree_csr;
2451
2452 END IF; -- p_components_flag
2453
2454 -- check if any data is populated in temp table.
2455 -- fix for perf bug# 9434441
2456 OPEN appl_inst_exists_csr;
2457 FETCH appl_inst_exists_csr INTO l_junk;
2458 IF (appl_inst_exists_csr%FOUND) THEN
2459 l_appl_inst_exists_flag := TRUE;
2460 END IF;
2461 CLOSE appl_inst_exists_csr;
2462
2463 END IF; -- l_path_posn_flag
2464
2465 IF l_debug = 'Y' THEN
2466 AHL_DEBUG_PUB.debug('Processing Inventory Items..', l_debug_prefix);
2467 END IF;
2468
2469 l_index := 1;
2470 l_bulk_insert_idx := 0;
2471
2472 -- initialize. Tracks if MC api to get path position details has been called or not.
2473 -- l_pc_node_inst_id := NULL; --amsriniv
2474
2475 -- indicates processing stage.
2476 l_process_loop := 1; -- process inv items for input instance.
2477
2478 -- Loop based on processing stage
2479 LOOP
2480 -- Get effectivities for the mr and instance.
2481 IF (l_process_loop = 1) THEN
2482 -- get eff for top node
2483 IF (p_visit_type_code IS NULL) THEN
2484 IF (p_mr_header_id IS NULL) THEN
2485 OPEN get_inst_mr_inv_csr(p_item_instance_id,G_APPLN_USAGE);
2486 ELSE
2487 OPEN get_inst_mrID_inv_csr(p_item_instance_id,p_mr_header_id,G_APPLN_USAGE);
2488 END IF;
2489 ELSE
2490 OPEN get_inst_vst_inv_csr(p_item_instance_id, p_visit_type_code);
2491 END IF;
2492 ELSIF (l_process_loop = 2) THEN
2493 -- get eff for components.
2494 IF (p_visit_type_code IS NULL) THEN
2495 IF (p_mr_header_id IS NULL) THEN
2496 OPEN get_comp_mr_inv_csr(p_item_instance_id, G_APPLN_USAGE);
2497 ELSE
2498 OPEN get_comp_mrID_inv_csr(p_item_instance_id, p_mr_header_id, G_APPLN_USAGE);
2499 END IF;
2500 ELSE
2501 OPEN get_comp_vst_inv_csr(p_item_instance_id, p_visit_type_code);
2502 END IF;
2503 IF l_debug = 'Y' THEN
2504 AHL_DEBUG_PUB.debug('Processing Component Effectivities based on Inventory Items..', l_debug_prefix);
2505 END IF;
2506
2507 ELSIF (l_process_loop = 3) THEN
2508 -- get eff based on positions
2509 IF (p_visit_type_code IS NULL) THEN
2510 IF (p_mr_header_id IS NULL) THEN
2511 OPEN get_posn_mr_csr(G_APPLN_USAGE);
2512 ELSE
2513 OPEN get_posn_mrID_csr(p_mr_header_id, G_APPLN_USAGE);
2514 END IF;
2515 ELSE
2516 OPEN get_posn_vst_csr(p_visit_type_code);
2517 END IF;
2518
2519 IF l_debug = 'Y' THEN
2520 AHL_DEBUG_PUB.debug('Processing Effectivities based on MC Positions..', l_debug_prefix);
2521 END IF;
2522 END IF;
2523
2524 LOOP
2525 -- fetch effectivity data and process.
2526 -- JKJain, NR Analysis and Forecasting : Added table l_fleet_id_tbl
2527 IF (l_process_loop = 1) THEN
2528 IF (p_visit_type_code IS NULL) THEN
2529 IF (p_mr_header_id IS NULL) THEN
2530 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,l_fleet_id_tbl,
2531 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --,l_eff_exists_tbl
2532 LIMIT l_buffer_limit;
2533 ELSE
2534 FETCH get_inst_mrID_inv_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,l_fleet_id_tbl,
2535 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --,l_eff_exists_tbl
2536 LIMIT l_buffer_limit;
2537 END IF;
2538
2539 ELSE
2540
2541 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,l_fleet_id_tbl,
2542 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --,l_eff_exists_tbl
2543 LIMIT l_buffer_limit;
2544 END IF;
2545 ELSIF (l_process_loop = 2) THEN
2546 IF (p_visit_type_code IS NULL) THEN
2547 IF (p_mr_header_id IS NULL) THEN
2548 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,l_fleet_id_tbl,
2549 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
2550 LIMIT l_buffer_limit;
2551 ELSE
2552 FETCH get_comp_mrID_inv_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,l_fleet_id_tbl,
2553 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
2554 LIMIT l_buffer_limit;
2555 END IF;
2556
2557 ELSE
2558
2559 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,l_fleet_id_tbl,
2560 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
2561 LIMIT l_buffer_limit;
2562 END IF;
2563 ELSIF (l_process_loop = 3) THEN
2564 IF (p_visit_type_code IS NULL) THEN
2565 IF (p_mr_header_id IS NULL) THEN
2566 FETCH get_posn_mr_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,l_fleet_id_tbl,
2567 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
2568 LIMIT l_buffer_limit;
2569 ELSE
2570 FETCH get_posn_mrID_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,l_fleet_id_tbl,
2571 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
2572 LIMIT l_buffer_limit;
2573 END IF;
2574 ELSE
2575
2576 FETCH get_posn_vst_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,l_fleet_id_tbl,
2577 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
2578 LIMIT l_buffer_limit;
2579 END IF;
2580
2581 END IF; -- l_process_loop
2582
2583 EXIT WHEN (l_mr_header_id_tbl.count = 0);
2584
2585 IF l_debug = 'Y' THEN
2586 AHL_DEBUG_PUB.debug('Count of l_mr_header_id_tbl:' || l_mr_header_id_tbl.count, l_debug_prefix);
2587 END IF;
2588
2589 -- process retrieved effectivity IDs.
2590 FOR i IN l_mr_effectivity_id_tbl.FIRST..l_mr_effectivity_id_tbl.LAST LOOP
2591
2592 /*
2593 IF l_debug = 'Y' THEN
2594 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));
2595 END IF;
2596 */
2597
2598 -- to begin with, set effectivity as valid.
2599 l_valid_mr_flag := 'Y';
2600
2601 --DBMS_OUTPUT.PUT_LINE('API2: The number of MR before checking effectivity details is: '||l_appli_mr_tbl.COUNT||' After loop '||i);
2602
2603 -- 24 Oct 08: performance changes to reduce executions on effect details query.
2604 --IF (l_eff_exists_tbl(i) = 'Y') THEN
2605 IF(l_prev_effectivity_id IS NULL OR l_prev_effectivity_id <> l_mr_effectivity_id_tbl(i)) THEN
2606 -- read effectivity details
2607 OPEN get_effect_details(l_mr_effectivity_id_tbl(i));
2608 FETCH get_effect_details BULK COLLECT INTO eff_dtl_rec.eflag_tbl, eff_dtl_rec.srl_from_tbl, eff_dtl_rec.srl_to_tbl,
2609 eff_dtl_rec.mID_tbl, eff_dtl_rec.mdate_from_tbl, eff_dtl_rec.mdate_to_tbl, eff_dtl_rec.c_code_tbl;
2610 CLOSE get_effect_details;
2611
2612 -- added for eff ext details.
2613 OPEN get_effect_ext_details(l_mr_effectivity_id_tbl(i));
2614 FETCH get_effect_ext_details INTO l_extn_exists_flag;
2615 IF (get_effect_ext_details%NOTFOUND) THEN
2616 l_extn_exists_flag := 'N';
2617 END IF;
2618 CLOSE get_effect_ext_details;
2619
2620 l_prev_effectivity_id := l_mr_effectivity_id_tbl(i);
2621 END IF;
2622 IF (eff_dtl_rec.eflag_tbl.count > 0) THEN
2623
2624 --FOR l_effect_dtl IN get_effect_details(l_mr_effectivity_id_tbl(i)) LOOP
2625 FOR j IN eff_dtl_rec.eflag_tbl.FIRST..eff_dtl_rec.eflag_tbl.LAST LOOP
2626 --l_rows_count := get_effect_details%ROWCOUNT;
2627 IF eff_dtl_rec.eflag_tbl(j) = 'N' THEN
2628 IF (check_sn_inside(l_serial_num_tbl(i), eff_dtl_rec.srl_from_tbl(j), eff_dtl_rec.srl_to_tbl(j)) AND
2629 (eff_dtl_rec.mID_tbl(j) IS NULL OR eff_dtl_rec.mID_tbl(j) = l_inst_manufacturer_id) AND
2630 (eff_dtl_rec.mdate_from_tbl(j) IS NULL OR eff_dtl_rec.mdate_from_tbl(j) <= l_mfg_date_tbl(i)) AND
2631 (eff_dtl_rec.mdate_to_tbl(j) IS NULL OR eff_dtl_rec.mdate_to_tbl(j) >= l_mfg_date_tbl(i)) AND
2632 (eff_dtl_rec.c_code_tbl(j) IS NULL OR eff_dtl_rec.c_code_tbl(j) = l_inst_country_code)) THEN
2633 l_valid_mr_flag := 'Y';
2634 EXIT;
2635 ELSE
2636 l_valid_mr_flag := 'N';
2637 END IF;
2638 ELSE
2639 IF (check_sn_outside(l_serial_num_tbl(i), eff_dtl_rec.srl_from_tbl(j), eff_dtl_rec.srl_to_tbl(j)) AND
2640 (eff_dtl_rec.mID_tbl(j) IS NULL OR eff_dtl_rec.mID_tbl(j) = l_inst_manufacturer_id) AND
2641 (eff_dtl_rec.mdate_from_tbl(j) IS NULL OR eff_dtl_rec.mdate_from_tbl(j) <= l_mfg_date_tbl(i)) AND
2642 (eff_dtl_rec.mdate_to_tbl(j) IS NULL OR eff_dtl_rec.mdate_to_tbl(j) >= l_mfg_date_tbl(i)) AND
2643 (eff_dtl_rec.c_code_tbl(j) IS NULL OR eff_dtl_rec.c_code_tbl(j) = l_inst_country_code)) THEN
2644 l_valid_mr_flag := 'Y';
2645 ELSE
2646 l_valid_mr_flag := 'N';
2647 EXIT;
2648 END IF;
2649 END IF;
2650 END LOOP;
2651
2652 END IF; -- eff_dtl_rec.eflag_tbl.count > 0
2653 --END IF; -- l_eff_exists_tbl(i)
2654
2655 -- added checks l_extn_exists_flag and l_valid_mr_flag to fix bug# 9434441 to filter number of calls to check_effectivity_ext_details.
2656 IF (l_valid_mr_flag = 'Y') AND (l_extn_exists_flag = 'Y') AND (NOT CHECK_EFFECTIVITY_EXT_DETAILS(l_instance_id_tbl(i),l_mr_effectivity_id_tbl(i)))
2657 THEN
2658 l_valid_mr_flag := 'N';
2659 END IF;
2660
2661 -- 24 Oct 08: reverted code changes to chk for all pairs of c_instance_id and c_pc_node_id due to sorting change.
2662 -- Eff rows are now processed by effectivity ID and mr header ID.
2663 /*amsriniv. Bug 6767803. Rather than executing the below query for all pairs of c_instance_id and c_pc_node_id, to
2664 save on performance, we execute validate_pc_node_csr only when instance changes. We BULK COLLECT the PC_NODE_IDs
2665 into a table and iterate throught it whehn instance is unchanged.
2666 */
2667 --amsriniv. Begin
2668 IF (l_valid_mr_flag = 'Y' AND l_mr_pc_node_id_tbl(i) IS NOT NULL) THEN
2669 --l_valid_mr_flag := 'N';
2670 /*
2671 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
2672 l_pc_node_id_tbl.delete;
2673 */
2674 -- check for units first.
2675 OPEN validate_unit_node_csr(l_instance_id_tbl(i), l_mr_pc_node_id_tbl(i));
2676 FETCH validate_unit_node_csr INTO l_junk;
2677 IF (validate_unit_node_csr%FOUND) THEN
2678 l_valid_mr_flag := 'Y';
2679 ELSE
2680 OPEN validate_itm_node_csr(l_instance_id_tbl(i), l_mr_pc_node_id_tbl(i));
2681 FETCH validate_itm_node_csr INTO l_junk;
2682 IF (validate_itm_node_csr%NOTFOUND) THEN
2683 l_valid_mr_flag := 'N';
2684 END IF;
2685 CLOSE validate_itm_node_csr;
2686 END IF;
2687 CLOSE validate_unit_node_csr;
2688 --l_pc_node_inst_id := l_instance_id_tbl(i);
2689 /*
2690 END IF;
2691 IF (l_pc_node_id_tbl.COUNT > 0) THEN
2692 FOR j IN l_pc_node_id_tbl.FIRST..l_pc_node_id_tbl.LAST LOOP
2693 IF (l_pc_node_id_tbl(j) = l_mr_pc_node_id_tbl(i)) THEN
2694 l_valid_mr_flag := 'Y';
2695 EXIT;
2696 END IF;
2697 END LOOP;
2698 END IF;
2699 */
2700 END IF;
2701 --amsriniv End
2702
2703 -- JKJain, NR Analysis and Forecasting
2704 -- Considering Fleet Effectivity
2705 IF (l_valid_mr_flag = 'Y' AND p_consider_fleet_flag = 'Y' AND l_fleet_id_tbl(i) IS NOT NULL) THEN
2706
2707 get_ucHeader(p_item_instance_id => l_instance_id_tbl(i),
2708 x_ucHeaderID => l_uc_header_id,
2709 x_unitName => l_uc_name);
2710
2711 IF l_debug = 'Y' THEN
2712 AHL_DEBUG_PUB.debug('Considering Fleet Effectivity for fleet = ' || l_fleet_id_tbl(i)|| ' and UC = ' || l_uc_header_id || ' and p_flt_range_calc_date = ' || p_flt_range_calc_date);
2713 END IF;
2714
2715 IF(l_fleet_id_tbl(i) <> nvl(AHL_UMP_ProcessUnit_PVT.get_fleet_from_unit_asso(l_uc_header_id,p_flt_range_calc_date,null),-1)) THEN
2716 l_valid_mr_flag := 'N';
2717 END IF;
2718 END IF;
2719 -- End changes for NR Analysis and Forecasting
2720
2721 -- apattark Changes for SB start
2722 l_terminate_flag := 'N';
2723 FOR get_terminating_mr_rec IN get_terminating_mr_csr(l_mr_header_id_tbl(i))
2724 LOOP
2725 IF l_debug = 'Y' THEN
2726 AHL_DEBUG_PUB.debug('MR ID:Valid Flag:Term-MR:' || l_mr_header_id_tbl(i) || ':' || l_valid_mr_flag || ':' || get_terminating_mr_rec.mr_header_id, l_debug_prefix);
2727 END IF;
2728 ahl_ump_util_pkg.get_last_accomplishment(p_csi_item_instance_id => l_instance_id_tbl(i),
2729 p_mr_header_id => get_terminating_mr_rec.mr_header_id,
2730 x_unit_effectivity_id => l_accom_ue_id,
2731 x_accomplishment_date => l_dummy_date,
2732 x_deferral_flag => l_dummy_boolean,
2733 x_status_code => l_dummy_varchar,
2734 x_return_val => l_dummy_boolean
2735 );
2736 IF (l_accom_ue_id IS NULL) THEN
2737 l_terminate_flag := 'N';
2738 EXIT;
2739 ELSE
2740 l_terminate_flag := 'Y';
2741 END IF;
2742 END LOOP;
2743
2744 IF l_terminate_flag = 'Y' THEN
2745 l_valid_mr_flag := 'N';
2746 END IF;
2747
2748 IF l_debug = 'Y' THEN
2749 AHL_DEBUG_PUB.debug('MR ID:EFF ID:PC NODE ID:INST ID:Valid Flag:' || l_mr_header_id_tbl(i) || ':' || l_mr_effectivity_id_tbl(i) || ':' ||
2750 l_mr_pc_node_id_tbl(i) || ':' || l_instance_id_tbl(i) || ':' || l_valid_mr_flag || 'terminate flag' || l_terminate_flag, l_debug_prefix);
2751 END IF;
2752 -- apattark Changes for SB end
2753
2754 -- add row to x_applicable_mr_tbl
2755 IF (l_valid_mr_flag = 'Y') THEN
2756 --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||';');
2757 -- read MR details only once.
2758 IF (l_prev_mr_header_id IS NULL OR l_prev_mr_header_id <> l_mr_header_id_tbl(i)) THEN
2759 OPEN get_mr_attri(l_mr_header_id_tbl(i));
2760 FETCH get_mr_attri INTO l_get_mr_attri;
2761 IF get_mr_attri%NOTFOUND THEN
2762 CLOSE get_mr_attri;
2763 FND_MESSAGE.set_name('AHL','AHL_FMP_INVALID_MR');
2764 FND_MSG_PUB.add;
2765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2766 ELSE
2767 l_prev_mr_header_id := l_mr_header_id_tbl(i);
2768 CLOSE get_mr_attri;
2769 END IF; -- get_mr_attri%NOTFOUND
2770 END IF; -- l_prev_mr_header_id
2771
2772 IF ((p_include_doNotImplmt <> 'N') OR
2773 (l_get_mr_attri.implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT')) THEN
2774 IF (p_insert_into_tmp_tbl = 'N') OR (p_insert_into_tmp_tbl IS NULL) THEN
2775 x_applicable_mr_tbl(l_index).mr_header_id := l_mr_header_id_tbl(i);
2776 x_applicable_mr_tbl(l_index).mr_effectivity_id := l_mr_effectivity_id_tbl(i);
2777 x_applicable_mr_tbl(l_index).item_instance_id := l_instance_id_tbl(i);
2778 x_applicable_mr_tbl(l_index).repetitive_flag := l_get_mr_attri.repetitive_flag;
2779 x_applicable_mr_tbl(l_index).show_repetitive_code := l_get_mr_attri.show_repetitive_code;
2780 -- commented for SB Enh
2781 --x_applicable_mr_tbl(l_index).preceding_mr_header_id := l_get_mr_attri.preceding_mr_header_id;
2782 x_applicable_mr_tbl(l_index).preceding_mr_header_id := null;
2783 x_applicable_mr_tbl(l_index).copy_accomplishment_flag := l_get_mr_attri.copy_accomplishment_flag;
2784 x_applicable_mr_tbl(l_index).implement_status_code := l_get_mr_attri.implement_status_code;
2785 x_applicable_mr_tbl(l_index).descendent_count := l_get_mr_attri.descendent_count;
2786 x_applicable_mr_tbl(l_index).relationship_code := l_get_mr_attri.relationship_code;
2787 x_applicable_mr_tbl(l_index).start_mr_header_id := l_get_mr_attri.start_mr_header_id;
2788 x_applicable_mr_tbl(l_index).sequence_number := l_get_mr_attri.sequence_number;
2789 IF l_debug = 'Y' THEN
2790 AHL_DEBUG_PUB.debug('AHL_APPLICABLE_MRS Attributes : mr_header_id ' ||
2791 x_applicable_mr_tbl(l_index).mr_header_id || ' mr_effectivity_id ' ||
2792 x_applicable_mr_tbl(l_index).mr_effectivity_id || ' item_instance_id ' ||
2793 x_applicable_mr_tbl(l_index).item_instance_id || ' repetitive_flag ' ||
2794 x_applicable_mr_tbl(l_index).repetitive_flag || ' show_repetitive_code ' ||
2795 x_applicable_mr_tbl(l_index).show_repetitive_code || ' preceding_mr_header_id ' ||
2796 x_applicable_mr_tbl(l_index).preceding_mr_header_id || ' copy_accomplishment_flag ' ||
2797 x_applicable_mr_tbl(l_index).copy_accomplishment_flag || ' implement_status_code ' ||
2798 x_applicable_mr_tbl(l_index).implement_status_code || ' descendent_count ' ||
2799 x_applicable_mr_tbl(l_index).descendent_count || 'relationship code' ||
2800 x_applicable_mr_tbl(l_index).relationship_code || 'start_mr_header_id'||
2801 x_applicable_mr_tbl(l_index).start_mr_header_id, l_debug_prefix);
2802 END IF;
2803 l_index := l_index+1;
2804 ELSIF (p_insert_into_tmp_tbl = 'Y') THEN
2805 l_bulk_insert_idx := l_bulk_insert_idx + 1;
2806 l_bulk_mr_hdr_id_tbl(l_bulk_insert_idx) := l_mr_header_id_tbl(i);
2807 l_bulk_mr_eff_id_tbl(l_bulk_insert_idx) :=l_mr_effectivity_id_tbl(i);
2808 l_bulk_csi_ii_id_tbl(l_bulk_insert_idx) := l_instance_id_tbl(i);
2809 l_bulk_repetitive_flag_tbl(l_bulk_insert_idx) := l_get_mr_attri.repetitive_flag;
2810 --l_bulk_preceding_mr_hdr_id_tbl(l_bulk_insert_idx) := l_get_mr_attri.preceding_mr_header_id;
2811 l_bulk_show_repetitive_tbl(l_bulk_insert_idx) := l_get_mr_attri.show_repetitive_code;
2812 l_bulk_copy_accpsh_flag_tbl(l_bulk_insert_idx) := l_get_mr_attri.copy_accomplishment_flag;
2813 l_bulk_implement_status_tbl(l_bulk_insert_idx) := l_get_mr_attri.implement_status_code;
2814 l_bulk_descendent_count_tbl(l_bulk_insert_idx) := l_get_mr_attri.descendent_count;
2815 -- added for SB Effect Enh
2816 l_bulk_reln_code_tbl(l_bulk_insert_idx) := l_get_mr_attri.relationship_code;
2817 l_bulk_start_mr_hdr_tbl(l_bulk_insert_idx) := l_get_mr_attri.start_mr_header_id;
2818 l_bulk_seq_num_tbl(l_bulk_insert_idx) := l_get_mr_attri.sequence_number;
2819
2820 IF (l_bulk_insert_idx >= 1000) THEN
2821 -- insert into table ahl_applicable_mrs
2822 FORALL z IN 1..l_bulk_insert_idx
2823
2824 INSERT INTO AHL_APPLICABLE_MRS (
2825 MR_HEADER_ID,
2826 MR_EFFECTIVITY_ID,
2827 CSI_ITEM_INSTANCE_ID,
2828 REPETITIVE_FLAG ,
2829 SHOW_REPETITIVE_CODE,
2830 PRECEDING_MR_HEADER_ID,
2831 COPY_ACCOMPLISHMENT_CODE,
2832 IMPLEMENT_STATUS_CODE,
2833 DESCENDENT_COUNT,
2834 ACCOMPLISH_TRIGGER_TYPE,
2835 START_MR_HEADER_ID,
2836 LOOP_CHAIN_SEQ_NUM,
2837 PROCESS_STATUS_FLAG,
2838 PROCESSING_ORDER
2839 )
2840 values (
2841 l_bulk_mr_hdr_id_tbl(z),
2842 l_bulk_mr_eff_id_tbl(z),
2843 l_bulk_csi_ii_id_tbl(z),
2844 l_bulk_repetitive_flag_tbl(z),
2845 l_bulk_show_repetitive_tbl(z),
2846 --l_bulk_preceding_mr_hdr_id_tbl(z),
2847 null,
2848 l_bulk_copy_accpsh_flag_tbl(z),
2849 l_bulk_implement_status_tbl(z),
2850 l_bulk_descendent_count_tbl(z),
2851 l_bulk_reln_code_tbl(z),
2852 l_bulk_start_mr_hdr_tbl(z),
2853 l_bulk_seq_num_tbl(z),
2854 'N', -- default
2855 1 -- default
2856 );
2857
2858 IF l_debug = 'Y' THEN
2859 AHL_DEBUG_PUB.debug('l_bulk_insert_idx:'||l_bulk_insert_idx||': Inserted '||SQL%ROWCOUNT||' rows in AHL_APPLICABLE_MRS');
2860 END IF;
2861
2862 -- reset
2863 l_bulk_insert_idx := 0;
2864
2865 l_bulk_mr_hdr_id_tbl.delete;
2866 l_bulk_mr_eff_id_tbl.delete;
2867 l_bulk_csi_ii_id_tbl.delete;
2868 l_bulk_repetitive_flag_tbl.delete;
2869 l_bulk_show_repetitive_tbl.delete;
2870 --l_bulk_preceding_mr_hdr_id_tbl.delete;
2871 l_bulk_copy_accpsh_flag_tbl.delete;
2872 l_bulk_implement_status_tbl.delete;
2873 l_bulk_descendent_count_tbl.delete;
2874 l_bulk_reln_code_tbl.delete;
2875 l_bulk_start_mr_hdr_tbl.delete;
2876 l_bulk_seq_num_tbl.delete;
2877 END IF; -- l_bulk_insert_idx
2878
2879 IF l_debug = 'Y' THEN
2880 AHL_DEBUG_PUB.debug('AHL_APPLICABLE_MRS Attributes : mr_header_id ' ||
2881 l_mr_header_id_tbl(i) || ' mr_effectivity_id ' ||
2882 l_mr_effectivity_id_tbl(i) || ' item_instance_id ' ||
2883 l_instance_id_tbl(i) || ' repetitive_flag ' ||
2884 l_get_mr_attri.repetitive_flag || ' show_repetitive_code ' ||
2885 l_get_mr_attri.show_repetitive_code || ' copy_accomplishment_flag ' ||
2886 l_get_mr_attri.copy_accomplishment_flag || ' implement_status_code ' ||
2887 l_get_mr_attri.implement_status_code || ' descendent_count ' ||
2888 l_get_mr_attri.descendent_count || ' relationship code ' ||
2889 l_get_mr_attri.relationship_code || ' start_mr_header_id '||
2890 l_get_mr_attri.start_mr_header_id || ' sequence_number ' ||
2891 l_get_mr_attri.sequence_number, l_debug_prefix);
2892 END IF;
2893
2894 END IF; -- p_insert_into_tmp_tbl
2895
2896 END IF;
2897
2898 END IF; -- l_valid_mr_flag
2899
2900 END LOOP; -- l_mr_effectivity_id_tbl
2901
2902 -- reset tables and get the next batch of mr effectivities.
2903 l_mr_header_id_tbl.delete;
2904 l_mr_effectivity_id_tbl.delete;
2905 l_mr_pc_node_id_tbl.delete;
2906 l_instance_id_tbl.delete;
2907 l_mfg_date_tbl.delete;
2908 l_serial_num_tbl.delete;
2909 --l_eff_exists_tbl.delete;
2910 -- JKJain, NR Analysis and Forecasting
2911 l_fleet_id_tbl.delete;
2912
2913 END LOOP;
2914 -- set l_process_loop value to process next set of rows.
2915 IF (l_process_loop = 1) THEN
2916 IF (get_inst_mr_inv_csr%ISOPEN) THEN
2917 CLOSE get_inst_mr_inv_csr;
2918 ELSIF (get_inst_mrID_inv_csr%ISOPEN) THEN
2919 CLOSE get_inst_mrID_inv_csr;
2920 ELSIF (get_inst_vst_inv_csr%ISOPEN) THEN
2921 CLOSE get_inst_vst_inv_csr;
2922 END IF;
2923
2924 IF (p_components_flag = 'Y') THEN
2925 l_process_loop := 2;
2926 ELSIF (l_appl_inst_exists_flag) THEN
2927 l_process_loop := 3;
2928 ELSE
2929 EXIT;
2930 END IF;
2931
2932 ELSIF (l_process_loop = 2) THEN
2933 IF (get_comp_mr_inv_csr%ISOPEN) THEN
2934 CLOSE get_comp_mr_inv_csr;
2935 ELSIF (get_comp_mrID_inv_csr%ISOPEN) THEN
2936 CLOSE get_comp_mrID_inv_csr;
2937 ELSIF (get_comp_vst_inv_csr%ISOPEN) THEN
2938 CLOSE get_comp_vst_inv_csr;
2939 END IF;
2940
2941 IF (l_appl_inst_exists_flag) THEN
2942 l_process_loop := 3;
2943 ELSE
2944 EXIT;
2945 END IF;
2946
2947 ELSIF (l_process_loop = 3) THEN
2948 IF (get_posn_mr_csr%ISOPEN) THEN
2949 CLOSE get_posn_mr_csr;
2950 ELSIF (get_posn_mrID_csr%ISOPEN) THEN
2951 CLOSE get_posn_mrID_csr;
2952 ELSIF (get_posn_vst_csr%ISOPEN) THEN
2953 CLOSE get_posn_vst_csr;
2954 END IF;
2955
2956 EXIT;
2957
2958 END IF;
2959
2960 END LOOP;
2961
2962 -- Insert any leftover MRs if present.
2963 IF (l_bulk_insert_idx > 0) THEN
2964 -- insert into gtt ahl_applicable_mrs
2965 FORALL z IN 1..l_bulk_insert_idx
2966 INSERT INTO AHL_APPLICABLE_MRS (
2967 MR_HEADER_ID,
2968 MR_EFFECTIVITY_ID,
2969 CSI_ITEM_INSTANCE_ID,
2970 REPETITIVE_FLAG ,
2971 SHOW_REPETITIVE_CODE,
2972 PRECEDING_MR_HEADER_ID,
2973 COPY_ACCOMPLISHMENT_CODE,
2974 IMPLEMENT_STATUS_CODE,
2975 DESCENDENT_COUNT,
2976 ACCOMPLISH_TRIGGER_TYPE,
2977 START_MR_HEADER_ID,
2978 LOOP_CHAIN_SEQ_NUM,
2979 PROCESS_STATUS_FLAG,
2980 PROCESSING_ORDER
2981 )
2982 values (
2983 l_bulk_mr_hdr_id_tbl(z),
2984 l_bulk_mr_eff_id_tbl(z),
2985 l_bulk_csi_ii_id_tbl(z),
2986 l_bulk_repetitive_flag_tbl(z),
2987 l_bulk_show_repetitive_tbl(z),
2988 --l_bulk_preceding_mr_hdr_id_tbl(z),
2989 null,
2990 l_bulk_copy_accpsh_flag_tbl(z),
2991 l_bulk_implement_status_tbl(z),
2992 l_bulk_descendent_count_tbl(z),
2993 l_bulk_reln_code_tbl(z),
2994 l_bulk_start_mr_hdr_tbl(z),
2995 l_bulk_seq_num_tbl(z),
2996 'N', -- default
2997 1 -- default
2998 );
2999
3000 IF l_debug = 'Y' THEN
3001 AHL_DEBUG_PUB.debug('Leftover:l_bulk_insert_idx:'||l_bulk_insert_idx||': Inserted '||SQL%ROWCOUNT||' rows in AHL_APPLICABLE_MRS');
3002 END IF;
3003
3004 END IF; -- l_bulk_insert_idx
3005
3006
3007 DELETE FROM ahl_applicable_instances;
3008
3009 --DBMS_OUTPUT.PUT_LINE('API2: Successfully executed API2!');
3010 IF l_debug = 'Y' THEN
3011 AHL_DEBUG_PUB.debug('End private API: AHL_FMP_PVT.GET_APPLICABLE_MRS', l_debug_prefix);
3012 AHL_DEBUG_PUB.disable_debug;
3013 END IF;
3014
3015
3016 EXCEPTION
3017
3018 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3019 ROLLBACK TO GET_APPLICABLE_MRS_PVT;
3020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3021 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3022 p_count => x_msg_count,
3023 p_data => x_msg_data);
3024 IF l_debug = 'Y' THEN
3025 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
3026 'UNEXPECTED ERROR IN PRIVATE:' );
3027 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_APPLICABLE_MRS', l_debug_prefix);
3028 AHL_DEBUG_PUB.disable_debug;
3029 END IF;
3030
3031 WHEN FND_API.G_EXC_ERROR THEN
3032 ROLLBACK TO GET_APPLICABLE_MRS_PVT;
3033 x_return_status := FND_API.G_RET_STS_ERROR;
3034 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3035 p_count => x_msg_count,
3036 p_data => x_msg_data);
3037 IF l_debug = 'Y' THEN
3038 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
3039 'ERROR IN PRIVATE:' );
3040 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_APPLICABLE_MRS', l_debug_prefix);
3041 AHL_DEBUG_PUB.disable_debug;
3042 END IF;
3043
3044 WHEN OTHERS THEN
3045 ROLLBACK TO GET_APPLICABLE_MRS_PVT;
3046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3047 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3048 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_FMP_PVT',
3049 p_procedure_name => 'GET_APPLICABLE_MRS',
3050 p_error_text => SUBSTRB(SQLERRM,1,240));
3051 END IF;
3052 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3053 p_count => x_msg_count,
3054 p_data => x_msg_data);
3055 IF l_debug = 'Y' THEN
3056 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
3057 'OTHER ERROR IN PRIVATE:' );
3058 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_APPLICABLE_MRS', l_debug_prefix);
3059 AHL_DEBUG_PUB.disable_debug;
3060 END IF;
3061 END GET_APPLICABLE_MRS;
3062
3063 -- Define procedure get_ucHeader, get the unit_config_header_id and unit name for
3064 -- a given item_instance_id
3065 PROCEDURE get_ucHeader (p_item_instance_id IN NUMBER,
3066 x_ucHeaderID OUT NOCOPY NUMBER,
3067 x_unitName OUT NOCOPY VARCHAR2)
3068 IS
3069 -- Get ucHeader for component
3070 CURSOR get_unit_name_com (p_item_instance_id IN NUMBER) IS
3071 SELECT unit_config_header_id, name
3072 FROM ahl_unit_config_headers
3073 WHERE csi_item_instance_id IN ( SELECT object_id
3074 FROM csi_ii_relationships
3075 START WITH subject_id = p_item_instance_id
3076 AND relationship_type_code = 'COMPONENT-OF'
3077 AND sysdate between trunc(nvl(active_start_date,sysdate))
3078 AND trunc(nvl(active_end_date, SYSDATE+1))
3079 CONNECT BY subject_id = PRIOR object_id
3080 AND relationship_type_code = 'COMPONENT-OF'
3081 AND sysdate between trunc(nvl(active_start_date,sysdate))
3082 AND trunc(nvl(active_end_date, SYSDATE+1))
3083 )
3084 AND sysdate between trunc(nvl(active_start_date,sysdate))
3085 AND trunc(nvl(active_end_date, SYSDATE+1));
3086
3087 -- Get ucHeader for top node
3088 CURSOR get_unit_name_top (p_item_instance_id IN NUMBER) IS
3089 SELECT unit_config_header_id, name
3090 FROM ahl_unit_config_headers
3091 WHERE csi_item_instance_id = p_item_instance_id
3092 AND sysdate between trunc(nvl(active_start_date,sysdate))
3093 AND trunc(nvl(active_end_date, SYSDATE+1));
3094
3095 l_get_unit_name_com get_unit_name_com%ROWTYPE;
3096 l_get_unit_name_top get_unit_name_top%ROWTYPE;
3097 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
3098 BEGIN
3099 --Check for top node.
3100 OPEN get_unit_name_top(p_item_instance_id);
3101 FETCH get_unit_name_top INTO l_get_unit_name_top;
3102 IF (get_unit_name_top%NOTFOUND) THEN
3103 -- Check for component.
3104 OPEN get_unit_name_com(p_item_instance_id);
3105 FETCH get_unit_name_com INTO l_get_unit_name_com;
3106 IF (get_unit_name_com%NOTFOUND) THEN
3107 x_ucHeaderID := NULL;
3108 x_unitName := NULL;
3109 ELSE
3110 x_ucHeaderID := l_get_unit_name_com.unit_config_header_id;
3111 x_unitName := l_get_unit_name_com.name;
3112 END IF;
3113 CLOSE get_unit_name_com;
3114 ELSE
3115 x_ucHeaderID := l_get_unit_name_top.unit_config_header_id;
3116 x_unitName := l_get_unit_name_top.name;
3117 END IF;
3118 CLOSE get_unit_name_top;
3119
3120 END get_ucHeader;
3121
3122 -- to get the top instance for a given item_instance_id
3123 FUNCTION get_topInstanceID(p_item_instance_id IN NUMBER) RETURN NUMBER
3124 IS
3125 -- Get top instance for top component
3126 CURSOR get_instance_top (p_item_instance_id IN NUMBER) IS
3127 SELECT A.instance_id
3128 FROM csi_item_instances A
3129 WHERE A.instance_id IN ( SELECT object_id
3130 FROM csi_ii_relationships
3131 START WITH subject_id = p_item_instance_id
3132 AND relationship_type_code = 'COMPONENT-OF'
3133 AND sysdate between trunc(nvl(active_start_date,sysdate))
3134 AND trunc(nvl(active_end_date, SYSDATE+1))
3135 CONNECT BY subject_id = PRIOR object_id
3136 AND relationship_type_code = 'COMPONENT-OF'
3137 AND sysdate between trunc(nvl(active_start_date,sysdate))
3138 AND trunc(nvl(active_end_date, SYSDATE+1))
3139 )
3140 AND sysdate between trunc(nvl(active_start_date,sysdate))
3141 AND trunc(nvl(active_end_date, SYSDATE+1))
3142 AND NOT EXISTS (SELECT 'X'
3143 FROM csi_ii_relationships B
3144 WHERE B.subject_id = A.instance_id
3145 AND relationship_type_code = 'COMPONENT-OF'
3146 AND SYSDATE between trunc(nvl(B.active_start_date,sysdate)) and trunc(NVL(b.active_end_date,sysdate+1))
3147 );
3148
3149 -- Get instance if it is a top component
3150 CURSOR get_instance_com (p_item_instance_id IN NUMBER) IS
3151 SELECT instance_id
3152 FROM csi_item_instances
3153 WHERE instance_id = p_item_instance_id
3154 AND sysdate between trunc(nvl(active_start_date,sysdate))
3155 AND trunc(nvl(active_end_date, SYSDATE+1));
3156
3157 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
3158 l_top_instance_id NUMBER;
3159 BEGIN
3160 --Check for top node.
3161 OPEN get_instance_top(p_item_instance_id);
3162 FETCH get_instance_top INTO l_top_instance_id;
3163 IF (get_instance_top%NOTFOUND) THEN
3164 -- Check for component.
3165 OPEN get_instance_com(p_item_instance_id);
3166 FETCH get_instance_com INTO l_top_instance_id;
3167 IF (get_instance_com%NOTFOUND) THEN
3168 l_top_instance_id := NULL;
3169 END IF;
3170 CLOSE get_instance_com;
3171 END IF;
3172 CLOSE get_instance_top;
3173
3174 RETURN l_top_instance_id;
3175
3176 END get_topInstanceID;
3177
3178 -- Define function COUNT_MR_DESCENDENTS --
3179 FUNCTION COUNT_MR_DESCENDENTS(p_mr_header_id IN NUMBER)
3180 RETURN NUMBER IS
3181 CURSOR get_mr_descendents(c_mr_header_id NUMBER) IS
3182 -- when two group MRs having common MRs are added into another group,
3183 -- common MRs are counted only once. This may cause the larger group
3184 -- MR to be processed later
3185 /*
3186 SELECT --count(distinct related_mr_header_id)
3187 count(related_mr_header_id)
3188 FROM ahl_mr_relationships
3189 WHERE EXISTS (SELECT mr_header_id
3190 FROM ahl_mr_headers_b M -- perf bug 6266738
3191 WHERE mr_header_id = related_mr_header_id
3192 AND mr_status_code = 'COMPLETE'
3193 AND SYSDATE between trunc(effective_from) and trunc(nvl(effective_to,SYSDATE+1))
3194 AND (version_number) in (SELECT max(M1.version_number)
3195 from ahl_mr_headers_b M1
3196 where M1.title = m.title -- perf bug 6266738
3197 AND mr_status_code = 'COMPLETE'
3198 AND SYSDATE between trunc(effective_from) and trunc(nvl(effective_to,SYSDATE+1))
3199 )
3200 )
3201 START WITH mr_header_id = c_mr_header_id
3202 AND relationship_code = 'PARENT'
3203 CONNECT BY mr_header_id = PRIOR related_mr_header_id
3204 AND relationship_code = 'PARENT';
3205 */
3206
3207 SELECT count(amr.related_mr_header_id)
3208 FROM ahl_mr_relationships amr
3209 START WITH amr.mr_header_id = c_mr_header_id
3210 AND amr.relationship_code = 'PARENT'
3211 AND exists (select 'x' from ahl_mr_headers_b mr1
3212 where mr1.mr_header_id = amr.related_mr_header_id
3213 and mr1.version_number = (select max(mr2.version_number)
3214 from ahl_mr_headers_b mr2
3215 where mr2.title = mr1.title
3216 and mr2.mr_status_code = 'COMPLETE'
3217 and SYSDATE between trunc(mr2.effective_from)
3218 and trunc(nvl(mr2.effective_to,SYSDATE+1))
3219 )
3220 )
3221 CONNECT BY amr.mr_header_id = PRIOR amr.related_mr_header_id
3222 AND amr.relationship_code = 'PARENT'
3223 AND exists (select 'x' from ahl_mr_headers_b mr1
3224 where mr1.mr_header_id = amr.related_mr_header_id
3225 and mr1.version_number = (select max(mr2.version_number)
3226 from ahl_mr_headers_b mr2
3227 where mr2.title = mr1.title
3228 and mr2.mr_status_code = 'COMPLETE'
3229 and SYSDATE between trunc(mr2.effective_from)
3230 and trunc(nvl(mr2.effective_to,SYSDATE+1))
3231 )
3232 );
3233
3234 l_count NUMBER;
3235 BEGIN
3236 OPEN get_mr_descendents(p_mr_header_id);
3237 FETCH get_mr_descendents INTO l_count;
3238 CLOSE get_mr_descendents;
3239 RETURN l_count;
3240 END COUNT_MR_DESCENDENTS;
3241
3242 -- 10/27/08: This function is no longer used.
3243 -- This function accepts an instance id and a path position id
3244 -- and checks if the instance matches the path position.
3245 -- The path position id may correspond to a version specific path or
3246 -- a version neutral (various degrees) path. It may represent the entire
3247 -- path or only some of the lower most levels of the path.
3248 -- This returns 'T' if the instance matches the path position and 'F' if it does not.
3249 FUNCTION Instance_Matches_Path_Pos(p_instance_id IN NUMBER,
3250 p_path_position_id IN NUMBER) RETURN VARCHAR2 IS
3251
3252 l_api_name CONSTANT VARCHAR2(30) := 'Instance_Matches_Path_Pos';
3253 l_full_name CONSTANT VARCHAR2(80) := 'ahl.plsql.' || g_pkg_name || '.' || l_api_name;
3254 l_return_value VARCHAR2(1) := 'F';
3255 l_return_status VARCHAR2(1);
3256 l_msg_count NUMBER;
3257 l_msg_data VARCHAR2(2000);
3258 l_inst_path_pos_id NUMBER;
3259 l_inst_encoded_path AHL_MC_PATH_POSITIONS.ENCODED_PATH_POSITION%TYPE;
3260 l_input_encoded_path AHL_MC_PATH_POSITIONS.ENCODED_PATH_POSITION%TYPE;
3261 l_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
3262 l_path_rec AHL_MC_PATH_POSITION_PVT.Path_Position_Rec_Type;
3263 l_unit_csi_id NUMBER;
3264 l_index NUMBER;
3265
3266 --Fetches lowest level info
3267 CURSOR get_last_uc_rec_csr(c_csi_instance_id IN NUMBER) IS
3268 SELECT hdr.mc_id, hdr.version_number, rel.position_key
3269 FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel, csi_ii_relationships csi_ii
3270 WHERE csi_ii.subject_id = c_csi_instance_id
3271 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
3272 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
3273 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
3274 AND TO_NUMBER(CSI_II.POSITION_REFERENCE) = REL.RELATIONSHIP_ID
3275 AND REL.mc_header_id = HDR.mc_header_id;
3276
3277 --Traverse up and fetch all unit instance ids
3278 CURSOR get_unit_instance_csr(c_csi_instance_id IN NUMBER) IS
3279 SELECT csi.object_id
3280 FROM csi_ii_relationships csi
3281 WHERE csi.object_id IN
3282 (SELECT csi_item_instance_id
3283 FROM ahl_unit_config_headers
3284 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
3285 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
3286 )
3287 START WITH csi.subject_id = c_csi_instance_id
3288 AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
3289 AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
3290 AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
3291 CONNECT BY csi.subject_id = PRIOR csi.object_id
3292 AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
3293 AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
3294 AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
3295 AND CSI.POSITION_REFERENCE IS NOT NULL;
3296
3297 --Fetch the unit and unit header info for each instance
3298 CURSOR get_uc_headers_csr(c_csi_instance_id IN NUMBER) IS
3299 SELECT up.parent_mc_id, up.parent_version_number, up.parent_position_key
3300 FROM ahl_uc_header_paths_v up
3301 WHERE up.csi_instance_id = c_csi_instance_id;
3302
3303 CURSOR get_top_unit_inst_csr (c_csi_instance_id IN NUMBER) IS
3304 SELECT hdr.mc_id, hdr.version_number, rel.position_key
3305 FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel, ahl_unit_config_headers uch
3306 WHERE uch.csi_item_instance_id = c_csi_instance_id
3307 AND TRUNC(nvl(uch.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
3308 AND TRUNC(nvl(uch.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
3309 AND hdr.mc_header_id = uch.master_config_id
3310 AND rel.mc_header_id = hdr.mc_header_id
3311 AND rel.parent_relationship_id IS NULL
3312 AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
3313 WHERE CIR.SUBJECT_ID = uch.csi_item_instance_id
3314 AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
3315 AND TRUNC(nvl(CIR.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
3316 AND TRUNC(nvl(CIR.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate));
3317
3318 CURSOR get_encoded_path_csr(c_path_position_id IN NUMBER) IS
3319 SELECT ENCODED_PATH_POSITION
3320 FROM AHL_MC_PATH_POSITIONS
3321 WHERE PATH_POSITION_ID = c_path_position_id;
3322
3323 BEGIN
3324 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3325 fnd_log.string(fnd_log.level_procedure, l_full_name||'.begin',
3326 'At the start of PLSQL function. p_instance_id = ' || p_instance_id ||
3327 ', p_path_position_id = ' || p_path_position_id);
3328 END IF;
3329
3330 IF (p_path_position_id IS NULL) THEN
3331 l_return_value := 'T';
3332 RETURN l_return_value;
3333 END IF;
3334
3335 -- Get the version specific encoded path position for the instance
3336 -- NOTE: The following lines of code are reproduced from AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID
3337 -- However AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID itself cannot be used directly
3338 -- since it also updates the db by creating a path position if it does not exist and this is not
3339 -- acceptable if this function is to be called from a Select statement.
3340 --Fetch the position informations for the instance
3341 OPEN get_last_uc_rec_csr(p_instance_id);
3342 FETCH get_last_uc_rec_csr INTO l_path_rec.mc_id, l_path_rec.version_number, l_path_rec.position_key;
3343 IF (get_last_uc_rec_csr%FOUND) THEN
3344 l_path_tbl(1) := l_path_rec;
3345 --Now fetch the position paths which match at higher levels.
3346 l_index := 0;
3347 --Fetch the header rec info for the instance
3348 OPEN get_unit_instance_csr(p_instance_id);
3349 LOOP
3350 FETCH get_unit_instance_csr INTO l_unit_csi_id;
3351 EXIT WHEN get_unit_instance_csr%NOTFOUND;
3352
3353 OPEN get_uc_headers_csr(l_unit_csi_id);
3354 FETCH get_uc_headers_csr INTO l_path_rec.mc_id, l_path_rec.version_number, l_path_rec.position_key;
3355 CLOSE get_uc_headers_csr;
3356
3357 --Add the path up the tree, decrementing index for each node.
3358 IF (l_path_rec.mc_id is not null AND l_path_rec.position_key is not null) THEN
3359 l_path_tbl(l_index) := l_path_rec;
3360 l_index := l_index - 1;
3361 END IF;
3362 END LOOP;
3363 CLOSE get_unit_instance_csr;
3364 ELSE --if not position node then check if instance is the top unit node
3365 --Fetch the position informations for the unit instance
3366 OPEN get_top_unit_inst_csr(p_instance_id);
3367 FETCH get_top_unit_inst_csr INTO l_path_rec.mc_id, l_path_rec.version_number, l_path_rec.position_key;
3368 IF (get_top_unit_inst_csr%FOUND) THEN
3369 l_path_tbl(1) := l_path_rec;
3370 END IF;
3371 CLOSE get_top_unit_inst_csr;
3372 END IF;
3373 CLOSE get_last_uc_rec_csr;
3374
3375 -- End reproduction from AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID
3376
3377 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3378 fnd_log.string(fnd_log.level_statement, l_full_name,
3379 'l_path_tbl.COUNT = ' || l_path_tbl.COUNT);
3380 END IF;
3381 -- Now use the contents of l_path_tbl to create the encoded path
3382 IF (l_path_tbl.COUNT > 0) THEN
3383 l_inst_encoded_path := '';
3384 FOR i IN l_path_tbl.FIRST..l_path_tbl.LAST LOOP
3385 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;
3386 IF (i < l_path_tbl.LAST) THEN
3387 l_inst_encoded_path := l_inst_encoded_path || '/';
3388 END IF;
3389 END LOOP;
3390 END IF;
3391
3392 -- Get the encoded path for the input path position
3393 OPEN get_encoded_path_csr(c_path_position_id => p_path_position_id);
3394 FETCH get_encoded_path_csr INTO l_input_encoded_path;
3395 CLOSE get_encoded_path_csr;
3396
3397 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3398 fnd_log.string(fnd_log.level_statement, l_full_name,
3399 'l_inst_encoded_path = ' || l_inst_encoded_path ||
3400 ', l_input_encoded_path = ' || l_input_encoded_path);
3401 END IF;
3402
3403 -- See if the path positions match
3404 IF (l_inst_encoded_path LIKE '%' || l_input_encoded_path) THEN
3405 l_return_value := 'T';
3406 END IF;
3407
3408 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3409 fnd_log.string(fnd_log.level_procedure, l_full_name||'.end',
3410 'At the end of PLSQL function. About to return ' || l_return_value);
3411 END IF;
3412 RETURN l_return_value;
3413 END;
3414
3415
3416 -- Define Local Function CHECK_EFFECTIVITY_DETAILS --
3417 FUNCTION CHECK_EFFECTIVITY_DETAILS(
3418 p_item_instance_id IN NUMBER,
3419 p_mr_effectivity_id IN NUMBER
3420 ) RETURN BOOLEAN IS
3421 CURSOR get_effect_detail(c_mr_effectivity_id NUMBER) IS
3422 SELECT exclude_flag, serial_number_from, serial_number_to, manufacturer_id,
3423 manufacture_date_from, manufacture_date_to, country_code
3424 FROM ahl_mr_effectivity_dtls -- perf bug 6266738
3425 WHERE mr_effectivity_id = c_mr_effectivity_id
3426 ORDER BY exclude_flag ASC;
3427
3428 --amsriniv
3429 CURSOR get_inst_attributes(c_item_instance_id NUMBER) IS
3430 SELECT csi.serial_number serial_number ,
3431 to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date ,
3432 'm' manufacturer_id ,
3433 'c' country_code
3434 FROM csi_item_instances csi,
3435 csi_inst_extend_attrib_v ciea1
3436 WHERE csi.instance_id = ciea1.instance_id(+)
3437 AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
3438 AND ciea1.attribute_level(+) = 'GLOBAL'
3439 AND csi.instance_id = c_item_instance_id;
3440
3441 l_inst_dtl get_inst_attributes%ROWTYPE;
3442 match_dtl BOOLEAN := FALSE;
3443 l_rows_count NUMBER := 0;
3444 BEGIN
3445 OPEN get_inst_attributes(p_item_instance_id);
3446 FETCH get_inst_attributes INTO l_inst_dtl;
3447 IF get_inst_attributes%NOTFOUND THEN
3448 CLOSE get_inst_attributes;
3449 FND_MESSAGE.set_name('AHL','AHL_FMP_INVALID_ITEM_INSTANCE');
3450 FND_MESSAGE.set_token('INSTANCE',p_item_instance_id);
3451 FND_MSG_PUB.add;
3452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3453 RETURN match_dtl;
3454 END IF;
3455 CLOSE get_inst_attributes;
3456 --DBMS_OUTPUT.PUT_LINE('In check effectivity detail function and before the loop');
3457 FOR l_effect_dtl IN get_effect_detail(p_mr_effectivity_id) LOOP
3458 l_rows_count := get_effect_detail%ROWCOUNT;
3459 IF l_effect_dtl.exclude_flag = 'N' THEN
3460 --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: '||
3461 --l_effect_dtl.serial_number_to);
3462 IF (check_sn_inside(l_inst_dtl.serial_number, l_effect_dtl.serial_number_from, l_effect_dtl.serial_number_to) AND
3463 -- l_inst_dtl.serial_number >= NVL(l_effect_dtl.serial_number_from, l_inst_dtl.serial_number) AND
3464 -- l_inst_dtl.serial_number <= NVL(l_effect_dtl.serial_number_to, l_inst_dtl.serial_number) AND
3465 (l_effect_dtl.manufacturer_id IS NULL OR l_effect_dtl.manufacturer_id = l_inst_dtl.manufacturer_id) AND
3466 (l_effect_dtl.manufacture_date_from IS NULL OR l_effect_dtl.manufacture_date_from <= l_inst_dtl.mfg_date) AND
3467 (l_effect_dtl.manufacture_date_to IS NULL OR l_effect_dtl.manufacture_date_to >= l_inst_dtl.mfg_date) AND
3468 (l_effect_dtl.country_code IS NULL OR l_effect_dtl.country_code = l_inst_dtl.country_code)) THEN
3469 match_dtl := TRUE;
3470 EXIT;
3471 END IF;
3472 ELSE
3473 --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: '||
3474 --l_effect_dtl.serial_number_to);
3475 IF (check_sn_outside(l_inst_dtl.serial_number, l_effect_dtl.serial_number_from, l_effect_dtl.serial_number_to) AND
3476 -- l_inst_dtl.serial_number < NVL(l_effect_dtl.serial_number_from, l_inst_dtl.serial_number||'A') AND
3477 -- 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
3478 (l_effect_dtl.manufacturer_id IS NULL OR l_effect_dtl.manufacturer_id <> l_inst_dtl.manufacturer_id) AND
3479 (l_effect_dtl.manufacture_date_from IS NULL OR l_effect_dtl.manufacture_date_from > l_inst_dtl.mfg_date) AND
3480 (l_effect_dtl.manufacture_date_to IS NULL OR l_effect_dtl.manufacture_date_to < l_inst_dtl.mfg_date) AND
3481 (l_effect_dtl.country_code IS NULL OR l_effect_dtl.country_code <> l_inst_dtl.country_code)) THEN
3482 match_dtl := TRUE;
3483 ELSE
3484 match_dtl := FALSE;
3485 EXIT;
3486 END IF;
3487 END IF;
3488
3489 END LOOP;
3490 IF l_rows_count = 0 THEN
3491 match_dtl := TRUE;
3492 END IF;
3493 RETURN match_dtl;
3494 END CHECK_EFFECTIVITY_DETAILS;
3495
3496 -- Define Local Function CHECK_EFFECTIVITY_EXT_DETAILS --
3497 FUNCTION CHECK_EFFECTIVITY_EXT_DETAILS(
3498 p_item_instance_id IN NUMBER,
3499 p_mr_effectivity_id IN NUMBER
3500 ) RETURN BOOLEAN IS
3501
3502 -- TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3503 -- TYPE vchar_tbl_type IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
3504 --define record type to hold extended effectivity details.
3505 TYPE eff_ext_dtl_rectype IS RECORD (
3506 eflag_tbl vchar_tbl_type,
3507 rectype_tbl vchar_tbl_type,
3508 owner_id_tbl nbr_tbl_type,
3509 loc_tbl vchar_tbl_type,
3510 a_Code_tbl vchar_tbl_type,
3511 a_Val_tbl vchar_tbl_type
3512 );
3513
3514 eff_ext_dtl_rec eff_ext_dtl_rectype;
3515
3516 CURSOR get_effect_ext_detail(c_mr_effectivity_id NUMBER) IS
3517 SELECT exclude_flag, EFFECT_EXT_DTL_REC_TYPE, OWNER_ID, LOCATION_TYPE_CODE,
3518 CSI_EXT_ATTRIBUTE_CODE, CSI_EXT_ATTRIBUTE_VALUE
3519 FROM AHL_MR_EFFECTIVITY_EXT_DTLS
3520 WHERE mr_effectivity_id = c_mr_effectivity_id
3521 AND EFFECT_EXT_DTL_REC_TYPE IN ('OWNER','LOCATION','CSIEXTATTR')
3522 ORDER BY EFFECT_EXT_DTL_REC_TYPE DESC,exclude_flag ASC; --exteremely improtant this --will break if changed
3523
3524 match_dtl BOOLEAN;
3525 fetch_inst_attr BOOLEAN;
3526 fetch_inst_ext_attr BOOLEAN;
3527
3528
3529 CURSOR get_inst_attributes(c_item_instance_id NUMBER) IS
3530 select CSI.OWNER_PARTY_ID,location_type_code from csi_item_instances CSI
3531 where CSI.instance_id = c_item_instance_id;
3532 l_inst_attr_rec get_inst_attributes%ROWTYPE;
3533
3534 CURSOR get_inst_ext_attributes(c_item_instance_id NUMBER) IS
3535 SELECT ciea1.attribute_code,ciea1.attribute_value
3536 FROM csi_item_instances csi,
3537 csi_inst_extend_attrib_v ciea1
3538 WHERE csi.instance_id = ciea1.instance_id(+)
3539 AND csi.instance_id = c_item_instance_id;
3540
3541 --define record type to hold extended effectivity details.
3542 TYPE inst_ext_dtl_rectype IS RECORD (
3543 a_Code_tbl vchar_tbl_type,
3544 a_Val_tbl vchar_tbl_type
3545 );
3546
3547 inst_ext_dtl_rec eff_ext_dtl_rectype;
3548
3549 BEGIN
3550
3551 match_dtl := TRUE;
3552
3553 fetch_inst_attr := FALSE;
3554 fetch_inst_ext_attr := FALSE;
3555 -- read effectivity details
3556 OPEN get_effect_ext_detail(p_mr_effectivity_id);
3557 FETCH get_effect_ext_detail BULK COLLECT INTO eff_ext_dtl_rec.eflag_tbl,
3558 eff_ext_dtl_rec.rectype_tbl,
3559 eff_ext_dtl_rec.owner_id_tbl,
3560 eff_ext_dtl_rec.loc_tbl,
3561 eff_ext_dtl_rec.a_Code_tbl,
3562 eff_ext_dtl_rec.a_Val_tbl;
3563 CLOSE get_effect_ext_detail;
3564 IF(eff_ext_dtl_rec.eflag_tbl.COUNT = 0)THEN
3565 RETURN match_dtl;
3566 ELSE
3567 FOR i IN eff_ext_dtl_rec.eflag_tbl.FIRST..eff_ext_dtl_rec.eflag_tbl.LAST LOOP
3568 IF(eff_ext_dtl_rec.rectype_tbl(i) = 'OWNER' OR eff_ext_dtl_rec.rectype_tbl(i) = 'LOCATION')THEN
3569 fetch_inst_attr := TRUE;
3570 ELSIF(eff_ext_dtl_rec.rectype_tbl(i) = 'CSIEXTATTR')THEN
3571 fetch_inst_ext_attr := TRUE;
3572 END IF;
3573 END LOOP;
3574 IF(fetch_inst_attr) THEN
3575 OPEN get_inst_attributes(p_item_instance_id);
3576 FETCH get_inst_attributes INTO l_inst_attr_rec.owner_party_id,l_inst_attr_rec.location_type_code;
3577 CLOSE get_inst_attributes;
3578 END IF;
3579
3580 FOR i IN eff_ext_dtl_rec.eflag_tbl.FIRST..eff_ext_dtl_rec.eflag_tbl.LAST LOOP
3581 IF(eff_ext_dtl_rec.rectype_tbl(i) = 'OWNER')THEN
3582 IF(eff_ext_dtl_rec.eflag_tbl(i) = 'N')THEN
3583 match_dtl := FALSE;
3584 IF(l_inst_attr_rec.owner_party_id = eff_ext_dtl_rec.owner_id_tbl(i))THEN
3585 match_dtl := TRUE;
3586 EXIT;
3587 END IF;
3588 ELSE
3589 IF(l_inst_attr_rec.owner_party_id = eff_ext_dtl_rec.owner_id_tbl(i))THEN
3590 match_dtl := FALSE;
3591 EXIT;
3592 END IF;
3593 END IF;
3594 END IF;
3595 END LOOP;
3596 IF (match_dtl = FALSE) THEN
3597 RETURN match_dtl;
3598 END IF;
3599 FOR i IN eff_ext_dtl_rec.eflag_tbl.FIRST..eff_ext_dtl_rec.eflag_tbl.LAST LOOP
3600 IF (eff_ext_dtl_rec.rectype_tbl(i) = 'LOCATION')THEN
3601 IF(eff_ext_dtl_rec.eflag_tbl(i) = 'N')THEN
3602 match_dtl := FALSE;
3603 IF(l_inst_attr_rec.location_type_code = eff_ext_dtl_rec.loc_tbl(i))THEN
3604 match_dtl := TRUE;
3605 EXIT;
3606 END IF;
3607 ELSE
3608 IF(l_inst_attr_rec.location_type_code = eff_ext_dtl_rec.loc_tbl(i))THEN
3609 match_dtl := FALSE;
3610 EXIT;
3611 END IF;
3612 END IF;
3613 END IF;
3614 END LOOP;
3615 IF (match_dtl = FALSE OR fetch_inst_ext_attr = FALSE)THEN
3616 RETURN match_dtl;
3617 END IF;
3618
3619 OPEN get_inst_ext_attributes(p_item_instance_id);
3620 FETCH get_inst_ext_attributes BULK COLLECT INTO
3621 inst_ext_dtl_rec.a_Code_tbl,
3622 inst_ext_dtl_rec.a_Val_tbl;
3623 CLOSE get_inst_ext_attributes;
3624
3625 FOR i IN eff_ext_dtl_rec.eflag_tbl.FIRST..eff_ext_dtl_rec.eflag_tbl.LAST LOOP
3626 IF(eff_ext_dtl_rec.rectype_tbl(i) = 'CSIEXTATTR')THEN
3627
3628 IF(eff_ext_dtl_rec.eflag_tbl(i) = 'N')THEN
3629 match_dtl := FALSE;
3630 IF (inst_ext_dtl_rec.a_Code_tbl.COUNT = 0)THEN
3631 EXIT;
3632 END IF;
3633 FOR j IN inst_ext_dtl_rec.a_Code_tbl.FIRST..inst_ext_dtl_rec.a_Code_tbl.LAST LOOP
3634
3635 IF(inst_ext_dtl_rec.a_Code_tbl(j) = eff_ext_dtl_rec.a_Code_tbl(i) AND
3636 inst_ext_dtl_rec.a_Val_tbl(j) = eff_ext_dtl_rec.a_Val_tbl(i))THEN
3637 -- AHL_DEBUG_PUB.debug('Matched exiting - for include');
3638 match_dtl := TRUE;
3639 EXIT;
3640 END IF;
3641 END LOOP;
3642 IF match_dtl = TRUE THEN
3643 EXIT;
3644 END IF;
3645 ELSE
3646 match_dtl := TRUE;
3647 IF (inst_ext_dtl_rec.a_Code_tbl.COUNT = 0)THEN
3648 EXIT;
3649 END IF;
3650 FOR j IN inst_ext_dtl_rec.a_Code_tbl.FIRST..inst_ext_dtl_rec.a_Code_tbl.LAST LOOP
3651
3652 IF(inst_ext_dtl_rec.a_Code_tbl(j) = eff_ext_dtl_rec.a_Code_tbl(i) AND
3653 inst_ext_dtl_rec.a_Val_tbl(j) = eff_ext_dtl_rec.a_Val_tbl(i))THEN
3654 match_dtl := FALSE;
3655 -- AHL_DEBUG_PUB.debug('Matched exiting - for exclude');
3656 EXIT;
3657 END IF;
3658 END LOOP;
3659 IF match_dtl = FALSE THEN
3660 EXIT;
3661 END IF;
3662 END IF;
3663 END IF;
3664 END LOOP;
3665
3666 END IF;
3667
3668 RETURN match_dtl;
3669 END CHECK_EFFECTIVITY_EXT_DETAILS;
3670
3671 -- Define Local Procedure strip_serial_number_prefix --
3672 PROCEDURE strip_serial_number_prefix(
3673 p_serial_number IN VARCHAR2,
3674 x_serial_prefix OUT NOCOPY VARCHAR2,
3675 x_serial_suffix OUT NOCOPY NUMBER
3676 ) IS
3677 i NUMBER := 0;
3678 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
3679 BEGIN
3680 LOOP
3681 i := i+1;
3682 EXIT WHEN SUBSTR(p_serial_number,-i,1) NOT IN ('0','1','2','3','4','5','6','7','8','9')
3683 OR i > LENGTH(p_serial_number);
3684 END LOOP;
3685 x_serial_prefix := SUBSTR(p_serial_number,1,LENGTH(p_serial_number)-i+1);
3686 x_serial_suffix := TO_NUMBER(NVL(SUBSTR(p_serial_number,-i+1,i-1),0));
3687 END strip_serial_number_prefix;
3688
3689 -- Check to see whether a VARCHAR2 contains only numeric values
3690 FUNCTION sn_num(
3691 p_serial_number IN VARCHAR2
3692 ) RETURN BOOLEAN IS
3693 i NUMBER := 0;
3694
3695 BEGIN
3696 LOOP
3697 i := i+1;
3698 EXIT WHEN SUBSTR(p_serial_number,-i,1) NOT IN ('0','1','2','3','4','5','6','7','8','9')
3699 OR i > LENGTH(p_serial_number);
3700 END LOOP;
3701 IF i = LENGTH(p_serial_number) + 1 THEN
3702 RETURN TRUE;
3703 ELSE
3704 RETURN FALSE;
3705 END IF;
3706 END sn_num;
3707
3708 -- Check to see whether a given serial number is within the serial number range defined.
3709 FUNCTION check_sn_inside(
3710 p_sn IN VARCHAR2,
3711 p_sn1 IN VARCHAR2,
3712 p_sn2 IN VARCHAR2
3713 ) RETURN BOOLEAN IS
3714 BEGIN
3715 IF (p_sn) IS NULL THEN
3716 RETURN FALSE;
3717 END IF;
3718
3719 IF (p_sn1 IS NOT NULL AND sn_num(p_sn1) AND p_sn2 IS NOT NULL AND sn_num(p_sn2)) THEN
3720 IF (sn_num(p_sn)) THEN
3721 IF (TO_NUMBER(p_sn1)<=TO_NUMBER(p_sn) AND TO_NUMBER(p_sn)<=TO_NUMBER(p_sn2)) THEN
3722 RETURN TRUE;
3723 ELSE
3724 RETURN FALSE;
3725 END IF;
3726 -- nonnumeric case.
3727 ELSE
3728 IF (p_sn1 <= p_sn AND p_sn<= p_sn2) THEN
3729 RETURN TRUE;
3730 ELSE
3731 RETURN FALSE;
3732 END IF;
3733 END IF;
3734 ELSIF (p_sn1 IS NULL AND p_sn2 IS NOT NULL AND sn_num(p_sn2)) THEN
3735 IF (sn_num(p_sn)) THEN
3736 IF (TO_NUMBER(p_sn)<=TO_NUMBER(p_sn2)) THEN
3737 RETURN TRUE;
3738 ELSE
3739 RETURN FALSE;
3740 END IF;
3741 -- nonnumeric case.
3742 ELSE
3743 IF (p_sn <= p_sn2) THEN
3744 RETURN TRUE;
3745 ELSE
3746 RETURN FALSE;
3747 END IF;
3748 END IF;
3749 ELSIF (p_sn2 IS NULL AND p_sn1 IS NOT NULL AND sn_num(p_sn1)) THEN
3750 IF (sn_num(p_sn)) THEN
3751 IF (TO_NUMBER(p_sn1)<=TO_NUMBER(p_sn)) THEN
3752 RETURN TRUE;
3753 ELSE
3754 RETURN FALSE;
3755 END IF;
3756 -- nonnumeric case.
3757 ELSE
3758 IF (p_sn1<p_sn) THEN
3759 RETURN TRUE;
3760 ELSE
3761 RETURN FALSE;
3762 END IF;
3763 END IF;
3764 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
3765 IF (p_sn IS NOT NULL AND p_sn <= P_sn2 AND p_sn >= p_sn1) THEN
3766 RETURN TRUE;
3767 ELSE
3768 RETURN FALSE;
3769 END IF;
3770 ELSIF (p_sn1 IS NOT NULL AND NOT sn_num(p_sn1) AND p_sn2 IS NULL) THEN
3771 IF (p_sn IS NOT NULL AND p_sn >= p_sn1) THEN
3772 RETURN TRUE;
3773 ELSE
3774 RETURN FALSE;
3775 END IF;
3776 ELSIF (p_sn2 IS NOT NULL AND NOT sn_num(p_sn2) AND p_sn1 IS NULL) THEN
3777 IF (p_sn IS NOT NULL AND p_sn <= p_sn2) THEN
3778 RETURN TRUE;
3779 ELSE
3780 RETURN FALSE;
3781 END IF;
3782 ELSE
3783 RETURN TRUE;
3784 END IF;
3785 END check_sn_inside;
3786
3787
3788 -- Check to see whether a given serial number is outside of the serial number range defined.
3789 FUNCTION check_sn_outside(
3790 p_sn IN VARCHAR2,
3791 p_sn1 IN VARCHAR2,
3792 p_sn2 IN VARCHAR2
3793 ) RETURN BOOLEAN IS
3794 BEGIN
3795
3796 -- serial number is null.
3797 IF (p_sn IS NULL) THEN
3798 RETURN FALSE;
3799 END IF;
3800
3801 IF (p_sn1 IS NOT NULL AND sn_num(p_sn1) AND p_sn2 IS NOT NULL AND sn_num(p_sn2)) THEN
3802 IF (sn_num(p_sn)) THEN
3803 IF (TO_NUMBER(p_sn1)>TO_NUMBER(p_sn) OR TO_NUMBER(p_sn)>TO_NUMBER(p_sn2)) THEN
3804 RETURN TRUE;
3805 ELSE
3806 RETURN FALSE;
3807 END IF;
3808 -- fix for bug# 6449096 - non-numeric serials.
3809 ELSIF (p_sn > P_sn2 OR p_sn < p_sn1) THEN
3810 RETURN TRUE;
3811 ELSE
3812 RETURN FALSE;
3813 END IF;
3814 ELSIF (p_sn1 IS NULL AND p_sn2 IS NOT NULL AND sn_num(p_sn2)) THEN
3815 IF (sn_num(p_sn)) THEN
3816 IF (TO_NUMBER(p_sn)>TO_NUMBER(p_sn2)) THEN
3817 RETURN TRUE;
3818 ELSE
3819 RETURN FALSE;
3820 END IF;
3821 -- fix for bug# 6449096 - non-numeric serials.
3822 ELSIF (p_sn > P_sn2) THEN
3823 RETURN TRUE;
3824 ELSE
3825 RETURN FALSE;
3826 END IF;
3827 ELSIF (p_sn2 IS NULL AND p_sn1 IS NOT NULL AND sn_num(p_sn1)) THEN
3828 IF (sn_num(p_sn)) THEN
3829 IF (TO_NUMBER(p_sn1)>TO_NUMBER(p_sn)) THEN
3830 RETURN TRUE;
3831 ELSE
3832 RETURN FALSE;
3833 END IF;
3834 -- fix for bug# 6449096 - non-numeric serials.
3835 ELSIF (p_sn1 > p_sn) THEN
3836 RETURN TRUE;
3837 ELSE
3838 RETURN FALSE;
3839 END IF;
3840 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
3841 IF (p_sn > P_sn2 OR p_sn < p_sn1) THEN
3842 RETURN TRUE;
3843 ELSE
3844 RETURN FALSE;
3845 END IF;
3846 ELSIF (p_sn1 IS NOT NULL AND NOT sn_num(p_sn1) AND p_sn2 IS NULL) THEN
3847 IF (p_sn < p_sn1) THEN
3848 RETURN TRUE;
3849 ELSE
3850 RETURN FALSE;
3851 END IF;
3852 ELSIF (p_sn2 IS NOT NULL AND NOT sn_num(p_sn2) AND p_sn1 IS NULL) THEN
3853 IF (p_sn > p_sn2) THEN
3854 RETURN TRUE;
3855 ELSE
3856 RETURN FALSE;
3857 END IF;
3858 ELSE
3859 RETURN TRUE;
3860 END IF;
3861
3862 -- if control reached here, then return status FALSE.
3863 RETURN FALSE;
3864 END check_sn_outside;
3865
3866 PROCEDURE GET_PM_APPLICABLE_MRS (
3867 p_api_version IN NUMBER,
3868 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3869 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3870 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3871 x_return_status OUT NOCOPY VARCHAR2,
3872 x_msg_count OUT NOCOPY NUMBER,
3873 x_msg_data OUT NOCOPY VARCHAR2,
3874 p_item_instance_id IN NUMBER,
3875 x_applicable_activities_tbl OUT NOCOPY applicable_activities_tbl_type,
3876 x_applicable_programs_tbl OUT NOCOPY applicable_programs_tbl_type
3877 ) IS
3878 iap NUMBER;
3879 iap_u NUMBER;
3880 iaa NUMBER;
3881 l_api_name CONSTANT VARCHAR2(30) := 'GET_PM_APPLICABLE_MRS';
3882 l_api_version CONSTANT NUMBER := 1.0;
3883 l_msg_count NUMBER;
3884 l_msg_data VARCHAR2(2000);
3885 l_return_status VARCHAR2(1);
3886 l_item_instance_id NUMBER;
3887 l_inventory_item_id NUMBER;
3888 l_pm_install VARCHAR2(1);
3889 l_appln_code VARCHAR2(30):=FND_PROFILE.VALUE('AHL_APPLN_USAGE');
3890 l_inp_rec OKS_PM_ENTITLEMENTS_PUB.get_pmcontin_rec;
3891 l_ent_contracts OKS_ENTITLEMENTS_PUB.get_contop_tbl;
3892 l_pm_activities OKS_PM_ENTITLEMENTS_PUB.get_activityop_tbl;
3893 l_prior_mr_header_id NUMBER;
3894 /*
3895 TYPE unique_mr_headers_rec_type IS RECORD
3896 (
3897 mr_header_id NUMBER,
3898 service_line_id NUMBER
3899 );
3900 TYPE unique_mr_headers_tbl_type IS TABLE OF unique_mr_headers_rec_type
3901 INDEX BY BINARY_INTEGER;
3902 l_unique_mr_headers_tbl unique_mr_headers_tbl_type;
3903 */
3904 /* modified for performance fix - bug# 6511501
3905 CURSOR get_applicable_mrs(c_inventory_item_id NUMBER) IS
3906 SELECT mr_header_id, mr_effectivity_id
3907 FROM ahl_mr_effectivities_app_v
3908 WHERE inventory_item_id = c_inventory_item_id
3909 ORDER BY mr_header_id, mr_effectivity_id;
3910 */
3911
3912 CURSOR get_applicable_mrs(c_inventory_item_id NUMBER,
3913 c_mr_header_id NUMBER) IS
3914 SELECT mr_effectivity_id, repetitive_flag,
3915 show_repetitive_code,
3916 whichever_first_code,
3917 implement_status_code
3918 FROM ahl_mr_effectivities me, ahl_mr_headers_app_v mh
3919 WHERE me.mr_header_id = mh.mr_header_id
3920 AND mh.mr_header_id = c_mr_header_id
3921 AND me.inventory_item_id = c_inventory_item_id;
3922
3923 /*
3924 CURSOR get_activities(c_mr_header_id NUMBER) IS
3925 SELECT related_mr_header_id
3926 FROM ahl_mr_relationships_app_v
3927 WHERE mr_header_id = c_mr_header_id
3928 AND relationship_code = 'PARENT';
3929 */
3930 /* modified for performance fix - bug# 6511501
3931 --Get attributes of a given MR
3932 CURSOR get_mr_attri(c_mr_header_id NUMBER) IS
3933 SELECT repetitive_flag,
3934 show_repetitive_code,
3935 whichever_first_code,
3936 implement_status_code
3937 FROM ahl_mr_headers_app_v
3938 WHERE mr_header_id = c_mr_header_id;
3939 */
3940
3941 -- validate instance and get its attributes.
3942 CURSOR check_instance_exists_csr(c_item_instance_id NUMBER) IS
3943 SELECT instance_id, inventory_item_id
3944 FROM csi_item_instances
3945 WHERE instance_id = c_item_instance_id
3946 AND nvl(active_start_date,sysdate) <= sysdate and
3947 sysdate < NVL(active_end_date,sysdate+1);
3948
3949 --l_get_mr_attri get_mr_attri%ROWTYPE;
3950 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
3951 l_get_applicable_mrs_rec get_applicable_mrs%ROWTYPE;
3952
3953 BEGIN
3954 SAVEPOINT GET_PM_APPLICABLE_MRS_PVT;
3955 IF l_debug = 'Y' THEN
3956 AHL_DEBUG_PUB.enable_debug;
3957 AHL_DEBUG_PUB.debug('Begin private API: AHL_FMP_PVT.GET_PM_APPLICABLE_MRS');
3958 AHL_DEBUG_PUB.debug('');
3959 END IF;
3960 IF FND_API.to_boolean(p_init_msg_list) THEN
3961 FND_MSG_PUB.initialize;
3962 END IF;
3963 x_return_status := 'S';
3964 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version,
3965 l_api_name, G_PKG_NAME)
3966 THEN
3967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3968 END IF;
3969
3970 l_appln_code:=nvl(FND_PROFILE.VALUE('AHL_APPLN_USAGE'),'x');
3971 IF l_appln_code <> 'PM' THEN
3972 FND_MESSAGE.set_name('AHL', 'AHL_FMP_PM_NOT_INSTALLED');
3973 FND_MSG_PUB.add;
3974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3975 END IF;
3976
3977 OPEN check_instance_exists_csr(p_item_instance_id);
3978 FETCH check_instance_exists_csr INTO l_item_instance_id, l_inventory_item_id;
3979 IF check_instance_exists_csr%NOTFOUND THEN
3980 CLOSE check_instance_exists_csr;
3981 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_ITEM_INSTANCE');
3982 FND_MESSAGE.SET_TOKEN('INSTANCE',p_item_instance_id);
3983 FND_MSG_PUB.ADD;
3984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3985 END IF;
3986 CLOSE check_instance_exists_csr;
3987
3988 /* combined into the above cursor for performance bug#5093064.
3989 OPEN get_inventory_item(p_item_instance_id);
3990 FETCH get_inventory_item INTO l_inventory_item_id;
3991 IF get_inventory_item%NOTFOUND THEN
3992 CLOSE get_inventory_item;
3993 FND_MESSAGE.set_name('AHL', 'AHL_FMP_ITEM_NOT_EXISTS');
3994 FND_MESSAGE.set_token('INSTANCE',p_item_instance_id);
3995 FND_MSG_PUB.add;
3996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3997 END IF;
3998 CLOSE get_inventory_item;
3999 */
4000
4001 IF l_debug = 'Y' THEN
4002 AHL_DEBUG_PUB.debug('Item instance id:' || l_item_instance_id);
4003 END IF;
4004
4005 -- Call OKS API to get program_ids
4006 l_inp_rec.contract_number := NULL;
4007 l_inp_rec.contract_number_modifier := NULL;
4008 l_inp_rec.service_line_id := NULL;
4009 l_inp_rec.party_id := NULL;
4010 l_inp_rec.item_id := NULL;
4011 l_inp_rec.product_id := l_item_instance_id;
4012 l_inp_rec.request_date := NULL;
4013
4014 -- [FP Bug #5509763] -- Changes begin
4015 -- l_inp_rec.request_date_start := NULL;
4016 -- l_inp_rec.request_date_end := NULL;
4017 l_inp_rec.request_date_start := sysdate;
4018 l_inp_rec.request_date_end := to_date('31-12-4712','DD-MM-YYYY');
4019 -- [FP Bug #5509763] -- Changes end
4020
4021 l_inp_rec.sort_key := NULL;
4022
4023 OKS_PM_ENTITLEMENTS_PUB.get_pm_contracts
4024 ( p_api_version => 1.0,
4025 p_init_msg_list => FND_API.G_TRUE,
4026 p_inp_rec => l_inp_rec,
4027 x_return_status => l_return_status,
4028 x_msg_count => l_msg_count,
4029 x_msg_data => l_msg_data,
4030 x_ent_contracts => l_ent_contracts,
4031 x_pm_activities => l_pm_activities );
4032 IF l_msg_count > 0 THEN
4033 FND_MESSAGE.set_name('AHL', 'AHL_FMP_CALLING_OKS_API_ERROR');
4034 FND_MSG_PUB.add;
4035 RAISE FND_API.G_EXC_ERROR;
4036 END IF;
4037
4038 IF l_debug = 'Y' THEN
4039 AHL_DEBUG_PUB.debug('Count on ent_contracts:' || l_ent_contracts.count);
4040 AHL_DEBUG_PUB.debug('Count on pm_activities:' || l_pm_activities.count );
4041 END IF;
4042
4043 iap := 1;
4044 iaa := 1;
4045 -- iap_u := 1;
4046 -- l_prior_mr_header_id := -1;
4047 --FOR l_get_applicable_mrs IN get_applicable_mrs(l_inventory_item_id) LOOP
4048 --IF l_debug = 'Y' THEN
4049 -- AHL_DEBUG_PUB.debug('Appl MR found:' || l_get_applicable_mrs.mr_header_id);
4050 --END IF;
4051
4052 IF l_ent_contracts.COUNT > 0 THEN
4053 FOR i IN l_ent_contracts.FIRST..l_ent_contracts.LAST LOOP
4054 OPEN get_applicable_mrs(l_inventory_item_id, l_ent_contracts(i).pm_program_id);
4055 FETCH get_applicable_mrs INTO l_get_applicable_mrs_rec;
4056 IF (get_applicable_mrs%FOUND) THEN
4057 --IF l_ent_contracts(i).pm_program_id = l_get_applicable_mrs.mr_header_id THEN
4058
4059 IF l_debug = 'Y' THEN
4060 AHL_DEBUG_PUB.debug('Contract Number found:' || l_ent_contracts(i).contract_number);
4061 AHL_DEBUG_PUB.debug('Service Line ID:' || l_ent_contracts(i).service_line_id);
4062 AHL_DEBUG_PUB.debug('---------------------------');
4063 END IF;
4064
4065 x_applicable_programs_tbl(iap).contract_id := l_ent_contracts(i).contract_id;
4066 x_applicable_programs_tbl(iap).contract_number := l_ent_contracts(i).contract_number;
4067 x_applicable_programs_tbl(iap).contract_number_modifier := l_ent_contracts(i).contract_number_modifier;
4068 x_applicable_programs_tbl(iap).sts_code := l_ent_contracts(i).sts_code;
4069 x_applicable_programs_tbl(iap).service_line_id := l_ent_contracts(i).service_line_id;
4070 x_applicable_programs_tbl(iap).service_name := l_ent_contracts(i).service_name;
4071 x_applicable_programs_tbl(iap).service_description := l_ent_contracts(i).service_description;
4072 x_applicable_programs_tbl(iap).coverage_term_line_id := l_ent_contracts(i).coverage_term_line_id;
4073 x_applicable_programs_tbl(iap).coverage_term_name := l_ent_contracts(i).coverage_term_name;
4074 x_applicable_programs_tbl(iap).coverage_term_description := l_ent_contracts(i).coverage_term_description;
4075 x_applicable_programs_tbl(iap).coverage_type_code := l_ent_contracts(i).coverage_type_code;
4076 x_applicable_programs_tbl(iap).coverage_type_meaning := l_ent_contracts(i).coverage_type_meaning;
4077 x_applicable_programs_tbl(iap).coverage_type_imp_level := l_ent_contracts(i).coverage_type_imp_level;
4078 x_applicable_programs_tbl(iap).service_start_date := l_ent_contracts(i).service_start_date;
4079 x_applicable_programs_tbl(iap).service_end_date := l_ent_contracts(i).service_end_date;
4080 x_applicable_programs_tbl(iap).warranty_flag := l_ent_contracts(i).warranty_flag;
4081 x_applicable_programs_tbl(iap).eligible_for_entitlement := l_ent_contracts(i).eligible_for_entitlement;
4082 x_applicable_programs_tbl(iap).exp_reaction_time := l_ent_contracts(i).exp_reaction_time;
4083 x_applicable_programs_tbl(iap).exp_resolution_time := l_ent_contracts(i).exp_resolution_time;
4084 x_applicable_programs_tbl(iap).status_code := l_ent_contracts(i).status_code;
4085 x_applicable_programs_tbl(iap).status_text := l_ent_contracts(i).status_text;
4086 x_applicable_programs_tbl(iap).date_terminated := l_ent_contracts(i).date_terminated;
4087 x_applicable_programs_tbl(iap).pm_schedule_exists := l_ent_contracts(i).pm_schedule_exists;
4088 x_applicable_programs_tbl(iap).pm_program_id := l_ent_contracts(i).pm_program_id;
4089 --x_applicable_programs_tbl(iap).mr_effectivity_id := l_get_applicable_mrs.mr_effectivity_id;
4090 x_applicable_programs_tbl(iap).mr_effectivity_id := l_get_applicable_mrs_rec.mr_effectivity_id;
4091 iap := iap + 1;
4092 END IF;
4093 CLOSE get_applicable_mrs;
4094 END LOOP;
4095 END IF;
4096 /* IF l_ent_contracts(i).pm_program_id <> l_prior_mr_header_id THEN
4097 l_unique_mr_headers_tbl(iap_u).mr_header_id := l_ent_contracts(i).pm_program_id;
4098 l_unique_mr_headers_tbl(iap_u).service_line_id := l_ent_contracts(i).service_line_id;
4099 iap_u := iap_u + 1;
4100 END IF;
4101 l_prior_mr_header_id := l_ent_contracts(i).pm_program_id;
4102 */
4103 IF l_pm_activities.COUNT > 0 THEN
4104 FOR i IN l_pm_activities.FIRST..l_pm_activities.LAST LOOP
4105 OPEN get_applicable_mrs(l_inventory_item_id, l_pm_activities(i).activity_id);
4106 FETCH get_applicable_mrs INTO l_get_applicable_mrs_rec;
4107 IF (get_applicable_mrs%FOUND) THEN
4108 --IF (l_pm_activities(i).activity_id = l_get_applicable_mrs.mr_header_id ) THEN
4109 x_applicable_activities_tbl(iaa).mr_header_id := l_pm_activities(i).activity_id;
4110 x_applicable_activities_tbl(iaa).item_instance_id := l_item_instance_id;
4111 x_applicable_activities_tbl(iaa).program_mr_header_id := l_pm_activities(i).pm_program_id;
4112 x_applicable_activities_tbl(iaa).service_line_id := l_pm_activities(i).service_line_id;
4113 x_applicable_activities_tbl(iaa).act_schedule_exists := l_pm_activities(i).act_schedule_exists;
4114 --x_applicable_activities_tbl(iaa).mr_effectivity_id := l_get_applicable_mrs.mr_effectivity_id;
4115 x_applicable_activities_tbl(iaa).mr_effectivity_id := l_get_applicable_mrs_rec.mr_effectivity_id;
4116
4117 -- added for performance fix - bug# 6511501
4118 x_applicable_activities_tbl(i).repetitive_flag := l_get_applicable_mrs_rec.repetitive_flag;
4119 x_applicable_activities_tbl(i).show_repetitive_code := l_get_applicable_mrs_rec.show_repetitive_code;
4120 x_applicable_activities_tbl(i).whichever_first_code := l_get_applicable_mrs_rec.whichever_first_code;
4121 x_applicable_activities_tbl(i).implement_status_code := l_get_applicable_mrs_rec.implement_status_code;
4122
4123 iaa := iaa + 1;
4124 END IF;
4125 CLOSE get_applicable_mrs;
4126 END LOOP;
4127 END IF;
4128 --END LOOP;
4129 /*
4130 iaa := 1;
4131 IF x_applicable_programs_tbl.COUNT >= 1 THEN
4132 FOR i IN x_applicable_programs_tbl.FIRST..x_applicable_programs_tbl.LAST LOOP
4133 FOR l_get_activities IN get_activities(x_applicable_programs_tbl(i).pm_program_id) LOOP
4134 FOR l_get_applicable_mrs IN get_applicable_mrs(l_inventory_item_id) LOOP
4135 IF l_get_activities.related_mr_header_id = l_get_applicable_mrs.mr_header_id THEN
4136 x_applicable_activities_tbl(iaa).mr_header_id := l_get_applicable_mrs.mr_header_id;
4137 x_applicable_activities_tbl(iaa).mr_effectivity_id := l_get_applicable_mrs.mr_effectivity_id;
4138 x_applicable_activities_tbl(iaa).program_mr_header_id := x_applicable_programs_tbl(i).pm_program_id;
4139 x_applicable_activities_tbl(iaa).service_line_id := x_applicable_programs_tbl(i).service_line_id;
4140 iaa := iaa + 1;
4141 END IF;
4142 END LOOP;
4143 END LOOP;
4144 END LOOP;
4145 END IF;
4146 */
4147 /* modified for performance fix - bug# 6511501
4148 IF x_applicable_activities_tbl.COUNT >= 1 THEN
4149 FOR i IN x_applicable_activities_tbl.FIRST..x_applicable_activities_tbl.LAST LOOP
4150 OPEN get_mr_attri(x_applicable_activities_tbl(i).mr_header_id);
4151 FETCH get_mr_attri INTO l_get_mr_attri;
4152 IF get_mr_attri%NOTFOUND THEN
4153 CLOSE get_mr_attri;
4154 FND_MESSAGE.set_name('AHL','AHL_FMP_INVALID_MR');
4155 FND_MSG_PUB.add;
4156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4157 ELSE
4158 x_applicable_activities_tbl(i).repetitive_flag := l_get_mr_attri.repetitive_flag;
4159 x_applicable_activities_tbl(i).show_repetitive_code := l_get_mr_attri.show_repetitive_code;
4160 x_applicable_activities_tbl(i).whichever_first_code := l_get_mr_attri.whichever_first_code;
4161 x_applicable_activities_tbl(i).implement_status_code := l_get_mr_attri.implement_status_code;
4162 CLOSE get_mr_attri;
4163 END IF;
4164 END LOOP;
4165 END IF;
4166 */
4167 IF l_debug = 'Y' THEN
4168 AHL_DEBUG_PUB.debug('End private API: AHL_FMP_PVT.GET_PM_APPLICABLE_MRS');
4169 AHL_DEBUG_PUB.disable_debug;
4170 END IF;
4171
4172 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4173 ROLLBACK TO GET_PM_APPLICABLE_MRS_PVT;
4174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4175 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4176 p_count => x_msg_count,
4177 p_data => x_msg_data);
4178 IF l_debug = 'Y' THEN
4179 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
4180 'UNEXPECTED ERROR IN PRIVATE:' );
4181 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_PM_APPLICABLE_MRS');
4182 AHL_DEBUG_PUB.disable_debug;
4183 END IF;
4184 WHEN FND_API.G_EXC_ERROR THEN
4185 ROLLBACK TO GET_PM_APPLICABLE_MRS_PVT;
4186 x_return_status := FND_API.G_RET_STS_ERROR;
4187 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4188 p_count => x_msg_count,
4189 p_data => x_msg_data);
4190 IF l_debug = 'Y' THEN
4191 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
4192 'ERROR IN PRIVATE:' );
4193 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_PM_APPLICABLE_MRS');
4194 AHL_DEBUG_PUB.disable_debug;
4195 END IF;
4196 WHEN OTHERS THEN
4197 ROLLBACK TO GET_PM_APPLICABLE_MRS_PVT;
4198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4199 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4200 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_FMP_PVT',
4201 p_procedure_name => 'GET_PM_APPLICABLE_MRS',
4202 p_error_text => SUBSTRB(SQLERRM,1,240));
4203 END IF;
4204 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4205 p_count => x_msg_count,
4206 p_data => x_msg_data);
4207 IF l_debug = 'Y' THEN
4208
4209 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
4210 'OTHER ERROR IN PRIVATE:' );
4211 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_PM_APPLICABLE_MRS');
4212 AHL_DEBUG_PUB.disable_debug;
4213 END IF;
4214
4215 END GET_PM_APPLICABLE_MRS;
4216
4217
4218 PROCEDURE get_visit_applicable_mrs (
4219 p_api_version IN NUMBER,
4220 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4221 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4222 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4223 x_return_status OUT NOCOPY VARCHAR2,
4224 x_msg_count OUT NOCOPY NUMBER,
4225 x_msg_data OUT NOCOPY VARCHAR2,
4226 p_item_instance_id IN NUMBER,
4227 p_visit_type_code IN VARCHAR2
4228 )
4229 AS
4230 l_api_version CONSTANT NUMBER := 1.0;
4231 l_appl_mrs_tbl AHL_FMP_PVT.applicable_mr_tbl_type;
4232
4233 BEGIN
4234 -- Initialize temporary table.
4235 SAVEPOINT GET_VISIT_APPLICABLE_MRS_PVT;
4236 DELETE FROM AHL_APPLICABLE_MRS;
4237
4238 -- call api to fetch all applicable mrs for ASO installation.
4239 AHL_FMP_PVT.get_applicable_mrs(
4240 p_api_version => l_api_version,
4241 p_init_msg_list => FND_API.G_FALSE,
4242 p_commit => FND_API.G_FALSE,
4243 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4244 x_return_status => x_return_status,
4245 x_msg_count => x_msg_count,
4246 x_msg_data => x_msg_data,
4247 p_item_instance_id => p_item_instance_id,
4248 p_components_flag => 'N',
4249 p_include_doNotImplmt => 'Y',
4250 p_visit_type_code => p_visit_type_code,
4251 x_applicable_mr_tbl => l_appl_mrs_tbl);
4252
4253 -- Raise errors if exceptions occur
4254 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
4255 RAISE FND_API.G_EXC_ERROR;
4256 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4258 END IF;
4259
4260
4261
4262 -- Populate temporary table ahl_applicable_mrs.
4263 IF (l_appl_mrs_tbl.COUNT > 0) THEN
4264 FOR i IN l_appl_mrs_tbl.FIRST..l_appl_mrs_tbl.LAST LOOP
4265 -- dbms_output.put_line( l_appl_mrs_tbl(i).item_instance_id||' '||
4266 -- l_appl_mrs_tbl(i).mr_header_id);
4267 INSERT INTO AHL_APPLICABLE_MRS
4268 (
4269 CSI_ITEM_INSTANCE_ID,
4270 MR_HEADER_ID,
4271 MR_EFFECTIVITY_ID,
4272 REPETITIVE_FLAG ,
4273 SHOW_REPETITIVE_CODE,
4274 COPY_ACCOMPLISHMENT_CODE,
4275 PRECEDING_MR_HEADER_ID,
4276 IMPLEMENT_STATUS_CODE,
4277 DESCENDENT_COUNT
4278 )
4279 Values
4280 (
4281 l_appl_mrs_tbl(i).item_instance_id,
4282 l_appl_mrs_tbl(i).mr_header_id,
4283 l_appl_mrs_tbl(i).mr_effectivity_id,
4284 l_appl_mrs_tbl(i).repetitive_flag,
4285 l_appl_mrs_tbl(i).show_repetitive_code,
4286 l_appl_mrs_tbl(i).copy_accomplishment_flag,
4287 l_appl_mrs_tbl(i).preceding_mr_header_id,
4288 l_appl_mrs_tbl(i).implement_status_code,
4289 l_appl_mrs_tbl(i).descendent_count
4290 );
4291 END LOOP;
4292 END IF;
4293
4294 AHL_UMP_UTIL_PKG.process_group_mrs;
4295
4296 -- Delete visit types that do not match..
4297 DELETE AHL_APPLICABLE_MRS A
4298 WHERE NOT EXISTS (SELECT 'x' FROM AHL_MR_VISIT_TYPES
4299 WHERE MR_HEADER_ID=A.MR_HEADER_ID
4300 AND MR_VISIT_TYPE_CODE = P_VISIT_TYPE_CODE
4301 );
4302
4303 -- delete MRs from relationships table to remove duplicates.
4304 DELETE AHL_APPLICABLE_MRS A
4305 WHERE EXISTS ( select 'x' FROM
4306 AHL_APPLICABLE_MR_RELNS REL
4307 WHERE REL.related_mr_header_id = A.mr_header_id
4308 AND REL.RELATED_CSI_ITEM_INSTANCE_ID = A.CSI_ITEM_INSTANCE_ID);
4309
4310 -- Delete corressponding records for the above deletes from relationships table.
4311 DELETE AHL_APPLICABLE_MR_RELNS A
4312 WHERE NOT EXISTS (SELECT 'x' FROM AHL_APPLICABLE_MRS B
4313 WHERE B.MR_HEADER_ID = A.ORIG_MR_HEADER_ID
4314 AND B.CSI_ITEM_INSTANCE_ID = A.ORIG_CSI_ITEM_INSTANCE_ID);
4315
4316
4317
4318 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4319 ROLLBACK TO GET_VISIT_APPLICABLE_MRS_PVT;
4320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4321 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4322 p_count => x_msg_count,
4323 p_data => x_msg_data);
4324 WHEN FND_API.G_EXC_ERROR THEN
4325 ROLLBACK TO GET_VISIT_APPLICABLE_MRS_PVT;
4326 x_return_status := FND_API.G_RET_STS_ERROR;
4327 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4328 p_count => x_msg_count,
4329 p_data => x_msg_data);
4330 WHEN OTHERS THEN
4331 ROLLBACK TO GET_VISIT_APPLICABLE_MRS_PVT;
4332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4333 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4334 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_FMP_PVT',
4335 p_procedure_name => 'GET_PM_APPLICABLE_MRS',
4336 p_error_text => SUBSTRB(SQLERRM,1,240));
4337 END IF;
4338 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4339 p_count => x_msg_count,
4340 p_data => x_msg_data);
4341 END GET_VISIT_APPLICABLE_MRS;
4342
4343 FUNCTION is_pc_assoc_valid(p_item_instance_id IN NUMBER,p_pc_node_id IN NUMBER) RETURN VARCHAR2 IS
4344
4345 -- Get valid pc nodes for an instance
4346 -- check pc_node against items.
4347 -- Added hints recommended by appsperf to fix bug# 16003176
4348 CURSOR validate_itm_node_csr (c_instance_id NUMBER,
4349 c_pc_node_id NUMBER)
4350 IS
4351 SELECT /*+ CONNECT_BY_FILTERING */ 'x'
4352 FROM ahl_pc_nodes_b B
4353 WHERE B.pc_node_id = c_pc_node_id
4354 START WITH B.pc_node_id IN (select /*+ UNNEST */ pc_node_id
4355 from ahl_pc_associations itm, csi_item_instances csi,
4356 (select /*+ INDEX(E) */ object_id
4357 FROM csi_ii_relationships E
4358 START WITH E.subject_id = c_instance_id
4359 AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
4360 CONNECT BY E.subject_id = PRIOR E.object_id
4361 AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
4362 UNION ALL
4363 SELECT c_instance_id
4364 FROM DUAL) ii
4365 where itm.association_type_flag = 'I'
4366 and itm.unit_item_id = csi.inventory_item_id
4367 and csi.instance_id = ii.object_id)
4368 CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
4369
4370 -- check pc_node against units.
4371 -- Added hints recommended by appsperf to fix bug# 16003176
4372 CURSOR validate_unit_node_csr (c_instance_id NUMBER,
4373 c_pc_node_id NUMBER)
4374 IS
4375 SELECT /*+ CONNECT_BY_FILTERING */ 'x'
4376 FROM ahl_pc_nodes_b B
4377 WHERE B.pc_node_id = c_pc_node_id
4378 START WITH B.pc_node_id IN (select /*+ UNNEST */ pc_node_id
4379 from ahl_pc_associations unit, ahl_unit_config_headers uc,
4380 (select /*+ INDEX(E) */ object_id
4381 FROM csi_ii_relationships E
4382 START WITH E.subject_id = c_instance_id
4383 AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
4384 CONNECT BY E.subject_id = PRIOR E.object_id
4385 AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
4386 UNION ALL
4387 SELECT c_instance_id
4388 FROM DUAL) ii
4389 where unit.association_type_flag = 'U'
4390 and unit.unit_item_id = uc.unit_config_header_id
4391 and uc.csi_item_instance_id = ii.object_id)
4392 CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
4393
4394 l_junk VARCHAR2(1);
4395
4396 BEGIN
4397 /* modified for bug# 9620276
4398 OPEN validate_pc_node_csr(p_item_instance_id,p_pc_node_id);
4399 FETCH validate_pc_node_csr INTO l_junk;
4400 IF(validate_pc_node_csr%NOTFOUND)THEN
4401 CLOSE validate_pc_node_csr;
4402 RETURN FND_API.G_FALSE;
4403 END IF;
4404 CLOSE validate_pc_node_csr;
4405 RETURN FND_API.G_TRUE;
4406 */
4407
4408 -- check for units first.
4409 OPEN validate_unit_node_csr(p_item_instance_id, p_pc_node_id);
4410 FETCH validate_unit_node_csr INTO l_junk;
4411 IF (validate_unit_node_csr%FOUND) THEN
4412 CLOSE validate_unit_node_csr;
4413 RETURN FND_API.G_TRUE;
4414 ELSE
4415 CLOSE validate_unit_node_csr;
4416 -- check for item PCs
4417 OPEN validate_itm_node_csr(p_item_instance_id, p_pc_node_id);
4418 FETCH validate_itm_node_csr INTO l_junk;
4419 IF (validate_itm_node_csr%FOUND) THEN
4420 CLOSE validate_itm_node_csr;
4421 RETURN FND_API.G_TRUE;
4422 END IF;
4423 CLOSE validate_itm_node_csr;
4424 END IF;
4425 RETURN FND_API.G_FALSE;
4426
4427
4428 END is_pc_assoc_valid;
4429
4430 -- Added for perf fix - 9434441
4431 -- procedure will populate the full component hierarchy as
4432 -- query for pc_node_id validation needs to traverse up the tree from input instance
4433 -- and other queries need to traverse down from the input instance.
4434 PROCEDURE Populate_Config_Components (p_item_instance_id IN NUMBER) IS
4435
4436 -- get config elements.
4437 -- added subj_child_exists to fix BUE perf bug# 13629335
4438 CURSOR get_config_components(p_root_instance_id IN NUMBER) IS
4439 SELECT cir.subject_id, cir.object_id,
4440 (select 'Y' from csi_ii_relationships where object_id = cir.subject_id
4441 and relationship_type_code = 'COMPONENT-OF'
4442 and trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
4443 and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
4444 and rownum = 1) subj_child_exists
4445 FROM csi_ii_relationships cir
4446 START WITH cir.object_id = p_root_instance_id
4447 AND cir.relationship_type_code = 'COMPONENT-OF'
4448 AND trunc(nvl(cir.active_start_date,sysdate)) <= trunc(sysdate)
4449 AND trunc(sysdate) < trunc(nvl(cir.active_end_date, sysdate+1))
4450 CONNECT BY PRIOR cir.subject_id = cir.object_id
4451 AND cir.relationship_type_code = 'COMPONENT-OF'
4452 AND trunc(nvl(cir.active_start_date,sysdate)) <= trunc(sysdate)
4453 AND trunc(sysdate) < trunc(nvl(cir.active_end_date, sysdate+1));
4454
4455 -- get root instance
4456 CURSOR csi_root_instance_csr (p_instance_id IN NUMBER) IS
4457 SELECT root.object_id
4458 FROM csi_ii_relationships root
4459 WHERE NOT EXISTS (SELECT 'x'
4460 FROM csi_ii_relationships
4461 WHERE subject_id = root.object_id
4462 AND relationship_type_code = 'COMPONENT-OF'
4463 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
4464 )
4465 START WITH root.subject_id = p_instance_id
4466 AND root.relationship_type_code = 'COMPONENT-OF'
4467 AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
4468 AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1))
4469 CONNECT BY PRIOR root.object_id = root.subject_id
4470 AND root.relationship_type_code = 'COMPONENT-OF'
4471 AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
4472 AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1));
4473
4474 l_buffer_limit NUMBER := 1000;
4475
4476 l_subj_id_tbl nbr_tbl_type;
4477 l_obj_id_tbl nbr_tbl_type;
4478
4479 l_root_instance_id number;
4480
4481 -- added to fix BUE perf bug# 13629335
4482 l_child_exists_tbl vchar_tbl_type;
4483
4484 BEGIN
4485
4486 -- get root instance.
4487 OPEN csi_root_instance_csr(p_item_instance_id);
4488 FETCH csi_root_instance_csr INTO l_root_instance_id;
4489 IF (csi_root_instance_csr%NOTFOUND) THEN
4490 l_root_instance_id := p_item_instance_id;
4491 END IF;
4492 CLOSE csi_root_instance_csr;
4493
4494 -- populate config children.
4495 -- added l_child_exists_tbl to fix BUE perf bug# 13629335
4496 OPEN get_config_components(l_root_instance_id);
4497 LOOP
4498 FETCH get_config_components BULK COLLECT INTO l_subj_id_tbl, l_obj_id_tbl, l_child_exists_tbl LIMIT l_buffer_limit;
4499 EXIT WHEN (l_subj_id_tbl.count = 0);
4500
4501 -- insert into temp table.
4502 FORALL i IN l_subj_id_tbl.FIRST..l_subj_id_tbl.LAST
4503 INSERT INTO AHL_CONFIG_COMPONENTS (subject_id, object_id, root_object_id, subj_child_exists)
4504 values (l_subj_id_tbl(i), l_obj_id_tbl(i), l_root_instance_id, nvl(l_child_exists_tbl(i), 'N'));
4505
4506 l_subj_id_tbl.delete;
4507 l_obj_id_tbl.delete;
4508 l_child_exists_tbl.delete;
4509
4510 END LOOP;
4511 CLOSE get_config_components;
4512
4513 END Populate_Config_Components;
4514 -----------------------------------------
4515 -- get MR applicability even if MR is expired.
4516 PROCEDURE GET_MR_APPLICABILITY(
4517 p_api_version IN NUMBER,
4518 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4519 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4520 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4521 x_return_status OUT NOCOPY VARCHAR2,
4522 x_msg_count OUT NOCOPY NUMBER,
4523 x_msg_data OUT NOCOPY VARCHAR2,
4524 p_item_instance_id IN NUMBER,
4525 p_mr_header_id IN NUMBER,
4526 p_components_flag IN VARCHAR2 := 'N',
4527 p_include_doNotImplmt IN VARCHAR2 := 'Y',
4528 x_applicable_mr_tbl OUT NOCOPY APPLICABLE_MR_TBL_TYPE
4529 ) IS
4530
4531
4532 l_api_name CONSTANT VARCHAR2(30) := 'GET_MR_APPLICABILITY';
4533 l_api_version CONSTANT NUMBER := 1.0;
4534 l_msg_count NUMBER;
4535 l_return_status VARCHAR2(1);
4536 l_item_instance_id NUMBER;
4537 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
4538 l_debug_prefix VARCHAR2(40) := 'AHL_FMP_PVT.GET_MR_APPLICABILITY';
4539
4540
4541 -- Get valid pc nodes for an instance
4542 -- Query to get valid pc nodes for an instance was split into 2 SQLs (item and unit)
4543 -- modified for perf fix 9434441
4544 -- check pc_node against items.
4545 CURSOR validate_itm_node_csr (c_instance_id NUMBER,
4546 c_pc_node_id NUMBER)
4547 IS
4548 SELECT 'x'
4549 FROM ahl_pc_nodes_b B
4550 WHERE B.pc_node_id = c_pc_node_id
4551 START WITH B.pc_node_id IN (select pc_node_id
4552 from ahl_pc_associations itm, csi_item_instances csi,
4553 (select object_id
4554 FROM ahl_config_components E
4555 START WITH E.subject_id = c_instance_id
4556 CONNECT BY E.subject_id = PRIOR E.object_id
4557 UNION ALL
4558 SELECT c_instance_id
4559 FROM DUAL) ii
4560 where itm.association_type_flag = 'I'
4561 and itm.unit_item_id = csi.inventory_item_id
4562 and csi.instance_id = ii.object_id)
4563 CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
4564
4565 -- check pc_node against units.
4566 CURSOR validate_unit_node_csr (c_instance_id NUMBER,
4567 c_pc_node_id NUMBER)
4568 IS
4569 SELECT 'x'
4570 FROM ahl_pc_nodes_b B
4571 WHERE B.pc_node_id = c_pc_node_id
4572 START WITH B.pc_node_id IN (select pc_node_id
4573 from ahl_pc_associations unit, ahl_unit_config_headers uc,
4574 (select object_id
4575 FROM ahl_config_components E
4576 START WITH E.subject_id = c_instance_id
4577 CONNECT BY E.subject_id = PRIOR E.object_id
4578 UNION ALL
4579 SELECT c_instance_id
4580 FROM DUAL) ii
4581 where unit.association_type_flag = 'U'
4582 and unit.unit_item_id = uc.unit_config_header_id
4583 and uc.csi_item_instance_id = ii.object_id)
4584 CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
4585
4586 --Get attributes of a given MR
4587 CURSOR get_mr_attri(c_mr_header_id NUMBER) IS
4588 SELECT repetitive_flag,
4589 show_repetitive_code,
4590 --preceding_mr_header_id,
4591 copy_accomplishment_flag,
4592 implement_status_code,
4593 count_mr_descendents(c_mr_header_id) descendent_count
4594 FROM ahl_mr_headers_b --perf bug 6266738. using base tables.
4595 WHERE mr_header_id = c_mr_header_id;
4596
4597 l_get_mr_attri get_mr_attri%ROWTYPE;
4598
4599 -- for the configuration components, get valid mr effectivities based on inventory items.
4600 -- replaced csi_ii_relationships with ahl_config_components to fix bug# 9434441
4601 CURSOR get_comp_mr_inv_csr(c_instance_id NUMBER, c_mr_header_id NUMBER) IS
4602 SELECT A.mr_header_id, A.mr_effectivity_id,
4603 A.pc_node_id,
4604 ii.instance_id subject_id,
4605 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
4606 WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
4607 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
4608 ii.serial_number
4609 FROM ( SELECT a.subject_id
4610 --FROM csi_ii_relationships a
4611 FROM ahl_config_components a
4612 START WITH object_id = c_instance_id
4613 --AND relationship_type_code = 'COMPONENT-OF'
4614 --AND SYSDATE between trunc(nvl(active_start_date,sysdate))
4615 --AND trunc(NVL(active_end_date,sysdate+1))
4616 CONNECT BY object_id = PRIOR subject_id
4617 --AND relationship_type_code = 'COMPONENT-OF'
4618 --AND SYSDATE between trunc(nvl(active_start_date,sysdate))
4619 --AND trunc(NVL(active_end_date,sysdate+1))
4620 ) cir, csi_item_instances ii,
4621 ahl_mr_effectivities A
4622 WHERE A.mr_header_id = c_mr_header_id
4623 AND ii.instance_id = cir.subject_id
4624 AND A.inventory_item_id = ii.inventory_item_id
4625 AND A.relationship_id is null
4626 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
4627 WHERE MR.mr_header_id = c_mr_header_id
4628 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
4629 AND mr_status_code='COMPLETE'
4630 )
4631 ORDER BY A.mr_effectivity_id, A.mr_header_id;
4632
4633 -- for input instance, get valid mr effectivities based on inventory items.
4634 CURSOR get_inst_mr_inv_csr(c_instance_id NUMBER, c_mr_header_id NUMBER) IS
4635 SELECT A.mr_header_id, A.mr_effectivity_id,
4636 A.pc_node_id,
4637 ii.instance_id subject_id,
4638 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
4639 WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
4640 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
4641 ii.serial_number
4642 FROM csi_item_instances ii,
4643 ahl_mr_effectivities A
4644 WHERE ii.instance_id = c_instance_id
4645 AND A.mr_header_id = c_mr_header_id
4646 AND A.inventory_item_id = ii.inventory_item_id
4647 AND A.relationship_id is null
4648 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
4649 WHERE MR.mr_header_id = c_mr_header_id
4650 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
4651 AND mr_status_code='COMPLETE'
4652 )
4653 ORDER BY A.mr_effectivity_id, A.mr_header_id;
4654
4655
4656 -- get valid mr effectivities based on path position ID.
4657 CURSOR get_posn_mr_csr(c_mr_header_id NUMBER) IS
4658 SELECT A.mr_header_id, A.mr_effectivity_id,
4659 A.pc_node_id,
4660 cii.instance_id subject_id,
4661 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
4662 WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
4663 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
4664 cii.serial_number
4665 FROM ahl_applicable_instances aai,csi_item_instances cii,
4666 ahl_mr_effectivities A
4667 WHERE A.mr_header_id = c_mr_header_id
4668 AND A.relationship_id IS NOT NULL
4669 AND aai.position_id = A.relationship_id
4670 AND aai.csi_item_instance_id = cii.instance_id
4671 AND nvl(A.inventory_item_id,cii.inventory_item_id) = cii.inventory_item_id
4672 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
4673 WHERE MR.mr_header_id = c_mr_header_id
4674 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
4675 AND mr_status_code='COMPLETE'
4676 )
4677 --ORDER BY cii.instance_id;
4678 ORDER BY A.mr_effectivity_id, A.mr_header_id;
4679
4680 -- check for path position based effectivities for MR
4681 CURSOR relationship_mr_csr(p_mr_header_id IN NUMBER) IS
4682 SELECT 'x' from dual
4683 WHERE exists (select 'x'
4684 from ahl_mr_effectivities
4685 where mr_header_id = p_mr_header_id
4686 and relationship_id is not null);
4687
4688
4689 /*
4690 amsriniv Bug 6971165 : To improve performance, replicating the logic of CHECK_EFFECTIVITY_DETAILS in this
4691 procedure so that the below two cursors are invoked only when required. Previously, the function
4692 CHECK_EFFECTIVITY_DETAILS was being called for every combination of mr_effectivity and instance_id. Now, the
4693 get_inst_att cursor is called once for an instance and get_effect_details is called only if the MR validity still
4694 holds.
4695 */
4696 CURSOR get_effect_details(c_mr_effectivity_id NUMBER) IS
4697 SELECT exclude_flag, serial_number_from, serial_number_to, manufacturer_id,
4698 manufacture_date_from, manufacture_date_to, country_code
4699 FROM ahl_mr_effectivity_dtls
4700 WHERE mr_effectivity_id = c_mr_effectivity_id
4701 ORDER BY exclude_flag ASC;
4702
4703 -- added for perf fix 9434441
4704 CURSOR get_effect_ext_details(c_mr_effectivity_id NUMBER) IS
4705 SELECT 'Y'
4706 FROM ahl_mr_effectivity_ext_dtls a
4707 WHERE a.mr_effectivity_id = c_mr_effectivity_id
4708 AND rownum < 2;
4709
4710 -- to get configuration nodes.
4711 CURSOR get_config_tree_csr ( p_csi_instance_id IN NUMBER) IS
4712 SELECT subject_id
4713 FROM csi_ii_relationships
4714 START WITH object_id = p_csi_instance_id
4715 AND relationship_type_code = 'COMPONENT-OF'
4716 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
4717 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
4718 CONNECT BY PRIOR subject_id = object_id
4719 AND relationship_type_code = 'COMPONENT-OF'
4720 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
4721 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
4722 ORDER BY level;
4723
4724
4725
4726 l_mr_header_id_tbl nbr_tbl_type;
4727 l_mr_effectivity_id_tbl nbr_tbl_type;
4728 l_mr_pc_node_id_tbl nbr_tbl_type;
4729 l_instance_id_tbl nbr_tbl_type;
4730 l_position_id_tbl nbr_tbl_type;
4731 l_pc_node_id_tbl nbr_tbl_type; --amsriniv
4732
4733 l_mfg_date_tbl date_tbl_type;
4734 l_serial_num_tbl vchar_tbl_type;
4735 l_subj_id_tbl nbr_tbl_type;
4736
4737 l_index number;
4738 l_valid_mr_flag varchar2(1);
4739 l_junk varchar2(1);
4740 l_rows_count number := 0; --amsriniv Bug 6971165
4741
4742 l_buffer_limit number := 1000;
4743
4744 -- dummy values as manufacturer and country code are not supported by
4745 -- the application.
4746 l_inst_manufacturer_id VARCHAR2(1) := 'm';
4747 l_inst_country_code VARCHAR2(1) := 'c';
4748
4749 l_path_posn_flag BOOLEAN;
4750
4751 l_process_loop NUMBER := 0;
4752 -- l_process_loop indicates the processing stage
4753 -- = 1: processing effectivities based on inventory items for input instance
4754 -- = 2: processing effectivities based on inventory items for config components.
4755 -- = 3: processing effectivities based on MC positions for input instance and
4756 -- components. Note component level details are populated in temp table
4757 -- only if the p_components_flag = 'Y'
4758
4759 l_prev_effectivity_id NUMBER;
4760 l_prev_mr_header_id NUMBER;
4761
4762
4763 --define record type to hold effectivity details.
4764 TYPE eff_dtl_rectype IS RECORD (
4765 eflag_tbl vchar_tbl_type,
4766 srl_from_tbl vchar_tbl_type,
4767 srl_to_tbl vchar_tbl_type,
4768 mID_tbl nbr_tbl_type,
4769 mdate_from_tbl date_tbl_type,
4770 mdate_to_tbl date_tbl_type,
4771 c_code_tbl vchar_tbl_type
4772 );
4773
4774 eff_dtl_rec eff_dtl_rectype;
4775
4776 l_extn_exists_flag VARCHAR2(3);
4777
4778 -- added to fix perf bug# 9434441
4779 CURSOR appl_inst_exists_csr IS
4780 SELECT 'x'
4781 FROM dual
4782 WHERE EXISTS (select 'x' from ahl_applicable_instances);
4783
4784 l_appl_inst_exists_flag BOOLEAN;
4785
4786 BEGIN
4787
4788 IF l_debug = 'Y' THEN
4789 AHL_DEBUG_PUB.enable_debug;
4790 AHL_DEBUG_PUB.debug('Begin private API: AHL_FMP_PVT.GET_MR_APPLICABILITY', l_debug_prefix);
4791 AHL_DEBUG_PUB.debug('Input p_item_instance_id:' || p_item_instance_id, l_debug_prefix);
4792 AHL_DEBUG_PUB.debug('Input p_mr_header_id:' || p_mr_header_id, l_debug_prefix);
4793 AHL_DEBUG_PUB.debug('Input p_components_flag:' || p_components_flag, l_debug_prefix);
4794 AHL_DEBUG_PUB.debug('Input p_include_doNotImplmt:' || p_include_doNotImplmt, l_debug_prefix);
4795 END IF;
4796
4797 IF FND_API.to_boolean(p_init_msg_list) THEN
4798 FND_MSG_PUB.initialize;
4799 END IF;
4800
4801 x_return_status := 'S';
4802
4803 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version,
4804 l_api_name, G_PKG_NAME)
4805 THEN
4806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4807 END IF;
4808
4809 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL)
4810 THEN
4811 -- validate input instance.
4812 OPEN check_instance_exists(p_item_instance_id);
4813 FETCH check_instance_exists INTO l_item_instance_id;
4814 IF check_instance_exists%NOTFOUND THEN
4815 CLOSE check_instance_exists;
4816 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_ITEM_INSTANCE');
4817 FND_MESSAGE.SET_TOKEN('INSTANCE',p_item_instance_id);
4818 FND_MSG_PUB.ADD;
4819 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4820 END IF;
4821 CLOSE check_instance_exists;
4822 END IF;
4823
4824 IF l_debug = 'Y' THEN
4825 AHL_DEBUG_PUB.debug('After instance validation:' || p_item_instance_id, l_debug_prefix);
4826 END IF;
4827
4828 -- start processing. First populate temp table ahl_applicable_instances by calling
4829 -- MC api to map instance (and components) to path positions.
4830
4831 IF l_debug = 'Y' THEN
4832 AHL_DEBUG_PUB.debug('Start Processing..', l_debug_prefix);
4833 END IF;
4834
4835 -- initialize temp table.
4836 -- added to fix bug# 9434441.
4837 DELETE FROM AHL_CONFIG_COMPONENTS;
4838
4839 -- populate temp table with config components
4840 --IF (p_components_flag = 'Y') THEN -- commented out as part of fix for bug# 9434441 as the pc_node_id validation needs the configuration to be populated.
4841 IF l_debug = 'Y' THEN
4842 AHL_DEBUG_PUB.debug('populating Config components..', l_debug_prefix);
4843 END IF;
4844 Populate_Config_Components(p_item_instance_id);
4845 --END IF;
4846
4847 l_path_posn_flag := FALSE;
4848
4849 -- check if effectivities exist with path positions for mr_header_id.
4850 OPEN relationship_mr_csr(p_mr_header_id);
4851 FETCH relationship_mr_csr INTO l_junk;
4852 IF (relationship_mr_csr%FOUND) THEN
4853 l_path_posn_flag := TRUE;
4854 END IF;
4855 CLOSE relationship_mr_csr;
4856
4857 -- fix for perf bug# 9434441
4858 -- initialize flag that indicate that ahl_applicable_instances has at least one row.
4859 l_appl_inst_exists_flag := FALSE;
4860
4861
4862 IF (l_path_posn_flag) THEN
4863
4864 IF l_debug = 'Y' THEN
4865 AHL_DEBUG_PUB.debug('Processing MC Relationships..', l_debug_prefix);
4866 END IF;
4867
4868 DELETE FROM ahl_applicable_instances;
4869 -- for input instance.
4870 AHL_MC_PATH_POSITION_PVT.map_instance_to_positions
4871 (
4872 p_api_version => 1.0,
4873 p_init_msg_list => fnd_api.g_false,
4874 p_commit => fnd_api.g_false,
4875 p_validation_level => p_validation_level,
4876 p_csi_item_instance_id => p_item_instance_id,
4877 x_return_status => l_return_status,
4878 x_msg_count => l_msg_count,
4879 x_msg_data => x_msg_data
4880 );
4881
4882 -- Raise errors if exceptions occur
4883 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4884 RAISE FND_API.G_EXC_ERROR;
4885 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4887 END IF;
4888
4889 IF (p_components_flag = 'Y') THEN
4890 IF l_debug = 'Y' THEN
4891 AHL_DEBUG_PUB.debug('Processing Component MC Relationships..', l_debug_prefix);
4892 END IF;
4893
4894 OPEN get_config_tree_csr(p_item_instance_id);
4895 LOOP
4896 FETCH get_config_tree_csr BULK COLLECT INTO l_subj_id_tbl LIMIT l_buffer_limit;
4897 EXIT WHEN l_subj_id_tbl.COUNT = 0;
4898
4899 FOR j IN l_subj_id_tbl.FIRST..l_subj_id_tbl.LAST LOOP
4900 AHL_MC_PATH_POSITION_PVT.map_instance_to_positions
4901 (
4902 p_api_version => 1.0,
4903 p_init_msg_list => fnd_api.g_false,
4904 p_commit => fnd_api.g_false,
4905 p_validation_level => p_validation_level,
4906 p_csi_item_instance_id => l_subj_id_tbl(j),
4907 x_return_status => l_return_status,
4908 x_msg_count => l_msg_count,
4909 x_msg_data => x_msg_data
4910 );
4911 -- Raise errors if exceptions occur
4912 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4913 RAISE FND_API.G_EXC_ERROR;
4914 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4916 END IF;
4917
4918 END LOOP;
4919 l_subj_id_tbl.DELETE;
4920 END LOOP;
4921 CLOSE get_config_tree_csr;
4922
4923 END IF; -- p_components_flag
4924
4925 -- check if any data is populated in temp table.
4926 -- fix for perf bug# 9434441
4927 OPEN appl_inst_exists_csr;
4928 FETCH appl_inst_exists_csr INTO l_junk;
4929 IF (appl_inst_exists_csr%FOUND) THEN
4930 l_appl_inst_exists_flag := TRUE;
4931 END IF;
4932 CLOSE appl_inst_exists_csr;
4933
4934 END IF; -- l_path_posn_flag
4935
4936 IF l_debug = 'Y' THEN
4937 AHL_DEBUG_PUB.debug('Processing Inventory Items..', l_debug_prefix);
4938 END IF;
4939
4940 l_index := 1;
4941
4942 -- initialize. Tracks if MC api to get path position details has been called or not.
4943 -- l_pc_node_inst_id := NULL; --amsriniv
4944
4945 -- indicates processing stage.
4946 l_process_loop := 1; -- process inv items for input instance.
4947
4948 -- Loop based on processing stage
4949 LOOP
4950 -- Get effectivities for the mr and instance.
4951 IF (l_process_loop = 1) THEN
4952 -- get eff for top node
4953 OPEN get_inst_mr_inv_csr(p_item_instance_id,p_mr_header_id);
4954 ELSIF (l_process_loop = 2) THEN
4955 -- get eff for components.
4956 OPEN get_comp_mr_inv_csr(p_item_instance_id,p_mr_header_id);
4957 IF l_debug = 'Y' THEN
4958 AHL_DEBUG_PUB.debug('Processing Component Effectivities based on Inventory Items..', l_debug_prefix);
4959 END IF;
4960
4961 ELSIF (l_process_loop = 3) THEN
4962 -- get eff based on positions
4963 OPEN get_posn_mr_csr(p_mr_header_id);
4964
4965 IF l_debug = 'Y' THEN
4966 AHL_DEBUG_PUB.debug('Processing Effectivities based on MC Positions..', l_debug_prefix);
4967 END IF;
4968 END IF;
4969
4970 LOOP
4971 -- fetch effectivity data and process.
4972 IF (l_process_loop = 1) THEN
4973 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,
4974 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --,l_eff_exists_tbl
4975 LIMIT l_buffer_limit;
4976 ELSIF (l_process_loop = 2) THEN
4977 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,
4978 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
4979 LIMIT l_buffer_limit;
4980 ELSIF (l_process_loop = 3) THEN
4981 FETCH get_posn_mr_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,
4982 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
4983 LIMIT l_buffer_limit;
4984
4985 END IF; -- l_process_loop
4986
4987 EXIT WHEN (l_mr_header_id_tbl.count = 0);
4988
4989 IF l_debug = 'Y' THEN
4990 AHL_DEBUG_PUB.debug('Count of l_mr_header_id_tbl:' || l_mr_header_id_tbl.count, l_debug_prefix);
4991 END IF;
4992
4993 -- process retrieved effectivity IDs.
4994 FOR i IN l_mr_effectivity_id_tbl.FIRST..l_mr_effectivity_id_tbl.LAST LOOP
4995
4996 -- to begin with, set effectivity as valid.
4997 l_valid_mr_flag := 'Y';
4998
4999 IF(l_prev_effectivity_id IS NULL OR l_prev_effectivity_id <> l_mr_effectivity_id_tbl(i)) THEN
5000 -- read effectivity details
5001 OPEN get_effect_details(l_mr_effectivity_id_tbl(i));
5002 FETCH get_effect_details BULK COLLECT INTO eff_dtl_rec.eflag_tbl, eff_dtl_rec.srl_from_tbl, eff_dtl_rec.srl_to_tbl,
5003 eff_dtl_rec.mID_tbl, eff_dtl_rec.mdate_from_tbl, eff_dtl_rec.mdate_to_tbl, eff_dtl_rec.c_code_tbl;
5004 CLOSE get_effect_details;
5005
5006 -- added for eff ext details.
5007 OPEN get_effect_ext_details(l_mr_effectivity_id_tbl(i));
5008 FETCH get_effect_ext_details INTO l_extn_exists_flag;
5009 IF (get_effect_ext_details%NOTFOUND) THEN
5010 l_extn_exists_flag := 'N';
5011 END IF;
5012 CLOSE get_effect_ext_details;
5013
5014 l_prev_effectivity_id := l_mr_effectivity_id_tbl(i);
5015 END IF;
5016 IF (eff_dtl_rec.eflag_tbl.count > 0) THEN
5017
5018 FOR j IN eff_dtl_rec.eflag_tbl.FIRST..eff_dtl_rec.eflag_tbl.LAST LOOP
5019 IF eff_dtl_rec.eflag_tbl(j) = 'N' THEN
5020 IF (check_sn_inside(l_serial_num_tbl(i), eff_dtl_rec.srl_from_tbl(j), eff_dtl_rec.srl_to_tbl(j)) AND
5021 (eff_dtl_rec.mID_tbl(j) IS NULL OR eff_dtl_rec.mID_tbl(j) = l_inst_manufacturer_id) AND
5022 (eff_dtl_rec.mdate_from_tbl(j) IS NULL OR eff_dtl_rec.mdate_from_tbl(j) <= l_mfg_date_tbl(i)) AND
5023 (eff_dtl_rec.mdate_to_tbl(j) IS NULL OR eff_dtl_rec.mdate_to_tbl(j) >= l_mfg_date_tbl(i)) AND
5024 (eff_dtl_rec.c_code_tbl(j) IS NULL OR eff_dtl_rec.c_code_tbl(j) = l_inst_country_code)) THEN
5025 l_valid_mr_flag := 'Y';
5026 EXIT;
5027 ELSE
5028 l_valid_mr_flag := 'N';
5029 END IF;
5030 ELSE
5031 IF (check_sn_outside(l_serial_num_tbl(i), eff_dtl_rec.srl_from_tbl(j), eff_dtl_rec.srl_to_tbl(j)) AND
5032 (eff_dtl_rec.mID_tbl(j) IS NULL OR eff_dtl_rec.mID_tbl(j) = l_inst_manufacturer_id) AND
5033 (eff_dtl_rec.mdate_from_tbl(j) IS NULL OR eff_dtl_rec.mdate_from_tbl(j) <= l_mfg_date_tbl(i)) AND
5034 (eff_dtl_rec.mdate_to_tbl(j) IS NULL OR eff_dtl_rec.mdate_to_tbl(j) >= l_mfg_date_tbl(i)) AND
5035 (eff_dtl_rec.c_code_tbl(j) IS NULL OR eff_dtl_rec.c_code_tbl(j) = l_inst_country_code)) THEN
5036 l_valid_mr_flag := 'Y';
5037 ELSE
5038 l_valid_mr_flag := 'N';
5039 EXIT;
5040 END IF;
5041 END IF;
5042 END LOOP;
5043
5044 END IF; -- eff_dtl_rec.eflag_tbl.count > 0
5045
5046 -- added checks l_extn_exists_flag and l_valid_mr_flag to fix bug# 9434441 to filter number of calls to check_effectivity_ext_details.
5047 IF (l_valid_mr_flag = 'Y') AND (l_extn_exists_flag = 'Y') AND (NOT CHECK_EFFECTIVITY_EXT_DETAILS(l_instance_id_tbl(i),l_mr_effectivity_id_tbl(i)))
5048 THEN
5049 l_valid_mr_flag := 'N';
5050 END IF;
5051
5052 --amsriniv. Begin
5053 IF (l_valid_mr_flag = 'Y' AND l_mr_pc_node_id_tbl(i) IS NOT NULL) THEN
5054 -- check for units first.
5055 OPEN validate_unit_node_csr(l_instance_id_tbl(i), l_mr_pc_node_id_tbl(i));
5056 FETCH validate_unit_node_csr INTO l_junk;
5057 IF (validate_unit_node_csr%FOUND) THEN
5058 l_valid_mr_flag := 'Y';
5059 ELSE
5060 OPEN validate_itm_node_csr(l_instance_id_tbl(i), l_mr_pc_node_id_tbl(i));
5061 FETCH validate_itm_node_csr INTO l_junk;
5062 IF (validate_itm_node_csr%NOTFOUND) THEN
5063 l_valid_mr_flag := 'N';
5064 END IF;
5065 CLOSE validate_itm_node_csr;
5066 END IF;
5067 CLOSE validate_unit_node_csr;
5068 END IF;
5069 --amsriniv End
5070
5071 IF l_debug = 'Y' THEN
5072 AHL_DEBUG_PUB.debug('MR ID:EFF ID:PC NODE ID:INST ID:Valid Flag:' || l_mr_header_id_tbl(i) || ':' || l_mr_effectivity_id_tbl(i) || ':' || l_mr_pc_node_id_tbl(i) || ':' || l_instance_id_tbl(i) || ':' || l_valid_mr_flag, l_debug_prefix);
5073 END IF;
5074
5075 -- add row to x_applicable_mr_tbl
5076 IF (l_valid_mr_flag = 'Y') THEN
5077 -- read MR details only once.
5078 IF (l_prev_mr_header_id IS NULL OR l_prev_mr_header_id <> l_mr_header_id_tbl(i)) THEN
5079 OPEN get_mr_attri(l_mr_header_id_tbl(i));
5080 FETCH get_mr_attri INTO l_get_mr_attri;
5081 IF get_mr_attri%NOTFOUND THEN
5082 CLOSE get_mr_attri;
5083 FND_MESSAGE.set_name('AHL','AHL_FMP_INVALID_MR');
5084 FND_MSG_PUB.add;
5085 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5086 ELSE
5087 l_prev_mr_header_id := l_mr_header_id_tbl(i);
5088 CLOSE get_mr_attri;
5089 END IF; -- get_mr_attri%NOTFOUND
5090 END IF; -- l_prev_mr_header_id
5091
5092 IF ((p_include_doNotImplmt <> 'N') OR
5093 (l_get_mr_attri.implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT')) THEN
5094 x_applicable_mr_tbl(l_index).mr_header_id := l_mr_header_id_tbl(i);
5095 x_applicable_mr_tbl(l_index).mr_effectivity_id := l_mr_effectivity_id_tbl(i);
5096 x_applicable_mr_tbl(l_index).item_instance_id := l_instance_id_tbl(i);
5097 x_applicable_mr_tbl(l_index).repetitive_flag := l_get_mr_attri.repetitive_flag;
5098 x_applicable_mr_tbl(l_index).show_repetitive_code := l_get_mr_attri.show_repetitive_code;
5099 -- commented for SB Enh
5100 --x_applicable_mr_tbl(l_index).preceding_mr_header_id := l_get_mr_attri.preceding_mr_header_id;
5101 x_applicable_mr_tbl(l_index).preceding_mr_header_id := null;
5102 x_applicable_mr_tbl(l_index).copy_accomplishment_flag := l_get_mr_attri.copy_accomplishment_flag;
5103 x_applicable_mr_tbl(l_index).implement_status_code := l_get_mr_attri.implement_status_code;
5104 x_applicable_mr_tbl(l_index).descendent_count := l_get_mr_attri.descendent_count;
5105 IF l_debug = 'Y' THEN
5106 AHL_DEBUG_PUB.debug('AHL_APPLICABLE_MRS Attributes : mr_header_id ' ||
5107 x_applicable_mr_tbl(l_index).mr_header_id || ' mr_effectivity_id ' ||
5108 x_applicable_mr_tbl(l_index).mr_effectivity_id || ' item_instance_id ' ||
5109 x_applicable_mr_tbl(l_index).item_instance_id || ' repetitive_flag ' ||
5110 x_applicable_mr_tbl(l_index).repetitive_flag || ' show_repetitive_code ' ||
5111 x_applicable_mr_tbl(l_index).show_repetitive_code || ' preceding_mr_header_id ' ||
5112 x_applicable_mr_tbl(l_index).preceding_mr_header_id || ' copy_accomplishment_flag ' ||
5113 x_applicable_mr_tbl(l_index).copy_accomplishment_flag || ' implement_status_code ' ||
5114 x_applicable_mr_tbl(l_index).implement_status_code || ' descendent_count ' ||
5115 x_applicable_mr_tbl(l_index).descendent_count, l_debug_prefix);
5116 END IF;
5117 l_index := l_index+1;
5118 END IF;
5119
5120 END IF; -- l_valid_mr_flag
5121
5122 END LOOP; -- l_mr_effectivity_id_tbl
5123
5124 -- reset tables and get the next batch of mr effectivities.
5125 l_mr_header_id_tbl.delete;
5126 l_mr_effectivity_id_tbl.delete;
5127 l_mr_pc_node_id_tbl.delete;
5128 l_instance_id_tbl.delete;
5129 l_mfg_date_tbl.delete;
5130 l_serial_num_tbl.delete;
5131
5132 END LOOP;
5133 -- set l_process_loop value to process next set of rows.
5134 IF (l_process_loop = 1) THEN
5135 IF (get_inst_mr_inv_csr%ISOPEN) THEN
5136 CLOSE get_inst_mr_inv_csr;
5137 END IF;
5138
5139 IF (p_components_flag = 'Y') THEN
5140 l_process_loop := 2;
5141 ELSIF (l_appl_inst_exists_flag) THEN
5142 l_process_loop := 3;
5143 ELSE
5144 EXIT;
5145 END IF;
5146
5147 ELSIF (l_process_loop = 2) THEN
5148 IF (get_comp_mr_inv_csr%ISOPEN) THEN
5149 CLOSE get_comp_mr_inv_csr;
5150 END IF;
5151
5152 IF (l_appl_inst_exists_flag) THEN
5153 l_process_loop := 3;
5154 ELSE
5155 EXIT;
5156 END IF;
5157
5158 ELSIF (l_process_loop = 3) THEN
5159 IF (get_posn_mr_csr%ISOPEN) THEN
5160 CLOSE get_posn_mr_csr;
5161 END IF;
5162
5163 EXIT;
5164
5165 END IF;
5166
5167 END LOOP;
5168
5169 DELETE FROM ahl_applicable_instances;
5170
5171 IF l_debug = 'Y' THEN
5172 AHL_DEBUG_PUB.debug('End private API: AHL_FMP_PVT.GET_MR_APPLICABILITY', l_debug_prefix);
5173 AHL_DEBUG_PUB.disable_debug;
5174 END IF;
5175
5176
5177 EXCEPTION
5178
5179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5181 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5182 p_count => x_msg_count,
5183 p_data => x_msg_data);
5184 IF l_debug = 'Y' THEN
5185 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
5186 'UNEXPECTED ERROR IN PRIVATE:' );
5187 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_MR_APPLICABILITY', l_debug_prefix);
5188 AHL_DEBUG_PUB.disable_debug;
5189 END IF;
5190
5191 WHEN FND_API.G_EXC_ERROR THEN
5192 x_return_status := FND_API.G_RET_STS_ERROR;
5193 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5194 p_count => x_msg_count,
5195 p_data => x_msg_data);
5196 IF l_debug = 'Y' THEN
5197 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
5198 'ERROR IN PRIVATE:' );
5199 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_MR_APPLICABILITY', l_debug_prefix);
5200 AHL_DEBUG_PUB.disable_debug;
5201 END IF;
5202
5203 WHEN OTHERS THEN
5204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5205 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5206 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_FMP_PVT',
5207 p_procedure_name => 'GET_MR_APPLICABILITY',
5208 p_error_text => SUBSTRB(SQLERRM,1,240));
5209 END IF;
5210 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5211 p_count => x_msg_count,
5212 p_data => x_msg_data);
5213 IF l_debug = 'Y' THEN
5214 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
5215 'OTHER ERROR IN PRIVATE:' );
5216 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_MR_APPLICABILITY', l_debug_prefix);
5217 AHL_DEBUG_PUB.disable_debug;
5218 END IF;
5219 END GET_MR_APPLICABILITY;
5220
5221
5222 END AHL_FMP_PVT; -- Package Body
[Home] [Help]