The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mtl_system_items_intf_sets_s.NEXTVAL
INTO l_set_process_id
FROM dual;
SELECT DISTINCT organization_id
INTO l_in_context_organization_id
FROM ego_massupdate_item_tmp
WHERE batch_id = p_batch_id;
INSERT INTO mtl_system_items_interface
( process_flag
, set_process_id
, transaction_type
, inventory_item_id
, item_number -- added for Item-Org assignment across master orgs
, description -- added for Item-Org assignment across master orgs
, organization_id
, primary_uom_code
, primary_unit_of_measure
, cost_of_sales_account
, encumbrance_account
, sales_account
, expense_account
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
)
SELECT 1, --Process_Flag
l_set_process_id, --SET_PROCESS_ID
'CREATE', --TXN_TYPE
emit.inventory_item_id, --ITEM_ID
emit.item_number,
emit.description,
emot.organization_id_child, --ORG_ID
NVL( (SELECT msib.primary_uom_code
FROM mtl_system_items_b msib, mtl_parameters mp
WHERE msib.organization_id = mp.master_organization_id
AND mp.organization_id = emot.organization_id_child
AND msib.inventory_item_id = emit.inventory_item_id),
(SELECT msib2.primary_uom_code
FROM mtl_system_items_b msib2
WHERE msib2.organization_id = emit.organization_id
AND msib2.inventory_item_id = emit.inventory_item_id)
) UOM_CODE,
NVL( (SELECT msib.primary_unit_of_measure -- Can We avoid this second select
FROM mtl_system_items_b msib, mtl_parameters mp
WHERE msib.organization_id = mp.master_organization_id
AND mp.organization_id = emot.organization_id_child
AND msib.inventory_item_id = emit.inventory_item_id),
(SELECT msib2.primary_unit_of_measure
FROM mtl_system_items_b msib2
WHERE msib2.organization_id = emit.organization_id
AND msib2.inventory_item_id = emit.inventory_item_id)
), --PUOM
mp.cost_of_sales_account,
mp.encumbrance_account,
mp.sales_account,
mp.expense_account,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
FND_GLOBAL.conc_request_id,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
SYSDATE
FROM ego_massupdate_item_tmp emit,
ego_massupdate_org_tmp emot,
mtl_parameters mp
WHERE NOT EXISTS
(SELECT '1'
FROM mtl_system_items_b msib
WHERE msib.organization_id = emot.organization_id_child
AND msib.inventory_item_id = emit.inventory_item_id)
AND emit.selected_flag = 'Y'
AND emot.org_selected_flag = 'Y'
AND mp.organization_id = emot.organization_id_child
AND emot.batch_id = p_batch_id
AND emot.batch_id = emit.batch_id;
-- Can we avoid this select and directly use the seq value
-- If used directly then error 'Exact fetch return more than one row' occurs
-- because for each row the sequence value is being incremented
/* SELECT mtl_system_items_intf_sets_s.NEXTVAL
INTO l_batch_set_process_id
FROM dual;
UPDATE mtl_system_items_interface
SET set_process_id = l_batch_set_process_id
WHERE set_process_id = l_set_process_id
AND ROWNUM <= l_max_batch_size;
-- If no more rows are left to be updated then exit the loop
IF (SQL%ROWCOUNT = 0) THEN
Write_Debug('REQUEST HAS BEEN SUBMITTED FOR ALL BATCHES');
INSERT INTO mtl_item_revisions_interface
( inventory_item_id
, item_number -- added for Item-Org assignment across master orgs
, description -- added for Item-Org assignment across master orgs
, organization_id
, revision
, implementation_date
, effectivity_date
, transaction_id
, process_flag
, transaction_type
, set_process_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
)
SELECT
msii.inventory_item_id
, msii.item_number
, msii.description
, msii.organization_id
, mp.starting_revision
, SYSDATE
, SYSDATE
, MTL_SYSTEM_ITEMS_INTERFACE_S.nextval --- TRANSACTION_ID
, 1
, 'CREATE'
, l_set_process_id --l_batch_set_process_id
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, FND_GLOBAL.conc_request_id
, FND_GLOBAL.prog_appl_id
, FND_GLOBAL.conc_program_id
, SYSDATE
FROM mtl_system_items_interface msii,
mtl_parameters mp
WHERE msii.set_process_id = l_set_process_id
AND mp.organization_id = msii.organization_id;
DELETE ego_massupdate_item_tmp where to_date(to_char (creation_date,'hh24:mi:ss
dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy') < to_date(to_char (sysdate-hours/24,'hh24:mi:ss
dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy');
DELETE ego_massupdate_org_tmp where to_date(to_char (creation_date,'hh24:mi:ss
dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy') < to_date(to_char (sysdate-hours/24,'hh24:mi:ss
dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy');