2 Replies Latest reply: Jan 31, 2013 11:10 PM by user12116294 RSS

    Need to know the grain of table for join

    user12116294
      Hi Experts,

      I have a doubt,

      let say i have to take data from two table
      pay_costs
      PAY_COST_ALLOCATIONS_F

      columns for the tables are
      pay_costs.BALANCE_OR_COSTS
      pay_costs.DEBIT_OR_CREDIT

      PAY_COST_ALLOCATIONS_F.PROPORTION

      Now I need to join these two tables respectively. I follow etrm site and found that we can create join

      PAY_COST_ALLOCATIONS_F.PAY_COST_ALLOCATION_KEYFLEX = PAY_COST_ALLOCATION_KEYFLEX.PAY_COST_ALLOCATION_KEYFLEX
      and PAY_COST_ALLOCATION_KEYFLEX.COST_ALLOCATION_KEYFLEX_ID = pay_costs.COST_ALLOCATION_KEYFLEX_ID

      so I am doing anything wrong. Will the grain of data will change .... Kindly help I am new to ebs field
        • 1. Re: Need to know the grain of table for join
          Michael Armstrong-Smith
          Hello
          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:

          SELECT
          ASSIGNMENT_ID, COST_ALLOCATION_KEYFLEX_ID, PROPORTION
          FROM
          HR.PAY_COST_ALLOCATIONS_F
          WHERE
          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
          Michael
          • 2. Re: Need to know the grain of table for join
            user12116294
            awesome explanation Dude thank you have clear my doubts regarding Proprtions