[Home] [Help]
PACKAGE BODY: APPS.PAY_PRETAX_UDFS
Source
1 PACKAGE BODY pay_pretax_udfs AS
2 /* $Header: pyptxudf.pkb 115.3 99/07/17 06:26:52 porting ship $ */
3 FUNCTION pretax_period_type ( p_date_earned in date,
4 p_eletype_id in number) return varchar2 IS
5
6 l_ptx_period_type varchar2(150);
7
8 BEGIN
9
10 SELECT nvl(etype.element_information4, 'NOT ENTERED')
11 INTO l_ptx_period_type
12 FROM pay_element_types_f ETYPE
13 WHERE ETYPE.element_information20 = p_eletype_id
14 AND p_date_earned BETWEEN ETYPE.effective_start_date
15 AND ETYPE.effective_end_date;
16
17 RETURN l_ptx_period_type;
18
19 EXCEPTION when no_data_found THEN
20
21 l_ptx_period_type := 'NOT ENTERED';
22 RETURN l_ptx_period_type;
23
24 END pretax_period_type;
25
26 FUNCTION pretax_row_type ( p_date_earned in date,
27 p_eletype_id in number) return varchar2 IS
28
29 l_ptx_row_type varchar2(150);
30
31 BEGIN
32
33 SELECT NVL(etype.element_information7, 'NOT ENTERED')
34 INTO l_ptx_row_type
35 FROM pay_element_types_f ETYPE
36 WHERE ETYPE.element_information20 = p_eletype_id
37 AND p_date_earned BETWEEN ETYPE.effective_start_date
38 AND ETYPE.effective_end_date;
39
40 RETURN l_ptx_row_type;
41
42 EXCEPTION when no_data_found THEN
43
44 l_ptx_row_type := 'NOT ENTERED';
45 RETURN l_ptx_row_type;
46
47 END pretax_row_type;
48
49
50 FUNCTION pretax_payroll_table ( p_date_earned in date,
51 p_eletype_id in number) return varchar2 IS
52
53 l_ptx_paytab varchar2(150);
54
55 BEGIN
56
57 SELECT NVL(etype.element_information6, 'NOT ENTERED')
58 INTO l_ptx_paytab
59 FROM pay_element_types_f ETYPE
60 WHERE ETYPE.element_information20 = p_eletype_id
61 AND p_date_earned BETWEEN ETYPE.effective_start_date
62 AND ETYPE.effective_end_date;
63
64 RETURN l_ptx_paytab;
65
66 EXCEPTION when no_data_found THEN
67
68 l_ptx_paytab := 'NOT ENTERED';
69 RETURN l_ptx_paytab;
70
71 END pretax_payroll_table;
72
73
74 FUNCTION pretax_ben_ee_contr ( p_date_earned in date,
75 p_eletype_id in number,
76 p_coverage in varchar2) return number is
77
78 l_ee_contr number;
79
80 BEGIN
81
82 SELECT fnd_number.canonical_to_number(BCONTR.employee_contribution)
83 INTO l_ee_contr
84 FROM pay_element_types_f PET,
85 ben_benefit_contributions_f BCONTR
86 WHERE PET.element_information20 = p_eletype_id
87 AND BCONTR.element_type_id = PET.element_type_id
88 AND BCONTR.coverage_type = p_coverage
89 AND p_date_earned BETWEEN BCONTR.effective_start_date
90 AND BCONTR.effective_end_date;
91
92 RETURN l_ee_contr;
93
94 exception when NO_DATA_FOUND then
95
96 l_ee_contr := 0;
97 RETURN l_ee_contr;
98
99 END pretax_ben_ee_contr;
100
101
102 FUNCTION pretax_ben_er_contr ( p_date_earned in date,
103 p_eletype_id in number,
104 p_coverage in varchar2) return number is
105
106 l_er_contr number;
107
108 BEGIN
109
110 SELECT fnd_number.canonical_to_number(BCONTR.employer_contribution)
111 INTO l_er_contr
112 FROM pay_element_types_f PET,
113 ben_benefit_contributions_f BCONTR
114 WHERE PET.element_information20 = p_eletype_id
115 AND BCONTR.element_type_id = PET.element_type_id
116 AND BCONTR.coverage_type = p_coverage
117 AND p_date_earned BETWEEN BCONTR.effective_start_date
118 AND BCONTR.effective_end_date;
119
120 RETURN l_er_contr;
121
122 exception when NO_DATA_FOUND THEN
123
124 l_er_contr := 0;
125 RETURN l_er_contr;
126
127 END pretax_ben_er_contr;
128
129
130 END pay_pretax_udfs;