Skip to Content
author's profile photo Former Member
Former Member

Sales query

hai

Blow this query i need a customer name how do i add pls reply soon , above this query mothly sales item wise report

Pls reply soon

select a1.[PART NO],a1.[PART DESCRIPTION],sum(a1.Quantity)[Qunatity],sum(a1.[BASE AMOUNT])[Total],

sum(a1.[BED 10%])[Excise],sum(a1.[ECESS 2%])[Cess],SUM(a1.[HSCESS 1%])[Hcess],SUM(a1.CST)[Cst],SUM(a1.VAT)[Vat],

SUM(a1.TOTAL)[Total]

from(

SELECT DISTINCT(T0.[DocNum]) AS 'INVOICE NUMBER', T0.[DocDate] AS 'INVOICE DATE', T0.[CardName] AS 'NAME OF THE PARTY',

T3.[City][City],T1.[ItemCode] AS 'PART NO', T1.[Dscription] AS 'PART DESCRIPTION',

T1.[Quantity], T1.[Price], T1.[LineTotal] AS 'BASE AMOUNT',

(SELECT sum(TaxSum) FROM OEI4 where statype=-90 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'BED 10%',

(SELECT sum(TaxSum) FROM OEI4 where statype=-60 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'ECESS 2%',

(SELECT sum(TaxSum) FROM OEI4 where statype=-55 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'HSCESS 1%',

(SELECT sum(TaxSum) FROM OEI4 where statype=4 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'CST',

(SELECT sum(TaxSum) FROM OEI4 where statype=1 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'VAT',

T1.[LineTotal]+t1.[VatSum] as'TOTAL' FROM OOEI T0 INNER JOIN OEI1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OEI4 T2 ON T0.DocEntry = T2.DocEntry

left JOIN OCRD T3 ON T3.CardCode=T0.CardCode

WHERE T1.[TargetType] ='-1' and T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND (T0.[CardName] ='[%2]' OR '[%2]'=' ') AND (T1.[Dscription] ='[%3]' OR '[%3]'=' ')AND T0.[Series]<>'52' AND T1.[LocCode]<>'1'

) A1 group by a1.[PART NO],a1.[PART DESCRIPTION]

union all

select a1.[PART NO],a1.[PART DESCRIPTION],sum(a1.Quantity)[Qunatity],sum(a1.[BASE AMOUNT])[Total],

sum(a1.[BED 10%])[Excise],sum(a1.[ECESS 2%])[Cess],SUM(a1.[HSCESS 1%])[Hcess],SUM(a1.CST)[Cst],SUM(a1.VAT)[Vat],

SUM(a1.TOTAL)[Total]

from(

SELECT DISTINCT(T0.[DocNum]) AS 'INVOICE NUMBER', T0.[DocDate] AS 'INVOICE DATE', T0.[CardName] AS 'NAME OF THE PARTY',

T3.[City][City],T1.[ItemCode] AS 'PART NO', T1.[Dscription] AS 'PART DESCRIPTION',

T1.[Quantity], T1.[Price], T1.[LineTotal] AS 'BASE AMOUNT',

(SELECT sum(TaxSum) FROM INV4 where statype=-90 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'BED 10%',

(SELECT sum(TaxSum) FROM INV4 where statype=-60 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'ECESS 2%',

(SELECT sum(TaxSum) FROM INV4 where statype=-55 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'HSCESS 1%',

(SELECT sum(TaxSum) FROM INV4 where statype=4 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'CST',

(SELECT sum(TaxSum) FROM INV4 where statype=1 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'VAT',

T1.[LineTotal]+t1.[VatSum] as'TOTAL' FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN INV4 T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN OCRD T3 ON T3.CardCode=T0.CardCode

WHERE T1.[TargetType] ='-1' and T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND (T0.[CardName] ='[%2]' OR '[%2]'=' ') AND (T1.[Dscription] ='[%3]' OR '[%3]'=' ') AND T0.[Series]='41'

) A1 group by a1.[PART NO],a1.[PART DESCRIPTION]

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Nov 09, 2011 at 09:11 AM

    Hi,

    Check this

    
    select a1.[Business Partner Code] as 'Cardcode', a1.[PART NO] as 'NO',a1.[PART DEscription] as 'DESCRIPTION',sum(a1.[Quantity]) as 'Qunatity',sum(a1.[BASE AMOUNT]) as 'Total',
    sum(a1.[BED 10%]) as 'Excise',sum(a1.[ECESS 2%])as 'Cess',SUM(a1.[HSCESS 1%]) as 'Hcess',SUM(a1.[CST]) as 'Cst',SUM(a1.[VAT]) as 'Vat',
    SUM(a1.TOTAL)as 'Total'
    from(
    SELECT DISTINCT(T0.DocNum) AS 'INVOICE NUMBER', T0.DocDate AS 'INVOICE DATE', T0.CardName AS 'NAME OF THE PARTY',
    T3.CityCity,T1.ItemCode AS 'PART NO', T1.Dscription AS 'PART DESCRIPTION', 
    T1.Quantity, T1.Price, T1.LineTotal AS 'BASE AMOUNT', T3.cardcode as 'Business Partner Code', 
    (SELECT sum(TaxSum) FROM OEI4 where statype=-90 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'BED 10%',
    (SELECT sum(TaxSum) FROM OEI4 where statype=-60 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'ECESS 2%',
    (SELECT sum(TaxSum) FROM OEI4 where statype=-55 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'HSCESS 1%',
    (SELECT sum(TaxSum) FROM OEI4 where statype=4 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'CST',
    (SELECT sum(TaxSum) FROM OEI4 where statype=1 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'VAT',
    
    T1.LineTotal+t1.VatSum as'TOTAL' 
    FROM OOEI T0 
    INNER JOIN OEI1 T1 ON T0.DocEntry = T1.DocEntry 
    INNER JOIN OEI4 T2 ON T0.DocEntry = T2.DocEntry 
    left JOIN OCRD T3 ON T3.CardCode=T0.CardCode
    WHERE T1.TargetType ='-1' and T0.DocDate >=[%0] AND T0.DocDate <= [%1] AND (T0.CardName ='%2' OR '%2'=' ') AND (T1.Dscription ='%3' OR '%3'=' ')AND T0.Series<>'52' AND T1.LocCode'1' 
    ) a1 group by a1.[PART DESCRIPTION],a1.[PART NO],a1.[Business Partner Code]
    
    
    Union all
    
    select a1.[Business Partner Code] as 'Cardcode', a1.[PART NO] as 'NO',a1.[PART DEscription] as 'DESCRIPTION',sum(a1.[Quantity]) as 'Qunatity',sum(a1.[BASE AMOUNT]) as 'Total',
    sum(a1.[BED 10%]) as 'Excise',sum(a1.[ECESS 2%])as 'Cess',SUM(a1.[HSCESS 1%]) as 'Hcess',SUM(a1.[CST]) as 'Cst',SUM(a1.[VAT]) as 'Vat',
    SUM(a1.TOTAL)as 'Total'
    from(
    SELECT DISTINCT(T0.DocNum) AS 'INVOICE NUMBER', T0.DocDate AS 'INVOICE DATE', T0.CardName AS 'NAME OF THE PARTY',
    T3.City,T1.ItemCode AS 'PART NO', T1.Dscription AS 'PART DESCRIPTION', 
    T1.Quantity, T1.Price, T1.LineTotal AS 'BASE AMOUNT', T3.cardcode as 'Business Partner Code', 
    (SELECT sum(TaxSum) FROM INV4 where statype=-90 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'BED 10%',
    (SELECT sum(TaxSum) FROM INV4 where statype=-60 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'ECESS 2%',
    (SELECT sum(TaxSum) FROM INV4 where statype=-55 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'HSCESS 1%',
    (SELECT sum(TaxSum) FROM INV4 where statype=4 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'CST',
    (SELECT sum(TaxSum) FROM INV4 where statype=1 and DocEntry=T0.DocEntry and LineNum=t1.LineNum) as 'VAT',
     
    T1.LineTotal+t1.VatSum as 'TOTAL' 
    FROM OINV T0 
    INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
    INNER JOIN INV4 T2 ON T0.DocEntry = T2.DocEntry 
    INNER JOIN OCRD T3 ON T3.CardCode=T0.CardCode
    WHERE T1.TargetType ='-1' and T0.DocDate >= '[%0]' AND T0.DocDate <='[%1]' AND (T0.CardName ='%2' OR '%2'=' ') 
    AND (T1.Dscription ='%3' OR '%3'=' ') 
    ) a1 group by a1.[PART DESCRIPTION],a1.[PART NO],a1.[Business Partner Code]
    
    

    Kind Regards,

    Jitin

    SAP Business One Forum Team

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.