Requisition Attachments Transferring to Purchase Orders

July 27 | By Ryan McBee

We have had an increase in requests from customers wanting to implement the Requisitions Module within Purchasing. One of the limitations in Requisitions Entry is the ability to have attachments flow to the Purchase Order. Our customers want to have the ability to email the Requisition Attachments back to the vendor. In many cases, these are quotes for the product on the Requisition.

To over come this obstacle, we wrote the stored procedure below.

It can be scheduled, called manually or added to the Requisition Entry window and called upon creation of the Purchase Order.

You will now see the same attachment on the Purchase Order. It can be used to email back to the Vendor as a reference.



USE [TWO]
GO

/****** Object: StoredProcedure [dbo].[AddIn_ReqPOAttachment] Script Date: 7/27/2017 9:07:57 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
EXEC [AddIn_ReqPOAttachment];
*/
/**********************************************************************
* SP Name: [AddIn_ReqPOAttachment]
* Author: BA
* Date: 07/10/2016
* DB(s): GP Companies
*
* Purpose: Create record attachments that originated on Req to the PO
*
*
* The return value will indicate SUCCESS if the value is 0, and
* FAILURE if @ 0.
*
**********************************************************************
* Date - Changed By
* Changes
**********************************************************************
*
*/

CREATE PROC [dbo].[AddIn_ReqPOAttachment] --@ReqNo VARCHAR(50), @PONo VARCHAR(50)
AS

BEGIN TRY
DECLARE
@ReqNo VARCHAR(50),
@PONo VARCHAR(50),
@AttachmentKeyReq VARCHAR(MAX),
@AttachmentKeyPO VARCHAR(MAX),
@ReqIdx NUMERIC(19,5),
@POIdx NUMERIC(19,5),
@attachmentid char(37);

DECLARE attachment_cursor CURSOR FOR
SELECT DISTINCT
--SOPNUMBE AS ReqNo,
--CONVERT(VARCHAR(MAX), CONVERT(BINARY(4), CAST(req.Requisition_Note_Index AS INTEGER)), 2) ReqIdx,
--p.PONUMBER,
--CONVERT(VARCHAR(MAX), CONVERT(BINARY(4), CAST(p.PONOTIDS_1 AS INTEGER)), 2) POIdx,
req.Requisition_Note_Index AS ReqIdx,
p.PONOTIDS_1 AS POIdx,
b.BusObjKey,
'0\PM\Purchase Order\' + rtrim(p.ponumber) as 'NewKey' ,
p.PONUMBER,
b.Attachment_ID
--REPLACE(b.BusObjKey, CONVERT(VARCHAR(MAX), CONVERT(BINARY(4), CAST(req.Requisition_Note_Index AS INTEGER)), 2), CONVERT(VARCHAR(MAX), CONVERT(BINARY(4), CAST(p.PONOTIDS_1 AS INTEGER)), 2) ) NewKey
FROM
POP10100 p
INNER JOIN SOP60100 link ON p.PONUMBER = link.PONUMBER
INNER JOIN POP30200 req ON link.SOPNUMBE = req.POPRequisitionNumber
INNER JOIN CO00102 b ON req.POPRequisitionNumber =
substring(b.BusObjKey, charindex('REQ0', b.BusObjKey), len(b.BusObjKey))

--INNER JOIN CO00102 b
---ON CONVERT(VARCHAR(MAX), CONVERT(BINARY(4), CAST(req.Requisition_Note_Index AS INTEGER)), 2) = SUBSTRING(b.BusObjKey, 16, 8)

WHERE --(p.PONUMBER = @PONo OR @PONo IS NULL) AND
'0\PM\Purchase Order\' + rtrim(p.ponumber) NOT IN (SELECT BusObjKey FROM CO00102) ;

OPEN attachment_cursor;
FETCH NEXT FROM attachment_cursor
INTO @ReqIdx, @POIdx, @AttachmentKeyReq, @AttachmentKeyPO, @pono, @attachmentid

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO [dbo].[CO00102]
([BusObjKey]
,[Attachment_ID]
,[CRUSRID]
,[CREATDDT]
,[CREATETIME]
,[HISTRX]
,[AllowAttachmentFlow]
,[DELETE1]
,[AllowAttachmentEmail]
,[AttachmentOrigin]
,[WorkflowStepInstanceID])
SELECT
@AttachmentKeyPO AS [BusObjKey]
,[Attachment_ID]
,[CRUSRID]
,[CREATDDT]
,[CREATETIME]
,[HISTRX]
,[AllowAttachmentFlow]
,[DELETE1]
,[AllowAttachmentEmail]
,[AttachmentOrigin]
,[WorkflowStepInstanceID]
FROM
CO00102
WHERE
BusObjKey = @AttachmentKeyReq
AND @AttachmentKeyPO NOT IN (SELECT BusObjKey FROM CO00102);

INSERT INTO [dbo].[CO00105]
([BusObjKey]
,[docnumbr]
,[filename]
,[STRTDSCR]
,[Attachment_ID]
,[CREATDDT]
,[CREATETIME]
,[FileType]
,[Size]
,[EmailAllowAttachments]
,[ORD]
,[DELETE1])
SELECT
@AttachmentKeyPO
,@PONo
,[filename]
,[STRTDSCR]
,[Attachment_ID]
,[CREATDDT]
,[CREATETIME]
,[FileType]
,[Size]
,1
,1
,[DELETE1]
FROM
CO00105
WHERE
BusObjKey = @AttachmentKeyReq
AND @AttachmentKeyPO NOT IN (SELECT BusObjKey FROM CO00105);

update co00101 set ODESCTN = 'PO' where attachment_id = @attachmentid;

INSERT INTO [dbo].[SY03900]
([NOTEINDX]
,[DATE1]
,[TIME1]
,[TXTFIELD])
SELECT
@POIdx AS [NOTEINDX]
,[DATE1]
,[TIME1]
,[TXTFIELD]
FROM
[SY03900]
WHERE
[NOTEINDX] = @ReqIdx AND @POIdx NOT IN (SELECT [NOTEINDX] FROM SY03900)

FETCH NEXT FROM attachment_cursor
INTO @ReqIdx, @POIdx, @AttachmentKeyReq, @AttachmentKeyPO, @PONo, @attachmentid;
END

CLOSE attachment_cursor;
DEALLOCATE attachment_cursor;

END TRY

/* Capture errors, select from common sp and return to application.*/
BEGIN CATCH
RETURN -100
END CATCH

RETURN 0;

GRANT ALL ON [AddIn_ReqPOAttachment] TO DYNGRP;

GO