[Home] [Help]
PACKAGE BODY: APPS.GHR_FETCH_POSITION_HISTORY
Source
1 PACKAGE BODY GHR_FETCH_POSITION_HISTORY AS
2 /* $Header: ghfetdpo.pkb 120.0.12010000.2 2009/05/26 10:41:05 utokachi noship $ */
3 g_package_name varchar2(31) := 'GHR_FETCH_POSITION_HISTORY.';
4 -- -------------------------------
5 PROCEDURE get_position_detail (
6 -- -------------------------------
7 p_position_id IN per_positions.position_id%type,
8 p_session_date IN DATE,
9 p_date_effective IN out NOCOPY per_positions.date_effective%type,
10 p_date_end IN out NOCOPY per_positions.date_end%type,
11 p_working_hours IN out NOCOPY per_positions.working_hours%type,
12 p_time_normal_start IN out NOCOPY per_positions.time_normal_start%type,
13 p_time_normal_finish IN out NOCOPY per_positions.time_normal_finish%type,
14 p_probation_period IN out NOCOPY per_positions.probation_period%type,
15 p_probation_period_units IN out NOCOPY per_positions.probation_period_units%type,
16 p_position_definition_id IN out NOCOPY per_positions.position_definition_id%type,
17 p_business_group_id IN out NOCOPY per_positions.business_group_id%type,
18 p_job_id IN out NOCOPY per_positions.job_id%type,
19 p_organization_id IN out NOCOPY per_positions.organization_id%type,
20 p_successor_position_id IN out NOCOPY per_positions.successor_position_id%type,
21 p_relief_position_id IN out NOCOPY per_positions.relief_position_id%type,
22 p_location_id IN out NOCOPY per_positions.location_id%type,
23 p_comments IN out NOCOPY per_positions.comments%type,
24 p_status IN out NOCOPY per_positions.status%type,
25 p_frequency IN out NOCOPY per_positions.frequency%type,
26 p_name IN out NOCOPY per_positions.name%type,
27 p_replacement_required_flag IN out NOCOPY per_positions.replacement_required_flag%type,
28 p_request_id IN out NOCOPY per_positions.request_id%type,
29 p_program_application_id IN out NOCOPY per_positions.program_application_id%type,
30 p_program_id IN out NOCOPY per_positions.program_id%type,
31 p_program_update_date IN out NOCOPY per_positions.program_update_date%type,
32 p_attribute_category IN out NOCOPY per_positions.attribute_category%type,
33 p_attribute1 IN out NOCOPY per_positions.attribute1%type,
34 p_attribute2 IN out NOCOPY per_positions.attribute2%type,
35 p_attribute3 IN out NOCOPY per_positions.attribute3%type,
36 p_attribute4 IN out NOCOPY per_positions.attribute4%type,
37 p_attribute5 IN out NOCOPY per_positions.attribute5%type,
38 p_attribute6 IN out NOCOPY per_positions.attribute6%type,
39 p_attribute7 IN out NOCOPY per_positions.attribute7%type,
40 p_attribute8 IN out NOCOPY per_positions.attribute8%type,
41 p_attribute9 IN out NOCOPY per_positions.attribute9%type,
42 p_attribute10 IN out NOCOPY per_positions.attribute10%type,
43 p_attribute11 IN out NOCOPY per_positions.attribute11%type,
44 p_attribute12 IN out NOCOPY per_positions.attribute12%type,
45 p_attribute13 IN out NOCOPY per_positions.attribute13%type,
46 p_attribute14 IN out NOCOPY per_positions.attribute14%type,
47 p_attribute15 IN out NOCOPY per_positions.attribute15%type,
48 p_attribute16 IN out NOCOPY per_positions.attribute16%type,
49 p_attribute17 IN out NOCOPY per_positions.attribute17%type,
50 p_attribute18 IN out NOCOPY per_positions.attribute18%type,
51 p_attribute19 IN out NOCOPY per_positions.attribute19%type,
52 p_attribute20 IN out NOCOPY per_positions.attribute20%type,
53 p_last_update_date IN out NOCOPY per_positions.last_update_date%type,
54 p_last_updated_by IN out NOCOPY per_positions.last_updated_by%type,
55 p_last_update_login IN out NOCOPY per_positions.last_update_login%type,
56 p_created_by IN out NOCOPY per_positions.created_by%type,
57 p_creation_date IN out NOCOPY per_positions.creation_date%type,
58 p_org_name IN out NOCOPY hr_all_organization_units.name%type,
59 p_job_name IN out NOCOPY per_jobs.name%type,
60 p_location_code IN out NOCOPY hr_locations.location_code%type,
61 p_status_desc IN out NOCOPY hr_lookups.meaning%type,
62 p_frequency_desc IN out NOCOPY hr_lookups.meaning%type,
63 p_prob_units_desc IN out NOCOPY hr_lookups.meaning%type,
64 p_rep_req_desc IN out NOCOPY hr_lookups.meaning%type,
65 p_rel_name IN out NOCOPY per_positions.name%type,
66 p_succ_name IN out NOCOPY per_positions.name%type,
67 p_result_code IN out NOCOPY varchar2
68 )
69 IS
70 l_proc VARCHAR2(61) := g_package_name || 'CLOSE_CURSOR';
71 l_position_rec per_positions%rowtype;
72 l_position_rec_temp per_positions%rowtype;
73 l_result_code varchar2(30);
74
75 CURSOR c_org_name (p_org_id IN NUMBER) IS
76 SELECT org.name
77 FROM hr_all_organization_units org
78 WHERE org.organization_id = p_org_id;
79
80 CURSOR c_job_name (p_job_id IN NUMBER) IS
81 SELECT job.name
82 FROM per_jobs job
83 WHERE job.job_id = p_job_id;
84
85 CURSOR c_loc_code (p_loc_id IN NUMBER) IS
86 SELECT loc.location_code
87 FROM hr_locations loc
88 WHERE loc.location_id = p_loc_id;
89
90 CURSOR c_lookup_meaning (p_lookup_type VARCHAR2, p_lookup_code VARCHAR2) IS
91 SELECT hrl.meaning
92 FROM hr_lookups hrl
93 WHERE hrl.lookup_code = p_lookup_code
94 AND hrl.lookup_type = p_lookup_type;
95
96 BEGIN
97 hr_utility.set_location('Entering '|| l_proc, 10);
98 /*GHR_HISTORY_FETCH.fetch_position (
99 p_position_id => p_position_id,
100 p_date_effective => p_session_date,
101 p_position_data => l_position_rec,
102 p_result_code => p_result_code );
103 */
104
105 p_date_effective := l_position_rec.date_effective;
106 p_date_end := l_position_rec.date_end;
107 p_working_hours := l_position_rec.working_hours;
108 p_time_normal_start := l_position_rec.time_normal_start;
109 p_time_normal_finish := l_position_rec.time_normal_finish;
110 p_probation_period := l_position_rec.probation_period;
111 p_probation_period_units := l_position_rec.probation_period_units;
112 p_position_definition_id := l_position_rec.position_definition_id;
113 p_business_group_id := l_position_rec.business_group_id;
114 p_job_id := l_position_rec.job_id;
115 p_organization_id := l_position_rec.organization_id;
116 p_successor_position_id := l_position_rec.successor_position_id;
117 p_relief_position_id := l_position_rec.relief_position_id;
118 p_location_id := l_position_rec.location_id;
119 p_comments := l_position_rec.comments;
120 p_status := l_position_rec.status;
121 p_frequency := l_position_rec.frequency;
122 p_name := l_position_rec.name;
123 p_replacement_required_flag := l_position_rec.replacement_required_flag;
124 p_request_id := l_position_rec.request_id;
125 p_program_application_id := l_position_rec.program_application_id;
126 p_program_id := l_position_rec.program_id;
127 p_program_update_date := l_position_rec.program_update_date;
128 p_attribute_category := l_position_rec.attribute_category;
129 p_attribute1 := l_position_rec.attribute1;
130 p_attribute2 := l_position_rec.attribute2;
131 p_attribute3 := l_position_rec.attribute3;
132 p_attribute4 := l_position_rec.attribute4;
133 p_attribute5 := l_position_rec.attribute5;
134 p_attribute6 := l_position_rec.attribute6;
135 p_attribute7 := l_position_rec.attribute7;
136 p_attribute8 := l_position_rec.attribute8;
137 p_attribute9 := l_position_rec.attribute9;
138 p_attribute10 := l_position_rec.attribute10;
139 p_attribute11 := l_position_rec.attribute11;
140 p_attribute12 := l_position_rec.attribute12;
141 p_attribute13 := l_position_rec.attribute13;
142 p_attribute14 := l_position_rec.attribute14;
143 p_attribute15 := l_position_rec.attribute15;
144 p_attribute16 := l_position_rec.attribute16;
145 p_attribute17 := l_position_rec.attribute17;
146 p_attribute18 := l_position_rec.attribute18;
147 p_attribute19 := l_position_rec.attribute19;
148 p_attribute20 := l_position_rec.attribute20;
149 p_last_update_date := l_position_rec.last_update_date;
150 p_last_updated_by := l_position_rec.last_updated_by;
151 p_last_update_login := l_position_rec.last_update_login;
152 p_created_by := l_position_rec.created_by;
153 p_creation_date := l_position_rec.creation_date;
154
155 /*GHR_HISTORY_FETCH.fetch_position (
156 p_position_id => l_position_rec.relief_position_id,
157 p_date_effective => p_session_date,
158 p_position_data => l_position_rec_temp,
159 p_result_code => l_result_code );
160 */
161 p_rel_name := l_position_rec_temp.name;
162 /* GHR_HISTORY_FETCH.fetch_position (
163 p_position_id => l_position_rec.successor_position_id,
164 p_date_effective => p_session_date,
165 p_position_data => l_position_rec_temp,
166 p_result_code => l_result_code );
167 */
168 p_succ_name := l_position_rec_temp.name;
169 OPEN c_org_name (l_position_rec.organization_id);
170 FETCH c_org_name INTO p_org_name;
171 CLOSE c_org_name;
172
173 OPEN c_job_name (l_position_rec.job_id);
174 FETCH c_job_name INTO p_job_name;
175 CLOSE c_job_name;
176
177 OPEN c_loc_code (l_position_rec.location_id);
178 FETCH c_loc_code INTO p_location_code;
179 CLOSE c_loc_code;
180
181 OPEN c_lookup_meaning ('FREQUENCY', l_position_rec.frequency);
182 FETCH c_lookup_meaning INTO p_frequency_desc;
183 CLOSE c_lookup_meaning;
184
185 OPEN c_lookup_meaning ('YES_NO', l_position_rec.replacement_required_flag);
186 FETCH c_lookup_meaning INTO p_rep_req_desc;
187 CLOSE c_lookup_meaning;
188
189 OPEN c_lookup_meaning ('QUALIFYING_UNITS', l_position_rec.probation_period_units);
190 FETCH c_lookup_meaning INTO p_prob_units_desc;
191 CLOSE c_lookup_meaning;
192
193 OPEN c_lookup_meaning ('POSITION_STATUS', l_position_rec.status);
194 FETCH c_lookup_meaning INTO p_status_desc;
195 CLOSE c_lookup_meaning;
196
197 hr_utility.set_location('Exiting '|| l_proc, 100);
198 END;
199 END GHR_FETCH_POSITION_HISTORY;