1 PACKAGE qa_ss_lov_api AUTHID CURRENT_USER AS
2 /* $Header: qltsslob.pls 120.5.12010000.8 2010/04/26 17:14:55 ntungare ship $ */
3
4
5 TYPE LovRecord IS RECORD (
6 code VARCHAR2(150),
7 description VARCHAR2(2000));
8
9 TYPE LovRefCursor IS REF CURSOR;
10
11 FUNCTION values_exist (plan_id IN NUMBER, element_id IN NUMBER)
12 RETURN BOOLEAN;
13
14
15 FUNCTION sql_validation_exists (element_id IN NUMBER)
16 RETURN BOOLEAN;
17
18
19 FUNCTION element_in_plan (plan_id IN NUMBER, element_id IN NUMBER)
20 RETURN BOOLEAN;
21
22
23 FUNCTION get_sql_validation_string (element_id IN NUMBER)
24 RETURN VARCHAR2;
25
26
27 PROCEDURE get_department_lov(org_id IN NUMBER, value IN VARCHAR2,
28 x_lov_sql OUT NOCOPY VARCHAR2);
29
30
31 PROCEDURE get_job_lov(org_id IN NUMBER, value IN VARCHAR2,
32 x_lov_sql OUT NOCOPY VARCHAR2);
33
34 PROCEDURE get_work_order_lov (org_id IN NUMBER, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
35
36 PROCEDURE get_production_lov(org_id IN NUMBER, value IN VARCHAR2,
37 x_lov_sql OUT NOCOPY VARCHAR2);
38
39
40 PROCEDURE get_resource_code_lov (org_id IN NUMBER, value IN VARCHAR2,
41 x_lov_sql OUT NOCOPY VARCHAR2);
42
43
44 PROCEDURE get_supplier_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
45
46
47 PROCEDURE get_po_number_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
48
49
50 PROCEDURE get_customer_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
51
52
53 PROCEDURE get_so_number_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
54
55
56 -- Bug 7716875.Changed the definition of the procedure
57 -- to introduce additional parameters.pdube Mon Apr 13 03:25:19 PDT 2009
58 -- PROCEDURE get_so_line_number_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
59 PROCEDURE get_so_line_number_lov (p_plan_id IN NUMBER,
60 p_so_number IN VARCHAR2,
61 value IN VARCHAR2,
62 x_lov_sql OUT NOCOPY VARCHAR2);
63
64 -- Bug 5003511 SQLID : 15008630
65 -- Release number is dependent on PO Number.
66 -- As per safe spec, creating procedure for getting the lov sql.
67 -- and commneting out unused overridden procedure below
68 -- saugupta Tue, 14 Feb 2006 07:07:11 -0800 PDT
69 /*
70 PROCEDURE get_po_release_number_lov (value IN VARCHAR2, x_lov_sql OUT
71 NOCOPY VARCHAR2);
72 */
73 PROCEDURE get_po_release_number_lov (p_plan_id IN NUMBER,
74 po_header_id IN VARCHAR2,
75 value IN VARCHAR2,
76 x_lov_sql OUT NOCOPY VARCHAR2);
77
78 PROCEDURE get_project_number_lov (value IN VARCHAR2, x_lov_sql OUT
79 NOCOPY VARCHAR2);
80
81
82 PROCEDURE get_task_number_lov (value IN VARCHAR2, x_lov_sql OUT
83 NOCOPY VARCHAR2);
84
85
86 PROCEDURE get_rma_number_lov (value IN VARCHAR2, x_lov_sql OUT
87 NOCOPY VARCHAR2);
88
89
90 PROCEDURE get_uom_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
91 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
92
93
94 PROCEDURE get_revision_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
95 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
96
97
98 PROCEDURE get_subinventory_lov (org_id IN NUMBER, value IN VARCHAR2,
99 x_lov_sql OUT NOCOPY VARCHAR2);
100
101
102 PROCEDURE get_lot_number_lov (x_transaction_id IN NUMBER, value IN VARCHAR2,
103 x_lov_sql OUT NOCOPY VARCHAR2);
104
105
106 PROCEDURE get_serial_number_lov (x_transaction_id IN NUMBER, x_lot_number
107 IN VARCHAR2, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
108
109 --dgupta: Start R12 EAM Integration. Bug 4345492
110 PROCEDURE get_asset_instance_number_lov (plan_id IN NUMBER, x_org_id IN NUMBER, x_asset_group IN VARCHAR2,x_asset_number IN VARCHAR2,
111 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
112 --dgupta: End R12 EAM Integration. Bug 4345492
113
114 PROCEDURE get_asset_number_lov ( x_org_id IN NUMBER, x_asset_group IN VARCHAR2, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
115
116 PROCEDURE get_op_seq_number_lov(org_id IN NUMBER, value IN VARCHAR2,
117 job_name IN VARCHAR2, production_line IN VARCHAR2 DEFAULT NULL,
118 x_lov_sql OUT NOCOPY VARCHAR2);
119
120
121 PROCEDURE get_po_line_number_lov (po_number IN VARCHAR2, value IN
122 VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
123
124 --
125 -- bug 9652549 CLM changes
126 --
127 PROCEDURE get_po_shipments_lov (po_line_num IN VARCHAR2, po_number IN VARCHAR2,
128 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
129
130 --
131 -- Bug 7197055
132 -- Added new parameter, production line, to base the item's lov on prod line,
133 -- whenever a value for prod line is present.
134 -- skolluku
135 --
136 PROCEDURE get_item_lov (org_id IN NUMBER, value IN VARCHAR2, p_production_line IN
137 VARCHAR2 DEFAULT NULL, x_lov_sql OUT NOCOPY VARCHAR2);
138
139 PROCEDURE get_asset_group_lov (x_org_id IN NUMBER, value IN VARCHAR2,
140 x_lov_sql OUT NOCOPY VARCHAR2);
141
142
143 PROCEDURE get_locator_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
144 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
145
146
147 PROCEDURE get_receipt_num_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
148
149
150 PROCEDURE get_party_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
151
152 --
153 -- Included the following get_lov procedures for ASO project
154 -- rkunchal Thu Aug 1 12:04:56 PDT 2002
155 --
156
157 PROCEDURE get_item_instance_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
158
159 PROCEDURE get_counter_name_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
160
161 PROCEDURE get_maintenance_req_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
162
163 PROCEDURE get_service_request_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
164
165 PROCEDURE get_rework_job_lov (org_id IN NUMBER, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
166
167 PROCEDURE get_disposition_source_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
168
169 PROCEDURE get_disposition_action_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
170
171 PROCEDURE get_disposition_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
172
173 PROCEDURE get_disposition_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
174
175 /* R12 DR Integration. Bug 4345489 Start */
176 PROCEDURE get_repair_order_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
177
178 PROCEDURE get_jtf_task_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
179 /* R12 DR Integration. Bug 4345489 End */
180
181 -- R12 OPM Deviations. Bug 4345503 Start
182
183 PROCEDURE get_process_batch_num_lov
184 (org_id IN NUMBER,
185 value IN VARCHAR2,
186 x_lov_sql OUT NOCOPY VARCHAR2);
187
188 PROCEDURE get_process_batchstep_num_lov
189 (org_id IN NUMBER,
190 plan_id IN NUMBER,
191 process_batch_num IN VARCHAR2,
192 value IN VARCHAR2,
193 x_lov_sql OUT NOCOPY VARCHAR2);
194
195 PROCEDURE get_process_operation_lov
196 (org_id IN NUMBER,
197 plan_id IN NUMBER,
198 process_batch_num IN VARCHAR2,
199 process_batchstep_num IN VARCHAR2,
200 value IN VARCHAR2,
201 x_lov_sql OUT NOCOPY VARCHAR2);
202
203 PROCEDURE get_process_activity_lov
204 (org_id IN NUMBER,
205 plan_id IN NUMBER,
206 process_batch_num IN VARCHAR2,
207 process_batchstep_num IN VARCHAR2,
208 value IN VARCHAR2,
209 x_lov_sql OUT NOCOPY VARCHAR2);
210
211 PROCEDURE get_process_resource_lov
212 (org_id IN NUMBER,
213 plan_id IN NUMBER,
214 process_batch_num IN VARCHAR2,
215 process_batchstep_num IN VARCHAR2,
216 process_activity IN VARCHAR2,
217 value IN VARCHAR2,
218 x_lov_sql OUT NOCOPY VARCHAR2);
219
220 PROCEDURE get_process_parameter_lov
221 (org_id IN NUMBER,
222 plan_id IN NUMBER,
223 process_resource IN VARCHAR2,
224 value IN VARCHAR2,
225 x_lov_sql OUT NOCOPY VARCHAR2);
226
227 -- R12 OPM Deviations. Bug 4345503 End
228 --
229 -- See Bug 2588213
230 -- To support the element Maintenance Op Seq Number
231 -- to be used along with Maintenance Workorder
232 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
233 --
234
235 PROCEDURE get_maintenance_op_seq_lov(org_id IN NUMBER,
236 value IN VARCHAR2,
237 maintenance_work_order IN VARCHAR2,
238 x_lov_sql OUT NOCOPY VARCHAR2);
239
240 --
241 -- End of inclusions for ASO project
242 -- rkunchal Thu Aug 1 12:04:56 PDT 2002
243 --
244
245 PROCEDURE get_plan_element_lov(plan_id IN NUMBER, char_id IN NUMBER,
246 org_id IN NUMBER, user_id IN NUMBER DEFAULT NULL,
247 x_lov_sql OUT NOCOPY VARCHAR2);
248
249 -- Start of inclusions for NCM Hardcode Elements.
250 -- suramasw Thu Oct 31 10:48:59 PST 2002.
251 -- Bug 2449067.
252
253
254 PROCEDURE get_bill_reference_lov (org_id IN NUMBER, value IN VARCHAR2,
255 x_lov_sql OUT NOCOPY VARCHAR2);
256
257 PROCEDURE get_routing_reference_lov (org_id IN NUMBER, value IN VARCHAR2,
258 x_lov_sql OUT NOCOPY VARCHAR2);
259
260 PROCEDURE get_to_locator_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
261 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
262
263 PROCEDURE get_to_subinventory_lov (org_id IN NUMBER, value IN VARCHAR2,
264 x_lov_sql OUT NOCOPY VARCHAR2);
265
266 PROCEDURE get_lot_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
267
268 -- Bug 7588754.pdube Wed Apr 15 07:37:25 PDT 2009
269 -- PROCEDURE get_serial_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
270 PROCEDURE get_serial_status_lov(value IN VARCHAR2,item_name IN VARCHAR2,
271 serial_num IN VARCHAR2,x_lov_sql OUT NOCOPY VARCHAR2);
272
273 PROCEDURE get_nonconformance_source_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
274
275 PROCEDURE get_nonconform_severity_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
276
277 PROCEDURE get_nonconform_priority_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
278
279 PROCEDURE get_nonconformance_type_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
280
281 PROCEDURE get_nonconformance_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
282
283 -- End of inclusions for NCM Hardcode Elements.
284
285 --anagarwa Fri Nov 15 13:03:35 PST 2002
286 --Following added for new CAR lov's
287
288 PROCEDURE get_request_source_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
289
290 PROCEDURE get_request_priority_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
291
292 PROCEDURE get_request_severity_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
293
294 PROCEDURE get_request_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2);
295
296 -- End of inclusions for CAR Hardcode Elements.
297
298 /*
299 FUNCTION get_lov_sql (
300 plan_id IN NUMBER,
301 char_id IN NUMBER,
302 org_id IN NUMBER DEFAULT NULL,
303 user_id IN NUMBER DEFAULT NULL,
304 item_name IN VARCHAR2 DEFAULT NULL,
305 job_name IN VARCHAR2 DEFAULT NULL,
306 lot_number IN VARCHAR2 DEFAULT NULL,
307 po_line_number IN NUMBER DEFAULT NULL,
308 po_number IN VARCHAR2 DEFAULT NULL,
309 production_line IN VARCHAR2 DEFAULT NULL,
310 transaction_id IN NUMBER DEFAULT NULL,
311 value IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
312 */
313
314 FUNCTION get_lov_sql (
315 plan_id IN NUMBER,
316 char_id IN NUMBER,
317 org_id IN NUMBER DEFAULT NULL,
318 user_id IN NUMBER DEFAULT NULL,
319 depen1 IN VARCHAR2 DEFAULT NULL,
320 depen2 IN VARCHAR2 DEFAULT NULL,
321 depen3 IN VARCHAR2 DEFAULT NULL,
322 value IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
323
324 -- Bug 4270911. SQL bind compliance fix.
325 -- Please see bugdb for more details and TD link.
326 -- srhariha. Thu Apr 7 21:43:08 PDT 2005.
327
328 FUNCTION get_lov_bind_values (
329 plan_id IN NUMBER,
330 char_id IN NUMBER,
331 org_id IN NUMBER DEFAULT NULL,
332 user_id IN NUMBER DEFAULT NULL,
333 depen1 IN VARCHAR2 DEFAULT NULL,
334 depen2 IN VARCHAR2 DEFAULT NULL,
335 depen3 IN VARCHAR2 DEFAULT NULL,
336 value IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
337
338
339
340 END qa_ss_lov_api;
[Home] [Help]