Skip to Content
0
Former Member
Jul 07, 2011 at 03:56 AM

Serial No

15 Views

SELECT T0.docentry,

convert (varchar, T0.DocDate, 103),

T0.docnum,

T0.docdate,

T0.cardcode,

T0.cardname,

T1.itemcode,

T1.dscription as 'Item Name',

T1.Quantity,

T1.price,

T1.linetotal,

(SELECT max(T12.IntrSerial)

FROM SRI1 T11

Inner Join OSRI T12 On T11.SysSerial=T12.SysSerial AND T12.ItemCode=T11.ItemCode

INNER JOIN DLN1 T2 ON T2.DocEntry=T11.BaseEntry AND T2.ItemCode=T11.ItemCode

and T2.DocEntry = T10.DocEntry

and T10.DocEntry = T0.DocEntry

and T0.DocEntry = T1.DocEntry

and T11.BaseType = 15

and T1.BaseRef = T10.DocNum ) 'Serial Number',

isnull(vat.taxsum,0) as 'VAT Amount',

ISNULL (vat.taxrate,0) as 'VAT Rate',

(select max(T0.ChapterId) from OCHP T0 inner join OITM T4

on T0.AbsEntry = T4.ChapterID

and T1.ItemCode = T4.ItemCode) 'Chapter Id1',

(select max(T5.EccNo) from CRD7 T5 where

T5.CardCode = T0.CardCode) 'Ecc No',

isnull(cst.taxsum,0) as 'CST Amount',

ISNULL (cst.taxrate, 0) as 'CST Rate',

Excise.taxsum as 'Cenvat BED Amount',

Excise.TaxRate as 'Cenvat BED Tax Rate',

Cess.TaxSum as 'Cess Amount',

Cess.TaxRate as 'Cess Tax Amount',

Hcess.Taxsum as ' Hcess Amount',

Hcess.TaxRate as 'Hcess Tax Amount',

CVD.Taxsum as 'CVD Amount',

CVD.TaxRate as 'CVD Tax Rate',

BCD.Taxsum as 'Basic Custom Duty Amount',

BCD.TaxRate as 'Basic Custom Duty Tax Rate',

Bcuseducess.Taxsum as 'Basic Cus Edu Cess Amount',

Bcuseducess.TaxRate as 'Basic Cus Edu Cess Rate',

Bcushseeducess.Taxsum as 'Basic Cus HS Cess Amount',

Bcushseeducess.TaxRate as 'Basic Cus HS Cess Amount Rate',

AdddutyN.Taxsum as 'Additional Duty Amount',

AdddutyN.TaxRate as 'Additional Duty Tax Rate',

T0.discsum as 'Discount',

T0.TotalExpns as 'Freight Charges',

(select T8.ExpnsName from OEXD T8 inner join INV3 T9 on

T8.ExpnsCode = T9.ExpnsCode and T9.DocEntry = T0.DocEntry) 'Freight Charge Name',

T0.doctotal

from OINV T0

inner join INV1 T1 on

T0.docentry=T1.docentry

left outer join ODLN T10 on

T0.DocEntry = T10.DocEntry

left outer join (select * from INV4 where statype=1) vat on T1.linenum=vat.linenum and T1.docentry=vat.docentry

left outer join (select * from INV4 where statype=4) cst on T1.linenum=cst.linenum and T1.docentry=cst.docentry

left outer join (select * from INV4 where statype=-90) excise on T1.linenum=excise.linenum and T1.docentry=excise.docentry

left outer join (select * from INV4 where statype=-60) cess on T1.linenum=cess.linenum and T1.docentry=cess.docentry

left outer join (select * from INV4 where statype=-55) Hcess on T1.linenum=Hcess.linenum and T1.docentry=Hcess.docentry

left outer join (select * from INV4 where statype=14) CVD on T1.linenum=Hcess.linenum and T1.docentry=CVD.docentry

left outer join (select * from INV4 where statype=10) BCD on T1.linenum=Hcess.linenum and T1.docentry=BCD.docentry

left outer join (select * from INV4 where statype=11) Bcuseducess on T1.linenum=Hcess.linenum and T1.docentry=Bcuseducess.docentry

left outer join (select * from INV4 where statype=12) Bcushseeducess on T1.linenum=Hcess.linenum and T1.docentry=Bcushseeducess.docentry

left outer join (select * from INV4 where statype=13) AdddutyN on T1.linenum=Hcess.linenum and T1.docentry=AdddutyN.docentry

where T0.DocNum = '70000007'

In this query the Serial No subquery is not giving correct value...

But

SELECT Distinct T1.IntrSerial

FROM SRI1 T0

Inner Join OSRI T1 On T0.SysSerial=T1.SysSerial AND T1.ItemCode=T0.ItemCode

INNER JOIN DLN1 T2 ON T2.DocEntry=T0.BaseEntry AND T2.ItemCode=T0.ItemCode

inner join ODLN T3 on T2.DocEntry = T3.DocEntry LEFT JOIN INV1 T4 On T4.BaseEntry=T2.DocEntry Left Outer Join OINV T5 On T5.DocEntry=T4.DocEntry

WHERE T0.BaseType = 15

and T3.DocNum = '60000008'

this is giving right value...

please guide me experts how to incoporate this sub query to the main query to get the desired rsult...