I am trying to get data twice from the same table. I am trying to get the subassembly(subass) where a selected purcased item is used. Then I am trying to get the main item(test) that the subassembly is used in.

The system is returning the subass as the test.
declaration:
table ttiitm001
table ttibom010

extern domain tcitem subass, test

detail.1:
before.layout:
select tibom010.*
from tibom010, tiitm001
where tibom010.sitm = :tdpsc001.item
and tibom010.sitm = tiitm001.item
and tiitm001.kitm =2
and tibom010.exdt =0
selectdo
endselect

select tiitm001.*
from tiitm001
where tiitm001.item = :tibom010.sitm
selectdo
subass = tibom010.mitm
endselect

select tibom010.*
from tibom010
where tibom010.mitm = :subass
selectdo
test = tibom010.mitm
endselect

I am not sure I understand why you are doing this.
To get the make components for a sub-component you only need one query.


select tibom010.mitm,tibom010.sitm,tiitm001.dsca
from tibom010, tiitm001
where tibom010.sitm = :tdpsc001.item
and tibom010.sitm refers to tiitm001.item
and tiitm001.kitm =2
and tibom010.exdt =0
selectdo
endselect


This would return ALL make items for a purchase part. But because of the selectdo/endselect you would only get the last make item. Are you sure you want to do this in the script? I would expect some kind of spool commands in the select do to print results.

I do not understand the other queries at all. The second query sets subass to tibom010.mitm, but the query is on tiitm001. In this case the subass will be set to the LAST make item from the first query. Then you turn around and find the make item on the tibom010 again. This would find all components of the make item, but it would only set it for the last sub-items.

It sounds like you are passing a purchase item to the report and want to print all of the assemblies that this item is used on? If true then you could use the query above(with spool commands) to print all of the assemblies. Just not sure what you are trying to do.

Mark

Dear Itannous,

You have to change the WHERE clause in the last query, ie line 4 from the bottom, to:

where tibom010.sitm=:subass

and you should get the right answer.

By the way, to speed up query you might want to use table indices. Particularly the index2 of table tibom010 because it contains tibom010.sitm

Rgds,
Paul

Thanks Paul, that worked. How can I print a summary. I have in the past just deleted the detail layout and added the after field, but that is not working this time. Any ideas.

Dear Itannous,

You can print after.field kind of summary without putting any detail section if the number you want to summarise has been passed from program script to report engine. Say your prog script already send the purchase amount per PO line to report engine, then you can omit detail section and summarise purchase amount per PO just using after.field

However, if you need to calculate something based on data thrown by program script and summarise this calculated data, you'd have to have detail section. For example, prog script send purchase amount per PO line in purchase currency only and you need the purchase amount in Thailand Baht. Then you'd need to convert it in detail section before summarising in after.field section. Of course you can use lattr.print=false after calculation in detail section, so the calc will still be performed but the detail section won't show up. This way, you can still have summary report of numbers that need additional calc

Rgds,
Paul

Thanks for you help Paul

I thought it had worked, but when I reviewed the details of the report, it is only writing the last main item(mitm) for the subassembly (sitm). It only writes the sitm once, even though it appears in many main items.
How can I write the details many times.

This is the report script

declaration:
table ttimps200
table ttimps300
table ttiitm001
table ttibom010
extern domain tcitem subass, test
extern domain tcqiv1 unosub, usgmtm, quan1, quan2, val1, val2
extern domain tccuni unomea, unmain
extern domain tcdate date.f
extern domain tcdate date.t
extern domain timps.plnc plnc.f
extern domain timps.plnc plnc.t
extern domain timps.plvl plvl.f
extern domain timps.plvl plvl.t
extern domain timps.plnc livec
extern domain timps.plvl livel

detail.1:
before.layout:
select tibom010.*
from tibom010, tiitm001
where tibom010.sitm = :tdpsc001.item
and tibom010.sitm = tiitm001.item
and tiitm001.kitm =2
and tibom010.exdt =0
order by tibom010._index2
selectdo
endselect

select tiitm001.*
from tiitm001
where tiitm001.item = :tibom010.sitm
selectdo
unomea = tiitm001.cuni
unosub = tibom010.qana
subass = tibom010.mitm
endselect

select tiitm001.*
from tiitm001
where tiitm001.item = :tibom010.mitm
selectdo
unmain= tiitm001.cuni
endselect

select tibom010.*
from tibom010
where tibom010.sitm = :subass
and tibom010.exdt = 0
selectdo
test = tibom010.mitm
usgmtm = tibom010.qana
endselect

select sum (timps300.demf)
from timps300, timps200
where timps300.plnc inrange :plnc.f and :plnc.t |= "300"
and timps300.plvl inrange :plvl.f and :plvl.t |= 99
and timps300.plni = timps200.plni
and timps200.plnc = :livec
and timps200.plvl = :livel
and timps300.plni = :test
and timps300.demf > 0
and timps300.pdat inrange :date.f and :date.t
selectdo
quan1 = timps300.demf
endselect

select sum(timps300.demf)
from timps300, timps200
where timps300.plnc inrange :plnc.f and :plnc.t |= "300"
and timps300.plvl inrange :plvl.f and :plvl.t |= 99
and timps300.plni = timps200.plni
and timps200.plnc = :livec
and timps200.plvl = :livel
and timps300.plni = :subass
and timps300.demf > 0
and timps300.pdat inrange :date.f and :date.t

selectdo
quan2 = timps300.demf
endselect

val1 = unosub * quan2
val2 = quan1 * usgmtm * unosub

after.tdpsc001.item.2:
before.layout:
select tiitm001.*
from tiitm001
where tiitm001.item = :tibom010.sitm
selectdo
unomea = tiitm001.cuni
endselect

Dear Itannous,

Oh, I see you are trying to make a report that shows the demand forecast of sub assemblies and finished goods that needs a certain item as base component. I think it would be better then if you customise the report of the session tibom1413m000, Print where used BOM components (multi level). In this session, Baan will already find out for you what sub assemblies and finished goods that needs the item as component. You then would just have to find the demand forecast for every sub assembly and FG that Baan finds. Much easier, ay?

Rgds,
Paul

I dont have the source code for this session and I need to add additional selection options

Dear Itannous,

Wow, now this is a tough case. I'm assuming you don't have dev license and are currently using SQL query then to find the item list and other data before throwing them to report engine. Am I right?

If you wanna keep the SQL query, one alternative is to use arrays on your report script. That is, array of sub assembly, array of sub assy demand forecast, array of FG, and array of FG demand forecast. But this is gonna be quite a complicated programming.
Would it be possible at all to move part of your query from your report script to your SQL query? For example, could you move the query for sub assy from report script to SQL query. That would eradicate the need for sub assy and sub assy demand forecast arrays.

I'm just wondering, do you have a base component used in 2 different sub assy, and later these 2 are assembled into one FG. If you do, this indeed is going to complicate things even more because you have to prevent this FG from appearing twice

One last thing, do you find the rough material requirements by critical item report unusable for your case? Just checking :)

Surely having development license would help a lot since you can just push all the sub assy and FG query right to prog script. Anybody else has another method :) ?

Rgds,
Paul

Paul P,

Looks like Itannous does have a development license or I don't think he would be able to write a report script (although I don't know this for fact). I believe the best course of action would be to write another session to meet the needs of this report. This is not a very hard thing to do. The hard part is already done because the current report script could be used as the program script for the new session (as you suggest) and probably even simplified. That would be my advice anyway, as I don't have source either and have come across similar problems trying to use a standard session to print a more complicated report than was originally written for it.

We do have a license, but I don't have source code for the session Pual metioned.

I have created the session script to gather the initial data and also included the report script below.

We did not want to set up these as critical items because we need to run this for various items and do not want it to effect our mrp/mps run.
The sub assembly is used once in a f.g. item. But it can be used in many f.goods.


Here is the session script:

declaration:

table ttdpsc001 | Purchase Contracts
table ttdpsc013 | Supplier Profile
table ttiitm001 | Items
table ttcmcs072 | Contract Analysis Codes
table ttcmcs002 | Currencies
table ttcmcs001 | Units
table ttcmcs070 | Contract Status Codes
table ttcmcs003 | Warehouses
table ttcmcs071 | Label Types
table ttcmcs046 | Languages
table ttcmcs074 | Receiving Pattern Descriptions
table ttccom001 | Employees
table ttibom010 | BOMs
extern domain ttyeno txta.txt
extern domain ttyeno txti.txt

extern domain tcitem item.f fixed
extern domain tcitem item.t fixed
extern domain tcsuno suno.f fixed
extern domain tcsuno suno.t fixed
extern domain tccono cont.f
extern domain tccono cont.t
extern domain tdpsc.pono pono.f
extern domain tdpsc.pono pono.t
extern domain ttyeno txta.txt
extern domain ttyeno txti.txt
extern domain tcdsca item.dsca
extern domain tcdsca anl1.dsca
extern domain tcdsca anl2.dsca
extern domain tcdsca cuqp.dsca
extern domain tcdsca cupp.dsca
extern domain tcdsca atyp.dsca
extern domain tcdsca btyp.dsca
extern domain tcdsca mtyp.dsca
extern domain tcdsca utyp.dsca
extern domain tcdsca aodl.dsca
extern domain tcdsca bodl.dsca
extern domain tcdsca modl.dsca
extern domain tcdsca uodl.dsca
extern domain tcdate date.f
extern domain tcdate date.t
extern domain timps.plnc plnc.f
extern domain timps.plnc plnc.t
extern domain timps.plvl plvl.f
extern domain timps.plvl plvl.t
extern domain timps.plvl livel
extern domain timps.plnc livec



|****************************** form section **********************************

form.1:
init.form:
get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif


|****************************** field section *********************************

field.item.f:
when.field.changes:
item.t = item.f

before.zoom:
tiitm001.item = item.f

field.item.t:
before.zoom:
tiitm001.item = item.t

field.suno.f:
when.field.changes:
suno.t = suno.f

field.cont.f:
when.field.changes:
cont.t = cont.f

field.pono.f:
when.field.changes:
pono.t = pono.f


field.plnc.f:
when.field.changes:

plnc.t = plnc.f


field.plvl.f:
when.field.changes:

plvl.t = plvl.f








|****************************** function section ******************************

functions:

function read.main.table()
{
select tdpsc001.*, tdpsc013.*, titem.dsca:item.dsca, tanl1.dsca:anl1.dsca,
tanl2.dsca:anl2.dsca, tcmcs002.*, tcuqp.dsca:cuqp.dsca,
tcupp.dsca:cupp.dsca, tatyp.dsca:atyp.dsca, tbtyp.dsca:btyp.dsca,
tmtyp.dsca:mtyp.dsca, tutyp.dsca:utyp.dsca, tcmcs070.*,
tcmcs003.*, taodl.dsca:aodl.dsca, tbodl.dsca:bodl.dsca,
tmodl.dsca:modl.dsca, tuodl.dsca:uodl.dsca, tcmcs046.*,
tcmcs074.*, tccom001.*, tibom010.*
from tdpsc001, tdpsc013, tiitm001 titem, tcmcs072 tanl1,
tcmcs072 tanl2, tcmcs002, tcmcs001 tcuqp, tcmcs001 tcupp,
tiitm001 tatyp, tiitm001 tbtyp, tiitm001 tmtyp, tiitm001 tutyp,
tcmcs070, tcmcs003, tcmcs071 taodl, tcmcs071 tbodl, tcmcs071 tmodl,
tcmcs071 tuodl, tcmcs046, tcmcs074, tccom001, tibom010
where tdpsc001._index3 inrange {:item.f, :suno.f, :cont.f,
:pono.f}
and {:item.t, :suno.t, :cont.t, :pono.t}
and tdpsc001.suno refers to tdpsc013
and tdpsc001.item refers to titem
and tdpsc001.anl1 refers to tanl1
and tdpsc001.anl2 refers to tanl2
and tdpsc001.ccur refers to tcmcs002
and tdpsc001.cuqp refers to tcuqp
and tdpsc001.cupp refers to tcupp
and tdpsc001.atyp refers to tatyp
and tdpsc001.btyp refers to tbtyp
and tdpsc001.mtyp refers to tmtyp
and tdpsc001.utyp refers to tutyp
and tdpsc001.stat refers to tcmcs070
and tdpsc001.cwar refers to tcmcs003
and tdpsc001.aodl refers to taodl
and tdpsc001.bodl refers to tbodl
and tdpsc001.modl refers to tmodl
and tdpsc001.uodl refers to tuodl
and tdpsc001.clan refers to tcmcs046
and tdpsc001.ship refers to tcmcs074
and tdpsc001.cpln refers to tccom001
and tibom010.sitm = tdpsc001.item
and tdpsc001.stat = "LV"
order by tdpsc001._index3
selectdo
rprt_send()
endselect
}

Dear Itannous,

Well, it's great then! All you have to do is

- Move the double query on tibom010 from report script to selectdo of your prog script. This will make prog script look for all sub assy and FG you want before sending to report engine

- Move the second query on tibom010 (finding FG) inside the first query on tibom010 (finding sub assy). This will make sure you find every combination of sub assy and FG used by the component

- Move the rprt_send inside the second query to tibom010 (finding FG). Hence you will be sending every time new combination of sub assy and FG is found (this is what you want, right?)

- As a result, every time a particular detail section, say detail.1, is printed, it will be on new component-sub assy-FG combo. You can then just leave demand forecast calc of the FG in the report script of that detail section.

After all of this, you'll have something very similar to rough material requirements by critical item that Baan provides. Are you sure you don't want to use that instead? Pls let know how it work out. Thanks :)

Rgds,
Paul

PS.
Ev, anything I miss that you might want to add? Thanks

Not sure if I followed your insturctions exactly. This is what I have added to the session script

I will send as an attachment

Dear Itannous,

I've just started to look at your main select. You selected the sub assy already in there. I saw that you already include tibom010 in the main select. Then all you have to do is have one select inside the main select to find the FG, not double select. BTW, you made sure that all your BOMs are 2 levels only (base comp->sub assy->FG), right? Also, you made sure that only base component will be passed to the main select, right? I don't know the table structure of the customised PSC module, hence I don't know whether these checks have been performed

Also pls remove the additional tiitm001 query on base component's inventory unit in selectdo of main select. You can perform this right in the main select since you already include tiitm001 of base component there.

It's now getting a bit too complicated, don't you think? What do you think about giving OmeLuuk's suggestion a try, ie get the source code of tibom1413m000 and start from there?

Rgds,
Paul

I split this thread per OmeLuuks request. The other piece of this post can be found here (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7560).

Mark