Stored procedure at Purchase order..

[Former Member]

hi.

i need a stored procedure at purchase order..

what i need is purchase quotation , posting date should be equal to purchase order posting date other wise..

error will show...

the below one i tried but it's not working..

--IF (@object_type = '22' )and (@transaction_type IN ('A','U'))

--BEGIN

--If exists(SELECT T3.[Docentry]  FROM POR1 T0 left join OPQT T1 on  T0.[BaseDocNum]  =  T1.[DocNum]

-- INNER JOIN PQT1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN OPOR T3 ON T0.DocEntry = T3.DocEntry WHERE DATEDIFF(DD,T3.DocDate,T1.DocDate ) <> 0   and T3.DocEntry =

--@list_of_cols_val_tab_del )

--  Begin

--  set @error =105

--  set @error_message = 'Please Check the Date'

--  End

--  end

[Former Member · Accepted solution]

Try:

IF (@object_type = '22' )and (@transaction_type IN ('A','U'))

BEGIN

If exists(SELECT T3.[Docentry]  FROM POR1 T0 left join OPQT T1 on  T0.[BaseEntry]  =  T1.[DocEntry]

INNER JOIN PQT1 T2 ON T1.DocEntry = T2.DocEntry

INNER JOIN OPOR T3 ON T0.DocEntry = T3.DocEntry

WHERE T3.DocEntry = @list_of_cols_val_tab_del and DateDiff(dd,T3.DocDate,T1.DocDate) != 0)

  Begin

  set @error =22

  set @error_message = 'PO date must be the same as Quote date'

End

END

Thanks,

Gordon

[Former Member · Accepted solution]

hi., Sir Thank u very much for your posting..

[kothandaraman_nagarajan · Accepted solution]

Dear Gordon,

Just want to clarify:

Same code which is proposed by this document owner also blocking transaction. Also I have tested and working fine me. I know correct relationship between tables in doc entry.

Why same code is not blocking owner system?

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

The link using BaseDocNum is wrong. It is only coincidence working for you because you docnum and docentry are the same.

[kothandaraman_nagarajan · Accepted solution]

Dear Gordon,

Thanks for explanation.

Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Why your query is not working? Its working for me.

You need to enable "allow future posting date" under general settings.Other you will get an error message.

Thanks & Regards,

Nagarajan

[Former Member]

hi. Stored procedure is not working...

it is allowing diff posting date at purchase order.. that purchase quotation..

You need to enable "allow future posting date" under general settings.Other you will get an error message.

I opened general setting but i have not seen any option allow future posting date

under which tab i can found above option...

[kothandaraman_nagarajan]

Hi,

1. Did you select correct database to test? Please refer screen shot of blocking PO if PQ posting date not equal to PO posting date.

2. For allow future posting, refer below screen shot:

Thanks & Regards,

Nagarajan

[Former Member]

hi. Thanks for your reply..

it is all ready  checked it..

My requirement is i want to block it...

ok

purchase quotation posting date should be equal to purchase order posting date..

[Former Member]

i am working on same data base no doubt....sql and b1...

[kothandaraman_nagarajan]

Hi,

Check another query posted by Shachar Saado and let me know the result.

Thanks & Regards,

Nagarajan

[Former Member]

hi. i checked that one only

it's not working..

[kothandaraman_nagarajan]

Hi,

Run below query in query generator and let me know the result:

SELECT T3.[Docentry]  FROM POR1 T0 left join OPQT T1 on  T0.[BaseDocNum]  =  T1.[DocNum]

INNER JOIN PQT1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN OPOR T3 ON T0.DocEntry = T3.DocEntry WHERE DATEDIFF(DD,T3.DocDate,T1.DocDate ) <> 0

Thanks & Regards,

Nagarajan

[Former Member]

it is showing three records docentry lik

26

27

28

[kothandaraman_nagarajan]

Hi,

1. That means you have three PO with different posting date with PQ posting date.

2. Any error message while running above code in SQL server?

[Former Member]

Any error message while running above code in SQL server?

No Error....

[kothandaraman_nagarajan]

Hi,

Can I check it through team viewer? If yes send ID and password.

Thanks & Regards,

Nagarajan

[Former Member]

Sorry nagarajan i can not give u..

We dont have any teamviewer , ammy, join me

Restricted....

not possible..

is there any way..

give me any suggestion....

[kothandaraman_nagarajan]

Hi,

OK no problem.

Please post screen shot result of SQL server here.

[Former Member]

hi.

This is the i had run the query in sql server.

[kothandaraman_nagarajan]

Hi,

I want stored procedure result as same like above.

[Former Member]

IF (@object_type = '22' )and (@transaction_type IN ('A','U'))

BEGIN

If exists(SELECT T3.[Docentry]  FROM POR1 T0 left join OPQT T1 on  T0.[BaseDocNum]  =  T1.[DocEntry]

INNER JOIN PQT1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN OPOR T3 ON T0.DocEntry = T3.DocEntry WHERE T3.DocDate<>T1.DocDate and T3.DocEntry =

@list_of_cols_val_tab_del )

Begin

set @error =105

set @error_message = 'Please Check the Date'

End

end

[kothandaraman_nagarajan]

Hi,

1. Restart SAP B1 and server

2. Run stored procedure again

3. Please post screen shot PQ and PO with posting date here.

Thanks & Regards,

Nagarajan

[Former Member]

ok Give me some time i will post it...

[Former Member]

hi.

plz have a look below images.

i started my pc ...

the above stored procedure only i was used no changes..

purchase quotation...posting date is 06/05/2014

purchase order date is 07/05/2014

but still i am able to add it.

but i want to restrict it..

Any information update me..

[kothandaraman_nagarajan]

Hi,

From Purchase order screen shot, found that you are using spilt purchase order. In this case, the above stored procedure will not work.

Try without split PO, then above code will work.

Thanks & Regards,

Nagarajan

[Former Member]

Sorry Split means what ....

[Former Member]

I think that is not a reason

again i disabled split purchase order. then

again i posted

purchase quotation  06052014 

purhcase order         07/05/5014

but i am able to add it...

Any reasons.

[kothandaraman_nagarajan]

Hi,

If you create same item with different ware house, system automatically creates two separate PO for each item.

In this case, the base reference number is not from PQ, its taken from same PO number. That's why, the above code is not blocking the transaction.

Try :

1. Create new PQ with single item

2. Raise PO based on above PQ

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Please try new transaction.

[Former Member]

ok point noted..

[kothandaraman_nagarajan]

Hi,

Have you tried with new transaction?

Thanks & Regards,

Nagarajan

[Former Member]

New Transaction mean what..

i know only this one...

i am checking whether the query is correct or not..

i dont have any other query..

but still checking..

[kothandaraman_nagarajan]

Hi,

Create any item with new purchase quotation (without split) and copy to purchase order.

Thanks & Regards,

Nagarajan

[Former Member]

Try this :

IF (@object_type = '22' )and (@transaction_type IN ('A','U'))

BEGIN

If exists(SELECT T3.[Docentry]  FROM POR1 T0 left join OPQT T1 on  T0.[BaseDocNum]  =  T1.[DocEntry]

INNER JOIN PQT1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN OPOR T3 ON T0.DocEntry = T3.DocEntry WHERE T3.DocDate<>T1.DocDate and T3.DocEntry =

@list_of_cols_val_tab_del )

  Begin

  set @error =105

  set @error_message = 'Please Check the Date'

  End

shachar

[Former Member]

k i will check it give me some time