[Home] [Help]
PACKAGE BODY: APPS.OKC_K_ENTITY_LOCKS_PKG
Source
1 PACKAGE BODY okc_k_entity_locks_pkg
2 /* $Header: OKCVELKB.pls 120.0 2011/12/09 11:11:10 serukull noship $ */
3
4 AS
5
6 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7
8 ---------------------------------------------------------------------------
9 -- GLOBAL MESSAGE CONSTANTS
10 ---------------------------------------------------------------------------
11 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
12 ---------------------------------------------------------------------------
13 -- GLOBAL VARIABLES
14 ---------------------------------------------------------------------------
15 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_K_ENTITY_LOCKS_PKG';
16 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
17
18 ------------------------------------------------------------------------------
19 -- GLOBAL CONSTANTS
20 ------------------------------------------------------------------------------
21 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
22 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
23
24 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
25 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
26 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
27
28 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
29 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
30 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
31
32 G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
33 G_AMEND_CODE_ADDED CONSTANT VARCHAR2(30) := 'ADDED';
34 G_AMEND_CODE_UPDATED CONSTANT VARCHAR2(30) := 'UPDATED';
35
36 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
37 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
38 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
39 G_STMT_LEVEL NUMBER := FND_LOG.LEVEL_STATEMENT;
40
41
42 PROCEDURE insert_row ( p_entity_name IN VARCHAR2,
43 p_entity_pk1 IN VARCHAR2,
44 p_entity_pk2 IN VARCHAR2,
45 p_entity_pk3 IN VARCHAR2,
46 p_entity_pk4 IN VARCHAR2,
47 p_entity_pk5 IN VARCHAR2,
48 P_LOCK_BY_ENTITY_ID IN NUMBER,
49 P_LOCK_by_document_type IN VARCHAR2,
50 p_LOCK_by_document_id IN NUMBER,
51 P_OBJECT_VERSION_NUMBER IN NUMBER,
52 P_CREATED_BY IN NUMBER,
53 P_CREATION_DATE IN DATE,
54 P_LAST_UPDATED_BY IN NUMBER,
55 P_LAST_UPDATE_DATE IN DATE,
56 P_LAST_UPDATE_LOGIN IN NUMBER,
57 x_k_entity_lock_id OUT NOCOPY NUMBER,
58 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
59 X_MSG_COUNT OUT NOCOPY NUMBER,
60 X_MSG_DATA OUT NOCOPY VARCHAR2
61 )
62 IS
63 BEGIN
64
65 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
66 FND_LOG.STRING(G_STMT_LEVEL,
67 G_PKG_NAME, '1000: Entered Insert_Row Procedure' );
68 END IF;
69
70
71 INSERT INTO okc_k_entity_locks(
72 k_entity_lock_id,
73 entity_name,
74 entity_pk1,
75 entity_pk2,
76 entity_pk3,
77 entity_pk4,
78 entity_pk5,
79 LOCK_BY_ENTITY_ID,
80 LOCK_BY_DOCUMENT_TYPE,
81 LOCK_BY_DOCUMENT_ID,
82 OBJECT_VERSION_NUMBER,
83 CREATED_BY,
84 CREATION_DATE,
85 LAST_UPDATED_BY,
86 LAST_UPDATE_DATE,
87 LAST_UPDATE_LOGIN
88 )
89 VALUES(
90 okc_k_entity_locks_s.NEXTVAL,
91 P_ENTITY_NAME,
92 p_entity_pk1,
93 p_entity_pk2,
94 p_entity_pk3,
95 p_entity_pk4,
96 p_entity_pk5,
97 P_LOCK_BY_ENTITY_ID,
98 P_LOCK_BY_DOCUMENT_TYPE,
99 P_LOCK_BY_DOCUMENT_ID,
100 P_OBJECT_VERSION_NUMBER,
101 P_CREATED_BY,
102 P_CREATION_DATE,
103 P_LAST_UPDATED_BY,
104 P_LAST_UPDATE_DATE,
105 P_LAST_UPDATE_LOGIN
106 )
107 returning k_entity_lock_id INTO x_k_entity_lock_id;
108
109 -- Check uniqueness
110
111 X_RETURN_STATUS := G_RET_STS_SUCCESS;
112 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
113 FND_LOG.STRING(G_STMT_LEVEL,
114 G_PKG_NAME, '9999: Completed Insert_Row Procedure' );
115 END IF;
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
120 FND_LOG.STRING(G_STMT_LEVEL,
121 G_PKG_NAME, '0000: Leaving Insert_Row because of EXCEPTION: '||sqlerrm);
122 END IF;
123 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
124 p_msg_name => G_UNEXPECTED_ERROR,
125 p_token1 => G_SQLCODE_TOKEN,
126 p_token1_value => sqlcode,
127 p_token2 => G_SQLERRM_TOKEN,
128 p_token2_value => sqlerrm);
129 X_RETURN_STATUS := G_RET_STS_UNEXP_ERROR;
130 --FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
131 END insert_row;
132
133 PROCEDURE delete_row (P_ENTITY_NAME IN VARCHAR2,
134 p_entity_pk1 IN VARCHAR2,
135 p_entity_pk2 IN VARCHAR2,
136 p_entity_pk3 IN VARCHAR2,
137 p_entity_pk4 IN VARCHAR2,
138 p_entity_pk5 IN VARCHAR2,
139 P_LOCK_BY_ENTITY_ID IN NUMBER,
140 P_LOCK_by_document_type IN VARCHAR2,
141 p_LOCK_by_document_id IN NUMBER,
142 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
143 X_MSG_COUNT OUT NOCOPY NUMBER,
144 X_MSG_DATA OUT NOCOPY VARCHAR2
145 )
146 IS
147
148 l_del_Sql VARCHAR2(2000);
149
150 BEGIN
151
152 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
153 FND_LOG.STRING(G_STMT_LEVEL, G_PKG_NAME, '0100: Entered delete_row ');
154 FND_LOG.STRING(G_STMT_LEVEL, G_PKG_NAME, '0100: P_ENTITY_NAME ' || P_ENTITY_NAME );
155 FND_LOG.STRING(G_STMT_LEVEL, G_PKG_NAME, '0100: p_entity_pk1 ' ||p_entity_pk1 );
156 FND_LOG.STRING(G_STMT_LEVEL, G_PKG_NAME, '0100: p_entity_pk2 '||p_entity_pk2);
157 FND_LOG.STRING(G_STMT_LEVEL, G_PKG_NAME, '0100: p_entity_pk3 '||p_entity_pk3);
158 FND_LOG.STRING(G_STMT_LEVEL, G_PKG_NAME, '0100: p_entity_pk4 '||p_entity_pk4);
159 FND_LOG.STRING(G_STMT_LEVEL, G_PKG_NAME, '0100: p_entity_pk5 '||p_entity_pk5);
160 FND_LOG.STRING(G_STMT_LEVEL, G_PKG_NAME, '0100: P_LOCK_BY_ENTITY_ID '||P_LOCK_BY_ENTITY_ID);
161 FND_LOG.STRING(G_STMT_LEVEL, G_PKG_NAME, '0100: P_LOCK_by_document_type '||P_LOCK_by_document_type);
162 FND_LOG.STRING(G_STMT_LEVEL, G_PKG_NAME, '0100: p_LOCK_by_document_id '||p_LOCK_by_document_id);
163 END IF;
164
165
166 l_del_Sql := 'DELETE FROM OKC_K_ENTITY_LOCKS WHERE ENTITY_NAME = P_ENTITY_NAME AND ENTITY_PK1 = '|| ''''||p_entity_pk1 ||'''';
167
168 IF p_entity_pk2 IS NOT NULL THEN
169 l_del_Sql := l_del_Sql || ' AND ENTITY_PK2 = '|| ''''||p_entity_pk2||'''';
170 END IF;
171 IF p_entity_pk3 IS NOT NULL THEN
172 l_del_Sql := l_del_Sql || ' AND ENTITY_PK3 = '||''''||p_entity_pk3||'''';
173 END IF;
174 IF p_entity_pk4 IS NOT NULL THEN
175 l_del_Sql := l_del_Sql || ' AND ENTITY_PK4 = '||''''||p_entity_pk4||'''';
176 END IF;
177 IF p_entity_pk5 IS NOT NULL THEN
178 l_del_Sql := l_del_Sql || ' AND ENTITY_PK5 = '||''''||p_entity_pk5||'''';
179 END IF;
180 IF P_LOCK_BY_ENTITY_ID IS NOT NULL THEN
181 l_del_Sql := l_del_Sql || ' AND LOCK_BY_ENTITY_ID = '||P_LOCK_BY_ENTITY_ID;
182 END IF;
183 IF P_LOCK_by_document_type IS NOT NULL THEN
184 l_del_Sql := l_del_Sql || ' AND LOCK_BY_DOCUMENT_TYPE = '||''''||P_LOCK_by_document_type||'''';
185 END IF;
186 IF p_LOCK_by_document_id IS NOT NULL THEN
187 l_del_Sql := l_del_Sql || ' AND LOCK_BY_DOCUMENT_ID = '||p_LOCK_by_document_id;
188 END IF;
189
190 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
191 FND_LOG.STRING(G_STMT_LEVEL,
192 G_PKG_NAME, '0500: sql to delete the lock: '||l_del_Sql);
193 END IF;
194
195
196 EXECUTE IMMEDIATE l_del_Sql;
197
198 IF (SQL%NOTFOUND) THEN
199 RAISE No_Data_Found;
200 END IF;
201
202 X_RETURN_STATUS := G_RET_STS_SUCCESS;
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
207 FND_LOG.STRING(G_STMT_LEVEL,
208 G_PKG_NAME, '0000: Leaving DELETE_row because of EXCEPTION: '||sqlerrm);
209 END IF;
210 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
211 p_msg_name => G_UNEXPECTED_ERROR,
212 p_token1 => G_SQLCODE_TOKEN,
213 p_token1_value => sqlcode,
214 p_token2 => G_SQLERRM_TOKEN,
215 p_token2_value => sqlerrm);
216 X_RETURN_STATUS := G_RET_STS_UNEXP_ERROR;
217 --FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
218 END DELETE_row;
219
220 END okc_k_entity_locks_pkg;