[Home] [Help]
PACKAGE BODY: APPS.HXC_ALIAS_DEFN_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_alias_defn_upload_pkg AS
2 /* $Header: hxcloddef.pkb 115.1 2002/09/03 12:09:27 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_alias_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_alias_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
41 PROCEDURE load_alias_definition_row (
42 p_alias_definition_name IN VARCHAR2
43 , p_owner IN VARCHAR2
44 , p_legislation_code IN VARCHAR2
45 , p_alias_context_code IN VARCHAR2
46 , p_description IN VARCHAR2
47 , p_timecard_field IN VARCHAR2
48 , p_prompt IN VARCHAR2
49 , p_alias_type IN VARCHAR2
50 , p_reference_object IN VARCHAR2
51 , p_custom_mode IN VARCHAR2 ) IS
52
53 l_alias_type_id hxc_alias_types.alias_type_id%TYPE;
54 l_alias_reference_object hxc_alias_types.reference_object%TYPE;
55 l_alias_definition_id hxc_alias_definitions.alias_definition_id%TYPE;
56 l_business_group_id hxc_alias_definitions.business_group_id%TYPE;
57 l_legislation_code hxc_alias_definitions.legislation_code%TYPE;
58 l_alias_context_code hxc_alias_definitions.alias_context_code%TYPE;
59 l_description hxc_alias_definitions.description%TYPE;
60 l_alias_definition_name hxc_alias_definitions.alias_definition_name%TYPE;
61 l_timecard_field hxc_alias_definitions.timecard_field%TYPE;
62 l_ovn hxc_alias_definitions.object_version_number%TYPE;
63 l_owner VARCHAR2(6);
64
65 BEGIN
66 l_alias_reference_object:= find_alias_reference_object(p_alias_type, p_reference_object);
67 l_alias_type_id := find_alias_type_id(p_alias_type, l_alias_reference_object);
68
69
70 BEGIN
71 SELECT alias_definition_id
72 , DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
73 , business_group_id
74 , legislation_code
75 , alias_context_code
76 , description
77 , alias_definition_name
78 , timecard_field
79 , object_version_number
80
81 INTO l_alias_definition_id
82 ,l_owner
83 ,l_business_group_id
84 ,l_legislation_code
85 ,l_alias_context_code
86 ,l_description
87 ,l_alias_definition_name
88 ,l_timecard_field
89 ,l_ovn
90
91 FROM hxc_alias_definitions had
92 WHERE had.alias_definition_name = p_alias_definition_name
93 and had.ALIAS_TYPE_ID = l_alias_type_id;
94
95 IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
96 THEN
97 -- only update if the alias component mapping that has actually changed
98
99
100 hxc_had_upd.upd
101 (p_alias_definition_id => l_alias_definition_id
102 ,p_object_version_number => l_ovn
103 ,p_alias_definition_name => p_alias_definition_name
104 ,p_alias_context_code => p_alias_context_code
105 ,p_business_group_id => null
106 ,p_legislation_code => p_legislation_code
107 ,p_timecard_field => p_timecard_field
108 ,p_description => p_description
109 ,p_prompt => p_prompt
110 ,p_alias_type_id => l_alias_type_id
114 EXCEPTION WHEN NO_DATA_FOUND
111 );
112
113 END IF;
115 THEN
116 hxc_had_ins.ins
117 (p_alias_definition_name => p_alias_definition_name
118 ,p_alias_context_code => p_alias_context_code
119 ,p_business_group_id => null
120 ,p_legislation_code => p_legislation_code
121 ,p_timecard_field => p_timecard_field
122 ,p_description => p_description
123 ,p_prompt => p_prompt
124 ,p_alias_definition_id => l_alias_definition_id
125 ,p_object_version_number => l_ovn
126 ,p_alias_type_id => l_alias_type_id
127 );
128 END;
129 END load_alias_definition_row;
130 END hxc_alias_defn_upload_pkg;