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;
/
Comments
Firstly I really need to appreciate for this excellent website dedicated for Oracle Applications User Group. I have gone through many of the forums and sites, but they dont really provide any useful information in the practical scenario.
In regards to the P35, I was looking for the report related to P35 in Apps. Just want to know, is there any specific name in Apps now to generate P35?
Your help is quite appreciated on this.
Regard s
Vijay
For P35, you will be required to run EOY - End of Year Processes.
O nce you run this process, just like any payroll run, payroll action, assignment action will be created
Additi onally, archive tables will get populated.
T here is no user-intrerface screen for archive tables, because data in these tables are not meant to be modified.
P35 picks information from these archive tables
Also, all those employees, for which archive records were created, these will have Magnetic Tape entry against their assignment actions.
Tha nks
Anil Passi
RSS feed for comments to this post