Skip to Content
0
Sep 05, 2013 at 06:42 AM

Need a help in query

25 Views

Hello Experts,

select oscl.callid [Call Id#], OSCL.CREATEDATE [CALL DATE] , OSCL.custmrName [Service Customer],

oscl.itemName [Service Product] , OSCL.SUBJECT [FAULT] , o1.DocDate , NNM1.SeriesName + STR(o1.docnum) , OSCL.U_Loc , OSCS.Name

from OSCL inner join OSCS on OSCS.statusID = OSCL.status and oscs.name = 'Ready for delivery'

left join (select SCL4.srcvcallid , scl4.docabs , SCL4.object From scl4) s1 on oscl.callid = s1.srcvcallid

left join (select oinv.docentry , OINV.docdate , OINV.docnum , oinv.series from OINV) o1 on s1.DocAbs = o1.DocEntry

and s1.object=13

inner join NNM1 on NNM1.Series = o1.Series

group by oscl.callid , OSCL.CREATEDATE , OSCL.custmrName ,

oscl.itemName , OSCL.SUBJECT , o1.DocDate , NNM1.SeriesName + STR(o1.docnum) , OSCL.U_Loc , OSCS.Name

This is giving me a list of service calls whose status is "ready for delivery' with invoice punched in the system.

But there are calls with status as 'ready for delivery' but no invoice.

Please help me in listing all ready for delivery calls with and without invoicing.

Thanking You

Pradnya