7 Replies Latest reply on Dec 7, 2018 1:17 PM by pbcs_challenged

    PBCS - Data Management - NetSuite Error

    pbcs_challenged

      Hi Experts,

       

      Oracle support is of zero help thus taking a shot here.

       

      We are trying to make a connection to NetSuite using the built in adapter.

       

      We have created a successful source connection and it validates.

      We created our Import Format

      We created our Target app

      We created our Location

      We created Explicit Period mapping to handle the 'Oct 2018' format for NetSuite

      We create our Data Load Rule

       

       

      However when we run our Location in Data Load Workbench, we get no rows returned.

       

      The attached Vision_518.log file is the from Process Details. Line 38 fails.  We can't figure out what this error is.   The NetSuite user we use is able to run the saved search when we log into NetSuite.

       

      I also attached the ODI log.

       

      We are perplexed as not sure why we can't bring in rows of data from our saved search.

       

      Thanks for any help!!!

        • 1. Re: PBCS - Data Management - NetSuite Error
          pbcs_challenged

          Not sure why/if the log didn't attach.   Here it is...

           

           

           

          2018-11-09 22:22:49,229 INFO  [AIF]: FDMEE Process: 518, Log Level: 5

          2018-11-09 22:22:49,229 INFO  [AIF]: Location     : NetSuite_LOC_TD3 (Partitionkey:22)

          2018-11-09 22:22:49,229 INFO  [AIF]: Period Name  : Oct 2018 (Period Key:10/31/18 12:00 AM)

          2018-11-09 22:22:49,229 INFO  [AIF]: Category Name: Actual (Category key:2)

          2018-11-09 22:22:49,229 INFO  [AIF]: Rule Name    : NetSuite_Rule_TD3 (Rule ID:32)

          2018-11-09 22:22:51,988 INFO  [AIF]: FDM Version: 11.1.2.4.230

          2018-11-09 22:22:51,988 INFO  [AIF]: Log File Encoding: UTF-8

          2018-11-09 22:22:52,974 INFO  [AIF]: -------START IMPORT STEP-------

          2018-11-09 22:22:53,236 DEBUG [AIF]: FileData.extractDataFromSource - START

          2018-11-09 22:22:53,236 DEBUG [AIF]: CommData.getRuleInfo - START

          2018-11-09 22:22:53,241 DEBUG [AIF]:

                SELECT adim.BALANCE_COLUMN_NAME DIMNAME, adim.DIMENSION_ID, adim.TARGET_DIMENSION_CLASS_NAME, tiie.IMPCONCATCHAR

                      ,(SELECT COA_SEGMENT_NAME FROM AIF_COA_SEGMENTS cs WHERE cs.COA_LINE_ID = tiie.IMPSOURCECOALINEID1) COA_SEGMENT_NAME1

                FROM TPOVPARTITION tpp

                INNER JOIN AIF_TARGET_APPL_DIMENSIONS adim

                  ON adim.APPLICATION_ID = 9

                LEFT OUTER JOIN TBHVIMPITEMERPI tiie

                  ON tiie.IMPGROUPKEY = tpp.PARTIMPGROUP AND tiie.IMPFLDFIELDNAME = adim.BALANCE_COLUMN_NAME AND tiie.IMPMAPTYPE = 'ERP'

                WHERE tpp.PARTITIONKEY = 22

                AND adim.BALANCE_COLUMN_NAME IS NOT NULL

                AND adim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'

                AND (adim.VALID_FOR_PLAN1 = 1 OR adim.TARGET_DIMENSION_CLASS_NAME = 'LOOKUP')

                ORDER BY adim.BALANCE_COLUMN_NAME

               

          2018-11-09 22:22:53,244 DEBUG [AIF]: {'PLAN_NUMBER': 1L, 'EXPORT_TO_TARGET_FLAG': u'N', 'SRC_TARGET_APP_NAME': u'customsearch_nspbcs_all_transactions_s_3', 'JOURNAL_FLAG': None, 'DIMNAME_MAP': {u'UD2': {'DIMNAME': u'UD2', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD1': {'DIMNAME': u'UD1', 'TARGET_DIMENSION_CLASS_NAME': u'Version'}, u'UD3': {'DIMNAME': u'UD3', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'ACCOUNT': {'DIMNAME': u'ACCOUNT', 'TARGET_DIMENSION_CLASS_NAME': u'Account'}, u'ENTITY': {'DIMNAME': u'ENTITY', 'TARGET_DIMENSION_CLASS_NAME': u'Entity'}}, 'CATKEY': 2L, 'TGT_APP_SUB_TYPE': None, 'TGT_APP_ID': 9L, 'DIMNAME_LIST': [u'ACCOUNT', u'ENTITY', u'UD1', u'UD2', u'UD3'], 'SRC_SOURCE_SYSTEM_ID': 8L, 'TGT_SOURCE_SYSTEM_TYPE': None, 'TGT_APP_TYPE': u'HPL', 'CALENDAR_ID': u'Fiscal', 'TGT_TARGET_APP_NAME': u'Vision', 'PARTITIONKEY': 22L, 'EXECUTION_MODE': u'REPLACE', 'LEDGER_GROUP_CODE': u'NONE', 'PS_LEDGER': None, 'SRC_DATA_LOAD_METHOD': u'NETSUITE', 'PARTVALGROUP': u'[NONE]', 'WEB_SERVICE_URL': u'http://us2pbcsr1702009.usdc2.oraclecloud.com:9000/aif', 'IMPORT_FROM_SOURCE_FLAG': u'Y', 'IS_INCREMENTAL_LOAD': False, 'INCLUDE_ADJ_PERIODS_FLAG': u'N', 'SRC_APP_NAME': u'NS_PBCS - Transaction Detail_3', 'FCI_FLAG': 'N', 'KK_FLAG': u'N', 'LAST_UPDATED_BY': u'Sam.Toteve@terillium.com', 'CHECK_FLAG': u'N', 'RULE_ID': 32L, 'PERIOD_MAPPING_TYPE': u'EXPLICIT', 'SRC_APP_ID': u'20', 'INCLUDE_ZERO_BALANCE_FLAG': None, 'IMPGROUPKEY': u'NetSuite_IF_TD3', 'TGT_SOURCE_SYSTEM_ID': 0L, 'SRC_APP_TYPE': u'DATASOURCE', 'TGT_APP_NAME': u'Vision', 'IMPGROUPFILETYPE': u'DELIMITED', 'LEDGER_GROUP': None, 'TGT_COA_ID': 0L, 'SOURCE_ADAPTER_FLAG': u'N', 'RECALCULATE_FLAG': u'Y', 'TGT_DATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI', 'SRC_SOURCE_SYSTEM_TYPE': u'NETSUITE', 'PLAN_TYPE': u'PLAN1', 'TGT_METADATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI'}

          2018-11-09 22:22:53,246 DEBUG [AIF]: {u'Account': {'BCN': u'ACCOUNT', 'DCN': u'Account'}, u'Entity': {'BCN': u'ENTITY', 'DCN': u'Entity'}, u'Period': {'BCN': None, 'DCN': u'Period'}, u'Product': {'BCN': u'UD3', 'DCN': u'Generic'}, u'Scenario': {'BCN': None, 'DCN': u'Scenario'}, u'Year': {'BCN': None, 'DCN': u'Year'}, u'Version': {'BCN': u'UD1', 'DCN': u'Version'}, u'HSP_View': {'BCN': u'UD2', 'DCN': u'Generic'}}

          2018-11-09 22:22:53,248 DEBUG [AIF]: {u'UD3': {'IMPFLDSOURCECOLNAME': u'Product'}, u'ACCOUNT': {'IMPFLDSOURCECOLNAME': u'Account'}, u'ENTITY': {'IMPFLDSOURCECOLNAME': u'Department'}, u'AMOUNT': {'IMPFLDSOURCECOLNAME': u'Amount'}}

          2018-11-09 22:22:53,248 DEBUG [AIF]: CommData.getRuleInfo - END

          2018-11-09 22:22:53,298 DEBUG [AIF]: NetsuiteDatasourceImpl.importData- Begin

          2018-11-09 22:22:53,301 DEBUG [AIF]: SavedSearch URL:https://rest.netsuite.com/app/site/hosting/restlet.nl?script=customscript_nspbcs_rl_saved_search&deploy=customdeploy_nspbcs_rl_saved_search

          2018-11-09 22:22:53,301 DEBUG [AIF]: SavedSearchRequest URL:https://rest.netsuite.com/app/site/hosting/restlet.nl?script=customscript_nspbcs_rl_search_request&deploy=customdeploy_nspbcs_rl_search_request

          2018-11-09 22:22:53,316 DEBUG [AIF]: Name:value:operator::postingperiod:66:is

          2018-11-09 22:22:53,316 DEBUG [AIF]: Name:value:operator::posting:T:is

          2018-11-09 22:22:53,387 DEBUG [AIF]: No Query params

          2018-11-09 22:22:53,387 DEBUG [AIF]: Authentication Type NLAUTH: nlAuth

          2018-11-09 22:22:53,387 INFO  [AIF]: Post Request

          2018-11-09 22:22:56,265 DEBUG [AIF]: Response Status: 200

          2018-11-09 22:22:56,266 DEBUG [AIF]: Error converting request id to Integer. Request ID = [null]

          2018-11-09 22:22:56,266 ERROR [AIF]: java.lang.String cannot be cast to java.lang.Integer

          2018-11-09 22:22:56,267 DEBUG [AIF]: No exception in response object.java.lang.String cannot be cast to org.json.JSONObject

          2018-11-09 22:22:56,267 DEBUG [AIF]: Checking for status of saved search run

          2018-11-09 22:22:56,270 DEBUG [AIF]: BATCH_TIMEOUT: null

          2018-11-09 22:22:56,270 DEBUG [AIF]: Failed request or response. Request status:FAILURE

          2018-11-09 22:22:56,270 DEBUG [AIF]: Exception:null

          2018-11-09 22:22:56,277 ERROR [AIF]: POST https://rest.netsuite.com/app/site/hosting/restlet.nl?script=customscript_nspbcs_rl_search_request&deploy=customdeploy_nspbcs_rl_search_request returned a response status of 200 OK

          2018-11-09 22:22:56,280 DEBUG [AIF]: NetsuiteDatasourceImpl.importData- End

          2018-11-09 22:22:56,281 FATAL [AIF]: Error in FileData.extractDataFromSource

          Traceback (most recent call last):

            File "<string>", line 129, in extractDataFromSource

          RuntimeException: java.lang.RuntimeException: POST https://rest.netsuite.com/app/site/hosting/restlet.nl?script=customscript_nspbcs_rl_search_request&deploy=customdeploy_nspbcs_rl_search_request returned a response status of 200 OK

           

          2018-11-09 22:22:56,400 DEBUG [AIF]: Comm.finalizeProcess - START

          2018-11-09 22:22:56,400 DEBUG [AIF]: CommData.updateRuleStatus - START

          2018-11-09 22:22:56,402 DEBUG [AIF]:

              UPDATE AIF_BALANCE_RULES

              SET STATUS = CASE 'FAILED'

                WHEN 'SUCCESS' THEN

                  CASE ( SELECT COUNT(*) FROM AIF_PROCESS_DETAILS pd WHERE pd.PROCESS_ID = 518 AND pd.STATUS IN ('FAILED','WARNING') )

                    WHEN 0 THEN 'SUCCESS'

                    ELSE ( SELECT MIN(pd.STATUS) FROM AIF_PROCESS_DETAILS pd WHERE pd.PROCESS_ID = 518 AND pd.STATUS IN ('FAILED','WARNING') ) END

                ELSE 'FAILED' END

              WHERE RULE_ID = 32

             

          2018-11-09 22:22:56,407 DEBUG [AIF]: CommData.updateRuleStatus - END

          2018-11-09 22:22:56,407 FATAL [AIF]: Error in File Extract Data

          2018-11-09 22:22:56,407 DEBUG [AIF]: Comm.updateProcess - START

          2018-11-09 22:22:56,412 DEBUG [AIF]: Comm.updateProcess - END

          2018-11-09 22:22:56,412 INFO  [AIF]: FDMEE Process End, Process ID: 518

          • 2. Re: PBCS - Data Management - NetSuite Error
            3326272

            Hi

            Was this issue ever resolved?

            We are experiencing the same issue since Nov 6.  An integration which worked without issue no longer is working.  PBCS cannot communicate with the Saved Search in NetSuite.

            Thanks -

            Tom

            • 4. Re: PBCS - Data Management - NetSuite Error
              pbcs_challenged

              Issue still not resolved.  We had another saved search that was working.  So we just modified that saved search in NetSuite to meet our data needs and then proceeded to build the FDMEE load etc.  Really weird behavior.  I expect this adapter has issues and Oracle has no idea how to resolve.

              • 5. Re: PBCS - Data Management - NetSuite Error
                3378057

                Hi ,

                 

                Try setting permissions of custom columns of your saved search from default\Read to Edit for the  integrator role and run your DL rule.

                 

                Cheers

                1 person found this helpful
                • 6. Re: PBCS - Data Management - NetSuite Error
                  Robert Angel

                  I know this is no comfort but I have two SRs with Oracle that are still ongoing, one trying to access Oracle Financials, the other trying to access my other pod - PBCS Test to Prod - both of which have been running for sometime and both of which Oracle have so far not found an answer for.

                   

                  The latter of which I find truly staggering as cloud to cloud on the same domain it is all in their hands!

                   

                  Oracle sell PBCS as an enterprise system and yet their DR has no defined timescale should there be complete loss of service and the system itself is still well behind on premise when it comes to integrating with other systems.

                  1 person found this helpful
                  • 7. Re: PBCS - Data Management - NetSuite Error
                    pbcs_challenged

                    I could only imagine.  Honestly the adapter to Netsuite is a joke.  I would rather get a flat file to be quite honest.  I know flat files are so 1990 however they stand the test of time and the work