[Home] [Help]
1258: END IF;
1259:
1260: END IF;
1261:
1262: -- Primary name should be of subtype 'STANDARD_NAME'
1263: IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.primary_flag = 'Y') THEN
1264: IF p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME' THEN
1265: FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_INVALID_SUBTYPE' );
1266: FND_MESSAGE.SET_TOKEN( 'SUBTYPE', p_geo_identifier_rec.identifier_subtype);
1260: END IF;
1261:
1262: -- Primary name should be of subtype 'STANDARD_NAME'
1263: IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.primary_flag = 'Y') THEN
1264: IF p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME' THEN
1265: FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_INVALID_SUBTYPE' );
1266: FND_MESSAGE.SET_TOKEN( 'SUBTYPE', p_geo_identifier_rec.identifier_subtype);
1267: FND_MSG_PUB.ADD;
1268: x_return_status := FND_API.G_RET_STS_ERROR;
1270: END IF;
1271: END IF;
1272:
1273: -- If this is the first row for the language_code for type NAME then if it is of subtype other than STANDAR_NAME,
1274: -- , make it as STANDARD_NAME
1275:
1276: IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME') THEN
1277:
1278: SELECT count(*) INTO l_count from
1272:
1273: -- If this is the first row for the language_code for type NAME then if it is of subtype other than STANDAR_NAME,
1274: -- , make it as STANDARD_NAME
1275:
1276: IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME') THEN
1277:
1278: SELECT count(*) INTO l_count from
1279: hz_geography_identifiers
1280: WHERE geography_id = p_geo_identifier_rec.geography_id
1280: WHERE geography_id = p_geo_identifier_rec.geography_id
1281: AND language_code = l_language_code;
1282:
1283: IF l_count = 0 THEN
1284: l_identifier_subtype:= 'STANDARD_NAME';
1285: END IF;
1286: END IF;
1287:
1288: IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1285: END IF;
1286: END IF;
1287:
1288: IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1289: IF (p_geo_identifier_rec.identifier_type='NAME' AND p_geo_identifier_rec.identifier_subtype = 'STANDARD_NAME') THEN
1290: -- check if there exists a STANDARD_NAME + Primary Flag = Y
1291: SELECT count(*) INTO l_count
1292: FROM hz_geography_identifiers
1293: WHERE geography_id = p_geo_identifier_rec.geography_id
1286: END IF;
1287:
1288: IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1289: IF (p_geo_identifier_rec.identifier_type='NAME' AND p_geo_identifier_rec.identifier_subtype = 'STANDARD_NAME') THEN
1290: -- check if there exists a STANDARD_NAME + Primary Flag = Y
1291: SELECT count(*) INTO l_count
1292: FROM hz_geography_identifiers
1293: WHERE geography_id = p_geo_identifier_rec.geography_id
1294: AND identifier_type = 'NAME'
1291: SELECT count(*) INTO l_count
1292: FROM hz_geography_identifiers
1293: WHERE geography_id = p_geo_identifier_rec.geography_id
1294: AND identifier_type = 'NAME'
1295: AND identifier_subtype = 'STANDARD_NAME'
1296: AND primary_flag = 'Y'
1297: AND language_code = l_language_code;
1298:
1299: IF l_count > 0 THEN
1296: AND primary_flag = 'Y'
1297: AND language_code = l_language_code;
1298:
1299: IF l_count > 0 THEN
1300: -- update STANDARD_NAME+Y to STANDARD_NAME+N
1301: UPDATE hz_geography_identifiers
1302: SET primary_flag = 'N'
1303: WHERE geography_id = p_geo_identifier_rec.geography_id
1304: AND identifier_type = 'NAME'
1301: UPDATE hz_geography_identifiers
1302: SET primary_flag = 'N'
1303: WHERE geography_id = p_geo_identifier_rec.geography_id
1304: AND identifier_type = 'NAME'
1305: AND identifier_subtype = 'STANDARD_NAME'
1306: AND primary_flag = 'Y'
1307: AND language_code = l_language_code;
1308: END IF;
1309:
1306: AND primary_flag = 'Y'
1307: AND language_code = l_language_code;
1308: END IF;
1309:
1310: l_identifier_subtype := 'STANDARD_NAME';
1311: END IF;
1312:
1313: --check if there exists a primary row already for this geography_id
1314: SELECT count(*) INTO l_count
1548: ELSE -- new geo subtype is null (i.e. no need to update), use the old subtype
1549: l_new_geo_subtype := l_geo_identifier_subtype;
1550: l_subtype_updated := 'N';
1551: END IF;
1552: ELSE -- if idenifier type is NAME (only 1 subtype 'STANDARD_NAME' is allowed)
1553: l_new_geo_subtype := p_geo_identifier_rec.new_identifier_subtype;
1554: -- validate new subtype
1555: IF (l_new_geo_subtype IS NOT NULL) THEN
1556: IF (p_geo_identifier_rec.new_identifier_subtype <> 'STANDARD_NAME') THEN
1552: ELSE -- if idenifier type is NAME (only 1 subtype 'STANDARD_NAME' is allowed)
1553: l_new_geo_subtype := p_geo_identifier_rec.new_identifier_subtype;
1554: -- validate new subtype
1555: IF (l_new_geo_subtype IS NOT NULL) THEN
1556: IF (p_geo_identifier_rec.new_identifier_subtype <> 'STANDARD_NAME') THEN
1557: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
1558: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'identifier_subtype' );
1559: FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', 'HZ_GEO_IDENTIFIER_SUBTYPE' );
1560: FND_MSG_PUB.ADD;
1802: x_return_status := FND_API.G_RET_STS_ERROR;
1803: RAISE FND_API.G_EXC_ERROR;
1804: END IF;
1805:
1806: -- If a STANDARD_NAME is being deleted , if there exists another name mark it as STANDARD and delete
1807: -- this row else if another name doesn't exist then delete the row.
1808: IF (p_identifier_type = 'NAME' AND p_identifier_subtype = 'STANDARD_NAME') THEN
1809: select count(*) INTO l_count
1810: from hz_geography_identifiers
1804: END IF;
1805:
1806: -- If a STANDARD_NAME is being deleted , if there exists another name mark it as STANDARD and delete
1807: -- this row else if another name doesn't exist then delete the row.
1808: IF (p_identifier_type = 'NAME' AND p_identifier_subtype = 'STANDARD_NAME') THEN
1809: select count(*) INTO l_count
1810: from hz_geography_identifiers
1811: where geography_id = p_geography_id
1812: AND language_code = p_language_code
1812: AND language_code = p_language_code
1813: AND identifier_type = 'NAME'
1814: ;
1815: IF l_count > 1 THEN
1816: -- update an identifier to STANDARD
1817: UPDATE hz_geography_identifiers
1818: SET identifier_subtype = 'STANDARD_NAME'
1819: WHERE geography_id = p_geography_id
1820: AND identifier_type= p_identifier_type
1814: ;
1815: IF l_count > 1 THEN
1816: -- update an identifier to STANDARD
1817: UPDATE hz_geography_identifiers
1818: SET identifier_subtype = 'STANDARD_NAME'
1819: WHERE geography_id = p_geography_id
1820: AND identifier_type= p_identifier_type
1821: AND identifier_subtype <> p_identifier_subtype
1822: AND identifier_value <> p_identifier_value
2004:
2005: -- create an identifier for this geography in HZ_GEOGRAPHY_IDENTIFIERS
2006: -- construct Identifier record for identifier_type 'NAME'/'CODE'
2007: l_geo_identifier_rec.geography_id := x_geography_id;
2008: l_geo_identifier_rec.identifier_subtype := 'STANDARD_NAME';
2009: l_geo_identifier_rec.identifier_value := p_master_geography_rec.geography_name;
2010: l_geo_identifier_rec.identifier_type := 'NAME';
2011: l_geo_identifier_rec.geo_data_provider := p_master_geography_rec.geo_data_provider;
2012: l_geo_identifier_rec.primary_flag := 'Y';
2395: FROM hz_geography_identifiers id
2396: WHERE UPPER(id.identifier_value) = l_master_geography_rec.geography_name
2397: AND id.geography_use = 'MASTER_REF'
2398: AND id.identifier_type = 'NAME'
2399: AND id.identifier_subtype = 'STANDARD_NAME'
2400: AND EXISTS ( SELECT '1'
2401: FROM hz_relationships rel
2402: WHERE rel.subject_id = l_geography_range_rec.master_ref_geography_id
2403: AND rel.object_id = id.geography_id
3132:
3133:
3134: -- construct identifier record
3135: l_geo_identifier_rec.geography_id := x_geography_id;
3136: l_geo_identifier_rec.identifier_subtype := 'STANDARD_NAME';
3137: l_geo_identifier_rec.identifier_value := p_zone_name;
3138: l_geo_identifier_rec.identifier_type := 'NAME';
3139: l_geo_identifier_rec.geo_data_provider := NVL(p_geo_data_provider,'USER_ENTERED');
3140: l_geo_identifier_rec.primary_flag := 'Y';
3247:
3248: --l_master_relation_rec MASTER_RELATION_REC_TYPE := p_master_relation_rec;
3249:
3250: BEGIN
3251: -- Standard start of API savepoint
3252: SAVEPOINT create_master_relation;
3253:
3254: -- Initialize message list if p_init_msg_list is set to TRUE.
3255: IF FND_API.to_Boolean(p_init_msg_list) THEN
3271: RAISE FND_API.G_EXC_ERROR;
3272: END IF;
3273:
3274: --g_dup_checked := 'N';
3275: -- Standard call to get message count and if count is 1, get message info.
3276: FND_MSG_PUB.Count_And_Get(
3277: p_encoded => FND_API.G_FALSE,
3278: p_count => x_msg_count,
3279: p_data => x_msg_data);
3351: x_msg_data OUT NOCOPY VARCHAR2
3352: )IS
3353:
3354: BEGIN
3355: -- Standard start of API savepoint
3356: SAVEPOINT update_relationship;
3357:
3358: -- Initialize message list if p_init_msg_list is set to TRUE.
3359: IF FND_API.to_Boolean(p_init_msg_list) THEN
3375: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3376: RAISE FND_API.G_EXC_ERROR;
3377: END IF;
3378:
3379: -- Standard call to get message count and if count is 1, get message info.
3380: FND_MSG_PUB.Count_And_Get(
3381: p_encoded => FND_API.G_FALSE,
3382: p_count => x_msg_count,
3383: p_data => x_msg_data);
3451:
3452: p_index_name VARCHAR2(30);
3453:
3454: BEGIN
3455: -- Standard start of API savepoint
3456: SAVEPOINT create_geo_identifier;
3457:
3458: -- Initialize message list if p_init_msg_list is set to TRUE.
3459: IF FND_API.to_Boolean(p_init_msg_list) THEN
3473: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3474: RAISE FND_API.G_EXC_ERROR;
3475: END IF;
3476:
3477: -- Standard call to get message count and if count is 1, get message info.
3478: FND_MSG_PUB.Count_And_Get(
3479: p_encoded => FND_API.G_FALSE,
3480: p_count => x_msg_count,
3481: p_data => x_msg_data);
3568: )IS
3569:
3570: BEGIN
3571:
3572: -- Standard start of API savepoint
3573: SAVEPOINT update_geo_identifier;
3574:
3575: -- Initialize message list if p_init_msg_list is set to TRUE.
3576: IF FND_API.to_Boolean(p_init_msg_list) THEN
3592: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3593: RAISE FND_API.G_EXC_ERROR;
3594: END IF;
3595:
3596: -- Standard call to get message count and if count is 1, get message info.
3597: FND_MSG_PUB.Count_And_Get(
3598: p_encoded => FND_API.G_FALSE,
3599: p_count => x_msg_count,
3600: p_data => x_msg_data);
3673: x_msg_data OUT NOCOPY VARCHAR2
3674: ) IS
3675: BEGIN
3676:
3677: -- Standard start of API savepoint
3678: SAVEPOINT delete_geo_identifier;
3679:
3680: -- Initialize message list if p_init_msg_list is set to TRUE.
3681: IF FND_API.to_Boolean(p_init_msg_list) THEN
3699: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3700: RAISE FND_API.G_EXC_ERROR;
3701: END IF;
3702:
3703: -- Standard call to get message count and if count is 1, get message info.
3704: FND_MSG_PUB.Count_And_Get(
3705: p_encoded => FND_API.G_FALSE,
3706: p_count => x_msg_count,
3707: p_data => x_msg_data);
3776: x_msg_data OUT NOCOPY VARCHAR2
3777: )IS
3778:
3779: BEGIN
3780: -- Standard start of API savepoint
3781: SAVEPOINT create_master_geography;
3782: --dbms_output.put_line('In the beginning of create_master_geography');
3783:
3784: -- Initialize message list if p_init_msg_list is set to TRUE.
3801: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3802: RAISE FND_API.G_EXC_ERROR;
3803: END IF;
3804:
3805: -- Standard call to get message count and if count is 1, get message info.
3806: FND_MSG_PUB.Count_And_Get(
3807: p_encoded => FND_API.G_FALSE,
3808: p_count => x_msg_count,
3809: p_data => x_msg_data);
3881: ) IS
3882:
3883: BEGIN
3884:
3885: -- Standard start of API savepoint
3886: SAVEPOINT update_geography;
3887:
3888: -- Initialize message list if p_init_msg_list is set to TRUE.
3889: IF FND_API.to_Boolean(p_init_msg_list) THEN
3907: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3908: RAISE FND_API.G_EXC_ERROR;
3909: END IF;
3910:
3911: -- Standard call to get message count and if count is 1, get message info.
3912: FND_MSG_PUB.Count_And_Get(
3913: p_encoded => FND_API.G_FALSE,
3914: p_count => x_msg_count,
3915: p_data => x_msg_data);
3983:
3984: p_index_name VARCHAR2(30);
3985:
3986: BEGIN
3987: -- Standard start of API savepoint
3988: SAVEPOINT create_geography_range;
3989: --dbms_output.put_line('In the beginning of create_master_geography');
3990:
3991: -- Initialize message list if p_init_msg_list is set to TRUE.
4007: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4008: RAISE FND_API.G_EXC_ERROR;
4009: END IF;
4010:
4011: -- Standard call to get message count and if count is 1, get message info.
4012: FND_MSG_PUB.Count_And_Get(
4013: p_encoded => FND_API.G_FALSE,
4014: p_count => x_msg_count,
4015: p_data => x_msg_data);
4104: ) IS
4105:
4106: BEGIN
4107:
4108: -- Standard start of API savepoint
4109: SAVEPOINT update_geography_range;
4110:
4111: -- Initialize message list if p_init_msg_list is set to TRUE.
4112: IF FND_API.to_Boolean(p_init_msg_list) THEN
4130: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4131: RAISE FND_API.G_EXC_ERROR;
4132: END IF;
4133:
4134: -- Standard call to get message count and if count is 1, get message info.
4135: FND_MSG_PUB.Count_And_Get(
4136: p_encoded => FND_API.G_FALSE,
4137: p_count => x_msg_count,
4138: p_data => x_msg_data);
4208: x_msg_data OUT NOCOPY VARCHAR2
4209: ) IS
4210:
4211: BEGIN
4212: -- Standard start of API savepoint
4213: SAVEPOINT create_zone_relation;
4214: --dbms_output.put_line('In the beginning of create_zone_relation');
4215:
4216: -- Initialize message list if p_init_msg_list is set to TRUE.
4237: END IF;
4238:
4239: --dbms_output.put_line('after call to do_create '|| x_return_status);
4240:
4241: -- Standard call to get message count and if count is 1, get message info.
4242: FND_MSG_PUB.Count_And_Get(
4243: p_encoded => FND_API.G_FALSE,
4244: p_count => x_msg_count,
4245: p_data => x_msg_data);
4338:
4339: BEGIN
4340:
4341:
4342: -- Standard start of API savepoint
4343: SAVEPOINT create_zone;
4344: --dbms_output.put_line('In the beginning of create_zone');
4345:
4346: -- Initialize message list if p_init_msg_list is set to TRUE.
4375: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4376: RAISE FND_API.G_EXC_ERROR;
4377: END IF;
4378:
4379: -- Standard call to get message count and if count is 1, get message info.
4380: FND_MSG_PUB.Count_And_Get(
4381: p_encoded => FND_API.G_FALSE,
4382: p_count => x_msg_count,
4383: p_data => x_msg_data);