Hi Friends,
My blog has been (b)locked for all these days. I am managed to unlock and publishing a new post on retreiving Statement of earnings, P45 data as there is no record history available for Statement of earnings and P45 details in employee assignment, it might be difficult for someone who is working first time on this requiremet.
So, I would like to give you breif overview on how to get this data using standard APIs.
Navigation of below forms in Oracle application:
Statement of Earnings Navigation: People - Assignment - Others - Statement of Earnings
P45 form Navigation: People - Assignment - Others - P45
P11D Entries Navigation: People - Assignment - Others - P11D Entries
ANONYMOUS BLOCKS ARE GIVEN TO DERIVE BELOW POINTS:
1.Get Balance details/P60
2.Get Tax details
3.Get amount paid
4.Get assignment action id of P45 process run for an employee
5.Get P45 details using API/query
6.Messages
7.Payment methods
8.P11 D details
You can change the code and hardcoded values according to your requirement
/******* To get Balance details/P60 ********/
DECLARE
l_displayed_balance PAY_GB_PAYROLL_ACTIONS_PKG.balance_name_table;
l_displayed_value PAY_GB_PAYROLL_ACTIONS_PKG.balance_value_table;
l_count NUMBER := 0;
l_bal_ix BINARY_INTEGER;
l_amount_paid NUMBER;
l_total_payment NUMBER;
l_total_deduct NUMBER;
BEGIN
-- P60 Info: To get Balance Block details in statement of earnings (People-Assignment-Others-Statement of Earnings)
PAY_GB_PAYROLL_ACTIONS_PKG.get_report_balances (p_assignment_action_id => 72205296 ,
p_business_group_id => 526 ,
g_displayed_balance => l_displayed_balance,
g_displayed_value => l_displayed_value );
-- To get amount paid
-- Total Amount = Payments - Deductions
PAY_GB_PAYROLL_ACTIONS_PKG.total_payment(l_assignment_action_id,l_total_payment); -- To get Total Payment
PAY_GB_PAYROLL_ACTIONS_PKG.total_deduct(l_assignment_action_id,l_total_deduct); -- To get Total deductions
-- Calculate the amount
l_amount_paid := l_total_payment - l_total_deduct;
dbms_output.put_line( 'l_amount_paid - '||l_amount_paid);
WHILE l_count < l_displayed_balance.COUNT
LOOP
IF l_count = 0 THEN
l_bal_ix := l_displayed_balance.FIRST;
ELSE
dbms_output.put_line('Count not equal to zero');
END IF;
IF l_displayed_balance(l_bal_ix) IS NOT NULL THEN
dbms_output.put_line('Displayed Balance: '|| l_displayed_balance(l_bal_ix)); -- This will return the balance names (like Gross YTD, Gross PTD,
Taxable YTD,PAYE YTD....etc whatever defined in system)
dbms_output.put_line('Balance Value: '|| l_displayed_value(l_bal_ix)); -- This will return the corresponding balance values of above balances
l_bal_ix := l_displayed_balance.NEXT(l_bal_ix);
l_count := l_count + 1;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error-'||SUBSTR(SQLERRM,1,200));
END;
/***********************************************************/
/*********** To get Tax details *********************************/
declare
p_tax_period varchar2(100);
p_tax_refno varchar2(100);
p_tax_phone varchar2(100);
p_tax_code varchar2(100);
p_tax_basis varchar2(100);
p_ni_category varchar2(100);
l_assignment_id number;
l_assignment_action_id number;
l_payroll_action_id number;
l_date varchar2(20);
begin
l_assignment_id :=33727;
l_assignment_action_id :=72205296;
l_payroll_action_id :=347704;
l_date:= '2012/06/30';
dbms_output.put_line('Calling API...');
PAY_GB_PAYROLL_ACTIONS_PKG.get_report_db_items ( p_assignment_id => l_assignment_id ,
p_run_assignment_action_id => l_assignment_action_id ,
p_date_earned =>l_date,
p_payroll_action_id => l_payroll_action_id ,
p_tax_period => p_tax_period ,
p_tax_refno => p_tax_refno,
p_tax_phone => p_tax_phone ,
p_tax_code => p_tax_code,
p_tax_basis => p_tax_basis ,
p_ni_category => p_ni_category);
dbms_output.put_line('p_tax_period - '||p_tax_period);
dbms_output.put_line('p_tax_refno - '||p_tax_refno);
dbms_output.put_line('p_tax_phone - '||p_tax_phone);
dbms_output.put_line('p_tax_code - '||p_tax_code);
dbms_output.put_line('p_tax_basis - '||p_tax_basis);
dbms_output.put_line('p_ni_category - '||p_ni_category);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Custom Error-'||SUBSTR(SQLERRM,1,200));
END;
/***********************************************************/
/*************** To get amount paid *****************************/
DECLARE
l_total_payment number;
l_total_deduct number;
l_assignment_action_id number;
l_amount_paid number;
BEGIN
l_assignment_action_id :=72205296;
PAY_GB_PAYROLL_ACTIONS_PKG.total_payment(l_assignment_action_id,l_total_payment);
PAY_GB_PAYROLL_ACTIONS_PKG.total_deduct(l_assignment_action_id,l_total_deduct);
l_amount_paid := l_total_payment - l_total_deduct;
dbms_output.put_line('l_total_payment - '||l_total_payment);
dbms_output.put_line('l_total_deduct - '||l_total_deduct);
dbms_output.put_line('l_amount_paid - '||l_amount_paid);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Custom Error-'||SUBSTR(SQLERRM,1,200));
END;
/***********************************************************/
/************ To get assignment action id of P45 process run *********/
DECLARE
l_assignment_id NUMBER;
l_p45_assignment_action_id NUMBER;
l_issue_date DATE;
l_action_sequence NUMBER;
BEGIN
--l_assignment_id := 1234; -- Assignment id
PAY_P45_PKG.get_p45_asg_action_id(p_assignment_id => 1234 , -- This is in parameter
p_assignment_action_id => l_p45_assignment_action_id ,
p_issue_date => l_issue_date ,
p_action_sequence => l_action_sequence
);
dbms_output.put_line('l_p45_assignment_action_id - '||l_p45_assignment_action_id);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error-'||SUBSTR(SQLERRM,1,200));
END;
/***********************************************************/
/************ To get P45 details using API *************************/
DECLARE
l_assignment_action_id number;
l_last_name varchar2(100);
l_first_name varchar2(100);
l_dol_dd varchar2(100);
l_dol_mm varchar2(100);
l_dol_yy varchar2(100);
l_tax_code varchar2(100);
l_month_no varchar2(100);
l_1 varchar2(100);
l_2 varchar2(100);
l_3 varchar2(100);
l_4 varchar2(100);
l_5 varchar2(100);
l_6 varchar2(100);
l_7 varchar2(100);
l_8 varchar2(100);
l_9 varchar2(100);
l_title varchar2(100);
l_mth1 varchar2(100);
l_week_no varchar2(100);
l_pay_td_pounds number;
l_pay_td_pence number;
l_tax_td_pounds number;
l_tax_td_pence number;
l_pay_in_emp_pounds number;
l_apy_in_emp_pence number;
l_tax_in_emp_pounds number;
l_tax_in_emp_pence number;
l_assignment_number varchar2(100);
l_org_name varchar2(100);
l_address_line1 varchar2(100);
l_address_line2 varchar2(100);
l_address_line3 varchar2(100);
l_town_or_city varchar2(100);
l_region_1 varchar2(100);
l_postal_code varchar2(100);
l_deceased_flag varchar2(100);
l_issue_date varchar2(100);
l_tax_ref_transfer varchar2(100);
l_student_loan_flag varchar2(100);
l_country varchar2(100);
l_date_of_birth_dd varchar2(100);
l_date_of_birth_mm varchar2(100);
l_date_of_birth_yy varchar2(100);
l_sex_m varchar2(100);
l_sex_f varchar2(100);
BEGIN
--l_assignment_action_id := 72205296;
PAY_P45_PKG.pop_term_asg_from_archive(X_ASSIGNMENT_ACTION_ID => 68688196, -- l_assignment_action_id,
X_NI1 => l_1,
X_NI2 =>l_2,
X_NI3 =>l_3,
X_NI4 =>l_4,
X_NI5 =>l_5,
X_NI6 =>l_6,
X_NI7 =>l_7,
X_NI8 =>l_8,
X_NI9 =>l_9,
X_LAST_NAME => l_last_name,
X_TITLE => l_title,
X_FIRST_NAME => l_first_name,
X_DATE_OF_LEAVING_DD => l_dol_dd,
X_DATE_OF_LEAVING_MM => l_dol_mm,
X_DATE_OF_LEAVING_YY => l_dol_yy,
X_TAX_CODE_AT_LEAVING => l_tax_code,
X_WK1_OR_MTH1 => l_mth1,
X_WEEK_NO => l_week_no,
X_MONTH_NO => l_month_no,
X_PAY_TD_POUNDS => l_pay_td_pounds,
X_PAY_TD_PENCE => l_pay_td_pence,
X_TAX_TD_POUNDS => l_tax_td_pounds,
X_TAX_TD_PENCE => l_tax_td_pence,
X_PAY_IN_EMP_POUNDS => l_pay_in_emp_pounds,
X_PAY_IN_EMP_PENCE => l_apy_in_emp_pence,
X_TAX_IN_EMP_POUNDS => l_tax_in_emp_pounds,
X_TAX_IN_EMP_PENCE => l_tax_in_emp_pence,
X_ASSIGNMENT_NUMBER => l_assignment_number,
X_ORG_NAME => l_org_name,
X_ADDRESS_LINE1 => l_address_line1,
X_ADDRESS_LINE2 => l_address_line2,
X_ADDRESS_LINE3 => l_address_line3,
X_TOWN_OR_CITY => l_town_or_city,
X_REGION_1 => l_region_1,
X_POSTAL_CODE => l_postal_code,
X_DECEASED_FLAG => l_deceased_flag,
X_ISSUE_DATE => l_issue_date,
X_TAX_REF_TRANSFER =>l_tax_ref_transfer,
X_STUDENT_LOAN_FLAG =>l_student_loan_flag,
X_COUNTRY =>l_country,
X_DATE_OF_BIRTH_DD =>l_date_of_birth_dd,
X_DATE_OF_BIRTH_MM =>l_date_of_birth_mm,
X_DATE_OF_BIRTH_YY =>l_date_of_birth_yy,
X_SEX_M =>l_sex_m,
X_SEX_F =>l_sex_f
);
dbms_output.put_line('l_last_name - '||l_last_name);
dbms_output.put_line('l_first_name - '||l_first_name);
dbms_output.put_line('l_dol_dd - '||l_dol_dd);
dbms_output.put_line('l_dol_mm - '||l_dol_mm);
dbms_output.put_line('l_dol_yy - '||l_dol_yy);
dbms_output.put_line('l_tax_code - '||l_tax_code);
dbms_output.put_line('l_month_no - '||l_month_no);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error - '||SUBSTR(SQLERRM,1,200));
END;
/************************************************************/
/************** To get P45 details using query **********************/
SELECT act.assignment_action_id,
max(decode(fue.user_entity_name,'X_TAX_CODE',substr(UPPER(fai.value),1,10))) tax_code,
max(decode(fue.user_entity_name,'X_TERMINATION_DATE',fnd_date.canonical_to_date(fai.value))) date_of_leaving,
max(decode(fue.user_entity_name,'X_MONTH_NUMBER',fnd_number.canonical_to_number(fai.value))) month_number,
max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',substr(UPPER(fai.value),1,20))) ni_number,
max(decode(fue.user_entity_name,'X_TAX_PAID', fnd_number.canonical_to_number(fai.value))) Total_tax_to_date,
max(decode(fue.user_entity_name,'X_TAXABLE_PAY',fnd_number.canonical_to_number(fai.value))) Total_pay_to_date
-- max(decode(fue.user_entity_name,'X_PREVIOUS_TAX_PAID',
-- fnd_number.canonical_to_number(fai.value))) previous_tax_paid,
-- max(decode(fue.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',
-- fnd_number.canonical_to_number(fai.value))) previous_taxable_pay,
-- max(decode(fue.user_entity_name,'X_WEEK_NUMBER',
-- fnd_number.canonical_to_number(fai.value))) week_number,
-- max(decode(fue.user_entity_name,'X_PAYROLL_ID',
-- fnd_number.canonical_to_number(fai.value))) payroll_id,
-- max(decode(fue.user_entity_name,'X_ISSUE_DATE',
-- fnd_date.canonical_to_date(fai.value))) issue_date,
-- max(decode(fue.user_entity_name,'X_DECEASED_FLAG',
-- substr(fai.value,1,1))) deceased_flag,
-- max(decode(fue.user_entity_name,'X_ASSIGNMENT_NUMBER',
-- substr(fai.VALUE,1,30))) assignment_number,
-- max(decode(fue.user_entity_name,'X_FIRST_NAME',
-- substr(UPPER(fai.value),1,40)))||' '||
-- max(decode(fue.user_entity_name,'X_MIDDLE_NAME',
-- substr(UPPER(fai.value),1,40))) first_name,
-- max(decode(fue.user_entity_name,'X_LAST_NAME',
-- substr(UPPER(fai.value),1,40))) last_name,
-- max(decode(fue.user_entity_name,'X_ORGANIZATION_NAME',
-- substr(fai.value,1,60))) organization_name,
-- max(decode(fue.user_entity_name,'X_ADDRESS_LINE1',
-- substr(fai.value,1,60))) address_line1,
-- max(decode(fue.user_entity_name,'X_ADDRESS_LINE2',
-- substr(fai.value,1,60))) address_line2,
-- max(decode(fue.user_entity_name,'X_ADDRESS_LINE3',
-- substr(fai.value,1,60))) address_line3,
-- max(decode(fue.user_entity_name,'X_TOWN_OR_CITY',
-- substr(fai.value,1,30))) town_or_city,
-- max(decode(fue.user_entity_name,'X_COUNTY',
-- substr(fai.value,1,30))) county,
-- max(decode(fue.user_entity_name,'X_POSTAL_CODE',
-- substr(fai.value,1,20))) post_code,
-- max(decode(fue.user_entity_name,'X_STUDENT_LOAN_FLAG',
-- substr(fai.value,1,1))) student_loan_flag,
-- max(decode(fue.user_entity_name,'X_TITLE',
-- substr(UPPER(fai.value),1,40))) title,
-- max(decode(fue.user_entity_name,'X_W1_M1_INDICATOR',
-- substr(fai.value,1,1))) w1_m1_indicator,
-- max(decode(fue.user_entity_name,'X_COUNTRY',
-- substr(fai.value,1,30))) country,
nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),1,2),' ') DATE_OF_BIRTH_DD,
-- nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),4,2),' ') DATE_OF_BIRTH_MM,
-- nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),7,4),' ') DATE_OF_BIRTH_YY,
-- nvl(max (decode(fue.user_entity_name,'X_SEX', substr(fai.value,1,1))),' ') SEX,
--nvl(substr(to_char(SYSDATE,'DD-MM-YYYY'),1,2),' ') TODAY_DD,
-- nvl(substr(to_char(SYSDATE,'DD-MM-YYYY'),4,2),' ') TODAY_MM,
-- nvl(substr(to_char(SYSDATE,'DD-MM-YYYY'),7,4),' ') TODAY_YYYY
FROM pay_assignment_actions act,
pay_payroll_actions ppa,
ff_archive_items fai,
ff_user_entities fue
WHERE act.assignment_action_id = fai.context1
AND act.action_status = 'C'
AND ppa.action_type = 'X'
AND fue.legislation_code = 'GB'
AND fue.business_group_id IS NULL
AND fue.user_entity_id = fai.user_entity_id
AND ppa.payroll_action_id = act.payroll_action_id
and act.assignment_id = p_assignment_id
and act.assignment_action_id = p_assignment_action_id
and ppa.report_type = 'P45'
and ppa.report_category = 'P45'
GROUP BY act.assignment_action_id
ORDER BY assignment_action_id desc;
/***********************************************************/
/************* To get Messages ********************************/
SELECT LINE_SEQUENCE, SOURCE_ID, LINE_TEXT
FROM PAY_GB_SOE_MESSAGE_V
WHERE source_id = 72310872 -- assignment action id
ORDER BY line_sequence
/***********************************************************/
/************* To get payment methods ***************************/
SELECT * FROM pay_org_payment_methods_f
SELECT * FROM pay_personal_payment_methods_f
SELECT * FROM pay_external_accounts
/***********************************************************/
/**************** To get P11D entry details **********************/
SELECT ELEMENT_ENTRY_ID,
ENTRY_TYPE,
INPUT_VALUE_ID,
ADJUSTMENT_TYPE,
ENTRY_VALUE,
EFFECTIVE_START_DATE,
TARGET_ENTRY_ID,
SCREEN_ENTRY_VALUE
FROM PAY_PAYWSMEE_ADJUSTMENT
WHERE input_value_id + 0 = ''
AND assignment_id = 33727
AND element_link_id = 2716
AND TO_DATE ('01-03-2013', 'DD-MM-YYYY') BETWEEN effective_start_date
AND effective_end_date
AND (TARGET_ENTRY_ID = 725955); -- assignment action id
SELECT * FROM PAY_PAYWSMEE_ELEMENT_ENTRIES;
SELECT * FROM PAY_PAYWSMEE_ADJUSTMENT;
SELECT * FROM PAY_ELEMENT_ENTRIES_F ;
SELECT * FROM hr_lookups
WHERE lookup_type ='ENTRY_TYPE' --like '%ENTRY%'
AND meaning='Element Entry';
/***********************************************************/
Your suggestions/inputs are most welcome.
Thanks,
Rajesh
My blog has been (b)locked for all these days. I am managed to unlock and publishing a new post on retreiving Statement of earnings, P45 data as there is no record history available for Statement of earnings and P45 details in employee assignment, it might be difficult for someone who is working first time on this requiremet.
So, I would like to give you breif overview on how to get this data using standard APIs.
Navigation of below forms in Oracle application:
Statement of Earnings Navigation: People - Assignment - Others - Statement of Earnings
P45 form Navigation: People - Assignment - Others - P45
P11D Entries Navigation: People - Assignment - Others - P11D Entries
ANONYMOUS BLOCKS ARE GIVEN TO DERIVE BELOW POINTS:
1.Get Balance details/P60
2.Get Tax details
3.Get amount paid
4.Get assignment action id of P45 process run for an employee
5.Get P45 details using API/query
6.Messages
7.Payment methods
8.P11 D details
You can change the code and hardcoded values according to your requirement
/******* To get Balance details/P60 ********/
DECLARE
l_displayed_balance PAY_GB_PAYROLL_ACTIONS_PKG.balance_name_table;
l_displayed_value PAY_GB_PAYROLL_ACTIONS_PKG.balance_value_table;
l_count NUMBER := 0;
l_bal_ix BINARY_INTEGER;
l_amount_paid NUMBER;
l_total_payment NUMBER;
l_total_deduct NUMBER;
BEGIN
-- P60 Info: To get Balance Block details in statement of earnings (People-Assignment-Others-Statement of Earnings)
PAY_GB_PAYROLL_ACTIONS_PKG.get_report_balances (p_assignment_action_id => 72205296 ,
p_business_group_id => 526 ,
g_displayed_balance => l_displayed_balance,
g_displayed_value => l_displayed_value );
-- To get amount paid
-- Total Amount = Payments - Deductions
PAY_GB_PAYROLL_ACTIONS_PKG.total_payment(l_assignment_action_id,l_total_payment); -- To get Total Payment
PAY_GB_PAYROLL_ACTIONS_PKG.total_deduct(l_assignment_action_id,l_total_deduct); -- To get Total deductions
-- Calculate the amount
l_amount_paid := l_total_payment - l_total_deduct;
dbms_output.put_line( 'l_amount_paid - '||l_amount_paid);
WHILE l_count < l_displayed_balance.COUNT
LOOP
IF l_count = 0 THEN
l_bal_ix := l_displayed_balance.FIRST;
ELSE
dbms_output.put_line('Count not equal to zero');
END IF;
IF l_displayed_balance(l_bal_ix) IS NOT NULL THEN
dbms_output.put_line('Displayed Balance: '|| l_displayed_balance(l_bal_ix)); -- This will return the balance names (like Gross YTD, Gross PTD,
Taxable YTD,PAYE YTD....etc whatever defined in system)
dbms_output.put_line('Balance Value: '|| l_displayed_value(l_bal_ix)); -- This will return the corresponding balance values of above balances
l_bal_ix := l_displayed_balance.NEXT(l_bal_ix);
l_count := l_count + 1;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error-'||SUBSTR(SQLERRM,1,200));
END;
/***********************************************************/
/*********** To get Tax details *********************************/
declare
p_tax_period varchar2(100);
p_tax_refno varchar2(100);
p_tax_phone varchar2(100);
p_tax_code varchar2(100);
p_tax_basis varchar2(100);
p_ni_category varchar2(100);
l_assignment_id number;
l_assignment_action_id number;
l_payroll_action_id number;
l_date varchar2(20);
begin
l_assignment_id :=33727;
l_assignment_action_id :=72205296;
l_payroll_action_id :=347704;
l_date:= '2012/06/30';
dbms_output.put_line('Calling API...');
PAY_GB_PAYROLL_ACTIONS_PKG.get_report_db_items ( p_assignment_id => l_assignment_id ,
p_run_assignment_action_id => l_assignment_action_id ,
p_date_earned =>l_date,
p_payroll_action_id => l_payroll_action_id ,
p_tax_period => p_tax_period ,
p_tax_refno => p_tax_refno,
p_tax_phone => p_tax_phone ,
p_tax_code => p_tax_code,
p_tax_basis => p_tax_basis ,
p_ni_category => p_ni_category);
dbms_output.put_line('p_tax_period - '||p_tax_period);
dbms_output.put_line('p_tax_refno - '||p_tax_refno);
dbms_output.put_line('p_tax_phone - '||p_tax_phone);
dbms_output.put_line('p_tax_code - '||p_tax_code);
dbms_output.put_line('p_tax_basis - '||p_tax_basis);
dbms_output.put_line('p_ni_category - '||p_ni_category);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Custom Error-'||SUBSTR(SQLERRM,1,200));
END;
/***********************************************************/
/*************** To get amount paid *****************************/
DECLARE
l_total_payment number;
l_total_deduct number;
l_assignment_action_id number;
l_amount_paid number;
BEGIN
l_assignment_action_id :=72205296;
PAY_GB_PAYROLL_ACTIONS_PKG.total_payment(l_assignment_action_id,l_total_payment);
PAY_GB_PAYROLL_ACTIONS_PKG.total_deduct(l_assignment_action_id,l_total_deduct);
l_amount_paid := l_total_payment - l_total_deduct;
dbms_output.put_line('l_total_payment - '||l_total_payment);
dbms_output.put_line('l_total_deduct - '||l_total_deduct);
dbms_output.put_line('l_amount_paid - '||l_amount_paid);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Custom Error-'||SUBSTR(SQLERRM,1,200));
END;
/***********************************************************/
/************ To get assignment action id of P45 process run *********/
DECLARE
l_assignment_id NUMBER;
l_p45_assignment_action_id NUMBER;
l_issue_date DATE;
l_action_sequence NUMBER;
BEGIN
--l_assignment_id := 1234; -- Assignment id
PAY_P45_PKG.get_p45_asg_action_id(p_assignment_id => 1234 , -- This is in parameter
p_assignment_action_id => l_p45_assignment_action_id ,
p_issue_date => l_issue_date ,
p_action_sequence => l_action_sequence
);
dbms_output.put_line('l_p45_assignment_action_id - '||l_p45_assignment_action_id);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error-'||SUBSTR(SQLERRM,1,200));
END;
/***********************************************************/
/************ To get P45 details using API *************************/
DECLARE
l_assignment_action_id number;
l_last_name varchar2(100);
l_first_name varchar2(100);
l_dol_dd varchar2(100);
l_dol_mm varchar2(100);
l_dol_yy varchar2(100);
l_tax_code varchar2(100);
l_month_no varchar2(100);
l_1 varchar2(100);
l_2 varchar2(100);
l_3 varchar2(100);
l_4 varchar2(100);
l_5 varchar2(100);
l_6 varchar2(100);
l_7 varchar2(100);
l_8 varchar2(100);
l_9 varchar2(100);
l_title varchar2(100);
l_mth1 varchar2(100);
l_week_no varchar2(100);
l_pay_td_pounds number;
l_pay_td_pence number;
l_tax_td_pounds number;
l_tax_td_pence number;
l_pay_in_emp_pounds number;
l_apy_in_emp_pence number;
l_tax_in_emp_pounds number;
l_tax_in_emp_pence number;
l_assignment_number varchar2(100);
l_org_name varchar2(100);
l_address_line1 varchar2(100);
l_address_line2 varchar2(100);
l_address_line3 varchar2(100);
l_town_or_city varchar2(100);
l_region_1 varchar2(100);
l_postal_code varchar2(100);
l_deceased_flag varchar2(100);
l_issue_date varchar2(100);
l_tax_ref_transfer varchar2(100);
l_student_loan_flag varchar2(100);
l_country varchar2(100);
l_date_of_birth_dd varchar2(100);
l_date_of_birth_mm varchar2(100);
l_date_of_birth_yy varchar2(100);
l_sex_m varchar2(100);
l_sex_f varchar2(100);
BEGIN
--l_assignment_action_id := 72205296;
PAY_P45_PKG.pop_term_asg_from_archive(X_ASSIGNMENT_ACTION_ID => 68688196, -- l_assignment_action_id,
X_NI1 => l_1,
X_NI2 =>l_2,
X_NI3 =>l_3,
X_NI4 =>l_4,
X_NI5 =>l_5,
X_NI6 =>l_6,
X_NI7 =>l_7,
X_NI8 =>l_8,
X_NI9 =>l_9,
X_LAST_NAME => l_last_name,
X_TITLE => l_title,
X_FIRST_NAME => l_first_name,
X_DATE_OF_LEAVING_DD => l_dol_dd,
X_DATE_OF_LEAVING_MM => l_dol_mm,
X_DATE_OF_LEAVING_YY => l_dol_yy,
X_TAX_CODE_AT_LEAVING => l_tax_code,
X_WK1_OR_MTH1 => l_mth1,
X_WEEK_NO => l_week_no,
X_MONTH_NO => l_month_no,
X_PAY_TD_POUNDS => l_pay_td_pounds,
X_PAY_TD_PENCE => l_pay_td_pence,
X_TAX_TD_POUNDS => l_tax_td_pounds,
X_TAX_TD_PENCE => l_tax_td_pence,
X_PAY_IN_EMP_POUNDS => l_pay_in_emp_pounds,
X_PAY_IN_EMP_PENCE => l_apy_in_emp_pence,
X_TAX_IN_EMP_POUNDS => l_tax_in_emp_pounds,
X_TAX_IN_EMP_PENCE => l_tax_in_emp_pence,
X_ASSIGNMENT_NUMBER => l_assignment_number,
X_ORG_NAME => l_org_name,
X_ADDRESS_LINE1 => l_address_line1,
X_ADDRESS_LINE2 => l_address_line2,
X_ADDRESS_LINE3 => l_address_line3,
X_TOWN_OR_CITY => l_town_or_city,
X_REGION_1 => l_region_1,
X_POSTAL_CODE => l_postal_code,
X_DECEASED_FLAG => l_deceased_flag,
X_ISSUE_DATE => l_issue_date,
X_TAX_REF_TRANSFER =>l_tax_ref_transfer,
X_STUDENT_LOAN_FLAG =>l_student_loan_flag,
X_COUNTRY =>l_country,
X_DATE_OF_BIRTH_DD =>l_date_of_birth_dd,
X_DATE_OF_BIRTH_MM =>l_date_of_birth_mm,
X_DATE_OF_BIRTH_YY =>l_date_of_birth_yy,
X_SEX_M =>l_sex_m,
X_SEX_F =>l_sex_f
);
dbms_output.put_line('l_last_name - '||l_last_name);
dbms_output.put_line('l_first_name - '||l_first_name);
dbms_output.put_line('l_dol_dd - '||l_dol_dd);
dbms_output.put_line('l_dol_mm - '||l_dol_mm);
dbms_output.put_line('l_dol_yy - '||l_dol_yy);
dbms_output.put_line('l_tax_code - '||l_tax_code);
dbms_output.put_line('l_month_no - '||l_month_no);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error - '||SUBSTR(SQLERRM,1,200));
END;
/************************************************************/
/************** To get P45 details using query **********************/
SELECT act.assignment_action_id,
max(decode(fue.user_entity_name,'X_TAX_CODE',substr(UPPER(fai.value),1,10))) tax_code,
max(decode(fue.user_entity_name,'X_TERMINATION_DATE',fnd_date.canonical_to_date(fai.value))) date_of_leaving,
max(decode(fue.user_entity_name,'X_MONTH_NUMBER',fnd_number.canonical_to_number(fai.value))) month_number,
max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',substr(UPPER(fai.value),1,20))) ni_number,
max(decode(fue.user_entity_name,'X_TAX_PAID', fnd_number.canonical_to_number(fai.value))) Total_tax_to_date,
max(decode(fue.user_entity_name,'X_TAXABLE_PAY',fnd_number.canonical_to_number(fai.value))) Total_pay_to_date
-- max(decode(fue.user_entity_name,'X_PREVIOUS_TAX_PAID',
-- fnd_number.canonical_to_number(fai.value))) previous_tax_paid,
-- max(decode(fue.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',
-- fnd_number.canonical_to_number(fai.value))) previous_taxable_pay,
-- max(decode(fue.user_entity_name,'X_WEEK_NUMBER',
-- fnd_number.canonical_to_number(fai.value))) week_number,
-- max(decode(fue.user_entity_name,'X_PAYROLL_ID',
-- fnd_number.canonical_to_number(fai.value))) payroll_id,
-- max(decode(fue.user_entity_name,'X_ISSUE_DATE',
-- fnd_date.canonical_to_date(fai.value))) issue_date,
-- max(decode(fue.user_entity_name,'X_DECEASED_FLAG',
-- substr(fai.value,1,1))) deceased_flag,
-- max(decode(fue.user_entity_name,'X_ASSIGNMENT_NUMBER',
-- substr(fai.VALUE,1,30))) assignment_number,
-- max(decode(fue.user_entity_name,'X_FIRST_NAME',
-- substr(UPPER(fai.value),1,40)))||' '||
-- max(decode(fue.user_entity_name,'X_MIDDLE_NAME',
-- substr(UPPER(fai.value),1,40))) first_name,
-- max(decode(fue.user_entity_name,'X_LAST_NAME',
-- substr(UPPER(fai.value),1,40))) last_name,
-- max(decode(fue.user_entity_name,'X_ORGANIZATION_NAME',
-- substr(fai.value,1,60))) organization_name,
-- max(decode(fue.user_entity_name,'X_ADDRESS_LINE1',
-- substr(fai.value,1,60))) address_line1,
-- max(decode(fue.user_entity_name,'X_ADDRESS_LINE2',
-- substr(fai.value,1,60))) address_line2,
-- max(decode(fue.user_entity_name,'X_ADDRESS_LINE3',
-- substr(fai.value,1,60))) address_line3,
-- max(decode(fue.user_entity_name,'X_TOWN_OR_CITY',
-- substr(fai.value,1,30))) town_or_city,
-- max(decode(fue.user_entity_name,'X_COUNTY',
-- substr(fai.value,1,30))) county,
-- max(decode(fue.user_entity_name,'X_POSTAL_CODE',
-- substr(fai.value,1,20))) post_code,
-- max(decode(fue.user_entity_name,'X_STUDENT_LOAN_FLAG',
-- substr(fai.value,1,1))) student_loan_flag,
-- max(decode(fue.user_entity_name,'X_TITLE',
-- substr(UPPER(fai.value),1,40))) title,
-- max(decode(fue.user_entity_name,'X_W1_M1_INDICATOR',
-- substr(fai.value,1,1))) w1_m1_indicator,
-- max(decode(fue.user_entity_name,'X_COUNTRY',
-- substr(fai.value,1,30))) country,
nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),1,2),' ') DATE_OF_BIRTH_DD,
-- nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),4,2),' ') DATE_OF_BIRTH_MM,
-- nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),7,4),' ') DATE_OF_BIRTH_YY,
-- nvl(max (decode(fue.user_entity_name,'X_SEX', substr(fai.value,1,1))),' ') SEX,
--nvl(substr(to_char(SYSDATE,'DD-MM-YYYY'),1,2),' ') TODAY_DD,
-- nvl(substr(to_char(SYSDATE,'DD-MM-YYYY'),4,2),' ') TODAY_MM,
-- nvl(substr(to_char(SYSDATE,'DD-MM-YYYY'),7,4),' ') TODAY_YYYY
FROM pay_assignment_actions act,
pay_payroll_actions ppa,
ff_archive_items fai,
ff_user_entities fue
WHERE act.assignment_action_id = fai.context1
AND act.action_status = 'C'
AND ppa.action_type = 'X'
AND fue.legislation_code = 'GB'
AND fue.business_group_id IS NULL
AND fue.user_entity_id = fai.user_entity_id
AND ppa.payroll_action_id = act.payroll_action_id
and act.assignment_id = p_assignment_id
and act.assignment_action_id = p_assignment_action_id
and ppa.report_type = 'P45'
and ppa.report_category = 'P45'
GROUP BY act.assignment_action_id
ORDER BY assignment_action_id desc;
/***********************************************************/
/************* To get Messages ********************************/
SELECT LINE_SEQUENCE, SOURCE_ID, LINE_TEXT
FROM PAY_GB_SOE_MESSAGE_V
WHERE source_id = 72310872 -- assignment action id
ORDER BY line_sequence
/***********************************************************/
/************* To get payment methods ***************************/
SELECT * FROM pay_org_payment_methods_f
SELECT * FROM pay_personal_payment_methods_f
SELECT * FROM pay_external_accounts
/***********************************************************/
/**************** To get P11D entry details **********************/
SELECT ELEMENT_ENTRY_ID,
ENTRY_TYPE,
INPUT_VALUE_ID,
ADJUSTMENT_TYPE,
ENTRY_VALUE,
EFFECTIVE_START_DATE,
TARGET_ENTRY_ID,
SCREEN_ENTRY_VALUE
FROM PAY_PAYWSMEE_ADJUSTMENT
WHERE input_value_id + 0 = ''
AND assignment_id = 33727
AND element_link_id = 2716
AND TO_DATE ('01-03-2013', 'DD-MM-YYYY') BETWEEN effective_start_date
AND effective_end_date
AND (TARGET_ENTRY_ID = 725955); -- assignment action id
SELECT * FROM PAY_PAYWSMEE_ELEMENT_ENTRIES;
SELECT * FROM PAY_PAYWSMEE_ADJUSTMENT;
SELECT * FROM PAY_ELEMENT_ENTRIES_F ;
SELECT * FROM hr_lookups
WHERE lookup_type ='ENTRY_TYPE' --like '%ENTRY%'
AND meaning='Element Entry';
/***********************************************************/
Your suggestions/inputs are most welcome.
Thanks,
Rajesh