Create Invoices for multiple shipment receipt numbers per purchase order

(0) Share

Report

Posted on by Jyotshna Reddy

210

Hello,

I want to know how to create invoices for Mulple Shipment Receipt Numbers. I did for the purchase orders which had 1 but not sure how to do for the ones which have multiple.

I have automatically created invoices for single shipment using econnect using taPopEnterMatchInvLine,taPopEnterMatchInvHdr. I thought whaich have multiples I need to use this taPopEnterMatchInvToShpMultiLine but dont know how to exactly use that.

Please provide me sample code. It will be very helpful to me as I am new to Dynamics GP.

Thanks,

Jyotshna

*This post is locked for comments

All responses (15)

Answers (1)

soma 24,406 on at

Like (0)

Report

RE: Create Invoices for multiple shipment receipt numbers per purchase order

Good to heard that for your problem got resolved.

Mark this as a verified, because this will helps to some others who having the same case.
Verified answer

Jyotshna Reddy 210 on at

Like (0)

Report

RE: Create Invoices for multiple shipment receipt numbers per purchase order

Good Morning,

I got it worked . Problem was with the data, QTYMATCH column should be 0 in order for me to create shipment receipt number. Once I fixed that, above code worked.

Thanks a bunch for all the help.

Thanks,

Jyotshna
Jyotshna Reddy 210 on at

Like (0)

Report

RE: Create Invoices for multiple shipment receipt numbers per purchase order

Hi,

I did try few more ways and one way works but it does not put matching shipment receipt number which I need. If I put that MultiLineShip proc, it gves me the same error. Below is the code which works with out having matching shipment receipt number which I need though. Thought this may help you.

Code: I have commented MultiLineShip proc lines in below code.

ALTER proc [dbo].[uspPerceptive_CreatePOInvoice_Test2]

@ErrorMsg VARCHAR(500) = NULL output

As

-- Select * from dynamics..taErrorCode where ErrorCode in (233)

-- Exec uspPerceptive_CreatePOInvoice_Test2

Set nocount on

Declare @SBACompany varchar(10),

@SBACompanyProc varchar(10),

@VendorId varchar(15),

@PONumber varchar(25),

@ProcName varchar(500),

@VendorName varchar(64),

@ErrOut varchar(1000),

@ErrState int,

@DocDate datetime,

@Amount decimal(19,2),

@ErrDesc varchar(500),

@GLAccount varchar(25),

@GLAccountNumber varchar(25),

@BatchName varchar(25),

@DocNum varchar(25),

@CURNCYID varchar(15),

@Description varchar(30),

@SQL NVARCHAR(777),

@server_name VARCHAR(100),

@PopEnterMatchInvHdrProcName varchar(50),

@PopEnterMatchInvLineProcName varchar(50),

@PopDistributionProcName varchar(50),

@PopEnterMatchInvToShipMultiLineProcName varchar(50),

@FreightAmount money,

@MiscChargeAmount money,

@GSTAmount money,

@PSTAmount money,

@HSTAmount money,

@TaxAmount money,

@POLineNumber int,

@POItemNumber varchar(50),

@POItemDesc varchar(101),

@LineQty int,

@LineUOM varchar(10),

@LineUnitPrice money,

@LineExtendedAmount money,

@POPRCTNM varchar(50),

@POPRCTSHPRCTNM varchar(50),

@RowNum Int

SELECT

@SBACompany = SBACompany,

@server_name = Case When SBACompany = 'SBANS' then 'GPDEV6'

When SBACompany NOT IN ('SBANS','CATOW') then ''

When SBACompany = 'CATOW' then 'FL1GPTEST2' END

FROM [FL1VPERCDB1QA].[PerceptiveImport].[dbo].[AP_PO] with (nolock)

Where PerceptiveNum = 'p05'

--Run the following scripts against the company database to determine the next receipt number.

Set @SQL = ('SELECT @POPRCTNM = POPRCTNM FROM ' + @server_name +'.'+ rtrim(@SBACompany) + '.dbo.POP40100')

exec sp_executesql @SQL, N'@POPRCTNM nchar(100) output', @POPRCTNM output

Select @PopEnterMatchInvHdrProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopEnterMatchInvHdr'

Select @PopEnterMatchInvLineProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopEnterMatchInvLine'

Select @PopDistributionProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopDistribution'

Select @PopEnterMatchInvToShipMultiLineProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopEnterMatchInvToShpMultiLine'

BEGIN TRANSACTION

Declare item cursor READ_ONLY for

SELECT PO.[SBACompany]

,[VendorId]

,PO.[PONumber]

,[CurrencyId]

,[POLineNumber]

,[POItemNumber]

,[POItemDesc]

,[LineQty]

,[LineUOM]

,[LineUnitPrice]

,[LineExtendedAmount]

FROM [FL1VPERCDB1QA].[PerceptiveImport].[dbo].[AP_PO] PO with (nolock)

Left JOIN [FL1VPERCDB1QA].[PerceptiveImport].[dbo].[AP_PO_Line] PL with (nolock) ON PO.SBACompany = PL.SBACompany and PO.PONumber = PL.PONumber

Where PO.PerceptiveNum = 'p05'

Order By PO.[SBACompany],PO.PONumber

Open item

Fetch item into

@SBACompany

,@VendorId

,@PONumber

,@CURNCYID

,@POLineNumber

,@POItemNumber

,@POItemDesc

,@LineQty

,@LineUOM

,@LineUnitPrice

,@LineExtendedAmount

WHILE @@fetch_status = 0

BEGIN

Print 'Begin Process'

--Run the following scripts against the company database to get Shipment Receipt Number

Set @SQL = ('Select @POPRCTSHPRCTNM = POPRCTNM From ' + rtrim(@SBACompany) + '.dbo.POP10500 Where PONUMBER =' + '''' + rtrim(@PONumber) + ''' and ITEMNMBR =' + '''' + rtrim(@POItemNumber) + '''')

exec sp_executesql @SQL, N'@POPRCTSHPRCTNM nchar(100) output', @POPRCTSHPRCTNM output

Select @POPRCTNM as ReceiptNumber,@POPRCTSHPRCTNM as ReceiptShipmentNumber,@POItemNumber as POItemNumber

--Print @SQL

exec @PopEnterMatchInvLineProcName

@I_vPOPRCTNM = @POPRCTNM

--,@I_vPOPMtchShpRcpt = @POPRCTSHPRCTNM

--,@I_vShipRCPTLNNM = 16384

,@I_vPONUMBER = @PONumber

,@I_vITEMNMBR = @POItemNumber

,@I_vVENDORID = @VendorId

,@I_vVNDITNUM = @POItemNumber

,@I_vVNDITDSC = @POItemDesc

--,@I_vUOFM = @LineUOM

,@I_vUNITCOST = @LineUnitPrice

,@I_vEXTDCOST = @LineExtendedAmount

--,@I_vAUTOCOST = 1

--,@I_vEXTDCOST = 0

,@I_vQTYINVCD = @LineQty

--,@I_vPOLNENUM = @POLineNumber

--,@I_vCURNCYID = @CURNCYID

,@I_vNONINVEN = 1

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> ''

Begin

print 'Error with Match Invoice Line....'

PRINT('Proc Params:')

--PRINT('PerceptiveNum:' +@RowNum)

PRINT('SBACorp:' +@SBACompany)

PRINT('I_vPOPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('I_vPONUMBER=' +COALESCE(CONVERT(VARCHAR(200),@PONumber),'NULL'))

PRINT('I_vITEMNMBR=' +COALESCE(CONVERT(VARCHAR(200),@POItemNumber),'NULL'))

PRINT('I_vVENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

PRINT('I_vVNDITNUM=' +COALESCE(CONVERT(VARCHAR(200),@POItemNumber),'NULL'))

PRINT('I_vVNDITDSC=' +COALESCE(CONVERT(VARCHAR(200),@POItemDesc),'NULL'))

PRINT('I_vUOFM=' +COALESCE(CONVERT(VARCHAR(200),@LineUOM),'NULL'))

PRINT('I_vUNITCOST=' +COALESCE(CONVERT(VARCHAR(200),@LineUnitPrice),'NULL'))

PRINT('I_vEXTDCOST=' +COALESCE(CONVERT(VARCHAR(200),@LineExtendedAmount),'NULL'))

PRINT('I_vPOLNENUM=' +COALESCE(CONVERT(VARCHAR(200),@POLineNumber),'NULL'))

PRINT('I_vCURNCYID=' +COALESCE(CONVERT(VARCHAR(200),@CURNCYID),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Match Invoice Line Sucessful....'

End

--exec @PopEnterMatchInvToShipMultiLineProcName

-- @I_vPOPRCTNM = @POPRCTNM

-- ,@I_vPOPMtchShpRcpt = @POPRCTSHPRCTNM

-- ,@I_vPONUMBER = @PONumber

-- ,@I_vQTYINVCD = @LineQty

-- ,@I_vITEMNMBR = @POItemNumber

-- ,@I_vShipRCPTLNNM = 16384

-- ,@I_vVENDORID = @VendorId

-- ,@I_vVNDITNUM = @POItemNumber

-- ,@I_vNONINVEN = 1

-- ,@O_iErrorState=@ErrState output

-- ,@oErrString=@ErrOut output

--If @ErrOut <> ''

--Begin

-- print 'Error with Match Invoice Multi Line....'

-- PRINT('Proc Params:')

-- --PRINT('PerceptiveNum:' +@RowNum)

-- PRINT('SBACorp:' +@SBACompany)

-- PRINT('I_vPOPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

-- PRINT('I_vPOPMtchShpRcpt=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTSHPRCTNM),'NULL'))

-- PRINT('I_vPONUMBER=' +COALESCE(CONVERT(VARCHAR(200),@PONumber),'NULL'))

-- PRINT('I_vQTYINVCD=' +COALESCE(CONVERT(VARCHAR(200),@LineQty),'NULL'))

-- PRINT('I_vITEMNMBR=' +COALESCE(CONVERT(VARCHAR(200),@POItemNumber),'NULL'))

-- PRINT('I_vVENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

-- PRINT('I_vVNDITNUM=' +COALESCE(CONVERT(VARCHAR(200),@POItemNumber),'NULL'))

-- print @ErrOut

-- rollback transaction

-- goto err

--End

--Else

--Begin

-- print 'Match Invoice Multi Line Sucessful....'

--End

Fetch item into

@SBACompany

,@VendorId

,@PONumber

,@CURNCYID

,@POLineNumber

,@POItemNumber

,@POItemDesc

,@LineQty

,@LineUOM

,@LineUnitPrice

,@LineExtendedAmount

--COMMIT TRANSACTION

--if @@trancount > 0 Or (@ErrState > 0)

-- ROLLBACK TRANSACTION

End

Close item

Deallocate item

Declare itemheader cursor READ_ONLY for

SELECT PO.[SBACompany]

,[DocNumber]

,[DocDate]

,[VendorId]

,PO.[DocAmount]

,PO.[PONumber]

,[FreightAmount]

,[MiscChargeAmount]

,[TaxAmount]

,[CurrencyId]

,[BatchId]

FROM [FL1VPERCDB1QA].[PerceptiveImport].[dbo].[AP_PO] PO with (nolock)

Where PO.PerceptiveNum = 'p05'

Order By PO.[SBACompany],PO.PONumber

Open itemheader

Fetch itemheader into

@SBACompany

,@DocNum

,@DocDate

,@VendorId

,@Amount

,@PONumber

,@FreightAmount

,@MiscChargeAmount

,@TaxAmount

,@CURNCYID

,@BatchName

WHILE @@fetch_status = 0

BEGIN

exec @PopEnterMatchInvHdrProcName

@I_vPOPRCTNM = @POPRCTNM

,@I_vVNDDOCNM = @DocNum

,@I_vReceiptdate = @DocDate

,@I_vBACHNUMB = @BatchName

,@I_vVENDORID = @VendorId

--,@I_vSUBTOTAL = @Amount

,@I_vSUBTOTAL = 0

,@I_vAUTOCOST = 1

,@I_vFRTAMNT = @FreightAmount

,@I_vMISCAMNT = @MiscChargeAmount

,@I_vTAXAMNT = @TaxAmount

,@I_vCURNCYID = @CURNCYID

,@I_vCreateDist = 0

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> ''

Begin

print 'Error with Match Invoice Header....'

PRINT('Proc Params:')

PRINT('SBACorp:' +@SBACompany)

PRINT('I_vPOPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('I_vVNDDOCNM=' +COALESCE(CONVERT(VARCHAR(200),@DocNum),'NULL'))

PRINT('I_vReceiptdate=' +COALESCE(CONVERT(VARCHAR(200),@DocDate),'NULL'))

PRINT('I_vBACHNUMB=' +COALESCE(CONVERT(VARCHAR(200),@BatchName),'NULL'))

PRINT('I_vVENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

PRINT('I_vSUBTOTAL=' +COALESCE(CONVERT(VARCHAR(200),@Amount),'NULL'))

PRINT('I_vFRTAMNT=' +COALESCE(CONVERT(VARCHAR(200),@FreightAmount),'NULL'))

PRINT('I_MISCAMNT=' +COALESCE(CONVERT(VARCHAR(200),@MiscChargeAmount),'NULL'))

PRINT('I_TAXAMNT=' +COALESCE(CONVERT(VARCHAR(200),@TaxAmount),'NULL'))

PRINT('I_vCURNCYID=' +COALESCE(CONVERT(VARCHAR(200),@CURNCYID),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Match Invoice Header Sucessful....'

End

Exec @PopDistributionProcName

@I_vPOPTYPE = 1

,@I_vPOPRCTNM = @POPRCTNM

,@I_vVENDORID=@VendorId

,@I_vDISTTYPE=9

,@I_vACTNUMST='2020-00000-00'

,@I_vCRDTAMNT=0

,@I_vDEBITAMT=@Amount

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> '' or @@Error<>0

Begin

print 'Error with DEBIT Distribution....'

PRINT('BATCH:' +@BatchName)

PRINT('SBACorp:' +@SBACompany)

PRINT('POPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('VENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

PRINT('DEBITAMT=' +COALESCE(CONVERT(VARCHAR(200),@Amount),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Debit Distribution Sucessful.'

End

Exec @PopDistributionProcName

@I_vPOPTYPE = 1

,@I_vPOPRCTNM = @POPRCTNM

,@I_vVENDORID=@VendorId

,@I_vDISTTYPE=7

,@I_vACTNUMST='2110-00000-00'

,@I_vCRDTAMNT=@Amount

,@I_vDEBITAMT=0

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> '' or @@Error<>0

Begin

print 'Error with Credit Distribution....'

PRINT('BATCH:' +@BatchName)

PRINT('SBACorp:' +@SBACompany)

PRINT('POPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('VENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

PRINT('CREDITAMT=' +COALESCE(CONVERT(VARCHAR(200),@Amount),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Credit Distribution Sucessful.'

End

Fetch itemheader into

@SBACompany

,@DocNum

,@DocDate

,@VendorId

,@Amount

,@PONumber

,@FreightAmount

,@MiscChargeAmount

,@TaxAmount

,@CURNCYID

,@BatchName

End

Close itemheader

Deallocate itemheader

COMMIT TRANSACTION

err:

IF (@ErrState > 0)

Begin

print 'Error: ' + rtrim(@ErrOut)

Set @ErrorMsg = (Select top 1 'Error#: '+isnull(rtrim(@ErrOut),'')+': '+errordesc

From dynamics..taerrorcode where errorcode=isnull(@ErrState,1))

Select @ErrorMsg

Close item

Deallocate item
Jyotshna Reddy 210 on at

Like (0)

Report

RE: Create Invoices for multiple shipment receipt numbers per purchase order

Sure. Thank You.
soma 24,406 on at

Like (0)

Report

RE: Create Invoices for multiple shipment receipt numbers per purchase order

Give me some times I will try it on myself and let you know the status.
Jyotshna Reddy 210 on at

Like (0)

Report

RE: Create Invoices for multiple shipment receipt numbers per purchase order

Thank you for the reply.

I tried creating manually in GP and it works fine there but not through econnect with above code.
soma 24,406 on at

Like (0)

Report

RE: Create Invoices for multiple shipment receipt numbers per purchase order

You have getting this error "Quantity To Invoice is greater then the amount remaining to match" because of the invoice quantity is greater than the original Purchase Order Remaining quantity.

Try to manually create a invoice with the same shipment numbers which is used for your SQL stored procedure.

Your reply is much appreciated.

Hope this helps!!!
Jyotshna Reddy 210 on at

Like (0)

Report

RE: Create Invoices for multiple shipment receipt numbers per purchase order

I already tried that. I was getting below error when using multiline.

Error#: 4882:   Quantity To Invoice is greater then the amount remaining to match

Here is my code:

USE

[GPReports]

GO

/****** Object: StoredProcedure [dbo].[uspPerceptive_CreatePOInvoice]    Script Date: 5/30/2014 1:10:10 PM ******/

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

　

ALTER

proc [dbo].[uspPerceptive_CreatePOInvoice_Test]

@ErrorMsg

VARCHAR(500) = NULL output

As

-- Select * from dynamics..taErrorCode where ErrorCode in (233)

-- Exec uspPerceptive_CreatePOInvoice_Test

Set

nocount on

Declare

@SBACompany     varchar(10),



@SBACompanyProc varchar(10),



@VendorId       varchar(15),

@PONumber varchar(25),



@ProcName       varchar(500),

@VendorName     varchar(64),



@ErrOut         varchar(1000),



@ErrState       int,



@DocDate datetime,

@Amount decimal(19,2),

@ErrDesc varchar(500),

@GLAccount varchar(25),

@GLAccountNumber varchar(25),

@BatchName varchar(25),

@DocNum varchar(25),

@CURNCYID varchar(15),

@Description varchar(30),

@SQL NVARCHAR(777),

@server_name VARCHAR(100),

@PopEnterMatchInvHdrProcName varchar(50),

@PopEnterMatchInvLineProcName varchar(50),

@PopDistributionProcName varchar(50),

@PopEnterMatchInvToShipMultiLineProcName varchar(50),

@FreightAmount money,

@MiscChargeAmount money,

@GSTAmount money,

@PSTAmount money,

@HSTAmount money,

@TaxAmount money,

@POLineNumber int,

@POItemNumber varchar(50),

@POItemDesc varchar(101),

@LineQty int,

@LineUOM varchar(10),

@LineUnitPrice money,

@LineExtendedAmount money,

@POPRCTNM varchar(50),

@POPRCTSHPRCTNM varchar(50),

@RowNum Int

Declare

item cursor READ_ONLY for

SELECT

PO.[SBACompany]



,[DocNumber]



--,[DocID]



,[DocDescription]



,[DocDate]



,[VendorId]



,PO.[DocAmount]



,PO.[PONumber]



,[FreightAmount]



,[MiscChargeAmount]



,[TaxAmount]



,[GSTAmount]



,[PSTAmount]



,[HSTAmount]



,[CurrencyId]



,[BatchId]



,[POLineNumber]



,[POItemNumber]



,[POItemDesc]



,[LineQty]



,[LineUOM]



,[LineUnitPrice]



,[LineExtendedAmount]



,[GLAccount]

,Case When PO.SBACompany = 'SBANS' then 'GPDEV6'

When PO.SBACompany NOT IN ('SBANS','CATOW') then ''

When PO.SBACompany = 'CATOW' then 'FL1GPTEST2' END As ServerName

,row_number() over(order by PL.PerceptiveNum) as RowNum

FROM

[FL1VPERCDB1QA].[PerceptiveImport].[dbo].[AP_PO] PO with (nolock)

Left

JOIN [FL1VPERCDB1QA].[PerceptiveImport].[dbo].[AP_PO_Line] PL with (nolock) ON PO.SBACompany = PL.SBACompany and PO.PONumber = PL.PONumber

Where

PO.PerceptiveNum = 'p06'

Order

By PO.[SBACompany],PO.PONumber

Open

item

Fetch

item into

@SBACompany

,

@DocNum

,

@Description

,

@DocDate

,

@VendorId

,

@Amount

,

@PONumber

,

@FreightAmount

,

@MiscChargeAmount

,

@TaxAmount

,

@GSTAmount

,

@PSTAmount

,

@HSTAmount

,

@CURNCYID

,

@BatchName

,

@POLineNumber

,

@POItemNumber

,

@POItemDesc

,

@LineQty

,

@LineUOM

,

@LineUnitPrice

,

@LineExtendedAmount

,

@GLAccountNumber

,

@server_name

,

@RowNum

　

--Run the following scripts against the company database to determine the next receipt number.

Set

@SQL = ('SELECT @POPRCTNM = POPRCTNM FROM ' + @server_name +'.'+ rtrim(@SBACompany) + '.dbo.POP40100')

exec

sp_executesql @SQL, N'@POPRCTNM nchar(100) output', @POPRCTNM output

WHILE

@@fetch_status = 0

BEGIN

Print 'Begin Process'

BEGIN TRANSACTION

Select @PopEnterMatchInvHdrProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopEnterMatchInvHdr'

Select @PopEnterMatchInvLineProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopEnterMatchInvLine'

Select @PopDistributionProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopDistribution'

Select @PopEnterMatchInvToShipMultiLineProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopEnterMatchInvToShpMultiLine'

--Run the following scripts against the company database to get Shipment Receipt Number

Set @SQL = ('Select @POPRCTSHPRCTNM = POPRCTNM From ' + rtrim(@SBACompany) + '.dbo.POP10500 Where PONUMBER =' + '''' + rtrim(@PONumber) + ''' and ITEMNMBR =' + '''' + rtrim(@POItemNumber) + '''')

exec sp_executesql @SQL, N'@POPRCTSHPRCTNM nchar(100) output', @POPRCTSHPRCTNM output

Select @POPRCTNM as ReceiptNumber,@POPRCTSHPRCTNM as ReceiptShipmentNumber,@POItemNumber as POItemNumber

--Print @SQL

exec @PopEnterMatchInvToShipMultiLineProcName

@I_vPOPRCTNM = @POPRCTNM

,@I_vPOPMtchShpRcpt = @POPRCTSHPRCTNM

,@I_vPONUMBER = @PONumber

,@I_vQTYINVCD = @LineQty

,@I_vITEMNMBR = @POItemNumber

,@I_vShipRCPTLNNM = 16384

,@I_vVENDORID = @VendorId

,@I_vVNDITNUM = @POItemNumber

,@I_vNONINVEN = 1

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> ''

Begin

print 'Error with Match Invoice Multi Line....'

PRINT('Proc Params:')

--PRINT('PerceptiveNum:' +@RowNum)

PRINT('SBACorp:' +@SBACompany)

PRINT('I_vPOPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('I_vPOPMtchShpRcpt=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTSHPRCTNM),'NULL'))

PRINT('I_vPONUMBER=' +COALESCE(CONVERT(VARCHAR(200),@PONumber),'NULL'))

PRINT('I_vQTYINVCD=' +COALESCE(CONVERT(VARCHAR(200),@LineQty),'NULL'))

PRINT('I_vITEMNMBR=' +COALESCE(CONVERT(VARCHAR(200),@POItemNumber),'NULL'))

PRINT('I_vVENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

PRINT('I_vVNDITNUM=' +COALESCE(CONVERT(VARCHAR(200),@POItemNumber),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Match Invoice Multi Line Sucessful....'

End

exec @PopEnterMatchInvHdrProcName

@I_vPOPRCTNM = @POPRCTNM

,@I_vVNDDOCNM = @DocNum

,@I_vReceiptdate = @DocDate

,@I_vBACHNUMB = @BatchName

,@I_vVENDORID = @VendorId

--,@I_vSUBTOTAL = @Amount

,@I_vSUBTOTAL = 0

,@I_vAUTOCOST = 1

,@I_vFRTAMNT = @FreightAmount

,@I_vMISCAMNT = @MiscChargeAmount

,@I_vTAXAMNT = @TaxAmount

,@I_vCURNCYID = @CURNCYID

,@I_vCreateDist = 0

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> ''

Begin

print 'Error with Match Invoice Header....'

PRINT('Proc Params:')

--PRINT('PerceptiveNum:' +@RowNum)

PRINT('SBACorp:' +@SBACompany)

PRINT('I_vPOPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('I_vVNDDOCNM=' +COALESCE(CONVERT(VARCHAR(200),@DocNum),'NULL'))

PRINT('I_vReceiptdate=' +COALESCE(CONVERT(VARCHAR(200),@DocDate),'NULL'))

PRINT('I_vBACHNUMB=' +COALESCE(CONVERT(VARCHAR(200),@BatchName),'NULL'))

PRINT('I_vVENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

PRINT('I_vSUBTOTAL=' +COALESCE(CONVERT(VARCHAR(200),@Amount),'NULL'))

PRINT('I_vFRTAMNT=' +COALESCE(CONVERT(VARCHAR(200),@FreightAmount),'NULL'))

PRINT('I_MISCAMNT=' +COALESCE(CONVERT(VARCHAR(200),@MiscChargeAmount),'NULL'))

PRINT('I_TAXAMNT=' +COALESCE(CONVERT(VARCHAR(200),@TaxAmount),'NULL'))

PRINT('I_vCURNCYID=' +COALESCE(CONVERT(VARCHAR(200),@CURNCYID),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Match Invoice Header Sucessful....'

End

Exec @PopDistributionProcName

@I_vPOPTYPE = 1

,@I_vPOPRCTNM = @POPRCTNM

,@I_vVENDORID=@VendorId

,@I_vDISTTYPE=9

--,@I_vACTINDX=12

,@I_vACTNUMST='2020-00000-00'

,@I_vCRDTAMNT=0

,@I_vDEBITAMT=@Amount

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> '' or @@Error<>0

Begin

print 'Error with DEBIT Distribution....'

--PRINT('RowID:' +CONVERT(VARCHAR(200),@gl_row_ID))

PRINT('BATCH:' +@BatchName)

PRINT('SBACorp:' +@SBACompany)

PRINT('POPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('VENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

--PRINT('GLAccount=' +COALESCE(CONVERT(VARCHAR(200),@GLAccountNumber),'NULL'))

PRINT('DEBITAMT=' +COALESCE(CONVERT(VARCHAR(200),@Amount),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Debit Distribution Sucessful.'

End

Exec @PopDistributionProcName

@I_vPOPTYPE = 1

,@I_vPOPRCTNM = @POPRCTNM

,@I_vVENDORID=@VendorId

,@I_vDISTTYPE=7

--,@I_vACTINDX=13

,@I_vACTNUMST='2110-00000-00'

,@I_vCRDTAMNT=@Amount

,@I_vDEBITAMT=0

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> '' or @@Error<>0

Begin

print 'Error with Credit Distribution....'

--PRINT('RowID:' +CONVERT(VARCHAR(200),@gl_row_ID))

PRINT('BATCH:' +@BatchName)

PRINT('SBACorp:' +@SBACompany)

PRINT('POPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('VENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

--PRINT('GLAccount=' +COALESCE(CONVERT(VARCHAR(200),@GLAccount),'NULL'))

PRINT('CREDITAMT=' +COALESCE(CONVERT(VARCHAR(200),@Amount),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Credit Distribution Sucessful.'

End

Fetch

item into

@SBACompany

,

@DocNum

,

@Description

,

@DocDate

,

@VendorId

,

@Amount

,

@PONumber

,

@FreightAmount

,

@MiscChargeAmount

,

@TaxAmount

,

@GSTAmount

,

@PSTAmount

,

@HSTAmount

,

@CURNCYID

,

@BatchName

,

@POLineNumber

,

@POItemNumber

,

@POItemDesc

,

@LineQty

,

@LineUOM

,

@LineUnitPrice

,

@LineExtendedAmount

,

@GLAccountNumber

,

@server_name

,

@RowNum

COMMIT

TRANSACTION

if

@@trancount > 0 Or (@ErrState > 0)

ROLLBACK TRANSACTION

End

err:

IF

(@ErrState > 0)



Begin



print 'Error: ' + rtrim(@ErrOut)

--Set @ErrorMsg = @ErrDesc

Set @ErrorMsg = (Select top 1 'Error#: '+isnull(rtrim(@ErrOut),'')+':   '+errordesc

From dynamics..taerrorcode where errorcode=isnull(@ErrState,1))

Select @ErrorMsg

End



Close item



Deallocate item
soma 24,406 on at

Like (0)

Report

RE: Create Invoices for multiple shipment receipt numbers per purchase order

Can you try to use the Stored Procedure PopEnterMatchInvToShipMultiLineProcName instead of PopEnterMatchInvLineProcName to pass multiple lines of shipments?

Have a look on the below link for providing parameter values for the PopEnterMatchInvToShipMultiLineProcName .

msdn.microsoft.com/.../ff623944.aspx

Hope this helps!!!
Jyotshna Reddy 210 on at

Like (0)

Report

RE: Create Invoices for multiple shipment receipt numbers per purchase order

This is my code: Please take a look and let me know what should I change to make it work. If I run this code, I am getting error "Receipt Number Already Exists". I know why I am getting that error since everytime it is adding line item, it is creating Receipt Number which it should not since Receipt Number is unique but how can I avoid this, Receipt number is required when inserting line item.

USE [GPReports]

GO

/****** Object: StoredProcedure [dbo].[uspPerceptive_CreatePOInvoice] Script Date: 5/30/2014 1:10:10 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE proc [dbo].[uspPerceptive_CreatePOInvoice_Test]

@ErrorMsg VARCHAR(500) = NULL output

As

-- Select * from dynamics..taErrorCode where ErrorCode in (233)

-- Exec uspPerceptive_CreatePOInvoice

Set nocount on

Declare @SBACompany varchar(10),

@SBACompanyProc varchar(10),

@VendorId varchar(15),

@PONumber varchar(25),

@ProcName varchar(500),

@VendorName varchar(64),

@ErrOut varchar(1000),

@ErrState int,

@DocDate datetime,

@Amount decimal(19,2),

@ErrDesc varchar(500),

@GLAccount varchar(25),

@GLAccountNumber varchar(25),

@BatchName varchar(25),

@DocNum varchar(25),

@CURNCYID varchar(15),

@Description varchar(30),

@SQL NVARCHAR(777),

@server_name VARCHAR(100),

@PopEnterMatchInvHdrProcName varchar(50),

@PopEnterMatchInvLineProcName varchar(50),

@PopDistributionProcName varchar(50),

@PopEnterMatchInvToShipMultiLineProcName varchar(50),

@FreightAmount money,

@MiscChargeAmount money,

@GSTAmount money,

@PSTAmount money,

@HSTAmount money,

@TaxAmount money,

@POLineNumber int,

@POItemNumber varchar(50),

@POItemDesc varchar(101),

@LineQty int,

@LineUOM varchar(10),

@LineUnitPrice money,

@LineExtendedAmount money,

@POPRCTNM varchar(50),

@POPRCTSHPRCTNM varchar(50),

@RowNum Int

Declare item cursor READ_ONLY for

SELECT PO.[SBACompany]

,[DocNumber]

--,[DocID]

,[DocDescription]

,[DocDate]

,[VendorId]

,PO.[DocAmount]

,PO.[PONumber]

,[FreightAmount]

,[MiscChargeAmount]

,[TaxAmount]

,[GSTAmount]

,[PSTAmount]

,[HSTAmount]

,[CurrencyId]

,[BatchId]

,[POLineNumber]

,[POItemNumber]

,[POItemDesc]

,[LineQty]

,[LineUOM]

,[LineUnitPrice]

,[LineExtendedAmount]

,[GLAccount]

,Case When PO.SBACompany = 'SBANS' then 'GPDEV6'

When PO.SBACompany NOT IN ('SBANS','CATOW') then ''

When PO.SBACompany = 'CATOW' then 'FL1GPTEST2' END As ServerName

,row_number() over(order by PL.PerceptiveNum) as RowNum

FROM [FL1VPERCDB1QA].[PerceptiveImport].[dbo].[AP_PO] PO with (nolock)

Left JOIN [FL1VPERCDB1QA].[PerceptiveImport].[dbo].[AP_PO_Line] PL with (nolock) ON PO.SBACompany = PL.SBACompany and PO.PONumber = PL.PONumber

Where PO.PerceptiveNum = 'p05'

Order By PO.[SBACompany],PO.PONumber

Open item

Fetch item into

@SBACompany

,@DocNum

,@Description

,@DocDate

,@VendorId

,@Amount

,@PONumber

,@FreightAmount

,@MiscChargeAmount

,@TaxAmount

,@GSTAmount

,@PSTAmount

,@HSTAmount

,@CURNCYID

,@BatchName

,@POLineNumber

,@POItemNumber

,@POItemDesc

,@LineQty

,@LineUOM

,@LineUnitPrice

,@LineExtendedAmount

,@GLAccountNumber

,@server_name

,@RowNum

--Run the following scripts against the company database to determine the next receipt number.

Set @SQL = ('SELECT @POPRCTNM = POPRCTNM FROM ' + @server_name +'.'+ rtrim(@SBACompany) + '.dbo.POP40100')

exec sp_executesql @SQL, N'@POPRCTNM nchar(100) output', @POPRCTNM output

WHILE @@fetch_status = 0

BEGIN

Print 'Begin Process'

BEGIN TRANSACTION

Select @PopEnterMatchInvHdrProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopEnterMatchInvHdr'

Select @PopEnterMatchInvLineProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopEnterMatchInvLine'

Select @PopDistributionProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopDistribution'

Select @PopEnterMatchInvToShipMultiLineProcName = @server_name+'.'+rtrim(@SBACompany)+'.dbo.taPopEnterMatchInvToShpMultiLine'

--Run the following scripts against the company database to get Shipment Receipt Number

Set @SQL = ('Select @POPRCTSHPRCTNM = POPRCTNM From ' + rtrim(@SBACompany) + '.dbo.POP10500 Where PONUMBER =' + '''' + rtrim(@PONumber) + ''' and ITEMNMBR =' + '''' + rtrim(@POItemNumber) + '''')

exec sp_executesql @SQL, N'@POPRCTSHPRCTNM nchar(100) output', @POPRCTSHPRCTNM output

Select @POPRCTNM as ReceiptNumber,@POPRCTSHPRCTNM as ReceiptShipmentNumber,@POItemNumber as POItemNumber

--Print @SQL

exec @PopEnterMatchInvLineProcName

@I_vPOPRCTNM = @POPRCTNM

,@I_vPOPMtchShpRcpt = @POPRCTSHPRCTNM

,@I_vShipRCPTLNNM = 16384

,@I_vPONUMBER = @PONumber

,@I_vITEMNMBR = @POItemNumber

,@I_vVENDORID = @VendorId

,@I_vVNDITNUM = @POItemNumber

,@I_vVNDITDSC = @POItemDesc

--,@I_vUOFM = @LineUOM

,@I_vUNITCOST = @LineUnitPrice

,@I_vEXTDCOST = @LineExtendedAmount

--,@I_vAUTOCOST = 1

--,@I_vEXTDCOST = 0

,@I_vQTYINVCD = @LineQty

--,@I_vPOLNENUM = @POLineNumber

--,@I_vCURNCYID = @CURNCYID

,@I_vNONINVEN = 1

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> ''

Begin

print 'Error with Match Invoice Line....'

PRINT('Proc Params:')

--PRINT('PerceptiveNum:' +@RowNum)

PRINT('SBACorp:' +@SBACompany)

PRINT('I_vPOPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('I_vPONUMBER=' +COALESCE(CONVERT(VARCHAR(200),@PONumber),'NULL'))

PRINT('I_vITEMNMBR=' +COALESCE(CONVERT(VARCHAR(200),@POItemNumber),'NULL'))

PRINT('I_vVENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

PRINT('I_vVNDITNUM=' +COALESCE(CONVERT(VARCHAR(200),@POItemNumber),'NULL'))

PRINT('I_vVNDITDSC=' +COALESCE(CONVERT(VARCHAR(200),@POItemDesc),'NULL'))

PRINT('I_vUOFM=' +COALESCE(CONVERT(VARCHAR(200),@LineUOM),'NULL'))

PRINT('I_vUNITCOST=' +COALESCE(CONVERT(VARCHAR(200),@LineUnitPrice),'NULL'))

PRINT('I_vEXTDCOST=' +COALESCE(CONVERT(VARCHAR(200),@LineExtendedAmount),'NULL'))

PRINT('I_vPOLNENUM=' +COALESCE(CONVERT(VARCHAR(200),@POLineNumber),'NULL'))

PRINT('I_vCURNCYID=' +COALESCE(CONVERT(VARCHAR(200),@CURNCYID),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Match Invoice Line Sucessful....'

End

exec @PopEnterMatchInvHdrProcName

@I_vPOPRCTNM = @POPRCTNM

,@I_vVNDDOCNM = @DocNum

,@I_vReceiptdate = @DocDate

,@I_vBACHNUMB = @BatchName

,@I_vVENDORID = @VendorId

--,@I_vSUBTOTAL = @Amount

,@I_vSUBTOTAL = 0

,@I_vAUTOCOST = 1

,@I_vFRTAMNT = @FreightAmount

,@I_vMISCAMNT = @MiscChargeAmount

,@I_vTAXAMNT = @TaxAmount

,@I_vCURNCYID = @CURNCYID

,@I_vCreateDist = 0

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> ''

Begin

print 'Error with Match Invoice Header....'

PRINT('Proc Params:')

--PRINT('PerceptiveNum:' +@RowNum)

PRINT('SBACorp:' +@SBACompany)

PRINT('I_vPOPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('I_vVNDDOCNM=' +COALESCE(CONVERT(VARCHAR(200),@DocNum),'NULL'))

PRINT('I_vReceiptdate=' +COALESCE(CONVERT(VARCHAR(200),@DocDate),'NULL'))

PRINT('I_vBACHNUMB=' +COALESCE(CONVERT(VARCHAR(200),@BatchName),'NULL'))

PRINT('I_vVENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

PRINT('I_vSUBTOTAL=' +COALESCE(CONVERT(VARCHAR(200),@Amount),'NULL'))

PRINT('I_vFRTAMNT=' +COALESCE(CONVERT(VARCHAR(200),@FreightAmount),'NULL'))

PRINT('I_MISCAMNT=' +COALESCE(CONVERT(VARCHAR(200),@MiscChargeAmount),'NULL'))

PRINT('I_TAXAMNT=' +COALESCE(CONVERT(VARCHAR(200),@TaxAmount),'NULL'))

PRINT('I_vCURNCYID=' +COALESCE(CONVERT(VARCHAR(200),@CURNCYID),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Match Invoice Header Sucessful....'

End

Exec @PopDistributionProcName

@I_vPOPTYPE = 1

,@I_vPOPRCTNM = @POPRCTNM

,@I_vVENDORID=@VendorId

,@I_vDISTTYPE=9

--,@I_vACTINDX=12

,@I_vACTNUMST='2020-00000-00'

,@I_vCRDTAMNT=0

,@I_vDEBITAMT=@Amount

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> '' or @@Error<>0

Begin

print 'Error with DEBIT Distribution....'

--PRINT('RowID:' +CONVERT(VARCHAR(200),@gl_row_ID))

PRINT('BATCH:' +@BatchName)

PRINT('SBACorp:' +@SBACompany)

PRINT('POPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('VENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

--PRINT('GLAccount=' +COALESCE(CONVERT(VARCHAR(200),@GLAccountNumber),'NULL'))

PRINT('DEBITAMT=' +COALESCE(CONVERT(VARCHAR(200),@Amount),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Debit Distribution Sucessful.'

End

Exec @PopDistributionProcName

@I_vPOPTYPE = 1

,@I_vPOPRCTNM = @POPRCTNM

,@I_vVENDORID=@VendorId

,@I_vDISTTYPE=7

--,@I_vACTINDX=13

,@I_vACTNUMST='2110-00000-00'

,@I_vCRDTAMNT=@Amount

,@I_vDEBITAMT=0

,@O_iErrorState=@ErrState output

,@oErrString=@ErrOut output

If @ErrOut <> '' or @@Error<>0

Begin

print 'Error with Credit Distribution....'

--PRINT('RowID:' +CONVERT(VARCHAR(200),@gl_row_ID))

PRINT('BATCH:' +@BatchName)

PRINT('SBACorp:' +@SBACompany)

PRINT('POPRCTNM=' +COALESCE(CONVERT(VARCHAR(200),@POPRCTNM),'NULL'))

PRINT('VENDORID=' +COALESCE(CONVERT(VARCHAR(200),@VendorId),'NULL'))

--PRINT('GLAccount=' +COALESCE(CONVERT(VARCHAR(200),@GLAccount),'NULL'))

PRINT('CREDITAMT=' +COALESCE(CONVERT(VARCHAR(200),@Amount),'NULL'))

print @ErrOut

rollback transaction

goto err

End

Else

Begin

print 'Credit Distribution Sucessful.'

End

Fetch item into

@SBACompany

,@DocNum

,@Description

,@DocDate

,@VendorId

,@Amount

,@PONumber

,@FreightAmount

,@MiscChargeAmount

,@TaxAmount

,@GSTAmount

,@PSTAmount

,@HSTAmount

,@CURNCYID

,@BatchName

,@POLineNumber

,@POItemNumber

,@POItemDesc

,@LineQty

,@LineUOM

,@LineUnitPrice

,@LineExtendedAmount

,@GLAccountNumber

,@server_name

,@RowNum

COMMIT TRANSACTION

if @@trancount > 0 Or (@ErrState > 0)

ROLLBACK TRANSACTION

End

err:

IF (@ErrState > 0)

Begin

print 'Error: ' + rtrim(@ErrOut)

--Set @ErrorMsg = @ErrDesc

Set @ErrorMsg = (Select top 1 'Error#: '+isnull(rtrim(@ErrOut),'')+': '+errordesc

From dynamics..taerrorcode where errorcode=isnull(@ErrState,1))

Select @ErrorMsg

End

Close item

Deallocate item