[Home] [Help]
49: -- Version Initial version number = 1.0
50: -- History Current version number = 1.0
51: --=======================================================
52: procedure UPLOAD_REGION (
53: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
54: p_api_version_number IN NUMBER,
55: p_return_status OUT NOCOPY VARCHAR2,
56: p_index IN OUT NOCOPY NUMBER,
57: p_loader_timestamp IN DATE,
54: p_api_version_number IN NUMBER,
55: p_return_status OUT NOCOPY VARCHAR2,
56: p_index IN OUT NOCOPY NUMBER,
57: p_loader_timestamp IN DATE,
58: p_line_num IN NUMBER := FND_API.G_MISS_NUM,
59: p_buffer IN AK_ON_OBJECTS_PUB.Buffer_Type,
60: p_line_num_out OUT NOCOPY NUMBER,
61: p_buffer_out OUT NOCOPY AK_ON_OBJECTS_PUB.Buffer_Type,
62: p_upl_loader_cur IN OUT NOCOPY AK_ON_OBJECTS_PUB.LoaderCurTyp,
123: begin
124: --dbms_output.put_line('Started region upload: ' ||
125: -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
126:
127: IF NOT FND_API.Compatible_API_Call (
128: l_api_version_number, p_api_version_number, l_api_name,
129: G_PKG_NAME) then
130: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131: return;
126:
127: IF NOT FND_API.Compatible_API_Call (
128: l_api_version_number, p_api_version_number, l_api_name,
129: G_PKG_NAME) then
130: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131: return;
132: END IF;
133:
134: -- 5665840 - removed savepoint as child procedures do the savepoint/
143: -- the tokens 'BEGIN REGION' has already been parsed. Set initial
144: -- buffer to 'BEGIN REGION' before reading the next line from the
145: -- file. Otherwise, set initial buffer to null.
146: --
147: if (p_loader_timestamp <> FND_API.G_MISS_DATE) then
148: l_buffer := 'BEGIN REGION ' || p_buffer;
149: else
150: l_buffer := null;
151: end if;
149: else
150: l_buffer := null;
151: end if;
152:
153: if (p_line_num = FND_API.G_MISS_NUM) then
154: l_line_num := 0;
155: else
156: l_line_num := p_line_num;
157: end if;
164: p_lines_read => l_lines_read,
165: p_eof_flag => l_eof_flag,
166: p_upl_loader_cur => p_upl_loader_cur
167: );
168: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
169: (l_return_status = FND_API.G_RET_STS_ERROR) then
170: RAISE FND_API.G_EXC_ERROR;
171: end if;
172: l_line_num := l_line_num + l_lines_read;
165: p_eof_flag => l_eof_flag,
166: p_upl_loader_cur => p_upl_loader_cur
167: );
168: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
169: (l_return_status = FND_API.G_RET_STS_ERROR) then
170: RAISE FND_API.G_EXC_ERROR;
171: end if;
172: l_line_num := l_line_num + l_lines_read;
173: --
166: p_upl_loader_cur => p_upl_loader_cur
167: );
168: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
169: (l_return_status = FND_API.G_RET_STS_ERROR) then
170: RAISE FND_API.G_EXC_ERROR;
171: end if;
172: l_line_num := l_line_num + l_lines_read;
173: --
174: -- trim leading spaces and discard comment lines
186: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
187: FND_MESSAGE.SET_NAME('AK','AK_EMPTY_BUFFER');
188: FND_MSG_PUB.Add;
189: end if;
190: raise FND_API.G_EXC_ERROR;
191: end if;
192:
193: -- Read tokens from file, one at a time
194:
202: );
203:
204: --dbms_output.put_line(' State:' || l_state || 'Token:' || l_token);
205:
206: if (l_return_status = FND_API.G_RET_STS_ERROR) or
207: (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
208: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
209: FND_MESSAGE.SET_NAME('AK','AK_GET_TOKEN_ERROR');
210: FND_MSG_PUB.Add;
203:
204: --dbms_output.put_line(' State:' || l_state || 'Token:' || l_token);
205:
206: if (l_return_status = FND_API.G_RET_STS_ERROR) or
207: (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
208: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
209: FND_MESSAGE.SET_NAME('AK','AK_GET_TOKEN_ERROR');
210: FND_MSG_PUB.Add;
211: end if;
209: FND_MESSAGE.SET_NAME('AK','AK_GET_TOKEN_ERROR');
210: FND_MSG_PUB.Add;
211: end if;
212: --dbms_output.put_line(l_api_name || ' Error parsing buffer');
213: raise FND_API.G_EXC_ERROR;
214: end if;
215:
216:
217: --
227: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
228: FND_MESSAGE.SET_TOKEN('EXPECTED','BEGIN');
229: FND_MSG_PUB.Add;
230: end if;
231: raise FND_API.G_EXC_ERROR;
232: end if;
233: elsif (l_state = 1) then
234: if (l_token = 'REGION') then
235: --== Clear out previous column data ==--
247: l_region_rec.region_application_id := to_number(l_token);
248: l_state := 3;
249: else
250: --dbms_output.put_line('Expecting region application ID');
251: raise FND_API.G_EXC_ERROR;
252: end if;
253: elsif (l_state = 3) then
254: if (l_token is not null) then
255: l_region_rec.region_code := l_token;
262: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
263: FND_MESSAGE.SET_TOKEN('EXPECTED','REGION_CODE');
264: FND_MSG_PUB.Add;
265: end if;
266: raise FND_API.G_EXC_ERROR;
267: end if;
268: elsif (l_state = 10) then
269: if (l_token = 'BEGIN') then
270: l_state := 13;
353: FND_MESSAGE.SET_TOKEN('EXPECTED','REGION');
354: FND_MSG_PUB.Add;
355: end if;
356: -- dbms_output.put_line('Expecting region field, BEGIN, or END');
357: raise FND_API.G_EXC_ERROR;
358: end if;
359: end if;
360: elsif (l_state = 11) then
361: if (l_token = '=') then
367: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
368: FND_MESSAGE.SET_TOKEN('EXPECTED','=');
369: FND_MSG_PUB.Add;
370: end if;
371: raise FND_API.G_EXC_ERROR;
372: end if;
373: elsif (l_state = 12) then
374: l_value_count := 1;
375: if (l_column = 'DATABASE_OBJECT_NAME') then
489: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
490: FND_MESSAGE.SET_TOKEN('EXPECTED', l_column);
491: FND_MSG_PUB.Add;
492: end if;
493: raise FND_API.G_EXC_ERROR;
494: end if;
495: l_state := 10;
496: elsif (l_state = 13) then
497: if (l_token = 'REGION_ITEM') then
541: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
542: FND_MESSAGE.SET_TOKEN('EXPECTED', 'REGION_ITEM, REGION_LOV_RELATION, CATEGORY_USAGE');
543: FND_MSG_PUB.Add;
544: end if;
545: raise FND_API.G_EXC_ERROR;
546: end if;
547: elsif (l_state = 19) then
548: if (l_token = 'REGION') then
549: l_state := 0;
556: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
557: FND_MESSAGE.SET_TOKEN('EXPECTED', 'REGION');
558: FND_MSG_PUB.Add;
559: end if;
560: raise FND_API.G_EXC_ERROR;
561: end if;
562:
563: --
564: -- REGION_ITEM (states 20 - 39)
574: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
575: FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_APPLICATION_ID');
576: FND_MSG_PUB.Add;
577: end if;
578: raise FND_API.G_EXC_ERROR;
579: end if;
580: elsif (l_state = 21) then
581: if (l_token is not null) then
582: l_item_rec.attribute_code := l_token;
589: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
590: FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_CODE');
591: FND_MSG_PUB.Add;
592: end if;
593: raise FND_API.G_EXC_ERROR;
594: end if;
595: elsif (l_state = 30) then
596: if (l_token = 'END') then
597: l_state := 39;
713: end if;
714: FND_MESSAGE.SET_TOKEN('EXPECTED','REGION_ITEM');
715: FND_MSG_PUB.Add;
716: end if;
717: raise FND_API.G_EXC_ERROR;
718: end if;
719: end if;
720: elsif (l_state = 31) then
721: if (l_token = '=') then
727: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
728: FND_MESSAGE.SET_TOKEN('EXPECTED', '=');
729: FND_MSG_PUB.Add;
730: end if;
731: raise FND_API.G_EXC_ERROR;
732: end if;
733: elsif (l_state = 32) then
734: l_value_count := 1;
735: if (l_column = 'DISPLAY_SEQUENCE') then
1006: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1007: FND_MESSAGE.SET_TOKEN('EXPECTED', l_column || ' value');
1008: FND_MSG_PUB.Add;
1009: end if;
1010: raise FND_API.G_EXC_ERROR;
1011: end if;
1012: elsif (l_state = 34) then
1013: if (l_column = 'LOV_REGION') then
1014: l_item_rec.lov_region_code := l_token;
1023: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1024: FND_MESSAGE.SET_TOKEN('EXPECTED', l_column);
1025: FND_MSG_PUB.Add;
1026: end if;
1027: raise FND_API.G_EXC_ERROR;
1028: end if;
1029: elsif (l_state = 39) then
1030: if (l_token = 'REGION_ITEM') then
1031: l_value_count := null;
1039: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1040: FND_MESSAGE.SET_TOKEN('EXPECTED', 'REGION_ITEM');
1041: FND_MSG_PUB.Add;
1042: end if;
1043: raise FND_API.G_EXC_ERROR;
1044: end if;
1045:
1046: --
1047: -- REGION_LOV_RELATION (states 100 - 139)
1057: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1058: FND_MESSAGE.SET_TOKEN('EXPECTED', 'LOV_REGION_APPL_ID');
1059: FND_MSG_PUB.Add;
1060: end if;
1061: raise FND_API.G_EXC_ERROR;
1062: end if;
1063: elsif (l_state = 101) then
1064: if (l_token is not null) then
1065: l_lov_relation_rec.lov_region_code := l_token;
1072: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1073: FND_MESSAGE.SET_TOKEN('EXPECTED', 'LOV_REGION_CODE');
1074: FND_MSG_PUB.Add;
1075: end if;
1076: raise FND_API.G_EXC_ERROR;
1077: end if;
1078: elsif (l_state = 102) then
1079: if (l_token is not null) then
1080: l_lov_relation_rec.lov_attribute_appl_id := to_number(l_token);
1086: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1087: FND_MESSAGE.SET_TOKEN('EXPECTED', 'LOV_ATTRIBUTE_APPL_ID');
1088: FND_MSG_PUB.Add;
1089: end if;
1090: raise FND_API.G_EXC_ERROR;
1091: end if;
1092: elsif (l_state = 103) then
1093: if (l_token is not null) then
1094: l_lov_relation_rec.lov_attribute_code := l_token;
1101: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1102: FND_MESSAGE.SET_TOKEN('EXPECTED', 'LOV_ATTRIBUTE_CODE');
1103: FND_MSG_PUB.Add;
1104: end if;
1105: raise FND_API.G_EXC_ERROR;
1106: end if;
1107: elsif (l_state = 104) then
1108: if (l_token is not null) then
1109: l_lov_relation_rec.base_attribute_appl_id := to_number(l_token);
1115: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1116: FND_MESSAGE.SET_TOKEN('EXPECTED', 'BASE_ATTRIBUTE_APPL_ID');
1117: FND_MSG_PUB.Add;
1118: end if;
1119: raise FND_API.G_EXC_ERROR;
1120: end if;
1121: elsif (l_state = 105) then
1122: if (l_token is not null) then
1123: l_lov_relation_rec.base_attribute_code := l_token;
1130: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1131: FND_MESSAGE.SET_TOKEN('EXPECTED', 'BASE_ATTRIBUTE_CODE');
1132: FND_MSG_PUB.Add;
1133: end if;
1134: raise FND_API.G_EXC_ERROR;
1135: end if;
1136: elsif (l_state = 106) then
1137: if (l_token is not null) then
1138: l_lov_relation_rec.direction_flag := l_token;
1145: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1146: FND_MESSAGE.SET_TOKEN('EXPECTED', 'DIRECTION_FLAG');
1147: FND_MSG_PUB.Add;
1148: end if;
1149: raise FND_API.G_EXC_ERROR;
1150: end if;
1151: elsif (l_state = 130) then
1152: if (l_token = 'END') then
1153: l_state := 139;
1188: end if;
1189: FND_MESSAGE.SET_TOKEN('EXPECTED','REGION_LOV_RELATION');
1190: FND_MSG_PUB.Add;
1191: end if;
1192: raise FND_API.G_EXC_ERROR;
1193: end if;
1194: end if;
1195: elsif (l_state = 131) then
1196: if (l_token = '=') then
1202: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1203: FND_MESSAGE.SET_TOKEN('EXPECTED', '=');
1204: FND_MSG_PUB.Add;
1205: end if;
1206: raise FND_API.G_EXC_ERROR;
1207: end if;
1208: elsif (l_state = 132) then
1209: l_value_count := 1;
1210: if (l_column = 'BASE_REGION_APPL_ID') then
1243: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1244: FND_MESSAGE.SET_TOKEN('EXPECTED', l_column || ' value');
1245: FND_MSG_PUB.Add;
1246: end if;
1247: raise FND_API.G_EXC_ERROR;
1248: end if;
1249: elsif (l_state = 139) then
1250: if (l_token = 'REGION_LOV_RELATION') then
1251: l_value_count := null;
1259: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1260: FND_MESSAGE.SET_TOKEN('EXPECTED', 'REGION_LOV_RELATION');
1261: FND_MSG_PUB.Add;
1262: end if;
1263: raise FND_API.G_EXC_ERROR;
1264: end if;
1265:
1266: --
1267: -- CATEGORY_USAGE (states 200 - 239)
1279: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1280: FND_MESSAGE.SET_TOKEN('EXPECTED', 'CATEGORY_ID');
1281: FND_MSG_PUB.Add;
1282: end if;
1283: raise FND_API.G_EXC_ERROR;
1284: end if;
1285: elsif (l_state = 230) then
1286: if (l_token = 'END') then
1287: l_state := 239;
1322: end if;
1323: FND_MESSAGE.SET_TOKEN('EXPECTED','CATEGORY_USAGE');
1324: FND_MSG_PUB.Add;
1325: end if;
1326: raise FND_API.G_EXC_ERROR;
1327: end if;
1328: end if;
1329: elsif (l_state = 231) then
1330: if (l_token = '=') then
1336: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1337: FND_MESSAGE.SET_TOKEN('EXPECTED', '=');
1338: FND_MSG_PUB.Add;
1339: end if;
1340: raise FND_API.G_EXC_ERROR;
1341: end if;
1342: elsif (l_state = 232) then
1343: l_value_count := 1;
1344: if (l_column = 'CATEGORY_NAME') then
1377: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1378: FND_MESSAGE.SET_TOKEN('EXPECTED', l_column || ' value');
1379: FND_MSG_PUB.Add;
1380: end if;
1381: raise FND_API.G_EXC_ERROR;
1382: end if;
1383: elsif (l_state = 239) then
1384: if (l_token = 'CATEGORY_USAGE') then
1385: l_value_count := null;
1393: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1394: FND_MESSAGE.SET_TOKEN('EXPECTED', 'CATEGORY_USAGE');
1395: FND_MSG_PUB.Add;
1396: end if;
1397: raise FND_API.G_EXC_ERROR;
1398: end if;
1399:
1400: --
1401: -- REGION_GRAPH (states 300 - 339)
1411: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1412: FND_MESSAGE.SET_TOKEN('EXPECTED', 'GRAPH_NUMBER');
1413: FND_MSG_PUB.Add;
1414: end if;
1415: raise FND_API.G_EXC_ERROR;
1416: end if;
1417: elsif (l_state = 310) then
1418: if (l_token = 'END') then
1419: l_state := 319;
1457: end if;
1458: FND_MESSAGE.SET_TOKEN('EXPECTED','REGION_GRAPH');
1459: FND_MSG_PUB.Add;
1460: end if;
1461: raise FND_API.G_EXC_ERROR;
1462: end if;
1463: end if;
1464: elsif (l_state = 311) then
1465: if (l_token = '=') then
1471: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1472: FND_MESSAGE.SET_TOKEN('EXPECTED', '=');
1473: FND_MSG_PUB.Add;
1474: end if;
1475: raise FND_API.G_EXC_ERROR;
1476: end if;
1477: elsif (l_state = 312) then
1478: l_value_count := 1;
1479: if (l_column = 'GRAPH_STYLE') then
1515: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1516: FND_MESSAGE.SET_TOKEN('EXPECTED', l_column || ' value');
1517: FND_MSG_PUB.Add;
1518: end if;
1519: raise FND_API.G_EXC_ERROR;
1520: end if;
1521: elsif (l_state = 319) then
1522: if (l_token = 'REGION_GRAPH') then
1523: l_value_count := null;
1531: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1532: FND_MESSAGE.SET_TOKEN('EXPECTED', 'REGION_GRAPH');
1533: FND_MSG_PUB.Add;
1534: end if;
1535: raise FND_API.G_EXC_ERROR;
1536: end if;
1537:
1538: --
1539: -- REGION_GRAPH_COLUMN (400 - 449)
1549: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1550: FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_APPLICATION_ID');
1551: FND_MSG_PUB.Add;
1552: end if;
1553: raise FND_API.G_EXC_ERROR;
1554: end if;
1555: elsif (l_state = 401) then
1556: if (l_token is not null) then
1557: -- l_graph_column_rec.attribute_code := l_token;
1564: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1565: FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_CODE');
1566: FND_MSG_PUB.Add;
1567: end if;
1568: raise FND_API.G_EXC_ERROR;
1569: end if;
1570: elsif (l_state = 410) then
1571: if (l_token = 'END') then
1572: l_state := 419;
1596: end if;
1597: FND_MESSAGE.SET_TOKEN('EXPECTED','REGION_GRAPH_COLUMN');
1598: FND_MSG_PUB.Add;
1599: end if;
1600: raise FND_API.G_EXC_ERROR;
1601: end if;
1602: end if;
1603: elsif (l_state = 419) then
1604: if (l_token = 'REGION_GRAPH_COLUMN') then
1613: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1614: FND_MESSAGE.SET_TOKEN('EXPECTED', 'REGION_GRAPH_COLUMN');
1615: FND_MSG_PUB.Add;
1616: end if;
1617: raise FND_API.G_EXC_ERROR;
1618: end if;
1619:
1620: end if; -- if l_state = ...
1621:
1633: p_lines_read => l_lines_read,
1634: p_eof_flag => l_eof_flag,
1635: p_upl_loader_cur => p_upl_loader_cur
1636: );
1637: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1638: (l_return_status = FND_API.G_RET_STS_ERROR) then
1639: RAISE FND_API.G_EXC_ERROR;
1640: end if;
1641: l_line_num := l_line_num + l_lines_read;
1634: p_eof_flag => l_eof_flag,
1635: p_upl_loader_cur => p_upl_loader_cur
1636: );
1637: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1638: (l_return_status = FND_API.G_RET_STS_ERROR) then
1639: RAISE FND_API.G_EXC_ERROR;
1640: end if;
1641: l_line_num := l_line_num + l_lines_read;
1642: --
1635: p_upl_loader_cur => p_upl_loader_cur
1636: );
1637: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1638: (l_return_status = FND_API.G_RET_STS_ERROR) then
1639: RAISE FND_API.G_EXC_ERROR;
1640: end if;
1641: l_line_num := l_line_num + l_lines_read;
1642: --
1643: -- trim leading spaces and discard comment lines
1662: FND_MSG_PUB.Add;
1663: end if;
1664: --dbms_output.put_line('Unexpected END OF FILE: state is ' ||
1665: -- to_char(l_state));
1666: raise FND_API.G_EXC_ERROR;
1667: end if;
1668:
1669: --
1670: -- create or update all regions to the database
1967: );
1968: end if; -- /* if REGION_EXISTS */
1969: --
1970: -- If API call returns with an error status, upload aborts
1971: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1972: (l_return_status = FND_API.G_RET_STS_ERROR) then
1973: RAISE FND_API.G_EXC_ERROR;
1974: end if; -- /* if l_return_status */
1975: --
1968: end if; -- /* if REGION_EXISTS */
1969: --
1970: -- If API call returns with an error status, upload aborts
1971: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1972: (l_return_status = FND_API.G_RET_STS_ERROR) then
1973: RAISE FND_API.G_EXC_ERROR;
1974: end if; -- /* if l_return_status */
1975: --
1976: -- if validation fails, then this record should go to second pass
1969: --
1970: -- If API call returns with an error status, upload aborts
1971: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1972: (l_return_status = FND_API.G_RET_STS_ERROR) then
1973: RAISE FND_API.G_EXC_ERROR;
1974: end if; -- /* if l_return_status */
1975: --
1976: -- if validation fails, then this record should go to second pass
1977: if (l_copy_redo_flag) then
2390: );
2391: end if; -- /* if ITEM_EXISTS */
2392: --
2393: -- If API call returns with an error status, upload aborts
2394: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2395: (l_return_status = FND_API.G_RET_STS_ERROR) then
2396: RAISE FND_API.G_EXC_ERROR;
2397: end if; -- /* if l_return_status */
2398: --
2391: end if; -- /* if ITEM_EXISTS */
2392: --
2393: -- If API call returns with an error status, upload aborts
2394: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2395: (l_return_status = FND_API.G_RET_STS_ERROR) then
2396: RAISE FND_API.G_EXC_ERROR;
2397: end if; -- /* if l_return_status */
2398: --
2399: -- if validation fails, then this record should go to second pass
2392: --
2393: -- If API call returns with an error status, upload aborts
2394: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2395: (l_return_status = FND_API.G_RET_STS_ERROR) then
2396: RAISE FND_API.G_EXC_ERROR;
2397: end if; -- /* if l_return_status */
2398: --
2399: -- if validation fails, then this record should go to second pass
2400: if (l_copy_redo_flag) then
2509: );
2510: end if; -- /* if REGION_EXISTS */
2511: --
2512: -- If API call returns with an error status, upload aborts
2513: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2514: (l_return_status = FND_API.G_RET_STS_ERROR) then
2515: RAISE FND_API.G_EXC_ERROR;
2516: end if; -- /* if l_return_status */
2517: --
2510: end if; -- /* if REGION_EXISTS */
2511: --
2512: -- If API call returns with an error status, upload aborts
2513: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2514: (l_return_status = FND_API.G_RET_STS_ERROR) then
2515: RAISE FND_API.G_EXC_ERROR;
2516: end if; -- /* if l_return_status */
2517: --
2518: -- if validation fails, then this record should go to second pass
2511: --
2512: -- If API call returns with an error status, upload aborts
2513: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2514: (l_return_status = FND_API.G_RET_STS_ERROR) then
2515: RAISE FND_API.G_EXC_ERROR;
2516: end if; -- /* if l_return_status */
2517: --
2518: -- if validation fails, then this record should go to second pass
2519: -- validation usually fails when referenced record has not been created yet
2550: FND_MESSAGE.SET_TOKEN('KEY', (l_category_usage_tbl(l_index).category_name));
2551: FND_MSG_PUB.Add;
2552: end if;
2553: close l_check_fnd_category_id_csr;
2554: raise FND_API.G_EXC_ERROR;
2555: end if;
2556: close l_check_fnd_category_id_csr;
2557: end if;
2558:
2621: end if; -- /* if CATEGORY_USAGE_EXISTS */
2622: end if;
2623: --
2624: -- If API call returns with an error status, upload aborts
2625: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2626: (l_return_status = FND_API.G_RET_STS_ERROR) then
2627: RAISE FND_API.G_EXC_ERROR;
2628: end if; -- /* if l_return_status */
2629: --
2622: end if;
2623: --
2624: -- If API call returns with an error status, upload aborts
2625: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2626: (l_return_status = FND_API.G_RET_STS_ERROR) then
2627: RAISE FND_API.G_EXC_ERROR;
2628: end if; -- /* if l_return_status */
2629: --
2630: -- if validation fails, then this record should go to second pass
2623: --
2624: -- If API call returns with an error status, upload aborts
2625: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2626: (l_return_status = FND_API.G_RET_STS_ERROR) then
2627: RAISE FND_API.G_EXC_ERROR;
2628: end if; -- /* if l_return_status */
2629: --
2630: -- if validation fails, then this record should go to second pass
2631: -- validation usually fails when referenced record has not been created yet
2742: );
2743: end if; -- if ITEM_EXISTS
2744: --
2745: -- If API call returns with an error status, upload aborts
2746: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2747: (l_return_status = FND_API.G_RET_STS_ERROR) then
2748: RAISE FND_API.G_EXC_ERROR;
2749: end if; -- if l_return_status
2750: --
2743: end if; -- if ITEM_EXISTS
2744: --
2745: -- If API call returns with an error status, upload aborts
2746: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2747: (l_return_status = FND_API.G_RET_STS_ERROR) then
2748: RAISE FND_API.G_EXC_ERROR;
2749: end if; -- if l_return_status
2750: --
2751: -- if validation fails, then this record should go to second pass
2744: --
2745: -- If API call returns with an error status, upload aborts
2746: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2747: (l_return_status = FND_API.G_RET_STS_ERROR) then
2748: RAISE FND_API.G_EXC_ERROR;
2749: end if; -- if l_return_status
2750: --
2751: -- if validation fails, then this record should go to second pass
2752: if (l_copy_redo_flag) then
2853: );
2854: end if; -- if GRAPH_EXISTS
2855: --
2856: -- If API call returns with an error status, upload aborts
2857: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2858: (l_return_status = FND_API.G_RET_STS_ERROR) then
2859: RAISE FND_API.G_EXC_ERROR;
2860: end if; -- if l_return_status
2861: --
2854: end if; -- if GRAPH_EXISTS
2855: --
2856: -- If API call returns with an error status, upload aborts
2857: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2858: (l_return_status = FND_API.G_RET_STS_ERROR) then
2859: RAISE FND_API.G_EXC_ERROR;
2860: end if; -- if l_return_status
2861: --
2862: -- if validation fails, then this record should go to second pass
2855: --
2856: -- If API call returns with an error status, upload aborts
2857: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2858: (l_return_status = FND_API.G_RET_STS_ERROR) then
2859: RAISE FND_API.G_EXC_ERROR;
2860: end if; -- if l_return_status
2861: --
2862: -- if validation fails, then this record should go to second pass
2863: if (l_copy_redo_flag) then
2874: -- Load line number of the last file line processed
2875: --
2876: p_line_num_out := l_line_num;
2877:
2878: p_return_status := FND_API.G_RET_STS_SUCCESS;
2879:
2880: --dbms_output.put_line('Leaving region upload: ' ||
2881: -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
2882:
2880: --dbms_output.put_line('Leaving region upload: ' ||
2881: -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
2882:
2883: EXCEPTION
2884: WHEN FND_API.G_EXC_ERROR THEN
2885: p_return_status := FND_API.G_RET_STS_ERROR;
2886: --rollback to Start_Upload;
2887: WHEN VALUE_ERROR THEN
2888: p_return_status := FND_API.G_RET_STS_ERROR;
2881: -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
2882:
2883: EXCEPTION
2884: WHEN FND_API.G_EXC_ERROR THEN
2885: p_return_status := FND_API.G_RET_STS_ERROR;
2886: --rollback to Start_Upload;
2887: WHEN VALUE_ERROR THEN
2888: p_return_status := FND_API.G_RET_STS_ERROR;
2889: FND_MESSAGE.SET_NAME('AK','AK_REGION_VALUE_ERROR');
2884: WHEN FND_API.G_EXC_ERROR THEN
2885: p_return_status := FND_API.G_RET_STS_ERROR;
2886: --rollback to Start_Upload;
2887: WHEN VALUE_ERROR THEN
2888: p_return_status := FND_API.G_RET_STS_ERROR;
2889: FND_MESSAGE.SET_NAME('AK','AK_REGION_VALUE_ERROR');
2890: FND_MESSAGE.SET_TOKEN('KEY',to_char(l_region_rec.region_application_id)||' '||
2891: l_region_rec.region_code);
2892: FND_MSG_PUB.Add;
2893: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2894: SUBSTR (SQLERRM, 1, 240)||': '||l_column||'='||l_token );
2895: FND_MSG_PUB.Add;
2896: WHEN OTHERS THEN
2897: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2898: --rollback to Start_Upload;
2899: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2900: SUBSTR (SQLERRM, 1, 240) );
2901: FND_MSG_PUB.Add;
2928: -- Version Initial version number = 1.0
2929: -- History Current version number = 1.0
2930: --=======================================================
2931: procedure UPLOAD_REGION_SECOND (
2932: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2933: p_return_status OUT NOCOPY VARCHAR2,
2934: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
2935: p_pass IN NUMBER := 2
2936: ) is
2930: --=======================================================
2931: procedure UPLOAD_REGION_SECOND (
2932: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2933: p_return_status OUT NOCOPY VARCHAR2,
2934: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
2935: p_pass IN NUMBER := 2
2936: ) is
2937: l_api_name CONSTANT varchar2(30) := 'Upload_Region_Second';
2938: l_rec_index NUMBER;
3108: );
3109: end if; -- /* if REGION_EXISTS */
3110: --
3111: -- If API call returns with an error status, upload aborts
3112: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3113: (l_return_status = FND_API.G_RET_STS_ERROR) then
3114: RAISE FND_API.G_EXC_ERROR;
3115: end if; -- /* if l_return_status */
3116: end if; -- /* if G_REGION_REDO_TBL.exists(l_index) */
3109: end if; -- /* if REGION_EXISTS */
3110: --
3111: -- If API call returns with an error status, upload aborts
3112: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3113: (l_return_status = FND_API.G_RET_STS_ERROR) then
3114: RAISE FND_API.G_EXC_ERROR;
3115: end if; -- /* if l_return_status */
3116: end if; -- /* if G_REGION_REDO_TBL.exists(l_index) */
3117: end loop;
3110: --
3111: -- If API call returns with an error status, upload aborts
3112: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3113: (l_return_status = FND_API.G_RET_STS_ERROR) then
3114: RAISE FND_API.G_EXC_ERROR;
3115: end if; -- /* if l_return_status */
3116: end if; -- /* if G_REGION_REDO_TBL.exists(l_index) */
3117: end loop;
3118: G_REGION_REDO_INDEX := 0;
3524: );
3525: end if; -- /* if ITEM_EXISTS */
3526: --
3527: -- If API call returns with an error status, upload aborts
3528: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3529: (l_return_status = FND_API.G_RET_STS_ERROR) then
3530: RAISE FND_API.G_EXC_ERROR;
3531: end if; -- /* if l_return_status */
3532: end if; -- /* if G_ITEM_REDO_TBL.exists */
3525: end if; -- /* if ITEM_EXISTS */
3526: --
3527: -- If API call returns with an error status, upload aborts
3528: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3529: (l_return_status = FND_API.G_RET_STS_ERROR) then
3530: RAISE FND_API.G_EXC_ERROR;
3531: end if; -- /* if l_return_status */
3532: end if; -- /* if G_ITEM_REDO_TBL.exists */
3533: end loop;
3526: --
3527: -- If API call returns with an error status, upload aborts
3528: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3529: (l_return_status = FND_API.G_RET_STS_ERROR) then
3530: RAISE FND_API.G_EXC_ERROR;
3531: end if; -- /* if l_return_status */
3532: end if; -- /* if G_ITEM_REDO_TBL.exists */
3533: end loop;
3534: G_ITEM_REDO_INDEX := 0;
3627: );
3628: end if; -- /* if LOV_RELATION_EXISTS */
3629: --
3630: -- If API call returns with an error status, upload aborts
3631: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3632: (l_return_status = FND_API.G_RET_STS_ERROR) then
3633: RAISE FND_API.G_EXC_ERROR;
3634: end if; -- /* if l_return_status */
3635: end if; -- /* if G_LOV_RELATION_REDO_TBL.exists(l_index) */
3628: end if; -- /* if LOV_RELATION_EXISTS */
3629: --
3630: -- If API call returns with an error status, upload aborts
3631: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3632: (l_return_status = FND_API.G_RET_STS_ERROR) then
3633: RAISE FND_API.G_EXC_ERROR;
3634: end if; -- /* if l_return_status */
3635: end if; -- /* if G_LOV_RELATION_REDO_TBL.exists(l_index) */
3636: end loop;
3629: --
3630: -- If API call returns with an error status, upload aborts
3631: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3632: (l_return_status = FND_API.G_RET_STS_ERROR) then
3633: RAISE FND_API.G_EXC_ERROR;
3634: end if; -- /* if l_return_status */
3635: end if; -- /* if G_LOV_RELATION_REDO_TBL.exists(l_index) */
3636: end loop;
3637: G_LOV_RELATION_REDO_INDEX := 0;
3637: G_LOV_RELATION_REDO_INDEX := 0;
3638: G_LOV_RELATION_REDO_TBL.DELETE;
3639: end if; -- /* if G_LOV_RELATION_REDO_INDEX > 0 */
3640:
3641: p_return_status := FND_API.G_RET_STS_SUCCESS;
3642:
3643: EXCEPTION
3644: WHEN FND_API.G_EXC_ERROR THEN
3645: p_return_status := FND_API.G_RET_STS_ERROR;
3640:
3641: p_return_status := FND_API.G_RET_STS_SUCCESS;
3642:
3643: EXCEPTION
3644: WHEN FND_API.G_EXC_ERROR THEN
3645: p_return_status := FND_API.G_RET_STS_ERROR;
3646: FND_MSG_PUB.Count_And_Get (
3647: p_count => l_msg_count,
3648: p_data => l_msg_data);
3641: p_return_status := FND_API.G_RET_STS_SUCCESS;
3642:
3643: EXCEPTION
3644: WHEN FND_API.G_EXC_ERROR THEN
3645: p_return_status := FND_API.G_RET_STS_ERROR;
3646: FND_MSG_PUB.Count_And_Get (
3647: p_count => l_msg_count,
3648: p_data => l_msg_data);
3649: G_REGION_REDO_INDEX := 0;
3652: G_REGION_REDO_TBL.DELETE;
3653: G_ITEM_REDO_TBL.DELETE;
3654: G_LOV_RELATION_REDO_TBL.DELETE;
3655: WHEN OTHERS THEN
3656: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3657: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3658: SUBSTR (SQLERRM, 1, 240) );
3659: FND_MSG_PUB.Count_And_Get (
3660: p_count => l_msg_count,
3729: l_orig_ri_rec ak_region_items%ROWTYPE;
3730: l_ri_tl_rec ak_region_items_tl%ROWTYPE;
3731:
3732: begin
3733: l_return_status := FND_API.G_RET_STS_SUCCESS;
3734: open l_ri_csr( p_region_code,
3735: p_region_application_id,
3736: p_display_sequence);
3737: fetch l_ri_csr into l_ri_rec;
3879: p_return_status := l_return_status;
3880: close l_ri_csr;
3881:
3882: EXCEPTION
3883: WHEN FND_API.G_EXC_ERROR THEN
3884: p_return_status := FND_API.G_RET_STS_ERROR;
3885: WHEN OTHERS THEN
3886: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3887: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3880: close l_ri_csr;
3881:
3882: EXCEPTION
3883: WHEN FND_API.G_EXC_ERROR THEN
3884: p_return_status := FND_API.G_RET_STS_ERROR;
3885: WHEN OTHERS THEN
3886: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3887: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3888: SUBSTR (SQLERRM, 1, 240) );
3882: EXCEPTION
3883: WHEN FND_API.G_EXC_ERROR THEN
3884: p_return_status := FND_API.G_RET_STS_ERROR;
3885: WHEN OTHERS THEN
3886: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3887: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3888: SUBSTR (SQLERRM, 1, 240) );
3889: FND_MSG_PUB.Add;
3890:
3935: l_api_version_number CONSTANT number := 1.0;
3936: l_api_name CONSTANT varchar2(30) := 'Graph_Column_Exists';
3937: l_dummy number;
3938: begin
3939: IF NOT FND_API.Compatible_API_Call (
3940: l_api_version_number, p_api_version_number, l_api_name,
3941: G_PKG_NAME) then
3942: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3943: return FALSE;
3938: begin
3939: IF NOT FND_API.Compatible_API_Call (
3940: l_api_version_number, p_api_version_number, l_api_name,
3941: G_PKG_NAME) then
3942: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3943: return FALSE;
3944: END IF;
3945:
3946: open l_check_graph_column_csr;
3946: open l_check_graph_column_csr;
3947: fetch l_check_graph_column_csr into l_dummy;
3948: if (l_check_graph_column_csr%notfound) then
3949: close l_check_graph_column_csr;
3950: p_return_status := FND_API.G_RET_STS_SUCCESS;
3951: return FALSE;
3952: else
3953: close l_check_graph_column_csr;
3954: p_return_status := FND_API.G_RET_STS_SUCCESS;
3950: p_return_status := FND_API.G_RET_STS_SUCCESS;
3951: return FALSE;
3952: else
3953: close l_check_graph_column_csr;
3954: p_return_status := FND_API.G_RET_STS_SUCCESS;
3955: return TRUE;
3956: end if;
3957:
3958: EXCEPTION
3955: return TRUE;
3956: end if;
3957:
3958: EXCEPTION
3959: WHEN FND_API.G_EXC_ERROR THEN
3960: p_return_status := FND_API.G_RET_STS_ERROR;
3961: return FALSE;
3962: WHEN OTHERS THEN
3963: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3956: end if;
3957:
3958: EXCEPTION
3959: WHEN FND_API.G_EXC_ERROR THEN
3960: p_return_status := FND_API.G_RET_STS_ERROR;
3961: return FALSE;
3962: WHEN OTHERS THEN
3963: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3964: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3959: WHEN FND_API.G_EXC_ERROR THEN
3960: p_return_status := FND_API.G_RET_STS_ERROR;
3961: return FALSE;
3962: WHEN OTHERS THEN
3963: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3964: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3965: SUBSTR (SQLERRM, 1, 240) );
3966: FND_MSG_PUB.Add;
3967: return FALSE;
4028: l_api_version_number CONSTANT number := 1.0;
4029: l_api_name CONSTANT varchar2(30) := 'Lov_Relation_Exists';
4030: l_dummy number;
4031: begin
4032: IF NOT FND_API.Compatible_API_Call (
4033: l_api_version_number, p_api_version_number, l_api_name,
4034: G_PKG_NAME) then
4035: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4036: return FALSE;
4031: begin
4032: IF NOT FND_API.Compatible_API_Call (
4033: l_api_version_number, p_api_version_number, l_api_name,
4034: G_PKG_NAME) then
4035: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4036: return FALSE;
4037: END IF;
4038:
4039: open l_check_lov_relation_csr;
4039: open l_check_lov_relation_csr;
4040: fetch l_check_lov_relation_csr into l_dummy;
4041: if (l_check_lov_relation_csr%notfound) then
4042: close l_check_lov_relation_csr;
4043: p_return_status := FND_API.G_RET_STS_SUCCESS;
4044: return FALSE;
4045: else
4046: close l_check_lov_relation_csr;
4047: p_return_status := FND_API.G_RET_STS_SUCCESS;
4043: p_return_status := FND_API.G_RET_STS_SUCCESS;
4044: return FALSE;
4045: else
4046: close l_check_lov_relation_csr;
4047: p_return_status := FND_API.G_RET_STS_SUCCESS;
4048: return TRUE;
4049: end if;
4050:
4051: EXCEPTION
4048: return TRUE;
4049: end if;
4050:
4051: EXCEPTION
4052: WHEN FND_API.G_EXC_ERROR THEN
4053: p_return_status := FND_API.G_RET_STS_ERROR;
4054: return FALSE;
4055: WHEN OTHERS THEN
4056: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4049: end if;
4050:
4051: EXCEPTION
4052: WHEN FND_API.G_EXC_ERROR THEN
4053: p_return_status := FND_API.G_RET_STS_ERROR;
4054: return FALSE;
4055: WHEN OTHERS THEN
4056: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4057: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4052: WHEN FND_API.G_EXC_ERROR THEN
4053: p_return_status := FND_API.G_RET_STS_ERROR;
4054: return FALSE;
4055: WHEN OTHERS THEN
4056: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4057: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4058: SUBSTR (SQLERRM, 1, 240) );
4059: FND_MSG_PUB.Add;
4060: return FALSE;
4091: --
4092: -- Version Initial version number = 1.1
4093: --=======================================================
4094: FUNCTION VALIDATE_GRAPH_COLUMN (
4095: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4096: p_api_version_number IN NUMBER,
4097: p_return_status OUT NOCOPY VARCHAR2,
4098: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4099: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4094: FUNCTION VALIDATE_GRAPH_COLUMN (
4095: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4096: p_api_version_number IN NUMBER,
4097: p_return_status OUT NOCOPY VARCHAR2,
4098: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4099: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4100: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4101: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4102: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4095: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4096: p_api_version_number IN NUMBER,
4097: p_return_status OUT NOCOPY VARCHAR2,
4098: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4099: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4100: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4101: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4102: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4103: p_pass IN NUMBER := 2,
4096: p_api_version_number IN NUMBER,
4097: p_return_status OUT NOCOPY VARCHAR2,
4098: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4099: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4100: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4101: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4102: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4103: p_pass IN NUMBER := 2,
4104: p_caller IN VARCHAR2
4097: p_return_status OUT NOCOPY VARCHAR2,
4098: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4099: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4100: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4101: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4102: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4103: p_pass IN NUMBER := 2,
4104: p_caller IN VARCHAR2
4105: ) return boolean is
4098: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4099: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4100: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4101: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4102: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4103: p_pass IN NUMBER := 2,
4104: p_caller IN VARCHAR2
4105: ) return boolean is
4106: cursor l_check_region_graph_csr (param_region_code in varchar2,
4128: l_error BOOLEAN;
4129: l_return_status VARCHAR2(1);
4130: begin
4131:
4132: IF NOT FND_API.Compatible_API_Call (
4133: l_api_version_number, p_api_version_number, l_api_name,
4134: G_PKG_NAME) then
4135: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4136: return FALSE;
4131:
4132: IF NOT FND_API.Compatible_API_Call (
4133: l_api_version_number, p_api_version_number, l_api_name,
4134: G_PKG_NAME) then
4135: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4136: return FALSE;
4137: END IF;
4138:
4139: l_error := FALSE;
4138:
4139: l_error := FALSE;
4140:
4141: --** if validation level is none, no validation is necessary
4142: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4143: p_return_status := FND_API.G_RET_STS_SUCCESS;
4144: return TRUE;
4145: end if;
4146:
4139: l_error := FALSE;
4140:
4141: --** if validation level is none, no validation is necessary
4142: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4143: p_return_status := FND_API.G_RET_STS_SUCCESS;
4144: return TRUE;
4145: end if;
4146:
4147: --** check that key columns are not null and not missing **
4145: end if;
4146:
4147: --** check that key columns are not null and not missing **
4148: if ((p_region_application_id is null) or
4149: (p_region_application_id = FND_API.G_MISS_NUM)) then
4150: l_error := TRUE;
4151: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4152: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4153: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
4155: end if;
4156: end if;
4157:
4158: if ((p_region_code is null) or
4159: (p_region_code = FND_API.G_MISS_CHAR)) then
4160: l_error := TRUE;
4161: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4162: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4163: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
4165: end if;
4166: end if;
4167:
4168: if ((p_attribute_application_id is null) or
4169: (p_attribute_application_id = FND_API.G_MISS_NUM)) then
4170: l_error := TRUE;
4171: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4172: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4173: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
4175: end if;
4176: end if;
4177:
4178: if ((p_attribute_code is null) or
4179: (p_attribute_code = FND_API.G_MISS_CHAR)) then
4180: l_error := TRUE;
4181: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4182: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4183: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
4185: end if;
4186: end if;
4187:
4188: if ((p_graph_number is null) or
4189: (p_graph_number = FND_API.G_MISS_NUM)) then
4190: l_error := TRUE;
4191: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4192: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4193: FND_MESSAGE.SET_TOKEN('COLUMN', 'GRAPH_NUMBER');
4226: end if;
4227: close l_check_region_graph_csr;
4228:
4229: -- return true if no error, false otherwise
4230: p_return_status := FND_API.G_RET_STS_SUCCESS;
4231: return (not l_error);
4232:
4233: EXCEPTION
4234: WHEN FND_API.G_EXC_ERROR THEN
4230: p_return_status := FND_API.G_RET_STS_SUCCESS;
4231: return (not l_error);
4232:
4233: EXCEPTION
4234: WHEN FND_API.G_EXC_ERROR THEN
4235: p_return_status := FND_API.G_RET_STS_ERROR;
4236: return FALSE;
4237: WHEN OTHERS THEN
4238: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4231: return (not l_error);
4232:
4233: EXCEPTION
4234: WHEN FND_API.G_EXC_ERROR THEN
4235: p_return_status := FND_API.G_RET_STS_ERROR;
4236: return FALSE;
4237: WHEN OTHERS THEN
4238: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4239: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4234: WHEN FND_API.G_EXC_ERROR THEN
4235: p_return_status := FND_API.G_RET_STS_ERROR;
4236: return FALSE;
4237: WHEN OTHERS THEN
4238: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4239: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4240: SUBSTR (SQLERRM, 1, 240) );
4241: FND_MSG_PUB.Add;
4242: return FALSE;
4274: --
4275: -- Version Initial version number = 1.1
4276: --=======================================================
4277: FUNCTION VALIDATE_LOV_RELATION (
4278: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4279: p_api_version_number IN NUMBER,
4280: p_return_status OUT NOCOPY VARCHAR2,
4281: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4282: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4277: FUNCTION VALIDATE_LOV_RELATION (
4278: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4279: p_api_version_number IN NUMBER,
4280: p_return_status OUT NOCOPY VARCHAR2,
4281: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4282: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4283: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4284: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4285: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4278: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4279: p_api_version_number IN NUMBER,
4280: p_return_status OUT NOCOPY VARCHAR2,
4281: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4282: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4283: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4284: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4285: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4286: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4279: p_api_version_number IN NUMBER,
4280: p_return_status OUT NOCOPY VARCHAR2,
4281: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4282: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4283: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4284: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4285: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4286: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4287: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4280: p_return_status OUT NOCOPY VARCHAR2,
4281: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4282: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4283: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4284: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4285: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4286: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4287: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4288: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4281: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4282: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4283: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4284: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4285: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4286: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4287: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4288: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4289: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4282: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4283: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4284: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4285: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4286: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4287: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4288: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4289: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4290: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4283: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4284: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4285: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4286: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4287: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4288: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4289: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4290: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4291: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4284: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4285: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4286: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4287: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4288: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4289: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4290: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4291: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4292: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4285: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4286: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4287: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4288: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4289: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4290: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4291: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4292: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4293: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4286: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4287: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4288: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4289: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4290: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4291: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4292: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4293: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4294: p_caller IN VARCHAR2,
4287: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4288: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4289: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4290: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4291: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4292: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4293: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4294: p_caller IN VARCHAR2,
4295: p_pass IN NUMBER := 2
4288: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4289: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4290: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4291: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4292: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4293: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4294: p_caller IN VARCHAR2,
4295: p_pass IN NUMBER := 2
4296: ) return boolean is
4289: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4290: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4291: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4292: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4293: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4294: p_caller IN VARCHAR2,
4295: p_pass IN NUMBER := 2
4296: ) return boolean is
4297: cursor l_check_region_csr (param_region_code in varchar2, param_region_appl_id in number) is
4315: l_error BOOLEAN;
4316: l_return_status VARCHAR2(1);
4317: begin
4318:
4319: IF NOT FND_API.Compatible_API_Call (
4320: l_api_version_number, p_api_version_number, l_api_name,
4321: G_PKG_NAME) then
4322: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4323: return FALSE;
4318:
4319: IF NOT FND_API.Compatible_API_Call (
4320: l_api_version_number, p_api_version_number, l_api_name,
4321: G_PKG_NAME) then
4322: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4323: return FALSE;
4324: END IF;
4325:
4326: l_error := FALSE;
4325:
4326: l_error := FALSE;
4327:
4328: --** if validation level is none, no validation is necessary
4329: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4330: p_return_status := FND_API.G_RET_STS_SUCCESS;
4331: return TRUE;
4332: end if;
4333:
4326: l_error := FALSE;
4327:
4328: --** if validation level is none, no validation is necessary
4329: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4330: p_return_status := FND_API.G_RET_STS_SUCCESS;
4331: return TRUE;
4332: end if;
4333:
4334: --** check that key columns are not null and not missing **
4332: end if;
4333:
4334: --** check that key columns are not null and not missing **
4335: if ((p_region_application_id is null) or
4336: (p_region_application_id = FND_API.G_MISS_NUM)) then
4337: l_error := TRUE;
4338: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4339: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4340: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
4342: end if;
4343: end if;
4344:
4345: if ((p_region_code is null) or
4346: (p_region_code = FND_API.G_MISS_CHAR)) then
4347: l_error := TRUE;
4348: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4349: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4350: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
4352: end if;
4353: end if;
4354:
4355: if ((p_attribute_application_id is null) or
4356: (p_attribute_application_id = FND_API.G_MISS_NUM)) then
4357: l_error := TRUE;
4358: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4359: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4360: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
4362: end if;
4363: end if;
4364:
4365: if ((p_attribute_code is null) or
4366: (p_attribute_code = FND_API.G_MISS_CHAR)) then
4367: l_error := TRUE;
4368: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4369: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4370: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
4372: end if;
4373: end if;
4374:
4375: if ((p_lov_region_appl_id is null) or
4376: (p_lov_region_appl_id = FND_API.G_MISS_NUM)) then
4377: l_error := TRUE;
4378: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4379: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4380: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
4382: end if;
4383: end if;
4384:
4385: if ((p_lov_region_code is null) or
4386: (p_lov_region_code = FND_API.G_MISS_CHAR)) then
4387: l_error := TRUE;
4388: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4389: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4390: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
4392: end if;
4393: end if;
4394:
4395: if ((p_lov_attribute_appl_id is null) or
4396: (p_lov_attribute_appl_id = FND_API.G_MISS_NUM)) then
4397: l_error := TRUE;
4398: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4399: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4400: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
4402: end if;
4403: end if;
4404:
4405: if ((p_lov_attribute_code is null) or
4406: (p_lov_attribute_code = FND_API.G_MISS_CHAR)) then
4407: l_error := TRUE;
4408: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4409: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4410: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
4415: -- do not raise an error for now, there are ppl who has an old jlt file and upload
4416: -- it manually to a database which contains this patch
4417: /**
4418: if ((p_base_region_appl_id is null) or
4419: (p_base_region_appl_id = FND_API.G_MISS_NUM)) then
4420:
4421: l_error := TRUE;
4422: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4423: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4430: -- do not raise an error for now, there are ppl who has an old jlt file and upload
4431: -- it manually to a database which contains this patch
4432: /**
4433: if ((p_base_region_code is null) or
4434: (p_base_region_code = FND_API.G_MISS_CHAR)) then
4435: l_error := TRUE;
4436: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4437: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4438: FND_MESSAGE.SET_TOKEN('COLUMN', 'BASE_REGION_CODE');
4443:
4444: -- these two columns are not part of primary key but cannot be null
4445: --
4446: if ((p_base_attribute_appl_id is null) or
4447: (p_base_attribute_appl_id = FND_API.G_MISS_NUM)) then
4448: l_error := TRUE;
4449: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4450: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4451: FND_MESSAGE.SET_TOKEN('COLUMN', 'BASE_ATTRIBUTE_APPL_ID');
4453: end if;
4454: end if;
4455:
4456: if ((p_base_attribute_code is null) or
4457: (p_base_attribute_code = FND_API.G_MISS_CHAR)) then
4458: l_error := TRUE;
4459: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4460: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4461: FND_MESSAGE.SET_TOKEN('COLUMN', 'BASE_ATTRIBUTE_CODE');
4525: end if;
4526: close l_check_region_item_csr;
4527:
4528: -- direction_flag
4529: if ( (p_direction_flag <> FND_API.G_MISS_CHAR) and
4530: (p_direction_flag is not null) )then
4531: if (NOT AK_ON_OBJECTS_PVT.VALID_LOOKUP_CODE (
4532: p_api_version_number => 1.0,
4533: p_return_status => l_return_status,
4549: end if;
4550: end if;
4551:
4552: -- return true if no error, false otherwise
4553: p_return_status := FND_API.G_RET_STS_SUCCESS;
4554: return (not l_error);
4555:
4556: EXCEPTION
4557: WHEN FND_API.G_EXC_ERROR THEN
4553: p_return_status := FND_API.G_RET_STS_SUCCESS;
4554: return (not l_error);
4555:
4556: EXCEPTION
4557: WHEN FND_API.G_EXC_ERROR THEN
4558: p_return_status := FND_API.G_RET_STS_ERROR;
4559: return FALSE;
4560: WHEN OTHERS THEN
4561: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4554: return (not l_error);
4555:
4556: EXCEPTION
4557: WHEN FND_API.G_EXC_ERROR THEN
4558: p_return_status := FND_API.G_RET_STS_ERROR;
4559: return FALSE;
4560: WHEN OTHERS THEN
4561: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4562: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4557: WHEN FND_API.G_EXC_ERROR THEN
4558: p_return_status := FND_API.G_RET_STS_ERROR;
4559: return FALSE;
4560: WHEN OTHERS THEN
4561: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4562: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4563: SUBSTR (SQLERRM, 1, 240) );
4564: FND_MSG_PUB.Add;
4565: return FALSE;
4591: --
4592: -- Version Initial version number = 1.1
4593: --=======================================================
4594: PROCEDURE CREATE_GRAPH_COLUMN (
4595: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4596: p_api_version_number IN NUMBER,
4597: p_init_msg_tbl IN BOOLEAN := FALSE,
4598: p_msg_count OUT NOCOPY NUMBER,
4599: p_msg_data OUT NOCOPY VARCHAR2,
4597: p_init_msg_tbl IN BOOLEAN := FALSE,
4598: p_msg_count OUT NOCOPY NUMBER,
4599: p_msg_data OUT NOCOPY VARCHAR2,
4600: p_return_status OUT NOCOPY VARCHAR2,
4601: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4602: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4603: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4604: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4605: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4598: p_msg_count OUT NOCOPY NUMBER,
4599: p_msg_data OUT NOCOPY VARCHAR2,
4600: p_return_status OUT NOCOPY VARCHAR2,
4601: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4602: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4603: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4604: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4605: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4606: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4599: p_msg_data OUT NOCOPY VARCHAR2,
4600: p_return_status OUT NOCOPY VARCHAR2,
4601: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4602: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4603: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4604: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4605: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4606: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4607: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4600: p_return_status OUT NOCOPY VARCHAR2,
4601: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4602: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4603: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4604: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4605: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4606: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4607: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4608: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4601: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4602: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4603: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4604: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4605: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4606: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4607: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4608: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4609: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4602: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4603: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4604: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4605: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4606: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4607: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4608: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4609: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4610: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4603: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4604: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4605: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4606: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4607: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4608: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4609: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4610: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4611: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4604: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4605: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4606: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4607: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4608: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4609: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4610: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4611: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4612: p_pass IN NUMBER,
4605: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4606: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4607: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4608: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4609: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4610: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4611: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4612: p_pass IN NUMBER,
4613: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4606: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4607: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4608: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4609: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4610: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4611: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4612: p_pass IN NUMBER,
4613: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4614: ) is
4607: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4608: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4609: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4610: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4611: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4612: p_pass IN NUMBER,
4613: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4614: ) is
4615:
4623: l_return_status varchar2(1);
4624:
4625: begin
4626:
4627: IF NOT FND_API.Compatible_API_Call (
4628: l_api_version_number, p_api_version_number, l_api_name,
4629: G_PKG_NAME) then
4630: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4631: return;
4626:
4627: IF NOT FND_API.Compatible_API_Call (
4628: l_api_version_number, p_api_version_number, l_api_name,
4629: G_PKG_NAME) then
4630: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4631: return;
4632: END IF;
4633:
4634: -- Initialize the message table if requested.
4651: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4652: FND_MESSAGE.SET_NAME('AK','AK_GRAPH_COLUMN_EXISTS');
4653: FND_MSG_PUB.Add;
4654: end if;
4655: raise FND_API.G_EXC_ERROR;
4656: end if;
4657:
4658: --** validate table columns passed in **
4659: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4655: raise FND_API.G_EXC_ERROR;
4656: end if;
4657:
4658: --** validate table columns passed in **
4659: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4660: if not AK_REGION2_PVT.VALIDATE_GRAPH_COLUMN (
4661: p_validation_level => p_validation_level,
4662: p_api_version_number => 1.0,
4663: p_return_status => l_return_status,
4672: -- Do not raise an error if it's the first pass
4673: if (p_pass = 1) then
4674: p_copy_redo_flag := TRUE;
4675: else
4676: raise FND_API.G_EXC_ERROR;
4677: end if;
4678: end if;
4679: end if;
4680:
4733: to_char(p_graph_number));
4734: FND_MSG_PUB.Add;
4735: end if;
4736:
4737: p_return_status := FND_API.G_RET_STS_SUCCESS;
4738:
4739: FND_MSG_PUB.Count_And_Get (
4740: p_count => p_msg_count,
4741: p_data => p_msg_data);
4750: ' '||p_attribute_code||' '||
4751: to_char(p_graph_number));
4752: FND_MSG_PUB.Add;
4753: end if;
4754: p_return_status := FND_API.G_RET_STS_ERROR;
4755: rollback to start_create_graph_column;
4756: FND_MSG_PUB.Count_And_Get (
4757: p_count => p_msg_count,
4758: p_data => p_msg_data);
4756: FND_MSG_PUB.Count_And_Get (
4757: p_count => p_msg_count,
4758: p_data => p_msg_data);
4759: WHEN OTHERS THEN
4760: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4761: rollback to start_create_graph_column;
4762: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4763: SUBSTR (SQLERRM, 1, 240) );
4764: FND_MSG_PUB.Add;
4793: --
4794: -- Version Initial version number = 1.1
4795: --=======================================================
4796: PROCEDURE CREATE_LOV_RELATION (
4797: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4798: p_api_version_number IN NUMBER,
4799: p_init_msg_tbl IN BOOLEAN := FALSE,
4800: p_msg_count OUT NOCOPY NUMBER,
4801: p_msg_data OUT NOCOPY VARCHAR2,
4799: p_init_msg_tbl IN BOOLEAN := FALSE,
4800: p_msg_count OUT NOCOPY NUMBER,
4801: p_msg_data OUT NOCOPY VARCHAR2,
4802: p_return_status OUT NOCOPY VARCHAR2,
4803: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4804: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4805: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4806: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4807: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4800: p_msg_count OUT NOCOPY NUMBER,
4801: p_msg_data OUT NOCOPY VARCHAR2,
4802: p_return_status OUT NOCOPY VARCHAR2,
4803: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4804: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4805: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4806: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4807: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4808: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4801: p_msg_data OUT NOCOPY VARCHAR2,
4802: p_return_status OUT NOCOPY VARCHAR2,
4803: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4804: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4805: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4806: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4807: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4808: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4809: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4802: p_return_status OUT NOCOPY VARCHAR2,
4803: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4804: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4805: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4806: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4807: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4808: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4809: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4810: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4803: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4804: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4805: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4806: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4807: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4808: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4809: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4810: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4811: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4804: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4805: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4806: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4807: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4808: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4809: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4810: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4811: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4812: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4805: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4806: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4807: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4808: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4809: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4810: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4811: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4812: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4813: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4806: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4807: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4808: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4809: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4810: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4811: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4812: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4813: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4814: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4807: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4808: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4809: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4810: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4811: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4812: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4813: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4814: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4815: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4808: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4809: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4810: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4811: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4812: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4813: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4814: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4815: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4816: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4809: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4810: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4811: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4812: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4813: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4814: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4815: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4816: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4817: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4810: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4811: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4812: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4813: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4814: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4815: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4816: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4817: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4818: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4811: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4812: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4813: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4814: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4815: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4816: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4817: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4818: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4819: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4812: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4813: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4814: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4815: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4816: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4817: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4818: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4819: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4820: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4813: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4814: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4815: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4816: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4817: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4818: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4819: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4820: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4821: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4814: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4815: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4816: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4817: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4818: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4819: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4820: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4821: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4822: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4815: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4816: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4817: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4818: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4819: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4820: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4821: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4822: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4823: p_pass IN NUMBER,
4816: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4817: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4818: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4819: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4820: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4821: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4822: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4823: p_pass IN NUMBER,
4824: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4817: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4818: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4819: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4820: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4821: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4822: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4823: p_pass IN NUMBER,
4824: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4825: ) is
4818: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4819: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4820: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4821: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4822: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4823: p_pass IN NUMBER,
4824: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4825: ) is
4826:
4836: l_return_status varchar2(1);
4837:
4838: begin
4839:
4840: IF NOT FND_API.Compatible_API_Call (
4841: l_api_version_number, p_api_version_number, l_api_name,
4842: G_PKG_NAME) then
4843: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4844: return;
4839:
4840: IF NOT FND_API.Compatible_API_Call (
4841: l_api_version_number, p_api_version_number, l_api_name,
4842: G_PKG_NAME) then
4843: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4844: return;
4845: END IF;
4846:
4847: -- Initialize the message table if requested.
4872: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4873: FND_MESSAGE.SET_NAME('AK','AK_LOV_RELATION_EXISTS');
4874: FND_MSG_PUB.Add;
4875: end if;
4876: raise FND_API.G_EXC_ERROR;
4877: end if;
4878:
4879: --** validate table columns passed in **
4880: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4876: raise FND_API.G_EXC_ERROR;
4877: end if;
4878:
4879: --** validate table columns passed in **
4880: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4881: if not AK_REGION2_PVT.VALIDATE_LOV_RELATION (
4882: p_validation_level => p_validation_level,
4883: p_api_version_number => 1.0,
4884: p_return_status => l_return_status,
4901: -- Do not raise an error if it's the first pass
4902: if (p_pass = 1) then
4903: p_copy_redo_flag := TRUE;
4904: else
4905: raise FND_API.G_EXC_ERROR;
4906: end if;
4907: end if;
4908: end if;
4909:
4910: -- Do not insert if fails validation ie. p_copy_redo_flag = true
4911: if (not p_copy_redo_flag) then
4912:
4913: -- default a value for required_flag column if no value is given
4914: if ( p_required_flag <> FND_API.G_MISS_CHAR and p_required_flag is not null ) then
4915: l_required_flag := p_required_flag;
4916: end if;
4917:
4918: if (p_created_by <> FND_API.G_MISS_NUM) then
4914: if ( p_required_flag <> FND_API.G_MISS_CHAR and p_required_flag is not null ) then
4915: l_required_flag := p_required_flag;
4916: end if;
4917:
4918: if (p_created_by <> FND_API.G_MISS_NUM) then
4919: l_created_by := p_created_by;
4920: end if;
4921:
4922: if (p_creation_date <> FND_API.G_MISS_DATE) then
4918: if (p_created_by <> FND_API.G_MISS_NUM) then
4919: l_created_by := p_created_by;
4920: end if;
4921:
4922: if (p_creation_date <> FND_API.G_MISS_DATE) then
4923: l_creation_date := p_creation_date;
4924: end if;
4925:
4926: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
4922: if (p_creation_date <> FND_API.G_MISS_DATE) then
4923: l_creation_date := p_creation_date;
4924: end if;
4925:
4926: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
4927: l_last_updated_by := p_last_updated_by;
4928: end if;
4929:
4930: if (p_last_update_date <> FND_API.G_MISS_DATE) then
4926: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
4927: l_last_updated_by := p_last_updated_by;
4928: end if;
4929:
4930: if (p_last_update_date <> FND_API.G_MISS_DATE) then
4931: l_last_update_date := p_last_update_date;
4932: end if;
4933:
4934: if (p_last_update_login <> FND_API.G_MISS_NUM) then
4930: if (p_last_update_date <> FND_API.G_MISS_DATE) then
4931: l_last_update_date := p_last_update_date;
4932: end if;
4933:
4934: if (p_last_update_login <> FND_API.G_MISS_NUM) then
4935: l_last_update_login := p_last_update_login;
4936: end if;
4937:
4938:
5013: end if;
5014:
5015: end if; -- /* if not p_copy_redo_flag */
5016:
5017: p_return_status := FND_API.G_RET_STS_SUCCESS;
5018:
5019: FND_MSG_PUB.Count_And_Get (
5020: p_count => p_msg_count,
5021: p_data => p_msg_data);
5032: p_lov_region_code||' '||to_char(p_base_attribute_appl_id)||
5033: ' '||p_base_attribute_code);
5034: FND_MSG_PUB.Add;
5035: end if;
5036: p_return_status := FND_API.G_RET_STS_ERROR;
5037: rollback to start_create_lov_relation;
5038: FND_MSG_PUB.Count_And_Get (
5039: p_count => p_msg_count,
5040: p_data => p_msg_data);
5037: rollback to start_create_lov_relation;
5038: FND_MSG_PUB.Count_And_Get (
5039: p_count => p_msg_count,
5040: p_data => p_msg_data);
5041: WHEN FND_API.G_EXC_ERROR THEN
5042: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5043: FND_MESSAGE.SET_NAME('AK','AK_REGION_NOT_CREATED');
5044: FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
5045: p_region_code||' '||to_char(p_attribute_application_id)||
5049: p_lov_region_code||' '||to_char(p_base_attribute_appl_id)||
5050: ' '||p_base_attribute_code);
5051: FND_MSG_PUB.Add;
5052: end if;
5053: p_return_status := FND_API.G_RET_STS_ERROR;
5054: rollback to start_create_lov_relation;
5055: FND_MSG_PUB.Count_And_Get (
5056: p_count => p_msg_count,
5057: p_data => p_msg_data);
5055: FND_MSG_PUB.Count_And_Get (
5056: p_count => p_msg_count,
5057: p_data => p_msg_data);
5058: WHEN OTHERS THEN
5059: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5060: rollback to start_create_region;
5061: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5062: SUBSTR (SQLERRM, 1, 240) );
5063: FND_MSG_PUB.Add;
5091: -- Version Initial version number = 1.0
5092: -- History Current version number = 1.0
5093: --=======================================================
5094: PROCEDURE UPDATE_GRAPH_COLUMN (
5095: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5096: p_api_version_number IN NUMBER,
5097: p_init_msg_tbl IN BOOLEAN := FALSE,
5098: p_msg_count OUT NOCOPY NUMBER,
5099: p_msg_data OUT NOCOPY VARCHAR2,
5097: p_init_msg_tbl IN BOOLEAN := FALSE,
5098: p_msg_count OUT NOCOPY NUMBER,
5099: p_msg_data OUT NOCOPY VARCHAR2,
5100: p_return_status OUT NOCOPY VARCHAR2,
5101: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5102: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5103: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5104: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5105: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5098: p_msg_count OUT NOCOPY NUMBER,
5099: p_msg_data OUT NOCOPY VARCHAR2,
5100: p_return_status OUT NOCOPY VARCHAR2,
5101: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5102: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5103: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5104: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5105: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5106: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5099: p_msg_data OUT NOCOPY VARCHAR2,
5100: p_return_status OUT NOCOPY VARCHAR2,
5101: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5102: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5103: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5104: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5105: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5106: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5107: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5100: p_return_status OUT NOCOPY VARCHAR2,
5101: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5102: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5103: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5104: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5105: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5106: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5107: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5108: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5101: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5102: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5103: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5104: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5105: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5106: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5107: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5108: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5109: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5102: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5103: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5104: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5105: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5106: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5107: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5108: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5109: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5110: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5103: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5104: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5105: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5106: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5107: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5108: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5109: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5110: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5111: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5104: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5105: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5106: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5107: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5108: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5109: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5110: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5111: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5112: p_pass IN NUMBER,
5105: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5106: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5107: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5108: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5109: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5110: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5111: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5112: p_pass IN NUMBER,
5113: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5106: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5107: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5108: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5109: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5110: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5111: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5112: p_pass IN NUMBER,
5113: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5114: ) is
5107: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5108: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5109: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5110: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5111: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5112: p_pass IN NUMBER,
5113: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5114: ) is
5115: cursor l_get_row_csr is
5130: l_last_update_login number;
5131: l_last_updated_by number;
5132: l_return_status varchar2(1);
5133: begin
5134: IF NOT FND_API.Compatible_API_Call (
5135: l_api_version_number, p_api_version_number, l_api_name,
5136: G_PKG_NAME) then
5137: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5138: return;
5133: begin
5134: IF NOT FND_API.Compatible_API_Call (
5135: l_api_version_number, p_api_version_number, l_api_name,
5136: G_PKG_NAME) then
5137: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5138: return;
5139: END IF;
5140:
5141: -- Initialize the message table if requested.
5155: FND_MSG_PUB.Add;
5156: end if;
5157: --dbms_output.put_line(l_api_name || 'Error - Row does not exist');
5158: close l_get_row_csr;
5159: raise FND_API.G_EXC_ERROR;
5160: end if;
5161: close l_get_row_csr;
5162:
5163: --** validate table columns passed in **
5160: end if;
5161: close l_get_row_csr;
5162:
5163: --** validate table columns passed in **
5164: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5165: if not AK_REGION2_PVT.VALIDATE_GRAPH_COLUMN (
5166: p_validation_level => p_validation_level,
5167: p_api_version_number => 1.0,
5168: p_return_status => l_return_status,
5178: -- Do not raise an error if it's the first pass
5179: if (p_pass = 1) then
5180: p_copy_redo_flag := TRUE;
5181: else
5182: raise FND_API.G_EXC_ERROR;
5183: end if; -- if p_pass
5184: end if;
5185: end if;
5186:
5220: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5221: FND_MESSAGE.SET_NAME('AK','AK_GRAPH_COLUMN_UPDATE_FAILED');
5222: FND_MSG_PUB.Add;
5223: end if;
5224: raise FND_API.G_EXC_ERROR;
5225: end if;
5226:
5227: -- ** commit the update **
5228: -- commit;
5233: ' ' || p_region_code);
5234: FND_MSG_PUB.Add;
5235: end if;
5236:
5237: p_return_status := FND_API.G_RET_STS_SUCCESS;
5238:
5239: FND_MSG_PUB.Count_And_Get (
5240: p_count => p_msg_count,
5241: p_data => p_msg_data);
5251: to_char(p_graph_number));
5252: FND_MSG_PUB.Add;
5253: end if;
5254: rollback to start_update_graph_column;
5255: p_return_status := FND_API.G_RET_STS_ERROR;
5256: FND_MSG_PUB.Count_And_Get (
5257: p_count => p_msg_count,
5258: p_data => p_msg_data);
5259: WHEN FND_API.G_EXC_ERROR THEN
5255: p_return_status := FND_API.G_RET_STS_ERROR;
5256: FND_MSG_PUB.Count_And_Get (
5257: p_count => p_msg_count,
5258: p_data => p_msg_data);
5259: WHEN FND_API.G_EXC_ERROR THEN
5260: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5261: FND_MESSAGE.SET_NAME('AK','AK_GRAPH_COLUMN_NOT_UPDATED');
5262: FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
5263: p_region_code||' '||
5265: ' '||p_attribute_code||' '||
5266: to_char(p_graph_number));
5267: FND_MSG_PUB.Add;
5268: end if;
5269: p_return_status := FND_API.G_RET_STS_ERROR;
5270: rollback to start_update_graph_column;
5271: FND_MSG_PUB.Count_And_Get (
5272: p_count => p_msg_count,
5273: p_data => p_msg_data);
5271: FND_MSG_PUB.Count_And_Get (
5272: p_count => p_msg_count,
5273: p_data => p_msg_data);
5274: WHEN OTHERS THEN
5275: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5276: rollback to start_update_graph_column;
5277: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5278: SUBSTR (SQLERRM, 1, 240) );
5279: FND_MSG_PUB.Add;
5310: -- Version Initial version number = 1.0
5311: -- History Current version number = 1.0
5312: --=======================================================
5313: PROCEDURE UPDATE_LOV_RELATION (
5314: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5315: p_api_version_number IN NUMBER,
5316: p_init_msg_tbl IN BOOLEAN := FALSE,
5317: p_msg_count OUT NOCOPY NUMBER,
5318: p_msg_data OUT NOCOPY VARCHAR2,
5316: p_init_msg_tbl IN BOOLEAN := FALSE,
5317: p_msg_count OUT NOCOPY NUMBER,
5318: p_msg_data OUT NOCOPY VARCHAR2,
5319: p_return_status OUT NOCOPY VARCHAR2,
5320: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5321: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5322: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5323: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5324: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5317: p_msg_count OUT NOCOPY NUMBER,
5318: p_msg_data OUT NOCOPY VARCHAR2,
5319: p_return_status OUT NOCOPY VARCHAR2,
5320: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5321: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5322: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5323: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5324: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5325: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5318: p_msg_data OUT NOCOPY VARCHAR2,
5319: p_return_status OUT NOCOPY VARCHAR2,
5320: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5321: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5322: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5323: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5324: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5325: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5326: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5319: p_return_status OUT NOCOPY VARCHAR2,
5320: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5321: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5322: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5323: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5324: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5325: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5326: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5327: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5320: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5321: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5322: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5323: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5324: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5325: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5326: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5327: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5328: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5321: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5322: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5323: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5324: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5325: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5326: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5327: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5328: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5329: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5322: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5323: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5324: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5325: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5326: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5327: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5328: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5329: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5330: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5323: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5324: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5325: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5326: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5327: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5328: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5329: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5330: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5331: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5324: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5325: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5326: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5327: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5328: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5329: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5330: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5331: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5332: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5325: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5326: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5327: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5328: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5329: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5330: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5331: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5332: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5333: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5326: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5327: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5328: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5329: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5330: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5331: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5332: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5333: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5334: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5327: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5328: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5329: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5330: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5331: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5332: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5333: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5334: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5335: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5328: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5329: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5330: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5331: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5332: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5333: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5334: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5335: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5336: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5329: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5330: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5331: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5332: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5333: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5334: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5335: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5336: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5337: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5330: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5331: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5332: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5333: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5334: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5335: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5336: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5337: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5338: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5331: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5332: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5333: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5334: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5335: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5336: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5337: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5338: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5339: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5332: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5333: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5334: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5335: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5336: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5337: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5338: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5339: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5340: p_pass IN NUMBER,
5333: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5334: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5335: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5336: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5337: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5338: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5339: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5340: p_pass IN NUMBER,
5341: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5334: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5335: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5336: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5337: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5338: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5339: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5340: p_pass IN NUMBER,
5341: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5342: ) is
5335: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5336: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5337: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5338: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5339: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5340: p_pass IN NUMBER,
5341: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5342: ) is
5343: cursor l_get_row_csr is
5367: l_last_update_login number;
5368: l_last_updated_by number;
5369: l_return_status varchar2(1);
5370: begin
5371: IF NOT FND_API.Compatible_API_Call (
5372: l_api_version_number, p_api_version_number, l_api_name,
5373: G_PKG_NAME) then
5374: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5375: return;
5370: begin
5371: IF NOT FND_API.Compatible_API_Call (
5372: l_api_version_number, p_api_version_number, l_api_name,
5373: G_PKG_NAME) then
5374: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5375: return;
5376: END IF;
5377:
5378: -- Initialize the message table if requested.
5392: FND_MSG_PUB.Add;
5393: end if;
5394: --dbms_output.put_line(l_api_name || 'Error - Row does not exist');
5395: close l_get_row_csr;
5396: raise FND_API.G_EXC_ERROR;
5397: end if;
5398: close l_get_row_csr;
5399:
5400: --** validate table columns passed in **
5397: end if;
5398: close l_get_row_csr;
5399:
5400: --** validate table columns passed in **
5401: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5402: if not AK_REGION2_PVT.VALIDATE_LOV_RELATION (
5403: p_validation_level => p_validation_level,
5404: p_api_version_number => 1.0,
5405: p_return_status => l_return_status,
5423: -- Do not raise an error if it's the first pass
5424: if (p_pass = 1) then
5425: p_copy_redo_flag := TRUE;
5426: else
5427: raise FND_API.G_EXC_ERROR;
5428: end if; -- /* if p_pass */
5429: end if;
5430: end if;
5431:
5438:
5439: --** - next, load non-null, non-key columns **
5440:
5441: -- default a value for required_flag column if no value is given
5442: if ( p_required_flag <> FND_API.G_MISS_CHAR and p_required_flag is not null ) then
5443: l_lov_relation_rec.required_flag := p_required_flag;
5444: else
5445: l_lov_relation_rec.required_flag := l_required_flag;
5446: end if;
5444: else
5445: l_lov_relation_rec.required_flag := l_required_flag;
5446: end if;
5447:
5448: if (p_created_by <> FND_API.G_MISS_NUM) then
5449: l_created_by := p_created_by;
5450: end if;
5451:
5452: if (p_creation_date <> FND_API.G_MISS_DATE) then
5448: if (p_created_by <> FND_API.G_MISS_NUM) then
5449: l_created_by := p_created_by;
5450: end if;
5451:
5452: if (p_creation_date <> FND_API.G_MISS_DATE) then
5453: l_creation_date := p_creation_date;
5454: end if;
5455:
5456: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5452: if (p_creation_date <> FND_API.G_MISS_DATE) then
5453: l_creation_date := p_creation_date;
5454: end if;
5455:
5456: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5457: l_last_updated_by := p_last_updated_by;
5458: end if;
5459:
5460: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5456: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5457: l_last_updated_by := p_last_updated_by;
5458: end if;
5459:
5460: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5461: l_last_update_date := p_last_update_date;
5462: end if;
5463:
5464: if (p_last_update_login <> FND_API.G_MISS_NUM) then
5460: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5461: l_last_update_date := p_last_update_date;
5462: end if;
5463:
5464: if (p_last_update_login <> FND_API.G_MISS_NUM) then
5465: l_last_update_login := p_last_update_login;
5466: end if;
5467:
5468: -- Set WHO columns
5499: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5500: FND_MESSAGE.SET_NAME('AK','AK_LOV_RELATION_UPDATE_FAILED');
5501: FND_MSG_PUB.Add;
5502: end if;
5503: raise FND_API.G_EXC_ERROR;
5504: end if;
5505:
5506: -- /** commit the update **/
5507: -- commit;
5516: end if;
5517:
5518: end if; -- /* if not p_copy_redo_flag */
5519:
5520: p_return_status := FND_API.G_RET_STS_SUCCESS;
5521:
5522: FND_MSG_PUB.Count_And_Get (
5523: p_count => p_msg_count,
5524: p_data => p_msg_data);
5537: ' '||to_char(p_base_region_appl_id)||' '||p_base_region_code);
5538: FND_MSG_PUB.Add;
5539: end if;
5540: rollback to start_update_lov_relation;
5541: p_return_status := FND_API.G_RET_STS_ERROR;
5542: FND_MSG_PUB.Count_And_Get (
5543: p_count => p_msg_count,
5544: p_data => p_msg_data);
5545: WHEN FND_API.G_EXC_ERROR THEN
5541: p_return_status := FND_API.G_RET_STS_ERROR;
5542: FND_MSG_PUB.Count_And_Get (
5543: p_count => p_msg_count,
5544: p_data => p_msg_data);
5545: WHEN FND_API.G_EXC_ERROR THEN
5546: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5547: FND_MESSAGE.SET_NAME('AK','AK_LOV_RELATION_NOT_UPDATED');
5548: FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
5549: p_region_code||' '||to_char(p_attribute_application_id)||
5553: p_lov_region_code||' '||to_char(p_base_attribute_appl_id)||
5554: ' '||p_base_attribute_code);
5555: FND_MSG_PUB.Add;
5556: end if;
5557: p_return_status := FND_API.G_RET_STS_ERROR;
5558: rollback to start_update_lov_relation;
5559: FND_MSG_PUB.Count_And_Get (
5560: p_count => p_msg_count,
5561: p_data => p_msg_data);
5559: FND_MSG_PUB.Count_And_Get (
5560: p_count => p_msg_count,
5561: p_data => p_msg_data);
5562: WHEN OTHERS THEN
5563: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5564: rollback to start_update_lov_relation;
5565: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5566: SUBSTR (SQLERRM, 1, 240) );
5567: FND_MSG_PUB.Add;
5617: l_api_version_number CONSTANT number := 1.0;
5618: l_api_name CONSTANT varchar2(30) := 'Category_Usage_Exists';
5619: l_dummy number;
5620: begin
5621: IF NOT FND_API.Compatible_API_Call (
5622: l_api_version_number, p_api_version_number, l_api_name,
5623: G_PKG_NAME) then
5624: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5625: return FALSE;
5620: begin
5621: IF NOT FND_API.Compatible_API_Call (
5622: l_api_version_number, p_api_version_number, l_api_name,
5623: G_PKG_NAME) then
5624: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5625: return FALSE;
5626: END IF;
5627:
5628: open l_check_category_usage_csr;
5628: open l_check_category_usage_csr;
5629: fetch l_check_category_usage_csr into l_dummy;
5630: if (l_check_category_usage_csr%notfound) then
5631: close l_check_category_usage_csr;
5632: p_return_status := FND_API.G_RET_STS_SUCCESS;
5633: return FALSE;
5634: else
5635: close l_check_category_usage_csr;
5636: p_return_status := FND_API.G_RET_STS_SUCCESS;
5632: p_return_status := FND_API.G_RET_STS_SUCCESS;
5633: return FALSE;
5634: else
5635: close l_check_category_usage_csr;
5636: p_return_status := FND_API.G_RET_STS_SUCCESS;
5637: return TRUE;
5638: end if;
5639:
5640: EXCEPTION
5637: return TRUE;
5638: end if;
5639:
5640: EXCEPTION
5641: WHEN FND_API.G_EXC_ERROR THEN
5642: p_return_status := FND_API.G_RET_STS_ERROR;
5643: return FALSE;
5644: WHEN OTHERS THEN
5645: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5638: end if;
5639:
5640: EXCEPTION
5641: WHEN FND_API.G_EXC_ERROR THEN
5642: p_return_status := FND_API.G_RET_STS_ERROR;
5643: return FALSE;
5644: WHEN OTHERS THEN
5645: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5646: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5641: WHEN FND_API.G_EXC_ERROR THEN
5642: p_return_status := FND_API.G_RET_STS_ERROR;
5643: return FALSE;
5644: WHEN OTHERS THEN
5645: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5646: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5647: SUBSTR (SQLERRM, 1, 240) );
5648: FND_MSG_PUB.Add;
5649: return FALSE;
5679: --
5680: -- Version Initial version number = 1.1
5681: --=======================================================
5682: FUNCTION VALIDATE_CATEGORY_USAGE (
5683: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5684: p_api_version_number IN NUMBER,
5685: p_return_status OUT NOCOPY VARCHAR2,
5686: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5687: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5682: FUNCTION VALIDATE_CATEGORY_USAGE (
5683: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5684: p_api_version_number IN NUMBER,
5685: p_return_status OUT NOCOPY VARCHAR2,
5686: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5687: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5688: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5689: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5690: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5683: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5684: p_api_version_number IN NUMBER,
5685: p_return_status OUT NOCOPY VARCHAR2,
5686: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5687: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5688: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5689: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5690: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5691: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5684: p_api_version_number IN NUMBER,
5685: p_return_status OUT NOCOPY VARCHAR2,
5686: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5687: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5688: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5689: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5690: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5691: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5692: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5685: p_return_status OUT NOCOPY VARCHAR2,
5686: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5687: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5688: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5689: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5690: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5691: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5692: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5693: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5686: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5687: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5688: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5689: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5690: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5691: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5692: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5693: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5694: p_caller IN VARCHAR2,
5687: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5688: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5689: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5690: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5691: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5692: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5693: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5694: p_caller IN VARCHAR2,
5695: p_pass IN NUMBER := 2
5688: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5689: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5690: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5691: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5692: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5693: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5694: p_caller IN VARCHAR2,
5695: p_pass IN NUMBER := 2
5696: ) return boolean is
5689: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5690: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5691: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5692: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5693: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5694: p_caller IN VARCHAR2,
5695: p_pass IN NUMBER := 2
5696: ) return boolean is
5697:
5717: l_error BOOLEAN;
5718: l_return_status VARCHAR2(1);
5719: begin
5720:
5721: IF NOT FND_API.Compatible_API_Call (
5722: l_api_version_number, p_api_version_number, l_api_name,
5723: G_PKG_NAME) then
5724: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5725: return FALSE;
5720:
5721: IF NOT FND_API.Compatible_API_Call (
5722: l_api_version_number, p_api_version_number, l_api_name,
5723: G_PKG_NAME) then
5724: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5725: return FALSE;
5726: END IF;
5727:
5728: l_error := FALSE;
5727:
5728: l_error := FALSE;
5729:
5730: --** if validation level is none, no validation is necessary
5731: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
5732: p_return_status := FND_API.G_RET_STS_SUCCESS;
5733: return TRUE;
5734: end if;
5735:
5728: l_error := FALSE;
5729:
5730: --** if validation level is none, no validation is necessary
5731: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
5732: p_return_status := FND_API.G_RET_STS_SUCCESS;
5733: return TRUE;
5734: end if;
5735:
5736: --** check that key columns are not null and not missing **
5734: end if;
5735:
5736: --** check that key columns are not null and not missing **
5737: if ((p_region_application_id is null) or
5738: (p_region_application_id = FND_API.G_MISS_NUM)) then
5739: l_error := TRUE;
5740: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
5741: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
5742: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
5744: end if;
5745: end if;
5746:
5747: if ((p_region_code is null) or
5748: (p_region_code = FND_API.G_MISS_CHAR)) then
5749: l_error := TRUE;
5750: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
5751: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
5752: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
5754: end if;
5755: end if;
5756:
5757: if ((p_attribute_application_id is null) or
5758: (p_attribute_application_id = FND_API.G_MISS_NUM)) then
5759: l_error := TRUE;
5760: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
5761: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
5762: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
5764: end if;
5765: end if;
5766:
5767: if ((p_attribute_code is null) or
5768: (p_attribute_code = FND_API.G_MISS_CHAR)) then
5769: l_error := TRUE;
5770: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
5771: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
5772: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
5815: end if;
5816: close l_check_category_csr;
5817:
5818: -- return true if no error, false otherwise
5819: p_return_status := FND_API.G_RET_STS_SUCCESS;
5820: return (not l_error);
5821:
5822: EXCEPTION
5823: WHEN FND_API.G_EXC_ERROR THEN
5819: p_return_status := FND_API.G_RET_STS_SUCCESS;
5820: return (not l_error);
5821:
5822: EXCEPTION
5823: WHEN FND_API.G_EXC_ERROR THEN
5824: p_return_status := FND_API.G_RET_STS_ERROR;
5825: return FALSE;
5826: WHEN OTHERS THEN
5827: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5820: return (not l_error);
5821:
5822: EXCEPTION
5823: WHEN FND_API.G_EXC_ERROR THEN
5824: p_return_status := FND_API.G_RET_STS_ERROR;
5825: return FALSE;
5826: WHEN OTHERS THEN
5827: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5828: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5823: WHEN FND_API.G_EXC_ERROR THEN
5824: p_return_status := FND_API.G_RET_STS_ERROR;
5825: return FALSE;
5826: WHEN OTHERS THEN
5827: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5828: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5829: SUBSTR (SQLERRM, 1, 240) );
5830: FND_MSG_PUB.Add;
5831: return FALSE;
5856: --
5857: -- Version Initial version number = 1.1
5858: --=======================================================
5859: PROCEDURE CREATE_CATEGORY_USAGE (
5860: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5861: p_api_version_number IN NUMBER,
5862: p_init_msg_tbl IN BOOLEAN := FALSE,
5863: p_msg_count OUT NOCOPY NUMBER,
5864: p_msg_data OUT NOCOPY VARCHAR2,
5862: p_init_msg_tbl IN BOOLEAN := FALSE,
5863: p_msg_count OUT NOCOPY NUMBER,
5864: p_msg_data OUT NOCOPY VARCHAR2,
5865: p_return_status OUT NOCOPY VARCHAR2,
5866: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5867: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5868: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5869: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5870: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5863: p_msg_count OUT NOCOPY NUMBER,
5864: p_msg_data OUT NOCOPY VARCHAR2,
5865: p_return_status OUT NOCOPY VARCHAR2,
5866: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5867: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5868: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5869: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5870: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5871: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5864: p_msg_data OUT NOCOPY VARCHAR2,
5865: p_return_status OUT NOCOPY VARCHAR2,
5866: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5867: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5868: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5869: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5870: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5871: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5872: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5865: p_return_status OUT NOCOPY VARCHAR2,
5866: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5867: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5868: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5869: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5870: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5871: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5872: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5873: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5866: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5867: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5868: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5869: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5870: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5871: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5872: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5873: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5874: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5867: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5868: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5869: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5870: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5871: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5872: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5873: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5874: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5875: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5868: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5869: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5870: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5871: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5872: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5873: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5874: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5875: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5876: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5869: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5870: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5871: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5872: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5873: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5874: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5875: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5876: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5877: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5870: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5871: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5872: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5873: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5874: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5875: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5876: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5877: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5878: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5871: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5872: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5873: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5874: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5875: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5876: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5877: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5878: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5879: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5872: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5873: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5874: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5875: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5876: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5877: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5878: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5879: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5880: p_pass IN NUMBER,
5873: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5874: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5875: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5876: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5877: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5878: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5879: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5880: p_pass IN NUMBER,
5881: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5874: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5875: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5876: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5877: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5878: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5879: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5880: p_pass IN NUMBER,
5881: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5882: ) is
5875: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5876: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5877: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5878: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5879: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5880: p_pass IN NUMBER,
5881: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5882: ) is
5883:
5891: l_return_status varchar2(1);
5892:
5893: begin
5894:
5895: IF NOT FND_API.Compatible_API_Call (
5896: l_api_version_number, p_api_version_number, l_api_name,
5897: G_PKG_NAME) then
5898: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5899: return;
5894:
5895: IF NOT FND_API.Compatible_API_Call (
5896: l_api_version_number, p_api_version_number, l_api_name,
5897: G_PKG_NAME) then
5898: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5899: return;
5900: END IF;
5901:
5902: -- Initialize the message table if requested.
5919: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
5920: FND_MESSAGE.SET_NAME('AK','AK_CATEGORY_USAGE_EXISTS');
5921: FND_MSG_PUB.Add;
5922: end if;
5923: raise FND_API.G_EXC_ERROR;
5924: end if;
5925:
5926: --** validate table columns passed in **
5927: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5923: raise FND_API.G_EXC_ERROR;
5924: end if;
5925:
5926: --** validate table columns passed in **
5927: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5928: if not AK_REGION2_PVT.VALIDATE_CATEGORY_USAGE (
5929: p_validation_level => p_validation_level,
5930: p_api_version_number => 1.0,
5931: p_return_status => l_return_status,
5943: -- Do not raise an error if it's the first pass
5944: if (p_pass = 1) then
5945: p_copy_redo_flag := TRUE;
5946: else
5947: raise FND_API.G_EXC_ERROR;
5948: end if;
5949: end if;
5950: end if;
5951:
5951:
5952: -- Do not insert if fails validation ie. p_copy_redo_flag = true
5953: if (not p_copy_redo_flag) then
5954:
5955: if (p_created_by <> FND_API.G_MISS_NUM) then
5956: l_created_by := p_created_by;
5957: end if;
5958:
5959: if (p_creation_date <> FND_API.G_MISS_DATE) then
5955: if (p_created_by <> FND_API.G_MISS_NUM) then
5956: l_created_by := p_created_by;
5957: end if;
5958:
5959: if (p_creation_date <> FND_API.G_MISS_DATE) then
5960: l_creation_date := p_creation_date;
5961: end if;
5962:
5963: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5959: if (p_creation_date <> FND_API.G_MISS_DATE) then
5960: l_creation_date := p_creation_date;
5961: end if;
5962:
5963: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5964: l_last_updated_by := p_last_updated_by;
5965: end if;
5966:
5967: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5963: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5964: l_last_updated_by := p_last_updated_by;
5965: end if;
5966:
5967: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5968: l_last_update_date := p_last_update_date;
5969: end if;
5970:
5971: if (p_last_update_login <> FND_API.G_MISS_NUM) then
5967: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5968: l_last_update_date := p_last_update_date;
5969: end if;
5970:
5971: if (p_last_update_login <> FND_API.G_MISS_NUM) then
5972: l_last_update_login := p_last_update_login;
5973: end if;
5974:
5975: -- Create record if no validation error was found
6034: end if;
6035:
6036: end if; -- /* if not p_copy_redo_flag */
6037:
6038: p_return_status := FND_API.G_RET_STS_SUCCESS;
6039:
6040: FND_MSG_PUB.Count_And_Get (
6041: p_count => p_msg_count,
6042: p_data => p_msg_data);
6050: ' '||p_attribute_code||' '||
6051: p_category_name);
6052: FND_MSG_PUB.Add;
6053: end if;
6054: p_return_status := FND_API.G_RET_STS_ERROR;
6055: rollback to start_create_category_usage;
6056: FND_MSG_PUB.Count_And_Get (
6057: p_count => p_msg_count,
6058: p_data => p_msg_data);
6055: rollback to start_create_category_usage;
6056: FND_MSG_PUB.Count_And_Get (
6057: p_count => p_msg_count,
6058: p_data => p_msg_data);
6059: WHEN FND_API.G_EXC_ERROR THEN
6060: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6061: FND_MESSAGE.SET_NAME('AK','AK_CATEGORY_USAGE_NOT_CREATED');
6062: FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
6063: p_region_code||' '||to_char(p_attribute_application_id)||
6064: ' '||p_attribute_code||' '||
6065: p_category_name);
6066: FND_MSG_PUB.Add;
6067: end if;
6068: p_return_status := FND_API.G_RET_STS_ERROR;
6069: rollback to start_create_category_usage;
6070: FND_MSG_PUB.Count_And_Get (
6071: p_count => p_msg_count,
6072: p_data => p_msg_data);
6070: FND_MSG_PUB.Count_And_Get (
6071: p_count => p_msg_count,
6072: p_data => p_msg_data);
6073: WHEN OTHERS THEN
6074: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6075: rollback to start_create_category_usage;
6076: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
6077: SUBSTR (SQLERRM, 1, 240) );
6078: FND_MSG_PUB.Add;
6105: -- Version Initial version number = 1.0
6106: -- History Current version number = 1.0
6107: --=======================================================
6108: PROCEDURE UPDATE_CATEGORY_USAGE (
6109: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6110: p_api_version_number IN NUMBER,
6111: p_init_msg_tbl IN BOOLEAN := FALSE,
6112: p_msg_count OUT NOCOPY NUMBER,
6113: p_msg_data OUT NOCOPY VARCHAR2,
6111: p_init_msg_tbl IN BOOLEAN := FALSE,
6112: p_msg_count OUT NOCOPY NUMBER,
6113: p_msg_data OUT NOCOPY VARCHAR2,
6114: p_return_status OUT NOCOPY VARCHAR2,
6115: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
6116: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6117: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6118: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6119: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6112: p_msg_count OUT NOCOPY NUMBER,
6113: p_msg_data OUT NOCOPY VARCHAR2,
6114: p_return_status OUT NOCOPY VARCHAR2,
6115: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
6116: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6117: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6118: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6119: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6120: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6113: p_msg_data OUT NOCOPY VARCHAR2,
6114: p_return_status OUT NOCOPY VARCHAR2,
6115: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
6116: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6117: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6118: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6119: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6120: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6121: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6114: p_return_status OUT NOCOPY VARCHAR2,
6115: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
6116: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6117: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6118: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6119: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6120: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6121: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6122: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6115: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
6116: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6117: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6118: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6119: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6120: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6121: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6122: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6123: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6116: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6117: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6118: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6119: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6120: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6121: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6122: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6123: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6124: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6117: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6118: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6119: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6120: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6121: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6122: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6123: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6124: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6125: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6118: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6119: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6120: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6121: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6122: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6123: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6124: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6125: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6126: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6119: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6120: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6121: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6122: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6123: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6124: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6125: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6126: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6127: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6120: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6121: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6122: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6123: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6124: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6125: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6126: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6127: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6128: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6121: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6122: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6123: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6124: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6125: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6126: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6127: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6128: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6129: p_pass IN NUMBER,
6122: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6123: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6124: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6125: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6126: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6127: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6128: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6129: p_pass IN NUMBER,
6130: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
6123: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6124: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6125: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6126: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6127: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6128: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6129: p_pass IN NUMBER,
6130: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
6131: ) is
6124: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6125: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6126: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6127: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6128: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6129: p_pass IN NUMBER,
6130: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
6131: ) is
6132: cursor l_get_row_csr is
6146: l_last_update_login number;
6147: l_last_updated_by number;
6148: l_return_status varchar2(1);
6149: begin
6150: IF NOT FND_API.Compatible_API_Call (
6151: l_api_version_number, p_api_version_number, l_api_name,
6152: G_PKG_NAME) then
6153: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6154: return;
6149: begin
6150: IF NOT FND_API.Compatible_API_Call (
6151: l_api_version_number, p_api_version_number, l_api_name,
6152: G_PKG_NAME) then
6153: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6154: return;
6155: END IF;
6156:
6157: -- Initialize the message table if requested.
6176: FND_MSG_PUB.Add;
6177: end if;
6178: --dbms_output.put_line(l_api_name || 'Error - Row does not exist');
6179: close l_get_row_csr;
6180: raise FND_API.G_EXC_ERROR;
6181: end if;
6182: close l_get_row_csr;
6183:
6184: --** validate table columns passed in **
6181: end if;
6182: close l_get_row_csr;
6183:
6184: --** validate table columns passed in **
6185: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
6186: if not AK_REGION2_PVT.VALIDATE_CATEGORY_USAGE (
6187: p_validation_level => p_validation_level,
6188: p_api_version_number => 1.0,
6189: p_return_status => l_return_status,
6201: -- Do not raise an error if it's the first pass
6202: if (p_pass = 1) then
6203: p_copy_redo_flag := TRUE;
6204: else
6205: raise FND_API.G_EXC_ERROR;
6206: end if;
6207: end if;
6208: end if;
6209:
6209:
6210: -- Do not update if fails validation ie. p_copy_redo_flag = true
6211: if (not p_copy_redo_flag) then
6212:
6213: if (p_created_by <> FND_API.G_MISS_NUM) then
6214: l_created_by := p_created_by;
6215: end if;
6216: if (p_creation_date <> FND_API.G_MISS_DATE) then
6217: l_creation_date := p_creation_date;
6212:
6213: if (p_created_by <> FND_API.G_MISS_NUM) then
6214: l_created_by := p_created_by;
6215: end if;
6216: if (p_creation_date <> FND_API.G_MISS_DATE) then
6217: l_creation_date := p_creation_date;
6218: end if;
6219: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
6220: l_last_updated_by := p_last_updated_by;
6215: end if;
6216: if (p_creation_date <> FND_API.G_MISS_DATE) then
6217: l_creation_date := p_creation_date;
6218: end if;
6219: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
6220: l_last_updated_by := p_last_updated_by;
6221: end if;
6222: if (p_last_update_date <> FND_API.G_MISS_DATE) then
6223: l_last_update_date := p_last_update_date;
6218: end if;
6219: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
6220: l_last_updated_by := p_last_updated_by;
6221: end if;
6222: if (p_last_update_date <> FND_API.G_MISS_DATE) then
6223: l_last_update_date := p_last_update_date;
6224: end if;
6225: if (p_last_update_login <> FND_API.G_MISS_NUM) then
6226: l_last_update_login := p_last_update_login;
6221: end if;
6222: if (p_last_update_date <> FND_API.G_MISS_DATE) then
6223: l_last_update_date := p_last_update_date;
6224: end if;
6225: if (p_last_update_login <> FND_API.G_MISS_NUM) then
6226: l_last_update_login := p_last_update_login;
6227: end if;
6228:
6229: --** Load record to be updated to the database **
6254: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6255: FND_MESSAGE.SET_NAME('AK','AK_CATEGORY_UPDATE_FAILED');
6256: FND_MSG_PUB.Add;
6257: end if;
6258: raise FND_API.G_EXC_ERROR;
6259: end if;
6260:
6261: -- /** commit the update **/
6262: -- commit;
6271: end if;
6272:
6273: end if; -- /* if not p_copy_redo_flag */
6274:
6275: p_return_status := FND_API.G_RET_STS_SUCCESS;
6276:
6277: FND_MSG_PUB.Count_And_Get (
6278: p_count => p_msg_count,
6279: p_data => p_msg_data);
6287: p_attribute_code||' '|| p_category_name ||' '||
6288: to_char(p_application_id));
6289: FND_MSG_PUB.Add;
6290: end if;
6291: p_return_status := FND_API.G_RET_STS_ERROR;
6292: rollback to start_update_category_usage;
6293: FND_MSG_PUB.Count_And_Get (
6294: p_count => p_msg_count,
6295: p_data => p_msg_data);
6293: FND_MSG_PUB.Count_And_Get (
6294: p_count => p_msg_count,
6295: p_data => p_msg_data);
6296: WHEN OTHERS THEN
6297: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6298: rollback to start_update_category_usage;
6299: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
6300: SUBSTR (SQLERRM, 1, 240) );
6301: FND_MSG_PUB.Add;