[Home] [Help]
PACKAGE BODY: APPS.PVX_PRTNR_PRFLS_PVT
Source
1 PACKAGE BODY PVX_PRTNR_PRFLS_PVT AS
2 /* $Header: pvxvppfb.pls 115.19 2003/08/07 05:20:43 rdsharma ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='PVX_PRTNR_PRFLS_PVT';
6
7 ---------------------------------------------------------------------
8 -- PROCEDURE
9 -- Create_Prtnr_Prfls
10 --
11 -- PURPOSE
12 -- Create a new partner profile record
13 --
14 -- PARAMETERS
15 -- p_prtnr_prfls_rec: the new record to be inserted
16 -- x_partner_profile_id: return the partner_profile_id of the new record.
17 --
18 -- NOTES
19 -- 1. object_version_number will be set to 1.
20 -- 2. If partner_profile_id is passed in, the uniqueness will be checked.
21 -- Raise exception in case of duplicates.
22 -- 3. If partner_profile_id is not passed in, generate a unique one from
23 -- the sequence.
24 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
25 -- Raise exception for invalid flag.
26 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
27 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
28 ---------------------------------------------------------------------
29 PROCEDURE Create_Prtnr_Prfls(
30 p_api_version IN NUMBER
31 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
32 ,p_commit IN VARCHAR2 := FND_API.g_false
33 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
34
35 ,x_return_status OUT NOCOPY VARCHAR2
36 ,x_msg_count OUT NOCOPY NUMBER
37 ,x_msg_data OUT NOCOPY VARCHAR2
38
39 ,p_prtnr_prfls_rec IN prtnr_prfls_rec_type
40 ,x_partner_profile_id OUT NOCOPY NUMBER
41 )
42 IS
43
44 l_api_version CONSTANT NUMBER := 1.0;
45 l_api_name CONSTANT VARCHAR2(30) := 'Create_Prtnr_Prfls';
46 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
47
48 l_return_status VARCHAR2(1);
49 l_prtnr_prfls_rec prtnr_prfls_rec_type := p_prtnr_prfls_rec;
50
51 l_object_version_number NUMBER := 1;
52
53 l_uniqueness_check VARCHAR2(10);
54 l_status VARCHAR2(1);
55
56
57 -- Cursor to get the sequence for prtnr_prfls
58 CURSOR c_prtnr_prfls_seq IS
59 SELECT PV_partner_profiles_S.NEXTVAL
60 FROM DUAL;
61
62 -- Cursor to validate the uniqueness
63 CURSOR c_count(cv_partner_profile_id IN NUMBER) IS
64 SELECT 'ANYTHING'
65 FROM PV_partner_profiles
66 WHERE partner_profile_id = cv_partner_profile_id;
67
68
69 BEGIN
70
71 --------------------- initialize -----------------------
72 SAVEPOINT Create_Prtnr_Prfls;
73
74 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
75 PVX_Utility_PVT.debug_message(l_full_name||': start');
76 END IF;
77
78 --dbms_output.put_line(l_full_name||': start');
79
80 IF FND_API.to_boolean(p_init_msg_list) THEN
81 FND_MSG_PUB.initialize;
82 END IF;
83
84 IF NOT FND_API.compatible_api_call(
85 l_api_version,
86 p_api_version,
87 l_api_name,
88 g_pkg_name
89 ) THEN
90 RAISE FND_API.g_exc_unexpected_error;
91 END IF;
92
93 x_return_status := FND_API.g_ret_sts_success;
94
95 ----------------------- validate -----------------------
96 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
97 PVX_Utility_PVT.debug_message(l_full_name ||': validate');
98 END IF;
99
100 --dbms_output.put_line(l_full_name||': validate');
101 Validate_Prtnr_Prfls(
102 p_api_version => l_api_version,
103 p_init_msg_list => p_init_msg_list,
104 p_validation_level => p_validation_level,
105 x_return_status => l_return_status,
106 x_msg_count => x_msg_count,
107 x_msg_data => x_msg_data,
108 p_prtnr_prfls_rec => l_prtnr_prfls_rec
109 );
110
111
112 IF l_return_status = FND_API.g_ret_sts_error THEN
113 RAISE FND_API.g_exc_error;
114 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
115 RAISE FND_API.g_exc_unexpected_error;
116 END IF;
117
118
119 --dbms_output.put_line(l_full_name||': back validate');
120
121 -------------------------- insert --------------------------
122 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
123 PVX_Utility_PVT.debug_message(l_full_name ||': insert');
124 END IF;
125
126
127 IF l_prtnr_prfls_rec.partner_profile_id IS NULL THEN
128 LOOP
129 -- Get the identifier
130 OPEN c_prtnr_prfls_seq;
131 FETCH c_prtnr_prfls_seq INTO l_prtnr_prfls_rec.partner_profile_id;
132 CLOSE c_prtnr_prfls_seq;
133
134 -- Check the uniqueness of the identifier
135 OPEN c_count(l_prtnr_prfls_rec.partner_profile_id);
136 FETCH c_count INTO l_uniqueness_check;
137 -- Exit when the identifier uniqueness is established
138 EXIT WHEN c_count%ROWCOUNT = 0;
139 CLOSE c_count;
140 END LOOP;
141 END IF;
142
143 IF c_count%ISOPEN THEN
144 CLOSE c_count;
145 END IF;
146
147 IF l_prtnr_prfls_rec.status IS NULL OR l_prtnr_prfls_rec.status = FND_API.G_MISS_CHAR THEN
148 Determine_Partner_Status(l_prtnr_prfls_rec, l_status);
149 ELSE
150 l_status := l_prtnr_prfls_rec.status;
151 END IF;
152
153 --dbms_output.put_line(l_full_name||': start insert');
154 INSERT INTO PV_partner_profiles (
155 partner_profile_id
156 ,last_update_date
157 ,last_updated_by
158 ,creation_date
159 ,created_by
160 ,last_update_login
161 ,partner_id
162 ,target_revenue_amt
163 ,actual_revenue_amt
164 ,target_revenue_pct
165 ,actual_revenue_pct
166 ,orig_system_reference
167 ,orig_system_type
168 ,capacity_size
169 ,capacity_amount
170 ,auto_match_allowed_flag
171 ,purchase_method
172 ,cm_id
173 ,ph_support_rep
174 --,security_group_id
175 ,object_version_number
176 ,lead_sharing_status
177 ,lead_share_appr_flag
178 ,partner_relationship_id
179 ,partner_level
180 ,preferred_vad_id
181 ,partner_group_id
182 ,partner_resource_id
183 ,partner_group_number
184 ,partner_resource_number
185 ,sales_partner_flag
186 ,indirectly_managed_flag
187 ,channel_marketing_manager
188 ,related_partner_id
189 ,max_users
190 ,partner_party_id
191 ,status
192 )
193 VALUES (
194 l_prtnr_prfls_rec.partner_profile_id
195 ,SYSDATE -- LAST_UPDATE_DATE
196 ,NVL(FND_GLOBAL.user_id,-1) -- LAST_UPDATED_BY
197 ,SYSDATE -- CREATION_DATE
198 ,NVL(FND_GLOBAL.user_id,-1) -- CREATED_BY
199 ,NVL(FND_GLOBAL.conc_login_id,-1) -- LAST_UPDATE_LOGIN
200 ,l_prtnr_prfls_rec.partner_id
201 ,l_prtnr_prfls_rec.target_revenue_amt
202 ,l_prtnr_prfls_rec.actual_revenue_amt
203 ,l_prtnr_prfls_rec.target_revenue_pct
204 ,l_prtnr_prfls_rec.actual_revenue_pct
205 ,l_prtnr_prfls_rec.orig_system_reference
206 ,l_prtnr_prfls_rec.orig_system_type
207 ,l_prtnr_prfls_rec.capacity_size
208 ,l_prtnr_prfls_rec.capacity_amount
209 ,l_prtnr_prfls_rec.auto_match_allowed_flag
210 ,l_prtnr_prfls_rec.purchase_method
211 ,l_prtnr_prfls_rec.cm_id
212 ,l_prtnr_prfls_rec.ph_support_rep
213 --,l_prtnr_prfls_rec.security_group_id
214 ,l_object_version_number -- object_version_number
215 ,l_prtnr_prfls_rec.lead_sharing_status
216 ,l_prtnr_prfls_rec.lead_share_appr_flag
217 ,l_prtnr_prfls_rec.partner_relationship_id
218 ,l_prtnr_prfls_rec.partner_level
219 ,l_prtnr_prfls_rec.preferred_vad_id
220 ,l_prtnr_prfls_rec.partner_group_id
221 ,l_prtnr_prfls_rec.partner_resource_id
222 ,l_prtnr_prfls_rec.partner_group_number
223 ,l_prtnr_prfls_rec.partner_resource_number
224 ,l_prtnr_prfls_rec.sales_partner_flag
225 ,l_prtnr_prfls_rec.indirectly_managed_flag
226 ,l_prtnr_prfls_rec.channel_marketing_manager
227 ,l_prtnr_prfls_rec.related_partner_id
228 ,l_prtnr_prfls_rec.max_users
229 ,l_prtnr_prfls_rec.partner_party_id
230 ,l_status
231 );
232
233 ------------------------- finish -------------------------------
234 x_partner_profile_id := l_prtnr_prfls_rec.partner_profile_id;
235
236 IF l_return_status = FND_API.g_ret_sts_error THEN
237 RAISE FND_API.g_exc_error;
238 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
239 RAISE FND_API.g_exc_unexpected_error;
240 END IF;
241
242
243 -- Check for commit
244 IF FND_API.to_boolean(p_commit) THEN
245 COMMIT;
246 END IF;
247
248 FND_MSG_PUB.count_and_get(
249 p_encoded => FND_API.g_false,
250 p_count => x_msg_count,
251 p_data => x_msg_data
252 );
253
254 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
255 PVX_Utility_PVT.debug_message(l_full_name ||': end');
256 END IF;
257
258 EXCEPTION
259
260 WHEN FND_API.g_exc_error THEN
261 ROLLBACK TO Create_Prtnr_Prfls;
262 x_return_status := FND_API.g_ret_sts_error;
263 FND_MSG_PUB.count_and_get (
264 p_encoded => FND_API.g_false
265 ,p_count => x_msg_count
266 ,p_data => x_msg_data
267 );
268
269 WHEN FND_API.g_exc_unexpected_error THEN
270 ROLLBACK TO Create_Prtnr_Prfls;
271 x_return_status := FND_API.g_ret_sts_unexp_error ;
272 FND_MSG_PUB.count_and_get (
273 p_encoded => FND_API.g_false
274 ,p_count => x_msg_count
275 ,p_data => x_msg_data
276 );
277
278
279 WHEN OTHERS THEN
280 ROLLBACK TO Create_Prtnr_Prfls;
281 x_return_status := FND_API.g_ret_sts_unexp_error ;
282
283 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
284 THEN
285 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
286 END IF;
287
288 FND_MSG_PUB.count_and_get(
289 p_encoded => FND_API.g_false
290 ,p_count => x_msg_count
291 ,p_data => x_msg_data
292 );
293
294 END Create_Prtnr_Prfls;
295
296
297 ---------------------------------------------------------------
298 -- PROCEDURE
299 -- Delete_Prtnr_Prfls
300 --
301 ---------------------------------------------------------------
302 PROCEDURE Delete_Prtnr_Prfls(
303 p_api_version IN NUMBER
304 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
305 ,p_commit IN VARCHAR2 := FND_API.g_false
306
307 ,x_return_status OUT NOCOPY VARCHAR2
308 ,x_msg_count OUT NOCOPY NUMBER
309 ,x_msg_data OUT NOCOPY VARCHAR2
310
311 ,p_partner_profile_id IN NUMBER
312 ,p_object_version IN NUMBER
313 )
314 IS
315
316 l_api_version CONSTANT NUMBER := 1.0;
317 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Prtnr_Prflss';
318 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
319
320 BEGIN
321
322 --------------------- initialize -----------------------
323 SAVEPOINT Delete_Prtnr_Prfls;
324
325 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
326 PVX_Utility_PVT.debug_message(l_full_name||': start');
327 END IF;
328
329 IF FND_API.to_boolean(p_init_msg_list) THEN
330 FND_MSG_PUB.initialize;
331 END IF;
332
333 IF NOT FND_API.compatible_api_call(
334 l_api_version,
335 p_api_version,
336 l_api_name,
337 g_pkg_name
338 ) THEN
339 RAISE FND_API.g_exc_unexpected_error;
340 END IF;
341
342 x_return_status := FND_API.G_RET_STS_SUCCESS;
343
344 ------------------------ delete ------------------------
345 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
346 PVX_Utility_PVT.debug_message(l_full_name ||': delete');
347 END IF;
348
349 DELETE FROM PV_PARTNER_PROFILES
350 WHERE partner_profile_id = p_partner_profile_id
351 AND object_version_number = p_object_version;
352
353 IF (SQL%NOTFOUND) THEN
354 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
355 THEN
356 FND_MESSAGE.set_name('PV', 'PV_NO_RECORD_FOUND');
357 FND_MSG_PUB.add;
358 END IF;
359 RAISE FND_API.g_exc_error;
360 END IF;
361
362 -------------------- finish --------------------------
363 IF FND_API.to_boolean(p_commit) THEN
364 COMMIT;
365 END IF;
366
367 FND_MSG_PUB.count_and_get(
368 p_encoded => FND_API.g_false,
369 p_count => x_msg_count,
370 p_data => x_msg_data
371 );
372
373 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
374 PVX_Utility_PVT.debug_message(l_full_name ||': end');
375 END IF;
376
377 EXCEPTION
378
379 WHEN FND_API.g_exc_error THEN
380 ROLLBACK TO Delete_Prtnr_Prfls;
381 x_return_status := FND_API.g_ret_sts_error;
382 FND_MSG_PUB.count_and_get(
383 p_encoded => FND_API.g_false,
384 p_count => x_msg_count,
385 p_data => x_msg_data
386 );
387
388 WHEN FND_API.g_exc_unexpected_error THEN
389 ROLLBACK TO Delete_Prtnr_Prfls;
390 x_return_status := FND_API.g_ret_sts_unexp_error ;
391 FND_MSG_PUB.count_and_get(
392 p_encoded => FND_API.g_false,
393 p_count => x_msg_count,
394 p_data => x_msg_data
395 );
396
397 WHEN OTHERS THEN
398 ROLLBACK TO Delete_Prtnr_Prfls;
399 x_return_status := FND_API.g_ret_sts_unexp_error ;
400
401 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
402 THEN
403 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
404 END IF;
405
406 FND_MSG_PUB.count_and_get(
407 p_encoded => FND_API.g_false,
408 p_count => x_msg_count,
409 p_data => x_msg_data
410 );
411
412 END Delete_Prtnr_Prfls;
413
414
415 -------------------------------------------------------------------
416 -- PROCEDURE
417 -- Lock_Prtnr_Prfls
418 --
419 --------------------------------------------------------------------
420 PROCEDURE Lock_Prtnr_Prfls(
421 p_api_version IN NUMBER
422 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
423
424 ,x_return_status OUT NOCOPY VARCHAR2
425 ,x_msg_count OUT NOCOPY NUMBER
426 ,x_msg_data OUT NOCOPY VARCHAR2
427
428 ,p_partner_profile_id IN NUMBER
429 ,p_object_version IN NUMBER
430 )
431 IS
432
433 l_api_version CONSTANT NUMBER := 1.0;
434 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Prtnr_Prfls';
435 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
436
437 l_partner_profile_id NUMBER;
438
439 CURSOR c_prtnr_prfls IS
440 SELECT partner_profile_id
441 FROM PV_PARTNER_PROFILES
442 WHERE partner_profile_id = p_partner_profile_id
443 AND object_version_number = p_object_version
444 FOR UPDATE OF partner_profile_id NOWAIT;
445
446 BEGIN
447
448 -------------------- initialize ------------------------
449 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
450 PVX_Utility_PVT.debug_message(l_full_name||': start');
451 END IF;
452
453 IF FND_API.to_boolean(p_init_msg_list) THEN
454 FND_MSG_PUB.initialize;
455 END IF;
456
457 IF NOT FND_API.compatible_api_call(
458 l_api_version,
459 p_api_version,
460 l_api_name,
461 g_pkg_name
462 ) THEN
463 RAISE FND_API.g_exc_unexpected_error;
464 END IF;
465
466 x_return_status := FND_API.G_RET_STS_SUCCESS;
467
468 ------------------------ lock -------------------------
469 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
470 PVX_Utility_PVT.debug_message(l_full_name||': lock');
471 END IF;
472
473 OPEN c_prtnr_prfls;
474 FETCH c_prtnr_prfls INTO l_partner_profile_id;
475 IF (c_prtnr_prfls%NOTFOUND) THEN
476 CLOSE c_prtnr_prfls;
477 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
478 FND_MESSAGE.set_name('PV', 'PV_NO_RECORD_FOUND');
479 FND_MSG_PUB.add;
480 END IF;
481 RAISE FND_API.g_exc_error;
482 END IF;
483 CLOSE c_prtnr_prfls;
484
485
486 -------------------- finish --------------------------
487 FND_MSG_PUB.count_and_get(
488 p_encoded => FND_API.g_false,
489 p_count => x_msg_count,
490 p_data => x_msg_data
491 );
492
493 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
494 PVX_Utility_PVT.debug_message(l_full_name ||': end');
495 END IF;
496
497 EXCEPTION
498
499 WHEN PVX_Utility_PVT.resource_locked THEN
500 x_return_status := FND_API.g_ret_sts_error;
501 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
502 FND_MESSAGE.set_name('PV', 'PV_RESOURCE_LOCKED');
503 FND_MSG_PUB.add;
504 END IF;
505
506 FND_MSG_PUB.count_and_get(
507 p_encoded => FND_API.g_false,
508 p_count => x_msg_count,
509 p_data => x_msg_data
510 );
511
512 WHEN FND_API.g_exc_error THEN
513 x_return_status := FND_API.g_ret_sts_error;
514 FND_MSG_PUB.count_and_get(
515 p_encoded => FND_API.g_false,
516 p_count => x_msg_count,
517 p_data => x_msg_data
518 );
519
520 WHEN FND_API.g_exc_unexpected_error THEN
521 x_return_status := FND_API.g_ret_sts_unexp_error ;
522 FND_MSG_PUB.count_and_get(
523 p_encoded => FND_API.g_false,
524 p_count => x_msg_count,
525 p_data => x_msg_data
526 );
527
528 WHEN OTHERS THEN
529 x_return_status := FND_API.g_ret_sts_unexp_error ;
530 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
531 THEN
532 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
533 END IF;
534
535 FND_MSG_PUB.count_and_get(
536 p_encoded => FND_API.g_false,
537 p_count => x_msg_count,
538 p_data => x_msg_data
539 );
540
541 END Lock_Prtnr_Prfls;
542
543
544 ---------------------------------------------------------------------
545 -- PROCEDURE
546 -- Update_Prtnr_Prfls
547 ----------------------------------------------------------------------
548 PROCEDURE Update_Prtnr_Prfls(
549 p_api_version IN NUMBER
550 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
551 ,p_commit IN VARCHAR2 := FND_API.g_false
552 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
553
554 ,x_return_status OUT NOCOPY VARCHAR2
555 ,x_msg_count OUT NOCOPY NUMBER
556 ,x_msg_data OUT NOCOPY VARCHAR2
557 ,p_prtnr_prfls_rec IN prtnr_prfls_rec_type
558 )
559 IS
560
561 l_api_version CONSTANT NUMBER := 1.0;
562 l_api_name CONSTANT VARCHAR2(30) := 'Update_Prtnr_Prfls';
563 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
564
565 l_prtnr_prfls_rec prtnr_prfls_rec_type;
566 l_return_status VARCHAR2(1);
567 l_mode VARCHAR2(30) := 'UPDATE';
568
569 -- Define the record for the related partner_id
570 rel_prtnr_prfls_rec prtnr_prfls_rec_type;
571
572 l_status VARCHAR2(1);
573 l_old_status VARCHAR2(1);
574
575 -- Cursor to select old status value
576
577 CURSOR c_prtnr_status IS
578 SELECT NVL(status, 'A') partner_status
579 FROM pv_partner_profiles
580 WHERE partner_profile_id = p_prtnr_prfls_rec.partner_profile_id;
581
582 -- Cursor : cur_related_record
583 CURSOR cur_related_record (var_partner_id IN NUMBER) IS
584 SELECT partner_id, partner_profile_id, object_version_number
585 FROM pv_partner_profiles
586 WHERE related_partner_id = var_partner_id;
587 -- Cursor record
588 currec_related_record cur_related_record%ROWTYPE;
589
590 l_list WF_PARAMETER_LIST_T;
591 l_param WF_PARAMETER_T;
592 l_key VARCHAR2(240);
593 l_event_name VARCHAR2(240) := 'oracle.apps.pv.partner.Profile.updateStatus';
594
595 BEGIN
596 --dbms_output.put_line('entered Update');
597 -------------------- initialize -------------------------
598 SAVEPOINT Update_Prtnr_Prfls;
599
600 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
601 PVX_Utility_PVT.debug_message(l_full_name||': start');
602 END IF;
603
604 IF FND_API.to_boolean(p_init_msg_list) THEN
605 FND_MSG_PUB.initialize;
606 END IF;
607
608 IF NOT FND_API.compatible_api_call(
609 l_api_version,
610 p_api_version,
611 l_api_name,
612 g_pkg_name
613 ) THEN
614 RAISE FND_API.g_exc_unexpected_error;
615 END IF;
616
617 x_return_status := FND_API.G_RET_STS_SUCCESS;
618
619 --dbms_output.put_line('Before Validate');
620
621 ----------------------- validate ----------------------
622 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
623 PVX_Utility_PVT.debug_message(l_full_name ||': validate');
624 END IF;
625
626 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
627 Check_Prtnr_Prfls_Items(
628 p_prtnr_prfls_rec => p_prtnr_prfls_rec,
629 p_validation_mode => JTF_PLSQL_API.g_update,
630 x_return_status => l_return_status
631 );
632
633 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
634 RAISE FND_API.g_exc_unexpected_error;
635 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
636 RAISE FND_API.g_exc_error;
637 END IF;
638 END IF;
639
640 -- replace g_miss_char/num/date with current column values
641 Complete_Prtnr_Prfls_Rec(p_prtnr_prfls_rec, l_prtnr_prfls_rec);
642
643 --dbms_output.put_line('p_prtnr_prfls_rec.cm_id = '||TO_CHAR(p_prtnr_prfls_rec.cm_id));
644 --dbms_output.put_line('l_prtnr_prfls_rec.cm_id = '||TO_CHAR(l_prtnr_prfls_rec.cm_id));
645
646 --dbms_output.put_line('Got complete record ');
647
648 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
649 Check_Prtnr_Prfls_Record(
650 p_prtnr_prfls_rec => p_prtnr_prfls_rec,
651 p_complete_rec => l_prtnr_prfls_rec,
652 p_mode => l_mode,
653 x_return_status => l_return_status
654 );
655
656 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
657 RAISE FND_API.g_exc_unexpected_error;
658 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
659 RAISE FND_API.g_exc_error;
660 END IF;
661 END IF;
662
663
664 -------------------------- update --------------------
665 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
666 PVX_Utility_PVT.debug_message(l_full_name ||': update');
667 END IF;
668
669 --dbms_output.put_line('partner_profile_id = '||TO_CHAR(l_prtnr_prfls_rec.partner_profile_id));
670 --dbms_output.put_line('object_version_number = '||TO_CHAR(l_prtnr_prfls_rec.object_version_number));
671
672
673 IF l_prtnr_prfls_rec.status IS NULL OR l_prtnr_prfls_rec.status = FND_API.G_MISS_CHAR THEN
674 Determine_Partner_Status(l_prtnr_prfls_rec, l_status);
675 ELSE
676 l_status := l_prtnr_prfls_rec.status;
677 END IF;
678
679 OPEN c_prtnr_status;
680 FETCH c_prtnr_status INTO l_old_status;
681 CLOSE c_prtnr_status;
682
683 IF l_old_status <> l_status THEN
684
685 -- Raise Business Event
686 --Get the item key
687 l_key := PVX_EVENT_PKG.item_key( l_event_name );
688
689 -- initialization of object variables
690 l_list := WF_PARAMETER_LIST_T();
691
692 -- Add Context values to the list
693 pvx_event_pkg.AddParamEnvToList(l_list);
694
695 l_param := WF_PARAMETER_T( NULL, NULL );
696
697 -- fill the parameters list
698 l_list.extend;
699 l_param.SetName( 'PARTNER_ID' );
700 l_param.SetValue( l_prtnr_prfls_rec.PARTNER_ID );
701 l_list(l_list.last) := l_param;
702
703 l_list.extend;
704 l_param.SetName( 'OLD_PARTNER_STATUS' );
705 l_param.SetValue( l_old_status );
706 l_list(l_list.last) := l_param;
707
708 l_list.extend;
709 l_param.SetName( 'NEW_PARTNER_STATUS' );
710 l_param.SetValue( l_status );
711 l_list(l_list.last) := l_param;
712
713 -- Raise Event
714 PVX_EVENT_PKG.Raise_Event(
715 p_event_name => l_event_name,
716 p_event_key => l_key,
717 p_parameters => l_list );
718
719 l_list.DELETE;
720
721 END IF;
722
723 UPDATE PV_PARTNER_PROFILES SET
724 last_update_date = SYSDATE
725 ,last_updated_by = NVL(FND_GLOBAL.user_id,-1)
726 ,last_update_login = NVL(FND_GLOBAL.conc_login_id,-1)
727 ,PARTNER_ID = l_prtnr_prfls_rec.PARTNER_ID
728 ,TARGET_REVENUE_AMT = l_prtnr_prfls_rec.TARGET_REVENUE_AMT
729 ,ACTUAL_REVENUE_AMT = l_prtnr_prfls_rec.ACTUAL_REVENUE_AMT
730 ,TARGET_REVENUE_PCT = l_prtnr_prfls_rec.TARGET_REVENUE_PCT
731 ,ACTUAL_REVENUE_PCT = l_prtnr_prfls_rec.ACTUAL_REVENUE_PCT
732 ,ORIG_SYSTEM_REFERENCE = l_prtnr_prfls_rec.ORIG_SYSTEM_REFERENCE
733 ,ORIG_SYSTEM_TYPE = l_prtnr_prfls_rec.ORIG_SYSTEM_TYPE
734 ,CAPACITY_SIZE = l_prtnr_prfls_rec.CAPACITY_SIZE
735 ,CAPACITY_AMOUNT = l_prtnr_prfls_rec.CAPACITY_AMOUNT
736 ,AUTO_MATCH_ALLOWED_FLAG = l_prtnr_prfls_rec.AUTO_MATCH_ALLOWED_FLAG
737 ,PURCHASE_METHOD = l_prtnr_prfls_rec.PURCHASE_METHOD
738 ,CM_ID = l_prtnr_prfls_rec.CM_ID
739 ,PH_SUPPORT_REP = l_prtnr_prfls_rec.PH_SUPPORT_REP
740 --,security_group_id = l_prtnr_prfls_rec.security_group_id
741 ,object_version_number = l_prtnr_prfls_rec.object_version_number + 1
742 ,lead_sharing_status = l_prtnr_prfls_rec.lead_sharing_status
743 ,lead_share_appr_flag = l_prtnr_prfls_rec.lead_share_appr_flag
744 ,partner_relationship_id = l_prtnr_prfls_rec.partner_relationship_id
745 ,partner_level = l_prtnr_prfls_rec.partner_level
746 ,preferred_vad_id = l_prtnr_prfls_rec.preferred_vad_id
747 ,partner_group_id = l_prtnr_prfls_rec.partner_group_id
748 ,partner_resource_id = l_prtnr_prfls_rec.partner_resource_id
749 ,partner_group_number = l_prtnr_prfls_rec.partner_group_number
750 ,partner_resource_number = l_prtnr_prfls_rec.partner_resource_number
751 ,sales_partner_flag = l_prtnr_prfls_rec.sales_partner_flag
752 ,indirectly_managed_flag = l_prtnr_prfls_rec.indirectly_managed_flag
753 ,channel_marketing_manager = l_prtnr_prfls_rec.channel_marketing_manager
754 ,related_partner_id = l_prtnr_prfls_rec.related_partner_id
755 ,max_users = l_prtnr_prfls_rec.max_users
756 ,partner_party_id = l_prtnr_prfls_rec.partner_party_id
757 ,status = l_status
758 WHERE partner_profile_id = l_prtnr_prfls_rec.partner_profile_id
759 AND object_version_number = l_prtnr_prfls_rec.object_version_number;
760
761 IF (SQL%NOTFOUND) THEN
762 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
763 FND_MESSAGE.set_name('PV', 'PV_NO_RECORD_FOUND');
764 FND_MSG_PUB.add;
765 END IF;
766 RAISE FND_API.g_exc_error;
767 END IF;
768
769 -- Call the PVX_PRTNR_PRFLS_PVT.Update_Prtnr_Prfls recursively
770 -- if partner_id also happens to be a related_partner_id for some records
771
772 -- Open the cursor cur_related_record
773 OPEN cur_related_record(l_prtnr_prfls_rec.partner_id);
774 FETCH cur_related_record INTO currec_related_record;
775 CLOSE cur_related_record;
776
777 IF (currec_related_record.partner_id IS NOT NULL) THEN
778
779 --dbms_output.put_line('Related Record Exists');
780
781 /*dbms_output.put_line('partner_id : '||TO_CHAR(l_prtnr_prfls_rec.partner_id)
782 ||' is the related_partner_id for the partner_id = '
783 ||TO_CHAR(currec_related_record.partner_id)
784 ||' and the profile_id = '||TO_CHAR(currec_related_record.partner_profile_id));
785 */
786 -- Initialize the record with G_MISS values
787 PVX_PRTNR_PRFLS_PVT.Init_Prtnr_Prfls_Rec(rel_prtnr_prfls_rec);
788 --dbms_output.put_line('Initialized Record');
789
790 -- Set the record for the related partner_id
791 rel_prtnr_prfls_rec.partner_profile_id := currec_related_record.partner_profile_id;
792 rel_prtnr_prfls_rec.object_version_number := currec_related_record.object_version_number;
793 rel_prtnr_prfls_rec.lead_share_appr_flag := l_prtnr_prfls_rec.lead_share_appr_flag;
794 rel_prtnr_prfls_rec.sales_partner_flag := l_prtnr_prfls_rec.sales_partner_flag;
795
796 --dbms_output.put_line('rel_prtnr_prfls_rec.partner_profile_id = '||TO_CHAR(rel_prtnr_prfls_rec.partner_profile_id));
797 --dbms_output.put_line('rel_prtnr_prfls_rec.object_version_number = '||TO_CHAR(rel_prtnr_prfls_rec.object_version_number));
798 --dbms_output.put_line('rel_prtnr_prfls_rec.lead_share_appr_flag = '||rel_prtnr_prfls_rec.lead_share_appr_flag);
799 --dbms_output.put_line('rel_prtnr_prfls_rec.sales_partner_flag = '||rel_prtnr_prfls_rec.sales_partner_flag);
800
801 --dbms_output.put_line('Before updating the related_partner_id record');
802 PVX_PRTNR_PRFLS_PVT.Update_Prtnr_Prfls(
803 p_api_version => p_api_version
804 ,p_init_msg_list => p_init_msg_list
805 ,p_commit => p_commit
806 ,p_validation_level => p_validation_level
807
808 ,x_return_status => x_return_status
809 ,x_msg_count => x_msg_count
810 ,x_msg_data => x_msg_data
811 ,p_prtnr_prfls_rec => rel_prtnr_prfls_rec
812 );
813
814 /*
815 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
816 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_LOGD_RES_ID');
817 FND_MESSAGE.set_token('ID',to_char(l_admin_rec.logged_resource_id) );
818 FND_MSG_PUB.add;
819 END IF;
820 */
821
822 IF x_return_status = FND_API.g_ret_sts_error THEN
823 RAISE FND_API.g_exc_error;
824 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
825 RAISE FND_API.g_exc_unexpected_error;
826 END IF;
827
828
829 --dbms_output.put_line('After updating the related_partner_id record');
830
831 END IF;
832
833 -------------------- finish --------------------------
834
835 -- Check for commit
836 IF FND_API.to_boolean(p_commit) THEN
837 COMMIT;
838 END IF;
839
840 FND_MSG_PUB.count_and_get(
841 p_encoded => FND_API.g_false,
842 p_count => x_msg_count,
843 p_data => x_msg_data
844 );
845
846 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
847 PVX_Utility_PVT.debug_message(l_full_name ||': end');
848 END IF;
849
850 EXCEPTION
851
852 WHEN FND_API.g_exc_error THEN
853 ROLLBACK TO Update_Prtnr_Prfls;
854 x_return_status := FND_API.g_ret_sts_error;
855 FND_MSG_PUB.count_and_get(
856 p_encoded => FND_API.g_false,
857 p_count => x_msg_count,
858 p_data => x_msg_data
859 );
860
861 WHEN FND_API.g_exc_unexpected_error THEN
862 ROLLBACK TO Update_Prtnr_Prfls;
863 x_return_status := FND_API.g_ret_sts_unexp_error ;
864 FND_MSG_PUB.count_and_get(
865 p_encoded => FND_API.g_false,
866 p_count => x_msg_count,
867 p_data => x_msg_data
868 );
869
870 WHEN OTHERS THEN
871 ROLLBACK TO Update_Prtnr_Prfls;
872 x_return_status := FND_API.g_ret_sts_unexp_error ;
873
874 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
875 THEN
876 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
877 END IF;
878
879 FND_MSG_PUB.count_and_get(
880 p_encoded => FND_API.g_false,
881 p_count => x_msg_count,
882 p_data => x_msg_data
883 );
884
885 END Update_Prtnr_Prfls;
886
887
888 --------------------------------------------------------------------
889 -- PROCEDURE
890 -- Validate_Prtnr_Prfls
891 --
892 --------------------------------------------------------------------
893 PROCEDURE Validate_Prtnr_Prfls(
894 p_api_version IN NUMBER
895 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
896 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
897
898 ,x_return_status OUT NOCOPY VARCHAR2
899 ,x_msg_count OUT NOCOPY NUMBER
900 ,x_msg_data OUT NOCOPY VARCHAR2
901
902 ,p_prtnr_prfls_rec IN prtnr_prfls_rec_type
903 )
904 IS
905
906 l_api_version CONSTANT NUMBER := 1.0;
907 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Prtnr_Prfls';
908 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
909 l_return_status VARCHAR2(1);
910
911 BEGIN
912
913 ----------------------- initialize --------------------
914 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
915 PVX_Utility_PVT.debug_message(l_full_name||': start');
916 END IF;
917
918 IF FND_API.to_boolean(p_init_msg_list) THEN
919 FND_MSG_PUB.initialize;
920 END IF;
921
922 IF NOT FND_API.compatible_api_call(
923 l_api_version,
924 p_api_version,
925 l_api_name,
926 g_pkg_name
927 ) THEN
928 RAISE FND_API.g_exc_unexpected_error;
929 END IF;
930
931 x_return_status := FND_API.g_ret_sts_success;
932
933 ---------------------- validate ------------------------
934 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
935 PVX_Utility_PVT.debug_message(l_full_name||': check items');
936 END IF;
937 --dbms_output.put_line(l_full_name||': start item validate');
938
939 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
940 Check_Prtnr_Prfls_Items(
941 p_prtnr_prfls_rec => p_prtnr_prfls_rec,
942 p_validation_mode => JTF_PLSQL_API.g_create,
943 x_return_status => l_return_status
944 );
945
946 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
947 RAISE FND_API.g_exc_unexpected_error;
948 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
949 RAISE FND_API.g_exc_error;
950 END IF;
951 END IF;
952
953 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
954 PVX_Utility_PVT.debug_message(l_full_name||': check record');
955 END IF;
956
957 --dbms_output.put_line(l_full_name||': start record validate');
958
959 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
960 Check_Prtnr_Prfls_Record(
961 p_prtnr_prfls_rec => p_prtnr_prfls_rec,
962 p_complete_rec => NULL,
963 x_return_status => l_return_status
964 );
965
966 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
967 RAISE FND_API.g_exc_unexpected_error;
968 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
969 RAISE FND_API.g_exc_error;
970 END IF;
971 END IF;
972
973 -------------------- finish --------------------------
974 FND_MSG_PUB.count_and_get(
975 p_encoded => FND_API.g_false,
976 p_count => x_msg_count,
977 p_data => x_msg_data
978 );
979
980 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
981 PVX_Utility_PVT.debug_message(l_full_name ||': end');
982 END IF;
983
984 EXCEPTION
985
986 WHEN FND_API.g_exc_error THEN
987 x_return_status := FND_API.g_ret_sts_error;
988 FND_MSG_PUB.count_and_get(
989 p_encoded => FND_API.g_false,
990 p_count => x_msg_count,
991 p_data => x_msg_data
992 );
993
994 WHEN FND_API.g_exc_unexpected_error THEN
995 x_return_status := FND_API.g_ret_sts_unexp_error ;
996 FND_MSG_PUB.count_and_get(
997 p_encoded => FND_API.g_false,
998 p_count => x_msg_count,
999 p_data => x_msg_data
1000 );
1001
1002 WHEN OTHERS THEN
1003 x_return_status := FND_API.g_ret_sts_unexp_error;
1004 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1005 THEN
1006 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1007 END IF;
1008
1009 FND_MSG_PUB.count_and_get(
1010 p_encoded => FND_API.g_false,
1011 p_count => x_msg_count,
1012 p_data => x_msg_data
1013 );
1014
1015 END Validate_Prtnr_Prfls;
1016
1017 ---------------------------------------------------------------------
1018 -- PROCEDURE
1019 -- Determine_Partner_Status
1020 --
1021 ---------------------------------------------------------------------
1022 PROCEDURE Determine_Partner_Status(
1023 p_prtnr_prfls_rec IN prtnr_prfls_rec_type
1024 ,x_partner_status OUT NOCOPY VARCHAR2
1025 )
1026 IS
1027 CURSOR c_relationship_status (p_party_id IN NUMBER, p_partner_party_id IN NUMBER) IS
1028 SELECT subject_id vendor_party_id,
1029 start_date,
1030 end_date,
1031 status
1032 FROM hz_relationships
1033 WHERE party_id = p_party_id
1034 AND object_id = p_partner_party_id;
1035
1036 CURSOR c_party_status (p_party_id IN NUMBER) IS
1037 SELECT NVL(status, 'A') party_status
1038 FROM hz_parties
1039 WHERE party_id = p_party_id;
1040
1041 CURSOR c_resource_status (p_resource_id IN NUMBER) IS
1042 SELECT start_date_active,
1043 end_date_active
1044 FROM jtf_rs_resource_extns
1045 WHERE resource_id = p_resource_id;
1046
1047 l_vendor_party_id NUMBER;
1048 l_start_date DATE;
1049 l_end_date DATE;
1050 l_status VARCHAR2(1);
1051 l_new_partner_status VARCHAR2(1);
1052
1053 BEGIN
1054
1055 l_new_partner_status := 'A';
1056
1057 OPEN c_relationship_status ( p_prtnr_prfls_rec.partner_id, p_prtnr_prfls_rec.partner_party_id );
1058 FETCH c_relationship_status INTO l_vendor_party_id, l_start_date, l_end_date, l_status;
1059 IF c_relationship_status%FOUND THEN
1060 IF l_status = 'I' THEN
1061 l_new_partner_status := 'I';
1062 ELSE
1063 IF l_start_date > SYSDATE OR NVL(l_end_date, SYSDATE) < SYSDATE THEN
1064 l_new_partner_status := 'I';
1065 END IF;
1066 END IF;
1067 END IF;
1068 CLOSE c_relationship_status;
1069
1070 IF l_new_partner_status <> 'I' THEN
1071 OPEN c_party_status (p_prtnr_prfls_rec.partner_party_id);
1072 FETCH c_party_status INTO l_status;
1073 IF c_party_status%FOUND THEN
1074 IF l_status = 'I' THEN
1075 l_new_partner_status := 'I';
1076 END IF;
1077 END IF;
1078 CLOSE c_party_status;
1079 END IF;
1080
1081 IF l_new_partner_status <> 'I' THEN
1082 OPEN c_party_status (l_vendor_party_id);
1083 FETCH c_party_status INTO l_status;
1084 IF c_party_status%FOUND THEN
1085 IF l_status = 'I' THEN
1086 l_new_partner_status := 'I';
1087 END IF;
1088 END IF;
1089 CLOSE c_party_status;
1090 END IF;
1091
1092 IF l_new_partner_status <> 'I' THEN
1093 OPEN c_resource_status (p_prtnr_prfls_rec.partner_resource_id);
1094 FETCH c_resource_status INTO l_start_date, l_end_date;
1095 IF c_resource_status%FOUND THEN
1096 IF l_start_date > SYSDATE OR NVL(l_end_date, SYSDATE) < SYSDATE THEN
1097 l_new_partner_status := 'I';
1098 END IF;
1099 END IF;
1100 CLOSE c_resource_status;
1101 END IF;
1102
1103 x_partner_status := l_new_partner_status;
1104
1105 END Determine_Partner_Status;
1106
1107 ---------------------------------------------------------------------
1108 -- PROCEDURE
1109 -- Check_Req_Items
1110 --
1111 ---------------------------------------------------------------------
1112 PROCEDURE Check_Req_Items(
1113 p_prtnr_prfls_rec IN prtnr_prfls_rec_type
1114 ,x_return_status OUT NOCOPY VARCHAR2
1115 )
1116 IS
1117 BEGIN
1118
1119 x_return_status := FND_API.g_ret_sts_success;
1120
1121 ------------------------ partner_id --------------------------
1122 IF p_prtnr_prfls_rec.partner_id IS NULL THEN
1123 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1124 THEN
1125 FND_MESSAGE.set_name('PV', 'PV_NO_PARTNER_ID');
1126 FND_MESSAGE.set_token('ID',to_char(p_prtnr_prfls_rec.partner_id) );
1127 FND_MSG_PUB.add;
1128 END IF;
1129
1130 x_return_status := FND_API.g_ret_sts_error;
1131 RETURN;
1132 ------------------------ cm_id -------------------------------
1133 /*ELSIF p_prtnr_prfls_rec.cm_id IS NULL THEN
1134 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1135 THEN
1136 FND_MESSAGE.set_name('PV', 'PV_NO_CM_ID');
1137 FND_MESSAGE.set_token('ID',to_char(p_prtnr_prfls_rec.cm_id) );
1138 FND_MSG_PUB.add;
1139 END IF;
1140
1141 x_return_status := FND_API.g_ret_sts_error;
1142 RETURN; */
1143 END IF;
1144
1145 END Check_Req_Items;
1146
1147
1148 ---------------------------------------------------------------------
1149 -- PROCEDURE
1150 -- Check_Uk_Items
1151 --
1152 ---------------------------------------------------------------------
1153 PROCEDURE Check_Uk_Items(
1154 p_prtnr_prfls_rec IN prtnr_prfls_rec_type
1155 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
1156 ,x_return_status OUT NOCOPY VARCHAR2
1157 )
1158 IS
1159 l_valid_flag VARCHAR2(1);
1160 BEGIN
1161
1162 x_return_status := FND_API.g_ret_sts_success;
1163
1164 -- when attr_val_id is passed in, we need to
1165 -- check if this is unique.
1166 IF p_validation_mode = JTF_PLSQL_API.g_create
1167 AND p_prtnr_prfls_rec.partner_profile_id IS NOT NULL
1168 THEN
1169 IF PVX_Utility_PVT.check_uniqueness(
1170 'PV_PARTNER_PROFILES',
1171 'partner_profile_id = ' || p_prtnr_prfls_rec.partner_profile_id
1172 ) = FND_API.g_false
1173 THEN
1174 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1175 THEN
1176 FND_MESSAGE.set_name('PV', 'PV_DUPLICATE_ID');
1177 FND_MESSAGE.set_token('ID',to_char(p_prtnr_prfls_rec.partner_profile_id) );
1178 FND_MSG_PUB.add;
1179 END IF;
1180 x_return_status := FND_API.g_ret_sts_error;
1181 RETURN;
1182 END IF;
1183 END IF;
1184
1185 -- check other unique items
1186
1187 END Check_Uk_Items;
1188
1189
1190 ---------------------------------------------------------------------
1191 -- PROCEDURE
1192 -- Check_Fk_Items
1193 --
1194 ---------------------------------------------------------------------
1195 PROCEDURE Check_Fk_Items(
1196 p_prtnr_prfls_rec IN prtnr_prfls_rec_type
1197 ,x_return_status OUT NOCOPY VARCHAR2
1198 )
1199 IS
1200 BEGIN
1201
1202 x_return_status := FND_API.g_ret_sts_success;
1203
1204
1205 ----------------------- partner_id ------------------------
1206 IF p_prtnr_prfls_rec.partner_id <> FND_API.g_miss_num THEN
1207 IF PVX_Utility_PVT.check_fk_exists(
1208 'HZ_PARTIES', -- Parent schema object having the primary key
1209 'PARTY_ID', -- Column name in the parent object that maps to the fk value
1210 p_prtnr_prfls_rec.partner_id, -- Value of fk to be validated against the parent object's pk column
1211 PVX_utility_PVT.g_number -- datatype of fk
1212 ) = FND_API.g_false
1213 THEN
1214 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1215 THEN
1216 FND_MESSAGE.set_name('PV', 'PV_BAD_PARTNER_ID');
1217 FND_MESSAGE.set_token('ID',to_char(p_prtnr_prfls_rec.partner_id) );
1218 FND_MSG_PUB.add;
1219 END IF;
1220
1221 x_return_status := FND_API.g_ret_sts_error;
1222 RETURN;
1223 END IF;
1224 END IF;
1225
1226 -- check other fk items
1227
1228 END Check_Fk_Items;
1229
1230
1231 ---------------------------------------------------------------------
1232 -- PROCEDURE
1233 -- Check_Lookup_Items
1234 --
1235 ---------------------------------------------------------------------
1236 PROCEDURE Check_Lookup_Items(
1237 p_prtnr_prfls_rec IN prtnr_prfls_rec_type
1238 ,x_return_status OUT NOCOPY VARCHAR2
1239 )
1240 IS
1241 BEGIN
1242
1243 x_return_status := FND_API.g_ret_sts_success;
1244
1245
1246 -- check other lookup codes
1247
1248 END Check_Lookup_Items;
1249
1250
1251 ---------------------------------------------------------------------
1252 -- PROCEDURE
1253 -- Check_Flag_Items
1254 --
1255 ---------------------------------------------------------------------
1256 PROCEDURE Check_Flag_Items(
1257 p_prtnr_prfls_rec IN prtnr_prfls_rec_type
1258 ,x_return_status OUT NOCOPY VARCHAR2
1259 )
1260 IS
1261 BEGIN
1262
1263 x_return_status := FND_API.g_ret_sts_success;
1264 ----------------------- lead_share_appr_flag ------------------------
1265 IF p_prtnr_prfls_rec.lead_share_appr_flag <> FND_API.g_miss_char
1266 AND p_prtnr_prfls_rec.lead_share_appr_flag IS NOT NULL
1267 THEN
1268 IF PVX_Utility_PVT.is_Y_or_N(p_prtnr_prfls_rec.lead_share_appr_flag) = FND_API.g_false
1269 THEN
1270 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1271 THEN
1272 FND_MESSAGE.set_name('PV', 'PV_INVALID_FLAG');
1273 FND_MSG_PUB.add;
1274 END IF;
1275
1276 x_return_status := FND_API.g_ret_sts_error;
1277 RETURN;
1278 END IF;
1279 END IF;
1280
1281 -- check other flags
1282
1283 END Check_Flag_Items;
1284
1285
1286 ---------------------------------------------------------------------
1287 -- PROCEDURE
1288 -- Check_Prtnr_Prfls_Items
1289 --
1290 ---------------------------------------------------------------------
1291 PROCEDURE Check_Prtnr_Prfls_Items(
1292 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
1293 ,x_return_status OUT NOCOPY VARCHAR2
1294 ,p_prtnr_prfls_rec IN prtnr_prfls_rec_type
1295 )
1296 IS
1297 BEGIN
1298
1299 --dbms_output.put_line(': start req items validate');
1300 Check_Req_Items(
1301 p_prtnr_prfls_rec => p_prtnr_prfls_rec
1302 ,x_return_status => x_return_status
1303 );
1304
1305 IF x_return_status <> FND_API.g_ret_sts_success THEN
1306 RETURN;
1307 END IF;
1308
1309 --dbms_output.put_line(': start uk record validate');
1310 Check_Uk_Items(
1311 p_prtnr_prfls_rec => p_prtnr_prfls_rec
1312 ,p_validation_mode => p_validation_mode
1313 ,x_return_status => x_return_status
1314 );
1315
1316 IF x_return_status <> FND_API.g_ret_sts_success THEN
1317 RETURN;
1318 END IF;
1319
1320 --dbms_output.put_line(': start fk record validate');
1321 Check_Fk_Items(
1322 p_prtnr_prfls_rec => p_prtnr_prfls_rec
1323 ,x_return_status => x_return_status
1324 );
1325
1326 IF x_return_status <> FND_API.g_ret_sts_success THEN
1327 RETURN;
1328 END IF;
1329
1330 --dbms_output.put_line(': start lookup record validate');
1331 Check_Lookup_Items(
1332 p_prtnr_prfls_rec => p_prtnr_prfls_rec
1333 ,x_return_status => x_return_status
1334 );
1335
1336 IF x_return_status <> FND_API.g_ret_sts_success THEN
1337 RETURN;
1338 END IF;
1339
1340 --dbms_output.put_line(': start flag record validate');
1341 Check_Flag_Items(
1342 p_prtnr_prfls_rec => p_prtnr_prfls_rec
1343 ,x_return_status => x_return_status
1344 );
1345
1346 IF x_return_status <> FND_API.g_ret_sts_success THEN
1347 RETURN;
1348 END IF;
1349
1350 END Check_Prtnr_Prfls_Items;
1351
1352
1353
1354 ---------------------------------------------------------------------
1355 -- PROCEDURE
1356 -- Check_Prtnr_Prfls_Record
1357 --
1358 ---------------------------------------------------------------------
1359 PROCEDURE Check_Prtnr_Prfls_Record(
1360 p_prtnr_prfls_rec IN prtnr_prfls_rec_type
1361 ,p_complete_rec IN prtnr_prfls_rec_type := NULL
1362 ,p_mode IN VARCHAR2 := 'INSERT'
1363 ,x_return_status OUT NOCOPY VARCHAR2
1364 )
1365 IS
1366 BEGIN
1367
1368 x_return_status := FND_API.g_ret_sts_success;
1369 /* Raise an error if already NOT NULL value for CM_ID is being updated to null.
1370 */
1371 IF p_complete_rec.cm_id IS NOT NULL THEN
1372 IF p_prtnr_prfls_rec.cm_id IS NULL THEN
1373 --OR p_prtnr_prfls_rec.cm_id = FND_API.G_MISS_NUM
1374 FND_MESSAGE.Set_name('PV','PV_CM_ID_UPDATE_VIOLATION');
1375 FND_MESSAGE.Set_Token('ID', TO_CHAR(p_complete_rec.cm_id));
1376 FND_MSG_PUB.ADD;
1377 x_return_status := FND_API.g_ret_sts_error;
1378 RETURN;
1379 END IF;
1380 END IF;
1381
1382 -- do other record level checkings
1383
1384 END Check_Prtnr_Prfls_Record;
1385
1386
1387 ---------------------------------------------------------------------
1388 -- PROCEDURE
1389 -- Init_Prtnr_Prfls_Rec
1390 --
1391 ---------------------------------------------------------------------
1392 PROCEDURE Init_Prtnr_Prfls_Rec(
1393 x_prtnr_prfls_rec OUT NOCOPY prtnr_prfls_rec_type
1394 )
1395 IS
1396 BEGIN
1397 x_prtnr_prfls_rec.PARTNER_PROFILE_ID := FND_API.G_MISS_NUM;
1398 x_prtnr_prfls_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
1399 x_prtnr_prfls_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
1400 x_prtnr_prfls_rec.CREATION_DATE := FND_API.G_MISS_DATE;
1401 x_prtnr_prfls_rec.CREATED_BY := FND_API.G_MISS_NUM;
1402 x_prtnr_prfls_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
1403 x_prtnr_prfls_rec.PARTNER_ID := FND_API.G_MISS_NUM;
1404 x_prtnr_prfls_rec.TARGET_REVENUE_AMT := FND_API.G_MISS_NUM;
1405 x_prtnr_prfls_rec.ACTUAL_REVENUE_AMT := FND_API.G_MISS_NUM;
1406 x_prtnr_prfls_rec.TARGET_REVENUE_PCT := FND_API.G_MISS_NUM;
1407 x_prtnr_prfls_rec.ACTUAL_REVENUE_PCT := FND_API.G_MISS_NUM;
1408 x_prtnr_prfls_rec.ORIG_SYSTEM_REFERENCE := FND_API.G_MISS_CHAR;
1409 x_prtnr_prfls_rec.ORIG_SYSTEM_TYPE := FND_API.G_MISS_CHAR;
1410 x_prtnr_prfls_rec.CAPACITY_SIZE := FND_API.G_MISS_CHAR;
1411 x_prtnr_prfls_rec.CAPACITY_AMOUNT := FND_API.G_MISS_CHAR;
1412 x_prtnr_prfls_rec.AUTO_MATCH_ALLOWED_FLAG := FND_API.G_MISS_CHAR;
1413 x_prtnr_prfls_rec.PURCHASE_METHOD := FND_API.G_MISS_CHAR;
1414 x_prtnr_prfls_rec.CM_ID := FND_API.G_MISS_NUM;
1415 x_prtnr_prfls_rec.PH_SUPPORT_REP := FND_API.G_MISS_NUM;
1416 --x_prtnr_prfls_rec.SECURITY_GROUP_ID := FND_API.G_MISS_NUM;
1417 x_prtnr_prfls_rec.OBJECT_VERSION_NUMBER := FND_API.G_MISS_NUM;
1418 x_prtnr_prfls_rec.LEAD_SHARING_STATUS := FND_API.G_MISS_CHAR;
1419 x_prtnr_prfls_rec.LEAD_SHARE_APPR_FLAG := FND_API.G_MISS_CHAR;
1420 x_prtnr_prfls_rec.PARTNER_RELATIONSHIP_ID := FND_API.G_MISS_NUM;
1421 x_prtnr_prfls_rec.PARTNER_LEVEL := FND_API.G_MISS_CHAR;
1422 x_prtnr_prfls_rec.PREFERRED_VAD_ID := FND_API.G_MISS_NUM;
1423 x_prtnr_prfls_rec.partner_group_id := FND_API.G_MISS_NUM;
1424 x_prtnr_prfls_rec.partner_resource_id := FND_API.G_MISS_NUM;
1425 x_prtnr_prfls_rec.partner_group_number := FND_API.G_MISS_CHAR;
1426 x_prtnr_prfls_rec.partner_resource_number := FND_API.G_MISS_CHAR;
1427 x_prtnr_prfls_rec.sales_partner_flag := FND_API.G_MISS_CHAR;
1428 x_prtnr_prfls_rec.indirectly_managed_flag := FND_API.G_MISS_CHAR;
1429 x_prtnr_prfls_rec.channel_marketing_manager := FND_API.G_MISS_NUM;
1430 x_prtnr_prfls_rec.related_partner_id := FND_API.G_MISS_NUM;
1431 x_prtnr_prfls_rec.max_users := FND_API.G_MISS_NUM;
1432 x_prtnr_prfls_rec.partner_party_id := FND_API.G_MISS_NUM;
1433 x_prtnr_prfls_rec.status := FND_API.G_MISS_CHAR;
1434
1435 END Init_Prtnr_Prfls_Rec;
1436
1437
1438 ---------------------------------------------------------------------
1439 -- PROCEDURE
1440 -- Complete_Prtnr_Prfls_Rec
1441 --
1442 ---------------------------------------------------------------------
1443 PROCEDURE Complete_Prtnr_Prfls_Rec(
1444 p_prtnr_prfls_rec IN prtnr_prfls_rec_type
1445 ,x_complete_rec OUT NOCOPY prtnr_prfls_rec_type
1446 )
1447 IS
1448
1449 CURSOR c_prtnr_prfls IS
1450 SELECT *
1451 FROM PV_PARTNER_PROFILES
1452 WHERE partner_profile_id = p_prtnr_prfls_rec.partner_profile_id;
1453
1454 l_prtnr_prfls_rec c_prtnr_prfls%ROWTYPE;
1455
1456 BEGIN
1457
1458 x_complete_rec := p_prtnr_prfls_rec;
1459
1460 OPEN c_prtnr_prfls;
1461 FETCH c_prtnr_prfls INTO l_prtnr_prfls_rec;
1462 IF c_prtnr_prfls%NOTFOUND THEN
1463 CLOSE c_prtnr_prfls;
1464 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1465 FND_MESSAGE.set_name('PV', 'PV_NO_RECORD_FOUND');
1466 FND_MSG_PUB.add;
1467 END IF;
1468 RAISE FND_API.g_exc_error;
1469 END IF;
1470 CLOSE c_prtnr_prfls;
1471
1472 IF p_prtnr_prfls_rec.partner_id = FND_API.G_MISS_NUM THEN
1473 x_complete_rec.partner_id := l_prtnr_prfls_rec.partner_id;
1474 END IF;
1475
1476 IF p_prtnr_prfls_rec.target_revenue_amt = FND_API.G_MISS_NUM THEN
1477 x_complete_rec.target_revenue_amt := l_prtnr_prfls_rec.target_revenue_amt;
1478 END IF;
1479
1480 IF p_prtnr_prfls_rec.actual_revenue_amt = FND_API.G_MISS_NUM THEN
1481 x_complete_rec.actual_revenue_amt := l_prtnr_prfls_rec.actual_revenue_amt;
1482 END IF;
1483
1484 IF p_prtnr_prfls_rec.target_revenue_pct = FND_API.G_MISS_NUM THEN
1485 x_complete_rec.target_revenue_pct := l_prtnr_prfls_rec.target_revenue_pct;
1486 END IF;
1487
1488 IF p_prtnr_prfls_rec.actual_revenue_pct = FND_API.G_MISS_NUM THEN
1489 x_complete_rec.actual_revenue_pct := l_prtnr_prfls_rec.actual_revenue_pct;
1490 END IF;
1491
1492 IF p_prtnr_prfls_rec.orig_system_reference = FND_API.G_MISS_CHAR THEN
1493 x_complete_rec.orig_system_reference := l_prtnr_prfls_rec.orig_system_reference;
1494 END IF;
1495
1496 IF p_prtnr_prfls_rec.orig_system_type = FND_API.G_MISS_CHAR THEN
1497 x_complete_rec.orig_system_type := l_prtnr_prfls_rec.orig_system_type;
1498 END IF;
1499
1500 IF p_prtnr_prfls_rec.capacity_size = FND_API.G_MISS_CHAR THEN
1501 x_complete_rec.capacity_size := l_prtnr_prfls_rec.capacity_size;
1502 END IF;
1503
1504 IF p_prtnr_prfls_rec.capacity_amount = FND_API.G_MISS_CHAR THEN
1505 x_complete_rec.capacity_amount := l_prtnr_prfls_rec.capacity_amount;
1506 END IF;
1507
1508 IF p_prtnr_prfls_rec.auto_match_allowed_flag = FND_API.G_MISS_CHAR THEN
1509 x_complete_rec.auto_match_allowed_flag := l_prtnr_prfls_rec.auto_match_allowed_flag;
1510 END IF;
1511
1512 IF p_prtnr_prfls_rec.purchase_method = FND_API.G_MISS_CHAR THEN
1513 x_complete_rec.purchase_method := l_prtnr_prfls_rec.purchase_method;
1514 END IF;
1515
1516 IF p_prtnr_prfls_rec.cm_id = FND_API.G_MISS_NUM THEN
1517 x_complete_rec.cm_id := l_prtnr_prfls_rec.cm_id;
1518 END IF;
1519
1520 IF p_prtnr_prfls_rec.ph_support_rep = FND_API.G_MISS_NUM THEN
1521 x_complete_rec.ph_support_rep := l_prtnr_prfls_rec.ph_support_rep;
1522 END IF;
1523 /*
1524 IF p_prtnr_prfls_rec.security_group_id = FND_API.G_MISS_NUM THEN
1525 x_complete_rec.security_group_id := l_prtnr_prfls_rec.security_group_id;
1526 END IF;
1527 */
1528 IF p_prtnr_prfls_rec.object_version_number = FND_API.G_MISS_NUM THEN
1529 x_complete_rec.object_version_number := l_prtnr_prfls_rec.object_version_number;
1530 END IF;
1531
1532 IF p_prtnr_prfls_rec.lead_sharing_status = FND_API.G_MISS_CHAR THEN
1533 x_complete_rec.lead_sharing_status := l_prtnr_prfls_rec.lead_sharing_status;
1534 END IF;
1535
1536 IF p_prtnr_prfls_rec.lead_share_appr_flag = FND_API.G_MISS_CHAR THEN
1537 x_complete_rec.lead_share_appr_flag := l_prtnr_prfls_rec.lead_share_appr_flag;
1538 END IF;
1539
1540 IF p_prtnr_prfls_rec.partner_relationship_id = FND_API.G_MISS_NUM THEN
1541 x_complete_rec.partner_relationship_id := l_prtnr_prfls_rec.partner_relationship_id;
1542 END IF;
1543
1544 IF p_prtnr_prfls_rec.partner_level = FND_API.G_MISS_CHAR THEN
1545 x_complete_rec.partner_level := l_prtnr_prfls_rec.partner_level;
1546 END IF;
1547
1548 IF p_prtnr_prfls_rec.preferred_vad_id = FND_API.G_MISS_NUM THEN
1549 x_complete_rec.preferred_vad_id := l_prtnr_prfls_rec.preferred_vad_id;
1550 END IF;
1551
1552 IF p_prtnr_prfls_rec.partner_group_id = FND_API.G_MISS_NUM THEN
1553 x_complete_rec.partner_group_id := l_prtnr_prfls_rec.partner_group_id;
1554 END IF;
1555
1556 IF p_prtnr_prfls_rec.partner_resource_id = FND_API.G_MISS_NUM THEN
1557 x_complete_rec.partner_resource_id := l_prtnr_prfls_rec.partner_resource_id;
1558 END IF;
1559
1560 IF p_prtnr_prfls_rec.partner_group_number = FND_API.G_MISS_CHAR THEN
1561 x_complete_rec.partner_group_number := l_prtnr_prfls_rec.partner_group_number;
1562 END IF;
1563
1564 IF p_prtnr_prfls_rec.partner_resource_number = FND_API.G_MISS_CHAR THEN
1565 x_complete_rec.partner_resource_number := l_prtnr_prfls_rec.partner_resource_number;
1566 END IF;
1567
1568 IF p_prtnr_prfls_rec.sales_partner_flag = FND_API.G_MISS_CHAR THEN
1569 x_complete_rec.sales_partner_flag := l_prtnr_prfls_rec.sales_partner_flag;
1570 END IF;
1571
1572 IF p_prtnr_prfls_rec.indirectly_managed_flag = FND_API.G_MISS_CHAR THEN
1573 x_complete_rec.indirectly_managed_flag := l_prtnr_prfls_rec.indirectly_managed_flag;
1574 END IF;
1575
1576 IF p_prtnr_prfls_rec.channel_marketing_manager = FND_API.G_MISS_NUM THEN
1577 x_complete_rec.channel_marketing_manager := l_prtnr_prfls_rec.channel_marketing_manager;
1578 END IF;
1579
1580 IF p_prtnr_prfls_rec.related_partner_id = FND_API.G_MISS_NUM THEN
1581 x_complete_rec.related_partner_id := l_prtnr_prfls_rec.related_partner_id;
1582 END IF;
1583
1584 IF p_prtnr_prfls_rec.max_users = FND_API.G_MISS_NUM THEN
1585 x_complete_rec.max_users := l_prtnr_prfls_rec.max_users;
1586 END IF;
1587
1588 IF p_prtnr_prfls_rec.partner_party_id = FND_API.G_MISS_NUM THEN
1589 x_complete_rec.partner_party_id := l_prtnr_prfls_rec.partner_party_id;
1590 END IF;
1591
1592
1593 END Complete_Prtnr_Prfls_Rec;
1594
1595
1596 END PVX_PRTNR_PRFLS_PVT;