[Home] [Help]
PACKAGE BODY: APPS.PON_CF_TYPE_GRP
Source
1 PACKAGE BODY PON_CF_TYPE_GRP AS
2 /* $Header: PONGCFTB.pls 120.0 2005/06/01 19:59:28 appldev noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_CF_TYPE_GRP';
5
6 --------------------------------------------------------------------------------
7 -- Private procedure/function definitions --
8 --------------------------------------------------------------------------------
9
10 --------------------------------------------------------------------------------
11 -- Public procedure/function definition --
12 --------------------------------------------------------------------------------
13
14 --------------------------------------------------------------------------------
15 --- get_cost_factor_details --
16 --------------------------------------------------------------------------------
17
18 PROCEDURE get_Cost_Factor_details(
19 p_api_version IN NUMBER
20 ,p_price_element_id IN pon_price_element_types.price_element_type_id%TYPE
21 ,p_price_element_code IN pon_price_element_types.price_element_code%TYPE DEFAULT NULL
22 ,p_name IN pon_price_element_types_tl.name%TYPE DEFAULT NULL
23 ,x_cost_factor_rec OUT NOCOPY pon_price_element_types_vl%ROWTYPE
24 ,x_return_status OUT NOCOPY VARCHAR2
25 ,x_msg_data OUT NOCOPY VARCHAR2
26 ,x_msg_count OUT NOCOPY NUMBER
27 ) IS
28
29 l_api_name CONSTANT VARCHAR2(30) := 'GET_COST_FACTOR_DETAILS';
30 l_api_version CONSTANT NUMBER := 1.0;
31 l_stage VARCHAR2(50);
32
33
34 TYPE cost_factor_type IS REF CURSOR RETURN pon_price_element_types_vl%ROWTYPE;
35 cost_factor_cur cost_factor_type;
36
37 l_query_ind VARCHAR2(1);
38
39 BEGIN
40
41 -- Check for API comptability
42 l_stage := '10: API check';
43
44 IF fnd_api.compatible_api_call(
45 p_current_version_number => l_api_version
46 ,p_caller_version_number => p_api_version
47 ,p_api_name => l_api_name
48 ,p_pkg_name => g_pkg_name)
49 THEN
50 NULL;
51 ELSE
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53 END IF;
54
55 -- Check for input parameters
56 l_stage := '20: input parameter check';
57 l_query_ind := 'I'; -- set to query by id
58 IF p_price_element_id IS NULL THEN
59 l_query_ind := 'C'; -- query by code
60 IF p_price_element_code IS NULL THEN
61 l_query_ind := 'N'; -- query by name
62 IF p_name IS NULL THEN
63 -- all input parameters are null, raise error
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66 END IF;
67 END IF;
68
69 -- decode query indicator and execute query
70 l_stage := '30: running query';
71 IF l_query_ind = 'I' THEN
72 l_stage := '40: running query for ID';
73 OPEN cost_factor_cur
74 FOR SELECT * FROM pon_price_element_types_vl
75 WHERE price_element_type_id = p_price_element_id;
76 FETCH cost_factor_cur INTO x_cost_factor_rec;
77 IF cost_factor_cur%NOTFOUND THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79 ELSE
80 CLOSE cost_factor_cur;
81 END IF;
82
83 ELSIF l_query_ind = 'C' THEN
84 l_stage := '50: running query for CODE';
85 OPEN cost_factor_cur
86 FOR SELECT * FROM pon_price_element_types_vl
87 WHERE price_element_code = p_price_element_code;
88 FETCH cost_factor_cur INTO x_cost_factor_rec;
89 IF cost_factor_cur%NOTFOUND THEN
90 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
91 ELSE
92 CLOSE cost_factor_cur;
93 END IF;
94
95 ELSIF l_query_ind = 'N' THEN
96 l_stage := '40: running query for NAME';
97 OPEN cost_factor_cur
98 FOR SELECT * FROM pon_price_element_types_vl
99 WHERE name = p_name;
100 FETCH cost_factor_cur INTO x_cost_factor_rec;
101 IF cost_factor_cur%NOTFOUND THEN
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 ELSE
104 CLOSE cost_factor_cur;
105 END IF;
106 END IF;
107
108 x_return_status := fnd_api.G_RET_STS_SUCCESS;
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
113 IF cost_factor_cur%ISOPEN THEN
114 CLOSE cost_factor_cur;
115 END IF;
116 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
117 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,SQLERRM);
118 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
119 fnd_log.string(log_level => fnd_log.level_unexpected
120 ,module => g_pkg_name ||'.'||l_api_name
121 ,message => l_stage || ': ' || SQLERRM);
122 fnd_log.string(log_level=>fnd_log.level_unexpected
123 ,module =>g_pkg_name ||'.'||l_api_name
124 ,message => 'Input parameter list: ' );
125 fnd_log.string(log_level=>fnd_log.level_unexpected
126 ,module =>g_pkg_name ||'.'||l_api_name
127 ,message => 'Price element type id: '||p_price_element_id);
128 fnd_log.string(log_level=>fnd_log.level_unexpected
129 ,module =>g_pkg_name ||'.'||l_api_name
130 ,message => 'Price element code: '|| p_price_element_code );
131 fnd_log.string(log_level=>fnd_log.level_unexpected
132 ,module =>g_pkg_name ||'.'||l_api_name
133 ,message => 'Price element type name: '||p_name );
134 fnd_log.string(log_level=>fnd_log.level_unexpected
135 ,module =>g_pkg_name ||'.'||l_api_name
136 ,message => 'p_api_version: '||p_api_version );
137 END IF;
138 END IF;
139 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
140 ,p_data => x_msg_data);
141 END;
142
143 ----------------------------------------------------------------------
144 --- opm_create_update_cost_factor ---
145 ----------------------------------------------------------------------
146
147
148 PROCEDURE opm_create_update_cost_factor(
149 p_api_version IN NUMBER
150 ,p_price_element_code IN pon_price_element_types.price_element_code%TYPE
151 ,p_pricing_basis IN pon_price_element_types.pricing_basis%TYPE
152 ,p_cost_component_class_id IN pon_price_element_types.cost_component_class_id%TYPE
153 ,p_cost_analysis_code IN pon_price_element_types.cost_analysis_code%TYPE
154 ,p_cost_acquisition_code IN pon_price_element_types.cost_acquisition_code%TYPE
155 ,p_name IN pon_price_element_types_tl.name%TYPE
156 ,p_description IN pon_price_element_types_tl.name%TYPE
157 ,x_insert_update_action OUT NOCOPY VARCHAR2
158 ,x_price_element_type_id OUT NOCOPY pon_price_element_types.price_element_type_id%TYPE
159 ,x_pricing_basis OUT NOCOPY pon_price_element_types.pricing_basis%TYPE
160 ,x_return_status OUT NOCOPY VARCHAR2
161 ,x_msg_data OUT NOCOPY VARCHAR2
162 ,x_msg_count OUT NOCOPY NUMBER
163 ) IS
164
165 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_UPDATE_COST_FACTOR';
166 l_api_version CONSTANT NUMBER := 1.0;
167 l_stage VARCHAR2(50);
168 l_temp VARCHAR2(3);
169
170 l_price_element_type_id pon_price_element_types.price_element_type_id%TYPE;
171
172 l_source_language fnd_languages.language_code%TYPE;
173
174 BEGIN
175
176 -- Check for API comptability
177
178 l_stage := '10: API check';
179
180 IF fnd_api.compatible_api_call(
181 p_current_version_number => l_api_version
182 ,p_caller_version_number => p_api_version
183 ,p_api_name => l_api_name
184 ,p_pkg_name => g_pkg_name)
185 THEN
186 NULL;
187 ELSE
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END IF;
190
191 l_stage := '20: PE Code check';
192 -- Check if the code exists. If it does, we need to update the information
193 -- otherwise we need to insert the information
194
195 BEGIN
196
197 SELECT price_element_type_id
198 ,pricing_basis
199 INTO l_price_element_type_id
200 ,x_pricing_basis
201 FROM pon_price_element_types
202 WHERE price_element_code = p_price_element_code;
203
204 l_stage := '25: PE Code found';
205
206 x_insert_update_action := 'UPDATE';
207
208 EXCEPTION
209 WHEN NO_DATA_FOUND
210 THEN
211 x_insert_update_action := 'INSERT';
212 x_pricing_basis := p_pricing_basis;
213 l_price_element_type_id := NULL;
214 END;
215
216 -- Validate the pricing basis
217 l_stage := '30: Cost analysis code check';
218
219 BEGIN
220
221 SELECT 'x'
222 INTO l_temp
223 FROM fnd_lookups lkp
224 WHERE lkp.lookup_type = 'PON_PRICING_BASIS'
225 AND lkp.lookup_code = p_pricing_basis;
226
227 EXCEPTION
228
229 WHEN NO_DATA_FOUND
230 THEN
231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
232 END;
233
234 -- Validate the cost analysis code
235
236 l_stage := '40: Cost analysis code check';
237
238 BEGIN
239
240 SELECT 'x'
241 INTO l_temp
242 FROM CM_ALYS_MST c
243 WHERE c.cost_analysis_code = p_cost_analysis_code;
244
245 EXCEPTION
246 WHEN NO_DATA_FOUND
247 THEN
248 -- Raise fatal error for invalid cost analysis code
249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
250 END;
251
252 -- Validate the cost component class id
253
254 BEGIN
255
256 l_stage := '50: Cost component class check';
257
258 SELECT 'x'
259 INTO l_temp
260 FROM cm_cmpt_mst_b c
261 WHERE c.cost_cmpntcls_id = p_cost_component_class_id;
262
263 EXCEPTION
264 WHEN NO_DATA_FOUND
265 THEN
266 -- raise error for invalid cost component class id
267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
268 END;
269
270 -- Get the installed language
271
272 BEGIN
273
274 l_stage := '60: Get source language';
275
276 SELECT language_code
277 INTO l_source_language
278 FROM fnd_languages fndlang
279 WHERE installed_flag = 'B';
280
281 END;
282
283
284 x_price_element_type_id := l_price_element_type_id;
285
286 IF x_insert_update_action = 'INSERT'
287 THEN
288
289 l_stage := '100: Insert cost factor';
290
291 INSERT INTO PON_PRICE_ELEMENT_TYPES
292 (
293 price_element_type_id
294 ,trading_partner_id
295 ,price_element_code
296 ,pricing_basis
297 ,enabled_flag
298 ,system_flag
299 ,creation_date
300 ,created_by
301 ,last_update_date
302 ,last_updated_by
303 ,allocation_basis
304 ,invoice_line_type
305 ,cost_analysis_code
306 ,cost_component_class_id
307 ,cost_acquisition_code
308 )
309 VALUES
310 (
311 pon_price_element_types_s.NEXTVAL
312 ,0 -- trading_partner_id
313 ,p_price_element_code
314 ,p_pricing_basis
315 ,'Y' -- enabled_flag
316 ,'N' -- system_flag
317 ,SYSDATE -- creation_date,
318 ,fnd_global.user_id -- created_by
319 ,SYSDATE -- last_update_date
320 ,fnd_global.user_id -- last_updated_by
321 ,NULL -- allocation_basis
322 ,NULL -- invoice_line_type
323 ,p_cost_analysis_code
324 ,p_cost_component_class_id
325 ,p_cost_acquisition_code
326 )
327 RETURNING
328 price_element_type_id INTO l_price_element_type_id;
329
330 x_price_element_type_id := l_price_element_type_id;
331
332 -- If an existing code, then update information
333 -- Business rule: If an existing code, then the name and description
334 -- that are passed in are ignored and the _TL table is not updated
335 -- The id is passed back so that OPM can update their tables
336
337 l_stage := '120: Insert cost factor tl';
338
339 INSERT INTO pon_price_element_types_tl
340 ( price_element_type_id
341 ,trading_partner_id
342 ,name
343 ,description
344 ,language
345 ,source_lang
346 ,creation_date
347 ,created_by
348 ,last_update_date
349 ,last_updated_by)
350 SELECT
351 l_price_element_type_id
352 ,0 -- trading_partner_id
353 ,p_name
354 ,p_description
355 ,fndlang.language_code
356 ,l_source_language -- source_lang
357 ,SYSDATE -- creation_date
358 ,fnd_global.user_id -- created_by
359 ,SYSDATE -- last_update_date
360 ,fnd_global.user_id -- last_updated_by
361 FROM fnd_languages fndlang
362 WHERE fndlang.installed_flag IN ('I','B');
363
364 ELSE -- if not a new cost factor
365
366 -- If the cost factor is existing, then we only update the base table
367 -- and ignore the passed in name, description - as per discussion with
368 -- the OPM team
369
370 l_stage := '150: Update cost factor';
371
372 UPDATE pon_price_element_types
373 SET
374 last_update_date = SYSDATE
375 ,last_updated_by = fnd_global.user_id
376 ,cost_analysis_code = p_cost_analysis_code
377 ,cost_component_class_id = p_cost_component_class_id
378 ,cost_acquisition_code = p_cost_acquisition_code
379 WHERE price_element_type_id = l_price_element_type_id;
380
381 l_stage := '160: Check rows update';
382
383 IF SQL%NOTFOUND
384 THEN
385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
386 END IF;
387
388 END IF;
389
390 x_return_status := fnd_api.G_RET_STS_SUCCESS;
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
395 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
396 THEN
397 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,SQLERRM);
398 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
399 THEN
400 fnd_log.string(log_level => fnd_log.level_unexpected
401 ,module => g_pkg_name ||'.'||l_api_name
402 ,message => l_stage || ': ' || SQLERRM);
403 fnd_log.string(log_level=>fnd_log.level_unexpected,
404 module =>g_pkg_name ||'.'||l_api_name,
405 message => 'Input parameter list: ' );
406 fnd_log.string(log_level=>fnd_log.level_unexpected,
407 module =>g_pkg_name ||'.'||l_api_name,
408 message => 'p_api_version = ' || p_api_version);
409 fnd_log.string(log_level=>fnd_log.level_unexpected,
410 module =>g_pkg_name ||'.'||l_api_name,
411 message => 'p_price_element_code = ' || p_price_element_code);
412 fnd_log.string(log_level=>fnd_log.level_unexpected,
413 module =>g_pkg_name ||'.'||l_api_name,
414 message => 'p_pricing_basis = ' || p_pricing_basis);
415 fnd_log.string(log_level=>fnd_log.level_unexpected,
416 module =>g_pkg_name ||'.'||l_api_name,
417 message => 'p_cost_component_class_id = ' || p_cost_component_class_id);
418 fnd_log.string(log_level=>fnd_log.level_unexpected,
419 module =>g_pkg_name ||'.'||l_api_name,
420 message => 'p_cost_analysis_code = ' || p_cost_analysis_code);
421 fnd_log.string(log_level=>fnd_log.level_unexpected,
422 module =>g_pkg_name ||'.'||l_api_name,
423 message => 'p_cost_acquisition_code = ' || p_cost_acquisition_code);
424 fnd_log.string(log_level=>fnd_log.level_unexpected,
425 module =>g_pkg_name ||'.'||l_api_name,
426 message => 'p_name = ' || p_name);
427 fnd_log.string(log_level=>fnd_log.level_unexpected,
428 module =>g_pkg_name ||'.'||l_api_name,
429 message => 'p_description = ' || p_description);
430 END IF ;
431 END IF;
432 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
433 ,p_data => x_msg_data);
434
435
436 END opm_create_update_cost_factor;
437
438 --------------------------------------------------------------------------------
439 --- OVERLOADED get_cost_factor_details --
440 --------------------------------------------------------------------------------
441
442 FUNCTION get_Cost_Factor_details(
443 p_price_element_id IN pon_price_element_types.price_element_type_id%TYPE
444 )
445 RETURN pon_price_element_types_vl%ROWTYPE IS
446
447 l_api_name CONSTANT VARCHAR2(30) := 'get_cost_factor_details';
448 l_stage VARCHAR2(30);
449
450 CURSOR cost_factor_cur (p_cf_type_id NUMBER) IS
451 SELECT *
452 FROM pon_price_element_types_vl
453 WHERE price_element_type_id = p_cf_type_id;
454
455 l_cost_factor_type_rec cost_factor_cur%ROWTYPE;
456
457 BEGIN
458
459 l_stage := '10: Execute curosr for ID';
460
461 IF p_price_element_id IS NOT NULL THEN
462 OPEN cost_factor_cur(p_price_element_id);
463 FETCH cost_factor_cur INTO l_cost_factor_type_rec;
464 CLOSE cost_factor_cur;
465 END IF;
466
467 RETURN l_cost_factor_type_rec;
468
469 EXCEPTION
470 WHEN OTHERS THEN
471 IF cost_factor_cur%ISOPEN THEN
472 CLOSE cost_factor_cur;
473 END IF;
474 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
475 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,SQLERRM);
476 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
477 fnd_log.string(log_level => fnd_log.level_unexpected
478 ,module => g_pkg_name ||'.'||l_api_name
479 ,message => l_stage || ': ' || SQLERRM);
480 fnd_log.string(log_level=>fnd_log.level_unexpected
481 ,module =>g_pkg_name ||'.'||l_api_name
482 ,message => 'Input parameter: ' );
483 fnd_log.string(log_level=>fnd_log.level_unexpected
484 ,module =>g_pkg_name ||'.'||l_api_name
485 ,message => 'Price element type id: '||p_price_element_id);
486 END IF;
487 END IF;
488 RETURN l_cost_factor_type_rec;
489 END;
490
491 --------------------------------------------------------------------------------
492 --- OVERLOADED get_cost_factor_details --
493 --------------------------------------------------------------------------------
494
495 FUNCTION get_Cost_Factor_details(
496 p_price_element_code IN pon_price_element_types.price_element_code%TYPE
497 )
498 RETURN pon_price_element_types_vl%ROWTYPE IS
499
500 l_api_name CONSTANT VARCHAR2(30) := 'get_cost_factor_details';
501 l_stage VARCHAR2(30);
502
503 CURSOR cost_factor_cur (p_cf_code VARCHAR2) IS
504 SELECT *
505 FROM pon_price_element_types_vl
506 WHERE price_element_code = p_cf_code;
507
508 l_cost_factor_type_rec cost_factor_cur%ROWTYPE;
509
510 BEGIN
511
512 l_stage := '10: Execute cursor for Code';
513
514 IF p_price_element_code IS NOT NULL THEN
515 OPEN cost_factor_cur(p_price_element_code);
516 FETCH cost_factor_cur INTO l_cost_factor_type_rec;
517 CLOSE cost_factor_cur;
518 END IF;
519 RETURN l_cost_factor_type_rec;
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 IF cost_factor_cur%ISOPEN THEN
524 CLOSE cost_factor_cur;
525 END IF;
526 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
527 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,SQLERRM);
528 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
529 fnd_log.string(log_level => fnd_log.level_unexpected
530 ,module => g_pkg_name ||'.'||l_api_name
531 ,message => l_stage || ': ' || SQLERRM);
532 fnd_log.string(log_level=>fnd_log.level_unexpected
533 ,module =>g_pkg_name ||'.'||l_api_name
534 ,message => 'Input parameter: ' );
535 fnd_log.string(log_level=>fnd_log.level_unexpected
536 ,module =>g_pkg_name ||'.'||l_api_name
537 ,message => 'Price element code: '||p_price_element_code);
538 END IF;
539 END IF;
540 RETURN l_cost_factor_type_rec;
541 END;
542
543 END PON_CF_TYPE_GRP;