[Home] [Help]
176:
177: -- get list of tables which can be used for expression building
178: CURSOR osc_elements IS
179: SELECT user_name,schema, name, alias, object_id
180: FROM cn_objects_all
181: WHERE calc_eligible_flag = 'Y'
182: AND object_type in ('TBL', 'VIEW')
183: AND user_name is not null
184: AND object_id < 0
188:
189: -- get list of columns which can be used in expression building
190: CURSOR table_columns(p_table_id number) is
191: SELECT user_name, name ,object_id ,alias
192: FROM cn_objects_all
193: WHERE table_id = p_table_id
194: AND calc_formula_flag = 'Y'
195: AND object_type = 'COL'
196: AND org_id = g_org_id
198:
199: --get alias of commlines table
200: CURSOR comm_line_alias is
201: SELECT alias
202: FROM cn_objects_all
203: WHERE name = 'CN_COMMISSION_LINES'
204: AND object_type in ('TBL', 'VIEW')
205: AND org_id = g_org_id;
206:
817: BEGIN
818: -- check whether formula package exist in cn_objects
819: SELECT COUNT(*)
820: INTO dummy
821: FROM cn_objects_all
822: WHERE name = x_name
823: AND object_type = x_object_type
824: AND org_id = g_org_id;
825:
840: x_object_value => NULL );
841:
842: ELSIF dummy = 1 THEN
843: SELECT object_id INTO x_object_id
844: FROM cn_objects_all
845: WHERE name = x_name
846: AND object_type = x_object_type
847: AND org_id = g_org_id;
848: END IF;
886: RETURN VARCHAR2 IS
887: x_table_alias VARCHAR2(30);
888: CURSOR p_table_alias_csr(l_table_name VARCHAR2) IS
889: SELECT alias
890: FROM cn_objects_all
891: WHERE name = Upper(l_table_name)
892: AND object_type = 'TBL'
893: AND org_id = g_org_id;
894: BEGIN
1327:
1328: CURSOR l_all_column_pairs_csr(l_calc_ext_table_id NUMBER)IS
1329: SELECT lower(obj1.name) internal_column_name, lower(obj2.name) external_column_name
1330: FROM cn_calc_ext_tbl_dtls_all detail,
1331: cn_objects_all obj1,
1332: cn_objects_all obj2
1333: WHERE detail.calc_ext_table_id = l_calc_ext_table_id
1334: AND obj1.object_id = detail.internal_column_id
1335: AND obj1.org_id = detail.org_id
1328: CURSOR l_all_column_pairs_csr(l_calc_ext_table_id NUMBER)IS
1329: SELECT lower(obj1.name) internal_column_name, lower(obj2.name) external_column_name
1330: FROM cn_calc_ext_tbl_dtls_all detail,
1331: cn_objects_all obj1,
1332: cn_objects_all obj2
1333: WHERE detail.calc_ext_table_id = l_calc_ext_table_id
1334: AND obj1.object_id = detail.internal_column_id
1335: AND obj1.org_id = detail.org_id
1336: AND obj2.object_id = detail.external_column_id
1341: lower(obj.name) internal_table_name,
1342: trim( both ' ' FROM lower(obj.alias) ) internal_table_alias,
1343: lower(l_table_name) external_table_name,
1344: lower(l_table_alias) external_table_alias
1345: FROM cn_objects_all obj,
1346: cn_calc_ext_tables_all map
1347: WHERE lower(map.alias) = l_table_alias
1348: AND map.org_id = g_org_id
1349: AND obj.object_id = map.internal_table_id
1398:
1399: CURSOR l_all_columns_csr IS
1400: SELECT col.table_id, lower(col.name) column_name
1401: FROM cn_calc_ext_tables_all map,
1402: cn_objects_all col
1403: WHERE map.alias = l_table_alias
1404: AND map.org_id = g_org_id
1405: AND col.table_id = map.external_table_id
1406: AND col.org_id = map.org_id
3896: l_perf_sql_where varchar2(4000) := 'WHERE 1=1 ';
3897:
3898: CURSOR l_all_columns_csr (l_table_id NUMBER ) IS
3899: SELECT lower(name) column_name, data_type
3900: FROM cn_objects_all
3901: WHERE table_id = l_table_id
3902: AND org_id = g_org_id
3903: AND object_type = 'COL'
3904: AND primary_key = 'Y'
5072: end if;
5073:
5074: SELECT co.object_id
5075: INTO l_pkg_object_id
5076: FROM cn_objects_all co
5077: WHERE co.name = l_formula_name
5078: AND co.object_type = 'PKS'
5079: AND co.org_id = g_org_id;
5080:
5095: sqlstring := empty_sqlstring;
5096:
5097: SELECT co.object_id
5098: INTO l_pkg_object_id
5099: FROM cn_objects_all co
5100: WHERE co.name = l_formula_name
5101: AND co.object_type = 'PKB'
5102: AND co.org_id = g_org_id;
5103: