1 package body pay_kr_nonstat_spay_efile_fun as
2 /*$Header: pykrnspen.pkb 120.4.12020000.3 2013/02/13 13:33:21 mdubasi ship $ */
3
4 /*************************************************************************
5 * Function that count the Previous Employers.
6 *
7 *************************************************************************/
8 function get_prev_emp_count (p_assignment_action_id IN Number) return Number
9 is
10 l_prev_emp_count NUMBER(4);
11 cursor csr_get_prev_emp_count
12 is
13 select
14 nvl(count(fue.user_entity_id),0) prev_emp_count
15 from ff_Archive_items fai
16 ,ff_user_entities fue
17 where fue.user_entity_id = fai.user_entity_id
18 and fue.user_entity_name = 'X_KR_PREV_BP_NUMBER'
19 and fai.context1 = p_assignment_action_id
20 group by fai.context1;
21 begin
22 open csr_get_prev_emp_count;
23 fetch csr_get_prev_emp_count into l_prev_emp_count;
24 close csr_get_prev_emp_count;
25 return nvl(l_prev_emp_count, 0);
26 end get_prev_emp_count;
27
28 /*************************************************************************
29 * Bug 7712932: Function to return the Statutory/Non-Statutory Separation
30 * Pay Overseas Tax Break values.
31 *************************************************************************/
32 FUNCTION get_sep_pay_ovr_tax_brk(
33 p_assignment_action_id in number,
34 p_assignment_id in number,
35 p_type in varchar2) return number
36 IS
37 --
38 l_stat_sp_ovr_tax_brk number:= 0;
39 l_non_stat_sp_ovr_tax_brk number:= 0;
40 --
41 CURSOR csr_sep_pay_ovr_tax_brk is
42 select nvl(sep_pay_overseas_tax_break,0),
43 nvl(non_stat_sep_overseas_tax_brk,0)
44 from pay_kr_sep_tax_v
45 where xassignment_action_id = p_assignment_action_id
46 and assignment_id = p_assignment_id;
47 --
48 BEGIN
49 open csr_sep_pay_ovr_tax_brk;
50 fetch csr_sep_pay_ovr_tax_brk into l_stat_sp_ovr_tax_brk,l_non_stat_sp_ovr_tax_brk;
51 close csr_sep_pay_ovr_tax_brk;
52
53 if (p_type = 'SEP') then
54 return nvl(l_stat_sp_ovr_tax_brk,0);
55 elsif (p_type = 'NSEP') then
56 return nvl(l_non_stat_sp_ovr_tax_brk,0);
57 end if;
58
59 END get_sep_pay_ovr_tax_brk;
60
61
62 FUNCTION get_assignment_action_id(
63 p_assignment_action_id in number,
64 p_assignment_id in number) return number
65 IS
66 l_assignment_action_id number := 0;
67
68 CURSOR csr_get_assgn_act_id is
69 select assignment_action_id
70 from pay_kr_sep_tax_v
71 where xassignment_action_id = p_assignment_action_id
72 and assignment_id = p_assignment_id;
73
74 begin
75
76 open csr_get_assgn_act_id;
77 fetch csr_get_assgn_act_id into l_assignment_action_id;
78 close csr_get_assgn_act_id;
79
80 return nvl(to_number(l_assignment_action_id),0);
81
82 end get_assignment_action_id;
83
84 FUNCTION get_element_rr_date_value(
85 p_assignment_action_id in number
86 ,p_input_value_name in varchar2
87 ,p_element_name in varchar2
88 ) RETURN varchar2
89 IS
90 --
91 l_dummy pay_run_result_values.result_value%type;
92 CURSOR csr(l_assignment_action_id number, l_input_value_name varchar2, l_element_name varchar2) is
93 SELECT nvl(to_char(fnd_date.canonical_to_date(prrv.result_value),'YYYY/MM/DD'), '0')
94 FROM pay_input_values_f piv
95 ,pay_run_result_values prrv
96 ,pay_run_results prr
97 ,pay_payroll_actions ppa
98 ,pay_assignment_actions paa
99 ,pay_element_types_f pet
100 WHERE paa.assignment_action_id = l_assignment_action_id
101 and ppa.payroll_action_id = paa.payroll_action_id
102 and prr.assignment_action_id = paa.assignment_action_id
103 and prr.status in ('P', 'PA')
104 and prr.element_type_id = pet.element_type_id
105 and pet.element_name = l_element_name
106 and piv.legislation_code = 'KR'
107 and pet.legislation_code = 'KR'
108 and prrv.run_result_id = prr.run_result_id
109 and piv.input_value_id = prrv.input_value_id
110 and piv.name = l_input_value_name
111 and ppa.effective_date
112 between piv.effective_start_date and piv.effective_end_date;
113 --
114 BEGIN
115 --
116 l_dummy := '0';
117
118 OPEN csr(p_assignment_action_id,p_input_value_name,p_element_name);
119 FETCH csr into l_dummy;
120
121 IF csr%NOTFOUND THEN
122 l_dummy := '0';
123 END IF;
124
125 CLOSE csr;
126
127 RETURN l_dummy;
128 --
129 END;
130
131 FUNCTION get_bus_reg_fin_num(
132 p_fin_inst_code in varchar2,
133 p_fin_inst_name out NOCOPY varchar2,
134 p_bus_reg_number out NOCOPY varchar2
135 ) return number
136 IS
137 l_bus_reg_number varchar2(20);
138
139 begin
140
141 select meaning,description into p_fin_inst_name,p_bus_reg_number from
142 hr_lookups where lookup_type = 'PAY_KR_PENSION_FINANCIAL_INST' and
143 lookup_code = p_fin_inst_code;
144
145 return 1;
146
147 end get_bus_reg_fin_num;
148
149 end pay_kr_nonstat_spay_efile_fun;
[Home] [Help]