Hi Buddies,

I want to develop report which fetches data from different tables as follows,
for a given Business Partner(Supplier/Customer) I want to print data for PO as well as SO in format given below,

BP PO POItem PO Date SO SOItem SO Date

ZZZ01 123 MAT01 1 Jan 07 987 MAT02 2 Jan 07
456 MAT02 3 Jan 07

The sale & purchase data has to be printed besides each other for a given BP(Business Partner). How do i do it?

Ive thought of a solution wherein i will create a temporary table with structure as follows,

Business Partner
Sale Order No
Purchase Order No
index --> business partner

I will make two runs thro Purchase & Sales tables. In first run i will populate all PO found, keeping SO as blank. With reference to above example my table will have 2 records ,PO Nos 123 & 456. For SO , the moment I find order no 987 i will search for the first record in temporary table where the SO field is blank and populate this field with the SO(i.e. 987)
In case if i dont find any such record i will insert a new record with PO as blank and SO as the newly found SO

In the second run , i will parse this temporary tables which will have collection of all SO & PO , fetch their respective data and print the report.

I think this approach should work, but is their any better solution? A solution that will eliminate the need for temporary tables or arrays and the extra second run.

Cheers!

You can think about somethink like:

sql.so = sql.parse("select * from so where bp = :1")
sql.po = sql.parse("select * from po where bp = :1")

select * from BP
selectdo
sql.where.bind(sql.so, 1, bp)
sql.exec(sql.so)
so.err = 0

sql.where.bind(sql.po, 1, bp)
sql.exec(sql.po)
po.err = 0

repeat
if so.err = 0 then
so.err = sql.fetch(sql.so)
if so.err <>0 then
clear.so.fields()

if po.err = 0 then
po.err = sql.fetch(sql.po)
if po.err <>0 then
clear.po.fields()
until so.err <> 0 and po.err <> 0
endselect

As you see you have to do a lot of coding yourself, but I assume you can manage that. I only will give you the general idea not the details.

Dynamic SQL is the trick that you will use I think.

Best regards,
Dick

hey Dick,

Thats a brilliant suggestion. I have never tried dynamic sql before, and would love to explore it now. Thnx for your inputs. I will get back with an update soon.

Cya.