Scenario: User wants to be able to attach one set of attachments and be able to access those attachments from any of the lines in a GL Journal Entry.

Example Solution:

  1. Open SSMS (SQL Management Studio) and connect to your SQL Server
  2. Copy and paste the SQL Query below into a new Query window
  3. Review the query to ensure you copied the entire query and didn’t miss and of the WHERE clause as that would cause unexpected results.
  4. Execute the query to create the stored procedure.
  5. Open a new query window and copy and paste GRANT EXECUTE ON [dbo].[CTS_GLDBAttachtoAll] TO [VCSUsers] into the query window and execute the query.
  6. Open VA Custom Form Buttons in Vista
  7. Add a new button record for form GLJE.
  8. Title the button Sync Attachments to All Lines
  9. Set action type to Stored Procedure
  10. Set Action to CTS_GLDBAttachtoAll
  11. Set Button Refresh to Refresh Data Set.
  12. Switch to the parameter tab
  13. Setup Parameters as follows:
    1. @GLCo,Type=4
    2. @Mth,Type=3,DefaultValue=1
    3. @BatchID,Type=3, DefaultValue=2
    4. @BatchSeq,Type=3,DefaultValue=10
  14. Open the GL Journal Transaction Entry form.
  15. Select move custom buttons from the tools menu.
  16. Drag the new button to a desired location, typically to the right of the reference total.
  17. Accept the location by clicking accept on the sidebar panel.
  18. Test button to validate functionality before using on production entries by using the following steps.
  19. Enter a journal entry and then attach attachments to one line.
  20. Select the line with the attachments and then click the Sync Attachments to All Lines button to sync the attachments to the remaining lines in the journal entry.
    1. A couple of items to note on this functionality:
      1. This works by sharing the UniqueAttchID (which is Vista’s pointer to a record contains the links to the attachments) across all of the lines in the journal entry.
      2. Only use this if you want this attachments shared with all lines in the batch. SO don’t use this if you have an reclass entry and a WIP adjustment in the same batch.
      3. If desired, the Stored procedure could be modified to restrict the attachment sync to only the lines with the same Reference.
      4. If another line already has attachments this will override the UniqueAttchID of that line and orphan those attachments in the system.
  21. Verify the attachments on each line of the entry.
  22. One the first production entry, verify the attachments using GL Trial Balance Drilldown.

SQL Query

USE [Viewpoint]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[CTS_GLDBAttachtoAll] @GLCo bCompany 
, @Mth bMonth 
, @BatchID INT 
, @BatchSeq INT 
, @ReturnMessage VARCHAR(255) = NULL OUTPUT

AS
/*
* Created by: Constructive Tech Solutions kah 2020.12.18
* (C) 2020 Constructive Tech Solutions, All Rights Reserved 
* PURPOSE: Copies the Unique Attachment ID so that Attachments can be shared across an entire Journal Entry without reattaching multiple copies of the files
*
* SOURCES: GLDB
*
* Change History:
* 
*/

DECLARE @msg varchar(1000)
, @rows INT

BEGIN

UPDATE GLDB
SET UniqueAttchID = (select UniqueAttchID
FROM GLDB
WHERE Co = @GLCo
and Mth = @Mth
and BatchId = @BatchID
and BatchSeq = @BatchSeq)
FROM GLDB b
WHERE b.Co = @GLCo
and b.Mth = @Mth
and b.BatchId = @BatchID

set @rows = @@ROWCOUNT

SELECT @ReturnMessage = ISNULL(@msg, CAST((@rows-1) AS varchar(10)) + ' lines were linked to the attachments from sequence ' + ISNULL(CAST((@BatchSeq) AS varchar(10)),'**Error**'))

END
GO