[Hardik Gupta]

Up

0

Down

::

Hi @larrycarrethers

This one trips up a lot of people because of how GP stores kit data in SOP30300.

The key thing to know: revenue sits on the kit header row (CMPNTSEQ = 0), and cost sits on the component rows (CMPNTSEQ > 0). Miss that, and your COGS will be all over the place.

The fix is straightforward: pull revenue from the header row, aggregate costs from components using LNITMSEQ as the link, and filter IV00101 for ITEMTYPE = 3 (Kits). There’s no native GP report for this, so SQL/SSRS is your best bet.

sql

SELECT
    h.SOPNUMBE, h.DOCDATE, h.CUSTNMBR,
    l.ITEMNMBR, l.XTNDPRCE AS Revenue,
    ISNULL(comp.Total_Cost, 0) AS COGS,
    l.XTNDPRCE - ISNULL(comp.Total_Cost, 0) AS Gross_Profit
FROM SOP30200 h
INNER JOIN SOP30300 l
    ON h.SOPNUMBE = l.SOPNUMBE AND h.SOPTYPE = l.SOPTYPE
    AND l.CMPNTSEQ = 0
INNER JOIN IV00101 iv ON l.ITEMNMBR = iv.ITEMNMBR AND iv.ITEMTYPE = 3
LEFT JOIN (
    SELECT SOPNUMBE, SOPTYPE, LNITMSEQ, SUM(EXTDCOST) AS Total_Cost
    FROM SOP30300 WHERE CMPNTSEQ > 0
    GROUP BY SOPNUMBE, SOPTYPE, LNITMSEQ
) comp ON l.SOPNUMBE = comp.SOPNUMBE
      AND l.SOPTYPE = comp.SOPTYPE
      AND l.LNITMSEQ = comp.LNITMSEQ
WHERE h.SOPTYPE = 3 AND h.VOIDSTTS = 0;

• This reply was modified 1 week, 4 days ago by  Kerry Hataley.