BAQ for Stock Status Report

Hi Epicor community,

I am interested in creating a BAQ (or an external BAQ) for Stock Status report.
The problem is after browsing the internet for finding the correct way of doing this, now I am confused.
At this point it seems no one knows how the canned report of stock status calculates the values. Currently I understand that this should be done through PartTran table (most likely) and some parts require an average calculation and some a different kind.
Are you able to provide the logic?
Or maybe I am mistaken and this should be done through GL?!

The logic that I think makes the most sense is to check what goes into stock and what leaves stock and what gets consumed/eliminated during the process and what adjustments are applied.

The fist table is what gets added to stock
Second one is what leaves stock
Third one is the adjustments
Fourth one is when part gets consumed without reaching stock

  with 
  
  incoming as  (select partnum, sum(TranQty) as qty, sum(ExtCost) as cost  FROM [CEL_Prod].[Erp].[PartTran]
  where TranDate <'Date' and TranType  like '%-stk'
  group by partnum
  ),


  outgoing as  (select partnum, sum(TranQty) as qty, sum(ExtCost) as cost  FROM [CEL_Prod].[Erp].[PartTran]
  where TranDate <'Date' and TranType  like 'stk-%'
  group by partnum
  ),


  adjustments as (select partnum, sum(TranQty) as qty, sum(ExtCost) as cost  FROM [CEL_Prod].[Erp].[PartTran]
  where TranDate <'Date' and TranType  like 'adj-%' 
  group by partnum
  ),

  jobtoship as (select partnum, sum(TranQty) as qty, sum(ExtCost) as cost  FROM [CEL_Prod].[Erp].[PartTran]
  where TranDate <'Date' and (TranType  like '%-cus' or TranType like '%-mtl') and TranType not like 'stk-%'
  group by partnum  
  )


  select 
  incoming.qty-outgoing.qty+adjustments.qty-jobtoship.qty as qtyfinal,
  incoming.cost-outgoing.cost+adjustments.cost-jobtoship.cost as costfinal,
  (incoming.cost-outgoing.cost+adjustments.cost-jobtoship.cost)/(incoming.qty-outgoing.qty+adjustments.qty-jobtoship.qty) as average

  from incoming
  left outer join outgoing
  on
  incoming.PartNum = outgoing.PartNum
  left outer join adjustments
  on
  incoming.PartNum = adjustments.PartNum
  left outer join jobtoship
  on
  incoming.PartNum = jobtoship.PartNum
  where incoming.partnum like 'Your Part Number'

Regards,
Shizar

Hi @Jason_Woods
I was wondering if you can provide your perspective here :slight_smile:

My previous version of this was simply a current status and not a version that does history.

1 Like

If you could share the logic it would be amazing. I think I can add the date logic to it as well. :slight_smile:

Aside from the more obvious logic like using PartBin to get the current Qty and lots, the basics was to use the Plant’s Cost Type for each part (PartPlant), the Plant’s Cost ID, and then get the costs from the PartCost table (add together the 5 cost buckets for the correct Cost Type).
A “CASE” statement can work to decide on which costing method is used.

SSR gets the QOH o on a specified date, by starting with the current QOJ and subtracting out all the part transactions that have happened after that date.

When it comes to the unit cost, SSR cheats. It uses the current cost, and not the cost on the date specified. you’ll need to make a sub-query that finds the newest parttran, that is prior to the SSR specified date. Not very simple. And even harder when cost methods like FIFO are used.

Hi @Jason_Woods
I will give it a try :slight_smile:

Hi @ckrusen
Thank you for the hint. I never thought of that. By any chance do you know how I should treat the transactions based on their trantype? I see these in our database:

ADJ-CST
ADJ-MTL
ADJ-PUR
ADJ-QTY
ADJ-SUB
ASM-INS
DMR-ASM
DMR-MTL
DMR-REJ
DMR-STK
INS-ASM
INS-DMR
INS-MTL
INS-STK
INS-SUB
KIT-CUS
MFG-CUS
MFG-STK
MFG-VAR
Mfg-Ven
MFG-WIP
MTL-INS
PLT-STK
PUR-INS
PUR-MTL
PUR-STK
PUR-SUB
PUR-UKN
RMA-INS
STK-ADJ
STK-CUS
STK-INS
STK-KIT
STK-MTL
STK-PLT
STK-STK
STK-UKN
SUB-INS
UKN-CUS
WIP-MFG

It should be easy subtracting the transactions from the specified date onwards as long as I know how trantype should be treated.

FYI, the cost is built into the PartTran, so you don’t need the cost data historically. Also, check the PartTran.InventoryTrans field. That will narrow things down I hope.

Oh wow… really? The cost in parttran is the average cost (if average is chosen obviously)?! That makes things very simple for me!
Thanks a million.

Don’t get too excited about the cost in parttran. It takes extra work to get it. Because you need the cost of the transaction prior to the oldest one that you’re subtracting out from the current QOH

1 Like

Hi @ckrusen
What I did was to find the latest transaction extended cost where qty is not 0 (to avoid adjustments and …) and divided it by tranqty, something like this:

In your professional opinion does this result in wrong output? (just for the latest cost prior to a certain date)

Looks good.

Would a ADJ-CST tran always have a tran qty != 0?

Might want to test for actual trantypes that would have a valid cost value.

Search this site to find the sign for each tran type.

Very valid point
Will do. Thank you very much! :slight_smile:

For the poor souls who need the trantype definition I provide it here :smiley:

Type

Displays the type of inventory transaction:

  • ADJ-CST - Adjustment to Stock Cost
  • ADJ-DEM - Adjustment to Demand Quantity
  • ADJ-MTL - Adjustment to Job Cost Material
  • ADJ-PUR - Purchase Price Variance (created by A/P invoice)
  • ADJ-QTY - Adjustment to Stock Quantity
  • ADJ-SUB - Adjustment to Job Cost Subcontract
  • ASM-INS - Assembly to Inspection
  • DMR-MTL - DMR Issue to Job Material
  • DMR-REJ - DMR to Returns
  • DMR-STK - DMR to Stock
  • DMR-SUB - DMR to Subcontract
  • INS-ASM - Inspection to Assembly
  • INS-DMR - Inspection to DMR
  • INS-MTL - Inspection to Material
  • INS-STK - Inspection to Stock
  • INS-REJ - Inspection to Reject
  • INS-SUB - Inspection to Job Subcontract
  • MFG-CUS - Shipment of Manufacturing Job to Customer
  • MFG-PLT - Manufacturing Receipt to Site
  • MFG-STK - Manufacturing Receipt to Stock
  • MFG-VEN - Shipment of Manufacturing Job to Supplier
  • MFG-VAR - Mfg Cost to Variance
  • MFG-WIP - Manufacturing Receipt to Job
  • MTL-DMR - Job Material to DMR. This has been replaced by MTL-INS when appropriate because the Epicor application code still references it for backward compatability.
  • MTL-INS - Material to Inspection
  • PLT-ASM - Site to Assembly
  • PLT-MTL - Site to Material
  • PLT-STK - Site to Stock
  • PUR-INS - Purchase Receipt to Inspection
  • PUR-MTL - Purchase Receipt to Material
  • PUR-STK - Purchase Receipt to Stock
  • PUR-SUB - Purchased Receipt to Subcontract
  • PUR-UKN - Purchased Receipt to Unknown (not Stock or Job)
  • RMA-INS - RMA Receipt to Inspection
  • STK-ASM - Stock to Job assembly
  • STK-CUS - Shipment of Stock to Customer
  • STK-DMR - Stock to DMR. This has been replaced by STK-INS and is obsolete; it is displayed when appropriate because the Epicor application code still references it for backward compatability.
  • STK-INS - Stock to Inspection
  • STK-MTL - Stock to Job Material
  • STK-PLT - Stock to Site
  • STK-SRV - Stock to Service
  • STK-STK - Stock to Stock Transfer
  • STK-UKN - Stock to Unknown Issue (Miscellaneous Issue)
  • SUB-DMR - Job Subcontract to DMR
  • SUB-INS - Subcontract to Inspection (Not implemented)
  • SVG-STK - Salvage Material Receipt to Stock
  • WIP-MFG - Job to Job

MTL-DMR replaced by MTL-INS, SUB-DMR not changed, SUB-INS was not implemented. MTL-DMR and STK-DMR are obsolete. However, code still references them for backward compatability.

I’m in that circle of hell right now. I get the calculation for the TotCost and group by the part, but then when I try to join it up to my main query that narrows it down to just the MAX Transaction number It doubles… I am sure I will get it eventually!

Picture of your BAQ designer?
(the graphic showing how the tables and subqueries are related)

I have one subquery that does the calculation: (PartTran.MtlUnitCost + PartTran.LbrUnitCost+ PartTran.BurUnitCost+ PartTran.SubUnitCost+ PartTran.MtlBurUnitCost)

I have a second subquery that does: MAX(PartTran.TranNum)

I join them in the main (top) query, but any which way I get multi-part identifier can’t be bound error.
image

I’d love to just assign a variable to the single result and pass it.

Put the second subquery in the first (the one that adds the cost fields together). The flow being that the subquery that returns the MAX(TranNum) is used to limit the part tran records used to add up that part’s cost. Then the first subquery would be in your top level query.

SubQ2 Returns one record for each part in parttran. Display fields:

  • Company (group by)
  • PartNum (Group By)
  • Calc field MaxTran = MAX(PartTran.TranNum)

SubQ1 Contains PartTran and SubQ2, relating company, part number and Trannum (to MaxTran).
Display fields:

  • Company
  • PartNum
  • Calc Filed DatedCost = MtlUnitCost + … + MtlBurUnitCos

In the top level insert SubQ1 joining Company and partnumber as needed in the top level. SubQ1.Calculated_DatedCost should hold the cost of interest.

I left a lot of details required for you end goal, but this should point you in the right direction.

Thanks so much!! Will try right now!

I must have missed something…

“The multi-part identifier “PartTran.TranNum” could not be bound.”