[Home] [Help]
15: -- and after main process. 3) fixed nth row by
16: -- passing nth row instead of pct random. 4)
17: -- used bind vars in populate_using_sql.
18: -- 06-Mar-2001 choang Added workbook_owner_name so query uses
19: -- index against ams_discoverer_sql.
20: -- 18-Apr-2001 choang Added semicolon after exit for standards.
21: -- 22-Jul-2001 choang Replaced select party_id from list_entries with
22: -- select list_entry_source_system_id.
23: -- 31-Aug-2001 choang Changed logic for getting responses.
408: -- worksheet.
409: --
410: -- NOTE
411: -- Discoverer SQL statements could span across multiple
412: -- ams_discoverer_sql records. Must use combination of
413: -- owner, workbook, and worksheet to query table for
414: -- complete sql statement.
415: --
416: -- Parameter:
813: CURSOR c_workbook (p_discoverer_sql_id IN NUMBER) IS
814: SELECT workbook_name,
815: worksheet_name,
816: workbook_owner_name
817: FROM ams_discoverer_sql
818: WHERE discoverer_sql_id = p_discoverer_sql_id
819: ;
820: l_workbook_rec c_workbook%ROWTYPE;
821:
1153: CURSOR c_workbook (p_discoverer_sql_id IN NUMBER) IS
1154: SELECT workbook_name,
1155: worksheet_name,
1156: workbook_owner_name
1157: FROM ams_discoverer_sql
1158: WHERE discoverer_sql_id = p_discoverer_sql_id
1159: ;
1160: BEGIN
1161: -- Initialize API return status to SUCCESS
1197: -- from the from_and_where
1198: -- Note: SQL must not exceed 32K. Shouldn't
1199: -- have that problem because we are only
1200: -- using the from and where clauses of
1201: -- the SQL from ams_discoverer_sql.
1202: EXECUTE IMMEDIATE
1203: -- 'INSERT INTO ams_dm_target_stg ' ||
1204: 'INSERT INTO ams_dm_target_stg_gt ' ||
1205: '(arc_object_used_by, object_used_by_id, arc_object, object_id, party_id) ' ||
1338:
1339: CURSOR c_master_types (p_workbook_owner_name IN VARCHAR2, p_workbook_name IN VARCHAR2, p_worksheet_name IN VARCHAR2) IS
1340: SELECT a.source_type_code,
1341: a.source_object_name || '.' || a.source_object_pk_field
1342: FROM ams_list_src_types a , ams_discoverer_sql b
1343: WHERE a.master_source_type_flag = 'Y'
1344: AND a.enabled_flag = 'Y'
1345: AND b.workbook_owner_name = p_workbook_owner_name
1346: AND b.workbook_name = p_workbook_name
1823: l_temp_sql VARCHAR2(4000);
1824:
1825: CURSOR c_sql (p_str_num IN NUMBER) IS
1826: SELECT sql_string
1827: FROM ams_discoverer_sql
1828: WHERE workbook_owner_name = p_workbook_owner_name
1829: AND workbook_name = p_workbook_name
1830: AND worksheet_name = p_worksheet_name
1831: AND sequence_order >= p_str_num
1848: AMS_Utility_PVT.error_message ('AMS_SQL_NO_FROM', 'WORKBOOKSHEET', p_workbook_name || '.' || p_worksheet_name);
1849: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1850: END IF;
1851:
1852: -- fetch the sql string from ams_discoverer_sql
1853: -- and start from the position of FROM. append
1854: -- all subsequent strings to the out variable.
1855: OPEN c_sql (l_found_in_str);
1856: FETCH c_sql INTO l_from_sql;
3318: -- worksheet.
3319: --
3320: -- NOTE
3321: -- Discoverer SQL statements could span across multiple
3322: -- ams_discoverer_sql records. Must use combination of
3323: -- owner, workbook, and worksheet to query table for
3324: -- complete sql statement.
3325: --
3326: -- Parameter:
3744: CURSOR c_discoverer_sql (p_workbook_name IN VARCHAR2,
3745: p_worksheet_name IN VARCHAR2,
3746: p_workbook_owner_name IN VARCHAR2) IS
3747: SELECT sql_string, sequence_order
3748: FROM ams_discoverer_sql
3749: WHERE workbook_name = p_workbook_name
3750: AND worksheet_name = p_worksheet_name
3751: AND workbook_owner_name = p_workbook_owner_name
3752: ORDER BY sequence_order;