HR Suite

I had prepared some scripts four years ago for reconciling Oracle Payroll for my past Client.
Hence find an article that explains P35 and Gross To Net reconciliation in Oracle Payroll.

I was thinking to write on this topic lately, but a comment from Latha prompted me to write on this subject a bit earlier than as planned.

What is P35?
P35 is annual Tax & Salary related data that every limited company in UK must submit to Inland Revenue.
It is a report produced at the end of each Payroll year, also known as a "Tax Year".
This is a part of End of Year processes often termed as EOY in short.
This report is UK specific, and its output is sent to Inland Revenue.
This report lists NI and tax contribution of each of the  Employee's Assignment record.



What is Gross To Net Report?
It contains summation of Element Run Values for each Payroll Period.
This report does not break down at Employee assignment level. Instead this report groups the elements as per their "Primary Classification". Non Payment Classifications are not included.
In UK, this report is named "Gross To Net Summary Report (GB)".
The main Parameters are:-
   Payroll Name
   Period Name




Why the need of reconciliation between Gross To Net and P35?
Ideally, the total taxation figures in P35 must always be the same as that in Gross to net.
However these figures could differ, and the differences must be explained to the auditors.




How does P35 collate its data?
When you run Tax EOY, it populates some archive tables. P35 report is passed a parameter that maps to the Archive-Id.
The archive tables are:-
           ff_archive_item_contexts
           ff_archive_items
           ff_user_entities
           ff_archive_items
           ff_user_entities
           pay_assignment_actions --For Magnetic Tape Action

The reason for using archive tables is simple. Annual reports sent to tax authorities must be archived so that reported data is sacred.
EOY populate these archive tables, and p35 reads from those tables for reporting.




How does gross to net collate its data?
Gross to net uses Run result tables and summates the run results data per payroll period.



Why do the two reports go out of synch?
The EOY archival tables i.e. ff_archive makes join to pay_assignment_actions that references the magnetic tape entry record for each Assignment. In some cases, assignment action record can be deletedaccidentally. Hence the join to pay_assignment action can fail.
If P35 is run after such entry has been deleted, the it could differ from Gross to Net figures.



What is the role of payroll consultant in the process of reconciliation?
You can be asked to explain the differences. Or at least you can specify the handful  assignment records that are causing figures in two reports to deviate. Its impossible for users to do this reconciliation themselves, given that "Gross To Net" produces Tax and NI figures at Element level.
Note: P35 produces Tax and NI figures for each applicable assignment.


How do I go about this reconciliation?
Although I am providing you the scripts that I developed some 4yrs ago, you can get the latest SQL's from the reports executable itself.
The steps are:-
Gross To Net
1. Open Gross To Net report in Reports Builder [Short name of conc prog is PAYGBGTN]
2. Double-check if the query has changed w.r.t. the script provided by me
3. Run the script calling it for each Assignment  Id, and dump its Tax & NI result per assignment into NI & GTN temp table.


P35 concurrent program short name PAYRPP35
1. Open report in report developer
2. Double-check if the query has changed w.r.t. the script provided by me
3. Run the script calling it for each Assignment  Id, and dump its Tax & NI result per assignment into NI & GTN temp tables.


I leave it for you to create those temp tables, but their structure can include below tables.
NI_RECONCILIATION_TEMP  Table will look like below
       ASSIGNMENT_ID     
       ASSIGNMENT_NUMBER 
       PAYROLL_ACTION_ID 
       PAYROLL_ID        
       NI_GTN            
       NI_P35            
       DIFFERENCE   

PAYE_TAX_RECONCILIATION_TEMP  Table will look like below
       ASSIGNMENT_ID     
       ASSIGNMENT_NUMBER 
       PAYROLL_ACTION_ID 
       PAYROLL_ID        
       PAYE_TAX_GTN            
       PAYE_TAX_P35            
       DIFFERENCE   







-- RECONCILE NI AMOUNTS
--Script for "Gross To Net NI" & "P35 NI" values per assignment_id
--There is nothing special about this utility.
--Use the package  xx_p35_gtn_ni to  reconcile  National Insurance Amount
--The SQL for these are picked up from the reports, modified a bit, so that reconcilliation can be carried out
--Parameter p_payroll_action_id is Payroll Action Id of EOY Process{Appears in screen as Magnetic Tape}


CREATE OR REPLACE PACKAGE xx_p35_gtn_ni IS
  FUNCTION get_ni_gtn_value(p_assignment_id IN NUMBER) RETURN NUMBER;
  FUNCTION get_p35_ni_amount(p_assignment_id     IN NUMBER
                            ,p_payroll_action_id IN NUMBER) RETURN NUMBER;

  FUNCTION get_ass_num(p_assignment_id IN NUMBER) RETURN VARCHAR2;

  PRAGMA RESTRICT_REFERENCES(get_ass_num
                            ,WNPS
                            ,WNDS);
END xx_p35_gtn_ni;
/


CREATE OR REPLACE PACKAGE BODY xx_p35_gtn_ni IS
  FUNCTION get_ass_num(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
    CURSOR c_get IS
      SELECT assignment_number
      FROM per_all_assignments_f
      WHERE assignment_id = p_assignment_id;
 
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN p_get.assignment_number;
  END get_ass_num;

  FUNCTION get_ni_gtn_value(p_assignment_id IN NUMBER) RETURN NUMBER IS
    CURSOR c_get IS
      SELECT
      /*+ ORDERED */
       SUM(to_number(prrv.result_value)) sum_values
      FROM pay_payroll_actions            ppa
          ,pay_assignment_actions         paa
          ,pay_run_results                prr
          ,pay_input_values_f             piv
          ,pay_element_types_f            pet
          ,pay_element_classifications    pec
          ,pay_balance_classifications    pcb
          ,pay_balance_types              pbt
          ,pay_balance_types_tl           pbt_tl
          ,pay_element_classifications_tl pec_tl
          ,pay_element_types_f_tl         pet_tl
          ,pay_run_result_values          prrv
      WHERE paa.payroll_action_id = ppa.payroll_action_id
      AND pbt.balance_type_id IN
            (SELECT balance_type_id
             FROM pay_balance_types
             WHERE balance_name IN
                   ('Gross Pay', 'Total Deductions', 'Total Direct Payments',
                    'Total Employer Charges'))
      AND ppa.action_status = 'C'
      AND ppa.action_type IN ('Q', 'R', 'V')
           --Replace the appropriate period id with their Payroll Id
           --I have put dummy integers below
      AND ((ppa.time_period_id BETWEEN 1000 AND 1011 AND payroll_id = 55) OR
            (ppa.time_period_id BETWEEN 2080 AND 2091 AND payroll_id = 46) )
          --check that your profile value is available from SQLPLUS--will be at site level
      AND ppa.business_id + 0 = fnd_profile.value('PER_BUSINESS_GROUP_ID')
      AND pec.classification_id = pet.classification_id
      AND (pec_tl.classification_name = 'NI' OR
            nvl(pet_tl.reporting_name
             ,pet_tl.element_name) = 'NI Employer')
      AND pet.element_type_id = pet_tl.element_type_id
      AND pet_tl.LANGUAGE = userenv('LANG')
      AND pbt.balance_type_id = pbt_tl.balance_type_id
      AND pbt_tl.LANGUAGE = userenv('LANG')
      AND pbt.balance_type_id = pcb.balance_type_id
      AND pcb.classification_id = pec.classification_id
      AND pec.classification_id = pec_tl.classification_id
      AND pec_tl.LANGUAGE = userenv('LANG')
      AND pbt.legislation_code = 'GB'
      AND pet.element_type_id = prr.element_type_id
      AND prr.element_type_id = piv.element_type_id
      AND prr.status IN ('P', 'PA')
      AND piv.NAME = 'Pay Value'
      AND piv.uom = 'M'
      AND ppa.effective_date BETWEEN pet.effective_start_date AND
            pet.effective_end_date
      AND ppa.effective_date BETWEEN piv.effective_start_date AND
            piv.effective_end_date
      AND piv.input_value_id = prrv.input_value_id
      AND prr.run_result_id = prrv.run_result_id
      AND prrv.result_value IS NOT NULL
      AND prr.assignment_action_id = paa.assignment_action_id
      AND paa.assignment_id = p_assignment_id;
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN nvl(p_get.sum_values
              ,0);
  END get_ni_gtn_value;

  FUNCTION get_p35_ni_amount(p_assignment_id     IN NUMBER
                            ,p_payroll_action_id IN NUMBER) RETURN NUMBER IS
    CURSOR c_get IS
      SELECT SUM(fai2.VALUE / 100) total_contributions
      FROM ff_archive_item_contexts aic3
          ,ff_archive_item_contexts aic2
          ,ff_archive_item_contexts aic1
          ,ff_archive_items         fai2
          ,ff_user_entities         fue2
          ,ff_archive_items         fai1
          ,ff_user_entities         fue1
          ,pay_assignment_actions   act
      WHERE act.payroll_action_id = p_payroll_action_id
      AND act.assignment_id = p_assignment_id
      AND act.assignment_action_id = fai1.context1
      AND act.assignment_action_id = fai2.context1
      AND fue1.user_entity_name = 'X_EFFECTIVE_END_DATE'
      AND fue2.user_entity_name LIKE 'X_NI_%TOTAL_CONTRIBUTIONS'
      AND fue1.legislation_code = 'GB'
      AND fue1.business_id IS NULL
      AND fue2.legislation_code = 'GB'
      AND fue2.business_id IS NULL
      AND fue1.user_entity_id = fai1.user_entity_id + 0
      AND fue2.user_entity_id = fai2.user_entity_id + 0
      AND aic1.archive_item_id = fai2.archive_item_id
      AND aic2.archive_item_id = fai2.archive_item_id
      AND aic3.archive_item_id(+) = fai2.archive_item_id
      AND aic1.sequence_no = 1
      AND aic2.sequence_no = 2
      AND aic3.sequence_no(+) = 3;
 
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN nvl(p_get.total_contributions
              ,0);
  END get_p35_ni_amount;

BEGIN
  NULL;
END xx_p35_gtn_ni;
/






-- RECONCILE TAX AMOUNTS
-- Script for "Gross To Net Tax PAYE" & "P35 Tax PAYE" values per assignment_id
--Again, there is nothing special about this utility.
--Use the package  xx_p35_gtn_tax_paye to  reconcile  Tax Amounts per assignment
--The SQL for these were picked up from the respective Oracle Reports, modified a bit, so that reconcilliation could be carried out
--Parameter p_payroll_action_id is Payroll Action Id of EOY Process{Appears in screen as Magnetic Tape}
CREATE OR REPLACE PACKAGE xx_p35_gtn_tax_paye IS
  FUNCTION get_paye_gtn_value(p_assignment_id IN NUMBER) RETURN NUMBER;

  FUNCTION ic_get_refund(p_assignment_action_id IN NUMBER) RETURN NUMBER;

  PRAGMA RESTRICT_REFERENCES(ic_get_refund
                            ,WNPS
                            ,WNDS);

  FUNCTION get_p35_paye_amount(p_assignment_id     IN NUMBER
                              ,p_payroll_action_id IN NUMBER) RETURN NUMBER;

  FUNCTION get_ass_num(p_assignment_id IN NUMBER) RETURN VARCHAR2;

  PRAGMA RESTRICT_REFERENCES(get_ass_num
                            ,WNPS
                            ,WNDS);
END xx_p35_gtn_tax_paye;
/




CREATE OR REPLACE PACKAGE BODY xx_p35_gtn_tax_paye IS
  FUNCTION get_ass_num(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
    CURSOR c_get IS
      SELECT assignment_number
      FROM per_all_assignments_f
      WHERE assignment_id = p_assignment_id;
 
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN p_get.assignment_number;
  END get_ass_num;

  FUNCTION ic_get_refund(p_assignment_action_id IN NUMBER) RETURN NUMBER IS
    CURSOR c_get IS
      SELECT fai.VALUE
      FROM ff_archive_items fai
      WHERE fai.context1 = p_assignment_action_id
      AND fai.user_entity_id =
            (SELECT user_entity_id
             FROM ff_user_entities
             WHERE user_entity_name = 'X_TAX_REFUND');
 
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
 
    IF p_get.VALUE = 'R'
    THEN
      RETURN - 1;
    END IF;
 
    RETURN 1;
  END ic_get_refund;

  FUNCTION get_paye_gtn_value(p_assignment_id IN NUMBER) RETURN NUMBER IS
    CURSOR c_get IS
      SELECT
      /*+ ORDERED */
       SUM(to_number(prrv.result_value)) element_name_and_value
      FROM pay_assignment_actions         paa
          ,pay_payroll_actions            ppa
          ,pay_run_results                prr
          ,pay_input_values_f             piv
          ,pay_element_types_f            pet
          ,pay_element_classifications    pec
          ,pay_balance_classifications    pcb
          ,pay_balance_types              pbt
          ,pay_balance_types_tl           pbt_tl
          ,pay_element_classifications_tl pec_tl
          ,pay_element_types_f_tl         pet_tl
          ,pay_run_result_values          prrv
      WHERE paa.payroll_action_id + 0 = ppa.payroll_action_id
      AND pbt.balance_type_id IN
            (SELECT balance_type_id
             FROM pay_balance_types
             WHERE balance_name IN
                   ('Gross Pay', 'Total Deductions', 'Total Direct Payments',
                    'Total Employer Charges'))
      AND ppa.action_status = 'C'
      AND ppa.action_type IN ('Q', 'R', 'V')
           --   AND ppa.payroll_id = :p_payroll_id
      AND ppa.business_id + 0 =
            fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
      AND pec.classification_id = pet.classification_id
      AND pet.element_type_id = pet_tl.element_type_id
      AND pet_tl.LANGUAGE = userenv('LANG')
      AND pbt.balance_type_id = pbt_tl.balance_type_id
      AND pbt_tl.LANGUAGE = userenv('LANG')
      AND pbt.balance_type_id = pcb.balance_type_id
      AND pcb.classification_id = pec.classification_id
      AND pec.classification_id = pec_tl.classification_id
      AND pec_tl.LANGUAGE = userenv('LANG')
      AND pbt.legislation_code = 'GB'
      AND pet.element_type_id = prr.element_type_id
      AND prr.element_type_id = piv.element_type_id
      AND prr.status IN ('P', 'PA')
      AND piv.NAME = 'Pay Value'
      AND piv.uom = 'M'
      AND ppa.effective_date BETWEEN pet.effective_start_date AND
            pet.effective_end_date
      AND ppa.effective_date BETWEEN piv.effective_start_date AND
            piv.effective_end_date
      AND piv.input_value_id = prrv.input_value_id
      AND prr.run_result_id = prrv.run_result_id
      AND prrv.result_value IS NOT NULL
      AND prr.assignment_action_id = paa.assignment_action_id
           --Replace the appropriate period id with their Payroll Id
           --I have put dummy integers below
      AND ((ppa.time_period_id BETWEEN 1000 AND 1011 AND payroll_id = 55) OR
            (ppa.time_period_id BETWEEN 2080 AND 2091 AND payroll_id = 46))
      AND paa.assignment_id = p_assignment_id
      AND pet_tl.element_name = 'PAYE'
      GROUP BY pbt_tl.balance_name
              ,pec_tl.classification_name
              ,nvl(substr(pet_tl.reporting_name
                         ,1
                         ,30)
                  ,pet_tl.element_name)
              ,nvl(substr(pet_tl.reporting_name
                         ,1
                         ,30)
                  ,substr(pet_tl.element_name
                         ,1
                         ,40)) ||
               rpad(' '
                   ,39 - length(nvl(substr(pet_tl.reporting_name
                                          ,1
                                          ,30)
                                   ,pet_tl.element_name))
                   ,' ');
 
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN nvl(p_get.element_name_and_value
              ,0);
  END get_paye_gtn_value;

  FUNCTION get_p35_paye_amount(p_assignment_id     IN NUMBER
                              ,p_payroll_action_id IN NUMBER) RETURN NUMBER IS
    CURSOR c_get IS
      SELECT ((fai.VALUE * ic_get_refund(fai.context1)) / 100) "VALUE"
             ,fai.context1 "ASSIGNMENT_ACTION_ID"
      FROM ff_archive_items fai
      WHERE fai.context1 =
            (SELECT assignment_action_id
             FROM pay_assignment_actions paa
             WHERE paa.payroll_action_id = p_payroll_action_id
             AND assignment_id = p_assignment_id)
      AND fai.user_entity_id =
                   (SELECT user_entity_id
                    FROM ff_user_entities
                    WHERE user_entity_name = 'X_TAX_REFUND');
 
 
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;
    RETURN nvl(p_get.VALUE
              ,0);
  END get_p35_paye_amount;

BEGIN
  NULL;
END xx_p35_gtn_tax_paye;
/

Add comment

Name (required)

E-mail (required, but will not display)

Website

Notify me of follow-up comments

Refresh

Send

Cancel