[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_USG_ASSIGNMENT_PVT
Source
1 PACKAGE BODY HZ_PARTY_USG_ASSIGNMENT_PVT AS
2 /*$Header: ARHPUAPB.pls 120.13 2011/03/24 11:12:22 rgokavar ship $ */
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 D_FUTURE_DATE CONSTANT DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
9
10 G_SETUP_LOADED NUMBER(1) := 0;
11 G_CALLING_API VARCHAR2(30);
12
13 TYPE INDEX_VARCHAR100_TBL IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
14 TYPE INDEX_NUMBER_TBL IS TABLE OF NUMBER(15) INDEX BY VARCHAR2(30);
15
16 G_PARTY_USAGE_CODES INDEX_VARCHAR100_TBL;
17 G_CREATED_BY_MODULES INDEX_NUMBER_TBL;
18 G_PARTY_USAGE_RULES INDEX_VARCHAR100_TBL;
19
20 TYPE VARCHAR100_TBL IS TABLE OF VARCHAR2(100);
21 TYPE NUMBER15_TBL IS TABLE OF NUMBER(15);
22 TYPE DATE_TBL IS TABLE OF DATE;
23
24 TYPE ASSIGNMENT_REC_TBL IS TABLE OF party_usg_assignment_rec_type;
25
26
27 --------------------------------------
28 -- declaration of private procedures and functions
29 --------------------------------------
30
31 PROCEDURE initialize;
32
33 PROCEDURE split (
34 p_string IN VARCHAR2,
35 p_delimiter IN VARCHAR2,
36 x_table OUT NOCOPY VARCHAR100_TBL
37 );
38
39 FUNCTION duplicates_exist (
40 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
41 x_party_usg_assignment_id OUT NOCOPY NUMBER
42 ) RETURN VARCHAR2;
43
44 PROCEDURE insert_row (
45 p_party_usg_assignment_rec IN party_usg_assignment_rec_type
46 );
47
48 PROCEDURE update_row (
49 p_party_usg_assignment_id IN NUMBER,
50 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
51 p_object_version_number IN OUT NOCOPY NUMBER,
52 p_old_object_version_number IN NUMBER,
53 p_status IN VARCHAR2
54 );
55
56 FUNCTION violate_exclusive_rules (
57 p_party_usage_code IN VARCHAR2,
58 p_related_party_usage_code IN VARCHAR2
59 ) RETURN BOOLEAN;
60
61 FUNCTION violate_coexist_rules (
62 p_party_usage_code IN VARCHAR2,
63 p_related_party_usage_code IN VARCHAR2
64 ) RETURN BOOLEAN;
65
66 FUNCTION has_transition_rules (
67 p_party_usage_code IN VARCHAR2,
68 p_related_party_usage_code IN VARCHAR2
69 ) RETURN BOOLEAN;
70
71 PROCEDURE populate_bot (
72 p_create_update_flag IN VARCHAR2,
73 p_party_usg_assignment_id IN NUMBER
74 );
75
76 --------------------------------------
77 -- private procedures and functions
78 --------------------------------------
79
80 /**
81 * PROCEDURE populate_bot
82 *
83 * DESCRIPTION
84 * Populate Business Object Tracking table.
85 *
86 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
87 *
88 * NOTES
89 *
90 * MODIFICATION HISTORY
91 *
92 * 02/24/06 Jianying Huang o Created.
93 *
94 */
95
96 PROCEDURE populate_bot (
97 p_create_update_flag IN VARCHAR2,
98 p_party_usg_assignment_id IN NUMBER
99 ) IS
100
101 c_api_name CONSTANT VARCHAR2(30) :=
102 'populate_bot';
103 l_debug_prefix VARCHAR2(30);
104
105 BEGIN
106
107 l_debug_prefix := '';
108
109 -- Debug info.
110 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
111 hz_utility_v2pub.debug (
112 p_message => c_api_name||' (+)',
113 p_prefix => l_debug_prefix,
114 p_msg_level => fnd_log.level_procedure);
115 END IF;
116
117 -- populate function for integration service
118 IF hz_utility_v2pub.G_EXECUTE_API_CALLOUTS IN ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')
119 THEN
120 hz_populate_bot_pkg.pop_hz_party_usg_assignments(
121 p_operation => p_create_update_flag,
122 P_party_usg_assignment_id => p_party_usg_assignment_id);
123 END IF;
124
125 -- Debug info.
126 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
127 hz_utility_v2pub.debug (
128 p_message => c_api_name||' (-)',
129 p_prefix => l_debug_prefix,
130 p_msg_level => fnd_log.level_procedure);
131 END IF;
132
133 END populate_bot;
134
135
136 /**
137 * FUNCTION violate_exclusive_rules
138 *
139 * DESCRIPTION
140 * Check if violate exclusive rules
141 *
142 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
143 *
144 * NOTES
145 *
146 * MODIFICATION HISTORY
147 *
148 * 07/19/05 Jianying Huang o Created.
149 *
150 */
151
152 FUNCTION violate_exclusive_rules (
153 p_party_usage_code IN VARCHAR2,
154 p_related_party_usage_code IN VARCHAR2
155 ) RETURN BOOLEAN IS
156
157 l_return BOOLEAN;
158
159 BEGIN
160
161 IF (G_PARTY_USAGE_RULES.exists('EXCLUSIVE##'||p_party_usage_code||'##') OR
162 G_PARTY_USAGE_RULES.exists('EXCLUSIVE##'||p_related_party_usage_code||'##'))
163 THEN
164 l_return := TRUE;
165 ELSE
166 l_return := FALSE;
167 END IF;
168
169 RETURN l_return;
170
171 END violate_exclusive_rules;
172
173
174 /**
175 * FUNCTION violate_coexist_rules
176 *
177 * DESCRIPTION
178 * Check if violate co-exist rules
179 *
180 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
181 *
182 * NOTES
183 *
184 * MODIFICATION HISTORY
185 *
186 * 07/19/05 Jianying Huang o Created.
187 *
188 */
189
190 FUNCTION violate_coexist_rules (
191 p_party_usage_code IN VARCHAR2,
192 p_related_party_usage_code IN VARCHAR2
193 ) RETURN BOOLEAN IS
194
195 l_return BOOLEAN;
196
197 BEGIN
198
199 IF (G_PARTY_USAGE_RULES.exists(
200 'CANNOT_COEXIST##'||p_party_usage_code||'##'||p_related_party_usage_code) OR
201 G_PARTY_USAGE_RULES.exists(
202 'CANNOT_COEXIST##'||p_related_party_usage_code||'##'||p_party_usage_code))
203 THEN
204 l_return := TRUE;
205 ELSE
206 l_return := FALSE;
207 END IF;
208
209 RETURN l_return;
210
211 END violate_coexist_rules;
212
213
214 /**
215 * FUNCTION has_transition_rules
216 *
217 * DESCRIPTION
218 * Check if violate co-exist rules
219 *
220 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
221 *
222 * NOTES
223 *
224 * MODIFICATION HISTORY
225 *
226 * 07/19/05 Jianying Huang o Created.
227 *
228 */
229
230 FUNCTION has_transition_rules (
231 p_party_usage_code IN VARCHAR2,
232 p_related_party_usage_code IN VARCHAR2
233 ) RETURN BOOLEAN IS
234
235 l_return BOOLEAN;
236
237 BEGIN
238
239 IF G_PARTY_USAGE_RULES.exists(
240 'TRANSITION_BI##'||
241 p_party_usage_code||'##'||p_related_party_usage_code) OR
242 G_PARTY_USAGE_RULES.exists(
243 'TRANSITION_BI##'||
244 p_related_party_usage_code||'##'||p_party_usage_code) OR
245 G_PARTY_USAGE_RULES.exists(
246 'TRANSITION##'||
247 p_party_usage_code||'##'||p_related_party_usage_code)
248 THEN
249 l_return := TRUE;
250 ELSE
251 l_return := FALSE;
252 END IF;
253
254 RETURN l_return;
255
256 END has_transition_rules;
257
258
259 /**
260 * PROCEDURE validate_party_usg_assignment
261 *
262 * DESCRIPTION
263 * Validate usage assignment.
264 *
265 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
266 *
267 * NOTES
268 *
269 * MODIFICATION HISTORY
270 *
271 * 05/01/05 Jianying Huang o Created.
272 *
273 */
274
275 PROCEDURE validate_party_usg_assignment (
276 p_create_update_flag IN VARCHAR2,
277 p_validation_level IN NUMBER,
278 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
279 p_old_usg_assignment_rec IN party_usg_assignment_rec_type,
280 x_return_status IN OUT NOCOPY VARCHAR2
281 ) IS
282
283 c_api_name CONSTANT VARCHAR2(30) :=
284 'validate_party_usg_assignment';
285 l_debug_prefix VARCHAR2(30);
286
287 -- party: foreign key
288 CURSOR c_party (
289 p_party_id NUMBER
290 ) IS
291 SELECT party_type,
292 party_name
293 FROM hz_parties
294 WHERE party_id = p_party_id
295 AND status IN ('A', 'I');
296
297 /*
298 -- relationship: foreign key
299 CURSOR c_relationship (
300 p_relationship_id NUMBER
301 ) IS
302 SELECT null
303 FROM hz_relationships
304 WHERE relationship_id = p_relationship_id
305 AND status IN ('A', 'I')
306 AND rownum = 1;
307 */
308
309 -- assginments
310 CURSOR c_assignments (
311 p_party_id NUMBER
312 ) IS
313 SELECT party_usage_code
314 FROM hz_party_usg_assignments
315 WHERE party_id = p_party_id;
316
317 db_party_type VARCHAR2(30);
318 db_party_name VARCHAR2(360);
319 db_party_usage_status VARCHAR2(30);
320 db_party_usage_type VARCHAR2(30);
321 db_restrict_manual_assign VARCHAR2(30);
322 db_restrict_manual_update VARCHAR2(30);
323 db_party_usage_created_by NUMBER;
324 l_created_by NUMBER(15);
325 l_party_usage_codes_tbl VARCHAR100_TBL;
326 l_party_usage_code VARCHAR2(30);
327 l_created_by_module VARCHAR2(150);
328 i NUMBER;
329 l_continue VARCHAR2(1);
330 l_dummy VARCHAR2(30);
331 l_value_tbl VARCHAR100_TBL;
332 l_temp_party_usage_codes INDEX_VARCHAR100_TBL;
333
334 BEGIN
335
336 l_debug_prefix := '';
337
338 -- Debug info.
339 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
340 hz_utility_v2pub.debug (
341 p_message => c_api_name||' (+)',
342 p_prefix => l_debug_prefix,
343 p_msg_level => fnd_log.level_procedure);
344 END IF;
345
346 -- Debug info.
347 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
348 hz_utility_v2pub.debug(
349 p_prefix => l_debug_prefix,
350 p_message => 'x_return_status = '||x_return_status,
351 p_msg_level => fnd_log.level_statement);
352 END IF;
353
354 --
355 -- HIGH VALIDATION
356 --
357 IF p_validation_level >= G_VALID_LEVEL_HIGH THEN
358 --
359 -- the following validations are only needed when create
360 -- a new assignment because all of involved columns
361 -- are non-updateable.
362 --
363 IF p_create_update_flag = 'C' THEN
364 --
365 -- mandatory: party_id
366 --
367 hz_utility_v2pub.validate_mandatory (
368 p_create_update_flag => p_create_update_flag,
369 p_column => 'party_id',
370 p_column_value => p_party_usg_assignment_rec.party_id,
371 x_return_status => x_return_status
372 );
373
374 -- Debug info.
375 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
376 hz_utility_v2pub.debug(
377 p_prefix => l_debug_prefix,
378 p_message => 'party_id is a mandatory column. '||
379 'x_return_status = '||x_return_status,
380 p_msg_level => fnd_log.level_statement);
381 END IF;
382
383 --
384 -- mandatory: party_usage_code
385 --
386 hz_utility_v2pub.validate_mandatory (
387 p_create_update_flag => p_create_update_flag,
388 p_column => 'party_usage_code',
389 p_column_value => p_party_usg_assignment_rec.party_usage_code,
390 x_return_status => x_return_status
391 );
392
393 -- Debug info.
394 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
395 hz_utility_v2pub.debug(
396 p_prefix => l_debug_prefix,
397 p_message => 'party_usage_code is a mandatory column. '||
398 'x_return_status = '||x_return_status,
399 p_msg_level => fnd_log.level_statement);
400 END IF;
401
402 --
403 -- conditional mandatory: owner_table_name, owner_table_id
404 --
405 IF p_party_usg_assignment_rec.owner_table_name IS NOT NULL AND
406 p_party_usg_assignment_rec.owner_table_name <> fnd_api.G_MISS_CHAR
407 THEN
408 hz_utility_v2pub.validate_mandatory (
409 p_create_update_flag => p_create_update_flag,
410 p_column => 'owner_table_id',
411 p_column_value => p_party_usg_assignment_rec.owner_table_id,
412 x_return_status => x_return_status
413 );
414 END IF;
415
416 IF p_party_usg_assignment_rec.owner_table_id IS NOT NULL AND
417 p_party_usg_assignment_rec.owner_table_id <> fnd_api.G_MISS_NUM
418 THEN
419 hz_utility_v2pub.validate_mandatory (
420 p_create_update_flag => p_create_update_flag,
421 p_column => 'owner_table_name',
422 p_column_value => p_party_usg_assignment_rec.owner_table_name,
423 x_return_status => x_return_status
424 );
425 END IF;
426
427 -- Debug info.
428 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
429 hz_utility_v2pub.debug(
430 p_prefix => l_debug_prefix,
431 p_message => 'conditional mandatory: owner_table_name, owner_table_id. '||
432 'x_return_status = '||x_return_status,
433 p_msg_level => fnd_log.level_statement);
434 END IF;
435
436 --
437 -- foreign key: owner_table_name, owner_table_id.
438 --
439 /*
440 IF p_owner_table_name = 'HZ_RELATIONSHIPS' THEN
441 OPEN c_relationship (p_owner_table_id);
442 FETCH c_relationship INTO l_dummy;
443 IF c_relationship%NOTFOUND THEN
444 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
445 fnd_message.set_token('FK', 'owner_table_id');
446 fnd_message.set_token('COLUMN', 'relationship_id');
447 fnd_message.set_token('TABLE', 'hz_relationships');
448 fnd_msg_pub.add;
449 x_return_status := fnd_api.G_RET_STS_ERROR;
450 END IF;
451
452 -- Debug info.
453 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
454 hz_utility_v2pub.debug(
455 p_prefix => l_debug_prefix,
456 p_message => 'foreign key check when owner_table_name = HZ_RELATIONSHIPS. '||
457 'x_return_status = '||x_return_status,
458 p_msg_level => fnd_log.level_statement);
459 END IF;
460 END IF;
461 */
462
463 ELSE -- p_create_update_flag = 'U'
464 --
465 -- non-updateable: party_id
466 --
467 hz_utility_v2pub.validate_nonupdateable (
468 p_column => 'party_id',
469 p_column_value => p_party_usg_assignment_rec.party_id,
470 p_old_column_value => p_old_usg_assignment_rec.party_id,
471 x_return_status => x_return_status
472 );
473
474 -- Debug info.
475 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
476 hz_utility_v2pub.debug(
477 p_prefix => l_debug_prefix,
478 p_message => 'party_id is a non-updateable column. '||
479 'x_return_status = '||x_return_status,
480 p_msg_level => fnd_log.level_statement);
481 END IF;
482
483 --
484 -- non-updateable: party_usage_code
485 --
486 hz_utility_v2pub.validate_nonupdateable (
487 p_column => 'party_usage_code',
488 p_column_value => p_party_usg_assignment_rec.party_usage_code,
489 p_old_column_value => p_old_usg_assignment_rec.party_usage_code,
490 x_return_status => x_return_status
491 );
492
493 -- Debug info.
494 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
495 hz_utility_v2pub.debug(
496 p_prefix => l_debug_prefix,
497 p_message => 'party_usage_code is a non-updateable column. '||
498 'x_return_status = '||x_return_status,
499 p_msg_level => fnd_log.level_statement);
500 END IF;
501
502 --
503 -- non-updateable: owner_table_name
504 --
505 hz_utility_v2pub.validate_nonupdateable (
506 p_column => 'owner_table_name',
507 p_column_value => p_party_usg_assignment_rec.owner_table_name,
508 p_old_column_value => p_old_usg_assignment_rec.owner_table_name,
509 x_return_status => x_return_status
510 );
511
512 -- Debug info.
513 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
514 hz_utility_v2pub.debug(
515 p_prefix => l_debug_prefix,
516 p_message => 'owner_table_name is a non-updateable column. '||
517 'x_return_status = '||x_return_status,
518 p_msg_level => fnd_log.level_statement);
519 END IF;
520
521 --
522 -- non-updateable: owner_table_id
523 --
524 hz_utility_v2pub.validate_nonupdateable (
525 p_column => 'owner_table_id',
526 p_column_value => p_party_usg_assignment_rec.owner_table_id,
527 p_old_column_value => p_old_usg_assignment_rec.owner_table_id,
528 x_return_status => x_return_status
529 );
530
531 -- Debug info.
532 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
533 hz_utility_v2pub.debug(
534 p_prefix => l_debug_prefix,
535 p_message => 'owner_table_id is a non-updateable column. '||
536 'x_return_status = '||x_return_status,
537 p_msg_level => fnd_log.level_statement);
538 END IF;
539
540 END IF; -- p_create_update_flag = 'U'
541
542 --
543 -- effective_start_date, effective_end_date
544 -- end date can't be ealier then start date
545 --
546 IF trunc(p_party_usg_assignment_rec.effective_start_date) >
547 trunc(p_party_usg_assignment_rec.effective_end_date)
548 THEN
549 fnd_message.set_name('AR', 'HZ_API_DATE_GREATER');
550 fnd_message.set_token('DATE2', 'effective_end_date');
551 fnd_message.set_token('DATE1', 'effective_start_date');
552 fnd_msg_pub.add;
553 x_return_status := fnd_api.G_RET_STS_ERROR;
554 END IF;
555
556 -- Debug info.
557 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
558 hz_utility_v2pub.debug(
559 p_prefix => l_debug_prefix,
560 p_message => 'end date can not be earlier than start date. '||
561 'x_return_status = '||x_return_status,
562 p_msg_level => fnd_log.level_statement);
563 END IF;
564
565 --
566 -- mandatory, non-updateable, lookup : created_by_module
567 --
568 hz_utility_v2pub.validate_created_by_module (
569 p_create_update_flag => p_create_update_flag,
570 p_created_by_module => p_party_usg_assignment_rec.created_by_module,
571 p_old_created_by_module => p_old_usg_assignment_rec.created_by_module,
572 x_return_status => x_return_status
573 );
574
575 --
576 -- quit when basic validations fail
577 --
578 IF x_return_status = fnd_api.G_RET_STS_ERROR THEN
579 RETURN;
580 END IF;
581
582 END IF; -- HIGH VALIDATION
583
584 IF p_create_update_flag = 'C' THEN
585 l_party_usage_code := p_party_usg_assignment_rec.party_usage_code;
586 l_created_by_module := p_party_usg_assignment_rec.created_by_module;
587 ELSE
588 l_party_usage_code := p_old_usg_assignment_rec.party_usage_code;
589 END IF;
590
591 --
592 -- MEDIUM VALIDATION
593 --
594 IF p_validation_level >= G_VALID_LEVEL_MEDIUM THEN
595 --
596 -- foreign key: party_usage_code
597 --
598 IF G_PARTY_USAGE_CODES.exists(l_party_usage_code) THEN
599 split(G_PARTY_USAGE_CODES(l_party_usage_code), '##', l_value_tbl);
600 db_party_usage_status := l_value_tbl(1);
601 db_party_usage_type := l_value_tbl(2);
602 db_restrict_manual_assign := l_value_tbl(3);
603 db_restrict_manual_update := l_value_tbl(4);
604 db_party_usage_created_by := l_value_tbl(5);
605
606 -- Debug info.
607 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
608 hz_utility_v2pub.debug(
609 p_prefix => l_debug_prefix,
610 p_message => 'foreign key validation for party_usage_code. '||
611 'db_party_usage_status = '||db_party_usage_status||' '||
612 'db_party_usage_type = '||db_party_usage_type||' '||
613 'db_restrict_manual_assign = '||db_restrict_manual_assign||' '||
614 'db_restrict_manual_update = '||db_restrict_manual_update,
615 p_msg_level => fnd_log.level_statement);
616 END IF;
617 ELSIF p_create_update_flag = 'C' THEN
618 --
619 -- invalid foreign key
620 --
621 fnd_message.set_name('AR', 'HZ_PU_INVALID_PARTY_USAGE_CODE');
622 fnd_message.set_token('PARTY_USAGE_CODE', l_party_usage_code);
623 fnd_msg_pub.add;
624 x_return_status := fnd_api.G_RET_STS_ERROR;
625 END IF;
626
627 -- Debug info.
628 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
629 hz_utility_v2pub.debug(
630 p_prefix => l_debug_prefix,
631 p_message => 'foreign key validation for party_usage_code. '||
632 'x_return_status = '||x_return_status,
633 p_msg_level => fnd_log.level_statement);
634 END IF;
635
636 IF p_create_update_flag = 'C' THEN
637 --
638 -- inactive party usage code
639 --
640 IF db_party_usage_status <> 'A' THEN
641 fnd_message.set_name('AR', 'HZ_PU_INACTIVE_PARTY_USG_CODE');
642 fnd_message.set_token('PARTY_USAGE_CODE', p_party_usg_assignment_rec.party_usage_code);
643 fnd_msg_pub.add;
644 x_return_status := fnd_api.G_RET_STS_ERROR;
645 END IF;
646
647 -- Debug info.
648 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
649 hz_utility_v2pub.debug(
650 p_prefix => l_debug_prefix,
651 p_message => 'inactive party_usage_code. '||
652 'x_return_status = '||x_return_status,
653 p_msg_level => fnd_log.level_statement);
654 END IF;
655
656 --
657 -- foreign key: party_id
658 --
659 OPEN c_party (p_party_usg_assignment_rec.party_id);
660 FETCH c_party INTO db_party_type, db_party_name;
661 IF (c_party%NOTFOUND) THEN
662 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
663 fnd_message.set_token('FK', 'party_id');
664 fnd_message.set_token('COLUMN', 'party_id');
665 fnd_message.set_token('TABLE', 'hz_parties');
666 fnd_msg_pub.add;
667 x_return_status := fnd_api.G_RET_STS_ERROR;
668 END IF;
669 CLOSE c_party;
670
671 -- Debug info.
672 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
673 hz_utility_v2pub.debug(
674 p_prefix => l_debug_prefix,
675 p_message => 'foreign key validation for party_id. '||
676 'x_return_status = '||x_return_status||' '||
677 'party_type = '||db_party_type,
678 p_msg_level => fnd_log.level_statement);
679 END IF;
680
681 --
682 -- party type doesn't match
683 --
684 IF instrb(db_party_usage_type, db_party_type) = 0 THEN
685 fnd_message.set_name('AR', 'HZ_PU_INVALID_PARTY_TYPE');
686 fnd_message.set_token('PARTY_USAGE_CODE', p_party_usg_assignment_rec.party_usage_code);
687 fnd_message.set_token('PARTY_TYPE', db_party_type);
688 fnd_msg_pub.add;
689 x_return_status := fnd_api.G_RET_STS_ERROR;
690 END IF;
691
692 -- Debug info.
693 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
694 hz_utility_v2pub.debug(
695 p_prefix => l_debug_prefix,
696 p_message => 'party type doesn''t match. '||
697 'x_return_status = '||x_return_status,
698 p_msg_level => fnd_log.level_statement);
699 END IF;
700
701 --
702 -- disallow certain usages be used by public API
703 --
704 IF db_restrict_manual_assign = 'Y' THEN
705 IF G_CREATED_BY_MODULES.exists(l_created_by_module) THEN
706 l_created_by := G_CREATED_BY_MODULES(l_created_by_module);
707 ELSE
708 l_created_by := -99;
709 END IF;
710
711 --
712 -- disallow manual assignment when created_by_module
713 -- is not a seeded value and party usage is a seeded one.
714 --
715 --Bug 7149894: Included 121 in user id validation while validating
716 -- Party Usage Assignment.
717 --Bug 7260706: Included 122 in user id validation while validating
718 -- Party Usage Assignment.
719 IF l_created_by NOT IN (0, 1, 2, 120, 121,122) AND
720 db_party_usage_created_by IN (0, 1, 2, 120, 121,122)
721 THEN
722 fnd_message.set_name('AR', 'HZ_PU_SEED_CBM_ASSIGN');
723 fnd_message.set_token('PARTY_USAGE_CODE', l_party_usage_code);
724 fnd_msg_pub.add;
725 x_return_status := fnd_api.G_RET_STS_ERROR;
726 END IF;
727
728 -- Debug info.
729 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
730 hz_utility_v2pub.debug(
731 p_prefix => l_debug_prefix,
732 p_message => 'created_by_module is created by . '||
733 'l_created_by = '||l_created_by||' '||
734 'x_return_status = '||x_return_status,
735 p_msg_level => fnd_log.level_statement);
736 END IF;
737 END IF;
738
739 -- Bug 4586451
740 --
741 IF l_party_usage_code = 'SUPPLIER' AND
742 db_party_type='ORGANIZATION' AND
743 p_validation_level<>G_VALID_LEVEL_THIRD_MEDIUM
744 THEN
745 validate_supplier_name (
746 p_party_id => p_party_usg_assignment_rec.party_id,
747 p_party_name => db_party_name,
748 x_return_status => x_return_status);
749 END IF;
750
751 ELSE -- p_create_update_flag = 'U'
752 --Bug 7149894: Included 121 in user id validation while validating
753 -- Party Usage Assignment.
754 --Bug 7260706: Included 122 in user id validation while validating
755 -- Party Usage Assignment.
756 IF db_restrict_manual_update = 'Y' AND
757 G_CALLING_API = 'HZ_PARTY_USG_ASSIGNMENT_PUB' AND
758 db_party_usage_created_by IN (0, 1, 2, 120, 121,122)
759 THEN
760 fnd_message.set_name('AR', 'HZ_PU_SEED_CBM_UPDATE');
761 fnd_message.set_token('PARTY_USAGE_CODE', l_party_usage_code);
762 fnd_msg_pub.add;
763 x_return_status := fnd_api.G_RET_STS_ERROR;
764 END IF;
765
766 -- Debug info.
767 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
768 hz_utility_v2pub.debug(
769 p_prefix => l_debug_prefix,
770 p_message => 'manual update is Y. calling from public API. '||
771 'x_return_status = '||x_return_status,
772 p_msg_level => fnd_log.level_statement);
773 END IF;
774 END IF;
775
776 END IF; -- MEDIUM VALIDATION
777
778 --
779 -- LOW VALIDATION
780 --
781 IF p_validation_level >= G_VALID_LEVEL_LOW AND
782 p_create_update_flag = 'C'
783 THEN
784 --
785 -- check party usage rules
786 --
787 IF G_SETUP_LOADED = 3 THEN
788
789 OPEN c_assignments(p_party_usg_assignment_rec.party_id);
790 FETCH c_assignments BULK COLLECT INTO
791 l_party_usage_codes_tbl;
792 CLOSE c_assignments;
793
794 --
795 -- the following check are needed only when there
796 -- are some existing assignments
797 --
798 l_continue := 'Y'; i := 1;
799 WHILE (i <= l_party_usage_codes_tbl.count AND
800 l_continue = 'Y')
801 LOOP
802 -- Debug info.
803 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
804 hz_utility_v2pub.debug(
805 p_prefix => l_debug_prefix,
806 p_message => 'l_party_usage_codes_tbl('||i||') = '||
807 l_party_usage_codes_tbl(i),
808 p_msg_level => fnd_log.level_statement);
809 END IF;
810
811 IF NOT l_temp_party_usage_codes.exists(l_party_usage_codes_tbl(i)) AND
812 l_party_usage_codes_tbl(i) <> l_party_usage_code
813 THEN
814
815 -- store dupliate party usage codes into a temporary pl/sql table.
816 l_temp_party_usage_codes(l_party_usage_codes_tbl(i)) := 'Y';
817
818 --
819 -- check exclusive rule
820 -- check co-exist rule
821 --
822 IF (violate_exclusive_rules(
823 l_party_usage_code, l_party_usage_codes_tbl(i)) OR
824 violate_coexist_rules(
825 l_party_usage_code, l_party_usage_codes_tbl(i)))
826 THEN
827 fnd_message.set_name('AR', 'HZ_PU_EXCLUSIVE_RULE_FAILED');
828 fnd_message.set_token('EXISTING_PARTY_USAGE_CODE', l_party_usage_codes_tbl(i));
829 fnd_message.set_token('NEW_PARTY_USAGE_CODE', l_party_usage_code);
830 fnd_msg_pub.add;
831 x_return_status := fnd_api.G_RET_STS_ERROR;
832
833 l_continue := 'N';
834 END IF;
835
836 -- Debug info.
837 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
838 hz_utility_v2pub.debug(
839 p_prefix => l_debug_prefix,
840 p_message => 'check exclusive and co-exist rule. '||
841 'x_return_status = '||x_return_status,
842 p_msg_level => fnd_log.level_statement);
843 END IF;
844
845 END IF;
846
847 i := i + 1;
848 END LOOP;
849 END IF;
850
851 END IF; -- LOW VALIDATION
852
853 -- Debug info.
854 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
855 hz_utility_v2pub.debug (
856 p_message => c_api_name||' (-)',
857 p_prefix => l_debug_prefix,
858 p_msg_level => fnd_log.level_procedure);
859 END IF;
860
861 END validate_party_usg_assignment;
862
863
864 /**
865 * PRIVATE PROCEDURE duplicates_exist
866 *
867 * DESCRIPTION
868 * Private procedure to check if there is any duplicates
869 *
870 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
871 *
872 * MODIFICATION HISTORY
873 *
874 * 05/01/05 Jianying Huang o Created.
875 *
876 */
877
878 FUNCTION duplicates_exist (
879 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
880 x_party_usg_assignment_id OUT NOCOPY NUMBER
881 ) RETURN VARCHAR2 IS
882
883 c_api_name CONSTANT VARCHAR2(30) := 'duplicates_exist';
884 l_debug_prefix VARCHAR2(30);
885
886 -- search by owner_table_name/owner_table_id
887 CURSOR c_duplicate_assignment0 (
888 p_party_id NUMBER,
889 p_party_usage_code VARCHAR2,
890 p_owner_table_name VARCHAR2,
891 p_owner_table_id NUMBER
892 ) IS
893 SELECT party_usg_assignment_id
894 FROM hz_party_usg_assignments
895 WHERE owner_table_name = p_owner_table_name
896 AND owner_table_id = p_owner_table_id
897 AND party_id = p_party_id
898 AND party_usage_code = p_party_usage_code
899 AND rownum = 1;
900
901 -- search by party id/party usage code
902 CURSOR c_duplicate_assignment1 (
903 p_party_id NUMBER,
904 p_party_usage_code VARCHAR2,
905 p_effective_start_date DATE,
906 p_effective_end_date DATE
907 ) IS
908 SELECT party_usg_assignment_id
909 FROM hz_party_usg_assignments
910 WHERE party_id = p_party_id
911 AND party_usage_code = p_party_usage_code
912 AND status_flag = 'A'
913 AND p_effective_start_date BETWEEN
914 effective_start_date AND effective_end_date
915 AND effective_end_date >= p_effective_end_date
916 AND rownum = 1;
917
918 l_has_duplicates VARCHAR2(1);
919 l_assignment_id NUMBER(15);
920
921 BEGIN
922
923 l_debug_prefix := '';
924
925 -- Debug info.
926 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
927 hz_utility_v2pub.debug (
928 p_message => c_api_name||' (+)',
929 p_prefix => l_debug_prefix,
930 p_msg_level => fnd_log.level_procedure);
931 END IF;
932
933 l_has_duplicates := 'N';
934
935 -- check duplicate assignment
936 --
937 -- check owner_table_name, owner_table_id
938 --
939 IF p_party_usg_assignment_rec.owner_table_name IS NOT NULL AND
940 p_party_usg_assignment_rec.owner_table_name <> fnd_api.G_MISS_CHAR AND
941 p_party_usg_assignment_rec.owner_table_id IS NOT NULL AND
942 p_party_usg_assignment_rec.owner_table_id <> fnd_api.G_MISS_NUM
943 THEN
944 -- Debug info.
945 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
946 hz_utility_v2pub.debug(
947 p_prefix => l_debug_prefix,
948 p_message => 'owner_table_name, owner_table_id are passed in for this usage. '||
949 'Check duplicate assignment.',
950 p_msg_level => fnd_log.level_statement);
951 END IF;
952
953 OPEN c_duplicate_assignment0 (
954 p_party_usg_assignment_rec.party_id,
955 p_party_usg_assignment_rec.party_usage_code,
956 p_party_usg_assignment_rec.owner_table_name,
957 p_party_usg_assignment_rec.owner_table_id
958 );
959 FETCH c_duplicate_assignment0 INTO l_assignment_id;
960 IF c_duplicate_assignment0%FOUND THEN
961 -- duplicate exist. won't assign the current usage.
962 l_has_duplicates := 'Y';
963 x_party_usg_assignment_id := l_assignment_id;
964 END IF;
965 CLOSE c_duplicate_assignment0;
966 --
967 -- check party_id, party_usage_code, effective_start_date, effective_end_date
968 --
969 ELSIF p_party_usg_assignment_rec.party_id IS NOT NULL AND
970 p_party_usg_assignment_rec.party_id <> fnd_api.G_MISS_NUM AND
971 p_party_usg_assignment_rec.party_usage_code IS NOT NULL AND
972 p_party_usg_assignment_rec.party_usage_code <> fnd_api.G_MISS_CHAR
973 THEN
974 -- Debug info.
975 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
976 hz_utility_v2pub.debug(
977 p_prefix => l_debug_prefix,
978 p_message => 'No date tracking for this usage '||
979 p_party_usg_assignment_rec.party_usage_code||'. '||
980 'Check duplicate assignment.',
981 p_msg_level => fnd_log.level_statement);
982 END IF;
983
984 OPEN c_duplicate_assignment1 (
985 p_party_usg_assignment_rec.party_id,
986 p_party_usg_assignment_rec.party_usage_code,
987 p_party_usg_assignment_rec.effective_start_date,
988 p_party_usg_assignment_rec.effective_end_date
989 );
990 FETCH c_duplicate_assignment1 INTO l_assignment_id;
991 IF c_duplicate_assignment1%FOUND THEN
992 -- duplicate exist. won't assign the current usage.
993 l_has_duplicates := 'Y';
994 x_party_usg_assignment_id := l_assignment_id;
995 END IF;
996 CLOSE c_duplicate_assignment1;
997 END IF;
998
999 -- Debug info.
1000 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1001 hz_utility_v2pub.debug(
1002 p_prefix => l_debug_prefix,
1003 p_message => 'l_has_duplicates = '||l_has_duplicates,
1004 p_msg_level => fnd_log.level_statement);
1005 END IF;
1006
1007 -- Debug info.
1008 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1009 hz_utility_v2pub.debug (
1010 p_message => c_api_name||' (-)',
1011 p_prefix => l_debug_prefix,
1012 p_msg_level => fnd_log.level_procedure);
1013 END IF;
1014
1015 return l_has_duplicates;
1016
1017 END duplicates_exist;
1018
1019
1020 /**
1021 * PRIVATE PROCEDURE do_assign_party_usage
1022 *
1023 * DESCRIPTION
1024 * Private procedure to create party usage assignment
1025 *
1026 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1027 *
1028 * MODIFICATION HISTORY
1029 *
1030 * 05/01/05 Jianying Huang o Created.
1031 *
1032 */
1033
1034 PROCEDURE do_assign_party_usage (
1035 p_validation_level IN NUMBER,
1036 p_party_usg_assignment_rec IN OUT NOCOPY party_usg_assignment_rec_type,
1037 x_return_status IN OUT NOCOPY VARCHAR2
1038 ) IS
1039
1040 c_api_name CONSTANT VARCHAR2(30) := 'do_assign_party_usage';
1041 l_debug_prefix VARCHAR2(30);
1042
1043 CURSOR c_assignments (
1044 p_party_id NUMBER
1045 ) IS
1046 SELECT party_usg_assignment_id,
1047 party_usage_code,
1048 effective_start_date
1049 FROM hz_party_usg_assignments
1050 WHERE party_id = p_party_id
1051 AND status_flag = 'A'
1052 AND trunc(sysdate) between
1053 effective_start_date and effective_end_date;
1054
1055 l_party_usg_assignment_id_tbl NUMBER15_TBL;
1056 l_party_usage_code_tbl VARCHAR100_TBL;
1057 l_start_date_tbl DATE_TBL;
1058 l_party_usg_assignment_rec party_usg_assignment_rec_type;
1059 l_object_version_number NUMBER;
1060 l_continue VARCHAR2(1);
1061 i NUMBER;
1062 l_has_duplicates VARCHAR2(1);
1063 l_dummy NUMBER(15);
1064 l_status VARCHAR2(1);
1065
1066
1067 BEGIN
1068
1069 l_debug_prefix := '';
1070
1071 -- Debug info.
1072 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1073 hz_utility_v2pub.debug (
1074 p_message => c_api_name||' (+)',
1075 p_prefix => l_debug_prefix,
1076 p_msg_level => fnd_log.level_procedure);
1077 END IF;
1078
1079 -- load setup data
1080 IF G_SETUP_LOADED = 0 THEN
1081 initialize;
1082 END IF;
1083
1084 -- check dates.
1085 IF p_party_usg_assignment_rec.effective_start_date IS NULL OR
1086 p_party_usg_assignment_rec.effective_start_date = fnd_api.G_MISS_DATE
1087 THEN
1088 p_party_usg_assignment_rec.effective_start_date := trunc(sysdate);
1089 ELSE
1090 p_party_usg_assignment_rec.effective_start_date :=
1091 trunc(p_party_usg_assignment_rec.effective_start_date);
1092 END IF;
1093
1094 IF p_party_usg_assignment_rec.effective_end_date IS NULL OR
1095 p_party_usg_assignment_rec.effective_end_date = fnd_api.G_MISS_DATE
1096 THEN
1097 p_party_usg_assignment_rec.effective_end_date := D_FUTURE_DATE;
1098 ELSE
1099 p_party_usg_assignment_rec.effective_end_date :=
1100 trunc(p_party_usg_assignment_rec.effective_end_date);
1101 END IF;
1102
1103 -- Debug info.
1104 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1105 hz_utility_v2pub.debug(
1106 p_prefix => l_debug_prefix,
1107 p_message => 'effective_start_date = '||
1108 TO_CHAR(p_party_usg_assignment_rec.effective_start_date, 'YYYY/MM/DD')||' '||
1109 'effective_end_date = '||
1110 TO_CHAR(p_party_usg_assignment_rec.effective_end_date, 'YYYY/MM/DD'),
1111 p_msg_level => fnd_log.level_statement);
1112 END IF;
1113
1114 --
1115 -- Do validation
1116 --
1117 IF p_validation_level > G_VALID_LEVEL_NONE THEN
1118 validate_party_usg_assignment (
1119 p_create_update_flag => 'C',
1120 p_validation_level => p_validation_level,
1121 p_party_usg_assignment_rec => p_party_usg_assignment_rec,
1122 p_old_usg_assignment_rec => l_party_usg_assignment_rec,
1123 x_return_status => x_return_status
1124 );
1125
1126 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1127 RAISE fnd_api.G_EXC_ERROR;
1128 END IF;
1129 END IF;
1130
1131 --
1132 -- return if there is any duplicates exist
1133 --
1134 l_has_duplicates := duplicates_exist(p_party_usg_assignment_rec, l_dummy);
1135
1136 IF l_has_duplicates = 'Y' THEN
1137 RETURN;
1138 END IF;
1139
1140 --
1141 -- handle transition rule. per talk with vinoo, we will not adjust the dates.
1142 -- we just inactivate existing assignments.
1143 --
1144 l_continue := 'Y';
1145
1146 IF G_SETUP_LOADED >= 2 THEN
1147
1148 OPEN c_assignments (p_party_usg_assignment_rec.party_id);
1149 FETCH c_assignments BULK COLLECT INTO
1150 l_party_usg_assignment_id_tbl,
1151 l_party_usage_code_tbl, l_start_date_tbl;
1152 CLOSE c_assignments;
1153
1154 SAVEPOINT party_usage_transition;
1155
1156 i := 1;
1157 WHILE i <= l_party_usg_assignment_id_tbl.count AND
1158 l_continue = 'Y'
1159 LOOP
1160 -- Bug 4954932: transition rule indicates that by assigning
1161 -- the related party usage, the existing usage will be end-dated
1162 IF has_transition_rules(
1163 l_party_usage_code_tbl(i),
1164 p_party_usg_assignment_rec.party_usage_code)
1165 THEN
1166 l_party_usg_assignment_rec.effective_end_date := trunc(sysdate);
1167 IF l_start_date_tbl(i) = trunc(sysdate) THEN
1168 l_status := 'I';
1169 ELSE
1170 l_status := 'A';
1171 END IF;
1172
1173 -- don't need to compare object version number here.
1174 l_object_version_number := null;
1175
1176 update_row (
1177 p_party_usg_assignment_id => l_party_usg_assignment_id_tbl(i),
1178 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
1179 p_object_version_number => l_object_version_number,
1180 p_old_object_version_number => null,
1181 p_status => l_status
1182 );
1183 -- Bug 4954932: transition rule indicates that by assigning
1184 -- the related party usage, the existing usage will be end-dated
1185 ELSIF G_PARTY_USAGE_RULES.exists(
1186 'TRANSITION##'||
1187 p_party_usg_assignment_rec.party_usage_code||'##'||
1188 l_party_usage_code_tbl(i))
1189 THEN
1190 l_continue := 'N';
1191 ROLLBACK TO party_usage_transition;
1192 END IF;
1193
1194 i := i + 1;
1195
1196 END LOOP;
1197
1198 END IF;
1199
1200 --
1201 -- create party usage assignment
1202 --
1203 IF l_continue = 'Y' THEN
1204 insert_row (
1205 p_party_usg_assignment_rec => p_party_usg_assignment_rec
1206 );
1207 END IF;
1208
1209 -- Debug info.
1210 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1211 hz_utility_v2pub.debug (
1212 p_message => c_api_name||' (-)',
1213 p_prefix => l_debug_prefix,
1214 p_msg_level => fnd_log.level_procedure);
1215 END IF;
1216
1217 END do_assign_party_usage;
1218
1219
1220 /**
1221 * PRIVATE PROCEDURE do_update_usg_assignment
1222 *
1223 * DESCRIPTION
1224 * Private procedure to update party usage assignment
1225 *
1226 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1227 *
1228 * MODIFICATION HISTORY
1229 *
1230 * 05/01/05 Jianying Huang o Created.
1231 *
1232 */
1233
1234 PROCEDURE do_update_usg_assignment (
1235 p_validation_level IN NUMBER,
1236 p_usg_assignment_id_tbl IN NUMBER15_TBL,
1237 p_party_usg_assignment_rec IN OUT NOCOPY party_usg_assignment_rec_type,
1238 p_old_usg_assignment_rec_tbl IN ASSIGNMENT_REC_TBL,
1239 x_return_status IN OUT NOCOPY VARCHAR2
1240 ) IS
1241
1242 c_api_name CONSTANT VARCHAR2(30) := 'do_update_usg_assignment';
1243 l_debug_prefix VARCHAR2(30);
1244 l_object_version_number NUMBER;
1245
1246 CURSOR c_assignments (
1247 p_party_id NUMBER
1248 ) IS
1249 SELECT party_usg_assignment_id,
1250 party_usage_code,
1251 effective_start_date
1252 FROM hz_party_usg_assignments
1253 WHERE party_id = p_party_id
1254 AND status_flag = 'A'
1255 AND trunc(sysdate) between
1256 effective_start_date and effective_end_date;
1257
1258 l_party_usg_assignment_id_tbl NUMBER15_TBL;
1259 l_party_usage_code_tbl VARCHAR100_TBL;
1260 l_start_date_tbl DATE_TBL;
1261 l_party_usg_assignment_rec party_usg_assignment_rec_type;
1262 l_object_version_number1 NUMBER;
1263 l_continue VARCHAR2(1);
1264 j NUMBER;
1265 l_status VARCHAR2(1);
1266
1267 BEGIN
1268
1269 l_debug_prefix := '';
1270
1271 -- Debug info.
1272 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1273 hz_utility_v2pub.debug (
1274 p_message => c_api_name||' (+)',
1275 p_prefix => l_debug_prefix,
1276 p_msg_level => fnd_log.level_procedure);
1277 END IF;
1278
1279 -- load setup data
1280 IF G_SETUP_LOADED = 0 THEN
1281 initialize;
1282 END IF;
1283
1284 FOR i IN 1..p_usg_assignment_id_tbl.count LOOP
1285 l_object_version_number := null;
1286 l_object_version_number1 := null;
1287
1288 -- check dates.
1289 IF p_party_usg_assignment_rec.effective_start_date IS NULL OR
1290 p_party_usg_assignment_rec.effective_start_date = fnd_api.G_MISS_DATE
1291 THEN
1292 p_party_usg_assignment_rec.effective_start_date :=
1293 p_old_usg_assignment_rec_tbl(i).effective_start_date;
1294 ELSE
1295 p_party_usg_assignment_rec.effective_start_date :=
1296 trunc(p_party_usg_assignment_rec.effective_start_date);
1297 END IF;
1298
1299 IF p_party_usg_assignment_rec.effective_end_date IS NOT NULL AND
1300 p_party_usg_assignment_rec.effective_end_date <> fnd_api.G_MISS_DATE
1301 THEN
1302 p_party_usg_assignment_rec.effective_end_date :=
1303 trunc(p_party_usg_assignment_rec.effective_end_date);
1304 ELSIF p_party_usg_assignment_rec.effective_end_date IS NULL THEN
1305 p_party_usg_assignment_rec.effective_end_date :=
1306 p_old_usg_assignment_rec_tbl(i).effective_end_date;
1307 ELSE
1308 p_party_usg_assignment_rec.effective_end_date := D_FUTURE_DATE;
1309 END IF;
1310
1311 -- Debug info.
1312 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1313 hz_utility_v2pub.debug(
1314 p_prefix => l_debug_prefix,
1315 p_message => 'effective_start_date = '||
1316 TO_CHAR(p_party_usg_assignment_rec.effective_start_date, 'YYYY/MM/DD')||' '||
1317 'effective_end_date = '||
1318 TO_CHAR(p_party_usg_assignment_rec.effective_end_date, 'YYYY/MM/DD'),
1319 p_msg_level => fnd_log.level_statement);
1320 END IF;
1321
1322 --
1323 -- Do validation
1324 --
1325 IF p_validation_level > G_VALID_LEVEL_NONE THEN
1326 validate_party_usg_assignment (
1327 p_create_update_flag => 'U',
1328 p_validation_level => p_validation_level,
1329 p_party_usg_assignment_rec => p_party_usg_assignment_rec,
1330 p_old_usg_assignment_rec => p_old_usg_assignment_rec_tbl(i),
1331 x_return_status => x_return_status
1332 );
1333
1334 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1335 RAISE fnd_api.G_EXC_ERROR;
1336 END IF;
1337 END IF;
1338
1339 --
1340 -- handle transition rule. per talk with vinoo, we will not adjust the dates.
1341 -- we just inactivate existing assignments.
1342 --
1343 l_continue := 'Y';
1344
1345 IF G_SETUP_LOADED >= 2 AND
1346 p_party_usg_assignment_rec.effective_end_date = D_FUTURE_DATE
1347 THEN
1348
1349 OPEN c_assignments (p_party_usg_assignment_rec.party_id);
1350 FETCH c_assignments BULK COLLECT INTO
1351 l_party_usg_assignment_id_tbl,
1352 l_party_usage_code_tbl, l_start_date_tbl;
1353 CLOSE c_assignments;
1354
1355 SAVEPOINT party_usage_transition;
1356
1357 j := 1;
1358 WHILE j <= l_party_usg_assignment_id_tbl.count AND
1359 l_continue = 'Y'
1360 LOOP
1361 -- Bug 4954932: transition rule indicates that by assigning
1362 -- the related party usage, the existing usage will be end-dated
1363 IF has_transition_rules(
1364 l_party_usage_code_tbl(j),
1365 p_party_usg_assignment_rec.party_usage_code)
1366 THEN
1367 l_party_usg_assignment_rec.effective_end_date := trunc(sysdate);
1368 IF l_start_date_tbl(j) = trunc(sysdate) THEN
1369 l_status := 'I';
1370 ELSE
1371 l_status := 'A';
1372 END IF;
1373
1374 -- don't need to compare object version number here.
1375 l_object_version_number1 := null;
1376
1377 update_row (
1378 p_party_usg_assignment_id => l_party_usg_assignment_id_tbl(j),
1379 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
1380 p_object_version_number => l_object_version_number1,
1381 p_old_object_version_number => null,
1382 p_status => l_status
1383 );
1384 -- Bug 4954932: transition rule indicates that by assigning
1385 -- the related party usage, the existing usage will be end-dated
1386 ELSIF G_PARTY_USAGE_RULES.exists(
1387 'TRANSITION##'||
1388 p_party_usg_assignment_rec.party_usage_code||'##'||
1389 l_party_usage_code_tbl(j))
1390 THEN
1391 l_continue := 'N';
1392 ROLLBACK TO party_usage_transition;
1393 END IF;
1394
1395 j := j + 1;
1396
1397 END LOOP;
1398 END IF;
1399
1400 --
1401 -- update party usage assignment
1402 --
1403 IF l_continue = 'Y' THEN
1404 update_row (
1405 p_party_usg_assignment_id => p_usg_assignment_id_tbl(i),
1406 p_party_usg_assignment_rec => p_party_usg_assignment_rec,
1407 p_object_version_number => l_object_version_number,
1408 p_old_object_version_number => null,
1409 p_status => null
1410 );
1411 END IF;
1412 END LOOP;
1413
1414 -- Debug info.
1415 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1416 hz_utility_v2pub.debug (
1417 p_message => c_api_name||' (-)',
1418 p_prefix => l_debug_prefix,
1419 p_msg_level => fnd_log.level_procedure);
1420 END IF;
1421
1422 END do_update_usg_assignment;
1423
1424
1425 /**
1426 * PROCEDURE initialize
1427 *
1428 * DESCRIPTION
1429 * cache setup.
1430 *
1431 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1432 *
1433 * NOTES
1434 *
1435 * MODIFICATION HISTORY
1436 *
1437 * 05/01/05 Jianying Huang o Created.
1438 *
1439 */
1440
1441 PROCEDURE initialize IS
1442
1443 c_api_name CONSTANT VARCHAR2(30) := 'initialize';
1444 l_debug_prefix VARCHAR2(30);
1445
1446 -- load party usages
1447 CURSOR c_party_usages IS
1448 SELECT party_usage_code,
1449 party_usage_type,
1450 status_flag,
1451 restrict_manual_assign_flag,
1452 restrict_manual_update_flag,
1453 created_by
1454 FROM hz_party_usages_b;
1455
1456 -- load rules
1457 CURSOR c_exist_exclusive_rules IS
1458 SELECT null
1459 FROM hz_party_usage_rules
1460 WHERE (party_usage_rule_type = 'EXCLUSIVE' OR
1461 party_usage_rule_type = 'CANNOT_COEXIST')
1462 AND trunc(sysdate) between
1463 effective_start_date AND effective_end_date
1464 AND rownum = 1;
1465
1466 CURSOR c_party_usage_rules IS
1467 SELECT party_usage_rule_type||'##'||
1468 party_usage_code||'##'||
1469 related_party_usage_code
1470 FROM hz_party_usage_rules
1471 WHERE trunc(sysdate) between
1472 effective_start_date AND effective_end_date;
1473
1474 -- load created by module
1475 CURSOR c_created_by_module IS
1476 SELECT lookup_code, created_by
1477 FROM fnd_lookup_values
1478 WHERE lookup_type = 'HZ_CREATED_BY_MODULES'
1479 AND view_application_id = 222
1480 AND language = userenv('LANG')
1481 AND enabled_flag = 'Y'
1482 AND trunc(sysdate) BETWEEN
1483 trunc(nvl(start_date_active, sysdate)) AND
1484 trunc(nvl(end_date_active, sysdate));
1485
1486 l_party_usages_tbl VARCHAR100_TBL;
1487 l_party_usage_type_tbl VARCHAR100_TBL;
1488 l_party_usage_status_tbl VARCHAR100_TBL;
1489 l_restrict_manual_assign_tbl VARCHAR100_TBL;
1490 l_restrict_manual_update_tbl VARCHAR100_TBL;
1491 l_party_usage_created_by_tbl NUMBER15_TBL;
1492 l_party_usage_rules_tbl VARCHAR100_TBL;
1493 l_created_by_module_tbl VARCHAR100_TBL;
1494 l_created_by_tbl NUMBER15_TBL;
1495 l_dummy VARCHAR2(1);
1496
1497 BEGIN
1498
1499 l_debug_prefix := '';
1500
1501 -- Debug info.
1502 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1503 hz_utility_v2pub.debug (
1504 p_message => c_api_name||' (+)',
1505 p_prefix => l_debug_prefix,
1506 p_msg_level => fnd_log.level_procedure);
1507 END IF;
1508
1509 G_PARTY_USAGE_CODES.delete;
1510 G_CREATED_BY_MODULES.delete;
1511 G_PARTY_USAGE_RULES.delete;
1512
1513 --
1514 -- load set up data
1515 --
1516
1517 -- load party usages
1518 OPEN c_party_usages;
1519 FETCH c_party_usages BULK COLLECT INTO
1520 l_party_usages_tbl, l_party_usage_type_tbl,
1521 l_party_usage_status_tbl, l_restrict_manual_assign_tbl,
1522 l_restrict_manual_update_tbl, l_party_usage_created_by_tbl;
1523 CLOSE c_party_usages;
1524
1525 FOR i IN 1..l_party_usages_tbl.count LOOP
1526 G_PARTY_USAGE_CODES(l_party_usages_tbl(i)) :=
1527 l_party_usage_status_tbl(i)||'##'||
1528 l_party_usage_type_tbl(i)||'##'||
1529 l_restrict_manual_assign_tbl(i)||'##'||
1530 l_restrict_manual_update_tbl(i)||'##'||
1531 l_party_usage_created_by_tbl(i);
1532 END LOOP;
1533
1534 -- load created by module
1535 OPEN c_created_by_module;
1536 FETCH c_created_by_module BULK COLLECT INTO
1537 l_created_by_module_tbl, l_created_by_tbl;
1538 CLOSE c_created_by_module;
1539
1540 FOR i IN 1..l_created_by_module_tbl.count LOOP
1541 G_CREATED_BY_MODULES(l_created_by_module_tbl(i)) := l_created_by_tbl(i);
1542 END LOOP;
1543
1544 G_SETUP_LOADED := 1;
1545
1546 -- load party usage rule
1547 OPEN c_party_usage_rules;
1548 FETCH c_party_usage_rules BULK COLLECT INTO l_party_usage_rules_tbl;
1549 CLOSE c_party_usage_rules;
1550
1551 IF l_party_usage_rules_tbl.count > 0 THEN
1552 FOR i IN 1..l_party_usage_rules_tbl.count LOOP
1553 G_PARTY_USAGE_RULES(l_party_usage_rules_tbl(i)) := 'Y';
1554 END LOOP;
1555
1556 --
1557 -- have rules defined
1558 --
1559 G_SETUP_LOADED := 2;
1560
1561 -- check if there is any exclusive or co-exist rule
1562 OPEN c_exist_exclusive_rules;
1563 FETCH c_exist_exclusive_rules INTO l_dummy;
1564 IF c_exist_exclusive_rules%FOUND THEN
1565 G_SETUP_LOADED := 3;
1566 END IF;
1567 CLOSE c_exist_exclusive_rules;
1568
1569 END IF;
1570
1571 -- Debug info.
1572 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1573 hz_utility_v2pub.debug(
1574 p_prefix => l_debug_prefix,
1575 p_message => 'G_SETUP_LOADED = '||G_SETUP_LOADED,
1576 p_msg_level => fnd_log.level_statement);
1577 END IF;
1578
1579 -- Debug info.
1580 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1581 hz_utility_v2pub.debug (
1582 p_message => c_api_name||' (-)',
1583 p_prefix => l_debug_prefix,
1584 p_msg_level => fnd_log.level_procedure);
1585 END IF;
1586
1587 END initialize;
1588
1589
1590 /**
1591 * PROCEDURE insert_row
1592 *
1593 * DESCRIPTION
1594 * Insert a new assignment.
1595 *
1596 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1597 *
1598 * NOTES
1599 *
1600 * MODIFICATION HISTORY
1601 *
1602 * 05/01/05 Jianying Huang o Created.
1603 *
1604 */
1605
1606 PROCEDURE insert_row (
1607 p_party_usg_assignment_rec IN party_usg_assignment_rec_type
1608 ) IS
1609
1610 c_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1611 l_debug_prefix VARCHAR2(30);
1612 l_party_usg_assignment_id NUMBER(15);
1613
1614 BEGIN
1615
1616 l_debug_prefix := '';
1617
1618 -- Debug info.
1619 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1620 hz_utility_v2pub.debug (
1621 p_message => c_api_name||' (+)',
1622 p_prefix => l_debug_prefix,
1623 p_msg_level => fnd_log.level_procedure);
1624 END IF;
1625
1626 HZ_PARTY_USG_ASSIGNMENTS_PKG.insert_row (
1627 x_party_id => p_party_usg_assignment_rec.party_id,
1628 x_party_usage_code => p_party_usg_assignment_rec.party_usage_code,
1629 x_effective_start_date => p_party_usg_assignment_rec.effective_start_date,
1630 x_effective_end_date => p_party_usg_assignment_rec.effective_end_date,
1631 x_status_flag => 'A',
1632 x_comments => p_party_usg_assignment_rec.comments,
1633 x_owner_table_name => p_party_usg_assignment_rec.owner_table_name,
1634 x_owner_table_id => p_party_usg_assignment_rec.owner_table_id,
1635 x_attribute_category => p_party_usg_assignment_rec.attribute_category,
1636 x_attribute1 => p_party_usg_assignment_rec.attribute1,
1637 x_attribute2 => p_party_usg_assignment_rec.attribute2,
1638 x_attribute3 => p_party_usg_assignment_rec.attribute3,
1639 x_attribute4 => p_party_usg_assignment_rec.attribute4,
1640 x_attribute5 => p_party_usg_assignment_rec.attribute5,
1641 x_attribute6 => p_party_usg_assignment_rec.attribute6,
1642 x_attribute7 => p_party_usg_assignment_rec.attribute7,
1643 x_attribute8 => p_party_usg_assignment_rec.attribute8,
1644 x_attribute9 => p_party_usg_assignment_rec.attribute9,
1645 x_attribute10 => p_party_usg_assignment_rec.attribute10,
1646 x_attribute11 => p_party_usg_assignment_rec.attribute11,
1647 x_attribute12 => p_party_usg_assignment_rec.attribute12,
1648 x_attribute13 => p_party_usg_assignment_rec.attribute13,
1649 x_attribute14 => p_party_usg_assignment_rec.attribute14,
1650 x_attribute15 => p_party_usg_assignment_rec.attribute15,
1651 x_attribute16 => p_party_usg_assignment_rec.attribute16,
1652 x_attribute17 => p_party_usg_assignment_rec.attribute17,
1653 x_attribute18 => p_party_usg_assignment_rec.attribute18,
1654 x_attribute19 => p_party_usg_assignment_rec.attribute19,
1655 x_attribute20 => p_party_usg_assignment_rec.attribute20,
1656 x_object_version_number => 1,
1657 x_created_by_module => p_party_usg_assignment_rec.created_by_module,
1658 x_application_id => fnd_global.resp_appl_id,
1659 x_party_usg_assignment_id => l_party_usg_assignment_id
1660 );
1661
1662 -- populate business object tracking table
1663 populate_bot(
1664 p_create_update_flag => 'I',
1665 p_party_usg_assignment_id => l_party_usg_assignment_id);
1666
1667 -- Debug info.
1668 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1669 hz_utility_v2pub.debug (
1670 p_message => c_api_name||' (-)',
1671 p_prefix => l_debug_prefix,
1672 p_msg_level => fnd_log.level_procedure);
1673 END IF;
1674
1675 END insert_row;
1676
1677
1678 /**
1679 * PROCEDURE update_row
1680 *
1681 * DESCRIPTION
1682 * Update a new assignment.
1683 *
1684 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1685 *
1686 * NOTES
1687 *
1688 * MODIFICATION HISTORY
1689 *
1690 * 05/01/05 Jianying Huang o Created.
1691 *
1692 */
1693
1694 PROCEDURE update_row (
1695 p_party_usg_assignment_id IN NUMBER,
1696 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
1697 p_object_version_number IN OUT NOCOPY NUMBER,
1698 p_old_object_version_number IN NUMBER,
1699 p_status IN VARCHAR2
1700 ) IS
1701
1702 c_api_name CONSTANT VARCHAR2(30) := 'update_row';
1703 l_debug_prefix VARCHAR2(30);
1704
1705 CURSOR c_assignment (
1706 p_party_usg_assignment_id NUMBER
1707 ) IS
1708 SELECT object_version_number
1709 FROM hz_party_usg_assignments
1710 WHERE party_usg_assignment_id = p_party_usg_assignment_id
1711 FOR UPDATE NOWAIT;
1712
1713 l_object_version_number NUMBER;
1714
1715 BEGIN
1716
1717 l_debug_prefix := '';
1718
1719 -- Debug info.
1720 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1721 hz_utility_v2pub.debug (
1722 p_message => c_api_name||' (+)',
1723 p_prefix => l_debug_prefix,
1724 p_msg_level => fnd_log.level_procedure);
1725 END IF;
1726
1727 OPEN c_assignment(p_party_usg_assignment_id);
1728 FETCH c_assignment INTO l_object_version_number;
1729 CLOSE c_assignment;
1730
1731 IF p_object_version_number IS NOT NULL THEN
1732 IF p_old_object_version_number IS NOT NULL THEN
1733 l_object_version_number := p_old_object_version_number;
1734 END IF;
1735
1736 IF p_object_version_number <> l_object_version_number THEN
1737 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
1738 fnd_message.set_token('TABLE', 'hz_party_usg_assignments');
1739 fnd_msg_pub.add;
1740 RAISE fnd_api.g_exc_error;
1741 END IF;
1742 END IF;
1743
1744 p_object_version_number := l_object_version_number + 1;
1745
1746 HZ_PARTY_USG_ASSIGNMENTS_PKG.update_row (
1747 x_party_usg_assignment_id => p_party_usg_assignment_id,
1748 x_party_id => null,
1749 x_party_usage_code => null,
1750 x_effective_start_date => p_party_usg_assignment_rec.effective_start_date,
1751 x_effective_end_date => p_party_usg_assignment_rec.effective_end_date,
1752 x_status_flag => p_status,
1753 x_comments => p_party_usg_assignment_rec.comments,
1754 x_owner_table_name => null,
1755 x_owner_table_id => null,
1756 x_attribute_category => p_party_usg_assignment_rec.attribute_category,
1757 x_attribute1 => p_party_usg_assignment_rec.attribute1,
1758 x_attribute2 => p_party_usg_assignment_rec.attribute2,
1759 x_attribute3 => p_party_usg_assignment_rec.attribute3,
1760 x_attribute4 => p_party_usg_assignment_rec.attribute4,
1761 x_attribute5 => p_party_usg_assignment_rec.attribute5,
1762 x_attribute6 => p_party_usg_assignment_rec.attribute6,
1763 x_attribute7 => p_party_usg_assignment_rec.attribute7,
1764 x_attribute8 => p_party_usg_assignment_rec.attribute8,
1765 x_attribute9 => p_party_usg_assignment_rec.attribute9,
1766 x_attribute10 => p_party_usg_assignment_rec.attribute10,
1767 x_attribute11 => p_party_usg_assignment_rec.attribute11,
1768 x_attribute12 => p_party_usg_assignment_rec.attribute12,
1769 x_attribute13 => p_party_usg_assignment_rec.attribute13,
1770 x_attribute14 => p_party_usg_assignment_rec.attribute14,
1771 x_attribute15 => p_party_usg_assignment_rec.attribute15,
1772 x_attribute16 => p_party_usg_assignment_rec.attribute16,
1773 x_attribute17 => p_party_usg_assignment_rec.attribute17,
1774 x_attribute18 => p_party_usg_assignment_rec.attribute18,
1775 x_attribute19 => p_party_usg_assignment_rec.attribute19,
1776 x_attribute20 => p_party_usg_assignment_rec.attribute20,
1777 x_object_version_number => p_object_version_number
1778 );
1779
1780 -- populate business object tracking table
1781 populate_bot(
1782 p_create_update_flag => 'U',
1783 p_party_usg_assignment_id => p_party_usg_assignment_id);
1784
1785 -- Debug info.
1786 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1787 hz_utility_v2pub.debug (
1788 p_message => c_api_name||' (-)',
1789 p_prefix => l_debug_prefix,
1790 p_msg_level => fnd_log.level_procedure);
1791 END IF;
1792
1793 END update_row;
1794
1795
1796 /**
1797 * PROCEDURE split
1798 *
1799 * DESCRIPTION
1800 * Split a string via delimiter.
1801 *
1802 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1803 *
1804 * NOTES
1805 *
1806 * MODIFICATION HISTORY
1807 *
1808 * 05/01/05 Jianying Huang o Created.
1809 *
1810 */
1811
1812 PROCEDURE split (
1813 p_string IN VARCHAR2,
1814 p_delimiter IN VARCHAR2,
1815 x_table OUT NOCOPY VARCHAR100_TBL
1816 ) IS
1817
1818 l_length NUMBER;
1819 l_dlength NUMBER;
1820 l_start NUMBER;
1821 l_counter NUMBER;
1822 l_index NUMBER;
1823
1824 BEGIN
1825
1826 x_table := VARCHAR100_TBL();
1827 l_length := lengthb(p_string);
1828 l_dlength := lengthb(p_delimiter);
1829
1830 l_start := 1; l_counter := 1; l_index := 1;
1831 WHILE (l_start <= l_length AND l_index > 0)
1832 LOOP
1833 l_index := instrb(p_string, p_delimiter, l_start);
1834 IF l_index <> 0 THEN
1835 x_table.extend(1);
1836 x_table(l_counter) := substr(p_string, l_start, l_index - l_start);
1837 l_start := l_index + l_dlength;
1838 l_counter := l_counter + 1;
1839 END IF;
1840 END LOOP;
1841
1842 IF l_start <= l_length THEN
1843 x_table.extend(1);
1844 x_table(l_counter) := substrb(p_string, l_start);
1845 END IF;
1846
1847 END split;
1848
1849
1850 --------------------------------------
1851 -- public procedures and functions
1852 --------------------------------------
1853
1854 /**
1855 * PROCEDURE assign_party_usage
1856 *
1857 * DESCRIPTION
1858 * Creates party usage assignment.
1859 *
1860 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1861 *
1862 * ARGUMENTS
1863 * IN:
1864 * p_init_msg_list Initialize message stack if it is set to
1865 * fnd_api.G_TRUE. Default is fnd_api.G_FALSE.
1866 * p_validation_level Validation level. Default is full validation.
1867 * p_party_usg_assignment_rec Party usage assignment record.
1868 * IN/OUT:
1869 * OUT:
1870 * x_return_status Return status after the call. The status can
1871 * be fnd_api.G_RET_STS_SUCCESS (success),
1872 * fnd_api.G_RET_STS_ERROR (error),
1873 * fnd_api.G_RET_STS_UNEXP_ERROR (unexpected error).
1874 * x_msg_count Number of messages in message stack.
1875 * x_msg_data Message text if x_msg_count is 1.
1876 *
1877 * NOTES
1878 *
1879 * MODIFICATION HISTORY
1880 *
1881 * 05/01/05 Jianying Huang o Created.
1882 *
1883 */
1884
1885 PROCEDURE assign_party_usage (
1886 p_init_msg_list IN VARCHAR2,
1887 p_validation_level IN NUMBER,
1888 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
1889 x_return_status OUT NOCOPY VARCHAR2,
1890 x_msg_count OUT NOCOPY NUMBER,
1891 x_msg_data OUT NOCOPY VARCHAR2
1892 ) IS
1893
1894 c_api_name CONSTANT VARCHAR2(30) := 'assign_party_usage';
1895 l_debug_prefix VARCHAR2(30);
1896 l_validation_level NUMBER(3);
1897 l_party_usg_assignment_rec party_usg_assignment_rec_type;
1898
1899 BEGIN
1900
1901 -- standard start of API savepoint
1902 SAVEPOINT assign_party_usage;
1903
1904 l_debug_prefix := '';
1905
1906 -- Debug info.
1907 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1908 hz_utility_v2pub.debug (
1909 p_message => c_api_name||' (+)',
1910 p_prefix => l_debug_prefix,
1911 p_msg_level => fnd_log.level_procedure
1912 );
1913 END IF;
1914
1915 -- initialize message list if p_init_msg_list is set to TRUE.
1916 IF p_init_msg_list IS NOT NULL AND
1917 fnd_api.To_Boolean(p_init_msg_list)
1918 THEN
1919 fnd_msg_pub.initialize;
1920 END IF;
1921
1922 -- initialize validation level
1923 IF p_validation_level IS NULL THEN
1924 l_validation_level := G_VALID_LEVEL_FULL;
1925 ELSE
1926 l_validation_level := p_validation_level;
1927 END IF;
1928
1929 -- initialize API return status to success.
1930 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1931
1932 -- call to business logic.
1933 l_party_usg_assignment_rec := p_party_usg_assignment_rec;
1934
1935 do_assign_party_usage (
1936 p_validation_level => l_validation_level,
1937 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
1938 x_return_status => x_return_status
1939 );
1940
1941 -- standard call to get message count and if count is 1, get message info.
1942 fnd_msg_pub.Count_And_Get (
1943 p_encoded => fnd_api.G_FALSE,
1944 p_count => x_msg_count,
1945 p_data => x_msg_data);
1946
1947 -- Debug info.
1948 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1949 hz_utility_v2pub.debug_return_messages (
1950 p_msg_count => x_msg_count,
1951 p_msg_data => x_msg_data,
1952 p_msg_type => 'WARNING',
1953 p_msg_level => fnd_log.level_exception
1954 );
1955 END IF;
1956
1957 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1958 hz_utility_v2pub.debug (
1959 p_message => c_api_name||' (-)',
1960 p_prefix => l_debug_prefix,
1961 p_msg_level => fnd_log.level_procedure
1962 );
1963 END IF;
1964
1965 EXCEPTION
1966 WHEN fnd_api.G_EXC_ERROR THEN
1967 ROLLBACK TO assign_party_usage;
1968 x_return_status := fnd_api.G_RET_STS_ERROR;
1969
1970 fnd_msg_pub.Count_And_Get (
1971 p_encoded => fnd_api.G_FALSE,
1972 p_count => x_msg_count,
1973 p_data => x_msg_data
1974 );
1975
1976 -- Debug info.
1977 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1978 hz_utility_v2pub.debug_return_messages (
1979 p_msg_count => x_msg_count,
1980 p_msg_data => x_msg_data,
1981 p_msg_type => 'ERROR',
1982 p_msg_level => fnd_log.level_error
1983 );
1984 END IF;
1985
1986 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1987 hz_utility_v2pub.debug (
1988 p_message => c_api_name||' (-)',
1989 p_prefix => l_debug_prefix,
1990 p_msg_level => fnd_log.level_procedure
1991 );
1992 END IF;
1993
1994 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1995 ROLLBACK TO assign_party_usage;
1996 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1997 fnd_msg_pub.Count_And_Get (
1998 p_encoded => fnd_api.G_FALSE,
1999 p_count => x_msg_count,
2000 p_data => x_msg_data
2001 );
2002
2003 -- Debug info.
2004 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2005 hz_utility_v2pub.debug_return_messages (
2006 p_msg_count => x_msg_count,
2007 p_msg_data => x_msg_data,
2008 p_msg_type => 'UNEXPECTED ERROR',
2009 p_msg_level => fnd_log.level_error
2010 );
2011 END IF;
2012
2013 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2014 hz_utility_v2pub.debug (
2015 p_message => c_api_name||' (-)',
2016 p_prefix => l_debug_prefix,
2017 p_msg_level => fnd_log.level_procedure
2018 );
2019 END IF;
2020
2021 WHEN OTHERS THEN
2022 ROLLBACK TO assign_party_usage;
2023 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2024
2025 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2026 fnd_message.set_token('ERROR' ,SQLERRM);
2027 fnd_msg_pub.add;
2028
2029 fnd_msg_pub.Count_And_Get (
2030 p_encoded => fnd_api.G_FALSE,
2031 p_count => x_msg_count,
2032 p_data => x_msg_data
2033 );
2034
2035 -- Debug info.
2036 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2037 hz_utility_v2pub.debug_return_messages (
2038 p_msg_count => x_msg_count,
2039 p_msg_data => x_msg_data,
2040 p_msg_type => 'SQL ERROR',
2041 p_msg_level => fnd_log.level_error
2042 );
2043 END IF;
2044
2045 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2046 hz_utility_v2pub.debug (
2047 p_message => c_api_name||' (-)',
2048 p_prefix => l_debug_prefix,
2049 p_msg_level => fnd_log.level_procedure
2050 );
2051 END IF;
2052
2053 END assign_party_usage;
2054
2055
2056 /**
2057 * PROCEDURE get_usg_assignment
2058 *
2059 * DESCRIPTION
2060 * Get party usage assignment.
2061 *
2062 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2063 *
2064 * ARGUMENTS
2065 * IN:
2066 * p_init_msg_list Initialize message stack if it is set to
2067 * fnd_api.G_TRUE. Default is fnd_api.G_FALSE.
2068 * p_party_usg_assignment_id Party usage assignment Id.
2069 * p_party_usg_assignment_rec Party usage assignment record.
2070 * IN/OUT:
2071 * OUT:
2072 * x_usg_assignment_id_tbl Table of party usage assignment Id.
2073 * x_usg_assignment_rec_tbl Table of party usage assignment record.
2074 * x_return_status Return status after the call. The status can
2075 * be fnd_api.G_RET_STS_SUCCESS (success),
2076 * fnd_api.G_RET_STS_ERROR (error),
2077 * fnd_api.G_RET_STS_UNEXP_ERROR (unexpected error).
2078 * x_msg_count Number of messages in message stack.
2079 * x_msg_data Message text if x_msg_count is 1.
2080 *
2081 * NOTES
2082 *
2083 * MODIFICATION HISTORY
2084 *
2085 * 05/01/05 Jianying Huang o Created.
2086 *
2087 */
2088
2089 PROCEDURE get_usg_assignment (
2090 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
2091 p_party_usg_assignment_id IN NUMBER,
2092 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
2093 x_usg_assignment_id_tbl OUT NOCOPY NUMBER15_TBL,
2094 x_usg_assignment_rec_tbl OUT NOCOPY ASSIGNMENT_REC_TBL,
2095 x_return_status OUT NOCOPY VARCHAR2,
2096 x_msg_count OUT NOCOPY NUMBER,
2097 x_msg_data OUT NOCOPY VARCHAR2
2098 ) IS
2099
2100 c_api_name CONSTANT VARCHAR2(30) := 'get_usg_assignment';
2101 l_debug_prefix VARCHAR2(30);
2102
2103 TYPE assignment_cursor_type IS REF CURSOR RETURN hz_party_usg_assignments%ROWTYPE;
2104 c_assignment assignment_cursor_type;
2105 assignment_row hz_party_usg_assignments%ROWTYPE;
2106 l_counter NUMBER;
2107 l_search_by VARCHAR2(30);
2108
2109 BEGIN
2110
2111 l_debug_prefix := '';
2112
2113 -- Debug info.
2114 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2115 hz_utility_v2pub.debug (
2116 p_message => c_api_name||' (+)',
2117 p_prefix => l_debug_prefix,
2118 p_msg_level => fnd_log.level_procedure
2119 );
2120 END IF;
2121
2122 -- initialize message list if p_init_msg_list is set to TRUE.
2123 IF p_init_msg_list IS NOT NULL AND
2124 fnd_api.to_Boolean(p_init_msg_list)
2125 THEN
2126 fnd_msg_pub.initialize;
2127 END IF;
2128
2129 -- initialize API return status to success.
2130 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2131
2132 x_usg_assignment_id_tbl := NUMBER15_TBL();
2133 x_usg_assignment_rec_tbl := ASSIGNMENT_REC_TBL();
2134
2135 -- check assignment id
2136 --
2137 IF p_party_usg_assignment_id IS NOT NULL AND
2138 p_party_usg_assignment_id <> fnd_api.G_MISS_NUM
2139 THEN
2140 l_search_by := 'ASSIGNMENT_ID';
2141 OPEN c_assignment FOR
2142 SELECT *
2143 FROM hz_party_usg_assignments
2144 WHERE party_usg_assignment_id = p_party_usg_assignment_id;
2145 --
2146 -- check party_id, party_usage_code
2147 --
2148 ELSIF p_party_usg_assignment_rec.party_usage_code IS NOT NULL AND
2149 p_party_usg_assignment_rec.party_usage_code <> fnd_api.G_MISS_CHAR AND
2150 p_party_usg_assignment_rec.party_id IS NOT NULL AND
2151 p_party_usg_assignment_rec.party_id <> fnd_api.G_MISS_NUM
2152 THEN
2153 l_search_by := 'USAGE_CODE';
2154 OPEN c_assignment FOR
2155 SELECT *
2156 FROM hz_party_usg_assignments
2157 WHERE party_id = p_party_usg_assignment_rec.party_id
2158 AND party_usage_code = p_party_usg_assignment_rec.party_usage_code
2159 AND status_flag = 'A'
2160 AND effective_end_date > trunc(sysdate);
2161 --
2162 -- check owner_table_name, owner_table_id
2163 --
2164 ELSIF p_party_usg_assignment_rec.owner_table_name IS NOT NULL AND
2165 p_party_usg_assignment_rec.owner_table_name <> fnd_api.G_MISS_CHAR AND
2166 p_party_usg_assignment_rec.owner_table_id IS NOT NULL AND
2167 p_party_usg_assignment_rec.owner_table_id <> fnd_api.G_MISS_NUM
2168 THEN
2169 l_search_by := 'OWNER_TABLE_NAME';
2170 OPEN c_assignment FOR
2171 SELECT *
2172 FROM hz_party_usg_assignments
2173 WHERE owner_table_name = p_party_usg_assignment_rec.owner_table_name
2174 AND owner_table_id = p_party_usg_assignment_rec.owner_table_id;
2175 -- need required parameters
2176 --
2177 ELSE
2178 fnd_message.set_name('AR', 'HZ_PU_MISSING_COLUMN');
2179 fnd_msg_pub.add;
2180 RAISE fnd_api.G_EXC_ERROR;
2181 END IF;
2182
2183 l_counter := 0;
2184 LOOP
2185 FETCH c_assignment INTO assignment_row;
2186 EXIT WHEN c_assignment%NOTFOUND;
2187
2188 l_counter := l_counter + 1;
2189 IF l_counter > 1 AND
2190 l_search_by IN ('USAGE_CODE', 'ASSIGNMENT_ID')
2191 THEN
2192 EXIT;
2193 END IF;
2194
2195 x_usg_assignment_id_tbl.extend(1);
2196 x_usg_assignment_rec_tbl.extend(1);
2197
2198 x_usg_assignment_id_tbl(l_counter) := assignment_row.party_usg_assignment_id;
2199 x_usg_assignment_rec_tbl(l_counter).party_id := assignment_row.party_id;
2200 x_usg_assignment_rec_tbl(l_counter).party_usage_code := assignment_row.party_usage_code;
2201 x_usg_assignment_rec_tbl(l_counter).effective_start_date := assignment_row.effective_start_date;
2202 x_usg_assignment_rec_tbl(l_counter).effective_end_date := assignment_row.effective_end_date;
2203 x_usg_assignment_rec_tbl(l_counter).comments := assignment_row.comments;
2204 x_usg_assignment_rec_tbl(l_counter).owner_table_name := assignment_row.owner_table_name;
2205 x_usg_assignment_rec_tbl(l_counter).owner_table_id := assignment_row.owner_table_id;
2206 x_usg_assignment_rec_tbl(l_counter).created_by_module := assignment_row.created_by_module;
2207 x_usg_assignment_rec_tbl(l_counter).attribute_category := assignment_row.attribute_category;
2208 x_usg_assignment_rec_tbl(l_counter).attribute1 := assignment_row.attribute1;
2209 x_usg_assignment_rec_tbl(l_counter).attribute2 := assignment_row.attribute2;
2210 x_usg_assignment_rec_tbl(l_counter).attribute3 := assignment_row.attribute3;
2211 x_usg_assignment_rec_tbl(l_counter).attribute4 := assignment_row.attribute4;
2212 x_usg_assignment_rec_tbl(l_counter).attribute5 := assignment_row.attribute5;
2213 x_usg_assignment_rec_tbl(l_counter).attribute6 := assignment_row.attribute6;
2214 x_usg_assignment_rec_tbl(l_counter).attribute7 := assignment_row.attribute7;
2215 x_usg_assignment_rec_tbl(l_counter).attribute8 := assignment_row.attribute8;
2216 x_usg_assignment_rec_tbl(l_counter).attribute9 := assignment_row.attribute9;
2217 x_usg_assignment_rec_tbl(l_counter).attribute10 := assignment_row.attribute10;
2218 x_usg_assignment_rec_tbl(l_counter).attribute11 := assignment_row.attribute11;
2219 x_usg_assignment_rec_tbl(l_counter).attribute12 := assignment_row.attribute12;
2220 x_usg_assignment_rec_tbl(l_counter).attribute13 := assignment_row.attribute13;
2221 x_usg_assignment_rec_tbl(l_counter).attribute14 := assignment_row.attribute14;
2222 x_usg_assignment_rec_tbl(l_counter).attribute15 := assignment_row.attribute15;
2223 x_usg_assignment_rec_tbl(l_counter).attribute16 := assignment_row.attribute16;
2224 x_usg_assignment_rec_tbl(l_counter).attribute17 := assignment_row.attribute17;
2225 x_usg_assignment_rec_tbl(l_counter).attribute18 := assignment_row.attribute18;
2226 x_usg_assignment_rec_tbl(l_counter).attribute19 := assignment_row.attribute19;
2227 x_usg_assignment_rec_tbl(l_counter).attribute20 := assignment_row.attribute20;
2228
2229 END LOOP;
2230 CLOSE c_assignment;
2231
2232 --
2233 -- more than one assignment exist
2234 --
2235 IF l_counter > 1 AND
2236 l_search_by IN ('USAGE_CODE', 'ASSIGNMENT_ID')
2237 THEN
2238 fnd_message.set_name('AR', 'HZ_PU_MULTIPLE_ASSIGNMENT');
2239 fnd_msg_pub.add;
2240 RAISE fnd_api.G_EXC_ERROR;
2241 --
2242 -- no assignment exist
2243 --
2244 ELSIF l_counter = 0 THEN
2245 fnd_message.set_name('AR', 'HZ_PU_INVALID_ASSIGNMENT');
2246 fnd_msg_pub.add;
2247 RAISE fnd_api.G_EXC_ERROR;
2248 END IF;
2249
2250 -- standard call to get message count and if count is 1, get message info.
2251 fnd_msg_pub.Count_And_Get (
2252 p_encoded => fnd_api.G_FALSE,
2253 p_count => x_msg_count,
2254 p_data => x_msg_data);
2255
2256 -- Debug info.
2257 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2258 hz_utility_v2pub.debug_return_messages (
2259 p_msg_count => x_msg_count,
2260 p_msg_data => x_msg_data,
2261 p_msg_type => 'WARNING',
2262 p_msg_level => fnd_log.level_exception
2263 );
2264 END IF;
2265
2266 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2267 hz_utility_v2pub.debug (
2268 p_message => c_api_name||' (-)',
2269 p_prefix => l_debug_prefix,
2270 p_msg_level => fnd_log.level_procedure
2271 );
2272 END IF;
2273
2274 EXCEPTION
2275 WHEN fnd_api.G_EXC_ERROR THEN
2276 x_return_status := fnd_api.G_RET_STS_ERROR;
2277
2278 fnd_msg_pub.Count_And_Get (
2279 p_encoded => fnd_api.G_FALSE,
2280 p_count => x_msg_count,
2281 p_data => x_msg_data
2282 );
2283
2284 -- Debug info.
2285 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2286 hz_utility_v2pub.debug_return_messages (
2287 p_msg_count => x_msg_count,
2288 p_msg_data => x_msg_data,
2289 p_msg_type => 'ERROR',
2290 p_msg_level => fnd_log.level_error
2291 );
2292 END IF;
2293
2294 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2295 hz_utility_v2pub.debug (
2296 p_message => c_api_name||' (-)',
2297 p_prefix => l_debug_prefix,
2298 p_msg_level => fnd_log.level_procedure
2299 );
2300 END IF;
2301
2302 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2303 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2304 fnd_msg_pub.Count_And_Get (
2305 p_encoded => fnd_api.G_FALSE,
2306 p_count => x_msg_count,
2307 p_data => x_msg_data
2308 );
2309
2310 -- Debug info.
2311 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2312 hz_utility_v2pub.debug_return_messages (
2313 p_msg_count => x_msg_count,
2314 p_msg_data => x_msg_data,
2315 p_msg_type => 'UNEXPECTED ERROR',
2316 p_msg_level => fnd_log.level_error
2317 );
2318 END IF;
2319
2320 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2321 hz_utility_v2pub.debug (
2322 p_message => c_api_name||' (-)',
2323 p_prefix => l_debug_prefix,
2324 p_msg_level => fnd_log.level_procedure
2325 );
2326 END IF;
2327
2328 WHEN OTHERS THEN
2329 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2330
2331 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2332 fnd_message.set_token('ERROR' ,SQLERRM);
2333 fnd_msg_pub.add;
2334
2335 fnd_msg_pub.Count_And_Get (
2336 p_encoded => fnd_api.G_FALSE,
2337 p_count => x_msg_count,
2338 p_data => x_msg_data
2339 );
2340
2341 -- Debug info.
2342 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2343 hz_utility_v2pub.debug_return_messages (
2344 p_msg_count => x_msg_count,
2345 p_msg_data => x_msg_data,
2346 p_msg_type => 'SQL ERROR',
2347 p_msg_level => fnd_log.level_error
2348 );
2349 END IF;
2350
2351 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2352 hz_utility_v2pub.debug (
2353 p_message => c_api_name||' (-)',
2354 p_prefix => l_debug_prefix,
2355 p_msg_level => fnd_log.level_procedure
2356 );
2357 END IF;
2358
2359 END get_usg_assignment;
2360
2361
2362 /**
2363 * PROCEDURE update_usg_assignment
2364 *
2365 * DESCRIPTION
2366 * Update party usage assignment.
2367 *
2368 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2369 *
2370 * ARGUMENTS
2371 * IN:
2372 * p_init_msg_list Initialize message stack if it is set to
2373 * fnd_api.G_TRUE. Default is fnd_api.G_FALSE.
2374 * p_validation_level Validation level. Default is full validation.
2375 * p_party_usg_assignment_id Party usage assignment Id.
2376 * p_party_usg_assignment_rec Party usage assignment record.
2377 * IN/OUT:
2378 * OUT:
2379 * x_return_status Return status after the call. The status can
2380 * be fnd_api.G_RET_STS_SUCCESS (success),
2381 * fnd_api.G_RET_STS_ERROR (error),
2382 * fnd_api.G_RET_STS_UNEXP_ERROR (unexpected error).
2383 * x_msg_count Number of messages in message stack.
2384 * x_msg_data Message text if x_msg_count is 1.
2385 *
2386 * NOTES
2387 *
2388 * MODIFICATION HISTORY
2389 *
2390 * 05/01/05 Jianying Huang o Created.
2391 *
2392 */
2393
2394 PROCEDURE update_usg_assignment (
2395 p_init_msg_list IN VARCHAR2,
2396 p_validation_level IN NUMBER,
2397 p_party_usg_assignment_id IN NUMBER,
2398 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
2399 x_return_status OUT NOCOPY VARCHAR2,
2400 x_msg_count OUT NOCOPY NUMBER,
2401 x_msg_data OUT NOCOPY VARCHAR2
2402 ) IS
2403
2404 c_api_name CONSTANT VARCHAR2(30) := 'update_usg_assignment';
2405 l_debug_prefix VARCHAR2(30);
2406 l_validation_level NUMBER(3);
2407 l_party_usg_assignment_rec party_usg_assignment_rec_type;
2408 l_usg_assignment_id_tbl NUMBER15_TBL;
2409 l_usg_assignment_rec_tbl ASSIGNMENT_REC_TBL;
2410
2411 BEGIN
2412
2413 -- standard start of API savepoint
2414 SAVEPOINT update_usg_assignment;
2415
2416 l_debug_prefix := '';
2417
2418 -- Debug info.
2419 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2420 hz_utility_v2pub.debug (
2421 p_message => c_api_name||' (+)',
2422 p_prefix => l_debug_prefix,
2423 p_msg_level => fnd_log.level_procedure
2424 );
2425 END IF;
2426
2427 -- initialize message list if p_init_msg_list is set to TRUE.
2428 IF p_init_msg_list IS NOT NULL AND
2429 fnd_api.to_Boolean(p_init_msg_list)
2430 THEN
2431 fnd_msg_pub.initialize;
2432 END IF;
2433
2434 -- initialize validation level
2435 IF p_validation_level IS NULL THEN
2436 l_validation_level := G_VALID_LEVEL_FULL;
2437 ELSE
2438 l_validation_level := p_validation_level;
2439 END IF;
2440
2441 -- initialize API return status to success.
2442 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2443
2444 -- get old assignment.
2445 get_usg_assignment(
2446 p_party_usg_assignment_id => p_party_usg_assignment_id,
2447 p_party_usg_assignment_rec => p_party_usg_assignment_rec,
2448 x_usg_assignment_id_tbl => l_usg_assignment_id_tbl,
2449 x_usg_assignment_rec_tbl => l_usg_assignment_rec_tbl,
2450 x_return_status => x_return_status,
2451 x_msg_count => x_msg_count,
2452 x_msg_data => x_msg_data
2453 );
2454
2455 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2456 RAISE fnd_api.G_EXC_ERROR;
2457 END IF;
2458
2459 -- call to business logic.
2460 l_party_usg_assignment_rec := p_party_usg_assignment_rec;
2461
2462 do_update_usg_assignment (
2463 p_validation_level => l_validation_level,
2464 p_usg_assignment_id_tbl => l_usg_assignment_id_tbl,
2465 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
2466 p_old_usg_assignment_rec_tbl => l_usg_assignment_rec_tbl,
2467 x_return_status => x_return_status
2468 );
2469
2470 -- standard call to get message count and if count is 1, get message info.
2471 fnd_msg_pub.Count_And_Get (
2472 p_encoded => fnd_api.G_FALSE,
2473 p_count => x_msg_count,
2474 p_data => x_msg_data);
2475
2476 -- Debug info.
2477 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2478 hz_utility_v2pub.debug_return_messages (
2479 p_msg_count => x_msg_count,
2480 p_msg_data => x_msg_data,
2481 p_msg_type => 'WARNING',
2482 p_msg_level => fnd_log.level_exception
2483 );
2484 END IF;
2485
2486 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2487 hz_utility_v2pub.debug (
2488 p_message => c_api_name||' (-)',
2489 p_prefix => l_debug_prefix,
2490 p_msg_level => fnd_log.level_procedure
2491 );
2492 END IF;
2493
2494 EXCEPTION
2495 WHEN fnd_api.G_EXC_ERROR THEN
2496 ROLLBACK TO update_usg_assignment;
2497 x_return_status := fnd_api.G_RET_STS_ERROR;
2498
2499 fnd_msg_pub.Count_And_Get (
2500 p_encoded => fnd_api.G_FALSE,
2501 p_count => x_msg_count,
2502 p_data => x_msg_data
2503 );
2504
2505 -- Debug info.
2506 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2507 hz_utility_v2pub.debug_return_messages (
2508 p_msg_count => x_msg_count,
2509 p_msg_data => x_msg_data,
2510 p_msg_type => 'ERROR',
2511 p_msg_level => fnd_log.level_error
2512 );
2513 END IF;
2514
2515 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2516 hz_utility_v2pub.debug (
2517 p_message => c_api_name||' (-)',
2518 p_prefix => l_debug_prefix,
2519 p_msg_level => fnd_log.level_procedure
2520 );
2521 END IF;
2522
2523 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2524 ROLLBACK TO update_usg_assignment;
2525 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2526 fnd_msg_pub.Count_And_Get (
2527 p_encoded => fnd_api.G_FALSE,
2528 p_count => x_msg_count,
2529 p_data => x_msg_data
2530 );
2531
2532 -- Debug info.
2533 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2534 hz_utility_v2pub.debug_return_messages (
2535 p_msg_count => x_msg_count,
2536 p_msg_data => x_msg_data,
2537 p_msg_type => 'UNEXPECTED ERROR',
2538 p_msg_level => fnd_log.level_error
2539 );
2540 END IF;
2541
2542 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2543 hz_utility_v2pub.debug (
2544 p_message => c_api_name||' (-)',
2545 p_prefix => l_debug_prefix,
2546 p_msg_level => fnd_log.level_procedure
2547 );
2548 END IF;
2549
2550 WHEN OTHERS THEN
2551 ROLLBACK TO update_usg_assignment;
2552 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2553
2554 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2555 fnd_message.set_token('ERROR' ,SQLERRM);
2556 fnd_msg_pub.add;
2557
2558 fnd_msg_pub.Count_And_Get (
2559 p_encoded => fnd_api.G_FALSE,
2560 p_count => x_msg_count,
2561 p_data => x_msg_data
2562 );
2563
2564 -- Debug info.
2565 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2566 hz_utility_v2pub.debug_return_messages (
2567 p_msg_count => x_msg_count,
2568 p_msg_data => x_msg_data,
2569 p_msg_type => 'SQL ERROR',
2570 p_msg_level => fnd_log.level_error
2571 );
2572 END IF;
2573
2574 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2575 hz_utility_v2pub.debug (
2576 p_message => c_api_name||' (-)',
2577 p_prefix => l_debug_prefix,
2578 p_msg_level => fnd_log.level_procedure
2579 );
2580 END IF;
2581
2582 END update_usg_assignment;
2583
2584
2585 /**
2586 * PROCEDURE inactivate_usg_assignment
2587 *
2588 * DESCRIPTION
2589 * Inactivates party usage assignment.
2590 *
2591 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2592 *
2593 * ARGUMENTS
2594 * IN:
2595 * p_init_msg_list Initialize message stack if it is set to
2596 * fnd_api.G_TRUE. Default is fnd_api.G_FALSE.
2597 * p_validation_level Validation level. Default is full validation.
2598 * p_party_id Party Id
2599 * p_party_usage_code Party usage code
2600 * IN/OUT:
2601 * OUT:
2602 * x_return_status Return status after the call. The status can
2603 * be fnd_api.G_RET_STS_SUCCESS (success),
2604 * fnd_api.G_RET_STS_ERROR (error),
2605 * fnd_api.G_RET_STS_UNEXP_ERROR (unexpected error).
2606 * x_msg_count Number of messages in message stack.
2607 * x_msg_data Message text if x_msg_count is 1.
2608 *
2609 * NOTES
2610 *
2611 * MODIFICATION HISTORY
2612 *
2613 * 05/01/05 Jianying Huang o Created.
2614 *
2615 */
2616
2617 PROCEDURE inactivate_usg_assignment (
2618 p_init_msg_list IN VARCHAR2,
2619 p_validation_level IN NUMBER,
2620 p_party_usg_assignment_id IN NUMBER,
2621 p_party_id IN NUMBER,
2622 p_party_usage_code IN VARCHAR2,
2623 x_return_status OUT NOCOPY VARCHAR2,
2624 x_msg_count OUT NOCOPY NUMBER,
2625 x_msg_data OUT NOCOPY VARCHAR2
2626 ) IS
2627
2628 c_api_name CONSTANT VARCHAR2(30) := 'inactivate_usg_assignment';
2629 l_debug_prefix VARCHAR2(30);
2630 l_party_usg_assignment_rec party_usg_assignment_rec_type;
2631 l_success VARCHAR2(1);
2632
2633 BEGIN
2634
2635 l_debug_prefix := '';
2636
2637 -- Debug info.
2638 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2639 hz_utility_v2pub.debug (
2640 p_message => c_api_name||' (+)',
2641 p_prefix => l_debug_prefix,
2642 p_msg_level => fnd_log.level_procedure
2643 );
2644 END IF;
2645
2646 l_party_usg_assignment_rec.party_id := p_party_id;
2647 l_party_usg_assignment_rec.party_usage_code := p_party_usage_code;
2648 l_party_usg_assignment_rec.effective_end_date := trunc(sysdate);
2649
2650 update_usg_assignment (
2651 p_init_msg_list => p_init_msg_list,
2652 p_validation_level => p_validation_level,
2653 p_party_usg_assignment_id => p_party_usg_assignment_id,
2654 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
2655 x_return_status => x_return_status,
2656 x_msg_count => x_msg_count,
2657 x_msg_data => x_msg_data
2658 );
2659
2660 -- replace error message
2661 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2662 fnd_message.set_name('AR', 'HZ_PU_WRONG_API');
2663 fnd_msg_pub.Set_Search_Name('AR', 'HZ_PU_MULTIPLE_ASSIGNMENT');
2664 l_success := fnd_msg_pub.Change_Msg;
2665
2666 IF l_success = 'T' THEN
2667 -- standard call to get message count and if count is 1, get message info.
2668 fnd_msg_pub.Count_And_Get (
2669 p_encoded => fnd_api.G_FALSE,
2670 p_count => x_msg_count,
2671 p_data => x_msg_data);
2672 END IF;
2673 END IF;
2674
2675 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2676 hz_utility_v2pub.debug (
2677 p_message => c_api_name||' (-)',
2678 p_prefix => l_debug_prefix,
2679 p_msg_level => fnd_log.level_procedure
2680 );
2681 END IF;
2682
2683 END inactivate_usg_assignment;
2684
2685
2686 /**
2687 * PROCEDURE refresh
2688 *
2689 * DESCRIPTION
2690 * Refresh the cached setup. Need to be called when the party usage setup
2691 * is changed via admin UI.
2692 *
2693 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2694 *
2695 * NOTES
2696 *
2697 * MODIFICATION HISTORY
2698 *
2699 * 05/01/05 Jianying Huang o Created.
2700 *
2701 */
2702
2703 PROCEDURE refresh IS
2704
2705 BEGIN
2706
2707 G_SETUP_LOADED := 0;
2708
2709 END refresh;
2710
2711
2712 /**
2713 * PROCEDURE set_calling_api
2714 *
2715 * DESCRIPTION
2716 * Set calling api. Internal use only.
2717 *
2718 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2719 *
2720 * NOTES
2721 *
2722 * MODIFICATION HISTORY
2723 *
2724 * 05/01/05 Jianying Huang o Created.
2725 *
2726 */
2727
2728 PROCEDURE set_calling_api (
2729 p_calling_api IN VARCHAR2
2730 ) IS
2731
2732 BEGIN
2733
2734 G_CALLING_API := p_calling_api;
2735
2736 END set_calling_api;
2737
2738
2739 /**
2740 * FUNCTION allow_party_merge
2741 *
2742 * DESCRIPTION
2743 * Created for party merge. Check party usage
2744 * rules to determine if merge is allowed.
2745 *
2746 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2747 *
2748 * NOTES
2749 *
2750 * MODIFICATION HISTORY
2751 *
2752 * 07/19/05 Jianying Huang o Created.
2753 *
2754 */
2755
2756 FUNCTION allow_party_merge (
2757 p_init_msg_list IN VARCHAR2,
2758 p_from_party_id IN NUMBER,
2759 p_to_party_id IN NUMBER,
2760 x_msg_count OUT NOCOPY NUMBER,
2761 x_msg_data OUT NOCOPY VARCHAR2
2762 ) RETURN VARCHAR2 IS
2763
2764 c_api_name CONSTANT VARCHAR2(30) := 'allow_party_merge';
2765 l_debug_prefix VARCHAR2(30);
2766 l_allow_party_merge VARCHAR2(1);
2767
2768 CURSOR c_assignments (
2769 p_party_id NUMBER
2770 ) IS
2771 SELECT UNIQUE party_usage_code
2772 FROM hz_party_usg_assignments
2773 WHERE party_id = p_party_id;
2774
2775 l_from_party_usage_codes_tbl VARCHAR100_TBL;
2776 l_to_party_usage_codes_tbl VARCHAR100_TBL;
2777 l_continue_i VARCHAR2(1);
2778 l_continue_j VARCHAR2(1);
2779 i NUMBER;
2780 j NUMBER;
2781
2782 BEGIN
2783
2784 l_debug_prefix := '';
2785 l_allow_party_merge := 'Y';
2786
2787 -- Debug info.
2788 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2789 hz_utility_v2pub.debug (
2790 p_message => c_api_name||' (+)',
2791 p_prefix => l_debug_prefix,
2792 p_msg_level => fnd_log.level_procedure
2793 );
2794 END IF;
2795
2796 -- initialize message list if p_init_msg_list is set to TRUE.
2797 IF p_init_msg_list IS NOT NULL AND
2798 fnd_api.to_Boolean(p_init_msg_list)
2799 THEN
2800 fnd_msg_pub.initialize;
2801 END IF;
2802
2803 -- load setup data
2804 IF G_SETUP_LOADED = 0 THEN
2805 initialize;
2806 END IF;
2807
2808 --
2809 -- check party usage rules
2810 --
2811 IF G_SETUP_LOADED = 3 THEN
2812 OPEN c_assignments(p_from_party_id);
2813 FETCH c_assignments BULK COLLECT INTO
2814 l_from_party_usage_codes_tbl;
2815 CLOSE c_assignments;
2816
2817 IF l_from_party_usage_codes_tbl.count > 0 THEN
2818 OPEN c_assignments(p_to_party_id);
2819 FETCH c_assignments BULK COLLECT INTO
2820 l_to_party_usage_codes_tbl;
2821 CLOSE c_assignments;
2822
2823 IF l_to_party_usage_codes_tbl.count > 0 THEN
2824 --
2825 -- the following check are needed only when there
2826 -- are some existing assignments
2827 --
2828 l_continue_i := 'Y'; i := 1;
2829 WHILE (i <= l_from_party_usage_codes_tbl.count AND
2830 l_continue_i = 'Y')
2831 LOOP
2832 -- Debug info.
2833 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2834 hz_utility_v2pub.debug(
2835 p_prefix => l_debug_prefix,
2836 p_message => 'l_from_party_usage_codes_tbl('||i||') = '||
2837 l_from_party_usage_codes_tbl(i),
2838 p_msg_level => fnd_log.level_statement);
2839 END IF;
2840
2841 l_continue_j := 'Y'; j := 1;
2842 WHILE (j <= l_to_party_usage_codes_tbl.count AND
2843 l_continue_j = 'Y')
2844 LOOP
2845 -- Debug info.
2846 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2847 hz_utility_v2pub.debug(
2848 p_prefix => l_debug_prefix,
2849 p_message => 'l_to_party_usage_codes_tbl('||j||') = '||
2850 l_to_party_usage_codes_tbl(j),
2851 p_msg_level => fnd_log.level_statement);
2852 END IF;
2853
2854 --
2855 -- check exclusive rule
2856 -- check co-exist rule
2857 --
2858 IF (violate_exclusive_rules(
2859 l_from_party_usage_codes_tbl(i),
2860 l_to_party_usage_codes_tbl(j)) OR
2861 violate_coexist_rules(
2862 l_from_party_usage_codes_tbl(i),
2863 l_to_party_usage_codes_tbl(j)))
2864 THEN
2865 fnd_message.set_name('AR', 'HZ_PU_EXCLUSIVE_RULE_FAILED');
2866 fnd_message.set_token('EXISTING_PARTY_USAGE_CODE', l_from_party_usage_codes_tbl(i));
2867 fnd_message.set_token('NEW_PARTY_USAGE_CODE', l_to_party_usage_codes_tbl(j));
2868 fnd_msg_pub.add;
2869
2870 l_continue_j := 'N'; l_continue_i := 'N';
2871 l_allow_party_merge := 'N';
2872 ELSE
2873 j := j + 1;
2874 END IF;
2875
2876 END LOOP;
2877
2878 i := i + 1;
2879
2880 END LOOP;
2881 END IF; -- to party has assignments
2882 END IF; -- from party has assignments
2883 END IF; -- has rules defined.
2884
2885 -- standard call to get message count and if count is 1, get message info.
2886 fnd_msg_pub.Count_And_Get (
2887 p_encoded => fnd_api.G_FALSE,
2888 p_count => x_msg_count,
2889 p_data => x_msg_data);
2890
2891 -- Debug info.
2892 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2893 hz_utility_v2pub.debug_return_messages (
2894 p_msg_count => x_msg_count,
2895 p_msg_data => x_msg_data,
2896 p_msg_type => 'ERROR',
2897 p_msg_level => fnd_log.level_exception
2898 );
2899 END IF;
2900
2901 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2902 hz_utility_v2pub.debug (
2903 p_message => c_api_name||' (-)',
2904 p_prefix => l_debug_prefix,
2905 p_msg_level => fnd_log.level_procedure
2906 );
2907 END IF;
2908
2909 RETURN l_allow_party_merge;
2910
2911 END allow_party_merge;
2912
2913
2914 /**
2915 * FUNCTION find_duplicates
2916 *
2917 * DESCRIPTION
2918 * Created for party merge. Find duplicate assignment.
2919 *
2920 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2921 *
2922 * NOTES
2923 *
2924 * MODIFICATION HISTORY
2925 *
2926 * 07/19/05 Jianying Huang o Created.
2927 *
2928 */
2929
2930 PROCEDURE find_duplicates (
2931 p_from_assignment_id IN NUMBER,
2932 p_to_party_id IN NUMBER,
2933 x_to_assignment_id OUT NOCOPY NUMBER
2934 ) IS
2935
2936 c_api_name CONSTANT VARCHAR2(30) := 'find_duplicates';
2937 l_debug_prefix VARCHAR2(30);
2938 l_party_usg_assignment_rec party_usg_assignment_rec_type;
2939 l_usg_assignment_id_tbl NUMBER15_TBL;
2940 l_usg_assignment_rec_tbl ASSIGNMENT_REC_TBL;
2941 l_has_duplicates VARCHAR2(1);
2942 x_return_status VARCHAR2(1);
2943 x_msg_count NUMBER;
2944 x_msg_data VARCHAR2(2000);
2945
2946 BEGIN
2947 -- Debug info.
2948 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2949 hz_utility_v2pub.debug (
2950 p_message => c_api_name||' (+)',
2951 p_prefix => l_debug_prefix,
2952 p_msg_level => fnd_log.level_procedure
2953 );
2954 END IF;
2955
2956 -- get old assignment.
2957 get_usg_assignment(
2958 p_party_usg_assignment_id => p_from_assignment_id,
2959 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
2960 x_usg_assignment_id_tbl => l_usg_assignment_id_tbl,
2961 x_usg_assignment_rec_tbl => l_usg_assignment_rec_tbl,
2962 x_return_status => x_return_status,
2963 x_msg_count => x_msg_count,
2964 x_msg_data => x_msg_data
2965 );
2966
2967 IF l_usg_assignment_rec_tbl.count = 1 THEN
2968 l_party_usg_assignment_rec := l_usg_assignment_rec_tbl(1);
2969 l_party_usg_assignment_rec.party_id := p_to_party_id;
2970
2971 l_has_duplicates := duplicates_exist(l_party_usg_assignment_rec, x_to_assignment_id);
2972 END IF;
2973
2974 -- Debug info.
2975 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2976 hz_utility_v2pub.debug (
2977 p_message => c_api_name||' (-)',
2978 p_prefix => l_debug_prefix,
2979 p_msg_level => fnd_log.level_procedure
2980 );
2981 END IF;
2982
2983 END find_duplicates;
2984
2985
2986 /**
2987 * PROCEDURE validate_supplier_name
2988 *
2989 * DESCRIPTION
2990 * Validate supplier name.
2991 *
2992 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2993 *
2994 * ARGUMENTS
2995 * IN:
2996 * p_party_id party id
2997 * p_party_name party name
2998 * x_return_status return status
2999 *
3000 * NOTES
3001 *
3002 * MODIFICATION HISTORY
3003 */
3004
3005 PROCEDURE validate_supplier_name (
3006 p_party_id IN NUMBER,
3007 p_party_name IN VARCHAR2,
3008 x_return_status IN OUT NOCOPY VARCHAR2
3009 ) IS
3010
3011 c_supplier_code CONSTANT VARCHAR2(30) := 'SUPPLIER';
3012
3013 CURSOR c_party (
3014 p_party_id NUMBER,
3015 p_party_name VARCHAR2
3016 ) IS
3017 SELECT null
3018 FROM hz_parties p
3019 WHERE p.party_name = p_party_name
3020 AND p.party_type = 'ORGANIZATION'
3021 AND p.party_id <> p_party_id
3022 AND p.status IN ('A', 'I')
3023 AND EXISTS (
3024 SELECT null
3025 FROM hz_party_usg_assignments pu
3026 WHERE pu.party_usage_code = c_supplier_code
3027 AND pu.party_id = p.party_id
3028 AND ROWNUM = 1)
3029 AND ROWNUM = 1;
3030
3031 l_dummy VARCHAR2(1);
3032
3033 BEGIN
3034
3035 -- check uniqueness across supplier parties
3036 OPEN c_party(p_party_id, p_party_name);
3037 FETCH c_party INTO l_dummy;
3038 IF c_party%FOUND THEN
3039 fnd_message.set_name('AR', 'HZ_NONUNIQUE_SUPPLIER_NAME');
3040 fnd_msg_pub.add;
3041 x_return_status := fnd_api.g_ret_sts_error;
3042 END IF;
3043 CLOSE c_party;
3044
3045 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3046 hz_utility_v2pub.debug(
3047 p_prefix => '',
3048 p_message => 'after validate supplier name uniqueness ... ' ||
3049 'x_return_status = ' || x_return_status,
3050 p_msg_level => fnd_log.level_statement);
3051 END IF;
3052
3053 END validate_supplier_name;
3054
3055
3056 END HZ_PARTY_USG_ASSIGNMENT_PVT;