[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_COMMON_UTIL
Source
1 PACKAGE BODY ENG_CHANGE_COMMON_UTIL AS
2 /* $Header: ENGUCMNB.pls 120.3 2006/09/08 12:13:09 ksathupa noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Eng_Change_Common_Util';
5
6
7 -- Get_User_Party_Id
8 /*****************************************************************************
9 * Function : Get_User_Party_Id
10 * Returns : The party id for a user idenitfied by fnd user name
11 * NULL if no matching party is found
12 * Purpose : Convert the user name into a party id.
13 *
14 * Bug No: 4327218
15 * Changing all reference to FND_USER.customer_id as FND_USER.person_party_id
16 * Changing the variable name also to avoid confusion.
17 *****************************************************************************/
18 FUNCTION Get_User_Party_Id
19 ( p_user_name IN VARCHAR2
20 , x_err_text OUT NOCOPY VARCHAR2
21 )
22
23 RETURN NUMBER
24 IS
25 l_employee_id NUMBER;
26 --l_customer_id NUMBER;
27 l_person_party_id NUMBER;
28 l_supplier_id NUMBER;
29 l_user_party_id NUMBER;
30 BEGIN
31
32 SELECT employee_id, person_party_id, supplier_id
33 INTO l_employee_id, l_person_party_id, l_supplier_id
34 FROM fnd_user
35 WHERE user_name = upper(p_user_name);--Bug No 3463516
36 -- Since the internal value of user_name is always upper case
37 -- removed the upper fn from the column, to fix perfoemance bug
38 -- 4950315
39
40 IF l_employee_id IS NULL THEN
41 l_user_party_id := nvl(l_person_party_id, l_supplier_id);
42 ELSE
43 SELECT party_id INTO l_user_party_id
44 FROM per_all_people_f
45 WHERE person_id = l_employee_id and rownum = 1;
46
47 --SELECT party_id INTO l_user_party_id
48 --FROM hz_parties
49 --WHERE person_identifier = l_employee_id;
50 END IF;
51
52 RETURN l_user_party_id;
53
54 EXCEPTION
55
56 WHEN NO_DATA_FOUND THEN
57 x_err_text := NULL;
58 RETURN NULL;
59
60 WHEN OTHERS THEN
61 x_err_text := G_PKG_NAME || ' : (User to Party id conversion) '
62 || substrb(SQLERRM,1,200);
63 RETURN FND_API.G_MISS_NUM;
64
65 END Get_User_Party_Id;
66
67 FUNCTION Get_New_Ref_Designators(p_component_seq_id IN NUMBER)
68 RETURN VARCHAR2
69 IS
70 CURSOR c_new_ref_designators IS
71 SELECT COMPONENT_REFERENCE_DESIGNATOR
72 FROM bom_reference_designators
73 WHERE acd_type = 1
74 AND COMPONENT_SEQUENCE_ID = p_component_seq_id;
75
76 l_new_ref_desig VARCHAR2(4000);
77
78 BEGIN
79
80 FOR cnrd IN c_new_ref_designators
81 LOOP
82 IF(l_new_ref_desig IS NOT NULL)
83 THEN
84 l_new_ref_desig := l_new_ref_desig || ', ';
85 END IF;
86 l_new_ref_desig := l_new_ref_desig || cnrd.COMPONENT_REFERENCE_DESIGNATOR;
87 END LOOP;
88 RETURN l_new_ref_desig;
89 END Get_New_Ref_Designators;
90
91 -- Added for 11.5.10E changes
92 FUNCTION GET_COMP_REVISION_FN(
93 p_organization_id IN NUMBER
94 , p_component_item_id IN NUMBER
95 , p_item_revision_id IN NUMBER)
96 RETURN VARCHAR2
97 IS
98
99 CURSOR c_get_item_revision(cp_revision_id NUMBER) IS
100 SELECT revision
101 FROM mtl_item_revisions
102 WHERE revision_id = cp_revision_id;
103
104 l_revision_id NUMBER;
105 l_revision VARCHAR2(3);
106
107 BEGIN
108
109 l_revision_id := p_item_revision_id;
110 IF l_revision_id IS NULL
111 THEN
112 l_revision_id := BOM_REVISIONS.GET_ITEM_REVISION_ID_FN('ALL', 'IMPL_ONLY', p_organization_id, p_component_item_id, SYSDATE);
113 END IF;
114
115 OPEN c_get_item_revision(l_revision_id);
116 FETCH c_get_item_revision INTO l_revision;
117 CLOSE c_get_item_revision;
118 RETURN l_revision;
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 IF c_get_item_revision%ISOPEN
123 THEN
124 CLOSE c_get_item_revision;
125 END IF;
126 RETURN l_revision;
127
128 END GET_COMP_REVISION_FN;
129
130 PROCEDURE process_attribute_defaulting(p_change_attr_def_tab IN OUT NOCOPY ENG_CHANGE_ATTR_DEFAULT_TABLE
131 ,p_commit IN VARCHAR2
132 ,p_pk_val_name IN VARCHAR2
133 ,p_pk_class_val_name IN VARCHAR2
134 ,x_return_status OUT NOCOPY VARCHAR2
135 ,x_msg_data OUT NOCOPY VARCHAR2
136 ,x_msg_count OUT NOCOPY NUMBER)
137
138 IS
139
140 l_error_code VARCHAR2(2000);
141 l_msg_count NUMBER;
142 l_msg_data VARCHAR2(2000);
143 l_failed_row_id_list VARCHAR2(2000);
144
145
146 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
147 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
148 l_data_level_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
149
150 l_entity_id NUMBER;
151 l_object_name VARCHAR2(30);
152 l_application_id NUMBER;
153 l_change_type_id NUMBER;
154 l_additional_class_Code_list VARCHAR2(32000);
155 l_attribute_group_type VARCHAR2(20);
156 l_record_first NUMBER;
157 l_record_last NUMBER;
158 l_return_status VARCHAR2(10);
159 l_commit VARCHAR2(2);
160 l_attr_groups_to_exclude VARCHAR2(2000);
161
162 CURSOR attr_default_recs IS
163 SELECT A.ENTITY_ID ENTITY_ID
164 ,A.APPLICATION_ID APPLICATION_ID
165 ,A.OBJECT_NAME OBJECT_NAME
166 ,A.ATTRIBUTE_GROUP_TYPE ATTRIBUTE_GROUP_TYPE
167 ,A.CHANGE_TYPE_ID CHANGE_TYPE_ID
168 FROM THE (SELECT CAST(
169 p_change_attr_def_tab AS ENG_CHANGE_ATTR_DEFAULT_TABLE)
170 FROM dual) A
171 ORDER BY ENTITY_ID ;
172
173
174 BEGIN
175 x_return_status := l_return_status;
176 x_msg_count := 0;
177 l_record_first := p_change_attr_def_tab.FIRST;
178 l_record_last := p_change_attr_def_tab.LAST;
179
180 FOR attr_default_rec IN attr_default_recs LOOP
181 l_entity_id := attr_default_rec.ENTITY_ID;
182 l_object_name := attr_default_rec.OBJECT_NAME;
183 l_application_id := attr_default_rec.APPLICATION_ID;
184 l_change_type_id := attr_default_rec.CHANGE_TYPE_ID;
185 l_attribute_group_type := attr_default_rec.ATTRIBUTE_GROUP_TYPE;
186 l_commit := p_commit;
187
188
189
190 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
191 ( EGO_COL_NAME_VALUE_PAIR_OBJ(p_pk_val_name , TO_CHAR(attr_default_rec.ENTITY_ID)));
192 -- get this as param
193 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
194 (EGO_COL_NAME_VALUE_PAIR_OBJ(p_pk_class_val_name, to_char(attr_default_rec.CHANGE_TYPE_ID)));
195
196 l_data_level_values := NULL;
197
198 EGO_USER_ATTRS_DATA_PVT.Apply_Default_Vals_For_Entity
199 ( p_object_name => l_object_name
200 ,p_application_id => l_application_id
201 ,p_attr_group_type => l_attribute_group_type
202 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
203 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
204 ,p_data_level_values => l_data_level_values
205 ,p_init_error_handler => 'T'
206 ,p_init_fnd_msg_list => 'T'
207 ,p_log_errors => 'T'
208 ,p_add_errors_to_fnd_stack => 'T'
209 ,P_commit => l_commit
210 ,x_failed_row_id_list => l_failed_row_id_list
211 ,x_return_status => l_return_status
212 ,x_errorcode => l_error_code
213 ,x_msg_count => l_msg_count
214 ,x_msg_data => l_msg_data
215 );
216
217 x_return_status := l_return_status ;
218 END LOOP;
219 EXCEPTION
220 WHEN OTHERS THEN
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 x_msg_data := SQLERRM;
223 END;
224
225 END ENG_CHANGE_COMMON_UTIL;