Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Finance, Invoicing and Integration

User login

Frontpage Sponsor

Main

Poll
When will you move your ERP to the cloud?
We are on the cloud already!
33%
Next year
0%
from 2-3 years
22%
from 4-5 years
0%
Never!
44%
Total votes: 9

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 5th April 2007, 14:54
manish_patel's Avatar
manish_patel manish_patel is offline
Senior Member
 
Join Date: Apr 2007
Location: Surat-->Bangalore, INDIA
Posts: 183
manish_patel is on a distinguished road
Baan: Baan IVc4, Baan 5.0 b - DB: Oracle 11g R2 - OS: HP-UX 11.31
Question Restoring tdpur046 records
Baan: Baan IVc4

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.

Last edited by manish_patel : 5th April 2007 at 15:07. Reason: Note: ILC is not implemented.
Reply With Quote
  #2  
Old 7th April 2007, 09:00
sukesh75's Avatar
sukesh75 sukesh75 is offline
Guru
 
Join Date: Dec 2002
Posts: 1,031
sukesh75 is on a distinguished road
Baan: Baan 4c - DB: SQL Server 2000 - OS: Windows 2003
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
Reply With Quote
  #3  
Old 7th April 2007, 09:17
sukesh75's Avatar
sukesh75 sukesh75 is offline
Guru
 
Join Date: Dec 2002
Posts: 1,031
sukesh75 is on a distinguished road
Baan: Baan 4c - DB: SQL Server 2000 - OS: Windows 2003
Arrow Forgot to add..

"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
Reply With Quote
  #4  
Old 7th April 2007, 10:44
manish_patel's Avatar
manish_patel manish_patel is offline
Senior Member
 
Join Date: Apr 2007
Location: Surat-->Bangalore, INDIA
Posts: 183
manish_patel is on a distinguished road
Baan: Baan IVc4, Baan 5.0 b - DB: Oracle 11g R2 - OS: HP-UX 11.31
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?
Reply With Quote
  #5  
Old 7th April 2007, 11:43
sukesh75's Avatar
sukesh75 sukesh75 is offline
Guru
 
Join Date: Dec 2002
Posts: 1,031
sukesh75 is on a distinguished road
Baan: Baan 4c - DB: SQL Server 2000 - OS: Windows 2003
Manish,
May we know what all tables got archived?

sk
Reply With Quote
  #6  
Old 7th April 2007, 12:32
manish_patel's Avatar
manish_patel manish_patel is offline
Senior Member
 
Join Date: Apr 2007
Location: Surat-->Bangalore, INDIA
Posts: 183
manish_patel is on a distinguished road
Baan: Baan IVc4, Baan 5.0 b - DB: Oracle 11g R2 - OS: HP-UX 11.31
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.
Reply With Quote
Sponsored Links
  #7  
Old 7th April 2007, 16:23
sukesh75's Avatar
sukesh75 sukesh75 is offline
Guru
 
Join Date: Dec 2002
Posts: 1,031
sukesh75 is on a distinguished road
Baan: Baan 4c - DB: SQL Server 2000 - OS: Windows 2003
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
Reply With Quote
  #8  
Old 7th April 2007, 16:57
manish_patel's Avatar
manish_patel manish_patel is offline
Senior Member
 
Join Date: Apr 2007
Location: Surat-->Bangalore, INDIA
Posts: 183
manish_patel is on a distinguished road
Baan: Baan IVc4, Baan 5.0 b - DB: Oracle 11g R2 - OS: HP-UX 11.31
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.
Reply With Quote
  #9  
Old 24th April 2007, 09:29
mtho33 mtho33 is offline
Junior Member
 
Join Date: Apr 2007
Posts: 12
mtho33 is on a distinguished road
Baan: Baan Vc - DB: Oracle - OS: Winn 2003
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.
Reply With Quote
  #10  
Old 25th April 2007, 04:50
mtho33 mtho33 is offline
Junior Member
 
Join Date: Apr 2007
Posts: 12
mtho33 is on a distinguished road
Baan: Baan Vc - DB: Oracle - OS: Winn 2003
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.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Select distinct records thru query extension?? arajasek Tools Development 5 25th August 2010 08:24
Insert/Writing records to DB very slow tnzabo Tools Development 9 13th October 2006 19:03
Selecting Records for First Forms Post tslaton Tools Development 1 7th April 2006 00:18
Losed tfgld418 records - Urgent Help Please mprakash Finance, Invoicing and Integration 13 25th September 2004 10:26


All times are GMT +2. The time now is 19:15.


©2001-2016 - Baanboard.com - Baanforums.com