How to Perform A Miscellaneous Transaction Using An API (文档 ID 2078693.1)

APPLIES TO:

Oracle Inventory Management - Version 12.0.6 and later
Information in this document applies to any platform.

GOAL

How to Perform Miscellaneous Transaction Using API.

SOLUTION

1) Below is a sample insert script.

Insert into mtl_transactions_interface
(
TRANSACTION_HEADER_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
PROCESS_FLAG,
TRANSACTION_MODE,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
ORGANIZATION_ID ,
TRANSACTION_QUANTITY ,
TRANSACTION_UOM ,
TRANSACTION_DATE ,
TRANSACTION_TYPE_ID ,
INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
dst_segment1,
dst_segment2,
dst_segment3,
dst_segment4,
dst_segment5
)

VALUES
(
12345,   --TRANSACTION_HEADER_ID,
'Test' , --SOURCE_CODE
83,      --SOURCE_LINE_ID,
83,      --SOURCE_HEADER_ID,
1,       --PROCESS_FLAG,
3,       --TRANSACTION_MODE,
SYSDATE, --LAST_UPDATE_DATE ,
6096,    --LAST_UPDATED_BY ,
SYSDATE, --CREATION_DATE ,
6096,    --CREATED_BY ,
207,     --ORGANIZATION_ID ,
3,       --TRANSACTION_QUANTITY ,
'Ea',    --TRANSACTION_UOM ,
SYSDATE, --TRANSACTION_DATE ,
42,      --TRANSACTION_TYPE_ID ,
149,     --INVENTORY_ITEM_ID,
'Loc',   --SUBINVENTORY_CODE
'01',    --dst_segment1,
'580',   --dst_segment2,
'7740',  --dst_segment3,
'0000',  --dst_segment4,
'000'    --dst_segment5
)

 

COMMIT ;

2) The below API can be used for processing the record from the interface table.

set serveroutput on
declare
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2(255);
x_trans_count number;
l_result number;
l_header_id NUMBER;

-- WHO columns
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_row_cnt NUMBER := 1;
l_user_name VARCHAR2(30) := 'OPERATIONS'; --Need Modification
l_resp_name VARCHAR2(30) := 'INVENTORY_VISION_OPERATIONS';--Need Modification

BEGIN

-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;

-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;

select transaction_header_id into l_header_id
from mtl_transactions_interface
where source_code like 'Ramya';

FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);
dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '||l_application_id );

-- call API to process transaction
DBMS_OUTPUT.PUT_LINE('=======================================================');
DBMS_OUTPUT.PUT_LINE('Calling inv_txn_manager_pub.process_Transactions API');

l_result := inv_txn_manager_pub.process_Transactions( p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => fnd_api.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_trans_count => x_trans_count,
p_table => 1,
p_header_id => l_header_id);

DBMS_OUTPUT.PUT_LINE('=======================================================');
DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);

IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);
END IF;

IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('Transaction Processed Successfully');
END IF;

DBMS_OUTPUT.PUT_LINE('=======================================================');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('=======================================================');

END;