[Home] [Help]
PACKAGE BODY: APPS.AML_SALES_LEADS_V2_PUB
Source
1 PACKAGE BODY AML_SALES_LEADS_V2_PUB as
2 /* $Header: amlpaslb.pls 120.2 2005/11/07 16:37:28 solin noship $ */
3 -- Start of Comments
4 -- Package name : AML_SALES_LEADS_V2_PUB
5 -- Purpose : Sales Leads Management
6 -- NOTE : This is atomic public API to create lead.
7 -- History
8 -- 08/27/2000 AANJARIA Created.
9 --
10 -- End of Comments
11
12 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_SALES_LEADS_V2_PUB';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amlpaslb.pls';
14
15
16 -- *********************************************************************
17 -- Procedure : Create_sales_lead
18 -- Description: Atomic procedure to create lead and associated entities
19 -- *********************************************************************
20
21
22 PROCEDURE Create_sales_lead (
23 P_Api_Version_Number IN NUMBER,
24 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
25 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
26 P_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
27 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
28 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
29 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
30 P_Identity_Salesforce_Id IN NUMBER := FND_API.G_MISS_NUM,
31 P_Salesgroup_Id IN NUMBER := FND_API.G_MISS_NUM,
32 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
33 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
34 P_SALES_LEAD_Rec IN AS_SALES_LEADS_PUB.SALES_LEAD_Rec_Type
35 := AS_SALES_LEADS_PUB.G_MISS_SALES_LEAD_REC,
36 P_SALES_LEAD_LINE_Tbl IN AS_SALES_LEADS_PUB.SALES_LEAD_LINE_Tbl_type
37 := AS_SALES_LEADS_PUB.G_MISS_SALES_LEAD_LINE_Tbl,
38 P_SALES_LEAD_CONTACT_Tbl IN AS_SALES_LEADS_PUB.SALES_LEAD_CONTACT_Tbl_Type
39 := AS_SALES_LEADS_PUB.G_MISS_SALES_LEAD_CONTACT_Tbl,
40 P_Lead_note IN VARCHAR2 DEFAULT NULL,
41 P_Note_type IN VARCHAR2 DEFAULT NULL,
42 X_SALES_LEAD_ID OUT NOCOPY NUMBER,
43 X_SALES_LEAD_LINE_OUT_Tbl OUT NOCOPY AS_SALES_LEADS_PUB.SALES_LEAD_LINE_OUT_Tbl_type,
44 X_SALES_LEAD_CNT_OUT_Tbl OUT NOCOPY AS_SALES_LEADS_PUB.SALES_LEAD_CNT_OUT_Tbl_Type,
45 X_note_id OUT NOCOPY NUMBER,
46 X_Return_Status OUT NOCOPY VARCHAR2,
47 X_Msg_Count OUT NOCOPY NUMBER,
48 X_Msg_Data OUT NOCOPY VARCHAR2
49 )
50 IS
51
52 l_api_name CONSTANT VARCHAR2(30) := 'Create_sales_lead';
53 l_api_version_number CONSTANT NUMBER := 2.0;
54 l_salesforce_id NUMBER;
55 l_group_id NUMBER;
56 l_party_type VARCHAR2(30);
57 l_org_contact_id NUMBER;
58
59 l_classification_tbl as_interest_pub.interest_tbl_type;
60 l_interest_use_code VARCHAR2(30);
61 l_interest_out_id NUMBER;
62 l_sales_lead_id NUMBER;
63
64 l_note_context_rec jtf_notes_pub.jtf_note_contexts_rec_type;
65 l_note_context_rec_tbl jtf_notes_pub.jtf_note_contexts_tbl_type;
66
67 CURSOR C_get_slaesforce(c_user_id NUMBER)
68 IS
69 SELECT JS.RESOURCE_ID
70 FROM JTF_RS_RESOURCE_EXTNS JS
71 WHERE JS.USER_ID = C_User_Id;
72
73 CURSOR c_get_group_id (c_resource_id NUMBER)
74 IS
75 SELECT MAX(grp.group_id) salesgroup_id
76 FROM JTF_RS_GROUP_MEMBERS mem,
77 JTF_RS_ROLE_RELATIONS rrel,
78 JTF_RS_ROLES_B role,
79 JTF_RS_GROUP_USAGES u,
80 JTF_RS_GROUPS_B grp
81 WHERE mem.group_member_id = rrel.role_resource_id AND
82 rrel.role_resource_type = 'RS_GROUP_MEMBER' AND
83 rrel.role_id = role.role_id AND
84 role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM') AND
85 mem.delete_flag <> 'Y' AND
86 rrel.delete_flag <> 'Y' AND
87 sysdate BETWEEN rrel.start_date_active AND
88 NVL(rrel.end_date_active, SYSDATE) AND
89 mem.group_id = u.group_id AND
90 u.usage in ('SALES','PRM') AND
91 mem.group_id = grp.group_id AND
92 sysdate BETWEEN grp.start_date_active AND
93 NVL(grp.end_date_active,sysdate) AND
94 mem.resource_id = c_resource_id;
95
96 CURSOR C_get_party_type (p_customer_id IN NUMBER)
97 IS
98 SELECT party_type
99 FROM hz_parties
100 WHERE party_id = p_customer_id;
101
102 CURSOR C_get_org_contact_id (p_customer_id IN NUMBER, p_contact_id IN NUMBER)
103 IS
104 SELECT org_contact_id
105 FROM hz_org_contacts hzoc, hz_relationships hzr
106 WHERE hzoc.party_relationship_id = hzr.relationship_id
107 AND subject_id = p_customer_id
108 AND object_id = p_contact_id;
109
110 BEGIN
111 SAVEPOINT Create_sales_lead_pub;
112
113 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
114 'Pub: ' || l_api_name || ' Start');
115
116 -- Standard call to check for call compatibility.
117 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
118 p_api_version_number,
119 l_api_name,
120 G_PKG_NAME)
121 THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124
125 -- Initialize message list if p_init_msg_list is set to TRUE.
126 IF FND_API.to_Boolean( p_init_msg_list )
127 THEN
128 FND_MSG_PUB.initialize;
129 END IF;
130
131 -- Initialize API return status to SUCCESS
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 l_salesforce_id := P_Identity_Salesforce_Id;
135
136 IF l_salesforce_id IS NULL OR l_salesforce_id = FND_API.G_MISS_NUM
137 THEN
138 OPEN C_Get_SLAESFORCE(fnd_global.user_id);
139 FETCH C_Get_SLAESFORCE
140 INTO l_salesforce_id;
141 CLOSE C_Get_SLAESFORCE;
142
143 If (l_salesforce_id is null) then
144 l_salesforce_id := fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
145 End if;
146 END IF;
147
148 IF P_Salesgroup_Id = FND_API.G_MISS_NUM
149 THEN
150 OPEN c_get_group_id (l_salesforce_id);
151 FETCH c_get_group_id INTO l_group_id;
152 CLOSE c_get_group_id;
153 ELSE
154 l_group_id := P_Salesgroup_Id;
155 END IF;
156
157 -- Create Sales Lead
158
159 AS_SALES_LEADS_PVT.create_sales_lead(
160 p_api_version_number => 2.0,
161 p_init_msg_list => P_Init_Msg_List,
162 p_commit => P_Commit,
163 p_validation_level => P_Validation_Level,
164 p_check_access_flag => P_Check_Access_Flag,
165 p_admin_flag => P_Admin_Flag,
166 p_admin_group_id => P_Admin_Group_Id,
167 p_identity_salesforce_id => l_salesforce_id,
168 p_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
169 p_sales_lead_rec => P_SALES_LEAD_Rec,
170 p_sales_lead_line_tbl => p_sales_lead_line_tbl,
171 p_sales_lead_contact_tbl => p_sales_lead_contact_tbl,
172 x_sales_lead_id => l_sales_lead_id,
173 x_return_status => x_return_status,
174 x_msg_count => x_MSG_COUNT,
175 x_msg_data => x_msg_data,
176 x_sales_lead_line_out_tbl => x_sales_lead_line_out_tbl,
177 x_sales_lead_cnt_out_tbl => x_sales_lead_cnt_out_tbl);
178
179
180 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
181 raise FND_API.G_EXC_ERROR;
182 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
183 raise FND_API.G_EXC_UNEXPECTED_ERROR;
184 END IF;
185
186 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
187 'Pub: ' || l_api_name || ' Sales lead id '
188 ||to_char(l_sales_lead_id)||'created');
189
190 --dbms_output.put_line('sales lead id '||to_char(l_sales_lead_id));
191
192 -- Create Interest
193 /* --Commenting out till we get patch from sales for AS_INTEREST_PUB API
194 -- Get party type
195 OPEN C_get_party_type(p_sales_lead_rec.customer_id);
196 FETCH C_get_party_type INTO l_party_type;
197 CLOSE C_get_party_type;
198
199 IF p_sales_lead_rec.primary_cnt_person_party_id IS NOT NULL THEN
200 -- Get org_contact_id
201 OPEN C_get_org_contact_id (p_sales_lead_rec.customer_id, p_sales_lead_rec.primary_cnt_person_party_id);
202 FETCH C_get_org_contact_id into l_org_contact_id;
203 CLOSE C_get_org_contact_id;
204 END IF;
205
206 For i IN 1..p_sales_lead_line_tbl.count Loop
207 l_classification_tbl(i).customer_id := p_sales_lead_rec.customer_id;
208 l_classification_tbl(i).address_id := p_sales_lead_rec.address_id;
209 l_classification_tbl(i).contact_id := l_org_contact_id;
210 l_classification_tbl(i).category_id := p_sales_lead_line_tbl(i).category_id;
211 -- l_classification_tbl(i).interest_type_id := p_sales_lead_line_tbl(i).interest_type_id;
212 -- l_classification_tbl(i).primary_interest_code_id := p_sales_lead_line_tbl(i).primary_interest_code_id;
213 -- l_classification_tbl(i).secondary_interest_code_id := p_sales_lead_line_tbl(i).secondary_interest_code_id;
214
215 IF l_party_type = 'PERSON' THEN
216 l_interest_use_code := 'CONTACT_INTEREST';
217 ELSIF l_party_type = 'ORGANIZATION' THEN
218 l_interest_use_code := 'COMPANY_CLASSIFICATION' ;
219 END IF;
220
221 AS_INTEREST_PUB.Create_Interest(
222 p_api_version_number => 2.0 ,
223 p_init_msg_list => FND_API.G_FALSE,
224 p_Commit => FND_API.G_FALSE,
225 p_interest_rec => l_classification_tbl(i),
226 p_customer_id => p_sales_lead_rec.customer_id,
227 p_address_id => p_sales_lead_rec.address_id,
228 p_contact_id => l_org_contact_id,
229 p_lead_id => null,
230 p_interest_use_code => l_interest_use_code,
231 p_check_access_flag => 'N',
232 p_admin_flag => P_Admin_Flag,
233 p_admin_group_id => P_Admin_Group_Id,
234 p_identity_salesforce_id => l_salesforce_id,
235 p_access_profile_rec => null,
236 p_return_status => x_return_status,
237 p_msg_count => x_msg_count,
238 p_msg_data => x_msg_data,
239 p_interest_out_id => l_interest_out_id) ;
240 End Loop;
241
242
243 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
244 RAISE FND_API.G_EXC_ERROR;
245 END IF;
246
247 --dbms_output.put_line('Interest created');
248 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
249 'Pub: ' || l_api_name || ' Interest created');
250 */
251
252 -- Create Lead Note and Context
253
254
255 If ((l_sales_lead_id is not null)
256 AND (p_sales_lead_rec.customer_id is not null)
257 AND ((p_lead_note is not null) OR (p_lead_note <> FND_API.G_MISS_CHAR))
258 )
259 THEN
260 --Assign values to context rec type
261 l_note_context_rec.NOTE_CONTEXT_TYPE := 'LEAD';
262 l_note_context_rec.NOTE_CONTEXT_TYPE_ID := l_sales_lead_id;
263 l_note_context_rec.LAST_UPDATE_DATE := SYSDATE;
264 l_note_context_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
265 l_note_context_rec.CREATION_DATE := SYSDATE;
266 l_note_context_rec.CREATED_BY := FND_GLOBAL.USER_ID;
267 l_note_context_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
268
269 l_note_context_rec_tbl(1) := l_note_context_rec;
270
271 l_note_context_rec.NOTE_CONTEXT_TYPE := 'PARTY_ORGANIZATION';
272 l_note_context_rec.NOTE_CONTEXT_TYPE_ID := p_sales_lead_rec.customer_id;
273 l_note_context_rec.LAST_UPDATE_DATE := SYSDATE;
274 l_note_context_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
275 l_note_context_rec.CREATION_DATE := SYSDATE;
276 l_note_context_rec.CREATED_BY := FND_GLOBAL.USER_ID;
277 l_note_context_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
278
279 l_note_context_rec_tbl(2) := l_note_context_rec;
280
281 -- Call Jtf_notes_pub.create_note()
282
283 JTF_NOTES_PUB.Create_Note (
284 p_parent_note_id => NULL
285 , p_jtf_note_id => NULL
286 , p_api_version => 1.0
287 , p_init_msg_list => 'T'
288 , p_commit => 'F'
289 , p_validation_level => 100
290 , x_return_status => x_return_status
291 , x_msg_count => x_msg_count
292 , x_msg_data => x_msg_data
293 , p_org_id => NULL
294 , p_source_object_id => l_sales_lead_id
295 , p_source_object_code => 'LEAD'
296 , p_notes => p_lead_note
297 , p_notes_detail => NULL --EMPTY_CLOB()
298 , p_note_status => NULL
299 , p_entered_by => FND_GLOBAL.USER_ID
300 , p_entered_date => SYSDATE
301 , x_jtf_note_id => x_note_id
302 , p_last_update_date => SYSDATE
303 , p_last_updated_by => FND_GLOBAL.USER_ID
304 , p_creation_date => SYSDATE
305 , p_created_by => FND_GLOBAL.USER_ID
306 , p_last_update_login => FND_GLOBAL.USER_ID
307 , p_attribute1 => NULL
308 , p_attribute2 => NULL
309 , p_attribute3 => NULL
310 , p_attribute4 => NULL
311 , p_attribute5 => NULL
312 , p_attribute6 => NULL
313 , p_attribute7 => NULL
314 , p_attribute8 => NULL
315 , p_attribute9 => NULL
316 , p_attribute10 => NULL
317 , p_attribute11 => NULL
318 , p_attribute12 => NULL
319 , p_attribute13 => NULL
320 , p_attribute14 => NULL
321 , p_attribute15 => NULL
322 , p_context => NULL
323 , p_note_type => NVL(p_note_type,'AS_USER')
324 , p_jtf_note_contexts_tab => l_note_context_rec_tbl
325 );
326
327 --dbms_output.put_line (x_return_status);
328 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
329 raise FND_API.G_EXC_ERROR;
330 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
331 raise FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333
334
335 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
336 'Pub: ' || l_api_name || ' Note id '
337 ||to_char(x_note_id)||'created');
338
339 --dbms_output.put_line('note id '||to_char(x_note_id));
340
341 END IF; --if lead_note is not null
342
343 -- Process Lead after creation
344 AS_SALES_LEAD_ENGINE_PVT.Lead_Process_After_Create(
345 P_Api_Version_Number => 2.0,
346 P_Init_Msg_List => FND_API.G_FALSE,
347 P_Commit => FND_API.G_FALSE,
348 P_Validation_Level => P_Validation_Level,
349 P_Check_Access_Flag => P_Check_Access_Flag,
350 p_Admin_Flag => p_Admin_Flag,
351 P_Admin_Group_Id => P_Admin_Group_Id,
352 P_identity_salesforce_id => l_salesforce_id,
353 P_Salesgroup_Id => l_group_Id,
354 P_Sales_Lead_Id => l_sales_lead_id,
355 X_Return_Status => x_return_status,
356 X_Msg_Count => x_msg_count,
357 X_Msg_Data => x_msg_data);
358
359
360 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
361 raise FND_API.G_EXC_ERROR;
362 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
363 raise FND_API.G_EXC_UNEXPECTED_ERROR;
364 END IF;
365
366 --dbms_output.put_line('lead processed.');
367 x_sales_lead_id := l_sales_lead_id;
368 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
369 'Pub: ' || l_api_name || ' Lead Processing completed ');
370
371 --
372 -- End of API body
373 --
374
375 IF FND_API.to_Boolean( p_commit )
376 THEN
377 COMMIT WORK;
378 END IF;
379
380 FND_MSG_PUB.Count_And_Get
381 ( p_count => x_msg_count,
382 p_data => x_msg_data
383 );
384
385 EXCEPTION
386 /*
387 WHEN others THEN
388 dbms_output.put_line('EXCEPTION RAISED: -->');
389 FOR l_msg_index IN 1..x_msg_count LOOP
390 fnd_message.set_encoded(fnd_msg_pub.get(l_msg_index));
391 dbms_output.put_line(fnd_message.get);
392 END LOOP;
393 */
394
395 WHEN FND_API.G_EXC_ERROR THEN
396 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
397 P_API_NAME => L_API_NAME
398 ,P_PKG_NAME => G_PKG_NAME
399 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
400 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
401 ,X_MSG_COUNT => X_MSG_COUNT
402 ,X_MSG_DATA => X_MSG_DATA
403 ,X_RETURN_STATUS => X_RETURN_STATUS);
404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
405 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
406 P_API_NAME => L_API_NAME
407 ,P_PKG_NAME => G_PKG_NAME
408 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
409 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
410 ,X_MSG_COUNT => X_MSG_COUNT
411 ,X_MSG_DATA => X_MSG_DATA
412 ,X_RETURN_STATUS => X_RETURN_STATUS);
413 WHEN OTHERS THEN
414 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
415 P_API_NAME => L_API_NAME
416 ,P_PKG_NAME => G_PKG_NAME
417 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
418 ,P_SQLCODE => SQLCODE
419 ,P_SQLERRM => SQLERRM
420 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
421 ,X_MSG_COUNT => X_MSG_COUNT
422 ,X_MSG_DATA => X_MSG_DATA
423 ,X_RETURN_STATUS => X_RETURN_STATUS);
424
425 END Create_sales_lead;
426
427 END AML_SALES_LEADS_V2_PUB;