cancel
Showing results for 
Search instead for 
Did you mean: 

Purchase Analysis query

Former Member
0 Kudos

dear friends,

i need query for this

Purchase Analysis report query

SeriesDocument NoDateBP NameItem CodeItem NameItem GroupTax CodeWhseBase ValueTax AmtTotal AmountLanded Cost GRNLanded Cost APTotal Purchase cost Including Landed cost

warm regards,

venkadeswaran

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

Please try with this.

;With Temp as (

SELECT     'IN' as Type,T0.DocEntry, T0.DocNum AS 'Invoice No.', T0.Docdate AS 'Invoice Date',T0.U_party_Bill_No AS 'Party Bill No.',T0.U_Party_Bill_Date AS 'Party Bill Date', T0.CardName AS 'Customer Name', T0.DocTotal - T0.VatSum AS 'Gross Amount',

                      ISNULL(T2.SeriesName,'Manual') as 'SeriesName', T0.DocTotal AS 'Net Amount', isnull(T1.TaxCode ,'NotDefined') AS 'Taxtype', T3.City,

                       --PCH10.LineText,

                       T1.Weight1,  T1.LineTotal,OCHP.ChapterID,ocrg.GroupName,

                      (Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-90 and PCH4.docentry=T0.Docentry  )BED@10,

                      (Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-80 and PCH4.docentry=T0.Docentry  )AED,

(Select distinct isnull (Sum(PCH4.taxsum),0) from PCH4 where PCH4.Statype = 1 and PCH4.Docentry=T0.Docentry ) VAT@125,

(Select distinct isnull (Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype = 7 and PCH4.Docentry=T0.Docentry ) ADVAT125,

(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-60 and PCH4.docentry=T0.Docentry )ECESS@2,

(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-55 and PCH4.docentry=T0.Docentry )SHE@1,

(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=  4 and PCH4.docentry=T0.Docentry )CST@2,

(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=  1 and PCH4.docentry=T0.Docentry )VAT@4,

(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=  7 and PCH4.docentry=T0.Docentry )ADDVAT@1,

T0.NumAtCard,OITM.ItemCode,T1.Dscription as 'Itemname' ,T1.Price,T1.Quantity ,Isnull(PCH12.TransCat,'NotDefined') as TransCat,

(Select top 1 Baseref from PDN1 Where PDN1.DocEntry = T1.BaseEntry ) as PONO,

T0.TotalExpns as Frght,

isnull((Select Mattype from OMTP Where OMTP.AbsEntry = OITM.MatType ),'NA') as Mattype,

T3.CardCode as PartyCode

FROM         OCHP INNER JOIN

                      OITM ON OCHP.AbsEntry = OITM.ChapterID RIGHT OUTER JOIN

                      OPCH AS T0 INNER JOIN

                      PCH1 AS T1 ON T0.DocEntry = T1.DocEntry Left Outer Join

                      NNM1 AS T2 ON T0.Series = T2.Series ON OITM.ItemCode = T1.ItemCode LEFT OUTER JOIN

                      --PCH10 ON T0.DocEntry = PCH10.DocEntry LEFT OUTER JOIN

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

                      OCRG ON OCRG.GroupCode = T3.GroupCode Left Outer Join

                      PCH12 ON T0.DocEntry = PCH12.DocEntry

                     

                     

WHERE T0.DocType <> 'S' and T0.Docdate >= '20141001'

and T0.Docdate <= '20141031'

--and T3.CardName Like (CASE When '{?Party@ Select 'All' as Cardname from OCRD Union Select Cardname from OCRD where CardType = 'S'}'

-- = 'All' then '%' else '{?Party@ Select 'All' as Cardname from OCRD Union Select Cardname from OCRD where CardType = 'S'}'

--end )

--and OCHP.ChapterID Like(Case When '{?Tarrif@ Select 'All' as ChapterID , 'All' as Dscription from OCHP union Select ChapterId,Dscription from OCHP}'

-- = 'All' then '%' else '{?Tarrif@ Select 'All' as ChapterID , 'All' as Dscription from OCHP union Select ChapterId,Dscription from OCHP}'

-- end )

Union All

SELECT     'RN' as Type,T0.DocEntry, T0.DocNum AS 'Invoice No.', T0.Docdate AS 'Invoice Date',T0.U_party_Bill_No AS 'Party Bill No.',T0.U_Party_Bill_Date AS 'Party Bill Date' , T0.CardName AS 'Customer Name', -1 * (T0.DocTotal - T0.VatSum) AS 'Gross Amount',

                      ISNULL(T2.SeriesName,'Manual') as 'SeriesName', (-1 * T0.DocTotal) AS 'Net Amount', isnull(T1.TaxCode,'NotDefined') AS 'Taxtype', T3.City,

                      --RPC10.LineText,

                      T1.Weight1,  (-1 * T1.LineTotal) as LineTotal,OCHP.ChapterID,ocrg.GroupName,

                      (Select distinct (-1 * isnull(Sum(RPC4.taxsum),0)) from RPC4 where RPC4.statype=-90 and RPC4.docentry=T0.Docentry  )BED@10,

                       (Select distinct (-1 * isnull(Sum(RPC4.taxsum),0)) from RPC4 where RPC4.statype=-80 and RPC4.docentry=T0.Docentry  )AED,

(Select distinct isnull (Sum(PCH4.taxsum),0) from PCH4 where PCH4.Statype = 1 and PCH4.Docentry=T0.Docentry ) VAT@125,

(Select distinct isnull (Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype = 7 and PCH4.Docentry=T0.Docentry ) ADVAT125,

(Select distinct (-1 * isnull(Sum(RPC4.taxsum),0)) from RPC4 where RPC4.statype=-60 and RPC4.docentry=T0.Docentry )ECESS@2,

(Select distinct (-1 * isnull(Sum(RPC4.taxsum),0)) from RPC4 where RPC4.statype=-55 and RPC4.docentry=T0.Docentry )SHE@1,

(Select distinct (-1 * isnull(Sum(RPC4.taxsum),0)) from RPC4 where RPC4.statype=  4 and RPC4.docentry=T0.Docentry )CST@2,

(Select distinct (-1 * isnull(Sum(RPC4.taxsum),0)) from RPC4 where RPC4.statype=  1 and RPC4.docentry=T0.Docentry )VAT@4,

(Select distinct (-1 * isnull(Sum(RPC4.taxsum),0)) from RPC4 where RPC4.statype=  7 and RPC4.docentry=T0.Docentry )ADDVAT@1,

T0.NumAtCard,OITM.ItemCode,T1.Dscription as 'Itemname' ,(-1 * T1.Price) as Price,(-1 * T1.Quantity) as Quantity ,Isnull(RPC12.TransCat,'NotDefined') as TransCat,

(Select top 1 Baseref from PDN1 Where PDN1.DocEntry = T1.BaseEntry ) as PONO,

-1 * T0.TotalExpns as Frght,

isnull((Select Mattype from OMTP Where OMTP.AbsEntry = OITM.MatType ),'NA') as Mattype,

T3.CardCode as PartyCode

FROM         OCHP INNER JOIN

                      OITM ON OCHP.AbsEntry = OITM.ChapterID RIGHT OUTER JOIN

                      ORPC AS T0 INNER JOIN

                      RPC1 AS T1 ON T0.DocEntry = T1.DocEntry Left Outer Join

                      NNM1 AS T2 ON T0.Series = T2.Series ON OITM.ItemCode = T1.ItemCode LEFT OUTER JOIN

                      --RPC10 ON T0.DocEntry = RPC10.DocEntry LEFT OUTER JOIN

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

                      OCRG ON OCRG.GroupCode = T3.GroupCode Left Outer Join

                      RPC12 ON T0.DocEntry = RPC12.DocEntry

                      WHERE T0.DocType <> 'S' and T0.Docdate >= '20141001'

and T0.Docdate <= '20141031'

--and T3.CardName Like (CASE When '{?Party@ Select 'All' as Cardname from OCRD Union Select Cardname from OCRD where CardType = 'S'}'

-- = 'All' then '%' else '{?Party@ Select 'All' as Cardname from OCRD Union Select Cardname from OCRD where CardType = 'S'}'

--end )

--and OCHP.ChapterID Like(Case When '{?Tarrif@ Select 'All' as ChapterID , 'All' as Dscription from OCHP union Select ChapterId,Dscription from OCHP}'

-- = 'All' then '%' else '{?Tarrif@ Select 'All' as ChapterID , 'All' as Dscription from OCHP union Select ChapterId,Dscription from OCHP}'

-- end )

)

Select * from Temp

--Where Temp.Taxtype Like (CASE When '{?TaxType@ Select 'All' as FldValue ,'All' as Descr from UFD1 union Select FldValue ,Descr from UFD1 where TableID = 'OPCH' and FieldID =25}'

-- = 'All' then '%' else '{?TaxType@ Select 'All' as FldValue ,'All' as Descr from UFD1 union Select FldValue ,Descr from UFD1 where TableID = 'OPCH' and FieldID =25}'

-- end )

-- and Temp.SeriesName Like (Case When '{?Series@ select 'All' as seriesName union select 'Manual' as seriesName union Select SeriesName from NNM1 where ObjectCode=18}'

-- = 'All' then '%' else '{?Series@ select 'All' as seriesName union select 'Manual' as seriesName union Select SeriesName from NNM1 where ObjectCode=18}'

--  end )

-- and Temp.TransCat Like (Case when '{?TransType@ select 'All' as TransCat from OTNC Union select 'NotDefined' as TransCat from OTNC Union Select TransCat from OTNC}'

-- = 'All' then '%' else '{?TransType@ select 'All' as TransCat from OTNC Union select 'NotDefined' as TransCat from OTNC Union Select TransCat from OTNC}'

-- end)

Regards,

Harshal

Answers (2)

Answers (2)

KennedyT21
Active Contributor
0 Kudos

Try this

DECLARE

@StartDate DATETIME,
@EndDate DATETIME,
@Dummy INTEGER

SELECT TOP 1 @Dummy = DocNum
FROM OPCH T0
WHERE T0.DocDate >= [%1]
AND T0.DocDate <= [%2]

SELECT

@StartDate= '[%1]' ,
@EndDate = '[%2]'

SELECT DISTINCT
T0.DocEntry,
T0.DocNum AS 'AP Inv Num',
CONVERT(VARCHAR(10), T0.DocDate, 3) AS 'Bill Date',
T0.CardName AS 'Vendor Name',
T1.Dscription AS 'Item Description',
T1.Quantity,
T1.Price AS 'Price',
T1.TaxCode,
T1.LineTotal AS 'Basic Line Total',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType = -90
AND PCH4.DocEntry = T0.DocEntry
AND PCH4.LineNum = T1.LineNum) AS 'BED',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType = -60
AND PCH4.DocEntry = T0.DocEntry
AND PCH4.LineNum = T1.LineNum) AS 'Cess',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType = 9
AND PCH4.DocEntry = T0.DocEntry
AND PCH4.LineNum = T1.LineNum) AS 'HeCess',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType = 1
AND PCH4.DocEntry = T0.DocEntry
AND PCH4.LineNum = T1.LineNum) AS 'VAT',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType = 8
AND PCH4.DocEntry = T0.DocEntry
AND PCH4.LineNum = T1.LineNum) AS 'CST',

T0.Comments

FROM OPCH T0

INNER JOIN PCH1 T1
ON T0.DocEntry = T1.DocEntry

WHERE T0.DocDate >= @StartDate
AND T0.DocDate <= @EndDate
AND T1.TargetType < > 19

FOR BROWSE

Regards

Kennedy

former_member188586
Active Contributor
0 Kudos

hi

please check bellow thread it may help you

http://scn.sap.com/thread/3676306

--Ramudu