How to build Hana Model on Payroll Posting Table

SumithaRD · ‎2024 Oct 24

To overcome this issue HANA models can be built on Payroll posting tables as described in below steps:

It would be right to start this model from the posting table in Finance so the Finance team can use the same model to understand the actual postings into different GL accounts
Create a projection of BKPF table from Finance system the AWSYS = <HANA Fin Sys> and the BKTXT = ‘PP’ posted documents
Create a projection of the Document header table PPDHD from the HCM system
Join BKPF and PPDHD with join on

BKRF.BLART = PPDHD.BLART

BKPF.XBLNR = PPDHD.XBLNR

BKPF.BUKRS = PPDHD.BUKRS

BKPF.CPUDT = PPDHD.DOCNUM

Get the line items from PPDIT for the Document:

JOIN PPDHD with PPDIT with condition PPDHD.DOCNUM = PPDIT.DOCNUM

Fetch DOCLIN, HKONT, KOSTL, PRCTR with 1: n cardinality

Fetch RUNID and LINUM from PPDIX table. Create projection on PPDIX with below condition

JOIN PPDIT with above projection with condition on DOCNUM and DOCLIN and fetch the RUNID and LINUM

Create projection on PPOIX with Sum on BETRG by PERNR, SEQNR

Get the sum of BETRG by PERNR SEQNR in aggregation

JOIN PPDIT output with PPOIX

To check reversals PPOPX - join RUNID TSLIN of PPOPX with RUNID LINUM from PPDIT

If record exists in PPOPX then again query back the posting lines reversed from PPOIX again join with PPOIX from Join to fetch RUNID

Filter condition on PPOIX_SID

When ACTISIGN is P subtract the records else add the records

Now to fetch Org details join with Payroll cluster table WPBP with RUNID and SEQNR

Join the PERNR Details with Org Infotype to fetch further details

Now the model can be used to find the total spend across the employee groups and organizational objects. In the Semantics layer we do not include Employee level details as this report would be shared by the finance or reporting and analytics team