[Home] [Help]
1263: END IF;
1264:
1265: END IF;
1266:
1267: -- Primary name should be of subtype 'STANDARD_NAME'
1268: IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.primary_flag = 'Y') THEN
1269: IF p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME' THEN
1270: FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_INVALID_SUBTYPE' );
1271: FND_MESSAGE.SET_TOKEN( 'SUBTYPE', p_geo_identifier_rec.identifier_subtype);
1265: END IF;
1266:
1267: -- Primary name should be of subtype 'STANDARD_NAME'
1268: IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.primary_flag = 'Y') THEN
1269: IF p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME' THEN
1270: FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_INVALID_SUBTYPE' );
1271: FND_MESSAGE.SET_TOKEN( 'SUBTYPE', p_geo_identifier_rec.identifier_subtype);
1272: FND_MSG_PUB.ADD;
1273: x_return_status := FND_API.G_RET_STS_ERROR;
1275: END IF;
1276: END IF;
1277:
1278: -- If this is the first row for the language_code for type NAME then if it is of subtype other than STANDAR_NAME,
1279: -- , make it as STANDARD_NAME
1280:
1281: IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME') THEN
1282:
1283: SELECT count(*) INTO l_count from
1277:
1278: -- If this is the first row for the language_code for type NAME then if it is of subtype other than STANDAR_NAME,
1279: -- , make it as STANDARD_NAME
1280:
1281: IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME') THEN
1282:
1283: SELECT count(*) INTO l_count from
1284: hz_geography_identifiers
1285: WHERE geography_id = p_geo_identifier_rec.geography_id
1285: WHERE geography_id = p_geo_identifier_rec.geography_id
1286: AND language_code = l_language_code;
1287:
1288: IF l_count = 0 THEN
1289: l_identifier_subtype:= 'STANDARD_NAME';
1290: END IF;
1291: END IF;
1292:
1293: IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1290: END IF;
1291: END IF;
1292:
1293: IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1294: IF (p_geo_identifier_rec.identifier_type='NAME' AND p_geo_identifier_rec.identifier_subtype = 'STANDARD_NAME') THEN
1295: -- check if there exists a STANDARD_NAME + Primary Flag = Y
1296: SELECT count(*) INTO l_count
1297: FROM hz_geography_identifiers
1298: WHERE geography_id = p_geo_identifier_rec.geography_id
1291: END IF;
1292:
1293: IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1294: IF (p_geo_identifier_rec.identifier_type='NAME' AND p_geo_identifier_rec.identifier_subtype = 'STANDARD_NAME') THEN
1295: -- check if there exists a STANDARD_NAME + Primary Flag = Y
1296: SELECT count(*) INTO l_count
1297: FROM hz_geography_identifiers
1298: WHERE geography_id = p_geo_identifier_rec.geography_id
1299: AND identifier_type = 'NAME'
1296: SELECT count(*) INTO l_count
1297: FROM hz_geography_identifiers
1298: WHERE geography_id = p_geo_identifier_rec.geography_id
1299: AND identifier_type = 'NAME'
1300: AND identifier_subtype = 'STANDARD_NAME'
1301: AND primary_flag = 'Y'
1302: AND language_code = l_language_code;
1303:
1304: IF l_count > 0 THEN
1301: AND primary_flag = 'Y'
1302: AND language_code = l_language_code;
1303:
1304: IF l_count > 0 THEN
1305: -- update STANDARD_NAME+Y to STANDARD_NAME+N
1306: UPDATE hz_geography_identifiers
1307: SET primary_flag = 'N'
1308: WHERE geography_id = p_geo_identifier_rec.geography_id
1309: AND identifier_type = 'NAME'
1306: UPDATE hz_geography_identifiers
1307: SET primary_flag = 'N'
1308: WHERE geography_id = p_geo_identifier_rec.geography_id
1309: AND identifier_type = 'NAME'
1310: AND identifier_subtype = 'STANDARD_NAME'
1311: AND primary_flag = 'Y'
1312: AND language_code = l_language_code;
1313: END IF;
1314:
1311: AND primary_flag = 'Y'
1312: AND language_code = l_language_code;
1313: END IF;
1314:
1315: l_identifier_subtype := 'STANDARD_NAME';
1316: END IF;
1317:
1318: --check if there exists a primary row already for this geography_id
1319: SELECT count(*) INTO l_count
1557: ELSE -- new geo subtype is null (i.e. no need to update), use the old subtype
1558: l_new_geo_subtype := l_geo_identifier_subtype;
1559: l_subtype_updated := 'N';
1560: END IF;
1561: ELSE -- if idenifier type is NAME (only 1 subtype 'STANDARD_NAME' is allowed)
1562: l_new_geo_subtype := p_geo_identifier_rec.new_identifier_subtype;
1563: -- validate new subtype
1564: IF (l_new_geo_subtype IS NOT NULL) THEN
1565: IF (p_geo_identifier_rec.new_identifier_subtype <> 'STANDARD_NAME') THEN
1561: ELSE -- if idenifier type is NAME (only 1 subtype 'STANDARD_NAME' is allowed)
1562: l_new_geo_subtype := p_geo_identifier_rec.new_identifier_subtype;
1563: -- validate new subtype
1564: IF (l_new_geo_subtype IS NOT NULL) THEN
1565: IF (p_geo_identifier_rec.new_identifier_subtype <> 'STANDARD_NAME') THEN
1566: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
1567: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'identifier_subtype' );
1568: FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', 'HZ_GEO_IDENTIFIER_SUBTYPE' );
1569: FND_MSG_PUB.ADD;
1815: x_return_status := FND_API.G_RET_STS_ERROR;
1816: RAISE FND_API.G_EXC_ERROR;
1817: END IF;
1818:
1819: -- If a STANDARD_NAME is being deleted , if there exists another name mark it as STANDARD and delete
1820: -- this row else if another name doesn't exist then delete the row.
1821: IF (p_identifier_type = 'NAME' AND p_identifier_subtype = 'STANDARD_NAME') THEN
1822: select count(*) INTO l_count
1823: from hz_geography_identifiers
1817: END IF;
1818:
1819: -- If a STANDARD_NAME is being deleted , if there exists another name mark it as STANDARD and delete
1820: -- this row else if another name doesn't exist then delete the row.
1821: IF (p_identifier_type = 'NAME' AND p_identifier_subtype = 'STANDARD_NAME') THEN
1822: select count(*) INTO l_count
1823: from hz_geography_identifiers
1824: where geography_id = p_geography_id
1825: AND language_code = p_language_code
1825: AND language_code = p_language_code
1826: AND identifier_type = 'NAME'
1827: ;
1828: IF l_count > 1 THEN
1829: -- update an identifier to STANDARD
1830: UPDATE hz_geography_identifiers
1831: SET identifier_subtype = 'STANDARD_NAME'
1832: WHERE geography_id = p_geography_id
1833: AND identifier_type= p_identifier_type
1827: ;
1828: IF l_count > 1 THEN
1829: -- update an identifier to STANDARD
1830: UPDATE hz_geography_identifiers
1831: SET identifier_subtype = 'STANDARD_NAME'
1832: WHERE geography_id = p_geography_id
1833: AND identifier_type= p_identifier_type
1834: AND identifier_subtype <> p_identifier_subtype
1835: AND identifier_value <> p_identifier_value
2017:
2018: -- create an identifier for this geography in HZ_GEOGRAPHY_IDENTIFIERS
2019: -- construct Identifier record for identifier_type 'NAME'/'CODE'
2020: l_geo_identifier_rec.geography_id := x_geography_id;
2021: l_geo_identifier_rec.identifier_subtype := 'STANDARD_NAME';
2022: l_geo_identifier_rec.identifier_value := p_master_geography_rec.geography_name;
2023: l_geo_identifier_rec.identifier_type := 'NAME';
2024: l_geo_identifier_rec.geo_data_provider := p_master_geography_rec.geo_data_provider;
2025: l_geo_identifier_rec.primary_flag := 'Y';
2408: FROM hz_geography_identifiers id
2409: WHERE UPPER(id.identifier_value) = l_master_geography_rec.geography_name
2410: AND id.geography_use = 'MASTER_REF'
2411: AND id.identifier_type = 'NAME'
2412: AND id.identifier_subtype = 'STANDARD_NAME'
2413: AND EXISTS ( SELECT '1'
2414: FROM hz_relationships rel
2415: WHERE rel.subject_id = l_geography_range_rec.master_ref_geography_id
2416: AND rel.object_id = id.geography_id
3145:
3146:
3147: -- construct identifier record
3148: l_geo_identifier_rec.geography_id := x_geography_id;
3149: l_geo_identifier_rec.identifier_subtype := 'STANDARD_NAME';
3150: l_geo_identifier_rec.identifier_value := p_zone_name;
3151: l_geo_identifier_rec.identifier_type := 'NAME';
3152: l_geo_identifier_rec.geo_data_provider := NVL(p_geo_data_provider,'USER_ENTERED');
3153: l_geo_identifier_rec.primary_flag := 'Y';
3260:
3261: --l_master_relation_rec MASTER_RELATION_REC_TYPE := p_master_relation_rec;
3262:
3263: BEGIN
3264: -- Standard start of API savepoint
3265: SAVEPOINT create_master_relation;
3266:
3267: -- Initialize message list if p_init_msg_list is set to TRUE.
3268: IF FND_API.to_Boolean(p_init_msg_list) THEN
3284: RAISE FND_API.G_EXC_ERROR;
3285: END IF;
3286:
3287: --g_dup_checked := 'N';
3288: -- Standard call to get message count and if count is 1, get message info.
3289: FND_MSG_PUB.Count_And_Get(
3290: p_encoded => FND_API.G_FALSE,
3291: p_count => x_msg_count,
3292: p_data => x_msg_data);
3364: x_msg_data OUT NOCOPY VARCHAR2
3365: )IS
3366:
3367: BEGIN
3368: -- Standard start of API savepoint
3369: SAVEPOINT update_relationship;
3370:
3371: -- Initialize message list if p_init_msg_list is set to TRUE.
3372: IF FND_API.to_Boolean(p_init_msg_list) THEN
3388: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3389: RAISE FND_API.G_EXC_ERROR;
3390: END IF;
3391:
3392: -- Standard call to get message count and if count is 1, get message info.
3393: FND_MSG_PUB.Count_And_Get(
3394: p_encoded => FND_API.G_FALSE,
3395: p_count => x_msg_count,
3396: p_data => x_msg_data);
3464:
3465: p_index_name VARCHAR2(30);
3466:
3467: BEGIN
3468: -- Standard start of API savepoint
3469: SAVEPOINT create_geo_identifier;
3470:
3471: -- Initialize message list if p_init_msg_list is set to TRUE.
3472: IF FND_API.to_Boolean(p_init_msg_list) THEN
3486: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3487: RAISE FND_API.G_EXC_ERROR;
3488: END IF;
3489:
3490: -- Standard call to get message count and if count is 1, get message info.
3491: FND_MSG_PUB.Count_And_Get(
3492: p_encoded => FND_API.G_FALSE,
3493: p_count => x_msg_count,
3494: p_data => x_msg_data);
3581: )IS
3582:
3583: BEGIN
3584:
3585: -- Standard start of API savepoint
3586: SAVEPOINT update_geo_identifier;
3587:
3588: -- Initialize message list if p_init_msg_list is set to TRUE.
3589: IF FND_API.to_Boolean(p_init_msg_list) THEN
3605: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3606: RAISE FND_API.G_EXC_ERROR;
3607: END IF;
3608:
3609: -- Standard call to get message count and if count is 1, get message info.
3610: FND_MSG_PUB.Count_And_Get(
3611: p_encoded => FND_API.G_FALSE,
3612: p_count => x_msg_count,
3613: p_data => x_msg_data);
3686: x_msg_data OUT NOCOPY VARCHAR2
3687: ) IS
3688: BEGIN
3689:
3690: -- Standard start of API savepoint
3691: SAVEPOINT delete_geo_identifier;
3692:
3693: -- Initialize message list if p_init_msg_list is set to TRUE.
3694: IF FND_API.to_Boolean(p_init_msg_list) THEN
3712: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3713: RAISE FND_API.G_EXC_ERROR;
3714: END IF;
3715:
3716: -- Standard call to get message count and if count is 1, get message info.
3717: FND_MSG_PUB.Count_And_Get(
3718: p_encoded => FND_API.G_FALSE,
3719: p_count => x_msg_count,
3720: p_data => x_msg_data);
3789: x_msg_data OUT NOCOPY VARCHAR2
3790: )IS
3791:
3792: BEGIN
3793: -- Standard start of API savepoint
3794: SAVEPOINT create_master_geography;
3795: --dbms_output.put_line('In the beginning of create_master_geography');
3796:
3797: -- Initialize message list if p_init_msg_list is set to TRUE.
3814: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3815: RAISE FND_API.G_EXC_ERROR;
3816: END IF;
3817:
3818: -- Standard call to get message count and if count is 1, get message info.
3819: FND_MSG_PUB.Count_And_Get(
3820: p_encoded => FND_API.G_FALSE,
3821: p_count => x_msg_count,
3822: p_data => x_msg_data);
3894: ) IS
3895:
3896: BEGIN
3897:
3898: -- Standard start of API savepoint
3899: SAVEPOINT update_geography;
3900:
3901: -- Initialize message list if p_init_msg_list is set to TRUE.
3902: IF FND_API.to_Boolean(p_init_msg_list) THEN
3920: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3921: RAISE FND_API.G_EXC_ERROR;
3922: END IF;
3923:
3924: -- Standard call to get message count and if count is 1, get message info.
3925: FND_MSG_PUB.Count_And_Get(
3926: p_encoded => FND_API.G_FALSE,
3927: p_count => x_msg_count,
3928: p_data => x_msg_data);
3996:
3997: p_index_name VARCHAR2(30);
3998:
3999: BEGIN
4000: -- Standard start of API savepoint
4001: SAVEPOINT create_geography_range;
4002: --dbms_output.put_line('In the beginning of create_master_geography');
4003:
4004: -- Initialize message list if p_init_msg_list is set to TRUE.
4020: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4021: RAISE FND_API.G_EXC_ERROR;
4022: END IF;
4023:
4024: -- Standard call to get message count and if count is 1, get message info.
4025: FND_MSG_PUB.Count_And_Get(
4026: p_encoded => FND_API.G_FALSE,
4027: p_count => x_msg_count,
4028: p_data => x_msg_data);
4117: ) IS
4118:
4119: BEGIN
4120:
4121: -- Standard start of API savepoint
4122: SAVEPOINT update_geography_range;
4123:
4124: -- Initialize message list if p_init_msg_list is set to TRUE.
4125: IF FND_API.to_Boolean(p_init_msg_list) THEN
4143: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4144: RAISE FND_API.G_EXC_ERROR;
4145: END IF;
4146:
4147: -- Standard call to get message count and if count is 1, get message info.
4148: FND_MSG_PUB.Count_And_Get(
4149: p_encoded => FND_API.G_FALSE,
4150: p_count => x_msg_count,
4151: p_data => x_msg_data);
4221: x_msg_data OUT NOCOPY VARCHAR2
4222: ) IS
4223:
4224: BEGIN
4225: -- Standard start of API savepoint
4226: SAVEPOINT create_zone_relation;
4227: --dbms_output.put_line('In the beginning of create_zone_relation');
4228:
4229: -- Initialize message list if p_init_msg_list is set to TRUE.
4250: END IF;
4251:
4252: --dbms_output.put_line('after call to do_create '|| x_return_status);
4253:
4254: -- Standard call to get message count and if count is 1, get message info.
4255: FND_MSG_PUB.Count_And_Get(
4256: p_encoded => FND_API.G_FALSE,
4257: p_count => x_msg_count,
4258: p_data => x_msg_data);
4351:
4352: BEGIN
4353:
4354:
4355: -- Standard start of API savepoint
4356: SAVEPOINT create_zone;
4357: --dbms_output.put_line('In the beginning of create_zone');
4358:
4359: -- Initialize message list if p_init_msg_list is set to TRUE.
4388: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4389: RAISE FND_API.G_EXC_ERROR;
4390: END IF;
4391:
4392: -- Standard call to get message count and if count is 1, get message info.
4393: FND_MSG_PUB.Count_And_Get(
4394: p_encoded => FND_API.G_FALSE,
4395: p_count => x_msg_count,
4396: p_data => x_msg_data);