[Home] [Help]
PACKAGE BODY: APPS.IGS_OR_PHONES_PKG
Source
1 PACKAGE BODY IGS_OR_PHONES_PKG AS
2 /* $Header: IGSOI25B.pls 115.13 2003/05/06 09:31:26 ssawhney ship $ */
3 /******************************************************************
4 Change History
5
6 Who When What
7 npalanis 14-feb-2002 Bug ID : 2225917 : SWCR008 Obsoleted Get_Level Procedure ,
8 Check_Primary . Removed parameters
9 Customer_Id ,Address_Id,Contact_Id, in Insert_row and Update Row Procedure ,
10 Removed Cust contact point API
11 ssawhney 9-may-2002 Bug 2338473 -- allow for more than one HZ error to appear, where ever direct calls to HZ APIs are present.
12
13 gmuralid 26-NOV-2002 BUG 2466674 -- V2API uptake
14 Changed reference of HZ_CONTACT_POINT_PUB
15 TO HZ_CONTACT_POINT_V2PUB for create
16 and update of contact points
17 gmuralid 27-NOV-02 BUG 2676422 Commented created_by_module := IGS in update call
18 pkpatel 6-JAN-2003 Bug 2730137
19 Removed the hard coding with g_gen
20 ssawhney 30-APR-2003 OVN changes for V2API signature changes
21 ***************************************************************** */
22 g_parties CONSTANT VARCHAR2(15) := 'HZ_PARTIES';
23 g_tlx CONSTANT VARCHAR2(10) := 'TLX';
24 g_phone CONSTANT VARCHAR2(10) := 'PHONE';
25
26
27 FUNCTION get_error_msg(p_msg_cnt IN NUMBER) RETURN VARCHAR2 AS
28 l_count NUMBER(3);
29 l_cnt NUMBER(3);
30 l_message VARCHAR2(2000);
31 l_var VARCHAR2(2000);
32 /******************************************************************
33 Created By: Amit Gairola
34 Date Created By: 01-AUG-2001
35 Purpose: This function returns the message from the message stack
36 and concatenates all the messages
37 Known limitations,enhancements,remarks:
38 Change History
39 Who When What
40 ***************************************************************** */
41 BEGIN
42 FOR l_cnt IN 1..p_msg_cnt LOOP
43 l_var := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
44 l_message := l_message||' '||l_var;
45 END LOOP;
46
47 RETURN (l_message);
48 END get_error_msg;
49
50 PROCEDURE Insert_Row(
51 X_Phone_Id IN OUT NOCOPY NUMBER,
52 X_Last_Update_Date DATE,
53 X_Last_Updated_By NUMBER,
54 X_Creation_Date DATE,
55 X_Created_By NUMBER,
56 X_Phone_Number VARCHAR2,
57 X_Status VARCHAR2,
58 X_Phone_Type VARCHAR2,
59 X_Last_Update_Login NUMBER,
60 X_Country_code VARCHAR2,
61 X_Area_Code VARCHAR2,
62 X_Extension VARCHAR2,
63 X_Primary_Flag VARCHAR2,
64 X_Orig_System_Reference IN OUT NOCOPY VARCHAR2,
65 X_Attribute_Category VARCHAR2,
66 X_Attribute1 VARCHAR2,
67 X_Attribute2 VARCHAR2,
68 X_Attribute3 VARCHAR2,
69 X_Attribute4 VARCHAR2,
70 X_Attribute5 VARCHAR2,
71 X_Attribute6 VARCHAR2,
72 X_Attribute7 VARCHAR2,
73 X_Attribute8 VARCHAR2,
74 X_Attribute9 VARCHAR2,
75 X_Attribute10 VARCHAR2,
76 X_Attribute11 VARCHAR2,
77 X_Attribute12 VARCHAR2,
78 X_Attribute13 VARCHAR2,
79 X_Attribute14 VARCHAR2,
80 X_Attribute15 VARCHAR2,
81 X_Attribute16 VARCHAR2,
82 X_Attribute17 VARCHAR2,
83 X_Attribute18 VARCHAR2,
84 X_Attribute19 VARCHAR2,
85 X_Attribute20 VARCHAR2,
86 x_party_id NUMBER,
87 x_party_site_id NUMBER,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_contact_point_ovn IN OUT NOCOPY NUMBER) AS
92 /******************************************************************
93
94 Created By: Amit Gairola
95
96 Date Created By: 01-AUG-2001
97
98 Purpose: This procedure Inserts the record for the Contact Phones
99
100 Known limitations,enhancements,remarks:
101
102 Change History
103
104 Who When What
105
106 ***************************************************************** */
107 email_rec HZ_CONTACT_POINT_V2PUB.email_rec_type;
108 edi_rec HZ_CONTACT_POINT_V2PUB.edi_rec_type;
109 phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type;
110 telex_rec HZ_CONTACT_POINT_V2PUB.telex_rec_type;
111 web_rec HZ_CONTACT_POINT_V2PUB.web_rec_type;
112 cpoint_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;
113 x_owner_table VARCHAR2(30);
114 x_owner_table_id NUMBER;
115
116 tmp_var VARCHAR2(2000);
117 tmp_var1 VARCHAR2(2000);
118 BEGIN
119
120 x_owner_table := g_parties;
121 x_owner_table_id := x_party_id;
122
123 -- Get the value for the x_phone_id from the Sequence
124 SELECT hz_contact_points_s.NEXTVAL
125 INTO x_phone_id
126 FROM dual;
127
128 IF (x_orig_system_reference IS NULL) THEN
129 x_orig_system_reference := x_phone_id;
130 END IF;
131
132 -- Assign values to the Contacts record based
133 -- on the inputs provided to the procedure
134 cpoint_rec.contact_point_id := x_phone_Id;
135 cpoint_rec.contact_point_type := X_Phone_Type;
136 cpoint_rec.status := x_status;
137 cpoint_rec.owner_table_name := x_owner_table;
138 cpoint_rec.owner_table_id := x_owner_table_id;
139 cpoint_rec.primary_flag := x_primary_flag;
140 cpoint_rec.orig_system_reference := x_orig_system_reference;
141 cpoint_rec.attribute_category := x_attribute_category;
142 cpoint_rec.attribute1 := x_attribute1;
143 cpoint_rec.attribute2 := x_attribute2;
144 cpoint_rec.attribute3 := x_attribute3;
145 cpoint_rec.attribute4 := x_attribute4;
146 cpoint_rec.attribute5 := x_attribute5;
147 cpoint_rec.attribute6 := x_attribute6;
148 cpoint_rec.attribute7 := x_attribute7;
149 cpoint_rec.attribute8 := x_attribute8;
150 cpoint_rec.attribute9 := x_attribute9;
151 cpoint_rec.attribute10 := x_attribute10;
152 cpoint_rec.attribute11 := x_attribute11;
153 cpoint_rec.attribute12 := x_attribute12;
154 cpoint_rec.attribute13 := x_attribute13;
155 cpoint_rec.attribute14 := x_attribute14;
156 cpoint_rec.attribute15 := x_attribute15;
157 cpoint_rec.attribute16 := x_attribute16;
158 cpoint_rec.attribute17 := x_attribute17;
159 cpoint_rec.attribute18 := x_attribute18;
160 cpoint_rec.attribute19 := x_attribute19;
161 cpoint_rec.attribute20 := x_attribute20;
162 cpoint_rec.created_by_module := 'IGS';
163 cpoint_rec.content_source_type := 'USER_ENTERED';
164
165
166 IF x_phone_type = g_tlx THEN
167 telex_rec.telex_number := x_phone_number;
168 END IF;
169
170 IF x_phone_type = g_tlx AND
171 x_area_code IS NOT NULL THEN
172 telex_rec.telex_number := x_area_code ||'-'||x_phone_number;
173 END IF;
174 IF x_phone_type NOT IN ( g_tlx) THEN
175 phone_rec.phone_line_type := x_phone_type;
176 cpoint_rec.contact_point_type := g_phone;
177 phone_rec.phone_number := x_phone_number;
178 phone_rec.phone_country_code := x_country_code;
179 phone_rec.phone_area_code := x_area_code;
180 phone_rec.phone_extension := x_extension;
181 END IF;
182
183 -- call the API for the creation of the Contact Points
184 HZ_CONTACT_POINT_V2PUB.create_contact_point(
185 p_init_msg_list => FND_API.G_FALSE,
186 p_contact_point_rec => cpoint_rec,
187 p_edi_rec => edi_rec,
188 p_email_rec => email_rec,
189 p_phone_rec => phone_rec,
190 p_telex_rec => telex_rec,
191 p_web_rec => web_rec,
192 x_return_status => x_return_status,
193 x_msg_count => x_msg_count,
194 x_msg_data => x_msg_data,
195 x_contact_point_id => x_phone_id);
196
197
198 IF x_return_status <> 'S' THEN
199 -- ssawhney bug 2338473
200 IF x_msg_count > 1 THEN
201 FOR i IN 1..x_msg_count LOOP
202 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
203 tmp_var1 := tmp_var1 || ' '|| tmp_var;
204 END LOOP;
205 x_msg_data := tmp_var1;
206 END IF;
207 RETURN;
208 END IF;
209
210 -- everything successful
211 x_contact_point_ovn := 1;
212
213 END insert_row;
214
215
216 PROCEDURE Update_Row(
217 X_phone_id NUMBER,
218 X_Last_Update_Date IN OUT NOCOPY DATE,
219 X_Last_Updated_By NUMBER,
220 X_Phone_Number VARCHAR2,
221 X_Status VARCHAR2,
222 X_Phone_Type VARCHAR2,
223 X_Last_Update_Login NUMBER,
224 X_country_code VARCHAR2,
225 X_Area_Code VARCHAR2,
226 X_Extension VARCHAR2,
227 X_Primary_Flag VARCHAR2,
228 X_Attribute_Category VARCHAR2,
229 X_Attribute1 VARCHAR2,
230 X_Attribute2 VARCHAR2,
231 X_Attribute3 VARCHAR2,
232 X_Attribute4 VARCHAR2,
233 X_Attribute5 VARCHAR2,
234 X_Attribute6 VARCHAR2,
235 X_Attribute7 VARCHAR2,
236 X_Attribute8 VARCHAR2,
237 X_Attribute9 VARCHAR2,
238 X_Attribute10 VARCHAR2,
239 X_Attribute11 VARCHAR2,
240 X_Attribute12 VARCHAR2,
241 X_Attribute13 VARCHAR2,
242 X_Attribute14 VARCHAR2,
243 X_Attribute15 VARCHAR2,
244 X_Attribute16 VARCHAR2,
245 X_Attribute17 VARCHAR2,
246 X_Attribute18 VARCHAR2,
247 X_Attribute19 VARCHAR2,
248 X_Attribute20 VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_contact_point_ovn IN OUT NOCOPY NUMBER) AS
253 /******************************************************************
254
255 Created By: Amit Gairola
256
257 Date Created By: 01-AUG-2001
258
259 Purpose: This procedure updates the Phones Record
260
261 Known limitations,enhancements,remarks:
262
263 Change History
264
265 Who When What
266
267 ***************************************************************** */
268
269 email_rec HZ_CONTACT_POINT_V2PUB.email_rec_type;
270 edi_rec HZ_CONTACT_POINT_V2PUB.edi_rec_type;
271 phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type;
272 telex_rec HZ_CONTACT_POINT_V2PUB.telex_rec_type;
273 web_rec HZ_CONTACT_POINT_V2PUB.web_rec_type;
274 cpoint_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;
275 l_obj_ver hz_contact_points.object_version_number%TYPE;
276
277 tmp_var VARCHAR2(2000);
278 tmp_var1 VARCHAR2(2000);
279
280
281
282 BEGIN
283 -- Assign the values from the input to the Contact Points record
284 cpoint_rec.contact_point_id := x_phone_Id;
285 cpoint_rec.contact_point_type := X_Phone_Type;
286 cpoint_rec.status := x_status;
287 cpoint_rec.primary_flag := NVL(x_primary_flag,FND_API.G_MISS_CHAR);
288 cpoint_rec.attribute_category := NVL(x_attribute_category,FND_API.G_MISS_CHAR);
289 cpoint_rec.attribute1 := NVL(x_attribute1,FND_API.G_MISS_CHAR);
290 cpoint_rec.attribute2 := NVL(x_attribute2,FND_API.G_MISS_CHAR);
291 cpoint_rec.attribute3 := NVL(x_attribute3,FND_API.G_MISS_CHAR);
292 cpoint_rec.attribute4 := NVL(x_attribute4,FND_API.G_MISS_CHAR);
293 cpoint_rec.attribute5 := NVL(x_attribute5,FND_API.G_MISS_CHAR);
294 cpoint_rec.attribute6 := NVL(x_attribute6,FND_API.G_MISS_CHAR);
295 cpoint_rec.attribute7 := NVL(x_attribute7,FND_API.G_MISS_CHAR);
296 cpoint_rec.attribute8 := NVL(x_attribute8,FND_API.G_MISS_CHAR);
297 cpoint_rec.attribute9 := NVL(x_attribute9,FND_API.G_MISS_CHAR);
298 cpoint_rec.attribute10 := NVL(x_attribute10,FND_API.G_MISS_CHAR);
299 cpoint_rec.attribute11 := NVL(x_attribute11,FND_API.G_MISS_CHAR);
300 cpoint_rec.attribute12 := NVL(x_attribute12,FND_API.G_MISS_CHAR);
301 cpoint_rec.attribute13 := NVL(x_attribute13,FND_API.G_MISS_CHAR);
302 cpoint_rec.attribute14 := NVL(x_attribute14,FND_API.G_MISS_CHAR);
303 cpoint_rec.attribute15 := NVL(x_attribute15,FND_API.G_MISS_CHAR);
304 cpoint_rec.attribute16 := NVL(x_attribute16,FND_API.G_MISS_CHAR);
305 cpoint_rec.attribute17 := NVL(x_attribute17,FND_API.G_MISS_CHAR);
306 cpoint_rec.attribute18 := NVL(x_attribute18,FND_API.G_MISS_CHAR);
307 cpoint_rec.attribute19 := NVL(x_attribute19,FND_API.G_MISS_CHAR);
308 cpoint_rec.attribute20 := NVL(x_attribute20,FND_API.G_MISS_CHAR);
309 -- cpoint_rec.created_by_module := 'IGS';
310 --cpoint_rec.content_source_type := 'USER_ENTERED ';
311
312 IF x_phone_type = g_tlx THEN
313 telex_rec.telex_number := x_phone_number;
314 END IF;
315
316 IF x_phone_type = g_tlx AND
317 x_area_code IS NOT NULL THEN
318 telex_rec.telex_number := x_area_code ||'-'||x_phone_number;
319 END IF;
320 IF x_phone_type NOT IN ( g_tlx) THEN
321 phone_rec.phone_line_type := NVL(x_phone_type,FND_API.G_MISS_CHAR);
322 cpoint_rec.contact_point_type := g_phone;
323 phone_rec.phone_number := NVL(x_phone_number,FND_API.G_MISS_CHAR);
324 phone_rec.phone_country_code := NVL(x_country_code,FND_API.G_MISS_CHAR);
325 phone_rec.phone_area_code := NVL(x_area_code,FND_API.G_MISS_CHAR);
326 phone_rec.phone_extension := NVL(x_extension,FND_API.G_MISS_CHAR);
327 END IF;
328
329 -- Call the API for the updation of the Contact Points
330
331
332
333 HZ_CONTACT_POINT_V2PUB.update_contact_point(
334 p_init_msg_list => FND_API.G_FALSE,
335 p_contact_point_rec => cpoint_rec,
336 p_edi_rec => edi_rec,
337 p_email_rec => email_rec ,
338 p_phone_rec => phone_rec,
339 p_telex_rec => telex_rec,
340 p_web_rec => web_rec,
341 p_object_version_number => x_contact_point_ovn,
342 x_return_status => x_return_status,
343 x_msg_count => x_msg_count,
344 x_msg_data => x_msg_data
345 );
346
347 IF x_return_status <> 'S' THEN
348 IF x_msg_count > 1 THEN
349 FOR i IN 1..x_msg_count LOOP
350 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
351 tmp_var1 := tmp_var1 || ' '|| tmp_var;
352 END LOOP;
353 x_msg_data := tmp_var1;
354 END IF;
355 RETURN;
356 END IF;
357
358 END update_row;
359
360
361 PROCEDURE Delete_Row(X_phoneid VARCHAR2) AS
362 /******************************************************************
363
364 Created By: Amit Gairola
365
366 Date Created By: 01-AUG-2001
367
368 Purpose: This procedure deletes the record for the Phone Id passed
369 as Input
370
371 Known limitations,enhancements,remarks:
372
373 Change History
374
375 Who When What
376 ssawhney made this NULL. Can not delete from HZ table
377 ***************************************************************** */
378 BEGIN
379 Null;
380 END delete_row;
381
382 END igs_or_phones_pkg;