Registration and Posting Process

Registration and Posting Process

Registration and Posting Process


Table of Contents




Registration

Once a record is updated via the Registered QUB or via an index_registration workflow. The data gets written to the staging tables within the Adest database. 

Indexing_Registration (workflow)

  1. Validate data via adsp_custom_registration_validation.


  • This will validate for the existence of a DocID, Company Name, Company Number, Net, VAT, Total, Document Type, Document Date, Invoice Number, PO Number, Currency


  • The VAT validation will only throw an error if the VAT Value is Null or zero, and the Net and Total value is not equal.


  • If the ValidateLineItems setting within the AD_CUSTOM_SETTINGS table is set to True, the line items will be validated. If not set, the line item validation will not be done.


  • The following is validated with the ValidateLineItems setting is set to True. Product Code, Product Description, Quantity, Unit Price, Net, VAT Code, VAT Rate, VAT, Total, CostCode, and GL Code.


  • If the NoLineItemVAT setting is set to “True” within AD_CUSTOM_SETTINGS table, the validation will not check for a NULL value within the VAT Code, VAT Rate, or VAT fields at line item level.


  1. If successful, the records are written to the staging table within the same SP as above. Option will check LineItemValidate setting if line item registration is required. Populate the Status field with New.


  1. Update ERP Status registered.


Registration Output

Option One: The output of the registration data to an ERP system is preferably done by the ERP system, which polls our staging table for any record with a status of New.

Option Two: Alternatively the data can be output to a text file for processing by the ERP system.

Option One (ERP System Link)

The ERP system will poll all records within the AD_custom_reg_criteria and ad_custom_reg_lineitem tables where the Status is equal to New. 

Once polled, all records processed must be updated to processed, by setting the status field to “Processed”.



Option Two (Flat File Output)

  • If using the flat file output method, the db polling job Registration within the ad_def_polling_job table must have the Disabled column set to “False”. Otherwise set to True to disable this output method. 
  • A db polling job (Registration) will run continuously for the day if the disabled column is set to True. This will call the SP ADSP_CUSTOM_SELECT_REG_NEW which returns all DocmasterID’s within the AD_CUSTOM_REG_CRITERIA table with status “New”.
  • The registration db polling activity returns all DocID’s to the Registration_Output db polling workflow activity. 
  • This workflow activity has the data export activity setup to export the data from the ad_custom_reg_criteria table. 
  • The data output activity creates a CSV file called AdestRegExport<today> with todays timestamp within the \\Servername\Adest\Output directory.
    • Field separator is set to pipe (|).
    • Include column headers set to True.
    • Keep temp file set to True.
    • Date output format set to DD/MM/YYYY.
    • Overwrite file set to false as a new file must be created each time.
    • Temp export location set to \\Servername\Adest\Temp
    • TextDelimiter set to None.
    • Use Stored Procedure set to True.
  • LineItemStoredProcedure is set to adsp_custom_select_reg_lineitem_output. If line item output is not required for registration, remove this setting from the activity. 
  • StoredProcedure is set to ADSP_CUSTOM_SELECT_REG_CRITERIA_OUTPUT. You can change the order of the output if required by rearranging the last select statement within the SP.
  • UpdateStoredProcedure is set to adsp_custom_update_reg_status which sets the status field within the ad_custom_reg_criteria table to processed.


Registration Confirmation

Upon successful confirmation of registration, the same record is updated with the ERP number and the status updated to “Registered”.

Option One (ERP System Link)

This is done via the ERP system which will populate the ERP number into the ERP number for the relevant DocmasterID and update the Status field to Registered.

Option Two (Flat file)

A custom import will need to be written to import the registration confirmation and update the relevant DocmasterID with the status and ERP number.

Option One or Two must be followed to carry out the above registration acknowledgement.

A DB polling job (Registration Confirmation) will poll all criteria records in Adest AP category where the ERP status is Registered and ERP number is blank, and join to the registratration staging table where status is Registered. This is done via SP ADSP_CUSTOM_SELECT_REGISTERED. This will return all DocID’s to the Registration_Confirmation workflow.

The Registration_Confirmation workflow executes a SP activity which executes ADSP_CUSTOM_UPDATE_REG_ERPNUMBER. This SP updates the ERP Number within the AP category to the ERP number populated for the DocID within the registration staging table.


Registration Error

This process allows the record to be registered again once the issue that occurred has been corrected. This is only used when an error has occurred in the ERP system once it has left Adest.

Option One (ERP System Link)

To record an error from the ERP system to Adest. The ad_custom_reg_criteria is updated with the status “Error” and a comment populated into the comment field by the ERP system.

A DB polling job “Registration Error” calls an SP called ADSP_CUSTOM_SELECT_REG_ERROR which polls for all records with the status Error within the staging table, and the ERP Status Registered within the category table, linked by DocmasterID.

Each record returned is passed to a workflow called Registration_Error. 

This workflow executes an SP (ADSP_CUSTOM_UPDATE_REG_ERPSTATUS) where the record in the category table is updated to ERP Status “Not Registered”, with the exception table populated with the comment from the ERP system with the text “ERP System Error:” proceeding the comment supplied from the ERP system. 


If no comment is supplied, an error is thrown. The ERP status is not updated and an exception added.


The existing record within the staging table gets inserted into the Audit tables and then removed from the ad_custom_reg_criteria and  ad_custom_reg_lineitem tables.

The record within the Ad_Registration for that DocID is removed.

Option Two (Flat file)

To record an error from the ERP system to Adest. The ad_custom_reg_criteria is updated with the status “Error” and a comment populated into the comment field. It is recommended that a table is created in the main database to import the data from the file. Join this table to the ad_custom_reg_criteria table and update the status to Error and populate the comment. Please see the guidelines section for a table example.

A DB polling job polls for all records with the status Error within the staging table, and the ERP Status Registered with the category table. 

Each record returned is passed to a workflow called Registration_Error. 

This executes an SP where each record in the category table is updated to ERP Status “Not Registered”, with the exception table populated with the comment from the ERP system with the text “ERP System Error:” proceeding the comment supplied from the ERP system. 

The existing record within the staging table gets inserted into the Audit tables and then removed from the ad_custom_reg_criteria and  ad_custom_reg_lineitem tables.

The record within the Ad_Registration for that DocID is removed.



Posting

Once a record is updated via the Posted QUB or via an updating_SentToERP workflow. The data gets written to the staging tables within the Adest database. 

Updating_SentToERP (workflow)

  1. Validate data via adsp_custom_registration_validation.


  • This will validate for the existence of a DocID, Company Name, Company Number, Net, VAT, Total, Document Type, Document Date, Invoice Number, PO Number, Currency


  • The VAT validation will only throw an error if the VAT Value is Null or zero, and the Net and Total value is not equal.


  • The following is validated with the ValidateLineItems setting is set to True. Product Code, Product Description, Quantity, Unit Price, Net, VAT Code, VAT Rate, VAT, Total, CostCode, and GL Code.


  • If the NoLineItemVAT setting is set to “True” within AD_CUSTOM_SETTINGS table, the validation will not check for a NULL value within the VAT Code, VAT Rate, or VAT fields at line item level.


  1. If successful, the records are written to the staging table within the same SP as above. Populate the Status field with New.


  1. Update ERP Status Sent To ERP.


Posting Output

Option One: The output of the posting data to an ERP system is preferably done by the ERP system, which polls our staging table for any record with a status of New.

Option Two: Alternatively the data can be output to a text file for processing by the ERP system.

Option One (ERP System Link)

The ERP system will poll all records within the AD_custom_post_criteria and ad_custom_post_lineitem tables where the Status is equal to New. 

Once polled, all records processed must be updated to processed, by setting the status field to “Processed”.

Option Two (Flat File Output)

  • If using the flat file output method, the db polling job Post within the ad_def_polling_job table must have the Disabled column set to “False”. Otherwise set to True to disable this output method. 


  • A db polling job (Post) will run continuously for the day if the disabled column is set to True. This will call the SP ADSP_CUSTOM_SELECT_POST_NEW which returns all DocmasterID’s within the AD_CUSTOM_POST_CRITERIA table with status “New”.
  • The post db polling activity returns all DocID’s to the Posting_Output db polling workflow activity. 
  • This workflow activity has the data export activity setup to export the data from the ad_custom_post_criteria table. 
  • The data output activity creates a CSV file called AdestPostExport<today> with todays timestamp within the \\Servername\Adest\Output directory.
    • Field separator is set to pipe (|).
    • Include column headers set to True.
    • Keep temp file set to True.
    • Date output format set to DD/MM/YYYY.
    • Overwrite file set to false as a new file must be created each time.
    • Temp export location set to \\Servername\Adest\Temp
    • TextDelimiter set to None.
    • Use Stored Procedure set to True.
  • LineItemStoredProcedure is set to adsp_custom_select_post_lineitem_output.
  • StoredProcedure is set to ADSP_CUSTOM_SELECT_POST_CRITERIA_OUTPUT. You can change the order of the output if required by rearranging the last select statement within the SP.
  • UpdateStoredProcedure is set to adsp_custom_update_post_status which sets the status field within the ad_custom_post_criteria table to processed.


Posting Confirmation

Upon successful confirmation of registration, the same record is updated with the ERP number and the status updated to “Posted”.

Option One (ERP System Link)

This is done via the ERP system which will populate the ERP number into the ERP number for the relevant DocmasterID and update the Status field to Posted within the ad_custom_post_criteria table.

Option Two (Flat file)

A custom import will need to be written to import the posting confirmation and update the relevant DocmasterID with the status and ERP number.

Option One or Two must be followed to carry out the above posting acknowledgement.

A DB polling job (Posting Confirmation) will poll all criteria records in Adest AP category where the ERP status is Sent To ERP and ERP number is blank, and join to the posting staging table where status is Posted. This is done via SP ADSP_CUSTOM_SELECT_POSTED. This will return all DocID’s to the Posting_Confirmation workflow.

The Posting_Confirmation workflow executes a SP activity which executes ADSP_CUSTOM_UPDATE_POST_ERPNUMBER. This SP updates the ERP Number within the AP category to the ERP number populated for the DocID within the posting staging table.


Posting Error

This process allows the record to be posted again once the issue that occurred has been corrected. This is only used when an error has occurred in the ERP system once it has left Adest.

Option One (ERP System Link)

To record an error from the ERP system to Adest. The ad_custom_post_criteria is updated with the status “Error” and a comment populated into the comment field by the ERP system.

A DB polling job polls for all records with the status Error within the staging table, and the ERP Status Sent To ERP within the category table. 

Each record returned is passed to a workflow called Posting_Error. 

This executes an SP where each record in the category table is updated to ERP Status “Not Posted”, with the exception table populated with the comment from the ERP system with the text “ERP System Error:” proceeding the comment supplied from the ERP system. 

The existing record within the staging table gets inserted into the Audit tables and then removed from the ad_custom_post_criteria and  ad_custom_post_lineitem tables.

The record within the Ad_Posted for that DocID is removed.

Option Two (Flat file)

To record an error from the ERP system to Adest. The ad_custom_post_criteria is updated with the status “Error” and a comment populated into the comment field. It is recommended that a table is created in the main database to import the data from the file. Join this table to the ad_custom_post_criteria table and update the status to Error and populate the comment. Please see the guidelines section for a table example.

A DB polling job polls for all records with the status Error within the staging table, and the ERP Status Sent To ERP within the category table. 

Each record returned is passed to a workflow called Posting_Error. 

This executes an SP where each record in the category table is updated to ERP Status “Not Posted”, with the exception table populated with the comment from the ERP system with the text “ERP System Error:” proceeding the comment supplied from the ERP system. 

The existing record within the staging table gets inserted into the Audit tables and then removed from the ad_custom_post_criteria and  ad_custom_post_lineitem tables.

The record within the Ad_Posted for that DocID is removed.




Guidelines




Table Schema

AD_CUSTOM _REG_CRITERIA

Column Name

Datatype

Constraints

DocMasterID

Bigint

NOT NULL, PK

CompanyName

Nvarchar(50)

NOT NULL

CompanyNumber

Nvarchar(20)

NOT NULL

SupplierCode

Nvarchar(50)

NOT NULL

DocumentType

Nvarchar(30)

NOT NULL

DocumentDate

SMALLDATETME

NOT NULL

InvoiceNumber

Nvarchar(20)

NOT NULL

PONumber

Nvarchar(50)

NOT NULL

Net

Money

NOT NULL

VAT

Money

NOT NULL

Total

Money

NOT NULL

Currency

Nvarchar(3)

NOT NULL

PostingDate

Smalldatetime

NOT NULL

InsertDate

Smalldatetime

NOT NULL

UserName

Nvarchar(100)

NOT NULL

Status

Nvarchar(10)

New, Processed, Registered, Posted, Error, NOT NULL

Comment

Nvarchar(200)

NOT NULL

ERPNumber

Nvarchar(50)

NOT NULL


AD_CUSTOM_REG_LINEITEM

Column Name

Datatype

Constraints

DocMasterID

Bigint

NOT NULL, PK

ProductCode

Nvarchar(50)

NOT NULL

ProductDescription

Nvarchar(200)

NOT NULL

CostCode

Nvarchar(20)

NOT NULL

GLCode

Nvarchar(20)

NOT NULL

UnitsOrdered

Decimal(18,2)

NOT NULL

UnitPrice

Money

NOT NULL

Net

Money

NOT NULL

VATCode

Nvarchar(12)

NOT NULL

VATRate

Nvarchar(6)

NOT NULL

VAT

Money

NOT NULL

Total

Money

NOT NULL

Status

Nvarchar(10)

NOT NULL

Comment

Nvarchar(200)

NOT NULL





AD_CUSTOM _POST_CRITERIA

Column Name

Datatype

Constraints

DocMasterID

Bigint

NOT NULL, PK

CompanyName

Nvarchar(50)

NOT NULL

CompanyNumber

Nvarchar(20)

NOT NULL

SupplierCode

Nvarchar(50)

NOT NULL

DocumentType

Nvarchar(30)

NOT NULL

DocumentDate

SMALLDATETME

NOT NULL

InvoiceNumber

Nvarchar(20)

NOT NULL

PONumber

Nvarchar(50)

NOT NULL

Net

Money

NOT NULL

VAT

Money

NOT NULL

Total

Money

NOT NULL

Currency

Nvarchar(3)

NOT NULL

PostingDate

Smalldatetime

NOT NULL

InsertDate

Smalldatetime

NOT NULL

UserName

Nvarchar(100)

NOT NULL

Status

Nvarchar(10)

New, Processed, Registered, Posted, Error, NOT NULL

Comment

Nvarchar(200)

NOT NULL

ERPNumber

Nvarchar(50)

NOT NULL


AD_CUSTOM_POST_LINEITEM

Column Name

Datatype

Constraints

DocMasterID

Bigint

NOT NULL, PK

ProductCode

Nvarchar(50)

NOT NULL

ProductDescription

Nvarchar(200)

NOT NULL

CostCode

Nvarchar(20)

NOT NULL

GLCode

Nvarchar(20)

NOT NULL

UnitsOrdered

Decimal(18,2)

NOT NULL

UnitPrice

Money

NOT NULL

Net

Money

NOT NULL

VATCode

Nvarchar(12)

NOT NULL

VATRate

Nvarchar(6)

NOT NULL

VAT

Money

NOT NULL

Total

Money

NOT NULL

Status

Nvarchar(10)

NOT NULL

Comment

Nvarchar(200)

NOT NULL





AD_CUSTOM _CRITERIA_AUDIT

Column Name

Datatype

Constraints

DocMasterID

Bigint

NOT NULL, PK

CompanyName

Nvarchar(50)

NOT NULL

CompanyNumber

Nvarchar(20)

NOT NULL

SupplierCode

Nvarchar(50)

NOT NULL

DocumentType

Nvarchar(30)

NOT NULL

DocumentDate

SMALLDATETME

NOT NULL

InvoiceNumber

Nvarchar(20)

NOT NULL

PONumber

Nvarchar(50)

NOT NULL

Net

Money

NOT NULL

VAT

Money

NOT NULL

Total

Money

NOT NULL

Currency

Nvarchar(3)

NOT NULL

PostingDate

Smalldatetime

NOT NULL

InsertDate

Smalldatetime

NOT NULL

UserName

Nvarchar(100)

NOT NULL

Status

Nvarchar(10)

New, Processed, Registered, Posted, Error, NOT NULL

Comment

Nvarchar(200)

NOT NULL

ERPNumber

Nvarchar(50)

NOT NULL


AD_CUSTOM_LINEITEM_AUDIT

Column Name

Datatype

Constraints

DocMasterID

Bigint

NOT NULL, PK

ProductCode

Nvarchar(50)

NOT NULL

ProductDescription

Nvarchar(200)

NOT NULL

CostCode

Nvarchar(20)

NOT NULL

GLCode

Nvarchar(20)

NOT NULL

UnitsOrdered

Decimal(18,2)

NOT NULL

UnitPrice

Money

NOT NULL

Net

Money

NOT NULL

VATCode

Nvarchar(12)

NOT NULL

VATRate

Nvarchar(6)

NOT NULL

VAT

Money

NOT NULL

Total

Money

NOT NULL

Status

Nvarchar(10)

NOT NULL

Comment

Nvarchar(200)

NOT NULL


1 | Page




    • Related Articles

    • KB - Could not load file or assembly

      Knowledge Base Article Could not load file or assembly 'Adest.Base.Info' or one of its dependencies. An attempt was made to load a program with an incorrect format. Product AdestWebV5 Engineer Cathal O’Brien Date 23/07/2012 Symptoms An following ...

    • Manual - Adest Client Matching Module

           Contents Overview PO Matching Module Access Rights Accessing PO Matching Module from the Retrieve Module Enabling/Disabling Match Button Loading PO Matching Module: Matching Module Design/Layout Toolbar Action Group QUB Group E.G. Matching ...

    • Manual - Adest Web Administration

      Adest User Administration Introduction The follow document describes the use of the Adest Web Administration site. This includes the process for adding and changing existing users within the Adest system.  Adding New Users The web administration page ...

    • SQL Server Installation Guide

      Adest SQL Server Installation Guide  Copyright This material is intended for Adest Limited employees, resellers, and their clients. Quotation, citation, attribution, reproduction, or utilisation of any portion of this publication by any other party ...

    • Manual - Adest Web Retrieve

      Contents Introduction Connect to Adest Disconnect from Adest User Interface Ribbon Bar Adest Criteria Adest Grid (Results Grid) Group Results Filter Results Document Viewer Quick Update Buttons Invoice Actions Line Items Add a line item Delete a line ...