How is IC220 “Location Totals Receipt” Calculated

Just because I want one I can't find one handy - but IC220 would also include IC receipts - things from IC20, IC22, IC23 etc and runs based on release date/update date - which may throw off your matching.

I also think it only reports on inventory tracked items - so your non-stock purchases are not in there either.

If you want to spot check your crystal report - PO58 is probably the better bet.

Thanks, Kat!  Very helpful.  I will take a look at PO58.

Since you're limiting the IC220 report to PO receipts this should match up. Are you converting POLINE.REC_QTY to the stock uom qty? IC220 shows quantity based on the stock UOM. If not, use this formula.

IF {POLINE.ENT_BUY_UOM} = {ITEMMAST.STOCK_UOM}
THEN {POLINE.REC_QTY}
ELSE IF {POLINE.ENT_BUY_UOM} = {ITEMMAST.ALT_UOM_02}
THEN {POLINE.REC_QTY}*{ITEMMAST.ALT_UOM_CONV_02}
ELSE IF {POLINE.ENT_BUY_UOM} = {ITEMMAST.ALT_UOM_03}
THEN {POLINE.REC_QTY}*{ITEMMAST.ALT_UOM_CONV_03}
ELSE {POLINE.REC_QTY}*{ITEMMAST.ALT_UOM_CONV_04}
etc...

Also, POLINE.REC_ACT_DATE is really the date of last receipt activity for the PO line. If there are multiple receipts for a line and one of those receipts is outside the date range you select in IC220, that receipt won't be captured in the IC220 report.

For example:

PO line 1: Qty ordered- 10 EA

Receipt for 2 EA on 8/29/14
Receipt for 3 EA on 9/01/14
Receipt for 5 EA on 9/05/14

POLINE.REC_ACT_DATE will be 9/05/14 and POLINE.REC_QTY will show 10. IC220 ran for a date range of 9/01/14-9/30/14 will show receipts of 8 EA.

Thank you for the clarification! 

No, I am not converting POLINE.REC_QTY to the Stock UOM QTY.  So, that is one source of the problem; and, I am sure that the POLINE.REC_ACT_DATE is another cause for the discrepancy between IC220 and the Crystal Report.  I will use the formula that you provided and see if I can get the Crystal Report’s total to come closer to IC220’s total.

I don't believe vendor returns are adjusted out of POLINE.REC_QTY, so that may effort the report accuracy too.

FYI, below, is the Where Clause taken from the Crystal Report:

 WHERE  ("PURCHORDER"."LOCATION"='98MDC' OR "PURCHORDER"."LOCATION"='MDC')
 AND ("POLINE"."REC_ACT_DATE">=TO_DATE ('01-09-2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
 AND "POLINE"."REC_ACT_DATE"

Locations 98MDC and MCD are inventory only locations I take it? No other item types are received for these locations?

Yes, that is correct.

Here's the entire query:

SELECT
  "POLINE"."LINE_NBR",
  "POLINE"."ITEM",
  "POLINE"."DESCRIPTION",
  "POLINE"."REC_ACT_DATE",
  "POLINE"."REC_QTY",
  "PURCHORDER"."LOCATION",
  "POLINE"."PO_NUMBER",
  "POLINE"."ENT_BUY_UOM",
  "POLINE"."ENT_UNIT_CST"
FROM "PURCHORDER" "PURCHORDER"
INNER JOIN "POLINE" "POLINE"
  ON ((("PURCHORDER"."COMPANY"  ="POLINE"."COMPANY")
  AND ("PURCHORDER"."PO_NUMBER" ="POLINE"."PO_NUMBER"))
  AND ("PURCHORDER"."PO_RELEASE"="POLINE"."PO_RELEASE"))
  AND ("PURCHORDER"."PO_CODE"   ="POLINE"."PO_CODE")
WHERE ("PURCHORDER"."LOCATION"='98MDC'
  OR "PURCHORDER"."LOCATION"    ='MDC')
  AND ("POLINE"."REC_ACT_DATE" >=TO_DATE ('01-09-2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
  AND "POLINE"."REC_ACT_DATE"   ORDER BY "POLINE"."REC_ACT_DATE"

Then, I total POLINE.REC_ACT_DATE at the end of the report.

Instead of using POLINE how about using PORECLINE? It would fix the inaccuracy due to the POLINE.REC_ACT_DATE issue. You would still have to add the formula to convert the receipt qty to stock uom.

SELECT
"PORECLINE"."PO_LINE_NBR"
"PORECLINE"."ITEM"
"PORECLINE"."DESCRIPTION"
"PORECLINE"."REC_DATE",
"PORECLINE"."ENT_REC_QTY"
"PORECLINE"."LOCATION"
FROM "PORECLINE" "PORECLINE"
WHERE ("PORECLINE"."LOCATION"='98MDC'
OR "PORECLINE"."LOCATION"='MDC')
AND "PORECLINE"."REC_DATE>=TO_DATE ('01-09-2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Ok, I will give it a try.  Thanks, Jon!

Ok, I made changes to the query; however, the total receipt qty that the query produces is still more than the total that IC220 displays.  I ran the following query:

SELECT
  PRL.REC_DATE AS PORECLINE_REC_DATE,
  PRL.ITEM AS PORECLINE_ITEM,
  PRL.DESCRIPTION AS PORECLINE_DESCRIPTION,
  PRL.PO_LINE_NBR AS PORECLINE_PO_LINE_NBR,
  PRL.LOCATION AS PORECLINE_LOCATION,
  PRL.ENT_REC_QTY AS PORECLINE_ENT_REC_QTY,
  (
  CASE PRL.ENT_REC_UOM
    WHEN ITE.STOCK_UOM THEN PRL.ENT_REC_QTY
    WHEN ITE.ALT_UOM_02 THEN (PRL.ENT_REC_QTY * ITE.ALT_UOM_CONV_02)
    WHEN ITE.ALT_UOM_03 THEN (PRL.ENT_REC_QTY * ITE.ALT_UOM_CONV_03)
    WHEN ITE.ALT_UOM_04 THEN (PRL.ENT_REC_QTY * ITE.ALT_UOM_CONV_04)
    WHEN ITE.ALT_UOM_05 THEN (PRL.ENT_REC_QTY * ITE.ALT_UOM_CONV_05)
    WHEN ITE.ALT_UOM_06 THEN (PRL.ENT_REC_QTY * ITE.ALT_UOM_CONV_06)
    WHEN ITE.ALT_UOM_07 THEN (PRL.ENT_REC_QTY * ITE.ALT_UOM_CONV_07)
    WHEN ITE.ALT_UOM_08 THEN (PRL.ENT_REC_QTY * ITE.ALT_UOM_CONV_08)
    WHEN ITE.ALT_UOM_09 THEN (PRL.ENT_REC_QTY * ITE.ALT_UOM_CONV_09)
    WHEN ITE.ALT_UOM_10 THEN (PRL.ENT_REC_QTY * ITE.ALT_UOM_CONV_10)
    ELSE PRL.ENT_REC_QTY
  END
  ) AS PORECLINE_ENT_REC_QTY_CONV
FROM PORECLINE PRL
LEFT OUTER JOIN ITEMMAST ITE
  ON ITE.ITEM = PRL.ITEM
WHERE (PRL.LOCATION='98MDC' OR PRL.LOCATION='MDC')
AND (PRL.REC_DATE >= TO_DATE ('01-09-2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
AND PRL.REC_DATE < TO_DATE ('01-10-2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
AND PRL.PO_CODE <> 'PRT'

OK I think I know what could be happening. The IC220 report you're comparing the Crystal report to is set up to return PO receipts only (Transaction Type = PO) for a date range, correct? I directed you to pull PORECLINE.ENT_REC_QTY or in your database it's PRL.ENT_REC_QTY. This data may not match with if there was a receiving adjustment. The adjustment hits the PRL.ENT_REC_QTY field. In that case use PRL.ORIG_REC_QTY in place of PRL.ENT_REC_QTY.

Ok, thanks Jon!