In my BI Publisher data model I’m using a custom SQL query that returns payroll balances driven from the standard payroll action tables: PAY_PAYROLL_ACTIONS, PAY_PAYROLL_REL_ACTIONS, and PAY_PAY_RELATIONSHIPS_DN (filtered by payroll action type). This part of the query is working as expected.
Now I need to bring in the flow information so that each row also shows the corresponding payroll flow. I’m looking at the PAY_FLOWS_VL view, which exposes FLOW_ID as the primary key for the payroll flow definition. However, I don’t see a straightforward way to join FLOW_ID from PAY_FLOWS_VL to my existing set of tables (PAY_PAYROLL_ACTIONS, PAY_PAYROLL_REL_ACTIONS, PAY_PAY_RELATIONSHIPS_DN) that are driving the balances.
Has anyone implemented a reliable join between PAY_FLOWS_VL (or the underlying PAY_FLOWS table) and the payroll action tables used for balances? Specifically:
Is there a standard column or relationship that links FLOW_ID to PAYROLL_ACTION_ID or any of the payroll action tables used for balance reporting?
If not, is the recommended approach to start from the flow / flow instance objects (for example, PAY_FLOW_INSTANCES or related views such as PAY_FLOW_SEARCH_VL) and then navigate to the payroll actions initiated by that flow, instead of trying to join PAY_FLOWS_VL directly to the balances query?
Any guidance, data model references, or sample joins that show how to correctly relate flows to payroll actions in a BI Publisher balances report would be greatly appreciated.
