1 PACKAGE BODY AHL_UMP_SMRINSTANCE_PVT AS
2 /* $Header: AHLVSMRB.pls 120.9.12010000.2 2008/12/27 18:00:29 sracha ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UMP_SMRINSTANCE_PVT';
5
6 -- FND Logging Constants
7 G_DEBUG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 G_DEBUG_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
9 G_DEBUG_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10 G_DEBUG_UEXP CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11
12 -------------------------------
13 -- Declare Local Procedures --
14 -------------------------------
15 PROCEDURE populate_instances(
16 p_module_type IN VARCHAR2,
17 p_search_mr_instance_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_MRInstance_Rec_Type);
18
19
20 PROCEDURE populate_dependent_instances(
21 p_module_type IN VARCHAR2,
22 p_search_mr_instance_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_MRInstance_Rec_Type);
23
24 TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
25
26 -------------------------------------
27 -- End Local Procedures Declaration--
28 -------------------------------------
29
30 ------------------------
31 -- Define Procedures --
32 ------------------------
33 --------------------------------------------------------------------------------------------
34 -- Start of Comments --
35 -- Procedure name : Search_MR_Instances
36 -- Type : Private
37 -- Function : This procedure fetches all the MR Instances based both at the instance level
38 -- and the item level for the given search criteria.
39 -- Pre-reqs :
40 -- Parameters :
41 --
42 -- Standard IN Parameters :
43 -- p_api_version IN NUMBER Required
44 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
45 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
46 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
47 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
48 -- Based on this flag, the API will set the default attributes.
49 -- p_module_type IN VARCHAR2 Default NULL
50 -- This will be null.
51 -- Standard OUT Parameters :
52 -- x_return_status OUT NOCOPY VARCHAR2 Required
53 -- x_msg_count OUT NOCOPY NUMBER Required
54 -- x_msg_data OUT NOCOPY VARCHAR2 Required
55 --
56 -- Search_MR_Instances Parameters :
57 -- p_start_row IN NUMBER Required
58 -- The row from which the search results table should be displayed.
59 -- p_rows_per_page IN NUMBER Required
60 -- The number of rows to be displayed per page.
61 -- p_search_mr_instance_rec IN Search_MRInstance_Rec_Type Required
62 -- The search criteria based on which the query needs to be run to
63 -- return the MR Instances.
64 -- x_results_mr_instance_tbl OUT NOCOPY Results_MRInstance_Tbl_Type Required
65 -- List of all the MR Instances which match the search criteria entered.
66 -- x_results_count OUT NOCOPY NUMBER Required
67 -- The total count of the results returned from the entered search criteria.
68 --
69 -- Version :
70 -- Initial Version 1.0
71 -- Sunil Kumar redesigned and recoded. Performance optimized.
72 --
73 -- End of Comments.
74 --------------------------------------------------------------------------------------------
75
76 PROCEDURE Search_MR_Instances
77 (
78 p_api_version IN NUMBER,
79 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
80 p_commit IN VARCHAR2 := FND_API.G_FALSE,
81 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
82 p_default IN VARCHAR2 := FND_API.G_TRUE,
83 p_module_type IN VARCHAR2 := NULL,
84 p_start_row IN NUMBER,
85 p_rows_per_page IN NUMBER,
86 p_search_mr_instance_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_MRInstance_Rec_Type,
87 x_results_mr_instance_tbl OUT NOCOPY AHL_UMP_SMRINSTANCE_PVT.Results_MRInstance_Tbl_Type,
88 x_results_count OUT NOCOPY NUMBER,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_count OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2 ) IS
92
93 l_api_version CONSTANT NUMBER := 1.0;
94 l_api_name CONSTANT VARCHAR2(30) := 'Search_MR_Instances';
95
96 -- Local Variable for the sql string.
97 l_sql_string VARCHAR2(30000);
98 l_get_items_sql VARCHAR2(4000);
99 -- Local Variables for Instance Level Search queries
100 l_all_csi_items_sql VARCHAR2(4000);
101 l_get_csi_ii_id_sql VARCHAR2(4000);
102 l_unit_effectivity_id number;
103 --Local Variable for iterating through the result set.
104 l_counter NUMBER;
105 --Local Variable for triggering the record to be picked.
106 l_pick_record_flag boolean;
107 --Local Variable for getting the row count.
108 row_count NUMBER;
109 --Fix for Bug 2745891
110 l_early_exit_status boolean;
111 -- Bind variable index and table
112 l_bind_index NUMBER;
113 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
114 -- dynamic cursor
115 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
116 -- Logging purposes
117 l_sql_segment_count NUMBER;
118 -- Added for deferral details.
119 CURSOR ahl_defer_to_ue_csr(p_unit_effectivity_id IN NUMBER) IS
120 SELECT unit_effectivity_id
121 FROM ahl_unit_effectivities_b
122 WHERE defer_from_ue_id = p_unit_effectivity_id
123 AND rownum < 2;
124 -- cursor to actually fecth details
125 -- anraj: added UMP.cs_incident_id, UMP.cs_incident_number for bug#4133332
126 CURSOR ump_details_csr(p_unit_effectivity_id IN NUMBER) IS
127 SELECT UMP.program_type, UMP.Title, UMP.part_number, UMP.serial_number,
128 -- R12: Fix for bug# 5231770.
129 -- Due to CSI counter schema changes, the latest net_reading is
130 -- no longer available in csi_cp_counters_v. Net_reading will be
131 -- queried seperately to calculate uom_remain.
132 -- In this cursor, UMP.due_counter_value will be retrieved instead.
133 --(UMP.due_counter_value - nvl(UMP.net_reading,0)) uom_remain,
134 UMP.due_counter_value uom_remain,
135 UMP.counter_name, UMP.earliest_due_date, UMP.due_date, UMP.latest_due_date,
136 UMP.tolerance, UMP.status_code, UMP.status, UMP.originator_title, UMP.dependant_title,
137 UMP.unit_effectivity_id, UMP.mr_header_id, UMP.csi_item_instance_id, UMP.instance_number,
138 UMP.mr_interval_id, UMP.unit_name, UMP.program_title, UMP.contract_number,
139 UMP.defer_from_ue_id, UMP.object_type, UMP.counter_id, UMP.MANUALLY_PLANNED_FLAG,
140 UMP.MANUALLY_PLANNED_DESC,
141 UMP.cs_incident_id, UMP.cs_incident_number,
142 -- added for bug# 6530920.
143 UMP.orig_ue_instance_id
144 FROM ahl_unit_effectivities_v UMP
145 WHERE UMP.unit_effectivity_id = p_unit_effectivity_id;
146
147 -- Added to fix bug# 2780716.
148 l_counter_id NUMBER;
149
150 -- to get uom remain from a counter_id.
151 -- Changed for R12 CSI Counter changes.
152 CURSOR ump_ctr_name_csr (p_counter_id IN NUMBER) IS
153 /* modified for uptake of IB changes 7374316.
154 SELECT cc.counter_template_name counter_name,
155 nvl(cv.net_reading,0) net_reading
156 FROM csi_counter_values_v cv, csi_counters_vl cc
157 WHERE cv.counter_id = cc.counter_id
158 AND cv.counter_id = p_counter_id
159 AND rownum < 2;
160 */
161
162 SELECT cc.counter_template_name counter_name,
163 (select ccr.net_reading from csi_counter_readings ccr
164 where ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
165 and nvl(ccr.disabled_flag,'N') = 'N')
166 FROM csi_counters_vl cc
167 WHERE cc.counter_id = p_counter_id;
168
169 -- Added to fix bug number 3693957
170 l_service_req_id NUMBER;
171 l_service_req_num VARCHAR2(64);
172 l_service_req_date DATE;
173
174 l_scheduled_date DATE;
175 l_visit_number VARCHAR2(80);
176
177 --PDOKI Added for ER# 6333770
178 l_visit_id NUMBER;
179
180 /*
181 -- 11.5.10CU2: Ignore Simulated visits.
182 CURSOR ahl_visit_csr(p_ue_id IN NUMBER) IS
183 SELECT vst.start_date_time, vst.visit_number
184 FROM ahl_visit_tasks_b tsk,
185 (select vst1.* from
186 ahl_visits_b vst1, ahl_simulation_plans_b sim
187 where vst1.simulation_plan_id = sim.simulation_plan_id
188 and sim.primary_plan_flag = 'Y'
189 UNION ALL
190 select vst1.* from
191 ahl_visits_b vst1
192 where simulation_plan_id IS NULL)vst
193 WHERE vst.visit_id = tsk.visit_id
194 AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
195 AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
196 AND tsk.unit_effectivity_id = p_ue_id;
197 */
198 --amsriniv ER 6116245 Begin
199 CURSOR ahl_visit_csr(p_ue_id IN NUMBER, p_visit_num IN VARCHAR2, p_visit_org_name IN VARCHAR2, p_visit_dept_name IN VARCHAR2) IS
200 SELECT vst.start_date_time, vst.visit_number, vst.visit_id
201 FROM ahl_visit_tasks_b tsk,
202 (select vst1.* from
203 ahl_visits_b vst1, ahl_simulation_plans_b sim
204 where vst1.simulation_plan_id = sim.simulation_plan_id
205 and sim.primary_plan_flag = 'Y'
206 UNION ALL
207 select vst1.* from
208 ahl_visits_b vst1
209 where simulation_plan_id IS NULL)vst,
210 hr_all_organization_units hrou,
211 bom_departments bdpt
212 WHERE vst.visit_id = tsk.visit_id
213 AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
214 AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
215 AND tsk.unit_effectivity_id = p_ue_id
216 AND vst.organization_id = hrou.organization_id(+)
217 AND ((vst.organization_id IS NULL AND p_visit_org_name IS NULL) OR upper(hrou.name) LIKE NVL(upper(p_visit_org_name),upper(hrou.name)))
218 AND vst.department_id = bdpt.department_id(+)
219 AND ((vst.department_id IS NULL AND p_visit_dept_name IS NULL) OR upper(bdpt.description) LIKE NVL(upper(p_visit_dept_name),upper(bdpt.description)))
220 AND vst.visit_number like nvl(p_visit_num,vst.visit_number);
221 --amsriniv ER 6116245 End
222
223 -- R12: MEL/CDL changes.
224 -- get deferral status for deferred UMP rows.
225 cursor get_deferral_sts (p_ue_id in number) IS
226 select decode(unit_deferral_type, 'MEL', 'MEL ' || fk.meaning,
227 'CDL', 'CDL ' || fk.meaning,
228 fk.meaning) deferral_meaning
229 from ahl_unit_deferrals_b, fnd_lookup_values_vl fk
230 where unit_effectivity_id = p_ue_id
231 and fk.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
232 and fk.lookup_code = approval_status_code;
233
234 -- get deferral status for open UMP rows.
235 cursor get_open_deferral_sts (p_ue_id in number) IS
236 select fk.meaning defer_meaning
237 from ahl_unit_deferrals_b udf, fnd_lookup_values_vl fk
238 where udf.unit_effectivity_id = p_ue_id
239 and fk.lookup_code = decode(udf.approval_status_code, 'DRAFT',
240 'DEFERRAL_DRAFT',udf.approval_status_code)
241 and fk.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
242 and udf.unit_deferral_type = 'DEFERRAL';
243
244 -- get mel/cdl status for open UMP rows.
245 cursor get_open_mel_cdl_sts (p_ue_id in number) IS
246 select unit_deferral_type || ' ' || fk.meaning defer_meaning
247 from ahl_unit_deferrals_b udf, fnd_lookup_values_vl fk
248 where udf.unit_effectivity_id = p_ue_id
249 and fk.lookup_code = decode(udf.approval_status_code, 'DEFERRED',
250 'APPROVED',udf.approval_status_code)
251 and fk.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
252 and udf.unit_deferral_type IN ('MEL','CDL') ;
253
254 --l_defer_code VARCHAR2(30);
255 l_defer_mean VARCHAR2(80);
256
257 -- R12: Fix for bug# 5231770.
258 l_due_counter_value NUMBER;
259 l_net_reading NUMBER;
260 l_approval_status_code VARCHAR2(30);
261 l_unit_deferral_type VARCHAR2(30);
262
263 -- to get the net counter reading for a counter name and item instance.
264 -- modified for uptake of IB fix. Refer bug 7374316.
265 CURSOR get_net_reading_csr (p_csi_item_instance_id IN NUMBER,
266 p_ctr_template_name IN VARCHAR2)
267 IS
268 SELECT (select ccr.net_reading from csi_counter_readings ccr
269 where ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
270 and nvl(ccr.disabled_flag,'N') = 'N') net_reading
271 FROM CSI_COUNTER_ASSOCIATIONS CCA, CSI_COUNTERS_VL CC
272 WHERE CCA.COUNTER_ID = CC.COUNTER_ID
273 AND CCA.SOURCE_OBJECT_ID = p_csi_item_instance_id
274 AND CCA.SOURCE_OBJECT_CODE = 'CP'
275 AND CC.COUNTER_TEMPLATE_NAME = p_ctr_template_name;
276 /*
277 SELECT nvl(CV.NET_READING, 0)
278 FROM CSI_COUNTER_READINGS CV, CSI_COUNTER_ASSOCIATIONS CCA, CSI_COUNTERS_VL CC
279 WHERE CCA.SOURCE_OBJECT_CODE = 'CP'
280 AND CCA.COUNTER_ID = CV.COUNTER_ID
281 --AND CC.COUNTER_ID = CV.COUNTER_ID
282 AND CC.CTR_VAL_MAX_SEQ_NO = CV.counter_value_id
283 AND CCA.SOURCE_OBJECT_ID = p_csi_item_instance_id
284 AND CC.COUNTER_TEMPLATE_NAME = p_ctr_template_name;
285 --ORDER BY CV.VALUE_TIMESTAMP DESC;
286 */
287
288 -- added for bug# 6530920.
289 l_orig_ue_instance_id NUMBER;
290 l_buffer_limit number := 500;
294 l_mr_select_sql_string VARCHAR2(4000);
291 l_ue_id_tbl nbr_tbl_type;
292
293 l_is_pm_installed VARCHAR2(3) := AHL_UTIL_PKG.is_pm_installed();
295 l_select_sql_string VARCHAR2(4000);
296 l_mr_select_flag BOOLEAN;
297 l_nr_select_sql_string VARCHAR2(4000);
298
299 BEGIN
300 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
301 fnd_log.string
302 (
303 G_DEBUG_PROC,
304 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances.begin',
305 'At the start of PLSQL procedure'
306 );
307 END IF;
308 -- Standard start of API savepoint
309 SAVEPOINT Search_MR_Instances_Pvt;
310
311 -- Standard call to check for call compatibility
312 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
313 G_PKG_NAME) THEN
314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315 END IF;
316
317 -- Initialize message list if p_init_msg_list is set to TRUE
318 IF FND_API.To_Boolean(p_init_msg_list) THEN
319 FND_MSG_PUB.Initialize;
320 END IF;
321
322 -- Initialize API return status to success
323 x_return_status := FND_API.G_RET_STS_SUCCESS;
324
325
326 -- other initilizations needed for API
327 l_early_exit_status := TRUE;
328 l_bind_index := 1;
329 -- Logging input
330 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
331 fnd_log.string
332 (
333 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
334 'p_search_mr_instance_rec.unit_name :' || p_search_mr_instance_rec.unit_name
335 );
336 fnd_log.string
337 (
338 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
339 'p_search_mr_instance_rec.part_number :' || p_search_mr_instance_rec.part_number
340 );
341 fnd_log.string
342 (
343 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
344 'p_search_mr_instance_rec.serial_number :' || p_search_mr_instance_rec.serial_number
345 );
346 fnd_log.string
347 (
348 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
349 'p_search_mr_instance_rec.sort_by :' || p_search_mr_instance_rec.sort_by
350 );
351 fnd_log.string
352 (
353 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
354 'p_search_mr_instance_rec.mr_status :' || p_search_mr_instance_rec.mr_status
355 );
356 fnd_log.string
357 (
358 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
359 'p_search_mr_instance_rec.program_type :' || p_search_mr_instance_rec.program_type
360 );
361 fnd_log.string
362 (
363 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
364 'p_search_mr_instance_rec.due_from :' || p_search_mr_instance_rec.due_from
365 );
366 fnd_log.string
367 (
368 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
369 'p_search_mr_instance_rec.due_to :' || p_search_mr_instance_rec.due_to
370 );
371 fnd_log.string
372 (
373 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
374 'p_search_mr_instance_rec.show_tolerance :' || p_search_mr_instance_rec.show_tolerance
375 );
376 fnd_log.string
377 (
378 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
379 'p_search_mr_instance_rec.components_flag :' || p_search_mr_instance_rec.components_flag
380 );
381 fnd_log.string
382 (
383 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
384 'p_search_mr_instance_rec.repetitive_flag :' || p_search_mr_instance_rec.repetitive_flag
385 );
386 fnd_log.string
387 (
388 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
389 'p_search_mr_instance_rec.contract_number :' || p_search_mr_instance_rec.contract_number
390 );
391 fnd_log.string
392 (
393 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
394 'p_search_mr_instance_rec.contract_modifier :' || p_search_mr_instance_rec.contract_modifier
395 );
396 fnd_log.string
397 (
398 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
399 'p_search_mr_instance_rec.contract_number :' || p_search_mr_instance_rec.contract_number
400 );
401 fnd_log.string
402 (
403 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
404 'p_search_mr_instance_rec.service_line_id :' || p_search_mr_instance_rec.service_line_id
405 );
406 fnd_log.string
407 (
408 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
409 'p_search_mr_instance_rec.service_line_num :' || p_search_mr_instance_rec.service_line_num
410 );
411 fnd_log.string
412 (
413 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
414 'p_search_mr_instance_rec.program_id :' || p_search_mr_instance_rec.program_id
415 );
419 'p_search_mr_instance_rec.program_title :' || p_search_mr_instance_rec.program_title
416 fnd_log.string
417 (
418 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
420 );
421 fnd_log.string
422 (
423 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
424 'p_search_mr_instance_rec.show_groupmr :' || p_search_mr_instance_rec.show_groupmr
425 );
426 fnd_log.string
427 (
428 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
429 'p_search_mr_instance_rec.object_type :' || p_search_mr_instance_rec.object_type
430 );
431
432 fnd_log.string
433 (
434 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
435 'p_search_mr_instance_rec.search_for_type :' || p_search_mr_instance_rec.search_for_type
436 );
437
438 fnd_log.string
439 (
440 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
441 'p_search_mr_instance_rec.INCIDENT_TYPE_ID :' || p_search_mr_instance_rec.INCIDENT_TYPE_ID
442 );
443
444 fnd_log.string
445 (
446 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
447 'p_search_mr_instance_rec.SERVICE_REQ_NUM :' || p_search_mr_instance_rec.SERVICE_REQ_NUM
448 );
449 END IF;
450
451 l_mr_select_flag := FALSE;
452
453 -- validate input
454 -- if both MR title and (INCIDENT_TYPE_ID and/or SERVICE_REQ_NUM) entered, raise error.
455 IF (p_search_mr_instance_rec.mr_title is NOT NULL) AND
456 (p_search_mr_instance_rec.INCIDENT_TYPE_ID IS NOT NULL OR p_search_mr_instance_rec.SERVICE_REQ_NUM IS NOT NULL)
457 THEN
458 FND_MESSAGE.Set_Name('AHL','AHL_UMP_MR_SERQ_INPUT');
459 FND_MSG_PUB.ADD;
460 RAISE FND_API.G_EXC_ERROR;
461 END IF;
462
463 --start fix for bug#7327283
464 --SELECT Clause
465 --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_V UMP WHERE 0=0 ';
466
467 --fix for bug# 7562008. Added hint /*+ dynamic_sampling(AAI1 4) */ to queries based on AHL_APPLICABLE_INSTANCES table
468 --as per feedback from Application Performance team.
469 IF (l_is_pm_installed = 'Y') THEN
470 l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_V UMP WHERE 0=0 ';
471 ELSE
472 IF (p_search_mr_instance_rec.unit_name is NOT NULL OR nvl(p_search_mr_instance_rec.part_number,'%')
473 <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
474 --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= ''AHL'' ';
475 l_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1 ';
476 l_select_sql_string := l_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id ';
477
478 l_mr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1, AHL_MR_HEADERS_B MR';
479 l_mr_select_sql_string := l_mr_select_sql_string || ' WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id AND UMP.mr_header_id = MR.mr_header_id ';
480
481 l_bindvar_tbl(l_bind_index) := ltrim(rtrim(fnd_profile.value('AHL_APPLN_USAGE')));
482 l_bind_index := l_bind_index + 1;
483
484 IF p_search_mr_instance_rec.INCIDENT_TYPE_ID IS NOT NULL THEN
485 IF nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%' THEN
486 l_nr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP , AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI, CS_INCIDENT_TYPES_VL CIT ';
487 l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id AND ';
488 l_nr_select_sql_string := l_nr_select_sql_string || 'UMP.cs_incident_id = CI.incident_id AND CI.incident_number like :CS_SERVC_NUM AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';
489
490 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
491 l_bind_index := l_bind_index + 1;
492
493 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
494 l_bind_index := l_bind_index + 1;
495
496 ELSE -- servc req num is null.
497 l_nr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI,CS_INCIDENT_TYPES_VL CIT ';
498 l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id AND UMP.cs_incident_id = CI.incident_id';
499 l_nr_select_sql_string := l_nr_select_sql_string || ' AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';
500 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
501 l_bind_index := l_bind_index + 1;
502 END IF; -- p_search_mr_instance_rec.SERVICE_REQ_NUM
503 ELSE -- INCIDENT_TYPE_ID is null
507 l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id ';
504 IF (nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%') THEN
505 -- servc req is not null
506 l_nr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI ';
508 l_nr_select_sql_string := l_nr_select_sql_string || 'AND UMP.cs_incident_id = CI.incident_id AND CI.incident_number like :CS_SERVC_NUM ';
509 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
510 l_bind_index := l_bind_index + 1;
511 ELSE
512 -- both are null
513 null;
514 END IF;
515 END IF; -- p_search_mr_instance_rec.INCIDENT_TYPE_ID
516 ELSE -- p_search_mr_instance_rec.unit_name
517 l_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= :APPL_USG_CODE ';
518
519 l_mr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_MR_HEADERS_B MR WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.mr_header_id = MR.mr_header_id ';
520 l_bindvar_tbl(l_bind_index) := ltrim(rtrim(fnd_profile.value('AHL_APPLN_USAGE')));
521 l_bind_index := l_bind_index + 1;
522 IF p_search_mr_instance_rec.INCIDENT_TYPE_ID IS NOT NULL THEN
523 IF nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%' THEN
524 l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, CS_INCIDENTS_ALL_B CI, CS_INCIDENT_TYPES_VL CIT WHERE UMP.application_usg_code= :APPL_USG_CODE ';
525 l_nr_select_sql_string := l_nr_select_sql_string || ' AND UMP.cs_incident_id = CI.incident_id AND CI.incident_number like :CS_SERVC_NUM AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';
526
527 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
528 l_bind_index := l_bind_index + 1;
529
530 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
531 l_bind_index := l_bind_index + 1;
532
533 ELSE -- servc req num is null.
534 l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,CS_INCIDENTS_ALL_B CI,CS_INCIDENT_TYPES_VL CIT ';
535 l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.cs_incident_id = CI.incident_id ';
536 l_nr_select_sql_string := l_nr_select_sql_string || 'AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';
537
538 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
539 l_bind_index := l_bind_index + 1;
540 END IF; -- p_search_mr_instance_rec.SERVICE_REQ_NUM
541 ELSE -- INCIDENT_TYPE_ID is null
542 IF (nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%') THEN
543 -- servc req is not null
544 l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,CS_INCIDENTS_ALL_B CI ';
545 l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.cs_incident_id = CI.incident_id AND CI.incident_number like :CS_SERVC_NUM ';
546
547 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
548 l_bind_index := l_bind_index + 1;
549 ELSE
550 -- both are null
551 null;
552 END IF;
553 END IF; -- p_search_mr_instance_rec.INCIDENT_TYPE_ID
554 END IF; --p_search_mr_instance_rec.unit_name
555 END IF;
556
557 --end fix for bug#7327283
558
559 --MR Title Check
560 IF p_search_mr_instance_rec.mr_title is NOT NULL THEN
561 IF (l_is_pm_installed = 'Y') THEN
562 l_sql_string := l_sql_string || ' AND UPPER(UMP.TITLE) like :MR_TITLE ';
563 ELSE
564 l_sql_string := l_sql_string || ' AND UPPER(MR.TITLE) like :MR_TITLE ';
565 l_mr_select_flag := TRUE;
566 END IF;
567 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.mr_title);
568 l_bind_index := l_bind_index + 1;
569 END IF;
570
571 --Program Type
572 IF p_search_mr_instance_rec.program_type is NOT NULL THEN
573 /*
574 IF p_module_type = 'VWP' THEN
575 --If the caller is 'VWP'
576 l_sql_string := l_sql_string || ' AND UMP.OBJECT_TYPE = ''MR'' ';
577 ELSIF p_search_mr_instance_rec.program_type = 'NON-ROUTINE' THEN
578 --If the Prgram_type_code is NON_ROUTINE
579 l_sql_string := l_sql_string || ' AND UMP.OBJECT_TYPE IN (''SR'',''MR'') ';
580 END IF;
581 */
582 IF (l_is_pm_installed = 'Y') THEN
583 l_sql_string := l_sql_string || ' AND UMP.PROGRAM_TYPE_CODE like :FMP_PROGRAM_TYPE ';
584 ELSE
585 IF (p_search_mr_instance_rec.program_type = 'NON-ROUTINE' AND l_mr_select_flag = FALSE AND
586 l_nr_select_sql_string IS NULL) THEN
587 --If the Prgram_type_code is NON_ROUTINE
588 l_sql_string := l_sql_string || ' AND UMP.OBJECT_TYPE IN (''SR'',''MR'') ';
589 -- fix for bug#7327283
590 l_sql_string := l_sql_string || ' AND DECODE(UMP.OBJECT_TYPE,''SR'',''NON-ROUTINE'',
594 l_bind_index := l_bind_index + 1;
591 (select MR.program_type_code from AHL_MR_HEADERS_B MR where MR.mr_header_id = UMP.mr_header_id)) like :FMP_PROGRAM_TYPE ';
592
593 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.program_type;
595
596 ELSIF (p_search_mr_instance_rec.program_type = 'NON-ROUTINE' AND l_nr_select_sql_string IS NOT NULL) THEN
597 null; -- filter not required.
598 ELSIF (l_nr_select_sql_string IS NULL) THEN
599 -- only MRs to be selected
600 l_sql_string := l_sql_string || ' AND MR.PROGRAM_TYPE_CODE like :FMP_PROGRAM_TYPE ';
601 l_mr_select_flag := TRUE;
602
603 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.program_type;
604 l_bind_index := l_bind_index + 1;
605 END IF;
606 END IF;
607 END IF;
608
609 --MR Status Check
610 IF (p_search_mr_instance_rec.mr_status is NOT NULL AND UPPER(p_search_mr_instance_rec.mr_status) <> 'ALL') THEN
611 IF UPPER(p_search_mr_instance_rec.mr_status) IN ('OPEN','SCHEDULED','UNSCHEDULED') THEN
612 --l_sql_string := l_sql_string || ' AND nvl(UMP.status_code,''x'') NOT IN (''ACCOMPLISHED'', ''EXCEPTION'', ''INIT-ACCOMPLISHED'', ''TERMINATED'',''MR-TERMINATE'',''DEFERRED'', ''SR-CLOSED'')';
613 l_sql_string := l_sql_string || ' AND (UMP.status_code IS NULL OR UMP.status_code = ''INIT-DUE'') ';
614 ELSIF p_search_mr_instance_rec.mr_status IN ('DEFERRAL_PENDING',
615 'DEFERRAL_REJECTED',
616 'DRAFT')
617 THEN
618 l_sql_string := l_sql_string || ' AND (UMP.status_code IS NULL OR UMP.status_code = ''INIT-DUE'') ';
619 l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' FROM ahl_unit_deferrals_b WHERE unit_effectivity_id = UMP.unit_effectivity_id AND approval_status_code = :DEFERRAL_STATUS) ';
620 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.mr_status;
621 l_bind_index := l_bind_index + 1;
622 /*
623 ELSIF p_search_mr_instance_rec.mr_status like 'CDL%' THEN
624 l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' FROM ahl_unit_deferrals_b WHERE unit_effectivity_id = UMP.unit_effectivity_id AND unit_deferral_type = ''CDL'' AND approval_status_code = :CDL_STATUS) ';
625 l_bindvar_tbl(l_bind_index) := 'CDL:' || p_search_mr_instance_rec.mr_status;
626 l_bind_index := l_bind_index + 1;
627 ELSIF p_search_mr_instance_rec.mr_status like 'MEL%' THEN
628 l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' FROM ahl_unit_deferrals_b WHERE unit_effectivity_id = UMP.unit_effectivity_id AND unit_deferral_type = ''MEL'' AND approval_status_code = :MEL_STATUS) ';
629 l_bindvar_tbl(l_bind_index) := 'MEL:' || p_search_mr_instance_rec.mr_status;
630 l_bind_index := l_bind_index + 1;
631 */
632 ELSE
633 l_sql_string := l_sql_string || ' AND UMP.STATUS_CODE = :MR_STATUS ';
634 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.mr_status;
635 l_bind_index := l_bind_index + 1;
636 END IF;
637 END IF;
638
639 --Manually Planned Flag Check
640 IF (p_search_mr_instance_rec.search_for_type is NOT NULL AND p_search_mr_instance_rec.search_for_type <> 'ALL')
641 THEN
642 IF (p_search_mr_instance_rec.search_for_type = 'FORECASTED')
643 THEN
644 l_sql_string := l_sql_string || ' AND NVL(UMP.MANUALLY_PLANNED_FLAG, ''N'') = ''N'' ';
645 ELSIF (p_search_mr_instance_rec.search_for_type = 'MANUALLY_PLANNED')
646 THEN
647 l_sql_string := l_sql_string || ' AND UMP.MANUALLY_PLANNED_FLAG = ''Y'' ';
648 END IF;
649 END IF;
650
651
652 /* *********************************** BEGIN: SHOW TOLERANCE ****************************************** */
653 -- Show Tolerance is N
654 IF p_search_mr_instance_rec.show_tolerance ='N' THEN
655 --Due-From Date Check
656 IF p_search_mr_instance_rec.due_from is NOT NULL THEN
657 l_sql_string := l_sql_string || ' AND UMP.DUE_DATE >= :DUE_FROM1 ';
658 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
659 l_bind_index := l_bind_index + 1;
660 END IF;
661 --Due-To Date Check
662 IF p_search_mr_instance_rec.due_to is NOT NULL THEN
663 l_sql_string := l_sql_string || ' AND UMP.DUE_DATE <= :DUE_TO1 ';
664 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_to);
665 l_bind_index := l_bind_index + 1;
666 END IF;
667 ELSE -- Show Tolerance is Y
668 IF ( p_search_mr_instance_rec.due_from is NOT NULL AND p_search_mr_instance_rec.due_to is NOT NULL ) THEN
669 -- Both Due-From and Due-To Dates are there
670 --adivenka modified the following part of the query for bug# 4315128
671 l_sql_string := l_sql_string || ' AND (
672 (:DUE_FROM2 <= nvl(UMP.earliest_due_date, UMP.due_date) AND nvl(UMP.earliest_due_date, UMP.due_date) <= :DUE_TO2)
673 OR (nvl(UMP.earliest_due_date, UMP.due_date) <= :DUE_FROM3 AND :DUE_TO3 <= nvl(UMP.latest_due_date, UMP.due_date))
674 OR (:DUE_FROM4 <= nvl(UMP.latest_due_date, UMP.due_date) AND nvl(UMP.latest_due_date, UMP.due_date) <= :DUE_TO4)
675 )';
676 --adivenka changes end
677
678 -- due from2 and due to2
679 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
680 l_bind_index := l_bind_index + 1;
681 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_to);
685 l_bind_index := l_bind_index + 1;
682 l_bind_index := l_bind_index + 1;
683 -- due from3 and due to3
684 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
686 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_to);
687 l_bind_index := l_bind_index + 1;
688 -- due from4 and due to4
689 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
690 l_bind_index := l_bind_index + 1;
691 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_to);
692 l_bind_index := l_bind_index + 1;
693 ELSIF ( p_search_mr_instance_rec.due_from is NOT NULL AND p_search_mr_instance_rec.due_to is NULL ) THEN
694 -- Only Due-From Date is there and Due-To Date is NULL
695 --adivenka modified the following part of the query for bug# 4315128
696 l_sql_string := l_sql_string || ' AND ( ( ( :DUE_FROM2 <= nvl(UMP.earliest_due_date, UMP.due_date) )
697 )
698 OR
699 ( ( :DUE_FROM3 <= nvl(UMP.latest_due_date, UMP.due_date) )
700 ) )';
701 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
702 l_bind_index := l_bind_index + 1;
703 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
704 l_bind_index := l_bind_index + 1;
705 ELSIF ( p_search_mr_instance_rec.due_from is NULL AND p_search_mr_instance_rec.due_to is NOT NULL ) THEN
706 -- Only Due-To Date is there and Due-From Date is NULL
707 --adivenka modified the following part of the query for bug# 4315128
708 l_sql_string := l_sql_string || ' AND ( ( ( nvl(UMP.earliest_due_date, UMP.due_date) <= :DUE_TO2 )
709 )
710 OR
711 ( ( nvl(UMP.latest_due_date, UMP.due_date) <= :DUE_TO3 )
712 ) )';
713 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_to);
714 l_bind_index := l_bind_index + 1;
715 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_to);
716 l_bind_index := l_bind_index + 1;
717 END IF;--Case is ignored when Both Due-From and Due-To Dates are NOT here
718 END IF;
719 /* ***********************************END: SHOW TOLERANCE ****************************************** */
720
721 --Repetitive MR Flag Check
722 IF p_search_mr_instance_rec.repetitive_flag is NOT NULL THEN
723 IF UPPER(p_search_mr_instance_rec.repetitive_flag) <> 'Y' THEN
724 l_sql_string := l_sql_string || ' AND nvl(UMP.REPETITIVE_MR_FLAG,''x'') <> ''Y'' ';
725 END IF;
726 END IF;
727
728 -- Show GroupMR check.
729 IF p_search_mr_instance_rec.show_GroupMR is NOT NULL THEN
730 IF UPPER(p_search_mr_instance_rec.show_GroupMR) = 'Y' THEN
731 l_sql_string := l_sql_string || ' AND NOT EXISTS (SELECT ''x'' FROM AHL_UE_RELATIONSHIPS WHERE RELATED_UE_ID = UMP.unit_effectivity_id)';
732 ELSE
733 -- Added to fix bug# 6972854.
734 -- Child MRs for parent MR that is Init-Accomplished should not be displayed.
735 l_sql_string := l_sql_string || ' AND NOT EXISTS (SELECT ''x'' FROM AHL_UNIT_EFFECTIVITIES_B PARENT_UE, AHL_UE_RELATIONSHIPS CHILD_UER';
736 l_sql_string := l_sql_string || ' WHERE PARENT_UE.UNIT_EFFECTIVITY_ID = CHILD_UER.ORIGINATOR_UE_ID AND CHILD_UER.RELATED_UE_ID = UMP.unit_effectivity_id AND PARENT_UE.STATUS_CODE = ''INIT-ACCOMPLISHED'') ';
737
738 END IF;
739 END IF;
740
741 /*
742 * Temporary table use is introduced to improve performance to fix bug # 3786626
743 */
744 IF p_search_mr_instance_rec.components_flag is NOT NULL THEN
745 DELETE AHL_APPLICABLE_INSTANCES;
746 IF p_search_mr_instance_rec.components_flag = 'N' THEN
747 IF (p_search_mr_instance_rec.unit_name is NOT NULL OR nvl(p_search_mr_instance_rec.part_number,'%')
748 <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
749 populate_instances
750 (
751 p_module_type => p_module_type,
752 p_search_mr_instance_rec => p_search_mr_instance_rec
753 );
754 -- not required as included in join in l_select_sql_string
755 -- l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' from AHL_APPLICABLE_INSTANCES AAI WHERE AAI.csi_item_instance_id = UMP.csi_item_instance_id)' ; */
756 END IF;
757 /*ELSE
758 l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' From csi_unit_instances_v csiu WHERE csiu.instance_id = UMP.csi_item_instance_id )';
759 END IF;*/
760 ELSIF p_search_mr_instance_rec.components_flag = 'Y' THEN
761 IF (p_search_mr_instance_rec.unit_name is NOT NULL OR nvl(p_search_mr_instance_rec.part_number,'%')
762 <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
763 populate_instances
764 (
765 p_module_type => p_module_type,
766 p_search_mr_instance_rec => p_search_mr_instance_rec
767 );
768 populate_dependent_instances
769 (
770 p_module_type => p_module_type,
771 p_search_mr_instance_rec => p_search_mr_instance_rec
772 );
773 -- not required as included in join in l_select_sql_string
777 END IF;
774 --l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' from AHL_APPLICABLE_INSTANCES AAI WHERE AAI.csi_item_instance_id = UMP.csi_item_instance_id)' ;
775 END IF;
776 END IF;
778
779 --start fix for bug#7327283
780
781 IF (l_is_pm_installed = 'Y') THEN
782 --Contract Number
783 IF p_search_mr_instance_rec.contract_number is NOT NULL THEN
784 l_sql_string := l_sql_string || ' AND UPPER(UMP.contract_number) like :CONTRACT_NUMBER ';
785 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.contract_number);
786 l_bind_index := l_bind_index + 1;
787 END IF;
788
789 --Contract Modifier
790 IF p_search_mr_instance_rec.contract_modifier is NOT NULL THEN
791 l_sql_string := l_sql_string || ' AND UPPER(UMP.contract_number_modifier) like :CONTRACT_MODIFIER ';
792 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.contract_modifier);
793 l_bind_index := l_bind_index + 1;
794 END IF;
795
796 --If Contract Number is NOT NULL but Contract Modifier is NULL.
797 IF ( p_search_mr_instance_rec.contract_number is NOT NULL AND p_search_mr_instance_rec.contract_modifier is NULL ) THEN
798 l_sql_string := l_sql_string || ' AND UMP.contract_number_modifier is NULL';
799 END IF;
800
801 --Service Line ID
802 IF ( p_search_mr_instance_rec.service_line_id is NOT NULL AND p_search_mr_instance_rec.service_line_num is NULL) THEN
803 l_sql_string := l_sql_string || ' AND UMP.service_line_id = :SERVICE_LINE_ID ' ;
804 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.service_line_id;
805 l_bind_index := l_bind_index + 1;
806 END IF;
807
808 --Service Line Number
809 IF p_search_mr_instance_rec.service_line_num is NOT NULL THEN
810 l_sql_string := l_sql_string || ' AND UPPER(UMP.service_line_number) like :SERVICE_LINE_NUM ';
811 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.service_line_num);
812 l_bind_index := l_bind_index + 1;
813 END IF;
814
815 --Program ID
816 IF ( p_search_mr_instance_rec.program_id is NOT NULL AND p_search_mr_instance_rec.program_title is NULL ) THEN
817 l_sql_string := l_sql_string || ' AND UMP.program_mr_header_id = :PROGRAM_ID ';
818 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.program_id;
819 l_bind_index := l_bind_index + 1;
820 END IF;
821
822 --Program Title
823 IF p_search_mr_instance_rec.program_title is NOT NULL THEN
824 l_sql_string := l_sql_string || ' AND UPPER(UMP.program_title) like :PROGRAM_TITLE ';
825 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.program_title);
826 l_bind_index := l_bind_index + 1;
827 END IF;
828 END IF;
829
830 --Object Type
831 -- Set when calling for VWP search.
832 IF p_search_mr_instance_rec.object_type is NOT NULL THEN
833 l_sql_string := l_sql_string || ' AND UMP.OBJECT_TYPE = :UMP_OBJECT_TYPE ';
834 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.object_type;
835 l_bind_index := l_bind_index + 1;
836 END IF;
837
838 --amsriniv Begin
839 --Visit Number
840 --Set when calling for UMP Search when not in PM Mode.
841 IF p_search_mr_instance_rec.visit_number is NOT NULL THEN
842 l_sql_string := l_sql_string || ' and exists (select ''x'' from ahl_visits_b vst1,ahl_simulation_plans_b sim, ahl_visit_tasks_b tsk where vst1.simulation_plan_id = sim.simulation_plan_id(+) ';
843 l_sql_string := l_sql_string || 'and sim.primary_plan_flag(+) = ''Y'' and vst1.visit_number like :VISIT_NUMBER and vst1.visit_id = tsk.visit_id AND NVL(vst1.status_code,''x'') NOT ';
844 l_sql_string := l_sql_string || 'IN (''DELETED'',''CANCELLED'') AND NVL(tsk.status_code,''x'') NOT IN (''DELETED'',''CANCELLED'') AND tsk.unit_effectivity_id = UMP.unit_effectivity_id) ' ;
845 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.visit_number;
846 l_bind_index := l_bind_index + 1;
847 END IF;
848 --amsriniv End
849
850 -- order by clause.. based on the sortBy criteria
851 IF p_search_mr_instance_rec.sort_by is NOT NULL THEN
852 IF p_search_mr_instance_rec.sort_by = 'AHL_COM_DUE_DATE' THEN
853 l_sql_string := l_sql_string || ' ORDER BY UMP.DUE_DATE ASC NULLS FIRST';
854 ELSIF p_search_mr_instance_rec.sort_by = 'AHL_UMP_MR_PROGRAM' THEN
855 IF (l_is_pm_installed = 'Y') THEN
856 l_sql_string := l_sql_string || ' ORDER BY UMP.PROGRAM_TYPE_CODE';
857 ELSIF (l_mr_select_flag = TRUE) THEN
858 l_sql_string := l_sql_string || ' ORDER BY MR.PROGRAM_TYPE_CODE';
859 ELSIF (l_nr_select_sql_string IS NOT NULL) THEN
860 l_sql_string := l_sql_string || ' ORDER BY ''NONROUTINE'' ';
861 ELSE
862 l_sql_string := l_sql_string || ' ORDER BY DECODE(UMP.OBJECT_TYPE,''SR'',''NON-ROUTINE'',
863 (select MR.program_type_code from AHL_MR_HEADERS_B MR where MR.mr_header_id = UMP.mr_header_id))';
864 END IF;
865 ELSIF p_search_mr_instance_rec.sort_by = 'AHL_UMP_MR_CATEGORY' THEN
866 IF (l_is_pm_installed = 'Y') THEN
867 l_sql_string := l_sql_string || ' ORDER BY UMP.CATEGORY_CODE';
868 ELSIF (l_mr_select_flag = TRUE) THEN
869 l_sql_string := l_sql_string || ' ORDER BY MR.CATEGORY_CODE';
870 ELSE
871 l_sql_string := l_sql_string || ' ORDER BY DECODE(UMP.OBJECT_TYPE,''SR'',NULL,
872 (select MR.category_code from AHL_MR_HEADERS_B MR where MR.mr_header_id = UMP.mr_header_id))';
873 END IF;
874 ELSIF p_search_mr_instance_rec.sort_by = 'AHL_UMP_IMPL_STATUS' THEN
875 l_sql_string := l_sql_string || ' ORDER BY UMP.STATUS_CODE';
876 END IF;
877 END IF;
878
879 -- form complete SQL
880 IF (l_mr_select_flag = TRUE) THEN
881 l_sql_string := l_mr_select_sql_string || l_sql_string;
882 ELSIF (l_nr_select_sql_string IS NOT NULL) THEN
883 l_sql_string := l_nr_select_sql_string || l_sql_string;
884 ELSE
885 l_sql_string := l_select_sql_string || l_sql_string;
886 END IF;
887
888 -- Logging the sql string .
889 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
890 fnd_log.string
891 (
892 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
893 'Prepared SQL string IS '
894 );
895 l_sql_segment_count := CEIL(LENGTH(l_sql_string)/4000);
896 FOR i in 1..l_sql_segment_count LOOP
897 IF(i < l_sql_segment_count - 1) THEN
898 fnd_log.string
899 (
900 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
901 SUBSTR(l_sql_string,(i-1)*4000,4000)
902 );
903 ELSE
904 fnd_log.string
905 (
906 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
907 SUBSTR(l_sql_string,(i-1)*4000, LENGTH(l_sql_string) - (l_sql_segment_count -1 ) * 4000)
908 );
909 END IF;
910 END LOOP;
911 END IF;
912
913 --Fix for Bug 2745891
914 IF ( p_search_mr_instance_rec.mr_status = 'SCHEDULED' OR p_search_mr_instance_rec.mr_status = 'UNSCHEDULED') THEN
915 l_early_exit_status := false;
916 END IF;
917
918 --open l_cur FOR l_sql_string;
919 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
920 (
921 p_conditions_tbl => l_bindvar_tbl,
922 p_sql_str => l_sql_string,
923 p_x_csr => l_cur
924 );
925
926 -- initialize loop counters
927 row_count := 0;
928 l_counter:= 0;
929 LOOP
930 -- l_unit_effectivity_id
931 FETCH l_cur BULK COLLECT INTO l_ue_id_tbl LIMIT l_buffer_limit;
932 EXIT WHEN (l_ue_id_tbl.count = 0);
933
934 FOR j IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST LOOP
935 l_unit_effectivity_id := l_ue_id_tbl(j);
936 l_pick_record_flag := TRUE;--record picked
937
938 -- Fix for Bug 2745891
939 -- Get details only if required
940 IF ( l_early_exit_status = FALSE OR ( row_count >= p_start_row AND row_count < p_start_row + p_rows_per_page)) THEN
941 -- Check if PM is installed
942 IF (l_is_pm_installed = 'Y') THEN
943 -- IF PM is installed, Service Request Details are displayed.
944 l_service_req_id := NULL;
945 l_service_req_num := NULL;
946 l_service_req_date := NULL;
947
948 AHL_UMP_UTIL_PKG.get_ServiceRequest_Details
949 (
950 l_unit_effectivity_id,
951 l_service_req_id,
952 l_service_req_num,
953 l_service_req_date
954 );
955
956 -- Handling Scheduled and Unscheduled status.
957 IF ( p_search_mr_instance_rec.mr_status = 'SCHEDULED' AND
958 l_service_req_num IS NULL ) OR
959 ( p_search_mr_instance_rec.mr_status = 'UNSCHEDULED' AND
960 l_service_req_num IS NOT NULL )
961 THEN
962 l_pick_record_flag := FALSE;
963 END IF;
964 ELSE
968 l_visit_id := NULL; --PDOKI Added for ER# 6333770
965 -- IF PM is NOT installed, Visit Details are displayed.
966 l_scheduled_date := NULL;
967 l_visit_number := NULL;
969 --amsriniv ER 6116245
970 OPEN ahl_visit_csr (l_unit_effectivity_id, p_search_mr_instance_rec.visit_number, p_search_mr_instance_rec.visit_org_name, p_search_mr_instance_rec.visit_dept_name);
971 FETCH ahl_visit_csr INTO l_scheduled_date, l_visit_number, l_visit_id;
972 CLOSE ahl_visit_csr;
973 IF ((p_search_mr_instance_rec.visit_number IS NOT NULL OR
974 p_search_mr_instance_rec.visit_org_name IS NOT NULL OR
975 p_search_mr_instance_rec.visit_dept_name IS NOT NULL) AND
976 l_visit_number IS NULL) THEN
977 l_pick_record_flag := FALSE;
978 END IF;
979 --amsriniv ER 6116245
980 --Error check
981 IF FND_MSG_PUB.count_msg > 0 THEN
982 RAISE FND_API.G_EXC_ERROR;
983 END IF;
984 -- Handling Scheduled and Unscheduled status.
985 IF ( p_search_mr_instance_rec.mr_status = 'SCHEDULED' AND
986 l_visit_number IS NULL ) OR
987 ( p_search_mr_instance_rec.mr_status = 'UNSCHEDULED' AND
988 l_visit_number IS NOT NULL ) THEN
989 l_pick_record_flag := FALSE;
990 END IF;
991 END IF; -- End ifPMinstalled check
992 END IF; -- End if Get Details Only if required
993
994 -- Picking the records for which the l_pick_record_flag is true.
995 IF(l_pick_record_flag) THEN
996 IF ( row_count >= p_start_row AND row_count < p_start_row + p_rows_per_page) THEN
997
998 OPEN ump_details_csr(l_unit_effectivity_id);
999 FETCH ump_details_csr INTO x_results_mr_instance_tbl(l_counter).PROGRAM_TYPE_MEANING,
1000 x_results_mr_instance_tbl(l_counter).MR_TITLE,
1001 x_results_mr_instance_tbl(l_counter).PART_NUMBER,
1002 x_results_mr_instance_tbl(l_counter).SERIAL_NUMBER,
1003 -- R12: Fix for bug# 5231770.
1004 -- commented uom_remain and added l_due_counter_value
1005 -- x_results_mr_instance_tbl(l_counter).UOM_REMAIN,
1006 l_due_counter_value,
1007 x_results_mr_instance_tbl(l_counter).COUNTER_NAME,
1008 x_results_mr_instance_tbl(l_counter).EARLIEST_DUE_DATE,
1009 x_results_mr_instance_tbl(l_counter).DUE_DATE,
1010 x_results_mr_instance_tbl(l_counter).LATEST_DUE_DATE,
1011 x_results_mr_instance_tbl(l_counter).TOLERANCE_FLAG,
1012 x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE,
1013 x_results_mr_instance_tbl(l_counter).UMR_STATUS_MEANING,
1014 x_results_mr_instance_tbl(l_counter).ORIGINATOR_TITLE,
1015 x_results_mr_instance_tbl(l_counter).DEPENDANT_TITLE,
1016 x_results_mr_instance_tbl(l_counter).UNIT_EFFECTIVITY_ID,
1017 x_results_mr_instance_tbl(l_counter).MR_ID,
1018 x_results_mr_instance_tbl(l_counter).CSI_ITEM_INSTANCE_ID,
1019 x_results_mr_instance_tbl(l_counter).INSTANCE_NUMBER,
1020 x_results_mr_instance_tbl(l_counter).MR_INTERVAL_ID,
1021 x_results_mr_instance_tbl(l_counter).UNIT_NAME,
1022 x_results_mr_instance_tbl(l_counter).PROGRAM_TITLE,
1023 x_results_mr_instance_tbl(l_counter).CONTRACT_NUMBER,
1024 x_results_mr_instance_tbl(l_counter).DEFER_FROM_UE_ID,
1025 x_results_mr_instance_tbl(l_counter).OBJECT_TYPE,
1026 l_counter_id,
1027 x_results_mr_instance_tbl(l_counter).MANUALLY_PLANNED_FLAG,
1028 x_results_mr_instance_tbl(l_counter).MANUALLY_PLANNED_DESC,
1029 -- anraj: added UMP.cs_incident_id, UMP.cs_incident_number
1030 -- for bug#4133332
1031 x_results_mr_instance_tbl(l_counter).service_req_id,
1032 x_results_mr_instance_tbl(l_counter).service_req_num,
1033 l_orig_ue_instance_id;
1034
1035 CLOSE ump_details_csr;-- no record found case is not possible
1036
1037 -- Added to fix bug number 3693957
1038 IF (l_is_pm_installed = 'Y') THEN
1039 x_results_mr_instance_tbl(l_counter).service_req_id := l_service_req_id;
1040 x_results_mr_instance_tbl(l_counter).service_req_num := l_service_req_num;
1041 x_results_mr_instance_tbl(l_counter).service_req_date := l_service_req_date;
1042 ELSE
1043 x_results_mr_instance_tbl(l_counter).scheduled_date := l_scheduled_date;
1044 x_results_mr_instance_tbl(l_counter).visit_number := l_visit_number;
1045 x_results_mr_instance_tbl(l_counter).visit_id := l_visit_id; --PDOKI Added for ER# 6333770
1046
1047 END IF;
1048 -- end of fix for bug number 3693957
1049
1050 --Type of record
1051 IF(x_results_mr_instance_tbl(l_counter).MR_ID IS NULL) THEN
1052 x_results_mr_instance_tbl(l_counter).unit_effectivity_type := 'SR';
1053 ELSE
1054 x_results_mr_instance_tbl(l_counter).unit_effectivity_type := 'MR';
1055 END IF;
1056
1057 -- For 'Deferred' UE, get the defer to ue id.
1058 IF (x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE = 'DEFERRED') THEN
1059 OPEN ahl_defer_to_ue_csr(l_unit_effectivity_id);
1060 FETCH ahl_defer_to_ue_csr INTO x_results_mr_instance_tbl(l_counter).DEFER_TO_UE_ID;
1061 CLOSE ahl_defer_to_ue_csr;
1062
1063 -- indicate if MEL or CDL deferred.
1064 OPEN get_deferral_sts(l_unit_effectivity_id);
1065 FETCH get_deferral_sts INTO l_defer_mean;
1066 IF (get_deferral_sts%FOUND) THEN
1067 --x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE := l_defer_code;
1068 x_results_mr_instance_tbl(l_counter).UMR_STATUS_MEANING := l_defer_mean;
1069 END IF;
1070 CLOSE get_deferral_sts;
1071 END IF;
1072
1073 -- set status code based on deferrals. Note this changes the
1074 -- UMR_STATUS_CODE.
1075 --IF (x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE IS NULL OR
1076 -- x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE = 'INIT-DUE') THEN
1077 --END IF;
1078
1079 -- Set UOM remain as Null for closed UMPs.
1080 -- R12: Fix for bug# 5231770 - calculate uom_remain.
1081 IF (x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE IS NULL OR
1082 x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE = 'INIT-DUE')
1083 THEN
1084 IF (x_results_mr_instance_tbl(l_counter).COUNTER_NAME IS NOT NULL) THEN
1085 -- uom_remain based on interval threshold.
1086 IF (x_results_mr_instance_tbl(l_counter).ORIGINATOR_TITLE IS NULL) THEN
1087 OPEN get_net_reading_csr (
1088 x_results_mr_instance_tbl(l_counter).CSI_ITEM_INSTANCE_ID,
1089 x_results_mr_instance_tbl(l_counter).COUNTER_NAME);
1090 ELSE
1091 OPEN get_net_reading_csr (
1092 l_orig_ue_instance_id,
1093 x_results_mr_instance_tbl(l_counter).COUNTER_NAME);
1094 END IF;
1095 FETCH get_net_reading_csr INTO l_net_reading;
1096 IF (get_net_reading_csr%NOTFOUND) OR (l_net_reading IS NULL) THEN
1097 l_net_reading := 0;
1098 END IF;
1099 CLOSE get_net_reading_csr;
1100 x_results_mr_instance_tbl(l_counter).UOM_REMAIN := l_due_counter_value - l_net_reading;
1101
1102 -- UOM remain based on init due threshold counter_id.
1103 ELSIF (l_counter_id IS NOT NULL) THEN
1104 OPEN ump_ctr_name_csr(l_counter_id);
1105 FETCH ump_ctr_name_csr INTO x_results_mr_instance_tbl(l_counter).COUNTER_NAME,
1106 l_net_reading;
1107 IF (ump_ctr_name_csr%NOTFOUND) THEN
1108 l_net_reading := 0;
1109 ELSIF (l_net_reading IS NULL) THEN
1110 l_net_reading := 0;
1111 END IF;
1112 CLOSE ump_ctr_name_csr;
1113
1114 x_results_mr_instance_tbl(l_counter).UOM_REMAIN := l_due_counter_value - l_net_reading;
1115
1116 END IF; -- x_results_mr_instance_tbl(l_counter).COUNTER_NAME
1117
1118 -- get MEL/CDL deferral status
1119 -- first check for deferral record.
1120 OPEN get_open_deferral_sts(l_unit_effectivity_id);
1121 FETCH get_open_deferral_sts INTO l_defer_mean;
1122 IF (get_open_deferral_sts%FOUND) THEN
1123 x_results_mr_instance_tbl(l_counter).UMR_STATUS_MEANING := l_defer_mean;
1124 ELSE -- no deferral record found. Chk for MEL/CDL deferral.
1125 OPEN get_open_mel_cdl_sts(l_unit_effectivity_id);
1126 FETCH get_open_mel_cdl_sts INTO l_defer_mean;
1127 IF (get_open_mel_cdl_sts%FOUND) THEN
1128 x_results_mr_instance_tbl(l_counter).UMR_STATUS_MEANING := l_defer_mean;
1129 END IF;
1130 CLOSE get_open_mel_cdl_sts;
1131 END IF;
1135 x_results_mr_instance_tbl(l_counter).UOM_REMAIN := NULL;
1132 CLOSE get_open_deferral_sts;
1133
1134 ELSE
1136 END IF; -- x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE
1137
1138 -- increment counter
1139 l_counter := l_counter +1;
1140 END IF;
1141 row_count := row_count + 1;
1142 END IF; -- End l_picked_flag
1143 END LOOP; -- l_ue_id_tbl loop
1144 l_ue_id_tbl.DELETE;
1145 END LOOP; -- End LOOP
1146 -- The total number of rows returned
1147 x_results_count := row_count;
1148 --Close the dynamic cursor and free up resources
1149 CLOSE l_cur;
1150
1151 -- Standard call to get message count and if count is 1, get message info
1152 FND_MSG_PUB.Count_And_Get
1153 (
1154 p_count => x_msg_count,
1155 p_data => x_msg_data,
1156 p_encoded => fnd_api.g_false
1157 );
1158
1159 -- Logging input
1160 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1161 fnd_log.string
1162 (
1163 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
1164 'number of records returned :' || x_results_count
1165 );
1166 END IF;
1167
1168 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
1169 fnd_log.string
1170 (
1171 G_DEBUG_PROC,
1172 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances.end',
1173 'At the end of PLSQL procedure'
1174 );
1175 END IF;
1176
1177 EXCEPTION
1178 WHEN FND_API.G_EXC_ERROR THEN
1179 x_return_status := FND_API.G_RET_STS_ERROR;
1180 ROLLBACK TO Search_MR_Instances_Pvt;
1181 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1182 p_data => x_msg_data,
1183 p_encoded => fnd_api.g_false);
1184
1185
1186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1188 ROLLBACK TO Search_MR_Instances_Pvt;
1189 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1190 p_data => x_msg_data,
1191 p_encoded => fnd_api.g_false);
1192
1193 WHEN OTHERS THEN
1194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1195 ROLLBACK TO Search_MR_Instances_Pvt;
1196 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
1197 p_procedure_name => 'Search_MR_Instances',
1198 p_error_text => SQLERRM);
1199
1200 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1201 p_data => x_msg_data,
1202 p_encoded => fnd_api.g_false);
1203
1204 END Search_MR_Instances;
1205
1206
1207 PROCEDURE populate_instances(
1208 p_module_type IN VARCHAR2,
1209 p_search_mr_instance_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_MRInstance_Rec_Type) IS
1210
1211
1212 CURSOR only_units_csr(p_unit_name in VARCHAR2) IS
1213 Select ahlu.csi_item_instance_id from ahl_unit_config_headers ahlu
1214 where UPPER(ahlu.name) like upper(p_unit_name);
1215
1216
1217 l_unit_related_ii_sql VARCHAR2(4000);
1218
1219 l_bind_index NUMBER;
1220 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
1221
1222 -- dynamic cursor
1223 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
1224 --l_instance_id NUMBER;
1225
1226 l_inst_tbl nbr_tbl_type;
1227 l_buffer_limit number := 500;
1228
1229 BEGIN
1230 --l_all_csi_ii_id sql_string [ Blind search at unit level]
1231 IF (p_search_mr_instance_rec.unit_name is NOT NULL AND nvl(p_search_mr_instance_rec.part_number,'%')
1232 = '%' AND p_search_mr_instance_rec.serial_number is NULL) THEN
1233 FOR only_units_rec IN only_units_csr(p_search_mr_instance_rec.unit_name) LOOP
1234 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES (only_units_rec.csi_item_instance_id,0);
1235 END LOOP;
1236
1237 ELSE
1238 l_bind_index := 1;
1239 l_unit_related_ii_sql := 'select csii.instance_id from csi_item_instances csii ';
1240
1241 IF nvl(p_search_mr_instance_rec.part_number,'%')
1242 <> '%' THEN
1243 l_unit_related_ii_sql := l_unit_related_ii_sql || ', mtl_system_items_kfv mtl ';
1244 END IF;
1245
1246 IF p_search_mr_instance_rec.unit_name is NOT NULL THEN
1247 l_unit_related_ii_sql := l_unit_related_ii_sql || ', ahl_unit_config_headers uc ';
1248 END IF;
1249
1250 l_unit_related_ii_sql := l_unit_related_ii_sql || 'where 0=0 ';
1251
1252 IF nvl(p_search_mr_instance_rec.part_number,'%')
1253 <> '%' THEN
1254 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and mtl.inventory_item_id = csii.inventory_item_id and mtl.organization_id = csii.inv_master_organization_id ';
1255
1256 /*
1257 IF p_module_type = 'VWP' THEN
1258 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and csidv.concatenated_segments like :PART_NUMBER ';
1259 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.part_number;
1260 ELSE
1261 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and UPPER(csidv.concatenated_segments) like :PART_NUMBER ';
1262 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.part_number);
1263 END IF;
1264 */
1265
1266 /* Using UPPER() on concatenated_segments does not pick up any index and cost is very high ~ 45K in gsihrms
1267 * Also Ahmed Alomari's mail to appsperf_us (dt: 17-Feb-2006) says the following...
1268 * Part numbers are rarely case insensitive, so you can remove the upper.
1269 */
1270
1271 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and mtl.concatenated_segments like :PART_NUMBER ';
1272 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.part_number;
1273 l_bind_index := l_bind_index + 1;
1274 END IF;
1275
1276 IF p_search_mr_instance_rec.serial_number is NOT NULL THEN
1277 IF p_module_type = 'VWP' THEN
1278 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and csii.serial_number like :SERIAL_NUMBER ';
1279 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.serial_number;
1280 ELSE
1281 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and UPPER(csii.serial_number) like :SERIAL_NUMBER ';
1282 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.serial_number);
1283 END IF;
1284 l_bind_index := l_bind_index + 1;
1285 END IF;
1286
1287 IF p_search_mr_instance_rec.unit_name is NOT NULL THEN
1288 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and csii.instance_id = uc.csi_item_instance_id ';
1289 IF p_module_type = 'VWP' THEN
1290 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and uc.name like :UNIT_NAME ';
1291 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.unit_name;
1292 ELSE
1293 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and UPPER(uc.name) like :UNIT_NAME ';
1294 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.unit_name);
1295 END IF;
1296 l_bind_index := l_bind_index + 1;
1297 END IF;
1298
1299 IF p_search_mr_instance_rec.components_flag = 'N' THEN
1300 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and EXISTS (select ''x'' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.instance_id)';
1301 END IF;
1302
1303 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
1304 (
1305 p_conditions_tbl => l_bindvar_tbl,
1306 p_sql_str => l_unit_related_ii_sql,
1307 p_x_csr => l_cur
1308 );
1309 -- use dynamic cursor to execute query
1310 LOOP
1311 FETCH l_cur BULK COLLECT INTO l_inst_tbl LIMIT l_buffer_limit;
1312 EXIT WHEN (l_inst_tbl.count = 0);
1313 FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1314 --FETCH l_cur INTO l_instance_id;
1315 --EXIT WHEN l_cur%NOTFOUND;
1316 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),0);
1317 END LOOP; -- l_ue_id_tbl loop
1318 l_inst_tbl.DELETE;
1319 END LOOP;
1320
1321 CLOSE l_cur;
1322 END IF;
1323
1324
1325 END populate_instances;
1326
1327
1328 PROCEDURE populate_dependent_instances(
1329 p_module_type IN VARCHAR2,
1330 p_search_mr_instance_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_MRInstance_Rec_Type)IS
1331
1332 CURSOR dependent_components_csr IS
1333 SELECT subject_id from csi_ii_relationships csii WHERE
1334 EXISTS (select 'x' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.subject_id)
1335 AND NOT EXISTS (select 'x' from AHL_APPLICABLE_INSTANCES where csi_item_instance_id = csii.subject_id)
1336 START WITH object_id IN (SELECT csi_item_instance_id FROM AHL_APPLICABLE_INSTANCES WHERE POSITION_ID = 0)
1337 AND trunc(nvl(csii.active_start_date, sysdate)) <= Trunc(sysdate)
1338 AND trunc(nvl(csii.active_end_date, sysdate+1)) > Trunc(sysdate)
1339 AND relationship_type_code = 'COMPONENT-OF'
1340 CONNECT BY PRIOR subject_id = object_id
1341 AND trunc(nvl(csii.active_start_date, sysdate)) <= Trunc(sysdate)
1342 AND trunc(nvl(csii.active_end_date, sysdate+1)) > Trunc(sysdate)
1343 AND relationship_type_code = 'COMPONENT-OF';
1344
1345 l_inst_tbl nbr_tbl_type;
1346 l_buffer_limit number := 500;
1347
1348
1349 BEGIN
1350 /*FOR dependent_component_rec IN dependent_components_csr
1351 LOOP
1352 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID)
1353 VALUES(dependent_component_rec.subject_id,1);
1354 END LOOP;*/
1355
1356 OPEN dependent_components_csr;
1357 LOOP
1358 FETCH dependent_components_csr BULK COLLECT INTO l_inst_tbl LIMIT l_buffer_limit;
1359 EXIT WHEN (l_inst_tbl.count = 0);
1360 FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1361 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),1);
1362 END LOOP;
1363 l_inst_tbl.DELETE;
1364 END LOOP;
1365
1366 CLOSE dependent_components_csr;
1367
1368
1369 END populate_dependent_instances;
1370
1371 END AHL_UMP_SMRINSTANCE_PVT;
[Home] [Help]