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