[Home] [Help]
PACKAGE BODY: APPS.OTA_RA_CON_API
Source
1 PACKAGE BODY OTA_RA_CON_API as
2 /* $Header: otcon01t.pkb 120.2.12000000.2 2007/05/02 04:57:00 aabalakr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ra_con_api.'; -- Global package name
9 --
10 -- |--------------------------------------------------------------------------|
11 -- |-------------------------< insert_contact >-------------------------------|
12 -- |--------------------------------------------------------------------------|
13 --
14 -- PUBLIC
15 -- Inserts a Contact using name and customer ID
16 -- Currently only used by Delegate Bookings
17 --
18 procedure insert_contact (p_contact_id out nocopy number,
19 p_customer_id in number,
20 p_last_name in varchar2,
21 p_first_name in varchar2,
22 p_title in varchar2,
23 p_administrator in number) is
24 --
25 l_proc varchar2(72) := g_package||'insert_contact';
26 --
27
28 i_rel_party_id NUMBER;
29 i_return_status VARCHAR2(1);
30 i_msg_count NUMBER;
31 i_msg_data VARCHAR2(2000);
32
33 X_Cust_Account_Role_Id NUMBER;
34 X_Contact_Number VARCHAR2(20);
35 X_Orig_System_Reference VARCHAR2(20);
36 X_Contact_Party_Id NUMBER;
37 X_Org_Contact_Id NUMBER;
38
39 begin
40 --
41 hr_utility.set_location('Entering:'||l_proc, 5);
42 X_Cust_Account_Role_Id := NULL;
43 X_Contact_Number := NULL;
44 X_Orig_System_Reference:= NULL;
45 X_Contact_Party_Id := NULL;
46 X_Org_Contact_Id := NULL;
47 --
48 Insert_Row(
49 X_Contact_Id => P_Contact_Id,
50 X_Created_By => p_administrator,
51 X_Customer_Id => P_Customer_Id,
52 X_Last_Name => P_Last_Name,
53 X_Last_Updated_By => p_administrator,
54 X_Orig_System_Reference => X_Orig_System_Reference,
55 X_First_Name => P_First_Name,
56 X_Title => P_Title,
57 X_Contact_Number => X_Contact_Number,
58 X_Contact_Party_Id => X_Contact_Party_Id,
59 X_Rel_Party_Id => i_rel_party_id,
60 X_Org_Contact_Id => X_Org_Contact_Id,
61 X_Cust_Account_Role_Id => X_Cust_Account_Role_Id,
62 X_Return_Status => i_Return_Status,
63 X_Msg_Count => i_Msg_Count,
64 X_Msg_Data => i_Msg_Data
65 ) ;
66
67 --
68 p_contact_id := x_cust_account_role_id;
69 --
70 hr_utility.set_location(' Leaving:'||l_proc, 10);
71 end insert_contact;
72
73 -- |--------------------------------------------------------------------------|
74 -- |-------------------------< update_contact >-------------------------------|
75 -- |--------------------------------------------------------------------------|
76 --
77 -- PUBLIC
78 -- Updates a given Contact in RA_CONTACTS
79 -- Currently only used by Delegate Bookings
80 --
81 procedure update_contact (p_contact_id in number,
82 p_last_name in varchar2,
83 p_first_name in varchar2,
84 p_title in varchar2) is
85 --
86 l_proc varchar2(72) := g_package||'update_contact';
87 x_profile_id NUMBER;
88 x_return_status VARCHAR2(1);
89 x_msg_count NUMBER;
90 x_msg_data VARCHAR2(2000);
91 tmp_var VARCHAR2(2000);
92 tmp_var1 VARCHAR2(2000);
93 l_temp VARCHAR2(30);
94 x_party_id HZ_PARTIES.PARTY_ID%type;
95 x_party_object_version_number NUMBER;
96 l_sql_stat VARCHAR2(4000);
97 --
98
99 begin
100 --
101 hr_utility.set_location('Entering:'||l_proc, 5);
102 --
103 -- arkashya Bug #2652833: Changed update on ra_contacts to be based on HZ_ tables directly
104 /*
105 update HZ_PARTIES
106 set
107 PERSON_LAST_NAME = substrb( p_last_name,1,50),
108 PERSON_FIRST_NAME = substrb(p_first_name,1,40),
109 PERSON_PRE_NAME_ADJUNCT = p_title
110 where
111 PARTY_ID = (select PARTY.PARTY_ID
112 from HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
113 HZ_PARTIES PARTY,
114 HZ_RELATIONSHIPS REL,
115 HZ_ORG_CONTACTS ORG_CONT,
116 HZ_CUST_ACCOUNTS ROLE_ACCT
117 where ACCT_ROLE.PARTY_ID = REL.PARTY_ID
118 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
119 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
120 AND REL.SUBJECT_ID = PARTY.PARTY_ID
121 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
122 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
123 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
124 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
125 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id );
126 */
127 SELECT party.party_id, party.object_version_number
128 INTO x_party_id, x_party_object_version_number
129 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
130 HZ_PARTIES PARTY,
131 HZ_RELATIONSHIPS REL,
132 HZ_ORG_CONTACTS ORG_CONT,
133 HZ_CUST_ACCOUNTS ROLE_ACCT
134 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
135 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
136 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
137 AND REL.SUBJECT_ID = PARTY.PARTY_ID
138 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
139 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
140 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
141 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
142 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id;
143
144 -- Update person party using HZ_PARTY_V2PUB V2 APIs
145
146 l_sql_stat := ' Declare
147 per_rec HZ_PARTY_V2PUB.person_rec_type;
148 party_rec HZ_PARTY_V2PUB.party_rec_type;
149 Begin
150 per_rec.party_rec.party_id := :x_party_id;
151 per_rec.party_rec.status := ''A'';
152 per_rec.person_first_name := :p_first_name;
153 per_rec.person_last_name := :p_last_name;
154 per_rec.person_title := :p_title;
155
156 HZ_PARTY_V2PUB.update_person(
157 null,
158 per_rec,
159 :x_party_object_version_number,
160 :x_profile_id,
161 :x_return_status,
162 :x_msg_count,
163 :x_msg_data
164 );
165 End;';
166
167 EXECUTE IMMEDIATE l_sql_stat
168 USING IN x_party_id
169 , IN p_first_name
170 , IN p_last_name
171 , IN p_title
172 , IN OUT x_party_object_version_number
173 , OUT x_profile_id
174 , OUT x_return_status
175 , OUT x_msg_count
176 , OUT x_msg_data;
177
178
179 --dbms_output.put_line('x_contact_party_id--'||x_contact_party_id);
180 IF x_msg_count > 1 THEN
181 FOR i IN 1..x_msg_count LOOP
182 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
183 tmp_var1 := tmp_var1 || ' '|| tmp_var;
184 END LOOP;
185 x_msg_data := tmp_var1;
186 END IF;
187
188
189 --dbms_output.put_line('x_return_status--'||x_return_status);
190 IF x_return_status <> 'S' THEN
191 RETURN;
192 END IF;
193
194
195
196 hr_utility.set_location(' Leaving:'||l_proc, 10);
197 end;
198 --
199
200 -- |--------------------------------------------------------------------------|
201 -- |-------------------------< Insert_Row >-----------------------------------|
202 -- |--------------------------------------------------------------------------|
203 --
204 -- PUBLIC
205 -- hdshah Bug#1729321 Insert_Row procedure included to create contacts using TCA apis.
206 PROCEDURE Insert_Row(
207 X_Contact_Id IN OUT NOCOPY NUMBER,
208 X_Created_By NUMBER,
209 X_Creation_Date DATE ,
210 X_Customer_Id NUMBER,
211 X_Last_Name VARCHAR2,
212 X_Last_Updated_By NUMBER,
213 X_Last_Update_Date DATE ,
214 X_Orig_System_Reference IN OUT NOCOPY VARCHAR2 ,
215 X_Status VARCHAR2 ,
216 X_Address_Id NUMBER ,
217 X_Contact_Key VARCHAR2 ,
218 X_First_Name VARCHAR2,
219 X_Job_Title VARCHAR2 ,
220 X_Last_Update_Login NUMBER ,
221 X_Mail_Stop VARCHAR2 ,
222 X_Title VARCHAR2,
223 X_Attribute_Category VARCHAR2 ,
224 X_Attribute1 VARCHAR2 ,
225 X_Attribute2 VARCHAR2 ,
226 X_Attribute3 VARCHAR2 ,
227 X_Attribute4 VARCHAR2 ,
228 X_Attribute5 VARCHAR2 ,
229 X_Attribute6 VARCHAR2 ,
230 X_Attribute7 VARCHAR2 ,
231 X_Attribute8 VARCHAR2 ,
232 X_Attribute9 VARCHAR2 ,
233 X_Attribute10 VARCHAR2 ,
234 X_Attribute11 VARCHAR2 ,
235 X_Attribute12 VARCHAR2 ,
236 X_Attribute13 VARCHAR2 ,
237 X_Attribute14 VARCHAR2 ,
238 X_Attribute15 VARCHAR2 ,
239 X_Attribute16 VARCHAR2 ,
240 X_Attribute17 VARCHAR2 ,
241 X_Attribute18 VARCHAR2 ,
242 X_Attribute19 VARCHAR2 ,
243 X_Attribute20 VARCHAR2 ,
244 X_Attribute21 VARCHAR2 ,
245 X_Attribute22 VARCHAR2 ,
246 X_Attribute23 VARCHAR2 ,
247 X_Attribute24 VARCHAR2 ,
248 X_Attribute25 VARCHAR2 ,
249 X_Email_Address VARCHAR2 ,
250 X_Last_Name_Alt VARCHAR2 ,
251 X_First_Name_Alt VARCHAR2 ,
252 X_Contact_Number IN OUT NOCOPY VARCHAR2 ,
253 X_Party_Id NUMBER ,
254 X_Party_Site_Id NUMBER ,
255 X_Contact_Party_Id IN OUT NOCOPY NUMBER ,
256 X_Rel_Party_Id IN OUT NOCOPY NUMBER ,
257 X_Org_Contact_Id IN OUT NOCOPY NUMBER ,
258 X_Contact_Point_Id NUMBER ,
259 X_Cust_Account_Role_Id IN OUT NOCOPY NUMBER ,
260 X_Return_Status OUT NOCOPY VARCHAR2,
261 X_Msg_Count OUT NOCOPY NUMBER,
262 X_Msg_Data OUT NOCOPY VARCHAR2
263 ) IS
264
265 i_subject_party_id HZ_PARTIES.PARTY_ID%type;
266 i_subject_party_number VARCHAR2(30);
267 i_object_party_id NUMBER;
268 i_profile_id NUMBER;
269 tmp_var VARCHAR2(2000);
270 i_party_relationship_id NUMBER;
271 i_party_id HZ_PARTIES.PARTY_ID%type;
272 i_party_number VARCHAR2(30);
273 i_org_contact_id NUMBER;
274 tmp_var1 VARCHAR2(2000);
275 i_create_org_contact VARCHAR2(1);
276 i_lock_id NUMBER;
277 customer_party_id NUMBER;
278 l_temp VARCHAR2(1);
279 l_sql_stat VARCHAR2(4000);
280 l_sql_stat_2 VARCHAR2(4000);
281 l_sql_stat_3 VARCHAR2(4000);
282 l_created_by_module varchar2(150);
283 per_rec HZ_PARTY_V2PUB.person_rec_type ;
284 party_rec HZ_PARTY_V2PUB.party_rec_type ;
285 ocon_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type ;
286 party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type ;
287 arole_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
288
289 BEGIN
290 i_create_org_contact := 'Y';
291 l_created_by_module := 'OLMENR';
292
293 SELECT hz_contact_numbers_s.nextval INTO X_Contact_Number FROM DUAL;
294
295
296
297
298 per_rec.party_rec.status := 'A';
299 per_rec.person_first_name := x_first_name;
300 per_rec.person_last_name := x_last_name;
301 per_rec.person_title := x_title;
302 per_rec.created_by_module := l_created_by_module;
303
304 HZ_PARTY_V2PUB.create_person(
305 l_temp,
306 per_rec,
307 i_subject_party_id,
308 i_subject_party_number,
309 i_profile_id,
310 x_return_status,
311 x_msg_count,
312 x_msg_data
313 );
314
315
316 x_contact_party_id := i_subject_party_id;
317
318 --dbms_output.put_line('x_contact_party_id--'||x_contact_party_id);
319 IF x_msg_count > 1 THEN
320 FOR i IN 1..x_msg_count LOOP
321 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
322 tmp_var1 := tmp_var1 || ' '|| tmp_var;
323 END LOOP;
324 x_msg_data := tmp_var1;
325 END IF;
326
327 --dbms_output.put_line('x_return_status--'||x_return_status);
328 IF x_return_status <> 'S' THEN
329 RETURN;
330 END IF;
331 --Column relationship_code must have a value. Invalid value for subject_id. Please enter id value from .
332 --Column subject_table_name must have a value.(HZ_PARTIES)
333 --Column subject_type must have a value. Invalid value for object_id. Please enter id value from .(PERSON)
334 --Column object_table_name must have a value.(HZ_PARTIES)
335 --Column object_type must have a value (ORGANIZATION)
336 --
337 -- Create an Org Contact
338 --
339 --Check for select and customer_party_id hdshah
340 select party_id into customer_party_id from hz_cust_accounts where
341 cust_account_id = x_customer_id;
342
343 ocon_rec.party_rel_rec.subject_id := i_subject_party_id;
344 ocon_rec.party_rel_rec.object_id := customer_party_id;
345 ocon_rec.party_rel_rec.relationship_type := 'CONTACT';
346 ocon_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
347 ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
348 ocon_rec.party_rel_rec.object_table_name :='HZ_PARTIES';
349 ocon_rec.party_rel_rec.subject_type := 'PERSON';
350 ocon_rec.party_rel_rec.object_type := 'ORGANIZATION';
354 ocon_rec.party_site_id := x_party_site_id;
351 ocon_rec.party_rel_rec.start_date := sysdate;
352 ocon_rec.contact_number := x_contact_number;
353 ocon_rec.job_title := x_job_title;
355 --ocon_rec.title := x_title;
356 ocon_rec.orig_system_reference := x_orig_system_reference;
357 ocon_rec.attribute_category := x_Attribute_Category;
358 ocon_rec.attribute1 := x_Attribute1;
359 ocon_rec.attribute2 := x_Attribute2;
360 ocon_rec.attribute3 := x_Attribute3;
361 ocon_rec.attribute4 := x_Attribute4;
362 ocon_rec.attribute5 := x_Attribute5;
363 ocon_rec.attribute6 := x_Attribute6;
364 ocon_rec.attribute7 := x_Attribute7;
365 ocon_rec.attribute8 := x_attribute8;
366 ocon_rec.attribute9 := x_Attribute9;
367 ocon_rec.attribute10 := x_Attribute10;
368 ocon_rec.attribute11 := x_Attribute11;
369 ocon_rec.attribute12 := x_Attribute12;
370 ocon_rec.attribute13 := x_Attribute13;
371 ocon_rec.attribute14 := x_Attribute14;
372 ocon_rec.attribute15 := x_Attribute15;
373 ocon_rec.attribute16 := x_Attribute16;
374 ocon_rec.attribute17 := x_Attribute17;
375 ocon_rec.attribute18 := x_Attribute18;
376 ocon_rec.attribute19 := x_Attribute19;
377 ocon_rec.attribute20 := x_Attribute20;
378 ocon_rec.created_by_module := l_created_by_module;
379
380 HZ_PARTY_CONTACT_V2PUB.create_org_contact(
381 l_temp,
382 ocon_rec,
383 i_org_contact_id,
384 i_party_relationship_id,
385 i_party_id,
386 i_party_number,
387 x_return_status,
388 x_msg_count,
389 x_msg_data);
390
391
392
393
394 x_org_contact_id := i_org_contact_id;
395 x_rel_party_id := i_party_id;
396
397 --dbms_output.put_line('x_org_contact_id--'||x_org_contact_id);
398 --dbms_output.put_line('x_rel_party_id--'||x_rel_party_id);
399
400 IF x_msg_count > 1 THEN
401 FOR i IN 1..x_msg_count LOOP
402 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
403 tmp_var1 := tmp_var1 || ' '|| tmp_var;
404 END LOOP;
405 x_msg_data := tmp_var1;
406 END IF;
407
408 --dbms_output.put_line('x_msg_data--'||x_msg_data);
409 --dbms_output.put_line('x_return_status22--'||x_return_status);
410 IF x_return_status <> 'S' THEN
411 RETURN;
412 END IF;
413
414 --
415 -- Create a Cust Account Role
416 --
417
418
419 arole_rec.party_id := i_party_id;
420 arole_rec.cust_account_id := x_customer_id;
421 arole_rec.cust_acct_site_id := x_address_id;
422 arole_rec.role_type := 'CONTACT';
423 arole_rec.attribute_category := x_Attribute_Category;
424 arole_rec.attribute1 := x_Attribute1;
425 arole_rec.attribute2 := x_Attribute2;
426 arole_rec.attribute3 := x_Attribute3;
427 arole_rec.attribute4 := x_Attribute4;
428 arole_rec.attribute5 := x_Attribute5;
429 arole_rec.attribute6 := x_Attribute6;
430 arole_rec.attribute7 := x_Attribute7;
431 arole_rec.attribute8 := x_attribute8;
432 arole_rec.attribute9 := x_Attribute9;
433 arole_rec.attribute10 := x_Attribute10;
434 arole_rec.attribute11 := x_Attribute11;
435 arole_rec.attribute12 := x_Attribute12;
436 arole_rec.attribute13 := x_Attribute13;
437 arole_rec.attribute14 := x_Attribute14;
438 arole_rec.attribute15 := x_Attribute15;
439 arole_rec.attribute16 := x_Attribute16;
440 arole_rec.attribute17 := x_Attribute17;
441 arole_rec.attribute18 := x_Attribute18;
442 arole_rec.attribute19 := x_Attribute19;
443 arole_rec.attribute20 := x_Attribute20;
444 arole_rec.created_by_module := l_created_by_module;
445
446 HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role(
447 l_temp,
448 arole_rec,
449 x_cust_account_role_id,
450 x_return_status,
451 x_msg_count,
452 x_msg_data);
453
454
455 IF x_msg_count > 1 THEN
456 FOR i IN 1..x_msg_count LOOP
457 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
458 tmp_var1 := tmp_var1 || ' '|| tmp_var;
459 END LOOP;
460 x_msg_data := tmp_var1;
461
462 END IF;
463
464
465
466 END Insert_Row;
467
468 end ota_ra_con_api;