[Home] [Help]
PACKAGE BODY: APPS.AHL_WORKORDER_SEARCH_PUB
Source
1 PACKAGE BODY AHL_WORKORDER_SEARCH_PUB AS
2 /* $Header: AHLPWSOB.pls 120.0.12010000.2 2008/11/30 22:00:58 sikumar noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_WORKORDER_SEARCH_PUB';
5
6 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
7 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_WORKORDER_SEARCH_PUB';
8
9 G_BPEL_USER_ROLE_KEY VARCHAR2(240) := 'AHL_PRD_TECH_RESP';--FND_PROFILE.VALUE('AHL_BPEL_USER_ROLE');
10
11 FUNCTION init_user_and_role(p_user_id IN VARCHAR2) RETURN VARCHAR2 IS
12
13 CURSOR get_user_id_csr(p_user_id IN VARCHAR2) IS
14 select user_id from fnd_user where user_name = p_user_id;
15
16 l_user_id NUMBER;
17 l_resp_id NUMBER;
18
19 CURSOR get_resp_id_csr IS
20 select responsibility_id from fnd_responsibility_vl where responsibility_key = G_BPEL_USER_ROLE_KEY;
21 BEGIN
22 OPEN get_user_id_csr(p_user_id);
23 FETCH get_user_id_csr INTO l_user_id;
24 IF get_user_id_csr%NOTFOUND THEN
25 FND_MESSAGE.set_name('AHL','AHL_PRD_INV_BPEL_USR');
26 FND_MESSAGE.SET_TOKEN('USER_NAME',p_user_id);
27 FND_MSG_PUB.ADD;
28 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
29 END IF;
30 CLOSE get_user_id_csr;
31
32 OPEN get_resp_id_csr;
33 FETCH get_resp_id_csr INTO l_resp_id;
34 CLOSE get_resp_id_csr;
35
36 FND_GLOBAL.apps_initialize(l_user_id,l_resp_id,867);
37 mo_global.init('AHL');
38 return Fnd_Api.G_RET_STS_SUCCESS;
39
40 END init_user_and_role;
41
42 -- Assigned Work Order Query---------------------------
43
44 PROCEDURE get_wo_search_results(
45 p_api_version IN NUMBER,
46 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
47 p_commit IN VARCHAR2 := FND_API.G_FALSE,
48 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
49 p_module_type IN VARCHAR2,
50 p_userid IN VARCHAR2 := NULL,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2,
54 p_workorders_search_rec IN WORKORDERS_SEARCH_REC_TYPE,
55 x_work_order_results OUT NOCOPY WORK_ORDERS_TYPE
56 )
57 IS
58 l_api_version CONSTANT NUMBER := 1.0;
59 l_api_name CONSTANT VARCHAR2(30) := 'get_workorder_search_results';
60 l_msg_data VARCHAR2(2000);
61 l_model VARCHAR2(30) := 'Model' ;
62 l_ata_code VARCHAR2(30) := 'ATA';
63 l_tail_number VARCHAR2(30);
64 l_user_name VARCHAR2(40);
65 l_user_lang VARCHAR2(40);
66 l_doc_id VARCHAR2(80) :='docid';
67 l_query_string VARCHAR2(30000) := NULL;
68
69 l_bind_index NUMBER;
70 i NUMBER;
71 j NUMBER;
72 l_search_wo_csr AHL_OSP_UTIL_PKG.ahl_search_csr;
73 l_bind_value_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
74 l_work_order_rec WORK_ORDER_REC_TYPE;
75
76 BEGIN
77 SAVEPOINT Search_Work_Orders;
78
79 --FND_GLOBAL.apps_initialize(1003259,62211,867);
80 --mo_global.init('AHL');
81
82 -- Initialize message list if p_init_msg_list is set to TRUE.
83 IF Fnd_Api.to_boolean(p_init_msg_list)
84 THEN
85 Fnd_Msg_Pub.initialize;
86 END IF;
87
88 x_return_status := init_user_and_role(p_userid);
89 -- Initialize API return status to success
90 --x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
91 x_work_order_results.WORK_ORDERS(0).WORKORDER_ID := NULL;
92
93 -- Standard call to check for call compatibility.
94 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
95 p_api_version,
96 l_api_name,'AHL_WORKORDER_SEARCH_PUB')
97 THEN
98 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
99 END IF;
100
101 IF (p_workorders_search_rec.SEARCH_TABLE_INDEX = 1) THEN
102
103 l_query_string := 'SELECT DISTINCT '||
104 'wo.workorder_id workorder_id, '||
105 'wo.object_version_number object_version_number, '||
106 'Wo.Job_Number workorder_number, '||
107 'wo.Job_Description Description, '||
108 'wo.job_status_code status_code, '||
109 'Wo.Job_Status_Meaning status, '||
110 'Wo.Visit_Number visit_number, '||
111 'Wo.Unit_Name unit_name, '||
112 'Wo.Scheduled_Start_Date assigned_start_date, '||
113 'Wo.Confirm_Failure_flag is_complete_enabled, '||
114 'Wo.Confirm_Failure_flag is_update_enabled, '||
115 'Wo.Confirm_Failure_flag is_res_txn_enabled '||
116 'FROM '||
117 'ahl_Search_Workorders_V Wo , '||
118 'wip_discrete_jobs widj, '||
119 'ahl_Workorder_Operations Wop, '||
120 'ahl_Operation_Resources Opr, '||
121 'ahl_Work_Assignments Wass, '||
122 'per_People_F Pf, '||
123 'per_Person_Types Pt, '||
124 'Fnd_User fnd '||
125 'WHERE '||
126 'fnd.USER_ID = FND_GLOBAL.USER_ID and '||
127 'fnd.employee_id = pf.PERSON_ID and '||
128 'wass.employee_id = pf.PERSON_ID and '||
129 ' pt.Person_Type_Id = Pf.Person_Type_Id And '||
130 'pt.system_person_type =''EMP'' AND '||
131 '( Trunc(Sysdate) Between Pf.Effective_Start_Date And '||
132 'Pf.Effective_End_Date) and '||
133 'wass.Operation_Resource_Id = Opr.Operation_Resource_Id And '||
134 'opr.Workorder_Operation_Id = Wop.Workorder_Operation_Id And '||
135 'wop.Workorder_Id = Wo.Workorder_Id and '||
136 'wo.JOB_STATUS_CODE not in (1, 17, 22) and '||
137 'wo.wip_entity_id = widj.wip_entity_id and '||
138 'widj.date_released is not null';
139
140 ELSIF (p_workorders_search_rec.SEARCH_TABLE_INDEX = 2) THEN
141
142 l_query_string := 'SELECT '||
143 'wo.workorder_id workorder_id, '||
144 'wo.object_version_number object_version_number, '||
145 'Wo.Job_Number workorder_number, '||
146 'wo.Job_Description Description, '||
147 'wo.job_status_code status_code, '||
148 'Wo.Job_Status_Meaning status, '||
149 'Wo.Visit_Number visit_number, '||
150 'Wo.Unit_Name unit_name, '||
151 'Wo.Scheduled_Start_Date assigned_start_date, '||
152 'Wo.Confirm_Failure_flag is_complete_enabled, '||
153 'Wo.Confirm_Failure_flag is_update_enabled, '||
154 'Wo.Confirm_Failure_flag is_res_txn_enabled '||
155 'FROM( '||
156 'SELECT DISTINCT '||
157 'wod.workorder_id , '||
158 'Wod.Job_Number , '||
159 'wod.job_status_code , '||
160 'Wod.Job_Status_Meaning , '||
161 'Wod.Visit_Number , '||
162 'wod.VISIT_ID , '||
163 'Wod.Incident_Number , '||
164 'wod.INCIDENT_ID , '||
165 'nvl(wod.ACtual_START_DATE, wod.SCHEDULED_START_DATE) scheduled_start_date, '||
166 'wod.JOB_DESCRIPTION , '||
167 'wod.class_code class_code, '||
168 'wod.Visit_Task_number, '||
169 'wod.project_name , '||
170 'wod.Project_Task_name , '||
171 'wod.Mr_Title, '||
172 'wod.wo_part_number , '||
173 'wod.Serial_Number, '||
174 ' wod.Organization_name , '||
175 'wod.department_name , '||
176 'wod.UNIT_NAME, '||
177 'NVL(WOd.ACTUAL_END_DATE, WOd.SCHEDULED_END_DATE) scheduled_end_date, '||
178 ' wod.department_class_code, '||
179 'wod.visit_task_id, '||
180 'wod.object_version_number, '||
181 'wod.Confirm_Failure_flag '||
182 'FROM '||
183 ' ahl_search_workorders_v wod, '||
184 'wip_discrete_jobs widj, '||
185 'per_people_f pf, '||
186 'bom_resource_employees bre, '||
187 'PER_PERSON_TYPES PEPT, '||
188 'ahl_pp_requirement_v aprv, '||
189 'Fnd_User fnd '||
190 'WHERE '||
191 'fnd.USER_ID = FND_GLOBAL.USER_ID and '||
192 'fnd.employee_id = pf.PERSON_ID and '||
193 ' NVL(pf.CURRENT_EMPLOYEE_FLAG, ''X'') = ''Y'' AND '||
194 'PEPT.PERSON_TYPE_ID = pf.PERSON_TYPE_ID AND '||
195 'PEPT.SYSTEM_PERSON_TYPE =''EMP'' AND '||
196 '( TRUNC(SYSDATE) BETWEEN PF.EFFECTIVE_START_DATE AND PF.EFFECTIVE_END_DATE ) and '||
197 'pf.person_id = bre.person_id and '||
198 'bre.resource_id = aprv.RESOURCE_ID and '||
199 'wod.workorder_id not in ( '||
200 'SELECT DISTINCT '||
201 'wo1.workorder_id '||
202 'FROM '||
203 'ahl_Workorders Wo1, '||
204 'wip_discrete_jobs widj1, '||
205 'ahl_Workorder_Operations Wop, '||
206 'ahl_Operation_Resources Opr, '||
207 'ahl_Work_Assignments Wass, '||
208 'Fnd_User fnd '||
209 'WHERE '||
210 'fnd.USER_ID = FND_GLOBAL.USER_ID and '||
211 'fnd.employee_id = pf.PERSON_ID and '||
212 'NVL(pf.CURRENT_EMPLOYEE_FLAG, ''X'') = ''Y'' AND '||
213 'PEPT.PERSON_TYPE_ID = pf.PERSON_TYPE_ID AND '||
214 'PEPT.SYSTEM_PERSON_TYPE =''EMP'' AND '||
215 '( TRUNC(SYSDATE) BETWEEN PF.EFFECTIVE_START_DATE AND PF.EFFECTIVE_END_DATE ) and '||
216 'wass.Employee_Id = pf.person_id And '||
217 'wass.Operation_Resource_Id = Opr.Operation_Resource_Id And '||
218 'opr.Workorder_Operation_Id = Wop.Workorder_Operation_Id And '||
219 'wop.Workorder_Id = Wo1.Workorder_Id and '||
220 'wo1.STATUS_CODE not in (1, 17, 22) and '||
221 'wo1.wip_entity_id = widj1.wip_entity_id and '||
222 'widj1.date_released is not null '||
223 ' ) and '||
224 'aprv.job_id = wod.workorder_id and '||
225 'wod.JOB_STATUS_CODE not in (1, 17, 22) and '||
226 'wod.wip_entity_id = widj.wip_entity_id and '||
227 'widj.date_released is not null '||
228
229 ' UNION '||
230
231 'SELECT DISTINCT '||
232 'wod.workorder_id , '||
233 'Wod.Job_Number , '||
234 'wod.job_status_code , '||
235 'Wod.Job_Status_Meaning , '||
236 'Wod.Visit_Number , '||
237 'wod.VISIT_ID , '||
238 'Wod.Incident_Number , '||
239 'wod.INCIDENT_ID , '||
240 'nvl(wod.ACtual_START_DATE, wod.SCHEDULED_START_DATE) scheduled_start_date, '||
241 'wod.JOB_DESCRIPTION , '||
242 'wod.class_code class_code, '||
243 'wod.Visit_Task_number, '||
244 'wod.project_name , '||
245 'wod.Project_Task_name , '||
246 'wod.Mr_Title, '||
247 'wod.wo_part_number , '||
248 'wod.Serial_Number, '||
249 ' wod.Organization_name , '||
250 'wod.department_name , '||
251 'wod.UNIT_NAME, '||
252 'NVL(WOd.ACTUAL_END_DATE, WOd.SCHEDULED_END_DATE) scheduled_end_date, '||
253 ' wod.department_class_code, '||
254 'wod.visit_task_id, '||
255 'wod.object_version_number, '||
256 'wod.Confirm_Failure_flag '||
257 ' FROM '||
258 'ahl_search_workorders_v wod, '||
259 'wip_discrete_jobs widj '||
260 ' WHERE '||
261 'wod.workorder_id not in (select job_id from ahl_pp_requirement_v where resource_type_code = 2) and wod.JOB_STATUS_CODE not in (1, 17, 22) and '||
262 'wod.wip_entity_id = widj.wip_entity_id and '||
263 'widj.date_released is not null) wo where 1=1';
264
265 ELSE
266 RAISE FND_API.G_EXC_ERROR;
267 END IF;
268
269 l_bind_index :=1;
270
271 IF p_workorders_search_rec.workorder_number IS NOT NULL THEN
272 l_query_string := l_query_string || ' AND wo.job_number like :'||l_bind_index;
273 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.workorder_number;
274 l_bind_index := l_bind_index + 1;
275 END IF;
276 IF p_workorders_search_rec.status_code IS NOT NULL THEN
277 l_query_string := l_query_string || ' AND wo.job_status_code = :'||l_bind_index;
278 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.status_code;
279 l_bind_index := l_bind_index + 1;
280 END IF;
281 IF p_workorders_search_rec.status IS NOT NULL THEN
282 l_query_string := l_query_string || ' AND upper(wo.Job_Status_Meaning) like upper(:'||l_bind_index ||')';
283 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.status;
284 l_bind_index := l_bind_index + 1;
285 END IF;
286 IF p_workorders_search_rec.description IS NOT NULL THEN
287 l_query_string := l_query_string || ' AND wo.job_description like :'||l_bind_index;
288 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.description;
289 l_bind_index := l_bind_index + 1;
290 END IF;
291 IF p_workorders_search_rec.non_routine_number IS NOT NULL THEN
292 l_query_string := l_query_string || ' AND Wo.Incident_Number like :'||l_bind_index;
293 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.non_routine_number;
294 l_bind_index := l_bind_index + 1;
295 END IF;
296 IF p_workorders_search_rec.visit_number IS NOT NULL THEN
297 l_query_string := l_query_string || ' AND wo.visit_number = :'||l_bind_index;
298 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.visit_number;
299 l_bind_index := l_bind_index + 1;
300 END IF;
301 IF p_workorders_search_rec.project IS NOT NULL THEN
302 l_query_string := l_query_string || ' AND wo.project_name = :'||l_bind_index;
303 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.project;
304 l_bind_index := l_bind_index + 1;
305 END IF;
306 IF p_workorders_search_rec.project_task IS NOT NULL THEN
307 l_query_string := l_query_string || ' AND wo.project_task_name = :'||l_bind_index;
308 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.project_task;
309 l_bind_index := l_bind_index + 1;
310 END IF;
311 IF p_workorders_search_rec.item IS NOT NULL THEN
312 l_query_string := l_query_string || ' AND wo.wo_part_number = :'||l_bind_index;
313 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.item;
314 l_bind_index := l_bind_index + 1;
315 END IF;
316 IF p_workorders_search_rec.unit_name IS NOT NULL THEN
317 l_query_string := l_query_string || ' AND wo.unit_name = :'||l_bind_index;
318 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.unit_name;
319 l_bind_index := l_bind_index + 1;
320 END IF;
321 IF p_workorders_search_rec.department IS NOT NULL THEN
322 l_query_string := l_query_string || ' AND wo.department_name = :'||l_bind_index;
323 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.department;
324 l_bind_index := l_bind_index + 1;
325 END IF;
326 IF p_workorders_search_rec.organization IS NOT NULL THEN
327 l_query_string := l_query_string || ' AND wo.organization_name = :'||l_bind_index;
328 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.organization;
329 l_bind_index := l_bind_index + 1;
330 END IF;
331 IF p_workorders_search_rec.department_class_code IS NOT NULL THEN
332 l_query_string := l_query_string || ' AND wo.department_class_code = :'||l_bind_index;
333 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.department_class_code;
334 l_bind_index := l_bind_index + 1;
335 END IF;
336 IF p_workorders_search_rec.maintenance_requirement_title IS NOT NULL THEN
337 l_query_string := l_query_string || ' AND wo.mr_title = :'||l_bind_index;
338 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.maintenance_requirement_title;
339 l_bind_index := l_bind_index + 1;
340 END IF;
341 IF p_workorders_search_rec.accounting_class IS NOT NULL THEN
342 l_query_string := l_query_string || ' AND wo.CLASS_CODE = :'||l_bind_index;
343 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.accounting_class;
344 l_bind_index := l_bind_index + 1;
345 END IF;
346 IF p_workorders_search_rec.confirmed_failure_flag IS NOT NULL THEN
347 l_query_string := l_query_string || ' AND wo.CONFIRMED_FAILURE_FLAG = :'||l_bind_index;
348 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.confirmed_failure_flag;
349 l_bind_index := l_bind_index + 1;
350 END IF;
351 IF p_workorders_search_rec.priority IS NOT NULL THEN
352 l_query_string := l_query_string || ' AND wo.PRIORITY = :'||l_bind_index;
353 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.priority;
354 l_bind_index := l_bind_index + 1;
355 END IF;
356 IF p_workorders_search_rec.visit_task_number IS NOT NULL THEN
357 l_query_string := l_query_string || ' AND wo.VISIT_TASK_NUMBER = :'||l_bind_index;
358 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.visit_task_number;
359 l_bind_index := l_bind_index + 1;
360 END IF;
361 IF p_workorders_search_rec.employee IS NOT NULL THEN
362 l_query_string := l_query_string || 'AND wo.WORKORDER_ID in (select wop.workorder_id from per_people_f pf, ahl_work_assignments wass, ahl_operation_resources opr, ahl_workorder_operations wop where ';
363 l_query_string := l_query_string || ' wop.workorder_operation_id = opr.workorder_operation_id and opr.operation_resource_id = wass.operation_resource_id ';
364 l_query_string := l_query_string || ' and wass.employee_id = pf.person_id and upper(pf.full_name) like upper(:' ||l_bind_index ||'))';
365 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.employee;
366 l_bind_index := l_bind_index + 1;
367 END IF;
368 IF p_workorders_search_rec.workorder_type IS NOT NULL THEN
369 l_query_string := l_query_string || ' AND wo.WO_TYPE_CODE like :'||l_bind_index ||')';
370 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.workorder_type;
371 l_bind_index := l_bind_index + 1;
372 END IF;
373 l_query_string := l_query_string || ' AND exists ( SELECT ''x'' FROM AHL_WORKORDER_OPERATIONS_V WOP where WOP.workorder_id = WO.workorder_id';
374 IF p_workorders_search_rec.OPERATION_CODE IS NOT NULL THEN
375 l_query_string := l_query_string || ' AND upper(WOP.OPERATION_CODE) like upper(:' ||l_bind_index ||')';
376 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.OPERATION_CODE;
377 l_bind_index := l_bind_index + 1;
378 END IF;
379 l_query_string := l_query_string || ')';
380 IF p_workorders_search_rec.operation_description IS NOT NULL THEN
381 l_query_string := l_query_string || ' AND upper(WOP.DESCRIPTION) like upper(:' ||l_bind_index ||')';
382 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.operation_description;
383 l_bind_index := l_bind_index + 1;
384 END IF;
385 IF p_workorders_search_rec.bom_resource IS NOT NULL THEN
386 l_query_string := l_query_string || ' AND exists ( SELECT ''x'' FROM AHL_PP_REQUIREMENT_V RES where RES.job_id = WO.workorder_id and upper(RES.RESOURCE_CODE) like upper(:' ||l_bind_index ||'))';
387 l_bind_value_tbl(l_bind_index) := p_workorders_search_rec.bom_resource;
388 l_bind_index := l_bind_index + 1;
389 END IF;
390 IF p_workorders_search_rec.SCHEDULED_START_DATE IS NOT NULL AND p_workorders_search_rec.SCHEDULED_END_DATE IS NOT NULL THEN
391 l_query_string := l_query_string || ' AND ((wo.SCHEDULED_START_DATE >= to_date(:'||l_bind_index||',''DD-MON-RRRR HH24:MI:SS'')';
392 l_bind_value_tbl(l_bind_index) := to_char(p_workorders_search_rec.scheduled_start_date, 'DD-MON-RRRR HH24:MI:SS');
393 l_bind_index := l_bind_index + 1;
394 l_query_string := l_query_string || ' AND wo.SCHEDULED_START_DATE <= to_date(:'||l_bind_index||',''DD-MON-RRRR HH24:MI:SS''))';
395 l_bind_value_tbl(l_bind_index) := to_char(p_workorders_search_rec.scheduled_end_date, 'DD-MON-RRRR HH24:MI:SS');
396 l_bind_index := l_bind_index + 1;
397 l_query_string := l_query_string || ' OR ( wo.SCHEDULED_END_DATE >= to_date(:'||l_bind_index||',''DD-MON-RRRR HH24:MI:SS'')';
398 l_bind_value_tbl(l_bind_index) := to_char(p_workorders_search_rec.scheduled_start_date, 'DD-MON-RRRR HH24:MI:SS');
399 l_bind_index := l_bind_index + 1;
400 l_query_string := l_query_string || ' AND wo.SCHEDULED_END_DATE <= to_date(:'||l_bind_index||',''DD-MON-RRRR HH24:MI:SS''))';
401 l_bind_value_tbl(l_bind_index) := to_char(p_workorders_search_rec.scheduled_end_date, 'DD-MON-RRRR HH24:MI:SS');
402 l_bind_index := l_bind_index + 1;
403 l_query_string := l_query_string || ' OR ( wo.SCHEDULED_START_DATE <= to_date(:'||l_bind_index||',''DD-MON-RRRR HH24:MI:SS'')';
404 l_bind_value_tbl(l_bind_index) := to_char(p_workorders_search_rec.scheduled_start_date, 'DD-MON-RRRR HH24:MI:SS');
405 l_bind_index := l_bind_index + 1;
406 l_query_string := l_query_string || ' AND wo.SCHEDULED_END_DATE >= to_date(:'||l_bind_index||',''DD-MON-RRRR HH24:MI:SS'')' || '))';
407 l_bind_value_tbl(l_bind_index) := to_char(p_workorders_search_rec.scheduled_end_date, 'DD-MON-RRRR HH24:MI:SS');
408 l_bind_index := l_bind_index + 1;
409 ELSIF p_workorders_search_rec.SCHEDULED_START_DATE IS NOT NULL THEN
410 l_query_string := l_query_string || ' AND wo.SCHEDULED_END_DATE >= to_date(:'||l_bind_index||',''DD-MON-RRRR HH24:MI:SS'')';
411 l_bind_value_tbl(l_bind_index) := to_char(p_workorders_search_rec.scheduled_start_date, 'DD-MON-RRRR HH24:MI:SS');
412 l_bind_index := l_bind_index + 1;
413 ELSIF p_workorders_search_rec.SCHEDULED_END_DATE IS NOT NULL THEN
414 l_query_string := l_query_string || ' AND wo.SCHEDULED_START_DATE <= to_date(:'||l_bind_index||',''DD-MON-RRRR HH24:MI:SS'')';
415 l_bind_value_tbl(l_bind_index) := to_char(p_workorders_search_rec.scheduled_end_date, 'DD-MON-RRRR HH24:MI:SS');
416 l_bind_index := l_bind_index + 1;
417 END IF;
418
419 --OPEN l_search_wo_csr FOR l_query_string
420 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR(l_search_wo_csr, l_bind_value_tbl, l_query_string);
421 i := 0;
422 j := 0;
423 LOOP
424 --Get search results
425 FETCH l_search_wo_csr INTO l_work_order_rec.WORKORDER_ID,
426 l_work_order_rec.OBJECT_VERSION_NUMBER,
427 l_work_order_rec.WORKORDER_NUMBER,
428 l_work_order_rec.DESCRIPTION,
429 l_work_order_rec.STATUS_CODE,
430 l_work_order_rec.STATUS,
431 l_work_order_rec.VISIT_NUMBER,
432 l_work_order_rec.UNIT_NAME,
433 l_work_order_rec.ASSIGNED_START_DATE,
434 l_work_order_rec.IS_COMPLETE_ENABLED,
435 l_work_order_rec.IS_UPDATE_ENABLED,
436 l_work_order_rec.IS_RES_TXN_ENABLED;
437
438 EXIT WHEN l_search_wo_csr%NOTFOUND;
439 i := i + 1;
440 AHL_ENIGMA_UTIL_PKG.get_enigma_url_params('WO',
441 l_work_order_rec.WORKORDER_ID,
442 l_work_order_rec.WORKORDER_ID,
443 x_model => l_model,
444 x_ata_code => l_ata_code,
445 x_tail_number => l_tail_number,
446 x_user_name => l_user_name,
447 x_user_lang => l_user_lang,
448 x_doc_id => l_doc_id
449 );
450
451 IF (i > NVL(p_workorders_search_rec.start_row_index,0) AND i <= NVL(p_workorders_search_rec.start_row_index + p_workorders_search_rec.number_of_rows,1000)) THEN
452 x_work_order_results.WORK_ORDERS(j).WORKORDER_ID := l_work_order_rec.WORKORDER_ID;
453 x_work_order_results.WORK_ORDERS(j).OBJECT_VERSION_NUMBER := l_work_order_rec.OBJECT_VERSION_NUMBER;
454 x_work_order_results.WORK_ORDERS(j).WORKORDER_NUMBER := l_work_order_rec.WORKORDER_NUMBER;
455 x_work_order_results.WORK_ORDERS(j).DESCRIPTION := l_work_order_rec.DESCRIPTION;
456 x_work_order_results.WORK_ORDERS(j).STATUS_CODE := l_work_order_rec.STATUS_CODE;
457 x_work_order_results.WORK_ORDERS(j).STATUS := l_work_order_rec.STATUS;
458 x_work_order_results.WORK_ORDERS(j).VISIT_NUMBER := l_work_order_rec.VISIT_NUMBER;
459 x_work_order_results.WORK_ORDERS(j).UNIT_NAME := l_work_order_rec.UNIT_NAME;
460 x_work_order_results.WORK_ORDERS(j).MODEL := l_model;
461 x_work_order_results.WORK_ORDERS(j).ATA_CODE := l_ata_code;
462 x_work_order_results.WORK_ORDERS(j).ENIGMA_DOCUMENT_ID := l_doc_id;
463 x_work_order_results.WORK_ORDERS(j).ASSIGNED_START_DATE := l_work_order_rec.ASSIGNED_START_DATE;
464
465 x_work_order_results.WORK_ORDERS(j).IS_COMPLETE_ENABLED := AHL_COMPLETIONS_PVT.Is_Complete_Enabled(l_work_order_rec.WORKORDER_ID, NULL, NULL, 'T');
466 IF(l_work_order_rec.STATUS_CODE IN ('22','7','12','1','7') OR AHL_PRD_UTIL_PKG.is_wo_updatable(l_work_order_rec.WORKORDER_ID,'T') = 'F')THEN
467
468 x_work_order_results.WORK_ORDERS(j).IS_UPDATE_ENABLED := 'F';
469 ELSE
470 x_work_order_results.WORK_ORDERS(j).IS_UPDATE_ENABLED := 'T';
471 END IF;
472
473 x_work_order_results.WORK_ORDERS(j).IS_RES_TXN_ENABLED := AHL_PRD_UTIL_PKG.Is_ResTxn_Allowed(l_work_order_rec.WORKORDER_ID,'T');
474 j := j + 1;
475 END IF;
476 END LOOP;
477 CLOSE l_search_wo_csr;
478 x_work_order_results.start_row_index := NVL(p_workorders_search_rec.start_row_index,0);
479 x_work_order_results.NUMBER_OF_ROWS := j;
480
481 EXCEPTION
482 WHEN Fnd_Api.G_EXC_ERROR THEN
483 ROLLBACK TO Search_Work_Orders;
484 x_return_status := Fnd_Api.G_RET_STS_ERROR;
485 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
486 p_data => x_msg_data,
487 p_encoded => Fnd_Api.g_false);
488
489 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
490 ROLLBACK TO Search_Work_Orders;
491 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
492 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
493 p_data => x_msg_data,
494 p_encoded => Fnd_Api.g_false);
495
496 WHEN OTHERS THEN
497 -- dbms_output.put_line(' OTHERS ');
498 ROLLBACK TO Search_Work_Orders;
499 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
500 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => 'AHL_WORKORDER_SEARCH_PUB',
501 p_procedure_name => 'Get_Workorder_Search_Results',
502 p_error_text => SQLERRM);
503
504 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
505 p_data => x_msg_data,
506 p_encoded => Fnd_Api.g_false);
507
508 END get_wo_search_results;
509
510 END AHL_WORKORDER_SEARCH_PUB;