Skip to end of metadata
Go to start of metadata

(Back to E-PU Purchasing AP Module SQL Posted to Wiki Listing - E-PU Purchasing AP Module SQL Posted to Wiki  )

TYPE: Frequently Requested SQL

AUTHOR NAME: Neetu Dhami

PURPOSE AND BACKGROUND:  This SQL was created in 2007A SP 01PL 08. Few Clients require to implement validation/approval process when invoice value is more than order value. You can achieve this with transaction notification script and approval based on query script.Create one user defined filed on marketing document header level as Approval with valida values Y/N. You might be able to use this script "as is" or add more data fields to this SQL, depending upon your own requirements.

Copy and Paste this SQL:

--E-PU Validation and approval when Invoice Value is more than order Ver 1 ND 26 Oct,2011  

--DESCRIPTION:  In a typical Client scenario, we need Validation/Approval to check if invoice value is more than order. The following is a sample which can be used  as a basic outline for purchase transactions  and modified per your requirements.

--Version 1 Neetu Dhami 26th Oct ,2011

Add below script in transaction notification procedure.

IF@object_type='18'and @transaction_type='A'




DECLARE @ItemCode Nvarchar(100)

Declare @LineValue nvarchar(max)

SET @OPCHDocEntry = CAST(@list_of_cols_val_tab_del asint)

Select @OPRDocEntry = cast(T1.BaseEntry asint) from PCH1 T1 where DocEntry=@OPCHDocEntry

IF EXISTS(Select T1.ItemCode ,T1.LineNum From OPCH T0 Inner join PCH1 T1 on T0.DocEntry =T1.DocEntry and T1.BaseType ='22'Left Outer Join POR1 t2 on t2.DocEntry =t1.BaseEntry WHERE ISNULL(T0.U_approval,'')='N'and T1.BaseEntry =@OPRDocEntry and T1.ItemCode =t2.ItemCode GROUP BY T1.ItemCode ,t1.LineNum ,T2.Currency ,T1.currency,T2.TotalFrgn ,T2.Quantity ,t2.Price Having (CASE when t1.Currency ='GBP'then sum(T1.Quantity*t1.Price )elsesum(t1.TotalFrgn) end )>(((CASE when t2.Currency ='GBP'then t2.Quantity*t2.Priceelset2.TotalFrgn end )))


SELECT @error=101, @error_message ='Purchase Invoice Approval Required



For approval base on query:Save below query in query manager and set approval based on Query

Select 'True' From OPCH where $[OPCH.U_Approval]='Y'

  • No labels