Customization DS1358 Contract Number Additional Field

Customization DS1358

Contract Number Additional Field

Problem Definition:

ACME needs a Contract Number field added to the Receivables Transaction Entry and Bank Transaction Entry windows. The field needs to hold 50 characters that is not connected to another ID field within GP. This field does not need to be validated nor does it require a lookup.

The Contract Number must also be visible from the corresponding inquiry windows.

Solution Overview:

This solution will use a Virtual Field to add Contract Number to the requested windows.

Virtual Fields are added to windows at runtime and do not require modified or alternate forms. The Contract Number entered on the windows will be stored in a custom table with key fields to link back to the GP transaction tables.

The table will consist of the following fields:

Table Name: wContractNumbers

Field	Description (* Indicates Key Field)
Key String*	Required Key Field. Since this table will be used for both Bank Transactions and RM Transactions, this field will contain EITHER the Customer Number for RM Transactions or Checkbook ID for Bank Transactions.
Document Number*	The Bank Transaction Number or RM Transaction Number.
Document Type*	The Document Type. Values will match those in the Bank Transactions table or RM Transactions table.
Contract Number	The 50 character string field that stores the contract number.

Additional information regarding using the table for reporting is included in each section below.

Design Features:

Receivables Transaction Entry/Zoom

Field	Function
Contract Number	50 Character field that will be added just above the existing Currency ID field. This field will be editable on the Entry window, view-only on the Zoom window. The value will be saved to the database when the user clicks SAVE.

Table Name: wRMContractNumber

Field	Description (* Indicates Key Field)
Document Type*	The Document Type
Document Number*	The RM Document Number
Contract Number	The 50 character string field that stores the contract number

When a Receivables Transaction is created (and unposted), GP saves the record into the RM_Sales_WORK (SQL Name: RM10301) table. The primary key of this table is RM Document Type and RM Document Number. When a Contract Number needs to be entered, it will be saved to our custom table (wRMContractNumber).

The two tables can use the following relationship to be joined for reports that use the data in the RM_Sales_WORK table.

* Indicates Unique Record Key

RM_Sales_WORK		wRMContractNumber
RM Document Type-All*	————–	Document Type*
RM_Document Number-WORK*	————–	Document Number*
Customer Number		Contract Number
Etc.

After the Receivables transaction is posted, the document is moved into the RM_OPEN (RM20101) table. A unique record can also be found using the RM Document Type and Document Number. Any reports referencing the RM_OPEN table can also use the custom table by using the following join:

RM_OPEN		wRMContractNumber
RM_Document Type-All*	————–	Document Type*
RM_Document Number-WORK*	————–	Document Number*
Customer Number		Contract Number
Etc.

Bank Transaction Entry/Zoom

Field	Function
Contract Number	50 Character field that will be added below the existing Type field. This field will be editable on the Entry window, view-only on the Zoom window. The value will be saved to the database when the user clicks POST.

Table Name: wCMContractNumber

Field	Description (*Indicates Key Field)
Record Number*	This is an auto-generated record number for the Bank Transaction. It is not displayed on the window but is a unique identifier for the record.
Contract Number	The 50 character string field that stores the contract number.

When a Bank transaction is posted, GP saves the record into the CM_Transactions (SQL Name: CM20200) table or CM_Receipts (CM20300). The primary key of these tables is Record Number. When a Contract Number needs to be entered, it will be saved to our custom table (wCMContractNumber).

The tables can use the following relationship to be joined for reports that use the data in the CM_Transactions or CM_Receipts table.

* Indicates Unique Record Key

CM_Transactions		wCMContractNumber
Record Number*	————–	Record Number*
CM Transaction Number		Contract Number
CM Transaction Type
Etc.

CM_Receipts		wCMContractNumber
Record Number*	————–	Record Number*
Receipt Number		Contract Number
Receipt Type
Etc.

GP Transaction Entry/Zoom

Field	Function
Contract Number	50 Character field that will be added below the existing Type field. This field will be editable on the Entry window, view-only on the Zoom window. The value will be saved to the database when the user clicks SAVE.

Table Name: wGLContractNumber

Field	Description (*Indicates Key Field)
Journal Entry*	The JE number from the transaction
Contract Number	The 50 character string field that stores the contract number.

When a journal entry is saved, GP saves the record into the GL_TRX_HDR_WORK (SQL Name: GL10000) table. The primary key of this tables is Journal Entry. When a Contract Number needs to be entered, it will be saved to our custom table (wGLContractNumber).

The tables can use the following relationship to be joined for reports that use the data in the GL_TRX_HDR_WORK table.

* Indicates Unique Record Key

GL_TRX_HDR_WORK		wCMContractNumber
Journal Entry*	—————-	Journal Entry*
Etc.		Contract Number

After the GL Transaction is posted, the document is moved into the GL_YTD_TRX_OPEN (GL20000) table. A unique record can also be found using the Journal Entry. Any reports referencing the GL_YTD_TRX_OPEN table can also use the custom table by using the following join:

GL_YTD_TRX_OPEN		wCMContractNumber
Journal Entry*	—————–	Journal Entry*
Etc.		Contract Number

After the GL Transaction is moved to a historical year, the document is moved into the GL_YTD_TRX_HIST (GL30000) table. A unique record can also be found using the Journal Entry. Any reports referencing the GL_YTD_TRX_HIST table can also use the custom table by using the following join:

GL_YTD_TRX_HIST		wCMContractNumber
Journal Entry*	—————–	Journal Entry*
Etc.		Contract Number

Assumptions/Requirements:

1. The functionality described above will not work with eConnect, any software that uses eConnect, or any software that directly writes to, updates, or deletes from SQL tables.
2. The functionality described above is intended for the GP Desktop client.
3. National Accounts functionality is NOT used.
4. Advanced Distribution functionality is NOT used.
5. Unless otherwise noted in this document, reporting is not included in this estimate.
6. Unless otherwise noted in this document, Word Template functionality is not addressed.
7. Unless otherwise noted in this document, the enhancement will not integrate with 3^rd party products. Some examples of 3^rd party products would be:

• An ISV plug-in product including WilloWare products
• A dexterity customization designed by another developer
• Dynamics GP Modules including, but not limited to:
• Project Accounting
• MDA
• Analytical Accounting
• Copy functionality found in SOP, POP and Inventory
• Field Service
• Extended Pricing
• Manufacturing

For Information on this design, or any other WilloWare customization or product, please contact us:

www.willoware.com/contact-me