on 04-05-2009 7:54 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
86 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.