1 PACKAGE qa_plan_element_api AUTHID CURRENT_USER AS
2 /* $Header: qltelemb.pls 120.20.12020000.2 2013/02/11 14:10:43 ntungare 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 -- bug 16214422
142 -- Added p_sales_order_id parameter
143 --
144 FUNCTION get_so_line_number_id (value IN VARCHAR2, p_sales_order_id IN NUMBER)
145 RETURN NUMBER;
146
147
148 FUNCTION get_po_release_number_id (value IN VARCHAR2, x_po_header_id IN NUMBER)
149 RETURN NUMBER;
150
151
152 FUNCTION get_project_number_id (value IN VARCHAR2)
153 RETURN NUMBER;
154
155
156 --
157 -- Bug 2672396. Added p_project_id because task is a dependent element.
158 -- Fix is required in qltvalb.plb also.
159 -- bso Mon Nov 25 17:29:56 PST 2002
160 --
161 FUNCTION get_task_number_id (value IN VARCHAR2, p_project_id IN NUMBER)
162 RETURN NUMBER;
163
164
165 FUNCTION get_rma_number_id (value IN VARCHAR2)
166 RETURN NUMBER;
167
168 FUNCTION get_LPN_id (value IN VARCHAR2)
169 RETURN NUMBER;
170
171 -- added the following to include new hardcoded element Transfer license plate number
172 -- saugupta Aug 2003
173
174 FUNCTION get_XFR_LPN_id (value IN VARCHAR2)
175 RETURN NUMBER;
176
177 FUNCTION get_contract_id (value IN VARCHAR2)
178 RETURN NUMBER;
179
180 FUNCTION get_contract_line_id (value IN VARCHAR2)
181 RETURN NUMBER;
182
183 FUNCTION get_deliverable_id (value IN VARCHAR2)
184 RETURN NUMBER;
185
186 FUNCTION get_work_order_id (org_id IN NUMBER, value IN VARCHAR2)
187 RETURN NUMBER;
188
189 FUNCTION get_party_id (value IN VARCHAR2)
190 RETURN NUMBER;
191
192 FUNCTION retrieve_id (sql_statement IN VARCHAR2)
193 RETURN NUMBER;
194
195 --
196 -- Included the following six get_id functions for ASO project
197 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
198 --
199
200 FUNCTION get_item_instance_id (value IN VARCHAR2)
201 RETURN NUMBER;
202
203 FUNCTION get_counter_name_id (value IN VARCHAR2)
204 RETURN NUMBER;
205
206 FUNCTION get_maintenance_req_id (value IN VARCHAR2)
207 RETURN NUMBER;
208
209 FUNCTION get_service_request_id (value IN VARCHAR2)
210 RETURN NUMBER;
211
212 FUNCTION get_rework_job_id (org_id IN NUMBER, value IN VARCHAR2)
213 RETURN NUMBER;
214
215 -- R12 OPM Deviations. Bug 4345503 Start
216 FUNCTION get_process_batch_id (value IN VARCHAR2,p_org_id IN NUMBER)
217 RETURN NUMBER;
218
219 FUNCTION get_process_batchstep_id (value IN VARCHAR2,
220 p_process_batch_id IN NUMBER)
221 RETURN NUMBER;
222
223 FUNCTION get_process_operation_id (value IN VARCHAR2,
224 p_process_batch_id IN NUMBER,
225 p_process_batchstep_id IN NUMBER)
226 RETURN NUMBER;
227
228 FUNCTION get_process_activity_id (value IN VARCHAR2,
229 p_process_batch_id IN NUMBER,
230 p_process_batchstep_id IN NUMBER)
231 RETURN NUMBER;
232
233 FUNCTION get_process_resource_id (value IN VARCHAR2,
234 p_process_batch_id IN NUMBER,
235 p_process_batchstep_id IN NUMBER,
236 p_process_activity_id IN NUMBER)
237 RETURN NUMBER;
238
239 FUNCTION get_process_parameter_id (value IN VARCHAR2,
240 p_process_resource_id IN NUMBER)
241 RETURN NUMBER;
242
243 -- R12 OPM Deviations. Bug 4345503 End
244
245 /* R12 DR Integration. Bug 4345489 Start */
246
247 FUNCTION get_repair_line_id (value IN VARCHAR2)
248 RETURN NUMBER;
249
250 FUNCTION get_jtf_task_id (value IN VARCHAR2)
251 RETURN NUMBER;
252
253 /* R12 DR Integration. Bug 4345489 End */
254
255 --
256 -- See Bug 2588213
257 -- To support the element Maintenance Op Seq Number
258 -- to be used along with Maintenance Workorder
259 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
260 --
261
262 FUNCTION validate_maintenance_op_seq (x_org_id IN NUMBER,
263 x_maintenance_work_order_id IN NUMBER,
264 x_maintenance_op_seq IN VARCHAR2)
265 RETURN BOOLEAN;
266
267 --
268 -- End of inclusions for ASO project
269 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
270 --
271
272 -- Start of inclusions for NCM Hardcode Elements.
273 -- suramasw Thu Oct 31 10:48:59 PST 2002.
274 -- Bug 2449067.
275
276
277 FUNCTION validate_to_subinventory (x_org_id IN NUMBER,
278 x_to_subinventory IN VARCHAR2)
279 RETURN BOOLEAN;
280
281 FUNCTION get_lot_status_id (value IN VARCHAR2)
282 RETURN NUMBER;
283
284 FUNCTION get_serial_status_id (value IN VARCHAR2)
285 RETURN NUMBER;
286
287 -- End of inclusions for NCM Hardcode Elements.
288
289
290 FUNCTION value_in_sql (sql_statement IN VARCHAR2, value IN VARCHAR2)
291 RETURN BOOLEAN;
292
293
294 FUNCTION validate_transaction_date(transaction_number IN NUMBER)
295 RETURN BOOLEAN;
296
297
298 FUNCTION validate_uom(x_org_id IN NUMBER, x_item_id IN NUMBER,
299 x_uom_code IN VARCHAR2)
300 RETURN BOOLEAN;
301
302
303 FUNCTION validate_revision (x_org_id IN NUMBER, x_item_id IN NUMBER,
304 x_revision IN VARCHAR2)
305 RETURN BOOLEAN;
306
307 FUNCTION validate_lot_num(x_org_id IN NUMBER, x_item_id IN NUMBER,
308 x_lot_num IN VARCHAR2)
309 RETURN BOOLEAN;
310
311
312 FUNCTION validate_serial_num(x_org_id IN NUMBER, x_item_id IN NUMBER,
313 x_lot_num IN VARCHAR2, x_revision IN VARCHAR2, x_serial_num IN VARCHAR2)
314 RETURN BOOLEAN;
315
316 FUNCTION validate_subinventory (x_org_id IN NUMBER, x_subinventory IN VARCHAR2)
317 RETURN BOOLEAN;
318
319
320 FUNCTION validate_lot_number (x_transaction_number IN NUMBER, x_transaction_id
321 IN NUMBER, x_lot_number IN VARCHAR2)
322 RETURN BOOLEAN;
323
324
325 FUNCTION validate_serial_number (x_transaction_number IN NUMBER,
326 x_transaction_id IN NUMBER, x_lot_number IN VARCHAR2, x_serial_number IN
327 VARCHAR2)
328 RETURN BOOLEAN;
329
330
331 FUNCTION validate_op_seq_number (x_org_id IN NUMBER, x_line_id IN NUMBER,
332 x_wip_entity_id IN NUMBER, x_op_seq_number IN VARCHAR2)
333 RETURN BOOLEAN;
334
335
336 FUNCTION validate_po_line_number (x_po_header_id IN NUMBER, x_po_line_number
337 IN VARCHAR2)
338 RETURN BOOLEAN;
339
340 --
341 -- bug 9652549 CLM changes
342 --
343 FUNCTION validate_po_shipments (x_po_line_num IN VARCHAR2, x_po_header_id IN
344 NUMBER, x_po_shipments IN VARCHAR2)
345 RETURN BOOLEAN;
346
347
348 FUNCTION validate_receipt_number (x_receipt_number IN VARCHAR2)
349 RETURN BOOLEAN;
350
351
352 FUNCTION get_target_element (plan_char_action_id IN NUMBER)
353 RETURN NUMBER;
354
355
356 FUNCTION get_enabled_flag (plan_id IN NUMBER, element_id IN NUMBER)
357 RETURN NUMBER;
358
359 --
360 -- See Bug 2624112
361 -- The decimal precision for a number type collection
362 -- element is to be configured at plan level.
363 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
364 --
365 -- New function to get the decimal precision for the element
366 -- from the QA_PLAN_CHARS table.
367 --
368
369 FUNCTION decimal_precision (p_plan_id IN NUMBER, p_element_id IN NUMBER)
370 RETURN NUMBER;
371
372 FUNCTION get_mandatory_flag (plan_id IN NUMBER, element_id IN NUMBER)
373 RETURN NUMBER;
374
375 FUNCTION get_sql_validation_string (element_id IN NUMBER)
376 RETURN VARCHAR2;
377
378 FUNCTION qsc_lower_reasonable_limit(spec_id IN NUMBER,
379 element_id IN NUMBER) RETURN VARCHAR2;
380
381 FUNCTION qsc_upper_reasonable_limit(spec_id IN NUMBER,
382 element_id IN NUMBER) RETURN VARCHAR2;
383
384 FUNCTION qpc_enabled_flag(plan_id IN NUMBER,
385 element_id IN NUMBER) RETURN NUMBER;
386
387 FUNCTION qpc_mandatory_flag(plan_id IN NUMBER,
388 element_id IN NUMBER) RETURN NUMBER;
389
390 FUNCTION qpc_values_exist_flag(plan_id IN NUMBER,
391 element_id IN NUMBER) RETURN NUMBER;
392
393 -- Added the below function in specs for Bug 3754667. kabalakr.
394 FUNCTION qpc_result_column_name (plan_id IN NUMBER,
395 element_id IN NUMBER) RETURN VARCHAR2;
396
397 FUNCTION get_prompt(plan_id IN NUMBER, element_id IN NUMBER)
398 RETURN VARCHAR2;
399
400 FUNCTION get_uom_code(plan_id IN NUMBER, element_id IN NUMBER)
401 RETURN VARCHAR2;
402
403 FUNCTION get_decimal_precision(plan_id IN NUMBER, element_id IN NUMBER)
404 RETURN VARCHAR2;
405
406 FUNCTION get_result_column_name (plan_id IN NUMBER, element_id IN NUMBER)
407 RETURN VARCHAR2;
408
409 -- New get functions added for the new columns in qa_plan_chars
410 -- SSQR project
411
412 FUNCTION qpc_displayed_flag(plan_id IN NUMBER,
413 element_id IN NUMBER) RETURN NUMBER;
414
415 FUNCTION qpc_poplist_flag(plan_id IN NUMBER,
416 element_id IN NUMBER) RETURN NUMBER;
417
418 FUNCTION qpc_read_only_flag(plan_id IN NUMBER,
419 element_id IN NUMBER) RETURN NUMBER;
420
421
422 PROCEDURE get_department_lov(org_id IN NUMBER, value IN VARCHAR2,
423 x_ref OUT NOCOPY LovRefCursor);
424
425
426 PROCEDURE get_job_lov(org_id IN NUMBER, value IN VARCHAR2,
427 x_ref OUT NOCOPY LovRefCursor);
428
429
430 PROCEDURE get_production_lov(org_id IN NUMBER, value IN VARCHAR2,
431 x_ref OUT NOCOPY LovRefCursor);
432
433
434 PROCEDURE get_resource_code_lov (org_id IN NUMBER, value IN VARCHAR2,
435 x_ref OUT NOCOPY LovRefCursor);
436
437
438 PROCEDURE get_supplier_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
439
440
444 PROCEDURE get_customer_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
441 PROCEDURE get_po_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
442
443
445
446
447 PROCEDURE get_so_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
448
449
450 -- Bug 7716875.Added parameter x_so_number.pdube
451 -- PROCEDURE get_so_line_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
452 PROCEDURE get_so_line_number_lov (x_so_number IN VARCHAR2,value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
453
454 -- Bug 4958763. SQL Repository Fix
455 -- Modified procedure signature to honour po_header_id
456 -- saugupta Wed, 08 Feb 2006 23:30:37 -0800 PDT
457 PROCEDURE get_po_release_number_lov (p_po_header_id IN NUMBER, value IN VARCHAR2, x_ref OUT
458 NOCOPY LovRefCursor);
459
460 -- Bug 9817478.
461 -- Overloaded procedure which uses PO Number and PO Line Num to derive PO Release Num's LOV
462 -- skolluku
463 PROCEDURE get_po_release_number_lov (p_po_header_id IN NUMBER, po_line_num IN VARCHAR2, value IN VARCHAR2,
464 x_ref OUT NOCOPY LovRefCursor);
465
466 PROCEDURE get_project_number_lov (value IN VARCHAR2, x_ref OUT
467 NOCOPY LovRefCursor);
468
469 PROCEDURE get_LPN_lov (value IN VARCHAR2, x_ref OUT
470 NOCOPY LovRefCursor);
471
472 -- added the following to include new hardcoded element Transfer license plate number
473 -- saugupta Aug 2003
474
475 PROCEDURE get_XFR_LPN_lov (value IN VARCHAR2, x_ref OUT
476 NOCOPY LovRefCursor);
477
478 PROCEDURE get_contract_lov (value IN VARCHAR2, x_ref OUT
479 NOCOPY LovRefCursor);
480
481 PROCEDURE get_contract_line_lov (value IN VARCHAR2, contract_number IN VARCHAR2,
482 x_ref OUT NOCOPY LovRefCursor);
483
484 PROCEDURE get_deliverable_lov (value IN VARCHAR2, contract_number IN VARCHAR2,
485 line_number IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
486
487 --dgupta: Start R12 EAM Integration. Bug 4345492
488 PROCEDURE get_asset_instance_number_lov (p_org_id IN NUMBER, p_asset_group IN VARCHAR2,
489 p_asset_number IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
490
491 FUNCTION get_asset_instance_id (value IN VARCHAR2)
492 RETURN NUMBER;
493
494 FUNCTION get_asset_instance_id (p_asset_group_id IN NUMBER, p_asset_number IN VARCHAR2)
495 RETURN NUMBER;
496
497 FUNCTION get_asset_group_id (org_id IN NUMBER, value IN VARCHAR2)
498 RETURN NUMBER;
499
500 PROCEDURE get_asset_number_lov ( x_org_id IN NUMBER, x_asset_group IN VARCHAR2,
501 x_asset_instance_number IN VARCHAR2,
502 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
503 --dgupta: End R12 EAM Integration. Bug 4345492
504
505
506 PROCEDURE get_work_order_lov (org_id IN NUMBER, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
507
508
509 PROCEDURE get_task_number_lov (value IN VARCHAR2, x_ref OUT
510 NOCOPY LovRefCursor);
511
512
513 PROCEDURE get_rma_number_lov (value IN VARCHAR2, x_ref OUT
514 NOCOPY LovRefCursor);
515 --
516 -- Bug 6161802
517 -- Added procedure to return lov sql for rma line number
518 -- with rma number as a bind variable
519 -- skolluku Tue Jul 17 02:49:13 PDT 2007
520 --
521 PROCEDURE get_rma_line_num_lov (x_rma_number IN VARCHAR2,
522 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
523
524
525 PROCEDURE get_uom_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
526 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
527
528
529 PROCEDURE get_revision_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
530 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
531
532
533 PROCEDURE get_subinventory_lov (x_org_id IN NUMBER, value IN VARCHAR2,
534 x_ref OUT NOCOPY LovRefCursor);
535
536 PROCEDURE get_lot_num_lov(x_org_id IN NUMBER, x_item_name IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
537
538 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);
539
540 PROCEDURE get_lot_number_lov (x_transaction_id IN NUMBER, value IN
541 VARCHAR2,
542 x_ref OUT NOCOPY LovRefCursor);
543
544 PROCEDURE get_serial_number_lov (x_transaction_id IN NUMBER, x_lot_number
545 IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
546
547
548 PROCEDURE get_op_seq_number_lov(org_id IN NUMBER, value IN VARCHAR2,
549 job_name IN VARCHAR2, production_line IN VARCHAR2 DEFAULT NULL,
550 x_ref OUT NOCOPY LovRefCursor);
551
552
553 PROCEDURE get_po_line_number_lov(p_po_header_id IN NUMBER, value IN
554 VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
555
556 --
557 -- bug 9652549 CLM changes
558 --
559 PROCEDURE get_po_shipments_lov(po_line_num IN VARCHAR2, p_po_header_id IN NUMBER,
560 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
561
562 -- Bug 9817478
563 -- Overloaded procedure which uses PO Release Num in addition to PO Number and PO Line Num
564 -- to get lov for PO Shipments.
565 -- skolluku
566 PROCEDURE get_po_shipments_lov (po_line_num IN VARCHAR2, p_po_header_id IN NUMBER, po_release_num IN VARCHAR2,
567 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
568
569 PROCEDURE get_item_lov (x_org_id IN NUMBER, value IN VARCHAR2,
570 x_ref OUT NOCOPY LovRefCursor);
571
572
573 -- Bug 5292020 adding comp item LOV to mqa.
574 -- bso Thu Jun 8 00:45:32 PDT 2006
575 PROCEDURE get_comp_item_lov(
576 p_org_id IN NUMBER,
577 p_item_name IN VARCHAR2,
578 p_job_name IN VARCHAR2,
579 p_prod_line IN VARCHAR2,
580 p_value IN VARCHAR2,
581 x_ref OUT NOCOPY LovRefCursor);
582
583
587
584 PROCEDURE get_asset_group_lov (x_org_id IN NUMBER, value IN VARCHAR2,
585 x_ref OUT NOCOPY LovRefCursor);
586
588 PROCEDURE get_asset_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
589 p_asset_number IN VARCHAR2,
590 p_asset_instance_number IN VARCHAR2, -- R12 EAM Integration. Bug 4345492
591 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
592
593 -- added the following to include new hardcoded element followup activity
594 -- saugupta Aug 2003
595
596 PROCEDURE get_followup_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
597 p_asset_number IN VARCHAR2,
598 p_asset_instance_number IN VARCHAR2, -- R12 EAM Integration. Bug 4345492
599 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
600
601 PROCEDURE get_locator_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
602 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
603
604
605 PROCEDURE get_receipt_num_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
606
607
608 PROCEDURE get_party_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
609
610 --
611 -- Included the following five get_lov procedures for ASO project
612 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
613 --
614
615 PROCEDURE get_item_instance_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
616
617 --
618 -- Bug 9032151
619 -- Overloading above procedure with the new one which takes
620 -- care of the dependency of item instance on item.
621 -- skolluku
622 --
623 PROCEDURE get_item_instance_lov (p_org_id IN NUMBER, p_item_name IN VARCHAR2,
624 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
625
626 --
627 -- Bug 9359442
628 -- New procedure which returns lov for item instance serial based on item.
629 -- skolluku
630 --
631 PROCEDURE get_item_instance_serial_lov (p_org_id IN NUMBER, p_item_name IN VARCHAR2,
632 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
633
634 PROCEDURE get_counter_name_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
635
636 PROCEDURE get_maintenance_req_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
637
638 PROCEDURE get_service_request_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
639
640 PROCEDURE get_rework_job_lov (org_id IN NUMBER, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
641
642 --
643 -- See Bug 2588213
644 -- To support the element Maintenance Op Seq Number
645 -- to be used along with Maintenance Workorder
646 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
647 --
648
649 PROCEDURE get_maintenance_op_seq_lov(org_id IN NUMBER,
650 value IN VARCHAR2,
651 maintenance_work_order IN VARCHAR2,
652 x_ref OUT NOCOPY LovRefCursor);
653 --
654 -- End of inclusions for ASO project
655 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
656 --
657
658 -- Start of inclusions for NCM Hardcode Elements.
659 -- suramasw Thu Oct 31 10:48:59 PST 2002.
660 -- Bug 2449067.
661
662 PROCEDURE get_bill_reference_lov (x_org_id IN NUMBER, value IN VARCHAR2,
663 x_ref OUT NOCOPY LovRefCursor);
664
665 PROCEDURE get_routing_reference_lov (x_org_id IN NUMBER, value IN VARCHAR2,
666 x_ref OUT NOCOPY LovRefCursor);
667
668 PROCEDURE get_to_locator_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
669 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor);
670
671 PROCEDURE get_to_subinventory_lov (x_org_id IN NUMBER, value IN VARCHAR2,
672 x_ref OUT NOCOPY LovRefCursor);
673
674 PROCEDURE get_lot_status_lov (x_org_id IN NUMBER, x_lot_num IN VARCHAR2,
675 x_item_name IN VARCHAR2, value IN VARCHAR2,
676 x_ref OUT NOCOPY LovRefCursor);
677
678 PROCEDURE get_serial_status_lov(x_org_id IN NUMBER, x_serial_num IN VARCHAR2,
679 x_item_name IN VARCHAR2, value IN VARCHAR2,
680 x_ref OUT NOCOPY LovRefCursor);
681
682 -- End of inclusions for NCM Hardcode Elements.
683
684 -- R12 OPM Deviations. Bug 4345503 Start
685 PROCEDURE get_process_batch_num_lov (p_org_id IN NUMBER,
686 value IN VARCHAR2,
687 x_ref OUT NOCOPY LovRefCursor);
688
689 PROCEDURE get_process_batchstep_num_lov (p_org_id IN NUMBER,
690 p_process_batch_num IN VARCHAR2,
691 value IN VARCHAR2,
692 x_ref OUT NOCOPY LovRefCursor);
693
694 PROCEDURE get_process_operation_lov (p_org_id IN NUMBER,
695 p_process_batch_num IN VARCHAR2,
696 p_process_batchstep_num IN NUMBER,
697 value IN VARCHAR2,
698 x_ref OUT NOCOPY LovRefCursor);
699
700 PROCEDURE get_process_activity_lov (p_org_id IN NUMBER,
701 p_process_batch_num IN VARCHAR2,
702 p_process_batchstep_num IN NUMBER,
703 value IN VARCHAR2,
704 x_ref OUT NOCOPY LovRefCursor);
705
706 PROCEDURE get_process_resource_lov (p_org_id IN NUMBER,
707 p_process_batch_num IN VARCHAR2,
708 p_process_batchstep_num IN NUMBER,
709 p_process_activity IN VARCHAR2,
710 value IN VARCHAR2,
711 x_ref OUT NOCOPY LovRefCursor);
712
713 PROCEDURE get_process_parameter_lov (p_org_id IN NUMBER,
714 p_process_resource IN VARCHAR2,
715 value IN VARCHAR2,
716 x_ref OUT NOCOPY LovRefCursor);
720
717 -- R12 OPM Deviations. Bug 4345503 End
718
719 /* R12 DR Integration. Bug 4345489 Start */
721 PROCEDURE get_repair_number_lov (value IN VARCHAR2, x_ref OUT
722 NOCOPY LovRefCursor);
723
724 PROCEDURE get_jtf_task_lov (value IN VARCHAR2, x_ref OUT
725 NOCOPY LovRefCursor);
726
727 /* R12 DR Integration. Bug 4345489 End */
728
729 PROCEDURE get_plan_element_lov(plan_id IN NUMBER, char_id IN NUMBER,
730 org_id IN NUMBER, user_id IN NUMBER DEFAULT NULL,
731 value IN VARCHAR2 DEFAULT NULL,
732 x_ref OUT NOCOPY LovRefCursor);
733
734
735 PROCEDURE get_spec_details ( x_spec_id IN NUMBER, x_char_id IN NUMBER,
736 x_target_value OUT NOCOPY VARCHAR2,
737 x_lower_spec_limit OUT NOCOPY VARCHAR2,
738 x_upper_spec_limit OUT NOCOPY VARCHAR2,
739 x_lower_user_defined_limit OUT NOCOPY VARCHAR2,
740 x_upper_user_defined_limit OUT NOCOPY VARCHAR2,
741 x_lower_reasonable_limit OUT NOCOPY VARCHAR2,
742 x_upper_reasonable_limit OUT NOCOPY VARCHAR2);
743
744
745 PROCEDURE get_spec_sub_type (x_spec_id IN NUMBER,
746 x_element_name OUT NOCOPY VARCHAR2);
747
748
749 PROCEDURE get_spec_type (p_spec_id IN NUMBER,
750 x_spec_type OUT NOCOPY VARCHAR2);
751
752
753 PROCEDURE get_item_name (p_spec_id IN NUMBER,
754 x_item_name OUT NOCOPY VARCHAR2);
755
756
757 PROCEDURE get_supplier_name (p_spec_id IN NUMBER,
758 x_supplier_name OUT NOCOPY VARCHAR2);
759
760
761 PROCEDURE get_customer_name (p_spec_id IN NUMBER,
762 x_customer_name OUT NOCOPY VARCHAR2);
763
764
765 FUNCTION context_element (element_id IN NUMBER, txn_number IN NUMBER)
766 RETURN BOOLEAN;
767
768 -- Bug 4519558. OA Framework integration project. UT bug fix.
769 -- Transaction type element was erroring out for WIP transactions.
770 -- New function to validate "Transaction Type".
771 -- srhariha.Tue Aug 2 00:43:07 PDT 2005.
772 FUNCTION validate_transaction_type(p_transaction_number IN NUMBER,
773 p_org_id IN NUMBER,
774 p_user_id IN NUMBER,
775 p_value IN VARCHAR2)
776 RETURN BOOLEAN ;
777
778 -- Bug 5186397
779 -- New function to perform the UOM
780 -- conversion for the source val passed
781 -- from the source UOM to the Target UOM
782 -- SHKALYAN 01-May-2006
783 --
784 FUNCTION perform_uom_conversion (p_source_val IN VARCHAR2,
785 p_precision IN NUMBER ,
786 p_source_UOM IN VARCHAR2,
787 p_target_UOM IN VARCHAR2)
788 RETURN NUMBER;
789
790
791 --
792 -- Bug 5383667
793 -- New function to get the
794 -- Id Values from QA_results table
795 -- ntungare Thu Aug 24 02:01:29 PDT 2006
796 --
797 Function get_id_val(p_child_char_id IN NUMBER,
798 p_plan_id IN NUMBER,
799 p_collection_id IN NUMBER,
800 p_occurrence IN NUMBER)
801 RETURN VARCHAR2;
802
803 -- bug 6263809
804 -- New function to get the quantity received for
805 -- a particular shipment in a receipt.
806 -- This is needed for LPN Inspections wherein
807 -- if there is a shipment number collection element
808 -- then the quantity validation should happen
809 -- based on it.
810 -- bhsankar Fri Oct 12 03:06:24 PDT 2007
811 --
812 PROCEDURE get_qty_for_shipment(
813 p_po_num IN VARCHAR2,
814 p_line_num IN VARCHAR2,
815 p_ship_num IN NUMBER,
816 x_qty OUT NOCOPY NUMBER);
817
818 --
819 -- 12.1 QWB Usabitlity Improvements
820 -- Function to build the Info column value
821 --
822 FUNCTION build_info_column(p_plan_id IN NUMBER,
823 p_collection_id IN NUMBER,
824 p_occurrence IN NUMBER)
825 RETURN VARCHAR2;
826
827 -- 12.1 QWB Usability Improvemenets
828 -- New procedure to process dependent elements
829 PROCEDURE process_dependent_elements(result_string IN VARCHAR2,
830 id_string IN VARCHAR2,
831 org_id IN NUMBER,
832 p_plan_id IN NUMBER,
833 char_Id IN VARCHAR2,
834 dependent_elements OUT NOCOPY VARCHAR2,
835 disable_enable_flag_list OUT NOCOPY VARCHAR2);
836
837 END qa_plan_element_api;
[Home] [Help]