Approval Query - ORDR & OSCN

[Former Member]

Hi

I'm trying to create an approval procedure that checks sales order

based on data in customer(card) - Item connection : OSCN

I should get 2 demands

1: total qnty for item in order is not higher than MAX_QNTY - UDF in OSCN

2: the minimum ship date is less than lastDate - UDF in OSCN

my query is:

select distinct

'True'

from

(

select

ordr.DocEntry,

ordr.CardCode,

rdr1.ItemCode,

sum(rdr1.OrderedQty) as total_Ordered,

MIN(rdr1.ShipDate) as min_date

from

ORDR ordr

inner join

RDR1 rdr1

on

ordr.DocEntry  = rdr1.DocEntry

group by

ordr.DocEntry,

ordr.CardCode,

rdr1.ItemCode

) o

left join

OSCN oscn

on

o.CardCode = oscn.CardCode and

o.ItemCode =oscn.ItemCode

where

o.DocEntry = $[ORDR.DOCENTRY] AND

o.total_Ordered < oscn.U_MaxQty and

o.min_date < oscn.U_LastDate

any suggestions?

[kothandaraman_nagarajan]

Hi,

Any problem or error with above query?

Thanks & Regards,

Nagarajan

[Rafaee_Mustafa]

check with this. If Ordered Qty get higher than Max Qty and MinDate less than LastDate

select distinct

'True'

from

(select

ordr.DocEntry,ordr.CardCode,rdr1.ItemCode,

sum(rdr1.OrderedQty) as total_Ordered,

MIN(rdr1.ShipDate) as min_date

from ORDR ordr

inner join RDR1 rdr1

on ordr.DocEntry  = rdr1.DocEntry

group by

ordr.DocEntry, ordr.CardCode, rdr1.ItemCode

) o

left join

OSCN oscn

on o.CardCode = oscn.CardCode and

o.ItemCode =oscn.ItemCode

where

o.DocEntry = $[ORDR.DOCENTRY] AND

o.total_Ordered > oscn.U_MaxQty and

o.min_date < oscn.U_LastDate