[Home] [Help]
PACKAGE BODY: APPS.AHL_OSP_QUERIES_PVT
Source
1 PACKAGE BODY AHL_OSP_QUERIES_PVT AS
2 /*$Header: AHLVOSQB.pls 120.4 2008/03/18 15:04:39 mpothuku ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_OSP_QUERIES_PVT';
5
6 G_OSP_ENTERED_STATUS CONSTANT VARCHAR2(30) := 'ENTERED';
7 G_EMPTY_WO_IDS_TABLE AHL_OSP_QUERIES_PVT.work_id_tbl_type;
8
9 G_VENDOR_DEPT_CLASS_CODE CONSTANT VARCHAR2(30) := 'Vendor';
10
11 --G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
12 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
13 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_OSP_QUERIES_PVT';
14
15
16 FUNCTION Get_Suggested_Vendor(p_work_order_id IN NUMBER ,
17 p_work_order_ids IN AHL_OSP_QUERIES_PVT.work_id_tbl_type)
18 RETURN VARCHAR2;
19
20
21 ----------------------------------------------------------------------------------
22 -- PROCEDURE SEARCH_OSP_ORDERS
23 ----------------------------------------------------------------------------------
24 -- This procedure Search for osp order based on the search criteria specify in parameter P_search_order_rec
25 -- The search result will be populated into x_results_order_tbl.
26 -- Start of Comments --
27 -- Procedure name : Search_OSP
28 -- Type : Public
29 -- Function : Search OSP Order
30 -- Pre-reqs :
31 -- Parameters :
32 --
33 -- Standard IN Parameters :
34 -- p_api_version IN NUMBER Required
35 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
36 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
37 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
38 -- p_module_type IN VARCHAR2 Default Null
39 -- Standard OUT Parameters :
40 -- x_return_status OUT VARCHAR2 Required
41 -- x_msg_count OUT NUMBER Required
42 -- x_msg_data OUT VARCHAR2 Required
43 --
44 -- p_module_type IN VARCHAR2 Required.
45 -- This parameter indicates the front-end form interface. The default value is null. If the value
46 -- is JSP, then this API clears out all id columns and validations are done using the values;based
47 -- on which the Id's are populated.
48 --
49 -- Search_OSP Parameters :
50 -- p_start_row IN NUMBER specify the start row to populate into search result table
51 -- p_rows_per_page IN NUMBER specify the number of row to be populated in the search result table
52 -- P_search_order_rec IN Search_Order_rec_type, specify the search criteria
53 -- x_results_order_tbl OUT Results_Order_Tbl_Type, the search Result table
54 -- x_results_count OUT NUMBER, row count from the query, this number can be more than the number of row in search result table
55 --
56 -- Version :
57 -- Initial Version 1.0
58 --
59 -- End of Comments.
60
61 PROCEDURE Search_OSP_Orders
62 (
63 p_api_version IN NUMBER,
64 p_init_msg_list IN VARCHAR2,
65 p_commit IN VARCHAR2 ,
66 p_validation_level IN NUMBER,
67 p_default IN VARCHAR2,
68 p_module_type IN VARCHAR2,
69 p_start_row IN NUMBER,
70 p_rows_per_page IN NUMBER,
71 P_search_order_rec IN AHL_OSP_QUERIES_PVT.Search_Order_rec_type,
72 x_results_order_tbl OUT NOCOPY AHL_OSP_QUERIES_PVT.Results_Order_Tbl_Type,
73 x_results_count OUT NOCOPY NUMBER,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_msg_count OUT NOCOPY NUMBER,
76 x_msg_data OUT NOCOPY VARCHAR2
77 ) IS
78
79 l_api_version CONSTANT NUMBER := 1.0;
80 l_api_name CONSTANT VARCHAR2(30) := 'Search_OSP_Orders';
81
82 l_search_criteria_rec AHL_OSP_QUERIES_PVT.Search_Order_Rec_Type;
83 l_results_rec AHL_OSP_QUERIES_PVT.Results_Order_Rec_Type;
84 l_count NUMBER;
85 i NUMBER;
86 l_cur_index NUMBER;
87
88 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
89 l_bind_index NUMBER := 1;
90 l_conditions AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
91
92
93 CURSOR l_department_csr(dept_id IN NUMBER, dept_code IN VARCHAR2) IS
94 SELECT 'X' FROM BOM_DEPARTMENTS where DEPARTMENT_ID = dept_id AND DEPARTMENT_CODE = dept_code;
95
96 l_junk VARCHAR2(1);
97
98
99 l_OSP_ID NUMBER ;
100 l_Object_version_number NUMBER ;
101 l_Order_Number NUMBER ;
102 l_Order_Date DATE ;
103 l_Description VARCHAR2(2000);
104 l_order_type_code VARCHAR2(30) ;
105 l_Order_Type VARCHAR2(80) ;
106 l_status_code VARCHAR2(30) ;
107 l_Order_Status VARCHAR2(80) ;
108 l_po_header_id NUMBER ;
109 l_po_Number VARCHAR2(20) ;
110 l_oe_header_id NUMBER ;
111 l_Shipment_Number NUMBER ;
112 l_po_interface_header_id NUMBER ;
113
114 --l_sql_string hold the query string
115 l_sql_string VARCHAR2(10000);
116 l_from_string VARCHAR2(5000);
117 l_search_criteria VARCHAR2(10000);
118 l_count_query VARCHAR2(10000);
119 and_str VARCHAR(20);
120 LI_Field_Exist BOOLEAN; --check whether query contain search field that belong to Order Lines
121
122 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Search_OSP_Orders';
123
124 BEGIN
125
126 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
127 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
128 END IF;
129
130 IF G_DEBUG='Y' THEN
131 AHL_DEBUG_PUB.enable_debug;
132
133 END IF;
134
135 -- Standard call to check for call compatibility
136 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
137 G_PKG_NAME) THEN
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
139 END IF;
140
141 -- Initialize message list if p_init_msg_list is set to TRUE
142 IF FND_API.To_Boolean(p_init_msg_list) THEN
143 FND_MSG_PUB.Initialize;
144 END IF;
145
146 --Commented by mpothuku as this API is not being used. At a later point, need to consider removing the
147 --declaration in Spec, Record Structure for Criteria and Rosetta
148 /*
149 -- Initialize API return status to success
150 x_return_status := FND_API.G_RET_STS_SUCCESS;
151
152
153 -- Begin build Query
154 l_sql_string := 'SELECT DISTINCT OSP.OSP_ORDER_ID ';
155 l_sql_string := l_sql_string ||', OSP.OBJECT_VERSION_NUMBER ';
156 l_sql_string := l_sql_string ||', OSP.OSP_ORDER_NUMBER ';
157 l_sql_string := l_sql_string ||', OSP.ORDER_DATE ';
158 l_sql_string := l_sql_string ||', OSP.DESCRIPTION';
159 l_sql_string := l_sql_string ||', OSP.ORDER_TYPE_CODE ';
160 l_sql_string := l_sql_string ||', OSP.ORDER_TYPE ';
161 l_sql_string := l_sql_string ||', OSP.STATUS_CODE ';
162 l_sql_string := l_sql_string ||', OSP.STATUS ';
163 l_sql_string := l_sql_string ||', OSP.PO_HEADER_ID ';
164 l_sql_string := l_sql_string ||', OSP.PO_NUMBER ';
165 l_sql_string := l_sql_string ||', OSP.OE_HEADER_ID ';
166 l_sql_string := l_sql_string ||', OSP.SHIPMENT_NUMBER ';
167 l_sql_string := l_sql_string ||', OSP.PO_INTERFACE_HEADER_ID ';
168
169 l_from_string := ' FROM AHL_OSP_ORDERS_V OSP ';
170
171 and_str := '';
172 l_search_criteria := NULL;
173 LI_Field_Exist := FALSE;
174
175 IF P_search_order_rec.Order_Number IS NOT NULL THEN
176 l_search_criteria := l_search_criteria || ' UPPER(OSP.OSP_ORDER_NUMBER) LIKE UPPER (:b' || l_bind_index || ')';
177 l_conditions(l_bind_index) := P_search_order_rec.Order_Number;
178 l_bind_index := l_bind_index + 1;
179 and_str := ' AND ';
180 END IF;
181 IF P_search_order_rec.Description IS NOT NULL THEN
182 l_search_criteria := l_search_criteria || and_str || ' UPPER(OSP.DESCRIPTION) LIKE UPPER (:b' || l_bind_index || ')';
183 l_conditions(l_bind_index) := P_search_order_rec.Description;
184 l_bind_index := l_bind_index + 1;
185 and_str := ' AND ';
186 END IF;
187 IF P_search_order_rec.Order_Type_Code IS NOT NULL THEN
188 l_search_criteria := l_search_criteria || and_str || ' UPPER(OSP.ORDER_TYPE) LIKE UPPER (:b' || l_bind_index || ')';
189 l_conditions(l_bind_index) := P_search_order_rec.Order_Type_Code;
190 l_bind_index := l_bind_index + 1;
191 and_str := ' AND ';
192 END IF;
193 IF P_search_order_rec.Order_Status_Code IS NOT NULL THEN
194 l_search_criteria := l_search_criteria || and_str || ' UPPER(OSP.STATUS) LIKE UPPER (:b' || l_bind_index || ')';
195 l_conditions(l_bind_index) := P_search_order_rec.Order_Status_Code;
196 l_bind_index := l_bind_index + 1;
197 and_str := ' AND ';
198 END IF;
199 IF P_search_order_rec.vendor IS NOT NULL THEN
200 l_search_criteria := l_search_criteria || and_str || ' UPPER(OSP.VENDOR) LIKE UPPER (:b' || l_bind_index || ')';
201 l_conditions(l_bind_index) := P_search_order_rec.vendor;
202 l_bind_index := l_bind_index + 1;
203 and_str := ' AND ';
204 END IF;
205
206
207 IF P_search_order_rec.job_number IS NOT NULL THEN
208 l_search_criteria := l_search_criteria || and_str || ' UPPER(LI.JOB_NUMBER) LIKE UPPER (:b' || l_bind_index || ')';
209 l_conditions(l_bind_index) := P_search_order_rec.job_number;
210 l_bind_index := l_bind_index + 1;
211 and_str := ' AND ';
212 LI_Field_Exist := TRUE;
213 END IF;
214 IF P_search_order_rec.Project_Name IS NOT NULL THEN
215 l_search_criteria := l_search_criteria || and_str || ' UPPER(LI.PROJECT_NAME) LIKE UPPER (:b' || l_bind_index || ')';
216 l_conditions(l_bind_index) := P_search_order_rec.Project_Name;
217 l_bind_index := l_bind_index + 1;
218 and_str := ' AND ';
219 LI_Field_Exist := TRUE;
220 END IF;
221 IF P_search_order_rec.Task_Name IS NOT NULL THEN
222 l_search_criteria := l_search_criteria || and_str || ' UPPER(LI.PROJECT_TASK_NAME) LIKE UPPER (:b' || l_bind_index || ')';
223 l_conditions(l_bind_index) := P_search_order_rec.Task_Name;
224 l_bind_index := l_bind_index + 1;
225 and_str := ' AND ';
226 LI_Field_Exist := TRUE;
227 END IF;
228 IF P_search_order_rec.Part_Number IS NOT NULL THEN
229 l_search_criteria := l_search_criteria || and_str || ' UPPER(LI.WO_PART_NUMBER) LIKE UPPER (:b' || l_bind_index || ')';
230 l_conditions(l_bind_index) := P_search_order_rec.Part_Number;
231 l_bind_index := l_bind_index + 1;
232 and_str := ' AND ';
233 LI_Field_Exist := TRUE;
234 END IF;
235 IF P_search_order_rec.Serial_Number IS NOT NULL THEN
236 l_search_criteria := l_search_criteria || and_str || ' UPPER(LI.SERIAL_NUMBER) LIKE UPPER (:b' || l_bind_index || ')';
237 l_conditions(l_bind_index) := P_search_order_rec.Serial_Number;
238 l_bind_index := l_bind_index + 1;
239 and_str := ' AND ';
240 LI_Field_Exist := TRUE;
241 END IF;
242 IF P_search_order_rec.Department IS NOT NULL THEN
243 l_search_criteria := l_search_criteria || and_str || ' EXISTS (SELECT OL.OSP_ORDER_LINE_ID FROM AHL_WORKORDERS_OSP_V WO, '
244 || ' AHL_OSP_ORDER_LINES OL WHERE '
245 || ' OL.OSP_ORDER_ID = OSP.OSP_ORDER_ID AND OL.WORKORDER_ID = WO.WORKORDER_ID ';
246 --|| ' AND UPPER(WO.DEPARTMENT_CODE) LIKE UPPER('''|| P_search_order_rec.Department || ''')) ';
247
248 IF P_search_order_rec.department_id IS NOT NULL THEN
249 OPEN l_department_csr(P_search_order_rec.department_id, P_search_order_rec.Department);
250 FETCH l_department_csr INTO l_junk;
251 IF (l_department_csr%NOTFOUND) THEN
252 l_search_criteria := l_search_criteria || ' AND UPPER(WO.DEPARTMENT_CODE) LIKE UPPER (:b' || l_bind_index || '))';
253 l_conditions(l_bind_index) := P_search_order_rec.Department;
254 ELSE
255 l_search_criteria := l_search_criteria || ' AND WO.DEPARTMENT_ID = :b' || l_bind_index || ') ';
256 l_conditions(l_bind_index) := P_search_order_rec.Department_Id;
257 END IF;
258 CLOSE l_department_csr;
259 ELSE
260 l_search_criteria := l_search_criteria || ' AND UPPER(WO.DEPARTMENT_CODE) LIKE UPPER(:b' || l_bind_index ||')) ';
261 l_conditions(l_bind_index) := P_search_order_rec.Department;
262 END IF;
263
264 l_bind_index := l_bind_index + 1;
265 and_str := ' AND ';
266 -- LI_Field_Exist := TRUE;
267 END IF;
268
269
270 IF P_search_order_rec.Has_New_PO_Line = 'Y' OR P_search_order_rec.Has_New_PO_Line = 'N' THEN
271 l_search_criteria := l_search_criteria || and_str || ' AHL_OSP_PO_PVT.Has_New_PO_Line(OSP.OSP_ORDER_ID) = :b' || l_bind_index;
272 l_conditions(l_bind_index) := P_search_order_rec.Has_New_PO_Line;
273 l_bind_index := l_bind_index + 1;
274 and_str := ' AND ';
275 END IF;
276
277
278 IF LI_Field_Exist THEN
279 l_from_string := l_from_string || ', AHL_OSP_ORDER_LINES_V LI ';
280 l_search_criteria := l_search_criteria || ' AND LI.OSP_ORDER_ID = OSP.OSP_ORDER_ID ';
281 END IF;
282
283 l_sql_string := l_sql_string || l_from_string ;
284
285 IF l_search_criteria IS NOT NULL THEN
286 l_sql_string := l_sql_string || ' WHERE ' || l_search_criteria ;
287 END IF;
288
289 l_count_query := 'SELECT COUNT(*) FROM (' || l_sql_string || ')';
290
291 l_sql_string := l_sql_string || ' ORDER BY OSP.OSP_ORDER_ID DESC ';
292
293 IF G_DEBUG='Y' THEN
294 AHL_DEBUG_PUB.debug(SUBSTR( l_sql_string, 1, 1000), 'SEARCH_OSP: ');
295 END IF;
296
297 --remove this line when not test
298 --l_sql_string := l_sql_string || ' WHERE ROWNUM < 3 ';
299
300
301 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR(l_cur, l_conditions, l_sql_string);
302 i := 0;
303 l_cur_index := 0;
304
305 LOOP
306 FETCH l_cur into l_OSP_ID ,
307 l_Object_version_number,
308 l_Order_Number ,
309 l_Order_Date ,
310 l_Description ,
311 l_order_type_code ,
312 l_Order_Type ,
313 l_status_code ,
314 l_Order_Status ,
315 l_po_header_id ,
316 l_po_Number ,
317 l_oe_header_id ,
318 l_Shipment_Number ,
319 l_po_interface_header_id;
320 EXIT WHEN l_cur%NOTFOUND;
321 EXIT WHEN l_cur_index = p_start_row + p_rows_per_page; -- stop fetching
322
323 IF (l_cur_index >= p_start_row AND l_cur_index < p_start_row + p_rows_per_page) THEN
324 x_results_order_tbl(i).OSP_ID := l_OSP_ID ;
325 x_results_order_tbl(i).Object_version_number := l_Object_version_number ;
326 x_results_order_tbl(i).Order_Number := l_Order_Number ;
327 x_results_order_tbl(i).Order_Date := l_Order_Date;
328 x_results_order_tbl(i).Description := l_Description ;
329 x_results_order_tbl(i).order_type_code := l_order_type_code ;
330 x_results_order_tbl(i).Order_Type := l_Order_Type;
331 x_results_order_tbl(i).status_code := l_status_code;
332 x_results_order_tbl(i).Order_Status := l_Order_Status;
333 x_results_order_tbl(i).po_header_id := l_po_header_id ;
334 x_results_order_tbl(i).po_Number := l_po_Number ;
335 x_results_order_tbl(i).oe_header_id := l_oe_header_id;
336 x_results_order_tbl(i).Shipment_Number := l_Shipment_Number;
337 x_results_order_tbl(i).po_interface_header_id := l_po_interface_header_id;
338 i := i+1;
339 END IF;
340
341 l_cur_index := l_cur_index + 1;
342
343 END LOOP;
344 CLOSE l_cur;
345
346
347 BEGIN
348 AHL_OSP_UTIL_PKG.EXEC_IMMEDIATE(l_conditions, l_count_query, x_results_count);
349 EXCEPTION
350 WHEN NO_DATA_FOUND THEN
351 x_results_count := 0;
352 END;
353 IF G_DEBUG='Y' THEN
354 AHL_DEBUG_PUB.debug(' x_results_count:-' || x_results_count);
355 END IF;
356
357 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
358 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'End Procedure');
359 END IF;
360 */
361 EXCEPTION
362 WHEN FND_API.G_EXC_ERROR THEN
363 --dbms_output.put_line('Excep 1 ');
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 -- Rollback to Search_OSP_Pvt;
366 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
367 p_data => x_msg_data,
368 p_encoded => fnd_api.g_false);
369
370 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
371 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, L_DEBUG_KEY, 'Execution Exception: ' || x_msg_data);
372 END IF;
373
374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
375 -- dbms_output.put_line('Excep 2 ');
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
378 p_data => x_msg_data,
379 p_encoded => fnd_api.g_false);
380 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
381 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
382 END IF;
383
384 WHEN OTHERS THEN
385 -- dbms_output.put_line('Excep 3 ');
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 -- Rollback to Search_OSP_Pvt;
388 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
389 p_procedure_name => 'Search_OSP_Orders',
390 p_error_text => SQLERRM);
391
392 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
393 p_data => x_msg_data,
394 p_encoded => fnd_api.g_false);
395
396 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
397 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
398 END IF;
399
400
401
402 END Search_OSP_Orders;
403
404
405
406 ----------------------------------------------------------------------------------
407 -- PROCEDURE Search_WO
408 ----------------------------------------------------------------------------------
409 -- This procedure Search for Work orders based on the search criteria specify in parameter P_search_WO_rec
410 -- The search result will be populated into x_results_order_tbl.
411 -- Start of Comments --
412 -- Procedure name : Search_OSP
413 -- Type : Public
414 -- Function : Search OSP Order
415 -- Pre-reqs :
416 -- Parameters :
417 --
418 -- Standard IN Parameters :
419 -- p_api_version IN NUMBER Required
420 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
421 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
422 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
423 -- p_module_type IN VARCHAR2 Default Null
424 -- Standard OUT Parameters :
425 -- x_return_status OUT VARCHAR2 Required
426 -- x_msg_count OUT NUMBER Required
427 -- x_msg_data OUT VARCHAR2 Required
428 --
429 -- p_module_type IN VARCHAR2 Required.
430 -- This parameter indicates the front-end form interface. The default value is null. If the value
431 -- is JSP, then this API clears out all id columns and validations are done using the values;based
432 -- on which the Id's are populated.
433 --
434 -- Search_OSP Parameters :
435 -- p_start_row IN NUMBER specify the start row to populate into search result table
436 -- p_rows_per_page IN NUMBER specify the number of row to be populated in the search result table
437 -- P_search_order_rec IN Search_Order_rec_type, specify the search criteria
438 -- x_results_order_tbl OUT Results_Order_Tbl_Type, the search Result table
439 -- x_results_count OUT NUMBER, row count from the query, this number can be more than the number of row in search result table
440 --
441 -- Version :
442 -- Initial Version 1.0
443 --
444 -- End of Comments.
445
446 PROCEDURE Search_WO
447 (
448 p_api_version IN NUMBER,
449 p_init_msg_list IN VARCHAR2 ,
450 p_commit IN VARCHAR2 ,
451 p_validation_level IN NUMBER ,
452 p_module_type IN VARCHAR2,
453 p_start_row IN NUMBER,
454 p_rows_per_page IN NUMBER,
455 p_search_WO_rec IN AHL_OSP_QUERIES_PVT.Search_WO_Rec_Type,
456 x_result_WO_tbl OUT NOCOPY AHL_OSP_QUERIES_PVT.Results_WO_Tbl_Type,
457 x_results_count OUT NOCOPY NUMBER,
458 x_return_status OUT NOCOPY VARCHAR2,
459 x_msg_count OUT NOCOPY NUMBER,
460 x_msg_data OUT NOCOPY VARCHAR2)
461 IS
462 l_api_version CONSTANT NUMBER := 1.0;
463 l_api_name CONSTANT VARCHAR2(30) := 'Search_WO';
464
465 l_l_search_criteria_rec AHL_OSP_QUERIES_PVT.Search_Order_Rec_Type;
466 l_count NUMBER;
467
468 Workorder_ID NUMBER;
469 job_number VARCHAR2(80);
470 Part_number VARCHAR2(40);
471 Instance_number VARCHAR2(30);
472 Serial_number VARCHAR2(30);
473 Svc_item_number VARCHAR2(40);
474 Svc_Description VARCHAR2(240);
475 Suggested_Vendor VARCHAR2(240);
476 Department VARCHAR2(10);
477
478 l_wo_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
479 l_bind_index NUMBER := 1;
480 l_conditions AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
481
482 CURSOR l_department_csr(dept_id IN NUMBER, dept_code IN VARCHAR2) IS
483 SELECT 'X' FROM BOM_DEPARTMENTS where DEPARTMENT_ID = dept_id AND DEPARTMENT_CODE = dept_code;
484
485 l_junk VARCHAR2(1);
486 l_sql_string VARCHAR2(10000);
487 l_search_criteria VARCHAR2(10000);
488 l_count_query VARCHAR2(10000);
489 and_str VARCHAR2(10);
490
491 l_cur_index NUMBER; -- index used by cursor
492 i NUMBER; -- index used by table
493
494 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Search_WO';
495
496 BEGIN
497
498
499 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
500 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
501 END IF;
502
503 IF G_DEBUG='Y' THEN
504 AHL_DEBUG_PUB.enable_debug;
505
506 END IF;
507
508 -- Standard call to check for call compatibility
509 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
510 G_PKG_NAME) THEN
511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
512 END IF;
513
514 -- Initialize message list if p_init_msg_list is set to TRUE
515 IF FND_API.To_Boolean(p_init_msg_list) THEN
516 FND_MSG_PUB.Initialize;
517 END IF;
518
519 --Commented by mpothuku as this API is not being used. At a later point, need to consider removing the
520 --declaration in Spec, Record Structure for Criteria and Rosetta
521 /*
522 -- Initialize API return status to success
523 x_return_status := FND_API.G_RET_STS_SUCCESS;
524
525 l_sql_string := 'SELECT WORKORDER_ID';
526 l_sql_string:= l_sql_string || ', JOB_NUMBER';
527 l_sql_string:= l_sql_string || ', WO_PART_NUMBER';
528 l_sql_string:= l_sql_string || ', ITEM_INSTANCE_NUMBER';
529 l_sql_string:= l_sql_string || ', SERIAL_NUMBER ';
530 l_sql_string:= l_sql_string || ', SERVICE_ITEM_NUMBER ';
531 l_sql_string:= l_sql_string || ', SERVICE_ITEM_DESCRIPTION ';
532 l_sql_string:= l_sql_string || ', DEPARTMENT_CODE ';
533
534
535
536 l_sql_string:= l_sql_string || ' FROM AHL_WORKORDERS_OSP_V WO ';
537
538 -- l_search_criteria := '';
539 and_str := ' ';
540
541 l_search_criteria := l_search_criteria || ' WO.DEPARTMENT_CLASS_CODE = ''' || G_VENDOR_DEPT_CLASS_CODE || '''';
542 l_search_criteria := l_search_criteria || ' AND WO.JOB_STATUS_CODE = ''3'' ';
543 l_search_criteria := l_search_criteria || ' AND NOT EXISTS (SELECT OL1.WORKORDER_ID FROM AHL_OSP_ORDER_LINES OL1 WHERE OL1.WORKORDER_ID = WO.WORKORDER_ID AND OL1.STATUS_CODE IS NULL) ';
544
545
546 -- l_search_criteria := l_search_criteria || ' (WO.WORKORDER_ID NOT IN (SELECT WORKORDER_ID FROM AHL_OSP_ORDER_LINES) OR WO.WORKORDER_ID IN (SELECT WORKORDER_ID FROM AHL_OSP_ORDER_LINES WHERE JOB_STATUS_CODE IN (''PO_DELETED'', ''PO_CANCEL''))) ';
547
548 IF p_search_WO_rec.job_number IS NOT NULL THEN
549 l_search_criteria := l_search_criteria || ' AND UPPER(JOB_NUMBER) LIKE UPPER (:b' || l_bind_index || ')';
550 l_conditions(l_bind_index) := p_search_WO_rec.job_number;
551 l_bind_index := l_bind_index + 1;
552 END IF;
553 IF p_search_WO_rec.description IS NOT NULL THEN
554 l_search_criteria := l_search_criteria || ' AND UPPER(SERVICE_ITEM_DESCRIPTION) LIKE UPPER (:b' || l_bind_index || ')';
555 l_conditions(l_bind_index) := p_search_WO_rec.description;
556 l_bind_index := l_bind_index + 1;
557 END IF;
558 IF p_search_WO_rec.project_name IS NOT NULL THEN
559 l_search_criteria := l_search_criteria || ' AND UPPER(PROJECT_NAME) LIKE UPPER (:b' || l_bind_index || ')';
560 l_conditions(l_bind_index) := p_search_WO_rec.project_name;
561 l_bind_index := l_bind_index + 1;
562 END IF;
563 IF p_search_WO_rec.task_name IS NOT NULL THEN
564 l_search_criteria := l_search_criteria || ' AND UPPER(PROJECT_TASK_NAME) LIKE UPPER (:b' || l_bind_index || ')';
565 l_conditions(l_bind_index) := p_search_WO_rec.task_name;
566 l_bind_index := l_bind_index + 1;
567 END IF;
568 IF p_search_WO_rec.Part_number IS NOT NULL THEN
569 l_search_criteria := l_search_criteria || ' AND UPPER(WO_PART_NUMBER) LIKE UPPER (:b' || l_bind_index || ')';
570 l_conditions(l_bind_index) := p_search_WO_rec.Part_number;
571 l_bind_index := l_bind_index + 1;
572 END IF;
573 IF p_search_WO_rec.Instance_number IS NOT NULL THEN
574 l_search_criteria := l_search_criteria || ' AND UPPER(ITEM_INSTANCE_NUMBER) LIKE UPPER (:b' || l_bind_index || ')';
575 l_conditions(l_bind_index) := p_search_WO_rec.Instance_number;
576 l_bind_index := l_bind_index + 1;
577 END IF;
578 IF p_search_WO_rec.Serial_number IS NOT NULL THEN
579 l_search_criteria := l_search_criteria || ' AND UPPER(SERIAL_NUMBER) LIKE UPPER (:b' || l_bind_index || ')';
580 l_conditions(l_bind_index) := p_search_WO_rec.Serial_number;
581 l_bind_index := l_bind_index + 1;
582 END IF;
583 IF p_search_WO_rec.Svc_item_number IS NOT NULL THEN
584 l_search_criteria := l_search_criteria || ' AND UPPER(SERVICE_ITEM_NUMBER) LIKE UPPER (:b' || l_bind_index || ')';
585 l_conditions(l_bind_index) := p_search_WO_rec.Svc_item_number;
586 l_bind_index := l_bind_index + 1;
587 END IF;
588 IF p_search_WO_rec.Department IS NOT NULL THEN
589 IF p_search_WO_rec.department_id IS NOT NULL THEN
590 OPEN l_department_csr(p_search_WO_rec.department_id, p_search_WO_rec.Department);
591 FETCH l_department_csr INTO l_junk;
592 IF (l_department_csr%NOTFOUND) THEN
593 l_search_criteria := l_search_criteria || ' AND UPPER(DEPARTMENT_CODE) LIKE UPPER (:b' || l_bind_index || ')';
594 l_conditions(l_bind_index) := p_search_WO_rec.Department;
595 ELSE
596 l_search_criteria := l_search_criteria || ' AND DEPARTMENT_ID = :b' || l_bind_index;
597 l_conditions(l_bind_index) := p_search_WO_rec.department_id;
598 END IF;
599 CLOSE l_department_csr;
600 ELSE
601 l_search_criteria := l_search_criteria || ' AND UPPER(DEPARTMENT_CODE) LIKE UPPER (:b' || l_bind_index || ')';
602 l_conditions(l_bind_index) := p_search_WO_rec.Department;
603 END IF;
604 l_bind_index := l_bind_index + 1;
605 END IF;
606
607 IF l_search_criteria IS NOT NULL THEN
608 l_sql_string := l_sql_string || ' WHERE ' || l_search_criteria;
609 END IF;
610
611 l_count_query := 'SELECT COUNT(*) FROM (' ||l_sql_string || ')';
612
613 l_sql_string := l_sql_string || ' ORDER BY JOB_NUMBER ';
614
615 IF G_DEBUG='Y' THEN
616 AHL_DEBUG_PUB.debug(SUBSTR(l_sql_string,1, 1000), 'SEARCH_WO: ');
617 END IF;
618
619 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR(l_wo_cur, l_conditions, l_sql_string);
620 i := 0;
621 l_cur_index := 0;
622 LOOP
623 FETCH l_wo_cur INTO Workorder_ID,
624 job_number,
625 Part_number,
626 Instance_number,
627 Serial_number,
628 Svc_item_number,
629 Svc_Description,
630 Department;
631 EXIT WHEN l_wo_cur%NOTFOUND;
632 EXIT WHEN l_cur_index = p_start_row + p_rows_per_page; -- stop fetching
633
634 IF (l_cur_index >= p_start_row AND l_cur_index < p_start_row + p_rows_per_page) THEN
635 x_result_WO_tbl(i).Workorder_ID := Workorder_ID;
636 x_result_WO_tbl(i).job_number := job_number;
637 x_result_WO_tbl(i).Part_number := Part_number;
638 x_result_WO_tbl(i).Instance_number := Instance_number;
639 x_result_WO_tbl(i).Serial_number := Serial_number;
640 x_result_WO_tbl(i).Svc_item_number := Svc_item_number;
641 x_result_WO_tbl(i).Svc_Description := Svc_Description;
642 x_result_WO_tbl(i).department := department;
643
644 x_result_WO_tbl(i).Suggested_Vendor := Get_Suggested_Vendor(p_work_order_id => Workorder_ID,
645 p_work_order_ids =>AHL_OSP_QUERIES_PVT.G_EMPTY_WO_IDS_TABLE );
646
647 i:= i + 1;
648 END IF;
649
650 l_cur_index := l_cur_index + 1;
651 END LOOP;
652
653
654
655 BEGIN
656 AHL_OSP_UTIL_PKG.EXEC_IMMEDIATE(l_conditions, l_count_query, x_results_count);
657 EXCEPTION
658 WHEN NO_DATA_FOUND THEN
659 x_results_count := 0;
660 END;
661 IF G_DEBUG='Y' THEN
662 AHL_DEBUG_PUB.debug(' x_results_count:-' || x_results_count);
663 END IF;
664
665
666
667 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
668 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'End Procedure');
669 END IF;
670 */
671 EXCEPTION
672 WHEN FND_API.G_EXC_ERROR THEN
673 --dbms_output.put_line('Excep 1 ');
674 x_return_status := FND_API.G_RET_STS_ERROR;
675 -- Rollback to Search_WO_pvt;
676 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
677 p_data => x_msg_data,
678 p_encoded => fnd_api.g_false);
679
680 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
681 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, L_DEBUG_KEY, 'Execution Exception: ' || x_msg_data);
682 END IF;
683
684 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
685 -- dbms_output.put_line('Excep 2 ');
686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
687 --Rollback to Search_WO_pvt;
688 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
689 p_data => x_msg_data,
690 p_encoded => fnd_api.g_false);
691
692
693 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
694 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
695 END IF;
696
697 WHEN OTHERS THEN
698 -- dbms_output.put_line('Excep 3 ');
699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
700 --Rollback to Search_WO_pvt;
701 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
702 p_procedure_name => 'Search_WO',
703 p_error_text => SQLERRM);
704
705 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
706 p_data => x_msg_data,
707 p_encoded => fnd_api.g_false);
708
709 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
710 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
711 END IF;
712
713 END Search_WO;
714
715
716 ----------------------------------------------------------------------------------
717 -- PROCEDURE GET_HEADER_AND_LINES
718 ----------------------------------------------------------------------------------
719 ----------------------------------------
720 -- Declare Procedures for GET_HEADER_AND_LINES --
721 ----------------------------------------
722 -- This procedure Search for OSP Order Header and order lines based on the input parameter P_osp_id.
723 -- When the input parameter p_osp_id is null it will use input parameter P_work_order_ids to search for workorders
724 -- and populate into order line table.
725
726 -- Start of Comments --
727 -- Procedure name : Search_OSP
728 -- Type : Public
729 -- Function : Search OSP Order
730 -- Pre-reqs :
731 -- Parameters :
732 --
733 -- Standard IN Parameters :
734 -- p_api_version IN NUMBER Required
735 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
736 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
737 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
738 -- p_module_type IN VARCHAR2 Default Null
739 -- Standard OUT Parameters :
740 -- x_return_status OUT VARCHAR2 Required
741 -- x_msg_count OUT NUMBER Required
742 -- x_msg_data OUT VARCHAR2 Required
743 --
744 -- p_module_type IN VARCHAR2 Required.
745 -- This parameter indicates the front-end form interface. The default value is null. If the value
746 -- is JSP, then this API clears out all id columns and validations are done using the values;based
747 -- on which the Id's are populated.
748 --
749 -- Search_OSP Parameters :
750 -- P_osp_id IN NUMBER, id that the search will be based on.
751 -- P_work_order_ids IN work_id_tbl_type, List of workorder id that search will be based on if p_osp_id is null
752 -- x_order_header_rec IN order_header_rec_Type, store order header
753 -- x_order_lines_tbl OUT order_line_tbl_Type, Store order order lines rows
754 -- x_msg_count OUT NUMBER,
755 -- Version :
756 -- Initial Version 1.0
757 --
758 -- End of Comments.
759 PROCEDURE GET_HEADER_AND_LINES
760 (
761 p_api_version IN NUMBER,
762 p_init_msg_list IN VARCHAR2 ,
763 p_commit IN VARCHAR2 ,
764 p_validation_level IN NUMBER ,
765 p_module_type IN VARCHAR2 ,
766 P_osp_id IN NUMBER,
767 P_work_order_ids IN AHL_OSP_QUERIES_PVT.work_id_tbl_type,
768 x_order_header_rec OUT NOCOPY AHL_OSP_QUERIES_PVT.order_header_rec_Type,
769 x_order_lines_tbl OUT NOCOPY AHL_OSP_QUERIES_PVT.order_line_tbl_Type,
770 x_return_status OUT NOCOPY VARCHAR2,
771 x_msg_count OUT NOCOPY NUMBER,
772 x_msg_data OUT NOCOPY VARCHAR2
773
774 ) IS
775
776 l_api_version CONSTANT NUMBER := 1.0;
777 l_api_name CONSTANT VARCHAR2(30) := 'GET_HEADER_AND_LINES';
778
779 TYPE header_csr_type is REF CURSOR;
780 TYPE line_csr_type IS REF CURSOR;
781 l_header_csr header_csr_type; --cursor to hold order header
782 l_line_csr line_csr_type; --cursor to hold order lines
783
784
785 CURSOR l_default_status_csr IS
786 SELECT FND.MEANING FROM FND_LOOKUP_VALUES_VL FND
787 WHERE FND.LOOKUP_TYPE = 'AHL_OSP_STATUS_TYPE'
788 AND FND.LOOKUP_CODE = 'ENTERED';
789 CURSOR l_default_buyer_csr IS
790 SELECT buyer_id, full_name FROM PO_AGENTS_NAME_V, fnd_user fnd
791 where buyer_id = fnd.employee_id and fnd.user_id = fnd_global.user_id ;
792
793
794 l_header_queries VARCHAR2(10000);
795 l_line_queries VARCHAR2(1000);
796
797 l_po_header_id NUMBER;
798
799 OSP_ID NUMBER;
800 object_version_number NUMBER;
801 order_number NUMBER;
802 order_description VARCHAR2(1000);
803 order_type_code VARCHAR(30);
804 order_type VARCHAR2(80);
805 order_status_code VARCHAR2(30);
806 order_status VARCHAR2(80);
807 order_date DATE;
808 VENDOR_ID NUMBER;
809 vendor_name VARCHAR2(240);
810 vendor_site_id NUMBER;
811 vendor_location VARCHAR2(15);
812 CUSTOMER_ID NUMBER;
813 CUSTOMER VARCHAR2(360);
814 single_instance_Flag VARCHAR2(20);
815 single_instance_meaning VARCHAR2(80);
816 PO_AGENT_ID NUMBER;
817 buyer_name VARCHAR2(240);
818 PO_HEADER_ID NUMBER;
819 po_number VARCHAR2(80);
820 po_synch_flag VARCHAR(20);
821 OE_HEADER_ID NUMBER;
822 shipment_number NUMBER;
823 CONTRACT_ID NUMBER;
824 contract_number VARCHAR2(120);
825
826 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.GET_HEADER_AND_LINES';
827
828
829 BEGIN
830
831 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
832 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
833 END IF;
834 -- Standard call to check for call compatibility
835 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
836 G_PKG_NAME) THEN
837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
838 END IF;
839
840 -- Initialize message list if p_init_msg_list is set to TRUE
841 IF FND_API.To_Boolean(p_init_msg_list) THEN
842 FND_MSG_PUB.Initialize;
843 END IF;
844
845 -- Initialize API return status to success
846 x_return_status := FND_API.G_RET_STS_SUCCESS;
847
848
849
850 IF (P_OSP_ID IS NOT NULL) THEN
851
852
853 IF G_DEBUG='Y' THEN
854 AHL_DEBUG_PUB.debug('Before Calling Associate_OSP_With_PO', 'GET_HEADER_AND_LINES: ');
855 END IF;
856 -- CALL PO API to synch OSP with PO
857 AHL_OSP_PO_PVT.Associate_OSP_With_PO
858 (
859 p_api_version => p_api_version ,
860 p_init_msg_list => p_init_msg_list,
861 p_commit => p_commit,
862 p_validation_level => p_validation_level,
863 p_default => FND_API.G_TRUE,
864 p_module_type => p_module_type,
865 p_osp_order_id => P_OSP_ID,
866 x_po_header_id => l_po_header_id,
867 x_return_status => x_return_status,
868 x_msg_count => x_msg_count,
869 x_msg_data => x_msg_data
870 );
871
872 IF G_DEBUG='Y' THEN
873 IF G_DEBUG='Y' THEN
874 AHL_DEBUG_PUB.debug('After Calling Associate_OSP_With_PO', 'GET_HEADER_AND_LINES: ');
875
876 END IF;
877 IF G_DEBUG='Y' THEN
878 AHL_DEBUG_PUB.debug('x_return_status' || x_return_status, 'GET_HEADER_AND_LINES: ');
879
880 END IF;
881 IF G_DEBUG='Y' THEN
882 AHL_DEBUG_PUB.debug('x_msg_data' || x_msg_data, 'GET_HEADER_AND_LINES: ');
883
884 END IF;
885
886 END IF;
887
888
889 l_header_queries := 'SELECT OSP.OSP_ORDER_ID ';
890 l_header_queries := l_header_queries || ', OSP.OBJECT_VERSION_NUMBER ';
891 l_header_queries := l_header_queries || ', OSP.OSP_ORDER_NUMBER ';
892 l_header_queries := l_header_queries || ', OSP.DESCRIPTION ';
893 l_header_queries := l_header_queries || ', OSP.ORDER_TYPE_CODE';
894 l_header_queries := l_header_queries || ', OSP.ORDER_TYPE ';
895 l_header_queries := l_header_queries || ', OSP.STATUS_CODE ';
896 l_header_queries := l_header_queries || ', OSP.STATUS ';
897 l_header_queries := l_header_queries || ', OSP.ORDER_DATE ';
898 l_header_queries := l_header_queries || ', OSP.VENDOR_ID ';
899 l_header_queries := l_header_queries || ', OSP.VENDOR ';
900 l_header_queries := l_header_queries || ', OSP.VENDOR_SITE_ID ';
901 l_header_queries := l_header_queries || ', OSP.VENDOR_LOCATION ';
902 l_header_queries := l_header_queries || ', OSP.CUSTOMER_ID ';
903 l_header_queries := l_header_queries || ', OSP.CUSTOMER ';
904 l_header_queries := l_header_queries || ', OSP.SINGLE_INSTANCE_FLAG ';
905 l_header_queries := l_header_queries || ', OSP.SINGLE_INSTANCE_MEANING ';
906 l_header_queries := l_header_queries || ', OSP.PO_AGENT_ID ';
907 l_header_queries := l_header_queries || ', OSP.BUYER';
908 l_header_queries := l_header_queries || ', OSP.PO_HEADER_ID ';
909 l_header_queries := l_header_queries || ', OSP.PO_NUMBER ';
910 l_header_queries := l_header_queries || ', OSP.PO_SYNCH_FLAG ';
911 l_header_queries := l_header_queries || ', OSP.OE_HEADER_ID ';
912 l_header_queries := l_header_queries || ', OSP.SHIPMENT_NUMBER ';
913 l_header_queries := l_header_queries || ', OSP.CONTRACT_ID ';
914 l_header_queries := l_header_queries || ', OSP.CONTRACT_NUMBER ';
915
916 l_header_queries := l_header_queries || ' FROM AHL_OSP_ORDERS_V OSP ';
917 l_header_queries := l_header_queries || ' WHERE OSP_ORDER_ID = ';
918 l_header_queries := l_header_queries || p_osp_id;
919
920
921 IF G_DEBUG='Y' THEN
922 AHL_DEBUG_PUB.debug('l_header_queries: ' || SUBSTR(l_header_queries,1, 900), 'GET_HEADER_AND_LINES: ');
923 END IF;
924
925 --dbms_output.put_line( 'SQL:' || l_header_queries );
926 OPEN l_header_csr FOR l_header_queries;
927
928 FETCH l_header_csr INTO OSP_ID,
929 object_version_number,
930 order_number,
931 order_description,
932 order_type_code,
933 order_type,
934 order_status_code,
935 order_status,
936 order_date,
937 vendor_id,
938 vendor_name,
939 vendor_site_id,
940 vendor_location,
941 CUSTOMER_ID,
942 CUSTOMER,
943 single_instance_Flag,
944 single_instance_meaning,
945 PO_AGENT_ID,
946 buyer_name,
947 PO_HEADER_ID,
948 po_number ,
949 po_synch_flag,
950 OE_HEADER_ID,
951 shipment_number,
952 CONTRACT_ID,
953 contract_number;
954
955
956 IF G_DEBUG='Y' THEN
957 AHL_DEBUG_PUB.debug('After Fetch l_header_queries: ' , 'GET_HEADER_AND_LINES: ');
958 END IF;
959
960 x_order_header_rec.OSP_ID := OSP_ID;
961 x_order_header_rec.object_version_number := object_version_number;
962 x_order_header_rec.order_number := order_number;
963 x_order_header_rec.order_description := order_description;
964 x_order_header_rec.order_type_code := order_type_code;
965 x_order_header_rec.order_type := order_type;
966 x_order_header_rec.order_status_code := order_status_code;
967 x_order_header_rec.order_status := order_status;
968 x_order_header_rec.order_date := order_date;
969 x_order_header_rec.vendor_id := vendor_id;
970 x_order_header_rec.vendor_name := vendor_name;
971 x_order_header_rec.vendor_site_id := vendor_site_id;
972 x_order_header_rec.vendor_location := vendor_location;
973 x_order_header_rec.CUSTOMER_ID := CUSTOMER_ID;
974 x_order_header_rec.CUSTOMER := CUSTOMER;
975 x_order_header_rec.single_instance_Flag := single_instance_Flag;
976 x_order_header_rec.single_instance_meaning := single_instance_meaning;
977 x_order_header_rec.PO_AGENT_ID := PO_AGENT_ID;
978 x_order_header_rec.buyer_name := buyer_name;
979 x_order_header_rec.PO_HEADER_ID := PO_HEADER_ID;
980 x_order_header_rec.po_number := po_number;
981 x_order_header_rec.po_synch_flag := po_synch_flag;
982 x_order_header_rec.OE_HEADER_ID := OE_HEADER_ID;
983 x_order_header_rec.shipment_number := shipment_number;
984 x_order_header_rec.CONTRACT_ID := CONTRACT_ID;
985 x_order_header_rec.contract_number := contract_number;
986
987 CLOSE l_header_csr;
988
989 IF G_DEBUG='Y' THEN
990 AHL_DEBUG_PUB.debug('After Assign to x_order_header_rec: ' , 'GET_HEADER_AND_LINES: ');
991 END IF;
992
993 ELSE
994 --In Create Mode get Default Values for Order Header
995 x_order_header_rec.order_date := SYSDATE;
996 x_order_header_rec.order_status_code := G_OSP_ENTERED_STATUS;
997
998 OPEN l_default_status_csr;
999 FETCH l_default_status_csr INTO x_order_header_rec.order_status;
1000
1001 OPEN l_default_buyer_csr;
1002 FETCH l_default_buyer_csr INTO x_order_header_rec.PO_AGENT_ID,
1003 x_order_header_rec.buyer_name;
1004 --default vendor name
1005 x_order_header_rec.vendor_name := Get_Suggested_Vendor(p_work_order_id => null,
1006 p_work_order_ids => P_work_order_ids);
1007
1008 END IF;
1009
1010 AHL_OSP_QUERIES_PVT.GET_ORDER_LINES
1011 (
1012 p_api_version => p_api_version ,
1013 p_init_msg_list => p_init_msg_list,
1014 p_commit => p_commit,
1015 p_validation_level => p_validation_level,
1016 p_module_type => p_module_type,
1017 P_osp_id => P_osp_id,
1018 P_work_order_ids => P_work_order_ids,
1019 x_order_lines_tbl => x_order_lines_tbl,
1020 x_return_status => x_return_status,
1021 x_msg_count => x_msg_count,
1022 x_msg_data => x_msg_data
1023
1024 );
1025
1026 IF x_order_lines_tbl.COUNT > 0 THEN
1027 FOR i IN x_order_lines_tbl.FIRST..x_order_lines_tbl.LAST LOOP
1028 IF(x_order_lines_tbl(i).status_code IS NULL) THEN
1029 -- use header status
1030 x_order_lines_tbl(i).status_code := x_order_header_rec.order_status_code;
1031 x_order_lines_tbl(i).status := x_order_header_rec.order_status;
1032 END IF;
1033 END LOOP;
1034 END IF;
1035
1036
1037 -- Check return status.
1038 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1039 RAISE FND_API.G_EXC_ERROR;
1040 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1042 END IF;
1043
1044 -- Standard check of p_commit
1045 /*IF FND_API.TO_BOOLEAN(p_commit) THEN
1046 COMMIT WORK;
1047 END IF;
1048 */
1049
1050 -- Standard call to get message count and if count is 1, get message info
1051 FND_MSG_PUB.Count_And_Get
1052 ( p_count => x_msg_count,
1053 p_data => x_msg_data,
1054 p_encoded => fnd_api.g_false
1055 );
1056
1057 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1058 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'End Procedure');
1059 END IF;
1060
1061 EXCEPTION
1062 WHEN FND_API.G_EXC_ERROR THEN
1063 --dbms_output.put_line('Excep 1 ');
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065 --Rollback to get_header_lines_pvt;
1066 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1067 p_data => x_msg_data,
1068 p_encoded => fnd_api.g_false);
1069 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1070 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, L_DEBUG_KEY, 'Execution Exception: ' || x_msg_data);
1071 END IF;
1072
1073
1074 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1075 -- dbms_output.put_line('Excep 2 ');
1076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1077 -- Rollback to get_header_lines_pvt;
1078 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1079 p_data => x_msg_data,
1080 p_encoded => fnd_api.g_false);
1081 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1082 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
1083 END IF;
1084
1085 WHEN OTHERS THEN
1086 --dbms_output.put_line('Excep 3 ');
1087 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1088 -- Rollback to get_header_lines_pvt;
1089 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
1090 p_procedure_name => 'GET_HEADER_AND_LINES',
1091 p_error_text => SQLERRM);
1092
1093 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1094 p_data => x_msg_data,
1095 p_encoded => fnd_api.g_false);
1096 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1097 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
1098 END IF;
1099 END Get_Header_And_Lines;
1100
1101
1102 ----------------------------------------------------------------------------------
1103 -- PROCEDURE GET_ORDER_LINES
1104 ----------------------------------------------------------------------------------
1105 -- This procedure Search for OSP Order lines based on the input parameter P_osp_id.
1106 -- When the input parameter p_osp_id is null it will use input parameter P_work_order_ids to search for workorders
1107 -- and populate into order line table.
1108
1109 -- Start of Comments --
1110 -- Procedure name : Search_OSP
1111 -- Type : Public
1112 -- Function : Search OSP Order
1113 -- Pre-reqs :
1114 -- Parameters :
1115 --
1116 -- Standard IN Parameters :
1117 -- p_api_version IN NUMBER Required
1118 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1119 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1120 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1121 -- p_module_type IN VARCHAR2 Default Null
1122 -- Standard OUT Parameters :
1123 -- x_return_status OUT VARCHAR2 Required
1124 -- x_msg_count OUT NUMBER Required
1125 -- x_msg_data OUT VARCHAR2 Required
1126 --
1127 -- p_module_type IN VARCHAR2 Required.
1128 -- This parameter indicates the front-end form interface. The default value is null. If the value
1129 -- is JSP, then this API clears out all id columns and validations are done using the values;based
1130 -- on which the Id's are populated.
1131 --
1132 -- Search_OSP Parameters :
1133 -- P_osp_id IN NUMBER, id that the search will be based on.
1134 -- P_work_order_ids IN work_id_tbl_type, List of workorder id that search will be based on if p_osp_id is null
1135 -- x_order_lines_tbl OUT order_line_tbl_Type, Store order order lines rows
1136 -- x_msg_count OUT NUMBER,
1137 -- Version :
1138 -- Initial Version 1.0
1139 --
1140 -- End of Comments.
1141 PROCEDURE GET_ORDER_LINES
1142 (
1143 p_api_version IN NUMBER,
1144 p_init_msg_list IN VARCHAR2,
1145 p_commit IN VARCHAR2,
1146 p_validation_level IN NUMBER,
1147 p_module_type IN VARCHAR2,
1148 P_osp_id IN NUMBER,
1149 P_work_order_ids IN AHL_OSP_QUERIES_PVT.work_id_tbl_type,
1150 x_order_lines_tbl OUT NOCOPY AHL_OSP_QUERIES_PVT.order_line_tbl_Type,
1151 x_return_status OUT NOCOPY VARCHAR2,
1152 x_msg_count OUT NOCOPY NUMBER,
1153 x_msg_data OUT NOCOPY VARCHAR2
1154
1155 ) IS
1156
1157 l_api_version CONSTANT NUMBER := 1.0;
1158 l_api_name CONSTANT VARCHAR2(30) := 'GET_HEADER_AND_LINES';
1159
1160 TYPE line_csr_type IS REF CURSOR;
1161 l_line_csr line_csr_type; --cursor to hold order lines
1162
1163 l_line_queries VARCHAR2(10000);
1164 i NUMBER;
1165 testNum NUMBER;
1166
1167 l_osp_order_line_id NUMBER;
1168 l_object_version_number NUMBER;
1169 l_osp_order_id NUMBER;
1170 l_osp_line_number NUMBER;
1171 l_status_code VARCHAR2(30);
1172 l_status VARCHAR2(80);
1173 l_po_line_type_id NUMBER;
1174 l_po_line_type VARCHAR2(25);
1175 l_service_item_id NUMBER;
1176 l_service_item_number VARCHAR2(40);
1177 l_service_item_description VARCHAR2(2000);
1178 l_service_item_uom_code VARCHAR2(3);
1179 l_need_by_date DATE;
1180 l_ship_by_date DATE;
1181 l_po_line_id NUMBER;
1182 l_oe_ship_line_id NUMBER;
1183 l_oe_return_line_id NUMBER;
1184 l_workorder_id NUMBER;
1185 l_job_number VARCHAR2(80);
1186 l_operation_id NUMBER;
1187 l_attribute_category VARCHAR2(30);
1188 l_wo_part_number VARCHAR2(40);
1189 l_quantity NUMBER;
1190 l_item_instance_id NUMBER;
1191 l_item_instance_number VARCHAR2(30);
1192 l_exchange_instance_number VARCHAR2(30);
1193 l_project_id NUMBER;
1194 l_project_name VARCHAR2(30);
1195 l_PROJECT_TASK_ID NUMBER;
1196 l_PROJECT_TASK_NAME VARCHAR2(20);
1197
1198 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.GET_ORDER_LINES';
1199
1200 BEGIN
1201
1202 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1203 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1204 END IF;
1205 IF G_DEBUG='Y' THEN
1206 AHL_DEBUG_PUB.enable_debug;
1207 END IF;
1208
1209 IF G_DEBUG='Y' THEN
1210 IF G_DEBUG='Y' THEN
1211 AHL_DEBUG_PUB.debug('Begin Get_order_lines', 'GET_ORDER_LINES: ');
1212
1213 END IF;
1214 --i := 0;
1215 IF P_work_order_ids IS NULL THEN
1216 IF G_DEBUG='Y' THEN
1217 AHL_DEBUG_PUB.debug('P_work_order_ids is null', 'GET_ORDER_LINES: ');
1218 END IF;
1219 END IF;
1220
1221 IF G_DEBUG='Y' THEN
1222 AHL_DEBUG_PUB.debug('P_osp_id = ' || P_osp_id, 'GET_ORDER_LINES: ');
1223 END IF;
1224
1225 END IF;
1226
1227 -- Standard call to check for call compatibility
1228 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1229 G_PKG_NAME) THEN
1230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1231 END IF;
1232
1233 -- Initialize message list if p_init_msg_list is set to TRUE
1234 IF FND_API.To_Boolean(p_init_msg_list) THEN
1235 FND_MSG_PUB.Initialize;
1236 END IF;
1237
1238 -- Initialize API return status to success
1239 x_return_status := FND_API.G_RET_STS_SUCCESS;
1240 IF(P_OSP_ID IS NOT NULL) THEN
1241 l_line_queries := 'SELECT OSP_ORDER_LINE_ID ';
1242 l_line_queries := l_line_queries ||', OBJECT_VERSION_NUMBER ';
1243 l_line_queries := l_line_queries ||', OSP_ORDER_ID ';
1244 l_line_queries := l_line_queries ||', OSP_LINE_NUMBER ';
1245 l_line_queries := l_line_queries ||', STATUS_CODE ';
1246 l_line_queries := l_line_queries ||', STATUS ';
1247 l_line_queries := l_line_queries ||', PO_LINE_TYPE_ID ';
1248 l_line_queries := l_line_queries ||', PO_LINE_TYPE ';
1249 l_line_queries := l_line_queries ||', SERVICE_ITEM_ID ';
1250 l_line_queries := l_line_queries ||', SERVICE_ITEM_NUMBER ';
1251 l_line_queries := l_line_queries ||', SERVICE_ITEM_DESCRIPTION ';
1252 l_line_queries := l_line_queries ||', SERVICE_ITEM_UOM_CODE ';
1253 l_line_queries := l_line_queries ||', NEED_BY_DATE ';
1254 l_line_queries := l_line_queries ||', SHIP_BY_DATE ';
1255 l_line_queries := l_line_queries ||', PO_LINE_ID ';
1256 l_line_queries := l_line_queries ||', OE_SHIP_LINE_ID ';
1257 l_line_queries := l_line_queries ||', OE_RETURN_LINE_ID ';
1258 l_line_queries := l_line_queries ||', WORKORDER_ID ';
1259 l_line_queries := l_line_queries ||', JOB_NUMBER ';
1260 l_line_queries := l_line_queries ||', OPERATION_ID ';
1261 l_line_queries := l_line_queries ||', ATTRIBUTE_CATEGORY ';
1262
1263 l_line_queries := l_line_queries ||', WO_PART_NUMBER';
1264 l_line_queries := l_line_queries ||', QUANTITY ';
1265 l_line_queries := l_line_queries ||', ITEM_INSTANCE_ID ';
1266 l_line_queries := l_line_queries ||', ITEM_INSTANCE_NUMBER ';
1267 l_line_queries := l_line_queries ||', EXCHANGE_INSTANCE_NUMBER ';
1268 l_line_queries := l_line_queries ||', PROJECT_ID ';
1269 l_line_queries := l_line_queries ||', PROJECT_NAME ';
1270 l_line_queries := l_line_queries ||', PROJECT_TASK_ID ';
1271 l_line_queries := l_line_queries ||', PROJECT_TASK_NAME ';
1272
1273 l_line_queries := l_line_queries || ' FROM AHL_OSP_ORDER_LINES_V ';
1274 l_line_queries := l_line_queries || ' WHERE OSP_ORDER_ID = ';
1275 l_line_queries := l_line_queries || P_OSP_ID;
1276 l_line_queries := l_line_queries || ' ORDER BY OSP_LINE_NUMBER ';
1277
1278
1279 IF G_DEBUG='Y' THEN
1280 AHL_DEBUG_PUB.debug('l_line_queries: ' || SUBSTR(l_line_queries, 1, 900), 'GET_ORDER_LINES: ');
1281 END IF;
1282
1283 OPEN l_line_csr FOR l_line_queries;
1284 i := 0;
1285 LOOP
1286 FETCH l_line_csr INTO l_osp_order_line_id ,
1287 l_object_version_number,
1288 l_osp_order_id ,
1289 l_osp_line_number ,
1290 l_status_code ,
1291 l_status ,
1292 l_po_line_type_id ,
1293 l_po_line_type ,
1294 l_service_item_id,
1295 l_service_item_number,
1296 l_service_item_description,
1297 l_service_item_uom_code ,
1298 l_need_by_date,
1299 l_ship_by_date ,
1300 l_po_line_id ,
1301 l_oe_ship_line_id ,
1302 l_oe_return_line_id ,
1303 l_workorder_id ,
1304 l_job_number ,
1305 l_operation_id ,
1306 l_attribute_category,
1307 l_wo_part_number ,
1308 l_quantity ,
1309 l_item_instance_id ,
1310 l_item_instance_number ,
1311 l_exchange_instance_number,
1312 l_project_id ,
1313 l_project_name ,
1314 l_PROJECT_TASK_ID ,
1315 l_PROJECT_TASK_NAME ;
1316
1317 EXIT WHEN l_line_csr%NOTFOUND;
1318
1319 IF G_DEBUG='Y' THEN
1320 AHL_DEBUG_PUB.debug('After FETCH Order Lines', 'GET_ORDER_LINES: ');
1321 END IF;
1322
1323 x_order_lines_tbl(i).OSP_ORDER_LINE_ID := l_osp_order_line_id;
1324 x_order_lines_tbl(i).OBJECT_VERSION_NUMBER := l_object_version_number;
1325 x_order_lines_tbl(i).OSP_ORDER_ID := l_osp_order_id ;
1326 x_order_lines_tbl(i).OSP_LINE_NUMBER := l_osp_line_number ;
1327 x_order_lines_tbl(i).STATUS_CODE := l_status_code;
1328 x_order_lines_tbl(i).STATUS := l_status;
1329 x_order_lines_tbl(i).PO_LINE_TYPE_ID := l_po_line_type_id;
1330 x_order_lines_tbl(i).PO_LINE_TYPE := l_po_line_type;
1331 x_order_lines_tbl(i).SERVICE_ITEM_ID := l_service_item_id;
1332 x_order_lines_tbl(i).SERVICE_ITEM_NUMBER := l_service_item_number;
1333 x_order_lines_tbl(i).SERVICE_ITEM_DESCRIPTION := l_service_item_description;
1334 x_order_lines_tbl(i).SERVICE_ITEM_UOM_CODE := l_service_item_uom_code;
1335 x_order_lines_tbl(i).NEED_BY_DATE := l_need_by_date;
1336 x_order_lines_tbl(i).SHIP_BY_DATE := l_ship_by_date;
1337 x_order_lines_tbl(i).PO_LINE_ID := l_po_line_id;
1338 x_order_lines_tbl(i).OE_SHIP_LINE_ID := l_oe_ship_line_id;
1339 x_order_lines_tbl(i).OE_RETURN_LINE_ID := l_oe_return_line_id;
1340 x_order_lines_tbl(i).WORKORDER_ID := l_workorder_id;
1341 x_order_lines_tbl(i).JOB_NUMBER := l_job_number;
1342 x_order_lines_tbl(i).OPERATION_ID := l_operation_id;
1343 x_order_lines_tbl(i).ATTRIBUTE_CATEGORY := l_attribute_category;
1344
1345 x_order_lines_tbl(i).WO_PART_NUMBER := l_wo_part_number ;
1346 x_order_lines_tbl(i).QUANTITY := l_quantity ;
1347 x_order_lines_tbl(i).ITEM_INSTANCE_ID := l_item_instance_id ;
1348 x_order_lines_tbl(i).ITEM_INSTANCE_NUMBER := l_item_instance_number ;
1349 x_order_lines_tbl(i).EXCHANGE_INSTANCE_NUMBER := l_exchange_instance_number ;
1350 x_order_lines_tbl(i).PROJECT_ID := l_project_id ;
1351 x_order_lines_tbl(i).PROJECT_NAME := l_project_name ;
1352 x_order_lines_tbl(i).PRJ_TASK_ID := l_PROJECT_TASK_ID ;
1353 x_order_lines_tbl(i).PRJ_TASK_NAME := l_PROJECT_TASK_NAME ;
1354
1355 IF G_DEBUG='Y' THEN
1356 AHL_DEBUG_PUB.debug('After Assign Order Lines', 'GET_ORDER_LINES: ');
1357 END IF;
1358
1359 i:= i + 1;
1360
1361
1362 END LOOP;
1363
1364 --END IF; --P_OSP_ID NOT NULL
1365
1366 ELSIF (P_work_order_ids.COUNT > 0) THEN
1367 -- testNum := p_work_order_ids(0).work_order_id;
1368
1369 l_line_queries := 'SELECT WO.WORKORDER_ID ';
1370 l_line_queries := l_line_queries ||', WO.JOB_NUMBER';
1371 l_line_queries := l_line_queries ||', WO.WO_PART_NUMBER ';
1372 l_line_queries := l_line_queries ||', WO.ITEM_INSTANCE_ID ';
1373 l_line_queries := l_line_queries ||', WO.item_instance_number ';
1374 l_line_queries := l_line_queries ||', WO.SERVICE_ITEM_ID ';
1375 l_line_queries := l_line_queries ||', WO.SERVICE_ITEM_NUMBER ';
1376 l_line_queries := l_line_queries ||', WO.SERVICE_ITEM_DESCRIPTION ';
1377 l_line_queries := l_line_queries ||', WO.SERVICE_ITEM_UOM ';
1378 l_line_queries := l_line_queries ||', WO.QUANTITY ';
1379 l_line_queries := l_line_queries ||', WO.PROJECT_ID ';
1380 l_line_queries := l_line_queries ||', WO.PROJECT_NAME ';
1381 l_line_queries := l_line_queries ||', WO.PROJECT_TASK_ID ';
1382 l_line_queries := l_line_queries ||', WO.PROJECT_TASK_NAME ';
1383
1384 l_line_queries := l_line_queries || ' FROM AHL_WORKORDERS_OSP_V WO ';
1385 l_line_queries := l_line_queries || ' WHERE ';
1386
1387 l_line_queries := l_line_queries || ' (';
1388 i := 0;
1389 FOR i IN P_work_order_ids.FIRST .. P_work_order_ids.LAST
1390 LOOP
1391 IF (i <> P_work_order_ids.FIRST) THEN
1392 l_line_queries := l_line_queries || ' OR ';
1393 END IF;
1394 l_line_queries := l_line_queries || ' WO.WORKORDER_ID =' || p_work_order_ids(i).work_order_id;
1395 END LOOP;
1396 l_line_queries := l_line_queries || ' )';
1397
1398 IF G_DEBUG='Y' THEN
1399 AHL_DEBUG_PUB.debug('Search_WO: l_line_queries = ' || SUBSTR(l_line_queries, 1, 900));
1400 END IF;
1401
1402 IF G_DEBUG='Y' THEN
1403 AHL_DEBUG_PUB.debug('l_line_queries: ' || SUBSTR(l_line_queries, 1, 900), 'GET_ORDER_LINES: ');
1404 END IF;
1405
1406 OPEN l_line_csr FOR l_line_queries;
1407 i := 0;
1408 LOOP
1409
1410 FETCH l_line_csr INTO l_workorder_id,
1411 l_Job_number,
1412 l_wo_Part_number,
1413 l_item_instance_id,
1414 l_item_instance_number,
1415 l_service_item_id,
1416 l_service_item_number,
1417 l_service_item_description,
1418 l_service_item_uom_code ,
1419 l_quantity,
1420 l_project_id ,
1421 l_Project_Name,
1422 l_PROJECT_TASK_ID,
1423 l_PROJECT_TASK_NAME;
1424
1425 EXIT WHEN l_line_csr%NOTFOUND;
1426
1427 x_order_lines_tbl(i).workorder_id := l_workorder_id;
1428 x_order_lines_tbl(i).Job_number := l_Job_number;
1429 x_order_lines_tbl(i).WO_PART_NUMBER := l_wo_Part_number;
1430 x_order_lines_tbl(i).ITEM_INSTANCE_ID := l_item_instance_id ;
1431 x_order_lines_tbl(i).item_instance_number := l_item_instance_number;
1432 x_order_lines_tbl(i).SERVICE_ITEM_ID := l_service_item_id;
1433 x_order_lines_tbl(i).SERVICE_ITEM_NUMBER := l_service_item_number;
1434 x_order_lines_tbl(i).SERVICE_ITEM_DESCRIPTION := l_service_item_description;
1435 x_order_lines_tbl(i).SERVICE_ITEM_UOM_CODE := l_service_item_uom_code;
1436 x_order_lines_tbl(i).quantity := l_quantity;
1437 x_order_lines_tbl(i).PROJECT_ID := l_project_id ;
1438 x_order_lines_tbl(i).Project_Name := l_Project_Name;
1439 x_order_lines_tbl(i).PRJ_TASK_ID := l_PROJECT_TASK_ID ;
1440 x_order_lines_tbl(i).PRJ_TASK_NAME := l_PROJECT_TASK_NAME;
1441
1442 i := i + 1;
1443 END LOOP; -- END LOOP FOR L_LINE_CSR
1444
1445 END IF;
1446
1447 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1448 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'End Procedure');
1449 END IF;
1450
1451 EXCEPTION
1452 WHEN FND_API.G_EXC_ERROR THEN
1453 --dbms_output.put_line('Excep 1 ');
1454 x_return_status := FND_API.G_RET_STS_ERROR;
1455 --Rollback to get_order_lines_pvt;
1456 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1457 p_data => x_msg_data,
1458 p_encoded => fnd_api.g_false);
1459 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1460 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, L_DEBUG_KEY, 'Execution Exception: ' || x_msg_data);
1461 END IF;
1462
1463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1464 --dbms_output.put_line('Excep 2 ');
1465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1466 --Rollback to get_order_lines_pvt;
1467 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1468 p_data => x_msg_data,
1469 p_encoded => fnd_api.g_false);
1470 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1471 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
1472 END IF;
1473
1474 WHEN OTHERS THEN
1475 --dbms_output.put_line('Excep 3 ');
1476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1477 -- Rollback to get_order_lines_pvt;
1478 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
1479 p_procedure_name => 'GET_ORDER_LINES',
1480 p_error_text => SQLERRM);
1481
1482 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1483 p_data => x_msg_data,
1484 p_encoded => fnd_api.g_false);
1485 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1486 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
1487 END IF;
1488
1489
1490
1491 END GET_ORDER_LINES;
1492
1493 -----------------------------------------------
1494 --This is a published function wrapper just for the convenience usage in view
1495 -----------------------------------------------
1496 FUNCTION Get_Suggested_Vendor(p_work_order_id IN NUMBER)
1497 RETURN VARCHAR2 IS
1498 CURSOR get_items_from_wo IS
1499 --Modified by mpothuku to fix the Perf Bug# 4919307
1500 /*
1501 SELECT inventory_item_id,
1502 organization_id,
1503 service_item_id
1504 FROM ahl_workorders_osp_v
1505 WHERE workorder_id = p_work_order_id;
1506 */
1507 SELECT vts.inventory_item_id,
1508 vst.organization_id,
1509 arb.service_item_id
1510 FROM ahl_workorders wo,
1511 ahl_visits_b vst,
1512 ahl_visit_tasks_b vts,
1513 ahl_routes_b arb
1514 WHERE workorder_id = p_work_order_id
1515 AND wo.visit_task_id = vts.visit_task_id
1516 AND vts.visit_id = vst.visit_id
1517 AND wo.route_id = arb.route_id(+);
1518
1519 CURSOR get_vendor_certs(c_inv_item_id NUMBER, c_inv_org_id NUMBER, c_service_item_id NUMBER) IS
1520 SELECT IV.vendor_certification_id,
1521 IV.rank
1522 FROM ahl_inv_service_item_rels SI,
1523 ahl_item_vendor_rels IV
1524 WHERE SI.inv_service_item_rel_id = IV.inv_service_item_rel_id
1525 AND SI.inv_item_id = c_inv_item_id
1526 AND SI.inv_org_id = c_inv_org_id
1527 AND SI.service_item_id = c_service_item_id
1528 AND trunc(IV.active_start_date) <= trunc(SYSDATE)
1529 AND trunc(nvl(IV.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1530 AND trunc(SI.active_start_date) <= trunc(SYSDATE)
1531 AND trunc(nvl(SI.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1532 ORDER BY IV.rank;
1533 l_get_vendor_certs get_vendor_certs%ROWTYPE;
1534 CURSOR get_vendor_name(c_vendor_cert_id NUMBER) IS
1535 SELECT vendor_name
1536 FROM ahl_vendor_certifications_v
1537 WHERE vendor_certification_id = c_vendor_cert_id
1538 AND trunc(active_start_date) <= trunc(SYSDATE)
1539 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1540 l_vendor_name VARCHAR2(240) := NULL;
1541 l_vendor_cert_id NUMBER := NULL;
1542 l_inv_item_id NUMBER;
1543 l_inv_org_id NUMBER;
1544 l_service_item_id NUMBER;
1545 BEGIN
1546 --The following line is only line in original code, commented out by Jerry on 06/10/2005
1547 --RETURN Get_Suggested_Vendor(p_work_order_id, G_EMPTY_WO_IDS_TABLE);
1548 OPEN get_items_from_wo;
1549 FETCH get_items_from_wo INTO l_inv_item_id, l_inv_org_id, l_service_item_id;
1550 IF (get_items_from_wo%FOUND AND l_service_item_id IS NOT NULL) THEN
1551 OPEN get_vendor_certs(l_inv_item_id, l_inv_org_id, l_service_item_id);
1552 FETCH get_vendor_certs INTO l_get_vendor_certs;
1553 IF get_vendor_certs%FOUND THEN
1554 l_vendor_cert_id := l_get_vendor_certs.vendor_certification_id;
1555 IF l_vendor_cert_id IS NOT NULL THEN
1556 OPEN get_vendor_name(l_vendor_cert_id);
1557 FETCH get_vendor_name INTO l_vendor_name;
1558 CLOSE get_vendor_name;
1559 END IF;
1560 END IF;
1561 CLOSE get_vendor_certs;
1562 END IF;
1563 CLOSE get_items_from_wo;
1564 RETURN l_vendor_name;
1565 END;
1566
1567 /* Added by mpothuku on 03-17-05 for calculating the onhand quantity for an inventory item */
1568 -----------------------------------------------
1569 --Function for calculating the onhand quantity of an item
1570 -----------------------------------------------
1571 FUNCTION Get_Onhand_Quantity(p_org_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_inventory_item_id IN NUMBER,
1572 --Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
1573 p_lot_number IN VARCHAR2)
1574
1575 RETURN NUMBER IS
1576 l_debug_key CONSTANT VARCHAR2(150) := g_log_prefix || '.Get_Onhand_Quantity';
1577 l_onhand_quantity NUMBER;
1578 l_apparent_quantity NUMBER;
1579 --l_quant_withoutship NUMBER;
1580 l_quant_ship_notbooked NUMBER;
1581 l_quant_notshippedout NUMBER;
1582
1583 BEGIN
1584
1585 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1586 fnd_log.string(fnd_log.level_procedure, l_debug_key || '.begin:', 'Entered Get_Onhand_Quantity Function');
1587 END IF;
1588
1589 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1590 fnd_log.string
1591 (
1592 fnd_log.level_statement, l_debug_key|| ':',
1593 'p_org_id: '|| p_org_id ||
1594 'and p_subinventory_code :' || p_subinventory_code ||
1595 'and p_inventory_item_id :' || p_inventory_item_id ||
1596 --Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
1597 'and p_lot_number :' || p_lot_number
1598 );
1599 END IF;
1600
1601 Select nvl(sum(transaction_quantity),0) into l_apparent_quantity from mtl_onhand_quantities where
1602 organization_id = p_org_id and
1603 inventory_item_id = p_inventory_item_id and
1604 subinventory_code = p_subinventory_code and
1605 --Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
1606 (p_lot_number is null or lot_number = p_lot_number);
1607
1608 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1609 fnd_log.string(fnd_log.level_statement, l_debug_key|| ':', 'l_apparent_quantity : '|| l_apparent_quantity);
1610 END IF;
1611
1612 --Gives the quantity of the items without ship lines reserved by osp
1613 /***** For Bug 5673279,
1614 1. we need not deduct this quantity from onhand anymore
1615 Instead we show an indicator in the front end saying that there are Orders already created against this item in the subinventory
1616 in question
1617 2. We need to use this query in the front end to decide on the visibility of the indicator
1618 3. We need to retain the ospl.status_code clause as, if the order does not have shipments and PO is deleted, we consider it released */
1619 /*
1620 Select
1621 nvl(sum(nvl(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty
1622 (ospl.inventory_item_id,ospl.inventory_item_uom,ospl.inventory_item_quantity),0)
1623 ),0) into l_quant_withoutship
1624 from ahl_osp_order_lines ospl, ahl_osp_orders_b osp where
1625 ospl.osp_order_id = osp.osp_order_id and
1626 osp.status_code <> 'CLOSED' and
1627 --Added by mpothuku on 23-Aug-06 to exclude the quantity involved in PO_CANCELLED or PO_DELETED Lines for Bug 5252627
1628 ospl.status_code is null and
1629 --mpothuku End
1630 ospl.oe_ship_line_id is null and
1631 ospl.inventory_org_id = p_org_id and
1632 ospl.inventory_item_id = p_inventory_item_id and
1633 ospl.sub_inventory = p_subinventory_code and
1634 --Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
1635 (p_lot_number is null or ospl.lot_number = p_lot_number);
1636
1637 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1638 fnd_log.string(fnd_log.level_statement, l_debug_key ||':', 'l_quant_withoutship : '|| l_quant_withoutship);
1639 END IF;
1640 */
1641
1642 --Gives the quantity of the items that have ship lines, but whose shipments are not booked.
1643 /***** For Bug 5673279
1644 1. Need to consider the clause for the cases where OE Lines or Orders are deleted from OM forms. For such cases
1645 we still hold the reference in AHL tables.
1646 2. Need to consider the case where the Shipments are cancelled
1647 3. Need to remove the ospl.status code clause as the items are not considered released till the shipments are deleted
1648 and PO deletion no more enables the items to be released for Osp.
1649 */
1650 Select
1651 nvl(sum(nvl(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty
1652 (ospl.inventory_item_id,ospl.inventory_item_uom,ospl.inventory_item_quantity),0)
1653 ),0) into l_quant_ship_notbooked
1654
1655 from ahl_osp_order_lines ospl, ahl_osp_orders_b osp,
1656 /* Fix for the AE Bug 5673279 (Release/Holding of inventory items) */
1657 oe_order_lines_all oel
1658 where
1659 ospl.osp_order_id = osp.osp_order_id and
1660 osp.status_code <> 'CLOSED' and
1661 /* Fix for the AE Bug 5673279 (Release/Holding of inventory items)
1662 --Added by mpothuku on 23-Aug-06 to exclude the quantity involved in PO_CANCELLED or PO_DELETED Lines for Bug 5252627
1663 ospl.status_code is null and
1664 --mpothuku End
1665 */
1666 ospl.oe_ship_line_id is not null and
1667 ospl.inventory_org_id = p_org_id and
1668 ospl.inventory_item_id = p_inventory_item_id and
1669 ospl.sub_inventory = p_subinventory_code and
1670 /* Fix for the AE Bug 5673279 (Release/Holding of inventory items) */
1671 --This join ensure that if the OE ship lines are deleted from the OM forms the quantity is not reserved.
1672 oel.line_id = ospl.OE_SHIP_LINE_ID and
1673 --The order line should not be closed and should not be cancelled to be considered here
1674 --mpothuku 16-Nov-06, following two checks may be redundant as, the order cannot be closed if there are no deliveries
1675 --and unless the order is booked, it cannot be in the cancelled status
1676 (nvl(oel.cancelled_flag, 'N') <> 'Y' OR nvl(oel.flow_status_code, 'XXX') <> 'CANCELLED') and
1677 (oel.open_flag <> 'N' OR nvl(oel.flow_status_code, 'XXX') <> 'CLOSED') and
1678 --Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
1679 (p_lot_number is null or ospl.lot_number = p_lot_number) and
1680 not exists
1681 (select 1 from wsh_delivery_details where SOURCE_CODE = 'OE' and SOURCE_LINE_ID = OSPL.oe_ship_line_id);
1682
1683 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1684 fnd_log.string(fnd_log.level_statement, l_debug_key||':','l_quant_ship_notbooked:'|| l_quant_ship_notbooked);
1685 END IF;
1686
1687 --Gives the quantity of the items that have ship lines, and whose shipments are in those phases, where
1688 --the quantity is not reduced from the inventory yet, but nonetheless reserved.
1689
1690 Select
1691 nvl(sum(nvl(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty
1692 (inventory_item_id, requested_quantity_uom, requested_quantity),0)
1693 ),0) into l_quant_notshippedout
1694
1695 from wsh_delivery_details where
1696 organization_id = p_org_id and
1697 inventory_item_id = p_inventory_item_id and
1698 subinventory = p_subinventory_code and
1699 released_status in ('R','S','Y','C','B') and
1700 --Added by mpothuku on 17th May, 06 to fix the Bug 5231358
1701 (p_lot_number is null or lot_number = p_lot_number);
1702
1703 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1704 fnd_log.string(fnd_log.level_statement, l_debug_key||':','l_quant_notshippedout : '|| l_quant_notshippedout);
1705 END IF;
1706
1707
1708 l_onhand_quantity := l_apparent_quantity - (l_quant_ship_notbooked + l_quant_notshippedout);
1709
1710 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1711 fnd_log.string(fnd_log.level_statement, l_debug_key||':' , 'l_onhand_quantity : '|| l_onhand_quantity);
1712 END IF;
1713
1714 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1715 fnd_log.string(fnd_log.level_procedure, l_debug_key||'.end','End Get_Onhand_Quantity Function');
1716 END IF;
1717
1718 return l_onhand_quantity;
1719
1720 END;
1721
1722
1723 -------------------------------GET SUGGESTED VENDOR--------------------------------------------------
1724
1725 -- Return the suggested vendor for a work order/multiple workorders
1726 -- For a single work order (p_work_order_id parameter):
1727 -- If there are no suggested vendors, returns null
1728 -- If there are multiple suggested vendors, returns '*'
1729 -- If there is only one vendor, returns the vendor name
1730 -- For multiple work orders (p_work_order_ids parameter)
1731 -- If all work orders have the same (not null, not multiple) suggested vendor, that vendor name is returned
1732 -- Else, null is returned
1733 ----------------------------------------------------------------------------
1734 FUNCTION Get_Suggested_Vendor(p_work_order_id IN NUMBER,
1735 p_work_order_ids IN AHL_OSP_QUERIES_PVT.work_id_tbl_type )
1736 RETURN VARCHAR2 IS
1737 l_vendor_name VARCHAR2(240) := NULL;
1738 l_dummy VARCHAR2(80) := NULL;
1739 l_org_id NUMBER;
1740 CURSOR l_approved_vendors_csr(l_workorder_id IN NUMBER,
1741 l_org_id IN NUMBER) IS
1742 /*
1743 SELECT VENDOR_NAME
1744 from PO_VENDORS_VIEW VEN, PO_ASL_STATUSES AST, PO_APPROVED_SUPPLIER_LIST ASL, AHL_WORKORDERS_OSP_V WO
1745 WHERE WO.WORKORDER_ID = l_workorder_id AND
1746 ASL.ITEM_ID = WO.SERVICE_ITEM_ID AND
1747 (ASL.USING_ORGANIZATION_ID = l_org_id OR
1748 (ASL.OWNING_ORGANIZATION_ID = l_org_id AND ASL.USING_ORGANIZATION_ID = -1)) AND
1749 ASL.ASL_STATUS_ID = AST.STATUS_ID AND
1750 AST.STATUS = 'Approved' AND
1751 VEN.VENDOR_ID = ASL.VENDOR_ID AND
1752 VEN.ENABLED_FLAG = 'Y' AND
1753 NVL(VENDOR_START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE AND
1754 NVL(VENDOR_END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE;
1755 */
1756 -- Modified by mpothuku on 13-Mar-06 to fix the Perf Bug #4919307
1757 SELECT VENDOR_NAME
1758 from PO_VENDORS_VIEW VEN,
1759 PO_ASL_STATUSES AST,
1760 PO_APPROVED_SUPPLIER_LIST ASL,
1761 AHL_WORKORDERS WO,
1762 AHL_ROUTES_B arb
1763 WHERE WO.WORKORDER_ID = l_workorder_id
1764 AND arb.route_id = wo.route_id
1765 AND ASL.ITEM_ID = arb.SERVICE_ITEM_ID
1766 AND (ASL.USING_ORGANIZATION_ID = l_org_id
1767 OR (ASL.OWNING_ORGANIZATION_ID = l_org_id
1768 AND ASL.USING_ORGANIZATION_ID = -1))
1769 AND ASL.ASL_STATUS_ID = AST.STATUS_ID
1770 AND AST.STATUS = 'Approved'
1771 AND VEN.VENDOR_ID = ASL.VENDOR_ID
1772 AND VEN.ENABLED_FLAG = 'Y'
1773 AND NVL(VENDOR_START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
1774 AND NVL(VENDOR_END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE;
1775
1776 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Suggested_Vendor';
1777
1778 BEGIN
1779
1780 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1781 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Function');
1782 END IF;
1783
1784 l_org_id := FND_PROFILE.VALUE('ORG_ID');
1785 IF (p_work_order_id IS NOT NULL) THEN
1786 IF (p_work_order_ids IS NOT NULL AND p_work_order_ids.COUNT > 0) THEN
1787 -- Cannot provide values in both the parameters of this function
1788 RAISE TOO_MANY_ROWS;
1789 END IF;
1790 -- Get the Suggested Vendor for the given Work order
1791 OPEN l_approved_vendors_csr(p_work_order_id, l_org_id);
1792 FETCH l_approved_vendors_csr INTO l_vendor_name;
1793 IF (l_approved_vendors_csr%NOTFOUND) THEN
1794 -- No active approved vendor for this item
1795 CLOSE l_approved_vendors_csr;
1796 RETURN null;
1797 ELSE
1798 FETCH l_approved_vendors_csr INTO l_dummy;
1799 IF (l_approved_vendors_csr%NOTFOUND) THEN
1800 -- Only one approved vendor: return this vendor name
1801 CLOSE l_approved_vendors_csr;
1802 RETURN l_vendor_name;
1803 ELSE
1804 -- Multiple approved vendors: Return '*'
1805 CLOSE l_approved_vendors_csr;
1806 RETURN '*';
1807 END IF;
1808 END IF;
1809 ELSE
1810 -- Get the common vendor for all the given work orders
1811 IF (p_work_order_ids IS NULL OR p_work_order_ids.COUNT = 0) THEN
1812 -- Don't throw any error: return null
1813 return NULL;
1814 END IF;
1815 FOR i IN p_work_order_ids.FIRST..p_work_order_ids.LAST LOOP
1816 l_dummy := Get_Suggested_Vendor(p_work_order_id => p_work_order_ids(i).work_order_id,
1817 p_work_order_ids => AHL_OSP_QUERIES_PVT.G_EMPTY_WO_IDS_TABLE);
1818 IF ((l_dummy IS NULL) OR (l_dummy = '*')) THEN
1819 -- No vendor or Multiple vendors
1820 RETURN NULL;
1821 ELSIF ((l_vendor_name IS NOT NULL) AND (l_dummy <> l_vendor_name)) THEN
1822 -- Different Vendors
1823 RETURN NULL;
1824 ELSE
1825 -- Unique (Not null and not multiple) Vendor so far
1826 l_vendor_name := l_dummy;
1827 END IF;
1828 END LOOP;
1829 RETURN l_vendor_name;
1830 END IF;
1831 END Get_Suggested_Vendor;
1832
1833
1834 END AHL_OSP_QUERIES_PVT;