[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqltxt VARCHAR2(9999); -- SQL select statement
select inventory_item_status_code status
from mtl_item_status_tl
order by inventory_item_status_code;
select inventory_item_status_code,substr(attribute_name,18) attribute_name,attribute_value
from mtl_stat_attrib_values_all_v
where status_code_ndb = 1
and inventory_item_status_code=l_item_status_code
order by attribute_name;
select index_name
from all_indexes
where table_name = 'MTL_SYSTEM_ITEMS_B'
and owner = l_owner
and UNIQUENESS = 'UNIQUE';
JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
select 1 into l_org_exists
from mtl_parameters
where organization_id = l_org_id;
sqltxt := 'SELECT mif.PADDED_ITEM_NUMBER "Item Number", '||
' mp.organization_code "Org Code", '||
' mif.inventory_item_id "Item Id", '||
' mif.organization_id "Org Id" '||
' FROM mtl_item_flexfields mif , mtl_parameters mp '||
' WHERE (mif.organization_id,PADDED_ITEM_NUMBER) in '||
' (select organization_id , padded_item_number '||
' from mtl_item_flexfields '||
' group by organization_Id, PADDED_ITEM_NUMBER '||
' having count(*) > 1 ) '||
' AND mif.organization_id = mp.organization_id ';
To rename the Master Item, make use of the below mentioned update script.
Case 2: Both Child and Master org duplicates:
If the Duplicate Item is present in both Master and Child orgs , then user can
choose any one of the duplicated items and rename the same in the Master org.
To rename the Master Item, make use of the below mentioned update script.
Please use the below update statements to rename the Master Item.
Important: Please try these scripts on a TEST instance first.
The below update statement can be used to correct a specific item.
Substitute the bind variables orgid and itemid with the appropriate values.
update mtl_system_items_b
set segment1 = ''segment1'' || ''_DUP''
where organization_id = :orgid
and inventory_item_id = :itemid;
Line: 192
Use ''Delete Items'' form to delete the renamed item.
';
Line: 229
sqltxt := 'select segment_num "Segment Number", segment_name "Segment Name", '||
' application_column_name "Column Name", required_flag "Required Flag" '||
' from fnd_id_flex_segments_vl '||
' where application_id = 401 '||
' and id_flex_code = ''MSTK'' '||
' and id_flex_num = 101 '||
' and enabled_flag = ''Y'' '||
' order by segment_num ';
Line: 266
Select 1 into l_seg_count from dual
where not exists (
(select APPLICATION_COLUMN_NAME "Column_Name"
from FND_ID_FLEX_SEGMENTS
where ID_FLEX_CODE = 'MSTK'
and enabled_flag = 'Y'
MINUS
select ori.logical_column_name "Column_Name" from (select * from (select
tab.owner
, tab.table_name
, col.table_name real_table_name
, decode(ev.view_name, NULL, col.column_name, evc.view_column_name) logical_column_name
, col.column_name real_column_name
from
dba_tables tab
, dba_tab_columns col
, dba_editioning_views ev
, dba_editioning_view_cols evc
WHERE tab.table_name = 'MTL_SYSTEM_ITEMS_B'
and col.owner = tab.owner
and col.table_name = tab.table_name
and ev.owner(+) = tab.owner
and ev.table_name(+) = tab.table_name
and evc.owner(+) = ev.owner
AND evc.view_name(+) = ev.view_name
AND (ev.view_name IS NULL OR evc.table_column_name = col.column_name)) WHERE logical_column_name LIKE 'SEGMENT%') ori, all_ind_columns aic
WHERE ori.real_column_name = aic.column_name
and ori.real_table_name = aic.table_name
AND aic.index_name =l_index.index_name
AND aic.index_owner=l_oracle_schema)
UNION
(
select ori.logical_column_name "Column_Name" from (select * from (select
tab.owner
, tab.table_name
, col.table_name real_table_name
, decode(ev.view_name, NULL, col.column_name, evc.view_column_name) logical_column_name
, col.column_name real_column_name
from
dba_tables tab
, dba_tab_columns col
, dba_editioning_views ev
, dba_editioning_view_cols evc
WHERE tab.table_name = 'MTL_SYSTEM_ITEMS_B'
and col.owner = tab.owner
and col.table_name = tab.table_name
and ev.owner(+) = tab.owner
and ev.table_name(+) = tab.table_name
and evc.owner(+) = ev.owner
AND evc.view_name(+) = ev.view_name
AND (ev.view_name IS NULL OR evc.table_column_name = col.column_name)) WHERE logical_column_name LIKE 'SEGMENT%') ori, all_ind_columns aic
WHERE ori.real_column_name = aic.column_name
and ori.real_table_name = aic.table_name
AND aic.index_name =l_index.index_name
AND aic.index_owner=l_oracle_schema
MINUS
select APPLICATION_COLUMN_NAME "Column_Name"
from FND_ID_FLEX_SEGMENTS
where ID_FLEX_CODE = 'MSTK'
and enabled_flag = 'Y')
);
Line: 330
select 1 into l_org_col_exists
from (select * from (select
tab.owner
, tab.table_name
, col.table_name real_table_name
, decode(ev.view_name, NULL, col.column_name, evc.view_column_name) logical_column_name
, col.column_name real_column_name
from
dba_tables tab
, dba_tab_columns col
, dba_editioning_views ev
, dba_editioning_view_cols evc
WHERE tab.table_name = 'MTL_SYSTEM_ITEMS_B'
and col.owner = tab.owner
and col.table_name = tab.table_name
and ev.owner(+) = tab.owner
and ev.table_name(+) = tab.table_name
and evc.owner(+) = ev.owner
AND evc.view_name(+) = ev.view_name
AND (ev.view_name IS NULL OR evc.table_column_name = col.column_name)) WHERE logical_column_name = 'ORGANIZATION_ID') ori, all_ind_columns aic
WHERE ori.real_column_name = aic.column_name
and ori.real_table_name = aic.table_name
AND aic.index_name =l_index.index_name
AND aic.index_owner=l_oracle_schema;
Line: 385
sqltxt := ' SELECT glcc.PADDED_CONCATENATED_SEGMENTS "Code Combination", '||
' mif.PADDED_ITEM_NUMBER "Item Number", '||
' mp.organization_code "Org Code", '||
' glcc.code_combination_id "Code Combination Id", '||
' mif.inventory_item_id "Item Id", '||
' mp.organization_id "Org Id", '||
' decode(mif.cost_of_sales_account,glcc.code_combination_Id,''X'') "Cost Of Sales Account", '||
' decode(mif.sales_account,glcc.code_combination_Id,''X'') "Sales Account", '||
' decode(mif.expense_account,glcc.code_combination_Id,''X'') "Expense Account", '||
' decode(mif.encumbrance_account,glcc.code_combination_Id,''X'') "Encumbrance Account" '||
' FROM gl_code_combinations_kfv glcc, mtl_item_flexfields mif , mtl_parameters mp '||
' WHERE glcc.code_combination_id in (mif.cost_of_sales_account, mif.SALES_ACCOUNT, '||
' mif.EXPENSE_ACCOUNT, mif.ENCUMBRANCE_ACCOUNT) '||
' AND nvl(glcc.END_DATE_ACTIVE,sysdate) < sysdate '||
' AND DETAIL_POSTING_ALLOWED = ''Y'' '||
' AND CHART_OF_ACCOUNTS_ID in (select chart_of_accounts_id '||
' from ORG_ORGANIZATION_DEFINITIONS ood '||
' where ood.organization_id = mif.organization_id and rownum = 1) '||
' AND mif.organization_id = mp.organization_id ';
Line: 453
sqltxt := 'SELECT mif.PADDED_ITEM_NUMBER "Item Number", '||
' mp.Organization_code "Org Code", '||
' mif.inventory_item_id "Item Id", '||
' mif.organization_id "Org Id" '||
' FROM mtl_item_flexfields mif , mtl_parameters mp '||
' WHERE Not Exists (SELECT ''x'' '||
' FROM mtl_system_items_tl msitl '||
' WHERE msitl.organization_id = mif.organization_id '||
' AND msitl.inventory_item_id = mif.inventory_item_id) '||
' AND mif.organization_id = mp.organization_id ';
Line: 497
sqltxt := ' select '||
' mif.padded_item_number "PADDED ITEM NUMBER", '||
' mp.organization_code "ORGANIZATION CODE", '||
' mif.costing_enabled_flag "COSTING ENABLED FLAG", '||
' mif.inventory_asset_flag "INVENTORY ASSET FLAG", '||
' mif.inventory_item_id "INVENTORY ITEM ID", '||
' mp.organization_id "ORGANIZATION ID" '||
' from mtl_item_flexfields mif, mtl_parameters mp '||
' where mif.organization_id = mp.organization_id '||
' and mif.costing_enabled_flag = ''Y'' '||
' and not exists (select null '||
' from cst_item_costs cic '||
' where cic.organization_id = mif.organization_id '||
' and cic.inventory_item_id = mif.inventory_item_id) ';
Line: 538
4) Select ''Costing Enabled'' checkbox. Select ''Inventory Asset Value'' checkbox if required.
If these checkboxes are already checked, then uncheck them first and then check again.
5) Save changes to this item.
Case 2:(On Hand Quantity exists for the item in the corresponding organization)
1) Issue out the on hand quantity for this item.
Now NO On Hand Quantity exists for this item.
2) Follow the steps in case 1 to enable the costing flags.
3) Receive the quantity back into the organization.
Caution:Costing Flags should be flipped only when NO On Hand Qty exists for an Item.
Otherwise it will lead to severe data corruption.
Note: If the number of items to corrected are huge, then Item Import functionality
can also be used for flipping the costing flags.
For details on Item Import, please refer to
''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
';
Line: 574
sqltxt := ' select '||
' mif.padded_item_number "item number", '||
' mp.organization_code "org code", '||
' mif.tracking_quantity_ind "tracking quantity ind", '||
' mif.ont_pricing_qty_source "ont pricing qty source", '||
' mif.secondary_uom_code "secondary uom code", '||
' mif.inventory_item_id "inventory item id", '||
' mif.organization_id "organization id" '||
' from mtl_item_flexfields mif, mtl_parameters mp '||
' where mif.organization_id=mp.organization_id '||
' and nvl(mif.tracking_quantity_ind,''P'') = ''P'' '||
' and nvl(mif.ont_pricing_qty_source,''P'') = ''P'' '||
' and mif.secondary_uom_code is not null ';
Line: 607
Item import process cannot be run to update any of the Item attributes
as users will receive the following error message.
''If tracking and pricing are set to Primary,
then Secondary Unit of measure can only have a null value. '' ');
Line: 613
Use Item Import in UPDATE mode to update the column SECONDARY_UOM_CODE
as NULL for these Items.
To null out varchar attributes through Item Import, the value ''!'' (exclamation mark)
has to be populated for the corresponding column in the interface record.
For details on Item Import, please refer to
''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
';
Line: 634
sqltxt := ' select '||
' mif.padded_item_number "Item Number", '||
' mp.organization_code "Organization Code", '||
' pav.agent_name "Default Buyer", '||
' to_char(pav.start_date_active,''DD-MON-YYYY HH24:MI:SS'') "Start Date Active", '||
' to_char(pav.end_date_active,''DD-MON-YYYY HH24:MI:SS'') "End Date Active", '||
' mif.inventory_item_id "Inventory Item Id", '||
' mif.organization_id "Organization Id", '||
' mif.buyer_id "Default Buyer Id" '||
' from mtl_item_flexfields mif, mtl_parameters mp, po_agents_v pav '||
' where mif.organization_id=mp.organization_id '||
' and mif.buyer_id=pav.agent_id '||
' and sysdate not between nvl(pav.start_date_active, sysdate-1) '||
' and nvl(pav.end_date_active, sysdate+1) ';
Line: 665
Item import process cannot be run to update any of the Item attributes as users
will receive error messages corresponding to inactive buyers. ');
Line: 684
/*
Note: If the number of items to be corrected are huge, then use Item Import in UPDATE mode to null out the buyer_id column.
For nulling out numeric attributes through IOI the value -999999 has to be populated for the corresponding column in the interface record.
*/
End If; /* End of Impact and Action */
Line: 699
sqltxt := ' select '||
' mif.padded_item_number "Item Number", '||
' mp.organization_code "Organization Code", '||
' mpl.planner_code "Planner", '||
' to_char(mpl.disable_date,''DD-MON-YYYY HH24:MI:SS'') "Planner Disable Date", '||
' mif.inventory_item_id "Inventory Item Id", '||
' mif.organization_id "Organization Id" '||
' from mtl_item_flexfields mif, mtl_parameters mp, mtl_planners mpl '||
' where mif.organization_id=mp.organization_id '||
' and mif.organization_id=mpl.organization_id '||
' and mif.planner_code=mpl.planner_code '||
' and nvl(mpl.disable_date, sysdate+1) < sysdate ';
Line: 729
Item import process cannot be run to update any of the Item attributes as users
will receive error messages corresponding to invalid planners. ');
Line: 744
use Item Import in UPDATE mode to update the column PLANNER_CODE
for these Items to either a NULL value or a valid Planner.
To null out varchar attributes through Item Import, the value ''!'' (exclamation mark)
has to be populated for the corresponding column in the interface record.
For details on Item Import, please refer to
''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
';
Line: 767
sqltxt := ' Select '||
' mif.padded_item_number "Item Number", '||
' mp.organization_code "Org Code", '||
' mif.inventory_item_flag "INVENTORY ITEM FLAG", '||
' mif.purchasing_item_flag "PURCHASING ITEM FLAG", '||
' mif.customer_order_flag "CUSTOMER ORDER FLAG", '||
' mif.internal_order_flag "INTERNAL ORDER FLAG", '||
' mif.invoiceable_item_flag "INVOICEABLE ITEM FLAG", '||
' decode(mif.bom_item_type,1,''Model'',2,''Option Class'',3,''Planning'',4,''Standard'',5,''Product Family'') "BOM ITEM TYPE", '||
' decode(stock_enabled_flag,inventory_item_flag,null,''N'',null,''Y'',stock_enabled_flag) "STOCK ENABLED FLAG", '||
' decode(mtl_transactions_enabled_flag,stock_enabled_flag,null,''N'',null,''Y'',mtl_transactions_enabled_flag) "MTL TRANSACTIONS ENABLED FLAG", '||
' decode(purchasing_enabled_flag,purchasing_item_flag,null,''N'',null,''Y'',purchasing_enabled_flag) "PURCHASING ENABLED FLAG", '||
' decode(customer_order_enabled_flag,customer_order_flag,null,''N'',null,''Y'',customer_order_enabled_flag) "CUSTOMER ORDER ENABLED FLAG", '||
' decode(internal_order_enabled_flag,internal_order_flag,null,''N'',null,''Y'',internal_order_enabled_flag) "INTERNAL ORDER ENABLED FLAG", '||
' decode(invoice_enabled_flag,invoiceable_item_flag,null,''N'',null,''Y'',invoice_enabled_flag) "INVOICE ENABLED FLAG", '||
' decode(build_in_wip_flag,inventory_item_flag,decode(build_in_wip_flag,decode(bom_item_type,1,''N'',2,''N'',3,''N'',5,''N'',build_in_wip_flag),null,build_in_wip_flag), '||
' ''N'',decode(build_in_wip_flag,decode(bom_item_type,1,''N'',2,''N'',3,''N'',5,''N'',build_in_wip_flag),null,build_in_wip_flag),'||
' ''Y'',build_in_wip_flag) "BUILD IN WIP FLAG", '||
' mif.inventory_item_id "Inventory Item Id",mif.organization_id "Org Id" '||
' from mtl_item_flexfields mif, mtl_parameters mp '||
' where '||
' mif.organization_id=mp.organization_id '||
' and '||
' ( '||
' ( mif.inventory_item_flag=''N'' and mif.stock_enabled_flag=''Y'') '||
' or (mif.inventory_item_flag=''N'' and mif.mtl_transactions_enabled_flag=''Y'') '||
' or (mif.purchasing_item_flag=''N'' and mif.purchasing_enabled_flag=''Y'') '||
' or (mif.customer_order_flag=''N'' and mif.customer_order_enabled_flag=''Y'') '||
' or (mif.internal_order_flag=''N'' and mif.internal_order_enabled_flag=''Y'') '||
' or (mif.invoiceable_item_flag=''N'' and mif.invoice_enabled_flag=''Y'') '||
' or ( (mif.inventory_item_flag=''N'' or mif.bom_item_type <> 4) and mif.build_in_wip_flag=''Y'') '||
' ) ';
Line: 838
If records are fetched above,then please use Item Import in UPDATE mode
to correct the respective Item Status Attribute value.
For details on Item Import, please refer to
''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
';
Line: 858
delete from bom_diag_temp_tab; -- Clear the temporary tables
Line: 867
/* Insert the fetched records into temporary table bom_diag_temp_tab */
sql_stmt := '';
Line: 869
sql_stmt := ' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id, '||
' char_col1,char_col2,char_col3,char_col4,char_col5,char_col6,char_col7,char_col8,char_col9) '||
' Select mif.inventory_item_id , '||
' mif.organization_Id, '||
' MIF.INVENTORY_ITEM_STATUS_CODE, '||
' MIF.BOM_ENABLED_FLAG, '||
' MIF.BUILD_IN_WIP_FLAG, '||
' MIF.CUSTOMER_ORDER_ENABLED_FLAG, '||
' MIF.INTERNAL_ORDER_ENABLED_FLAG, '||
' MIF.INVOICE_ENABLED_FLAG, '||
' MIF.MTL_TRANSACTIONS_ENABLED_FLAG, '||
' MIF.PURCHASING_ENABLED_FLAG, '||
' MIF.STOCK_ENABLED_FLAG '||
' From mtl_item_flexfields mif '||
' Where 1=1 '||l_where_clause;
Line: 897
sqltxt := ' Select Inventory_Item_Id "Inventory Item Id", '||
' Organization_id "Organization id", '||
' Char_Col1 "Inventory Item Status Code", '||
' Char_Col2 "BOM ENABLED FLAG", '||
' Char_Col3 "BUILD IN WIP FLAG", '||
' Char_Col4 "CUSTOMER ORDER ENABLED FLAG", '||
' Char_Col5 "INTERNAL ORDER ENABLED FLAG", '||
' Char_Col6 "INVOICE ENABLED FLAG", '||
' Char_Col7 "MTL TRANSACTIONS ENABLED FLAG", '||
' Char_Col8 "PURCHASING ENABLED FLAG", '||
' Char_Col9 "STOCK ENABLED FLAG" '||
' From bom_diag_temp_tab where 1=1 ';
Line: 933
sqltxt := ' select attribute_name "Attribute Name" '||
' from mtl_item_attributes '||
' where status_control_code = 1 ';
Line: 940
1) Please use Item Import in UPDATE mode to modify the Item Status Code
which will in turn set the values of all Status Control Attributes.
2) If required, the values of the Status Controlled Attributes that
do NOT use ''Sets Value'' setting, can then be changed.
For details on Item Import, please refer to
''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
');
Line: 963
sqltxt := ' SELECT mif.padded_item_number "Item Number", '||
' mp.organization_code "Org Code", '||
' to_char(mif.start_date_active,''DD-MON-YYYY HH24:MI:SS'') "Start Date Active", '||
' to_char(mif.end_date_active,''DD-MON-YYYY HH24:MI:SS'') "End Date Active", '||
' mif.inventory_item_id "Item Id", '||
' mp.organization_id "Org Id" '||
' FROM mtl_item_flexfields mif , mtl_parameters mp '||
' WHERE 1=1 '||
' AND mif.organization_id=mp.organization_id '||
' AND ( mif.end_date_active IS NOT NULL OR mif.start_date_active IS NOT NULL ) ';
Line: 993
None of the attributes of these items can be updated.
FRM-40654 error may also occur while trying to
update these items through the Items form.
');
Line: 1001
Please use the below update statements to null out these two date fields.
Important: Please try these scripts on a TEST instance first.
The below update statement can be used to correct a specific item.
Substitute the bind variables orgid and itemid
with the appropriate values.
update mtl_system_items_b
set start_date_active = null
,end_date_active = null
where organization_id = :orgid
and inventory_item_id = :itemid;
Line: 1014
The below two update statements can be used
to collectively correct all the problematic items.
update mtl_system_items_b
set start_date_active = null
where start_date_active is not null;
Line: 1021
update mtl_system_items_b
set end_date_active = null
where end_date_active is not null;