MK 8.1 (very similar to IVc4); BISAM DB; HP-UX; no source.

I have a bit of a strange report requirement for an SFC order packet. Using one of the shop floor order reports (I am playing with tisfc040811000), find run times for each operation of each subcomponent: link tisfc001.mitm to tibom010.mitm and then tibom010.sitm to tirou020.mitm, and return all routing records for each subcomponent.

The report should look like:

Header
-------
Sucomponent; Net Qty * Plan Qty
-------------
Operation number; task; work center; set up time; run time

What is coming out is:

Header
-------
Sub1
Sub2
Sub3
-------
Details for Sub3

I could post some code but honestly I have tried so many different ways - placing counts; layout.again; etc in different sections - that I don't know if any of it is valuable.

Without source am I fighting a losing battle?

EDIT: I should elaborate that I can't use one of the ticst001-based reports (Material list, Material issue note) because the subcomponents I am looking for are phantoms in the tibom010 table.

Well if tisfc001.mitm makes it to the report then you should be able to do it. Just so show that you can do it. Create an after.report layout section - you really do not need anything in the layout. In the script just put a simple select:


after.report.1:
after.layout:
select tibom010.*
from tibom010
where tibom010._index1 = {:tisfc001.mitm}
spool.pr.line = tab$(5) & tibom010.mitm & tab$(5) & tibom010.sitm
spool.line()
endselect


It will not be pretty, but it should at least list all the subitems. Making it pretty can come later. I did this off the top of my head and I really hope you are not trying to explode a multi-level bom. That becomes something different.

Is my deduction correct that if a field is being used in the program script that reusing that field for another purpose on the same report will be futile?

You see in my example above that I am trying to link tibom010.sitm with tirou020.mitm, when I'm quite certain that the program script has already established that tisfc001.mitm is what links tirou020.mitm.

I decided to create a query where I link tisfc001 and tisfc010 by pdno. Then, in the report script of that query, I make my other links. It works great, but I would have preferred to have this an optional report when running "print order documents" rather than a stand alone query.

FYI, here is the report script:


declaration:

long noop
long count
long qtp
double purt
extern domain tcmcs.str50 vksbarcode

header.1:
before.layout:

select
tiitm001.item,
tiitm001.csel,
tiitm001.seak,
tiitm001.dsca,
tiitm001.csig,
tiitm001.dscd

from
tiitm001

where
tiitm001.item = :tisfc001.mitm

selectdo
endselect

select

tcmcs003.dsca

from
tcmcs003

where
tcmcs003.cwar = :tisfc001.cwar

selectdo
endselect

select
tiecc030.revt

from
tiecc030

where
tiecc030.item = :tisfc001.mitm and
tiecc030.revn = :tisfc001.revn

selectdo
endselect

select
tiecc010.dsca

from
tiecc010

where
tiecc010.ectp = :tiecc030.revt

selectdo
endselect

select
tcmcs022.dsca

from
tcmcs022

where
tcmcs022.csel = :tiitm001.csel

selectdo
endselect

before.tisfc010.opno.1:
before.layout:

select count(*):count

from tirou020,
tibom010

where tibom010.mitm = :tisfc001.mitm and
tibom010.cpha = tcyesno.yes and
tirou020.mitm = tibom010.sitm and
tirou020.opno = :tisfc010.opno

selectdo
endselect

if count = 0 then
noop = 1
else noop = 0
endif

detail.10:
before.layout:

select
tirou020.*,
tibom010.*,
tirou003.*,
tirou001

from tirou020
tibom010

where tibom010.mitm = :tisfc001.mitm and
tibom010.cpha = tcyesno.yes and
tirou020.mitm = tibom010.sitm and
tirou020.opno = :tisfc010.opno and
tirou003.tano = :tisfc010.tano and
tirou001.cwoc = :tisfc010.cwoc

order by tibom010.sitm desc
as set with :count rows
selectdo
endselect


qtp = tisfc001.qutp * tibom010.qana

purt = qtp * tirou020.rutm

vksbarcode = "=0=1=" & str$(tisfc001.pdno) & "=3=" & str$(qtp) & "=3=" & str$(purt) & "=3=" & str$(tirou020.sutm) & "="

after.layout:

count = count -1
if count > 0 then
layout.again()
endif

detail.20:
before.layout:

select
tirou020.*,
tirou003.*,
tirou001.*

from
tirou020,
tirou003,
tirou001

where
tirou020.mitm = :tisfc001.mitm and
tirou020.opno = :tisfc010.opno and
tirou003.tano = :tisfc010.tano and
tirou001.cwoc = :tisfc010.cwoc

selectdo
endselect

qtp = tisfc001.qutp

purt = qtp * tirou020.rutm

vksbarcode = "=0=1=" & str$(tisfc001.pdno) & "=3=" & str$(qtp) & "=3=" & str$(purt) & "=3=" & str$(tirou020.sutm) & "="


Detail 10 is conditional on noop = 0 and Detail 20 on noop = 1. The vksbarcode is a variable that contains production info and some control characters that are used to create a barcode that when scanned, populates a form in another application.