1 PACKAGE INV_UI_RCV_LOVS AUTHID CURRENT_USER AS
2 /* $Header: INVRCVLS.pls 120.8 2011/01/10 10:40:18 schiluve ship $ */
3
4 TYPE t_genref IS REF CURSOR;
5
6 -- Name: GET_PO_LOV
7 --
8 -- Input parameters:
9 -- p_Organization_Id which restricts LOV SQL to current org
10 -- p_po_number which restricts LOV SQL to the user input text
11 -- e.g. FG%
12 -- p_manual_po_num_type NUMERIC or ALPHANUMERIC
13 -- p_mobile_form which mobile form this LOV is for (RECEIPT or DELIVER)
14 -- SQL query will be different for these forms
15 --
16 -- Output parameters:
17 -- x_po_num_lov returns LOV rows as reference cursor
18 --
19 -- Functions: This API returns PO number for a given org
20 --
21
22 /* R12 MOAC
23 Removed parameter p_manual_po_num_type from procedures -
24 GET_PO_LOV
25 GET_RECEIPT_NUMBER_LOV
26 GET_RECEIPT_NUMBER_INSPECT_LOV
27 GET_DOC_LOV
28 */
29
30 PROCEDURE GET_PO_LOV(x_po_num_lov OUT NOCOPY t_genref,
31 p_organization_id IN NUMBER,
32 p_po_number IN VARCHAR2,
33 p_mobile_form IN VARCHAR2,
34 p_shipment_header_id IN VARCHAR2);
35
36
37 -- Name: GET_PO_LINE_NUM_LOV
38 --
39 -- Input parameters:
40 -- p_Organization_Id which restricts LOV SQL to current org
41 -- p_po_header_id which restricts LOV SQL to the PO
42 -- p_po_line_num which restricts the LOV to the user input text.
43 --
44 -- Output parameters:
45 -- x_po_line_num_lov returns LOV rows as reference cursor
46 --
47 -- Functions: This API returns PO Line numbers for a given PO
48 --
49 PROCEDURE GET_PO_LINE_NUM_LOV(x_po_line_num_lov OUT NOCOPY t_genref,
50 p_organization_id IN NUMBER,
51 p_po_header_id IN NUMBER,
52 p_mobile_form IN VARCHAR2,
53 p_po_line_num IN VARCHAR2);
54
55
56 -- Name: GET_PO_RELEASE_LOV
57 --
58 -- Input parameters:
59 -- p_Organization_Id which restricts LOV SQL to current org
60 -- p_po_header_id which restricts LOV SQL to the PO
61 -- p_po_release_num which restricts the LOV to the user input text.
62 --
63 -- Output parameters:
64 -- x_po_release_num_lov returns LOV rows as reference cursor
65 --
66 -- Functions: This API returns PO Release numbers for a given PO
67 --
68 PROCEDURE GET_PO_RELEASE_LOV(x_po_release_num_lov OUT NOCOPY t_genref,
69 p_organization_id IN NUMBER,
70 p_po_header_id IN NUMBER,
71 p_mobile_form IN VARCHAR2,
72 p_po_release_num IN VARCHAR2);
73
74
75
76
77 -- Name: GET_LOCATION_LOV
78 --
79 -- Input parameters:
80 -- p_Organization_Id which restricts LOV SQL to current org
81 -- p_location_code which restricts LOV SQL to the user input text
82 -- e.g. 1-1%
83 --
84 -- Output parameters:
85 -- x_location returns LOV rows as reference cursor
86 --
87 -- Functions: This API is to returns location for given org
88
89
90 PROCEDURE get_location_lov (x_location OUT NOCOPY t_genref,
91 p_organization_id IN NUMBER,
92 p_location_code IN VARCHAR2);
93
94
95
96 -- Name: get_freight_carrier_lov
97 --
98 -- Input parameters:
99 -- p_Organization_Id which restricts LOV SQL to current org
100 -- p_freight_carrier which restricts LOV SQL to the user input text
101 --
102 --
103 -- Output parameters:
104 -- x_freight_carrier returns LOV rows as reference cursor
105 --
106 -- Functions: This API returns freight carrier for given org
107
108
109 PROCEDURE get_freight_carrier_lov (x_freight_carrier OUT NOCOPY t_genref,
110 p_organization_id IN NUMBER,
111 p_freight_carrier IN VARCHAR2);
112
113
114
115 -- Name: GET_SHIPMENT_NUM_LOV
116 --
117 -- Input parameters:
118 -- p_Organization_Id which restricts LOV SQL to current org
119 -- p_shipment_num which restricts LOV SQL to the user input text
120 -- p_mobile_form which mobile form this LOV is for (RECEIPT or DELIVER)
121 -- SQL query will be different for these forms
122 --
123 -- Output parameters:
124 -- x_shipment_num_lov returns LOV rows as reference cursor
125 --
126 -- Functions: This API returns Shipment number for a given org
127 --
128
129
130 PROCEDURE GET_SHIPMENT_NUM_LOV(x_shipment_num_lov OUT NOCOPY t_genref,
131 p_organization_id IN NUMBER,
132 p_shipment_num IN VARCHAR2,
133 p_mobile_form IN VARCHAR2,
134 p_po_header_id IN VARCHAR2);
135
136
137 -- Name: GET_REQ_NUM_LOV
138 --
139 -- Input parameters:
140 -- p_Organization_Id which restricts LOV SQL to current org
141 -- p_requisition_num which restricts LOV SQL to the user input text
142 -- p_mobile_form which mobile form this LOV is for (RECEIPT or DELIVER)
143 -- SQL query will be different for these forms
144 --
145 -- Output parameters:
146 -- x_requisition_num_lov returns LOV rows as reference cursor
147 --
148 -- Functions: This API returns Shipment number for a given org
149 -- Also it returns an ASN numner for ASN receipt
150
151
152 PROCEDURE GET_REQ_NUM_LOV(x_requisition_num_lov OUT NOCOPY t_genref,
153 p_organization_id IN NUMBER,
154 p_requisition_num IN VARCHAR2,
155 p_mobile_form IN VARCHAR2
156 );
157
158 -- Almost same as GET_SHIPMENT_NUM_LOV
159 -- but for getting pack slip number
160
161 PROCEDURE GET_PACK_SLIP_NUM_LOV(x_pack_slip_num_lov OUT NOCOPY t_genref,
162 p_organization_id IN NUMBER,
163 p_pack_slip_num IN VARCHAR2,
164 p_po_header_id IN VARCHAR2);
165
166
167 PROCEDURE GET_CARRIER(x_getcarrierLOV OUT NOCOPY t_genref,
168 p_FromOrganization_Id IN NUMBER,
169 p_ToOrganization_Id IN NUMBER,
170 p_carrier IN VARCHAR2);
171
172
173 -- LOV for the possible receipt numbers that can be used.
174 PROCEDURE GET_RECEIPT_NUMBER_LOV(x_getRcptNumLOV OUT NOCOPY t_genref,
175 p_organization_id IN NUMBER,
176 p_receipt_number IN VARCHAR2);
177
178 -- LOV for the possible quality codes for mobile inspection form
179 PROCEDURE GET_QUALITY_CODES_LOV(
180 x_getQltyCodesLOV OUT NOCOPY t_genref
181 ,p_quality_code IN VARCHAR2);
182
183 -- LOV for the possible reason codes for mobile inspection form
184 PROCEDURE GET_REASON_CODES_LOV(
185 x_getReasonCodesLOV OUT NOCOPY t_genref
186 ,p_reason_code IN VARCHAR2);
187
188 -- LOV for the possible reason codes for mobile inspection form
189 -- Procedure overloaded for Transaction Reason Security build. 4505091, nsrivast
190 PROCEDURE GET_REASON_CODES_LOV(
191 x_getReasonCodesLOV OUT NOCOPY t_genref
192 ,p_reason_code IN VARCHAR2
193 ,p_txn_type_id IN VARCHAR2 );
194
195 -- LOV for the possible receipt numbers for inspection
196 PROCEDURE get_receipt_number_inspect_lov
197 (x_getRcptNumLOV OUT NOCOPY t_genref
198 , p_organization_id IN NUMBER
199 , p_receipt_number IN VARCHAR2);
200
201 -- LOV for RMA
202 PROCEDURE get_rma_lov
203 (x_getRMALOV OUT NOCOPY t_genref,
204 p_organization_id IN NUMBER,
205 p_rma_number IN VARCHAR,
206 p_mobile_form IN VARCHAR2);
207
208 --
209 -- Bug 2192815
210 -- Uom LOV for Expense Items
211 --
212
213 PROCEDURE get_uom_lov_expense(x_uoms OUT NOCOPY t_genref,
214 p_organization_id IN NUMBER,
215 p_item_id IN NUMBER,
216 p_uom_type IN NUMBER,
217 p_uom_code IN VARCHAR2,
218 p_primary_uom_code IN VARCHAR2);
219
220 /* Direct Shipping */
221 -- LOV for the Location Code
222 PROCEDURE get_locationcode_lov (
223 x_locationcode OUT NOCOPY t_genref
224 , p_location_code IN VARCHAR2);
225
226 -- LOV for the Location
227 PROCEDURE get_directship_location_lov (
228 x_location OUT NOCOPY t_genref
229 , p_organization_id IN NUMBER
230 , p_location_code IN VARCHAR2);
231
232 /* Direct Shipping */
233 -- Bug 2008025
234 -- Lov for Docs
235
236 -- Name: GET_DOC_LOV
237 --
238 -- Input parameters:
239 -- p_Organization_Id which restricts LOV SQL to current org
240 -- p_doc_number which restricts LOV SQL to the user input text
241 -- e.g. FG%
242 -- p_manual_po_num_type NUMERIC or ALPHANUMERIC
243 -- p_mobile_form which mobile form this LOV is for (RECEIPT or DELIVER)
244 -- SQL query will be different for these forms
245 --
246 -- Output parameters:
247 -- x_doc_num_lov returns LOV rows as reference cursor
248 --
249 -- Functions: This API returns PO number for a given org
250 --
251
252 PROCEDURE GET_DOC_LOV(x_doc_num_lov OUT NOCOPY t_genref,
253 p_organization_id IN NUMBER,
254 p_doc_number IN VARCHAR2,
255 p_mobile_form IN VARCHAR2,
256 p_shipment_header_id IN VARCHAR2,
257 p_inventory_item_id IN VARCHAR2,
258 p_item_description IN VARCHAR2,
259 p_doc_type IN VARCHAR2,
260 p_vendor_prod_num IN VARCHAR2);
261
262
263 PROCEDURE GET_PO_LINE_ITEM_NUM_LOV(x_po_line_num_lov OUT NOCOPY t_genref,
264 p_organization_id IN NUMBER,
265 p_po_header_id IN NUMBER,
266 p_mobile_form IN VARCHAR2,
267 p_po_line_num IN VARCHAR2,
268 p_inventory_item_id IN VARCHAR2);
269
270 PROCEDURE get_job_lov (x_job_lov OUT NOCOPY t_genref,
271 p_organization_id IN NUMBER,
272 p_po_header_id IN NUMBER,
273 p_po_line_id IN NUMBER,
274 p_item_id IN NUMBER,
275 p_Job IN VARCHAR2,
276 p_po_release_id IN NUMBER DEFAULT NULL, --Bug #3883926
277 p_shipment_header_id IN NUMBER DEFAULT NULL ); -- Added for bug 9360553
278
279
280 PROCEDURE GET_PO_RELEASE_ITEM_LOV(x_po_release_num_lov OUT NOCOPY t_genref,
281 p_organization_id IN NUMBER,
282 p_po_header_id IN NUMBER,
283 p_mobile_form IN VARCHAR2,
284 p_po_release_num IN VARCHAR2,
285 p_item_id IN NUMBER);
286
287 PROCEDURE GET_ITEM_LOV_RECEIVING (
288 x_Items OUT NOCOPY t_genref,
289 p_Organization_Id IN NUMBER,
290 p_Concatenated_Segments IN VARCHAR2,
291 p_poHeaderID IN VARCHAR2,
292 p_poReleaseID IN VARCHAR2,
293 p_poLineID IN VARCHAR2,
294 p_shipmentHeaderID IN VARCHAR2,
295 p_oeOrderHeaderID IN VARCHAR2,
296 p_reqHeaderID IN VARCHAR2,
297 p_projectId IN VARCHAR2,
298 p_taskId IN VARCHAR2,
299 p_pjmorg IN VARCHAR2,
300 p_crossreftype IN VARCHAR2,
301 p_from_lpn_id IN VARCHAR2 default NULL
302 )
303 ;
304
305 PROCEDURE GET_ITEM_LOV_INVTXN (
306 x_Items OUT NOCOPY t_genref,
307 p_Organization_Id IN NUMBER default null ,
308 p_Concatenated_Segments IN VARCHAR2 default null )
309 ;
310
311 PROCEDURE GET_LPN_LOV_INSPECT
312 (x_lpn_lov OUT NOCOPY t_genref,
313 p_lpn IN VARCHAR2,
314 p_orgid IN NUMBER ,
315 p_projid IN NUMBER ,
316 p_taskid IN NUMBER )
317 ;
318
319 PROCEDURE GET_LPN_LOV_INVTXN
320 (x_lpn_lov OUT NOCOPY t_genref,
321 p_lpn IN VARCHAR2,
322 p_orgid IN NUMBER )
323 ;
324
325 PROCEDURE GET_LPN_LOV_PJM
326 (x_lpn_lov OUT NOCOPY t_genref,
327 p_lpn IN VARCHAR2,
328 p_orgid IN NUMBER )
329 ;
330
331 PROCEDURE GET_COUNTRY_LOV
332 (x_country_lov OUT NOCOPY t_genref,
333 p_country IN VARCHAR2 )
334 ;
335
336 FUNCTION get_conversion_rate_expense(p_from_uom_code varchar2,
337 p_organization_id NUMBER,
338 p_item_id NUMBER,
339 p_primary_uom_code varchar2)
340 RETURN VARCHAR2;
341
342 -- get_hr_hz_locations_lov procedure will return all HR and HZ Active Locations.
343 -- Added as part of eIB Build; Bug# 4348541
344 PROCEDURE get_hr_hz_locations_lov(
345 x_location_codes OUT NOCOPY t_genref,
346 p_location_code IN VARCHAR2);
347
348 -- Added for BUG 4309432
349 PROCEDURE GET_ACTRJTQTY_LOV
350 (x_actrjtqty_lov OUT NOCOPY t_genref,
351 p_deliver_type IN VARCHAR2);
352
353 --Added for Bug 4498173
354 PROCEDURE GET_INV_ITEM_LOV_RECEIVING
355 (
356 x_Items OUT NOCOPY t_genref,
357 p_Organization_Id IN NUMBER,
358 p_Concatenated_Segments IN VARCHAR2,
359 p_receiptNum IN VARCHAR2,
360 p_poHeaderID IN VARCHAR2,
361 p_poReleaseID IN VARCHAR2,
362 p_poLineID IN VARCHAR2,
363 p_shipmentHeaderID IN VARCHAR2,
364 p_oeOrderHeaderID IN VARCHAR2,
365 p_reqHeaderID IN VARCHAR2,
366 p_shipmentHeaderReceipt IN VARCHAR2
367 );
368
369 --Added for bug 5246626
370 PROCEDURE GET_RCV_SHP_FLEX_DETAILS
371 ( p_shipment_num IN VARCHAR2
372 , p_orgid IN NUMBER
373 , x_attribute1 OUT NOCOPY VARCHAR2
374 , x_attribute2 OUT NOCOPY VARCHAR2
375 , x_attribute3 OUT NOCOPY VARCHAR2
376 , x_attribute4 OUT NOCOPY VARCHAR2
377 , x_attribute5 OUT NOCOPY VARCHAR2
378 , x_attribute6 OUT NOCOPY VARCHAR2
379 , x_attribute7 OUT NOCOPY VARCHAR2
380 , x_attribute8 OUT NOCOPY VARCHAR2
381 , x_attribute9 OUT NOCOPY VARCHAR2
382 , x_attribute10 OUT NOCOPY VARCHAR2
383 , x_attribute11 OUT NOCOPY VARCHAR2
384 , x_attribute12 OUT NOCOPY VARCHAR2
385 , x_attribute13 OUT NOCOPY VARCHAR2
386 , x_attribute14 OUT NOCOPY VARCHAR2
387 , x_attribute15 OUT NOCOPY VARCHAR2
388 , x_val_attribute1 OUT NOCOPY VARCHAR2
389 , x_val_attribute2 OUT NOCOPY VARCHAR2
390 , x_val_attribute3 OUT NOCOPY VARCHAR2
391 , x_val_attribute4 OUT NOCOPY VARCHAR2
392 , x_val_attribute5 OUT NOCOPY VARCHAR2
393 , x_val_attribute6 OUT NOCOPY VARCHAR2
394 , x_val_attribute7 OUT NOCOPY VARCHAR2
395 , x_val_attribute8 OUT NOCOPY VARCHAR2
396 , x_val_attribute9 OUT NOCOPY VARCHAR2
397 , x_val_attribute10 OUT NOCOPY VARCHAR2
398 , x_val_attribute11 OUT NOCOPY VARCHAR2
399 , x_val_attribute12 OUT NOCOPY VARCHAR2
400 , x_val_attribute13 OUT NOCOPY VARCHAR2
401 , x_val_attribute14 OUT NOCOPY VARCHAR2
402 , x_val_attribute15 OUT NOCOPY VARCHAR2
403 , x_attribute_category OUT NOCOPY VARCHAR2
404 , x_concatenated_val OUT NOCOPY VARCHAR2
405 )
406 ;
407
408 END INV_UI_RCV_LOVS;