[Home] [Help]
PACKAGE BODY: APPS.QA_SS_LOV
Source
1 package body qa_ss_lov as
2 /* $Header: qltsslvb.plb 120.2 2006/02/06 00:34:22 saugupta noship $ */
3
4 --
5 -- PRIVATE FUNCTIONS AND PROCEDURES HERE
6 --
7
8 --
9 -- Bug 5003509. R12 Performance fix. Obsoleted the function.
10 -- srhariha. Wed Feb 1 03:29:16 PST 2006
11 --
12
13 function Q_Comp_Revision (
14 plan_id_i IN NUMBER DEFAULT NULL,
15 vcharid IN NUMBER DEFAULT NULL,
16 orgz_id IN NUMBER DEFAULT NULL )
17 Return VARCHAR2
18 IS
19 BEGIN
20 RETURN NULL;
21 END;
22 -------------------------------------------------------------------------------------------
23
24 function Q_Comp_Subinventory (
25 plan_id_i IN NUMBER DEFAULT NULL,
26 vcharid IN NUMBER DEFAULT NULL,
27 orgz_id IN NUMBER DEFAULT NULL )
28 Return VARCHAR2
29 IS
30 pos NUMBER;
31 dep_err BOOLEAN;
32 e_value VARCHAR2(150);
33 e_id NUMBER;
34 sql_stmt VARCHAR2(32000);
35
36 BEGIN
37
38 -- Construct the sql (hardcoded)
39 sql_stmt := 'SELECT SECONDARY_INVENTORY_NAME, DESCRIPTION '
40 || ' FROM MTL_SECONDARY_INVENTORIES '
41 || ' WHERE Organization_id = :parameter.org_id '
42 || ' And nvl(disable_date, sysdate+1) > sysdate '
43 || ' order by secondary_inventory_name';
44
45 Return sql_stmt;
46
47 -- return NULL;
48 EXCEPTION
49 WHEN OTHERS THEN
50 htp.p('Exception in Lov package Q_Comp_subinventory');
51 htp.p(SQLERRM);
52 END;
53 -------------------------------------------------------------------------------------------
54 --
55 -- Bug 5003509. R12 Performance fix. Obsoleted the function.
56 -- srhariha. Wed Feb 1 03:29:16 PST 2006
57 --
58
59 function Q_Comp_UOM (
60 plan_id_i IN NUMBER DEFAULT NULL,
61 vcharid IN NUMBER DEFAULT NULL,
62 orgz_id IN NUMBER DEFAULT NULL )
63 Return VARCHAR2
64 IS
65 BEGIN
66 RETURN NULL;
67 END;
68 -------------------------------------------------------------------------------------------
69
70 function Q_Customers (
71 plan_id_i IN NUMBER DEFAULT NULL,
72 vcharid IN NUMBER DEFAULT NULL,
73 orgz_id IN NUMBER DEFAULT NULL )
74 Return VARCHAR2
75 IS
76 pos NUMBER;
77 dep_err BOOLEAN;
78 e_value VARCHAR2(150);
79 e_id NUMBER;
80 sql_stmt VARCHAR2(32000);
81 BEGIN
82 -- Bug 5003509. SQL Repository Fix. Obsoleted the function
83 /*
84 sql_stmt := 'Select Customer_name, customer_Number '
85 || ' From qa_customers_lov_v '
86 || ' where status = ''A'' and '
87 || ' nvl(customer_prospect_code, ''CUSTOMER'') = ''CUSTOMER'' order by '
88 || ' customer_number';
89
90 Return sql_stmt;
91 */
92 return NULL;
93 EXCEPTION
94 WHEN OTHERS THEN
95 htp.p('Exception in Lov package Q_Customers');
96 htp.p(SQLERRM);
97 END;
98 -------------------------------------------------------------------------------------------
99
100 function Q_Department (
101 plan_id_i IN NUMBER DEFAULT NULL,
102 vcharid IN NUMBER DEFAULT NULL,
103 orgz_id IN NUMBER DEFAULT NULL )
104 Return VARCHAR2
105 IS
106 pos NUMBER;
107 dep_err BOOLEAN;
108 e_value VARCHAR2(150);
109 e_id NUMBER;
110 sql_stmt VARCHAR2(32000);
111 BEGIN
112 sql_stmt := 'Select department_code department, description '
113 || ' From bom_departments_val_v '
114 || ' where organization_id = :parameter.org_id '
115 || ' order by department_code';
116
117 Return sql_stmt;
118
119
120 -- return NULL;
121 EXCEPTION
122 WHEN OTHERS THEN
123 htp.p('Exception in Lov package Q_Department');
124 htp.p(SQLERRM);
125 END;
126 -------------------------------------------------------------------------------------------
127 --
128 -- Bug 5003509. R12 Performance fix. Obsoleted the function.
129 -- srhariha. Wed Feb 1 03:29:16 PST 2006
130 --
131
132 function Q_From_Op_Seq_Num (
133 plan_id_i IN NUMBER DEFAULT NULL,
134 vcharid IN NUMBER DEFAULT NULL,
135 orgz_id IN NUMBER DEFAULT NULL )
136 Return VARCHAR2
137 IS
138 BEGIN
139 RETURN NULL;
140 END;
141 -------------------------------------------------------------------------------------------
142
143 function Q_Line (
144 plan_id_i IN NUMBER DEFAULT NULL,
145 vcharid IN NUMBER DEFAULT NULL,
146 orgz_id IN NUMBER DEFAULT NULL )
147 Return VARCHAR2
148 IS
149 sql_stmt VARCHAR2(32000);
150 BEGIN
151 sql_stmt := 'SELECT wl.line_code, wl.description '
152 || ' From wip_lines_val_v wl '
153 || ' where wl.organization_id = :parameter.org_id '
154 || ' order by wl.line_code';
155
156 Return sql_stmt;
157 -- return NULL;
158 EXCEPTION
159 WHEN OTHERS THEN
160 htp.p('Exception in Lov package Q_Line');
161 htp.p(SQLERRM);
162 END;
163 -------------------------------------------------------------------------------------------
164
165 function Q_Lot_Number (
166 plan_id_i IN NUMBER DEFAULT NULL,
167 vcharid IN NUMBER DEFAULT NULL,
168 orgz_id IN NUMBER DEFAULT NULL )
169 Return VARCHAR2
170 IS
171
172 BEGIN
173 -- not supported here
174 -- there is nothing like :mtl_trx_line.transaction_temp_id
175 -- equivalent in selfservice
176 return 'DEP_ERROR';
177 EXCEPTION
178 WHEN OTHERS THEN
179 htp.p('Exception in Lov package Q_Lot_Number');
180 htp.p(SQLERRM);
181 END;
182 -------------------------------------------------------------------------------------------
183
184 function Q_Po_Headers (
185 plan_id_i IN NUMBER DEFAULT NULL,
186 vcharid IN NUMBER DEFAULT NULL,
187 orgz_id IN NUMBER DEFAULT NULL )
188 Return VARCHAR2
189 IS
190 sql_stmt VARCHAR2(32000);
191 BEGIN
192 -- Bug 5003509. SQL Repository Fix. Obsoleted the function
193 /*
194 sql_stmt := 'Select segment1, type_name '
195 || ' From po_pos_val_v '
196 || ' order by segment1';
197
198 Return sql_stmt;
199 */
200 return NULL;
201 EXCEPTION
202 WHEN OTHERS THEN
203 htp.p('Exception in Lov package Q_Po_Headers');
204 htp.p(SQLERRM);
205 END;
206 -------------------------------------------------------------------------------------------
207 --
208 -- Bug 5003509. R12 Performance fix. Obsoleted the function.
209 -- srhariha. Wed Feb 1 03:29:16 PST 2006
210 --
211
212 function Q_Po_Lines (
213 plan_id_i IN NUMBER DEFAULT NULL,
214 vcharid IN NUMBER DEFAULT NULL,
215 orgz_id IN NUMBER DEFAULT NULL )
216 Return VARCHAR2
217 IS
218 BEGIN
219 RETURN NULL;
220 END;
221 -------------------------------------------------------------------------------------------
222 --
223 -- Bug 5003509. R12 Performance fix. Obsoleted the function.
224 -- srhariha. Wed Feb 1 03:29:16 PST 2006
225 --
226
227 function Q_Po_Release_Nums (
228 plan_id_i IN NUMBER DEFAULT NULL,
229 vcharid IN NUMBER DEFAULT NULL,
230 orgz_id IN NUMBER DEFAULT NULL )
231 Return VARCHAR2
232 IS
233 BEGIN
234 RETURN NULL;
235 END;
236 -------------------------------------------------------------------------------------------
237 --
238 -- Bug 5003509. R12 Performance fix. Obsoleted the function.
239 -- srhariha. Wed Feb 1 03:29:16 PST 2006
240 --
241
242 function Q_Po_Shipments (
243 plan_id_i IN NUMBER DEFAULT NULL,
244 vcharid IN NUMBER DEFAULT NULL,
245 orgz_id IN NUMBER DEFAULT NULL )
246 Return VARCHAR2
247 IS
248 BEGIN
249 RETURN NULL;
250 END;
251 -------------------------------------------------------------------------------------------
252
253 function Q_Project (
254 plan_id_i IN NUMBER DEFAULT NULL,
255 vcharid IN NUMBER DEFAULT NULL,
256 orgz_id IN NUMBER DEFAULT NULL )
257 Return VARCHAR2
258 IS
259 sql_stmt VARCHAR2(32000);
260 BEGIN
261 /*
262 mtl_project_v changed to pjm_projects_all_v (selects from both pjm enabled and
263 non-pjm enabled orgs).
264 rkaza, 11/10/2001.
265 */
266 -- Bug 5003509. SQL Repository Fix. Obsoleted the function
267 /*
268 sql_stmt := 'SELECT project_number, project_name '
269 || ' FROM pjm_projects_all_v '
270 || ' Order By project_number ';
271
272 Return sql_stmt;
273 */
274 return NULL;
275 EXCEPTION
276 WHEN OTHERS THEN
277 htp.p('Exception in Lov package Q_Project');
278 htp.p(SQLERRM);
279 END;
280 -------------------------------------------------------------------------------------------
281
282 function Q_Receipt_Nums (
283 plan_id_i IN NUMBER DEFAULT NULL,
284 vcharid IN NUMBER DEFAULT NULL,
285 orgz_id IN NUMBER DEFAULT NULL )
286 Return VARCHAR2
287 IS
288 sql_stmt VARCHAR2(32000);
289 BEGIN
290 -- Bug 5003509. SQL Repository Fix. Obsoleted the function
291 /*
292 sql_stmt :=
293 'SELECT DISTINCT RCVSH.RECEIPT_NUM, POV.VENDOR_NAME
294 FROM RCV_SHIPMENT_HEADERS RCVSH, PO_VENDORS POV, RCV_TRANSACTIONS RT
295 WHERE RCVSH.RECEIPT_SOURCE_CODE = ''VENDOR'' AND
296 RCVSH.VENDOR_ID = POV.VENDOR_ID AND
297 RT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID';
298
299 return sql_stmt;
300 */
301 return NULL;
302 EXCEPTION
303 WHEN OTHERS THEN
304 htp.p('Exception in Lov package Q_Receipt_Nums');
305 htp.p(SQLERRM);
306 END;
307 -------------------------------------------------------------------------------------------
308
309 function Q_Resource (
310 plan_id_i IN NUMBER DEFAULT NULL,
311 vcharid IN NUMBER DEFAULT NULL,
312 orgz_id IN NUMBER DEFAULT NULL )
313 Return VARCHAR2
314 IS
315 sql_stmt VARCHAR2(32000);
316 BEGIN
317 sql_stmt := 'SELECT resource_code Resource, description Description '
318 || ' From BOM_Resources_Val_V '
319 || ' Where organization_id = :parameter.org_id '
320 || ' Order by resource_code ';
321
322 Return sql_stmt;
323 -- return NULL;
324 EXCEPTION
325 WHEN OTHERS THEN
326 htp.p('Exception in Lov package Q_Resource');
327 htp.p(SQLERRM);
328 END;
329 -------------------------------------------------------------------------------------------
330 --
331 -- Bug 5003509. R12 Performance fix. Obsoleted the function.
332 -- srhariha. Wed Feb 1 03:29:16 PST 2006
333 --
334
335 function Q_Revision (
336 plan_id_i IN NUMBER DEFAULT NULL,
337 vcharid IN NUMBER DEFAULT NULL,
338 orgz_id IN NUMBER DEFAULT NULL )
339 Return VARCHAR2
340 IS
341 BEGIN
342 RETURN NULL;
343 END;
344 -------------------------------------------------------------------------------------------
345
346 function Q_Rma_Number (
347 plan_id_i IN NUMBER DEFAULT NULL,
348 vcharid IN NUMBER DEFAULT NULL,
349 orgz_id IN NUMBER DEFAULT NULL )
350 Return VARCHAR2
351 IS
352 sql_stmt VARCHAR2(32000);
353 BEGIN
354 -- Order Management
355 -- Bug 5003509. SQL Repository Fix. Obsoleted the function
356 /*
357 sql_stmt := 'SELECT to_char(SH.ORDER_NUMBER), SOT.NAME '
358 || ' FROM SO_ORDER_TYPES SOT, OE_ORDER_HEADERS SH, '
359 || ' QA_CUSTOMERS_LOV_V RC '
360 || ' WHERE sh.order_type_id = sot.order_type_id and '
361 || ' sh.sold_to_org_id = rc.customer_id and '
362 || ' sh.order_category_code in (''RETURN'',''MIXED'') ';
363 RETURN sql_stmt;
364 */
365 return NULL;
366 EXCEPTION
367 WHEN OTHERS THEN
368 htp.p('Exception in Lov package Q_Rma_Number');
369 htp.p(SQLERRM);
370 END;
371 -------------------------------------------------------------------------------------------
372
373 function Q_Serial_Number (
374 plan_id_i IN NUMBER DEFAULT NULL,
375 vcharid IN NUMBER DEFAULT NULL,
376 orgz_id IN NUMBER DEFAULT NULL )
377 Return VARCHAR2
378 IS
379
380 BEGIN
381 -- not in selfservice - look at lot num for similar comment
382 return 'DEP_ERROR';
383 EXCEPTION
384 WHEN OTHERS THEN
385 htp.p('Exception in Lov package Q_Serial_Number');
386 htp.p(SQLERRM);
387 END;
388 -------------------------------------------------------------------------------------------
389
390 function Q_Sales_Orders (
391 plan_id_i IN NUMBER DEFAULT NULL,
392 vcharid IN NUMBER DEFAULT NULL,
393 orgz_id IN NUMBER DEFAULT NULL )
394 Return VARCHAR2
395 IS
396 sql_stmt VARCHAR2(32000);
397 BEGIN
398 -- Order Management changes
399 -- Bug 5003509. SQL Repository Fix. Obsoleted the function
400 /*
401 sql_stmt := 'SELECT to_char(SH.ORDER_NUMBER), SOT.NAME '
402 || ' FROM SO_ORDER_TYPES SOT, OE_ORDER_HEADERS SH, '
403 || ' QA_CUSTOMERS_LOV_V RC '
404 || ' WHERE sh.order_type_id = sot.order_type_id and '
405 || ' sh.sold_to_org_id = rc.customer_id and '
406 || ' sh.order_category_code in (''ORDER'',''MIXED'') ';
407 RETURN sql_stmt;
408 */
409 return NULL;
410 EXCEPTION
411 WHEN OTHERS THEN
412 htp.p('Exception in Lov package Q_Sales_Orders');
413 htp.p(SQLERRM);
414 END;
415 -------------------------------------------------------------------------------------------
416
417 function Q_Subinventory (
418 plan_id_i IN NUMBER DEFAULT NULL,
419 vcharid IN NUMBER DEFAULT NULL,
420 orgz_id IN NUMBER DEFAULT NULL )
421 Return VARCHAR2
422 IS
423 sql_stmt VARCHAR2(32000);
424 BEGIN
425 sql_stmt := 'SELECT Secondary_Inventory_Name, Description '
426 || ' FROM mtl_secondary_inventories '
427 || ' WHERE organization_id = :parameter.org_id '
428 || ' AND NVL(disable_date, sysdate+1) > sysdate '
429 || ' ORDER BY SECONDARY_INVENTORY_NAME ';
430
431 RETURN sql_stmt;
432 EXCEPTION
433 WHEN OTHERS THEN
434 htp.p('Exception in Lov package Q_Subinventory');
435 htp.p(SQLERRM);
436 END;
437 -------------------------------------------------------------------------------------------
438 --
439 -- Bug 5003509. R12 Performance fix. Obsoleted the function.
440 -- srhariha. Wed Feb 1 03:29:16 PST 2006
441 --
442
443 function Q_To_Op_Seq_Num (
444 plan_id_i IN NUMBER DEFAULT NULL,
445 vcharid IN NUMBER DEFAULT NULL,
446 orgz_id IN NUMBER DEFAULT NULL )
447 Return VARCHAR2
448 IS
449 BEGIN
450 RETURN NULL;
451 END;
452 -------------------------------------------------------------------------------------------
453 --
454 -- Bug 5003509. R12 Performance fix. Obsoleted the function.
455 -- srhariha. Wed Feb 1 03:29:16 PST 2006
456 --
457
458 function Q_UOM (
459 plan_id_i IN NUMBER DEFAULT NULL,
460 vcharid IN NUMBER DEFAULT NULL,
461 orgz_id IN NUMBER DEFAULT NULL )
462 Return VARCHAR2
463 IS
464 BEGIN
465 RETURN NULL;
466 END;
467 -------------------------------------------------------------------------------------------
468
469 function Q_Vendors (
470 plan_id_i IN NUMBER DEFAULT NULL,
471 vcharid IN NUMBER DEFAULT NULL,
472 orgz_id IN NUMBER DEFAULT NULL )
473 Return VARCHAR2
474 IS
475 sql_stmt VARCHAR2(32000);
476 BEGIN
477 -- Bug 5003509. SQL Repository Fix. Obsoleted the function
478 /*
479 sql_stmt := 'SELECT vendor_name, segment1 '
480 || ' FROM po_vendors '
481 || ' order by vendor_name ';
482 Return sql_stmt;
483 */
484 return NULL;
485 EXCEPTION
486 WHEN OTHERS THEN
487 htp.p('Exception in Lov package Q_Vendors');
488 htp.p(SQLERRM);
489 END;
490 -------------------------------------------------------------------------------------------
491
492 function Q_Job (
493 plan_id_i IN NUMBER DEFAULT NULL,
494 vcharid IN NUMBER DEFAULT NULL,
495 orgz_id IN NUMBER DEFAULT NULL )
496 Return VARCHAR2
497 IS
498 sql_stmt VARCHAR2(32000);
499 BEGIN
500
501 -- #2382432
502 -- Changed the view to WIP_DISCRETE_JOBS_ALL_V instead of
503 -- earlier wip_open_discrete_jobs_val_v
504 -- rkunchal Sun Jun 30 22:59:11 PDT 2002
505
506 sql_stmt := 'SELECT wip_entity_name, description '
507 || ' From wip_discrete_jobs_all_v '
508 || ' where organization_id = :parameter.org_id '
509 || ' order by wip_entity_name ';
510
511 Return sql_stmt;
512 -- return NULL;
513 EXCEPTION
514 WHEN OTHERS THEN
515 htp.p('Exception in Lov package Q_Job');
516 htp.p(SQLERRM);
517 END;
518 -------------------------------------------------------------------------------------------
519 --
520 -- Bug 5003509. R12 Performance fix. Obsoleted the function.
521 -- srhariha. Wed Feb 1 03:29:16 PST 2006
522 --
523
524 function Q_Task (
525 plan_id_i IN NUMBER DEFAULT NULL,
526 vcharid IN NUMBER DEFAULT NULL,
527 orgz_id IN NUMBER DEFAULT NULL )
528 Return VARCHAR2
529 IS
530 BEGIN
531 RETURN NULL;
532 END;
533 -------------------------------------------------------------------------------------------
534 function Q_Item (
535 plan_id_i IN NUMBER DEFAULT NULL,
536 vcharid IN NUMBER DEFAULT NULL,
537 orgz_id IN NUMBER DEFAULT NULL )
538 Return VARCHAR2
539 IS
540 sql_stmt VARCHAR2(32000);
541 BEGIN
542 sql_stmt := 'SELECT concatenated_segments, description '
543 || ' From mtl_system_items_b_kfv '
544 || ' where organization_id = :parameter.org_id '
545 || ' order by concatenated_segments ';
546 Return sql_stmt;
547
548 -- return NULL;
549 EXCEPTION
550 WHEN OTHERS THEN
551 htp.p('Exception in Lov package Q_Item');
552 htp.p(SQLERRM);
553 END;
554 -------------------------------------------------------------------------------------------
555
556 --
557 -- PUBLIC FUNCTIONS AND PROCEDURES BELOW AREA
558 --
559
560 procedure gen_list (
561 vchar_id IN qa_chars.char_id%TYPE,
562 rnumb IN NUMBER,
563 cnumb IN NUMBER,
564 find1 IN VARCHAR2
565 )
566
567 IS
568 xyz VARCHAR2(20000);
569 find_str VARCHAR2(2000);
570 BEGIN
571 if (icx_sec.validatesession) then
572
573 find_str := wfa_html.conv_special_url_chars(find1);
574
575 xyz := 'qa_ss_lov.Lov_Header?vchar_id='||vchar_id
576 ||'&' || 'rnumb='||rnumb
577 ||'&' ||'cnumb='||cnumb
578 ||'&' || 'find_str=' || find_str;
579
580
581 htp.p('<HTML>');
582 htp.p('<HEAD>');
583 htp.p('<SCRIPT LANGUAGE="JavaScript">');
584 htp.p('</SCRIPT>');
585 htp.p('</HEAD>');
586 htp.p('<FRAMESET ROWS="70,*">');
587 htp.p('<FRAME NAME="LOVHeader" SRC="'||xyz|| '">');
588 htp.p('<FRAME NAME="LOVValues" SRC="qa_ss_lov.Lov_Values">');
589 htp.p('</FRAMESET>');
590 htp.p('</HTML>');
591
592
593 end if; -- end icx session
594
595 EXCEPTION
596
597 WHEN OTHERS THEN
598 htp.p('Exception in procedure gen_list');
599 htp.p(SQLERRM);
600
601 END gen_list;
602 --------------------------------------------------------------------------------------
603
604 procedure LOV_Header (
605 vchar_id IN qa_chars.char_id%TYPE,
606 rnumb IN NUMBER,
607 cnumb IN NUMBER,
608 find_str IN VARCHAR2
609 )
610
611 IS
612 V_Prompt VARCHAR2(30);
613 msg VARCHAR2(2000);
614 l_language_code VARCHAR2(30);
615 BEGIN
616 if (icx_sec.validatesession) then
617 fnd_message.clear;
618 l_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
619
620 htp.p('<HTML>');
621 htp.p('<HEAD>');
622 htp.p('<SCRIPT LANGUAGE="JavaScript">');
623 htp.p('function Lov_Check(rnumb, strow, lastrow)
624 {
625 document.LOVHeader.x_txn_num.value = parent.opener.document.HiddenRSMDF.x_txn_num.value;
626 document.LOVHeader.x_wip_entity_type.value = parent.opener.document.HiddenRSMDF.x_wip_entity_type.value;
627 document.LOVHeader.x_wip_rep_sch_id.value = parent.opener.document.HiddenRSMDF.x_wip_rep_sch_id.value;
628 document.LOVHeader.x_po_header_id.value = parent.opener.document.HiddenRSMDF.x_po_header_id.value;
629 document.LOVHeader.x_po_release_id.value = parent.opener.document.HiddenRSMDF.x_po_release_id.value;
630 document.LOVHeader.x_po_line_id.value = parent.opener.document.HiddenRSMDF.x_po_line_id.value;
631 document.LOVHeader.x_line_location_id.value = parent.opener.document.HiddenRSMDF.x_line_location_id.value;
632 document.LOVHeader.x_po_distribution_id.value = parent.opener.document.HiddenRSMDF.x_po_distribution_id.value;
633 document.LOVHeader.x_item_id.value = parent.opener.document.HiddenRSMDF.x_item_id.value;
634 document.LOVHeader.x_wip_entity_id.value = parent.opener.document.HiddenRSMDF.x_wip_entity_id.value;
635 document.LOVHeader.x_wip_line_id.value = parent.opener.document.HiddenRSMDF.x_wip_line_id.value;
636 document.LOVHeader.x_po_shipment_id.value = parent.opener.document.HiddenRSMDF.x_po_shipment_id.value;
637
638 document.LOVHeader.start_row.value = strow;
639 document.LOVHeader.p_end_row.value = lastrow;
640 document.LOVHeader.Flag.value = 1; //indicator to lov_values procedure
641 document.LOVHeader.orgz_id.value = parent.opener.document.RSMDF.orgz_id.value;
642 document.LOVHeader.plan_id_i.value = parent.opener.document.RSMDF.hid_planid.value;
643 // copy values to hidden form elements
644 document.LOVHeader.p1.value = parent.opener.document.RSMDF.p_col1[rnumb].value;
645 document.LOVHeader.p2.value = parent.opener.document.RSMDF.p_col2[rnumb].value;
646 document.LOVHeader.p3.value = parent.opener.document.RSMDF.p_col3[rnumb].value;
647 document.LOVHeader.p4.value = parent.opener.document.RSMDF.p_col4[rnumb].value;
648 document.LOVHeader.p5.value = parent.opener.document.RSMDF.p_col5[rnumb].value;
649 document.LOVHeader.p6.value = parent.opener.document.RSMDF.p_col6[rnumb].value;
650 document.LOVHeader.p7.value = parent.opener.document.RSMDF.p_col7[rnumb].value;
651 document.LOVHeader.p8.value = parent.opener.document.RSMDF.p_col8[rnumb].value;
652 document.LOVHeader.p9.value = parent.opener.document.RSMDF.p_col9[rnumb].value;
653 document.LOVHeader.p10.value = parent.opener.document.RSMDF.p_col10[rnumb].value;
654 document.LOVHeader.p11.value = parent.opener.document.RSMDF.p_col11[rnumb].value;
655 document.LOVHeader.p12.value = parent.opener.document.RSMDF.p_col12[rnumb].value;
656 document.LOVHeader.p13.value = parent.opener.document.RSMDF.p_col13[rnumb].value;
657 document.LOVHeader.p14.value = parent.opener.document.RSMDF.p_col14[rnumb].value;
658 document.LOVHeader.p15.value = parent.opener.document.RSMDF.p_col15[rnumb].value;
659 document.LOVHeader.p16.value = parent.opener.document.RSMDF.p_col16[rnumb].value;
660 document.LOVHeader.p17.value = parent.opener.document.RSMDF.p_col17[rnumb].value;
661 document.LOVHeader.p18.value = parent.opener.document.RSMDF.p_col18[rnumb].value;
662 document.LOVHeader.p19.value = parent.opener.document.RSMDF.p_col19[rnumb].value;
663 document.LOVHeader.p20.value = parent.opener.document.RSMDF.p_col20[rnumb].value;
664 document.LOVHeader.p21.value = parent.opener.document.RSMDF.p_col21[rnumb].value;
665 document.LOVHeader.p22.value = parent.opener.document.RSMDF.p_col22[rnumb].value;
666 document.LOVHeader.p23.value = parent.opener.document.RSMDF.p_col23[rnumb].value;
667 document.LOVHeader.p24.value = parent.opener.document.RSMDF.p_col24[rnumb].value;
668 document.LOVHeader.p25.value = parent.opener.document.RSMDF.p_col25[rnumb].value;
669 document.LOVHeader.p26.value = parent.opener.document.RSMDF.p_col26[rnumb].value;
670 document.LOVHeader.p27.value = parent.opener.document.RSMDF.p_col27[rnumb].value;
671 document.LOVHeader.p28.value = parent.opener.document.RSMDF.p_col28[rnumb].value;
672 document.LOVHeader.p29.value = parent.opener.document.RSMDF.p_col29[rnumb].value;
673 document.LOVHeader.p30.value = parent.opener.document.RSMDF.p_col30[rnumb].value;
674 document.LOVHeader.p31.value = parent.opener.document.RSMDF.p_col31[rnumb].value;
675 document.LOVHeader.p32.value = parent.opener.document.RSMDF.p_col32[rnumb].value;
676 document.LOVHeader.p33.value = parent.opener.document.RSMDF.p_col33[rnumb].value;
677 document.LOVHeader.p34.value = parent.opener.document.RSMDF.p_col34[rnumb].value;
678 document.LOVHeader.p35.value = parent.opener.document.RSMDF.p_col35[rnumb].value;
679 document.LOVHeader.p36.value = parent.opener.document.RSMDF.p_col36[rnumb].value;
680 document.LOVHeader.p37.value = parent.opener.document.RSMDF.p_col37[rnumb].value;
681 document.LOVHeader.p38.value = parent.opener.document.RSMDF.p_col38[rnumb].value;
682 document.LOVHeader.p39.value = parent.opener.document.RSMDF.p_col39[rnumb].value;
683 document.LOVHeader.p40.value = parent.opener.document.RSMDF.p_col40[rnumb].value;
684 document.LOVHeader.p41.value = parent.opener.document.RSMDF.p_col41[rnumb].value;
685 document.LOVHeader.p42.value = parent.opener.document.RSMDF.p_col42[rnumb].value;
686 document.LOVHeader.p43.value = parent.opener.document.RSMDF.p_col43[rnumb].value;
687 document.LOVHeader.p44.value = parent.opener.document.RSMDF.p_col44[rnumb].value;
688 document.LOVHeader.p45.value = parent.opener.document.RSMDF.p_col45[rnumb].value;
689 document.LOVHeader.p46.value = parent.opener.document.RSMDF.p_col46[rnumb].value;
690 document.LOVHeader.p47.value = parent.opener.document.RSMDF.p_col47[rnumb].value;
691 document.LOVHeader.p48.value = parent.opener.document.RSMDF.p_col48[rnumb].value;
692 document.LOVHeader.p49.value = parent.opener.document.RSMDF.p_col49[rnumb].value;
693 document.LOVHeader.p50.value = parent.opener.document.RSMDF.p_col50[rnumb].value;
694 document.LOVHeader.p51.value = parent.opener.document.RSMDF.p_col51[rnumb].value;
695 document.LOVHeader.p52.value = parent.opener.document.RSMDF.p_col52[rnumb].value;
696 document.LOVHeader.p53.value = parent.opener.document.RSMDF.p_col53[rnumb].value;
697 document.LOVHeader.p54.value = parent.opener.document.RSMDF.p_col54[rnumb].value;
698 document.LOVHeader.p55.value = parent.opener.document.RSMDF.p_col55[rnumb].value;
699 document.LOVHeader.p56.value = parent.opener.document.RSMDF.p_col56[rnumb].value;
700 document.LOVHeader.p57.value = parent.opener.document.RSMDF.p_col57[rnumb].value;
701 document.LOVHeader.p58.value = parent.opener.document.RSMDF.p_col58[rnumb].value;
702 document.LOVHeader.p59.value = parent.opener.document.RSMDF.p_col59[rnumb].value;
703 document.LOVHeader.p60.value = parent.opener.document.RSMDF.p_col60[rnumb].value;
704 document.LOVHeader.p61.value = parent.opener.document.RSMDF.p_col61[rnumb].value;
705 document.LOVHeader.p62.value = parent.opener.document.RSMDF.p_col62[rnumb].value;
706 document.LOVHeader.p63.value = parent.opener.document.RSMDF.p_col63[rnumb].value;
707 document.LOVHeader.p64.value = parent.opener.document.RSMDF.p_col64[rnumb].value;
708 document.LOVHeader.p65.value = parent.opener.document.RSMDF.p_col65[rnumb].value;
709 document.LOVHeader.p66.value = parent.opener.document.RSMDF.p_col66[rnumb].value;
710 document.LOVHeader.p67.value = parent.opener.document.RSMDF.p_col67[rnumb].value;
711 document.LOVHeader.p68.value = parent.opener.document.RSMDF.p_col68[rnumb].value;
712 document.LOVHeader.p69.value = parent.opener.document.RSMDF.p_col69[rnumb].value;
713 document.LOVHeader.p70.value = parent.opener.document.RSMDF.p_col70[rnumb].value;
714 document.LOVHeader.p71.value = parent.opener.document.RSMDF.p_col71[rnumb].value;
715 document.LOVHeader.p72.value = parent.opener.document.RSMDF.p_col72[rnumb].value;
716 document.LOVHeader.p73.value = parent.opener.document.RSMDF.p_col73[rnumb].value;
717 document.LOVHeader.p74.value = parent.opener.document.RSMDF.p_col74[rnumb].value;
718 document.LOVHeader.p75.value = parent.opener.document.RSMDF.p_col75[rnumb].value;
719 document.LOVHeader.p76.value = parent.opener.document.RSMDF.p_col76[rnumb].value;
720 document.LOVHeader.p77.value = parent.opener.document.RSMDF.p_col77[rnumb].value;
721 document.LOVHeader.p78.value = parent.opener.document.RSMDF.p_col78[rnumb].value;
722 document.LOVHeader.p79.value = parent.opener.document.RSMDF.p_col79[rnumb].value;
723 document.LOVHeader.p80.value = parent.opener.document.RSMDF.p_col80[rnumb].value;
724 document.LOVHeader.p81.value = parent.opener.document.RSMDF.p_col81[rnumb].value;
725 document.LOVHeader.p82.value = parent.opener.document.RSMDF.p_col82[rnumb].value;
726 document.LOVHeader.p83.value = parent.opener.document.RSMDF.p_col83[rnumb].value;
727 document.LOVHeader.p84.value = parent.opener.document.RSMDF.p_col84[rnumb].value;
728 document.LOVHeader.p85.value = parent.opener.document.RSMDF.p_col85[rnumb].value;
729 document.LOVHeader.p86.value = parent.opener.document.RSMDF.p_col86[rnumb].value;
730 document.LOVHeader.p87.value = parent.opener.document.RSMDF.p_col87[rnumb].value;
731 document.LOVHeader.p88.value = parent.opener.document.RSMDF.p_col88[rnumb].value;
732 document.LOVHeader.p89.value = parent.opener.document.RSMDF.p_col89[rnumb].value;
733 document.LOVHeader.p90.value = parent.opener.document.RSMDF.p_col90[rnumb].value;
734 document.LOVHeader.p91.value = parent.opener.document.RSMDF.p_col91[rnumb].value;
735 document.LOVHeader.p92.value = parent.opener.document.RSMDF.p_col92[rnumb].value;
736 document.LOVHeader.p93.value = parent.opener.document.RSMDF.p_col93[rnumb].value;
737 document.LOVHeader.p94.value = parent.opener.document.RSMDF.p_col94[rnumb].value;
738 document.LOVHeader.p95.value = parent.opener.document.RSMDF.p_col95[rnumb].value;
739 document.LOVHeader.p96.value = parent.opener.document.RSMDF.p_col96[rnumb].value;
740 document.LOVHeader.p97.value = parent.opener.document.RSMDF.p_col97[rnumb].value;
741 document.LOVHeader.p98.value = parent.opener.document.RSMDF.p_col98[rnumb].value;
742 document.LOVHeader.p99.value = parent.opener.document.RSMDF.p_col99[rnumb].value;
743 document.LOVHeader.p100.value = parent.opener.document.RSMDF.p_col100[rnumb].value;
744 document.LOVHeader.p101.value = parent.opener.document.RSMDF.p_col101[rnumb].value;
745 document.LOVHeader.p102.value = parent.opener.document.RSMDF.p_col102[rnumb].value;
746 document.LOVHeader.p103.value = parent.opener.document.RSMDF.p_col103[rnumb].value;
747 document.LOVHeader.p104.value = parent.opener.document.RSMDF.p_col104[rnumb].value;
748 document.LOVHeader.p105.value = parent.opener.document.RSMDF.p_col105[rnumb].value;
749 document.LOVHeader.p106.value = parent.opener.document.RSMDF.p_col106[rnumb].value;
750 document.LOVHeader.p107.value = parent.opener.document.RSMDF.p_col107[rnumb].value;
751 document.LOVHeader.p108.value = parent.opener.document.RSMDF.p_col108[rnumb].value;
752 document.LOVHeader.p109.value = parent.opener.document.RSMDF.p_col109[rnumb].value;
753 document.LOVHeader.p110.value = parent.opener.document.RSMDF.p_col110[rnumb].value;
754 document.LOVHeader.p111.value = parent.opener.document.RSMDF.p_col111[rnumb].value;
755 document.LOVHeader.p112.value = parent.opener.document.RSMDF.p_col112[rnumb].value;
756 document.LOVHeader.p113.value = parent.opener.document.RSMDF.p_col113[rnumb].value;
757 document.LOVHeader.p114.value = parent.opener.document.RSMDF.p_col114[rnumb].value;
758 document.LOVHeader.p115.value = parent.opener.document.RSMDF.p_col115[rnumb].value;
759 document.LOVHeader.p116.value = parent.opener.document.RSMDF.p_col116[rnumb].value;
760 document.LOVHeader.p117.value = parent.opener.document.RSMDF.p_col117[rnumb].value;
761 document.LOVHeader.p118.value = parent.opener.document.RSMDF.p_col118[rnumb].value;
762 document.LOVHeader.p119.value = parent.opener.document.RSMDF.p_col119[rnumb].value;
763 document.LOVHeader.p120.value = parent.opener.document.RSMDF.p_col120[rnumb].value;
764 document.LOVHeader.p121.value = parent.opener.document.RSMDF.p_col121[rnumb].value;
765 document.LOVHeader.p122.value = parent.opener.document.RSMDF.p_col122[rnumb].value;
766 document.LOVHeader.p123.value = parent.opener.document.RSMDF.p_col123[rnumb].value;
767 document.LOVHeader.p124.value = parent.opener.document.RSMDF.p_col124[rnumb].value;
768 document.LOVHeader.p125.value = parent.opener.document.RSMDF.p_col125[rnumb].value;
769 document.LOVHeader.p126.value = parent.opener.document.RSMDF.p_col126[rnumb].value;
770 document.LOVHeader.p127.value = parent.opener.document.RSMDF.p_col127[rnumb].value;
771 document.LOVHeader.p128.value = parent.opener.document.RSMDF.p_col128[rnumb].value;
772 document.LOVHeader.p129.value = parent.opener.document.RSMDF.p_col129[rnumb].value;
773 document.LOVHeader.p130.value = parent.opener.document.RSMDF.p_col130[rnumb].value;
774 document.LOVHeader.p131.value = parent.opener.document.RSMDF.p_col131[rnumb].value;
775 document.LOVHeader.p132.value = parent.opener.document.RSMDF.p_col132[rnumb].value;
776 document.LOVHeader.p133.value = parent.opener.document.RSMDF.p_col133[rnumb].value;
777 document.LOVHeader.p134.value = parent.opener.document.RSMDF.p_col134[rnumb].value;
778 document.LOVHeader.p135.value = parent.opener.document.RSMDF.p_col135[rnumb].value;
779 document.LOVHeader.p136.value = parent.opener.document.RSMDF.p_col136[rnumb].value;
780 document.LOVHeader.p137.value = parent.opener.document.RSMDF.p_col137[rnumb].value;
781 document.LOVHeader.p138.value = parent.opener.document.RSMDF.p_col138[rnumb].value;
782 document.LOVHeader.p139.value = parent.opener.document.RSMDF.p_col139[rnumb].value;
783 document.LOVHeader.p140.value = parent.opener.document.RSMDF.p_col140[rnumb].value;
784
785 document.LOVHeader.submit(); // this will call Lov_Values plsql procedure
786 // alert (parent.opener.document.RSMDF.p_col2[rnumb].value);
787
788
789 }');
790 htp.p('function queryText()
791 {
792 document.write("queryText function");
793 }');
794
795 htp.p('</SCRIPT>');
796 htp.p('</HEAD>');
797 htp.p('<BODY bgcolor="#cccccc">');
798 htp.p('<FORM ACTION="qa_ss_lov.Lov_Values" METHOD="POST"
799 NAME="LOVHeader" TARGET="LOVValues">');
800
801 -- Hidden elements in the LOVHeaderForm
802 htp.formHidden('vchar_id', vchar_id);
803 htp.formHidden('rnumb', rnumb);
804 htp.formHidden('cnumb', cnumb);
805 htp.formHidden('orgz_id'); -- set in JS
806 htp.formHidden('plan_id_i'); -- set in JS
807 htp.formHidden('Flag'); -- value will be set inside Javascript
808 htp.formHidden('start_row', '1');
809 htp.formHidden('p_end_row');
810 htp.formHidden('x_txn_num');
811 htp.formHidden('x_wip_entity_type');
812 htp.formHidden('x_wip_rep_sch_id');
813 htp.formHidden('x_po_header_id');
814 htp.formHidden('x_po_release_id');
815 htp.formHidden('x_po_line_id');
816 htp.formHidden('x_line_location_id');
817 htp.formHidden('x_po_distribution_id'); -- Primary Key for OSP
818 htp.formHidden('x_item_id');
819 htp.formHidden('x_wip_entity_id');
820 htp.formHidden('x_wip_line_id'); -- This is Production Line
821 htp.formHidden('x_po_shipment_id'); -- Primary Key for SS Shipments
822
823 htp.formHidden('p1'); -- values for p1 to p160 will be set inside Javascript
824 htp.formHidden('p2');
825 htp.formHidden('p3');
826 htp.formHidden('p4');
827 htp.formHidden('p5');
828 htp.formHidden('p6');
829 htp.formHidden('p7');
830 htp.formHidden('p8');
831 htp.formHidden('p9');
832 htp.formHidden('p10');
833 htp.formHidden('p11');
834 htp.formHidden('p12');
835 htp.formHidden('p13');
836 htp.formHidden('p14');
837 htp.formHidden('p15');
838 htp.formHidden('p16');
839 htp.formHidden('p17');
840 htp.formHidden('p18');
841 htp.formHidden('p19');
842 htp.formHidden('p20');
843 htp.formHidden('p21');
844 htp.formHidden('p22');
845 htp.formHidden('p23');
846 htp.formHidden('p24');
847 htp.formHidden('p25');
848 htp.formHidden('p26');
849 htp.formHidden('p27');
850 htp.formHidden('p28');
851 htp.formHidden('p29');
852 htp.formHidden('p30');
853 htp.formHidden('p31');
854 htp.formHidden('p32');
855 htp.formHidden('p33');
856 htp.formHidden('p34');
857 htp.formHidden('p35');
858 htp.formHidden('p36');
859 htp.formHidden('p37');
860 htp.formHidden('p38');
861 htp.formHidden('p39');
862 htp.formHidden('p40');
863 htp.formHidden('p41');
864 htp.formHidden('p42');
865 htp.formHidden('p43');
866 htp.formHidden('p44');
867 htp.formHidden('p45');
868 htp.formHidden('p46');
869 htp.formHidden('p47');
870 htp.formHidden('p48');
871 htp.formHidden('p49');
872 htp.formHidden('p50');
873 htp.formHidden('p51');
874 htp.formHidden('p52');
875 htp.formHidden('p53');
876 htp.formHidden('p54');
877 htp.formHidden('p55');
878 htp.formHidden('p56');
879 htp.formHidden('p57');
880 htp.formHidden('p58');
881 htp.formHidden('p59');
882 htp.formHidden('p60');
883 htp.formHidden('p61');
884 htp.formHidden('p62');
885 htp.formHidden('p63');
886 htp.formHidden('p64');
887 htp.formHidden('p65');
888 htp.formHidden('p66');
889 htp.formHidden('p67');
890 htp.formHidden('p68');
891 htp.formHidden('p69');
892 htp.formHidden('p70');
893 htp.formHidden('p71');
894 htp.formHidden('p72');
895 htp.formHidden('p73');
896 htp.formHidden('p74');
897 htp.formHidden('p75');
898 htp.formHidden('p76');
899 htp.formHidden('p77');
900 htp.formHidden('p78');
901 htp.formHidden('p79');
902 htp.formHidden('p80');
903 htp.formHidden('p81');
904 htp.formHidden('p82');
905 htp.formHidden('p83');
906 htp.formHidden('p84');
907 htp.formHidden('p85');
908 htp.formHidden('p86');
909 htp.formHidden('p87');
910 htp.formHidden('p88');
911 htp.formHidden('p89');
912 htp.formHidden('p90');
913 htp.formHidden('p91');
914 htp.formHidden('p92');
915 htp.formHidden('p93');
916 htp.formHidden('p94');
917 htp.formHidden('p95');
918 htp.formHidden('p96');
919 htp.formHidden('p97');
920 htp.formHidden('p98');
921 htp.formHidden('p99');
922 htp.formHidden('p100');
923 htp.formHidden('p101');
924 htp.formHidden('p102');
925 htp.formHidden('p103');
926 htp.formHidden('p104');
927 htp.formHidden('p105');
928 htp.formHidden('p106');
929 htp.formHidden('p107');
930 htp.formHidden('p108');
931 htp.formHidden('p109');
932 htp.formHidden('p110');
933 htp.formHidden('p111');
934 htp.formHidden('p112');
935 htp.formHidden('p113');
936 htp.formHidden('p114');
937 htp.formHidden('p115');
938 htp.formHidden('p116');
939 htp.formHidden('p117');
940 htp.formHidden('p118');
941 htp.formHidden('p119');
942 htp.formHidden('p120');
943 htp.formHidden('p121');
944 htp.formHidden('p122');
945 htp.formHidden('p123');
946 htp.formHidden('p124');
947 htp.formHidden('p125');
948 htp.formHidden('p126');
949 htp.formHidden('p127');
950 htp.formHidden('p128');
951 htp.formHidden('p129');
952 htp.formHidden('p130');
953 htp.formHidden('p131');
954 htp.formHidden('p132');
955 htp.formHidden('p133');
956 htp.formHidden('p134');
957 htp.formHidden('p135');
958 htp.formHidden('p136');
959 htp.formHidden('p137');
960 htp.formHidden('p138');
961 htp.formHidden('p139');
962 htp.formHidden('p140');
963 htp.formHidden('p141');
964 htp.formHidden('p142');
965 htp.formHidden('p143');
966 htp.formHidden('p144');
967 htp.formHidden('p145');
968 htp.formHidden('p146');
969 htp.formHidden('p147');
970 htp.formHidden('p148');
971 htp.formHidden('p149');
972 htp.formHidden('p150');
973 htp.formHidden('p151');
974 htp.formHidden('p152');
975 htp.formHidden('p153');
976 htp.formHidden('p154');
977 htp.formHidden('p155');
978 htp.formHidden('p156');
979 htp.formHidden('p157');
980 htp.formHidden('p158');
981 htp.formHidden('p159');
982 htp.formHidden('p160');
983
984 htp.p('<TABLE BORDER=0 WIDTH=100%>');
985
986 SELECT Prompt into V_Prompt
987 From QA_CHARS
988 Where char_id = vchar_id;
989
990 htp.p('<TR>');
991 htp.p('<TD><SELECT NAME="a_1"><OPTION SELECTED VALUE="value">'||V_Prompt
992 ||'</SELECT></TD>');
993 htp.p('<TD><SELECT NAME="c_1"<OPTION VALUE="AIS">is<OPTION VALUE="BNOT">is not<OPTION VALUE="CCONTAIN">contains<OPTION SELECTED VALUE="DSTART">starts with<OPTION VALUE="EEND">ends with</SELECT>
994 </TD>');
995 htp.p('<TD><INPUT TYPE="text" NAME="i_1" SIZE="20" MAXLENGTH="35" VALUE="'
996 || replace(find_str,'"','&'||'quot;') || '"></TD>');
997 htp.p('<SCRIPT LANGUAGE="JavaScript">');
998 -- htp.p('queryText()');
999 htp.p('</SCRIPT>');
1000 htp.p('<TD ALIGN="LEFT" WIDTH="100%">');
1001
1002 fnd_message.set_name('QA','QA_SS_FIND');
1003 msg := fnd_message.get;
1004 msg := substr(msg,1, 20);
1005
1006 /*
1007 icx_util.DynamicButton(P_ButtonText => msg,
1008 P_ImageFileName => 'FNDBSBMT',
1009 P_OnMouseOverText => msg,
1010 P_HyperTextCall => 'javascript:Lov_Check('||rnumb||',1,3)',
1011 P_LanguageCode => l_language_code,
1012 P_JavaScriptFlag => FALSE);
1013 */
1014 qa_ss_core.draw_html_button('javascript:Lov_Check('||rnumb||',1,3)',msg);
1015 htp.p('</TD>');
1016 htp.p('</TR>');
1017 htp.p('<TR>');
1018 htp.p('<TD><INPUT TYPE="checkbox" NAME="case_sensitive" CHECKED>Match Case</TD>');
1019 htp.p('</TR>');
1020 htp.p('</TABLE>');
1021 htp.p('</CENTER>');
1022 htp.p('</FORM>');
1023
1024 htp.p('</BODY>');
1025
1026 htp.p('</HTML>');
1027
1028 fnd_message.clear;
1029
1030 end if; -- end icx session
1031
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 htp.p('Exception in Lov_Headers');
1035 htp.p(SQLERRM);
1036
1037 END LOV_Header;
1038 -------------------------------------------------------------------------------------
1039
1040 procedure LOV_Values (
1041 vchar_id IN qa_chars.char_id%TYPE DEFAULT NULL,
1042 rnumb IN NUMBER DEFAULT NULL,
1043 cnumb IN NUMBER DEFAULT NULL,
1044 start_row IN NUMBER DEFAULT 1,
1045 p_end_row IN NUMBER DEFAULT NULL,
1046 orgz_id IN NUMBER DEFAULT NULL,
1047 plan_id_i IN NUMBER DEFAULT NULL,
1048 x_txn_num IN NUMBER DEFAULT NULL,
1049 x_wip_entity_type IN NUMBER DEFAULT NULL,
1050 x_wip_rep_sch_id IN NUMBER DEFAULT NULL,
1051 x_po_header_id IN NUMBER DEFAULT NULL,
1052 x_po_release_id IN NUMBER DEFAULT NULL,
1053 x_po_line_id IN NUMBER DEFAULT NULL,
1054 x_line_location_id IN NUMBER DEFAULT NULL,
1055 x_po_distribution_id IN NUMBER DEFAULT NULL,
1056 x_item_id IN NUMBER DEFAULT NULL,
1057 x_wip_entity_id IN NUMBER DEFAULT NULL,
1058 x_wip_line_id IN NUMBER DEFAULT NULL,
1059 x_po_shipment_id IN NUMBER DEFAULT NULL,
1060 p1 IN VARCHAR2 DEFAULT NULL,
1061 p2 IN VARCHAR2 DEFAULT NULL,
1062 p3 IN VARCHAR2 DEFAULT NULL,
1063 p4 IN VARCHAR2 DEFAULT NULL,
1064 p5 IN VARCHAR2 DEFAULT NULL,
1065 p6 IN VARCHAR2 DEFAULT NULL,
1066 p7 IN VARCHAR2 DEFAULT NULL,
1067 p8 IN VARCHAR2 DEFAULT NULL,
1068 p9 IN VARCHAR2 DEFAULT NULL,
1069 p10 IN VARCHAR2 DEFAULT NULL,
1070 p11 IN VARCHAR2 DEFAULT NULL,
1071 p12 IN VARCHAR2 DEFAULT NULL,
1072 p13 IN VARCHAR2 DEFAULT NULL,
1073 p14 IN VARCHAR2 DEFAULT NULL,
1074 p15 IN VARCHAR2 DEFAULT NULL,
1075 p16 IN VARCHAR2 DEFAULT NULL,
1076 p17 IN VARCHAR2 DEFAULT NULL,
1077 p18 IN VARCHAR2 DEFAULT NULL,
1078 p19 IN VARCHAR2 DEFAULT NULL,
1079 p20 IN VARCHAR2 DEFAULT NULL,
1080 p21 IN VARCHAR2 DEFAULT NULL,
1081 p22 IN VARCHAR2 DEFAULT NULL,
1082 p23 IN VARCHAR2 DEFAULT NULL,
1083 p24 IN VARCHAR2 DEFAULT NULL,
1084 p25 IN VARCHAR2 DEFAULT NULL,
1085 p26 IN VARCHAR2 DEFAULT NULL,
1086 p27 IN VARCHAR2 DEFAULT NULL,
1087 p28 IN VARCHAR2 DEFAULT NULL,
1088 p29 IN VARCHAR2 DEFAULT NULL,
1089 p30 IN VARCHAR2 DEFAULT NULL,
1090 p31 IN VARCHAR2 DEFAULT NULL,
1091 p32 IN VARCHAR2 DEFAULT NULL,
1092 p33 IN VARCHAR2 DEFAULT NULL,
1093 p34 IN VARCHAR2 DEFAULT NULL,
1094 p35 IN VARCHAR2 DEFAULT NULL,
1095 p36 IN VARCHAR2 DEFAULT NULL,
1096 p37 IN VARCHAR2 DEFAULT NULL,
1097 p38 IN VARCHAR2 DEFAULT NULL,
1098 p39 IN VARCHAR2 DEFAULT NULL,
1099 p40 IN VARCHAR2 DEFAULT NULL,
1100 p41 IN VARCHAR2 DEFAULT NULL,
1101 p42 IN VARCHAR2 DEFAULT NULL,
1102 p43 IN VARCHAR2 DEFAULT NULL,
1103 p44 IN VARCHAR2 DEFAULT NULL,
1104 p45 IN VARCHAR2 DEFAULT NULL,
1105 p46 IN VARCHAR2 DEFAULT NULL,
1106 p47 IN VARCHAR2 DEFAULT NULL,
1107 p48 IN VARCHAR2 DEFAULT NULL,
1108 p49 IN VARCHAR2 DEFAULT NULL,
1109 p50 IN VARCHAR2 DEFAULT NULL,
1110 p51 IN VARCHAR2 DEFAULT NULL,
1111 p52 IN VARCHAR2 DEFAULT NULL,
1112 p53 IN VARCHAR2 DEFAULT NULL,
1113 p54 IN VARCHAR2 DEFAULT NULL,
1114 p55 IN VARCHAR2 DEFAULT NULL,
1115 p56 IN VARCHAR2 DEFAULT NULL,
1116 p57 IN VARCHAR2 DEFAULT NULL,
1117 p58 IN VARCHAR2 DEFAULT NULL,
1118 p59 IN VARCHAR2 DEFAULT NULL,
1119 p60 IN VARCHAR2 DEFAULT NULL,
1120 p61 IN VARCHAR2 DEFAULT NULL,
1121 p62 IN VARCHAR2 DEFAULT NULL,
1122 p63 IN VARCHAR2 DEFAULT NULL,
1123 p64 IN VARCHAR2 DEFAULT NULL,
1124 p65 IN VARCHAR2 DEFAULT NULL,
1125 p66 IN VARCHAR2 DEFAULT NULL,
1126 p67 IN VARCHAR2 DEFAULT NULL,
1127 p68 IN VARCHAR2 DEFAULT NULL,
1128 p69 IN VARCHAR2 DEFAULT NULL,
1129 p70 IN VARCHAR2 DEFAULT NULL,
1130 p71 IN VARCHAR2 DEFAULT NULL,
1131 p72 IN VARCHAR2 DEFAULT NULL,
1132 p73 IN VARCHAR2 DEFAULT NULL,
1133 p74 IN VARCHAR2 DEFAULT NULL,
1134 p75 IN VARCHAR2 DEFAULT NULL,
1135 p76 IN VARCHAR2 DEFAULT NULL,
1136 p77 IN VARCHAR2 DEFAULT NULL,
1137 p78 IN VARCHAR2 DEFAULT NULL,
1138 p79 IN VARCHAR2 DEFAULT NULL,
1139 p80 IN VARCHAR2 DEFAULT NULL,
1140 p81 IN VARCHAR2 DEFAULT NULL,
1141 p82 IN VARCHAR2 DEFAULT NULL,
1142 p83 IN VARCHAR2 DEFAULT NULL,
1143 p84 IN VARCHAR2 DEFAULT NULL,
1144 p85 IN VARCHAR2 DEFAULT NULL,
1145 p86 IN VARCHAR2 DEFAULT NULL,
1146 p87 IN VARCHAR2 DEFAULT NULL,
1147 p88 IN VARCHAR2 DEFAULT NULL,
1148 p89 IN VARCHAR2 DEFAULT NULL,
1149 p90 IN VARCHAR2 DEFAULT NULL,
1150 p91 IN VARCHAR2 DEFAULT NULL,
1151 p92 IN VARCHAR2 DEFAULT NULL,
1152 p93 IN VARCHAR2 DEFAULT NULL,
1153 p94 IN VARCHAR2 DEFAULT NULL,
1154 p95 IN VARCHAR2 DEFAULT NULL,
1155 p96 IN VARCHAR2 DEFAULT NULL,
1156 p97 IN VARCHAR2 DEFAULT NULL,
1157 p98 IN VARCHAR2 DEFAULT NULL,
1158 p99 IN VARCHAR2 DEFAULT NULL,
1159 p100 IN VARCHAR2 DEFAULT NULL,
1160 p101 IN VARCHAR2 DEFAULT NULL,
1161 p102 IN VARCHAR2 DEFAULT NULL,
1162 p103 IN VARCHAR2 DEFAULT NULL,
1163 p104 IN VARCHAR2 DEFAULT NULL,
1164 p105 IN VARCHAR2 DEFAULT NULL,
1165 p106 IN VARCHAR2 DEFAULT NULL,
1166 p107 IN VARCHAR2 DEFAULT NULL,
1167 p108 IN VARCHAR2 DEFAULT NULL,
1168 p109 IN VARCHAR2 DEFAULT NULL,
1169 p110 IN VARCHAR2 DEFAULT NULL,
1170 p111 IN VARCHAR2 DEFAULT NULL,
1171 p112 IN VARCHAR2 DEFAULT NULL,
1172 p113 IN VARCHAR2 DEFAULT NULL,
1173 p114 IN VARCHAR2 DEFAULT NULL,
1174 p115 IN VARCHAR2 DEFAULT NULL,
1175 p116 IN VARCHAR2 DEFAULT NULL,
1176 p117 IN VARCHAR2 DEFAULT NULL,
1177 p118 IN VARCHAR2 DEFAULT NULL,
1178 p119 IN VARCHAR2 DEFAULT NULL,
1179 p120 IN VARCHAR2 DEFAULT NULL,
1180 p121 IN VARCHAR2 DEFAULT NULL,
1181 p122 IN VARCHAR2 DEFAULT NULL,
1182 p123 IN VARCHAR2 DEFAULT NULL,
1183 p124 IN VARCHAR2 DEFAULT NULL,
1184 p125 IN VARCHAR2 DEFAULT NULL,
1185 p126 IN VARCHAR2 DEFAULT NULL,
1186 p127 IN VARCHAR2 DEFAULT NULL,
1187 p128 IN VARCHAR2 DEFAULT NULL,
1188 p129 IN VARCHAR2 DEFAULT NULL,
1189 p130 IN VARCHAR2 DEFAULT NULL,
1190 p131 IN VARCHAR2 DEFAULT NULL,
1191 p132 IN VARCHAR2 DEFAULT NULL,
1192 p133 IN VARCHAR2 DEFAULT NULL,
1193 p134 IN VARCHAR2 DEFAULT NULL,
1194 p135 IN VARCHAR2 DEFAULT NULL,
1195 p136 IN VARCHAR2 DEFAULT NULL,
1196 p137 IN VARCHAR2 DEFAULT NULL,
1197 p138 IN VARCHAR2 DEFAULT NULL,
1198 p139 IN VARCHAR2 DEFAULT NULL,
1199 p140 IN VARCHAR2 DEFAULT NULL,
1200 p141 IN VARCHAR2 DEFAULT NULL,
1201 p142 IN VARCHAR2 DEFAULT NULL,
1202 p143 IN VARCHAR2 DEFAULT NULL,
1203 p144 IN VARCHAR2 DEFAULT NULL,
1204 p145 IN VARCHAR2 DEFAULT NULL,
1205 p146 IN VARCHAR2 DEFAULT NULL,
1206 p147 IN VARCHAR2 DEFAULT NULL,
1207 p148 IN VARCHAR2 DEFAULT NULL,
1208 p149 IN VARCHAR2 DEFAULT NULL,
1209 p150 IN VARCHAR2 DEFAULT NULL,
1210 p151 IN VARCHAR2 DEFAULT NULL,
1211 p152 IN VARCHAR2 DEFAULT NULL,
1212 p153 IN VARCHAR2 DEFAULT NULL,
1213 p154 IN VARCHAR2 DEFAULT NULL,
1214 p155 IN VARCHAR2 DEFAULT NULL,
1215 p156 IN VARCHAR2 DEFAULT NULL,
1216 p157 IN VARCHAR2 DEFAULT NULL,
1217 p158 IN VARCHAR2 DEFAULT NULL,
1218 p159 IN VARCHAR2 DEFAULT NULL,
1219 p160 IN VARCHAR2 DEFAULT NULL,
1220 i_1 IN VARCHAR2 DEFAULT NULL, -- find_str
1221 a_1 IN VARCHAR2 DEFAULT NULL, -- search on
1222 c_1 IN VARCHAR2 DEFAULT NULL, -- condition
1223 case_sensitive IN VARCHAR2 DEFAULT NULL,
1224 Flag IN NUMBER DEFAULT NULL
1225 )
1226
1227 IS
1228 r_cnt NUMBER := 0;
1229 more_records BOOLEAN;
1230 vef NUMBER := 0;
1231 fld VARCHAR2(20);
1232 sbox VARCHAR2(20); -- New (for select box)
1233 f_str VARCHAR2(500); -- increased from 40
1234 code VARCHAR2(500);
1235 description VARCHAR2(500);
1236 cur INTEGER;
1237 sql_val_str qa_chars.sql_validation_string%TYPE := NULL;
1238 ignore INTEGER;
1239 ORDER_POS NUMBER;
1240 empty_valstring_ex EXCEPTION;
1241 -- A qa_ss_const.var150_table;
1242 -- instead of A table above, use global GV_Elmt_tab better perf
1243 plan_org_ex EXCEPTION;
1244
1245 /* PAGING VARS */
1246 end_row NUMBER;
1247 total_rows NUMBER;
1248 l_query_size NUMBER := 10;
1249 srow_st varchar2(1000);
1250 erow_st varchar2(1000);
1251
1252 row_color VARCHAR2(10) := 'BLUE';
1253 where_cond varchar2(20);
1254
1255 BEGIN
1256 if (icx_sec.validatesession) then
1257 if (Flag = 1) Then
1258 -- htp.p('Find Button has been clicked');
1259 -- htp.p('variable sensit = ' || case_sensitive);
1260 -- htp.p('search condition = ' || c_1);
1261
1262 if (orgz_id is Null or plan_id_i is Null) Then
1263 Raise plan_org_ex;
1264 end if;
1265
1266 if (p_end_row is Null) then
1267 end_row := l_query_size;
1268 else
1269 end_row := p_end_row;
1270 end if;
1271
1272 /*
1273 htp.p('start = ' || start_row);
1274 htp.p('end = ' || end_row);
1275 htp.p('l_query_size = ' || l_query_size);
1276
1277 htp.nl;
1278 htp.p('orgz_id: '|| to_char(NVL(orgz_id, -9999))); htp.nl;
1279 htp.p('plan_id_i: ' || to_char(NVL(plan_id_i, -9999))); htp.nl;
1280 htp.p('x_txn_num: ' || to_char(NVL(x_txn_num, -9999))); htp.nl;
1281 htp.p('x_wip_entity_type: ' || to_char(NVL(x_wip_entity_type,-9999))); htp.nl;
1282 htp.p('x_wip_rep_sch_id: ' || to_char(NVL(x_wip_rep_sch_id, -9999))); htp.nl;
1283 htp.p('x_po_header_id: ' || to_char(NVL(x_po_header_id, -9999))); htp.nl;
1284 htp.p('x_po_release_id: ' || to_char(NVL(x_po_release_id, -9999))); htp.nl;
1285 htp.p('x_po_line_id: ' || to_char(NVL(x_po_line_id, -9999))); htp.nl;
1286 htp.p('x_line_location_id: ' || to_char(NVL(x_line_location_id, -9999))); htp.nl;
1287 htp.p('x_po_distribution_id: ' || to_char(NVL(x_po_distribution_id, -9999))); htp.nl;
1288 htp.p('x_item_id: ' || to_char(NVL(x_item_id, -9999))); htp.nl;
1289 htp.p('x_wip_entity_id: ' || to_char(NVL(x_wip_entity_id, -9999))); htp.nl;
1290 htp.p('x_wip_line_id: ' || to_char(NVL(x_wip_line_id, -9999))); htp.nl;
1291 htp.p('x_po_shipment_id: ' || to_char(NVL(x_po_shipment_id, -9999))); htp.nl;
1292 */
1293
1294 if (c_1 = 'BNOT') then
1295 f_str := i_1;
1296 where_cond := '<>';
1297 elsif (c_1 = 'DSTART') then
1298 f_str := i_1||'%';
1299 where_cond := 'LIKE';
1300 elsif (c_1 = 'EEND') then
1301 f_str := '%'||i_1;
1302 where_cond := 'LIKE';
1303 else
1304 f_str := '%'||i_1||'%';
1305 where_cond := 'LIKE';
1306 end if;
1307
1308 -- adding the dequote function here, Oct22 1999
1309 f_str := QA_CORE_PKG.dequote(f_str);
1310
1311 select sql_validation_string
1312 into sql_val_str
1313 from qa_chars
1314 where char_id = vchar_id; -- THIS has to be changed to vchar_id later. DONE now
1315
1316 select qpc.values_exist_flag into vef
1317 from qa_plan_chars qpc
1318 where char_id = vchar_id
1319 and plan_id = plan_id_i;
1320 --
1321 -- Bug 5003509. R12 Performance fix. Comment out the code.
1322 -- srhariha. Wed Feb 1 03:29:16 PST 2006
1323 --
1324 /*
1325 IF (vef = 1) Then -- User defined Values do exist
1326 sql_val_str := 'SELECT short_code, description from qa_plan_char_value_lookups '
1327 || ' where char_id = '||vchar_id
1328 || ' and plan_id = ' ||plan_id_i;
1329 End IF; -- end if user values exist
1330 */
1331
1332 -- Assign values to the table A
1333 GV_Elmt_tab(1) := p1;
1334 GV_Elmt_tab(2) := p2;
1335 GV_Elmt_tab(3) := p3;
1336 GV_Elmt_tab(4) := p4;
1337 GV_Elmt_tab(5) := p5;
1338 GV_Elmt_tab(6) := p6;
1339 GV_Elmt_tab(7) := p7;
1340 GV_Elmt_tab(8) := p8;
1341 GV_Elmt_tab(9) := p9;
1342 GV_Elmt_tab(10) := p10;
1343 GV_Elmt_tab(11) := p11;
1344 GV_Elmt_tab(12) := p12;
1345 GV_Elmt_tab(13) := p13;
1346 GV_Elmt_tab(14) := p14;
1347 GV_Elmt_tab(15) := p15;
1348 GV_Elmt_tab(16) := p16;
1349 GV_Elmt_tab(17) := p17;
1350 GV_Elmt_tab(18) := p18;
1351 GV_Elmt_tab(19) := p19;
1352 GV_Elmt_tab(20) := p20;
1353 GV_Elmt_tab(21) := p21;
1354 GV_Elmt_tab(22) := p22;
1355 GV_Elmt_tab(23) := p23;
1356 GV_Elmt_tab(24) := p24;
1357 GV_Elmt_tab(25) := p25;
1358 GV_Elmt_tab(26) := p26;
1359 GV_Elmt_tab(27) := p27;
1360 GV_Elmt_tab(28) := p28;
1361 GV_Elmt_tab(29) := p29;
1362 GV_Elmt_tab(30) := p30;
1363 GV_Elmt_tab(31) := p31;
1364 GV_Elmt_tab(32) := p32;
1365 GV_Elmt_tab(33) := p33;
1366 GV_Elmt_tab(34) := p34;
1367 GV_Elmt_tab(35) := p35;
1368 GV_Elmt_tab(36) := p36;
1369 GV_Elmt_tab(37) := p37;
1370 GV_Elmt_tab(38) := p38;
1371 GV_Elmt_tab(39) := p39;
1372 GV_Elmt_tab(40) := p40;
1373 GV_Elmt_tab(41) := p41;
1374 GV_Elmt_tab(42) := p42;
1375 GV_Elmt_tab(43) := p43;
1376 GV_Elmt_tab(44) := p44;
1377 GV_Elmt_tab(45) := p45;
1378 GV_Elmt_tab(46) := p46;
1379 GV_Elmt_tab(47) := p47;
1380 GV_Elmt_tab(48) := p48;
1381 GV_Elmt_tab(49) := p49;
1382 GV_Elmt_tab(50) := p50;
1383 GV_Elmt_tab(51) := p51;
1384 GV_Elmt_tab(52) := p52;
1385 GV_Elmt_tab(53) := p53;
1386 GV_Elmt_tab(54) := p54;
1387 GV_Elmt_tab(55) := p55;
1388 GV_Elmt_tab(56) := p56;
1389 GV_Elmt_tab(57) := p57;
1390 GV_Elmt_tab(58) := p58;
1391 GV_Elmt_tab(59) := p59;
1392 GV_Elmt_tab(60) := p60;
1393 GV_Elmt_tab(61) := p61;
1394 GV_Elmt_tab(62) := p62;
1395 GV_Elmt_tab(63) := p63;
1396 GV_Elmt_tab(64) := p64;
1397 GV_Elmt_tab(65) := p65;
1398 GV_Elmt_tab(66) := p66;
1399 GV_Elmt_tab(67) := p67;
1400 GV_Elmt_tab(68) := p68;
1401 GV_Elmt_tab(69) := p69;
1402 GV_Elmt_tab(70) := p70;
1403 GV_Elmt_tab(71) := p71;
1404 GV_Elmt_tab(72) := p72;
1405 GV_Elmt_tab(73) := p73;
1406 GV_Elmt_tab(74) := p74;
1407 GV_Elmt_tab(75) := p75;
1408 GV_Elmt_tab(76) := p76;
1409 GV_Elmt_tab(77) := p77;
1410 GV_Elmt_tab(78) := p78;
1411 GV_Elmt_tab(79) := p79;
1412 GV_Elmt_tab(80) := p80;
1413 GV_Elmt_tab(81) := p81;
1414 GV_Elmt_tab(82) := p82;
1415 GV_Elmt_tab(83) := p83;
1416 GV_Elmt_tab(84) := p84;
1417 GV_Elmt_tab(85) := p85;
1418 GV_Elmt_tab(86) := p86;
1419 GV_Elmt_tab(87) := p87;
1420 GV_Elmt_tab(88) := p88;
1421 GV_Elmt_tab(89) := p89;
1422 GV_Elmt_tab(90) := p90;
1423 GV_Elmt_tab(91) := p91;
1424 GV_Elmt_tab(92) := p92;
1425 GV_Elmt_tab(93) := p93;
1426 GV_Elmt_tab(94) := p94;
1427 GV_Elmt_tab(95) := p95;
1428 GV_Elmt_tab(96) := p96;
1429 GV_Elmt_tab(97) := p97;
1430 GV_Elmt_tab(98) := p98;
1431 GV_Elmt_tab(99) := p99;
1432 GV_Elmt_tab(100) := p100;
1433 GV_Elmt_tab(101) := p101;
1434 GV_Elmt_tab(102) := p102;
1435 GV_Elmt_tab(103) := p103;
1436 GV_Elmt_tab(104) := p104;
1437 GV_Elmt_tab(105) := p105;
1438 GV_Elmt_tab(106) := p106;
1439 GV_Elmt_tab(107) := p107;
1440 GV_Elmt_tab(108) := p108;
1441 GV_Elmt_tab(109) := p109;
1442 GV_Elmt_tab(110) := p110;
1443 GV_Elmt_tab(111) := p111;
1444 GV_Elmt_tab(112) := p112;
1445 GV_Elmt_tab(113) := p113;
1446 GV_Elmt_tab(114) := p114;
1447 GV_Elmt_tab(115) := p115;
1448 GV_Elmt_tab(116) := p116;
1449 GV_Elmt_tab(117) := p117;
1450 GV_Elmt_tab(118) := p118;
1451 GV_Elmt_tab(119) := p119;
1452 GV_Elmt_tab(120) := p120;
1453 GV_Elmt_tab(121) := p121;
1454 GV_Elmt_tab(122) := p122;
1455 GV_Elmt_tab(123) := p123;
1456 GV_Elmt_tab(124) := p124;
1457 GV_Elmt_tab(125) := p125;
1458 GV_Elmt_tab(126) := p126;
1459 GV_Elmt_tab(127) := p127;
1460 GV_Elmt_tab(128) := p128;
1461 GV_Elmt_tab(129) := p129;
1462 GV_Elmt_tab(130) := p130;
1463 GV_Elmt_tab(131) := p131;
1464 GV_Elmt_tab(132) := p132;
1465 GV_Elmt_tab(133) := p133;
1466 GV_Elmt_tab(134) := p134;
1467 GV_Elmt_tab(135) := p135;
1468 GV_Elmt_tab(136) := p136;
1469 GV_Elmt_tab(137) := p137;
1470 GV_Elmt_tab(138) := p138;
1471 GV_Elmt_tab(139) := p139;
1472 GV_Elmt_tab(140) := p140;
1473 GV_Elmt_tab(141) := p141;
1474 GV_Elmt_tab(142) := p142;
1475 GV_Elmt_tab(143) := p143;
1476 GV_Elmt_tab(144) := p144;
1477 GV_Elmt_tab(145) := p145;
1478 GV_Elmt_tab(146) := p146;
1479 GV_Elmt_tab(147) := p147;
1480 GV_Elmt_tab(148) := p148;
1481 GV_Elmt_tab(149) := p149;
1482 GV_Elmt_tab(150) := p150;
1483 GV_Elmt_tab(151) := p151;
1484 GV_Elmt_tab(152) := p152;
1485 GV_Elmt_tab(153) := p153;
1486 GV_Elmt_tab(154) := p154;
1487 GV_Elmt_tab(155) := p155;
1488 GV_Elmt_tab(156) := p156;
1489 GV_Elmt_tab(157) := p157;
1490 GV_Elmt_tab(158) := p158;
1491 GV_Elmt_tab(159) := p159;
1492 GV_Elmt_tab(160) := p160;
1493
1494 -- Assign Package Global Variables here
1495 GV_Wip_Entity_Type := x_wip_entity_type;
1496 GV_Wip_Rep_Sch_Id := x_wip_rep_sch_id;
1497 GV_Po_Header_Id := x_po_header_id;
1498 GV_Po_Release_Id := x_po_release_id;
1499 GV_Po_Line_Id := x_po_line_id;
1500 GV_Line_Location_Id := x_line_location_id;
1501 GV_Po_Distribution_Id := x_po_distribution_id;
1502 GV_Item_Id := x_item_id;
1503 GV_wip_entity_Id := x_wip_entity_id;
1504 GV_Wip_Line_Id := x_wip_line_id;
1505 GV_Po_Shipment_Id := x_po_shipment_id;
1506 GV_Txn_Num := x_txn_num;
1507
1508 /* -- only for debugging
1509 FOR b in 1..160
1510 Loop
1511 htp.p('A('||b||') = '|| A(b));
1512 End Loop;
1513 */
1514
1515
1516 If (vchar_id = qa_ss_const.Item) Then
1517 sql_val_str := Q_Item(plan_id_i, vchar_id, orgz_id);
1518 Elsif (vchar_id = qa_ss_const.Comp_Revision) Then
1519 sql_val_str := Q_Comp_Revision(plan_id_i, vchar_id, orgz_id);
1520 Elsif (vchar_id = qa_ss_const.Comp_Subinventory) Then
1521 sql_val_str := Q_Comp_Subinventory(plan_id_i, vchar_id, orgz_id);
1522 Elsif (vchar_id = qa_ss_const.Comp_UOM) Then
1523 sql_val_str := Q_Comp_Uom(plan_id_i, vchar_id, orgz_id);
1524 Elsif (vchar_id = qa_ss_const.Customer_Name) Then
1525 sql_val_str := Q_Customers(plan_id_i, vchar_id, orgz_id);
1526 Elsif (vchar_id = qa_ss_const.Department) Then
1527 sql_val_str := Q_Department(plan_id_i, vchar_id, orgz_id);
1528 Elsif (vchar_id = qa_ss_const.From_Op_Seq_Num) Then
1529 sql_val_str := Q_From_Op_Seq_Num(plan_id_i, vchar_id, orgz_id);
1530 Elsif (vchar_id = qa_ss_const.Production_Line) Then
1531 sql_val_str := Q_Line(plan_id_i, vchar_id, orgz_id);
1532 Elsif (vchar_id = qa_ss_const.Po_Number) Then
1533 sql_val_str := Q_Po_Headers(plan_id_i, vchar_id, orgz_id);
1534 Elsif (vchar_id = qa_ss_const.Po_Line_Num) Then
1535 sql_val_str := Q_Po_Lines(plan_id_i, vchar_id, orgz_id);
1536 Elsif (vchar_id = qa_ss_const.Po_Release_Num) Then
1537 sql_val_str := Q_Po_Release_Nums(plan_id_i, vchar_id, orgz_id);
1538 Elsif (vchar_id = qa_ss_const.Po_Shipment_Num) Then
1539 sql_val_str := Q_Po_Shipments(plan_id_i, vchar_id, orgz_id);
1540 Elsif (vchar_id = qa_ss_const.Project_Number) Then
1541 sql_val_str := Q_Project(plan_id_i, vchar_id, orgz_id);
1542 Elsif (vchar_id = qa_ss_const.Receipt_Num) Then
1543 sql_val_str := Q_Receipt_Nums(plan_id_i, vchar_id, orgz_id);
1544 Elsif (vchar_id = qa_ss_const.Resource_Code) Then
1545 sql_val_str := Q_Resource(plan_id_i, vchar_id, orgz_id);
1546 Elsif (vchar_id = qa_ss_const.Revision) Then
1547 sql_val_str := Q_Revision(plan_id_i, vchar_id, orgz_id);
1548 Elsif (vchar_id = qa_ss_const.RMA_Number) Then
1549 sql_val_str := Q_Rma_Number(plan_id_i, vchar_id, orgz_id);
1550 Elsif (vchar_id = qa_ss_const.Sales_Order) Then
1551 sql_val_str := Q_Sales_Orders(plan_id_i, vchar_id, orgz_id);
1552 Elsif (vchar_id = qa_ss_const.Subinventory) Then
1553 sql_val_str := Q_Subinventory(plan_id_i, vchar_id, orgz_id);
1554 Elsif (vchar_id = qa_ss_const.To_op_seq_num) Then
1555 sql_val_str := Q_To_Op_Seq_Num(plan_id_i, vchar_id, orgz_id);
1556 Elsif (vchar_id = qa_ss_const.UOM) Then
1557 sql_val_str := Q_UOM(plan_id_i, vchar_id, orgz_id);
1558 Elsif (vchar_id = qa_ss_const.Vendor_Name) Then
1559 sql_val_str := Q_Vendors(plan_id_i, vchar_id, orgz_id);
1560 Elsif (vchar_id = qa_ss_const.Job_Name) Then
1561 sql_val_str := Q_Job(plan_id_i, vchar_id, orgz_id);
1562 Elsif (vchar_id = qa_ss_const.Task_Number) Then
1563 sql_val_str := Q_Task(plan_id_i, vchar_id, orgz_id);
1564
1565 End If;
1566
1567
1568
1569
1570 IF (sql_val_str IS NULL or sql_val_str = 'DEP_ERROR' )
1571 Then
1572 RAISE empty_valstring_ex;
1573 End if;
1574 -- Now lets do some processing of the validation string obtained
1575 sql_val_str := UPPER(sql_val_str);
1576 ORDER_POS := INSTR(sql_val_str, 'ORDER BY');
1577 IF (ORDER_POS <> 0) THEN
1578 sql_val_str := SUBSTR(sql_val_str, 1, ORDER_POS-1);
1579 END IF;
1580
1581 sql_val_str := REPLACE(sql_val_str, ':PARAMETER.ORG_ID', to_char(orgz_id));
1582 -- adding this rtrim below oct27,1999
1583 -- bryan suggested this as per bug 956708 he fixed
1584 -- after / I hit enter key on purpose. dont alter this
1585 sql_val_str := rtrim(sql_val_str, ' ;/
1586 ');
1587 -- htp.p('after rtrim as per bryan suggestion');
1588 sql_val_str := 'SELECT CODE, DESCRIPTION FROM (' ||
1589 'SELECT ''1'' AS CODE, ''1'' AS DESCRIPTION ' ||
1590 'FROM DUAL WHERE 1=2 ' ||
1591 'UNION ALL (' ||
1592 sql_val_str ||
1593 ') )
1594 WHERE CODE '||where_cond|| '''' || f_str || '''
1595 ORDER BY CODE';
1596 -- htp.p('Before Cursor open: '||sql_val_str);
1597
1598 cur := dbms_sql.open_cursor;
1599 -- htp.p('before parse');
1600 dbms_sql.parse(cur, sql_val_str, DBMS_SQL.v7);
1601 -- htp.p('after parse');
1602 dbms_sql.define_column(cur, 1, code, 500);
1603 dbms_sql.define_column(cur, 2, description, 500);
1604 ignore := dbms_sql.execute(cur);
1605
1606 fld := 'p_col'||cnumb|| '[' || rnumb || ']';
1607 sbox := 'selectbox[' || rnumb || ']';
1608 htp.htmlOpen;
1609 htp.headOpen;
1610 htp.p('<SCRIPT LANGUAGE="JavaScript">');
1611
1612 -- New code Make the selectbox to be Yes
1613 -- Otherwise, if you choose Lov, it does not mark the record as dirty, which it should
1614 -- Initial Bug Fix July 29, 1999
1615 htp.p('function clicked(return_val)
1616 {
1617 parent.opener.document.RSMDF.'||fld||'.value = return_val;
1618 parent.opener.document.RSMDF.'||sbox||'.checked = true;
1619 parent.window.close();
1620 }');
1621
1622 htp.p('</SCRIPT>');
1623 htp.headClose;
1624
1625 htp.bodyOpen(cattributes=>'bgcolor="#cccccc"');
1626 -- htp.p('Corresponding field is ' || fld);
1627
1628 htp.p('Records ' || to_char(start_row) || ' to ' || to_char(end_row));
1629
1630 htp.p('<FORM ACTION="" METHOD="POST" NAME="LOVValues">');
1631
1632 htp.tableOpen('BORDER');
1633 htp.tableRowOpen(cattributes=>'BGCOLOR="#336699"');
1634 htp.p('<TD ALIGN="LEFT"><STRONG><FONT color="#ffffff">Code</Font></STRONG></TD>');
1635 htp.p('<TD ALIGN="LEFT"><STRONG><FONT color="#ffffff">Description</Font></STRONG></TD>');
1636 htp.tableRowClose;
1637
1638 r_cnt := 0;
1639 more_records := TRUE;
1640 LOOP
1641 IF DBMS_SQL.FETCH_ROWS(cur) > 0 THEN
1642 r_cnt := r_cnt + 1;
1643 if (r_cnt > end_row) then
1644 exit;
1645 end if;
1646
1647 if (r_cnt >= start_row) THEN
1648 dbms_sql.column_value(cur, 1, code);
1649 dbms_sql.column_value(cur, 2, description);
1650 if (row_color = 'BLUE') then
1651 htp.tableRowOpen(cattributes=>'BGCOLOR="#99ccff"');
1652 row_color := 'WHITE';
1653 else
1654 htp.tableRowOpen(cattributes=>'BGCOLOR="#ffffff"');
1655 row_color := 'BLUE';
1656 end if; -- end if for row color
1657 /*
1658 htp.tableData('<A HREF="javascript:clicked('''||
1659 replace(code,'"','&'||'quot;')||''')">'||code||'</A>');
1660 */
1661 -- AUG 19 Using icx_util to try and fix special chars issue
1662 htp.tableData('<A HREF="javascript:clicked('''||
1663 icx_util.replace_jsdw_quotes(code)||''')">'||code||'</A>');
1664
1665 if (description IS NULL)
1666 then
1667 description := ' ';
1668 end if;
1669 htp.tableData(description);
1670 htp.tableRowClose;
1671 end if; -- end r_cnt check
1672 ELSE
1673 -- no more rows
1674 more_records := FALSE;
1675 EXIT;
1676 End If;
1677 END LOOP; -- end of dynamic cursor loop
1678 htp.tableClose;
1679
1680
1681 srow_st := 'javascript:parent.LOVHeader.Lov_Check('
1682 || to_char(rnumb)
1683 || ', ' || to_char(start_row-l_query_size)
1684 || ', ' || to_char(start_row-1) || ')';
1685
1686 erow_st := 'javascript:parent.LOVHeader.Lov_Check('
1687 || to_char(rnumb)
1688 || ', ' || to_char(end_row+1)
1689 || ', ' || to_char(end_row+l_query_size) || ')';
1690
1691 if (start_row > 1) then
1692 htp.anchor(srow_st , 'Previous ');
1693 end if;
1694
1695 -- ||to_char(start_row-l_query_size)||' to '
1696 -- || to_char(start_row-1));
1697
1698 htp.p('------------'); -- Dont delete this is a spacer
1699
1700 if (more_records = TRUE) then
1701 htp.anchor(erow_st, 'Next ');
1702 end if;
1703
1704 -- || to_char(end_row+1) || ' to '
1705 -- || to_char(end_row+l_query_size));
1706
1707
1708 htp.bodyClose;
1709
1710 htp.htmlClose;
1711 Else
1712 htp.p('Please Enter Search above and click Find button');
1713
1714 End If; -- End if flag=1
1715
1716 -- htp.p('Before Find has been clicked');
1717
1718
1719
1720 end if; -- end icx session
1721
1722 EXCEPTION
1723 WHEN empty_valstring_ex THEN
1724 htp.p(fnd_message.get_string('QA','QA_SS_NO_LOV_VALUES'));
1725 WHEN plan_org_ex THEN
1726 htp.p('Plan id or Org Id is Null');
1727 WHEN OTHERS THEN
1728 htp.p('Exception in Lov_Values');
1729 htp.p(SQLERRM);
1730 END LOV_Values;
1731 -----------------------------------------------------------------------------------------
1732
1733
1734 function return_col_num ( x_char_id IN NUMBER, x_plan_id IN NUMBER )
1735 RETURN NUMBER
1736 IS
1737 CURSOR qpc_cur IS
1738 select qpc.char_id
1739 from qa_plan_chars qpc
1740 where qpc.plan_id = x_plan_id
1741 and qpc.enabled_flag = 1
1742 ORDER BY qpc.prompt_sequence;
1743
1744 pos NUMBER;
1745 BEGIN
1746 pos := 0;
1747 FOR qpc_rec IN qpc_cur
1748 LOOP
1749 pos := pos + 1;
1750 If (qpc_rec.char_id = x_char_id) Then
1751 RETURN pos;
1752 End If;
1753 END LOOP; -- end cursor for loop
1754
1755 RETURN -1; -- charid Not found, some mistake
1756
1757 -- return NULL;
1758 EXCEPTION
1759 WHEN OTHERS THEN
1760 htp.p('Exception in Lov package - function return_col_num');
1761 htp.p(SQLERRM);
1762 END;
1763 -------------------------------------------------------------------------------------------
1764
1765
1766 function value_to_id ( charid IN NUMBER, val IN VARCHAR2, orgz_id IN NUMBER DEFAULT NULL)
1767 RETURN NUMBER
1768 -- eg, pass in Project Number as val, will return Project Id
1769 IS
1770 str varchar2(10000);
1771 x_pk_id varchar2(30);
1772 x_fk_table_name varchar2(30);
1773 x_fk_meaning varchar2(30);
1774 cur INTEGER;
1775 ignore INTEGER;
1776 id NUMBER := -1 ;
1777 BEGIN
1778 if (charid is Null or val is Null )
1779 then
1780 return -1;
1781 end if;
1782
1783 if (charid in (qa_ss_const.Item,
1784 qa_ss_const.job_name,
1785 qa_ss_const.production_line,
1786 qa_ss_const.po_number,
1787 qa_ss_const.project_number,
1788 qa_ss_const.item,
1789 qa_ss_const.comp_item) )
1790 THEN
1791 select qc.fk_table_name, qc.pk_id, qc.fk_meaning
1792 INTO x_fk_table_name, x_pk_id, x_fk_meaning
1793 FROM qa_chars qc
1794 WHERE qc.char_id = charid;
1795
1796 --this code path is obsolete -only used for old
1797 --plsql web cartridge based application
1798 --never get executed in 11.5.9
1799 --commenting out just to prevent false positive
1800 --in SQL Bind project
1801 -- isivakum May 5, 2003
1802
1803 str := null;
1804
1805 -- str := 'SELECT ' || x_pk_id || ' FROM ' || x_fk_table_name
1806 -- || ' Where ' || x_fk_meaning || ' = ''' || val
1807 -- || ''' and Rownum = 1 ';
1808
1809
1810
1811 cur := dbms_sql.open_cursor;
1812 dbms_sql.parse(cur, str, DBMS_SQL.v7);
1813 dbms_sql.define_column( cur, 1, id);
1814 ignore := dbms_sql.execute(cur);
1815
1816 if dbms_sql.fetch_rows(cur) > 0 Then
1817 dbms_sql.column_value(cur, 1, id);
1818 end if;
1819 return id;
1820 ELSE
1821 Return -1;
1822 End If;
1823 -- return NULL;
1824 EXCEPTION
1825 WHEN OTHERS THEN
1826 htp.p('Exception in Lov package - func value_to_id');
1827 htp.p(SQLERRM);
1828 END;
1829 -------------------------------------------------------------------------------------------
1830
1831
1832 end qa_ss_lov;
1833