[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.12 2008/06/11 06:46:36 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');
448 fnd_msg_pub.add;
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');
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,
563 END IF;
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);
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||' '||
680
677 'party_type = '||db_party_type,
678 p_msg_level => fnd_log.level_statement);
679 END IF;
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 IF l_created_by NOT IN (0, 1, 2, 120, 121) AND
718 db_party_usage_created_by IN (0, 1, 2, 120, 121)
719 THEN
720 fnd_message.set_name('AR', 'HZ_PU_SEED_CBM_ASSIGN');
721 fnd_message.set_token('PARTY_USAGE_CODE', l_party_usage_code);
722 fnd_msg_pub.add;
723 x_return_status := fnd_api.G_RET_STS_ERROR;
724 END IF;
725
726 -- Debug info.
727 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
728 hz_utility_v2pub.debug(
729 p_prefix => l_debug_prefix,
730 p_message => 'created_by_module is created by . '||
731 'l_created_by = '||l_created_by||' '||
732 'x_return_status = '||x_return_status,
733 p_msg_level => fnd_log.level_statement);
734 END IF;
735 END IF;
736
737 -- Bug 4586451
738 --
739 IF l_party_usage_code = 'SUPPLIER' AND
740 db_party_type='ORGANIZATION' AND
741 p_validation_level<>G_VALID_LEVEL_THIRD_MEDIUM
742 THEN
743 validate_supplier_name (
744 p_party_id => p_party_usg_assignment_rec.party_id,
745 p_party_name => db_party_name,
746 x_return_status => x_return_status);
747 END IF;
748
749 ELSE -- p_create_update_flag = 'U'
750 --Bug 7149894: Included 121 in user id validation while validating
751 -- Party Usage Assignment.
752 IF db_restrict_manual_update = 'Y' AND
753 G_CALLING_API = 'HZ_PARTY_USG_ASSIGNMENT_PUB' AND
754 db_party_usage_created_by IN (0, 1, 2, 120, 121)
755 THEN
756 fnd_message.set_name('AR', 'HZ_PU_SEED_CBM_UPDATE');
757 fnd_message.set_token('PARTY_USAGE_CODE', l_party_usage_code);
758 fnd_msg_pub.add;
759 x_return_status := fnd_api.G_RET_STS_ERROR;
760 END IF;
761
762 -- Debug info.
763 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
764 hz_utility_v2pub.debug(
765 p_prefix => l_debug_prefix,
766 p_message => 'manual update is Y. calling from public API. '||
767 'x_return_status = '||x_return_status,
768 p_msg_level => fnd_log.level_statement);
769 END IF;
770 END IF;
771
772 END IF; -- MEDIUM VALIDATION
773
774 --
775 -- LOW VALIDATION
776 --
777 IF p_validation_level >= G_VALID_LEVEL_LOW AND
778 p_create_update_flag = 'C'
779 THEN
780 --
781 -- check party usage rules
782 --
783 IF G_SETUP_LOADED = 3 THEN
784
785 OPEN c_assignments(p_party_usg_assignment_rec.party_id);
786 FETCH c_assignments BULK COLLECT INTO
787 l_party_usage_codes_tbl;
788 CLOSE c_assignments;
789
790 --
791 -- the following check are needed only when there
792 -- are some existing assignments
793 --
794 l_continue := 'Y'; i := 1;
795 WHILE (i <= l_party_usage_codes_tbl.count AND
796 l_continue = 'Y')
797 LOOP
798 -- Debug info.
799 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
800 hz_utility_v2pub.debug(
801 p_prefix => l_debug_prefix,
805 END IF;
802 p_message => 'l_party_usage_codes_tbl('||i||') = '||
803 l_party_usage_codes_tbl(i),
804 p_msg_level => fnd_log.level_statement);
806
807 IF NOT l_temp_party_usage_codes.exists(l_party_usage_codes_tbl(i)) AND
808 l_party_usage_codes_tbl(i) <> l_party_usage_code
809 THEN
810
811 -- store dupliate party usage codes into a temporary pl/sql table.
812 l_temp_party_usage_codes(l_party_usage_codes_tbl(i)) := 'Y';
813
814 --
815 -- check exclusive rule
816 -- check co-exist rule
817 --
818 IF (violate_exclusive_rules(
819 l_party_usage_code, l_party_usage_codes_tbl(i)) OR
820 violate_coexist_rules(
821 l_party_usage_code, l_party_usage_codes_tbl(i)))
822 THEN
823 fnd_message.set_name('AR', 'HZ_PU_EXCLUSIVE_RULE_FAILED');
824 fnd_message.set_token('EXISTING_PARTY_USAGE_CODE', l_party_usage_codes_tbl(i));
825 fnd_message.set_token('NEW_PARTY_USAGE_CODE', l_party_usage_code);
826 fnd_msg_pub.add;
827 x_return_status := fnd_api.G_RET_STS_ERROR;
828
829 l_continue := 'N';
830 END IF;
831
832 -- Debug info.
833 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
834 hz_utility_v2pub.debug(
835 p_prefix => l_debug_prefix,
836 p_message => 'check exclusive and co-exist rule. '||
837 'x_return_status = '||x_return_status,
838 p_msg_level => fnd_log.level_statement);
839 END IF;
840
841 END IF;
842
843 i := i + 1;
844 END LOOP;
845 END IF;
846
847 END IF; -- LOW VALIDATION
848
849 -- Debug info.
850 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
851 hz_utility_v2pub.debug (
852 p_message => c_api_name||' (-)',
853 p_prefix => l_debug_prefix,
854 p_msg_level => fnd_log.level_procedure);
855 END IF;
856
857 END validate_party_usg_assignment;
858
859
860 /**
861 * PRIVATE PROCEDURE duplicates_exist
862 *
863 * DESCRIPTION
864 * Private procedure to check if there is any duplicates
865 *
866 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
867 *
868 * MODIFICATION HISTORY
869 *
870 * 05/01/05 Jianying Huang o Created.
871 *
872 */
873
874 FUNCTION duplicates_exist (
875 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
876 x_party_usg_assignment_id OUT NOCOPY NUMBER
877 ) RETURN VARCHAR2 IS
878
879 c_api_name CONSTANT VARCHAR2(30) := 'duplicates_exist';
880 l_debug_prefix VARCHAR2(30);
881
882 -- search by owner_table_name/owner_table_id
883 CURSOR c_duplicate_assignment0 (
884 p_party_id NUMBER,
885 p_party_usage_code VARCHAR2,
886 p_owner_table_name VARCHAR2,
887 p_owner_table_id NUMBER
888 ) IS
889 SELECT party_usg_assignment_id
890 FROM hz_party_usg_assignments
891 WHERE owner_table_name = p_owner_table_name
892 AND owner_table_id = p_owner_table_id
893 AND party_id = p_party_id
894 AND party_usage_code = p_party_usage_code
895 AND rownum = 1;
896
897 -- search by party id/party usage code
898 CURSOR c_duplicate_assignment1 (
899 p_party_id NUMBER,
900 p_party_usage_code VARCHAR2,
901 p_effective_start_date DATE,
902 p_effective_end_date DATE
903 ) IS
904 SELECT party_usg_assignment_id
905 FROM hz_party_usg_assignments
906 WHERE party_id = p_party_id
907 AND party_usage_code = p_party_usage_code
908 AND status_flag = 'A'
909 AND p_effective_start_date BETWEEN
910 effective_start_date AND effective_end_date
911 AND effective_end_date >= p_effective_end_date
912 AND rownum = 1;
913
914 l_has_duplicates VARCHAR2(1);
915 l_assignment_id NUMBER(15);
916
917 BEGIN
918
919 l_debug_prefix := '';
920
921 -- Debug info.
922 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
923 hz_utility_v2pub.debug (
924 p_message => c_api_name||' (+)',
925 p_prefix => l_debug_prefix,
926 p_msg_level => fnd_log.level_procedure);
927 END IF;
928
929 l_has_duplicates := 'N';
930
931 -- check duplicate assignment
932 --
933 -- check owner_table_name, owner_table_id
934 --
935 IF p_party_usg_assignment_rec.owner_table_name IS NOT NULL AND
936 p_party_usg_assignment_rec.owner_table_name <> fnd_api.G_MISS_CHAR AND
937 p_party_usg_assignment_rec.owner_table_id IS NOT NULL AND
941 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
938 p_party_usg_assignment_rec.owner_table_id <> fnd_api.G_MISS_NUM
939 THEN
940 -- Debug info.
942 hz_utility_v2pub.debug(
943 p_prefix => l_debug_prefix,
944 p_message => 'owner_table_name, owner_table_id are passed in for this usage. '||
945 'Check duplicate assignment.',
946 p_msg_level => fnd_log.level_statement);
947 END IF;
948
949 OPEN c_duplicate_assignment0 (
950 p_party_usg_assignment_rec.party_id,
951 p_party_usg_assignment_rec.party_usage_code,
952 p_party_usg_assignment_rec.owner_table_name,
953 p_party_usg_assignment_rec.owner_table_id
954 );
955 FETCH c_duplicate_assignment0 INTO l_assignment_id;
956 IF c_duplicate_assignment0%FOUND THEN
957 -- duplicate exist. won't assign the current usage.
958 l_has_duplicates := 'Y';
959 x_party_usg_assignment_id := l_assignment_id;
960 END IF;
961 CLOSE c_duplicate_assignment0;
962 --
963 -- check party_id, party_usage_code, effective_start_date, effective_end_date
964 --
965 ELSIF p_party_usg_assignment_rec.party_id IS NOT NULL AND
966 p_party_usg_assignment_rec.party_id <> fnd_api.G_MISS_NUM AND
967 p_party_usg_assignment_rec.party_usage_code IS NOT NULL AND
968 p_party_usg_assignment_rec.party_usage_code <> fnd_api.G_MISS_CHAR
969 THEN
970 -- Debug info.
971 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
972 hz_utility_v2pub.debug(
973 p_prefix => l_debug_prefix,
974 p_message => 'No date tracking for this usage '||
975 p_party_usg_assignment_rec.party_usage_code||'. '||
976 'Check duplicate assignment.',
977 p_msg_level => fnd_log.level_statement);
978 END IF;
979
980 OPEN c_duplicate_assignment1 (
981 p_party_usg_assignment_rec.party_id,
982 p_party_usg_assignment_rec.party_usage_code,
983 p_party_usg_assignment_rec.effective_start_date,
984 p_party_usg_assignment_rec.effective_end_date
985 );
986 FETCH c_duplicate_assignment1 INTO l_assignment_id;
987 IF c_duplicate_assignment1%FOUND THEN
988 -- duplicate exist. won't assign the current usage.
989 l_has_duplicates := 'Y';
990 x_party_usg_assignment_id := l_assignment_id;
991 END IF;
992 CLOSE c_duplicate_assignment1;
993 END IF;
994
995 -- Debug info.
996 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
997 hz_utility_v2pub.debug(
998 p_prefix => l_debug_prefix,
999 p_message => 'l_has_duplicates = '||l_has_duplicates,
1000 p_msg_level => fnd_log.level_statement);
1001 END IF;
1002
1003 -- Debug info.
1004 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1005 hz_utility_v2pub.debug (
1006 p_message => c_api_name||' (-)',
1007 p_prefix => l_debug_prefix,
1008 p_msg_level => fnd_log.level_procedure);
1009 END IF;
1010
1011 return l_has_duplicates;
1012
1013 END duplicates_exist;
1014
1015
1016 /**
1017 * PRIVATE PROCEDURE do_assign_party_usage
1018 *
1019 * DESCRIPTION
1020 * Private procedure to create party usage assignment
1021 *
1022 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1023 *
1024 * MODIFICATION HISTORY
1025 *
1026 * 05/01/05 Jianying Huang o Created.
1027 *
1028 */
1029
1030 PROCEDURE do_assign_party_usage (
1031 p_validation_level IN NUMBER,
1032 p_party_usg_assignment_rec IN OUT NOCOPY party_usg_assignment_rec_type,
1033 x_return_status IN OUT NOCOPY VARCHAR2
1034 ) IS
1035
1036 c_api_name CONSTANT VARCHAR2(30) := 'do_assign_party_usage';
1037 l_debug_prefix VARCHAR2(30);
1038
1039 CURSOR c_assignments (
1040 p_party_id NUMBER
1041 ) IS
1042 SELECT party_usg_assignment_id,
1043 party_usage_code,
1044 effective_start_date
1045 FROM hz_party_usg_assignments
1046 WHERE party_id = p_party_id
1047 AND status_flag = 'A'
1048 AND trunc(sysdate) between
1049 effective_start_date and effective_end_date;
1050
1051 l_party_usg_assignment_id_tbl NUMBER15_TBL;
1052 l_party_usage_code_tbl VARCHAR100_TBL;
1053 l_start_date_tbl DATE_TBL;
1054 l_party_usg_assignment_rec party_usg_assignment_rec_type;
1055 l_object_version_number NUMBER;
1056 l_continue VARCHAR2(1);
1057 i NUMBER;
1058 l_has_duplicates VARCHAR2(1);
1059 l_dummy NUMBER(15);
1060 l_status VARCHAR2(1);
1061
1062
1063 BEGIN
1064
1065 l_debug_prefix := '';
1066
1067 -- Debug info.
1071 p_prefix => l_debug_prefix,
1068 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1069 hz_utility_v2pub.debug (
1070 p_message => c_api_name||' (+)',
1072 p_msg_level => fnd_log.level_procedure);
1073 END IF;
1074
1075 -- load setup data
1076 IF G_SETUP_LOADED = 0 THEN
1077 initialize;
1078 END IF;
1079
1080 -- check dates.
1081 IF p_party_usg_assignment_rec.effective_start_date IS NULL OR
1082 p_party_usg_assignment_rec.effective_start_date = fnd_api.G_MISS_DATE
1083 THEN
1084 p_party_usg_assignment_rec.effective_start_date := trunc(sysdate);
1085 ELSE
1086 p_party_usg_assignment_rec.effective_start_date :=
1087 trunc(p_party_usg_assignment_rec.effective_start_date);
1088 END IF;
1089
1090 IF p_party_usg_assignment_rec.effective_end_date IS NULL OR
1091 p_party_usg_assignment_rec.effective_end_date = fnd_api.G_MISS_DATE
1092 THEN
1093 p_party_usg_assignment_rec.effective_end_date := D_FUTURE_DATE;
1094 ELSE
1095 p_party_usg_assignment_rec.effective_end_date :=
1096 trunc(p_party_usg_assignment_rec.effective_end_date);
1097 END IF;
1098
1099 -- Debug info.
1100 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1101 hz_utility_v2pub.debug(
1102 p_prefix => l_debug_prefix,
1103 p_message => 'effective_start_date = '||
1104 TO_CHAR(p_party_usg_assignment_rec.effective_start_date, 'YYYY/MM/DD')||' '||
1105 'effective_end_date = '||
1106 TO_CHAR(p_party_usg_assignment_rec.effective_end_date, 'YYYY/MM/DD'),
1107 p_msg_level => fnd_log.level_statement);
1108 END IF;
1109
1110 --
1111 -- Do validation
1112 --
1113 IF p_validation_level > G_VALID_LEVEL_NONE THEN
1114 validate_party_usg_assignment (
1115 p_create_update_flag => 'C',
1116 p_validation_level => p_validation_level,
1117 p_party_usg_assignment_rec => p_party_usg_assignment_rec,
1118 p_old_usg_assignment_rec => l_party_usg_assignment_rec,
1119 x_return_status => x_return_status
1120 );
1121
1122 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1123 RAISE fnd_api.G_EXC_ERROR;
1124 END IF;
1125 END IF;
1126
1127 --
1128 -- return if there is any duplicates exist
1129 --
1130 l_has_duplicates := duplicates_exist(p_party_usg_assignment_rec, l_dummy);
1131
1132 IF l_has_duplicates = 'Y' THEN
1133 RETURN;
1134 END IF;
1135
1136 --
1137 -- handle transition rule. per talk with vinoo, we will not adjust the dates.
1138 -- we just inactivate existing assignments.
1139 --
1140 l_continue := 'Y';
1141
1142 IF G_SETUP_LOADED >= 2 THEN
1143
1144 OPEN c_assignments (p_party_usg_assignment_rec.party_id);
1145 FETCH c_assignments BULK COLLECT INTO
1146 l_party_usg_assignment_id_tbl,
1147 l_party_usage_code_tbl, l_start_date_tbl;
1148 CLOSE c_assignments;
1149
1150 SAVEPOINT party_usage_transition;
1151
1152 i := 1;
1153 WHILE i <= l_party_usg_assignment_id_tbl.count AND
1154 l_continue = 'Y'
1155 LOOP
1156 -- Bug 4954932: transition rule indicates that by assigning
1157 -- the related party usage, the existing usage will be end-dated
1158 IF has_transition_rules(
1159 l_party_usage_code_tbl(i),
1160 p_party_usg_assignment_rec.party_usage_code)
1161 THEN
1162 l_party_usg_assignment_rec.effective_end_date := trunc(sysdate);
1163 IF l_start_date_tbl(i) = trunc(sysdate) THEN
1164 l_status := 'I';
1165 ELSE
1166 l_status := 'A';
1167 END IF;
1168
1169 -- don't need to compare object version number here.
1170 l_object_version_number := null;
1171
1172 update_row (
1173 p_party_usg_assignment_id => l_party_usg_assignment_id_tbl(i),
1174 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
1175 p_object_version_number => l_object_version_number,
1176 p_old_object_version_number => null,
1177 p_status => l_status
1178 );
1179 -- Bug 4954932: transition rule indicates that by assigning
1180 -- the related party usage, the existing usage will be end-dated
1181 ELSIF G_PARTY_USAGE_RULES.exists(
1182 'TRANSITION##'||
1183 p_party_usg_assignment_rec.party_usage_code||'##'||
1184 l_party_usage_code_tbl(i))
1185 THEN
1186 l_continue := 'N';
1187 ROLLBACK TO party_usage_transition;
1188 END IF;
1189
1190 i := i + 1;
1191
1192 END LOOP;
1193
1194 END IF;
1195
1196 --
1197 -- create party usage assignment
1198 --
1199 IF l_continue = 'Y' THEN
1200 insert_row (
1201 p_party_usg_assignment_rec => p_party_usg_assignment_rec
1202 );
1203 END IF;
1204
1208 p_message => c_api_name||' (-)',
1205 -- Debug info.
1206 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1207 hz_utility_v2pub.debug (
1209 p_prefix => l_debug_prefix,
1210 p_msg_level => fnd_log.level_procedure);
1211 END IF;
1212
1213 END do_assign_party_usage;
1214
1215
1216 /**
1217 * PRIVATE PROCEDURE do_update_usg_assignment
1218 *
1219 * DESCRIPTION
1220 * Private procedure to update party usage assignment
1221 *
1222 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1223 *
1224 * MODIFICATION HISTORY
1225 *
1226 * 05/01/05 Jianying Huang o Created.
1227 *
1228 */
1229
1230 PROCEDURE do_update_usg_assignment (
1231 p_validation_level IN NUMBER,
1232 p_usg_assignment_id_tbl IN NUMBER15_TBL,
1233 p_party_usg_assignment_rec IN OUT NOCOPY party_usg_assignment_rec_type,
1234 p_old_usg_assignment_rec_tbl IN ASSIGNMENT_REC_TBL,
1235 x_return_status IN OUT NOCOPY VARCHAR2
1236 ) IS
1237
1238 c_api_name CONSTANT VARCHAR2(30) := 'do_update_usg_assignment';
1239 l_debug_prefix VARCHAR2(30);
1240 l_object_version_number NUMBER;
1241
1242 CURSOR c_assignments (
1243 p_party_id NUMBER
1244 ) IS
1245 SELECT party_usg_assignment_id,
1246 party_usage_code,
1247 effective_start_date
1248 FROM hz_party_usg_assignments
1249 WHERE party_id = p_party_id
1250 AND status_flag = 'A'
1251 AND trunc(sysdate) between
1252 effective_start_date and effective_end_date;
1253
1254 l_party_usg_assignment_id_tbl NUMBER15_TBL;
1255 l_party_usage_code_tbl VARCHAR100_TBL;
1256 l_start_date_tbl DATE_TBL;
1257 l_party_usg_assignment_rec party_usg_assignment_rec_type;
1258 l_object_version_number1 NUMBER;
1259 l_continue VARCHAR2(1);
1260 j NUMBER;
1261 l_status VARCHAR2(1);
1262
1263 BEGIN
1264
1265 l_debug_prefix := '';
1266
1267 -- Debug info.
1268 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1269 hz_utility_v2pub.debug (
1270 p_message => c_api_name||' (+)',
1271 p_prefix => l_debug_prefix,
1272 p_msg_level => fnd_log.level_procedure);
1273 END IF;
1274
1275 -- load setup data
1276 IF G_SETUP_LOADED = 0 THEN
1277 initialize;
1278 END IF;
1279
1280 FOR i IN 1..p_usg_assignment_id_tbl.count LOOP
1281 l_object_version_number := null;
1282 l_object_version_number1 := null;
1283
1284 -- check dates.
1285 IF p_party_usg_assignment_rec.effective_start_date IS NULL OR
1286 p_party_usg_assignment_rec.effective_start_date = fnd_api.G_MISS_DATE
1287 THEN
1288 p_party_usg_assignment_rec.effective_start_date :=
1289 p_old_usg_assignment_rec_tbl(i).effective_start_date;
1290 ELSE
1291 p_party_usg_assignment_rec.effective_start_date :=
1292 trunc(p_party_usg_assignment_rec.effective_start_date);
1293 END IF;
1294
1295 IF p_party_usg_assignment_rec.effective_end_date IS NOT NULL AND
1296 p_party_usg_assignment_rec.effective_end_date <> fnd_api.G_MISS_DATE
1297 THEN
1298 p_party_usg_assignment_rec.effective_end_date :=
1299 trunc(p_party_usg_assignment_rec.effective_end_date);
1300 ELSIF p_party_usg_assignment_rec.effective_end_date IS NULL THEN
1301 p_party_usg_assignment_rec.effective_end_date :=
1302 p_old_usg_assignment_rec_tbl(i).effective_end_date;
1303 ELSE
1304 p_party_usg_assignment_rec.effective_end_date := D_FUTURE_DATE;
1305 END IF;
1306
1307 -- Debug info.
1308 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1309 hz_utility_v2pub.debug(
1310 p_prefix => l_debug_prefix,
1311 p_message => 'effective_start_date = '||
1312 TO_CHAR(p_party_usg_assignment_rec.effective_start_date, 'YYYY/MM/DD')||' '||
1313 'effective_end_date = '||
1314 TO_CHAR(p_party_usg_assignment_rec.effective_end_date, 'YYYY/MM/DD'),
1315 p_msg_level => fnd_log.level_statement);
1316 END IF;
1317
1318 --
1319 -- Do validation
1320 --
1321 IF p_validation_level > G_VALID_LEVEL_NONE THEN
1322 validate_party_usg_assignment (
1323 p_create_update_flag => 'U',
1324 p_validation_level => p_validation_level,
1325 p_party_usg_assignment_rec => p_party_usg_assignment_rec,
1326 p_old_usg_assignment_rec => p_old_usg_assignment_rec_tbl(i),
1327 x_return_status => x_return_status
1328 );
1329
1330 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1331 RAISE fnd_api.G_EXC_ERROR;
1332 END IF;
1333 END IF;
1334
1335 --
1336 -- handle transition rule. per talk with vinoo, we will not adjust the dates.
1340
1337 -- we just inactivate existing assignments.
1338 --
1339 l_continue := 'Y';
1341 IF G_SETUP_LOADED >= 2 AND
1342 p_party_usg_assignment_rec.effective_end_date = D_FUTURE_DATE
1343 THEN
1344
1345 OPEN c_assignments (p_party_usg_assignment_rec.party_id);
1346 FETCH c_assignments BULK COLLECT INTO
1347 l_party_usg_assignment_id_tbl,
1348 l_party_usage_code_tbl, l_start_date_tbl;
1349 CLOSE c_assignments;
1350
1351 SAVEPOINT party_usage_transition;
1352
1353 j := 1;
1354 WHILE j <= l_party_usg_assignment_id_tbl.count AND
1355 l_continue = 'Y'
1356 LOOP
1357 -- Bug 4954932: transition rule indicates that by assigning
1358 -- the related party usage, the existing usage will be end-dated
1359 IF has_transition_rules(
1360 l_party_usage_code_tbl(j),
1361 p_party_usg_assignment_rec.party_usage_code)
1362 THEN
1363 l_party_usg_assignment_rec.effective_end_date := trunc(sysdate);
1364 IF l_start_date_tbl(j) = trunc(sysdate) THEN
1365 l_status := 'I';
1366 ELSE
1367 l_status := 'A';
1368 END IF;
1369
1370 -- don't need to compare object version number here.
1371 l_object_version_number1 := null;
1372
1373 update_row (
1374 p_party_usg_assignment_id => l_party_usg_assignment_id_tbl(j),
1375 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
1376 p_object_version_number => l_object_version_number1,
1377 p_old_object_version_number => null,
1378 p_status => l_status
1379 );
1380 -- Bug 4954932: transition rule indicates that by assigning
1381 -- the related party usage, the existing usage will be end-dated
1382 ELSIF G_PARTY_USAGE_RULES.exists(
1383 'TRANSITION##'||
1384 p_party_usg_assignment_rec.party_usage_code||'##'||
1385 l_party_usage_code_tbl(j))
1386 THEN
1387 l_continue := 'N';
1388 ROLLBACK TO party_usage_transition;
1389 END IF;
1390
1391 j := j + 1;
1392
1393 END LOOP;
1394 END IF;
1395
1396 --
1397 -- update party usage assignment
1398 --
1399 IF l_continue = 'Y' THEN
1400 update_row (
1401 p_party_usg_assignment_id => p_usg_assignment_id_tbl(i),
1402 p_party_usg_assignment_rec => p_party_usg_assignment_rec,
1403 p_object_version_number => l_object_version_number,
1404 p_old_object_version_number => null,
1405 p_status => null
1406 );
1407 END IF;
1408 END LOOP;
1409
1410 -- Debug info.
1411 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1412 hz_utility_v2pub.debug (
1413 p_message => c_api_name||' (-)',
1414 p_prefix => l_debug_prefix,
1415 p_msg_level => fnd_log.level_procedure);
1416 END IF;
1417
1418 END do_update_usg_assignment;
1419
1420
1421 /**
1422 * PROCEDURE initialize
1423 *
1424 * DESCRIPTION
1425 * cache setup.
1426 *
1427 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1428 *
1429 * NOTES
1430 *
1431 * MODIFICATION HISTORY
1432 *
1433 * 05/01/05 Jianying Huang o Created.
1434 *
1435 */
1436
1437 PROCEDURE initialize IS
1438
1439 c_api_name CONSTANT VARCHAR2(30) := 'initialize';
1440 l_debug_prefix VARCHAR2(30);
1441
1442 -- load party usages
1443 CURSOR c_party_usages IS
1444 SELECT party_usage_code,
1445 party_usage_type,
1446 status_flag,
1447 restrict_manual_assign_flag,
1448 restrict_manual_update_flag,
1449 created_by
1450 FROM hz_party_usages_b;
1451
1452 -- load rules
1453 CURSOR c_exist_exclusive_rules IS
1454 SELECT null
1455 FROM hz_party_usage_rules
1456 WHERE (party_usage_rule_type = 'EXCLUSIVE' OR
1457 party_usage_rule_type = 'CANNOT_COEXIST')
1458 AND trunc(sysdate) between
1459 effective_start_date AND effective_end_date
1460 AND rownum = 1;
1461
1462 CURSOR c_party_usage_rules IS
1463 SELECT party_usage_rule_type||'##'||
1464 party_usage_code||'##'||
1465 related_party_usage_code
1466 FROM hz_party_usage_rules
1467 WHERE trunc(sysdate) between
1468 effective_start_date AND effective_end_date;
1469
1470 -- load created by module
1471 CURSOR c_created_by_module IS
1472 SELECT lookup_code, created_by
1473 FROM fnd_lookup_values
1474 WHERE lookup_type = 'HZ_CREATED_BY_MODULES'
1475 AND view_application_id = 222
1476 AND language = userenv('LANG')
1477 AND enabled_flag = 'Y'
1478 AND trunc(sysdate) BETWEEN
1479 trunc(nvl(start_date_active, sysdate)) AND
1483 l_party_usage_type_tbl VARCHAR100_TBL;
1480 trunc(nvl(end_date_active, sysdate));
1481
1482 l_party_usages_tbl VARCHAR100_TBL;
1484 l_party_usage_status_tbl VARCHAR100_TBL;
1485 l_restrict_manual_assign_tbl VARCHAR100_TBL;
1486 l_restrict_manual_update_tbl VARCHAR100_TBL;
1487 l_party_usage_created_by_tbl NUMBER15_TBL;
1488 l_party_usage_rules_tbl VARCHAR100_TBL;
1489 l_created_by_module_tbl VARCHAR100_TBL;
1490 l_created_by_tbl NUMBER15_TBL;
1491 l_dummy VARCHAR2(1);
1492
1493 BEGIN
1494
1495 l_debug_prefix := '';
1496
1497 -- Debug info.
1498 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1499 hz_utility_v2pub.debug (
1500 p_message => c_api_name||' (+)',
1501 p_prefix => l_debug_prefix,
1502 p_msg_level => fnd_log.level_procedure);
1503 END IF;
1504
1505 G_PARTY_USAGE_CODES.delete;
1506 G_CREATED_BY_MODULES.delete;
1507 G_PARTY_USAGE_RULES.delete;
1508
1509 --
1510 -- load set up data
1511 --
1512
1513 -- load party usages
1514 OPEN c_party_usages;
1515 FETCH c_party_usages BULK COLLECT INTO
1516 l_party_usages_tbl, l_party_usage_type_tbl,
1517 l_party_usage_status_tbl, l_restrict_manual_assign_tbl,
1518 l_restrict_manual_update_tbl, l_party_usage_created_by_tbl;
1519 CLOSE c_party_usages;
1520
1521 FOR i IN 1..l_party_usages_tbl.count LOOP
1522 G_PARTY_USAGE_CODES(l_party_usages_tbl(i)) :=
1523 l_party_usage_status_tbl(i)||'##'||
1524 l_party_usage_type_tbl(i)||'##'||
1525 l_restrict_manual_assign_tbl(i)||'##'||
1526 l_restrict_manual_update_tbl(i)||'##'||
1527 l_party_usage_created_by_tbl(i);
1528 END LOOP;
1529
1530 -- load created by module
1531 OPEN c_created_by_module;
1532 FETCH c_created_by_module BULK COLLECT INTO
1533 l_created_by_module_tbl, l_created_by_tbl;
1534 CLOSE c_created_by_module;
1535
1536 FOR i IN 1..l_created_by_module_tbl.count LOOP
1537 G_CREATED_BY_MODULES(l_created_by_module_tbl(i)) := l_created_by_tbl(i);
1538 END LOOP;
1539
1540 G_SETUP_LOADED := 1;
1541
1542 -- load party usage rule
1543 OPEN c_party_usage_rules;
1544 FETCH c_party_usage_rules BULK COLLECT INTO l_party_usage_rules_tbl;
1545 CLOSE c_party_usage_rules;
1546
1547 IF l_party_usage_rules_tbl.count > 0 THEN
1548 FOR i IN 1..l_party_usage_rules_tbl.count LOOP
1549 G_PARTY_USAGE_RULES(l_party_usage_rules_tbl(i)) := 'Y';
1550 END LOOP;
1551
1552 --
1553 -- have rules defined
1554 --
1555 G_SETUP_LOADED := 2;
1556
1557 -- check if there is any exclusive or co-exist rule
1558 OPEN c_exist_exclusive_rules;
1559 FETCH c_exist_exclusive_rules INTO l_dummy;
1560 IF c_exist_exclusive_rules%FOUND THEN
1561 G_SETUP_LOADED := 3;
1562 END IF;
1563 CLOSE c_exist_exclusive_rules;
1564
1565 END IF;
1566
1567 -- Debug info.
1568 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1569 hz_utility_v2pub.debug(
1570 p_prefix => l_debug_prefix,
1571 p_message => 'G_SETUP_LOADED = '||G_SETUP_LOADED,
1572 p_msg_level => fnd_log.level_statement);
1573 END IF;
1574
1575 -- Debug info.
1576 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1577 hz_utility_v2pub.debug (
1578 p_message => c_api_name||' (-)',
1579 p_prefix => l_debug_prefix,
1580 p_msg_level => fnd_log.level_procedure);
1581 END IF;
1582
1583 END initialize;
1584
1585
1586 /**
1587 * PROCEDURE insert_row
1588 *
1589 * DESCRIPTION
1590 * Insert a new assignment.
1591 *
1592 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1593 *
1594 * NOTES
1595 *
1596 * MODIFICATION HISTORY
1597 *
1598 * 05/01/05 Jianying Huang o Created.
1599 *
1600 */
1601
1602 PROCEDURE insert_row (
1603 p_party_usg_assignment_rec IN party_usg_assignment_rec_type
1604 ) IS
1605
1606 c_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1607 l_debug_prefix VARCHAR2(30);
1608 l_party_usg_assignment_id NUMBER(15);
1609
1610 BEGIN
1611
1612 l_debug_prefix := '';
1613
1614 -- Debug info.
1615 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1616 hz_utility_v2pub.debug (
1617 p_message => c_api_name||' (+)',
1618 p_prefix => l_debug_prefix,
1619 p_msg_level => fnd_log.level_procedure);
1620 END IF;
1621
1622 HZ_PARTY_USG_ASSIGNMENTS_PKG.insert_row (
1623 x_party_id => p_party_usg_assignment_rec.party_id,
1624 x_party_usage_code => p_party_usg_assignment_rec.party_usage_code,
1625 x_effective_start_date => p_party_usg_assignment_rec.effective_start_date,
1629 x_owner_table_name => p_party_usg_assignment_rec.owner_table_name,
1626 x_effective_end_date => p_party_usg_assignment_rec.effective_end_date,
1627 x_status_flag => 'A',
1628 x_comments => p_party_usg_assignment_rec.comments,
1630 x_owner_table_id => p_party_usg_assignment_rec.owner_table_id,
1631 x_attribute_category => p_party_usg_assignment_rec.attribute_category,
1632 x_attribute1 => p_party_usg_assignment_rec.attribute1,
1633 x_attribute2 => p_party_usg_assignment_rec.attribute2,
1634 x_attribute3 => p_party_usg_assignment_rec.attribute3,
1635 x_attribute4 => p_party_usg_assignment_rec.attribute4,
1636 x_attribute5 => p_party_usg_assignment_rec.attribute5,
1637 x_attribute6 => p_party_usg_assignment_rec.attribute6,
1638 x_attribute7 => p_party_usg_assignment_rec.attribute7,
1639 x_attribute8 => p_party_usg_assignment_rec.attribute8,
1640 x_attribute9 => p_party_usg_assignment_rec.attribute9,
1641 x_attribute10 => p_party_usg_assignment_rec.attribute10,
1642 x_attribute11 => p_party_usg_assignment_rec.attribute11,
1643 x_attribute12 => p_party_usg_assignment_rec.attribute12,
1644 x_attribute13 => p_party_usg_assignment_rec.attribute13,
1645 x_attribute14 => p_party_usg_assignment_rec.attribute14,
1646 x_attribute15 => p_party_usg_assignment_rec.attribute15,
1647 x_attribute16 => p_party_usg_assignment_rec.attribute16,
1648 x_attribute17 => p_party_usg_assignment_rec.attribute17,
1649 x_attribute18 => p_party_usg_assignment_rec.attribute18,
1650 x_attribute19 => p_party_usg_assignment_rec.attribute19,
1651 x_attribute20 => p_party_usg_assignment_rec.attribute20,
1652 x_object_version_number => 1,
1653 x_created_by_module => p_party_usg_assignment_rec.created_by_module,
1654 x_application_id => fnd_global.resp_appl_id,
1655 x_party_usg_assignment_id => l_party_usg_assignment_id
1656 );
1657
1658 -- populate business object tracking table
1659 populate_bot(
1660 p_create_update_flag => 'I',
1661 p_party_usg_assignment_id => l_party_usg_assignment_id);
1662
1663 -- Debug info.
1664 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1665 hz_utility_v2pub.debug (
1666 p_message => c_api_name||' (-)',
1667 p_prefix => l_debug_prefix,
1668 p_msg_level => fnd_log.level_procedure);
1669 END IF;
1670
1671 END insert_row;
1672
1673
1674 /**
1675 * PROCEDURE update_row
1676 *
1677 * DESCRIPTION
1678 * Update a new assignment.
1679 *
1680 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1681 *
1682 * NOTES
1683 *
1684 * MODIFICATION HISTORY
1685 *
1686 * 05/01/05 Jianying Huang o Created.
1687 *
1688 */
1689
1690 PROCEDURE update_row (
1691 p_party_usg_assignment_id IN NUMBER,
1692 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
1693 p_object_version_number IN OUT NOCOPY NUMBER,
1694 p_old_object_version_number IN NUMBER,
1695 p_status IN VARCHAR2
1696 ) IS
1697
1698 c_api_name CONSTANT VARCHAR2(30) := 'update_row';
1699 l_debug_prefix VARCHAR2(30);
1700
1701 CURSOR c_assignment (
1702 p_party_usg_assignment_id NUMBER
1703 ) IS
1704 SELECT object_version_number
1705 FROM hz_party_usg_assignments
1706 WHERE party_usg_assignment_id = p_party_usg_assignment_id
1707 FOR UPDATE NOWAIT;
1708
1709 l_object_version_number NUMBER;
1710
1711 BEGIN
1712
1713 l_debug_prefix := '';
1714
1715 -- Debug info.
1716 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1717 hz_utility_v2pub.debug (
1718 p_message => c_api_name||' (+)',
1719 p_prefix => l_debug_prefix,
1720 p_msg_level => fnd_log.level_procedure);
1721 END IF;
1722
1723 OPEN c_assignment(p_party_usg_assignment_id);
1724 FETCH c_assignment INTO l_object_version_number;
1725 CLOSE c_assignment;
1726
1727 IF p_object_version_number IS NOT NULL THEN
1728 IF p_old_object_version_number IS NOT NULL THEN
1729 l_object_version_number := p_old_object_version_number;
1730 END IF;
1731
1732 IF p_object_version_number <> l_object_version_number THEN
1733 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
1734 fnd_message.set_token('TABLE', 'hz_party_usg_assignments');
1735 fnd_msg_pub.add;
1736 RAISE fnd_api.g_exc_error;
1737 END IF;
1738 END IF;
1739
1740 p_object_version_number := l_object_version_number + 1;
1741
1742 HZ_PARTY_USG_ASSIGNMENTS_PKG.update_row (
1743 x_party_usg_assignment_id => p_party_usg_assignment_id,
1744 x_party_id => null,
1745 x_party_usage_code => null,
1749 x_comments => p_party_usg_assignment_rec.comments,
1746 x_effective_start_date => p_party_usg_assignment_rec.effective_start_date,
1747 x_effective_end_date => p_party_usg_assignment_rec.effective_end_date,
1748 x_status_flag => p_status,
1750 x_owner_table_name => null,
1751 x_owner_table_id => null,
1752 x_attribute_category => p_party_usg_assignment_rec.attribute_category,
1753 x_attribute1 => p_party_usg_assignment_rec.attribute1,
1754 x_attribute2 => p_party_usg_assignment_rec.attribute2,
1755 x_attribute3 => p_party_usg_assignment_rec.attribute3,
1756 x_attribute4 => p_party_usg_assignment_rec.attribute4,
1757 x_attribute5 => p_party_usg_assignment_rec.attribute5,
1758 x_attribute6 => p_party_usg_assignment_rec.attribute6,
1759 x_attribute7 => p_party_usg_assignment_rec.attribute7,
1760 x_attribute8 => p_party_usg_assignment_rec.attribute8,
1761 x_attribute9 => p_party_usg_assignment_rec.attribute9,
1762 x_attribute10 => p_party_usg_assignment_rec.attribute10,
1763 x_attribute11 => p_party_usg_assignment_rec.attribute11,
1764 x_attribute12 => p_party_usg_assignment_rec.attribute12,
1765 x_attribute13 => p_party_usg_assignment_rec.attribute13,
1766 x_attribute14 => p_party_usg_assignment_rec.attribute14,
1767 x_attribute15 => p_party_usg_assignment_rec.attribute15,
1768 x_attribute16 => p_party_usg_assignment_rec.attribute16,
1769 x_attribute17 => p_party_usg_assignment_rec.attribute17,
1770 x_attribute18 => p_party_usg_assignment_rec.attribute18,
1771 x_attribute19 => p_party_usg_assignment_rec.attribute19,
1772 x_attribute20 => p_party_usg_assignment_rec.attribute20,
1773 x_object_version_number => p_object_version_number
1774 );
1775
1776 -- populate business object tracking table
1777 populate_bot(
1778 p_create_update_flag => 'U',
1779 p_party_usg_assignment_id => p_party_usg_assignment_id);
1780
1781 -- Debug info.
1782 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1783 hz_utility_v2pub.debug (
1784 p_message => c_api_name||' (-)',
1785 p_prefix => l_debug_prefix,
1786 p_msg_level => fnd_log.level_procedure);
1787 END IF;
1788
1789 END update_row;
1790
1791
1792 /**
1793 * PROCEDURE split
1794 *
1795 * DESCRIPTION
1796 * Split a string via delimiter.
1797 *
1798 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1799 *
1800 * NOTES
1801 *
1802 * MODIFICATION HISTORY
1803 *
1804 * 05/01/05 Jianying Huang o Created.
1805 *
1806 */
1807
1808 PROCEDURE split (
1809 p_string IN VARCHAR2,
1810 p_delimiter IN VARCHAR2,
1811 x_table OUT NOCOPY VARCHAR100_TBL
1812 ) IS
1813
1814 l_length NUMBER;
1815 l_dlength NUMBER;
1816 l_start NUMBER;
1817 l_counter NUMBER;
1818 l_index NUMBER;
1819
1820 BEGIN
1821
1822 x_table := VARCHAR100_TBL();
1823 l_length := lengthb(p_string);
1824 l_dlength := lengthb(p_delimiter);
1825
1826 l_start := 1; l_counter := 1; l_index := 1;
1827 WHILE (l_start <= l_length AND l_index > 0)
1828 LOOP
1829 l_index := instrb(p_string, p_delimiter, l_start);
1830 IF l_index <> 0 THEN
1831 x_table.extend(1);
1832 x_table(l_counter) := substr(p_string, l_start, l_index - l_start);
1833 l_start := l_index + l_dlength;
1834 l_counter := l_counter + 1;
1835 END IF;
1836 END LOOP;
1837
1838 IF l_start <= l_length THEN
1839 x_table.extend(1);
1840 x_table(l_counter) := substrb(p_string, l_start);
1841 END IF;
1842
1843 END split;
1844
1845
1846 --------------------------------------
1847 -- public procedures and functions
1848 --------------------------------------
1849
1850 /**
1851 * PROCEDURE assign_party_usage
1852 *
1853 * DESCRIPTION
1854 * Creates party usage assignment.
1855 *
1856 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1857 *
1858 * ARGUMENTS
1859 * IN:
1860 * p_init_msg_list Initialize message stack if it is set to
1861 * fnd_api.G_TRUE. Default is fnd_api.G_FALSE.
1862 * p_validation_level Validation level. Default is full validation.
1863 * p_party_usg_assignment_rec Party usage assignment record.
1864 * IN/OUT:
1865 * OUT:
1866 * x_return_status Return status after the call. The status can
1867 * be fnd_api.G_RET_STS_SUCCESS (success),
1868 * fnd_api.G_RET_STS_ERROR (error),
1869 * fnd_api.G_RET_STS_UNEXP_ERROR (unexpected error).
1870 * x_msg_count Number of messages in message stack.
1874 *
1871 * x_msg_data Message text if x_msg_count is 1.
1872 *
1873 * NOTES
1875 * MODIFICATION HISTORY
1876 *
1877 * 05/01/05 Jianying Huang o Created.
1878 *
1879 */
1880
1881 PROCEDURE assign_party_usage (
1882 p_init_msg_list IN VARCHAR2,
1883 p_validation_level IN NUMBER,
1884 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
1885 x_return_status OUT NOCOPY VARCHAR2,
1886 x_msg_count OUT NOCOPY NUMBER,
1887 x_msg_data OUT NOCOPY VARCHAR2
1888 ) IS
1889
1890 c_api_name CONSTANT VARCHAR2(30) := 'assign_party_usage';
1891 l_debug_prefix VARCHAR2(30);
1892 l_validation_level NUMBER(3);
1893 l_party_usg_assignment_rec party_usg_assignment_rec_type;
1894
1895 BEGIN
1896
1897 -- standard start of API savepoint
1898 SAVEPOINT assign_party_usage;
1899
1900 l_debug_prefix := '';
1901
1902 -- Debug info.
1903 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1904 hz_utility_v2pub.debug (
1905 p_message => c_api_name||' (+)',
1906 p_prefix => l_debug_prefix,
1907 p_msg_level => fnd_log.level_procedure
1908 );
1909 END IF;
1910
1911 -- initialize message list if p_init_msg_list is set to TRUE.
1912 IF p_init_msg_list IS NOT NULL AND
1913 fnd_api.To_Boolean(p_init_msg_list)
1914 THEN
1915 fnd_msg_pub.initialize;
1916 END IF;
1917
1918 -- initialize validation level
1919 IF p_validation_level IS NULL THEN
1920 l_validation_level := G_VALID_LEVEL_FULL;
1921 ELSE
1922 l_validation_level := p_validation_level;
1923 END IF;
1924
1925 -- initialize API return status to success.
1926 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1927
1928 -- call to business logic.
1929 l_party_usg_assignment_rec := p_party_usg_assignment_rec;
1930
1931 do_assign_party_usage (
1932 p_validation_level => l_validation_level,
1933 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
1934 x_return_status => x_return_status
1935 );
1936
1937 -- standard call to get message count and if count is 1, get message info.
1938 fnd_msg_pub.Count_And_Get (
1939 p_encoded => fnd_api.G_FALSE,
1940 p_count => x_msg_count,
1941 p_data => x_msg_data);
1942
1943 -- Debug info.
1944 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1945 hz_utility_v2pub.debug_return_messages (
1946 p_msg_count => x_msg_count,
1947 p_msg_data => x_msg_data,
1948 p_msg_type => 'WARNING',
1949 p_msg_level => fnd_log.level_exception
1950 );
1951 END IF;
1952
1953 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1954 hz_utility_v2pub.debug (
1955 p_message => c_api_name||' (-)',
1956 p_prefix => l_debug_prefix,
1957 p_msg_level => fnd_log.level_procedure
1958 );
1959 END IF;
1960
1961 EXCEPTION
1962 WHEN fnd_api.G_EXC_ERROR THEN
1963 ROLLBACK TO assign_party_usage;
1964 x_return_status := fnd_api.G_RET_STS_ERROR;
1965
1966 fnd_msg_pub.Count_And_Get (
1967 p_encoded => fnd_api.G_FALSE,
1968 p_count => x_msg_count,
1969 p_data => x_msg_data
1970 );
1971
1972 -- Debug info.
1973 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1974 hz_utility_v2pub.debug_return_messages (
1975 p_msg_count => x_msg_count,
1976 p_msg_data => x_msg_data,
1977 p_msg_type => 'ERROR',
1978 p_msg_level => fnd_log.level_error
1979 );
1980 END IF;
1981
1982 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1983 hz_utility_v2pub.debug (
1984 p_message => c_api_name||' (-)',
1985 p_prefix => l_debug_prefix,
1986 p_msg_level => fnd_log.level_procedure
1987 );
1988 END IF;
1989
1990 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1991 ROLLBACK TO assign_party_usage;
1992 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1993 fnd_msg_pub.Count_And_Get (
1994 p_encoded => fnd_api.G_FALSE,
1995 p_count => x_msg_count,
1996 p_data => x_msg_data
1997 );
1998
1999 -- Debug info.
2000 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2001 hz_utility_v2pub.debug_return_messages (
2002 p_msg_count => x_msg_count,
2003 p_msg_data => x_msg_data,
2004 p_msg_type => 'UNEXPECTED ERROR',
2005 p_msg_level => fnd_log.level_error
2006 );
2007 END IF;
2011 p_message => c_api_name||' (-)',
2008
2009 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2010 hz_utility_v2pub.debug (
2012 p_prefix => l_debug_prefix,
2013 p_msg_level => fnd_log.level_procedure
2014 );
2015 END IF;
2016
2017 WHEN OTHERS THEN
2018 ROLLBACK TO assign_party_usage;
2019 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2020
2021 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2022 fnd_message.set_token('ERROR' ,SQLERRM);
2023 fnd_msg_pub.add;
2024
2025 fnd_msg_pub.Count_And_Get (
2026 p_encoded => fnd_api.G_FALSE,
2027 p_count => x_msg_count,
2028 p_data => x_msg_data
2029 );
2030
2031 -- Debug info.
2032 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2033 hz_utility_v2pub.debug_return_messages (
2034 p_msg_count => x_msg_count,
2035 p_msg_data => x_msg_data,
2036 p_msg_type => 'SQL ERROR',
2037 p_msg_level => fnd_log.level_error
2038 );
2039 END IF;
2040
2041 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2042 hz_utility_v2pub.debug (
2043 p_message => c_api_name||' (-)',
2044 p_prefix => l_debug_prefix,
2045 p_msg_level => fnd_log.level_procedure
2046 );
2047 END IF;
2048
2049 END assign_party_usage;
2050
2051
2052 /**
2053 * PROCEDURE get_usg_assignment
2054 *
2055 * DESCRIPTION
2056 * Get party usage assignment.
2057 *
2058 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2059 *
2060 * ARGUMENTS
2061 * IN:
2062 * p_init_msg_list Initialize message stack if it is set to
2063 * fnd_api.G_TRUE. Default is fnd_api.G_FALSE.
2064 * p_party_usg_assignment_id Party usage assignment Id.
2065 * p_party_usg_assignment_rec Party usage assignment record.
2066 * IN/OUT:
2067 * OUT:
2068 * x_usg_assignment_id_tbl Table of party usage assignment Id.
2069 * x_usg_assignment_rec_tbl Table of party usage assignment record.
2070 * x_return_status Return status after the call. The status can
2071 * be fnd_api.G_RET_STS_SUCCESS (success),
2072 * fnd_api.G_RET_STS_ERROR (error),
2073 * fnd_api.G_RET_STS_UNEXP_ERROR (unexpected error).
2074 * x_msg_count Number of messages in message stack.
2075 * x_msg_data Message text if x_msg_count is 1.
2076 *
2077 * NOTES
2078 *
2079 * MODIFICATION HISTORY
2080 *
2081 * 05/01/05 Jianying Huang o Created.
2082 *
2083 */
2084
2085 PROCEDURE get_usg_assignment (
2086 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
2087 p_party_usg_assignment_id IN NUMBER,
2088 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
2089 x_usg_assignment_id_tbl OUT NOCOPY NUMBER15_TBL,
2090 x_usg_assignment_rec_tbl OUT NOCOPY ASSIGNMENT_REC_TBL,
2091 x_return_status OUT NOCOPY VARCHAR2,
2092 x_msg_count OUT NOCOPY NUMBER,
2093 x_msg_data OUT NOCOPY VARCHAR2
2094 ) IS
2095
2096 c_api_name CONSTANT VARCHAR2(30) := 'get_usg_assignment';
2097 l_debug_prefix VARCHAR2(30);
2098
2099 TYPE assignment_cursor_type IS REF CURSOR RETURN hz_party_usg_assignments%ROWTYPE;
2100 c_assignment assignment_cursor_type;
2101 assignment_row hz_party_usg_assignments%ROWTYPE;
2102 l_counter NUMBER;
2103 l_search_by VARCHAR2(30);
2104
2105 BEGIN
2106
2107 l_debug_prefix := '';
2108
2109 -- Debug info.
2110 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2111 hz_utility_v2pub.debug (
2112 p_message => c_api_name||' (+)',
2113 p_prefix => l_debug_prefix,
2114 p_msg_level => fnd_log.level_procedure
2115 );
2116 END IF;
2117
2121 THEN
2118 -- initialize message list if p_init_msg_list is set to TRUE.
2119 IF p_init_msg_list IS NOT NULL AND
2120 fnd_api.to_Boolean(p_init_msg_list)
2122 fnd_msg_pub.initialize;
2123 END IF;
2124
2125 -- initialize API return status to success.
2126 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2127
2128 x_usg_assignment_id_tbl := NUMBER15_TBL();
2129 x_usg_assignment_rec_tbl := ASSIGNMENT_REC_TBL();
2130
2131 -- check assignment id
2132 --
2133 IF p_party_usg_assignment_id IS NOT NULL AND
2134 p_party_usg_assignment_id <> fnd_api.G_MISS_NUM
2135 THEN
2136 l_search_by := 'ASSIGNMENT_ID';
2137 OPEN c_assignment FOR
2138 SELECT *
2139 FROM hz_party_usg_assignments
2140 WHERE party_usg_assignment_id = p_party_usg_assignment_id;
2141 --
2142 -- check party_id, party_usage_code
2143 --
2144 ELSIF p_party_usg_assignment_rec.party_usage_code IS NOT NULL AND
2145 p_party_usg_assignment_rec.party_usage_code <> fnd_api.G_MISS_CHAR AND
2146 p_party_usg_assignment_rec.party_id IS NOT NULL AND
2147 p_party_usg_assignment_rec.party_id <> fnd_api.G_MISS_NUM
2148 THEN
2149 l_search_by := 'USAGE_CODE';
2150 OPEN c_assignment FOR
2151 SELECT *
2152 FROM hz_party_usg_assignments
2153 WHERE party_id = p_party_usg_assignment_rec.party_id
2154 AND party_usage_code = p_party_usg_assignment_rec.party_usage_code
2155 AND status_flag = 'A'
2156 AND effective_end_date > trunc(sysdate);
2157 --
2158 -- check owner_table_name, owner_table_id
2159 --
2160 ELSIF p_party_usg_assignment_rec.owner_table_name IS NOT NULL AND
2161 p_party_usg_assignment_rec.owner_table_name <> fnd_api.G_MISS_CHAR AND
2162 p_party_usg_assignment_rec.owner_table_id IS NOT NULL AND
2163 p_party_usg_assignment_rec.owner_table_id <> fnd_api.G_MISS_NUM
2164 THEN
2165 l_search_by := 'OWNER_TABLE_NAME';
2166 OPEN c_assignment FOR
2167 SELECT *
2168 FROM hz_party_usg_assignments
2169 WHERE owner_table_name = p_party_usg_assignment_rec.owner_table_name
2170 AND owner_table_id = p_party_usg_assignment_rec.owner_table_id;
2171 -- need required parameters
2172 --
2173 ELSE
2174 fnd_message.set_name('AR', 'HZ_PU_MISSING_COLUMN');
2175 fnd_msg_pub.add;
2176 RAISE fnd_api.G_EXC_ERROR;
2177 END IF;
2178
2179 l_counter := 0;
2180 LOOP
2181 FETCH c_assignment INTO assignment_row;
2182 EXIT WHEN c_assignment%NOTFOUND;
2183
2184 l_counter := l_counter + 1;
2185 IF l_counter > 1 AND
2186 l_search_by IN ('USAGE_CODE', 'ASSIGNMENT_ID')
2187 THEN
2188 EXIT;
2189 END IF;
2190
2191 x_usg_assignment_id_tbl.extend(1);
2192 x_usg_assignment_rec_tbl.extend(1);
2193
2194 x_usg_assignment_id_tbl(l_counter) := assignment_row.party_usg_assignment_id;
2195 x_usg_assignment_rec_tbl(l_counter).party_id := assignment_row.party_id;
2196 x_usg_assignment_rec_tbl(l_counter).party_usage_code := assignment_row.party_usage_code;
2197 x_usg_assignment_rec_tbl(l_counter).effective_start_date := assignment_row.effective_start_date;
2198 x_usg_assignment_rec_tbl(l_counter).effective_end_date := assignment_row.effective_end_date;
2199 x_usg_assignment_rec_tbl(l_counter).comments := assignment_row.comments;
2200 x_usg_assignment_rec_tbl(l_counter).owner_table_name := assignment_row.owner_table_name;
2201 x_usg_assignment_rec_tbl(l_counter).owner_table_id := assignment_row.owner_table_id;
2202 x_usg_assignment_rec_tbl(l_counter).created_by_module := assignment_row.created_by_module;
2203 x_usg_assignment_rec_tbl(l_counter).attribute_category := assignment_row.attribute_category;
2204 x_usg_assignment_rec_tbl(l_counter).attribute1 := assignment_row.attribute1;
2205 x_usg_assignment_rec_tbl(l_counter).attribute2 := assignment_row.attribute2;
2206 x_usg_assignment_rec_tbl(l_counter).attribute3 := assignment_row.attribute3;
2207 x_usg_assignment_rec_tbl(l_counter).attribute4 := assignment_row.attribute4;
2208 x_usg_assignment_rec_tbl(l_counter).attribute5 := assignment_row.attribute5;
2209 x_usg_assignment_rec_tbl(l_counter).attribute6 := assignment_row.attribute6;
2210 x_usg_assignment_rec_tbl(l_counter).attribute7 := assignment_row.attribute7;
2211 x_usg_assignment_rec_tbl(l_counter).attribute8 := assignment_row.attribute8;
2212 x_usg_assignment_rec_tbl(l_counter).attribute9 := assignment_row.attribute9;
2213 x_usg_assignment_rec_tbl(l_counter).attribute10 := assignment_row.attribute10;
2214 x_usg_assignment_rec_tbl(l_counter).attribute11 := assignment_row.attribute11;
2218 x_usg_assignment_rec_tbl(l_counter).attribute15 := assignment_row.attribute15;
2215 x_usg_assignment_rec_tbl(l_counter).attribute12 := assignment_row.attribute12;
2216 x_usg_assignment_rec_tbl(l_counter).attribute13 := assignment_row.attribute13;
2217 x_usg_assignment_rec_tbl(l_counter).attribute14 := assignment_row.attribute14;
2219 x_usg_assignment_rec_tbl(l_counter).attribute16 := assignment_row.attribute16;
2220 x_usg_assignment_rec_tbl(l_counter).attribute17 := assignment_row.attribute17;
2221 x_usg_assignment_rec_tbl(l_counter).attribute18 := assignment_row.attribute18;
2222 x_usg_assignment_rec_tbl(l_counter).attribute19 := assignment_row.attribute19;
2223 x_usg_assignment_rec_tbl(l_counter).attribute20 := assignment_row.attribute20;
2224
2225 END LOOP;
2226 CLOSE c_assignment;
2227
2228 --
2229 -- more than one assignment exist
2230 --
2231 IF l_counter > 1 AND
2232 l_search_by IN ('USAGE_CODE', 'ASSIGNMENT_ID')
2233 THEN
2234 fnd_message.set_name('AR', 'HZ_PU_MULTIPLE_ASSIGNMENT');
2235 fnd_msg_pub.add;
2236 RAISE fnd_api.G_EXC_ERROR;
2237 --
2238 -- no assignment exist
2239 --
2240 ELSIF l_counter = 0 THEN
2241 fnd_message.set_name('AR', 'HZ_PU_INVALID_ASSIGNMENT');
2242 fnd_msg_pub.add;
2243 RAISE fnd_api.G_EXC_ERROR;
2244 END IF;
2245
2246 -- standard call to get message count and if count is 1, get message info.
2247 fnd_msg_pub.Count_And_Get (
2248 p_encoded => fnd_api.G_FALSE,
2249 p_count => x_msg_count,
2250 p_data => x_msg_data);
2251
2252 -- Debug info.
2253 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2254 hz_utility_v2pub.debug_return_messages (
2255 p_msg_count => x_msg_count,
2256 p_msg_data => x_msg_data,
2257 p_msg_type => 'WARNING',
2258 p_msg_level => fnd_log.level_exception
2259 );
2260 END IF;
2261
2262 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2263 hz_utility_v2pub.debug (
2264 p_message => c_api_name||' (-)',
2265 p_prefix => l_debug_prefix,
2266 p_msg_level => fnd_log.level_procedure
2267 );
2268 END IF;
2269
2270 EXCEPTION
2271 WHEN fnd_api.G_EXC_ERROR THEN
2272 x_return_status := fnd_api.G_RET_STS_ERROR;
2273
2274 fnd_msg_pub.Count_And_Get (
2275 p_encoded => fnd_api.G_FALSE,
2276 p_count => x_msg_count,
2277 p_data => x_msg_data
2278 );
2279
2280 -- Debug info.
2281 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2282 hz_utility_v2pub.debug_return_messages (
2283 p_msg_count => x_msg_count,
2284 p_msg_data => x_msg_data,
2285 p_msg_type => 'ERROR',
2286 p_msg_level => fnd_log.level_error
2287 );
2288 END IF;
2289
2290 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2291 hz_utility_v2pub.debug (
2292 p_message => c_api_name||' (-)',
2293 p_prefix => l_debug_prefix,
2294 p_msg_level => fnd_log.level_procedure
2295 );
2296 END IF;
2297
2298 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2299 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2300 fnd_msg_pub.Count_And_Get (
2301 p_encoded => fnd_api.G_FALSE,
2302 p_count => x_msg_count,
2303 p_data => x_msg_data
2304 );
2305
2306 -- Debug info.
2307 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2308 hz_utility_v2pub.debug_return_messages (
2309 p_msg_count => x_msg_count,
2310 p_msg_data => x_msg_data,
2311 p_msg_type => 'UNEXPECTED ERROR',
2312 p_msg_level => fnd_log.level_error
2313 );
2314 END IF;
2315
2316 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2317 hz_utility_v2pub.debug (
2318 p_message => c_api_name||' (-)',
2319 p_prefix => l_debug_prefix,
2320 p_msg_level => fnd_log.level_procedure
2321 );
2322 END IF;
2323
2324 WHEN OTHERS THEN
2325 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2326
2327 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2328 fnd_message.set_token('ERROR' ,SQLERRM);
2329 fnd_msg_pub.add;
2330
2331 fnd_msg_pub.Count_And_Get (
2332 p_encoded => fnd_api.G_FALSE,
2333 p_count => x_msg_count,
2334 p_data => x_msg_data
2335 );
2336
2337 -- Debug info.
2338 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2339 hz_utility_v2pub.debug_return_messages (
2340 p_msg_count => x_msg_count,
2341 p_msg_data => x_msg_data,
2342 p_msg_type => 'SQL ERROR',
2346
2343 p_msg_level => fnd_log.level_error
2344 );
2345 END IF;
2347 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2348 hz_utility_v2pub.debug (
2349 p_message => c_api_name||' (-)',
2350 p_prefix => l_debug_prefix,
2351 p_msg_level => fnd_log.level_procedure
2352 );
2353 END IF;
2354
2355 END get_usg_assignment;
2356
2357
2358 /**
2359 * PROCEDURE update_usg_assignment
2360 *
2361 * DESCRIPTION
2362 * Update party usage assignment.
2363 *
2364 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2365 *
2366 * ARGUMENTS
2367 * IN:
2368 * p_init_msg_list Initialize message stack if it is set to
2369 * fnd_api.G_TRUE. Default is fnd_api.G_FALSE.
2370 * p_validation_level Validation level. Default is full validation.
2371 * p_party_usg_assignment_id Party usage assignment Id.
2372 * p_party_usg_assignment_rec Party usage assignment record.
2373 * IN/OUT:
2374 * OUT:
2375 * x_return_status Return status after the call. The status can
2376 * be fnd_api.G_RET_STS_SUCCESS (success),
2377 * fnd_api.G_RET_STS_ERROR (error),
2378 * fnd_api.G_RET_STS_UNEXP_ERROR (unexpected error).
2379 * x_msg_count Number of messages in message stack.
2380 * x_msg_data Message text if x_msg_count is 1.
2381 *
2382 * NOTES
2383 *
2384 * MODIFICATION HISTORY
2385 *
2386 * 05/01/05 Jianying Huang o Created.
2387 *
2388 */
2389
2390 PROCEDURE update_usg_assignment (
2391 p_init_msg_list IN VARCHAR2,
2392 p_validation_level IN NUMBER,
2393 p_party_usg_assignment_id IN NUMBER,
2394 p_party_usg_assignment_rec IN party_usg_assignment_rec_type,
2395 x_return_status OUT NOCOPY VARCHAR2,
2396 x_msg_count OUT NOCOPY NUMBER,
2397 x_msg_data OUT NOCOPY VARCHAR2
2398 ) IS
2399
2400 c_api_name CONSTANT VARCHAR2(30) := 'update_usg_assignment';
2401 l_debug_prefix VARCHAR2(30);
2402 l_validation_level NUMBER(3);
2403 l_party_usg_assignment_rec party_usg_assignment_rec_type;
2404 l_usg_assignment_id_tbl NUMBER15_TBL;
2405 l_usg_assignment_rec_tbl ASSIGNMENT_REC_TBL;
2406
2407 BEGIN
2408
2409 -- standard start of API savepoint
2410 SAVEPOINT update_usg_assignment;
2411
2412 l_debug_prefix := '';
2413
2414 -- Debug info.
2415 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2416 hz_utility_v2pub.debug (
2417 p_message => c_api_name||' (+)',
2418 p_prefix => l_debug_prefix,
2419 p_msg_level => fnd_log.level_procedure
2420 );
2421 END IF;
2422
2423 -- initialize message list if p_init_msg_list is set to TRUE.
2424 IF p_init_msg_list IS NOT NULL AND
2425 fnd_api.to_Boolean(p_init_msg_list)
2426 THEN
2427 fnd_msg_pub.initialize;
2428 END IF;
2429
2430 -- initialize validation level
2431 IF p_validation_level IS NULL THEN
2432 l_validation_level := G_VALID_LEVEL_FULL;
2433 ELSE
2434 l_validation_level := p_validation_level;
2435 END IF;
2436
2437 -- initialize API return status to success.
2438 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2439
2440 -- get old assignment.
2441 get_usg_assignment(
2442 p_party_usg_assignment_id => p_party_usg_assignment_id,
2443 p_party_usg_assignment_rec => p_party_usg_assignment_rec,
2444 x_usg_assignment_id_tbl => l_usg_assignment_id_tbl,
2445 x_usg_assignment_rec_tbl => l_usg_assignment_rec_tbl,
2446 x_return_status => x_return_status,
2447 x_msg_count => x_msg_count,
2448 x_msg_data => x_msg_data
2449 );
2450
2451 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2452 RAISE fnd_api.G_EXC_ERROR;
2453 END IF;
2454
2455 -- call to business logic.
2456 l_party_usg_assignment_rec := p_party_usg_assignment_rec;
2457
2458 do_update_usg_assignment (
2459 p_validation_level => l_validation_level,
2460 p_usg_assignment_id_tbl => l_usg_assignment_id_tbl,
2461 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
2462 p_old_usg_assignment_rec_tbl => l_usg_assignment_rec_tbl,
2463 x_return_status => x_return_status
2464 );
2465
2466 -- standard call to get message count and if count is 1, get message info.
2467 fnd_msg_pub.Count_And_Get (
2468 p_encoded => fnd_api.G_FALSE,
2469 p_count => x_msg_count,
2470 p_data => x_msg_data);
2471
2472 -- Debug info.
2473 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2474 hz_utility_v2pub.debug_return_messages (
2475 p_msg_count => x_msg_count,
2476 p_msg_data => x_msg_data,
2480 END IF;
2477 p_msg_type => 'WARNING',
2478 p_msg_level => fnd_log.level_exception
2479 );
2481
2482 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2483 hz_utility_v2pub.debug (
2484 p_message => c_api_name||' (-)',
2485 p_prefix => l_debug_prefix,
2486 p_msg_level => fnd_log.level_procedure
2487 );
2488 END IF;
2489
2490 EXCEPTION
2491 WHEN fnd_api.G_EXC_ERROR THEN
2492 ROLLBACK TO update_usg_assignment;
2493 x_return_status := fnd_api.G_RET_STS_ERROR;
2494
2495 fnd_msg_pub.Count_And_Get (
2496 p_encoded => fnd_api.G_FALSE,
2497 p_count => x_msg_count,
2498 p_data => x_msg_data
2499 );
2500
2501 -- Debug info.
2502 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2503 hz_utility_v2pub.debug_return_messages (
2504 p_msg_count => x_msg_count,
2505 p_msg_data => x_msg_data,
2506 p_msg_type => 'ERROR',
2507 p_msg_level => fnd_log.level_error
2508 );
2509 END IF;
2510
2511 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2512 hz_utility_v2pub.debug (
2513 p_message => c_api_name||' (-)',
2514 p_prefix => l_debug_prefix,
2515 p_msg_level => fnd_log.level_procedure
2516 );
2517 END IF;
2518
2519 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2520 ROLLBACK TO update_usg_assignment;
2521 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2522 fnd_msg_pub.Count_And_Get (
2523 p_encoded => fnd_api.G_FALSE,
2524 p_count => x_msg_count,
2525 p_data => x_msg_data
2526 );
2527
2528 -- Debug info.
2529 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2530 hz_utility_v2pub.debug_return_messages (
2531 p_msg_count => x_msg_count,
2532 p_msg_data => x_msg_data,
2533 p_msg_type => 'UNEXPECTED ERROR',
2534 p_msg_level => fnd_log.level_error
2535 );
2536 END IF;
2537
2538 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2539 hz_utility_v2pub.debug (
2540 p_message => c_api_name||' (-)',
2541 p_prefix => l_debug_prefix,
2542 p_msg_level => fnd_log.level_procedure
2543 );
2544 END IF;
2545
2546 WHEN OTHERS THEN
2547 ROLLBACK TO update_usg_assignment;
2548 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2549
2550 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2551 fnd_message.set_token('ERROR' ,SQLERRM);
2552 fnd_msg_pub.add;
2553
2554 fnd_msg_pub.Count_And_Get (
2555 p_encoded => fnd_api.G_FALSE,
2556 p_count => x_msg_count,
2557 p_data => x_msg_data
2558 );
2559
2560 -- Debug info.
2561 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2562 hz_utility_v2pub.debug_return_messages (
2563 p_msg_count => x_msg_count,
2564 p_msg_data => x_msg_data,
2565 p_msg_type => 'SQL ERROR',
2566 p_msg_level => fnd_log.level_error
2567 );
2568 END IF;
2569
2570 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2571 hz_utility_v2pub.debug (
2572 p_message => c_api_name||' (-)',
2573 p_prefix => l_debug_prefix,
2574 p_msg_level => fnd_log.level_procedure
2575 );
2576 END IF;
2577
2578 END update_usg_assignment;
2579
2580
2581 /**
2582 * PROCEDURE inactivate_usg_assignment
2583 *
2584 * DESCRIPTION
2585 * Inactivates party usage assignment.
2586 *
2587 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2588 *
2589 * ARGUMENTS
2590 * IN:
2591 * p_init_msg_list Initialize message stack if it is set to
2592 * fnd_api.G_TRUE. Default is fnd_api.G_FALSE.
2593 * p_validation_level Validation level. Default is full validation.
2594 * p_party_id Party Id
2595 * p_party_usage_code Party usage code
2596 * IN/OUT:
2597 * OUT:
2598 * x_return_status Return status after the call. The status can
2599 * be fnd_api.G_RET_STS_SUCCESS (success),
2600 * fnd_api.G_RET_STS_ERROR (error),
2601 * fnd_api.G_RET_STS_UNEXP_ERROR (unexpected error).
2602 * x_msg_count Number of messages in message stack.
2603 * x_msg_data Message text if x_msg_count is 1.
2604 *
2605 * NOTES
2606 *
2607 * MODIFICATION HISTORY
2608 *
2609 * 05/01/05 Jianying Huang o Created.
2610 *
2611 */
2612
2613 PROCEDURE inactivate_usg_assignment (
2614 p_init_msg_list IN VARCHAR2,
2615 p_validation_level IN NUMBER,
2616 p_party_usg_assignment_id IN NUMBER,
2617 p_party_id IN NUMBER,
2618 p_party_usage_code IN VARCHAR2,
2619 x_return_status OUT NOCOPY VARCHAR2,
2620 x_msg_count OUT NOCOPY NUMBER,
2621 x_msg_data OUT NOCOPY VARCHAR2
2622 ) IS
2623
2624 c_api_name CONSTANT VARCHAR2(30) := 'inactivate_usg_assignment';
2625 l_debug_prefix VARCHAR2(30);
2626 l_party_usg_assignment_rec party_usg_assignment_rec_type;
2627 l_success VARCHAR2(1);
2628
2629 BEGIN
2630
2631 l_debug_prefix := '';
2632
2633 -- Debug info.
2634 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2635 hz_utility_v2pub.debug (
2636 p_message => c_api_name||' (+)',
2637 p_prefix => l_debug_prefix,
2638 p_msg_level => fnd_log.level_procedure
2639 );
2640 END IF;
2641
2642 l_party_usg_assignment_rec.party_id := p_party_id;
2643 l_party_usg_assignment_rec.party_usage_code := p_party_usage_code;
2644 l_party_usg_assignment_rec.effective_end_date := trunc(sysdate);
2645
2646 update_usg_assignment (
2647 p_init_msg_list => p_init_msg_list,
2648 p_validation_level => p_validation_level,
2649 p_party_usg_assignment_id => p_party_usg_assignment_id,
2650 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
2651 x_return_status => x_return_status,
2652 x_msg_count => x_msg_count,
2653 x_msg_data => x_msg_data
2654 );
2655
2656 -- replace error message
2657 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2658 fnd_message.set_name('AR', 'HZ_PU_WRONG_API');
2659 fnd_msg_pub.Set_Search_Name('AR', 'HZ_PU_MULTIPLE_ASSIGNMENT');
2660 l_success := fnd_msg_pub.Change_Msg;
2661
2662 IF l_success = 'T' THEN
2663 -- standard call to get message count and if count is 1, get message info.
2664 fnd_msg_pub.Count_And_Get (
2665 p_encoded => fnd_api.G_FALSE,
2666 p_count => x_msg_count,
2667 p_data => x_msg_data);
2668 END IF;
2669 END IF;
2670
2671 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2672 hz_utility_v2pub.debug (
2673 p_message => c_api_name||' (-)',
2674 p_prefix => l_debug_prefix,
2675 p_msg_level => fnd_log.level_procedure
2676 );
2677 END IF;
2678
2679 END inactivate_usg_assignment;
2680
2681
2682 /**
2683 * PROCEDURE refresh
2684 *
2685 * DESCRIPTION
2686 * Refresh the cached setup. Need to be called when the party usage setup
2687 * is changed via admin UI.
2688 *
2689 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2690 *
2691 * NOTES
2692 *
2693 * MODIFICATION HISTORY
2694 *
2695 * 05/01/05 Jianying Huang o Created.
2696 *
2697 */
2698
2699 PROCEDURE refresh IS
2700
2701 BEGIN
2702
2703 G_SETUP_LOADED := 0;
2704
2705 END refresh;
2706
2707
2708 /**
2709 * PROCEDURE set_calling_api
2710 *
2711 * DESCRIPTION
2712 * Set calling api. Internal use only.
2713 *
2714 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2715 *
2716 * NOTES
2717 *
2718 * MODIFICATION HISTORY
2719 *
2720 * 05/01/05 Jianying Huang o Created.
2721 *
2722 */
2723
2724 PROCEDURE set_calling_api (
2725 p_calling_api IN VARCHAR2
2726 ) IS
2727
2728 BEGIN
2729
2730 G_CALLING_API := p_calling_api;
2731
2732 END set_calling_api;
2733
2734
2735 /**
2736 * FUNCTION allow_party_merge
2737 *
2738 * DESCRIPTION
2739 * Created for party merge. Check party usage
2740 * rules to determine if merge is allowed.
2741 *
2742 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2743 *
2744 * NOTES
2745 *
2746 * MODIFICATION HISTORY
2747 *
2748 * 07/19/05 Jianying Huang o Created.
2749 *
2750 */
2751
2752 FUNCTION allow_party_merge (
2753 p_init_msg_list IN VARCHAR2,
2754 p_from_party_id IN NUMBER,
2755 p_to_party_id IN NUMBER,
2756 x_msg_count OUT NOCOPY NUMBER,
2757 x_msg_data OUT NOCOPY VARCHAR2
2758 ) RETURN VARCHAR2 IS
2759
2760 c_api_name CONSTANT VARCHAR2(30) := 'allow_party_merge';
2761 l_debug_prefix VARCHAR2(30);
2762 l_allow_party_merge VARCHAR2(1);
2763
2764 CURSOR c_assignments (
2765 p_party_id NUMBER
2766 ) IS
2767 SELECT UNIQUE party_usage_code
2768 FROM hz_party_usg_assignments
2769 WHERE party_id = p_party_id;
2770
2771 l_from_party_usage_codes_tbl VARCHAR100_TBL;
2772 l_to_party_usage_codes_tbl VARCHAR100_TBL;
2773 l_continue_i VARCHAR2(1);
2774 l_continue_j VARCHAR2(1);
2775 i NUMBER;
2776 j NUMBER;
2777
2778 BEGIN
2779
2780 l_debug_prefix := '';
2781 l_allow_party_merge := 'Y';
2782
2783 -- Debug info.
2784 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2785 hz_utility_v2pub.debug (
2786 p_message => c_api_name||' (+)',
2787 p_prefix => l_debug_prefix,
2788 p_msg_level => fnd_log.level_procedure
2789 );
2790 END IF;
2791
2792 -- initialize message list if p_init_msg_list is set to TRUE.
2793 IF p_init_msg_list IS NOT NULL AND
2794 fnd_api.to_Boolean(p_init_msg_list)
2795 THEN
2796 fnd_msg_pub.initialize;
2797 END IF;
2798
2799 -- load setup data
2800 IF G_SETUP_LOADED = 0 THEN
2801 initialize;
2802 END IF;
2803
2804 --
2805 -- check party usage rules
2806 --
2807 IF G_SETUP_LOADED = 3 THEN
2808 OPEN c_assignments(p_from_party_id);
2809 FETCH c_assignments BULK COLLECT INTO
2810 l_from_party_usage_codes_tbl;
2811 CLOSE c_assignments;
2812
2813 IF l_from_party_usage_codes_tbl.count > 0 THEN
2814 OPEN c_assignments(p_to_party_id);
2815 FETCH c_assignments BULK COLLECT INTO
2816 l_to_party_usage_codes_tbl;
2817 CLOSE c_assignments;
2818
2819 IF l_to_party_usage_codes_tbl.count > 0 THEN
2820 --
2821 -- the following check are needed only when there
2822 -- are some existing assignments
2823 --
2824 l_continue_i := 'Y'; i := 1;
2825 WHILE (i <= l_from_party_usage_codes_tbl.count AND
2826 l_continue_i = 'Y')
2827 LOOP
2828 -- Debug info.
2829 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2830 hz_utility_v2pub.debug(
2831 p_prefix => l_debug_prefix,
2832 p_message => 'l_from_party_usage_codes_tbl('||i||') = '||
2833 l_from_party_usage_codes_tbl(i),
2834 p_msg_level => fnd_log.level_statement);
2835 END IF;
2836
2837 l_continue_j := 'Y'; j := 1;
2838 WHILE (j <= l_to_party_usage_codes_tbl.count AND
2839 l_continue_j = 'Y')
2840 LOOP
2841 -- Debug info.
2842 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2843 hz_utility_v2pub.debug(
2844 p_prefix => l_debug_prefix,
2845 p_message => 'l_to_party_usage_codes_tbl('||j||') = '||
2846 l_to_party_usage_codes_tbl(j),
2847 p_msg_level => fnd_log.level_statement);
2848 END IF;
2849
2850 --
2851 -- check exclusive rule
2852 -- check co-exist rule
2853 --
2854 IF (violate_exclusive_rules(
2855 l_from_party_usage_codes_tbl(i),
2856 l_to_party_usage_codes_tbl(j)) OR
2857 violate_coexist_rules(
2858 l_from_party_usage_codes_tbl(i),
2859 l_to_party_usage_codes_tbl(j)))
2860 THEN
2861 fnd_message.set_name('AR', 'HZ_PU_EXCLUSIVE_RULE_FAILED');
2862 fnd_message.set_token('EXISTING_PARTY_USAGE_CODE', l_from_party_usage_codes_tbl(i));
2863 fnd_message.set_token('NEW_PARTY_USAGE_CODE', l_to_party_usage_codes_tbl(j));
2864 fnd_msg_pub.add;
2865
2866 l_continue_j := 'N'; l_continue_i := 'N';
2867 l_allow_party_merge := 'N';
2868 ELSE
2869 j := j + 1;
2870 END IF;
2871
2872 END LOOP;
2873
2874 i := i + 1;
2875
2876 END LOOP;
2877 END IF; -- to party has assignments
2878 END IF; -- from party has assignments
2879 END IF; -- has rules defined.
2880
2881 -- standard call to get message count and if count is 1, get message info.
2882 fnd_msg_pub.Count_And_Get (
2883 p_encoded => fnd_api.G_FALSE,
2884 p_count => x_msg_count,
2885 p_data => x_msg_data);
2886
2887 -- Debug info.
2888 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2889 hz_utility_v2pub.debug_return_messages (
2890 p_msg_count => x_msg_count,
2891 p_msg_data => x_msg_data,
2892 p_msg_type => 'ERROR',
2893 p_msg_level => fnd_log.level_exception
2894 );
2895 END IF;
2896
2897 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2898 hz_utility_v2pub.debug (
2899 p_message => c_api_name||' (-)',
2900 p_prefix => l_debug_prefix,
2901 p_msg_level => fnd_log.level_procedure
2902 );
2903 END IF;
2904
2905 RETURN l_allow_party_merge;
2906
2907 END allow_party_merge;
2908
2909
2910 /**
2911 * FUNCTION find_duplicates
2912 *
2913 * DESCRIPTION
2914 * Created for party merge. Find duplicate assignment.
2915 *
2916 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2917 *
2918 * NOTES
2919 *
2920 * MODIFICATION HISTORY
2921 *
2922 * 07/19/05 Jianying Huang o Created.
2923 *
2924 */
2925
2926 PROCEDURE find_duplicates (
2927 p_from_assignment_id IN NUMBER,
2928 p_to_party_id IN NUMBER,
2929 x_to_assignment_id OUT NOCOPY NUMBER
2930 ) IS
2931
2932 c_api_name CONSTANT VARCHAR2(30) := 'find_duplicates';
2933 l_debug_prefix VARCHAR2(30);
2934 l_party_usg_assignment_rec party_usg_assignment_rec_type;
2935 l_usg_assignment_id_tbl NUMBER15_TBL;
2936 l_usg_assignment_rec_tbl ASSIGNMENT_REC_TBL;
2937 l_has_duplicates VARCHAR2(1);
2938 x_return_status VARCHAR2(1);
2939 x_msg_count NUMBER;
2940 x_msg_data VARCHAR2(2000);
2941
2942 BEGIN
2943 -- Debug info.
2944 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2945 hz_utility_v2pub.debug (
2946 p_message => c_api_name||' (+)',
2947 p_prefix => l_debug_prefix,
2948 p_msg_level => fnd_log.level_procedure
2949 );
2953 get_usg_assignment(
2950 END IF;
2951
2952 -- get old assignment.
2954 p_party_usg_assignment_id => p_from_assignment_id,
2955 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
2956 x_usg_assignment_id_tbl => l_usg_assignment_id_tbl,
2957 x_usg_assignment_rec_tbl => l_usg_assignment_rec_tbl,
2958 x_return_status => x_return_status,
2959 x_msg_count => x_msg_count,
2960 x_msg_data => x_msg_data
2961 );
2962
2963 IF l_usg_assignment_rec_tbl.count = 1 THEN
2964 l_party_usg_assignment_rec := l_usg_assignment_rec_tbl(1);
2965 l_party_usg_assignment_rec.party_id := p_to_party_id;
2966
2967 l_has_duplicates := duplicates_exist(l_party_usg_assignment_rec, x_to_assignment_id);
2968 END IF;
2969
2970 -- Debug info.
2971 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2972 hz_utility_v2pub.debug (
2973 p_message => c_api_name||' (-)',
2974 p_prefix => l_debug_prefix,
2975 p_msg_level => fnd_log.level_procedure
2976 );
2977 END IF;
2978
2979 END find_duplicates;
2980
2981
2982 /**
2983 * PROCEDURE validate_supplier_name
2984 *
2985 * DESCRIPTION
2986 * Validate supplier name.
2987 *
2988 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2989 *
2990 * ARGUMENTS
2991 * IN:
2992 * p_party_id party id
2993 * p_party_name party name
2994 * x_return_status return status
2995 *
2996 * NOTES
2997 *
2998 * MODIFICATION HISTORY
2999 */
3000
3001 PROCEDURE validate_supplier_name (
3002 p_party_id IN NUMBER,
3003 p_party_name IN VARCHAR2,
3004 x_return_status IN OUT NOCOPY VARCHAR2
3005 ) IS
3006
3007 c_supplier_code CONSTANT VARCHAR2(30) := 'SUPPLIER';
3008
3009 CURSOR c_party (
3010 p_party_id NUMBER,
3011 p_party_name VARCHAR2
3012 ) IS
3013 SELECT null
3014 FROM hz_parties p
3015 WHERE p.party_name = p_party_name
3016 AND p.party_type = 'ORGANIZATION'
3017 AND p.party_id <> p_party_id
3018 AND p.status IN ('A', 'I')
3019 AND EXISTS (
3020 SELECT null
3021 FROM hz_party_usg_assignments pu
3022 WHERE pu.party_usage_code = c_supplier_code
3023 AND pu.party_id = p.party_id
3024 AND ROWNUM = 1)
3025 AND ROWNUM = 1;
3026
3027 l_dummy VARCHAR2(1);
3028
3029 BEGIN
3030
3031 -- check uniqueness across supplier parties
3032 OPEN c_party(p_party_id, p_party_name);
3033 FETCH c_party INTO l_dummy;
3034 IF c_party%FOUND THEN
3035 fnd_message.set_name('AR', 'HZ_NONUNIQUE_SUPPLIER_NAME');
3036 fnd_msg_pub.add;
3037 x_return_status := fnd_api.g_ret_sts_error;
3038 END IF;
3039 CLOSE c_party;
3040
3041 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3042 hz_utility_v2pub.debug(
3043 p_prefix => '',
3044 p_message => 'after validate supplier name uniqueness ... ' ||
3045 'x_return_status = ' || x_return_status,
3046 p_msg_level => fnd_log.level_statement);
3047 END IF;
3048
3049 END validate_supplier_name;
3050
3051
3052 END HZ_PARTY_USG_ASSIGNMENT_PVT;