A/P Invoice due date user can't change

[MukeshSingh]

Hi Export,

When we making A/P Invoice due date coming automatic based on supplier payment term setup.

We required...

1) When we post A/P Invoice if user change due date than A/P Invoice goes for approval.

2) After adding the A/P Invoice if user update due date than system block through transaction notification.

Please suggest how to validate.

Thanks in advance

Mukesh Singh

[Former Member]

Hi,

Try this ....

To Block Update

IF @transaction_type IN ('U') AND @object_type = '18'and @error = 0

BEGIN

IF EXISTS (SELECT T0.DocNum FROM OPCH T0 INNER JOIN ocrd T1 on T1.CardCode=T0.CardCode

LEFT JOIN ADOC  T3 ON T3.ObjType=T0.ObjType AND T3.DocDueDate=T0.DocDueDate WHERE  T0.DOCENTRY = @list_of_cols_val_tab_del

AND T0.DocDueDate <> (select  T3.DocDueDate from ADOC T3

WHERE T3.ObjType='18' AND T3.UpdateDate=(SELECT MAX(B.UpdateDate) FROM ADOC T4 WHERE T4.ObjType=20 )

AND T3.LogInstanc=(SELECT MAX(T5.LogInstanc) FROM ADOC T5 WHERE  T5.ObjType=18 ) ))

BEGIN

SELECT @error = 56, @error_message = 'You are not allowed to update DocDueDate'

END

END

Regards

Bhanu

[MukeshSingh]

Hi Bhanu,

Sorry your TN is not working still we can update

[Former Member]

HI

TRY THIS

IF @transaction_type IN ('U') AND @object_type = '18'and @error = 0

BEGIN

IF EXISTS (SELECT T0.DocNum FROM OPCH T0 INNER JOIN OCRD T1 on T1.CardCode=T0.CardCode

LEFT JOIN ADOC  T2 ON T2.ObjType=T0.ObjType AND T2.DocDueDate=T0.DocDueDate WHERE  T0.DOCENTRY = @list_of_cols_val_tab_del

AND T0.DocDueDate <> (select  T2.DocDueDate from ADOC T2

WHERE T2.ObjType='18'AND T2.UpdateDate=(SELECT MAX(T3.UpdateDate) FROM ADOC T3 WHERE T3.ObjType=18 )

AND T2.LogInstanc=(SELECT MAX(T4.LogInstanc) FROM ADOC T4 WHERE  T4.ObjType=18 ) ))

BEGIN

SELECT @error = 8, @error_message = 'Can Not Update Doc Due Date'

END

END

Regards

Bhanu

[MukeshSingh]

Hi Bhanu,

Still error are some.

After adding if we update the due date system not blocking.

My requirement is simple

In A/P Invoice if user change due date only than system block and if user change other field than system not block .

Regards

Mukesh Singh

[MukeshSingh]

Hi all,

Approval Query if AP Invoice Due Date is not same as per Payment Term - system asking for approval every time even i not change due date.

Transaction Notification to Avoid Update of AP Invoice DocDueDate After Adding AP Invoice - we required if user update(change) due date after adding the document than system block if user change other field than system not block the entry.

[former_member184146]

Hi Mukesh ,

                    Have you checked my TN ?? to block user in case of updating due date after adding the document.

--Manish

[Former Member]

Hi Mukesh,

Can you show us here Payment Term window.

Need to check How Payment Term is configured.

Thanks'

--

--

Regards::::

Atul Chakraborty

[MukeshSingh]

Hi Manish,

I checked your TN but after adding if i update doc due date system not blocking.

Mukesh

[MukeshSingh]

[Former Member]

Hi Mukesh,

Please Create one UDF at Header Level for Due Date Approval and Place this below Query in UDF and make Auto refersh on DocDate.

SELECT T1.ExtraDays  FROM OPCH T0

INNER JOIN OCTG T1 ON T0.GroupNum = T1.GroupNum

Where $[OPCH.GroupNum] = T1.[GroupNum]

Then Please Place this below Query in Approval Template.

SELECT DISTINCT 'TRUE' FROM OPCH T0

Where DATEDIFF(DAY,$[OPCH.DocDate.Date],$[OPCH.DocDueDate.Date])  <> UDF

Please check above and let me know result.

Thanks'

--

--

Regards::::

Atul Chakraborty

[kothandaraman_nagarajan]

Hi,

Try this query for approval:

SELECT DISTINCT 'TRUE' FROM OPCH T0

INNER JOIN OCTG T1 ON T0.GroupNum = T1.GroupNum

Where DATEDIFF(DAY,$[$10.0.DATE],$[$12.0.DATE]) <> T1.ExtraDays

Thanks & Regards,

Nagarajan

[Former Member]

Hi Mukesh,

Please check below Approval Query if AP Invoice Due Date is not same as per Payment Term.

SELECT DISTINCT 'TRUE' FROM OPCH T0

INNER JOIN OCTG T1 ON T0.GroupNum = T1.GroupNum

Where DATEDIFF(DAY,$[OPCH.T0.DocDate],$[OPCH.T0.DocDueDate]) <> T1.ExtraDays

Please Check below Transaction Notification to Avoid Update of AP Invoice DocDueDate After Adding AP Invoice.

IF @transaction_type IN ('U') AND @object_type = '18'and @error = 0

BEGIN

IF EXISTS (SELECT T0.DocEntry FROM OPCH T0 INNER JOIN ADOC T1 on T1.ObjType = T0.ObjType  WHERE T0.DocDueDate <>  T1.DocDueDate AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SELECT @Error = -1, @error_message = 'You are not Authorized to Change Document Due Date'

END

END

Hope this helps

--

--

Regards::::

Atul Chakraborty

[former_member184146]

Hi,

     Use the below Query for approval

SELECT DISTINCT T0.DocNum FROM OPCH T0

LEFT JOIN ADOC  A ON A.ObjType=T0.ObjType  WHERE  T0.DocNum =$[OPCH.DOCNUM]

AND T0.DOCduedate <> (select  A.DOCduedate from ADOC A

WHERE A.ObjType='18'AND A.UpdateDate=(SELECT MAX(B.UpdateDate) FROM ADOC B WHERE B.ObjType=18 )

AND A.LogInstanc=(SELECT MAX(C.LogInstanc) FROM ADOC C WHERE  C.ObjType=18 ))

and to Block the Users using TN

IF @transaction_type IN ('U') AND @object_type = '18'and @error = 0

BEGIN

IF EXISTS (SELECT T0.DocNum FROM OPCH T0

LEFT JOIN ADOC  A ON A.ObjType=T0.ObjType  WHERE  T0.DOCENTRY = @list_of_cols_val_tab_del

AND T0.DOCduedate <> (select  A.DOCduedate from ADOC A

WHERE A.ObjType='18'AND A.UpdateDate=(SELECT MAX(B.UpdateDate) FROM ADOC B WHERE B.ObjType=18 )

AND A.LogInstanc=(SELECT MAX(C.LogInstanc) FROM ADOC C WHERE  C.ObjType=18 ) ))

BEGIN

SELECT @error = 8, @error_message = 'You are not allowed to update Due Date'

END

END

--Manish