on 09-29-2011 3:42 AM
Hi,
I need to create a inventory report which focuses on the Quantity on Hand, it's cost breakdown per UOM and its corresponding Order No -- said Order No is a UDF field in documents.
How do I go about it with the query?
Please, Help!
The client industry is Vehicle Distribution, wherein the vehicles are produced here but raw/base units are purchased outside the country.
So lets say for: As of today
Item1: Vehicle 1 - completely built up unit
Qty on Hand: 17
Cost breakdown per unit (based on Qty on Hand): ???
Order No: Sales Order No/Lot No of the raw/base unit. it is set up as UDF in Goods Receipt PO and/or Receipt from Production--> Serial Number Report
Cost breakdown is variable as exchange rates are to be considered..
so layout would be like:
ItemCode (Grp 1)
Order No: (Grp2) Cost: based on Receipt from production where landed cost have been factored in
Sample:
AD6
AD6 - Lot 1 12456.75
AD6 - Lot 2 17859.30
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Kristine,
there are several tables which may be relevant
OITM is the master Item table and has units of measure for purchasing and inventory.
OITM has a field for ON Hand - OnHand this quantity corresponds to the units in InvntryUom. It is an inventory quantity not a purchase units quantity.
The simple cost is in LastPurPrc the last purchase price. EvalSystem will tell you whether the item has standard costing, FIFO or moving average
Are you trying to look at past purchases to get the costs? Purchase order data is in OPOR and POR1. However you may need to look at the receivables tables to see the latest receivables for the item - OPDN and PDN1
If the data is set up as FIFO then there are buckets set up to store the most recent purchases. OIVQ I think.
you may find that the joins that you need mean you need to create a view or procedure in SQL and use Crystal Reports to create what you are after.
I'm new to this forum. Hope this helps
Chris
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Khristine......
Do you have any User defined Field for BreakDown Cost of Item or from where you are deriving this cost?
Can you give some example?
By the way you get this Report from OITM Table......
Regards,
Rahul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Give an example what do you mean cost breakdown per UOM.
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.