Hi Friends,

I'm developing a report to get a breakdown of cost of sales using tdsls051 and ticpr300 tables.
I can't get details of more than one cost component per item .


this is the code which i wrote.

I would greatly appreciate if one of you experts can advice me on how to correct this.

function get.cost.values()
{

select max(ticpr300.indt):max.date
from ticpr300
where ticpr300._index1 inrange {:item.f} and {:item.t}
and ticpr300.item = :tdsls051.item
and ticpr300.cntr = :tdsls051.cntr
and ticpr300.indt between :trdt.f and :trdt.t
selectdo

read.cost.price()

endselect
}

function read.cost.price()
{
comp = " "
amnt = 0.00

select ticpr300.cpcp, ticpr300.amnt
from ticpr300
where ticpr300._index1 inrange {:item.f} and {:item.t}
and ticpr300.item = :tdsls051.item
and ticpr300.cntr = :tdsls051.cntr
and ticpr300.indt = :max.date
as set with 1 rows
selectdo
comp = ticpr300.cpcp
amnt = ticpr300.amnt
selectempty
comp = " "
amnt = 0.00
endselect

}

Thanks in advance

Hi

You have given clause "as set with 1 rows" so is it right as per what you want ?

I think following should work for you in second select statement :

function read.cost.price()
{
comp = " "
amnt = 0.00

select ticpr300.cpcp, ticpr300.amnt
from ticpr300
where ticpr300._index1 = {:tdsls051.item,: tdsls051.cntr, :max.date}
selectdo
comp = ticpr300.cpcp
amnt = ticpr300.amnt
selectempty
comp = " "
amnt = 0.00
endselect
}

Thanks & Regards

Hi Chandra,

Thanks a lot for your reply.

I've changed the 2nd select statement as per your suggession;

But the real problem I'm having is that once the program selects one Item it does't give all Cost Components of that particular Item. (it only gives the first cost component).

I need the program to check all cost components once it read a item. For this I think I have to use a WHILE loop.

I'd really appreciate if you could help me on this.

Best Regards

Hi

I think there is some problem with your first statement, try writing it this way :

function get.cost.values()
{
select max(ticpr300.indt):max.date, ticpr300.cpcp
from ticpr300
where ticpr300._index1 = {:tdsls051.item,:tdsls051.cntr}
and ticpr300.indt between :trdt.f and :trdt.t
group by ticpr300.cpcp
selectdo
read.cost.price()
endselect
}

This should give you max date (ticpr300.indt) for the said item, container and given range of date. Also no need to use item.f and item.t when you have specific item (tdsls051.item).


Hope this will work for you

Thanks and Regards

Hi ,

I was able to get max date and correct price of one cost component (ticpr300.cpcp) per one item. But then the script go to the next item without searching for other cost omponents of selected item. I think this can be done using a "loop" which i'm not very much familiar. Please suggest a way to sort this out.

Thanks in advance

Hi

Check if this works :

domain tccpcp cost.comp

function get.cost.value()
{
cost.comp = ""
select ticpr300.cpcp, ticpr300.indt, ticpr300.amnt
from ticpr300
where ticpr300._index1 = {:tdsls051.item,:tdsls051.cntr}
and ticpr300.indt between :trdt.f and :trdt.t
order by ticpr300.cpcp asc, ticpr300.indt desc
selectdo
If cost.comp <> ticpr300.cpcp then
cost.comp = ticpr300.cpcp
< should get cost price of each component with max date for
first cost component>
< Also no need to call read.cost.price()>
endif
endselect
}


Thanks & Regards