1 PACKAGE INV_UI_RCV_LOVS AS
2 /* $Header: INVRCVLS.pls 120.7.12010000.1 2008/07/24 01:44:56 appldev 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
278 PROCEDURE GET_PO_RELEASE_ITEM_LOV(x_po_release_num_lov OUT NOCOPY t_genref,
279 p_organization_id IN NUMBER,
280 p_po_header_id IN NUMBER,
281 p_mobile_form IN VARCHAR2,
282 p_po_release_num IN VARCHAR2,
283 p_item_id IN NUMBER);
284
285 PROCEDURE GET_ITEM_LOV_RECEIVING (
286 x_Items OUT NOCOPY t_genref,
287 p_Organization_Id IN NUMBER,
288 p_Concatenated_Segments IN VARCHAR2,
289 p_poHeaderID IN VARCHAR2,
290 p_poReleaseID IN VARCHAR2,
291 p_poLineID IN VARCHAR2,
292 p_shipmentHeaderID IN VARCHAR2,
293 p_oeOrderHeaderID IN VARCHAR2,
294 p_reqHeaderID IN VARCHAR2,
295 p_projectId IN VARCHAR2,
296 p_taskId IN VARCHAR2,
297 p_pjmorg IN VARCHAR2,
298 p_crossreftype IN VARCHAR2,
299 p_from_lpn_id IN VARCHAR2 default NULL
300 )
301 ;
302
303 PROCEDURE GET_ITEM_LOV_INVTXN (
304 x_Items OUT NOCOPY t_genref,
305 p_Organization_Id IN NUMBER default null ,
306 p_Concatenated_Segments IN VARCHAR2 default null )
307 ;
308
309 PROCEDURE GET_LPN_LOV_INSPECT
310 (x_lpn_lov OUT NOCOPY t_genref,
311 p_lpn IN VARCHAR2,
312 p_orgid IN NUMBER ,
313 p_projid IN NUMBER ,
314 p_taskid IN NUMBER )
315 ;
316
317 PROCEDURE GET_LPN_LOV_INVTXN
318 (x_lpn_lov OUT NOCOPY t_genref,
319 p_lpn IN VARCHAR2,
320 p_orgid IN NUMBER )
321 ;
322
323 PROCEDURE GET_LPN_LOV_PJM
324 (x_lpn_lov OUT NOCOPY t_genref,
325 p_lpn IN VARCHAR2,
326 p_orgid IN NUMBER )
327 ;
328
329 PROCEDURE GET_COUNTRY_LOV
330 (x_country_lov OUT NOCOPY t_genref,
331 p_country IN VARCHAR2 )
332 ;
333
334 FUNCTION get_conversion_rate_expense(p_from_uom_code varchar2,
335 p_organization_id NUMBER,
336 p_item_id NUMBER,
337 p_primary_uom_code varchar2)
338 RETURN VARCHAR2;
339
340 -- get_hr_hz_locations_lov procedure will return all HR and HZ Active Locations.
341 -- Added as part of eIB Build; Bug# 4348541
342 PROCEDURE get_hr_hz_locations_lov(
343 x_location_codes OUT NOCOPY t_genref,
344 p_location_code IN VARCHAR2);
345
346 -- Added for BUG 4309432
347 PROCEDURE GET_ACTRJTQTY_LOV
348 (x_actrjtqty_lov OUT NOCOPY t_genref,
349 p_deliver_type IN VARCHAR2);
350
351 --Added for Bug 4498173
352 PROCEDURE GET_INV_ITEM_LOV_RECEIVING
353 (
354 x_Items OUT NOCOPY t_genref,
355 p_Organization_Id IN NUMBER,
356 p_Concatenated_Segments IN VARCHAR2,
357 p_receiptNum IN VARCHAR2,
358 p_poHeaderID IN VARCHAR2,
359 p_poReleaseID IN VARCHAR2,
360 p_poLineID IN VARCHAR2,
361 p_shipmentHeaderID IN VARCHAR2,
362 p_oeOrderHeaderID IN VARCHAR2,
363 p_reqHeaderID IN VARCHAR2,
364 p_shipmentHeaderReceipt IN VARCHAR2
365 );
366
367 --Added for bug 5246626
368 PROCEDURE GET_RCV_SHP_FLEX_DETAILS
369 ( p_shipment_num IN VARCHAR2
370 , p_orgid IN NUMBER
371 , x_attribute1 OUT NOCOPY VARCHAR2
372 , x_attribute2 OUT NOCOPY VARCHAR2
373 , x_attribute3 OUT NOCOPY VARCHAR2
374 , x_attribute4 OUT NOCOPY VARCHAR2
375 , x_attribute5 OUT NOCOPY VARCHAR2
376 , x_attribute6 OUT NOCOPY VARCHAR2
377 , x_attribute7 OUT NOCOPY VARCHAR2
378 , x_attribute8 OUT NOCOPY VARCHAR2
379 , x_attribute9 OUT NOCOPY VARCHAR2
380 , x_attribute10 OUT NOCOPY VARCHAR2
381 , x_attribute11 OUT NOCOPY VARCHAR2
382 , x_attribute12 OUT NOCOPY VARCHAR2
383 , x_attribute13 OUT NOCOPY VARCHAR2
384 , x_attribute14 OUT NOCOPY VARCHAR2
385 , x_attribute15 OUT NOCOPY VARCHAR2
386 , x_val_attribute1 OUT NOCOPY VARCHAR2
387 , x_val_attribute2 OUT NOCOPY VARCHAR2
388 , x_val_attribute3 OUT NOCOPY VARCHAR2
389 , x_val_attribute4 OUT NOCOPY VARCHAR2
390 , x_val_attribute5 OUT NOCOPY VARCHAR2
391 , x_val_attribute6 OUT NOCOPY VARCHAR2
392 , x_val_attribute7 OUT NOCOPY VARCHAR2
393 , x_val_attribute8 OUT NOCOPY VARCHAR2
394 , x_val_attribute9 OUT NOCOPY VARCHAR2
395 , x_val_attribute10 OUT NOCOPY VARCHAR2
396 , x_val_attribute11 OUT NOCOPY VARCHAR2
397 , x_val_attribute12 OUT NOCOPY VARCHAR2
398 , x_val_attribute13 OUT NOCOPY VARCHAR2
399 , x_val_attribute14 OUT NOCOPY VARCHAR2
400 , x_val_attribute15 OUT NOCOPY VARCHAR2
401 , x_attribute_category OUT NOCOPY VARCHAR2
402 , x_concatenated_val OUT NOCOPY VARCHAR2
403 )
404 ;
405
406 END INV_UI_RCV_LOVS;