[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Variable required for the validation for UPDATES */
/******************************************************/
loc_ctrl_code NUMBER;
select
ROWID,
ITEM_NUMBER,
TRANSACTION_ID,
ORGANIZATION_ID,
ORGANIZATION_CODE,
TRANSACTION_TYPE,
PROCESS_FLAG,
INVENTORY_ITEM_ID,
SUMMARY_FLAG,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
DESCRIPTION,
BUYER_ID,
ACCOUNTING_RULE_ID,
INVOICING_RULE_ID,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15,
SEGMENT16,
SEGMENT17,
SEGMENT18,
SEGMENT19,
SEGMENT20,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PURCHASING_ITEM_FLAG,
SHIPPABLE_ITEM_FLAG,
CUSTOMER_ORDER_FLAG,
INTERNAL_ORDER_FLAG,
-- SERVICE_ITEM_FLAG,
INVENTORY_ITEM_FLAG,
ENG_ITEM_FLAG,
INVENTORY_ASSET_FLAG,
PURCHASING_ENABLED_FLAG,
CUSTOMER_ORDER_ENABLED_FLAG,
INTERNAL_ORDER_ENABLED_FLAG,
SO_TRANSACTIONS_FLAG,
MTL_TRANSACTIONS_ENABLED_FLAG,
STOCK_ENABLED_FLAG,
BOM_ENABLED_FLAG,
BUILD_IN_WIP_FLAG,
REVISION_QTY_CONTROL_CODE,
ITEM_CATALOG_GROUP_ID,
CATALOG_STATUS_FLAG,
RETURNABLE_FLAG,
DEFAULT_SHIPPING_ORG,
COLLATERAL_FLAG,
TAXABLE_FLAG,
QTY_RCV_EXCEPTION_CODE,
ALLOW_ITEM_DESC_UPDATE_FLAG,
INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
MARKET_PRICE,
HAZARD_CLASS_ID,
RFQ_REQUIRED_FLAG,
QTY_RCV_TOLERANCE,
LIST_PRICE_PER_UNIT,
UN_NUMBER_ID,
PRICE_TOLERANCE_PERCENT,
ASSET_CATEGORY_ID,
ROUNDING_FACTOR,
UNIT_OF_ISSUE,
ENFORCE_SHIP_TO_LOCATION_CODE,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
ALLOW_UNORDERED_RECEIPTS_FLAG,
ALLOW_EXPRESS_DELIVERY_FLAG,
DAYS_EARLY_RECEIPT_ALLOWED,
DAYS_LATE_RECEIPT_ALLOWED,
RECEIPT_DAYS_EXCEPTION_CODE,
RECEIVING_ROUTING_ID,
INVOICE_CLOSE_TOLERANCE,
RECEIVE_CLOSE_TOLERANCE,
AUTO_LOT_ALPHA_PREFIX,
START_AUTO_LOT_NUMBER,
LOT_CONTROL_CODE,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
SERIAL_NUMBER_CONTROL_CODE,
START_AUTO_SERIAL_NUMBER,
AUTO_SERIAL_ALPHA_PREFIX,
SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
EXPENSE_ACCOUNT,
ENCUMBRANCE_ACCOUNT,
RESTRICT_SUBINVENTORIES_CODE,
UNIT_WEIGHT,
WEIGHT_UOM_CODE,
VOLUME_UOM_CODE,
UNIT_VOLUME,
RESTRICT_LOCATORS_CODE,
LOCATION_CONTROL_CODE,
SHRINKAGE_RATE,
ACCEPTABLE_EARLY_DAYS,
PLANNING_TIME_FENCE_CODE,
DEMAND_TIME_FENCE_CODE,
LEAD_TIME_LOT_SIZE,
STD_LOT_SIZE,
CUM_MANUFACTURING_LEAD_TIME,
OVERRUN_PERCENTAGE,
MRP_CALCULATE_ATP_FLAG,
ACCEPTABLE_RATE_INCREASE,
ACCEPTABLE_RATE_DECREASE,
CUMULATIVE_TOTAL_LEAD_TIME,
PLANNING_TIME_FENCE_DAYS,
DEMAND_TIME_FENCE_DAYS,
END_ASSEMBLY_PEGGING_FLAG,
REPETITIVE_PLANNING_FLAG,
PLANNING_EXCEPTION_SET,
BOM_ITEM_TYPE,
PICK_COMPONENTS_FLAG,
REPLENISH_TO_ORDER_FLAG,
BASE_ITEM_ID,
ATP_COMPONENTS_FLAG,
ATP_FLAG,
FIXED_LEAD_TIME,
VARIABLE_LEAD_TIME,
WIP_SUPPLY_LOCATOR_ID,
WIP_SUPPLY_TYPE,
WIP_SUPPLY_SUBINVENTORY,
PRIMARY_UOM_CODE,
PRIMARY_UNIT_OF_MEASURE,
ALLOWED_UNITS_LOOKUP_CODE,
COST_OF_SALES_ACCOUNT,
SALES_ACCOUNT,
DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
INVENTORY_ITEM_STATUS_CODE,
INVENTORY_PLANNING_CODE,
PLANNER_CODE,
PLANNING_MAKE_BUY_CODE,
FIXED_LOT_MULTIPLIER,
ROUNDING_CONTROL_TYPE,
CARRYING_COST,
POSTPROCESSING_LEAD_TIME,
PREPROCESSING_LEAD_TIME,
FULL_LEAD_TIME,
ORDER_COST,
MRP_SAFETY_STOCK_PERCENT,
MRP_SAFETY_STOCK_CODE,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
MINIMUM_ORDER_QUANTITY,
FIXED_ORDER_QUANTITY,
FIXED_DAYS_SUPPLY,
MAXIMUM_ORDER_QUANTITY,
ATP_RULE_ID,
PICKING_RULE_ID,
RESERVABLE_TYPE,
POSITIVE_MEASUREMENT_ERROR,
NEGATIVE_MEASUREMENT_ERROR,
ENGINEERING_ECN_CODE,
ENGINEERING_ITEM_ID,
ENGINEERING_DATE,
SERVICE_STARTING_DELAY,
-- VENDOR_WARRANTY_FLAG,
-- SERVICEABLE_COMPONENT_FLAG,
SERVICEABLE_PRODUCT_FLAG,
BASE_WARRANTY_SERVICE_ID,
PAYMENT_TERMS_ID,
PREVENTIVE_MAINTENANCE_FLAG,
PRIMARY_SPECIALIST_ID,
SECONDARY_SPECIALIST_ID,
SERVICEABLE_ITEM_CLASS_ID,
TIME_BILLABLE_FLAG,
MATERIAL_BILLABLE_FLAG,
EXPENSE_BILLABLE_FLAG,
PRORATE_SERVICE_FLAG,
COVERAGE_SCHEDULE_ID,
SERVICE_DURATION_PERIOD_CODE,
SERVICE_DURATION,
WARRANTY_VENDOR_ID,
MAX_WARRANTY_AMOUNT,
RESPONSE_TIME_PERIOD_CODE,
RESPONSE_TIME_VALUE,
NEW_REVISION_CODE,
INVOICEABLE_ITEM_FLAG,
TAX_CODE,
INVOICE_ENABLED_FLAG,
MUST_USE_APPROVED_VENDOR_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OUTSIDE_OPERATION_FLAG,
OUTSIDE_OPERATION_UOM_TYPE,
SAFETY_STOCK_BUCKET_DAYS,
AUTO_REDUCE_MPS,
COSTING_ENABLED_FLAG,
CYCLE_COUNT_ENABLED_FLAG,
AUTO_CREATED_CONFIG_FLAG,
ITEM_TYPE,
MODEL_CONFIG_CLAUSE_NAME,
SHIP_MODEL_COMPLETE_FLAG,
MRP_PLANNING_CODE,
RETURN_INSPECTION_REQUIREMENT,
ATO_FORECAST_CONTROL,
RELEASE_TIME_FENCE_CODE, /*NP 19AUG96 Eight new cols added for 10.7 */
RELEASE_TIME_FENCE_DAYS,
CONTAINER_ITEM_FLAG,
CONTAINER_TYPE_CODE,
INTERNAL_VOLUME,
MAXIMUM_LOAD_WEIGHT,
MINIMUM_FILL_PERCENT,
VEHICLE_ITEM_FLAG,
CHECK_SHORTAGES_FLAG, /*CK 21MAY98 Added new attribute*/
INDIVISIBLE_FLAG,
CONTRACT_ITEM_TYPE_CODE,
--Adding attributes now updateable for Pending items R12 C
DIMENSION_UOM_CODE,
UNIT_LENGTH,
UNIT_WIDTH,
UNIT_HEIGHT
from MTL_SYSTEM_ITEMS_INTERFACE
where ((organization_id = org_id) or -- fix for bug#8757041,removed + 0
(all_Org = 1))
and set_process_id = xset_id
and process_flag = 2;
SELECT
name
FROM hr_organization_units
WHERE organization_id = cp_org_id;
SELECT 1 FROM ego_import_option_sets
WHERE batch_id = cp_xset_id
AND enabled_for_data_pool = 'Y';
** inventory item id from the master, since we insert an item
** in master org
** in the first pass (before the insertion into the child org).
** However it performs ONE very important task:
** It allows you to insert an item in more than one MASTER org
** in the same FIRST pass
** and it ensures that they all get the same InvItemId
** (which has to be constant across ALL orgs)
** Following cursor now being replaced by Dyn SQL 2
** CURSOR dd1 (seg1 varchar2,seg2 varchar2,seg3 varchar2,
** blah blah...
** seg17 varchar2,seg18 varchar2,
** seg19 varchar2,seg20 varchar2,
** item_id_in number) is
** select inventory_item_id,
** transaction_id,
** organization_id
** from MTL_SYSTEM_ITEMS_INTERFACE
** where inventory_item_id <> item_id_in
** and set_process_id = nvl(xset_id, set_process_id)
** and nvl(segment1,'.') = nvl(seg1,'.')
** and nvl(segment2,'.') = nvl(seg2,'.')
** and so on so forth...
** and nvl(segment20,'.') = nvl(seg20,'.');
select max(FS.segment_num)
into g_max_segment
from FND_ID_FLEX_SEGMENTS FS
where FS.APPLICATION_ID = l_application_id
and FS.id_flex_code = l_id_flex_code
and FS.ENABLED_FLAG = l_enabled_flag
and FS.id_flex_num = l_id_flex_num;
select count(*) into g_totalsegs
from FND_ID_FLEX_SEGMENTS FS
where FS.APPLICATION_ID = l_application_id
and FS.id_flex_code = l_id_flex_code
and FS.ENABLED_FLAG = l_enabled_flag
and FS.id_flex_num = l_id_flex_num;
select concatenated_segment_delimiter
into g_segment_delimiter
from fnd_id_flex_structures
where id_flex_code = l_id_flex_code
and APPLICATION_ID = l_application_id
and ID_FLEX_NUM = l_id_flex_num;
/* NP 05SEP96 changed this select FS.segment_num
** to match the changes made in INVPUL1B.pls get_dynamic_sql_str
*/
select to_number(substr(FS.application_column_name, 8))
into segnum
from FND_ID_FLEX_SEGMENTS FS
where FS.SEGMENT_NUM = n
and FS.ID_FLEX_CODE = l_id_flex_code
and FS.ID_FLEX_NUM = l_id_flex_num
and FS.ENABLED_FLAG = l_enabled_flag
and FS.APPLICATION_ID = l_application_id;
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 INSTR(l_item_number,g_segment_delimiter,1,totalsegs -1)
INTO l_deliminator_count FROM DUAL;
select count(*)
into is_master_org
from mtl_parameters
where organization_id = cr.organization_id
and master_organization_id = cr.organization_id ;
select count(*)
into no_of_masterorgs
from mtl_parameters
where organization_id = master_organization_id ;
DSQL_statement1 := 'select distinct msi.inventory_item_id
from mtl_system_items msi,
mtl_parameters mp
where msi.organization_id <>
:organization_id_bind
and msi.inventory_item_id <> :l_item_id_bind
and msi.organization_id = mp.organization_id
and ' || statement_temp1;
**select distinct inventory_item_id
**into dup_item_id
**from mtl_system_items msi
**where
**msi.organization_id <> cr.organization_id
**and msi.inventory_item_id <> l_item_id
**and segment1 = nvl(cr.segment1,'.');
update mtl_system_items_interface
set inventory_item_id = dup_item_id
where transaction_id = cr.transaction_id
and set_process_id = xset_id;
DSQL_statement2 := ' select msii.inventory_item_id,
msii.transaction_id,
msii.organization_id
from mtl_system_items_interface msii,
mtl_parameters mp
where msii.inventory_item_id <>
:l_item_id_bind
and set_process_id = :xset_id_bind
and msii.organization_id = mp.organization_id
and ' || statement_temp2;
/* update item header with new item id
** This is for updating the III for similar
** segment items
** going to different master orgs
** Child items will not enter this NO DATA FOUND
** clause because
** the TWO_PASS design ensures that item WILL be
** found in masterorg in MSI*/
IF l_inv_debug_level IN(101, 102) THEN
INVPUTLI.info('INVPVHDR: Same item being added to MULTIPLE masters ');
update mtl_system_items_interface
set inventory_item_id = l_item_id
where transaction_id = DSQL2_transaction_id
and set_process_id = xset_id;
/* 09-APR-96 Added update to MIRI
** because if the inv_item_id is being
** changed in MSII
** it should also be changed in MIRI and MICI
** otherwise there will be dangling
** references that will
** be flagged as errors in mtl_interface_errors
** Also do so only where the org ids don't match
** since now the iii will be same we definitely
** don't want to do it for a record with same
** orgs: it will be a violation
** This violation WILL be caught elsewhere
** on checking that similar segs don't have
** same org in msii (duplicate record)
**
**Also,the following not needed since categories INSERT
**takes place much later..so no point in updating.
**update mtl_item_categories_interface
**set inventory_item_id = l_item_id
**where inventory_item_id = DSQL2_inventory_item_id;
** is updated correctly in MIRI. The <> is
** changed to equals.
*/
update mtl_item_revisions_interface
set inventory_item_id = l_item_id
where inventory_item_id = DSQL2_inventory_item_id
and organization_id = DSQL2_organization_id
-- and set_process_id + 0 = xset_id; -- fix for bug#8757041,removed + 0
select count(*)
into ext_flag
from MTL_SYSTEM_ITEMS_B
where inventory_item_id = l_item_id
and organization_id = cr.organization_id
and cr.transaction_type = 'CREATE';
select count(*)
into ext_flag
from MTL_SYSTEM_ITEMS_INTERFACE
where inventory_item_id = l_item_id
and organization_id = cr.organization_id
and process_flag = 2
and set_process_id = xset_id;
DSQL_statement3 := 'select count(*)
from MTL_SYSTEM_ITEMS msi
where organization_id = :organization_id_bind
and :transaction_type_bind = ''CREATE''
and ' || statement_temp3;
**select count(*)
**into ext_flag
**from MTL_SYSTEM_ITEMS_INTERFACE msii
**where organization_id = cr.organization_id
**and nvl(msii.segment1, '.') = nvl(cr.segment1,'.');
--DSQL_statement4 := 'select count(*)
DSQL_statement4 := 'select 1
from MTL_SYSTEM_ITEMS_INTERFACE msii
where organization_id = :organization_id_bind
and set_process_id = :xset_id_bind
and process_flag = 2
and ' || statement_temp4;
select lot_number_generation into lot_num_generation_val
from mtl_parameters
where organization_id = cr.organization_id
and rownum =1; /*NP 21DEC94 */
select lot_number_generation into lot_num_generation_val
from mtl_parameters
where organization_id = cr.organization_id
and rownum =1; /*NP 21DEC94 */
SELECT TO_CHAR(TO_NUMBER(cr.START_AUTO_LOT_NUMBER))
INTO l_start_auto_lot_num
FROM DUAL;
select serial_number_generation into ser_num_generation_val
from mtl_parameters
where organization_id = cr.organization_id
and rownum =1; /*NP 21DEC94 */
select serial_number_generation into ser_num_generation_val
from mtl_parameters
where organization_id = cr.organization_id
and rownum =1; /*NP 21DEC94 */
select 'item_in_source_org'
into l_dummy
from mtl_system_items_b
where inventory_item_id = l_item_id
and organization_id = cr.source_organization_id;
Select 'inter-org network defined'
into l_dummy
from mtl_interorg_parameters
where to_organization_id = cr.organization_id
and from_organization_id = cr.source_organization_id;
select 'nettable or null source sub'
into l_dummy
from mtl_secondary_inventories
where secondary_inventory_name =
nvl(cr.source_subinventory,
secondary_inventory_name)
and availability_type = 1
and rownum < 2 ;
/* R12 C Unit Weight can now be updated for Pending items. Moving the below set of validations to INVPVHDR */
IF cr.WEIGHT_UOM_CODE IS NOT NULL THEN
BEGIN
SELECT 'x' INTO temp
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = cr.WEIGHT_UOM_CODE;
/* NP26DEC94 : New code to update process_flag.
** This modified/new 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 41 if all okay and 31 if some validation failed in this procedure
** The process flag values that are possible at this time are
** 2 set by the previous procedure.
** Since this is the first validation procedure..the logic is a little different
** from the 5 other validation procedures in files
** INVPVDR2.sql thru INVPVDR6.sql
*/
--R12C WHERE clause changed to ROWID
update MTL_SYSTEM_ITEMS_INTERFACE
set process_flag = DECODE(status,0,41,31),
PRIMARY_UOM_CODE = cr.primary_uom_code,
primary_unit_of_measure = cr.primary_unit_of_measure
where rowid = cr.rowid;