[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
6446: (
6447: p_create_update_flag = 'C' OR
6448: (
6449: p_create_update_flag = 'U' AND
6450: p_party_site_rec.global_location_number <> NVL(l_global_location_number, fnd_api.g_miss_char)
6451: )
6452: )
6453: THEN
6454: validate_global_loc_num(
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,
6749: -- validate combination of party_site_id and site_use_type
6750: ----------------------------------------------------------
6751:
6752: -- combination of party_site_id and site_use_type is unique
6753: if p_party_site_use_rec.status <> 'I' or (p_create_update_flag = 'C' and (p_party_site_use_rec.status is null or p_party_site_use_rec.status = fnd_api.g_miss_char ))
6754: -- bug 8506794, BO API allows to pass in multiple site uses at same time such as 'A', 'I', null for status
6755: then
6756: BEGIN
6757: SELECT 'Y'
6760: -- Bug 3988537.
6761: WHERE PARTY_SITE_ID = nvl(p_party_site_use_rec.party_site_id,l_party_site_id)
6762: AND SITE_USE_TYPE = nvl(p_party_site_use_rec.site_use_type,l_site_use_type)
6763: AND STATUS = 'A'
6764: AND PARTY_SITE_USE_ID <> NVL(p_party_site_use_rec.party_site_use_id, fnd_api.g_miss_num);
6765:
6766: fnd_message.set_name('AR', 'HZ_API_UNIQUE_SITE_USE_TYPE');
6767: fnd_msg_pub.add;
6768: x_return_status := fnd_api.g_ret_sts_error;
6764: AND PARTY_SITE_USE_ID <> NVL(p_party_site_use_rec.party_site_use_id, fnd_api.g_miss_num);
6765:
6766: fnd_message.set_name('AR', 'HZ_API_UNIQUE_SITE_USE_TYPE');
6767: fnd_msg_pub.add;
6768: x_return_status := fnd_api.g_ret_sts_error;
6769:
6770: EXCEPTION
6771: WHEN NO_DATA_FOUND THEN
6772: NULL;
6802: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
6803: -- status is lookup code in lookup type REGISTRY_STATUS
6804: IF p_party_site_use_rec.status IS NOT NULL
6805: AND
6806: p_party_site_use_rec.status <> fnd_api.g_miss_char
6807: AND
6808: (p_create_update_flag = 'C'
6809: OR
6810: (p_create_update_flag = 'U'
6808: (p_create_update_flag = 'C'
6809: OR
6810: (p_create_update_flag = 'U'
6811: AND
6812: p_party_site_use_rec.status <> NVL(l_status, fnd_api.g_miss_char)
6813: )
6814: )
6815: THEN
6816: validate_lookup (
6868: FND_MESSAGE.SET_TOKEN('ENTITY','Party Site Use');
6869: -- FND_MESSAGE.SET_TOKEN('COLUMN1','PRIMARY_PER_TYPE');
6870: -- FND_MESSAGE.SET_TOKEN('COLUMN2','STATUS');
6871: FND_MSG_PUB.ADD;
6872: x_return_status := FND_API.G_RET_STS_ERROR;
6873: END IF;
6874:
6875:
6876: -- Debug info.
6908: * p_org_contact_rec Org contact record.
6909: * p_rowid Rowid of the record (used only in update mode).
6910: * IN/OUT:
6911: * x_return_status Return status after the call. The status can
6912: * be FND_API.G_RET_STS_SUCCESS (success),
6913: * fnd_api.g_ret_sts_error (error),
6914: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6915: *
6916: * NOTES
6909: * p_rowid Rowid of the record (used only in update mode).
6910: * IN/OUT:
6911: * x_return_status Return status after the call. The status can
6912: * be FND_API.G_RET_STS_SUCCESS (success),
6913: * fnd_api.g_ret_sts_error (error),
6914: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6915: *
6916: * NOTES
6917: *
6910: * IN/OUT:
6911: * x_return_status Return status after the call. The status can
6912: * be FND_API.G_RET_STS_SUCCESS (success),
6913: * fnd_api.g_ret_sts_error (error),
6914: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6915: *
6916: * NOTES
6917: *
6918: * MODIFICATION HISTORY
7004:
7005: -- title is lookup code in lookup type CONTACT_TITLE
7006: IF p_org_contact_rec.title IS NOT NULL
7007: AND
7008: p_org_contact_rec.title <> fnd_api.g_miss_char
7009: AND
7010: (p_create_update_flag = 'C'
7011: OR
7012: (p_create_update_flag = 'U'
7010: (p_create_update_flag = 'C'
7011: OR
7012: (p_create_update_flag = 'U'
7013: AND
7014: p_org_contact_rec.title <> NVL(l_title, fnd_api.g_miss_char)
7015: )
7016: )
7017: THEN
7018: validate_lookup (
7043:
7044: -- job_title_code is lookup code in lookup type RESPONSIBILITY
7045: IF p_org_contact_rec.job_title_code IS NOT NULL
7046: AND
7047: p_org_contact_rec.job_title_code <> fnd_api.g_miss_char
7048: AND
7049: (p_create_update_flag = 'C'
7050: OR
7051: (p_create_update_flag = 'U'
7049: (p_create_update_flag = 'C'
7050: OR
7051: (p_create_update_flag = 'U'
7052: AND
7053: p_org_contact_rec.job_title_code <> NVL(l_job_title_code, fnd_api.g_miss_char)
7054: )
7055: )
7056: THEN
7057: validate_lookup (
7132:
7133: -- department_code is lookup code in lookup type DEPARTMENT_TYPE
7134: IF p_org_contact_rec.department_code IS NOT NULL
7135: AND
7136: p_org_contact_rec.department_code <> fnd_api.g_miss_char
7137: AND
7138: (p_create_update_flag = 'C'
7139: OR
7140: (p_create_update_flag = 'U'
7138: (p_create_update_flag = 'C'
7139: OR
7140: (p_create_update_flag = 'U'
7141: AND
7142: p_org_contact_rec.department_code <> NVL(l_department_code, fnd_api.g_miss_char)
7143: )
7144: )
7145: THEN
7146: validate_lookup (
7172: -- if party_site_id is passed, then it must be validated as
7173: -- foreign key to hz_party_sites.party_site_id
7174: IF p_org_contact_rec.party_site_id IS NOT NULL
7175: AND
7176: p_org_contact_rec.party_site_id <> fnd_api.g_miss_num
7177: THEN
7178: BEGIN
7179: SELECT 'Y'
7180: INTO l_dummy
7186: fnd_message.set_token('FK', 'party_site_id');
7187: fnd_message.set_token('COLUMN', 'party_site_id');
7188: fnd_message.set_token('TABLE', 'hz_party_sites');
7189: fnd_msg_pub.add;
7190: x_return_status := fnd_api.g_ret_sts_error;
7191: END;
7192:
7193: /*IF g_debug THEN
7194: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
7209: -- if party_site_id is passed then its party_id should be
7210: -- same as the object_id of the relationship record for the org_contact
7211: IF p_org_contact_rec.party_site_id IS NOT NULL
7212: AND
7213: p_org_contact_rec.party_site_id <> fnd_api.g_miss_num
7214: THEN
7215: BEGIN
7216: SELECT 'Y'
7217: INTO l_dummy
7221: EXCEPTION
7222: WHEN NO_DATA_FOUND THEN
7223: fnd_message.set_name('AR', 'HZ_API_PARTY_OBJECT_MISMATCH');
7224: fnd_msg_pub.add;
7225: x_return_status := fnd_api.g_ret_sts_error;
7226: END;
7227:
7228: /*IF g_debug THEN
7229: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
7288: * p_org_contact_role_rec Org contact role record.
7289: * p_rowid Rowid of the record (used only in update mode).
7290: * IN/OUT:
7291: * x_return_status Return status after the call. The status can
7292: * be FND_API.G_RET_STS_SUCCESS (success),
7293: * fnd_api.g_ret_sts_error (error),
7294: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7295: *
7296: * NOTES
7289: * p_rowid Rowid of the record (used only in update mode).
7290: * IN/OUT:
7291: * x_return_status Return status after the call. The status can
7292: * be FND_API.G_RET_STS_SUCCESS (success),
7293: * fnd_api.g_ret_sts_error (error),
7294: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7295: *
7296: * NOTES
7297: *
7290: * IN/OUT:
7291: * x_return_status Return status after the call. The status can
7292: * be FND_API.G_RET_STS_SUCCESS (success),
7293: * fnd_api.g_ret_sts_error (error),
7294: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7295: *
7296: * NOTES
7297: *
7298: * MODIFICATION HISTORY
7401: -- non-updateable.
7402: IF p_create_update_flag = 'C'
7403: AND
7404: p_org_contact_role_rec.org_contact_id IS NOT NULL
7405: AND p_org_contact_role_rec.org_contact_id <> fnd_api.g_miss_num
7406: THEN
7407: BEGIN
7408: SELECT 'Y'
7409: INTO l_dummy
7415: fnd_message.set_token('FK', 'org_contact_id');
7416: fnd_message.set_token('COLUMN', 'org_contact_id');
7417: fnd_message.set_token('TABLE', 'hz_org_contacts');
7418: fnd_msg_pub.add;
7419: x_return_status := fnd_api.g_ret_sts_error;
7420: END;
7421:
7422: /*IF g_debug THEN
7423: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
7440: ---------------------------------
7441: /****Logical APIs - validation not required****/
7442: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
7443: IF (p_org_contact_role_rec.orig_system is not null
7444: and p_org_contact_role_rec.orig_system <>fnd_api.g_miss_char)
7445: and (p_org_contact_role_rec.orig_system_reference is not null
7446: and p_org_contact_role_rec.orig_system_reference <>fnd_api.g_miss_char)
7447: and p_create_update_flag = 'U'
7448:
7442: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
7443: IF (p_org_contact_role_rec.orig_system is not null
7444: and p_org_contact_role_rec.orig_system <>fnd_api.g_miss_char)
7445: and (p_org_contact_role_rec.orig_system_reference is not null
7446: and p_org_contact_role_rec.orig_system_reference <>fnd_api.g_miss_char)
7447: and p_create_update_flag = 'U'
7448:
7449: then
7450: hz_orig_system_ref_pub.get_owner_table_id
7453: p_owner_table_name => 'HZ_ORG_CONTACT_ROLES',
7454: x_owner_table_id => l_mosr_owner_table_id,
7455: x_return_status => l_temp_return_status);
7456:
7457: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
7458: l_mosr_owner_table_id= nvl(p_org_contact_role_rec.org_contact_role_id,l_mosr_owner_table_id))
7459: THEN
7460: l_validate_osr := 'N';
7461: -- if we can get owner_table_id based on osr and os in mosr table,
7466: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
7467: -- x_return_status. Set x_return_status to error, ONLY if there is error.
7468: -- In case of success, leave it to carry over previous value as before this call.
7469: -- Fix for Bug 5498116 (29-AUG-2006)
7470: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
7471: x_return_status := l_temp_return_status;
7472: END IF;
7473:
7474: end if;
7599: IF l_role_id <> p_org_contact_role_rec.org_contact_role_id OR
7600: p_create_update_flag = 'C' THEN
7601: fnd_message.set_name('AR', 'HZ_API_UNIQUE_PRIMARY_ROLE');
7602: fnd_msg_pub.add;
7603: x_return_status := fnd_api.g_ret_sts_error;
7604: END IF;
7605: EXCEPTION
7606: WHEN NO_DATA_FOUND THEN
7607: NULL;
7633: BEGIN
7634: SELECT ORG_CONTACT_ROLE_ID
7635: INTO l_role_id
7636: FROM HZ_ORG_CONTACT_ROLES
7637: WHERE ORG_CONTACT_ID = NVL(p_org_contact_role_rec.org_contact_id, fnd_api.g_miss_num)
7638: AND ROLE_TYPE = p_org_contact_role_rec.role_type
7639: AND STATUS = 'A' -- Added: Bug#6411541
7640: AND ROWNUM = 1;
7641:
7638: AND ROLE_TYPE = p_org_contact_role_rec.role_type
7639: AND STATUS = 'A' -- Added: Bug#6411541
7640: AND ROWNUM = 1;
7641:
7642: IF l_role_id <> nvl(p_org_contact_role_rec.org_contact_role_id, fnd_api.g_miss_num) THEN
7643: fnd_message.set_name('AR', 'HZ_API_UNIQUE_ROLE_TYPE');
7644: fnd_msg_pub.add;
7645: x_return_status := fnd_api.g_ret_sts_error;
7646: END IF;
7641:
7642: IF l_role_id <> nvl(p_org_contact_role_rec.org_contact_role_id, fnd_api.g_miss_num) THEN
7643: fnd_message.set_name('AR', 'HZ_API_UNIQUE_ROLE_TYPE');
7644: fnd_msg_pub.add;
7645: x_return_status := fnd_api.g_ret_sts_error;
7646: END IF;
7647: EXCEPTION
7648: WHEN NO_DATA_FOUND THEN
7649: NULL;
7713: 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
7714:
7715: fnd_message.set_name('AR', 'HZ_API_UNIQUE_PRIMARY_ORG_CONT');
7716: fnd_msg_pub.add;
7717: x_return_status := fnd_api.g_ret_sts_error;
7718: EXCEPTION
7719: WHEN NO_DATA_FOUND THEN
7720: NULL;
7721: END;
7742: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
7743: -- status is lookup code in lookup type REGISTRY_STATUS
7744: IF p_org_contact_role_rec.status IS NOT NULL
7745: AND
7746: p_org_contact_role_rec.status <> fnd_api.g_miss_char
7747: AND
7748: (p_create_update_flag = 'C'
7749: OR
7750: (p_create_update_flag = 'U'
7748: (p_create_update_flag = 'C'
7749: OR
7750: (p_create_update_flag = 'U'
7751: AND
7752: p_org_contact_role_rec.status <> NVL(l_status, fnd_api.g_miss_char)
7753: )
7754: )
7755: THEN
7756: validate_lookup (
7830: * p_person_language_rec Person language record.
7831: * p_rowid Rowid of the record (used only in update mode).
7832: * IN/OUT:
7833: * x_return_status Return status after the call. The status can
7834: * be FND_API.G_RET_STS_SUCCESS (success),
7835: * fnd_api.g_ret_sts_error (error),
7836: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7837: *
7838: * NOTES
7831: * p_rowid Rowid of the record (used only in update mode).
7832: * IN/OUT:
7833: * x_return_status Return status after the call. The status can
7834: * be FND_API.G_RET_STS_SUCCESS (success),
7835: * fnd_api.g_ret_sts_error (error),
7836: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7837: *
7838: * NOTES
7839: *
7832: * IN/OUT:
7833: * x_return_status Return status after the call. The status can
7834: * be FND_API.G_RET_STS_SUCCESS (success),
7835: * fnd_api.g_ret_sts_error (error),
7836: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7837: *
7838: * NOTES
7839: *
7840: * MODIFICATION HISTORY
7958: IF p_create_update_flag = 'C'
7959: AND
7960: p_person_language_rec.party_id IS NOT NULL
7961: AND
7962: p_person_language_rec.party_id <> fnd_api.g_miss_num
7963: THEN
7964: BEGIN
7965: SELECT 'Y'
7966: INTO l_dummy
7973: fnd_message.set_token('FK', 'party_id');
7974: fnd_message.set_token('COLUMN', 'party_id');
7975: fnd_message.set_token('TABLE', 'hz_parties');
7976: fnd_msg_pub.add;
7977: x_return_status := fnd_api.g_ret_sts_error;
7978: END;
7979:
7980: /*IF g_debug THEN
7981: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
8042:
8043: -- language_name has foreign key fnd_languages.language_code
8044: IF p_person_language_rec.language_name IS NOT NULL
8045: AND
8046: p_person_language_rec.language_name <> fnd_api.g_miss_char
8047: THEN
8048: BEGIN
8049: SELECT 'Y'
8050: INTO l_dummy
8056: fnd_message.set_token('FK', 'language_name');
8057: fnd_message.set_token('COLUMN', 'language_code');
8058: fnd_message.set_token('TABLE', 'fnd_languages');
8059: fnd_msg_pub.add;
8060: x_return_status := fnd_api.g_ret_sts_error;
8061: END;
8062:
8063: /*IF g_debug THEN
8064: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
8081: INTO l_dummy
8082: FROM HZ_PERSON_LANGUAGE
8083: WHERE PARTY_ID = l_party_id
8084: AND LANGUAGE_NAME = p_person_language_rec.language_name
8085: AND LANGUAGE_USE_REFERENCE_ID <> NVL(p_person_language_rec.language_use_reference_id, fnd_api.g_miss_num);
8086:
8087: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
8088: fnd_message.set_token('COLUMN', 'language_name');
8089: fnd_msg_pub.add;
8086:
8087: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
8088: fnd_message.set_token('COLUMN', 'language_name');
8089: fnd_msg_pub.add;
8090: x_return_status := fnd_api.g_ret_sts_error;
8091:
8092: EXCEPTION
8093: WHEN NO_DATA_FOUND THEN
8094: NULL;
8178: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8179: -- status is lookup code in lookup type REGISTRY_STATUS
8180: IF p_person_language_rec.status IS NOT NULL
8181: AND
8182: p_person_language_rec.status <> fnd_api.g_miss_char
8183: AND
8184: (p_create_update_flag = 'C'
8185: OR
8186: (p_create_update_flag = 'U'
8184: (p_create_update_flag = 'C'
8185: OR
8186: (p_create_update_flag = 'U'
8187: AND
8188: p_person_language_rec.status <> NVL(l_status, fnd_api.g_miss_char)
8189: )
8190: )
8191: THEN
8192: validate_lookup (
8228: -- the status for a primary language was updated from 'A' to 'I'.
8229:
8230: /*
8231: IF p_person_language_rec.primary_language_indicator is NOT NULL
8232: AND p_person_language_rec.primary_language_indicator <> fnd_api.g_miss_char
8233: AND (p_create_update_flag = 'C'
8234: OR (p_create_update_flag = 'U'
8235: AND p_person_language_rec.primary_language_indicator <>
8236: NVL(l_primary_language_indicator, fnd_api.g_miss_char)))
8232: AND p_person_language_rec.primary_language_indicator <> fnd_api.g_miss_char
8233: AND (p_create_update_flag = 'C'
8234: OR (p_create_update_flag = 'U'
8235: AND p_person_language_rec.primary_language_indicator <>
8236: NVL(l_primary_language_indicator, fnd_api.g_miss_char)))
8237: THEN
8238: SELECT DECODE(p_person_language_rec.primary_language_indicator,
8239: 'Y', DECODE(p_person_language_rec.status,
8240: 'I', 'N',
8248: fnd_message.set_token('ENTITY', 'language');
8249: -- fnd_message.set_token('COLUMN1', 'primary_language_indicator');
8250: -- fnd_message.set_token('COLUMN2', 'status');
8251: fnd_msg_pub.add;
8252: x_return_status := FND_API.G_RET_STS_ERROR;
8253: END IF;
8254:
8255: */
8256:
8264: fnd_message.set_token('ENTITY', 'Language');
8265: -- fnd_message.set_token('COLUMN1', 'primary_language_indicator');
8266: -- fnd_message.set_token('COLUMN2', 'status');
8267: fnd_msg_pub.add;
8268: x_return_status := FND_API.G_RET_STS_ERROR;
8269:
8270: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
8271: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
8272: p_message=>'If the primary_language_indicator is set to Yes, the status column should not be Inactive.' ||
8283: fnd_message.set_token('ENTITY', 'Language');
8284: --fnd_message.set_token('COLUMN1', 'primary_language_indicator');
8285: -- fnd_message.set_token('COLUMN2', 'status');
8286: fnd_msg_pub.add;
8287: x_return_status := FND_API.G_RET_STS_ERROR;
8288:
8289: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
8290: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
8291: p_message=>'If the primary_language_indicator is set to Yes, the status column should not be Inactive.' ||
8353: * p_citizenship_rec Citizenship record.
8354: * p_rowid Rowid of the record (used only in update mode).
8355: * IN/OUT:
8356: * x_return_status Return status after the call. The status can
8357: * be FND_API.G_RET_STS_SUCCESS (success),
8358: * fnd_api.g_ret_sts_error (error),
8359: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8360: *
8361: * NOTES
8354: * p_rowid Rowid of the record (used only in update mode).
8355: * IN/OUT:
8356: * x_return_status Return status after the call. The status can
8357: * be FND_API.G_RET_STS_SUCCESS (success),
8358: * fnd_api.g_ret_sts_error (error),
8359: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8360: *
8361: * NOTES
8362: *
8355: * IN/OUT:
8356: * x_return_status Return status after the call. The status can
8357: * be FND_API.G_RET_STS_SUCCESS (success),
8358: * fnd_api.g_ret_sts_error (error),
8359: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8360: *
8361: * NOTES
8362: *
8363: * MODIFICATION HISTORY
8433: -- from sequence by table handler.
8434:
8435: IF p_create_update_flag = 'C' THEN
8436: IF p_citizenship_rec.citizenship_id IS NOT NULL AND
8437: p_citizenship_rec.citizenship_id <> fnd_api.g_miss_num
8438: THEN
8439: OPEN citizen_cur (p_citizenship_rec.citizenship_id);
8440: FETCH citizen_cur INTO l_dummy;
8441:
8443: IF NVL(citizen_cur%FOUND, FALSE) THEN
8444: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
8445: fnd_message.set_token('COLUMN', 'citizenship_id');
8446: fnd_msg_pub.add;
8447: x_return_status := fnd_api.g_ret_sts_error;
8448: END IF;
8449: CLOSE citizen_cur;
8450:
8451: /*IF g_debug THEN
8557: IF p_create_update_flag = 'C'
8558: AND
8559: p_citizenship_rec.party_id IS NOT NULL
8560: AND
8561: p_citizenship_rec.party_id <> fnd_api.g_miss_num
8562: THEN
8563: BEGIN
8564: SELECT 'Y'
8565: INTO l_dummy
8573: -- fnd_message.set_token('FK', 'party_id');
8574: fnd_message.set_token('TABLE_NAME', 'HZ_CITIZENSHIP');
8575: fnd_message.set_token('PARTY_ID_COL', 'party_id');
8576: fnd_msg_pub.add;
8577: x_return_status := fnd_api.g_ret_sts_error;
8578: END;
8579:
8580: /*IF g_debug THEN
8581: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
8680: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8681: -- status is lookup code in lookup type REGISTRY_STATUS
8682: IF p_citizenship_rec.status IS NOT NULL
8683: AND
8684: p_citizenship_rec.status <> fnd_api.g_miss_char
8685: AND
8686: (p_create_update_flag = 'C'
8687: OR
8688: (p_create_update_flag = 'U'
8686: (p_create_update_flag = 'C'
8687: OR
8688: (p_create_update_flag = 'U'
8689: AND
8690: p_citizenship_rec.status <> NVL(l_status, fnd_api.g_miss_char)
8691: )
8692: )
8693: THEN
8694: validate_lookup (
8770: * p_education_rec Education record.
8771: * p_rowid Rowid of the record (used only in update mode).
8772: * IN/OUT:
8773: * x_return_status Return status after the call. The status can
8774: * be FND_API.G_RET_STS_SUCCESS (success),
8775: * fnd_api.g_ret_sts_error (error),
8776: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8777: *
8778: * NOTES
8771: * p_rowid Rowid of the record (used only in update mode).
8772: * IN/OUT:
8773: * x_return_status Return status after the call. The status can
8774: * be FND_API.G_RET_STS_SUCCESS (success),
8775: * fnd_api.g_ret_sts_error (error),
8776: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8777: *
8778: * NOTES
8779: *
8772: * IN/OUT:
8773: * x_return_status Return status after the call. The status can
8774: * be FND_API.G_RET_STS_SUCCESS (success),
8775: * fnd_api.g_ret_sts_error (error),
8776: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
8777: *
8778: * NOTES
8779: *
8780: * MODIFICATION HISTORY
8877:
8878:
8879: IF p_create_update_flag = 'C' THEN
8880: IF p_education_rec.education_id IS NOT NULL AND
8881: p_education_rec.education_id <> fnd_api.g_miss_num
8882: THEN
8883: OPEN education_cur (p_education_rec.education_id);
8884: FETCH education_cur INTO l_dummy;
8885:
8887: IF NVL(education_cur%FOUND, FALSE) THEN
8888: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
8889: fnd_message.set_token('COLUMN', 'education_id');
8890: fnd_msg_pub.add;
8891: x_return_status := fnd_api.g_ret_sts_error;
8892: END IF;
8893: CLOSE education_cur;
8894:
8895: /*IF g_debug THEN
9000: IF p_create_update_flag = 'C'
9001: AND
9002: p_education_rec.party_id IS NOT NULL
9003: AND
9004: p_education_rec.party_id <> fnd_api.g_miss_num
9005: THEN
9006: BEGIN
9007: SELECT 'Y'
9008: INTO l_dummy
9015: fnd_message.set_name('AR', 'HZ_API_PARTY_NOT_PERSON');
9016: fnd_message.set_token('TABLE_NAME', 'HZ_EDUCATION');
9017: fnd_message.set_token('PARTY_ID_COL', 'party_id');
9018: fnd_msg_pub.add;
9019: x_return_status := fnd_api.g_ret_sts_error;
9020: END;
9021:
9022: /*IF g_debug THEN
9023: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9067: temp_last_date_attended := p_education_rec.last_date_attended;
9068: END IF;
9069:
9070: IF (temp_start_date_attended IS NOT NULL AND
9071: temp_start_date_attended <> FND_API.G_MISS_DATE AND
9072: temp_last_date_attended IS NOT NULL AND
9073: temp_last_date_attended <> FND_API.G_MISS_DATE) THEN
9074:
9075: validate_start_end_date (
9069:
9070: IF (temp_start_date_attended IS NOT NULL AND
9071: temp_start_date_attended <> FND_API.G_MISS_DATE AND
9072: temp_last_date_attended IS NOT NULL AND
9073: temp_last_date_attended <> FND_API.G_MISS_DATE) THEN
9074:
9075: validate_start_end_date (
9076:
9077: p_create_update_flag => p_create_update_flag,
9142: temp_school_attended_name := p_education_rec.school_attended_name;
9143: END IF;
9144:
9145: IF temp_school_party_id is NOT NULL and
9146: temp_school_party_id <> fnd_api.g_miss_num and
9147: temp_school_attended_name is NOT NULL and
9148: temp_school_attended_name <> fnd_api.g_miss_char then
9149:
9150: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9144:
9145: IF temp_school_party_id is NOT NULL and
9146: temp_school_party_id <> fnd_api.g_miss_num and
9147: temp_school_attended_name is NOT NULL and
9148: temp_school_attended_name <> fnd_api.g_miss_char then
9149:
9150: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9151: fnd_message.set_token('COLUMN1', 'school_party_id');
9152: fnd_message.set_token('COLUMN2', 'school_attended_name');
9150: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9151: fnd_message.set_token('COLUMN1', 'school_party_id');
9152: fnd_message.set_token('COLUMN2', 'school_attended_name');
9153: fnd_msg_pub.add;
9154: x_return_status := fnd_api.g_ret_sts_error;
9155: END IF;
9156:
9157: /*IF g_debug THEN
9158: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9175:
9176: -- school_party_id must exist in HZ_PARTIES
9177: IF p_education_rec.school_party_id IS NOT NULL
9178: AND
9179: p_education_rec.school_party_id <> fnd_api.g_miss_num
9180: THEN
9181: BEGIN
9182: SELECT 'Y'
9183: INTO l_dummy
9190: fnd_message.set_name('AR', 'HZ_API_PARTY_NOT_ORG');
9191: fnd_message.set_token('TABLE_NAME', 'HZ_EDUCATION');
9192: fnd_message.set_token('PARTY_ID_COL', 'school_party_id');
9193: fnd_msg_pub.add;
9194: x_return_status := fnd_api.g_ret_sts_error;
9195: END;
9196:
9197: /*IF g_debug THEN
9198: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9220: -- The validation will be called only if the value changes.
9221:
9222: IF p_education_rec.type_of_school IS NOT NULL
9223: AND
9224: p_education_rec.type_of_school <> fnd_api.g_miss_char
9225: AND
9226: (
9227: p_create_update_flag = 'C'
9228: OR
9263: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
9264: -- status is lookup code in lookup type REGISTRY_STATUS
9265: IF p_education_rec.status IS NOT NULL
9266: AND
9267: p_education_rec.status <> fnd_api.g_miss_char
9268: AND
9269: (p_create_update_flag = 'C'
9270: OR
9271: (p_create_update_flag = 'U'
9269: (p_create_update_flag = 'C'
9270: OR
9271: (p_create_update_flag = 'U'
9272: AND
9273: p_education_rec.status <> NVL(l_status, fnd_api.g_miss_char)
9274: )
9275: )
9276: THEN
9277: validate_lookup (
9354: * p_employment_history_rec Employment_history record.
9355: * p_rowid Rowid of the record (used only in update mode).
9356: * IN/OUT:
9357: * x_return_status Return status after the call. The status can
9358: * be FND_API.G_RET_STS_SUCCESS (success),
9359: * fnd_api.g_ret_sts_error (error),
9360: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9361: *
9362: * NOTES
9355: * p_rowid Rowid of the record (used only in update mode).
9356: * IN/OUT:
9357: * x_return_status Return status after the call. The status can
9358: * be FND_API.G_RET_STS_SUCCESS (success),
9359: * fnd_api.g_ret_sts_error (error),
9360: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9361: *
9362: * NOTES
9363: *
9356: * IN/OUT:
9357: * x_return_status Return status after the call. The status can
9358: * be FND_API.G_RET_STS_SUCCESS (success),
9359: * fnd_api.g_ret_sts_error (error),
9360: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9361: *
9362: * NOTES
9363: *
9364: * MODIFICATION HISTORY
9464:
9465:
9466: IF p_create_update_flag = 'C' THEN
9467: IF p_employment_history_rec.employment_history_id IS NOT NULL AND
9468: p_employment_history_rec.employment_history_id <> fnd_api.g_miss_num
9469: THEN
9470: OPEN employment_history_cur (p_employment_history_rec.employment_history_id);
9471: FETCH employment_history_cur INTO l_dummy;
9472:
9474: IF NVL(employment_history_cur%FOUND, FALSE) THEN
9475: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
9476: fnd_message.set_token('COLUMN', 'employment_history_id');
9477: fnd_msg_pub.add;
9478: x_return_status := fnd_api.g_ret_sts_error;
9479: END IF;
9480: CLOSE employment_history_cur;
9481:
9482: /*IF g_debug THEN
9588: IF p_create_update_flag = 'C'
9589: AND
9590: p_employment_history_rec.party_id IS NOT NULL
9591: AND
9592: p_employment_history_rec.party_id <> fnd_api.g_miss_num
9593: THEN
9594: BEGIN
9595: SELECT 'Y'
9596: INTO l_dummy
9603: fnd_message.set_name('AR', 'HZ_API_PARTY_NOT_PERSON');
9604: fnd_message.set_token('TABLE_NAME', 'HZ_EMPLOYMENT_HISTORY');
9605: fnd_message.set_token('PARTY_ID_COL', 'party_id');
9606: fnd_msg_pub.add;
9607: x_return_status := fnd_api.g_ret_sts_error;
9608: END;
9609:
9610: /*IF g_debug THEN
9611: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9654: temp_end_date := p_employment_history_rec.end_date;
9655: END IF;
9656:
9657: IF (temp_begin_date IS NOT NULL AND
9658: temp_begin_date <> FND_API.G_MISS_DATE AND
9659: temp_end_date IS NOT NULL AND
9660: temp_end_date <> FND_API.G_MISS_DATE) THEN
9661:
9662: validate_start_end_date (
9656:
9657: IF (temp_begin_date IS NOT NULL AND
9658: temp_begin_date <> FND_API.G_MISS_DATE AND
9659: temp_end_date IS NOT NULL AND
9660: temp_end_date <> FND_API.G_MISS_DATE) THEN
9661:
9662: validate_start_end_date (
9663:
9664: p_create_update_flag => p_create_update_flag,
9784: END IF;
9785:
9786:
9787: IF (temp_employed_as_title_code IS NOT NULL and
9788: temp_employed_as_title_code <> FND_API.G_MISS_CHAR AND
9789: temp_employed_as_title IS NOT NULL AND
9790: temp_employed_as_title <> FND_API.G_MISS_CHAR) THEN
9791:
9792: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9786:
9787: IF (temp_employed_as_title_code IS NOT NULL and
9788: temp_employed_as_title_code <> FND_API.G_MISS_CHAR AND
9789: temp_employed_as_title IS NOT NULL AND
9790: temp_employed_as_title <> FND_API.G_MISS_CHAR) THEN
9791:
9792: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9793: fnd_message.set_token('COLUMN1', 'employed_as_title');
9794: fnd_message.set_token('COLUMN2', 'employed_as_title_code');
9792: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9793: fnd_message.set_token('COLUMN1', 'employed_as_title');
9794: fnd_message.set_token('COLUMN2', 'employed_as_title_code');
9795: fnd_msg_pub.add;
9796: x_return_status := fnd_api.g_ret_sts_error;
9797:
9798: /*IF g_debug THEN
9799: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9800: 'check that employed_as_title is empty when employed_as_title_code isn t. '||
9833:
9834: -- employed_by_party_id must exist in HZ_PARTIES
9835: IF p_employment_history_rec.employed_by_party_id IS NOT NULL
9836: AND
9837: p_employment_history_rec.employed_by_party_id <> fnd_api.g_miss_num
9838: THEN
9839: BEGIN
9840: SELECT 'Y'
9841: INTO l_dummy
9848: fnd_message.set_token('FK', 'employed_by_party_id');
9849: fnd_message.set_token('COLUMN', 'party_id');
9850: fnd_message.set_token('TABLE', 'hz_parties');
9851: fnd_msg_pub.add;
9852: x_return_status := fnd_api.g_ret_sts_error;
9853: END;
9854:
9855: /*IF g_debug THEN
9856: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9897: END IF;
9898:
9899:
9900: IF (temp_employed_by_party_id IS NOT NULL and
9901: temp_employed_by_party_id <> FND_API.G_MISS_NUM AND
9902: temp_employed_by_name_company IS NOT NULL AND
9903: temp_employed_by_name_company <> FND_API.G_MISS_CHAR) THEN
9904:
9905: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9899:
9900: IF (temp_employed_by_party_id IS NOT NULL and
9901: temp_employed_by_party_id <> FND_API.G_MISS_NUM AND
9902: temp_employed_by_name_company IS NOT NULL AND
9903: temp_employed_by_name_company <> FND_API.G_MISS_CHAR) THEN
9904:
9905: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9906: fnd_message.set_token('COLUMN1', 'employed_by_name_company');
9907: fnd_message.set_token('COLUMN2', 'employed_by_party_id');
9905: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
9906: fnd_message.set_token('COLUMN1', 'employed_by_name_company');
9907: fnd_message.set_token('COLUMN2', 'employed_by_party_id');
9908: fnd_msg_pub.add;
9909: x_return_status := fnd_api.g_ret_sts_error;
9910:
9911: /*IF g_debug THEN
9912: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
9913: 'check that employed_by_name_company is empty when employed_by_party_id isn t. ' ||
9947: -- If weekly_work_hours is passed, must be greater than or equal to zero
9948: -- and less than or equal to 168.
9949:
9950: IF p_employment_history_rec.weekly_work_hours IS NOT NULL AND
9951: p_employment_history_rec.weekly_work_hours <> fnd_api.g_miss_num
9952: THEN
9953:
9954: IF p_employment_history_rec.weekly_work_hours < 0 OR
9955: p_employment_history_rec.weekly_work_hours > 168 THEN
9959: -- Bug 4226199 : This should be changed for bug 3848056
9960: fnd_message.set_token('VALUE1', '0');
9961: fnd_message.set_token('VALUE2', '168');
9962: fnd_msg_pub.add;
9963: x_return_status := fnd_api.g_ret_sts_error;
9964: END IF;
9965:
9966: /*IF g_debug THEN
9967: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
10060: temp_faculty_position_flag := p_employment_history_rec.faculty_position_flag;
10061: END IF;
10062:
10063: IF p_employment_history_rec.tenure_code IS NOT NULL AND
10064: p_employment_history_rec.tenure_code <> fnd_api.g_miss_char then
10065:
10066: IF temp_faculty_position_flag = 'Y' THEN
10067:
10068: validate_lookup (
10091: fnd_message.set_token('COLUMN2', 'tenure_code');
10092: fnd_message.set_token('VALUE1', 'N');
10093: fnd_message.set_token('VALUE2', 'null');
10094: fnd_msg_pub.add;
10095: x_return_status := fnd_api.g_ret_sts_error;
10096:
10097: END IF;
10098:
10099: /*IF g_debug THEN
10117: -- If fraction_of_tenure is passed, faculty_position_flag
10118: -- should be Y.
10119:
10120: IF p_employment_history_rec.fraction_of_tenure IS NOT NULL AND
10121: p_employment_history_rec.fraction_of_tenure <> fnd_api.g_miss_num then
10122: IF temp_faculty_position_flag = 'Y' then
10123: IF p_employment_history_rec.fraction_of_tenure < 0 OR
10124: p_employment_history_rec.fraction_of_tenure > 100 THEN
10125:
10127: fnd_message.set_token('COLUMN', 'fraction_of_tenure');
10128: fnd_message.set_token('VALUE1', '0');
10129: fnd_message.set_token('VALUE2', '100');
10130: fnd_msg_pub.add;
10131: x_return_status := fnd_api.g_ret_sts_error;
10132: END IF;
10133:
10134: /*IF g_debug THEN
10135: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
10152: fnd_message.set_token('VALUE2', 'null');
10153:
10154: fnd_message.set_token('COLUMN', 'fraction_of_tenure');
10155: fnd_msg_pub.add;
10156: x_return_status := fnd_api.g_ret_sts_error;
10157: END IF;
10158:
10159: END IF;
10160:
10179: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10180: -- status is lookup code in lookup type REGISTRY_STATUS
10181: IF p_employment_history_rec.status IS NOT NULL
10182: AND
10183: p_employment_history_rec.status <> fnd_api.g_miss_char
10184: AND
10185: (p_create_update_flag = 'C'
10186: OR
10187: (p_create_update_flag = 'U'
10185: (p_create_update_flag = 'C'
10186: OR
10187: (p_create_update_flag = 'U'
10188: AND
10189: p_employment_history_rec.status <> NVL(l_status, fnd_api.g_miss_char)
10190: )
10191: )
10192: THEN
10193: validate_lookup (
10268: * p_work_class_rec Work_class record.
10269: * p_rowid Rowid of the record (used only in update mode).
10270: * IN/OUT:
10271: * x_return_status Return status after the call. The status can
10272: * be FND_API.G_RET_STS_SUCCESS (success),
10273: * fnd_api.g_ret_sts_error (error),
10274: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10275: *
10276: * NOTES
10269: * p_rowid Rowid of the record (used only in update mode).
10270: * IN/OUT:
10271: * x_return_status Return status after the call. The status can
10272: * be FND_API.G_RET_STS_SUCCESS (success),
10273: * fnd_api.g_ret_sts_error (error),
10274: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10275: *
10276: * NOTES
10277: *
10270: * IN/OUT:
10271: * x_return_status Return status after the call. The status can
10272: * be FND_API.G_RET_STS_SUCCESS (success),
10273: * fnd_api.g_ret_sts_error (error),
10274: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10275: *
10276: * NOTES
10277: *
10278: * MODIFICATION HISTORY
10347:
10348:
10349: IF p_create_update_flag = 'C' THEN
10350: IF p_work_class_rec.work_class_id IS NOT NULL AND
10351: p_work_class_rec.work_class_id <> fnd_api.g_miss_num
10352: THEN
10353: OPEN work_class_cur (p_work_class_rec.work_class_id);
10354: FETCH work_class_cur INTO l_dummy;
10355:
10357: IF NVL(work_class_cur%FOUND, FALSE) THEN
10358: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
10359: fnd_message.set_token('COLUMN', 'work_class_id');
10360: fnd_msg_pub.add;
10361: x_return_status := fnd_api.g_ret_sts_error;
10362: END IF;
10363: CLOSE work_class_cur;
10364:
10365: /*IF g_debug THEN
10469: IF p_create_update_flag = 'C'
10470: AND
10471: p_work_class_rec.employment_history_id IS NOT NULL
10472: AND
10473: p_work_class_rec.employment_history_id <> fnd_api.g_miss_num
10474: THEN
10475: BEGIN
10476: SELECT 'Y'
10477: INTO l_dummy
10484: fnd_message.set_token('FK', 'employment_history_id');
10485: fnd_message.set_token('COLUMN', 'employment_history_id');
10486: fnd_message.set_token('TABLE', 'hz_employment_history');
10487: fnd_msg_pub.add;
10488: x_return_status := fnd_api.g_ret_sts_error;
10489: END;
10490:
10491: /*IF g_debug THEN
10492: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
10537: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10538: -- status is lookup code in lookup type REGISTRY_STATUS
10539: IF p_work_class_rec.status IS NOT NULL
10540: AND
10541: p_work_class_rec.status <> fnd_api.g_miss_char
10542: AND
10543: (p_create_update_flag = 'C'
10544: OR
10545: (p_create_update_flag = 'U'
10543: (p_create_update_flag = 'C'
10544: OR
10545: (p_create_update_flag = 'U'
10546: AND
10547: p_work_class_rec.status <> NVL(l_status, fnd_api.g_miss_char)
10548: )
10549: )
10550: THEN
10551: validate_lookup (
10627: * p_person_interest_rec person_interest record.
10628: * p_rowid Rowid of the record (used only in update mode).
10629: * IN/OUT:
10630: * x_return_status Return status after the call. The status can
10631: * be FND_API.G_RET_STS_SUCCESS (success),
10632: * fnd_api.g_ret_sts_error (error),
10633: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10634: *
10635: * NOTES
10628: * p_rowid Rowid of the record (used only in update mode).
10629: * IN/OUT:
10630: * x_return_status Return status after the call. The status can
10631: * be FND_API.G_RET_STS_SUCCESS (success),
10632: * fnd_api.g_ret_sts_error (error),
10633: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10634: *
10635: * NOTES
10636: *
10629: * IN/OUT:
10630: * x_return_status Return status after the call. The status can
10631: * be FND_API.G_RET_STS_SUCCESS (success),
10632: * fnd_api.g_ret_sts_error (error),
10633: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10634: *
10635: * NOTES
10636: *
10637: * MODIFICATION HISTORY
10709:
10710:
10711: IF p_create_update_flag = 'C' THEN
10712: IF p_person_interest_rec.person_interest_id IS NOT NULL AND
10713: p_person_interest_rec.person_interest_id <> fnd_api.g_miss_num
10714: THEN
10715: OPEN person_interest_cur (p_person_interest_rec.person_interest_id);
10716: FETCH person_interest_cur INTO l_dummy;
10717:
10719: IF NVL(person_interest_cur%FOUND, FALSE) THEN
10720: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
10721: fnd_message.set_token('COLUMN', 'person_interest_id');
10722: fnd_msg_pub.add;
10723: x_return_status := fnd_api.g_ret_sts_error;
10724: END IF;
10725: CLOSE person_interest_cur;
10726:
10727: /*IF g_debug THEN
10831: IF p_create_update_flag = 'C'
10832: AND
10833: p_person_interest_rec.party_id IS NOT NULL
10834: AND
10835: p_person_interest_rec.party_id <> fnd_api.g_miss_num
10836: THEN
10837: BEGIN
10838: SELECT 'Y'
10839: INTO l_dummy
10851: fnd_message.set_token('FK', 'party_id');
10852: fnd_message.set_token('COLUMN', 'party_id');
10853: fnd_message.set_token('TABLE', 'hz_parties');
10854: fnd_msg_pub.add;
10855: x_return_status := fnd_api.g_ret_sts_error;
10856: END;
10857:
10858: /*IF g_debug THEN
10859: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
10932: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10933: -- status is lookup code in lookup type REGISTRY_STATUS
10934: IF p_person_interest_rec.status IS NOT NULL
10935: AND
10936: p_person_interest_rec.status <> fnd_api.g_miss_char
10937: AND
10938: (p_create_update_flag = 'C'
10939: OR
10940: (p_create_update_flag = 'U'
10938: (p_create_update_flag = 'C'
10939: OR
10940: (p_create_update_flag = 'U'
10941: AND
10942: p_person_interest_rec.status <> NVL(l_status, fnd_api.g_miss_char)
10943: )
10944: )
10945: THEN
10946: validate_lookup (
11023: * p_location_rec Location record.
11024: * p_rowid Rowid of the record (used only in update mode).
11025: * IN/OUT:
11026: * x_return_status Return status after the call. The status can
11027: * be FND_API.G_RET_STS_SUCCESS (success),
11028: * fnd_api.g_ret_sts_error (error),
11029: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11030: *
11031: * NOTES
11024: * p_rowid Rowid of the record (used only in update mode).
11025: * IN/OUT:
11026: * x_return_status Return status after the call. The status can
11027: * be FND_API.G_RET_STS_SUCCESS (success),
11028: * fnd_api.g_ret_sts_error (error),
11029: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11030: *
11031: * NOTES
11032: *
11025: * IN/OUT:
11026: * x_return_status Return status after the call. The status can
11027: * be FND_API.G_RET_STS_SUCCESS (success),
11028: * fnd_api.g_ret_sts_error (error),
11029: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11030: *
11031: * NOTES
11032: *
11033: * MODIFICATION HISTORY
11264: -- profile value.
11265: /*NVL(FND_PROFILE.value('HZ_UPDATE_THIRD_PARTY_DATA'), 'N') = 'N'*/
11266: --HZ_UTILITY_V2PUB.is_purchased_content_source(db_actual_content_source) = 'Y'
11267: THEN
11268: l_return_status := FND_API.G_RET_STS_SUCCESS;
11269:
11270: validate_nonupdateable (
11271: p_column => 'orig_system_reference',
11272: p_column_value => p_location_rec.orig_system_reference,
11371: p_old_column_value => db_location_directions,
11372: x_return_status => l_return_status,
11373: p_raise_error => 'N');
11374:
11375: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
11376:
11377: HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
11378: p_actual_content_source => db_actual_content_source,
11379: p_new_actual_content_source => p_location_rec.actual_content_source,
11383: HZ_UTILITY_V2PUB.G_UPDATE_ACS := 'Y';
11384: /*
11385: FND_MESSAGE.SET_NAME('AR', 'HZ_NOTALLOW_UPDATE_THIRD_PARTY');
11386: FND_MSG_PUB.ADD;
11387: x_return_status := FND_API.G_RET_STS_ERROR;
11388: */
11389: END IF;
11390:
11391: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
11410: --------------------------
11411: -- language has foreign key fnd_languages.language_code
11412: IF p_location_rec.language IS NOT NULL
11413: AND
11414: p_location_rec.language <> fnd_api.g_miss_char
11415: THEN
11416: BEGIN
11417: SELECT 'Y'
11418: INTO l_dummy
11425: fnd_message.set_token('FK', 'language');
11426: fnd_message.set_token('COLUMN', 'language_code');
11427: fnd_message.set_token('TABLE', 'fnd_languages(installed)');
11428: fnd_msg_pub.add;
11429: x_return_status := fnd_api.g_ret_sts_error;
11430: END;
11431:
11432: /*IF g_debug THEN
11433: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
11448: -----------------------------
11449: -- timezone_id has foreign key hz_timezones.timezone_id
11450: IF p_location_rec.timezone_id IS NOT NULL
11451: AND
11452: p_location_rec.timezone_id <> fnd_api.g_miss_num
11453: THEN
11454: BEGIN
11455: /* Bug 11874705 : Changed the validation from HZ_TIMEZONES to FND_TIMEZONES_VL
11456: SELECT 'Y'
11469: fnd_message.set_token('FK', 'timezone_id');
11470: fnd_message.set_token('COLUMN', 'upgrade_tz_id');
11471: fnd_message.set_token('TABLE', 'fnd_timezones_vl');
11472: fnd_msg_pub.add;
11473: x_return_status := fnd_api.g_ret_sts_error;
11474: END;
11475:
11476: /*IF g_debug THEN
11477: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
11513: -- Added the below validations as a part of bug fix # 4967075
11514:
11515: l_vertex_taxware_installed := zx_r11i_tax_partner_pkg.TAX_VENDOR_EXTENSION;
11516:
11517: IF p_location_rec.sales_tax_geocode IS NOT NULL AND p_location_rec.sales_tax_geocode <> fnd_api.g_miss_char THEN
11518: If (zx_r11i_tax_partner_pkg.IS_GEOCODE_VALID(p_location_rec.sales_tax_geocode) = FALSE) then
11519: x_return_status := fnd_api.g_ret_sts_error;
11520: end if;
11521: END IF;
11515: l_vertex_taxware_installed := zx_r11i_tax_partner_pkg.TAX_VENDOR_EXTENSION;
11516:
11517: IF p_location_rec.sales_tax_geocode IS NOT NULL AND p_location_rec.sales_tax_geocode <> fnd_api.g_miss_char THEN
11518: If (zx_r11i_tax_partner_pkg.IS_GEOCODE_VALID(p_location_rec.sales_tax_geocode) = FALSE) then
11519: x_return_status := fnd_api.g_ret_sts_error;
11520: end if;
11521: END IF;
11522:
11523: 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
11519: x_return_status := fnd_api.g_ret_sts_error;
11520: end if;
11521: END IF;
11522:
11523: 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
11524: If (zx_r11i_tax_partner_pkg.IS_CITY_LIMIT_VALID(p_location_rec.sales_tax_inside_city_limits) = FALSE) then
11525: x_return_status := fnd_api.g_ret_sts_error;
11526: end if;
11527: END IF;
11521: END IF;
11522:
11523: 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
11524: If (zx_r11i_tax_partner_pkg.IS_CITY_LIMIT_VALID(p_location_rec.sales_tax_inside_city_limits) = FALSE) then
11525: x_return_status := fnd_api.g_ret_sts_error;
11526: end if;
11527: END IF;
11528:
11529: --------------------------
11539: tax_location_validation(p_location_rec,
11540: p_create_update_flag,
11541: x_return_status);
11542:
11543: IF x_return_status = fnd_api.g_ret_sts_error THEN
11544: RAISE FND_API.G_EXC_ERROR;
11545: END IF;
11546:
11547: -- Debug info.
11540: p_create_update_flag,
11541: x_return_status);
11542:
11543: IF x_return_status = fnd_api.g_ret_sts_error THEN
11544: RAISE FND_API.G_EXC_ERROR;
11545: END IF;
11546:
11547: -- Debug info.
11548: /*IF g_debug THEN
11571: * p_location_rec Location record.
11572: * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
11573: * IN/OUT:
11574: * x_return_status Return status after the call. The status can
11575: * be FND_API.G_RET_STS_SUCCESS (success),
11576: * fnd_api.g_ret_sts_error (error),
11577: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11578: *
11579: * NOTES
11572: * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
11573: * IN/OUT:
11574: * x_return_status Return status after the call. The status can
11575: * be FND_API.G_RET_STS_SUCCESS (success),
11576: * fnd_api.g_ret_sts_error (error),
11577: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11578: *
11579: * NOTES
11580: *
11573: * IN/OUT:
11574: * x_return_status Return status after the call. The status can
11575: * be FND_API.G_RET_STS_SUCCESS (success),
11576: * fnd_api.g_ret_sts_error (error),
11577: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11578: *
11579: * NOTES
11580: *
11581: * MODIFICATION HISTORY
11703: IF l_is_remit_to_location = 'N' and l_loc_assignment_exist = 'Y' THEN
11704:
11705: -- check if the taxable components are changed
11706: IF ( (p_location_rec.country IS NOT NULL
11707: AND p_location_rec.country <> fnd_api.g_miss_char)
11708: OR (p_location_rec.city IS NOT NULL
11709: AND p_location_rec.city <> fnd_api.g_miss_char)
11710: OR (p_location_rec.state IS NOT NULL
11711: AND p_location_rec.state <> fnd_api.g_miss_char)
11705: -- check if the taxable components are changed
11706: IF ( (p_location_rec.country IS NOT NULL
11707: AND p_location_rec.country <> fnd_api.g_miss_char)
11708: OR (p_location_rec.city IS NOT NULL
11709: AND p_location_rec.city <> fnd_api.g_miss_char)
11710: OR (p_location_rec.state IS NOT NULL
11711: AND p_location_rec.state <> fnd_api.g_miss_char)
11712: OR (p_location_rec.county IS NOT NULL
11713: AND p_location_rec.county <> fnd_api.g_miss_char)
11707: AND p_location_rec.country <> fnd_api.g_miss_char)
11708: OR (p_location_rec.city IS NOT NULL
11709: AND p_location_rec.city <> fnd_api.g_miss_char)
11710: OR (p_location_rec.state IS NOT NULL
11711: AND p_location_rec.state <> fnd_api.g_miss_char)
11712: OR (p_location_rec.county IS NOT NULL
11713: AND p_location_rec.county <> fnd_api.g_miss_char)
11714: OR (p_location_rec.province IS NOT NULL
11715: AND p_location_rec.province <> fnd_api.g_miss_char)
11709: AND p_location_rec.city <> fnd_api.g_miss_char)
11710: OR (p_location_rec.state IS NOT NULL
11711: AND p_location_rec.state <> fnd_api.g_miss_char)
11712: OR (p_location_rec.county IS NOT NULL
11713: AND p_location_rec.county <> fnd_api.g_miss_char)
11714: OR (p_location_rec.province IS NOT NULL
11715: AND p_location_rec.province <> fnd_api.g_miss_char)
11716: OR (p_location_rec.postal_code IS NOT NULL
11717: AND p_location_rec.postal_code <> fnd_api.g_miss_char)
11711: AND p_location_rec.state <> fnd_api.g_miss_char)
11712: OR (p_location_rec.county IS NOT NULL
11713: AND p_location_rec.county <> fnd_api.g_miss_char)
11714: OR (p_location_rec.province IS NOT NULL
11715: AND p_location_rec.province <> fnd_api.g_miss_char)
11716: OR (p_location_rec.postal_code IS NOT NULL
11717: AND p_location_rec.postal_code <> fnd_api.g_miss_char)
11718: OR (p_location_rec.attribute1 IS NOT NULL
11719: AND p_location_rec.attribute1 <> fnd_api.g_miss_char)
11713: AND p_location_rec.county <> fnd_api.g_miss_char)
11714: OR (p_location_rec.province IS NOT NULL
11715: AND p_location_rec.province <> fnd_api.g_miss_char)
11716: OR (p_location_rec.postal_code IS NOT NULL
11717: AND p_location_rec.postal_code <> fnd_api.g_miss_char)
11718: OR (p_location_rec.attribute1 IS NOT NULL
11719: AND p_location_rec.attribute1 <> fnd_api.g_miss_char)
11720: OR (p_location_rec.attribute2 IS NOT NULL
11721: AND p_location_rec.attribute2 <> fnd_api.g_miss_char)
11715: AND p_location_rec.province <> fnd_api.g_miss_char)
11716: OR (p_location_rec.postal_code IS NOT NULL
11717: AND p_location_rec.postal_code <> fnd_api.g_miss_char)
11718: OR (p_location_rec.attribute1 IS NOT NULL
11719: AND p_location_rec.attribute1 <> fnd_api.g_miss_char)
11720: OR (p_location_rec.attribute2 IS NOT NULL
11721: AND p_location_rec.attribute2 <> fnd_api.g_miss_char)
11722: OR (p_location_rec.attribute3 IS NOT NULL
11723: AND p_location_rec.attribute3 <> fnd_api.g_miss_char)
11717: AND p_location_rec.postal_code <> fnd_api.g_miss_char)
11718: OR (p_location_rec.attribute1 IS NOT NULL
11719: AND p_location_rec.attribute1 <> fnd_api.g_miss_char)
11720: OR (p_location_rec.attribute2 IS NOT NULL
11721: AND p_location_rec.attribute2 <> fnd_api.g_miss_char)
11722: OR (p_location_rec.attribute3 IS NOT NULL
11723: AND p_location_rec.attribute3 <> fnd_api.g_miss_char)
11724: OR (p_location_rec.attribute4 IS NOT NULL
11725: AND p_location_rec.attribute4 <> fnd_api.g_miss_char)
11719: AND p_location_rec.attribute1 <> fnd_api.g_miss_char)
11720: OR (p_location_rec.attribute2 IS NOT NULL
11721: AND p_location_rec.attribute2 <> fnd_api.g_miss_char)
11722: OR (p_location_rec.attribute3 IS NOT NULL
11723: AND p_location_rec.attribute3 <> fnd_api.g_miss_char)
11724: OR (p_location_rec.attribute4 IS NOT NULL
11725: AND p_location_rec.attribute4 <> fnd_api.g_miss_char)
11726: OR (p_location_rec.attribute5 IS NOT NULL
11727: AND p_location_rec.attribute5 <> fnd_api.g_miss_char)
11721: AND p_location_rec.attribute2 <> fnd_api.g_miss_char)
11722: OR (p_location_rec.attribute3 IS NOT NULL
11723: AND p_location_rec.attribute3 <> fnd_api.g_miss_char)
11724: OR (p_location_rec.attribute4 IS NOT NULL
11725: AND p_location_rec.attribute4 <> fnd_api.g_miss_char)
11726: OR (p_location_rec.attribute5 IS NOT NULL
11727: AND p_location_rec.attribute5 <> fnd_api.g_miss_char)
11728: OR (p_location_rec.attribute6 IS NOT NULL
11729: AND p_location_rec.attribute6 <> fnd_api.g_miss_char)
11723: AND p_location_rec.attribute3 <> fnd_api.g_miss_char)
11724: OR (p_location_rec.attribute4 IS NOT NULL
11725: AND p_location_rec.attribute4 <> fnd_api.g_miss_char)
11726: OR (p_location_rec.attribute5 IS NOT NULL
11727: AND p_location_rec.attribute5 <> fnd_api.g_miss_char)
11728: OR (p_location_rec.attribute6 IS NOT NULL
11729: AND p_location_rec.attribute6 <> fnd_api.g_miss_char)
11730: OR (p_location_rec.attribute7 IS NOT NULL
11731: AND p_location_rec.attribute7 <> fnd_api.g_miss_char)
11725: AND p_location_rec.attribute4 <> fnd_api.g_miss_char)
11726: OR (p_location_rec.attribute5 IS NOT NULL
11727: AND p_location_rec.attribute5 <> fnd_api.g_miss_char)
11728: OR (p_location_rec.attribute6 IS NOT NULL
11729: AND p_location_rec.attribute6 <> fnd_api.g_miss_char)
11730: OR (p_location_rec.attribute7 IS NOT NULL
11731: AND p_location_rec.attribute7 <> fnd_api.g_miss_char)
11732: OR (p_location_rec.attribute8 IS NOT NULL
11733: AND p_location_rec.attribute8 <> fnd_api.g_miss_char)
11727: AND p_location_rec.attribute5 <> fnd_api.g_miss_char)
11728: OR (p_location_rec.attribute6 IS NOT NULL
11729: AND p_location_rec.attribute6 <> fnd_api.g_miss_char)
11730: OR (p_location_rec.attribute7 IS NOT NULL
11731: AND p_location_rec.attribute7 <> fnd_api.g_miss_char)
11732: OR (p_location_rec.attribute8 IS NOT NULL
11733: AND p_location_rec.attribute8 <> fnd_api.g_miss_char)
11734: OR (p_location_rec.attribute9 IS NOT NULL
11735: AND p_location_rec.attribute9 <> fnd_api.g_miss_char)
11729: AND p_location_rec.attribute6 <> fnd_api.g_miss_char)
11730: OR (p_location_rec.attribute7 IS NOT NULL
11731: AND p_location_rec.attribute7 <> fnd_api.g_miss_char)
11732: OR (p_location_rec.attribute8 IS NOT NULL
11733: AND p_location_rec.attribute8 <> fnd_api.g_miss_char)
11734: OR (p_location_rec.attribute9 IS NOT NULL
11735: AND p_location_rec.attribute9 <> fnd_api.g_miss_char)
11736: OR (p_location_rec.attribute10 IS NOT NULL
11737: AND p_location_rec.attribute10 <> fnd_api.g_miss_char))
11731: AND p_location_rec.attribute7 <> fnd_api.g_miss_char)
11732: OR (p_location_rec.attribute8 IS NOT NULL
11733: AND p_location_rec.attribute8 <> fnd_api.g_miss_char)
11734: OR (p_location_rec.attribute9 IS NOT NULL
11735: AND p_location_rec.attribute9 <> fnd_api.g_miss_char)
11736: OR (p_location_rec.attribute10 IS NOT NULL
11737: AND p_location_rec.attribute10 <> fnd_api.g_miss_char))
11738: THEN
11739: BEGIN
11733: AND p_location_rec.attribute8 <> fnd_api.g_miss_char)
11734: OR (p_location_rec.attribute9 IS NOT NULL
11735: AND p_location_rec.attribute9 <> fnd_api.g_miss_char)
11736: OR (p_location_rec.attribute10 IS NOT NULL
11737: AND p_location_rec.attribute10 <> fnd_api.g_miss_char))
11738: THEN
11739: BEGIN
11740: SELECT country, city, state, county, province, postal_code,
11741: attribute1, attribute2, attribute3, attribute4, attribute5,
11758: THEN
11759: fnd_message.set_name('AR', 'AR_CUST_ADDR_HAS_TRANSACTION');
11760: --Bug 2452282 fnd_message.set_token('COLUMN', 'tax related fields');
11761: fnd_msg_pub.add;
11762: x_return_status := fnd_api.g_ret_sts_error;
11763: ELSE -- non taxable components to be modified
11764: null; -- allow updates
11765: END IF;
11766: END IF;
11803: * p_relationship_type_rec relationship type record.
11804: * p_rowid Rowid of the record (used only in update mode).
11805: * IN/OUT:
11806: * x_return_status Return status after the call. The status can
11807: * be FND_API.G_RET_STS_SUCCESS (success),
11808: * fnd_api.g_ret_sts_error (error),
11809: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11810: *
11811: * NOTES
11804: * p_rowid Rowid of the record (used only in update mode).
11805: * IN/OUT:
11806: * x_return_status Return status after the call. The status can
11807: * be FND_API.G_RET_STS_SUCCESS (success),
11808: * fnd_api.g_ret_sts_error (error),
11809: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11810: *
11811: * NOTES
11812: *
11805: * IN/OUT:
11806: * x_return_status Return status after the call. The status can
11807: * be FND_API.G_RET_STS_SUCCESS (success),
11808: * fnd_api.g_ret_sts_error (error),
11809: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
11810: *
11811: * NOTES
11812: *
11813: * MODIFICATION HISTORY
12046:
12047: -- status is lookup code in lookup type CODE_STATUS
12048: IF p_relationship_type_rec.status IS NOT NULL
12049: AND
12050: p_relationship_type_rec.status <> fnd_api.g_miss_char
12051: AND
12052: (p_create_update_flag = 'C'
12053: OR
12054: (p_create_update_flag = 'U'
12052: (p_create_update_flag = 'C'
12053: OR
12054: (p_create_update_flag = 'U'
12055: AND
12056: p_relationship_type_rec.status <> NVL(l_status, fnd_api.g_miss_char)
12057: )
12058: )
12059: THEN
12060: validate_lookup (
12105:
12106: -- create_party_flag is non-updateable if relationship records are already created based
12107: -- on the current value of the flag
12108: IF p_create_update_flag = 'U' THEN
12109: IF p_relationship_type_rec.create_party_flag <> fnd_api.g_miss_char
12110: AND
12111: p_relationship_type_rec.create_party_flag IS NOT NULL
12112: AND
12113: p_relationship_type_rec.create_party_flag <> l_create_party_flag
12129:
12130: -- update is not allowed, raise error
12131: fnd_message.set_name('AR', 'HZ_API_CPF_NON_UPDATEABLE');
12132: fnd_msg_pub.add;
12133: x_return_status := fnd_api.g_ret_sts_error;
12134:
12135: EXCEPTION
12136: WHEN NO_DATA_FOUND THEN
12137: NULL;
12155:
12156: -- update is not allowed, raise error
12157: fnd_message.set_name('AR', 'HZ_API_CPF_NON_UPDATEABLE');
12158: fnd_msg_pub.add;
12159: x_return_status := fnd_api.g_ret_sts_error;
12160:
12161: EXCEPTION
12162: WHEN NO_DATA_FOUND THEN
12163: NULL;
12481:
12482: -- subject_type has foreign key fnd_object_instance_sets.instance_set_name
12483: IF p_relationship_type_rec.subject_type IS NOT NULL
12484: AND
12485: p_relationship_type_rec.subject_type <> fnd_api.g_miss_char
12486: THEN
12487: BEGIN
12488: SELECT 'Y'
12489: INTO l_dummy
12495: fnd_message.set_token('FK', 'subject_type');
12496: fnd_message.set_token('COLUMN', 'instance_set_name');
12497: fnd_message.set_token('TABLE', 'fnd_object_instance_sets');
12498: fnd_msg_pub.add;
12499: x_return_status := fnd_api.g_ret_sts_error;
12500: END;
12501:
12502: /*IF g_debug THEN
12503: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12564:
12565: -- object_type has foreign key fnd_object_instance_sets.instance_set_name
12566: IF p_relationship_type_rec.object_type IS NOT NULL
12567: AND
12568: p_relationship_type_rec.object_type <> fnd_api.g_miss_char
12569: THEN
12570: BEGIN
12571: SELECT 'Y'
12572: INTO l_dummy
12578: fnd_message.set_token('FK', 'object_type');
12579: fnd_message.set_token('COLUMN', 'instance_set_name');
12580: fnd_message.set_token('TABLE', 'fnd_object_instance_sets');
12581: fnd_msg_pub.add;
12582: x_return_status := fnd_api.g_ret_sts_error;
12583: END;
12584:
12585: /*IF g_debug THEN
12586: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12657: | -- obtained from above query, error out NOCOPY
12658: | IF l_code <> p_relationship_type_rec.backward_rel_code then
12659: | fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION');
12660: | fnd_msg_pub.add;
12661: | x_return_status := FND_API.G_RET_STS_ERROR;
12662: | END IF;
12663: | END IF;
12664: */
12665:
12709: IF l_count <> 0
12710: THEN
12711: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION');
12712: fnd_msg_pub.add;
12713: x_return_status := FND_API.G_RET_STS_ERROR;
12714: END IF;
12715:
12716: /*IF g_debug THEN
12717: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12745:
12746: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
12747: fnd_message.set_token('COLUMN', 'relationship type, forward rel code, backward rel code, subject type, object type');
12748: fnd_msg_pub.add;
12749: x_return_status := fnd_api.g_ret_sts_error;
12750:
12751: EXCEPTION
12752: WHEN NO_DATA_FOUND THEN
12753: NULL;
12777: p_relationship_type_rec.direction_code <> 'N'
12778: THEN
12779: fnd_message.set_name('AR', 'HZ_INVALID_DIRECTION_CODE1');
12780: fnd_msg_pub.add;
12781: x_return_status := FND_API.G_RET_STS_ERROR;
12782: END IF;
12783:
12784: -- if forward_rel_code and backward_rel_code are not same then direction_flag
12785: -- cannot be 'N'
12787: AND p_relationship_type_rec.direction_code = 'N'
12788: THEN
12789: fnd_message.set_name('AR', 'HZ_INVALID_DIRECTION_CODE2');
12790: fnd_msg_pub.add;
12791: x_return_status := FND_API.G_RET_STS_ERROR;
12792: END IF;
12793:
12794: /*IF g_debug THEN
12795: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12864: fnd_message.set_token('VALUE1', 'Y(Yes)');
12865: fnd_message.set_token('COLUMN2', 'allow_circular_relationships');
12866: fnd_message.set_token('VALUE2', 'N(No)');
12867: fnd_msg_pub.add;
12868: x_return_status := FND_API.G_RET_STS_ERROR;
12869: END IF;
12870: END IF;
12871: /*IF g_debug THEN
12872: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12896: fnd_message.set_token('VALUE1', 'Y(Yes)');
12897: fnd_message.set_token('COLUMN2', 'direction_code');
12898: fnd_message.set_token('VALUE2', 'P(Parent)/C(Child)');
12899: fnd_msg_pub.add;
12900: x_return_status := FND_API.G_RET_STS_ERROR;
12901: END IF;
12902: END IF;
12903: /*IF g_debug THEN
12904: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
12927: IF l_count > 0 THEN
12928: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
12929: fnd_message.set_token('COLUMN', 'role');
12930: fnd_msg_pub.add;
12931: x_return_status := fnd_api.g_ret_sts_error;
12932: END IF;
12933: END;
12934: END IF;
12935:
12936: -- validate role/rel_code
12937:
12938: IF p_create_update_flag = 'C' THEN
12939: IF ( p_relationship_type_rec.forward_role IS NOT NULL AND
12940: p_relationship_type_rec.forward_role <> fnd_api.g_miss_char) AND
12941: ( p_relationship_type_rec.backward_role IS NOT NULL AND
12942: p_relationship_type_rec.backward_role <> fnd_api.g_miss_char) THEN
12943: validate_rel_code(
12944: p_forward_rel_code =>p_relationship_type_rec.forward_rel_code,
12938: IF p_create_update_flag = 'C' THEN
12939: IF ( p_relationship_type_rec.forward_role IS NOT NULL AND
12940: p_relationship_type_rec.forward_role <> fnd_api.g_miss_char) AND
12941: ( p_relationship_type_rec.backward_role IS NOT NULL AND
12942: p_relationship_type_rec.backward_role <> fnd_api.g_miss_char) THEN
12943: validate_rel_code(
12944: p_forward_rel_code =>p_relationship_type_rec.forward_rel_code,
12945: p_backward_rel_code =>p_relationship_type_rec.backward_rel_code,
12946: p_forward_role => p_relationship_type_rec.forward_role,
12980: fnd_message.set_token('VALUE1', 'Y(Yes)');
12981: fnd_message.set_token('COLUMN2', 'allow_relate_to_self_flag');
12982: fnd_message.set_token('VALUE2', 'N(No)');
12983: fnd_msg_pub.add;
12984: x_return_status := FND_API.G_RET_STS_ERROR;
12985: END IF;
12986: END IF;
12987: /*IF g_debug THEN
12988: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
13013: fnd_message.set_token('VALUE1', 'N(No)');
13014: fnd_message.set_token('COLUMN2', 'allow_circular_relationships');
13015: fnd_message.set_token('VALUE2', 'Y(Yes)');
13016: fnd_msg_pub.add;
13017: x_return_status := FND_API.G_RET_STS_ERROR;
13018: END IF;
13019: END IF;
13020: /*IF g_debug THEN
13021: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
13055: fnd_message.set_token('VALUE1', 'N(No)');
13056: fnd_message.set_token('COLUMN2', 'multiple_parent_allowed');
13057: fnd_message.set_token('VALUE2', 'Y(Yes)');
13058: fnd_msg_pub.add;
13059: x_return_status := FND_API.G_RET_STS_ERROR;
13060: END IF;
13061: END IF;
13062:
13063: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
13095: fnd_message.set_token('ENTITY', 'relationship type');
13096: fnd_message.set_token('COLUMN1', 'relationship_type');
13097: fnd_message.set_token('COLUMN2', 'hierarchical_flag');
13098: fnd_msg_pub.add;
13099: x_return_status := FND_API.G_RET_STS_ERROR;
13100: END IF;
13101:
13102: EXCEPTION
13103: -- if no data found, then its fine since this is the first record
13141: fnd_message.set_token('ENTITY', 'relationship type');
13142: fnd_message.set_token('COLUMN1', 'relationship_type');
13143: fnd_message.set_token('COLUMN2', 'allow_circular_relationships');
13144: fnd_msg_pub.add;
13145: x_return_status := FND_API.G_RET_STS_ERROR;
13146: END IF;
13147:
13148: EXCEPTION
13149: -- if no data found, then its fine since this is the first record
13184: fnd_message.set_token('ENTITY', 'relationship type');
13185: fnd_message.set_token('COLUMN1', 'relationship_type');
13186: fnd_message.set_token('COLUMN2', 'multiple_parent_allowed');
13187: fnd_msg_pub.add;
13188: x_return_status := FND_API.G_RET_STS_ERROR;
13189: END IF;
13190:
13191: EXCEPTION
13192: -- if no data found, then its fine since this is the first record
13310: AND (p_relationship_type_rec.subject_type <> p_relationship_type_rec.object_type))
13311: THEN
13312: fnd_message.set_name('AR', 'HZ_API_RELTYPE_INVALID');
13313: fnd_msg_pub.add;
13314: x_return_status := fnd_api.g_ret_sts_error;
13315:
13316: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
13317: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
13318: p_message=>'Same subject and object type because the relationship phrase pair has the same subject (forward) and object (backward) phrase. ' ||
13346: * p_relationship_rec Relationship record.
13347: * p_rowid Rowid of the record (used only in update mode).
13348: * IN/OUT:
13349: * x_return_status Return status after the call. The status can
13350: * be FND_API.G_RET_STS_SUCCESS (success),
13351: * fnd_api.g_ret_sts_error (error),
13352: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
13353: *
13354: * NOTES
13347: * p_rowid Rowid of the record (used only in update mode).
13348: * IN/OUT:
13349: * x_return_status Return status after the call. The status can
13350: * be FND_API.G_RET_STS_SUCCESS (success),
13351: * fnd_api.g_ret_sts_error (error),
13352: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
13353: *
13354: * NOTES
13355: *
13348: * IN/OUT:
13349: * x_return_status Return status after the call. The status can
13350: * be FND_API.G_RET_STS_SUCCESS (success),
13351: * fnd_api.g_ret_sts_error (error),
13352: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
13353: *
13354: * NOTES
13355: *
13356: * MODIFICATION HISTORY
13413: AND hr.relationship_type = l_relationship_type
13414: --AND hr.directional_flag = 'F'
13415: AND hr.status = 'A'
13416: AND hr.actual_content_source = p_data_source
13417: AND NVL(p_relationship_rec.relationship_id, fnd_api.g_miss_num) <> hr.relationship_id;
13418:
13419: -- Bug 3294936 : Comment rownum = 1 so that the cursor picks all records
13420: -- with same relationship type between same subject and object.
13421:
13646: END IF;
13647:
13648: -- check whether subject_id belongs to subject_type
13649: IF p_relationship_rec.subject_id IS NOT NULL
13650: AND p_relationship_rec.subject_id <> fnd_api.g_miss_num
13651: AND p_create_update_flag = 'C'
13652: THEN
13653: l_in := hz_relationship_type_v2pub.in_instance_sets (
13654: p_relationship_rec.subject_type,
13661: fnd_message.set_token('COLUMN',
13662: lower(p_relationship_rec.subject_type)||' id');
13663: fnd_message.set_token('TABLE', p_relationship_rec.subject_table_name);
13664: fnd_msg_pub.add;
13665: x_return_status := fnd_api.g_ret_sts_error;
13666: END IF;
13667:
13668: /*IF g_debug THEN
13669: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'subject_id belongs to subject_type. ' ||
13729: -- do not need to check during update because subject_table_name is
13730: -- non-updateable.
13731: IF p_create_update_flag = 'C'
13732: AND p_relationship_rec.subject_table_name IS NOT NULL
13733: AND p_relationship_rec.subject_table_name <> fnd_api.g_miss_char
13734: THEN
13735: BEGIN
13736: SELECT 'Y'
13737: INTO l_dummy
13743: fnd_message.set_token('FK', 'subject_table_name');
13744: fnd_message.set_token('COLUMN', 'obj_name');
13745: fnd_message.set_token('TABLE', 'fnd_objects');
13746: fnd_msg_pub.add;
13747: x_return_status := fnd_api.g_ret_sts_error;
13748: END;
13749:
13750: /*IF g_debug THEN
13751: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
13815: -- do not need to check during update because subject_type is
13816: -- non-updateable.
13817: IF p_create_update_flag = 'C'
13818: AND p_relationship_rec.subject_type IS NOT NULL
13819: AND p_relationship_rec.subject_type <> fnd_api.g_miss_char
13820: THEN
13821: BEGIN
13822: SELECT 'Y'
13823: INTO l_dummy
13829: fnd_message.set_token('FK', 'subject_type');
13830: fnd_message.set_token('COLUMN', 'instance_set_name');
13831: fnd_message.set_token('TABLE', 'fnd_object_instance_sets');
13832: fnd_msg_pub.add;
13833: x_return_status := fnd_api.g_ret_sts_error;
13834: END;
13835:
13836: /*IF g_debug THEN
13837: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
13900: END IF;
13901:
13902: -- check whether object_id belongs to object_type
13903: IF p_relationship_rec.object_id IS NOT NULL
13904: AND p_relationship_rec.object_id <> fnd_api.g_miss_num
13905: AND p_create_update_flag = 'C'
13906: THEN
13907: l_in := hz_relationship_type_v2pub.in_instance_sets (
13908: p_relationship_rec.object_type,
13915: fnd_message.set_token('COLUMN',
13916: LOWER(p_relationship_rec.object_type)||' id');
13917: fnd_message.set_token('TABLE', p_relationship_rec.object_table_name);
13918: fnd_msg_pub.add;
13919: x_return_status := fnd_api.g_ret_sts_error;
13920: END IF;
13921:
13922: /*IF g_debug THEN
13923: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'object_id belongs to object_type. ' ||
13985: -- do not need to check during update because object_table_name is
13986: -- non-updateable.
13987: IF p_create_update_flag = 'C'
13988: AND p_relationship_rec.object_table_name IS NOT NULL
13989: AND p_relationship_rec.object_table_name <> fnd_api.g_miss_char
13990: THEN
13991: BEGIN
13992: SELECT 'Y'
13993: INTO l_dummy
13999: fnd_message.set_token('FK', 'object_table_name');
14000: fnd_message.set_token('COLUMN', 'obj_name');
14001: fnd_message.set_token('TABLE', 'fnd_objects');
14002: fnd_msg_pub.add;
14003: x_return_status := fnd_api.g_ret_sts_error;
14004: END;
14005:
14006: /*IF g_debug THEN
14007: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
14073: -- do not need to check during update because object_type is
14074: -- non-updateable.
14075: IF p_create_update_flag = 'C'
14076: AND p_relationship_rec.object_type IS NOT NULL
14077: AND p_relationship_rec.object_type <> fnd_api.g_miss_char
14078: THEN
14079: BEGIN
14080: SELECT 'Y'
14081: INTO l_dummy
14087: fnd_message.set_token('FK', 'object_type');
14088: fnd_message.set_token('COLUMN', 'instance_set_name');
14089: fnd_message.set_token('TABLE', 'fnd_object_instance_sets');
14090: fnd_msg_pub.add;
14091: x_return_status := fnd_api.g_ret_sts_error;
14092: END;
14093:
14094: /*IF g_debug THEN
14095: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
14211: /****Logical APIs - validation not required****/
14212: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
14213: -- status is lookup code in lookup type REGISTRY_STATUS
14214: IF p_relationship_rec.status IS NOT NULL
14215: AND p_relationship_rec.status <> fnd_api.g_miss_char
14216: AND (p_create_update_flag = 'C'
14217: OR (p_create_update_flag = 'U'
14218: AND p_relationship_rec.status <> NVL(l_status,
14219: fnd_api.g_miss_char)))
14215: AND p_relationship_rec.status <> fnd_api.g_miss_char
14216: AND (p_create_update_flag = 'C'
14217: OR (p_create_update_flag = 'U'
14218: AND p_relationship_rec.status <> NVL(l_status,
14219: fnd_api.g_miss_char)))
14220: THEN
14221: validate_lookup (
14222: p_column => 'status',
14223: p_lookup_type => 'REGISTRY_STATUS',
14311:
14312: -- end_date must be null or greater than start date
14313: IF (p_create_update_flag = 'C') THEN
14314: IF p_relationship_rec.end_date IS NOT NULL AND
14315: p_relationship_rec.end_date <> fnd_api.g_miss_date AND
14316: p_relationship_rec.end_date < p_relationship_rec.start_date
14317: THEN
14318: fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
14319: fnd_msg_pub.add;
14316: p_relationship_rec.end_date < p_relationship_rec.start_date
14317: THEN
14318: fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
14319: fnd_msg_pub.add;
14320: x_return_status := fnd_api.g_ret_sts_error;
14321: END IF;
14322: ELSIF (p_create_update_flag = 'U') THEN
14323: -- old start_date, end_date has been selected from table
14324: -- and put into l_start_date, l_end_date
14322: ELSIF (p_create_update_flag = 'U') THEN
14323: -- old start_date, end_date has been selected from table
14324: -- and put into l_start_date, l_end_date
14325:
14326: IF p_relationship_rec.start_date <> fnd_api.g_miss_date THEN
14327: l_start_date := p_relationship_rec.start_date;
14328: END IF;
14329:
14330: IF p_relationship_rec.end_date IS NULL
14327: l_start_date := p_relationship_rec.start_date;
14328: END IF;
14329:
14330: IF p_relationship_rec.end_date IS NULL
14331: OR p_relationship_rec.end_date <> fnd_api.g_miss_date
14332: THEN
14333: l_end_date := p_relationship_rec.end_date;
14334: END IF;
14335:
14337: AND l_end_date < l_start_date
14338: THEN
14339: fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
14340: fnd_msg_pub.add;
14341: x_return_status := fnd_api.g_ret_sts_error;
14342: END IF;
14343: END IF;
14344:
14345: /*IF g_debug THEN
14391: );
14392: IF l_overlap = 'Y' THEN
14393: fnd_message.set_name('AR', 'HZ_RELATIONSHIP_DATE_OVERLAP');
14394: fnd_msg_pub.add;
14395: x_return_status := fnd_api.g_ret_sts_error;
14396: -- Bug 3294936 : Add exit to come out of loop
14397: EXIT;
14398: END IF;
14399: END LOOP;
14458: -- p_web_rec Web record.
14459: -- p_rowid Rowid of the record (used only in update mode).
14460: -- IN/OUT:
14461: -- x_return_status Return status after the call. The status can
14462: -- be FND_API.G_RET_STS_SUCCESS (success),
14463: -- fnd_api.g_ret_sts_error (error),
14464: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14465: --
14466: -- NOTES
14459: -- p_rowid Rowid of the record (used only in update mode).
14460: -- IN/OUT:
14461: -- x_return_status Return status after the call. The status can
14462: -- be FND_API.G_RET_STS_SUCCESS (success),
14463: -- fnd_api.g_ret_sts_error (error),
14464: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14465: --
14466: -- NOTES
14467: --
14460: -- IN/OUT:
14461: -- x_return_status Return status after the call. The status can
14462: -- be FND_API.G_RET_STS_SUCCESS (success),
14463: -- fnd_api.g_ret_sts_error (error),
14464: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14465: --
14466: -- NOTES
14467: --
14468: -- MODIFICATION HISTORY
14543: -- p_edi_rec EDI record.
14544: -- p_rowid Rowid of the record (used only in update mode).
14545: -- IN/OUT:
14546: -- x_return_status Return status after the call. The status can
14547: -- be FND_API.G_RET_STS_SUCCESS (success),
14548: -- fnd_api.g_ret_sts_error (error),
14549: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14550: --
14551: -- NOTES
14544: -- p_rowid Rowid of the record (used only in update mode).
14545: -- IN/OUT:
14546: -- x_return_status Return status after the call. The status can
14547: -- be FND_API.G_RET_STS_SUCCESS (success),
14548: -- fnd_api.g_ret_sts_error (error),
14549: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14550: --
14551: -- NOTES
14552: --
14545: -- IN/OUT:
14546: -- x_return_status Return status after the call. The status can
14547: -- be FND_API.G_RET_STS_SUCCESS (success),
14548: -- fnd_api.g_ret_sts_error (error),
14549: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14550: --
14551: -- NOTES
14552: --
14553: -- MODIFICATION HISTORY
14613: -- p_eft_rec EFT record.
14614: -- p_rowid Rowid of the record (used only in update mode).
14615: -- IN/OUT:
14616: -- x_return_status Return status after the call. The status can
14617: -- be FND_API.G_RET_STS_SUCCESS (success),
14618: -- fnd_api.g_ret_sts_error (error),
14619: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14620: --
14621: -- NOTES
14614: -- p_rowid Rowid of the record (used only in update mode).
14615: -- IN/OUT:
14616: -- x_return_status Return status after the call. The status can
14617: -- be FND_API.G_RET_STS_SUCCESS (success),
14618: -- fnd_api.g_ret_sts_error (error),
14619: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14620: --
14621: -- NOTES
14622: --
14615: -- IN/OUT:
14616: -- x_return_status Return status after the call. The status can
14617: -- be FND_API.G_RET_STS_SUCCESS (success),
14618: -- fnd_api.g_ret_sts_error (error),
14619: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14620: --
14621: -- NOTES
14622: --
14623: -- MODIFICATION HISTORY
14683: -- p_web_rec Web record.
14684: -- p_rowid Rowid of the record (used only in update mode).
14685: -- IN/OUT:
14686: -- x_return_status Return status after the call. The status can
14687: -- be FND_API.G_RET_STS_SUCCESS (success),
14688: -- fnd_api.g_ret_sts_error (error),
14689: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14690: --
14691: -- NOTES
14684: -- p_rowid Rowid of the record (used only in update mode).
14685: -- IN/OUT:
14686: -- x_return_status Return status after the call. The status can
14687: -- be FND_API.G_RET_STS_SUCCESS (success),
14688: -- fnd_api.g_ret_sts_error (error),
14689: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14690: --
14691: -- NOTES
14692: --
14685: -- IN/OUT:
14686: -- x_return_status Return status after the call. The status can
14687: -- be FND_API.G_RET_STS_SUCCESS (success),
14688: -- fnd_api.g_ret_sts_error (error),
14689: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14690: --
14691: -- NOTES
14692: --
14693: -- MODIFICATION HISTORY
14754: -- p_phone_rec Phone record.
14755: -- p_rowid Rowid of the record (used only in update mode).
14756: -- IN/OUT:
14757: -- x_return_status Return status after the call. The status can
14758: -- be FND_API.G_RET_STS_SUCCESS (success),
14759: -- fnd_api.g_ret_sts_error (error),
14760: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14761: --
14762: -- NOTES
14755: -- p_rowid Rowid of the record (used only in update mode).
14756: -- IN/OUT:
14757: -- x_return_status Return status after the call. The status can
14758: -- be FND_API.G_RET_STS_SUCCESS (success),
14759: -- fnd_api.g_ret_sts_error (error),
14760: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14761: --
14762: -- NOTES
14763: --
14756: -- IN/OUT:
14757: -- x_return_status Return status after the call. The status can
14758: -- be FND_API.G_RET_STS_SUCCESS (success),
14759: -- fnd_api.g_ret_sts_error (error),
14760: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14761: --
14762: -- NOTES
14763: --
14764: -- MODIFICATION HISTORY
14824: -- p_telex_rec Telex record.
14825: -- p_rowid Rowid of the record (used only in update mode).
14826: -- IN/OUT:
14827: -- x_return_status Return status after the call. The status can
14828: -- be FND_API.G_RET_STS_SUCCESS (success),
14829: -- fnd_api.g_ret_sts_error (error),
14830: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14831: --
14832: -- NOTES
14825: -- p_rowid Rowid of the record (used only in update mode).
14826: -- IN/OUT:
14827: -- x_return_status Return status after the call. The status can
14828: -- be FND_API.G_RET_STS_SUCCESS (success),
14829: -- fnd_api.g_ret_sts_error (error),
14830: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14831: --
14832: -- NOTES
14833: --
14826: -- IN/OUT:
14827: -- x_return_status Return status after the call. The status can
14828: -- be FND_API.G_RET_STS_SUCCESS (success),
14829: -- fnd_api.g_ret_sts_error (error),
14830: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14831: --
14832: -- NOTES
14833: --
14834: -- MODIFICATION HISTORY
14894: -- p_email_rec Email record.
14895: -- p_rowid Rowid of the record (used only in update mode).
14896: -- IN/OUT:
14897: -- x_return_status Return status after the call. The status can
14898: -- be FND_API.G_RET_STS_SUCCESS (success),
14899: -- fnd_api.g_ret_sts_error (error),
14900: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14901: --
14902: -- NOTES
14895: -- p_rowid Rowid of the record (used only in update mode).
14896: -- IN/OUT:
14897: -- x_return_status Return status after the call. The status can
14898: -- be FND_API.G_RET_STS_SUCCESS (success),
14899: -- fnd_api.g_ret_sts_error (error),
14900: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14901: --
14902: -- NOTES
14903: --
14896: -- IN/OUT:
14897: -- x_return_status Return status after the call. The status can
14898: -- be FND_API.G_RET_STS_SUCCESS (success),
14899: -- fnd_api.g_ret_sts_error (error),
14900: -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
14901: --
14902: -- NOTES
14903: --
14904: -- MODIFICATION HISTORY
14970:
14971:
14972: IF ( p_create_update_flag = 'C' AND
14973: p_organization_rec.avg_high_credit IS NOT NULL AND
14974: p_organization_rec.avg_high_credit <> FND_API.G_MISS_NUM )
14975: OR
14976: ( p_create_update_flag = 'U' AND
14977: ( p_organization_rec.avg_high_credit = FND_API.G_MISS_NUM OR
14978: p_organization_rec.avg_high_credit IS NOT NULL
14973: p_organization_rec.avg_high_credit IS NOT NULL AND
14974: p_organization_rec.avg_high_credit <> FND_API.G_MISS_NUM )
14975: OR
14976: ( p_create_update_flag = 'U' AND
14977: ( p_organization_rec.avg_high_credit = FND_API.G_MISS_NUM OR
14978: p_organization_rec.avg_high_credit IS NOT NULL
14979: ) )
14980: THEN
14981: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
14980: THEN
14981: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
14982: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'avg_high_credit' );
14983: FND_MSG_PUB.ADD;
14984: x_return_status := FND_API.G_RET_STS_ERROR;
14985: END IF;
14986:
14987: IF ( p_create_update_flag = 'C' AND
14988: p_organization_rec.credit_score IS NOT NULL AND
14985: END IF;
14986:
14987: IF ( p_create_update_flag = 'C' AND
14988: p_organization_rec.credit_score IS NOT NULL AND
14989: p_organization_rec.credit_score <> FND_API.G_MISS_CHAR )
14990: OR
14991: ( p_create_update_flag = 'U' AND
14992: ( p_organization_rec.credit_score = FND_API.G_MISS_CHAR OR
14993: p_organization_rec.credit_score IS NOT NULL
14988: p_organization_rec.credit_score IS NOT NULL AND
14989: p_organization_rec.credit_score <> FND_API.G_MISS_CHAR )
14990: OR
14991: ( p_create_update_flag = 'U' AND
14992: ( p_organization_rec.credit_score = FND_API.G_MISS_CHAR OR
14993: p_organization_rec.credit_score IS NOT NULL
14994: ) )
14995: THEN
14996: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
14995: THEN
14996: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
14997: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score' );
14998: FND_MSG_PUB.ADD;
14999: x_return_status := FND_API.G_RET_STS_ERROR;
15000: END IF;
15001:
15002: IF ( p_create_update_flag = 'C' AND
15003: p_organization_rec.credit_score_age IS NOT NULL AND
15000: END IF;
15001:
15002: IF ( p_create_update_flag = 'C' AND
15003: p_organization_rec.credit_score_age IS NOT NULL AND
15004: p_organization_rec.credit_score_age <> FND_API.G_MISS_NUM )
15005: OR
15006: ( p_create_update_flag = 'U' AND
15007: ( p_organization_rec.credit_score_age = FND_API.G_MISS_NUM OR
15008: p_organization_rec.credit_score_age IS NOT NULL
15003: p_organization_rec.credit_score_age IS NOT NULL AND
15004: p_organization_rec.credit_score_age <> FND_API.G_MISS_NUM )
15005: OR
15006: ( p_create_update_flag = 'U' AND
15007: ( p_organization_rec.credit_score_age = FND_API.G_MISS_NUM OR
15008: p_organization_rec.credit_score_age IS NOT NULL
15009: ) )
15010: THEN
15011: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15010: THEN
15011: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15012: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_age' );
15013: FND_MSG_PUB.ADD;
15014: x_return_status := FND_API.G_RET_STS_ERROR;
15015: END IF;
15016:
15017: IF ( p_create_update_flag = 'C' AND
15018: p_organization_rec.credit_score_class IS NOT NULL AND
15015: END IF;
15016:
15017: IF ( p_create_update_flag = 'C' AND
15018: p_organization_rec.credit_score_class IS NOT NULL AND
15019: p_organization_rec.credit_score_class <> FND_API.G_MISS_NUM )
15020: OR
15021: ( p_create_update_flag = 'U' AND
15022: ( p_organization_rec.credit_score_class = FND_API.G_MISS_NUM OR
15023: p_organization_rec.credit_score_class IS NOT NULL
15018: p_organization_rec.credit_score_class IS NOT NULL AND
15019: p_organization_rec.credit_score_class <> FND_API.G_MISS_NUM )
15020: OR
15021: ( p_create_update_flag = 'U' AND
15022: ( p_organization_rec.credit_score_class = FND_API.G_MISS_NUM OR
15023: p_organization_rec.credit_score_class IS NOT NULL
15024: ) )
15025: THEN
15026: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15025: THEN
15026: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15027: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_class' );
15028: FND_MSG_PUB.ADD;
15029: x_return_status := FND_API.G_RET_STS_ERROR;
15030: END IF;
15031:
15032: IF ( p_create_update_flag = 'C' AND
15033: p_organization_rec.credit_score_commentary IS NOT NULL AND
15030: END IF;
15031:
15032: IF ( p_create_update_flag = 'C' AND
15033: p_organization_rec.credit_score_commentary IS NOT NULL AND
15034: p_organization_rec.credit_score_commentary <> FND_API.G_MISS_CHAR )
15035: OR
15036: ( p_create_update_flag = 'U' AND
15037: ( p_organization_rec.credit_score_commentary = FND_API.G_MISS_CHAR OR
15038: p_organization_rec.credit_score_commentary IS NOT NULL
15033: p_organization_rec.credit_score_commentary IS NOT NULL AND
15034: p_organization_rec.credit_score_commentary <> FND_API.G_MISS_CHAR )
15035: OR
15036: ( p_create_update_flag = 'U' AND
15037: ( p_organization_rec.credit_score_commentary = FND_API.G_MISS_CHAR OR
15038: p_organization_rec.credit_score_commentary IS NOT NULL
15039: ) )
15040: THEN
15041: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15040: THEN
15041: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15042: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary' );
15043: FND_MSG_PUB.ADD;
15044: x_return_status := FND_API.G_RET_STS_ERROR;
15045: END IF;
15046:
15047: IF ( p_create_update_flag = 'C' AND
15048: p_organization_rec.credit_score_commentary2 IS NOT NULL AND
15045: END IF;
15046:
15047: IF ( p_create_update_flag = 'C' AND
15048: p_organization_rec.credit_score_commentary2 IS NOT NULL AND
15049: p_organization_rec.credit_score_commentary2 <> FND_API.G_MISS_CHAR )
15050: OR
15051: ( p_create_update_flag = 'U' AND
15052: ( p_organization_rec.credit_score_commentary2 = FND_API.G_MISS_CHAR OR
15053: p_organization_rec.credit_score_commentary2 IS NOT NULL
15048: p_organization_rec.credit_score_commentary2 IS NOT NULL AND
15049: p_organization_rec.credit_score_commentary2 <> FND_API.G_MISS_CHAR )
15050: OR
15051: ( p_create_update_flag = 'U' AND
15052: ( p_organization_rec.credit_score_commentary2 = FND_API.G_MISS_CHAR OR
15053: p_organization_rec.credit_score_commentary2 IS NOT NULL
15054: ) )
15055: THEN
15056: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15055: THEN
15056: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15057: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary2' );
15058: FND_MSG_PUB.ADD;
15059: x_return_status := FND_API.G_RET_STS_ERROR;
15060: END IF;
15061:
15062: IF ( p_create_update_flag = 'C' AND
15063: p_organization_rec.credit_score_commentary3 IS NOT NULL AND
15060: END IF;
15061:
15062: IF ( p_create_update_flag = 'C' AND
15063: p_organization_rec.credit_score_commentary3 IS NOT NULL AND
15064: p_organization_rec.credit_score_commentary3 <> FND_API.G_MISS_CHAR )
15065: OR
15066: ( p_create_update_flag = 'U' AND
15067: ( p_organization_rec.credit_score_commentary3 = FND_API.G_MISS_CHAR OR
15068: p_organization_rec.credit_score_commentary3 IS NOT NULL
15063: p_organization_rec.credit_score_commentary3 IS NOT NULL AND
15064: p_organization_rec.credit_score_commentary3 <> FND_API.G_MISS_CHAR )
15065: OR
15066: ( p_create_update_flag = 'U' AND
15067: ( p_organization_rec.credit_score_commentary3 = FND_API.G_MISS_CHAR OR
15068: p_organization_rec.credit_score_commentary3 IS NOT NULL
15069: ) )
15070: THEN
15071: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15070: THEN
15071: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15072: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary3' );
15073: FND_MSG_PUB.ADD;
15074: x_return_status := FND_API.G_RET_STS_ERROR;
15075: END IF;
15076:
15077: IF ( p_create_update_flag = 'C' AND
15078: p_organization_rec.credit_score_commentary4 IS NOT NULL AND
15075: END IF;
15076:
15077: IF ( p_create_update_flag = 'C' AND
15078: p_organization_rec.credit_score_commentary4 IS NOT NULL AND
15079: p_organization_rec.credit_score_commentary4 <> FND_API.G_MISS_CHAR )
15080: OR
15081: ( p_create_update_flag = 'U' AND
15082: ( p_organization_rec.credit_score_commentary4 = FND_API.G_MISS_CHAR OR
15083: p_organization_rec.credit_score_commentary4 IS NOT NULL
15078: p_organization_rec.credit_score_commentary4 IS NOT NULL AND
15079: p_organization_rec.credit_score_commentary4 <> FND_API.G_MISS_CHAR )
15080: OR
15081: ( p_create_update_flag = 'U' AND
15082: ( p_organization_rec.credit_score_commentary4 = FND_API.G_MISS_CHAR OR
15083: p_organization_rec.credit_score_commentary4 IS NOT NULL
15084: ) )
15085: THEN
15086: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15085: THEN
15086: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15087: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary4' );
15088: FND_MSG_PUB.ADD;
15089: x_return_status := FND_API.G_RET_STS_ERROR;
15090: END IF;
15091:
15092: IF ( p_create_update_flag = 'C' AND
15093: p_organization_rec.credit_score_commentary5 IS NOT NULL AND
15090: END IF;
15091:
15092: IF ( p_create_update_flag = 'C' AND
15093: p_organization_rec.credit_score_commentary5 IS NOT NULL AND
15094: p_organization_rec.credit_score_commentary5 <> FND_API.G_MISS_CHAR )
15095: OR
15096: ( p_create_update_flag = 'U' AND
15097: ( p_organization_rec.credit_score_commentary5 = FND_API.G_MISS_CHAR OR
15098: p_organization_rec.credit_score_commentary5 IS NOT NULL
15093: p_organization_rec.credit_score_commentary5 IS NOT NULL AND
15094: p_organization_rec.credit_score_commentary5 <> FND_API.G_MISS_CHAR )
15095: OR
15096: ( p_create_update_flag = 'U' AND
15097: ( p_organization_rec.credit_score_commentary5 = FND_API.G_MISS_CHAR OR
15098: p_organization_rec.credit_score_commentary5 IS NOT NULL
15099: ) )
15100: THEN
15101: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15100: THEN
15101: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15102: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary5' );
15103: FND_MSG_PUB.ADD;
15104: x_return_status := FND_API.G_RET_STS_ERROR;
15105: END IF;
15106:
15107: IF ( p_create_update_flag = 'C' AND
15108: p_organization_rec.credit_score_commentary6 IS NOT NULL AND
15105: END IF;
15106:
15107: IF ( p_create_update_flag = 'C' AND
15108: p_organization_rec.credit_score_commentary6 IS NOT NULL AND
15109: p_organization_rec.credit_score_commentary6 <> FND_API.G_MISS_CHAR )
15110: OR
15111: ( p_create_update_flag = 'U' AND
15112: ( p_organization_rec.credit_score_commentary6 = FND_API.G_MISS_CHAR OR
15113: p_organization_rec.credit_score_commentary6 IS NOT NULL
15108: p_organization_rec.credit_score_commentary6 IS NOT NULL AND
15109: p_organization_rec.credit_score_commentary6 <> FND_API.G_MISS_CHAR )
15110: OR
15111: ( p_create_update_flag = 'U' AND
15112: ( p_organization_rec.credit_score_commentary6 = FND_API.G_MISS_CHAR OR
15113: p_organization_rec.credit_score_commentary6 IS NOT NULL
15114: ) )
15115: THEN
15116: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15115: THEN
15116: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15117: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary6' );
15118: FND_MSG_PUB.ADD;
15119: x_return_status := FND_API.G_RET_STS_ERROR;
15120: END IF;
15121:
15122: IF ( p_create_update_flag = 'C' AND
15123: p_organization_rec.credit_score_commentary7 IS NOT NULL AND
15120: END IF;
15121:
15122: IF ( p_create_update_flag = 'C' AND
15123: p_organization_rec.credit_score_commentary7 IS NOT NULL AND
15124: p_organization_rec.credit_score_commentary7 <> FND_API.G_MISS_CHAR )
15125: OR
15126: ( p_create_update_flag = 'U' AND
15127: ( p_organization_rec.credit_score_commentary7 = FND_API.G_MISS_CHAR OR
15128: p_organization_rec.credit_score_commentary7 IS NOT NULL
15123: p_organization_rec.credit_score_commentary7 IS NOT NULL AND
15124: p_organization_rec.credit_score_commentary7 <> FND_API.G_MISS_CHAR )
15125: OR
15126: ( p_create_update_flag = 'U' AND
15127: ( p_organization_rec.credit_score_commentary7 = FND_API.G_MISS_CHAR OR
15128: p_organization_rec.credit_score_commentary7 IS NOT NULL
15129: ) )
15130: THEN
15131: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15130: THEN
15131: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15132: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary7' );
15133: FND_MSG_PUB.ADD;
15134: x_return_status := FND_API.G_RET_STS_ERROR;
15135: END IF;
15136:
15137: IF ( p_create_update_flag = 'C' AND
15138: p_organization_rec.credit_score_commentary8 IS NOT NULL AND
15135: END IF;
15136:
15137: IF ( p_create_update_flag = 'C' AND
15138: p_organization_rec.credit_score_commentary8 IS NOT NULL AND
15139: p_organization_rec.credit_score_commentary8 <> FND_API.G_MISS_CHAR )
15140: OR
15141: ( p_create_update_flag = 'U' AND
15142: ( p_organization_rec.credit_score_commentary8 = FND_API.G_MISS_CHAR OR
15143: p_organization_rec.credit_score_commentary8 IS NOT NULL
15138: p_organization_rec.credit_score_commentary8 IS NOT NULL AND
15139: p_organization_rec.credit_score_commentary8 <> FND_API.G_MISS_CHAR )
15140: OR
15141: ( p_create_update_flag = 'U' AND
15142: ( p_organization_rec.credit_score_commentary8 = FND_API.G_MISS_CHAR OR
15143: p_organization_rec.credit_score_commentary8 IS NOT NULL
15144: ) )
15145: THEN
15146: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15145: THEN
15146: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15147: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary8' );
15148: FND_MSG_PUB.ADD;
15149: x_return_status := FND_API.G_RET_STS_ERROR;
15150: END IF;
15151:
15152: IF ( p_create_update_flag = 'C' AND
15153: p_organization_rec.credit_score_commentary9 IS NOT NULL AND
15150: END IF;
15151:
15152: IF ( p_create_update_flag = 'C' AND
15153: p_organization_rec.credit_score_commentary9 IS NOT NULL AND
15154: p_organization_rec.credit_score_commentary9 <> FND_API.G_MISS_CHAR )
15155: OR
15156: ( p_create_update_flag = 'U' AND
15157: ( p_organization_rec.credit_score_commentary9 = FND_API.G_MISS_CHAR OR
15158: p_organization_rec.credit_score_commentary9 IS NOT NULL
15153: p_organization_rec.credit_score_commentary9 IS NOT NULL AND
15154: p_organization_rec.credit_score_commentary9 <> FND_API.G_MISS_CHAR )
15155: OR
15156: ( p_create_update_flag = 'U' AND
15157: ( p_organization_rec.credit_score_commentary9 = FND_API.G_MISS_CHAR OR
15158: p_organization_rec.credit_score_commentary9 IS NOT NULL
15159: ) )
15160: THEN
15161: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15160: THEN
15161: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15162: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary9' );
15163: FND_MSG_PUB.ADD;
15164: x_return_status := FND_API.G_RET_STS_ERROR;
15165: END IF;
15166:
15167: IF ( p_create_update_flag = 'C' AND
15168: p_organization_rec.credit_score_commentary10 IS NOT NULL AND
15165: END IF;
15166:
15167: IF ( p_create_update_flag = 'C' AND
15168: p_organization_rec.credit_score_commentary10 IS NOT NULL AND
15169: p_organization_rec.credit_score_commentary10 <> FND_API.G_MISS_CHAR )
15170: OR
15171: ( p_create_update_flag = 'U' AND
15172: ( p_organization_rec.credit_score_commentary10 = FND_API.G_MISS_CHAR OR
15173: p_organization_rec.credit_score_commentary10 IS NOT NULL
15168: p_organization_rec.credit_score_commentary10 IS NOT NULL AND
15169: p_organization_rec.credit_score_commentary10 <> FND_API.G_MISS_CHAR )
15170: OR
15171: ( p_create_update_flag = 'U' AND
15172: ( p_organization_rec.credit_score_commentary10 = FND_API.G_MISS_CHAR OR
15173: p_organization_rec.credit_score_commentary10 IS NOT NULL
15174: ) )
15175: THEN
15176: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15175: THEN
15176: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15177: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_commentary10' );
15178: FND_MSG_PUB.ADD;
15179: x_return_status := FND_API.G_RET_STS_ERROR;
15180: END IF;
15181:
15182: IF ( p_create_update_flag = 'C' AND
15183: p_organization_rec.credit_score_date IS NOT NULL AND
15180: END IF;
15181:
15182: IF ( p_create_update_flag = 'C' AND
15183: p_organization_rec.credit_score_date IS NOT NULL AND
15184: p_organization_rec.credit_score_date <> FND_API.G_MISS_DATE )
15185: OR
15186: ( p_create_update_flag = 'U' AND
15187: ( p_organization_rec.credit_score_date = FND_API.G_MISS_DATE OR
15188: p_organization_rec.credit_score_date IS NOT NULL
15183: p_organization_rec.credit_score_date IS NOT NULL AND
15184: p_organization_rec.credit_score_date <> FND_API.G_MISS_DATE )
15185: OR
15186: ( p_create_update_flag = 'U' AND
15187: ( p_organization_rec.credit_score_date = FND_API.G_MISS_DATE OR
15188: p_organization_rec.credit_score_date IS NOT NULL
15189: ) )
15190: THEN
15191: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15190: THEN
15191: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15192: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_date' );
15193: FND_MSG_PUB.ADD;
15194: x_return_status := FND_API.G_RET_STS_ERROR;
15195: END IF;
15196:
15197: IF ( p_create_update_flag = 'C' AND
15198: p_organization_rec.credit_score_incd_default IS NOT NULL AND
15195: END IF;
15196:
15197: IF ( p_create_update_flag = 'C' AND
15198: p_organization_rec.credit_score_incd_default IS NOT NULL AND
15199: p_organization_rec.credit_score_incd_default <> FND_API.G_MISS_NUM )
15200: OR
15201: ( p_create_update_flag = 'U' AND
15202: ( p_organization_rec.credit_score_incd_default = FND_API.G_MISS_NUM OR
15203: p_organization_rec.credit_score_incd_default IS NOT NULL
15198: p_organization_rec.credit_score_incd_default IS NOT NULL AND
15199: p_organization_rec.credit_score_incd_default <> FND_API.G_MISS_NUM )
15200: OR
15201: ( p_create_update_flag = 'U' AND
15202: ( p_organization_rec.credit_score_incd_default = FND_API.G_MISS_NUM OR
15203: p_organization_rec.credit_score_incd_default IS NOT NULL
15204: ) )
15205: THEN
15206: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15205: THEN
15206: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15207: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_incd_default' );
15208: FND_MSG_PUB.ADD;
15209: x_return_status := FND_API.G_RET_STS_ERROR;
15210: END IF;
15211:
15212: IF ( p_create_update_flag = 'C' AND
15213: p_organization_rec.credit_score_natl_percentile IS NOT NULL AND
15210: END IF;
15211:
15212: IF ( p_create_update_flag = 'C' AND
15213: p_organization_rec.credit_score_natl_percentile IS NOT NULL AND
15214: p_organization_rec.credit_score_natl_percentile <> FND_API.G_MISS_NUM)
15215: OR
15216: ( p_create_update_flag = 'U' AND
15217: ( p_organization_rec.credit_score_natl_percentile = FND_API.G_MISS_NUM OR
15218: p_organization_rec.credit_score_natl_percentile IS NOT NULL
15213: p_organization_rec.credit_score_natl_percentile IS NOT NULL AND
15214: p_organization_rec.credit_score_natl_percentile <> FND_API.G_MISS_NUM)
15215: OR
15216: ( p_create_update_flag = 'U' AND
15217: ( p_organization_rec.credit_score_natl_percentile = FND_API.G_MISS_NUM OR
15218: p_organization_rec.credit_score_natl_percentile IS NOT NULL
15219: ) )
15220: THEN
15221: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15220: THEN
15221: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15222: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'credit_score_natl_percentile' );
15223: FND_MSG_PUB.ADD;
15224: x_return_status := FND_API.G_RET_STS_ERROR;
15225: END IF;
15226:
15227: IF ( p_create_update_flag = 'C' AND
15228: p_organization_rec.db_rating IS NOT NULL AND
15225: END IF;
15226:
15227: IF ( p_create_update_flag = 'C' AND
15228: p_organization_rec.db_rating IS NOT NULL AND
15229: p_organization_rec.db_rating <> FND_API.G_MISS_CHAR )
15230: OR
15231: ( p_create_update_flag = 'U' AND
15232: ( p_organization_rec.db_rating = FND_API.G_MISS_CHAR OR
15233: p_organization_rec.db_rating IS NOT NULL
15228: p_organization_rec.db_rating IS NOT NULL AND
15229: p_organization_rec.db_rating <> FND_API.G_MISS_CHAR )
15230: OR
15231: ( p_create_update_flag = 'U' AND
15232: ( p_organization_rec.db_rating = FND_API.G_MISS_CHAR OR
15233: p_organization_rec.db_rating IS NOT NULL
15234: ) )
15235: THEN
15236: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15235: THEN
15236: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15237: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'db_rating' );
15238: FND_MSG_PUB.ADD;
15239: x_return_status := FND_API.G_RET_STS_ERROR;
15240: END IF;
15241:
15242: IF ( p_create_update_flag = 'C' AND
15243: p_organization_rec.debarment_ind IS NOT NULL AND
15240: END IF;
15241:
15242: IF ( p_create_update_flag = 'C' AND
15243: p_organization_rec.debarment_ind IS NOT NULL AND
15244: p_organization_rec.debarment_ind <> FND_API.G_MISS_CHAR )
15245: OR
15246: ( p_create_update_flag = 'U' AND
15247: ( p_organization_rec.debarment_ind = FND_API.G_MISS_CHAR OR
15248: p_organization_rec.debarment_ind IS NOT NULL
15243: p_organization_rec.debarment_ind IS NOT NULL AND
15244: p_organization_rec.debarment_ind <> FND_API.G_MISS_CHAR )
15245: OR
15246: ( p_create_update_flag = 'U' AND
15247: ( p_organization_rec.debarment_ind = FND_API.G_MISS_CHAR OR
15248: p_organization_rec.debarment_ind IS NOT NULL
15249: ) )
15250: THEN
15251: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15250: THEN
15251: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15252: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'debarment_ind' );
15253: FND_MSG_PUB.ADD;
15254: x_return_status := FND_API.G_RET_STS_ERROR;
15255: END IF;
15256:
15257: IF ( p_create_update_flag = 'C' AND
15258: p_organization_rec.debarments_count IS NOT NULL AND
15255: END IF;
15256:
15257: IF ( p_create_update_flag = 'C' AND
15258: p_organization_rec.debarments_count IS NOT NULL AND
15259: p_organization_rec.debarments_count <> FND_API.G_MISS_NUM )
15260: OR
15261: ( p_create_update_flag = 'U' AND
15262: ( p_organization_rec.debarments_count = FND_API.G_MISS_NUM OR
15263: p_organization_rec.debarments_count IS NOT NULL
15258: p_organization_rec.debarments_count IS NOT NULL AND
15259: p_organization_rec.debarments_count <> FND_API.G_MISS_NUM )
15260: OR
15261: ( p_create_update_flag = 'U' AND
15262: ( p_organization_rec.debarments_count = FND_API.G_MISS_NUM OR
15263: p_organization_rec.debarments_count IS NOT NULL
15264: ) )
15265: THEN
15266: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15265: THEN
15266: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15267: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'debarments_count' );
15268: FND_MSG_PUB.ADD;
15269: x_return_status := FND_API.G_RET_STS_ERROR;
15270: END IF;
15271:
15272: IF ( p_create_update_flag = 'C' AND
15273: p_organization_rec.debarments_date IS NOT NULL AND
15270: END IF;
15271:
15272: IF ( p_create_update_flag = 'C' AND
15273: p_organization_rec.debarments_date IS NOT NULL AND
15274: p_organization_rec.debarments_date <> FND_API.G_MISS_DATE )
15275: OR
15276: ( p_create_update_flag = 'U' AND
15277: ( p_organization_rec.debarments_date = FND_API.G_MISS_DATE OR
15278: p_organization_rec.debarments_date IS NOT NULL
15273: p_organization_rec.debarments_date IS NOT NULL AND
15274: p_organization_rec.debarments_date <> FND_API.G_MISS_DATE )
15275: OR
15276: ( p_create_update_flag = 'U' AND
15277: ( p_organization_rec.debarments_date = FND_API.G_MISS_DATE OR
15278: p_organization_rec.debarments_date IS NOT NULL
15279: ) )
15280: THEN
15281: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15280: THEN
15281: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15282: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'debarments_date' );
15283: FND_MSG_PUB.ADD;
15284: x_return_status := FND_API.G_RET_STS_ERROR;
15285: END IF;
15286:
15287: IF ( p_create_update_flag = 'C' AND
15288: p_organization_rec.high_credit IS NOT NULL AND
15285: END IF;
15286:
15287: IF ( p_create_update_flag = 'C' AND
15288: p_organization_rec.high_credit IS NOT NULL AND
15289: p_organization_rec.high_credit <> FND_API.G_MISS_NUM )
15290: OR
15291: ( p_create_update_flag = 'U' AND
15292: ( p_organization_rec.high_credit = FND_API.G_MISS_NUM OR
15293: p_organization_rec.high_credit IS NOT NULL
15288: p_organization_rec.high_credit IS NOT NULL AND
15289: p_organization_rec.high_credit <> FND_API.G_MISS_NUM )
15290: OR
15291: ( p_create_update_flag = 'U' AND
15292: ( p_organization_rec.high_credit = FND_API.G_MISS_NUM OR
15293: p_organization_rec.high_credit IS NOT NULL
15294: ) )
15295: THEN
15296: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15295: THEN
15296: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15297: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'high_credit' );
15298: FND_MSG_PUB.ADD;
15299: x_return_status := FND_API.G_RET_STS_ERROR;
15300: END IF;
15301:
15302: IF ( p_create_update_flag = 'C' AND
15303: p_organization_rec.maximum_credit_currency_code IS NOT NULL AND
15300: END IF;
15301:
15302: IF ( p_create_update_flag = 'C' AND
15303: p_organization_rec.maximum_credit_currency_code IS NOT NULL AND
15304: p_organization_rec.maximum_credit_currency_code <> FND_API.G_MISS_CHAR )
15305: OR
15306: ( p_create_update_flag = 'U' AND
15307: ( p_organization_rec.maximum_credit_currency_code = FND_API.G_MISS_CHAR OR
15308: p_organization_rec.maximum_credit_currency_code IS NOT NULL
15303: p_organization_rec.maximum_credit_currency_code IS NOT NULL AND
15304: p_organization_rec.maximum_credit_currency_code <> FND_API.G_MISS_CHAR )
15305: OR
15306: ( p_create_update_flag = 'U' AND
15307: ( p_organization_rec.maximum_credit_currency_code = FND_API.G_MISS_CHAR OR
15308: p_organization_rec.maximum_credit_currency_code IS NOT NULL
15309: ) )
15310: THEN
15311: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15310: THEN
15311: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15312: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'maximum_credit_currency_code' );
15313: FND_MSG_PUB.ADD;
15314: x_return_status := FND_API.G_RET_STS_ERROR;
15315: END IF;
15316:
15317: IF ( p_create_update_flag = 'C' AND
15318: p_organization_rec.maximum_credit_recommendation IS NOT NULL AND
15315: END IF;
15316:
15317: IF ( p_create_update_flag = 'C' AND
15318: p_organization_rec.maximum_credit_recommendation IS NOT NULL AND
15319: p_organization_rec.maximum_credit_recommendation <> FND_API.G_MISS_NUM)
15320: OR
15321: ( p_create_update_flag = 'U' AND
15322: ( p_organization_rec.maximum_credit_recommendation = FND_API.G_MISS_NUM OR
15323: p_organization_rec.maximum_credit_recommendation IS NOT NULL
15318: p_organization_rec.maximum_credit_recommendation IS NOT NULL AND
15319: p_organization_rec.maximum_credit_recommendation <> FND_API.G_MISS_NUM)
15320: OR
15321: ( p_create_update_flag = 'U' AND
15322: ( p_organization_rec.maximum_credit_recommendation = FND_API.G_MISS_NUM OR
15323: p_organization_rec.maximum_credit_recommendation IS NOT NULL
15324: ) )
15325: THEN
15326: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15325: THEN
15326: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15327: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'maximum_credit_recommendation' );
15328: FND_MSG_PUB.ADD;
15329: x_return_status := FND_API.G_RET_STS_ERROR;
15330: END IF;
15331:
15332: IF ( p_create_update_flag = 'C' AND
15333: p_organization_rec.paydex_norm IS NOT NULL AND
15330: END IF;
15331:
15332: IF ( p_create_update_flag = 'C' AND
15333: p_organization_rec.paydex_norm IS NOT NULL AND
15334: p_organization_rec.paydex_norm <> FND_API.G_MISS_CHAR )
15335: OR
15336: ( p_create_update_flag = 'U' AND
15337: ( p_organization_rec.paydex_norm = FND_API.G_MISS_CHAR OR
15338: p_organization_rec.paydex_norm IS NOT NULL
15333: p_organization_rec.paydex_norm IS NOT NULL AND
15334: p_organization_rec.paydex_norm <> FND_API.G_MISS_CHAR )
15335: OR
15336: ( p_create_update_flag = 'U' AND
15337: ( p_organization_rec.paydex_norm = FND_API.G_MISS_CHAR OR
15338: p_organization_rec.paydex_norm IS NOT NULL
15339: ) )
15340: THEN
15341: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15340: THEN
15341: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15342: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'paydex_norm' );
15343: FND_MSG_PUB.ADD;
15344: x_return_status := FND_API.G_RET_STS_ERROR;
15345: END IF;
15346:
15347: IF ( p_create_update_flag = 'C' AND
15348: p_organization_rec.paydex_score IS NOT NULL AND
15345: END IF;
15346:
15347: IF ( p_create_update_flag = 'C' AND
15348: p_organization_rec.paydex_score IS NOT NULL AND
15349: p_organization_rec.paydex_score <> FND_API.G_MISS_CHAR )
15350: OR
15351: ( p_create_update_flag = 'U' AND
15352: ( p_organization_rec.paydex_score = FND_API.G_MISS_CHAR OR
15353: p_organization_rec.paydex_score IS NOT NULL
15348: p_organization_rec.paydex_score IS NOT NULL AND
15349: p_organization_rec.paydex_score <> FND_API.G_MISS_CHAR )
15350: OR
15351: ( p_create_update_flag = 'U' AND
15352: ( p_organization_rec.paydex_score = FND_API.G_MISS_CHAR OR
15353: p_organization_rec.paydex_score IS NOT NULL
15354: ) )
15355: THEN
15356: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15355: THEN
15356: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15357: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'paydex_score' );
15358: FND_MSG_PUB.ADD;
15359: x_return_status := FND_API.G_RET_STS_ERROR;
15360: END IF;
15361:
15362: IF ( p_create_update_flag = 'C' AND
15363: p_organization_rec.paydex_three_months_ago IS NOT NULL AND
15360: END IF;
15361:
15362: IF ( p_create_update_flag = 'C' AND
15363: p_organization_rec.paydex_three_months_ago IS NOT NULL AND
15364: p_organization_rec.paydex_three_months_ago <> FND_API.G_MISS_CHAR )
15365: OR
15366: ( p_create_update_flag = 'U' AND
15367: ( p_organization_rec.paydex_three_months_ago = FND_API.G_MISS_CHAR OR
15368: p_organization_rec.paydex_three_months_ago IS NOT NULL
15363: p_organization_rec.paydex_three_months_ago IS NOT NULL AND
15364: p_organization_rec.paydex_three_months_ago <> FND_API.G_MISS_CHAR )
15365: OR
15366: ( p_create_update_flag = 'U' AND
15367: ( p_organization_rec.paydex_three_months_ago = FND_API.G_MISS_CHAR OR
15368: p_organization_rec.paydex_three_months_ago IS NOT NULL
15369: ) )
15370: THEN
15371: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15370: THEN
15371: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15372: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'paydex_three_months_ago' );
15373: FND_MSG_PUB.ADD;
15374: x_return_status := FND_API.G_RET_STS_ERROR;
15375: END IF;
15376:
15377: IF ( p_create_update_flag = 'C' AND
15378: p_organization_rec.failure_score IS NOT NULL AND
15375: END IF;
15376:
15377: IF ( p_create_update_flag = 'C' AND
15378: p_organization_rec.failure_score IS NOT NULL AND
15379: p_organization_rec.failure_score <> FND_API.G_MISS_CHAR )
15380: OR
15381: ( p_create_update_flag = 'U' AND
15382: ( p_organization_rec.failure_score = FND_API.G_MISS_CHAR OR
15383: p_organization_rec.failure_score IS NOT NULL
15378: p_organization_rec.failure_score IS NOT NULL AND
15379: p_organization_rec.failure_score <> FND_API.G_MISS_CHAR )
15380: OR
15381: ( p_create_update_flag = 'U' AND
15382: ( p_organization_rec.failure_score = FND_API.G_MISS_CHAR OR
15383: p_organization_rec.failure_score IS NOT NULL
15384: ) )
15385: THEN
15386: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15385: THEN
15386: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15387: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score' );
15388: FND_MSG_PUB.ADD;
15389: x_return_status := FND_API.G_RET_STS_ERROR;
15390: END IF;
15391:
15392: IF ( p_create_update_flag = 'C' AND
15393: p_organization_rec.failure_score_age IS NOT NULL AND
15390: END IF;
15391:
15392: IF ( p_create_update_flag = 'C' AND
15393: p_organization_rec.failure_score_age IS NOT NULL AND
15394: p_organization_rec.failure_score_age <> FND_API.G_MISS_NUM )
15395: OR
15396: ( p_create_update_flag = 'U' AND
15397: ( p_organization_rec.failure_score_age = FND_API.G_MISS_NUM OR
15398: p_organization_rec.failure_score_age IS NOT NULL
15393: p_organization_rec.failure_score_age IS NOT NULL AND
15394: p_organization_rec.failure_score_age <> FND_API.G_MISS_NUM )
15395: OR
15396: ( p_create_update_flag = 'U' AND
15397: ( p_organization_rec.failure_score_age = FND_API.G_MISS_NUM OR
15398: p_organization_rec.failure_score_age IS NOT NULL
15399: ) )
15400: THEN
15401: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15400: THEN
15401: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15402: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_age' );
15403: FND_MSG_PUB.ADD;
15404: x_return_status := FND_API.G_RET_STS_ERROR;
15405: END IF;
15406:
15407: IF ( p_create_update_flag = 'C' AND
15408: p_organization_rec.failure_score_class IS NOT NULL AND
15405: END IF;
15406:
15407: IF ( p_create_update_flag = 'C' AND
15408: p_organization_rec.failure_score_class IS NOT NULL AND
15409: p_organization_rec.failure_score_class <> FND_API.G_MISS_NUM )
15410: OR
15411: ( p_create_update_flag = 'U' AND
15412: ( p_organization_rec.failure_score_class = FND_API.G_MISS_NUM OR
15413: p_organization_rec.failure_score_class IS NOT NULL
15408: p_organization_rec.failure_score_class IS NOT NULL AND
15409: p_organization_rec.failure_score_class <> FND_API.G_MISS_NUM )
15410: OR
15411: ( p_create_update_flag = 'U' AND
15412: ( p_organization_rec.failure_score_class = FND_API.G_MISS_NUM OR
15413: p_organization_rec.failure_score_class IS NOT NULL
15414: ) )
15415: THEN
15416: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15415: THEN
15416: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15417: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_class' );
15418: FND_MSG_PUB.ADD;
15419: x_return_status := FND_API.G_RET_STS_ERROR;
15420: END IF;
15421:
15422: IF ( p_create_update_flag = 'C' AND
15423: p_organization_rec.failure_score_commentary IS NOT NULL AND
15420: END IF;
15421:
15422: IF ( p_create_update_flag = 'C' AND
15423: p_organization_rec.failure_score_commentary IS NOT NULL AND
15424: p_organization_rec.failure_score_commentary <> FND_API.G_MISS_CHAR )
15425: OR
15426: ( p_create_update_flag = 'U' AND
15427: ( p_organization_rec.failure_score_commentary = FND_API.G_MISS_CHAR OR
15428: p_organization_rec.failure_score_commentary IS NOT NULL
15423: p_organization_rec.failure_score_commentary IS NOT NULL AND
15424: p_organization_rec.failure_score_commentary <> FND_API.G_MISS_CHAR )
15425: OR
15426: ( p_create_update_flag = 'U' AND
15427: ( p_organization_rec.failure_score_commentary = FND_API.G_MISS_CHAR OR
15428: p_organization_rec.failure_score_commentary IS NOT NULL
15429: ) )
15430: THEN
15431: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15430: THEN
15431: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15432: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary' );
15433: FND_MSG_PUB.ADD;
15434: x_return_status := FND_API.G_RET_STS_ERROR;
15435: END IF;
15436:
15437: IF ( p_create_update_flag = 'C' AND
15438: p_organization_rec.failure_score_commentary2 IS NOT NULL AND
15435: END IF;
15436:
15437: IF ( p_create_update_flag = 'C' AND
15438: p_organization_rec.failure_score_commentary2 IS NOT NULL AND
15439: p_organization_rec.failure_score_commentary2 <> FND_API.G_MISS_CHAR )
15440: OR
15441: ( p_create_update_flag = 'U' AND
15442: ( p_organization_rec.failure_score_commentary2 = FND_API.G_MISS_CHAR OR
15443: p_organization_rec.failure_score_commentary2 IS NOT NULL
15438: p_organization_rec.failure_score_commentary2 IS NOT NULL AND
15439: p_organization_rec.failure_score_commentary2 <> FND_API.G_MISS_CHAR )
15440: OR
15441: ( p_create_update_flag = 'U' AND
15442: ( p_organization_rec.failure_score_commentary2 = FND_API.G_MISS_CHAR OR
15443: p_organization_rec.failure_score_commentary2 IS NOT NULL
15444: ) )
15445: THEN
15446: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15445: THEN
15446: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15447: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary2' );
15448: FND_MSG_PUB.ADD;
15449: x_return_status := FND_API.G_RET_STS_ERROR;
15450: END IF;
15451:
15452: IF ( p_create_update_flag = 'C' AND
15453: p_organization_rec.failure_score_commentary3 IS NOT NULL AND
15450: END IF;
15451:
15452: IF ( p_create_update_flag = 'C' AND
15453: p_organization_rec.failure_score_commentary3 IS NOT NULL AND
15454: p_organization_rec.failure_score_commentary3 <> FND_API.G_MISS_CHAR )
15455: OR
15456: ( p_create_update_flag = 'U' AND
15457: ( p_organization_rec.failure_score_commentary3 = FND_API.G_MISS_CHAR OR
15458: p_organization_rec.failure_score_commentary3 IS NOT NULL
15453: p_organization_rec.failure_score_commentary3 IS NOT NULL AND
15454: p_organization_rec.failure_score_commentary3 <> FND_API.G_MISS_CHAR )
15455: OR
15456: ( p_create_update_flag = 'U' AND
15457: ( p_organization_rec.failure_score_commentary3 = FND_API.G_MISS_CHAR OR
15458: p_organization_rec.failure_score_commentary3 IS NOT NULL
15459: ) )
15460: THEN
15461: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15460: THEN
15461: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15462: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary3' );
15463: FND_MSG_PUB.ADD;
15464: x_return_status := FND_API.G_RET_STS_ERROR;
15465: END IF;
15466:
15467: IF ( p_create_update_flag = 'C' AND
15468: p_organization_rec.failure_score_commentary4 IS NOT NULL AND
15465: END IF;
15466:
15467: IF ( p_create_update_flag = 'C' AND
15468: p_organization_rec.failure_score_commentary4 IS NOT NULL AND
15469: p_organization_rec.failure_score_commentary4 <> FND_API.G_MISS_CHAR )
15470: OR
15471: ( p_create_update_flag = 'U' AND
15472: ( p_organization_rec.failure_score_commentary4 = FND_API.G_MISS_CHAR OR
15473: p_organization_rec.failure_score_commentary4 IS NOT NULL
15468: p_organization_rec.failure_score_commentary4 IS NOT NULL AND
15469: p_organization_rec.failure_score_commentary4 <> FND_API.G_MISS_CHAR )
15470: OR
15471: ( p_create_update_flag = 'U' AND
15472: ( p_organization_rec.failure_score_commentary4 = FND_API.G_MISS_CHAR OR
15473: p_organization_rec.failure_score_commentary4 IS NOT NULL
15474: ) )
15475: THEN
15476: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15475: THEN
15476: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15477: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary4' );
15478: FND_MSG_PUB.ADD;
15479: x_return_status := FND_API.G_RET_STS_ERROR;
15480: END IF;
15481:
15482: IF ( p_create_update_flag = 'C' AND
15483: p_organization_rec.failure_score_commentary5 IS NOT NULL AND
15480: END IF;
15481:
15482: IF ( p_create_update_flag = 'C' AND
15483: p_organization_rec.failure_score_commentary5 IS NOT NULL AND
15484: p_organization_rec.failure_score_commentary5 <> FND_API.G_MISS_CHAR )
15485: OR
15486: ( p_create_update_flag = 'U' AND
15487: ( p_organization_rec.failure_score_commentary5 = FND_API.G_MISS_CHAR OR
15488: p_organization_rec.failure_score_commentary5 IS NOT NULL
15483: p_organization_rec.failure_score_commentary5 IS NOT NULL AND
15484: p_organization_rec.failure_score_commentary5 <> FND_API.G_MISS_CHAR )
15485: OR
15486: ( p_create_update_flag = 'U' AND
15487: ( p_organization_rec.failure_score_commentary5 = FND_API.G_MISS_CHAR OR
15488: p_organization_rec.failure_score_commentary5 IS NOT NULL
15489: ) )
15490: THEN
15491: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15490: THEN
15491: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15492: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary5' );
15493: FND_MSG_PUB.ADD;
15494: x_return_status := FND_API.G_RET_STS_ERROR;
15495: END IF;
15496:
15497: IF ( p_create_update_flag = 'C' AND
15498: p_organization_rec.failure_score_commentary6 IS NOT NULL AND
15495: END IF;
15496:
15497: IF ( p_create_update_flag = 'C' AND
15498: p_organization_rec.failure_score_commentary6 IS NOT NULL AND
15499: p_organization_rec.failure_score_commentary6 <> FND_API.G_MISS_CHAR )
15500: OR
15501: ( p_create_update_flag = 'U' AND
15502: ( p_organization_rec.failure_score_commentary6 = FND_API.G_MISS_CHAR OR
15503: p_organization_rec.failure_score_commentary6 IS NOT NULL
15498: p_organization_rec.failure_score_commentary6 IS NOT NULL AND
15499: p_organization_rec.failure_score_commentary6 <> FND_API.G_MISS_CHAR )
15500: OR
15501: ( p_create_update_flag = 'U' AND
15502: ( p_organization_rec.failure_score_commentary6 = FND_API.G_MISS_CHAR OR
15503: p_organization_rec.failure_score_commentary6 IS NOT NULL
15504: ) )
15505: THEN
15506: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15505: THEN
15506: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15507: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary6' );
15508: FND_MSG_PUB.ADD;
15509: x_return_status := FND_API.G_RET_STS_ERROR;
15510: END IF;
15511:
15512: IF ( p_create_update_flag = 'C' AND
15513: p_organization_rec.failure_score_commentary7 IS NOT NULL AND
15510: END IF;
15511:
15512: IF ( p_create_update_flag = 'C' AND
15513: p_organization_rec.failure_score_commentary7 IS NOT NULL AND
15514: p_organization_rec.failure_score_commentary7 <> FND_API.G_MISS_CHAR )
15515: OR
15516: ( p_create_update_flag = 'U' AND
15517: ( p_organization_rec.failure_score_commentary7 = FND_API.G_MISS_CHAR OR
15518: p_organization_rec.failure_score_commentary7 IS NOT NULL
15513: p_organization_rec.failure_score_commentary7 IS NOT NULL AND
15514: p_organization_rec.failure_score_commentary7 <> FND_API.G_MISS_CHAR )
15515: OR
15516: ( p_create_update_flag = 'U' AND
15517: ( p_organization_rec.failure_score_commentary7 = FND_API.G_MISS_CHAR OR
15518: p_organization_rec.failure_score_commentary7 IS NOT NULL
15519: ) )
15520: THEN
15521: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15520: THEN
15521: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15522: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary7' );
15523: FND_MSG_PUB.ADD;
15524: x_return_status := FND_API.G_RET_STS_ERROR;
15525: END IF;
15526:
15527: IF ( p_create_update_flag = 'C' AND
15528: p_organization_rec.failure_score_commentary8 IS NOT NULL AND
15525: END IF;
15526:
15527: IF ( p_create_update_flag = 'C' AND
15528: p_organization_rec.failure_score_commentary8 IS NOT NULL AND
15529: p_organization_rec.failure_score_commentary8 <> FND_API.G_MISS_CHAR )
15530: OR
15531: ( p_create_update_flag = 'U' AND
15532: ( p_organization_rec.failure_score_commentary8 = FND_API.G_MISS_CHAR OR
15533: p_organization_rec.failure_score_commentary8 IS NOT NULL
15528: p_organization_rec.failure_score_commentary8 IS NOT NULL AND
15529: p_organization_rec.failure_score_commentary8 <> FND_API.G_MISS_CHAR )
15530: OR
15531: ( p_create_update_flag = 'U' AND
15532: ( p_organization_rec.failure_score_commentary8 = FND_API.G_MISS_CHAR OR
15533: p_organization_rec.failure_score_commentary8 IS NOT NULL
15534: ) )
15535: THEN
15536: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15535: THEN
15536: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15537: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary8' );
15538: FND_MSG_PUB.ADD;
15539: x_return_status := FND_API.G_RET_STS_ERROR;
15540: END IF;
15541:
15542: IF ( p_create_update_flag = 'C' AND
15543: p_organization_rec.failure_score_commentary9 IS NOT NULL AND
15540: END IF;
15541:
15542: IF ( p_create_update_flag = 'C' AND
15543: p_organization_rec.failure_score_commentary9 IS NOT NULL AND
15544: p_organization_rec.failure_score_commentary9 <> FND_API.G_MISS_CHAR )
15545: OR
15546: ( p_create_update_flag = 'U' AND
15547: ( p_organization_rec.failure_score_commentary9 = FND_API.G_MISS_CHAR OR
15548: p_organization_rec.failure_score_commentary9 IS NOT NULL
15543: p_organization_rec.failure_score_commentary9 IS NOT NULL AND
15544: p_organization_rec.failure_score_commentary9 <> FND_API.G_MISS_CHAR )
15545: OR
15546: ( p_create_update_flag = 'U' AND
15547: ( p_organization_rec.failure_score_commentary9 = FND_API.G_MISS_CHAR OR
15548: p_organization_rec.failure_score_commentary9 IS NOT NULL
15549: ) )
15550: THEN
15551: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15550: THEN
15551: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15552: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary9' );
15553: FND_MSG_PUB.ADD;
15554: x_return_status := FND_API.G_RET_STS_ERROR;
15555: END IF;
15556:
15557: IF ( p_create_update_flag = 'C' AND
15558: p_organization_rec.failure_score_commentary10 IS NOT NULL AND
15555: END IF;
15556:
15557: IF ( p_create_update_flag = 'C' AND
15558: p_organization_rec.failure_score_commentary10 IS NOT NULL AND
15559: p_organization_rec.failure_score_commentary10 <> FND_API.G_MISS_CHAR )
15560: OR
15561: ( p_create_update_flag = 'U' AND
15562: ( p_organization_rec.failure_score_commentary10 = FND_API.G_MISS_CHAR OR
15563: p_organization_rec.failure_score_commentary10 IS NOT NULL
15558: p_organization_rec.failure_score_commentary10 IS NOT NULL AND
15559: p_organization_rec.failure_score_commentary10 <> FND_API.G_MISS_CHAR )
15560: OR
15561: ( p_create_update_flag = 'U' AND
15562: ( p_organization_rec.failure_score_commentary10 = FND_API.G_MISS_CHAR OR
15563: p_organization_rec.failure_score_commentary10 IS NOT NULL
15564: ) )
15565: THEN
15566: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15565: THEN
15566: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15567: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_commentary10' );
15568: FND_MSG_PUB.ADD;
15569: x_return_status := FND_API.G_RET_STS_ERROR;
15570: END IF;
15571:
15572: IF ( p_create_update_flag = 'C' AND
15573: p_organization_rec.failure_score_date IS NOT NULL AND
15570: END IF;
15571:
15572: IF ( p_create_update_flag = 'C' AND
15573: p_organization_rec.failure_score_date IS NOT NULL AND
15574: p_organization_rec.failure_score_date <> FND_API.G_MISS_DATE )
15575: OR
15576: ( p_create_update_flag = 'U' AND
15577: ( p_organization_rec.failure_score_date = FND_API.G_MISS_DATE OR
15578: p_organization_rec.failure_score_date IS NOT NULL
15573: p_organization_rec.failure_score_date IS NOT NULL AND
15574: p_organization_rec.failure_score_date <> FND_API.G_MISS_DATE )
15575: OR
15576: ( p_create_update_flag = 'U' AND
15577: ( p_organization_rec.failure_score_date = FND_API.G_MISS_DATE OR
15578: p_organization_rec.failure_score_date IS NOT NULL
15579: ) )
15580: THEN
15581: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15580: THEN
15581: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15582: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_date' );
15583: FND_MSG_PUB.ADD;
15584: x_return_status := FND_API.G_RET_STS_ERROR;
15585: END IF;
15586:
15587: IF ( p_create_update_flag = 'C' AND
15588: p_organization_rec.failure_score_incd_default IS NOT NULL AND
15585: END IF;
15586:
15587: IF ( p_create_update_flag = 'C' AND
15588: p_organization_rec.failure_score_incd_default IS NOT NULL AND
15589: p_organization_rec.failure_score_incd_default <> FND_API.G_MISS_NUM )
15590: OR
15591: ( p_create_update_flag = 'U' AND
15592: ( p_organization_rec.failure_score_incd_default = FND_API.G_MISS_NUM OR
15593: p_organization_rec.failure_score_incd_default IS NOT NULL
15588: p_organization_rec.failure_score_incd_default IS NOT NULL AND
15589: p_organization_rec.failure_score_incd_default <> FND_API.G_MISS_NUM )
15590: OR
15591: ( p_create_update_flag = 'U' AND
15592: ( p_organization_rec.failure_score_incd_default = FND_API.G_MISS_NUM OR
15593: p_organization_rec.failure_score_incd_default IS NOT NULL
15594: ) )
15595: THEN
15596: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15595: THEN
15596: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15597: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_incd_default' );
15598: FND_MSG_PUB.ADD;
15599: x_return_status := FND_API.G_RET_STS_ERROR;
15600: END IF;
15601:
15602: IF ( p_create_update_flag = 'C' AND
15603: p_organization_rec.failure_score_natnl_percentile IS NOT NULL AND
15600: END IF;
15601:
15602: IF ( p_create_update_flag = 'C' AND
15603: p_organization_rec.failure_score_natnl_percentile IS NOT NULL AND
15604: p_organization_rec.failure_score_natnl_percentile <> FND_API.G_MISS_NUM)
15605: OR
15606: ( p_create_update_flag = 'U' AND
15607: ( p_organization_rec.failure_score_natnl_percentile = FND_API.G_MISS_NUM OR
15608: p_organization_rec.failure_score_natnl_percentile IS NOT NULL
15603: p_organization_rec.failure_score_natnl_percentile IS NOT NULL AND
15604: p_organization_rec.failure_score_natnl_percentile <> FND_API.G_MISS_NUM)
15605: OR
15606: ( p_create_update_flag = 'U' AND
15607: ( p_organization_rec.failure_score_natnl_percentile = FND_API.G_MISS_NUM OR
15608: p_organization_rec.failure_score_natnl_percentile IS NOT NULL
15609: ) )
15610: THEN
15611: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15610: THEN
15611: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15612: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_natnl_percentile' );
15613: FND_MSG_PUB.ADD;
15614: x_return_status := FND_API.G_RET_STS_ERROR;
15615: END IF;
15616:
15617: IF ( p_create_update_flag = 'C' AND
15618: p_organization_rec.failure_score_override_code IS NOT NULL AND
15615: END IF;
15616:
15617: IF ( p_create_update_flag = 'C' AND
15618: p_organization_rec.failure_score_override_code IS NOT NULL AND
15619: p_organization_rec.failure_score_override_code <> FND_API.G_MISS_CHAR)
15620: OR
15621: ( p_create_update_flag = 'U' AND
15622: ( p_organization_rec.failure_score_override_code = FND_API.G_MISS_CHAR OR
15623: p_organization_rec.failure_score_override_code IS NOT NULL
15618: p_organization_rec.failure_score_override_code IS NOT NULL AND
15619: p_organization_rec.failure_score_override_code <> FND_API.G_MISS_CHAR)
15620: OR
15621: ( p_create_update_flag = 'U' AND
15622: ( p_organization_rec.failure_score_override_code = FND_API.G_MISS_CHAR OR
15623: p_organization_rec.failure_score_override_code IS NOT NULL
15624: ) )
15625: THEN
15626: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15625: THEN
15626: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15627: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'failure_score_override_code' );
15628: FND_MSG_PUB.ADD;
15629: x_return_status := FND_API.G_RET_STS_ERROR;
15630: END IF;
15631:
15632: IF ( p_create_update_flag = 'C' AND
15633: p_organization_rec.global_failure_score IS NOT NULL AND
15630: END IF;
15631:
15632: IF ( p_create_update_flag = 'C' AND
15633: p_organization_rec.global_failure_score IS NOT NULL AND
15634: p_organization_rec.global_failure_score <> FND_API.G_MISS_CHAR)
15635: OR
15636: ( p_create_update_flag = 'U' AND
15637: ( p_organization_rec.global_failure_score = FND_API.G_MISS_CHAR OR
15638: p_organization_rec.global_failure_score IS NOT NULL
15633: p_organization_rec.global_failure_score IS NOT NULL AND
15634: p_organization_rec.global_failure_score <> FND_API.G_MISS_CHAR)
15635: OR
15636: ( p_create_update_flag = 'U' AND
15637: ( p_organization_rec.global_failure_score = FND_API.G_MISS_CHAR OR
15638: p_organization_rec.global_failure_score IS NOT NULL
15639: ) )
15640: THEN
15641: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15640: THEN
15641: FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
15642: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'global_failure_score' );
15643: FND_MSG_PUB.ADD;
15644: x_return_status := FND_API.G_RET_STS_ERROR;
15645: END IF;
15646:
15647: -- Debug info.
15648: /*IF G_DEBUG THEN
15672: IF p_forward_rel_code = p_backward_rel_code THEN
15673: IF p_forward_role <> p_backward_role THEN
15674: fnd_message.set_name('AR', 'HZ_INVALID_ROLE1');
15675: fnd_msg_pub.add;
15676: x_return_status := FND_API.G_RET_STS_ERROR;
15677: END IF;
15678: END IF;
15679:
15680: IF p_forward_rel_code <> p_backward_rel_code THEN
15680: IF p_forward_rel_code <> p_backward_rel_code THEN
15681: IF p_forward_role = p_backward_role THEN
15682: fnd_message.set_name('AR', 'HZ_INVALID_ROLE2');
15683: fnd_msg_pub.add;
15684: x_return_status := FND_API.G_RET_STS_ERROR;
15685: END IF;
15686: END IF;
15687:
15688: END validate_rel_code;
15707: * p_financial_report_rec Financial report record.
15708: * p_rowid Rowid of the record (used only in update mode).
15709: * IN/OUT:
15710: * x_return_status Return status after the call. The status can
15711: * be FND_API.G_RET_STS_SUCCESS (success),
15712: * fnd_api.g_ret_sts_error (error),
15713: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
15714: *
15715: * NOTES
15708: * p_rowid Rowid of the record (used only in update mode).
15709: * IN/OUT:
15710: * x_return_status Return status after the call. The status can
15711: * be FND_API.G_RET_STS_SUCCESS (success),
15712: * fnd_api.g_ret_sts_error (error),
15713: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
15714: *
15715: * NOTES
15716: *
15709: * IN/OUT:
15710: * x_return_status Return status after the call. The status can
15711: * be FND_API.G_RET_STS_SUCCESS (success),
15712: * fnd_api.g_ret_sts_error (error),
15713: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
15714: *
15715: * NOTES
15716: *
15717: * MODIFICATION HISTORY
15908: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
15909: --check for unique financial_report_id
15910: IF p_create_update_flag = 'C' THEN
15911: IF p_financial_report_rec.financial_report_id IS NOT NULL AND
15912: p_financial_report_rec.financial_report_id <> fnd_api.g_miss_num
15913: THEN
15914: OPEN c_dup_financial_report_id (p_financial_report_rec.financial_report_id);
15915: FETCH c_dup_financial_report_id INTO l_dummy;
15916:
15918: IF NVL(c_dup_financial_report_id%FOUND, FALSE) THEN
15919: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
15920: fnd_message.set_token('COLUMN', 'financial_report_id');
15921: fnd_msg_pub.add;
15922: x_return_status := fnd_api.g_ret_sts_error;
15923: END IF;
15924: CLOSE c_dup_financial_report_id;
15925:
15926: /*IF g_debug THEN
16031: IF p_create_update_flag = 'C'
16032: AND
16033: p_financial_report_rec.party_id IS NOT NULL
16034: AND
16035: p_financial_report_rec.party_id <> fnd_api.g_miss_num
16036: THEN
16037: BEGIN
16038: SELECT 'Y'
16039: INTO l_dummy
16046: fnd_message.set_token('FK', 'party_id');
16047: fnd_message.set_token('COLUMN', 'party_id');
16048: fnd_message.set_token('TABLE', 'hz_parties');
16049: fnd_msg_pub.add;
16050: x_return_status := fnd_api.g_ret_sts_error;
16051: END;
16052: */
16053: /*IF g_debug THEN
16054: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
16072: p_create_update_flag = 'U'
16073: If the value is NULL / G_MISS, error should be thrown in validate_mandatory only.
16074: */
16075: AND p_financial_report_rec.party_id IS NOT NULL
16076: AND p_financial_report_rec.party_id <> FND_API.G_MISS_NUM
16077: THEN
16078: --Bug 2886268: Added the following code instead of calling check_organization
16079: OPEN c_partytype;
16080:
16087: fnd_message.set_name('AR', 'HZ_API_INVALID_PARTY_TYPE');
16088: fnd_message.set_token('PARTY_ID', TO_CHAR(p_financial_report_rec.party_id));
16089: fnd_message.set_token('TYPE', 'ORGANIZATION');
16090: fnd_msg_pub.add;
16091: x_return_status := fnd_api.g_ret_sts_error;
16092: END IF;
16093: ELSE -- 3456205
16094: -- party_id has foreign key HZ_PARTIES.PARTY_ID
16095: -- However since no record found in HZ_PARTIES, therefore throw error.
16097: fnd_message.set_token('FK', 'party_id');
16098: fnd_message.set_token('COLUMN', 'party_id');
16099: fnd_message.set_token('TABLE', 'hz_parties');
16100: fnd_msg_pub.add;
16101: x_return_status := fnd_api.g_ret_sts_error;
16102: END IF;
16103: CLOSE c_partytype;
16104: --Bug 2886268: Commented out the following call as it is throwing erroneous message
16105: --check_organization(p_financial_report_rec.party_id, x_return_status);
16125: --during creation that only one of the fields is being provided.
16126:
16127: /*
16128: |IF ((nvl(p_financial_report_rec.issued_period, l_issued_period) IS NOT NULL AND
16129: | nvl(p_financial_report_rec.issued_period, l_issued_period) <> fnd_api.g_miss_char) AND
16130: | (nvl(p_financial_report_rec.report_start_date, l_report_start_date) IS NOT NULL AND
16131: | nvl(p_financial_report_rec.report_start_date, l_report_start_date) <> fnd_api.g_miss_date))
16132: |THEN
16133: | fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
16127: /*
16128: |IF ((nvl(p_financial_report_rec.issued_period, l_issued_period) IS NOT NULL AND
16129: | nvl(p_financial_report_rec.issued_period, l_issued_period) <> fnd_api.g_miss_char) AND
16130: | (nvl(p_financial_report_rec.report_start_date, l_report_start_date) IS NOT NULL AND
16131: | nvl(p_financial_report_rec.report_start_date, l_report_start_date) <> fnd_api.g_miss_date))
16132: |THEN
16133: | fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
16134: | fnd_message.set_token('COLUMN1', 'issued_period');
16135: | fnd_message.set_token('COLUMN2', 'report_start_date');
16133: | fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
16134: | fnd_message.set_token('COLUMN1', 'issued_period');
16135: | fnd_message.set_token('COLUMN2', 'report_start_date');
16136: | fnd_msg_pub.add;
16137: | x_return_status := fnd_api.g_ret_sts_error;
16138: |END IF;
16139: */
16140:
16141: --Bug Number 3148753.As both the fields are non-updateable,therefore validation
16148: (--Both the values are not set during creation.
16149: (
16150: p_financial_report_rec.issued_period IS NULL
16151: OR
16152: p_financial_report_rec.issued_period = fnd_api.g_miss_char
16153: )
16154: AND
16155: (
16156: p_financial_report_rec.report_start_date IS NULL
16154: AND
16155: (
16156: p_financial_report_rec.report_start_date IS NULL
16157: OR
16158: p_financial_report_rec.report_start_date = fnd_api.g_miss_date
16159: )
16160: /* Bug 3539597.Commented out changes incorporated in fix 3200870.
16161: | AND
16162: | (
16161: | AND
16162: | (
16163: | p_financial_report_rec.DATE_REPORT_ISSUED IS NULL
16164: | OR
16165: | p_financial_report_rec.DATE_REPORT_ISSUED = fnd_api.g_miss_date
16166: | )
16167: */
16168: )
16169: OR
16170: (--Both the values are provided during creation
16171: (
16172: p_financial_report_rec.issued_period IS NOT NULL
16173: AND
16174: p_financial_report_rec.issued_period <> fnd_api.g_miss_char
16175: )
16176: AND
16177: (
16178: p_financial_report_rec.report_start_date IS NOT NULL
16176: AND
16177: (
16178: p_financial_report_rec.report_start_date IS NOT NULL
16179: AND
16180: p_financial_report_rec.report_start_date <> fnd_api.g_miss_date
16181: )
16182: )
16183: )
16184: THEN
16185: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
16186: fnd_message.set_token('COLUMN1', 'issued_period');
16187: fnd_message.set_token('COLUMN2', 'report_start_date');
16188: fnd_msg_pub.add;
16189: x_return_status := fnd_api.g_ret_sts_error;
16190: END IF;
16191: END IF;
16192:
16193: -----------------------------------
16197: -- bug 4417943.
16198: IF p_create_update_flag = 'C' /*Bug 3456205 Both these columns are non-updateable */
16199: AND (
16200: (p_financial_report_rec.report_start_date IS NOT NULL
16201: AND p_financial_report_rec.report_start_date <> fnd_api.g_miss_date
16202: AND (p_financial_report_rec.report_end_date IS NULL OR
16203: p_financial_report_rec.report_end_date = fnd_api.g_miss_date )
16204: ) OR
16205: (p_financial_report_rec.report_end_date IS NOT NULL
16199: AND (
16200: (p_financial_report_rec.report_start_date IS NOT NULL
16201: AND p_financial_report_rec.report_start_date <> fnd_api.g_miss_date
16202: AND (p_financial_report_rec.report_end_date IS NULL OR
16203: p_financial_report_rec.report_end_date = fnd_api.g_miss_date )
16204: ) OR
16205: (p_financial_report_rec.report_end_date IS NOT NULL
16206: AND p_financial_report_rec.report_end_date <> fnd_api.g_miss_date
16207: AND (p_financial_report_rec.report_start_date IS NULL OR
16202: AND (p_financial_report_rec.report_end_date IS NULL OR
16203: p_financial_report_rec.report_end_date = fnd_api.g_miss_date )
16204: ) OR
16205: (p_financial_report_rec.report_end_date IS NOT NULL
16206: AND p_financial_report_rec.report_end_date <> fnd_api.g_miss_date
16207: AND (p_financial_report_rec.report_start_date IS NULL OR
16208: p_financial_report_rec.report_start_date = fnd_api.g_miss_date)
16209: ))
16210: THEN
16204: ) OR
16205: (p_financial_report_rec.report_end_date IS NOT NULL
16206: AND p_financial_report_rec.report_end_date <> fnd_api.g_miss_date
16207: AND (p_financial_report_rec.report_start_date IS NULL OR
16208: p_financial_report_rec.report_start_date = fnd_api.g_miss_date)
16209: ))
16210: THEN
16211: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION3');
16212: fnd_message.set_token('COLUMN1', 'report_start_date');
16211: fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION3');
16212: fnd_message.set_token('COLUMN1', 'report_start_date');
16213: fnd_message.set_token('COLUMN2', 'report_end_date');
16214: fnd_msg_pub.add;
16215: x_return_status := fnd_api.g_ret_sts_error;
16216: END IF;
16217:
16218: --Bug 2888670: Modified the end_date validation as per V1 validation.
16219: --If report_start_date is provided, then it must be less than or equal
16220: --to report_end_date.
16221:
16222: IF p_create_update_flag = 'C' THEN
16223: IF p_financial_report_rec.report_end_date is NOT NULL AND
16224: p_financial_report_rec.report_end_date <> FND_API.G_MISS_DATE THEN
16225: if (p_financial_report_rec.report_end_date
16226: < p_financial_report_rec.report_start_date
16227: ) THEN
16228: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16226: < p_financial_report_rec.report_start_date
16227: ) THEN
16228: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16229: FND_MSG_PUB.ADD;
16230: x_return_status := FND_API.G_RET_STS_ERROR;
16231: end if;
16232: END IF;
16233: END IF;
16234:
16237:
16238: -- compare end_date with database data and user passed data.
16239: ELSIF p_create_update_flag = 'U' THEN
16240: if (p_financial_report_rec.report_end_date is NOT NULL AND
16241: p_financial_report_rec.report_end_date <> FND_API.G_MISS_DATE) THEN
16242: if p_financial_report_rec.report_start_date is NOT NULL AND
16243: p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE then
16244: if p_financial_report_rec.report_end_date
16245: < p_financial_report_rec.report_start_date then
16239: ELSIF p_create_update_flag = 'U' THEN
16240: if (p_financial_report_rec.report_end_date is NOT NULL AND
16241: p_financial_report_rec.report_end_date <> FND_API.G_MISS_DATE) THEN
16242: if p_financial_report_rec.report_start_date is NOT NULL AND
16243: p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE then
16244: if p_financial_report_rec.report_end_date
16245: < p_financial_report_rec.report_start_date then
16246: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16247: FND_MSG_PUB.ADD;
16244: if p_financial_report_rec.report_end_date
16245: < p_financial_report_rec.report_start_date then
16246: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16247: FND_MSG_PUB.ADD;
16248: x_return_status := FND_API.G_RET_STS_ERROR;
16249:
16250: end if;
16251: elsif ( p_financial_report_rec.report_end_date < l_report_start_date OR
16252: l_report_start_date is NULL) then
16251: elsif ( p_financial_report_rec.report_end_date < l_report_start_date OR
16252: l_report_start_date is NULL) then
16253: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16254: FND_MSG_PUB.ADD;
16255: x_return_status := FND_API.G_RET_STS_ERROR;
16256:
16257: end if;
16258: elsif (p_financial_report_rec.report_start_date is NOT NULL AND
16259: p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE) THEN
16255: x_return_status := FND_API.G_RET_STS_ERROR;
16256:
16257: end if;
16258: elsif (p_financial_report_rec.report_start_date is NOT NULL AND
16259: p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE) THEN
16260: if l_report_end_date < p_financial_report_rec.report_start_date then
16261: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16262: FND_MSG_PUB.ADD;
16263: x_return_status := FND_API.G_RET_STS_ERROR;
16259: p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE) THEN
16260: if l_report_end_date < p_financial_report_rec.report_start_date then
16261: FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
16262: FND_MSG_PUB.ADD;
16263: x_return_status := FND_API.G_RET_STS_ERROR;
16264:
16265: end if;
16266: end if;
16267:
16285: -- combination key is not unique, push an error onto the stack.
16286: IF NVL(c_unique_financial_report_rec%FOUND, FALSE) THEN
16287: fnd_message.set_name('AR', 'HZ_API_DUP_FIN_REPORT_REC');
16288: fnd_msg_pub.add;
16289: x_return_status := fnd_api.g_ret_sts_error;
16290: END IF;
16291: CLOSE c_unique_financial_report_rec;
16292:
16293: /*IF g_debug THEN
16776: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
16777: -- status is lookup code in lookup type REGISTRY_STATUS
16778: IF p_financial_report_rec.status IS NOT NULL
16779: AND
16780: p_financial_report_rec.status <> fnd_api.g_miss_char
16781: AND
16782: (p_create_update_flag = 'C'
16783: OR
16784: (p_create_update_flag = 'U'
16782: (p_create_update_flag = 'C'
16783: OR
16784: (p_create_update_flag = 'U'
16785: AND
16786: p_financial_report_rec.status <> NVL(l_status, fnd_api.g_miss_char)
16787: )
16788: )
16789: THEN
16790: validate_lookup (
16916: * p_financial_number_rec Financial number record.
16917: * p_rowid Rowid of the record (used only in update mode).
16918: * IN/OUT:
16919: * x_return_status Return status after the call. The status can
16920: * be FND_API.G_RET_STS_SUCCESS (success),
16921: * fnd_api.g_ret_sts_error (error),
16922: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
16923: *
16924: * NOTES
16917: * p_rowid Rowid of the record (used only in update mode).
16918: * IN/OUT:
16919: * x_return_status Return status after the call. The status can
16920: * be FND_API.G_RET_STS_SUCCESS (success),
16921: * fnd_api.g_ret_sts_error (error),
16922: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
16923: *
16924: * NOTES
16925: *
16918: * IN/OUT:
16919: * x_return_status Return status after the call. The status can
16920: * be FND_API.G_RET_STS_SUCCESS (success),
16921: * fnd_api.g_ret_sts_error (error),
16922: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
16923: *
16924: * NOTES
16925: *
16926: * MODIFICATION HISTORY
16995: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
16996: --check for unique financial_number_id
16997: IF p_create_update_flag = 'C' THEN
16998: IF p_financial_number_rec.financial_number_id IS NOT NULL AND
16999: p_financial_number_rec.financial_number_id <> fnd_api.g_miss_num
17000: THEN
17001: OPEN c_dup_financial_number_id (p_financial_number_rec.financial_number_id);
17002: FETCH c_dup_financial_number_id INTO l_dummy;
17003:
17005: IF NVL(c_dup_financial_number_id%FOUND, FALSE) THEN
17006: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
17007: fnd_message.set_token('COLUMN', 'financial_number_id');
17008: fnd_msg_pub.add;
17009: x_return_status := fnd_api.g_ret_sts_error;
17010: END IF;
17011: CLOSE c_dup_financial_number_id;
17012:
17013: /*IF g_debug THEN
17070: IF p_create_update_flag = 'C'
17071: AND
17072: p_financial_number_rec.financial_report_id IS NOT NULL
17073: AND
17074: p_financial_number_rec.financial_report_id <> fnd_api.g_miss_num
17075: THEN
17076: BEGIN
17077: --bug 3942332: selected actual_content_source from the hz_financial_reports record.
17078: --SELECT 'Y'
17088: fnd_message.set_token('FK', 'financial_report_id');
17089: fnd_message.set_token('COLUMN', 'financial_report_id');
17090: fnd_message.set_token('TABLE', 'hz_financial_reports');
17091: fnd_msg_pub.add;
17092: x_return_status := fnd_api.g_ret_sts_error;
17093: END;
17094:
17095: /*IF g_debug THEN
17096: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
17164: -- combination key is not unique, push an error onto the stack.
17165: IF NVL(c_unique_financial_number_rec%FOUND, FALSE) THEN
17166: fnd_message.set_name('AR', 'HZ_API_DUP_FIN_NUMBER_REC');
17167: fnd_msg_pub.add;
17168: x_return_status := fnd_api.g_ret_sts_error;
17169: END IF;
17170: CLOSE c_unique_financial_number_rec;
17171:
17172: /*IF g_debug THEN
17238: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
17239: -- status is lookup code in lookup type REGISTRY_STATUS
17240: IF p_financial_number_rec.status IS NOT NULL
17241: AND
17242: p_financial_number_rec.status <> fnd_api.g_miss_char
17243: AND
17244: (p_create_update_flag = 'C'
17245: OR
17246: (p_create_update_flag = 'U'
17244: (p_create_update_flag = 'C'
17245: OR
17246: (p_create_update_flag = 'U'
17247: AND
17248: p_financial_number_rec.status <> NVL(l_status, fnd_api.g_miss_char)
17249: )
17250: )
17251: THEN
17252: validate_lookup (
17316: * p_credit_rating_rec Credit rating record.
17317: * p_rowid Rowid of the record (used only in update mode).
17318: * IN/OUT:
17319: * x_return_status Return status after the call. The status can
17320: * be FND_API.G_RET_STS_SUCCESS (success),
17321: * fnd_api.g_ret_sts_error (error),
17322: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
17323: *
17324: * NOTES
17317: * p_rowid Rowid of the record (used only in update mode).
17318: * IN/OUT:
17319: * x_return_status Return status after the call. The status can
17320: * be FND_API.G_RET_STS_SUCCESS (success),
17321: * fnd_api.g_ret_sts_error (error),
17322: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
17323: *
17324: * NOTES
17325: *
17318: * IN/OUT:
17319: * x_return_status Return status after the call. The status can
17320: * be FND_API.G_RET_STS_SUCCESS (success),
17321: * fnd_api.g_ret_sts_error (error),
17322: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
17323: *
17324: * NOTES
17325: *
17326: * MODIFICATION HISTORY
17520: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
17521: --check for unique credit_rating_id
17522: IF p_create_update_flag = 'C' THEN
17523: IF p_credit_rating_rec.credit_rating_id IS NOT NULL AND
17524: p_credit_rating_rec.credit_rating_id <> fnd_api.g_miss_num
17525: THEN
17526: OPEN c_dup_credit_rating_id (p_credit_rating_rec.credit_rating_id);
17527: FETCH c_dup_credit_rating_id INTO l_dummy;
17528:
17530: IF NVL(c_dup_credit_rating_id%FOUND, FALSE) THEN
17531: fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
17532: fnd_message.set_token('COLUMN', 'credit_rating_id');
17533: fnd_msg_pub.add;
17534: x_return_status := fnd_api.g_ret_sts_error;
17535: END IF;
17536: CLOSE c_dup_credit_rating_id;
17537:
17538: /*IF g_debug THEN
17616: IF p_create_update_flag = 'C'
17617: AND
17618: p_credit_rating_rec.party_id IS NOT NULL
17619: AND
17620: p_credit_rating_rec.party_id <> fnd_api.g_miss_num
17621: THEN
17622: BEGIN
17623: SELECT 'Y'
17624: INTO l_dummy
17631: fnd_message.set_token('FK', 'party_id');
17632: fnd_message.set_token('COLUMN', 'party_id');
17633: fnd_message.set_token('TABLE', 'hz_parties');
17634: fnd_msg_pub.add;
17635: x_return_status := fnd_api.g_ret_sts_error;
17636: END;
17637:
17638: /*IF g_debug THEN
17639: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
17691: | -- combination key is not unique, push an error onto the stack.
17692: | IF NVL(c_unique_credit_rating_rec%FOUND, FALSE) THEN
17693: | fnd_message.set_name('AR', 'HZ_API_DUP_CREDIT_RATING_REC');
17694: | fnd_msg_pub.add;
17695: | x_return_status := fnd_api.g_ret_sts_error;
17696: | END IF;
17697: | CLOSE c_unique_credit_rating_rec;
17698: |
17699: | IF g_debug THEN
18434: IF p_create_update_flag = 'C'
18435: AND
18436: p_credit_rating_rec.maximum_credit_currency_code IS NOT NULL
18437: AND
18438: p_credit_rating_rec.maximum_credit_currency_code <> fnd_api.g_miss_char
18439: THEN
18440: BEGIN
18441: --Bug 2898670: Changed the validation as the foreign key validation
18442: --has to be against active records
18455: fnd_message.set_token('FK', 'currency_code');
18456: fnd_message.set_token('COLUMN', 'maximum_credit_currency_code');
18457: fnd_message.set_token('TABLE', 'fnd_currencies');
18458: fnd_msg_pub.add;
18459: x_return_status := fnd_api.g_ret_sts_error;
18460: END;
18461:
18462: /*IF g_debug THEN
18463: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
18482: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
18483: -- status is lookup code in lookup type REGISTRY_STATUS
18484: IF p_credit_rating_rec.status IS NOT NULL
18485: AND
18486: p_credit_rating_rec.status <> fnd_api.g_miss_char
18487: AND
18488: (p_create_update_flag = 'C'
18489: OR
18490: (p_create_update_flag = 'U'
18488: (p_create_update_flag = 'C'
18489: OR
18490: (p_create_update_flag = 'U'
18491: AND
18492: p_credit_rating_rec.status <> NVL(l_status, fnd_api.g_miss_char)
18493: )
18494: )
18495: THEN
18496: validate_lookup (
18528: --------------------------------------------------------
18529: -- This column should have a value within the range 0 - 100.
18530:
18531: IF p_credit_rating_rec.credit_score_natl_percentile IS NOT NULL AND
18532: p_credit_rating_rec.credit_score_natl_percentile <> FND_API.G_MISS_NUM AND
18533: (p_create_update_flag = 'C' OR
18534: (
18535: p_create_update_flag = 'U' AND
18536: p_credit_rating_rec.credit_score_natl_percentile <> l_credit_score_natl_percentile
18543: FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_PERCENTAGE');
18544: FND_MESSAGE.SET_TOKEN('INVALIDVAL', p_credit_rating_rec.credit_score_natl_percentile);
18545: FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'credit_score_natl_percentile');
18546: FND_MSG_PUB.ADD;
18547: x_return_status := fnd_api.g_ret_sts_error;
18548: END IF;
18549: END IF;
18550:
18551: ----------------------------------------------------------
18553: ----------------------------------------------------------
18554: -- This column should have a value within the range 0 - 100.
18555:
18556: IF p_credit_rating_rec.failure_score_natnl_percentile IS NOT NULL AND
18557: p_credit_rating_rec.failure_score_natnl_percentile <> FND_API.G_MISS_NUM AND
18558: (
18559: p_create_update_flag = 'C' OR
18560: (
18561: p_create_update_flag = 'U' AND
18569: FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_PERCENTAGE');
18570: FND_MESSAGE.SET_TOKEN('INVALIDVAL', p_credit_rating_rec.failure_score_natnl_percentile);
18571: FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'failure_score_natnl_percentile');
18572: FND_MSG_PUB.ADD;
18573: x_return_status := fnd_api.g_ret_sts_error;
18574: END IF;
18575: END IF;
18576:
18577:
18707: END IF;
18708:
18709: -- created_by_module is lookup code in lookup type HZ_CREATED_BY_MODULES
18710: IF p_created_by_module IS NOT NULL AND
18711: p_created_by_module <> fnd_api.g_miss_char AND
18712: (p_create_update_flag = 'C' OR
18713: (p_create_update_flag = 'U' AND
18714: (p_old_created_by_module IS NULL OR
18715: p_created_by_module <> p_old_created_by_module)))