XAF auditfile transaction nr in meerdere journals - hoe uniek te koppelen aan transaction lines

Vragen
,
1

Ik loop bij diverse auditfiles tegen de volgende uitdaging aan; een transaction nr (<transaction>/<nr>) is gespreid over twee (of meer) journals. Bij het laden van de auditfile naar SQL worden de journals, transactions en transaction lines tabellen separaat geladen.

Mijn doel is om de transaction lines aan de journals te koppelen om te zien over welke journals (dagboeken) een transactie loopt. Als ik echter deze JOIN maak via de transactions tabel levert dit duplicate data op omdat sommige transactions over meer dan 1 journal lopen.

Zie snip hieronder voor een voorbeeld. Transaction nr 78355 loopt over jrn_id 7 en 8. Sommige trLine nr’s gaan via 7 en sommige via 8, maar deze koppeling kan ik nu niet maken.

Is er een manier voor om dit wel te doen? Bijvoorbeeld bij het inlezen een unieke combinatie van journal_id en transaction nr toe te voegen d.m.v. CONCAT?

Resultaat van de transactions tabel (bovenste) en transaction lines (onderste) in SQL:

image
image1421×414 70.5 KB

Dit is de auditfile voor dezelfde transactie:
Journal 7:
image

Journal 8:
image

2

Ik kan niet doorgronden wat de vraag is.

Elke dagboek in de meeste administratieve pakketten heeft een eigen nummering van de documenten / transacties. Hetzelfde transactienummer mag in verschillende dagboeken opnieuw gebruikt worden, maar er is geen regel die voorschrijft dat hetzelfde nummer op hetzelfde betrekking dient te hebben. Elke transactie valt weer uiteen in 0, 1 of meer regels.

De natuurlijke sleutel is dus:

  • Uniek kenmerk van administratie.
  • Uniek kenmerk van dagboek binnen administratie, bijvoorbeeld code.
  • Uniek kenmerk van transactie binnen dagboek, bijvoorbeeld nummer.
  • Uniek kenmerk van regel binnen transactie, bijvoorbeeld nummer.

Deze logica wordt gezien het universele formaat van XAF ook in de XAF-formaten gevolgd.

Wat is het uiteindelijke gewenste doel cq. wat is de bedrijfsmatige setting van het probleem dat geanalyseerd moet worden.

3

Bedankt voor je snelle reactie.

De natuurlijke sleutel en kenmerken ben ik mee bekend.

Het doel is het volgende: het analyseren van alle transaction lines om inzichtelijk te maken over welke dagboeken deze lopen (m.a.w. welke dagboeken worden geboekt voor bepaalde soort transacties).

Als ik ‘handmatig’ de XAF in PowerBI open en dan downdrill kan ik een tabel/query creëren waarbij de transaction lines ook het jrn_id bevatten zodat de analyse mogelijk is zoals hierboven beschreven: wat zijn bijv. veel voorkomende transacties in een bepaald dagboek, worden de dagboeken gebruikt zoals ze bedoeld zijn.

Echter doordat bij het laden in SQL Server de journals, transactions en transaction lines in separate tabellen komen kan ik deze link niet meer leggen.

4

Hierbij nog een snip uit PowerBI ter verduidelijking (betreft zelfde auditfile en zelfde transactie nr):

image
image1203×437 55 KB

5

Het lukt me niet de vraag te begrijpen.

In een XAF 3.2 auditfile staat bijvoorbeeld:

<transactions>
...
  <journal>
      <jrnID>60</jrnID>
      <desc>Inkoopboek</desc>
      <jrnTp>P</jrnTp>
      <offsetAccID>1600</offsetAccID>
      <transaction>
          <nr>15600001</nr>
          <desc>nggncngc</desc>
          <periodNumber>2</periodNumber>
          <trDt>2015-02-04</trDt>
          <sourceID>Electronic Invoice</sourceID>
          <trLine>
              <nr>0:1</nr>
              <accID>1600</accID>
              <docRef />

Hier zullen bij de gekozen normalisatie van XML naar relationeel model drie tabellen uit rollen:

De join kan gemaakt worden als volgt:

  • TransctionLines.TRANSACTION_NR met Transactions.NR
  • Transactions.JOURNAL_JRNID met TransactionJournals.JRNID
6

De normalisatie en het maken van de 3 tabellen begrijp ik, de joins daartussen ook, die worden ook reeds gebruikt.

Het probleem dat daarbij ontstaat is het volgende, als er een transaction nr 10 is, met transaction lines 1,2,3,4,5,6 waarbij transaction line 1,2,3 in journal 60 vallen en 4,5 en 6 in journal 70.

Voor analyses op de transaction lines willen we inzichtelijk kunnen maken onder welke journal die transaction lines geboekt zijn.
Momenteel is dat niet mogelijk met deze joins, omdat transaction nr 10 (in dit voorbeeld) de identifier is tussen transactionLines en Transactions, maar deze komt 2x voor in de transactions tabel omdat er 2 journalid’s bij horen. Je kunt de koppeling tussen transaction line 1,2,3 en journal 60 dus niet maken nu. (in het Power BI voorbeeld is dat wel aangebracht op transactionline niveau).

Kort gezegd komt het erop neer dat we per transactionline willen zien welke journal id erbij hoort.

7

Als ik het goed interpreteer, dan bedoel je dat er twee transacties zijn, beiden met hetzelfde nummer. En dat ieder van die transacties in een ander dagboek zit.

En dat de samengestelde natuurlijke sleutel op transactieregel geen goed pad biedt om het dagboek te bepalen.

Klopt dat?

Het lijkt er namelijk op dat journal ID verdwenen is uit het automatisch samenstellen van de transactieregelkolommen.

8

Ja dat klopt inderdaad, als je xaf zelf opent in komt het transactie nr 2x voor, 1x per dagboek.

Feitelijk is dit 1 transactie met meerdere transactie regels die geboekt wordt over 2 dagboeken (bijvoorbeeld inkoop en voorraad). Dat is ook te herkennen aan het feit dat de transactie regels voor die betreffende transactie doornummeren, dus lijn 1,2,3 in dagboek A en lijn 4,5,6 in dagboek B als voorbeeld.

journal ID ontbreekt inderdaad in de transactie regel kolommen.

9

Vanaf release 22.0.14 die de komende twee weken in alle producten verwerkt zal worden zullen er extra velden beschikbaar zijn voor de dagboekinformatie uit XAF 3.0, XAF 3.1 en XAF 3.2 op:

  • TransactionLines,
  • TransactionLineVats.