eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.GMA_AUDIT_PKG dependencies on TAB

Line 3: PROCEDURE RETRIEVE_RESULT_TABLES(AUDIT_WHERE_CLAUSE VARCHAR2,

1: PACKAGE BODY GMA_AUDIT_PKG AS
2: /* $Header: GMAAUDTB.pls 115.2 2001/12/20 01:49:29 pkm ship $ */
3: PROCEDURE RETRIEVE_RESULT_TABLES(AUDIT_WHERE_CLAUSE VARCHAR2,
4: TABLE_WHERE_CLAUSE VARCHAR2,
5: QUERY_TABLES IN OUT AUDIT_REQUIRED_TABLES_TYPE) AS
6: TYPE RETRIEVE_DATA IS REF CURSOR;
7: AUDIT_TABLE RETRIEVE_DATA; -- declare cursor variable

Line 4: TABLE_WHERE_CLAUSE VARCHAR2,

1: PACKAGE BODY GMA_AUDIT_PKG AS
2: /* $Header: GMAAUDTB.pls 115.2 2001/12/20 01:49:29 pkm ship $ */
3: PROCEDURE RETRIEVE_RESULT_TABLES(AUDIT_WHERE_CLAUSE VARCHAR2,
4: TABLE_WHERE_CLAUSE VARCHAR2,
5: QUERY_TABLES IN OUT AUDIT_REQUIRED_TABLES_TYPE) AS
6: TYPE RETRIEVE_DATA IS REF CURSOR;
7: AUDIT_TABLE RETRIEVE_DATA; -- declare cursor variable
8: TABLE_DATA RETRIEVE_DATA; -- declare cursor variable

Line 5: QUERY_TABLES IN OUT AUDIT_REQUIRED_TABLES_TYPE) AS

1: PACKAGE BODY GMA_AUDIT_PKG AS
2: /* $Header: GMAAUDTB.pls 115.2 2001/12/20 01:49:29 pkm ship $ */
3: PROCEDURE RETRIEVE_RESULT_TABLES(AUDIT_WHERE_CLAUSE VARCHAR2,
4: TABLE_WHERE_CLAUSE VARCHAR2,
5: QUERY_TABLES IN OUT AUDIT_REQUIRED_TABLES_TYPE) AS
6: TYPE RETRIEVE_DATA IS REF CURSOR;
7: AUDIT_TABLE RETRIEVE_DATA; -- declare cursor variable
8: TABLE_DATA RETRIEVE_DATA; -- declare cursor variable
9: audit_sql_stmt VARCHAR2(4000);

Line 7: AUDIT_TABLE RETRIEVE_DATA; -- declare cursor variable

3: PROCEDURE RETRIEVE_RESULT_TABLES(AUDIT_WHERE_CLAUSE VARCHAR2,
4: TABLE_WHERE_CLAUSE VARCHAR2,
5: QUERY_TABLES IN OUT AUDIT_REQUIRED_TABLES_TYPE) AS
6: TYPE RETRIEVE_DATA IS REF CURSOR;
7: AUDIT_TABLE RETRIEVE_DATA; -- declare cursor variable
8: TABLE_DATA RETRIEVE_DATA; -- declare cursor variable
9: audit_sql_stmt VARCHAR2(4000);
10: table_sql_stmt VARCHAR2(4000);
11: AUDIT_TABLE_REC AUDIT_REQUIRED_TABLE;

Line 8: TABLE_DATA RETRIEVE_DATA; -- declare cursor variable

4: TABLE_WHERE_CLAUSE VARCHAR2,
5: QUERY_TABLES IN OUT AUDIT_REQUIRED_TABLES_TYPE) AS
6: TYPE RETRIEVE_DATA IS REF CURSOR;
7: AUDIT_TABLE RETRIEVE_DATA; -- declare cursor variable
8: TABLE_DATA RETRIEVE_DATA; -- declare cursor variable
9: audit_sql_stmt VARCHAR2(4000);
10: table_sql_stmt VARCHAR2(4000);
11: AUDIT_TABLE_REC AUDIT_REQUIRED_TABLE;
12: i INTEGER := 1;

Line 10: table_sql_stmt VARCHAR2(4000);

6: TYPE RETRIEVE_DATA IS REF CURSOR;
7: AUDIT_TABLE RETRIEVE_DATA; -- declare cursor variable
8: TABLE_DATA RETRIEVE_DATA; -- declare cursor variable
9: audit_sql_stmt VARCHAR2(4000);
10: table_sql_stmt VARCHAR2(4000);
11: AUDIT_TABLE_REC AUDIT_REQUIRED_TABLE;
12: i INTEGER := 1;
13: j INTEGER := 1;
14: DATA_EXISTS_FLAG INTEGER := null;

Line 11: AUDIT_TABLE_REC AUDIT_REQUIRED_TABLE;

7: AUDIT_TABLE RETRIEVE_DATA; -- declare cursor variable
8: TABLE_DATA RETRIEVE_DATA; -- declare cursor variable
9: audit_sql_stmt VARCHAR2(4000);
10: table_sql_stmt VARCHAR2(4000);
11: AUDIT_TABLE_REC AUDIT_REQUIRED_TABLE;
12: i INTEGER := 1;
13: j INTEGER := 1;
14: DATA_EXISTS_FLAG INTEGER := null;
15: shadow_table_exists number;

Line 15: shadow_table_exists number;

11: AUDIT_TABLE_REC AUDIT_REQUIRED_TABLE;
12: i INTEGER := 1;
13: j INTEGER := 1;
14: DATA_EXISTS_FLAG INTEGER := null;
15: shadow_table_exists number;
16: BEGIN
17: audit_sql_stmt := ' select distinct FND_TABLES.TABLE_NAME TABLE_NAME ,
18: FND_TABLES.USER_TABLE_NAME USER_TABLE_NAME ,
19: FND_TABLES.TABLE_ID TABLE_ID ,

Line 17: audit_sql_stmt := ' select distinct FND_TABLES.TABLE_NAME TABLE_NAME ,

13: j INTEGER := 1;
14: DATA_EXISTS_FLAG INTEGER := null;
15: shadow_table_exists number;
16: BEGIN
17: audit_sql_stmt := ' select distinct FND_TABLES.TABLE_NAME TABLE_NAME ,
18: FND_TABLES.USER_TABLE_NAME USER_TABLE_NAME ,
19: FND_TABLES.TABLE_ID TABLE_ID ,
20: FND_TABLES.APPLICATION_ID TABLE_APPLICATION_ID
21: from FND_AUDIT_GROUPS ,

Line 18: FND_TABLES.USER_TABLE_NAME USER_TABLE_NAME ,

14: DATA_EXISTS_FLAG INTEGER := null;
15: shadow_table_exists number;
16: BEGIN
17: audit_sql_stmt := ' select distinct FND_TABLES.TABLE_NAME TABLE_NAME ,
18: FND_TABLES.USER_TABLE_NAME USER_TABLE_NAME ,
19: FND_TABLES.TABLE_ID TABLE_ID ,
20: FND_TABLES.APPLICATION_ID TABLE_APPLICATION_ID
21: from FND_AUDIT_GROUPS ,
22: GMA_AUDIT_TMPLT_DTL ,

Line 19: FND_TABLES.TABLE_ID TABLE_ID ,

15: shadow_table_exists number;
16: BEGIN
17: audit_sql_stmt := ' select distinct FND_TABLES.TABLE_NAME TABLE_NAME ,
18: FND_TABLES.USER_TABLE_NAME USER_TABLE_NAME ,
19: FND_TABLES.TABLE_ID TABLE_ID ,
20: FND_TABLES.APPLICATION_ID TABLE_APPLICATION_ID
21: from FND_AUDIT_GROUPS ,
22: GMA_AUDIT_TMPLT_DTL ,
23: FND_AUDIT_TABLES ,

Line 20: FND_TABLES.APPLICATION_ID TABLE_APPLICATION_ID

16: BEGIN
17: audit_sql_stmt := ' select distinct FND_TABLES.TABLE_NAME TABLE_NAME ,
18: FND_TABLES.USER_TABLE_NAME USER_TABLE_NAME ,
19: FND_TABLES.TABLE_ID TABLE_ID ,
20: FND_TABLES.APPLICATION_ID TABLE_APPLICATION_ID
21: from FND_AUDIT_GROUPS ,
22: GMA_AUDIT_TMPLT_DTL ,
23: FND_AUDIT_TABLES ,
24: FND_TABLES

Line 23: FND_AUDIT_TABLES ,

19: FND_TABLES.TABLE_ID TABLE_ID ,
20: FND_TABLES.APPLICATION_ID TABLE_APPLICATION_ID
21: from FND_AUDIT_GROUPS ,
22: GMA_AUDIT_TMPLT_DTL ,
23: FND_AUDIT_TABLES ,
24: FND_TABLES
25: WHERE GMA_AUDIT_TMPLT_DTL.AUDIT_GROUP_ID = FND_AUDIT_GROUPS.AUDIT_GROUP_ID
26: AND GMA_AUDIT_TMPLT_DTL.APPLICATION_ID = FND_AUDIT_GROUPS.APPLICATION_ID
27: AND FND_AUDIT_GROUPS.APPLICATION_ID = FND_AUDIT_TABLES.AUDIT_GROUP_APP_ID

Line 24: FND_TABLES

20: FND_TABLES.APPLICATION_ID TABLE_APPLICATION_ID
21: from FND_AUDIT_GROUPS ,
22: GMA_AUDIT_TMPLT_DTL ,
23: FND_AUDIT_TABLES ,
24: FND_TABLES
25: WHERE GMA_AUDIT_TMPLT_DTL.AUDIT_GROUP_ID = FND_AUDIT_GROUPS.AUDIT_GROUP_ID
26: AND GMA_AUDIT_TMPLT_DTL.APPLICATION_ID = FND_AUDIT_GROUPS.APPLICATION_ID
27: AND FND_AUDIT_GROUPS.APPLICATION_ID = FND_AUDIT_TABLES.AUDIT_GROUP_APP_ID
28: AND FND_AUDIT_GROUPS.AUDIT_GROUP_ID = FND_AUDIT_TABLES.AUDIT_GROUP_ID

Line 27: AND FND_AUDIT_GROUPS.APPLICATION_ID = FND_AUDIT_TABLES.AUDIT_GROUP_APP_ID

23: FND_AUDIT_TABLES ,
24: FND_TABLES
25: WHERE GMA_AUDIT_TMPLT_DTL.AUDIT_GROUP_ID = FND_AUDIT_GROUPS.AUDIT_GROUP_ID
26: AND GMA_AUDIT_TMPLT_DTL.APPLICATION_ID = FND_AUDIT_GROUPS.APPLICATION_ID
27: AND FND_AUDIT_GROUPS.APPLICATION_ID = FND_AUDIT_TABLES.AUDIT_GROUP_APP_ID
28: AND FND_AUDIT_GROUPS.AUDIT_GROUP_ID = FND_AUDIT_TABLES.AUDIT_GROUP_ID
29: AND FND_AUDIT_TABLES.TABLE_ID = FND_TABLES.TABLE_ID
30: AND FND_AUDIT_TABLES.TABLE_APP_ID = FND_TABLES.APPLICATION_ID
31: AND FND_AUDIT_GROUPS.STATE in (''E'',''G'',''N'')

Line 28: AND FND_AUDIT_GROUPS.AUDIT_GROUP_ID = FND_AUDIT_TABLES.AUDIT_GROUP_ID

24: FND_TABLES
25: WHERE GMA_AUDIT_TMPLT_DTL.AUDIT_GROUP_ID = FND_AUDIT_GROUPS.AUDIT_GROUP_ID
26: AND GMA_AUDIT_TMPLT_DTL.APPLICATION_ID = FND_AUDIT_GROUPS.APPLICATION_ID
27: AND FND_AUDIT_GROUPS.APPLICATION_ID = FND_AUDIT_TABLES.AUDIT_GROUP_APP_ID
28: AND FND_AUDIT_GROUPS.AUDIT_GROUP_ID = FND_AUDIT_TABLES.AUDIT_GROUP_ID
29: AND FND_AUDIT_TABLES.TABLE_ID = FND_TABLES.TABLE_ID
30: AND FND_AUDIT_TABLES.TABLE_APP_ID = FND_TABLES.APPLICATION_ID
31: AND FND_AUDIT_GROUPS.STATE in (''E'',''G'',''N'')
32: AND FND_AUDIT_TABLES.STATE in (''E'',''G'',''N'')

Line 29: AND FND_AUDIT_TABLES.TABLE_ID = FND_TABLES.TABLE_ID

25: WHERE GMA_AUDIT_TMPLT_DTL.AUDIT_GROUP_ID = FND_AUDIT_GROUPS.AUDIT_GROUP_ID
26: AND GMA_AUDIT_TMPLT_DTL.APPLICATION_ID = FND_AUDIT_GROUPS.APPLICATION_ID
27: AND FND_AUDIT_GROUPS.APPLICATION_ID = FND_AUDIT_TABLES.AUDIT_GROUP_APP_ID
28: AND FND_AUDIT_GROUPS.AUDIT_GROUP_ID = FND_AUDIT_TABLES.AUDIT_GROUP_ID
29: AND FND_AUDIT_TABLES.TABLE_ID = FND_TABLES.TABLE_ID
30: AND FND_AUDIT_TABLES.TABLE_APP_ID = FND_TABLES.APPLICATION_ID
31: AND FND_AUDIT_GROUPS.STATE in (''E'',''G'',''N'')
32: AND FND_AUDIT_TABLES.STATE in (''E'',''G'',''N'')
33: AND '|| AUDIT_WHERE_CLAUSE;

Line 30: AND FND_AUDIT_TABLES.TABLE_APP_ID = FND_TABLES.APPLICATION_ID

26: AND GMA_AUDIT_TMPLT_DTL.APPLICATION_ID = FND_AUDIT_GROUPS.APPLICATION_ID
27: AND FND_AUDIT_GROUPS.APPLICATION_ID = FND_AUDIT_TABLES.AUDIT_GROUP_APP_ID
28: AND FND_AUDIT_GROUPS.AUDIT_GROUP_ID = FND_AUDIT_TABLES.AUDIT_GROUP_ID
29: AND FND_AUDIT_TABLES.TABLE_ID = FND_TABLES.TABLE_ID
30: AND FND_AUDIT_TABLES.TABLE_APP_ID = FND_TABLES.APPLICATION_ID
31: AND FND_AUDIT_GROUPS.STATE in (''E'',''G'',''N'')
32: AND FND_AUDIT_TABLES.STATE in (''E'',''G'',''N'')
33: AND '|| AUDIT_WHERE_CLAUSE;
34: OPEN AUDIT_TABLE FOR audit_sql_stmt;

Line 32: AND FND_AUDIT_TABLES.STATE in (''E'',''G'',''N'')

28: AND FND_AUDIT_GROUPS.AUDIT_GROUP_ID = FND_AUDIT_TABLES.AUDIT_GROUP_ID
29: AND FND_AUDIT_TABLES.TABLE_ID = FND_TABLES.TABLE_ID
30: AND FND_AUDIT_TABLES.TABLE_APP_ID = FND_TABLES.APPLICATION_ID
31: AND FND_AUDIT_GROUPS.STATE in (''E'',''G'',''N'')
32: AND FND_AUDIT_TABLES.STATE in (''E'',''G'',''N'')
33: AND '|| AUDIT_WHERE_CLAUSE;
34: OPEN AUDIT_TABLE FOR audit_sql_stmt;
35: LOOP
36: FETCH AUDIT_TABLE INTO AUDIT_TABLE_REC;

Line 34: OPEN AUDIT_TABLE FOR audit_sql_stmt;

30: AND FND_AUDIT_TABLES.TABLE_APP_ID = FND_TABLES.APPLICATION_ID
31: AND FND_AUDIT_GROUPS.STATE in (''E'',''G'',''N'')
32: AND FND_AUDIT_TABLES.STATE in (''E'',''G'',''N'')
33: AND '|| AUDIT_WHERE_CLAUSE;
34: OPEN AUDIT_TABLE FOR audit_sql_stmt;
35: LOOP
36: FETCH AUDIT_TABLE INTO AUDIT_TABLE_REC;
37: EXIT WHEN AUDIT_TABLE%NOTFOUND;
38: BEGIN

Line 36: FETCH AUDIT_TABLE INTO AUDIT_TABLE_REC;

32: AND FND_AUDIT_TABLES.STATE in (''E'',''G'',''N'')
33: AND '|| AUDIT_WHERE_CLAUSE;
34: OPEN AUDIT_TABLE FOR audit_sql_stmt;
35: LOOP
36: FETCH AUDIT_TABLE INTO AUDIT_TABLE_REC;
37: EXIT WHEN AUDIT_TABLE%NOTFOUND;
38: BEGIN
39: SELECT 1 into shadow_table_exists
40: FROM tab

Line 37: EXIT WHEN AUDIT_TABLE%NOTFOUND;

33: AND '|| AUDIT_WHERE_CLAUSE;
34: OPEN AUDIT_TABLE FOR audit_sql_stmt;
35: LOOP
36: FETCH AUDIT_TABLE INTO AUDIT_TABLE_REC;
37: EXIT WHEN AUDIT_TABLE%NOTFOUND;
38: BEGIN
39: SELECT 1 into shadow_table_exists
40: FROM tab
41: where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_AC1';

Line 39: SELECT 1 into shadow_table_exists

35: LOOP
36: FETCH AUDIT_TABLE INTO AUDIT_TABLE_REC;
37: EXIT WHEN AUDIT_TABLE%NOTFOUND;
38: BEGIN
39: SELECT 1 into shadow_table_exists
40: FROM tab
41: where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_AC1';
42: IF TABLE_WHERE_CLAUSE IS NULL THEN
43: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;

Line 40: FROM tab

36: FETCH AUDIT_TABLE INTO AUDIT_TABLE_REC;
37: EXIT WHEN AUDIT_TABLE%NOTFOUND;
38: BEGIN
39: SELECT 1 into shadow_table_exists
40: FROM tab
41: where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_AC1';
42: IF TABLE_WHERE_CLAUSE IS NULL THEN
43: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
44: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;

Line 41: where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_AC1';

37: EXIT WHEN AUDIT_TABLE%NOTFOUND;
38: BEGIN
39: SELECT 1 into shadow_table_exists
40: FROM tab
41: where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_AC1';
42: IF TABLE_WHERE_CLAUSE IS NULL THEN
43: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
44: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
45: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;

Line 42: IF TABLE_WHERE_CLAUSE IS NULL THEN

38: BEGIN
39: SELECT 1 into shadow_table_exists
40: FROM tab
41: where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_AC1';
42: IF TABLE_WHERE_CLAUSE IS NULL THEN
43: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
44: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
45: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
46: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;

Line 43: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;

39: SELECT 1 into shadow_table_exists
40: FROM tab
41: where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_AC1';
42: IF TABLE_WHERE_CLAUSE IS NULL THEN
43: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
44: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
45: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
46: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
47: i:=i+1;

Line 44: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;

40: FROM tab
41: where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_AC1';
42: IF TABLE_WHERE_CLAUSE IS NULL THEN
43: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
44: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
45: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
46: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
47: i:=i+1;
48: ELSE

Line 45: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;

41: where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_AC1';
42: IF TABLE_WHERE_CLAUSE IS NULL THEN
43: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
44: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
45: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
46: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
47: i:=i+1;
48: ELSE
49: table_sql_stmt := ' SELECT 1 DATA_EXISTS_FLAG '||

Line 46: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;

42: IF TABLE_WHERE_CLAUSE IS NULL THEN
43: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
44: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
45: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
46: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
47: i:=i+1;
48: ELSE
49: table_sql_stmt := ' SELECT 1 DATA_EXISTS_FLAG '||
50: ' FROM '||substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_A ' ||

Line 49: table_sql_stmt := ' SELECT 1 DATA_EXISTS_FLAG '||

45: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
46: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
47: i:=i+1;
48: ELSE
49: table_sql_stmt := ' SELECT 1 DATA_EXISTS_FLAG '||
50: ' FROM '||substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_A ' ||
51: ' WHERE ' || TABLE_WHERE_CLAUSE;
52: OPEN TABLE_DATA FOR table_sql_stmt;
53: LOOP

Line 50: ' FROM '||substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_A ' ||

46: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
47: i:=i+1;
48: ELSE
49: table_sql_stmt := ' SELECT 1 DATA_EXISTS_FLAG '||
50: ' FROM '||substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_A ' ||
51: ' WHERE ' || TABLE_WHERE_CLAUSE;
52: OPEN TABLE_DATA FOR table_sql_stmt;
53: LOOP
54: FETCH TABLE_DATA INTO DATA_EXISTS_FLAG;

Line 51: ' WHERE ' || TABLE_WHERE_CLAUSE;

47: i:=i+1;
48: ELSE
49: table_sql_stmt := ' SELECT 1 DATA_EXISTS_FLAG '||
50: ' FROM '||substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_A ' ||
51: ' WHERE ' || TABLE_WHERE_CLAUSE;
52: OPEN TABLE_DATA FOR table_sql_stmt;
53: LOOP
54: FETCH TABLE_DATA INTO DATA_EXISTS_FLAG;
55: IF TABLE_DATA%FOUND THEN

Line 52: OPEN TABLE_DATA FOR table_sql_stmt;

48: ELSE
49: table_sql_stmt := ' SELECT 1 DATA_EXISTS_FLAG '||
50: ' FROM '||substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_A ' ||
51: ' WHERE ' || TABLE_WHERE_CLAUSE;
52: OPEN TABLE_DATA FOR table_sql_stmt;
53: LOOP
54: FETCH TABLE_DATA INTO DATA_EXISTS_FLAG;
55: IF TABLE_DATA%FOUND THEN
56: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;

Line 54: FETCH TABLE_DATA INTO DATA_EXISTS_FLAG;

50: ' FROM '||substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_A ' ||
51: ' WHERE ' || TABLE_WHERE_CLAUSE;
52: OPEN TABLE_DATA FOR table_sql_stmt;
53: LOOP
54: FETCH TABLE_DATA INTO DATA_EXISTS_FLAG;
55: IF TABLE_DATA%FOUND THEN
56: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
57: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
58: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;

Line 55: IF TABLE_DATA%FOUND THEN

51: ' WHERE ' || TABLE_WHERE_CLAUSE;
52: OPEN TABLE_DATA FOR table_sql_stmt;
53: LOOP
54: FETCH TABLE_DATA INTO DATA_EXISTS_FLAG;
55: IF TABLE_DATA%FOUND THEN
56: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
57: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
58: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
59: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;

Line 56: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;

52: OPEN TABLE_DATA FOR table_sql_stmt;
53: LOOP
54: FETCH TABLE_DATA INTO DATA_EXISTS_FLAG;
55: IF TABLE_DATA%FOUND THEN
56: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
57: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
58: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
59: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
60: i:=i+1;

Line 57: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;

53: LOOP
54: FETCH TABLE_DATA INTO DATA_EXISTS_FLAG;
55: IF TABLE_DATA%FOUND THEN
56: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
57: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
58: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
59: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
60: i:=i+1;
61: END IF;

Line 58: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;

54: FETCH TABLE_DATA INTO DATA_EXISTS_FLAG;
55: IF TABLE_DATA%FOUND THEN
56: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
57: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
58: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
59: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
60: i:=i+1;
61: END IF;
62: EXIT;

Line 59: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;

55: IF TABLE_DATA%FOUND THEN
56: QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
57: QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
58: QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
59: QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
60: i:=i+1;
61: END IF;
62: EXIT;
63: END LOOP;

Line 64: CLOSE TABLE_DATA;

60: i:=i+1;
61: END IF;
62: EXIT;
63: END LOOP;
64: CLOSE TABLE_DATA;
65: END IF;
66: EXCEPTION WHEN NO_DATA_FOUND THEN
67: null;
68: END;

Line 70: CLOSE AUDIT_TABLE;

66: EXCEPTION WHEN NO_DATA_FOUND THEN
67: null;
68: END;
69: END LOOP;
70: CLOSE AUDIT_TABLE;
71: END RETRIEVE_RESULT_TABLES;
72:
73:
74: /*********************************************************************************************

Line 71: END RETRIEVE_RESULT_TABLES;

67: null;
68: END;
69: END LOOP;
70: CLOSE AUDIT_TABLE;
71: END RETRIEVE_RESULT_TABLES;
72:
73:
74: /*********************************************************************************************
75: ******* *******

Line 80: PROCEDURE POPULATE_TAB_REP_DATA(P_SELECT_CLAUSE VARCHAR2,

76: ******* *******
77: **********************************************************************************************/
78:
79:
80: PROCEDURE POPULATE_TAB_REP_DATA(P_SELECT_CLAUSE VARCHAR2,
81: P_TABLE_NAME VARCHAR2,
82: P_USER_TABLE_NAME VARCHAR2,
83: P_WHERE_CLAUSE VARCHAR2,
84: P_APPLICATION_ID NUMBER,

Line 81: P_TABLE_NAME VARCHAR2,

77: **********************************************************************************************/
78:
79:
80: PROCEDURE POPULATE_TAB_REP_DATA(P_SELECT_CLAUSE VARCHAR2,
81: P_TABLE_NAME VARCHAR2,
82: P_USER_TABLE_NAME VARCHAR2,
83: P_WHERE_CLAUSE VARCHAR2,
84: P_APPLICATION_ID NUMBER,
85: P_TABLE_ID NUMBER,

Line 82: P_USER_TABLE_NAME VARCHAR2,

78:
79:
80: PROCEDURE POPULATE_TAB_REP_DATA(P_SELECT_CLAUSE VARCHAR2,
81: P_TABLE_NAME VARCHAR2,
82: P_USER_TABLE_NAME VARCHAR2,
83: P_WHERE_CLAUSE VARCHAR2,
84: P_APPLICATION_ID NUMBER,
85: P_TABLE_ID NUMBER,
86: P_REP_ID NUMBER) IS

Line 85: P_TABLE_ID NUMBER,

81: P_TABLE_NAME VARCHAR2,
82: P_USER_TABLE_NAME VARCHAR2,
83: P_WHERE_CLAUSE VARCHAR2,
84: P_APPLICATION_ID NUMBER,
85: P_TABLE_ID NUMBER,
86: P_REP_ID NUMBER) IS
87:
88: -- Local Variables
89:

Line 93: AUDIT_DATA_REC REPORT_TABLE;

89:
90: TYPE RETRIEVE_DATA IS REF CURSOR;
91: DISP_COL_VALUE RETRIEVE_DATA; -- declare cursor variable
92: AUDIT_DATA RETRIEVE_DATA; -- declare cursor variable
93: AUDIT_DATA_REC REPORT_TABLE;
94: PREV_AUDIT_KEY VARCHAR2(240);
95: user_column_list VARCHAR2(4000) := null;
96: pk_column_list VARCHAR2(4000) := null;
97: l_where_clause VARCHAR2(4000) := null;

Line 101: l_tab_position number;

97: l_where_clause VARCHAR2(4000) := null;
98: col_flag number :=0;
99: j NUMBER:=1;
100: i NUMBER:=1;
101: l_tab_position number;
102: l_comma_position number;
103: table_alias VARCHAR2(40);
104: parse_text1 VARCHAR2(80);
105: parse_text2 VARCHAR2(80);

Line 103: table_alias VARCHAR2(40);

99: j NUMBER:=1;
100: i NUMBER:=1;
101: l_tab_position number;
102: l_comma_position number;
103: table_alias VARCHAR2(40);
104: parse_text1 VARCHAR2(80);
105: parse_text2 VARCHAR2(80);
106: parsed_text VARCHAR2(80);
107: remaining_text VARCHAR2(2000);

Line 108: missing_base_table_flag number := 0;

104: parse_text1 VARCHAR2(80);
105: parse_text2 VARCHAR2(80);
106: parsed_text VARCHAR2(80);
107: remaining_text VARCHAR2(2000);
108: missing_base_table_flag number := 0;
109: l_cursor_id INTEGER;
110: l_dummy INTEGER;
111: id VARCHAR2(2000);
112: disp_col_val VARCHAR2(2000);

Line 128: AND TABLE_ID = P_TABLE_ID;

124: FROM_CLAUSE,
125: WHERE_CLAUSE
126: FROM GMA_AUDIT_DISP_COLS
127: WHERE APPLICATION_ID = P_APPLICATION_ID
128: AND TABLE_ID = P_TABLE_ID;
129:
130: User_key_columns_rec GET_USER_KEY_COLUMNS%ROWTYPE;
131:
132: CURSOR GET_SYSTEM_KEY_COLS IS

Line 138: AND pks.table_id = keycols.table_id

134: FROM FND_COLUMNS fnd_cols,
135: FND_PRIMARY_KEYS pks,
136: FND_PRIMARY_KEY_COLUMNS keycols
137: WHERE pks.application_id = keycols.application_id
138: AND pks.table_id = keycols.table_id
139: AND pks.primary_key_id = keycols.primary_key_id
140: AND keycols.application_id = fnd_cols.application_id
141: AND keycols.table_id = fnd_cols.table_id
142: AND keycols.column_id = fnd_cols.column_id

Line 141: AND keycols.table_id = fnd_cols.table_id

137: WHERE pks.application_id = keycols.application_id
138: AND pks.table_id = keycols.table_id
139: AND pks.primary_key_id = keycols.primary_key_id
140: AND keycols.application_id = fnd_cols.application_id
141: AND keycols.table_id = fnd_cols.table_id
142: AND keycols.column_id = fnd_cols.column_id
143: AND pks.table_id = P_TABLE_ID
144: AND pks.application_id = P_APPLICATION_ID
145: AND pks.audit_key_flag = 'Y';

Line 143: AND pks.table_id = P_TABLE_ID

139: AND pks.primary_key_id = keycols.primary_key_id
140: AND keycols.application_id = fnd_cols.application_id
141: AND keycols.table_id = fnd_cols.table_id
142: AND keycols.column_id = fnd_cols.column_id
143: AND pks.table_id = P_TABLE_ID
144: AND pks.application_id = P_APPLICATION_ID
145: AND pks.audit_key_flag = 'Y';
146:
147: SYSTEM_KEY_COLS_REC GET_SYSTEM_KEY_COLS%ROWTYPE;

Line 167: IF upper(parse_text1) = upper(p_table_name) THEN

163: remaining_text := null;
164: END IF;
165: parse_text2 := rtrim(ltrim(substrb(parse_text1,1,instrb(parse_text1,' ',1,1))));
166: IF parse_text2 is null THEN
167: IF upper(parse_text1) = upper(p_table_name) THEN
168: table_alias := parse_text1;
169: EXIT;
170: END IF;
171: ELSE

Line 168: table_alias := parse_text1;

164: END IF;
165: parse_text2 := rtrim(ltrim(substrb(parse_text1,1,instrb(parse_text1,' ',1,1))));
166: IF parse_text2 is null THEN
167: IF upper(parse_text1) = upper(p_table_name) THEN
168: table_alias := parse_text1;
169: EXIT;
170: END IF;
171: ELSE
172: IF upper(parse_text2) = upper(p_table_name) THEN

Line 172: IF upper(parse_text2) = upper(p_table_name) THEN

168: table_alias := parse_text1;
169: EXIT;
170: END IF;
171: ELSE
172: IF upper(parse_text2) = upper(p_table_name) THEN
173: table_alias := rtrim(ltrim(substrb(parse_text1,instrb(parse_text1,' ',1,1))));
174: EXIT;
175: END IF;
176: END IF ;

Line 173: table_alias := rtrim(ltrim(substrb(parse_text1,instrb(parse_text1,' ',1,1))));

169: EXIT;
170: END IF;
171: ELSE
172: IF upper(parse_text2) = upper(p_table_name) THEN
173: table_alias := rtrim(ltrim(substrb(parse_text1,instrb(parse_text1,' ',1,1))));
174: EXIT;
175: END IF;
176: END IF ;
177: IF remaining_text IS NULL THEN

Line 178: missing_base_table_flag := 1;

174: EXIT;
175: END IF;
176: END IF ;
177: IF remaining_text IS NULL THEN
178: missing_base_table_flag := 1;
179: table_alias := null;
180: EXIT;
181: END IF;
182: END LOOP;

Line 179: table_alias := null;

175: END IF;
176: END IF ;
177: IF remaining_text IS NULL THEN
178: missing_base_table_flag := 1;
179: table_alias := null;
180: EXIT;
181: END IF;
182: END LOOP;
183: if table_alias IS NOT NULL THEN

Line 183: if table_alias IS NOT NULL THEN

179: table_alias := null;
180: EXIT;
181: END IF;
182: END LOOP;
183: if table_alias IS NOT NULL THEN
184: table_alias := table_alias || '.';
185: ELSE
186: missing_base_table_flag := 1;
187: table_alias := p_table_name || '.';

Line 184: table_alias := table_alias || '.';

180: EXIT;
181: END IF;
182: END LOOP;
183: if table_alias IS NOT NULL THEN
184: table_alias := table_alias || '.';
185: ELSE
186: missing_base_table_flag := 1;
187: table_alias := p_table_name || '.';
188: end if;

Line 186: missing_base_table_flag := 1;

182: END LOOP;
183: if table_alias IS NOT NULL THEN
184: table_alias := table_alias || '.';
185: ELSE
186: missing_base_table_flag := 1;
187: table_alias := p_table_name || '.';
188: end if;
189: ELSE
190: -- missing_base_table_flag := 1;

Line 187: table_alias := p_table_name || '.';

183: if table_alias IS NOT NULL THEN
184: table_alias := table_alias || '.';
185: ELSE
186: missing_base_table_flag := 1;
187: table_alias := p_table_name || '.';
188: end if;
189: ELSE
190: -- missing_base_table_flag := 1;
191: table_alias := p_table_name || '.';

Line 190: -- missing_base_table_flag := 1;

186: missing_base_table_flag := 1;
187: table_alias := p_table_name || '.';
188: end if;
189: ELSE
190: -- missing_base_table_flag := 1;
191: table_alias := p_table_name || '.';
192: END IF;
193: OPEN GET_SYSTEM_KEY_COLS;
194: LOOP

Line 191: table_alias := p_table_name || '.';

187: table_alias := p_table_name || '.';
188: end if;
189: ELSE
190: -- missing_base_table_flag := 1;
191: table_alias := p_table_name || '.';
192: END IF;
193: OPEN GET_SYSTEM_KEY_COLS;
194: LOOP
195: FETCH GET_SYSTEM_KEY_COLS into SYSTEM_KEY_COLS_REC;

Line 198: pk_column_list := table_alias||SYSTEM_KEY_COLS_REC.column_name;

194: LOOP
195: FETCH GET_SYSTEM_KEY_COLS into SYSTEM_KEY_COLS_REC;
196: EXIT WHEN GET_SYSTEM_KEY_COLS%NOTFOUND;
197: IF pk_column_list IS NULL THEN
198: pk_column_list := table_alias||SYSTEM_KEY_COLS_REC.column_name;
199: l_where_clause := SYSTEM_KEY_COLS_REC.column_name;
200: ELSE
201: pk_column_list := pk_column_list||','||table_alias||SYSTEM_KEY_COLS_REC.column_name;
202: l_where_clause := l_where_clause ||','||SYSTEM_KEY_COLS_REC.column_name;

Line 201: pk_column_list := pk_column_list||','||table_alias||SYSTEM_KEY_COLS_REC.column_name;

197: IF pk_column_list IS NULL THEN
198: pk_column_list := table_alias||SYSTEM_KEY_COLS_REC.column_name;
199: l_where_clause := SYSTEM_KEY_COLS_REC.column_name;
200: ELSE
201: pk_column_list := pk_column_list||','||table_alias||SYSTEM_KEY_COLS_REC.column_name;
202: l_where_clause := l_where_clause ||','||SYSTEM_KEY_COLS_REC.column_name;
203: END IF;
204: END LOOP;
205: pk_column_list := ltrim(replace(pk_column_list,',','||'||''''||','||''''||'||'));

Line 207: IF missing_base_table_flag = 0 AND User_key_columns_rec.select_clause IS NOT NULL THEN

203: END IF;
204: END LOOP;
205: pk_column_list := ltrim(replace(pk_column_list,',','||'||''''||','||''''||'||'));
206: l_where_clause := ltrim(replace(l_where_clause,',','||'||''''||','||''''||'||'));
207: IF missing_base_table_flag = 0 AND User_key_columns_rec.select_clause IS NOT NULL THEN
208: user_column_list := replace(User_key_columns_rec.select_clause,',','||'||''''||','||''''||'||');
209: v_select_stmt := ' SELECT '||pk_column_list || ' ID, '||
210: user_column_list || ' disp_val ' ;
211: IF User_key_columns_rec.from_clause is not null THEN

Line 214: v_select_stmt := v_select_stmt || ' FROM '|| p_table_name;

210: user_column_list || ' disp_val ' ;
211: IF User_key_columns_rec.from_clause is not null THEN
212: v_select_stmt := v_select_stmt || ' FROM ' ||User_key_columns_rec.FROM_CLAUSE;
213: ELSE
214: v_select_stmt := v_select_stmt || ' FROM '|| p_table_name;
215: END IF;
216: IF User_key_columns_rec.where_clause IS not NULL THEN
217: v_select_stmt := v_select_stmt ||
218: ' WHERE '||User_key_columns_rec.where_clause;

Line 226: ' FROM '|| p_table_name ||

222: END IF;
223: ELSE
224: v_select_stmt := ' SELECT '||pk_column_list || ' ID, '||
225: pk_column_list || ' disp_val ' ||
226: ' FROM '|| p_table_name ||
227: ' WHERE 1=1 ';
228: END IF;
229: /******
230: Retrieve Data from AC1 View then lookup for display column value

Line 235: || ' FROM '||substrb(P_TABLE_NAME,1,26)||'_AC1'

231: ******/
232:
233: data_select_stmt := ' SELECT '||l_where_clause || ' AUDIT_KEY,AUDIT_TIMESTAMP,AUDIT_TRANSACTION_TYPE,AUDIT_USER_NAME,'
234: || P_SELECT_CLAUSE
235: || ' FROM '||substrb(P_TABLE_NAME,1,26)||'_AC1'
236: || ' WHERE '|| P_WHERE_CLAUSE
237: || ' order by '||l_where_clause;
238: OPEN AUDIT_DATA FOR data_select_stmt;
239: LOOP

Line 254: ,TABLE_NAME

250: CLOSE DISP_COL_VALUE;
251: PREV_AUDIT_KEY := AUDIT_DATA_REC.AUDIT_KEY ;
252: END IF;
253: INSERT INTO GMA_AUDIT_REP_DTL(REP_ID
254: ,TABLE_NAME
255: ,AUDIT_KEY
256: ,AUDIT_TIMESTAMP
257: ,AUDIT_TRANSACTION_TYPE
258: ,AUDIT_USER_NAME

Line 267: ,P_TABLE_NAME

263: ,COLUMN5_VALUE
264: ,ROW_DISP_COL)
265: VALUES
266: (P_REP_ID
267: ,P_TABLE_NAME
268: ,substrb(AUDIT_DATA_REC.AUDIT_KEY,1,240)
269: ,AUDIT_DATA_REC.AUDIT_TIMESTAMP
270: ,AUDIT_DATA_REC.AUDIT_TRANSACTION_TYPE
271: ,AUDIT_DATA_REC.AUDIT_USER_NAME