[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_LOV_SERVICE_PVT
Source
1 PACKAGE BODY AHL_PRD_LOV_SERVICE_PVT AS
2 /* $Header: AHLVLOVB.pls 120.0.12010000.3 2008/11/30 22:06:54 sikumar noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_PRD_LOV_SERVICE_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6
7
8 ------------------------------------
9 -- Common constants and variables --
10 ------------------------------------
11 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
12 l_log_statement NUMBER := fnd_log.level_statement;
13 l_log_procedure NUMBER := fnd_log.level_procedure;
14 l_log_error NUMBER := fnd_log.level_error;
15 l_log_unexpected NUMBER := fnd_log.level_unexpected;
16
17 G_BPEL_USER_ROLE_KEY VARCHAR2(240) := FND_PROFILE.VALUE('AHL_BPEL_USER_ROLE');
18
19 FUNCTION init_user_and_role(p_user_id IN VARCHAR2) RETURN VARCHAR2 IS
20
21 CURSOR get_user_id_csr(p_user_id IN VARCHAR2) IS
22 select user_id from fnd_user where user_name = p_user_id;
23
24 l_user_id NUMBER;
25 l_resp_id NUMBER;
26
27 CURSOR get_resp_id_csr IS
28 select responsibility_id from fnd_responsibility_vl where responsibility_key = G_BPEL_USER_ROLE_KEY;
29
30 BEGIN
31 OPEN get_user_id_csr(p_user_id);
32 FETCH get_user_id_csr INTO l_user_id;
33 IF get_user_id_csr%NOTFOUND THEN
34 FND_MESSAGE.set_name('AHL','AHL_PRD_INV_BPEL_USR');
35 FND_MESSAGE.SET_TOKEN('USER_NAME',p_user_id);
36 FND_MSG_PUB.ADD;
37 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
38 END IF;
39 CLOSE get_user_id_csr;
40
41 OPEN get_resp_id_csr;
42 FETCH get_resp_id_csr INTO l_resp_id;
43 CLOSE get_resp_id_csr;
44
45 FND_GLOBAL.apps_initialize(l_user_id,l_resp_id,867);
46 mo_global.init('AHL');
47 return Fnd_Api.G_RET_STS_SUCCESS;
48
49 END init_user_and_role;
50
51 ---------------------------------------------------------------------
52 -- PROCEDURE
53 -- getVisitNumberMetaData
54 --
55 -- PURPOSE
56 --
57 -- PARAMETERS
58 --
59 -- NOTES
60 ---------------------------------------------------------------------
61 PROCEDURE getVisitNumberMetaData(
62 p_lov_input_rec IN LOV_Input_Rec_Type,
63 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_msg_count OUT NOCOPY NUMBER,
66 x_msg_data OUT NOCOPY VARCHAR2)
67 IS
68
69 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
70 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
71
72 BEGIN
73
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75
76 -- Create Attributes Table
77 -- Visit ID
78 l_Meta_Attribute_Rec.AttributeName := 'VisitId';
79 l_Meta_Attribute_Rec.Prompt := null;
80 l_Meta_Attribute_Rec.IsDisplayed := 'F';
81 l_Meta_Attribute_Rec.IsSearcheable := 'F';
82 l_Meta_Attribute_Rec.DataType := 'integer';
83
84 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
85
86 -- Visit Numbeer
87 l_Meta_Attribute_Rec.AttributeName := 'VisitNumber';
88 l_Meta_Attribute_Rec.Prompt := 'Visit Number';
89 l_Meta_Attribute_Rec.IsDisplayed := 'T';
90 l_Meta_Attribute_Rec.IsSearcheable := 'T';
91 l_Meta_Attribute_Rec.DataType := 'string';
92
93 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
94
95 -- Visit Type
96 l_Meta_Attribute_Rec.AttributeName := 'VisitTypeMean';
97 l_Meta_Attribute_Rec.Prompt := 'Visit Type';
98 l_Meta_Attribute_Rec.IsDisplayed := 'T';
99 l_Meta_Attribute_Rec.IsSearcheable := 'F';
100 l_Meta_Attribute_Rec.DataType := 'string';
101
102 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
103
104 -- Item Description
105 l_Meta_Attribute_Rec.AttributeName := 'ItemDescription';
106 l_Meta_Attribute_Rec.Prompt := 'Item Description';
107 l_Meta_Attribute_Rec.IsDisplayed := 'T';
108 l_Meta_Attribute_Rec.IsSearcheable := 'F';
109 l_Meta_Attribute_Rec.DataType := 'string';
110
111 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
112
113 -- Serial Number
114 l_Meta_Attribute_Rec.AttributeName := 'SerialNumber';
115 l_Meta_Attribute_Rec.Prompt := 'Serial Number';
116 l_Meta_Attribute_Rec.IsDisplayed := 'T';
117 l_Meta_Attribute_Rec.IsSearcheable := 'F';
118 l_Meta_Attribute_Rec.DataType := 'string';
119
120 l_Meta_Attribute_Tbl(4) := l_Meta_Attribute_Rec;
121
122 -- Unit Name
123 l_Meta_Attribute_Rec.AttributeName := 'UnitName';
124 l_Meta_Attribute_Rec.Prompt := 'Unit';
125 l_Meta_Attribute_Rec.IsDisplayed := 'T';
126 l_Meta_Attribute_Rec.IsSearcheable := 'F';
127 l_Meta_Attribute_Rec.DataType := 'string';
128
129 l_Meta_Attribute_Tbl(5) := l_Meta_Attribute_Rec;
130
131 -- Organization Name
132 l_Meta_Attribute_Rec.AttributeName := 'OrganizationName';
133 l_Meta_Attribute_Rec.Prompt := 'Organization';
134 l_Meta_Attribute_Rec.IsDisplayed := 'T';
135 l_Meta_Attribute_Rec.IsSearcheable := 'F';
136 l_Meta_Attribute_Rec.DataType := 'string';
137
138 l_Meta_Attribute_Tbl(6) := l_Meta_Attribute_Rec;
139
140 -- Department Name
141 l_Meta_Attribute_Rec.AttributeName := 'DepartmentName';
142 l_Meta_Attribute_Rec.Prompt := 'Department';
143 l_Meta_Attribute_Rec.IsDisplayed := 'T';
144 l_Meta_Attribute_Rec.IsSearcheable := 'F';
145 l_Meta_Attribute_Rec.DataType := 'string';
146
147 l_Meta_Attribute_Tbl(7) := l_Meta_Attribute_Rec;
148
149 -- Populate output parameter
150 x_lov_meta_output_rec.LovTitle := 'Search Visits';
151 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
152
153 END getVisitNumberMetaData;
154
155 ---------------------------------------------------------------------
156 -- PROCEDURE
157 -- getVisitNumberResults
158 --
159 -- PURPOSE
160 --
161 -- PARAMETERS
162 --
163 -- NOTES
164 ---------------------------------------------------------------------
165 PROCEDURE getVisitNumberResults(
166 p_lov_input_rec IN LOV_Input_Rec_Type,
167 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
168 x_return_status OUT NOCOPY VARCHAR2,
169 x_msg_count OUT NOCOPY NUMBER,
170 x_msg_data OUT NOCOPY VARCHAR2)
171 IS
172
173 l_criteria_tbl LovCriteria_Tbl_Type;
174 l_results_tbl LovResult_Tbl_Type;
175 l_attributes_tbl LovResultAttribute_Tbl_Type;
176
177 i integer ;
178
179 -- Local Variables for the sql string.
180 l_sql_string VARCHAR2(30000);
181 l_bind_index NUMBER;
182 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
183 -- dynamic cursor
184 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
185
186 --Local Variables for search results
187 l_rownum NUMBER;
188 l_visit_id NUMBER;
189 l_visit_num NUMBER;
190 l_visit_type fnd_lookup_values_vl.meaning%type;
191 l_item MTL_SYSTEM_ITEMS_KFV.concatenated_segments%type;
192 l_serial_number CSI_ITEM_INSTANCES.serial_number%type;
193 l_org HR_ALL_ORGANIZATION_UNITS.name%type;
194 l_dept BOM_DEPARTMENTS.description%type;
195 l_unit ahl_unit_config_headers.name%type;
196
197 BEGIN
198
199 x_return_status := FND_API.G_RET_STS_SUCCESS;
200
201 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
202 l_sql_string := 'SELECT * FROM ( SELECT DISTINCT Rownum RN, vst.visit_id,';
203 l_sql_string := l_sql_string || ' vst.visit_number,fndvt.meaning visit_type_mean, ';
204 l_sql_string := l_sql_string || ' mtsb.concatenated_segments item_description, csis.serial_number, ';
205 l_sql_string := l_sql_string || ' HROU.name organization_name,BDPT.description department_name, ';
206 l_sql_string := l_sql_string || ' AHL_UTILITY_PVT.GET_UNIT_NAME(VST.ITEM_INSTANCE_ID) UNIT_NAME ';
207
208 l_sql_string := l_sql_string || ' FROM ahl_visits_b vst, AHL_SIMULATION_PLANS_B ASML, ';
209 l_sql_string := l_sql_string || ' fnd_lookup_values_vl fndvt, MTL_SYSTEM_ITEMS_KFV MTSB, ';
210 l_sql_string := l_sql_string || ' CSI_ITEM_INSTANCES CSIS, HR_ALL_ORGANIZATION_UNITS HROU, ';
211 l_sql_string := l_sql_string || ' BOM_DEPARTMENTS BDPT ';
212
213 l_sql_string := l_sql_string || ' WHERE vst.simulation_plan_id = asml.simulation_plan_id AND ';
214 l_sql_string := l_sql_string || ' ASML.PRIMARY_PLAN_FLAG = ''Y'' AND ';
215 l_sql_string := l_sql_string || ' vst.status_code not in ( ''PLANNING'', ''DELETED'' ) AND ';
216 l_sql_string := l_sql_string || ' vst.visit_type_code = fndvt.lookup_code(+) AND ';
217 l_sql_string := l_sql_string || ' fndvt.lookup_type(+) = ''AHL_PLANNING_VISIT_TYPE'' AND ';
218 l_sql_string := l_sql_string || ' VSt.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+) AND ';
219 l_sql_string := l_sql_string || ' VSt.ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID AND ';
220 l_sql_string := l_sql_string || ' Vst.ITEM_INSTANCE_ID = CSIS.INSTANCE_ID(+) AND ';
221 l_sql_string := l_sql_string || ' Vst.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND ';
222 l_sql_string := l_sql_string || ' VSt.DEPARTMENT_ID = BDPT.DEPARTMENT_ID AND ';
223 l_sql_string := l_sql_string || ' vSt.TEMPLATE_FLAG = ''N'' ';
224
225 --Get Dynamic Search Criteria
226 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
227 l_bind_index := 1;
228
229 IF l_criteria_tbl.count > 0 THEN
230 i:=l_criteria_tbl.first;
231 LOOP
232
233 IF upper(l_criteria_tbl(i).AttributeName) = upper('VisitTypeMean') THEN
234 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
235 l_sql_string := l_sql_string || ' AND upper(fndvt.meaning) like :VISIT_TYPE ';
236 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
237 l_bind_index := l_bind_index + 1;
238 END IF;
239 END IF;
240
241 IF upper(l_criteria_tbl(i).AttributeName) = upper('VisitId') THEN
242 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
243 l_sql_string := l_sql_string || ' AND vst.visit_id like :VISIT_ID ';
244 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
245 l_bind_index := l_bind_index + 1;
246 END IF;
247 END IF;
248
249 IF upper(l_criteria_tbl(i).AttributeName) = upper('VisitNumber') THEN
250 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
251 l_sql_string := l_sql_string || ' AND vst.visit_number like :VISIT_NUM ';
252 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
253 l_bind_index := l_bind_index + 1;
254 END IF;
255 END IF;
256
257 IF upper(l_criteria_tbl(i).AttributeName) = upper('DepartmentName') THEN
258 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
259 l_sql_string := l_sql_string || ' AND upper(BDPT.description) like :DEPT ';
260 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
261 l_bind_index := l_bind_index + 1;
262 END IF;
263 END IF;
264
265 IF upper(l_criteria_tbl(i).AttributeName) = upper('ItemDescription') THEN
266 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
267 l_sql_string := l_sql_string || ' AND upper(mtsb.concatenated_segments) like :ITEM ';
268 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
269 l_bind_index := l_bind_index + 1;
270 END IF;
271 END IF;
272
273 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerialNumber') THEN
274 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
275 l_sql_string := l_sql_string || ' AND upper(csis.serial_number) like :SN ';
276 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
277 l_bind_index := l_bind_index + 1;
278 END IF;
279 END IF;
280
281 IF upper(l_criteria_tbl(i).AttributeName) = upper('UnitName') THEN
282 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
283 l_sql_string := l_sql_string || ' AND upper(UNIT_NAME) like :UNIT ';
284 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
285 l_bind_index := l_bind_index + 1;
286 END IF;
287 END IF;
288
289 IF upper(l_criteria_tbl(i).AttributeName) = upper('OrganizationName') THEN
290 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
291 l_sql_string := l_sql_string || ' AND upper(HROU.name) like :ORG ';
292 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
293 l_bind_index := l_bind_index + 1;
294 END IF;
295 END IF;
296
297 EXIT WHEN i= l_criteria_tbl.last;
298 i:= i+1;
299 END LOOP;
300 END IF;
301
302 -- SET START/END Row
303 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
304 --Max Row
305 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows+1;
306 l_bind_index := l_bind_index + 1;
307
308 l_sql_string := l_sql_string || ' Order By vst.visit_number ) ';
309 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
310 --start row
311 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
312 l_bind_index := l_bind_index + 1;
313 --end row
314 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
315 l_bind_index := l_bind_index + 1;
316
317 --open l_cur FOR l_sql_string;
318 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
319 (
320 p_conditions_tbl => l_bindvar_tbl,
321 p_sql_str => l_sql_string,
322 p_x_csr => l_cur
323 );
324
325 i:=1;
326 LOOP
327 FETCH l_cur INTO l_rownum,
328 l_visit_id,
329 l_visit_num,
330 l_visit_type,
331 l_item,
332 l_serial_number,
333 l_org,
334 l_dept,
335 l_unit;
336
337
338 EXIT WHEN l_cur%NOTFOUND;
339
340 -- Visit ID
341 l_attributes_tbl(1).AttributeName := 'VisitId';
342 l_attributes_tbl(1).AttributeValue := l_visit_id;
343
344 -- Visit Numbeer
345 l_attributes_tbl(2).AttributeName := 'VisitNumber';
346 l_attributes_tbl(2).AttributeValue := l_visit_num;
347
348 -- Visit Type
349 l_attributes_tbl(3).AttributeName := 'VisitTypeMean';
350 l_attributes_tbl(3).AttributeValue := l_visit_type;
351
352 -- Item Description
353 l_attributes_tbl(4).AttributeName := 'ItemDescription';
354 l_attributes_tbl(4).AttributeValue := l_item;
355
356 -- Serial Number
357 l_attributes_tbl(5).AttributeName := 'SerialNumber';
358 l_attributes_tbl(5).AttributeValue := l_serial_number;
359
360 -- Unit Name
361 l_attributes_tbl(6).AttributeName := 'UnitName';
362 l_attributes_tbl(6).AttributeValue := l_unit;
363
364 -- Organization Name
365 l_attributes_tbl(7).AttributeName := 'OrganizationName';
366 l_attributes_tbl(7).AttributeValue := l_org;
367
368 -- Department Name
369 l_attributes_tbl(8).AttributeName := 'DepartmentName';
370 l_attributes_tbl(8).AttributeValue := l_dept;
371
372 l_results_tbl(i) := l_attributes_tbl;
373 i:=i+1;
374
375 END LOOP;
376 CLOSE l_cur;
377 -- Create Attributes Table
378
379 -- Populate output parameter
380 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
381 x_lov_result_output_rec.NumberOfRows := i-1;
382 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
383
384 END getVisitNumberResults;
385
386 ---------------------------------------------------------------------
387 -- PROCEDURE
388 -- getUnitMetaData
389 --
390 -- PURPOSE
391 --
392 -- PARAMETERS
393 --
394 -- NOTES
395 ---------------------------------------------------------------------
396 PROCEDURE getUnitMetaData(
397 p_lov_input_rec IN LOV_Input_Rec_Type,
398 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
399 x_return_status OUT NOCOPY VARCHAR2,
400 x_msg_count OUT NOCOPY NUMBER,
401 x_msg_data OUT NOCOPY VARCHAR2)
402 IS
403
404 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
405 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
406
407 BEGIN
408
409 x_return_status := FND_API.G_RET_STS_SUCCESS;
410
411 -- Create Attributes Table
412 -- Unit Config Header ID
413 l_Meta_Attribute_Rec.AttributeName := 'UnitConfigHeaderId';
414 l_Meta_Attribute_Rec.Prompt := null;
415 l_Meta_Attribute_Rec.IsDisplayed := 'F';
416 l_Meta_Attribute_Rec.IsSearcheable := 'F';
417 l_Meta_Attribute_Rec.DataType := 'integer';
418
419 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
420
421 -- Unit Name
422 l_Meta_Attribute_Rec.AttributeName := 'UnitName';
423 l_Meta_Attribute_Rec.Prompt := 'Unit Name';
424 l_Meta_Attribute_Rec.IsDisplayed := 'T';
425 l_Meta_Attribute_Rec.IsSearcheable := 'T';
426 l_Meta_Attribute_Rec.DataType := 'string';
427
428 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
429
430 -- Instance Number
431 l_Meta_Attribute_Rec.AttributeName := 'InstanceNumber';
432 l_Meta_Attribute_Rec.Prompt := 'Instance Number';
433 l_Meta_Attribute_Rec.IsDisplayed := 'T';
434 l_Meta_Attribute_Rec.IsSearcheable := 'F';
435 l_Meta_Attribute_Rec.DataType := 'string';
436
437 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
438
439 -- Item Number
440 l_Meta_Attribute_Rec.AttributeName := 'ItemNumber';
441 l_Meta_Attribute_Rec.Prompt := 'Item Number';
442 l_Meta_Attribute_Rec.IsDisplayed := 'T';
443 l_Meta_Attribute_Rec.IsSearcheable := 'F';
444 l_Meta_Attribute_Rec.DataType := 'string';
445
446 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
447
448 -- Serial Number
449 l_Meta_Attribute_Rec.AttributeName := 'SerialNumber';
450 l_Meta_Attribute_Rec.Prompt := 'Serial Number';
451 l_Meta_Attribute_Rec.IsDisplayed := 'T';
452 l_Meta_Attribute_Rec.IsSearcheable := 'F';
453 l_Meta_Attribute_Rec.DataType := 'string';
454
455 l_Meta_Attribute_Tbl(4) := l_Meta_Attribute_Rec;
456
457 -- Populate output parameter
458 x_lov_meta_output_rec.LovTitle := 'Search Tail Number';
459 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
460
461 END getUnitMetaData;
462
463 ---------------------------------------------------------------------
464 -- PROCEDURE
465 -- getUnitResults
466 --
467 -- PURPOSE
468 --
469 -- PARAMETERS
470 --
471 -- NOTES
472 ---------------------------------------------------------------------
473 PROCEDURE getUnitResults(
474 p_lov_input_rec IN LOV_Input_Rec_Type,
475 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
476 x_return_status OUT NOCOPY VARCHAR2,
477 x_msg_count OUT NOCOPY NUMBER,
478 x_msg_data OUT NOCOPY VARCHAR2)
479 IS
480
481 l_criteria_tbl LovCriteria_Tbl_Type;
482 l_results_tbl LovResult_Tbl_Type;
483 l_attributes_tbl LovResultAttribute_Tbl_Type;
484
485 i integer ;
486
487 -- Local Variables for the sql string.
488 l_sql_string VARCHAR2(30000);
489 l_bind_index NUMBER;
490 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
491 -- dynamic cursor
492 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
493
494 --Local Variables for search results
495 l_rownum NUMBER;
496 l_uc_header_id NUMBER;
497 l_uc_name AHL_UNIT_CONFIG_HEADERS.NAME%type;
498 l_instance_num MTL_SYSTEM_ITEMS_KFV.concatenated_segments%type;
499 l_serial_number CSI_ITEM_INSTANCES.INSTANCE_NUMBER%type;
500 l_item MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%type;
501
502 BEGIN
503
504 x_return_status := FND_API.G_RET_STS_SUCCESS;
505
506 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
507 l_sql_string := 'SELECT * FROM ( SELECT DISTINCT Rownum RN, U.UNIT_CONFIG_HEADER_ID UC_HEADER_ID,';
508 l_sql_string := l_sql_string || ' U.NAME UC_NAME,C.INSTANCE_NUMBER , ';
509 l_sql_string := l_sql_string || ' I.CONCATENATED_SEGMENTS ITEM_NUMBER, ';
510 l_sql_string := l_sql_string || ' C.SERIAL_NUMBER ';
511
512 l_sql_string := l_sql_string || ' FROM AHL_UNIT_CONFIG_HEADERS U, ';
513 l_sql_string := l_sql_string || ' CSI_ITEM_INSTANCES C, ';
514 l_sql_string := l_sql_string || ' MTL_SYSTEM_ITEMS_KFV I ';
515
516 l_sql_string := l_sql_string || ' WHERE U.csi_item_instance_id = C.instance_id ';
517 l_sql_string := l_sql_string || ' AND C.inventory_item_id = I.inventory_item_id ';
518 l_sql_string := l_sql_string || ' AND C.last_vld_organization_id = I.organization_id ';
519 l_sql_string := l_sql_string || ' AND ahl_util_uc_pkg.get_uc_status_code(U.UNIT_CONFIG_HEADER_ID) NOT IN (''DRAFT'', ''EXPIRED'')';
520
521 --Get Dynamic Search Criteria
522 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
523 l_bind_index := 1;
524
525 IF l_criteria_tbl.count > 0 THEN
526 i:=l_criteria_tbl.first;
527 LOOP
528
529 IF upper(l_criteria_tbl(i).AttributeName) = upper('UnitName') THEN
530 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
531 l_sql_string := l_sql_string || ' AND upper(U.NAME) like :UNIT_NAME ';
532 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
533 l_bind_index := l_bind_index + 1;
534 END IF;
535 END IF;
536
537 IF upper(l_criteria_tbl(i).AttributeName) = upper('UnitConfigHeaderId') THEN
538 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
539 l_sql_string := l_sql_string || ' AND U.UNIT_CONFIG_HEADER_ID like :UC_ID ';
540 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
541 l_bind_index := l_bind_index + 1;
542 END IF;
543 END IF;
544
545 IF upper(l_criteria_tbl(i).AttributeName) = upper('InstanceNumber') THEN
546 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
547 l_sql_string := l_sql_string || ' AND upper(C.INSTANCE_NUMBER) like :INS_NUM ';
548 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
549 l_bind_index := l_bind_index + 1;
550 END IF;
551 END IF;
552
553 IF upper(l_criteria_tbl(i).AttributeName) = upper('ItemNumber') THEN
554 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
555 l_sql_string := l_sql_string || ' AND upper(I.CONCATENATED_SEGMENTS) like :ITEM ';
556 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
557 l_bind_index := l_bind_index + 1;
558 END IF;
559 END IF;
560
561 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerialNumber') THEN
562 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
563 l_sql_string := l_sql_string || ' AND upper(C.SERIAL_NUMBER) like :SN ';
564 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
565 l_bind_index := l_bind_index + 1;
566 END IF;
567 END IF;
568
569 EXIT WHEN i= l_criteria_tbl.last;
570 i:= i+1;
571 END LOOP;
572 END IF;
573
574 -- SET START/END Row
575 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
576 --Max Row
577 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows +1;
578 l_bind_index := l_bind_index + 1;
579
580 l_sql_string := l_sql_string || ' Order By U.NAME ) ';
581 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
582 --start row
583 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow +1;
584 l_bind_index := l_bind_index + 1;
585 --end row
586 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows ;
587 l_bind_index := l_bind_index + 1;
588
589 --open l_cur FOR l_sql_string;
590 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
591 (
592 p_conditions_tbl => l_bindvar_tbl,
593 p_sql_str => l_sql_string,
594 p_x_csr => l_cur
595 );
596
597 i:=1;
598 LOOP
599 FETCH l_cur INTO l_rownum,
600 l_uc_header_id,
601 l_uc_name,
602 l_instance_num,
603 l_item,
604 l_serial_number;
605
606 EXIT WHEN l_cur%NOTFOUND;
607
608 l_attributes_tbl(1).AttributeName := 'UnitConfigHeaderId';
609 l_attributes_tbl(1).AttributeValue := l_uc_header_id;
610
611 l_attributes_tbl(2).AttributeName := 'UnitName';
612 l_attributes_tbl(2).AttributeValue := l_uc_name;
613
614 l_attributes_tbl(3).AttributeName := 'InstanceNumber';
615 l_attributes_tbl(3).AttributeValue := l_instance_num;
616
617 l_attributes_tbl(4).AttributeName := 'ItemNumber';
618 l_attributes_tbl(4).AttributeValue := l_item;
619
620 l_attributes_tbl(5).AttributeName := 'SerialNumber';
621 l_attributes_tbl(5).AttributeValue := l_serial_number;
622
623 l_results_tbl(i) := l_attributes_tbl;
624 i:=i+1;
625
626 END LOOP;
627 CLOSE l_cur;
628 -- Create Attributes Table
629
630 -- Populate output parameter
631 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
632 x_lov_result_output_rec.NumberOfRows := i-1;
633 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
634
635 END getUnitResults;
636
637 ---------------------------------------------------------------------
638 -- PROCEDURE
639 -- getEmployeeMetaData
640 --
641 -- PURPOSE
642 --
643 -- PARAMETERS
644 --
645 -- NOTES
646 ---------------------------------------------------------------------
647 PROCEDURE getEmployeeMetaData(
648 p_lov_input_rec IN LOV_Input_Rec_Type,
649 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
650 x_return_status OUT NOCOPY VARCHAR2,
651 x_msg_count OUT NOCOPY NUMBER,
652 x_msg_data OUT NOCOPY VARCHAR2)
653 IS
654
655 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
656 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
657
658 BEGIN
659
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 -- Create Attributes Table
663 l_Meta_Attribute_Rec.AttributeName := 'EmployeeNumber';
664 l_Meta_Attribute_Rec.Prompt := 'Employee Number';
665 l_Meta_Attribute_Rec.IsDisplayed := 'T';
666 l_Meta_Attribute_Rec.IsSearcheable := 'T';
667 l_Meta_Attribute_Rec.DataType := 'string';
668
669 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
670
671 ----
672 l_Meta_Attribute_Rec.AttributeName := 'EmpName';
673 l_Meta_Attribute_Rec.Prompt := 'Name';
674 l_Meta_Attribute_Rec.IsDisplayed := 'T';
675 l_Meta_Attribute_Rec.IsSearcheable := 'T';
676 l_Meta_Attribute_Rec.DataType := 'string';
677
678 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
679
680 ----
681 l_Meta_Attribute_Rec.AttributeName := 'EffectiveStartDate';
682 l_Meta_Attribute_Rec.Prompt := 'Effective Start Date';
683 l_Meta_Attribute_Rec.IsDisplayed := 'T';
684 l_Meta_Attribute_Rec.IsSearcheable := 'F';
685 l_Meta_Attribute_Rec.DataType := 'date';
686
687 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
688
689 -----
690 l_Meta_Attribute_Rec.AttributeName := 'EffectiveEndDate';
691 l_Meta_Attribute_Rec.Prompt := 'Effective End Date';
692 l_Meta_Attribute_Rec.IsDisplayed := 'T';
693 l_Meta_Attribute_Rec.IsSearcheable := 'F';
694 l_Meta_Attribute_Rec.DataType := 'date';
695
696 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
697
698 ----
699 l_Meta_Attribute_Rec.AttributeName := 'OrgName';
700 l_Meta_Attribute_Rec.Prompt := 'Organization';
701 l_Meta_Attribute_Rec.IsDisplayed := 'T';
702 l_Meta_Attribute_Rec.IsSearcheable := 'T';
703 l_Meta_Attribute_Rec.DataType := 'string';
704
705 l_Meta_Attribute_Tbl(4) := l_Meta_Attribute_Rec;
706 ----
707 l_Meta_Attribute_Rec.AttributeName := 'OrgID';
708 l_Meta_Attribute_Rec.Prompt := null;
709 l_Meta_Attribute_Rec.IsDisplayed := 'F';
710 l_Meta_Attribute_Rec.IsSearcheable := 'T';
711 l_Meta_Attribute_Rec.DataType := 'integer';
712
713 l_Meta_Attribute_Tbl(5) := l_Meta_Attribute_Rec;
714 ----
715 l_Meta_Attribute_Rec.AttributeName := 'EmployeeId';
716 l_Meta_Attribute_Rec.Prompt := null;
717 l_Meta_Attribute_Rec.IsDisplayed := 'F';
718 l_Meta_Attribute_Rec.IsSearcheable := 'F';
719 l_Meta_Attribute_Rec.DataType := 'integer';
720
721 l_Meta_Attribute_Tbl(6) := l_Meta_Attribute_Rec;
722
723 -- Populate output parameter
724 x_lov_meta_output_rec.LovTitle := 'Search Employee';
725 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
726
727 END getEmployeeMetaData;
728
729 ---------------------------------------------------------------------
730 -- PROCEDURE
731 -- getEmployeeResults
732 --
733 -- PURPOSE
734 --
735 -- PARAMETERS
736 --
737 -- NOTES
738 ---------------------------------------------------------------------
739 PROCEDURE getEmployeeResults(
740 p_lov_input_rec IN LOV_Input_Rec_Type,
741 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
742 x_return_status OUT NOCOPY VARCHAR2,
743 x_msg_count OUT NOCOPY NUMBER,
744 x_msg_data OUT NOCOPY VARCHAR2)
745 IS
746
747 l_criteria_tbl LovCriteria_Tbl_Type;
748 l_results_tbl LovResult_Tbl_Type;
749 l_attributes_tbl LovResultAttribute_Tbl_Type;
750
751 i integer ;
752
753 -- Local Variables for the sql string.
754 l_sql_string VARCHAR2(30000);
755 l_bind_index NUMBER;
756 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
757 -- dynamic cursor
758 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
759
760 --Local Variables for search results
761 l_rownum NUMBER;
762 l_emp_num mtl_employees_current_view.EMPLOYEE_NUM%type;
763 l_emp_name mtl_employees_current_view.FULL_NAME%type;
764 l_org_name HR_ORGANIZATION_UNITS.NAME%type;
765 l_start_date DATE;
766 l_end_date DATE;
767 l_org_id NUMBER;
768 l_emp_id NUMBER;
769
770 BEGIN
771
772 x_return_status := FND_API.G_RET_STS_SUCCESS;
773
774 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
775 l_sql_string := 'SELECT * FROM ( SELECT DISTINCT Rownum RN, PF.EMPLOYEE_NUM EMPLOYEE_NUMBER,';
776 l_sql_string := l_sql_string || ' PF.FULL_NAME EMP_NAME, BRE.EFFECTIVE_START_DATE , ';
777 l_sql_string := l_sql_string || ' BRE.EFFECTIVE_END_DATE ,HOU.NAME ORG_NAME, ';
778 l_sql_string := l_sql_string || ' HOU.ORGANIZATION_ID,pf.employee_id EMPLOYEE_ID ';
779
780 l_sql_string := l_sql_string || ' FROM mtl_employees_current_view pf, bom_resource_employees bre, ';
781 l_sql_string := l_sql_string || ' HR_ORGANIZATION_UNITS HOU, FND_USER FU ';
782
783 l_sql_string := l_sql_string || ' WHERE pf.employee_id = bre.person_id ';
784 l_sql_string := l_sql_string || ' and pf.organization_id = bre.organization_id ';
785 l_sql_string := l_sql_string || ' and sysdate between BRE.EFFECTIVE_START_DATE and BRE.EFFECTIVE_END_DATE ';
786 l_sql_string := l_sql_string || ' and pf.organization_id = hou.organization_id ';
787 l_sql_string := l_sql_string || ' and FU.employee_id = pf.employee_id ';
788
789 --Get Dynamic Search Criteria
790 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
791 l_bind_index := 1;
792
793 IF l_criteria_tbl.count > 0 THEN
794 i:=l_criteria_tbl.first;
795 LOOP
796
797 IF upper(l_criteria_tbl(i).AttributeName) = upper('EmployeeNumber') THEN
798 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
799 l_sql_string := l_sql_string || ' AND upper(PF.EMPLOYEE_NUM) like :EMP_NUM ';
800 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
801 l_bind_index := l_bind_index + 1;
802 END IF;
803 END IF;
804
805 IF upper(l_criteria_tbl(i).AttributeName) = upper('EffectiveStartDate') THEN
806 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
807 l_sql_string := l_sql_string || ' AND BRE.EFFECTIVE_START_DATE like :START_DATE ';
808 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
809 l_bind_index := l_bind_index + 1;
810 END IF;
811 END IF;
812
813 IF upper(l_criteria_tbl(i).AttributeName) = upper('EffectiveEndDate') THEN
814 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
815 l_sql_string := l_sql_string || ' AND BRE.EFFECTIVE_END_DATE like :END_DATE ';
816 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
817 l_bind_index := l_bind_index + 1;
818 END IF;
819 END IF;
820
821 IF upper(l_criteria_tbl(i).AttributeName) = upper('EmpName') THEN
822 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
823 l_sql_string := l_sql_string || ' AND upper(PF.FULL_NAME) like :EMP_NAME ';
824 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
825 l_bind_index := l_bind_index + 1;
826 END IF;
827 END IF;
828
829 IF upper(l_criteria_tbl(i).AttributeName) = upper('OrgName') THEN
830 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
831 l_sql_string := l_sql_string || ' AND upper(HOU.NAME) like :ORG_NAME ';
832 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
833 l_bind_index := l_bind_index + 1;
834 END IF;
835 END IF;
836
837 IF upper(l_criteria_tbl(i).AttributeName) = upper('OrgID') THEN
838 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
839 l_sql_string := l_sql_string || ' AND HOU.ORGANIZATION_ID like :ORG_ID ';
840 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
841 l_bind_index := l_bind_index + 1;
842 END IF;
843 END IF;
844
845 IF upper(l_criteria_tbl(i).AttributeName) = upper('EmployeeId') THEN
846 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
847 l_sql_string := l_sql_string || ' AND pf.employee_id like :EMP_ID ';
848 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
849 l_bind_index := l_bind_index + 1;
850 END IF;
851 END IF;
852
853 EXIT WHEN i= l_criteria_tbl.last;
854 i:= i+1;
855 END LOOP;
856 END IF;
857
858 -- SET START/END Row
859 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
860 --Max Row
861 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows+1;
862 l_bind_index := l_bind_index + 1;
863
864 l_sql_string := l_sql_string || ' Order By PF.FULL_NAME ) ';
865 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
866 --start row
867 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
868 l_bind_index := l_bind_index + 1;
869 --end row
870 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
871 l_bind_index := l_bind_index + 1;
872
873 --open l_cur FOR l_sql_string;
874 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
875 (
876 p_conditions_tbl => l_bindvar_tbl,
877 p_sql_str => l_sql_string,
878 p_x_csr => l_cur
879 );
880
881 i:=1;
882 LOOP
883 FETCH l_cur INTO l_rownum,
884 l_emp_num,
885 l_emp_name,
886 l_start_date,
887 l_end_date,
888 l_org_name,
889 l_org_id,
890 l_emp_id;
891
892 EXIT WHEN l_cur%NOTFOUND;
893
894 l_attributes_tbl(1).AttributeName := 'EmployeeNumber';
895 l_attributes_tbl(1).AttributeValue := l_emp_num;
896
897 l_attributes_tbl(2).AttributeName := 'EmpName';
898 l_attributes_tbl(2).AttributeValue := l_emp_name;
899
900 l_attributes_tbl(3).AttributeName := 'EffectiveStartDate';
901 l_attributes_tbl(3).AttributeValue := l_start_date;
902
903 l_attributes_tbl(4).AttributeName := 'EffectiveEndDate';
904 l_attributes_tbl(4).AttributeValue := l_end_date;
905
906 l_attributes_tbl(5).AttributeName := 'OrgName';
907 l_attributes_tbl(5).AttributeValue := l_org_name;
908
909 l_attributes_tbl(6).AttributeName := 'OrgID';
910 l_attributes_tbl(6).AttributeValue := l_org_id;
911
912 l_attributes_tbl(7).AttributeName := 'EmployeeId';
913 l_attributes_tbl(7).AttributeValue := l_emp_id;
914
915 l_results_tbl(i) := l_attributes_tbl;
916 i:=i+1;
917
918 END LOOP;
919 CLOSE l_cur;
920 -- Create Attributes Table
921
922 -- Populate output parameter
923 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
924 x_lov_result_output_rec.NumberOfRows := i-1;
925 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
926
927 END getEmployeeResults;
928
929 ---------------------------------------------------------------------
930 -- PROCEDURE
931 -- getEmpNameMetaData
932 --
933 -- PURPOSE
934 --
935 -- PARAMETERS
936 --
937 -- NOTES
938 ---------------------------------------------------------------------
939 PROCEDURE getEmpNameMetaData(
940 p_lov_input_rec IN LOV_Input_Rec_Type,
941 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
942 x_return_status OUT NOCOPY VARCHAR2,
943 x_msg_count OUT NOCOPY NUMBER,
944 x_msg_data OUT NOCOPY VARCHAR2)
945 IS
946
947 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
948 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
949
950 BEGIN
951
952 x_return_status := FND_API.G_RET_STS_SUCCESS;
953
954 -- Create Attributes Table
955 l_Meta_Attribute_Rec.AttributeName := 'EmpName';
956 l_Meta_Attribute_Rec.Prompt := 'Name';
957 l_Meta_Attribute_Rec.IsDisplayed := 'T';
958 l_Meta_Attribute_Rec.IsSearcheable := 'T';
959 l_Meta_Attribute_Rec.DataType := 'string';
960
961 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
962
963 ----
964 l_Meta_Attribute_Rec.AttributeName := 'EmpLovEmpNum';
965 l_Meta_Attribute_Rec.Prompt := 'Employee Number';
966 l_Meta_Attribute_Rec.IsDisplayed := 'T';
967 l_Meta_Attribute_Rec.IsSearcheable := 'F';
968 l_Meta_Attribute_Rec.DataType := 'string';
969
970 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
971
972 ----
973 l_Meta_Attribute_Rec.AttributeName := 'EmpLovEmpId';
974 l_Meta_Attribute_Rec.Prompt := null;
975 l_Meta_Attribute_Rec.IsDisplayed := 'F';
976 l_Meta_Attribute_Rec.IsSearcheable := 'F';
977 l_Meta_Attribute_Rec.DataType := 'integer';
978
979 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
980
981 ----
982 l_Meta_Attribute_Rec.AttributeName := 'EmpLovDepartmentId';
983 l_Meta_Attribute_Rec.Prompt := null;
984 l_Meta_Attribute_Rec.IsDisplayed := 'F';
985 l_Meta_Attribute_Rec.IsSearcheable := 'T';
986 l_Meta_Attribute_Rec.DataType := 'integer';
987
988 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
989
990 ----
991 l_Meta_Attribute_Rec.AttributeName := 'EmpLovResourceId';
992 l_Meta_Attribute_Rec.Prompt := null;
993 l_Meta_Attribute_Rec.IsDisplayed := 'F';
994 l_Meta_Attribute_Rec.IsSearcheable := 'T';
995 l_Meta_Attribute_Rec.DataType := 'integer';
996
997 l_Meta_Attribute_Tbl(4) := l_Meta_Attribute_Rec;
998
999 ----
1000 l_Meta_Attribute_Rec.AttributeName := 'EmpLovEmpId';
1001 l_Meta_Attribute_Rec.Prompt := null;
1002 l_Meta_Attribute_Rec.IsDisplayed := 'F';
1003 l_Meta_Attribute_Rec.IsSearcheable := 'F';
1004 l_Meta_Attribute_Rec.DataType := 'integer';
1005
1006 l_Meta_Attribute_Tbl(5) := l_Meta_Attribute_Rec;
1007
1008 -- Populate output parameter
1009 x_lov_meta_output_rec.LovTitle := 'Search Employee';
1010 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
1011
1012 END getEmpNameMetaData;
1013
1014 ---------------------------------------------------------------------
1015 -- PROCEDURE
1016 -- getEmpNameResults
1017 --
1018 -- PURPOSE
1019 --
1020 -- PARAMETERS
1021 --
1022 -- NOTES
1023 ---------------------------------------------------------------------
1024 PROCEDURE getEmpNameResults(
1025 p_lov_input_rec IN LOV_Input_Rec_Type,
1026 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
1027 x_return_status OUT NOCOPY VARCHAR2,
1028 x_msg_count OUT NOCOPY NUMBER,
1029 x_msg_data OUT NOCOPY VARCHAR2)
1030 IS
1031
1032 -- Define local Variables
1033 L_API_VERSION CONSTANT NUMBER := 1.0;
1034 L_API_NAME CONSTANT VARCHAR2(30) := 'getEmpNameResults';
1035 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1036
1037 l_criteria_tbl LovCriteria_Tbl_Type;
1038 l_results_tbl LovResult_Tbl_Type;
1039 l_attributes_tbl LovResultAttribute_Tbl_Type;
1040
1041 i integer ;
1042
1043 -- Local Variables for the sql string.
1044 l_sql_string VARCHAR2(30000);
1045 l_bind_index NUMBER;
1046 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
1047 -- dynamic cursor
1048 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
1049
1050 --Local Variables for search results
1051 l_rownum NUMBER ;
1052 l_Resource_ID NUMBER ;
1053 l_Emp_Number mtl_employees_current_view.employee_num%type;
1054 l_Emp_Name mtl_employees_current_view.full_name%type;
1055 l_Dept_ID NUMBER ;
1056 l_Emp_ID NUMBER ;
1057
1058
1059 BEGIN
1060
1061 x_return_status := FND_API.G_RET_STS_SUCCESS;
1062
1063 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
1064 l_sql_string := 'SELECT * FROM ( SELECT rownum RN, bdri.resource_id ,pf.employee_num,pf.full_name, bdri.department_id,pf.employee_id ';
1065 l_sql_string := l_sql_string || ' FROM mtl_employees_current_view pf, bom_resource_employees bre, bom_dept_res_instances bdri ';
1066 l_sql_string := l_sql_string || ' WHERE bre.instance_id = bdri.instance_id ';
1067 l_sql_string := l_sql_string || ' and pf.employee_id=bre.person_id ';
1068 l_sql_string := l_sql_string || ' and pf.organization_id = bre.organization_id ';
1069 l_sql_string := l_sql_string || ' and sysdate between BRE.EFFECTIVE_START_DATE and BRE.EFFECTIVE_END_DATE ';
1070
1071 --Get Dynamic Search Criteria
1072 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
1073 l_bind_index := 1;
1074
1075 IF l_criteria_tbl.count > 0 THEN
1076 i:=l_criteria_tbl.first;
1077 LOOP
1078 IF upper(l_criteria_tbl(i).AttributeName) = upper('EmpName') THEN
1079 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1080 l_sql_string := l_sql_string || ' AND upper(pf.full_name) like :EMP_NAME ';
1081 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
1082 l_bind_index := l_bind_index + 1;
1083 END IF;
1084 END IF;
1085
1086 IF upper(l_criteria_tbl(i).AttributeName) = upper('EmpLovDepartmentId') THEN
1087 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1088 l_sql_string := l_sql_string || ' AND bdri.department_id like :DEPT_ID ';
1089 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
1090 l_bind_index := l_bind_index + 1;
1091 END IF;
1092 END IF;
1093
1094 IF upper(l_criteria_tbl(i).AttributeName) = upper('EmpLovResourceId') THEN
1095 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1096 l_sql_string := l_sql_string || ' AND bdri.resource_id like :RES_ID ';
1097 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
1098 l_bind_index := l_bind_index + 1;
1099 END IF;
1100 END IF;
1101
1102 IF upper(l_criteria_tbl(i).AttributeName) = upper('EmpLovEmpNum') THEN
1103 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1104 l_sql_string := l_sql_string || ' AND upper(pf.employee_num) like :EMP_NUM ';
1105 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
1106 l_bind_index := l_bind_index + 1;
1107 END IF;
1108 END IF;
1109
1110 IF upper(l_criteria_tbl(i).AttributeName) = upper('EmpLovEmpId') THEN
1111 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1112 l_sql_string := l_sql_string || ' AND pf.employee_id like :EMP_ID ';
1113 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
1114 l_bind_index := l_bind_index + 1;
1115 END IF;
1116 END IF;
1117
1118 EXIT WHEN i= l_criteria_tbl.last;
1119 i:= i+1;
1120 END LOOP;
1121 END IF;
1122
1123 -- SET START/END Row
1124 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
1125 --Max Row
1126 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows +1;
1127 l_bind_index := l_bind_index + 1;
1128
1129 l_sql_string := l_sql_string || ' Order By pf.full_name) ';
1130 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
1131 --start row
1132 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow +1;
1133 l_bind_index := l_bind_index + 1;
1134 --end row
1135 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
1136 l_bind_index := l_bind_index + 1;
1137
1138 --open l_cur FOR l_sql_string;
1139 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
1140 (
1141 p_conditions_tbl => l_bindvar_tbl,
1142 p_sql_str => l_sql_string,
1143 p_x_csr => l_cur
1144 );
1145
1146 i:=1;
1147 LOOP
1148 FETCH l_cur INTO l_rownum,
1149 l_Resource_ID,
1150 l_Emp_Number,
1151 l_Emp_Name,
1152 l_Dept_ID,
1153 l_Emp_ID;
1154 EXIT WHEN l_cur%NOTFOUND;
1155
1156 --
1157 l_attributes_tbl(1).AttributeName := 'EmpName';
1158 l_attributes_tbl(1).AttributeValue := l_Emp_Name;
1159
1160 --
1161 l_attributes_tbl(2).AttributeName := 'EmpLovEmpNum';
1162 l_attributes_tbl(2).AttributeValue := l_Emp_Number;
1163
1164 --
1165 l_attributes_tbl(3).AttributeName := 'EmpLovEmpId';
1166 l_attributes_tbl(3).AttributeValue := l_Emp_ID;
1167
1168 --
1169 l_attributes_tbl(4).AttributeName := 'EmpLovDepartmentId';
1170 l_attributes_tbl(4).AttributeValue := l_Dept_ID;
1171
1172 --
1173 l_attributes_tbl(5).AttributeName := 'EmpLovResourceId';
1174 l_attributes_tbl(5).AttributeValue := l_Resource_ID;
1175
1176 l_results_tbl(i) := l_attributes_tbl;
1177 i:=i+1;
1178
1179 END LOOP;
1180 CLOSE l_cur;
1181 -- Create Attributes Table
1182
1183 -- Populate output parameter
1184 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
1185 x_lov_result_output_rec.NumberOfRows := i-1;
1186 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
1187
1188 END getEmpNameResults;
1189
1190 ---------------------------------------------------------------------
1191 -- PROCEDURE
1192 -- getWOStatusMetaData
1193 --
1194 -- PURPOSE
1195 --
1196 -- PARAMETERS
1197 --
1198 -- NOTES
1199 ---------------------------------------------------------------------
1200 PROCEDURE getWOStatusMetaData(
1201 p_lov_input_rec IN LOV_Input_Rec_Type,
1202 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
1203 x_return_status OUT NOCOPY VARCHAR2,
1204 x_msg_count OUT NOCOPY NUMBER,
1205 x_msg_data OUT NOCOPY VARCHAR2)
1206 IS
1207
1208 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
1209 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
1210
1211 BEGIN
1212
1213 x_return_status := FND_API.G_RET_STS_SUCCESS;
1214
1215 -- Create Attributes Table
1216 l_Meta_Attribute_Rec.AttributeName := 'Code';
1217 l_Meta_Attribute_Rec.Prompt := 'Code';
1218 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1219 l_Meta_Attribute_Rec.IsSearcheable := 'T';
1220 l_Meta_Attribute_Rec.DataType := 'string';
1221
1222 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
1223
1224 ----
1225 l_Meta_Attribute_Rec.AttributeName := 'Meaning';
1226 l_Meta_Attribute_Rec.Prompt := 'Name';
1227 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1228 l_Meta_Attribute_Rec.IsSearcheable := 'T';
1229 l_Meta_Attribute_Rec.DataType := 'string';
1230
1231 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
1232
1233 -- Populate output parameter
1234 x_lov_meta_output_rec.LovTitle := 'Search Work Order Status';
1235 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
1236
1237 END getWOStatusMetaData;
1238
1239 ---------------------------------------------------------------------
1240 -- PROCEDURE
1241 -- getWOStatusResults
1242 --
1243 -- PURPOSE
1244 --
1245 -- PARAMETERS
1246 --
1247 -- NOTES
1248 ---------------------------------------------------------------------
1249 PROCEDURE getWOStatusResults(
1250 p_lov_input_rec IN LOV_Input_Rec_Type,
1251 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
1252 x_return_status OUT NOCOPY VARCHAR2,
1253 x_msg_count OUT NOCOPY NUMBER,
1254 x_msg_data OUT NOCOPY VARCHAR2)
1255 IS
1256
1257 -- Define local Variables
1258 L_API_VERSION CONSTANT NUMBER := 1.0;
1259 L_API_NAME CONSTANT VARCHAR2(30) := 'getWOStatusResults';
1260 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1261
1262 l_criteria_tbl LovCriteria_Tbl_Type;
1263 l_results_tbl LovResult_Tbl_Type;
1264 l_attributes_tbl LovResultAttribute_Tbl_Type;
1265
1266 i integer ;
1267
1268 -- Local Variables for the sql string.
1269 l_sql_string VARCHAR2(30000);
1270 l_bind_index NUMBER;
1271 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
1272 -- dynamic cursor
1273 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
1274
1275 --Local Variables for search results
1276 l_code FND_LOOKUP_VALUES_VL.lookup_code%type;
1277 l_mean FND_LOOKUP_VALUES_VL.meaning%type;
1278 l_rownum NUMBER;
1279
1280 BEGIN
1281
1282 x_return_status := FND_API.G_RET_STS_SUCCESS;
1283
1284 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
1285 l_sql_string := 'SELECT rownum , meaning,STATUS_CODE FROM ( select DISTINCT FND.MEANING meaning, SOR.NEXT_STATUS_CODE STATUS_CODE ';
1286 l_sql_string := l_sql_string || ' FROM AHL_STATUS_ORDER_RULES SOR, FND_LOOKUP_VALUES_VL FND ';
1287 l_sql_string := l_sql_string || ' where SOR.SYSTEM_STATUS_TYPE = ''AHL_JOB_STATUS'' AND FND.LOOKUP_TYPE(+)= ''AHL_JOB_STATUS'' ';
1288 l_sql_string := l_sql_string || ' AND FND.LOOKUP_CODE(+) = SOR.NEXT_STATUS_CODE ';
1289 --Get Dynamic Search Criteria
1290 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
1291 l_bind_index := 1;
1292
1293 IF l_criteria_tbl.count > 0 THEN
1294 i:=l_criteria_tbl.first;
1295 LOOP
1296 IF upper(l_criteria_tbl(i).AttributeName) = upper('Code') THEN
1297 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1298 l_sql_string := l_sql_string || ' AND SOR.CURRENT_STATUS_CODE like :CODE ';
1299 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
1300 l_bind_index := l_bind_index + 1;
1301 END IF;
1302 ELSIF upper(l_criteria_tbl(i).AttributeName) = upper('Meaning') THEN
1303 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1304 l_sql_string := l_sql_string || ' AND upper(FND.MEANING) like :MEAN ';
1305 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
1306 l_bind_index := l_bind_index + 1;
1307 END IF;
1308 END IF;
1309
1310 EXIT WHEN i= l_criteria_tbl.last;
1311 i:= i+1;
1312 END LOOP;
1313 END IF;
1314
1315
1316
1317 l_sql_string := l_sql_string || ' Order By meaning) '; -- SET START/END Row
1318
1319 l_sql_string := l_sql_string || ' WHERE rownum BETWEEN :START_ROW AND :END_ROW ';
1320 --start row
1321 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
1322 l_bind_index := l_bind_index + 1;
1323 --end row
1324 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
1325 l_bind_index := l_bind_index + 1;
1326 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
1327 --Max Row
1328 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows+1;
1329 l_bind_index := l_bind_index + 1;
1330
1331 --open l_cur FOR l_sql_string;
1332 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
1333 (
1334 p_conditions_tbl => l_bindvar_tbl,
1335 p_sql_str => l_sql_string,
1336 p_x_csr => l_cur
1337 );
1338
1339 i:=1;
1340 LOOP
1341 FETCH l_cur INTO l_rownum,
1342 l_mean,
1343 l_code;
1344
1345 EXIT WHEN l_cur%NOTFOUND;
1346
1347 --
1348 l_attributes_tbl(1).AttributeName := 'Code';
1349 l_attributes_tbl(1).AttributeValue := l_code;
1350
1351 --
1352 l_attributes_tbl(2).AttributeName := 'Meaning';
1353 l_attributes_tbl(2).AttributeValue := l_mean;
1354
1355 l_results_tbl(i) := l_attributes_tbl;
1356 i:=i+1;
1357
1358 END LOOP;
1359 CLOSE l_cur;
1360 -- Create Attributes Table
1361
1362 -- Populate output parameter
1363 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
1364 x_lov_result_output_rec.NumberOfRows := i-1;
1365 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
1366
1367 END getWOStatusResults;
1368
1369 ---------------------------------------------------------------------
1370 -- PROCEDURE
1371 -- getOperSeqMetaData
1372 --
1373 -- PURPOSE
1374 --
1375 -- PARAMETERS
1376 --
1377 -- NOTES
1378 ---------------------------------------------------------------------
1379 PROCEDURE getOperSeqMetaData(
1380 p_lov_input_rec IN LOV_Input_Rec_Type,
1381 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
1382 x_return_status OUT NOCOPY VARCHAR2,
1383 x_msg_count OUT NOCOPY NUMBER,
1384 x_msg_data OUT NOCOPY VARCHAR2)
1385 IS
1386
1387 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
1388 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
1389
1390 BEGIN
1391
1392 x_return_status := FND_API.G_RET_STS_SUCCESS;
1393
1394 -- Create Attributes Table
1395 l_Meta_Attribute_Rec.AttributeName := 'WorkorderOperationId';
1396 l_Meta_Attribute_Rec.Prompt := null;
1397 l_Meta_Attribute_Rec.IsDisplayed := 'F';
1398 l_Meta_Attribute_Rec.IsSearcheable := 'F';
1399 l_Meta_Attribute_Rec.DataType := 'integer';
1400
1401 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
1402
1403 ----
1404 l_Meta_Attribute_Rec.AttributeName := 'OrganizationId';
1405 l_Meta_Attribute_Rec.Prompt := null;
1406 l_Meta_Attribute_Rec.IsDisplayed := 'F';
1407 l_Meta_Attribute_Rec.IsSearcheable := 'F';
1408 l_Meta_Attribute_Rec.DataType := 'integer';
1409
1410 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
1411
1412 ----
1413 l_Meta_Attribute_Rec.AttributeName := 'DepartmentId';
1414 l_Meta_Attribute_Rec.Prompt := null;
1415 l_Meta_Attribute_Rec.IsDisplayed := 'F';
1416 l_Meta_Attribute_Rec.IsSearcheable := 'F';
1417 l_Meta_Attribute_Rec.DataType := 'integer';
1418
1419 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
1420
1421 ----
1422 l_Meta_Attribute_Rec.AttributeName := 'OperationSequenceNum';
1423 l_Meta_Attribute_Rec.Prompt := 'Operation Sequence';
1424 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1425 l_Meta_Attribute_Rec.IsSearcheable := 'T';
1426 l_Meta_Attribute_Rec.DataType := 'integer';
1427
1428 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
1429
1430 ----
1431 l_Meta_Attribute_Rec.AttributeName := 'WorkorderId';
1432 l_Meta_Attribute_Rec.Prompt := null;
1433 l_Meta_Attribute_Rec.IsDisplayed := 'F';
1434 l_Meta_Attribute_Rec.IsSearcheable := 'T';
1435 l_Meta_Attribute_Rec.DataType := 'integer';
1436
1437 l_Meta_Attribute_Tbl(4) := l_Meta_Attribute_Rec;
1438
1439 ----
1440 l_Meta_Attribute_Rec.AttributeName := 'Status';
1441 l_Meta_Attribute_Rec.Prompt := 'Status';
1442 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1443 l_Meta_Attribute_Rec.IsSearcheable := 'F';
1444 l_Meta_Attribute_Rec.DataType := 'string';
1445
1446 l_Meta_Attribute_Tbl(5) := l_Meta_Attribute_Rec;
1447
1448 ----
1449 l_Meta_Attribute_Rec.AttributeName := 'Description';
1450 l_Meta_Attribute_Rec.Prompt := 'Description';
1451 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1452 l_Meta_Attribute_Rec.IsSearcheable := 'F';
1453 l_Meta_Attribute_Rec.DataType := 'string';
1454
1455 l_Meta_Attribute_Tbl(6) := l_Meta_Attribute_Rec;
1456
1457 -- Populate output parameter
1458 x_lov_meta_output_rec.LovTitle := 'Search Operation Status';
1459 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
1460
1461 END getOperSeqMetaData;
1462
1463 ---------------------------------------------------------------------
1464 -- PROCEDURE
1465 -- getOperSeqResults
1466 --
1467 -- PURPOSE
1468 --
1469 -- PARAMETERS
1470 --
1471 -- NOTES
1472 ---------------------------------------------------------------------
1473 PROCEDURE getOperSeqResults(
1474 p_lov_input_rec IN LOV_Input_Rec_Type,
1475 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
1476 x_return_status OUT NOCOPY VARCHAR2,
1477 x_msg_count OUT NOCOPY NUMBER,
1478 x_msg_data OUT NOCOPY VARCHAR2)
1479 IS
1480
1481 -- Define local Variables
1482 L_API_VERSION CONSTANT NUMBER := 1.0;
1483 L_API_NAME CONSTANT VARCHAR2(30) := 'getOperSeqResults';
1484 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1485
1486 l_criteria_tbl LovCriteria_Tbl_Type;
1487 l_results_tbl LovResult_Tbl_Type;
1488 l_attributes_tbl LovResultAttribute_Tbl_Type;
1489
1490 i integer ;
1491
1492 -- Local Variables for the sql string.
1493 l_sql_string VARCHAR2(30000);
1494 l_bind_index NUMBER;
1495 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
1496 -- dynamic cursor
1497 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
1498
1499 --Local Variables for search results
1500 l_rownum NUMBER;
1501 l_wo_oper_id NUMBER;
1502 l_org_id NUMBER;
1503 l_oper_seq NUMBER;
1504 l_wo_id NUMBER;
1505 l_status AHL_WORKORDER_OPERATIONS_V.STATUS%type;
1506 l_desc AHL_WORKORDER_OPERATIONS_V.DESCRIPTION%type;
1507 l_dept_id NUMBER;
1508
1509 BEGIN
1510
1511 x_return_status := FND_API.G_RET_STS_SUCCESS;
1512
1513 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
1514 l_sql_string := 'SELECT * FROM ( SELECT Rownum RN, WORKORDER_OPERATION_ID, ';
1515 l_sql_string := l_sql_string || ' ORGANIZATION_ID, OPERATION_SEQUENCE_NUM, ';
1516 l_sql_string := l_sql_string || ' WORKORDER_ID, STATUS, DESCRIPTION, DEPARTMENT_ID ';
1517 l_sql_string := l_sql_string || ' FROM AHL_WORKORDER_OPERATIONS_V ';
1518 l_sql_string := l_sql_string || ' WHERE 0=0 ';
1519
1520 --Get Dynamic Search Criteria
1521 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
1522 l_bind_index := 1;
1523
1524 IF l_criteria_tbl.count > 0 THEN
1525 i:=l_criteria_tbl.first;
1526 LOOP
1527 IF upper(l_criteria_tbl(i).AttributeName) = upper('WorkorderOperationId') THEN
1528 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1529 l_sql_string := l_sql_string || ' AND WORKORDER_OPERATION_ID like :WO_OPER_ID ';
1530 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
1531 l_bind_index := l_bind_index + 1;
1532 END IF;
1533 END IF;
1534
1535 IF upper(l_criteria_tbl(i).AttributeName) = upper('OrganizationId') THEN
1536 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1537 l_sql_string := l_sql_string || ' AND ORGANIZATION_ID like :ORG_ID ';
1538 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
1539 l_bind_index := l_bind_index + 1;
1540 END IF;
1541 END IF;
1542
1543 IF upper(l_criteria_tbl(i).AttributeName) = upper('DepartmentId') THEN
1544 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1545 l_sql_string := l_sql_string || ' AND DEPARTMENT_ID like :DEPT_ID ';
1546 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
1547 l_bind_index := l_bind_index + 1;
1548 END IF;
1549 END IF;
1550
1551 IF upper(l_criteria_tbl(i).AttributeName) = upper('OperationSequenceNum') THEN
1552 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1553 l_sql_string := l_sql_string || ' AND OPERATION_SEQUENCE_NUM like :OPER_SEQ ';
1554 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
1555 l_bind_index := l_bind_index + 1;
1556 END IF;
1557 END IF;
1558
1559 IF upper(l_criteria_tbl(i).AttributeName) = upper('WorkorderId') THEN
1560 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1561 l_sql_string := l_sql_string || ' AND WORKORDER_ID like :WO_ID ';
1562 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
1563 l_bind_index := l_bind_index + 1;
1564 END IF;
1565 END IF;
1566
1567 IF upper(l_criteria_tbl(i).AttributeName) = upper('Status') THEN
1568 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1569 l_sql_string := l_sql_string || ' AND upper(STATUS) like :STATUS ';
1570 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
1571 l_bind_index := l_bind_index + 1;
1572 END IF;
1573 END IF;
1574
1575 IF upper(l_criteria_tbl(i).AttributeName) = upper('Description') THEN
1576 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1577 l_sql_string := l_sql_string || ' AND upper(Description) like :DESC ';
1578 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
1579 l_bind_index := l_bind_index + 1;
1580 END IF;
1581 END IF;
1582
1583 EXIT WHEN i= l_criteria_tbl.last;
1584 i:= i+1;
1585 END LOOP;
1586 END IF;
1587
1588 -- SET START/END Row
1589 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
1590 --Max Row
1591 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows +1;
1592 l_bind_index := l_bind_index + 1;
1593
1594 l_sql_string := l_sql_string || ' Order By WORKORDER_ID,OPERATION_SEQUENCE_NUM ) ';
1595 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
1596 --start row
1597 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow +1;
1598 l_bind_index := l_bind_index + 1;
1599 --end row
1600 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
1601 l_bind_index := l_bind_index + 1;
1602
1603 --open l_cur FOR l_sql_string;
1604 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
1605 (
1606 p_conditions_tbl => l_bindvar_tbl,
1607 p_sql_str => l_sql_string,
1608 p_x_csr => l_cur
1609 );
1610
1611 i:=1;
1612 LOOP
1613 FETCH l_cur INTO l_rownum,
1614 l_wo_oper_id,
1615 l_org_id,
1616 l_oper_seq,
1617 l_wo_id,
1618 l_status,
1619 l_desc,
1620 l_dept_id;
1621
1622
1623 EXIT WHEN l_cur%NOTFOUND;
1624
1625 --
1626 l_attributes_tbl(1).AttributeName := 'WorkorderOperationId';
1627 l_attributes_tbl(1).AttributeValue := l_wo_oper_id;
1628
1629 --
1630 l_attributes_tbl(2).AttributeName := 'OrganizationId';
1631 l_attributes_tbl(2).AttributeValue := l_org_id;
1632
1633 --
1634 l_attributes_tbl(3).AttributeName := 'DepartmentId';
1635 l_attributes_tbl(3).AttributeValue := l_dept_id;
1636
1637 --
1638 l_attributes_tbl(4).AttributeName := 'OperationSequenceNum';
1639 l_attributes_tbl(4).AttributeValue := l_oper_seq;
1640
1641 --
1642 l_attributes_tbl(5).AttributeName := 'WorkorderId';
1643 l_attributes_tbl(5).AttributeValue := l_wo_id;
1644
1645 --
1646 l_attributes_tbl(6).AttributeName := 'Status';
1647 l_attributes_tbl(6).AttributeValue := l_status;
1648
1649 --
1650 l_attributes_tbl(7).AttributeName := 'Description';
1651 l_attributes_tbl(7).AttributeValue := l_desc;
1652
1653 l_results_tbl(i) := l_attributes_tbl;
1654 i:=i+1;
1655
1656 END LOOP;
1657 CLOSE l_cur;
1658 -- Create Attributes Table
1659
1660 -- Populate output parameter
1661 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
1662 x_lov_result_output_rec.NumberOfRows := i-1;
1663 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
1664
1665 END getOperSeqResults;
1666
1667 ---------------------------------------------------------------------
1668 -- PROCEDURE
1669 -- getResCodeMetaData
1670 --
1671 -- PURPOSE
1672 --
1673 -- PARAMETERS
1674 --
1675 -- NOTES
1676 ---------------------------------------------------------------------
1677 PROCEDURE getResCodeMetaData(
1678 p_lov_input_rec IN LOV_Input_Rec_Type,
1679 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
1680 x_return_status OUT NOCOPY VARCHAR2,
1681 x_msg_count OUT NOCOPY NUMBER,
1682 x_msg_data OUT NOCOPY VARCHAR2)
1683 IS
1684
1685 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
1686 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
1687
1688 BEGIN
1689
1690 x_return_status := FND_API.G_RET_STS_SUCCESS;
1691
1692 -- Create Attributes Table
1693 l_Meta_Attribute_Rec.AttributeName := 'ResCodeLovResCode';
1694 l_Meta_Attribute_Rec.Prompt := 'Resource';
1695 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1696 l_Meta_Attribute_Rec.IsSearcheable := 'T';
1697 l_Meta_Attribute_Rec.DataType := 'string';
1698
1699 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
1700
1701 ----
1702 l_Meta_Attribute_Rec.AttributeName := 'ResCodeLovDescription';
1703 l_Meta_Attribute_Rec.Prompt := 'Description';
1704 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1705 l_Meta_Attribute_Rec.IsSearcheable := 'F';
1706 l_Meta_Attribute_Rec.DataType := 'string';
1707
1708 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
1709
1710 ----
1711 l_Meta_Attribute_Rec.AttributeName := 'ResCodeLovUom';
1712 l_Meta_Attribute_Rec.Prompt := 'UOM';
1713 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1714 l_Meta_Attribute_Rec.IsSearcheable := 'F';
1715 l_Meta_Attribute_Rec.DataType := 'string';
1716
1717 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
1718
1719 ----
1720 l_Meta_Attribute_Rec.AttributeName := 'ResCodeLovResType';
1721 l_Meta_Attribute_Rec.Prompt := 'Resource Type';
1722 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1723 l_Meta_Attribute_Rec.IsSearcheable := 'T';
1724 l_Meta_Attribute_Rec.DataType := 'string';
1725
1726 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
1727
1728 ----
1729 l_Meta_Attribute_Rec.AttributeName := 'ResCodeLovResId';
1730 l_Meta_Attribute_Rec.Prompt := null;
1731 l_Meta_Attribute_Rec.IsDisplayed := 'F';
1732 l_Meta_Attribute_Rec.IsSearcheable := 'F';
1733 l_Meta_Attribute_Rec.DataType := 'integer';
1734
1735 l_Meta_Attribute_Tbl(4) := l_Meta_Attribute_Rec;
1736
1737 ----
1738 l_Meta_Attribute_Rec.AttributeName := 'ResCodeLovDepartmentId';
1739 l_Meta_Attribute_Rec.Prompt := null;
1740 l_Meta_Attribute_Rec.IsDisplayed := 'F';
1741 l_Meta_Attribute_Rec.IsSearcheable := 'T';
1742 l_Meta_Attribute_Rec.DataType := 'integer';
1743
1744 l_Meta_Attribute_Tbl(5) := l_Meta_Attribute_Rec;
1745
1746 -- Populate output parameter
1747 x_lov_meta_output_rec.LovTitle := 'Search Resource';
1748 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
1749
1750 END getResCodeMetaData;
1751
1752 ---------------------------------------------------------------------
1753 -- PROCEDURE
1754 -- getResCodeResults
1755 --
1756 -- PURPOSE
1757 --
1758 -- PARAMETERS
1759 --
1760 -- NOTES
1761 ---------------------------------------------------------------------
1762 PROCEDURE getResCodeResults(
1763 p_lov_input_rec IN LOV_Input_Rec_Type,
1764 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
1765 x_return_status OUT NOCOPY VARCHAR2,
1766 x_msg_count OUT NOCOPY NUMBER,
1767 x_msg_data OUT NOCOPY VARCHAR2)
1768 IS
1769
1770 -- Define local Variables
1771 L_API_VERSION CONSTANT NUMBER := 1.0;
1772 L_API_NAME CONSTANT VARCHAR2(30) := 'getResCodeResults';
1773 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1774
1775 l_criteria_tbl LovCriteria_Tbl_Type;
1776 l_results_tbl LovResult_Tbl_Type;
1777 l_attributes_tbl LovResultAttribute_Tbl_Type;
1778
1779 i integer ;
1780
1781 -- Local Variables for the sql string.
1782 l_sql_string VARCHAR2(30000);
1783 l_bind_index NUMBER;
1784 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
1785 -- dynamic cursor
1786 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
1787
1788 --Local Variables for search results
1789 l_rownum NUMBER;
1790 l_res_id NUMBER;
1791 l_res_code BOM_RESOURCES.RESOURCE_CODE%type;
1792 l_desc BOM_RESOURCES.DESCRIPTION%type;
1793 l_res_type MFG_LOOKUPS.MEANING%type;
1794 l_UOM mtl_units_of_measure.unit_of_measure%type;
1795 l_dept_id NUMBER;
1796
1797 BEGIN
1798
1799 x_return_status := FND_API.G_RET_STS_SUCCESS;
1800
1801 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
1802 l_sql_string := 'SELECT * FROM ( SELECT Rownum RN, ';
1803 l_sql_string := l_sql_string || ' BR.RESOURCE_ID ,BR.RESOURCE_CODE, BR.description , ';
1804 l_sql_string := l_sql_string || ' MFGL.MEANING RESOURCE_TYPE,UOM.unit_of_measure, BDR.DEPARTMENT_ID ';
1805 l_sql_string := l_sql_string || ' FROM BOM_RESOURCES BR,BOM_DEPARTMENT_RESOURCES BDR, ';
1806 l_sql_string := l_sql_string || ' MFG_LOOKUPS MFGL, mtl_units_of_measure UOM ';
1807 l_sql_string := l_sql_string || ' WHERE UOM.uom_code(+) = BR.UNIT_OF_MEASURE ';
1808 l_sql_string := l_sql_string || ' AND BR.RESOURCE_ID = BDR.RESOURCE_ID ';
1809 l_sql_string := l_sql_string || ' AND MFGL.LOOKUP_TYPE(+) = ''BOM_RESOURCE_TYPE'' ';
1810 l_sql_string := l_sql_string || ' AND MFGL.LOOKUP_CODE(+) = BR.RESOURCE_TYPE ';
1811
1812 --Get Dynamic Search Criteria
1813 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
1814 l_bind_index := 1;
1815
1816 IF l_criteria_tbl.count > 0 THEN
1817 i:=l_criteria_tbl.first;
1818 LOOP
1819
1820 IF upper(l_criteria_tbl(i).AttributeName) = upper('ResCodeLovResCode') THEN
1821 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1822 l_sql_string := l_sql_string || ' AND upper(BR.RESOURCE_CODE) like :RES_CODE ';
1823 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
1824 l_bind_index := l_bind_index + 1;
1825 END IF;
1826 END IF;
1827
1828 IF upper(l_criteria_tbl(i).AttributeName) = upper('ResCodeLovDescription') THEN
1829 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1830 l_sql_string := l_sql_string || ' AND upper(BR.description) like :RES_DESC ';
1831 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
1832 l_bind_index := l_bind_index + 1;
1833 END IF;
1834 END IF;
1835
1836 IF upper(l_criteria_tbl(i).AttributeName) = upper('ResCodeLovUom') THEN
1837 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1838 l_sql_string := l_sql_string || ' AND upper(UOM.unit_of_measure) like :UOM ';
1839 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
1840 l_bind_index := l_bind_index + 1;
1841 END IF;
1842 END IF;
1843
1844 IF upper(l_criteria_tbl(i).AttributeName) = upper('ResCodeLovResType') THEN
1845 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1846 l_sql_string := l_sql_string || ' AND upper(MFGL.MEANING) like :RES_TYPE ';
1847 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
1848 l_bind_index := l_bind_index + 1;
1849 END IF;
1850 END IF;
1851
1852 IF upper(l_criteria_tbl(i).AttributeName) = upper('ResCodeLovResId') THEN
1853 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1854 l_sql_string := l_sql_string || ' AND BR.RESOURCE_ID like :RES_ID ';
1855 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
1856 l_bind_index := l_bind_index + 1;
1857 END IF;
1858 END IF;
1859
1860 IF upper(l_criteria_tbl(i).AttributeName) = upper('ResCodeLovDepartmentId') THEN
1861 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
1862 l_sql_string := l_sql_string || ' AND BDR.DEPARTMENT_ID like :DEPT_ID ';
1863 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
1864 l_bind_index := l_bind_index + 1;
1865 END IF;
1866 END IF;
1867
1868 EXIT WHEN i= l_criteria_tbl.last;
1869 i:= i+1;
1870 END LOOP;
1871 END IF;
1872
1873 -- SET START/END Row
1874 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
1875 --Max Row
1876 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows+1;
1877 l_bind_index := l_bind_index + 1;
1878
1879 l_sql_string := l_sql_string || ' Order By BDR.DEPARTMENT_ID,BR.RESOURCE_CODE ) ';
1880 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
1881 --start row
1882 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
1883 l_bind_index := l_bind_index + 1;
1884 --end row
1885 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
1886 l_bind_index := l_bind_index + 1;
1887
1888 --open l_cur FOR l_sql_string;
1889 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
1890 (
1891 p_conditions_tbl => l_bindvar_tbl,
1892 p_sql_str => l_sql_string,
1893 p_x_csr => l_cur
1894 );
1895
1896 i:=1;
1897 LOOP
1898 FETCH l_cur INTO l_rownum,
1899 l_res_id,
1900 l_res_code,
1901 l_desc,
1902 l_res_type,
1903 l_UOM,
1904 l_dept_id;
1905
1906
1907 EXIT WHEN l_cur%NOTFOUND;
1908
1909 --
1910 l_attributes_tbl(1).AttributeName := 'ResCodeLovResCode';
1911 l_attributes_tbl(1).AttributeValue := l_res_code;
1912
1913 --
1914 l_attributes_tbl(2).AttributeName := 'ResCodeLovDescription';
1915 l_attributes_tbl(2).AttributeValue := l_desc;
1916
1917 --
1918 l_attributes_tbl(3).AttributeName := 'ResCodeLovUom';
1919 l_attributes_tbl(3).AttributeValue := l_UOM;
1920
1921 --
1922 l_attributes_tbl(4).AttributeName := 'ResCodeLovResType';
1923 l_attributes_tbl(4).AttributeValue := l_res_type;
1924
1925 --
1926 l_attributes_tbl(5).AttributeName := 'ResCodeLovResId';
1927 l_attributes_tbl(5).AttributeValue := l_res_id;
1928
1929 --
1930 l_attributes_tbl(6).AttributeName := 'ResCodeLovDepartmentId';
1931 l_attributes_tbl(6).AttributeValue := l_dept_id;
1932
1933 l_results_tbl(i) := l_attributes_tbl;
1934 i:=i+1;
1935
1936 END LOOP;
1937 CLOSE l_cur;
1938 -- Create Attributes Table
1939
1940 -- Populate output parameter
1941 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
1942 x_lov_result_output_rec.NumberOfRows := i-1;
1943 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
1944
1945 END getResCodeResults;
1946
1947 ---------------------------------------------------------------------
1948 -- PROCEDURE
1949 -- getATACodeMetaData
1950 --
1951 -- PURPOSE
1952 --
1953 -- PARAMETERS
1954 --
1955 -- NOTES
1956 ---------------------------------------------------------------------
1957 PROCEDURE getATACodeMetaData(
1958 p_lov_input_rec IN LOV_Input_Rec_Type,
1959 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
1960 x_return_status OUT NOCOPY VARCHAR2,
1961 x_msg_count OUT NOCOPY NUMBER,
1962 x_msg_data OUT NOCOPY VARCHAR2)
1963 IS
1964
1965 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
1966 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
1967
1968 BEGIN
1969
1970 x_return_status := FND_API.G_RET_STS_SUCCESS;
1971
1972 -- Create Attributes Table
1973 l_Meta_Attribute_Rec.AttributeName := 'ATACode';
1974 l_Meta_Attribute_Rec.Prompt := 'ATA Code';
1975 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1976 l_Meta_Attribute_Rec.IsSearcheable := 'T';
1977 l_Meta_Attribute_Rec.DataType := 'string';
1978
1979 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
1980
1981 ----
1982 l_Meta_Attribute_Rec.AttributeName := ' ';
1983 l_Meta_Attribute_Rec.Prompt := 'Description';
1984 l_Meta_Attribute_Rec.IsDisplayed := 'T';
1985 l_Meta_Attribute_Rec.IsSearcheable := 'F';
1986 l_Meta_Attribute_Rec.DataType := 'string';
1987
1988 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
1989
1990 ----
1991 l_Meta_Attribute_Rec.AttributeName := 'Position';
1992 l_Meta_Attribute_Rec.Prompt := 'Position';
1993 l_Meta_Attribute_Rec.IsDisplayed := 'F';
1994 l_Meta_Attribute_Rec.IsSearcheable := 'T';
1995 l_Meta_Attribute_Rec.DataType := 'string';
1996
1997 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
1998
1999 ----
2000 l_Meta_Attribute_Rec.AttributeName := 'PositionID';
2001 l_Meta_Attribute_Rec.Prompt := null;
2002 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2003 l_Meta_Attribute_Rec.IsSearcheable := 'F';
2004 l_Meta_Attribute_Rec.DataType := 'integer';
2005
2006 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
2007
2008 ----
2009 l_Meta_Attribute_Rec.AttributeName := 'UnitName';
2010 l_Meta_Attribute_Rec.Prompt := 'Unit';
2011 l_Meta_Attribute_Rec.IsDisplayed := 'T';
2012 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2013 l_Meta_Attribute_Rec.DataType := 'string';
2014
2015 l_Meta_Attribute_Tbl(4) := l_Meta_Attribute_Rec;
2016
2017 -- Populate output parameter
2018 x_lov_meta_output_rec.LovTitle := 'Search ATA Code';
2019 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
2020
2021 END getATACodeMetaData;
2022
2023 ---------------------------------------------------------------------
2024 -- PROCEDURE
2025 -- getATACodeResults
2026 --
2027 -- PURPOSE
2028 --
2029 -- PARAMETERS
2030 --
2031 -- NOTES
2032 ---------------------------------------------------------------------
2033 PROCEDURE getATACodeResults(
2034 p_lov_input_rec IN LOV_Input_Rec_Type,
2035 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
2036 x_return_status OUT NOCOPY VARCHAR2,
2037 x_msg_count OUT NOCOPY NUMBER,
2038 x_msg_data OUT NOCOPY VARCHAR2)
2039 IS
2040
2041 -- Define local Variables
2042 L_API_VERSION CONSTANT NUMBER := 1.0;
2043 L_API_NAME CONSTANT VARCHAR2(30) := 'getATACodeResults';
2044 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2045
2046 l_criteria_tbl LovCriteria_Tbl_Type;
2047 l_results_tbl LovResult_Tbl_Type;
2048 l_attributes_tbl LovResultAttribute_Tbl_Type;
2049
2050 i integer ;
2051
2052 -- Local Variables for the sql string.
2053 l_sql_string VARCHAR2(30000);
2054 l_bind_index NUMBER;
2055 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
2056 -- dynamic cursor
2057 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
2058
2059 --Local Variables for search results
2060 l_rownum NUMBER;
2061 l_ata ahl_mc_relationships_v.ATA_MEANING%type;
2062 l_ata_desc ahl_mc_relationships_v.ATA_DESC%type;
2063 l_position_id NUMBER;
2064 l_unit ahl_unit_config_headers.name%type;
2065 l_position ahl_mc_relationships_v.position_ref_meaning%type;
2066
2067 BEGIN
2068
2069 x_return_status := FND_API.G_RET_STS_SUCCESS;
2070
2071 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
2072 l_sql_string := 'SELECT * FROM ( SELECT DISTINCT Rownum RN, ATA_meaning, ATA_Desc, position_ref_meaning, position_key,uc.name ';
2073 l_sql_string := l_sql_string || ' from ahl_mc_relationships_v mc, ahl_unit_config_headers uc ';
2074 l_sql_string := l_sql_string || ' where uc.MASTER_CONFIG_ID = mc.mc_header_id AND ATA_meaning IS NOT NULL ';
2075
2076 --Get Dynamic Search Criteria
2077 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
2078 l_bind_index := 1;
2079
2080 IF l_criteria_tbl.count > 0 THEN
2081 i:=l_criteria_tbl.first;
2082 LOOP
2083
2084 IF upper(l_criteria_tbl(i).AttributeName) = upper('ATACode') THEN
2085 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2086 l_sql_string := l_sql_string || ' AND upper(ATA_meaning) like :ATAC ';
2087 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
2088 l_bind_index := l_bind_index + 1;
2089 END IF;
2090 ELSIF upper(l_criteria_tbl(i).AttributeName) = upper('Position') THEN
2091 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2092 l_sql_string := l_sql_string || ' AND upper(position_ref_meaning) like :POS ';
2093 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
2094 l_bind_index := l_bind_index + 1;
2095 END IF;
2096 ELSIF upper(l_criteria_tbl(i).AttributeName) = upper('UnitName') THEN
2097 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2098 l_sql_string := l_sql_string || ' AND upper(uc.name) like :UNIT ';
2099 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
2100 l_bind_index := l_bind_index + 1;
2101 END IF;
2102 END IF;
2103
2104 EXIT WHEN i= l_criteria_tbl.last;
2105 i:= i+1;
2106 END LOOP;
2107 END IF;
2108
2109 -- SET START/END Row
2110 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
2111 --Max Row
2112 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows +1;
2113 l_bind_index := l_bind_index + 1;
2114
2115 l_sql_string := l_sql_string || ' Order By ATA_meaning ) ';
2116 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
2117 --start row
2118 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow +1;
2119 l_bind_index := l_bind_index + 1;
2120 --end row
2121 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
2122 l_bind_index := l_bind_index + 1;
2123
2124 --open l_cur FOR l_sql_string;
2125 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
2126 (
2127 p_conditions_tbl => l_bindvar_tbl,
2128 p_sql_str => l_sql_string,
2129 p_x_csr => l_cur
2130 );
2131
2132 i:=1;
2133 LOOP
2134 FETCH l_cur INTO l_rownum,
2135 l_ata,
2136 l_ata_desc,
2137 l_position,
2138 l_position_id,
2139 l_unit;
2140
2141
2142
2143
2144 EXIT WHEN l_cur%NOTFOUND;
2145
2146 --
2147 l_attributes_tbl(1).AttributeName := 'ATACode';
2148 l_attributes_tbl(1).AttributeValue := l_ata;
2149
2150 --
2151 l_attributes_tbl(2).AttributeName := 'Description';
2152 l_attributes_tbl(2).AttributeValue := l_ata_desc;
2153
2154 --
2155 l_attributes_tbl(3).AttributeName := 'Position';
2156 l_attributes_tbl(3).AttributeValue := l_position;
2157
2158 --
2159 l_attributes_tbl(4).AttributeName := 'PositionID';
2160 l_attributes_tbl(4).AttributeValue := l_position_id;
2161
2162 l_attributes_tbl(5).AttributeName := 'UnitName';
2163 l_attributes_tbl(5).AttributeValue := l_unit;
2164
2165
2166 l_results_tbl(i) := l_attributes_tbl;
2167 i:=i+1;
2168
2169 END LOOP;
2170 CLOSE l_cur;
2171 -- Create Attributes Table
2172
2173 -- Populate output parameter
2174 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
2175 x_lov_result_output_rec.NumberOfRows := i-1;
2176 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
2177
2178 END getATACodeResults;
2179
2180 ---------------------------------------------------------------------
2181 -- PROCEDURE
2182 -- getPositionMetaData
2183 --
2184 -- PURPOSE
2185 --
2186 -- PARAMETERS
2187 --
2188 -- NOTES
2189 ---------------------------------------------------------------------
2190 PROCEDURE getPositionMetaData(
2191 p_lov_input_rec IN LOV_Input_Rec_Type,
2192 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
2193 x_return_status OUT NOCOPY VARCHAR2,
2194 x_msg_count OUT NOCOPY NUMBER,
2195 x_msg_data OUT NOCOPY VARCHAR2)
2196 IS
2197
2198 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
2199 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
2200
2201 BEGIN
2202
2203 x_return_status := FND_API.G_RET_STS_SUCCESS;
2204
2205 ----
2206 l_Meta_Attribute_Rec.AttributeName := 'Position';
2207 l_Meta_Attribute_Rec.Prompt := 'Position';
2208 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2209 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2210 l_Meta_Attribute_Rec.DataType := 'string';
2211
2212 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
2213 ----
2214 l_Meta_Attribute_Rec.AttributeName := 'Description';
2215 l_Meta_Attribute_Rec.Prompt := 'Description';
2216 l_Meta_Attribute_Rec.IsDisplayed := 'T';
2217 l_Meta_Attribute_Rec.IsSearcheable := 'F';
2218 l_Meta_Attribute_Rec.DataType := 'string';
2219
2220 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
2221
2222 -- Create Attributes Table
2223 l_Meta_Attribute_Rec.AttributeName := 'ATACode';
2224 l_Meta_Attribute_Rec.Prompt := 'ATA Code';
2225 l_Meta_Attribute_Rec.IsDisplayed := 'T';
2226 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2227 l_Meta_Attribute_Rec.DataType := 'string';
2228
2229 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
2230
2231 ----
2232 l_Meta_Attribute_Rec.AttributeName := 'PositionID';
2233 l_Meta_Attribute_Rec.Prompt := null;
2234 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2235 l_Meta_Attribute_Rec.IsSearcheable := 'F';
2236 l_Meta_Attribute_Rec.DataType := 'integer';
2237
2238 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
2239
2240 ----
2241 l_Meta_Attribute_Rec.AttributeName := 'UnitName';
2242 l_Meta_Attribute_Rec.Prompt := 'Unit';
2243 l_Meta_Attribute_Rec.IsDisplayed := 'T';
2244 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2245 l_Meta_Attribute_Rec.DataType := 'string';
2246
2247 l_Meta_Attribute_Tbl(4) := l_Meta_Attribute_Rec;
2248
2249 -- Populate output parameter
2250 x_lov_meta_output_rec.LovTitle := 'Search Position';
2251 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
2252
2253 END getPositionMetadata;
2254
2255 ---------------------------------------------------------------------
2256 -- PROCEDURE
2257 -- getATACodeResults
2258 --
2259 -- PURPOSE
2260 --
2261 -- PARAMETERS
2262 --
2263 -- NOTES
2264 ---------------------------------------------------------------------
2265 PROCEDURE getPositionResults(
2266 p_lov_input_rec IN LOV_Input_Rec_Type,
2267 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
2268 x_return_status OUT NOCOPY VARCHAR2,
2269 x_msg_count OUT NOCOPY NUMBER,
2270 x_msg_data OUT NOCOPY VARCHAR2)
2271 IS
2272
2273 -- Define local Variables
2274 L_API_VERSION CONSTANT NUMBER := 1.0;
2275 L_API_NAME CONSTANT VARCHAR2(30) := 'getPositionResults';
2276 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2277
2278 l_criteria_tbl LovCriteria_Tbl_Type;
2279 l_results_tbl LovResult_Tbl_Type;
2280 l_attributes_tbl LovResultAttribute_Tbl_Type;
2281
2282 i integer ;
2283
2284 -- Local Variables for the sql string.
2285 l_sql_string VARCHAR2(30000);
2286 l_bind_index NUMBER;
2287 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
2288 -- dynamic cursor
2289 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
2290
2291 --Local Variables for search results
2292 l_rownum NUMBER;
2293 l_ata ahl_mc_relationships_v.ATA_MEANING%type;
2294 l_pos_desc ahl_mc_relationships_v.position_ref_desc%type;
2295 l_position_id NUMBER;
2296 l_unit ahl_unit_config_headers.name%type;
2297 l_position ahl_mc_relationships_v.position_ref_meaning%type;
2298
2299 BEGIN
2300
2301 x_return_status := FND_API.G_RET_STS_SUCCESS;
2302
2303 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
2304 l_sql_string := 'SELECT * FROM ( SELECT DISTINCT Rownum RN, ATA_meaning, position_ref_desc, position_ref_meaning, position_key,uc.name ';
2305 l_sql_string := l_sql_string || ' from ahl_mc_relationships_v mc, ahl_unit_config_headers uc ';
2306 l_sql_string := l_sql_string || ' where uc.MASTER_CONFIG_ID = mc.mc_header_id ';
2307
2308 --Get Dynamic Search Criteria
2309 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
2310 l_bind_index := 1;
2311
2312 IF l_criteria_tbl.count > 0 THEN
2313 i:=l_criteria_tbl.first;
2314 LOOP
2315
2316 IF upper(l_criteria_tbl(i).AttributeName) = upper('ATACode') THEN
2317 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2318 l_sql_string := l_sql_string || ' AND upper(ATA_meaning) like :ATAC ';
2319 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
2320 l_bind_index := l_bind_index + 1;
2321 END IF;
2322 ELSIF upper(l_criteria_tbl(i).AttributeName) = upper('Position') THEN
2323 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2324 l_sql_string := l_sql_string || ' AND upper(position_ref_meaning) like :POS ';
2325 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
2326 l_bind_index := l_bind_index + 1;
2327 END IF;
2328 ELSIF upper(l_criteria_tbl(i).AttributeName) = upper('UnitName') THEN
2329 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2330 l_sql_string := l_sql_string || ' AND upper(uc.name) like :UNIT ';
2331 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
2332 l_bind_index := l_bind_index + 1;
2333 END IF;
2334 END IF;
2335
2336 EXIT WHEN i= l_criteria_tbl.last;
2337 i:= i+1;
2338 END LOOP;
2339 END IF;
2340
2341 -- SET START/END Row
2342 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
2343 --Max Row
2344 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows +1;
2345 l_bind_index := l_bind_index + 1;
2346
2347 l_sql_string := l_sql_string || ' Order By position_ref_meaning ) ';
2348 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
2349 --start row
2350 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow +1;
2351 l_bind_index := l_bind_index + 1;
2352 --end row
2353 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
2354 l_bind_index := l_bind_index + 1;
2355
2356 --open l_cur FOR l_sql_string;
2357 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
2358 (
2359 p_conditions_tbl => l_bindvar_tbl,
2360 p_sql_str => l_sql_string,
2361 p_x_csr => l_cur
2362 );
2363
2364 i:=1;
2365 LOOP
2366 FETCH l_cur INTO l_rownum,
2367 l_ata,
2368 l_pos_desc,
2369 l_position,
2370 l_position_id,
2371 l_unit;
2372
2373
2374
2375
2376 EXIT WHEN l_cur%NOTFOUND;
2377 --
2378 l_attributes_tbl(1).AttributeName := 'Position';
2379 l_attributes_tbl(1).AttributeValue := l_position;
2380 --
2381 l_attributes_tbl(2).AttributeName := 'Description';
2382 l_attributes_tbl(2).AttributeValue := l_pos_desc;
2383 --
2384 l_attributes_tbl(3).AttributeName := 'ATACode';
2385 l_attributes_tbl(3).AttributeValue := l_ata;
2386
2387 --
2388 l_attributes_tbl(4).AttributeName := 'PositionID';
2389 l_attributes_tbl(4).AttributeValue := l_position_id;
2390
2391 l_attributes_tbl(5).AttributeName := 'UnitName';
2392 l_attributes_tbl(5).AttributeValue := l_unit;
2393
2394
2395 l_results_tbl(i) := l_attributes_tbl;
2396 i:=i+1;
2397
2398 END LOOP;
2399 CLOSE l_cur;
2400 -- Create Attributes Table
2401
2402 -- Populate output parameter
2403 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
2404 x_lov_result_output_rec.NumberOfRows := i-1;
2405 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
2406
2407 END getPositionResults;
2408 ---------------------------------------------------------------------
2409 -- PROCEDURE
2410 -- getSerialNumMetaData
2411 --
2412 -- PURPOSE
2413 --
2414 -- PARAMETERS
2415 --
2416 -- NOTES
2417 ---------------------------------------------------------------------
2418 PROCEDURE getSerialNumMetaData(
2419 p_lov_input_rec IN LOV_Input_Rec_Type,
2420 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
2421 x_return_status OUT NOCOPY VARCHAR2,
2422 x_msg_count OUT NOCOPY NUMBER,
2423 x_msg_data OUT NOCOPY VARCHAR2)
2424 IS
2425
2426 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
2427 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
2428
2429 BEGIN
2430
2431 x_return_status := FND_API.G_RET_STS_SUCCESS;
2432
2433 -- Create Attributes Table
2434 l_Meta_Attribute_Rec.AttributeName := 'SerNumLovSerNum';
2435 l_Meta_Attribute_Rec.Prompt := 'Serial Number';
2436 l_Meta_Attribute_Rec.IsDisplayed := 'T';
2437 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2438 l_Meta_Attribute_Rec.DataType := 'string';
2439
2440 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
2441
2442 ----
2443 l_Meta_Attribute_Rec.AttributeName := 'SerNumLovInstId';
2444 l_Meta_Attribute_Rec.Prompt := null;
2445 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2446 l_Meta_Attribute_Rec.IsSearcheable := 'F';
2447 l_Meta_Attribute_Rec.DataType := 'integer';
2448
2449 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
2450
2451 ----
2452 l_Meta_Attribute_Rec.AttributeName := 'SerNumLovDeptId';
2453 l_Meta_Attribute_Rec.Prompt := null;
2454 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2455 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2456 l_Meta_Attribute_Rec.DataType := 'integer';
2457
2458 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
2459
2460 ----
2461 l_Meta_Attribute_Rec.AttributeName := 'SerNumLovResId';
2462 l_Meta_Attribute_Rec.Prompt := null;
2463 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2464 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2465 l_Meta_Attribute_Rec.DataType := 'integer';
2466
2467 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
2468
2469 -- Populate output parameter
2470 x_lov_meta_output_rec.LovTitle := 'Search Serial Number';
2471 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
2472
2473 END getSerialNumMetaData;
2474
2475 ---------------------------------------------------------------------
2476 -- PROCEDURE
2477 -- getSerialNumResults
2478 --
2479 -- PURPOSE
2480 --
2481 -- PARAMETERS
2482 --
2483 -- NOTES
2484 ---------------------------------------------------------------------
2485 PROCEDURE getSerialNumResults(
2486 p_lov_input_rec IN LOV_Input_Rec_Type,
2487 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
2488 x_return_status OUT NOCOPY VARCHAR2,
2489 x_msg_count OUT NOCOPY NUMBER,
2490 x_msg_data OUT NOCOPY VARCHAR2)
2491 IS
2492
2493 -- Define local Variables
2494 L_API_VERSION CONSTANT NUMBER := 1.0;
2495 L_API_NAME CONSTANT VARCHAR2(30) := 'getSerialNumResults';
2496 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2497
2498 l_criteria_tbl LovCriteria_Tbl_Type;
2499 l_results_tbl LovResult_Tbl_Type;
2500 l_attributes_tbl LovResultAttribute_Tbl_Type;
2501
2502 i integer ;
2503
2504 -- Local Variables for the sql string.
2505 l_sql_string VARCHAR2(30000);
2506 l_bind_index NUMBER;
2507 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
2508 -- dynamic cursor
2509 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
2510
2511 --Local Variables for search results
2512 l_rownum NUMBER;
2513 l_serial_num bom_dept_res_instances.SERIAL_NUMBER%type;
2514 l_instance_id NUMBER;
2515 l_dept_id NUMBER;
2516 l_res_id NUMBER;
2517
2518 BEGIN
2519
2520 x_return_status := FND_API.G_RET_STS_SUCCESS;
2521
2522 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
2523 l_sql_string := 'SELECT * FROM ( SELECT DISTINCT Rownum RN, bdri.SERIAL_NUMBER,';
2524 l_sql_string := l_sql_string || ' bdri.instance_id, bdri.department_id, bdri.resource_id ';
2525 l_sql_string := l_sql_string || ' FROM bom_dept_res_instances BDRI ';
2526 l_sql_string := l_sql_string || ' WHERE bdri.SERIAL_NUMBER is not null ';
2527
2528 --Get Dynamic Search Criteria
2529 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
2530 l_bind_index := 1;
2531
2532 IF l_criteria_tbl.count > 0 THEN
2533 i:=l_criteria_tbl.first;
2534 LOOP
2535
2536 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovSerNum') THEN
2537 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2538 l_sql_string := l_sql_string || ' AND upper(bdri.SERIAL_NUMBER) like :SN ';
2539 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
2540 l_bind_index := l_bind_index + 1;
2541 END IF;
2542 END IF;
2543
2544 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovInstId') THEN
2545 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2546 l_sql_string := l_sql_string || ' AND upper(bdri.instance_id) like :INS_ID ';
2547 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
2548 l_bind_index := l_bind_index + 1;
2549 END IF;
2550 END IF;
2551
2552 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovResId') THEN
2553 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2554 l_sql_string := l_sql_string || ' AND bdri.resource_id like :RES_ID ';
2555 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
2556 l_bind_index := l_bind_index + 1;
2557 END IF;
2558 END IF;
2559
2560 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovDeptId') THEN
2561 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2562 l_sql_string := l_sql_string || ' AND bdri.department_id like :DEPT_ID ';
2563 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
2564 l_bind_index := l_bind_index + 1;
2565 END IF;
2566 END IF;
2567
2568 EXIT WHEN i= l_criteria_tbl.last;
2569 i:= i+1;
2570 END LOOP;
2571 END IF;
2572
2573 -- SET START/END Row
2574 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
2575 --Max Row
2576 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows+1;
2577 l_bind_index := l_bind_index + 1;
2578
2579 l_sql_string := l_sql_string || ' Order By bdri.department_id,bdri.resource_id,bdri.SERIAL_NUMBER ) ';
2580 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
2581 --start row
2582 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
2583 l_bind_index := l_bind_index + 1;
2584 --end row
2585 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
2586 l_bind_index := l_bind_index + 1;
2587
2588 --open l_cur FOR l_sql_string;
2589 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
2590 (
2591 p_conditions_tbl => l_bindvar_tbl,
2592 p_sql_str => l_sql_string,
2593 p_x_csr => l_cur
2594 );
2595
2596 i:=1;
2597 LOOP
2598 FETCH l_cur INTO l_rownum,
2599 l_serial_num,
2600 l_instance_id,
2601 l_dept_id,
2602 l_res_id;
2603
2604
2605 EXIT WHEN l_cur%NOTFOUND;
2606
2607 --
2608 l_attributes_tbl(1).AttributeName := 'SerNumLovSerNum';
2609 l_attributes_tbl(1).AttributeValue := l_serial_num;
2610
2611 --
2612 l_attributes_tbl(2).AttributeName := 'SerNumLovInstId';
2613 l_attributes_tbl(2).AttributeValue := l_instance_id;
2614
2615 --
2616 l_attributes_tbl(3).AttributeName := 'SerNumLovDeptId';
2617 l_attributes_tbl(3).AttributeValue := l_dept_id;
2618
2619 --
2620 l_attributes_tbl(4).AttributeName := 'SerNumLovResId';
2621 l_attributes_tbl(4).AttributeValue := l_res_id;
2622
2623
2624 l_results_tbl(i) := l_attributes_tbl;
2625 i:=i+1;
2626
2627 END LOOP;
2628 CLOSE l_cur;
2629 -- Create Attributes Table
2630
2631 -- Populate output parameter
2632 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
2633 x_lov_result_output_rec.NumberOfRows := i-1;
2634 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
2635
2636 END getSerialNumResults;
2637
2638 ---------------------------------------------------------------------
2639 -- PROCEDURE
2640 -- getWOPositionMetaData
2641 --
2642 -- PURPOSE
2643 --
2644 -- PARAMETERS
2645 --
2646 -- NOTES
2647 ---------------------------------------------------------------------
2648 PROCEDURE getWOPositionMetaData(
2649 p_lov_input_rec IN LOV_Input_Rec_Type,
2650 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
2651 x_return_status OUT NOCOPY VARCHAR2,
2652 x_msg_count OUT NOCOPY NUMBER,
2653 x_msg_data OUT NOCOPY VARCHAR2)
2654 IS
2655
2656 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
2657 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
2658
2659 BEGIN
2660
2661 x_return_status := FND_API.G_RET_STS_SUCCESS;
2662
2663 -- Create Attributes Table
2664 l_Meta_Attribute_Rec.AttributeName := 'SerNumLovSerNum';
2665 l_Meta_Attribute_Rec.Prompt := 'Serial Number';
2666 l_Meta_Attribute_Rec.IsDisplayed := 'T';
2667 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2668 l_Meta_Attribute_Rec.DataType := 'string';
2669
2670 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
2671
2672 ----
2673 l_Meta_Attribute_Rec.AttributeName := 'SerNumLovInstId';
2674 l_Meta_Attribute_Rec.Prompt := null;
2675 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2676 l_Meta_Attribute_Rec.IsSearcheable := 'F';
2677 l_Meta_Attribute_Rec.DataType := 'integer';
2678
2679 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
2680
2681 ----
2682 l_Meta_Attribute_Rec.AttributeName := 'SerNumLovDeptId';
2683 l_Meta_Attribute_Rec.Prompt := null;
2684 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2685 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2686 l_Meta_Attribute_Rec.DataType := 'integer';
2687
2688 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
2689
2690 ----
2691 l_Meta_Attribute_Rec.AttributeName := 'SerNumLovResId';
2692 l_Meta_Attribute_Rec.Prompt := null;
2693 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2694 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2695 l_Meta_Attribute_Rec.DataType := 'integer';
2696
2697 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
2698
2699 -- Populate output parameter
2700 x_lov_meta_output_rec.LovTitle := 'Search Serial Number';
2701 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
2702
2703 END getWOPositionMetaData;
2704
2705 ---------------------------------------------------------------------
2706 -- PROCEDURE
2707 -- getWOPositionResults
2708 --
2709 -- PURPOSE
2710 --
2711 -- PARAMETERS
2712 --
2713 -- NOTES
2714 ---------------------------------------------------------------------
2715 PROCEDURE getWOPositionResults(
2716 p_lov_input_rec IN LOV_Input_Rec_Type,
2717 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
2718 x_return_status OUT NOCOPY VARCHAR2,
2719 x_msg_count OUT NOCOPY NUMBER,
2720 x_msg_data OUT NOCOPY VARCHAR2)
2721 IS
2722
2723 -- Define local Variables
2724 L_API_VERSION CONSTANT NUMBER := 1.0;
2725 L_API_NAME CONSTANT VARCHAR2(30) := 'getWOPositionResults';
2726 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2727
2728 l_criteria_tbl LovCriteria_Tbl_Type;
2729 l_results_tbl LovResult_Tbl_Type;
2730 l_attributes_tbl LovResultAttribute_Tbl_Type;
2731
2732 i integer ;
2733
2734 -- Local Variables for the sql string.
2735 l_sql_string VARCHAR2(30000);
2736 l_bind_index NUMBER;
2737 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
2738 -- dynamic cursor
2739 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
2740
2741 --Local Variables for search results
2742 l_rownum NUMBER;
2743 l_serial_num bom_dept_res_instances.SERIAL_NUMBER%type;
2744 l_instance_id NUMBER;
2745 l_dept_id NUMBER;
2746 l_res_id NUMBER;
2747
2748 BEGIN
2749
2750 x_return_status := FND_API.G_RET_STS_SUCCESS;
2751
2752 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
2753 l_sql_string := 'SELECT * FROM ( SELECT DISTINCT Rownum RN, bdri.SERIAL_NUMBER,';
2754 l_sql_string := l_sql_string || ' bdri.instance_id, bdri.department_id, bdri.resource_id ';
2755 l_sql_string := l_sql_string || ' FROM bom_dept_res_instances BDRI ';
2756 l_sql_string := l_sql_string || ' WHERE bdri.SERIAL_NUMBER is not null ';
2757
2758 --Get Dynamic Search Criteria
2759 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
2760 l_bind_index := 1;
2761
2762 IF l_criteria_tbl.count > 0 THEN
2763 i:=l_criteria_tbl.first;
2764 LOOP
2765
2766 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovSerNum') THEN
2767 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2768 l_sql_string := l_sql_string || ' AND upper(bdri.SERIAL_NUMBER) like :SN ';
2769 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
2770 l_bind_index := l_bind_index + 1;
2771 END IF;
2772 END IF;
2773
2774 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovInstId') THEN
2775 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2776 l_sql_string := l_sql_string || ' AND bdri.instance_id like :INS_ID ';
2777 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
2778 l_bind_index := l_bind_index + 1;
2779 END IF;
2780 END IF;
2781
2782 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovResId') THEN
2783 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2784 l_sql_string := l_sql_string || ' AND bdri.resource_id like :RES_ID ';
2785 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
2786 l_bind_index := l_bind_index + 1;
2787 END IF;
2788 END IF;
2789
2790 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovDeptId') THEN
2791 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
2792 l_sql_string := l_sql_string || ' AND bdri.department_id like :DEPT_ID ';
2793 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
2794 l_bind_index := l_bind_index + 1;
2795 END IF;
2796 END IF;
2797
2798 EXIT WHEN i= l_criteria_tbl.last;
2799 i:= i+1;
2800 END LOOP;
2801 END IF;
2802
2803 -- SET START/END Row
2804 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
2805 --Max Row
2806 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows+1;
2807 l_bind_index := l_bind_index + 1;
2808
2809 l_sql_string := l_sql_string || ' Order By bdri.department_id,bdri.resource_id,bdri.SERIAL_NUMBER ) ';
2810 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
2811 --start row
2812 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
2813 l_bind_index := l_bind_index + 1;
2814 --end row
2815 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
2816 l_bind_index := l_bind_index + 1;
2817
2818 --open l_cur FOR l_sql_string;
2819 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
2820 (
2821 p_conditions_tbl => l_bindvar_tbl,
2822 p_sql_str => l_sql_string,
2823 p_x_csr => l_cur
2824 );
2825
2826 i:=1;
2827 LOOP
2828 FETCH l_cur INTO l_rownum,
2829 l_serial_num,
2830 l_instance_id,
2831 l_dept_id,
2832 l_res_id;
2833
2834
2835 EXIT WHEN l_cur%NOTFOUND;
2836
2837 --
2838 l_attributes_tbl(1).AttributeName := 'SerNumLovSerNum';
2839 l_attributes_tbl(1).AttributeValue := l_serial_num;
2840
2841 --
2842 l_attributes_tbl(2).AttributeName := 'SerNumLovInstId';
2843 l_attributes_tbl(2).AttributeValue := l_instance_id;
2844
2845 --
2846 l_attributes_tbl(3).AttributeName := 'SerNumLovDeptId';
2847 l_attributes_tbl(3).AttributeValue := l_dept_id;
2848
2849 --
2850 l_attributes_tbl(4).AttributeName := 'SerNumLovResId';
2851 l_attributes_tbl(4).AttributeValue := l_res_id;
2852
2853
2854 l_results_tbl(i) := l_attributes_tbl;
2855 i:=i+1;
2856
2857 END LOOP;
2858 CLOSE l_cur;
2859 -- Create Attributes Table
2860
2861 -- Populate output parameter
2862 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
2863 x_lov_result_output_rec.NumberOfRows := i-1;
2864 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
2865
2866 END getWOPositionResults;
2867
2868 ---------------------------------------------------------------------
2869 -- PROCEDURE
2870 -- getOnItemMetaData
2871 --
2872 -- PURPOSE
2873 --
2874 -- PARAMETERS
2875 --
2876 -- NOTES
2877 ---------------------------------------------------------------------
2878 PROCEDURE getOnItemMetaData(
2879 p_lov_input_rec IN LOV_Input_Rec_Type,
2880 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
2881 x_return_status OUT NOCOPY VARCHAR2,
2882 x_msg_count OUT NOCOPY NUMBER,
2883 x_msg_data OUT NOCOPY VARCHAR2)
2884 IS
2885
2886 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
2887 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
2888
2889 BEGIN
2890
2891 x_return_status := FND_API.G_RET_STS_SUCCESS;
2892
2893 -- Create Attributes Table
2894 l_Meta_Attribute_Rec.AttributeName := 'ItemNum';
2895 l_Meta_Attribute_Rec.Prompt := 'Item';
2896 l_Meta_Attribute_Rec.IsDisplayed := 'T';
2897 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2898 l_Meta_Attribute_Rec.DataType := 'string';
2899
2900 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
2901
2902 ----
2903 l_Meta_Attribute_Rec.AttributeName := 'Description';
2904 l_Meta_Attribute_Rec.Prompt := 'Description';
2905 l_Meta_Attribute_Rec.IsDisplayed := 'T';
2906 l_Meta_Attribute_Rec.IsSearcheable := 'F';
2907 l_Meta_Attribute_Rec.DataType := 'string';
2908
2909 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
2910
2911 ----
2912 l_Meta_Attribute_Rec.AttributeName := 'WorkOrderId';
2913 l_Meta_Attribute_Rec.Prompt := null;
2914 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2915 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2916 l_Meta_Attribute_Rec.DataType := 'integer';
2917
2918 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
2919
2920 ----
2921 l_Meta_Attribute_Rec.AttributeName := 'Position';
2922 l_Meta_Attribute_Rec.Prompt := null;
2923 l_Meta_Attribute_Rec.IsDisplayed := 'T';
2924 l_Meta_Attribute_Rec.IsSearcheable := 'F';
2925 l_Meta_Attribute_Rec.DataType := 'integer';
2926
2927 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
2928
2929 ----
2930 l_Meta_Attribute_Rec.AttributeName := 'PositionId';
2931 l_Meta_Attribute_Rec.Prompt := null;
2932 l_Meta_Attribute_Rec.IsDisplayed := 'F';
2933 l_Meta_Attribute_Rec.IsSearcheable := 'T';
2934 l_Meta_Attribute_Rec.DataType := 'integer';
2935
2936 l_Meta_Attribute_Tbl(4) := l_Meta_Attribute_Rec;
2937
2938 -- Populate output parameter
2939 x_lov_meta_output_rec.LovTitle := 'Search Item';
2940 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
2941
2942 END getOnItemMetaData;
2943
2944 ---------------------------------------------------------------------
2945 -- PROCEDURE
2946 -- getOnItemResults
2947 --
2948 -- PURPOSE
2949 --
2950 -- PARAMETERS
2951 --
2952 -- NOTES
2953 ---------------------------------------------------------------------
2954 PROCEDURE getOnItemResults(
2955 p_lov_input_rec IN LOV_Input_Rec_Type,
2956 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
2957 x_return_status OUT NOCOPY VARCHAR2,
2958 x_msg_count OUT NOCOPY NUMBER,
2959 x_msg_data OUT NOCOPY VARCHAR2)
2960 IS
2961
2962 -- Define local Variables
2963 L_API_VERSION CONSTANT NUMBER := 1.0;
2964 L_API_NAME CONSTANT VARCHAR2(30) := 'getOnItemResults';
2965 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2966
2967 l_criteria_tbl LovCriteria_Tbl_Type;
2968 l_results_tbl LovResult_Tbl_Type;
2969 l_attributes_tbl LovResultAttribute_Tbl_Type;
2970
2971 i integer ;
2972
2973 -- Local Variables for the sql string.
2974 l_sql_string VARCHAR2(30000);
2975 l_bind_index NUMBER;
2976 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
2977 -- dynamic cursor
2978 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
2979
2980 --Local Variables for search results
2981 l_rownum NUMBER;
2982 l_serial_num bom_dept_res_instances.SERIAL_NUMBER%type;
2983 l_instance_id NUMBER;
2984 l_dept_id NUMBER;
2985 l_res_id NUMBER;
2986
2987 BEGIN
2988
2989 x_return_status := FND_API.G_RET_STS_SUCCESS;
2990
2991 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
2992 l_sql_string := 'SELECT * FROM ( SELECT DISTINCT Rownum RN, bdri.SERIAL_NUMBER,';
2993 l_sql_string := l_sql_string || ' bdri.instance_id, bdri.department_id, bdri.resource_id ';
2994 l_sql_string := l_sql_string || ' FROM bom_dept_res_instances BDRI ';
2995 l_sql_string := l_sql_string || ' WHERE bdri.SERIAL_NUMBER is not null ';
2996
2997 --Get Dynamic Search Criteria
2998 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
2999 l_bind_index := 1;
3000
3001 IF l_criteria_tbl.count > 0 THEN
3002 i:=l_criteria_tbl.first;
3003 LOOP
3004
3005 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovSerNum') THEN
3006 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3007 l_sql_string := l_sql_string || ' AND upper(bdri.SERIAL_NUMBER) like :SN ';
3008 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
3009 l_bind_index := l_bind_index + 1;
3010 END IF;
3011 END IF;
3012
3013 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovInstId') THEN
3014 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3015 l_sql_string := l_sql_string || ' AND bdri.instance_id like :INS_ID ';
3016 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
3017 l_bind_index := l_bind_index + 1;
3018 END IF;
3019 END IF;
3020
3021 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovResId') THEN
3022 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3023 l_sql_string := l_sql_string || ' AND bdri.resource_id like :RES_ID ';
3024 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
3025 l_bind_index := l_bind_index + 1;
3026 END IF;
3027 END IF;
3028
3029 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerNumLovDeptId') THEN
3030 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3031 l_sql_string := l_sql_string || ' AND bdri.department_id like :DEPT_ID ';
3032 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
3033 l_bind_index := l_bind_index + 1;
3034 END IF;
3035 END IF;
3036
3037 EXIT WHEN i= l_criteria_tbl.last;
3038 i:= i+1;
3039 END LOOP;
3040 END IF;
3041
3042 -- SET START/END Row
3043 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
3044 --Max Row
3045 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows +1;
3046 l_bind_index := l_bind_index + 1;
3047
3048 l_sql_string := l_sql_string || ' Order By bdri.department_id,bdri.resource_id,bdri.SERIAL_NUMBER ) ';
3049 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
3050 --start row
3051 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow +1;
3052 l_bind_index := l_bind_index + 1;
3053 --end row
3054 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
3055 l_bind_index := l_bind_index + 1;
3056
3057 --open l_cur FOR l_sql_string;
3058 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
3059 (
3060 p_conditions_tbl => l_bindvar_tbl,
3061 p_sql_str => l_sql_string,
3062 p_x_csr => l_cur
3063 );
3064
3065 i:=1;
3066 LOOP
3067 FETCH l_cur INTO l_rownum,
3068 l_serial_num,
3069 l_instance_id,
3070 l_dept_id,
3071 l_res_id;
3072
3073
3074 EXIT WHEN l_cur%NOTFOUND;
3075
3076 --
3077 l_attributes_tbl(1).AttributeName := 'SerNumLovSerNum';
3078 l_attributes_tbl(1).AttributeValue := l_serial_num;
3079
3080 --
3081 l_attributes_tbl(2).AttributeName := 'SerNumLovInstId';
3082 l_attributes_tbl(2).AttributeValue := l_instance_id;
3083
3084 --
3085 l_attributes_tbl(3).AttributeName := 'SerNumLovDeptId';
3086 l_attributes_tbl(3).AttributeValue := l_dept_id;
3087
3088 --
3089 l_attributes_tbl(4).AttributeName := 'SerNumLovResId';
3090 l_attributes_tbl(4).AttributeValue := l_res_id;
3091
3092
3093 l_results_tbl(i) := l_attributes_tbl;
3094 i:=i+1;
3095
3096 END LOOP;
3097 CLOSE l_cur;
3098 -- Create Attributes Table
3099
3100 -- Populate output parameter
3101 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
3102 x_lov_result_output_rec.NumberOfRows := i-1;
3103 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
3104
3105 END getOnItemResults;
3106
3107 ---------------------------------------------------------------------
3108 -- PROCEDURE
3109 -- getConditionMetaData
3110 --
3111 -- PURPOSE
3112 --
3113 -- PARAMETERS
3114 --
3115 -- NOTES
3116 ---------------------------------------------------------------------
3117 PROCEDURE getConditionMetaData(
3118 p_lov_input_rec IN LOV_Input_Rec_Type,
3119 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
3120 x_return_status OUT NOCOPY VARCHAR2,
3121 x_msg_count OUT NOCOPY NUMBER,
3122 x_msg_data OUT NOCOPY VARCHAR2)
3123 IS
3124
3125 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
3126 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
3127
3128 BEGIN
3129
3130 x_return_status := FND_API.G_RET_STS_SUCCESS;
3131
3132 -- Create Attributes Table
3133 l_Meta_Attribute_Rec.AttributeName := 'Condition';
3134 l_Meta_Attribute_Rec.Prompt := 'Condition';
3135 l_Meta_Attribute_Rec.IsDisplayed := 'T';
3136 l_Meta_Attribute_Rec.IsSearcheable := 'T';
3137 l_Meta_Attribute_Rec.DataType := 'string';
3138
3139 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
3140
3141 ----
3142 l_Meta_Attribute_Rec.AttributeName := 'Description';
3143 l_Meta_Attribute_Rec.Prompt := 'Description';
3144 l_Meta_Attribute_Rec.IsDisplayed := 'T';
3145 l_Meta_Attribute_Rec.IsSearcheable := 'T';
3146 l_Meta_Attribute_Rec.DataType := 'string';
3147
3148 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
3149
3150 ----
3151 l_Meta_Attribute_Rec.AttributeName := 'ConditionId';
3152 l_Meta_Attribute_Rec.Prompt := null;
3153 l_Meta_Attribute_Rec.IsDisplayed := 'F';
3154 l_Meta_Attribute_Rec.IsSearcheable := 'T';
3155 l_Meta_Attribute_Rec.DataType := 'integer';
3156
3157 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
3158
3159 -- Populate output parameter
3160 x_lov_meta_output_rec.LovTitle := 'Search Condition';
3161 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
3162
3163 END getConditionMetaData;
3164
3165 ---------------------------------------------------------------------
3166 -- PROCEDURE
3167 -- getConditionResults
3168 --
3169 -- PURPOSE
3170 --
3171 -- PARAMETERS
3172 --
3173 -- NOTES
3174 ---------------------------------------------------------------------
3175 PROCEDURE getConditionResults(
3176 p_lov_input_rec IN LOV_Input_Rec_Type,
3177 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
3178 x_return_status OUT NOCOPY VARCHAR2,
3179 x_msg_count OUT NOCOPY NUMBER,
3180 x_msg_data OUT NOCOPY VARCHAR2)
3181 IS
3182
3183 -- Define local Variables
3184 L_API_VERSION CONSTANT NUMBER := 1.0;
3185 L_API_NAME CONSTANT VARCHAR2(30) := 'getConditionResults';
3186 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
3187
3188 l_criteria_tbl LovCriteria_Tbl_Type;
3189 l_results_tbl LovResult_Tbl_Type;
3190 l_attributes_tbl LovResultAttribute_Tbl_Type;
3191
3192 i integer ;
3193
3194 -- Local Variables for the sql string.
3195 l_sql_string VARCHAR2(30000);
3196 l_bind_index NUMBER;
3197 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
3198 -- dynamic cursor
3199 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
3200
3201 --Local Variables for search results
3202 l_rownum NUMBER;
3203 l_status_id NUMBER;
3204 l_status_code mtl_material_statuses.status_code%type;
3205 l_status_desc mtl_material_statuses.description%type;
3206
3207 BEGIN
3208
3209 x_return_status := FND_API.G_RET_STS_SUCCESS;
3210
3211 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
3212 l_sql_string := 'SELECT * FROM ( SELECT Rownum RN, status_id,';
3213 l_sql_string := l_sql_string || ' status_code, description ';
3214 l_sql_string := l_sql_string || ' FROM mtl_material_statuses ';
3215 l_sql_string := l_sql_string || ' WHERE enabled_flag = 1 ';
3216
3217 --Get Dynamic Search Criteria
3218 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
3219 l_bind_index := 1;
3220
3221 IF l_criteria_tbl.count > 0 THEN
3222 i:=l_criteria_tbl.first;
3223 LOOP
3224
3225 IF upper(l_criteria_tbl(i).AttributeName) = upper('Condition') THEN
3226 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3227 l_sql_string := l_sql_string || ' AND upper(status_code) like :CODE ';
3228 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
3229 l_bind_index := l_bind_index + 1;
3230 END IF;
3231 END IF;
3232
3233 IF upper(l_criteria_tbl(i).AttributeName) = upper('Description') THEN
3234 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3235 l_sql_string := l_sql_string || ' AND upper(description) like :DESC ';
3236 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
3237 l_bind_index := l_bind_index + 1;
3238 END IF;
3239 END IF;
3240
3241 IF upper(l_criteria_tbl(i).AttributeName) = upper('ConditionId') THEN
3242 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3243 l_sql_string := l_sql_string || ' AND status_id like :ID ';
3244 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
3245 l_bind_index := l_bind_index + 1;
3246 END IF;
3247 END IF;
3248
3249 EXIT WHEN i= l_criteria_tbl.last;
3250 i:= i+1;
3251 END LOOP;
3252 END IF;
3253
3254 -- SET START/END Row
3255 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
3256 --Max Row
3257 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows +1;
3258 l_bind_index := l_bind_index + 1;
3259
3260 l_sql_string := l_sql_string || ' Order By status_code ) ';
3261 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
3262 --start row
3263 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow +1;
3264 l_bind_index := l_bind_index + 1;
3265 --end row
3266 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
3267 l_bind_index := l_bind_index + 1;
3268
3269 --open l_cur FOR l_sql_string;
3270 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
3271 (
3272 p_conditions_tbl => l_bindvar_tbl,
3273 p_sql_str => l_sql_string,
3274 p_x_csr => l_cur
3275 );
3276
3277 i:=1;
3278 LOOP
3279 FETCH l_cur INTO l_rownum,
3280 l_status_id,
3281 l_status_code,
3282 l_status_desc;
3283
3284
3285 EXIT WHEN l_cur%NOTFOUND;
3286
3287 --
3288 l_attributes_tbl(1).AttributeName := 'Condition';
3289 l_attributes_tbl(1).AttributeValue := l_status_code;
3290
3291 --
3292 l_attributes_tbl(2).AttributeName := 'Description';
3293 l_attributes_tbl(2).AttributeValue := l_status_desc;
3294
3295 --
3296 l_attributes_tbl(3).AttributeName := 'ConditionId';
3297 l_attributes_tbl(3).AttributeValue := l_status_id;
3298
3299
3300 l_results_tbl(i) := l_attributes_tbl;
3301 i:=i+1;
3302
3303 END LOOP;
3304 CLOSE l_cur;
3305 -- Create Attributes Table
3306
3307 -- Populate output parameter
3308 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
3309 x_lov_result_output_rec.NumberOfRows := i-1;
3310 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
3311
3312 END getConditionResults;
3313
3314 ---------------------------------------------------------------------
3315 -- PROCEDURE
3316 -- getRemReasonMetaData
3317 --
3318 -- PURPOSE
3319 --
3320 -- PARAMETERS
3321 --
3322 -- NOTES
3323 ---------------------------------------------------------------------
3324 PROCEDURE getRemReasonMetaData(
3325 p_lov_input_rec IN LOV_Input_Rec_Type,
3326 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
3327 x_return_status OUT NOCOPY VARCHAR2,
3328 x_msg_count OUT NOCOPY NUMBER,
3329 x_msg_data OUT NOCOPY VARCHAR2)
3330 IS
3331
3332 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
3333 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
3334
3335 BEGIN
3336
3337 x_return_status := FND_API.G_RET_STS_SUCCESS;
3338
3339 -- Create Attributes Table
3340 l_Meta_Attribute_Rec.AttributeName := 'RemovalLovReason';
3341 l_Meta_Attribute_Rec.Prompt := 'Removal Reason';
3342 l_Meta_Attribute_Rec.IsDisplayed := 'T';
3343 l_Meta_Attribute_Rec.IsSearcheable := 'T';
3344 l_Meta_Attribute_Rec.DataType := 'string';
3345
3346 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
3347
3348 ----
3349 l_Meta_Attribute_Rec.AttributeName := 'RemovalLovDescription';
3350 l_Meta_Attribute_Rec.Prompt := 'Description';
3351 l_Meta_Attribute_Rec.IsDisplayed := 'T';
3352 l_Meta_Attribute_Rec.IsSearcheable := 'F';
3353 l_Meta_Attribute_Rec.DataType := 'string';
3354
3355 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
3356
3357 ----
3358 l_Meta_Attribute_Rec.AttributeName := 'RemovalLovReasonId';
3359 l_Meta_Attribute_Rec.Prompt := null;
3360 l_Meta_Attribute_Rec.IsDisplayed := 'F';
3361 l_Meta_Attribute_Rec.IsSearcheable := 'F';
3362 l_Meta_Attribute_Rec.DataType := 'integer';
3363
3364 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
3365
3366 -- Populate output parameter
3367 x_lov_meta_output_rec.LovTitle := 'Search Removal Reason';
3368 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
3369
3370 END getRemReasonMetaData;
3371
3372 ---------------------------------------------------------------------
3373 -- PROCEDURE
3374 -- getRemReasonResults
3375 --
3376 -- PURPOSE
3377 --
3378 -- PARAMETERS
3379 --
3380 -- NOTES
3381 ---------------------------------------------------------------------
3382 PROCEDURE getRemReasonResults(
3383 p_lov_input_rec IN LOV_Input_Rec_Type,
3384 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
3385 x_return_status OUT NOCOPY VARCHAR2,
3386 x_msg_count OUT NOCOPY NUMBER,
3387 x_msg_data OUT NOCOPY VARCHAR2)
3388 IS
3389
3390 -- Define local Variables
3391 L_API_VERSION CONSTANT NUMBER := 1.0;
3392 L_API_NAME CONSTANT VARCHAR2(30) := 'getRemReasonResults';
3393 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
3394
3395 l_criteria_tbl LovCriteria_Tbl_Type;
3396 l_results_tbl LovResult_Tbl_Type;
3397 l_attributes_tbl LovResultAttribute_Tbl_Type;
3398
3399 i integer ;
3400
3401 -- Local Variables for the sql string.
3402 l_sql_string VARCHAR2(30000);
3403 l_bind_index NUMBER;
3404 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
3405 -- dynamic cursor
3406 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
3407
3408 --Local Variables for search results
3409 l_rownum NUMBER;
3410 l_reason_name mtl_transaction_reasons.reason_name%type;
3411 l_reason_id NUMBER;
3412 l_reason_desc mtl_transaction_reasons.description%type;
3413
3414 BEGIN
3415
3416 x_return_status := FND_API.G_RET_STS_SUCCESS;
3417
3418 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
3419 l_sql_string := 'SELECT * FROM ( SELECT Rownum RN, reason_name,';
3420 l_sql_string := l_sql_string || ' reason_id, description ';
3421 l_sql_string := l_sql_string || ' FROM mtl_transaction_reasons ';
3422 l_sql_string := l_sql_string || ' WHERE nvl(disable_date,sysdate) >= sysdate ';
3423
3424 --Get Dynamic Search Criteria
3425 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
3426 l_bind_index := 1;
3427
3428 IF l_criteria_tbl.count > 0 THEN
3429 i:=l_criteria_tbl.first;
3430 LOOP
3431
3432 IF upper(l_criteria_tbl(i).AttributeName) = upper('RemovalLovReason') THEN
3433 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3434 l_sql_string := l_sql_string || ' AND upper(reason_name) like :NAME ';
3435 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
3436 l_bind_index := l_bind_index + 1;
3437 END IF;
3438 END IF;
3439
3440 IF upper(l_criteria_tbl(i).AttributeName) = upper('RemovalLovDescription') THEN
3441 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3442 l_sql_string := l_sql_string || ' AND upper(description) like :DESC ';
3443 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
3444 l_bind_index := l_bind_index + 1;
3445 END IF;
3446 END IF;
3447
3448 IF upper(l_criteria_tbl(i).AttributeName) = upper('RemovalLovReasonId') THEN
3449 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3450 l_sql_string := l_sql_string || ' AND reason_id like :ID ';
3451 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
3452 l_bind_index := l_bind_index + 1;
3453 END IF;
3454 END IF;
3455
3456 EXIT WHEN i= l_criteria_tbl.last;
3457 i:= i+1;
3458 END LOOP;
3459 END IF;
3460
3461 -- SET START/END Row
3462 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
3463 --Max Row
3464 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows+1;
3465 l_bind_index := l_bind_index + 1;
3466
3467 l_sql_string := l_sql_string || ' Order By reason_name ) ';
3468 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
3469 --start row
3470 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
3471 l_bind_index := l_bind_index + 1;
3472 --end row
3473 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
3474 l_bind_index := l_bind_index + 1;
3475
3476 --open l_cur FOR l_sql_string;
3477 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
3478 (
3479 p_conditions_tbl => l_bindvar_tbl,
3480 p_sql_str => l_sql_string,
3481 p_x_csr => l_cur
3482 );
3483
3484 i:=1;
3485 LOOP
3486 FETCH l_cur INTO l_rownum,
3487 l_reason_name,
3488 l_reason_id,
3489 l_reason_desc;
3490
3491
3492 EXIT WHEN l_cur%NOTFOUND;
3493
3494 --
3495 l_attributes_tbl(1).AttributeName := 'RemovalLovReason';
3496 l_attributes_tbl(1).AttributeValue := l_reason_name;
3497
3498 --
3499 l_attributes_tbl(2).AttributeName := 'RemovalLovDescription';
3500 l_attributes_tbl(2).AttributeValue := l_reason_desc;
3501
3502 --
3503 l_attributes_tbl(3).AttributeName := 'RemovalLovReasonId';
3504 l_attributes_tbl(3).AttributeValue := l_reason_id;
3505
3506
3507 l_results_tbl(i) := l_attributes_tbl;
3508 i:=i+1;
3509
3510 END LOOP;
3511 CLOSE l_cur;
3512 -- Create Attributes Table
3513
3514 -- Populate output parameter
3515 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
3516 x_lov_result_output_rec.NumberOfRows := i-1;
3517 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
3518
3519 END getRemReasonResults;
3520
3521 ---------------------------------------------------------------------
3522 -- PROCEDURE
3523 -- getRemCodeMetaData
3524 --
3525 -- PURPOSE
3526 --
3527 -- PARAMETERS
3528 --
3529 -- NOTES
3530 ---------------------------------------------------------------------
3531 PROCEDURE getRemCodeMetaData(
3532 p_lov_input_rec IN LOV_Input_Rec_Type,
3533 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
3534 x_return_status OUT NOCOPY VARCHAR2,
3535 x_msg_count OUT NOCOPY NUMBER,
3536 x_msg_data OUT NOCOPY VARCHAR2)
3537 IS
3538
3539 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
3540 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
3541
3542 BEGIN
3543
3544 x_return_status := FND_API.G_RET_STS_SUCCESS;
3545
3546 -- Create Attributes Table
3547 l_Meta_Attribute_Rec.AttributeName := 'RemovalLovCode';
3548 l_Meta_Attribute_Rec.Prompt := 'Removal Code';
3549 l_Meta_Attribute_Rec.IsDisplayed := 'T';
3550 l_Meta_Attribute_Rec.IsSearcheable := 'T';
3551 l_Meta_Attribute_Rec.DataType := 'string';
3552
3553 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
3554
3555 ----
3556 l_Meta_Attribute_Rec.AttributeName := 'RemovalLovMeaning';
3557 l_Meta_Attribute_Rec.Prompt := 'Meaning';
3558 l_Meta_Attribute_Rec.IsDisplayed := 'T';
3559 l_Meta_Attribute_Rec.IsSearcheable := 'T';
3560 l_Meta_Attribute_Rec.DataType := 'string';
3561
3562 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
3563
3564 -- Populate output parameter
3565 x_lov_meta_output_rec.LovTitle := 'Search Removal Code';
3566 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
3567
3568 END getRemCodeMetaData;
3569
3570 ---------------------------------------------------------------------
3571 -- PROCEDURE
3572 -- getRemCodeResults
3573 --
3574 -- PURPOSE
3575 --
3576 -- PARAMETERS
3577 --
3578 -- NOTES
3579 ---------------------------------------------------------------------
3580 PROCEDURE getRemCodeResults(
3581 p_lov_input_rec IN LOV_Input_Rec_Type,
3582 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
3583 x_return_status OUT NOCOPY VARCHAR2,
3584 x_msg_count OUT NOCOPY NUMBER,
3585 x_msg_data OUT NOCOPY VARCHAR2)
3586 IS
3587
3588 -- Define local Variables
3589 L_API_VERSION CONSTANT NUMBER := 1.0;
3590 L_API_NAME CONSTANT VARCHAR2(30) := 'getRemCodeResults';
3591 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
3592
3593 l_criteria_tbl LovCriteria_Tbl_Type;
3594 l_results_tbl LovResult_Tbl_Type;
3595 l_attributes_tbl LovResultAttribute_Tbl_Type;
3596
3597 i integer ;
3598
3599 -- Local Variables for the sql string.
3600 l_sql_string VARCHAR2(30000);
3601 l_bind_index NUMBER;
3602 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
3603 -- dynamic cursor
3604 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
3605
3606 --Local Variables for search results
3607 l_rownum NUMBER;
3608 l_code FND_LOOKUP_VALUES_VL.lookup_code%type;
3609 l_mean FND_LOOKUP_VALUES_VL.meaning%type;
3610
3611 BEGIN
3612
3613 x_return_status := FND_API.G_RET_STS_SUCCESS;
3614
3615 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
3616 l_sql_string := 'SELECT * FROM ( SELECT Rownum RN, meaning,';
3617 l_sql_string := l_sql_string || ' lookup_code ';
3618 l_sql_string := l_sql_string || ' FROM FND_LOOKUP_VALUES_VL ';
3619 l_sql_string := l_sql_string || ' WHERE lookup_type=''AHL_REMOVAL_CODE'' ';
3620 l_sql_string := l_sql_string || ' and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate) ';
3621
3622 --Get Dynamic Search Criteria
3623 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
3624 l_bind_index := 1;
3625
3626 IF l_criteria_tbl.count > 0 THEN
3627 i:=l_criteria_tbl.first;
3628 LOOP
3629
3630 IF upper(l_criteria_tbl(i).AttributeName) = upper('RemovalLovCode') THEN
3631 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3632 l_sql_string := l_sql_string || ' AND upper(lookup_code) like :CODE ';
3633 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
3634 l_bind_index := l_bind_index + 1;
3635 END IF;
3636 END IF;
3637
3638 IF upper(l_criteria_tbl(i).AttributeName) = upper('RemovalLovMeaning') THEN
3639 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3640 l_sql_string := l_sql_string || ' AND upper(meaning) like :MEAN ';
3641 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
3642 l_bind_index := l_bind_index + 1;
3643 END IF;
3644 END IF;
3645
3646 EXIT WHEN i= l_criteria_tbl.last;
3647 i:= i+1;
3648 END LOOP;
3649 END IF;
3650
3651 -- SET START/END Row
3652 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
3653 --Max Row
3654 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows+1;
3655 l_bind_index := l_bind_index + 1;
3656
3657 l_sql_string := l_sql_string || ' Order By meaning ) ';
3658 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
3659 --start row
3660 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
3661 l_bind_index := l_bind_index + 1;
3662 --end row
3663 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
3664 l_bind_index := l_bind_index + 1;
3665
3666 --open l_cur FOR l_sql_string;
3667 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
3668 (
3669 p_conditions_tbl => l_bindvar_tbl,
3670 p_sql_str => l_sql_string,
3671 p_x_csr => l_cur
3672 );
3673
3674 i:=1;
3675 LOOP
3676 FETCH l_cur INTO l_rownum,
3677 l_mean,
3678 l_code;
3679
3680
3681 EXIT WHEN l_cur%NOTFOUND;
3682
3683 --
3684 l_attributes_tbl(1).AttributeName := 'RemovalLovCode';
3685 l_attributes_tbl(1).AttributeValue := l_code;
3686
3687 --
3688 l_attributes_tbl(2).AttributeName := 'RemovalLovMeaning';
3689 l_attributes_tbl(2).AttributeValue := l_mean;
3690
3691 l_results_tbl(i) := l_attributes_tbl;
3692 i:=i+1;
3693
3694 END LOOP;
3695 CLOSE l_cur;
3696 -- Create Attributes Table
3697
3698 -- Populate output parameter
3699 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
3700 x_lov_result_output_rec.NumberOfRows := i-1;
3701 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
3702
3703 END getRemCodeResults;
3704
3705 ---------------------------------------------------------------------
3706 -- PROCEDURE
3707 -- getResolutionMetaData
3708 --
3709 -- PURPOSE
3710 --
3711 -- PARAMETERS
3712 --
3713 -- NOTES
3714 ---------------------------------------------------------------------
3715 PROCEDURE getResolutionMetaData(
3716 p_lov_input_rec IN LOV_Input_Rec_Type,
3717 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
3718 x_return_status OUT NOCOPY VARCHAR2,
3719 x_msg_count OUT NOCOPY NUMBER,
3720 x_msg_data OUT NOCOPY VARCHAR2)
3721 IS
3722
3723 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
3724 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
3725
3726 BEGIN
3727
3728 x_return_status := FND_API.G_RET_STS_SUCCESS;
3729
3730 -- Create Attributes Table
3731 l_Meta_Attribute_Rec.AttributeName := 'ResolutionLovCode';
3732 l_Meta_Attribute_Rec.Prompt := 'Resolution Code';
3733 l_Meta_Attribute_Rec.IsDisplayed := 'T';
3734 l_Meta_Attribute_Rec.IsSearcheable := 'T';
3735 l_Meta_Attribute_Rec.DataType := 'string';
3736
3737 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
3738
3739 ----
3740 l_Meta_Attribute_Rec.AttributeName := 'ResolutionLovMeaning';
3741 l_Meta_Attribute_Rec.Prompt := 'Name';
3742 l_Meta_Attribute_Rec.IsDisplayed := 'T';
3743 l_Meta_Attribute_Rec.IsSearcheable := 'T';
3744 l_Meta_Attribute_Rec.DataType := 'string';
3745
3746 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
3747
3748 -- Populate output parameter
3749 x_lov_meta_output_rec.LovTitle := 'Search Resolution Code';
3750 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
3751
3752 END getResolutionMetaData;
3753
3754 ---------------------------------------------------------------------
3755 -- PROCEDURE
3756 -- getResolutionResults
3757 --
3758 -- PURPOSE
3759 --
3760 -- PARAMETERS
3761 --
3762 -- NOTES
3763 ---------------------------------------------------------------------
3764 PROCEDURE getResolutionResults(
3765 p_lov_input_rec IN LOV_Input_Rec_Type,
3766 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
3767 x_return_status OUT NOCOPY VARCHAR2,
3768 x_msg_count OUT NOCOPY NUMBER,
3769 x_msg_data OUT NOCOPY VARCHAR2)
3770 IS
3771
3772 -- Define local Variables
3773 L_API_VERSION CONSTANT NUMBER := 1.0;
3774 L_API_NAME CONSTANT VARCHAR2(30) := 'getResolutionResults';
3775 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
3776
3777 l_criteria_tbl LovCriteria_Tbl_Type;
3778 l_results_tbl LovResult_Tbl_Type;
3779 l_attributes_tbl LovResultAttribute_Tbl_Type;
3780
3781 i integer ;
3782
3783 -- Local Variables for the sql string.
3784 l_sql_string VARCHAR2(30000);
3785 l_bind_index NUMBER;
3786 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
3787 -- dynamic cursor
3788 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
3789
3790 --Local Variables for search results
3791 l_rownum NUMBER;
3792 l_code FND_LOOKUP_VALUES_VL.lookup_code%type;
3793 l_mean FND_LOOKUP_VALUES_VL.meaning%type;
3794
3795 BEGIN
3796
3797 x_return_status := FND_API.G_RET_STS_SUCCESS;
3798
3799 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
3800 l_sql_string := 'SELECT * FROM ( SELECT Rownum RN, meaning,';
3801 l_sql_string := l_sql_string || ' lookup_code ';
3802 l_sql_string := l_sql_string || ' FROM FND_LOOKUP_VALUES_VL ';
3803 l_sql_string := l_sql_string || ' WHERE lookup_type=''REQUEST_RESOLUTION_CODE'' ';
3804 l_sql_string := l_sql_string || ' and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate) ';
3805
3806 --Get Dynamic Search Criteria
3807 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
3808 l_bind_index := 1;
3809
3810 IF l_criteria_tbl.count > 0 THEN
3811 i:=l_criteria_tbl.first;
3812 LOOP
3813
3814 IF upper(l_criteria_tbl(i).AttributeName) = upper('ResolutionLovCode') THEN
3815 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3816 l_sql_string := l_sql_string || ' AND upper(lookup_code) like :CODE ';
3817 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
3818 l_bind_index := l_bind_index + 1;
3819 END IF;
3820 END IF;
3821
3822 IF upper(l_criteria_tbl(i).AttributeName) = upper('ResolutionLovMeaning') THEN
3823 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
3824 l_sql_string := l_sql_string || ' AND upper(meaning) like :MEAN ';
3825 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
3826 l_bind_index := l_bind_index + 1;
3827 END IF;
3828 END IF;
3829
3830 EXIT WHEN i= l_criteria_tbl.last;
3831 i:= i+1;
3832 END LOOP;
3833 END IF;
3834
3835 -- SET START/END Row
3836 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
3837 --Max Row
3838 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows+1;
3839 l_bind_index := l_bind_index + 1;
3840
3841 l_sql_string := l_sql_string || ' Order By meaning ) ';
3842 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
3843 --start row
3844 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
3845 l_bind_index := l_bind_index + 1;
3846 --end row
3847 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
3848 l_bind_index := l_bind_index + 1;
3849
3850 --open l_cur FOR l_sql_string;
3851 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
3852 (
3853 p_conditions_tbl => l_bindvar_tbl,
3854 p_sql_str => l_sql_string,
3855 p_x_csr => l_cur
3856 );
3857
3858 i:=1;
3859 LOOP
3860 FETCH l_cur INTO l_rownum,
3861 l_mean,
3862 l_code;
3863
3864
3865 EXIT WHEN l_cur%NOTFOUND;
3866
3867 --
3868 l_attributes_tbl(1).AttributeName := 'ResolutionLovCode';
3869 l_attributes_tbl(1).AttributeValue := l_code;
3870
3871 --
3872 l_attributes_tbl(2).AttributeName := 'ResolutionLovMeaning';
3873 l_attributes_tbl(2).AttributeValue := l_mean;
3874
3875 l_results_tbl(i) := l_attributes_tbl;
3876 i:=i+1;
3877
3878 END LOOP;
3879 CLOSE l_cur;
3880 -- Create Attributes Table
3881
3882 -- Populate output parameter
3883 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
3884 x_lov_result_output_rec.NumberOfRows := i-1;
3885 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
3886
3887 END getResolutionResults;
3888
3889 ---------------------------------------------------------------------
3890 -- PROCEDURE
3891 -- getProblemMetaData
3892 --
3893 -- PURPOSE
3894 --
3895 -- PARAMETERS
3896 --
3897 -- NOTES
3898 ---------------------------------------------------------------------
3899 PROCEDURE getProblemMetaData(
3900 p_lov_input_rec IN LOV_Input_Rec_Type,
3901 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
3902 x_return_status OUT NOCOPY VARCHAR2,
3903 x_msg_count OUT NOCOPY NUMBER,
3904 x_msg_data OUT NOCOPY VARCHAR2)
3905 IS
3906
3907 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
3908 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
3909
3910 BEGIN
3911
3912 x_return_status := FND_API.G_RET_STS_SUCCESS;
3913
3914 -- Create Attributes Table
3915 l_Meta_Attribute_Rec.AttributeName := 'ProblemLovCode';
3916 l_Meta_Attribute_Rec.Prompt := 'Problem Code';
3917 l_Meta_Attribute_Rec.IsDisplayed := 'T';
3918 l_Meta_Attribute_Rec.IsSearcheable := 'T';
3919 l_Meta_Attribute_Rec.DataType := 'string';
3920
3921 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
3922
3923 ----
3924 l_Meta_Attribute_Rec.AttributeName := 'ProblemLovMeaning';
3925 l_Meta_Attribute_Rec.Prompt := 'Meaning';
3926 l_Meta_Attribute_Rec.IsDisplayed := 'T';
3927 l_Meta_Attribute_Rec.IsSearcheable := 'F';
3928 l_Meta_Attribute_Rec.DataType := 'string';
3929
3930 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
3931
3932 -- Populate output parameter
3933 x_lov_meta_output_rec.LovTitle := 'Search Problem Code';
3934 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
3935
3936 END getProblemMetaData;
3937
3938 ---------------------------------------------------------------------
3939 -- PROCEDURE
3940 -- getProblemResults
3941 --
3942 -- PURPOSE
3943 --
3944 -- PARAMETERS
3945 --
3946 -- NOTES
3947 ---------------------------------------------------------------------
3948 PROCEDURE getProblemResults(
3949 p_lov_input_rec IN LOV_Input_Rec_Type,
3950 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
3951 x_return_status OUT NOCOPY VARCHAR2,
3952 x_msg_count OUT NOCOPY NUMBER,
3953 x_msg_data OUT NOCOPY VARCHAR2)
3954 IS
3955
3956 -- Define local Variables
3957 L_API_VERSION CONSTANT NUMBER := 1.0;
3958 L_API_NAME CONSTANT VARCHAR2(30) := 'getProblemResults';
3959 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
3960
3961 l_criteria_tbl LovCriteria_Tbl_Type;
3962 l_results_tbl LovResult_Tbl_Type;
3963 l_attributes_tbl LovResultAttribute_Tbl_Type;
3964
3965 i integer ;
3966
3967 -- Local Variables for the sql string.
3968 l_sql_string VARCHAR2(30000);
3969 l_bind_index NUMBER;
3970 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
3971 -- dynamic cursor
3972 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
3973
3974 --Local Variables for search results
3975 l_rownum NUMBER;
3976 l_code FND_LOOKUP_VALUES_VL.lookup_code%type;
3977 l_mean FND_LOOKUP_VALUES_VL.meaning%type;
3978
3979 BEGIN
3980
3981 x_return_status := FND_API.G_RET_STS_SUCCESS;
3982
3983 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
3984 l_sql_string := 'SELECT * FROM ( SELECT Rownum RN, description,';
3985 l_sql_string := l_sql_string || ' lookup_code ';
3986 l_sql_string := l_sql_string || ' FROM FND_LOOKUP_VALUES_VL fl ';
3987 l_sql_string := l_sql_string || ' WHERE lookup_type=''REQUEST_PROBLEM_CODE'' ';
3988 l_sql_string := l_sql_string || ' and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate) ';
3989 l_sql_string := l_sql_string || ' and enabled_flag = ''Y'' ';
3990 l_sql_string := l_sql_string || ' and ((NOT EXISTS (SELECT 1 FROM CS_SR_PROB_CODE_MAPPING_V WHERE INCIDENT_TYPE_ID = FND_PROFILE.Value(''AHL_PRD_SR_TYPE'') ';
3991 l_sql_string := l_sql_string || ' AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)))) ';
3992 l_sql_string := l_sql_string || ' OR (EXISTS (SELECT 1 FROM CS_SR_PROB_CODE_MAPPING_V MAP WHERE MAP.INCIDENT_TYPE_ID = FND_PROFILE.Value(''AHL_PRD_SR_TYPE'') ';
3993 l_sql_string := l_sql_string || ' AND MAP.INVENTORY_ITEM_ID IS NULL AND MAP.PROBLEM_CODE = FL.LOOKUP_CODE AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(MAP.START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(MAP.END_DATE_ACTIVE,SYSDATE))))) ';
3994
3995 --Get Dynamic Search Criteria
3996 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
3997 l_bind_index := 1;
3998
3999 IF l_criteria_tbl.count > 0 THEN
4000 i:=l_criteria_tbl.first;
4001 LOOP
4002
4003 IF upper(l_criteria_tbl(i).AttributeName) = upper('ProblemLovCode') THEN
4004 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4005 l_sql_string := l_sql_string || ' AND upper(lookup_code) like :CODE ';
4006 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4007 l_bind_index := l_bind_index + 1;
4008 END IF;
4009 END IF;
4010
4011 IF upper(l_criteria_tbl(i).AttributeName) = upper('ProblemLovMeaning') THEN
4012 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4013 l_sql_string := l_sql_string || ' AND upper(description) like :MEAN ';
4014 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4015 l_bind_index := l_bind_index + 1;
4016 END IF;
4017 END IF;
4018
4019 EXIT WHEN i= l_criteria_tbl.last;
4020 i:= i+1;
4021 END LOOP;
4022 END IF;
4023
4024 -- SET START/END Row
4025 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
4026 --Max Row
4027 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows +1;
4028 l_bind_index := l_bind_index + 1;
4029
4030 l_sql_string := l_sql_string || ' Order By meaning ) ';
4031 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
4032 --start row
4033 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
4034 l_bind_index := l_bind_index + 1;
4035 --end row
4036 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
4037 l_bind_index := l_bind_index + 1;
4038
4039 --open l_cur FOR l_sql_string;
4040 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
4041 (
4042 p_conditions_tbl => l_bindvar_tbl,
4043 p_sql_str => l_sql_string,
4044 p_x_csr => l_cur
4045 );
4046
4047 i:=1;
4048 LOOP
4049 FETCH l_cur INTO l_rownum,
4050 l_mean,
4051 l_code;
4052
4053
4054 EXIT WHEN l_cur%NOTFOUND;
4055
4056 --
4057 l_attributes_tbl(1).AttributeName := 'ProblemLovCode';
4058 l_attributes_tbl(1).AttributeValue := l_code;
4059
4060 --
4061 l_attributes_tbl(2).AttributeName := 'ProblemLovMeaning';
4062 l_attributes_tbl(2).AttributeValue := l_mean;
4063
4064 l_results_tbl(i) := l_attributes_tbl;
4065 i:=i+1;
4066
4067 END LOOP;
4068 CLOSE l_cur;
4069 -- Create Attributes Table
4070
4071 -- Populate output parameter
4072 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
4073 x_lov_result_output_rec.NumberOfRows := i-1;
4074 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
4075
4076 END getProblemResults;
4077
4078 ---------------------------------------------------------------------
4079 -- PROCEDURE
4080 -- getSeverityMetaData
4081 --
4082 -- PURPOSE
4083 --
4084 -- PARAMETERS
4085 --
4086 -- NOTES
4087 ---------------------------------------------------------------------
4088 PROCEDURE getSeverityMetaData(
4089 p_lov_input_rec IN LOV_Input_Rec_Type,
4090 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
4091 x_return_status OUT NOCOPY VARCHAR2,
4092 x_msg_count OUT NOCOPY NUMBER,
4093 x_msg_data OUT NOCOPY VARCHAR2)
4094 IS
4095
4096 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
4097 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
4098
4099 BEGIN
4100
4101 x_return_status := FND_API.G_RET_STS_SUCCESS;
4102
4103 -- Create Attributes Table
4104 l_Meta_Attribute_Rec.AttributeName := 'SeverityLovName';
4105 l_Meta_Attribute_Rec.Prompt := 'Severity';
4106 l_Meta_Attribute_Rec.IsDisplayed := 'T';
4107 l_Meta_Attribute_Rec.IsSearcheable := 'T';
4108 l_Meta_Attribute_Rec.DataType := 'string';
4109
4110 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
4111
4112 ----
4113 l_Meta_Attribute_Rec.AttributeName := 'SeverityLovDescription';
4114 l_Meta_Attribute_Rec.Prompt := 'Description';
4115 l_Meta_Attribute_Rec.IsDisplayed := 'T';
4116 l_Meta_Attribute_Rec.IsSearcheable := 'F';
4117 l_Meta_Attribute_Rec.DataType := 'string';
4118
4119 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
4120
4121 ----
4122 l_Meta_Attribute_Rec.AttributeName := 'SeverityLovSeverityId';
4123 l_Meta_Attribute_Rec.Prompt := null;
4124 l_Meta_Attribute_Rec.IsDisplayed := 'F';
4125 l_Meta_Attribute_Rec.IsSearcheable := 'F';
4126 l_Meta_Attribute_Rec.DataType := 'integer';
4127
4128 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
4129
4130 -- Populate output parameter
4131 x_lov_meta_output_rec.LovTitle := 'Search Severity';
4132 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
4133
4134 END getSeverityMetaData;
4135
4136 ---------------------------------------------------------------------
4137 -- PROCEDURE
4138 -- getSeverityResults
4139 --
4140 -- PURPOSE
4141 --
4142 -- PARAMETERS
4143 --
4144 -- NOTES
4145 ---------------------------------------------------------------------
4146 PROCEDURE getSeverityResults(
4147 p_lov_input_rec IN LOV_Input_Rec_Type,
4148 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
4149 x_return_status OUT NOCOPY VARCHAR2,
4150 x_msg_count OUT NOCOPY NUMBER,
4151 x_msg_data OUT NOCOPY VARCHAR2)
4152 IS
4153
4154 -- Define local Variables
4155 L_API_VERSION CONSTANT NUMBER := 1.0;
4156 L_API_NAME CONSTANT VARCHAR2(30) := 'getSeverityResults';
4157 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
4158
4159 l_criteria_tbl LovCriteria_Tbl_Type;
4160 l_results_tbl LovResult_Tbl_Type;
4161 l_attributes_tbl LovResultAttribute_Tbl_Type;
4162
4163 i integer ;
4164
4165 -- Local Variables for the sql string.
4166 l_sql_string VARCHAR2(30000);
4167 l_bind_index NUMBER;
4168 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
4169 -- dynamic cursor
4170 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
4171
4172 --Local Variables for search results
4173 l_rownum NUMBER;
4174 l_name cs_incident_severities_vl.name%type;
4175 l_id NUMBER;
4176 l_desc cs_incident_severities_vl.description%type;
4177
4178 BEGIN
4179
4180 x_return_status := FND_API.G_RET_STS_SUCCESS;
4181
4182 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
4183 l_sql_string := 'SELECT * FROM ( SELECT Rownum RN, name,';
4184 l_sql_string := l_sql_string || ' incident_severity_id, description ';
4185 l_sql_string := l_sql_string || ' FROM cs_incident_severities_vl ';
4186 l_sql_string := l_sql_string || ' WHERE trunc(sysdate) between trunc(nvl(start_date_active,sysdate)) and trunc(nvl(end_date_active,sysdate)) ';
4187 l_sql_string := l_sql_string || ' and incident_subtype = ''INC'' ';
4188 l_sql_string := l_sql_string || ' and name is not null ';
4189
4190 --Get Dynamic Search Criteria
4191 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
4192 l_bind_index := 1;
4193
4194 IF l_criteria_tbl.count > 0 THEN
4195 i:=l_criteria_tbl.first;
4196 LOOP
4197
4198 IF upper(l_criteria_tbl(i).AttributeName) = upper('SeverityLovName') THEN
4199 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4200 l_sql_string := l_sql_string || ' AND upper(name) like :NAME ';
4201 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4202 l_bind_index := l_bind_index + 1;
4203 END IF;
4204 END IF;
4205
4206 IF upper(l_criteria_tbl(i).AttributeName) = upper('SeverityLovDescription') THEN
4207 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4208 l_sql_string := l_sql_string || ' AND upper(description) like :DESC ';
4209 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4210 l_bind_index := l_bind_index + 1;
4211 END IF;
4212 END IF;
4213
4214 IF upper(l_criteria_tbl(i).AttributeName) = upper('SeverityLovSeverityId') THEN
4215 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4216 l_sql_string := l_sql_string || ' AND incident_severity_id like :ID ';
4217 l_bindvar_tbl(l_bind_index) := l_criteria_tbl(i).AttributeValue;
4218 l_bind_index := l_bind_index + 1;
4219 END IF;
4220 END IF;
4221
4222 EXIT WHEN i= l_criteria_tbl.last;
4223 i:= i+1;
4224 END LOOP;
4225 END IF;
4226
4227 -- SET START/END Row
4228 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
4229 --Max Row
4230 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows+1;
4231 l_bind_index := l_bind_index + 1;
4232
4233 l_sql_string := l_sql_string || ' Order By name ) ';
4234 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
4235 --start row
4236 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow+1;
4237 l_bind_index := l_bind_index + 1;
4238 --end row
4239 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
4240 l_bind_index := l_bind_index + 1;
4241
4242 --open l_cur FOR l_sql_string;
4243 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
4244 (
4245 p_conditions_tbl => l_bindvar_tbl,
4246 p_sql_str => l_sql_string,
4247 p_x_csr => l_cur
4248 );
4249
4250 i:=1;
4251 LOOP
4252 FETCH l_cur INTO l_rownum,
4253 l_name,
4254 l_id,
4255 l_desc;
4256
4257
4258 EXIT WHEN l_cur%NOTFOUND;
4259
4260 --
4261 l_attributes_tbl(1).AttributeName := 'SeverityLovName';
4262 l_attributes_tbl(1).AttributeValue := l_name;
4263
4264 --
4265 l_attributes_tbl(2).AttributeName := 'SeverityLovDescription';
4266 l_attributes_tbl(2).AttributeValue := l_desc;
4267
4268 --
4269 l_attributes_tbl(3).AttributeName := 'SeverityLovSeverityId';
4270 l_attributes_tbl(3).AttributeValue := l_id;
4271
4272
4273 l_results_tbl(i) := l_attributes_tbl;
4274 i:=i+1;
4275
4276 END LOOP;
4277 CLOSE l_cur;
4278 -- Create Attributes Table
4279
4280 -- Populate output parameter
4281 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
4282 x_lov_result_output_rec.NumberOfRows := i-1;
4283 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
4284
4285 END getSeverityResults;
4286
4287 ---------------------------------------------------------------------
4288 -- PROCEDURE
4289 -- getItemMetaData
4290 --
4291 -- PURPOSE
4292 --
4293 -- PARAMETERS
4294 --
4295 -- NOTES
4296 ---------------------------------------------------------------------
4297 PROCEDURE getItemMetaData(
4298 p_lov_input_rec IN LOV_Input_Rec_Type,
4299 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
4300 x_return_status OUT NOCOPY VARCHAR2,
4301 x_msg_count OUT NOCOPY NUMBER,
4302 x_msg_data OUT NOCOPY VARCHAR2)
4303 IS
4304
4305 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
4306 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
4307
4308 BEGIN
4309
4310 x_return_status := FND_API.G_RET_STS_SUCCESS;
4311
4312 -- Create Attributes Table
4313 l_Meta_Attribute_Rec.AttributeName := 'InstanceNum';
4314 l_Meta_Attribute_Rec.Prompt := 'Instance Number';
4315 l_Meta_Attribute_Rec.IsDisplayed := 'T';
4316 l_Meta_Attribute_Rec.IsSearcheable := 'T';
4317 l_Meta_Attribute_Rec.DataType := 'string';
4318
4319 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
4320
4321 ----
4322 l_Meta_Attribute_Rec.AttributeName := 'SerialNum';
4323 l_Meta_Attribute_Rec.Prompt := 'Serial Number';
4324 l_Meta_Attribute_Rec.IsDisplayed := 'T';
4325 l_Meta_Attribute_Rec.IsSearcheable := 'T';
4326 l_Meta_Attribute_Rec.DataType := 'string';
4327
4328 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
4329
4330 ----
4331 l_Meta_Attribute_Rec.AttributeName := 'ItemNum';
4332 l_Meta_Attribute_Rec.Prompt := 'Item';
4333 l_Meta_Attribute_Rec.IsDisplayed := 'T';
4334 l_Meta_Attribute_Rec.IsSearcheable := 'T';
4335 l_Meta_Attribute_Rec.DataType := 'string';
4336
4337 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
4338
4339 ----
4340 l_Meta_Attribute_Rec.AttributeName := 'LotNum';
4341 l_Meta_Attribute_Rec.Prompt := 'lot Number';
4342 l_Meta_Attribute_Rec.IsDisplayed := 'T';
4343 l_Meta_Attribute_Rec.IsSearcheable := 'F';
4344 l_Meta_Attribute_Rec.DataType := 'string';
4345
4346 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
4347
4348 -- Populate output parameter
4349 x_lov_meta_output_rec.LovTitle := 'Search Item';
4350 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
4351
4352 END getItemMetaData;
4353
4354 ---------------------------------------------------------------------
4355 -- PROCEDURE
4356 -- getItemResults
4357 --
4358 -- PURPOSE
4359 --
4360 -- PARAMETERS
4361 --
4362 -- NOTES
4363 ---------------------------------------------------------------------
4364 PROCEDURE getItemResults(
4365 p_lov_input_rec IN LOV_Input_Rec_Type,
4366 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
4367 x_return_status OUT NOCOPY VARCHAR2,
4368 x_msg_count OUT NOCOPY NUMBER,
4369 x_msg_data OUT NOCOPY VARCHAR2)
4370 IS
4371
4372 -- Define local Variables
4373 L_API_VERSION CONSTANT NUMBER := 1.0;
4374 L_API_NAME CONSTANT VARCHAR2(30) := 'getItemResults';
4375 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
4376
4377 l_criteria_tbl LovCriteria_Tbl_Type;
4378 l_results_tbl LovResult_Tbl_Type;
4379 l_attributes_tbl LovResultAttribute_Tbl_Type;
4380
4381 i integer ;
4382
4383 -- Local Variables for the sql string.
4384 l_sql_string VARCHAR2(30000);
4385 l_bind_index NUMBER;
4386 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
4387 -- dynamic cursor
4388 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
4389
4390 --Local Variables for search results
4391 l_rownum NUMBER;
4392 l_instance csi_item_instances.instance_number%type;
4393 l_lot csi_item_instances.lot_number%type;
4394 l_serial csi_item_instances.serial_number%type;
4395 l_item mtl_system_items_kfv.concatenated_segments%type;
4396
4397 BEGIN
4398
4399 x_return_status := FND_API.G_RET_STS_SUCCESS;
4400
4401 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS
4402 l_sql_string := 'SELECT * FROM ( SELECT Rownum RN, csi1.instance_number, csi1.lot_number,';
4403 l_sql_string := l_sql_string || ' csi1.serial_number, ahl1.concatenated_segments ';
4404 l_sql_string := l_sql_string || ' FROM csi_item_instances csi1,mtl_system_items_kfv ahl1 ';
4405 l_sql_string := l_sql_string || ' WHERE csi1.INV_MASTER_ORGANIZATION_ID=ahl1.organization_id ';
4406 l_sql_string := l_sql_string || ' and csi1.inventory_item_id=ahl1.inventory_item_id ';
4407 l_sql_string := l_sql_string || ' and csi1.INV_MASTER_ORGANIZATION_ID IN (SELECT master_organization_id ';
4408 l_sql_string := l_sql_string || ' FROM org_organization_definitions org, mtl_parameters mp WHERE org.organization_id = mp.organization_id AND NVL(operating_unit,mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ';
4409
4410 --Get Dynamic Search Criteria
4411 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
4412 l_bind_index := 1;
4413
4414 IF l_criteria_tbl.count > 0 THEN
4415 i:=l_criteria_tbl.first;
4416 LOOP
4417
4418 IF upper(l_criteria_tbl(i).AttributeName) = upper('InstanceNum') THEN
4419 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4420 l_sql_string := l_sql_string || ' AND upper(csi1.instance_number) like :INST ';
4421 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4422 l_bind_index := l_bind_index + 1;
4423 END IF;
4424 END IF;
4425
4426 IF upper(l_criteria_tbl(i).AttributeName) = upper('SerialNum') THEN
4427 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4428 l_sql_string := l_sql_string || ' AND upper(csi1.serial_number) like :SN ';
4429 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4430 l_bind_index := l_bind_index + 1;
4431 END IF;
4432 END IF;
4433
4434 IF upper(l_criteria_tbl(i).AttributeName) = upper('ItemNum') THEN
4435 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4436 l_sql_string := l_sql_string || ' AND upper(ahl1.concatenated_segments) like :ITEM ';
4437 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4438 l_bind_index := l_bind_index + 1;
4439 END IF;
4440 END IF;
4441
4442 IF upper(l_criteria_tbl(i).AttributeName) = upper('LotNum') THEN
4443 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4444 l_sql_string := l_sql_string || ' AND upper(csi1.lot_number) like :LOT ';
4445 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4446 l_bind_index := l_bind_index + 1;
4447 END IF;
4448 END IF;
4449
4450 EXIT WHEN i= l_criteria_tbl.last;
4451 i:= i+1;
4452 END LOOP;
4453 END IF;
4454
4455 -- SET START/END Row
4456 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
4457 --Max Row
4458 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows +1;
4459 l_bind_index := l_bind_index + 1;
4460
4461 l_sql_string := l_sql_string || ' Order By ahl1.concatenated_segments,csi1.serial_number ) ';
4462 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
4463 --start row
4464 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow +1;
4465 l_bind_index := l_bind_index + 1;
4466 --end row
4467 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
4468 l_bind_index := l_bind_index + 1;
4469
4470 --open l_cur FOR l_sql_string;
4471 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
4472 (
4473 p_conditions_tbl => l_bindvar_tbl,
4474 p_sql_str => l_sql_string,
4475 p_x_csr => l_cur
4476 );
4477
4478 i:=1;
4479 LOOP
4480 FETCH l_cur INTO l_rownum,
4481 l_instance,
4482 l_lot,
4483 l_serial,
4484 l_item;
4485
4486
4487 EXIT WHEN l_cur%NOTFOUND;
4488
4489 --
4490 l_attributes_tbl(1).AttributeName := 'InstanceNum';
4491 l_attributes_tbl(1).AttributeValue := l_instance;
4492
4493 --
4494 l_attributes_tbl(2).AttributeName := 'SerialNum';
4495 l_attributes_tbl(2).AttributeValue := l_serial;
4496
4497 --
4498 l_attributes_tbl(3).AttributeName := 'ItemNum';
4499 l_attributes_tbl(3).AttributeValue := l_item;
4500
4501 --
4502 l_attributes_tbl(4).AttributeName := 'LotNum';
4503 l_attributes_tbl(4).AttributeValue := l_lot;
4504
4505 l_results_tbl(i) := l_attributes_tbl;
4506 i:=i+1;
4507
4508 END LOOP;
4509 CLOSE l_cur;
4510 -- Create Attributes Table
4511
4512 -- Populate output parameter
4513 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
4514 x_lov_result_output_rec.NumberOfRows := i-1;
4515 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
4516
4517 END getItemResults;
4518
4519 PROCEDURE getReqItemMetaData(
4520 p_lov_input_rec IN LOV_Input_Rec_Type,
4521 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
4522 x_return_status OUT NOCOPY VARCHAR2,
4523 x_msg_count OUT NOCOPY NUMBER,
4524 x_msg_data OUT NOCOPY VARCHAR2)
4525 IS
4526
4527 l_Meta_Attribute_Rec LovMetaAttribute_Rec_Type;
4528 l_Meta_Attribute_Tbl LovMetaAttribute_Tbl_Type;
4529
4530 BEGIN
4531
4532 x_return_status := FND_API.G_RET_STS_SUCCESS;
4533
4534 -- Create Attributes Table
4535 l_Meta_Attribute_Rec.AttributeName := 'ItemNum';
4536 l_Meta_Attribute_Rec.Prompt := 'Item';
4537 l_Meta_Attribute_Rec.IsDisplayed := 'T';
4538 l_Meta_Attribute_Rec.IsSearcheable := 'T';
4539 l_Meta_Attribute_Rec.DataType := 'string';
4540
4541 l_Meta_Attribute_Tbl(0) := l_Meta_Attribute_Rec;
4542
4543 ----
4544 l_Meta_Attribute_Rec.AttributeName := 'Description';
4545 l_Meta_Attribute_Rec.Prompt := 'Description';
4546 l_Meta_Attribute_Rec.IsDisplayed := 'T';
4547 l_Meta_Attribute_Rec.IsSearcheable := 'F';
4548 l_Meta_Attribute_Rec.DataType := 'string';
4549
4550 l_Meta_Attribute_Tbl(1) := l_Meta_Attribute_Rec;
4551
4552 ----
4553 l_Meta_Attribute_Rec.AttributeName := 'UOM';
4554 l_Meta_Attribute_Rec.Prompt := 'UOM';
4555 l_Meta_Attribute_Rec.IsDisplayed := 'T';
4556 l_Meta_Attribute_Rec.IsSearcheable := 'F';
4557 l_Meta_Attribute_Rec.DataType := 'string';
4558
4559 l_Meta_Attribute_Tbl(2) := l_Meta_Attribute_Rec;
4560
4561 ----
4562 l_Meta_Attribute_Rec.AttributeName := 'InventoryItemId';
4563 l_Meta_Attribute_Rec.Prompt := '';
4564 l_Meta_Attribute_Rec.IsDisplayed := 'F';
4565 l_Meta_Attribute_Rec.IsSearcheable := 'F';
4566 l_Meta_Attribute_Rec.DataType := 'integer';
4567
4568 l_Meta_Attribute_Tbl(3) := l_Meta_Attribute_Rec;
4569
4570 ----
4571 l_Meta_Attribute_Rec.AttributeName := 'WorkorderId';
4572 l_Meta_Attribute_Rec.Prompt := '';
4573 l_Meta_Attribute_Rec.IsDisplayed := 'F';
4574 l_Meta_Attribute_Rec.IsSearcheable := 'T';
4575 l_Meta_Attribute_Rec.DataType := 'integer';
4576
4577 l_Meta_Attribute_Tbl(4) := l_Meta_Attribute_Rec;
4578
4579 -- Populate output parameter
4580 x_lov_meta_output_rec.LovTitle := 'Search Item';
4581 x_lov_meta_output_rec.LovMetaAttributeTbl := l_Meta_Attribute_Tbl;
4582
4583 END getReqItemMetaData;
4584
4585 ---------------------------------------------------------------------
4586 -- PROCEDURE
4587 -- getItemResults
4588 --
4589 -- PURPOSE
4590 --
4591 -- PARAMETERS
4592 --
4593 -- NOTES
4594 ---------------------------------------------------------------------
4595 PROCEDURE getReqItemResults(
4596 p_lov_input_rec IN LOV_Input_Rec_Type,
4597 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
4598 x_return_status OUT NOCOPY VARCHAR2,
4599 x_msg_count OUT NOCOPY NUMBER,
4600 x_msg_data OUT NOCOPY VARCHAR2)
4601 IS
4602
4603 -- Define local Variables
4604 L_API_VERSION CONSTANT NUMBER := 1.0;
4605 L_API_NAME CONSTANT VARCHAR2(30) := 'getReqItemResults';
4606 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
4607
4608 l_criteria_tbl LovCriteria_Tbl_Type;
4609 l_results_tbl LovResult_Tbl_Type;
4610 l_attributes_tbl LovResultAttribute_Tbl_Type;
4611
4612 i integer ;
4613
4614 -- Local Variables for the sql string.
4615 l_sql_string VARCHAR2(30000);
4616 l_sql_string1 VARCHAR2(30000);
4617 l_sql_string2 VARCHAR2(30000);
4618 l_bind_index NUMBER;
4619 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
4620 -- dynamic cursor
4621 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
4622
4623 --Local Variables for search results
4624 l_rownum NUMBER;
4625 l_inv_item_id NUMBER;
4626 l_wo_id NUMBER;
4627 l_uom mtl_system_items_kfv.primary_unit_of_measure%type;
4628 l_desc mtl_system_items_kfv.description%type;
4629 l_item mtl_system_items_kfv.concatenated_segments%type;
4630 l_wo_id_input BOOLEAN;
4631
4632 BEGIN
4633
4634 x_return_status := FND_API.G_RET_STS_SUCCESS;
4635 l_wo_id_input := FALSE;
4636
4637 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS when wo_id is not null
4638 l_sql_string1 := 'SELECT * FROM ( SELECT Rownum RN, mtl.concatenated_segments , mtl.description , mtl.primary_unit_of_measure, WO.workorder_id, mtl.inventory_item_id ';
4639 l_sql_string1 := l_sql_string1 || ' from mtl_system_items_kfv mtl,mtl_parameters mtlp, ahl_workorders wo, ahl_visits_b VST ';
4640 l_sql_string1 := l_sql_string1 || ' WHERE mtl.organization_id = mtlp.organization_id ';
4641 l_sql_string1 := l_sql_string1 || ' and mtlp.eam_enabled_flag = ''Y'' and mtl.organization_id = VST.organization_id ';
4642 l_sql_string1 := l_sql_string1 || ' and VST.visit_id = WO.visit_id ';
4643
4644 -- SELECT CLAUSE AND BASIC WHERE CONDITIONS when wo id is null
4645 l_sql_string2 := 'SELECT * FROM ( SELECT Rownum RN, mtl.concatenated_segments , mtl.description , mtl.primary_unit_of_measure, to_number(NULL), mtl.inventory_item_id ';
4646 l_sql_string2 := l_sql_string2 || ' from mtl_system_items_kfv mtl,mtl_parameters mtlp ';
4647 l_sql_string2 := l_sql_string2 || ' WHERE mtl.organization_id = mtlp.organization_id ';
4648 l_sql_string2 := l_sql_string2 || ' and mtlp.eam_enabled_flag = ''Y'' and mtl.organization_id IN (SELECT master_organization_id ';
4649 l_sql_string2 := l_sql_string2 || ' FROM org_organization_definitions org, mtl_parameters mp WHERE org.organization_id = mp.organization_id AND NVL(operating_unit,mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ';
4650
4651 --Get Dynamic Search Criteria
4652 l_criteria_tbl :=p_lov_input_rec.LovCriteriaTbl;
4653 l_bind_index := 1;
4654
4655 IF l_criteria_tbl.count > 0 THEN
4656 i:=l_criteria_tbl.first;
4657 LOOP
4658 IF upper(l_criteria_tbl(i).AttributeName) = upper('ItemNum') THEN
4659 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4660 l_sql_string := l_sql_string || ' AND upper(mtl.concatenated_segments) like :ITEM ';
4661 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4662 l_bind_index := l_bind_index + 1;
4663 END IF;
4664 ELSIF upper(l_criteria_tbl(i).AttributeName) = upper('Description') THEN
4665 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4666 l_sql_string := l_sql_string || ' AND upper(mtl.description) like :DESC ';
4667 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4668 l_bind_index := l_bind_index + 1;
4669 END IF;
4670 ELSIF upper(l_criteria_tbl(i).AttributeName) = upper('InventoryItemId') THEN
4671 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4672 l_sql_string := l_sql_string || ' AND mtl.inventory_item_id like :INVID ';
4673 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4674 l_bind_index := l_bind_index + 1;
4675 END IF;
4676 ELSIF upper(l_criteria_tbl(i).AttributeName) = upper('WorkorderId') THEN
4677 IF l_criteria_tbl(i).AttributeValue is not NULL THEN
4678 l_sql_string := l_sql_string || ' AND WO.workorder_id like :WOID ';
4679 l_bindvar_tbl(l_bind_index) := upper(l_criteria_tbl(i).AttributeValue);
4680 l_bind_index := l_bind_index + 1;
4681 l_wo_id_input := TRUE;
4682 END IF;
4683 END IF;
4684
4685 EXIT WHEN i= l_criteria_tbl.last;
4686 i:= i+1;
4687 END LOOP;
4688 END IF;
4689 IF(l_wo_id_input)THEN
4690 l_sql_string := l_sql_string1 || l_sql_string;
4691 ELSE
4692 l_sql_string := l_sql_string2 || l_sql_string;
4693 END IF;
4694 -- SET START/END Row
4695 l_sql_string := l_sql_string || ' and rownum < :MAX_ROW ';
4696 --Max Row
4697 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows +1;
4698 l_bind_index := l_bind_index + 1;
4699
4700 l_sql_string := l_sql_string || ' Order By mtl.concatenated_segments ) ';
4701 l_sql_string := l_sql_string || ' WHERE RN BETWEEN :START_ROW AND :END_ROW ';
4702 --start row
4703 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow +1;
4704 l_bind_index := l_bind_index + 1;
4705 --end row
4706 l_bindvar_tbl(l_bind_index) := p_lov_input_rec.StartRow + p_lov_input_rec.NumberOfRows;
4707 l_bind_index := l_bind_index + 1;
4708
4709 --open l_cur FOR l_sql_string;
4710 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
4711 (
4712 p_conditions_tbl => l_bindvar_tbl,
4713 p_sql_str => l_sql_string,
4714 p_x_csr => l_cur
4715 );
4716
4717 i:=1;
4718 LOOP
4719 FETCH l_cur INTO l_rownum,
4720 l_item,
4721 l_desc,
4722 l_uom,
4723 l_wo_id,
4724 l_inv_item_id;
4725
4726
4727 EXIT WHEN l_cur%NOTFOUND;
4728
4729 --
4730 l_attributes_tbl(1).AttributeName := 'ItemNum';
4731 l_attributes_tbl(1).AttributeValue := l_item;
4732 --
4733 l_attributes_tbl(2).AttributeName := 'Description';
4734 l_attributes_tbl(2).AttributeValue := l_desc;
4735
4736 --
4737 l_attributes_tbl(3).AttributeName := 'UOM';
4738 l_attributes_tbl(3).AttributeValue := l_uom;
4739
4740 --
4741 l_attributes_tbl(4).AttributeName := 'WorkorderId';
4742 l_attributes_tbl(4).AttributeValue := l_wo_id;
4743
4744 l_attributes_tbl(5).AttributeName := 'InventoryItemId';
4745 l_attributes_tbl(5).AttributeValue := l_inv_item_id;
4746
4747
4748 l_results_tbl(i) := l_attributes_tbl;
4749 i:=i+1;
4750
4751 END LOOP;
4752 CLOSE l_cur;
4753 -- Create Attributes Table
4754
4755 -- Populate output parameter
4756 x_lov_result_output_rec.StartRow := p_lov_input_rec.StartRow;
4757 x_lov_result_output_rec.NumberOfRows := i-1;
4758 x_lov_result_output_rec.LovResultTbl := l_results_tbl;
4759
4760 END getReqItemResults;
4761 ---------------------------------------------------------------------
4762 -- PROCEDURE
4763 -- getLOVMetaData
4764 --
4765 -- PURPOSE
4766 --
4767 -- PARAMETERS
4768 --
4769 -- NOTES
4770 ---------------------------------------------------------------------
4771 PROCEDURE getLOVMetaData(
4772 p_lov_input_rec IN LOV_Input_Rec_Type,
4773 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
4774 x_return_status OUT NOCOPY VARCHAR2,
4775 x_msg_count OUT NOCOPY NUMBER,
4776 x_msg_data OUT NOCOPY VARCHAR2)
4777 IS
4778 BEGIN
4779
4780 x_return_status := FND_API.G_RET_STS_SUCCESS;
4781
4782 CASE upper(p_lov_input_rec.lovID)
4783
4784 WHEN 'AHL_PRD_VISITNUM_LOV' THEN
4785
4786 getVisitNumberMetaData(
4787 p_lov_input_rec => p_lov_input_rec,
4788 x_lov_meta_output_rec => x_lov_meta_output_rec,
4789 x_return_status => x_return_status,
4790 x_msg_count => x_msg_count,
4791 x_msg_data => x_msg_data );
4792
4793 WHEN 'AHL_UNIT_LOV' THEN
4794
4795 getUnitMetaData(
4796 p_lov_input_rec => p_lov_input_rec,
4797 x_lov_meta_output_rec => x_lov_meta_output_rec,
4798 x_return_status => x_return_status,
4799 x_msg_count => x_msg_count,
4800 x_msg_data => x_msg_data );
4801
4802 WHEN 'AHL_PRD_EMP_LOV' THEN
4803
4804 getEmployeeMetaData(
4805 p_lov_input_rec => p_lov_input_rec,
4806 x_lov_meta_output_rec => x_lov_meta_output_rec,
4807 x_return_status => x_return_status,
4808 x_msg_count => x_msg_count,
4809 x_msg_data => x_msg_data );
4810
4811 WHEN 'AHL_PRD_EMPNAME_LOV' THEN
4812
4813 getEmpNameMetaData(
4814 p_lov_input_rec => p_lov_input_rec,
4815 x_lov_meta_output_rec => x_lov_meta_output_rec,
4816 x_return_status => x_return_status,
4817 x_msg_count => x_msg_count,
4818 x_msg_data => x_msg_data );
4819
4820 WHEN 'AHL_PRD_WOSTATUS_LOV' THEN
4821
4822 getWOStatusMetaData(
4823 p_lov_input_rec => p_lov_input_rec,
4824 x_lov_meta_output_rec => x_lov_meta_output_rec,
4825 x_return_status => x_return_status,
4826 x_msg_count => x_msg_count,
4827 x_msg_data => x_msg_data );
4828
4829 WHEN 'AHL_PRD_OPERSEQ_LOV' THEN
4830
4831 getOperSeqMetaData(
4832 p_lov_input_rec => p_lov_input_rec,
4833 x_lov_meta_output_rec => x_lov_meta_output_rec,
4834 x_return_status => x_return_status,
4835 x_msg_count => x_msg_count,
4836 x_msg_data => x_msg_data );
4837
4838 WHEN 'AHL_PRD_RESCODE_LOV' THEN
4839
4840 getResCodeMetaData(
4841 p_lov_input_rec => p_lov_input_rec,
4842 x_lov_meta_output_rec => x_lov_meta_output_rec,
4843 x_return_status => x_return_status,
4844 x_msg_count => x_msg_count,
4845 x_msg_data => x_msg_data );
4846
4847 WHEN 'AHL_PRD_RESSERIALNUM_LOV' THEN
4848
4849 getSerialNumMetaData(
4850 p_lov_input_rec => p_lov_input_rec,
4851 x_lov_meta_output_rec => x_lov_meta_output_rec,
4852 x_return_status => x_return_status,
4853 x_msg_count => x_msg_count,
4854 x_msg_data => x_msg_data );
4855
4856 WHEN 'AHL_PRD_ATACODE_LOV' THEN
4857
4858 getATACodeMetaData(
4859 p_lov_input_rec => p_lov_input_rec,
4860 x_lov_meta_output_rec => x_lov_meta_output_rec,
4861 x_return_status => x_return_status,
4862 x_msg_count => x_msg_count,
4863 x_msg_data => x_msg_data );
4864 WHEN 'AHL_PRD_POSITION_LOV' THEN
4865
4866 getPositionMetaData(
4867 p_lov_input_rec => p_lov_input_rec,
4868 x_lov_meta_output_rec => x_lov_meta_output_rec,
4869 x_return_status => x_return_status,
4870 x_msg_count => x_msg_count,
4871 x_msg_data => x_msg_data );
4872
4873 WHEN 'AHL_PRD_WOPOSITION_LOV' THEN
4874
4875 getWOPositionMetaData(
4876 p_lov_input_rec => p_lov_input_rec,
4877 x_lov_meta_output_rec => x_lov_meta_output_rec,
4878 x_return_status => x_return_status,
4879 x_msg_count => x_msg_count,
4880 x_msg_data => x_msg_data );
4881
4882 WHEN 'AHL_PRD_ONITEM_LOV' THEN
4883
4884 getOnItemMetaData(
4885 p_lov_input_rec => p_lov_input_rec,
4886 x_lov_meta_output_rec => x_lov_meta_output_rec,
4887 x_return_status => x_return_status,
4888 x_msg_count => x_msg_count,
4889 x_msg_data => x_msg_data );
4890
4891 WHEN 'AHL_PRD_CONDITION_LOV' THEN
4892
4893 getConditionMetaData(
4894 p_lov_input_rec => p_lov_input_rec,
4895 x_lov_meta_output_rec => x_lov_meta_output_rec,
4896 x_return_status => x_return_status,
4897 x_msg_count => x_msg_count,
4898 x_msg_data => x_msg_data );
4899
4900 WHEN 'AHL_PRD_REM_REASON_LOV' THEN
4901
4902 getRemReasonMetaData(
4903 p_lov_input_rec => p_lov_input_rec,
4904 x_lov_meta_output_rec => x_lov_meta_output_rec,
4905 x_return_status => x_return_status,
4906 x_msg_count => x_msg_count,
4907 x_msg_data => x_msg_data );
4908
4909 WHEN 'AHL_PRD_REMOVAL_LOV' THEN
4910
4911 getRemCodeMetaData(
4912 p_lov_input_rec => p_lov_input_rec,
4913 x_lov_meta_output_rec => x_lov_meta_output_rec,
4914 x_return_status => x_return_status,
4915 x_msg_count => x_msg_count,
4916 x_msg_data => x_msg_data );
4917
4918 WHEN 'AHL_PRD_RESOLUTION_LOV' THEN
4919
4920 getResolutionMetaData(
4921 p_lov_input_rec => p_lov_input_rec,
4922 x_lov_meta_output_rec => x_lov_meta_output_rec,
4923 x_return_status => x_return_status,
4924 x_msg_count => x_msg_count,
4925 x_msg_data => x_msg_data );
4926
4927 WHEN 'AHL_PRD_PROBLEM_LOV' THEN
4928
4929 getProblemMetaData(
4930 p_lov_input_rec => p_lov_input_rec,
4931 x_lov_meta_output_rec => x_lov_meta_output_rec,
4932 x_return_status => x_return_status,
4933 x_msg_count => x_msg_count,
4934 x_msg_data => x_msg_data );
4935
4936 WHEN 'AHL_PRD_SEVERITY_LOV' THEN
4937
4938 getSeverityMetaData(
4939 p_lov_input_rec => p_lov_input_rec,
4940 x_lov_meta_output_rec => x_lov_meta_output_rec,
4941 x_return_status => x_return_status,
4942 x_msg_count => x_msg_count,
4943 x_msg_data => x_msg_data );
4944
4945 WHEN 'AHL_PRD_ITEM_LOV' THEN
4946
4947 getItemMetaData(
4948 p_lov_input_rec => p_lov_input_rec,
4949 x_lov_meta_output_rec => x_lov_meta_output_rec,
4950 x_return_status => x_return_status,
4951 x_msg_count => x_msg_count,
4952 x_msg_data => x_msg_data );
4953 WHEN 'AHL_PRD_REQ_MTL_ITEM_LOV' THEN
4954
4955 getReqItemMetaData(
4956 p_lov_input_rec => p_lov_input_rec,
4957 x_lov_meta_output_rec => x_lov_meta_output_rec,
4958 x_return_status => x_return_status,
4959 x_msg_count => x_msg_count,
4960 x_msg_data => x_msg_data );
4961
4962 ELSE
4963 --throw exception;
4964 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_LOV_NOT_FOUND');
4965 FND_MSG_PUB.ADD;
4966 RAISE FND_API.G_EXC_ERROR;
4967 END CASE;
4968
4969 END getLOVMetaData;
4970
4971 ---------------------------------------------------------------------
4972 -- PROCEDURE
4973 -- getLOVResults
4974 --
4975 -- PURPOSE
4976 --
4977 -- PARAMETERS
4978 --
4979 -- NOTES
4980 ---------------------------------------------------------------------
4981 PROCEDURE getLOVResults(
4982 p_lov_input_rec IN LOV_Input_Rec_Type,
4983 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
4984 x_return_status OUT NOCOPY VARCHAR2,
4985 x_msg_count OUT NOCOPY NUMBER,
4986 x_msg_data OUT NOCOPY VARCHAR2)
4987 IS
4988 BEGIN
4989
4990 x_return_status := FND_API.G_RET_STS_SUCCESS;
4991
4992 CASE upper(p_lov_input_rec.lovID)
4993
4994 WHEN 'AHL_PRD_VISITNUM_LOV' THEN
4995
4996 getVisitNumberResults(
4997 p_lov_input_rec => p_lov_input_rec,
4998 x_lov_result_output_rec => x_lov_result_output_rec,
4999 x_return_status => x_return_status,
5000 x_msg_count => x_msg_count,
5001 x_msg_data => x_msg_data );
5002
5003 WHEN 'AHL_UNIT_LOV' THEN
5004
5005 getUnitResults(
5006 p_lov_input_rec => p_lov_input_rec,
5007 x_lov_result_output_rec => x_lov_result_output_rec,
5008 x_return_status => x_return_status,
5009 x_msg_count => x_msg_count,
5010 x_msg_data => x_msg_data );
5011
5012 WHEN 'AHL_PRD_EMP_LOV' THEN
5013
5014 getEmployeeResults(
5015 p_lov_input_rec => p_lov_input_rec,
5016 x_lov_result_output_rec => x_lov_result_output_rec,
5017 x_return_status => x_return_status,
5018 x_msg_count => x_msg_count,
5019 x_msg_data => x_msg_data );
5020
5021 WHEN 'AHL_PRD_EMPNAME_LOV' THEN
5022
5023 getEmpNameResults(
5024 p_lov_input_rec => p_lov_input_rec,
5025 x_lov_result_output_rec => x_lov_result_output_rec,
5026 x_return_status => x_return_status,
5027 x_msg_count => x_msg_count,
5028 x_msg_data => x_msg_data );
5029
5030 WHEN 'AHL_PRD_WOSTATUS_LOV' THEN
5031
5032 getWOStatusResults(
5033 p_lov_input_rec => p_lov_input_rec,
5034 x_lov_result_output_rec => x_lov_result_output_rec,
5035 x_return_status => x_return_status,
5036 x_msg_count => x_msg_count,
5037 x_msg_data => x_msg_data );
5038
5039 WHEN 'AHL_PRD_OPERSEQ_LOV' THEN
5040
5041 getOperSeqResults(
5042 p_lov_input_rec => p_lov_input_rec,
5043 x_lov_result_output_rec => x_lov_result_output_rec,
5044 x_return_status => x_return_status,
5045 x_msg_count => x_msg_count,
5046 x_msg_data => x_msg_data );
5047
5048 WHEN 'AHL_PRD_RESCODE_LOV' THEN
5049
5050 getResCodeResults(
5051 p_lov_input_rec => p_lov_input_rec,
5052 x_lov_result_output_rec => x_lov_result_output_rec,
5053 x_return_status => x_return_status,
5054 x_msg_count => x_msg_count,
5055 x_msg_data => x_msg_data );
5056
5057 WHEN 'AHL_PRD_RESSERIALNUM_LOV' THEN
5058
5059 getSerialNumResults(
5060 p_lov_input_rec => p_lov_input_rec,
5061 x_lov_result_output_rec => x_lov_result_output_rec,
5062 x_return_status => x_return_status,
5063 x_msg_count => x_msg_count,
5064 x_msg_data => x_msg_data );
5065
5066 WHEN 'AHL_PRD_ATACODE_LOV' THEN
5067
5068 getATACodeResults(
5069 p_lov_input_rec => p_lov_input_rec,
5070 x_lov_result_output_rec => x_lov_result_output_rec,
5071 x_return_status => x_return_status,
5072 x_msg_count => x_msg_count,
5073 x_msg_data => x_msg_data );
5074 WHEN 'AHL_PRD_POSITION_LOV' THEN
5075 getPositionResults(
5076 p_lov_input_rec => p_lov_input_rec,
5077 x_lov_result_output_rec => x_lov_result_output_rec,
5078 x_return_status => x_return_status,
5079 x_msg_count => x_msg_count,
5080 x_msg_data => x_msg_data );
5081
5082 WHEN 'AHL_PRD_WOPOSITION_LOV' THEN
5083
5084 getWOPositionResults(
5085 p_lov_input_rec => p_lov_input_rec,
5086 x_lov_result_output_rec => x_lov_result_output_rec,
5087 x_return_status => x_return_status,
5088 x_msg_count => x_msg_count,
5089 x_msg_data => x_msg_data );
5090
5091 WHEN 'AHL_PRD_ONITEM_LOV' THEN
5092
5093 getOnItemResults(
5094 p_lov_input_rec => p_lov_input_rec,
5095 x_lov_result_output_rec => x_lov_result_output_rec,
5096 x_return_status => x_return_status,
5097 x_msg_count => x_msg_count,
5098 x_msg_data => x_msg_data );
5099
5100 WHEN 'AHL_PRD_CONDITION_LOV' THEN
5101
5102 getConditionResults(
5103 p_lov_input_rec => p_lov_input_rec,
5104 x_lov_result_output_rec => x_lov_result_output_rec,
5105 x_return_status => x_return_status,
5106 x_msg_count => x_msg_count,
5107 x_msg_data => x_msg_data );
5108
5109 WHEN 'AHL_PRD_REM_REASON_LOV' THEN
5110
5111 getRemReasonResults(
5112 p_lov_input_rec => p_lov_input_rec,
5113 x_lov_result_output_rec => x_lov_result_output_rec,
5114 x_return_status => x_return_status,
5115 x_msg_count => x_msg_count,
5116 x_msg_data => x_msg_data );
5117
5118 WHEN 'AHL_PRD_REMOVAL_LOV' THEN
5119
5120 getRemCodeResults(
5121 p_lov_input_rec => p_lov_input_rec,
5122 x_lov_result_output_rec => x_lov_result_output_rec,
5123 x_return_status => x_return_status,
5124 x_msg_count => x_msg_count,
5125 x_msg_data => x_msg_data );
5126
5127 WHEN 'AHL_PRD_RESOLUTION_LOV' THEN
5128
5129 getResolutionResults(
5130 p_lov_input_rec => p_lov_input_rec,
5131 x_lov_result_output_rec => x_lov_result_output_rec,
5132 x_return_status => x_return_status,
5133 x_msg_count => x_msg_count,
5134 x_msg_data => x_msg_data );
5135
5136 WHEN 'AHL_PRD_PROBLEM_LOV' THEN
5137
5138 getProblemResults(
5139 p_lov_input_rec => p_lov_input_rec,
5140 x_lov_result_output_rec => x_lov_result_output_rec,
5141 x_return_status => x_return_status,
5142 x_msg_count => x_msg_count,
5143 x_msg_data => x_msg_data );
5144
5145 WHEN 'AHL_PRD_SEVERITY_LOV' THEN
5146
5147 getSeverityResults(
5148 p_lov_input_rec => p_lov_input_rec,
5149 x_lov_result_output_rec => x_lov_result_output_rec,
5150 x_return_status => x_return_status,
5151 x_msg_count => x_msg_count,
5152 x_msg_data => x_msg_data );
5153
5154 WHEN 'AHL_PRD_ITEM_LOV' THEN
5155
5156 getItemResults(
5157 p_lov_input_rec => p_lov_input_rec,
5158 x_lov_result_output_rec => x_lov_result_output_rec,
5159 x_return_status => x_return_status,
5160 x_msg_count => x_msg_count,
5161 x_msg_data => x_msg_data );
5162 WHEN 'AHL_PRD_REQ_MTL_ITEM_LOV' THEN
5163 NULL;
5164 getReqItemResults(
5165 p_lov_input_rec => p_lov_input_rec,
5166 x_lov_result_output_rec => x_lov_result_output_rec,
5167 x_return_status => x_return_status,
5168 x_msg_count => x_msg_count,
5169 x_msg_data => x_msg_data );
5170
5171 ELSE
5172 --throw exception;
5173 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_LOV_NOT_FOUND');
5174 FND_MSG_PUB.ADD;
5175 RAISE FND_API.G_EXC_ERROR;
5176
5177 END CASE;
5178
5179 END getLOVResults;
5180
5181 ---------------------------------------------------------------------
5182 -- PROCEDURE
5183 -- Call_LOV_Services
5184 --
5185 -- PURPOSE
5186 --
5187 -- PARAMETERS
5188 --
5189 -- NOTES
5190 ---------------------------------------------------------------------
5191
5192 PROCEDURE Call_LOV_Services (
5193 p_api_version IN NUMBER :=1.0,
5194 p_init_msg_list IN VARCHAR2 :=Fnd_Api.g_false,
5195 p_commit IN VARCHAR2 :=Fnd_Api.g_false,
5196 p_validation_level IN NUMBER :=Fnd_Api.g_valid_level_full,
5197 p_module_type IN VARCHAR2 :=null,
5198 p_userid IN VARCHAR2 := NULL,
5199 p_lov_input_rec IN LOV_Input_Rec_Type,
5200 x_lov_result_output_rec OUT NOCOPY LovOutput_Rec_Type,
5201 x_lov_meta_output_rec OUT NOCOPY LovMetaData_Rec_Type,
5202 x_return_status OUT NOCOPY VARCHAR2,
5203 x_msg_count OUT NOCOPY NUMBER,
5204 x_msg_data OUT NOCOPY VARCHAR2 )
5205 IS
5206 l_api_version CONSTANT NUMBER := 1.0;
5207 l_api_name CONSTANT VARCHAR2(30) := 'Call_LOV_Services';
5208 BEGIN
5209
5210 -- Initialize environment
5211
5212 --fnd_global.apps_initialize(1003259, 54506, 867);
5213 --mo_global.init('AHL');
5214 x_return_status := init_user_and_role(p_userid);
5215
5216 -- Standard call to check for call compatibility
5217 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
5218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5219 END IF;
5220
5221 IF FND_API.To_Boolean(p_init_msg_list) THEN
5222 FND_MSG_PUB.Initialize;
5223 END IF;
5224
5225 -- Initialize output variables
5226 x_lov_meta_output_rec.LovMetaAttributeTbl(0).AttributeName := null;
5227 x_lov_result_output_rec.LovResultTbl(0)(0).AttributeName := null;
5228
5229 x_return_status := FND_API.G_RET_STS_SUCCESS;
5230
5231 IF p_lov_input_rec.getMetaData is not null and
5232 upper(p_lov_input_rec.getMetaData) = 'T' THEN
5233
5234 getLOVMetaData(
5235 p_lov_input_rec => p_lov_input_rec,
5236 x_lov_meta_output_rec => x_lov_meta_output_rec,
5237 x_return_status => x_return_status,
5238 x_msg_count => x_msg_count,
5239 x_msg_data => x_msg_data );
5240 END IF;
5241 IF p_lov_input_rec.getResults is not null and
5242 upper(p_lov_input_rec.getResults) = 'T' THEN
5243
5244 getLOVResults(
5245 p_lov_input_rec => p_lov_input_rec,
5246 x_lov_result_output_rec => x_lov_result_output_rec,
5247 x_return_status => x_return_status,
5248 x_msg_count => x_msg_count,
5249 x_msg_data => x_msg_data );
5250
5251 END IF;
5252 IF(x_lov_result_output_rec.NumberOfRows IS NULL OR x_lov_result_output_rec.NumberOfRows < 1)THEN
5253 x_lov_result_output_rec.LovResultTbl(0)(0).AttributeName := NULL;
5254 END IF;
5255 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
5256 RAISE FND_API.G_EXC_ERROR;
5257 END IF;
5258 EXCEPTION
5259 WHEN FND_API.G_EXC_ERROR THEN
5260 x_return_status := FND_API.G_RET_STS_ERROR;
5261 x_msg_count := FND_MSG_PUB.count_msg;
5262 x_msg_data := AHL_PRD_WO_PUB.GET_MSG_DATA(x_msg_count);
5263 /*FND_MSG_PUB.count_and_get( p_count => x_msg_count,
5264 p_data => x_msg_data,
5265 p_encoded => fnd_api.g_false);*/
5266
5267
5268
5269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5271 x_msg_count := FND_MSG_PUB.count_msg;
5272 x_msg_data := AHL_PRD_WO_PUB.GET_MSG_DATA(x_msg_count);
5273 /*FND_MSG_PUB.count_and_get( p_count => x_msg_count,
5274 p_data => x_msg_data,
5275 p_encoded => fnd_api.g_false);*/
5276
5277
5278 WHEN OTHERS THEN
5279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5280 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5281 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
5282 p_procedure_name => l_api_name,
5283 p_error_text => SUBSTR(SQLERRM,1,500));
5284 END IF;
5285 x_msg_count := FND_MSG_PUB.count_msg;
5286 x_msg_data := AHL_PRD_WO_PUB.GET_MSG_DATA(x_msg_count);
5287 /*FND_MSG_PUB.count_and_get( p_count => x_msg_count,
5288 p_data => x_msg_data,
5289 p_encoded => fnd_api.g_false);*/
5290 END Call_LOV_Services;
5291
5292 END AHL_PRD_LOV_SERVICE_PVT;