Sales Queries

[Former Member]

Hi Gurus

I'm a beginer in SAP and SQL,Im facing difficulty generating a sales report which shows all sale with credit notes deducted. Im using SAP B1 8.8, basically what i need is

Barcode, Itemcode, Item_type (which is a user defined value ), Size (UDF), Gender (UDF), Brand (UDF), Lintetotal, Quantity, Grossprofit, Ocrcode, Ocrname, Cardcode, Cardname, docdate

Im able to generate total sales however i cannot find a way to remove the retuned items from sales report. please note sometimes credit notes are created without any link to invoice or item ( when invoice is closed there is no way to link to it ).

I would apriciate if you can help me on this.

Kind Regards

Hussain Shah

[Former Member]

Hi Hussain......

Welcome to SAP Forum.......

Can you please paste your query which you made so that i can make necessary changes and give it to you back.......

Regards,

Rahul

[Former Member]

SELECT ivx.codebars,

ivx.itemcode,

oi.U_CustomItemCode,

oi.U_ProdName,

(SELECT "NAME" FROM "dbo"."@TYPE" WHERE CODE = oi."U_ItemType") 'TYPE',

(SELECT "NAME" FROM "dbo"."@SIZE" WHERE CODE = oi."U_SizeName") 'SIZE',

(SELECT "NAME" FROM "dbo"."@GENDER" WHERE CODE = oi."U_GenderName") 'GENDER',

(SELECT "NAME" FROM "dbo"."@BRAND" WHERE CODE = oi."U_BrandName") 'BRAND',

ivx.linetotal,

ivx.quantity,

ivx.grossprofit,

ivx.ocrcode,

(SELECT "OCRNAME" FROM "SBOLintelLive_DB"."dbo"."OOCR" WHERE ocrcode = ivx.ocrcode) 'OcrName',

cd.cardcode,

cd.cardname

FROM

(select iv.ocrcode, iv.itemcode, iv.codebars, iv.dscription, sum(iv.linetotal) 'linetotal',

sum(iv.quantity) 'quantity', sum (iv.grossprofit) 'grossprofit'

from ( select i.ocrcode, i.itemcode, i.codebars, i.dscription,

i.linetotal - (i.linetotal*

(case when i.discprcnt>0 then 0 else o.discprcnt/100 end)) 'linetotal',

i.quantity, i.grssProfit 'grossprofit'

from dbo.inv1 i, dbo.oinv o

where i.docdate >= CAST(@DateFrom AS DATETIME)

AND i.docdate <= CAST(@DateTo AS DATETIME)

AND o.docentry = i.docentry

AND i.acctcode in (N'31102', N'31105')

union all

select r.ocrcode, r.itemcode, r.codebars, r.dscription,

r.linetotal * -1, r.quantity * -1, r.grssprofit * -1

from dbo.rin1 r

where r.docdate >= CAST(@DateFrom AS DATETIME)

AND r.docdate <= CAST(@DateTo AS DATETIME)

AND r.acctcode in (N'31102', N'31105') ) iv

group by iv.ocrcode, iv.itemcode, iv.codebars, iv.dscription ) ivx

LEFT OUTER JOIN

dbo.OITM oi ON (ivx.itemcode = oi.itemcode)

LEFT OUTER JOIN

DBO.OCRD cd ON (oi.cardcode = cd.cardcode)

)

[Former Member]

Hi.....

the query is quite confusing....

Anyways I tell you the Table name and field name and there relation so thay can modify your query report.....

Join RPC1 table with INV1 with the relation of RPC1.BaseEntry=Inv1.DocEntry....

Above relation gives you all the records agins an Invoice return is made and you can find out the relevant fields and apply calculations.....

Hope this help you......

Regards,

Rahul

[Former Member]

This is excatly why did not post the query before, rather then helping you understand it will confuse you. so let me explain the query. This query is complicated due to lot of reason. im using one query to do a lot of calculations.

Sometimes items are sold with row level discount or colum level, so i need to find out total discount given out.

select    i.ocrcode, i.itemcode, i.codebars, i.dscription, 
                          i.linetotal - (i.linetotal* 
                          (case when i.discprcnt>0 then 0 else o.discprcnt/100 end)

Calculate Item sales posted to Account 31102 and 31105

select    i.ocrcode, i.itemcode, i.codebars, i.dscription, 
                          i.linetotal - (i.linetotal* 
                          (case when i.discprcnt>0 then 0 else o.discprcnt/100 end)) 'linetotal', 
                          i.quantity, i.grssProfit 'grossprofit'
                  from    dbo.inv1 i, dbo.oinv o
                  where    i.docdate  >= CAST(@DateFrom AS DATETIME)  
                  AND        i.docdate  <= CAST(@DateTo AS DATETIME)
                  AND        o.docentry = i.docentry
                  AND        i.acctcode in (N'31102', N'31105')
                  union all

Deduct and remove retuned items

(SELECT "OCRNAME" FROM "SBOLintelLive_DB"."dbo"."OOCR"  WHERE ocrcode = ivx.ocrcode) 'OcrName',
		  cd.cardcode,
		  cd.cardname
  FROM
         (select iv.ocrcode, iv.itemcode, iv.codebars, iv.dscription, sum(iv.linetotal) 'linetotal', 
                 sum(iv.quantity) 'quantity', sum (iv.grossprofit) 'grossprofit' 
          from  ( select    i.ocrcode, i.itemcode, i.codebars, i.dscription, 
                          i.linetotal - (i.linetotal* 
                          (case when i.discprcnt>0 then 0 else o.discprcnt/100 end)) 'linetotal', 
                          i.quantity, i.grssProfit 'grossprofit'
                  from    dbo.inv1 i, dbo.oinv o
                  where    i.docdate  >= CAST(@DateFrom AS DATETIME)  
                  AND        i.docdate  <= CAST(@DateTo AS DATETIME)
                  AND        o.docentry = i.docentry
                  AND        i.acctcode in (N'31102', N'31105')
                  union all
                  select    r.ocrcode, r.itemcode, r.codebars, r.dscription, 
                          r.linetotal * -1, r.quantity * -1, r.grssprofit * -1
                  from    dbo.rin1 r
                  where    r.docdate >= CAST(@DateFrom AS DATETIME)
                  AND        r.docdate <= CAST(@DateTo AS DATETIME)
                  AND        r.acctcode in (N'31102', N'31105', N'31108') ) iv
          group by iv.ocrcode, iv.itemcode, iv.codebars, iv.dscription ) ivx

Hope this clears your confusion.

[Former Member]

Hi Hussain...

I give you very simple query report.

Just make necessary changes according to the fields Standard and UDFs as you want....

SELECT T0.[DocNum], T0.[DocDate], T0.NumAtCard As 'Bill No.', T0.[CardName], T1.[Dscription], T1.[Quantity], T1.Price As 'Rate', T1.[LineTotal] As 'Value', T0.[DocTotal] As 'Total Value', (ISNULL((SELECT SUM(T2.QUANTITY) FROM rin1 T2 WHERE T2.DocEntry=T0.DocEntry),0)) RetQTY, (ISNULL((SELECT SUM(T2.LineTotal) FROM rin1 T2 WHERE T2.DocEntry=T0.DocEntry),0)) RetValue, (isnull((SELECT SUM((case when upper(t4.STACode) like 'BED%' then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) ExciseDuty,
	(isnull((SELECT SUM((case when upper(t4.STACode) like 'eCess%' then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) ECess,
	(isnull((SELECT SUM((case when upper(t4.STACode) like 'hes%' then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) HEdCess, (isnull((SELECT SUM((case when upper(t4.STACode) like 'VAT%' and t4.TaxRate=12.5 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) As 'VAT12.5',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'VAT%' and t4.TaxRate=4 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0))VAT4, (isnull((SELECT SUM((case when upper(t4.STACode) like 'VAT%' and t4.TaxRate=2 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0))VAT2, (isnull((SELECT SUM((case when upper(t4.STACode) like 'CST%' and t4.TaxRate=2 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) As 'CST2',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'CST%' and t4.TaxRate=4 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) As 'CST4', T2.TransCat As 'Form No.'  FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN INV12 T2 ON T0.DocEntry = T2.DocEntry WHERE (T0.[DocDate]>='[%0]' and T0.[DocDate]<='[%1]')

The above query inclused teh join for Sales Return too that it RIN and ORIN.

and its not RPC sorry....

Please Try to modify above query as per your output you needed......

hope this will help you.....

Thanks & Regards,

Rahul

[Former Member]

Hi

Thank you for the prompt reply, i have tried to run the query and i got this error message.

Conversion failed when converting datetime from character string

Regards

Shah

[kvbalakumar]

Hi Shah,

Rahul's query is working fine and not giving the error message as you mentioned.

Check whether you have copied the query properly or not?

Regards,

Bala

[Former Member]

Yes you are correct it was a mistake in copying the code accept my aplologies. will test the query and let you know.

Edited by: Shah Hussain on Apr 6, 2011 2:48 PM