We urgently need to remove data from tdpur045 table to improve performance in Maintain Purchase Invoices session. We propose to do this by deleting order data using Process Delivered Purchase Orders.

I have been asked the question by Finance with regards to the traceability of Purchase Invoices after this session has been run. The link between the Purchase Invoice and the Purchase Order is held in tdpur046 and this data will be removed following running of Process Delivered PO session.

In summary I need some way of connecting the Purchase Invoice to the Purchase Order without using data held in tdpur046. I have investigated using data in tfgld410 but in our system setup individual records for Purchase Invoices do not appear as they do for Sales Invoices.

Are there any other tf tables which would give me this info ?

If all else fails I may have to consider manually dumping tdpur045 and tdpur046 across to my archive company on a monthly basis.

On the performance issue does anybody else suffer with hideous performance in Maintain Purchase Invoices session tfacp1110s000 in 4c3. The problem is related to the packing slip field where in validating what you enter in this field the session looks at every single record in tdpur045 and can take up to a minute before giving a response.

Any assistance would be appreciated.

Thanks
Neal Matthews
MSS Project Leader

As far as I know you cannot remove tdpur045 and tdpur046 until your purchase invoice has been matched and approved. After that there shouldn't be any problem. Just check that your GRN accounts balance as well otherwise you may have problems there.

As for tracebility, if your Purchase Order History is created then that should be sufficient for tracing purchase order information.

I think that Finance are thinking more of the sort of queries they may get from suppliers or internally regarding old Purchase Invoices which we know have been matched and approved but we need to find out further details such as the description from the reference to the Purchase Invoice number.

Purchase order history is OK if you have the Purchase Order number but we specifically need to somehow link our Purchase Invoice number to the Purchase Order number without reference to tdpur046 for data which has been removed from the aforementioned table.

Thanks

Neal Matthews

If the purchase invoice is of the type that is matched with purchase orders then the purchase order number will be stored in tfacp200 which should be sufficient to provide a link.

Fraid not.

We tend to match using the packing slip number on the main Purchase Invoice screen and the Purchase Order is not input at this stage. Then the lines are matched to the Purchase Order using the Match Receipts subsession. This data is then stored in tdpur046.

Regards
Neal Matthews

If there is no order number in tfacp200 then you will have to keep your histiry or you loose tracebility. I can't think of any other way to do it.

Thanks for trying I'll probably raise a case with BGS just in case they have any bright ideas but I think that I'll probably need to keep the contents of tdpur045 and tdpur046.

Regards

Neal Matthews

It seems that you must have a lot of records in that table - if this is the real cause?! - How many records are there? Is it going back a few years? Typically it's recommended to keep this data for about a year. Do you just need to keep the old data for query purposes or just traceability? If just traceability can you just back up the table with a different name or make a datawarehouse?

have you thought about archiving (setting up archiving company in Baan) or keeping a separate table with the data from before a certain date? Are you using Oracle8? I heard it has some features to do this.

Have you run trace / log files to confirm this is the cause?

Let us know what BGS come up with.

Hi,

We have approx 200000 records in tdpur045 which go back to September 1999. The trace I ran on tfacp1110s000 session suggests that this is the cause of our performance problems. Although to me 200000 doesn't seem a particular huge amount of records.

The old data ideally needs to accessable for user queries. We do have archive companies but the Process Delivered Purchase Orders session removes the data as opposed to archiving it.

I tend to agree that the best bet may be to copy the data to our archive company or another table.

Will let you know what BGS think and by the way we run on Informix.

Thanks for your response.

Regards
Neal Matthews

BGS couldn't come up with any bright ideas either although they didn't like me dumping the contents of tdpur046 into another copy of tdpur046 so I'll have to create a seperate table for this one.

Thanks

Neal Matthews
MSS Project Leader

BaanTEXAS (http://communities.msn.com/BaanTX)

I am currently running checks in our test box to do the same thing. I know po history table will keep the invoice information.
The tfacp table has payment history. I'm brainstorming of course since you use pack slip reference, but wonder if tfgld418 retains the information. At somepoint acct has to realize that if it is over a certain number of months old, they have to get up and pull copy of check, po, receiver or whatever. I recently struggled trying to link ppv from gl to purchase order. It's a nightmare using basic Baan query. Result is a different doc number from receipt, transaction type is not parsed in tfgld410, I finally gave up trying to build baan report spec and told them to use g/l ledger report with supplier and doc and doc line number for reference, then use A/P variance report for completeness (i.e., receipt date not always within same period for receipts and invoice payment), then look to link to finance table from logistics. We're going to download tdpur045 to ascii file, zip it, then purge data thru the process session.
Clark

Thanks for that;

I eventually put this one to bed by convincing Finance that for the amount of queries that they would have they should revert to the hard copy.

In the end we were really overtaken by our performance problems and the number of records in tdpur045.

Regards
Neal Matthews

Workaround could be creating a new customised table and storing the links between po/pack slip/supplier/reno/invoice nos in it. Seperate reports will have to be created to satisfy finance but this can help ....have not tried it.

Whatever you do letme know as we currently have 110000 records and will soon be heading ur way ..

Finance finally agreed to revert to the hard copy.

If you have 110000 records in tdpur045 what sort of performance do you get out of the Packing Slip field in Maintain Purchase Invoices ?

Regards
Neal Matthews

We do not use the packing slip field in Maintain Purchase Invoices.

We do have a customised table where we enter localised etails not captured in standard baan or the indian localisation version. In that table we have indexes on pack slip and supplier, other on receipt nos .... This table keeps a link between pack slip and supplier/recipt nos.

Thanks for the info.

It looks as if we may need to look at customisation to improve our performance.

Regards
Neal