[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION assign_item_rev_data_update(
org_id NUMBER,
all_org NUMBER := 2,
prog_appid NUMBER := -1,
prog_id NUMBER := -1,
request_id NUMBER := -1,
user_id NUMBER := -1,
login_id NUMBER := -1,
err_text IN OUT NOCOPY VARCHAR2,
xset_id IN NUMBER DEFAULT -999)
RETURN NUMBER;
FUNCTION mtl_pr_assign_item_data_update(
org_id NUMBER,
all_org NUMBER := 2,
prog_appid NUMBER := -1,
prog_id NUMBER := -1,
request_id NUMBER := -1,
user_id NUMBER := -1,
login_id NUMBER := -1,
err_text IN OUT NOCOPY VARCHAR2,
xset_id IN NUMBER DEFAULT -999) RETURN INTEGER IS
CURSOR C_msii_records IS
SELECT ROWID, intf.*
FROM mtl_system_items_interface intf
WHERE intf.process_flag = 1
AND intf.set_process_id = xset_id
AND ((intf.organization_id = org_id) or (all_org = 1));
SELECT status.attribute_name
,status.attribute_value
FROM mtl_status_attribute_values status
,mtl_item_attributes ctrl
WHERE status.attribute_name = ctrl.attribute_name
AND status.inventory_item_status_code = cp_item_status_code
AND ctrl.status_control_code = 1;
SELECT Primary_Unit_Of_Measure, ROWID
FROM Mtl_System_Items_Interface
WHERE Process_Flag = 1
AND Set_Process_Id = xset_id
AND ((organization_id = org_id) or (all_org = 1))
FOR UPDATE OF Primary_Uom_Code;
INVPUTLI.info('INVUPD1B.mtl_pr_assign_item_data_update: begin org_id=' || TO_CHAR(org_id));
SELECT Uom_Code
INTO temp_uom_code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = puom.Primary_Unit_Of_Measure --Bug 5192495
AND SYSDATE < nvl(Disable_Date, SYSDATE+1);
UPDATE Mtl_System_Items_Interface
SET Primary_Uom_Code = temp_uom_code
WHERE Rowid = puom.Rowid;
SELECT msi.primary_unit_of_measure,
msi.eng_item_flag,
msi.primary_uom_code, --* Added for Bug 4366615
msi.inventory_item_status_code,
msi.tracking_quantity_ind,
msi.secondary_uom_code,
msi.secondary_default_ind,
msi.ont_pricing_qty_source,
msi.dual_uom_deviation_high,
msi.dual_uom_deviation_low
INTO msi_primary_uom,
msi_eng_item_flag,
msi_primary_uom_code, --* Added for Bug 4366615
msi_inventory_item_status_code,
msi_tracking_quantity_ind,
msi_secondary_uom_code,
msi_secondary_default_ind,
msi_ont_pricing_qty_source,
msi_dual_uom_deviation_high,
msi_dual_uom_deviation_low
FROM mtl_system_items_B msi
WHERE msi.inventory_item_id = t_inventory_item_id
AND msi.organization_id = t_organization_id;
-- if any of the non-updateable fields are being updated [ENG_ITEM_FLAG and PRIMARY_UOM]
IF (rec.MATERIAL_COST IS NULL
AND rec.MATERIAL_SUB_ELEM IS NULL
AND rec.MATERIAL_OH_RATE IS NULL
AND rec.MATERIAL_OH_SUB_ELEM IS NULL
AND rec.MATERIAL_SUB_ELEM_ID IS NULL
AND rec.MATERIAL_OH_SUB_ELEM_ID IS NULL)
THEN
IF (rec.ENG_ITEM_FLAG IS NULL OR rec.ENG_ITEM_FLAG = msi_eng_item_flag)
AND (rec.PRIMARY_UOM_CODE IS NULL OR rec.primary_uom_code = msi_primary_uom_code)
--Jalaj Srivastava Bug 5017588
--all uom fields are non updateable
AND (rec.tracking_quantity_ind IS NULL OR rec.tracking_quantity_ind = msi_tracking_quantity_ind )
AND (rec.secondary_uom_code IS NULL OR rec.secondary_uom_code = msi_secondary_uom_code )
AND (rec.secondary_default_ind IS NULL OR rec.secondary_default_ind = msi_secondary_default_ind )
AND (rec.ont_pricing_qty_source IS NULL OR rec.ont_pricing_qty_source = msi_ont_pricing_qty_source )
AND (rec.dual_uom_deviation_high IS NULL OR rec.dual_uom_deviation_high = msi_dual_uom_deviation_high )
AND (rec.dual_uom_deviation_low IS NULL OR rec.dual_uom_deviation_low = msi_dual_uom_deviation_low )
THEN
status_code := 1;
/* Bug 4751471 Status Controlled attrs are relevantly updated when status is applied. Eliminating this chk
BEGIN -- {
-- if status controlled attributes are not being modified
IF NOT (rec.STOCK_ENABLED_FLAG IS NULL
AND rec.MTL_TRANSACTIONS_ENABLED_FLAG IS NULL
AND rec.PURCHASING_ENABLED_FLAG IS NULL
AND rec.BUILD_IN_WIP_FLAG IS NULL
AND rec.CUSTOMER_ORDER_ENABLED_FLAG IS NULL
AND rec.INTERNAL_ORDER_ENABLED_FLAG IS NULL
AND rec.BOM_ENABLED_FLAG IS NULL
AND rec.INVOICE_ENABLED_FLAG IS NULL
AND rec.RECIPE_ENABLED_FLAG IS NULL
AND rec.PROCESS_EXECUTION_ENABLED_FLAG IS NULL)
THEN -- {
-- Start 2913856 Changed is not null to <>
--4751471 : Status attributes under sets control
--Values passed in the interface table should be compared against status setup not against msb db value.
FOR srec IN C_status_controlled_attr(nvl(rec.inventory_item_status_code,msi_inventory_item_status_code)) LOOP -- {
IF((srec.attribute_name = 'MTL_SYSTEM_ITEMS.STOCK_ENABLED_FLAG'
AND NVL(rec.STOCK_ENABLED_FLAG,srec.attribute_value) <> srec.attribute_value)
OR (srec.attribute_name = 'MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG'
AND NVL(rec.MTL_TRANSACTIONS_ENABLED_FLAG,srec.attribute_value) <> srec.attribute_value)
OR (srec.attribute_name = 'MTL_SYSTEM_ITEMS.PURCHASING_ENABLED_FLAG'
AND NVL(rec.PURCHASING_ENABLED_FLAG,srec.attribute_value) <> srec.attribute_value)
OR (srec.attribute_name = 'MTL_SYSTEM_ITEMS.BUILD_IN_WIP_FLAG'
AND NVL(rec.BUILD_IN_WIP_FLAG,srec.attribute_value) <> srec.attribute_value)
OR (srec.attribute_name = 'MTL_SYSTEM_ITEMS.CUSTOMER_ORDER_ENABLED_FLAG'
AND NVL(rec.CUSTOMER_ORDER_ENABLED_FLAG,srec.attribute_value) <> srec.attribute_value)
OR (srec.attribute_name = 'MTL_SYSTEM_ITEMS.INTERNAL_ORDER_ENABLED_FLAG'
AND NVL(rec.INTERNAL_ORDER_ENABLED_FLAG,srec.attribute_value) <> srec.attribute_value)
OR (srec.attribute_name = 'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG'
AND NVL(rec.BOM_ENABLED_FLAG,srec.attribute_value) <> srec.attribute_value)
OR (srec.attribute_name = 'MTL_SYSTEM_ITEMS.INVOICE_ENABLED_FLAG'
AND NVL(rec.INVOICE_ENABLED_FLAG,srec.attribute_value) <> srec.attribute_value)
OR (srec.attribute_name = 'MTL_SYSTEM_ITEMS.RECIPE_ENABLED_FLAG'
AND NVL(rec.RECIPE_ENABLED_FLAG,srec.attribute_value) <> srec.attribute_value)
OR (srec.attribute_name = 'MTL_SYSTEM_ITEMS.PROCESS_EXECUTION_ENABLED_FLAG'
AND NVL(rec.PROCESS_EXECUTION_ENABLED_FLAG,srec.attribute_value) <> srec.attribute_value))
THEN -- {
status_code := 0;
-- check if an attribute that should not be updated if onhand qties exist is being updated
IF NOT (rec.LOCATION_CONTROL_CODE IS NULL
AND rec.LOT_CONTROL_CODE IS NULL
AND rec.REVISION_QTY_CONTROL_CODE IS NULL
AND rec.SERIAL_NUMBER_CONTROL_CODE IS NULL
AND rec.SHELF_LIFE_CODE IS NULL
AND rec.COSTING_ENABLED_FLAG IS NULL
AND rec.INVENTORY_ASSET_FLAG IS NULL )
THEN -- {
-- if onhand quantities exist or transactions pending then
IF (INVUPD1B.exists_onhand_quantities(t_organization_id, t_inventory_item_id) <> 1
AND INVUPD1B.exists_onhand_child_qties(t_organization_id, t_inventory_item_id) <> 1 )
THEN -- {
-- copy msi data to msii record for ``missing'' attributes, also set process_ flag = 2
dumm_status := INVUPD1B.copy_msi_to_msii(rec.rowid,t_organization_id, t_inventory_item_id);
-- Check for dependencies of attributes being updated
upd_status := INVUPD1B.mtl_validate_attr_upd(
rec.organization_id,
rec.inventory_item_id,
rec.rowid,
attr_err_mesg_name);
-- non-updateable attribute is being updated.
upd_status := INVPUOPI.mtl_log_interface_err(
t_organization_id,
user_id,
login_id,
prog_appid,
prog_id,
request_id,
t_trans_id,
error_text,
null,
'MTL_SYSTEM_ITEMS_INTERFACE',
attr_err_mesg_name,
err_text);
/* Bug 4751471 - Status Controlled attrs are relevantly updated when status is applied. Eliminating chk
ELSE -- } {
-- flag error that status controlled attribute is being modified and move to next record
dumm_status := INVPUOPI.mtl_log_interface_err(
t_organization_id,
user_id,
login_id,
prog_appid,
prog_id,
request_id,
t_trans_id,
error_text,
null,
'MTL_SYSTEM_ITEMS_INTERFACE',
'INV_STATUS_CNTRL_ATTRIB_ERROR',
err_text);
-- flag error that non-updateable fields are being updated and move to next record
dumm_status := INVPUOPI.mtl_log_interface_err(
t_organization_id,
user_id,
login_id,
prog_appid,
prog_id,
request_id,
t_trans_id,
error_text,
null,
'MTL_SYSTEM_ITEMS_INTERFACE',
'INV_NON_UPDATE_ATTRIBUTE_ERROR',
err_text);
-- flag error that costing related fields are being updated and move to next record
dumm_status := INVPUOPI.mtl_log_interface_err(
t_organization_id,
user_id,
login_id,
prog_appid,
prog_id,
request_id,
t_trans_id,
error_text,
null,
'MTL_SYSTEM_ITEMS_INTERFACE',
'INV_NON_UPDATE_ATTR_ERROR2',
err_text);
rtn_status := assign_item_rev_data_update(
org_id => org_id
,all_org => all_org
,prog_appid => prog_appid
,prog_id => prog_id
,request_id => request_id
,user_id => user_id
,login_id => login_id
,err_text => err_text
,xset_id => xset_id);
INVPUTLI.info('INVUPD1B.mtl_pr_assign_item_data_update: when OTHERS exception');
END mtl_pr_assign_item_data_update; -- }
select LOCATION_CONTROL_CODE,
LOT_CONTROL_CODE,
REVISION_QTY_CONTROL_CODE,
SERIAL_NUMBER_CONTROL_CODE,
SHELF_LIFE_CODE,
COSTING_ENABLED_FLAG,
INVENTORY_ASSET_FLAG,
RESTRICT_LOCATORS_CODE,
AUTO_SERIAL_ALPHA_PREFIX,
START_AUTO_SERIAL_NUMBER,
/* Start Bug 3713912 */
SECONDARY_UOM_CODE ,
TRACKING_QUANTITY_IND,
SECONDARY_DEFAULT_IND,
DUAL_UOM_DEVIATION_HIGH,
DUAL_UOM_DEVIATION_LOW
/* End Bug 3713912 */
from MTL_SYSTEM_ITEMS_INTERFACE
where rowid = row_id;
select LOCATION_CONTROL_CODE,
LOT_CONTROL_CODE,
REVISION_QTY_CONTROL_CODE,
SERIAL_NUMBER_CONTROL_CODE,
SHELF_LIFE_CODE,
COSTING_ENABLED_FLAG,
INVENTORY_ASSET_FLAG,
RESTRICT_LOCATORS_CODE,
AUTO_SERIAL_ALPHA_PREFIX, -- Bug #1402402
START_AUTO_SERIAL_NUMBER, -- Bug #1402402
/* Start Bug 3713912 */
SECONDARY_UOM_CODE ,
TRACKING_QUANTITY_IND,
SECONDARY_DEFAULT_IND,
DUAL_UOM_DEVIATION_HIGH,
DUAL_UOM_DEVIATION_LOW
/* End Bug 3713912 */
from MTL_SYSTEM_ITEMS
where inventory_item_id = item_id
and organization_id = org_id;
select
ROWID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
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,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
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,
PURCHASING_TAX_CODE,
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,
DEMAND_SOURCE_LINE,
COPY_ITEM_ID,
SET_ID,
REVISION,
AUTO_CREATED_CONFIG_FLAG,
ITEM_TYPE,
MODEL_CONFIG_CLAUSE_NAME,
SHIP_MODEL_COMPLETE_FLAG,
MRP_PLANNING_CODE,
RETURN_INSPECTION_REQUIREMENT,
DEMAND_SOURCE_TYPE,
DEMAND_SOURCE_HEADER_ID,
TRANSACTION_ID,
PROCESS_FLAG,
ORGANIZATION_CODE,
ITEM_NUMBER,
COPY_ITEM_NUMBER,
TEMPLATE_ID,
TEMPLATE_NAME,
COPY_ORGANIZATION_ID,
COPY_ORGANIZATION_CODE,
ATO_FORECAST_CONTROL,
TRANSACTION_TYPE,
MATERIAL_COST,
MATERIAL_SUB_ELEM,
MATERIAL_OH_RATE,
MATERIAL_OH_SUB_ELEM,
MATERIAL_SUB_ELEM_ID,
MATERIAL_OH_SUB_ELEM_ID,
RELEASE_TIME_FENCE_CODE,
RELEASE_TIME_FENCE_DAYS,
CONTAINER_ITEM_FLAG,
VEHICLE_ITEM_FLAG,
MAXIMUM_LOAD_WEIGHT,
MINIMUM_FILL_PERCENT,
CONTAINER_TYPE_CODE,
INTERNAL_VOLUME,
SET_PROCESS_ID
from MTL_SYSTEM_ITEMS_INTERFACE
where process_flag = 1
and set_process_id = xset_id
and ((organization_id = org_id) or (all_org = 1));
select process_flag, organization_id, transaction_id
from MTL_SYSTEM_ITEMS_INTERFACE MSII
where MSII.inventory_item_id is not NULL
and MSII.set_process_id = xset_id
and MSII.process_flag = 1
and not exists
(select inventory_item_id
from MTL_SYSTEM_ITEMS_B MSI
where MSII.inventory_item_id = MSI.inventory_item_id
and MSII.organization_id = MSI.organization_id)
for update;
update MTL_SYSTEM_ITEMS_INTERFACE
set process_flag = l_process_flag_3
where current of C_inv_item_id_records;
DSQL_statement := 'select msi.inventory_item_id
from mtl_system_items_B msi,
mtl_system_items_interface msii
where msii.rowid = :row_id_bind
AND msi.organization_id = msii.organization_id
and ' || statement_temp;
update MTL_SYSTEM_ITEMS_INTERFACE
set inventory_item_id = DSQL_inventory_item_id
where rowid = row_id;
select organization_id
into tmp_orgid
from MTL_SYSTEM_ITEMS_B
where organization_id = org_id
and inventory_item_id = inv_item_id;
select organization_id
into tmp_org_id
from MTL_ONHAND_QUANTITIES_DETAIL MOQ -- Bug:2687570
where MOQ.ORGANIZATION_ID = org_id
AND MOQ.INVENTORY_ITEM_ID = inv_item_id;
select
MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID
from MTL_SYSTEM_ITEMS_B MSI, MTL_PARAMETERS MP
where MP.master_organization_id = org_id
and MP.organization_id = MSI.organization_id
and MSI.inventory_item_id = inv_item_id
and MSI.organization_id <> MP.master_organization_id;
select organization_id
into tmp_org_id
from MTL_PARAMETERS MP
where MP.organization_id = org_id
AND MP.master_organization_id = org_id;
SELECT default_value FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE application_id = 431
AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_MASTER_ITEMS'
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Main'
AND END_USER_COLUMN_NAME = 'Trade_Item_Descriptor';
SELECT * INTO msi_record_temp
FROM MTL_SYSTEM_ITEMS_VL MSI
WHERE MSI.organization_id = org_id
AND MSI.inventory_item_id = inv_item_id;
SELECT * INTO msii_temp_data
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE MSII.ROWID = ROW_ID;
IF trim(msii_temp_data.ALLOW_ITEM_DESC_UPDATE_FLAG) is null then
msii_temp_data.ALLOW_ITEM_DESC_UPDATE_FLAG := msi_record_temp.ALLOW_ITEM_DESC_UPDATE_FLAG;
msii_temp_data.ALLOW_ITEM_DESC_UPDATE_FLAG := trim(msii_temp_data.ALLOW_ITEM_DESC_UPDATE_FLAG);
IF msii_temp_data.PROGRAM_UPDATE_DATE is null then
msii_temp_data.PROGRAM_UPDATE_DATE := msi_record_temp.PROGRAM_UPDATE_DATE;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE SET ROW = msii_temp_data WHERE ROWID = row_id;
FUNCTION mtl_pr_validate_item_update
(
org_id NUMBER,
all_org NUMBER := 2,
prog_appid NUMBER := -1,
prog_id NUMBER := -1,
request_id NUMBER := -1,
user_id NUMBER := -1,
login_id NUMBER := -1,
err_text IN OUT NOCOPY VARCHAR2,
xset_id IN NUMBER DEFAULT NULL
)
return INTEGER
IS
ret_code_create NUMBER := 1;
INVPUTLI.info('INVUPD1B: Inside mtl_pr_validate_item_update'|| '***orgid: ' || TO_CHAR(org_id));
ret_code_master := INVUPD2B.validate_item_update_master(
org_id,
all_org,
prog_appid,
prog_id,
request_id,
user_id,
login_id,
err_text,
xset_id);
ret_code_child := INVUPD2B.validate_item_update_child(
org_id,
all_org,
prog_appid,
prog_id,
request_id,
user_id,
login_id,
err_text,
xset_id);
INVPUTLI.info('when OTHERS exception raised in mtl_pr_validate_item_update' || SQLERRM);
END mtl_pr_validate_item_update; -- }
FUNCTION assign_item_rev_data_update(
org_id NUMBER,
all_org NUMBER := 2,
prog_appid NUMBER := -1,
prog_id NUMBER := -1,
request_id NUMBER := -1,
user_id NUMBER := -1,
login_id NUMBER := -1,
err_text IN OUT NOCOPY VARCHAR2,
xset_id IN NUMBER DEFAULT -999)
RETURN NUMBER IS
--Fill Item Id for Item Number
CURSOR c_fill_Item_Id IS
SELECT DISTINCT item_number,
organization_id
FROM mtl_item_revisions_interface
WHERE inventory_item_id IS NULL
AND item_number IS NOT NULL
AND organization_id IS NOT NULL
AND set_process_id = xset_id
AND process_flag = 1;
SELECT distinct inventory_item_id,
organization_id,
transaction_type
FROM mtl_item_revisions_interface
WHERE process_flag = 1
AND set_process_id = xset_id
AND transaction_id IS NULL --Bug: 3019435 Added condition
AND (organization_id = org_id or all_org = 1);
SELECT rowid
,revision
,revision_id
,description
,inventory_item_id
,organization_id
,change_notice
,ecn_initiation_date
,implementation_date
,implemented_serial_number
,effectivity_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,revision_label
,revision_reason
,transaction_id
FROM mtl_item_revisions_interface
WHERE process_flag = 1
AND set_process_id = xset_id
AND (organization_id = org_id or all_org = 1);
SELECT revision_id
,description
,change_notice
,ecn_initiation_date
,implementation_date
--,implemented_serial_number
,effectivity_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,revision_label
,revision_reason
,revised_item_sequence_id
,lifecycle_id
,current_phase_id
FROM mtl_item_revisions
WHERE organization_id = cp_org_id
AND inventory_item_id = cp_item_id
AND revision = cp_revision;
UPDATE mtl_item_revisions_interface i
SET i.organization_id = (SELECT o.organization_id
FROM mtl_parameters o
WHERE o.organization_code = i.organization_code)
WHERE i.organization_id is NULL
AND set_process_id = xset_id
AND i.process_flag = l_process_flag_1;
UPDATE mtl_item_revisions_interface
SET process_flag = l_process_flag_3,
transaction_id = NVL(transaction_id,MTL_SYSTEM_ITEMS_INTERFACE_S.nextval)
WHERE item_number = cr.item_number
AND inventory_item_id IS NULL
AND process_flag = l_process_flag_1
AND set_process_id = xset_id
AND organization_id = cr.organization_id
RETURNING transaction_id INTO tran_id;
SELECT MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
INTO tran_id
FROM dual;
/* UPDATE mtl_item_revisions_interface
SET process_flag = l_process_flag_3,
transaction_id = tran_id
WHERE item_number = cr.item_number
AND inventory_item_id IS NULL
AND process_flag = l_process_flag_1
AND set_process_id = xset_id
AND organization_id = cr.organization_id;
UPDATE mtl_item_revisions_interface
SET inventory_item_id = flex_id
WHERE item_number = cr.item_number
AND set_process_id = xset_id
AND organization_id = cr.organization_id;
SELECT MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
INTO tran_id FROM dual;
UPDATE mtl_item_revisions_interface
SET transaction_id = tran_id
WHERE inventory_item_id = cr.inventory_item_id
AND organization_id = cr.organization_id
AND transaction_type = cr.transaction_type
AND set_process_id + 0 = xset_id
AND process_flag = l_process_flag_1;
SELECT starting_revision
INTO l_default_revision
FROM mtl_parameters
WHERE organization_id = cr.organization_id;
UPDATE mtl_item_revisions_interface
SET effectivity_date = l_rev_rec.effectivity_date
WHERE rowid = cr.rowid;
UPDATE mtl_item_revisions_interface
SET revision_id = l_rev_rec.revision_id
,description = decode(description,NULL,l_rev_rec.description,'!',NULL,g_FND_Upd_Null_Char,NULL,description)
,change_notice = l_rev_rec.change_notice
,ecn_initiation_date = l_rev_rec.ecn_initiation_date
,implementation_date = NVL(implementation_date,l_rev_rec.implementation_date)
--,implemented_serial_number = NVL(implemented_serial_number,l_rev_rec.implemented_serial_number)
,effectivity_date = NVL(effectivity_date,l_rev_rec.effectivity_date)
,attribute_category = decode(attribute_category,NULL,l_rev_rec.attribute_category,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute_category)
,attribute1 = decode(attribute1,NULL,l_rev_rec.attribute1,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute1)
,attribute2 = decode(attribute2,NULL,l_rev_rec.attribute2,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute2)
,attribute3 = decode(attribute3,NULL,l_rev_rec.attribute3,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute3)
,attribute4 = decode(attribute4,NULL,l_rev_rec.attribute4,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute4)
,attribute5 = decode(attribute5,NULL,l_rev_rec.attribute5,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute5)
,attribute6 = decode(attribute6,NULL,l_rev_rec.attribute6,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute6)
,attribute7 = decode(attribute7,NULL,l_rev_rec.attribute7,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute7)
,attribute8 = decode(attribute8,NULL,l_rev_rec.attribute8,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute8)
,attribute9 = decode(attribute9,NULL,l_rev_rec.attribute9,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute9)
,attribute10 = decode(attribute10,NULL,l_rev_rec.attribute10,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute10)
,attribute11 = decode(attribute11,NULL,l_rev_rec.attribute11,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute11)
,attribute12 = decode(attribute12,NULL,l_rev_rec.attribute12,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute12)
,attribute13 = decode(attribute13,NULL,l_rev_rec.attribute13,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute13)
,attribute14 = decode(attribute14,NULL,l_rev_rec.attribute14,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute14)
,attribute15 = decode(attribute15,NULL,l_rev_rec.attribute15,'!',NULL, g_FND_Upd_Null_Char, NULL, attribute15)
,revision_label = decode(revision_label,NULL,l_rev_rec.revision_label,'!',NULL, g_FND_Upd_Null_Char, NULL, revision_label)
,revision_reason = decode(revision_reason,NULL,l_rev_rec.revision_reason,'!',NULL, g_FND_Upd_Null_Char, NULL, revision_reason)
,revised_item_sequence_id = decode(revised_item_sequence_id,NULL,l_rev_rec.revised_item_sequence_id,g_Upd_Null_NUM,NULL, g_FND_Upd_Null_NUM, NULL, revised_item_sequence_id)
,lifecycle_id = decode(lifecycle_id,NULL,l_rev_rec.lifecycle_id,g_Upd_Null_NUM,NULL, g_FND_Upd_Null_NUM, NULL, lifecycle_id)
,current_phase_id = decode(current_phase_id,NULL,l_rev_rec.current_phase_id,g_Upd_Null_NUM,NULL, g_FND_Upd_Null_NUM, NULL, current_phase_id)
WHERE rowid = cr.rowid;
UPDATE mtl_item_revisions_interface
SET process_flag = l_process_flag_3
WHERE rowid = cr.rowid;
UPDATE mtl_item_revisions_interface
SET process_flag = l_process_flag_2 ,
last_update_date = l_sysdate,
last_updated_by = decode(last_updated_by,NULL,user_id,last_updated_by),
creation_date = l_sysdate,
created_by = decode(created_by, NULL, user_id,created_by)
--3070781 :Revision defaults fill LOOP is enough to fill below columns
--This routine gets called only during revision update.
--implementation_date = nvl(effectivity_date, l_sysdate),
--effectivity_date = nvl(effectivity_date, l_sysdate)
WHERE inventory_item_id is not null
AND process_flag = l_process_flag_1
AND set_process_id = xset_id
AND (organization_id = org_id or all_org = l_all_org);
UPDATE mtl_item_revisions_interface i
SET i.process_flag = l_process_flag_3,
i.last_update_date = sysdate,
i.last_updated_by = decode(i.last_updated_by, NULL, user_id,i.last_updated_by),
i.creation_date = l_sysdate,
i.created_by = decode(i.created_by, NULL, user_id,i.created_by)
WHERE (i.inventory_item_id is NULL or i.organization_id is NULL)
AND i.set_process_id = xset_id
AND i.process_flag = l_process_flag_1
AND (i.organization_id = org_id or all_org = l_all_org );
END assign_item_rev_data_update;