[Home] [Help]
PACKAGE BODY: APPS.PV_ATTR_VALIDATION_PUB
Source
1 package body PV_ATTR_VALIDATION_PUB as
2 /* $Header: pvvatvtb.pls 120.2 2006/03/28 11:52:41 amaram noship $*/
3
4 -- --------------------------------------------------------------
5 -- Used for inserting output messages to the message table.
6 -- --------------------------------------------------------------
7 PROCEDURE Debug(
8 p_msg_string IN VARCHAR2
9 );
10
11
12 PROCEDURE attribute_validate(
13 p_api_version_number IN NUMBER,
14 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
15 p_commit IN VARCHAR2 := FND_API.g_false,
16 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
17 p_attribute_id IN NUMBER,
18 p_entity IN VARCHAR2,
19 p_entity_id IN VARCHAR2,
20 p_user_id IN VARCHAR2,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2
24 )
25 IS
26 l_api_name CONSTANT VARCHAR2(30) := 'Attribute_Validate';
27 l_api_version_number CONSTANT NUMBER := 1.0;
28
29 l_category VARCHAR2(100);
30
31 l_rs_details_tbl PV_ASSIGN_UTIL_PVT.resource_details_tbl_type := PV_ASSIGN_UTIL_PVT.resource_details_tbl_type();
32 l_username_tbl JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
33
34 l_partner_name VARCHAR2(1000);
35 l_partner_contact_name VARCHAR2(1000);
36 l_pt_contact_id VARCHAR2(1000);
37 l_attribute_name VARCHAR2(500);
38 l_email_enabled VARCHAR2(5);
39 l_vad_id NUMBER;
40
41
42
43 cursor lc_get_pt_details (pc_partner_id number) is
44 select pt.party_name party_name
45 from hz_relationships pr,
46 hz_organization_profiles op,
47 hz_parties pt
48 where pr.party_id = pc_partner_id
49 and pr.subject_table_name = 'HZ_PARTIES'
50 and pr.object_table_name = 'HZ_PARTIES'
51 and pr.status in ('A', 'I')
52 and pr.object_id = op.party_id
53 and op.internal_flag = 'Y'
54 and op.effective_end_date is null
55 and pr.subject_id = pt.party_id
56 and pt.status in ('A', 'I');
57
58
59 cursor lc_get_usr_dtails ( pc_user_id NUMBER )
60 is
61 select category, source_id
62 from jtf_rs_resource_extns extn, fnd_user usr
63 where extn.user_id = usr.user_id
64 and usr.user_id = pc_user_id;
65
66
67 cursor lc_get_pt_contact (pc_pt_contact_id NUMBER)
68 is
69 select d.party_name
70 from hz_relationships b,
71 hz_relationships c,
72 hz_organization_profiles po,
73 hz_parties d
74 where b.party_id = pc_pt_contact_id
75 and b.subject_table_name = 'HZ_PARTIES'
76 and b.object_table_name = 'HZ_PARTIES'
77 and b.directional_flag = 'F'
78 and b.relationship_code = 'EMPLOYEE_OF'
79 and b.relationship_type = 'EMPLOYMENT'
80 and (b.end_date is null or b.end_date > sysdate)
81 and b.status = 'A'
82 and b.object_id = c.subject_id
83 and c.subject_table_name = 'HZ_PARTIES'
84 and c.object_table_name = 'HZ_PARTIES'
85 and (c.end_date is null or c.end_date > sysdate)
86 and c.status = 'A'
87 and c.object_id = po.party_id
88 and d.party_id = b.subject_id
89 and po.internal_flag = 'Y'
90 and po.effective_end_date is null;
91
92
93
94
95 BEGIN
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) THEN
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102
103 -- Initialize message list if p_init_msg_list is set to TRUE.
104 IF FND_API.to_Boolean( p_init_msg_list )
105 THEN
106 fnd_msg_pub.initialize;
107 END IF;
108
109 -- Debug Message
110 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
111 dEBUG('In ' || l_api_name );
112
113 END IF;
114
115
116 IF p_entity = g_partner_entity THEN
117
118 -- Getting partner contact details
119
120
121 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
122 DEBUG('User id ' || p_user_id );
123 END IF;
124
125
126 FOR lc_user IN lc_get_usr_dtails(p_user_id )
127 LOOP
128
129 l_category := lc_user.category;
130 l_pt_contact_id := lc_user.source_id;
131
132 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
133 DEBUG('Category ' || lc_user.category );
134 DEBUG('Source ' || lc_user.source_id );
135
136 END IF;
137
138
139 END LOOP;
140
141
142 IF l_category is null AND l_pt_contact_id is null THEN
143
144 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
145 fnd_message.SET_TOKEN('TEXT' , 'No User exists for this resource id ');
146 fnd_msg_pub.ADD;
147
148 raise FND_API.G_EXC_ERROR;
149
150
151 -- If user is VENDOR then email notification will not sent out
152 -- Email notification has to sent only when the partner makes the
153 -- changes to the attribute
154
155 ELSIF l_category = 'EMPLOYEE' THEN
156 return;
157
158 ELSIF l_category = 'PARTY' THEN
159
160
161 -- Getting CM information
162
163 pv_assign_util_pvt.get_partner_info
164 (
165 p_api_version_number => p_api_version_number,
166 p_init_msg_list => p_init_msg_list,
167 p_commit => p_commit,
168 p_validation_level => p_validation_level,
169 p_mode => 'EXTERNAL',
170 p_partner_id => p_entity_id,
171 p_entity => p_entity,
172 p_entity_id => NULL,
173 p_retrieve_mode => 'CM',
174 x_rs_details_tbl => l_rs_details_tbl,
175 x_vad_id => l_vad_id,
176 x_return_status => x_return_status,
177 x_msg_count => x_msg_count,
178 x_msg_data => x_msg_data
179 );
180
181 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
182 raise FND_API.G_EXC_ERROR;
183 end if;
184
185 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
186 Debug('Size of l_rs_details_tbl: ' || l_rs_details_tbl.count);
187 END IF;
188
189 FOR lc_cursor IN lc_get_pt_details(p_entity_id)
190 LOOP
191
192 l_partner_name := lc_cursor.party_name;
193
194 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
195 Debug('Partner Name: ' || l_partner_name);
196 END IF;
197
198
199 END LOOP;
200
201
202 if l_rs_details_tbl.count = 0 then
203
204 fnd_message.SET_NAME('PV', 'PV_NO_CM_DECISION_MAKER');
205 fnd_message.SET_TOKEN('P_PARTNER_NAME' , l_partner_name);
206 fnd_msg_pub.ADD;
207
208 raise FND_API.G_EXC_ERROR;
209
210 else
211
212 l_username_tbl.extend(l_rs_details_tbl.count);
213
214 for i in 1 .. l_rs_details_tbl.count
215 loop
216
217 l_username_tbl(i) := l_rs_details_tbl(i).user_name;
218
219 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
220 Debug('User Name: ' || l_username_tbl(i));
221 END IF;
222
223
224 end loop;
225
226 end if;
227
228
229
230 FOR lc_pt_contact IN lc_get_pt_contact(l_pt_contact_id)
231 LOOP
232
233 l_partner_contact_name := lc_pt_contact.party_name;
234 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
235 Debug('Partner Contact Name: ' || l_partner_contact_name);
236 END IF;
237
238
239 END LOOP;
240
241 FOR lc_cur IN (select name from pv_attributes_vl where attribute_id = p_attribute_id)
242 LOOP
243
244 l_attribute_name := lc_cur.name;
245
246 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
247 Debug('Attribute Name: ' || l_attribute_name);
248 END IF;
249
250
251 END LOOP;
252
253
254 StartWorkflow
255 (
256 p_api_version_number => p_api_version_number,
257 p_init_msg_list => p_init_msg_list,
258 p_commit => p_commit,
259 p_validation_level => p_validation_level,
260 p_user_name_tbl => l_username_tbl,
261 p_attribute_id => p_attribute_id,
262 p_attribute_name => l_attribute_name,
263 p_partner_name => l_partner_name,
264 p_pt_contact_name => l_partner_contact_name,
265 x_return_status => x_return_status,
266 x_msg_count => x_msg_count,
267 x_msg_data => x_msg_data
268 );
269
270
271 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
272 RAISE FND_API.G_EXC_ERROR;
273 END IF;
274
275 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
276 Debug( 'Email is sent out successfully');
277 END IF;
278
279 END IF;
280
281 END IF;
282
283 IF FND_API.To_Boolean ( p_commit ) THEN
284 COMMIT WORK;
285 END IF;
286
287 -- Standard call to get message count and if count is 1, get message info.
288 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
289 p_count => x_msg_count,
290 p_data => x_msg_data);
291
292 EXCEPTION
293
294 WHEN FND_API.G_EXC_ERROR THEN
295 x_return_status := FND_API.G_RET_STS_ERROR ;
296 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
297 p_count => x_msg_count,
298 p_data => x_msg_data);
299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
301 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
302 p_count => x_msg_count,
303 p_data => x_msg_data);
304 WHEN OTHERS THEN
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
307 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
308 p_count => x_msg_count,
309 p_data => x_msg_data);
310 END;
311
312
313 procedure StartWorkflow
314 (
315 p_api_version_number IN NUMBER,
316 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
317 p_commit IN VARCHAR2 := FND_API.G_FALSE,
318 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
319 p_user_name_tbl IN JTF_VARCHAR2_TABLE_1000,
320 p_attribute_id IN VARCHAR2,
321 p_attribute_name IN VARCHAR2,
322 p_partner_name IN VARCHAR2,
323 p_pt_contact_name IN VARCHAR2,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2
327 )
328 is
329 l_api_name CONSTANT VARCHAR2(30) := 'StartWorkflow';
330 l_api_version_number CONSTANT NUMBER := 1.0;
331
332 l_send_respond_url VARCHAR2(500);
333 l_email_enabled VARCHAR2(5);
334 l_itemKey VARCHAR2(100);
335 l_itemType VARCHAR2(10) := g_wf_itemtype_notify;
336 l_role_list wf_directory.usertable;
337 l_adhoc_role VARCHAR2(1000);
338
339
340 begin
341 -- Standard call to check for call compatibility.
342 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
343 p_api_version_number,
344 l_api_name,
345 G_PKG_NAME) THEN
346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
347 END IF;
348
349 -- Initialize message list if p_init_msg_list is set to TRUE.
350 IF FND_API.to_Boolean( p_init_msg_list )
351 THEN
352 fnd_msg_pub.initialize;
353 END IF;
354
355 -- Debug Message
356 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
357 dEBUG('In ' || l_api_name );
358
359 END IF;
360
361 x_return_status := FND_API.G_RET_STS_SUCCESS ;
362
363 -- check the profile value and return if the value is not Y
364
365 l_email_enabled := nvl(fnd_profile.value('PV_EMAIL_NOTIFICATION_FLAG'), 'Y');
366
367 if (l_email_enabled <> 'Y') then
368 return;
369 end if;
370
371 debug('Email Enabled '|| l_email_enabled);
372
373 SELECT PV_LEAD_WORKFLOWS_S.nextval
374 INTO l_itemKey
375 FROM dual;
376
377 FOR i in 1 .. p_user_name_tbl.count
378 LOOP
379
380 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
381 dEBUG( 'In Loop of p_user_name_tbl ');
382 END IF;
383
384 l_role_list(i) := p_user_name_tbl(i);
385
386 END LOOP;
387
388 IF l_role_list.count > 0 then
389 l_adhoc_role := 'PV_' || l_itemKey || '_' || '0';
390
391 -- Debug Message
392
393 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
394 Debug('Creating role : '|| l_adhoc_role || ' with members:--' );
395 END IF;
396
397 FOR i in 1 .. l_role_list.count
398 LOOP
399
400
401
402 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
403 dEBUG( l_role_list(i) );
404 END IF;
405
406 END LOOP;
407
408
409 wf_directory.CreateAdHocRole2(role_name => l_adhoc_role,
410 role_display_name => l_adhoc_role,
411 role_users => l_role_list);
412
413
414 END IF;
415
416
417 IF l_role_list.count < 1
418 THEN
419 return;
420
421 ELSE
422
423
424
425 -- Once the parameters for workflow is validated, start the workflow
426 wf_engine.CreateProcess (ItemType => l_itemType,
427 ItemKey => l_itemKey,
428 process => g_wf_pcs_notify_cm);
429
430 wf_engine.SetItemUserKey (ItemType => l_itemType,
431 ItemKey => l_itemKey,
432 userKey => l_itemkey);
433
434 wf_engine.SetItemAttrText (ItemType => l_itemType,
435 ItemKey => l_itemKey,
436 aname => g_wf_attr_cm_notify_role,
437 avalue => l_adhoc_role);
438
439 wf_engine.SetItemAttrText (ItemType => l_itemType,
440 ItemKey => l_itemKey,
441 aname => g_wf_attr_attribute_name,
442 avalue => p_attribute_name);
443
444
445 wf_engine.SetItemAttrText (ItemType => l_itemType,
446 ItemKey => l_itemKey,
447 aname => g_wf_attr_partner_name,
448 avalue => p_partner_name);
449
450 wf_engine.SetItemAttrText (ItemType => l_itemType,
451 ItemKey => l_itemKey,
452 aname => g_wf_attr_prtnr_cont_name,
453 avalue => p_pt_contact_name);
454
455 l_send_respond_url := fnd_profile.value('PV_WORKFLOW_RESPOND_SELF_SERVICE_URL');
456
457 wf_engine.SetItemAttrText ( ItemType => l_itemType,
458 ItemKey => l_itemKey,
459 aname => g_wf_attr_send_url,
460 avalue => l_send_respond_url);
461
462
463 wf_engine.StartProcess (ItemType => l_itemType,
464 ItemKey => l_itemKey);
465
466 -- Call the following procedure to see whether workflow was able to send notification successfully.
467 PV_ASSIGN_UTIL_PVT.checkforErrors
468 (p_api_version_number => 1.0
469 ,p_init_msg_list => FND_API.G_FALSE
470 ,p_commit => FND_API.G_FALSE
471 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
472 ,p_itemtype => l_itemType
473 ,p_itemkey => l_itemKey
474 ,x_msg_count => x_msg_count
475 ,x_msg_data => x_msg_data
476 ,x_return_status => x_return_status);
477
478 -- Check the x_return_status. If its not successful throw an exception.
479 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
480 raise FND_API.G_EXC_ERROR;
481 end if;
482
483 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
484 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
485 fnd_message.Set_token('TEXT', 'After Checkforerror');
486 fnd_msg_pub.Add;
487 END IF;
488 END IF;
489
490 IF FND_API.To_Boolean ( p_commit ) THEN
491 COMMIT WORK;
492 END IF;
493
494 -- Standard call to get message count and if count is 1, get message info.
495 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
496 p_count => x_msg_count,
497 p_data => x_msg_data);
498 EXCEPTION
499
500 WHEN FND_API.G_EXC_ERROR THEN
501 x_return_status := FND_API.G_RET_STS_ERROR ;
502 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
503 p_count => x_msg_count,
504 p_data => x_msg_data);
505 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
507 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
508 p_count => x_msg_count,
509 p_data => x_msg_data);
510 WHEN OTHERS THEN
511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
512 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
513 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
514 p_count => x_msg_count,
515 p_data => x_msg_data);
516 end StartWorkflow;
517
518
519 PROCEDURE Debug(
520 p_msg_string IN VARCHAR2
521 )
522 IS
523
524 BEGIN
525 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
526 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
527 FND_MSG_PUB.Add;
528 END Debug;
529
530 END PV_ATTR_VALIDATION_PUB;