PBCS - Data Management - NetSuite Error

elwayisgod

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!!!

elwayisgod

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'[email protected]', '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

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

User_84P5D

Worth Checking?

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=425344169037653&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&…

elwayisgod

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.

User_84P5D

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

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.

elwayisgod

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