on 11-21-2014 11:49 AM
Hi Experts,
My customer having Scenario is that they create sales Inquiry through Sales opportunity and in stages they Select Quotation then copy to sales order --->Delivery -->Return-->Invoice--->CR Memo.
I got the Query through SCN as bellow this works very well for process (SO to INV ) in that i want only addition of Sales Opportunity and Sales Quotation Document also.
SELECT T1.DocEntry as 'Link', T1.[DocNum] as 'SO No.', T1.[DocDate] as 'SO Date', T1.DocStatus as ' SO Status', T1.[CardName] as 'Customer Name', T9.SlpName, T10.firstName AS 'SO Owner', T8.FrgnName AS 'Part No.' ,
T0.[Dscription] as 'Part Name', T0.[Quantity] as 'SO Qty',T0.[Price] as 'Sales Price', T0.[OpenQty], T0.[OpenSum], T3.DocNum as 'Delivery Doc Num', T2.[Quantity] as 'Deliverd Quantity',
T12.DocNum AS 'Return No', T12.DocDate as 'Return Date', T11.Quantity as 'Retuen Qty' ,
T5.DocNum as 'Invoice No', T5.DocDate as 'Invoice Date', T5.DocStatus as 'Invoice Status' , T4.Quantity as 'Invoice Qty', T5.DocTotal,T5.PaidToDate as 'Applied Amt',
T7.DocNum as 'Credit Note No.', T7.DocDate as 'Credit Note date',T6.Quantity as 'Credit Note Qty'
FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
left outer join DLN1 T2 on T2.BaseEntry = T0.DocEntry and T2.BaseLine = T0.Linenum
left outer join ODLN T3 on T2.DocEntry = T3.DocEntry
left Outer join INV1 T4 on T4.BaseEntry = T3.DocEntry and T4.BaseLine = T2.Linenum and T4.BaseType = 15
OR (T4.Basetype=17 and T4.BaseEntry=T0.DocEntry and T4.BaseLine=T0.LineNum)
LEFT outer join RDN1 T11 on T11.BaseEntry = T2.DocEntry and T11.BaseLine = T2.LineNum
LEFT outer join ORDN T12 on T11.DocEntry = T12.DocEntry
left outer join OINV T5 on T5.DocEntry = T4.DocEntry
left Outer join RIN1 T6 on T6.BaseEntry = T5.DocEntry and T6.BaseLine = T4.Linenum
left outer join ORIN T7 on T6.DocEntry = T7.DocEntry
left outer join OITM T8 on T0.ItemCode = T8.ItemCode
left outer join OSLP T9 on T9.SlpCode = T1.SlpCode
left outer join OHEM T10 on T10.empID = T1.OwnerCode
WHERE T1.[DocDate] >=[%0] and T1.[DocDate] <=[%1]
Group by T1.DocEntry, T1.DocNum,T1.DocDate,T1.DocStatus,T1.CardName, T9.SlpName,T10.firstName,T8.FrgnName,T0.[Dscription] , T0.[Quantity] ,T0.[Price], T0.[OpenQty], T0.[OpenSum], T3.DocNum , T2.[Quantity] ,
T5.DocNum , T5.DocDate, T5.DocStatus , T4.Quantity, T5.DocTotal,T5.PaidToDate ,
T7.DocNum , T7.DocDate ,T6.Quantity,T12.DocNum,T12.DocDate,T11.Quantity
please help me for that
Thanks & Regards,
Navnath
Hi,
Try this query first before linking to above query and add required fied from quotation and opportunity.
OOPR T0 INNER JOIN OPR1 T1 ON T1.OpprId = T0.OpprId and T1.ObjType='23'
left join OQUT T2 on t2.DocEntry = T1.[DocID]
left JOIN QUT1 T3 ON T2.DocEntry = T3.DocEntry
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Navanath,
Unlike SO to Invoice, The relationship between sales opportunity and them are different. Sales Quote has similar relationship but may not be as tight as SO to invoice relation either.
You may change the 1st part of the query to Sales Quotation and SO to see.
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
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.