1 PACKAGE IEC_SQL_LOGGER_PVT AUTHID CURRENT_USER AS
2 /* $Header: IECVLGRS.pls 115.8 2003/08/22 20:42:40 hhuang ship $ */
3
4 -- IEO Logging Constants
5 G_TL_NONE CONSTANT NUMBER := 0;
6 G_TL_FATAL CONSTANT NUMBER := 1;
7 G_TL_ERROR CONSTANT NUMBER := 2;
8 G_TL_WARNING CONSTANT NUMBER := 3;
9 G_TL_INFO CONSTANT NUMBER := 4;
10 G_TL_CALL_LEVEL CONSTANT NUMBER := 50;
11 G_TL_TXN CONSTANT NUMBER := 51;
12 G_TL_DEBUG CONSTANT NUMBER := 52;
13 G_TL_ALL CONSTANT NUMBER := 99;
14
15 -- IEO Alert Constants
16 G_ALERT_NONE CONSTANT NUMBER := 1;
17 G_ALERT_SET CONSTANT NUMBER := 4;
18 G_ALERT_CLEAR CONSTANT NUMBER := 16;
19 G_ALERT_CLEAR_ALL CONSTANT NUMBER := 64;
20
21 -- Generic Exception
22 G_SVR_EXCEPTION EXCEPTION;
23 G_SVR_WARNING EXCEPTION;
24 G_SVR_SUCCESS EXCEPTION;
25
26 -- Return Codes
27 G_RETURN_EXCEPTION CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
28 G_RETURN_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
29 G_RETURN_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
30
31 -- Generic table defs used for logging.
32 TYPE VARCHAR2_TABLE is TABLE of VARCHAR2(4000) index by BINARY_INTEGER;
33 TYPE NUMBER_TABLE is TABLE of NUMBER(15) index by BINARY_INTEGER;
34 TYPE DATE_TABLE is TABLE of DATE index by BINARY_INTEGER;
35
36 -- Used for insert into log, descriptions and params table.
37 L_DESC_B_INS_STMT VARCHAR2(4000);
38 L_REC_B_INS_STMT VARCHAR2(4000);
39 L_PARMS_B_INS_STMT VARCHAR2(4000);
40
41 -- Cache seq numbers here.
42 G_SEQ_NUM NUMBER(10);
43 G_FETCH_SEQ_NUM NUMBER(10);
44
45 -- Get the next record id
46 FUNCTION GET_NEXT_RECORD_ID RETURN NUMBER;
47
48 -- Get the source id for this source.
49 PROCEDURE GET_SOURCE_ID
50 ( P_FACILITY_GUID IN VARCHAR2
51 , P_APP_ID IN VARCHAR2
52 , P_FACILITY_NAME_MSG_NAME IN VARCHAR2
53 , P_FACILITY_INSTANCE IN VARCHAR2
54 , P_FACILITY_INSTANCE_UID IN VARCHAR2
55 , P_IP_ADDRESS IN VARCHAR2
56 , P_HOSTNAME IN VARCHAR2
57 , P_OS_USER_NAME IN VARCHAR2
58 , P_LOG_LEVEL IN NUMBER
59 , X_SOURCE_ID IN OUT NOCOPY NUMBER
60 );
61
62 -- Log a message
63 PROCEDURE LOG
64 ( P_SOURCE_ID IN NUMBER
65 , P_LOG_LEVEL IN NUMBER
66 , P_TIMESTAMP IN DATE
67 , P_TIMESTAMP_MILLI IN NUMBER
68 , P_ACTION_ID IN NUMBER
69 , P_SEVERITY_ID IN NUMBER
70 , P_TITLE_MSG_NAME IN VARCHAR2
71 , P_TITLE_MSG_APP_NAME IN VARCHAR2
72 , P_MESSAGE IN VARCHAR2
73 , X_RECORD_ID IN OUT NOCOPY NUMBER
74 );
75
76 -- This uses the format 'yyyy-MM-DD HH:MI:SS'
77 -- Log a message
78 PROCEDURE LOG
79 ( P_SOURCE_ID IN NUMBER
80 , P_LOG_LEVEL IN NUMBER
81 , P_TIMESTAMP IN VARCHAR2
82 , P_TIMESTAMP_MILLI IN NUMBER
83 , P_ACTION_ID IN NUMBER
84 , P_SEVERITY_ID IN NUMBER
85 , P_TITLE_MSG_NAME IN VARCHAR2
86 , P_TITLE_MSG_APP_NAME IN VARCHAR2
87 , P_MESSAGE IN VARCHAR2
88 , X_RECORD_ID IN OUT NOCOPY NUMBER
89 );
90
91 -- Log the corresponding description
92 PROCEDURE LOG_DESCRIPTION
93 ( P_RECORD_ID IN NUMBER
94 , P_DESC_POS IN NUMBER
95 , P_DESC_MSG_NAME IN VARCHAR2
96 , P_DESC_MSG_APP_NAME IN VARCHAR2
97 );
98
99 -- Use this for multiple descriptions
100 PROCEDURE LOG_DESCRIPTION
101 ( P_RECORD_ID IN NUMBER
102 , P_DESC_MSG_NAME IN VARCHAR2_TABLE
103 , P_DESC_MSG_APP_NAME IN VARCHAR2_TABLE
104 );
105
106 -- Log the description params
107 PROCEDURE DESCRIPTION_PARAMS
108 ( P_RECORD_ID IN NUMBER
109 , P_DESC_POS IN NUMBER
110 , P_PARAM_POS IN NUMBER
111 , P_PARAM_MSG_NAME IN VARCHAR2
112 , P_PARAM_MSG_APP_NAME IN VARCHAR2
113 , P_VALUE IN VARCHAR2
114 , P_VALUE_TYPE IN NUMBER
115 );
116
117 -- Use this for multiple parameters
118 PROCEDURE DESCRIPTION_PARAMS
119 ( P_RECORD_ID IN NUMBER
120 , P_DESC_POS IN NUMBER_TABLE
121 , P_PARAM_MSG_NAME IN VARCHAR2_TABLE
122 , P_PARAM_MSG_APP_NAME IN VARCHAR2_TABLE
123 , P_PARAM_VALUE IN VARCHAR2_TABLE
124 , P_PARAM_VALUE_TYPE IN NUMBER_TABLE
125 );
126
127 END IEC_SQL_LOGGER_PVT;