Skip to content

Sage 300

The following functions are specific to use for Sage300 (AccPac).

The functions will fail without the Sage300 license feature and/or the Sage300 libraries are not installed on the IMan server.

AccpacCalcCustPrice

Description

Calculates the price of an item a customer will receive as if by entering it through the O/E Order Entry screen.

Syntax

AccpacCalcCustPrice( systemid, customer, item, qty, flds, values, returnfld, errornoprice )

Arguments

  • systemid
    • System Id of the company to be queried.
  • customer
    • The customer id.
  • item
    • The formatted or unformatted item code.
  • qty
    • The quantity to order or calculate the price for.
  • flds
    • Optional. A single field name or an array of field names used as additional parameters to calculate the price.
    • Defaults and values will be obtained from the relevant Sage300 views (customer, location detail record, tax group, etc) if a field is not specified in this parameter or when the flds argument itself is not passed into the function.
    • For a full list of fields please consult the Sage300 Object Model for view OE0630.
    • The number of values specified in flds and values must match.
  • values
    • Optional. A single value or array or values corresponding to the field names specified in the flds argument.
  • returnfld
    • Optional. The field to return. If no value is specified the Calculated Price corresponding to the supplied parameters is returned.
  • errornoprice
    • Optional. When set to True will raise an error when no price for the specified parameters can be calculated. When empty or set to False the function will return 0.

Example

A simple price calculation on the TRADECO system for where the field replacements for Customer, Item and quantity are %Customer, %Item & %Qty respectively.

AccpacCalcCustPrice("TRADECO", %Customer, %Item, %Qty)

A price calculation specifying a single additional parameter of the Order Unit on the TRADECO system using the same parameters are above.

AccpacCalcCustPrice("TRADECO", %Customer, %Item, %Qty, "ORDERUNIT", %UnitOfMeasure)

A price calculation specifying a multiple additional parameters on the TRADECO system using the same parameters are above.

AccpacCalcCustPrice("TRADECO", %Customer, %Item, %Qty, Array("ORDERUNIT", "ORDERDATE"), Array(%UnitOfMeasure, %OrdDate))

AccpacERPLookup

Description

Looks up and returns a record in an Accpac company database in a field in a similar fashion to Lookup queries a value in a SQL database.

The lookup uses the Accpac ViewId and Order values for querying. Consult the Accpac Object Model for further details.

Syntax

AccpacERPLookup( systemid, queryorder, accpacview, queryfields, queryvalues, returnfld )

Arguments

  • systemid
    • System Id of the company to be queried.
  • queryorder
    • The order id for the query.
  • accpacview
    • The view Id to query.
  • queryfields
    • A single string or array of strings for the fields used to specify the query.
  • queryvalues
    • A single value or an array of values used to browse of filter the view. The number of values specified in queryfields and queryvalues must match.
  • returnfld
    • The field to return.

Example

To query the A/R Open Documents view (AR0036) for the Batch Number field for the Accpac company specified in the TRADECO system.

AccpacERPLookup(“TRADECO”, 0, “AR0036”, Array(“IDCUST”, “IDINVC”, Array(%Customer, %DocNo), “CNTBTCH”)

AccpacFiscalPeriod

Description

Returns the fiscal period from a date.

Syntax

AccpacFiscalPeriod( systemid, date )

Arguments

  • systemid
    • System Id of the company from which the fiscal calendar is to be queried.
  • Date
    • The date from which the fiscal period will be determined.

Example

' The fiscal period a date falls in, for the connected
' Sage 300 company.
AccpacFiscalPeriod(%OrdDate)   ' 12/03/2026, calendar year, returns 3

' Stamp the period onto the record being posted:
AccpacFiscalPeriod(CDate(%OrdDate))

AccpacFiscalYear

Description

Returns the fiscal year from a date.

Syntax

AccpacFiscalYear( systemid, date )

Arguments

  • systemid
    • System Id of the company from which the fiscal calendar is to be queried.
  • date
    • The date from which the fiscal year will be determined.

Example

' The fiscal year a date falls in. This is the FISCAL
' year, which only matches the calendar year when the
' company's year starts in January.
'
' It returns a STRING, not a number, so compare it as
' one or cast it.
AccpacFiscalYear(%OrdDate)          ' 12/03/2026 returns "2026"
CInt(AccpacFiscalYear(%OrdDate))    ' Returns 2026

AccpacIsPeriodOpen

Description

Checks if the fiscal period represented corresponding to the date is open.

Syntax

AccpacIsPeriodOpen( systemid, date )

Arguments

  • systemid
    • System Id of the company from which the fiscal calendar is to be queried.
  • date
    • The date from which the fiscal-year will be determined.

Example

' Whether the period a date falls in is open, according
' to the company's FISCAL CALENDAR. It is not a per-
' module or per-ledger test, so a period this reports as
' open can still be closed for a particular module.
'
' Guard an import with it rather than letting Sage 300
' reject the batch.
If AccpacIsPeriodOpen(%OrdDate) Then
  %OrdDate
Else
  Date()
End If   ' Falls back to today when the order's own period is closed

' Or stop the record outright, with a message on the
' audit log:
Check(AccpacIsPeriodOpen(%OrdDate), "PERIOD", "Fiscal period closed for " & %OrdDate)

AccpacRecordExists

Description

Checks for existence of a record in an Accpac company database in a field in a similar fashion to Lookup queries a value in a SQL database.

The lookup uses the Accpac ViewId and Order values for querying. Consult the Accpac Object Model for further details.

Syntax

AccpacERPLookup( systemid, queryorder, accpacview, queryfields, queryvalues )

Arguments

  • systemid
    • System Id of the company to be queried.
  • queryorder
    • The order id for the query.
  • accpacview
    • The view Id to query.
  • queryfields
    • A single string or array of strings for the fields used to specify the query.
  • queryvalues
    • A single value or an array of values used to browse of filter the view. The number of values specified in queryfields and queryvalues must match.

Example

To query the Z/I Intercompany Accounts view (ZI0029) for the existence of an account in a company for the Accpac company specified in the INTERCO system.

AccpacRecordExists(“INTERCO”, 0, “ZI0029”, Array(“IDCOMP”, “ACCTID”, Array(%Company, %AccoutNo))

AccpacERPCustomLookup

Description

Performs a lookup against the Sage300 using a SQL query.

Remarks

This lookup differs from the other AccpacXXX lookups in that the query will be executed directly against the Sage300 database with SQL (via the CS0120 view) as opposed to querying the standard Sage300 views.

Syntax

AccpacERPCustomLookup ( systemid, query, returnfld )

Arguments

  • system
    • of the company to be queried.
  • query
    • the SQL query.
  • returnfld
    • is the field to return.

Example

To query the AROBL table for the Batch Number field for the Accpac company specified in the TRADECO system.

AccpacERPCustomLookup(“TRADECO”, “select * from AROBL where IDCUST = ‘1200’ and IDINVC = ‘IN00036’”, “CNTBTCH”)