Forum Stats

  • 3,836,878 Users
  • 2,262,201 Discussions
  • 7,900,132 Comments

Discussions

PBCS - Data Management - NetSuite Error

elwayisgod
elwayisgod Member Posts: 103
edited Dec 7, 2018 8:17AM in Planning and Budgeting

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

Tagged:
Cord234

Answers

  • elwayisgod
    elwayisgod Member Posts: 103
    edited Nov 9, 2018 5:27PM

    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
    3326272 Member Posts: 3
    edited Nov 26, 2018 12:40PM

    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

  • elwayisgod
    elwayisgod Member Posts: 103
    edited Nov 29, 2018 9:36AM

    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
    User_84P5D Member Posts: 2 Red Ribbon
    edited Dec 6, 2018 9:07PM

    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

    elwayisgod
  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Dec 7, 2018 3:48AM

    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.

    Cord234elwayisgod
  • elwayisgod
    elwayisgod Member Posts: 103
    edited Dec 7, 2018 8:17AM

    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