1 PACKAGE INV_RCV_COMMON_APIS AS
2 /* $Header: INVRCVCS.pls 120.8.12010000.1 2008/07/24 01:44:49 appldev ship $*/
3
4 -- Added two new global variables to store the values for the po, WMS and inv
5 -- patch LEVEL.
6 -- Since we are hard prereqing PO ARU we do not need to check if PO.J is
7 -- installed. However, since all the code already is using it all we need
8 -- to do is assign it the value of g_inv_patch_level since if inv.J or
9 -- higher is installed, it will imply the PO.J functionality exists.
10 g_po_patch_level NUMBER := inv_control.Get_Current_Release_Level;
11 g_inv_patch_level NUMBER := inv_control.Get_Current_Release_Level;
12 g_wms_patch_level NUMBER := wms_control.Get_Current_Release_Level;
13
14 -- Added global constant to identify patchset J
15 g_patchset_j NUMBER := 110510;
16 g_patchset_j_po NUMBER := 110510;
17
18
19 /*
20 * FP-J Lot/Serial Support Enhancement
21 * Added lot_number with default NULL value. Used for matching lots
22 */
23 TYPE cascaded_trans_rec_type IS RECORD
24 (
25 customer_id NUMBER,
26 error_message VARCHAR2(255),
27 error_status VARCHAR2(1),
28 expected_receipt_date DATE,
29 from_organization_id NUMBER,
30 group_id NUMBER,
31 item_id NUMBER,
32 locator_id NUMBER,
33 oe_order_header_id NUMBER,
34 oe_order_line_id NUMBER,
35 parent_transaction_id NUMBER,
36 po_distribution_id NUMBER,
37 po_header_id NUMBER,
38 po_line_id NUMBER,
39 po_line_location_id NUMBER,
40 po_release_id NUMBER,
41 primary_quantity NUMBER,
42 primary_unit_of_measure VARCHAR2(25),
43 qty_rcv_exception_code VARCHAR2(25),
44 quantity NUMBER,
45 quantity_shipped NUMBER,
46 revision VARCHAR2(3),
47 ship_to_location_id NUMBER,
48 shipment_header_id NUMBER,
49 shipment_line_id NUMBER,
50 source_doc_quantity NUMBER,
51 source_doc_unit_of_measure VARCHAR2(25),
52 subinventory VARCHAR2(10),
53 tax_amount NUMBER,
54 to_organization_id NUMBER,
55 transaction_type VARCHAR2(25),
56 unit_of_measure VARCHAR2(25),
57 inspection_status_code VARCHAR2(25),
58 p_lpn_id NUMBER,
59 item_desc VARCHAR2(240),
60 project_id number default null,
61 task_id number default null,
62 lot_number mtl_lot_numbers.lot_number%TYPE DEFAULT NULL
63 );
64
65 TYPE cascaded_trans_tab_type IS TABLE OF cascaded_trans_rec_type
66 INDEX BY BINARY_INTEGER;
67
68 -- po_startup_value block record type
69 TYPE po_startup_value_tp IS RECORD
70 (inv_org_id NUMBER, --bug 5195963
71 org_name VARCHAR2(240),
72 org_location VARCHAR2(60),
73 sob_id NUMBER,
74 ussgl_value VARCHAR2(60),
75 period_name VARCHAR2(60),
76 gl_date DATE,
77 category_set_id NUMBER,
78 structure_id NUMBER,
79 user_id NUMBER,
80 logon_id NUMBER,
81 creation_date DATE,
82 update_date DATE,
83 inv_status VARCHAR2(1),
84 po_status VARCHAR2(1),
85 qa_status VARCHAR2(1),
86 wip_status VARCHAR2(1),
87 pa_status VARCHAR2(1),
88 oe_status VARCHAR2(1),
89 override_routing VARCHAR2(60),
90 transaction_mode VARCHAR2(60),
91 receipt_traveller VARCHAR2(60),
92 receipt_num_code VARCHAR2(60),
93 receipt_num_type VARCHAR2(60),
94 po_num_type VARCHAR2(60),
95 coa_id NUMBER,
96 allow_express VARCHAR2(1),
97 allow_cascade VARCHAR2(1),
98 org_locator_control NUMBER,
99 negative_inv_receipt_code NUMBER,
100 gl_set_of_bks_id VARCHAR2(60),
101 blind_receiving_flag VARCHAR2(1),
102 allow_unordered VARCHAR2(1),
103 display_inverse_rate VARCHAR2(60),
104 currency_code VARCHAR2(60),
105 project_reference_enabled NUMBER,
106 project_control_level NUMBER,
107 employee_id NUMBER,
108 wms_install_status VARCHAR2(1),
109 wms_purchased VARCHAR2(1),
110 effectivity_control NUMBER);
111
112 -- po_startup_value block
113 g_po_startup_value po_startup_value_tp;
114
115
116 -- rcv_global_var block record type
117 TYPE rcv_global_var_tp IS RECORD
118 (interface_group_id NUMBER,
119 transaction_header_id NUMBER,
120 receipt_num NUMBER,
121 unordered_mode VARCHAR2(30),
122 receiving_dsp_val VARCHAR2(80),
123 express_mode VARCHAR2(30));
124
125 g_rcv_global_var rcv_global_var_tp;
126
127 -- We use the following data structure to store new lot numbers created
128 -- at receipt time.
129 TYPE lot_status_rec IS RECORD
130 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
131 (lot_number VARCHAR2(80),
132 inventory_item_id NUMBER,
133 organization_id NUMBER
134 );
135 TYPE lot_status_rec_tb_tp IS TABLE OF lot_status_rec
136 INDEX BY BINARY_INTEGER;
137
138 g_lot_status_tb lot_status_rec_tb_tp;
139
140
141 -- We use the following data structure to store lpn_id used in this transaction cycle
142 TYPE lpn_id_tb_tp IS TABLE OF NUMBER
143 INDEX BY BINARY_INTEGER;
144
145 g_lpn_id_tb lpn_id_tb_tp;
146
147
148 TYPE trans_rec_type IS RECORD
149 (transaction_id NUMBER,
150 primary_quantity NUMBER,
151 secondary_quantity NUMBER --invconv kkillams
152 );
153
154 TYPE trans_rec_tb_tp IS TABLE OF trans_rec_type
155 INDEX BY BINARY_INTEGER;
156
157 --These constants are used by the break api in
158 --determining the order in which lots are split
159 g_order_lots_by_exp_date NUMBER := 1;
160 g_order_lots_by_creation_date NUMBER := 2;
161
162 g_order_lots_by NUMBER := g_order_lots_by_exp_date;--Default is by exp date
163
164 PROCEDURE init_form_values(p_org_id IN NUMBER,
165 x_inv_org_id OUT NOCOPY NUMBER, --bug 5195963
166 x_org_name OUT NOCOPY VARCHAR2,
167 x_org_location OUT NOCOPY VARCHAR2,
168 x_sob_id OUT NOCOPY NUMBER,
169 x_ussgl_value OUT NOCOPY VARCHAR2,
170 x_period_name OUT NOCOPY VARCHAR2,
171 x_gl_date OUT NOCOPY DATE,
172 x_category_set_id OUT NOCOPY NUMBER,
173 x_structure_id OUT NOCOPY NUMBER,
174 x_user_id OUT NOCOPY NUMBER,
175 x_logon_id OUT NOCOPY NUMBER,
176 x_creation_date OUT NOCOPY DATE,
177 x_update_date OUT NOCOPY DATE,
178 x_inv_status OUT NOCOPY VARCHAR2,
179 x_po_status OUT NOCOPY VARCHAR2,
180 x_qa_status OUT NOCOPY VARCHAR2,
181 x_wip_status OUT NOCOPY VARCHAR2,
182 x_pa_status OUT NOCOPY VARCHAR2,
183 x_oe_status OUT NOCOPY VARCHAR2,
184 x_override_routing OUT NOCOPY VARCHAR2,
185 x_transaction_mode OUT NOCOPY VARCHAR2,
186 x_receipt_traveller OUT NOCOPY VARCHAR2,
187 x_receipt_num_code OUT NOCOPY VARCHAR2,
188 x_receipt_num_type OUT NOCOPY VARCHAR2,
189 x_po_num_type OUT NOCOPY VARCHAR2,
190 x_coa_id OUT NOCOPY NUMBER,
191 x_allow_express OUT NOCOPY VARCHAR2,
192 x_allow_cascade OUT NOCOPY VARCHAR2,
193 x_org_locator_control OUT NOCOPY NUMBER,
194 x_negative_inv_receipt_code OUT NOCOPY NUMBER,
195 x_gl_set_of_bks_id OUT NOCOPY VARCHAR2,
196 x_blind_receiving_flag OUT NOCOPY VARCHAR2,
197 x_allow_unordered OUT NOCOPY VARCHAR2,
198 x_display_inverse_rate OUT NOCOPY VARCHAR2,
199 x_currency_code OUT NOCOPY VARCHAR2,
200 x_project_reference_enabled OUT NOCOPY NUMBER,
201 x_project_control_level OUT NOCOPY NUMBER,
202 x_effectivity_control OUT NOCOPY NUMBER,
203 x_employee_id OUT NOCOPY NUMBER,
204 x_wms_install_status OUT NOCOPY VARCHAR2,
205 x_wms_purchased OUT NOCOPY VARCHAR2,
206 x_message OUT NOCOPY VARCHAR2
207 );
208
209 -- a wrapper of the above to initialize global g_startup_value block
210
211 PROCEDURE init_startup_values(p_organization_id IN NUMBER);
212
213 -- Overloaded the procedure. This will be called from patchset J development.
214 PROCEDURE init_rcv_ui_startup_values(p_organization_id IN NUMBER,
215 x_org_id OUT NOCOPY NUMBER,
216 x_org_location OUT NOCOPY VARCHAR2,
217 x_org_locator_control OUT NOCOPY NUMBER,
218 x_manual_po_num_type OUT NOCOPY VARCHAR2,
219 x_wms_install_status OUT NOCOPY VARCHAR2,
220 x_wms_purchased OUT NOCOPY VARCHAR2,
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_msg_data OUT NOCOPY VARCHAR2,
223 x_inv_patch_level OUT NOCOPY NUMBER,
224 x_po_patch_level OUT NOCOPY NUMBER,
225 x_wms_patch_level OUT NOCOPY NUMBER);
226
227 PROCEDURE init_rcv_ui_startup_values(p_organization_id IN NUMBER,
228 x_org_id OUT NOCOPY NUMBER,
229 x_org_location OUT NOCOPY VARCHAR2,
230 x_org_locator_control OUT NOCOPY NUMBER,
231 x_manual_po_num_type OUT NOCOPY VARCHAR2,
232 x_wms_install_status OUT NOCOPY VARCHAR2,
233 x_wms_purchased OUT NOCOPY VARCHAR2,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_data OUT NOCOPY VARCHAR2);
236
237 -- break lot/serial record for transaction record
238
239 PROCEDURE break
240 (p_original_tid IN mtl_transaction_lots_temp.transaction_temp_id%TYPE,
241 p_new_transactions_tb IN trans_rec_tb_tp,
242 p_lot_control_code IN NUMBER,
243 p_serial_control_code IN NUMBER);
244
245 --PROCEDURE rcv_process_receive_txn;
246
247 PROCEDURE rcv_gen_receipt_num
248 (x_receipt_num OUT NOCOPY VARCHAR2,
249 p_organization_id IN NUMBER,
250 x_return_status OUT NOCOPY VARCHAR2,
251 x_msg_count OUT NOCOPY NUMBER,
252 x_msg_data OUT NOCOPY VARCHAR2);
253
254
255
256 PROCEDURE rcv_clear_global;
257
258
259
260 /*************************************************
261 * Name: get_po_routing_id
262 * This API returns routing id for a given PO header ID
263 * Routing ID is defined at PO line-location level (po_line_locations_all)
264 * We use the following rule to set headers routing ID
265 * If there is one line detail needs inspection the entire PO needs inspection
266 * elsif there is one line detail needs direct receiving the entire PO direct
267 * else (all line detail are standard) the entire PO is standard
268 * rounting lookups: 1. standard 2. Inspect 3. Direct
269
270 ******************************************************/
271
272
273 PROCEDURE get_po_routing_id (x_po_routing_id OUT NOCOPY NUMBER,
274 x_is_expense OUT NOCOPY VARCHAR2,
275 p_po_header_id IN NUMBER,
276 p_po_release_id IN NUMBER,
277 p_po_line_id IN NUMBER,
278 p_item_id IN NUMBER,
279 p_item_desc IN VARCHAR2 DEFAULT NULL);
280
281
282 /*************************************************
283 * Name: get_routing_id
284 * This API returns routing id for a given PO header ID or shipment header
285 * id or for the rma
286 * It also validates the lpn_context and the routing id
287 * The LPN Context can only be pregenerated and resides in inventory
288 * for a direct receipt and for others it can be in receiving and pregenerated.
289 ******************************************************/
290 PROCEDURE get_routing_id (x_routing_id OUT NOCOPY NUMBER,
291 x_return_status OUT NOCOPY VARCHAR2,
292 x_msg_count OUT NOCOPY NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2,
294 x_is_expense OUT NOCOPY VARCHAR2,
295 p_po_header_id IN NUMBER,
296 p_po_release_id IN NUMBER,
297 p_po_line_id IN NUMBER,
298 p_shipment_header_id IN NUMBER,
299 p_oe_order_header_id IN NUMBER,
300 p_item_id IN NUMBER,
301 p_organization_id IN NUMBER,
302 p_vendor_id IN NUMBER,
303 p_lpn_id IN NUMBER DEFAULT NULL,
304 p_item_desc IN VARCHAR2 DEFAULT NULL,
305 p_from_lpn_id IN NUMBER DEFAULT NULL,
306 p_project_id IN NUMBER DEFAULT NULL,
307 p_task_id IN NUMBER DEFAULT NULL);
308
309
310
311
312
313 /*************************************************
314 * Name: get_asn_routing_id
315 * This API returns routing id for a given shipment_header_ID,
316 * lpn_id, po_header_id combination.
317 * PO_header_id, po_line_id and item_id are queried based on the combination,
318 * and then passed to get_po_routing_id.
319 * If any of the lines has a direct routing, this API will return direct.
320 *******************************************************/
321
322 PROCEDURE get_asn_routing_id
323 (x_asn_routing_id OUT NOCOPY NUMBER,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2,
327 p_shipment_header_id IN NUMBER,
328 p_lpn_id IN NUMBER,
329 p_po_header_id IN NUMBER
330 );
331
332
333
334
335 /*********************************************
336 * This api first checks if lpn exists or not by calling validate_LPN api.
337 * If yes, it simply returns the lpnID,
338 * If not, it calls create_LPN api and return the lpnID
339 ********************************************************/
340
341 PROCEDURE create_lpn(p_organization_id IN NUMBER,
342 p_lpn IN VARCHAR2,
343 p_lpn_ID OUT NOCOPY NUMBER,
344 x_return_status OUT NOCOPY VARCHAR2,
345 x_msg_data OUT NOCOPY VARCHAR2);
346
347
348
349 -- This api creates a record in the mtl_transaction_lots_temp
350 -- It checks if the p_transaction_temp_id is null, if it is, then it
351 -- generates a new id and returns that.
352 PROCEDURE insert_lot(p_transaction_temp_id IN OUT NOCOPY NUMBER,
353 p_created_by IN NUMBER,
354 p_transaction_qty IN NUMBER,
355 p_primary_qty IN NUMBER,
356 p_lot_number IN VARCHAR2,
357 p_expiration_date IN DATE,
358 p_status_id IN NUMBER := NULL,
359 x_serial_transaction_temp_id OUT NOCOPY NUMBER,
360 x_return_status OUT NOCOPY VARCHAR2,
361 x_msg_data OUT NOCOPY VARCHAR2,
362 p_secondary_quantity IN NUMBER DEFAULT null--OPM Convergence
363 );
364
365 -- This api creates a record in the mtl_transaction_serial_temp
366 -- It checks if the p_transaction_temp_id is null, if it is, then it
367 -- generates a new id and returns that.
368 PROCEDURE insert_serial(p_serial_transaction_temp_id IN OUT NOCOPY NUMBER,
369 p_org_id IN NUMBER,
370 p_item_id IN NUMBER,
371 p_rev IN VARCHAR2,
372 p_lot IN VARCHAR2,
373 p_txn_src_id IN NUMBER,
374 p_txn_action_id IN NUMBER,
375 p_created_by IN NUMBER,
376 p_from_serial IN VARCHAR2,
377 p_to_serial IN VARCHAR2,
381 --BUG#3062591
378 p_status_id IN NUMBER := NULL,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_data OUT NOCOPY VARCHAR2);
382 --This api used to return the default UOM value from purchaseorderlines
383 --whenjust item is entered in the Mobile Receipt page.
384 --UOM is defaulted in the following way.
385 --if a single line for the item exist, api will return the UoM based on PO line.
386 --if multiple lines for the same item exist with same UoM, api will return
387 --this UoM.
388 --multiple lines for the same item exist with different UoM, api will not
389 --return any UOM and just '@@@'
390 PROCEDURE get_uom_code(x_return_status OUT NOCOPY VARCHAR2,
391 x_uom_code OUT NOCOPY VARCHAR2,
392 p_po_header_id IN NUMBER,
393 p_item_id IN NUMBER,
394 p_organization_id IN NUMBER,
395 p_line_no IN NUMBER DEFAULT NULL, --BUG 4500676
396 p_item_desc IN VARCHAR2 DEFAULT NULL --BUG 4500676
397 );
398
399 --Bug #3285227
400 -- This api is used to return the possible value that can be used for
401 -- subinventory when the item and PO/Shipment Number/RMA are entered.
402 -- For RMA it always returns null for subinventory.
403 -- Overloaded this procedure by adding two new OUT parameters
404 -- -> x_lpn_context - Stores context of the LPN passed
405 -- -> x_default_source - Stores the defaulting source
406 -- This version is called from RcptGenFListener.java if
407 -- WMS and PO patch levels are J or higher. For all other cases,
408 -- the older implementation is called.
409 PROCEDURE get_sub_code(
410 x_return_status OUT NOCOPY VARCHAR2,
411 x_msg_count OUT NOCOPY NUMBER,
412 x_msg_data OUT NOCOPY VARCHAR2,
413 x_sub_code OUT NOCOPY VARCHAR2,
414 x_locator_segs OUT NOCOPY VARCHAR2,
415 x_locator_id OUT NOCOPY NUMBER,
416 x_lpn_context OUT NOCOPY NUMBER,
417 x_default_source OUT NOCOPY VARCHAR2,
418 p_po_header_id IN NUMBER,
419 p_po_release_id IN NUMBER,
420 p_po_line_id IN NUMBER,
421 p_shipment_header_id IN NUMBER,
422 p_oe_order_header_id IN NUMBER,
423 p_item_id IN NUMBER,
424 p_organization_id IN NUMBER,
425 p_lpn_id IN NUMBER DEFAULT NULL,
426 p_project_id IN NUMBER DEFAULT NULL,
427 p_task_id IN NUMBER DEFAULT NULL);
428
429 -- This api is used to return the possible value that can be used for
430 -- subinventory when the item and PO/Shipment Number/RMA are entered.
431 PROCEDURE get_sub_code(
432 x_return_status OUT NOCOPY VARCHAR2,
433 x_msg_count OUT NOCOPY NUMBER,
434 x_msg_data OUT NOCOPY VARCHAR2,
435 x_sub_code OUT NOCOPY VARCHAR2,
436 x_locator_segs OUT NOCOPY VARCHAR2,
437 x_locator_id OUT NOCOPY NUMBER,
438 p_po_header_id IN NUMBER,
439 p_po_release_id IN NUMBER,
440 p_po_line_id IN NUMBER,
441 p_shipment_header_id IN NUMBER,
442 p_oe_order_header_id IN NUMBER,
443 p_item_id IN NUMBER,
444 p_organization_id IN NUMBER,
445 p_lpn_id IN NUMBER DEFAULT NULL,
446 p_project_id IN NUMBER DEFAULT NULL,
447 p_task_id IN NUMBER DEFAULT NULL);
448
449 -- This api calls inventory api to insert a range serial
450
451 --Bug 3890706 - Added the procedure to default the location
452 -- if line number or item is entered during the receipt
453
454 PROCEDURE get_location_code
455 (
456 x_return_status OUT NOCOPY VARCHAR2,
457 x_location_code OUT NOCOPY VARCHAR2,
458 p_po_header_id IN NUMBER,
459 p_item_id IN NUMBER,
460 p_po_line_id IN NUMBER,
461 p_po_release_id IN NUMBER,
462 p_organization_id IN NUMBER,
463 p_shipment_header_id IN NUMBER DEFAULT NULL, --BUG 5124472 (FP of BUG 5117987)
464 p_from_lpn_id IN NUMBER DEFAULT NULL); --BUG 5124472 (FP of BUG 5117987)
465
466 --End of fix for Bug 3890706
467
468 --Bug 4003683 - Added the procedure to default the revision for the item.
469
470 PROCEDURE GET_REVISION_CODE(
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_revision_code OUT NOCOPY VARCHAR2,
473 p_document_type IN VARCHAR2 DEFAULT NULL,
474 p_po_header_id IN NUMBER DEFAULT NULL,
475 p_po_line_id IN NUMBER DEFAULT NULL,
476 p_po_release_id IN NUMBER DEFAULT NULL,
477 p_req_header_id IN NUMBER DEFAULT NULL,
478 p_shipment_header_id IN NUMBER DEFAULT NULL,
479 p_item_id IN NUMBER DEFAULT NULL,
480 p_organization_id IN NUMBER,
481 p_oe_order_header_id IN NUMBER DEFAULT NULL -- Bug #:5768262 Added parameter p_oe_order_header_id to default the revision of item for RMA
482 ) ;
483
484 --End of fix for Bug 4003683
485
486 PROCEDURE insert_range_serial
487 (p_api_version IN NUMBER,
491 p_inventory_item_id IN NUMBER,
488 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
489 p_commit IN VARCHAR2 := FND_API.G_FALSE,
490 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
492 p_organization_id IN NUMBER,
493 p_from_serial_number IN VARCHAR2,
494 p_to_serial_number IN VARCHAR2,
495 p_revision IN VARCHAR2,
496 p_lot_number IN VARCHAR2,
497 p_primary_lot_quantity IN NUMBER,
498 p_transaction_action_id IN NUMBER,
499 p_current_status IN NUMBER,
500 p_serial_status_id IN NUMBER,
501 p_update_serial_status IN VARCHAR2,
502 p_inspection_required IN NUMBER DEFAULT NULL,
503 p_hdr_id IN NUMBER,
504 p_from_lpn_id IN NUMBER,
505 p_to_lpn_id IN NUMBER,
506 p_primary_uom_code IN VARCHAR2,
507 p_call_pack_unpack IN VARCHAR2,
508 x_return_status OUT NOCOPY VARCHAR2,
509 x_msg_count OUT NOCOPY NUMBER,
510 x_msg_data OUT NOCOPY VARCHAR2,
511 p_subinventory IN VARCHAR2 DEFAULT NULL,
512 p_locator_id IN NUMBER DEFAULT NULL);
513
514
515 -- This api updates the current_status for a range of serials
516
517 PROCEDURE update_serial_status
518 (p_api_version IN NUMBER,
519 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
520 p_commit IN VARCHAR2 := FND_API.G_FALSE,
521 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
522 p_inventory_item_id IN NUMBER,
523 p_organization_id IN NUMBER,
524 p_from_serial_number IN VARCHAR2,
525 p_to_serial_number IN VARCHAR2,
526 p_current_status IN NUMBER,
527 p_serial_status_id IN NUMBER,
528 p_update_serial_status IN VARCHAR2,
529 p_lot_number IN VARCHAR2,
530 p_primary_lot_quantity IN NUMBER,
531 p_inspection_required IN NUMBER,
532 p_hdr_id IN NUMBER,
533 p_from_lpn_id IN NUMBER,
534 p_to_lpn_id IN NUMBER,
535 p_revision IN VARCHAR2,
536 p_primary_uom_code IN VARCHAR2,
537 p_call_pack_unpack IN VARCHAR2 := 'FALSE',
538 x_return_status OUT NOCOPY VARCHAR2,
539 x_msg_count OUT NOCOPY NUMBER,
540 x_msg_data OUT NOCOPY VARCHAR2,
541 p_subinventory IN VARCHAR2 DEFAULT NULL,
542 p_locator_id IN NUMBER DEFAULT NULL,
543 p_txn_src_id IN VARCHAR2 DEFAULT NULL);
544
545
546 -- This is a wrapper to call inventory INV_LOT_API_PUB.insertLot
547 -- it stores the inserted lot info in a global variable for
548 -- transaction exception rollback
549
550 PROCEDURE insert_dynamic_lot
551 (p_api_version IN NUMBER,
552 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
553 p_commit IN VARCHAR2 := FND_API.G_FALSE,
554 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
555 p_inventory_item_id IN NUMBER,
556 p_organization_id IN NUMBER,
557 p_lot_number IN VARCHAR2,
558 p_expiration_date IN OUT NOCOPY DATE,
559 p_transaction_temp_id IN NUMBER DEFAULT NULL,
560 p_transaction_action_id IN NUMBER DEFAULT NULL,
561 p_transfer_organization_id IN NUMBER DEFAULT NULL,
562 p_status_id IN NUMBER,
563 p_update_status IN VARCHAR2 := 'FALSE',
564 x_object_id OUT NOCOPY NUMBER,
565 x_return_status OUT NOCOPY VARCHAR2,
566 x_msg_count OUT NOCOPY NUMBER,
567 x_msg_data OUT NOCOPY VARCHAR2);
568
569 PROCEDURE process_lot
570 (p_api_version IN NUMBER,
571 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
572 p_commit IN VARCHAR2 := FND_API.G_FALSE,
573 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
574 p_inventory_item_id IN NUMBER,
575 p_organization_id IN NUMBER,
576 p_lot_number IN VARCHAR2,
577 p_expiration_date IN OUT NOCOPY DATE,
578 p_transaction_temp_id IN NUMBER DEFAULT NULL,
579 p_transaction_action_id IN NUMBER DEFAULT NULL,
580 p_transfer_organization_id IN NUMBER DEFAULT NULL,
581 p_status_id IN NUMBER,
582 p_update_status IN VARCHAR2 := 'FALSE',
583 p_is_new_lot IN VARCHAR2 := 'TRUE',
584 p_call_pack_unpack IN VARCHAR2 := 'FALSE',
585 p_from_lpn_id IN NUMBER,
586 p_to_lpn_id IN NUMBER,
587 p_revision IN VARCHAR2,
588 p_lot_primary_qty IN NUMBER,
589 p_primary_uom_code IN VARCHAR2,
590 p_transaction_uom_code IN VARCHAR2 DEFAULT NULL,
591 x_object_id OUT NOCOPY NUMBER,
592 x_return_status OUT NOCOPY VARCHAR2,
593 x_msg_count OUT NOCOPY NUMBER,
594 x_msg_data OUT NOCOPY VARCHAR2,
595 p_subinventory IN VARCHAR2 DEFAULT NULL,
596 p_locator_id IN NUMBER DEFAULT NULL,
597 p_lot_secondary_qty IN NUMBER DEFAULT NULL, --OPM Convergence
598 p_secondary_uom_code IN VARCHAR2 DEFAULT null--OPM Convergence
599 );
600
601
602 PROCEDURE gen_txn_group_id;
603
604 PROCEDURE validate_trx_date(p_trx_date IN DATE,
605 p_organization_id IN NUMBER,
606 p_sob_id IN NUMBER,
607 x_return_status OUT NOCOPY VARCHAR2,
608 x_error_code OUT NOCOPY VARCHAR2);
609
610 PROCEDURE get_req_shipment_header_id
614 x_msg_data OUT NOCOPY VARCHAR2,
611 (x_shipment_header_id OUT NOCOPY NUMBER,
612 x_return_status OUT NOCOPY VARCHAR2,
613 x_msg_count OUT NOCOPY NUMBER,
615 p_organization_id IN NUMBER,
616 p_requiition_header_id IN NUMBER,
617 p_item_id IN NUMBER,
618 p_rcv_txn_type IN VARCHAR2,
619 p_lpn_id IN NUMBER DEFAULT NULL);
620
621 -- Bug 2086271
622 PROCEDURE get_req_shipment_header_id
623 (x_shipment_header_id OUT NOCOPY NUMBER,
624 x_from_org_id OUT NOCOPY NUMBER,
625 x_return_status OUT NOCOPY VARCHAR2,
626 x_msg_count OUT NOCOPY NUMBER,
627 x_msg_data OUT NOCOPY VARCHAR2,
628 p_organization_id IN NUMBER,
629 p_requiition_header_id IN NUMBER,
630 p_item_id IN NUMBER,
631 p_rcv_txn_type IN VARCHAR2,
632 p_lpn_id IN NUMBER DEFAULT NULL);
633
634
635 PROCEDURE DO_CHECK(p_organization_id IN NUMBER,
636 p_inventory_item_id IN NUMBER,
637 p_transaction_type_id IN NUMBER,
638 p_primary_quantity IN NUMBER,
639 x_return_status OUT NOCOPY VARCHAR2,
640 x_msg_data OUT NOCOPY VARCHAR2,
641 x_msg_count OUT NOCOPY NUMBER);
642
643 PROCEDURE insert_mtlt
644 (p_mtlt_rec mtl_transaction_lots_temp%ROWTYPE);
645
646 PROCEDURE insert_msnt
647 (p_msnt_rec mtl_serial_numbers_temp%ROWTYPE);
648
649 PROCEDURE GET_SERIAL_CTRL
650 (x_return_status OUT NOCOPY VARCHAR2,
651 x_serial_control OUT NOCOPY NUMBER,
652 p_from_org_id IN NUMBER,
653 p_item_id IN NUMBER
654 );
655
656 PROCEDURE GET_SERIAL_CTRL
657 (x_return_status OUT NOCOPY VARCHAR2,
658 x_serial_control OUT NOCOPY NUMBER,
659 p_to_org_id IN NUMBER,
660 p_ship_head_id IN NUMBER,
661 p_requisition_id IN NUMBER,
662 p_item_id IN NUMBER);
663
664 -- WMS+PJM Integration
665 -- returns project and task fields
666 PROCEDURE GET_DOCUMENT_PROJECT_TASK
667 (x_return_status OUT NOCOPY VARCHAR2,
668 x_project_tasks_count OUT NOCOPY NUMBER,
669 x_distributions_count OUT NOCOPY NUMBER,
670 p_document_type IN VARCHAR2,
671 p_po_header_id IN NUMBER,
672 p_po_line_id IN NUMBER,
673 p_oe_header_id IN NUMBER,
674 p_req_header_id IN NUMBER,
675 p_shipment_header_id IN NUMBER,
676 p_item_id IN NUMBER DEFAULT NULL,
677 p_item_rev IN VARCHAR2 DEFAULT NULL
678 );
679
680 -- MANEESH - BEGIN CHANGES - FOR CROSS REFERENCE ITEM CREATION
681
682 PROCEDURE create_cross_reference(p_api_version IN NUMBER,
683 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
684 p_commit IN VARCHAR2 := fnd_api.g_false,
685 p_organization_id IN NUMBER,
686 p_inventory_item_id IN NUMBER,
687 p_cross_reference IN VARCHAR2,
688 p_cross_reference_type IN VARCHAR2,
689 x_return_status OUT NOCOPY VARCHAR2,
690 x_msg_count OUT NOCOPY NUMBER,
691 x_msg_data OUT NOCOPY VARCHAR2);
692
693 -- MANEESH - END CHANGES - FOR CROSS REFERENCE ITEM CREATION
694
695 /* bug#2156143. Added procedures to get lot control of an item
696 at source org, for transactions INTRANSIT SHIPMENT and
697 INTERNAL REQUISITION. */
698
699 PROCEDURE GET_LOT_CTRL
700 (x_return_status OUT NOCOPY VARCHAR2,
701 x_msg_count OUT NOCOPY NUMBER,
702 x_msg_data OUT NOCOPY VARCHAR2,
703 x_lot_control OUT NOCOPY NUMBER,
704 p_from_org_id IN NUMBER,
705 p_item_id IN NUMBER
706 );
707
708 PROCEDURE GET_LOT_CTRL
709 (x_return_status OUT NOCOPY VARCHAR2,
710 x_msg_count OUT NOCOPY NUMBER,
711 x_msg_data OUT NOCOPY VARCHAR2,
712 x_lot_control OUT NOCOPY NUMBER,
713 p_to_org_id IN NUMBER,
714 p_ship_head_id IN NUMBER,
715 p_requisition_id IN NUMBER,
716 p_item_id IN NUMBER
717 );
718
719
720 PROCEDURE get_default_task(
721 x_return_status OUT NOCOPY VARCHAR2,
722 x_task_number OUT NOCOPY VARCHAR2,
723 p_document_type IN VARCHAR2,
724 p_po_header_id IN NUMBER DEFAULT NULL,
725 p_po_line_id IN NUMBER DEFAULT NULL,
726 p_oe_header_id IN NUMBER DEFAULT NULL,
727 p_req_header_id IN NUMBER DEFAULT NULL,
728 p_shipment_header_id IN NUMBER DEFAULT NULL,
729 p_item_id IN NUMBER DEFAULT NULL,
730 p_item_rev IN VARCHAR2 DEFAULT NULL,
731 p_project_id IN NUMBER DEFAULT NULL
732 );
733
734 PROCEDURE check_lot_serial_codes(
735 p_lpn_id IN NUMBER,
736 p_req_header_id IN NUMBER,
737 p_shipment_header_id IN NUMBER,
738 x_lot_ser_flag OUT NOCOPY VARCHAR2,
739 x_return_status OUT NOCOPY VARCHAR2,
740 x_msg_count OUT NOCOPY NUMBER,
741 x_msg_data OUT NOCOPY VARCHAR2
745 /**
742 );
743
744
746 * This procedure checks for the following
747 * 1. Whether the given LPN or its child LPN has contents. If
748 * either the given
749 * LPN or its child LPNs do not have any contents then
750 * through error.
751 * 2. Check If the LPN is already processed, and there is a
752 * RTI record exists
753 * for the LPN.
754
755 * @param p_lpn_id
756 * @param x_return_status
757 * @param x_msg_count
758 * @param x_msg_data
759 **/
760
761 PROCEDURE VALIDATE_NESTED_LPN(
762 p_lpn_id IN NUMBER,
763 x_lpn_flag OUT NOCOPY VARCHAR2,
764 x_return_status OUT NOCOPY VARCHAR2,
765 x_msg_count OUT NOCOPY NUMBER,
766 x_msg_data OUT NOCOPY VARCHAR2
767 );
768 --
769
770
771 -- This proecedure will return subinventory code and locator id
772 -- for given receiving LPN.
773
774 /**
775 * This procedure takes in the LPN and fetches the LPN context,
776 * subinventory code and locator id.
777 * If the LPN resides in receiving, it also fetches the subinventory
778 * and locator for that LPN
779 **/
780 PROCEDURE get_rcv_sub_loc(
781 x_return_status OUT NOCOPY VARCHAR2
782 , x_msg_count OUT NOCOPY NUMBER
783 , x_msg_data OUT NOCOPY VARCHAR2
784 , x_lpn_context OUT NOCOPY NUMBER
785 , x_locator_segs OUT NOCOPY VARCHAR2
786 , x_location_id OUT NOCOPY NUMBER
787 , x_location_code OUT NOCOPY VARCHAR2
788 , x_sub_code OUT NOCOPY VARCHAR2
789 , x_locator_id OUT NOCOPY NUMBER
790 , p_lpn_id IN NUMBER
791 , p_organization_id IN NUMBER
792 );
793
794 PROCEDURE validate_from_lpn(
795 p_lpn_id IN NUMBER
796 , p_req_id IN VARCHAR2 DEFAULT NULL
797 , x_lpn_flag OUT NOCOPY VARCHAR2
798 , x_count_of_lpns OUT NOCOPY NUMBER
799 , x_return_status OUT NOCOPY VARCHAR2
800 , x_msg_count OUT NOCOPY NUMBER
801 , x_msg_data OUT NOCOPY VARCHAR2
802 --BUG 3402623: Add 2 more parameters for SHIPMENTEXP
803 , p_shipment_num IN VARCHAR2 DEFAULT null
804 , p_org_id IN NUMBER DEFAULT null
805 );
806
807 PROCEDURE clear_lot_rec; -- Bug # 3156689
808
809 -- Bug 4087032 Need to write a wrapper on LENGTH function as
810 -- it creates compiltaion issues in 8i env.
811 FUNCTION get_serial_length(p_from_ser IN VARCHAR2)
812 return NUMBER;
813
814 --<R12 MOAC START>
815 /* Function get_operating_unit_id returns the org_id. */
816 FUNCTION get_operating_unit_id ( p_receipt_source_code IN VARCHAR2,
817 p_po_header_id IN NUMBER,
818 p_req_line_id IN NUMBER,
819 p_oe_order_header_id IN NUMBER
820 )
821 RETURN NUMBER;
822 --<R12 MOAC END>
823
824 /** Start of fix for bug 5065079 (FP of bug 4651362)
825 * Following procedure is added to count the number of open shipments for
826 * an internal requisition.
827 **/
828 PROCEDURE count_req_open_shipments
829 (p_organization_id IN NUMBER,
830 p_requisition_header_id IN NUMBER,
831 x_return_status OUT NOCOPY VARCHAR2 ,
832 x_msg_count OUT NOCOPY NUMBER ,
833 x_msg_data OUT NOCOPY VARCHAR2,
834 x_open_shipments OUT NOCOPY NUMBER
835 );
836
837 /* End of fix for bug 5065079 */
838
839 --BUG 5068944 (FP of BUG 4992317)
840 PROCEDURE get_rec_uom_code(
841 x_return_status OUT NOCOPY VARCHAR2
842 , x_uom_code OUT NOCOPY VARCHAR2
843 , p_shipment_header_id IN NUMBER
844 , p_item_id IN NUMBER
845 , p_organization_id IN NUMBER
846 );
847 --END BUG 5068944
848 END INV_RCV_COMMON_APIS;
[Home] [Help]