The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT batch.source_system_id, NVL(opt.import_xref_only,'N'), NVL(opt.ENABLED_FOR_DATA_POOL, 'N')
INTO l_source_system_id, l_import_xref_only, l_enabled_for_data_pool
FROM ego_import_batches_b batch
,ego_import_option_sets opt
WHERE batch.batch_id = p_xset_id
AND batch.batch_id = opt.batch_id;
SELECT 1 INTO l_pro_flag_3
FROM mtl_system_items_interface
WHERE process_flag = 3
AND request_id = l_request_id
AND set_process_id = p_xset_id
AND rownum = 1;
SELECT count(*) INTO l_pro_flag_3
FROM mtl_item_revisions_interface
WHERE process_flag = 3
AND request_id = l_request_id
AND rownum = 1;
SELECT intf.inventory_item_id
,intf.organization_id
,intf.revision_id
,intf.revision
FROM mtl_item_revisions_interface intf
WHERE intf.set_process_id = p_xset_id
AND intf.transaction_type = 'CREATE'
AND intf.request_id = p_request_id
AND intf.process_flag = 7
AND intf.revision_id is not null
/* Bug 7675166 added this validation as revision_id is passed as null in case new revision needs to be created with existing items for item effective AG from excel*/
AND NOT EXISTS (SELECT NULL
FROM mtl_parameters param
WHERE param.organization_id = intf.organization_id
AND param.starting_revision = intf.revision);
SELECT revision_id
FROM mtl_item_revisions_b
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id
AND revision < cp_revision
AND implementation_date IS NOT NULL
AND effectivity_date <= sysdate
ORDER BY effectivity_date desc;
SELECT batch.source_system_id, NVL(opt.import_xref_only,'N'), NVL(opt.ENABLED_FOR_DATA_POOL,'N')
INTO l_source_system_id, l_import_xref_only, l_enabled_for_data_pool
FROM ego_import_batches_b batch
,ego_import_option_sets opt
WHERE batch.batch_id = p_xset_id
AND batch.batch_id = opt.batch_id;
,p_delete_lines => p_del_rec_flag);
,p_delete_line_type => p_del_rec_flag
,p_mode =>'NORMAL'
,P_perform_security_check => FND_API.G_TRUE);
param p_selectQuery:Query for getting ITEM_NUMBER,ORGANIZATION_CODE,ERROR_MESSAGE
from the respective interface tables calling this API.
param p_request_id :Request ID of the transaction.
param x_return_status:Returns the unexpected error encountered during processing.
param x_msg_count: Indicates how many messages exist on ERROR_HANDLER
message stack upon completion of processing.
param x_msg_data:Contains message in ERROR_HANDLER message stack
upon completion of processing.
*/
--------------------------------------------------------------------------------------
PROCEDURE Write_Error_into_ConcurrentLog (
p_entity_name IN VARCHAR2,
p_table_name IN VARCHAR2,
p_selectQuery IN VARCHAR2,
p_request_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_dyn_sql VARCHAR2(10000);
l_dyn_sql := p_selectQuery;
,p_delete_rec_flag IN NUMBER := 0
,p_commit_flag IN NUMBER := 1
,p_prog_appid IN NUMBER := NULL
,p_prog_id IN NUMBER := NULL
,p_request_id IN NUMBER := NULL
,p_user_id IN NUMBER := NULL
,p_login_id IN NUMBER := NULL) IS
BEGIN
INV_EGO_REVISION_VALIDATE.Set_Process_Control('EGO_ITEM_BULKLOAD');
,p_delete_rec_flag => p_delete_rec_flag
,p_commit_flag => p_commit_flag
,p_prog_appid => p_prog_appid
,p_prog_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id --4105841
,p_user_id => p_user_id
,p_login_id => fnd_global.conc_login_id);
UPDATE mtl_system_items_interface
SET template_id = p_template_id
WHERE process_flag = 1
AND set_process_id = p_xset_id
AND((p_all_org = 1) or (organization_id = p_org_id));
UPDATE mtl_system_items_interface
SET template_id = null
WHERE process_flag = 1
AND set_process_id = p_xset_id
AND((p_all_org = 1) or (organization_id = p_org_id));
SELECT batch.source_system_id, NVL(opt.import_xref_only,'N'), NVL(opt.ENABLED_FOR_DATA_POOL,'N')
INTO l_source_system_id, l_import_xref_only, l_enabled_for_data_pool
FROM
ego_import_batches_b batch,
ego_import_option_sets opt
WHERE batch.batch_id = p_batch_id
AND batch.batch_id = opt.batch_id;
INVPUTLI.info('Calling EGO_IMPORT_UTIL_PVT.Update_Timestamp_In_Prod');
EGO_IMPORT_UTIL_PVT.Update_Timestamp_In_Prod(
ERRBUF => ERRBUF
,RETCODE => RETCODE
,p_batch_id => p_batch_id);
INVPUTLI.info('Returned EGO_IMPORT_UTIL_PVT.Update_Timestamp_In_Prod '||RETCODE);
l_retcode := INVPOPIF.indelitm_delete_item_oi (err_text => err_msg,
xset_id => p_batch_id);
INVPUTLI.info('Returned INVPOPIF.indelitm_delete_item_oi '||err_msg);
FOR i IN (SELECT msii.TRANSACTION_TYPE, msii.ITEM_NUMBER, NVL(msii.ORGANIZATION_CODE, mp.ORGANIZATION_CODE) AS ORGANIZATION_CODE
FROM MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
WHERE msii.SET_PROCESS_ID = p_batch_id
AND msii.REQUEST_ID = l_request_id
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
--AND mp.MASTER_ORGANIZATION_ID = mp.ORGANIZATION_ID Bug 11901255
AND msii.PROCESS_FLAG = 7
AND NVL(msii.CONFIRM_STATUS, 'X') NOT IN ('CFC', 'CFM', 'FMR', 'UFN', 'UFS', 'UFM', 'FK', 'FEX')
)
LOOP
FND_FILE.put_line(FND_FILE.LOG, RPAD(i.TRANSACTION_TYPE, 16, ' ') ||' '||RPAD(i.ORGANIZATION_CODE, 17, ' ') ||' '||i.ITEM_NUMBER);