[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: end if; -- /* if G_REGION_REDO_INDEX > 0 */
3522: );
3523: end if; -- /* if ITEM_EXISTS */
3524: --
3525: -- If API call returns with an error status, upload aborts
3526: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3527: (l_return_status = FND_API.G_RET_STS_ERROR) then
3528: RAISE FND_API.G_EXC_ERROR;
3529: end if; -- /* if l_return_status */
3530: end if; -- /* if G_ITEM_REDO_TBL.exists */
3523: end if; -- /* if ITEM_EXISTS */
3524: --
3525: -- If API call returns with an error status, upload aborts
3526: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3527: (l_return_status = FND_API.G_RET_STS_ERROR) then
3528: RAISE FND_API.G_EXC_ERROR;
3529: end if; -- /* if l_return_status */
3530: end if; -- /* if G_ITEM_REDO_TBL.exists */
3531: end loop;
3524: --
3525: -- If API call returns with an error status, upload aborts
3526: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3527: (l_return_status = FND_API.G_RET_STS_ERROR) then
3528: RAISE FND_API.G_EXC_ERROR;
3529: end if; -- /* if l_return_status */
3530: end if; -- /* if G_ITEM_REDO_TBL.exists */
3531: end loop;
3532: end if;
3623: );
3624: end if; -- /* if LOV_RELATION_EXISTS */
3625: --
3626: -- If API call returns with an error status, upload aborts
3627: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3628: (l_return_status = FND_API.G_RET_STS_ERROR) then
3629: RAISE FND_API.G_EXC_ERROR;
3630: end if; -- /* if l_return_status */
3631: end if; -- /* if G_LOV_RELATION_REDO_TBL.exists(l_index) */
3624: end if; -- /* if LOV_RELATION_EXISTS */
3625: --
3626: -- If API call returns with an error status, upload aborts
3627: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3628: (l_return_status = FND_API.G_RET_STS_ERROR) then
3629: RAISE FND_API.G_EXC_ERROR;
3630: end if; -- /* if l_return_status */
3631: end if; -- /* if G_LOV_RELATION_REDO_TBL.exists(l_index) */
3632: end loop;
3625: --
3626: -- If API call returns with an error status, upload aborts
3627: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
3628: (l_return_status = FND_API.G_RET_STS_ERROR) then
3629: RAISE FND_API.G_EXC_ERROR;
3630: end if; -- /* if l_return_status */
3631: end if; -- /* if G_LOV_RELATION_REDO_TBL.exists(l_index) */
3632: end loop;
3633: end if; -- /* if G_LOV_RELATION_REDO_INDEX > 0 */
3631: end if; -- /* if G_LOV_RELATION_REDO_TBL.exists(l_index) */
3632: end loop;
3633: end if; -- /* if G_LOV_RELATION_REDO_INDEX > 0 */
3634:
3635: p_return_status := FND_API.G_RET_STS_SUCCESS;
3636:
3637: EXCEPTION
3638: WHEN FND_API.G_EXC_ERROR THEN
3639: p_return_status := FND_API.G_RET_STS_ERROR;
3634:
3635: p_return_status := FND_API.G_RET_STS_SUCCESS;
3636:
3637: EXCEPTION
3638: WHEN FND_API.G_EXC_ERROR THEN
3639: p_return_status := FND_API.G_RET_STS_ERROR;
3640: FND_MSG_PUB.Count_And_Get (
3641: p_count => l_msg_count,
3642: p_data => l_msg_data);
3635: p_return_status := FND_API.G_RET_STS_SUCCESS;
3636:
3637: EXCEPTION
3638: WHEN FND_API.G_EXC_ERROR THEN
3639: p_return_status := FND_API.G_RET_STS_ERROR;
3640: FND_MSG_PUB.Count_And_Get (
3641: p_count => l_msg_count,
3642: p_data => l_msg_data);
3643: WHEN OTHERS THEN
3640: FND_MSG_PUB.Count_And_Get (
3641: p_count => l_msg_count,
3642: p_data => l_msg_data);
3643: WHEN OTHERS THEN
3644: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3645: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3646: SUBSTR (SQLERRM, 1, 240) );
3647: FND_MSG_PUB.Count_And_Get (
3648: p_count => l_msg_count,
3712: l_orig_ri_rec ak_region_items%ROWTYPE;
3713: l_ri_tl_rec ak_region_items_tl%ROWTYPE;
3714:
3715: begin
3716: l_return_status := FND_API.G_RET_STS_SUCCESS;
3717: open l_ri_csr( p_region_code,
3718: p_region_application_id,
3719: p_display_sequence);
3720: fetch l_ri_csr into l_ri_rec;
3862: p_return_status := l_return_status;
3863: close l_ri_csr;
3864:
3865: EXCEPTION
3866: WHEN FND_API.G_EXC_ERROR THEN
3867: p_return_status := FND_API.G_RET_STS_ERROR;
3868: WHEN OTHERS THEN
3869: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3870: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3863: close l_ri_csr;
3864:
3865: EXCEPTION
3866: WHEN FND_API.G_EXC_ERROR THEN
3867: p_return_status := FND_API.G_RET_STS_ERROR;
3868: WHEN OTHERS THEN
3869: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3870: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3871: SUBSTR (SQLERRM, 1, 240) );
3865: EXCEPTION
3866: WHEN FND_API.G_EXC_ERROR THEN
3867: p_return_status := FND_API.G_RET_STS_ERROR;
3868: WHEN OTHERS THEN
3869: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3870: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3871: SUBSTR (SQLERRM, 1, 240) );
3872: FND_MSG_PUB.Add;
3873:
3918: l_api_version_number CONSTANT number := 1.0;
3919: l_api_name CONSTANT varchar2(30) := 'Graph_Column_Exists';
3920: l_dummy number;
3921: begin
3922: IF NOT FND_API.Compatible_API_Call (
3923: l_api_version_number, p_api_version_number, l_api_name,
3924: G_PKG_NAME) then
3925: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3926: return FALSE;
3921: begin
3922: IF NOT FND_API.Compatible_API_Call (
3923: l_api_version_number, p_api_version_number, l_api_name,
3924: G_PKG_NAME) then
3925: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3926: return FALSE;
3927: END IF;
3928:
3929: open l_check_graph_column_csr;
3929: open l_check_graph_column_csr;
3930: fetch l_check_graph_column_csr into l_dummy;
3931: if (l_check_graph_column_csr%notfound) then
3932: close l_check_graph_column_csr;
3933: p_return_status := FND_API.G_RET_STS_SUCCESS;
3934: return FALSE;
3935: else
3936: close l_check_graph_column_csr;
3937: p_return_status := FND_API.G_RET_STS_SUCCESS;
3933: p_return_status := FND_API.G_RET_STS_SUCCESS;
3934: return FALSE;
3935: else
3936: close l_check_graph_column_csr;
3937: p_return_status := FND_API.G_RET_STS_SUCCESS;
3938: return TRUE;
3939: end if;
3940:
3941: EXCEPTION
3938: return TRUE;
3939: end if;
3940:
3941: EXCEPTION
3942: WHEN FND_API.G_EXC_ERROR THEN
3943: p_return_status := FND_API.G_RET_STS_ERROR;
3944: return FALSE;
3945: WHEN OTHERS THEN
3946: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3939: end if;
3940:
3941: EXCEPTION
3942: WHEN FND_API.G_EXC_ERROR THEN
3943: p_return_status := FND_API.G_RET_STS_ERROR;
3944: return FALSE;
3945: WHEN OTHERS THEN
3946: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3947: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3942: WHEN FND_API.G_EXC_ERROR THEN
3943: p_return_status := FND_API.G_RET_STS_ERROR;
3944: return FALSE;
3945: WHEN OTHERS THEN
3946: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3947: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3948: SUBSTR (SQLERRM, 1, 240) );
3949: FND_MSG_PUB.Add;
3950: return FALSE;
4011: l_api_version_number CONSTANT number := 1.0;
4012: l_api_name CONSTANT varchar2(30) := 'Lov_Relation_Exists';
4013: l_dummy number;
4014: begin
4015: IF NOT FND_API.Compatible_API_Call (
4016: l_api_version_number, p_api_version_number, l_api_name,
4017: G_PKG_NAME) then
4018: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4019: return FALSE;
4014: begin
4015: IF NOT FND_API.Compatible_API_Call (
4016: l_api_version_number, p_api_version_number, l_api_name,
4017: G_PKG_NAME) then
4018: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4019: return FALSE;
4020: END IF;
4021:
4022: open l_check_lov_relation_csr;
4022: open l_check_lov_relation_csr;
4023: fetch l_check_lov_relation_csr into l_dummy;
4024: if (l_check_lov_relation_csr%notfound) then
4025: close l_check_lov_relation_csr;
4026: p_return_status := FND_API.G_RET_STS_SUCCESS;
4027: return FALSE;
4028: else
4029: close l_check_lov_relation_csr;
4030: p_return_status := FND_API.G_RET_STS_SUCCESS;
4026: p_return_status := FND_API.G_RET_STS_SUCCESS;
4027: return FALSE;
4028: else
4029: close l_check_lov_relation_csr;
4030: p_return_status := FND_API.G_RET_STS_SUCCESS;
4031: return TRUE;
4032: end if;
4033:
4034: EXCEPTION
4031: return TRUE;
4032: end if;
4033:
4034: EXCEPTION
4035: WHEN FND_API.G_EXC_ERROR THEN
4036: p_return_status := FND_API.G_RET_STS_ERROR;
4037: return FALSE;
4038: WHEN OTHERS THEN
4039: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4032: end if;
4033:
4034: EXCEPTION
4035: WHEN FND_API.G_EXC_ERROR THEN
4036: p_return_status := FND_API.G_RET_STS_ERROR;
4037: return FALSE;
4038: WHEN OTHERS THEN
4039: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4040: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4035: WHEN FND_API.G_EXC_ERROR THEN
4036: p_return_status := FND_API.G_RET_STS_ERROR;
4037: return FALSE;
4038: WHEN OTHERS THEN
4039: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4040: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4041: SUBSTR (SQLERRM, 1, 240) );
4042: FND_MSG_PUB.Add;
4043: return FALSE;
4074: --
4075: -- Version Initial version number = 1.1
4076: --=======================================================
4077: FUNCTION VALIDATE_GRAPH_COLUMN (
4078: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4079: p_api_version_number IN NUMBER,
4080: p_return_status OUT NOCOPY VARCHAR2,
4081: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4082: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4077: FUNCTION VALIDATE_GRAPH_COLUMN (
4078: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4079: p_api_version_number IN NUMBER,
4080: p_return_status OUT NOCOPY VARCHAR2,
4081: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4082: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4083: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4084: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4085: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4078: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4079: p_api_version_number IN NUMBER,
4080: p_return_status OUT NOCOPY VARCHAR2,
4081: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4082: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4083: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4084: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4085: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4086: p_pass IN NUMBER := 2,
4079: p_api_version_number IN NUMBER,
4080: p_return_status OUT NOCOPY VARCHAR2,
4081: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4082: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4083: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4084: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4085: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4086: p_pass IN NUMBER := 2,
4087: p_caller IN VARCHAR2
4080: p_return_status OUT NOCOPY VARCHAR2,
4081: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4082: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4083: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4084: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4085: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4086: p_pass IN NUMBER := 2,
4087: p_caller IN VARCHAR2
4088: ) return boolean is
4081: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4082: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4083: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4084: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4085: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4086: p_pass IN NUMBER := 2,
4087: p_caller IN VARCHAR2
4088: ) return boolean is
4089: cursor l_check_region_graph_csr (param_region_code in varchar2,
4111: l_error BOOLEAN;
4112: l_return_status VARCHAR2(1);
4113: begin
4114:
4115: IF NOT FND_API.Compatible_API_Call (
4116: l_api_version_number, p_api_version_number, l_api_name,
4117: G_PKG_NAME) then
4118: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4119: return FALSE;
4114:
4115: IF NOT FND_API.Compatible_API_Call (
4116: l_api_version_number, p_api_version_number, l_api_name,
4117: G_PKG_NAME) then
4118: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4119: return FALSE;
4120: END IF;
4121:
4122: l_error := FALSE;
4121:
4122: l_error := FALSE;
4123:
4124: --** if validation level is none, no validation is necessary
4125: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4126: p_return_status := FND_API.G_RET_STS_SUCCESS;
4127: return TRUE;
4128: end if;
4129:
4122: l_error := FALSE;
4123:
4124: --** if validation level is none, no validation is necessary
4125: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4126: p_return_status := FND_API.G_RET_STS_SUCCESS;
4127: return TRUE;
4128: end if;
4129:
4130: --** check that key columns are not null and not missing **
4128: end if;
4129:
4130: --** check that key columns are not null and not missing **
4131: if ((p_region_application_id is null) or
4132: (p_region_application_id = FND_API.G_MISS_NUM)) then
4133: l_error := TRUE;
4134: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4135: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4136: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
4138: end if;
4139: end if;
4140:
4141: if ((p_region_code is null) or
4142: (p_region_code = FND_API.G_MISS_CHAR)) then
4143: l_error := TRUE;
4144: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4145: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4146: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
4148: end if;
4149: end if;
4150:
4151: if ((p_attribute_application_id is null) or
4152: (p_attribute_application_id = FND_API.G_MISS_NUM)) then
4153: l_error := TRUE;
4154: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4155: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4156: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
4158: end if;
4159: end if;
4160:
4161: if ((p_attribute_code is null) or
4162: (p_attribute_code = FND_API.G_MISS_CHAR)) then
4163: l_error := TRUE;
4164: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4165: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4166: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
4168: end if;
4169: end if;
4170:
4171: if ((p_graph_number is null) or
4172: (p_graph_number = FND_API.G_MISS_NUM)) then
4173: l_error := TRUE;
4174: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4175: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4176: FND_MESSAGE.SET_TOKEN('COLUMN', 'GRAPH_NUMBER');
4209: end if;
4210: close l_check_region_graph_csr;
4211:
4212: -- return true if no error, false otherwise
4213: p_return_status := FND_API.G_RET_STS_SUCCESS;
4214: return (not l_error);
4215:
4216: EXCEPTION
4217: WHEN FND_API.G_EXC_ERROR THEN
4213: p_return_status := FND_API.G_RET_STS_SUCCESS;
4214: return (not l_error);
4215:
4216: EXCEPTION
4217: WHEN FND_API.G_EXC_ERROR THEN
4218: p_return_status := FND_API.G_RET_STS_ERROR;
4219: return FALSE;
4220: WHEN OTHERS THEN
4221: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4214: return (not l_error);
4215:
4216: EXCEPTION
4217: WHEN FND_API.G_EXC_ERROR THEN
4218: p_return_status := FND_API.G_RET_STS_ERROR;
4219: return FALSE;
4220: WHEN OTHERS THEN
4221: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4222: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4217: WHEN FND_API.G_EXC_ERROR THEN
4218: p_return_status := FND_API.G_RET_STS_ERROR;
4219: return FALSE;
4220: WHEN OTHERS THEN
4221: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4222: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4223: SUBSTR (SQLERRM, 1, 240) );
4224: FND_MSG_PUB.Add;
4225: return FALSE;
4257: --
4258: -- Version Initial version number = 1.1
4259: --=======================================================
4260: FUNCTION VALIDATE_LOV_RELATION (
4261: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4262: p_api_version_number IN NUMBER,
4263: p_return_status OUT NOCOPY VARCHAR2,
4264: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4265: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4260: FUNCTION VALIDATE_LOV_RELATION (
4261: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4262: p_api_version_number IN NUMBER,
4263: p_return_status OUT NOCOPY VARCHAR2,
4264: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4265: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4266: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4267: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4268: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4261: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4262: p_api_version_number IN NUMBER,
4263: p_return_status OUT NOCOPY VARCHAR2,
4264: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4265: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4266: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4267: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4268: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4269: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4262: p_api_version_number IN NUMBER,
4263: p_return_status OUT NOCOPY VARCHAR2,
4264: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4265: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4266: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4267: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4268: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4269: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4270: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4263: p_return_status OUT NOCOPY VARCHAR2,
4264: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4265: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4266: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4267: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4268: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4269: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4270: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4271: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4264: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4265: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4266: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4267: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4268: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4269: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4270: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4271: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4272: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4265: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4266: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4267: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4268: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4269: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4270: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4271: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4272: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4273: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4266: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4267: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4268: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4269: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4270: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4271: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4272: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4273: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4274: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4267: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4268: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4269: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4270: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4271: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4272: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4273: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4274: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4275: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4268: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4269: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4270: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4271: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4272: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4273: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4274: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4275: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4276: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4269: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4270: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4271: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4272: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4273: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4274: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4275: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4276: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4277: p_caller IN VARCHAR2,
4270: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4271: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4272: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4273: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4274: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4275: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4276: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4277: p_caller IN VARCHAR2,
4278: p_pass IN NUMBER := 2
4271: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4272: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4273: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4274: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4275: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4276: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4277: p_caller IN VARCHAR2,
4278: p_pass IN NUMBER := 2
4279: ) return boolean is
4272: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4273: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4274: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4275: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4276: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4277: p_caller IN VARCHAR2,
4278: p_pass IN NUMBER := 2
4279: ) return boolean is
4280: cursor l_check_region_csr (param_region_code in varchar2, param_region_appl_id in number) is
4298: l_error BOOLEAN;
4299: l_return_status VARCHAR2(1);
4300: begin
4301:
4302: IF NOT FND_API.Compatible_API_Call (
4303: l_api_version_number, p_api_version_number, l_api_name,
4304: G_PKG_NAME) then
4305: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4306: return FALSE;
4301:
4302: IF NOT FND_API.Compatible_API_Call (
4303: l_api_version_number, p_api_version_number, l_api_name,
4304: G_PKG_NAME) then
4305: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4306: return FALSE;
4307: END IF;
4308:
4309: l_error := FALSE;
4308:
4309: l_error := FALSE;
4310:
4311: --** if validation level is none, no validation is necessary
4312: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4313: p_return_status := FND_API.G_RET_STS_SUCCESS;
4314: return TRUE;
4315: end if;
4316:
4309: l_error := FALSE;
4310:
4311: --** if validation level is none, no validation is necessary
4312: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
4313: p_return_status := FND_API.G_RET_STS_SUCCESS;
4314: return TRUE;
4315: end if;
4316:
4317: --** check that key columns are not null and not missing **
4315: end if;
4316:
4317: --** check that key columns are not null and not missing **
4318: if ((p_region_application_id is null) or
4319: (p_region_application_id = FND_API.G_MISS_NUM)) then
4320: l_error := TRUE;
4321: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4322: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4323: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
4325: end if;
4326: end if;
4327:
4328: if ((p_region_code is null) or
4329: (p_region_code = FND_API.G_MISS_CHAR)) then
4330: l_error := TRUE;
4331: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4332: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4333: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
4335: end if;
4336: end if;
4337:
4338: if ((p_attribute_application_id is null) or
4339: (p_attribute_application_id = FND_API.G_MISS_NUM)) then
4340: l_error := TRUE;
4341: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4342: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4343: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
4345: end if;
4346: end if;
4347:
4348: if ((p_attribute_code is null) or
4349: (p_attribute_code = FND_API.G_MISS_CHAR)) then
4350: l_error := TRUE;
4351: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4352: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4353: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
4355: end if;
4356: end if;
4357:
4358: if ((p_lov_region_appl_id is null) or
4359: (p_lov_region_appl_id = FND_API.G_MISS_NUM)) then
4360: l_error := TRUE;
4361: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4362: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4363: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
4365: end if;
4366: end if;
4367:
4368: if ((p_lov_region_code is null) or
4369: (p_lov_region_code = FND_API.G_MISS_CHAR)) then
4370: l_error := TRUE;
4371: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4372: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4373: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
4375: end if;
4376: end if;
4377:
4378: if ((p_lov_attribute_appl_id is null) or
4379: (p_lov_attribute_appl_id = FND_API.G_MISS_NUM)) then
4380: l_error := TRUE;
4381: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4382: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4383: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
4385: end if;
4386: end if;
4387:
4388: if ((p_lov_attribute_code is null) or
4389: (p_lov_attribute_code = FND_API.G_MISS_CHAR)) then
4390: l_error := TRUE;
4391: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4392: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4393: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
4398: -- do not raise an error for now, there are ppl who has an old jlt file and upload
4399: -- it manually to a database which contains this patch
4400: /**
4401: if ((p_base_region_appl_id is null) or
4402: (p_base_region_appl_id = FND_API.G_MISS_NUM)) then
4403:
4404: l_error := TRUE;
4405: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4406: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4413: -- do not raise an error for now, there are ppl who has an old jlt file and upload
4414: -- it manually to a database which contains this patch
4415: /**
4416: if ((p_base_region_code is null) or
4417: (p_base_region_code = FND_API.G_MISS_CHAR)) then
4418: l_error := TRUE;
4419: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4420: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4421: FND_MESSAGE.SET_TOKEN('COLUMN', 'BASE_REGION_CODE');
4426:
4427: -- these two columns are not part of primary key but cannot be null
4428: --
4429: if ((p_base_attribute_appl_id is null) or
4430: (p_base_attribute_appl_id = FND_API.G_MISS_NUM)) then
4431: l_error := TRUE;
4432: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4433: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4434: FND_MESSAGE.SET_TOKEN('COLUMN', 'BASE_ATTRIBUTE_APPL_ID');
4436: end if;
4437: end if;
4438:
4439: if ((p_base_attribute_code is null) or
4440: (p_base_attribute_code = FND_API.G_MISS_CHAR)) then
4441: l_error := TRUE;
4442: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
4443: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
4444: FND_MESSAGE.SET_TOKEN('COLUMN', 'BASE_ATTRIBUTE_CODE');
4508: end if;
4509: close l_check_region_item_csr;
4510:
4511: -- direction_flag
4512: if ( (p_direction_flag <> FND_API.G_MISS_CHAR) and
4513: (p_direction_flag is not null) )then
4514: if (NOT AK_ON_OBJECTS_PVT.VALID_LOOKUP_CODE (
4515: p_api_version_number => 1.0,
4516: p_return_status => l_return_status,
4532: end if;
4533: end if;
4534:
4535: -- return true if no error, false otherwise
4536: p_return_status := FND_API.G_RET_STS_SUCCESS;
4537: return (not l_error);
4538:
4539: EXCEPTION
4540: WHEN FND_API.G_EXC_ERROR THEN
4536: p_return_status := FND_API.G_RET_STS_SUCCESS;
4537: return (not l_error);
4538:
4539: EXCEPTION
4540: WHEN FND_API.G_EXC_ERROR THEN
4541: p_return_status := FND_API.G_RET_STS_ERROR;
4542: return FALSE;
4543: WHEN OTHERS THEN
4544: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4537: return (not l_error);
4538:
4539: EXCEPTION
4540: WHEN FND_API.G_EXC_ERROR THEN
4541: p_return_status := FND_API.G_RET_STS_ERROR;
4542: return FALSE;
4543: WHEN OTHERS THEN
4544: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4545: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4540: WHEN FND_API.G_EXC_ERROR THEN
4541: p_return_status := FND_API.G_RET_STS_ERROR;
4542: return FALSE;
4543: WHEN OTHERS THEN
4544: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4545: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4546: SUBSTR (SQLERRM, 1, 240) );
4547: FND_MSG_PUB.Add;
4548: return FALSE;
4574: --
4575: -- Version Initial version number = 1.1
4576: --=======================================================
4577: PROCEDURE CREATE_GRAPH_COLUMN (
4578: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4579: p_api_version_number IN NUMBER,
4580: p_init_msg_tbl IN BOOLEAN := FALSE,
4581: p_msg_count OUT NOCOPY NUMBER,
4582: p_msg_data OUT NOCOPY VARCHAR2,
4580: p_init_msg_tbl IN BOOLEAN := FALSE,
4581: p_msg_count OUT NOCOPY NUMBER,
4582: p_msg_data OUT NOCOPY VARCHAR2,
4583: p_return_status OUT NOCOPY VARCHAR2,
4584: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4585: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4586: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4587: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4588: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4581: p_msg_count OUT NOCOPY NUMBER,
4582: p_msg_data OUT NOCOPY VARCHAR2,
4583: p_return_status OUT NOCOPY VARCHAR2,
4584: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4585: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4586: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4587: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4588: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4589: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4582: p_msg_data OUT NOCOPY VARCHAR2,
4583: p_return_status OUT NOCOPY VARCHAR2,
4584: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4585: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4586: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4587: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4588: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4589: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4590: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4583: p_return_status OUT NOCOPY VARCHAR2,
4584: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4585: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4586: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4587: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4588: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4589: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4590: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4591: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4584: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4585: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4586: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4587: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4588: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4589: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4590: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4591: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4592: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4585: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4586: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4587: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4588: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4589: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4590: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4591: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4592: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4593: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4586: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4587: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4588: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4589: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4590: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4591: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4592: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4593: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4594: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4587: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4588: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4589: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4590: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4591: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4592: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4593: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4594: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4595: p_pass IN NUMBER,
4588: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
4589: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4590: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4591: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4592: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4593: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4594: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4595: p_pass IN NUMBER,
4596: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4589: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4590: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4591: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4592: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4593: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4594: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4595: p_pass IN NUMBER,
4596: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4597: ) is
4590: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4591: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4592: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4593: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4594: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4595: p_pass IN NUMBER,
4596: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4597: ) is
4598:
4606: l_return_status varchar2(1);
4607:
4608: begin
4609:
4610: IF NOT FND_API.Compatible_API_Call (
4611: l_api_version_number, p_api_version_number, l_api_name,
4612: G_PKG_NAME) then
4613: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4614: return;
4609:
4610: IF NOT FND_API.Compatible_API_Call (
4611: l_api_version_number, p_api_version_number, l_api_name,
4612: G_PKG_NAME) then
4613: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4614: return;
4615: END IF;
4616:
4617: -- Initialize the message table if requested.
4634: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4635: FND_MESSAGE.SET_NAME('AK','AK_GRAPH_COLUMN_EXISTS');
4636: FND_MSG_PUB.Add;
4637: end if;
4638: raise FND_API.G_EXC_ERROR;
4639: end if;
4640:
4641: --** validate table columns passed in **
4642: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4638: raise FND_API.G_EXC_ERROR;
4639: end if;
4640:
4641: --** validate table columns passed in **
4642: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4643: if not AK_REGION2_PVT.VALIDATE_GRAPH_COLUMN (
4644: p_validation_level => p_validation_level,
4645: p_api_version_number => 1.0,
4646: p_return_status => l_return_status,
4655: -- Do not raise an error if it's the first pass
4656: if (p_pass = 1) then
4657: p_copy_redo_flag := TRUE;
4658: else
4659: raise FND_API.G_EXC_ERROR;
4660: end if;
4661: end if;
4662: end if;
4663:
4716: to_char(p_graph_number));
4717: FND_MSG_PUB.Add;
4718: end if;
4719:
4720: p_return_status := FND_API.G_RET_STS_SUCCESS;
4721:
4722: FND_MSG_PUB.Count_And_Get (
4723: p_count => p_msg_count,
4724: p_data => p_msg_data);
4733: ' '||p_attribute_code||' '||
4734: to_char(p_graph_number));
4735: FND_MSG_PUB.Add;
4736: end if;
4737: p_return_status := FND_API.G_RET_STS_ERROR;
4738: rollback to start_create_graph_column;
4739: FND_MSG_PUB.Count_And_Get (
4740: p_count => p_msg_count,
4741: p_data => p_msg_data);
4739: FND_MSG_PUB.Count_And_Get (
4740: p_count => p_msg_count,
4741: p_data => p_msg_data);
4742: WHEN OTHERS THEN
4743: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4744: rollback to start_create_graph_column;
4745: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4746: SUBSTR (SQLERRM, 1, 240) );
4747: FND_MSG_PUB.Add;
4776: --
4777: -- Version Initial version number = 1.1
4778: --=======================================================
4779: PROCEDURE CREATE_LOV_RELATION (
4780: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4781: p_api_version_number IN NUMBER,
4782: p_init_msg_tbl IN BOOLEAN := FALSE,
4783: p_msg_count OUT NOCOPY NUMBER,
4784: p_msg_data OUT NOCOPY VARCHAR2,
4782: p_init_msg_tbl IN BOOLEAN := FALSE,
4783: p_msg_count OUT NOCOPY NUMBER,
4784: p_msg_data OUT NOCOPY VARCHAR2,
4785: p_return_status OUT NOCOPY VARCHAR2,
4786: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4787: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4788: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4789: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4790: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4783: p_msg_count OUT NOCOPY NUMBER,
4784: p_msg_data OUT NOCOPY VARCHAR2,
4785: p_return_status OUT NOCOPY VARCHAR2,
4786: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4787: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4788: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4789: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4790: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4791: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4784: p_msg_data OUT NOCOPY VARCHAR2,
4785: p_return_status OUT NOCOPY VARCHAR2,
4786: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4787: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4788: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4789: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4790: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4791: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4792: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4785: p_return_status OUT NOCOPY VARCHAR2,
4786: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4787: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4788: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4789: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4790: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4791: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4792: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4793: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4786: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4787: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4788: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4789: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4790: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4791: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4792: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4793: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4794: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4787: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4788: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4789: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4790: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4791: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4792: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4793: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4794: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4795: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4788: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
4789: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4790: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4791: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4792: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4793: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4794: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4795: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4796: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4789: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4790: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4791: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4792: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4793: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4794: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4795: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4796: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4797: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4790: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4791: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4792: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4793: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4794: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4795: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4796: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4797: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4798: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4791: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4792: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4793: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4794: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4795: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4796: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4797: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4798: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4799: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4792: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4793: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4794: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4795: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4796: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4797: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4798: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4799: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4800: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4793: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4794: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4795: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4796: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4797: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4798: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4799: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4800: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4801: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4794: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4795: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4796: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4797: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4798: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4799: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4800: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4801: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4802: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4795: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4796: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4797: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4798: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4799: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4800: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4801: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4802: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4803: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4796: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4797: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4798: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4799: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4800: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4801: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4802: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4803: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4804: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4797: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4798: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4799: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4800: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4801: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4802: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4803: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4804: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4805: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4798: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4799: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4800: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4801: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4802: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4803: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4804: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4805: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4806: p_pass IN NUMBER,
4799: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4800: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4801: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4802: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4803: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4804: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4805: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4806: p_pass IN NUMBER,
4807: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4800: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4801: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4802: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4803: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4804: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4805: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4806: p_pass IN NUMBER,
4807: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4808: ) is
4801: p_creation_date IN DATE := FND_API.G_MISS_DATE,
4802: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4803: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4804: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4805: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4806: p_pass IN NUMBER,
4807: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4808: ) is
4809:
4819: l_return_status varchar2(1);
4820:
4821: begin
4822:
4823: IF NOT FND_API.Compatible_API_Call (
4824: l_api_version_number, p_api_version_number, l_api_name,
4825: G_PKG_NAME) then
4826: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4827: return;
4822:
4823: IF NOT FND_API.Compatible_API_Call (
4824: l_api_version_number, p_api_version_number, l_api_name,
4825: G_PKG_NAME) then
4826: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4827: return;
4828: END IF;
4829:
4830: -- Initialize the message table if requested.
4855: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4856: FND_MESSAGE.SET_NAME('AK','AK_LOV_RELATION_EXISTS');
4857: FND_MSG_PUB.Add;
4858: end if;
4859: raise FND_API.G_EXC_ERROR;
4860: end if;
4861:
4862: --** validate table columns passed in **
4863: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4859: raise FND_API.G_EXC_ERROR;
4860: end if;
4861:
4862: --** validate table columns passed in **
4863: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4864: if not AK_REGION2_PVT.VALIDATE_LOV_RELATION (
4865: p_validation_level => p_validation_level,
4866: p_api_version_number => 1.0,
4867: p_return_status => l_return_status,
4884: -- Do not raise an error if it's the first pass
4885: if (p_pass = 1) then
4886: p_copy_redo_flag := TRUE;
4887: else
4888: raise FND_API.G_EXC_ERROR;
4889: end if;
4890: end if;
4891: end if;
4892:
4890: end if;
4891: end if;
4892:
4893: -- default a value for required_flag column if no value is given
4894: if ( p_required_flag <> FND_API.G_MISS_CHAR and p_required_flag is not null ) then
4895: l_required_flag := p_required_flag;
4896: end if;
4897:
4898: if (p_created_by <> FND_API.G_MISS_NUM) then
4894: if ( p_required_flag <> FND_API.G_MISS_CHAR and p_required_flag is not null ) then
4895: l_required_flag := p_required_flag;
4896: end if;
4897:
4898: if (p_created_by <> FND_API.G_MISS_NUM) then
4899: l_created_by := p_created_by;
4900: end if;
4901:
4902: if (p_creation_date <> FND_API.G_MISS_DATE) then
4898: if (p_created_by <> FND_API.G_MISS_NUM) then
4899: l_created_by := p_created_by;
4900: end if;
4901:
4902: if (p_creation_date <> FND_API.G_MISS_DATE) then
4903: l_creation_date := p_creation_date;
4904: end if;
4905:
4906: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
4902: if (p_creation_date <> FND_API.G_MISS_DATE) then
4903: l_creation_date := p_creation_date;
4904: end if;
4905:
4906: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
4907: l_last_updated_by := p_last_updated_by;
4908: end if;
4909:
4910: if (p_last_update_date <> FND_API.G_MISS_DATE) then
4906: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
4907: l_last_updated_by := p_last_updated_by;
4908: end if;
4909:
4910: if (p_last_update_date <> FND_API.G_MISS_DATE) then
4911: l_last_update_date := p_last_update_date;
4912: end if;
4913:
4914: if (p_last_update_login <> FND_API.G_MISS_NUM) then
4910: if (p_last_update_date <> FND_API.G_MISS_DATE) then
4911: l_last_update_date := p_last_update_date;
4912: end if;
4913:
4914: if (p_last_update_login <> FND_API.G_MISS_NUM) then
4915: l_last_update_login := p_last_update_login;
4916: end if;
4917:
4918:
4991: ' '||p_base_attribute_code);
4992: FND_MSG_PUB.Add;
4993: end if;
4994:
4995: p_return_status := FND_API.G_RET_STS_SUCCESS;
4996:
4997: FND_MSG_PUB.Count_And_Get (
4998: p_count => p_msg_count,
4999: p_data => p_msg_data);
5010: p_lov_region_code||' '||to_char(p_base_attribute_appl_id)||
5011: ' '||p_base_attribute_code);
5012: FND_MSG_PUB.Add;
5013: end if;
5014: p_return_status := FND_API.G_RET_STS_ERROR;
5015: rollback to start_create_lov_relation;
5016: FND_MSG_PUB.Count_And_Get (
5017: p_count => p_msg_count,
5018: p_data => p_msg_data);
5015: rollback to start_create_lov_relation;
5016: FND_MSG_PUB.Count_And_Get (
5017: p_count => p_msg_count,
5018: p_data => p_msg_data);
5019: WHEN FND_API.G_EXC_ERROR THEN
5020: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5021: FND_MESSAGE.SET_NAME('AK','AK_REGION_NOT_CREATED');
5022: FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
5023: p_region_code||' '||to_char(p_attribute_application_id)||
5027: p_lov_region_code||' '||to_char(p_base_attribute_appl_id)||
5028: ' '||p_base_attribute_code);
5029: FND_MSG_PUB.Add;
5030: end if;
5031: p_return_status := FND_API.G_RET_STS_ERROR;
5032: rollback to start_create_lov_relation;
5033: FND_MSG_PUB.Count_And_Get (
5034: p_count => p_msg_count,
5035: p_data => p_msg_data);
5033: FND_MSG_PUB.Count_And_Get (
5034: p_count => p_msg_count,
5035: p_data => p_msg_data);
5036: WHEN OTHERS THEN
5037: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5038: rollback to start_create_region;
5039: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5040: SUBSTR (SQLERRM, 1, 240) );
5041: FND_MSG_PUB.Add;
5069: -- Version Initial version number = 1.0
5070: -- History Current version number = 1.0
5071: --=======================================================
5072: PROCEDURE UPDATE_GRAPH_COLUMN (
5073: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5074: p_api_version_number IN NUMBER,
5075: p_init_msg_tbl IN BOOLEAN := FALSE,
5076: p_msg_count OUT NOCOPY NUMBER,
5077: p_msg_data OUT NOCOPY VARCHAR2,
5075: p_init_msg_tbl IN BOOLEAN := FALSE,
5076: p_msg_count OUT NOCOPY NUMBER,
5077: p_msg_data OUT NOCOPY VARCHAR2,
5078: p_return_status OUT NOCOPY VARCHAR2,
5079: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5080: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5081: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5082: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5083: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5076: p_msg_count OUT NOCOPY NUMBER,
5077: p_msg_data OUT NOCOPY VARCHAR2,
5078: p_return_status OUT NOCOPY VARCHAR2,
5079: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5080: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5081: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5082: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5083: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5084: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5077: p_msg_data OUT NOCOPY VARCHAR2,
5078: p_return_status OUT NOCOPY VARCHAR2,
5079: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5080: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5081: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5082: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5083: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5084: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5085: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5078: p_return_status OUT NOCOPY VARCHAR2,
5079: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5080: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5081: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5082: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5083: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5084: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5085: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5086: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5079: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5080: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5081: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5082: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5083: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5084: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5085: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5086: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5087: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5080: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5081: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5082: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5083: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5084: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5085: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5086: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5087: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5088: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5081: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5082: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5083: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5084: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5085: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5086: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5087: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5088: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5089: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5082: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5083: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5084: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5085: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5086: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5087: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5088: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5089: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5090: p_pass IN NUMBER,
5083: p_graph_number IN NUMBER := FND_API.G_MISS_NUM,
5084: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5085: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5086: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5087: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5088: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5089: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5090: p_pass IN NUMBER,
5091: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5084: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5085: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5086: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5087: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5088: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5089: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5090: p_pass IN NUMBER,
5091: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5092: ) is
5085: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5086: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5087: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5088: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5089: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5090: p_pass IN NUMBER,
5091: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5092: ) is
5093: cursor l_get_row_csr is
5108: l_last_update_login number;
5109: l_last_updated_by number;
5110: l_return_status varchar2(1);
5111: begin
5112: IF NOT FND_API.Compatible_API_Call (
5113: l_api_version_number, p_api_version_number, l_api_name,
5114: G_PKG_NAME) then
5115: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5116: return;
5111: begin
5112: IF NOT FND_API.Compatible_API_Call (
5113: l_api_version_number, p_api_version_number, l_api_name,
5114: G_PKG_NAME) then
5115: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5116: return;
5117: END IF;
5118:
5119: -- Initialize the message table if requested.
5133: FND_MSG_PUB.Add;
5134: end if;
5135: --dbms_output.put_line(l_api_name || 'Error - Row does not exist');
5136: close l_get_row_csr;
5137: raise FND_API.G_EXC_ERROR;
5138: end if;
5139: close l_get_row_csr;
5140:
5141: --** validate table columns passed in **
5138: end if;
5139: close l_get_row_csr;
5140:
5141: --** validate table columns passed in **
5142: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5143: if not AK_REGION2_PVT.VALIDATE_GRAPH_COLUMN (
5144: p_validation_level => p_validation_level,
5145: p_api_version_number => 1.0,
5146: p_return_status => l_return_status,
5156: -- Do not raise an error if it's the first pass
5157: if (p_pass = 1) then
5158: p_copy_redo_flag := TRUE;
5159: else
5160: raise FND_API.G_EXC_ERROR;
5161: end if; -- if p_pass
5162: end if;
5163: end if;
5164:
5198: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5199: FND_MESSAGE.SET_NAME('AK','AK_GRAPH_COLUMN_UPDATE_FAILED');
5200: FND_MSG_PUB.Add;
5201: end if;
5202: raise FND_API.G_EXC_ERROR;
5203: end if;
5204:
5205: -- ** commit the update **
5206: -- commit;
5211: ' ' || p_region_code);
5212: FND_MSG_PUB.Add;
5213: end if;
5214:
5215: p_return_status := FND_API.G_RET_STS_SUCCESS;
5216:
5217: FND_MSG_PUB.Count_And_Get (
5218: p_count => p_msg_count,
5219: p_data => p_msg_data);
5229: to_char(p_graph_number));
5230: FND_MSG_PUB.Add;
5231: end if;
5232: rollback to start_update_graph_column;
5233: p_return_status := FND_API.G_RET_STS_ERROR;
5234: FND_MSG_PUB.Count_And_Get (
5235: p_count => p_msg_count,
5236: p_data => p_msg_data);
5237: WHEN FND_API.G_EXC_ERROR THEN
5233: p_return_status := FND_API.G_RET_STS_ERROR;
5234: FND_MSG_PUB.Count_And_Get (
5235: p_count => p_msg_count,
5236: p_data => p_msg_data);
5237: WHEN FND_API.G_EXC_ERROR THEN
5238: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5239: FND_MESSAGE.SET_NAME('AK','AK_GRAPH_COLUMN_NOT_UPDATED');
5240: FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
5241: p_region_code||' '||
5243: ' '||p_attribute_code||' '||
5244: to_char(p_graph_number));
5245: FND_MSG_PUB.Add;
5246: end if;
5247: p_return_status := FND_API.G_RET_STS_ERROR;
5248: rollback to start_update_graph_column;
5249: FND_MSG_PUB.Count_And_Get (
5250: p_count => p_msg_count,
5251: p_data => p_msg_data);
5249: FND_MSG_PUB.Count_And_Get (
5250: p_count => p_msg_count,
5251: p_data => p_msg_data);
5252: WHEN OTHERS THEN
5253: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5254: rollback to start_update_graph_column;
5255: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5256: SUBSTR (SQLERRM, 1, 240) );
5257: FND_MSG_PUB.Add;
5288: -- Version Initial version number = 1.0
5289: -- History Current version number = 1.0
5290: --=======================================================
5291: PROCEDURE UPDATE_LOV_RELATION (
5292: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5293: p_api_version_number IN NUMBER,
5294: p_init_msg_tbl IN BOOLEAN := FALSE,
5295: p_msg_count OUT NOCOPY NUMBER,
5296: p_msg_data OUT NOCOPY VARCHAR2,
5294: p_init_msg_tbl IN BOOLEAN := FALSE,
5295: p_msg_count OUT NOCOPY NUMBER,
5296: p_msg_data OUT NOCOPY VARCHAR2,
5297: p_return_status OUT NOCOPY VARCHAR2,
5298: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5299: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5300: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5301: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5302: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5295: p_msg_count OUT NOCOPY NUMBER,
5296: p_msg_data OUT NOCOPY VARCHAR2,
5297: p_return_status OUT NOCOPY VARCHAR2,
5298: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5299: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5300: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5301: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5302: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5303: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5296: p_msg_data OUT NOCOPY VARCHAR2,
5297: p_return_status OUT NOCOPY VARCHAR2,
5298: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5299: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5300: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5301: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5302: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5303: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5304: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5297: p_return_status OUT NOCOPY VARCHAR2,
5298: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5299: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5300: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5301: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5302: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5303: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5304: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5305: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5298: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5299: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5300: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5301: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5302: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5303: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5304: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5305: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5306: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5299: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5300: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5301: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5302: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5303: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5304: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5305: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5306: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5307: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5300: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5301: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5302: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5303: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5304: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5305: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5306: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5307: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5308: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5301: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5302: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5303: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5304: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5305: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5306: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5307: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5308: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5309: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5302: p_lov_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5303: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5304: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5305: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5306: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5307: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5308: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5309: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5310: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5303: p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5304: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5305: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5306: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5307: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5308: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5309: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5310: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5311: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5304: p_lov_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5305: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5306: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5307: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5308: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5309: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5310: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5311: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5312: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5305: p_lov_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5306: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5307: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5308: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5309: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5310: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5311: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5312: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5313: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5306: p_base_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5307: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5308: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5309: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5310: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5311: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5312: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5313: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5314: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5307: p_base_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5308: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5309: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5310: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5311: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5312: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5313: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5314: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5315: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5308: p_direction_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5309: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5310: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5311: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5312: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5313: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5314: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5315: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5316: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5309: p_base_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5310: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5311: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5312: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5313: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5314: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5315: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5316: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5317: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5310: p_base_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5311: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5312: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5313: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5314: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5315: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5316: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5317: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5318: p_pass IN NUMBER,
5311: p_required_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
5312: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5313: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5314: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5315: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5316: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5317: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5318: p_pass IN NUMBER,
5319: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5312: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5313: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5314: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5315: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5316: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5317: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5318: p_pass IN NUMBER,
5319: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5320: ) is
5313: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5314: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5315: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5316: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5317: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5318: p_pass IN NUMBER,
5319: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5320: ) is
5321: cursor l_get_row_csr is
5345: l_last_update_login number;
5346: l_last_updated_by number;
5347: l_return_status varchar2(1);
5348: begin
5349: IF NOT FND_API.Compatible_API_Call (
5350: l_api_version_number, p_api_version_number, l_api_name,
5351: G_PKG_NAME) then
5352: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5353: return;
5348: begin
5349: IF NOT FND_API.Compatible_API_Call (
5350: l_api_version_number, p_api_version_number, l_api_name,
5351: G_PKG_NAME) then
5352: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5353: return;
5354: END IF;
5355:
5356: -- Initialize the message table if requested.
5370: FND_MSG_PUB.Add;
5371: end if;
5372: --dbms_output.put_line(l_api_name || 'Error - Row does not exist');
5373: close l_get_row_csr;
5374: raise FND_API.G_EXC_ERROR;
5375: end if;
5376: close l_get_row_csr;
5377:
5378: --** validate table columns passed in **
5375: end if;
5376: close l_get_row_csr;
5377:
5378: --** validate table columns passed in **
5379: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5380: if not AK_REGION2_PVT.VALIDATE_LOV_RELATION (
5381: p_validation_level => p_validation_level,
5382: p_api_version_number => 1.0,
5383: p_return_status => l_return_status,
5401: -- Do not raise an error if it's the first pass
5402: if (p_pass = 1) then
5403: p_copy_redo_flag := TRUE;
5404: else
5405: raise FND_API.G_EXC_ERROR;
5406: end if; -- /* if p_pass */
5407: end if;
5408: end if;
5409:
5413:
5414: --** - next, load non-null, non-key columns **
5415:
5416: -- default a value for required_flag column if no value is given
5417: if ( p_required_flag <> FND_API.G_MISS_CHAR and p_required_flag is not null ) then
5418: l_lov_relation_rec.required_flag := p_required_flag;
5419: else
5420: l_lov_relation_rec.required_flag := l_required_flag;
5421: end if;
5419: else
5420: l_lov_relation_rec.required_flag := l_required_flag;
5421: end if;
5422:
5423: if (p_created_by <> FND_API.G_MISS_NUM) then
5424: l_created_by := p_created_by;
5425: end if;
5426:
5427: if (p_creation_date <> FND_API.G_MISS_DATE) then
5423: if (p_created_by <> FND_API.G_MISS_NUM) then
5424: l_created_by := p_created_by;
5425: end if;
5426:
5427: if (p_creation_date <> FND_API.G_MISS_DATE) then
5428: l_creation_date := p_creation_date;
5429: end if;
5430:
5431: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5427: if (p_creation_date <> FND_API.G_MISS_DATE) then
5428: l_creation_date := p_creation_date;
5429: end if;
5430:
5431: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5432: l_last_updated_by := p_last_updated_by;
5433: end if;
5434:
5435: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5431: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5432: l_last_updated_by := p_last_updated_by;
5433: end if;
5434:
5435: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5436: l_last_update_date := p_last_update_date;
5437: end if;
5438:
5439: if (p_last_update_login <> FND_API.G_MISS_NUM) then
5435: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5436: l_last_update_date := p_last_update_date;
5437: end if;
5438:
5439: if (p_last_update_login <> FND_API.G_MISS_NUM) then
5440: l_last_update_login := p_last_update_login;
5441: end if;
5442:
5443: -- Set WHO columns
5474: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5475: FND_MESSAGE.SET_NAME('AK','AK_LOV_RELATION_UPDATE_FAILED');
5476: FND_MSG_PUB.Add;
5477: end if;
5478: raise FND_API.G_EXC_ERROR;
5479: end if;
5480:
5481: -- /** commit the update **/
5482: -- commit;
5488: FND_MSG_PUB.Add;
5489: end if;
5490:
5491: end if;
5492: p_return_status := FND_API.G_RET_STS_SUCCESS;
5493:
5494: FND_MSG_PUB.Count_And_Get (
5495: p_count => p_msg_count,
5496: p_data => p_msg_data);
5509: ' '||to_char(p_base_region_appl_id)||' '||p_base_region_code);
5510: FND_MSG_PUB.Add;
5511: end if;
5512: rollback to start_update_lov_relation;
5513: p_return_status := FND_API.G_RET_STS_ERROR;
5514: FND_MSG_PUB.Count_And_Get (
5515: p_count => p_msg_count,
5516: p_data => p_msg_data);
5517: WHEN FND_API.G_EXC_ERROR THEN
5513: p_return_status := FND_API.G_RET_STS_ERROR;
5514: FND_MSG_PUB.Count_And_Get (
5515: p_count => p_msg_count,
5516: p_data => p_msg_data);
5517: WHEN FND_API.G_EXC_ERROR THEN
5518: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5519: FND_MESSAGE.SET_NAME('AK','AK_LOV_RELATION_NOT_UPDATED');
5520: FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
5521: p_region_code||' '||to_char(p_attribute_application_id)||
5525: p_lov_region_code||' '||to_char(p_base_attribute_appl_id)||
5526: ' '||p_base_attribute_code);
5527: FND_MSG_PUB.Add;
5528: end if;
5529: p_return_status := FND_API.G_RET_STS_ERROR;
5530: rollback to start_update_lov_relation;
5531: FND_MSG_PUB.Count_And_Get (
5532: p_count => p_msg_count,
5533: p_data => p_msg_data);
5531: FND_MSG_PUB.Count_And_Get (
5532: p_count => p_msg_count,
5533: p_data => p_msg_data);
5534: WHEN OTHERS THEN
5535: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5536: rollback to start_update_lov_relation;
5537: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5538: SUBSTR (SQLERRM, 1, 240) );
5539: FND_MSG_PUB.Add;
5589: l_api_version_number CONSTANT number := 1.0;
5590: l_api_name CONSTANT varchar2(30) := 'Category_Usage_Exists';
5591: l_dummy number;
5592: begin
5593: IF NOT FND_API.Compatible_API_Call (
5594: l_api_version_number, p_api_version_number, l_api_name,
5595: G_PKG_NAME) then
5596: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5597: return FALSE;
5592: begin
5593: IF NOT FND_API.Compatible_API_Call (
5594: l_api_version_number, p_api_version_number, l_api_name,
5595: G_PKG_NAME) then
5596: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5597: return FALSE;
5598: END IF;
5599:
5600: open l_check_category_usage_csr;
5600: open l_check_category_usage_csr;
5601: fetch l_check_category_usage_csr into l_dummy;
5602: if (l_check_category_usage_csr%notfound) then
5603: close l_check_category_usage_csr;
5604: p_return_status := FND_API.G_RET_STS_SUCCESS;
5605: return FALSE;
5606: else
5607: close l_check_category_usage_csr;
5608: p_return_status := FND_API.G_RET_STS_SUCCESS;
5604: p_return_status := FND_API.G_RET_STS_SUCCESS;
5605: return FALSE;
5606: else
5607: close l_check_category_usage_csr;
5608: p_return_status := FND_API.G_RET_STS_SUCCESS;
5609: return TRUE;
5610: end if;
5611:
5612: EXCEPTION
5609: return TRUE;
5610: end if;
5611:
5612: EXCEPTION
5613: WHEN FND_API.G_EXC_ERROR THEN
5614: p_return_status := FND_API.G_RET_STS_ERROR;
5615: return FALSE;
5616: WHEN OTHERS THEN
5617: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5610: end if;
5611:
5612: EXCEPTION
5613: WHEN FND_API.G_EXC_ERROR THEN
5614: p_return_status := FND_API.G_RET_STS_ERROR;
5615: return FALSE;
5616: WHEN OTHERS THEN
5617: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5618: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5613: WHEN FND_API.G_EXC_ERROR THEN
5614: p_return_status := FND_API.G_RET_STS_ERROR;
5615: return FALSE;
5616: WHEN OTHERS THEN
5617: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5618: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5619: SUBSTR (SQLERRM, 1, 240) );
5620: FND_MSG_PUB.Add;
5621: return FALSE;
5651: --
5652: -- Version Initial version number = 1.1
5653: --=======================================================
5654: FUNCTION VALIDATE_CATEGORY_USAGE (
5655: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5656: p_api_version_number IN NUMBER,
5657: p_return_status OUT NOCOPY VARCHAR2,
5658: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5659: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5654: FUNCTION VALIDATE_CATEGORY_USAGE (
5655: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5656: p_api_version_number IN NUMBER,
5657: p_return_status OUT NOCOPY VARCHAR2,
5658: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5659: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5660: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5661: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5662: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5655: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5656: p_api_version_number IN NUMBER,
5657: p_return_status OUT NOCOPY VARCHAR2,
5658: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5659: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5660: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5661: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5662: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5663: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5656: p_api_version_number IN NUMBER,
5657: p_return_status OUT NOCOPY VARCHAR2,
5658: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5659: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5660: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5661: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5662: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5663: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5664: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5657: p_return_status OUT NOCOPY VARCHAR2,
5658: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5659: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5660: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5661: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5662: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5663: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5664: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5665: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5658: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5659: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5660: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5661: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5662: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5663: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5664: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5665: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5666: p_caller IN VARCHAR2,
5659: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5660: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5661: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5662: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5663: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5664: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5665: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5666: p_caller IN VARCHAR2,
5667: p_pass IN NUMBER := 2
5660: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5661: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5662: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5663: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5664: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5665: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5666: p_caller IN VARCHAR2,
5667: p_pass IN NUMBER := 2
5668: ) return boolean is
5661: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5662: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5663: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5664: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5665: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5666: p_caller IN VARCHAR2,
5667: p_pass IN NUMBER := 2
5668: ) return boolean is
5669:
5689: l_error BOOLEAN;
5690: l_return_status VARCHAR2(1);
5691: begin
5692:
5693: IF NOT FND_API.Compatible_API_Call (
5694: l_api_version_number, p_api_version_number, l_api_name,
5695: G_PKG_NAME) then
5696: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5697: return FALSE;
5692:
5693: IF NOT FND_API.Compatible_API_Call (
5694: l_api_version_number, p_api_version_number, l_api_name,
5695: G_PKG_NAME) then
5696: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5697: return FALSE;
5698: END IF;
5699:
5700: l_error := FALSE;
5699:
5700: l_error := FALSE;
5701:
5702: --** if validation level is none, no validation is necessary
5703: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
5704: p_return_status := FND_API.G_RET_STS_SUCCESS;
5705: return TRUE;
5706: end if;
5707:
5700: l_error := FALSE;
5701:
5702: --** if validation level is none, no validation is necessary
5703: if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
5704: p_return_status := FND_API.G_RET_STS_SUCCESS;
5705: return TRUE;
5706: end if;
5707:
5708: --** check that key columns are not null and not missing **
5706: end if;
5707:
5708: --** check that key columns are not null and not missing **
5709: if ((p_region_application_id is null) or
5710: (p_region_application_id = FND_API.G_MISS_NUM)) then
5711: l_error := TRUE;
5712: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
5713: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
5714: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
5716: end if;
5717: end if;
5718:
5719: if ((p_region_code is null) or
5720: (p_region_code = FND_API.G_MISS_CHAR)) then
5721: l_error := TRUE;
5722: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
5723: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
5724: FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
5726: end if;
5727: end if;
5728:
5729: if ((p_attribute_application_id is null) or
5730: (p_attribute_application_id = FND_API.G_MISS_NUM)) then
5731: l_error := TRUE;
5732: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
5733: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
5734: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
5736: end if;
5737: end if;
5738:
5739: if ((p_attribute_code is null) or
5740: (p_attribute_code = FND_API.G_MISS_CHAR)) then
5741: l_error := TRUE;
5742: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
5743: FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
5744: FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
5787: end if;
5788: close l_check_category_csr;
5789:
5790: -- return true if no error, false otherwise
5791: p_return_status := FND_API.G_RET_STS_SUCCESS;
5792: return (not l_error);
5793:
5794: EXCEPTION
5795: WHEN FND_API.G_EXC_ERROR THEN
5791: p_return_status := FND_API.G_RET_STS_SUCCESS;
5792: return (not l_error);
5793:
5794: EXCEPTION
5795: WHEN FND_API.G_EXC_ERROR THEN
5796: p_return_status := FND_API.G_RET_STS_ERROR;
5797: return FALSE;
5798: WHEN OTHERS THEN
5799: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5792: return (not l_error);
5793:
5794: EXCEPTION
5795: WHEN FND_API.G_EXC_ERROR THEN
5796: p_return_status := FND_API.G_RET_STS_ERROR;
5797: return FALSE;
5798: WHEN OTHERS THEN
5799: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5800: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5795: WHEN FND_API.G_EXC_ERROR THEN
5796: p_return_status := FND_API.G_RET_STS_ERROR;
5797: return FALSE;
5798: WHEN OTHERS THEN
5799: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5800: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5801: SUBSTR (SQLERRM, 1, 240) );
5802: FND_MSG_PUB.Add;
5803: return FALSE;
5828: --
5829: -- Version Initial version number = 1.1
5830: --=======================================================
5831: PROCEDURE CREATE_CATEGORY_USAGE (
5832: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5833: p_api_version_number IN NUMBER,
5834: p_init_msg_tbl IN BOOLEAN := FALSE,
5835: p_msg_count OUT NOCOPY NUMBER,
5836: p_msg_data OUT NOCOPY VARCHAR2,
5834: p_init_msg_tbl IN BOOLEAN := FALSE,
5835: p_msg_count OUT NOCOPY NUMBER,
5836: p_msg_data OUT NOCOPY VARCHAR2,
5837: p_return_status OUT NOCOPY VARCHAR2,
5838: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5839: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5840: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5841: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5842: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5835: p_msg_count OUT NOCOPY NUMBER,
5836: p_msg_data OUT NOCOPY VARCHAR2,
5837: p_return_status OUT NOCOPY VARCHAR2,
5838: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5839: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5840: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5841: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5842: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5843: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5836: p_msg_data OUT NOCOPY VARCHAR2,
5837: p_return_status OUT NOCOPY VARCHAR2,
5838: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5839: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5840: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5841: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5842: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5843: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5844: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5837: p_return_status OUT NOCOPY VARCHAR2,
5838: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5839: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5840: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5841: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5842: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5843: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5844: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5845: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5838: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
5839: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5840: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5841: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5842: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5843: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5844: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5845: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5846: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5839: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5840: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5841: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5842: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5843: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5844: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5845: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5846: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5847: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5840: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
5841: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5842: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5843: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5844: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5845: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5846: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5847: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5848: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5841: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5842: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5843: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5844: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5845: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5846: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5847: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5848: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5849: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5842: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5843: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5844: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5845: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5846: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5847: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5848: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5849: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5850: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5843: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5844: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5845: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5846: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5847: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5848: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5849: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5850: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5851: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5844: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5845: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5846: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5847: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5848: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5849: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5850: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5851: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5852: p_pass IN NUMBER,
5845: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
5846: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5847: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5848: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5849: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5850: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5851: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5852: p_pass IN NUMBER,
5853: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5846: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5847: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5848: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5849: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5850: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5851: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5852: p_pass IN NUMBER,
5853: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5854: ) is
5847: p_creation_date IN DATE := FND_API.G_MISS_DATE,
5848: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5849: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5850: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5851: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5852: p_pass IN NUMBER,
5853: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5854: ) is
5855:
5863: l_return_status varchar2(1);
5864:
5865: begin
5866:
5867: IF NOT FND_API.Compatible_API_Call (
5868: l_api_version_number, p_api_version_number, l_api_name,
5869: G_PKG_NAME) then
5870: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5871: return;
5866:
5867: IF NOT FND_API.Compatible_API_Call (
5868: l_api_version_number, p_api_version_number, l_api_name,
5869: G_PKG_NAME) then
5870: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5871: return;
5872: END IF;
5873:
5874: -- Initialize the message table if requested.
5891: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
5892: FND_MESSAGE.SET_NAME('AK','AK_CATEGORY_USAGE_EXISTS');
5893: FND_MSG_PUB.Add;
5894: end if;
5895: raise FND_API.G_EXC_ERROR;
5896: end if;
5897:
5898: --** validate table columns passed in **
5899: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5895: raise FND_API.G_EXC_ERROR;
5896: end if;
5897:
5898: --** validate table columns passed in **
5899: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5900: if not AK_REGION2_PVT.VALIDATE_CATEGORY_USAGE (
5901: p_validation_level => p_validation_level,
5902: p_api_version_number => 1.0,
5903: p_return_status => l_return_status,
5915: -- Do not raise an error if it's the first pass
5916: if (p_pass = 1) then
5917: p_copy_redo_flag := TRUE;
5918: else
5919: raise FND_API.G_EXC_ERROR;
5920: end if;
5921: end if;
5922: end if;
5923:
5920: end if;
5921: end if;
5922: end if;
5923:
5924: if (p_created_by <> FND_API.G_MISS_NUM) then
5925: l_created_by := p_created_by;
5926: end if;
5927:
5928: if (p_creation_date <> FND_API.G_MISS_DATE) then
5924: if (p_created_by <> FND_API.G_MISS_NUM) then
5925: l_created_by := p_created_by;
5926: end if;
5927:
5928: if (p_creation_date <> FND_API.G_MISS_DATE) then
5929: l_creation_date := p_creation_date;
5930: end if;
5931:
5932: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5928: if (p_creation_date <> FND_API.G_MISS_DATE) then
5929: l_creation_date := p_creation_date;
5930: end if;
5931:
5932: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5933: l_last_updated_by := p_last_updated_by;
5934: end if;
5935:
5936: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5932: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5933: l_last_updated_by := p_last_updated_by;
5934: end if;
5935:
5936: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5937: l_last_update_date := p_last_update_date;
5938: end if;
5939:
5940: if (p_last_update_login <> FND_API.G_MISS_NUM) then
5936: if (p_last_update_date <> FND_API.G_MISS_DATE) then
5937: l_last_update_date := p_last_update_date;
5938: end if;
5939:
5940: if (p_last_update_login <> FND_API.G_MISS_NUM) then
5941: l_last_update_login := p_last_update_login;
5942: end if;
5943:
5944: -- Create record if no validation error was found
6001: p_category_name);
6002: FND_MSG_PUB.Add;
6003: end if;
6004:
6005: p_return_status := FND_API.G_RET_STS_SUCCESS;
6006:
6007: FND_MSG_PUB.Count_And_Get (
6008: p_count => p_msg_count,
6009: p_data => p_msg_data);
6017: ' '||p_attribute_code||' '||
6018: p_category_name);
6019: FND_MSG_PUB.Add;
6020: end if;
6021: p_return_status := FND_API.G_RET_STS_ERROR;
6022: rollback to start_create_category_usage;
6023: FND_MSG_PUB.Count_And_Get (
6024: p_count => p_msg_count,
6025: p_data => p_msg_data);
6022: rollback to start_create_category_usage;
6023: FND_MSG_PUB.Count_And_Get (
6024: p_count => p_msg_count,
6025: p_data => p_msg_data);
6026: WHEN FND_API.G_EXC_ERROR THEN
6027: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6028: FND_MESSAGE.SET_NAME('AK','AK_CATEGORY_USAGE_NOT_CREATED');
6029: FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
6030: p_region_code||' '||to_char(p_attribute_application_id)||
6031: ' '||p_attribute_code||' '||
6032: p_category_name);
6033: FND_MSG_PUB.Add;
6034: end if;
6035: p_return_status := FND_API.G_RET_STS_ERROR;
6036: rollback to start_create_category_usage;
6037: FND_MSG_PUB.Count_And_Get (
6038: p_count => p_msg_count,
6039: p_data => p_msg_data);
6037: FND_MSG_PUB.Count_And_Get (
6038: p_count => p_msg_count,
6039: p_data => p_msg_data);
6040: WHEN OTHERS THEN
6041: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6042: rollback to start_create_category_usage;
6043: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
6044: SUBSTR (SQLERRM, 1, 240) );
6045: FND_MSG_PUB.Add;
6072: -- Version Initial version number = 1.0
6073: -- History Current version number = 1.0
6074: --=======================================================
6075: PROCEDURE UPDATE_CATEGORY_USAGE (
6076: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6077: p_api_version_number IN NUMBER,
6078: p_init_msg_tbl IN BOOLEAN := FALSE,
6079: p_msg_count OUT NOCOPY NUMBER,
6080: p_msg_data OUT NOCOPY VARCHAR2,
6078: p_init_msg_tbl IN BOOLEAN := FALSE,
6079: p_msg_count OUT NOCOPY NUMBER,
6080: p_msg_data OUT NOCOPY VARCHAR2,
6081: p_return_status OUT NOCOPY VARCHAR2,
6082: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
6083: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6084: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6085: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6086: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6079: p_msg_count OUT NOCOPY NUMBER,
6080: p_msg_data OUT NOCOPY VARCHAR2,
6081: p_return_status OUT NOCOPY VARCHAR2,
6082: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
6083: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6084: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6085: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6086: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6087: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6080: p_msg_data OUT NOCOPY VARCHAR2,
6081: p_return_status OUT NOCOPY VARCHAR2,
6082: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
6083: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6084: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6085: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6086: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6087: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6088: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6081: p_return_status OUT NOCOPY VARCHAR2,
6082: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
6083: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6084: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6085: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6086: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6087: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6088: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6089: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6082: p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
6083: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6084: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6085: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6086: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6087: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6088: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6089: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6090: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6083: p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6084: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6085: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6086: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6087: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6088: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6089: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6090: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6091: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6084: p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
6085: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6086: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6087: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6088: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6089: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6090: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6091: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6092: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6085: p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6086: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6087: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6088: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6089: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6090: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6091: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6092: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6093: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6086: p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6087: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6088: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6089: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6090: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6091: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6092: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6093: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6094: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6087: p_category_id IN NUMBER := FND_API.G_MISS_NUM,
6088: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6089: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6090: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6091: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6092: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6093: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6094: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6095: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6088: p_application_id IN NUMBER := FND_API.G_MISS_NUM,
6089: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6090: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6091: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6092: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6093: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6094: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6095: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6096: p_pass IN NUMBER,
6089: p_show_all IN VARCHAR2 := FND_API.G_MISS_CHAR,
6090: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6091: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6092: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6093: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6094: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6095: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6096: p_pass IN NUMBER,
6097: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
6090: p_created_by IN NUMBER := FND_API.G_MISS_NUM,
6091: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6092: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6093: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6094: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6095: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6096: p_pass IN NUMBER,
6097: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
6098: ) is
6091: p_creation_date IN DATE := FND_API.G_MISS_DATE,
6092: p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6093: p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6094: p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6095: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6096: p_pass IN NUMBER,
6097: p_copy_redo_flag IN OUT NOCOPY BOOLEAN
6098: ) is
6099: cursor l_get_row_csr is
6113: l_last_update_login number;
6114: l_last_updated_by number;
6115: l_return_status varchar2(1);
6116: begin
6117: IF NOT FND_API.Compatible_API_Call (
6118: l_api_version_number, p_api_version_number, l_api_name,
6119: G_PKG_NAME) then
6120: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6121: return;
6116: begin
6117: IF NOT FND_API.Compatible_API_Call (
6118: l_api_version_number, p_api_version_number, l_api_name,
6119: G_PKG_NAME) then
6120: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6121: return;
6122: END IF;
6123:
6124: -- Initialize the message table if requested.
6143: FND_MSG_PUB.Add;
6144: end if;
6145: --dbms_output.put_line(l_api_name || 'Error - Row does not exist');
6146: close l_get_row_csr;
6147: raise FND_API.G_EXC_ERROR;
6148: end if;
6149: close l_get_row_csr;
6150:
6151: --** validate table columns passed in **
6148: end if;
6149: close l_get_row_csr;
6150:
6151: --** validate table columns passed in **
6152: if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
6153: if not AK_REGION2_PVT.VALIDATE_CATEGORY_USAGE (
6154: p_validation_level => p_validation_level,
6155: p_api_version_number => 1.0,
6156: p_return_status => l_return_status,
6168: -- Do not raise an error if it's the first pass
6169: if (p_pass = 1) then
6170: p_copy_redo_flag := TRUE;
6171: else
6172: raise FND_API.G_EXC_ERROR;
6173: end if;
6174: end if;
6175: end if;
6176:
6173: end if;
6174: end if;
6175: end if;
6176:
6177: if (p_created_by <> FND_API.G_MISS_NUM) then
6178: l_created_by := p_created_by;
6179: end if;
6180: if (p_creation_date <> FND_API.G_MISS_DATE) then
6181: l_creation_date := p_creation_date;
6176:
6177: if (p_created_by <> FND_API.G_MISS_NUM) then
6178: l_created_by := p_created_by;
6179: end if;
6180: if (p_creation_date <> FND_API.G_MISS_DATE) then
6181: l_creation_date := p_creation_date;
6182: end if;
6183: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
6184: l_last_updated_by := p_last_updated_by;
6179: end if;
6180: if (p_creation_date <> FND_API.G_MISS_DATE) then
6181: l_creation_date := p_creation_date;
6182: end if;
6183: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
6184: l_last_updated_by := p_last_updated_by;
6185: end if;
6186: if (p_last_update_date <> FND_API.G_MISS_DATE) then
6187: l_last_update_date := p_last_update_date;
6182: end if;
6183: if (p_last_updated_by <> FND_API.G_MISS_NUM) then
6184: l_last_updated_by := p_last_updated_by;
6185: end if;
6186: if (p_last_update_date <> FND_API.G_MISS_DATE) then
6187: l_last_update_date := p_last_update_date;
6188: end if;
6189: if (p_last_update_login <> FND_API.G_MISS_NUM) then
6190: l_last_update_login := p_last_update_login;
6185: end if;
6186: if (p_last_update_date <> FND_API.G_MISS_DATE) then
6187: l_last_update_date := p_last_update_date;
6188: end if;
6189: if (p_last_update_login <> FND_API.G_MISS_NUM) then
6190: l_last_update_login := p_last_update_login;
6191: end if;
6192:
6193: --** Load record to be updated to the database **
6218: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6219: FND_MESSAGE.SET_NAME('AK','AK_CATEGORY_UPDATE_FAILED');
6220: FND_MSG_PUB.Add;
6221: end if;
6222: raise FND_API.G_EXC_ERROR;
6223: end if;
6224:
6225: -- /** commit the update **/
6226: -- commit;
6233: end if;
6234:
6235: end if;
6236:
6237: p_return_status := FND_API.G_RET_STS_SUCCESS;
6238:
6239: FND_MSG_PUB.Count_And_Get (
6240: p_count => p_msg_count,
6241: p_data => p_msg_data);
6249: p_attribute_code||' '|| p_category_name ||' '||
6250: to_char(p_application_id));
6251: FND_MSG_PUB.Add;
6252: end if;
6253: p_return_status := FND_API.G_RET_STS_ERROR;
6254: rollback to start_update_category_usage;
6255: FND_MSG_PUB.Count_And_Get (
6256: p_count => p_msg_count,
6257: p_data => p_msg_data);
6255: FND_MSG_PUB.Count_And_Get (
6256: p_count => p_msg_count,
6257: p_data => p_msg_data);
6258: WHEN OTHERS THEN
6259: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6260: rollback to start_update_category_usage;
6261: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
6262: SUBSTR (SQLERRM, 1, 240) );
6263: FND_MSG_PUB.Add;