[Steven Mayhue]

I am on GP2015R2 and have just started using AP workflows.  I am finding that you must attach files from the ribbon to be able to send in emails.  The problem I just encountered was that the DOCNUMBR in the CO00102 table is NOT the DOCNUMBR from the tramsaction, further I am seeing the wrong VENDORID listed in the same table.  Has anyone else seen this behavior?

I discovered this when my attachment viewer application stopped working for these new attachments which were not attached to Notes. using the query below:

string query = @"SELECT a.VENDORID,
		                    CAST(RTRIM(REPLACE(REPLACE(REPLACE(a.DOCNUMBR,'/',''),'.',''),'-','')) as varchar(21)) as InvoiceNumber,
		                    c.fileName,
		                    c.BinaryBlob
                    FROM   PM30200 a
                           INNER JOIN CO00102 b
                                   ON CONVERT(VARCHAR(MAX), CONVERT(BINARY(4), Cast(a.NOTEINDX AS INTEGER)), 2) = RIGHT(RTRIM(b.BusObjKey), 8)  
		                    INNER JOIN coAttachmentItems c ON c.Attachment_ID = b.Attachment_ID
                            WHERE a.DOCNUMBR = '{0}'";

Example data from query now:

VENDORID	DocNUMBR	noteindx	BusObjKey
REA03	7539	136817	0PMPayables Transaction History1~00021671
REA03	7539	136817	0PMPayables Transaction History1~00021671
ROG01	7540	136818	0PMPayables Transaction History1~00021672
ROG01	7540	136818	0PMPayables Transaction History1~00021672

First 2 belong to VENDORID AME01 for Invoice 117129
second 2 belong to VENDORID TLC01 for Invoice 1074

——————————
Steven Mayhue
Lead Developer/DBA
My Florida Regional MLS
Altamonte Springs FL
——————————

[Mahmood M. AlSaadi]

I have worked with both; workflow and document attachment, but never encountered such a case. May I ask you to use the script on my blog and provide the results back here in the forum ?

Script >> Attachment Report – All Attachments (Cards and Transactions)

Your feedback is highly appreciated,

——————————
Mahmood M. AlSaadi – MVP
Principal Dynamics ERP Consultant
SIROCo LLC – (844) 708-0008
McKinney TX
m.saadi@sirocollc.com
http://www.sirocotech.com
——————————
——————————————-

[Steven Mayhue]

I found out that the issue is that when using Attachments in Workflows for AP that the BusObjKey in the CO00102 and CO00105 is no longer the hex representation of the NOTEINDX of the PM30200 record.  It is now the VCHRNMBR from the PM30200 record!  In the query below see the change to the ON x=y after the JOIN to CO00102 table.

Using Notes attachments you get a BusObjKey of the format
     0SystemNoteshex representation of the NOTEINDX of the PM30200 record

Using Attachments from the ribbon you get the format 
     0PMPayables Transaction History1~VCHRNMBR from the PM30200 record
or
     0PMPayables Transaction1~VCHRNMBR from the PM30200 record

I changed my coded query as shown below:

BEFORE:
string query = @”SELECT a.VENDORID,
CAST(RTRIM(REPLACE(REPLACE(REPLACE(a.DOCNUMBR,’/’,”),’.’,”),’-‘,”)) as varchar(21)) as InvoiceNumber,
c.fileName,
c.BinaryBlob
FROM PM30200 a
INNER JOIN CO00102 b
ON CONVERT(VARCHAR(MAX), CONVERT(BINARY(4), Cast(a.NOTEINDX AS INTEGER)), 2) = RIGHT(RTRIM(b.BusObjKey), 8)
INNER JOIN coAttachmentItems c ON c.Attachment_ID = b.Attachment_ID
WHERE a.DOCNUMBR = ‘{0}'”;

AFTER:
string query = @”SELECT a.VENDORID,
CAST(RTRIM(REPLACE(REPLACE(REPLACE(a.DOCNUMBR,’/’,”),’.’,”),’-‘,”)) as varchar(21)) as InvoiceNumber,
c.fileName,
c.BinaryBlob
FROM PM30200 a
INNER JOIN CO00102 b
ON a.VCHRNMBR = RIGHT(RTRIM(b.BusObjKey), 8)
INNER JOIN coAttachmentItems c ON c.Attachment_ID = b.Attachment_ID
WHERE a.DOCNUMBR = ‘{0}'”;

——————————
Steven Mayhue
Lead Developer/DBA
My Florida Regional MLS
Altamonte Springs FL
——————————
——————————————-

[Mahmood M. AlSaadi]

?It makes sense, I will test this case in specific and keep you posted in case of any further findings that might be important.

Best Regards,

——————————
Mahmood M. AlSaadi – MVP
Principal Dynamics ERP Consultant
SIROCo LLC – (844) 708-0008
McKinney TX
m.saadi@sirocollc.com
http://www.sirocotech.com
——————————
——————————————-

[Steven Mayhue]

This was originally from an application I wrote to allow  check signers to pull up the original invoice without needing to log into GP.  The original query I got online and had the match as SUBSTRING(BusObjKey, 16,8) against the hex conversion of the NOTEINDEX.  This works fine for NOTES attachments.  It was only when we began using workflows for AP approvals that we discovered this difference.  You could not use the NOTES attachments in the approval emails, only the ribbon attachments.  In the workflows GP is apparently using the Voucher number directly as a match for the last 8 characters on the BusObjKey.  Why this changed doesn’t make a bit of sense to me?  AT least it gives us something to do with all that spare time we find ourselves with, or not.

I have seen many of your posts and believe this might be a good one for you to publish to the community regarding attachments and the workflow model within GP since most will find the tried and true query that is not valid in this scenario.

Steve

——————————
Steven Mayhue
Lead Developer/DBA
My Florida Regional MLS
Altamonte Springs FL
——————————
——————————————-