[Home] [Help]
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
1373: -- where this geography_id is de-normalized as a parent
1374:
1375: IF (p_identifier_type='NAME' AND l_geo_element_col IS NOT NULL)THEN
1376:
1377: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_identifier_value '||
1378: ', last_updated_by = hz_utility_pub.LAST_UPDATED_BY '||
1379: ', last_update_date = hz_utility_pub.LAST_UPDATE_DATE '||
1380: ' WHERE country_code = :l_country_code and '||
1381: l_geo_element_id||'= :l_geography_id '
1384: END IF;
1385:
1386: IF p_identifier_type='CODE' THEN
1387: IF l_geo_element_col IN ('GEOGRAPHY_ELEMENT1','GEOGRAPHY_ELEMENT2','GEOGRAPHY_ELEMENT3','GEOGRAPHY_ELEMENT4','GEOGRAPHY_ELEMENT5') THEN
1388: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_code||'= :l_identifier_value '||
1389: ', last_updated_by = hz_utility_pub.LAST_UPDATED_BY '||
1390: ', last_update_date = hz_utility_pub.LAST_UPDATE_DATE '||
1391: ' WHERE country_code = :l_country_code and '||
1392: l_geo_element_id||'= :l_geography_id '