This discussion is archived
1 Reply Latest reply: Sep 26, 2012 6:41 AM by 924340 RSS

Rearding API error

924340 Newbie
Currently Being Moderated
Hi I am trying to move LPN form one locator to another.
I am using an API ( inv_txn_manager_pub.process_transactions ) to perform this transaction.
We are performing two transactions one for Issue and another for Rceipt.
When I executed both procedure standalone they are working fine but if I call Receipt from issue I am getting an error saying INVALID LPN IN THE INTERFACE RECORD. So I added delay of 90 sec and it was working but ideally API should not take so much time to excute .
Any help appreciated..

Below is my package which i am calliing

CREATE OR REPLACE PACKAGE BODY APPS.xxcmfg_inv_process_pkg
AS

PROCEDURE wt_change_issue (
org_id IN NUMBER,
-- lpn IN VARCHAR2,
user_id IN NUMBER,
v_inv_item_id IN NUMBER,
v_curr_weight IN NUMBER,
v_p_uom IN VARCHAR2,
v_sub_inv_code IN VARCHAR2,
v_locator_id IN NUMBER,
v_lpn_no IN NUMBER,
v_lot_no IN VARCHAR2,
v_new_weight IN NUMBER,
v_new_loc IN VARCHAR2,
v_reason_id IN NUMBER,
-- api_sts OUT NOCOPY VARCHAR2,
-- api_msg OUT NOCOPY VARCHAR2,
err_msg OUT VARCHAR2
)
IS
v_txn_interface_id NUMBER := 0;
v_txn_hdr_id NUMBER := 0;
v_transaction_source_id NUMBER := 0;
v_transaction_source_name VARCHAR2 (20) := NULL;
v_trx_issue_type_id NUMBER;
v_issue_transaction_action_id NUMBER;
v_trx_receipt_type_id NUMBER;
l_proces_txn NUMBER; --returns 0 if Success else -1
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_trans_count NUMBER;
v_txn_source_type_id NUMBER;
-- v_sub_inv_code VARCHAR2 (50) := NULL;
-- v_locator_id NUMBER;
v_user_id NUMBER := user_id;
--fnd_profile.VALUE ('USER_ID');
v_org_id NUMBER := org_id; --86;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_lpn_state NUMBER;
v_message VARCHAR2 (2000);
v_phase VARCHAR2 (2000);
v_status VARCHAR2 (2000);
v_dev_phase VARCHAR2 (2000);
v_dev_status VARCHAR2 (2000);
l_val BOOLEAN;
l_request_id NUMBER := 0;

BEGIN


SELECT disposition_id, segment1
INTO v_transaction_source_id, v_transaction_source_name
FROM mtl_generic_dispositions
WHERE segment1 = 'ADJP' AND organization_id = v_org_id;


SELECT transaction_source_type_id
INTO v_txn_source_type_id
FROM mtl_txn_source_types
WHERE transaction_source_type_name = 'Account alias';


SELECT mtl_material_transactions_s.NEXTVAL,
mtl_material_transactions_s.CURRVAL
INTO v_txn_interface_id,
v_txn_hdr_id
FROM DUAL;


SELECT transaction_type_id, transaction_action_id
INTO v_trx_issue_type_id, v_issue_transaction_action_id
FROM mtl_transaction_types
WHERE transaction_type_name = 'Account alias issue';



INSERT INTO mtl_transactions_interface
(transaction_interface_id, 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,
inventory_item_id, organization_id, transaction_quantity,
transaction_uom, transaction_date, subinventory_code,
locator_id, transaction_source_id,
transaction_source_name, transaction_type_id,
flow_schedule, content_lpn_id, primary_quantity,
secondary_uom_code, secondary_transaction_quantity,
scheduled_flag, reason_id,
transaction_source_type_id, transaction_action_id--,lpn_id
)
VALUES (v_txn_interface_id, v_txn_hdr_id,
'Apps InvUtil Wt Change', 1, 1,
1, 3, SYSDATE,
v_user_id, SYSDATE, v_user_id,
v_inv_item_id, --:weight_change.inv_item_id,
v_org_id, --:weight_change.org_id,
(-1 * v_curr_weight
),
-- (-1 * :weight_change.curr_weight),
v_p_uom, --:weight_change.primary_uom_code,
SYSDATE, v_sub_inv_code,
--:weight_change.sub_inv_status,
v_locator_id, -- :weight_change.locator_id,
v_transaction_source_id,
v_transaction_source_name, v_trx_issue_type_id,
'Y', v_lpn_no, --:weight_change.lpn_no,
(-1 * v_curr_weight
), -- (-1 * :weight_change.curr_weight),
'PCS', 1,
2, v_reason_id --, :weight_change.reason_id
,
v_txn_source_type_id, v_issue_transaction_action_id--,v_lpn_no
);

INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id, source_code, source_line_id,
last_update_date, last_updated_by, creation_date,
created_by, lot_number, transaction_quantity,
primary_quantity, origination_date, retest_date,
secondary_transaction_quantity
)
VALUES (v_txn_interface_id, 'Apps InvUtil Wt Change', 1,
SYSDATE, v_user_id, SYSDATE,
v_user_id, v_lot_no, --:weight_change.lot_no,
(-1 * v_curr_weight
), --(-1 * :weight_change.curr_weight),
(-1 * v_curr_weight
), --(-1 * :weight_change.curr_weight),
SYSDATE, SYSDATE,
1
);

COMMIT;

l_proces_txn :=
inv_txn_manager_pub.process_transactions
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
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 => v_txn_hdr_id
);
commit;
dbms_lock.sleep(90);

DBMS_OUTPUT.put_line ('Issue completed');

IF x_return_status = 'S'
THEN

err_msg := 'issue success';
WT_CHANGE_RECEIPT ( ORG_ID, USER_ID, V_INV_ITEM_ID, V_NEW_WEIGHT, V_P_UOM, V_NEW_LOC, V_LPN_NO, V_LOT_NO,v_reason_id ,v_txn_source_type_id, ERR_MSG );
ELSE
err_msg := 'ERROR WHILE ISSUING - ' || v_message;
END IF;
END wt_change_issue;

PROCEDURE wt_change_receipt (
org_id IN NUMBER,
-- lpn IN VARCHAR2,
user_id IN NUMBER,
v_inv_item_id IN NUMBER,
v_new_weight IN NUMBER,
v_p_uom IN VARCHAR2,
v_new_loc IN VARCHAR2,
--v_locator_id     IN              NUMBER,
v_lpn_no IN NUMBER,
v_lot_no IN VARCHAR2,
-- api_sts OUT NOCOPY VARCHAR2,
-- api_msg OUT NOCOPY VARCHAR2,
v_reason_id IN NUMBER,
v_txn_source_type_id IN NUMBER,
err_msg OUT VARCHAR2
)
IS
v_sub_inv_code VARCHAR2 (100) := NULL;
v_locator_id NUMBER := 0;
v_txn_interface_id NUMBER := 0;
v_txn_hdr_id NUMBER := 0;
v_transaction_source_id NUMBER := 0;
v_transaction_source_name VARCHAR2 (20) := NULL;
v_trx_issue_type_id NUMBER;
v_receipt_action_id NUMBER;
v_trx_receipt_type_id NUMBER;
l_proces_txn NUMBER; --returns 0 if Success else -1
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_trans_count NUMBER;
-- v_sub_inv_code VARCHAR2 (50) := NULL;
-- v_locator_id NUMBER;
v_user_id NUMBER := user_id;
--fnd_profile.VALUE ('USER_ID');
v_org_id NUMBER := org_id; --86;
v_txn NUMBER;
v_lpn_state NUMBER;
v_message VARCHAR2 (2000);
v_phase VARCHAR2 (2000);
v_status VARCHAR2 (2000);
v_dev_phase VARCHAR2 (2000);
v_dev_status VARCHAR2 (2000);
l_val BOOLEAN;
l_request_id NUMBER := 0;
BEGIN

SELECT disposition_id, segment1
INTO v_transaction_source_id, v_transaction_source_name
FROM mtl_generic_dispositions
WHERE segment1 = 'ADJP' AND organization_id = v_org_id; --86;

SELECT mtl_material_transactions_s.NEXTVAL,
mtl_material_transactions_s.CURRVAL
INTO v_txn_interface_id,
v_txn_hdr_id
FROM DUAL;



SELECT inventory_location_id, subinventory_code
INTO v_locator_id, v_sub_inv_code
FROM mtl_item_locations
WHERE 1 = 1
AND segment1 = v_new_loc --:weight_change.new_loc
AND organization_id = v_org_id; --86;-- v_org_id;

SELECT transaction_type_id, transaction_action_id
INTO v_trx_receipt_type_id, v_receipt_action_id
FROM mtl_transaction_types
WHERE transaction_type_name = 'Account alias receipt';

INSERT INTO mtl_transactions_interface
(transaction_interface_id, 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,
inventory_item_id, organization_id, transaction_quantity,
transaction_uom, transaction_date, subinventory_code,
locator_id, transaction_source_id,
transaction_source_name, transaction_type_id,
flow_schedule, transfer_lpn_id, primary_quantity,
secondary_uom_code, secondary_transaction_quantity,
scheduled_flag, reason_id,
transaction_source_type_id, transaction_action_id
)
VALUES (v_txn_interface_id, v_txn_hdr_id,
'Apps InvUtil Wt Change', 1, 1,
1, 3, SYSDATE,
v_user_id, SYSDATE, v_user_id,
v_inv_item_id, --
v_org_id, --
v_new_weight, --
v_p_uom, --
SYSDATE, v_sub_inv_code,
v_locator_id, v_transaction_source_id,
v_transaction_source_name, v_trx_receipt_type_id,
'Y', v_lpn_no, v_new_weight,
'PCS', 1,
2, v_reason_id -- , :weight_change.reason_id
,
v_txn_source_type_id, v_receipt_action_id
);

INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id, source_code, source_line_id,
last_update_date, last_updated_by, creation_date,
created_by, lot_number, transaction_quantity,
primary_quantity, origination_date, retest_date,
secondary_transaction_quantity
)
VALUES (v_txn_interface_id, 'Apps InvUtil Wt Change', 1,
SYSDATE, v_user_id, SYSDATE,
v_user_id, v_lot_no, v_new_weight,
v_new_weight, SYSDATE, SYSDATE,
1
);

COMMIT;


l_proces_txn :=
inv_txn_manager_pub.process_transactions
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
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 => v_txn_hdr_id
);
commit;


iF x_return_status = 'S'
THEN
err_msg := 'receipt SUCCESSFUL';
ELSE
err_msg := 'receipt failed' || '--' || x_msg_data;
END IF;

END wt_change_receipt;
END xxcmfg_inv_process_pkg;
/
  • 1. Re: Rearding API error
    924340 Newbie
    Currently Being Moderated
    It was taking time because after issuing LPN_COTEXT was updated to 4(Issued out of Store) and it takes time to set to 5(Declared but not used).
    But it is varying from record to record for some record it is taking 10 sec and for others 2 min.
    Is there any way to reduce this time,because this is the time taken by only single transaction and I have to perfrom same transaction for around 30 records.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points