Stored Procedure

Using the Doc Number as Base Number for POs Generated from a Sales Document

The following stored procedure will cause the system to use a sales order’s Doc number as the base PO number for purchase orders generated from the sales document:

GO

/****** Object: StoredProcedure [dbo].[spcpGetPONextNumber] Script Date: 06/16/2011 12:35:02 ******/

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

create procedure [dbo].[spcpGetPONextNumber]

@sales_doc_type as varchar(25),

@sales_doc_num as varchar(20),

@line_num as decimal AS

declare @newPoNum as varchar(25),@num as int, @newDocNum as varchar(25) set nocount on

set @newDocNum = 'PO'+substring(@sales_doc_num,2,(len(@sales_doc_num)-1)) select identity(int, 1, 1) as n, LNITMSEQ into #tmp

from SOP10200 as sd (nolock) join spvSalesDocType as sdt on sdt.SOPTYPE=sd.SOPTYPE

where sdt.Sales_Doc_Type=@sales_doc_type and sd.SOPNUMBE=@sales_doc_num and sd.CMPNTSEQ=0

set @newPoNum = @newDocNum + '-' + cast((select n from #tmp where LNITMSEQ=@line_num) as varchar(25))

set @newPoNum = left(@newPoNum, 17) set nocount off

if ( (select count(*) from POP10100 where PONUMBER=@newPoNum) > 0) select '' as PONUMBER

else

select @newPoNum as PONUMBER