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'
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 @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:
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.
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
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:
For the poor souls who need the trantype definition I provide it here
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!
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).
Display fields:
