[Home] [Help]
235: l_rep_where_clause_main pqh_table_route.where_clause%TYPE;
236: l_select_stmt t_where_clause_typ;
237: l_tot_txn_columns NUMBER;
238: l_tot_txn_rows NUMBER;
239: l_all_txn_rows_array DBMS_SQL.VARCHAR2_TABLE;
240: l_txn_row_cnt NUMBER := 0;
241: l_ordered_txn_row DBMS_SQL.VARCHAR2_TABLE;
242: l_tot_shd_columns NUMBER;
243: l_tot_shd_rows NUMBER;
237: l_tot_txn_columns NUMBER;
238: l_tot_txn_rows NUMBER;
239: l_all_txn_rows_array DBMS_SQL.VARCHAR2_TABLE;
240: l_txn_row_cnt NUMBER := 0;
241: l_ordered_txn_row DBMS_SQL.VARCHAR2_TABLE;
242: l_tot_shd_columns NUMBER;
243: l_tot_shd_rows NUMBER;
244: l_all_shd_rows_array DBMS_SQL.VARCHAR2_TABLE;
245: l_tot_main_columns NUMBER;
240: l_txn_row_cnt NUMBER := 0;
241: l_ordered_txn_row DBMS_SQL.VARCHAR2_TABLE;
242: l_tot_shd_columns NUMBER;
243: l_tot_shd_rows NUMBER;
244: l_all_shd_rows_array DBMS_SQL.VARCHAR2_TABLE;
245: l_tot_main_columns NUMBER;
246: l_tot_main_rows NUMBER;
247: l_all_main_rows_array DBMS_SQL.VARCHAR2_TABLE;
248: l_legislation_code varchar2(30)
243: l_tot_shd_rows NUMBER;
244: l_all_shd_rows_array DBMS_SQL.VARCHAR2_TABLE;
245: l_tot_main_columns NUMBER;
246: l_tot_main_rows NUMBER;
247: l_all_main_rows_array DBMS_SQL.VARCHAR2_TABLE;
248: l_legislation_code varchar2(30)
249: := get_legislation_code(fnd_profile.value('PER_BUSINESS_GROUP_ID'));
250: type t_string is table of pqh_attributes_tl.attribute_name%type
251: index by binary_integer;
1195: p_from_clause IN pqh_table_route.from_clause%TYPE,
1196: p_where_clause IN pqh_table_route.where_clause%TYPE,
1197: p_total_columns IN NUMBER,
1198: p_total_rows OUT NOCOPY NUMBER,
1199: p_all_txn_rows OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE )
1200: IS
1201: /*
1202: This procedure will get all rows of the table and populate the array
1203: The OUT array p_all_txn_rows will have the following data .
1211: --
1212: l_proc varchar2(72) := g_package||'get_all_rows';
1213: c number; -- cursor handle
1214: d number; -- no of rows fetched by cursor
1215: v_tab dbMS_SQL.VARCHAR2_TABLE; -- temp array to hold elements
1216: l_qry_string VARCHAR2(32000); -- to cinstruct the qry string
1217: indx number := 1; -- start index of the array populated
1218: l_tot_rows_fetch number := 0; -- total of all array elements
1219: l_tot_cnt number :=1; -- index of the OUT array p_all_txn_rows
1273: hr_utility.set_location(substr(l_qry_string,2051,50), 10);
1274:
1275:
1276: -- open the cursor
1277: c := dbms_sql.open_cursor;
1278:
1279: hr_utility.set_location('Opened Cursor : '||c, 10);
1280:
1281:
1279: hr_utility.set_location('Opened Cursor : '||c, 10);
1280:
1281:
1282: -- parse the select stmt for errors
1283: dbms_sql.parse(c,l_qry_string, dbms_sql.native);
1284:
1285: hr_utility.set_location('Parsed Query String :', 15);
1286:
1287: -- for ALL COLUMNS we will LOOP one column at a time and build the
1292:
1293: -- hr_utility.set_location('Inside first Loop : ', 16);
1294:
1295: -- define v_tab array to hold all the column values
1296: dbms_sql.define_array(c,j,v_tab,1, indx);
1297:
1298: -- hr_utility.set_location('Defining Array : ', 18);
1299:
1300: -- execute the dynamic select for all rows
1298: -- hr_utility.set_location('Defining Array : ', 18);
1299:
1300: -- execute the dynamic select for all rows
1301: -- this will fetch the j th column values in the v_tab array
1302: d := dbms_sql.execute(c);
1303:
1304: -- hr_utility.set_location('Sql Execute : '||d, 20);
1305:
1306: LOOP
1303:
1304: -- hr_utility.set_location('Sql Execute : '||d, 20);
1305:
1306: LOOP
1307: d := dbms_sql.fetch_rows(c);
1308:
1309: -- hr_utility.set_location('Fetched rows : '||d, 25);
1310:
1311: EXIT WHEN d <> 1;
1310:
1311: EXIT WHEN d <> 1;
1312: l_tot_rows_fetch := l_tot_rows_fetch + 1;
1313: -- associate the fetch value with v_tab
1314: dbms_sql.column_value(c, j, v_tab);
1315: END LOOP;
1316:
1317: -- hr_utility.set_location('After exec Loop : ', 30);
1318:
1345: -- dbms_output.put_line('Total Rows : '||tot_rows);
1346: hr_utility.set_location('Total Rows :'||p_total_rows, 50);
1347:
1348: -- close the cursor
1349: dbms_sql.close_cursor(c);
1350: hr_utility.set_location('Leaving:'||l_proc, 1000);
1351:
1352: EXCEPTION
1353: WHEN OTHERS THEN