DBA Data[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;