An onComplete Transform Script

on_complete.js

//***** CM 5/14/2014 *****
//Find the missing departments. When a dept is closed in PeopleSoft it does not show up in the Oracle DB View
//http://wiki.servicenow.com/?title=Transform_Map_Scripts

//Find the sys_id of the iSetNum you are working with
//'import_set' is an object that holds information about the import set your working with.
var isetNum = getSysId (import_set.number,'sys_import_set');
gs.log("isetNum: " + isetNum + " Table: " + import_set.table_name);
var grImp = new GlideRecord(import_set.table_name);  //To look for missing depts
var ccount = 0;
//Get the departments
var grDept = new GlideRecord('cmn_department');
grDept.query(); // lets get them all...

while(grDept.next()){
    
    //Table was u_depts_from_ps on 5/15/2014
    grImp.initialize();
	grImp.addQuery('sys_import_set', isetNum); //this reflect the current import set
    grImp.addQuery('u_deptid', grDept.id);  //This is the department I'm looking for
    grImp.query();
    
    if(!grImp.next()){
        //I did not match what I found on the Dept table to the dept import set
		//gs.log("Did not Find: " + grDept.id);
		//This is where I'm going to process the missing departments
		ccount++;
    }
    
}

gs.log("dpt count: " + ccount);
//Helper function to get sys_id
function getSysId (itemName,tableName){
    //Todo: add varable incase the 'name' field is not correct
    var gdRec = new GlideRecord(tableName);
    gdRec.addQuery('number', '=', itemName);
    gdRec.query();
    if(gdRec.next()){
        return gdRec.sys_id;
    }else{
        return false;
    }
    
}