[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select intf.ROWID, intf.*
from MTL_SYSTEM_ITEMS_INTERFACE intf
where ((organization_id + 0 = org_id) or
(all_Org = 1))
and set_process_id = xset_id
and process_flag in (31, 32, 33, 34, 35, 36, 46);
SELECT stock_locator_control_code,
primary_cost_method,
NVL(wms_enabled_flag,'N'),
NVL(process_enabled_flag,'N'),
NVL(eam_enabled_flag,'N'),
NVL(trading_partner_org_flag,'N')
FROM mtl_parameters
where organization_id = cp_org_id;
SELECT locator_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = cp_subinv_name
AND organization_id = cp_org_id
AND SYSDATE < nvl(disable_date, SYSDATE+1);
select inventory_item_id
into l_item_id
from mtl_system_items_interface
where transaction_id = cr.transaction_id
and set_process_id = xset_id; */
select 'x' into l_temp
from MTL_GRADES_B
where GRADE_CODE = cr.DEFAULT_GRADE
and nvl(DISABLE_FLAG,'N') <> 'Y';
select lot_number_generation
into lot_num_generation_val
from mtl_parameters
where organization_id = cr.organization_id
and rownum =1;
select 'x' into l_temp
from FND_LOOKUP_VALUES_VL
where LOOKUP_TYPE = 'INV_PARENT_CHILD_GENERATION'
and LOOKUP_CODE = cr.PARENT_CHILD_GENERATION_FLAG
and SYSDATE between
NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
and ENABLED_FLAG = 'Y';
SELECT TO_CHAR(TO_NUMBER(cr.CHILD_LOT_STARTING_NUMBER))
INTO l_child_lot_starting_number
FROM DUAL;
elsif cr.RESTRICT_SUBINVENTORIES_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' then
begin
select 'x' INTO l_temp
from MTL_ITEM_SUB_INVENTORIES i
where i.inventory_item_id = cr.inventory_item_id
and i.ORGANIZATION_ID = cr.ORGANIZATION_ID
and i.SECONDARY_INVENTORY = cr.PROCESS_SUPPLY_SUBINVENTORY;
select 'x' INTO l_temp
from MTL_SECONDARY_INVENTORIES
where SECONDARY_INVENTORY_NAME = cr.PROCESS_SUPPLY_SUBINVENTORY
and ORGANIZATION_ID = cr.ORGANIZATION_ID
and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1);
elsif cr.RESTRICT_LOCATORS_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' then
BEGIN
select 'x' INTO l_temp
from MTL_SECONDARY_LOCATORS
where INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
and ORGANIZATION_ID = cr.ORGANIZATION_ID
and SECONDARY_LOCATOR = cr.PROCESS_SUPPLY_LOCATOR_ID
and SUBINVENTORY_CODE = cr.PROCESS_SUPPLY_SUBINVENTORY;
select 'x' INTO l_temp
from MTL_ITEM_LOCATIONS
where INVENTORY_LOCATION_ID = cr.PROCESS_SUPPLY_LOCATOR_ID
and SUBINVENTORY_CODE = cr.PROCESS_SUPPLY_SUBINVENTORY
and ORGANIZATION_ID = cr.ORGANIZATION_ID
and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
elsif cr.RESTRICT_SUBINVENTORIES_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' then
begin
select 'x' INTO l_temp
from MTL_ITEM_SUB_INVENTORIES i
where i.inventory_item_id = cr.inventory_item_id
and i.ORGANIZATION_ID = cr.ORGANIZATION_ID
and i.SECONDARY_INVENTORY = cr.PROCESS_YIELD_SUBINVENTORY;
select 'x' INTO l_temp
from MTL_SECONDARY_INVENTORIES
where SECONDARY_INVENTORY_NAME = cr.PROCESS_YIELD_SUBINVENTORY
and ORGANIZATION_ID = cr.ORGANIZATION_ID
and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1);
elsif cr.RESTRICT_LOCATORS_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' then
BEGIN
select 'x' INTO l_temp
from MTL_SECONDARY_LOCATORS
where INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
and ORGANIZATION_ID = cr.ORGANIZATION_ID
and SECONDARY_LOCATOR = cr.PROCESS_YIELD_LOCATOR_ID
and SUBINVENTORY_CODE = cr.PROCESS_YIELD_SUBINVENTORY;
select 'x' INTO l_temp
from MTL_ITEM_LOCATIONS
where INVENTORY_LOCATION_ID = cr.PROCESS_YIELD_LOCATOR_ID
and SUBINVENTORY_CODE = cr.PROCESS_YIELD_SUBINVENTORY
and ORGANIZATION_ID = cr.ORGANIZATION_ID
and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
select 'x' into l_temp
from mtl_actions_b
where action_code = cr.EXPIRATION_ACTION_CODE
and disable_flag= 'N';
SELECT 'x' INTO l_temp
FROM MTL_UOM_CONVERSIONS
WHERE UOM_CLASS = l_charge_periodicity_class
AND UOM_CODE = cr.CHARGE_PERIODICITY_CODE;
/* New code to update process_flag.
** This code necessiated due to the breaking up INVPVHDR into
** 6 smaller packages to overcome PL/SQL limitations
** with code size.
** Let's update the process flag for the record
** Give it value 42 if all okay and 32 if some
** validation failed in this procedure
** Need to do this ONLY if all previous validation okay.
** The process flag values that are possible at this time are
** 31 :set by INVPVHDR
** 32 :set by INVPVDR2
** 33 :set by INVPVDR3
** 34 :set by INVPVDR4
** 35, 45 :set by INVPVDR5
** 36, 46 :set by INVPVDR7
*/
/* Bug 4705184
select process_flag into temp_proc_flag
from MTL_SYSTEM_ITEMS_INTERFACE
where inventory_item_id = l_item_id
and set_process_id + 0 = xset_id
and process_flag in (3,31,32,33,34,35,36,46) --3571136
and organization_id = cr.organization_id
and rownum < 2; */
update MTL_SYSTEM_ITEMS_INTERFACE
set process_flag = DECODE(status,0,4,3),
PRIMARY_UOM_CODE = cr.primary_uom_code,
primary_unit_of_measure = cr.primary_unit_of_measure
where inventory_item_id = l_item_id
and set_process_id + 0 = xset_id
and process_flag = 46
and organization_id = cr.organization_id;
update MTL_ITEM_CATEGORIES_INTERFACE
set process_flag = DECODE(status,0,4,3)
where process_flag = 4
and set_process_id = xset_id
and inventory_item_id = l_item_id
and organization_id = cr.organization_id;
update MTL_SYSTEM_ITEMS_INTERFACE
set process_flag = 3,
PRIMARY_UOM_CODE = cr.primary_uom_code,
primary_unit_of_measure = cr.primary_unit_of_measure,
request_id = reqst_id,
program_application_id = nvl(program_application_id,prog_appid)
,
PROGRAM_ID = nvl(PROGRAM_ID,prog_id),
PROGRAM_UPDATE_DATE = nvl(PROGRAM_UPDATE_DATE,sysdate),
LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,sysdate),
LAST_UPDATED_BY = nvl(LAST_UPDATED_BY,user_id),
CREATION_DATE = nvl(CREATION_DATE,sysdate),
CREATED_BY = nvl(CREATED_BY,user_id),
LAST_UPDATE_LOGIN = nvl(LAST_UPDATE_LOGIN,login_id)
where inventory_item_id = l_item_id
and set_process_id + 0 = xset_id
and process_flag in (31,32,33,34,35,36)
and organization_id = cr.organization_id;
update MTL_ITEM_CATEGORIES_INTERFACE
set process_flag = l_process_flag_3,
request_id = reqst_id,
program_application_id = nvl(program_application_id,prog_appid)
,
PROGRAM_ID = nvl(PROGRAM_ID,prog_id),
PROGRAM_UPDATE_DATE = nvl(PROGRAM_UPDATE_DATE,sysdate),
LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,sysdate),
LAST_UPDATED_BY = nvl(LAST_UPDATED_BY,user_id),
CREATION_DATE = nvl(CREATION_DATE,sysdate),
CREATED_BY = nvl(CREATED_BY,user_id),
LAST_UPDATE_LOGIN = nvl(LAST_UPDATE_LOGIN,login_id)
where process_flag = l_process_flag_4
and set_process_id = xset_id
and inventory_item_id = l_item_id
and organization_id = cr.organization_id;