1 PACKAGE qa_plan_element_api AS
2 /* $Header: qltelemb.pls 120.15.12010000.1 2008/07/25 09:21:47 appldev ship $ */
3
4
5 TYPE LovRecord IS RECORD (
6 code VARCHAR2(150),
7 description VARCHAR2(2000));
8
9
10 TYPE LovRefCursor IS REF CURSOR;
11
12 -- Bug 3769260. shkalyan 30 July 2004.
13 -- Added this procedure to fetch all the elements of a specifications
14 -- The reason for introducing this procedure is to reduce the number of
15 -- hits on the QA_SPEC_CHARS.
16 -- Callers will use this procedure to pre-fetch all the Spec elements
17 -- to the cache if all the elements of a Spec would be accessed.
18
19 PROCEDURE fetch_qa_spec_chars (spec_id IN NUMBER);
20
21 -- Bug 3769260. shkalyan 30 July 2004.
22 -- Added this procedure to fetch all the elements of a plan
23 -- The reason for introducing this procedure is to reduce the number of
24 -- hits on the QA_PLAN_CHARS.
25 -- Callers will use this procedure to pre-fetch all the Plan elements
26 -- to the cache if all the elements of a plan would be accessed.
27
28 PROCEDURE fetch_qa_plan_chars (plan_id IN NUMBER);
29
30 --
31 -- Bug 5182097. Need a procedure to force repopulate qpc cache.
32 -- Some subtle changes in Setup Collection Plans are not immediately
33 -- reflected in QWB. So this is provided to reread qpc on demand.
34 -- bso Mon May 1 17:02:33 PDT 2006
35 --
36 PROCEDURE refetch_qa_plan_chars(p_plan_id IN NUMBER);
37
38
39 FUNCTION keyflex (element_id IN NUMBER)
40 RETURN BOOLEAN;
41
42
43 FUNCTION normalized (element_id IN NUMBER)
44 RETURN BOOLEAN;
45
46 FUNCTION derived (element_id IN NUMBER)
47 RETURN VARCHAR2;
48
49 FUNCTION hardcoded (element_id IN NUMBER)
50 RETURN BOOLEAN;
51
52
53 FUNCTION primitive (element_id IN NUMBER)
54 RETURN BOOLEAN;
55
56
57 FUNCTION values_exist (plan_id IN NUMBER, element_id IN NUMBER)
58 RETURN BOOLEAN;
59
60 --anagarwa
61 --Bug 2751198
62 --Added the function to spec so that it may be used by other packages
63 FUNCTION exists_qa_plan_chars(plan_id IN NUMBER, element_id IN NUMBER)
64 RETURN BOOLEAN;
65
66 FUNCTION sql_validation_exists (element_id IN NUMBER)
67 RETURN BOOLEAN;
68
69
70 FUNCTION element_in_plan (plan_id IN NUMBER, element_id IN NUMBER)
71 RETURN BOOLEAN;
72
73 FUNCTION element_in_spec (spec_id IN NUMBER, element_id IN NUMBER)
74 RETURN BOOLEAN;
75
76
77 FUNCTION get_actual_datatype (element_id IN NUMBER)
78 RETURN NUMBER;
79
80
81 FUNCTION get_element_datatype (element_id IN NUMBER)
82 RETURN NUMBER;
83
84
85 PROCEDURE get_spec_limits (spec_id IN NUMBER, element_id IN NUMBER,
86 lower_limit OUT NOCOPY VARCHAR2, upper_limit OUT NOCOPY VARCHAR2);
87
88
89 -- BUG 3303285
90 -- ksoh Mon Dec 29 13:33:02 PST 2003
91 -- overloaded get_spec_limits with a version that takes in plan_id
92 -- it is used for retrieving qa_plan_chars.uom_code for uom conversion
93 PROCEDURE get_spec_limits (p_plan_id IN NUMBER, p_spec_id IN NUMBER,
94 p_element_id IN NUMBER,
95 lower_limit OUT NOCOPY VARCHAR2, upper_limit OUT NOCOPY VARCHAR2);
96
97
98 -- BUG 3303285
99 -- ksoh Mon Jan 5 12:55:13 PST 2004
100 -- it is used for retrieving low/high value for evaluation of action triggers
101 -- it performs UOM conversion.
102 PROCEDURE get_low_high_values (p_plan_id IN NUMBER, p_spec_id IN NUMBER,
103 p_element_id IN NUMBER,
104 p_low_value_lookup IN NUMBER,
105 p_high_value_lookup IN NUMBER,
106 x_low_value OUT NOCOPY VARCHAR2, x_high_value OUT NOCOPY VARCHAR2);
107
108
109 FUNCTION get_department_id (org_id IN NUMBER, value IN VARCHAR2)
110 RETURN NUMBER;
111
112
113 FUNCTION get_job_id (org_id IN NUMBER, value IN VARCHAR2)
114 RETURN NUMBER;
115
116
117 FUNCTION get_production_line_id (org_id IN NUMBER, value IN VARCHAR2)
118 RETURN NUMBER;
119
120
121 FUNCTION get_resource_code_id (org_id IN NUMBER, value IN VARCHAR2)
122 RETURN NUMBER;
123
124
125 FUNCTION get_supplier_id (value IN VARCHAR2)
126 RETURN NUMBER;
127
128
129 FUNCTION get_po_number_id (value IN VARCHAR2)
130 RETURN NUMBER;
131
132
133 FUNCTION get_customer_id (value IN VARCHAR2)
134 RETURN NUMBER;
135
136
137 FUNCTION get_so_number_id (value IN VARCHAR2)
138 RETURN NUMBER;
139
140
141 FUNCTION get_so_line_number_id (value IN VARCHAR2)
142 RETURN NUMBER;
143
144
145 FUNCTION get_po_release_number_id (value IN VARCHAR2, x_po_header_id IN NUMBER)
146 RETURN NUMBER;
147
148
149 FUNCTION get_project_number_id (value IN VARCHAR2)
150 RETURN NUMBER;
151
152
153 --
154 -- Bug 2672396. Added p_project_id because task is a dependent element.
155 -- Fix is required in qltvalb.plb also.
156 -- bso Mon Nov 25 17:29:56 PST 2002
157 --
158 FUNCTION get_task_number_id (value IN VARCHAR2, p_project_id IN NUMBER)
159 RETURN NUMBER;
160
161
162 FUNCTION get_rma_number_id (value IN VARCHAR2)
163 RETURN NUMBER;
164
165 FUNCTION get_LPN_id (value IN VARCHAR2)
166 RETURN NUMBER;
167
168 -- added the following to include new hardcoded element Transfer license plate number
169 -- saugupta Aug 2003
170
171 FUNCTION get_XFR_LPN_id (value IN VARCHAR2)
172 RETURN NUMBER;
173
174 FUNCTION get_contract_id (value IN VARCHAR2)
175 RETURN NUMBER;
176
177 FUNCTION get_contract_line_id (value IN VARCHAR2)
178 RETURN NUMBER;
179
180 FUNCTION get_deliverable_id (value IN VARCHAR2)
181 RETURN NUMBER;
182
183 FUNCTION get_work_order_id (org_id IN NUMBER, value IN VARCHAR2)
184 RETURN NUMBER;
185
186 FUNCTION get_party_id (value IN VARCHAR2)
187 RETURN NUMBER;
188
189 FUNCTION retrieve_id (sql_statement IN VARCHAR2)
190 RETURN NUMBER;
191
192 --
193 -- Included the following six get_id functions for ASO project
194 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
195 --
196
197 FUNCTION get_item_instance_id (value IN VARCHAR2)
198 RETURN NUMBER;
199
200 FUNCTION get_counter_name_id (value IN VARCHAR2)
201 RETURN NUMBER;
202
203 FUNCTION get_maintenance_req_id (value IN VARCHAR2)
204 RETURN NUMBER;
205
206 FUNCTION get_service_request_id (value IN VARCHAR2)
207 RETURN NUMBER;
208
209 FUNCTION get_rework_job_id (org_id IN NUMBER, value IN VARCHAR2)
210 RETURN NUMBER;
211
212 -- R12 OPM Deviations. Bug 4345503 Start
213 FUNCTION get_process_batch_id (value IN VARCHAR2,p_org_id IN NUMBER)
214 RETURN NUMBER;
215
216 FUNCTION get_process_batchstep_id (value IN VARCHAR2,
217 p_process_batch_id IN NUMBER)
218 RETURN NUMBER;
219
220 FUNCTION get_process_operation_id (value IN VARCHAR2,
221 p_process_batch_id IN NUMBER,
222 p_process_batchstep_id IN NUMBER)
223 RETURN NUMBER;
224
225 FUNCTION get_process_activity_id (value IN VARCHAR2,
226 p_process_batch_id IN NUMBER,
227 p_process_batchstep_id IN NUMBER)
228 RETURN NUMBER;
229
230 FUNCTION get_process_resource_id (value IN VARCHAR2,
231 p_process_batch_id IN NUMBER,
232 p_process_batchstep_id IN NUMBER,
233 p_process_activity_id IN NUMBER)
234 RETURN NUMBER;
235
236 FUNCTION get_process_parameter_id (value IN VARCHAR2,
237 p_process_resource_id IN NUMBER)
238 RETURN NUMBER;
239
240 -- R12 OPM Deviations. Bug 4345503 End
241
242 /* R12 DR Integration. Bug 4345489 Start */
243
244 FUNCTION get_repair_line_id (value IN VARCHAR2)
245 RETURN NUMBER;
246
247 FUNCTION get_jtf_task_id (value IN VARCHAR2)
248 RETURN NUMBER;
249
250 /* R12 DR Integration. Bug 4345489 End */
251
252 --
253 -- See Bug 2588213
254 -- To support the element Maintenance Op Seq Number
255 -- to be used along with Maintenance Workorder
256 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
257 --
258
259 FUNCTION validate_maintenance_op_seq (x_org_id IN NUMBER,
260 x_maintenance_work_order_id IN NUMBER,
261 x_maintenance_op_seq IN VARCHAR2)
262 RETURN BOOLEAN;
263
264 --
265 -- End of inclusions for ASO project
266 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
267 --
268
269 -- Start of inclusions for NCM Hardcode Elements.
270 -- suramasw Thu Oct 31 10:48:59 PST 2002.
271 -- Bug 2449067.
272
273
274 FUNCTION validate_to_subinventory (x_org_id IN NUMBER,
275 x_to_subinventory IN VARCHAR2)
276 RETURN BOOLEAN;
277
278 FUNCTION get_lot_status_id (value IN VARCHAR2)
279 RETURN NUMBER;
280
281 FUNCTION get_serial_status_id (value IN VARCHAR2)
282 RETURN NUMBER;
283
284 -- End of inclusions for NCM Hardcode Elements.
285
286
287 FUNCTION value_in_sql (sql_statement IN VARCHAR2, value IN VARCHAR2)
288 RETURN BOOLEAN;
289
290
291 FUNCTION validate_transaction_date(transaction_number IN NUMBER)
292 RETURN BOOLEAN;
293
294
295 FUNCTION validate_uom(x_org_id IN NUMBER, x_item_id IN NUMBER,
296 x_uom_code IN VARCHAR2)
297 RETURN BOOLEAN;
298
299
300 FUNCTION validate_revision (x_org_id IN NUMBER, x_item_id IN NUMBER,
301 x_revision IN VARCHAR2)
302 RETURN BOOLEAN;
303
304 FUNCTION validate_lot_num(x_org_id IN NUMBER, x_item_id IN NUMBER,
305 x_lot_num IN VARCHAR2)
306 RETURN BOOLEAN;
307
308
309 FUNCTION validate_serial_num(x_org_id IN NUMBER, x_item_id IN NUMBER,
310 x_lot_num IN VARCHAR2, x_revision IN VARCHAR2, x_serial_num IN VARCHAR2)
311 RETURN BOOLEAN;
312
313 FUNCTION validate_subinventory (x_org_id IN NUMBER, x_subinventory IN VARCHAR2)
314 RETURN BOOLEAN;
315
316
317 FUNCTION validate_lot_number (x_transaction_number IN NUMBER, x_transaction_id
318 IN NUMBER, x_lot_number IN VARCHAR2)
319 RETURN BOOLEAN;
320
321
322 FUNCTION validate_serial_number (x_transaction_number IN NUMBER,
323 x_transaction_id IN NUMBER, x_lot_number IN VARCHAR2, x_serial_number IN
324 VARCHAR2)
325 RETURN BOOLEAN;
326
327
328 FUNCTION validate_op_seq_number (x_org_id IN NUMBER, x_line_id IN NUMBER,
329 x_wip_entity_id IN NUMBER, x_op_seq_number IN VARCHAR2)
330 RETURN BOOLEAN;
331
332
333 FUNCTION validate_po_line_number (x_po_header_id IN NUMBER, x_po_line_number
334 IN VARCHAR2)
335 RETURN BOOLEAN;
336
337
338 FUNCTION validate_po_shipments (x_po_line_num IN NUMBER, x_po_header_id IN
339 NUMBER, x_po_shipments IN VARCHAR2)
340 RETURN BOOLEAN;
341
342
343 FUNCTION validate_receipt_number (x_receipt_number IN VARCHAR2)
344 RETURN BOOLEAN;
345
346
347 FUNCTION get_target_element (plan_char_action_id IN NUMBER)
348 RETURN NUMBER;
349
350
351 FUNCTION get_enabled_flag (plan_id IN NUMBER, element_id IN NUMBER)
352 RETURN NUMBER;
353
354 --
355 -- See Bug 2624112
356 -- The decimal precision for a number type collection
357 -- element is to be configured at plan level.
358 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
359 --
360 -- New function to get the decimal precision for the element
361 -- from the QA_PLAN_CHARS table.
362 --
363
364 FUNCTION decimal_precision (p_plan_id IN NUMBER, p_element_id IN NUMBER)
365 RETURN NUMBER;
366
367 FUNCTION get_mandatory_flag (plan_id IN NUMBER, element_id IN NUMBER)
368 RETURN NUMBER;
369
370 FUNCTION get_sql_validation_string (element_id IN NUMBER)
371 RETURN VARCHAR2;
372
373 FUNCTION qsc_lower_reasonable_limit(spec_id IN NUMBER,
374 element_id IN NUMBER) RETURN VARCHAR2;
375
376 FUNCTION qsc_upper_reasonable_limit(spec_id IN NUMBER,
377 element_id IN NUMBER) RETURN VARCHAR2;
378
379 FUNCTION qpc_enabled_flag(plan_id IN NUMBER,
380 element_id IN NUMBER) RETURN NUMBER;
381
382 FUNCTION qpc_mandatory_flag(plan_id IN NUMBER,
383 element_id IN NUMBER) RETURN NUMBER;
384
385 FUNCTION qpc_values_exist_flag(plan_id IN NUMBER,
386 element_id IN NUMBER) RETURN NUMBER;
387
388 -- Added the below function in specs for Bug 3754667. kabalakr.
389 FUNCTION qpc_result_column_name (plan_id IN NUMBER,
390 element_id IN NUMBER) RETURN VARCHAR2;
391
392 FUNCTION get_prompt(plan_id IN NUMBER, element_id IN NUMBER)
393 RETURN VARCHAR2;
394
395 FUNCTION get_uom_code(plan_id IN NUMBER, element_id IN NUMBER)
396 RETURN VARCHAR2;
397
398 FUNCTION get_decimal_precision(plan_id IN NUMBER, element_id IN NUMBER)
399 RETURN VARCHAR2;
400
401 FUNCTION get_result_column_name (plan_id IN NUMBER, element_id IN NUMBER)
402 RETURN VARCHAR2;
403
404 -- New get functions added for the new columns in qa_plan_chars
405 -- SSQR project
406
407 FUNCTION qpc_displayed_flag(plan_id IN NUMBER,
408 element_id IN NUMBER) RETURN NUMBER;
409
410 FUNCTION qpc_poplist_flag(plan_id IN NUMBER,
411 element_id IN NUMBER) RETURN NUMBER;
412
413 FUNCTION qpc_read_only_flag(plan_id IN NUMBER,
414 element_id IN NUMBER) RETURN NUMBER;
415
416
417 PROCEDURE get_department_lov(org_id IN NUMBER, value IN VARCHAR2,
418 x_ref OUT NOCOPY LovRefCursor);
419
420
424
421 PROCEDURE get_job_lov(org_id IN NUMBER, value IN VARCHAR2,
422 x_ref OUT NOCOPY LovRefCursor);
423
425 PROCEDURE get_production_lov(org_id IN NUMBER, value IN VARCHAR2,
426 x_ref OUT NOCOPY LovRefCursor);
427
428
429 PROCEDURE get_resource_code_lov (org_id IN NUMBER, value IN VARCHAR2,
430 x_ref OUT NOCOPY LovRefCursor);
431
432
433 PROCEDURE get_supplier_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
434
435
436 PROCEDURE get_po_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
437
438
439 PROCEDURE get_customer_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
440
441
442 PROCEDURE get_so_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
443
444
445 PROCEDURE get_so_line_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
446
447 -- Bug 4958763. SQL Repository Fix
448 -- Modified procedure signature to honour po_header_id
449 -- saugupta Wed, 08 Feb 2006 23:30:37 -0800 PDT
450 PROCEDURE get_po_release_number_lov (p_po_header_id IN NUMBER, value IN VARCHAR2, x_ref OUT
451 NOCOPY LovRefCursor);
452
453
454 PROCEDURE get_project_number_lov (value IN VARCHAR2, x_ref OUT
455 NOCOPY LovRefCursor);
456
457 PROCEDURE get_LPN_lov (value IN VARCHAR2, x_ref OUT
458 NOCOPY LovRefCursor);
459
460 -- added the following to include new hardcoded element Transfer license plate number
461 -- saugupta Aug 2003
462
463 PROCEDURE get_XFR_LPN_lov (value IN VARCHAR2, x_ref OUT
464 NOCOPY LovRefCursor);
465
466 PROCEDURE get_contract_lov (value IN VARCHAR2, x_ref OUT
467 NOCOPY LovRefCursor);
468
469 PROCEDURE get_contract_line_lov (value IN VARCHAR2, contract_number IN VARCHAR2,
470 x_ref OUT NOCOPY LovRefCursor);
471
472 PROCEDURE get_deliverable_lov (value IN VARCHAR2, contract_number IN VARCHAR2,
473 line_number IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
474
475 --dgupta: Start R12 EAM Integration. Bug 4345492
476 PROCEDURE get_asset_instance_number_lov (p_org_id IN NUMBER, p_asset_group IN VARCHAR2,
477 p_asset_number IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
478
479 FUNCTION get_asset_instance_id (value IN VARCHAR2)
480 RETURN NUMBER;
481
482 FUNCTION get_asset_instance_id (p_asset_group_id IN NUMBER, p_asset_number IN VARCHAR2)
483 RETURN NUMBER;
484
485 FUNCTION get_asset_group_id (org_id IN NUMBER, value IN VARCHAR2)
486 RETURN NUMBER;
487
488 PROCEDURE get_asset_number_lov ( x_org_id IN NUMBER, x_asset_group IN VARCHAR2,
489 x_asset_instance_number IN VARCHAR2,
490 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
491 --dgupta: End R12 EAM Integration. Bug 4345492
492
493
494 PROCEDURE get_work_order_lov (org_id IN NUMBER, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
495
496
497 PROCEDURE get_task_number_lov (value IN VARCHAR2, x_ref OUT
498 NOCOPY LovRefCursor);
499
500
501 PROCEDURE get_rma_number_lov (value IN VARCHAR2, x_ref OUT
502 NOCOPY LovRefCursor);
503 --
504 -- Bug 6161802
505 -- Added procedure to return lov sql for rma line number
506 -- with rma number as a bind variable
507 -- skolluku Tue Jul 17 02:49:13 PDT 2007
508 --
509 PROCEDURE get_rma_line_num_lov (x_rma_number IN VARCHAR2,
510 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
511
512
513 PROCEDURE get_uom_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
514 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
515
516
517 PROCEDURE get_revision_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
518 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
519
520
521 PROCEDURE get_subinventory_lov (x_org_id IN NUMBER, value IN VARCHAR2,
522 x_ref OUT NOCOPY LovRefCursor);
523
524 PROCEDURE get_lot_num_lov(x_org_id IN NUMBER, x_item_name IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
525
526 PROCEDURE get_serial_num_lov(x_org_id IN NUMBER, x_item_name IN VARCHAR2, x_lot_number IN VARCHAR2, x_revision IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
527
528 PROCEDURE get_lot_number_lov (x_transaction_id IN NUMBER, value IN
529 VARCHAR2,
530 x_ref OUT NOCOPY LovRefCursor);
531
532 PROCEDURE get_serial_number_lov (x_transaction_id IN NUMBER, x_lot_number
533 IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
534
535
536 PROCEDURE get_op_seq_number_lov(org_id IN NUMBER, value IN VARCHAR2,
537 job_name IN VARCHAR2, production_line IN VARCHAR2 DEFAULT NULL,
538 x_ref OUT NOCOPY LovRefCursor);
539
540
541 PROCEDURE get_po_line_number_lov(p_po_header_id IN NUMBER, value IN
542 VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
543
544
545 PROCEDURE get_po_shipments_lov(po_line_num IN NUMBER, p_po_header_id IN NUMBER,
546 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
547
548
549 PROCEDURE get_item_lov (x_org_id IN NUMBER, value IN VARCHAR2,
550 x_ref OUT NOCOPY LovRefCursor);
551
552
553 -- Bug 5292020 adding comp item LOV to mqa.
554 -- bso Thu Jun 8 00:45:32 PDT 2006
555 PROCEDURE get_comp_item_lov(
556 p_org_id IN NUMBER,
557 p_item_name IN VARCHAR2,
561 x_ref OUT NOCOPY LovRefCursor);
558 p_job_name IN VARCHAR2,
559 p_prod_line IN VARCHAR2,
560 p_value IN VARCHAR2,
562
563
564 PROCEDURE get_asset_group_lov (x_org_id IN NUMBER, value IN VARCHAR2,
565 x_ref OUT NOCOPY LovRefCursor);
566
567
568 PROCEDURE get_asset_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
569 p_asset_number IN VARCHAR2,
570 p_asset_instance_number IN VARCHAR2, -- R12 EAM Integration. Bug 4345492
571 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
572
573 -- added the following to include new hardcoded element followup activity
574 -- saugupta Aug 2003
575
576 PROCEDURE get_followup_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
577 p_asset_number IN VARCHAR2,
578 p_asset_instance_number IN VARCHAR2, -- R12 EAM Integration. Bug 4345492
579 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
580
581 PROCEDURE get_locator_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
582 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
583
584
585 PROCEDURE get_receipt_num_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
586
587
588 PROCEDURE get_party_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
589
590 --
591 -- Included the following five get_lov procedures for ASO project
592 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
593 --
594
595 PROCEDURE get_item_instance_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
596
597 PROCEDURE get_counter_name_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
598
599 PROCEDURE get_maintenance_req_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
600
601 PROCEDURE get_service_request_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
602
603 PROCEDURE get_rework_job_lov (org_id IN NUMBER, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
604
605 --
606 -- See Bug 2588213
607 -- To support the element Maintenance Op Seq Number
608 -- to be used along with Maintenance Workorder
609 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
610 --
611
612 PROCEDURE get_maintenance_op_seq_lov(org_id IN NUMBER,
613 value IN VARCHAR2,
614 maintenance_work_order IN VARCHAR2,
615 x_ref OUT NOCOPY LovRefCursor);
616 --
617 -- End of inclusions for ASO project
618 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
619 --
620
621 -- Start of inclusions for NCM Hardcode Elements.
622 -- suramasw Thu Oct 31 10:48:59 PST 2002.
623 -- Bug 2449067.
624
625 PROCEDURE get_bill_reference_lov (x_org_id IN NUMBER, value IN VARCHAR2,
626 x_ref OUT NOCOPY LovRefCursor);
627
628 PROCEDURE get_routing_reference_lov (x_org_id IN NUMBER, value IN VARCHAR2,
629 x_ref OUT NOCOPY LovRefCursor);
630
631 PROCEDURE get_to_locator_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
632 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
633
634 PROCEDURE get_to_subinventory_lov (x_org_id IN NUMBER, value IN VARCHAR2,
635 x_ref OUT NOCOPY LovRefCursor);
636
637 PROCEDURE get_lot_status_lov (x_org_id IN NUMBER, x_lot_num IN VARCHAR2,
638 x_item_name IN VARCHAR2, value IN VARCHAR2,
639 x_ref OUT NOCOPY LovRefCursor);
640
641 PROCEDURE get_serial_status_lov(x_org_id IN NUMBER, x_serial_num IN VARCHAR2,
642 x_item_name IN VARCHAR2, value IN VARCHAR2,
643 x_ref OUT NOCOPY LovRefCursor);
644
645 -- End of inclusions for NCM Hardcode Elements.
646
647 -- R12 OPM Deviations. Bug 4345503 Start
648 PROCEDURE get_process_batch_num_lov (p_org_id IN NUMBER,
649 value IN VARCHAR2,
650 x_ref OUT NOCOPY LovRefCursor);
651
652 PROCEDURE get_process_batchstep_num_lov (p_org_id IN NUMBER,
653 p_process_batch_num IN VARCHAR2,
654 value IN VARCHAR2,
655 x_ref OUT NOCOPY LovRefCursor);
656
657 PROCEDURE get_process_operation_lov (p_org_id IN NUMBER,
658 p_process_batch_num IN VARCHAR2,
659 p_process_batchstep_num IN NUMBER,
660 value IN VARCHAR2,
661 x_ref OUT NOCOPY LovRefCursor);
662
663 PROCEDURE get_process_activity_lov (p_org_id IN NUMBER,
664 p_process_batch_num IN VARCHAR2,
665 p_process_batchstep_num IN NUMBER,
666 value IN VARCHAR2,
667 x_ref OUT NOCOPY LovRefCursor);
668
669 PROCEDURE get_process_resource_lov (p_org_id IN NUMBER,
670 p_process_batch_num IN VARCHAR2,
671 p_process_batchstep_num IN NUMBER,
672 p_process_activity IN VARCHAR2,
673 value IN VARCHAR2,
674 x_ref OUT NOCOPY LovRefCursor);
675
676 PROCEDURE get_process_parameter_lov (p_org_id IN NUMBER,
677 p_process_resource IN VARCHAR2,
678 value IN VARCHAR2,
679 x_ref OUT NOCOPY LovRefCursor);
683
680 -- R12 OPM Deviations. Bug 4345503 End
681
682 /* R12 DR Integration. Bug 4345489 Start */
684 PROCEDURE get_repair_number_lov (value IN VARCHAR2, x_ref OUT
685 NOCOPY LovRefCursor);
686
687 PROCEDURE get_jtf_task_lov (value IN VARCHAR2, x_ref OUT
688 NOCOPY LovRefCursor);
689
690 /* R12 DR Integration. Bug 4345489 End */
691
692 PROCEDURE get_plan_element_lov(plan_id IN NUMBER, char_id IN NUMBER,
693 org_id IN NUMBER, user_id IN NUMBER DEFAULT NULL,
694 value IN VARCHAR2 DEFAULT NULL,
695 x_ref OUT NOCOPY LovRefCursor);
696
697
698 PROCEDURE get_spec_details ( x_spec_id IN NUMBER, x_char_id IN NUMBER,
699 x_target_value OUT NOCOPY VARCHAR2,
700 x_lower_spec_limit OUT NOCOPY VARCHAR2,
701 x_upper_spec_limit OUT NOCOPY VARCHAR2,
702 x_lower_user_defined_limit OUT NOCOPY VARCHAR2,
703 x_upper_user_defined_limit OUT NOCOPY VARCHAR2,
704 x_lower_reasonable_limit OUT NOCOPY VARCHAR2,
705 x_upper_reasonable_limit OUT NOCOPY VARCHAR2);
706
707
708 PROCEDURE get_spec_sub_type (x_spec_id IN NUMBER,
709 x_element_name OUT NOCOPY VARCHAR2);
710
711
712 PROCEDURE get_spec_type (p_spec_id IN NUMBER,
713 x_spec_type OUT NOCOPY VARCHAR2);
714
715
716 PROCEDURE get_item_name (p_spec_id IN NUMBER,
717 x_item_name OUT NOCOPY VARCHAR2);
718
719
720 PROCEDURE get_supplier_name (p_spec_id IN NUMBER,
721 x_supplier_name OUT NOCOPY VARCHAR2);
722
723
724 PROCEDURE get_customer_name (p_spec_id IN NUMBER,
725 x_customer_name OUT NOCOPY VARCHAR2);
726
727
728 FUNCTION context_element (element_id IN NUMBER, txn_number IN NUMBER)
729 RETURN BOOLEAN;
730
731 -- Bug 4519558. OA Framework integration project. UT bug fix.
732 -- Transaction type element was erroring out for WIP transactions.
733 -- New function to validate "Transaction Type".
734 -- srhariha.Tue Aug 2 00:43:07 PDT 2005.
735 FUNCTION validate_transaction_type(p_transaction_number IN NUMBER,
736 p_org_id IN NUMBER,
737 p_user_id IN NUMBER,
738 p_value IN VARCHAR2)
739 RETURN BOOLEAN ;
740
741 -- Bug 5186397
742 -- New function to perform the UOM
743 -- conversion for the source val passed
744 -- from the source UOM to the Target UOM
745 -- SHKALYAN 01-May-2006
746 --
747 FUNCTION perform_uom_conversion (p_source_val IN VARCHAR2,
748 p_precision IN NUMBER ,
749 p_source_UOM IN VARCHAR2,
750 p_target_UOM IN VARCHAR2)
751 RETURN NUMBER;
752
753
754 --
755 -- Bug 5383667
756 -- New function to get the
757 -- Id Values from QA_results table
758 -- ntungare Thu Aug 24 02:01:29 PDT 2006
759 --
760 Function get_id_val(p_child_char_id IN NUMBER,
761 p_plan_id IN NUMBER,
762 p_collection_id IN NUMBER,
763 p_occurrence IN NUMBER)
764 RETURN VARCHAR2;
765
766 -- bug 6263809
767 -- New function to get the quantity received for
768 -- a particular shipment in a receipt.
769 -- This is needed for LPN Inspections wherein
770 -- if there is a shipment number collection element
771 -- then the quantity validation should happen
772 -- based on it.
773 -- bhsankar Fri Oct 12 03:06:24 PDT 2007
774 --
775 PROCEDURE get_qty_for_shipment(
776 p_po_num IN VARCHAR2,
777 p_line_num IN NUMBER,
778 p_ship_num IN NUMBER,
779 x_qty OUT NOCOPY NUMBER);
780
781 --
782 -- 12.1 QWB Usabitlity Improvements
783 -- Function to build the Info column value
784 --
785 FUNCTION build_info_column(p_plan_id IN NUMBER,
786 p_collection_id IN NUMBER,
787 p_occurrence IN NUMBER)
788 RETURN VARCHAR2;
789
790 -- 12.1 QWB Usability Improvemenets
791 -- New procedure to process dependent elements
792 PROCEDURE process_dependent_elements(result_string IN VARCHAR2,
793 id_string IN VARCHAR2,
794 org_id IN NUMBER,
795 p_plan_id IN NUMBER,
796 char_Id IN VARCHAR2,
797 dependent_elements OUT NOCOPY VARCHAR2,
798 disable_enable_flag_list OUT NOCOPY VARCHAR2);
799
800 END qa_plan_element_api;