cancel
Showing results for 
Search instead for 
Did you mean: 

Monthly Sales Order query Doc Status problem

Former Member
0 Kudos

Hi Experts,

I am having an problem while making a total monthly sales order query in which I am not getting the document status which are Open - Printed. Also note that these document which are open printed are not having any target document.

SELECT T0.[DocStatus],T1.[TargetType],T0.[CANCELED],T0.[CardCode], T0.[CardName], T0.[NumAtCard], T5.[SeriesName], T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[DocTotal],T0.[Max1099]'Total Before Discount',T0.[TotalExpns],T4.[descript]'Territory',T2.[SlpName]'Employee Name'
,T0.[discsum]  FROM ORDR T0  ,RDR1 T1,OSLP T2,OCRD T3,OTER T4,NNM1 T5  where  T0.DocEntry = T1.DocEntry AND T0.SlpCode = T2.SlpCode AND T0.CardCode = T3.CardCode
AND T3.Territory = T4.territryID AND T0.Series = T5.Series and T0.[CANCELED]='N' and T1.[TargetType] is not null

My mission for the above query is to have Total Monthly Sales Orders with value exluding the cancelled and closed sales sales ( close here means - which are manually done by right click on sales order screen and selecting Close )

Please guied how to solve this issue because in the back end for Sales Order which have turned to deleivery are showing doc status as closed and the sales order which are manually closed are also showing doc status as closed. How to diffrentiate between them.

Waiting for a positve feedback.

Regards,

Rahul

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

For manually closed ones, did they close with partial delivery? If that is the case, the query need to be updated. Otherwise, it is just what you need.

Former Member
0 Kudos

Try this one:


SELECT DISTINCT T0.[DocStatus],T0.[CardCode], T0.[CardName], T0.[NumAtCard], T5.[SeriesName],
T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[DocTotal],T0.[Max1099]'Total Before Discount',
T0.[TotalExpns],T4.[descript]'Territory',T2.[SlpName]'Employee Name',T0.[discsum]
FROM DBO.ORDR T0
INNER JOIN DBO.RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN DBO.OSLP T2 ON T0.SlpCode = T2.SlpCode 
INNER JOIN DBO.OCRD T3 ON T0.CardCode = T3.CardCode
INNER JOIN DBO.OTER T4 ON T3.Territory = T4.territryID 
INNER JOIN DBO.NNM1 T5 ON T0.Series = T5.Series
where  T0.[CANCELED]='N' and T1.[TargetType] != -1

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

Your query seems to work but in it the sales orders are not showing which do not have any target document. See my requirement is that I should have Sales Order which is Opened or Which are closed ( made target document ) and it should exclude the Cancelled ones and manually closed ones.

Your query is not giving me the required information.

Please help in this regard.

Regards,

Rahul

Former Member
0 Kudos

Hi

try with left join .See if it meets your requirement .

Thank you

Bishal