The following lines contain the word 'select', 'insert', 'update' or 'delete':
select transaction_type_id
into l_txn_type_id
from mtl_transaction_types
where transaction_type_name = p_trx_type;
select mtl_txns_history_s.nextval
into l_entity_id
from dual;
inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
insert into mtl_adjustment_sync_temp
(TRANSACTION_NUMBER,
TRANSACTION_DATE,
CATEGORY,
CATEGORY_ID,
WAREHOUSE,
ORGANIZATION_ID,
ITEM,
ITEM_DESCRIPTION,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY,
LOCATOR,
TRANSFER_WAREHOUSE,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR,
LPN,
TRANSFER_LPN,
CONTENT_LPN,
TRANSACTION_TYPE,
TRANSACTION_TYPE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE,
CREATION_DATE,
TRANSACTION_EXTRACTED,
XML_DOCUMENT_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM,
SECONDARY_QUANTITY,
SECONDARY_UOM,
ENTITY_ID)
select
TRANSACTION_NUMBER,
TRANSACTION_DATE,
CATEGORY,
CATEGORY_ID,
WAREHOUSE,
ORGANIZATION_ID,
WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
ITEM_DESCRIPTION,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY,
LOCATOR,
TRANSFER_WAREHOUSE,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR,
LPN,
TRANSFER_LPN,
CONTENT_LPN,
TRANSACTION_TYPE,
TRANSACTION_TYPE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE,
CREATION_DATE,
TRANSACTION_EXTRACTED,
XML_DOCUMENT_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM,
SECONDARY_QUANTITY,
SECONDARY_UOM,
l_entity_id
from mtl_adj_sync_wrapper_v
where organization_id = p_org_id
AND wms_deploy.get_client_code(inventory_item_id) = p_client_code
AND NVL(transaction_extracted, 'N') NOT IN ('Y','P')
AND transaction_type_id = nvl(l_txn_type_id, transaction_type_id)
AND transaction_date >= trx_date_from
AND transaction_date < trx_date_to + 1; -- Added 1 as the input dates are truncated.
inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
insert into mtl_adjustment_sync_temp
(TRANSACTION_NUMBER,
TRANSACTION_DATE,
CATEGORY,
CATEGORY_ID,
WAREHOUSE,
ORGANIZATION_ID,
ITEM,
ITEM_DESCRIPTION,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY,
LOCATOR,
TRANSFER_WAREHOUSE,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR,
LPN,
TRANSFER_LPN,
CONTENT_LPN,
TRANSACTION_TYPE,
TRANSACTION_TYPE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE,
CREATION_DATE,
TRANSACTION_EXTRACTED,
XML_DOCUMENT_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM,
SECONDARY_QUANTITY,
SECONDARY_UOM,
ENTITY_ID)
select
TRANSACTION_NUMBER,
TRANSACTION_DATE,
CATEGORY,
CATEGORY_ID,
WAREHOUSE,
ORGANIZATION_ID,
WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
ITEM_DESCRIPTION,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY,
LOCATOR,
TRANSFER_WAREHOUSE,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR,
LPN,
TRANSFER_LPN,
CONTENT_LPN,
TRANSACTION_TYPE,
TRANSACTION_TYPE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE,
CREATION_DATE,
TRANSACTION_EXTRACTED,
XML_DOCUMENT_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM,
SECONDARY_QUANTITY,
SECONDARY_UOM,
l_entity_id
from mtl_adj_sync_wrapper_v
where organization_id = p_org_id
AND wms_deploy.get_client_code(inventory_item_id) = p_client_code
AND xml_document_id = p_xml_doc_id
AND NVL(transaction_extracted, 'N') IN ('Y');
inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
insert into mtl_adjustment_sync_temp
(TRANSACTION_NUMBER,
TRANSACTION_DATE,
CATEGORY,
CATEGORY_ID,
WAREHOUSE,
ORGANIZATION_ID,
ITEM,
ITEM_DESCRIPTION,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY,
LOCATOR,
TRANSFER_WAREHOUSE,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR,
LPN,
TRANSFER_LPN,
CONTENT_LPN,
TRANSACTION_TYPE,
TRANSACTION_TYPE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE,
CREATION_DATE,
TRANSACTION_EXTRACTED,
XML_DOCUMENT_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM,
SECONDARY_QUANTITY,
SECONDARY_UOM,
ENTITY_ID)
select
TRANSACTION_NUMBER,
TRANSACTION_DATE,
CATEGORY,
CATEGORY_ID,
WAREHOUSE,
ORGANIZATION_ID,
WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
ITEM_DESCRIPTION,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY,
LOCATOR,
TRANSFER_WAREHOUSE,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR,
LPN,
TRANSFER_LPN,
CONTENT_LPN,
TRANSACTION_TYPE,
TRANSACTION_TYPE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE,
CREATION_DATE,
TRANSACTION_EXTRACTED,
XML_DOCUMENT_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM,
SECONDARY_QUANTITY,
SECONDARY_UOM,
l_entity_id
from mtl_adj_sync_wrapper_v
where organization_id = p_org_id
AND NVL(transaction_extracted, 'N') NOT IN ('Y','P')
AND transaction_type_id = nvl(l_txn_type_id, transaction_type_id)
AND transaction_date >= trx_date_from
AND transaction_date < trx_date_to + 1;
inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
insert into mtl_adjustment_sync_temp
(TRANSACTION_NUMBER,
TRANSACTION_DATE,
CATEGORY,
CATEGORY_ID,
WAREHOUSE,
ORGANIZATION_ID,
ITEM,
ITEM_DESCRIPTION,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY,
LOCATOR,
TRANSFER_WAREHOUSE,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR,
LPN,
TRANSFER_LPN,
CONTENT_LPN,
TRANSACTION_TYPE,
TRANSACTION_TYPE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE,
CREATION_DATE,
TRANSACTION_EXTRACTED,
XML_DOCUMENT_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM,
SECONDARY_QUANTITY,
SECONDARY_UOM,
ENTITY_ID)
select
TRANSACTION_NUMBER,
TRANSACTION_DATE,
CATEGORY,
CATEGORY_ID,
WAREHOUSE,
ORGANIZATION_ID,
WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
ITEM_DESCRIPTION,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY,
LOCATOR,
TRANSFER_WAREHOUSE,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR,
LPN,
TRANSFER_LPN,
CONTENT_LPN,
TRANSACTION_TYPE,
TRANSACTION_TYPE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE,
CREATION_DATE,
TRANSACTION_EXTRACTED,
XML_DOCUMENT_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM,
SECONDARY_QUANTITY,
SECONDARY_UOM,
l_entity_id
from mtl_adj_sync_wrapper_v
where organization_id = p_org_id
AND xml_document_id = p_xml_doc_id
AND NVL(transaction_extracted, 'N') IN ('Y');
inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
UPDATE mtl_material_transactions
SET transaction_extracted = 'P'
WHERE organization_id = p_org_id
AND NVL(transaction_extracted, 'N') NOT IN ( 'Y', 'P')
AND xml_document_id IS NULL
AND transaction_id IN (select transaction_number
from mtl_adjustment_sync_temp
where entity_id = l_entity_id);
UPDATE mtl_material_transactions
SET transaction_extracted = null,
xml_document_id = null
WHERE organization_id = p_org_id
AND NVL(transaction_extracted, 'N') = 'P'
AND xml_document_id IS NULL
AND transaction_id IN (select transaction_number
from mtl_adjustment_sync_temp
where entity_id = l_entity_id);
delete_temp_table(l_entity_id);
delete_temp_table(l_entity_id);
PROCEDURE delete_temp_table (p_entity_id NUMBER) IS
BEGIN
if (p_entity_id is not null) then
if (g_debug = 1) then
inv_trx_util_pub.TRACE('deleting the temp table for entity_id: '||p_entity_id, 'INV_INVENTORY_ADJUSTMENT', 9);
delete from mtl_adjustment_sync_temp
where entity_id = p_entity_id;
inv_trx_util_pub.TRACE('Exception : '||sqlerrm||' occurred in delete_temp_table', 'INV_INVENTORY_ADJUSTMENT', 9);
END delete_temp_table;