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