A/P Down payment invoice Problem

[former_member541807]

Hi all,

I have the ff. scenario

1. Create PO

2. Create A/P DP invoice(20%) against this PO

3. Create outgoing payment against A/P DP invoice of 20%

4. Create A/P DP invoice(30%) against this PO

5. Create outgoing payment against A/P DP invoice of 30%

6. Create A/P DP invoice(60%) against this PO

7. Create outgoing payment against A/P DP invoice of 60%

8. Create a regular A/P invoice against the 3 DP

Problem 1. We have exceeded our payment against the PO w/c in this case is 110%, but SBO allowed to post these Down payments. How can we prevent overpayment base on a specific PO?

Problem 2. Since we have overpayment what we should do on 10% overpayment?

Please help.

Fidel

[Former Member · Accepted solution]

Hi Fidel,

You could :

1. Cancel the 60% outgoing payment (subsequently, your A/P Down Payment document status will open again)

2. Create A/P Credit Memo based on A/P Down Payment 60%

3. Create A/P Down Payment with correct percentage / 50%

4. Create Outgoing Payment against 50% A/P Down Payment

As for your intention to block down payment for certain PO, you can achieve this using stored procedure notification transaction.

Best Regards,

Hendry Wijaya

[former_member541807 · Accepted solution]

hi Hendry,

thanks for the response.

yes, i am thing of SP_TransactionNotification to solve this issue. pls help me to do the script.

to your second response, we may just contact the vendor to treat our over payment as advance payment for our future purchases. but i really need to implement the SP_TransactionNotification so that this case can be prevented on the near future.

thanks

Fidel

[Former Member · Accepted solution]

Hi Fidel !

Check this SP in test system

IF (@object_type in ('204'))
BEGIN
 if (@transaction_type in ('A','U')) 
 BEGIN
 IF (Select Top 1
(Select  Sum(DpmPrcnt)  from ODPO D00, DPO1 D11
Where
D00.DocEntry = D11.DocEntry and D1.BaseEntry = D11.BaseEntry )
 from ODPO D0, DPO1 D1
Where
D0.DocEntry = D1.DocEntry and
D0.DocEntry = @list_of_cols_val_tab_del) <= 100
BEGIN
  set @error = 0
  
  
 END
 ELSE
 BEGIN
    set @error_message='More than 100 %'
    set @error=1   
 END
END END

[Former Member · Accepted solution]

Hi Fidel,

Sorry for the late post, quite a busy day for me today.

edit - start

Errrrr, line 12 actually should be IF @iBaseType (less-than-symbol)(greater-than-symbol) 22

Why this forum remove those 2 symbols whenever I write them? Anyway, you should understand it, I mean "IF @iBaseType is not equat to 22" in SQL script. Just curious why SDN automatically remove whenever those 2 symbols adjacent to each other, weird...

And the same thing apply to line 25, should be AND TargetType (less-than-symbol)(greater-than-symbol) 19

edit - end

IF @object_type='204'AND @transaction_type='A' -- 204 is object type for A/P DP Invoice
  BEGIN
    DECLARE @iBaseType AS INT			-- To contain base document type of A/P DP Invoice
    DECLARE @lDpmPrcnt AS DECIMAL(18,2) -- To contain sum of DP% related to the based PO
	
	SELECT @iBaseType=BaseType
	FROM ODPO T0
	INNER JOIN DPO1 T1 ON T0.DocEntry=T1.DocEntry
		WHERE T1.DocEntry=@list_of_cols_val_tab_del
	
	IF @iBaseType<>22		-- Make sure in order to create DP, a PO is required (PO object type is 22)
	  BEGIN
		SET @error=-1
		SET @error_message='PO required in order to create purchase down payment'
	  END
	ELSE
	  BEGIN
		SELECT @lDpmPrcnt=SUM(DpmPrcnt)
		FROM ODPO T0
		INNER JOIN DPO1 T1 ON T0.DocEntry=T1.DocEntry
			WHERE BaseType=22
			AND BaseEntry=(SELECT BaseEntry FROM DPO1 WHERE DocEntry=@list_of_cols_val_tab_del)
			-- Line above is to select all DP related to base PO
			AND TargetType<>19
			-- Line above is to make sure DP that already reversed using A/P Credit Memo is excluded
			AND LineNum=0
			-- Make sure to add line above due to facts that DP% located in document header
			-- and since we join ODPO with DPO1,
			-- if line above doesn't exist, the sum of DP% will duplicated accordingly to number of line items in document
		
		IF @lDpmPrcnt>100
		  BEGIN
			SET @error=-1
			SET @error_message='Down payment percentage already exceeded 100% of PO'
		  END
	  END
  END

Inform me if you have any inquiry or problem regarding this SP.

Best Regards,

Hendry Wijaya

Edited by: Hendry Wijaya on Sep 8, 2011 5:42 PM

[former_member541807 · Accepted solution]

hi,

@ Thaga Raj

thank you very mcuh. this meets my requirement.

@ Hendry

thanks a lot for spending time making the SP Sript. this also works fine and meet my requirement.

problem solved.

Thank you very much.

Fidel

[Former Member]

Dear All,

I am very new in SAP B1 and at learning stage. I was searching for the problem and I found that same problem with solution is available here.

My problem is as below:

1.     Create PO

2.     Create A/P DP invoice(20%) against this PO

3.     Create outgoing payment against A/P DP invoice of 20%

4.     Create A/P DP invoice(30%) against this PO

5.     Create outgoing payment against A/P DP invoice of 30%

6.     Create A/P DP invoice(60%) against this PO

7.     Create outgoing payment against A/P DP invoice of 60%

8.     Create a regular A/P invoice against the 3 DP

How can we prevent over payment (DMP > 100%) base on a specific PO?

I got the Sql script but I don't know how to run. Please guide my.

Thanks to all for your support in advance.

Regards

Ashutosh

[kothandaraman_nagarajan]

Hi,

Welcome to SAP B1 forum. As per forum rule, you have create new discussion for above query.

Thanks & Regards,

Nagarajan

[Former Member]

Hello Mr, Nagarajan,

I am very new in SAP Business One and looking for the solution step wise step.

Please help and support.

Regards,

Ashutosh

[kothandaraman_nagarajan]

Hi,

Sure we will help to fix issues. But we need to follow forum rule. For creating new discussion, please refer below document:

http://scn.sap.com/docs/DOC-46003

Thanks & Regards,

Nagarajan

[Former Member]

Modified ver script of Thaga Raj

If @object_type='204' and (@transaction_type in('A','U'))

BEGIN

If (Select Top 1

isnull((SELECT     SUM(T0.DocTotal)

FROM         dbo.ODPO AS T0

WHERE     (T0.DocEntry IN

                          (SELECT DISTINCT T1.DocEntry

                            FROM          dbo.DPO1 AS T1

                            WHERE      (T1.BaseEntry = D1.BaseEntry))) and T0.CANCELED='N'),0)

from ODPO D0, DPO1 D1

Where

D0.DocEntry = D1.DocEntry and

D0.DocEntry = @list_of_cols_val_tab_del) <= (SELECT     TOP (1) dbo.OPOR.DocTotal

FROM         dbo.OPOR INNER JOIN

                      dbo.DPO1 ON dbo.OPOR.DocEntry = dbo.DPO1.BaseEntry

WHERE     (dbo.DPO1.DocEntry = @list_of_cols_val_tab_del))

BEGIN

  set @error = 0

END

ELSE

BEGIN

    set @error_message='Dear User you are posting A/P Down Payment more then 100% of its PO Total Value'

    set @error=1  

END

End

[Former Member]

Hi Fidel,

Sorry, I might misunderstood your question (problem #2) here. Did you actually already paid the supplier with amount of 60%?

If that is the case, your finance department should contact your supplier and ask them whether the 10% overpayment should be treated as advanced payment for future purchase or maybe they could refund the 10% amount to your company. Usually, it is the previous one. If that is the case, you need to reclassify from A/P to purchase advanced payment account using A/P Credit Memo service type.

Best Regards,

Hendry Wijaya