create or replace PACKAGE BODY XXARJ_PAYSLIP_GENERATION_PKG AS
FUNCTION XXARJ_NET_SAL_FNC
(
P_ASG_ACTION_ID IN NUMBER ,
P_BAL_NAME IN VARCHAR2 ,
P_DIM_NAME IN VARCHAR2
)
RETURN NUMBER
AS
P_NET_SALARY NUMBER:=0;
P_DEF_BAL_ID NUMBER;
BEGIN
P_DEF_BAL_ID := XXARJ_pay_payroll_reports_pkg.get_defined_bal_id(P_BAL_NAME, P_DIM_NAME,81,'IN');
P_NET_SALARY := pay_balance_pkg.get_value(P_DEF_BAL_ID, P_ASG_ACTION_ID);
RETURN P_NET_SALARY;
EXCEPTION
WHEN OTHERS THEN
RETURN P_NET_SALARY;
fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function XXARJ_NET_SAL_FNC: '||SQLCODE||' '||SQLERRM);
END XXARJ_NET_SAL_FNC;
--------
---
--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;
-------------
-- ||XXARJ_ELEMENT_VALUE_TILLDATE_FNC used to calculate sum of Arrears From RUN RESULT VALUE of an element
-------------
FUNCTION XXARJ_ELEMENT_VALUE_TILL_FNC
(
P_ASSIGNMENT_ID NUMBER ,
P_INPUT_VALUE_NAME VARCHAR2 ,
P_ELEMENT_NAME VARCHAR2 ,
P_FIRST_DATE DATE,
P_DATE DATE )
RETURN NUMBER
AS
L_ELEMENT_VALUE NUMBER:=0;
BEGIN
SELECT SUM(fnd_number.canonical_to_number(NVL(PRRV.RESULT_VALUE,0)))
INTO L_ELEMENT_VALUE
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_RUN_RESULTS PRR,
PAY_RUN_RESULT_VALUES PRRV,
PAY_INPUT_VALUES_F PIVF,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA
WHERE PPA.ACTION_TYPE IN('R', 'Q', 'I', 'B')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.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'))
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
AND (PIVF.LEGISLATION_CODE = 'IN'
OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN P_FIRST_DATE AND LAST_DAY(P_DATE)
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;
RETURN L_ELEMENT_VALUE;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function XXARJ_ELEMENT_VALUE_TILLDATE_FNC: '||SQLERRM);
RETURN L_ELEMENT_VALUE;
END XXARJ_ELEMENT_VALUE_TILL_FNC;
-------------------------------------------------------------------------------------
---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;
--20-Aug-2011
l_act_ter_date date;
P_MON_LEVEL number;
l_standard_taxable_value_1 number;
l_element_run_result_value_1 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 "XXARJ_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-2011 and 31-mar-2012');
BEGIN
SELECT value,EFFECTIVE_ENDDATE
INTO l_ear_till_date , TABLE_TILL_DATE
FROM XXARJ_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 + XXARJ_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;
l_first_fin_date :=fnd_conc_date.string_to_date('01' || 'APR' || from_date);
Begin
Select actual_termination_date
Into l_act_ter_date
From per_periods_of_service
Where person_id = p_person_id;
Exception
When Others Then
l_act_ter_date:= to_date('31-Dec-4712');
End;
If(l_act_ter_date between l_first_fin_date And l_end_fin_date)Then
Begin
SELECT LEVEL INTO P_MON_LEVEL
FROM DUAL WHERE TO_CHAR(add_months(to_date('01-MAR-2000'),level),'MON') = TO_CHAR(l_act_ter_date,'MON') connect by level <= 12;
Exception
When Others Then
P_MON_LEVEL:=0;
End;
period_left:= P_MON_LEVEL - index_num;
Else
period_left := 12-index_num;
End If;
----------------------------updated on 01-nov-2011------
-- l_element_run_result_value_1 :=get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Pay Value',l_pay_gen_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_pay_gen_date);
---------------------------------
l_standard_value := get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Standard Value',l_pay_gen_date,'IP'); --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;
/*For Thailand Standard Value will be taken*/
if (p_element_name ='Thailand Allowance') then
fnd_file.PUT_LINE(fnd_file.LOG,'******************** Thai *************'||period_left);
--l_standard_taxable_value := get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Standard Value',l_pay_gen_date,'IP');
l_total_projected_value := get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Pay Value',l_pay_gen_date,'IP');
--l_project_value := l_standard_taxable_value * period_left;
--l_total_projected_value := l_standard_taxable_value_1+l_project_value ;--12*l_standard_taxable_value ;--value_till_date + l_project_value;
end if;
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||' period_left '||period_left);
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='Site Allowance'
OR p_element_name='Phone Subsidy' THEN
fnd_file.PUT_LINE(fnd_file.LOG,'*********************** '||p_element_name);
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;
l_first_fin_date :=fnd_conc_date.string_to_date('01' || 'APR' || from_date);
Begin
Select actual_termination_date
Into l_act_ter_date
From per_periods_of_service
Where person_id = p_person_id;
Exception
When Others Then
l_act_ter_date:= to_date('31-Dec-4712');
End;
If(l_act_ter_date between l_first_fin_date And l_end_fin_date)Then
Begin
SELECT LEVEL INTO P_MON_LEVEL
FROM DUAL WHERE TO_CHAR(add_months(to_date('01-MAR-2000'),level),'MON') = TO_CHAR(l_act_ter_date,'MON') connect by level <= 12;
Exception
When Others Then
P_MON_LEVEL:=0;
End;
period_left:= P_MON_LEVEL - index_num;
Else
period_left := 12-index_num;
End If;
fnd_file.PUT_LINE(fnd_file.LOG,'******************** Period Left cal *************'||period_left);
-- l_standard_value := get_screen_entry_value(l_assignment_id,p_element_name,l_pay_gen_date);
l_standard_value := get_run_result_value(p_hra_from_to_date
,l_assignment_action_id
,p_element_name
,'Pay Value'
,l_pay_gen_date
,'IP'
);
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;
ELSE
-------------------------------/*Code Changes 21-Aug-2011*/
-- IF p_element_name='Conveyance Allowance' THEN
-- fnd_file.PUT_LINE(fnd_file.LOG,'If the element is Conveyance Allowance ');
--
-- /* ------------Modified on 5/DEC/2010------------
-- /* value_till_date:= apps.XXARJ_in_payroll_utility_pkg.XXARJ_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,'Conveyance Allowance','Allowance Amount',l_pay_gen_date,'IP');
-- fnd_file.PUT_LINE(fnd_file.LOG,'Conveyance Allowance :'|| l_total_projected_value);
--
-- END IF;
-------------------------------/*Code Changes 21-Aug-2011*/--------------
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.XXARJ_in_payroll_utility_pkg.XXARJ_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.XXARJ_in_payroll_utility_pkg.XXARJ_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 : line 441'|| 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;
Begin
Select actual_termination_date
Into l_act_ter_date
From per_periods_of_service
Where person_id = p_person_id;
Exception
When Others Then
l_act_ter_date:= to_date('31-Dec-4712');
End;
If(l_act_ter_date between l_first_fin_date And l_end_fin_date)Then
Begin
SELECT LEVEL INTO P_MON_LEVEL
FROM DUAL WHERE TO_CHAR(add_months(to_date('01-MAR-2000'),level),'MON') = TO_CHAR(l_act_ter_date,'MON') connect by level <= 12;
Exception
When Others Then
P_MON_LEVEL:=0;
End;
period_left:= P_MON_LEVEL - index_num;
Else
period_left := 12-index_num;
End If;
fnd_file.PUT_LINE(fnd_file.LOG,'******************** Period Left **** Line 475 '||period_left||'value_till_date '||value_till_date);
-------------------------------/*Code Changes 21-sep-2011*/--------------------
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;
-------------------------------/*Code Changes 23-Aug-2011*/--------------------
/*For conveyance Standard Value will be taken*/
if (p_element_name ='Conveyance Allowance') then
value_till_date := apps.XXARJ_in_payroll_utility_pkg.XXARJ_IN_balance_value(l_assignment_action_id
,83
,'Allowance Amount'
,'_ASG_COMP_YTD'
,'SOURCE_TEXT2'
,p_context_value
) ;
l_standard_taxable_value := get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Standard Value',l_pay_gen_date,'IP');
l_standard_taxable_value_1 := get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Pay 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 ;--12*l_standard_taxable_value ;--value_till_date + l_project_value;
fnd_file.PUT_LINE(fnd_file.LOG,'Value till date from balance :'|| value_till_date);
end if;
/*For Education Standard Value will be taken*/
if (p_element_name ='Education Allowance') then
fnd_file.PUT_LINE(fnd_file.LOG,'******************** Period Left *************'||period_left||'value_till_date '||value_till_date);
value_till_date := apps.XXARJ_in_payroll_utility_pkg.XXARJ_IN_balance_value(l_assignment_action_id
,83
,'Allowance Amount'
,'_ASG_COMP_YTD'
,'SOURCE_TEXT2'
,p_context_value
) ;
l_standard_taxable_value := get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Standard Value',l_pay_gen_date,'IP');
l_standard_taxable_value_1 := get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Pay 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 ;--12*l_standard_taxable_value ;--value_till_date + l_project_value;
fnd_file.PUT_LINE(fnd_file.LOG,'Value till date from balance :'|| value_till_date);
end if;
/*For Hardship Offshore Allowance Standard Value will be taken*/
if (p_element_name ='Hardship Offshore Allowance') then
fnd_file.PUT_LINE(fnd_file.LOG,'******************** Period Left *************'||period_left||'value_till_date '||value_till_date);
value_till_date := apps.XXARJ_in_payroll_utility_pkg.XXARJ_IN_balance_value(l_assignment_action_id
,83
,'Allowance Amount'
,'_ASG_COMP_YTD'
,'SOURCE_TEXT2'
,p_context_value
) ;
l_standard_taxable_value := get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Standard Value',l_pay_gen_date,'IP');
l_standard_taxable_value_1 := get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Pay 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 ;--12*l_standard_taxable_value ;--value_till_date + l_project_value;
fnd_file.PUT_LINE(fnd_file.LOG,'Value till date from balance :'|| value_till_date);
end if;
-------------------------------/*Code Changes 21-Aug-2011*/
fnd_file.PUT_LINE(fnd_file.LOG,'*********************');
fnd_file.PUT_LINE(fnd_file.LOG,'Elemnt Name : '|| p_element_name);
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;
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.XXARJ_in_payroll_utility_pkg.XXARJ_IN_balance_value(l_assignment_action_id
,83
,'Taxable Allowances'
,'_ASG_COMP_YTD'
,'SOURCE_TEXT2'
,p_context_value
) ; */
------------------------------------------------
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;
-------------------------------------------------------
l_total_projected_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_pay_gen_date,'IP'); --value_till_date; updated on 2nd nov
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
,pay_run_types_f prtf
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 prtf.run_type_id = paa.run_type_id
and prtf.run_type_name not like '%Process Separate%' ---- updated for separate run
AND paaf.business_group_id =fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND p_effective_date BETWEEN prtf.effective_start_date AND prtf.effective_end_date
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_comments_value (
p_assignment_action_id IN NUMBER
,p_input_value_name IN VARCHAR2
,p_date IN DATE
)RETURN VARCHAR2
AS
l_value VARCHAR2(1000);--NUMBER := '0';
l_value_number NUMBER:=0;
BEGIN
SELECT (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 petf.element_name = 'PLG Payroll Exception'
AND (petf.legislation_code = 'IN' OR petf.business_group_id = 81)---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 = 81)---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;
RETURN l_value;
EXCEPTION
WHEN OTHERS THEN
l_value := ' ';
RETURN l_value;
END get_run_comments_value;
----------------------------------------------------------------------------------------------------
---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 peef.effective_start_date AND peef.effective_end_date
OR peef.effective_start_date BETWEEN p_date and last_day( p_date))
AND (p_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
OR peevf.effective_start_date BETWEEN p_date and last_day( p_date))
-- AND peef.effective_start_date BETWEEN p_date and last_day(p_date)
-- AND peevf.effective_start_date BETWEEN p_date and last_day(p_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 XXARJ_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 XXARJ_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','Perquisites') 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%'
ORDER BY petf.element_name;
-------------------------------------------------------------------------
--- 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)
ORDER BY petf.element_name;
-- 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;
l_exempted_amount number;
l_tax_till_date NUMBER;
L_ASS_AC_ID NUMBER;
L_ASS_ID NUMBER;
L_EX_DAYS NUMBER;
L_MSG VARCHAR2(5000);
l_overtime NUMBER;
l_att_r_val NUMBER;
l_comm VARCHAR2(2000);
l_prev_emp_sal NUMBER;
l_prev_emp_tax 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:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80CCD';
array_chap_ele(1) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80CCE';
array_chap_ele(2) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80CCF';
array_chap_ele(3) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80D';
array_chap_ele(4) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80DD';
array_chap_ele(5) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80DDB';
array_chap_ele(6):=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80E';
array_chap_ele(7):=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80GG';
array_chap_ele(8) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deduction under Section 80U';
array_chap_ele(9) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80G UE';
array_chap_ele(10) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80G LE';
array_chap_ele(11) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80G FP';
array_chap_ele(12) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80GGA';
array_chap_ele(13) :=chap_V1A;
chap_V1A.element_name:='Form16 Deduction Information';
chap_V1A.input_name :='Deductions Sec 80U';
array_chap_ele(14) :=chap_V1A;
chap_V1A.element_name:='Previous Employment Information';
chap_V1A.input_name :='PF Deduction';
array_chap_ele(15) :=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;
BEGIN
SELECT sum(prrv.result_value) --fnd_number.canonical_to_number(prrv.result_value) result_value
INTO l_att_r_val
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 = 42365810
AND pivf.name in ('Day_1','Day_2','Day_3','Day_4','Day_5','Day_6','Day_7','Day_8','Day_9','Day_10')
AND petf.element_name in ('Retro Attendance Details01','Retro Attendance Details02','Retro Attendance Details03')
AND (petf.legislation_code = 'IN' OR petf.business_group_id = 81)---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 = 81)---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 l_date BETWEEN petf.effective_start_date AND petf.effective_end_date
AND l_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
EXCEPTION WHEN OTHERS THEN
l_att_r_val :=0;
END;
l_total_days := (last_day(l_date)-l_date) + 1 ;
l_payable_days :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Payable Days','Days',l_date,'IP')+l_att_r_val;
l_site_days :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Site Allowance','Site Days',l_date,'IP');
l_overtime :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Overtime','Hours',l_date,'IP');
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_name || ']]></Employee_Name>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Emp_No><![CDATA[' ||l_emp_no || ']]></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>');
------------------ Updated on 28th sep--------
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<l_overtime>' ||l_overtime || ' </l_overtime>');
IF l_payable_days > l_total_days THEN
L_EX_DAYS := l_payable_days - l_total_days ;
begin
select get_run_comments_value(l_assignment_action_id,'Comments1',l_date)||' '||get_run_comments_value(l_assignment_action_id,'Comments2',l_date)||' '||get_run_comments_value(l_assignment_action_id,'Comments3',l_date)||' '||get_run_comments_value(l_assignment_action_id,'Comments4',l_date)||' '||get_run_comments_value(l_assignment_action_id,'Comments5',l_date)
into l_comm
from dual;
exception when others then
l_comm := ' ';
end;
L_MSG := L_EX_DAYS ||' Days For Previous Month .'||l_comm;
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<L_MSG>' ||L_MSG || ' </L_MSG>');
END IF;
---------------------------------------------
----
--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:=XXARJ_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:=XXARJ_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');
--***************************************
BEGIN
SELECT MAX(paa.assignment_action_id)
,paa.assignment_id
INTO L_ASS_AC_ID,L_ASS_ID
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_time_periods ptp
,per_all_assignments_f paaf
,pay_run_types_f prtf
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 prtf.run_type_id = paa.run_type_id
and prtf.run_type_name like '%Process Separate%' ---- updated for separate run
AND paaf.business_group_id =fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND l_date BETWEEN prtf.effective_start_date AND prtf.effective_end_date
AND ppa.effective_date BETWEEN l_date AND last_day(l_date)
AND (l_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
OR paaf.effective_start_date BETWEEN l_date AND last_day(l_date))
GROUP BY paa.assignment_id;
EXCEPTION WHEN OTHERS THEN
L_ASS_AC_ID := NULL;
L_ASS_ID :=NULL;
fnd_file.PUT_LINE(fnd_file.LOG,'NO SEPARATE RUN ACTION ID');
END;
IF L_ASS_AC_ID IS NOT NULL THEN
FOR I IN get_earn_deduct_elements(L_ASS_AC_ID,l_date,'EA')
LOOP
element_value:=XXARJ_PAYSLIP_GENERATION_PKG.get_run_result_value(ret_date,L_ASS_AC_ID,
I.element_name,'Pay Value',l_date,'IP');
--Others_N := nvl(element_value,0);
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_value :=0;
-- fnd_file.PUT_LINE(fnd_file.LOG,'INSIDE SEPARETE LOOP '||L_ASS_AC_ID||'element_name '|| ele_ret_arr(rec_count).element_name||ele_ret_arr(rec_count).element_value);
rec_count := rec_count +1;
fnd_file.PUT_LINE(fnd_file.LOG,'INSIDE SEPARETE LOOP '||L_ASS_AC_ID||'element_name '|| I.element_name||l_date);
END LOOP;
END IF;
-- ******************************************
----
--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[' || ele_ret_arr(rec_count).deduction || ']]></Deduction_Element_Name>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_Element_Value>' ||ele_ret_arr(rec_count).ded_value || ' </Deduction_Element_Value>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Deduction_details>');
END IF; */
END LOOP;
For rec_get_deduction_elements IN get_earn_deduct_elements(L_ASS_AC_ID,l_date,'D')
LOOP
l_run_result_value :=get_run_result_value(l_hra_from_to_date,L_ASS_AC_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;
END LOOP;
FOR I IN 1..ele_ret_arr.COUNT
LOOP
IF ele_ret_arr(I).ded_value <> 0 THEN
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_details>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_Element_Name><![CDATA[' || ele_ret_arr(I).deduction || ']]></Deduction_Element_Name>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_Element_Value>' ||ele_ret_arr(I).ded_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);
IF ele_ret_arr(I).element_name = 'Thailand Allowance' THEN
BEGIN
SELECT NVL(SUM(fnd_number.canonical_to_number(peevf.screen_entry_value)),0)
INTO l_standard_value
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 = L_ASS_ID
AND petf.element_name = 'Thailand Allowance'
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','Bonus 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 l_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 (l_date BETWEEN peef.effective_start_date AND peef.effective_end_date
OR peef.effective_start_date BETWEEN l_date and last_day( l_date))
AND (l_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
OR peevf.effective_start_date BETWEEN l_date and last_day( l_date))
-- AND peef.effective_start_date BETWEEN p_date and last_day(p_date)
-- AND peevf.effective_start_date BETWEEN p_date and last_day(p_date)
AND l_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
EXCEPTION WHEN OTHERS THEN
l_standard_value :=0;
END;
--l_standard_value :=get_screen_entry_value(L_ASS_ID,ele_ret_arr(I).element_name,l_date);
else
l_standard_value :=get_run_result_value(ret_date,l_assignment_action_id,ele_ret_arr(I).element_name,'Standard Value',l_date,'IP');--get_screen_entry_value(l_assignment_id,ele_ret_arr(I).element_name,l_date);
END IF;
l_gross :=l_gross+NVL(ele_ret_arr(I).total,0);
--fnd_file.PUT_LINE(fnd_file.LOG,'eraning print '||'element_name '|| ele_ret_arr(I).element_name);
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;
IF rec_projection.element_name = 'House Rent Allowance'
OR rec_projection.element_name ='Conveyance Allowance'
OR rec_projection.element_name ='Education Allowance' THEN
l_retro_value := 0;
ELSE
l_retro_value :=nvl(XXARJ_ELEMENT_VALUE_TILL_FNC (l_assignment_id,'Pay Value','Retro '||rec_projection.element_name,l_first_date,add_months(l_date,-1)),0);--XXARJ_PAYSLIP_GENERATION_PKG.get_run_result_value(ret_date,l_assignment_action_id, 'Retro '||rec_projection.element_name,'Pay Value',l_date,'IP');
END IF;
array_earn(J).run_result_value:=l_run_result_value+l_retro_value;
fnd_file.PUT_LINE(fnd_file.log,'****************** ELEMENT **************'||'Retro '||rec_projection.element_name||' '|| l_retro_value||'Projected Value '||l_run_result_value);
array_earn(J).processing_type :=l_processing_type;
IF L_ASS_AC_ID is not null then
l_total_n_rec :=XXARJ_PAYSLIP_GENERATION_PKG.get_run_result_value(ret_date,L_ASS_AC_ID,'Thailand Allowance','Pay Value',l_date,'IP');
fnd_file.PUT_LINE(fnd_file.LOG,'############# Others as Thailand Allowance :' ||' '||l_total_n_rec);
END IF;
l_total_n_rec :=l_total_n_rec+NVL(l_tot_non_rec,0);
J:=J+1;
END LOOP;
------
/* FOR i in 1 .. array_earn.COUNT
loop
fnd_file.PUT_LINE(fnd_file.log,'****************** ELEMENT **************'||array_earn(i).element_name );
end loop;
*/
------
FOR j IN 1 .. array_earn.COUNT
LOOP
--fnd_file.PUT_LINE(fnd_file.log,'______________________ ELEMENT _______________________________'||array_earn(j).element_name||' '||array_earn(J).run_result_value );
IF array_earn(J).processing_type = 'R' THEN
IF UPPER(p_month) IN('JUN','SEP','DEC','MAR') THEN
-- IF array_earn(J).element_name NOT LIKE '%Reimbursement%' 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>');
IF array_earn(J).element_name = 'Telephone Reimbursement' THEN
array_earn(J).run_result_value := XXARJ_NET_SAL_FNC(l_assignment_action_id,'Telephone Reimbursement Taxable Amount','_ASG_YTD');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable>' ||array_earn(J).run_result_value || '</Payable>');
ELSIF array_earn(J).element_name = 'Medical Reimbursement' THEN
array_earn(J).run_result_value := XXARJ_NET_SAL_FNC(l_assignment_action_id,'Medical Reimbursement Taxable Amount','_ASG_YTD');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable>' ||array_earn(J).run_result_value || '</Payable>');
ELSIF array_earn(J).element_name = 'Driver Reimbursement' THEN
array_earn(J).run_result_value := XXARJ_NET_SAL_FNC(l_assignment_action_id,'Driver Reimbursement Taxable Amount','_ASG_YTD');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable>' ||array_earn(J).run_result_value || '</Payable>');
ELSIF array_earn(J).element_name = 'Petrol Reimbursement' THEN
array_earn(J).run_result_value := XXARJ_NET_SAL_FNC(l_assignment_action_id,'Petrol Reimbursement Taxable Amount','_ASG_YTD');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable>' ||array_earn(J).run_result_value || '</Payable>');
ELSE
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable>' ||array_earn(J).run_result_value || '</Payable>');
END IF;
fnd_file.PUT_LINE(fnd_file.OUTPUT,'</ANNUAL_EARNING>');
-- END IF;
ELSE
--fnd_file.PUT_LINE(fnd_file.log,'****************** JUN **************' );
IF array_earn(J).element_name NOT LIKE '%Reimbursement%'AND array_earn(J).run_result_value <> 0 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 IF ;
END IF;
END LOOP;
--fnd_file.PUT_LINE(fnd_file.LOG,': *********************************' ||l_pf_def_bal_id );
--- Added on 7/DEC/2010
--PPF contribution
---
l_pf_def_bal_id := XXARJ_pay_payroll_reports_pkg.get_defined_bal_id('Employee Statutory PF Contribution' ,'_ASG_YTD',l_business_group_id,'IN');
--fnd_file.PUT_LINE(fnd_file.LOG,': *********************************' ||l_pf_def_bal_id );
BEGIN
l_pf_till_date := pay_balance_pkg.get_value(l_pf_def_bal_id, l_assignment_action_id);
EXCEPTION WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.LOG,': *********************************' ||SQLERRM );
END;
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.XXARJ_in_payroll_utility_pkg.XXARJ_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 XXARJ_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).input_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
----
IF L_ASS_AC_ID is not null then
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')+NVL(get_run_result_value(l_hra_from_to_date,L_ASS_AC_ID,'Form16 Income Tax Information','Total Income',l_date,'IP'),0);
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')+NVL(get_run_result_value(l_hra_from_to_date,L_ASS_AC_ID,'Form16 Income Tax Information','Tax on Total Income',l_date,'IP'),0);
l_surcharge :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Surcharge',l_date,'IP')+NVL(get_run_result_value(l_hra_from_to_date,L_ASS_AC_ID,'Form16 Income Tax Information','Surcharge',l_date,'IP'),0);
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')+NVL(get_run_result_value(l_hra_from_to_date,L_ASS_AC_ID,'Form16 Income Tax Information','Education Cess',l_date,'IP'),0);
------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')+NVL(get_run_result_value(l_hra_from_to_date,L_ASS_AC_ID,'Form16 Income Tax Information','Sec and HE Cess',l_date,'IP'),0);
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')+NVL(get_run_result_value(l_hra_from_to_date,L_ASS_AC_ID,'Income Tax','Pay Value',l_date,'IP'),0);
For rec_get_deduction_elements IN get_earn_deduct_elements(L_ASS_AC_ID,l_date,'D')
LOOP
l_run_result_value :=get_run_result_value(l_hra_from_to_date,L_ASS_AC_ID,rec_get_deduction_elements.element_name,'Pay Value',l_date,'IP');
l_tax_this_mon := l_tax_this_mon+l_run_result_value;
END LOOP;
l_proff_tax :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Professional Tax Deduction','Pay Value',l_date,'IP')+NVL(get_run_result_value(l_hra_from_to_date,L_ASS_AC_ID,'Professional Tax Deduction','Pay Value',l_date,'IP'),0);
l_proff_tax := l_proff_tax *12;
l_proff_tax := get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Information','Employment Tax',l_date,'IP');
l_other_inc_id := XXARJ_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 := XXARJ_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')+NVL(get_run_result_value(l_hra_from_to_date,L_ASS_AC_ID,'Form16 Income Information','Salary',l_date,'IP'),0);
l_exempted_amount :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Information','Allowances Exempt',l_date,'IP')+NVL(get_run_result_value(l_hra_from_to_date,L_ASS_AC_ID,'Form16 Income Information','Allowances Exempt',l_date,'IP'),0);
l_tax_till_date := XXARJ_NET_SAL_FNC(l_assignment_action_id,'F16 TDS','_ASG_PTD')+NVL(XXARJ_NET_SAL_FNC(L_ASS_AC_ID,'F16 TDS','_ASG_PTD'),0);
FND_FILE.PUT_LINE(FND_FILE.LOG,'**~~~~~~~***Tax**~~~~~**** :'||l_tax_till_date||' '||l_tax_this_mon);
l_tax_till_date := l_tax_till_date-l_tax_this_mon;
l_prev_emp_sal :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Previous Employment Information','Previous Earnings',l_date,'IP');
l_prev_emp_tax :=nvl(get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Previous Employment Information','Previous Tax Deduction',l_date,'IP'),0)+nvl(get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Previous Employment Information','Previous Cess',l_date,'IP'),0)+nvl(get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Previous Employment Information','Previous Sec and HE Cess',l_date,'IP'),0);
ELSE
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_proff_tax := l_proff_tax *12;
l_other_inc_id := XXARJ_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 := XXARJ_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');
l_exempted_amount :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Information','Allowances Exempt',l_date,'IP');
l_tax_till_date := XXARJ_NET_SAL_FNC(l_assignment_action_id,'F16 TDS','_ASG_PTD');
l_prev_emp_sal :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Previous Employment Information','Previous Earnings',l_date,'IP');
l_prev_emp_tax :=nvl(get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Previous Employment Information','Previous Tax Deduction',l_date,'IP'),0)+nvl(get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Previous Employment Information','Previous Cess',l_date,'IP'),0)+nvl(get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Previous Employment Information','Previous Sec and HE Cess',l_date,'IP'),0);
l_tax_till_date := l_tax_till_date-l_tax_this_mon;
END IF;
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Exempted_amount>' || round(l_exempted_amount,1) || '</Exempted_amount>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Tax_till_date>' || round(l_tax_till_date,1) || '</Tax_till_date>');
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(chap_V1_A_total,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,'<prev_emp_sal>' || round(l_prev_emp_sal,1) || '</prev_emp_sal>');
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<prev_emp_tax>' || round(l_prev_emp_tax,1) || '</prev_emp_tax>');
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 XXARJ_PAYSLIP_GENERATION_PRC: '||sqlerrm||SQLCODE||' '||SQLERRM);
END XXARJ_PAYSLIP_GENERATION_PRC;
END XXARJ_PAYSLIP_GENERATION_PKG;
