[Home] [Help]
PACKAGE BODY: APPS.HXC_ALIAS_TYPES_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_alias_types_upload_pkg AS
2 /* $Header: hxcaltload.pkb 115.1 2002/09/03 12:10:15 ksethi noship $ */
3
4 FUNCTION find_alias_reference_object ( p_alias_type VARCHAR2,p_reference_object VARCHAR2 ) RETURN VARCHAR2 IS
5
6 CURSOR csr_get_alias_reference_object IS
7 (select ff.descriptive_flex_context_code
8 from fnd_descr_flex_contexts_vl ff
9 where ff.DESCRIPTIVE_FLEX_CONTEXT_NAME = p_reference_object
10 and p_alias_type = 'OTL_ALT_DDF'
11 union
12 select to_char(ff.FLEX_VALUE_SET_ID)
13 from fnd_flex_value_sets ff
14 where ff.FLEX_VALUE_SET_NAME = p_reference_object
15 and p_alias_type in ('VALUE_SET_NONE','VALUE_SET_TABLE')
16 );
17 l_alias_reference_object hxc_alias_types.reference_object%TYPE;
18 BEGIN
19 OPEN csr_get_alias_reference_object;
20 FETCH csr_get_alias_reference_object INTO l_alias_reference_object;
21 CLOSE csr_get_alias_reference_object;
22 RETURN l_alias_reference_object;
23 END find_alias_reference_object;
24
25
26 FUNCTION find_alias_type_id ( p_alias_type VARCHAR2,p_reference_object VARCHAR2 ) RETURN number IS
27 CURSOR csr_get_alias_type_id IS
28 SELECT alias_type_id
29 FROM hxc_alias_types
30 WHERE alias_type = p_alias_type
31 and reference_object = p_reference_object;
32 l_alias_type_id hxc_alias_types.alias_type_id%TYPE;
33 BEGIN
34 OPEN csr_get_alias_type_id;
35 FETCH csr_get_alias_type_id INTO l_alias_type_id;
36 CLOSE csr_get_alias_type_id;
37 RETURN l_alias_type_id;
38 END find_alias_type_id;
39
40 FUNCTION find_mapping_component_id (p_mapping_component_name VARCHAR2) RETURN number IS
41 CURSOR csr_get_mapping_component_id IS
42 SELECT mapping_component_id
43 FROM hxc_mapping_components
44 WHERE name = p_mapping_component_name;
45 l_mapping_component_id hxc_mapping_components.mapping_component_id%TYPE;
46 BEGIN
47 OPEN csr_get_mapping_component_id;
48 FETCH csr_get_mapping_component_id INTO l_mapping_component_id;
49 CLOSE csr_get_mapping_component_id;
50 RETURN l_mapping_component_id;
51 END find_mapping_component_id;
52
53 PROCEDURE load_alias_type_row (
54 p_alias_type IN VARCHAR2
55 , p_reference_object IN VARCHAR2
56 , p_owner IN VARCHAR2
57 , p_custom_mode IN VARCHAR2 ) IS
58 l_alias_type_id hxc_alias_types.alias_type_id%TYPE;
59 l_alias_type hxc_alias_types.alias_type%TYPE;
60 l_reference_object hxc_alias_types.reference_object%TYPE;
61 l_ovn hxc_alias_types.object_version_number%TYPE;
62 l_owner VARCHAR2(6);
63 l_alias_reference_object hxc_alias_types.reference_object%TYPE;
64 BEGIN
65 l_alias_reference_object:= find_alias_reference_object(p_alias_type, p_reference_object);
66 BEGIN
67 SELECT alias_type_id
68 , alias_type
69 , reference_object
70 , object_version_number
71 , DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
72 INTO l_alias_type_id
73 , l_alias_type
74 , l_reference_object
75 , l_ovn
76 , l_owner
77 FROM hxc_alias_types
78 WHERE alias_type = P_alias_type
79 and reference_object= l_alias_reference_object;
80 IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
81 THEN
82 -- only update if the alias type has actually changed
83 IF ( ( p_alias_type <> l_alias_type ) OR ( l_alias_reference_object <> l_reference_object)
84 ) then
85 hxc_hat_upd.upd (
86 p_alias_type => p_alias_type,
87 p_reference_object => l_alias_reference_object,
88 p_alias_type_id => l_alias_type_id,
89 p_object_version_number => l_ovn
90 );
91 END IF;
92 END IF;
93 EXCEPTION WHEN NO_DATA_FOUND
94 THEN
95 hxc_hat_ins.ins (
96 p_alias_type => p_alias_type,
97 p_reference_object => l_alias_reference_object,
98 p_alias_type_id => l_alias_type_id,
99 p_object_version_number => l_ovn
100 );
101 END;
102 END load_alias_type_row;
103
104 PROCEDURE load_alias_comp_row (
105 p_component_name IN VARCHAR2
106 , p_component_type IN VARCHAR2
107 , p_mapping_component_name IN VARCHAR2
108 , p_alias_type IN VARCHAR2
109 , p_reference_object IN VARCHAR2
110 , p_owner IN VARCHAR2
111 , p_custom_mode IN VARCHAR2 ) IS
112 l_alias_type_id hxc_alias_types.alias_type_id%TYPE;
113 l_mapping_component_id hxc_mapping_components.mapping_component_id%TYPE;
114 l_alias_type_component_id hxc_alias_type_components.alias_type_component_id%TYPE;
115 l_component_name hxc_alias_type_components.component_name%TYPE;
116 l_component_type hxc_alias_type_components.component_type%TYPE;
117 l_ovn hxc_alias_type_components.object_version_number%TYPE;
118 l_owner VARCHAR2(6);
119 l_alias_reference_object hxc_alias_types.reference_object%TYPE;
120
121 BEGIN
122 l_alias_reference_object:= find_alias_reference_object(p_alias_type, p_reference_object);
123 l_alias_type_id := find_alias_type_id (p_alias_type, l_alias_reference_object);
124 l_mapping_component_id := find_mapping_component_id (p_mapping_component_name);
125 BEGIN
126 SELECT alias_type_component_id
127 , object_version_number
128 , DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
129 , component_name
130 , component_type
131 INTO l_alias_type_component_id
132 , l_ovn
133 , l_owner
134 , l_component_name
135 , l_component_type
136 FROM hxc_alias_type_components hatc
137 WHERE hatc.mapping_component_id = l_mapping_component_id
138 AND hatc.ALIAS_TYPE_ID = l_alias_type_id;
139 IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
140 THEN
141 -- only update if the alias component mapping that has actually changed
142 -- IF ( ( p_component_name <> l_component_name ) OR ( p_component_type <> l_component_type)
143 -- )
144 -- THEN
145 hxc_atc_upd.upd (
146 p_component_name => p_component_name
147 ,p_component_type => p_component_type
148 ,p_mapping_component_id => l_mapping_component_id
149 ,p_alias_type_id => l_alias_type_id
150 ,p_alias_type_component_id => l_alias_type_component_id
151 ,p_object_version_number => l_ovn
152 );
153 -- END IF;
154 END IF;
155 EXCEPTION WHEN NO_DATA_FOUND
156 THEN
157 hxc_atc_ins.ins (
158 p_component_name => p_component_name
159 ,p_component_type => p_component_type
160 ,p_mapping_component_id => l_mapping_component_id
161 ,p_alias_type_id => l_alias_type_id
162 ,p_alias_type_component_id => l_alias_type_component_id
163 ,p_object_version_number => l_ovn
164 );
165 END;
166 END load_alias_comp_row;
167 END hxc_alias_types_upload_pkg;