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
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
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
left outer join outgoing
incoming.PartNum = outgoing.PartNum
left outer join adjustments
incoming.PartNum = adjustments.PartNum
left outer join jobtoship
incoming.PartNum = jobtoship.PartNum
where incoming.partnum like 'Your Part Number'
I was wondering if you can provide your perspective here
My previous version of this was simply a current status and not a version that does history.
If you could share the logic it would be amazing. I think I can add the date logic to it as well.
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.
I will give it a try
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:
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
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)
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!
For the poor souls who need the trantype definition I provide it here
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.
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).
- 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.”