PACKAGE BODY: APPS.AMW_PURGE_MVIEW_LOG

Source

1 PACKAGE BODY AMW_PURGE_MVIEW_LOG AS
2 /* $Header: amwslprb.pls 120.2 2006/01/06 17:44:14 appldev noship $ */
3 
4   FUNCTION GET_ROW_COUNT (schema_name VARCHAR2, table_name VARCHAR2)
5       RETURN NUMBER IS
6 
7     p_query  VARCHAR2(1000);
8     p_result NUMBER := 0;
9 
10   BEGIN
11 
12     -- define the Query
13     p_query := 'SELECT COUNT(1) FROM '||schema_name||'.'||table_name;
14 
15     execute immediate p_query INTO p_result;
16     commit;
17 
18     return (p_result);
19 
20 
21   END GET_ROW_COUNT;
22 
23 
24 -- disable this function due to the limited access privilege to the sys table.  bug 4883995
25  PROCEDURE PURGE_LOG(errbuf OUT NOCOPY VARCHAR2,
26                      retcode OUT NOCOPY VARCHAR2) IS
27 
28    BEGIN
29 
30    NULL;
31 
32      END PURGE_LOG;
33 
34 
35  PROCEDURE REFRESH_ALL(errbuf  OUT NOCOPY VARCHAR2,
36                       retcode OUT NOCOPY VARCHAR2,
37                       p_mview_name IN VARCHAR2)
38        IS
39 
40        -- stop using sys table due to bug 4883995
41         --CURSOR C_mview IS
42  	--  SELECT distinct mview_name, owner FROM sys.dba_mviews
43           		--WHERE mview_name LIKE 'AMW_%';
44 
45      --p_mview      dba_mviews.mview_name%type;
46      --p_owner      dba_mviews.owner%type;
47 
48    BEGIN
49 
50    IF (p_mview_name is not null) THEN
51      DBMS_MVIEW.REFRESH(p_mview_name , 'C');
52    ELSE
53      --OPEN C_mview;
54      --LOOP
55 --	FETCH C_mview  INTO p_mview, p_owner;
56 --	EXIT WHEN C_mview%NOTFOUND;
57 --        DBMS_MVIEW.REFRESH(p_owner||'.'|| p_mview, 'C');
58 --        COMMIT;
59 --    END LOOP;
60 
61    DBMS_MVIEW.REFRESH('AMW_OPINION_LOG_MV', 'C');
62    DBMS_MVIEW.REFRESH('AMW_OPINION_MV', 'C');
63 
64     END IF;
65 
66 
67     EXCEPTION
68      WHEN NO_DATA_FOUND THEN
69          fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in AMW_PURGE_MVIEW_LOG.REFRESH_ALL'
70                 || SUBSTR (SQLERRM, 1, 100), 1, 200));
71      WHEN OTHERS THEN
72          fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in AMW_PURGE_MVIEW_LOG.REFRESH_ALL'
73                 || SUBSTR (SQLERRM, 1, 100), 1, 200));
74          errbuf := SQLERRM;
75          retcode := FND_API.G_RET_STS_UNEXP_ERROR;
76 
77 
78 END  REFRESH_ALL;
79 
80 
81 END  AMW_PURGE_MVIEW_LOG;