[Home] [Help]
1: PACKAGE BODY HZ_GEOGRAPHIES_PKG AS
2: /*$Header: ARHGEOTB.pls 120.1 2005/10/25 22:33:22 nsinghai noship $ */
3:
4: PROCEDURE Insert_Row (
5: x_rowid IN OUT NOCOPY VARCHAR2,
72:
73: --dbms_output.put_line.PUT_LINE('Before Insert');
74: WHILE l_success = 'N' LOOP
75: BEGIN
76: INSERT INTO HZ_GEOGRAPHIES (
77: geography_id,
78: object_version_number,
79: geography_type,
80: geography_name,
145: attribute20
146: )
147: VALUES (
148: DECODE(x_geography_id,
149: FND_API.G_MISS_NUM, HZ_GEOGRAPHIES_S.NEXTVAL,
150: NULL, HZ_GEOGRAPHIES_S.NEXTVAL,
151: x_geography_id),
152: DECODE(x_object_version_number,
153: FND_API.G_MISS_NUM, NULL,
146: )
147: VALUES (
148: DECODE(x_geography_id,
149: FND_API.G_MISS_NUM, HZ_GEOGRAPHIES_S.NEXTVAL,
150: NULL, HZ_GEOGRAPHIES_S.NEXTVAL,
151: x_geography_id),
152: DECODE(x_object_version_number,
153: FND_API.G_MISS_NUM, NULL,
154: x_object_version_number),
347: --dbms_output.put_line.PUT_LINE('l_success in after insert '||l_success);
348:
349: EXCEPTION
350: WHEN DUP_VAL_ON_INDEX THEN
351: IF INSTR(SQLERRM, 'HZ_GEOGRAPHIES_S') <> 0 THEN
352: DECLARE
353: l_count NUMBER;
354: l_dummy VARCHAR2(1);
355: BEGIN
354: l_dummy VARCHAR2(1);
355: BEGIN
356: l_count := 1;
357: WHILE l_count > 0 LOOP
358: SELECT HZ_GEOGRAPHIES_S.NEXTVAL
359: INTO x_geography_id FROM dual;
360: BEGIN
361: SELECT 'Y' INTO l_dummy
362: FROM HZ_GEOGRAPHIES
358: SELECT HZ_GEOGRAPHIES_S.NEXTVAL
359: INTO x_geography_id FROM dual;
360: BEGIN
361: SELECT 'Y' INTO l_dummy
362: FROM HZ_GEOGRAPHIES
363: WHERE geography_id = x_geography_id;
364: l_count := 1;
365: EXCEPTION
366: WHEN NO_DATA_FOUND THEN
440: x_attribute20 IN VARCHAR2
441: ) IS
442: BEGIN
443:
444: UPDATE HZ_GEOGRAPHIES
445: SET
446: geography_id =
447: DECODE(x_geography_id,
448: NULL, geography_id,
833: x_attribute20 IN VARCHAR2
834: ) IS
835:
836: CURSOR c IS
837: SELECT * FROM hz_geographies
838: WHERE rowid = x_rowid
839: FOR UPDATE NOWAIT;
840: Recinfo c%ROWTYPE;
841:
1253: x_attribute17,
1254: x_attribute18,
1255: x_attribute19,
1256: x_attribute20
1257: FROM HZ_GEOGRAPHIES
1258: WHERE geography_id = x_geography_id;
1259:
1260: EXCEPTION
1261: WHEN NO_DATA_FOUND THEN
1271: x_geography_id IN NUMBER
1272: ) IS
1273: BEGIN
1274:
1275: DELETE FROM HZ_GEOGRAPHIES
1276: WHERE geography_id = x_geography_id;
1277:
1278: IF ( SQL%NOTFOUND ) THEN
1279: RAISE NO_DATA_FOUND;
1282: END Delete_Row;
1283:
1284: /*
1285: Procedure update_geo_element_cp is concurrent program 'Update Geography Element Columns'
1286: (ARHGEOEU) which will update the HZ_GEOGRAPHIES name/code value for updated identifier
1287: values (if primary flag is Y).
1288: Created By Nishant Singhai 25-Oct-2005 For Bug 4578867
1289: */
1290: PROCEDURE update_geo_element_cp (
1306: l_geo_element_id VARCHAR2(60);
1307:
1308: CURSOR c_get_geo_details (l_geo_id IN NUMBER) IS
1309: SELECT geography_name, geography_code, geography_type, country_code
1310: FROM hz_geographies
1311: WHERE geography_id = l_geo_id
1312: AND geography_use = 'MASTER_REF'
1313: ;
1314: BEGIN
1349: l_geo_element_code := 'GEOGRAPHY_ELEMENT1_CODE';
1350: l_geo_element_id := 'GEOGRAPHY_ELEMENT1_ID';
1351: END IF;
1352:
1353: -- denormalize the primary identifier in HZ_GEOGRAPHIES for identifier_type='NAME' and 'CODE'
1354: -- for this geography_id
1355:
1356: IF p_identifier_type = 'CODE' THEN
1357: UPDATE HZ_GEOGRAPHIES
1353: -- denormalize the primary identifier in HZ_GEOGRAPHIES for identifier_type='NAME' and 'CODE'
1354: -- for this geography_id
1355:
1356: IF p_identifier_type = 'CODE' THEN
1357: UPDATE HZ_GEOGRAPHIES
1358: SET geography_code = l_identifier_value
1359: WHERE geography_id = p_geography_id;
1360: END IF;
1361:
1359: WHERE geography_id = p_geography_id;
1360: END IF;
1361:
1362: IF p_identifier_type = 'NAME' THEN
1363: UPDATE HZ_GEOGRAPHIES
1364: SET geography_name = l_identifier_value
1365: WHERE geography_id = p_geography_id;
1366: END IF;
1367:
1364: SET geography_name = l_identifier_value
1365: WHERE geography_id = p_geography_id;
1366: END IF;
1367:
1368: -- denormalize the primary identifier in HZ_GEOGRAPHIES for identifier_type='NAME'/'CODE' in all the rows
1369: -- where this geography_id is de-normalized as a parent
1370:
1371: IF (p_identifier_type='NAME' AND l_geo_element_col IS NOT NULL)THEN
1372:
1369: -- where this geography_id is de-normalized as a parent
1370:
1371: IF (p_identifier_type='NAME' AND l_geo_element_col IS NOT NULL)THEN
1372:
1373: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_identifier_value '||
1374: ' WHERE country_code = :l_country_code and '||
1375: l_geo_element_id||'= :l_geography_id '
1376: USING l_identifier_value, l_country_code, p_geography_id ;
1377:
1378: END IF;
1379:
1380: IF p_identifier_type='CODE' THEN
1381: IF l_geo_element_col IN ('GEOGRAPHY_ELEMENT1','GEOGRAPHY_ELEMENT2','GEOGRAPHY_ELEMENT3','GEOGRAPHY_ELEMENT4','GEOGRAPHY_ELEMENT5') THEN
1382: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_code||'= :l_identifier_value '||
1383: ' WHERE country_code = :l_country_code and '||
1384: l_geo_element_id||'= :l_geography_id '
1385: USING l_identifier_value, l_country_code, p_geography_id;
1386:
1397: retcode := '2'; -- Error
1398: errbuf := SQLERRM;
1399: END update_geo_element_cp;
1400:
1401: END HZ_GEOGRAPHIES_PKG;