1 PACKAGE BODY AHL_UMP_SMRINSTANCE_PVT AS
2 /* $Header: AHLVSMRB.pls 120.15.12020000.2 2012/12/07 14:40:58 sareepar 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 PROCEDURE populate_unit_instances(
20 P_MODULE_TYPE IN VARCHAR2,
21 p_search_ump_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_Ump_Rec_Type);
22
23
24 PROCEDURE populate_dependent_instances(
25 p_module_type IN VARCHAR2);
26
27 TYPE NBR_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
28
29
30
31 -------------------------------------
32 -- End Local Procedures Declaration--
33 -------------------------------------
34
35 ------------------------
36 -- Define Procedures --
37 ------------------------
38 --------------------------------------------------------------------------------------------
39 -- Start of Comments --
40 -- Procedure name : Search_MR_Instances
41 -- Type : Private
42 -- Function : This procedure fetches all the MR Instances based both at the instance level
43 -- and the item level for the given search criteria.
44 -- Pre-reqs :
45 -- Parameters :
46 --
47 -- Standard IN Parameters :
48 -- p_api_version IN NUMBER Required
49 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
50 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
51 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
52 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
53 -- Based on this flag, the API will set the default attributes.
54 -- p_module_type IN VARCHAR2 Default NULL
55 -- This will be null.
56 -- Standard OUT Parameters :
57 -- x_return_status OUT NOCOPY VARCHAR2 Required
58 -- x_msg_count OUT NOCOPY NUMBER Required
59 -- x_msg_data OUT NOCOPY VARCHAR2 Required
60 --
61 -- Search_MR_Instances Parameters :
62 -- p_start_row IN NUMBER Required
63 -- The row from which the search results table should be displayed.
64 -- p_rows_per_page IN NUMBER Required
65 -- The number of rows to be displayed per page.
66 -- p_search_mr_instance_rec IN Search_MRInstance_Rec_Type Required
67 -- The search criteria based on which the query needs to be run to
68 -- return the MR Instances.
69 -- x_results_mr_instance_tbl OUT NOCOPY Results_MRInstance_Tbl_Type Required
70 -- List of all the MR Instances which match the search criteria entered.
71 -- x_results_count OUT NOCOPY NUMBER Required
72 -- The total count of the results returned from the entered search criteria.
73 --
74 -- Version :
75 -- Initial Version 1.0
76 -- Sunil Kumar redesigned and recoded. Performance optimized.
77 --
78 -- End of Comments.
79 --------------------------------------------------------------------------------------------
80
81 PROCEDURE Search_MR_Instances
82 (
83 p_api_version IN NUMBER,
84 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
85 p_commit IN VARCHAR2 := FND_API.G_FALSE,
86 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
87 p_default IN VARCHAR2 := FND_API.G_TRUE,
88 p_module_type IN VARCHAR2 := NULL,
89 p_start_row IN NUMBER,
90 p_rows_per_page IN NUMBER,
91 p_search_mr_instance_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_MRInstance_Rec_Type,
92 x_results_mr_instance_tbl OUT NOCOPY AHL_UMP_SMRINSTANCE_PVT.Results_MRInstance_Tbl_Type,
93 x_results_count OUT NOCOPY NUMBER,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2 ) IS
97
98 l_api_version CONSTANT NUMBER := 1.0;
99 l_api_name CONSTANT VARCHAR2(30) := 'Search_MR_Instances';
100
101 -- Local Variable for the sql string.
102 l_sql_string VARCHAR2(30000);
103 l_get_items_sql VARCHAR2(4000);
104 -- Local Variables for Instance Level Search queries
105 l_all_csi_items_sql VARCHAR2(4000);
106 l_get_csi_ii_id_sql VARCHAR2(4000);
107 l_unit_effectivity_id number;
108 --Local Variable for iterating through the result set.
109 l_counter NUMBER;
110 --Local Variable for triggering the record to be picked.
111 l_pick_record_flag boolean;
112 --Local Variable for getting the row count.
113 row_count NUMBER;
114 --Fix for Bug 2745891
115 l_early_exit_status boolean;
116 -- Bind variable index and table
117 l_bind_index NUMBER;
118 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
119 -- dynamic cursor
120 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
121 -- Logging purposes
122 l_sql_segment_count NUMBER;
123 -- Added for deferral details.
124 CURSOR ahl_defer_to_ue_csr(p_unit_effectivity_id IN NUMBER) IS
125 SELECT unit_effectivity_id
126 FROM ahl_unit_effectivities_b
127 WHERE defer_from_ue_id = p_unit_effectivity_id
128 AND rownum < 2;
129 -- cursor to actually fecth details
130 -- anraj: added UMP.cs_incident_id, UMP.cs_incident_number for bug#4133332
131 CURSOR ump_details_csr(p_unit_effectivity_id IN NUMBER) IS
132 SELECT UMP.program_type, UMP.Title, UMP.part_number, UMP.serial_number,
133 -- R12: Fix for bug# 5231770.
134 -- Due to CSI counter schema changes, the latest net_reading is
135 -- no longer available in csi_cp_counters_v. Net_reading will be
136 -- queried seperately to calculate uom_remain.
137 -- In this cursor, UMP.due_counter_value will be retrieved instead.
138 --(UMP.due_counter_value - nvl(UMP.net_reading,0)) uom_remain,
139 UMP.due_counter_value uom_remain,
140 UMP.counter_name, UMP.earliest_due_date, UMP.due_date, UMP.latest_due_date,
141 UMP.tolerance, UMP.status_code, UMP.status, UMP.originator_title, UMP.dependant_title,
142 UMP.unit_effectivity_id, UMP.mr_header_id, UMP.csi_item_instance_id, UMP.instance_number,
143 UMP.mr_interval_id, UMP.unit_name, UMP.program_title, UMP.contract_number,
144 UMP.defer_from_ue_id, UMP.object_type, UMP.counter_id, UMP.MANUALLY_PLANNED_FLAG,
145 UMP.MANUALLY_PLANNED_DESC,
146 UMP.cs_incident_id, UMP.cs_incident_number,
147 -- added for bug# 6530920.
148 UMP.orig_ue_instance_id
149 FROM ahl_unit_effectivities_v UMP
150 WHERE UMP.unit_effectivity_id = p_unit_effectivity_id;
151
152 --Changes for SBE - Dependent MR Details Begins
153 l_prec_ue_id NUMBER;
154 l_term_ue_id NUMBER;
155 l_acc_trig_reln_type VARCHAR2(30);
156 l_term_title VARCHAR2(80);
157 --Changes for SBE - Dependent MR Details Ends
158 -- Added to fix bug# 2780716.
159 l_counter_id NUMBER;
160
161 -- to get uom remain from a counter_id.
162 -- Changed for R12 CSI Counter changes.
163 CURSOR ump_ctr_name_csr (p_counter_id IN NUMBER) IS
164 /* modified for uptake of IB changes 7374316.
165 SELECT cc.counter_template_name counter_name,
166 nvl(cv.net_reading,0) net_reading
167 FROM csi_counter_values_v cv, csi_counters_vl cc
168 WHERE cv.counter_id = cc.counter_id
169 AND cv.counter_id = p_counter_id
170 AND rownum < 2;
171 */
172
173 SELECT cc.counter_template_name counter_name,
174 (select ccr.net_reading from csi_counter_readings ccr
175 where ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
176 and nvl(ccr.disabled_flag,'N') = 'N')
177 FROM csi_counters_vl cc
178 WHERE cc.counter_id = p_counter_id;
179
180 -- Added to fix bug number 3693957
181 l_service_req_id NUMBER;
182 l_service_req_num VARCHAR2(64);
183 l_service_req_date DATE;
184
185 l_scheduled_date DATE;
186 l_visit_number VARCHAR2(80);
187
188 --PDOKI Added for ER# 6333770
189 l_visit_id NUMBER;
190
191 /*
192 -- 11.5.10CU2: Ignore Simulated visits.
193 CURSOR ahl_visit_csr(p_ue_id IN NUMBER) IS
194 SELECT vst.start_date_time, vst.visit_number
195 FROM ahl_visit_tasks_b tsk,
196 (select vst1.* from
197 ahl_visits_b vst1, ahl_simulation_plans_b sim
198 where vst1.simulation_plan_id = sim.simulation_plan_id
199 and sim.primary_plan_flag = 'Y'
200 UNION ALL
201 select vst1.* from
202 ahl_visits_b vst1
203 where simulation_plan_id IS NULL)vst
204 WHERE vst.visit_id = tsk.visit_id
205 AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
206 AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
207 AND tsk.unit_effectivity_id = p_ue_id;
208 */
209 --amsriniv ER 6116245 Begin
210 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
211 SELECT vst.start_date_time, vst.visit_number, vst.visit_id
212 FROM ahl_visit_tasks_b tsk,
213 (select vst1.* from
214 ahl_visits_b vst1, ahl_simulation_plans_b sim
215 where vst1.simulation_plan_id = sim.simulation_plan_id
216 and sim.primary_plan_flag = 'Y'
217 UNION ALL
218 select vst1.* from
219 ahl_visits_b vst1
220 where simulation_plan_id IS NULL)vst,
221 hr_all_organization_units hrou,
222 bom_departments bdpt
223 WHERE vst.visit_id = tsk.visit_id
224 AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
225 AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
226 AND tsk.unit_effectivity_id = p_ue_id
227 AND vst.organization_id = hrou.organization_id(+)
228 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)))
229 AND vst.department_id = bdpt.department_id(+)
230 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)))
231 AND vst.visit_number like nvl(p_visit_num,vst.visit_number);
232 --amsriniv ER 6116245 End
233
234 -- R12: MEL/CDL changes.
235 -- get deferral status for deferred UMP rows.
236 cursor get_deferral_sts (p_ue_id in number) IS
237 select decode(unit_deferral_type, 'MEL', 'MEL ' || fk.meaning,
238 'CDL', 'CDL ' || fk.meaning,
239 fk.meaning) deferral_meaning
240 from ahl_unit_deferrals_b, fnd_lookup_values_vl fk
241 where unit_effectivity_id = p_ue_id
242 and fk.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
243 and fk.lookup_code = approval_status_code;
244
245 -- get deferral status for open UMP rows.
246 cursor get_open_deferral_sts (p_ue_id in number) IS
247 select fk.meaning defer_meaning
248 from ahl_unit_deferrals_b udf, fnd_lookup_values_vl fk
249 where udf.unit_effectivity_id = p_ue_id
250 and fk.lookup_code = decode(udf.approval_status_code,
251 'DEFERRAL_PENDING', DECODE(NVL(udf.cancel_flag,'N'),'Y','CANCEL_PENDING','DEFERRAL_PENDING'),
252 'DEFERRAL_REJECTED', DECODE(NVL(udf.cancel_flag,'N'),'Y','CANCEL_REJECTED','DEFERRAL_REJECTED'),
253 udf.approval_status_code)
254 --and fk.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
255 and fk.lookup_type = 'AHL_UNIT_EFFECTIVITY_STATUS'
256 and udf.unit_deferral_type = 'DEFERRAL';
257
258 -- get mel/cdl status for open UMP rows.
259 cursor get_open_mel_cdl_sts (p_ue_id in number) IS
260 select unit_deferral_type || ' ' || fk.meaning defer_meaning
261 from ahl_unit_deferrals_b udf, fnd_lookup_values_vl fk
262 where udf.unit_effectivity_id = p_ue_id
263 and fk.lookup_code = decode(udf.approval_status_code, 'DEFERRED',
264 'APPROVED',udf.approval_status_code)
265 and fk.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
266 and udf.unit_deferral_type IN ('MEL','CDL') ;
267
268 --l_defer_code VARCHAR2(30);
269 l_defer_mean VARCHAR2(80);
270
271 -- R12: Fix for bug# 5231770.
272 l_due_counter_value NUMBER;
273 l_net_reading NUMBER;
274 l_approval_status_code VARCHAR2(30);
275 l_unit_deferral_type VARCHAR2(30);
276
277 -- to get the net counter reading for a counter name and item instance.
278 -- modified for uptake of IB fix. Refer bug 7374316.
279 CURSOR get_net_reading_csr (p_csi_item_instance_id IN NUMBER,
280 p_ctr_template_name IN VARCHAR2)
281 IS
282 SELECT (select ccr.net_reading from csi_counter_readings ccr
283 where ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
284 and nvl(ccr.disabled_flag,'N') = 'N') net_reading
285 FROM CSI_COUNTER_ASSOCIATIONS CCA, CSI_COUNTERS_VL CC
286 WHERE CCA.COUNTER_ID = CC.COUNTER_ID
287 AND CCA.SOURCE_OBJECT_ID = p_csi_item_instance_id
288 AND CCA.SOURCE_OBJECT_CODE = 'CP'
289 AND CC.COUNTER_TEMPLATE_NAME = p_ctr_template_name;
290 /*
291 SELECT nvl(CV.NET_READING, 0)
292 FROM CSI_COUNTER_READINGS CV, CSI_COUNTER_ASSOCIATIONS CCA, CSI_COUNTERS_VL CC
293 WHERE CCA.SOURCE_OBJECT_CODE = 'CP'
294 AND CCA.COUNTER_ID = CV.COUNTER_ID
295 --AND CC.COUNTER_ID = CV.COUNTER_ID
296 AND CC.CTR_VAL_MAX_SEQ_NO = CV.counter_value_id
297 AND CCA.SOURCE_OBJECT_ID = p_csi_item_instance_id
298 AND CC.COUNTER_TEMPLATE_NAME = p_ctr_template_name;
299 --ORDER BY CV.VALUE_TIMESTAMP DESC;
300 */
301
302 -- Bug 9410754 added for adding version number to results table Start
303
304 CURSOR ump_version_csr(mr_id IN NUMBER) IS
305 select mr_status_code, effective_to, version_number from ahl_mr_headers_b
306 where mr_header_id = mr_id;
307
308 l_version_number NUMBER;
309 l_status_code VARCHAR2(40);
310 l_end_date DATE;
311
312 --Bug 9410754 added for adding version number to results table End
313
314 -- added for bug# 6530920.
315 l_orig_ue_instance_id NUMBER;
316 l_buffer_limit number := 500;
317 l_ue_id_tbl nbr_tbl_type;
318
319 l_is_pm_installed VARCHAR2(3) := AHL_UTIL_PKG.is_pm_installed();
320 l_mr_select_sql_string VARCHAR2(4000);
321 l_select_sql_string VARCHAR2(4000);
322 l_mr_select_flag BOOLEAN;
323 l_nr_select_sql_string VARCHAR2(4000);
324
325 l_due_to DATE;
326
327 BEGIN
328 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
329 fnd_log.string
330 (
331 G_DEBUG_PROC,
332 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances.begin',
333 'At the start of PLSQL procedure'
334 );
335 END IF;
336 -- Standard start of API savepoint
337 SAVEPOINT Search_MR_Instances_Pvt;
338
339 -- Standard call to check for call compatibility
340 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
341 G_PKG_NAME) THEN
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 END IF;
344
345 -- Initialize message list if p_init_msg_list is set to TRUE
346 IF FND_API.To_Boolean(p_init_msg_list) THEN
347 FND_MSG_PUB.Initialize;
348 END IF;
349
350 -- Initialize API return status to success
351 x_return_status := FND_API.G_RET_STS_SUCCESS;
352
353
354 -- other initilizations needed for API
355 l_early_exit_status := TRUE;
356 l_bind_index := 1;
357 -- Logging input
358 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
359 fnd_log.string
360 (
361 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
362 'p_search_mr_instance_rec.unit_name :' || p_search_mr_instance_rec.unit_name
363 );
364 fnd_log.string
365 (
366 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
367 'p_search_mr_instance_rec.part_number :' || p_search_mr_instance_rec.part_number
368 );
369 fnd_log.string
370 (
371 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
372 'p_search_mr_instance_rec.serial_number :' || p_search_mr_instance_rec.serial_number
373 );
374 fnd_log.string
375 (
376 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
377 'p_search_mr_instance_rec.sort_by :' || p_search_mr_instance_rec.sort_by
378 );
379 fnd_log.string
380 (
381 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
382 'p_search_mr_instance_rec.mr_status :' || p_search_mr_instance_rec.mr_status
383 );
384 fnd_log.string
385 (
386 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
387 'p_search_mr_instance_rec.program_type :' || p_search_mr_instance_rec.program_type
388 );
389 fnd_log.string
390 (
391 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
392 'p_search_mr_instance_rec.due_from :' || p_search_mr_instance_rec.due_from
393 );
394 fnd_log.string
395 (
396 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
397 'p_search_mr_instance_rec.due_to :' || p_search_mr_instance_rec.due_to
398 );
399 fnd_log.string
400 (
401 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
402 'p_search_mr_instance_rec.show_tolerance :' || p_search_mr_instance_rec.show_tolerance
403 );
404 fnd_log.string
405 (
406 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
407 'p_search_mr_instance_rec.components_flag :' || p_search_mr_instance_rec.components_flag
408 );
409 fnd_log.string
410 (
411 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
412 'p_search_mr_instance_rec.repetitive_flag :' || p_search_mr_instance_rec.repetitive_flag
413 );
414 fnd_log.string
415 (
416 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
417 'p_search_mr_instance_rec.contract_number :' || p_search_mr_instance_rec.contract_number
418 );
419 fnd_log.string
420 (
421 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
422 'p_search_mr_instance_rec.contract_modifier :' || p_search_mr_instance_rec.contract_modifier
423 );
424 fnd_log.string
425 (
426 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
427 'p_search_mr_instance_rec.contract_number :' || p_search_mr_instance_rec.contract_number
428 );
429 fnd_log.string
430 (
431 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
432 'p_search_mr_instance_rec.service_line_id :' || p_search_mr_instance_rec.service_line_id
433 );
434 fnd_log.string
435 (
436 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
437 'p_search_mr_instance_rec.service_line_num :' || p_search_mr_instance_rec.service_line_num
438 );
439 fnd_log.string
440 (
441 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
442 'p_search_mr_instance_rec.program_id :' || p_search_mr_instance_rec.program_id
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.program_title :' || p_search_mr_instance_rec.program_title
448 );
449 fnd_log.string
450 (
451 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
452 'p_search_mr_instance_rec.show_groupmr :' || p_search_mr_instance_rec.show_groupmr
453 );
454 fnd_log.string
455 (
456 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
457 'p_search_mr_instance_rec.object_type :' || p_search_mr_instance_rec.object_type
458 );
459
460 fnd_log.string
461 (
462 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
463 'p_search_mr_instance_rec.search_for_type :' || p_search_mr_instance_rec.search_for_type
464 );
465
466 fnd_log.string
467 (
468 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
469 'p_search_mr_instance_rec.INCIDENT_TYPE_ID :' || p_search_mr_instance_rec.INCIDENT_TYPE_ID
470 );
471
472 fnd_log.string
473 (
474 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
475 'p_search_mr_instance_rec.SERVICE_REQ_NUM :' || p_search_mr_instance_rec.SERVICE_REQ_NUM
476 );
477
478 --apattark added for VWPE
479 fnd_log.string
480 (
481 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
482 'p_search_mr_instance_rec.item_grp_name :' || p_search_mr_instance_rec.ITEM_GRP_NAME
483 );
484 fnd_log.string
485 (
486 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
487 'p_search_mr_instance_rec.on_wing :' || p_search_mr_instance_rec.ON_WING
488 );
489
490 -- Sthilak CAM changes
491 fnd_log.string
492 (
493 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
494 'p_search_mr_instance_rec.service_category :' || p_search_mr_instance_rec.SERVICE_CATEGORY
495 );
496
497 fnd_log.string
498 (
499 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
500 'p_search_mr_instance_rec.mr_implement_status :' || p_search_mr_instance_rec.MR_IMPLEMENT_STATUS
501 );
502
503 END IF;
504
505 l_mr_select_flag := FALSE;
506
507 IF p_search_mr_instance_rec.due_to is NOT NULL THEN
508 --l_due_to := TRUNC(p_search_mr_instance_rec.due_to) + 86399/86400;
509 l_due_to := TRUNC(p_search_mr_instance_rec.due_to) + 1;
510 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
511 fnd_log.string
512 (
513 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
514 'l_due_to :' || to_char(l_due_to,'DD-MON-YYYY HH24:MI:SS')
515 );
516 END IF;
517 END IF;
518
519 -- validate input
520 -- if both MR title and (INCIDENT_TYPE_ID and/or SERVICE_REQ_NUM) entered, raise error.
521 IF (p_search_mr_instance_rec.mr_title is NOT NULL) AND
522 (p_search_mr_instance_rec.INCIDENT_TYPE_ID IS NOT NULL OR p_search_mr_instance_rec.SERVICE_REQ_NUM IS NOT NULL)
523 THEN
524 FND_MESSAGE.Set_Name('AHL','AHL_UMP_MR_SERQ_INPUT');
525 FND_MSG_PUB.ADD;
526 RAISE FND_API.G_EXC_ERROR;
527 END IF;
528
529 --start fix for bug#7327283
530 --SELECT Clause
531 --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_V UMP WHERE 0=0 ';
532
533 --fix for bug# 7562008. Added hint /*+ dynamic_sampling(AAI1 4) */ to queries based on AHL_APPLICABLE_INSTANCES table
534 --as per feedback from Application Performance team.
535 IF (l_is_pm_installed = 'Y') THEN
536 l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_V UMP WHERE 0=0 ';
537 ELSE
538 IF (p_search_mr_instance_rec.unit_name is NOT NULL OR nvl(p_search_mr_instance_rec.part_number,'%')
539 <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
540 --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= ''AHL'' ';
541 l_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1 ';
542 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 ';
543 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';
544 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 ';
545
546 l_bindvar_tbl(l_bind_index) := ltrim(rtrim(fnd_profile.value('AHL_APPLN_USAGE')));
547 l_bind_index := l_bind_index + 1;
548
549 IF p_search_mr_instance_rec.INCIDENT_TYPE_ID IS NOT NULL THEN
550 IF nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%' THEN
551 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 ';
552 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 ';
553 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 ';
554
555 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
556 l_bind_index := l_bind_index + 1;
557
558 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
559 l_bind_index := l_bind_index + 1;
560
561 ELSE -- servc req num is null.
562 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 ';
563 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';
564 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 ';
565 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
566 l_bind_index := l_bind_index + 1;
567 END IF; -- p_search_mr_instance_rec.SERVICE_REQ_NUM
568 ELSE -- INCIDENT_TYPE_ID is null
569 IF (nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%') THEN
570 -- servc req is not null
571 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 ';
572 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 ';
573 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 ';
574 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
575 l_bind_index := l_bind_index + 1;
576 ELSE
577 -- both are null
578 null;
579 END IF;
580 END IF; -- p_search_mr_instance_rec.INCIDENT_TYPE_ID
581 ELSE -- p_search_mr_instance_rec.unit_name
582 l_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= :APPL_USG_CODE ';
583
584 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 ';
585 l_bindvar_tbl(l_bind_index) := ltrim(rtrim(fnd_profile.value('AHL_APPLN_USAGE')));
586 l_bind_index := l_bind_index + 1;
587 IF p_search_mr_instance_rec.INCIDENT_TYPE_ID IS NOT NULL THEN
588 IF nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%' THEN
589 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 ';
590 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 ';
591
592 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
593 l_bind_index := l_bind_index + 1;
594
595 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
596 l_bind_index := l_bind_index + 1;
597
598 ELSE -- servc req num is null.
599 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 ';
600 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 ';
601 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 ';
602
603 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
604 l_bind_index := l_bind_index + 1;
605 END IF; -- p_search_mr_instance_rec.SERVICE_REQ_NUM
606 ELSE -- INCIDENT_TYPE_ID is null
607 IF (nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%') THEN
608 -- servc req is not null
609 l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,CS_INCIDENTS_ALL_B CI ';
610 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 ';
611
612 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
613 l_bind_index := l_bind_index + 1;
614 ELSE
615 -- both are null
616 null;
617 END IF;
618 END IF; -- p_search_mr_instance_rec.INCIDENT_TYPE_ID
619 END IF; --p_search_mr_instance_rec.unit_name
620 END IF;
621
622 --end fix for bug#7327283
623
624 --MR Title Check
625 IF p_search_mr_instance_rec.mr_title is NOT NULL THEN
626 IF (l_is_pm_installed = 'Y') THEN
627 l_sql_string := l_sql_string || ' AND UPPER(UMP.TITLE) like :MR_TITLE ';
628 ELSE
629 l_sql_string := l_sql_string || ' AND UPPER(MR.TITLE) like :MR_TITLE ';
630 l_mr_select_flag := TRUE;
631 END IF;
632 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.mr_title);
633 l_bind_index := l_bind_index + 1;
634 END IF;
635
636 --Program Type
637 IF p_search_mr_instance_rec.program_type is NOT NULL THEN
638 /*
639 IF p_module_type = 'VWP' THEN
640 --If the caller is 'VWP'
641 l_sql_string := l_sql_string || ' AND UMP.OBJECT_TYPE = ''MR'' ';
642 ELSIF p_search_mr_instance_rec.program_type = 'NON-ROUTINE' THEN
643 --If the Prgram_type_code is NON_ROUTINE
644 l_sql_string := l_sql_string || ' AND UMP.OBJECT_TYPE IN (''SR'',''MR'') ';
645 END IF;
646 */
647 IF (l_is_pm_installed = 'Y') THEN
648 l_sql_string := l_sql_string || ' AND UMP.PROGRAM_TYPE_CODE like :FMP_PROGRAM_TYPE ';
649 ELSE
650 IF (p_search_mr_instance_rec.program_type = 'NON-ROUTINE' AND l_mr_select_flag = FALSE AND
651 l_nr_select_sql_string IS NULL) THEN
652 --If the Prgram_type_code is NON_ROUTINE
653 l_sql_string := l_sql_string || ' AND UMP.OBJECT_TYPE IN (''SR'',''MR'') ';
654 -- fix for bug#7327283
655 l_sql_string := l_sql_string || ' AND DECODE(UMP.OBJECT_TYPE,''SR'',''NON-ROUTINE'',
656 (select MR.program_type_code from AHL_MR_HEADERS_B MR where MR.mr_header_id = UMP.mr_header_id)) like :FMP_PROGRAM_TYPE ';
657
658 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.program_type;
659 l_bind_index := l_bind_index + 1;
660
661 ELSIF (p_search_mr_instance_rec.program_type = 'NON-ROUTINE' AND l_nr_select_sql_string IS NOT NULL) THEN
662 null; -- filter not required.
663 ELSIF (l_nr_select_sql_string IS NULL) THEN
664 -- only MRs to be selected
665 l_sql_string := l_sql_string || ' AND MR.PROGRAM_TYPE_CODE like :FMP_PROGRAM_TYPE ';
666 l_mr_select_flag := TRUE;
667
668 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.program_type;
669 l_bind_index := l_bind_index + 1;
670 END IF;
671 END IF;
672 END IF;
673
674 --MR Status Check
675 IF (p_search_mr_instance_rec.mr_status is NOT NULL AND UPPER(p_search_mr_instance_rec.mr_status) <> 'ALL') THEN
676 IF UPPER(p_search_mr_instance_rec.mr_status) IN ('OPEN','SCHEDULED','UNSCHEDULED') THEN
677 --l_sql_string := l_sql_string || ' AND nvl(UMP.status_code,''x'') NOT IN (''ACCOMPLISHED'', ''EXCEPTION'', ''INIT-ACCOMPLISHED'', ''TERMINATED'',''MR-TERMINATE'',''DEFERRED'', ''SR-CLOSED'')';
678 l_sql_string := l_sql_string || ' AND (UMP.status_code IS NULL OR UMP.status_code = ''INIT-DUE'') ';
679 IF UPPER(p_search_mr_instance_rec.mr_status) = 'UNSCHEDULED' THEN
680 l_sql_string := l_sql_string || ' AND UMP.preceding_ue_id IS NULL ';
681 END IF;
682 ELSIF p_search_mr_instance_rec.mr_status IN ('DEFERRAL_PENDING','CANCEL_PENDING','CANCEL_REJECTED',
683 'DEFERRAL_REJECTED',
684 'DRAFT')
685 THEN
686 l_sql_string := l_sql_string || ' AND (UMP.status_code IS NULL OR UMP.status_code = ''INIT-DUE'') ';
687 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) ';
688 IF(p_search_mr_instance_rec.mr_status = 'CANCEL_REJECTED')THEN
689 l_bindvar_tbl(l_bind_index) := 'DEFERRAL_REJECTED';
690 ELSIF (p_search_mr_instance_rec.mr_status = 'CANCEL_PENDING')THEN
691 l_bindvar_tbl(l_bind_index) := 'DEFERRAL_PENDING';
692 ELSE
693 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.mr_status;
694 END IF;
695 l_bind_index := l_bind_index + 1;
696 /*
697 ELSIF p_search_mr_instance_rec.mr_status like 'CDL%' THEN
698 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) ';
699 l_bindvar_tbl(l_bind_index) := 'CDL:' || p_search_mr_instance_rec.mr_status;
700 l_bind_index := l_bind_index + 1;
701 ELSIF p_search_mr_instance_rec.mr_status like 'MEL%' THEN
702 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) ';
703 l_bindvar_tbl(l_bind_index) := 'MEL:' || p_search_mr_instance_rec.mr_status;
704 l_bind_index := l_bind_index + 1;
705 */
706 ELSE
707 l_sql_string := l_sql_string || ' AND UMP.STATUS_CODE = :MR_STATUS ';
708 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.mr_status;
709 l_bind_index := l_bind_index + 1;
710 END IF;
711 END IF;
712
713 --Manually Planned Flag Check
714 IF (p_search_mr_instance_rec.search_for_type is NOT NULL AND p_search_mr_instance_rec.search_for_type <> 'ALL')
715 THEN
716 IF (p_search_mr_instance_rec.search_for_type = 'FORECASTED')
717 THEN
718 l_sql_string := l_sql_string || ' AND NVL(UMP.MANUALLY_PLANNED_FLAG, ''N'') = ''N'' ';
719 ELSIF (p_search_mr_instance_rec.search_for_type = 'MANUALLY_PLANNED')
720 THEN
721 l_sql_string := l_sql_string || ' AND UMP.MANUALLY_PLANNED_FLAG = ''Y'' ';
722 END IF;
723 END IF;
724
725
726 /* *********************************** BEGIN: SHOW TOLERANCE ****************************************** */
727 -- Show Tolerance is N
728 IF p_search_mr_instance_rec.show_tolerance ='N' THEN
729 --Due-From Date Check
730 IF p_search_mr_instance_rec.due_from is NOT NULL THEN
731 l_sql_string := l_sql_string || ' AND UMP.DUE_DATE >= :DUE_FROM1 ';
732 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
733 l_bind_index := l_bind_index + 1;
734 END IF;
735 --Due-To Date Check
736 IF p_search_mr_instance_rec.due_to is NOT NULL THEN
737 --l_sql_string := l_sql_string || ' AND UMP.DUE_DATE <= :DUE_TO1 ';
738 l_sql_string := l_sql_string || ' AND UMP.DUE_DATE < :DUE_TO1 ';--bug8265049
739 l_bindvar_tbl(l_bind_index) := l_due_to;--TRUNC(p_search_mr_instance_rec.due_to);//bug8265049
740 l_bind_index := l_bind_index + 1;
741 END IF;
742 ELSE -- Show Tolerance is Y
743 IF ( p_search_mr_instance_rec.due_from is NOT NULL AND p_search_mr_instance_rec.due_to is NOT NULL ) THEN
744 -- Both Due-From and Due-To Dates are there
745 --adivenka modified the following part of the query for bug# 4315128
746 l_sql_string := l_sql_string || ' AND (
747 (:DUE_FROM2 <= nvl(UMP.earliest_due_date, UMP.due_date) AND nvl(UMP.earliest_due_date, UMP.due_date) < :DUE_TO2)
748 OR (nvl(UMP.earliest_due_date, UMP.due_date) <= :DUE_FROM3 AND :DUE_TO3 < nvl(UMP.latest_due_date, UMP.due_date))
749 OR (:DUE_FROM4 <= nvl(UMP.latest_due_date, UMP.due_date) AND nvl(UMP.latest_due_date, UMP.due_date) < :DUE_TO4)
750 )';
751 --adivenka changes end
752
753 -- due from2 and due to2
754 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
755 l_bind_index := l_bind_index + 1;
756 l_bindvar_tbl(l_bind_index) := l_due_to;--TRUNC(p_search_mr_instance_rec.due_to);//bug8265049
757 l_bind_index := l_bind_index + 1;
758 -- due from3 and due to3
759 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
760 l_bind_index := l_bind_index + 1;
761 l_bindvar_tbl(l_bind_index) := l_due_to;--TRUNC(p_search_mr_instance_rec.due_to);//bug8265049
762 l_bind_index := l_bind_index + 1;
763 -- due from4 and due to4
764 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
765 l_bind_index := l_bind_index + 1;
766 l_bindvar_tbl(l_bind_index) := l_due_to;--TRUNC(p_search_mr_instance_rec.due_to);//bug8265049
767 l_bind_index := l_bind_index + 1;
768 ELSIF ( p_search_mr_instance_rec.due_from is NOT NULL AND p_search_mr_instance_rec.due_to is NULL ) THEN
769 -- Only Due-From Date is there and Due-To Date is NULL
770 --adivenka modified the following part of the query for bug# 4315128
771 l_sql_string := l_sql_string || ' AND ( ( ( :DUE_FROM2 <= nvl(UMP.earliest_due_date, UMP.due_date) )
772 )
773 OR
774 ( ( :DUE_FROM3 <= nvl(UMP.latest_due_date, UMP.due_date) )
775 ) )';
776 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
777 l_bind_index := l_bind_index + 1;
778 l_bindvar_tbl(l_bind_index) := TRUNC(p_search_mr_instance_rec.due_from);
779 l_bind_index := l_bind_index + 1;
780 ELSIF ( p_search_mr_instance_rec.due_from is NULL AND p_search_mr_instance_rec.due_to is NOT NULL ) THEN
781 -- Only Due-To Date is there and Due-From Date is NULL
782 --adivenka modified the following part of the query for bug# 4315128
783 l_sql_string := l_sql_string || ' AND ( ( ( nvl(UMP.earliest_due_date, UMP.due_date) < :DUE_TO2 )
784 )
785 OR
786 ( ( nvl(UMP.latest_due_date, UMP.due_date) < :DUE_TO3 )
787 ) )';
788 l_bindvar_tbl(l_bind_index) := l_due_to;--TRUNC(p_search_mr_instance_rec.due_to);//bug8265049
789 l_bind_index := l_bind_index + 1;
790 l_bindvar_tbl(l_bind_index) := l_due_to;--TRUNC(p_search_mr_instance_rec.due_to);//bug8265049
791 l_bind_index := l_bind_index + 1;
792 END IF;--Case is ignored when Both Due-From and Due-To Dates are NOT here
793 END IF;
794 /* ***********************************END: SHOW TOLERANCE ****************************************** */
795
796 --Repetitive MR Flag Check
797 IF p_search_mr_instance_rec.repetitive_flag is NOT NULL THEN
798 -- added show_GroupMR condition to fix bug# 9911303
799 IF UPPER(p_search_mr_instance_rec.repetitive_flag) <> 'Y' THEN
800 IF (UPPER(nvl(p_search_mr_instance_rec.show_GroupMR, 'x')) = 'Y') THEN
801 l_sql_string := l_sql_string || ' AND nvl(UMP.REPETITIVE_MR_FLAG,''x'') <> ''Y'' ';
802 ELSE
803 -- for child MRs, check repetitive_flag set for root UE
804 l_sql_string := l_sql_string || ' AND ((nvl(UMP.REPETITIVE_MR_FLAG,''x'')) <> ''Y'' OR EXISTS ( ';
805 l_sql_string := l_sql_string || ' SELECT ''x'' FROM AHL_UNIT_EFFECTIVITIES_B PARENT_UE, AHL_UE_RELATIONSHIPS CHILD_UER';
806 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.REPETITIVE_MR_FLAG = ''N'')) ';
807 END IF;
808 END IF; -- p_search_mr_instance_rec.repetitive_flag
809 END IF;
810
811 -- Show GroupMR check.
812 IF p_search_mr_instance_rec.show_GroupMR is NOT NULL THEN
813 IF UPPER(p_search_mr_instance_rec.show_GroupMR) = 'Y' THEN
814 l_sql_string := l_sql_string || ' AND NOT EXISTS (SELECT /*+ no_unnest */ ''x'' FROM AHL_UE_RELATIONSHIPS WHERE RELATED_UE_ID = UMP.unit_effectivity_id)';
815 ELSE
816 -- Added to fix bug# 6972854.
817 -- Child MRs for parent MR that is Init-Accomplished should not be displayed.
818 l_sql_string := l_sql_string || ' AND NOT EXISTS (SELECT ''x'' FROM AHL_UNIT_EFFECTIVITIES_B PARENT_UE, AHL_UE_RELATIONSHIPS CHILD_UER';
819 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'') ';
820
821 END IF;
822 END IF;
823
824 /*
825 * Temporary table use is introduced to improve performance to fix bug # 3786626
826 */
827 IF p_search_mr_instance_rec.components_flag is NOT NULL THEN
828 DELETE AHL_APPLICABLE_INSTANCES;
829 IF p_search_mr_instance_rec.components_flag = 'N' THEN
830 IF (p_search_mr_instance_rec.unit_name is NOT NULL OR nvl(p_search_mr_instance_rec.part_number,'%')
831 <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
832 populate_instances
833 (
834 p_module_type => p_module_type,
835 p_search_mr_instance_rec => p_search_mr_instance_rec
836 );
837 -- not required as included in join in l_select_sql_string
838 -- 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)' ; */
839 END IF;
840 /*ELSE
841 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 )';
842 END IF;*/
843 ELSIF p_search_mr_instance_rec.components_flag = 'Y' THEN
844 IF (p_search_mr_instance_rec.unit_name is NOT NULL OR nvl(p_search_mr_instance_rec.part_number,'%')
845 <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
846 populate_instances
847 (
848 p_module_type => p_module_type,
849 p_search_mr_instance_rec => p_search_mr_instance_rec
850 );
851 populate_dependent_instances
852 (
853 p_module_type => p_module_type
854 );
855 -- not required as included in join in l_select_sql_string
856 --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)' ;
857 END IF;
858 END IF;
859 END IF;
860
861 --start fix for bug#7327283
862
863 IF (l_is_pm_installed = 'Y') THEN
864 --Contract Number
865 IF p_search_mr_instance_rec.contract_number is NOT NULL THEN
866 l_sql_string := l_sql_string || ' AND UPPER(UMP.contract_number) like :CONTRACT_NUMBER ';
867 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.contract_number);
868 l_bind_index := l_bind_index + 1;
869 END IF;
870
871 --Contract Modifier
872 IF p_search_mr_instance_rec.contract_modifier is NOT NULL THEN
873 l_sql_string := l_sql_string || ' AND UPPER(UMP.contract_number_modifier) like :CONTRACT_MODIFIER ';
874 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.contract_modifier);
875 l_bind_index := l_bind_index + 1;
876 END IF;
877
878 --If Contract Number is NOT NULL but Contract Modifier is NULL.
879 IF ( p_search_mr_instance_rec.contract_number is NOT NULL AND p_search_mr_instance_rec.contract_modifier is NULL ) THEN
880 l_sql_string := l_sql_string || ' AND UMP.contract_number_modifier is NULL';
881 END IF;
882
883 --Service Line ID
884 IF ( p_search_mr_instance_rec.service_line_id is NOT NULL AND p_search_mr_instance_rec.service_line_num is NULL) THEN
885 l_sql_string := l_sql_string || ' AND UMP.service_line_id = :SERVICE_LINE_ID ' ;
886 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.service_line_id;
887 l_bind_index := l_bind_index + 1;
888 END IF;
889
890 --Service Line Number
891 IF p_search_mr_instance_rec.service_line_num is NOT NULL THEN
892 l_sql_string := l_sql_string || ' AND UPPER(UMP.service_line_number) like :SERVICE_LINE_NUM ';
893 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.service_line_num);
894 l_bind_index := l_bind_index + 1;
895 END IF;
896
897 --Program ID
898 IF ( p_search_mr_instance_rec.program_id is NOT NULL AND p_search_mr_instance_rec.program_title is NULL ) THEN
899 l_sql_string := l_sql_string || ' AND UMP.program_mr_header_id = :PROGRAM_ID ';
900 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.program_id;
901 l_bind_index := l_bind_index + 1;
902 END IF;
903
904 --Program Title
905 IF p_search_mr_instance_rec.program_title is NOT NULL THEN
906 l_sql_string := l_sql_string || ' AND UPPER(UMP.program_title) like :PROGRAM_TITLE ';
907 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.program_title);
908 l_bind_index := l_bind_index + 1;
909 END IF;
910 END IF;
911
912 --Object Type
913 -- Set when calling for VWP search.
914 IF p_search_mr_instance_rec.object_type is NOT NULL THEN
915 l_sql_string := l_sql_string || ' AND UMP.OBJECT_TYPE = :UMP_OBJECT_TYPE ';
916 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.object_type;
917 l_bind_index := l_bind_index + 1;
918 END IF;
919
920 --amsriniv Begin
921 --Visit Number
922 --Set when calling for UMP Search when not in PM Mode.
923 IF p_search_mr_instance_rec.visit_number is NOT NULL THEN
924 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(+) ';
925 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 ';
926 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) ' ;
927 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.visit_number;
928 l_bind_index := l_bind_index + 1;
929 END IF;
930 --amsriniv End
931
932 -- pdoki added for VWP Enhancement, Start.
933 IF P_SEARCH_MR_INSTANCE_REC.ITEM_GRP_NAME IS NOT NULL THEN
934 L_SQL_STRING := L_SQL_STRING || ' and exists (select ''x'' from AHL_ITEM_GROUPS_V IG, AHL_ITEM_ASSOCIATIONS_B IA , CSI_ITEM_INSTANCES CSIB where UMP.CSI_ITEM_INSTANCE_ID = CSIB.INSTANCE_ID ';
935 l_sql_string := l_sql_string || ' AND CSIB.inventory_item_id = IA.inventory_item_id ';
936 l_sql_string := l_sql_string || ' AND IA.ITEM_GROUP_ID = IG.ITEM_GROUP_ID AND IG.NAME like :item_grp_name) ';
937 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.item_grp_name;
938 l_bind_index := l_bind_index + 1;
939 END IF;
940
941 IF p_search_mr_instance_rec.on_wing is NOT NULL THEN
942 l_sql_string := l_sql_string || ' and exists (select ''x'' from AHL_MR_HEADERS_B MRB ';
943 l_sql_string := l_sql_string || ' WHERE UMP.mr_header_id = MRB.mr_header_id AND MRB.service_type_code = :on_wing) ';
944 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.on_wing;
945 l_bind_index := l_bind_index + 1;
946 END IF;
947 -- pdoki added for VWP Enhancement, End.
948
949 --Sthilak CAM changes - begin
950 -- check the page loaded from VWP
951 IF (p_search_mr_instance_rec.IS_VWP_NAVG ='Y') THEN
952
953 -- MR implement status check
954 IF p_search_mr_instance_rec.MR_IMPLEMENT_STATUS is NOT NULL THEN
955 l_sql_string := l_sql_string || ' and exists (select ''x'' from AHL_MR_HEADERS_B MRB ';
956 l_sql_string := l_sql_string || ' WHERE UMP.mr_header_id = MRB.mr_header_id AND UPPER(MRB.IMPLEMENT_STATUS_CODE) = :MR_IMPLEMENT_STATUS )';
957
958 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.MR_IMPLEMENT_STATUS);
959 l_bind_index := l_bind_index + 1;
960 END IF;
961
962 -- MR service category check
963 IF p_search_mr_instance_rec.SERVICE_CATEGORY is NOT NULL THEN
964 BEGIN
965 IF(TO_NUMBER(p_search_mr_instance_rec.SERVICE_CATEGORY) >= 0)THEN
966 NULL;
967 END IF;
968 EXCEPTION
969 WHEN OTHERS THEN
970 FND_MESSAGE.SET_NAME('AHL','AHL_UMP_MR_SCAT_NON_NUM');
971 FND_MSG_PUB.ADD;
972 RAISE FND_API.G_EXC_ERROR;
973 END;
974 l_sql_string := l_sql_string || ' and exists (select ''x'' from AHL_MR_HEADERS_B MRB ';
975 l_sql_string := l_sql_string || ' WHERE UMP.mr_header_id = MRB.mr_header_id AND MRB.SERVICE_CATEGORY_RANK >= :SERVICE_CATEGORY_RANK )';
976
977 l_bindvar_tbl(l_bind_index) := TO_NUMBER(p_search_mr_instance_rec.SERVICE_CATEGORY);
978 l_bind_index := l_bind_index + 1;
979 END IF;
980
981 END IF;
982 --Sthilak CAM changes - end
983 -- order by clause.. based on the sortBy criteria
984 IF p_search_mr_instance_rec.sort_by is NOT NULL THEN
985 IF p_search_mr_instance_rec.sort_by = 'AHL_COM_DUE_DATE' THEN
986 l_sql_string := l_sql_string || ' ORDER BY UMP.DUE_DATE ASC NULLS FIRST';
987 ELSIF p_search_mr_instance_rec.sort_by = 'AHL_UMP_MR_PROGRAM' THEN
988 IF (l_is_pm_installed = 'Y') THEN
989 l_sql_string := l_sql_string || ' ORDER BY UMP.PROGRAM_TYPE_CODE';
990 ELSIF (l_mr_select_flag = TRUE) THEN
991 l_sql_string := l_sql_string || ' ORDER BY MR.PROGRAM_TYPE_CODE';
992 ELSIF (l_nr_select_sql_string IS NOT NULL) THEN
993 l_sql_string := l_sql_string || ' ORDER BY ''NONROUTINE'' ';
994 ELSE
995 l_sql_string := l_sql_string || ' ORDER BY DECODE(UMP.OBJECT_TYPE,''SR'',''NON-ROUTINE'',
996 (select MR.program_type_code from AHL_MR_HEADERS_B MR where MR.mr_header_id = UMP.mr_header_id))';
997 END IF;
998 ELSIF p_search_mr_instance_rec.sort_by = 'AHL_UMP_MR_CATEGORY' THEN
999 IF (l_is_pm_installed = 'Y') THEN
1000 l_sql_string := l_sql_string || ' ORDER BY UMP.CATEGORY_CODE';
1001 ELSIF (l_mr_select_flag = TRUE) THEN
1002 l_sql_string := l_sql_string || ' ORDER BY MR.CATEGORY_CODE';
1003 ELSE
1004 l_sql_string := l_sql_string || ' ORDER BY DECODE(UMP.OBJECT_TYPE,''SR'',NULL,
1005 (select MR.category_code from AHL_MR_HEADERS_B MR where MR.mr_header_id = UMP.mr_header_id))';
1006 END IF;
1007 ELSIF p_search_mr_instance_rec.sort_by = 'AHL_UMP_IMPL_STATUS' THEN
1008 l_sql_string := l_sql_string || ' ORDER BY UMP.STATUS_CODE';
1009 END IF;
1010 END IF;
1011
1012 -- form complete SQL
1013 IF (l_mr_select_flag = TRUE) THEN
1014 l_sql_string := l_mr_select_sql_string || l_sql_string;
1015 ELSIF (l_nr_select_sql_string IS NOT NULL) THEN
1016 l_sql_string := l_nr_select_sql_string || l_sql_string;
1017 ELSE
1018 l_sql_string := l_select_sql_string || l_sql_string;
1019 END IF;
1020
1021 -- Logging the sql string .
1022 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1023 fnd_log.string
1024 (
1025 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
1026 'Prepared SQL string IS '
1027 );
1028 l_sql_segment_count := CEIL(LENGTH(l_sql_string)/4000);
1029 FOR i in 1..l_sql_segment_count LOOP
1030 IF(i < l_sql_segment_count - 1) THEN
1031 fnd_log.string
1032 (
1033 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
1034 SUBSTR(l_sql_string,(i-1)*4000,4000)
1035 );
1036 ELSE
1037 fnd_log.string
1038 (
1039 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
1040 SUBSTR(l_sql_string,(i-1)*4000, LENGTH(l_sql_string) - (l_sql_segment_count -1 ) * 4000)
1041 );
1042 END IF;
1043 END LOOP;
1044 END IF;
1045
1046 --Fix for Bug 2745891
1047 IF ( p_search_mr_instance_rec.mr_status = 'SCHEDULED' OR p_search_mr_instance_rec.mr_status = 'UNSCHEDULED') THEN
1048 l_early_exit_status := false;
1049 END IF;
1050
1051 --open l_cur FOR l_sql_string;
1052 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
1053 (
1054 p_conditions_tbl => l_bindvar_tbl,
1055 p_sql_str => l_sql_string,
1056 p_x_csr => l_cur
1057 );
1058
1059 -- initialize loop counters
1060 row_count := 0;
1061 l_counter:= 0;
1062 LOOP
1063 -- l_unit_effectivity_id
1064 FETCH l_cur BULK COLLECT INTO l_ue_id_tbl LIMIT l_buffer_limit;
1065 EXIT WHEN (l_ue_id_tbl.count = 0);
1066
1067 FOR j IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST LOOP
1068 l_unit_effectivity_id := l_ue_id_tbl(j);
1069 l_pick_record_flag := TRUE;--record picked
1070
1071 -- Fix for Bug 2745891
1072 -- Get details only if required
1073 IF ( l_early_exit_status = FALSE OR ( row_count >= p_start_row AND row_count < p_start_row + p_rows_per_page)) THEN
1074 -- Check if PM is installed
1075 IF (l_is_pm_installed = 'Y') THEN
1076 -- IF PM is installed, Service Request Details are displayed.
1077 l_service_req_id := NULL;
1078 l_service_req_num := NULL;
1079 l_service_req_date := NULL;
1080
1081 AHL_UMP_UTIL_PKG.get_ServiceRequest_Details
1082 (
1083 l_unit_effectivity_id,
1084 l_service_req_id,
1085 l_service_req_num,
1086 l_service_req_date
1087 );
1088
1089 -- Handling Scheduled and Unscheduled status.
1090 IF ( p_search_mr_instance_rec.mr_status = 'SCHEDULED' AND
1091 l_service_req_num IS NULL ) OR
1092 ( p_search_mr_instance_rec.mr_status = 'UNSCHEDULED' AND
1093 l_service_req_num IS NOT NULL )
1094 THEN
1095 l_pick_record_flag := FALSE;
1096 END IF;
1097 ELSE
1098 -- IF PM is NOT installed, Visit Details are displayed.
1099 l_scheduled_date := NULL;
1100 l_visit_number := NULL;
1101 l_visit_id := NULL; --PDOKI Added for ER# 6333770
1102 --amsriniv ER 6116245
1103 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);
1104 FETCH ahl_visit_csr INTO l_scheduled_date, l_visit_number, l_visit_id;
1105 CLOSE ahl_visit_csr;
1106 IF ((p_search_mr_instance_rec.visit_number IS NOT NULL OR
1107 p_search_mr_instance_rec.visit_org_name IS NOT NULL OR
1108 p_search_mr_instance_rec.visit_dept_name IS NOT NULL) AND
1109 l_visit_number IS NULL) THEN
1110 l_pick_record_flag := FALSE;
1111 END IF;
1112 --amsriniv ER 6116245
1113 --Error check
1114 IF FND_MSG_PUB.count_msg > 0 THEN
1115 RAISE FND_API.G_EXC_ERROR;
1116 END IF;
1117 -- Handling Scheduled and Unscheduled status.
1118 IF ( p_search_mr_instance_rec.mr_status = 'SCHEDULED' AND
1119 l_visit_number IS NULL ) OR
1120 ( p_search_mr_instance_rec.mr_status = 'UNSCHEDULED' AND
1121 l_visit_number IS NOT NULL ) THEN
1122 l_pick_record_flag := FALSE;
1123 END IF;
1124 END IF; -- End ifPMinstalled check
1125 END IF; -- End if Get Details Only if required
1126
1127 -- Picking the records for which the l_pick_record_flag is true.
1128 IF(l_pick_record_flag) THEN
1129 IF ( row_count >= p_start_row AND row_count < p_start_row + p_rows_per_page) THEN
1130 --Changes for SBE - Dependent MR Details Begins
1131 SELECT preceding_ue_id, terminating_ue_id, accomplish_trigger_type,
1132 (SELECT mr.title FROM ahl_mr_headers_b mr,ahl_unit_effectivities_b dep
1133 WHERE dep.mr_header_id = mr.mr_header_id
1134 AND dep.unit_effectivity_id = eff.terminating_ue_id) termtitle
1135 INTO l_prec_ue_id, l_term_ue_id,l_acc_trig_reln_type,l_term_title
1136 FROM ahl_unit_effectivities_b eff
1137 WHERE unit_effectivity_id = l_unit_effectivity_id;
1138 --Changes for SBE - Dependent MR Details Ends
1139 OPEN ump_details_csr(l_unit_effectivity_id);
1140 FETCH ump_details_csr INTO x_results_mr_instance_tbl(l_counter).PROGRAM_TYPE_MEANING,
1141 x_results_mr_instance_tbl(l_counter).MR_TITLE,
1142 x_results_mr_instance_tbl(l_counter).PART_NUMBER,
1143 x_results_mr_instance_tbl(l_counter).SERIAL_NUMBER,
1144 -- R12: Fix for bug# 5231770.
1145 -- commented uom_remain and added l_due_counter_value
1146 -- x_results_mr_instance_tbl(l_counter).UOM_REMAIN,
1147 l_due_counter_value,
1148 x_results_mr_instance_tbl(l_counter).COUNTER_NAME,
1149 x_results_mr_instance_tbl(l_counter).EARLIEST_DUE_DATE,
1150 x_results_mr_instance_tbl(l_counter).DUE_DATE,
1151 x_results_mr_instance_tbl(l_counter).LATEST_DUE_DATE,
1152 x_results_mr_instance_tbl(l_counter).TOLERANCE_FLAG,
1153 x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE,
1154 x_results_mr_instance_tbl(l_counter).UMR_STATUS_MEANING,
1155 x_results_mr_instance_tbl(l_counter).ORIGINATOR_TITLE,
1156 x_results_mr_instance_tbl(l_counter).DEPENDANT_TITLE,
1157 x_results_mr_instance_tbl(l_counter).UNIT_EFFECTIVITY_ID,
1158 x_results_mr_instance_tbl(l_counter).MR_ID,
1159 x_results_mr_instance_tbl(l_counter).CSI_ITEM_INSTANCE_ID,
1160 x_results_mr_instance_tbl(l_counter).INSTANCE_NUMBER,
1161 x_results_mr_instance_tbl(l_counter).MR_INTERVAL_ID,
1162 x_results_mr_instance_tbl(l_counter).UNIT_NAME,
1163 x_results_mr_instance_tbl(l_counter).PROGRAM_TITLE,
1164 x_results_mr_instance_tbl(l_counter).CONTRACT_NUMBER,
1165 x_results_mr_instance_tbl(l_counter).DEFER_FROM_UE_ID,
1166 x_results_mr_instance_tbl(l_counter).OBJECT_TYPE,
1167 l_counter_id,
1168 x_results_mr_instance_tbl(l_counter).MANUALLY_PLANNED_FLAG,
1169 x_results_mr_instance_tbl(l_counter).MANUALLY_PLANNED_DESC,
1170 -- anraj: added UMP.cs_incident_id, UMP.cs_incident_number
1171 -- for bug#4133332
1172 x_results_mr_instance_tbl(l_counter).service_req_id,
1173 x_results_mr_instance_tbl(l_counter).service_req_num,
1174 l_orig_ue_instance_id;
1175
1176 CLOSE ump_details_csr;-- no record found case is not possible
1177
1178 -- Bug 9410754 added for adding version number to results table Start
1179 OPEN ump_version_csr(x_results_mr_instance_tbl(l_counter).MR_ID);
1180 FETCH ump_version_csr INTO l_status_code, l_end_date, l_version_number;
1181 CLOSE ump_version_csr;
1182
1183 --Changes for SBE - Dependent MR Details Begins
1184 -- For initiating UE, the dependent title is initiation MR title
1185 -- and for terminating UE, the dependent title should be populated with terminating MR title.
1186 IF l_prec_ue_id IS NULL AND l_term_ue_id IS NOT NULL THEN
1187 x_results_mr_instance_tbl(l_counter).DEPENDANT_TITLE := l_term_title;
1188 x_results_mr_instance_tbl(l_counter).acc_trig_reln_type := 'TERMINATED_BY';
1189 ELSIF l_acc_trig_reln_type IS NOT NULL THEN
1190 x_results_mr_instance_tbl(l_counter).acc_trig_reln_type := l_acc_trig_reln_type;
1191 ELSIF l_prec_ue_id IS NOT NULL THEN
1192 x_results_mr_instance_tbl(l_counter).acc_trig_reln_type := 'INITIATED_BY';
1193 END IF;
1194 --Changes for SBE - Dependent MR Details Ends
1195
1196 IF(l_status_code = 'TERMINATED' OR l_end_date <= sysdate) THEN
1197 x_results_mr_instance_tbl(l_counter).version_number := l_version_number || '+';
1198 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1199 fnd_log.string
1200 (
1201 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
1202 'entered +' || l_end_date || l_status_code || sysdate
1203 );
1204 END IF;
1205 ELSE
1206 x_results_mr_instance_tbl(l_counter).version_number := l_version_number;
1207 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1208 fnd_log.string
1209 (
1210 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
1211 'entered normal' || l_end_date || l_status_code || sysdate
1212 );
1213 END IF;
1214 END IF;
1215 -- Bug 9410754 for adding version number to results table End
1216
1217 -- Added to fix bug number 3693957
1218 IF (l_is_pm_installed = 'Y') THEN
1219 x_results_mr_instance_tbl(l_counter).service_req_id := l_service_req_id;
1220 x_results_mr_instance_tbl(l_counter).service_req_num := l_service_req_num;
1221 x_results_mr_instance_tbl(l_counter).service_req_date := l_service_req_date;
1222 ELSE
1223 x_results_mr_instance_tbl(l_counter).scheduled_date := l_scheduled_date;
1224 x_results_mr_instance_tbl(l_counter).visit_number := l_visit_number;
1225 x_results_mr_instance_tbl(l_counter).visit_id := l_visit_id; --PDOKI Added for ER# 6333770
1226
1227 END IF;
1228 -- end of fix for bug number 3693957
1229
1230 -- Sthilak CAM changes begin
1231 IF (p_search_mr_instance_rec.IS_VWP_NAVG ='Y') THEN
1232 x_results_mr_instance_tbl(l_counter).BUILD_GOAL_PERCENT := AHL_COMPLEX_MX_PVT.CALC_BUILD_GOAL(
1233 x_results_mr_instance_tbl(l_counter).CSI_ITEM_INSTANCE_ID,
1234 p_search_mr_instance_rec.VWP_VISIT_ID,
1235 x_results_mr_instance_tbl(l_counter).UNIT_EFFECTIVITY_ID);
1236 ELSE
1237 x_results_mr_instance_tbl(l_counter).BUILD_GOAL_PERCENT := 0;
1238 END IF;
1239 -- Sthilak CAM chnages end
1240
1241 --Type of record
1242 IF(x_results_mr_instance_tbl(l_counter).MR_ID IS NULL) THEN
1243 x_results_mr_instance_tbl(l_counter).unit_effectivity_type := 'SR';
1244 ELSE
1245 x_results_mr_instance_tbl(l_counter).unit_effectivity_type := 'MR';
1246 END IF;
1247
1248 -- For 'Deferred' UE, get the defer to ue id.
1249 IF (x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE = 'DEFERRED') THEN
1250 OPEN ahl_defer_to_ue_csr(l_unit_effectivity_id);
1251 FETCH ahl_defer_to_ue_csr INTO x_results_mr_instance_tbl(l_counter).DEFER_TO_UE_ID;
1252 CLOSE ahl_defer_to_ue_csr;
1253
1254 -- indicate if MEL or CDL deferred.
1255 OPEN get_deferral_sts(l_unit_effectivity_id);
1256 FETCH get_deferral_sts INTO l_defer_mean;
1257 IF (get_deferral_sts%FOUND) THEN
1258 --x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE := l_defer_code;
1259 x_results_mr_instance_tbl(l_counter).UMR_STATUS_MEANING := l_defer_mean;
1260 END IF;
1261 CLOSE get_deferral_sts;
1262 END IF;
1263
1264 -- set status code based on deferrals. Note this changes the
1265 -- UMR_STATUS_CODE.
1266 --IF (x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE IS NULL OR
1267 -- x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE = 'INIT-DUE') THEN
1268 --END IF;
1269
1270 -- Set UOM remain as Null for closed UMPs.
1271 -- R12: Fix for bug# 5231770 - calculate uom_remain.
1272 IF (x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE IS NULL OR
1273 x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE = 'INIT-DUE')
1274 THEN
1275 IF (x_results_mr_instance_tbl(l_counter).COUNTER_NAME IS NOT NULL) THEN
1276 -- uom_remain based on interval threshold.
1277 IF (x_results_mr_instance_tbl(l_counter).ORIGINATOR_TITLE IS NULL) THEN
1278 OPEN get_net_reading_csr (
1279 x_results_mr_instance_tbl(l_counter).CSI_ITEM_INSTANCE_ID,
1280 x_results_mr_instance_tbl(l_counter).COUNTER_NAME);
1281 ELSE
1282 OPEN get_net_reading_csr (
1283 l_orig_ue_instance_id,
1284 x_results_mr_instance_tbl(l_counter).COUNTER_NAME);
1285 END IF;
1286 FETCH get_net_reading_csr INTO l_net_reading;
1287 IF (get_net_reading_csr%NOTFOUND) OR (l_net_reading IS NULL) THEN
1288 l_net_reading := 0;
1289 END IF;
1290 CLOSE get_net_reading_csr;
1291 x_results_mr_instance_tbl(l_counter).UOM_REMAIN := l_due_counter_value - l_net_reading;
1292
1293 -- UOM remain based on init due threshold counter_id.
1294 ELSIF (l_counter_id IS NOT NULL) THEN
1295 OPEN ump_ctr_name_csr(l_counter_id);
1296 FETCH ump_ctr_name_csr INTO x_results_mr_instance_tbl(l_counter).COUNTER_NAME,
1297 l_net_reading;
1298 IF (ump_ctr_name_csr%NOTFOUND) THEN
1299 l_net_reading := 0;
1300 ELSIF (l_net_reading IS NULL) THEN
1301 l_net_reading := 0;
1302 END IF;
1303 CLOSE ump_ctr_name_csr;
1304
1305 x_results_mr_instance_tbl(l_counter).UOM_REMAIN := l_due_counter_value - l_net_reading;
1306
1307 END IF; -- x_results_mr_instance_tbl(l_counter).COUNTER_NAME
1308
1309 -- get MEL/CDL deferral status
1310 -- first check for deferral record.
1311 OPEN get_open_deferral_sts(l_unit_effectivity_id);
1312 FETCH get_open_deferral_sts INTO l_defer_mean;
1313 IF (get_open_deferral_sts%FOUND) THEN
1314 x_results_mr_instance_tbl(l_counter).UMR_STATUS_MEANING := l_defer_mean;
1315 ELSE -- no deferral record found. Chk for MEL/CDL deferral.
1316 OPEN get_open_mel_cdl_sts(l_unit_effectivity_id);
1317 FETCH get_open_mel_cdl_sts INTO l_defer_mean;
1318 IF (get_open_mel_cdl_sts%FOUND) THEN
1319 x_results_mr_instance_tbl(l_counter).UMR_STATUS_MEANING := l_defer_mean;
1320 END IF;
1321 CLOSE get_open_mel_cdl_sts;
1322 END IF;
1323 CLOSE get_open_deferral_sts;
1324
1325 ELSE
1326 x_results_mr_instance_tbl(l_counter).UOM_REMAIN := NULL;
1327 END IF; -- x_results_mr_instance_tbl(l_counter).UMR_STATUS_CODE
1328
1329 -- increment counter
1330 l_counter := l_counter +1;
1331 END IF;
1332 row_count := row_count + 1;
1333 END IF; -- End l_picked_flag
1334 END LOOP; -- l_ue_id_tbl loop
1335 l_ue_id_tbl.DELETE;
1336 END LOOP; -- End LOOP
1337 -- The total number of rows returned
1338 x_results_count := row_count;
1339 --Close the dynamic cursor and free up resources
1340 CLOSE l_cur;
1341
1342 -- Standard call to get message count and if count is 1, get message info
1343 FND_MSG_PUB.Count_And_Get
1344 (
1345 p_count => x_msg_count,
1346 p_data => x_msg_data,
1347 p_encoded => fnd_api.g_false
1348 );
1349
1350 -- Logging input
1351 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1352 fnd_log.string
1353 (
1354 G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances',
1355 'number of records returned :' || x_results_count
1356 );
1357 END IF;
1358
1359 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
1360 fnd_log.string
1361 (
1362 G_DEBUG_PROC,
1363 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Search_MR_Instances.end',
1364 'At the end of PLSQL procedure'
1365 );
1366 END IF;
1367
1368 EXCEPTION
1369 WHEN FND_API.G_EXC_ERROR THEN
1370 x_return_status := FND_API.G_RET_STS_ERROR;
1371 ROLLBACK TO Search_MR_Instances_Pvt;
1372 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1373 p_data => x_msg_data,
1374 p_encoded => fnd_api.g_false);
1375
1376
1377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1379 ROLLBACK TO Search_MR_Instances_Pvt;
1380 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1381 p_data => x_msg_data,
1382 p_encoded => fnd_api.g_false);
1383
1384 WHEN OTHERS THEN
1385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1386 ROLLBACK TO Search_MR_Instances_Pvt;
1387 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
1388 p_procedure_name => 'Search_MR_Instances',
1389 p_error_text => SQLERRM);
1390
1391 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1392 p_data => x_msg_data,
1393 p_encoded => fnd_api.g_false);
1394
1395 END Search_MR_Instances;
1396
1397
1398 PROCEDURE populate_instances(
1399 p_module_type IN VARCHAR2,
1400 p_search_mr_instance_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_MRInstance_Rec_Type) IS
1401
1402
1403 CURSOR only_units_csr(p_unit_name in VARCHAR2) IS
1404 Select ahlu.csi_item_instance_id from ahl_unit_config_headers ahlu
1405 where UPPER(ahlu.name) like upper(p_unit_name);
1406
1407
1408 l_unit_related_ii_sql VARCHAR2(4000);
1409
1410 l_bind_index NUMBER;
1411 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
1412
1413 -- dynamic cursor
1414 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
1415 --l_instance_id NUMBER;
1416
1417 l_inst_tbl nbr_tbl_type;
1418 l_buffer_limit number := 500;
1419
1420 BEGIN
1421 --l_all_csi_ii_id sql_string [ Blind search at unit level]
1422 IF (p_search_mr_instance_rec.unit_name is NOT NULL AND nvl(p_search_mr_instance_rec.part_number,'%')
1423 = '%' AND p_search_mr_instance_rec.serial_number is NULL) THEN
1424 FOR only_units_rec IN only_units_csr(p_search_mr_instance_rec.unit_name) LOOP
1425 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES (only_units_rec.csi_item_instance_id,0);
1426 END LOOP;
1427
1428 ELSE
1429 l_bind_index := 1;
1430 l_unit_related_ii_sql := 'select csii.instance_id from csi_item_instances csii ';
1431
1432 IF nvl(p_search_mr_instance_rec.part_number,'%')
1433 <> '%' THEN
1434 l_unit_related_ii_sql := l_unit_related_ii_sql || ', mtl_system_items_kfv mtl ';
1435 END IF;
1436
1437 IF p_search_mr_instance_rec.unit_name is NOT NULL THEN
1438 l_unit_related_ii_sql := l_unit_related_ii_sql || ', ahl_unit_config_headers uc ';
1439 END IF;
1440
1441 l_unit_related_ii_sql := l_unit_related_ii_sql || 'where 0=0 ';
1442
1443 IF nvl(p_search_mr_instance_rec.part_number,'%')
1444 <> '%' THEN
1445 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 ';
1446
1447 /*
1448 IF p_module_type = 'VWP' THEN
1449 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and csidv.concatenated_segments like :PART_NUMBER ';
1450 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.part_number;
1451 ELSE
1452 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and UPPER(csidv.concatenated_segments) like :PART_NUMBER ';
1453 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.part_number);
1454 END IF;
1455 */
1456
1457 /* Using UPPER() on concatenated_segments does not pick up any index and cost is very high ~ 45K in gsihrms
1458 * Also Ahmed Alomari's mail to appsperf_us (dt: 17-Feb-2006) says the following...
1459 * Part numbers are rarely case insensitive, so you can remove the upper.
1460 */
1461
1462 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and mtl.concatenated_segments like :PART_NUMBER ';
1463 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.part_number;
1464 l_bind_index := l_bind_index + 1;
1465 END IF;
1466
1467 IF p_search_mr_instance_rec.serial_number is NOT NULL THEN
1468 IF p_module_type = 'VWP' THEN
1469 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and csii.serial_number like :SERIAL_NUMBER ';
1470 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.serial_number;
1471 ELSE
1472 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and UPPER(csii.serial_number) like :SERIAL_NUMBER ';
1473 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.serial_number);
1474 END IF;
1475 l_bind_index := l_bind_index + 1;
1476 END IF;
1477
1478 IF p_search_mr_instance_rec.unit_name is NOT NULL THEN
1479 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and csii.instance_id = uc.csi_item_instance_id ';
1480 IF p_module_type = 'VWP' THEN
1481 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and uc.name like :UNIT_NAME ';
1482 l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.unit_name;
1483 ELSE
1484 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and UPPER(uc.name) like :UNIT_NAME ';
1485 l_bindvar_tbl(l_bind_index) := UPPER(p_search_mr_instance_rec.unit_name);
1486 END IF;
1487 l_bind_index := l_bind_index + 1;
1488 END IF;
1489
1490 IF p_search_mr_instance_rec.components_flag = 'N' THEN
1491 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)';
1492 END IF;
1493
1494 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
1495 (
1496 p_conditions_tbl => l_bindvar_tbl,
1497 p_sql_str => l_unit_related_ii_sql,
1498 p_x_csr => l_cur
1499 );
1500 -- use dynamic cursor to execute query
1501 LOOP
1502 FETCH l_cur BULK COLLECT INTO l_inst_tbl LIMIT l_buffer_limit;
1503 EXIT WHEN (l_inst_tbl.count = 0);
1504 FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1505 --FETCH l_cur INTO l_instance_id;
1506 --EXIT WHEN l_cur%NOTFOUND;
1507 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),0);
1508 END LOOP; -- l_ue_id_tbl loop
1509 l_inst_tbl.DELETE;
1510 END LOOP;
1511
1512 CLOSE l_cur;
1513 END IF;
1514
1515
1516 END populate_instances;
1517
1518 PROCEDURE populate_unit_instances(
1519 p_module_type IN VARCHAR2,
1520 p_search_ump_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_Ump_Rec_Type) IS
1521
1522
1523 CURSOR only_units_csr(p_unit_name in VARCHAR2) IS
1524 Select ahlu.csi_item_instance_id from ahl_unit_config_headers ahlu
1525 where UPPER(ahlu.name) like upper(p_unit_name);
1526
1527 l_unit_related_ii_sql VARCHAR2(4000);
1528
1529 l_bind_index NUMBER;
1530 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
1531
1532 -- dynamic cursor
1533 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
1534 --l_instance_id NUMBER;
1535
1536 l_inst_tbl nbr_tbl_type;
1537 L_BUFFER_LIMIT NUMBER := 500;
1538 L_DUE_FROM DATE;
1539 L_DUE_TO DATE;
1540
1541 BEGIN
1542 IF(P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
1543 IF (P_SEARCH_UMP_REC.DUE_WITHIN IS NOT NULL AND P_SEARCH_UMP_REC.DUE_WITHIN_UOM IS NOT NULL)THEN
1544 L_DUE_FROM := SYSDATE;
1545 IF(P_SEARCH_UMP_REC.DUE_WITHIN_UOM = 'HOUR')THEN
1546 L_DUE_TO := L_DUE_FROM + P_SEARCH_UMP_REC.DUE_WITHIN/24;
1547 ELSIF(P_SEARCH_UMP_REC.DUE_WITHIN_UOM = 'DAY')THEN
1548 L_DUE_TO := L_DUE_FROM + P_SEARCH_UMP_REC.DUE_WITHIN;
1549 END IF;
1550 END IF;
1551
1552 IF P_SEARCH_UMP_REC.DUE_FROM IS NOT NULL THEN
1553 L_DUE_FROM := TRUNC(P_SEARCH_UMP_REC.DUE_FROM);
1554 END IF;
1555 IF P_SEARCH_UMP_REC.DUE_TO IS NOT NULL THEN
1556 L_DUE_TO := TRUNC(P_SEARCH_UMP_REC.DUE_TO) + 1;
1557 END IF;
1558 END IF;
1559 --l_all_csi_ii_id sql_string [ Blind search at unit level]
1560 IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL AND NVL(P_SEARCH_UMP_REC.ITEM,'%')
1561 = '%' AND P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NULL) THEN
1562 IF (P_SEARCH_UMP_REC.ARRIVAL_ORG IS NULL) THEN
1563 FOR ONLY_UNITS_REC IN ONLY_UNITS_CSR(P_SEARCH_UMP_REC.UNIT_NAME) LOOP
1564 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES (ONLY_UNITS_REC.CSI_ITEM_INSTANCE_ID,0);
1565 END LOOP;
1566 ELSE
1567 L_BIND_INDEX := 1;
1568 l_unit_related_ii_sql := ' Select DISTINCT ahlu.csi_item_instance_id from ahl_unit_config_headers ahlu,ahl_unit_schedules aus
1569 where AUS.UNIT_CONFIG_HEADER_ID = AHLU.UNIT_CONFIG_HEADER_ID and UPPER(ahlu.name) like (:UNIT_NAME) ';
1570 L_BINDVAR_TBL(L_BIND_INDEX) := UPPER(P_SEARCH_UMP_REC.UNIT_NAME);
1571 L_BIND_INDEX := L_BIND_INDEX + 1;
1572 l_unit_related_ii_sql := l_unit_related_ii_sql || ' AND AUS.ARRIVAL_ORG_ID IN (SELECT ORGANIZATION_ID FROM HR_ALL_ORGANIZATION_UNITS HROU
1573 WHERE HROU.NAME LIKE :ORG_NAME) ';
1574 L_BINDVAR_TBL(L_BIND_INDEX) := P_SEARCH_UMP_REC.ARRIVAL_ORG;
1575 L_BIND_INDEX := L_BIND_INDEX + 1;
1576 IF(L_DUE_FROM IS NOT NULL)THEN
1577 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || 'AND AUS.EST_ARRIVAL_TIME >= NVL(:FROM_DATE , AUS.EST_ARRIVAL_TIME) ';
1578 L_BINDVAR_TBL(L_BIND_INDEX) := L_DUE_FROM;
1579 L_BIND_INDEX := L_BIND_INDEX + 1;
1580 END IF;
1581 IF(L_DUE_TO IS NOT NULL)THEN
1582 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || 'AND AUS.EST_ARRIVAL_TIME <= NVL(:TO_DATE , AUS.EST_ARRIVAL_TIME) ';
1583 L_BINDVAR_TBL(L_BIND_INDEX) := L_DUE_TO; -- a second before midnight
1584 L_BIND_INDEX := L_BIND_INDEX + 1;
1585 END IF;
1586 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
1587 (
1588 p_conditions_tbl => l_bindvar_tbl,
1589 p_sql_str => l_unit_related_ii_sql,
1590 P_X_CSR => L_CUR
1591 );
1592 -- use dynamic cursor to execute query
1593 LOOP
1594 FETCH L_CUR BULK COLLECT INTO L_INST_TBL LIMIT L_BUFFER_LIMIT;
1595 EXIT WHEN (L_INST_TBL.COUNT = 0);
1596 FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1597 --FETCH l_cur INTO l_instance_id;
1598 --EXIT WHEN l_cur%NOTFOUND;
1599 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(L_INST_TBL(J),0);
1600 END LOOP; -- l_ue_id_tbl loop
1601 L_INST_TBL.DELETE;
1602 END LOOP;
1603 CLOSE L_CUR;
1604 END IF;--end arrival org condition
1605 ELSE
1606 L_BIND_INDEX := 1;
1607 l_unit_related_ii_sql := 'select distinct csii.instance_id from csi_item_instances csii ';
1608
1609 IF nvl(p_search_ump_rec.item,'%')
1610 <> '%' THEN
1611 l_unit_related_ii_sql := l_unit_related_ii_sql || ', mtl_system_items_kfv mtl ';
1612 END IF;
1613
1614 IF p_search_ump_rec.unit_name is NOT NULL THEN
1615 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || ', ahl_unit_config_headers uc ';
1616 IF (P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
1617 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || ', ahl_unit_schedules aus ';
1618 END IF;
1619 END IF;
1620
1621 l_unit_related_ii_sql := l_unit_related_ii_sql || 'where 0=0 ';
1622
1623 IF nvl(p_search_ump_rec.item,'%')
1624 <> '%' THEN
1625 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 ';
1626
1627 /* Using UPPER() on concatenated_segments does not pick up any index and cost is very high ~ 45K in gsihrms
1628 * Also Ahmed Alomari's mail to appsperf_us (dt: 17-Feb-2006) says the following...
1629 * Part numbers are rarely case insensitive, so you can remove the upper.
1630 */
1631
1632 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and mtl.concatenated_segments like :PART_NUMBER ';
1633 l_bindvar_tbl(l_bind_index) := p_search_ump_rec.item;
1634 l_bind_index := l_bind_index + 1;
1635 END IF;
1636
1637 IF p_search_ump_rec.item_serial_number is NOT NULL THEN
1638 IF p_module_type = 'VWP' THEN
1639 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and csii.serial_number like :SERIAL_NUMBER ';
1640 l_bindvar_tbl(l_bind_index) := p_search_ump_rec.item_serial_number;
1641 ELSE
1642 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and UPPER(csii.serial_number) like :SERIAL_NUMBER ';
1643 l_bindvar_tbl(l_bind_index) := UPPER(p_search_ump_rec.item_serial_number);
1644 END IF;
1645 l_bind_index := l_bind_index + 1;
1646 END IF;
1647
1648 IF P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL THEN
1649 IF (P_SEARCH_UMP_REC.ARRIVAL_ORG IS NULL) THEN
1650 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || 'and csii.instance_id = uc.csi_item_instance_id ';
1651 ELSE
1652 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || 'and csii.instance_id = uc.csi_item_instance_id and AUS.UNIT_CONFIG_HEADER_ID = uc.UNIT_CONFIG_HEADER_ID ';
1653 END IF;
1654 IF p_module_type = 'VWP' THEN
1655 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and uc.name like :UNIT_NAME ';
1656 l_bindvar_tbl(l_bind_index) := p_search_ump_rec.unit_name;
1657 ELSE
1658 l_unit_related_ii_sql := l_unit_related_ii_sql || 'and UPPER(uc.name) like :UNIT_NAME ';
1659 l_bindvar_tbl(l_bind_index) := UPPER(p_search_ump_rec.unit_name);
1660 END IF;
1661 L_BIND_INDEX := L_BIND_INDEX + 1;
1662 IF (P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
1663 l_unit_related_ii_sql := l_unit_related_ii_sql || ' AND AUS.ARRIVAL_ORG_ID IN (SELECT ORGANIZATION_ID FROM HR_ALL_ORGANIZATION_UNITS HROU
1664 WHERE HROU.NAME LIKE :ORG_NAME) ';
1665 L_BINDVAR_TBL(L_BIND_INDEX) := P_SEARCH_UMP_REC.ARRIVAL_ORG;
1666 L_BIND_INDEX := L_BIND_INDEX + 1;
1667 IF(L_DUE_FROM IS NOT NULL)THEN
1668 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || 'AND AUS.EST_ARRIVAL_TIME >= NVL(:FROM_DATE , AUS.EST_ARRIVAL_TIME) ';
1669 L_BINDVAR_TBL(L_BIND_INDEX) := L_DUE_FROM;
1670 L_BIND_INDEX := L_BIND_INDEX + 1;
1671 END IF;
1672 IF(L_DUE_TO IS NOT NULL)THEN
1673 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || 'AND AUS.EST_ARRIVAL_TIME <= NVL(:TO_DATE , AUS.EST_ARRIVAL_TIME) ';
1674 L_BINDVAR_TBL(L_BIND_INDEX) := L_DUE_TO; -- a second before midnight
1675 L_BIND_INDEX := L_BIND_INDEX + 1;
1676 END IF;
1677 END IF;
1678 ELSE
1679 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || 'and exists (select ''x'' from ahl_unit_config_headers uc where uc.CSI_ITEM_INSTANCE_ID = csii.instance_id ) ';
1680 IF (P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
1681 l_unit_related_ii_sql := l_unit_related_ii_sql || ' and csii.instance_id IN (select uc.CSI_ITEM_INSTANCE_ID from ahl_unit_schedules aus,
1682 ahl_unit_config_headers uc where AUS.UNIT_CONFIG_HEADER_ID = UC.UNIT_CONFIG_HEADER_ID AND AUS.ARRIVAL_ORG_ID IN
1683 (SELECT ORGANIZATION_ID FROM HR_ALL_ORGANIZATION_UNITS HROU WHERE HROU.NAME LIKE :ORG_NAME) ';
1684 L_BINDVAR_TBL(L_BIND_INDEX) := P_SEARCH_UMP_REC.ARRIVAL_ORG;
1685 L_BIND_INDEX := L_BIND_INDEX + 1;
1686 IF(L_DUE_FROM IS NOT NULL)THEN
1687 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || 'AND AUS.EST_ARRIVAL_TIME >= NVL(:FROM_DATE , AUS.EST_ARRIVAL_TIME) ';
1688 L_BINDVAR_TBL(L_BIND_INDEX) := L_DUE_FROM;
1689 L_BIND_INDEX := L_BIND_INDEX + 1;
1690 END IF;
1691 IF(L_DUE_TO IS NOT NULL)THEN
1692 L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || 'AND AUS.EST_ARRIVAL_TIME <= NVL(:TO_DATE , AUS.EST_ARRIVAL_TIME) ';
1693 L_BINDVAR_TBL(L_BIND_INDEX) := L_DUE_TO; -- a second before midnight
1694 L_BIND_INDEX := L_BIND_INDEX + 1;
1695 END IF;
1696 l_unit_related_ii_sql := l_unit_related_ii_sql || ' ) ';
1697 END IF;
1698 END IF;
1699
1700 IF NVL(p_search_ump_rec.SHOW_DEPENDENT_REQUIREMENTS,'N') = 'N' THEN
1701 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)';
1702 END IF;
1703 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
1704 (
1705 p_conditions_tbl => l_bindvar_tbl,
1706 p_sql_str => l_unit_related_ii_sql,
1707 p_x_csr => l_cur
1708 );
1709 -- use dynamic cursor to execute query
1710 LOOP
1711 FETCH l_cur BULK COLLECT INTO l_inst_tbl LIMIT l_buffer_limit;
1712 EXIT WHEN (l_inst_tbl.count = 0);
1713 FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1714 --FETCH l_cur INTO l_instance_id;
1715 --EXIT WHEN l_cur%NOTFOUND;
1716 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),0);
1717 END LOOP; -- l_ue_id_tbl loop
1718 l_inst_tbl.DELETE;
1719 END LOOP;
1720
1721 CLOSE l_cur;
1722 END IF;
1723
1724
1725 END populate_unit_instances;
1726
1727 PROCEDURE populate_dependent_instances(
1728 p_module_type IN VARCHAR2)IS
1729
1730 CURSOR dependent_components_csr IS
1731 SELECT subject_id from csi_ii_relationships csii WHERE
1732 EXISTS (select 'x' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.subject_id)
1733 AND NOT EXISTS (select 'x' from AHL_APPLICABLE_INSTANCES where csi_item_instance_id = csii.subject_id)
1734 START WITH object_id IN (SELECT csi_item_instance_id FROM AHL_APPLICABLE_INSTANCES WHERE POSITION_ID = 0)
1735 AND trunc(nvl(csii.active_start_date, sysdate)) <= Trunc(sysdate)
1736 AND trunc(nvl(csii.active_end_date, sysdate+1)) > Trunc(sysdate)
1737 AND relationship_type_code = 'COMPONENT-OF'
1738 CONNECT BY PRIOR subject_id = object_id
1739 AND trunc(nvl(csii.active_start_date, sysdate)) <= Trunc(sysdate)
1740 AND trunc(nvl(csii.active_end_date, sysdate+1)) > Trunc(sysdate)
1741 AND relationship_type_code = 'COMPONENT-OF';
1742
1743 l_inst_tbl nbr_tbl_type;
1744 l_buffer_limit number := 500;
1745
1746
1747 BEGIN
1748 /*FOR dependent_component_rec IN dependent_components_csr
1749 LOOP
1750 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID)
1751 VALUES(dependent_component_rec.subject_id,1);
1752 END LOOP;*/
1753
1754 OPEN dependent_components_csr;
1755 LOOP
1756 FETCH dependent_components_csr BULK COLLECT INTO l_inst_tbl LIMIT l_buffer_limit;
1757 EXIT WHEN (l_inst_tbl.count = 0);
1758 FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1759 INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),1);
1760 END LOOP;
1761 l_inst_tbl.DELETE;
1762 END LOOP;
1763
1764 CLOSE dependent_components_csr;
1765
1766
1767 END populate_dependent_instances;
1768
1769
1770 --------------------------------------------------------------------------------------------
1771 -- Procedure name : Build_Ump_Search_query
1772 -- Type : Private
1773 -- Function : This procedure builds search query for planning workbench for the given search criterion.
1774 -- Pre-reqs :
1775 -- Parameters :
1776 --
1777 -- Standard IN Parameters :
1778 -- p_api_version IN NUMBER Required
1779 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1780 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1781 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1782 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
1783 -- Based on this flag, the API will set the default attributes.
1784 -- p_module_type IN VARCHAR2 Default NULL
1785 -- This will be null.
1786 -- Standard OUT Parameters :
1787 -- x_return_status OUT VARCHAR2 Required
1788 -- x_msg_count OUT NUMBER Required
1789 -- x_msg_data OUT VARCHAR2 Required
1790 --
1791 -- Build_Ump_Search_query Parameters :
1792 -- p_search_ump_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_Ump_Rec_Type Required
1793 -- The search criteria based on which the query needs to be built.
1794 -- x_ump_search_query OUT VARCHAR2 Required
1795 -- The query built based upon the search criterion passed.
1796 -- x_ump_bind_params OUT AHL_UMP_SMRINSTANCE_PVT.Ump_Bind_Param_Type Required
1797 -- Bind parameters for the UMP search query
1798 -- Version :
1799 -- Initial Version 1.0
1800 --
1801 -- End of Comments.
1802 --------------------------------------------------------------------------------------------
1803
1804 PROCEDURE BUILD_UMP_SEARCH_QUERY
1805 (
1806 P_API_VERSION IN NUMBER,
1807 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1808 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
1809 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1810 P_DEFAULT IN VARCHAR2 := FND_API.G_TRUE,
1811 P_MODULE_TYPE IN VARCHAR2 := NULL,
1812 P_SEARCH_UMP_REC IN AHL_UMP_SMRINSTANCE_PVT.SEARCH_UMP_REC_TYPE,
1813 X_UMP_SEARCH_QUERY OUT NOCOPY AHL_UMP_SMRINSTANCE_PVT.UMP_SEARCH_QUERY_TYPE,
1814 X_UMP_BIND_PARAMS OUT NOCOPY AHL_UMP_SMRINSTANCE_PVT.UMP_BIND_PARAM_TBL_TYPE,
1815 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1816 X_MSG_COUNT OUT NOCOPY NUMBER,
1817 X_MSG_DATA OUT NOCOPY VARCHAR2 ) IS
1818
1819 L_API_VERSION CONSTANT NUMBER := 1.0;
1820 L_API_NAME CONSTANT VARCHAR2(30) := 'Build_Ump_Search_query';
1821 L_UMP_SEARCH_QUERY VARCHAR2(31000);
1822 L_MR_SEARCH_QUERY VARCHAR2(31000);
1823 L_NR_SEARCH_QUERY VARCHAR2(31000);
1824 L_DUMMY_SEARCH_QUERY VARCHAR2(31000);
1825 L_BIND_INDEX NUMBER;
1826 L_DUE_FROM DATE;
1827 L_DUE_TO DATE;
1828 L_DURATION NUMBER;
1829 L_MR_SEARCHED BOOLEAN;
1830 L_NR_SEARCHED BOOLEAN;
1831
1832
1833 BEGIN
1834
1835 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
1836 FND_LOG.STRING
1837 (
1838 G_DEBUG_PROC,
1839 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Build_Ump_Search_query.start',
1840 'At the start of PLSQL procedure'
1841 );
1842 END IF;
1843 -- Standard call to check for call compatibility
1844 IF NOT FND_API.COMPATIBLE_API_CALL(L_API_VERSION, P_API_VERSION, L_API_NAME,
1845 G_PKG_NAME) THEN
1846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1847 END IF;
1848
1849 -- Initialize message list if p_init_msg_list is set to TRUE
1850 IF FND_API.TO_BOOLEAN(P_INIT_MSG_LIST) THEN
1851 FND_MSG_PUB.INITIALIZE;
1852 END IF;
1853
1854 -- Initialize API return status to success
1855 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1856 --Build query here
1857 L_DUMMY_SEARCH_QUERY := 'select ''N'' select_flag,
1858 ''Show'' details, null unit_name,null prog_type,null mr_header_id, null title,
1859 null concatenated_segments,null serial_number,null name,null uom_code,
1860 null due_counter_value,''View all UOMs'' view_all_uoms,null earliest_due_date,null due_date,null latest_due_date,
1861 null date_run,SYSDATE scheduledDate,null visit_name,null sr_incident_id,null sr_number,null unit_effectivity_id,null unit_config_header_id,
1862 null visit_id,null fleet , null operatingOrg, null mc , null ucPosition , null Eng_Org,
1863 null implementstatus ,null progsubtype ,null servicecat , null mrServiceType ,null mrduration , null MrDurationUOM , null PrimaryVisitType ,
1864 null scheduledvisittype , null scheduledVisitOrg from dual where 1=2';
1865
1866 L_MR_SEARCHED := TRUE;
1867 L_NR_SEARCHED := TRUE;
1868 IF((P_SEARCH_UMP_REC.PROGRAM_SUB_TYPE IS NOT NULL OR P_SEARCH_UMP_REC.MR_IMPLEMENT_STATUS IS NOT NULL
1869 OR P_SEARCH_UMP_REC.ENGINEERING_ORG IS NOT NULL OR P_SEARCH_UMP_REC.PRIMARY_VISIT_TYPE IS NOT NULL
1870 OR P_SEARCH_UMP_REC.SERVICE_CATEGORY IS NOT NULL OR P_SEARCH_UMP_REC.MAX_DURATION IS NOT NULL
1871 OR P_SEARCH_UMP_REC.MR_SERVICE_TYPE IS NOT NULL) -- Sthilak - CAM change - added mr_service_type
1872
1873 AND (P_SEARCH_UMP_REC.NR_TYPE IS NULL
1874 AND ( P_SEARCH_UMP_REC.SHOW_ONLY_NR_REQ_FLAG IS NULL OR P_SEARCH_UMP_REC.SHOW_ONLY_NR_REQ_FLAG = 'N')))THEN
1875 L_MR_SEARCHED := TRUE;
1876 L_NR_SEARCHED := FALSE;
1877 ELSIF ((P_SEARCH_UMP_REC.PROGRAM_SUB_TYPE IS NULL
1878 AND P_SEARCH_UMP_REC.MR_IMPLEMENT_STATUS IS NULL AND P_SEARCH_UMP_REC.ENGINEERING_ORG IS NULL
1879 AND P_SEARCH_UMP_REC.PRIMARY_VISIT_TYPE IS NULL OR P_SEARCH_UMP_REC.SERVICE_CATEGORY IS NULL
1880 AND P_SEARCH_UMP_REC.MAX_DURATION IS NULL AND P_SEARCH_UMP_REC.MR_SERVICE_TYPE IS NULL) -- Sthilak - CAM change - added mr_service_type
1881 AND (P_SEARCH_UMP_REC.NR_TYPE IS NOT NULL OR
1882 ( P_SEARCH_UMP_REC.SHOW_ONLY_NR_REQ_FLAG IS NOT NULL AND P_SEARCH_UMP_REC.SHOW_ONLY_NR_REQ_FLAG = 'Y')))THEN
1883 L_MR_SEARCHED := FALSE;
1884 L_NR_SEARCHED := TRUE;
1885 ELSIF((P_SEARCH_UMP_REC.PROGRAM_SUB_TYPE IS NOT NULL
1886 OR P_SEARCH_UMP_REC.MR_IMPLEMENT_STATUS IS NOT NULL OR P_SEARCH_UMP_REC.ENGINEERING_ORG IS NOT NULL
1887 OR P_SEARCH_UMP_REC.PRIMARY_VISIT_TYPE IS NOT NULL OR P_SEARCH_UMP_REC.SERVICE_CATEGORY IS NOT NULL
1888 OR P_SEARCH_UMP_REC.MAX_DURATION IS NOT NULL OR P_SEARCH_UMP_REC.MR_SERVICE_TYPE IS NOT NULL)
1889 AND (P_SEARCH_UMP_REC.NR_TYPE IS NOT NULL OR
1890 ( P_SEARCH_UMP_REC.SHOW_ONLY_NR_REQ_FLAG IS NOT NULL AND P_SEARCH_UMP_REC.SHOW_ONLY_NR_REQ_FLAG = 'Y')))THEN
1891 L_MR_SEARCHED := FALSE;
1892 L_NR_SEARCHED := FALSE;
1893 END IF;
1894
1895
1896 L_BIND_INDEX :=1;
1897
1898 IF(L_MR_SEARCHED)THEN
1899 IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
1900 <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL) THEN
1901 L_MR_SEARCH_QUERY := 'select /*+ dynamic_sampling(AAI1 4) */ ';
1902 ELSE
1903 L_MR_SEARCH_QUERY := 'select ';
1904 END IF;
1905 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' ''N'' select_flag, ''Show'' details,ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id) unit_name,
1906 (select meaning from fnd_lookup_values where lookup_code = mrb.PROGRAM_TYPE_CODE and lookup_type = ''AHL_FMP_MR_PROGRAM_TYPE'' and language = userenv(''LANG'')) prog_type ,
1907 B.MR_HEADER_ID,MRB.TITLE,MTL.CONCATENATED_SEGMENTS concatenated_segments,csib.serial_number,
1908 (select c.name from ahl_mr_intervals i,csi_counter_template_vl c where b.mr_interval_id = i.mr_interval_id and i.counter_id = c.counter_id) name ,
1909 (select c.uom_code from ahl_mr_intervals i,csi_counter_template_vl c where b.mr_interval_id = i.mr_interval_id AND I.COUNTER_ID = C.COUNTER_ID) uom_code,
1910 AHL_UMP_SMRINSTANCE_PVT.GET_UOM_REMAIN(b.unit_effectivity_id) DUE_COUNTER_VALUE, ''View all UOMs'' view_all_uoms, B.EARLIEST_DUE_DATE,B.DUE_DATE,B.LATEST_DUE_DATE,b.date_run,
1911 (select vst.START_DATE_TIME from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
1912 and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''and rownum < 2) scheduledDate,
1913 (select visit_number from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id and task.unit_effectivity_id = B.unit_effectivity_id
1914 and task.status_code <> ''DELETED'' and rownum < 2) visit_name,
1915 null sr_incident_id,null sr_number,b.unit_effectivity_id,
1916 (Select unit_config_header_id from AHL_UNIT_CONFIG_HEADERS where name = ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id)) unit_config_header_id,
1917 (select vst.visit_id from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
1918 and task.unit_effectivity_id = B.unit_effectivity_id and task.status_code <> ''DELETED''and rownum < 2) visit_id,
1919 (select name from ahl_fleet_headers_b flt where flt.fleet_header_id = b.fleet_header_id) fleet ,
1920 (SELECT HROU.NAME FROM ahl_fleet_headers_b flt, HR_ALL_ORGANIZATION_UNITS HROU where flt.OPERATING_ORG_ID = hrou.organization_id
1921 and flt.fleet_header_id = b.fleet_header_id and rownum < 2) operatingorg ,
1922 (select master_config_name from ahl_unit_header_details_v ucd where ucd.name = ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id)) mc,
1923 (Select position_ref_meaning from ahl_unit_details_v ucd where ucd.csi_item_instance_id = b.csi_item_instance_id and rownum< 2) ucposition,
1924 MAINTTORG.MR_MAINTENANCE_ORG_NAME eng_org,
1925 (select meaning from fnd_lookup_values where lookup_code = MRB.IMPLEMENT_STATUS_CODE and lookup_type = ''AHL_FMP_MR_IMPLEMENT_STATUS'' and language = userenv(''LANG'')) implementstatus ,
1926 (select meaning from fnd_lookup_values where lookup_code = mrb.PROGRAM_SUBTYPE_CODE and lookup_type = ''AHL_FMP_MR_PROGRAM_SUBTYPE'' and language = userenv(''LANG'')) progsubtype ,
1927 (select meaning from fnd_lookup_values where lookup_code = mrb.SPACE_CATEGORY_CODE and lookup_type = ''AHL_LTP_SPACE_CATEGORY'' and language = userenv(''LANG'')) servicecat ,
1928 (select meaning from fnd_lookup_values where lookup_code = mrb.SERVICE_TYPE_CODE and lookup_type = ''AHL_FMP_MR_SERVICE_TYPE'' and language = userenv(''LANG'')) mrServiceType ,
1929 mrb.down_time mrduration , mrb.uom_code MrDurationUOM , to_char(null) primaryvisittype ,
1930 (select flvt.meaning from ahl_visits_b vst,ahl_visit_tasks_b task,fnd_lookup_values flvt where vst.visit_id = task.visit_id
1931 and task.unit_effectivity_id = B.unit_effectivity_id and task.status_code <> ''DELETED'' and flvt.lookup_type = ''AHL_PLANNING_VISIT_TYPE''
1932 and flvt.lookup_code = vst.visit_type_code and flvt.language = userenv(''LANG'') and rownum < 2) scheduledvisittype ,
1933 (Select hrou.name from ahl_visits_b vst,ahl_visit_tasks_b task,hr_all_organization_units hrou
1934 where vst.visit_id = task.visit_id and task.unit_effectivity_id = B.unit_effectivity_id and task.status_code <> ''DELETED''
1935 and vst.organization_id = hrou.organization_id and rownum < 2) scheduledVisitOrg
1936 FROM AHL_UNIT_EFFECTIVITIES_B B, ahl_mr_headers_b mrb, CSI_ITEM_INSTANCES CSIB, mtl_system_items_kfv mtl,
1937 (SELECT AUE.unit_effectivity_id, ( CASE WHEN AUE.FLEET_HEADER_ID IS NULL AND AMO.OPERATING_ORG_ID IS NOT NULL
1938 THEN NULL
1939 WHEN AUE.FLEET_HEADER_ID IS NOT NULL AND AMO.OPERATING_ORG_ID IS NOT NULL AND AFH.OPERATING_ORG_ID != AMO.OPERATING_ORG_ID
1940 THEN (SELECT NAME FROM HR_ORGANIZATION_UNITS WHERE ORGANIZATION_ID= AFH.OPERATING_ORG_ID)
1941 WHEN AUE.FLEET_HEADER_ID IS NOT NULL AND AMO.MR_TITLE IS NULL
1942 THEN(SELECT NAME FROM HR_ORGANIZATION_UNITS WHERE ORGANIZATION_ID= AFH.OPERATING_ORG_ID)
1943 ELSE (SELECT NAME FROM HR_ORGANIZATION_UNITS WHERE ORGANIZATION_ID= AMO.MAINTENANCE_ORG_ID) END) MR_MAINTENANCE_ORG_NAME FROM AHL_UNIT_EFFECTIVITIES_B AUE, AHL_MR_HEADERS_B AMH, AHL_MR_ORGANIZATIONS AMO, AHL_FLEET_HEADERS_B AFH
1944 WHERE AUE.MR_HEADER_ID = AMH.MR_HEADER_ID (+) AND AMH.TITLE = AMO.MR_TITLE (+) AND AUE.FLEET_HEADER_ID = AFH.FLEET_HEADER_ID (+)
1945 AND ( (AUE.FLEET_HEADER_ID IS NULL AND AMO.OPERATING_ORG_ID IS NULL) OR (AFH.OPERATING_ORG_ID IS NULL
1946 AND AMO.OPERATING_ORG_ID IS NOT NULL AND (0 = (SELECT COUNT(MR_ORGANIZATION_ID) FROM AHL_MR_ORGANIZATIONS
1947 WHERE MR_TITLE = AMO.MR_TITLE AND OPERATING_ORG_ID IS NULL ))) OR (AUE.FLEET_HEADER_ID IS NOT NULL
1948 AND ((AFH.OPERATING_ORG_ID = AMO.OPERATING_ORG_ID) OR ( (0 = (SELECT COUNT(MR_ORGANIZATION_ID) FROM AHL_MR_ORGANIZATIONS
1949 WHERE MR_TITLE = AMO.MR_TITLE AND OPERATING_ORG_ID = AFH.OPERATING_ORG_ID ))AND AFH.OPERATING_ORG_ID != NVL(AMO.OPERATING_ORG_ID,-1)
1950 AND AMO.OPERATING_ORG_ID IS NULL))) OR (AUE.FLEET_HEADER_ID IS NOT NULL AND AMO.OPERATING_ORG_ID IS NOT NULL
1951 AND ( (0 = (SELECT COUNT(MR_ORGANIZATION_ID) FROM AHL_MR_ORGANIZATIONS WHERE MR_TITLE = AMO.MR_TITLE
1952 AND (OPERATING_ORG_ID IS NULL OR OPERATING_ORG_ID = AFH.OPERATING_ORG_ID))))) ) ) MAINTTORG ';
1953 IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
1954 <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL OR P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
1955 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' ,AHL_APPLICABLE_INSTANCES AAI1 ';
1956 END IF;
1957 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY ||
1958 'where b.mr_header_id = mrb.mr_header_id
1959 AND B.CSI_ITEM_INSTANCE_ID = csib.INSTANCE_ID
1960 AND csib.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
1961 and csib.inv_master_organization_id = mtl.organization_id
1962 AND B.APPLICATION_USG_CODE = FND_PROFILE.VALUE(''AHL_APPLN_USAGE'')
1963 and b.object_type = ''MR''
1964 and b.preceding_ue_id IS NULL
1965 and NVL(B.status_code,''INIT-DUE'' ) = ''INIT-DUE'' and MAINTTORG.unit_effectivity_id = b.unit_effectivity_id ';
1966 IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
1967 <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL OR P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
1968 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' AND B.CSI_ITEM_INSTANCE_ID = AAI1.CSI_ITEM_INSTANCE_ID ';
1969 ELSE
1970 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' AND ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id) IS NOT NULL ';
1971 END IF;
1972
1973 /*IF(P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL) THEN
1974 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.UNIT_NAME;
1975 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id) like (:' ||L_BIND_INDEX ||')';
1976 L_BIND_INDEX := L_BIND_INDEX+1;
1977 END IF;*/
1978 IF(P_SEARCH_UMP_REC.MR_TITLE IS NOT NULL) THEN
1979 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.MR_TITLE;
1980 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and MRB.TITLE like (:' ||L_BIND_INDEX ||')';
1981 L_BIND_INDEX := L_BIND_INDEX+1;
1982 END IF;
1983 /*IF(P_SEARCH_UMP_REC.ITEM IS NOT NULL) THEN
1984 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.ITEM;
1985 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and MTL.CONCATENATED_SEGMENTS like (:' ||L_BIND_INDEX ||')';
1986 L_BIND_INDEX := L_BIND_INDEX+1;
1987 END IF;*/
1988 /*IF(P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL) THEN
1989 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER;
1990 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and csib.SERIAL_NUMBER like (:' ||L_BIND_INDEX ||')';
1991 L_BIND_INDEX := L_BIND_INDEX+1;
1992 END IF;*/
1993 IF(P_SEARCH_UMP_REC.PROGRAM_TYPE IS NOT NULL) THEN
1994 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.PROGRAM_TYPE;
1995 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and mrb.PROGRAM_TYPE_CODE like (:' ||L_BIND_INDEX ||')';
1996 L_BIND_INDEX := L_BIND_INDEX+1;
1997 END IF;
1998 IF(P_SEARCH_UMP_REC.PROGRAM_SUB_TYPE IS NOT NULL) THEN
1999 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.PROGRAM_SUB_TYPE;
2000 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and mrb.PROGRAM_SUBTYPE_CODE like (:' ||L_BIND_INDEX ||')';
2001 L_BIND_INDEX := L_BIND_INDEX+1;
2002 END IF;
2003
2004 /* Sthilak CAM change begin */
2005 IF(P_SEARCH_UMP_REC.MR_SERVICE_TYPE IS NOT NULL) THEN
2006 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.MR_SERVICE_TYPE;
2007 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and mrb.SERVICE_TYPE_CODE = (:' ||L_BIND_INDEX ||')';
2008 L_BIND_INDEX := L_BIND_INDEX+1;
2009 END IF;
2010 /* Sthilak CAM change end */
2011
2012 IF(P_SEARCH_UMP_REC.FLEET IS NOT NULL) THEN
2013 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.FLEET;
2014 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and exists (select ''x'' from ahl_fleet_headers_b flt
2015 where flt.fleet_header_id = b.fleet_header_id and flt.name like (:' ||L_BIND_INDEX ||'))';
2016 L_BIND_INDEX := L_BIND_INDEX+1;
2017 END IF;
2018 IF(P_SEARCH_UMP_REC.MR_IMPLEMENT_STATUS IS NOT NULL) THEN
2019 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.MR_IMPLEMENT_STATUS;
2020 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and MRB.IMPLEMENT_STATUS_CODE like (:' ||L_BIND_INDEX ||')';
2021 L_BIND_INDEX := L_BIND_INDEX+1;
2022 END IF;
2023 IF(P_SEARCH_UMP_REC.MR_SCHEDULED_STATUS IS NOT NULL AND P_SEARCH_UMP_REC.MR_SCHEDULED_STATUS IN ('SCHEDULED','UNSCHEDULED') ) THEN
2024 IF(P_SEARCH_UMP_REC.MR_SCHEDULED_STATUS = 'SCHEDULED')THEN
2025 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and EXISTS (Select ''x'' from ahl_visit_tasks_b vts where vts.unit_effectivity_id =
2026 B.unit_effectivity_id AND vts.status_code <> ''DELETED''and rownum < 2) ';
2027 ELSE
2028 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and NOT EXISTS (Select ''x'' from ahl_visit_tasks_b vts
2029 where vts.unit_effectivity_id = B.unit_effectivity_id AND vts.status_code <> ''DELETED''and rownum < 2) ';
2030 END IF;
2031 END IF;
2032 IF(P_SEARCH_UMP_REC.VISIT_NUMBER IS NOT NULL) THEN
2033 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.VISIT_NUMBER;
2034 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b vts
2035 where vst.visit_id = vts.visit_id and vts.unit_effectivity_id = B.unit_effectivity_id
2036 and vst.visit_number like (:' || L_BIND_INDEX || ') and vts.status_code <> ''DELETED''and rownum < 2) ';
2037 L_BIND_INDEX := L_BIND_INDEX+1;
2038 END IF;
2039 IF(P_SEARCH_UMP_REC.SCHEDULED_VISIT_TYPE IS NOT NULL) THEN
2040 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.SCHEDULED_VISIT_TYPE;
2041 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task,fnd_lookup_values flvt where vst.visit_id = task.visit_id
2042 and task.unit_effectivity_id = B.unit_effectivity_id and flvt.lookup_type = ''AHL_PLANNING_VISIT_TYPE''
2043 and flvt.lookup_code = vst.visit_type_code and flvt.language = userenv(''LANG'') and flvt.meaning like (:' || L_BIND_INDEX || ') and task.status_code <> ''DELETED''and rownum < 2) ';
2044 L_BIND_INDEX := L_BIND_INDEX+1;
2045 END IF;
2046 IF(P_SEARCH_UMP_REC.VISIT_ORG_NAME IS NOT NULL) THEN
2047 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.VISIT_ORG_NAME;
2048 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task ,hr_all_organization_units hrou
2049 where vst.visit_id = task.visit_id and task.unit_effectivity_id = B.unit_effectivity_id and
2050 vst.organization_id = hrou.organization_id
2051 and hrou.name like (:' || L_BIND_INDEX || ') and task.status_code <> ''DELETED''and rownum < 2) ';
2052 L_BIND_INDEX := L_BIND_INDEX+1;
2053 END IF;
2054 /* *********************************** BEGIN: SHOW TOLERANCE ****************************************** */
2055 IF P_SEARCH_UMP_REC.DUE_FROM IS NOT NULL THEN
2056 L_DUE_FROM := P_SEARCH_UMP_REC.DUE_FROM;
2057 END IF;
2058 IF P_SEARCH_UMP_REC.DUE_TO IS NOT NULL THEN
2059 L_DUE_TO := TRUNC(P_SEARCH_UMP_REC.DUE_TO) + 1;
2060 END IF;
2061 /* *********************************** BEGIN: SHOW TOLERANCE ****************************************** */
2062 IF ((P_SEARCH_UMP_REC.DUE_FROM IS NOT NULL AND P_SEARCH_UMP_REC.DUE_TO IS NOT NULL) AND (P_SEARCH_UMP_REC.DUE_WITHIN IS NOT NULL))THEN
2063 FND_MESSAGE.SET_NAME('AHL','AHL_UMP_MR_DFROM_DWITHIN_CFLT');
2064 FND_MSG_PUB.ADD;
2065 RAISE FND_API.G_EXC_ERROR;
2066 ELSIF ((P_SEARCH_UMP_REC.DUE_FROM IS NULL OR P_SEARCH_UMP_REC.DUE_TO IS NULL) AND (P_SEARCH_UMP_REC.DUE_WITHIN IS NOT NULL AND (P_SEARCH_UMP_REC.DUE_WITHIN_UOM IS NULL
2067 OR P_SEARCH_UMP_REC.DUE_WITHIN_UOM NOT IN ('HOUR','DAY')) ))THEN
2068 FND_MESSAGE.SET_NAME('AHL','AHL_UMP_MR_DWITHIN_UOM_NLL');
2069 FND_MSG_PUB.ADD;
2070 RAISE FND_API.G_EXC_ERROR;
2071 ELSIF (P_SEARCH_UMP_REC.DUE_WITHIN IS NOT NULL AND P_SEARCH_UMP_REC.DUE_WITHIN_UOM IS NOT NULL)THEN
2072 IF(L_DUE_FROM IS NULL AND L_DUE_TO IS NULL)THEN
2073 L_DUE_FROM := SYSDATE;
2074 END IF;
2075 IF(L_DUE_FROM IS NOT NULL)THEN
2076 IF(P_SEARCH_UMP_REC.DUE_WITHIN_UOM = 'HOUR')THEN
2077 L_DUE_TO := (L_DUE_FROM + P_SEARCH_UMP_REC.DUE_WITHIN/24) + 1;
2078 ELSIF(P_SEARCH_UMP_REC.DUE_WITHIN_UOM = 'DAY')THEN
2079 L_DUE_TO := (L_DUE_FROM + P_SEARCH_UMP_REC.DUE_WITHIN) + 1;
2080 END IF;
2081 ELSIF(L_DUE_TO IS NOT NULL)THEN
2082 IF(P_SEARCH_UMP_REC.DUE_WITHIN_UOM = 'HOUR')THEN
2083 L_DUE_FROM := (L_DUE_TO - P_SEARCH_UMP_REC.DUE_WITHIN/24) -1;
2084 ELSIF(P_SEARCH_UMP_REC.DUE_WITHIN_UOM = 'DAY')THEN
2085 L_DUE_FROM := (L_DUE_TO - P_SEARCH_UMP_REC.DUE_WITHIN) - 1;
2086 END IF;
2087 END IF;
2088 END IF;
2089 -- Show Tolerance is N
2090 IF P_SEARCH_UMP_REC.SHOW_TOLERANCE ='N' THEN
2091 --Due-From Date Check
2092 IF L_DUE_FROM IS NOT NULL THEN
2093 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' AND B.DUE_DATE >= (:' ||L_BIND_INDEX||')';
2094 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2095 L_BIND_INDEX := L_BIND_INDEX + 1;
2096 END IF;
2097 --Due-To Date Check
2098 IF L_DUE_TO IS NOT NULL THEN
2099 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' AND B.DUE_DATE < (:' ||L_BIND_INDEX||')';
2100 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;--TRUNC(p_search_mr_instance_rec.due_to);//bug8265049
2101 L_BIND_INDEX := L_BIND_INDEX + 1;
2102 END IF;
2103 ELSE -- Show Tolerance is Y
2104 IF ( L_DUE_FROM IS NOT NULL AND L_DUE_TO IS NOT NULL ) THEN
2105 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' AND (
2106 ((:' ||L_BIND_INDEX||') <= nvl(B.earliest_due_date, B.due_date)
2107 AND nvl(B.earliest_due_date, B.due_date) < (:' ||(L_BIND_INDEX + 1) ||'))
2108 OR (nvl(B.earliest_due_date, B.due_date) <= (:' ||(L_BIND_INDEX + 2) ||')
2109 AND (:' ||(L_BIND_INDEX + 3) ||') < nvl(B.latest_due_date, B.due_date))
2110 OR ((:' ||(L_BIND_INDEX + 4) ||') <= nvl(B.latest_due_date, B.due_date)
2111 AND nvl(B.latest_due_date, B.due_date) < (:' ||(L_BIND_INDEX + 5) ||'))
2112 )';
2113
2114 -- due from2 and due to2
2115 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2116 L_BIND_INDEX := L_BIND_INDEX + 1;
2117 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;
2118 L_BIND_INDEX := L_BIND_INDEX + 1;
2119 -- due from3 and due to3
2120 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2121 L_BIND_INDEX := L_BIND_INDEX + 1;
2122 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;
2123 L_BIND_INDEX := L_BIND_INDEX + 1;
2124 -- due from4 and due to4
2125 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2126 L_BIND_INDEX := L_BIND_INDEX + 1;
2127 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;
2128 L_BIND_INDEX := L_BIND_INDEX + 1;
2129 ELSIF ( L_DUE_FROM IS NOT NULL AND L_DUE_TO IS NULL ) THEN
2130 -- Only Due-From Date is there and Due-To Date is NULL
2131 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' AND
2132 ( ( ( (:' ||L_BIND_INDEX ||') <= nvl(B.earliest_due_date, B.due_date) ) )
2133 OR ( ( (:' ||(L_BIND_INDEX + 1) ||') <= nvl(B.latest_due_date, B.due_date) ) ) )';
2134 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2135 L_BIND_INDEX := L_BIND_INDEX + 1;
2136 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2137 L_BIND_INDEX := L_BIND_INDEX + 1;
2138 ELSIF ( L_DUE_FROM IS NULL AND L_DUE_TO IS NOT NULL ) THEN
2139 -- Only Due-To Date is there and Due-From Date is NULL
2140 --adivenka modified the following part of the query for bug# 4315128
2141 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' AND ( ( ( nvl(B.earliest_due_date, B.due_date) < (:' ||L_BIND_INDEX ||') )
2142 ) OR ( ( nvl(B.latest_due_date, B.due_date) < (:' ||(L_BIND_INDEX + 1) ||') )) )';
2143 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;
2144 L_BIND_INDEX := L_BIND_INDEX + 1;
2145 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;
2146 L_BIND_INDEX := L_BIND_INDEX + 1;
2147 END IF;--Case is ignored when Both Due-From and Due-To Dates are NOT here
2148 END IF;
2149 /* ***********************************END: SHOW TOLERANCE ****************************************** */
2150 -- Max Duration
2151 IF (P_SEARCH_UMP_REC.MAX_DURATION IS NOT NULL AND (P_SEARCH_UMP_REC.MAX_DURATION_UOM IS NULL
2152 OR P_SEARCH_UMP_REC.MAX_DURATION_UOM NOT IN ('HOUR','DAY')) )THEN
2153 FND_MESSAGE.SET_NAME('AHL','AHL_UMP_MR_MXDUR_UOM_NLL');
2154 FND_MSG_PUB.ADD;
2155 RAISE FND_API.G_EXC_ERROR;
2156 ELSIF (P_SEARCH_UMP_REC.MAX_DURATION IS NOT NULL AND P_SEARCH_UMP_REC.MAX_DURATION_UOM IS NOT NULL)THEN
2157 L_DURATION := 0;
2158 IF(P_SEARCH_UMP_REC.MAX_DURATION_UOM = 'HOUR')THEN
2159 L_DURATION := P_SEARCH_UMP_REC.MAX_DURATION/24;
2160 ELSIF(P_SEARCH_UMP_REC.MAX_DURATION_UOM = 'DAY')THEN
2161 L_DURATION := P_SEARCH_UMP_REC.MAX_DURATION;
2162 END IF;
2163 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DURATION;
2164 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY ||
2165 ' and DECODE(mrb.uom_code,''HOURS'',NVL(mrb.down_time,0)/24,
2166 ''DAYS'',NVL(mrb.down_time,0),
2167 ''MINUTES'',NVL(mrb.down_time,0)/1440,
2168 ''MONTHS'',NVL(mrb.down_time,0)*30,
2169 ''WEEKS'',NVL(mrb.down_time,0)*7,0) <= (:' ||L_BIND_INDEX ||') ';
2170 L_BIND_INDEX := L_BIND_INDEX+1;
2171 END IF;
2172 IF(P_SEARCH_UMP_REC.SERVICE_CATEGORY IS NOT NULL) THEN
2173 BEGIN
2174 IF(TO_NUMBER(P_SEARCH_UMP_REC.SERVICE_CATEGORY) >= 0)THEN
2175 NULL;
2176 END IF;
2177 EXCEPTION
2178 WHEN OTHERS THEN
2179 FND_MESSAGE.SET_NAME('AHL','AHL_UMP_MR_SCAT_NON_NUM');
2180 FND_MSG_PUB.ADD;
2181 RAISE FND_API.G_EXC_ERROR;
2182 END;
2183 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.SERVICE_CATEGORY;
2184 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and MRB.SERVICE_CATEGORY_RANK >= (:' ||L_BIND_INDEX ||')';
2185 L_BIND_INDEX := L_BIND_INDEX+1;
2186 END IF;
2187 -- operating org
2188 if(p_search_ump_rec.OPERATING_ORG is not null) then
2189 x_ump_bind_params(l_bind_index).bind_param := p_search_ump_rec.operating_org;
2190 l_mr_search_query := l_mr_search_query || ' and EXISTS (select ''x'' FROM AHL_FLEET_HEADERS_B FLT, HR_ALL_ORGANIZATION_UNITS HROU where
2191 FLT.OPERATING_ORG_ID = HROU.ORGANIZATION_ID and flt.fleet_header_id = b.fleet_header_id and HROU.NAME like (:' || l_bind_index || '))';
2192 l_bind_index := l_bind_index+1;
2193 end if;
2194 -- engineering org
2195 if(p_search_ump_rec.ENGINEERING_ORG is not null) then
2196 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.ENGINEERING_ORG;
2197 l_mr_search_query := l_mr_search_query || ' and MAINTTORG.MR_MAINTENANCE_ORG_NAME like (:' || l_bind_index || ')';
2198 l_bind_index := l_bind_index+1;
2199 end if;
2200 IF(NVL(P_SEARCH_UMP_REC.SHOW_VISIT_DATE_VIOLATIONS,'N') = 'Y') THEN
2201 L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
2202 and task.unit_effectivity_id = B.unit_effectivity_id
2203 and task.status_code <> ''DELETED'' and B.due_date IS NOT NULL AND TRUNC(B.DUE_DATE) < TRUNC(vst.START_DATE_TIME) ) ';
2204 END IF;
2205 END IF; -- MR searched
2206 IF(L_NR_SEARCHED)THEN
2207 IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
2208 <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL) THEN
2209 L_NR_SEARCH_QUERY := 'select /*+ dynamic_sampling(AAI1 4) */ ';
2210 ELSE
2211 L_NR_SEARCH_QUERY := 'select ';
2212 END IF;
2213 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' ''N'' select_flag, ''Show'' details,ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id) unit_name,
2214 (select meaning from fnd_lookup_values where lookup_type = ''AHL_FMP_MR_PROGRAM_TYPE'' and lookup_code = ''NON-ROUTINE''
2215 AND LANGUAGE = USERENV(''LANG'')) prog_type,
2216 null mr_header_id,(CITT.NAME || ''-'' || CSB.INCIDENT_NUMBER) TITLE,MTL.CONCATENATED_SEGMENTS concatenated_segments,
2217 csib.serial_number,null name ,null uom_code,AHL_UMP_SMRINSTANCE_PVT.GET_UOM_REMAIN(b.unit_effectivity_id) due_counter_value,''View all UOMs'' view_all_uoms,
2218 B.EARLIEST_DUE_DATE,B.DUE_DATE,B.LATEST_DUE_DATE,b.date_run,
2219 (select vst.START_DATE_TIME from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
2220 and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''and rownum < 2) scheduledDate,
2221 (select visit_number from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
2222 and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''and rownum < 2) visit_name,
2223 B.CS_INCIDENT_ID sr_incident_id,CSB.INCIDENT_NUMBER sr_number,b.unit_effectivity_id,
2224 (Select unit_config_header_id from AHL_UNIT_CONFIG_HEADERS where name = ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id)) unit_config_header_id,
2225 (select vst.visit_id from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
2226 and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''and rownum < 2) visit_id,
2227 (select name from ahl_fleet_headers_b flt where flt.fleet_header_id = b.fleet_header_id) fleet ,
2228 (SELECT HROU.NAME FROM ahl_fleet_headers_b flt, HR_ALL_ORGANIZATION_UNITS HROU where flt.OPERATING_ORG_ID = hrou.organization_id
2229 and flt.fleet_header_id = b.fleet_header_id and rownum < 2) operatingorg,
2230 (select master_config_name from ahl_unit_header_details_v ucd where ucd.name = ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id)) mc,
2231 (Select position_ref_meaning from ahl_unit_details_v ucd where ucd.csi_item_instance_id = b.csi_item_instance_id and rownum < 2) ucposition,
2232 null eng_org,
2233 null implementstatus ,null progsubtype , null servicecat, null mrServiceType, null mrduration,
2234 null MrDurationUOM , to_char(null) primaryvisittype ,
2235 (select flvt.meaning from ahl_visits_b vst,ahl_visit_tasks_b task,fnd_lookup_values flvt
2236 where vst.visit_id = task.visit_id and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''
2237 and flvt.lookup_type = ''AHL_PLANNING_VISIT_TYPE'' and flvt.lookup_code = vst.visit_type_code and flvt.language = userenv(''LANG'')
2238 and rownum < 2) scheduledvisittype ,
2239 (Select hrou.name from ahl_visits_b vst,ahl_visit_tasks_b task,hr_all_organization_units hrou
2240 where vst.visit_id = task.visit_id and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''
2241 and vst.organization_id = hrou.organization_id and rownum < 2) scheduledVisitOrg
2242 FROM AHL_UNIT_EFFECTIVITIES_B B,
2243 CS_INCIDENT_TYPES_B CITB,
2244 cs_incident_types_tl citt,
2245 CS_INCIDENTS_ALL_B CSB,
2246 CSI_ITEM_INSTANCES CSIB,
2247 mtl_system_items_kfv mtl ';
2248 IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
2249 <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL OR P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
2250 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' ,AHL_APPLICABLE_INSTANCES AAI1 ';
2251 END IF;
2252 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY ||
2253 'where B.CSI_ITEM_INSTANCE_ID = csib.INSTANCE_ID
2254 AND csib.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
2255 AND csib.INV_MASTER_ORGANIZATION_ID = MTL.ORGANIZATION_ID
2256 AND B.CS_INCIDENT_ID = CSB.INCIDENT_ID
2257 and citb.incident_type_id = csb.incident_type_id
2258 and citb.incident_type_id = citt.incident_type_id
2259 and citt.language = userenv(''lang'')
2260 and b.application_usg_code = fnd_profile.value(''AHL_APPLN_USAGE'')
2261 and b.object_type = ''SR''
2262 and b.preceding_ue_id IS NULL
2263 and NVL(B.status_code,''INIT-DUE'' ) = ''INIT-DUE'' ';
2264
2265 IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
2266 <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL OR P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
2267 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' AND B.CSI_ITEM_INSTANCE_ID = AAI1.CSI_ITEM_INSTANCE_ID ';
2268 ELSE
2269 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' AND ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id) IS NOT NULL ';
2270 END IF;
2271 /*IF(P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL) THEN
2272 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.UNIT_NAME;
2273 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id) like (:' ||L_BIND_INDEX ||')';
2274 L_BIND_INDEX := L_BIND_INDEX+1;
2275 END IF;*/
2276 IF(P_SEARCH_UMP_REC.MR_TITLE IS NOT NULL) THEN
2277 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.MR_TITLE;
2278 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and (CITT.NAME || ''-'' || CSB.INCIDENT_NUMBER) like (:' ||L_BIND_INDEX ||')';
2279 L_BIND_INDEX := L_BIND_INDEX+1;
2280 END IF;
2281 /*IF(P_SEARCH_UMP_REC.ITEM IS NOT NULL) THEN
2282 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.ITEM;
2283 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and MTL.CONCATENATED_SEGMENTS like (:' ||L_BIND_INDEX ||')';
2284 L_BIND_INDEX := L_BIND_INDEX+1;
2285 END IF;
2286 IF(P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL) THEN
2287 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER;
2288 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and csib.SERIAL_NUMBER like (:' ||L_BIND_INDEX ||')';
2289 L_BIND_INDEX := L_BIND_INDEX+1;
2290 END IF;*/
2291 IF(P_SEARCH_UMP_REC.FLEET IS NOT NULL) THEN
2292 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.FLEET;
2293 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and exists (select ''x'' from ahl_fleet_headers_b flt where flt.fleet_header_id = b.fleet_header_id and flt.name like (:' ||L_BIND_INDEX ||'))';
2294 L_BIND_INDEX := L_BIND_INDEX+1;
2295 END IF;
2296 IF(P_SEARCH_UMP_REC.MR_SCHEDULED_STATUS IS NOT NULL AND P_SEARCH_UMP_REC.MR_SCHEDULED_STATUS IN ('SCHEDULED','UNSCHEDULED') ) THEN
2297 IF(P_SEARCH_UMP_REC.MR_SCHEDULED_STATUS = 'SCHEDULED')THEN
2298 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and EXISTS (Select ''x'' from ahl_visit_tasks_b vts
2299 where vts.unit_effectivity_id = B.unit_effectivity_id AND vts.status_code <> ''DELETED''and rownum < 2) ';
2300 ELSE
2301 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and NOT EXISTS (Select ''x'' from ahl_visit_tasks_b vts
2302 where vts.unit_effectivity_id = B.unit_effectivity_id and vts.status_code <> ''DELETED''and rownum < 2) ';
2303 END IF;
2304 END IF;
2305 IF(P_SEARCH_UMP_REC.VISIT_NUMBER IS NOT NULL) THEN
2306 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.VISIT_NUMBER;
2307 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task
2308 where vst.visit_id = task.visit_id and task.unit_effectivity_id = B.unit_effectivity_id and vst.visit_number like (:' || L_BIND_INDEX || ') and task.status_code <> ''DELETED''and rownum < 2) ';
2309 L_BIND_INDEX := L_BIND_INDEX+1;
2310 END IF;
2311 IF(P_SEARCH_UMP_REC.SCHEDULED_VISIT_TYPE IS NOT NULL) THEN
2312 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.SCHEDULED_VISIT_TYPE;
2313 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task,fnd_lookup_values flvt
2314 where vst.visit_id = task.visit_id and task.unit_effectivity_id = B.unit_effectivity_id and flvt.lookup_type = ''AHL_PLANNING_VISIT_TYPE''
2315 and flvt.lookup_code = vst.visit_type_code and flvt.language = userenv(''LANG'')
2316 and flvt.meaning like (:' || L_BIND_INDEX || ') and task.status_code <> ''DELETED''and rownum < 2) ';
2317 L_BIND_INDEX := L_BIND_INDEX+1;
2318 END IF;
2319 IF(P_SEARCH_UMP_REC.VISIT_ORG_NAME IS NOT NULL) THEN
2320 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.VISIT_ORG_NAME;
2321 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task ,hr_all_organization_units hrou
2322 where vst.visit_id = task.visit_id and task.unit_effectivity_id = B.unit_effectivity_id and vst.organization_id = hrou.organization_id
2323 and hrou.name like (:' || L_BIND_INDEX || ') and task.status_code <> ''DELETED''and rownum < 2) ';
2324 L_BIND_INDEX := L_BIND_INDEX+1;
2325 END IF;
2326 /* *********************************** BEGIN: SHOW TOLERANCE ****************************************** */
2327 IF P_SEARCH_UMP_REC.DUE_FROM IS NOT NULL THEN
2328 L_DUE_FROM := P_SEARCH_UMP_REC.DUE_FROM;
2329 END IF;
2330 IF P_SEARCH_UMP_REC.DUE_TO IS NOT NULL THEN
2331 L_DUE_TO := TRUNC(P_SEARCH_UMP_REC.DUE_TO) + 1;
2332 END IF;
2333 /* *********************************** BEGIN: SHOW TOLERANCE ****************************************** */
2334 IF ((P_SEARCH_UMP_REC.DUE_FROM IS NOT NULL AND P_SEARCH_UMP_REC.DUE_TO IS NOT NULL) AND (P_SEARCH_UMP_REC.DUE_WITHIN IS NOT NULL))THEN
2335 FND_MESSAGE.SET_NAME('AHL','AHL_UMP_MR_DFROM_DWITHIN_CFLT');
2336 FND_MSG_PUB.ADD;
2337 RAISE FND_API.G_EXC_ERROR;
2338 ELSIF ((P_SEARCH_UMP_REC.DUE_FROM IS NULL OR P_SEARCH_UMP_REC.DUE_TO IS NULL) AND (P_SEARCH_UMP_REC.DUE_WITHIN IS NOT NULL AND (P_SEARCH_UMP_REC.DUE_WITHIN_UOM IS NULL
2339 OR P_SEARCH_UMP_REC.DUE_WITHIN_UOM NOT IN ('HOUR','DAY')) ))THEN
2340 FND_MESSAGE.SET_NAME('AHL','AHL_UMP_MR_DWITHIN_UOM_NLL');
2341 FND_MSG_PUB.ADD;
2342 RAISE FND_API.G_EXC_ERROR;
2343 ELSIF (P_SEARCH_UMP_REC.DUE_WITHIN IS NOT NULL AND P_SEARCH_UMP_REC.DUE_WITHIN_UOM IS NOT NULL)THEN
2344 IF(L_DUE_FROM IS NULL AND L_DUE_TO IS NULL)THEN
2345 L_DUE_FROM := SYSDATE;
2346 END IF;
2347 IF(L_DUE_FROM IS NOT NULL)THEN
2348 IF(P_SEARCH_UMP_REC.DUE_WITHIN_UOM = 'HOUR')THEN
2349 L_DUE_TO := (L_DUE_FROM + P_SEARCH_UMP_REC.DUE_WITHIN/24) + 1;
2350 ELSIF(P_SEARCH_UMP_REC.DUE_WITHIN_UOM = 'DAY')THEN
2351 L_DUE_TO := (L_DUE_FROM + P_SEARCH_UMP_REC.DUE_WITHIN) + 1;
2352 END IF;
2353 ELSIF(L_DUE_TO IS NOT NULL)THEN
2354 IF(P_SEARCH_UMP_REC.DUE_WITHIN_UOM = 'HOUR')THEN
2355 L_DUE_FROM := (L_DUE_TO - P_SEARCH_UMP_REC.DUE_WITHIN/24) -1;
2356 ELSIF(P_SEARCH_UMP_REC.DUE_WITHIN_UOM = 'DAY')THEN
2357 L_DUE_FROM := (L_DUE_TO - P_SEARCH_UMP_REC.DUE_WITHIN) - 1;
2358 END IF;
2359 END IF;
2360 END IF;
2361 -- Show Tolerance is N
2362 IF P_SEARCH_UMP_REC.SHOW_TOLERANCE ='N' THEN
2363 --Due-From Date Check
2364 IF L_DUE_FROM IS NOT NULL THEN
2365 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' AND B.DUE_DATE >= (:' ||L_BIND_INDEX||')';
2366 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2367 L_BIND_INDEX := L_BIND_INDEX + 1;
2368 END IF;
2369 --Due-To Date Check
2370 IF L_DUE_TO IS NOT NULL THEN
2371 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' AND B.DUE_DATE < (:' ||L_BIND_INDEX||')';
2372 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;--TRUNC(p_search_mr_instance_rec.due_to);//bug8265049
2373 L_BIND_INDEX := L_BIND_INDEX + 1;
2374 END IF;
2375 ELSE -- Show Tolerance is Y
2376 IF ( L_DUE_FROM IS NOT NULL AND L_DUE_TO IS NOT NULL ) THEN
2377 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY ||
2378 ' AND ( ((:' ||L_BIND_INDEX||') <= nvl(B.earliest_due_date, B.due_date)
2379 AND nvl(B.earliest_due_date, B.due_date) < (:' ||(L_BIND_INDEX + 1) ||'))
2380 OR (nvl(B.earliest_due_date, B.due_date) <= (:' ||(L_BIND_INDEX + 2) ||')
2381 AND (:' ||(L_BIND_INDEX + 3) ||') < nvl(B.latest_due_date, B.due_date))
2382 OR ((:' ||(L_BIND_INDEX + 4) ||') <= nvl(B.latest_due_date, B.due_date)
2383 AND nvl(B.latest_due_date, B.due_date) < (:' ||(L_BIND_INDEX + 5) ||')) )';
2384 --adivenka changes end
2385 -- due from2 and due to2
2386 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2387 L_BIND_INDEX := L_BIND_INDEX + 1;
2388 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;
2389 L_BIND_INDEX := L_BIND_INDEX + 1;
2390 -- due from3 and due to3
2391 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2392 L_BIND_INDEX := L_BIND_INDEX + 1;
2393 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;
2394 L_BIND_INDEX := L_BIND_INDEX + 1;
2395 -- due from4 and due to4
2396 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2397 L_BIND_INDEX := L_BIND_INDEX + 1;
2398 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;
2399 L_BIND_INDEX := L_BIND_INDEX + 1;
2400 ELSIF ( L_DUE_FROM IS NOT NULL AND L_DUE_TO IS NULL ) THEN
2401 -- Only Due-From Date is there and Due-To Date is NULL
2402 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' AND ( ( ( (:' ||L_BIND_INDEX ||') <= nvl(B.earliest_due_date, B.due_date) )
2403 ) OR ( ( (:' ||(L_BIND_INDEX + 1) ||') <= nvl(B.latest_due_date, B.due_date) ) ) )';
2404 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2405 L_BIND_INDEX := L_BIND_INDEX + 1;
2406 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := TRUNC(L_DUE_FROM);
2407 L_BIND_INDEX := L_BIND_INDEX + 1;
2408 ELSIF ( L_DUE_FROM IS NULL AND L_DUE_TO IS NOT NULL ) THEN
2409 -- Only Due-To Date is there and Due-From Date is NULL
2410 --adivenka modified the following part of the query for bug# 4315128
2411 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' AND ( ( ( nvl(B.earliest_due_date, B.due_date) < (:' ||L_BIND_INDEX ||') )
2412 ) OR( ( nvl(B.latest_due_date, B.due_date) < (:' ||(L_BIND_INDEX + 1) ||') ) ) )';
2413 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;
2414 L_BIND_INDEX := L_BIND_INDEX + 1;
2415 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := L_DUE_TO;
2416 L_BIND_INDEX := L_BIND_INDEX + 1;
2417 END IF;--Case is ignored when Both Due-From and Due-To Dates are NOT here
2418 END IF;
2419 -- operating org
2420 if(p_search_ump_rec.OPERATING_ORG is not null) then
2421 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.OPERATING_ORG;
2422 l_nr_search_query := l_nr_search_query || ' and EXISTS (select ''x'' FROM AHL_FLEET_HEADERS_B FLT, HR_ALL_ORGANIZATION_UNITS HROU where FLT.OPERATING_ORG_ID = HROU.ORGANIZATION_ID
2423 and flt.fleet_header_id = b.fleet_header_id and HROU.NAME like (:' || l_bind_index || '))';
2424 l_bind_index := l_bind_index+1;
2425 end if;
2426 IF(NVL(P_SEARCH_UMP_REC.SHOW_VISIT_DATE_VIOLATIONS,'N') = 'Y') THEN
2427 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
2428 and task.unit_effectivity_id = B.unit_effectivity_id
2429 and task.status_code <> ''DELETED'' and B.due_date IS NOT NULL AND TRUNC(B.DUE_DATE) < TRUNC(vst.START_DATE_TIME) ) ';
2430 END IF;
2431 IF(P_SEARCH_UMP_REC.NR_TYPE IS NOT NULL) THEN
2432 X_UMP_BIND_PARAMS(L_BIND_INDEX).BIND_PARAM := P_SEARCH_UMP_REC.NR_TYPE;
2433 L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and citb.incident_type_id like (:' || L_BIND_INDEX || ')';
2434 L_BIND_INDEX := L_BIND_INDEX+1;
2435 END IF;
2436 END IF; -- NR searched
2437 IF(L_MR_SEARCHED AND L_NR_SEARCHED) THEN
2438 X_UMP_SEARCH_QUERY.UMP_SEARCH_QUERY_STR := L_MR_SEARCH_QUERY || ' UNION ALL ' || L_NR_SEARCH_QUERY;
2439 ELSIF (L_MR_SEARCHED) THEN
2440 X_UMP_SEARCH_QUERY.UMP_SEARCH_QUERY_STR := L_MR_SEARCH_QUERY ;
2441 ELSIF(L_NR_SEARCHED)THEN
2442 X_UMP_SEARCH_QUERY.UMP_SEARCH_QUERY_STR := L_NR_SEARCH_QUERY ;
2443 ELSE
2444 X_UMP_SEARCH_QUERY.UMP_SEARCH_QUERY_STR := L_DUMMY_SEARCH_QUERY ;
2445 END IF;
2446
2447 -- populate dependent instances
2448 DELETE AHL_APPLICABLE_INSTANCES;
2449 IF NVL(P_SEARCH_UMP_REC.SHOW_DEPENDENT_REQUIREMENTS,'N') = 'N' THEN
2450 IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
2451 <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL OR P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
2452 populate_unit_instances
2453 (
2454 P_MODULE_TYPE => P_MODULE_TYPE,
2455 p_search_ump_rec => P_SEARCH_UMP_REC
2456 );
2457
2458 END IF;
2459
2460 ELSIF P_SEARCH_UMP_REC.SHOW_DEPENDENT_REQUIREMENTS = 'Y' THEN
2461 IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
2462 <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL OR P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
2463 populate_unit_instances
2464 (
2465 P_MODULE_TYPE => P_MODULE_TYPE,
2466 p_search_ump_rec => P_SEARCH_UMP_REC
2467 );
2468 populate_dependent_instances
2469 (
2470 P_MODULE_TYPE => P_MODULE_TYPE
2471 );
2472 END IF;
2473 END IF;
2474 -- Standard call to get message count and if count is 1, get message info
2475 FND_MSG_PUB.COUNT_AND_GET (
2476 P_COUNT => X_MSG_COUNT,
2477 P_DATA => X_MSG_DATA,
2478 P_ENCODED => FND_API.G_FALSE );
2479
2480 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
2481 FND_LOG.STRING ( G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_SMRINSTANCE_PVT.Build_Ump_Search_query.end', 'At the end of PLSQL procedure' );
2482 END IF;
2483
2484 EXCEPTION
2485 WHEN FND_API.G_EXC_ERROR THEN
2486 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2487 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT,
2488 P_DATA => X_MSG_DATA,
2489 P_ENCODED => FND_API.G_FALSE);
2490
2491
2492 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2493 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2494 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT,
2495 P_DATA => X_MSG_DATA,
2496 P_ENCODED => FND_API.G_FALSE);
2497
2498 WHEN OTHERS THEN
2499 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2500 FND_MSG_PUB.ADD_EXC_MSG( P_PKG_NAME => G_PKG_NAME,
2501 P_PROCEDURE_NAME => 'Build_Ump_Search_query',
2502 P_ERROR_TEXT => SQLERRM);
2503
2504 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT,
2505 P_DATA => X_MSG_DATA,
2506 P_ENCODED => FND_API.G_FALSE);
2507
2508
2509 END BUILD_UMP_SEARCH_QUERY;
2510
2511 FUNCTION GET_UOM_REMAIN(
2512 P_UNIT_EFFECTIVITY_ID IN NUMBER)
2513 RETURN NUMBER
2514 IS
2515 CURSOR ump_ctr_name_csr (p_counter_id IN NUMBER) IS
2516 SELECT cc.counter_template_name counter_name,
2517 (select ccr.net_reading from csi_counter_readings ccr
2518 where ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
2519 and nvl(ccr.disabled_flag,'N') = 'N')
2520 FROM CSI_COUNTERS_VL CC
2521 WHERE CC.COUNTER_ID = P_COUNTER_ID;
2522
2523 CURSOR get_net_reading_csr (p_csi_item_instance_id IN NUMBER,
2524 P_CTR_TEMPLATE_NAME IN VARCHAR2) IS
2525 SELECT (select ccr.net_reading from csi_counter_readings ccr
2526 where ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
2527 and nvl(ccr.disabled_flag,'N') = 'N') net_reading
2528 FROM CSI_COUNTER_ASSOCIATIONS CCA, CSI_COUNTERS_VL CC
2529 WHERE CCA.COUNTER_ID = CC.COUNTER_ID
2530 AND CCA.SOURCE_OBJECT_ID = p_csi_item_instance_id
2531 AND CCA.SOURCE_OBJECT_CODE = 'CP'
2532 AND CC.COUNTER_TEMPLATE_NAME = p_ctr_template_name;
2533
2534 CURSOR ump_details_csr(p_unit_effectivity_id IN NUMBER) IS
2535 SELECT UMP.due_counter_value,
2536 UMP.counter_name,
2537 UMP.status_code, UMP.originator_title, UMP.csi_item_instance_id,
2538 UMP.COUNTER_ID,
2539 UMP.orig_ue_instance_id
2540 FROM AHL_UNIT_EFFECTIVITIES_V UMP
2541 WHERE UMP.UNIT_EFFECTIVITY_ID = P_UNIT_EFFECTIVITY_ID;
2542 l_net_reading NUMBER;
2543 l_uom_remain NUMBER;
2544 BEGIN
2545 L_UOM_REMAIN := NULL;
2546 FOR ue_details_rec IN ump_details_csr(p_unit_effectivity_id) LOOP
2547 IF (ue_details_rec.status_code IS NULL OR ue_details_rec.status_code = 'INIT-DUE') THEN
2548 IF (ue_details_rec.COUNTER_NAME IS NOT NULL) THEN
2549 -- uom_remain based on interval threshold.
2550 IF (ue_details_rec.ORIGINATOR_TITLE IS NULL) THEN
2551 OPEN get_net_reading_csr ( ue_details_rec.CSI_ITEM_INSTANCE_ID, ue_details_rec.COUNTER_NAME);
2552 ELSE
2553 OPEN get_net_reading_csr ( ue_details_rec.orig_ue_instance_id, ue_details_rec.COUNTER_NAME);
2554 END IF;
2555 FETCH get_net_reading_csr INTO l_net_reading;
2556 IF (get_net_reading_csr%NOTFOUND) OR (l_net_reading IS NULL) THEN
2557 l_net_reading := 0;
2558 END IF;
2559 CLOSE get_net_reading_csr;
2560 l_uom_remain := ue_details_rec.due_counter_value - l_net_reading;
2561 -- UOM remain based on init due threshold counter_id.
2562 ELSIF (ue_details_rec.counter_id IS NOT NULL) THEN
2563 OPEN ump_ctr_name_csr(ue_details_rec.counter_id);
2564 FETCH ump_ctr_name_csr INTO ue_details_rec.COUNTER_NAME, l_net_reading;
2565 IF (ump_ctr_name_csr%NOTFOUND) THEN
2566 l_net_reading := 0;
2567 ELSIF (l_net_reading IS NULL) THEN
2568 l_net_reading := 0;
2569 END IF;
2570 CLOSE ump_ctr_name_csr;
2571 l_uom_remain := ue_details_rec.due_counter_value - l_net_reading;
2572 END IF; -- ue_details_rec.COUNTER_NAME
2573 ELSE
2574 L_UOM_REMAIN := NULL;
2575 END IF; -- ue_details_rec.UMR_STATUS_CODE
2576 END LOOP;
2577 RETURN l_uom_remain;
2578 END;
2579
2580 END AHL_UMP_SMRINSTANCE_PVT;
[Home] [Help]