Transaction open interface in oracle apps r12
In this post , we will be discuss about Transaction open interface in oracle apps r12. Transaction Open Interface in oracle is uses to Upload the Inventory related transactions in the Oracle apps. This is Common Interface to Upload any kind of Inventory Transaction available in the Material Transaction Form. This is very important interface of the Inventory in Oracle Apps. We can do lot of transactions with this interface. Here below I will share one Example and Complete detail of this Transaction open interface in oracle apps r12.
What we can do with Transaction open interface in oracle apps r12
We can do multiple inventory transactions with the help of this Interface. I will share few of them as per my Experience.
1. Miscellaneous Receipt.
2.Miscellaneous Issue.
3.Subinvnetory Transfer.
4.Move Order Transfer.
5.Move Order Issue.
These above the most common uses of this Transaction open interface in oracle apps r12.
If you want to know how many kind of Transaction we can process with Open Interface then you can refer this MTL_TRANSACTION_TYPES.
To Process the Transaction open interface records in oracle apps r12
Now go to Inventory responsibility
1.Ctrl+L select launch interface manager
2.Cursor must on Material Transaction
3.Go to tools menu and there select launch manager and submit the request
Example of Transaction open interface in oracle apps r12
Declare
v_from_org_id NUMBER;
v_to_org_id NUMBER;
v_inventory_item_id NUMBER;
v_transaction_type_id NUMBER;
uom VARCHAR2 (200);
v_sqlcode VARCHAR2 (2000);
v_sqlerrm VARCHAR2 (2000);
CURSOR cur_stock_interface
IS
SELECT * FROM XX_OPENING_STOCK
WHERE NVL(VAILIDATE_FLAG_PROCESS,'E') = 'P'
AND NVL (FLAG_PROCESS, 'NULL') = 'NULL';
BEGIN
FOR i IN cur_stock_interface
LOOP
BEGIN
BEGIN
SELECT a.organization_id
INTO v_from_org_id
FROM mtl_parameters a, org_organization_definitions b
WHERE a.master_organization_id = b.organization_id
AND a.organization_code = i.org_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_from_org_id := NULL;
WHEN OTHERS
THEN
v_from_org_id := NULL;
END;
END;
-----------------
BEGIN
SELECT inventory_item_id
INTO v_inventory_item_id
FROM mtl_system_items_b
WHERE organization_id = v_from_org_id AND segment1 = i.item_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_inventory_item_id := 0;
WHEN OTHERS
THEN
v_inventory_item_id := 0;
END;
BEGIN
SELECT primary_uom_code
INTO uom
FROM mtl_system_items_b
WHERE organization_id = v_from_org_id AND segment1 = i.item_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
uom := NULL;
WHEN OTHERS
THEN
uom := NULL;
END;
---------------------For Transaction Type Id
BEGIN
IF i.stock_type = 'R'
THEN
BEGIN
SELECT transaction_type_id
INTO v_transaction_type_id
FROM mtl_transaction_types
WHERE UPPER (transaction_type_name) =
UPPER ('Miscellaneous Recpt(RG Update)');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_transaction_type_id := NULL;
WHEN OTHERS
THEN
v_transaction_type_id := NULL;
END;
ELSIF i.stock_type = 'I'
THEN
BEGIN
SELECT transaction_type_id
INTO v_transaction_type_id
FROM mtl_transaction_types
WHERE UPPER (transaction_type_name) =
UPPER ('Miscellaneous Issue(RG Update)');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_transaction_type_id := NULL;
WHEN OTHERS
THEN
v_transaction_type_id := NULL;
END;
END IF;
END;
-------------------------Insert Interface Data-----------------
BEGIN
INSERT INTO mtl_transactions_interface
(transaction_interface_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,
inventory_item_id, subinventory_code, locator_id,
transaction_quantity, transaction_uom,
transaction_date, distribution_account_id,
transaction_cost, attribute1, transaction_type_id,
transaction_reference
)
VALUES (mtl_system_items_interface_s.NEXTVAL,
i.source_code, mtl_demand_interface_s.NEXTVAL,
mtl_demand_interface_s.CURRVAL, 1, 3,
SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.user_id, v_from_org_id,
v_inventory_item_id, i.sub_inventory, i.locator_id,
i.qty, uom,
i.transaction_date, i.distribution_account,
i.rate, i.attribute1, v_transaction_type_id,
'Open Stock'
);
UPDATE xx_opening_stock
SET flag_process = 'P'
WHERE entry_id = i.entry_id;
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
UPDATE xx_opening_stock
SET flag_process = 'E',
error_message = v_sqlerrm
WHERE entry_id = i.entry_id;
END;
END LOOP;
0 comments:
Post a Comment