[Home] [Help]
PACKAGE BODY: APPS.PA_MAPPING_PVT
Source
1 PACKAGE BODY PA_MAPPING_PVT as
2 /* $Header: PAYMPVTB.pls 120.2 2005/08/19 17:23:27 mwasowic noship $ */
3
4 --
5 -- Procedure : Save_value_maps
6 -- Purpose : Save value map records to PA_VALUE_MAPS.
7 --
8 --
9 PROCEDURE save_value_maps
10 ( p_value_map_def_id IN NUMBER ,
11 p_record_version_number IN NUMBER ,
12 p_source_value_arr IN system.pa_varchar2_240_tbl_type ,
13 p_dest_value_arr IN system.pa_varchar2_240_tbl_type ,
14 p_source_value_pk1_arr IN system.pa_varchar2_240_tbl_type ,
15 p_source_value_pk2_arr IN system.pa_varchar2_240_tbl_type ,
16 p_source_value_pk3_arr IN system.pa_varchar2_240_tbl_type ,
17 p_source_value_pk4_arr IN system.pa_varchar2_240_tbl_type ,
18 p_source_value_pk5_arr IN system.pa_varchar2_240_tbl_type ,
19 p_dest_value_pk1_arr IN system.pa_varchar2_240_tbl_type ,
20 p_dest_value_pk2_arr IN system.pa_varchar2_240_tbl_type ,
21 p_dest_value_pk3_arr IN system.pa_varchar2_240_tbl_type ,
22 p_dest_value_pk4_arr IN system.pa_varchar2_240_tbl_type ,
23 p_dest_value_pk5_arr IN system.pa_varchar2_240_tbl_type ,
24 p_probability_list_id IN NUMBER := NULL ,
25 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
26 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
27 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
28 IS
29 v_1 NUMBER;
30
31 l_source_value_arr PA_PLSQL_DATATYPES.Char240TabTyp;
32 l_dest_value_arr PA_PLSQL_DATATYPES.Char240TabTyp;
33 l_source_value_pk1_arr PA_PLSQL_DATATYPES.Char240TabTyp;
34 l_source_value_pk2_arr PA_PLSQL_DATATYPES.Char240TabTyp;
35 l_source_value_pk3_arr PA_PLSQL_DATATYPES.Char240TabTyp;
36 l_source_value_pk4_arr PA_PLSQL_DATATYPES.Char240TabTyp;
37 l_source_value_pk5_arr PA_PLSQL_DATATYPES.Char240TabTyp;
38 l_dest_value_pk1_arr PA_PLSQL_DATATYPES.Char240TabTyp;
39 l_dest_value_pk2_arr PA_PLSQL_DATATYPES.Char240TabTyp;
40 l_dest_value_pk3_arr PA_PLSQL_DATATYPES.Char240TabTyp;
41 l_dest_value_pk4_arr PA_PLSQL_DATATYPES.Char240TabTyp;
42 l_dest_value_pk5_arr PA_PLSQL_DATATYPES.Char240TabTyp;
43
44 l_value_map_def_type VARCHAR2(30);
45 l_id NUMBER;
46 l_msg_index_out NUMBER;
47 -- added for bug: 4537865
48 l_new_msg_data VARCHAR2(2000);
49 -- added for bug: 4537865
50
51 BEGIN
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53
54 -- Get value map definition type.
55 SELECT value_map_def_type
56 INTO l_value_map_def_type
57 FROM PA_VALUE_MAP_DEFS
58 WHERE value_map_def_id = p_value_map_def_id;
59
60 -- Name and Id validation.
61 v_1:= 1;
62 FOR j IN p_source_value_arr.FIRST .. p_source_value_arr.LAST LOOP
63 debug('j = '|| j);
64 debug('dest_value = '||p_dest_value_arr(j));
65 IF p_dest_value_arr(j) IS NOT NULL THEN
66 debug('v_1 = '||v_1);
67
68 IF l_value_map_def_type = 'PERSON_ROLE_OPP_PROJ'
69 OR l_value_map_def_type = 'ORG_ROLE_OPP_PROJ' THEN
70 PA_ROLE_UTILS.check_role_name_or_id (p_role_id => p_dest_value_pk1_arr(j),
71 p_role_name => p_dest_value_arr(j),
72 p_check_id_flag => 'Y',
73 x_role_id => l_id,
74 x_return_status => x_return_status,
75 x_error_message_code => x_msg_data);
76 ELSIF l_value_map_def_type = 'PROBABILITY_OPP_PROJ' THEN
77 PA_PROJECTS_MAINT_UTILS.check_probability_code_or_id(
78 p_probability_member_id => p_dest_value_pk1_arr(j),
79 p_probability_percentage => p_dest_value_arr(j),
80 p_project_type => NULL,
81 p_probability_list_id => p_probability_list_id,
82 p_check_id_flag => 'Y',
83 x_probability_member_id => l_id,
84 x_return_status => x_return_status,
85 x_error_msg_code => x_msg_data);
86 END IF;
87 debug('After name id validation');
88 debug('l_id = '|| l_id);
89 debug('x_return_status = '|| x_return_status);
90
91 -- Raise the error if the id/name validation is failed.
92 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
93 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
94 ,p_msg_name => x_msg_data);
95 RAISE FND_API.G_EXC_ERROR;
96 -- Put the validated mapping data in a local pl/sql table.
97 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
98 l_source_value_arr(v_1) := p_source_value_arr(j);
99 l_dest_value_arr(v_1) := p_dest_value_arr(j);
100 l_source_value_pk1_arr(v_1) := p_source_value_pk1_arr(j);
101 l_source_value_pk2_arr(v_1) := p_source_value_pk2_arr(j);
102 l_source_value_pk3_arr(v_1) := p_source_value_pk3_arr(j);
103 l_source_value_pk4_arr(v_1) := p_source_value_pk4_arr(j);
104 l_source_value_pk5_arr(v_1) := p_source_value_pk5_arr(j);
105 l_dest_value_pk1_arr(v_1) := TO_CHAR(l_id);
106 l_dest_value_pk2_arr(v_1) := p_dest_value_pk2_arr(j);
107 l_dest_value_pk3_arr(v_1) := p_dest_value_pk3_arr(j);
108 l_dest_value_pk4_arr(v_1) := p_dest_value_pk4_arr(j);
109 l_dest_value_pk5_arr(v_1) := p_dest_value_pk5_arr(j);
110 v_1 := v_1 +1;
111 END IF;
112 END IF;
113 END LOOP;
114
115 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
116 -- Update record version number in header table PA_VALUE_MAP_DEFS.
117 PA_VALUE_MAP_DEF_PKG.update_row(p_value_map_def_id => p_value_map_def_id,
118 p_record_version_number => p_record_version_number,
119 x_return_status => x_return_status,
120 x_msg_count => x_msg_count,
121 x_msg_data => x_msg_data);
122 debug('After update_row');
123 END IF;
124
125 -- Delete all the value map records identified by p_value_map_def_id.
126 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
127 debug('Before delete_rows');
128 PA_VALUE_MAPS_PKG.delete_rows(p_value_map_def_id => p_value_map_def_id,
129 p_value_map_def_type => l_value_map_def_type,
130 p_probability_list_id => p_probability_list_id,
131 x_return_status => x_return_status,
132 x_msg_count => x_msg_count,
133 x_msg_data => x_msg_data);
134 debug('After delete_rows');
135 END IF;
136
137 -- Insert the new value map records.
138 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
139 debug('l_dest_value_arr.COUNT = '||l_dest_value_arr.COUNT);
140 IF l_dest_value_arr.COUNT > 0 THEN
141 debug('Before insert_rows');
142 PA_VALUE_MAPS_PKG.insert_rows (
143 p_value_map_def_id => p_value_map_def_id ,
144 p_source_value_arr => l_source_value_arr ,
145 p_dest_value_arr => l_dest_value_arr ,
146 p_source_value_pk1_arr => l_source_value_pk1_arr ,
147 p_source_value_pk2_arr => l_source_value_pk2_arr ,
148 p_source_value_pk3_arr => l_source_value_pk3_arr ,
149 p_source_value_pk4_arr => l_source_value_pk4_arr ,
150 p_source_value_pk5_arr => l_source_value_pk5_arr ,
151 p_dest_value_pk1_arr => l_dest_value_pk1_arr ,
152 p_dest_value_pk2_arr => l_dest_value_pk2_arr ,
153 p_dest_value_pk3_arr => l_dest_value_pk3_arr ,
154 p_dest_value_pk4_arr => l_dest_value_pk4_arr ,
155 p_dest_value_pk5_arr => l_dest_value_pk5_arr ,
156 x_return_status => x_return_status ,
157 x_msg_count => x_msg_count ,
158 x_msg_data => x_msg_data );
159 END IF;
160 debug('After insert_rows');
161 END IF;
162
163 EXCEPTION
164 WHEN FND_API.G_EXC_ERROR THEN
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 x_msg_count := FND_MSG_PUB.Count_Msg;
167 IF x_msg_count = 1 THEN
168 pa_interface_utils_pub.get_messages
169 (p_encoded => FND_API.G_TRUE,
170 p_msg_index => 1,
171 p_msg_count => x_msg_count,
172 p_msg_data => x_msg_data,
173 -- p_data => x_msg_data, * Commented for Bug: 4537865
174 p_data => l_new_msg_data, -- added for bug: 4537865
175 p_msg_index_out => l_msg_index_out );
176 -- added for bug: 4537865
177 x_msg_data := l_new_msg_data;
178 -- added for bug: 4537865
179 END IF;
180 WHEN OTHERS THEN
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_MAPPING_PVT',
183 p_procedure_name => 'save_value_maps');
184 raise;
185
186 END save_value_maps;
187
188
189 --
190 -- Procedure : Get_dest_values
191 -- Purpose : Get the corresponding destination values given source
192 -- values and a value map definition.
193 --
194 --
195 PROCEDURE get_dest_values
196 ( p_value_map_def_type IN VARCHAR2 ,
197 p_def_subtype IN VARCHAR2 ,
198 p_source_value IN VARCHAR2 ,
199 p_source_value_pk1 IN VARCHAR2 ,
200 p_source_value_pk2 IN VARCHAR2 ,
201 p_source_value_pk3 IN VARCHAR2 ,
202 p_source_value_pk4 IN VARCHAR2 ,
203 p_source_value_pk5 IN VARCHAR2 ,
204 p_probability_list_id IN NUMBER := NULL,
205 x_dest_value OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
206 x_dest_value_pk1 OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
207 x_dest_value_pk2 OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
208 x_dest_value_pk3 OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
209 x_dest_value_pk4 OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
210 x_dest_value_pk5 OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
211 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
212 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
213 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
214 IS
215
216 CURSOR c1 IS
217 SELECT dest_value, dest_value_pk1, dest_value_pk2, dest_value_pk3, dest_value_pk4, dest_value_pk5
218 FROM pa_value_maps map, pa_value_map_defs def
219 WHERE map.value_map_def_id = def.value_map_def_id
220 AND def.value_map_def_type = p_value_map_def_type
221 AND def.def_subtype = p_def_subtype
222 AND map.source_value = p_source_value
223 AND (map.source_value_pk1 = NVL(p_source_value_pk1, map.source_value_pk1)
224 OR map.source_value_pk1 IS NULL)
225 AND (map.source_value_pk2 = NVL(p_source_value_pk2, map.source_value_pk2)
226 OR map.source_value_pk2 IS NULL)
227 AND (map.source_value_pk3 = NVL(p_source_value_pk3, map.source_value_pk3)
228 OR map.source_value_pk3 IS NULL)
229 AND (map.source_value_pk4 = NVL(p_source_value_pk4, map.source_value_pk4)
230 OR map.source_value_pk4 IS NULL)
231 AND (map.source_value_pk5 = NVL(p_source_value_pk5, map.source_value_pk5)
232 OR map.source_value_pk5 IS NULL);
233
234
235 BEGIN
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237
238 x_dest_value := NULL;
239 x_dest_value_pk1 := NULL;
240 x_dest_value_pk2 := NULL;
241 x_dest_value_pk3 := NULL;
242 x_dest_value_pk4 := NULL;
243 x_dest_value_pk5 := NULL;
244
245 FOR v_c1 IN c1 LOOP
246 IF p_value_map_def_type = 'PROBABILITY_OPP_PROJ' THEN
247 IF v_c1.dest_value_pk2 = p_probability_list_id THEN
248 x_dest_value := v_c1.dest_value;
249 x_dest_value_pk1 := v_c1.dest_value_pk1;
250 x_dest_value_pk2 := v_c1.dest_value_pk2;
251 x_dest_value_pk3 := v_c1.dest_value_pk3;
252 x_dest_value_pk4 := v_c1.dest_value_pk4;
253 x_dest_value_pk5 := v_c1.dest_value_pk5;
254 RETURN;
255 END IF;
256 ELSE
257 x_dest_value := v_c1.dest_value;
258 x_dest_value_pk1 := v_c1.dest_value_pk1;
259 x_dest_value_pk2 := v_c1.dest_value_pk2;
260 x_dest_value_pk3 := v_c1.dest_value_pk3;
261 x_dest_value_pk4 := v_c1.dest_value_pk4;
262 x_dest_value_pk5 := v_c1.dest_value_pk5;
263 RETURN;
264 END IF;
265
266 END LOOP;
267
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
272 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_MAPPING_PVT',
273 p_procedure_name => 'get_dest_values');
274 raise;
275
276 END get_dest_values;
277
278
279 PROCEDURE debug(p_msg IN VARCHAR2) IS
280 BEGIN
281 --dbms_output.put_line('pa_mapping_pvt'|| ' : ' || p_msg);
282 PA_DEBUG.WRITE_LOG(
283 x_module => 'pa.plsql.pa_mapping_pvt',
284 x_msg => p_msg,
285 x_log_level => 6);
286 END debug;
287
288
289 END PA_MAPPING_PVT;