1 PACKAGE BODY AHL_OSP_UTIL_PKG AS
2 /* $Header: AHLVOPUB.pls 120.5 2008/05/05 15:22:47 mpothuku ship $ */
3
4 --G_DEBUG VARCHAR2(1) :=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6
7 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_OSP_UTIL_PKG';
8
9 /* Function declarations */
10 /* Added by jaramana on March 14, 2006 for fixing Perf Bug 4914529 */
11 FUNCTION GET_OSP_LINE_INSTANCE_NUMBER(p_workorder_id IN NUMBER,
12 p_serial_number IN VARCHAR2,
13 p_lot_number IN VARCHAR2) RETURN VARCHAR2;
14
15 --Added by mpothuku on 09-Jan-2008 to implement the Osp Receiving feature
16 FUNCTION GET_IB_SUBTRANS_INSTANCE_ID(p_oe_line_id IN NUMBER) RETURN NUMBER;
17
18 -- Start of Comments --
19 -- Procedure name : Log_Transaction
20 -- Type : Private
21 -- Function : Writes the details about a transaction in the Log Table
22 -- Pre-reqs :
23 -- Parameters :
24 --
25 -- Log_Transaction Parameters:
26 -- p_trans_type_code IN VARCHAR2 Required
27 -- p_src_doc_id IN VARCHAR2 Required
28 -- p_src_doc_type_code IN VARCHAR2 Required
29 -- p_dest_doc_id IN VARCHAR2 Required
30 -- p_dest_doc_type_code IN VARCHAR2 Required
31 -- p_attribute_category IN VARCHAR2 Default NULL
32 -- p_attribute1 IN VARCHAR2 Default NULL
33 -- p_attribute2 IN VARCHAR2 Default NULL
34 -- p_attribute3 IN VARCHAR2 Default NULL
35 -- p_attribute4 IN VARCHAR2 Default NULL
36 -- p_attribute5 IN VARCHAR2 Default NULL
37 -- p_attribute6 IN VARCHAR2 Default NULL
38 -- p_attribute7 IN VARCHAR2 Default NULL
39 -- p_attribute8 IN VARCHAR2 Default NULL
40 -- p_attribute9 IN VARCHAR2 Default NULL
41 -- p_attribute10 IN VARCHAR2 Default NULL
42 -- p_attribute11 IN VARCHAR2 Default NULL
43 -- p_attribute12 IN VARCHAR2 Default NULL
44 -- p_attribute13 IN VARCHAR2 Default NULL
45 -- p_attribute14 IN VARCHAR2 Default NULL
46 -- p_attribute15 IN VARCHAR2 Default NULL
47 --
48 -- Version :
49 -- Initial Version 1.0
50 --
51 -- End of Comments.
52 PROCEDURE Log_Transaction
53 (
54 p_trans_type_code IN VARCHAR2,
55 p_src_doc_id IN NUMBER,
56 p_src_doc_type_code IN VARCHAR2,
57 p_dest_doc_id IN NUMBER,
58 p_dest_doc_type_code IN VARCHAR2,
59 p_attribute_category IN VARCHAR2,
60 p_attribute1 IN VARCHAR2,
61 p_attribute2 IN VARCHAR2,
62 p_attribute3 IN VARCHAR2,
63 p_attribute4 IN VARCHAR2,
64 p_attribute5 IN VARCHAR2,
65 p_attribute6 IN VARCHAR2,
66 p_attribute7 IN VARCHAR2,
67 p_attribute8 IN VARCHAR2,
68 p_attribute9 IN VARCHAR2,
69 p_attribute10 IN VARCHAR2,
70 p_attribute11 IN VARCHAR2,
71 p_attribute12 IN VARCHAR2,
72 p_attribute13 IN VARCHAR2,
73 p_attribute14 IN VARCHAR2,
74 p_attribute15 IN VARCHAR2) IS
75
76 l_osp_order_log_id NUMBER;
77 L_DUMMY_TXN_STATUS_CODE CONSTANT VARCHAR2(30) := 'COMPLETE';
78
79 BEGIN
80
81 AHL_OSP_ORDER_LOGS_PKG.INSERT_ROW(
82 X_OSP_ORDER_LOG_ID => l_osp_order_log_id,
83 X_OBJECT_VERSION_NUMBER => 1,
84 X_LAST_UPDATE_DATE => SYSDATE,
85 X_LAST_UPDATED_BY => fnd_global.user_id,
86 X_CREATION_DATE => SYSDATE,
87 X_CREATED_BY => fnd_global.user_id,
88 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
89 X_TRANSACTION_DATE => SYSDATE,
90 X_TRANSACTION_TYPE_CODE => p_trans_type_code,
91 X_SOURCE_DOCUMENT_ID => p_src_doc_id,
92 X_SOURCE_DOCUMENT_TYPE_CODE => p_src_doc_type_code,
93 X_DESTINATION_DOCUMENT_ID => p_dest_doc_id,
94 X_DEST_DOCUMENT_TYPE_CODE => p_dest_doc_type_code,
95 X_TRANSACTION_STATUS_CODE => L_DUMMY_TXN_STATUS_CODE,
96 X_PROGRAM_ID => AHL_GLOBAL.AHL_OSP_PROGRAM_ID,
97 X_ATTRIBUTE_CATEGORY => p_attribute_category,
98 X_ATTRIBUTE1 => p_attribute1,
99 X_ATTRIBUTE2 => p_attribute2,
100 X_ATTRIBUTE3 => p_attribute3,
101 X_ATTRIBUTE4 => p_attribute4,
102 X_ATTRIBUTE5 => p_attribute5,
103 X_ATTRIBUTE6 => p_attribute6,
104 X_ATTRIBUTE7 => p_attribute7,
105 X_ATTRIBUTE8 => p_attribute8,
106 X_ATTRIBUTE9 => p_attribute9,
107 X_ATTRIBUTE10 => p_attribute10,
108 X_ATTRIBUTE11 => p_attribute11,
109 X_ATTRIBUTE12 => p_attribute12,
110 X_ATTRIBUTE13 => p_attribute13,
111 X_ATTRIBUTE14 => p_attribute14,
112 X_ATTRIBUTE15 => p_attribute15);
113
114 END Log_Transaction;
115
116 -- Start of Comments --
117 -- Procedure name : OPEN_SEARCH_CURSOR
118 -- Type : Public
119 -- Function : Opens a ref cursor that may have zero to a maximum of 16
120 -- dynamic binding variables
121 -- Pre-reqs : Only a maximum of 18 bind variables.
122 -- Parameters :
123 --
124 -- OPEN_SEARCH_CURSOR Parameters:
125 -- p_x_csr IN OUT ahl_search_csr Required
126 -- This is the cursor to be opened
127 -- p_conditions_tbl IN ahp_conditions_tbl Required
128 -- This is the array containing the binding values
129 -- p_sql_str IN VARCHAR2 Required
130 -- This is the sql string with the bind parameters
131 --
132 -- Version :
133 -- Initial Version 1.0
134 --
135 -- End of Comments.
136 PROCEDURE OPEN_SEARCH_CURSOR(p_x_csr IN OUT NOCOPY ahl_search_csr,
137 p_conditions_tbl IN ahl_conditions_tbl,
138 p_sql_str IN VARCHAR2) IS
139
140 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Open_Search_Cursor';
141
142 BEGIN
143 --@@@@@
144 -- dbms_output.put_line('*****Entering OPEN_SEARCH_CURSOR**********');
145 -- dbms_output.put_line('Conditions Table Count = ' || p_conditions_tbl.COUNT);
146 -- FOR i in 1 .. p_conditions_tbl.count LOOP
147 -- dbms_output.put_line('Condition ' || i || ': ' || p_conditions_tbl(i));
148 -- END LOOP;
149 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
150 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
151 END IF;
152
153 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
154 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Conditions Table Count = ' || p_conditions_tbl.COUNT);
155 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'SEARCH_SQL: ' || p_sql_str);
156 FOR i in 1 .. p_conditions_tbl.count LOOP
157 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Condition ' || i || ': ' || p_conditions_tbl(i));
158 END LOOP;
159 END IF;
160
161 IF p_conditions_tbl.COUNT = 0 THEN
162 OPEN p_x_csr FOR p_sql_str;
163 ELSIF p_conditions_tbl.COUNT = 1 THEN
164 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1);
165 ELSIF p_conditions_tbl.COUNT = 2 THEN
166 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
167 p_conditions_tbl(2);
168 ELSIF p_conditions_tbl.COUNT = 3 THEN
169 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
170 p_conditions_tbl(2),
171 p_conditions_tbl(3);
172 ELSIF p_conditions_tbl.COUNT = 4 THEN
173 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
174 p_conditions_tbl(2),
175 p_conditions_tbl(3),
176 p_conditions_tbl(4);
177 ELSIF p_conditions_tbl.COUNT = 5 THEN
178 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
179 p_conditions_tbl(2),
180 p_conditions_tbl(3),
181 p_conditions_tbl(4),
182 p_conditions_tbl(5);
183 ELSIF p_conditions_tbl.COUNT = 6 THEN
184 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
185 p_conditions_tbl(2),
186 p_conditions_tbl(3),
187 p_conditions_tbl(4),
188 p_conditions_tbl(5),
189 p_conditions_tbl(6);
190 ELSIF p_conditions_tbl.COUNT = 7 THEN
191 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
192 p_conditions_tbl(2),
193 p_conditions_tbl(3),
194 p_conditions_tbl(4),
195 p_conditions_tbl(5),
196 p_conditions_tbl(6),
197 p_conditions_tbl(7);
198 ELSIF p_conditions_tbl.COUNT = 8 THEN
199 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
200 p_conditions_tbl(2),
201 p_conditions_tbl(3),
202 p_conditions_tbl(4),
203 p_conditions_tbl(5),
204 p_conditions_tbl(6),
205 p_conditions_tbl(7),
206 p_conditions_tbl(8);
207 ELSIF p_conditions_tbl.COUNT = 9 THEN
208 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
209 p_conditions_tbl(2),
210 p_conditions_tbl(3),
211 p_conditions_tbl(4),
212 p_conditions_tbl(5),
213 p_conditions_tbl(6),
214 p_conditions_tbl(7),
215 p_conditions_tbl(8),
216 p_conditions_tbl(9);
217 ELSIF p_conditions_tbl.COUNT = 10 THEN
218 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
219 p_conditions_tbl(2),
220 p_conditions_tbl(3),
221 p_conditions_tbl(4),
222 p_conditions_tbl(5),
223 p_conditions_tbl(6),
224 p_conditions_tbl(7),
225 p_conditions_tbl(8),
226 p_conditions_tbl(9),
227 p_conditions_tbl(10);
228 ELSIF p_conditions_tbl.COUNT = 11 THEN
229 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
230 p_conditions_tbl(2),
231 p_conditions_tbl(3),
232 p_conditions_tbl(4),
233 p_conditions_tbl(5),
234 p_conditions_tbl(6),
235 p_conditions_tbl(7),
236 p_conditions_tbl(8),
237 p_conditions_tbl(9),
238 p_conditions_tbl(10),
239 p_conditions_tbl(11);
240 ELSIF p_conditions_tbl.COUNT = 12 THEN
241 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
242 p_conditions_tbl(2),
243 p_conditions_tbl(3),
244 p_conditions_tbl(4),
245 p_conditions_tbl(5),
246 p_conditions_tbl(6),
247 p_conditions_tbl(7),
248 p_conditions_tbl(8),
249 p_conditions_tbl(9),
250 p_conditions_tbl(10),
251 p_conditions_tbl(11),
252 p_conditions_tbl(12);
253 ELSIF p_conditions_tbl.COUNT = 13 THEN
254 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
255 p_conditions_tbl(2),
256 p_conditions_tbl(3),
257 p_conditions_tbl(4),
258 p_conditions_tbl(5),
259 p_conditions_tbl(6),
260 p_conditions_tbl(7),
261 p_conditions_tbl(8),
262 p_conditions_tbl(9),
263 p_conditions_tbl(10),
264 p_conditions_tbl(11),
265 p_conditions_tbl(12),
266 p_conditions_tbl(13);
267 ELSIF p_conditions_tbl.COUNT = 14 THEN
268 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
269 p_conditions_tbl(2),
270 p_conditions_tbl(3),
271 p_conditions_tbl(4),
272 p_conditions_tbl(5),
273 p_conditions_tbl(6),
274 p_conditions_tbl(7),
275 p_conditions_tbl(8),
276 p_conditions_tbl(9),
277 p_conditions_tbl(10),
278 p_conditions_tbl(11),
279 p_conditions_tbl(12),
280 p_conditions_tbl(13),
281 p_conditions_tbl(14);
282 ELSIF p_conditions_tbl.COUNT = 15 THEN
283 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
284 p_conditions_tbl(2),
285 p_conditions_tbl(3),
286 p_conditions_tbl(4),
287 p_conditions_tbl(5),
288 p_conditions_tbl(6),
289 p_conditions_tbl(7),
290 p_conditions_tbl(8),
291 p_conditions_tbl(9),
292 p_conditions_tbl(10),
293 p_conditions_tbl(11),
294 p_conditions_tbl(12),
295 p_conditions_tbl(13),
296 p_conditions_tbl(14),
297 p_conditions_tbl(15);
298 ELSIF p_conditions_tbl.COUNT = 16 THEN
299 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
300 p_conditions_tbl(2),
301 p_conditions_tbl(3),
302 p_conditions_tbl(4),
303 p_conditions_tbl(5),
304 p_conditions_tbl(6),
305 p_conditions_tbl(7),
306 p_conditions_tbl(8),
307 p_conditions_tbl(9),
308 p_conditions_tbl(10),
309 p_conditions_tbl(11),
310 p_conditions_tbl(12),
311 p_conditions_tbl(13),
312 p_conditions_tbl(14),
313 p_conditions_tbl(15),
314 p_conditions_tbl(16);
315 ELSIF p_conditions_tbl.COUNT = 17 THEN
316 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
317 p_conditions_tbl(2),
318 p_conditions_tbl(3),
319 p_conditions_tbl(4),
320 p_conditions_tbl(5),
321 p_conditions_tbl(6),
322 p_conditions_tbl(7),
323 p_conditions_tbl(8),
324 p_conditions_tbl(9),
325 p_conditions_tbl(10),
326 p_conditions_tbl(11),
327 p_conditions_tbl(12),
328 p_conditions_tbl(13),
329 p_conditions_tbl(14),
330 p_conditions_tbl(15),
331 p_conditions_tbl(16),
332 p_conditions_tbl(17);
333 ELSIF p_conditions_tbl.COUNT = 18 THEN
334 OPEN p_x_csr FOR p_sql_str USING p_conditions_tbl(1),
335 p_conditions_tbl(2),
336 p_conditions_tbl(3),
337 p_conditions_tbl(4),
338 p_conditions_tbl(5),
339 p_conditions_tbl(6),
340 p_conditions_tbl(7),
341 p_conditions_tbl(8),
342 p_conditions_tbl(9),
343 p_conditions_tbl(10),
344 p_conditions_tbl(11),
345 p_conditions_tbl(12),
346 p_conditions_tbl(13),
347 p_conditions_tbl(14),
348 p_conditions_tbl(15),
349 p_conditions_tbl(16),
350 p_conditions_tbl(17),
351 p_conditions_tbl(18);
352 ELSE
353 -- Error: Too many bind values
354 -- dbms_output.put_line('Error: Too many bind variables');
355 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
356 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Error: Too many bind variables');
357 END IF;
358 null;
359 END IF;
360 -- dbms_output.put_line('*****Exiting OPEN_SEARCH_CURSOR**********');
361 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
362 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
363 END IF;
364
365 END OPEN_SEARCH_CURSOR;
366
367 -- Start of Comments --
368 -- Procedure name : EXEC_IMMEDIATE
369 -- Type : Public
370 -- Function : Does an execute immediate of a SQL statement that returns
371 -- a single number value and that has up to 16 bind variables (0 to 16)
372 -- Pre-reqs :
373 -- Parameters :
374 --
375 -- EXEC_IMMEDIATE Parameters:
376 -- p_conditions_tbl IN ahp_conditions_tbl Required
377 -- This is the array containing the binding values
378 -- p_sql_str IN VARCHAR2 Required
379 -- This is the sql string with the bind parameters
380 -- x_results_count OUT NUMBER Required
381 -- This is the result of the execute immediate operation
382 --
383 -- Version :
384 -- Initial Version 1.0
385 --
386 -- End of Comments.
387 PROCEDURE EXEC_IMMEDIATE(p_conditions_tbl IN ahl_conditions_tbl,
388 p_sql_str IN VARCHAR2,
389 x_results_count OUT NOCOPY NUMBER) IS
390
391 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Exec_Immediate';
392
393 BEGIN
394 -- dbms_output.put_line('*****Entering EXEC_IMMEDIATE**********');
395 -- dbms_output.put_line('Conditions Table Count = ' || p_conditions_tbl.COUNT);
396 -- FOR i in 1 .. p_conditions_tbl.count LOOP
397 -- dbms_output.put_line('Condition ' || i || ': ' || p_conditions_tbl(i));
398 -- END LOOP;
399
400 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
401 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
402 END IF;
403
404 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
405 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Conditions Table Count = ' || p_conditions_tbl.COUNT);
406 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'SEARCH_SQL: ' || p_sql_str);
407 FOR i in 1 .. p_conditions_tbl.count LOOP
408 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Condition ' || i || ': ' || p_conditions_tbl(i));
409 END LOOP;
410 END IF;
411
412 IF p_conditions_tbl.COUNT = 0 THEN
413 EXECUTE IMMEDIATE p_sql_str INTO x_results_count;
414 ELSIF p_conditions_tbl.COUNT = 1 THEN
415 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1);
416 ELSIF p_conditions_tbl.COUNT = 2 THEN
417 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
418 p_conditions_tbl(2);
419 ELSIF p_conditions_tbl.COUNT = 3 THEN
420 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
421 p_conditions_tbl(2),
422 p_conditions_tbl(3);
423 ELSIF p_conditions_tbl.COUNT = 4 THEN
424 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
425 p_conditions_tbl(2),
426 p_conditions_tbl(3),
427 p_conditions_tbl(4);
428 ELSIF p_conditions_tbl.COUNT = 5 THEN
429 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
430 p_conditions_tbl(2),
431 p_conditions_tbl(3),
432 p_conditions_tbl(4),
433 p_conditions_tbl(5);
434 ELSIF p_conditions_tbl.COUNT = 6 THEN
435 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
436 p_conditions_tbl(2),
437 p_conditions_tbl(3),
438 p_conditions_tbl(4),
439 p_conditions_tbl(5),
440 p_conditions_tbl(6);
441 ELSIF p_conditions_tbl.COUNT = 7 THEN
442 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
443 p_conditions_tbl(2),
444 p_conditions_tbl(3),
445 p_conditions_tbl(4),
446 p_conditions_tbl(5),
447 p_conditions_tbl(6),
448 p_conditions_tbl(7);
449 ELSIF p_conditions_tbl.COUNT = 8 THEN
450 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
451 p_conditions_tbl(2),
452 p_conditions_tbl(3),
453 p_conditions_tbl(4),
454 p_conditions_tbl(5),
455 p_conditions_tbl(6),
456 p_conditions_tbl(7),
457 p_conditions_tbl(8);
458 ELSIF p_conditions_tbl.COUNT = 9 THEN
459 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
460 p_conditions_tbl(2),
461 p_conditions_tbl(3),
462 p_conditions_tbl(4),
463 p_conditions_tbl(5),
464 p_conditions_tbl(6),
465 p_conditions_tbl(7),
466 p_conditions_tbl(8),
467 p_conditions_tbl(9);
468 ELSIF p_conditions_tbl.COUNT = 10 THEN
469 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
470 p_conditions_tbl(2),
471 p_conditions_tbl(3),
472 p_conditions_tbl(4),
473 p_conditions_tbl(5),
474 p_conditions_tbl(6),
475 p_conditions_tbl(7),
476 p_conditions_tbl(8),
477 p_conditions_tbl(9),
478 p_conditions_tbl(10);
479 ELSIF p_conditions_tbl.COUNT = 11 THEN
480 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
481 p_conditions_tbl(2),
482 p_conditions_tbl(3),
483 p_conditions_tbl(4),
484 p_conditions_tbl(5),
485 p_conditions_tbl(6),
486 p_conditions_tbl(7),
487 p_conditions_tbl(8),
488 p_conditions_tbl(9),
489 p_conditions_tbl(10),
490 p_conditions_tbl(11);
491 ELSIF p_conditions_tbl.COUNT = 12 THEN
492 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
493 p_conditions_tbl(2),
494 p_conditions_tbl(3),
495 p_conditions_tbl(4),
496 p_conditions_tbl(5),
497 p_conditions_tbl(6),
498 p_conditions_tbl(7),
499 p_conditions_tbl(8),
500 p_conditions_tbl(9),
501 p_conditions_tbl(10),
502 p_conditions_tbl(11),
503 p_conditions_tbl(12);
504 ELSIF p_conditions_tbl.COUNT = 13 THEN
505 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
506 p_conditions_tbl(2),
507 p_conditions_tbl(3),
508 p_conditions_tbl(4),
509 p_conditions_tbl(5),
510 p_conditions_tbl(6),
511 p_conditions_tbl(7),
512 p_conditions_tbl(8),
513 p_conditions_tbl(9),
514 p_conditions_tbl(10),
515 p_conditions_tbl(11),
516 p_conditions_tbl(12),
517 p_conditions_tbl(13);
518 ELSIF p_conditions_tbl.COUNT = 14 THEN
519 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
520 p_conditions_tbl(2),
521 p_conditions_tbl(3),
522 p_conditions_tbl(4),
523 p_conditions_tbl(5),
524 p_conditions_tbl(6),
525 p_conditions_tbl(7),
526 p_conditions_tbl(8),
527 p_conditions_tbl(9),
528 p_conditions_tbl(10),
529 p_conditions_tbl(11),
530 p_conditions_tbl(12),
531 p_conditions_tbl(13),
532 p_conditions_tbl(14);
533 ELSIF p_conditions_tbl.COUNT = 15 THEN
534 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
535 p_conditions_tbl(2),
536 p_conditions_tbl(3),
537 p_conditions_tbl(4),
538 p_conditions_tbl(5),
539 p_conditions_tbl(6),
540 p_conditions_tbl(7),
541 p_conditions_tbl(8),
542 p_conditions_tbl(9),
543 p_conditions_tbl(10),
544 p_conditions_tbl(11),
545 p_conditions_tbl(12),
546 p_conditions_tbl(13),
547 p_conditions_tbl(14),
548 p_conditions_tbl(15);
549 ELSIF p_conditions_tbl.COUNT = 16 THEN
550 EXECUTE IMMEDIATE p_sql_str INTO x_results_count USING p_conditions_tbl(1),
551 p_conditions_tbl(2),
552 p_conditions_tbl(3),
553 p_conditions_tbl(4),
554 p_conditions_tbl(5),
555 p_conditions_tbl(6),
556 p_conditions_tbl(7),
557 p_conditions_tbl(8),
558 p_conditions_tbl(9),
559 p_conditions_tbl(10),
560 p_conditions_tbl(11),
561 p_conditions_tbl(12),
562 p_conditions_tbl(13),
563 p_conditions_tbl(14),
564 p_conditions_tbl(15),
565 p_conditions_tbl(16);
566 ELSE
567 -- Error: Too many bind values
568 -- dbms_output.put_line('Error: Too many bind variables');
569
570 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
571 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Error: Too many bind variables');
572 END IF;
573 null;
574 END IF;
575 -- dbms_output.put_line('*****Exiting EXEC_IMMEDIATE, x_results_count = ' || x_results_count || '**********');
576
577 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
578 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
579 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'x_results_count = ' || x_results_count);
580 END IF;
581 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
582 END IF;
583
584 END EXEC_IMMEDIATE;
585
586 /* Following three functions added by jaramana on March 14, 2006 for fixing Perf Bug 4914529 */
587 /* This function gets the instance number for a Ship (or Return) Line associated to a OSP Line */
588 /* Modified by mpothuku on 09-Jan-2008 to support the implementation of the Osp Receiving feature */
589 FUNCTION GET_SHIP_LINE_INSTANCE_NUMBER(p_ship_line_id IN NUMBER,
590 p_osp_line_id IN NUMBER) RETURN VARCHAR2 IS
591 /*
592 CURSOR get_line_details_csr IS
593 SELECT oe_ship_line_id,
594 oe_return_line_id,
595 workorder_id,
596 serial_number,
597 lot_number,
598 osp_order_id,
599 exchange_instance_id
600 FROM ahl_osp_order_lines
601 WHERE osp_order_line_id = p_osp_line_id;
602
603 CURSOR get_osp_order_type_csr(c_osp_order_id IN NUMBER) IS
604 SELECT order_type_code FROM ahl_osp_orders_b
605 WHERE osp_order_id = c_osp_order_id;
606 */
607
608 CURSOR get_instance_from_id_csr(c_instance_id IN NUMBER) IS
609 SELECT instance_number FROM csi_item_instances
610 WHERE instance_id = c_instance_id;
611
612 --osp_line_details_rec get_line_details_csr%ROWTYPE;
613
614 l_instance_number VARCHAR2(30) := NULL;
615 l_instance_id NUMBER := NULL;
616 --l_order_type VARCHAR2(30);
617
618 BEGIN
619 /*
620 open get_line_details_csr;
621 fetch get_line_details_csr into osp_line_details_rec;
622 if(get_line_details_csr%found) then
623 if(p_ship_line_id = osp_line_details_rec.oe_ship_line_id) then
624 l_instance_number := GET_OSP_LINE_INSTANCE_NUMBER(p_workorder_id => osp_line_details_rec.workorder_id,
625 p_serial_number => osp_line_details_rec.serial_number,
626 p_lot_number => osp_line_details_rec.lot_number);
627 elsif (p_ship_line_id = osp_line_details_rec.oe_return_line_id) then
628 -- Get the order type
629 open get_osp_order_type_csr(osp_line_details_rec.osp_order_id);
630 fetch get_osp_order_type_csr into l_order_type;
631 close get_osp_order_type_csr;
632 if(l_order_type = 'EXCHANGE') then
633 -- Get the instance number from the exchange instance id
634 open get_instance_from_id_csr(osp_line_details_rec.exchange_instance_id);
635 fetch get_instance_from_id_csr into l_instance_number;
636 close get_instance_from_id_csr;
637 else
638 l_instance_number := GET_OSP_LINE_INSTANCE_NUMBER(p_workorder_id => osp_line_details_rec.workorder_id,
639 p_serial_number => osp_line_details_rec.serial_number,
640 p_lot_number => osp_line_details_rec.lot_number);
641 end if;
642 end if;
643 end if;
644 close get_line_details_csr;
645 */
646 l_instance_id := GET_SHIP_LINE_INSTANCE_ID(p_ship_line_id,p_osp_line_id);
647
648 open get_instance_from_id_csr(l_instance_id);
649 fetch get_instance_from_id_csr into l_instance_number;
650 close get_instance_from_id_csr;
651
652 return l_instance_number;
653
654 END GET_SHIP_LINE_INSTANCE_NUMBER;
655
656 --Added by mpothuku on 09-Jan-2008 to implement the Osp Receiving feature
657 FUNCTION GET_SHIP_LINE_INSTANCE_ID(p_ship_line_id IN NUMBER,
658 p_osp_line_id IN NUMBER) RETURN NUMBER IS
659
660 CURSOR get_line_details_csr IS
661 SELECT oe_ship_line_id,
662 oe_return_line_id,
663 workorder_id,
664 serial_number,
665 lot_number,
666 osp_order_id,
667 exchange_instance_id
668 FROM ahl_osp_order_lines
669 WHERE osp_order_line_id = p_osp_line_id;
670
671 CURSOR get_osp_order_type_csr(c_osp_order_id IN NUMBER) IS
672 SELECT order_type_code FROM ahl_osp_orders_b
673 WHERE osp_order_id = c_osp_order_id;
674
675 CURSOR get_instance_from_id_csr(c_instance_id IN NUMBER) IS
676 SELECT instance_number FROM csi_item_instances
677 WHERE instance_id = c_instance_id;
678
679 osp_line_details_rec get_line_details_csr%ROWTYPE;
680
681 l_instance_id NUMBER := NULL;
682 l_order_type VARCHAR2(30);
683
684 BEGIN
685 open get_line_details_csr;
686 fetch get_line_details_csr into osp_line_details_rec;
687 if(get_line_details_csr%found) then
688 if(p_ship_line_id = osp_line_details_rec.oe_ship_line_id) then
689 l_instance_id := GET_IB_SUBTRANS_INSTANCE_ID(p_ship_line_id);
690 elsif (p_ship_line_id = osp_line_details_rec.oe_return_line_id) then
691 -- Get the order type
692 open get_osp_order_type_csr(osp_line_details_rec.osp_order_id);
693 fetch get_osp_order_type_csr into l_order_type;
694 close get_osp_order_type_csr;
695 if(l_order_type = 'EXCHANGE') then
696 l_instance_id := osp_line_details_rec.exchange_instance_id;
697 else
698 -- Get the instance number from the IB Subtransaction itself
699 l_instance_id := GET_IB_SUBTRANS_INSTANCE_ID(p_ship_line_id);
700 end if;
701 end if;
702 end if;
703 close get_line_details_csr;
704 return l_instance_id;
705 END GET_SHIP_LINE_INSTANCE_ID;
706
707 ---------------------------------
708 /* This function gets the serial number for a Ship (or Return) Line associated to a OSP Line */
709 -- Logic:
710 -- decode(ShipmentLineEO.line_id, nvl(ospl.oe_ship_line_id, -1), ospl.serial_number, nvl(ospl.oe_return_line_id, -1), decode(osp.order_type_code, 'EXCHANGE', ospl.exchange_instance_sl_no, ospl.serial_number), null) SERIAL_NUMBER,
711
712 FUNCTION GET_SHIP_LINE_SERIAL_NUMBER(p_ship_line_id IN NUMBER,
713 p_osp_line_id IN NUMBER) RETURN VARCHAR2 IS
714
715 CURSOR get_line_details_csr IS
716 SELECT oe_ship_line_id,
717 oe_return_line_id,
718 serial_number,
719 lot_number,
720 osp_order_id,
721 exchange_instance_id
722 FROM ahl_osp_order_lines
723 WHERE osp_order_line_id = p_osp_line_id;
724
725 CURSOR get_osp_order_type_csr(c_osp_order_id IN NUMBER) IS
726 SELECT order_type_code from ahl_osp_orders_b
727 WHERE osp_order_id = c_osp_order_id;
728
729 CURSOR get_serial_from_instance_csr(c_instance_id IN NUMBER) IS
730 SELECT serial_number FROM csi_item_instances
731 WHERE instance_id = c_instance_id;
732
733 cursor get_serial_from_oelsn_csr IS
734 SELECT FROM_SERIAL_NUMBER from oe_lot_serial_numbers
735 where LINE_ID = p_ship_line_id;
736
737 osp_line_details_rec get_line_details_csr%ROWTYPE;
738
739 l_serial_number VARCHAR2(30) := NULL;
740 l_order_type VARCHAR2(30);
741 l_instance_id NUMBER := NULL;
742
743 BEGIN
744 open get_line_details_csr;
745 fetch get_line_details_csr into osp_line_details_rec;
746 if(get_line_details_csr%found) then
747 if(p_ship_line_id = osp_line_details_rec.oe_ship_line_id) then
748 l_serial_number := osp_line_details_rec.serial_number;
749 elsif (p_ship_line_id = osp_line_details_rec.oe_return_line_id) then
750 -- Get the order type
751 open get_osp_order_type_csr(osp_line_details_rec.osp_order_id);
752 fetch get_osp_order_type_csr into l_order_type;
753 close get_osp_order_type_csr;
754 if(l_order_type = 'EXCHANGE') then
755 -- Get the serial number from the exchange instance id
756 open get_serial_from_instance_csr(osp_line_details_rec.exchange_instance_id);
757 fetch get_serial_from_instance_csr into l_serial_number;
758 close get_serial_from_instance_csr;
759 else
760 --Modified by mpothuku on 09-Jan-2008 to implement the Osp Receiving feature.
761 --If its a service order, we will need to show the serial corresponding to the serial number change (if any)
762 l_instance_id := GET_IB_SUBTRANS_INSTANCE_ID(p_ship_line_id);
763 IF(l_instance_id is not NULL) THEN
764 -- Get the serial number from the l_instance_id
765 open get_serial_from_instance_csr(l_instance_id);
766 fetch get_serial_from_instance_csr into l_serial_number;
767 close get_serial_from_instance_csr;
768 ELSE
769 l_serial_number := osp_line_details_rec.serial_number;
770 END IF;
771 end if;
772 end if;
773 else
774 -- Not OSP Line Based: Get the serial number from oe_lot_serial_numbers
775 -- Note that this oe_lot_serial_numbers applies only for return lines
776 open get_serial_from_oelsn_csr;
777 fetch get_serial_from_oelsn_csr into l_serial_number;
778 close get_serial_from_oelsn_csr;
779 end if;
780 close get_line_details_csr;
781 return l_serial_number;
782 END GET_SHIP_LINE_SERIAL_NUMBER;
783
784 ---------------------------------
785 /* This function gets the lot number for a Ship (or Return) Line associated to a OSP Line */
786 --Added by mpothuku on 05-May-2008 to fix the Bug 6322216
787
788 FUNCTION GET_SHIP_LINE_LOT_NUMBER(p_ship_line_id IN NUMBER,
789 p_osp_line_id IN NUMBER) RETURN VARCHAR2 IS
790
791 CURSOR get_line_details_csr IS
792 SELECT oe_ship_line_id,
793 oe_return_line_id,
794 serial_number,
795 lot_number,
796 osp_order_id,
797 exchange_instance_id
798 FROM ahl_osp_order_lines
799 WHERE osp_order_line_id = p_osp_line_id;
800
801 CURSOR get_osp_order_type_csr(c_osp_order_id IN NUMBER) IS
802 SELECT order_type_code from ahl_osp_orders_b
803 WHERE osp_order_id = c_osp_order_id;
804
805 CURSOR get_lot_num_from_instance_csr(c_instance_id IN NUMBER) IS
806 SELECT lot_number FROM csi_item_instances
807 WHERE instance_id = c_instance_id;
808
809 cursor get_lot_num_from_oelsn_csr IS
810 SELECT LOT_NUMBER from oe_lot_serial_numbers
811 where LINE_ID = p_ship_line_id;
812
813 osp_line_details_rec get_line_details_csr%ROWTYPE;
814
815 l_lot_number VARCHAR2(80) := NULL;
816 l_order_type VARCHAR2(30);
817 l_instance_id NUMBER := NULL;
818
819 BEGIN
820 open get_line_details_csr;
821 fetch get_line_details_csr into osp_line_details_rec;
822 if(get_line_details_csr%found) then
823 if(p_ship_line_id = osp_line_details_rec.oe_ship_line_id) then
824 l_lot_number := osp_line_details_rec.lot_number;
825 elsif (p_ship_line_id = osp_line_details_rec.oe_return_line_id) then
826 -- Get the order type
827 open get_osp_order_type_csr(osp_line_details_rec.osp_order_id);
828 fetch get_osp_order_type_csr into l_order_type;
829 close get_osp_order_type_csr;
830 if(l_order_type = 'EXCHANGE') then
831 -- Get the lot number from the exchange instance id
832 open get_lot_num_from_instance_csr(osp_line_details_rec.exchange_instance_id);
833 fetch get_lot_num_from_instance_csr into l_lot_number;
834 close get_lot_num_from_instance_csr;
835 else
836 --Modified by mpothuku on 09-Jan-2008 to implement the Osp Receiving feature.
837 --If its a service order, we will need to show the lot corresponding to the lot number change (if any)
838 l_instance_id := GET_IB_SUBTRANS_INSTANCE_ID(p_ship_line_id);
839 IF(l_instance_id is not NULL) THEN
840 -- Get the lot number from the l_instance_id
841 open get_lot_num_from_instance_csr(l_instance_id);
842 fetch get_lot_num_from_instance_csr into l_lot_number;
843 close get_lot_num_from_instance_csr;
844 ELSE
845 l_lot_number := osp_line_details_rec.lot_number;
846 END IF;
847 end if;
848 end if;
849 else
850 -- Not OSP Line Based: Get the lot number from oe_lot_serial_numbers
851 -- Note that this oe_lot_serial_numbers applies only for return lines
852 open get_lot_num_from_oelsn_csr;
853 fetch get_lot_num_from_oelsn_csr into l_lot_number;
854 close get_lot_num_from_oelsn_csr;
855 end if;
856 close get_line_details_csr;
857 return l_lot_number;
858 END GET_SHIP_LINE_LOT_NUMBER;
859
860
861 /******* Helper Function *********/
862
863 FUNCTION GET_OSP_LINE_INSTANCE_NUMBER(p_workorder_id IN NUMBER,
864 p_serial_number IN VARCHAR2,
865 p_lot_number IN VARCHAR2) RETURN VARCHAR2 IS
866 CURSOR get_wo_instance_csr IS
867 SELECT csi.instance_number
868 FROM AHL_WORKORDERS WO, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS, CSI_ITEM_INSTANCES CSI
869 WHERE WO.WORKORDER_ID = p_workorder_id AND
870 WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
871 VST.VISIT_ID = VTS.VISIT_ID AND
872 NVL(VTS.INSTANCE_ID, VST.ITEM_INSTANCE_ID) = CSI.INSTANCE_ID;
873
874 CURSOR get_instance_from_sl_csr IS
875 SELECT instance_number FROM CSI_ITEM_INSTANCES
876 WHERE serial_number = p_serial_number;
877
878 CURSOR get_instance_from_lot_csr IS
879 SELECT instance_number FROM csi_item_instances
880 WHERE lot_number = p_lot_number;
881
882 l_instance_number VARCHAR2(30) := null;
883
884 BEGIN
885 if(p_workorder_id is not null) then
886 -- If the line's workorder id is not null: Get the workorder instance
887 open get_wo_instance_csr;
888 fetch get_wo_instance_csr into l_instance_number;
889 close get_wo_instance_csr;
890 else
891 -- If the line's workorder id is null: Get the instance id and instance number from the line's serial number
892 if(p_serial_number is not null) then
893 open get_instance_from_sl_csr;
894 fetch get_instance_from_sl_csr into l_instance_number;
895 close get_instance_from_sl_csr;
896 else
897 -- If line's serial number is null, try to get it from the line's lot number
898 if(p_lot_number is not null) then
899 open get_instance_from_lot_csr;
900 fetch get_instance_from_lot_csr into l_instance_number;
901 close get_instance_from_lot_csr;
902 end if;
903 end if;
904 end if;
905 return l_instance_number;
906 END GET_OSP_LINE_INSTANCE_NUMBER;
907
908 /******* Added by mpothuku on 09-Jan-2008 to implement the Osp Receiving feature *********/
909
910 FUNCTION GET_IB_SUBTRANS_INSTANCE_ID(p_oe_line_id IN NUMBER) RETURN NUMBER IS
911 CURSOR get_IB_subtrans_instanceID_csr IS
912 SELECT tld.instance_id
913 FROM csi_t_transaction_lines tl,
914 csi_t_txn_line_details tld
915 WHERE tl.source_transaction_id = p_oe_line_id
916 AND tl.source_transaction_table = 'OE_ORDER_LINES_ALL'
917 AND tl.transaction_line_id = tld.transaction_line_id;
918
919 l_instance_id NUMBER := null;
920 BEGIN
921 if(p_oe_line_id is not null) then
922 open get_IB_subtrans_instanceID_csr;
923 fetch get_IB_subtrans_instanceID_csr into l_instance_id;
924 close get_IB_subtrans_instanceID_csr;
925 end if;
926 return l_instance_id;
927 END GET_IB_SUBTRANS_INSTANCE_ID;
928
929 --mpothuku End
930
931 END AHL_OSP_UTIL_PKG;
[Home] [Help]