Load Maximo 7.X classification
Loading classification data in Maximo could be quite complex, especially when it is being migrated from an earlier version of Maximo along with reference asset, location, item etc. data. Here is a techie's scratchpad for loading classification, read one line at a time :)
--**********************************************************************************************
Classification Data movement/merge
--**********************************************************************************************
2. LOAD MEASUREUNIT DATA
SELECT COUNT(*) FROM MAXSTG.MEASUREUNIT -- 77
SELECT * FROM MAXSTG.MEASUREUNIT
SELECT COUNT(*) FROM MEASUREUNIT -- 110, 189, 266
insert /*+ APPEND */ into MEASUREUNIT ( ABBREVIATION,CONTENTUID,DESCRIPTION,MEASUREUNITID,MEASUREUNITUID,
ORGID,SITEID, OLD_MEASUREUNITUID)
(SELECT ABBREVIATION,CONTENTUID,DESCRIPTION,MEASUREUNITID,MEASUREUNITSEQ.NEXTVAL,'XXXXX','STG',
MEASUREUNITUID from MAXSTG.MEASUREUNIT where measureunitid = 'FT' and rownum <=1 ) -- 76, 1 INSERTED
-- 'FT' defined twice in the measureunit table once at system level another at org level. Ignoring one
select distinct orgid, siteid from measureunit
select measureunitid, 'XXXXX' orgid, 'STG' siteid--, count(*)
from maxSTG.measureunit
group by measureunitid, orgid, siteid
having count(*) > 1
update maxSTG.measureunit set orgid = 'XXXXX', siteid = 'STG'
select * from maxSTG.measureunit where measureunitid = 'FT'
select measureunitid, count(*)
from maxSTG.measureunit
group by measureunitid
having count(*) > 1
select *
from measureunit m
inner join maxSTG.measureunit cm
on m.measureunitid = cm.measureunitid and m.orgid = cm.orgid and m.siteid = cm.siteid
select distinct orgid, siteid from maximo.measureunit
COMMIT;
3. LOAD ASSETATTRIBUTE DATA
select * from MAXSTG.assetattribute
select count(*) from MAXSTG.assetattribute -- 19084 records
select count(*) from assetattribute -- 18693, 36995, 56078
insert /*+ APPEND */ into ASSETATTRIBUTE
( DOMAINID,MEASUREUNITID,ORGID,DESCRIPTION,DATATYPE,ATTRDESCPREFIX,ASSETATTRID,SITEID,AA05,ASSETATTRIBUTEID,
OLD_ASSETATTRIBUTEID)
(select DOMAINID,MEASUREUNITID,'XXXXX',DESCRIPTION,DATATYPE,ATTRDESCPREFIX,ASSETATTRID,'STG',AA05,
ASSETATTRIBUTESEQ.NEXTVAL, ASSETATTRIBUTEID from MAXSTG.ASSETATTRIBUTE) -- 19084 INSERTED
commit;
-- DATA RESTRICTION NEEDED
4. Load classification data
select * from MAXSTG.classification
select count(*) from MAXSTG.classification -- 1621 records
select count(*) from classification -- 1437, 1470
insert /*+ APPEND */ into CLASSIFICATION ( CLASSIFICATIONID,CLASSIFICATIONUID,DESCRIPTION,HASLD,
ORGID,SITEID, OLD_CLASSIFICATIONUID)
(select CLASSIFICATIONID,CLASSIFICATIONSEQ.NEXTVAL,DESCRIPTION,HASLD,'XXXXX','STG', CLASSIFICATIONUID
from MAXSTG.CLASSIFICATION) -- 1621
commit;
-- data restriction needed
6. load classstructure data
select * from MAXSTG.classstructure
select count(*) from MAXSTG.classstructure -- 7461
-- Note :: 1. Generate new classstructureid and classstructureUID (already taken care in script)
-- 2. classstructureid is autokey configured and generated by sequence.
SELECT * FROM MAXSEQUENCE WHERE TBNAME LIKE 'CLASS%'
SELECT * FROM AUTOKEY WHERE AUTOKEYNAME LIKE 'CLASS%' -- CREATE A NEW SEQUENCE TO GENERATE VALUE AS PER
--THE AUTOKEY VALUE
CREATE SEQUENCE DM_CLASSSTRUCTID START WITH 1000 INCREMENT BY 1; -- CREATED
SELECT DM_CLASSSTRUCTID.NEXTVAL FROM DUAL
ALTER SEQUENCE DM_CLASSSTRUCTID INCREMENT BY 1;
DROP SEQUENCE DM_CLASSSTRUCTID
-- 3. Keep track of old classstructureid
select * from classstructure -- OLD_CLASSSTRUCTUREID ALREADY PRESENT
insert /*+ APPEND */ into CLASSSTRUCTURE ( ACS01, ACS02,CLASSIFICATIONID,CLASSSTRUCTUREID,
CLASSSTRUCTUREUID,DESCRIPTION,GENASSETDESC,HASCHILDREN,HASLD,LANGCODE,ORGID,PARENT,SITEID,
TYPE,USECLASSINDESC, OLD_CLASSSTRUCTUREUID, OLD_CLASSSTRUCTUREID)
(select ACS01, ACS02,CLASSIFICATIONID,DM_CLASSSTRUCTID.NEXTVAL,CLASSSTRUCTURESEQ.NEXTVAL,
DESCRIPTION,GENASSETDESC,HASCHILDREN,HASLD,LANGCODE,'XXXXX',PARENT,'STG',TYPE,USECLASSINDESC,
CLASSSTRUCTUREUID, CLASSSTRUCTUREID from MAXSTG.CLASSSTRUCTURE) -- 7461 inserted
-- ACS01, ACS02 not present in STG
insert into temp (classstructureid, old_classstructureid, parent, siteid)
(select classstructureid, old_classstructureid, parent, siteid
from classstructure
where siteid = 'STG') -- 7142 inserted
select * from temp where siteid = 'STG'
update classstructure O
set O.parent = (select distinct I.classstructureid
from temp I
WHERE I.OLD_CLASSSTRUCTUREID = O.PARENT AND I.SITEID = 'STG')
where O.parent is not null and siteid = 'STG' -- 4543 updated
select * from classstructure where siteid = 'STG'
-- 5. Update AUTOKEY value
select seed from autokey where autokeyname = 'CLASSSTRUCTUREID'
select max(classstructureid) from classstructure
--AUTOKEY seed automatically sets to correct value when a new record is created from front end.
5. Load classancestor data
select count(*) from classancestor -- 0
select count(*) from MAXSTG.classancestor --
-- why classancestor table is empty for TBU have we missed something during upgrade or table is truncated by mistake
-- class ancestor is at system level, it has unique index defined for ancestor and classstructure.
-- Merging put on hold, need to find usage of this object in classification application.
update maxSTG.classancestor O
SET CLASSSTRUCTUREID = (SELECT CLASSSTRUCTUREID FROM TEMP WHERE OLD_CLASSSTRUCTUREID = O.CLASSSTRUCTUREID AND SITEID = 'STG')
where classstructureid in (select old_classstructureid from temp where siteid = 'STG') -- 11685 updated
update maxSTG.classancestor O
SET ANCESTOR = (SELECT CLASSSTRUCTUREID FROM TEMP WHERE OLD_CLASSSTRUCTUREID = O.ANCESTOR AND SITEID = 'STG')
where ANCESTOR in (select old_classstructureid from temp where siteid = 'STG') -- 11685 updated
select description from maxobject where objectname = 'CLASSANCESTOR' -- Class Structure Ancestors
select * from classancestor
alter table classancestor ADD old_classstructureID number
SELECT * FROM MAXSEQUENCE WHERE NAME = 'CLASSANCESTORID'
SELECT CLASSANCESTORUSEQ.NEXTVAL FROM DUAL
INSERT /*+ APPEND */ into CLASSANCESTOR (CLASSANCESTORID,CLASSSTRUCTUREID,CLASSIFICATIONID,ANCESTOR,ANCESTORCLASSID,
HIERARCHYLEVELS,ORGID,SITEID,OLD_CLASSANCESTORID)
(select CLASSANCESTORUSEQ.NEXTVAL,CLASSSTRUCTUREID,CLASSIFICATIONID,ANCESTOR,ANCESTORCLASSID,
HIERARCHYLEVELS,'XXXXX','STG',CLASSANCESTORID
from MAXSTG.CLASSANCESTOR) -- 11685
select max(classancestorid) from classancestor
select classstructureid, ancestor, count(*)
from maxSTG.classancestor
group by classstructureid, ancestor
having count(*) > 1
--; 7. Load classspec data
select * from MAXSTG.classspec
select count(*) from MAXSTG.classspec -- 27450
insert /*+ APPEND */ into CLASSSPEC ( APPLYDOWNHIER,ASSETATTRIBUTEID,ASSETATTRID,ATTRDESCPREFIX,
CLASSSPECID,CLASSSTRUCTUREID,CONTINUOUS,CS01,CS02,CS03,CS04,CS05,DOMAINID,
INHERITEDFROM,INHERITEDFROMID,LINEARTYPE,LINKEDTOATTRIBUTE,LINKEDTOSECTION,LOOKUPNAME,
MEASUREUNITID,ORGID,SECTION,SITEID,TABLEATTRIBUTE, OLD_CLASSSPECID)
(select APPLYDOWNHIER,ASSETATTRIBUTEID,ASSETATTRID,ATTRDESCPREFIX,CLASSSPECSEQ.NEXTVAL,
CLASSSTRUCTUREID,CONTINUOUS,CS01,CS02,CS03,CS04,CS05,DOMAINID,INHERITEDFROM,INHERITEDFROMID,
LINEARTYPE,LINKEDTOATTRIBUTE,LINKEDTOSECTION,LOOKUPNAME,MEASUREUNITID,'XXXXX',SECTION,'STG',
TABLEATTRIBUTE, CLASSSPECID from MAXSTG.CLASSSPEC) -- 27541 inserted
-- update classstructureid
update classspec O
set classstructureid = (select classstructureid
FROM TEMP WHERE OLD_CLASSSTRUCTUREID = O.CLASSSTRUCTUREID AND SITEID = 'STG')
WHERE SITEID = 'STG' AND CLASSSTRUCTUREID IN (SELECT OLD_CLASSSTRUCTUREID FROM CLASSSTRUCTURE WHERE ORGID = 'XXXXX')
-- 26184 updated
update classspec O
set assetattributeid = (select assetattributeid
from assetattribute
WHERE ASSETATTRID = O.ASSETATTRID AND SITEID = 'STG')
WHERE SITEID = 'STG';
-- 27541 updated
select classstructureid from classspec where siteid = 'STG'
select * from classstructure where classstructureid = 6067
-- 8. load classspecusewith data
select * from MAXSTG.classspecusewith
select count(*) from MAXSTG.classspecusewith -- 82620
insert /*+ APPEND */ into CLASSSPECUSEWITH ( ASSETATTRID,CLASSSPECID,CLASSSPECUSEWITHID,CLASSSTRUCTUREID,
DEFAULTALNVALUE,DEFAULTNUMVALUE,DEFAULTTABLEVALUE,MANDATORY,OBJECTNAME,OBJECTVALUE,ORGID,
SECTION,SEQUENCE,USEINSPEC,SITEID,USEINDESC, OLD_CLASSSPECUSEWITHID)
(select ASSETATTRID,CLASSSPECID,CLASSSPECUSEWITHSEQ.NEXTVAL,CLASSSTRUCTUREID,DEFAULTALNVALUE,
DEFAULTNUMVALUE,DEFAULTTABLEVALUE,MANDATORY,OBJECTNAME,OBJECTVALUE,'XXXXX',SECTION,SEQUENCE,
USEINSPEC,'STG',USEINDESC, CLASSSPECUSEWITHID from MAXSTG.CLASSSPECUSEWITH) -- 288024 inserted
-- update classspecid
update classspecusewith O
set CLASSSPECID = (select CLASSSPECID from classspec
where old_classspecid = O.classspecid
AND SITEID = 'STG') -- 55359 rows updated
where siteid = 'STG' -- 288024 updated
-- update classstructureid
update classspecusewith O
set CLASSstructureid = (select CLASSstructureid
from temp
WHERE OLD_CLASSSTRUCTUREID = O.CLASSSTRUCTUREID AND SITEID = 'STG') -- rows updated
where siteid = 'STG' -- 288024 updated
-- 9. load classusewith data
select * from MAXSTG.classusewith
select count(*) from MAXSTG.classusewith -- 82071
insert /*+ APPEND */ into CLASSUSEWITH ( CLASSSTRUCTUREID,CLASSUSEWITHID,DESCRIPTION,OBJECTNAME,
OBJECTVALUE,TOPLEVEL, OLD_CLASSUSEWITHID)
(SELECT CLASSSTRUCTUREID,CLASSUSEWITHSEQ.NEXTVAL,DESCRIPTION,OBJECTNAME,OBJECTVALUE,
TOPLEVEL, CLASSUSEWITHID from MAXSTG.CLASSUSEWITH) -- 78526 inserted
-- update classstructureid
update maxSTG.classusewith O
SET CLASSSTRUCTUREID = (SELECT CLASSSTRUCTUREID FROM TEMP WHERE OLD_CLASSSTRUCTUREID = O.CLASSSTRUCTUREID AND SITEID = 'STG')
where classstructureid in (select old_classstructureid from temp where siteid = 'STG') -- 78562 updated
-- 10. Update AssetSpec
select count(*) from MAXSTG.assetspec where orgid = 'XXXXX'-- 914541 rows
update assetspec O
SET CLASSSTRUCTUREID = (SELECT CLASSSTRUCTUREID FROM TEMP WHERE OLD_CLASSSTRUCTUREID = O.CLASSSTRUCTUREID AND SITEID = 'STG')
where orgid = 'XXXXX' -- 924493 updated
select count(*) from asset where orgid = 'XXXXX' -- 39073
update asset O
SET CLASSSTRUCTUREID = (SELECT CLASSSTRUCTUREID FROM TEMP WHERE OLD_CLASSSTRUCTUREID = O.CLASSSTRUCTUREID AND SITEID = 'STG')
where orgid = 'XXXXX' -- 39131 updated
select classstructureid, assetnum from assetspec where orgid = 'XXXXX'
-- 11. Update locationspec
select classstructureid, location from MAXSTG.locationspec
select classstructureid, location from locationspec where orgid = 'XXXXX' -- this not updated prvsly
select count(*) from MAXSTG.locationspec where orgid = 'XXXXX' -- 14490
update locationspec O
set classstructureid = (select CLASSstructureid from temp where old_CLASSstructureid = O.CLASSstructureid and siteid = 'STG')
where orgid = 'XXXXX' -- 14490 updated
-- update classtructure in loations table
update locations O
SET CLASSSTRUCTUREID = (SELECT CLASSSTRUCTUREID FROM TEMP WHERE OLD_CLASSSTRUCTUREID = O.CLASSSTRUCTUREID AND SITEID = 'STG')
where orgid = 'XXXXX' -- 90281 updated
select * from locations where orgid = 'XXXXX' and classstructureid is not null
select classstructureid, location from maxSTG.locations where orgid = 'XXXXX' and classstructureid is not null
-- 12. Update itemspec
select classstructureid, itemnum, itemsetid from itemspec
select classstructureid, itemnum, itemsetid from MAXSTG.itemspec where itemsetid = 'CHPISET'
select count(*) from itemspec where orgid = 'XXXXX' -- 0
select distinct itemsetid from itemspec
select count(*) from itemspec
update itemspec O
SET CLASSSTRUCTUREID = (SELECT CLASSSTRUCTUREID FROM TEMP WHERE OLD_CLASSSTRUCTUREID = O.CLASSSTRUCTUREID AND SITEID = 'STG')
where orgid = 'XXXXX' -- 236975 updated
update item O
SET CLASSSTRUCTUREID = (SELECT CLASSSTRUCTUREID FROM TEMP WHERE OLD_CLASSSTRUCTUREID = O.CLASSSTRUCTUREID AND SITEID = 'STG')
where itemsetid = 'STGISET' -- 38492 updated
--**********************************************************************************************