Reading NetSuite Inventory Availability with SuiteQL

const netsuiteApi = new NetsuiteRest({
  consumer_key: process.env.NETSUITE_CUSTOMER_KEY,
  consumer_secret_key: process.env.NETSUITE_SECRET_KEY,
  token: process.env.NETSUITE_TOKEN,
  token_secret: process.env.NETSUITE_TOKEN_SECRET,
  realm: process.env.NETSUITE_ACCOUNT_ID,
  base_url: process.env.NETSUITE_API_URL,
})

interface IssueInventoryNumberByLotNumberResponse {
  id: string
  lotnumber: string
  itemid: string
  itemnumber: string
  itemname: string
  quantityavailable: string
  locationid: string
}

function listIssueInventoryNumber(
  itemNumber: string,
  locationId: number,
  lotNumber?: string
): Promise<IssueInventoryNumberByLotNumberResponse[]> {
  const normalizeLotNumber = (lotNumberToNormalize: string) => {
    try {
      // example if dates are passed in as 01/01/2025, we need to normalize it to 01/01/25

      const isDate = moment(lotNumberToNormalize, 'MM/DD/YYYY').isValid()

      if (isDate) {
        return moment(lotNumberToNormalize, 'MM/DD/YYYY').format('MM/DD/YY')
      }

      return lotNumberToNormalize
    } catch (error) {
      return lotNumberToNormalize
    }
  }

  let query = lotNumber
    ? `
		SELECT inventoryNumber.Id AS id, inventorynumber.inventorynumber AS lotNumber, inventorynumber.item AS itemId, item.itemid AS itemNumber,
			item.displayname AS itemName, ItemInventoryBalance.quantityAvailable AS quantityAvailable, ItemInventoryBalance.location AS locationId,
		FROM inventorynumber
		INNER JOIN ItemInventoryBalance ON ItemInventoryBalance.inventoryNumber = inventoryNumber.Id
		INNER JOIN item ON inventorynumber.item = item.id
		WHERE (inventorynumber.inventorynumber LIKE '%${lotNumber}%' or inventorynumber.inventorynumber LIKE '%${normalizeLotNumber(lotNumber)}%') and item.itemid = '${itemNumber}' and ItemInventoryBalance.location = ${locationId}
	`
    : `
		SELECT '' AS id, '' AS lotNumber, iteminventorybalance.item AS itemId, item.itemid AS itemNumber,
			item.displayname AS itemName, ItemInventoryBalance.quantityAvailable AS quantityAvailable, ItemInventoryBalance.location AS locationId,
		FROM ItemInventoryBalance
		INNER JOIN item ON item.id = iteminventorybalance.item
		WHERE item.itemid = '${itemNumber}' and iteminventorybalance.location = ${locationId}
	`

  return netsuiteApi
    .request({
      path: `query/v1/suiteql`,
      method: 'POST',
      body: JSON.stringify({
        q: query,
      }),
    })
    .then((response) => response.data?.items || [])
    .catch((error) => {
      const response = error && error.response
      const errorMessage = (response && response.body) || error

      return Promise.reject(errorMessage)
    })
}