Reading Data (Datamart)

Examples

Minimum parameters required to read Customers:

Specify the Datamart Tablename as the Template
Note the SubledgerCode is required for Customers (and other tables where this column is part of the primary key).

{"WebMethod": "GJZJ82J",
"Option": "download",
"PageNum": 1,
"CompanyCode": "JC",
"SubledgerCode":"AR",
"Template": "AR_Customers"}

Result set is paginated and will contain PageNum, PageSize, and PageMax values. It may be necessary to iterate over multiple pages where there are too many rows to return. Refer to the PageMax value to determine the number or pages. The number of objects per page can overridden by specifying your own PageSize value. The maximum PageSize is determined internally on a per table basis and will be automatically applied if not specified or if the provided parameter exceeds this value.

Filtering is possible using additional parameters:

{"WebMethod": "GJZJ82J",
"Option": "download",
"PageNum": 1,
"PageSize": 10,
"CompanyCode": "JC",
"SubledgerCode":"AR",
"PageNum": 1,
"SELECT": ["CustomerCode","CustomerName"],
"WHERE": [{"OP": "LIKE","CustomerCode": "A%"},
          {"OP": "LIKE","CustomerCode": "%-%"},
          {"OP": "LIKE","CustomerCode": "Z%"},
          {"OP": "BETWEEN","ETLDateModified": ["2019-01-01","2020-01-01"]},
          {"LOGIC":"((1 AND NOT(2)) OR 3) AND 4"}],
"Template": "AR_Customers"}

the keys SELECT, WHERE, OP, and LOGIC are case-sensitive (in uppercase)
SELECT node is an array of column names o All columns are selected by default when this parameter is not specified o columns that are part of the primary key are automatically selected o ETLDateModified is special column that only exists in DATAMART and can only be used for WHERE but not in SELECT.
WHERE node is an array of expression objects, where each expression must contain either OP or LOGIC node o the following operators can be used: =,>,<,>=,<=,<>,BETWEEN,LIKE,IN. o all OP expressions must contain a node with a column name as key. o BETWEEN expression must contain an array consisting of two values keyed by a column name o all expressions are applied using AND by default o LOGIC can be used to apply AND, OR, NOT combinations by referencing the expressions ordinally ▪ brackets must be applied when combining AND and OR ▪ all expressions must be referenced in your logic
DATAMART is required for the WHERE parameter to work.

For all support inquiries the json must be provided (in a text file)

GJZJ82J reference payloads

Reference payloads for GJZJ82J:

GJZJ82J sampleJSON payload

{"WebMethod":"GJZJ82J","Template":"_CUP","Option":"schema"}

Result:
{"$schema":"http://json-schema.org/draft-04/schema#","name":"_CUP template schema","type":"object","additionalProperties":false,"required":["WebMethod","Option","CompanyCode","ARSubledgerCode"],"properties":{"WebMethod":{"enum":["GJZJ82J"]},"Option":{"enum":["scan","update","schema","template","download"]},"CompanyCode":{"type":"string","pattern":"^[^a-z]*$","maxLength":2},"ARSubledgerCode":{"type":"string","pattern":"^[^a-z]*$","maxLength":5},"AR_CustomerPreferences_tbl":{"type":"array","items":{"type":"object","additionalProperties":false,"required":["CustomerCode"],"properties":{"CustomerCode":{"type":"string","minLength":1,"maxLength":10,"pattern":"^[^a-z]*$"},"PhoneNumber2":{"type":"string","maxLength":15},"Terms":{"type":"string","maxLength":15},"Modem":{"type":"string","maxLength":15},"RenewalDate":{"type":"string","format":"date-time"},"EmailAddress":{"type":"string","maxLength":36},"PORequestedInWO":{"type":"string","enum":["Y","N"]},"PreferredTechnician":{"type":"string","maxLength":15},"Spare1":{"type":"string","maxLength":15},"SellingPriceTable":{"type":"integer","maximum":99},"ZeroRatedSalesTax":{"type":"string","enum":["Y","N"]},"ContractLastVisitDate":{"type":"string","format":"date-time"},"AlternateContact":{"type":"string","maxLength":20},"TechnicianID":{"type":"string","maxLength":5,"pattern":"^[^a-z]*$"},"TechnicianID1":{"type":"string","maxLength":5,"pattern":"^[^a-z]*$"},"AlternateTechnicianID2":{"type":"string","maxLength":5,"pattern":"^[^a-z]*$"},"AlternateTechnicianID3":{"type":"string","maxLength":5,"pattern":"^[^a-z]*$"},"AlternateTechnicianID4":{"type":"string","maxLength":5,"pattern":"^[^a-z]*$"},"ExcludeFromCustomerLabels":{"type":"string","enum":["Y","N"]},"MTOCustomer":{"type":"string","enum":["Y","N"]},"Internal":{"type":"string","enum":["Y","N"]},"WebPin":{"type":"string","maxLength":6,"pattern":"^[^a-z]*$"},"DefaultContract":{"type":"string","maxLength":10,"pattern":"^[^a-z]*$"},"TermsValue":{"type":"integer","maximum":99999},"DiscountFlag":{"type":"string","enum":["Y","N"]},"DiscountedBy":{"type":"string","enum":["%","$"]},"DiscountValue":{"type":"number","multipleOf":0.01,"maximum":999999.99},"DiscountOnLabour":{"type":"string","enum":["Y","N"]},"DiscountOnMaterial":{"type":"string","enum":["Y","N"]},"DiscountOnEquipment":{"type":"string","enum":["Y","N"]},"DiscountOnTravel":{"type":"string","enum":["Y","N"]},"DiscountOnOther":{"type":"string","enum":["Y","N"]},"WOBillLabour":{"type":"string","enum":[" ","D","S","L"]},"WOBillMaterial":{"type":"string","enum":[" ","D","S","L"]},"LabourChargeOutRateNumber":{"type":"string","maxLength":2,"pattern":"^[^a-z]*$"},"EServiceForCustomer":{"type":"string","enum":["Y","N"]}}}}}}