1 PACKAGE BODY IEM_GETCUST_PVT as
2 /* $Header: iemgcstb.pls 120.4 2006/03/22 15:19:19 rtripath noship $*/
3 -- Include checking for do not use email address 10/30/03 RT
4 G_PKG_NAME varchar2(255) :='IEM_GETCUST_PVT';
5
6 PROCEDURE GetCustomerInfo(
7 P_Api_Version_Number IN NUMBER,
8 P_Init_Msg_List IN VARCHAR2 ,
9 P_Commit IN VARCHAR2 ,
10 p_email IN VARCHAR2,
11 p_party_id OUT NOCOPY NUMBER,
12 p_customer_name OUT NOCOPY VARCHAR2,
13 p_first_name OUT NOCOPY VARCHAR2,
14 p_last_name OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_data OUT NOCOPY VARCHAR2)
18
19 IS
20 BEGIN
21 null; -- This api is no longer in use hence stub it
22 -- 08/06/2002 rtripath
23 END GetCustomerInfo;
24
25 PROCEDURE GetCustomerId(
26 P_Api_Version_Number IN NUMBER,
27 p_email IN VARCHAR2,
28 p_party_id OUT NOCOPY NUMBER,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_data OUT NOCOPY VARCHAR2)
32
33 IS
34
35 l_api_name VARCHAR2(255):='GetCustomerId';
36 l_ptype VARCHAR2(30);
37 BEGIN
38 x_return_status := 'S';
39 select owner_table_id into p_party_id
40 from hz_contact_points
41 where owner_table_name='HZ_PARTIES'
42 and contact_point_type='EMAIL'
43 and status='A'
44 and upper(email_address)=upper(p_email)
45 and contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
46 where contact_level_table='HZ_CONTACT_POINTS' and status='A');
47 EXCEPTION
48 WHEN NO_DATA_FOUND THEN
49 p_party_id:=-1;
50 when too_many_rows then
51 p_party_id:=0;
52 when others then
53 x_return_status := 'F';
54 END GetCustomerId;
55
56 PROCEDURE CustomerSearch(
57 P_Api_Version_Number IN NUMBER,
58 p_email IN VARCHAR2,
59 x_party_id OUT NOCOPY NUMBER,
60 x_msg_count OUT NOCOPY NUMBER,
61 x_return_status OUT NOCOPY VARCHAR2,
62 x_msg_data OUT NOCOPY VARCHAR2)
63
64 IS
65
66 l_api_name VARCHAR2(255):='CustomerSearch';
67 l_ptype VARCHAR2(30);
68 l_address varchar2(100);
69 l_email varchar2(100);
70 l_v1count number;
71 l_v2count number;
72 l_party_id number;
73 l_party_type varchar2(30);
74 l_contactcount NUMBER:=0;
75 l_api_version_number NUMBER:=1.0;
76 l_party_status number:=0;
77 l_rec_tbl IEM_GETCUST_PVT.cust_rec_tbl;
78 l_rec_tbl1 IEM_GETCUST_PVT.cust_rec_tbl;
79 l_rec_tbl2 IEM_GETCUST_PVT.cust_rec_tbl;
80 l_counter number:=1;
81 l_counter1 number:=1;
82 l_counter2 number:=1;
83 cursor c1 is
84 select a.owner_table_id,b.party_type,a.email_address
85 from hz_contact_points a,hz_parties b
86 where a.owner_table_name='HZ_PARTIES'
87 and upper(a.email_address)=upper(p_email)
88 and a.contact_point_type='EMAIL'
89 and a.status='A'
90 and b.party_id=a.owner_table_id
91 and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
92 where contact_level_table='HZ_CONTACT_POINTS' and status='A')
93 order by 2,1 DESC;
94 BEGIN
95 -- Standard call to check for call compatibility.
96 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
97 p_api_version_number,
98 l_api_name,
99 G_PKG_NAME)
100 THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
102 END IF;
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104 l_rec_tbl.delete;
105 l_rec_tbl1.delete;
106 l_rec_tbl2.delete;
107 for v1 in c1 LOOP
108 IF v1.party_type='ORGANIZATION' THEN
109 l_rec_tbl(l_counter).owner_table_id:=v1.owner_table_id;
110 l_counter:=l_counter+1;
111 ELSIF v1.party_type='PARTY_RELATIONSHIP' THEN
112 l_rec_tbl2(l_counter2).owner_table_id:=v1.owner_table_id;
113 l_counter2:=l_counter2+1;
114 ELSIF v1.party_type='PERSON' THEN
115 l_rec_tbl1(l_counter1).owner_table_id:=v1.owner_table_id;
116 l_counter1:=l_counter1+1;
117 END IF;
118 END LOOP;
119 IF l_rec_tbl.count>0 THEN
120 l_party_id:=l_rec_tbl(1).owner_table_id;
121 ELSIF l_rec_tbl1.count>0 THEN
122 l_party_id:=l_rec_tbl1(1).owner_table_id;
123 ELSIF l_rec_tbl2.count>0 THEN
124 FOR j in l_rec_tbl2.FIRST..l_rec_tbl2.LAST LOOP
125
126 select count(hzr.party_id) into l_v1count
127 from HZ_PARTIES hzp, HZ_RELATIONSHIPS hzr
128 where hzp.party_type='PARTY_RELATIONSHIP'
129 and hzr.party_id=hzp.party_id
130 and hzr.party_id=l_rec_tbl2(j).owner_table_id
131 and hzr.status in('A','I')
132 and (hzr.relationship_code='CONTACT_OF' or hzr.relationship_code='EMPLOYEE_OF');
133 IF l_v1count>0 THEN
134 l_party_id:=l_rec_tbl2(j).owner_table_id;
135 EXIT;
136 END IF;
137 EXIT when l_party_id is not null;
138 END LOOP;
139 END IF;
140 IF l_party_id is not null THEN
141 x_party_id:=l_party_id;
142 ELSE
143 x_party_id:=FND_PROFILE.VALUE_SPECIFIC('IEM_DEFAULT_CUSTOMER_ID');
144 END IF;
145 -- Standard callto get message count and if count is 1, get message info.
146 FND_MSG_PUB.Count_And_Get
147 ( p_count => x_msg_count,
148 p_data => x_msg_data
149 );
150 EXCEPTION
151 WHEN FND_API.G_EXC_ERROR THEN
152 x_return_status := FND_API.G_RET_STS_ERROR ;
153 FND_MSG_PUB.Count_And_Get
154 ( p_count => x_msg_count,
155 p_data => x_msg_data
156 );
157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
159 FND_MSG_PUB.Count_And_Get
160 ( p_count => x_msg_count,
161 p_data => x_msg_data
162 );
163 WHEN OTHERS THEN
164 x_return_status := FND_API.G_RET_STS_ERROR;
165 IF FND_MSG_PUB.Check_Msg_Level
166 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
167 THEN
168 FND_MSG_PUB.Add_Exc_Msg
169 ( G_PKG_NAME ,
170 l_api_name
171 );
172 END IF;
173 FND_MSG_PUB.Count_And_Get
174 ( p_count => x_msg_count ,
175 p_data => x_msg_data
176 );
177 END CustomerSearch;
178 End IEM_GETCUST_PVT;
[Home] [Help]