[Home] [Help]
58:
59: --------------------- initialize -----------------------
60: SAVEPOINT create_cell;
61:
62: AMS_Utility_PVT.debug_message(l_full_name||': start');
63:
64: IF FND_API.to_boolean(p_init_msg_list) THEN
65: FND_MSG_PUB.initialize;
66: END IF;
81: FETCH c_default_cell_user_status_id INTO l_cell_rec.user_status_id;
82: CLOSE c_default_cell_user_status_id;
83:
84: ----------------------- validate -----------------------
85: AMS_Utility_PVT.debug_message(l_full_name ||': validate');
86:
87: validate_cell(
88: p_api_version => l_api_version,
89: p_init_msg_list => p_init_msg_list,
116: END LOOP;
117: END IF;
118:
119: -------------------------- insert --------------------------
120: AMS_Utility_PVT.debug_message(l_full_name ||': insert');
121: AMS_Utility_PVT.debug_message('CELLiD' ||l_cell_rec.cell_id);
122:
123: INSERT INTO ams_cells_all_b(
124: cell_id,
117: END IF;
118:
119: -------------------------- insert --------------------------
120: AMS_Utility_PVT.debug_message(l_full_name ||': insert');
121: AMS_Utility_PVT.debug_message('CELLiD' ||l_cell_rec.cell_id);
122:
123: INSERT INTO ams_cells_all_b(
124: cell_id,
125: sel_type,
207: p_count => x_msg_count,
208: p_data => x_msg_data
209: );
210:
211: AMS_Utility_PVT.debug_message(l_full_name ||': end');
212:
213: EXCEPTION
214:
215: WHEN FND_API.g_exc_error THEN
275:
276: --------------------- initialize -----------------------
277: SAVEPOINT delete_cell;
278:
279: AMS_Utility_PVT.debug_message(l_full_name||': start');
280:
281: IF FND_API.to_boolean(p_init_msg_list) THEN
282: FND_MSG_PUB.initialize;
283: END IF;
293:
294: x_return_status := FND_API.G_RET_STS_SUCCESS;
295:
296: ------------------------ delete ------------------------
297: AMS_Utility_PVT.debug_message(l_full_name ||': delete');
298:
299: UPDATE ams_cells_all_b
300: SET enabled_flag = 'N'
301: WHERE cell_id = p_cell_id
321: p_count => x_msg_count,
322: p_data => x_msg_data
323: );
324:
325: AMS_Utility_PVT.debug_message(l_full_name ||': end');
326:
327: EXCEPTION
328:
329: WHEN FND_API.g_exc_error THEN
404:
405: BEGIN
406:
407: -------------------- initialize ------------------------
408: AMS_Utility_PVT.debug_message(l_full_name||': start');
409:
410: IF FND_API.to_boolean(p_init_msg_list) THEN
411: FND_MSG_PUB.initialize;
412: END IF;
422:
423: x_return_status := FND_API.G_RET_STS_SUCCESS;
424:
425: ------------------------ lock -------------------------
426: AMS_Utility_PVT.debug_message(l_full_name||': lock');
427:
428: OPEN c_cell_b;
429: FETCH c_cell_b INTO l_cell_id;
430: IF (c_cell_b%NOTFOUND) THEN
446: p_count => x_msg_count,
447: p_data => x_msg_data
448: );
449:
450: AMS_Utility_PVT.debug_message(l_full_name ||': end');
451:
452: EXCEPTION
453:
454: WHEN AMS_Utility_PVT.resource_locked THEN
450: AMS_Utility_PVT.debug_message(l_full_name ||': end');
451:
452: EXCEPTION
453:
454: WHEN AMS_Utility_PVT.resource_locked THEN
455: x_return_status := FND_API.g_ret_sts_error;
456: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
457: FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
458: FND_MSG_PUB.add;
530:
531: -------------------- initialize -------------------------
532: SAVEPOINT update_cell;
533:
534: AMS_Utility_PVT.debug_message(l_full_name||': start');
535:
536: IF FND_API.to_boolean(p_init_msg_list) THEN
537: FND_MSG_PUB.initialize;
538: END IF;
548:
549: x_return_status := FND_API.G_RET_STS_SUCCESS;
550:
551: ----------------------- validate ----------------------
552: AMS_Utility_PVT.debug_message(l_full_name ||': validate');
553: AMS_Utility_PVT.debug_message(l_full_name ||': just before call the complete_cell_rec');
554:
555: -- replace g_miss_char/num/date with current column values
556: complete_cell_rec(p_cell_rec, l_cell_rec);
549: x_return_status := FND_API.G_RET_STS_SUCCESS;
550:
551: ----------------------- validate ----------------------
552: AMS_Utility_PVT.debug_message(l_full_name ||': validate');
553: AMS_Utility_PVT.debug_message(l_full_name ||': just before call the complete_cell_rec');
554:
555: -- replace g_miss_char/num/date with current column values
556: complete_cell_rec(p_cell_rec, l_cell_rec);
557:
554:
555: -- replace g_miss_char/num/date with current column values
556: complete_cell_rec(p_cell_rec, l_cell_rec);
557:
558: AMS_Utility_PVT.debug_message(l_full_name ||': after complete_cell_rec');
559: -- item level
560: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
561: check_cell_items(
562: p_cell_rec => l_cell_rec,
571: END IF;
572:
573: -- record level
574: -------------------------- update --------------------
575: AMS_Utility_PVT.debug_message(l_full_name ||': update' ||l_cell_rec.cell_id);
576:
577: UPDATE ams_cells_all_b SET
578: sel_type = l_cell_rec.sel_type,
579: last_update_date = SYSDATE,
595: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
596: FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
597: FND_MSG_PUB.add;
598: END IF;
599: AMS_Utility_PVT.debug_message(l_full_name ||': update b');
600: RAISE FND_API.g_exc_error;
601: END IF;
602:
603: update ams_cells_all_tl set
613: IF (SQL%NOTFOUND) THEN
614: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
615: FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
616: FND_MSG_PUB.add;
617: AMS_Utility_PVT.debug_message(l_full_name ||': updatetl');
618: END IF;
619: RAISE FND_API.g_exc_error;
620: END IF;
621:
629: p_count => x_msg_count,
630: p_data => x_msg_data
631: );
632:
633: AMS_Utility_PVT.debug_message(l_full_name ||': end');
634:
635: EXCEPTION
636:
637: WHEN FND_API.g_exc_error THEN
698:
699: BEGIN
700:
701: ----------------------- initialize --------------------
702: AMS_Utility_PVT.debug_message(l_full_name||': start');
703:
704: IF FND_API.to_boolean(p_init_msg_list) THEN
705: FND_MSG_PUB.initialize;
706: END IF;
716:
717: x_return_status := FND_API.g_ret_sts_success;
718:
719: ---------------------- validate ------------------------
720: AMS_Utility_PVT.debug_message(l_full_name||': check items');
721:
722: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
723: check_cell_items(
724: p_cell_rec => p_cell_rec,
739: p_count => x_msg_count,
740: p_data => x_msg_data
741: );
742:
743: AMS_Utility_PVT.debug_message(l_full_name ||': end');
744:
745: EXCEPTION
746:
747: WHEN FND_API.g_exc_error THEN
840: -- check if this cell_id is unique.
841: IF p_validation_mode = JTF_PLSQL_API.g_create
842: AND p_cell_rec.cell_id IS NOT NULL
843: THEN
844: IF AMS_Utility_PVT.check_uniqueness(
845: 'ams_cells_vl',
846: 'cell_id = ' || p_cell_rec.cell_id
847: ) = FND_API.g_false
848: THEN
859:
860: -- Check if cell_name is unique. Need to handle create and
861: -- update differently.
862: IF p_validation_mode = JTF_PLSQL_API.g_create THEN
863: l_valid_flag := AMS_Utility_PVT.check_uniqueness(
864: 'ams_cells_vl',
865: 'cell_name = ''' || p_cell_rec.cell_name ||''''
866: );
867: ELSE
864: 'ams_cells_vl',
865: 'cell_name = ''' || p_cell_rec.cell_name ||''''
866: );
867: ELSE
868: l_valid_flag := AMS_Utility_PVT.check_uniqueness(
869: 'ams_cells_vl',
870: 'cell_name = ''' || p_cell_rec.cell_name ||
871: ''' AND cell_id <> ' || p_cell_rec.cell_id
872: );
883: END IF;
884: -- Check if cell_code is unique. Need to handle create and
885: -- update differently.
886: IF p_validation_mode = JTF_PLSQL_API.g_create THEN
887: l_valid_flag := AMS_Utility_PVT.check_uniqueness(
888: 'ams_cells_vl',
889: 'cell_code = ''' || p_cell_rec.cell_code||''''
890: );
891: ELSE
888: 'ams_cells_vl',
889: 'cell_code = ''' || p_cell_rec.cell_code||''''
890: );
891: ELSE
892: l_valid_flag := AMS_Utility_PVT.check_uniqueness(
893: 'ams_cells_vl',
894: 'cell_code = ''' || p_cell_rec.cell_name ||
895: ''' AND cell_id <> ' || p_cell_rec.cell_id
896: );
924: BEGIN
925: x_return_status := FND_API.g_ret_sts_success;
926: --------------------ownerid---------------------------
927: IF p_cell_rec.owner_id <> FND_API.g_miss_num THEN
928: IF AMS_Utility_PVT.check_fk_exists(
929: 'ams_jtf_rs_emp_v',
930: 'resource_id',
931: p_cell_rec.owner_id
932: ) = FND_API.g_false
942: END IF;
943:
944: --------------------- parent_cell_id ------------------------
945: IF p_cell_rec.parent_cell_id <> FND_API.g_miss_num THEN
946: IF AMS_Utility_PVT.check_fk_exists(
947: 'ams_cells_vl',
948: 'cell_id',
949: p_cell_rec.parent_cell_id
950: ) = FND_API.g_false
982: ----------------------- enabled_flag ------------------------
983: IF p_cell_rec.enabled_flag <> FND_API.g_miss_char
984: AND p_cell_rec.enabled_flag IS NOT NULL
985: THEN
986: IF AMS_Utility_PVT.is_Y_or_N(p_cell_rec.enabled_flag) = FND_API.g_false
987: THEN
988: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
989: THEN
990: FND_MESSAGE.set_name('AMS', 'AMS_CELL_WRONG_ENABLED_FLAG');
1251: IF p_cell_rec.status_date = FND_API.g_miss_date THEN
1252: x_complete_rec.status_date := l_cell_rec.status_date;
1253: END IF;
1254:
1255: x_complete_rec.status_code := AMS_Utility_PVT.get_system_status_code(
1256: x_complete_rec.user_status_id );
1257:
1258:
1259: END complete_cell_rec;
1319:
1320: --------------------- initialize -----------------------
1321: SAVEPOINT add_sel_workbook;
1322:
1323: AMS_Utility_PVT.debug_message(l_full_name||': start');
1324:
1325: IF FND_API.to_boolean(p_init_msg_list) THEN
1326: FND_MSG_PUB.initialize;
1327: END IF;
1337:
1338: x_return_status := FND_API.g_ret_sts_success;
1339:
1340: ------------------------ start -------------------------
1341: AMS_Utility_PVT.debug_message(l_full_name ||': start');
1342:
1343: OPEN c_discoverer_cell;
1344: FETCH c_discoverer_cell INTO l_discoverer_cell_rec;
1345: IF c_discoverer_cell%NOTFOUND THEN
1354: ------------------ Verify the sql string --------------
1355: -- Verify that the sql string in this workbook is a valid cell sql string,
1356: -- e.g., there is party_id in the select column.
1357:
1358: AMS_Utility_PVT.debug_message(l_full_name ||': get SQL string');
1359: OPEN c_discoverer_sql (l_discoverer_cell_rec.workbook_name,
1360: l_discoverer_cell_rec.worksheet_name,
1361: l_discoverer_cell_rec.workbook_owner_name);
1362: FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
1415: END IF;
1416:
1417:
1418: ----------------------- Insert ---------------------------
1419: AMS_Utility_PVT.debug_message(l_full_name ||': insert');
1420:
1421: OPEN c_act_disc_seq;
1422: FETCH c_act_disc_seq INTO l_act_disc_id;
1423: CLOSE c_act_disc_seq;
1465: p_count => x_msg_count,
1466: p_data => x_msg_data
1467: );
1468:
1469: AMS_Utility_PVT.debug_message(l_full_name ||': end');
1470:
1471: EXCEPTION
1472:
1473: WHEN FND_API.g_exc_error THEN
1549:
1550: --------------------- initialize -----------------------
1551: SAVEPOINT add_sel_sql;
1552:
1553: AMS_Utility_PVT.debug_message(l_full_name||': initialize');
1554:
1555: IF FND_API.to_boolean(p_init_msg_list) THEN
1556: FND_MSG_PUB.initialize;
1557: END IF;
1567:
1568: x_return_status := FND_API.g_ret_sts_success;
1569:
1570: ------------------------ start -------------------------
1571: AMS_Utility_PVT.debug_message(l_full_name ||': start');
1572:
1573: ------------------ Verify the sql string --------------
1574: -- Verify that the l_sql_string is a valid cell sql string,
1575: -- e.g., there is party_id in the select column.
1573: ------------------ Verify the sql string --------------
1574: -- Verify that the l_sql_string is a valid cell sql string,
1575: -- e.g., there is party_id in the select column.
1576:
1577: AMS_Utility_PVT.debug_message(l_full_name ||': get SQL string');
1578:
1579: l_sql_string := upper(l_sql_string);
1580:
1581: IF instr(l_sql_string, ' FROM ') > 0
1615: RETURN;
1616: END IF;
1617:
1618: ----------------------- Insert ---------------------------
1619: AMS_Utility_PVT.debug_message(l_full_name ||': Create_List_Query');
1620:
1621: l_list_query_rec.name := p_cell_name;
1622: l_list_query_rec.type := p_cell_code;
1623: l_list_query_rec.sql_string := p_sql_string;
1654: p_count => x_msg_count,
1655: p_data => x_msg_data
1656: );
1657:
1658: AMS_Utility_PVT.debug_message(l_full_name ||': end');
1659:
1660: EXCEPTION
1661:
1662: WHEN FND_API.g_exc_error THEN
1750: BEGIN
1751:
1752: --------------------- initialize -----------------------
1753:
1754: AMS_Utility_PVT.debug_message(l_full_name||': start');
1755:
1756: IF FND_API.to_boolean(p_init_msg_list) THEN
1757: FND_MSG_PUB.initialize;
1758: END IF;
1769: x_return_status := FND_API.g_ret_sts_success;
1770: x_sql_string := '';
1771:
1772: ---------------------- get sel type --------------------
1773: AMS_Utility_PVT.debug_message(l_full_name ||': get sel type');
1774:
1775: SELECT sel_type into l_sel_type
1776: FROM ams_cells_all_b
1777: WHERE cell_id = l_cell_id;
1776: FROM ams_cells_all_b
1777: WHERE cell_id = l_cell_id;
1778:
1779: ------------------- get sql string ---------------------
1780: AMS_Utility_PVT.debug_message(l_full_name ||': get sql string');
1781:
1782: IF upper(l_sel_type) = 'DIWB' THEN
1783: AMS_Utility_PVT.debug_message(l_full_name ||': get workbook');
1784: OPEN c_act_discoverer (l_cell_id);
1779: ------------------- get sql string ---------------------
1780: AMS_Utility_PVT.debug_message(l_full_name ||': get sql string');
1781:
1782: IF upper(l_sel_type) = 'DIWB' THEN
1783: AMS_Utility_PVT.debug_message(l_full_name ||': get workbook');
1784: OPEN c_act_discoverer (l_cell_id);
1785: FETCH c_act_discoverer INTO l_act_discoverer_rec;
1786: CLOSE c_act_discoverer;
1787:
1784: OPEN c_act_discoverer (l_cell_id);
1785: FETCH c_act_discoverer INTO l_act_discoverer_rec;
1786: CLOSE c_act_discoverer;
1787:
1788: AMS_Utility_PVT.debug_message(l_full_name ||': get SQL string');
1789: OPEN c_discoverer_sql (l_act_discoverer_rec.workbook_name,
1790: l_act_discoverer_rec.worksheet_name,
1791: l_act_discoverer_rec.workbook_owner);
1792: FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
1800: END LOOP;
1801: CLOSE c_discoverer_sql;
1802:
1803: ELSIF upper(l_sel_type) = 'SQL' THEN
1804: AMS_Utility_PVT.debug_message(l_full_name ||': get SQL string');
1805: SELECT query INTO l_sql_string
1806: FROM ams_list_queries_all
1807: WHERE upper(arc_act_list_query_used_by) = 'CELL'
1808: AND act_list_query_used_by_id = l_cell_id;
1810:
1811: ------------------------- finish -------------------------------
1812: x_sql_string := l_sql_string;
1813:
1814: AMS_Utility_PVT.debug_message(l_full_name ||': end');
1815:
1816: EXCEPTION
1817:
1818: WHEN NO_DATA_FOUND THEN
1894: BEGIN
1895:
1896: --------------------- initialize -----------------------
1897:
1898: AMS_Utility_PVT.debug_message(l_full_name||': start');
1899:
1900: IF FND_API.to_boolean(p_init_msg_list) THEN
1901: FND_MSG_PUB.initialize;
1902: END IF;
1913: x_return_status := FND_API.g_ret_sts_success;
1914: x_sql_tbl(1) := '';
1915:
1916: ---------- get sql string for current cell ------------
1917: AMS_Utility_PVT.debug_message(l_full_name ||': get sql string for current cell');
1918: get_single_sql(
1919: p_api_version => l_api_version,
1920: p_init_msg_list => p_init_msg_list,
1921: p_validation_level => p_validation_level,
1937: l_sql_string1 := l_sql_string;
1938: format_sql_string(l_sql_string1, l_sql_string,l_party_id_string);
1939: END IF;
1940:
1941: AMS_Utility_PVT.debug_message(l_full_name ||'- l_sql_string from formatSql:'||l_sql_string);
1942: AMS_Utility_PVT.debug_message(l_full_name ||'- l_party_id_string from formatSql:'||l_party_id_string);
1943:
1944: ---------- put sql string into sql table ------------
1945: AMS_Utility_PVT.debug_message(l_full_name ||': put sql string into sql table');
1938: format_sql_string(l_sql_string1, l_sql_string,l_party_id_string);
1939: END IF;
1940:
1941: AMS_Utility_PVT.debug_message(l_full_name ||'- l_sql_string from formatSql:'||l_sql_string);
1942: AMS_Utility_PVT.debug_message(l_full_name ||'- l_party_id_string from formatSql:'||l_party_id_string);
1943:
1944: ---------- put sql string into sql table ------------
1945: AMS_Utility_PVT.debug_message(l_full_name ||': put sql string into sql table');
1946:
1941: AMS_Utility_PVT.debug_message(l_full_name ||'- l_sql_string from formatSql:'||l_sql_string);
1942: AMS_Utility_PVT.debug_message(l_full_name ||'- l_party_id_string from formatSql:'||l_party_id_string);
1943:
1944: ---------- put sql string into sql table ------------
1945: AMS_Utility_PVT.debug_message(l_full_name ||': put sql string into sql table');
1946:
1947: l_count := 0;
1948: l_string_copy := l_sql_string;
1949: l_length := length(l_string_copy);
1967:
1968: WHILE l_parent_cell_id is not NULL
1969: LOOP
1970:
1971: AMS_Utility_PVT.debug_message(l_full_name ||': get sql string for parent cell');
1972: get_single_sql(
1973: p_api_version => l_api_version,
1974: p_init_msg_list => p_init_msg_list,
1975: p_validation_level => p_validation_level,
1979: p_cell_id => l_parent_cell_id,
1980: x_sql_string => l_parent_sql_string
1981: );
1982:
1983: AMS_Utility_PVT.debug_message(l_full_name ||': return status from get single sql'|| x_return_status);
1984:
1985: IF x_return_status = FND_API.g_ret_sts_error THEN
1986: RAISE FND_API.g_exc_error;
1987: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1997: ELSE
1998: l_parent_sql_string := ' WHERE PARTY_ID IN (' || l_parent_sql_string || ')';
1999: END IF;
2000:
2001: AMS_Utility_PVT.debug_message(l_full_name || ':parent sql string ' || l_parent_sql_string);
2002:
2003: -- put parent cell's sql into sql table
2004: l_string_copy := l_parent_sql_string;
2005: l_length := length(l_string_copy);
2023: WHERE cell_id = l_temp_cell_id;
2024: END LOOP;
2025:
2026: ------------------- Parse the result sql ----------------------
2027: AMS_Utility_PVT.debug_message(l_full_name || ': parse the result sql');
2028:
2029: l_count := x_sql_tbl.first;
2030: if (l_count is not null) then
2031: loop
2028:
2029: l_count := x_sql_tbl.first;
2030: if (l_count is not null) then
2031: loop
2032: AMS_Utility_PVT.debug_message(x_sql_tbl(l_count));
2033: l_count := x_sql_tbl.next(l_count);
2034: exit when (l_count is null);
2035: end loop;
2036: end if;
2048: DBMS_SQL.Native);
2049:
2050:
2051: ------------------------- finish -------------------------------
2052: AMS_Utility_PVT.debug_message(l_full_name ||': end');
2053:
2054: EXCEPTION
2055:
2056: WHEN NO_DATA_FOUND THEN
2053:
2054: EXCEPTION
2055:
2056: WHEN NO_DATA_FOUND THEN
2057: AMS_Utility_PVT.debug_message(l_full_name || ': No Data Found error in get composite sql for cell ' || l_cell_id);
2058: AMS_Utility_Pvt.Debug_Message(l_full_name || ': Please check if the workbook or sql statement is valid');
2059: x_return_status := FND_API.g_ret_sts_unexp_error ;
2060:
2061: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2054: EXCEPTION
2055:
2056: WHEN NO_DATA_FOUND THEN
2057: AMS_Utility_PVT.debug_message(l_full_name || ': No Data Found error in get composite sql for cell ' || l_cell_id);
2058: AMS_Utility_Pvt.Debug_Message(l_full_name || ': Please check if the workbook or sql statement is valid');
2059: x_return_status := FND_API.g_ret_sts_unexp_error ;
2060:
2061: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2062: THEN
2069: p_data => x_msg_data
2070: );
2071:
2072: WHEN FND_API.g_exc_error THEN
2073: AMS_Utility_Pvt.Debug_Message(l_full_name ||': Expected error in get composite sql for cell '||l_cell_id);
2074: AMS_Utility_Pvt.Debug_Message(l_full_name ||': Please check if the workbook or sql statement is valid');
2075:
2076: x_return_status := FND_API.g_ret_sts_error;
2077: FND_MSG_PUB.count_and_get(
2070: );
2071:
2072: WHEN FND_API.g_exc_error THEN
2073: AMS_Utility_Pvt.Debug_Message(l_full_name ||': Expected error in get composite sql for cell '||l_cell_id);
2074: AMS_Utility_Pvt.Debug_Message(l_full_name ||': Please check if the workbook or sql statement is valid');
2075:
2076: x_return_status := FND_API.g_ret_sts_error;
2077: FND_MSG_PUB.count_and_get(
2078: p_encoded => FND_API.g_false,
2080: p_data => x_msg_data
2081: );
2082:
2083: WHEN FND_API.g_exc_unexpected_error THEN
2084: AMS_Utility_Pvt.Debug_Message(l_full_name || ': Unexpected error in get composite sql for cell ' || l_cell_id);
2085: AMS_Utility_Pvt.Debug_Message(l_full_name || ': Please check if the workbook or sql statement is valid');
2086: x_return_status := FND_API.g_ret_sts_unexp_error ;
2087: FND_MSG_PUB.count_and_get(
2088: p_encoded => FND_API.g_false,
2081: );
2082:
2083: WHEN FND_API.g_exc_unexpected_error THEN
2084: AMS_Utility_Pvt.Debug_Message(l_full_name || ': Unexpected error in get composite sql for cell ' || l_cell_id);
2085: AMS_Utility_Pvt.Debug_Message(l_full_name || ': Please check if the workbook or sql statement is valid');
2086: x_return_status := FND_API.g_ret_sts_unexp_error ;
2087: FND_MSG_PUB.count_and_get(
2088: p_encoded => FND_API.g_false,
2089: p_count => x_msg_count,
2090: p_data => x_msg_data
2091: );
2092:
2093: WHEN OTHERS THEN
2094: AMS_Utility_Pvt.Debug_Message(l_full_name ||': Error in get composite sql for cell ' || l_cell_id);
2095: AMS_Utility_Pvt.Debug_Message(l_full_name ||': Please check if the workbook or sql statement is valid');
2096: x_return_status := FND_API.g_ret_sts_unexp_error ;
2097: FND_MSG_PUB.count_and_get(
2098: p_encoded => FND_API.g_false,
2091: );
2092:
2093: WHEN OTHERS THEN
2094: AMS_Utility_Pvt.Debug_Message(l_full_name ||': Error in get composite sql for cell ' || l_cell_id);
2095: AMS_Utility_Pvt.Debug_Message(l_full_name ||': Please check if the workbook or sql statement is valid');
2096: x_return_status := FND_API.g_ret_sts_unexp_error ;
2097: FND_MSG_PUB.count_and_get(
2098: p_encoded => FND_API.g_false,
2099: p_count => x_msg_count,
2145: l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2146:
2147: BEGIN
2148:
2149: AMS_Utility_PVT.debug_message(l_full_name||': start');
2150: --l_string := upper(l_string);
2151:
2152: -- if there are redundant white space at end of the string, remove it
2153: l_string := rtrim(l_string);
2167: ELSIF instr(upper(l_string), '.PARTY_ID') = 0 THEN
2168: -- simple select ... party_id .... from ...
2169: x_string := concat('SELECT DISTINCT PARTY_ID ',
2170: substr(l_string, instr(upper(l_string), 'FROM')));
2171: AMS_Utility_PVT.debug_message(l_full_name||':x_string:'||x_string);
2172:
2173: -- bug:4695424 fix, adding the party_id string for where there is no alias
2174: x_party_id_string := 'PARTY_ID';
2175: ELSE
2187: ELSE
2188: -- get select ... ,ams_table.party_id
2189: l_party_id_str := substr(l_tmp_string, l_pos_comma + 1);
2190: END IF;
2191: AMS_Utility_PVT.debug_message(l_full_name||': l_party_id_str'||l_party_id_str);
2192: x_party_id_string := l_party_id_str;
2193: x_string := 'SELECT DISTINCT ' || l_party_id_str || ' '|| substr(l_string, instr(upper(l_string), 'FROM'));
2194: END IF;
2195:
2247: BEGIN
2248:
2249: --------------------- initialize -----------------------
2250:
2251: AMS_Utility_PVT.debug_message(l_full_name||': start');
2252:
2253: IF FND_API.to_boolean(p_init_msg_list) THEN
2254: FND_MSG_PUB.initialize;
2255: END IF;
2267: x_sql_string := '';
2268:
2269: ------------------- get sql string ---------------------
2270:
2271: AMS_Utility_PVT.debug_message(l_full_name ||': get workbook');
2272: OPEN c_discoverer (l_disc_sql_id);
2273: FETCH c_discoverer INTO l_discoverer_rec;
2274: CLOSE c_discoverer;
2275:
2272: OPEN c_discoverer (l_disc_sql_id);
2273: FETCH c_discoverer INTO l_discoverer_rec;
2274: CLOSE c_discoverer;
2275:
2276: AMS_Utility_PVT.debug_message(l_full_name ||': get SQL string');
2277: OPEN c_discoverer_sql (l_discoverer_rec.workbook_name,
2278: l_discoverer_rec.worksheet_name,
2279: l_discoverer_rec.workbook_owner_name);
2280: FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
2294: p_count => x_msg_count,
2295: p_data => x_msg_data
2296: );
2297:
2298: AMS_Utility_PVT.debug_message(l_full_name ||': end');
2299:
2300: EXCEPTION
2301:
2302: WHEN NO_DATA_FOUND THEN
2370: BEGIN
2371:
2372: --------------------- initialize -----------------------
2373:
2374: AMS_Utility_PVT.debug_message(l_full_name||': start');
2375:
2376: IF FND_API.to_boolean(p_init_msg_list) THEN
2377: FND_MSG_PUB.initialize;
2378: END IF;
2388:
2389: x_return_status := FND_API.g_ret_sts_success;
2390:
2391: ------------------ Validate sql string ----------------------
2392: AMS_Utility_PVT.debug_message(l_full_name||': validate sql string');
2393:
2394: l_sql_string := upper(l_sql_string);
2395: IF instr(l_sql_string, 'PARTY_ID') = 0
2396: OR instr(l_sql_string, 'PARTY_ID', 1, 1) > instr(l_sql_string, 'FROM', 1, 1)
2405: END IF;
2406:
2407: ------------------- get segment size ---------------------
2408:
2409: AMS_Utility_PVT.debug_message(l_full_name ||': get segment size');
2410:
2411: l_party_cur := DBMS_SQL.OPEN_CURSOR;
2412: l_sql_string := 'SELECT COUNT(*) FROM (' || l_sql_string || ')';
2413:
2432: p_count => x_msg_count,
2433: p_data => x_msg_data
2434: );
2435:
2436: AMS_Utility_PVT.debug_message(l_full_name ||': end');
2437:
2438: EXCEPTION
2439:
2440: WHEN NO_DATA_FOUND THEN
2531: BEGIN
2532:
2533: --------------------- initialize -----------------------
2534:
2535: AMS_Utility_PVT.debug_message(l_full_name||': start');
2536:
2537: IF FND_API.to_boolean(p_init_msg_list) THEN
2538: FND_MSG_PUB.initialize;
2539: END IF;
2549:
2550: x_return_status := FND_API.g_ret_sts_success;
2551:
2552: ------------------ Get comp sql string ----------------------
2553: AMS_Utility_PVT.debug_message(l_full_name||': get comp sql string');
2554: AMS_CELL_PVT.get_comp_sql(
2555: p_api_version => 1,
2556: p_init_msg_list => NULL,
2557: p_validation_level => NULL,
2570: END IF;
2571:
2572:
2573: ------------------- get segment size ---------------------
2574: AMS_Utility_PVT.debug_message(l_full_name ||': get segment size');
2575: AMS_Utility_PVT.debug_message(l_full_name ||': construct new sql');
2576:
2577: l_count := 1 ;
2578: l_sql_tbl_new(l_count) := 'select count(*) from ( ';
2571:
2572:
2573: ------------------- get segment size ---------------------
2574: AMS_Utility_PVT.debug_message(l_full_name ||': get segment size');
2575: AMS_Utility_PVT.debug_message(l_full_name ||': construct new sql');
2576:
2577: l_count := 1 ;
2578: l_sql_tbl_new(l_count) := 'select count(*) from ( ';
2579: for i in l_sql_tbl.first .. l_sql_tbl.last
2583: end loop;
2584: l_sql_tbl_new(l_count+1) := ' ) ';
2585:
2586: -- Open the cursor and parse it
2587: AMS_Utility_PVT.Debug_Message(l_api_name||': Parse the new sql ');
2588: IF (DBMS_SQL.Is_Open(l_party_cur) = FALSE) THEN
2589: l_party_cur := DBMS_SQL.Open_Cursor ;
2590: END IF;
2591: DBMS_SQL.Parse(l_party_cur ,
2597:
2598: -- l_dummy := DBMS_SQL.Execute(l_party_cur);
2599: DBMS_SQL.DEFINE_COLUMN(l_party_cur,1,l_temp);
2600: l_dummy := DBMS_SQL.Execute(l_party_cur);
2601: AMS_Utility_PVT.Debug_Message(l_api_name||': Executed the new sql ');
2602:
2603: LOOP
2604: IF DBMS_SQL.FETCH_ROWS(l_party_cur)>0 THEN
2605: -- get column values of the row
2616: ------------------------- finish -------------------------------
2617: -- x_segment_size := l_count;
2618: x_segment_size := l_segment_size;
2619:
2620: AMS_Utility_PVT.debug_message(l_full_name ||': x_segment_size:'||x_segment_size);
2621:
2622: FND_MSG_PUB.count_and_get(
2623: p_encoded => FND_API.g_false,
2624: p_count => x_msg_count,
2624: p_count => x_msg_count,
2625: p_data => x_msg_data
2626: );
2627:
2628: AMS_Utility_PVT.debug_message(l_full_name ||': end');
2629:
2630: EXCEPTION
2631:
2632: WHEN FND_API.g_exc_error THEN
2704:
2705: --------------------- initialize -----------------------
2706: SAVEPOINT create_sql_cell;
2707:
2708: AMS_Utility_PVT.debug_message(l_full_name||': start');
2709:
2710: IF FND_API.to_boolean(p_init_msg_list) THEN
2711: FND_MSG_PUB.initialize;
2712: END IF;
2731: RAISE FND_API.g_exc_error;
2732: end if;
2733: --
2734:
2735: AMS_Utility_PVT.debug_message(l_full_name ||': create_cell');
2736:
2737: l_cell_rec.cell_id := l_sql_cell_rec.cell_id;
2738: l_cell_rec.sel_type := l_sql_cell_rec.sel_type;
2739: l_cell_rec.last_update_date := l_sql_cell_rec.last_update_date;
2773: RAISE FND_API.g_exc_unexpected_error;
2774: END IF;
2775:
2776: --------------------- add selection ----------------------------
2777: AMS_Utility_PVT.debug_message(l_full_name ||': add selection');
2778: IF 'DIWB' = upper(l_sql_cell_rec.sel_type)
2779: AND l_sql_cell_rec.discoverer_sql_id IS NOT NULL THEN
2780: add_sel_workbook(
2781: p_api_version => l_api_version,
2826: p_count => x_msg_count,
2827: p_data => x_msg_data
2828: );
2829:
2830: AMS_Utility_PVT.debug_message(l_full_name ||': end');
2831:
2832: EXCEPTION
2833:
2834: WHEN FND_API.g_exc_error THEN
2923:
2924: --------------------- initialize -----------------------
2925: SAVEPOINT update_sql_cell;
2926:
2927: AMS_Utility_PVT.debug_message(l_full_name||': start');
2928:
2929: IF FND_API.to_boolean(p_init_msg_list) THEN
2930: FND_MSG_PUB.initialize;
2931: END IF;
2950: RAISE FND_API.g_exc_error;
2951: end if;
2952: --
2953:
2954: AMS_Utility_PVT.debug_message(l_full_name ||': update_cell');
2955:
2956: l_cell_rec.cell_id := l_sql_cell_rec.cell_id;
2957: l_cell_rec.sel_type := l_sql_cell_rec.sel_type;
2958: l_cell_rec.last_update_date := l_sql_cell_rec.last_update_date;
2991: RAISE FND_API.g_exc_unexpected_error;
2992: END IF;
2993:
2994: --------------------- update selection ----------------------------
2995: AMS_Utility_PVT.debug_message(l_full_name ||': update selection');
2996:
2997: OPEN c_cell;
2998: FETCH c_cell INTO l_sql_cell_rec_old;
2999: IF c_cell%NOTFOUND THEN
3018: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3019: FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3020: FND_MSG_PUB.add;
3021: END IF;
3022: AMS_Utility_PVT.debug_message(l_full_name ||': delete from ams_act_discoverer_all');
3023: RAISE FND_API.g_exc_error;
3024: END IF;
3025: ELSIF l_sql_cell_rec.discoverer_sql_id IS NOT NULL AND
3026: l_sql_cell_rec_old.discoverer_sql_id IS NULL THEN
3052: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3053: FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3054: FND_MSG_PUB.add;
3055: END IF;
3056: AMS_Utility_PVT.debug_message(l_full_name ||': delete from ams_act_discoverer_all');
3057: RAISE FND_API.g_exc_error;
3058: END IF;
3059: -- add new workbook relationship
3060: add_sel_workbook(
3123: l_list_query_rec.primary_key := 'PARTY_ID';
3124: l_list_query_rec.object_version_number := l_sql_cell_rec.list_query_version_number;
3125: l_list_query_rec.source_object_name := l_sql_cell_rec.source_object_name;
3126:
3127: AMS_UTILITY_PVT.debug_message('l_full_name: update_list_query');
3128:
3129: AMS_List_Query_PVT.Update_List_Query(
3130: p_api_version_number => l_api_version,
3131: p_init_msg_list => p_init_msg_list,
3156: p_count => x_msg_count,
3157: p_data => x_msg_data
3158: );
3159:
3160: AMS_Utility_PVT.debug_message(l_full_name ||': end');
3161:
3162: EXCEPTION
3163:
3164: WHEN FND_API.g_exc_error THEN
3240:
3241: l_cell_rec cell_rec_type;
3242:
3243: BEGIN
3244: AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
3245:
3246: x_return_status := FND_API.G_RET_STS_SUCCESS;
3247:
3248: IF p_cell_id IS NOT NULL
3252:
3253: -- Update segment size for one particular cell
3254:
3255: -- Calculate segment size
3256: AMS_Utility_PVT.Debug_Message(l_api_name||' get segment size for cell ' || l_cell_id);
3257: AMS_CELL_PVT.get_comp_segment_size(
3258: p_api_version => 1,
3259: p_init_msg_list => FND_API.g_false,
3260: p_validation_level => FND_API.g_valid_level_full,
3270: RAISE FND_API.g_exc_unexpected_error;
3271: END IF;
3272:
3273: -- Update cell record
3274: AMS_Utility_PVT.Debug_Message(l_api_name||' update_cell');
3275:
3276: UPDATE ams_cells_all_b
3277: SET object_version_number = object_version_number + 1 ,
3278: original_size = l_segment_size
3295: t_object_version_number(l_iterator) := l_object_version_number + 1;
3296:
3297:
3298: -- Calculate segment size
3299: AMS_Utility_PVT.Debug_Message(l_api_name||' get segment size for cell ' || l_cell_id);
3300: AMS_CELL_PVT.get_comp_segment_size(
3301: p_api_version => 1,
3302: p_init_msg_list => NULL,
3303: p_validation_level => NULL,
3322: END LOOP; -- end: the loop for all CELL_IDs
3323: CLOSE c_all_cell_rec;
3324:
3325: -- Do bulk update
3326: AMS_Utility_PVT.Debug_Message(l_api_name||' get segment size for cell ');
3327: FORALL I in t_cell_id.first .. t_cell_id.last
3328: UPDATE ams_cells_all_b
3329: SET object_version_number = t_object_version_number(i) ,
3330: original_size = t_segment_size(i)
3409: -- x_msg_count => l_msg_count,
3410: -- x_msg_data => l_msg_data);
3411: --
3412: -- -- Write_log ;
3413: -- Ams_Utility_Pvt.Write_Conc_log ;
3414: -- IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
3415: -- retcode :=0;
3416: -- ELSE
3417: -- retcode :=1;