ISTech Support Forum - BM-G: Materials component total

heliosquare

Full Member

Posts: 121
Gender: male

BM-G: Materials component total
02/11/05 at 16:46:25

I'm building a report in which I need the materials component total figure (as shown by running BM-G on any given part number).

I've spent the last 2 hours trying to figure out which database file(s) are needed to get this figure. Does anyone have any ideas as to how to do this? I felt like I was getting close using WOMAT but I'm missing something...below is an overview of the report as it stands:

Database files:
BKARHINV
BKARHIVL

The point of the report is to calculate the Throughput Per Unit (throughput accounting practice) based on any given part number input as described below.

I've got a few parameter fields set up in order to make the report more dynamic. One parameter field is for the part number, another is for a date range, and the third is where I manually enter the materials component total for the report to use (I want to eliminate this parameter field and have the report generate the figure based on the part number entered in the part number parameter field).

The report works fine as it stands but if I can manage work out how to do what I described above then it will be golden...

Hope this makes sense and thanks in advance to anyone willing/able to help me out...

Back to top

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 634
Gender: male

Re: BM-G: Materials component total
Reply #1 - 02/12/05 at 08:58:25

If I understand you correctly, you want the total material cost of all of the components of a given item's Master BOM calculated at standard cost.

The WOBOM file is where the Master BOM is copied when a Work Order is created. This allows you to make changes to only the BOM on a given WO for a specific production run.

The Master BOM file is BKBMMSTR. If you do not want to rely on the rolled up costs in MTICMSTR for your parent item number, you could link the component item codes from BKBMMSTR to the MTICMSTR file. The costs for each component are contained in the MTIC_PROD_RCOST[x] fields.

The exact field to use depends on which cost you want from the Item master record. I always have to look them up with Maintain Database to make sure I use the one I want.

In your report, you could set up a simple sub-report in a hidden 'Report Header' section that populates a global variable with the sum of all costs on the BOM. To do this, you can create a Crystal SQL function that is the qty required from the BOM multiplied by the cost that you chose. You can then summerize this SQL field and set a global variable equal to it's value.

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

heliosquare

Full Member

Posts: 121
Gender: male

Re: BM-G: Materials component total
Reply #2 - 02/14/05 at 12:50:19

Thanks Kevin for the input. You hit the nail on the head in regards to what I want to do. This is my first crack at using Crystal Reports so I'm kinda relying on intuition as I go...

2 problems:

1) In the sub-report I added BKMMSTR and MTICMSTR

I linked BKBM_COMPONENT to MTIC_PROD_CODE and get relevant results (level 1 only). However this only works if I don't specify a parent id in BKBM_PARENT. I only want the sub-report to return the components of a specified parent part number. The report simply returns zero results when I get specific (eg. {BKBMMSTR_B.BKBM_PARENT} = "3-IMP5502C")

2) How do I get subsequent level data? The BOM for the part number in the example above goes 5 levels deep...

Back to top

IP Logged

Tim Keating

Senior Member

DBA CLASSIC WIN 2000
C/S

Posts: 298

Re: BM-G: Materials component total
Reply #3 - 02/14/05 at 13:00:40

Here's the trick:

1. Add the BKBMMSTR to your report again. You will get a message indicating it is already in the report and will be promped to give it an alias name.

2. Name it BKBMMSTR2.

3. Clear any automatic links that were created and create a link from BKBM_COMPONENT in the BKBMMSTR file to BKBM_PARENT in the BKBMMSTR2 File.

4. Repeat this procedure as many times as you need to depending on the depth of your BOM's. We have 4 levels so we have 4 instances of the BKBMMSTR File. (BKBMMSTR thru BKBMMSTR3 all linked as described above.)

5. Create your report using proper grouping and sorting methods. You can pull part descriptions by linking all Parent and Component fields back to BKICMSTR File BKIC-PROD_DESC Field.

Back to top

Tim Keating
Motor Guard Corporation

IP Logged

heliosquare

Full Member

Posts: 121
Gender: male

Re: BM-G: Materials component total
Reply #4 - 02/14/05 at 13:45:38

I'm still confused ???

If I add BKBMMSTR to the report 5 times then I need to have 5 instances of BKBM_COMPONENT (one from each alias) to display the part numbers in each of the 5 levels, right? This seems to create alot of duplicate results...like I said, I'm confused.

Also, why can't I use the select expert on BKBM_PARENT to select only those records with a specific parent id?

Back to top

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 634
Gender: male

Re: BM-G: Materials component total
Reply #5 - 02/14/05 at 17:51:29

If you just need the rolled up standard cost number for the parent, You could just use the appropriate MTIC_PROD_RCOST[x] field from the MTICMSTR file for the Parent Item #.

The Value in this field will be whatever the last rollup calculated. Again, you need to use Maintain Database to determine which RCOST field to use. One of them is the total rolled up material cost.

------------------

OK, I though I remembered this, here's a post with the RCOST fields defined:
http://www.istechforum.com/cgi-bin/YaBB.pl?board=DBA-Items;action=display;num=10 73939953;start=2#2

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

heliosquare Full Member Posts: 121 Gender:	Re: BM-G: Materials component total Reply #6 - 02/15/05 at 09:23:32 Ok, thanks a ton Kevin. The fact that there was a single field holding the rolled up cost apparently went right over my head the first time. That value will suit my needs just fine.
Back to top	IP Logged

Tim Keating Senior Member DBA CLASSIC WIN 2000 C/S Posts: 298	Re: BM-G: Materials component total Reply #7 - 02/15/05 at 09:37:02 Don't know if this helps but it is a screenshot of the table links. Each instance of BKBM_PARENT or COMPONENT could be linked back to a single MTICMSTR file to pick up descriptions, costs, etc.... As Kevin says, though, this cost data is rolled up into the parent item by DBA and can be seen in the BM-G report.
Back to top	Tim Keating Motor Guard Corporation IP Logged