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