[Home] [Help]
100: l_exist VARCHAR2(1);
101: BEGIN
102:
103: IF ( p_column_value IS NOT NULL
104: AND p_column_value <> fnd_api.g_miss_char ) THEN
105: OPEN c_nace;
106: FETCH c_nace INTO l_exist;
107: IF c_nace%NOTFOUND THEN
108: fnd_message.set_name('AR','HZ_API_INVALID_LOOKUP');
108: fnd_message.set_name('AR','HZ_API_INVALID_LOOKUP');
109: fnd_message.set_token('COLUMN',p_column);
110: fnd_message.set_token('LOOKUP_TYPE', p_lookup_type);
111: fnd_msg_pub.add;
112: x_return_status := fnd_api.g_ret_sts_error;
113: END IF;
114: CLOSE c_nace;
115: END IF;
116: END validate_fnd_lookup;
455: -- IN:
456: -- p_party_id ID identifying the party.
457: -- IN/OUT:
458: -- x_return_status Return status after the call. The status can
459: -- be FND_API.G_RET_STS_SUCCESS (success),
460: -- fnd_api.g_ret_sts_error (error),
461: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
462: --
463: -- NOTES
456: -- p_party_id ID identifying the party.
457: -- IN/OUT:
458: -- x_return_status Return status after the call. The status can
459: -- be FND_API.G_RET_STS_SUCCESS (success),
460: -- fnd_api.g_ret_sts_error (error),
461: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
462: --
463: -- NOTES
464: --
457: -- IN/OUT:
458: -- x_return_status Return status after the call. The status can
459: -- be FND_API.G_RET_STS_SUCCESS (success),
460: -- fnd_api.g_ret_sts_error (error),
461: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
462: --
463: -- NOTES
464: --
465: -- MODIFICATION HISTORY
496: fnd_message.set_token('FK', 'OWNER_TABLE_ID');
497: fnd_message.set_token('COLUMN', 'PARTY_ID');
498: fnd_message.set_token('TABLE', 'HZ_PARTIES');
499: fnd_msg_pub.add;
500: x_return_status := fnd_api.g_ret_sts_error;
501: RETURN;
502: END IF;
503:
504: -- Check that the party is an organization.
512: | fnd_message.set_token('TYPE', 'ORGANIZATION'); */
513: fnd_message.set_name('AR','HZ_EDI_EFT_ORG_PARTIES_ONLY');
514:
515: fnd_msg_pub.add;
516: x_return_status := fnd_api.g_ret_sts_error;
517: END IF;
518: CLOSE c_partytype;
519: END check_organization;
520:
534: -- p_contact_point_type Type of contact point. 'EFT' and 'EDI'-type
535: -- contact points are accepted. Others are ignored.
536: -- IN/OUT:
537: -- x_return_status Return status after the call. The status can
538: -- be FND_API.G_RET_STS_SUCCESS (success),
539: -- fnd_api.g_ret_sts_error (error),
540: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
541: --
542: -- NOTES
535: -- contact points are accepted. Others are ignored.
536: -- IN/OUT:
537: -- x_return_status Return status after the call. The status can
538: -- be FND_API.G_RET_STS_SUCCESS (success),
539: -- fnd_api.g_ret_sts_error (error),
540: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
541: --
542: -- NOTES
543: --
536: -- IN/OUT:
537: -- x_return_status Return status after the call. The status can
538: -- be FND_API.G_RET_STS_SUCCESS (success),
539: -- fnd_api.g_ret_sts_error (error),
540: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
541: --
542: -- NOTES
543: --
544: -- MODIFICATION HISTORY
582: | fnd_message.set_token('TABLE', 'FND_OBJECTS'); */
583: fnd_message.set_name('AR','HZ_EDI_EFT_ORG_PARTIES_ONLY');
584:
585: fnd_msg_pub.add;
586: x_return_status := fnd_api.g_ret_sts_error;
587: RETURN;
588: END IF;
589:
590: -- validate party type based on the contact point type.
593: check_organization(p_party_id, x_return_status);
594: ELSIF p_contact_point_type = 'EFT' THEN
595: -- make sure that the contact point belongs to an organization.
596: check_organization(p_party_id, x_return_status);
597: IF x_return_status <> fnd_api.g_ret_sts_error THEN
598: -- EFT is only currently allowed for banks.
599:
600: OPEN c_class;
601: FETCH c_class INTO l_class_category, l_dummy;
607: fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
608: fnd_message.set_token('RECORD', 'Code Assignment');
609: fnd_message.set_token('VALUE', TO_CHAR(p_party_id));
610: fnd_msg_pub.add;
611: x_return_status := fnd_api.g_ret_sts_error;
612: -- END IF;
613: ELSE
614: -- Check that the category class is a bank.
615: IF l_class_category NOT LIKE 'BANK%' THEN
618: fnd_message.set_name('AR', 'HZ_API_INVALID_PARTY_TYPE');
619: fnd_message.set_token('PARTY_ID', TO_CHAR(p_party_id));
620: fnd_message.set_token('TYPE', 'BANK or BANK BRANCH');
621: fnd_msg_pub.add;
622: x_return_status := fnd_api.g_ret_sts_error;
623: END IF;
624: CLOSE c_class;
625: END IF;
626: END IF;
644:
645: IF p_restricted = 'N' THEN
646: IF (p_create_update_flag = 'C' AND
647: (p_column_value IS NULL OR
648: p_column_value = fnd_api.g_miss_char)) OR
649: (p_create_update_flag = 'U' AND
650: p_column_value = fnd_api.g_miss_char)
651: THEN
652: l_error := TRUE;
646: IF (p_create_update_flag = 'C' AND
647: (p_column_value IS NULL OR
648: p_column_value = fnd_api.g_miss_char)) OR
649: (p_create_update_flag = 'U' AND
650: p_column_value = fnd_api.g_miss_char)
651: THEN
652: l_error := TRUE;
653: END IF;
654: ELSE
652: l_error := TRUE;
653: END IF;
654: ELSE
655: IF (p_column_value IS NULL OR
656: p_column_value = fnd_api.g_miss_char)
657: THEN
658: l_error := TRUE;
659: END IF;
660: END IF;
662: IF l_error THEN
663: fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
664: fnd_message.set_token('COLUMN', p_column);
665: fnd_msg_pub.add;
666: x_return_status := fnd_api.g_ret_sts_error;
667: END IF;
668:
669: END validate_mandatory;
670:
682:
683: IF p_restricted = 'N' THEN
684: IF (p_create_update_flag = 'C' AND
685: (p_column_value IS NULL OR
686: p_column_value = fnd_api.g_miss_num)) OR
687: (p_create_update_flag = 'U' AND
688: p_column_value = fnd_api.g_miss_num)
689: THEN
690: l_error := TRUE;
684: IF (p_create_update_flag = 'C' AND
685: (p_column_value IS NULL OR
686: p_column_value = fnd_api.g_miss_num)) OR
687: (p_create_update_flag = 'U' AND
688: p_column_value = fnd_api.g_miss_num)
689: THEN
690: l_error := TRUE;
691: END IF;
692: ELSE
690: l_error := TRUE;
691: END IF;
692: ELSE
693: IF (p_column_value IS NULL OR
694: p_column_value = fnd_api.g_miss_num)
695: THEN
696: l_error := TRUE;
697: END IF;
698: END IF;
700: IF l_error THEN
701: fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
702: fnd_message.set_token('COLUMN', p_column);
703: fnd_msg_pub.add;
704: x_return_status := fnd_api.g_ret_sts_error;
705: END IF;
706:
707: END validate_mandatory;
708:
720:
721: IF p_restricted = 'N' THEN
722: IF (p_create_update_flag = 'C' AND
723: (p_column_value IS NULL OR
724: p_column_value = FND_API.G_MISS_DATE)) OR
725: (p_create_update_flag = 'U' AND
726: p_column_value = FND_API.G_MISS_DATE)
727: THEN
728: l_error := TRUE;
722: IF (p_create_update_flag = 'C' AND
723: (p_column_value IS NULL OR
724: p_column_value = FND_API.G_MISS_DATE)) OR
725: (p_create_update_flag = 'U' AND
726: p_column_value = FND_API.G_MISS_DATE)
727: THEN
728: l_error := TRUE;
729: END IF;
730: ELSE
728: l_error := TRUE;
729: END IF;
730: ELSE
731: IF (p_column_value IS NULL OR
732: p_column_value = FND_API.G_MISS_DATE)
733: THEN
734: l_error := TRUE;
735: END IF;
736: END IF;
738: IF l_error THEN
739: fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
740: fnd_message.set_token('COLUMN', p_column);
741: fnd_msg_pub.add;
742: x_return_status := fnd_api.g_ret_sts_error;
743: END IF;
744:
745: END validate_mandatory;
746:
758: BEGIN
759:
760: IF p_column_value IS NOT NULL THEN
761: IF p_restricted = 'Y' THEN
762: IF (p_column_value <> fnd_api.g_miss_char OR
763: p_old_column_value IS NOT NULL) AND
764: (p_old_column_value IS NULL OR
765: p_column_value <> p_old_column_value)
766: THEN
767: l_error := TRUE;
768: END IF;
769: ELSE
770: IF (p_old_column_value IS NOT NULL AND -- BUG 3367582.
771: p_old_column_value <> FND_API.G_MISS_CHAR)
772: AND
773: (p_column_value = fnd_api.g_miss_char OR
774: p_column_value <> p_old_column_value)
775: THEN
769: ELSE
770: IF (p_old_column_value IS NOT NULL AND -- BUG 3367582.
771: p_old_column_value <> FND_API.G_MISS_CHAR)
772: AND
773: (p_column_value = fnd_api.g_miss_char OR
774: p_column_value <> p_old_column_value)
775: THEN
776: l_error := TRUE;
777: END IF;
782: fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
783: fnd_message.set_token('COLUMN', p_column);
784: fnd_msg_pub.add;
785: END IF;
786: x_return_status := fnd_api.g_ret_sts_error;
787: END IF;
788:
789: END validate_nonupdateable;
790:
802: BEGIN
803:
804: IF p_column_value IS NOT NULL THEN
805: IF p_restricted = 'Y' THEN
806: IF (p_column_value <> fnd_api.g_miss_num OR
807: p_old_column_value IS NOT NULL) AND
808: (p_old_column_value IS NULL OR
809: p_column_value <> p_old_column_value)
810: THEN
811: l_error := TRUE;
812: END IF;
813: ELSE
814: IF (p_old_column_value IS NOT NULL AND -- Bug 3367582.
815: p_old_column_value <> FND_API.G_MISS_NUM)
816: AND
817: (p_column_value = fnd_api.g_miss_num OR
818: p_column_value <> p_old_column_value)
819: THEN
813: ELSE
814: IF (p_old_column_value IS NOT NULL AND -- Bug 3367582.
815: p_old_column_value <> FND_API.G_MISS_NUM)
816: AND
817: (p_column_value = fnd_api.g_miss_num OR
818: p_column_value <> p_old_column_value)
819: THEN
820: l_error := TRUE;
821: END IF;
827: fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
828: fnd_message.set_token('COLUMN', p_column);
829: fnd_msg_pub.add;
830: END IF;
831: x_return_status := fnd_api.g_ret_sts_error;
832: END IF;
833:
834: END validate_nonupdateable;
835:
847: BEGIN
848:
849: IF p_column_value IS NOT NULL THEN
850: IF p_restricted = 'Y' THEN
851: IF (p_column_value <> FND_API.G_MISS_DATE OR
852: p_old_column_value IS NOT NULL) AND
853: (p_old_column_value IS NULL OR
854: p_column_value <> p_old_column_value)
855: THEN
856: l_error := TRUE;
857: END IF;
858: ELSE
859: IF (p_old_column_value IS NOT NULL AND -- Bug 3367582
860: p_old_column_value <> FND_API.G_MISS_DATE)
861: AND
862: (p_column_value = FND_API.G_MISS_DATE OR
863: p_column_value <> p_old_column_value)
864: THEN
858: ELSE
859: IF (p_old_column_value IS NOT NULL AND -- Bug 3367582
860: p_old_column_value <> FND_API.G_MISS_DATE)
861: AND
862: (p_column_value = FND_API.G_MISS_DATE OR
863: p_column_value <> p_old_column_value)
864: THEN
865: l_error := TRUE;
866: END IF;
872: fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
873: fnd_message.set_token('COLUMN', p_column);
874: fnd_msg_pub.add;
875: END IF;
876: x_return_status := fnd_api.g_ret_sts_error;
877: END IF;
878:
879: END validate_nonupdateable;
880:
899: l_end_date := p_end_date;
900: ELSIF p_create_update_flag = 'U' THEN
901: IF p_start_date IS NOT NULL
902: THEN
903: IF p_start_date = FND_API.G_MISS_DATE THEN
904: l_start_date := NULL;
905: ELSE
906: l_start_date := p_start_date;
907: END IF;
908: END IF;
909:
910: IF p_end_date IS NOT NULL
911: THEN
912: IF p_end_date = FND_API.G_MISS_DATE THEN
913: l_end_date := NULL;
914: ELSE
915: l_end_date := p_end_date;
916: END IF;
917: END IF;
918: END IF;
919:
920: IF l_end_date IS NOT NULL AND
921: l_end_date <> FND_API.G_MISS_DATE AND
922: (l_start_date IS NULL OR
923: l_start_date = FND_API.G_MISS_DATE OR
924: l_start_date > l_end_date)
925: THEN
919:
920: IF l_end_date IS NOT NULL AND
921: l_end_date <> FND_API.G_MISS_DATE AND
922: (l_start_date IS NULL OR
923: l_start_date = FND_API.G_MISS_DATE OR
924: l_start_date > l_end_date)
925: THEN
926: fnd_message.set_name('AR', 'HZ_API_DATE_GREATER');
927: fnd_message.set_token('DATE2', p_end_date_column_name);
926: fnd_message.set_name('AR', 'HZ_API_DATE_GREATER');
927: fnd_message.set_token('DATE2', p_end_date_column_name);
928: fnd_message.set_token('DATE1', p_start_date_column_name);
929: fnd_msg_pub.add;
930: x_return_status := fnd_api.g_ret_sts_error;
931: END IF;
932:
933: END validate_start_end_date;
934:
939: ) IS
940:
941: BEGIN
942:
943: IF p_column_value = fnd_api.g_miss_char THEN
944: fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
945: fnd_message.set_token('COLUMN', p_column);
946: fnd_msg_pub.add;
947: x_return_status := fnd_api.g_ret_sts_error;
943: IF p_column_value = fnd_api.g_miss_char THEN
944: fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
945: fnd_message.set_token('COLUMN', p_column);
946: fnd_msg_pub.add;
947: x_return_status := fnd_api.g_ret_sts_error;
948: END IF;
949:
950: END validate_cannot_update_to_null;
951:
956: ) IS
957:
958: BEGIN
959:
960: IF p_column_value = fnd_api.g_miss_num THEN
961: fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
962: fnd_message.set_token('COLUMN', p_column);
963: fnd_msg_pub.add;
964: x_return_status := fnd_api.g_ret_sts_error;
960: IF p_column_value = fnd_api.g_miss_num THEN
961: fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
962: fnd_message.set_token('COLUMN', p_column);
963: fnd_msg_pub.add;
964: x_return_status := fnd_api.g_ret_sts_error;
965: END IF;
966:
967: END validate_cannot_update_to_null;
968:
973: ) IS
974:
975: BEGIN
976:
977: IF p_column_value = FND_API.G_MISS_DATE THEN
978: fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
979: fnd_message.set_token('COLUMN', p_column);
980: fnd_msg_pub.add;
981: x_return_status := fnd_api.g_ret_sts_error;
977: IF p_column_value = FND_API.G_MISS_DATE THEN
978: fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
979: fnd_message.set_token('COLUMN', p_column);
980: fnd_msg_pub.add;
981: x_return_status := fnd_api.g_ret_sts_error;
982: END IF;
983:
984: END validate_cannot_update_to_null;
985:
995:
996: BEGIN
997:
998: IF p_column_value IS NOT NULL AND
999: p_column_value <> fnd_api.g_miss_char THEN
1000:
1001: IF p_lookup_type = 'YES/NO' THEN
1002: IF p_column_value NOT IN ('Y', 'N') THEN
1003: l_error := TRUE;
1015: fnd_message.set_name('AR', 'HZ_API_INVALID_LOOKUP');
1016: fnd_message.set_token('COLUMN', p_column);
1017: fnd_message.set_token('LOOKUP_TYPE', p_lookup_type);
1018: fnd_msg_pub.add;
1019: x_return_status := fnd_api.g_ret_sts_error;
1020: END IF;
1021: END IF;
1022:
1023: END validate_lookup;
1030:
1031: BEGIN
1032:
1033: IF p_column_value IS NOT NULL AND
1034: p_column_value <> FND_API.G_MISS_CHAR
1035: THEN
1036: IF NOT search(p_column_value || G_SPECIAL_STRING, 'FND_TERRITORIES')
1037: THEN
1038: fnd_message.set_name('AR', 'HZ_API_INVALID_COUNTRY_CODE');
1036: IF NOT search(p_column_value || G_SPECIAL_STRING, 'FND_TERRITORIES')
1037: THEN
1038: fnd_message.set_name('AR', 'HZ_API_INVALID_COUNTRY_CODE');
1039: fnd_msg_pub.add;
1040: x_return_status := fnd_api.g_ret_sts_error;
1041: END IF;
1042: END IF;
1043:
1044: END validate_country_code;
1062: -- p_web_rec Web record.
1063: -- p_rowid Rowid of the record (used only in update mode).
1064: -- IN/OUT:
1065: -- x_return_status Return status after the call. The status can
1066: -- be FND_API.G_RET_STS_SUCCESS (success),
1067: -- fnd_api.g_ret_sts_error (error),
1068: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1069: --
1070: -- NOTES
1063: -- p_rowid Rowid of the record (used only in update mode).
1064: -- IN/OUT:
1065: -- x_return_status Return status after the call. The status can
1066: -- be FND_API.G_RET_STS_SUCCESS (success),
1067: -- fnd_api.g_ret_sts_error (error),
1068: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1069: --
1070: -- NOTES
1071: --
1064: -- IN/OUT:
1065: -- x_return_status Return status after the call. The status can
1066: -- be FND_API.G_RET_STS_SUCCESS (success),
1067: -- fnd_api.g_ret_sts_error (error),
1068: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1069: --
1070: -- NOTES
1071: --
1072: -- MODIFICATION HISTORY
1277: -- from sequence by table handler.
1278:
1279: IF p_create_update_flag = 'C' THEN
1280: IF p_contact_point_rec.contact_point_id IS NOT NULL AND
1281: p_contact_point_rec.contact_point_id <> fnd_api.g_miss_num
1282: THEN
1283: OPEN c_dup (p_contact_point_rec.contact_point_id);
1284: FETCH c_dup INTO l_dummy;
1285:
1287: IF NVL(c_dup%FOUND, FALSE) THEN
1288: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
1289: fnd_message.set_token('COLUMN', 'contact_point_id');
1290: fnd_msg_pub.add;
1291: x_return_status := fnd_api.g_ret_sts_error;
1292: END IF;
1293: CLOSE c_dup;
1294:
1295: /*IF g_debug THEN
1598: fnd_message.set_token('COLUMN', l_fk_column);
1599: fnd_message.set_token('TABLE',
1600: LOWER(p_contact_point_rec.owner_table_name));
1601: fnd_msg_pub.add;
1602: x_return_status := fnd_api.g_ret_sts_error;
1603: END IF;
1604:
1605: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1606: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
1629: --------------------------------------
1630: /****Logical APIs - validation not required****/
1631: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1632: IF (p_contact_point_rec.orig_system is not null
1633: and p_contact_point_rec.orig_system <>fnd_api.g_miss_char)
1634: and (p_contact_point_rec.orig_system_reference is not null
1635: and p_contact_point_rec.orig_system_reference <>fnd_api.g_miss_char)
1636: and p_create_update_flag = 'U'
1637: then
1631: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1632: IF (p_contact_point_rec.orig_system is not null
1633: and p_contact_point_rec.orig_system <>fnd_api.g_miss_char)
1634: and (p_contact_point_rec.orig_system_reference is not null
1635: and p_contact_point_rec.orig_system_reference <>fnd_api.g_miss_char)
1636: and p_create_update_flag = 'U'
1637: then
1638: hz_orig_system_ref_pub.get_owner_table_id
1639: (p_orig_system => p_contact_point_rec.orig_system,
1641: p_owner_table_name => 'HZ_CONTACT_POINTS',
1642: x_owner_table_id => l_mosr_owner_table_id,
1643: x_return_status => l_temp_return_status);
1644:
1645: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
1646: l_mosr_owner_table_id= nvl(p_contact_point_rec.contact_point_id,l_mosr_owner_table_id))
1647: THEN
1648: l_validate_osr := 'N';
1649: -- if we can get owner_table_id based on osr and os in mosr table,
1654: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
1655: -- x_return_status. Set x_return_status to error, ONLY if there is error.
1656: -- In case of success, leave it to carry over previous value as before this call.
1657: -- Fix for Bug 5498116 (29-AUG-2006)
1658: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
1659: x_return_status := l_temp_return_status;
1660: END IF;
1661:
1662: end if;
1802: /****Logical APIs - validation not required****/
1803: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1804: -- status is lookup code in lookup type REGISTRY_STATUS
1805: IF p_contact_point_rec.status IS NOT NULL AND
1806: p_contact_point_rec.status <> fnd_api.g_miss_char AND
1807: (p_create_update_flag = 'C' OR
1808: (p_create_update_flag = 'U' AND
1809: p_contact_point_rec.status <> NVL(l_status, fnd_api.g_miss_char)))
1810: THEN
1805: IF p_contact_point_rec.status IS NOT NULL AND
1806: p_contact_point_rec.status <> fnd_api.g_miss_char AND
1807: (p_create_update_flag = 'C' OR
1808: (p_create_update_flag = 'U' AND
1809: p_contact_point_rec.status <> NVL(l_status, fnd_api.g_miss_char)))
1810: THEN
1811: validate_lookup (
1812: p_column => 'status',
1813: p_lookup_type => 'REGISTRY_STATUS',
1848: /****Logical APIs - validation not required****/
1849: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1850: -- primary_flag is lookup code in lookup type YES/NO
1851: IF p_contact_point_rec.primary_flag IS NOT NULL AND
1852: p_contact_point_rec.primary_flag <> fnd_api.g_miss_char
1853: THEN
1854: validate_lookup (
1855: p_column => 'primary_flag',
1856: p_lookup_type => 'YES/NO',
1875: -- check to ensure that an inactive contact is never marked
1876: -- as primary.
1877: IF p_create_update_flag = 'C' THEN
1878: IF p_contact_point_rec.primary_flag IS NULL OR
1879: p_contact_point_rec.primary_flag = fnd_api.g_miss_char
1880: THEN
1881: l_primary_flag := 'N';
1882: ELSE
1883: l_primary_flag := p_contact_point_rec.primary_flag;
1883: l_primary_flag := p_contact_point_rec.primary_flag;
1884: END IF;
1885:
1886: IF p_contact_point_rec.status IS NULL OR
1887: p_contact_point_rec.status = fnd_api.g_miss_char
1888: THEN
1889: l_status := 'A';
1890: ELSE
1891: l_status := p_contact_point_rec.status;
1909:
1910: IF l_error THEN
1911: fnd_message.set_name('AR', 'HZ_API_INACTIVE_NOT_PRIMARY');
1912: fnd_msg_pub.add;
1913: x_return_status := fnd_api.g_ret_sts_error;
1914: -- reset l_error for later use.
1915: l_error := FALSE;
1916: END IF;
1917:
1930: -- check to ensure that an inactive contact is never marked
1931: -- as preferred.
1932: IF p_create_update_flag = 'C' THEN
1933: IF p_contact_point_rec.primary_by_purpose IS NULL OR
1934: p_contact_point_rec.primary_by_purpose = fnd_api.g_miss_char
1935: THEN
1936: l_preferred_flag := 'N';
1937: ELSE
1938: l_preferred_flag := p_contact_point_rec.primary_by_purpose;
1952: END IF;
1953: IF l_error THEN
1954: fnd_message.set_name('AR', 'HZ_API_INACTIVE_NOT_PREFERRED');
1955: fnd_msg_pub.add;
1956: x_return_status := fnd_api.g_ret_sts_error;
1957:
1958: -- reset l_error for later use.
1959: l_error := FALSE;
1960: END IF;
1980: THEN
1981: IF p_contact_point_rec.primary_by_purpose = 'Y'
1982: THEN
1983: IF (p_contact_point_rec.contact_point_purpose IS NULL OR
1984: p_contact_point_rec.contact_point_purpose = FND_API.G_MISS_CHAR)
1985: THEN
1986: -- Error
1987: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MAND_DEP_FIELDS' );
1988: FND_MESSAGE.SET_TOKEN('COLUMN1', 'primary_by_purpose');
1988: FND_MESSAGE.SET_TOKEN('COLUMN1', 'primary_by_purpose');
1989: FND_MESSAGE.SET_TOKEN('VALUE1', 'Y');
1990: FND_MESSAGE.SET_TOKEN('COLUMN2','contact_point_purpose');
1991: FND_MSG_PUB.ADD;
1992: x_return_status := FND_API.G_RET_STS_ERROR;
1993:
1994: END IF;
1995: END IF;
1996: ELSIF (p_create_update_flag = 'U') THEN
1997: IF ( p_contact_point_rec.primary_by_purpose = 'Y' OR
1998: ( p_contact_point_rec.primary_by_purpose IS NULL AND
1999: l_primary_by_purpose = 'Y'))
2000: THEN
2001: IF ( p_contact_point_rec.contact_point_purpose = FND_API.G_MISS_CHAR OR
2002: ( p_contact_point_rec.contact_point_purpose IS NULL AND
2003: l_contact_point_purpose IS NULL))
2004: THEN
2005: -- Error
2007: FND_MESSAGE.SET_TOKEN('COLUMN1', 'primary_by_purpose');
2008: FND_MESSAGE.SET_TOKEN('VALUE1', 'Y');
2009: FND_MESSAGE.SET_TOKEN('COLUMN2','contact_point_purpose');
2010: FND_MSG_PUB.ADD;
2011: x_return_status := FND_API.G_RET_STS_ERROR;
2012: END IF;
2013: END IF;
2014: END IF;
2015: /*IF g_debug THEN
2039: p_create_update_flag = 'C') OR
2040: (l_contact_point_type <> 'WEB' AND
2041: p_create_update_flag = 'U')) AND
2042: p_contact_point_rec.contact_point_purpose IS NOT NULL AND
2043: p_contact_point_rec.contact_point_purpose <> fnd_api.g_miss_char AND
2044: (p_create_update_flag = 'C' OR
2045: (p_create_update_flag = 'U' AND
2046: p_contact_point_rec.contact_point_purpose <>
2047: NVL(l_contact_point_purpose, fnd_api.g_miss_char)))
2043: p_contact_point_rec.contact_point_purpose <> fnd_api.g_miss_char AND
2044: (p_create_update_flag = 'C' OR
2045: (p_create_update_flag = 'U' AND
2046: p_contact_point_rec.contact_point_purpose <>
2047: NVL(l_contact_point_purpose, fnd_api.g_miss_char)))
2048: THEN
2049: validate_lookup (
2050: p_column => 'contact_point_purpose',
2051: p_lookup_type => 'CONTACT_POINT_PURPOSE',
2086: --------------------------------------
2087:
2088: -- primary_by_purpose is lookup code in lookup type YES/NO
2089: IF p_contact_point_rec.primary_by_purpose IS NOT NULL AND
2090: p_contact_point_rec.primary_by_purpose <> fnd_api.g_miss_char
2091: THEN
2092: validate_lookup (
2093: p_column => 'primary_by_purpose',
2094: p_lookup_type => 'YES/NO',
2233: END IF;
2234: -- Bug 4226199 : check update privilege for email also
2235: IF p_create_update_flag = 'U' AND
2236: db_actual_content_source <> 'USER_ENTERED' THEN
2237: l_return_status := FND_API.G_RET_STS_SUCCESS;
2238: validate_nonupdateable (
2239: p_column => 'email_address',
2240: p_column_value => p_email_rec.email_address,
2241: p_old_column_value => l_email_address,
2241: p_old_column_value => l_email_address,
2242: x_return_status => l_return_status,
2243: p_raise_error => 'N');
2244:
2245: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2246: HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
2247: p_actual_content_source => db_actual_content_source,
2248: p_new_actual_content_source=> p_contact_point_rec.actual_content_source,
2249: p_entity_name => 'HZ_CONTACT_POINTS',
2255:
2256:
2257: -- email_format is lookup code in lookup type EMAIL_FORMAT
2258: IF p_email_rec.email_format IS NOT NULL AND
2259: p_email_rec.email_format <> fnd_api.g_miss_char AND
2260: (p_create_update_flag = 'C' OR
2261: (p_create_update_flag = 'U' AND
2262: p_email_rec.email_format <>
2263: NVL(l_email_format, fnd_api.g_miss_char)))
2259: p_email_rec.email_format <> fnd_api.g_miss_char AND
2260: (p_create_update_flag = 'C' OR
2261: (p_create_update_flag = 'U' AND
2262: p_email_rec.email_format <>
2263: NVL(l_email_format, fnd_api.g_miss_char)))
2264: THEN
2265: validate_lookup (
2266: p_column => 'email_format',
2267: p_lookup_type => 'EMAIL_FORMAT',
2332: -- passed in. However, you can not pass both two.
2333:
2334: IF p_create_update_flag = 'C' THEN
2335: IF ((p_phone_rec.phone_number IS NULL OR
2336: p_phone_rec.phone_number = fnd_api.g_miss_char) AND
2337: (p_phone_rec.raw_phone_number IS NULL OR
2338: p_phone_rec.raw_phone_number = fnd_api.g_miss_char)) OR
2339: ((p_phone_rec.phone_number IS NOT NULL AND
2340: p_phone_rec.phone_number <> fnd_api.g_miss_char) AND
2334: IF p_create_update_flag = 'C' THEN
2335: IF ((p_phone_rec.phone_number IS NULL OR
2336: p_phone_rec.phone_number = fnd_api.g_miss_char) AND
2337: (p_phone_rec.raw_phone_number IS NULL OR
2338: p_phone_rec.raw_phone_number = fnd_api.g_miss_char)) OR
2339: ((p_phone_rec.phone_number IS NOT NULL AND
2340: p_phone_rec.phone_number <> fnd_api.g_miss_char) AND
2341: (p_phone_rec.raw_phone_number IS NOT NULL AND
2342: p_phone_rec.raw_phone_number <> fnd_api.g_miss_char))
2336: p_phone_rec.phone_number = fnd_api.g_miss_char) AND
2337: (p_phone_rec.raw_phone_number IS NULL OR
2338: p_phone_rec.raw_phone_number = fnd_api.g_miss_char)) OR
2339: ((p_phone_rec.phone_number IS NOT NULL AND
2340: p_phone_rec.phone_number <> fnd_api.g_miss_char) AND
2341: (p_phone_rec.raw_phone_number IS NOT NULL AND
2342: p_phone_rec.raw_phone_number <> fnd_api.g_miss_char))
2343: THEN
2344: l_error := TRUE;
2338: p_phone_rec.raw_phone_number = fnd_api.g_miss_char)) OR
2339: ((p_phone_rec.phone_number IS NOT NULL AND
2340: p_phone_rec.phone_number <> fnd_api.g_miss_char) AND
2341: (p_phone_rec.raw_phone_number IS NOT NULL AND
2342: p_phone_rec.raw_phone_number <> fnd_api.g_miss_char))
2343: THEN
2344: l_error := TRUE;
2345: END IF;
2346: ELSE
2345: END IF;
2346: ELSE
2347: IF p_phone_rec.phone_number IS NOT NULL AND
2348: p_phone_rec.raw_phone_number IS NOT NULL AND
2349: ((p_phone_rec.phone_number = fnd_api.g_miss_char AND
2350: p_phone_rec.raw_phone_number = fnd_api.g_miss_char) OR
2351: (p_phone_rec.phone_number <> fnd_api.g_miss_char AND
2352: p_phone_rec.raw_phone_number <> fnd_api.g_miss_char))
2353: THEN
2346: ELSE
2347: IF p_phone_rec.phone_number IS NOT NULL AND
2348: p_phone_rec.raw_phone_number IS NOT NULL AND
2349: ((p_phone_rec.phone_number = fnd_api.g_miss_char AND
2350: p_phone_rec.raw_phone_number = fnd_api.g_miss_char) OR
2351: (p_phone_rec.phone_number <> fnd_api.g_miss_char AND
2352: p_phone_rec.raw_phone_number <> fnd_api.g_miss_char))
2353: THEN
2354: l_error := TRUE;
2347: IF p_phone_rec.phone_number IS NOT NULL AND
2348: p_phone_rec.raw_phone_number IS NOT NULL AND
2349: ((p_phone_rec.phone_number = fnd_api.g_miss_char AND
2350: p_phone_rec.raw_phone_number = fnd_api.g_miss_char) OR
2351: (p_phone_rec.phone_number <> fnd_api.g_miss_char AND
2352: p_phone_rec.raw_phone_number <> fnd_api.g_miss_char))
2353: THEN
2354: l_error := TRUE;
2355: END IF;
2348: p_phone_rec.raw_phone_number IS NOT NULL AND
2349: ((p_phone_rec.phone_number = fnd_api.g_miss_char AND
2350: p_phone_rec.raw_phone_number = fnd_api.g_miss_char) OR
2351: (p_phone_rec.phone_number <> fnd_api.g_miss_char AND
2352: p_phone_rec.raw_phone_number <> fnd_api.g_miss_char))
2353: THEN
2354: l_error := TRUE;
2355: END IF;
2356: END IF;
2357:
2358: IF l_error THEN
2359: fnd_message.set_name('AR', 'HZ_INVALID_PHONE_PARAMETER');
2360: fnd_msg_pub.add;
2361: x_return_status := fnd_api.g_ret_sts_error;
2362:
2363: -- Reset l_error for later use.
2364: l_error := TRUE;
2365: END IF;
2395:
2396:
2397: -- phone_line_type is lookup code in lookup type PHONE_LINE_TYPE
2398: IF p_phone_rec.phone_line_type IS NOT NULL AND
2399: p_phone_rec.phone_line_type <> fnd_api.g_miss_char AND
2400: (p_create_update_flag = 'C' OR
2401: (p_create_update_flag = 'U' AND
2402: p_phone_rec.phone_line_type <> NVL(l_phone_line_type,
2403: fnd_api.g_miss_char)))
2399: p_phone_rec.phone_line_type <> fnd_api.g_miss_char AND
2400: (p_create_update_flag = 'C' OR
2401: (p_create_update_flag = 'U' AND
2402: p_phone_rec.phone_line_type <> NVL(l_phone_line_type,
2403: fnd_api.g_miss_char)))
2404: THEN
2405: validate_lookup (
2406: p_column => 'phone_line_type',
2407: p_lookup_type => 'PHONE_LINE_TYPE',
2441: --------------------------------------
2442:
2443: -- timezone_id is foreign key of hz_timezones
2444: IF p_phone_rec.timezone_id IS NOT NULL AND
2445: p_phone_rec.timezone_id <> fnd_api.g_miss_num
2446: THEN
2447: OPEN c_timezone(p_phone_rec.timezone_id);
2448: FETCH c_timezone INTO l_dummy;
2449:
2452: fnd_message.set_token('FK', 'upgrade_tz_id');
2453: fnd_message.set_token('COLUMN', 'upgrade_tz_id');
2454: fnd_message.set_token('TABLE', 'fnd_timezones_vl');
2455: fnd_msg_pub.add;
2456: x_return_status := fnd_api.g_ret_sts_error;
2457: END IF;
2458:
2459: CLOSE c_timezone;
2460:
2494: -- phone_country_code is foreign key of hz_phone_country_codes
2495: -- Bug 2007066: during update, only validate phone_country_code if it
2496: -- has been changed.
2497: IF p_phone_rec.phone_country_code IS NOT NULL AND
2498: p_phone_rec.phone_country_code <> fnd_api.g_miss_char AND
2499: (p_create_update_flag = 'C' OR
2500: (p_create_update_flag = 'U' AND
2501: p_phone_rec.phone_country_code <> NVL(l_phone_country_code,
2502: fnd_api.g_miss_char)))
2498: p_phone_rec.phone_country_code <> fnd_api.g_miss_char AND
2499: (p_create_update_flag = 'C' OR
2500: (p_create_update_flag = 'U' AND
2501: p_phone_rec.phone_country_code <> NVL(l_phone_country_code,
2502: fnd_api.g_miss_char)))
2503: THEN
2504: OPEN c_countrycode(p_phone_rec.phone_country_code);
2505: FETCH c_countrycode INTO l_dummy;
2506:
2507: IF c_countrycode%NOTFOUND THEN
2508: --Bug 4474646
2509: fnd_message.set_name('AR', 'HZ_INVALID_PHONE_COUNTRY_CODE');
2510: fnd_msg_pub.add;
2511: x_return_status := fnd_api.g_ret_sts_error;
2512: END IF;
2513:
2514: CLOSE c_countrycode;
2515:
2603: -- SSM SST Integration and Extension
2604: --NVL(FND_PROFILE.value('HZ_UPDATE_THIRD_PARTY_DATA'), 'N') = 'N'
2605: -- HZ_UTILITY_V2PUB.is_purchased_content_source(db_actual_content_source) = 'Y'
2606: THEN
2607: l_return_status := FND_API.G_RET_STS_SUCCESS;
2608: validate_nonupdateable (
2609: p_column => 'phone_area_code',
2610: p_column_value => p_phone_rec.phone_area_code,
2611: p_old_column_value => l_phone_area_code,
2611: p_old_column_value => l_phone_area_code,
2612: x_return_status => l_return_status,
2613: p_raise_error => 'N');
2614:
2615: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2616: validate_nonupdateable (
2617: p_column => 'phone_country_code',
2618: p_column_value => p_phone_rec.phone_country_code,
2619: p_old_column_value => l_phone_country_code,
2620: x_return_status => l_return_status,
2621: p_raise_error => 'N');
2622: END IF;
2623:
2624: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2625: validate_nonupdateable (
2626: p_column => 'phone_number',
2627: p_column_value => p_phone_rec.phone_number,
2628: p_old_column_value => l_phone_number,
2628: p_old_column_value => l_phone_number,
2629: x_return_status => l_return_status,
2630: p_raise_error => 'N');
2631: END IF;
2632: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2633: validate_nonupdateable (
2634: p_column => 'phone_extension',
2635: p_column_value => p_phone_rec.phone_extension,
2636: p_old_column_value => l_phone_extension,
2637: x_return_status => l_return_status,
2638: p_raise_error => 'N');
2639: END IF;
2640:
2641: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2642: validate_nonupdateable (
2643: p_column => 'raw_phone_number',
2644: p_column_value => p_phone_rec.raw_phone_number,
2645: p_old_column_value => l_raw_phone_number,
2646: x_return_status => l_return_status,
2647: p_raise_error => 'N');
2648: END IF;
2649:
2650: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2651:
2652: HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
2653: p_actual_content_source => db_actual_content_source,
2654: p_new_actual_content_source=> p_contact_point_rec.actual_content_source,
2658: HZ_UTILITY_V2PUB.G_UPDATE_ACS := 'Y';
2659:
2660: /*FND_MESSAGE.SET_NAME('AR', 'HZ_NOTALLOW_UPDATE_THIRD_PARTY');
2661: FND_MSG_PUB.ADD;
2662: x_return_status := FND_API.G_RET_STS_ERROR;
2663: */
2664: END IF;
2665:
2666: /*IF g_debug THEN
2718:
2719: -- Bug 4226199 : check update privilege for telex_number also
2720: IF p_create_update_flag = 'U' AND
2721: db_actual_content_source <> 'USER_ENTERED' THEN
2722: l_return_status := FND_API.G_RET_STS_SUCCESS;
2723: validate_nonupdateable (
2724: p_column => 'telex_number',
2725: p_column_value => p_telex_rec.telex_number,
2726: p_old_column_value => l_telex,
2726: p_old_column_value => l_telex,
2727: x_return_status => l_return_status,
2728: p_raise_error => 'N');
2729:
2730: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2731: HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
2732: p_actual_content_source => db_actual_content_source,
2733: p_new_actual_content_source=> p_contact_point_rec.actual_content_source,
2734: p_entity_name => 'HZ_CONTACT_POINTS',
2789:
2790: -- Bug 4226199 : check update privilege for url also
2791: IF p_create_update_flag = 'U' AND
2792: db_actual_content_source <> 'USER_ENTERED' THEN
2793: l_return_status := FND_API.G_RET_STS_SUCCESS;
2794: validate_nonupdateable (
2795: p_column => 'url',
2796: p_column_value => p_web_rec.url,
2797: p_old_column_value => l_url,
2797: p_old_column_value => l_url,
2798: x_return_status => l_return_status,
2799: p_raise_error => 'N');
2800:
2801: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2802: HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
2803: p_actual_content_source => db_actual_content_source,
2804: p_new_actual_content_source=> p_contact_point_rec.actual_content_source,
2805: p_entity_name => 'HZ_CONTACT_POINTS',
2816: -- contact_point_purpose is lookup code in lookup type
2817: -- CONTACT_POINT_PURPOSE_WEB if contact_point_type = 'WEB'.
2818:
2819: IF p_contact_point_rec.contact_point_purpose IS NOT NULL AND
2820: p_contact_point_rec.contact_point_purpose <> fnd_api.g_miss_char AND
2821: (p_create_update_flag = 'C' OR
2822: (p_create_update_flag = 'U' AND
2823: p_contact_point_rec.contact_point_purpose <>
2824: NVL(l_contact_point_purpose, fnd_api.g_miss_char)))
2820: p_contact_point_rec.contact_point_purpose <> fnd_api.g_miss_char AND
2821: (p_create_update_flag = 'C' OR
2822: (p_create_update_flag = 'U' AND
2823: p_contact_point_rec.contact_point_purpose <>
2824: NVL(l_contact_point_purpose, fnd_api.g_miss_char)))
2825: THEN
2826: validate_lookup (
2827: p_column => 'contact_point_purpose',
2828: p_lookup_type => 'CONTACT_POINT_PURPOSE_WEB',
2888: * p_person_rec Person record.
2889: * p_old_person_rec Old person record.
2890: * IN/OUT:
2891: * x_return_status Return status after the call. The status can
2892: * be FND_API.G_RET_STS_SUCCESS (success),
2893: * FND_API.G_RET_STS_ERROR (error),
2894: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2895: *
2896: * NOTES
2889: * p_old_person_rec Old person record.
2890: * IN/OUT:
2891: * x_return_status Return status after the call. The status can
2892: * be FND_API.G_RET_STS_SUCCESS (success),
2893: * FND_API.G_RET_STS_ERROR (error),
2894: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2895: *
2896: * NOTES
2897: *
2890: * IN/OUT:
2891: * x_return_status Return status after the call. The status can
2892: * be FND_API.G_RET_STS_SUCCESS (success),
2893: * FND_API.G_RET_STS_ERROR (error),
2894: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2895: *
2896: * NOTES
2897: *
2898: * MODIFICATION HISTORY
2971: ---------------------------------
2972: /****Logical APIs - validation not required****/
2973: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
2974: IF (p_party_rec.orig_system is not null
2975: and p_party_rec.orig_system <>fnd_api.g_miss_char)
2976: and (p_party_rec.orig_system_reference is not null
2977: and p_party_rec.orig_system_reference <>fnd_api.g_miss_char)
2978: and p_create_update_flag = 'U'
2979: then
2973: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
2974: IF (p_party_rec.orig_system is not null
2975: and p_party_rec.orig_system <>fnd_api.g_miss_char)
2976: and (p_party_rec.orig_system_reference is not null
2977: and p_party_rec.orig_system_reference <>fnd_api.g_miss_char)
2978: and p_create_update_flag = 'U'
2979: then
2980: hz_orig_system_ref_pub.get_owner_table_id
2981: (p_orig_system => p_party_rec.orig_system,
2983: p_owner_table_name => 'HZ_PARTIES',
2984: x_owner_table_id => l_mosr_owner_table_id,
2985: x_return_status => l_temp_return_status);
2986:
2987: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
2988: l_mosr_owner_table_id= nvl(p_party_rec.party_id,l_mosr_owner_table_id))
2989: THEN
2990: l_validate_osr := 'N';
2991: -- if we can get owner_table_id based on osr and os in mosr table,
2996: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
2997: -- x_return_status. Set x_return_status to error, ONLY if there is error.
2998: -- In case of success, leave it to carry over previous value as before this call.
2999: -- Fix for Bug 5498116 (29-AUG-2006)
3000: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
3001: x_return_status := l_temp_return_status;
3002: END IF;
3003:
3004: end if;
3052: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3053: -- status is lookup code in lookup type REGISTRY_STATUS
3054: IF p_party_rec.status IS NOT NULL
3055: AND
3056: p_party_rec.status <> fnd_api.g_miss_char
3057: AND
3058: (p_create_update_flag = 'C'
3059: OR
3060: (p_create_update_flag = 'U'
3094: | AND p_create_update_flag = 'U'
3095: | THEN
3096: | fnd_message.set_name('AR', 'HZ_CREATED_BY_MISMATCH');
3097: | fnd_msg_pub.add;
3098: | x_return_status := fnd_api.g_ret_sts_error;
3099: | END IF;
3100: | END IF;
3101: */
3102:
3113: | AND p_create_update_flag = 'U'
3114: |THEN
3115: | fnd_message.set_name('AR', 'HZ_CREATED_BY_MISMATCH');
3116: | fnd_msg_pub.add;
3117: | x_return_status := fnd_api.g_ret_sts_error;
3118: |END IF;
3119: */
3120:
3121: -------------------------
3124:
3125: -- category_code is lookup code in lookup type CUSTOMER_CATEGORY
3126: IF p_party_rec.category_code IS NOT NULL
3127: AND
3128: p_party_rec.category_code <> fnd_api.g_miss_char
3129: AND
3130: (p_create_update_flag = 'C'
3131: OR
3132: (p_create_update_flag = 'U'
3190: * p_person_rec Person record.
3191: * p_old_person_rec Old person record.
3192: * IN/OUT:
3193: * x_return_status Return status after the call. The status can
3194: * be FND_API.G_RET_STS_SUCCESS (success),
3195: * fnd_api.g_ret_sts_error (error),
3196: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3197: *
3198: * NOTES
3191: * p_old_person_rec Old person record.
3192: * IN/OUT:
3193: * x_return_status Return status after the call. The status can
3194: * be FND_API.G_RET_STS_SUCCESS (success),
3195: * fnd_api.g_ret_sts_error (error),
3196: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3197: *
3198: * NOTES
3199: *
3192: * IN/OUT:
3193: * x_return_status Return status after the call. The status can
3194: * be FND_API.G_RET_STS_SUCCESS (success),
3195: * fnd_api.g_ret_sts_error (error),
3196: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3197: *
3198: * NOTES
3199: *
3200: * MODIFICATION HISTORY
3264: (p_person_rec.person_name_suffix <> p_old_person_rec.person_name_suffix)
3265: ) THEN
3266: fnd_message.set_name('AR', 'HZ_CUST_NAME_UPDT_NOT_ALLOWED');
3267: fnd_msg_pub.add;
3268: x_return_status := fnd_api.g_ret_sts_error;
3269: END IF;
3270:
3271: END IF; -- profile = N
3272:
3285: -----------------------------------
3286:
3287: -- during insert, either first_name or last_name has to be passed in
3288: IF p_create_update_flag = 'C' THEN
3289: IF (p_person_rec.person_first_name = fnd_api.g_miss_char or p_person_rec.person_first_name IS NULL)
3290: AND
3291: (p_person_rec.person_last_name = fnd_api.g_miss_char or p_person_rec.person_last_name IS NULL)
3292: THEN
3293: fnd_message.set_name('AR', 'HZ_FIRST_OR_LAST_NAME_REQUIRED');
3287: -- during insert, either first_name or last_name has to be passed in
3288: IF p_create_update_flag = 'C' THEN
3289: IF (p_person_rec.person_first_name = fnd_api.g_miss_char or p_person_rec.person_first_name IS NULL)
3290: AND
3291: (p_person_rec.person_last_name = fnd_api.g_miss_char or p_person_rec.person_last_name IS NULL)
3292: THEN
3293: fnd_message.set_name('AR', 'HZ_FIRST_OR_LAST_NAME_REQUIRED');
3294: fnd_msg_pub.add;
3295: x_return_status := fnd_api.g_ret_sts_error;
3291: (p_person_rec.person_last_name = fnd_api.g_miss_char or p_person_rec.person_last_name IS NULL)
3292: THEN
3293: fnd_message.set_name('AR', 'HZ_FIRST_OR_LAST_NAME_REQUIRED');
3294: fnd_msg_pub.add;
3295: x_return_status := fnd_api.g_ret_sts_error;
3296: END IF;
3297: END IF;
3298:
3299: -- during update, both first_name or last_name cannot be set to null
3298:
3299: -- during update, both first_name or last_name cannot be set to null
3300: IF p_create_update_flag = 'U' THEN
3301: -- Bug 3747386
3302: IF (p_person_rec.person_first_name = fnd_api.g_miss_char
3303: or NVL(p_person_rec.person_first_name,p_old_person_rec.person_first_name)=fnd_api.g_miss_char)
3304: AND
3305: (p_person_rec.person_last_name = fnd_api.g_miss_char
3306: or NVL(p_person_rec.person_last_name,p_old_person_rec.person_last_name)=fnd_api.g_miss_char) THEN
3299: -- during update, both first_name or last_name cannot be set to null
3300: IF p_create_update_flag = 'U' THEN
3301: -- Bug 3747386
3302: IF (p_person_rec.person_first_name = fnd_api.g_miss_char
3303: or NVL(p_person_rec.person_first_name,p_old_person_rec.person_first_name)=fnd_api.g_miss_char)
3304: AND
3305: (p_person_rec.person_last_name = fnd_api.g_miss_char
3306: or NVL(p_person_rec.person_last_name,p_old_person_rec.person_last_name)=fnd_api.g_miss_char) THEN
3307: fnd_message.set_name('AR', 'HZ_FIRST_OR_LAST_NAME_REQUIRED');
3301: -- Bug 3747386
3302: IF (p_person_rec.person_first_name = fnd_api.g_miss_char
3303: or NVL(p_person_rec.person_first_name,p_old_person_rec.person_first_name)=fnd_api.g_miss_char)
3304: AND
3305: (p_person_rec.person_last_name = fnd_api.g_miss_char
3306: or NVL(p_person_rec.person_last_name,p_old_person_rec.person_last_name)=fnd_api.g_miss_char) THEN
3307: fnd_message.set_name('AR', 'HZ_FIRST_OR_LAST_NAME_REQUIRED');
3308: fnd_msg_pub.add;
3309: x_return_status := fnd_api.g_ret_sts_error;
3302: IF (p_person_rec.person_first_name = fnd_api.g_miss_char
3303: or NVL(p_person_rec.person_first_name,p_old_person_rec.person_first_name)=fnd_api.g_miss_char)
3304: AND
3305: (p_person_rec.person_last_name = fnd_api.g_miss_char
3306: or NVL(p_person_rec.person_last_name,p_old_person_rec.person_last_name)=fnd_api.g_miss_char) THEN
3307: fnd_message.set_name('AR', 'HZ_FIRST_OR_LAST_NAME_REQUIRED');
3308: fnd_msg_pub.add;
3309: x_return_status := fnd_api.g_ret_sts_error;
3310: END IF;
3305: (p_person_rec.person_last_name = fnd_api.g_miss_char
3306: or NVL(p_person_rec.person_last_name,p_old_person_rec.person_last_name)=fnd_api.g_miss_char) THEN
3307: fnd_message.set_name('AR', 'HZ_FIRST_OR_LAST_NAME_REQUIRED');
3308: fnd_msg_pub.add;
3309: x_return_status := fnd_api.g_ret_sts_error;
3310: END IF;
3311: END IF;
3312:
3313: /*IF g_debug THEN
3328:
3329: -- person_pre_name_adjunct is lookup code in lookup type CONTACT_TITLE
3330: IF p_person_rec.person_pre_name_adjunct IS NOT NULL
3331: AND
3332: p_person_rec.person_pre_name_adjunct <> fnd_api.g_miss_char
3333: AND
3334: (p_create_update_flag = 'C'
3335: OR
3336: (p_create_update_flag = 'U'
3395: -- The validation will be called only if the value changes.
3396:
3397: IF p_person_rec.rent_own_ind IS NOT NULL
3398: AND
3399: p_person_rec.rent_own_ind <> fnd_api.g_miss_char
3400: AND
3401: (
3402: p_create_update_flag = 'C'
3403: OR
3456: -- If date_of_death is not null then deceased_flag must be 'Y'
3457:
3458: --2794173, Added the g_miss_date check here
3459: IF (p_person_rec.date_of_death IS NOT NULL AND
3460: p_person_rec.date_of_death <> FND_API.G_MISS_DATE)
3461: THEN
3462:
3463: IF p_person_rec.deceased_flag <> 'Y'
3464: THEN
3468: fnd_message.set_token('VALUE1', 'not null');
3469: fnd_message.set_token('COLUMN2', 'DECEASED_FLAG');
3470: fnd_message.set_token('VALUE2', 'Y');
3471: fnd_msg_pub.add;
3472: x_return_status := fnd_api.g_ret_sts_error;
3473: END IF;
3474:
3475: /*IF g_debug THEN
3476: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
3495: IF p_person_rec.date_of_birth > SYSDATE then
3496: fnd_message.set_name('AR','HZ_API_NO_FUTURE_DATE_ALLOWED');
3497: fnd_message.set_token('COLUMN','DATE_OF_BIRTH');
3498: fnd_msg_pub.add;
3499: x_return_status := fnd_api.g_ret_sts_error;
3500: END IF;
3501:
3502: IF p_person_rec.date_of_death > SYSDATE then
3503: fnd_message.set_name('AR','HZ_API_NO_FUTURE_DATE_ALLOWED');
3502: IF p_person_rec.date_of_death > SYSDATE then
3503: fnd_message.set_name('AR','HZ_API_NO_FUTURE_DATE_ALLOWED');
3504: fnd_message.set_token('COLUMN','DATE_OF_DEATH');
3505: fnd_msg_pub.add;
3506: x_return_status := fnd_api.g_ret_sts_error;
3507: END IF;
3508:
3509: /*IF g_debug THEN
3510: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
3544: temp_date_of_death := p_person_rec.date_of_death;
3545: END IF;
3546:
3547: IF (temp_date_of_birth IS NOT NULL AND
3548: temp_date_of_birth <> FND_API.G_MISS_DATE AND
3549: temp_date_of_death IS NOT NULL AND
3550: temp_date_of_death <> FND_API.G_MISS_DATE) THEN
3551: validate_start_end_date (
3552:
3546:
3547: IF (temp_date_of_birth IS NOT NULL AND
3548: temp_date_of_birth <> FND_API.G_MISS_DATE AND
3549: temp_date_of_death IS NOT NULL AND
3550: temp_date_of_death <> FND_API.G_MISS_DATE) THEN
3551: validate_start_end_date (
3552:
3553: p_create_update_flag => p_create_update_flag,
3554: p_start_date_column_name => 'date_of_birth',
3628:
3629: -- marital_status is lookup code in lookup type MARITAL_STATUS
3630: IF p_person_rec.marital_status IS NOT NULL
3631: AND
3632: p_person_rec.marital_status <> fnd_api.g_miss_char
3633: AND
3634: (p_create_update_flag = 'C'
3635: OR
3636: (p_create_update_flag = 'U'
3685: --------------------------
3686:
3687: -- gender is lookup code in lookup type HZ_GENDER
3688: IF p_person_rec.gender IS NOT NULL AND
3689: p_person_rec.gender <> fnd_api.g_miss_char AND
3690: (p_create_update_flag = 'C' OR
3691: (p_create_update_flag = 'U' AND
3692: p_person_rec.gender <> p_old_person_rec.gender))
3693: THEN
3711: --------------------------
3712:
3713: -- person_iden_type is lookup code in lookup type HZ_PERSON_IDEN_TYPE
3714: IF p_person_rec.person_iden_type IS NOT NULL AND
3715: p_person_rec.person_iden_type <> fnd_api.g_miss_char AND
3716: (p_create_update_flag = 'C' OR
3717: (p_create_update_flag = 'U' AND
3718: p_person_rec.person_iden_type <> p_old_person_rec.person_iden_type))
3719: THEN
3794: * p_person_rec Person record.
3795: * p_old_person_rec Old person record.
3796: * IN/OUT:
3797: * x_return_status Return status after the call. The status can
3798: * be FND_API.G_RET_STS_SUCCESS (success),
3799: * fnd_api.g_ret_sts_error (error),
3800: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3801: *
3802: * NOTES
3795: * p_old_person_rec Old person record.
3796: * IN/OUT:
3797: * x_return_status Return status after the call. The status can
3798: * be FND_API.G_RET_STS_SUCCESS (success),
3799: * fnd_api.g_ret_sts_error (error),
3800: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3801: *
3802: * NOTES
3803: * The previous validation for the same purpose was performed in the procedure
3796: * IN/OUT:
3797: * x_return_status Return status after the call. The status can
3798: * be FND_API.G_RET_STS_SUCCESS (success),
3799: * fnd_api.g_ret_sts_error (error),
3800: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3801: *
3802: * NOTES
3803: * The previous validation for the same purpose was performed in the procedure
3804: * HZ_REGISTRY_VALIDATE_V2PUB.validate_party and locked all fields against update
3899: cols_updated:=SUBSTR(cols_updated,2);
3900: FND_MESSAGE.SET_NAME('AR', 'HZ_CREATED_BY_MISMATCH');
3901: FND_MESSAGE.SET_TOKEN('COLUMN',cols_updated);
3902: FND_MSG_PUB.ADD;
3903: x_return_status := FND_API.G_RET_STS_ERROR;
3904: END IF;
3905:
3906: ELSIF(FND_PROFILE.VALUE('HZ_PROTECT_HR_PERSON_INFO')='N') --elsif corresonding to --IF(FND_PROFILE.VALUE('HZ_PROTECT_PERSON_INFO')='Y')--
3907: THEN
3982: cols_updated:=SUBSTR(cols_updated,2);
3983: FND_MESSAGE.SET_NAME('AR', 'HZ_CREATED_BY_MISMATCH');
3984: FND_MESSAGE.SET_TOKEN('COLUMN',cols_updated);
3985: FND_MSG_PUB.ADD;
3986: x_return_status := FND_API.G_RET_STS_ERROR;
3987: END IF;
3988:
3989: END IF;--end if corresponding to --IF(FND_PROFILE.VALUE('HZ_PROTECT_PERSON_INFO')='YES')--
3990:
4030: * p_group_rec Group record.
4031: * p_old_group_rec Old group record.
4032: * IN/OUT:
4033: * x_return_status Return status after the call. The status can
4034: * be FND_API.G_RET_STS_SUCCESS (success),
4035: * fnd_api.g_ret_sts_error (error),
4036: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4037: *
4038: * NOTES
4031: * p_old_group_rec Old group record.
4032: * IN/OUT:
4033: * x_return_status Return status after the call. The status can
4034: * be FND_API.G_RET_STS_SUCCESS (success),
4035: * fnd_api.g_ret_sts_error (error),
4036: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4037: *
4038: * NOTES
4039: *
4032: * IN/OUT:
4033: * x_return_status Return status after the call. The status can
4034: * be FND_API.G_RET_STS_SUCCESS (success),
4035: * fnd_api.g_ret_sts_error (error),
4036: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4037: *
4038: * NOTES
4039: *
4040: * MODIFICATION HISTORY
4154: * p_organization_rec Organization record.
4155: * p_old_organization_rec Old organization record.
4156: * IN/OUT:
4157: * x_return_status Return status after the call. The status can
4158: * be FND_API.G_RET_STS_SUCCESS (success),
4159: * fnd_api.g_ret_sts_error (error),
4160: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4161: *
4162: * NOTES
4155: * p_old_organization_rec Old organization record.
4156: * IN/OUT:
4157: * x_return_status Return status after the call. The status can
4158: * be FND_API.G_RET_STS_SUCCESS (success),
4159: * fnd_api.g_ret_sts_error (error),
4160: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4161: *
4162: * NOTES
4163: *
4156: * IN/OUT:
4157: * x_return_status Return status after the call. The status can
4158: * be FND_API.G_RET_STS_SUCCESS (success),
4159: * fnd_api.g_ret_sts_error (error),
4160: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4161: *
4162: * NOTES
4163: *
4164: * MODIFICATION HISTORY
4226: p_create_update_flag,
4227: p_organization_rec,
4228: x_return_status );
4229:
4230: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4231: RAISE FND_API.G_EXC_ERROR;
4232: END IF;
4233: END IF;
4234:
4227: p_organization_rec,
4228: x_return_status );
4229:
4230: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4231: RAISE FND_API.G_EXC_ERROR;
4232: END IF;
4233: END IF;
4234:
4235: -------------------------------------------------------------------------
4245:
4246: IF (p_organization_rec.organization_name <> p_old_organization_rec.organization_name) THEN
4247: fnd_message.set_name('AR', 'HZ_ORG_NAME_UPDT_NOT_ALLOWED');
4248: fnd_msg_pub.add;
4249: x_return_status := fnd_api.g_ret_sts_error;
4250: END IF;
4251:
4252: END IF; -- profile = N
4253:
4335:
4336: -- sic_code_type is lookup code in lookup type 'SIC_CODE_TYPE'
4337: IF p_organization_rec.sic_code_type IS NOT NULL
4338: AND
4339: p_organization_rec.sic_code_type <> fnd_api.g_miss_char
4340: AND
4341: (p_create_update_flag = 'C'
4342: OR
4343: (p_create_update_flag = 'U'
4371:
4372: -- fiscal_yearend_month is lookup code in lookup type 'MONTH'
4373: IF p_organization_rec.fiscal_yearend_month IS NOT NULL
4374: AND
4375: p_organization_rec.fiscal_yearend_month <> fnd_api.g_miss_char
4376: AND
4377: (p_create_update_flag = 'C'
4378: OR
4379: (p_create_update_flag = 'U'
4432:
4433: -- legal_status is lookup code in lookup type LEGAL_STATUS
4434: IF p_organization_rec.legal_status IS NOT NULL
4435: AND
4436: p_organization_rec.legal_status <> fnd_api.g_miss_char
4437: AND
4438: (p_create_update_flag = 'C'
4439: OR
4440: (p_create_update_flag = 'U'
4468:
4469: -- hq_branch_ind is lookup code in lookup type HQ_BRANCH_IND
4470: IF p_organization_rec.hq_branch_ind IS NOT NULL
4471: AND
4472: p_organization_rec.hq_branch_ind <> fnd_api.g_miss_char
4473: AND
4474: (p_create_update_flag = 'C'
4475: OR
4476: (p_create_update_flag = 'U'
4754:
4755: -- failure_score_commentary is lookup code in lookup type FAILURE_SCORE_COMMENTARY
4756: IF p_organization_rec.failure_score_commentary IS NOT NULL
4757: AND
4758: p_organization_rec.failure_score_commentary <> fnd_api.g_miss_char
4759: AND
4760: (p_create_update_flag = 'C'
4761: OR
4762: (p_create_update_flag = 'U'
4789:
4790: -- credit_score_commentary is lookup code in lookup type CREDIT_SCORE_COMMENTARY
4791: IF p_organization_rec.credit_score_commentary IS NOT NULL
4792: AND
4793: p_organization_rec.credit_score_commentary <> fnd_api.g_miss_char
4794: AND
4795: (p_create_update_flag = 'C'
4796: OR
4797: (p_create_update_flag = 'U'
4821:
4822: -- local_bus_iden_type is lookup code in lookup type LOCAL_BUS_IDEN_TYPE
4823: IF p_organization_rec.local_bus_iden_type IS NOT NULL
4824: AND
4825: p_organization_rec.local_bus_iden_type <> fnd_api.g_miss_char
4826: AND
4827: (p_create_update_flag = 'C'
4828: OR
4829: (p_create_update_flag = 'U'
4858:
4859: -- registration_type is lookup code in lookup type REGISTRATION_TYPE
4860: IF p_organization_rec.registration_type IS NOT NULL
4861: AND
4862: p_organization_rec.registration_type <> fnd_api.g_miss_char
4863: AND
4864: (p_create_update_flag = 'C'
4865: OR
4866: (p_create_update_flag = 'U'
4946:
4947: -- local_activity_code_type is lookup code in lookup type LOCAL_ACTIVITY_CODE_TYPE
4948: IF p_organization_rec.local_activity_code_type IS NOT NULL
4949: AND
4950: p_organization_rec.local_activity_code_type <> fnd_api.g_miss_char
4951: AND
4952: (p_create_update_flag = 'C'
4953: OR
4954: (p_create_update_flag = 'U'
4986: -- local_activity_code is lookup code in one of the lookup type NACE, NAF, NAISC_1997
4987:
4988: IF p_organization_rec.local_activity_code IS NOT NULL
4989: AND
4990: p_organization_rec.local_activity_code <> fnd_api.g_miss_char
4991: AND
4992: (p_create_update_flag = 'C'
4993: OR
4994: (p_create_update_flag = 'U'
5423:
5424: -- maximum_credit_currency_code is foreign key of fnd_currencies.currency_code
5425: IF p_organization_rec.maximum_credit_currency_code IS NOT NULL
5426: AND
5427: p_organization_rec.maximum_credit_currency_code <> fnd_api.g_miss_char
5428: THEN
5429: BEGIN
5430: SELECT 'Y'
5431: INTO l_dummy
5439: fnd_message.set_token('FK', 'maximum_credit_currency_code');
5440: fnd_message.set_token('COLUMN', 'currency_code');
5441: fnd_message.set_token('TABLE', 'fnd_currencies');
5442: fnd_msg_pub.add;
5443: x_return_status := fnd_api.g_ret_sts_error;
5444: END;
5445:
5446: IF g_debug THEN
5447: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
5580: -- The validation will be called only if the value changes.
5581:
5582: IF p_organization_rec.rent_own_ind IS NOT NULL
5583: AND
5584: p_organization_rec.rent_own_ind <> fnd_api.g_miss_char
5585: AND
5586: (
5587: p_create_update_flag = 'C'
5588: OR
5633: NVL(FND_PROFILE.value('HZ_UPDATE_THIRD_PARTY_DATA'), 'N') = 'N'*/
5634: /* HZ_UTILITY_V2PUB.is_purchased_content_source(p_organization_rec.actual_content_source) = 'Y' AND
5635: p_organization_rec.actual_content_source <> HZ_PARTY_V2PUB.G_MISS_CONTENT_SOURCE_TYPE
5636: THEN
5637: l_return_status := FND_API.G_RET_STS_SUCCESS;
5638:
5639: validate_nonupdateable (
5640: p_column => 'ceo_title',
5641: p_column_value => p_organization_rec.ceo_title,
5680:
5681: -- displayed_duns_party_id is foreign key of hz_parties.party_id
5682: IF p_organization_rec.displayed_duns_party_id IS NOT NULL
5683: AND
5684: p_organization_rec.displayed_duns_party_id <> fnd_api.g_miss_num
5685: THEN
5686: BEGIN
5687: SELECT 'Y'
5688: INTO l_dummy
5694: fnd_message.set_token('FK', 'displayed_duns_party_id');
5695: fnd_message.set_token('COLUMN', 'party_id');
5696: fnd_message.set_token('TABLE', 'hz_parties');
5697: fnd_msg_pub.add;
5698: x_return_status := fnd_api.g_ret_sts_error;
5699: END;
5700:
5701: /*IF g_debug THEN
5702: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
5773: * global_location_number GLOBAL_LOCATION_NUMBER column in HZ_PARTY_SITES.
5774: *
5775: * IN/OUT:
5776: * x_return_status Return status after the call. The status can
5777: * be FND_API.G_RET_STS_SUCCESS (success),
5778: * fnd_api.g_ret_sts_error (error),
5779: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5780: *
5781: * NOTES
5774: *
5775: * IN/OUT:
5776: * x_return_status Return status after the call. The status can
5777: * be FND_API.G_RET_STS_SUCCESS (success),
5778: * fnd_api.g_ret_sts_error (error),
5779: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5780: *
5781: * NOTES
5782: * Please see http://www.uc-council.org/checkdig.htm for details of the check-digit
5775: * IN/OUT:
5776: * x_return_status Return status after the call. The status can
5777: * be FND_API.G_RET_STS_SUCCESS (success),
5778: * fnd_api.g_ret_sts_error (error),
5779: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5780: *
5781: * NOTES
5782: * Please see http://www.uc-council.org/checkdig.htm for details of the check-digit
5783: * validation.
5837: IF ( check_digit + even_sum ) MOD 10 <> 0
5838: THEN -- Global location number did not satisfy the check digit validation.
5839: FND_MESSAGE.SET_NAME('AR','HZ_API_GLOBAL_LOC_NUM_ERRORS');
5840: FND_MSG_PUB.ADD;
5841: x_return_status := FND_API.G_RET_STS_ERROR;
5842: END IF;
5843:
5844: ELSE -- Global_location_number is not 13 digits long.
5845: FND_MESSAGE.SET_NAME('AR','HZ_API_GLOBAL_LOC_NUM_ERRORS');
5843:
5844: ELSE -- Global_location_number is not 13 digits long.
5845: FND_MESSAGE.SET_NAME('AR','HZ_API_GLOBAL_LOC_NUM_ERRORS');
5846: FND_MSG_PUB.ADD;
5847: x_return_status := FND_API.G_RET_STS_ERROR;
5848: END IF;
5849:
5850: ELSE -- corresponding to IF TRIM(TRANSLATE(global_location_number,'0123456789',' '))
5851:
5852: -- Since there are non numeric characters, therefore the expression evaluated in the if
5853: -- clause is not null.
5854: FND_MESSAGE.SET_NAME('AR','HZ_API_GLOBAL_LOC_NUM_ERRORS');
5855: FND_MSG_PUB.ADD;
5856: x_return_status := FND_API.G_RET_STS_ERROR;
5857: END IF;
5858:
5859: IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
5860: THEN
5887: * p_party_site_rec Party site record.
5888: * p_rowid Rowid of the record (used only in update mode).
5889: * IN/OUT:
5890: * x_return_status Return status after the call. The status can
5891: * be FND_API.G_RET_STS_SUCCESS (success),
5892: * fnd_api.g_ret_sts_error (error),
5893: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5894: *
5895: * NOTES
5888: * p_rowid Rowid of the record (used only in update mode).
5889: * IN/OUT:
5890: * x_return_status Return status after the call. The status can
5891: * be FND_API.G_RET_STS_SUCCESS (success),
5892: * fnd_api.g_ret_sts_error (error),
5893: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5894: *
5895: * NOTES
5896: *
5889: * IN/OUT:
5890: * x_return_status Return status after the call. The status can
5891: * be FND_API.G_RET_STS_SUCCESS (success),
5892: * fnd_api.g_ret_sts_error (error),
5893: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5894: *
5895: * NOTES
5896: *
5897: * MODIFICATION HISTORY
6033: IF p_create_update_flag = 'C'
6034: AND
6035: p_party_site_rec.party_id IS NOT NULL
6036: AND
6037: p_party_site_rec.party_id <> fnd_api.g_miss_num
6038: AND
6039: p_party_site_rec.party_id <> -1
6040: THEN
6041: BEGIN
6049: fnd_message.set_token('FK', 'party_id');
6050: fnd_message.set_token('COLUMN', 'party_id');
6051: fnd_message.set_token('TABLE', 'hz_parties');
6052: fnd_msg_pub.add;
6053: x_return_status := fnd_api.g_ret_sts_error;
6054: END;
6055:
6056: /*IF g_debug THEN
6057: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
6121: IF p_create_update_flag = 'C'
6122: AND
6123: p_party_site_rec.location_id IS NOT NULL
6124: AND
6125: p_party_site_rec.location_id <> fnd_api.g_miss_num
6126: THEN
6127: BEGIN
6128:
6129: -- Bug 2197181: for mix-n-match, column actual_content_source
6141: fnd_message.set_token('FK', 'location_id');
6142: fnd_message.set_token('COLUMN', 'location_id');
6143: fnd_message.set_token('TABLE', 'hz_locations');
6144: fnd_msg_pub.add;
6145: x_return_status := fnd_api.g_ret_sts_error;
6146: END;
6147:
6148: /*IF g_debug THEN
6149: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
6191: ---------------------------------
6192: /****Logical APIs - validation not required****/
6193: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
6194: IF (p_party_site_rec.orig_system is not null
6195: and p_party_site_rec.orig_system <>fnd_api.g_miss_char)
6196: and (p_party_site_rec.orig_system_reference is not null
6197: and p_party_site_rec.orig_system_reference <>fnd_api.g_miss_char)
6198: and p_create_update_flag = 'U'
6199: then
6193: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
6194: IF (p_party_site_rec.orig_system is not null
6195: and p_party_site_rec.orig_system <>fnd_api.g_miss_char)
6196: and (p_party_site_rec.orig_system_reference is not null
6197: and p_party_site_rec.orig_system_reference <>fnd_api.g_miss_char)
6198: and p_create_update_flag = 'U'
6199: then
6200: hz_orig_system_ref_pub.get_owner_table_id
6201: (p_orig_system => p_party_site_rec.orig_system,
6203: p_owner_table_name => 'HZ_PARTY_SITES',
6204: x_owner_table_id => l_mosr_owner_table_id,
6205: x_return_status => l_temp_return_status);
6206:
6207: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
6208: l_mosr_owner_table_id= nvl(p_party_site_rec.party_site_id,l_mosr_owner_table_id))
6209: THEN
6210: l_validate_osr := 'N';
6211: -- if we can get owner_table_id based on osr and os in mosr table,
6216: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
6217: -- x_return_status. Set x_return_status to error, ONLY if there is error.
6218: -- In case of success, leave it to carry over previous value as before this call.
6219: -- Fix for Bug 5498116 (29-AUG-2006)
6220: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
6221: x_return_status := l_temp_return_status;
6222: END IF;
6223:
6224: end if;
6283: THEN
6284: /* new message */
6285: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_CANT_ACTIVATE_SITE' );
6286: FND_MSG_PUB.ADD;
6287: x_return_status := FND_API.G_RET_STS_ERROR;
6288: END IF;
6289: END IF;
6290:
6291: /****Logical APIs - validation not required****/
6292: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
6293: -- status is lookup code in lookup type REGISTRY_STATUS
6294: IF p_party_site_rec.status IS NOT NULL
6295: AND
6296: p_party_site_rec.status <> fnd_api.g_miss_char
6297: AND
6298: (p_create_update_flag = 'C'
6299: OR
6300: (p_create_update_flag = 'U'
6298: (p_create_update_flag = 'C'
6299: OR
6300: (p_create_update_flag = 'U'
6301: AND
6302: p_party_site_rec.status <> NVL(l_status, fnd_api.g_miss_char)
6303: )
6304: )
6305: THEN
6306: validate_lookup (
6355: -- If the identify address flag is set to Yes, the status column should
6356: -- not be Inactive
6357:
6358: IF p_party_site_rec.identifying_address_flag is NOT NULL
6359: AND p_party_site_rec.identifying_address_flag <> fnd_api.g_miss_char
6360: AND (p_create_update_flag = 'C'
6361: OR (p_create_update_flag = 'U'
6362: AND p_party_site_rec.identifying_address_flag <>
6363: NVL(l_identifying_address_flag, fnd_api.g_miss_char)))
6359: AND p_party_site_rec.identifying_address_flag <> fnd_api.g_miss_char
6360: AND (p_create_update_flag = 'C'
6361: OR (p_create_update_flag = 'U'
6362: AND p_party_site_rec.identifying_address_flag <>
6363: NVL(l_identifying_address_flag, fnd_api.g_miss_char)))
6364: THEN
6365: SELECT DECODE(p_party_site_rec.identifying_address_flag,
6366: 'Y', DECODE(p_party_site_rec.status,
6367: 'I', 'N',
6375: fnd_message.set_token('ENTITY', 'Site');
6376: -- fnd_message.set_token('COLUMN1', 'identifying_address');
6377: -- fnd_message.set_token('COLUMN2', 'status');
6378: fnd_msg_pub.add;
6379: x_return_status := FND_API.G_RET_STS_ERROR;
6380: END IF;
6381:
6382: /*IF g_debug THEN
6383: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
6403:
6404: -- language has foreign key fnd_languages.language_code
6405: IF p_party_site_rec.language IS NOT NULL
6406: AND
6407: p_party_site_rec.language <> fnd_api.g_miss_char
6408: THEN
6409: BEGIN
6410: SELECT 'Y'
6411: INTO l_dummy
6418: fnd_message.set_token('FK', 'language');
6419: fnd_message.set_token('COLUMN', 'language_code');
6420: fnd_message.set_token('TABLE', 'fnd_languages(installed)');
6421: fnd_msg_pub.add;
6422: x_return_status := fnd_api.g_ret_sts_error;
6423: END;
6424:
6425: -- IF g_debug THEN
6426: -- hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
6441: -------------------------------------------------
6442: -- validate global_location_number.( Bug 3175816)
6443: -------------------------------------------------
6444: IF p_party_site_rec.global_location_number IS NOT NULL AND
6445: p_party_site_rec.global_location_number <> FND_API.G_MISS_CHAR AND
6446: (
6447: p_create_update_flag = 'C' OR
6448: (
6449: p_create_update_flag = 'U' AND
6503: * p_party_site_use_rec Party site use record.
6504: * p_rowid Rowid of the record (used only in update mode).
6505: * IN/OUT:
6506: * x_return_status Return status after the call. The status can
6507: * be FND_API.G_RET_STS_SUCCESS (success),
6508: * fnd_api.g_ret_sts_error (error),
6509: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6510: *
6511: * NOTES
6504: * p_rowid Rowid of the record (used only in update mode).
6505: * IN/OUT:
6506: * x_return_status Return status after the call. The status can
6507: * be FND_API.G_RET_STS_SUCCESS (success),
6508: * fnd_api.g_ret_sts_error (error),
6509: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6510: *
6511: * NOTES
6512: *
6505: * IN/OUT:
6506: * x_return_status Return status after the call. The status can
6507: * be FND_API.G_RET_STS_SUCCESS (success),
6508: * fnd_api.g_ret_sts_error (error),
6509: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6510: *
6511: * NOTES
6512: *
6513: * MODIFICATION HISTORY
6619: IF p_create_update_flag = 'C'
6620: AND
6621: p_party_site_use_rec.party_site_id IS NOT NULL
6622: AND
6623: p_party_site_use_rec.party_site_id <> fnd_api.g_miss_num
6624: THEN
6625: BEGIN
6626: SELECT 'Y'
6627: INTO l_dummy
6633: fnd_message.set_token('FK', 'party_site_id');
6634: fnd_message.set_token('COLUMN', 'party_site_id');
6635: fnd_message.set_token('TABLE', 'hz_party_sites');
6636: fnd_msg_pub.add;
6637: x_return_status := fnd_api.g_ret_sts_error;
6638: END;
6639:
6640: /*IF g_debug THEN
6641: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
6757: -- Bug 3988537.
6758: WHERE PARTY_SITE_ID = nvl(p_party_site_use_rec.party_site_id,l_party_site_id)
6759: AND SITE_USE_TYPE = nvl(p_party_site_use_rec.site_use_type,l_site_use_type)
6760: AND STATUS = 'A'
6761: AND PARTY_SITE_USE_ID <> NVL(p_party_site_use_rec.party_site_use_id, fnd_api.g_miss_num);
6762:
6763: fnd_message.set_name('AR', 'HZ_API_UNIQUE_SITE_USE_TYPE');
6764: fnd_msg_pub.add;
6765: x_return_status := fnd_api.g_ret_sts_error;
6761: AND PARTY_SITE_USE_ID <> NVL(p_party_site_use_rec.party_site_use_id, fnd_api.g_miss_num);
6762:
6763: fnd_message.set_name('AR', 'HZ_API_UNIQUE_SITE_USE_TYPE');
6764: fnd_msg_pub.add;
6765: x_return_status := fnd_api.g_ret_sts_error;
6766:
6767: EXCEPTION
6768: WHEN NO_DATA_FOUND THEN
6769: NULL;
6799: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
6800: -- status is lookup code in lookup type REGISTRY_STATUS
6801: IF p_party_site_use_rec.status IS NOT NULL
6802: AND
6803: p_party_site_use_rec.status <> fnd_api.g_miss_char
6804: AND
6805: (p_create_update_flag = 'C'
6806: OR
6807: (p_create_update_flag = 'U'
6805: (p_create_update_flag = 'C'
6806: OR
6807: (p_create_update_flag = 'U'
6808: AND
6809: p_party_site_use_rec.status <> NVL(l_status, fnd_api.g_miss_char)
6810: )
6811: )
6812: THEN
6813: validate_lookup (
6865: FND_MESSAGE.SET_TOKEN('ENTITY','Party Site Use');
6866: -- FND_MESSAGE.SET_TOKEN('COLUMN1','PRIMARY_PER_TYPE');
6867: -- FND_MESSAGE.SET_TOKEN('COLUMN2','STATUS');
6868: FND_MSG_PUB.ADD;
6869: x_return_status := FND_API.G_RET_STS_ERROR;
6870: END IF;
6871:
6872:
6873: -- Debug info.
6905: * p_org_contact_rec Org contact record.
6906: * p_rowid Rowid of the record (used only in update mode).
6907: * IN/OUT:
6908: * x_return_status Return status after the call. The status can
6909: * be FND_API.G_RET_STS_SUCCESS (success),
6910: * fnd_api.g_ret_sts_error (error),
6911: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6912: *
6913: * NOTES
6906: * p_rowid Rowid of the record (used only in update mode).
6907: * IN/OUT:
6908: * x_return_status Return status after the call. The status can
6909: * be FND_API.G_RET_STS_SUCCESS (success),
6910: * fnd_api.g_ret_sts_error (error),
6911: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6912: *
6913: * NOTES
6914: *
6907: * IN/OUT:
6908: * x_return_status Return status after the call. The status can
6909: * be FND_API.G_RET_STS_SUCCESS (success),
6910: * fnd_api.g_ret_sts_error (error),
6911: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6912: *
6913: * NOTES
6914: *
6915: * MODIFICATION HISTORY
7001:
7002: -- title is lookup code in lookup type CONTACT_TITLE
7003: IF p_org_contact_rec.title IS NOT NULL
7004: AND
7005: p_org_contact_rec.title <> fnd_api.g_miss_char
7006: AND
7007: (p_create_update_flag = 'C'
7008: OR
7009: (p_create_update_flag = 'U'
7007: (p_create_update_flag = 'C'
7008: OR
7009: (p_create_update_flag = 'U'
7010: AND
7011: p_org_contact_rec.title <> NVL(l_title, fnd_api.g_miss_char)
7012: )
7013: )
7014: THEN
7015: validate_lookup (
7040:
7041: -- job_title_code is lookup code in lookup type RESPONSIBILITY
7042: IF p_org_contact_rec.job_title_code IS NOT NULL
7043: AND
7044: p_org_contact_rec.job_title_code <> fnd_api.g_miss_char
7045: AND
7046: (p_create_update_flag = 'C'
7047: OR
7048: (p_create_update_flag = 'U'
7046: (p_create_update_flag = 'C'
7047: OR
7048: (p_create_update_flag = 'U'
7049: AND
7050: p_org_contact_rec.job_title_code <> NVL(l_job_title_code, fnd_api.g_miss_char)
7051: )
7052: )
7053: THEN
7054: validate_lookup (
7129:
7130: -- department_code is lookup code in lookup type DEPARTMENT_TYPE
7131: IF p_org_contact_rec.department_code IS NOT NULL
7132: AND
7133: p_org_contact_rec.department_code <> fnd_api.g_miss_char
7134: AND
7135: (p_create_update_flag = 'C'
7136: OR
7137: (p_create_update_flag = 'U'
7135: (p_create_update_flag = 'C'
7136: OR
7137: (p_create_update_flag = 'U'
7138: AND
7139: p_org_contact_rec.department_code <> NVL(l_department_code, fnd_api.g_miss_char)
7140: )
7141: )
7142: THEN
7143: validate_lookup (
7169: -- if party_site_id is passed, then it must be validated as
7170: -- foreign key to hz_party_sites.party_site_id
7171: IF p_org_contact_rec.party_site_id IS NOT NULL
7172: AND
7173: p_org_contact_rec.party_site_id <> fnd_api.g_miss_num
7174: THEN
7175: BEGIN
7176: SELECT 'Y'
7177: INTO l_dummy
7183: fnd_message.set_token('FK', 'party_site_id');
7184: fnd_message.set_token('COLUMN', 'party_site_id');
7185: fnd_message.set_token('TABLE', 'hz_party_sites');
7186: fnd_msg_pub.add;
7187: x_return_status := fnd_api.g_ret_sts_error;
7188: END;
7189:
7190: /*IF g_debug THEN
7191: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
7206: -- if party_site_id is passed then its party_id should be
7207: -- same as the object_id of the relationship record for the org_contact
7208: IF p_org_contact_rec.party_site_id IS NOT NULL
7209: AND
7210: p_org_contact_rec.party_site_id <> fnd_api.g_miss_num
7211: THEN
7212: BEGIN
7213: SELECT 'Y'
7214: INTO l_dummy
7218: EXCEPTION
7219: WHEN NO_DATA_FOUND THEN
7220: fnd_message.set_name('AR', 'HZ_API_PARTY_OBJECT_MISMATCH');
7221: fnd_msg_pub.add;
7222: x_return_status := fnd_api.g_ret_sts_error;
7223: END;
7224:
7225: /*IF g_debug THEN
7226: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
7285: * p_org_contact_role_rec Org contact role record.
7286: * p_rowid Rowid of the record (used only in update mode).
7287: * IN/OUT:
7288: * x_return_status Return status after the call. The status can
7289: * be FND_API.G_RET_STS_SUCCESS (success),
7290: * fnd_api.g_ret_sts_error (error),
7291: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7292: *
7293: * NOTES
7286: * p_rowid Rowid of the record (used only in update mode).
7287: * IN/OUT:
7288: * x_return_status Return status after the call. The status can
7289: * be FND_API.G_RET_STS_SUCCESS (success),
7290: * fnd_api.g_ret_sts_error (error),
7291: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7292: *
7293: * NOTES
7294: *
7287: * IN/OUT:
7288: * x_return_status Return status after the call. The status can
7289: * be FND_API.G_RET_STS_SUCCESS (success),
7290: * fnd_api.g_ret_sts_error (error),
7291: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7292: *
7293: * NOTES
7294: *
7295: * MODIFICATION HISTORY
7398: -- non-updateable.
7399: IF p_create_update_flag = 'C'
7400: AND
7401: p_org_contact_role_rec.org_contact_id IS NOT NULL
7402: AND p_org_contact_role_rec.org_contact_id <> fnd_api.g_miss_num
7403: THEN
7404: BEGIN
7405: SELECT 'Y'
7406: INTO l_dummy
7412: fnd_message.set_token('FK', 'org_contact_id');
7413: fnd_message.set_token('COLUMN', 'org_contact_id');
7414: fnd_message.set_token('TABLE', 'hz_org_contacts');
7415: fnd_msg_pub.add;
7416: x_return_status := fnd_api.g_ret_sts_error;
7417: END;
7418:
7419: /*IF g_debug THEN
7420: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
7437: ---------------------------------
7438: /****Logical APIs - validation not required****/
7439: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
7440: IF (p_org_contact_role_rec.orig_system is not null
7441: and p_org_contact_role_rec.orig_system <>fnd_api.g_miss_char)
7442: and (p_org_contact_role_rec.orig_system_reference is not null
7443: and p_org_contact_role_rec.orig_system_reference <>fnd_api.g_miss_char)
7444: and p_create_update_flag = 'U'
7445:
7439: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
7440: IF (p_org_contact_role_rec.orig_system is not null
7441: and p_org_contact_role_rec.orig_system <>fnd_api.g_miss_char)
7442: and (p_org_contact_role_rec.orig_system_reference is not null
7443: and p_org_contact_role_rec.orig_system_reference <>fnd_api.g_miss_char)
7444: and p_create_update_flag = 'U'
7445:
7446: then
7447: hz_orig_system_ref_pub.get_owner_table_id
7450: p_owner_table_name => 'HZ_ORG_CONTACT_ROLES',
7451: x_owner_table_id => l_mosr_owner_table_id,
7452: x_return_status => l_temp_return_status);
7453:
7454: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
7455: l_mosr_owner_table_id= nvl(p_org_contact_role_rec.org_contact_role_id,l_mosr_owner_table_id))
7456: THEN
7457: l_validate_osr := 'N';
7458: -- if we can get owner_table_id based on osr and os in mosr table,
7463: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
7464: -- x_return_status. Set x_return_status to error, ONLY if there is error.
7465: -- In case of success, leave it to carry over previous value as before this call.
7466: -- Fix for Bug 5498116 (29-AUG-2006)
7467: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
7468: x_return_status := l_temp_return_status;
7469: END IF;
7470:
7471: end if;
7596: IF l_role_id <> p_org_contact_role_rec.org_contact_role_id OR
7597: p_create_update_flag = 'C' THEN
7598: fnd_message.set_name('AR', 'HZ_API_UNIQUE_PRIMARY_ROLE');
7599: fnd_msg_pub.add;
7600: x_return_status := fnd_api.g_ret_sts_error;
7601: END IF;
7602: EXCEPTION
7603: WHEN NO_DATA_FOUND THEN
7604: NULL;
7630: BEGIN
7631: SELECT ORG_CONTACT_ROLE_ID
7632: INTO l_role_id
7633: FROM HZ_ORG_CONTACT_ROLES
7634: WHERE ORG_CONTACT_ID = NVL(p_org_contact_role_rec.org_contact_id, fnd_api.g_miss_num)
7635: AND ROLE_TYPE = p_org_contact_role_rec.role_type
7636: AND STATUS = 'A' -- Added: Bug#6411541
7637: AND ROWNUM = 1;
7638:
7635: AND ROLE_TYPE = p_org_contact_role_rec.role_type
7636: AND STATUS = 'A' -- Added: Bug#6411541
7637: AND ROWNUM = 1;
7638:
7639: IF l_role_id <> nvl(p_org_contact_role_rec.org_contact_role_id, fnd_api.g_miss_num) THEN
7640: fnd_message.set_name('AR', 'HZ_API_UNIQUE_ROLE_TYPE');
7641: fnd_msg_pub.add;
7642: x_return_status := fnd_api.g_ret_sts_error;
7643: END IF;
7638:
7639: IF l_role_id <> nvl(p_org_contact_role_rec.org_contact_role_id, fnd_api.g_miss_num) THEN
7640: fnd_message.set_name('AR', 'HZ_API_UNIQUE_ROLE_TYPE');
7641: fnd_msg_pub.add;
7642: x_return_status := fnd_api.g_ret_sts_error;
7643: END IF;
7644: EXCEPTION
7645: WHEN NO_DATA_FOUND THEN
7646: NULL;
7710: and ocr.org_contact_role_id <>p_org_contact_role_rec.org_contact_role_id; --db primary role id is not same as the pass in role id
7711:
7712: fnd_message.set_name('AR', 'HZ_API_UNIQUE_PRIMARY_ORG_CONT');
7713: fnd_msg_pub.add;
7714: x_return_status := fnd_api.g_ret_sts_error;
7715: EXCEPTION
7716: WHEN NO_DATA_FOUND THEN
7717: NULL;
7718: END;
7739: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
7740: -- status is lookup code in lookup type REGISTRY_STATUS
7741: IF p_org_contact_role_rec.status IS NOT NULL
7742: AND
7743: p_org_contact_role_rec.status <> fnd_api.g_miss_char
7744: AND
7745: (p_create_update_flag = 'C'
7746: OR
7747: (p_create_update_flag = 'U'
7745: (p_create_update_flag = 'C'
7746: OR
7747: (p_create_update_flag = 'U'
7748: AND
7749: p_org_contact_role_rec.status <> NVL(l_status, fnd_api.g_miss_char)
7750: )
7751: )
7752: THEN
7753: validate_lookup (
7827: * p_person_language_rec Person language record.
7828: * p_rowid Rowid of the record (used only in update mode).
7829: * IN/OUT:
7830: * x_return_status Return status after the call. The status can
7831: * be FND_API.G_RET_STS_SUCCESS (success),
7832: * fnd_api.g_ret_sts_error (error),
7833: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7834: *
7835: * NOTES
7828: * p_rowid Rowid of the record (used only in update mode).
7829: * IN/OUT:
7830: * x_return_status Return status after the call. The status can
7831: * be FND_API.G_RET_STS_SUCCESS (success),
7832: * fnd_api.g_ret_sts_error (error),
7833: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7834: *
7835: * NOTES
7836: *
7829: * IN/OUT:
7830: * x_return_status Return status after the call. The status can
7831: * be FND_API.G_RET_STS_SUCCESS (success),
7832: * fnd_api.g_ret_sts_error (error),
7833: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7834: *
7835: * NOTES
7836: *
7837: * MODIFICATION HISTORY
7955: IF p_create_update_flag = 'C'
7956: AND
7957: p_person_language_rec.party_id IS NOT NULL
7958: AND
7959: p_person_language_rec.party_id <> fnd_api.g_miss_num
7960: THEN
7961: BEGIN
7962: SELECT 'Y'
7963: INTO l_dummy
7970: fnd_message.set_token('FK', 'party_id');
7971: fnd_message.set_token('COLUMN', 'party_id');
7972: fnd_message.set_token('TABLE', 'hz_parties');
7973: fnd_msg_pub.add;
7974: x_return_status := fnd_api.g_ret_sts_error;
7975: END;
7976:
7977: /*IF g_debug THEN
7978: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
8039:
8040: -- language_name has foreign key fnd_languages.language_code
8041: IF p_person_language_rec.language_name IS NOT NULL
8042: AND
8043: p_person_language_rec.language_name <> fnd_api.g_miss_char
8044: THEN
8045: BEGIN
8046: SELECT 'Y'
8047: INTO l_dummy
8053: fnd_message.set_token('FK', 'language_name');
8054: fnd_message.set_token('COLUMN', 'language_code');
8055: fnd_message.set_token('TABLE', 'fnd_languages');
8056: fnd_msg_pub.add;
8057: x_return_status := fnd_api.g_ret_sts_error;
8058: END;
8059:
8060: /*IF g_debug THEN
8061: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
8078: INTO l_dummy
8079: FROM HZ_PERSON_LANGUAGE
8080: WHERE PARTY_ID = l_party_id
8081: AND LANGUAGE_NAME = p_person_language_rec.language_name
8082: AND LANGUAGE_USE_REFERENCE_ID <> NVL(p_person_language_rec.language_use_reference_id, fnd_api.g_miss_num);
8083:
8084: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
8085: fnd_message.set_token('COLUMN', 'language_name');
8086: fnd_msg_pub.add;
8083:
8084: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
8085: fnd_message.set_token('COLUMN', 'language_name');
8086: fnd_msg_pub.add;
8087: x_return_status := fnd_api.g_ret_sts_error;
8088:
8089: EXCEPTION
8090: WHEN NO_DATA_FOUND THEN
8091: NULL;
8175: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8176: -- status is lookup code in lookup type REGISTRY_STATUS
8177: IF p_person_language_rec.status IS NOT NULL
8178: AND
8179: p_person_language_rec.status <> fnd_api.g_miss_char
8180: AND
8181: (p_create_update_flag = 'C'
8182: OR
8183: (p_create_update_flag = 'U'
8181: (p_create_update_flag = 'C'
8182: OR
8183: (p_create_update_flag = 'U'
8184: AND
8185: p_person_language_rec.status <> NVL(l_status, fnd_api.g_miss_char)
8186: )
8187: )
8188: THEN
8189: validate_lookup (
8225: -- the status for a primary language was updated from 'A' to 'I'.
8226:
8227: /*
8228: IF p_person_language_rec.primary_language_indicator is NOT NULL
8229: AND p_person_language_rec.primary_language_indicator <> fnd_api.g_miss_char
8230: AND (p_create_update_flag = 'C'
8231: OR (p_create_update_flag = 'U'
8232: AND p_person_language_rec.primary_language_indicator <>
8233: NVL(l_primary_language_indicator, fnd_api.g_miss_char)))
8229: AND p_person_language_rec.primary_language_indicator <> fnd_api.g_miss_char
8230: AND (p_create_update_flag = 'C'
8231: OR (p_create_update_flag = 'U'
8232: AND p_person_language_rec.primary_language_indicator <>
8233: NVL(l_primary_language_indicator, fnd_api.g_miss_char)))
8234: THEN
8235: SELECT DECODE(p_person_language_rec.primary_language_indicator,
8236: 'Y', DECODE(p_person_language_rec.status,
8237: 'I', 'N',
8245: fnd_message.set_token('ENTITY', 'language');
8246: -- fnd_message.set_token('COLUMN1', 'primary_language_indicator');
8247: -- fnd_message.set_token('COLUMN2', 'status');
8248: fnd_msg_pub.add;
8249: x_return_status := FND_API.G_RET_STS_ERROR;
8250: END IF;
8251:
8252: */
8253:
8261: fnd_message.set_token('ENTITY', 'Language');
8262: -- fnd_message.set_token('COLUMN1', 'primary_language_indicator');
8263: -- fnd_message.set_token('COLUMN2', 'status');
8264: fnd_msg_pub.add;
8265: x_return_status := FND_API.G_RET_STS_ERROR;
8266:
8267: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
8268: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
8269: p_message=>'If the primary_language_indicator is set to Yes, the status column should not be Inactive.' ||
8280: fnd_message.set_token('ENTITY', 'Language');
8281: --fnd_message.set_token('COLUMN1', 'primary_language_indicator');
8282: -- fnd_message.set_token('COLUMN2', 'status');
8283: fnd_msg_pub.add;
8284: x_return_status := FND_API.G_RET_STS_ERROR;
8285:
8286: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
8287: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
8288: p_message=>'If the primary_language_indicator is set to Yes, the status column should not be Inactive.' ||
8350: * p_citizenship_rec Citizenship record.
8351: * p_rowid Rowid of the record (used only in update mode).
8352: * IN/OUT:
8353: * x_return_status Return status after the call. The status can
8354: * be FND_API.G_RET_STS_SUCCESS (success),
8355: * fnd_api.g_ret_sts_error (error),
8356: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8357: *
8358: * NOTES
8351: * p_rowid Rowid of the record (used only in update mode).
8352: * IN/OUT:
8353: * x_return_status Return status after the call. The status can
8354: * be FND_API.G_RET_STS_SUCCESS (success),
8355: * fnd_api.g_ret_sts_error (error),
8356: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8357: *
8358: * NOTES
8359: *
8352: * IN/OUT:
8353: * x_return_status Return status after the call. The status can
8354: * be FND_API.G_RET_STS_SUCCESS (success),
8355: * fnd_api.g_ret_sts_error (error),
8356: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8357: *
8358: * NOTES
8359: *
8360: * MODIFICATION HISTORY
8430: -- from sequence by table handler.
8431:
8432: IF p_create_update_flag = 'C' THEN
8433: IF p_citizenship_rec.citizenship_id IS NOT NULL AND
8434: p_citizenship_rec.citizenship_id <> fnd_api.g_miss_num
8435: THEN
8436: OPEN citizen_cur (p_citizenship_rec.citizenship_id);
8437: FETCH citizen_cur INTO l_dummy;
8438:
8440: IF NVL(citizen_cur%FOUND, FALSE) THEN
8441: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
8442: fnd_message.set_token('COLUMN', 'citizenship_id');
8443: fnd_msg_pub.add;
8444: x_return_status := fnd_api.g_ret_sts_error;
8445: END IF;
8446: CLOSE citizen_cur;
8447:
8448: /*IF g_debug THEN
8554: IF p_create_update_flag = 'C'
8555: AND
8556: p_citizenship_rec.party_id IS NOT NULL
8557: AND
8558: p_citizenship_rec.party_id <> fnd_api.g_miss_num
8559: THEN
8560: BEGIN
8561: SELECT 'Y'
8562: INTO l_dummy
8570: -- fnd_message.set_token('FK', 'party_id');
8571: fnd_message.set_token('TABLE_NAME', 'HZ_CITIZENSHIP');
8572: fnd_message.set_token('PARTY_ID_COL', 'party_id');
8573: fnd_msg_pub.add;
8574: x_return_status := fnd_api.g_ret_sts_error;
8575: END;
8576:
8577: /*IF g_debug THEN
8578: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
8677: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8678: -- status is lookup code in lookup type REGISTRY_STATUS
8679: IF p_citizenship_rec.status IS NOT NULL
8680: AND
8681: p_citizenship_rec.status <> fnd_api.g_miss_char
8682: AND
8683: (p_create_update_flag = 'C'
8684: OR
8685: (p_create_update_flag = 'U'
8683: (p_create_update_flag = 'C'
8684: OR
8685: (p_create_update_flag = 'U'
8686: AND
8687: p_citizenship_rec.status <> NVL(l_status, fnd_api.g_miss_char)
8688: )
8689: )
8690: THEN
8691: validate_lookup (
8767: * p_education_rec Education record.
8768: * p_rowid Rowid of the record (used only in update mode).
8769: * IN/OUT:
8770: * x_return_status Return status after the call. The status can
8771: * be FND_API.G_RET_STS_SUCCESS (success),
8772: * fnd_api.g_ret_sts_error (error),
8773: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8774: *
8775: * NOTES
8768: * p_rowid Rowid of the record (used only in update mode).
8769: * IN/OUT:
8770: * x_return_status Return status after the call. The status can
8771: * be FND_API.G_RET_STS_SUCCESS (success),
8772: * fnd_api.g_ret_sts_error (error),
8773: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8774: *
8775: * NOTES
8776: *
8769: * IN/OUT:
8770: * x_return_status Return status after the call. The status can
8771: * be FND_API.G_RET_STS_SUCCESS (success),
8772: * fnd_api.g_ret_sts_error (error),
8773: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8774: *
8775: * NOTES
8776: *
8777: * MODIFICATION HISTORY
8874:
8875:
8876: IF p_create_update_flag = 'C' THEN
8877: IF p_education_rec.education_id IS NOT NULL AND
8878: p_education_rec.education_id <> fnd_api.g_miss_num
8879: THEN
8880: OPEN education_cur (p_education_rec.education_id);
8881: FETCH education_cur INTO l_dummy;
8882:
8884: IF NVL(education_cur%FOUND, FALSE) THEN
8885: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
8886: fnd_message.set_token('COLUMN', 'education_id');
8887: fnd_msg_pub.add;
8888: x_return_status := fnd_api.g_ret_sts_error;
8889: END IF;
8890: CLOSE education_cur;
8891:
8892: /*IF g_debug THEN
8997: IF p_create_update_flag = 'C'
8998: AND
8999: p_education_rec.party_id IS NOT NULL
9000: AND
9001: p_education_rec.party_id <> fnd_api.g_miss_num
9002: THEN
9003: BEGIN
9004: SELECT 'Y'
9005: INTO l_dummy
9012: fnd_message.set_name('AR', 'HZ_API_PARTY_NOT_PERSON');
9013: fnd_message.set_token('TABLE_NAME', 'HZ_EDUCATION');
9014: fnd_message.set_token('PARTY_ID_COL', 'party_id');
9015: fnd_msg_pub.add;
9016: x_return_status := fnd_api.g_ret_sts_error;
9017: END;
9018:
9019: /*IF g_debug THEN
9020: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9064: temp_last_date_attended := p_education_rec.last_date_attended;
9065: END IF;
9066:
9067: IF (temp_start_date_attended IS NOT NULL AND
9068: temp_start_date_attended <> FND_API.G_MISS_DATE AND
9069: temp_last_date_attended IS NOT NULL AND
9070: temp_last_date_attended <> FND_API.G_MISS_DATE) THEN
9071:
9072: validate_start_end_date (
9066:
9067: IF (temp_start_date_attended IS NOT NULL AND
9068: temp_start_date_attended <> FND_API.G_MISS_DATE AND
9069: temp_last_date_attended IS NOT NULL AND
9070: temp_last_date_attended <> FND_API.G_MISS_DATE) THEN
9071:
9072: validate_start_end_date (
9073:
9074: p_create_update_flag => p_create_update_flag,
9139: temp_school_attended_name := p_education_rec.school_attended_name;
9140: END IF;
9141:
9142: IF temp_school_party_id is NOT NULL and
9143: temp_school_party_id <> fnd_api.g_miss_num and
9144: temp_school_attended_name is NOT NULL and
9145: temp_school_attended_name <> fnd_api.g_miss_char then
9146:
9147: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9141:
9142: IF temp_school_party_id is NOT NULL and
9143: temp_school_party_id <> fnd_api.g_miss_num and
9144: temp_school_attended_name is NOT NULL and
9145: temp_school_attended_name <> fnd_api.g_miss_char then
9146:
9147: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9148: fnd_message.set_token('COLUMN1', 'school_party_id');
9149: fnd_message.set_token('COLUMN2', 'school_attended_name');
9147: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9148: fnd_message.set_token('COLUMN1', 'school_party_id');
9149: fnd_message.set_token('COLUMN2', 'school_attended_name');
9150: fnd_msg_pub.add;
9151: x_return_status := fnd_api.g_ret_sts_error;
9152: END IF;
9153:
9154: /*IF g_debug THEN
9155: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9172:
9173: -- school_party_id must exist in HZ_PARTIES
9174: IF p_education_rec.school_party_id IS NOT NULL
9175: AND
9176: p_education_rec.school_party_id <> fnd_api.g_miss_num
9177: THEN
9178: BEGIN
9179: SELECT 'Y'
9180: INTO l_dummy
9187: fnd_message.set_name('AR', 'HZ_API_PARTY_NOT_ORG');
9188: fnd_message.set_token('TABLE_NAME', 'HZ_EDUCATION');
9189: fnd_message.set_token('PARTY_ID_COL', 'school_party_id');
9190: fnd_msg_pub.add;
9191: x_return_status := fnd_api.g_ret_sts_error;
9192: END;
9193:
9194: /*IF g_debug THEN
9195: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9217: -- The validation will be called only if the value changes.
9218:
9219: IF p_education_rec.type_of_school IS NOT NULL
9220: AND
9221: p_education_rec.type_of_school <> fnd_api.g_miss_char
9222: AND
9223: (
9224: p_create_update_flag = 'C'
9225: OR
9260: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
9261: -- status is lookup code in lookup type REGISTRY_STATUS
9262: IF p_education_rec.status IS NOT NULL
9263: AND
9264: p_education_rec.status <> fnd_api.g_miss_char
9265: AND
9266: (p_create_update_flag = 'C'
9267: OR
9268: (p_create_update_flag = 'U'
9266: (p_create_update_flag = 'C'
9267: OR
9268: (p_create_update_flag = 'U'
9269: AND
9270: p_education_rec.status <> NVL(l_status, fnd_api.g_miss_char)
9271: )
9272: )
9273: THEN
9274: validate_lookup (
9351: * p_employment_history_rec Employment_history record.
9352: * p_rowid Rowid of the record (used only in update mode).
9353: * IN/OUT:
9354: * x_return_status Return status after the call. The status can
9355: * be FND_API.G_RET_STS_SUCCESS (success),
9356: * fnd_api.g_ret_sts_error (error),
9357: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9358: *
9359: * NOTES
9352: * p_rowid Rowid of the record (used only in update mode).
9353: * IN/OUT:
9354: * x_return_status Return status after the call. The status can
9355: * be FND_API.G_RET_STS_SUCCESS (success),
9356: * fnd_api.g_ret_sts_error (error),
9357: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9358: *
9359: * NOTES
9360: *
9353: * IN/OUT:
9354: * x_return_status Return status after the call. The status can
9355: * be FND_API.G_RET_STS_SUCCESS (success),
9356: * fnd_api.g_ret_sts_error (error),
9357: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9358: *
9359: * NOTES
9360: *
9361: * MODIFICATION HISTORY
9461:
9462:
9463: IF p_create_update_flag = 'C' THEN
9464: IF p_employment_history_rec.employment_history_id IS NOT NULL AND
9465: p_employment_history_rec.employment_history_id <> fnd_api.g_miss_num
9466: THEN
9467: OPEN employment_history_cur (p_employment_history_rec.employment_history_id);
9468: FETCH employment_history_cur INTO l_dummy;
9469:
9471: IF NVL(employment_history_cur%FOUND, FALSE) THEN
9472: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
9473: fnd_message.set_token('COLUMN', 'employment_history_id');
9474: fnd_msg_pub.add;
9475: x_return_status := fnd_api.g_ret_sts_error;
9476: END IF;
9477: CLOSE employment_history_cur;
9478:
9479: /*IF g_debug THEN
9585: IF p_create_update_flag = 'C'
9586: AND
9587: p_employment_history_rec.party_id IS NOT NULL
9588: AND
9589: p_employment_history_rec.party_id <> fnd_api.g_miss_num
9590: THEN
9591: BEGIN
9592: SELECT 'Y'
9593: INTO l_dummy
9600: fnd_message.set_name('AR', 'HZ_API_PARTY_NOT_PERSON');
9601: fnd_message.set_token('TABLE_NAME', 'HZ_EMPLOYMENT_HISTORY');
9602: fnd_message.set_token('PARTY_ID_COL', 'party_id');
9603: fnd_msg_pub.add;
9604: x_return_status := fnd_api.g_ret_sts_error;
9605: END;
9606:
9607: /*IF g_debug THEN
9608: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9651: temp_end_date := p_employment_history_rec.end_date;
9652: END IF;
9653:
9654: IF (temp_begin_date IS NOT NULL AND
9655: temp_begin_date <> FND_API.G_MISS_DATE AND
9656: temp_end_date IS NOT NULL AND
9657: temp_end_date <> FND_API.G_MISS_DATE) THEN
9658:
9659: validate_start_end_date (
9653:
9654: IF (temp_begin_date IS NOT NULL AND
9655: temp_begin_date <> FND_API.G_MISS_DATE AND
9656: temp_end_date IS NOT NULL AND
9657: temp_end_date <> FND_API.G_MISS_DATE) THEN
9658:
9659: validate_start_end_date (
9660:
9661: p_create_update_flag => p_create_update_flag,
9781: END IF;
9782:
9783:
9784: IF (temp_employed_as_title_code IS NOT NULL and
9785: temp_employed_as_title_code <> FND_API.G_MISS_CHAR AND
9786: temp_employed_as_title IS NOT NULL AND
9787: temp_employed_as_title <> FND_API.G_MISS_CHAR) THEN
9788:
9789: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9783:
9784: IF (temp_employed_as_title_code IS NOT NULL and
9785: temp_employed_as_title_code <> FND_API.G_MISS_CHAR AND
9786: temp_employed_as_title IS NOT NULL AND
9787: temp_employed_as_title <> FND_API.G_MISS_CHAR) THEN
9788:
9789: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9790: fnd_message.set_token('COLUMN1', 'employed_as_title');
9791: fnd_message.set_token('COLUMN2', 'employed_as_title_code');
9789: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9790: fnd_message.set_token('COLUMN1', 'employed_as_title');
9791: fnd_message.set_token('COLUMN2', 'employed_as_title_code');
9792: fnd_msg_pub.add;
9793: x_return_status := fnd_api.g_ret_sts_error;
9794:
9795: /*IF g_debug THEN
9796: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9797: 'check that employed_as_title is empty when employed_as_title_code isn t. '||
9830:
9831: -- employed_by_party_id must exist in HZ_PARTIES
9832: IF p_employment_history_rec.employed_by_party_id IS NOT NULL
9833: AND
9834: p_employment_history_rec.employed_by_party_id <> fnd_api.g_miss_num
9835: THEN
9836: BEGIN
9837: SELECT 'Y'
9838: INTO l_dummy
9845: fnd_message.set_token('FK', 'employed_by_party_id');
9846: fnd_message.set_token('COLUMN', 'party_id');
9847: fnd_message.set_token('TABLE', 'hz_parties');
9848: fnd_msg_pub.add;
9849: x_return_status := fnd_api.g_ret_sts_error;
9850: END;
9851:
9852: /*IF g_debug THEN
9853: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9894: END IF;
9895:
9896:
9897: IF (temp_employed_by_party_id IS NOT NULL and
9898: temp_employed_by_party_id <> FND_API.G_MISS_NUM AND
9899: temp_employed_by_name_company IS NOT NULL AND
9900: temp_employed_by_name_company <> FND_API.G_MISS_CHAR) THEN
9901:
9902: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9896:
9897: IF (temp_employed_by_party_id IS NOT NULL and
9898: temp_employed_by_party_id <> FND_API.G_MISS_NUM AND
9899: temp_employed_by_name_company IS NOT NULL AND
9900: temp_employed_by_name_company <> FND_API.G_MISS_CHAR) THEN
9901:
9902: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9903: fnd_message.set_token('COLUMN1', 'employed_by_name_company');
9904: fnd_message.set_token('COLUMN2', 'employed_by_party_id');
9902: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9903: fnd_message.set_token('COLUMN1', 'employed_by_name_company');
9904: fnd_message.set_token('COLUMN2', 'employed_by_party_id');
9905: fnd_msg_pub.add;
9906: x_return_status := fnd_api.g_ret_sts_error;
9907:
9908: /*IF g_debug THEN
9909: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9910: 'check that employed_by_name_company is empty when employed_by_party_id isn t. ' ||
9944: -- If weekly_work_hours is passed, must be greater than or equal to zero
9945: -- and less than or equal to 168.
9946:
9947: IF p_employment_history_rec.weekly_work_hours IS NOT NULL AND
9948: p_employment_history_rec.weekly_work_hours <> fnd_api.g_miss_num
9949: THEN
9950:
9951: IF p_employment_history_rec.weekly_work_hours < 0 OR
9952: p_employment_history_rec.weekly_work_hours > 168 THEN
9956: -- Bug 4226199 : This should be changed for bug 3848056
9957: fnd_message.set_token('VALUE1', '0');
9958: fnd_message.set_token('VALUE2', '168');
9959: fnd_msg_pub.add;
9960: x_return_status := fnd_api.g_ret_sts_error;
9961: END IF;
9962:
9963: /*IF g_debug THEN
9964: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
10057: temp_faculty_position_flag := p_employment_history_rec.faculty_position_flag;
10058: END IF;
10059:
10060: IF p_employment_history_rec.tenure_code IS NOT NULL AND
10061: p_employment_history_rec.tenure_code <> fnd_api.g_miss_char then
10062:
10063: IF temp_faculty_position_flag = 'Y' THEN
10064:
10065: validate_lookup (
10088: fnd_message.set_token('COLUMN2', 'tenure_code');
10089: fnd_message.set_token('VALUE1', 'N');
10090: fnd_message.set_token('VALUE2', 'null');
10091: fnd_msg_pub.add;
10092: x_return_status := fnd_api.g_ret_sts_error;
10093:
10094: END IF;
10095:
10096: /*IF g_debug THEN
10114: -- If fraction_of_tenure is passed, faculty_position_flag
10115: -- should be Y.
10116:
10117: IF p_employment_history_rec.fraction_of_tenure IS NOT NULL AND
10118: p_employment_history_rec.fraction_of_tenure <> fnd_api.g_miss_num then
10119: IF temp_faculty_position_flag = 'Y' then
10120: IF p_employment_history_rec.fraction_of_tenure < 0 OR
10121: p_employment_history_rec.fraction_of_tenure > 100 THEN
10122:
10124: fnd_message.set_token('COLUMN', 'fraction_of_tenure');
10125: fnd_message.set_token('VALUE1', '0');
10126: fnd_message.set_token('VALUE2', '100');
10127: fnd_msg_pub.add;
10128: x_return_status := fnd_api.g_ret_sts_error;
10129: END IF;
10130:
10131: /*IF g_debug THEN
10132: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
10149: fnd_message.set_token('VALUE2', 'null');
10150:
10151: fnd_message.set_token('COLUMN', 'fraction_of_tenure');
10152: fnd_msg_pub.add;
10153: x_return_status := fnd_api.g_ret_sts_error;
10154: END IF;
10155:
10156: END IF;
10157:
10176: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10177: -- status is lookup code in lookup type REGISTRY_STATUS
10178: IF p_employment_history_rec.status IS NOT NULL
10179: AND
10180: p_employment_history_rec.status <> fnd_api.g_miss_char
10181: AND
10182: (p_create_update_flag = 'C'
10183: OR
10184: (p_create_update_flag = 'U'
10182: (p_create_update_flag = 'C'
10183: OR
10184: (p_create_update_flag = 'U'
10185: AND
10186: p_employment_history_rec.status <> NVL(l_status, fnd_api.g_miss_char)
10187: )
10188: )
10189: THEN
10190: validate_lookup (
10265: * p_work_class_rec Work_class record.
10266: * p_rowid Rowid of the record (used only in update mode).
10267: * IN/OUT:
10268: * x_return_status Return status after the call. The status can
10269: * be FND_API.G_RET_STS_SUCCESS (success),
10270: * fnd_api.g_ret_sts_error (error),
10271: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10272: *
10273: * NOTES
10266: * p_rowid Rowid of the record (used only in update mode).
10267: * IN/OUT:
10268: * x_return_status Return status after the call. The status can
10269: * be FND_API.G_RET_STS_SUCCESS (success),
10270: * fnd_api.g_ret_sts_error (error),
10271: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10272: *
10273: * NOTES
10274: *
10267: * IN/OUT:
10268: * x_return_status Return status after the call. The status can
10269: * be FND_API.G_RET_STS_SUCCESS (success),
10270: * fnd_api.g_ret_sts_error (error),
10271: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10272: *
10273: * NOTES
10274: *
10275: * MODIFICATION HISTORY
10344:
10345:
10346: IF p_create_update_flag = 'C' THEN
10347: IF p_work_class_rec.work_class_id IS NOT NULL AND
10348: p_work_class_rec.work_class_id <> fnd_api.g_miss_num
10349: THEN
10350: OPEN work_class_cur (p_work_class_rec.work_class_id);
10351: FETCH work_class_cur INTO l_dummy;
10352:
10354: IF NVL(work_class_cur%FOUND, FALSE) THEN
10355: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
10356: fnd_message.set_token('COLUMN', 'work_class_id');
10357: fnd_msg_pub.add;
10358: x_return_status := fnd_api.g_ret_sts_error;
10359: END IF;
10360: CLOSE work_class_cur;
10361:
10362: /*IF g_debug THEN
10466: IF p_create_update_flag = 'C'
10467: AND
10468: p_work_class_rec.employment_history_id IS NOT NULL
10469: AND
10470: p_work_class_rec.employment_history_id <> fnd_api.g_miss_num
10471: THEN
10472: BEGIN
10473: SELECT 'Y'
10474: INTO l_dummy
10481: fnd_message.set_token('FK', 'employment_history_id');
10482: fnd_message.set_token('COLUMN', 'employment_history_id');
10483: fnd_message.set_token('TABLE', 'hz_employment_history');
10484: fnd_msg_pub.add;
10485: x_return_status := fnd_api.g_ret_sts_error;
10486: END;
10487:
10488: /*IF g_debug THEN
10489: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
10534: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10535: -- status is lookup code in lookup type REGISTRY_STATUS
10536: IF p_work_class_rec.status IS NOT NULL
10537: AND
10538: p_work_class_rec.status <> fnd_api.g_miss_char
10539: AND
10540: (p_create_update_flag = 'C'
10541: OR
10542: (p_create_update_flag = 'U'
10540: (p_create_update_flag = 'C'
10541: OR
10542: (p_create_update_flag = 'U'
10543: AND
10544: p_work_class_rec.status <> NVL(l_status, fnd_api.g_miss_char)
10545: )
10546: )
10547: THEN
10548: validate_lookup (
10624: * p_person_interest_rec person_interest record.
10625: * p_rowid Rowid of the record (used only in update mode).
10626: * IN/OUT:
10627: * x_return_status Return status after the call. The status can
10628: * be FND_API.G_RET_STS_SUCCESS (success),
10629: * fnd_api.g_ret_sts_error (error),
10630: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10631: *
10632: * NOTES
10625: * p_rowid Rowid of the record (used only in update mode).
10626: * IN/OUT:
10627: * x_return_status Return status after the call. The status can
10628: * be FND_API.G_RET_STS_SUCCESS (success),
10629: * fnd_api.g_ret_sts_error (error),
10630: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10631: *
10632: * NOTES
10633: *
10626: * IN/OUT:
10627: * x_return_status Return status after the call. The status can
10628: * be FND_API.G_RET_STS_SUCCESS (success),
10629: * fnd_api.g_ret_sts_error (error),
10630: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10631: *
10632: * NOTES
10633: *
10634: * MODIFICATION HISTORY
10706:
10707:
10708: IF p_create_update_flag = 'C' THEN
10709: IF p_person_interest_rec.person_interest_id IS NOT NULL AND
10710: p_person_interest_rec.person_interest_id <> fnd_api.g_miss_num
10711: THEN
10712: OPEN person_interest_cur (p_person_interest_rec.person_interest_id);
10713: FETCH person_interest_cur INTO l_dummy;
10714:
10716: IF NVL(person_interest_cur%FOUND, FALSE) THEN
10717: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
10718: fnd_message.set_token('COLUMN', 'person_interest_id');
10719: fnd_msg_pub.add;
10720: x_return_status := fnd_api.g_ret_sts_error;
10721: END IF;
10722: CLOSE person_interest_cur;
10723:
10724: /*IF g_debug THEN
10828: IF p_create_update_flag = 'C'
10829: AND
10830: p_person_interest_rec.party_id IS NOT NULL
10831: AND
10832: p_person_interest_rec.party_id <> fnd_api.g_miss_num
10833: THEN
10834: BEGIN
10835: SELECT 'Y'
10836: INTO l_dummy
10848: fnd_message.set_token('FK', 'party_id');
10849: fnd_message.set_token('COLUMN', 'party_id');
10850: fnd_message.set_token('TABLE', 'hz_parties');
10851: fnd_msg_pub.add;
10852: x_return_status := fnd_api.g_ret_sts_error;
10853: END;
10854:
10855: /*IF g_debug THEN
10856: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
10929: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10930: -- status is lookup code in lookup type REGISTRY_STATUS
10931: IF p_person_interest_rec.status IS NOT NULL
10932: AND
10933: p_person_interest_rec.status <> fnd_api.g_miss_char
10934: AND
10935: (p_create_update_flag = 'C'
10936: OR
10937: (p_create_update_flag = 'U'
10935: (p_create_update_flag = 'C'
10936: OR
10937: (p_create_update_flag = 'U'
10938: AND
10939: p_person_interest_rec.status <> NVL(l_status, fnd_api.g_miss_char)
10940: )
10941: )
10942: THEN
10943: validate_lookup (
11020: * p_location_rec Location record.
11021: * p_rowid Rowid of the record (used only in update mode).
11022: * IN/OUT:
11023: * x_return_status Return status after the call. The status can
11024: * be FND_API.G_RET_STS_SUCCESS (success),
11025: * fnd_api.g_ret_sts_error (error),
11026: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11027: *
11028: * NOTES
11021: * p_rowid Rowid of the record (used only in update mode).
11022: * IN/OUT:
11023: * x_return_status Return status after the call. The status can
11024: * be FND_API.G_RET_STS_SUCCESS (success),
11025: * fnd_api.g_ret_sts_error (error),
11026: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11027: *
11028: * NOTES
11029: *
11022: * IN/OUT:
11023: * x_return_status Return status after the call. The status can
11024: * be FND_API.G_RET_STS_SUCCESS (success),
11025: * fnd_api.g_ret_sts_error (error),
11026: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11027: *
11028: * NOTES
11029: *
11030: * MODIFICATION HISTORY
11261: -- profile value.
11262: /*NVL(FND_PROFILE.value('HZ_UPDATE_THIRD_PARTY_DATA'), 'N') = 'N'*/
11263: --HZ_UTILITY_V2PUB.is_purchased_content_source(db_actual_content_source) = 'Y'
11264: THEN
11265: l_return_status := FND_API.G_RET_STS_SUCCESS;
11266:
11267: validate_nonupdateable (
11268: p_column => 'orig_system_reference',
11269: p_column_value => p_location_rec.orig_system_reference,
11368: p_old_column_value => db_location_directions,
11369: x_return_status => l_return_status,
11370: p_raise_error => 'N');
11371:
11372: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
11373:
11374: HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
11375: p_actual_content_source => db_actual_content_source,
11376: p_new_actual_content_source => p_location_rec.actual_content_source,
11380: HZ_UTILITY_V2PUB.G_UPDATE_ACS := 'Y';
11381: /*
11382: FND_MESSAGE.SET_NAME('AR', 'HZ_NOTALLOW_UPDATE_THIRD_PARTY');
11383: FND_MSG_PUB.ADD;
11384: x_return_status := FND_API.G_RET_STS_ERROR;
11385: */
11386: END IF;
11387:
11388: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
11407: --------------------------
11408: -- language has foreign key fnd_languages.language_code
11409: IF p_location_rec.language IS NOT NULL
11410: AND
11411: p_location_rec.language <> fnd_api.g_miss_char
11412: THEN
11413: BEGIN
11414: SELECT 'Y'
11415: INTO l_dummy
11422: fnd_message.set_token('FK', 'language');
11423: fnd_message.set_token('COLUMN', 'language_code');
11424: fnd_message.set_token('TABLE', 'fnd_languages(installed)');
11425: fnd_msg_pub.add;
11426: x_return_status := fnd_api.g_ret_sts_error;
11427: END;
11428:
11429: /*IF g_debug THEN
11430: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
11445: -----------------------------
11446: -- timezone_id has foreign key hz_timezones.timezone_id
11447: IF p_location_rec.timezone_id IS NOT NULL
11448: AND
11449: p_location_rec.timezone_id <> fnd_api.g_miss_num
11450: THEN
11451: BEGIN
11452: SELECT 'Y'
11453: INTO l_dummy
11459: fnd_message.set_token('FK', 'timezone_id');
11460: fnd_message.set_token('COLUMN', 'timezone_id');
11461: fnd_message.set_token('TABLE', 'hz_timezones');
11462: fnd_msg_pub.add;
11463: x_return_status := fnd_api.g_ret_sts_error;
11464: END;
11465:
11466: /*IF g_debug THEN
11467: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
11503: -- Added the below validations as a part of bug fix # 4967075
11504:
11505: l_vertex_taxware_installed := zx_r11i_tax_partner_pkg.TAX_VENDOR_EXTENSION;
11506:
11507: IF p_location_rec.sales_tax_geocode IS NOT NULL AND p_location_rec.sales_tax_geocode <> fnd_api.g_miss_char THEN
11508: If (zx_r11i_tax_partner_pkg.IS_GEOCODE_VALID(p_location_rec.sales_tax_geocode) = FALSE) then
11509: x_return_status := fnd_api.g_ret_sts_error;
11510: end if;
11511: END IF;
11505: l_vertex_taxware_installed := zx_r11i_tax_partner_pkg.TAX_VENDOR_EXTENSION;
11506:
11507: IF p_location_rec.sales_tax_geocode IS NOT NULL AND p_location_rec.sales_tax_geocode <> fnd_api.g_miss_char THEN
11508: If (zx_r11i_tax_partner_pkg.IS_GEOCODE_VALID(p_location_rec.sales_tax_geocode) = FALSE) then
11509: x_return_status := fnd_api.g_ret_sts_error;
11510: end if;
11511: END IF;
11512:
11513: IF p_location_rec.sales_tax_inside_city_limits IS NOT NULL AND p_location_rec.sales_tax_inside_city_limits <> fnd_api.g_miss_char THEN
11509: x_return_status := fnd_api.g_ret_sts_error;
11510: end if;
11511: END IF;
11512:
11513: IF p_location_rec.sales_tax_inside_city_limits IS NOT NULL AND p_location_rec.sales_tax_inside_city_limits <> fnd_api.g_miss_char THEN
11514: If (zx_r11i_tax_partner_pkg.IS_CITY_LIMIT_VALID(p_location_rec.sales_tax_inside_city_limits) = FALSE) then
11515: x_return_status := fnd_api.g_ret_sts_error;
11516: end if;
11517: END IF;
11511: END IF;
11512:
11513: IF p_location_rec.sales_tax_inside_city_limits IS NOT NULL AND p_location_rec.sales_tax_inside_city_limits <> fnd_api.g_miss_char THEN
11514: If (zx_r11i_tax_partner_pkg.IS_CITY_LIMIT_VALID(p_location_rec.sales_tax_inside_city_limits) = FALSE) then
11515: x_return_status := fnd_api.g_ret_sts_error;
11516: end if;
11517: END IF;
11518:
11519: --------------------------
11529: tax_location_validation(p_location_rec,
11530: p_create_update_flag,
11531: x_return_status);
11532:
11533: IF x_return_status = fnd_api.g_ret_sts_error THEN
11534: RAISE FND_API.G_EXC_ERROR;
11535: END IF;
11536:
11537: -- Debug info.
11530: p_create_update_flag,
11531: x_return_status);
11532:
11533: IF x_return_status = fnd_api.g_ret_sts_error THEN
11534: RAISE FND_API.G_EXC_ERROR;
11535: END IF;
11536:
11537: -- Debug info.
11538: /*IF g_debug THEN
11561: * p_location_rec Location record.
11562: * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
11563: * IN/OUT:
11564: * x_return_status Return status after the call. The status can
11565: * be FND_API.G_RET_STS_SUCCESS (success),
11566: * fnd_api.g_ret_sts_error (error),
11567: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11568: *
11569: * NOTES
11562: * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
11563: * IN/OUT:
11564: * x_return_status Return status after the call. The status can
11565: * be FND_API.G_RET_STS_SUCCESS (success),
11566: * fnd_api.g_ret_sts_error (error),
11567: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11568: *
11569: * NOTES
11570: *
11563: * IN/OUT:
11564: * x_return_status Return status after the call. The status can
11565: * be FND_API.G_RET_STS_SUCCESS (success),
11566: * fnd_api.g_ret_sts_error (error),
11567: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11568: *
11569: * NOTES
11570: *
11571: * MODIFICATION HISTORY
11693: IF l_is_remit_to_location = 'N' and l_loc_assignment_exist = 'Y' THEN
11694:
11695: -- check if the taxable components are changed
11696: IF ( (p_location_rec.country IS NOT NULL
11697: AND p_location_rec.country <> fnd_api.g_miss_char)
11698: OR (p_location_rec.city IS NOT NULL
11699: AND p_location_rec.city <> fnd_api.g_miss_char)
11700: OR (p_location_rec.state IS NOT NULL
11701: AND p_location_rec.state <> fnd_api.g_miss_char)
11695: -- check if the taxable components are changed
11696: IF ( (p_location_rec.country IS NOT NULL
11697: AND p_location_rec.country <> fnd_api.g_miss_char)
11698: OR (p_location_rec.city IS NOT NULL
11699: AND p_location_rec.city <> fnd_api.g_miss_char)
11700: OR (p_location_rec.state IS NOT NULL
11701: AND p_location_rec.state <> fnd_api.g_miss_char)
11702: OR (p_location_rec.county IS NOT NULL
11703: AND p_location_rec.county <> fnd_api.g_miss_char)
11697: AND p_location_rec.country <> fnd_api.g_miss_char)
11698: OR (p_location_rec.city IS NOT NULL
11699: AND p_location_rec.city <> fnd_api.g_miss_char)
11700: OR (p_location_rec.state IS NOT NULL
11701: AND p_location_rec.state <> fnd_api.g_miss_char)
11702: OR (p_location_rec.county IS NOT NULL
11703: AND p_location_rec.county <> fnd_api.g_miss_char)
11704: OR (p_location_rec.province IS NOT NULL
11705: AND p_location_rec.province <> fnd_api.g_miss_char)
11699: AND p_location_rec.city <> fnd_api.g_miss_char)
11700: OR (p_location_rec.state IS NOT NULL
11701: AND p_location_rec.state <> fnd_api.g_miss_char)
11702: OR (p_location_rec.county IS NOT NULL
11703: AND p_location_rec.county <> fnd_api.g_miss_char)
11704: OR (p_location_rec.province IS NOT NULL
11705: AND p_location_rec.province <> fnd_api.g_miss_char)
11706: OR (p_location_rec.postal_code IS NOT NULL
11707: AND p_location_rec.postal_code <> fnd_api.g_miss_char)
11701: AND p_location_rec.state <> fnd_api.g_miss_char)
11702: OR (p_location_rec.county IS NOT NULL
11703: AND p_location_rec.county <> fnd_api.g_miss_char)
11704: OR (p_location_rec.province IS NOT NULL
11705: AND p_location_rec.province <> fnd_api.g_miss_char)
11706: OR (p_location_rec.postal_code IS NOT NULL
11707: AND p_location_rec.postal_code <> fnd_api.g_miss_char)
11708: OR (p_location_rec.attribute1 IS NOT NULL
11709: AND p_location_rec.attribute1 <> fnd_api.g_miss_char)
11703: AND p_location_rec.county <> fnd_api.g_miss_char)
11704: OR (p_location_rec.province IS NOT NULL
11705: AND p_location_rec.province <> fnd_api.g_miss_char)
11706: OR (p_location_rec.postal_code IS NOT NULL
11707: AND p_location_rec.postal_code <> fnd_api.g_miss_char)
11708: OR (p_location_rec.attribute1 IS NOT NULL
11709: AND p_location_rec.attribute1 <> fnd_api.g_miss_char)
11710: OR (p_location_rec.attribute2 IS NOT NULL
11711: AND p_location_rec.attribute2 <> fnd_api.g_miss_char)
11705: AND p_location_rec.province <> fnd_api.g_miss_char)
11706: OR (p_location_rec.postal_code IS NOT NULL
11707: AND p_location_rec.postal_code <> fnd_api.g_miss_char)
11708: OR (p_location_rec.attribute1 IS NOT NULL
11709: AND p_location_rec.attribute1 <> fnd_api.g_miss_char)
11710: OR (p_location_rec.attribute2 IS NOT NULL
11711: AND p_location_rec.attribute2 <> fnd_api.g_miss_char)
11712: OR (p_location_rec.attribute3 IS NOT NULL
11713: AND p_location_rec.attribute3 <> fnd_api.g_miss_char)
11707: AND p_location_rec.postal_code <> fnd_api.g_miss_char)
11708: OR (p_location_rec.attribute1 IS NOT NULL
11709: AND p_location_rec.attribute1 <> fnd_api.g_miss_char)
11710: OR (p_location_rec.attribute2 IS NOT NULL
11711: AND p_location_rec.attribute2 <> fnd_api.g_miss_char)
11712: OR (p_location_rec.attribute3 IS NOT NULL
11713: AND p_location_rec.attribute3 <> fnd_api.g_miss_char)
11714: OR (p_location_rec.attribute4 IS NOT NULL
11715: AND p_location_rec.attribute4 <> fnd_api.g_miss_char)
11709: AND p_location_rec.attribute1 <> fnd_api.g_miss_char)
11710: OR (p_location_rec.attribute2 IS NOT NULL
11711: AND p_location_rec.attribute2 <> fnd_api.g_miss_char)
11712: OR (p_location_rec.attribute3 IS NOT NULL
11713: AND p_location_rec.attribute3 <> fnd_api.g_miss_char)
11714: OR (p_location_rec.attribute4 IS NOT NULL
11715: AND p_location_rec.attribute4 <> fnd_api.g_miss_char)
11716: OR (p_location_rec.attribute5 IS NOT NULL
11717: AND p_location_rec.attribute5 <> fnd_api.g_miss_char)
11711: AND p_location_rec.attribute2 <> fnd_api.g_miss_char)
11712: OR (p_location_rec.attribute3 IS NOT NULL
11713: AND p_location_rec.attribute3 <> fnd_api.g_miss_char)
11714: OR (p_location_rec.attribute4 IS NOT NULL
11715: AND p_location_rec.attribute4 <> fnd_api.g_miss_char)
11716: OR (p_location_rec.attribute5 IS NOT NULL
11717: AND p_location_rec.attribute5 <> fnd_api.g_miss_char)
11718: OR (p_location_rec.attribute6 IS NOT NULL
11719: AND p_location_rec.attribute6 <> fnd_api.g_miss_char)
11713: AND p_location_rec.attribute3 <> fnd_api.g_miss_char)
11714: OR (p_location_rec.attribute4 IS NOT NULL
11715: AND p_location_rec.attribute4 <> fnd_api.g_miss_char)
11716: OR (p_location_rec.attribute5 IS NOT NULL
11717: AND p_location_rec.attribute5 <> fnd_api.g_miss_char)
11718: OR (p_location_rec.attribute6 IS NOT NULL
11719: AND p_location_rec.attribute6 <> fnd_api.g_miss_char)
11720: OR (p_location_rec.attribute7 IS NOT NULL
11721: AND p_location_rec.attribute7 <> fnd_api.g_miss_char)
11715: AND p_location_rec.attribute4 <> fnd_api.g_miss_char)
11716: OR (p_location_rec.attribute5 IS NOT NULL
11717: AND p_location_rec.attribute5 <> fnd_api.g_miss_char)
11718: OR (p_location_rec.attribute6 IS NOT NULL
11719: AND p_location_rec.attribute6 <> fnd_api.g_miss_char)
11720: OR (p_location_rec.attribute7 IS NOT NULL
11721: AND p_location_rec.attribute7 <> fnd_api.g_miss_char)
11722: OR (p_location_rec.attribute8 IS NOT NULL
11723: AND p_location_rec.attribute8 <> fnd_api.g_miss_char)
11717: AND p_location_rec.attribute5 <> fnd_api.g_miss_char)
11718: OR (p_location_rec.attribute6 IS NOT NULL
11719: AND p_location_rec.attribute6 <> fnd_api.g_miss_char)
11720: OR (p_location_rec.attribute7 IS NOT NULL
11721: AND p_location_rec.attribute7 <> fnd_api.g_miss_char)
11722: OR (p_location_rec.attribute8 IS NOT NULL
11723: AND p_location_rec.attribute8 <> fnd_api.g_miss_char)
11724: OR (p_location_rec.attribute9 IS NOT NULL
11725: AND p_location_rec.attribute9 <> fnd_api.g_miss_char)
11719: AND p_location_rec.attribute6 <> fnd_api.g_miss_char)
11720: OR (p_location_rec.attribute7 IS NOT NULL
11721: AND p_location_rec.attribute7 <> fnd_api.g_miss_char)
11722: OR (p_location_rec.attribute8 IS NOT NULL
11723: AND p_location_rec.attribute8 <> fnd_api.g_miss_char)
11724: OR (p_location_rec.attribute9 IS NOT NULL
11725: AND p_location_rec.attribute9 <> fnd_api.g_miss_char)
11726: OR (p_location_rec.attribute10 IS NOT NULL
11727: AND p_location_rec.attribute10 <> fnd_api.g_miss_char))
11721: AND p_location_rec.attribute7 <> fnd_api.g_miss_char)
11722: OR (p_location_rec.attribute8 IS NOT NULL
11723: AND p_location_rec.attribute8 <> fnd_api.g_miss_char)
11724: OR (p_location_rec.attribute9 IS NOT NULL
11725: AND p_location_rec.attribute9 <> fnd_api.g_miss_char)
11726: OR (p_location_rec.attribute10 IS NOT NULL
11727: AND p_location_rec.attribute10 <> fnd_api.g_miss_char))
11728: THEN
11729: BEGIN
11723: AND p_location_rec.attribute8 <> fnd_api.g_miss_char)
11724: OR (p_location_rec.attribute9 IS NOT NULL
11725: AND p_location_rec.attribute9 <> fnd_api.g_miss_char)
11726: OR (p_location_rec.attribute10 IS NOT NULL
11727: AND p_location_rec.attribute10 <> fnd_api.g_miss_char))
11728: THEN
11729: BEGIN
11730: SELECT country, city, state, county, province, postal_code,
11731: attribute1, attribute2, attribute3, attribute4, attribute5,
11748: THEN
11749: fnd_message.set_name('AR', 'AR_CUST_ADDR_HAS_TRANSACTION');
11750: --Bug 2452282 fnd_message.set_token('COLUMN', 'tax related fields');
11751: fnd_msg_pub.add;
11752: x_return_status := fnd_api.g_ret_sts_error;
11753: ELSE -- non taxable components to be modified
11754: null; -- allow updates
11755: END IF;
11756: END IF;
11793: * p_relationship_type_rec relationship type record.
11794: * p_rowid Rowid of the record (used only in update mode).
11795: * IN/OUT:
11796: * x_return_status Return status after the call. The status can
11797: * be FND_API.G_RET_STS_SUCCESS (success),
11798: * fnd_api.g_ret_sts_error (error),
11799: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11800: *
11801: * NOTES
11794: * p_rowid Rowid of the record (used only in update mode).
11795: * IN/OUT:
11796: * x_return_status Return status after the call. The status can
11797: * be FND_API.G_RET_STS_SUCCESS (success),
11798: * fnd_api.g_ret_sts_error (error),
11799: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11800: *
11801: * NOTES
11802: *
11795: * IN/OUT:
11796: * x_return_status Return status after the call. The status can
11797: * be FND_API.G_RET_STS_SUCCESS (success),
11798: * fnd_api.g_ret_sts_error (error),
11799: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11800: *
11801: * NOTES
11802: *
11803: * MODIFICATION HISTORY
12036:
12037: -- status is lookup code in lookup type CODE_STATUS
12038: IF p_relationship_type_rec.status IS NOT NULL
12039: AND
12040: p_relationship_type_rec.status <> fnd_api.g_miss_char
12041: AND
12042: (p_create_update_flag = 'C'
12043: OR
12044: (p_create_update_flag = 'U'
12042: (p_create_update_flag = 'C'
12043: OR
12044: (p_create_update_flag = 'U'
12045: AND
12046: p_relationship_type_rec.status <> NVL(l_status, fnd_api.g_miss_char)
12047: )
12048: )
12049: THEN
12050: validate_lookup (
12095:
12096: -- create_party_flag is non-updateable if relationship records are already created based
12097: -- on the current value of the flag
12098: IF p_create_update_flag = 'U' THEN
12099: IF p_relationship_type_rec.create_party_flag <> fnd_api.g_miss_char
12100: AND
12101: p_relationship_type_rec.create_party_flag IS NOT NULL
12102: AND
12103: p_relationship_type_rec.create_party_flag <> l_create_party_flag
12119:
12120: -- update is not allowed, raise error
12121: fnd_message.set_name('AR', 'HZ_API_CPF_NON_UPDATEABLE');
12122: fnd_msg_pub.add;
12123: x_return_status := fnd_api.g_ret_sts_error;
12124:
12125: EXCEPTION
12126: WHEN NO_DATA_FOUND THEN
12127: NULL;
12145:
12146: -- update is not allowed, raise error
12147: fnd_message.set_name('AR', 'HZ_API_CPF_NON_UPDATEABLE');
12148: fnd_msg_pub.add;
12149: x_return_status := fnd_api.g_ret_sts_error;
12150:
12151: EXCEPTION
12152: WHEN NO_DATA_FOUND THEN
12153: NULL;
12471:
12472: -- subject_type has foreign key fnd_object_instance_sets.instance_set_name
12473: IF p_relationship_type_rec.subject_type IS NOT NULL
12474: AND
12475: p_relationship_type_rec.subject_type <> fnd_api.g_miss_char
12476: THEN
12477: BEGIN
12478: SELECT 'Y'
12479: INTO l_dummy
12485: fnd_message.set_token('FK', 'subject_type');
12486: fnd_message.set_token('COLUMN', 'instance_set_name');
12487: fnd_message.set_token('TABLE', 'fnd_object_instance_sets');
12488: fnd_msg_pub.add;
12489: x_return_status := fnd_api.g_ret_sts_error;
12490: END;
12491:
12492: /*IF g_debug THEN
12493: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12554:
12555: -- object_type has foreign key fnd_object_instance_sets.instance_set_name
12556: IF p_relationship_type_rec.object_type IS NOT NULL
12557: AND
12558: p_relationship_type_rec.object_type <> fnd_api.g_miss_char
12559: THEN
12560: BEGIN
12561: SELECT 'Y'
12562: INTO l_dummy
12568: fnd_message.set_token('FK', 'object_type');
12569: fnd_message.set_token('COLUMN', 'instance_set_name');
12570: fnd_message.set_token('TABLE', 'fnd_object_instance_sets');
12571: fnd_msg_pub.add;
12572: x_return_status := fnd_api.g_ret_sts_error;
12573: END;
12574:
12575: /*IF g_debug THEN
12576: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12647: | -- obtained from above query, error out NOCOPY
12648: | IF l_code <> p_relationship_type_rec.backward_rel_code then
12649: | fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION');
12650: | fnd_msg_pub.add;
12651: | x_return_status := FND_API.G_RET_STS_ERROR;
12652: | END IF;
12653: | END IF;
12654: */
12655:
12699: IF l_count <> 0
12700: THEN
12701: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION');
12702: fnd_msg_pub.add;
12703: x_return_status := FND_API.G_RET_STS_ERROR;
12704: END IF;
12705:
12706: /*IF g_debug THEN
12707: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12735:
12736: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
12737: fnd_message.set_token('COLUMN', 'relationship type, forward rel code, backward rel code, subject type, object type');
12738: fnd_msg_pub.add;
12739: x_return_status := fnd_api.g_ret_sts_error;
12740:
12741: EXCEPTION
12742: WHEN NO_DATA_FOUND THEN
12743: NULL;
12767: p_relationship_type_rec.direction_code <> 'N'
12768: THEN
12769: fnd_message.set_name('AR', 'HZ_INVALID_DIRECTION_CODE1');
12770: fnd_msg_pub.add;
12771: x_return_status := FND_API.G_RET_STS_ERROR;
12772: END IF;
12773:
12774: -- if forward_rel_code and backward_rel_code are not same then direction_flag
12775: -- cannot be 'N'
12777: AND p_relationship_type_rec.direction_code = 'N'
12778: THEN
12779: fnd_message.set_name('AR', 'HZ_INVALID_DIRECTION_CODE2');
12780: fnd_msg_pub.add;
12781: x_return_status := FND_API.G_RET_STS_ERROR;
12782: END IF;
12783:
12784: /*IF g_debug THEN
12785: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12854: fnd_message.set_token('VALUE1', 'Y(Yes)');
12855: fnd_message.set_token('COLUMN2', 'allow_circular_relationships');
12856: fnd_message.set_token('VALUE2', 'N(No)');
12857: fnd_msg_pub.add;
12858: x_return_status := FND_API.G_RET_STS_ERROR;
12859: END IF;
12860: END IF;
12861: /*IF g_debug THEN
12862: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12886: fnd_message.set_token('VALUE1', 'Y(Yes)');
12887: fnd_message.set_token('COLUMN2', 'direction_code');
12888: fnd_message.set_token('VALUE2', 'P(Parent)/C(Child)');
12889: fnd_msg_pub.add;
12890: x_return_status := FND_API.G_RET_STS_ERROR;
12891: END IF;
12892: END IF;
12893: /*IF g_debug THEN
12894: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12917: IF l_count > 0 THEN
12918: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
12919: fnd_message.set_token('COLUMN', 'role');
12920: fnd_msg_pub.add;
12921: x_return_status := fnd_api.g_ret_sts_error;
12922: END IF;
12923: END;
12924: END IF;
12925:
12926: -- validate role/rel_code
12927:
12928: IF p_create_update_flag = 'C' THEN
12929: IF ( p_relationship_type_rec.forward_role IS NOT NULL AND
12930: p_relationship_type_rec.forward_role <> fnd_api.g_miss_char) AND
12931: ( p_relationship_type_rec.backward_role IS NOT NULL AND
12932: p_relationship_type_rec.backward_role <> fnd_api.g_miss_char) THEN
12933: validate_rel_code(
12934: p_forward_rel_code =>p_relationship_type_rec.forward_rel_code,
12928: IF p_create_update_flag = 'C' THEN
12929: IF ( p_relationship_type_rec.forward_role IS NOT NULL AND
12930: p_relationship_type_rec.forward_role <> fnd_api.g_miss_char) AND
12931: ( p_relationship_type_rec.backward_role IS NOT NULL AND
12932: p_relationship_type_rec.backward_role <> fnd_api.g_miss_char) THEN
12933: validate_rel_code(
12934: p_forward_rel_code =>p_relationship_type_rec.forward_rel_code,
12935: p_backward_rel_code =>p_relationship_type_rec.backward_rel_code,
12936: p_forward_role => p_relationship_type_rec.forward_role,
12970: fnd_message.set_token('VALUE1', 'Y(Yes)');
12971: fnd_message.set_token('COLUMN2', 'allow_relate_to_self_flag');
12972: fnd_message.set_token('VALUE2', 'N(No)');
12973: fnd_msg_pub.add;
12974: x_return_status := FND_API.G_RET_STS_ERROR;
12975: END IF;
12976: END IF;
12977: /*IF g_debug THEN
12978: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
13003: fnd_message.set_token('VALUE1', 'N(No)');
13004: fnd_message.set_token('COLUMN2', 'allow_circular_relationships');
13005: fnd_message.set_token('VALUE2', 'Y(Yes)');
13006: fnd_msg_pub.add;
13007: x_return_status := FND_API.G_RET_STS_ERROR;
13008: END IF;
13009: END IF;
13010: /*IF g_debug THEN
13011: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
13045: fnd_message.set_token('VALUE1', 'N(No)');
13046: fnd_message.set_token('COLUMN2', 'multiple_parent_allowed');
13047: fnd_message.set_token('VALUE2', 'Y(Yes)');
13048: fnd_msg_pub.add;
13049: x_return_status := FND_API.G_RET_STS_ERROR;
13050: END IF;
13051: END IF;
13052:
13053: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
13085: fnd_message.set_token('ENTITY', 'relationship type');
13086: fnd_message.set_token('COLUMN1', 'relationship_type');
13087: fnd_message.set_token('COLUMN2', 'hierarchical_flag');
13088: fnd_msg_pub.add;
13089: x_return_status := FND_API.G_RET_STS_ERROR;
13090: END IF;
13091:
13092: EXCEPTION
13093: -- if no data found, then its fine since this is the first record
13131: fnd_message.set_token('ENTITY', 'relationship type');
13132: fnd_message.set_token('COLUMN1', 'relationship_type');
13133: fnd_message.set_token('COLUMN2', 'allow_circular_relationships');
13134: fnd_msg_pub.add;
13135: x_return_status := FND_API.G_RET_STS_ERROR;
13136: END IF;
13137:
13138: EXCEPTION
13139: -- if no data found, then its fine since this is the first record
13174: fnd_message.set_token('ENTITY', 'relationship type');
13175: fnd_message.set_token('COLUMN1', 'relationship_type');
13176: fnd_message.set_token('COLUMN2', 'multiple_parent_allowed');
13177: fnd_msg_pub.add;
13178: x_return_status := FND_API.G_RET_STS_ERROR;
13179: END IF;
13180:
13181: EXCEPTION
13182: -- if no data found, then its fine since this is the first record
13300: AND (p_relationship_type_rec.subject_type <> p_relationship_type_rec.object_type))
13301: THEN
13302: fnd_message.set_name('AR', 'HZ_API_RELTYPE_INVALID');
13303: fnd_msg_pub.add;
13304: x_return_status := fnd_api.g_ret_sts_error;
13305:
13306: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
13307: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
13308: p_message=>'Same subject and object type because the relationship phrase pair has the same subject (forward) and object (backward) phrase. ' ||
13336: * p_relationship_rec Relationship record.
13337: * p_rowid Rowid of the record (used only in update mode).
13338: * IN/OUT:
13339: * x_return_status Return status after the call. The status can
13340: * be FND_API.G_RET_STS_SUCCESS (success),
13341: * fnd_api.g_ret_sts_error (error),
13342: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
13343: *
13344: * NOTES
13337: * p_rowid Rowid of the record (used only in update mode).
13338: * IN/OUT:
13339: * x_return_status Return status after the call. The status can
13340: * be FND_API.G_RET_STS_SUCCESS (success),
13341: * fnd_api.g_ret_sts_error (error),
13342: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
13343: *
13344: * NOTES
13345: *
13338: * IN/OUT:
13339: * x_return_status Return status after the call. The status can
13340: * be FND_API.G_RET_STS_SUCCESS (success),
13341: * fnd_api.g_ret_sts_error (error),
13342: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
13343: *
13344: * NOTES
13345: *
13346: * MODIFICATION HISTORY
13403: AND hr.relationship_type = l_relationship_type
13404: --AND hr.directional_flag = 'F'
13405: AND hr.status = 'A'
13406: AND hr.actual_content_source = p_data_source
13407: AND NVL(p_relationship_rec.relationship_id, fnd_api.g_miss_num) <> hr.relationship_id;
13408:
13409: -- Bug 3294936 : Comment rownum = 1 so that the cursor picks all records
13410: -- with same relationship type between same subject and object.
13411:
13636: END IF;
13637:
13638: -- check whether subject_id belongs to subject_type
13639: IF p_relationship_rec.subject_id IS NOT NULL
13640: AND p_relationship_rec.subject_id <> fnd_api.g_miss_num
13641: AND p_create_update_flag = 'C'
13642: THEN
13643: l_in := hz_relationship_type_v2pub.in_instance_sets (
13644: p_relationship_rec.subject_type,
13651: fnd_message.set_token('COLUMN',
13652: lower(p_relationship_rec.subject_type)||' id');
13653: fnd_message.set_token('TABLE', p_relationship_rec.subject_table_name);
13654: fnd_msg_pub.add;
13655: x_return_status := fnd_api.g_ret_sts_error;
13656: END IF;
13657:
13658: /*IF g_debug THEN
13659: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'subject_id belongs to subject_type. ' ||
13719: -- do not need to check during update because subject_table_name is
13720: -- non-updateable.
13721: IF p_create_update_flag = 'C'
13722: AND p_relationship_rec.subject_table_name IS NOT NULL
13723: AND p_relationship_rec.subject_table_name <> fnd_api.g_miss_char
13724: THEN
13725: BEGIN
13726: SELECT 'Y'
13727: INTO l_dummy
13733: fnd_message.set_token('FK', 'subject_table_name');
13734: fnd_message.set_token('COLUMN', 'obj_name');
13735: fnd_message.set_token('TABLE', 'fnd_objects');
13736: fnd_msg_pub.add;
13737: x_return_status := fnd_api.g_ret_sts_error;
13738: END;
13739:
13740: /*IF g_debug THEN
13741: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
13805: -- do not need to check during update because subject_type is
13806: -- non-updateable.
13807: IF p_create_update_flag = 'C'
13808: AND p_relationship_rec.subject_type IS NOT NULL
13809: AND p_relationship_rec.subject_type <> fnd_api.g_miss_char
13810: THEN
13811: BEGIN
13812: SELECT 'Y'
13813: INTO l_dummy
13819: fnd_message.set_token('FK', 'subject_type');
13820: fnd_message.set_token('COLUMN', 'instance_set_name');
13821: fnd_message.set_token('TABLE', 'fnd_object_instance_sets');
13822: fnd_msg_pub.add;
13823: x_return_status := fnd_api.g_ret_sts_error;
13824: END;
13825:
13826: /*IF g_debug THEN
13827: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
13890: END IF;
13891:
13892: -- check whether object_id belongs to object_type
13893: IF p_relationship_rec.object_id IS NOT NULL
13894: AND p_relationship_rec.object_id <> fnd_api.g_miss_num
13895: AND p_create_update_flag = 'C'
13896: THEN
13897: l_in := hz_relationship_type_v2pub.in_instance_sets (
13898: p_relationship_rec.object_type,
13905: fnd_message.set_token('COLUMN',
13906: LOWER(p_relationship_rec.object_type)||' id');
13907: fnd_message.set_token('TABLE', p_relationship_rec.object_table_name);
13908: fnd_msg_pub.add;
13909: x_return_status := fnd_api.g_ret_sts_error;
13910: END IF;
13911:
13912: /*IF g_debug THEN
13913: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'object_id belongs to object_type. ' ||
13975: -- do not need to check during update because object_table_name is
13976: -- non-updateable.
13977: IF p_create_update_flag = 'C'
13978: AND p_relationship_rec.object_table_name IS NOT NULL
13979: AND p_relationship_rec.object_table_name <> fnd_api.g_miss_char
13980: THEN
13981: BEGIN
13982: SELECT 'Y'
13983: INTO l_dummy
13989: fnd_message.set_token('FK', 'object_table_name');
13990: fnd_message.set_token('COLUMN', 'obj_name');
13991: fnd_message.set_token('TABLE', 'fnd_objects');
13992: fnd_msg_pub.add;
13993: x_return_status := fnd_api.g_ret_sts_error;
13994: END;
13995:
13996: /*IF g_debug THEN
13997: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
14063: -- do not need to check during update because object_type is
14064: -- non-updateable.
14065: IF p_create_update_flag = 'C'
14066: AND p_relationship_rec.object_type IS NOT NULL
14067: AND p_relationship_rec.object_type <> fnd_api.g_miss_char
14068: THEN
14069: BEGIN
14070: SELECT 'Y'
14071: INTO l_dummy
14077: fnd_message.set_token('FK', 'object_type');
14078: fnd_message.set_token('COLUMN', 'instance_set_name');
14079: fnd_message.set_token('TABLE', 'fnd_object_instance_sets');
14080: fnd_msg_pub.add;
14081: x_return_status := fnd_api.g_ret_sts_error;
14082: END;
14083:
14084: /*IF g_debug THEN
14085: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
14201: /****Logical APIs - validation not required****/
14202: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
14203: -- status is lookup code in lookup type REGISTRY_STATUS
14204: IF p_relationship_rec.status IS NOT NULL
14205: AND p_relationship_rec.status <> fnd_api.g_miss_char
14206: AND (p_create_update_flag = 'C'
14207: OR (p_create_update_flag = 'U'
14208: AND p_relationship_rec.status <> NVL(l_status,
14209: fnd_api.g_miss_char)))
14205: AND p_relationship_rec.status <> fnd_api.g_miss_char
14206: AND (p_create_update_flag = 'C'
14207: OR (p_create_update_flag = 'U'
14208: AND p_relationship_rec.status <> NVL(l_status,
14209: fnd_api.g_miss_char)))
14210: THEN
14211: validate_lookup (
14212: p_column => 'status',
14213: p_lookup_type => 'REGISTRY_STATUS',
14301:
14302: -- end_date must be null or greater than start date
14303: IF (p_create_update_flag = 'C') THEN
14304: IF p_relationship_rec.end_date IS NOT NULL AND
14305: p_relationship_rec.end_date <> fnd_api.g_miss_date AND
14306: p_relationship_rec.end_date < p_relationship_rec.start_date
14307: THEN
14308: fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
14309: fnd_msg_pub.add;
14306: p_relationship_rec.end_date < p_relationship_rec.start_date
14307: THEN
14308: fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
14309: fnd_msg_pub.add;
14310: x_return_status := fnd_api.g_ret_sts_error;
14311: END IF;
14312: ELSIF (p_create_update_flag = 'U') THEN
14313: -- old start_date, end_date has been selected from table
14314: -- and put into l_start_date, l_end_date
14312: ELSIF (p_create_update_flag = 'U') THEN
14313: -- old start_date, end_date has been selected from table
14314: -- and put into l_start_date, l_end_date
14315:
14316: IF p_relationship_rec.start_date <> fnd_api.g_miss_date THEN
14317: l_start_date := p_relationship_rec.start_date;
14318: END IF;
14319:
14320: IF p_relationship_rec.end_date IS NULL
14317: l_start_date := p_relationship_rec.start_date;
14318: END IF;
14319:
14320: IF p_relationship_rec.end_date IS NULL
14321: OR p_relationship_rec.end_date <> fnd_api.g_miss_date
14322: THEN
14323: l_end_date := p_relationship_rec.end_date;
14324: END IF;
14325:
14327: AND l_end_date < l_start_date
14328: THEN
14329: fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
14330: fnd_msg_pub.add;
14331: x_return_status := fnd_api.g_ret_sts_error;
14332: END IF;
14333: END IF;
14334:
14335: /*IF g_debug THEN
14371: );
14372: IF l_overlap = 'Y' THEN
14373: fnd_message.set_name('AR', 'HZ_RELATIONSHIP_DATE_OVERLAP');
14374: fnd_msg_pub.add;
14375: x_return_status := fnd_api.g_ret_sts_error;
14376: -- Bug 3294936 : Add exit to come out of loop
14377: EXIT;
14378: END IF;
14379: END LOOP;
14438: -- p_web_rec Web record.
14439: -- p_rowid Rowid of the record (used only in update mode).
14440: -- IN/OUT:
14441: -- x_return_status Return status after the call. The status can
14442: -- be FND_API.G_RET_STS_SUCCESS (success),
14443: -- fnd_api.g_ret_sts_error (error),
14444: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14445: --
14446: -- NOTES
14439: -- p_rowid Rowid of the record (used only in update mode).
14440: -- IN/OUT:
14441: -- x_return_status Return status after the call. The status can
14442: -- be FND_API.G_RET_STS_SUCCESS (success),
14443: -- fnd_api.g_ret_sts_error (error),
14444: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14445: --
14446: -- NOTES
14447: --
14440: -- IN/OUT:
14441: -- x_return_status Return status after the call. The status can
14442: -- be FND_API.G_RET_STS_SUCCESS (success),
14443: -- fnd_api.g_ret_sts_error (error),
14444: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14445: --
14446: -- NOTES
14447: --
14448: -- MODIFICATION HISTORY
14523: -- p_edi_rec EDI record.
14524: -- p_rowid Rowid of the record (used only in update mode).
14525: -- IN/OUT:
14526: -- x_return_status Return status after the call. The status can
14527: -- be FND_API.G_RET_STS_SUCCESS (success),
14528: -- fnd_api.g_ret_sts_error (error),
14529: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14530: --
14531: -- NOTES
14524: -- p_rowid Rowid of the record (used only in update mode).
14525: -- IN/OUT:
14526: -- x_return_status Return status after the call. The status can
14527: -- be FND_API.G_RET_STS_SUCCESS (success),
14528: -- fnd_api.g_ret_sts_error (error),
14529: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14530: --
14531: -- NOTES
14532: --
14525: -- IN/OUT:
14526: -- x_return_status Return status after the call. The status can
14527: -- be FND_API.G_RET_STS_SUCCESS (success),
14528: -- fnd_api.g_ret_sts_error (error),
14529: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14530: --
14531: -- NOTES
14532: --
14533: -- MODIFICATION HISTORY
14593: -- p_eft_rec EFT record.
14594: -- p_rowid Rowid of the record (used only in update mode).
14595: -- IN/OUT:
14596: -- x_return_status Return status after the call. The status can
14597: -- be FND_API.G_RET_STS_SUCCESS (success),
14598: -- fnd_api.g_ret_sts_error (error),
14599: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14600: --
14601: -- NOTES
14594: -- p_rowid Rowid of the record (used only in update mode).
14595: -- IN/OUT:
14596: -- x_return_status Return status after the call. The status can
14597: -- be FND_API.G_RET_STS_SUCCESS (success),
14598: -- fnd_api.g_ret_sts_error (error),
14599: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14600: --
14601: -- NOTES
14602: --
14595: -- IN/OUT:
14596: -- x_return_status Return status after the call. The status can
14597: -- be FND_API.G_RET_STS_SUCCESS (success),
14598: -- fnd_api.g_ret_sts_error (error),
14599: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14600: --
14601: -- NOTES
14602: --
14603: -- MODIFICATION HISTORY
14663: -- p_web_rec Web record.
14664: -- p_rowid Rowid of the record (used only in update mode).
14665: -- IN/OUT:
14666: -- x_return_status Return status after the call. The status can
14667: -- be FND_API.G_RET_STS_SUCCESS (success),
14668: -- fnd_api.g_ret_sts_error (error),
14669: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14670: --
14671: -- NOTES
14664: -- p_rowid Rowid of the record (used only in update mode).
14665: -- IN/OUT:
14666: -- x_return_status Return status after the call. The status can
14667: -- be FND_API.G_RET_STS_SUCCESS (success),
14668: -- fnd_api.g_ret_sts_error (error),
14669: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14670: --
14671: -- NOTES
14672: --
14665: -- IN/OUT:
14666: -- x_return_status Return status after the call. The status can
14667: -- be FND_API.G_RET_STS_SUCCESS (success),
14668: -- fnd_api.g_ret_sts_error (error),
14669: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14670: --
14671: -- NOTES
14672: --
14673: -- MODIFICATION HISTORY
14734: -- p_phone_rec Phone record.
14735: -- p_rowid Rowid of the record (used only in update mode).
14736: -- IN/OUT:
14737: -- x_return_status Return status after the call. The status can
14738: -- be FND_API.G_RET_STS_SUCCESS (success),
14739: -- fnd_api.g_ret_sts_error (error),
14740: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14741: --
14742: -- NOTES
14735: -- p_rowid Rowid of the record (used only in update mode).
14736: -- IN/OUT:
14737: -- x_return_status Return status after the call. The status can
14738: -- be FND_API.G_RET_STS_SUCCESS (success),
14739: -- fnd_api.g_ret_sts_error (error),
14740: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14741: --
14742: -- NOTES
14743: --
14736: -- IN/OUT:
14737: -- x_return_status Return status after the call. The status can
14738: -- be FND_API.G_RET_STS_SUCCESS (success),
14739: -- fnd_api.g_ret_sts_error (error),
14740: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14741: --
14742: -- NOTES
14743: --
14744: -- MODIFICATION HISTORY
14804: -- p_telex_rec Telex record.
14805: -- p_rowid Rowid of the record (used only in update mode).
14806: -- IN/OUT:
14807: -- x_return_status Return status after the call. The status can
14808: -- be FND_API.G_RET_STS_SUCCESS (success),
14809: -- fnd_api.g_ret_sts_error (error),
14810: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14811: --
14812: -- NOTES
14805: -- p_rowid Rowid of the record (used only in update mode).
14806: -- IN/OUT:
14807: -- x_return_status Return status after the call. The status can
14808: -- be FND_API.G_RET_STS_SUCCESS (success),
14809: -- fnd_api.g_ret_sts_error (error),
14810: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14811: --
14812: -- NOTES
14813: --
14806: -- IN/OUT:
14807: -- x_return_status Return status after the call. The status can
14808: -- be FND_API.G_RET_STS_SUCCESS (success),
14809: -- fnd_api.g_ret_sts_error (error),
14810: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14811: --
14812: -- NOTES
14813: --
14814: -- MODIFICATION HISTORY
14874: -- p_email_rec Email record.
14875: -- p_rowid Rowid of the record (used only in update mode).
14876: -- IN/OUT:
14877: -- x_return_status Return status after the call. The status can
14878: -- be FND_API.G_RET_STS_SUCCESS (success),
14879: -- fnd_api.g_ret_sts_error (error),
14880: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14881: --
14882: -- NOTES
14875: -- p_rowid Rowid of the record (used only in update mode).
14876: -- IN/OUT:
14877: -- x_return_status Return status after the call. The status can
14878: -- be FND_API.G_RET_STS_SUCCESS (success),
14879: -- fnd_api.g_ret_sts_error (error),
14880: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14881: --
14882: -- NOTES
14883: --
14876: -- IN/OUT:
14877: -- x_return_status Return status after the call. The status can
14878: -- be FND_API.G_RET_STS_SUCCESS (success),
14879: -- fnd_api.g_ret_sts_error (error),
14880: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14881: --
14882: -- NOTES
14883: --
14884: -- MODIFICATION HISTORY
14950:
14951:
14952: IF ( p_create_update_flag = 'C' AND
14953: p_organization_rec.avg_high_credit IS NOT NULL AND
14954: p_organization_rec.avg_high_credit <> FND_API.G_MISS_NUM )
14955: OR
14956: ( p_create_update_flag = 'U' AND
14957: ( p_organization_rec.avg_high_credit = FND_API.G_MISS_NUM OR
14958: p_organization_rec.avg_high_credit IS NOT NULL
14953: p_organization_rec.avg_high_credit IS NOT NULL AND
14954: p_organization_rec.avg_high_credit <> FND_API.G_MISS_NUM )
14955: OR
14956: ( p_create_update_flag = 'U' AND
14957: ( p_organization_rec.avg_high_credit = FND_API.G_MISS_NUM OR
14958: p_organization_rec.avg_high_credit IS NOT NULL
14959: ) )
14960: THEN
14961: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
14960: THEN
14961: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
14962: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'avg_high_credit' );
14963: FND_MSG_PUB.ADD;
14964: x_return_status := FND_API.G_RET_STS_ERROR;
14965: END IF;
14966:
14967: IF ( p_create_update_flag = 'C' AND
14968: p_organization_rec.credit_score IS NOT NULL AND
14965: END IF;
14966:
14967: IF ( p_create_update_flag = 'C' AND
14968: p_organization_rec.credit_score IS NOT NULL AND
14969: p_organization_rec.credit_score <> FND_API.G_MISS_CHAR )
14970: OR
14971: ( p_create_update_flag = 'U' AND
14972: ( p_organization_rec.credit_score = FND_API.G_MISS_CHAR OR
14973: p_organization_rec.credit_score IS NOT NULL
14968: p_organization_rec.credit_score IS NOT NULL AND
14969: p_organization_rec.credit_score <> FND_API.G_MISS_CHAR )
14970: OR
14971: ( p_create_update_flag = 'U' AND
14972: ( p_organization_rec.credit_score = FND_API.G_MISS_CHAR OR
14973: p_organization_rec.credit_score IS NOT NULL
14974: ) )
14975: THEN
14976: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
14975: THEN
14976: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
14977: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score' );
14978: FND_MSG_PUB.ADD;
14979: x_return_status := FND_API.G_RET_STS_ERROR;
14980: END IF;
14981:
14982: IF ( p_create_update_flag = 'C' AND
14983: p_organization_rec.credit_score_age IS NOT NULL AND
14980: END IF;
14981:
14982: IF ( p_create_update_flag = 'C' AND
14983: p_organization_rec.credit_score_age IS NOT NULL AND
14984: p_organization_rec.credit_score_age <> FND_API.G_MISS_NUM )
14985: OR
14986: ( p_create_update_flag = 'U' AND
14987: ( p_organization_rec.credit_score_age = FND_API.G_MISS_NUM OR
14988: p_organization_rec.credit_score_age IS NOT NULL
14983: p_organization_rec.credit_score_age IS NOT NULL AND
14984: p_organization_rec.credit_score_age <> FND_API.G_MISS_NUM )
14985: OR
14986: ( p_create_update_flag = 'U' AND
14987: ( p_organization_rec.credit_score_age = FND_API.G_MISS_NUM OR
14988: p_organization_rec.credit_score_age IS NOT NULL
14989: ) )
14990: THEN
14991: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
14990: THEN
14991: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
14992: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_age' );
14993: FND_MSG_PUB.ADD;
14994: x_return_status := FND_API.G_RET_STS_ERROR;
14995: END IF;
14996:
14997: IF ( p_create_update_flag = 'C' AND
14998: p_organization_rec.credit_score_class IS NOT NULL AND
14995: END IF;
14996:
14997: IF ( p_create_update_flag = 'C' AND
14998: p_organization_rec.credit_score_class IS NOT NULL AND
14999: p_organization_rec.credit_score_class <> FND_API.G_MISS_NUM )
15000: OR
15001: ( p_create_update_flag = 'U' AND
15002: ( p_organization_rec.credit_score_class = FND_API.G_MISS_NUM OR
15003: p_organization_rec.credit_score_class IS NOT NULL
14998: p_organization_rec.credit_score_class IS NOT NULL AND
14999: p_organization_rec.credit_score_class <> FND_API.G_MISS_NUM )
15000: OR
15001: ( p_create_update_flag = 'U' AND
15002: ( p_organization_rec.credit_score_class = FND_API.G_MISS_NUM OR
15003: p_organization_rec.credit_score_class IS NOT NULL
15004: ) )
15005: THEN
15006: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15005: THEN
15006: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15007: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_class' );
15008: FND_MSG_PUB.ADD;
15009: x_return_status := FND_API.G_RET_STS_ERROR;
15010: END IF;
15011:
15012: IF ( p_create_update_flag = 'C' AND
15013: p_organization_rec.credit_score_commentary IS NOT NULL AND
15010: END IF;
15011:
15012: IF ( p_create_update_flag = 'C' AND
15013: p_organization_rec.credit_score_commentary IS NOT NULL AND
15014: p_organization_rec.credit_score_commentary <> FND_API.G_MISS_CHAR )
15015: OR
15016: ( p_create_update_flag = 'U' AND
15017: ( p_organization_rec.credit_score_commentary = FND_API.G_MISS_CHAR OR
15018: p_organization_rec.credit_score_commentary IS NOT NULL
15013: p_organization_rec.credit_score_commentary IS NOT NULL AND
15014: p_organization_rec.credit_score_commentary <> FND_API.G_MISS_CHAR )
15015: OR
15016: ( p_create_update_flag = 'U' AND
15017: ( p_organization_rec.credit_score_commentary = FND_API.G_MISS_CHAR OR
15018: p_organization_rec.credit_score_commentary IS NOT NULL
15019: ) )
15020: THEN
15021: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15020: THEN
15021: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15022: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary' );
15023: FND_MSG_PUB.ADD;
15024: x_return_status := FND_API.G_RET_STS_ERROR;
15025: END IF;
15026:
15027: IF ( p_create_update_flag = 'C' AND
15028: p_organization_rec.credit_score_commentary2 IS NOT NULL AND
15025: END IF;
15026:
15027: IF ( p_create_update_flag = 'C' AND
15028: p_organization_rec.credit_score_commentary2 IS NOT NULL AND
15029: p_organization_rec.credit_score_commentary2 <> FND_API.G_MISS_CHAR )
15030: OR
15031: ( p_create_update_flag = 'U' AND
15032: ( p_organization_rec.credit_score_commentary2 = FND_API.G_MISS_CHAR OR
15033: p_organization_rec.credit_score_commentary2 IS NOT NULL
15028: p_organization_rec.credit_score_commentary2 IS NOT NULL AND
15029: p_organization_rec.credit_score_commentary2 <> FND_API.G_MISS_CHAR )
15030: OR
15031: ( p_create_update_flag = 'U' AND
15032: ( p_organization_rec.credit_score_commentary2 = FND_API.G_MISS_CHAR OR
15033: p_organization_rec.credit_score_commentary2 IS NOT NULL
15034: ) )
15035: THEN
15036: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15035: THEN
15036: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15037: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary2' );
15038: FND_MSG_PUB.ADD;
15039: x_return_status := FND_API.G_RET_STS_ERROR;
15040: END IF;
15041:
15042: IF ( p_create_update_flag = 'C' AND
15043: p_organization_rec.credit_score_commentary3 IS NOT NULL AND
15040: END IF;
15041:
15042: IF ( p_create_update_flag = 'C' AND
15043: p_organization_rec.credit_score_commentary3 IS NOT NULL AND
15044: p_organization_rec.credit_score_commentary3 <> FND_API.G_MISS_CHAR )
15045: OR
15046: ( p_create_update_flag = 'U' AND
15047: ( p_organization_rec.credit_score_commentary3 = FND_API.G_MISS_CHAR OR
15048: p_organization_rec.credit_score_commentary3 IS NOT NULL
15043: p_organization_rec.credit_score_commentary3 IS NOT NULL AND
15044: p_organization_rec.credit_score_commentary3 <> FND_API.G_MISS_CHAR )
15045: OR
15046: ( p_create_update_flag = 'U' AND
15047: ( p_organization_rec.credit_score_commentary3 = FND_API.G_MISS_CHAR OR
15048: p_organization_rec.credit_score_commentary3 IS NOT NULL
15049: ) )
15050: THEN
15051: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15050: THEN
15051: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15052: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary3' );
15053: FND_MSG_PUB.ADD;
15054: x_return_status := FND_API.G_RET_STS_ERROR;
15055: END IF;
15056:
15057: IF ( p_create_update_flag = 'C' AND
15058: p_organization_rec.credit_score_commentary4 IS NOT NULL AND
15055: END IF;
15056:
15057: IF ( p_create_update_flag = 'C' AND
15058: p_organization_rec.credit_score_commentary4 IS NOT NULL AND
15059: p_organization_rec.credit_score_commentary4 <> FND_API.G_MISS_CHAR )
15060: OR
15061: ( p_create_update_flag = 'U' AND
15062: ( p_organization_rec.credit_score_commentary4 = FND_API.G_MISS_CHAR OR
15063: p_organization_rec.credit_score_commentary4 IS NOT NULL
15058: p_organization_rec.credit_score_commentary4 IS NOT NULL AND
15059: p_organization_rec.credit_score_commentary4 <> FND_API.G_MISS_CHAR )
15060: OR
15061: ( p_create_update_flag = 'U' AND
15062: ( p_organization_rec.credit_score_commentary4 = FND_API.G_MISS_CHAR OR
15063: p_organization_rec.credit_score_commentary4 IS NOT NULL
15064: ) )
15065: THEN
15066: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15065: THEN
15066: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15067: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary4' );
15068: FND_MSG_PUB.ADD;
15069: x_return_status := FND_API.G_RET_STS_ERROR;
15070: END IF;
15071:
15072: IF ( p_create_update_flag = 'C' AND
15073: p_organization_rec.credit_score_commentary5 IS NOT NULL AND
15070: END IF;
15071:
15072: IF ( p_create_update_flag = 'C' AND
15073: p_organization_rec.credit_score_commentary5 IS NOT NULL AND
15074: p_organization_rec.credit_score_commentary5 <> FND_API.G_MISS_CHAR )
15075: OR
15076: ( p_create_update_flag = 'U' AND
15077: ( p_organization_rec.credit_score_commentary5 = FND_API.G_MISS_CHAR OR
15078: p_organization_rec.credit_score_commentary5 IS NOT NULL
15073: p_organization_rec.credit_score_commentary5 IS NOT NULL AND
15074: p_organization_rec.credit_score_commentary5 <> FND_API.G_MISS_CHAR )
15075: OR
15076: ( p_create_update_flag = 'U' AND
15077: ( p_organization_rec.credit_score_commentary5 = FND_API.G_MISS_CHAR OR
15078: p_organization_rec.credit_score_commentary5 IS NOT NULL
15079: ) )
15080: THEN
15081: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15080: THEN
15081: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15082: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary5' );
15083: FND_MSG_PUB.ADD;
15084: x_return_status := FND_API.G_RET_STS_ERROR;
15085: END IF;
15086:
15087: IF ( p_create_update_flag = 'C' AND
15088: p_organization_rec.credit_score_commentary6 IS NOT NULL AND
15085: END IF;
15086:
15087: IF ( p_create_update_flag = 'C' AND
15088: p_organization_rec.credit_score_commentary6 IS NOT NULL AND
15089: p_organization_rec.credit_score_commentary6 <> FND_API.G_MISS_CHAR )
15090: OR
15091: ( p_create_update_flag = 'U' AND
15092: ( p_organization_rec.credit_score_commentary6 = FND_API.G_MISS_CHAR OR
15093: p_organization_rec.credit_score_commentary6 IS NOT NULL
15088: p_organization_rec.credit_score_commentary6 IS NOT NULL AND
15089: p_organization_rec.credit_score_commentary6 <> FND_API.G_MISS_CHAR )
15090: OR
15091: ( p_create_update_flag = 'U' AND
15092: ( p_organization_rec.credit_score_commentary6 = FND_API.G_MISS_CHAR OR
15093: p_organization_rec.credit_score_commentary6 IS NOT NULL
15094: ) )
15095: THEN
15096: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15095: THEN
15096: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15097: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary6' );
15098: FND_MSG_PUB.ADD;
15099: x_return_status := FND_API.G_RET_STS_ERROR;
15100: END IF;
15101:
15102: IF ( p_create_update_flag = 'C' AND
15103: p_organization_rec.credit_score_commentary7 IS NOT NULL AND
15100: END IF;
15101:
15102: IF ( p_create_update_flag = 'C' AND
15103: p_organization_rec.credit_score_commentary7 IS NOT NULL AND
15104: p_organization_rec.credit_score_commentary7 <> FND_API.G_MISS_CHAR )
15105: OR
15106: ( p_create_update_flag = 'U' AND
15107: ( p_organization_rec.credit_score_commentary7 = FND_API.G_MISS_CHAR OR
15108: p_organization_rec.credit_score_commentary7 IS NOT NULL
15103: p_organization_rec.credit_score_commentary7 IS NOT NULL AND
15104: p_organization_rec.credit_score_commentary7 <> FND_API.G_MISS_CHAR )
15105: OR
15106: ( p_create_update_flag = 'U' AND
15107: ( p_organization_rec.credit_score_commentary7 = FND_API.G_MISS_CHAR OR
15108: p_organization_rec.credit_score_commentary7 IS NOT NULL
15109: ) )
15110: THEN
15111: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15110: THEN
15111: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15112: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary7' );
15113: FND_MSG_PUB.ADD;
15114: x_return_status := FND_API.G_RET_STS_ERROR;
15115: END IF;
15116:
15117: IF ( p_create_update_flag = 'C' AND
15118: p_organization_rec.credit_score_commentary8 IS NOT NULL AND
15115: END IF;
15116:
15117: IF ( p_create_update_flag = 'C' AND
15118: p_organization_rec.credit_score_commentary8 IS NOT NULL AND
15119: p_organization_rec.credit_score_commentary8 <> FND_API.G_MISS_CHAR )
15120: OR
15121: ( p_create_update_flag = 'U' AND
15122: ( p_organization_rec.credit_score_commentary8 = FND_API.G_MISS_CHAR OR
15123: p_organization_rec.credit_score_commentary8 IS NOT NULL
15118: p_organization_rec.credit_score_commentary8 IS NOT NULL AND
15119: p_organization_rec.credit_score_commentary8 <> FND_API.G_MISS_CHAR )
15120: OR
15121: ( p_create_update_flag = 'U' AND
15122: ( p_organization_rec.credit_score_commentary8 = FND_API.G_MISS_CHAR OR
15123: p_organization_rec.credit_score_commentary8 IS NOT NULL
15124: ) )
15125: THEN
15126: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15125: THEN
15126: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15127: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary8' );
15128: FND_MSG_PUB.ADD;
15129: x_return_status := FND_API.G_RET_STS_ERROR;
15130: END IF;
15131:
15132: IF ( p_create_update_flag = 'C' AND
15133: p_organization_rec.credit_score_commentary9 IS NOT NULL AND
15130: END IF;
15131:
15132: IF ( p_create_update_flag = 'C' AND
15133: p_organization_rec.credit_score_commentary9 IS NOT NULL AND
15134: p_organization_rec.credit_score_commentary9 <> FND_API.G_MISS_CHAR )
15135: OR
15136: ( p_create_update_flag = 'U' AND
15137: ( p_organization_rec.credit_score_commentary9 = FND_API.G_MISS_CHAR OR
15138: p_organization_rec.credit_score_commentary9 IS NOT NULL
15133: p_organization_rec.credit_score_commentary9 IS NOT NULL AND
15134: p_organization_rec.credit_score_commentary9 <> FND_API.G_MISS_CHAR )
15135: OR
15136: ( p_create_update_flag = 'U' AND
15137: ( p_organization_rec.credit_score_commentary9 = FND_API.G_MISS_CHAR OR
15138: p_organization_rec.credit_score_commentary9 IS NOT NULL
15139: ) )
15140: THEN
15141: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15140: THEN
15141: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15142: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary9' );
15143: FND_MSG_PUB.ADD;
15144: x_return_status := FND_API.G_RET_STS_ERROR;
15145: END IF;
15146:
15147: IF ( p_create_update_flag = 'C' AND
15148: p_organization_rec.credit_score_commentary10 IS NOT NULL AND
15145: END IF;
15146:
15147: IF ( p_create_update_flag = 'C' AND
15148: p_organization_rec.credit_score_commentary10 IS NOT NULL AND
15149: p_organization_rec.credit_score_commentary10 <> FND_API.G_MISS_CHAR )
15150: OR
15151: ( p_create_update_flag = 'U' AND
15152: ( p_organization_rec.credit_score_commentary10 = FND_API.G_MISS_CHAR OR
15153: p_organization_rec.credit_score_commentary10 IS NOT NULL
15148: p_organization_rec.credit_score_commentary10 IS NOT NULL AND
15149: p_organization_rec.credit_score_commentary10 <> FND_API.G_MISS_CHAR )
15150: OR
15151: ( p_create_update_flag = 'U' AND
15152: ( p_organization_rec.credit_score_commentary10 = FND_API.G_MISS_CHAR OR
15153: p_organization_rec.credit_score_commentary10 IS NOT NULL
15154: ) )
15155: THEN
15156: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15155: THEN
15156: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15157: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary10' );
15158: FND_MSG_PUB.ADD;
15159: x_return_status := FND_API.G_RET_STS_ERROR;
15160: END IF;
15161:
15162: IF ( p_create_update_flag = 'C' AND
15163: p_organization_rec.credit_score_date IS NOT NULL AND
15160: END IF;
15161:
15162: IF ( p_create_update_flag = 'C' AND
15163: p_organization_rec.credit_score_date IS NOT NULL AND
15164: p_organization_rec.credit_score_date <> FND_API.G_MISS_DATE )
15165: OR
15166: ( p_create_update_flag = 'U' AND
15167: ( p_organization_rec.credit_score_date = FND_API.G_MISS_DATE OR
15168: p_organization_rec.credit_score_date IS NOT NULL
15163: p_organization_rec.credit_score_date IS NOT NULL AND
15164: p_organization_rec.credit_score_date <> FND_API.G_MISS_DATE )
15165: OR
15166: ( p_create_update_flag = 'U' AND
15167: ( p_organization_rec.credit_score_date = FND_API.G_MISS_DATE OR
15168: p_organization_rec.credit_score_date IS NOT NULL
15169: ) )
15170: THEN
15171: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15170: THEN
15171: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15172: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_date' );
15173: FND_MSG_PUB.ADD;
15174: x_return_status := FND_API.G_RET_STS_ERROR;
15175: END IF;
15176:
15177: IF ( p_create_update_flag = 'C' AND
15178: p_organization_rec.credit_score_incd_default IS NOT NULL AND
15175: END IF;
15176:
15177: IF ( p_create_update_flag = 'C' AND
15178: p_organization_rec.credit_score_incd_default IS NOT NULL AND
15179: p_organization_rec.credit_score_incd_default <> FND_API.G_MISS_NUM )
15180: OR
15181: ( p_create_update_flag = 'U' AND
15182: ( p_organization_rec.credit_score_incd_default = FND_API.G_MISS_NUM OR
15183: p_organization_rec.credit_score_incd_default IS NOT NULL
15178: p_organization_rec.credit_score_incd_default IS NOT NULL AND
15179: p_organization_rec.credit_score_incd_default <> FND_API.G_MISS_NUM )
15180: OR
15181: ( p_create_update_flag = 'U' AND
15182: ( p_organization_rec.credit_score_incd_default = FND_API.G_MISS_NUM OR
15183: p_organization_rec.credit_score_incd_default IS NOT NULL
15184: ) )
15185: THEN
15186: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15185: THEN
15186: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15187: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_incd_default' );
15188: FND_MSG_PUB.ADD;
15189: x_return_status := FND_API.G_RET_STS_ERROR;
15190: END IF;
15191:
15192: IF ( p_create_update_flag = 'C' AND
15193: p_organization_rec.credit_score_natl_percentile IS NOT NULL AND
15190: END IF;
15191:
15192: IF ( p_create_update_flag = 'C' AND
15193: p_organization_rec.credit_score_natl_percentile IS NOT NULL AND
15194: p_organization_rec.credit_score_natl_percentile <> FND_API.G_MISS_NUM)
15195: OR
15196: ( p_create_update_flag = 'U' AND
15197: ( p_organization_rec.credit_score_natl_percentile = FND_API.G_MISS_NUM OR
15198: p_organization_rec.credit_score_natl_percentile IS NOT NULL
15193: p_organization_rec.credit_score_natl_percentile IS NOT NULL AND
15194: p_organization_rec.credit_score_natl_percentile <> FND_API.G_MISS_NUM)
15195: OR
15196: ( p_create_update_flag = 'U' AND
15197: ( p_organization_rec.credit_score_natl_percentile = FND_API.G_MISS_NUM OR
15198: p_organization_rec.credit_score_natl_percentile IS NOT NULL
15199: ) )
15200: THEN
15201: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15200: THEN
15201: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15202: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_natl_percentile' );
15203: FND_MSG_PUB.ADD;
15204: x_return_status := FND_API.G_RET_STS_ERROR;
15205: END IF;
15206:
15207: IF ( p_create_update_flag = 'C' AND
15208: p_organization_rec.db_rating IS NOT NULL AND
15205: END IF;
15206:
15207: IF ( p_create_update_flag = 'C' AND
15208: p_organization_rec.db_rating IS NOT NULL AND
15209: p_organization_rec.db_rating <> FND_API.G_MISS_CHAR )
15210: OR
15211: ( p_create_update_flag = 'U' AND
15212: ( p_organization_rec.db_rating = FND_API.G_MISS_CHAR OR
15213: p_organization_rec.db_rating IS NOT NULL
15208: p_organization_rec.db_rating IS NOT NULL AND
15209: p_organization_rec.db_rating <> FND_API.G_MISS_CHAR )
15210: OR
15211: ( p_create_update_flag = 'U' AND
15212: ( p_organization_rec.db_rating = FND_API.G_MISS_CHAR OR
15213: p_organization_rec.db_rating IS NOT NULL
15214: ) )
15215: THEN
15216: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15215: THEN
15216: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15217: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'db_rating' );
15218: FND_MSG_PUB.ADD;
15219: x_return_status := FND_API.G_RET_STS_ERROR;
15220: END IF;
15221:
15222: IF ( p_create_update_flag = 'C' AND
15223: p_organization_rec.debarment_ind IS NOT NULL AND
15220: END IF;
15221:
15222: IF ( p_create_update_flag = 'C' AND
15223: p_organization_rec.debarment_ind IS NOT NULL AND
15224: p_organization_rec.debarment_ind <> FND_API.G_MISS_CHAR )
15225: OR
15226: ( p_create_update_flag = 'U' AND
15227: ( p_organization_rec.debarment_ind = FND_API.G_MISS_CHAR OR
15228: p_organization_rec.debarment_ind IS NOT NULL
15223: p_organization_rec.debarment_ind IS NOT NULL AND
15224: p_organization_rec.debarment_ind <> FND_API.G_MISS_CHAR )
15225: OR
15226: ( p_create_update_flag = 'U' AND
15227: ( p_organization_rec.debarment_ind = FND_API.G_MISS_CHAR OR
15228: p_organization_rec.debarment_ind IS NOT NULL
15229: ) )
15230: THEN
15231: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15230: THEN
15231: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15232: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'debarment_ind' );
15233: FND_MSG_PUB.ADD;
15234: x_return_status := FND_API.G_RET_STS_ERROR;
15235: END IF;
15236:
15237: IF ( p_create_update_flag = 'C' AND
15238: p_organization_rec.debarments_count IS NOT NULL AND
15235: END IF;
15236:
15237: IF ( p_create_update_flag = 'C' AND
15238: p_organization_rec.debarments_count IS NOT NULL AND
15239: p_organization_rec.debarments_count <> FND_API.G_MISS_NUM )
15240: OR
15241: ( p_create_update_flag = 'U' AND
15242: ( p_organization_rec.debarments_count = FND_API.G_MISS_NUM OR
15243: p_organization_rec.debarments_count IS NOT NULL
15238: p_organization_rec.debarments_count IS NOT NULL AND
15239: p_organization_rec.debarments_count <> FND_API.G_MISS_NUM )
15240: OR
15241: ( p_create_update_flag = 'U' AND
15242: ( p_organization_rec.debarments_count = FND_API.G_MISS_NUM OR
15243: p_organization_rec.debarments_count IS NOT NULL
15244: ) )
15245: THEN
15246: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15245: THEN
15246: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15247: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'debarments_count' );
15248: FND_MSG_PUB.ADD;
15249: x_return_status := FND_API.G_RET_STS_ERROR;
15250: END IF;
15251:
15252: IF ( p_create_update_flag = 'C' AND
15253: p_organization_rec.debarments_date IS NOT NULL AND
15250: END IF;
15251:
15252: IF ( p_create_update_flag = 'C' AND
15253: p_organization_rec.debarments_date IS NOT NULL AND
15254: p_organization_rec.debarments_date <> FND_API.G_MISS_DATE )
15255: OR
15256: ( p_create_update_flag = 'U' AND
15257: ( p_organization_rec.debarments_date = FND_API.G_MISS_DATE OR
15258: p_organization_rec.debarments_date IS NOT NULL
15253: p_organization_rec.debarments_date IS NOT NULL AND
15254: p_organization_rec.debarments_date <> FND_API.G_MISS_DATE )
15255: OR
15256: ( p_create_update_flag = 'U' AND
15257: ( p_organization_rec.debarments_date = FND_API.G_MISS_DATE OR
15258: p_organization_rec.debarments_date IS NOT NULL
15259: ) )
15260: THEN
15261: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15260: THEN
15261: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15262: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'debarments_date' );
15263: FND_MSG_PUB.ADD;
15264: x_return_status := FND_API.G_RET_STS_ERROR;
15265: END IF;
15266:
15267: IF ( p_create_update_flag = 'C' AND
15268: p_organization_rec.high_credit IS NOT NULL AND
15265: END IF;
15266:
15267: IF ( p_create_update_flag = 'C' AND
15268: p_organization_rec.high_credit IS NOT NULL AND
15269: p_organization_rec.high_credit <> FND_API.G_MISS_NUM )
15270: OR
15271: ( p_create_update_flag = 'U' AND
15272: ( p_organization_rec.high_credit = FND_API.G_MISS_NUM OR
15273: p_organization_rec.high_credit IS NOT NULL
15268: p_organization_rec.high_credit IS NOT NULL AND
15269: p_organization_rec.high_credit <> FND_API.G_MISS_NUM )
15270: OR
15271: ( p_create_update_flag = 'U' AND
15272: ( p_organization_rec.high_credit = FND_API.G_MISS_NUM OR
15273: p_organization_rec.high_credit IS NOT NULL
15274: ) )
15275: THEN
15276: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15275: THEN
15276: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15277: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'high_credit' );
15278: FND_MSG_PUB.ADD;
15279: x_return_status := FND_API.G_RET_STS_ERROR;
15280: END IF;
15281:
15282: IF ( p_create_update_flag = 'C' AND
15283: p_organization_rec.maximum_credit_currency_code IS NOT NULL AND
15280: END IF;
15281:
15282: IF ( p_create_update_flag = 'C' AND
15283: p_organization_rec.maximum_credit_currency_code IS NOT NULL AND
15284: p_organization_rec.maximum_credit_currency_code <> FND_API.G_MISS_CHAR )
15285: OR
15286: ( p_create_update_flag = 'U' AND
15287: ( p_organization_rec.maximum_credit_currency_code = FND_API.G_MISS_CHAR OR
15288: p_organization_rec.maximum_credit_currency_code IS NOT NULL
15283: p_organization_rec.maximum_credit_currency_code IS NOT NULL AND
15284: p_organization_rec.maximum_credit_currency_code <> FND_API.G_MISS_CHAR )
15285: OR
15286: ( p_create_update_flag = 'U' AND
15287: ( p_organization_rec.maximum_credit_currency_code = FND_API.G_MISS_CHAR OR
15288: p_organization_rec.maximum_credit_currency_code IS NOT NULL
15289: ) )
15290: THEN
15291: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15290: THEN
15291: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15292: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'maximum_credit_currency_code' );
15293: FND_MSG_PUB.ADD;
15294: x_return_status := FND_API.G_RET_STS_ERROR;
15295: END IF;
15296:
15297: IF ( p_create_update_flag = 'C' AND
15298: p_organization_rec.maximum_credit_recommendation IS NOT NULL AND
15295: END IF;
15296:
15297: IF ( p_create_update_flag = 'C' AND
15298: p_organization_rec.maximum_credit_recommendation IS NOT NULL AND
15299: p_organization_rec.maximum_credit_recommendation <> FND_API.G_MISS_NUM)
15300: OR
15301: ( p_create_update_flag = 'U' AND
15302: ( p_organization_rec.maximum_credit_recommendation = FND_API.G_MISS_NUM OR
15303: p_organization_rec.maximum_credit_recommendation IS NOT NULL
15298: p_organization_rec.maximum_credit_recommendation IS NOT NULL AND
15299: p_organization_rec.maximum_credit_recommendation <> FND_API.G_MISS_NUM)
15300: OR
15301: ( p_create_update_flag = 'U' AND
15302: ( p_organization_rec.maximum_credit_recommendation = FND_API.G_MISS_NUM OR
15303: p_organization_rec.maximum_credit_recommendation IS NOT NULL
15304: ) )
15305: THEN
15306: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15305: THEN
15306: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15307: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'maximum_credit_recommendation' );
15308: FND_MSG_PUB.ADD;
15309: x_return_status := FND_API.G_RET_STS_ERROR;
15310: END IF;
15311:
15312: IF ( p_create_update_flag = 'C' AND
15313: p_organization_rec.paydex_norm IS NOT NULL AND
15310: END IF;
15311:
15312: IF ( p_create_update_flag = 'C' AND
15313: p_organization_rec.paydex_norm IS NOT NULL AND
15314: p_organization_rec.paydex_norm <> FND_API.G_MISS_CHAR )
15315: OR
15316: ( p_create_update_flag = 'U' AND
15317: ( p_organization_rec.paydex_norm = FND_API.G_MISS_CHAR OR
15318: p_organization_rec.paydex_norm IS NOT NULL
15313: p_organization_rec.paydex_norm IS NOT NULL AND
15314: p_organization_rec.paydex_norm <> FND_API.G_MISS_CHAR )
15315: OR
15316: ( p_create_update_flag = 'U' AND
15317: ( p_organization_rec.paydex_norm = FND_API.G_MISS_CHAR OR
15318: p_organization_rec.paydex_norm IS NOT NULL
15319: ) )
15320: THEN
15321: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15320: THEN
15321: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15322: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'paydex_norm' );
15323: FND_MSG_PUB.ADD;
15324: x_return_status := FND_API.G_RET_STS_ERROR;
15325: END IF;
15326:
15327: IF ( p_create_update_flag = 'C' AND
15328: p_organization_rec.paydex_score IS NOT NULL AND
15325: END IF;
15326:
15327: IF ( p_create_update_flag = 'C' AND
15328: p_organization_rec.paydex_score IS NOT NULL AND
15329: p_organization_rec.paydex_score <> FND_API.G_MISS_CHAR )
15330: OR
15331: ( p_create_update_flag = 'U' AND
15332: ( p_organization_rec.paydex_score = FND_API.G_MISS_CHAR OR
15333: p_organization_rec.paydex_score IS NOT NULL
15328: p_organization_rec.paydex_score IS NOT NULL AND
15329: p_organization_rec.paydex_score <> FND_API.G_MISS_CHAR )
15330: OR
15331: ( p_create_update_flag = 'U' AND
15332: ( p_organization_rec.paydex_score = FND_API.G_MISS_CHAR OR
15333: p_organization_rec.paydex_score IS NOT NULL
15334: ) )
15335: THEN
15336: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15335: THEN
15336: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15337: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'paydex_score' );
15338: FND_MSG_PUB.ADD;
15339: x_return_status := FND_API.G_RET_STS_ERROR;
15340: END IF;
15341:
15342: IF ( p_create_update_flag = 'C' AND
15343: p_organization_rec.paydex_three_months_ago IS NOT NULL AND
15340: END IF;
15341:
15342: IF ( p_create_update_flag = 'C' AND
15343: p_organization_rec.paydex_three_months_ago IS NOT NULL AND
15344: p_organization_rec.paydex_three_months_ago <> FND_API.G_MISS_CHAR )
15345: OR
15346: ( p_create_update_flag = 'U' AND
15347: ( p_organization_rec.paydex_three_months_ago = FND_API.G_MISS_CHAR OR
15348: p_organization_rec.paydex_three_months_ago IS NOT NULL
15343: p_organization_rec.paydex_three_months_ago IS NOT NULL AND
15344: p_organization_rec.paydex_three_months_ago <> FND_API.G_MISS_CHAR )
15345: OR
15346: ( p_create_update_flag = 'U' AND
15347: ( p_organization_rec.paydex_three_months_ago = FND_API.G_MISS_CHAR OR
15348: p_organization_rec.paydex_three_months_ago IS NOT NULL
15349: ) )
15350: THEN
15351: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15350: THEN
15351: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15352: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'paydex_three_months_ago' );
15353: FND_MSG_PUB.ADD;
15354: x_return_status := FND_API.G_RET_STS_ERROR;
15355: END IF;
15356:
15357: IF ( p_create_update_flag = 'C' AND
15358: p_organization_rec.failure_score IS NOT NULL AND
15355: END IF;
15356:
15357: IF ( p_create_update_flag = 'C' AND
15358: p_organization_rec.failure_score IS NOT NULL AND
15359: p_organization_rec.failure_score <> FND_API.G_MISS_CHAR )
15360: OR
15361: ( p_create_update_flag = 'U' AND
15362: ( p_organization_rec.failure_score = FND_API.G_MISS_CHAR OR
15363: p_organization_rec.failure_score IS NOT NULL
15358: p_organization_rec.failure_score IS NOT NULL AND
15359: p_organization_rec.failure_score <> FND_API.G_MISS_CHAR )
15360: OR
15361: ( p_create_update_flag = 'U' AND
15362: ( p_organization_rec.failure_score = FND_API.G_MISS_CHAR OR
15363: p_organization_rec.failure_score IS NOT NULL
15364: ) )
15365: THEN
15366: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15365: THEN
15366: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15367: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score' );
15368: FND_MSG_PUB.ADD;
15369: x_return_status := FND_API.G_RET_STS_ERROR;
15370: END IF;
15371:
15372: IF ( p_create_update_flag = 'C' AND
15373: p_organization_rec.failure_score_age IS NOT NULL AND
15370: END IF;
15371:
15372: IF ( p_create_update_flag = 'C' AND
15373: p_organization_rec.failure_score_age IS NOT NULL AND
15374: p_organization_rec.failure_score_age <> FND_API.G_MISS_NUM )
15375: OR
15376: ( p_create_update_flag = 'U' AND
15377: ( p_organization_rec.failure_score_age = FND_API.G_MISS_NUM OR
15378: p_organization_rec.failure_score_age IS NOT NULL
15373: p_organization_rec.failure_score_age IS NOT NULL AND
15374: p_organization_rec.failure_score_age <> FND_API.G_MISS_NUM )
15375: OR
15376: ( p_create_update_flag = 'U' AND
15377: ( p_organization_rec.failure_score_age = FND_API.G_MISS_NUM OR
15378: p_organization_rec.failure_score_age IS NOT NULL
15379: ) )
15380: THEN
15381: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15380: THEN
15381: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15382: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_age' );
15383: FND_MSG_PUB.ADD;
15384: x_return_status := FND_API.G_RET_STS_ERROR;
15385: END IF;
15386:
15387: IF ( p_create_update_flag = 'C' AND
15388: p_organization_rec.failure_score_class IS NOT NULL AND
15385: END IF;
15386:
15387: IF ( p_create_update_flag = 'C' AND
15388: p_organization_rec.failure_score_class IS NOT NULL AND
15389: p_organization_rec.failure_score_class <> FND_API.G_MISS_NUM )
15390: OR
15391: ( p_create_update_flag = 'U' AND
15392: ( p_organization_rec.failure_score_class = FND_API.G_MISS_NUM OR
15393: p_organization_rec.failure_score_class IS NOT NULL
15388: p_organization_rec.failure_score_class IS NOT NULL AND
15389: p_organization_rec.failure_score_class <> FND_API.G_MISS_NUM )
15390: OR
15391: ( p_create_update_flag = 'U' AND
15392: ( p_organization_rec.failure_score_class = FND_API.G_MISS_NUM OR
15393: p_organization_rec.failure_score_class IS NOT NULL
15394: ) )
15395: THEN
15396: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15395: THEN
15396: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15397: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_class' );
15398: FND_MSG_PUB.ADD;
15399: x_return_status := FND_API.G_RET_STS_ERROR;
15400: END IF;
15401:
15402: IF ( p_create_update_flag = 'C' AND
15403: p_organization_rec.failure_score_commentary IS NOT NULL AND
15400: END IF;
15401:
15402: IF ( p_create_update_flag = 'C' AND
15403: p_organization_rec.failure_score_commentary IS NOT NULL AND
15404: p_organization_rec.failure_score_commentary <> FND_API.G_MISS_CHAR )
15405: OR
15406: ( p_create_update_flag = 'U' AND
15407: ( p_organization_rec.failure_score_commentary = FND_API.G_MISS_CHAR OR
15408: p_organization_rec.failure_score_commentary IS NOT NULL
15403: p_organization_rec.failure_score_commentary IS NOT NULL AND
15404: p_organization_rec.failure_score_commentary <> FND_API.G_MISS_CHAR )
15405: OR
15406: ( p_create_update_flag = 'U' AND
15407: ( p_organization_rec.failure_score_commentary = FND_API.G_MISS_CHAR OR
15408: p_organization_rec.failure_score_commentary IS NOT NULL
15409: ) )
15410: THEN
15411: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15410: THEN
15411: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15412: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary' );
15413: FND_MSG_PUB.ADD;
15414: x_return_status := FND_API.G_RET_STS_ERROR;
15415: END IF;
15416:
15417: IF ( p_create_update_flag = 'C' AND
15418: p_organization_rec.failure_score_commentary2 IS NOT NULL AND
15415: END IF;
15416:
15417: IF ( p_create_update_flag = 'C' AND
15418: p_organization_rec.failure_score_commentary2 IS NOT NULL AND
15419: p_organization_rec.failure_score_commentary2 <> FND_API.G_MISS_CHAR )
15420: OR
15421: ( p_create_update_flag = 'U' AND
15422: ( p_organization_rec.failure_score_commentary2 = FND_API.G_MISS_CHAR OR
15423: p_organization_rec.failure_score_commentary2 IS NOT NULL
15418: p_organization_rec.failure_score_commentary2 IS NOT NULL AND
15419: p_organization_rec.failure_score_commentary2 <> FND_API.G_MISS_CHAR )
15420: OR
15421: ( p_create_update_flag = 'U' AND
15422: ( p_organization_rec.failure_score_commentary2 = FND_API.G_MISS_CHAR OR
15423: p_organization_rec.failure_score_commentary2 IS NOT NULL
15424: ) )
15425: THEN
15426: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15425: THEN
15426: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15427: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary2' );
15428: FND_MSG_PUB.ADD;
15429: x_return_status := FND_API.G_RET_STS_ERROR;
15430: END IF;
15431:
15432: IF ( p_create_update_flag = 'C' AND
15433: p_organization_rec.failure_score_commentary3 IS NOT NULL AND
15430: END IF;
15431:
15432: IF ( p_create_update_flag = 'C' AND
15433: p_organization_rec.failure_score_commentary3 IS NOT NULL AND
15434: p_organization_rec.failure_score_commentary3 <> FND_API.G_MISS_CHAR )
15435: OR
15436: ( p_create_update_flag = 'U' AND
15437: ( p_organization_rec.failure_score_commentary3 = FND_API.G_MISS_CHAR OR
15438: p_organization_rec.failure_score_commentary3 IS NOT NULL
15433: p_organization_rec.failure_score_commentary3 IS NOT NULL AND
15434: p_organization_rec.failure_score_commentary3 <> FND_API.G_MISS_CHAR )
15435: OR
15436: ( p_create_update_flag = 'U' AND
15437: ( p_organization_rec.failure_score_commentary3 = FND_API.G_MISS_CHAR OR
15438: p_organization_rec.failure_score_commentary3 IS NOT NULL
15439: ) )
15440: THEN
15441: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15440: THEN
15441: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15442: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary3' );
15443: FND_MSG_PUB.ADD;
15444: x_return_status := FND_API.G_RET_STS_ERROR;
15445: END IF;
15446:
15447: IF ( p_create_update_flag = 'C' AND
15448: p_organization_rec.failure_score_commentary4 IS NOT NULL AND
15445: END IF;
15446:
15447: IF ( p_create_update_flag = 'C' AND
15448: p_organization_rec.failure_score_commentary4 IS NOT NULL AND
15449: p_organization_rec.failure_score_commentary4 <> FND_API.G_MISS_CHAR )
15450: OR
15451: ( p_create_update_flag = 'U' AND
15452: ( p_organization_rec.failure_score_commentary4 = FND_API.G_MISS_CHAR OR
15453: p_organization_rec.failure_score_commentary4 IS NOT NULL
15448: p_organization_rec.failure_score_commentary4 IS NOT NULL AND
15449: p_organization_rec.failure_score_commentary4 <> FND_API.G_MISS_CHAR )
15450: OR
15451: ( p_create_update_flag = 'U' AND
15452: ( p_organization_rec.failure_score_commentary4 = FND_API.G_MISS_CHAR OR
15453: p_organization_rec.failure_score_commentary4 IS NOT NULL
15454: ) )
15455: THEN
15456: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15455: THEN
15456: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15457: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary4' );
15458: FND_MSG_PUB.ADD;
15459: x_return_status := FND_API.G_RET_STS_ERROR;
15460: END IF;
15461:
15462: IF ( p_create_update_flag = 'C' AND
15463: p_organization_rec.failure_score_commentary5 IS NOT NULL AND
15460: END IF;
15461:
15462: IF ( p_create_update_flag = 'C' AND
15463: p_organization_rec.failure_score_commentary5 IS NOT NULL AND
15464: p_organization_rec.failure_score_commentary5 <> FND_API.G_MISS_CHAR )
15465: OR
15466: ( p_create_update_flag = 'U' AND
15467: ( p_organization_rec.failure_score_commentary5 = FND_API.G_MISS_CHAR OR
15468: p_organization_rec.failure_score_commentary5 IS NOT NULL
15463: p_organization_rec.failure_score_commentary5 IS NOT NULL AND
15464: p_organization_rec.failure_score_commentary5 <> FND_API.G_MISS_CHAR )
15465: OR
15466: ( p_create_update_flag = 'U' AND
15467: ( p_organization_rec.failure_score_commentary5 = FND_API.G_MISS_CHAR OR
15468: p_organization_rec.failure_score_commentary5 IS NOT NULL
15469: ) )
15470: THEN
15471: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15470: THEN
15471: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15472: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary5' );
15473: FND_MSG_PUB.ADD;
15474: x_return_status := FND_API.G_RET_STS_ERROR;
15475: END IF;
15476:
15477: IF ( p_create_update_flag = 'C' AND
15478: p_organization_rec.failure_score_commentary6 IS NOT NULL AND
15475: END IF;
15476:
15477: IF ( p_create_update_flag = 'C' AND
15478: p_organization_rec.failure_score_commentary6 IS NOT NULL AND
15479: p_organization_rec.failure_score_commentary6 <> FND_API.G_MISS_CHAR )
15480: OR
15481: ( p_create_update_flag = 'U' AND
15482: ( p_organization_rec.failure_score_commentary6 = FND_API.G_MISS_CHAR OR
15483: p_organization_rec.failure_score_commentary6 IS NOT NULL
15478: p_organization_rec.failure_score_commentary6 IS NOT NULL AND
15479: p_organization_rec.failure_score_commentary6 <> FND_API.G_MISS_CHAR )
15480: OR
15481: ( p_create_update_flag = 'U' AND
15482: ( p_organization_rec.failure_score_commentary6 = FND_API.G_MISS_CHAR OR
15483: p_organization_rec.failure_score_commentary6 IS NOT NULL
15484: ) )
15485: THEN
15486: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15485: THEN
15486: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15487: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary6' );
15488: FND_MSG_PUB.ADD;
15489: x_return_status := FND_API.G_RET_STS_ERROR;
15490: END IF;
15491:
15492: IF ( p_create_update_flag = 'C' AND
15493: p_organization_rec.failure_score_commentary7 IS NOT NULL AND
15490: END IF;
15491:
15492: IF ( p_create_update_flag = 'C' AND
15493: p_organization_rec.failure_score_commentary7 IS NOT NULL AND
15494: p_organization_rec.failure_score_commentary7 <> FND_API.G_MISS_CHAR )
15495: OR
15496: ( p_create_update_flag = 'U' AND
15497: ( p_organization_rec.failure_score_commentary7 = FND_API.G_MISS_CHAR OR
15498: p_organization_rec.failure_score_commentary7 IS NOT NULL
15493: p_organization_rec.failure_score_commentary7 IS NOT NULL AND
15494: p_organization_rec.failure_score_commentary7 <> FND_API.G_MISS_CHAR )
15495: OR
15496: ( p_create_update_flag = 'U' AND
15497: ( p_organization_rec.failure_score_commentary7 = FND_API.G_MISS_CHAR OR
15498: p_organization_rec.failure_score_commentary7 IS NOT NULL
15499: ) )
15500: THEN
15501: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15500: THEN
15501: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15502: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary7' );
15503: FND_MSG_PUB.ADD;
15504: x_return_status := FND_API.G_RET_STS_ERROR;
15505: END IF;
15506:
15507: IF ( p_create_update_flag = 'C' AND
15508: p_organization_rec.failure_score_commentary8 IS NOT NULL AND
15505: END IF;
15506:
15507: IF ( p_create_update_flag = 'C' AND
15508: p_organization_rec.failure_score_commentary8 IS NOT NULL AND
15509: p_organization_rec.failure_score_commentary8 <> FND_API.G_MISS_CHAR )
15510: OR
15511: ( p_create_update_flag = 'U' AND
15512: ( p_organization_rec.failure_score_commentary8 = FND_API.G_MISS_CHAR OR
15513: p_organization_rec.failure_score_commentary8 IS NOT NULL
15508: p_organization_rec.failure_score_commentary8 IS NOT NULL AND
15509: p_organization_rec.failure_score_commentary8 <> FND_API.G_MISS_CHAR )
15510: OR
15511: ( p_create_update_flag = 'U' AND
15512: ( p_organization_rec.failure_score_commentary8 = FND_API.G_MISS_CHAR OR
15513: p_organization_rec.failure_score_commentary8 IS NOT NULL
15514: ) )
15515: THEN
15516: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15515: THEN
15516: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15517: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary8' );
15518: FND_MSG_PUB.ADD;
15519: x_return_status := FND_API.G_RET_STS_ERROR;
15520: END IF;
15521:
15522: IF ( p_create_update_flag = 'C' AND
15523: p_organization_rec.failure_score_commentary9 IS NOT NULL AND
15520: END IF;
15521:
15522: IF ( p_create_update_flag = 'C' AND
15523: p_organization_rec.failure_score_commentary9 IS NOT NULL AND
15524: p_organization_rec.failure_score_commentary9 <> FND_API.G_MISS_CHAR )
15525: OR
15526: ( p_create_update_flag = 'U' AND
15527: ( p_organization_rec.failure_score_commentary9 = FND_API.G_MISS_CHAR OR
15528: p_organization_rec.failure_score_commentary9 IS NOT NULL
15523: p_organization_rec.failure_score_commentary9 IS NOT NULL AND
15524: p_organization_rec.failure_score_commentary9 <> FND_API.G_MISS_CHAR )
15525: OR
15526: ( p_create_update_flag = 'U' AND
15527: ( p_organization_rec.failure_score_commentary9 = FND_API.G_MISS_CHAR OR
15528: p_organization_rec.failure_score_commentary9 IS NOT NULL
15529: ) )
15530: THEN
15531: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15530: THEN
15531: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15532: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary9' );
15533: FND_MSG_PUB.ADD;
15534: x_return_status := FND_API.G_RET_STS_ERROR;
15535: END IF;
15536:
15537: IF ( p_create_update_flag = 'C' AND
15538: p_organization_rec.failure_score_commentary10 IS NOT NULL AND
15535: END IF;
15536:
15537: IF ( p_create_update_flag = 'C' AND
15538: p_organization_rec.failure_score_commentary10 IS NOT NULL AND
15539: p_organization_rec.failure_score_commentary10 <> FND_API.G_MISS_CHAR )
15540: OR
15541: ( p_create_update_flag = 'U' AND
15542: ( p_organization_rec.failure_score_commentary10 = FND_API.G_MISS_CHAR OR
15543: p_organization_rec.failure_score_commentary10 IS NOT NULL
15538: p_organization_rec.failure_score_commentary10 IS NOT NULL AND
15539: p_organization_rec.failure_score_commentary10 <> FND_API.G_MISS_CHAR )
15540: OR
15541: ( p_create_update_flag = 'U' AND
15542: ( p_organization_rec.failure_score_commentary10 = FND_API.G_MISS_CHAR OR
15543: p_organization_rec.failure_score_commentary10 IS NOT NULL
15544: ) )
15545: THEN
15546: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15545: THEN
15546: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15547: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary10' );
15548: FND_MSG_PUB.ADD;
15549: x_return_status := FND_API.G_RET_STS_ERROR;
15550: END IF;
15551:
15552: IF ( p_create_update_flag = 'C' AND
15553: p_organization_rec.failure_score_date IS NOT NULL AND
15550: END IF;
15551:
15552: IF ( p_create_update_flag = 'C' AND
15553: p_organization_rec.failure_score_date IS NOT NULL AND
15554: p_organization_rec.failure_score_date <> FND_API.G_MISS_DATE )
15555: OR
15556: ( p_create_update_flag = 'U' AND
15557: ( p_organization_rec.failure_score_date = FND_API.G_MISS_DATE OR
15558: p_organization_rec.failure_score_date IS NOT NULL
15553: p_organization_rec.failure_score_date IS NOT NULL AND
15554: p_organization_rec.failure_score_date <> FND_API.G_MISS_DATE )
15555: OR
15556: ( p_create_update_flag = 'U' AND
15557: ( p_organization_rec.failure_score_date = FND_API.G_MISS_DATE OR
15558: p_organization_rec.failure_score_date IS NOT NULL
15559: ) )
15560: THEN
15561: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15560: THEN
15561: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15562: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_date' );
15563: FND_MSG_PUB.ADD;
15564: x_return_status := FND_API.G_RET_STS_ERROR;
15565: END IF;
15566:
15567: IF ( p_create_update_flag = 'C' AND
15568: p_organization_rec.failure_score_incd_default IS NOT NULL AND
15565: END IF;
15566:
15567: IF ( p_create_update_flag = 'C' AND
15568: p_organization_rec.failure_score_incd_default IS NOT NULL AND
15569: p_organization_rec.failure_score_incd_default <> FND_API.G_MISS_NUM )
15570: OR
15571: ( p_create_update_flag = 'U' AND
15572: ( p_organization_rec.failure_score_incd_default = FND_API.G_MISS_NUM OR
15573: p_organization_rec.failure_score_incd_default IS NOT NULL
15568: p_organization_rec.failure_score_incd_default IS NOT NULL AND
15569: p_organization_rec.failure_score_incd_default <> FND_API.G_MISS_NUM )
15570: OR
15571: ( p_create_update_flag = 'U' AND
15572: ( p_organization_rec.failure_score_incd_default = FND_API.G_MISS_NUM OR
15573: p_organization_rec.failure_score_incd_default IS NOT NULL
15574: ) )
15575: THEN
15576: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15575: THEN
15576: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15577: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_incd_default' );
15578: FND_MSG_PUB.ADD;
15579: x_return_status := FND_API.G_RET_STS_ERROR;
15580: END IF;
15581:
15582: IF ( p_create_update_flag = 'C' AND
15583: p_organization_rec.failure_score_natnl_percentile IS NOT NULL AND
15580: END IF;
15581:
15582: IF ( p_create_update_flag = 'C' AND
15583: p_organization_rec.failure_score_natnl_percentile IS NOT NULL AND
15584: p_organization_rec.failure_score_natnl_percentile <> FND_API.G_MISS_NUM)
15585: OR
15586: ( p_create_update_flag = 'U' AND
15587: ( p_organization_rec.failure_score_natnl_percentile = FND_API.G_MISS_NUM OR
15588: p_organization_rec.failure_score_natnl_percentile IS NOT NULL
15583: p_organization_rec.failure_score_natnl_percentile IS NOT NULL AND
15584: p_organization_rec.failure_score_natnl_percentile <> FND_API.G_MISS_NUM)
15585: OR
15586: ( p_create_update_flag = 'U' AND
15587: ( p_organization_rec.failure_score_natnl_percentile = FND_API.G_MISS_NUM OR
15588: p_organization_rec.failure_score_natnl_percentile IS NOT NULL
15589: ) )
15590: THEN
15591: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15590: THEN
15591: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15592: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_natnl_percentile' );
15593: FND_MSG_PUB.ADD;
15594: x_return_status := FND_API.G_RET_STS_ERROR;
15595: END IF;
15596:
15597: IF ( p_create_update_flag = 'C' AND
15598: p_organization_rec.failure_score_override_code IS NOT NULL AND
15595: END IF;
15596:
15597: IF ( p_create_update_flag = 'C' AND
15598: p_organization_rec.failure_score_override_code IS NOT NULL AND
15599: p_organization_rec.failure_score_override_code <> FND_API.G_MISS_CHAR)
15600: OR
15601: ( p_create_update_flag = 'U' AND
15602: ( p_organization_rec.failure_score_override_code = FND_API.G_MISS_CHAR OR
15603: p_organization_rec.failure_score_override_code IS NOT NULL
15598: p_organization_rec.failure_score_override_code IS NOT NULL AND
15599: p_organization_rec.failure_score_override_code <> FND_API.G_MISS_CHAR)
15600: OR
15601: ( p_create_update_flag = 'U' AND
15602: ( p_organization_rec.failure_score_override_code = FND_API.G_MISS_CHAR OR
15603: p_organization_rec.failure_score_override_code IS NOT NULL
15604: ) )
15605: THEN
15606: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15605: THEN
15606: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15607: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_override_code' );
15608: FND_MSG_PUB.ADD;
15609: x_return_status := FND_API.G_RET_STS_ERROR;
15610: END IF;
15611:
15612: IF ( p_create_update_flag = 'C' AND
15613: p_organization_rec.global_failure_score IS NOT NULL AND
15610: END IF;
15611:
15612: IF ( p_create_update_flag = 'C' AND
15613: p_organization_rec.global_failure_score IS NOT NULL AND
15614: p_organization_rec.global_failure_score <> FND_API.G_MISS_CHAR)
15615: OR
15616: ( p_create_update_flag = 'U' AND
15617: ( p_organization_rec.global_failure_score = FND_API.G_MISS_CHAR OR
15618: p_organization_rec.global_failure_score IS NOT NULL
15613: p_organization_rec.global_failure_score IS NOT NULL AND
15614: p_organization_rec.global_failure_score <> FND_API.G_MISS_CHAR)
15615: OR
15616: ( p_create_update_flag = 'U' AND
15617: ( p_organization_rec.global_failure_score = FND_API.G_MISS_CHAR OR
15618: p_organization_rec.global_failure_score IS NOT NULL
15619: ) )
15620: THEN
15621: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15620: THEN
15621: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15622: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'global_failure_score' );
15623: FND_MSG_PUB.ADD;
15624: x_return_status := FND_API.G_RET_STS_ERROR;
15625: END IF;
15626:
15627: -- Debug info.
15628: /*IF G_DEBUG THEN
15652: IF p_forward_rel_code = p_backward_rel_code THEN
15653: IF p_forward_role <> p_backward_role THEN
15654: fnd_message.set_name('AR', 'HZ_INVALID_ROLE1');
15655: fnd_msg_pub.add;
15656: x_return_status := FND_API.G_RET_STS_ERROR;
15657: END IF;
15658: END IF;
15659:
15660: IF p_forward_rel_code <> p_backward_rel_code THEN
15660: IF p_forward_rel_code <> p_backward_rel_code THEN
15661: IF p_forward_role = p_backward_role THEN
15662: fnd_message.set_name('AR', 'HZ_INVALID_ROLE2');
15663: fnd_msg_pub.add;
15664: x_return_status := FND_API.G_RET_STS_ERROR;
15665: END IF;
15666: END IF;
15667:
15668: END validate_rel_code;
15687: * p_financial_report_rec Financial report record.
15688: * p_rowid Rowid of the record (used only in update mode).
15689: * IN/OUT:
15690: * x_return_status Return status after the call. The status can
15691: * be FND_API.G_RET_STS_SUCCESS (success),
15692: * fnd_api.g_ret_sts_error (error),
15693: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
15694: *
15695: * NOTES
15688: * p_rowid Rowid of the record (used only in update mode).
15689: * IN/OUT:
15690: * x_return_status Return status after the call. The status can
15691: * be FND_API.G_RET_STS_SUCCESS (success),
15692: * fnd_api.g_ret_sts_error (error),
15693: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
15694: *
15695: * NOTES
15696: *
15689: * IN/OUT:
15690: * x_return_status Return status after the call. The status can
15691: * be FND_API.G_RET_STS_SUCCESS (success),
15692: * fnd_api.g_ret_sts_error (error),
15693: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
15694: *
15695: * NOTES
15696: *
15697: * MODIFICATION HISTORY
15888: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
15889: --check for unique financial_report_id
15890: IF p_create_update_flag = 'C' THEN
15891: IF p_financial_report_rec.financial_report_id IS NOT NULL AND
15892: p_financial_report_rec.financial_report_id <> fnd_api.g_miss_num
15893: THEN
15894: OPEN c_dup_financial_report_id (p_financial_report_rec.financial_report_id);
15895: FETCH c_dup_financial_report_id INTO l_dummy;
15896:
15898: IF NVL(c_dup_financial_report_id%FOUND, FALSE) THEN
15899: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
15900: fnd_message.set_token('COLUMN', 'financial_report_id');
15901: fnd_msg_pub.add;
15902: x_return_status := fnd_api.g_ret_sts_error;
15903: END IF;
15904: CLOSE c_dup_financial_report_id;
15905:
15906: /*IF g_debug THEN
16011: IF p_create_update_flag = 'C'
16012: AND
16013: p_financial_report_rec.party_id IS NOT NULL
16014: AND
16015: p_financial_report_rec.party_id <> fnd_api.g_miss_num
16016: THEN
16017: BEGIN
16018: SELECT 'Y'
16019: INTO l_dummy
16026: fnd_message.set_token('FK', 'party_id');
16027: fnd_message.set_token('COLUMN', 'party_id');
16028: fnd_message.set_token('TABLE', 'hz_parties');
16029: fnd_msg_pub.add;
16030: x_return_status := fnd_api.g_ret_sts_error;
16031: END;
16032: */
16033: /*IF g_debug THEN
16034: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
16052: p_create_update_flag = 'U'
16053: If the value is NULL / G_MISS, error should be thrown in validate_mandatory only.
16054: */
16055: AND p_financial_report_rec.party_id IS NOT NULL
16056: AND p_financial_report_rec.party_id <> FND_API.G_MISS_NUM
16057: THEN
16058: --Bug 2886268: Added the following code instead of calling check_organization
16059: OPEN c_partytype;
16060:
16067: fnd_message.set_name('AR', 'HZ_API_INVALID_PARTY_TYPE');
16068: fnd_message.set_token('PARTY_ID', TO_CHAR(p_financial_report_rec.party_id));
16069: fnd_message.set_token('TYPE', 'ORGANIZATION');
16070: fnd_msg_pub.add;
16071: x_return_status := fnd_api.g_ret_sts_error;
16072: END IF;
16073: ELSE -- 3456205
16074: -- party_id has foreign key HZ_PARTIES.PARTY_ID
16075: -- However since no record found in HZ_PARTIES, therefore throw error.
16077: fnd_message.set_token('FK', 'party_id');
16078: fnd_message.set_token('COLUMN', 'party_id');
16079: fnd_message.set_token('TABLE', 'hz_parties');
16080: fnd_msg_pub.add;
16081: x_return_status := fnd_api.g_ret_sts_error;
16082: END IF;
16083: CLOSE c_partytype;
16084: --Bug 2886268: Commented out the following call as it is throwing erroneous message
16085: --check_organization(p_financial_report_rec.party_id, x_return_status);
16105: --during creation that only one of the fields is being provided.
16106:
16107: /*
16108: |IF ((nvl(p_financial_report_rec.issued_period, l_issued_period) IS NOT NULL AND
16109: | nvl(p_financial_report_rec.issued_period, l_issued_period) <> fnd_api.g_miss_char) AND
16110: | (nvl(p_financial_report_rec.report_start_date, l_report_start_date) IS NOT NULL AND
16111: | nvl(p_financial_report_rec.report_start_date, l_report_start_date) <> fnd_api.g_miss_date))
16112: |THEN
16113: | fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
16107: /*
16108: |IF ((nvl(p_financial_report_rec.issued_period, l_issued_period) IS NOT NULL AND
16109: | nvl(p_financial_report_rec.issued_period, l_issued_period) <> fnd_api.g_miss_char) AND
16110: | (nvl(p_financial_report_rec.report_start_date, l_report_start_date) IS NOT NULL AND
16111: | nvl(p_financial_report_rec.report_start_date, l_report_start_date) <> fnd_api.g_miss_date))
16112: |THEN
16113: | fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
16114: | fnd_message.set_token('COLUMN1', 'issued_period');
16115: | fnd_message.set_token('COLUMN2', 'report_start_date');
16113: | fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
16114: | fnd_message.set_token('COLUMN1', 'issued_period');
16115: | fnd_message.set_token('COLUMN2', 'report_start_date');
16116: | fnd_msg_pub.add;
16117: | x_return_status := fnd_api.g_ret_sts_error;
16118: |END IF;
16119: */
16120:
16121: --Bug Number 3148753.As both the fields are non-updateable,therefore validation
16128: (--Both the values are not set during creation.
16129: (
16130: p_financial_report_rec.issued_period IS NULL
16131: OR
16132: p_financial_report_rec.issued_period = fnd_api.g_miss_char
16133: )
16134: AND
16135: (
16136: p_financial_report_rec.report_start_date IS NULL
16134: AND
16135: (
16136: p_financial_report_rec.report_start_date IS NULL
16137: OR
16138: p_financial_report_rec.report_start_date = fnd_api.g_miss_date
16139: )
16140: /* Bug 3539597.Commented out changes incorporated in fix 3200870.
16141: | AND
16142: | (
16141: | AND
16142: | (
16143: | p_financial_report_rec.DATE_REPORT_ISSUED IS NULL
16144: | OR
16145: | p_financial_report_rec.DATE_REPORT_ISSUED = fnd_api.g_miss_date
16146: | )
16147: */
16148: )
16149: OR
16150: (--Both the values are provided during creation
16151: (
16152: p_financial_report_rec.issued_period IS NOT NULL
16153: AND
16154: p_financial_report_rec.issued_period <> fnd_api.g_miss_char
16155: )
16156: AND
16157: (
16158: p_financial_report_rec.report_start_date IS NOT NULL
16156: AND
16157: (
16158: p_financial_report_rec.report_start_date IS NOT NULL
16159: AND
16160: p_financial_report_rec.report_start_date <> fnd_api.g_miss_date
16161: )
16162: )
16163: )
16164: THEN
16165: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
16166: fnd_message.set_token('COLUMN1', 'issued_period');
16167: fnd_message.set_token('COLUMN2', 'report_start_date');
16168: fnd_msg_pub.add;
16169: x_return_status := fnd_api.g_ret_sts_error;
16170: END IF;
16171: END IF;
16172:
16173: -----------------------------------
16177: -- bug 4417943.
16178: IF p_create_update_flag = 'C' /*Bug 3456205 Both these columns are non-updateable */
16179: AND (
16180: (p_financial_report_rec.report_start_date IS NOT NULL
16181: AND p_financial_report_rec.report_start_date <> fnd_api.g_miss_date
16182: AND (p_financial_report_rec.report_end_date IS NULL OR
16183: p_financial_report_rec.report_end_date = fnd_api.g_miss_date )
16184: ) OR
16185: (p_financial_report_rec.report_end_date IS NOT NULL
16179: AND (
16180: (p_financial_report_rec.report_start_date IS NOT NULL
16181: AND p_financial_report_rec.report_start_date <> fnd_api.g_miss_date
16182: AND (p_financial_report_rec.report_end_date IS NULL OR
16183: p_financial_report_rec.report_end_date = fnd_api.g_miss_date )
16184: ) OR
16185: (p_financial_report_rec.report_end_date IS NOT NULL
16186: AND p_financial_report_rec.report_end_date <> fnd_api.g_miss_date
16187: AND (p_financial_report_rec.report_start_date IS NULL OR
16182: AND (p_financial_report_rec.report_end_date IS NULL OR
16183: p_financial_report_rec.report_end_date = fnd_api.g_miss_date )
16184: ) OR
16185: (p_financial_report_rec.report_end_date IS NOT NULL
16186: AND p_financial_report_rec.report_end_date <> fnd_api.g_miss_date
16187: AND (p_financial_report_rec.report_start_date IS NULL OR
16188: p_financial_report_rec.report_start_date = fnd_api.g_miss_date)
16189: ))
16190: THEN
16184: ) OR
16185: (p_financial_report_rec.report_end_date IS NOT NULL
16186: AND p_financial_report_rec.report_end_date <> fnd_api.g_miss_date
16187: AND (p_financial_report_rec.report_start_date IS NULL OR
16188: p_financial_report_rec.report_start_date = fnd_api.g_miss_date)
16189: ))
16190: THEN
16191: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION3');
16192: fnd_message.set_token('COLUMN1', 'report_start_date');
16191: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION3');
16192: fnd_message.set_token('COLUMN1', 'report_start_date');
16193: fnd_message.set_token('COLUMN2', 'report_end_date');
16194: fnd_msg_pub.add;
16195: x_return_status := fnd_api.g_ret_sts_error;
16196: END IF;
16197:
16198: --Bug 2888670: Modified the end_date validation as per V1 validation.
16199: --If report_start_date is provided, then it must be less than or equal
16200: --to report_end_date.
16201:
16202: IF p_create_update_flag = 'C' THEN
16203: IF p_financial_report_rec.report_end_date is NOT NULL AND
16204: p_financial_report_rec.report_end_date <> FND_API.G_MISS_DATE THEN
16205: if (p_financial_report_rec.report_end_date
16206: < p_financial_report_rec.report_start_date
16207: ) THEN
16208: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16206: < p_financial_report_rec.report_start_date
16207: ) THEN
16208: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16209: FND_MSG_PUB.ADD;
16210: x_return_status := FND_API.G_RET_STS_ERROR;
16211: end if;
16212: END IF;
16213: END IF;
16214:
16217:
16218: -- compare end_date with database data and user passed data.
16219: ELSIF p_create_update_flag = 'U' THEN
16220: if (p_financial_report_rec.report_end_date is NOT NULL AND
16221: p_financial_report_rec.report_end_date <> FND_API.G_MISS_DATE) THEN
16222: if p_financial_report_rec.report_start_date is NOT NULL AND
16223: p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE then
16224: if p_financial_report_rec.report_end_date
16225: < p_financial_report_rec.report_start_date then
16219: ELSIF p_create_update_flag = 'U' THEN
16220: if (p_financial_report_rec.report_end_date is NOT NULL AND
16221: p_financial_report_rec.report_end_date <> FND_API.G_MISS_DATE) THEN
16222: if p_financial_report_rec.report_start_date is NOT NULL AND
16223: p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE then
16224: if p_financial_report_rec.report_end_date
16225: < p_financial_report_rec.report_start_date then
16226: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16227: FND_MSG_PUB.ADD;
16224: if p_financial_report_rec.report_end_date
16225: < p_financial_report_rec.report_start_date then
16226: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16227: FND_MSG_PUB.ADD;
16228: x_return_status := FND_API.G_RET_STS_ERROR;
16229:
16230: end if;
16231: elsif ( p_financial_report_rec.report_end_date < l_report_start_date OR
16232: l_report_start_date is NULL) then
16231: elsif ( p_financial_report_rec.report_end_date < l_report_start_date OR
16232: l_report_start_date is NULL) then
16233: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16234: FND_MSG_PUB.ADD;
16235: x_return_status := FND_API.G_RET_STS_ERROR;
16236:
16237: end if;
16238: elsif (p_financial_report_rec.report_start_date is NOT NULL AND
16239: p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE) THEN
16235: x_return_status := FND_API.G_RET_STS_ERROR;
16236:
16237: end if;
16238: elsif (p_financial_report_rec.report_start_date is NOT NULL AND
16239: p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE) THEN
16240: if l_report_end_date < p_financial_report_rec.report_start_date then
16241: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16242: FND_MSG_PUB.ADD;
16243: x_return_status := FND_API.G_RET_STS_ERROR;
16239: p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE) THEN
16240: if l_report_end_date < p_financial_report_rec.report_start_date then
16241: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16242: FND_MSG_PUB.ADD;
16243: x_return_status := FND_API.G_RET_STS_ERROR;
16244:
16245: end if;
16246: end if;
16247:
16265: -- combination key is not unique, push an error onto the stack.
16266: IF NVL(c_unique_financial_report_rec%FOUND, FALSE) THEN
16267: fnd_message.set_name('AR', 'HZ_API_DUP_FIN_REPORT_REC');
16268: fnd_msg_pub.add;
16269: x_return_status := fnd_api.g_ret_sts_error;
16270: END IF;
16271: CLOSE c_unique_financial_report_rec;
16272:
16273: /*IF g_debug THEN
16756: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
16757: -- status is lookup code in lookup type REGISTRY_STATUS
16758: IF p_financial_report_rec.status IS NOT NULL
16759: AND
16760: p_financial_report_rec.status <> fnd_api.g_miss_char
16761: AND
16762: (p_create_update_flag = 'C'
16763: OR
16764: (p_create_update_flag = 'U'
16762: (p_create_update_flag = 'C'
16763: OR
16764: (p_create_update_flag = 'U'
16765: AND
16766: p_financial_report_rec.status <> NVL(l_status, fnd_api.g_miss_char)
16767: )
16768: )
16769: THEN
16770: validate_lookup (
16896: * p_financial_number_rec Financial number record.
16897: * p_rowid Rowid of the record (used only in update mode).
16898: * IN/OUT:
16899: * x_return_status Return status after the call. The status can
16900: * be FND_API.G_RET_STS_SUCCESS (success),
16901: * fnd_api.g_ret_sts_error (error),
16902: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
16903: *
16904: * NOTES
16897: * p_rowid Rowid of the record (used only in update mode).
16898: * IN/OUT:
16899: * x_return_status Return status after the call. The status can
16900: * be FND_API.G_RET_STS_SUCCESS (success),
16901: * fnd_api.g_ret_sts_error (error),
16902: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
16903: *
16904: * NOTES
16905: *
16898: * IN/OUT:
16899: * x_return_status Return status after the call. The status can
16900: * be FND_API.G_RET_STS_SUCCESS (success),
16901: * fnd_api.g_ret_sts_error (error),
16902: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
16903: *
16904: * NOTES
16905: *
16906: * MODIFICATION HISTORY
16975: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
16976: --check for unique financial_number_id
16977: IF p_create_update_flag = 'C' THEN
16978: IF p_financial_number_rec.financial_number_id IS NOT NULL AND
16979: p_financial_number_rec.financial_number_id <> fnd_api.g_miss_num
16980: THEN
16981: OPEN c_dup_financial_number_id (p_financial_number_rec.financial_number_id);
16982: FETCH c_dup_financial_number_id INTO l_dummy;
16983:
16985: IF NVL(c_dup_financial_number_id%FOUND, FALSE) THEN
16986: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
16987: fnd_message.set_token('COLUMN', 'financial_number_id');
16988: fnd_msg_pub.add;
16989: x_return_status := fnd_api.g_ret_sts_error;
16990: END IF;
16991: CLOSE c_dup_financial_number_id;
16992:
16993: /*IF g_debug THEN
17050: IF p_create_update_flag = 'C'
17051: AND
17052: p_financial_number_rec.financial_report_id IS NOT NULL
17053: AND
17054: p_financial_number_rec.financial_report_id <> fnd_api.g_miss_num
17055: THEN
17056: BEGIN
17057: --bug 3942332: selected actual_content_source from the hz_financial_reports record.
17058: --SELECT 'Y'
17068: fnd_message.set_token('FK', 'financial_report_id');
17069: fnd_message.set_token('COLUMN', 'financial_report_id');
17070: fnd_message.set_token('TABLE', 'hz_financial_reports');
17071: fnd_msg_pub.add;
17072: x_return_status := fnd_api.g_ret_sts_error;
17073: END;
17074:
17075: /*IF g_debug THEN
17076: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
17144: -- combination key is not unique, push an error onto the stack.
17145: IF NVL(c_unique_financial_number_rec%FOUND, FALSE) THEN
17146: fnd_message.set_name('AR', 'HZ_API_DUP_FIN_NUMBER_REC');
17147: fnd_msg_pub.add;
17148: x_return_status := fnd_api.g_ret_sts_error;
17149: END IF;
17150: CLOSE c_unique_financial_number_rec;
17151:
17152: /*IF g_debug THEN
17218: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
17219: -- status is lookup code in lookup type REGISTRY_STATUS
17220: IF p_financial_number_rec.status IS NOT NULL
17221: AND
17222: p_financial_number_rec.status <> fnd_api.g_miss_char
17223: AND
17224: (p_create_update_flag = 'C'
17225: OR
17226: (p_create_update_flag = 'U'
17224: (p_create_update_flag = 'C'
17225: OR
17226: (p_create_update_flag = 'U'
17227: AND
17228: p_financial_number_rec.status <> NVL(l_status, fnd_api.g_miss_char)
17229: )
17230: )
17231: THEN
17232: validate_lookup (
17296: * p_credit_rating_rec Credit rating record.
17297: * p_rowid Rowid of the record (used only in update mode).
17298: * IN/OUT:
17299: * x_return_status Return status after the call. The status can
17300: * be FND_API.G_RET_STS_SUCCESS (success),
17301: * fnd_api.g_ret_sts_error (error),
17302: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
17303: *
17304: * NOTES
17297: * p_rowid Rowid of the record (used only in update mode).
17298: * IN/OUT:
17299: * x_return_status Return status after the call. The status can
17300: * be FND_API.G_RET_STS_SUCCESS (success),
17301: * fnd_api.g_ret_sts_error (error),
17302: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
17303: *
17304: * NOTES
17305: *
17298: * IN/OUT:
17299: * x_return_status Return status after the call. The status can
17300: * be FND_API.G_RET_STS_SUCCESS (success),
17301: * fnd_api.g_ret_sts_error (error),
17302: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
17303: *
17304: * NOTES
17305: *
17306: * MODIFICATION HISTORY
17500: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
17501: --check for unique credit_rating_id
17502: IF p_create_update_flag = 'C' THEN
17503: IF p_credit_rating_rec.credit_rating_id IS NOT NULL AND
17504: p_credit_rating_rec.credit_rating_id <> fnd_api.g_miss_num
17505: THEN
17506: OPEN c_dup_credit_rating_id (p_credit_rating_rec.credit_rating_id);
17507: FETCH c_dup_credit_rating_id INTO l_dummy;
17508:
17510: IF NVL(c_dup_credit_rating_id%FOUND, FALSE) THEN
17511: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
17512: fnd_message.set_token('COLUMN', 'credit_rating_id');
17513: fnd_msg_pub.add;
17514: x_return_status := fnd_api.g_ret_sts_error;
17515: END IF;
17516: CLOSE c_dup_credit_rating_id;
17517:
17518: /*IF g_debug THEN
17596: IF p_create_update_flag = 'C'
17597: AND
17598: p_credit_rating_rec.party_id IS NOT NULL
17599: AND
17600: p_credit_rating_rec.party_id <> fnd_api.g_miss_num
17601: THEN
17602: BEGIN
17603: SELECT 'Y'
17604: INTO l_dummy
17611: fnd_message.set_token('FK', 'party_id');
17612: fnd_message.set_token('COLUMN', 'party_id');
17613: fnd_message.set_token('TABLE', 'hz_parties');
17614: fnd_msg_pub.add;
17615: x_return_status := fnd_api.g_ret_sts_error;
17616: END;
17617:
17618: /*IF g_debug THEN
17619: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
17671: | -- combination key is not unique, push an error onto the stack.
17672: | IF NVL(c_unique_credit_rating_rec%FOUND, FALSE) THEN
17673: | fnd_message.set_name('AR', 'HZ_API_DUP_CREDIT_RATING_REC');
17674: | fnd_msg_pub.add;
17675: | x_return_status := fnd_api.g_ret_sts_error;
17676: | END IF;
17677: | CLOSE c_unique_credit_rating_rec;
17678: |
17679: | IF g_debug THEN
18414: IF p_create_update_flag = 'C'
18415: AND
18416: p_credit_rating_rec.maximum_credit_currency_code IS NOT NULL
18417: AND
18418: p_credit_rating_rec.maximum_credit_currency_code <> fnd_api.g_miss_char
18419: THEN
18420: BEGIN
18421: --Bug 2898670: Changed the validation as the foreign key validation
18422: --has to be against active records
18435: fnd_message.set_token('FK', 'currency_code');
18436: fnd_message.set_token('COLUMN', 'maximum_credit_currency_code');
18437: fnd_message.set_token('TABLE', 'fnd_currencies');
18438: fnd_msg_pub.add;
18439: x_return_status := fnd_api.g_ret_sts_error;
18440: END;
18441:
18442: /*IF g_debug THEN
18443: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
18462: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
18463: -- status is lookup code in lookup type REGISTRY_STATUS
18464: IF p_credit_rating_rec.status IS NOT NULL
18465: AND
18466: p_credit_rating_rec.status <> fnd_api.g_miss_char
18467: AND
18468: (p_create_update_flag = 'C'
18469: OR
18470: (p_create_update_flag = 'U'
18468: (p_create_update_flag = 'C'
18469: OR
18470: (p_create_update_flag = 'U'
18471: AND
18472: p_credit_rating_rec.status <> NVL(l_status, fnd_api.g_miss_char)
18473: )
18474: )
18475: THEN
18476: validate_lookup (
18508: --------------------------------------------------------
18509: -- This column should have a value within the range 0 - 100.
18510:
18511: IF p_credit_rating_rec.credit_score_natl_percentile IS NOT NULL AND
18512: p_credit_rating_rec.credit_score_natl_percentile <> FND_API.G_MISS_NUM AND
18513: (p_create_update_flag = 'C' OR
18514: (
18515: p_create_update_flag = 'U' AND
18516: p_credit_rating_rec.credit_score_natl_percentile <> l_credit_score_natl_percentile
18523: FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_PERCENTAGE');
18524: FND_MESSAGE.SET_TOKEN('INVALIDVAL', p_credit_rating_rec.credit_score_natl_percentile);
18525: FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'credit_score_natl_percentile');
18526: FND_MSG_PUB.ADD;
18527: x_return_status := fnd_api.g_ret_sts_error;
18528: END IF;
18529: END IF;
18530:
18531: ----------------------------------------------------------
18533: ----------------------------------------------------------
18534: -- This column should have a value within the range 0 - 100.
18535:
18536: IF p_credit_rating_rec.failure_score_natnl_percentile IS NOT NULL AND
18537: p_credit_rating_rec.failure_score_natnl_percentile <> FND_API.G_MISS_NUM AND
18538: (
18539: p_create_update_flag = 'C' OR
18540: (
18541: p_create_update_flag = 'U' AND
18549: FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_PERCENTAGE');
18550: FND_MESSAGE.SET_TOKEN('INVALIDVAL', p_credit_rating_rec.failure_score_natnl_percentile);
18551: FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'failure_score_natnl_percentile');
18552: FND_MSG_PUB.ADD;
18553: x_return_status := fnd_api.g_ret_sts_error;
18554: END IF;
18555: END IF;
18556:
18557:
18687: END IF;
18688:
18689: -- created_by_module is lookup code in lookup type HZ_CREATED_BY_MODULES
18690: IF p_created_by_module IS NOT NULL AND
18691: p_created_by_module <> fnd_api.g_miss_char AND
18692: (p_create_update_flag = 'C' OR
18693: (p_create_update_flag = 'U' AND
18694: (p_old_created_by_module IS NULL OR
18695: p_created_by_module <> p_old_created_by_module)))