ServiceNow Oracle Integration Guide

// Advanced transform script for PO line item processing
var sourceAmount = source.u_line_amount || '0';
var currencyCode = source.u_currency || 'USD';
var convertedAmount = parseFloat(sourceAmount);

// Apply currency conversion if needed
if (currencyCode != 'USD') {
    var gr = new GlideRecord('fx_rate');
    gr.addQuery('from_currency', currencyCode);
    gr.addQuery('to_currency', 'USD');
    gr.orderByDesc('sys_created_on');
    gr.setLimit(1);
    gr.query();
    if (gr.next()) {
        convertedAmount = convertedAmount * parseFloat(gr.rate);
    }
}
target.amount = convertedAmount.toString();

// Scheduled job script for Oracle asset synchronization
var integrationHub = new sn_ih.IntegrationHub();
var action = integrationHub.getAction('Oracle', 'Get Assets');
var inputs = {
    'connection': 'oracle_erp_connection',
    'asset_category': 'IT_HARDWARE',
    'modified_since': new GlideDateTime().addDaysLocalTime(-1).toString()
};

try {
    var result = action.execute(inputs);
    if (result.haveError) {
        gs.error('Oracle Asset Sync Error: ' + result.errorMessage);
        return;
    }
    
    // Process returned assets
    var assets = JSON.parse(result.responseBody);
    gs.info('Processing ' + assets.length + ' assets from Oracle');
    
    // Transform and insert into CMDB
    var transform = new GlideTransform();
    transform.setTransformMap('Oracle_Asset_to_CMDB');
    transform.setSource(assets);
    transform.execute();
} catch (e) {
    gs.error('Oracle integration error: ' + e.message);
}

// Scripted REST API resource for Oracle service request creation
(function process(request, response) {
    try {
        var requestBody = request.body.data;
        var oraclePayload = JSON.parse(requestBody);
        
        // Validate required Oracle fields
        if (!oraclePayload.request_type || !oraclePayload.oracle_user_id) {
            response.setStatus(400);
            response.setBody({error: 'Missing required Oracle fields'});
            return;
        }
        
        // Create ServiceNow service request
        var sr = new GlideRecord('sc_request');
        sr.initialize();
        sr.short_description = 'Oracle ERP Request: ' + oraclePayload.subject;
        sr.description = oraclePayload.description;
        sr.u_oracle_request_id = oraclePayload.oracle_request_id;
        sr.priority = mapOraclePriority(oraclePayload.priority);
        sr.assignment_group = determineAssignmentGroup(oraclePayload.request_type);
        sr.opened_by = findServiceNowUser(oraclePayload.oracle_user_id);
        
        var sysId = sr.insert();
        
        response.setStatus(201);
        response.setBody({servicenow_request: sysId, status: 'created'});
        
    } catch (e) {
        gs.error('Oracle SR Creation Error: ' + e.message);
        response.setStatus(500);
        response.setBody({error: 'Internal processing error'});
    }
})(request, response);

// REST Message SOAP call for Oracle ERP legacy services
var rm = new RESTMessage('Oracle ERP SOAP Services', 'Purchase Order Status Update');
rm.setStringParameterNoEscape('soap_envelope', buildPOStatusSOAP(poNumber, newStatus));
rm.setRequestHeader('SOAPAction', 'http://oracle.com/apps/po/updatePOStatus');
rm.setRequestHeader('Authorization', 'Bearer ' + getOracleAuthToken());

var response = rm.execute();
if (response.getStatusCode() == 200) {
    var responseXML = response.getBody();
    var xmlHelper = new XMLHelper(responseXML);
    var status = xmlHelper.getNodeText('//soap:Body//status');
    gs.info('Oracle PO update status: ' + status);
} else {
    gs.error('Oracle SOAP call failed: ' + response.getStatusCode() + ' - ' + response.getBody());
}

function buildPOStatusSOAP(poNum, status) {
    return '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">' +
           '<soap:Body><updatePOStatus><poNumber>' + poNum + '</poNumber>' +
           '<status>' + status + '</status></updatePOStatus></soap:Body></soap:Envelope>';
}

// Custom error handling and logging utility
var OracleIntegrationLogger = Class.create();
OracleIntegrationLogger.prototype = {
    initialize: function() {
        this.logTable = 'u_oracle_integration_log';
    },
    
    logAPICall: function(action, endpoint, responseTime, status, errorMsg) {
        var gr = new GlideRecord(this.logTable);
        gr.initialize();
        gr.action = action;
        gr.endpoint = endpoint;
        gr.response_time_ms = responseTime;
        gr.status = status;
        gr.error_message = errorMsg || '';
        gr.integration_user = gs.getUserID();
        gr.insert();
        
        // Log to system log as well
        if (status != 'SUCCESS') {
            gs.error('Oracle Integration Error - Action: ' + action + ', Error: ' + errorMsg);
            
            // Trigger alert for critical errors
            if (status == 'AUTH_FAILURE' || status == 'QUOTA_EXCEEDED') {
                gs.eventQueue('oracle.integration.critical_error', null, action, errorMsg);
            }
        }
    },
    
    type: 'OracleIntegrationLogger'
};

// Performance monitoring and optimization script
var OraclePerformanceMonitor = {
    testConnection: function(connectionAlias) {
        var startTime = new GlideDateTime();
        var testMessage = new RESTMessage();
        testMessage.setEndpoint('https://' + connectionAlias + '/fscmRestApi/resources/11.13.18.05/purchaseOrders?limit=1');
        testMessage.setHttpMethod('GET');
        testMessage.setRequestHeader('Authorization', 'Bearer ' + this.getAuthToken());
        
        var response = testMessage.execute();
        var endTime = new GlideDateTime();
        var responseTime = GlideDateTime.subtract(startTime, endTime).getNumericValue();
        
        var result = {
            status: response.getStatusCode(),
            responseTime: Math.abs(responseTime),
            connectionHealth: response.getStatusCode() == 200 ? 'HEALTHY' : 'FAILED'
        };
        
        gs.info('Oracle connection test - Status: ' + result.status + ', Response time: ' + result.responseTime + 'ms');
        return result;
    },
    
    optimizeBatchSize: function(recordCount) {
        // Dynamic batch sizing based on record volume
        if (recordCount < 100) return 25;
        if (recordCount < 1000) return 100;
        if (recordCount < 10000) return 500;
        return 1000;
    }
};