1 PACKAGE inv_rcv_std_inspect_apis AUTHID CURRENT_USER AS
2 /* $Header: INVSTDIS.pls 120.1 2011/10/15 15:13:37 ssrikaku ship $ */
3
4 /*
5 ** -------------------------------------------------------------------------
6 ** Procedure: main_process
7 ** Description:
8 ** Output:
9 ** x_return_status
10 ** return status indicating success, error, unexpected error
11 ** x_msg_count
12 ** number of messages in message list
13 ** x_msg_data
14 ** if the number of messages in message list is 1, contains
15 ** message text
16 ** Input:
17 **
18 ** Returns:
19 ** none
20 ** --------------------------------------------------------------------------
21 */
22
23 -- Added for 12942776
24 type serset_t is table of varchar2(30);
25 FUNCTION list_serials RETURN serset_t PIPELINED;
26 -- 12942776
27
28
29 PROCEDURE main_process(
30 x_return_status OUT NOCOPY VARCHAR2
31 , x_msg_count OUT NOCOPY NUMBER
32 , x_msg_data OUT NOCOPY VARCHAR2
33 , p_inventory_item_id IN NUMBER
34 , p_organization_id IN NUMBER
35 , p_lpn_id IN NUMBER
36 , p_revision IN VARCHAR2
37 , p_lot_number IN VARCHAR2
38 , p_uom_code IN VARCHAR2
39 , p_quantity IN NUMBER
40 , p_inspection_code IN VARCHAR2
41 , p_quality_code IN VARCHAR2
42 , p_transaction_type IN VARCHAR2
43 , p_reason_id IN NUMBER
44 , p_serial_number IN VARCHAR2
45 , p_accept_lpn_id IN NUMBER
46 , p_reject_lpn_id IN NUMBER
47 , p_transaction_date IN DATE DEFAULT SYSDATE
48 , p_qa_collection_id IN NUMBER DEFAULT NULL
49 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
50 , p_comments IN VARCHAR2 DEFAULT NULL
51 , p_attribute_category IN VARCHAR2 DEFAULT NULL
52 , p_attribute1 IN VARCHAR2 DEFAULT NULL
53 , p_attribute2 IN VARCHAR2 DEFAULT NULL
54 , p_attribute3 IN VARCHAR2 DEFAULT NULL
55 , p_attribute4 IN VARCHAR2 DEFAULT NULL
56 , p_attribute5 IN VARCHAR2 DEFAULT NULL
57 , p_attribute6 IN VARCHAR2 DEFAULT NULL
58 , p_attribute7 IN VARCHAR2 DEFAULT NULL
59 , p_attribute8 IN VARCHAR2 DEFAULT NULL
60 , p_attribute9 IN VARCHAR2 DEFAULT NULL
61 , p_attribute10 IN VARCHAR2 DEFAULT NULL
62 , p_attribute11 IN VARCHAR2 DEFAULT NULL
63 , p_attribute12 IN VARCHAR2 DEFAULT NULL
64 , p_attribute13 IN VARCHAR2 DEFAULT NULL
65 , p_attribute14 IN VARCHAR2 DEFAULT NULL
66 , p_attribute15 IN VARCHAR2 DEFAULT NULL
67 , p_secondary_qty IN NUMBER DEFAULT NULL); --OPM Convergence
68
69 procedure range_serial_process(
70 x_return_status OUT NOCOPY VARCHAR2
71 , x_msg_count OUT NOCOPY NUMBER
72 , x_msg_data OUT NOCOPY VARCHAR2
73 , p_inventory_item_id IN NUMBER
74 , p_organization_id IN NUMBER
75 , p_lpn_id IN NUMBER
76 , p_revision IN VARCHAR2
77 , p_lot_number IN VARCHAR2
78 , p_inspection_code IN VARCHAR2
79 , p_quality_code IN VARCHAR2
80 , p_transaction_type IN VARCHAR2
81 , p_reason_id IN NUMBER
82 , p_from_serial_number IN VARCHAR2
83 , p_to_serial_number IN VARCHAR2
84 , p_accept_lpn_id IN NUMBER
85 , p_reject_lpn_id IN NUMBER
86 , p_transaction_date IN DATE DEFAULT SYSDATE
87 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
88 , p_comments IN VARCHAR2 DEFAULT NULL
89 , p_attribute_category IN VARCHAR2 DEFAULT NULL
90 , p_attribute1 IN VARCHAR2 DEFAULT NULL
91 , p_attribute2 IN VARCHAR2 DEFAULT NULL
92 , p_attribute3 IN VARCHAR2 DEFAULT NULL
93 , p_attribute4 IN VARCHAR2 DEFAULT NULL
94 , p_attribute5 IN VARCHAR2 DEFAULT NULL
95 , p_attribute6 IN VARCHAR2 DEFAULT NULL
96 , p_attribute7 IN VARCHAR2 DEFAULT NULL
97 , p_attribute8 IN VARCHAR2 DEFAULT NULL
98 , p_attribute9 IN VARCHAR2 DEFAULT NULL
99 , p_attribute10 IN VARCHAR2 DEFAULT NULL
100 , p_attribute11 IN VARCHAR2 DEFAULT NULL
101 , p_attribute12 IN VARCHAR2 DEFAULT NULL
102 , p_attribute13 IN VARCHAR2 DEFAULT NULL
103 , p_attribute14 IN VARCHAR2 DEFAULT NULL
104 , p_attribute15 IN VARCHAR2 DEFAULT NULL);
105
106 procedure main_process_po(
107 x_return_status OUT NOCOPY VARCHAR2
108 , x_msg_count OUT NOCOPY NUMBER
109 , x_msg_data OUT NOCOPY VARCHAR2
110 , p_inventory_item_id IN NUMBER
111 , p_organization_id IN NUMBER
112 , p_po_header_id IN NUMBER
113 , p_revision IN VARCHAR2
114 , p_uom_code IN VARCHAR2
115 , p_quantity IN NUMBER
116 , p_inspection_code IN VARCHAR2
117 , p_quality_code IN VARCHAR2
118 , p_transaction_type IN VARCHAR2
119 , p_reason_id IN NUMBER
120 , p_transaction_date IN DATE DEFAULT SYSDATE
121 , p_qa_collection_id IN NUMBER DEFAULT NULL
122 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
123 , p_comments IN VARCHAR2 DEFAULT NULL
124 , p_attribute_category IN VARCHAR2 DEFAULT NULL
125 , p_attribute1 IN VARCHAR2 DEFAULT NULL
126 , p_attribute2 IN VARCHAR2 DEFAULT NULL
127 , p_attribute3 IN VARCHAR2 DEFAULT NULL
128 , p_attribute4 IN VARCHAR2 DEFAULT NULL
129 , p_attribute5 IN VARCHAR2 DEFAULT NULL
130 , p_attribute6 IN VARCHAR2 DEFAULT NULL
131 , p_attribute7 IN VARCHAR2 DEFAULT NULL
132 , p_attribute8 IN VARCHAR2 DEFAULT NULL
133 , p_attribute9 IN VARCHAR2 DEFAULT NULL
134 , p_attribute10 IN VARCHAR2 DEFAULT NULL
135 , p_attribute11 IN VARCHAR2 DEFAULT NULL
136 , p_attribute12 IN VARCHAR2 DEFAULT NULL
137 , p_attribute13 IN VARCHAR2 DEFAULT NULL
138 , p_attribute14 IN VARCHAR2 DEFAULT NULL
139 , p_attribute15 IN VARCHAR2 DEFAULT NULL
140 , p_secondary_qty IN NUMBER DEFAULT NULL); --OPM Convergence
141
142 procedure main_process_intransit(
143 x_return_status OUT NOCOPY VARCHAR2
144 , x_msg_count OUT NOCOPY NUMBER
145 , x_msg_data OUT NOCOPY VARCHAR2
146 , p_inventory_item_id IN NUMBER
147 , p_organization_id IN NUMBER
148 , p_shipment_header_id IN NUMBER
149 , p_revision IN VARCHAR2
150 , p_uom_code IN VARCHAR2
151 , p_quantity IN NUMBER
152 , p_inspection_code IN VARCHAR2
153 , p_quality_code IN VARCHAR2
154 , p_transaction_type IN VARCHAR2
155 , p_reason_id IN NUMBER
156 , p_transaction_date IN DATE DEFAULT SYSDATE
157 , p_qa_collection_id IN NUMBER DEFAULT NULL
158 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
159 , p_comments IN VARCHAR2 DEFAULT NULL
160 , p_attribute_category IN VARCHAR2 DEFAULT NULL
161 , p_attribute1 IN VARCHAR2 DEFAULT NULL
162 , p_attribute2 IN VARCHAR2 DEFAULT NULL
163 , p_attribute3 IN VARCHAR2 DEFAULT NULL
164 , p_attribute4 IN VARCHAR2 DEFAULT NULL
165 , p_attribute5 IN VARCHAR2 DEFAULT NULL
166 , p_attribute6 IN VARCHAR2 DEFAULT NULL
167 , p_attribute7 IN VARCHAR2 DEFAULT NULL
168 , p_attribute8 IN VARCHAR2 DEFAULT NULL
169 , p_attribute9 IN VARCHAR2 DEFAULT NULL
170 , p_attribute10 IN VARCHAR2 DEFAULT NULL
171 , p_attribute11 IN VARCHAR2 DEFAULT NULL
172 , p_attribute12 IN VARCHAR2 DEFAULT NULL
173 , p_attribute13 IN VARCHAR2 DEFAULT NULL
174 , p_attribute14 IN VARCHAR2 DEFAULT NULL
175 , p_attribute15 IN VARCHAR2 DEFAULT NULL
176 , p_secondary_qty IN NUMBER DEFAULT NULL); --OPM Convergence
177
178 procedure main_process_rma(
179 x_return_status OUT NOCOPY VARCHAR2
180 , x_msg_count OUT NOCOPY NUMBER
181 , x_msg_data OUT NOCOPY VARCHAR2
182 , p_inventory_item_id IN NUMBER
183 , p_organization_id IN NUMBER
184 , p_oe_order_header_id IN NUMBER
185 , p_revision IN VARCHAR2
186 , p_uom_code IN VARCHAR2
187 , p_quantity IN NUMBER
188 , p_inspection_code IN VARCHAR2
189 , p_quality_code IN VARCHAR2
190 , p_transaction_type IN VARCHAR2
191 , p_reason_id IN NUMBER
192 , p_transaction_date IN DATE DEFAULT SYSDATE
193 , p_qa_collection_id IN NUMBER DEFAULT NULL
194 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
195 , p_comments IN VARCHAR2 DEFAULT NULL
196 , p_attribute_category IN VARCHAR2 DEFAULT NULL
197 , p_attribute1 IN VARCHAR2 DEFAULT NULL
198 , p_attribute2 IN VARCHAR2 DEFAULT NULL
199 , p_attribute3 IN VARCHAR2 DEFAULT NULL
200 , p_attribute4 IN VARCHAR2 DEFAULT NULL
201 , p_attribute5 IN VARCHAR2 DEFAULT NULL
202 , p_attribute6 IN VARCHAR2 DEFAULT NULL
203 , p_attribute7 IN VARCHAR2 DEFAULT NULL
204 , p_attribute8 IN VARCHAR2 DEFAULT NULL
205 , p_attribute9 IN VARCHAR2 DEFAULT NULL
206 , p_attribute10 IN VARCHAR2 DEFAULT NULL
207 , p_attribute11 IN VARCHAR2 DEFAULT NULL
208 , p_attribute12 IN VARCHAR2 DEFAULT NULL
209 , p_attribute13 IN VARCHAR2 DEFAULT NULL
210 , p_attribute14 IN VARCHAR2 DEFAULT NULL
211 , p_attribute15 IN VARCHAR2 DEFAULT NULL
212 , p_secondary_qty IN NUMBER DEFAULT NULL); --OPM Convergence
213
214 procedure main_process_receipt(
215 x_return_status OUT NOCOPY VARCHAR2
216 , x_msg_count OUT NOCOPY NUMBER
217 , x_msg_data OUT NOCOPY VARCHAR2
218 , p_inventory_item_id IN NUMBER
219 , p_organization_id IN NUMBER
220 , p_receipt_num IN VARCHAR2
221 , p_revision IN VARCHAR2
222 , p_uom_code IN VARCHAR2
223 , p_quantity IN NUMBER
224 , p_inspection_code IN VARCHAR2
225 , p_quality_code IN VARCHAR2
226 , p_transaction_type IN VARCHAR2
227 , p_reason_id IN NUMBER
228 , p_transaction_date IN DATE DEFAULT SYSDATE
229 , p_qa_collection_id IN NUMBER DEFAULT NULL
230 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
231 , p_comments IN VARCHAR2 DEFAULT NULL
232 , p_attribute_category IN VARCHAR2 DEFAULT NULL
233 , p_attribute1 IN VARCHAR2 DEFAULT NULL
234 , p_attribute2 IN VARCHAR2 DEFAULT NULL
235 , p_attribute3 IN VARCHAR2 DEFAULT NULL
236 , p_attribute4 IN VARCHAR2 DEFAULT NULL
237 , p_attribute5 IN VARCHAR2 DEFAULT NULL
238 , p_attribute6 IN VARCHAR2 DEFAULT NULL
239 , p_attribute7 IN VARCHAR2 DEFAULT NULL
240 , p_attribute8 IN VARCHAR2 DEFAULT NULL
241 , p_attribute9 IN VARCHAR2 DEFAULT NULL
242 , p_attribute10 IN VARCHAR2 DEFAULT NULL
243 , p_attribute11 IN VARCHAR2 DEFAULT NULL
244 , p_attribute12 IN VARCHAR2 DEFAULT NULL
245 , p_attribute13 IN VARCHAR2 DEFAULT NULL
246 , p_attribute14 IN VARCHAR2 DEFAULT NULL
247 , p_attribute15 IN VARCHAR2 DEFAULT NULL
248 , p_secondary_qty IN NUMBER DEFAULT NULL); --OPM Convergence
249
250 procedure insert_inspect_rec_rti (
251 x_return_status OUT NOCOPY VARCHAR2
252 , x_msg_count OUT NOCOPY NUMBER
253 , x_msg_data OUT NOCOPY VARCHAR2
254 , p_rcv_transaction_id IN NUMBER
255 , p_quantity IN NUMBER
256 , p_uom IN VARCHAR2
257 , p_inspection_code IN VARCHAR2
258 , p_quality_code IN VARCHAR2
259 , p_transaction_date IN DATE
260 , p_transaction_type IN VARCHAR2
261 , p_vendor_lot IN VARCHAR2
262 , p_reason_id IN NUMBER
263 , p_primary_qty IN NUMBER
264 , p_organization_id IN NUMBER
265 , p_comments IN VARCHAR2 DEFAULT NULL
266 , p_attribute_category IN VARCHAR2 DEFAULT NULL
267 , p_attribute1 IN VARCHAR2 DEFAULT NULL
268 , p_attribute2 IN VARCHAR2 DEFAULT NULL
269 , p_attribute3 IN VARCHAR2 DEFAULT NULL
270 , p_attribute4 IN VARCHAR2 DEFAULT NULL
271 , p_attribute5 IN VARCHAR2 DEFAULT NULL
272 , p_attribute6 IN VARCHAR2 DEFAULT NULL
273 , p_attribute7 IN VARCHAR2 DEFAULT NULL
274 , p_attribute8 IN VARCHAR2 DEFAULT NULL
275 , p_attribute9 IN VARCHAR2 DEFAULT NULL
276 , p_attribute10 IN VARCHAR2 DEFAULT NULL
277 , p_attribute11 IN VARCHAR2 DEFAULT NULL
278 , p_attribute12 IN VARCHAR2 DEFAULT NULL
279 , p_attribute13 IN VARCHAR2 DEFAULT NULL
280 , p_attribute14 IN VARCHAR2 DEFAULT NULL
281 , p_attribute15 IN VARCHAR2 DEFAULT NULL
282 , p_qa_collection_id IN NUMBER DEFAULT NULL
283 , p_lpn_id IN NUMBER DEFAULT NULL
284 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
285 , p_mmtt_temp_id IN NUMBER DEFAULT NULL
286 , p_sec_uom IN VARCHAR2 DEFAULT NULL --OPM Convergenc
287 , p_secondary_qty IN NUMBER DEFAULT NULL
288 ); --OPM Convergence
289
290 procedure rcv_manager_rpc_call(
291 x_return_status out NOCOPY varchar2
292 , x_return_code out NOCOPY number);
293
294 procedure launch_rcv_manager_rpc(
295 x_return_status out NOCOPY varchar2
296 , x_return_code out NOCOPY number);
297
301
298 procedure rcv_manager_conc_call;
299
300 procedure launch_rcv_manager_conc;
302 FUNCTION get_inspection_qty(
303 p_type IN VARCHAR2
304 ,p_lpn_id IN NUMBER := NULL
305 ,p_po_header_id IN NUMBER := NULL
306 ,p_po_release_id IN NUMBER := NULL
307 ,p_po_line_id IN NUMBER := NULL
308 ,p_shipment_header_id IN NUMBER := NULL
309 ,p_oe_order_header_id IN NUMBER := NULL
310 ,p_organization_id IN NUMBER
311 ,p_item_id IN NUMBER
312 ,p_uom_code IN VARCHAR2
313 ,x_inspection_qty OUT NOCOPY NUMBER
314 ,x_return_status OUT NOCOPY VARCHAR2
315 ,x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER;
316
317 FUNCTION get_inspection_qty_wrapper(
318 p_type IN VARCHAR2
319 ,p_id1 IN NUMBER := NULL
320 ,p_id2 IN NUMBER := NULL
321 ,p_id3 IN NUMBER := NULL
322 ,p_organization_id IN NUMBER
323 ,p_item_id IN NUMBER
324 ,p_uom_code IN VARCHAR2) RETURN NUMBER;
325
326
327 PROCEDURE obtain_receiving_information(
328 p_lpn_id IN NUMBER
329 , p_organization_id IN NUMBER
330 , p_inventory_item_id IN NUMBER
331 , x_po_id OUT NOCOPY VARCHAR2
332 , x_po_number OUT NOCOPY VARCHAR2
333 , x_po_return_status OUT NOCOPY VARCHAR2
334 , x_vendor_id OUT NOCOPY VARCHAR2
335 , x_vendor_name OUT NOCOPY VARCHAR2
336 , x_asl_status_id OUT NOCOPY VARCHAR2
337 , x_asl_status_dsp OUT NOCOPY VARCHAR2
338 , x_rma_id OUT NOCOPY VARCHAR2
339 , x_rma_number OUT NOCOPY VARCHAR2
340 , x_rma_return_status OUT NOCOPY VARCHAR2
341 , x_customer_id OUT NOCOPY VARCHAR2
342 , x_customer_number OUT NOCOPY VARCHAR2
343 , x_customer_name OUT NOCOPY VARCHAR2
344 , x_intshp_id OUT NOCOPY VARCHAR2
345 , x_intshp_number OUT NOCOPY VARCHAR2
346 , x_intshp_return_status OUT NOCOPY VARCHAR2
347 , x_receipt_number OUT NOCOPY VARCHAR2
348 , x_receipt_return_status OUT NOCOPY VARCHAR2
349 , x_msg_count OUT NOCOPY VARCHAR2
350 , x_msg_data OUT NOCOPY VARCHAR2);
351
352 FUNCTION is_revision_required (
353 p_source_type IN VARCHAR2
354 , p_source_id IN NUMBER
355 , p_item_id IN NUMBER
356 ) RETURN NUMBER;
357
358 end inv_rcv_std_inspect_apis;