Shulou(Shulou.com)05/31 Report--

This article mainly explains "API how to achieve batch serial number sales out of the library", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "API how to achieve batch serial number sales out of the library" bar!

As manufacturing enterprises have higher and higher control over production and sales, more and more manufacturing enterprises have increased the control of material batches and serial numbers. We can in the inventory responsibilities: materials-> main organization materials / organization products-> inventory TAB page: to see whether sequences or batches are enabled in this inventory organization.

After enabling the batch and sequence control of the material, the boring needs to fill in the corresponding valid batch sequence when purchasing and selling out of the warehouse.

In Ebs, we often encounter inter-company transactions that need to be automated, such as automatic sales out of the warehouse and automatic purchase into the warehouse. This article will explain how to achieve sales with serial number, batch control out of the warehouse.

In the Ebs interface for manual sales out of the warehouse, we are to modify the properties of material waybill, material waybill-> deal with material waybill-> deal with material waybill distribution. If sequence and batch number control is enabled, the sequence / batch button will light up.

Since we actually deal with the modified batches and sequences when processing the material waybill, we will first think of the API of the material waybill to achieve this function: (before picking the warehouse to confirm) the API that can be used are:

Dead INV_MOVE_ORDER_PUB.Process_Move_Order

Dead INV_MOVE_ORDER_PUB.Process_Move_Order_Line

P_trolin_tbl (I). Lot_number, p_trolin_tbl (I). Serial_number_start, p_trolin_tbl (I). Serial_number_end in the API are allowed to be set and modified. However, it is disappointing that although it has been modified, there is no effect in picking up the warehouse and shipping, and the materials sent out are not the batches and sequences we specified at all.

This is because the inventory transaction is processed based on the data in the following three tables, not on the material handling line.

^ MTL_MATERIAL_TRANSACTIONS_TEMP (temporary table for inventory transactions)

@ MTL_TRANSACTION_LOTS_TEMP (batch temporary table)

Angular MTL_SERIAL_NUMBERS_TEMP (temporary table of serial numbers)

However, the three tables Oracle do not provide API to modify them.

So we thought: since they are temporary watches, why not UPDATE them directly? Then let's give it a try.

Processing steps:

1. Create a sales order

two。 Pick up and release (non-automatic confirmation)

3. Use INV_MOVE_ORDER_PUB.Process_Move_Order_Line to modify material handling line

4. Update relevant data in MMT, MLT, MST tables

5. Pick the library to confirm

6. Shipment confirmation

If your version is after 12.1, then the request during shipment will report an error, ERROR_CODE:Serial Mssing (in the case of serial number control, not in the batch, MetaLink patch solves this BUG).

But in any case, it's not good to directly UPDATE a watch (and there's a BUG), so what if you do?

We can use an undisclosed API of Oracle to feel this problem: inv_replenish_detail_pub.line_details_pub.

Solution:

Out of the original mindset, since Oracle does not have API (including undisclosed ones) to modify the sequence and batches of material handling orders, why don't we just make a material handling bill that meets our requirements?

The following is an API scheme for automatic transaction processing of sales orders:

1. Oe_order_pub.process_order creates a sales order.

2. Wsh_picking_batches_pub.Create_Batch creates a batch number (automatic confirmation: no / automatic assignment: no).

3. Wsh_picking_batches_pub.Release_Batch issues sales orders (concurrent, ONLINE is OK).

4. INV_Trolin_Util.Query_Rows acquires material handling order line

5. INV_MOVE_ORDER_PUB.Process_Move_Order_Line

Modify the material handling order line (the sending sublibrary above the head does not need to be modified).

6. Inv_replenish_detail_pub.line_details_pub

Create a material waybill distribution line (Note 1)

7. Inv_pick_wave_pick_confirm_pub.pick_confirm

Pick the library to confirm

8. Wsh_deliveries_pub.delivery_action delivery number shipment

9. Wsh_ship_confirm_actions.interface_all shipment confirmation

Note 1: if the distribution cannot be automatically assigned due to business requirements: no, or for other reasons, there is already a material handling line distribution line here, then please use API

Inv_mo_line_detail_util.reduce_allocation_quantity

To delete the allocation branch (when the reduced quantity is equal to the number of shipments, the transaction row will be deleted).

The following is an example of a call to modify the material handling line and regenerate the assigned single line of code:

[java] view plain copy

DECLARE

-- Common Declarations

L_api_version NUMBER: = 1.0

L_init_msg_list VARCHAR2 (2): = FND_API.G_TRUE

L_return_values VARCHAR2 (2): = FND_API.G_FALSE

L_commit VARCHAR2 (2): = FND_API.G_FALSE

X_return_status VARCHAR2 (2)

X_msg_count NUMBER: =

X_msg_data VARCHAR2 (255)

-- API specific declarations

L_header_id NUMBER: =

L_trohdr_rec INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE

L_trohdr_val_rec INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE

L_trolin_tbl INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE

O_trolin_tbl INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE

L_trolin_val_tbl INV_MOVE_ORDER_PUB.TROLIN_VAL_TBL_TYPE

X_trolin_tbl INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE

X_trolin_val_tbl INV_MOVE_ORDER_PUB.TROLIN_VAL_TBL_TYPE

X_trohdr_rec INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE

X_trohdr_val_rec INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE

-- Cursor to load Move Order Headers

L_mold_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type

X_mold_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type

L_move_order_type NUMBER: = 3

L_msg_return NUMBER

X_number_of_rows NUMBER

X_detailed_qty NUMBER

X_revision VARCHAR2 (20)

X_locator_id NUMBER

X_transfer_to_location NUMBER

X_lot_number VARCHAR2 (80)

X_expiration_date DATE

X_transaction_temp_id NUMBER

P_transaction_header_id NUMBER

P_transaction_mode NUMBER

P_move_order_type NUMBER: = 3

P_serial_flag VARCHAR2 (1)

P_plan_tasks BOOLEAN

P_auto_pick_confirm BOOLEAN

P_commit BOOLEAN

L_t_header_id NUMBER

L_lot_number VARCHAR2 (80): = 'SLT0021';-- mtl_lot_numbers.lot_number

L_serial_number VARCHAR2 (20): = '3.06.S0019;-- mtl_serial_numbers.serial_number

L_subinvetory_code VARCHAR2 (10): = 'BJJF_CLK';-- mtl_secondary_inventories.secondary_inventory_name

L_locator_id NUMBER: = 42;-- mtl_item_locations.inventory_location_id

L_trx_header_id NUMBER: = 93023;-- mtl_txn_request_headers.header_id

BEGIN

FND_GLOBAL.APPS_INITIALIZE (1371, 50627, 660)-- Suhasini / Mfg Mgr / INV

INV_MOVE_ORDER_PUB.Get_Move_Order (

P_API_VERSION_NUMBER = > l_api_version

, P_INIT_MSG_LIST = > l_init_msg_list

, P_RETURN_VALUES = > l_return_values

, X_RETURN_STATUS = > x_return_status

, X_MSG_COUNT = > x_msg_count

, X_MSG_DATA = > x_msg_data

, P_HEADER_ID = > l_trx_header_id--93023

, P_HEADER = > NULL

, X_TROHDR_REC = > l_trohdr_rec

, X_TROHDR_VAL_REC = > l_trohdr_val_rec

, X_TROLIN_TBL = > l_trolin_tbl

, X_TROLIN_VAL_TBL = > l_trolin_val_tbl

);

-- Print the Move Order Header/Lines to be processed

IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN

L_trohdr_rec.operation: = INV_GLOBALS.G_OPR_UPDATE

-- FOR i IN 1..l_trolin_tbl.COUNT LOOP

L_trolin_tbl (1) .from_subinventory_code: = l_subinvetory_code

L_trolin_tbl (1) .from_locator_id: = l_locator_id

L_trolin_tb1 (I) .lot_number: = l_lot_number

L_trolin_tbl (1) .serial_number_start: = l_serial_number

L_trolin_tbl (1) .serial_number_end: = l_serial_number

L_trolin_tbl (1) .attribute1: = 'update move order testworthy'

L_trolin_tbl (1) .operation: = INV_GLOBALS.G_OPR_UPDATE

-- END LOOP

ELSE

DBMS_OUTPUT.PUT_LINE ('Get_Move_Order errorships')

RETURN

END IF

INV_MOVE_ORDER_PUB.Process_Move_Order_Line (p_api_version_number = > 1.0)

, p_init_msg_list = > l_init_msg_list

, p_return_values = > l_return_values

, p_commit = > l_commit

, x_return_status = > x_return_status

, x_msg_count = > x_msg_count

, x_msg_data = > x_msg_data

, p_trolin_tbl = > l_trolin_tbl

, p_trolin_old_tbl = > l_trolin_tbl

, x_trolin_tbl = > x_trolin_tbl)

IF (x_return_status FND_API.G_RET_STS_SUCCESS) THEN

FOR i IN 1..x_msg_count LOOP

Fnd_msg_pub.get (p_msg_index = > I)

, p_encoded = >'F'

, p_data = > x_msg_data

, p_msg_index_out = > l_msg_return)

DBMS_OUTPUT.PUT_LINE (x_msg_data)

END LOOP

RETURN

ELSE

DBMS_OUTPUT.PUT_LINE ('MODIFY success')

END IF

/ * inv_mo_line_detail_util.reduce_allocation_quantity (

X_return_status = > x_return_status

, p_transaction_temp_id = > 2242994

, p_quantity = > 1

, p_secondary_quantity = > 1)

IF x_return_status 's THEN

DBMS_OUTPUT.PUT_LINE ('EE')

ELSE

DBMS_OUTPUT.PUT_LINE ('SS')

END IF;*/

Inv_replenish_detail_pub.line_details_pub (

P_line_id = > l_trolin_tbl (1). Line_id

, x_number_of_rows = > x_number_of_rows

, x_detailed_qty = > x_detailed_qty

, x_return_status = > x_return_status

, x_msg_count = > x_msg_count

, x_msg_data = > x_msg_data

, x_revision = > x_revision

, x_locator_id = > x_locator_id

, x_transfer_to_location = > x_transfer_to_location

, x_lot_number = > x_lot_number

, x_expiration_date = > x_expiration_date

, x_transaction_temp_id = > x_transaction_temp_id

, p_transaction_header_id = > NULL

, p_transaction_mode = > NULL

, p_move_order_type = > p_move_order_type

, p_serial_flag = > FND_API.G_FALSE

, p_plan_tasks = > FALSE

, p_auto_pick_confirm = > FALSE

, p_commit = > FALSE)

IF (x_return_status FND_API.G_RET_STS_SUCCESS) THEN

DBMS_OUTPUT.PUT_LINE ('E')

FOR i IN 1..x_msg_count LOOP

Fnd_msg_pub.get (p_msg_index = > I)

, p_encoded = >'F'

, p_data = > x_msg_data

, p_msg_index_out = > l_msg_return)

DBMS_OUTPUT.PUT_LINE (x_msg_data)

END LOOP

RETURN

ELSE

DBMS_OUTPUT.PUT_LINE ('ssssss')

DBMS_OUTPUT.PUT_LINE ('x_number_of_rows:' | | to_char (x_number_of_rows))

DBMS_OUTPUT.PUT_LINE ('x_locator_id:' | | to_char (x_locator_id))

DBMS_OUTPUT.PUT_LINE ('x_lot_number:' | | to_char (x_lot_number))

DBMS_OUTPUT.PUT_LINE ('x_transfer_to_location:' | | to_char (x_transfer_to_location))

DBMS_OUTPUT.PUT_LINE ('x_transaction_temp_id:' | | to_char (x_transaction_temp_id))

END IF

L_trolin_tbl: = INV_Trolin_Util.Query_Rows (p_line_id = > l_trolin_tbl (1) .line_id)

L_mold_tbl: = INV_MO_LINE_DETAIL_UTIL.query_rows (p_line_id = > l_trolin_tbl (1) .line_id)

INV_PICK_WAVE_PICK_CONFIRM_PUB.Pick_Confirm (p_api_version_number = > l_api_version)

P_init_msg_list = > l_init_msg_list

P_commit = > l_commit

X_return_status = > x_return_status

X_msg_count = > x_msg_count

X_msg_data = > x_msg_data

P_move_order_type = > l_move_order_type

P_transaction_mode = > 1

P_trolin_tbl = > l_trolin_tbl

P_mold_tbl = > l_mold_tbl

X_mmtt_tbl = > x_mold_tbl

X_trolin_tbl = > x_trolin_tbl)

IF (x_return_status FND_API.G_RET_STS_SUCCESS) THEN

DBMS_OUTPUT.PUT_LINE ('E')

FOR i IN 1..x_msg_count LOOP

Fnd_msg_pub.get (p_msg_index = > I)

, p_encoded = >'F'

, p_data = > x_msg_data

, p_msg_index_out = > l_msg_return)

DBMS_OUTPUT.PUT_LINE (x_msg_data)

END LOOP

RETURN

ELSE

DBMS_OUTPUT.PUT_LINE ('S')

END IF

-- END LOOP

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (SQLCODE | |':'| | SQLERRM)

END

Other demand situation

It is possible that the business only needs to display the serial number and batch number in the delivery number when it is shipped out of the warehouse, but there is no such requirement in the actual inventory control. After the warehouse is confirmed, the attribute of the delivery number can be modified through API to achieve the purpose of displaying the serial number and batch number: wsh_delivery_details_pub.Update_Shipping_Attributes

But this can not achieve the effect of actual out-of-warehouse control.

An example is also given below:

DECLARE

This is just an example of fetching numbers, modified to other fetching logic.

Cux begins with a customized table

CURSOR dev_header_cur (p_item_key VARCHAR2) IS

SELECT

Wdd.source_header_id AS source_header_id

, wdd.source_header_number AS source_header_number

, wdd.source_line_id AS source_line_id

, wda.delivery_id AS delivery_id

, wdd.delivery_detail_id AS delivery_detail_id

, wdd.organization_id AS organization_id

, wdd.source_code AS source_code

, wdd.requested_quantity AS requested_quantity

, tll.batch_number AS lot_number

, tll.sequence_number AS serial_number

, tll.line_number AS line_number

, tll.header_id AS load_header_id

, tll.line_id AS load_line_id

, tll.send_sec_inv_code AS send_subinventory

, tll.send_inv_location_id AS send_locator_id

FROM

Cux_tr_load_doc_wf_headers cwh

, cux_tr_load_doc_wf_lines cwl

, cux_tr_load_doc_lines_all tll

, oe_order_headers_all ooh

, oe_order_lines_all ool

, wsh_delivery_details wdd

, wsh_delivery_assignments wda

WHERE

Cwh.l_inner_oe_header_id = ooh.header_id

AND cwh.header_id = cwl.header_id

AND cwl.load_doc_line_id = tll.line_id

AND cwl.l_inner_oe_line_id = ool.line_id

AND ool.line_id = wdd.source_line_id-- bug fix 2010-08-19

AND wdd.delivery_detail_id = wda.delivery_detail_id

--

-- the value of parameter

--

AND cwh.item_key = 'STH001'

L_index NUMBER

L_msg_return NUMBER

X_return_status VARCHAR2 (1)

X_msg_count NUMBER

X_msg_data VARCHAR2 (2000)

L_source_code VARCHAR2 (40)

L_serialrangetabtype wsh_glbl_var_strct_grp.ddserialrangetabtype

L_changedattributetabtype wsh_delivery_details_pub.changedattributetabtype

BEGIN

Fnd_global.APPS_INITIALIZE (user_id = >-1)

, resp_id = >-1

, resp_appl_id = >-1)

L_index: =

FOR dev_header_rec IN dev_header_cur ('STH001') LOOP

L_index: = l_index + 1

L_source_code: = dev_header_rec.source_code

L_changedattributetabtype (l_index) .source_header_id: = dev_header_rec.source_header_id

L_changedattributetabtype (l_index) .source_line_id: = dev_header_rec.source_line_id

L_changedattributetabtype (l_index) .delivery_detail_id: = dev_header_rec.delivery_detail_id

L_changedattributetabtype (l_index) .subinventory: = 'CLK_SD'

L_changedattributetabtype (l_index) .locator_id: = 42betrom-location control

L_changedattributetabtype (l_index) .lot_number: = 'LOT_SK001';-- batch

IF dev_header_rec.requested_quantity = 1 THEN

L_changedattributetabtype (l_index) .serial_number: = 'LEOCHEN194'

END IF

FOR i IN 1..dev_header_rec.requested_quantity LOOP

L_serialrangetabtype (1) .delivery_detail_id: = dev_header_rec.delivery_detail_id

L_serialrangetabtype (1) .from_serial_number: = 'LEOCHEN194';--LEOCHEN165

L_serialrangetabtype (1) .to_serial_number: = 'LEOCHEN194'

-v_serialRangeTabType (1) .quantity: = 1;-- Dl.ordered_qty

L_serialrangetabtype (2) .delivery_detail_id: = dev_header_rec.delivery_detail_id

L_serialrangetabtype (2) .from_serial_number: = 'LEOCHEN195';--LEOCHEN165

L_serialrangetabtype (2) .to_serial_number: = 'LEOCHEN195'

-. Set multiple serial numbers on one line here

END LOOP

END LOOP

Wsh_delivery_details_pub.Update_Shipping_Attributes (p_api_version_number = > 1.0)

P_init_msg_list = > FND_API.G_FALSE

P_commit = > FND_API.G_FALSE

X_return_status = > x_return_status

X_msg_count = > x_msg_count

X_msg_data = > x_msg_data

P_changed_attributes = > l_changedattributetabtype

P_source_code = > l_source_code

P_container_flag = > NULL

P_serial_range_tab = > l_serialrangetabtype)

IF x_return_status fnd_api.G_RET_STS_SUCCESS THEN

FOR i IN 1..x_msg_count LOOP

Fnd_msg_pub.get (p_msg_index = > I)

, p_encoded = >'F'

, p_data = > x_msg_data

, p_msg_index_out = > l_msg_return)

Dbms_output.put_line (x_msg_data)

END LOOP

ELSE

Dbms_output.put_line ('S')

END IF

END

Thank you for your reading, the above is the "API how to achieve batch serial number sales out of the library" content, after the study of this article, I believe you on API how to achieve batch serial number sales out of the warehouse this problem has a deeper understanding, the specific use of the need for everyone to practice and verify. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.