A/R Minimum Price

[former_member264311]

Good Day Experts,

      It is possible that the minimum price of item in A/R Invoice will not below the price of item purchased in A/P invoice? It will not transact the A/R invoice if the price of item is below of purchased price in A/P invoice.

Regards Thanks.

[former_member212181 · Accepted solution]

Hi Romel,

This kind of validations are only effective for serial / Batch numbered items.

Otherwise,  we can't track actual Purchase price of AR Invoice item.

Another Option,

If you are using moving average price, then you can put a transaction notification validation or Approval procedure based on moving average item cost and sales Price.

Thanks

Unnikrishnan

[former_member264311 · Accepted solution]

How to Set Up that notification validation.

Thanks

[former_member212181 · Accepted solution]

Hi,

You need to execute one query in SQL.

If you don't have technical knowledge, you should take help of system admin / Service partner, while doing this.

and do this in test database first.

You can try below script for this

SQL > Database > Select database > Store procedure > Store procedure transaction notifications > Paste this script "Below add your code here"

---

If @object_type = '13' And @transaction_type in ('A')

Begin

If Exists(Select T0.DocEntry

  From OINV T0 Inner Join INV1 T1 On T0.DocEntry = T1.DocEntry

  Where T0.DocEntry = @list_of_cols_val_tab_del And T1.Price<T1.ItemCost)

Begin

  SET @error=@list_of_cols_val_tab_del--@object_type;

  SET @error_message = 'Sales Price is less than Itemcost'

End

---

Thanks

Unnikrishnan

[former_member264311 · Accepted solution]

Okay.

Thank you.

[former_member264311 · Accepted solution]

Hi there's no store procedure i saw in sql>database>database name>store procedure.

Thanks.

[former_member264311 · Accepted solution]

Hi Unnikrishnan,

This code will be notify if the minimum a/r transaction price was below Purchased price in A/P invoice?

[former_member212181 · Accepted solution]

Hi Romel,

Please try below query for approval procedure.

-- AR Invoice

Declare @LastPurprice numeric(19,6), @InvPrice  numeric(19,6)

Set @LastPurprice = (Select T0.LastPurPrc FROM OITM T0 WHERE T0.ItemCode =  $[$38.1.0])

Set @InvPrice  = $[$38.17.Number]

Select Distinct 'True' Where @LastPurprice > @InvPrice

Save this Query in Query manager,

and use the same in approval templates.

Thanks

Unnikrishnan

[former_member264311 · Accepted solution]

Hi Sir,

Why in the code i cant create A/P transaction?

"If Exists(Select T0.DocEntry

  From OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

  Where T0.DocEntry = @list_of_cols_val_tab_del And T1.Price< T2.[LastPurPrc] )

Begin

  SET @error=@list_of_cols_val_tab_del--@object_type;

  SET @error_message = 'Sales Price is less than Last Purchased Price'

End"

[former_member212181 · Accepted solution]

Hi Romel,

I don't understand.

Your issue solved?

Or you are facing new issue in AP Invoice, by having a TN validation in AR Invoice.

Please make it clear.

Thanks

Unnikrishnan

[former_member264311 · Accepted solution]

sorry. I thought i already solved my problem, but all transaction i will entry always appear an error " Sales Price is Less than Purchased Price" in this code.

If Exists(Select T0.DocEntry

  From OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

  Where T0.DocEntry = @list_of_cols_val_tab_del And T1.Price< T2.[LastPurPrc] )

Begin

  SET @error=@list_of_cols_val_tab_del--@object_type;

  SET @error_message = 'Sales Price is less than Last Purchased Price'

End

[former_member264311 · Accepted solution]

Hi i used the code below and it's okay now. is this correct?

If @object_type = '13' And @transaction_type in ('A')

If Exists(Select T0.DocEntry

  From OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

  Where T0.DocEntry = @list_of_cols_val_tab_del And T1.Price< T2.[LastPurPrc] )

Begin

  SET @error=@list_of_cols_val_tab_del--@object_type;

  SET @error_message = 'Sales Price is less than Last Purchased Price'

End

[former_member212181 · Accepted solution]

Hi Ramasamy,

Do you include Object Type in your query??

If no, Please use below query

------------------

If @object_type = '13' and @transaction_type in ( 'A')

BEGIN

     If Exists(Select T0.DocEntry

       From OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON      T1.ItemCode = T2.ItemCode

       Where T0.DocEntry = @list_of_cols_val_tab_del And T1.Price< T2.[LastPurPrc] )

          Begin

            SET @error=@list_of_cols_val_tab_del--@object_type;

            SET @error_message = 'Sales Price is less than Last Purchased Price'

          End

END

-----------

Thanks

Unnikrishnan

[former_member264311 · Accepted solution]

Hi sir,

how if i have many entry in A/R invoice and can i know what's the item is below purchased price if i encounter the error?

for example this is the error message " (item) is below purchased price".

Regards

[former_member212181 · Accepted solution]

HI Romel,

This TN looks correct.

In-order to identify which row & Item code is having issue in AR Invoice window, we need to add some more code in query.

Unfortunately I am not sound in TN Query and I don't have SQL environment also.

Thanks

Unnikrishnan

[former_member264311 · Accepted solution]

THANK YOU SO MUCH. BECAUSE OF YOU I GOT AN IDEA

[former_member264311 · Accepted solution]

Hi sir it possible that it will continue the transaction even below the purchased price that can be approve to the manager account?

[former_member212181 · Accepted solution]

Hi Romel,

It will block document, even if its approved.

But we can add one more condition in "Where" clause to skip approved document.

Field Name to identify authorization statu is WddStatus.

Please try below query

If @object_type = '13' And @transaction_type in ('A')

If Exists(Select T0.DocEntry

  From OINV T0

  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

  INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

  Where T0.DocEntry = @list_of_cols_val_tab_del

  and T0.WddStatus <>'P' -- to skip approved document from validation

  And T1.Price< T2.[LastPurPrc] )

Begin

  SET @error=@list_of_cols_val_tab_del--@object_type;

  SET @error_message = 'Sales Price is less than Last Purchased Price'

End

Thanks

Unnikrishnan

[former_member264311 · Accepted solution]

where can i see the authorization Status in A/R Invoice?

[former_member212181 · Accepted solution]

Hi,

You can see authorization status only if AR Invoice document is subject to approval process.

If AR Invoice is subject to approval Procedure, It will show on Window Name area

In SQL table, it will be displaying as OINV.WddStatus = P

Thanks

Unnikrishnan

[former_member264311 · Accepted solution]

i think in B1 v 8.82 pl12 there's no like that

Regards

[former_member264311 · Accepted solution]

HI SIR Unnikrishnan Balan , it possible that it will be prompt the user login like overide. and only the su...

[former_member212181 · Accepted solution]

Hi Romel,

The easy and right way of implementing your requirement is Approval process based on query.

Hope i give you the query for the same.

Thanks

Unnikrishnan