[Home] [Help]
PACKAGE BODY: APPS.OKC_REVIEW_UPLD_HEADER_PVT
Source
1 PACKAGE BODY OKC_REVIEW_UPLD_HEADER_PVT AS
2 /* $Header: OKCVRUHB.pls 120.0 2005/09/13 22:47 vnanjang noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
11 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
12 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
13 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
14 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
15 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
16 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
17 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
18 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
19 ---------------------------------------------------------------------------
20 -- VALIDATION LEVELS
21 ---------------------------------------------------------------------------
22 G_REQUIRED_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_REQUIRED_VALUE_VALID_LEVEL;
23 G_VALID_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_VALID_VALUE_VALID_LEVEL;
24 G_LOOKUP_CODE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_LOOKUP_CODE_VALID_LEVEL;
25 G_FOREIGN_KEY_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_FOREIGN_KEY_VALID_LEVEL;
26 G_RECORD_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_RECORD_VALID_LEVEL;
27 ---------------------------------------------------------------------------
28 -- GLOBAL VARIABLES
29 ---------------------------------------------------------------------------
30 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_review_upld_header_PVT';
31 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
32
33 ------------------------------------------------------------------------------
34 -- GLOBAL CONSTANTS
35 ------------------------------------------------------------------------------
36 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
37 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
38 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
39 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
40 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
41
42 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
43 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
44 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
45
46 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
47 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
48 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
49
50 ------------------------------------------------------------------------------
51 -- GLOBAL EXCEPTION
52 ------------------------------------------------------------------------------
53 E_Resource_Busy EXCEPTION;
54 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
55 ---------------------------------------------------------------------------
56 -- FUNCTION get_seq_id
57 ---------------------------------------------------------------------------
58 FUNCTION Get_Seq_Id (
59 p_review_upld_header_id IN NUMBER,
60 x_review_upld_header_id OUT NOCOPY NUMBER
61 ) RETURN VARCHAR2 IS
62 CURSOR l_seq_csr IS
63 SELECT OKC_review_upld_header_S1.NEXTVAL FROM DUAL;
64 BEGIN
65 IF (l_debug = 'Y') THEN
66 Okc_Debug.Log('100: Entered get_seq_id', 2);
67 END IF;
68
69 IF( p_review_upld_header_id IS NULL ) THEN
70 OPEN l_seq_csr;
71 FETCH l_seq_csr INTO x_review_upld_header_id;
72 IF l_seq_csr%NOTFOUND THEN
73 RAISE NO_DATA_FOUND;
74 END IF;
75 CLOSE l_seq_csr;
76 END IF;
77
78 IF (l_debug = 'Y') THEN
79 Okc_Debug.Log('200: Leaving get_seq_id', 2);
80 END IF;
81 RETURN G_RET_STS_SUCCESS;
82 EXCEPTION
83 WHEN OTHERS THEN
84
85 IF (l_debug = 'Y') THEN
86 Okc_Debug.Log('300: Leaving get_seq_id because of EXCEPTION: '||sqlerrm, 2);
87 END IF;
88
89 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
90 p_msg_name => G_UNEXPECTED_ERROR,
91 p_token1 => G_SQLCODE_TOKEN,
92 p_token1_value => sqlcode,
93 p_token2 => G_SQLERRM_TOKEN,
94 p_token2_value => sqlerrm);
95
96 IF l_seq_csr%ISOPEN THEN
97 CLOSE l_seq_csr;
98 END IF;
99
100 RETURN G_RET_STS_UNEXP_ERROR ;
101
102 END Get_Seq_Id;
103
104 ---------------------------------------------------------------------------
105 -- FUNCTION get_rec for: OKC_review_upld_header
106 ---------------------------------------------------------------------------
107 FUNCTION Get_Rec (
108 p_review_upld_header_id IN NUMBER,
109
110 x_file_name OUT NOCOPY VARCHAR2,
111 x_file_content_type OUT NOCOPY VARCHAR2,
112 x_file_data OUT NOCOPY BLOB,
113 x_document_type OUT NOCOPY VARCHAR2,
114 x_document_id OUT NOCOPY NUMBER,
115 x_object_version_number OUT NOCOPY NUMBER,
116 x_created_by OUT NOCOPY NUMBER,
117 x_creation_date OUT NOCOPY DATE,
118 x_last_updated_by OUT NOCOPY NUMBER,
119 x_last_update_login OUT NOCOPY NUMBER,
120 x_last_update_date OUT NOCOPY DATE,
121 x_new_contract_source OUT NOCOPY VARCHAR2,
122 x_enable_reporting_flag OUT NOCOPY VARCHAR2,
123 x_file_description OUT NOCOPY VARCHAR2
124
125 ) RETURN VARCHAR2 IS
126 CURSOR OKC_review_upld_header_pk_csr (cp_review_upld_header_id IN NUMBER) IS
127 SELECT
128 FILE_NAME,
129 FILE_CONTENT_TYPE,
130 FILE_DATA,
131 DOCUMENT_TYPE,
132 DOCUMENT_ID,
133 OBJECT_VERSION_NUMBER,
134 CREATED_BY,
135 CREATION_DATE,
136 LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN,
138 LAST_UPDATE_DATE,
139 new_contract_source,
140 enable_reporting_flag,
141 file_description
142 FROM OKC_review_upld_header t
143 WHERE t.review_upld_header_ID = cp_review_upld_header_id;
144 BEGIN
145
146 IF (l_debug = 'Y') THEN
147 Okc_Debug.Log('400: Entered get_rec', 2);
148 END IF;
149
150 -- Get current database values
151 OPEN OKC_review_upld_header_pk_csr (p_review_upld_header_id);
152 FETCH OKC_review_upld_header_pk_csr INTO
153 x_file_name,
154 x_file_content_type,
155 x_file_data,
156 x_document_type,
157 x_document_id,
158 x_object_version_number,
159 x_created_by,
160 x_creation_date,
161 x_last_updated_by,
162 x_last_update_login,
163 x_last_update_date,
164 x_new_contract_source,
165 x_enable_reporting_flag,
166 x_file_description ;
167 IF OKC_review_upld_header_pk_csr%NOTFOUND THEN
168 RAISE NO_DATA_FOUND;
169 END IF;
170 CLOSE OKC_review_upld_header_pk_csr;
171
172 IF (l_debug = 'Y') THEN
173 Okc_Debug.Log('500: Leaving get_rec ', 2);
174 END IF;
175
176 RETURN G_RET_STS_SUCCESS ;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180
181 IF (l_debug = 'Y') THEN
182 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
183 END IF;
184
185 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
186 p_msg_name => G_UNEXPECTED_ERROR,
187 p_token1 => G_SQLCODE_TOKEN,
188 p_token1_value => sqlcode,
189 p_token2 => G_SQLERRM_TOKEN,
190 p_token2_value => sqlerrm);
191
192 IF OKC_review_upld_header_pk_csr%ISOPEN THEN
193 CLOSE OKC_review_upld_header_pk_csr;
194 END IF;
195
196 RETURN G_RET_STS_UNEXP_ERROR ;
197
198 END Get_Rec;
199
200 -----------------------------------------
201 -- Set_Attributes for:OKC_review_upld_header --
202 -----------------------------------------
203 FUNCTION Set_Attributes(
204 p_review_upld_header_id IN NUMBER,
205 p_file_name IN VARCHAR2,
206 p_file_content_type IN VARCHAR2,
207 p_file_data IN BLOB,
208 p_document_type IN VARCHAR2,
209 p_document_id IN NUMBER,
210 p_object_version_number IN OUT NOCOPY NUMBER,
211 p_new_contract_source IN VARCHAR2,
212 p_enable_reporting_flag IN VARCHAR2,
213 p_file_description IN VARCHAR2,
214 x_file_name OUT NOCOPY VARCHAR2,
215 x_file_content_type OUT NOCOPY VARCHAR2,
216 x_file_data OUT NOCOPY BLOB,
217 x_document_type OUT NOCOPY VARCHAR2,
218 x_document_id OUT NOCOPY NUMBER,
219 x_new_contract_source OUT NOCOPY VARCHAR2,
220 x_enable_reporting_flag OUT NOCOPY VARCHAR2,
221 x_file_description OUT NOCOPY VARCHAR2
222 ) RETURN VARCHAR2 IS
223 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
224 l_object_version_number OKC_review_upld_header.OBJECT_VERSION_NUMBER%TYPE;
225 l_created_by OKC_review_upld_header.CREATED_BY%TYPE;
226 l_creation_date OKC_review_upld_header.CREATION_DATE%TYPE;
227 l_last_updated_by OKC_review_upld_header.LAST_UPDATED_BY%TYPE;
228 l_last_update_login OKC_review_upld_header.LAST_UPDATE_LOGIN%TYPE;
229 l_last_update_date OKC_review_upld_header.LAST_UPDATE_DATE%TYPE;
230 BEGIN
231 IF (l_debug = 'Y') THEN
232 Okc_Debug.Log('700: Entered Set_Attributes ', 2);
233 END IF;
234
235 IF( p_review_upld_header_id IS NOT NULL ) THEN
236 -- Get current database values
237 l_return_status := Get_Rec(
238 p_review_upld_header_id => p_review_upld_header_id,
239 x_file_name => x_file_name,
240 x_file_content_type => x_file_content_type,
241 x_file_data => x_file_data,
242 x_document_type => x_document_type,
243 x_document_id => x_document_id,
244 x_new_contract_source => x_new_contract_source,
245 x_enable_reporting_flag => x_enable_reporting_flag,
246 x_file_description => x_file_description,
247 x_object_version_number => l_object_version_number,
248 x_created_by => l_created_by,
249 x_creation_date => l_creation_date,
250 x_last_updated_by => l_last_updated_by,
251 x_last_update_login => l_last_update_login,
252 x_last_update_date => l_last_update_date
253 );
254 --- If any errors happen abort API
255 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
258 RAISE FND_API.G_EXC_ERROR;
259 END IF;
260
261 --- Reversing G_MISS/NULL values logic
262
263 IF (p_file_name = G_MISS_CHAR) THEN
264 x_file_name := NULL;
265 ELSIF (p_file_name IS NOT NULL) THEN
266 x_file_name := p_file_name;
267 END IF;
268
269 IF (p_file_content_type = G_MISS_CHAR) THEN
270 x_file_content_type := NULL;
271 ELSIF (p_file_content_type IS NOT NULL) THEN
272 x_file_content_type := p_file_content_type;
273 END IF;
274
275 IF (p_file_data IS NOT NULL) THEN
276 x_file_data := p_file_data;
277 END IF;
278
279 IF (p_document_type = G_MISS_CHAR) THEN
280 x_document_type := NULL;
281 ELSIF (p_document_type IS NOT NULL) THEN
282 x_document_type := p_document_type;
283 END IF;
284
285 IF (p_document_id = G_MISS_NUM) THEN
286 x_document_id := NULL;
287 ELSIF (p_document_id IS NOT NULL) THEN
288 x_document_id := p_document_id;
289 END IF;
290
291
292 IF (p_object_version_number IS NULL) THEN
293 p_object_version_number := l_object_version_number;
294 END IF;
295
296
297 IF (p_new_contract_source = G_MISS_CHAR) THEN
298 x_new_contract_source := NULL;
299 ELSIF (p_new_contract_source IS NOT NULL) THEN
300 x_new_contract_source := p_new_contract_source;
301 END IF;
302
303
304
305 IF (p_enable_reporting_flag = G_MISS_CHAR) THEN
306 x_enable_reporting_flag := NULL;
307 ELSIF (p_enable_reporting_flag IS NOT NULL) THEN
308 x_enable_reporting_flag := p_enable_reporting_flag;
309 END IF;
310
311
312 IF (p_file_description = G_MISS_CHAR) THEN
313 x_file_description := NULL;
314 ELSIF (p_file_description IS NOT NULL) THEN
315 x_file_description := p_file_description;
316 END IF;
317
318
319
320 END IF;
321
322 IF (l_debug = 'Y') THEN
323 Okc_Debug.Log('800: Leaving Set_Attributes ', 2);
324 END IF;
325
326 RETURN G_RET_STS_SUCCESS ;
327 EXCEPTION
328 WHEN FND_API.G_EXC_ERROR THEN
329 IF (l_debug = 'Y') THEN
330 Okc_Debug.Log('900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception', 2);
331 END IF;
332 RETURN G_RET_STS_ERROR;
333
334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
335 IF (l_debug = 'Y') THEN
336 Okc_Debug.Log('1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
337 END IF;
338 RETURN G_RET_STS_UNEXP_ERROR;
339
340 WHEN OTHERS THEN
341 IF (l_debug = 'Y') THEN
342 Okc_Debug.Log('1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm, 2);
343 END IF;
344 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
345 p_msg_name => G_UNEXPECTED_ERROR,
346 p_token1 => G_SQLCODE_TOKEN,
347 p_token1_value => sqlcode,
348 p_token2 => G_SQLERRM_TOKEN,
349 p_token2_value => sqlerrm);
350 RETURN G_RET_STS_UNEXP_ERROR;
351
352 END Set_Attributes ;
353
354 ----------------------------------------------
355 -- Validate_Attributes for: OKC_review_upld_header --
356 ----------------------------------------------
357 FUNCTION Validate_Attributes (
358 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
359
360 p_review_upld_header_id IN NUMBER,
361 p_file_name IN VARCHAR2,
362 p_file_content_type IN VARCHAR2,
363 p_file_data IN BLOB,
364 p_document_type IN VARCHAR2,
365 p_document_id IN NUMBER,
366 p_new_contract_source IN VARCHAR2,
367 p_enable_reporting_flag IN VARCHAR2,
368 p_file_description IN VARCHAR2
369 ) RETURN VARCHAR2 IS
370 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
371 l_dummy_var VARCHAR2(1) := '?';
372 /* ?? uncomment next part after you check and change this foreign key validation
373
374 CURSOR l_review_upld_header_id_csr is
375 SELECT '!'
376 FROM ??unknown_table??
377 WHERE ??review_upld_header_ID?? = p_review_upld_header_id;
378
379 CURSOR l_document_id_csr is
380 SELECT '!'
381 FROM ??unknown_table??
382 WHERE ??DOCUMENT_ID?? = p_document_id;
383
384 */
385 BEGIN
386
387 IF (l_debug = 'Y') THEN
388 Okc_Debug.Log('1200: Entered Validate_Attributes', 2);
389 END IF;
390
391 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
392 IF (l_debug = 'Y') THEN
393 Okc_Debug.Log('1300: required values validation', 2);
394 END IF;
395
396 IF (l_debug = 'Y') THEN
397 Okc_Debug.Log('1400: - attribute review_upld_header_ID ', 2);
398 END IF;
399 IF ( p_review_upld_header_id IS NULL) THEN
400 IF (l_debug = 'Y') THEN
401 Okc_Debug.Log('1500: - attribute review_upld_header_ID is invalid', 2);
402 END IF;
403 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'review_upld_header_ID');
404 l_return_status := G_RET_STS_ERROR;
405 END IF;
406
407 END IF;
408
409 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
410 IF (l_debug = 'Y') THEN
411 Okc_Debug.Log('1600: static values and range validation', 2);
412 END IF;
413
414 END IF;
415
416 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
417 IF (l_debug = 'Y') THEN
418 Okc_Debug.Log('1900: lookup codes validation', 2);
419 END IF;
420 /* ?? uncomment next part after you check and change this lokkup codes validation
421
422 */
423 END IF;
424
425 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
426 IF (l_debug = 'Y') THEN
427 Okc_Debug.Log('2100: foreigh keys validation ', 2);
428 END IF;
429 /* ?? uncomment next part after you check and change this foreign key validation
430
431 IF (l_debug = 'Y') THEN
432 Okc_Debug.Log('2200: - attribute review_upld_header_ID ', 2);
433 END IF;
434 IF p_review_upld_header_id IS NOT NULL THEN
435 l_dummy_var := '?';
436 OPEN l_review_upld_header_id_csr;
437 FETCH l_review_upld_header_id_csr INTO l_dummy_var;
438 CLOSE l_review_upld_header_id_csr;
439 IF (l_dummy_var = '?') THEN
440 IF (l_debug = 'Y') THEN
441 Okc_Debug.Log('2300: - attribute review_upld_header_ID is invalid', 2);
442 END IF;
443 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'review_upld_header_ID');
444 l_return_status := G_RET_STS_ERROR;
445 END IF;
446 END IF;
447
448 IF (l_debug = 'Y') THEN
449 Okc_Debug.Log('2200: - attribute DOCUMENT_ID ', 2);
450 END IF;
451 IF p_document_id IS NOT NULL THEN
452 l_dummy_var := '?';
453 OPEN l_document_id_csr;
454 FETCH l_document_id_csr INTO l_dummy_var;
455 CLOSE l_document_id_csr;
456 IF (l_dummy_var = '?') THEN
457 IF (l_debug = 'Y') THEN
458 Okc_Debug.Log('2300: - attribute DOCUMENT_ID is invalid', 2);
459 END IF;
460 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DOCUMENT_ID');
461 l_return_status := G_RET_STS_ERROR;
462 END IF;
463 END IF;
464
465 */
466 END IF;
467
468
469 IF (l_debug = 'Y') THEN
470 Okc_Debug.Log('2400: Leaving Validate_Attributes ', 2);
471 END IF;
472
473 RETURN l_return_status;
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 Okc_Debug.Log('2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
478 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
479 p_msg_name => G_UNEXPECTED_ERROR,
480 p_token1 => G_SQLCODE_TOKEN,
481 p_token1_value => sqlcode,
482 p_token2 => G_SQLERRM_TOKEN,
483 p_token2_value => sqlerrm);
484
485 /* ?? uncomment next part after you check and change this foreign key validation
486
487 IF l_review_upld_header_id_csr%ISOPEN THEN
488 CLOSE l_review_upld_header_id_csr;
489 END IF;
490
491 IF l_document_id_csr%ISOPEN THEN
492 CLOSE l_document_id_csr;
493 END IF;
494
495 */
496 RETURN G_RET_STS_UNEXP_ERROR;
497
498 END Validate_Attributes;
499
500
501 ---------------------------------------------------------------------------
502 -- PROCEDURE Validate_Record
503 -- It calls Item Level Validations and then makes Record Level Validations
504 ---------------------------------------------------------------------------
505 ------------------------------------------
506 -- Validate_Record for:OKC_review_upld_header --
507 ------------------------------------------
508 FUNCTION Validate_Record (
509 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
510
511 p_review_upld_header_id IN NUMBER,
512 p_file_name IN VARCHAR2,
513 p_file_content_type IN VARCHAR2,
514 p_file_data IN BLOB,
515 p_document_type IN VARCHAR2,
516 p_document_id IN NUMBER,
517 p_new_contract_source IN VARCHAR2,
518 p_enable_reporting_flag IN VARCHAR2,
519 p_file_description IN VARCHAR2
520
521 ) RETURN VARCHAR2 IS
522 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
523 BEGIN
524
525 IF (l_debug = 'Y') THEN
526 Okc_Debug.Log('2600: Entered Validate_Record', 2);
527 END IF;
528
529 --- Validate all non-missing attributes (Item Level Validation)
530 l_return_status := Validate_Attributes(
531 p_validation_level => p_validation_level,
532
533 p_review_upld_header_id => p_review_upld_header_id,
534 p_file_name => p_file_name,
535 p_file_content_type => p_file_content_type,
536 p_file_data => p_file_data,
537 p_document_type => p_document_type,
538 p_document_id => p_document_id,
539 p_new_contract_source => p_new_contract_source,
540 p_enable_reporting_flag => p_enable_reporting_flag,
541 p_file_description => p_file_description
542 );
543 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
544 IF (l_debug = 'Y') THEN
545 Okc_Debug.Log('2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
546 END IF;
547 RETURN G_RET_STS_UNEXP_ERROR;
548 END IF;
549
550 --- Record Level Validation
551 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
552 IF (l_debug = 'Y') THEN
553 Okc_Debug.Log('2800: Entered Record Level Validations', 2);
554 END IF;
555 /*+++++++++++++start of hand code +++++++++++++++++++*/
556 -- ?? manual coding for Record Level Validations if required ??
557 /*+++++++++++++End of hand code +++++++++++++++++++*/
558 END IF;
559
560 IF (l_debug = 'Y') THEN
561 Okc_Debug.Log('2900: Leaving Validate_Record : '||sqlerrm, 2);
562 END IF;
563 RETURN l_return_status ;
564
565 EXCEPTION
566 WHEN OTHERS THEN
567
568 IF (l_debug = 'Y') THEN
569 Okc_Debug.Log('3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
570 END IF;
571
572 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
573 p_msg_name => G_UNEXPECTED_ERROR,
574 p_token1 => G_SQLCODE_TOKEN,
575 p_token1_value => sqlcode,
576 p_token2 => G_SQLERRM_TOKEN,
577 p_token2_value => sqlerrm);
578 RETURN G_RET_STS_UNEXP_ERROR ;
579
580 END Validate_Record;
581
582 ---------------------------------------------------------------------------
583 -- PROCEDURE validate_row
584 ---------------------------------------------------------------------------
585 ---------------------------------------
586 -- validate_row for:OKC_review_upld_header --
587 ---------------------------------------
588 PROCEDURE validate_row(
589 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
590
591 x_return_status OUT NOCOPY VARCHAR2,
592
593 p_review_upld_header_id IN NUMBER,
594 p_file_name IN VARCHAR2,
595 p_file_content_type IN VARCHAR2,
596 p_file_data IN BLOB,
597 p_document_type IN VARCHAR2,
598 p_document_id IN NUMBER,
599 p_new_contract_source IN VARCHAR2,
600 p_enable_reporting_flag IN VARCHAR2,
601 p_file_description IN VARCHAR2,
602
603 p_object_version_number IN NUMBER
604 ) IS
605 l_file_name OKC_review_upld_header.FILE_NAME%TYPE;
606 l_file_content_type OKC_review_upld_header.FILE_CONTENT_TYPE%TYPE;
607 l_file_data OKC_review_upld_header.FILE_DATA%TYPE;
608 l_document_type OKC_review_upld_header.DOCUMENT_TYPE%TYPE;
609 l_document_id OKC_review_upld_header.DOCUMENT_ID%TYPE;
610 l_object_version_number OKC_review_upld_header.OBJECT_VERSION_NUMBER%TYPE;
611 l_created_by OKC_review_upld_header.CREATED_BY%TYPE;
612 l_creation_date OKC_review_upld_header.CREATION_DATE%TYPE;
613 l_last_updated_by OKC_review_upld_header.LAST_UPDATED_BY%TYPE;
614 l_last_update_login OKC_review_upld_header.LAST_UPDATE_LOGIN%TYPE;
615 l_last_update_date OKC_review_upld_header.LAST_UPDATE_DATE%TYPE;
616 l_new_contract_source OKC_review_upld_header.new_contract_source%TYPE;
617 l_enable_reporting_flag OKC_review_upld_header.enable_reporting_flag%TYPE;
618 l_file_description OKC_review_upld_header.file_description%TYPE;
619
620 BEGIN
621
622 IF (l_debug = 'Y') THEN
623 Okc_Debug.Log('3100: Entered validate_row', 2);
624 END IF;
625
626 -- Setting attributes
627 x_return_status := Set_Attributes(
628 p_review_upld_header_id => p_review_upld_header_id,
629 p_file_name => p_file_name,
630 p_file_content_type => p_file_content_type,
631 p_file_data => p_file_data,
632 p_document_type => p_document_type,
633 p_document_id => p_document_id,
634 p_new_contract_source => p_new_contract_source,
635 p_enable_reporting_flag => p_enable_reporting_flag,
636 p_file_description => p_file_description,
637
638 p_object_version_number => l_object_version_number,
639 x_file_name => l_file_name,
640 x_file_content_type => l_file_content_type,
641 x_file_data => l_file_data,
642 x_document_type => l_document_type,
643 x_document_id => l_document_id,
644 x_new_contract_source => l_new_contract_source,
645 x_enable_reporting_flag => l_enable_reporting_flag,
646 x_file_description => l_file_description
647
648 );
649 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
651 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
652 RAISE FND_API.G_EXC_ERROR;
653 END IF;
654
655 -- Validate all non-missing attributes (Item Level Validation)
656 l_object_version_number := p_object_version_number ;
657 x_return_status := Validate_Record(
658 p_validation_level => p_validation_level,
659 p_review_upld_header_id => p_review_upld_header_id,
660 p_file_name => l_file_name,
661 p_file_content_type => l_file_content_type,
662 p_file_data => l_file_data,
663 p_document_type => l_document_type,
664 p_document_id => l_document_id,
665 p_new_contract_source => l_new_contract_source,
666 p_enable_reporting_flag => l_enable_reporting_flag,
667 p_file_description => l_file_description
668
669 );
670
671 IF (l_debug = 'Y') THEN
672 Okc_Debug.Log('3200: Leaving validate_row', 2);
673 END IF;
674
675 EXCEPTION
676 WHEN FND_API.G_EXC_ERROR THEN
677 IF (l_debug = 'Y') THEN
678 Okc_Debug.Log('3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception', 2);
679 END IF;
680 x_return_status := G_RET_STS_ERROR;
681
682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
683 IF (l_debug = 'Y') THEN
684 Okc_Debug.Log('3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
685 END IF;
686 x_return_status := G_RET_STS_UNEXP_ERROR;
687
688 WHEN OTHERS THEN
689 IF (l_debug = 'Y') THEN
690 Okc_Debug.Log('3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
691 END IF;
692 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
693 p_msg_name => G_UNEXPECTED_ERROR,
694 p_token1 => G_SQLCODE_TOKEN,
695 p_token1_value => sqlcode,
696 p_token2 => G_SQLERRM_TOKEN,
697 p_token2_value => sqlerrm);
698 x_return_status := G_RET_STS_UNEXP_ERROR;
699
700 END Validate_Row;
701
702 ---------------------------------------------------------------------------
703 -- PROCEDURE Insert_Row
704 ---------------------------------------------------------------------------
705 -------------------------------------
706 -- Insert_Row for:OKC_review_upld_header --
707 -------------------------------------
708 FUNCTION Insert_Row(
709 p_review_upld_header_id IN NUMBER,
710 p_file_name IN VARCHAR2,
711 p_file_content_type IN VARCHAR2,
712 p_file_data IN BLOB,
713 p_document_type IN VARCHAR2,
714 p_document_id IN NUMBER,
715 p_object_version_number IN NUMBER,
716 p_created_by IN NUMBER,
717 p_creation_date IN DATE,
718 p_last_updated_by IN NUMBER,
719 p_last_update_login IN NUMBER,
720 p_last_update_date IN DATE,
721 p_new_contract_source IN VARCHAR2,
722 p_enable_reporting_flag IN VARCHAR2,
723 p_file_description IN VARCHAR2
724
725 ) RETURN VARCHAR2 IS
726
727 BEGIN
728
729 IF (l_debug = 'Y') THEN
730 Okc_Debug.Log('3600: Entered Insert_Row function', 2);
731 END IF;
732
733 INSERT INTO OKC_review_upld_header(
734 review_upld_header_ID,
735 FILE_NAME,
736 FILE_CONTENT_TYPE,
737 FILE_DATA,
738 DOCUMENT_TYPE,
739 DOCUMENT_ID,
740 OBJECT_VERSION_NUMBER,
741 CREATED_BY,
742 CREATION_DATE,
743 LAST_UPDATED_BY,
744 LAST_UPDATE_LOGIN,
745 LAST_UPDATE_DATE,
746 new_contract_source,
747 enable_reporting_flag,
748 file_description)
749 VALUES (
750 p_review_upld_header_id,
751 p_file_name,
752 p_file_content_type,
753 p_file_data,
754 p_document_type,
755 p_document_id,
756 p_object_version_number,
757 p_created_by,
758 p_creation_date,
759 p_last_updated_by,
760 p_last_update_login,
761 p_last_update_date,
762 p_new_contract_source,
763 p_enable_reporting_flag,
764 p_file_description);
765
766 IF (l_debug = 'Y') THEN
767 Okc_Debug.Log('3700: Leaving Insert_Row', 2);
768 END IF;
769
770 RETURN( G_RET_STS_SUCCESS );
771
772 EXCEPTION
773 WHEN OTHERS THEN
774
775 IF (l_debug = 'Y') THEN
776 Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
777 END IF;
778
779 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
780 p_msg_name => G_UNEXPECTED_ERROR,
781 p_token1 => G_SQLCODE_TOKEN,
782 p_token1_value => sqlcode,
783 p_token2 => G_SQLERRM_TOKEN,
784 p_token2_value => sqlerrm);
785
786 RETURN( G_RET_STS_UNEXP_ERROR );
787
788 END Insert_Row;
789
790
791 -------------------------------------
792 -- Insert_Row for:OKC_review_upld_header --
793 -------------------------------------
794 PROCEDURE Insert_Row(
795 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
796 x_return_status OUT NOCOPY VARCHAR2,
797
798 p_review_upld_header_id IN NUMBER,
799 p_file_name IN VARCHAR2,
800 p_file_content_type IN VARCHAR2,
801 p_file_data IN BLOB,
802 p_document_type IN VARCHAR2,
803 p_document_id IN NUMBER,
804 p_new_contract_source IN VARCHAR2,
805 p_enable_reporting_flag IN VARCHAR2,
806 p_file_description IN VARCHAR2,
807
808 x_review_upld_header_id OUT NOCOPY NUMBER
809
810 ) IS
811
812 l_object_version_number OKC_review_upld_header.OBJECT_VERSION_NUMBER%TYPE;
813 l_created_by OKC_review_upld_header.CREATED_BY%TYPE;
814 l_creation_date OKC_review_upld_header.CREATION_DATE%TYPE;
815 l_last_updated_by OKC_review_upld_header.LAST_UPDATED_BY%TYPE;
816 l_last_update_login OKC_review_upld_header.LAST_UPDATE_LOGIN%TYPE;
817 l_last_update_date OKC_review_upld_header.LAST_UPDATE_DATE%TYPE;
818 BEGIN
819
820 x_return_status := G_RET_STS_SUCCESS;
821
822 IF (l_debug = 'Y') THEN
823 Okc_Debug.Log('4200: Entered Insert_Row', 2);
824 END IF;
825
826 --- Setting item attributes
827 -- Set primary key value
828 IF( p_review_upld_header_id IS NULL ) THEN
829 x_return_status := Get_Seq_Id(
830 p_review_upld_header_id => p_review_upld_header_id,
831 x_review_upld_header_id => x_review_upld_header_id
832 );
833 --- If any errors happen abort API
834 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
837 RAISE FND_API.G_EXC_ERROR;
838 END IF;
839 ELSE
840 x_review_upld_header_id := p_review_upld_header_id;
841 END IF;
842 -- Set Internal columns
843 l_object_version_number := 1;
844 l_creation_date := Sysdate;
845 l_created_by := Fnd_Global.User_Id;
846 l_last_update_date := l_creation_date;
847 l_last_updated_by := l_created_by;
848 l_last_update_login := Fnd_Global.Login_Id;
849
850
851 --- Validate all non-missing attributes
852 x_return_status := Validate_Record(
853 p_validation_level => p_validation_level,
854 p_review_upld_header_id => x_review_upld_header_id,
855 p_file_name => p_file_name,
856 p_file_content_type => p_file_content_type,
857 p_file_data => p_file_data,
858 p_document_type => p_document_type,
859 p_document_id => p_document_id,
860 p_new_contract_source => p_new_contract_source,
861 p_enable_reporting_flag => p_enable_reporting_flag,
862 p_file_description => p_file_description
863 );
864 --- If any errors happen abort API
865 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
867 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
868 RAISE FND_API.G_EXC_ERROR;
869 END IF;
870
871 --------------------------------------------
872 -- Call the internal Insert_Row for each child record
873 --------------------------------------------
874 IF (l_debug = 'Y') THEN
875 Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
876 END IF;
877
878 x_return_status := Insert_Row(
879 p_review_upld_header_id => x_review_upld_header_id,
880 p_file_name => p_file_name,
881 p_file_content_type => p_file_content_type,
882 p_file_data => p_file_data,
883 p_document_type => p_document_type,
884 p_document_id => p_document_id,
885 p_object_version_number => l_object_version_number,
886 p_created_by => l_created_by,
887 p_creation_date => l_creation_date,
888 p_last_updated_by => l_last_updated_by,
889 p_last_update_login => l_last_update_login,
890 p_last_update_date => l_last_update_date,
891 p_new_contract_source => p_new_contract_source,
892 p_enable_reporting_flag => p_enable_reporting_flag,
893 p_file_description => p_file_description
894 );
895 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
897 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
898 RAISE FND_API.G_EXC_ERROR;
899 END IF;
900
901
902
903 IF (l_debug = 'Y') THEN
904 Okc_Debug.Log('4500: Leaving Insert_Row', 2);
905 END IF;
906
907 EXCEPTION
908 WHEN FND_API.G_EXC_ERROR THEN
909 IF (l_debug = 'Y') THEN
910 Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
911 END IF;
912 x_return_status := G_RET_STS_ERROR;
913
914 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
915 IF (l_debug = 'Y') THEN
916 Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
917 END IF;
918 x_return_status := G_RET_STS_UNEXP_ERROR;
919
920 WHEN OTHERS THEN
921 IF (l_debug = 'Y') THEN
922 Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
923 END IF;
924 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
925 p_msg_name => G_UNEXPECTED_ERROR,
926 p_token1 => G_SQLCODE_TOKEN,
927 p_token1_value => sqlcode,
928 p_token2 => G_SQLERRM_TOKEN,
929 p_token2_value => sqlerrm);
930 x_return_status := G_RET_STS_UNEXP_ERROR;
931
932 END Insert_Row;
933 ---------------------------------------------------------------------------
934 -- PROCEDURE Lock_Row
935 ---------------------------------------------------------------------------
936 -----------------------------------
937 -- Lock_Row for:OKC_review_upld_header --
938 -----------------------------------
939 FUNCTION Lock_Row(
940 p_review_upld_header_id IN NUMBER,
941 p_object_version_number IN NUMBER
942 ) RETURN VARCHAR2 IS
943
944 l_return_status VARCHAR2(1);
945 l_object_version_number OKC_review_upld_header.OBJECT_VERSION_NUMBER%TYPE;
946 l_row_notfound BOOLEAN := FALSE;
947
948 CURSOR lock_csr (cp_review_upld_header_id NUMBER, cp_object_version_number NUMBER) IS
949 SELECT object_version_number
950 FROM OKC_review_upld_header
951 WHERE review_upld_header_ID = cp_review_upld_header_id
952 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
953 FOR UPDATE OF object_version_number NOWAIT;
954
955 CURSOR lchk_csr (cp_review_upld_header_id NUMBER) IS
956 SELECT object_version_number
957 FROM OKC_review_upld_header
958 WHERE review_upld_header_ID = cp_review_upld_header_id;
959 BEGIN
960
961 IF (l_debug = 'Y') THEN
962 Okc_Debug.Log('4900: Entered Lock_Row', 2);
963 END IF;
964
965
966 BEGIN
967
968 OPEN lock_csr( p_review_upld_header_id, p_object_version_number );
969 FETCH lock_csr INTO l_object_version_number;
970 l_row_notfound := lock_csr%NOTFOUND;
971 CLOSE lock_csr;
972
973 EXCEPTION
974 WHEN E_Resource_Busy THEN
975
976 IF (l_debug = 'Y') THEN
977 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
978 END IF;
979
980 IF (lock_csr%ISOPEN) THEN
981 CLOSE lock_csr;
982 END IF;
983 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
984 RETURN( G_RET_STS_ERROR );
985 END;
986
987 IF ( l_row_notfound ) THEN
988 l_return_status := G_RET_STS_ERROR;
989
990 OPEN lchk_csr(p_review_upld_header_id);
991 FETCH lchk_csr INTO l_object_version_number;
992 l_row_notfound := lchk_csr%NOTFOUND;
993 CLOSE lchk_csr;
994
995 IF (l_row_notfound) THEN
996 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_DELETED);
997 ELSIF l_object_version_number > p_object_version_number THEN
998 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_CHANGED);
999 ELSIF l_object_version_number = -1 THEN
1000 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1001 ELSE -- it can be the only above condition. It can happen after restore version
1002 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_CHANGED);
1003 END IF;
1004 ELSE
1005 l_return_status := G_RET_STS_SUCCESS;
1006 END IF;
1007
1008 IF (l_debug = 'Y') THEN
1009 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
1010 END IF;
1011
1012 RETURN( l_return_status );
1013
1014 EXCEPTION
1015 WHEN OTHERS THEN
1016
1017 IF (lock_csr%ISOPEN) THEN
1018 CLOSE lock_csr;
1019 END IF;
1020 IF (lchk_csr%ISOPEN) THEN
1021 CLOSE lchk_csr;
1022 END IF;
1023
1024 IF (l_debug = 'Y') THEN
1025 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1026 END IF;
1027
1028 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1029 p_msg_name => G_UNEXPECTED_ERROR,
1030 p_token1 => G_SQLCODE_TOKEN,
1031 p_token1_value => sqlcode,
1032 p_token2 => G_SQLERRM_TOKEN,
1033 p_token2_value => sqlerrm);
1034
1035 RETURN( G_RET_STS_UNEXP_ERROR );
1036 END Lock_Row;
1037
1038 -----------------------------------
1039 -- Lock_Row for:OKC_review_upld_header --
1040 -----------------------------------
1041 PROCEDURE Lock_Row(
1042 x_return_status OUT NOCOPY VARCHAR2,
1043
1044 p_review_upld_header_id IN NUMBER,
1045 p_object_version_number IN NUMBER
1046 ) IS
1047 BEGIN
1048
1049 IF (l_debug = 'Y') THEN
1050 Okc_Debug.Log('5700: Entered Lock_Row', 2);
1051 Okc_Debug.Log('5800: Locking Row for Base Table', 2);
1052 END IF;
1053
1054 --------------------------------------------
1055 -- Call the LOCK_ROW for each _B child record
1056 --------------------------------------------
1057 x_return_status := Lock_Row(
1058 p_review_upld_header_id => p_review_upld_header_id,
1059 p_object_version_number => p_object_version_number
1060 );
1061 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1063 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1064 RAISE FND_API.G_EXC_ERROR;
1065 END IF;
1066
1067
1068
1069 IF (l_debug = 'Y') THEN
1070 Okc_Debug.Log('6000: Leaving Lock_Row', 2);
1071 END IF;
1072
1073 EXCEPTION
1074 WHEN FND_API.G_EXC_ERROR THEN
1075 IF (l_debug = 'Y') THEN
1076 Okc_Debug.Log('6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception', 2);
1077 END IF;
1078 x_return_status := G_RET_STS_ERROR;
1079
1080 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1081 IF (l_debug = 'Y') THEN
1082 Okc_Debug.Log('6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1083 END IF;
1084 x_return_status := G_RET_STS_UNEXP_ERROR;
1085
1086 WHEN OTHERS THEN
1087 IF (l_debug = 'Y') THEN
1088 Okc_Debug.Log('6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1089 END IF;
1090 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1091 p_msg_name => G_UNEXPECTED_ERROR,
1092 p_token1 => G_SQLCODE_TOKEN,
1093 p_token1_value => sqlcode,
1094 p_token2 => G_SQLERRM_TOKEN,
1095 p_token2_value => sqlerrm);
1096 x_return_status := G_RET_STS_UNEXP_ERROR;
1097
1098 END Lock_Row;
1099 ---------------------------------------------------------------------------
1100 -- PROCEDURE Update_Row
1101 ---------------------------------------------------------------------------
1102 -------------------------------------
1103 -- Update_Row for:OKC_review_upld_header --
1104 -------------------------------------
1105 FUNCTION Update_Row(
1106 p_review_upld_header_id IN NUMBER,
1107 p_file_name IN VARCHAR2,
1108 p_file_content_type IN VARCHAR2,
1109 p_file_data IN BLOB,
1110 p_document_type IN VARCHAR2,
1111 p_document_id IN NUMBER,
1112 p_object_version_number IN NUMBER,
1113 p_last_updated_by IN NUMBER,
1114 p_last_update_login IN NUMBER,
1115 p_last_update_date IN DATE,
1116 p_new_contract_source IN VARCHAR2,
1117 p_enable_reporting_flag IN VARCHAR2,
1118 p_file_description IN VARCHAR2
1119
1120 ) RETURN VARCHAR2 IS
1121
1122 BEGIN
1123
1124 IF (l_debug = 'Y') THEN
1125 Okc_Debug.Log('6400: Entered Update_Row', 2);
1126 END IF;
1127
1128 UPDATE OKC_review_upld_header
1129 SET FILE_NAME = p_file_name,
1130 FILE_CONTENT_TYPE = p_file_content_type,
1131 FILE_DATA = p_file_data,
1132 DOCUMENT_TYPE = p_document_type,
1133 DOCUMENT_ID = p_document_id,
1134 OBJECT_VERSION_NUMBER = p_object_version_number,
1135 LAST_UPDATED_BY = p_last_updated_by,
1136 LAST_UPDATE_LOGIN = p_last_update_login,
1137 LAST_UPDATE_DATE = p_last_update_date,
1138 new_contract_source = p_new_contract_source,
1139 enable_reporting_flag = p_enable_reporting_flag,
1140 file_description = p_file_description
1141 WHERE review_upld_header_ID = p_review_upld_header_id;
1142
1143 IF (l_debug = 'Y') THEN
1144 Okc_Debug.Log('6500: Leaving Update_Row', 2);
1145 END IF;
1146
1147 RETURN G_RET_STS_SUCCESS ;
1148
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151
1152 IF (l_debug = 'Y') THEN
1153 Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1154 END IF;
1155
1156 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1157 p_msg_name => G_UNEXPECTED_ERROR,
1158 p_token1 => G_SQLCODE_TOKEN,
1159 p_token1_value => sqlcode,
1160 p_token2 => G_SQLERRM_TOKEN,
1161 p_token2_value => sqlerrm);
1162
1163 RETURN G_RET_STS_UNEXP_ERROR ;
1164
1165 END Update_Row;
1166
1167 -------------------------------------
1168 -- Update_Row for:OKC_review_upld_header --
1169 -------------------------------------
1170 PROCEDURE Update_Row(
1171 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1172
1173 x_return_status OUT NOCOPY VARCHAR2,
1174
1175 p_review_upld_header_id IN NUMBER,
1176
1177 p_file_name IN VARCHAR2 := NULL,
1178 p_file_content_type IN VARCHAR2 := NULL,
1179 p_file_data IN BLOB := NULL,
1180 p_document_type IN VARCHAR2 := NULL,
1181 p_document_id IN NUMBER := NULL,
1182
1183 p_object_version_number IN NUMBER,
1184 p_new_contract_source IN VARCHAR2 := NULL,
1185 p_enable_reporting_flag IN VARCHAR2 := NULL,
1186 p_file_description IN VARCHAR2 := NULL
1187
1188 ) IS
1189
1190 l_file_name OKC_review_upld_header.FILE_NAME%TYPE;
1191 l_file_content_type OKC_review_upld_header.FILE_CONTENT_TYPE%TYPE;
1192 l_file_data OKC_review_upld_header.FILE_DATA%TYPE;
1193 l_document_type OKC_review_upld_header.DOCUMENT_TYPE%TYPE;
1194 l_document_id OKC_review_upld_header.DOCUMENT_ID%TYPE;
1195 l_object_version_number OKC_review_upld_header.OBJECT_VERSION_NUMBER%TYPE;
1196 l_last_updated_by OKC_review_upld_header.LAST_UPDATED_BY%TYPE;
1197 l_last_update_login OKC_review_upld_header.LAST_UPDATE_LOGIN%TYPE;
1198 l_last_update_date OKC_review_upld_header.LAST_UPDATE_DATE%TYPE;
1199 l_new_contract_source OKC_review_upld_header.new_contract_source%TYPE;
1200 l_enable_reporting_flag OKC_review_upld_header.enable_reporting_flag%TYPE;
1201 l_file_description OKC_review_upld_header.file_description%TYPE;
1202 BEGIN
1203
1204 IF (l_debug = 'Y') THEN
1205 Okc_Debug.Log('7000: Entered Update_Row', 2);
1206 Okc_Debug.Log('7100: Locking _B row', 2);
1207 END IF;
1208
1209 x_return_status := Lock_row(
1210 p_review_upld_header_id => p_review_upld_header_id,
1211 p_object_version_number => p_object_version_number
1212 );
1213 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1215 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1216 RAISE FND_API.G_EXC_ERROR;
1217 END IF;
1218
1219
1220 IF (l_debug = 'Y') THEN
1221 Okc_Debug.Log('7300: Setting attributes', 2);
1222 END IF;
1223
1224 l_object_version_number := p_object_version_number;
1225 x_return_status := Set_Attributes(
1226 p_review_upld_header_id => p_review_upld_header_id,
1227 p_file_name => p_file_name,
1228 p_file_content_type => p_file_content_type,
1229 p_file_data => p_file_data,
1230 p_document_type => p_document_type,
1231 p_document_id => p_document_id,
1232 p_new_contract_source => p_new_contract_source,
1233 p_enable_reporting_flag => p_enable_reporting_flag,
1234 p_file_description => p_file_description,
1235 p_object_version_number => l_object_version_number,
1236 x_file_name => l_file_name,
1237 x_file_content_type => l_file_content_type,
1238 x_file_data => l_file_data,
1239 x_document_type => l_document_type,
1240 x_document_id => l_document_id,
1241 x_new_contract_source => l_new_contract_source,
1242 x_enable_reporting_flag => l_enable_reporting_flag,
1243 x_file_description => l_file_description
1244 );
1245 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1247 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1248 RAISE FND_API.G_EXC_ERROR;
1249 END IF;
1250
1251 IF (l_debug = 'Y') THEN
1252 Okc_Debug.Log('7400: Record Validation', 2);
1253 END IF;
1254
1255 --- Validate all non-missing attributes
1256 x_return_status := Validate_Record(
1257 p_validation_level => p_validation_level,
1258 p_review_upld_header_id => p_review_upld_header_id,
1259 p_file_name => l_file_name,
1260 p_file_content_type => l_file_content_type,
1261 p_file_data => l_file_data,
1262 p_document_type => l_document_type,
1263 p_document_id => l_document_id,
1264 p_new_contract_source => l_new_contract_source,
1265 p_enable_reporting_flag => l_enable_reporting_flag,
1266 p_file_description => l_file_description
1267 );
1268 --- If any errors happen abort API
1269 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1271 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1272 RAISE FND_API.G_EXC_ERROR;
1273 END IF;
1274
1275 IF (l_debug = 'Y') THEN
1276 Okc_Debug.Log('7500: Filling WHO columns', 2);
1277 END IF;
1278
1279 -- Filling who columns
1280 l_last_update_date := SYSDATE;
1281 l_last_updated_by := FND_GLOBAL.USER_ID;
1282 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1283
1284 -- Object version increment
1285 -- IF Nvl(p_object_version_number, 0) >= 0 THEN
1286 -- l_object_version_number := Nvl( p_object_version_number, 0) + 1;
1287 -- END IF;
1288 l_object_version_number := l_object_version_number + 1; -- l_object_version_number should not be NULL because of Set_Attribute
1289
1290 --------------------------------------------
1291 -- Call the Update_Row for each child record
1292 --------------------------------------------
1293 IF (l_debug = 'Y') THEN
1294 Okc_Debug.Log('7600: Updating Row', 2);
1295 END IF;
1296
1297 x_return_status := Update_Row(
1298 p_review_upld_header_id => p_review_upld_header_id,
1299 p_file_name => l_file_name,
1300 p_file_content_type => l_file_content_type,
1301 p_file_data => l_file_data,
1302 p_document_type => l_document_type,
1303 p_document_id => l_document_id,
1304 p_object_version_number => l_object_version_number,
1305 p_last_updated_by => l_last_updated_by,
1306 p_last_update_login => l_last_update_login,
1307 p_last_update_date => l_last_update_date,
1308 p_new_contract_source => l_new_contract_source,
1309 p_enable_reporting_flag => p_enable_reporting_flag,
1310 p_file_description => p_file_description
1311 );
1312 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1314 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1315 RAISE FND_API.G_EXC_ERROR;
1316 END IF;
1317
1318
1319 IF (l_debug = 'Y') THEN
1320 Okc_Debug.Log('7800: Leaving Update_Row', 2);
1321 END IF;
1322
1323 EXCEPTION
1324 WHEN FND_API.G_EXC_ERROR THEN
1325 IF (l_debug = 'Y') THEN
1326 Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
1327 END IF;
1328 x_return_status := G_RET_STS_ERROR;
1329
1330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1331 IF (l_debug = 'Y') THEN
1332 Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1333 END IF;
1334 x_return_status := G_RET_STS_UNEXP_ERROR;
1335
1336 WHEN OTHERS THEN
1337 IF (l_debug = 'Y') THEN
1338 Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1339 END IF;
1340 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1341 p_msg_name => G_UNEXPECTED_ERROR,
1342 p_token1 => G_SQLCODE_TOKEN,
1343 p_token1_value => sqlcode,
1344 p_token2 => G_SQLERRM_TOKEN,
1345 p_token2_value => sqlerrm);
1346 x_return_status := G_RET_STS_UNEXP_ERROR;
1347
1348 END Update_Row;
1349
1350 ---------------------------------------------------------------------------
1351 -- PROCEDURE Delete_Row
1352 ---------------------------------------------------------------------------
1353 -------------------------------------
1354 -- Delete_Row for:OKC_review_upld_header --
1355 -------------------------------------
1356 FUNCTION Delete_Row(
1357 p_review_upld_header_id IN NUMBER
1358 ) RETURN VARCHAR2 IS
1359
1360 BEGIN
1361
1362 IF (l_debug = 'Y') THEN
1363 Okc_Debug.Log('8200: Entered Delete_Row', 2);
1364 END IF;
1365
1366 DELETE FROM OKC_review_upld_header
1367 WHERE review_upld_header_ID = p_review_upld_header_ID;
1368
1369 IF (l_debug = 'Y') THEN
1370 Okc_Debug.Log('8300: Leaving Delete_Row', 2);
1371 END IF;
1372
1373 RETURN( G_RET_STS_SUCCESS );
1374
1375 EXCEPTION
1376 WHEN OTHERS THEN
1377
1378 IF (l_debug = 'Y') THEN
1379 Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1380 END IF;
1381
1382 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1383 p_msg_name => G_UNEXPECTED_ERROR,
1384 p_token1 => G_SQLCODE_TOKEN,
1385 p_token1_value => sqlcode,
1386 p_token2 => G_SQLERRM_TOKEN,
1387 p_token2_value => sqlerrm);
1388
1389 RETURN( G_RET_STS_UNEXP_ERROR );
1390
1391 END Delete_Row;
1392
1393 -------------------------------------
1394 -- Delete_Row for:OKC_review_upld_header --
1395 -------------------------------------
1396 PROCEDURE Delete_Row(
1397 x_return_status OUT NOCOPY VARCHAR2,
1398 p_review_upld_header_id IN NUMBER,
1399 p_object_version_number IN NUMBER
1400 ) IS
1401 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
1402 BEGIN
1403
1404 IF (l_debug = 'Y') THEN
1405 Okc_Debug.Log('8800: Entered Delete_Row', 2);
1406 Okc_Debug.Log('8900: Locking _B row', 2);
1407 END IF;
1408
1409 x_return_status := Lock_row(
1410 p_review_upld_header_id => p_review_upld_header_id,
1411 p_object_version_number => p_object_version_number
1412 );
1413 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1415 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1416 RAISE FND_API.G_EXC_ERROR;
1417 END IF;
1418
1419
1420 IF (l_debug = 'Y') THEN
1421 Okc_Debug.Log('9100: Removing _B row', 2);
1422 END IF;
1423 x_return_status := Delete_Row( p_review_upld_header_id => p_review_upld_header_id );
1424 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1426 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1427 RAISE FND_API.G_EXC_ERROR;
1428 END IF;
1429
1430
1431 IF (l_debug = 'Y') THEN
1432 Okc_Debug.Log('9300: Leaving Delete_Row', 2);
1433 END IF;
1434
1435 EXCEPTION
1436 WHEN FND_API.G_EXC_ERROR THEN
1437 IF (l_debug = 'Y') THEN
1438 Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
1439 END IF;
1440 x_return_status := G_RET_STS_ERROR;
1441
1442 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1443 IF (l_debug = 'Y') THEN
1444 Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1445 END IF;
1446 x_return_status := G_RET_STS_UNEXP_ERROR;
1447
1448 WHEN OTHERS THEN
1449 IF (l_debug = 'Y') THEN
1450 Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1451 END IF;
1452 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1453 p_msg_name => G_UNEXPECTED_ERROR,
1454 p_token1 => G_SQLCODE_TOKEN,
1455 p_token1_value => sqlcode,
1456 p_token2 => G_SQLERRM_TOKEN,
1457 p_token2_value => sqlerrm);
1458 x_return_status := G_RET_STS_UNEXP_ERROR;
1459
1460 END Delete_Row;
1461
1462
1463
1464
1465 END OKC_REVIEW_UPLD_HEADER_PVT;
1466