Hello,

1. When a service order is costed, integration transaction "service/approved for invoicing" debits account 10010.

2. When the service order is invoiced, the account 10010 is credited with the same amount by the integration transaction "sales-invoicing(service)/COGS(material)" so that the balance in account 10010 becomes zero.

But I find there is credit in account 10010 which is surprising. All the transactions have been posted in the financial year 2003 and if at all there is some amount it must be debit and not credit.

To know what happened, I want to find out for each credit entry in sales-invoicing(service)/COGS(material) transaction in account 10010 whether there is equivalent debit amount in transaction service/approved for invoicing(With the same amount). I want a list which doesn't satisfy this criteria in the financial year 2003 in all the 12 periods. I think service order number, financial year, financial period, transaction time, transaction origin, financial transaction, ledger account etc. are the fields which must be used for selection and the tables must be tfgld410, tfgld417 and tfgld418. I am a functional guy and I know a bit of SQl but I find it difficult to combine the three tables and write an SQL query. Also I understand that the query must run in a loop(like PL/SQL) so that it brings the list in the entire year. Can someone help me please? If I get the SQL code, I can cut/paste/modify in the easy sql module and get a report. Thanks in advance.

(I have already verified that no other account is involved for these two integration transactions and also the account 10010 has no other entry than for these integration transactions which makes the query to be simple.)

Thanks in advance,

srikarthy

This is a query our finance people use all the time:

select *
from ttfgld106100 a,
ttfgld418100 b,
ttfgld011100 c,
ttfgld008100 d
where a.t$otyp = b.t$ttyp (+)
and a.t$odoc = b.t$docn (+)
and a.t$olin = b.t$lino (+)
and a.t$ocmp = b.t$fcom (+)
and a.t$otyp = c.t$ttyp
and a.t$leac = d.t$leac
and b.t$tror = 15
and a.t$fyer = 2003;

(tror = Transaction Origin, 15 = Service Order)

I tried to add a little extra that I believe you were trying to filter for. You'll need to supply the columns you want instead of the "*" that I placed in the "select". This is an Oracle SQL query, so you will need to change the company numbers to what you're using. The "(+)" syntax is for an outer join because not all records from 418 are found in 106 especially if you want to find all records that have a specific ledger account. Hope this helps.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Hi Gilbert,

Thanks a lot. I am using the text manager in Easy SQL and accordingly I have modified the query you have supplied.

select tfgld418.orno, tfgld418.docn
from tfgld106,
tfgld418,
tfgld011,
tfgld008
where tfgld106.otyp = tfgld418.ttyp |(+)
and tfgld106.odoc = tfgld418.docn |(+)
and tfgld106.olin = tfgld418.lino |(+)
and tfgld106.ocmp = tfgld418.fcom |(+)
and tfgld106.otyp = tfgld011.ttyp
and tfgld106.leac = tfgld008.leac
and tfgld418.tror = 15
and tfgld418.fitr = 4
and tfgld106.fyer = 2003
order by tfgld418.orno

This query gives me all the records that are in tfgld106 and as you have mentioned, I need the records which are not in tfgld106. The outer join is not working in easy sql. I found that you have mentioned earlier to some other query that 'refers to' or '...empty...' can be used in Baan SQL. Could you please modify the above one for outer joins, if it is simple?

Thanks,
srikarthy

This query is Oracle specific and the syntax of the outer join is not applicable to Easy SQL. In Baan the outer join can be done using the "refers to", but can only be used were a primary key matches the primary key of the other table. The primary keys for both tables do not match up in this case. I thought you were seeking an Oracle type of solution, however this could be done programatically in a Baan print session if you have access to the Baan development tools.


Gilbert

Try the not exists command. You have tfgld008 but its not being used in you select. You may need to make a few changes but this should help some.

select tfgld418.orno, tfgld418.docn
from
tfgld418,
tfgld011,
tfgld008
where tfgld418.tror = 15
and tfgld418.fitr = 4
and tfgld418.ttyp = tfgld011.ttyp
and not exists
(select *
from tfgld106
where
tfgld106.otyp = tfgld418.ttyp |(+)
and tfgld106.odoc = tfgld418.docn |(+)
and tfgld106.olin = tfgld418.lino |(+)
and tfgld106.ocmp = tfgld418.fcom |(+)
and tfgld106.otyp = tfgld011.ttyp
and tfgld106.leac = tfgld008.leac
and tfgld106.fyer = 2003)
order by tfgld418.orno

Hi Gilbert, Tommy,

Thank you very much. The queries were highly helpful.

Thanks,
srikarthy

hi,

table1 : tdkpm014
fields : salesReq,
prod

table2: tdkpm010
fields : salesReq,
salesOrder,
prod

table3: tdkpm015
fields : salesOrder,
lotid

tdkpm014 has size : 700, 900, 1000
But tdkpm015 has item.
tdkpm015.item(8;4) = tdkpm014.size
kn421800700xxxxn, kn421900800xxxxn, kn421800900xxxxn, kn421801000xxxxn.



i want to write a query to display size 800 from tdkpm015 which is not exist in tdkpm014.

I tried this query in report script:

detail.2:
before.layout:

SELECT tdkpm010.*, tdkpm015.*
FROM tdkpm010, tdkpm015
WHERE tdkpm010.salesOrder = tdkpm015.salesOrder
AND NOT EXISTS (SELECT * FROM tdkpm014
WHERE tdkpm014.salesReq = tdkpm010.salesReq
AND tdkpm014.prod = tdkpm010.prod)
selectdo

endselect

this method is not working.
Can pls help me on this?

Thanks

Regards
Anusha