importData
이 CCDS 시작 스크립트는 사용 사례에 맞게 JavaScript 개발자가 수정해야 합니다. 수정하지 않으면 작동하지 않습니다.
아래 코드블록을 SAGE Intacct CCDS에 스크립트로 저장하고 이름을 importData로 지정합니다.
function importData(context) { /*** * * Extract data from Sage Intacct * * This code uses the Intacct API (https://developer.intacct.com/api/) to extract General Ledger balances and details, * and also several other tables that may hold data of interest. * * The extracts break out into two basic types: GL data, which requires reporting book and period parameters, and other data, * which does not. * * The staging tables are mostly defined by querying Intacct and replicating the table structure in Adaptive. The one exception * is the GL Balances table. That table is not a replica of a table in Intacct so the layout has to be explicitly defined. * * Data in Intacct is organized in "reporting books", with "Accrual" being the default but others are common. Data has to be extracted * one reporting book at a time. The main extract loop for GL data loops first over all reporting books, and within that loop over * all requested periods. * * There may also be user defined dimensions. The paramater "User Defined Dimensions (comma separated)" holds the names of any user defined dimensions you wish to include. * Once the list has been added, or updated, in the parameter, you must run "import structure" to build the staging tables to hold the * user defined dimension information. The extract itself will automatically include the user defined dimensions. * * Programming note: * Responses from Intacct are in XML format. Two different techniques are used to parse the XML. For smaller responses the non-streaming parser created by ai.xml.createParser() is used. * Most tables are potentially large, so the streaming SAX parser created by ai.xml.sax.createFromUrl() is used instead. * ***/ //global settings const intacctSessionTimeoutTolerance = 600; //seconds; if session timeout is less than this many seconds away get a new session token const maxRows = 1000; //number of rows returned by paged call; 1000 is the maximum var offset = 0; //for query calls, incremental steps if more than maxRows of data returned //Get values from designer settings, and set other variables needed for Intacct API calls var dataSource = context.getDataSource(); //Intacct details var intacctRequestParms = getintacctRequestParms(dataSource); var intacctGLRequestParms = getintacctGLRequestParms(dataSource); var intacctURL = dataSource.getSetting('Intacct URL').getValue(); var method = 'POST'; var headers = { 'Content-Type': 'x-intacct-xml-request' }; var periodRange = dataSource.getSetting('Period Range'); //the Adaptive API is called to get start and end dates of the accounting periods var adaptiveUser = dataSource.getSetting('Adaptive User').getValue(); var adaptivePassword = dataSource.getSetting('Adaptive Password').getValue(); /*** * * validate that all required parameters have valid values * * * some paramaters have defaults; set the defaults if there user made no selection * **/ if (intacctGLRequestParms.reportingBooks === null || intacctGLRequestParms.reportingBooks.trim() === '') intacctGLRequestParms.reportingBooks = 'Accrual'; //default intacctGLRequestParms.reportingBooks = intacctGLRequestParms.reportingBooks.toUpperCase(); if (intacctURL === null || intacctURL.trim() === '') intacctURL = 'https://www.intacct.com/ia/xml/xmlgw.phtml'; //default //user could validly specify no dimensions //if (intacctGLRequestParms.intacctDimensions === null || intacctGLRequestParms.intacctDimensions.trim() === '') intacctGLRequestParms.intacctDimensions = 'Location,Department,Project,Customer,Vendor,Employee,Item,Class'; //default if (intacctGLRequestParms.intacctDimensions === null || intacctGLRequestParms.intacctDimensions.trim() === '') ai.log.logVerbose("No dimensions specified","No dimensions were specified so data will not be split by any dimensions"); /*** * * other required parameters do not have defaults; report a fatal error if empty * ***/ var missingParms = ''; function checkIfEmpty(item) { //item is a 2 element array; the thing to check, and some text for an error message //missingParms is a string that will contain a list of parameters that are empty if (item[0] === null || item[0].trim() === '') missingParms += item[1] + '; '; } [ [intacctRequestParms.userID,'Intacct UserID'], [intacctRequestParms.userPassword,'Intacct Password'], [intacctRequestParms.companyId, 'Company ID'], [intacctRequestParms.senderId,'Sender ID'], [intacctRequestParms.senderPassword,'Sender Password'], [adaptiveUser,'Adaptive User'], [adaptivePassword, 'Adaptive Password'] ].forEach(checkIfEmpty); if (missingParms.length > 0 ) { missingParms = missingParms.substring(0, missingParms.length - 2); //trim last semicolon and space throw('The following required parameter values are empty: ' + missingParms); } var rowsProcessed = 0; var extractPeriodRange = {}; //holds start and end day, month, and year values from user periodRange parameter var requestStart,firstRequestBody,requestEnd,rows,rowsremaining,response; //for making api requests to Intacct var reportingBook='unknown', reportingPeriodName='unknown', reportingPeriodStartDate='unknown', reportingPeriodEndDate='unknown'; //defined here, needed inside different functions // Prepare to extract data from Intacct and load into the staging table var rowset = context.getRowset(); rowset.setSmartParsingEnabled(true); var tableId = rowset.getTableId(); var columns = rowset.getColumns(); var columnRemoteIds = getColumnRemoteIds(rowset); //get the session token and new endpoint var sessionAndEndpoint = getIntacctSessionID(intacctURL,intacctRequestParms); if (!sessionAndEndpoint) { ai.log.logError('Intacct Request Failed','The request to Intacct failed. See log for additional messages.'); return; } //extract session id and new url from response var operation_sessionid = sessionAndEndpoint.sessionId; intacctURL = sessionAndEndpoint.endpoint; switch(tableId) { /*** * * GL Balances and GLDETAIL require different processing from other tables due to the need to loop over reporting books and periods * ***/ case 'GL Balances' : case 'GLDETAIL' : /*** * * Build specific extract requests for these tables. * * We have to loop over both reporting books and reporting periods. * The exact syntax for specifying the request varies with the table. * ***/ //get the specified period range info for the GL balances import var reportingPeriodsToImport = getReportingPeriodsToImport(periodRange); /*** * * if there are user defined dimensions, build the clause to include them in balances request * and also need to add them to the groupby list, with a leading comma to make a syntactily correct list * ***/ var userDefinedDimensions=''; if (intacctGLRequestParms.userDefinedDimensions !== null && intacctGLRequestParms.userDefinedDimensions !== '') { userDefinedDimensions = intacctGLRequestParms.userDefinedDimensions.split(','); //turn into array } var uddXMLclause=''; if (userDefinedDimensions.length > 0 ) { uddXMLclause = '<userDefinedDimensions>'; for (let j=0; j<userDefinedDimensions.length; j++ ) { uddXMLclause += '<userDefinedDimension><objectName>' + userDefinedDimensions[j] + '</objectName></userDefinedDimension>'; } uddXMLclause += '</userDefinedDimensions>'; userDefinedDimensions = ',' + userDefinedDimensions.join(','); // turn back into string, add leading comma for groupBy clause } /*** * * Finally, start pulling the data from Intacct * * *** * * Iterate over reporting books * ***/ var reportingBooksToExport = intacctGLRequestParms.reportingBooks.split(','); //put reportingbooks into an array for (reportingBook of reportingBooksToExport) { if (reportingBook === null || reportingBook === '') continue; //skip blank values ai.log.logVerbose('Beginning import for book ' + reportingBook); /*** * * Iterate over reporting periods * ***/ for (let i=0; i<reportingPeriodsToImport.length; i++ ) { reportingPeriodStartDate = reportingPeriodsToImport[i].startDate; reportingPeriodEndDate = reportingPeriodsToImport[i].endDate; //build the first part of the request, common for both GL Balances and GLDETAIL //this is done inside the loop because the sessionid could change var glExportRequest, glExportRequestHeader, glExportQuery, glExportRequestFooter; //query will vary if multiple requests are required glExportRequestHeader = '<request><control><senderid>' + intacctRequestParms.senderId + '</senderid><password>' + intacctRequestParms.senderPassword + '</password><controlid>' + intacctRequestParms.control_controlid + '</controlid><dtdversion>' + intacctRequestParms.control_dtdversion + '</dtdversion></control>'; glExportRequestHeader += '<operation><authentication><sessionid>' + operation_sessionid + '</sessionid></authentication>'; glExportRequestHeader += '<content><function controlid="' + intacctRequestParms.control_controlid + '">'; switch(tableId) { //the export function is different between the two GL tables case 'GL Balances' : glExportQuery = '<get_accountbalancesbydimensions>'; glExportQuery += '<startdate>' + getDateElements(reportingPeriodStartDate) + '</startdate>'; glExportQuery += '<enddate>' + getDateElements(reportingPeriodEndDate) + '</enddate>'; /* include this inside get_accountbalancesbydimensions to limit to an account range <startaccountno>4000</startaccountno> \ <endaccountno>4999</endaccountno> \ */ glExportQuery += '<reportingbook>' + reportingBook + '</reportingbook>'; glExportQuery += uddXMLclause; //user defined dimensions, if any glExportQuery += '<groupby>' + intacctGLRequestParms.intacctDimensions + userDefinedDimensions + '</groupby></get_accountbalancesbydimensions>'; break; case 'GLDETAIL' : //generate the query block, incorporating the offset needed for paging calls glExportQuery = setGLDetailQuery(offset); break; default: throw('FATAL ERROR Attempt to create extract request for table ' + tableId); } //end switch //close out the request glExportRequestFooter = '</function></content></operation></request>'; //put it all together glExportRequest = glExportRequestHeader + glExportQuery + glExportRequestFooter; /*** * * Make Intacct call, process results, load staging * ***/ response = updateStagingFromGLQuery(tableId,rowset,intacctURL,glExportRequest,reportingBook,reportingPeriodStartDate); if (!response) { ai.log.logError('Intacct Request Error','The request to read data from Intacct failed. See log for additional messages.'); break; } numremaining = response.numremaining; //check for more records; if so, increment offset and call it again if (tableId =='GLDETAIL' && numremaining !== null && numremaining != '0') { //There are more records for GLDETAIL, need to keep calling until we get them all while (numremaining != '0') { offset = offset + parseInt(numremaining); //compute where to start with next call //build the query glExportQuery = setGLDetailQuery(offset); glExportRequest = glExportRequestHeader + glExportQuery + glExportRequestFooter; //put it all together /*** * * Make Intacct call, process results, load staging * ***/ response = updateStagingFromGLQuery(tableId,rowset,intacctURL,glExportRequest,reportingBook,reportingPeriodStartDate); if (!response) { ai.log.logError('Intacct Request Error','The request to read data from Intacct failed. See log for additional messages.'); break; } numremaining = response.numremaining; } } } //end for reporting periods } //end for reporting books function setGLDetailQuery(offset) { /*** * internal function to build GLDETAIL query, setting the offset appropriately * * <query> <object>GLDETAIL</object> <select> <field>BOOKID</field> <field>ENTRY_DATE</field> <field>ACCOUNTNO</field> <field>AMOUNT</field> <field>TRX_AMOUNT</field> </select> <filter> <and> <equalto> <field>MODULEKEY</field> <value>2.GL</value> </equalto> <equalto> <field>BOOKID</field> <value>ACCRUAL</value> </equalto> <greaterthanorequalto> <field>ENTRY_DATE</field> <value>01/01/2011</value> </greaterthanorequalto> <lessthanorequalto> <field>ENTRY_DATE</field> <value>02/28/2011</value> </lessthanorequalto> </and> </filter> </query> * ***/ var glExportQuery = '<query><object>GLDETAIL</object>'; //get columns in staging table glExportQuery += '<select>'; for (var j=0; j<columnRemoteIds.length; j++) { glExportQuery += '<field>'+ columnRemoteIds[j] +'</field>'; } glExportQuery +='</select>'; glExportQuery +='<filter><and>'; glExportQuery +='<equalto><field>MODULEKEY</field><value>2.GL</value></equalto>'; glExportQuery +='<equalto><field>BOOKID</field><value>' + reportingBook +'</value></equalto>'; //get start and end date of reporting period glExportQuery += '<greaterthanorequalto><field>ENTRY_DATE</field><value>' + reportingPeriodStartDate + '</value></greaterthanorequalto>'; glExportQuery += '<lessthanorequalto><field>ENTRY_DATE</field><value>' + reportingPeriodEndDate + '</value></lessthanorequalto>'; glExportQuery += '</and></filter><pagesize>' + maxRows + '</pagesize><offset>' + offset + '</offset></query>'; return glExportQuery; } //end setGLDetailQuery break; //end GL Balances default: /*** * * all other tables, using the readByQuery method * ***/ rowsremaining = -1; //intial condition while(rowsremaining != 0) { /*** * * Make Intacct call, process results, load staging * ***/ //returns {rows,rowsremaining,operation_sessionid,intacctURL} response = intacctReadByQueryRequest(tableId,rowset,intacctRequestParms,maxRows,operation_sessionid,intacctURL,rowsremaining); if (!response) { ai.log.logError('Intacct Request Error','The request to read data from Intacct failed. See log for additional messages.'); break; } //set variables from response rowsremaining = response.rowsremaining; if (response.operation_sessionid !== undefined) operation_sessionid = response.operation_sessionid; if (response.intacctURL !== undefined) intacctURL = response.intacctURL; } //end while } //end table switch /*** * * Internal functions. These may modify variables that are defined outside the functions * ***/ function intacctReadByQueryRequest(table,rowset,intacctRequestParms,maxRows,operation_sessionid,intacctURL,rowsremaining) { /*** * * returns {rows,rowsremaining,operation_sessionid,intacctURL}; * return false if any errors in getting the data * * Read by query request. If more than maxPages of data is returned then rowsremaining will be non-zero (for number of rows remaining) and subsequent calls will be required. * rowsremaining is initially set to -1 coming in. * ***/ var requestStart, requestQuery, requestEnd; var operation_sessionid_internal = operation_sessionid; var intacctURL_internal = intacctURL; var columnRemoteIds = getColumnRemoteIds(rowset); var cols = columnRemoteIds.join(",") /*** * * Build the read-by-query HTTPS request * ***/ //first part of request, authentication etc. requestStart = '<request><control><senderid>' + intacctRequestParms.senderId + '</senderid><password>' + intacctRequestParms.senderPassword + '</password><controlid>' + intacctRequestParms.control_controlid + '</controlid><dtdversion>' + intacctRequestParms.control_dtdversion + '</dtdversion></control>'; requestStart += '<operation><authentication><sessionid>' + operation_sessionid + '</sessionid></authentication>'; requestStart += '<content><function controlid="' + intacctRequestParms.control_controlid + '">'; //middle of request, the actual query if (rowsremaining == -1) { //intial call requestQuery = '<readByQuery><object>' + table + '</object><fields>' + cols + '</fields><query></query><pagesize>' + maxRows + '</pagesize><returnFormat>xml</returnFormat></readByQuery>'; } else { //subsequent calls to get remaining rows requestQuery = '<readMore><object>' + table + '</object></readMore>' ; } //close out the request requestEnd = '</function></content></operation></request>'; //put it all together var strXMLRequest = requestStart + requestQuery + requestEnd; /*** * * Make the request, load staging * ***/ //returns {rowsremaining, sessionTO} var response = updateStagingFromReadByQuery(table, rowset, intacctURL, strXMLRequest); //retrieve the rows of data from Intacct, push to staging if (!response) return false; /*** * * Update session id if needed * ***/ sessionTO = response.sessionTO; sessionTimeOut = new Date(sessionTO); //datetime when session expires //intacctSessionTimeoutTolerance is tolerance in seconds, multiply by 1000 to get milliseconds //compute how much time is left to expiration, compare with the tolerance var needNewSessionId = (parseInt((sessionTimeOut.getTime() - new Date().getTime()).toString()) < intacctSessionTimeoutTolerance * 1000) if (needNewSessionId) { var sessionAndEndpoint = getIntacctSessionID(intacctURL,intacctRequestParms); if (!sessionAndEndpoint) { ai.log.logError('Intacct Request Failed','The request to Intacct to update the session id failed. See log for additional messages.'); return false; } //extract session id and new url from response operation_sessionid_internal = sessionAndEndpoint.sessionId; intacctURL_internal = sessionAndEndpoint.endpoint; } /** * * return results * ***/ return {rowsremaining:response.rowsremaining,operation_sessionid:operation_sessionid_internal,intacctURL:intacctURL_internal}; } //end intacctReadByQueryRequest function getReportingPeriodsToImport(periodRange) { /*** * * Get the specified period range info for the GL balances importf * * Get the period range from Adaptive Time Stratum Settings * Use the parameter periodRange to pull all periods that fall between start and end * Return data in Intacct by converting YYYY-MM-DD to MM/DD/YYYY * Set Period End Dates to Day-1 (Adaptive period end date is first day of next month; for Intacct request we want last day of current month) * ***/ var periodRangeStartSetting = periodRange.getValue().getFromPeriodStartDateTime(); var periodRangeEndSetting = periodRange.getValue().getToPeriodEndDateTime(); //extract start and end dates into strings formatted YYYY-MM-DD //these will be used to pull exportTime periods extractPeriodRangeStartDate = periodRangeStartSetting.substring(0,10); extractPeriodRangeEndDate = periodRangeEndSetting.substring(0,10); /*** * * Make Adaptive exportTime call * ***/ var adaptiveURL = 'https://api.adaptiveplanning.com/api/v39'; var method = 'POST'; var xmlBody = '<call method="exportTime" callerName="IntacctImport">'; xmlBody += '<credentials login="' + adaptiveUser + '" password="' + adaptivePassword + '"/></call>'; var headers = null; try { response = ai.https.request(adaptiveURL, method, xmlBody, headers); } catch(e) { //Catch catastrophic failure ai.log.logError('exportTime Failed','exportTime request failed. Error: ' + e); return false; } //validate response var responseCode = response.getHttpCode(); if (responseCode != 200){ ai.log.logError('exportTime Failed','exportTime response http code: ' + responseCode); return false; } /*** * * Process the response * ***/ var responseBody = response.getBody(); var defaultStratumID; var withinPeriodRange = false; //Used to add periods between specified start and end ranges var doneParsing = false; //Indicates that all data has been collected var reportingPeriodMap = []; //For mapping calendar dates to Intacct reporting periods var loadTimeCallbacks = {}; var responseStatusSuccess, responseMessage, adaptiveMessages= [], foundMessages=false, foundMessage=false, messageText=''; //Coded with expectation that extractTime is ordered chronological and is consistent //stratum tags should be read before period tags loadTimeCallbacks.onOpenTag = function(tag){ switch (tag.name){ case 'response' : responseStatusSuccess = tag.attributes['success'] == 'true'; break; case 'messages' : foundMessages = true; break; case 'message' : foundMessage = true; messageKey = tag.attributes['key'] ; break; case 'stratum': //assumes the "default" startum is the one we want if (tag.attributes['isDefault'] == 1) { defaultStratumID = tag.attributes['id']; } break; case 'period': //if no more data is being collected, skip this tag if(doneParsing){ break; } //if still looking for data, process tag if correct stratumId if (tag.attributes['stratumId'] == defaultStratumID){ var periodStart; var periodEnd; //runs when first part of range is met if (extractPeriodRangeStartDate == tag.attributes['start']){ periodStart = tag.attributes['start']; periodEnd = tag.attributes['end']; //check if end of range is part of first period //if not set withinPeriodRange and skip else if and else checks //if yes then all data is collected after this run if(extractPeriodRangeEndDate != periodEnd){ withinPeriodRange = true; } else { doneParsing = true; } } else if (extractPeriodRangeEndDate == tag.attributes['end']){ //runs when last part of range is met //set withinPeriodRange to false and that all data has been collected withinPeriodRange = false; doneParsing = true; periodStart = tag.attributes['start']; periodEnd = tag.attributes['end']; } else if (withinPeriodRange){ //add values and continue since we are somewhere in the middle of the range periodStart = tag.attributes['start']; periodEnd = tag.attributes['end']; } //If data was collected, reformat the data and add it to output if(typeof periodStart != 'undefined' && typeof periodEnd != 'undefined'){ //Reformat periodStart YYYY-MM-DD to MM/DD/YYYY mo = periodStart.substring(5,7); dy = periodStart.substring(8,10); yr = periodStart.substring(0,4); periodStartTemp = mo + '/' + dy + '/' + yr; //Reformat periodStart YYYY-MM-DD to MM/DD/YYYY -1 day periodEndTemp = new Date(periodEnd +'T00:00:00'); periodEndTemp = new Date(periodEndTemp.setDate(periodEndTemp.getDate()-1)); yr = periodEndTemp.getFullYear().toString(); mo = (periodEndTemp.getMonth() + 1); //Month is 0 based pad = ''; if (mo < 10) { pad = '0'; } mo = pad + mo.toString(); dy = periodEndTemp.getDate(); pad = ''; if (dy < 10) { pad = '0'; } dy = pad + dy.toString(); periodEndTemp = mo + '/' + dy + '/' + yr; reportingPeriodMap.push({startDate: periodStartTemp, endDate: periodEndTemp}); } } } } //end onOpenTag loadTimeCallbacks.onText = function(text) { if (foundMessage) { foundText=true; messageText = text; //save value in row } }; loadTimeCallbacks.onCloseTag = function(tagName) { switch (tagName) { case 'response' : responseStatusSuccess = true; //TODO work around bug PLNINT-9135 if (!responseStatusSuccess) { //something went wrong, log the messages for (var msgIdx in adaptiveMessages) { ai.log.logError('Adaptive GetTime API error', adaptiveMessages[msgIdx]); } //fatal error throw('Error attempting to process Adaptive Time Stratum to determine accounting periods. See log for additional details'); } break; case 'messages' : foundMessages = false; break; case 'message' : adaptiveMessages.push(messageKey + ':' + messageText); foundMessage = false; break; } } // end onCloseTag //Sets SAX parser which will run the above onOpenTag function for each tag var timeParser = ai.xml.sax.createFromText(responseBody, "UTF8", loadTimeCallbacks); timeParser.readToEnd(); //process response return reportingPeriodMap; } //end getReportingPeriodsToImport function getDateElements(aMMDDYYYYdate) { /*** * * Generate an XML formated block containing a date * * assumes date is in MM/DD/YYYY format * 0123456789 * * returns: * <year>2010</year> <month>06</month> <day>01</day> * ***/ let dateElements = '<year>' +aMMDDYYYYdate.substring(6,10) +'</year>'; dateElements += '<month>' + aMMDDYYYYdate.substring(0,2) + '</month>'; dateElements += '<day>' + aMMDDYYYYdate.substring(3,5) + '</day>'; return dateElements; }; //end getDateElements } //end importData /*** * * Global functions * ***/ function updateStagingFromGLQuery(tableId,rowset,url,requestBody,reportingBook,reportingPeriodStartDate) { /*** * * returns {numremaining : numremaining} * * Post GLQuery request to Intacct, process the results, load data into staging * ***/ var columnRemoteIds = getColumnRemoteIds(rowset); var row = {}; var loadGLCallbacks = {}; var data = { currentTag: '' }; //state flags for keeping track of where we are in the xml stream, and for capturing some data var foundOperation=false, foundResult=false, foundData=false, foundAccountBalance=false, foundText=false, foundGLDetail = false, foundError = false, errorsFound=false, numremaining=0; var functionText, errorNo, errorDescription, errorDescription2, correction; var errorMsg=''; /*** * Set up the SAX parser callbacks to process the XML reponse * For GL Balance the result looks like - <result> <status>success</status> <function>get_accountbalancesbydimensions</function> <controlid>f860fdfd-f88c-41ae-a93f-e1d1b8012ace</controlid> <data> <accountbalance> <glaccountno>4000</glaccountno> ... for each field </accountbalance> For GLDETAIL the result looks like - <result> <status>success</status> <function>query</function> <controlid>d5589b55-3c0b-40ff-8e19-d8aa11dab11e</controlid> <data listtype="GLDETAIL" totalcount="10" offset="0" count="10" numremaining="0"> <GLDETAIL> <BATCHKEY>44</BATCHKEY> ... for each field </GLDETAIL> * * ***/ loadGLCallbacks.onOpenTag = function(tag) { data.currentTag = tag.name; switch (tag.name) { case 'errormessage' : foundError = true; //bad news break; case 'operation' : foundOperation = true; break; case 'result' : foundResult = true; break; case 'data' : foundData = true; numremaining = tag.attributes['numremaining']; //GLDETAIL may require more than one call break; case 'accountbalance' : foundAccountBalance = true; row = {}; //reset break; case 'GLDETAIL' : foundGLDetail = true; row = {}; //reset break; default : } }; loadGLCallbacks.onText = function(text) { // looking for error messages switch (data.currentTag) { case 'correction' : correction = text; break; case 'description2' : errorDescription2 = text; break; case 'description' : errorDescription = text; break; case 'errorno' : errorNo = text; break; case 'function' : functionText = text; break; } if (foundAccountBalance || foundGLDetail) { foundText=true; row[data.currentTag] = text; //save value in row } }; loadGLCallbacks.onCloseTag = function(tagName) { let cells = []; switch (tagName) { case 'errormessage' : //Oops, found an error //assemble the error message to return to caller foundError = false; if (functionText) errorMsg += 'Operation: ' + functionText + ' '; errorMsg += 'Error Number: ' + errorNo + ' Description: ' + errorDescription; if (errorDescription2) errorMsg += '; ' + errorDescription2; if (correction) errorMsg += ' Correction: ' + correction; ai.log.logError('Intacct API request operation failed',errorMsg); errorsFound = true; case 'operation' : foundOperation = false; break; case 'result' : foundResult = false; break; case 'data' : foundData = false; break; case 'accountbalance' : //done processing one "row" of data foundAccountBalance = false; //tack on reporting book row.reportingbook=reportingBook; //tack on reporting period start date row.periodstartdate = reportingPeriodStartDate; cells = []; //put data in the right column order for (let i=0;i<columnRemoteIds.length;i++) { let val = row[columnRemoteIds[i]] === undefined ? '' : row[columnRemoteIds[i]]; //has to have a value cells.push(val); } rowset.addRow(cells); //push to staging break; case 'GLDETAIL' : foundGLDetail = false; cells = []; //put data in the right column order for (let i=0;i<columnRemoteIds.length;i++) { let val = row[columnRemoteIds[i]] === undefined ? '' : row[columnRemoteIds[i]]; //has to have a value cells.push(val); } rowset.addRow(cells); //push to staging break; default: if (foundAccountBalance || foundGLDetail) { if (foundText) { foundText = false; //reset flag } else { //empty field, output an empty string row[data.currentTag] = ''; } } } }; //end onCloseTag var method = 'POST'; var headers = { 'Content-Type': 'x-intacct-xml-request' }; var glParser = ai.xml.sax.createFromUrl(url, method ,requestBody, headers, loadGLCallbacks); glParser.readToEnd(); if (errorsFound) return false; //errors mean no data was returned return {numremaining : numremaining}; } //end updateStagingFromGLQuery function updateStagingFromReadByQuery(tableId,rowset,url,requestBody) { /*** * * returns {rowsremaining, sessionTO} * * Post read-by-query request to Intacct, process the results, load data into staging * ***/ //state flags for keeping track of where we are in the xml stream, and for capturing some data var foundOperation=false, foundAuthentication=false, foundResult=false, foundSessionTimeout=false, foundResult=false, foundData=false, foundTableRow=false, foundError = false, errorsFound = false; var functionText, errorNo, errorDescription, errorDescription2, correction, sessionTimeout, rowsRemaining; var errorMsg='', rows=[], row={}; var rowCallBacks = {}, data = {}, cells=[]; var columnRemoteIds = getColumnRemoteIds(rowset); /*** * * Define SAX parser callbacks * response looks like this <?xml version="1.0" encoding="UTF-8"?> <response> <control> <status>success</status> <senderid>adaptiveplanning</senderid> <controlid>c5KLKSWFEKPR50GF4</controlid> <uniqueid>false</uniqueid> <dtdversion>3.0</dtdversion> </control> <operation> <authentication> <status>success</status> <userid>Julia</userid> <companyid>adaptiveplanning-DEV</companyid> <locationid></locationid> <sessiontimestamp>2024-09-12T18:30:53+00:00</sessiontimestamp> <sessiontimeout>2024-09-12T22:30:53+00:00</sessiontimeout> </authentication> <result> <status>success</status> <function>readByQuery</function> <controlid>c5KLKSWFEKPR50GF4000002.00</controlid> <data listtype="department" count="14" totalcount="14" numremaining="0" resultId=""> <department> <DEPARTMENTID>QADept</DEPARTMENTID> <RECORDNO>14</RECORDNO> <TITLE>Brisbane-QA</TITLE> <PARENTKEY>12</PARENTKEY> <PARENTID>QA</PARENTID> <PARENTNAME>TestQA</PARENTNAME> <SUPERVISORKEY>1</SUPERVISORKEY> <SUPERVISORID>1</SUPERVISORID> <WHENCREATED>05/12/2023 07:14:45</WHENCREATED> <WHENMODIFIED>05/12/2023 07:14:45</WHENMODIFIED> <SUPERVISORNAME>John Pearce</SUPERVISORNAME> <STATUS>active</STATUS> <CUSTTITLE></CUSTTITLE> <CREATEDBY>19</CREATEDBY> <MODIFIEDBY>19</MODIFIEDBY> </department> ... </data> </result> </operation> </response> ***/ rowCallBacks.onOpenTag = function(tag) { data.currentTag = tag.name; switch (tag.name) { case 'operation' : foundOperation = true; break; case 'authentication' : foundAuthentication = true; break; case 'result' : foundResult = true; break; case 'errormessage' : foundError = true; //bad news break; case 'sessiontimeout' : foundSessionTimeout = true; break; case 'data' : foundData = true; rowsRemaining = tag.attributes['numremaining'] ; break; case tableId.toLowerCase() : foundTableRow = true; break; default : } }; rowCallBacks.onText = function(text) { // looking for error messages switch (data.currentTag) { case 'sessiontimeout' : if (foundOperation && foundAuthentication ) sessionTimeout = text; break; case 'correction' : correction = text; break; case 'description2' : errorDescription2 = text; break; case 'description' : errorDescription = text; break; case 'errorno' : errorNo = text; break; case 'function' : functionText = text; break; default : if (foundOperation && foundResult && foundData && foundTableRow) { //we're processing a row of data; grab this text value and add it to the row array row[data.currentTag] = text; } } }; rowCallBacks.onCloseTag = function(tagName) { let cells = []; switch (tagName) { case 'operation' : foundOperation = false; break; case 'authentication' : foundAuthentication = false; break case 'result' : foundResult = false; break; case 'errormessage' : //Oops, found an error //assemble the error message to return to caller foundError = false; if (functionText) errorMsg += 'Operation: ' + functionText + ' '; errorMsg += 'Error Number: ' + errorNo + ' Description: ' + errorDescription; if (errorDescription2) errorMsg += '; ' + errorDescription2; if (correction) errorMsg += ' Correction: ' + correction; ai.log.logError('Intacct API request operation failed',errorMsg); errorsFound = true; case 'sessiontimeout' : foundSessionTimeout = false; break; case 'data' : foundResult = false; break; case tableId.toLowerCase() : //done with one row entry; add row to collection of rows foundTableRow = false; cells = []; //put data in the right column order for (let i=0;i<columnRemoteIds.length;i++) { let val = row[columnRemoteIds[i]] === undefined ? '' : row[columnRemoteIds[i]]; //has to have a value cells.push(val); } rowset.addRow(cells); //push to staging row = {}; //reset row holder break; default : } }; //end onCloseTag /*** * * Make the request, process the response * **/ var method = 'POST'; var headers = { 'Content-Type': 'x-intacct-xml-request' }; var saxParser = ai.xml.sax.createFromUrl(url, method, requestBody, headers, rowCallBacks); saxParser.readToEnd(); //process response if (errorsFound) return false; //errors mean no data was returned return {rowsremaining : rowsRemaining, sessionTO : sessionTimeout}; } //end updateStagingFromReadByQuery function previewData(context) { //previewData should do the same as importData, except that the results are not saved to staging //since importData is just loading the data without any other manipulation there isn't much to be learned by "previewing" throw('External Preview Data is not implemented'); }