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 
127 END pretax_ben_er_contr;
124   l_er_contr := 0;
125   RETURN l_er_contr;
126 
128 
129 
130 END pay_pretax_udfs;