[Home] [Help]
PACKAGE BODY: APPS.OKC_CONTRACT_DOCS_PVT
Source
1 PACKAGE BODY OKC_CONTRACT_DOCS_PVT AS
2 /* $Header: OKCVCONTRACTDOCB.pls 120.1 2006/02/21 16:16:30 vamuru noship $ */
3
4
5
6
7
8 ---------------------------------------------------------------------------
9 -- GLOBAL MESSAGE CONSTANTS
10 ---------------------------------------------------------------------------
11 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
12 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
13 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
14 G_NEW_RECORD_DELETED CONSTANT VARCHAR2(200) := 'OKC_LOCK_RECORD_DELETED';
15 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
16 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
17 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
18 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
19 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
20 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
21 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
22 ---------------------------------------------------------------------------
23 -- VALIDATION LEVELS
24 ---------------------------------------------------------------------------
25 G_REQUIRED_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_REQUIRED_VALUE_VALID_LEVEL;
26 G_VALID_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_VALID_VALUE_VALID_LEVEL;
27 G_LOOKUP_CODE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_LOOKUP_CODE_VALID_LEVEL;
28 G_FOREIGN_KEY_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_FOREIGN_KEY_VALID_LEVEL;
29 G_RECORD_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_RECORD_VALID_LEVEL;
30 ---------------------------------------------------------------------------
31 -- GLOBAL VARIABLES
32 ---------------------------------------------------------------------------
33 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_CONTRACT_DOCS_PVT';
34 G_MODULE CONSTANT VARCHAR2(200) := 'okc.plsql.'||G_PKG_NAME||'.';
35 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
36 G_ENTITY_NAME CONSTANT VARCHAR2(200) := 'Contract Document';
37
38
39 ------------------------------------------------------------------------------
40 -- GLOBAL CONSTANTS
41 ------------------------------------------------------------------------------
42 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
43 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
44 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
45 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
46 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
47
48 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
49 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
50 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
51
52 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
53 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
54 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
55
56 ------------------------------------------------------------------------------
57 -- GLOBAL EXCEPTION
58 ------------------------------------------------------------------------------
59 E_Resource_Busy EXCEPTION;
60 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
61
62
63 ---------------------------------------------------------------------------
64 -- FUNCTION get_rec for: OKC_CONTRACT_DOCS
65 ---------------------------------------------------------------------------
66 FUNCTION Get_Rec (
67 p_business_document_type IN VARCHAR2,
68 p_business_document_id IN NUMBER,
69 p_business_document_version IN NUMBER,
70 p_attached_document_id IN NUMBER,
71
72 x_external_visibility_flag OUT NOCOPY VARCHAR2,
73 x_effective_from_type OUT NOCOPY VARCHAR2,
74 x_effective_from_id OUT NOCOPY NUMBER,
75 x_effective_from_version OUT NOCOPY NUMBER,
76 x_include_for_approval_flag OUT NOCOPY VARCHAR2,
77 x_program_id OUT NOCOPY NUMBER,
78 x_program_application_id OUT NOCOPY NUMBER,
79 x_request_id OUT NOCOPY NUMBER,
80 x_program_update_date OUT NOCOPY DATE,
81 x_parent_attached_doc_id OUT NOCOPY NUMBER,
82 x_delete_flag OUT NOCOPY VARCHAR2,
83 x_generated_flag OUT NOCOPY VARCHAR2,
84 x_object_version_number OUT NOCOPY NUMBER,
85 x_created_by OUT NOCOPY NUMBER,
86 x_creation_date OUT NOCOPY DATE,
87 x_last_updated_by OUT NOCOPY NUMBER,
88 x_last_update_login OUT NOCOPY NUMBER,
89 x_last_update_date OUT NOCOPY DATE,
90
91 x_primary_contract_doc_flag OUT NOCOPY VARCHAR2,
92 x_mergeable_doc_flag OUT NOCOPY VARCHAR2
93
94 ) RETURN VARCHAR2 IS
95 l_api_name CONSTANT VARCHAR2(30) := 'get_rec';
96 CURSOR OKC_CONTRACT_DOCS_pk_csr (cp_business_document_type IN VARCHAR2,cp_business_document_id IN NUMBER,cp_business_document_version IN NUMBER,cp_attached_document_id IN NUMBER) IS
97 SELECT
98 EXTERNAL_VISIBILITY_FLAG,
99 EFFECTIVE_FROM_TYPE,
100 EFFECTIVE_FROM_ID,
101 EFFECTIVE_FROM_VERSION,
102 INCLUDE_FOR_APPROVAL_FLAG,
103 PROGRAM_ID,
104 PROGRAM_APPLICATION_ID,
105 REQUEST_ID,
106 PROGRAM_UPDATE_DATE,
107 PARENT_ATTACHED_DOC_ID,
108 DELETE_FLAG,
109 GENERATED_FLAG,
110 OBJECT_VERSION_NUMBER,
111 CREATED_BY,
112 CREATION_DATE,
113 LAST_UPDATED_BY,
114 LAST_UPDATE_LOGIN,
115 LAST_UPDATE_DATE,
116 PRIMARY_CONTRACT_DOC_FLAG,
117 MERGEABLE_DOC_FLAG
118 FROM OKC_CONTRACT_DOCS t
119 WHERE t.BUSINESS_DOCUMENT_TYPE = cp_business_document_type and
120 t.BUSINESS_DOCUMENT_ID = cp_business_document_id and
121 t.BUSINESS_DOCUMENT_VERSION = cp_business_document_version and
122 t.ATTACHED_DOCUMENT_ID = cp_attached_document_id;
123 BEGIN
124
125 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
126 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Entered get_rec');
127 END IF;
128
129 -- Get current database values
130 OPEN OKC_CONTRACT_DOCS_pk_csr (p_business_document_type, p_business_document_id, p_business_document_version, p_attached_document_id);
131 FETCH OKC_CONTRACT_DOCS_pk_csr INTO
132 x_external_visibility_flag,
133 x_effective_from_type,
134 x_effective_from_id,
135 x_effective_from_version,
136 x_include_for_approval_flag,
137 x_program_id,
138 x_program_application_id,
139 x_request_id,
140 x_program_update_date,
141 x_parent_attached_doc_id,
142 x_delete_flag,
143 x_generated_flag,
144 x_object_version_number,
145 x_created_by,
146 x_creation_date,
147 x_last_updated_by,
148 x_last_update_login,
149 x_last_update_date,
150 x_primary_contract_doc_flag,
151 x_mergeable_doc_flag ;
152 IF OKC_CONTRACT_DOCS_pk_csr%NOTFOUND THEN
153 RAISE NO_DATA_FOUND;
154 END IF;
155 CLOSE OKC_CONTRACT_DOCS_pk_csr;
156
157 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
158 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: Leaving get_rec ');
159 END IF;
160
161 RETURN G_RET_STS_SUCCESS ;
162
163 EXCEPTION
164 WHEN OTHERS THEN
165
166 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
167 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving get_rec because of EXCEPTION: '||sqlerrm);
168 END IF;
169
170 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
171 p_msg_name => G_UNEXPECTED_ERROR,
172 p_token1 => G_SQLCODE_TOKEN,
173 p_token1_value => sqlcode,
174 p_token2 => G_SQLERRM_TOKEN,
175 p_token2_value => sqlerrm);
176
177 IF OKC_CONTRACT_DOCS_pk_csr%ISOPEN THEN
178 CLOSE OKC_CONTRACT_DOCS_pk_csr;
179 END IF;
180
181 RETURN G_RET_STS_UNEXP_ERROR ;
182
183 END Get_Rec;
184
185 -----------------------------------------
186 -- Set_Attributes for:OKC_CONTRACT_DOCS --
187 -----------------------------------------
188 FUNCTION Set_Attributes(
189 p_business_document_type IN VARCHAR2,
190 p_business_document_id IN NUMBER,
191 p_business_document_version IN NUMBER,
192 p_attached_document_id IN NUMBER,
193 p_external_visibility_flag IN VARCHAR2,
194 p_effective_from_type IN VARCHAR2,
195 p_effective_from_id IN NUMBER,
196 p_effective_from_version IN NUMBER,
197 p_include_for_approval_flag IN VARCHAR2,
198 p_program_id IN NUMBER,
199 p_program_application_id IN NUMBER,
200 p_request_id IN NUMBER,
201 p_program_update_date IN DATE,
202 p_parent_attached_doc_id IN NUMBER,
203 p_delete_flag IN VARCHAR2,
204 p_generated_flag IN VARCHAR2,
205 p_object_version_number IN OUT NOCOPY NUMBER,
206 p_primary_contract_doc_flag IN VARCHAR2,
207 p_mergeable_doc_flag IN VARCHAR2,
208
209 x_external_visibility_flag OUT NOCOPY VARCHAR2,
210 x_effective_from_type OUT NOCOPY VARCHAR2,
211 x_effective_from_id OUT NOCOPY NUMBER,
212 x_effective_from_version OUT NOCOPY NUMBER,
213 x_include_for_approval_flag OUT NOCOPY VARCHAR2,
214 x_program_id OUT NOCOPY NUMBER,
215 x_program_application_id OUT NOCOPY NUMBER,
216 x_request_id OUT NOCOPY NUMBER,
217 x_program_update_date OUT NOCOPY DATE,
218 x_parent_attached_doc_id OUT NOCOPY NUMBER,
219 x_delete_flag OUT NOCOPY VARCHAR2,
220 x_generated_flag OUT NOCOPY VARCHAR2,
221 x_primary_contract_doc_flag OUT NOCOPY VARCHAR2,
222 x_mergeable_doc_flag OUT NOCOPY VARCHAR2
223 ) RETURN VARCHAR2 IS
224 l_api_name CONSTANT VARCHAR2(30) := 'set_attributes';
225 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
226 l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE;
227 l_created_by OKC_CONTRACT_DOCS.CREATED_BY%TYPE;
228 l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
229 l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
230 l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
231 l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
232 BEGIN
233 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
234 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Entered Set_Attributes ');
235 END IF;
236
237 IF( p_business_document_type IS NOT NULL AND p_business_document_id IS NOT NULL AND p_business_document_version IS NOT NULL AND p_attached_document_id IS NOT NULL ) THEN
238 -- Get current database values
239 l_return_status := Get_Rec(
240 p_business_document_type => p_business_document_type,
241 p_business_document_id => p_business_document_id,
242 p_business_document_version => p_business_document_version,
243 p_attached_document_id => p_attached_document_id,
244 x_external_visibility_flag => x_external_visibility_flag,
245 x_effective_from_type => x_effective_from_type,
246 x_effective_from_id => x_effective_from_id,
247 x_effective_from_version => x_effective_from_version,
248 x_include_for_approval_flag => x_include_for_approval_flag,
249 x_program_id => x_program_id,
250 x_program_application_id => x_program_application_id,
251 x_request_id => x_request_id,
252 x_program_update_date => x_program_update_date,
253 x_parent_attached_doc_id => x_parent_attached_doc_id,
254 x_delete_flag => x_delete_flag,
255 x_generated_flag => x_generated_flag,
256 x_object_version_number => l_object_version_number,
257 x_created_by => l_created_by,
258 x_creation_date => l_creation_date,
259 x_last_updated_by => l_last_updated_by,
260 x_last_update_login => l_last_update_login,
261 x_last_update_date => l_last_update_date,
262 x_primary_contract_doc_flag => x_primary_contract_doc_flag,
263 x_mergeable_doc_flag => x_mergeable_doc_flag
264 );
265 --- If any errors happen abort API
266 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
268 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271
272 --- Reversing G_MISS/NULL values logic
273
274 IF (p_external_visibility_flag = G_MISS_CHAR) THEN
275 x_external_visibility_flag := NULL;
276 ELSIF (p_external_visibility_flag IS NOT NULL) THEN
277 x_external_visibility_flag := p_external_visibility_flag;
278 END IF;
279
280 IF (p_effective_from_type = G_MISS_CHAR) THEN
281 x_effective_from_type := NULL;
282 ELSIF (p_effective_from_type IS NOT NULL) THEN
283 x_effective_from_type := p_effective_from_type;
284 END IF;
285
286 IF (p_effective_from_id = G_MISS_NUM) THEN
287 x_effective_from_id := NULL;
288 ELSIF (p_effective_from_id IS NOT NULL) THEN
289 x_effective_from_id := p_effective_from_id;
290 END IF;
291
292 IF (p_effective_from_version = G_MISS_NUM) THEN
293 x_effective_from_version := NULL;
294 ELSIF (p_effective_from_version IS NOT NULL) THEN
295 x_effective_from_version := p_effective_from_version;
296 END IF;
297
298 IF (p_include_for_approval_flag = G_MISS_CHAR) THEN
299 x_include_for_approval_flag := NULL;
300 ELSIF (p_include_for_approval_flag IS NOT NULL) THEN
301 x_include_for_approval_flag := p_include_for_approval_flag;
302 END IF;
303
304 IF (p_program_id = G_MISS_NUM) THEN
305 x_program_id := NULL;
306 ELSIF (p_program_id IS NOT NULL) THEN
307 x_program_id := p_program_id;
308 END IF;
309
310 IF (p_program_application_id = G_MISS_NUM) THEN
311 x_program_application_id := NULL;
312 ELSIF (p_program_application_id IS NOT NULL) THEN
313 x_program_application_id := p_program_application_id;
314 END IF;
315
316 IF (p_request_id = G_MISS_NUM) THEN
317 x_request_id := NULL;
318 ELSIF (p_request_id IS NOT NULL) THEN
319 x_request_id := p_request_id;
320 END IF;
321
322 IF (p_program_update_date = G_MISS_DATE) THEN
323 x_program_update_date := NULL;
324 ELSIF (p_program_update_date IS NOT NULL) THEN
325 x_program_update_date := p_program_update_date;
326 END IF;
327
328 IF (p_parent_attached_doc_id = G_MISS_NUM) THEN
329 x_parent_attached_doc_id := NULL;
330 ELSIF (p_parent_attached_doc_id IS NOT NULL) THEN
331 x_parent_attached_doc_id := p_parent_attached_doc_id;
332 END IF;
333
334 IF (p_delete_flag = G_MISS_CHAR) THEN
335 x_delete_flag := NULL;
336 ELSIF (p_delete_flag IS NOT NULL) THEN
337 x_delete_flag := p_delete_flag;
338 END IF;
339
340 IF (p_generated_flag = G_MISS_CHAR) THEN
341 x_generated_flag := NULL;
342 ELSIF (p_generated_flag IS NOT NULL) THEN
343 x_generated_flag := p_generated_flag;
344 END IF;
345
346
347 IF (p_object_version_number IS NULL) THEN
348 p_object_version_number := l_object_version_number;
349 END IF;
350
351
352 IF (p_primary_contract_doc_flag = G_MISS_CHAR) THEN
353 x_primary_contract_doc_flag := NULL;
354 ELSIF (p_primary_contract_doc_flag IS NOT NULL) THEN
355 x_primary_contract_doc_flag := p_primary_contract_doc_flag;
356 END IF;
357
358 IF (p_mergeable_doc_flag = G_MISS_CHAR) THEN
359 x_mergeable_doc_flag := NULL;
360 ELSIF (p_mergeable_doc_flag IS NOT NULL) THEN
361 x_mergeable_doc_flag := p_mergeable_doc_flag;
362 END IF;
363
364 END IF;
365
366 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
367 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: Leaving Set_Attributes ');
368 END IF;
369
370 RETURN G_RET_STS_SUCCESS ;
371 EXCEPTION
372 WHEN FND_API.G_EXC_ERROR THEN
373 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
374 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception');
375 END IF;
376 RETURN G_RET_STS_ERROR;
377
378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
379 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
380 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
381 END IF;
382 RETURN G_RET_STS_UNEXP_ERROR;
383
384 WHEN OTHERS THEN
385 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
386 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm);
387 END IF;
388 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
389 p_msg_name => G_UNEXPECTED_ERROR,
390 p_token1 => G_SQLCODE_TOKEN,
391 p_token1_value => sqlcode,
392 p_token2 => G_SQLERRM_TOKEN,
393 p_token2_value => sqlerrm);
394 RETURN G_RET_STS_UNEXP_ERROR;
395
396 END Set_Attributes ;
397
398 ----------------------------------------------
399 -- Validate_Attributes for: OKC_CONTRACT_DOCS --
400 ----------------------------------------------
401 FUNCTION Validate_Attributes (
402 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
403
404 p_business_document_type IN VARCHAR2,
405 p_business_document_id IN NUMBER,
406 p_business_document_version IN NUMBER,
407 p_attached_document_id IN NUMBER,
408 p_external_visibility_flag IN VARCHAR2,
409 p_effective_from_type IN VARCHAR2,
410 p_effective_from_id IN NUMBER,
411 p_effective_from_version IN NUMBER,
412 p_include_for_approval_flag IN VARCHAR2,
413 p_program_id IN NUMBER,
414 p_program_application_id IN NUMBER,
415 p_request_id IN NUMBER,
416 p_program_update_date IN DATE,
417 p_parent_attached_doc_id IN NUMBER,
418 p_delete_flag IN VARCHAR2,
419 p_generated_flag IN VARCHAR2,
420
421 p_primary_contract_doc_flag IN VARCHAR2,
422 p_mergeable_doc_flag IN VARCHAR2
423 ) RETURN VARCHAR2 IS
424 l_api_name CONSTANT VARCHAR2(30) := 'validate_attributes';
425 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
426 l_dummy_var VARCHAR2(1) := '?';
427
428 BEGIN
429
430 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
431 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1200: Entered Validate_Attributes');
432 END IF;
433
434 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
435 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
436 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1300: required values validation');
437 END IF;
438
439 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
440 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: - attribute BUSINESS_DOCUMENT_TYPE ');
441 END IF;
442 IF ( p_business_document_type IS NULL) THEN
443 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
444 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute BUSINESS_DOCUMENT_TYPE is invalid');
445 END IF;
446 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'BUSINESS_DOCUMENT_TYPE');
447 l_return_status := G_RET_STS_ERROR;
448 END IF;
449
450 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
451 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: - attribute BUSINESS_DOCUMENT_ID ');
452 END IF;
453 IF ( p_business_document_id IS NULL) THEN
454 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
455 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute BUSINESS_DOCUMENT_ID is invalid');
456 END IF;
457 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'BUSINESS_DOCUMENT_ID');
458 l_return_status := G_RET_STS_ERROR;
459 END IF;
460
461 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
462 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: - attribute BUSINESS_DOCUMENT_VERSION ');
463 END IF;
464 IF ( p_business_document_version IS NULL) THEN
465 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
466 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute BUSINESS_DOCUMENT_VERSION is invalid');
467 END IF;
468 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'BUSINESS_DOCUMENT_VERSION');
469 l_return_status := G_RET_STS_ERROR;
470 END IF;
471
472 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
473 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: - attribute ATTACHED_DOCUMENT_ID ');
474 END IF;
475 IF ( p_attached_document_id IS NULL) THEN
476 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute ATTACHED_DOCUMENT_ID is invalid');
478 END IF;
479 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ATTACHED_DOCUMENT_ID');
480 l_return_status := G_RET_STS_ERROR;
481 END IF;
482
483 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
484 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: - attribute EXTERNAL_VISIBILITY_FLAG ');
485 END IF;
486 IF ( p_external_visibility_flag IS NULL) THEN
487 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
488 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute EXTERNAL_VISIBILITY_FLAG is invalid');
489 END IF;
490 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'EXTERNAL_VISIBILITY_FLAG');
491 l_return_status := G_RET_STS_ERROR;
492 END IF;
493
494 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
495 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: - attribute EFFECTIVE_FROM_TYPE ');
496 END IF;
497 IF ( p_effective_from_type IS NULL) THEN
498 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
499 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute EFFECTIVE_FROM_TYPE is invalid');
500 END IF;
501 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'EFFECTIVE_FROM_TYPE');
502 l_return_status := G_RET_STS_ERROR;
503 END IF;
504
505 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
506 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: - attribute EFFECTIVE_FROM_ID ');
507 END IF;
508 IF ( p_effective_from_id IS NULL) THEN
509 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
510 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute EFFECTIVE_FROM_ID is invalid');
511 END IF;
512 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'EFFECTIVE_FROM_ID');
513 l_return_status := G_RET_STS_ERROR;
514 END IF;
515
516 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
517 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: - attribute EFFECTIVE_FROM_VERSION ');
518 END IF;
519 IF ( p_effective_from_version IS NULL) THEN
520 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
521 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute EFFECTIVE_FROM_VERSION is invalid');
522 END IF;
523 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'EFFECTIVE_FROM_VERSION');
524 l_return_status := G_RET_STS_ERROR;
525 END IF;
526
527 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
528 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: - attribute INCLUDE_FOR_APPROVAL_FLAG ');
529 END IF;
530 IF ( p_include_for_approval_flag IS NULL) THEN
531 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
532 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute INCLUDE_FOR_APPROVAL_FLAG is invalid');
533 END IF;
534 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'INCLUDE_FOR_APPROVAL_FLAG');
535 l_return_status := G_RET_STS_ERROR;
536 END IF;
537
538 END IF;
539
540 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
541 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
542 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: static values and range validation');
543 END IF;
544
545 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
546 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1700: - attribute PRIMARY_CONTRACT_DOC_FLAG ');
547 END IF;
548 IF ( p_primary_contract_doc_flag NOT IN ('Y','N') AND p_primary_contract_doc_flag IS NOT NULL) THEN
549 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
550 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: - attribute PRIMARY_CONTRACT_DOC_FLAG is invalid');
551 END IF;
552 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'PRIMARY_CONTRACT_DOC_FLAG');
553 l_return_status := G_RET_STS_ERROR;
554 END IF;
555
556 IF ( p_mergeable_doc_flag NOT IN ('Y','N') AND p_mergeable_doc_flag IS NOT NULL) THEN
557 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
558 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: - attribute mergeable_doc_flag is invalid');
559 END IF;
560 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'mergeable_doc_flag');
561 l_return_status := G_RET_STS_ERROR;
562 END IF;
563
564 END IF;
565
566 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
567 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
568 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1900: lookup codes validation');
569 END IF;
570
571 END IF;
572
573 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
574 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
575 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2100: foreigh keys validation ');
576 END IF;
577
578 END IF;
579
580
581 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
582 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2400: Leaving Validate_Attributes ');
583 END IF;
584
585 RETURN l_return_status;
586
587 EXCEPTION
588 WHEN OTHERS THEN
589 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
590 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm);
591 END IF;
592 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
593 p_msg_name => G_UNEXPECTED_ERROR,
594 p_token1 => G_SQLCODE_TOKEN,
595 p_token1_value => sqlcode,
596 p_token2 => G_SQLERRM_TOKEN,
597 p_token2_value => sqlerrm);
598
599
600 RETURN G_RET_STS_UNEXP_ERROR;
601
602 END Validate_Attributes;
603
604
605 ---------------------------------------------------------------------------
606 -- PROCEDURE Validate_Record
607 -- It calls Item Level Validations and then makes Record Level Validations
608 ---------------------------------------------------------------------------
609 ------------------------------------------
610 -- Validate_Record for:OKC_CONTRACT_DOCS --
611 ------------------------------------------
612 FUNCTION Validate_Record (
613 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
614
615 p_business_document_type IN VARCHAR2,
616 p_business_document_id IN NUMBER,
617 p_business_document_version IN NUMBER,
618 p_attached_document_id IN NUMBER,
619 p_external_visibility_flag IN VARCHAR2,
620 p_effective_from_type IN VARCHAR2,
621 p_effective_from_id IN NUMBER,
622 p_effective_from_version IN NUMBER,
623 p_include_for_approval_flag IN VARCHAR2,
624 p_program_id IN NUMBER,
625 p_program_application_id IN NUMBER,
626 p_request_id IN NUMBER,
627 p_program_update_date IN DATE,
628 p_parent_attached_doc_id IN NUMBER,
629 p_delete_flag IN VARCHAR2,
630 p_generated_flag IN VARCHAR2,
631
632 p_primary_contract_doc_flag IN VARCHAR2,
633 p_mergeable_doc_flag IN VARCHAR2
634 ) RETURN VARCHAR2 IS
635 l_api_name CONSTANT VARCHAR2(30) := 'validate_record';
636 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
637 BEGIN
638
639 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
640 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2600: Entered Validate_Record');
641 END IF;
642
643 --- Validate all non-missing attributes (Item Level Validation)
644 l_return_status := Validate_Attributes(
645 p_validation_level => p_validation_level,
646
647 p_business_document_type => p_business_document_type,
648 p_business_document_id => p_business_document_id,
649 p_business_document_version => p_business_document_version,
650 p_attached_document_id => p_attached_document_id,
651 p_external_visibility_flag => p_external_visibility_flag,
652 p_effective_from_type => p_effective_from_type,
653 p_effective_from_id => p_effective_from_id,
654 p_effective_from_version => p_effective_from_version,
655 p_include_for_approval_flag => p_include_for_approval_flag,
656 p_program_id => p_program_id,
657 p_program_application_id => p_program_application_id,
658 p_request_id => p_request_id,
659 p_program_update_date => p_program_update_date,
660 p_parent_attached_doc_id => p_parent_attached_doc_id,
661 p_delete_flag => p_delete_flag,
662 p_generated_flag => p_generated_flag,
663 p_primary_contract_doc_flag => p_primary_contract_doc_flag,
664 p_mergeable_doc_flag => p_mergeable_doc_flag
665 );
666 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
667 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
668 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm);
669 END IF;
670 RETURN G_RET_STS_UNEXP_ERROR;
671 END IF;
672
673 --- Record Level Validation
674 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
675 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
676 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2800: Entered Record Level Validations');
677 END IF;
678
679 END IF;
680
681 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
682 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2900: Leaving Validate_Record : '||sqlerrm);
683 END IF;
684 RETURN l_return_status ;
685
686 EXCEPTION
687 WHEN OTHERS THEN
688
689 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
690 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm);
691 END IF;
692
693 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
694 p_msg_name => G_UNEXPECTED_ERROR,
695 p_token1 => G_SQLCODE_TOKEN,
696 p_token1_value => sqlcode,
697 p_token2 => G_SQLERRM_TOKEN,
698 p_token2_value => sqlerrm);
699 RETURN G_RET_STS_UNEXP_ERROR ;
700
701 END Validate_Record;
702
703 ---------------------------------------------------------------------------
704 -- PROCEDURE validate_row
705 ---------------------------------------------------------------------------
706 ---------------------------------------
707 -- validate_row for:OKC_CONTRACT_DOCS --
708 ---------------------------------------
709 PROCEDURE validate_row(
710 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
711
712 x_return_status OUT NOCOPY VARCHAR2,
713
714 p_business_document_type IN VARCHAR2,
715 p_business_document_id IN NUMBER,
716 p_business_document_version IN NUMBER,
717 p_attached_document_id IN NUMBER,
718 p_external_visibility_flag IN VARCHAR2,
719 p_effective_from_type IN VARCHAR2,
720 p_effective_from_id IN NUMBER,
721 p_effective_from_version IN NUMBER,
722 p_include_for_approval_flag IN VARCHAR2,
723 p_program_id IN NUMBER,
724 p_program_application_id IN NUMBER,
725 p_request_id IN NUMBER,
726 p_program_update_date IN DATE,
727 p_parent_attached_doc_id IN NUMBER,
728 p_delete_flag IN VARCHAR2,
729 p_generated_flag IN VARCHAR2,
730
731 p_primary_contract_doc_flag IN VARCHAR2,
732 p_mergeable_doc_flag IN VARCHAR2,
733
734 p_object_version_number IN NUMBER
735 ) IS
736 l_api_name CONSTANT VARCHAR2(30) := 'validate_row';
737 l_external_visibility_flag OKC_CONTRACT_DOCS.EXTERNAL_VISIBILITY_FLAG%TYPE;
738 l_effective_from_type OKC_CONTRACT_DOCS.EFFECTIVE_FROM_TYPE%TYPE;
739 l_effective_from_id OKC_CONTRACT_DOCS.EFFECTIVE_FROM_ID%TYPE;
740 l_effective_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
741 l_include_for_approval_flag OKC_CONTRACT_DOCS.INCLUDE_FOR_APPROVAL_FLAG%TYPE;
742 l_program_id OKC_CONTRACT_DOCS.PROGRAM_ID%TYPE;
743 l_program_application_id OKC_CONTRACT_DOCS.PROGRAM_APPLICATION_ID%TYPE;
744 l_request_id OKC_CONTRACT_DOCS.REQUEST_ID%TYPE;
745 l_program_update_date OKC_CONTRACT_DOCS.PROGRAM_UPDATE_DATE%TYPE;
746 l_parent_attached_doc_id OKC_CONTRACT_DOCS.PARENT_ATTACHED_DOC_ID%TYPE;
747 l_delete_flag OKC_CONTRACT_DOCS.DELETE_FLAG%TYPE;
748 l_generated_flag OKC_CONTRACT_DOCS.GENERATED_FLAG%TYPE;
749 l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE;
750 l_created_by OKC_CONTRACT_DOCS.CREATED_BY%TYPE;
751 l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
752 l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
753 l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
754 l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
755 l_primary_contract_doc_flag OKC_CONTRACT_DOCS.PRIMARY_CONTRACT_DOC_FLAG%TYPE;
756 l_mergeable_doc_flag OKC_CONTRACT_DOCS.MERGEABLE_DOC_FLAG%TYPE;
757 BEGIN
758
759 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
760 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3100: Entered validate_row');
761 END IF;
762
763 -- Setting attributes
764 x_return_status := Set_Attributes(
765 p_business_document_type => p_business_document_type,
766 p_business_document_id => p_business_document_id,
767 p_business_document_version => p_business_document_version,
768 p_attached_document_id => p_attached_document_id,
769 p_external_visibility_flag => p_external_visibility_flag,
770 p_effective_from_type => p_effective_from_type,
771 p_effective_from_id => p_effective_from_id,
772 p_effective_from_version => p_effective_from_version,
773 p_include_for_approval_flag => p_include_for_approval_flag,
774 p_program_id => p_program_id,
775 p_program_application_id => p_program_application_id,
776 p_request_id => p_request_id,
777 p_program_update_date => p_program_update_date,
778 p_parent_attached_doc_id => p_parent_attached_doc_id,
779 p_delete_flag => p_delete_flag,
780 p_generated_flag => p_generated_flag,
781 p_object_version_number => l_object_version_number,
782 p_primary_contract_doc_flag => p_primary_contract_doc_flag,
783 p_mergeable_doc_flag => p_mergeable_doc_flag ,
784
785 x_external_visibility_flag => l_external_visibility_flag,
786 x_effective_from_type => l_effective_from_type,
787 x_effective_from_id => l_effective_from_id,
788 x_effective_from_version => l_effective_from_version,
789 x_include_for_approval_flag => l_include_for_approval_flag,
790 x_program_id => l_program_id,
791 x_program_application_id => l_program_application_id,
792 x_request_id => l_request_id,
793 x_program_update_date => l_program_update_date,
794 x_parent_attached_doc_id => l_parent_attached_doc_id,
795 x_delete_flag => l_delete_flag,
796 x_generated_flag => l_generated_flag,
797 x_primary_contract_doc_flag => l_primary_contract_doc_flag,
798 x_mergeable_doc_flag => l_mergeable_doc_flag
799 );
800 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
801 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
802 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
803 RAISE FND_API.G_EXC_ERROR;
804 END IF;
805
806 -- Validate all non-missing attributes (Item Level Validation)
807 l_object_version_number := p_object_version_number ;
808 x_return_status := Validate_Record(
809 p_validation_level => p_validation_level,
810 p_business_document_type => p_business_document_type,
811 p_business_document_id => p_business_document_id,
812 p_business_document_version => p_business_document_version,
813 p_attached_document_id => p_attached_document_id,
814 p_external_visibility_flag => l_external_visibility_flag,
815 p_effective_from_type => l_effective_from_type,
816 p_effective_from_id => l_effective_from_id,
817 p_effective_from_version => l_effective_from_version,
818 p_include_for_approval_flag => l_include_for_approval_flag,
819 p_program_id => l_program_id,
820 p_program_application_id => l_program_application_id,
821 p_request_id => l_request_id,
822 p_program_update_date => l_program_update_date,
823 p_parent_attached_doc_id => l_parent_attached_doc_id,
824 p_delete_flag => l_delete_flag,
825 p_generated_flag => l_generated_flag,
826 p_primary_contract_doc_flag => l_primary_contract_doc_flag,
827 p_mergeable_doc_flag => l_mergeable_doc_flag
828 );
829
830 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
831 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3200: Leaving validate_row');
832 END IF;
833
834 EXCEPTION
835 WHEN FND_API.G_EXC_ERROR THEN
836 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
837 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception');
838 END IF;
839 x_return_status := G_RET_STS_ERROR;
840
841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
842 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
843 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
844 END IF;
845 x_return_status := G_RET_STS_UNEXP_ERROR;
846
847 WHEN OTHERS THEN
848 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
849 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm);
850 END IF;
851 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
852 p_msg_name => G_UNEXPECTED_ERROR,
853 p_token1 => G_SQLCODE_TOKEN,
854 p_token1_value => sqlcode,
855 p_token2 => G_SQLERRM_TOKEN,
856 p_token2_value => sqlerrm);
857 x_return_status := G_RET_STS_UNEXP_ERROR;
858
859 END Validate_Row;
860
861 ---------------------------------------------------------------------------
862 -- PROCEDURE Insert_Row
863 ---------------------------------------------------------------------------
864 -------------------------------------
865 -- Insert_Row for:OKC_CONTRACT_DOCS --
866 -------------------------------------
867 FUNCTION Insert_Row(
868 p_business_document_type IN VARCHAR2,
869 p_business_document_id IN NUMBER,
870 p_business_document_version IN NUMBER,
871 p_attached_document_id IN NUMBER,
872 p_external_visibility_flag IN VARCHAR2,
873 p_effective_from_type IN VARCHAR2,
874 p_effective_from_id IN NUMBER,
875 p_effective_from_version IN NUMBER,
876 p_include_for_approval_flag IN VARCHAR2,
877 p_program_id IN NUMBER,
878 p_program_application_id IN NUMBER,
879 p_request_id IN NUMBER,
880 p_program_update_date IN DATE,
881 p_parent_attached_doc_id IN NUMBER,
882 p_delete_flag IN VARCHAR2,
883 p_generated_flag IN VARCHAR2,
884 p_object_version_number IN NUMBER,
885 p_created_by IN NUMBER,
886 p_creation_date IN DATE,
887 p_last_updated_by IN NUMBER,
888 p_last_update_login IN NUMBER,
889 p_last_update_date IN DATE,
890
891 p_primary_contract_doc_flag IN VARCHAR2,
892 p_mergeable_doc_flag IN VARCHAR2
893 ) RETURN VARCHAR2 IS
894 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
895 BEGIN
896
897 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
898 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Entered Insert_Row function');
899 END IF;
900
901 INSERT INTO OKC_CONTRACT_DOCS(
902 BUSINESS_DOCUMENT_TYPE,
903 BUSINESS_DOCUMENT_ID,
904 BUSINESS_DOCUMENT_VERSION,
905 ATTACHED_DOCUMENT_ID,
906 EXTERNAL_VISIBILITY_FLAG,
907 EFFECTIVE_FROM_TYPE,
908 EFFECTIVE_FROM_ID,
909 EFFECTIVE_FROM_VERSION,
910 INCLUDE_FOR_APPROVAL_FLAG,
911 PROGRAM_ID,
912 PROGRAM_APPLICATION_ID,
913 REQUEST_ID,
914 PROGRAM_UPDATE_DATE,
915 PARENT_ATTACHED_DOC_ID,
916 DELETE_FLAG,
917 GENERATED_FLAG,
918 OBJECT_VERSION_NUMBER,
919 CREATED_BY,
920 CREATION_DATE,
921 LAST_UPDATED_BY,
922 LAST_UPDATE_LOGIN,
923 LAST_UPDATE_DATE,
924 PRIMARY_CONTRACT_DOC_FLAG,
925 MERGEABLE_DOC_FLAG )
926 VALUES (
927 p_business_document_type,
928 p_business_document_id,
929 p_business_document_version,
930 p_attached_document_id,
931 p_external_visibility_flag,
932 p_effective_from_type,
933 p_effective_from_id,
934 p_effective_from_version,
935 p_include_for_approval_flag,
936 p_program_id,
937 p_program_application_id,
938 p_request_id,
939 p_program_update_date,
940 p_parent_attached_doc_id,
941 p_delete_flag,
942 p_generated_flag,
943 p_object_version_number,
944 p_created_by,
945 p_creation_date,
946 p_last_updated_by,
947 p_last_update_login,
948 p_last_update_date,
949 p_primary_contract_doc_flag,
950 p_mergeable_doc_flag );
951
952 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
953 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3700: Leaving Insert_Row');
954 END IF;
955
956 RETURN( G_RET_STS_SUCCESS );
957
958 EXCEPTION
959 WHEN OTHERS THEN
960
961 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
962 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3800: Leaving Insert_Row:OTHERS Exception');
963 END IF;
964
965 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
966 p_msg_name => G_UNEXPECTED_ERROR,
967 p_token1 => G_SQLCODE_TOKEN,
968 p_token1_value => sqlcode,
969 p_token2 => G_SQLERRM_TOKEN,
970 p_token2_value => sqlerrm);
971
972 RETURN( G_RET_STS_UNEXP_ERROR );
973
974 END Insert_Row;
975
976
977 -------------------------------------
978 -- Insert_Row for:OKC_CONTRACT_DOCS --
979 -------------------------------------
980 PROCEDURE Insert_Row(
981 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
982 x_return_status OUT NOCOPY VARCHAR2,
983
984 p_business_document_type IN VARCHAR2,
985 p_business_document_id IN NUMBER,
986 p_business_document_version IN NUMBER,
987 p_attached_document_id IN NUMBER,
988 p_external_visibility_flag IN VARCHAR2,
989 p_effective_from_type IN VARCHAR2,
990 p_effective_from_id IN NUMBER,
991 p_effective_from_version IN NUMBER,
992 p_include_for_approval_flag IN VARCHAR2,
993 p_program_id IN NUMBER,
994 p_program_application_id IN NUMBER,
995 p_request_id IN NUMBER,
996 p_program_update_date IN DATE,
997 p_parent_attached_doc_id IN NUMBER,
998 p_delete_flag IN VARCHAR2,
999 p_generated_flag IN VARCHAR2,
1000
1001 p_primary_contract_doc_flag IN VARCHAR2,
1002 p_mergeable_doc_flag IN VARCHAR2,
1003
1004 x_business_document_type OUT NOCOPY VARCHAR2,
1005 x_business_document_id OUT NOCOPY NUMBER,
1006 x_business_document_version OUT NOCOPY NUMBER,
1007 x_attached_document_id OUT NOCOPY NUMBER
1008
1009 ) IS
1010 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1011 l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE;
1012 l_created_by OKC_CONTRACT_DOCS.CREATED_BY%TYPE;
1013 l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
1014 l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
1015 l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
1016 l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
1017 BEGIN
1018
1019 x_return_status := G_RET_STS_SUCCESS;
1020
1021 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1022 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4200: Entered Insert_Row');
1023 END IF;
1024
1025 x_business_document_type := p_business_document_type;
1026 x_business_document_id := p_business_document_id;
1027 x_business_document_version := p_business_document_version;
1028 x_attached_document_id := p_attached_document_id;
1029 l_object_version_number := 1;
1030 l_creation_date := Sysdate;
1031 l_created_by := Fnd_Global.User_Id;
1032 l_last_update_date := l_creation_date;
1033 l_last_updated_by := l_created_by;
1034 l_last_update_login := Fnd_Global.Login_Id;
1035
1036
1037 --- Validate all non-missing attributes
1038 x_return_status := Validate_Record(
1039 p_validation_level => p_validation_level,
1040 p_business_document_type => x_business_document_type,
1041 p_business_document_id => x_business_document_id,
1042 p_business_document_version => x_business_document_version,
1043 p_attached_document_id => x_attached_document_id,
1044 p_external_visibility_flag => p_external_visibility_flag,
1045 p_effective_from_type => p_effective_from_type,
1046 p_effective_from_id => p_effective_from_id,
1047 p_effective_from_version => p_effective_from_version,
1048 p_include_for_approval_flag => p_include_for_approval_flag,
1049 p_program_id => p_program_id,
1050 p_program_application_id => p_program_application_id,
1051 p_request_id => p_request_id,
1052 p_program_update_date => p_program_update_date,
1053 p_parent_attached_doc_id => p_parent_attached_doc_id,
1054 p_delete_flag => p_delete_flag,
1055 p_generated_flag => p_generated_flag,
1056 p_primary_contract_doc_flag => p_primary_contract_doc_flag,
1057 p_mergeable_doc_flag => p_mergeable_doc_flag
1058 );
1059 --- If any errors happen abort API
1060 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1062 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1063 RAISE FND_API.G_EXC_ERROR;
1064 END IF;
1065
1066 --------------------------------------------
1067 -- Call the internal Insert_Row for each child record
1068 --------------------------------------------
1069 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1070 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4300: Call the internal Insert_Row for Base Table');
1071 END IF;
1072
1073 x_return_status := Insert_Row(
1074 p_business_document_type => x_business_document_type,
1075 p_business_document_id => x_business_document_id,
1076 p_business_document_version => x_business_document_version,
1077 p_attached_document_id => x_attached_document_id,
1078 p_external_visibility_flag => p_external_visibility_flag,
1079 p_effective_from_type => p_effective_from_type,
1080 p_effective_from_id => p_effective_from_id,
1081 p_effective_from_version => p_effective_from_version,
1082 p_include_for_approval_flag => p_include_for_approval_flag,
1083 p_program_id => p_program_id,
1084 p_program_application_id => p_program_application_id,
1085 p_request_id => p_request_id,
1086 p_program_update_date => p_program_update_date,
1087 p_parent_attached_doc_id => p_parent_attached_doc_id,
1088 p_delete_flag => p_delete_flag,
1089 p_generated_flag => p_generated_flag,
1090 p_object_version_number => l_object_version_number,
1091 p_created_by => l_created_by,
1092 p_creation_date => l_creation_date,
1093 p_last_updated_by => l_last_updated_by,
1094 p_last_update_login => l_last_update_login,
1095 p_last_update_date => l_last_update_date,
1096 p_primary_contract_doc_flag => p_primary_contract_doc_flag,
1097 p_mergeable_doc_flag => p_mergeable_doc_flag
1098 );
1099 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1101 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1102 RAISE FND_API.G_EXC_ERROR;
1103 END IF;
1104
1105
1106
1107 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1108 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4500: Leaving Insert_Row');
1109 END IF;
1110
1111 EXCEPTION
1112 WHEN FND_API.G_EXC_ERROR THEN
1113 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1114 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception');
1115 END IF;
1116 x_return_status := G_RET_STS_ERROR;
1117
1118 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1119 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1120 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1121 END IF;
1122 x_return_status := G_RET_STS_UNEXP_ERROR;
1123
1124 WHEN OTHERS THEN
1125 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1126 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm);
1127 END IF;
1128 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1129 p_msg_name => G_UNEXPECTED_ERROR,
1130 p_token1 => G_SQLCODE_TOKEN,
1131 p_token1_value => sqlcode,
1132 p_token2 => G_SQLERRM_TOKEN,
1133 p_token2_value => sqlerrm);
1134 x_return_status := G_RET_STS_UNEXP_ERROR;
1135
1136 END Insert_Row;
1137 ---------------------------------------------------------------------------
1138 -- PROCEDURE Lock_Row
1139 ---------------------------------------------------------------------------
1140 -----------------------------------
1141 -- Lock_Row for:OKC_CONTRACT_DOCS --
1142 -----------------------------------
1143 FUNCTION Lock_Row(
1144 p_business_document_type IN VARCHAR2,
1145 p_business_document_id IN NUMBER,
1146 p_business_document_version IN NUMBER,
1147 p_attached_document_id IN NUMBER,
1148 p_object_version_number IN NUMBER
1149 ) RETURN VARCHAR2 IS
1150 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1151 l_return_status VARCHAR2(1);
1152 l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE;
1153 l_row_notfound BOOLEAN := FALSE;
1154 l_pk_string VARCHAR2(240);
1155
1156 CURSOR lock_csr (cp_business_document_type VARCHAR2, cp_business_document_id NUMBER, cp_business_document_version NUMBER, cp_attached_document_id NUMBER, cp_object_version_number NUMBER) IS
1157 SELECT object_version_number
1158 FROM OKC_CONTRACT_DOCS
1159 WHERE BUSINESS_DOCUMENT_TYPE = cp_business_document_type AND BUSINESS_DOCUMENT_ID = cp_business_document_id AND BUSINESS_DOCUMENT_VERSION = cp_business_document_version AND ATTACHED_DOCUMENT_ID = cp_attached_document_id
1160 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1161 FOR UPDATE OF object_version_number NOWAIT;
1162
1163 CURSOR lchk_csr (cp_business_document_type VARCHAR2, cp_business_document_id NUMBER, cp_business_document_version NUMBER, cp_attached_document_id NUMBER) IS
1164 SELECT object_version_number
1165 FROM OKC_CONTRACT_DOCS
1166 WHERE BUSINESS_DOCUMENT_TYPE = cp_business_document_type AND BUSINESS_DOCUMENT_ID = cp_business_document_id AND BUSINESS_DOCUMENT_VERSION = cp_business_document_version AND ATTACHED_DOCUMENT_ID = cp_attached_document_id;
1167 BEGIN
1168
1169 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1170 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4900: Entered Lock_Row');
1171 END IF;
1172
1173
1174 BEGIN
1175
1176 OPEN lock_csr( p_business_document_type, p_business_document_id, p_business_document_version, p_attached_document_id, p_object_version_number );
1177 FETCH lock_csr INTO l_object_version_number;
1178 l_row_notfound := lock_csr%NOTFOUND;
1179 CLOSE lock_csr;
1180
1181 EXCEPTION
1182 WHEN E_Resource_Busy THEN
1183
1184 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1185 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5000: Leaving Lock_Row:E_Resource_Busy Exception');
1186 END IF;
1187
1188 IF (lock_csr%ISOPEN) THEN
1189 CLOSE lock_csr;
1190 END IF;
1191 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1192 RETURN( G_RET_STS_ERROR );
1193 END;
1194
1195 IF ( l_row_notfound ) THEN
1196 l_return_status := G_RET_STS_ERROR;
1197
1198 OPEN lchk_csr(p_business_document_type, p_business_document_id, p_business_document_version, p_attached_document_id);
1199 FETCH lchk_csr INTO l_object_version_number;
1200 l_row_notfound := lchk_csr%NOTFOUND;
1201 CLOSE lchk_csr;
1202
1203 IF (l_row_notfound) THEN
1204 l_pk_string := p_business_document_type || ':' || p_business_document_id || ':' || p_business_document_version || ':' || p_attached_document_id;
1205 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1206 p_msg_name => G_NEW_RECORD_DELETED,
1207 p_token1 => 'ENTITYNAME',
1208 p_token1_value => G_ENTITY_NAME,
1209 p_token2 => 'PKEY',
1210 p_token2_value => l_pk_string,
1211 p_token3 => 'OVN',
1212 p_token3_value => l_object_version_number);
1213 -- Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
1214
1215 ELSIF l_object_version_number > p_object_version_number THEN
1216 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1217 ELSIF l_object_version_number = -1 THEN
1218 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1219 ELSE -- it can be the only above condition. It can happen after restore version
1220 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1221 END IF;
1222 ELSE
1223 l_return_status := G_RET_STS_SUCCESS;
1224 END IF;
1225
1226 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1227 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5100: Leaving Lock_Row');
1228 END IF;
1229
1230 RETURN( l_return_status );
1231
1232 EXCEPTION
1233 WHEN OTHERS THEN
1234
1235 IF (lock_csr%ISOPEN) THEN
1236 CLOSE lock_csr;
1237 END IF;
1238 IF (lchk_csr%ISOPEN) THEN
1239 CLOSE lchk_csr;
1240 END IF;
1241
1242 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1243 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm);
1244 END IF;
1245
1246 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1247 p_msg_name => G_UNEXPECTED_ERROR,
1248 p_token1 => G_SQLCODE_TOKEN,
1249 p_token1_value => sqlcode,
1250 p_token2 => G_SQLERRM_TOKEN,
1251 p_token2_value => sqlerrm);
1252
1253 RETURN( G_RET_STS_UNEXP_ERROR );
1254 END Lock_Row;
1255
1256 -----------------------------------
1257 -- Lock_Row for:OKC_CONTRACT_DOCS --
1258 -----------------------------------
1259 PROCEDURE Lock_Row(
1260 x_return_status OUT NOCOPY VARCHAR2,
1261
1262 p_business_document_type IN VARCHAR2,
1263 p_business_document_id IN NUMBER,
1264 p_business_document_version IN NUMBER,
1265 p_attached_document_id IN NUMBER,
1266 p_object_version_number IN NUMBER
1267 ) IS
1268 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1269 BEGIN
1270
1271 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1272 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5700: Entered Lock_Row');
1273 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5800: Locking Row for Base Table');
1274 END IF;
1275
1276 --------------------------------------------
1277 -- Call the LOCK_ROW for each _B child record
1278 --------------------------------------------
1279 x_return_status := Lock_Row(
1280 p_business_document_type => p_business_document_type,
1281 p_business_document_id => p_business_document_id,
1282 p_business_document_version => p_business_document_version,
1283 p_attached_document_id => p_attached_document_id,
1284 p_object_version_number => p_object_version_number
1285 );
1286 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1288 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1289 RAISE FND_API.G_EXC_ERROR;
1290 END IF;
1291
1292
1293
1294 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1295 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6000: Leaving Lock_Row');
1296 END IF;
1297
1298 EXCEPTION
1299 WHEN FND_API.G_EXC_ERROR THEN
1300 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1301 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception');
1302 END IF;
1303 x_return_status := G_RET_STS_ERROR;
1304
1305 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1306 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1307 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1308 END IF;
1309 x_return_status := G_RET_STS_UNEXP_ERROR;
1310
1311 WHEN OTHERS THEN
1312 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1313 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm);
1314 END IF;
1315 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1316 p_msg_name => G_UNEXPECTED_ERROR,
1317 p_token1 => G_SQLCODE_TOKEN,
1318 p_token1_value => sqlcode,
1319 p_token2 => G_SQLERRM_TOKEN,
1320 p_token2_value => sqlerrm);
1321 x_return_status := G_RET_STS_UNEXP_ERROR;
1322
1323 END Lock_Row;
1324 ---------------------------------------------------------------------------
1325 -- PROCEDURE Update_Row
1326 ---------------------------------------------------------------------------
1327 -------------------------------------
1328 -- Update_Row for:OKC_CONTRACT_DOCS --
1329 -------------------------------------
1330 FUNCTION Update_Row(
1331 p_business_document_type IN VARCHAR2,
1332 p_business_document_id IN NUMBER,
1333 p_business_document_version IN NUMBER,
1334 p_attached_document_id IN NUMBER,
1335 p_external_visibility_flag IN VARCHAR2,
1336 p_effective_from_type IN VARCHAR2,
1337 p_effective_from_id IN NUMBER,
1338 p_effective_from_version IN NUMBER,
1339 p_include_for_approval_flag IN VARCHAR2,
1340 p_program_id IN NUMBER,
1341 p_program_application_id IN NUMBER,
1342 p_request_id IN NUMBER,
1343 p_program_update_date IN DATE,
1344 p_parent_attached_doc_id IN NUMBER,
1345 p_delete_flag IN VARCHAR2,
1346 p_generated_flag IN VARCHAR2,
1347 p_object_version_number IN NUMBER,
1348 p_last_updated_by IN NUMBER,
1349 p_last_update_login IN NUMBER,
1350 p_last_update_date IN DATE,
1351
1352 p_primary_contract_doc_flag IN VARCHAR2,
1353 p_mergeable_doc_flag IN VARCHAR2
1354 ) RETURN VARCHAR2 IS
1355 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
1356 BEGIN
1357
1358 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1359 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6400: Entered Update_Row');
1360 END IF;
1361
1362 UPDATE OKC_CONTRACT_DOCS
1363 SET EXTERNAL_VISIBILITY_FLAG = p_external_visibility_flag,
1364 EFFECTIVE_FROM_TYPE = p_effective_from_type,
1365 EFFECTIVE_FROM_ID = p_effective_from_id,
1366 EFFECTIVE_FROM_VERSION = p_effective_from_version,
1367 INCLUDE_FOR_APPROVAL_FLAG = p_include_for_approval_flag,
1368 PROGRAM_ID = p_program_id,
1369 PROGRAM_APPLICATION_ID = p_program_application_id,
1370 REQUEST_ID = p_request_id,
1371 PROGRAM_UPDATE_DATE = p_program_update_date,
1372 PARENT_ATTACHED_DOC_ID = p_parent_attached_doc_id,
1373 DELETE_FLAG = p_delete_flag,
1374 GENERATED_FLAG = p_generated_flag,
1375 OBJECT_VERSION_NUMBER = p_object_version_number,
1376 LAST_UPDATED_BY = p_last_updated_by,
1377 LAST_UPDATE_LOGIN = p_last_update_login,
1378 LAST_UPDATE_DATE = p_last_update_date,
1379 PRIMARY_CONTRACT_DOC_FLAG = p_primary_contract_doc_flag,
1380 MERGEABLE_DOC_FLAG = p_mergeable_doc_flag
1381 WHERE BUSINESS_DOCUMENT_TYPE = p_business_document_type AND BUSINESS_DOCUMENT_ID = p_business_document_id AND BUSINESS_DOCUMENT_VERSION = p_business_document_version AND ATTACHED_DOCUMENT_ID = p_attached_document_id;
1382
1383 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1384 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6500: Leaving Update_Row');
1385 END IF;
1386
1387 RETURN G_RET_STS_SUCCESS ;
1388
1389 EXCEPTION
1390 WHEN OTHERS THEN
1391
1392 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1393 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm);
1394 END IF;
1395
1396 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1397 p_msg_name => G_UNEXPECTED_ERROR,
1398 p_token1 => G_SQLCODE_TOKEN,
1399 p_token1_value => sqlcode,
1400 p_token2 => G_SQLERRM_TOKEN,
1401 p_token2_value => sqlerrm);
1402
1403 RETURN G_RET_STS_UNEXP_ERROR ;
1404
1405 END Update_Row;
1406
1407 -------------------------------------
1408 -- Update_Row for:OKC_CONTRACT_DOCS --
1409 -------------------------------------
1410 PROCEDURE Update_Row(
1411 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1412
1413 x_return_status OUT NOCOPY VARCHAR2,
1414
1415 p_business_document_type IN VARCHAR2,
1416 p_business_document_id IN NUMBER,
1417 p_business_document_version IN NUMBER,
1418 p_attached_document_id IN NUMBER,
1419
1420 p_external_visibility_flag IN VARCHAR2 := NULL,
1421 p_effective_from_type IN VARCHAR2 := NULL,
1422 p_effective_from_id IN NUMBER := NULL,
1423 p_effective_from_version IN NUMBER := NULL,
1424 p_include_for_approval_flag IN VARCHAR2 := NULL,
1425 p_program_id IN NUMBER := NULL,
1426 p_program_application_id IN NUMBER := NULL,
1427 p_request_id IN NUMBER := NULL,
1428 p_program_update_date IN DATE := NULL,
1429 p_parent_attached_doc_id IN NUMBER := NULL,
1430 p_delete_flag IN VARCHAR2 := NULL,
1431 p_generated_flag IN VARCHAR2 := NULL,
1432
1433 p_primary_contract_doc_flag IN VARCHAR2 := NULL,
1434 p_mergeable_doc_flag IN VARCHAR2 := NULL,
1435
1436 p_object_version_number IN NUMBER
1437
1438 ) IS
1439 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
1440 l_external_visibility_flag OKC_CONTRACT_DOCS.EXTERNAL_VISIBILITY_FLAG%TYPE;
1441 l_effective_from_type OKC_CONTRACT_DOCS.EFFECTIVE_FROM_TYPE%TYPE;
1442 l_effective_from_id OKC_CONTRACT_DOCS.EFFECTIVE_FROM_ID%TYPE;
1443 l_effective_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
1444 l_include_for_approval_flag OKC_CONTRACT_DOCS.INCLUDE_FOR_APPROVAL_FLAG%TYPE;
1445 l_program_id OKC_CONTRACT_DOCS.PROGRAM_ID%TYPE;
1446 l_program_application_id OKC_CONTRACT_DOCS.PROGRAM_APPLICATION_ID%TYPE;
1447 l_request_id OKC_CONTRACT_DOCS.REQUEST_ID%TYPE;
1448 l_program_update_date OKC_CONTRACT_DOCS.PROGRAM_UPDATE_DATE%TYPE;
1449 l_parent_attached_doc_id OKC_CONTRACT_DOCS.PARENT_ATTACHED_DOC_ID%TYPE;
1450 l_delete_flag OKC_CONTRACT_DOCS.DELETE_FLAG%TYPE;
1451 l_generated_flag OKC_CONTRACT_DOCS.GENERATED_FLAG%TYPE;
1452 l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE;
1453 l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
1454 l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
1455 l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
1456 l_primary_contract_doc_flag OKC_CONTRACT_DOCS.PRIMARY_CONTRACT_DOC_FLAG%TYPE;
1457 l_mergeable_doc_flag OKC_CONTRACT_DOCS.MERGEABLE_DOC_FLAG%TYPE;
1458
1459 BEGIN
1460
1461 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1462 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7000: Entered Update_Row');
1463 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7100: Locking _B row');
1464 END IF;
1465
1466 x_return_status := Lock_row(
1467 p_business_document_type => p_business_document_type,
1468 p_business_document_id => p_business_document_id,
1469 p_business_document_version => p_business_document_version,
1470 p_attached_document_id => p_attached_document_id,
1471 p_object_version_number => p_object_version_number
1472 );
1473 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1475 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1476 RAISE FND_API.G_EXC_ERROR;
1477 END IF;
1478
1479
1480 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1481 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7300: Setting attributes');
1482 END IF;
1483
1484 l_object_version_number := p_object_version_number;
1485 x_return_status := Set_Attributes(
1486 p_business_document_type => p_business_document_type,
1487 p_business_document_id => p_business_document_id,
1488 p_business_document_version => p_business_document_version,
1489 p_attached_document_id => p_attached_document_id,
1490 p_external_visibility_flag => p_external_visibility_flag,
1491 p_effective_from_type => p_effective_from_type,
1492 p_effective_from_id => p_effective_from_id,
1493 p_effective_from_version => p_effective_from_version,
1494 p_include_for_approval_flag => p_include_for_approval_flag,
1495 p_program_id => p_program_id,
1496 p_program_application_id => p_program_application_id,
1497 p_request_id => p_request_id,
1498 p_program_update_date => p_program_update_date,
1499 p_parent_attached_doc_id => p_parent_attached_doc_id,
1500 p_delete_flag => p_delete_flag,
1501 p_generated_flag => p_generated_flag,
1502 p_object_version_number => l_object_version_number,
1503 p_primary_contract_doc_flag => p_primary_contract_doc_flag,
1504 p_mergeable_doc_flag => p_mergeable_doc_flag,
1505
1506 x_external_visibility_flag => l_external_visibility_flag,
1507 x_effective_from_type => l_effective_from_type,
1508 x_effective_from_id => l_effective_from_id,
1509 x_effective_from_version => l_effective_from_version,
1510 x_include_for_approval_flag => l_include_for_approval_flag,
1511 x_program_id => l_program_id,
1512 x_program_application_id => l_program_application_id,
1513 x_request_id => l_request_id,
1514 x_program_update_date => l_program_update_date,
1515 x_parent_attached_doc_id => l_parent_attached_doc_id,
1516 x_delete_flag => l_delete_flag,
1517 x_generated_flag => l_generated_flag,
1518 x_primary_contract_doc_flag => l_primary_contract_doc_flag,
1519 x_mergeable_doc_flag => l_mergeable_doc_flag
1520 );
1521 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1523 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1524 RAISE FND_API.G_EXC_ERROR;
1525 END IF;
1526
1527 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1528 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7400: Record Validation');
1529 END IF;
1530
1531 --- Validate all non-missing attributes
1532 x_return_status := Validate_Record(
1533 p_validation_level => p_validation_level,
1534 p_business_document_type => p_business_document_type,
1535 p_business_document_id => p_business_document_id,
1536 p_business_document_version => p_business_document_version,
1537 p_attached_document_id => p_attached_document_id,
1538 p_external_visibility_flag => l_external_visibility_flag,
1539 p_effective_from_type => l_effective_from_type,
1540 p_effective_from_id => l_effective_from_id,
1541 p_effective_from_version => l_effective_from_version,
1542 p_include_for_approval_flag => l_include_for_approval_flag,
1543 p_program_id => l_program_id,
1544 p_program_application_id => l_program_application_id,
1545 p_request_id => l_request_id,
1546 p_program_update_date => l_program_update_date,
1547 p_parent_attached_doc_id => l_parent_attached_doc_id,
1548 p_delete_flag => l_delete_flag,
1549 p_generated_flag => l_generated_flag,
1550 p_primary_contract_doc_flag => l_primary_contract_doc_flag,
1551 p_mergeable_doc_flag => l_mergeable_doc_flag
1552 );
1553 --- If any errors happen abort API
1554 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1556 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1557 RAISE FND_API.G_EXC_ERROR;
1558 END IF;
1559
1560 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1561 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7500: Filling WHO columns');
1562 END IF;
1563
1564 -- Filling who columns
1565 l_last_update_date := SYSDATE;
1566 l_last_updated_by := FND_GLOBAL.USER_ID;
1567 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1568
1569 -- Object version increment
1570 -- IF Nvl(p_object_version_number, 0) >= 0 THEN
1571 -- l_object_version_number := Nvl( p_object_version_number, 0) + 1;
1572 -- END IF;
1573 l_object_version_number := l_object_version_number + 1; -- l_object_version_number should not be NULL because of Set_Attribute
1574
1575 --------------------------------------------
1576 -- Call the Update_Row for each child record
1577 --------------------------------------------
1578 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1579 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7600: Updating Row');
1580 END IF;
1581
1582 x_return_status := Update_Row(
1583 p_business_document_type => p_business_document_type,
1584 p_business_document_id => p_business_document_id,
1585 p_business_document_version => p_business_document_version,
1586 p_attached_document_id => p_attached_document_id,
1587 p_external_visibility_flag => l_external_visibility_flag,
1588 p_effective_from_type => l_effective_from_type,
1589 p_effective_from_id => l_effective_from_id,
1590 p_effective_from_version => l_effective_from_version,
1591 p_include_for_approval_flag => l_include_for_approval_flag,
1592 p_program_id => l_program_id,
1593 p_program_application_id => l_program_application_id,
1594 p_request_id => l_request_id,
1595 p_program_update_date => l_program_update_date,
1596 p_parent_attached_doc_id => l_parent_attached_doc_id,
1597 p_delete_flag => l_delete_flag,
1598 p_generated_flag => l_generated_flag,
1599 p_object_version_number => l_object_version_number,
1600 p_last_updated_by => l_last_updated_by,
1601 p_last_update_login => l_last_update_login,
1602 p_last_update_date => l_last_update_date,
1603 p_primary_contract_doc_flag => l_primary_contract_doc_flag,
1604 p_mergeable_doc_flag => l_mergeable_doc_flag
1605 );
1606 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1608 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1609 RAISE FND_API.G_EXC_ERROR;
1610 END IF;
1611
1612
1613 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1614 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7800: Leaving Update_Row');
1615 END IF;
1616
1617 EXCEPTION
1618 WHEN FND_API.G_EXC_ERROR THEN
1619 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1620 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception');
1621 END IF;
1622 x_return_status := G_RET_STS_ERROR;
1623
1624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1625 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1626 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1627 END IF;
1628 x_return_status := G_RET_STS_UNEXP_ERROR;
1629
1630 WHEN OTHERS THEN
1631 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1632 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm);
1633 END IF;
1634 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1635 p_msg_name => G_UNEXPECTED_ERROR,
1636 p_token1 => G_SQLCODE_TOKEN,
1637 p_token1_value => sqlcode,
1638 p_token2 => G_SQLERRM_TOKEN,
1639 p_token2_value => sqlerrm);
1640 x_return_status := G_RET_STS_UNEXP_ERROR;
1641
1642 END Update_Row;
1643
1644 ---------------------------------------------------------------------------
1645 -- PROCEDURE Delete_Row
1646 ---------------------------------------------------------------------------
1647 -------------------------------------
1648 -- Delete_Row for:OKC_CONTRACT_DOCS --
1649 -------------------------------------
1650 FUNCTION Delete_Row(
1651 p_business_document_type IN VARCHAR2,
1652 p_business_document_id IN NUMBER,
1653 p_business_document_version IN NUMBER,
1654 p_attached_document_id IN NUMBER
1655 ) RETURN VARCHAR2 IS
1656 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
1657 BEGIN
1658
1659 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1660 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8200: Entered Delete_Row');
1661 END IF;
1662
1663 DELETE FROM OKC_CONTRACT_DOCS
1664 WHERE BUSINESS_DOCUMENT_TYPE = p_BUSINESS_DOCUMENT_TYPE AND BUSINESS_DOCUMENT_ID = p_BUSINESS_DOCUMENT_ID AND BUSINESS_DOCUMENT_VERSION = p_BUSINESS_DOCUMENT_VERSION AND ATTACHED_DOCUMENT_ID = p_ATTACHED_DOCUMENT_ID;
1665
1666 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1667 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8300: Leaving Delete_Row');
1668 END IF;
1669
1670 RETURN( G_RET_STS_SUCCESS );
1671
1672 EXCEPTION
1673 WHEN OTHERS THEN
1674
1675 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1676 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm);
1677 END IF;
1678
1679 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1680 p_msg_name => G_UNEXPECTED_ERROR,
1681 p_token1 => G_SQLCODE_TOKEN,
1682 p_token1_value => sqlcode,
1683 p_token2 => G_SQLERRM_TOKEN,
1684 p_token2_value => sqlerrm);
1685
1686 RETURN( G_RET_STS_UNEXP_ERROR );
1687
1688 END Delete_Row;
1689
1690 -------------------------------------
1691 -- Delete_Row for:OKC_CONTRACT_DOCS --
1692 -------------------------------------
1693 PROCEDURE Delete_Row(
1694 x_return_status OUT NOCOPY VARCHAR2,
1695 p_business_document_type IN VARCHAR2,
1696 p_business_document_id IN NUMBER,
1697 p_business_document_version IN NUMBER,
1698 p_attached_document_id IN NUMBER,
1699 p_object_version_number IN NUMBER
1700 ) IS
1701 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
1702 BEGIN
1703
1704 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1705 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8800: Entered Delete_Row');
1706 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8900: Locking _B row');
1707 END IF;
1708
1709 x_return_status := Lock_row(
1710 p_business_document_type => p_business_document_type,
1711 p_business_document_id => p_business_document_id,
1712 p_business_document_version => p_business_document_version,
1713 p_attached_document_id => p_attached_document_id,
1714 p_object_version_number => p_object_version_number
1715 );
1716 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1717 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1718 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1719 RAISE FND_API.G_EXC_ERROR;
1720 END IF;
1721
1722
1723 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1724 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9100: Removing _B row');
1725 END IF;
1726 x_return_status := Delete_Row( p_business_document_type => p_business_document_type,p_business_document_id => p_business_document_id,p_business_document_version => p_business_document_version,p_attached_document_id => p_attached_document_id );
1727 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1729 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1730 RAISE FND_API.G_EXC_ERROR;
1731 END IF;
1732
1733
1734 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1735 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9300: Leaving Delete_Row');
1736 END IF;
1737
1738 EXCEPTION
1739 WHEN FND_API.G_EXC_ERROR THEN
1740 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1741 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception');
1742 END IF;
1743 x_return_status := G_RET_STS_ERROR;
1744
1745 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1746 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1747 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1748 END IF;
1749 x_return_status := G_RET_STS_UNEXP_ERROR;
1750
1751 WHEN OTHERS THEN
1752 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1753 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm);
1754 END IF;
1755 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1756 p_msg_name => G_UNEXPECTED_ERROR,
1757 p_token1 => G_SQLCODE_TOKEN,
1758 p_token1_value => sqlcode,
1759 p_token2 => G_SQLERRM_TOKEN,
1760 p_token2_value => sqlerrm);
1761 x_return_status := G_RET_STS_UNEXP_ERROR;
1762
1763 END Delete_Row;
1764
1765
1766 --API name : reset_bus_doc_ver_to_current
1767 --Type : Private.
1768 --Function : When: This API is invoked from the Repository module. It is called when
1769 -- a contract's current version is deleted and a previous version of that contract
1770 -- exists.
1771 -- : What: This API updates the previous version's attachments in OKC_CONTRACT_DOCS.
1772 -- : This function does two things:
1773 -- : 1. Updates the BUSINESS_DOCUMENT_VERSION from its current value to -99.
1774 -- : 2. Updates the EFFECTIVE_FROM_VERSION to -99 for those attachments that were effective
1775 -- : from this current version only (not added from a previous version).
1776 -- : Why: This reset is required since Contract Documents (module) requires attachments of the current
1777 -- : version of a business document (contract) to have a BUSINESS_DOCUMENT_VERSION of -99.
1778 --Pre-reqs : None.
1779 --Parameters :
1780 --IN : p_business_document_type IN VARCHAR2 Required
1781 -- : p_business_document_id IN NUMBER Required
1782 -- : p_business_document_version IN NUMBER Required
1783 --OUT : Returns G_RET_STS_SUCCESS if resetting of version number is succeeded.
1784 --Note : This API is created as part of the fix of bug 5044121
1785 -- End of comments
1786
1787 FUNCTION reset_bus_doc_ver_to_current(
1788 p_business_document_type IN VARCHAR2,
1789 p_business_document_id IN NUMBER,
1790 p_business_document_version IN NUMBER
1791 ) RETURN VARCHAR2 IS
1792 l_api_name VARCHAR2(35);
1793 BEGIN
1794
1795 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1796 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1797 'Entered OKC_CONTRACT_DOCS_PVT.reset_bus_doc_ver_to_current');
1798 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1799 'p_business_document_type is: ' || p_business_document_type);
1800 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1801 'p_business_document_id is: ' || to_char(p_business_document_id));
1802 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1803 'p_business_document_version is: ' || to_char(p_business_document_version));
1804 END IF;
1805
1806 l_api_name := 'reset_bus_doc_ver_to_current';
1807
1808 -- Standard Start of API savepoint
1809 SAVEPOINT reset_bus_doc_ver_to_current;
1810
1811 -- Initialize message list
1812 FND_MSG_PUB.initialize;
1813
1814 -- Update the effective_from_version of the documents added in that version only
1815 UPDATE okc_contract_docs
1816 SET effective_from_version = -99
1817 WHERE delete_flag = 'N'
1818 AND business_document_version = p_business_document_version - 1
1819 AND business_document_id = p_business_document_id
1820 AND business_document_type = p_business_document_type
1821 AND business_document_version = effective_from_version;
1822
1823
1824 -- Update the business_document_version of all the documents of previous version
1825 UPDATE okc_contract_docs
1826 SET business_document_version = -99
1827 WHERE delete_flag = 'N'
1828 AND business_document_version = p_business_document_version - 1
1829 AND business_document_id = p_business_document_id
1830 AND business_document_type = p_business_document_type;
1831
1832
1833 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1834 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1835 'Leaving OKC_CONTRACT_DOCS_PVT.reset_bus_doc_ver_to_current');
1836 END IF;
1837
1838 RETURN(G_RET_STS_SUCCESS);
1839
1840 EXCEPTION
1841 WHEN OTHERS THEN
1842
1843 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1844 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'Leaving OKC_CONTRACT_DOCS_PVT.reset_bus_doc_ver_to_current because of EXCEPTION: '||sqlerrm);
1845 END IF;
1846
1847 ROLLBACK TO reset_bus_doc_ver_to_current;
1848
1849 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1850 p_msg_name => G_UNEXPECTED_ERROR,
1851 p_token1 => G_SQLCODE_TOKEN,
1852 p_token1_value => sqlcode,
1853 p_token2 => G_SQLERRM_TOKEN,
1854 p_token2_value => sqlerrm);
1855
1856 RETURN( G_RET_STS_UNEXP_ERROR );
1857
1858 END reset_bus_doc_ver_to_current;
1859
1860
1861
1862 END OKC_CONTRACT_DOCS_PVT;