Requirement
The client would like to sync the customer data and customer balance data from Netsuite to the Application.
Our Solution
We will provide a REST API for the Application user to fetch the data from Netsuite to the Application.
Customer Data Sync
We will be implementing an API to fetch the customer data from Netsuite to an application.
Request : GET
Parameters:
- CompanyCode : Company code(Company code field in the subsidiary record corresponding to subsidiary field in customer record)– Mandatory
- CustomerCode : Customer Code(id:custentity_code) field in the Customer record – Mandatory
| JSON field | Netsuite Field | Mandatory | Description |
| customer | entityid | Yes | Customer ID field in customer record |
| credit_ctrl_area | custrecord_company_code | Yes | Company code field in subsidiary recordNote: Script will check the primary subsidiary field in Subsidiary record |
| credit_limit | custentity_credit_limit_pqn ,custentity_credit_limit_sppi | Yes | Custom fields “Credit limit for PQN” and “credit limit for SPPI” in the financial subtab of the customer record |
| acct_ref | entityid | Yes | Customer ID field in customer record |
| sale_value | unbilledorders | Yes | Open Sale order amount based on Company Code (subsidairy) Unbilled orders field in the subsidiaries subtab of the customer record.Value will be corresponding to the company code parameter sent from Application |
| receivables | balance | Yes | Open invoice amount based on Company Code (subsidiary)Balance field in the subsidiaries subtab of the customer record.Value will be corresponding to the company code parameter sent from Application |
| rel_spec_liabilities | depositbalance | Yes | Deposit amount based on Company Code (subsidiary)Deposit Balance in the Financial subtab of the customer subtab |
| exceeded_date | Empty string will be returned | ||
| change_obj | Empty string will be returned | ||
| created_by | Empty string will be returned | ||
| created_on | Empty string will be returned | ||
| risk_cate | Empty string will be returned | ||
| last_review | Empty string will be returned | ||
| ind_blocked_credit | Empty string will be returned | ||
| credit_rep_grp | Empty string will be returned | ||
| next_review | Empty string will be returned | ||
| credit_info_no | Empty string will be returned | ||
| cust_credit_grp | Empty string will be returned | ||
| changed_by | Empty string will be returned | ||
| ref_date | Empty string will be returned | ||
| cust_grp | Empty string will be returned | ||
| paym_date | Empty string will be returned | ||
| paym_amt | Empty string will be returned | ||
| paym_curr | Empty string will be returned | ||
| rec_credit_limit | Empty string will be returned | ||
| rec_credit_limit_curr | Empty string will be returned | ||
| secured_receivables | Empty string will be returned | ||
| credit_exposure | unbilledorders+balance-depositbalance | ||
| credit_available | Credit limit for PQN- unbilledorders + balance – depositbalance | ||
| status | Success – Can get the data in Netsuite | ||
| message | Error – Cannot get the data in Netsuite and put the error message from Netsuite.Will put the error message from Netsuite. |
Sample Response
When the request to Netsuite made was successful, the API will return a success array in JSON format
“summary”: {
“status”: “SUCCESS”,
“reason”: “RECORD_FOUND”
},
“Lines”:
[
{
“customer”: “CP01189”
“credit_ctrl_area”: “1810”
“credit_limit”: “1000000.00”
“acct_ref”: “CP01189”
“sale_value”: “150000.00”
“receivables”: “30000.00”
“rel_spec_liabilities”: “0.00”
“exceeded_date”: “”
“change_obj”: “”
“created_by”: “”
“created_on”: “”
“risk_cate”: “”
“last_review”: “”
“ind_blocked_credit”: “”
“credit_rep_grp”: “”
“next_review”: “”
“credit_info_no”: “”
“cust_credit_grp”: “”
“changed_by”: “”
“ref_date”: “”
“cust_grp”: “”
“paym_date”: “”
“paym_amt”: “”
“paym_curr”: “”
“rec_credit_limit”: “”
“rec_credit_limit_curr”: “”
“secured_receivables”:””
“credit_exposure”: “180000.00”
“credit_available”: “820000.00”
“status”: “success”
}
]
Error Response
When the request to Netsuite made was unsuccessful, the API will return a Error array
“summary”: {
“status”: “ERROR”,
“message”: ” Invalid Customer Code”,
“company_code “: “1810”,
“cust_no”: “CP01189T”
}
Error Response Messages:
- Invalid Customer Code
- Invalid Company Code
- Customer Code is null
- Company Code is null
- Records not found
Customer Balance Sync
We will be implementing an API to fetch the customer balance data from Netsuite to an application.
Request : GET
Parameters:
- CompanyCode : Company code(Company code field in the subsidiary record corresponding to subsidiary field in customer record)– Mandatory
- CustomerCode : Customer Code(id:custentity_code) field in the Customer record- Mandatory
- PageIndex: Index of the page(starting from 1)– Mandatory
Note:
- API will be returning an array of all open invoices or credit notes that are applied as response for every request made through the API. Each invoice details object will be separated by a comma.
- Page size will be limited to 25 results per page
| JSON Field | Netsuite Field | Description |
| company_code | custrecord_company_code | Company code field in subsidiary recordNote: Script will check the primary subsidiary field in Subsidiary record |
| cust_no | entityid | Customer ID field in customer record |
| fiscal_year | Year from date | |
| acct_no | tranid | Document Number in the invoice |
| post_date | trandate | Date in the Invoice |
| assign_no | tranid | Document Number in the invoice |
| doc_date | trandate | Date in the Invoice |
| bill_number | tranid | Document Number in the invoice |
| so_number | createdfrom | Createdfrom field in the invoice |
| local_amt | subtotal | Subtotal Value in the invoice(show minus if the document is Credit Note) |
| doc_amt | subtotal | Subtotal Value in the invoice(show minus if the document is Credit Note)local_amt will be the subtotal +tax totalNo.11 will be subtotal + tax total in foreign currency itself without any conversion if the currency is not IDRIf the currency is IDR, value will be loc_amt |
| doc_curr | currency | Currency field value in the invoice |
| due_date | duedate | Duedate field value in the invoice |
| due_days | Date diff (DUE_DATE, current date) | The number of days remaining to duedate from the current dateNote: If due date is already over, we will be adding a “-” before the days count |
| payment_trm | Internal ID of Term | Payment term value in the invoiceEx: 2 |
| ref_doc | tranid | Document Number in the invoice |
| doc_type | Invoice = RACredit Note = RC | Invoice = RACredit Note = RC |
| crdr_indicator | Invoice = SCredit Note = H | Invoice = SCredit Note = H |
| thai_amt | Empty string will be returned | |
| exchange_rate_thai | Empty string will be returned | |
| text | Term | Term field text in the invoice recordEx: Net 30 |
| flag_excess | Empty string will be returned | |
| flag_due | Empty string will be returned | |
| po_number | otherrefnum | PO# field in invoice |
| custom_inv | Empty string will be returned | |
| success | Success – Can get the data in Netsuite | |
| Error | error – Cannot get the data in Netsuite and put the error message from Netsuite.Will put the error message from Netsuite. |
Sample Response message
“summary”: {
“status”: “SUCCESS”,
“reason”: “RECORD_FOUND”
},
“result”: {
“pageInfo”: {
“pageLength”: 1,
“pageIndex”: 1,
“isLastPage”: true
},
“lines”: [
{
“company_code”: “1810”,
“cust_no”: “CP01189”,
“fiscal_year “: “2021”,
“acct_no”: “INVSPPI-1149”,
“post_date”: “23/10/2021”,
“assign_no”: “INVSPPI-1149”,
“doc_date”: “23/10/2021”,
“bill_number”: “INVSPPI-1149”,
“so_number”: “SOSPPI-13”,
“local_amt”: “3641550”,
“doc_amt”: “3641550”,
“doc_curr”: “IDR”,
“due_days”: “21”,
“payment_trm”: “2”,
“ref_doc”: “INVSPPI-1149”,
“doc_type “: “RA”,
“crdr_indicator “: “S”,
“thai_amt”: “”,
“exchange_rate_thai”: “”,
“text”: “Net 30”,
“flag_excess”: “”,
“po_number”: “PO12”,
“custom_inv”: “”,
“status”: “success”
},
{
“company_code”: “1810”,
“cust_no”: “CP01890”,
“fiscal_year “: “2021”,
“acct_no”: “INVSPPI-1150”,
“post_date”: “23/10/2021”,
“assign_no”: “INVSPPI-1150”,
“doc_date”: “23/10/2021”,
“bill_number”: “INVSPPI-1150”,
“so_number”: “SOSPPI-14”,
“local_amt”: “3641550”,
“doc_amt”: “3641550”,
“doc_curr”: “IDR”,
“due_days”: “21”,
“payment_trm”: “2”,
“ref_doc”: “INVSPPI-1150”,
“doc_type “: “RA”,
“crdr_indicator “: “S”,
“thai_amt”: “”,
“exchange_rate_thai”: “”,
“text”: “Net 30”,
“flag_excess”: “”,
“po_number”: “PO13”,
“custom_inv”: “”,
“status”: “success”
}
]
}
Error Response
“summary”: {
“status”: “ERROR”,
“message”: ” Invalid Customer Code”,
“company_code “: “1810”,
“cust_no”: “CP01189T”
}
Error Response Messages:
- Invalid Customer Code
- Invalid Company Code
- Customer Code is null
- Company Code is null
- Records not found
- Invalid PageIndex (should be greater than one)
API Set Up
API credentials will be shared to the Application user to fetch the data from Netsuite. The Integration record will be created in the Netsuite with Auth 1.0 credentials. The token will be generated for the customized role.
Steps in customized role setup:
- “External API User” role will be created in the account for the user to Use the API with limited permissions
- Employee records will be created for the external API user.
- Role will be added to the new user
- Token will be generated for the new customised role
Assumptions
- We will be considering all invoices with open statuses only
- Authentication will be provided for the customised role only
- We will only validate the company code and customer code is not null or the parameters is present or not
- We will be considering the invoices in open status and we will not be considering the date filter.
- The date format will be dd/mm/yyyy
- We will be using the invoice record in open status and credit Notes that are unapplied while returning the response. Also, the documents need to be approved
Risk
- The number of requests made to API is limited to a concurrency of 5.