create or replace PACKAGE BODY XXTEST_PAYSLIP_GENERATION_PKG AS
--
/* Title : PAYROLL
* Program Name : XXTEST_PAYSLIP_GEN_PKG.pkh
* Created by :
* Creation Date :
* Called By : Used FOR generating Payslip
* DepENDency : None
* Frequency : As AND when required
* Related documents :
* Tables/views accessed:
*
*
* Change History Log
*=============================================================================================================+
* Date |Name |Ticket / Bug |Version |Remarks
*==============================================================================================================
***************************************************************************************************************
*/
---
--First day of month
---
FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS
vMo VARCHAR2(2);
vYr VARCHAR2(4);
BEGIN
vMo := TO_CHAR(value_in, 'MM');
vYr := TO_CHAR(value_in, 'YYYY');
RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
---
--Number to word conversion
---
FUNCTION notoword(amt number
)return varchar2
AS
ntword varchar2(10000);
BEGIN
select replace(replace(' ' ||
decode(floor(amt/power(10,7)),0,'',
initcap(to_char(to_date(floor(amt/power(10,7)),
'J'),'JSP') || ' crore ')) ||
decode(floor((amt-power(10,7)*floor(amt/power(10,7)))/power(10,5)),0,'',
initcap(to_char(to_date(floor((amt-power(10,7)*floor(amt/power(10,7)))/power(10,5)),
'J'),'JSP') || ' lac ')) ||
decode(floor((amt-power(10,5)*floor(amt/power(10,5)))/power(10,3)),0,'',
initcap(to_char(to_date(floor((amt-power(10,5)*floor(amt/power(10,5)))/power(10,3)),
'J'),'JSP') || ' thousand ')) ||
decode(floor((amt-power(10,3)*floor(amt/power(10,3)))/power(10,2)),0,'',
initcap(to_char(to_date(floor((amt-power(10,3)*floor(amt/power(10,3)))/power(10,2)),
'J'),'JSP') || ' hundred ')) ||
decode(floor((amt-power(10,2)*floor(amt/power(10,2)))/power(10,0)),0,'',
initcap(to_char(to_date(floor((amt-power(10,2)*floor(amt/power(10,2)))/power(10,0)),
'J'),'JSP') || ' ')) ||
'and ' ||
decode(power(10,2)*(amt-floor(amt)),0,'Zero',
initcap(to_char(to_date(power(10,2)*(amt-floor(amt)),
'J'),'JSP') )) ||
' Paise ' ||
'Only','-',' '),' and Zero Paise','')
into ntword
from dual;
return ntword;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function notoword: '||SQLCODE||' '||SQLERRM);
END notoword;
-------------------------------------------------------------------------------------
---Function get_projection_value
---Purpose:Fetches all the elements with projected value
--- from first payroll run of the financial year
--- to till date(the date of this payslip generation)
---Parameter:IN parameter ->Employee Number
--- Element Name
--- Month
--- Year
--- Classification Name(Earnig or Allowance etc
--- Processing Type(Recurring or Non recurring)
--- OUT Parameter->Non recurring value if the element is non recurring
---------------------------------------------------------------------------------------
FUNCTION get_projection_value(p_person_id IN NUMBER
,p_element_name IN VARCHAR2
,P_effective_month IN VARCHAR2
,P_effective_year IN NUMBER
,p_classification_name IN VARCHAR2
,p_processing_type IN OUT VARCHAR2
,p_non_rec OUT NUMBER
-- ,p_rem_flag IN VARCHAR2
,p_context_value IN VARCHAR2
,p_period_left OUT NUMBER
)RETURN NUMBER
AS
TYPE month_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
month_array month_type;
l_assignment_action_id NUMBER := 0;
l_assignment_id NUMBER := 0;
l_element_run_result_value NUMBER := 0;
l_standard_value NUMBER := 0;
l_taxable_value NUMBER := 0;
l_tot_non_rec NUMBER := 0;
l_pay_gen_date DATE;
from_date NUMBER;
total_sum NUMBER:=0;
index_num NUMBER;
period_left NUMBER;
i NUMBER:=1;
p_hra_from_to_date DATE;
value_till_date NUMBER;
l_standard_taxable_value NUMBER;
period_left_DATE DATE;
l_ear_till_date NUMBER;
l_first_fin_date DATE;
l_end_fin_date DATE;
TABLE_TILL_DATE DATE;
l_first_tab_date DATE;
l_element_till_date_value NUMBER;
l_project_value NUMBER;
l_total_projected_value NUMBER;
BEGIN
month_array(1) := 'APR';
month_array(2) := 'MAY';
month_array(3) := 'JUN';
month_array(4) := 'JUL';
month_array(5) := 'AUG';
month_array(6) := 'SEP';
month_array(7) := 'OCT';
month_array(8) := 'NOV';
month_array(9) := 'DEC';
month_array(10):= 'JAN';
month_array(11):= 'FEB';
month_array(12):= 'MAR';
fnd_file.PUT_LINE(fnd_file.LOG,'###########################################');
fnd_file.PUT_LINE(fnd_file.LOG,'Element Name : '||p_element_name);
FOR i IN 1 ..month_array.COUNT
LOOP
BEGIN
IF month_array(i)=substr(P_effective_month,1,3) THEN
index_num:=i;
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Month Array: '||SQLCODE||' '||SQLERRM);
END;
END LOOP;
---------------------------
---Date Formation
---------------------------
IF UPPER(P_effective_month) IN('JAN','FEB','MAR') THEN
from_date:=P_effective_year-1;
ELSE
from_date:=P_effective_year;
END IF;
l_pay_gen_date :=fnd_conc_date.string_to_date('01' || P_effective_month || P_effective_year);
l_first_fin_date :=fnd_conc_date.string_to_date('01' || 'APR' || from_date);
l_end_fin_date :=fnd_conc_date.string_to_date('01' || 'MAR' || (from_date+1));
--
l_assignment_action_id := getAssignmentActionId(l_assignment_id,p_person_id,l_pay_gen_date);
period_left:=12-index_num;
-----------------------------
---IF the element is earning
-----------------------------
IF p_classification_name='Earnings' THEN
fnd_file.PUT_LINE(fnd_file.LOG,'This is a earning element');
-- l_standard_value := get_screen_entry_value(l_assignment_id,p_element_name,l_pay_gen_date);
-----------------------------------------------------------------------------------------
--- IF the payslip generation date is between 01-APR-2010 and 31-MAR-2011
--- then we have to fetch value till august from custom table named "XXTEST_EARNING_TILL_DATE"
--- and pay value from run results for rest of the month.
-------------------------------------------------------------------------------------------
IF l_pay_gen_date BETWEEN TO_DATE('01-APR-2010') AND TO_DATE('31-MAR-2011') THEN
fnd_file.PUT_LINE(fnd_file.LOG,'Payslip generation date is between 01-apr-2010 and 31-mar-2010');
BEGIN
SELECT value,EFFECTIVE_ENDDATE
INTO l_ear_till_date , TABLE_TILL_DATE
FROM XXTEST_EARNING_TILL_DATE
WHERE assignment_id =l_assignment_id
AND element_type_id=(SELECT element_type_id
from pay_element_types_f petf
WHERE element_name = p_element_name
and (petf.legislation_code = 'IN' OR petf.business_group_id =fnd_profile.value('PER_BUSINESS_GROUP_ID')));--fnd_profile.value('PER_BUSINESS_GROUP_ID')));-- )
fnd_file.PUT_LINE(fnd_file.LOG,'Value of this element from custom table : '|| l_ear_till_date ||'Till month' || TABLE_TILL_DATE );
l_first_tab_date := FDAY_OFMONTH(ADD_MONTHS(TABLE_TILL_DATE,1));
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_ear_till_date:=0;
l_first_tab_date:=l_pay_gen_date;
fnd_file.PUT_LINE(fnd_file.LOG,'Value is not present for this element from custom table : '|| l_ear_till_date );
END;
WHILE l_first_tab_date <= l_pay_gen_date
LOOP
l_assignment_action_id := getAssignmentActionId(l_assignment_id,p_person_id,l_first_tab_date);
l_element_run_result_value := l_element_run_result_value + XXTEST_PAYSLIP_GENERATION_PKG.get_run_result_value(p_hra_from_to_date
,l_assignment_action_id
,p_element_name
,'Pay Value'
,l_first_tab_date
,'IP'
);
fnd_file.PUT_LINE(fnd_file.LOG,'Value from run result for this element : '|| l_element_run_result_value ||'For the month of ' || l_first_tab_date);
l_first_tab_date:=add_months(l_first_tab_date,1);
END LOOP;
l_standard_value :=get_screen_entry_value(l_assignment_id,p_element_name,l_pay_gen_date);
/* SELECT months_between(l_end_fin_date,l_pay_gen_date )
INTO period_left
from dual; */
l_project_value := l_standard_value * period_left;
l_element_till_date_value := l_element_run_result_value + l_ear_till_date;
l_total_projected_value := l_element_till_date_value + l_project_value;
fnd_file.PUT_LINE(fnd_file.LOG,'Standard value of the element : '||l_standard_value);
fnd_file.PUT_LINE(fnd_file.LOG,'value till date = custom table value + run result value : '||l_element_till_date_value);
fnd_file.PUT_LINE(fnd_file.LOG,'Projected value = standard value * period left : '||l_project_value);
fnd_file.PUT_LINE(fnd_file.LOG,'total projected value : '||l_total_projected_value);
ELSE
fnd_file.PUT_LINE(fnd_file.LOG,'Payslip generation date is NOT between 01-APR-2010 and 31-MAR-2010');
FOR i IN 1..index_num
LOOP
l_assignment_action_id := getAssignmentActionId(l_assignment_id,p_person_id,l_first_fin_date);
l_element_run_result_value := l_element_run_result_value + get_run_result_value(p_hra_from_to_date
,l_assignment_action_id
,p_element_name
,'Pay Value'
,l_first_fin_date
,'IP'
);
fnd_file.PUT_LINE(fnd_file.LOG,'Value from run result for this element : '|| l_element_run_result_value ||'For the month of ' || l_first_fin_date);
l_first_fin_date:=add_months(l_first_fin_date,1);
END LOOP;
period_left := 12-index_num;
l_standard_value := get_screen_entry_value(l_assignment_id,p_element_name,l_pay_gen_date);
l_element_till_date_value := l_element_run_result_value;
l_project_value := l_standard_value * period_left;
l_total_projected_value := l_element_till_date_value + l_project_value;
fnd_file.PUT_LINE(fnd_file.LOG,'Standard value of the element : '||l_standard_value);
fnd_file.PUT_LINE(fnd_file.LOG,'value till date = total run result value from 1st financial year : '||l_element_till_date_value);
fnd_file.PUT_LINE(fnd_file.LOG,'Projected value = standard value * period left : '||l_project_value);
fnd_file.PUT_LINE(fnd_file.LOG,'total projected value : '||l_total_projected_value);
END IF;
END IF;
IF p_classification_name='Allowances' THEN
fnd_file.PUT_LINE(fnd_file.LOG,'If the element is allowance ');
IF p_element_name='House Rent Allowance' THEN
fnd_file.PUT_LINE(fnd_file.LOG,'If the element is House Rent Allowance ');
------------Modified on 5/DEC/2010------------
/* value_till_date:= apps.XXTEST_in_payroll_utility_pkg.XXTEST_IN_balance_value(l_assignment_action_id
,83 ----Remove this hard coding
,'Allowance Amount'
,'_ASG_COMP_YTD'
,'SOURCE_TEXT2'
,'House Rent Allowance'
); */
l_total_projected_value:=get_run_result_value(p_hra_from_to_date,l_assignment_action_id,'Taxable HRA','Annual Value',l_pay_gen_date,'IP');
fnd_file.PUT_LINE(fnd_file.LOG,'Annual value of Taxable HRA :'|| l_total_projected_value);
ELSE
value_till_date:= apps.XXTEST_in_payroll_utility_pkg.XXTEST_IN_balance_value(l_assignment_action_id
,83
,'Taxable Allowances'
,'_ASG_COMP_YTD'
,'SOURCE_TEXT2'
,p_context_value
) ;
fnd_file.PUT_LINE(fnd_file.LOG,'Value till date from balance :'|| value_till_date);
IF p_element_name LIKE ('%Reimbursement') THEN
l_total_projected_value:= value_till_date;
fnd_file.PUT_LINE(fnd_file.LOG,'Value till date from balance is the total projection value in case of Reimbursement :'|| l_total_projected_value);
ELSE
l_standard_taxable_value := get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Standard Taxable Value',l_pay_gen_date,'IP');
l_project_value := l_standard_taxable_value * period_left;
l_total_projected_value := value_till_date + l_project_value;
fnd_file.PUT_LINE(fnd_file.LOG,'*********************');
fnd_file.PUT_LINE(fnd_file.LOG,'Standard Taxable value : '|| l_standard_taxable_value);
fnd_file.PUT_LINE(fnd_file.LOG,'Projected value : '|| l_project_value);
fnd_file.PUT_LINE(fnd_file.LOG,' Total Projected value : '|| l_total_projected_value);
fnd_file.PUT_LINE(fnd_file.LOG,'*********************');
END IF;
END IF;
END IF;
IF p_processing_type='N' THEN
fnd_file.PUT_LINE(fnd_file.LOG,'This element is non recurring element');
IF p_classification_name='Allowances' THEN
fnd_file.PUT_LINE(fnd_file.LOG,'This element is non recurring element and allowance');
/* l_total_projected_value:=apps.XXTEST_in_payroll_utility_pkg.XXTEST_IN_balance_value(l_assignment_action_id
,83
,'Taxable Allowances'
,'_ASG_COMP_YTD'
,'SOURCE_TEXT2'
,p_context_value
) ; */
l_total_projected_value:= value_till_date;
fnd_file.PUT_LINE(fnd_file.LOG,' Total Projected value is the balance value : '|| l_total_projected_value);
ELSIF p_classification_name='Earnings' THEN
fnd_file.PUT_LINE(fnd_file.LOG,'This element is non recurring element and earning');
l_total_projected_value:=l_element_till_date_value;
fnd_file.PUT_LINE(fnd_file.LOG,' Total Projected value is the value till date : '|| l_total_projected_value);
END IF;
p_non_rec :=l_total_projected_value;
END IF;
p_period_left:=period_left;
dbms_output.put_line(l_total_projected_value);
RETURN l_total_projected_value ;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function get_projection_value: '||SQLCODE||' '||SQLERRM);
END;
---------------------------------------------------------------------------------------------------------------------------
--- Function: getAssignmentActionId
--- Purpose : This function fetches assignment action id and assignment id(out parameter) against a employee number,date
---------------------------------------------------------------------------------------------------------------------------
FUNCTION getAssignmentActionId(p_assignment_id OUT NUMBER
,p_person_id IN NUMBER
,p_effective_date IN DATE
)RETURN NUMBER
AS
assignment_action_id NUMBER:=0;
BEGIN
SELECT MAX(paa.assignment_action_id)
,paa.assignment_id
INTO assignment_action_id,p_assignment_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_time_periods ptp
,per_all_assignments_f paaf
WHERE PPA.action_type in ('R','Q','I','B')
AND PPA.action_status = 'C'
AND paa.payroll_action_id = ppa.payroll_action_id
AND PAA.action_status = 'C'
AND ppa.payroll_id = ptp.payroll_id
AND ptp.time_period_id = ppa.time_period_id
AND paa.assignment_id = paaf.assignment_id
AND paaf.person_id = p_person_id
AND paaf.business_group_id =fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND ppa.effective_date BETWEEN p_effective_date AND last_day(p_effective_date)
AND (p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
OR paaf.effective_start_date BETWEEN p_effective_date AND last_day(p_effective_date))
GROUP BY paa.assignment_id;
RETURN assignment_action_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN assignment_action_id;
WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function getAssignmentActionId: '||SQLCODE||' '||SQLERRM);
END getAssignmentActionId;
----------------------------------------------------------------------------------------------------
---Function :get_run_result_value
---Purpose :This function fetches value
---Parameter: IN Parameter->Assignment action id
--- Element Name
--- Input value name
--- Type
--- date
--- OUT Parameter->Date (when fetches a date from run result instead of number )
----------------------------------------------------------------------------------------------------
FUNCTION get_run_result_value (p_hra_from_to_date OUT DATE
,p_assignment_action_id IN NUMBER
,p_element_name IN VARCHAR2
,p_input_value_name IN VARCHAR2
,p_date IN DATE
,p_type IN VARCHAR2
)RETURN NUMBER
AS
l_value VARCHAR2(1000);--NUMBER := '0';
l_value_number NUMBER:=0;
BEGIN
SELECT sum(prrv.result_value) --fnd_number.canonical_to_number(prrv.result_value) result_value
INTO l_value
FROM pay_run_result_values prrv
,pay_run_results prr
,pay_element_types_f petf
,pay_input_values_f pivf
WHERE prrv.run_result_id = prr.run_result_id
AND prr.element_type_id = petf.element_type_id
AND pivf.input_value_id = prrv.input_value_id
AND prr.assignment_action_id = p_assignment_action_id
AND ( (pivf.name = p_input_value_name AND p_type='IP' )
OR (pivf.name IN('Taxable Value' ,'Taxable Amount') AND p_type='TA_TV' )
)
AND petf.element_name = p_element_name
AND (petf.legislation_code = 'IN' OR petf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---FND_PROFILE.value('PER_BUSINESS_GROUP_ID')) --83)-- FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND (pivf.legislation_code = 'IN' OR pivf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---FND_PROFILE.value('PER_BUSINESS_GROUP_ID')) --83)-- FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND petf.element_type_id = pivf.element_type_id
AND p_date BETWEEN petf.effective_start_date AND petf.effective_end_date
AND p_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
BEGIN
l_value_number:=TO_NUMBER(NVL(l_value,0));
RETURN l_value_number;
EXCEPTION
WHEN OTHERS THEN
p_hra_from_to_date:=FND_CONC_DATE.STRING_TO_DATE(l_value);
RETURN 0;
END;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function get_run_result_value: '||SQLCODE||' '||SQLERRM);
RETURN 0;
END get_run_result_value;
---------------------------------------------------------------------------
--- FUNCTION get_screen_entry_value
----Purpose :To fetch screen enrty value of an input value of a element
----------------------------------------------------------------------------
FUNCTION get_screen_entry_value(p_assignment_id NUMBER,
p_element_name VARCHAR2,
p_date DATE
)RETURN NUMBER
AS
l_basic_rate NUMBER := 0;
BEGIN
SELECT NVL(SUM(fnd_number.canonical_to_number(peevf.screen_entry_value)),0)
INTO l_basic_rate
FROM pay_element_types_f petf,
pay_element_entries_f peef,
pay_element_entry_values_f peevf,
pay_input_values_f pivf
WHERE peef.element_entry_id = peevf.element_entry_id
AND peef.assignment_id = p_assignment_id
AND petf.element_name = p_element_name
AND (petf.legislation_code = 'IN' OR petf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---fnd_profile.value('PER_BUSINESS_GROUP_ID')) --83)-- fnd_profile.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.NAME IN ('Standard Value','Allowance Amount')
AND (pivf.legislation_code = 'IN' OR pivf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---fnd_profile.value('PER_BUSINESS_GROUP_ID')) --83)-- fnd_profile.value('PER_BUSINESS_GROUP_ID'))
AND petf.element_type_id = pivf.element_type_id
AND peevf.input_value_id = pivf.input_value_id
AND p_date BETWEEN petf.effective_start_date AND petf.effective_end_date
AND p_date BETWEEN peef.effective_start_date AND peef.effective_end_date
AND p_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
AND p_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
RETURN l_basic_rate;
EXCEPTION
WHEN no_data_found THEN
RETURN l_basic_rate;
WHEN OTHERS THEN
RETURN l_basic_rate;
fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function get_screen_entry_value: '||SQLCODE||' '||SQLERRM);
END get_screen_entry_value;
-------------------------------------------------------------------------------
--- PROCEDURE XXTEST_PAYSLIP_GENERATION_PRC
----This is the main procedure which will be called from concurrent program
----Parameter:IN Parameter->1)employee number
---- 2)month
---- 3)Year
--------------------------------------------------------------------------------
PROCEDURE XXTEST_PAYSLIP_GENERATION_PRC(p_errbuf OUT VARCHAR2
,p_retcode OUT NUMBER
, p_person_id IN NUMBER
,p_month IN VARCHAR2
,p_year IN NUMBER
)
IS
--Cursor Declaration
----------------------------------------------------
---CURSOR get_personal_details
---Purpose :This cursor fetches employee details
---Parameter->Assignment id and date
----------------------------------------------------
CURSOR get_personal_details (c_asg_id NUMBER
,c_date DATE
) IS
SELECT papf.employee_number Emp_no
,initcap(papf.full_name) Emp_name
,pj.name Designation
,pg.name Grade_name
,hrl.location_code Location_name
,papf.original_date_of_hire Original_date_of_hire
,pea.segment1 Acc_no
,HR_GENERAL.DECODE_LOOKUP('IN_BANK',pea.SEGMENT3)||' '||HR_GENERAL.DECODE_LOOKUP('IN_BANK_BRANCH',pea.SEGMENT4) Bank
-- ,pea.segment3|| pea.segment4 Bank
,papf.per_information4 Pan_no
,papf.per_information8 Pf_no
,papf.per_information9 Esi_no
,haou.name Department
,popmf.org_payment_method_name Pay_mode
-- ,ppt.payment_type_name Pay_mode
FROM per_all_people_f papf
,per_all_assignments_f paaf
,per_jobs pj
,per_grades pg
,hr_locations hrl
,pay_personal_payment_methods_f pppmf
,pay_external_accounts pea
,per_periods_of_service ppos
,hr_all_organization_units haou
,pay_payment_types ppt
,pay_org_payment_methods_f popmf
WHERE papf.person_id = paaf.person_id
AND paaf.job_id = pj.job_id(+)
AND paaf.grade_id = pg.grade_id(+)
AND paaf.location_id = hrl.location_id(+)
AND paaf.assignment_id = pppmf.assignment_id(+)
AND pea.external_account_id (+) = pppmf.external_account_id
AND pppmf.ORG_PAYMENT_METHOD_ID = popmf.ORG_PAYMENT_METHOD_ID(+)
AND ppos.period_of_service_id = paaf.period_of_service_id
AND haou.organization_id = paaf.organization_id
AND popmf.payment_type_iD = ppt.payment_type_id (+)
AND paaf.assignment_id = c_asg_id
--AND c_date BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND ( c_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND c_date BETWEEN PAAF.effective_start_date AND paaf.effective_end_date
or (
ppos.actual_termination_date BETWEEN c_date AND last_day( c_date)
AND ppos.actual_termination_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND ppos.actual_termination_date BETWEEN PAAF.effective_start_date AND PAAF.effective_end_date
)
or (
papf.original_date_of_hire BETWEEN c_date AND last_day( c_date)
AND papf.original_date_of_hire BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.original_date_of_hire BETWEEN paaf.effective_start_date AND paaf.effective_end_date
)
) ;
--------------------------------------------------------------------------------------------------------------------------
---CURSOR get_earn_deduct_elements
---This cursor fetches all the earnign and allowance elements from run result processed in a particular month when type is EA
---and deduction elements when type is D .The flag denotes whether the element is retro element or not.
----------------------------------------------------------------------------------------------------------------------------
CURSOR get_earn_deduct_elements (p_assign_action_id IN NUMBER
,p_date IN DATE
,p_type IN VARCHAR2
)IS
SELECT DISTINCT petf.element_type_id element_type_id
, petf.element_name element_name
,decode(nvl(prcuv.creator_id ,0),0,'T','F') Retro_flag
FROM pay_run_results prr
, pay_element_types_f petf
, pay_input_values_f pivf
, pay_element_classifications pec
, pay_retro_component_usages_v prcuv
, pay_element_span_usages pesu
WHERE prr.element_type_id = petf.element_type_id
AND prr.assignment_action_id = p_assign_action_id
AND pivf.name = 'Pay Value'
AND (petf.legislation_code = 'IN' OR petf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND (pivf.legislation_code = 'IN' OR pivf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND petf.element_type_id = pivf.element_type_id
AND pec.classification_id = petf.classification_id
AND prcuv.creator_id (+) =petf.element_type_id
AND pesu.retro_component_usage_id(+) =prcuv.retro_component_usage_id
AND pec.legislation_code ='IN'
AND (
( pec.classification_name IN ('Voluntary Deductions' ,'Involuntary Deductions','Tax Deductions','Employee Charges') and p_type ='D')
OR
( pec.classification_name IN('Earnings','Allowances') and p_type ='EA')
)
AND p_date BETWEEN petf.effective_start_date AND petf.effective_end_date
AND p_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND petf.element_name not like 'Retro%';
-------------------------------------------------------------------------
--- CURSOR retroElement
---Purpose:This cursor fetches the corresponding retro element if exits
-------------------------------------------------------------------------
CURSOR retroElement(p_element_type_id NUMBER
) IS
SELECT petf.element_name,
petf.element_type_id
FROM pay_retro_component_usages_v prcuv
,pay_element_span_usages pesu
,pay_element_types_f petf
WHERE prcuv.creator_id = p_element_type_id
AND prcuv.retro_component_usage_id = pesu.retro_component_usage_id
AND petf.element_type_id = pesu.retro_element_type_id;
-------------------------------------------------------------------------
--- CURSOR retroBaseElement
---It fetches the base or creator element of a retro element
-------------------------------------------------------------------------
CURSOR retroBaseElement(p_element_type_id NUMBER
) IS
SELECT petf.element_name,
petf.element_type_id
FROM pay_retro_component_usages_v prcuv
, pay_element_span_usages pesu
, pay_element_types_f petf
WHERE prcuv.retro_component_usage_id = pesu.retro_component_usage_id
AND petf.element_type_id = prcuv.creator_id
AND pesu.retro_element_type_id =p_element_type_id;
------------------------------------------------------------------------------------------------------------
--- CURSOR projection
--- This picks all the elements which are processed from 1st month(01-Apr) of financial year to till date.
------------------------------------------------------------------------------------------------------------
CURSOR projection (c_asg_id NUMBER
,c_first_date DATE
,c_date DATE
)IS
SELECT distinct petf.element_name element_name
, petf.processing_type processing_type
, pec.classification_name classification_type
, ppa.effective_date
-- ,PETEI.EEI_INFORMATION1 rem_flag
,PETF.element_information1 context_value
FROM pay_run_results prr
,pay_element_types_f petf
,pay_assignment_actions paa
,pay_element_classifications pec
,pay_payroll_actions ppa
,PAY_ELEMENT_TYPE_EXTRA_INFO PETEI
WHERE prr.element_type_id = petf.element_type_id
AND paa.assignment_action_id = prr.assignment_action_id
AND pec.classification_id = petf.classification_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND PETEI.ELEMENT_TYPE_ID(+) =PETF.ELEMENT_TYPE_ID
AND PPA.action_type IN ('R','Q','I','B')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND paa.assignment_id = c_asg_id
AND (petf.legislation_code = 'IN' OR petf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))--83)-- FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND petf.element_name NOT LIKE 'Retro%'
AND pec.classification_name IN ('Earnings','Allowances')
AND ppa.effective_date BETWEEN c_first_date AND last_day(c_date);
-- AND c_first_date BETWEEN petf.effective_start_date AND petf.effective_end_date
-- AND last_day(c_date) BETWEEN petf.effective_start_date AND petf.effective_end_date
/* SELECT distinct petf.element_name element_name
, petf.processing_type processing_type
, pec.classification_name classification_type
, ppa.effective_date
FROM pay_run_results prr
,pay_element_types_f petf
,pay_assignment_actions paa
,pay_element_classifications pec
,pay_payroll_actions ppa
WHERE prr.element_type_id = petf.element_type_id
AND paa.assignment_action_id = prr.assignment_action_id
AND pec.classification_id = petf.classification_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND PPA.action_type IN ('R','Q','I','B')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND paa.assignment_id = c_asg_id
AND (petf.legislation_code = 'IN' OR petf.business_group_id =83)-- FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND petf.element_name NOT LIKE 'Retro%'
AND pec.classification_name IN ('Earnings','Allowances')
AND ppa.effective_date BETWEEN c_first_date AND last_day(c_date);
-- AND c_first_date BETWEEN petf.effective_start_date AND petf.effective_end_date
-- AND last_day(c_date) BETWEEN petf.effective_start_date AND petf.effective_end_date */
------------------------------------------------------------------------------------------------------------
---CURSOR get_house_rent_info
---This cursor fetches the details of 'House Rent Information' if it is attached to an employee
------------------------------------------------------------------------------------------------------------
CURSOR get_house_rent_info(c_asg_id NUMBER
)IS
SELECT SUM(prrv.result_value),COUNT(*)
FROM pay_element_types_f petf,
pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prrv
WHERE paa.assignment_id = c_asg_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = petf.element_type_id
AND prr.run_result_id = prrv.run_result_id
AND petf.element_name = 'House Rent Information'; /***we will do it by out parameter ***/
-------
-------
--Variables Declaration
l_date DATE:= NULL;
l_hra_from_to_date DATE;
l_date_of_hire DATE;
l_start_date DATE;
l_end_date DATE;
l_from DATE;
to_from DATE;
l_emp_no VARCHAR2(100);
l_emp_name VARCHAR2(100);
l_designation VARCHAR2(100);
l_grade VARCHAR2(100);
l_location VARCHAR2(100);
l_acc_no VARCHAR2(100);
l_bank VARCHAR2(100);
l_pan_no VARCHAR2(100);
l_pf_no VARCHAR2(100);
l_esi_no VARCHAR2(100);
l_paymode VARCHAR2(40);
l_department VARCHAR2(100);
l_furniture_cost VARCHAR2(100);
l_words VARCHAR2(1000);
l_site_days NUMBER;
l_payable_days NUMBER;
l_total_days NUMBER;
l_retro_value NUMBER;
l_gross NUMBER:=0;
l_deduction NUMBER:=0;
l_rent_paid NUMBER;
l_row_count NUMBER;
l_run_result_value NUMBER;
l_hra_exempted NUMBER;
l_comp_rent_paid NUMBER;
l_comp_from NUMBER;
l_comp_to NUMBER;
l_taxable_rfa NUMBER;
l_taxable_hra NUMBER;
l_net NUMBER:=0;
l_assignment_action_id NUMBER:= 0;
l_assignment_id NUMBER:= 0;
l_result NUMBER;
-- chap_V1_A_total VARCHAR2(150);
chap_V1_A_total NUMBER:=0;
l_standard_value NUMBER;
l_tot_non_rec NUMBER;
l_total_n_rec NUMBER:=0;
l_taxable_income NUMBER;
l_inc_tax_liability NUMBER;
l_surcharge NUMBER;
l_edu_cess NUMBER;
l_net_tax NUMBER;
l_tax_this_mon NUMBER;
l_proff_tax NUMBER;
l_other_income NUMBER;
l_other_inc_id NUMBER;
l_other_inc NUMBER;
l_chap_VIA_id NUMBER;
l_chap_VIA NUMBER;
row_count NUMBER:=1;
element_name VARCHAR2(1000);
l_processing_type VARCHAR2(100);
l_element_type_id NUMBER;
l_element_type_id1 NUMBER;
element_value NUMBER;
retro_value NUMBER;
ret_date DATE;
l_first_date DATE;
rec_count NUMBER:=1;
l_gross_salary NUMBER;
count1 NUMBER:=0;
l_Sec_HE_Cess NUMBER;
l_exempted_amt NUMBER;
l_business_group_id NUMBER;
l_tax_id NUMBER;
l_pf_def_bal_id NUMBER;
l_pf_till_date NUMBER;
l_period_left NUMBER;
l_pf_val NUMBER;
----
--Array Declarations
----
Type var_type is table of varchar2(1000) index by BINARY_integer;
TYPE earning_ele_name IS RECORD(element_name VARCHAR2(70)
,run_result_value NUMBER(16,2)
,processing_type VARCHAR2(20)
);
TYPE array_earning IS TABLE OF earning_ele_name INDEX BY BINARY_INTEGER;
array_earn array_earning ;
TYPE Chapter_V1A is RECORD (element_name varchar2(70),input_name varchar2(70),res_value number);
chap_V1A Chapter_V1A;
type array_chap_V1A is table of Chapter_V1A index by binary_integer;
array_chap_ele array_chap_V1A;
I NUMBER:=1;
J NUMBER:=1;
--------------------type for retro----------------------------------------
TYPE ELE_RET_REC IS RECORD ( element_name VARCHAR2(70),
element_value NUMBER(16,2),
retro_element VARCHAR2(100),
retro_value NUMBER,
total NUMBER,
deduction varchar2(100),
ded_value number
);
TYPE ele_ret_tab IS TABLE OF ELE_RET_REC INDEX BY BINARY_INTEGER;
ele_ret_arr ele_ret_tab ;
--------------------------------------------------------------------------
BEGIN
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<?xml version="1.0" ?>');
-----
--1)Formation of date of payslip generation
--2)Formation of first date of financial year
-----
l_date := FND_CONC_DATE.STRING_TO_DATE('01' ||UPPER(p_month)||p_year);
l_first_date:= FND_CONC_DATE.STRING_TO_DATE('01' ||'APR'||p_year);
l_assignment_action_id:=getAssignmentActionId(l_assignment_id, p_person_id,l_date);
-----
--If the assignment action id is found then there is payroll run
--If not then no payroll process for the particular month
-----
IF l_assignment_action_id IS NULL OR l_assignment_action_id =0 THEN
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Message>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<SHOW_ERR>Y</SHOW_ERR>');
fnd_file.PUT_LINE(fnd_file.LOG,'1: Assignment Action id'||l_assignment_action_id );
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Message>');
ELSE
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<PAYSLIP_DETAILS>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<SHOW_ERR>N</SHOW_ERR>');
fnd_file.PUT_LINE(fnd_file.LOG,'2:Assignment Action id'||l_assignment_action_id );
---
--Business group id
---
l_business_group_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
---
--Tax unit id
---
begin
select tax_unit_id
into l_tax_id
from pay_assignment_actions
where assignment_action_id = l_assignment_action_id;
exception when others then
fnd_file.PUT_LINE(fnd_file.LOG,'Tax unit id');
l_tax_id:=0;
end;
-----
--Chapter VI-A Deduction Elements
-----
fnd_file.PUT_LINE(fnd_file.LOG,'2: Assignment id'||l_assignment_id);
chap_V1A.element_name:='CGHS Contribution';
chap_V1A.input_name :='Exemption Amount';
array_chap_ele(1) :=chap_V1A;
chap_V1A.element_name:='Deduction under Section 80CCE';
chap_V1A.input_name :='Investment Amount';
array_chap_ele(2) :=chap_V1A;
chap_V1A.element_name:='Deduction under Section 80CCF';
chap_V1A.input_name :='Exemption Amount';
array_chap_ele(3) :=chap_V1A;
chap_V1A.element_name:='Deduction under Section 80D';
chap_V1A.input_name :='Maximum Exemption';
array_chap_ele(4) :=chap_V1A;
chap_V1A.element_name:='Deduction under Section 80DD';
chap_V1A.input_name :='Max Exemption';
array_chap_ele(5) :=chap_V1A;
chap_V1A.element_name:='Deduction under Section 80DDB';
chap_V1A.input_name :='Maximum Exemption';
array_chap_ele(6):=chap_V1A;
chap_V1A.element_name:='Deduction under Section 80E';
chap_V1A.input_name :='Interest Amount';
array_chap_ele(7):=chap_V1A;
chap_V1A.element_name:='Deduction under Section 80GGA';
chap_V1A.input_name :='Maximum Exemption';
array_chap_ele(8) :=chap_V1A;
chap_V1A.element_name:='Deduction under Section 80U';
chap_V1A.input_name :='Maximum Exemption';
array_chap_ele(9) :=chap_V1A;
chap_V1A.element_name:='Deferred Annuity';
chap_V1A.input_name :='Qualifying Amount';
array_chap_ele(10) :=chap_V1A;
chap_V1A.element_name:='LIC Salary Savings Scheme';
chap_V1A.input_name :='Qualifying Amount';
array_chap_ele(11) :=chap_V1A;
chap_V1A.element_name:='Life Insurance Premium';
chap_V1A.input_name :='Qualifying Amount';
array_chap_ele(12) :=chap_V1A;
chap_V1A.element_name:='Pension Fund 80CCC';
chap_V1A.input_name :='Qualifying Amount';
array_chap_ele(13) :=chap_V1A;
chap_V1A.element_name:='Senior Citizens Savings Scheme';
chap_V1A.input_name :='Qualifying Amount';
array_chap_ele(14) :=chap_V1A;
----
--Personal Details and calculation of total days in a month
----
fnd_file.PUT_LINE(fnd_file.LOG,'Before open fetch');
OPEN get_personal_details(l_assignment_id,l_date);
FETCH get_personal_details
INTO l_emp_no ,
l_emp_name ,
l_designation ,
l_grade ,
l_location ,
l_date_of_hire,
l_acc_no ,
l_bank ,
l_pan_no ,
l_pf_no ,
l_esi_no ,
l_department ,
l_paymode ;
CLOSE get_personal_details;
l_total_days := (last_day(l_date)-l_date) + 1 ;
fnd_file.PUT_LINE(fnd_file.LOG,'After open fetch');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Personal_details>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Employee_Name><![CDATA['|| l_emp_no || ']]></Employee_Name>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Emp_No><![CDATA[' ||l_emp_name || ']]></Emp_No>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Designation><![CDATA[' ||l_designation || ']]></Designation>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Grade><![CDATA[' ||l_grade || ']]></Grade>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Location><![CDATA[' ||l_location || ']]></Location>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Date_of_hire>' ||l_date_of_hire || '</Date_of_hire>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<ACC_no><![CDATA[' ||l_acc_no || ']]></ACC_no>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Bank><![CDATA[' ||l_bank || ']]></Bank>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Pan>' ||l_pan_no || '</Pan>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Pf_no>' ||l_pf_no || '</Pf_no>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Esi_no>' ||l_esi_no || '</Esi_no>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Site_days>' ||l_site_days || '</Site_days>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Total_days>' ||l_total_days || '</Total_days>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Department><![CDATA[' ||l_department || ']]></Department>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Paymode><![CDATA[' ||l_paymode || ']]></Paymode>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<month>' ||trim(to_char(l_date,'Month')) || '</month>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<year>' ||trim(to_char(p_year)) || '</year>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Personal_details>');
----
--Start of loop for Fetching earning elements and corresponding retro elements
----
FOR I IN get_earn_deduct_elements(l_assignment_action_id,l_date,'EA')
LOOP
element_value:=XXTEST_PAYSLIP_GENERATION_PKG.get_run_result_value(ret_date,l_assignment_action_id,
I.element_name,'Pay Value',l_date,'IP');
----
--If the element fetched from run result is not retro
----
IF I.Retro_flag='F' THEN
ele_ret_arr(rec_count).element_name :=I.element_name;
ele_ret_arr(rec_count).element_value:=element_value;
----
--To fetch corresponding retro element of a non retro element
----
OPEN retroElement(I.element_type_id);
FETCH retroElement INTO element_name,l_element_type_id;
----
--If the retro element is fetched then calculation of non retro element value
----
IF retroElement%ROWCOUNT>0 THEN
retro_value:=XXTEST_PAYSLIP_GENERATION_PKG.get_run_result_value(ret_date,l_assignment_action_id,
element_name,'Pay Value',l_date,'IP');
ele_ret_arr(rec_count).retro_element:=element_name;
ele_ret_arr(rec_count).retro_value :=retro_value;
END IF;
CLOSE retroElement;
rec_count:=rec_count+1;
END IF;
----
--If the element fetched from run result is retro
----
IF I.Retro_flag='T' THEN
----
--To fetch corresponding base/creator element of a retro element
----
OPEN retroBaseElement(I.element_type_id);
FETCH retroBaseElement INTO element_name,l_element_type_id;
IF retroBaseElement%ROWCOUNT>0 THEN
----
--To check if the base/creator element fetched is processed in the present month by using assignment action id
----
BEGIN
SELECT element_type_id
INTO l_element_type_id1
FROM pay_run_results PRR
WHERE PRR.element_type_id = l_element_type_id
AND prr.assignment_action_id = l_assignment_action_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ele_ret_arr(rec_count).element_name :=element_name;
ele_ret_arr(rec_count).element_value:=0;
ele_ret_arr(rec_count).retro_element:=I.element_name;
ele_ret_arr(rec_count).retro_value :=element_value;
rec_count:=rec_count+1;
WHEN OTHERS THEN
NULL;
fnd_file.PUT_LINE(fnd_file.LOG,'ERROR : '||SQLCODE||' '||SQLERRM);
END;
----
--If no rows fetched then its neither a retro element or creator/base element
----
ELSE
ele_ret_arr(rec_count).element_name :=I.element_name;
ele_ret_arr(rec_count).element_value:=element_value;
ele_ret_arr(rec_count).retro_element:=' ';
ele_ret_arr(rec_count).retro_value :=0;
rec_count:=rec_count+1;
END IF;
CLOSE retroBaseElement;
END IF;
END LOOP; --End of loop for Fetching earning elements and corresponding retro elements
fnd_file.PUT_LINE(fnd_file.LOG,'30');
----
--Fetching deduction elements by passing assignment action id,date,type D means deduction
--and sum of all deduction element values
----
rec_count:=1;
For rec_get_deduction_elements IN get_earn_deduct_elements(l_assignment_action_id,l_date,'D')
LOOP
l_run_result_value :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,rec_get_deduction_elements.element_name,'Pay Value',l_date,'IP');
l_deduction :=l_deduction+l_run_result_value;
ele_ret_arr(rec_count).deduction :=rec_get_deduction_elements.element_name;
ele_ret_arr(rec_count).ded_value :=nvl(l_run_result_value,0);
rec_count:=rec_count+1;
/*
IF l_run_result_value <> 0 THEN
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_details>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_Element_Name><![CDATA[' || rec_get_deduction_elements.element_name || ']]></Deduction_Element_Name>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_Element_Value>' ||l_run_result_value || ' </Deduction_Element_Value>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Deduction_details>');
END IF; */
END LOOP;
----
--Calculation of gross and rate of earning elements
----
FOR I IN 1..ele_ret_arr.COUNT
LOOP
ele_ret_arr(I).total:=nvl(ele_ret_arr(I).element_value,0) + nvl(ele_ret_arr(I).RETRO_VALUE,0);
l_standard_value :=get_screen_entry_value(l_assignment_id,ele_ret_arr(I).element_name,l_date);
l_gross :=l_gross+NVL(ele_ret_arr(I).total,0);
IF
ele_ret_arr(I).element_value <> 0 OR ele_ret_arr(I).RETRO_VALUE <> 0 THEN
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<EARNING_ROW>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<EARNING_ELEMENT_NAME><![CDATA['|| ele_ret_arr(I).element_name || ']]></EARNING_ELEMENT_NAME>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable>' ||round(ele_ret_arr(I).element_value,1)|| '</Payable>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Rate>' ||l_standard_value || '</Rate>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Arrears>' ||ele_ret_arr(I).RETRO_VALUE || '</Arrears>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Total>' ||round(ele_ret_arr(I).total,1) || '</Total>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduct>' ||ele_ret_arr(I).deduction || '</Deduct>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Ded_value>' ||round(ele_ret_arr(I).ded_value,1) || '</Ded_value>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'</EARNING_ROW>');
END IF;
END LOOP;
----
--Calculation of net pay
----
-- l_net:=l_gross-l_deduction;
-- l_words:=notoword(l_net);
IF l_gross > 0 THEN
l_net:=l_gross-l_deduction;
ELSE
l_net:=l_deduction;
END IF;
l_words:=notoword(l_net);
----
--Fetching elements for projection coloumn and calculation of total non recurring element values
----
FOR rec_projection in projection(l_assignment_id,l_first_date,l_date)
LOOP
DBMS_OUTPUT.PUT_LINE(rec_projection.element_name);
l_processing_type :=rec_projection.processing_type;
l_run_result_value :=get_projection_value(p_person_id,
rec_projection.element_name,
p_month,
p_year,
rec_projection.classification_type,
l_processing_type,
l_tot_non_rec
-- ,rec_projection.rem_flag
,rec_projection.context_value
,l_period_left
);
array_earn(J).element_name :=rec_projection.element_name;
array_earn(J).run_result_value:=l_run_result_value;
array_earn(J).processing_type :=l_processing_type;
l_total_n_rec :=l_total_n_rec+NVL(l_tot_non_rec,0);
J:=J+1;
END LOOP;
FOR j IN 1 .. array_earn.COUNT
LOOP
IF array_earn(J).processing_type = 'R' THEN
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<ANNUAL_EARNING>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<EARNING_ELEMENT_NAME><![CDATA['||array_earn(J).element_name || ']]></EARNING_ELEMENT_NAME>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable>' ||array_earn(J).run_result_value || '</Payable>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'</ANNUAL_EARNING>');
END IF;
END LOOP;
--- Added on 7/DEC/2010
--PPF contribution
---
l_pf_def_bal_id := XXTEST_pay_payroll_reports_pkg.get_defined_bal_id('Employee Statutory PF Contribution' ,'_ASG_YTD',l_business_group_id,'IN');
l_pf_till_date := pay_balance_pkg.get_value(l_pf_def_bal_id, l_assignment_action_id);
l_standard_value := get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Employee Statutory PF Contribution','Standard Value',l_date,'IP');
l_pf_val:=l_pf_till_date + (l_standard_value * l_period_left);
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<PF>' || l_pf_val || '</PF>');
fnd_file.PUT_LINE(fnd_file.LOG,': Defined Balance id' ||l_pf_def_bal_id );
fnd_file.PUT_LINE(fnd_file.LOG,': Value till date of PF' ||l_pf_till_date );
fnd_file.PUT_LINE(fnd_file.LOG,': standard value OF PF' ||l_standard_value );
fnd_file.PUT_LINE(fnd_file.LOG,': Total PF Projection ' ||l_pf_val );
----
--House rent information
----
l_from :=FND_CONC_DATE.STRING_TO_DATE('01' ||'APR'||p_year);
to_from :=add_months(l_from,11);
OPEN get_house_rent_info(l_assignment_id);
FETCH get_house_rent_info
INTO l_rent_paid
,l_row_count;
CLOSE get_house_rent_info;
-----Modified on 5/DEC/2010------------
IF l_row_count >1 THEN
-- l_taxable_hra :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Taxable HRA','Pay Value',l_date,'IP');
l_taxable_hra :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Taxable HRA','Annual Value',l_date,'IP');
l_exempted_amt:= APPS.XXTEST_in_payroll_utility_pkg.XXTEST_get_exempted_amt(l_business_group_id,l_assignment_action_id,l_tax_id,'House Rent Allowance',l_date);
l_hra_exempted:=l_exempted_amt - l_taxable_hra;
fnd_file.PUT_LINE(fnd_file.LOG,'############# Issues Annual value in Taxable HRA:' ||' '||l_taxable_hra);
fnd_file.PUT_LINE(fnd_file.LOG,'############# Issues Taxable exempted amt from XXTEST_in_payroll_utility_pkg :' ||' '||l_exempted_amt);
fnd_file.PUT_LINE(fnd_file.LOG,'############# HRA exempted is :' ||' '||l_hra_exempted);
----------------------------------------
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<HRA_CALCULATION>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Rent_Paid>' || l_rent_paid || '</Rent_Paid>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<From>' || l_from || '</From>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<To>' || to_from || '</To>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<HRA_Exempted>' ||l_hra_exempted || '</HRA_Exempted>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Gross>' ||l_gross || '</Gross>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction>' ||l_deduction || '</Deduction>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Net>' ||l_net || '</Net>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<words>' ||l_words || '</words>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<month>' ||to_char(l_date,'Month') || '</month>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<year>' ||p_year || '</year>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</HRA_CALCULATION>');
END IF;
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Others_N>' ||l_total_n_rec || '</Others_N>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Gross>' ||l_gross || '</Gross>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction>' ||l_deduction || '</Deduction>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Net>' ||l_net || '</Net>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<words>' ||l_words || '</words>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Fyear>' ||trim(to_char(l_from,'YYYY')) || '</Fyear>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<TYear>' ||trim(to_char(to_from,'YYYY')) || '</TYear>');
----
--Calxulation of 1)site days 2) payable days 3) furniture cost using function get_run_result_value
----
l_site_days :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Site Allowance','Site Days',l_date,'IP');
l_payable_days :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Payable Days','Days',l_date,'IP');
l_furniture_cost:=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Projected Company Accommodation','Furniture Cost',l_date,'IP');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable_days>' ||l_payable_days || '</Payable_days>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Furniture>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,' <furniture_cost>'|| l_furniture_cost|| '</furniture_cost>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,' <furniture_cost>' ||' '|| '</furniture_cost>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,' <Taxable_Furniture_Perk>'||' '|| '</Taxable_Furniture_Perk>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Furniture>');
----
--Company Accommodation
----
l_comp_rent_paid:=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Company Accommodation','Rent Paid by Employer',l_date,'IP');
l_taxable_rfa :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Company Accommodation','Pay Value', l_date,'IP');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<COMPANY_ACCOMODATION>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<CRent_Paid>' || l_comp_rent_paid|| '</CRent_Paid>');
l_comp_from :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Company Accommodation','Benefit Start Date', l_date,'IP');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<CFrom>' || l_hra_from_to_date || '</CFrom>');
l_comp_to :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Company Accommodation','Benefit End Date', l_date,'IP');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<CTo>' || l_hra_from_to_date || '</CTo>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<CTaxable_RFA>' ||l_taxable_rfa || '</CTaxable_RFA>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</COMPANY_ACCOMODATION>');
----
--Deduction Under Chapter VI A Starts
----
FOR elem in 1..array_chap_ele.COUNT
LOOP
l_result:=0;
l_result:=get_run_result_value(l_hra_from_to_date,
l_assignment_action_id,
array_chap_ele(elem).element_name,
array_chap_ele(elem).input_name,
l_date,
'IP');
--chap_V1_A_total :=TO_NUMBER(chap_V1_A_total)+l_result;
chap_V1_A_total :=chap_V1_A_total+l_result;
array_chap_ele(elem).res_value := l_result;
IF l_result <>0 then
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_under_chap_V1A>');
row_count:=row_count+1;
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<RowCount>' ||row_count || '</RowCount>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_chap_V1A_element>' || array_chap_ele(elem).element_name|| '</Deduction_chap_V1A_element>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_chap_V1A_element_value>'|| array_chap_ele(elem).res_value || '</Deduction_chap_V1A_element_value>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Deduction_under_chap_V1A>');
END IF;
END LOOP;
IF row_count>1 THEN
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_Message>Y</Deduction_Message>');
-- fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_Message>' || 'Y' || '</Deduction_Message>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Total_value>' || 'Total' || '</Total_value>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_chap_V1A_total>' || chap_V1_A_total || '</Deduction_chap_V1A_total>');
END IF;
----
--Form 16 Income Tax Information
----
l_taxable_income :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Total Income',l_date,'IP');
l_inc_tax_liability:=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Tax on Total Income',l_date,'IP');
l_surcharge :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Surcharge',l_date,'IP');
l_edu_cess :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Education Cess',l_date,'IP');
------Modified on 5/DEC/2010-----------
-- l_net_tax :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Income Tax till Date',l_date,'IP');
l_Sec_HE_Cess :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Sec and HE Cess',l_date,'IP');
l_net_tax:=round(l_inc_tax_liability + l_edu_cess + l_Sec_HE_Cess,1);
l_edu_cess:=l_edu_cess + l_Sec_HE_Cess;
---------------------------------
l_tax_this_mon :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Income Tax','Pay Value',l_date,'IP');
l_proff_tax :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Professional Tax Deduction','Pay Value',l_date,'IP');
l_other_inc_id := XXTEST_pay_payroll_reports_pkg.get_defined_bal_id('Other Income','_ASG_RUN');
l_other_inc := pay_balance_pkg.get_value(l_other_inc_id, l_assignment_action_id);
l_chap_VIA_id := XXTEST_pay_payroll_reports_pkg.get_defined_bal_id('Chapter VIA Deductions','_ASG_RUN');
l_chap_VIA := pay_balance_pkg.get_value(l_chap_VIA_id, l_assignment_action_id);
l_gross_salary :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Information','Salary',l_date,'IP');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Others>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Taxable_income>' || round(l_taxable_income,1) || '</Taxable_income>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Tax_liability>' || round(l_inc_tax_liability,1) || '</Tax_liability>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Surcharge>' || round(l_surcharge,1) || '</Surcharge>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Educational_cess>'|| round(l_edu_cess,1) || '</Educational_cess>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Net_tax>' || round(l_net_tax,1) || '</Net_tax>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Tax_this_month>' || round(l_tax_this_mon,1) || '</Tax_this_month>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Proff_tax>' || round(l_proff_tax,1) || '</Proff_tax>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Other_income>' || round(l_other_inc,1) || '</Other_income>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_VIA>' || round(l_chap_VIA,1) || '</Deduction_VIA>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Gross_salary>' || round(l_gross_salary,1) || '</Gross_salary>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Others>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</PAYSLIP_DETAILS>');
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Procedure XXTEST_PAYSLIP_GENERATION_PRC: '||sqlerrm||SQLCODE||' '||SQLERRM);
END XXTEST_PAYSLIP_GENERATION_PRC;
END XXTEST_PAYSLIP_GENERATION_PKG;
