NetSuite

Business Scenario

There is a need to get the NetSuite data including the financial, sales, inventory etc. to be integrated with the Oracle Analytics Cloud for analysis and reporting. Need was to develop an Integration Tool (not using DataSync) for connecting the OAC (Oracle Analytics Cloud) enterprise with NetSuite ERP, if data in NetSuite is to be analyzed using a cube with multiple dimensions and data visualization.

Solution

The solution provides for a connector tool which uses the NetSuite JDBC/ODBC connect features and OAC APIs (Essbase Cube) to transfer the financial, sales, inventory data in the NetSuite ERP system to the Essbase Cube for data analytics.

Features

• Custom API based connector to fetch data directly using ODBC/JDBC layer from NetSuite and load it into OAC (Essbase Cube) using the oracle delivered Essbase Cube APIs.
• SOX compliant since there is no human interaction needed to migrate and manipulate the data from NetSuite into OAC (Essbase Cube)
• The connector can be called on-demand basis or can be scheduled to run automatically
• The connector we developed will reduce the implementation time by half as we don’t have to worry about running the reports in NetSuite and downloading them to .CSV file and then uploading it to OAC (Essbase Cube) using Data Sync tool
• Also, the connector can be extended to as many modules from NetSuite as possible to load into OAC (Essbase Cube). We build it for Financial Module but can be extended to others.

Potential Reporting

• GL/Financial Reporting – Trial Balance, etc.
• Accounts Payable Aging by Invoice
• Account Receivables Aging by Invoice
• Expenses by Vendor Report
• Profitability by Customer Report
• Sales Analysis
• Inventory Analysis

Business Scenario

As part of NetSuite invoice payments feature, there is a business need for EMEA (Middle East Asia) to address a functional gap of capturing and applying post-dated-checks from customers to a single invoice over pre-defined future dates. The system should provide a placeholder for capturing the post-dated cheque details, used for paying an invoice, as well as the status of the individual cheques (cleared, cancelled, dishonored). The solution should ensure the invoice payments, accounting impact, and customer balance updates properly. The solution should enable the bank reconciliation to happen for the specific cheques.

Existing NetSuite Process

The NetSuite system does not provide a placeholder for capturing the post-dated cheques used for paying individual invoices. It does allow for installment payments of invoice but has the limitation of the amounts and periods to be distributed equally (e.g. EMIs). It also does not have the flexibility of designating individual invoices for a sales order to be paid using PDC (post-dated cheques).

Solution

• The solution will overcome the limitations in the existing NetSuite process, by enabling the AR (receivables) accountants, to capture all the relevant cheque details for the post-dated cheques used for paying an invoice.
• It will allow for designating individual invoices to be paid using post-dated cheques.
• It provides for flexibility of accounting the payments either as ‘Undeposited Funds’ or be captured in the ‘PDC Accounts’.
• It provides for validating the dates on which the cheques can be deposited to the bank and any further actions applicable on the individual cheques.
• It provides for flexibility of adding additional cheques in case of existing post-dated cheques being cancelled/dishonoured. It provides for validating the cheque amounts to not exceed the invoice balance, ensuring that, any adjustments of the invoice happening outside of this feature is also taken care of.
• The solution ensures that the relevant AR (Accounts Receivables) account and bank accounts are properly updated per the individual cheque payments/cancellations/dishonored.
• The solution ensures that the customer balance and aging are properly updated.
• The solution allows for the bank reconciliation feature provided by the NetSuite to consider the individual post-dated cheque transactions.
• It provides the flexibility of depositing the individual post-dated cheques to different bank accounts per the need of the business.
• It provides for the users to capture the correct dates on which the cheques have been deposited/cleared to enable proper bank reconciliation. It provides a one stop solution for the AR accountant to take care of all the above scenarios.
• It provides for reminders to the AR accountants, detailing any cheques to be deposited in the next week.
• It also sends out the email notification to the individual customers providing details of the post-dated cheques, which are due to be deposited to the bank, to enable them to ensure adequate balance.

Business Scenario

As part of employee creation in the NetSuite ERP, there is a need to get the employees to be I-9 verified using the Smart E-Verify system. The new employee data will be captured in an HR Employee details form filled by the individuals, which will then be entered in NetSuite by the HR personnel. There should be provision for capturing all the required details mentioned in the “Section-1” employee I-9 verification and must be validated per the USCIS requirements for e-verification. Once validated, the same details should be sent over to the Smart E-Verify system, and the HR administrators responsible for the I-9 verification, should then proceed with the pre-filled data in the Smart E-Verify, to get the individuals verified.

Existing NetSuite Process

The employees for any subsidiary are created by the HR (Human Resource) personnel, using specific NetSuite Roles. NetSuite also allows the employees to be imported into the system, using excel templates. The employees are created, by providing all the required details, including some additional details for US employees like: Resident Status, Social Security Number, Alien Number, Authorized to work until… etc. The NetSuite system does not provide for capturing all the required details to be used for the mandatory I-9 verification for all the US new hires. Also, it does not validate the data entered in some of the existing fields as mentioned above.

Solution

• The solution will overcome the limitations in the existing NetSuite process, by enabling the HR personnel, to capture all the relevant and required details for the new hires in the US based subsidiaries, for getting the mandatory I-9 verification done via, SmartE-Verify application.
• It will get all the required employees’ I-9 Section -1 data integrated and pre-filled in the Smart E-Verify.
• It provides for validating the employee data in the I-9 verification form, per the existing guidelines of USCIS Department of Homeland Security, before being sent over to the SmartE-Verify application. Details below.
• It provides for I-9 integration to work, when the employees are being imported into the NetSuite system, using the csv templates.
• Provides for re-triggering the data integration process for any unforeseen integration failure.
• The solution in no way impedes the hire/creation of employees for any other subsidiary, allowing the business as usual approach for non-US subsidiary employees.
• Provides for secure access to the designated personnel to add/view the employee data.
• Allows for employees to be created using the NetSuite Mobile App.
• Context help for the I-9 fields, tailored to reflect the help shown in a USCIS I-9 application form.

Employee Data Validations

Key validation requirements as part of I-9 verification integration are

• Employees (new hires) of the Subsidiaries based in United States, created/imported into the NetSuite system should be allowed to capture all the relevant and required details, mandatory to complete the I-9 e-verification.
• The data/values captured for the I-9 verification must be mandatorily validated against the below per the requirement of USCIS. ⦁ Mandatory fields should be employee Phone Number, Email Address, Home Address, birth date, Resident Status.
  • Home Address must be provided of residence country, which must be either of US, Mexico or Canada. The ZIP code of the provided address should be validated according to the required format (5- digit for US and Mexico, and alphanumeric zip code in the format 1A1A1A for Canada).
  • Birth Date: should be validated for the correct date format, as well as, to ensure that the date provided is neither in future nor beyond 120 years in the past.
  • Phone Number, if provided should be consisting of 10 digits.
  • Social Security Number, if provided should consists of 9 digits, with no alphabets or special characters. Allow for capture of details for employees who have applied for but have not yet received a Social Security Number.
  • Attesting to the Citizenship or Immigration Status.
    • A citizen of the United States.
    • A noncitizen national of the United States.
    • A Lawful Permanent resident: The system should make it mandatory to either enter the 7-9-digit USCIS number or Alien registration number (A number) in the proper format.
    • An alien authorized to work: The system should make it mandatory to enter one of the following:
      • 7-9 digits Alien Registration Number/USCIS number; or
      • 11 digits Form I-94 admission number; or
      • Foreign Passport Number and the Country of Issuance
      • Work Authorization Expiry date/No Expiration date.

NetSuite E-Verify Swimlane Design