tables relationship

[Former Member]

Hi all,

I need to join between invoice item table and contract condition table

(tables : Virainvitem and Vicdcond)

Any suggestion will be appreciate.

Also any RE relationship table will be helpful

[Former Member]

Hi,

I m facing the same issue.

Did you solve it?

Franz's hints are helpful, but i m not agree with :

VIRAINVITEM-REFITEMNO = VIRADOCITEM-REFITEMNO

So i m missing a link.

Regards,

Dev

[Former Member]

Unfortunately no.

[franz_posch]

Hi Dev,

I checked this with a simple example where it worked fine.

VIRAINVITEM

ITEMNO GROSSAMOUNT REFITEMNO

0000000001 119,00 0000000001

0000000002 178,50 0000000003

0000000003 357,00 0000000005

0000000004 368,90 0000000007

VIRADOCITEM

ITEMNO REFITEMNO AMOUNT

0000000001 0000000001 119,00 <<<

0000000002 0000000002 100,00

0000000003 0000000003 178,50 <<<

0000000004 0000000004 150,00

0000000005 0000000005 357,00 <<<

0000000006 0000000006 300,00

0000000007 0000000007 368,90 <<<

0000000008 0000000008 310,00

0000000009 0000000009 19,00

0000000010 0000000010 28,50

0000000011 0000000011 57,00

0000000012 0000000012 58,90

VIRAINVITEM-REFITEMNO = VIRADOCITEM-REFITEMNO

I have to admit that this was really a simple example but here it holds.

Do you use summarization of invoice items or adapt the invoice items somehow else?

Regards, Franz

[Former Member]

Franz,

Thanks X 10 for your response

I'll check it and come back with the results

[Former Member]

Hi Franz,

Unfortunately this is not the situation in my system

In table VIRAINVITEM the 3 fields are not unique

E.g.

Itemno Gross Amt refitemno

4 232 5

11 232 4

5 232 1

2 232 1

1 251.73 1

3 580 1

1 580 1

1 580 1

1 582.5 1

2 602.96 1

[franz_posch]

Hi,

I am afraid that it is not quite easy to get a proper join between these tables.

Please check table VICDCFPAY.

If you create invoices for already posted FI documents then you have DOCITEMID filled in VIRAINVITEM.

VICDCFPAY entry also refers to RE document VIRADOC and VIRADOCITEM and these tables link to the FI document line item.

Please try if the following link works to find the right VIRADOCITEM entry.

VIRAINVITEM-OBJKEY = VIRADOC-REFDOCID

VIRAINVITEM-REFITEMNO = VIRADOCITEM-REFITEMNO

VIRADOCITEM-REFGUID = VICDCFPAY-CFPAYGUID to get the cash flow entry.

VICDCFPAY-CONDGUID is the link to VICDCOND.

But observe that the CONDGUIDs that are stored in VICDCFPAY items may be logically compressed!

If you have several conditions that have no changes relevant for posting always the CONDGUID of the initial one is stored in the cash flow.

So as you see it may be difficult to get this link and you have to check carefully if it works and try out with examples.

But I hope nevertheless that my hints are helpful for you.

Regards, Franz

[Former Member]

Hi Franz,

Sorry about my late answer but I still have misunderstanding your proposal .

My questions are:

1. Why the table VIRADOC is necessary?

2. The relation between VIRAINVITEM and VIRADOCITEM is one to many.

I need one to one relationship to get any info from VICDCOND.

Any Idea how to get it?

Thanks in advance

Itzik