Chad,
This is great information. However, is there nothing anywhere that just
says this "part" is not in the normal warehouse but out in the shop floor
somewhere with xxx number of on hand and is worth xxx amount?
From:
vantage@yahoogroups.com [mailto:
vantage@yahoogroups.com] On Behalf Of
Chad
Sent: Wednesday, July 18, 2012 12:09 PM
To:
vantage@yahoogroups.com
Subject: [Vantage] Re: DB tables needed for inventory (MFG,PUR, and WIP)
valuation - Vantage 8.03.409
If you just need summary information, you should be able to get what you
need from JobAsmbl. This stores the rolled up values for actual and
estimated costs. The TLAxxx fields are the top level actual costs and the
LLAxxx fields are lower level actual costs. So top level plus lower level is
your total cost. Likewise, the TLExxx and LLExxx fields are estimated costs.
There are buckets for labor, burden, material, subcontract, and material
burden.
If you need more detailed information you'd have to use JobHead, JobOper,
JobMtl, PartTran, and LaborDtl.
Here is the long version of the notes on this report when I asked Epicor a
similar question:
Summary:Work In Process (WIP) report notesDetails:PROBLEM DESCRIPTION :
Report notes for the WIP report
PROBLEM RESOLUTION :
Work in Process Report - Tech Notes
This section contains additional programming notes about the Work in Process
report in the Jobs module.
1. Files referred to are JobHead, JobOper, JobMtl, PartTran and LaborDtl.
2. The basic report logic is to figure out the total job costs as of the
given cutoff date, then how much of these total costs are to be relieved and
categorized as Cost of Sales and Cost to Inventory, with the remainder
categorized as Work in Process costs.
3. To provide the most accuracy, costs are totaled up then relieved at the
job detail level (for each JobMtl and JobOper record). The Cost to Inventory
is an accumulation of the PartTran MFG-STK records for each job.
4. A temporary table (WipRpt) is used to print the report. This file
consists of one record for each job selected, with the calculated quantities
and costs necessary to print the report. To create the record, read all
JobHead records for the current company. If job is closed, job number/part
number/product group does not match selection criteria or if there are no
LaborDtl or PartTran records for the job, read the next record, else create
WipRpt record initializing fields from JobHead.
5. Calculate QtyShipped, QtyToInventory and
Labor/Burden/Material/SubcontractCTI (Cost to Inventory):
Calculate QtyShipped as sum of all PartTran MFG-CUS records where TranDate
<= Cutoff Date
Calculate QtyToInventory as sum of all PartTran MFG-STK records where
TranDate <= Cutoff Date
Accumulate MFG-STK costs for Cost to Inventory cost totals
Set variable QtyRelieved equal to QtyShipped + QtyToInventory
6. Special cases for calculating Cost to Inventory, Cost of Sales and/or
WIP:
- if QtyRelieved > 0 and QtyShipped = 0 then WIP is reduced only by Cost to
Inventory, COS is always 0
- If QtyRelieved = 0 then all costs are considered WIP
- If QtyRelieved >= JobHead.ProdQty (or >= JobHead.CompleteQty if job
completed) and QtyShipped >0, then all costs are considered "relieved costs"
(either Cost to Inventory or COS) and WIP is always 0.
In all these cases, ToDate Costs are simple summarizations of PartTran and
LaborDtl cost records for each job into their proper cost categories (labor,
burden, material or subcontract). To calculate
TDLabor/Burden/Material/SubcontractCost (actual job costs to-date):
- Read all PartTran records for the job where TranDate<=EndDate, JobSeq>0
and TranType = PUR-MTL, PUR-SUB, STK-MTL, STK-ASM, ADJ-PUR, ADJ-MTL, ADJ-SUB
or SVG-STK
- If JobSeqType = "M" or "S" then add PartTran.ExtCost to TDMaterial or
SubcontractCost
- If TranType = SVG.STK then subtract cost instead of adding
- Read all LaborDtl records for the job where PayRollDate <= EndDate
- add LaborDtl.LaborHrs x LaborDtl.LaborRate to TDLaborCost
- add LaborDt.BurdenHrs x LaborDtl.BurdenRate to TDBurdenCost
7. In all other cases (QtyRelieved < JobHead.ProdQty for incomplete job,
QtyRelieved < JobHead.CompleteQty for completed job, QtyShipped > 0) the
total cost for each job detail record (JobMtl or JobOper) needs to be
factored into "cost relieved" based on the QtyRelieved.
Factoring Material and Subcontract Costs:
- Read all PartTran records for the job where TranDate<=EndDate and TranType
= PUR-MTL, PUR-SUB, STK-MTL, STK-ASM, ADJ-PUR, ADJ-MTL, ADJ-SUB, DMR-MTL,
DMR-SUB, MTL-DMR, INS-SUB, INS-MTL, SUB-DMR, or SVG-STK. Break by
assembly/type/seq. (The MTL-DMR, SUB-DMR reduce WIP.)
- Accumulate PartTran.ExtCost into DetailCost
- Accumulate PartTran.TranQty into DetailQty (not if SVG-STK)
- When sequence breaks, need to figure out how much of the accumulated
DetailCost should be considered "relieved cost";
Find the JobMatl, JobAsmbl or JobOper record for the break group (for
Material, if JobSeq=0 find JobAsmbl else JobMtl; for Subcontract, find
JobOper)
Calculate QtyPerOne of the end part (this is the material or subcontract
quantity requirement to produce 1 of the end part); calculate as either
JobAsmbl.RequiredQty, JobMtl.RequiredQty or JobOper.RunQty divided by
JobHead.ProdQty
Set QtyDivisor variable; If material or subcontract has been over-received
(actual quantity > estimated) then QtyDivisor = JobAsmbl.RequiredQty,
JobMatl.RequiredQty or JobOper.RunQty else QtyDivisor = DetailQty
Calculate the StandardQty (this is the amount of material/subcontract that
should have been used to fulfill the RelievedQty based on the estimates);
StandardQty = QtyRelieved x QtyPerOne
Calculate the RelievedCost for the detail record as either the DetailCost
(if DetailQty < standardQty or QtyDivisor = 0) or DetailCost/QtyDivisor x
QtyRelieved x QtyPerOne
Add RelievedCost to Material/SubcontractCOS
Add DetailCost to TDMaterial/SubcontractCost
Factoring Labor and Burden Costs:
Read all LaborDtl records for the job where PayRollDate<=EndDate; Break by
assembly/seq
Accumulate LaborHrs x LaborRate into DetailLaborCost
Accumulate BurdenHrs x BurdenRate into DetailBurdenCost
Accumulate LaborQty into DetailQty (do not include Rework quantities)
When sequence breaks, figure out how much of the accumulated DetailCost
should be considered "relieved cost";
- Find the JobOper record for the break group
- Calculate QtyPerOne as JobOper.RunQty/JobHead.ProdQty
- Set QtyDivisor variable as JobOper.RunQty (if overrun) else DetailQty
- Calculate the StandardQty as QtyRelieved x QtyPerOne
- Calculate the labor and burden RelievedCost for the detail record as
either the DetailCost (if DetailQty < StandardQty or QtyDivisor = 0) or
DetailCost/QtyDivisor x QtyRelieved x QtyPerOne
- Add RelievedCost to Labor/BurdenCOS
- Add DetailCost to TDLabor/BurdenCost
8. Cost of Sales comes from the PartTran records associated with the
Shipment. The cost of sales gets updated into shipment records when you
capture WIP and COS data.
9. At end of job, remove Cost to Inventory from COS calculated totals
(LaborCOS = LaborCOS - LaborCTI, MaterialCOS = MaterialCOS - MaterialCTI,
etc.)
10. Print each job:
- Total column figure is equal to crossfoot of Labor, Burden, Material and
Subcontract columns
- WIP costs are calculated as the difference between To-Date Costs and
Relieved Costs, for example,
WIPLabor = TDLaborCost - (LaborCTI + LaborCOS)
WIPMaterial = TDMaterialCost - (MaterialCTI + MaterialCOS) etc.
11. Accumulate and print report totals
- If sorted by Product Group, print product group and part headings and
subtotals
- If sorted by Part, print part headings and subtotals
--- In
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"flexial_ma" <mike.abell@...> wrote:
>
> I am needing to write a Crystal report that figures out the current value
(cost) of our inventory based on Quantity on Hand. We have purchased,
manufactured, and WIP (Work in Process - JOB) parts. I would requesting some
guidance on what tables in Vantage 8 (Progress) might house this
information. I am currently using PART, PARTBIN, PARTCLASS, and PARTCOST
tables but cannot find WIP information in those. I know about the stock
status report but I need it to be more customized so I am writing my own.
Any help or guidance on tables to use would be GREATLY appreciated. Thanks!
>
> Mike Abell
>
[Non-text portions of this message have been removed]