[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
1328:
1329: CURSOR l_all_column_pairs_csr(l_calc_ext_table_id NUMBER)IS
1330: SELECT lower(obj1.name) internal_column_name, lower(obj2.name) external_column_name
1331: FROM cn_calc_ext_tbl_dtls_all detail,
1332: cn_objects_all obj1,
1333: cn_objects_all obj2
1334: WHERE detail.calc_ext_table_id = l_calc_ext_table_id
1335: AND obj1.object_id = detail.internal_column_id
1336: AND obj1.org_id = detail.org_id
1329: CURSOR l_all_column_pairs_csr(l_calc_ext_table_id NUMBER)IS
1330: SELECT lower(obj1.name) internal_column_name, lower(obj2.name) external_column_name
1331: FROM cn_calc_ext_tbl_dtls_all detail,
1332: cn_objects_all obj1,
1333: cn_objects_all obj2
1334: WHERE detail.calc_ext_table_id = l_calc_ext_table_id
1335: AND obj1.object_id = detail.internal_column_id
1336: AND obj1.org_id = detail.org_id
1337: AND obj2.object_id = detail.external_column_id
1342: lower(obj.name) internal_table_name,
1343: trim( both ' ' FROM lower(obj.alias) ) internal_table_alias,
1344: lower(l_table_name) external_table_name,
1345: lower(l_table_alias) external_table_alias
1346: FROM cn_objects_all obj,
1347: cn_calc_ext_tables_all map
1348: WHERE lower(map.alias) = l_table_alias
1349: AND map.org_id = g_org_id
1350: AND obj.object_id = map.internal_table_id
1403:
1404: CURSOR l_all_columns_csr IS
1405: SELECT col.table_id, lower(col.name) column_name
1406: FROM cn_calc_ext_tables_all map,
1407: cn_objects_all col
1408: WHERE map.alias = l_table_alias
1409: AND map.org_id = g_org_id
1410: AND col.table_id = map.external_table_id
1411: AND col.org_id = map.org_id
3909: l_perf_sql_where varchar2(4000) := 'WHERE 1=1 ';
3910:
3911: CURSOR l_all_columns_csr (l_table_id NUMBER ) IS
3912: SELECT lower(name) column_name, data_type
3913: FROM cn_objects_all
3914: WHERE table_id = l_table_id
3915: AND org_id = g_org_id
3916: AND object_type = 'COL'
3917: AND primary_key = 'Y'
5058: l_applsys_schema VARCHAR2(30);
5059:
5060: CURSOR pkg_object_id (c_formula_name VARCHAR2, c_obj_type VARCHAR2, c_org_id NUMBER) IS
5061: SELECT co.object_id, UPPER(co.name) name
5062: FROM cn_objects_all co
5063: WHERE co.name = c_formula_name
5064: AND co.object_type = c_obj_type
5065: AND co.org_id = c_org_id;
5066: