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