[Home] [Help]
59: );
60:
61: -- Bug 3175816
62: PROCEDURE validate_global_loc_num(
63: global_location_number IN HZ_PARTY_SITES.GLOBAL_LOCATION_NUMBER%TYPE,
64: x_return_status IN OUT NOCOPY VARCHAR2
65: );
66:
67: PROCEDURE validate_created_by_module (
1192: WHERE party_id = p_owner_table_id;
1193:
1194: CURSOR c_psexist (p_owner_table_id IN NUMBER) IS
1195: SELECT 'Y'
1196: FROM hz_party_sites hps
1197: WHERE hps.party_site_id = p_owner_table_id;
1198:
1199: CURSOR c_timezone (p_timezone_id IN NUMBER) IS--updated against bug 7046491
1200: SELECT 'Y'
1562: END IF;
1563:
1564: -- owner_table_id is foreign key of hz_parties if
1565: -- owner_table_name = HZ_PARTIES and is foreign key
1566: -- of hz_party_sites if owner_table_name = HZ_PARTY_SITES.
1567:
1568: -- Do not need to check during update because owner_table_id is
1569: -- non-updateable.
1570: IF p_create_update_flag = 'C' THEN
1577: l_fk_column := 'party_id';
1578: END IF;
1579:
1580: CLOSE c_pexist;
1581: ELSIF p_contact_point_rec.owner_table_name = 'HZ_PARTY_SITES' THEN
1582: OPEN c_psexist(p_contact_point_rec.owner_table_id);
1583: FETCH c_psexist INTO l_fk_exist;
1584:
1585: IF c_psexist%NOTFOUND THEN
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,
1607: p_message=>'owner_table_id is foreign key of hz_parties if owner_table_name = HZ_PARTIES and is foreign key of hz_party_sites if owner_table_name = HZ_PARTY_SITES. ' ||
1608: 'x_return_status = ' || x_return_status,
1609: p_msg_level=>fnd_log.level_statement);
1610: END IF;
1611:
5762: /**
5763: * PROCEDURE validate_global_loc_num
5764: *
5765: * DESCRIPTION
5766: * Validates GLOBAL_LOCATION_NUMBER in HZ_PARTY_SITES for check-digit.
5767: *
5768: * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
5769: *
5770: * ARGUMENTS
5769: *
5770: * ARGUMENTS
5771: *
5772: * IN:
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),
5788: *
5789: */
5790:
5791: PROCEDURE validate_global_loc_num (
5792: global_location_number IN HZ_PARTY_SITES.GLOBAL_LOCATION_NUMBER%TYPE,
5793: x_return_status IN OUT NOCOPY VARCHAR2
5794: ) IS
5795: even_sum NUMBER := 0;
5796: odd_sum NUMBER := 0;
5931: l_validate_osr varchar2(1) := 'Y';
5932: l_mosr_owner_table_id number;
5933:
5934: -- Bug 3175816
5935: l_global_location_number HZ_PARTY_SITES.GLOBAL_LOCATION_NUMBER%TYPE;
5936:
5937: l_temp_return_status VARCHAR2(10); -- for storing return status from
5938: -- hz_orig_system_ref_pub.get_owner_table_id
5939: BEGIN
5975: l_created_by_module,
5976: l_application_id,
5977: db_actual_content_source,
5978: l_global_location_number
5979: FROM HZ_PARTY_SITES
5980: WHERE ROWID = p_rowid;
5981: END IF;
5982:
5983: --------------------
6126: THEN
6127: BEGIN
6128:
6129: -- Bug 2197181: for mix-n-match, column actual_content_source
6130: -- was added to hz_party_sites. It is denormalized from hz_locations.
6131: -- Therefore, it is selected from hz_locations to be passed back
6132: -- to create_party_site API.
6133:
6134: SELECT actual_content_source
6199: then
6200: hz_orig_system_ref_pub.get_owner_table_id
6201: (p_orig_system => p_party_site_rec.orig_system,
6202: p_orig_system_reference => p_party_site_rec.orig_system_reference,
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
6612: END IF;
6613:
6614: END IF;
6615:
6616: -- party_site_id is foreign key of hz_party_sites.party_site_id
6617: -- Do not need to check during update because party_site_id is
6618: -- non-updateable.
6619: IF p_create_update_flag = 'C'
6620: AND
6624: THEN
6625: BEGIN
6626: SELECT 'Y'
6627: INTO l_dummy
6628: FROM HZ_PARTY_SITES
6629: WHERE PARTY_SITE_ID = p_party_site_use_rec.party_site_id;
6630: EXCEPTION
6631: WHEN NO_DATA_FOUND THEN
6632: fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
6631: WHEN NO_DATA_FOUND THEN
6632: fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
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:
6638: END;
6639:
6640: /*IF g_debug THEN
6641: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
6642: 'party_site_id is foreign key of hz_party_sites.party_site_id. ' ||
6643: 'x_return_status = ' || x_return_status, l_debug_prefix);
6644: END IF;
6645: */
6646: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6644: END IF;
6645: */
6646: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6647: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
6648: p_message=>'party_site_id is foreign key of hz_party_sites.party_site_id. ' ||
6649: 'x_return_status = ' || x_return_status,
6650: p_msg_level=>fnd_log.level_statement);
6651: END IF;
6652:
7169: -- validate party_site_id
7170: -------------------------
7171:
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
7177: THEN
7178: BEGIN
7179: SELECT 'Y'
7180: INTO l_dummy
7181: FROM HZ_PARTY_SITES
7182: WHERE PARTY_SITE_ID = p_org_contact_rec.party_site_id;
7183: EXCEPTION
7184: WHEN NO_DATA_FOUND THEN
7185: fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
7184: WHEN NO_DATA_FOUND THEN
7185: fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
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:
7191: END;
7192:
7193: /*IF g_debug THEN
7194: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
7195: 'party_site_id is foreign key to hz_party_sites.party_site_id. ' ||
7196: 'x_return_status = ' || x_return_status, l_debug_prefix);
7197: END IF;
7198: */
7199: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
7197: END IF;
7198: */
7199: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
7200: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
7201: p_message=>'party_site_id is foreign key to hz_party_sites.party_site_id. ' ||
7202: 'x_return_status = ' || x_return_status,
7203: p_msg_level=>fnd_log.level_statement);
7204: END IF;
7205:
7214: THEN
7215: BEGIN
7216: SELECT 'Y'
7217: INTO l_dummy
7218: FROM HZ_PARTY_SITES
7219: WHERE PARTY_SITE_ID = p_org_contact_rec.party_site_id
7220: AND PARTY_ID = p_org_contact_rec.party_rel_rec.object_id;
7221: EXCEPTION
7222: WHEN NO_DATA_FOUND THEN
11689: SELECT 'Y'
11690: INTO l_is_remit_to_location
11691: FROM DUAL
11692: WHERE EXISTS (SELECT 1
11693: FROM hz_party_sites ps
11694: WHERE ps.location_id = l_location_id
11695: AND ps.party_id = -1
11696: );
11697: EXCEPTION