Skip to Content
0
Former Member
Dec 09, 2009 at 08:07 AM

item and service invoices in same query

444 Views

Hi All,

i want item based and service based invoices in same query but in this query when user individuality select item or service it shows records how can i get both in same query

SELECT 
1 as sr,'Invoice' [Document Type],
t0.docentry,
T0.[CardCode] as 'BP Number', 
T0.[CardName] as 'BP name',
t0.numatcard as 'Vendor reference No.',
t0.u_loc as 'Location name' ,
t4.state ,--'Null')  as 'State' ,
T0.[DocNum] as 'Document Number', 
--T0.[DocType],
t0.docdate, 
ISNULL(T1.ItemCode,'Service Item') AS 'Item Code',
T1.[Dscription], 
T1.[Quantity], 
t3.name as 'MAG',
t1.linetotal as 'Net Amount',
t1.vatsum as 'Tax Amount',
(t1.linetotal + t1.vatsum) as 'Invoice Value'
FROM [dbo].[OINV]  T0 
INNER JOIN [dbo].[INV1]  T1 ON T0.DocEntry = T1.DocEntry 
left outer join owhs t4 on t1.whscode = t4.whscode
LEFT JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode 
LEFT JOIN [dbo].[@ITMG]  T3 ON T2.U_itmg = T3.Code
WHERE 
T0.[DocType] = '[%0]' and t0.cardcode between [%3] and [%4] and t0.docdate >= [%1]and t0.docdate <=[%2] 
union

SELECT 2,'Credit Memo' [Document Type],
t0.docentry, 
T0.[CardCode] as 'BP Number', 
T0.[CardName] as 'BP Name',
t0.numatcard as 'Vendor reference No.',
t0.u_loc as 'Location name' ,
t4.state ,--'Null')  as 'State' ,
T0.[DocNum] as 'Document Number', 
--T0.[DocType],
t0.docdate, 
ISNULL(T1.ItemCode,'Service Item') AS 'Item Code',
T1.[Dscription], 
-T1.[Quantity], 
t3.name as 'MAG',
-t1.linetotal as 'Net Amount',
-t1.vatsum as 'Tax Amount',
(t1.linetotal + t1.vatsum) as 'Invoice Value'
FROM [dbo].[ORIN]  T0 
INNER JOIN [dbo].[RIN1]  T1 ON T0.DocEntry = T1.DocEntry 
left outer join owhs t4 on t1.whscode = t4.whscode
LEFT JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode 
LEFT JOIN [dbo].[@ITMG]  T3 ON T2.U_itmg = T3.Code
WHERE 
T0.[DocType] = '[%0]' and t0.cardcode between [%3] and [%4] and t0.docdate >= [%1]and t0.docdate <=[%2]

pls help me

ketan..........