DD_PMVendorDocument_SEL

Today I had an eConnect integration return this ugly error...

Sql procedure error codes returned:
Error Number = 4794  Stored Procedure taPopEnterMatchInvHdr  Error Description = Vendor Document numbers for Invoices must be unique
Node Identifier Parameters: taPopEnterMatchInvHdr                             
POPRCTNM = VCH00104170
Related Error Code Parameters for Node : taPopEnterMatchInvHdr                             
VENDORID = GILCON01      
VNDDOCNM = 12-421

So I wrote the stored procedure below to check for a duplicate Vendor/Vendor Doc combination before I send the document to eConnect.

If the VendorID/Vendor Doc combination exists a row(s) will be returned, otherwise no rows

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

 

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'DD_PMVendorDocument_SEL' 
)
   DROP PROCEDURE dbo.DD_PMVendorDocument_SEL
GO
  
CREATE PROCEDURE dbo.DD_PMVendorDocument_SEL
--  DD_PMVendorDocument_SEL 'GILCON01', '12-421A'
  
@VENDORID VARCHAR(15),
@VNDDOCNM VARCHAR(21)
  
AS
  
select 'PMKEYS' AS tablename, cntrlnum as VOUCHER from pm00400 where docnumbr = @VNDDOCNM AND VENDORID = @VENDORID
UNION ALL
select 'PM WORK' AS tablename, VCHNUMWK as VOUCHER from pm10000 where docnumbr = @VNDDOCNM AND VENDORID = @VENDORID
UNION ALL
select 'PM OPEN' AS tablename, VCHRNMBR as VOUCHER from pm20000 where docnumbr = @VNDDOCNM AND VENDORID = @VENDORID
UNION ALL
select 'PM HIST' AS tablename, VCHRNMBR as VOUCHER from pm30200 where docnumbr = @VNDDOCNM AND VENDORID = @VENDORID
UNION ALL
select 'POP WORK' AS tablename, POPRCTNM as VOUCHER from pop10300 where vnddocnm = @VNDDOCNM AND VENDORID = @VENDORID
UNION ALL
select 'POP HIST' AS tablename, POPRCTNM as VOUCHER from pop30300 where vnddocnm  = @VNDDOCNM AND VENDORID = @VENDORID
  
  
  
go 
  
grant exec on DD_PMVendorDocument_SEL to public

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences