1 PACKAGE BODY IEM_GETCUST_PVT as
2 /* $Header: iemgcstb.pls 120.4.12020000.2 2012/08/01 06:57:13 shramana ship $*/
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 --Join with hz_parties and do not include inactivated relationships/parties
40 --Fix for bug 14337195 - 7/30/2012 - Sharanya R
41 select a.owner_table_id into p_party_id
42 from hz_contact_points a,hz_parties b
43 where a.owner_table_name='HZ_PARTIES'
44 and a.contact_point_type='EMAIL'
45 and a.status='A'
46 and b.status = 'A'
47 and b.party_id=a.owner_table_id
48 and upper(a.email_address)=upper(p_email)
49 and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
50 where contact_level_table='HZ_CONTACT_POINTS' and status='A');
51 /*select owner_table_id into p_party_id
52 from hz_contact_points
53 where owner_table_name='HZ_PARTIES'
54 and contact_point_type='EMAIL'
55 and status='A'
56 and upper(email_address)=upper(p_email)
57 and contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
58 where contact_level_table='HZ_CONTACT_POINTS' and status='A');*/
59 EXCEPTION
60 WHEN NO_DATA_FOUND THEN
61 p_party_id:=-1;
62 when too_many_rows then
63 p_party_id:=0;
64 when others then
65 x_return_status := 'F';
66 END GetCustomerId;
67
68 PROCEDURE CustomerSearch(
69 P_Api_Version_Number IN NUMBER,
70 p_email IN VARCHAR2,
71 x_party_id OUT NOCOPY NUMBER,
72 x_msg_count OUT NOCOPY NUMBER,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_msg_data OUT NOCOPY VARCHAR2)
75
76 IS
77
78 l_api_name VARCHAR2(255):='CustomerSearch';
79 l_ptype VARCHAR2(30);
80 l_address varchar2(100);
81 l_email varchar2(100);
82 l_v1count number;
83 l_v2count number;
84 l_party_id number;
85 l_party_type varchar2(30);
86 l_contactcount NUMBER:=0;
87 l_api_version_number NUMBER:=1.0;
88 l_party_status number:=0;
89 l_rec_tbl IEM_GETCUST_PVT.cust_rec_tbl;
90 l_rec_tbl1 IEM_GETCUST_PVT.cust_rec_tbl;
91 l_rec_tbl2 IEM_GETCUST_PVT.cust_rec_tbl;
92 l_counter number:=1;
93 l_counter1 number:=1;
94 l_counter2 number:=1;
95 cursor c1 is
96 select a.owner_table_id,b.party_type,a.email_address
97 from hz_contact_points a,hz_parties b
98 where a.owner_table_name='HZ_PARTIES'
99 and upper(a.email_address)=upper(p_email)
100 and a.contact_point_type='EMAIL'
101 and a.status='A'
102 AND b.status = 'A'--Fix for bug 14337195 - 7/30/2012 - Sharanya R
103 and b.party_id=a.owner_table_id
104 and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
105 where contact_level_table='HZ_CONTACT_POINTS' and status='A')
106 order by 2,1 DESC;
107 BEGIN
108 -- Standard call to check for call compatibility.
109 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
110 p_api_version_number,
111 l_api_name,
112 G_PKG_NAME)
113 THEN
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117 l_rec_tbl.delete;
118 l_rec_tbl1.delete;
119 l_rec_tbl2.delete;
120 for v1 in c1 LOOP
121 IF v1.party_type='ORGANIZATION' THEN
122 l_rec_tbl(l_counter).owner_table_id:=v1.owner_table_id;
123 l_counter:=l_counter+1;
124 ELSIF v1.party_type='PARTY_RELATIONSHIP' THEN
125 l_rec_tbl2(l_counter2).owner_table_id:=v1.owner_table_id;
126 l_counter2:=l_counter2+1;
127 ELSIF v1.party_type='PERSON' THEN
128 l_rec_tbl1(l_counter1).owner_table_id:=v1.owner_table_id;
129 l_counter1:=l_counter1+1;
130 END IF;
131 END LOOP;
132 IF l_rec_tbl.count>0 THEN
133 l_party_id:=l_rec_tbl(1).owner_table_id;
134 ELSIF l_rec_tbl1.count>0 THEN
135 l_party_id:=l_rec_tbl1(1).owner_table_id;
136 ELSIF l_rec_tbl2.count>0 THEN
137 FOR j in l_rec_tbl2.FIRST..l_rec_tbl2.LAST LOOP
138
139 select count(hzr.party_id) into l_v1count
140 from HZ_PARTIES hzp, HZ_RELATIONSHIPS hzr
141 where hzp.party_type='PARTY_RELATIONSHIP'
142 and hzr.party_id=hzp.party_id
143 and hzr.party_id=l_rec_tbl2(j).owner_table_id
144 and hzr.status in('A','I')
145 and (hzr.relationship_code='CONTACT_OF' or hzr.relationship_code='EMPLOYEE_OF');
146 IF l_v1count>0 THEN
147 l_party_id:=l_rec_tbl2(j).owner_table_id;
148 EXIT;
149 END IF;
150 EXIT when l_party_id is not null;
151 END LOOP;
152 END IF;
153 IF l_party_id is not null THEN
154 x_party_id:=l_party_id;
155 ELSE
156 x_party_id:=FND_PROFILE.VALUE_SPECIFIC('IEM_DEFAULT_CUSTOMER_ID');
157 END IF;
158 -- Standard callto get message count and if count is 1, get message info.
159 FND_MSG_PUB.Count_And_Get
160 ( p_count => x_msg_count,
161 p_data => x_msg_data
162 );
163 EXCEPTION
164 WHEN FND_API.G_EXC_ERROR THEN
165 x_return_status := FND_API.G_RET_STS_ERROR ;
166 FND_MSG_PUB.Count_And_Get
167 ( p_count => x_msg_count,
168 p_data => x_msg_data
169 );
170 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
172 FND_MSG_PUB.Count_And_Get
173 ( p_count => x_msg_count,
174 p_data => x_msg_data
175 );
176 WHEN OTHERS THEN
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 IF FND_MSG_PUB.Check_Msg_Level
179 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
180 THEN
181 FND_MSG_PUB.Add_Exc_Msg
182 ( G_PKG_NAME ,
183 l_api_name
184 );
185 END IF;
186 FND_MSG_PUB.Count_And_Get
187 ( p_count => x_msg_count ,
188 p_data => x_msg_data
189 );
190 END CustomerSearch;
191 End IEM_GETCUST_PVT;
[Home] [Help]