[Home] [Help]
PACKAGE BODY: APPS.EDW_GL_CONSOLIDATION
Source
1 Package Body EDW_GL_CONSOLIDATION AS
2 /* $Header: FIIECONB.pls 120.2 2005/08/30 15:05:22 sgautam noship $ */
3
4 procedure edw_get_cons_flex_value (
5 p_coa_mapping_id IN gl_cons_segment_map.coa_mapping_id%TYPE,
6 p_cons_from_flex_set_id IN fnd_flex_values.FLEX_VALUE_SET_ID%TYPE ,
7 p_cons_to_flex_set_id IN fnd_flex_values.FLEX_VALUE_SET_ID%TYPE ,
8 p_cons_from_flex_value IN fnd_flex_values.FLEX_VALUE%TYPE ,
9 p_parent_flag IN varchar2,
10 p_cons_to_flex_value OUT NOCOPY /* file.sql.39 change */ fnd_flex_values.FLEX_VALUE%TYPE ,
11 p_return_msg OUT NOCOPY /* file.sql.39 change */ varchar2,
12 p_status OUT NOCOPY /* file.sql.39 change */ boolean
13 ) IS
14
15 l_map_type gl_cons_segment_map.segment_map_type%TYPE;
16 l_proc_name varchar2(30) :='EDW_GET_CONS_FLEX_VALUE';
17
18 diamond_problem exception;
19 no_return_value exception;
20
21 CURSOR csr_get_cons_to_value (
22 p_from_value_set_id in number,
23 p_to_value_set_id in number,
24 p_coa_mapping_id in number,
25 p_from_value in varchar2
26 ) IS
27 SELECT cfh.parent_flex_value parent,
28 csm.segment_map_type segment_map_type
29 FROM gl_cons_segment_map csm,
30 gl_cons_flex_hierarchies cfh
31 WHERE cfh.segment_map_id = csm.segment_map_id
32 AND csm.segment_map_type = 'R'
33 AND csm.from_value_set_id = p_from_value_set_id
34 AND csm.to_value_set_id = p_to_value_set_id
35 AND csm.coa_mapping_id = p_coa_mapping_id
36 AND p_from_value between cfh.child_flex_value_low
37 and cfh.child_flex_value_high
38 UNION ALL
39 SELECT csm.single_value parent,
40 csm.segment_map_type segment_map_type
41 FROM gl_cons_segment_map csm
42 WHERE csm.segment_map_type = 'P'
43 AND csm.from_value_set_id = p_from_value_set_id
44 AND csm.to_value_set_id = p_to_value_set_id
45 AND csm.coa_mapping_id = p_coa_mapping_id
46 AND p_from_value = csm.parent_rollup_value
47 UNION ALL
48 SELECT csm.single_value parent,
49 csm.segment_map_type segment_map_type
50 FROM gl_cons_segment_map csm
51 WHERE csm.segment_map_type = 'C'
52 AND csm.from_value_set_id = p_from_value_set_id
53 AND csm.to_value_set_id = p_to_value_set_id
54 AND csm.coa_mapping_id = p_coa_mapping_id
55 UNION ALL
56 SELECT csm.single_value parent,
57 csm.segment_map_type segment_map_type
58 FROM gl_cons_segment_map csm
59 WHERE csm.segment_map_type = 'S'
60 AND csm.to_value_set_id = p_to_value_set_id
61 AND csm.coa_mapping_id = p_coa_mapping_id;
62
63 BEGIN
64
65 open csr_get_cons_to_value(
66 p_cons_from_flex_set_id,
67 p_cons_to_flex_set_id,
68 p_coa_mapping_id,
69 p_cons_from_flex_value
70 );
71
72 fetch csr_get_cons_to_value
73 into p_cons_to_flex_value, l_map_type;
74
75 close csr_get_cons_to_value;
76
77 -- Throw exception to avoid 'diamond problem' for map type 'C'
78
79 if (l_map_type = 'C') then
80
81 if (p_parent_flag = 'Y') then
82 raise diamond_problem;
83 else
84 p_cons_to_flex_value := p_cons_from_flex_value;
85 end if;
86
87 end if;
88
89 -- Throw exception if no return value
90
91 if ( p_cons_to_flex_value is null ) then
92 raise no_return_value;
93 end if;
94
95 p_return_msg := null;
96 p_status := true;
97
98 EXCEPTION
99
100 WHEN diamond_problem THEN
101
102 p_cons_to_flex_value := null;
103
104 p_return_msg := l_proc_name || ' - not mapped due to diamond problem';
105
106 p_status := false;
107
108 WHEN no_return_value THEN
109
110 p_cons_to_flex_value := null;
111
112 p_return_msg := l_proc_name || ' - no mapping found in API';
113
114 p_status := false;
115
116 WHEN OTHERS THEN
117
118 p_cons_to_flex_value := NULL;
119
120 p_return_msg := substrb(l_proc_name || ' Unexpected Error '
121 || sqlerrm, 1, 240);
122
123 p_status := false;
124
125 END EDW_GET_CONS_FLEX_VALUE;
126
127 END EDW_GL_CONSOLIDATION ;