APPS.SHP_GEN_UNIQUE_PKG dependencies on DBMS_SQL

Line 19: -- uses DBMS_SQL package to create and execute cursor for given query

15: -- prod_name product name to send message for
16: -- msg_name message to print if duplicate found
17: --
18: -- Notes
19: -- uses DBMS_SQL package to create and execute cursor for given query
20:
21: PROCEDURE Gen_Check_Unique(query_text VARCHAR2,
22: prod_name VARCHAR2,
23: msg_name VARCHAR2) IS

Line 27: rec_cursor := dbms_sql.open_cursor;

23: msg_name VARCHAR2) IS
24: rec_cursor INTEGER;
25: any_found INTEGER;
26: BEGIN
27: rec_cursor := dbms_sql.open_cursor;
28: dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);
29: any_found := dbms_sql.execute_and_fetch(rec_cursor);
30: IF (any_found > 0) THEN
31: FND_MESSAGE.SET_NAME(prod_name,msg_name);

Line 28: dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);

24: rec_cursor INTEGER;
25: any_found INTEGER;
26: BEGIN
27: rec_cursor := dbms_sql.open_cursor;
28: dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);
29: any_found := dbms_sql.execute_and_fetch(rec_cursor);
30: IF (any_found > 0) THEN
31: FND_MESSAGE.SET_NAME(prod_name,msg_name);
32: APP_EXCEPTION.RAISE_EXCEPTION;

Line 29: any_found := dbms_sql.execute_and_fetch(rec_cursor);

25: any_found INTEGER;
26: BEGIN
27: rec_cursor := dbms_sql.open_cursor;
28: dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);
29: any_found := dbms_sql.execute_and_fetch(rec_cursor);
30: IF (any_found > 0) THEN
31: FND_MESSAGE.SET_NAME(prod_name,msg_name);
32: APP_EXCEPTION.RAISE_EXCEPTION;
33: END IF;

Line 43: rec_cursor := dbms_sql.open_cursor;

39: row_processed INTEGER;
40: error_out EXCEPTION;
41: date_in_table DATE;
42: BEGIN
43: rec_cursor := dbms_sql.open_cursor;
44: dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);
45: dbms_sql.define_column(rec_cursor, 1, date_in_table);
46: row_processed := dbms_sql.execute(rec_cursor);
47:

Line 44: dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);

40: error_out EXCEPTION;
41: date_in_table DATE;
42: BEGIN
43: rec_cursor := dbms_sql.open_cursor;
44: dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);
45: dbms_sql.define_column(rec_cursor, 1, date_in_table);
46: row_processed := dbms_sql.execute(rec_cursor);
47:
48: IF ( dbms_sql.fetch_rows(rec_cursor) > 0) THEN

Line 45: dbms_sql.define_column(rec_cursor, 1, date_in_table);

41: date_in_table DATE;
42: BEGIN
43: rec_cursor := dbms_sql.open_cursor;
44: dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);
45: dbms_sql.define_column(rec_cursor, 1, date_in_table);
46: row_processed := dbms_sql.execute(rec_cursor);
47:
48: IF ( dbms_sql.fetch_rows(rec_cursor) > 0) THEN
49: dbms_sql.column_value( rec_cursor, 1, date_in_table);

Line 46: row_processed := dbms_sql.execute(rec_cursor);

42: BEGIN
43: rec_cursor := dbms_sql.open_cursor;
44: dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);
45: dbms_sql.define_column(rec_cursor, 1, date_in_table);
46: row_processed := dbms_sql.execute(rec_cursor);
47:
48: IF ( dbms_sql.fetch_rows(rec_cursor) > 0) THEN
49: dbms_sql.column_value( rec_cursor, 1, date_in_table);
50: ELSE

Line 48: IF ( dbms_sql.fetch_rows(rec_cursor) > 0) THEN

44: dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);
45: dbms_sql.define_column(rec_cursor, 1, date_in_table);
46: row_processed := dbms_sql.execute(rec_cursor);
47:
48: IF ( dbms_sql.fetch_rows(rec_cursor) > 0) THEN
49: dbms_sql.column_value( rec_cursor, 1, date_in_table);
50: ELSE
51: RAISE error_out;
52: END IF;

Line 49: dbms_sql.column_value( rec_cursor, 1, date_in_table);

45: dbms_sql.define_column(rec_cursor, 1, date_in_table);
46: row_processed := dbms_sql.execute(rec_cursor);
47:
48: IF ( dbms_sql.fetch_rows(rec_cursor) > 0) THEN
49: dbms_sql.column_value( rec_cursor, 1, date_in_table);
50: ELSE
51: RAISE error_out;
52: END IF;
53:

Line 54: dbms_sql.close_cursor(rec_cursor);

50: ELSE
51: RAISE error_out;
52: END IF;
53:
54: dbms_sql.close_cursor(rec_cursor);
55:
56: date_fetched := date_in_table;
57:
58: EXCEPTION

Line 60: dbms_sql.close_cursor(rec_cursor);

56: date_fetched := date_in_table;
57:
58: EXCEPTION
59: WHEN OTHERS THEN
60: dbms_sql.close_cursor(rec_cursor);
61: FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
62: FND_MESSAGE.Set_Token('PACKAGE','SHP_GEN_UNIQUE_PKG');
63: FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
64: FND_MESSAGE.Set_Token('ORA_TEXT',query_text);