This content has been marked as final. Show 2 replies
You are going to have potentially millions of records in PAY_COSTS because this is the main pay table.
Your PAY_COST_ALLOCATIONS_F just contains a breakdown of how a user's costs are to be broken out. There's an ASSIGNMENT_ID (which links to the person) and a PROPORTION which is the percentage.
Depending upon how long the person has been with the company, for each Assignment ID (which you can think of as PERSON_ID) there could be hundreds of records in PAY_COSTS.
The primary key for PAY_COSTS is the COST_ID while the primary key for PAY_COST_ALLOCATIONS_F is COST_ALLOCATION_ID plus EFFECTIVE START and EFFECTIVE END dates. What this tells me is that the Allocations are time based and if you just try and join using the ALLOCATION_ID you will end up with an incorrect answer. If you were inside E-Business Suite the application would ask you what effective date to use with the current date being the default. What it then does is apply a filter on all of the tables that have effective dates. So for example you might have to limit allocations to only the current ones this could be done by using this SQL:
ASSIGNMENT_ID, COST_ALLOCATION_KEYFLEX_ID, PROPORTION
SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
ORDER BY ASSIGNMENT_ID, COST_ALLOCATION_KEYFLEX_ID
You can get a clue by doing COUNT(*) from both tables. If there are more records in one table than another then you can be sure that there is not a one-to-one 1:1 join and therefore the granularity must be one-to-many 1:n
Hope this helps