--INV Items
--Concurrent programs:Import Items N: INV/Items/Import/Import Items
SELECT *
FROM mtl_system_items_interface
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
SELECT *
FROM mtl_item_revisions_interface
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
SELECT *
FROM mtl_interface_errors
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
--Delete
DELETE FROM mtl_system_items_interface
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
DELETE FROM mtl_item_revisions_interface
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
DELETE FROM mtl_interface_errors
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
--******************************************************************************
SELECT *
FROM mtl_system_items_b
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
SELECT *
FROM mtl_system_items_tl
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
SELECT *
FROM mtl_item_revisions_b
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
SELECT *
FROM mtl_item_revisions_tl
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
--******************************************************************************
--Table Relation:
/*mtl_system_items_interface.organization_code = mtl_item_revisions_interface.organization_code
AND
mtl_system_items_interface.item_number = mtl_item_revisions_interface.item_number
mtl_interface_errors*/
SELECT mie.error_message
FROM mtl_system_items_interface mii, mtl_interface_errors mie
WHERE mii.transaction_id = mie.transaction_id;
--******************************************************************************
--Use templete creat item
DECLARE
l_iface_rec inv.mtl_system_items_interface%ROWTYPE;
l_user_id NUMBER := 1318;
--User ID, Sysadmin here
l_mst_mfg_org_id NUMBER := 204;
--Master Inv Organization
l_cur_mfg_org_id NUMBER := 207;
--Current Inv Organization
BEGIN
l_iface_rec.last_update_date := SYSDATE;
l_iface_rec.last_updated_by := l_user_id;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := l_user_id;
l_iface_rec.last_update_login := -1;
l_iface_rec.process_flag := 1;
l_iface_rec.transaction_type := 'CREATE';
l_iface_rec.set_process_id := 987654321;
l_iface_rec.segment1 := 'MON_TEST_ITEM_0001';
l_iface_rec.primary_uom_code := 'Ea';
l_iface_rec.description := 'For Interface Test';
l_iface_rec.template_id := 259;
--Purchased Item?table inv.mtl_item_templates
--Master Inv Organization
l_iface_rec.organization_id := l_mst_mfg_org_id;
INSERT INTO mtl_system_items_interface
VALUES l_iface_rec;
--Current Inv Organization
l_iface_rec.organization_id := l_cur_mfg_org_id;
INSERT INTO inv.mtl_system_items_interface
VALUES l_iface_rec;
COMMIT;
END;
--******************************************************************************
SELECT *
FROM inv.mtl_item_templates mit
WHERE mit.template_name LIKE 'Purchased%';
--******************************************************************************
SELECT mp.*
FROM inv.mtl_parameters mp
WHERE mp.organization_id = 84;
--******************************************************************************
--Assign organization
SELECT *
FROM mtl_parameters mp
WHERE mp.organization_id = 84;
DECLARE
l_iface_rec inv.mtl_system_items_interface%ROWTYPE;
l_user_id NUMBER := 1318;
--User ID, Sysadmin here
l_cur_mfg_org_id NUMBER := 208;
--Current Inv Organization
BEGIN
l_iface_rec.last_update_date := SYSDATE;
l_iface_rec.last_updated_by := l_user_id;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := l_user_id;
l_iface_rec.last_update_login := -1;
l_iface_rec.process_flag := 1;
l_iface_rec.transaction_type := 'CREATE';
l_iface_rec.set_process_id := 987654321;
l_iface_rec.organization_id := l_cur_mfg_org_id;
l_iface_rec.segment1 := 'MON_TEST_ITEM_0001';
INSERT INTO inv.mtl_system_items_interface
VALUES l_iface_rec;
COMMIT;
END;
--******************************************************************************
--Use API Create item templete
DECLARE
l_item_rec inv_item_grp.item_rec_type;
x_item_rec inv_item_grp.item_rec_type;
x_error_tbl inv_item_grp.error_tbl_type;
l_template_id NUMBER := 259;
--Purchased Item£table inv.mtl_item_templates
x_return_status VARCHAR2 (1);
l_user_id NUMBER := 1318;
--User ID, Sysadmin here
l_mst_mfg_org_id NUMBER := 204;
--Master Inv Organization
l_cur_mfg_org_id NUMBER := 207;
--Current Inv Organization
BEGIN
--Initialize first, or create_by will be -1
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => 50583,
resp_appl_id => 401
);
--Item
l_item_rec.item_number := 'MON_TEST_ITEM_0002';
l_item_rec.description := 'For API Test';
l_item_rec.primary_uom_code := 'Ea';
--Master Inv Organization first, then Current Inv Organization
--l_item_rec.organization_id := l_mst_mfg_org_id;
l_item_rec.organization_id := l_cur_mfg_org_id;
--API
inv_item_grp.create_item (p_commit => fnd_api.g_false,
p_item_rec => l_item_rec,
x_item_rec => x_item_rec,
x_return_status => x_return_status,
x_error_tbl => x_error_tbl,
p_template_id => l_template_id
);
--Result
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
ROLLBACK;
FOR i IN 1 .. x_error_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line ( 'Transaction ID :'
|| x_error_tbl (i).transaction_id
);
DBMS_OUTPUT.put_line ( 'Unique ID :'
|| x_error_tbl (i).unique_id
);
DBMS_OUTPUT.put_line ( 'Message Name :'
|| x_error_tbl (i).message_name
);
DBMS_OUTPUT.put_line ( 'Message Text: :'
|| x_error_tbl (i).MESSAGE_TEXT
);
DBMS_OUTPUT.put_line ( 'Table Name :'
|| x_error_tbl (i).table_name
);
DBMS_OUTPUT.put_line ('Column Name :'
|| x_error_tbl (i).column_name
);
DBMS_OUTPUT.put_line ( 'Organization ID :'
|| x_error_tbl (i).organization_id
);
END LOOP;
IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
THEN
RAISE fnd_api.g_exc_unexpected_error;
ELSIF (x_return_status = fnd_api.g_ret_sts_error)
THEN
RAISE fnd_api.g_exc_error;
END IF;
ELSE
COMMIT;
DBMS_OUTPUT.put_line (x_item_rec.inventory_item_id);
END IF;
COMMIT;
END;
--******************************************************************************
--use API Update Item flag
DECLARE
l_item_rec inv_item_grp.item_rec_type;
x_item_rec inv_item_grp.item_rec_type;
x_error_tbl inv_item_grp.error_tbl_type;
x_return_status VARCHAR2 (1);
l_user_id NUMBER := 1318;
--User ID, Sysadmin here
l_cur_mfg_org_id NUMBER := 208;
--Current Inv Organization
BEGIN
--Initialize first, or create_by will be -1
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => 50583,
resp_appl_id => 401
);
--Item
l_item_rec.item_number := '100E033L';
l_item_rec.inventory_item_status_code := 'Inactive'; --Active Inactive
--Master Inv Organization first, then Current Inv Organization
l_item_rec.organization_id := l_cur_mfg_org_id;
--API
inv_item_grp.update_item (p_commit => fnd_api.g_false,
p_item_rec => l_item_rec,
x_item_rec => x_item_rec,
x_return_status => x_return_status,
x_error_tbl => x_error_tbl
);
--Result
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
ROLLBACK;
FOR i IN 1 .. x_error_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line ( 'Transaction ID :'
|| x_error_tbl (i).transaction_id
);
DBMS_OUTPUT.put_line ( 'Unique ID :'
|| x_error_tbl (i).unique_id
);
DBMS_OUTPUT.put_line ( 'Message Name :'
|| x_error_tbl (i).message_name
);
DBMS_OUTPUT.put_line ( 'Message Text: :'
|| x_error_tbl (i).MESSAGE_TEXT
);
DBMS_OUTPUT.put_line ( 'Table Name :'
|| x_error_tbl (i).table_name
);
DBMS_OUTPUT.put_line ('Column Name :'
|| x_error_tbl (i).column_name
);
DBMS_OUTPUT.put_line ( 'Organization ID :'
|| x_error_tbl (i).organization_id
);
END LOOP;
IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
THEN
RAISE fnd_api.g_exc_unexpected_error;
ELSIF (x_return_status = fnd_api.g_ret_sts_error)
THEN
RAISE fnd_api.g_exc_error;
END IF;
ELSE
COMMIT;
DBMS_OUTPUT.put_line (x_item_rec.item_number);
END IF;
END;