cancel
Showing results for 
Search instead for 
Did you mean: 

Query for approval procedure on credit limit with validity period

former_member209762
Participant
0 Kudos
788

Hi everyone !

I have the following issue.

I set a credit limit (OCRD.CreditLine field) for all customers with group code '4' (OCRD.GroupCode). I created two UDF at the header lever in BP form (OCRD table): OCRD.U_StartDate  and OCRD.U_EndDate to hold the start and end dates of this credit.

I want an approval procedure on A/R invoice which trigger each time the credit is exceed. To achieve this, I modify the stadard query of credit limit check an link it to an approval procedure (set active) but it does not trigger at all. What wrong?

DECLARE @Factor as numeric(1,0) 

SELECT @Factor = CASE (SELECT TOP 1 DispPosDeb FROM OADM)

WHEN 'N' THEN 1

ELSE -END

SELECT T0.CardCode, T0.CardName, T0.Balance, T0.CreditLine,(T0.CreditLine + @Factor*T0.Balance) "Deviation"

FROM OCRD T0 INNER JOIN OINV T1 ON T0.CardCode=T1.CardCode

WHERE (select T0.CreditLine + @Factor*T0.Balance)  < 0 AND T0.GroupCode='4'AND

T1.DocDate >=T0.U_StartDate AND T1.DocDate<=T0.U_EndDate

Thanks for your support

View Entire Topic
Former Member
0 Kudos

Hi,

Try:

SELECT Distinct 'true'

FROM OCRD T0 INNER JOIN OINV T1 ON T0.CardCode=T1.CardCode

WHERE T0.CreditLine < T0.Balance AND T0.GroupCode=4 AND

T1.DocDate >=T0.U_StartDate AND T1.DocDate<=T0.U_EndDate AND T1.DocNum = $[$8.0.0]

Thanks,

Gordon

former_member209762
Participant
0 Kudos

Hi Gordon, Kennedy

It try your query but the approval procedure.does not trigger.

One question: why did you include T1.DocNum in the query? And Why did you ignore $[$4.0.0] for the BP code in the query?

Thanks for your help.