Item wise Arrival Cost

[former_member320372]

Hi All,

          I am purchasing raw materials from Local and import vendors. Now i want to know the item arriving cost based on the AP invoice including all tax & Freight Etc. Because i am purchasing same item from different vendor, so i want to know the invoice wise item arriving cost......

Cheers!

Prabakaran R

[former_member185296]

Hi,

Use the purchase analysis report to identify the purchase price  of the item based on suppliers.

PURCHASING --> PURCHASING REPORTS --> PURCHASE ANALYSIS

in the selection criteria select items tab and enter the particular from and to item code. then select ok. the report will generate with the total purchase value.

Then double click on the serial no such that you can get the list of purchases happened for the particular item code from different suppliers

Regards,

Vinoth

[former_member320372]

Hi Vinoth,

               Already i have tested that report. It's shows qty,unit price & base amount. But i want line wise item,qty,unit price,tax amount,Document freight Etc.

               Then only i can find the purchasing price(including all expns) of raw materials.

Cheers,

Prabakaran R

[former_member185296]

HI,

SELECT  M.DocNum AS 'AP Inv. #', M.DocDate as 'Date',

M.CardName as 'Customer Name',t4.groupname as 'Customer   Group',T6.ITMSGRPNAM,

L.Itemcode,L.Dscription,T7.[AcctName],L.Quantity,L.[PriceBefDi] as 'Unit Price',

L.LineTotal as 'Basic Amount.(Rs.)', L.TAXCODE,

(SELECT TaxSum FROM PCH4 where statype=-90 and DocEntry=M.DocEntry and linenum=L.linenum) as 'BED@12% ',

(SELECT TaxSum FROM PCH4 where statype=-60 and DocEntry=M.DocEntry and linenum=L.linenum) as 'Cess@2%',

(SELECT TaxSum FROM PCH4 where statype=-55 and DocEntry=M.DocEntry and linenum=L.linenum) as 'HECess @1%', 

(SELECT Taxsum FROM PCH4 where statype =1 and taxrate = 5     and docentry =m.docentry and linenum =l.linenum) VAT@5,

(SELECT Taxsum FROM PCH4 where statype =1 and taxrate = 14.5 and docentry =m.docentry and linenum =l.linenum) 'VAT@14.5',

(SELECT TaxSum FROM PCH4 where statype=4 and TAXRATE = 2 AND DocEntry=M.DocEntry and linenum=L.linenum) as ' CST@2  ',

(SELECT TaxSum FROM PCH4 where statype=4 and TAXRATE = 4 AND DocEntry=M.DocEntry and linenum=L.linenum) as ' CST@4  ',

(SELECT TaxSum FROM PCH4 where statype=5 and TAXRATE=10 AND DocEntry=M.DocEntry and linenum=L.linenum) as ' Ser.Tax@12%  ',

(SELECT LINETOTAL FROM PCH3 WHERE EXPNSCODE=1 AND DOCENTRY=M.DOCENTRY) AS 'FRIEGHT1',

(SELECT LINETOTAL FROM PCH3 WHERE EXPNSCODE=2 AND DOCENTRY=M.DOCENTRY) AS 'FRIEGHT2',

H.LINETOTAL FREIGHT,  L.LINETOTAL+L.VATSUM TOTAL

FROM OPCH M LEFT OUTER JOIN PCH1 L on L.DocEntry=M.DocEntry

LEFT OUTER JOIN PCH4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum

LEFT OUTER JOIN PCH5 J ON M.DocEntry = J.AbsEntry

LEFT OUTER JOIN PCH2 H ON M.DOCENTRY=H.DOCENTRY AND H.LINENUM=L.LINENUM

LEFT OUTER JOIN PCH3 F ON  M.DOCENTRY=F.DOCENTRY

INNER JOIN [OCRD] T3 ON M.CardCode = T3.CardCode

INNER JOIN OCRG T4 ON T3.GroupCode = T4.GroupCode

LEFT OUTER JOIN OITM T5 ON T5.ITEMCODE = L.ITEMCODE

LEFT OUTER JOIN OITB T6 ON T6.ITMSGRPCOD=T5.ITMSGRPCOD

LEFT JOIN OACT T7 ON T7.[AcctCode] = L.AcctCode

WHERE (M.DocDate >= '[%0]' AND M.DocDate <= '[%1]')  AND M.DOCTYPE ='[%2]'

GROUP BY  M.DocNum,M.DocDate,M.CardName,M.NumAtCard,L.TAXCODE,L.VATSUM,M.DocEntry,L.Itemcode,L.Dscription,L.Quantity,L.linenum,L.LineTotal,t4.groupname,L.  [PriceBefDi],M.Doccur,M.[DocTotal],T6.ITMSGRPNAM,H.LINETOTAL ,T7.[AcctName]

ORDER BY   M.DocNum,M.DocDate,M.CardName,M.NumAtCard,L.VATSUM,M.DocEntry,L.Itemcode,L.Dscription,L.Quantity,L.linenum,L.LineTotal,t4.groupname,L.  [PriceBefDi],M.Doccur,M.[DocTotal],T6.ITMSGRPNAM,H.LINETOTAL

[former_member320372]

Hi Vinoth,

               Your query shows some error in my data base......below is my query can you plz give the idea for this. In this query i got all the fields what i want. But now i want calculate line wise freight and Tax per QTY. Then only i can find the arrival cost of raw materials.

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.CardCode AS 'Vendor Code',

T2.CardName AS 'Vendor Name',

T3.GroupName AS 'Vendor Group',

T1.ItemCode AS 'ItemCode',

T1.Dscription AS 'Item Description',

T1.Quantity,

T1.Price AS 'Price',

T1.TaxCode,

T0.TotalExpns AS 'Freight',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = -90 AND PCH4.Taxrate=12.5

AND PCH4.DocEntry = T0.DocEntry

AND PCH4.LineNum = T1.LineNum) AS 'BED 12.5%',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = -90 AND PCH4.Taxrate=12

AND PCH4.DocEntry = T0.DocEntry

AND PCH4.LineNum = T1.LineNum) AS 'BED 12%',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = -90 AND PCH4.Taxrate=10

AND PCH4.DocEntry = T0.DocEntry

AND PCH4.LineNum = T1.LineNum) AS 'BED 10%',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = -90 AND PCH4.Taxrate=8

AND PCH4.DocEntry = T0.DocEntry

AND PCH4.LineNum = T1.LineNum) AS 'BED 8%',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = -90 AND PCH4.Taxrate=6

AND PCH4.DocEntry = T0.DocEntry

AND PCH4.LineNum = T1.LineNum) AS 'BED 6%',

(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 = -55

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.Taxrate = 14.5

AND PCH4.DocEntry = T0.DocEntry

AND PCH4.LineNum = T1.LineNum) AS 'VAT 14.5%',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = 1 and PCH4.Taxrate = 5

AND PCH4.DocEntry = T0.DocEntry

AND PCH4.LineNum = T1.LineNum) AS 'VAT 5%',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = 4

AND PCH4.DocEntry = T0.DocEntry

AND PCH4.LineNum = T1.LineNum) AS 'CST 2%',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = -80

AND PCH4.DocEntry = T0.DocEntry

AND PCH4.LineNum = T1.LineNum) AS 'AED',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = -70

AND PCH4.DocEntry = T0.DocEntry

AND PCH4.LineNum = T1.LineNum) AS 'SED',

T1.[VatSum],T1.[LineTotal]

FROM OPCH T0

INNER JOIN PCH1 T1

ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode=T2.CardCode INNER JOIN OCRG T3 ON T3.GroupCode=T2.GroupCode

WHERE T0.DocType = 'I' AND T0.DocDate >= @StartDate

AND T0.DocDate <= @EndDate

AND T0.DocType ='I'