1 PACKAGE BODY PAYRPENP AS
2 /* $Header: payrpenp.pkb 120.1 2006/01/04 00:44:40 pgongada noship $ */
3 /* +======================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8
9 File Name : payrpenp.pkb
10
11 Description : This package used for Employee Not Processes.
12
13 Change History
14 --------------
15
16 Ver Date Bug Author Description
17 ------ ----------- ------- -------- ---------------------------
18 115.2 04-JAN-2001 ahanda Added Header Info.
19 115.3 21-JAN-2004 3372714 saurgupt Modify definition of cursor
20 missing_assignment_action to
21 reduce the cost of query.
22 115.4 04-JAN-2006 4771529 pgongada Changed the size of the local
23 variables to corresponding
24 columns of the tables.
25 */
26
27 --
28 --
29 --
30 -----------------------------------------------------------------------------
31 -- Name --
32 -- get_gre_name --
33 -- Purpose --
34 -- This function returns the name of the government reporting entity --
35 -- associated with the soft_coding_keyflex_id. If this is null, the --
36 -- function returns ' ', avoiding the need for an outer join to handle --
37 -- non-US business groups. --
38 -----------------------------------------------------------------------------
39
40 FUNCTION get_gre_name( p_soft_coding_keyflex_id IN NUMBER )
41 RETURN VARCHAR2 IS
42
43 cursor c_get_gre_name is
44 select tax.name
45 from
46 hr_soft_coding_keyflex flx,
47 hr_organization_units tax,
48 hr_organization_information inf
49 where
50 tax.organization_id = inf.organization_id
51 and inf.org_information_context||'' = 'CLASS'
52 and inf.org_information1 = 'HR_LEGAL'
53 and flx.soft_coding_keyflex_id = p_soft_coding_keyflex_id
54 and tax.organization_id = flx.segment1;
55
56 l_gre_name hr_organization_units.name%TYPE;
57
58 BEGIN
59
60 open c_get_gre_name;
61 fetch c_get_gre_name into l_gre_name;
62 close c_get_gre_name;
63
64 IF l_gre_name IS NULL THEN
65
66 RETURN ' ';
67
68 END IF;
69
70 RETURN l_gre_name;
71
72 END get_gre_name;
73
74 -----------------------------------------------------------------------------
75 -- Name --
76 -- get_gre_id --
77 -- Purpose --
78 -- This function returns the id of the government reporting entity --
79 -- associated with the soft_coding_keyflex_id. If this is null, the --
80 -- function returns NULL, avoiding the need for an outer join to handle --
81 -- non-US business groups. --
82 -- --
83 -----------------------------------------------------------------------------
84
85 FUNCTION get_gre_id( p_soft_coding_keyflex_id IN NUMBER )
86 RETURN NUMBER IS
87
88 cursor c_get_gre_id is
89 select tax.organization_id
90 from
91 hr_soft_coding_keyflex flx,
92 hr_organization_units tax,
93 hr_organization_information inf
94 where
95 tax.organization_id = inf.organization_id
96 and inf.org_information_context||'' = 'CLASS'
97 and inf.org_information1 = 'HR_LEGAL'
98 and flx.soft_coding_keyflex_id = p_soft_coding_keyflex_id
99 and tax.organization_id = flx.segment1;
100
101 l_gre_id hr_organization_units.organization_id%TYPE;
102
103 BEGIN
104
105 open c_get_gre_id;
106 fetch c_get_gre_id into l_gre_id;
107 close c_get_gre_id;
108
109 IF l_gre_id IS NULL THEN
110
111 RETURN NULL;
112
113 END IF;
114
115 RETURN l_gre_id;
116
117 END get_gre_id;
118
119 -----------------------------------------------------------------------------
120 -- Name --
121 -- get_location_code --
122 -- Purpose --
123 -- This function returns the location code associated with the --
124 -- location_id. If this is null, the function returns ' ', avoiding --
125 -- the need for an outer join to handle non-US business groups in which --
126 -- the location is not a mandatory field. --
127 -- --
128 -----------------------------------------------------------------------------
129
130 FUNCTION get_location_code( p_location_id IN NUMBER )
131 RETURN VARCHAR2 IS
132
133 cursor c_get_location_code is
134 select loc.location_code
135 from hr_locations loc
136 where loc.location_id = p_location_id;
137
138 l_location_code hr_locations.location_code%TYPE;
139
140 BEGIN
141
142 open c_get_location_code;
143 fetch c_get_location_code into l_location_code;
144 close c_get_location_code;
145
146 IF l_location_code IS NULL THEN
147
148 RETURN NULL;
149
150 END IF;
151
152 RETURN l_location_code;
153
154 END get_location_code;
155
156 -----------------------------------------------------------------------------
157 -- Name --
158 -- missing_assignment_action --
159 -- Purpose --
160 -- This function identifies those assignments which do not have --
161 -- completed assignment actions in a given payroll period. --
162 -- --
163 -----------------------------------------------------------------------------
164
165 FUNCTION missing_assignment_action( p_assignment_id IN NUMBER,
166 p_time_period_id IN NUMBER )
167 RETURN VARCHAR2 IS
168
169 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
170
171 CURSOR c_get_assignment_action_id IS
172 select assignment_action_id -- Bug 3372714: Single query is broken into two queries to reduce the cost.
173 from pay_assignment_actions act
174 where act.assignment_id = p_assignment_id
175 and act.action_status in ('C', 'S')
176 and exists
177 (select 'x'
178 from per_time_periods ptp ,
179 pay_payroll_actions pct
180 where ptp.time_period_id = p_time_period_id
181 and ptp.time_period_id = pct.time_period_id
182 and pct.effective_date between ptp.start_date and ptp.end_Date
183 and act.payroll_action_id = pct.payroll_action_id
184 );
185
186 BEGIN
187
188 open c_get_assignment_action_id;
189 fetch c_get_assignment_action_id into l_assignment_action_id;
190 close c_get_assignment_action_id;
191
192 IF l_assignment_action_id IS NULL THEN
193
194 RETURN 'Y';
195
196 END IF;
197
198 RETURN 'N';
199
200 END missing_assignment_action;
201
202 END PAYRPENP;
[Home] [Help]