[Home] [Help]
PACKAGE BODY: APPS.HZ_DYN_VALIDATION
Source
1 PACKAGE BODY hz_dyn_validation AS
2 /*$Header: ARHDVSB.pls 120.5 2005/10/30 03:52:05 appldev noship $ */
3
4 -- declaration of private global variables
5 g_debug_count NUMBER := 0;
6 --g_debug BOOLEAN := FALSE;
7
8 -- local error pragma
9 compile_error EXCEPTION;
10 PRAGMA EXCEPTION_INIT (compile_error, -6550);
11
12 /*=======================================================================+
13 | PRIVATE PROCEDURE enable_debug |
14 | |
15 | DESCRIPTION |
16 | Turn on debug mode. |
17 | |
18 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
19 | hz_utility_v2pub.enable_debug |
20 | |
21 | MODIFICATION HISTORY |
22 | 12-NOV-2001 J. del Callar Created. |
23 +=======================================================================*/
24
25 /*PROCEDURE enable_debug IS
26 BEGIN
27 g_debug_count := g_debug_count + 1;
28 IF g_debug_count = 1 THEN
29 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
30 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
31 THEN
32 hz_utility_v2pub.enable_debug;
33 g_debug := TRUE;
34 END IF;
35 END IF;
36 END enable_debug;
37 */
38
39
40 /*=======================================================================+
41 | PRIVATE PROCEDURE disable_debug |
42 | |
43 | DESCRIPTION |
44 | Turn off debug mode. |
45 | |
46 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
47 | hz_utility_v2pub.disable_debug |
48 | |
49 | MODIFICATION HISTORY |
50 | 12-NOV-2001 J. del Callar Created. |
51 +=======================================================================*/
52
53 /*PROCEDURE disable_debug IS
54 BEGIN
55 IF g_debug THEN
56 g_debug_count := g_debug_count - 1;
57 IF g_debug_count = 0 THEN
58 hz_utility_v2pub.disable_debug;
59 g_debug := FALSE;
60 END IF;
61 END IF;
62 END disable_debug;
63 */
64
65
66 /*=======================================================================+
67 | PRIVATE PROCEDURE create_party_gt |
68 | |
69 | DESCRIPTION |
70 | Used to insert a record into the party global temporary table. |
71 | |
72 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
73 | hz_party_v2pub.party_rec_type |
74 | |
75 | MODIFICATION HISTORY |
76 | 12-NOV-2001 J. del Callar Created. |
77 +=======================================================================*/
78
79 FUNCTION create_party_gt (x_party IN hz_party_v2pub.party_rec_type)
80 RETURN NUMBER IS
81 CURSOR partyidcur IS
82 SELECT hz_party_val_gt_s.NEXTVAL
83 FROM DUAL;
84
85 l_party_id NUMBER(15);
86 l_debug_prefix VARCHAR2(30) := '';
87 BEGIN
88 -- Debug info.
89 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
90 hz_utility_v2pub.debug(p_message=>'create party',
91 p_prefix =>l_debug_prefix,
92 p_msg_level=>fnd_log.level_statement);
93 END IF;
94
95
96 -- Get the party ID.
97 OPEN partyidcur;
98 FETCH partyidcur INTO l_party_id;
99 IF partyidcur%NOTFOUND THEN
100 -- Close the cursor and raise an error if the group ID could not be
101 -- selected from the sequence.
102 CLOSE partyidcur;
103
104 fnd_message.set_name('AR', 'HZ_DV_ID_NOT_FOUND');
105 fnd_message.set_token('SEQUENCE', 'hz_party_val_gt_s');
106 fnd_msg_pub.add;
107 RAISE fnd_api.g_exc_error;
108 END IF;
109 CLOSE partyidcur;
110
111 -- Stage the data into the party temporary table.
112 INSERT INTO hz_party_val_gt (temp_id,
113 party_number,
114 validated_flag,
115 orig_system_reference,
116 status,
117 category_code,
118 salutation,
119 attribute_category,
120 attribute1,
121 attribute2,
122 attribute3,
123 attribute4,
124 attribute5,
125 attribute6,
126 attribute7,
127 attribute8,
128 attribute9,
129 attribute10,
130 attribute11,
131 attribute12,
132 attribute13,
133 attribute14,
134 attribute15,
135 attribute16,
136 attribute17,
137 attribute18,
138 attribute19,
139 attribute20,
140 attribute21,
141 attribute22,
142 attribute23,
143 attribute24)
144 VALUES (l_party_id,
145 x_party.party_number,
146 x_party.validated_flag,
147 x_party.orig_system_reference,
148 x_party.status,
149 x_party.category_code,
150 x_party.salutation,
151 x_party.attribute_category,
152 x_party.attribute1,
153 x_party.attribute2,
154 x_party.attribute3,
155 x_party.attribute4,
156 x_party.attribute5,
157 x_party.attribute6,
158 x_party.attribute7,
159 x_party.attribute8,
160 x_party.attribute9,
161 x_party.attribute10,
162 x_party.attribute11,
163 x_party.attribute12,
164 x_party.attribute13,
165 x_party.attribute14,
166 x_party.attribute15,
167 x_party.attribute16,
168 x_party.attribute17,
169 x_party.attribute18,
170 x_party.attribute19,
171 x_party.attribute20,
172 x_party.attribute21,
173 x_party.attribute22,
174 x_party.attribute23,
175 x_party.attribute24);
176
177 -- Debug info.
178 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
179 hz_utility_v2pub.debug(p_message=>' returning party ID='||l_party_id,
180 p_prefix =>l_debug_prefix,
181 p_msg_level=>fnd_log.level_statement);
182 END IF;
183
184 RETURN l_party_id;
185 END create_party_gt;
186
187
188 /*=======================================================================+
189 | PRIVATE PROCEDURE create_relationship_gt |
190 | |
191 | DESCRIPTION |
192 | Used to insert a record into the relationship global temporary |
193 | table. |
194 | |
195 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
196 | hz_relationship_v2pub.relationship_rec_type |
197 | |
198 | MODIFICATION HISTORY |
199 | 12-NOV-2001 J. del Callar Created. |
200 +=======================================================================*/
201
202 FUNCTION create_relationship_gt (
203 x_relationship IN hz_relationship_v2pub.relationship_rec_type,
204 x_temp_id IN NUMBER DEFAULT NULL
205 ) RETURN NUMBER IS
206 CURSOR relidcur IS
207 SELECT hz_relationship_val_gt_s.NEXTVAL
208 FROM DUAL;
209
210 l_relationship_id NUMBER(15);
211 l_party_id NUMBER(15);
212 l_debug_prefix VARCHAR2(30) := '';
213 BEGIN
214 -- Debug info.
215 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
216 hz_utility_v2pub.debug(p_message=>'create relationship',
217 p_prefix =>l_debug_prefix,
218 p_msg_level=>fnd_log.level_statement);
219 END IF;
220
221 -- Create the party record
222 l_party_id := create_party_gt(x_relationship.party_rec);
223
224 -- Get the relationship ID.
225 IF x_temp_id IS NOT NULL THEN
226 l_relationship_id := x_temp_id;
227 ELSE
228 OPEN relidcur;
229 FETCH relidcur INTO l_relationship_id;
230 IF relidcur%NOTFOUND THEN
231 -- Close the cursor and raise an error if the relationship ID
232 -- could not be selected from the sequence.
233 CLOSE relidcur;
234
235 fnd_message.set_name('AR', 'HZ_DV_ID_NOT_FOUND');
236 fnd_message.set_token('SEQUENCE', 'hz_relationship_val_gt_s');
237 fnd_msg_pub.add;
238 RAISE fnd_api.g_exc_error;
239 END IF;
240 CLOSE relidcur;
241 END IF;
242
243 -- Create the relationship record.
244 INSERT INTO hz_relationship_val_gt(temp_id,
245 temp_party_id,
246 subject_id,
247 subject_type,
248 subject_table_name,
249 object_id,
250 object_type,
251 object_table_name,
252 relationship_code,
253 relationship_type,
254 comments,
255 start_date,
256 end_date,
257 status,
258 content_source_type,
259 attribute_category,
260 attribute1,
261 attribute2,
262 attribute3,
263 attribute4,
264 attribute5,
265 attribute6,
266 attribute7,
267 attribute8,
268 attribute9,
269 attribute10,
270 attribute11,
271 attribute12,
272 attribute13,
273 attribute14,
274 attribute15,
275 attribute16,
276 attribute17,
277 attribute18,
278 attribute19,
279 attribute20,
280 created_by_module,
281 application_id)
282 VALUES (l_relationship_id,
283 l_party_id,
284 x_relationship.subject_id,
285 x_relationship.subject_type,
286 x_relationship.subject_table_name,
287 x_relationship.object_id,
288 x_relationship.object_type,
289 x_relationship.object_table_name,
290 x_relationship.relationship_code,
291 x_relationship.relationship_type,
292 x_relationship.comments,
293 x_relationship.start_date,
294 x_relationship.end_date,
295 x_relationship.status,
296 x_relationship.content_source_type,
297 x_relationship.attribute_category,
298 x_relationship.attribute1,
299 x_relationship.attribute2,
300 x_relationship.attribute3,
301 x_relationship.attribute4,
302 x_relationship.attribute5,
303 x_relationship.attribute6,
304 x_relationship.attribute7,
305 x_relationship.attribute8,
306 x_relationship.attribute9,
307 x_relationship.attribute10,
308 x_relationship.attribute11,
309 x_relationship.attribute12,
310 x_relationship.attribute13,
311 x_relationship.attribute14,
312 x_relationship.attribute15,
313 x_relationship.attribute16,
314 x_relationship.attribute17,
315 x_relationship.attribute18,
316 x_relationship.attribute19,
317 x_relationship.attribute20,
318 x_relationship.created_by_module,
319 x_relationship.application_id);
320
321 RETURN l_relationship_id;
322 END create_relationship_gt;
323
324
325 /*=======================================================================+
326 | PRIVATE PROCEDURE exec_procedure |
327 | |
328 | DESCRIPTION |
329 | Execute a dynamic procedure call. |
330 | |
331 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
332 | |
333 | MODIFICATION HISTORY |
334 | 12-NOV-2001 J. del Callar Created. |
335 | 23-JAN-2004 Rajesh Jose Bug 2776412 |
336 +=======================================================================*/
337
338 PROCEDURE exec_procedure (x_profile_name IN VARCHAR2,
339 x_temp_id IN NUMBER) IS
340 l_exec_string VARCHAR2(300);
341 l_procedure_name VARCHAR2(240) := NULL;
342 l_debug_prefix VARCHAR2(30) := '';
343 -- Added for Bug 2776412
344 l_return_status VARCHAR2(1);
345 BEGIN
346 -- Debug info.
347 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
348 hz_utility_v2pub.debug(p_message=>'exec_procedure (' || x_profile_name|| ',' || x_temp_id || ')',
349 p_prefix =>l_debug_prefix,
350 p_msg_level=>fnd_log.level_statement);
351 END IF;
352
353 -- initialize API return status to success
354 l_return_status := fnd_api.g_ret_sts_success;
355
356 -- get the name of the procedure being executed.
357 l_procedure_name := fnd_profile.value(x_profile_name);
358
359 -- raise an error if no value is set for the profile option.
360 IF l_procedure_name IS NULL THEN
361 fnd_message.set_name('AR', 'HZ_DV_NULL_PROFILE_VALUE');
362 fnd_message.set_token('PROFILE', x_profile_name);
363 fnd_msg_pub.add;
364 RAISE hz_dyn_validation.null_profile_value;
365 END IF;
366
367 -- Debug info.
368 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
369 hz_utility_v2pub.debug(p_message=>' l_procedure_name=' || l_procedure_name,
370 p_prefix =>l_debug_prefix,
371 p_msg_level=>fnd_log.level_statement);
372 END IF;
373
374 -- Create the execution string.
375
376 l_exec_string := 'BEGIN ' || l_procedure_name ||
377 '(' ||x_temp_id|| ',:l_return_status); END;'; -- Bug 2776412
378
379 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
380 hz_utility_v2pub.debug(p_message=>' Executing: ' || l_exec_string,
381 p_prefix =>l_debug_prefix,
382 p_msg_level=>fnd_log.level_statement);
383 END IF;
384
385 -- Execute the dynamic call.
386 EXECUTE IMMEDIATE l_exec_string USING IN OUT l_return_status; -- Bug 2776412
387
388 IF l_return_status <> fnd_api.g_ret_sts_success THEN
389 RAISE hz_dyn_validation.execution_error;
390 END IF;
391
392
393 EXCEPTION
394 -- Catch instances where the procedure specified in the profile option
395 -- has not been defined in the database.
396 WHEN compile_error THEN
397 fnd_message.set_name('AR', 'HZ_DV_EXEC_ERROR');
398 fnd_message.set_token('PROCEDURE', l_procedure_name);
399 fnd_message.set_token('ERRM', SQLERRM);
400 fnd_msg_pub.add;
401 RAISE hz_dyn_validation.execution_error;
402
403 END exec_procedure;
404
405
406 /*=======================================================================+
407 | PUBLIC PROCEDURE validate_organization |
408 | |
409 | DESCRIPTION |
410 | Validate an organization given the name of the validation procedure |
411 | |
412 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
413 | hz_party_v2pub.organization_rec_type |
414 | MODIFICATION HISTORY |
415 | 12-NOV-2001 J. del Callar Created. |
416 +=======================================================================*/
417
418 PROCEDURE validate_organization (
419 x_organization IN hz_party_v2pub.organization_rec_type,
420 x_validation_profile IN VARCHAR2,
421 x_temp_id IN NUMBER DEFAULT NULL
422 ) IS
423 CURSOR orgidcur IS
424 SELECT hz_org_profile_val_gt_s.NEXTVAL
425 FROM DUAL;
426
427 l_org_profile_id NUMBER(15);
428 l_party_id NUMBER(15);
429 l_debug_prefix VARCHAR2(30) := '';
430 BEGIN
431 -- Debug info.
432 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
433 hz_utility_v2pub.debug(p_message=>'validate_organization, x_validation_profile='
434 || x_validation_profile,
435 p_prefix =>l_debug_prefix,
436 p_msg_level=>fnd_log.level_statement);
437 END IF;
438
439 -- Check the profile option name.
440 -- Raise an error if it is not an "allowed" profile option.
441 IF x_validation_profile <> 'HZ_BANK_VALIDATION_PROCEDURE'
442 AND x_validation_profile <> 'HZ_BANK_BRANCH_VALIDATION_PROCEDURE'
443 THEN
444 fnd_message.set_name('AR', 'HZ_DV_INVALID_PROFILE_OPTION');
445 fnd_message.set_token('PROFILE', x_validation_profile);
446 fnd_msg_pub.add;
447 RAISE hz_dyn_validation.invalid_profile_option;
448 END IF;
449
450 -- Create the party record
451 l_party_id := create_party_gt(x_organization.party_rec);
452
453 -- Get the org profile ID.
454 IF x_temp_id IS NOT NULL THEN
455 l_org_profile_id := x_temp_id;
456 ELSE
457 OPEN orgidcur;
458 FETCH orgidcur INTO l_org_profile_id;
459 IF orgidcur%NOTFOUND THEN
460 -- Close the cursor and raise an error if the group ID could not be
461 -- selected from the sequence.
462 CLOSE orgidcur;
463
464 fnd_message.set_name('AR', 'HZ_DV_ID_NOT_FOUND');
465 fnd_message.set_token('SEQUENCE', 'hz_org_profile_val_gt_s');
466 fnd_msg_pub.add;
467 RAISE fnd_api.g_exc_error;
468 END IF;
469 CLOSE orgidcur;
470 END IF;
471
472 -- Bug 3814832
473 DELETE FROM hz_org_profile_val_gt;
474 -- create the org profile record
475 INSERT INTO hz_org_profile_val_gt(organization_name,
476 temp_id,
477 temp_party_id,
478 duns_number_c,
479 enquiry_duns,
480 ceo_name,
481 ceo_title,
482 principal_name,
483 principal_title,
484 legal_status,
485 control_yr,
486 employees_total,
487 hq_branch_ind,
488 branch_flag,
489 oob_ind,
490 line_of_business,
491 cong_dist_code,
492 sic_code,
493 import_ind,
494 export_ind,
495 labor_surplus_ind,
496 debarment_ind,
497 minority_owned_ind,
498 minority_owned_type,
499 woman_owned_ind,
500 disadv_8a_ind,
501 small_bus_ind,
502 rent_own_ind,
503 debarments_count,
504 debarments_date,
505 failure_score,
506 failure_score_natnl_percentile,
507 failure_score_override_code,
508 failure_score_commentary,
509 global_failure_score,
510 db_rating,
511 credit_score,
512 credit_score_commentary,
513 paydex_score,
514 paydex_three_months_ago,
515 paydex_norm,
516 best_time_contact_begin,
517 best_time_contact_end,
518 organization_name_phonetic,
519 tax_reference,
520 gsa_indicator_flag,
521 jgzz_fiscal_code,
522 analysis_fy,
523 fiscal_yearend_month,
524 curr_fy_potential_revenue,
525 next_fy_potential_revenue,
526 year_established,
527 mission_statement,
528 organization_type,
529 business_scope,
530 corporation_class,
531 known_as,
532 known_as2,
533 known_as3,
534 known_as4,
535 known_as5,
536 local_bus_iden_type,
537 local_bus_identifier,
538 pref_functional_currency,
539 registration_type,
540 total_employees_text,
541 total_employees_ind,
542 total_emp_est_ind,
543 total_emp_min_ind,
544 parent_sub_ind,
545 incorp_year,
546 sic_code_type,
547 public_private_ownership_flag,
548 internal_flag,
549 local_activity_code_type,
550 local_activity_code,
551 emp_at_primary_adr,
552 emp_at_primary_adr_text,
553 emp_at_primary_adr_est_ind,
554 emp_at_primary_adr_min_ind,
555 high_credit,
556 avg_high_credit,
557 total_payments,
558 credit_score_class,
559 credit_score_natl_percentile,
560 credit_score_incd_default,
561 credit_score_age,
562 credit_score_date,
563 credit_score_commentary2,
564 credit_score_commentary3,
565 credit_score_commentary4,
566 credit_score_commentary5,
567 credit_score_commentary6,
568 credit_score_commentary7,
569 credit_score_commentary8,
570 credit_score_commentary9,
571 credit_score_commentary10,
572 failure_score_class,
573 failure_score_incd_default,
574 failure_score_age,
575 failure_score_date,
576 failure_score_commentary2,
577 failure_score_commentary3,
578 failure_score_commentary4,
579 failure_score_commentary5,
580 failure_score_commentary6,
581 failure_score_commentary7,
582 failure_score_commentary8,
583 failure_score_commentary9,
584 failure_score_commentary10,
585 maximum_credit_recommendation,
586 maximum_credit_currency_code,
587 displayed_duns_party_id,
588 content_source_type,
589 content_source_number,
590 attribute_category,
591 attribute1,
592 attribute2,
593 attribute3,
594 attribute4,
595 attribute5,
596 attribute6,
597 attribute7,
598 attribute8,
599 attribute9,
600 attribute10,
601 attribute11,
602 attribute12,
603 attribute13,
604 attribute14,
605 attribute15,
606 attribute16,
607 attribute17,
608 attribute18,
609 attribute19,
610 attribute20,
611 created_by_module,
612 application_id)
613 VALUES (x_organization.organization_name,
614 l_org_profile_id,
615 l_party_id,
616 x_organization.duns_number_c,
617 x_organization.enquiry_duns,
618 x_organization.ceo_name,
619 x_organization.ceo_title,
620 x_organization.principal_name,
621 x_organization.principal_title,
622 x_organization.legal_status,
623 x_organization.control_yr,
624 x_organization.employees_total,
625 x_organization.hq_branch_ind,
626 x_organization.branch_flag,
627 x_organization.oob_ind,
628 x_organization.line_of_business,
629 x_organization.cong_dist_code,
630 x_organization.sic_code,
631 x_organization.import_ind,
632 x_organization.export_ind,
633 x_organization.labor_surplus_ind,
634 x_organization.debarment_ind,
635 x_organization.minority_owned_ind,
636 x_organization.minority_owned_type,
637 x_organization.woman_owned_ind,
638 x_organization.disadv_8a_ind,
639 x_organization.small_bus_ind,
640 x_organization.rent_own_ind,
641 x_organization.debarments_count,
642 x_organization.debarments_date,
643 x_organization.failure_score,
644 x_organization.failure_score_natnl_percentile,
645 x_organization.failure_score_override_code,
646 x_organization.failure_score_commentary,
647 x_organization.global_failure_score,
648 x_organization.db_rating,
649 x_organization.credit_score,
650 x_organization.credit_score_commentary,
651 x_organization.paydex_score,
652 x_organization.paydex_three_months_ago,
653 x_organization.paydex_norm,
654 x_organization.best_time_contact_begin,
655 x_organization.best_time_contact_end,
656 x_organization.organization_name_phonetic,
657 x_organization.tax_reference,
658 x_organization.gsa_indicator_flag,
659 x_organization.jgzz_fiscal_code,
660 x_organization.analysis_fy,
661 x_organization.fiscal_yearend_month,
662 x_organization.curr_fy_potential_revenue,
663 x_organization.next_fy_potential_revenue,
664 x_organization.year_established,
665 x_organization.mission_statement,
666 x_organization.organization_type,
667 x_organization.business_scope,
668 x_organization.corporation_class,
669 x_organization.known_as,
670 x_organization.known_as2,
671 x_organization.known_as3,
672 x_organization.known_as4,
673 x_organization.known_as5,
674 x_organization.local_bus_iden_type,
675 x_organization.local_bus_identifier,
676 x_organization.pref_functional_currency,
677 x_organization.registration_type,
678 x_organization.total_employees_text,
679 x_organization.total_employees_ind,
680 x_organization.total_emp_est_ind,
681 x_organization.total_emp_min_ind,
682 x_organization.parent_sub_ind,
683 x_organization.incorp_year,
684 x_organization.sic_code_type,
685 x_organization.public_private_ownership_flag,
686 x_organization.internal_flag,
687 x_organization.local_activity_code_type,
688 x_organization.local_activity_code,
689 x_organization.emp_at_primary_adr,
690 x_organization.emp_at_primary_adr_text,
691 x_organization.emp_at_primary_adr_est_ind,
692 x_organization.emp_at_primary_adr_min_ind,
693 x_organization.high_credit,
694 x_organization.avg_high_credit,
695 x_organization.total_payments,
696 x_organization.credit_score_class,
697 x_organization.credit_score_natl_percentile,
698 x_organization.credit_score_incd_default,
699 x_organization.credit_score_age,
700 x_organization.credit_score_date,
701 x_organization.credit_score_commentary2,
702 x_organization.credit_score_commentary3,
703 x_organization.credit_score_commentary4,
704 x_organization.credit_score_commentary5,
705 x_organization.credit_score_commentary6,
706 x_organization.credit_score_commentary7,
707 x_organization.credit_score_commentary8,
708 x_organization.credit_score_commentary9,
709 x_organization.credit_score_commentary10,
710 x_organization.failure_score_class,
711 x_organization.failure_score_incd_default,
712 x_organization.failure_score_age,
713 x_organization.failure_score_date,
714 x_organization.failure_score_commentary2,
715 x_organization.failure_score_commentary3,
716 x_organization.failure_score_commentary4,
717 x_organization.failure_score_commentary5,
718 x_organization.failure_score_commentary6,
719 x_organization.failure_score_commentary7,
720 x_organization.failure_score_commentary8,
721 x_organization.failure_score_commentary9,
722 x_organization.failure_score_commentary10,
723 x_organization.maximum_credit_recommendation,
724 x_organization.maximum_credit_currency_code,
725 x_organization.displayed_duns_party_id,
726 x_organization.content_source_type,
727 x_organization.content_source_number,
728 x_organization.attribute_category,
729 x_organization.attribute1,
730 x_organization.attribute2,
731 x_organization.attribute3,
732 x_organization.attribute4,
733 x_organization.attribute5,
734 x_organization.attribute6,
735 x_organization.attribute7,
736 x_organization.attribute8,
737 x_organization.attribute9,
738 x_organization.attribute10,
739 x_organization.attribute11,
740 x_organization.attribute12,
741 x_organization.attribute13,
742 x_organization.attribute14,
743 x_organization.attribute15,
744 x_organization.attribute16,
745 x_organization.attribute17,
746 x_organization.attribute18,
747 x_organization.attribute19,
748 x_organization.attribute20,
749 x_organization.created_by_module,
750 x_organization.application_id);
751
752 -- Execute dynamic call.
753 exec_procedure(x_validation_profile, l_org_profile_id);
754
755 END validate_organization;
756
757
758 /*=======================================================================+
759 | PUBLIC PROCEDURE validate_group |
760 | |
761 | DESCRIPTION |
762 | Validate a group given the name of the validation procedure |
763 | |
764 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
765 | hz_party_v2pub.group_rec_type |
766 | MODIFICATION HISTORY |
767 | 12-NOV-2001 J. del Callar Created. |
768 +=======================================================================*/
769
770 PROCEDURE validate_group (
771 x_group IN hz_party_v2pub.group_rec_type,
772 x_validation_profile IN VARCHAR2,
773 x_temp_id IN NUMBER DEFAULT NULL
774 ) IS
775 CURSOR grpidcur IS
776 SELECT hz_group_val_gt_s.NEXTVAL
777 FROM DUAL;
778
779 l_group_id NUMBER(15);
780 l_party_id NUMBER(15);
781 l_debug_prefix VARCHAR2(30) := '';
782 BEGIN
783 -- Debug info.
784 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
785 hz_utility_v2pub.debug(p_message=>'validate_group, x_validation_profile='
786 || x_validation_profile,
787 p_prefix =>l_debug_prefix,
788 p_msg_level=>fnd_log.level_statement);
789 END IF;
790
791 -- Check the profile option name.
792 -- Raise an error if it is not an "allowed" profile option.
793 IF x_validation_profile <> 'HZ_BANKING_GROUP_VALIDATION_PROCEDURE'
794 THEN
795 fnd_message.set_name('AR', 'HZ_DV_INVALID_PROFILE_OPTION');
796 fnd_message.set_token('PROFILE', x_validation_profile);
797 fnd_msg_pub.add;
798 RAISE hz_dyn_validation.invalid_profile_option;
799 END IF;
800
801 -- Create the party record
802 l_party_id := create_party_gt(x_group.party_rec);
803
804 -- Get the group ID.
805 IF x_temp_id IS NOT NULL THEN
806 l_group_id := x_temp_id;
807 ELSE
808 OPEN grpidcur;
809 FETCH grpidcur INTO l_group_id;
810
811 IF grpidcur%NOTFOUND THEN
812 -- Close the cursor and raise an error if the group ID could not be
813 -- selected from the sequence.
814 CLOSE grpidcur;
815
816 fnd_message.set_name('AR', 'HZ_DV_ID_NOT_FOUND');
817 fnd_message.set_token('SEQUENCE', 'hz_group_val_gt_s');
818 fnd_msg_pub.add;
819 RAISE fnd_api.g_exc_error;
820 END IF;
821
822 CLOSE grpidcur;
823 END IF;
824
825 -- Bug 3814832
826 DELETE FROM hz_group_val_gt;
827 -- Create the group record.
828 INSERT INTO hz_group_val_gt(group_name,
829 temp_id,
830 temp_party_id,
831 group_type,
832 created_by_module,
833 application_id)
834 VALUES (x_group.group_name,
835 l_group_id,
836 l_party_id,
837 x_group.group_type,
838 x_group.created_by_module,
839 x_group.application_id);
840
841 -- Execute dynamic call.
842 exec_procedure(x_validation_profile, l_group_id);
843
844 END validate_group;
845
846
847 /*=======================================================================+
848 | PUBLIC PROCEDURE validate_relationship |
849 | |
850 | DESCRIPTION |
851 | Validate a relationship given the name of the validation procedure |
852 | |
853 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
854 | hz_relationship_v2pub.relationship_rec_type |
855 | MODIFICATION HISTORY |
856 | 12-NOV-2001 J. del Callar Created. |
857 +=======================================================================*/
858
859 PROCEDURE validate_relationship (
860 x_relationship IN hz_relationship_v2pub.relationship_rec_type,
861 x_validation_profile IN VARCHAR2,
862 x_temp_id IN NUMBER DEFAULT NULL
863 ) IS
864 l_relationship_id NUMBER(15);
865 l_debug_prefix VARCHAR2(30) := '';
866 BEGIN
867 -- Debug info.
868 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
869 hz_utility_v2pub.debug(p_message=>'validate_relationship_member, x_validation_prof='
870 || x_validation_profile,
871 p_prefix =>l_debug_prefix,
872 p_msg_level=>fnd_log.level_statement);
873 END IF;
874
875 -- Check the profile option name.
876 -- Raise an error if it is not an "allowed" profile option.
877 IF x_validation_profile NOT IN
878 ('HZ_BANKING_GROUP_MEMBER_VALIDATION_PROCEDURE',
879 'HZ_CLEARINGHOUSE_ASSIGNMENT_VALIDATION_PROCEDURE')
880 THEN
881 fnd_message.set_name('AR', 'HZ_DV_INVALID_PROFILE_OPTION');
882 fnd_message.set_token('PROFILE', x_validation_profile);
883 fnd_msg_pub.add;
884 RAISE hz_dyn_validation.invalid_profile_option;
885 END IF;
886
887 -- Create the relationship (includes the sub-party, if any).
888 l_relationship_id := create_relationship_gt(x_relationship, x_temp_id);
889
890 -- Execute dynamic call.
891 exec_procedure(x_validation_profile, l_relationship_id);
892
893 END validate_relationship;
894
895
896 /*=======================================================================+
897 | PUBLIC PROCEDURE validate_org_contact |
898 | |
899 | DESCRIPTION |
900 | Validate an organization contact given the name of the validation |
901 | procedure |
902 | |
903 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
904 | hz_party_contact_v2pub.org_contact_rec_type |
905 | MODIFICATION HISTORY |
906 | 12-NOV-2001 J. del Callar Created. |
907 +=======================================================================*/
908
909 PROCEDURE validate_org_contact (
910 x_org_contact IN hz_party_contact_v2pub.org_contact_rec_type,
911 x_validation_profile IN VARCHAR2,
912 x_temp_id IN NUMBER DEFAULT NULL
913 ) IS
914 CURSOR ocidcur IS
915 SELECT hz_contact_val_gt_s.NEXTVAL
916 FROM DUAL;
917
918 l_org_contact_id NUMBER(15);
919 l_relationship_id NUMBER(15);
920 l_debug_prefix VARCHAR2(30) := '';
921 BEGIN
922 -- Debug info.
923 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
924 hz_utility_v2pub.debug(p_message=>'validate_org_contact, x_validation_profile='|| x_validation_profile,
925 p_prefix =>l_debug_prefix,
926 p_msg_level=>fnd_log.level_statement);
927 END IF;
928
929 -- Check the profile option name.
930 -- Raise an error if it is not an "allowed" profile option.
931 IF x_validation_profile <> 'HZ_BANK_BRANCH_CONTACT_VALIDATION_PROCEDURE'
932 AND x_validation_profile <> 'HZ_BANK_CONTACT_VALIDATION_PROCEDURE'
933 THEN
934 fnd_message.set_name('AR', 'HZ_DV_INVALID_PROFILE_OPTION');
935 fnd_message.set_token('PROFILE', x_validation_profile);
936 fnd_msg_pub.add;
937 RAISE hz_dyn_validation.invalid_profile_option;
938 END IF;
939
940 -- Create the relationship record
941 l_relationship_id :=
942 create_relationship_gt(x_org_contact.party_rel_rec);
943
944 -- Get the org_contact ID.
945 IF x_temp_id IS NOT NULL THEN
946 l_org_contact_id := x_temp_id;
947 ELSE
948 OPEN ocidcur;
949 FETCH ocidcur INTO l_org_contact_id;
950
951 IF ocidcur%NOTFOUND THEN
952 -- Close the cursor and raise an error if the org_contact ID could not
953 -- be selected from the sequence.
954 CLOSE ocidcur;
955
956 fnd_message.set_name('AR', 'HZ_DV_ID_NOT_FOUND');
957 fnd_message.set_token('SEQUENCE', 'hz_contact_val_gt_s');
958 fnd_msg_pub.add;
959 RAISE fnd_api.g_exc_error;
960 END IF;
961 CLOSE ocidcur;
962 END IF;
963
964 -- Bug 3814832
965 DELETE FROM hz_org_contact_val_gt;
966 -- Create the org_contact record.
967 INSERT INTO hz_org_contact_val_gt(temp_id,
968 temp_party_id,
969 comments,
970 contact_number,
971 department_code,
972 department,
973 title,
974 job_title,
975 decision_maker_flag,
976 job_title_code,
977 reference_use_flag,
978 rank,
979 party_site_id,
980 orig_system_reference,
981 attribute_category,
982 attribute1,
983 attribute2,
984 attribute3,
985 attribute4,
986 attribute5,
987 attribute6,
988 attribute7,
989 attribute8,
990 attribute9,
991 attribute10,
992 attribute11,
993 attribute12,
994 attribute13,
995 attribute14,
996 attribute15,
997 attribute16,
998 attribute17,
999 attribute18,
1000 attribute19,
1001 attribute20,
1002 created_by_module,
1003 application_id)
1004 VALUES (l_org_contact_id,
1005 l_relationship_id,
1006 x_org_contact.comments,
1007 x_org_contact.contact_number,
1008 x_org_contact.department_code,
1009 x_org_contact.department,
1010 x_org_contact.title,
1011 x_org_contact.job_title,
1012 x_org_contact.decision_maker_flag,
1013 x_org_contact.job_title_code,
1014 x_org_contact.reference_use_flag,
1015 x_org_contact.rank,
1016 x_org_contact.party_site_id,
1017 x_org_contact.orig_system_reference,
1018 x_org_contact.attribute_category,
1019 x_org_contact.attribute1,
1020 x_org_contact.attribute2,
1021 x_org_contact.attribute3,
1022 x_org_contact.attribute4,
1023 x_org_contact.attribute5,
1024 x_org_contact.attribute6,
1025 x_org_contact.attribute7,
1026 x_org_contact.attribute8,
1027 x_org_contact.attribute9,
1028 x_org_contact.attribute10,
1029 x_org_contact.attribute11,
1030 x_org_contact.attribute12,
1031 x_org_contact.attribute13,
1032 x_org_contact.attribute14,
1033 x_org_contact.attribute15,
1034 x_org_contact.attribute16,
1035 x_org_contact.attribute17,
1036 x_org_contact.attribute18,
1037 x_org_contact.attribute19,
1038 x_org_contact.attribute20,
1039 x_org_contact.created_by_module,
1040 x_org_contact.application_id);
1041
1042 -- Execute dynamic call.
1043 exec_procedure(x_validation_profile, l_org_contact_id);
1044
1045 END validate_org_contact;
1046
1047
1048 /*=======================================================================+
1049 | PUBLIC PROCEDURE validate_party_site |
1050 | |
1051 | DESCRIPTION |
1052 | Validate a party site given the name of the validation procedure |
1053 | |
1054 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1055 | hz_party_site_v2pub.party_site_rec_type |
1056 | MODIFICATION HISTORY |
1057 | 11-13-2001 J. del Callar Created. |
1058 +=======================================================================*/
1059
1060 PROCEDURE validate_party_site (
1061 x_party_site IN hz_party_site_v2pub.party_site_rec_type,
1062 x_validation_profile IN VARCHAR2,
1063 x_temp_id IN NUMBER DEFAULT NULL
1064 ) IS
1065 CURSOR psidcur IS
1066 SELECT hz_party_site_val_gt_s.NEXTVAL
1067 FROM DUAL;
1068
1069 l_party_site_id NUMBER(15);
1070 l_debug_prefix VARCHAR2(30) := '';
1071 BEGIN
1072 -- Debug info.
1073 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1074 hz_utility_v2pub.debug(p_message=>'validate_party_site, x_validation_profile='|| x_validation_profile,
1075 p_prefix =>l_debug_prefix,
1076 p_msg_level=>fnd_log.level_statement);
1077 END IF;
1078
1079 -- Check the profile option name.
1080 -- Raise an error if it is not an "allowed" profile option.
1081 IF x_validation_profile <> 'HZ_BANK_BRANCH_SITE_VALIDATION_PROCEDURE'
1082 AND x_validation_profile <> 'HZ_BANK_SITE_VALIDATION_PROCEDURE'
1083 THEN
1084 fnd_message.set_name('AR', 'HZ_DV_INVALID_PROFILE_OPTION');
1085 fnd_message.set_token('PROFILE', x_validation_profile);
1086 fnd_msg_pub.add;
1087 RAISE hz_dyn_validation.invalid_profile_option;
1088 END IF;
1089
1090 -- Get the party_site ID.
1091 IF x_temp_id IS NOT NULL THEN
1092 l_party_site_id := x_temp_id;
1093 ELSE
1094 OPEN psidcur;
1095 FETCH psidcur INTO l_party_site_id;
1096
1097 IF psidcur%NOTFOUND THEN
1098 -- Close the cursor and raise an error if the party_site ID could not
1099 -- be selected from the sequence.
1100 CLOSE psidcur;
1101
1102 fnd_message.set_name('AR', 'HZ_DV_ID_NOT_FOUND');
1103 fnd_message.set_token('SEQUENCE', 'hz_party_site_val_gt_s');
1104 fnd_msg_pub.add;
1105 RAISE fnd_api.g_exc_error;
1106 END IF;
1107 CLOSE psidcur;
1108 END IF;
1109
1110 -- Bug 3814832
1111 DELETE FROM hz_party_site_val_gt;
1112 -- Create the party_site record.
1113 INSERT INTO hz_party_site_val_gt(temp_id,
1114 party_id,
1115 location_id,
1116 party_site_number,
1117 orig_system_reference,
1118 mailstop,
1119 identifying_address_flag,
1120 status,
1121 party_site_name,
1122 attribute_category,
1123 attribute1,
1124 attribute2,
1125 attribute3,
1126 attribute4,
1127 attribute5,
1128 attribute6,
1129 attribute7,
1130 attribute8,
1131 attribute9,
1132 attribute10,
1133 attribute11,
1134 attribute12,
1135 attribute13,
1136 attribute14,
1137 attribute15,
1138 attribute16,
1139 attribute17,
1140 attribute18,
1141 attribute19,
1142 attribute20,
1143 language,
1144 addressee,
1145 created_by_module,
1146 application_id)
1147 VALUES (l_party_site_id,
1148 x_party_site.party_id,
1149 x_party_site.location_id,
1150 x_party_site.party_site_number,
1151 x_party_site.orig_system_reference,
1152 x_party_site.mailstop,
1153 x_party_site.identifying_address_flag,
1154 x_party_site.status,
1155 x_party_site.party_site_name,
1156 x_party_site.attribute_category,
1157 x_party_site.attribute1,
1158 x_party_site.attribute2,
1159 x_party_site.attribute3,
1160 x_party_site.attribute4,
1161 x_party_site.attribute5,
1162 x_party_site.attribute6,
1163 x_party_site.attribute7,
1164 x_party_site.attribute8,
1165 x_party_site.attribute9,
1166 x_party_site.attribute10,
1167 x_party_site.attribute11,
1168 x_party_site.attribute12,
1169 x_party_site.attribute13,
1170 x_party_site.attribute14,
1171 x_party_site.attribute15,
1172 x_party_site.attribute16,
1173 x_party_site.attribute17,
1174 x_party_site.attribute18,
1175 x_party_site.attribute19,
1176 x_party_site.attribute20,
1177 x_party_site.language,
1178 x_party_site.addressee,
1179 x_party_site.created_by_module,
1180 x_party_site.application_id);
1181
1182 -- Execute dynamic call.
1183 exec_procedure(x_validation_profile, l_party_site_id);
1184
1185 END validate_party_site;
1186
1187
1188 /*=======================================================================+
1189 | PUBLIC PROCEDURE validate_location |
1190 | |
1191 | DESCRIPTION |
1192 | Validate a party site given the name of the validation procedure |
1193 | |
1194 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1195 | hz_location_v2pub.location_rec_type |
1196 | MODIFICATION HISTORY |
1197 | 11-13-2001 J. del Callar Created. |
1198 +=======================================================================*/
1199
1200 PROCEDURE validate_location (
1201 x_location IN hz_location_v2pub.location_rec_type,
1202 x_validation_profile IN VARCHAR2,
1203 x_temp_id IN NUMBER DEFAULT NULL
1204 ) IS
1205 CURSOR locidcur IS
1206 SELECT hz_location_val_gt_s.NEXTVAL
1207 FROM DUAL;
1208
1209 l_location_id NUMBER(15);
1210 l_debug_prefix VARCHAR2(30) := '';
1211 BEGIN
1212 -- Debug info.
1213 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1214 hz_utility_v2pub.debug(p_message=>'validate_location, x_validation_profile='|| x_validation_profile,
1215 p_prefix =>l_debug_prefix,
1216 p_msg_level=>fnd_log.level_statement);
1217 END IF;
1218
1219 -- Check the profile option name.
1220 -- Raise an error if it is not an "allowed" profile option.
1221 IF x_validation_profile <> 'HZ_BANK_BRANCH_LOCATION_VALIDATION_PROCEDURE'
1222 AND x_validation_profile <> 'HZ_BANK_LOCATION_VALIDATION_PROCEDURE'
1223 THEN
1224 fnd_message.set_name('AR', 'HZ_DV_INVALID_PROFILE_OPTION');
1225 fnd_message.set_token('PROFILE', x_validation_profile);
1226 fnd_msg_pub.add;
1227 RAISE hz_dyn_validation.invalid_profile_option;
1228 END IF;
1229
1230 -- Get the location ID.
1231 IF x_temp_id IS NOT NULL THEN
1232 l_location_id := x_temp_id;
1233 ELSE
1234 OPEN locidcur;
1235 FETCH locidcur INTO l_location_id;
1236
1237 IF locidcur%NOTFOUND THEN
1238 -- Close the cursor and raise an error if the location ID could not be
1239 -- selected from the sequence.
1240 CLOSE locidcur;
1241
1242 fnd_message.set_name('AR', 'HZ_DV_ID_NOT_FOUND');
1243 fnd_message.set_token('SEQUENCE', 'hz_location_val_gt_s');
1244 fnd_msg_pub.add;
1245 RAISE fnd_api.g_exc_error;
1246 END IF;
1247 CLOSE locidcur;
1248 END IF;
1249
1250 -- Bug 3814832
1251 DELETE FROM hz_location_val_gt;
1252 -- Create the location record.
1253 INSERT INTO hz_location_val_gt(temp_id,
1254 orig_system_reference,
1255 country,
1256 address1,
1257 address2,
1258 address3,
1259 address4,
1260 city,
1261 postal_code,
1262 state,
1263 province,
1264 county,
1265 address_key,
1266 address_style,
1267 validated_flag,
1268 address_lines_phonetic,
1269 po_box_number,
1270 house_number,
1271 street_suffix,
1272 street,
1273 street_number,
1274 floor,
1275 suite,
1276 postal_plus4_code,
1277 position,
1278 location_directions,
1279 address_effective_date,
1280 address_expiration_date,
1281 clli_code,
1282 language,
1283 short_description,
1284 description,
1285 loc_hierarchy_id,
1286 sales_tax_geocode,
1287 sales_tax_inside_city_limits,
1288 fa_location_id,
1289 content_source_type,
1290 attribute_category,
1291 attribute1,
1292 attribute2,
1293 attribute3,
1294 attribute4,
1295 attribute5,
1296 attribute6,
1297 attribute7,
1298 attribute8,
1299 attribute9,
1300 attribute10,
1301 attribute11,
1302 attribute12,
1303 attribute13,
1304 attribute14,
1305 attribute15,
1306 attribute16,
1307 attribute17,
1308 attribute18,
1309 attribute19,
1310 attribute20,
1311 timezone_id,
1312 created_by_module,
1313 application_id)
1314 VALUES (l_location_id,
1315 x_location.orig_system_reference,
1316 x_location.country,
1317 x_location.address1,
1318 x_location.address2,
1319 x_location.address3,
1320 x_location.address4,
1321 x_location.city,
1322 x_location.postal_code,
1323 x_location.state,
1324 x_location.province,
1325 x_location.county,
1326 x_location.address_key,
1327 x_location.address_style,
1328 x_location.validated_flag,
1329 x_location.address_lines_phonetic,
1330 x_location.po_box_number,
1331 x_location.house_number,
1332 x_location.street_suffix,
1333 x_location.street,
1334 x_location.street_number,
1335 x_location.floor,
1336 x_location.suite,
1337 x_location.postal_plus4_code,
1338 x_location.position,
1339 x_location.location_directions,
1340 x_location.address_effective_date,
1341 x_location.address_expiration_date,
1342 x_location.clli_code,
1343 x_location.language,
1344 x_location.short_description,
1345 x_location.description,
1346 x_location.loc_hierarchy_id,
1347 x_location.sales_tax_geocode,
1348 x_location.sales_tax_inside_city_limits,
1349 x_location.fa_location_id,
1350 x_location.content_source_type,
1351 x_location.attribute_category,
1352 x_location.attribute1,
1353 x_location.attribute2,
1354 x_location.attribute3,
1355 x_location.attribute4,
1356 x_location.attribute5,
1357 x_location.attribute6,
1358 x_location.attribute7,
1359 x_location.attribute8,
1360 x_location.attribute9,
1361 x_location.attribute10,
1362 x_location.attribute11,
1363 x_location.attribute12,
1364 x_location.attribute13,
1365 x_location.attribute14,
1366 x_location.attribute15,
1367 x_location.attribute16,
1368 x_location.attribute17,
1369 x_location.attribute18,
1370 x_location.attribute19,
1371 x_location.attribute20,
1372 x_location.timezone_id,
1373 x_location.created_by_module,
1374 x_location.application_id);
1375
1376 -- Execute dynamic call.
1377 exec_procedure(x_validation_profile, l_location_id);
1378
1379 END validate_location;
1380
1381
1382 /*=======================================================================+
1383 | PUBLIC PROCEDURE validate_contact_point |
1384 | |
1385 | DESCRIPTION |
1386 | Validate a party site given the name of the validation procedure |
1387 | |
1388 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1389 | hz_contact_point_v2pub.contact_point_rec_type |
1390 | MODIFICATION HISTORY |
1391 | 11-13-2001 J. del Callar Created. |
1392 +=======================================================================*/
1393
1394 PROCEDURE validate_contact_point (
1395 x_contact_point IN hz_contact_point_v2pub.contact_point_rec_type,
1396 x_edi_contact IN hz_contact_point_v2pub.edi_rec_type,
1397 x_eft_contact IN hz_contact_point_v2pub.eft_rec_type,
1398 x_email_contact IN hz_contact_point_v2pub.email_rec_type,
1399 x_phone_contact IN hz_contact_point_v2pub.phone_rec_type,
1400 x_telex_contact IN hz_contact_point_v2pub.telex_rec_type,
1401 x_web_contact IN hz_contact_point_v2pub.web_rec_type,
1402 x_validation_profile IN VARCHAR2,
1403 x_temp_id IN NUMBER DEFAULT NULL
1404 ) IS
1405 CURSOR psidcur IS
1406 SELECT hz_contact_point_val_gt_s.NEXTVAL
1407 FROM DUAL;
1408
1409 l_contact_point_id NUMBER(15);
1410 l_debug_prefix VARCHAR2(30) := '';
1411 BEGIN
1412 -- Debug info.
1413 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1414 hz_utility_v2pub.debug(p_message=>'validate_contact_point, x_validation_profile='|| x_validation_profile,
1415 p_prefix =>l_debug_prefix,
1416 p_msg_level=>fnd_log.level_statement);
1417 END IF;
1418
1419 -- Check the profile option name.
1420 -- Raise an error if it is not an "allowed" profile option.
1421 IF x_validation_profile <> 'HZ_BANK_BRANCH_CONTACT_POINT_VALIDATION_PROCEDURE'
1422 AND x_validation_profile <> 'HZ_BANK_CONTACT_POINT_VALIDATION_PROCEDURE'
1423 THEN
1424 fnd_message.set_name('AR', 'HZ_DV_INVALID_PROFILE_OPTION');
1425 fnd_message.set_token('PROFILE', x_validation_profile);
1426 fnd_msg_pub.add;
1427 RAISE hz_dyn_validation.invalid_profile_option;
1428 END IF;
1429
1430 -- Get the contact_point ID.
1431 IF x_temp_id IS NOT NULL THEN
1432 l_contact_point_id := x_temp_id;
1433 ELSE
1434 OPEN psidcur;
1435 FETCH psidcur INTO l_contact_point_id;
1436
1437 IF psidcur%NOTFOUND THEN
1438 -- Close the cursor and raise an error if the contact point ID could
1439 -- not be selected from the sequence.
1440 CLOSE psidcur;
1441
1442 fnd_message.set_name('AR', 'HZ_DV_ID_NOT_FOUND');
1443 fnd_message.set_token('SEQUENCE', 'hz_contact_point_val_gt_s');
1444 fnd_msg_pub.add;
1445 RAISE fnd_api.g_exc_error;
1446 END IF;
1447
1448 CLOSE psidcur;
1449 END IF;
1450
1451 -- Bug 3814832
1452 DELETE FROM hz_contact_point_val_gt;
1453 -- Create the contact_point record.
1454 INSERT INTO hz_contact_point_val_gt(temp_id,
1455 contact_point_type,
1456 status,
1457 owner_table_name,
1458 owner_table_id,
1459 primary_flag,
1460 orig_system_reference,
1461 content_source_type,
1462 attribute_category,
1463 attribute1,
1464 attribute2,
1465 attribute3,
1466 attribute4,
1467 attribute5,
1468 attribute6,
1469 attribute7,
1470 attribute8,
1471 attribute9,
1472 attribute10,
1473 attribute11,
1474 attribute12,
1475 attribute13,
1476 attribute14,
1477 attribute15,
1478 attribute16,
1479 attribute17,
1480 attribute18,
1481 attribute19,
1482 attribute20,
1483 contact_point_purpose,
1484 primary_by_purpose,
1485 created_by_module,
1486 application_id,
1487 edi_transaction_handling,
1488 edi_id_number,
1489 edi_payment_method,
1490 edi_payment_format,
1491 edi_remittance_method,
1492 edi_remittance_instruction,
1493 edi_tp_header_id,
1494 edi_ece_tp_location_code,
1495 eft_transmission_program_id,
1496 eft_printing_program_id,
1497 eft_user_number,
1498 eft_swift_code,
1499 email_format,
1500 email_address,
1501 phone_calling_calendar,
1502 last_contact_dt_time,
1503 timezone_id,
1504 phone_area_code,
1505 phone_country_code,
1506 phone_number,
1507 phone_extension,
1508 phone_line_type,
1509 raw_phone_number,
1510 telex_number,
1511 web_type,
1512 url)
1513 VALUES (l_contact_point_id,
1514 x_contact_point.contact_point_type,
1515 x_contact_point.status,
1516 x_contact_point.owner_table_name,
1517 x_contact_point.owner_table_id,
1518 x_contact_point.primary_flag,
1519 x_contact_point.orig_system_reference,
1520 x_contact_point.content_source_type,
1521 x_contact_point.attribute_category,
1522 x_contact_point.attribute1,
1523 x_contact_point.attribute2,
1524 x_contact_point.attribute3,
1525 x_contact_point.attribute4,
1526 x_contact_point.attribute5,
1527 x_contact_point.attribute6,
1528 x_contact_point.attribute7,
1529 x_contact_point.attribute8,
1530 x_contact_point.attribute9,
1531 x_contact_point.attribute10,
1532 x_contact_point.attribute11,
1533 x_contact_point.attribute12,
1534 x_contact_point.attribute13,
1535 x_contact_point.attribute14,
1536 x_contact_point.attribute15,
1537 x_contact_point.attribute16,
1538 x_contact_point.attribute17,
1539 x_contact_point.attribute18,
1540 x_contact_point.attribute19,
1541 x_contact_point.attribute20,
1542 x_contact_point.contact_point_purpose,
1543 x_contact_point.primary_by_purpose,
1544 x_contact_point.created_by_module,
1545 x_contact_point.application_id,
1546 x_edi_contact.edi_transaction_handling,
1547 x_edi_contact.edi_id_number,
1548 x_edi_contact.edi_payment_method,
1549 x_edi_contact.edi_payment_format,
1550 x_edi_contact.edi_remittance_method,
1551 x_edi_contact.edi_remittance_instruction,
1552 x_edi_contact.edi_tp_header_id,
1553 x_edi_contact.edi_ece_tp_location_code,
1554 x_eft_contact.eft_transmission_program_id,
1555 x_eft_contact.eft_printing_program_id,
1556 x_eft_contact.eft_user_number,
1557 x_eft_contact.eft_swift_code,
1558 x_email_contact.email_format,
1559 x_email_contact.email_address,
1560 x_phone_contact.phone_calling_calendar,
1561 x_phone_contact.last_contact_dt_time,
1562 x_phone_contact.timezone_id,
1563 x_phone_contact.phone_area_code,
1564 x_phone_contact.phone_country_code,
1565 x_phone_contact.phone_number,
1566 x_phone_contact.phone_extension,
1567 x_phone_contact.phone_line_type,
1568 x_phone_contact.raw_phone_number,
1569 x_telex_contact.telex_number,
1570 x_web_contact.web_type,
1571 x_web_contact.url);
1572
1573 -- Execute dynamic call.
1574 exec_procedure(x_validation_profile, l_contact_point_id);
1575
1576 END validate_contact_point;
1577
1578 END hz_dyn_validation;