[Home] [Help]
PACKAGE BODY: APPS.PV_LEADLOG_PVT
Source
1 package body PV_LEADLOG_PVT as
2 /* $Header: pvxvlalb.pls 115.14 2002/11/20 02:06:16 pklin ship $ */
3
4 --
5 -- NAME
6 -- PV_LEADASN_PVT
7 --
8 -- PURPOSE
9 -- Private API for creating pv_leads
10 -- uses.
11 --
12 -- NOTES
13 -- This pacakge should not be used by any non-osm sources. All non OSM
14 -- sources should use the Public create_account API
15 --
16 --
17 --
18 -- HISTORY
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):='PV_LEADLOG_PVT';
21 G_FILE_NAME CONSTANT VARCHAR2(12):='pvxvlalb.pls';
22
23 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
24 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
25 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
26 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
27 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
28
29
30
31
32 --
33 -- NAME
34 -- InsertAssignRow
35 --
36 -- PURPOSE
37 --
38 -- NOTES
39 --
40 --
41 --
42 --
43
44 PROCEDURE InsertAssignLogRow (
45 X_Rowid OUT NOCOPY ROWID ,
46 x_assignlog_ID OUT NOCOPY NUMBER ,
47 p_Lead_assignment_ID IN NUMBER ,
48 p_Last_Updated_By IN NUMBER ,
49 p_Last_Update_Date IN DATE ,
50 p_Object_Version_number IN NUMBER ,
51 p_Last_Update_Login IN NUMBER ,
52 p_Created_By IN NUMBER ,
53 p_Creation_Date IN DATE ,
54 p_lead_id IN NUMBER ,
55 p_duration IN NUMBER ,
56 p_partner_id IN NUMBER ,
57 p_assign_sequence IN NUMBER ,
58 p_status_date IN DATE ,
59 p_status IN VARCHAR2 ,
60 p_cm_id IN NUMBER ,
61 p_wf_pt_user IN VARCHAR2 ,
62 p_wf_cm_user IN VARCHAR2 ,
63 p_wf_item_type IN VARCHAR2 ,
64 p_wf_item_key IN VARCHAR2 ,
65 p_trans_type IN NUMBER ,
66 p_error_txt IN VARCHAR2 ,
67 p_status_change_comments IN VARCHAR2 ,
68 x_return_status OUT NOCOPY VARCHAR2) IS
69
70
71 CURSOR C IS
72 SELECT rowid
73 FROM pv_assignment_logs
74 WHERE assignment_ID = X_assignlog_ID;
75
76 l_assignment_id number;
77
78 BEGIN
79
80
81 x_return_status := 'S';
82
83 select pv_assignment_logs_s.nextval
84 into l_assignment_ID
85 from sys.dual;
86
87
88
89 insert into pv_assignment_logs (
90 ASSIGNMENT_ID ,
91 LAST_UPDATE_DATE,
92 LAST_UPDATED_BY,
93 CREATION_DATE,
94 CREATED_BY,
95 OBJECT_VERSION_NUMBER,
96 LAST_UPDATE_LOGIN,
97 LEAD_ID,
98 DURATION,
99 LEAD_ASSIGNMENT_ID,
100 PARTNER_ID,
101 ASSIGN_SEQUENCE,
102 CM_ID,
103 WF_PT_USER,
104 WF_CM_USER,
105 WF_ITEM_TYPE,
106 WF_ITEM_KEY,
107 STATUS_DATE,
108 STATUS,
109 TRANS_TYPE,
110 ERROR_TXT,
111 STATUS_CHANGE_COMMENTS)
112 Values (
113 l_assignment_id ,
114 p_Last_Update_Date ,
115 p_Last_Updated_By ,
116 p_Creation_Date ,
117 p_Created_By ,
118 p_Object_version_number,
119 p_Last_Update_Login ,
120 p_lead_id ,
121 p_duration ,
122 p_Lead_assignment_ID ,
123 p_partner_id ,
124 p_assign_sequence ,
125 p_cm_id ,
126 p_wf_pt_user ,
127 p_wf_cm_user ,
128 p_wf_item_type ,
129 p_wf_item_key ,
130 p_status_date ,
131 p_status ,
132 p_trans_type ,
133 p_error_txt ,
134 p_status_change_comments);
135
136 X_assignlog_ID := l_assignment_id;
137
138 OPEN C;
139 FETCH C INTO X_Rowid;
140 IF (C%NOTFOUND)
141 THEN
142 CLOSE C;
143 Raise NO_DATA_FOUND;
144 END IF;
145 CLOSE C;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 x_return_status := 'E';
150
151 END InsertAssignLogRow;
152
153
154 --
155 -- NAME
156 -- CreateAssignLog
157 --
158 -- PURPOSE
159 -- Private API to update customer, address, site uses in ra tables for OSM
160 --
161 -- NOTES
162 -- This is a private API, which should only be called from PV. All
163 --
164 --
165 --
166
167 PROCEDURE CreateAssignLog
168 ( p_api_version_number IN NUMBER,
169 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
170 p_commit IN VARCHAR2 := FND_API.G_FALSE,
171 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
172 p_assignlog_rec IN ASSIGNLOG_REC_TYPE := G_MISS_ASSIGNLOG_REC,
173 x_assignment_id OUT NOCOPY NUMBER,
174 x_return_status OUT NOCOPY VARCHAR2,
175 x_msg_count OUT NOCOPY NUMBER,
176 x_msg_data OUT NOCOPY VARCHAR2)
177 IS
178
179
180 l_api_name CONSTANT VARCHAR2(30) := 'CreateAssignLog';
181 l_api_version_number CONSTANT NUMBER := 1.0;
182 l_assignment_id NUMBER;
183 l_rowid ROWID;
184 l_return_status VARCHAR2(1); -- Local return status for calling
185 l_return_status_full varchar2(1);
186 l_assignlog_rec ASSIGNLOG_REC_TYPE := p_assignlog_rec;
187
188
189
190 BEGIN
191
192 -- Standard Start of API savepoint
193 SAVEPOINT CREATE_AssignLog_PVT;
194
195 -- Standard call to check for call compatibility.
196 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
197 p_api_version_number,
198 l_api_name,
199 G_PKG_NAME)
200 THEN
201 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_ERROR)
202 THEN
203 fnd_message.Set_Name('PV', 'API_UNEXP_ERROR_IN_PROCESSING');
204 fnd_message.Set_Token('ROW', 'PV_LEADLOG', TRUE);
205 fnd_msg_pub.ADD;
206 END IF;
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208 END IF;
209
210
211 -- Initialize message list if p_init_msg_list is set to TRUE.
212 IF FND_API.to_Boolean( p_init_msg_list )
213 THEN
214 fnd_msg_pub.initialize;
215 END IF;
216
217 -- Debug Message
218 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
219 THEN
220 fnd_message.Set_Name('PV', 'Pvt Acc API: Start');
221 fnd_msg_pub.Add;
222 END IF;
223
224 -- Initialize API return status to success
225 --
226 x_return_status := FND_API.G_RET_STS_SUCCESS;
227 l_return_status_full := FND_API.G_RET_STS_SUCCESS;
228 l_assignment_id := NULL;
229
230 --
231 -- API body
232 --
233
234
235 -- ******************************************************************
236 -- Validate Environment
237 -- ******************************************************************
238 IF G_User_Id IS NULL
239 THEN
240 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_ERROR)
241 THEN
242 fnd_message.Set_Name('PV', 'UT_CANNOT_GET_PROFILE_VALUE');
243 fnd_message.Set_Token('PROFILE', 'USER_ID', FALSE);
244 fnd_msg_pub.ADD;
245 END IF;
246 RAISE FND_API.G_EXC_ERROR;
247 END IF;
248 -- ******************************************************************
249
250
251
252 InsertAssignLogRow (
253 X_Rowid => l_rowid ,
254 x_assignlog_ID => l_assignment_id ,
255 p_Lead_assignment_ID => l_assignlog_rec.Lead_assignment_ID ,
256 p_Last_Updated_By => G_USER_ID ,
257 p_Last_Update_Date => SYSDATE ,
258 p_Last_Update_Login => G_LOGIN_ID ,
259 p_Created_By => G_USER_ID ,
260 p_Creation_Date => SYSDATE ,
261 p_Object_Version_Number => l_assignlog_rec.object_version_number,
262 p_lead_id => l_assignlog_rec.lead_id ,
263 p_duration => l_assignlog_rec.duration ,
264 p_partner_id => l_assignlog_rec.partner_id ,
265 p_assign_sequence => l_assignlog_rec.assign_sequence ,
266 p_status_date => l_assignlog_rec.status_date ,
267 p_status => l_assignlog_rec.status ,
268 p_cm_id => l_assignlog_rec.cm_id ,
269 p_wf_pt_user => l_assignlog_rec.wf_pt_user ,
270 p_wf_cm_user => l_assignlog_rec.wf_cm_user ,
271 p_wf_item_type => l_assignlog_rec.wf_item_type ,
272 p_wf_item_key => l_assignlog_rec.wf_item_key ,
273 p_trans_type => l_assignlog_rec.trans_type ,
274 p_error_txt => l_assignlog_rec.error_txt ,
275 p_status_change_comments => l_assignlog_rec.status_change_comments,
276 x_return_status => l_return_status);
277
278 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
279 raise FND_API.G_EXC_ERROR;
280 end if;
281
282
283
284 x_assignment_id := l_assignment_id;
285
286 -- Debug Message
287 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
288 THEN
289 fnd_message.Set_Name('PV', 'Pvt Acc API: Insert Addr Rec');
290 fnd_msg_pub.Add;
291 END IF;
292
293
294 --
295 -- End of API body.
296 --
297
298 -- Success Message
299 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_SUCCESS) and
300 l_return_status_full = FND_API.G_RET_STS_SUCCESS
301 THEN
302 fnd_message.Set_Name('PV', 'API_SUCCESS');
303 fnd_message.Set_Token('ROW', 'AS_ACCOUNT', TRUE);
304 fnd_msg_pub.Add;
305 END IF;
306
307 IF FND_API.To_Boolean ( p_commit )
308 THEN
309 COMMIT WORK;
310 END IF;
311
312 -- Debug Message
313 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW)
314 THEN
315 fnd_message.Set_Name('PV', 'Pvt Acc API: End');
316 fnd_msg_pub.Add;
317 END IF;
318
319 -- Standard call to get message count and if count is 1, get message info.
320 fnd_msg_pub.Count_And_Get ( p_count => x_msg_count,
321 p_data => x_msg_data
322 );
323
324 EXCEPTION
325
326 WHEN FND_API.G_EXC_ERROR THEN
327
328 ROLLBACK TO CREATE_AssignLog_PVT;
329 x_return_status := FND_API.G_RET_STS_ERROR ;
330
331 fnd_msg_pub.Count_And_Get ( p_count => x_msg_count,
332 p_data => x_msg_data
333 );
334
335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
336
337 ROLLBACK TO CREATE_AssignLog_PVT;
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
339
340 fnd_msg_pub.Count_And_Get ( p_count => x_msg_count,
341 p_data => x_msg_data
342 );
343
344 WHEN OTHERS THEN
345
346 ROLLBACK TO CREATE_AssignLog_PVT;
347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
348
349 IF fnd_msg_pub.Check_Msg_Level ( fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR )
350 THEN
351 fnd_msg_pub.Add_Exc_Msg ( G_PKG_NAME,l_api_name );
352 END IF;
353
354 fnd_msg_pub.Count_And_Get ( p_count => x_msg_count,
355 p_data => x_msg_data
356 );
357
358 END CreateAssignLog;
359
360
361 --
362 -- NAME
363 -- InsertLeadStatusLogRow
364 --
365 -- PURPOSE
366 --
367 -- NOTES
368 --
369 --
370 --
371 --
372
373 PROCEDURE InsertLeadStatusLogRow (
374 X_Rowid OUT NOCOPY ROWID ,
375 x_assignlog_ID OUT NOCOPY NUMBER ,
376 p_Last_Updated_By IN NUMBER ,
377 p_Last_Update_Date IN DATE ,
378 p_Object_Version_number IN NUMBER ,
379 p_Last_Update_Login IN NUMBER ,
380 p_Created_By IN NUMBER ,
381 p_Creation_Date IN DATE ,
382 p_lead_id IN NUMBER ,
383 p_partner_id IN NUMBER ,
384 p_status_date IN DATE ,
385 p_from_status IN VARCHAR2 ,
386 p_to_status IN VARCHAR2 ,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_msg_count OUT NOCOPY NUMBER,
389 x_msg_data OUT NOCOPY VARCHAR2) IS
390
391 CURSOR C IS
392 SELECT rowid
393 FROM pv_assignment_logs
394 WHERE assignment_ID = X_assignlog_ID;
395
396 l_assignment_id number;
397 l_api_name CONSTANT VARCHAR2(30) := 'InsertLeadStatusLogRow';
398
399 BEGIN
400
401
402 x_return_status := FND_API.G_RET_STS_SUCCESS;
403
404 select pv_assignment_logs_s.nextval
405 into l_assignment_ID
406 from sys.dual;
407
408
409 insert into pv_assignment_logs (
410 ASSIGNMENT_ID ,
411 LAST_UPDATE_DATE,
412 LAST_UPDATED_BY,
413 CREATION_DATE,
414 CREATED_BY,
415 OBJECT_VERSION_NUMBER,
416 LAST_UPDATE_LOGIN,
417 LEAD_ID,
418 PARTNER_ID,
419 STATUS_DATE,
420 FROM_LEAD_STATUS,
421 TO_LEAD_STATUS)
422 Values (
423 l_assignment_id ,
424 p_Last_Update_Date ,
425 p_Last_Updated_By ,
426 p_Creation_Date ,
427 p_Created_By ,
428 p_Object_version_number,
429 p_Last_Update_Login ,
430 p_lead_id ,
431 p_partner_id ,
432 p_status_date ,
433 p_from_status ,
434 p_to_status);
435
436 X_assignlog_ID := l_assignment_id;
437
438 OPEN C;
439 FETCH C INTO X_Rowid;
440 IF (C%NOTFOUND)
441 THEN
442 CLOSE C;
443 Raise NO_DATA_FOUND;
444 END IF;
445 CLOSE C;
446
447 EXCEPTION
448 WHEN OTHERS THEN
449
450
451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
452
453 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
454
455 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
456 p_count => x_msg_count,
457 p_data => x_msg_data);
458
459
460 END InsertLeadStatusLogRow;
461
462
463 end pv_leadlog_pvt;