[Home] [Help]
PACKAGE BODY: APPS.IGS_PERSONSTATS_PUB
Source
1 PACKAGE BODY IGS_PERSONSTATS_PUB AS
2 /* $Header: IGSPAPSB.pls 120.0 2006/05/02 05:37:47 apadegal noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2 (30):='IGS_PERSONSTATS_PUB';
4
5 PROCEDURE check_length(p_param_name IN VARCHAR2, p_table_name IN VARCHAR2, p_param_length IN NUMBER) AS
6 CURSOR c_col_length IS
7 SELECT WIDTH , precision , column_type ,scale
8 FROM FND_COLUMNS
9 WHERE table_id IN
10 (SELECT TABLE_ID
11 FROM FND_TABLES
12 WHERE table_name = p_table_name AND APPLICATION_ID = 8405)
13 AND column_name = p_param_name
14 AND APPLICATION_ID = 8405;
15
16 l_col_length c_col_length%ROWTYPE;
17 begin
18 OPEN c_col_length;
19 FETCH c_col_length INTO l_col_length;
20 CLOSE c_col_length;
21 IF l_col_length.column_type = 'V' AND p_param_length > l_col_length.width THEN
22 FND_MESSAGE.SET_NAME('IGS','IGS_AD_EXCEED_MAX_LENGTH');
23 FND_MESSAGE.SET_TOKEN('PARAMETER',p_param_name);
24 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.width);
25 IGS_GE_MSG_STACK.ADD;
26 RAISE FND_API.G_EXC_ERROR;
27
28
29 ELSIF l_col_length.column_type ='N' AND p_param_length > (l_col_length.precision - l_col_length.scale) THEN
30 FND_MESSAGE.SET_NAME('IGS','IGS_AD_EXCEED_MAX_LENGTH');
31 FND_MESSAGE.SET_TOKEN('PARAMETER',p_param_name);
32 IF l_col_length.scale > 0 THEN
33 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.precision || ',' || l_col_length.scale);
34 ELSE
35 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.precision );
36 END IF;
37 IGS_GE_MSG_STACK.ADD;
38 RAISE FND_API.G_EXC_ERROR;
39 END IF;
40
41
42 END check_length;
43
44
45
46
47
48 --API
49 PROCEDURE REDERIVE_PERSON_STATS(
50 --Standard Parameters Start
51 p_api_version IN NUMBER,
52 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
53 p_commit IN VARCHAR2 default FND_API.G_FALSE,
54 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2,
58 --Standard parameter ends
59 p_person_id IN NUMBER,
60 p_group_id IN NUMBER,
61 x_return_status_tbl OUT NOCOPY Return_Status_Tbl_Type
62 )
63 AS
64 l_api_version CONSTANT NUMBER := '1.0';
65 l_api_name CONSTANT VARCHAR2(30) := 'REDERIVE_PERSONSTATS';
66 l_msg_index NUMBER;
67 l_return_status VARCHAR2(1);
68 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
69 l_ctr NUMBER DEFAULT 0;
70 l_person_id NUMBER;
71 l_group_id NUMBER;
72 l_ind_g_exec_error BOOLEAN DEFAULT FALSE; -- individual unexpected err occured
73 l_ind_unc_exec_error BOOLEAN DEFAULT FALSE; -- individual generic err occured
74
75
76
77 CURSOR c_person_group IS
78 SELECT
79 person_id
80 FROM
81 igs_pe_prsid_grp_mem_v
82 WHERE group_id = p_group_id
83 AND NVL(TRUNC(start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
84 AND NVL(TRUNC(end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
85
86 CURSOR c_group_id(p_person_id hz_parties.party_id%TYPE,p_group_id igs_pe_persid_group.group_id%TYPE) IS
87 SELECT 'X'
88 FROM igs_pe_prsid_grp_mem
89 WHERE person_id = p_person_id
90 AND group_id = p_group_id
91 AND NVL(TRUNC(start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
92 AND NVL(TRUNC(end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
93 l_exists VARCHAR2(1);
94
95 BEGIN
96
97 l_msg_index := 0;
98
99 -- Standard call to check for call compatibility.
100 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
101 RAISE FND_API.G_EXC_ERROR;
102 END IF;
103
104 -- Initialize message list if p_init_msg_list is set to TRUE.
105 IF FND_API.to_Boolean( p_init_msg_list ) THEN
106 FND_MSG_PUB.initialize;
107 END IF;
108
109
110 -- Validate all the parameters for their length
111 -- PERSON_ID
112 check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
113 -- P_GROUP_ID
114 check_length('GROUP_ID', 'IGS_PE_PERSID_GROUP_ALL', length(TRUNC(p_group_id)));
115 -- Initialize API return status to success
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117 ------------------------------
118 --Intialization of varable to handle G_MISS_CHAR/NUM/DATE
119 -------------------------------
120
121 IF p_group_id IS NOT NULL and p_person_id IS NOT NULL
122 THEN
123 OPEN c_group_id(p_person_id,p_group_id);
124 FETCH c_group_id INTO l_exists;
125 IF c_group_id%NOTFOUND THEN
126 FND_MESSAGE.SET_NAME('IGS','IGS_AD_INVALID_PER_ID_GRP');
127 IGS_GE_MSG_STACK.ADD;
128 RAISE FND_API.G_EXC_ERROR;
129
130 END IF;
131 CLOSE c_group_id;
132 END IF;
133
134 IF p_group_id IS NULL and p_person_id IS NULL
135 THEN
136 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRS_PRSIDGRP_NULL');
137 IGS_GE_MSG_STACK.ADD;
138 RAISE FND_API.G_EXC_ERROR;
139
140 END IF;
141
142
143 IF p_group_id IS NULL THEN
144
145 IF p_person_id IS NOT NULL THEN --When p_group_id is not null and p_person_id is null
146
147 l_msg_index := igs_ge_msg_stack.count_msg;
148 BEGIN
149 SAVEPOINT REDERIVE_PERSON_STATS_PUB;
150
151 IGS_AD_UPD_INITIALISE.update_per_stats(p_person_id,NULL);
152
153 IF FND_API.To_Boolean( p_commit ) THEN
154 COMMIT WORK;
155 END IF;
156 x_return_status := FND_API.G_RET_STS_SUCCESS;
157 -- Standard call to get message count and if count is 1, get message info.
158 EXCEPTION
159 WHEN FND_API.G_EXC_ERROR THEN
160 ROLLBACK TO REDERIVE_PERSON_STATS_PUB;
161 x_return_status := FND_API.G_RET_STS_ERROR ;
162 igs_ad_gen_016.extract_msg_from_stack (
163 p_msg_at_index => l_msg_index,
164 p_return_status => l_return_status,
165 p_msg_count => x_msg_count,
166 p_msg_data => x_msg_data,
167 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
168 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-2).text;
169 x_msg_count := x_msg_count-1;
170
171 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
172 ROLLBACK TO REDERIVE_PERSON_STATS_PUB;
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
174 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
175 p_data => x_msg_data);
176
177 WHEN OTHERS THEN
178 ROLLBACK TO REDERIVE_PERSON_STATS_PUB;
179 igs_ad_gen_016.extract_msg_from_stack (
180 p_msg_at_index => l_msg_index,
181 p_return_status => l_return_status,
182 p_msg_count => x_msg_count,
183 p_msg_data => x_msg_data,
184 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
185 IF l_hash_msg_name_text_type_tab(x_msg_count-1).name <> 'ORA' THEN
186 x_return_status := FND_API.G_RET_STS_ERROR ;
187 ELSE
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
189 END IF;
190 END;
191 END IF;
192 ELSE -- group id is passed
193
194
195 FOR c_person_group_rec in c_person_group
196 LOOP
197 l_ctr := l_ctr + 1;
198 l_msg_index := igs_ge_msg_stack.count_msg;
199 BEGIN
200 SAVEPOINT REDERIVE_PERSON_STATS_PUB;
201 x_return_status_tbl(l_ctr).Person_id := c_person_group_rec.person_id;
202 IGS_AD_UPD_INITIALISE.update_per_stats(c_person_group_rec.person_id,NULL);
203
204 IF FND_API.To_Boolean( p_commit ) THEN
205 COMMIT WORK;
206 END IF;
207
208 x_return_status_tbl(l_ctr).sub_return_status := FND_API.G_RET_STS_SUCCESS;
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210 -- Standard call to get message count and if count is 1, get message info.
211 EXCEPTION
212 WHEN FND_API.G_EXC_ERROR THEN
213 l_ind_g_exec_error := TRUE;
214 ROLLBACK TO REDERIVE_PERSON_STATS_PUB;
215 x_return_status_tbl(l_ctr).sub_return_status := FND_API.G_RET_STS_ERROR ;
216 igs_ad_gen_016.extract_msg_from_stack (
217 p_msg_at_index => l_msg_index,
218 p_return_status => l_return_status,
219 p_msg_count => x_return_status_tbl(l_ctr).sub_msg_count,
220 p_msg_data => x_return_status_tbl(l_ctr).sub_msg_data,
221 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
222 x_return_status_tbl(l_ctr).sub_msg_data := l_hash_msg_name_text_type_tab(x_return_status_tbl(l_ctr).sub_msg_count-2).text;
223 x_return_status_tbl(l_ctr).sub_msg_count := x_return_status_tbl(l_ctr).sub_msg_count-1;
224
225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
226 l_ind_unc_exec_error := TRUE;
227 ROLLBACK TO REDERIVE_PERSON_STATS_PUB;
228 x_return_status_tbl(l_ctr).sub_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 FND_MSG_PUB.Count_And_Get(p_count => x_return_status_tbl(l_ctr).sub_msg_count,
230 p_data => x_return_status_tbl(l_ctr).sub_msg_data);
231
232 WHEN OTHERS THEN
233
234 ROLLBACK TO REDERIVE_PERSON_STATS_PUB;
235
236 igs_ad_gen_016.extract_msg_from_stack (
237 p_msg_at_index => l_msg_index,
238 p_return_status => l_return_status,
239 p_msg_count => x_return_status_tbl(l_ctr).sub_msg_count,
240 p_msg_data => x_return_status_tbl(l_ctr).sub_msg_data,
241 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
242
243
244 IF l_hash_msg_name_text_type_tab(x_return_status_tbl(l_ctr).sub_msg_count-1).name <> 'ORA' THEN
245 l_ind_g_exec_error := TRUE;
246 x_return_status_tbl(l_ctr).sub_return_status := FND_API.G_RET_STS_ERROR ;
247 ELSE
248 l_ind_unc_exec_error := TRUE;
249 x_return_status_tbl(l_ctr).sub_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
250 END IF;
251
252 END;
253 END LOOP;
254
255 IF l_ind_unc_exec_error THEN
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257 ELSIF l_ind_g_exec_error THEN
258 x_return_status := FND_API.G_RET_STS_ERROR ;
259 ELSE
260 x_return_status := FND_API.G_RET_STS_SUCCESS;
261 END IF;
262 END IF;
263 EXCEPTION ---- This expection block is for the whole api.
264 WHEN FND_API.G_EXC_ERROR THEN
265
266 x_return_status := FND_API.G_RET_STS_ERROR ;
267 igs_ad_gen_016.extract_msg_from_stack (
268 p_msg_at_index => l_msg_index,
269 p_return_status => l_return_status,
270 p_msg_count => x_msg_count,
271 p_msg_data => x_msg_data,
272 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
273 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-2).text;
274 x_msg_count := x_msg_count-1;
275
276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
277
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
280 p_data => x_msg_data);
281
282 WHEN OTHERS THEN
283
284
285 igs_ad_gen_016.extract_msg_from_stack (
286 p_msg_at_index => l_msg_index,
287 p_return_status => l_return_status,
288 p_msg_count => x_msg_count,
289 p_msg_data => x_msg_data,
290 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
291
292 IF l_hash_msg_name_text_type_tab(x_msg_count-1).name <> 'ORA' THEN
293 x_return_status := FND_API.G_RET_STS_ERROR ;
294 ELSE
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 END IF;
297
298
299 END REDERIVE_PERSON_STATS;
300
301
302
303
304 END IGS_PERSONSTATS_PUB;