Need Sp query for purchase order

[Former Member]

Dear Experts,

I want one SP Query.

When i making purchase order. If i am adding a item in PO and price ill come automatically according to vendor wise price. But i don't want edit price.

If i edit price Document should not to be add and error message need to display.

This is my SP query.It is not working on above condition

if @object_type=22 and  @transaction_type='u'

begin

declare @price int

set @price=(select price from ospp where ItemCode=@list_of_cols_val_tab_del )

begin

if exists(select opor.DocNum from OPOR  INNER JOIN POR1  ON

opor.[DocEntry] = por1.[DocEntry] where por1.Price<>@price and por1.ItemCode=@list_of_cols_val_tab_del )

begin

set @error = -1

set @error_message = 'price cannot to be edit'

end

end

end

Thanks & Regards

ram

[Former Member · Accepted solution]

Try this

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

BEGIN

If Exists (select r.docentry from opor r inner join por1 r1 on r.docentry=r1.docentry inner join ospp p on p.ItemCode =r1.ItemCode and p.CardCode =r.CardCode where p.Price <>r1.Price and r.DocEntry = @list_of_cols_val_tab_del  )

BEGIN

Select @error = 22,

@error_message = 'Price cannot to be edit'

End

End

[Former Member · Accepted solution]

Hi Bharathiraja,

Thanks for Quick reply.

The querry is not working and document is adding.

Regards

ram

[kothandaraman_nagarajan]

Hi,

Please advice whether vendor wise price is special price or normal  price list.

Thanks.

[MukeshSingh]

Hi Ram Teja,

Try below SPTN

-- 1: PO: Unit price can't be more than define price list

IF @transaction_type IN ('A','U') AND @Object_type = '22'

Begin

   if Exists (select t0.DocEntry from OPOR t0, POR1 t1, OSPP t2 where t0.DocEntry=t1.DocEntry and t0.CardCode=t2.CardCode

  and t0.DocEntry=@list_of_cols_val_tab_del and t1.itemcode<>'S0001' and ((t1.ItemCode=t2.ItemCode and  t1.Price<t2.Price) or (t1.ItemCode<>t2.ItemCode)))

  begin

   select @error=1, @error_message='SBO: AR01-AR: Rent can not be less than defined price for the Customer in price list.'

  end

End

Regards,

Mukesh Singh

[Former Member]

Hi experts,

Experts please tell me the solution.

Regards

Ram

[Former Member]

Try this

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

BEGIN

If Exists (select r.docentry from opor r inner join por1 r1 on r.docentry=r1.docentry inner join ospp p on p.ItemCode =r1.ItemCode and p.CardCode =r.CardCode where p.Price <>r1.PriceBefDi and r.DocEntry = @list_of_cols_val_tab_del  )

BEGIN

Select @error = 22,

@error_message = 'Price cannot to be edit'

End

End