Skip to content

Sage 300

These functions are specific to Sage 300 (Accpac).

The functions fail if IMan does not have the Sage 300 licence feature, or if the Sage 300 libraries are not installed on the IMan server.

AccpacCalcCustPrice

Description

Calculates the price a customer will pay for an item, as if the item were entered on the O/E Order Entry screen.

Syntax

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

Arguments

  • systemid
    • The system id of the company to query.
  • 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.
    • For any field not named in flds, or if you omit flds, the function takes defaults and values from the relevant Sage 300 views (customer, location detail record, tax group and so on).
    • For a full list of fields, see the Sage 300 Object Model for view OE0630.
    • The number of values specified in flds and values must match.
  • values
    • Optional. A single value or an array of values, one for each field name in flds.
  • returnfld
    • Optional. The field to return. If omitted, the function returns the calculated price for the parameters supplied.
  • errornoprice
    • Optional. When True, the function raises an error if it cannot calculate a price for the parameters. When empty or False, it returns 0.

Example

A simple price calculation on the TRADECO system, with %Customer, %Item and %Qty as the customer, item and quantity:

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

The same calculation with one additional parameter, the order unit:

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

The same calculation with several additional parameters:

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

AccpacERPLookup

Description

Looks up a record in an Accpac company database and returns a field from it, in the same way that Lookup queries a value in a SQL database.

The lookup queries by the Accpac ViewId and Order values. See the Accpac Object Model for details.

Syntax

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

Arguments

  • systemid
    • The system id of the company to query.
  • queryorder
    • The order id for the query.
  • accpacview
    • The view Id to query.
  • queryfields
    • A single string or an array of strings naming the fields to query on.
  • queryvalues
    • A single value or an array of values used to browse or 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, in the Accpac company set up 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
    • The system id of the company whose fiscal calendar to query.
  • Date
    • The date to find the fiscal period for.

Example

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

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

AccpacFiscalYear

Description

Returns the fiscal year from a date.

Syntax

AccpacFiscalYear( systemid, date )

Arguments

  • systemid
    • The system id of the company whose fiscal calendar to query.
  • date
    • The date to find the fiscal year for.

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("TRADECO", %OrdDate)          ' 12/03/2026 returns "2026"
CInt(AccpacFiscalYear("TRADECO", %OrdDate))    ' Returns 2026

AccpacIsPeriodOpen

Description

Checks whether the fiscal period that a date falls in is open.

Syntax

AccpacIsPeriodOpen( systemid, date )

Arguments

  • systemid
    • The system id of the company whose fiscal calendar to query.
  • date
    • The date to find the fiscal year for.

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. Fall back to today when the order's
' own period is closed:
Dim PostDate
PostDate = Date()
If AccpacIsPeriodOpen("TRADECO", %OrdDate) Then PostDate = %OrdDate
PostDate

Or stop the record outright, with a message on the audit log:

Check(AccpacIsPeriodOpen("TRADECO", %OrdDate), "PERIOD", "Fiscal period closed for " & %OrdDate)

AccpacRecordExists

Description

Checks whether a record exists in an Accpac company database, in the same way that Lookup queries a value in a SQL database.

The lookup queries by the Accpac ViewId and Order values. See the Accpac Object Model for details.

Syntax

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

Arguments

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

Example

To check whether an account exists in the Z/I Intercompany Accounts view (ZI0029), in the Accpac company set up in the INTERCO system:

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

AccpacERPCustomLookup

Description

Looks up a value in the Sage 300 database with a SQL query.

Remarks

Unlike the other AccpacXXX lookups, this one runs SQL directly against the Sage 300 database (through the CS0120 view) in place of querying the standard Sage 300 views.

Syntax

AccpacERPCustomLookup ( systemid, query, returnfld )

Arguments

  • systemid
    • The system id of the company to query.
  • query
    • The SQL query.
  • returnfld
    • The field to return.

Example

To query the AROBL table for the Batch Number field, in the Accpac company set up in the TRADECO system:

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