[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*Values used in IOI to indicate an attribute update to NULL. Added for bug
* 6417006*/
g_Upd_Null_CHAR VARCHAR2(1) := '!';
select inventory_item_id,
organization_id,
organization_code,
item_number,
item_catalog_group_id,
copy_item_id,
copy_item_number,
copy_organization_id,
copy_organization_code,
transaction_id,
revision,
cost_of_sales_account,
encumbrance_account,
sales_account,
expense_account,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
set_process_id ,
rowid,
--Adding R12 C attribute changes
style_item_flag,
style_item_id,
style_item_number,
source_system_id,
source_system_reference
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 organization_id,item_number
FROM mtl_item_revisions_interface
WHERE inventory_item_id IS NULL
AND item_number = cp_item_number
AND organization_id = cp_organization_id
AND revision = cp_revision
AND set_process_id = xset_id
AND process_flag = 1;
select rowid ,transaction_id,inventory_item_id
from mtl_system_items_interface child
where inventory_item_id is not NULL
and set_process_id = xset_id
and process_flag = 1
and not exists
(select inventory_item_id
from mtl_system_items msi
where msi.inventory_item_id = child.inventory_item_id);
Need to select unit_of_measure column instead of unit_of_measure_tl*/
--3818646 : PUOM from Profile is always in US.
--Below cursor gets PUOM in session langauge.
CURSOR c_get_uom (cp_unit_measure VARCHAR2) IS
SELECT unit_of_measure
FROM mtl_units_of_measure_vl
WHERE uom_code IN (SELECT uom_code
FROM mtl_units_of_measure_tl
WHERE unit_of_measure =cp_unit_measure);
SELECT inventory_item_id
FROM mtl_system_items_b_kfv
WHERE concatenated_segments = cp_style_item_number
AND organization_id = cp_organization_id;
SELECT ITEM_NUM_GEN_METHOD
FROM
(
SELECT ICC.ITEM_NUM_GEN_METHOD
FROM MTL_ITEM_CATALOG_GROUPS_B ICC
WHERE ICC.ITEM_NUM_GEN_METHOD IS NOT NULL
AND ICC.ITEM_NUM_GEN_METHOD <> 'I'
CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
START WITH ICC.ITEM_CATALOG_GROUP_ID = cp_catalog_group_id
ORDER BY LEVEL ASC
)
WHERE ROWNUM = 1;
update mtl_system_items_interface
set transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
where transaction_id is NULL
and set_process_id = xset_id;
SELECT MTL_SYSTEM_ITEMS_S.CURRVAL
INTO l_item_id FROM DUAL;
update mtl_system_items_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid ;
SELECT DISTINCT item_catalog_group_id BULK COLLECT INTO catalog_table
FROM mtl_system_items_interface
WHERE process_flag = 1
AND set_process_id = xset_id
AND ((organization_id = org_id) or (all_org = 1))
AND organization_id IN (SELECT master_organization_id /*Bug 6158936*/
FROM mtl_parameters)
AND item_catalog_group_id IS NOT NULL ;
UPDATE mtl_system_items_interface msii
SET msii.set_process_id = xset_id + 5000000000000
WHERE msii.process_flag = 1
AND msii.set_process_id = xset_id
AND nvl(msii.style_item_flag, 'N') <> 'Y' --Bug 6182208
AND ((msii.organization_id = org_id) or (all_org = 1))
AND msii.organization_id IN (SELECT master_organization_id /*Bug 6158936*/
FROM mtl_parameters)
AND (msii.item_catalog_group_id IS NOT NULL AND msii.item_catalog_group_id = catalog_table(i));
UPDATE mtl_system_items_interface
SET process_flag = 3
,set_process_id = xset_id
WHERE set_process_id = xset_id + 5000000000000;
UPDATE mtl_system_items_interface
SET set_process_id = xset_id
WHERE set_process_id = xset_id + 5000000000000;
UPDATE mtl_system_items_interface msii
SET msii.process_flag = 3
WHERE msii.process_flag = 1
AND msii.transaction_type = 'CREATE'
AND msii.set_process_id = xset_id
AND msii.organization_id = (SELECT mp.master_organization_id FROM mtl_parameters mp
WHERE mp.organization_id = msii.organization_id )
AND ( msii.style_item_flag = 'N' AND msii.style_item_id IS NULL AND msii.style_item_number IS NULL)
RETURNING transaction_id BULK COLLECT INTO transaction_table;
SELECT msii.style_item_number,msii.organization_id,transaction_id
BULK COLLECT INTO style_item_num_table, org_table, transaction_table
FROM mtl_system_items_interface msii
WHERE msii.process_flag = 1
AND msii.transaction_type = 'CREATE'
AND msii.set_process_id = xset_id
AND msii.organization_id = (SELECT mp.master_organization_id FROM mtl_parameters mp
WHERE mp.organization_id = msii.organization_id )
AND ( msii.style_item_flag = 'N' AND msii.style_item_id IS NULL AND msii.style_item_number IS NOT NULL);
UPDATE mtl_system_items_interface msii
SET process_flag = 3
WHERE msii.process_flag = 1
AND msii.transaction_type = 'CREATE'
AND msii.set_process_id = xset_id
AND msii.organization_id = (SELECT mp.master_organization_id FROM mtl_parameters mp
WHERE mp.organization_id = msii.organization_id )
AND msii.style_item_number = style_item_num_table(i);
UPDATE mtl_system_items_interface msii
SET msii.style_item_id = l_style_item_id
WHERE msii.process_flag = 1
AND msii.transaction_type = 'CREATE'
AND msii.set_process_id = xset_id
AND msii.organization_id = (SELECT mp.master_organization_id FROM mtl_parameters mp
WHERE mp.organization_id = msii.organization_id )
AND msii.style_item_number = style_item_num_table(i);
DSQL_ff_statement := 'select msii.transaction_id
from mtl_system_items_interface msii
where msii.inventory_item_id is NULL
and msii.organization_id is not NULL
and msii.process_flag = 1
and msii.set_process_id = :set_id_bind
and exists (select null
from mtl_system_items_b msi
where msii.organization_id = msi.organization_id and ' || ff_statement_temp || ')';
update mtl_system_items_interface
set process_flag = l_process_flag_3
where transaction_id = DSQL_ff_transaction_id
and set_process_id = nvl(xset_id, set_process_id);
DSQL_statement := 'select msi.inventory_item_id
from mtl_system_items msi,
mtl_system_items_interface msii,
mtl_parameters mp
where msii.transaction_id = :transaction_id_bind
and msii.set_process_id = :set_id_bind2
and rownum = 1
and msi.organization_id = mp.organization_id + 0
and ' || ff_statement_temp;
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_system_items_interface
--Bug: 2821206 Replaced ltrim with trim for segment1..20
set segment1 = trim(segment1),
segment2 = trim(segment2),
segment3 = trim(segment3),
segment4 = trim(segment4),
segment5 = trim(segment5),
segment6 = trim(segment6),
segment7 = trim(segment7),
segment8 = trim(segment8),
segment9 = trim(segment9),
segment10 = trim(segment10),
segment11 = trim(segment11),
segment12 = trim(segment12),
segment13 = trim(segment13),
segment14 = trim(segment14),
segment15 = trim(segment15),
segment16 = trim(segment16),
segment17 = trim(segment17),
segment18 = trim(segment18),
segment19 = trim(segment19),
segment20 = trim(segment20) ,
description = trim(description),
long_description = trim(long_description),
attribute_category = trim(attribute_category),
attribute1 = trim(attribute1),
attribute2 = trim(attribute2),
attribute3 = trim(attribute3),
attribute4 = trim(attribute4),
attribute5 = trim(attribute5),
attribute6 = trim(attribute6),
attribute7 = trim(attribute7),
attribute8 = trim(attribute8),
attribute9 = trim(attribute9),
attribute10 = trim(attribute10),
attribute11 = trim(attribute11),
attribute12 = trim(attribute12),
attribute13 = trim(attribute13),
attribute14 = trim(attribute14),
attribute15 = trim(attribute15),
/* Start Bug 3713912 */
attribute16= trim(attribute16),
attribute17= trim(attribute17),
attribute18= trim(attribute18),
attribute19= trim(attribute19),
attribute20= trim(attribute20),
attribute21= trim(attribute21),
attribute22= trim(attribute22),
attribute23= trim(attribute23),
attribute24= trim(attribute24),
attribute25= trim(attribute25),
attribute26= trim(attribute26),
attribute27= trim(attribute27),
attribute28= trim(attribute28),
attribute29= trim(attribute29),
attribute30= trim(attribute30),
cas_number = trim(cas_number),
child_lot_prefix= rtrim(child_lot_prefix),
/* End Bug 3713912 */
auto_lot_alpha_prefix = trim(auto_lot_alpha_prefix), -- Rtrim changed to TRIM for bug-5896824
start_auto_lot_number = rtrim(start_auto_lot_number),
start_auto_serial_number =rtrim(start_auto_serial_number),
auto_serial_alpha_prefix =trim(auto_serial_alpha_prefix), -- Rtrim changed to TRIM for bug-5896824
engineering_ecn_code = rtrim(engineering_ecn_code),
model_config_clause_name = trim(model_config_clause_name),
global_attribute_category = trim(global_attribute_category),
global_attribute1 = trim(global_attribute1),
global_attribute2 = trim(global_attribute2),
global_attribute3 = trim(global_attribute3),
global_attribute4 = trim(global_attribute4),
global_attribute5 = trim(global_attribute5),
global_attribute6 = trim(global_attribute6),
global_attribute7 = trim(global_attribute7),
global_attribute8 = trim(global_attribute8),
global_attribute9 = trim(global_attribute9),
global_attribute10 = trim(global_attribute10),
global_attribute11 = trim(global_attribute11),
global_attribute12 = trim(global_attribute12),
global_attribute13 = trim(global_attribute13),
global_attribute14 = trim(global_attribute14),
global_attribute15 = trim(global_attribute15),
global_attribute16 = trim(global_attribute16),
global_attribute17 = trim(global_attribute17),
global_attribute18 = trim(global_attribute18),
global_attribute19 = trim(global_attribute19),
global_attribute20 = trim(global_attribute20)
where rowid = cr.rowid ;
select organization_code
into org_code
from mtl_parameters
where organization_id = cr.organization_id;
select DECODE(ORG_INFORMATION_CONTEXT,
'Accounting Information',
TO_NUMBER(ORG_INFORMATION3),
TO_NUMBER(NULL)) operating_unit
into op_unit
from hr_organization_information
where organization_id = cr.organization_id
and (org_information_context|| '') ='Accounting Information';
select PSPA.ALLOW_ITEM_DESC_UPDATE_FLAG,
PSPA.RFQ_REQUIRED_FLAG,
PSPA.receiving_flag, PSPA.TAXABLE_FLAG
into allow_item_desc_flag,
req_required_flag,
receiving_flag,
tax_flag
from PO_SYSTEM_PARAMETERS_ALL PSPA
where nvl(PSPA.org_id, -111) = nvl(op_unit, -111)
and rownum = 1;
SELECT name INTO l_org_name
FROM hr_all_organization_units_vl
WHERE organization_id = cr.organization_id;
update MTL_SYSTEM_ITEMS_INTERFACE
set inventory_item_id = exists_id
where rowid = cr.rowid ;
DSQL_Statement_Msii := 'SELECT msi.inventory_item_id
FROM mtl_system_items_interface msi, mtl_system_items_interface msii
WHERE msi.set_process_id = msii.set_process_id
AND msi.organization_id =
(SELECT mp.master_organization_id FROM mtl_parameters mp
WHERE mp.organization_id = msii.organization_id)
AND msii.set_process_id = :set_id_bind2
AND msii.transaction_id = :transaction_id_bind
AND rownum = 1
AND ' ||ff_statement_temp ;
update MTL_SYSTEM_ITEMS_INTERFACE
set inventory_item_id = exists_id
where rowid = cr.rowid ;
update MTL_SYSTEM_ITEMS_INTERFACE
set inventory_item_id = MTL_SYSTEM_ITEMS_S.nextval
where rowid = cr.rowid
returning inventory_item_id INTO cr.inventory_item_id;
select mp.master_organization_id ,
mp.starting_revision ,
cost_of_sales_account,
encumbrance_account,
sales_account,
expense_account
into master_org_id ,
default_rev ,
d_cost_of_sales_account,
d_encumbrance_account,
d_sales_account,
d_expense_account
from mtl_parameters mp
where mp.organization_id = cr.organization_id;
select count(*) into msiicount
from mtl_system_items msii
where cr.inventory_item_id = msii.inventory_item_id
and msii.organization_id = master_org_id;
select count(*) into msiicount
from mtl_system_items_interface
where inventory_item_id = cr.inventory_item_id
and organization_id = master_org_id
and set_process_id = xset_id
and process_flag in (1,60000+1); /*masters are moved to 60000+ to before calling child create*/
update MTL_SYSTEM_ITEMS_INTERFACE
set cost_of_sales_account = nvl(cost_of_sales_account,d_cost_of_sales_account),
encumbrance_account = nvl(encumbrance_account,d_encumbrance_account),
sales_account = nvl(sales_account,d_sales_account),
expense_account = nvl(expense_account,d_expense_account)
where rowid = cr.rowid ;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET
DESCRIPTION = DECODE( DESCRIPTION, g_Upd_Null_CHAR, NULL,
trim(DESCRIPTION )),
LONG_DESCRIPTION = DECODE( LONG_DESCRIPTION, g_Upd_Null_CHAR, NULL,
trim(LONG_DESCRIPTION)),
BUYER_ID = decode(BUYER_ID, g_Upd_Null_NUM, NULL,
BUYER_ID),
ACCOUNTING_RULE_ID = decode(ACCOUNTING_RULE_ID,g_Upd_Null_NUM,
NULL, ACCOUNTING_RULE_ID),
INVOICING_RULE_ID = decode(INVOICING_RULE_ID,g_Upd_Null_NUM,
NULL, INVOICING_RULE_ID),
ATTRIBUTE_CATEGORY = decode(ATTRIBUTE_CATEGORY,
g_Upd_Null_CHAR, NULL, trim(ATTRIBUTE_CATEGORY)),
ATTRIBUTE1 = decode(ATTRIBUTE1, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE1)),
ATTRIBUTE2 = decode(ATTRIBUTE2, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE2)),
ATTRIBUTE3 = decode(ATTRIBUTE3, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE3)),
ATTRIBUTE4 = decode(ATTRIBUTE4, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE4)),
ATTRIBUTE5 = decode(ATTRIBUTE5, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE5)),
ATTRIBUTE6 = decode(ATTRIBUTE6, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE6)),
ATTRIBUTE7 = decode(ATTRIBUTE7, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE7)),
ATTRIBUTE8 = decode(ATTRIBUTE8, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE8)),
ATTRIBUTE9 = decode(ATTRIBUTE9, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE9)),
ATTRIBUTE10 = decode(ATTRIBUTE10, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE10)),
ATTRIBUTE11 = decode(ATTRIBUTE11, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE11)),
ATTRIBUTE12 = decode(ATTRIBUTE12, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE12)),
ATTRIBUTE13 = decode(ATTRIBUTE13, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE13)),
ATTRIBUTE14 = decode(ATTRIBUTE14, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE14)),
ATTRIBUTE15 = decode(ATTRIBUTE15, g_Upd_Null_CHAR,
NULL, trim(ATTRIBUTE15)),
GLOBAL_ATTRIBUTE_CATEGORY =
decode(GLOBAL_ATTRIBUTE_CATEGORY, g_Upd_Null_CHAR, NULL,
trim(GLOBAL_ATTRIBUTE_CATEGORY)),
GLOBAL_ATTRIBUTE1 = decode(GLOBAL_ATTRIBUTE1,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE1)),
GLOBAL_ATTRIBUTE2 = decode(GLOBAL_ATTRIBUTE2,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE2)),
GLOBAL_ATTRIBUTE3 = decode(GLOBAL_ATTRIBUTE3,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE3)),
GLOBAL_ATTRIBUTE4 = decode(GLOBAL_ATTRIBUTE4,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE4)),
GLOBAL_ATTRIBUTE5 = decode(GLOBAL_ATTRIBUTE5,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE5)),
GLOBAL_ATTRIBUTE6 = decode(GLOBAL_ATTRIBUTE6,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE6)),
GLOBAL_ATTRIBUTE7 = decode(GLOBAL_ATTRIBUTE7,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE7)),
GLOBAL_ATTRIBUTE8 = decode(GLOBAL_ATTRIBUTE8,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE8)),
GLOBAL_ATTRIBUTE9 = decode(GLOBAL_ATTRIBUTE9,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE9)),
GLOBAL_ATTRIBUTE10 = decode(GLOBAL_ATTRIBUTE10,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE10)),
GLOBAL_ATTRIBUTE11 = decode(GLOBAL_ATTRIBUTE11,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE11)),
GLOBAL_ATTRIBUTE12 = decode(GLOBAL_ATTRIBUTE12,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE12)),
GLOBAL_ATTRIBUTE13 = decode(GLOBAL_ATTRIBUTE13,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE13)),
GLOBAL_ATTRIBUTE14 = decode(GLOBAL_ATTRIBUTE14,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE14)),
GLOBAL_ATTRIBUTE15 = decode(GLOBAL_ATTRIBUTE15,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE15)),
GLOBAL_ATTRIBUTE16 = decode(GLOBAL_ATTRIBUTE16,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE16)),
GLOBAL_ATTRIBUTE17 = decode(GLOBAL_ATTRIBUTE17,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE17)),
GLOBAL_ATTRIBUTE18 = decode(GLOBAL_ATTRIBUTE18,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE18)),
GLOBAL_ATTRIBUTE19 = decode(GLOBAL_ATTRIBUTE19,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE19)),
GLOBAL_ATTRIBUTE20 = decode(GLOBAL_ATTRIBUTE20,
g_Upd_Null_CHAR, NULL, trim(GLOBAL_ATTRIBUTE20)),
ITEM_CATALOG_GROUP_ID = decode(ITEM_CATALOG_GROUP_ID,
g_Upd_Null_NUM, NULL, ITEM_CATALOG_GROUP_ID),
CATALOG_STATUS_FLAG = DECODE(CATALOG_STATUS_FLAG,
g_Upd_Null_CHAR, NULL, trim(CATALOG_STATUS_FLAG)),
DEFAULT_SHIPPING_ORG = decode(DEFAULT_SHIPPING_ORG,
g_Upd_Null_NUM, NULL, DEFAULT_SHIPPING_ORG),
TAXABLE_FLAG = decode(TAXABLE_FLAG,
g_Upd_Null_CHAR, NULL, trim(TAXABLE_FLAG)),
PURCHASING_TAX_CODE =
decode(PURCHASING_TAX_CODE,g_Upd_Null_CHAR,NULL,trim(PURCHASING_TAX_CODE)),
QTY_RCV_EXCEPTION_CODE = decode(QTY_RCV_EXCEPTION_CODE,
g_Upd_Null_CHAR, NULL, trim(QTY_RCV_EXCEPTION_CODE)),
INSPECTION_REQUIRED_FLAG = decode(INSPECTION_REQUIRED_FLAG,
g_Upd_Null_CHAR, NULL, trim(INSPECTION_REQUIRED_FLAG)),
RECEIPT_REQUIRED_FLAG = decode(RECEIPT_REQUIRED_FLAG,
g_Upd_Null_CHAR, NULL, trim(RECEIPT_REQUIRED_FLAG)),
MARKET_PRICE = decode(MARKET_PRICE,
g_Upd_Null_NUM, NULL, MARKET_PRICE),
HAZARD_CLASS_ID = decode(HAZARD_CLASS_ID,
g_Upd_Null_NUM, NULL, HAZARD_CLASS_ID),
QTY_RCV_TOLERANCE = decode(QTY_RCV_TOLERANCE,
g_Upd_Null_NUM, NULL, QTY_RCV_TOLERANCE),
LIST_PRICE_PER_UNIT = decode(LIST_PRICE_PER_UNIT,
g_Upd_Null_NUM, NULL, LIST_PRICE_PER_UNIT),
UN_NUMBER_ID = decode(UN_NUMBER_ID,
g_Upd_Null_NUM, NULL, UN_NUMBER_ID),
PRICE_TOLERANCE_PERCENT = decode(PRICE_TOLERANCE_PERCENT,
g_Upd_Null_NUM, NULL, PRICE_TOLERANCE_PERCENT),
ASSET_CATEGORY_ID = decode(ASSET_CATEGORY_ID,
g_Upd_Null_NUM, NULL, ASSET_CATEGORY_ID),
ROUNDING_FACTOR = decode(ROUNDING_FACTOR,
g_Upd_Null_NUM, NULL, ROUNDING_FACTOR),
UNIT_OF_ISSUE = decode(UNIT_OF_ISSUE,
g_Upd_Null_CHAR, NULL, trim(UNIT_OF_ISSUE)),
ENFORCE_SHIP_TO_LOCATION_CODE =
decode(ENFORCE_SHIP_TO_LOCATION_CODE, g_Upd_Null_CHAR, NULL,
trim(ENFORCE_SHIP_TO_LOCATION_CODE)),
ALLOW_SUBSTITUTE_RECEIPTS_FLAG =
decode(ALLOW_SUBSTITUTE_RECEIPTS_FLAG, g_Upd_Null_CHAR, NULL,
trim(ALLOW_SUBSTITUTE_RECEIPTS_FLAG)),
ALLOW_UNORDERED_RECEIPTS_FLAG =
decode(ALLOW_UNORDERED_RECEIPTS_FLAG, g_Upd_Null_CHAR, NULL,
trim(ALLOW_UNORDERED_RECEIPTS_FLAG)),
ALLOW_EXPRESS_DELIVERY_FLAG =
decode(ALLOW_EXPRESS_DELIVERY_FLAG, g_Upd_Null_CHAR, NULL,
trim(ALLOW_EXPRESS_DELIVERY_FLAG)),
DAYS_EARLY_RECEIPT_ALLOWED =
decode(DAYS_EARLY_RECEIPT_ALLOWED, g_Upd_Null_NUM, NULL,
DAYS_EARLY_RECEIPT_ALLOWED),
DAYS_LATE_RECEIPT_ALLOWED = decode(DAYS_LATE_RECEIPT_ALLOWED,
g_Upd_Null_NUM, NULL, DAYS_LATE_RECEIPT_ALLOWED),
RECEIPT_DAYS_EXCEPTION_CODE =
decode(RECEIPT_DAYS_EXCEPTION_CODE, g_Upd_Null_CHAR, NULL,
trim(RECEIPT_DAYS_EXCEPTION_CODE)),
RECEIVING_ROUTING_ID = decode(RECEIVING_ROUTING_ID,
g_Upd_Null_NUM, NULL, RECEIVING_ROUTING_ID),
INVOICE_CLOSE_TOLERANCE = decode(INVOICE_CLOSE_TOLERANCE,
g_Upd_Null_NUM, NULL, INVOICE_CLOSE_TOLERANCE),
RECEIVE_CLOSE_TOLERANCE = decode(RECEIVE_CLOSE_TOLERANCE,
g_Upd_Null_NUM, NULL, RECEIVE_CLOSE_TOLERANCE),
AUTO_LOT_ALPHA_PREFIX = decode(AUTO_LOT_ALPHA_PREFIX,
g_Upd_Null_CHAR, NULL, trim(AUTO_LOT_ALPHA_PREFIX)),
START_AUTO_LOT_NUMBER = decode(START_AUTO_LOT_NUMBER,
g_Upd_Null_CHAR, NULL, trim(START_AUTO_LOT_NUMBER)),
SHELF_LIFE_DAYS = decode(SHELF_LIFE_DAYS,
g_Upd_Null_NUM, NULL, SHELF_LIFE_DAYS),
START_AUTO_SERIAL_NUMBER = decode(START_AUTO_SERIAL_NUMBER,
g_Upd_Null_CHAR, NULL, trim(START_AUTO_SERIAL_NUMBER)),
AUTO_SERIAL_ALPHA_PREFIX = decode(AUTO_SERIAL_ALPHA_PREFIX,
g_Upd_Null_CHAR, NULL, trim(AUTO_SERIAL_ALPHA_PREFIX)),
SOURCE_TYPE = decode(SOURCE_TYPE,
g_Upd_Null_NUM, NULL, SOURCE_TYPE),
SOURCE_ORGANIZATION_ID = decode(SOURCE_ORGANIZATION_ID,
g_Upd_Null_NUM, NULL, SOURCE_ORGANIZATION_ID),
SOURCE_SUBINVENTORY = decode(SOURCE_SUBINVENTORY,
g_Upd_Null_CHAR, NULL, trim(SOURCE_SUBINVENTORY)),
EXPENSE_ACCOUNT = decode(EXPENSE_ACCOUNT,
g_Upd_Null_NUM, NULL, EXPENSE_ACCOUNT),
ENCUMBRANCE_ACCOUNT = decode(ENCUMBRANCE_ACCOUNT,
g_Upd_Null_NUM, NULL, ENCUMBRANCE_ACCOUNT),
UNIT_WEIGHT = decode(UNIT_WEIGHT,
g_Upd_Null_NUM, NULL, UNIT_WEIGHT),
WEIGHT_UOM_CODE = decode(WEIGHT_UOM_CODE,
g_Upd_Null_CHAR, NULL, trim(WEIGHT_UOM_CODE)),
VOLUME_UOM_CODE = decode(VOLUME_UOM_CODE,
g_Upd_Null_CHAR, NULL, trim(VOLUME_UOM_CODE)),
UNIT_VOLUME = decode(UNIT_VOLUME,
g_Upd_Null_NUM, NULL, UNIT_VOLUME),
SHRINKAGE_RATE = decode(SHRINKAGE_RATE,
g_Upd_Null_NUM, NULL, SHRINKAGE_RATE),
ACCEPTABLE_EARLY_DAYS = decode(ACCEPTABLE_EARLY_DAYS,
g_Upd_Null_NUM, NULL, ACCEPTABLE_EARLY_DAYS),
DEMAND_TIME_FENCE_CODE = decode(DEMAND_TIME_FENCE_CODE,
g_Upd_Null_NUM, NULL, DEMAND_TIME_FENCE_CODE),
STD_LOT_SIZE = decode(STD_LOT_SIZE,
g_Upd_Null_NUM, NULL, STD_LOT_SIZE),
LEAD_TIME_LOT_SIZE = decode(LEAD_TIME_LOT_SIZE,
g_Upd_Null_NUM, NULL, LEAD_TIME_LOT_SIZE),
CUM_MANUFACTURING_LEAD_TIME =
decode(CUM_MANUFACTURING_LEAD_TIME, g_Upd_Null_NUM, NULL,
CUM_MANUFACTURING_LEAD_TIME),
OVERRUN_PERCENTAGE = decode(OVERRUN_PERCENTAGE,
g_Upd_Null_NUM, NULL, OVERRUN_PERCENTAGE),
ACCEPTABLE_RATE_INCREASE = decode(ACCEPTABLE_RATE_INCREASE,
g_Upd_Null_NUM, NULL, ACCEPTABLE_RATE_INCREASE),
ACCEPTABLE_RATE_DECREASE = decode(ACCEPTABLE_RATE_DECREASE,
g_Upd_Null_NUM, NULL, ACCEPTABLE_RATE_DECREASE),
CUMULATIVE_TOTAL_LEAD_TIME =
decode(CUMULATIVE_TOTAL_LEAD_TIME, g_Upd_Null_NUM, NULL,
CUMULATIVE_TOTAL_LEAD_TIME),
PLANNING_TIME_FENCE_DAYS = decode(PLANNING_TIME_FENCE_DAYS,
g_Upd_Null_NUM, NULL, PLANNING_TIME_FENCE_DAYS),
DEMAND_TIME_FENCE_DAYS = decode(DEMAND_TIME_FENCE_DAYS,
g_Upd_Null_NUM, NULL, DEMAND_TIME_FENCE_DAYS),
RELEASE_TIME_FENCE_CODE = decode(RELEASE_TIME_FENCE_CODE, g_Upd_Null_NUM,
NULL, RELEASE_TIME_FENCE_CODE),
RELEASE_TIME_FENCE_DAYS = decode(RELEASE_TIME_FENCE_DAYS,g_Upd_Null_NUM,
NULL, RELEASE_TIME_FENCE_DAYS),
END_ASSEMBLY_PEGGING_FLAG = decode(END_ASSEMBLY_PEGGING_FLAG,
g_Upd_Null_CHAR, NULL, trim(END_ASSEMBLY_PEGGING_FLAG)),
PLANNING_EXCEPTION_SET = decode(PLANNING_EXCEPTION_SET,
g_Upd_Null_CHAR, NULL, trim(PLANNING_EXCEPTION_SET)),
BASE_ITEM_ID = decode(BASE_ITEM_ID,
g_Upd_Null_NUM, NULL, BASE_ITEM_ID),
FIXED_LEAD_TIME = decode(FIXED_LEAD_TIME,
g_Upd_Null_NUM, NULL, FIXED_LEAD_TIME),
VARIABLE_LEAD_TIME = decode(VARIABLE_LEAD_TIME,
g_Upd_Null_NUM, NULL, VARIABLE_LEAD_TIME),
WIP_SUPPLY_LOCATOR_ID = decode(WIP_SUPPLY_LOCATOR_ID,
g_Upd_Null_NUM, NULL, WIP_SUPPLY_LOCATOR_ID),
WIP_SUPPLY_TYPE = decode(WIP_SUPPLY_TYPE,
g_Upd_Null_NUM, 1, WIP_SUPPLY_TYPE), -- Syalaman - Fix for bug 5886000
WIP_SUPPLY_SUBINVENTORY = decode(WIP_SUPPLY_SUBINVENTORY,
g_Upd_Null_CHAR, NULL, trim(WIP_SUPPLY_SUBINVENTORY)),
PLANNER_CODE = decode(PLANNER_CODE,
g_Upd_Null_CHAR, NULL, trim(PLANNER_CODE)),
FIXED_LOT_MULTIPLIER = decode(FIXED_LOT_MULTIPLIER,
g_Upd_Null_NUM, NULL, FIXED_LOT_MULTIPLIER),
CARRYING_COST = decode(CARRYING_COST,
g_Upd_Null_NUM, NULL, CARRYING_COST),
POSTPROCESSING_LEAD_TIME =
decode(POSTPROCESSING_LEAD_TIME, g_Upd_Null_NUM,NULL,
POSTPROCESSING_LEAD_TIME),
PREPROCESSING_LEAD_TIME = decode(PREPROCESSING_LEAD_TIME,
g_Upd_Null_NUM, NULL, PREPROCESSING_LEAD_TIME),
FULL_LEAD_TIME = decode(FULL_LEAD_TIME,
g_Upd_Null_NUM, NULL, FULL_LEAD_TIME),
ORDER_COST = decode(ORDER_COST,
g_Upd_Null_NUM, NULL, ORDER_COST),
MRP_SAFETY_STOCK_PERCENT = decode(MRP_SAFETY_STOCK_PERCENT,
g_Upd_Null_NUM, NULL, MRP_SAFETY_STOCK_PERCENT),
MIN_MINMAX_QUANTITY = decode(MIN_MINMAX_QUANTITY,
g_Upd_Null_NUM, NULL, MIN_MINMAX_QUANTITY),
MAX_MINMAX_QUANTITY = decode(MAX_MINMAX_QUANTITY,
g_Upd_Null_NUM, NULL, MAX_MINMAX_QUANTITY),
MINIMUM_ORDER_QUANTITY = decode(MINIMUM_ORDER_QUANTITY,
g_Upd_Null_NUM, NULL, MINIMUM_ORDER_QUANTITY),
FIXED_ORDER_QUANTITY = decode(FIXED_ORDER_QUANTITY,
g_Upd_Null_NUM, NULL, FIXED_ORDER_QUANTITY),
FIXED_DAYS_SUPPLY = decode(FIXED_DAYS_SUPPLY,
g_Upd_Null_NUM, NULL, FIXED_DAYS_SUPPLY),
MAXIMUM_ORDER_QUANTITY = decode(MAXIMUM_ORDER_QUANTITY,
g_Upd_Null_NUM, NULL, MAXIMUM_ORDER_QUANTITY),
ATP_RULE_ID = decode(ATP_RULE_ID,
g_Upd_Null_NUM, NULL, ATP_RULE_ID),
PICKING_RULE_ID = decode(PICKING_RULE_ID,
g_Upd_Null_NUM, NULL, PICKING_RULE_ID),
POSITIVE_MEASUREMENT_ERROR =
decode(POSITIVE_MEASUREMENT_ERROR, g_Upd_Null_NUM, NULL,
POSITIVE_MEASUREMENT_ERROR),
NEGATIVE_MEASUREMENT_ERROR =
decode(NEGATIVE_MEASUREMENT_ERROR, g_Upd_Null_NUM, NULL,
NEGATIVE_MEASUREMENT_ERROR),
SERVICE_STARTING_DELAY = decode(SERVICE_STARTING_DELAY,
g_Upd_Null_NUM, NULL, SERVICE_STARTING_DELAY),
PAYMENT_TERMS_ID = decode(PAYMENT_TERMS_ID,
g_Upd_Null_NUM, NULL, PAYMENT_TERMS_ID),
MATERIAL_BILLABLE_FLAG =
decode(MATERIAL_BILLABLE_FLAG,g_Upd_Null_CHAR,NULL,trim(MATERIAL_BILLABLE_FLAG)),
COVERAGE_SCHEDULE_ID = decode(COVERAGE_SCHEDULE_ID,
g_Upd_Null_NUM, NULL, COVERAGE_SCHEDULE_ID),
SERVICE_DURATION_PERIOD_CODE =
decode(SERVICE_DURATION_PERIOD_CODE, g_Upd_Null_CHAR, NULL,
trim(SERVICE_DURATION_PERIOD_CODE)),
SERVICE_DURATION = decode(SERVICE_DURATION,
g_Upd_Null_NUM, NULL, SERVICE_DURATION),
TAX_CODE = decode(TAX_CODE, g_Upd_Null_CHAR,
NULL, trim(TAX_CODE)),
OUTSIDE_OPERATION_UOM_TYPE =
decode(OUTSIDE_OPERATION_UOM_TYPE,g_Upd_Null_CHAR,NULL,trim(OUTSIDE_OPERATION_UOM_TYPE)),
SAFETY_STOCK_BUCKET_DAYS = decode(SAFETY_STOCK_BUCKET_DAYS,
g_Upd_Null_NUM, NULL, SAFETY_STOCK_BUCKET_DAYS),
AUTO_REDUCE_MPS = decode(AUTO_REDUCE_MPS,
g_Upd_Null_NUM, NULL, trim(AUTO_REDUCE_MPS)),
ITEM_TYPE = decode(ITEM_TYPE, g_Upd_Null_CHAR,
NULL, trim(ITEM_TYPE)),
ATO_FORECAST_CONTROL = decode(ATO_FORECAST_CONTROL,
g_Upd_Null_NUM, NULL, ATO_FORECAST_CONTROL),
MAXIMUM_LOAD_WEIGHT = decode(MAXIMUM_LOAD_WEIGHT,
g_Upd_Null_NUM, NULL, MAXIMUM_LOAD_WEIGHT),
MINIMUM_FILL_PERCENT =
decode(MINIMUM_FILL_PERCENT,g_Upd_Null_NUM, NULL, MINIMUM_FILL_PERCENT),
CONTAINER_TYPE_CODE = decode(CONTAINER_TYPE_CODE,
g_Upd_Null_CHAR, NULL, trim(CONTAINER_TYPE_CODE)),
INTERNAL_VOLUME = decode(INTERNAL_VOLUME,
g_Upd_Null_NUM, NULL, INTERNAL_VOLUME),
OVERCOMPLETION_TOLERANCE_TYPE = DECODE( OVERCOMPLETION_TOLERANCE_TYPE,
g_Upd_Null_NUM, NULL, OVERCOMPLETION_TOLERANCE_TYPE ),
OVERCOMPLETION_TOLERANCE_VALUE = DECODE( OVERCOMPLETION_TOLERANCE_VALUE,
g_Upd_Null_NUM, NULL, OVERCOMPLETION_TOLERANCE_VALUE ),
OVER_SHIPMENT_TOLERANCE = DECODE( OVER_SHIPMENT_TOLERANCE,
g_Upd_Null_NUM, NULL, OVER_SHIPMENT_TOLERANCE ),
UNDER_SHIPMENT_TOLERANCE = DECODE(
UNDER_SHIPMENT_TOLERANCE,g_Upd_Null_NUM, NULL, UNDER_SHIPMENT_TOLERANCE ),
OVER_RETURN_TOLERANCE = DECODE( OVER_RETURN_TOLERANCE,
g_Upd_Null_NUM, NULL, OVER_RETURN_TOLERANCE ),
UNDER_RETURN_TOLERANCE = DECODE( UNDER_RETURN_TOLERANCE,
g_Upd_Null_NUM, NULL, UNDER_RETURN_TOLERANCE ),
RECOVERED_PART_DISP_CODE = DECODE( RECOVERED_PART_DISP_CODE,
g_Upd_Null_CHAR, NULL, trim(RECOVERED_PART_DISP_CODE) ),
ASSET_CREATION_CODE = DECODE( ASSET_CREATION_CODE,
g_Upd_Null_CHAR, NULL, trim(ASSET_CREATION_CODE) ),
DIMENSION_UOM_CODE = DECODE( DIMENSION_UOM_CODE,
g_Upd_Null_CHAR, NULL, trim(DIMENSION_UOM_CODE) ),
UNIT_LENGTH = DECODE( UNIT_LENGTH, g_Upd_Null_NUM,
NULL, UNIT_LENGTH ),
UNIT_WIDTH = DECODE( UNIT_WIDTH, g_Upd_Null_NUM,
NULL, UNIT_WIDTH ),
UNIT_HEIGHT = DECODE( UNIT_HEIGHT, g_Upd_Null_NUM,
NULL, UNIT_HEIGHT ),
DEFAULT_LOT_STATUS_ID = DECODE( DEFAULT_LOT_STATUS_ID,
g_Upd_Null_NUM, NULL, DEFAULT_LOT_STATUS_ID ),
DEFAULT_SERIAL_STATUS_ID = DECODE( DEFAULT_SERIAL_STATUS_ID,
g_Upd_Null_NUM, NULL, DEFAULT_SERIAL_STATUS_ID ),
INVENTORY_CARRY_PENALTY = DECODE( INVENTORY_CARRY_PENALTY,
g_Upd_Null_NUM, NULL, INVENTORY_CARRY_PENALTY ),
OPERATION_SLACK_PENALTY = DECODE( OPERATION_SLACK_PENALTY,
g_Upd_Null_NUM, NULL, OPERATION_SLACK_PENALTY ),
EAM_ITEM_TYPE = DECODE( EAM_ITEM_TYPE, g_Upd_Null_NUM, NULL,
EAM_ITEM_TYPE ),
EAM_ACTIVITY_TYPE_CODE = DECODE( EAM_ACTIVITY_TYPE_CODE, g_Upd_Null_CHAR,
NULL, trim(EAM_ACTIVITY_TYPE_CODE) ),
EAM_ACTIVITY_CAUSE_CODE = DECODE( EAM_ACTIVITY_CAUSE_CODE,
g_Upd_Null_CHAR, NULL, trim(EAM_ACTIVITY_CAUSE_CODE) ),
EAM_ACT_NOTIFICATION_FLAG = DECODE( EAM_ACT_NOTIFICATION_FLAG,
g_Upd_Null_CHAR, NULL, trim(EAM_ACT_NOTIFICATION_FLAG) ),
EAM_ACT_SHUTDOWN_STATUS = DECODE( EAM_ACT_SHUTDOWN_STATUS,
g_Upd_Null_CHAR, NULL, trim(EAM_ACT_SHUTDOWN_STATUS) ),
SECONDARY_UOM_CODE = DECODE( SECONDARY_UOM_CODE, g_Upd_Null_CHAR,
NULL, trim(SECONDARY_UOM_CODE) ),
DUAL_UOM_DEVIATION_HIGH = DECODE( DUAL_UOM_DEVIATION_HIGH, g_Upd_Null_NUM,
NULL, DUAL_UOM_DEVIATION_HIGH ),
DUAL_UOM_DEVIATION_LOW = DECODE( DUAL_UOM_DEVIATION_LOW, g_Upd_Null_NUM,
NULL, DUAL_UOM_DEVIATION_LOW ),
CONTRACT_ITEM_TYPE_CODE = DECODE( CONTRACT_ITEM_TYPE_CODE,
g_Upd_Null_CHAR, NULL, trim(CONTRACT_ITEM_TYPE_CODE) ),
SUBSCRIPTION_DEPEND_FLAG = DECODE( SUBSCRIPTION_DEPEND_FLAG,
g_Upd_Null_CHAR, NULL, trim(SUBSCRIPTION_DEPEND_FLAG) ),
SERV_REQ_ENABLED_CODE = DECODE( SERV_REQ_ENABLED_CODE, g_Upd_Null_CHAR,
NULL, trim(SERV_REQ_ENABLED_CODE) ),
SERV_BILLING_ENABLED_FLAG = DECODE( SERV_BILLING_ENABLED_FLAG,
g_Upd_Null_CHAR, NULL, trim(SERV_BILLING_ENABLED_FLAG) ),
SERV_IMPORTANCE_LEVEL = DECODE( SERV_IMPORTANCE_LEVEL, g_Upd_Null_NUM,
NULL, SERV_IMPORTANCE_LEVEL ),
PLANNED_INV_POINT_FLAG = DECODE( PLANNED_INV_POINT_FLAG, g_Upd_Null_CHAR,
NULL, trim(PLANNED_INV_POINT_FLAG) ),
LOT_TRANSLATE_ENABLED = DECODE( LOT_TRANSLATE_ENABLED, g_Upd_Null_CHAR,
NULL, trim(LOT_TRANSLATE_ENABLED) ),
DEFAULT_SO_SOURCE_TYPE = DECODE( DEFAULT_SO_SOURCE_TYPE, g_Upd_Null_CHAR,
NULL, trim(DEFAULT_SO_SOURCE_TYPE) ),
CREATE_SUPPLY_FLAG = DECODE( CREATE_SUPPLY_FLAG, g_Upd_Null_CHAR,
NULL, trim(CREATE_SUPPLY_FLAG) ),
SUBSTITUTION_WINDOW_CODE = DECODE( SUBSTITUTION_WINDOW_CODE,
g_Upd_Null_NUM, NULL, SUBSTITUTION_WINDOW_CODE ),
SUBSTITUTION_WINDOW_DAYS = DECODE( SUBSTITUTION_WINDOW_DAYS,
g_Upd_Null_NUM, NULL, SUBSTITUTION_WINDOW_DAYS ),
LOT_SUBSTITUTION_ENABLED = DECODE( LOT_SUBSTITUTION_ENABLED,
g_Upd_Null_CHAR, NULL, trim(LOT_SUBSTITUTION_ENABLED) ),
MINIMUM_LICENSE_QUANTITY = DECODE( MINIMUM_LICENSE_QUANTITY,
g_Upd_Null_NUM, NULL, MINIMUM_LICENSE_QUANTITY),
EAM_ACTIVITY_SOURCE_CODE = DECODE( EAM_ACTIVITY_SOURCE_CODE,
g_Upd_Null_CHAR, NULL, trim(EAM_ACTIVITY_SOURCE_CODE) ),
IB_ITEM_INSTANCE_CLASS = DECODE( IB_ITEM_INSTANCE_CLASS, g_Upd_Null_CHAR,
NULL, trim(IB_ITEM_INSTANCE_CLASS) ),
CONFIG_MODEL_TYPE = DECODE( CONFIG_MODEL_TYPE, g_Upd_Null_CHAR,
NULL, trim(CONFIG_MODEL_TYPE) ),
TRACKING_QUANTITY_IND = DECODE( TRACKING_QUANTITY_IND, g_Upd_Null_CHAR,
NULL, trim(TRACKING_QUANTITY_IND) ),
ONT_PRICING_QTY_SOURCE = DECODE( ONT_PRICING_QTY_SOURCE, g_Upd_Null_CHAR,
NULL, trim(ONT_PRICING_QTY_SOURCE) ),
SECONDARY_DEFAULT_IND = DECODE( SECONDARY_DEFAULT_IND, g_Upd_Null_CHAR,
NULL, trim(SECONDARY_DEFAULT_IND) ),
CONFIG_ORGS = DECODE( CONFIG_ORGS, g_Upd_Null_CHAR, NULL,
trim(CONFIG_ORGS) ),
CONFIG_MATCH = DECODE( CONFIG_MATCH, g_Upd_Null_CHAR, NULL,
trim(CONFIG_MATCH) ),
LIFECYCLE_ID =
decode(LIFECYCLE_ID,g_Upd_Null_NUM,NULL,LIFECYCLE_ID),
CURRENT_PHASE_ID =
decode(CURRENT_PHASE_ID,g_Upd_Null_NUM,NULL,CURRENT_PHASE_ID),
VMI_MINIMUM_UNITS = DECODE( VMI_MINIMUM_UNITS,g_Upd_Null_NUM, NULL,
VMI_MINIMUM_UNITS ) ,
VMI_MINIMUM_DAYS = DECODE( VMI_MINIMUM_DAYS, g_Upd_Null_NUM, NULL,
VMI_MINIMUM_DAYS ) ,
VMI_MAXIMUM_UNITS = DECODE( VMI_MAXIMUM_UNITS,g_Upd_Null_NUM, NULL,
VMI_MAXIMUM_UNITS ),
VMI_MAXIMUM_DAYS = DECODE( VMI_MAXIMUM_DAYS, g_Upd_Null_NUM, NULL,
VMI_MAXIMUM_DAYS ),
VMI_FIXED_ORDER_QUANTITY = DECODE( VMI_FIXED_ORDER_QUANTITY,
g_Upd_Null_NUM, NULL, VMI_FIXED_ORDER_QUANTITY ),
SO_AUTHORIZATION_FLAG = DECODE(SO_AUTHORIZATION_FLAG, g_Upd_Null_NUM,
NULL, SO_AUTHORIZATION_FLAG ),
CONSIGNED_FLAG = DECODE(CONSIGNED_FLAG, g_Upd_Null_NUM,
NULL,CONSIGNED_FLAG ),
ASN_AUTOEXPIRE_FLAG = DECODE( ASN_AUTOEXPIRE_FLAG, g_Upd_Null_NUM,
NULL, ASN_AUTOEXPIRE_FLAG ),
VMI_FORECAST_TYPE = DECODE( VMI_FORECAST_TYPE, g_Upd_Null_NUM, NULL,
VMI_FORECAST_TYPE ),
FORECAST_HORIZON = DECODE( FORECAST_HORIZON, g_Upd_Null_NUM,
NULL,FORECAST_HORIZON ),
EXCLUDE_FROM_BUDGET_FLAG = DECODE( EXCLUDE_FROM_BUDGET_FLAG,
g_Upd_Null_NUM, NULL, EXCLUDE_FROM_BUDGET_FLAG ),
DAYS_TGT_INV_SUPPLY = DECODE( DAYS_TGT_INV_SUPPLY,
g_Upd_Null_NUM,NULL, DAYS_TGT_INV_SUPPLY),
DAYS_TGT_INV_WINDOW = DECODE( DAYS_TGT_INV_WINDOW, g_Upd_Null_NUM,
NULL, DAYS_TGT_INV_WINDOW ),
DAYS_MAX_INV_SUPPLY = DECODE( DAYS_MAX_INV_SUPPLY,g_Upd_Null_NUM,
NULL, DAYS_MAX_INV_SUPPLY ),
DAYS_MAX_INV_WINDOW = DECODE( DAYS_MAX_INV_WINDOW, g_Upd_Null_NUM,
NULL, DAYS_MAX_INV_WINDOW ),
DRP_PLANNED_FLAG = DECODE( DRP_PLANNED_FLAG, g_Upd_Null_NUM, NULL,
DRP_PLANNED_FLAG ),
CRITICAL_COMPONENT_FLAG = DECODE( CRITICAL_COMPONENT_FLAG, g_Upd_Null_NUM,
NULL, CRITICAL_COMPONENT_FLAG ),
CONTINOUS_TRANSFER = DECODE( CONTINOUS_TRANSFER, g_Upd_Null_NUM,
NULL, CONTINOUS_TRANSFER ),
CONVERGENCE = DECODE( CONVERGENCE, g_Upd_Null_NUM, NULL, CONVERGENCE
),
DIVERGENCE = DECODE( DIVERGENCE, g_Upd_Null_NUM, NULL, DIVERGENCE )
WHERE
MSII.rowid = cr.rowid;
-- Store sysdate minus 1 sec when inserting default revision data
l_curr_sysdate := sysdate - 1/86400;
update mtl_item_revisions_interface
set effectivity_date = sysdate
where set_process_id = xset_id
and process_flag = 1
and revision = cr.revision
and (effectivity_date is null or effectivity_date > sysdate);
update mtl_item_revisions_interface
set inventory_item_id = flex_id
where item_number = c_revision_record.item_number
and set_process_id = xset_id
and organization_id = c_revision_record.organization_id
and revision = cr.revision
RETURNING effectivity_date,rowid INTO l_effectivity_date,l_rowid;
update mtl_item_revisions_interface
set effectivity_date = sysdate
where rowid = l_rowid;
SELECT count(*) INTO revs
FROM mtl_item_revisions_interface
WHERE ((organization_id = cr.organization_id
AND inventory_item_id = cr.inventory_item_id)
OR (organization_id = cr.organization_id
AND item_number = cr.item_number))
AND revision = cr.revision
AND process_flag = 1--Bug No: 3344480
AND set_process_id = cr.set_process_id;
/*** insert a record into the revs interface table because one does not exist
** NP 06MAY96 Now inserting xset_id into set_process_id for MIRI
** NP 28MAY96 Choose the set_process_id of the relevant record for insertion to MIRI
** not the xset_id*/
l_transaction_type := 'CREATE' ;
--Bug 4626774 added request_id in both inserts
--Adding Source System Id and Source System Reference to ensure Sequence Generated Item Number propogation
insert into mtl_item_revisions_interface
(organization_id, inventory_item_id, revision,
process_flag, transaction_type, set_process_id, implementation_date,
effectivity_date, creation_date, last_update_date
,request_id, source_system_id, source_system_reference)
values(cr.organization_id, cr.inventory_item_id, cr.revision,
l_process_flag_1, l_transaction_type, cr.set_process_id, l_curr_sysdate,
l_curr_sysdate, l_curr_sysdate, l_curr_sysdate
,request_id, cr.source_system_id, cr.source_system_reference);
insert into mtl_item_revisions_interface
(organization_id, inventory_item_id, revision,
process_flag, transaction_type, set_process_id
,request_id, source_system_id, source_system_reference)
values (cr.organization_id, cr.inventory_item_id, cr.revision,
l_process_flag_1, l_transaction_type, cr.set_process_id
,request_id, cr.source_system_id, cr.source_system_reference);
select count(revision) into revs
from mtl_item_revisions_interface
where set_process_id = xset_id
and revision = default_rev
AND process_flag = 1--Bug No: 3344480
and ((organization_id = cr.organization_id
and inventory_item_id = cr.inventory_item_id)
or(organization_id = cr.organization_id
and item_number = cr.item_number));
/*** insert a record into the revs interface table because one does not exist
** for the DEFAULT STARTING REVISION
** Included implementation_date, effectivity_date , creation_date and last_update_date in the below
sql query to insert sysdate value for bug fix 3226359 */
l_transaction_type := 'CREATE' ;
--Bug 4626774 added request_id in the insert
--Adding Source System Id and Source System Reference to ensure Sequence Generated Item Number propogation
insert into mtl_item_revisions_interface -- create a default revision with sysdate - 1 sec
(organization_id, inventory_item_id, revision,
process_flag, transaction_type, set_process_id,implementation_date,
effectivity_date,creation_date,last_update_date
,request_id, source_system_id, source_system_reference)
values (cr.organization_id, cr.inventory_item_id, default_rev,
l_process_flag_1, l_transaction_type, cr.set_process_id, l_curr_sysdate,
l_curr_sysdate, l_curr_sysdate,l_curr_sysdate
,request_id, cr.source_system_id, cr.source_system_reference);
/*** we finished one record assignment, let's update it*/
if (validation_check_status = 0) then
if (rtn_status = 0) then
if default_flag = 1 then
process_flag_temp := 2;
update MTL_SYSTEM_ITEMS_INTERFACE
set process_flag = process_flag_temp,
creation_date = nvl(creation_date, l_sysdate),
revision = cr.revision
where rowid = cr.rowid ;
update mtl_system_items_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid ;