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
- AccpacERPLookup
- AccpacFiscalPeriod
- AccpacFiscalYear
- AccpacIsPeriodOpen
- AccpacRecordExists
- AccpacERPCustomLookup
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.
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.
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.
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.
Examples
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”)