I need your favor for linking tdpur045/tdpur046 and tfacp200 tables. I had assigned one project to restore Return Purchase Order Data which has been permanently lost during archiving.

Tables to be recovered:

tdpur040 – Purchase Order Header
tdpur041 – Purchase Order Lines
tdpur045 – Receipts
tdpur046 – Purchase Invoices

Available Data:

tdpur050 – Purchase Order History
tdpur051 – Purchase Order Line History
tdinv700 – Inventory Transactions by Item
tfacp200 – Open Items (Purchase Invoices & Payments)

I want to link tfacp200 with tdpur045 for getting below data.

tdpur045.date=tfacp200.recd (Receipt Date)
tdpur045.iamt=tfacp200.amti (Invoiced Amount )
tdpur045.dino=tfacp200.disp (Packing Slip Number)


I tried to link the table as follows:

tfacp200.orno =tdpur045.orno
tfacp200.suno=tdpur045.suno
tfacp200.tpay =tfacp.tpay.invoice
tfacp200.appr=tfacp.matc.pur

but there is no fields like Position, Sequence in tfacp200 (tdpur045.pono, tdpur045.srnb). So it is difficult to link the tables. How this problem can be solve?

Generally we can link tdpur045 by selecting from tfacp200 if there is a matching entry for the purchase order, packing slip and receipt date.
But here we have to resore packing slip and receipt date from tfacp200 itself. Also If Invoice is linked with more than 1 order than purchase order filed of tfacp200 may be empty.


May be following logic to get the required data works:

tfacp200 table contains purchase order number. Equate tdpur045.orno with tfacp200 purchase order and get the transaction type and document number and using this combination make a query on tfgld410 and from tfgld410 get the purchase order, purchase order line#. In case if there are no records available in tfgld410, check in tfgld418.

But when I checked the some test data, the transaction type and document number of tfacp200/tdpur046 does not match with tfgld410/tfgld418.

Could we use tfgld410/tfgld418 tables combination with tfacp200 table since tfgld410 contains Order No and Position number, while tfgld418 contains the sequence number also?

Note: ILC is not implemented.

Hello Manish,
Before linking the tables, do you have any recent Backup of your Baan data that you could perhaps restore to a test environment?

sk

"But when I checked the some test data, the transaction type and document number of tfacp200/tdpur046 does not match with tfgld410/tfgld418"

What were the Transaction Origin and Financial Transaction values for the records you searched? I am sure it must have been Purchase & Receipts respectively...
tfgld410 and 418 might only contain receipts and not invoices that you want to link with pur046 and acp200..

sk

Thanks for reply.
You are right tfgld410 and tfgld418 might only contain receipts and not invoices.
We don't have any Backup of Baan data.
Is there any alternate solution for restoring tdpur046 data from tfacp200?

Manish,
May we know what all tables got archived?

sk

Dear Sukesh,

During archive some Return Purchase Order Data has been permanently lost. This includes around 240 orders, including 4600 order lines.

Data to be restored:
tdpur040 – Purchase Order Header
tdpur041 – Purchase Order Lines
tdpur045 – Receipts
tdpur046 – Purchase Invoices

Available Data:

tdpur050 – Purchase Order History
tdpur051 – Purchase Order Line History
tdinv700 – Inventory Transactions by Item
tfacp200 – Open Items (Purchase Invoices & Payments)

tdpur040,tdpur041,tdpur045 can be restored thru History tables.
But I have no idea how to linkage tdpur046 with tfacp200 to restore tdpur046.

Note: ILC is not implemented.

Hi,
It doesnt look like you could link the invoice detail in acp200 to a purchase order line in pur45 or pur51. The best course of action, according to me, would be to link the acp200 to pur45 by purchase order. Have that(records from acp200 that matches the missing orders in pur45) in a spreadsheet and then compare one or two orders in that spreadsheet with the tdpur45 table to work out a possible join...

Under the given circumstance, this is what i could think of ...I hope someone else has a brighter idea..

sk

Hi Sukesh,
Thanks a lot.
In table tfacp200, many records didn’t get purchase orders. It may be possible that invoice linked with more than 1 order.

I am sorry to hear that you have lost some much data. There is limited things you can so to get back tdpur46 if there is no archived.
In order to get back the data, you have to do a lot of manual work, a lot of GTM and cross checking with actual documents from supplier. tdpur46 is the only bridge that linked acp200 to tdpur45 and in your situation it was broken.

In Baan V you have more choices but not in Baan IV.

Below were the information you can get from your existing tables:

In acp200, every invoice that have the status Matched or Approved will have a record in tdpur46. The approved amount is not that useful if the invoice was linked to more than one PO but can be used to double check with the total PO amount you analyzed.

In tdpur45, every PO that have the status Matched, Partial and Approved will have a record in tdpur46. The field invoice qty and invoice amount will show the total invoice amount matched to the PO. This means that there can be more than one invoice linked to the PO.

The two tables above can only tell you that a particular PO and a particular invoice should be in tdpur46. You have to manually figure out which invoice linked to which PO based on documents you have.

Hi,
I wish to make a correction to my previous post regarding acp200.

I said that all invoice with the status matched and approved have a record in tdpur46 table. This is not fully correct.
an
What is correct is all invoice with status Matched will have a record in tdpur46.

Invoice with status Approved in acp200 does not neccessary have a record in
tdpur46. The reason was that the user can approve an invoice without matching to a PO. However you can verify whether the invoice that have status Approved was linked to a PO or not. In acp101 table, if you find the same invoice as in acp200 and the amount was similar then the invoice was approved without matching to a PO. However if the amount in acp101 table was less than the invoice amount in acp200, then part of the invoice amount not found in acp101 was partially linked to one or more PO and hence the invoice should be found in tdpur46.