[Home] [Help]
PACKAGE BODY: APPS.OKC_DOC_QA_LIST_PVT
Source
1 PACKAGE BODY OKC_DOC_QA_LIST_PVT AS
2 /* $Header: OKCVQALB.pls 120.0 2005/05/25 19:45:57 appldev 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_QAL_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 -- FUNCTION get_rec for: OKC_DOC_QA_LISTS
52 ---------------------------------------------------------------------------
53 FUNCTION Get_Rec (
54 p_qa_code IN VARCHAR2,
55 p_document_type IN VARCHAR2,
56
57 x_severity_flag OUT NOCOPY VARCHAR2,
58 x_enable_qa_yn OUT NOCOPY VARCHAR2,
59 x_object_version_number OUT NOCOPY NUMBER,
60 x_created_by OUT NOCOPY NUMBER,
61 x_creation_date OUT NOCOPY DATE,
62 x_last_updated_by OUT NOCOPY NUMBER,
63 x_last_update_login OUT NOCOPY NUMBER,
64 x_last_update_date OUT NOCOPY DATE
65
66 ) RETURN VARCHAR2 IS
67 CURSOR OKC_DOC_QA_LISTS_pk_csr (cp_qa_code IN VARCHAR2,cp_document_type IN VARCHAR2) IS
68 SELECT
69 SEVERITY_FLAG,
70 ENABLE_QA_YN,
71 OBJECT_VERSION_NUMBER,
72 CREATED_BY,
73 CREATION_DATE,
74 LAST_UPDATED_BY,
75 LAST_UPDATE_LOGIN,
76 LAST_UPDATE_DATE
77 FROM OKC_DOC_QA_LISTS t
78 WHERE t.QA_CODE = cp_qa_code and
79 t.DOCUMENT_TYPE = cp_document_type;
80 BEGIN
81
82 IF (l_debug = 'Y') THEN
83 Okc_Debug.Log('400: Entered get_rec', 2);
84 END IF;
85
86 -- Get current database values
87 OPEN OKC_DOC_QA_LISTS_pk_csr (p_qa_code, p_document_type);
88 FETCH OKC_DOC_QA_LISTS_pk_csr INTO
89 x_severity_flag,
90 x_enable_qa_yn,
91 x_object_version_number,
92 x_created_by,
93 x_creation_date,
94 x_last_updated_by,
95 x_last_update_login,
96 x_last_update_date;
97 IF OKC_DOC_QA_LISTS_pk_csr%NOTFOUND THEN
98 RAISE NO_DATA_FOUND;
99 END IF;
100 CLOSE OKC_DOC_QA_LISTS_pk_csr;
101
102 IF (l_debug = 'Y') THEN
103 Okc_Debug.Log('500: Leaving get_rec ', 2);
104 END IF;
105
106 RETURN G_RET_STS_SUCCESS ;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110
111 IF (l_debug = 'Y') THEN
112 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
113 END IF;
114
115 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
116 p_msg_name => G_UNEXPECTED_ERROR,
117 p_token1 => G_SQLCODE_TOKEN,
118 p_token1_value => sqlcode,
119 p_token2 => G_SQLERRM_TOKEN,
120 p_token2_value => sqlerrm);
121
122 IF OKC_DOC_QA_LISTS_pk_csr%ISOPEN THEN
123 CLOSE OKC_DOC_QA_LISTS_pk_csr;
124 END IF;
125
126 RETURN G_RET_STS_UNEXP_ERROR ;
127
128 END Get_Rec;
129
130 -----------------------------------------
131 -- Set_Attributes for:OKC_DOC_QA_LISTS --
132 -----------------------------------------
133 FUNCTION Set_Attributes(
134 p_qa_code IN VARCHAR2,
135 p_document_type IN VARCHAR2,
136 p_severity_flag IN VARCHAR2,
137 p_enable_qa_yn IN VARCHAR2,
138 p_object_version_number IN NUMBER,
139
140 x_object_version_number OUT NOCOPY NUMBER,
141 x_severity_flag OUT NOCOPY VARCHAR2,
142 x_enable_qa_yn OUT NOCOPY VARCHAR2
143 ) RETURN VARCHAR2 IS
144 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
145 l_object_version_number OKC_DOC_QA_LISTS.OBJECT_VERSION_NUMBER%TYPE;
146 l_created_by OKC_DOC_QA_LISTS.CREATED_BY%TYPE;
147 l_creation_date OKC_DOC_QA_LISTS.CREATION_DATE%TYPE;
148 l_last_updated_by OKC_DOC_QA_LISTS.LAST_UPDATED_BY%TYPE;
149 l_last_update_login OKC_DOC_QA_LISTS.LAST_UPDATE_LOGIN%TYPE;
150 l_last_update_date OKC_DOC_QA_LISTS.LAST_UPDATE_DATE%TYPE;
151 BEGIN
152 IF (l_debug = 'Y') THEN
153 Okc_Debug.Log('700: Entered Set_Attributes ', 2);
154 END IF;
155
156 IF( p_qa_code IS NOT NULL AND p_document_type IS NOT NULL ) THEN
157 -- Get current database values
158 l_return_status := Get_Rec(
159 p_qa_code => p_qa_code,
160 p_document_type => p_document_type,
161 x_severity_flag => x_severity_flag,
162 x_enable_qa_yn => x_enable_qa_yn,
163 x_object_version_number => x_object_version_number,
164 x_created_by => l_created_by,
165 x_creation_date => l_creation_date,
166 x_last_updated_by => l_last_updated_by,
167 x_last_update_login => l_last_update_login,
168 x_last_update_date => l_last_update_date
169 );
170 --- If any errors happen abort API
171 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176
177 --- Reversing G_MISS/NULL values logic
178
179 IF (p_severity_flag = G_MISS_CHAR) THEN
180 x_severity_flag := NULL;
181 ELSIF (p_SEVERITY_FLAG IS NOT NULL) THEN
182 x_severity_flag := Upper( p_severity_flag );
183 END IF;
184
185
186 IF (p_enable_qa_yn = G_MISS_CHAR) THEN
187 x_enable_qa_yn := NULL;
188 ELSIF (p_SEVERITY_FLAG IS NOT NULL) THEN
189 x_enable_qa_yn := Upper( p_enable_qa_yn );
190 END IF;
191
192 END IF;
193
194 IF (l_debug = 'Y') THEN
195 Okc_Debug.Log('800: Leaving Set_Attributes ', 2);
196 END IF;
197
198 RETURN G_RET_STS_SUCCESS ;
199 EXCEPTION
200 WHEN FND_API.G_EXC_ERROR THEN
201 IF (l_debug = 'Y') THEN
202 Okc_Debug.Log('900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception', 2);
203 END IF;
204 RETURN G_RET_STS_ERROR;
205
206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
207 IF (l_debug = 'Y') THEN
208 Okc_Debug.Log('1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
209 END IF;
210 RETURN G_RET_STS_UNEXP_ERROR;
211
212 WHEN OTHERS THEN
213 IF (l_debug = 'Y') THEN
214 Okc_Debug.Log('1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm, 2);
215 END IF;
216 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
217 p_msg_name => G_UNEXPECTED_ERROR,
218 p_token1 => G_SQLCODE_TOKEN,
219 p_token1_value => sqlcode,
220 p_token2 => G_SQLERRM_TOKEN,
221 p_token2_value => sqlerrm);
222 RETURN G_RET_STS_UNEXP_ERROR;
223
224 END Set_Attributes ;
225
226 ----------------------------------------------
227 -- Validate_Attributes for: OKC_DOC_QA_LISTS --
228 ----------------------------------------------
229 FUNCTION Validate_Attributes (
230 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
231
232 p_qa_code IN VARCHAR2,
233 p_document_type IN VARCHAR2,
234 p_severity_flag IN VARCHAR2,
235 p_enable_qa_yn IN VARCHAR2
236 ) RETURN VARCHAR2 IS
237 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
238 l_dummy_var VARCHAR2(1) := '?';
239 CURSOR l_doc_type_csr is
240 SELECT '!'
241 FROM OKC_BUS_DOC_TYPES_V
242 WHERE DOCUMENT_TYPE = p_document_type;
243 BEGIN
244
245 IF (l_debug = 'Y') THEN
246 Okc_Debug.Log('1200: Entered Validate_Attributes', 2);
247 END IF;
248
249 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
250 IF (l_debug = 'Y') THEN
251 Okc_Debug.Log('1300: required values validation', 2);
252 END IF;
253
254 IF (l_debug = 'Y') THEN
255 Okc_Debug.Log('1400: - attribute QA_CODE ', 2);
256 END IF;
257 IF ( p_qa_code IS NULL) THEN
258 IF (l_debug = 'Y') THEN
259 Okc_Debug.Log('1500: - attribute QA_CODE is invalid', 2);
260 END IF;
261 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'QA_CODE');
262 l_return_status := G_RET_STS_ERROR;
263 END IF;
264
265 IF (l_debug = 'Y') THEN
266 Okc_Debug.Log('1400: - attribute DOCUMENT_TYPE ', 2);
267 END IF;
268 IF ( p_document_type IS NULL) THEN
269 IF (l_debug = 'Y') THEN
270 Okc_Debug.Log('1500: - attribute DOCUMENT_TYPE is invalid', 2);
271 END IF;
272 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'DOCUMENT_TYPE');
273 l_return_status := G_RET_STS_ERROR;
274 END IF;
275
276 IF (l_debug = 'Y') THEN
277 Okc_Debug.Log('1400: - attribute SEVERITY_FLAG ', 2);
278 END IF;
279 IF ( p_severity_flag IS NULL) THEN
280 IF (l_debug = 'Y') THEN
281 Okc_Debug.Log('1500: - attribute SEVERITY_FLAG is invalid', 2);
282 END IF;
283 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'SEVERITY_FLAG');
284 l_return_status := G_RET_STS_ERROR;
285 END IF;
286
287 IF (l_debug = 'Y') THEN
288 Okc_Debug.Log('1400: - attribute ENABLE_QA_YN ', 2);
289 END IF;
290 IF ( p_enable_qa_yn IS NULL) THEN
291 IF (l_debug = 'Y') THEN
292 Okc_Debug.Log('1500: - attribute ENABLE_QA_YN is invalid', 2);
293 END IF;
294 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ENABLE_QA_YN');
295 l_return_status := G_RET_STS_ERROR;
296 END IF;
297 END IF;
298
299 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
300 IF (l_debug = 'Y') THEN
301 Okc_Debug.Log('1600: static values and range validation', 2);
302 END IF;
303
304 IF (l_debug = 'Y') THEN
305 Okc_Debug.Log('1700: - attribute SEVERITY_FLAG ', 2);
306 END IF;
307 IF ( p_severity_flag NOT IN ('E','W') AND p_severity_flag IS NOT NULL) THEN
308 IF (l_debug = 'Y') THEN
309 Okc_Debug.Log('1800: - attribute SEVERITY_FLAG is invalid', 2);
310 END IF;
311 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'SEVERITY_FLAG');
312 l_return_status := G_RET_STS_ERROR;
313 END IF;
314
315
316 IF (l_debug = 'Y') THEN
317 Okc_Debug.Log('1700: - attribute ENABLE_QA_YN ', 2);
318 END IF;
319 IF ( p_ENABLE_QA_YN NOT IN ('Y','N') AND p_ENABLE_QA_YN IS NOT NULL) THEN
320 IF (l_debug = 'Y') THEN
321 Okc_Debug.Log('1800: - attribute ENABLE_QA_YN is invalid', 2);
322 END IF;
323 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'ENABLE_QA_YN');
324 l_return_status := G_RET_STS_ERROR;
325 END IF;
326 END IF;
327
328 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
329 IF (l_debug = 'Y') THEN
330 Okc_Debug.Log('1900: lookup codes validation', 2);
331 END IF;
332 IF (l_debug = 'Y') THEN
333 Okc_Debug.Log('2000: - attribute QA_CODE ', 2);
334 END IF;
335 IF p_qa_code IS NOT NULL THEN
336 l_return_status := Okc_Util.Check_Lookup_Code('OKC_TERM_QA_LIST',p_qa_code);
337 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
338 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'QA_CODE');
339 l_return_status := G_RET_STS_ERROR;
340 END IF;
341 END IF;
342 END IF;
343
344 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
345 IF (l_debug = 'Y') THEN
346 Okc_Debug.Log('2100: foreigh keys validation ', 2);
347 END IF;
348
349 IF (l_debug = 'Y') THEN
350 Okc_Debug.Log('2200: - attribute DOCUMENT_TYPE ', 2);
351 END IF;
352 IF p_document_type IS NOT NULL THEN
353 l_dummy_var := '?';
354 OPEN l_doc_type_csr ;
355 FETCH l_doc_type_csr INTO l_dummy_var;
356 CLOSE l_doc_type_csr ;
357 IF (l_dummy_var = '?') THEN
358 IF (l_debug = 'Y') THEN
359 Okc_Debug.Log('2300: - attribute DOCUMENT_TYPE is invalid', 2);
360 END IF;
361 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DOCUMENT_TYPE');
362 l_return_status := G_RET_STS_ERROR;
363 END IF;
364 END IF;
365
366 END IF;
367
368
369 IF (l_debug = 'Y') THEN
370 Okc_Debug.Log('2400: Leaving Validate_Attributes ', 2);
371 END IF;
372
373 RETURN l_return_status;
374
375 EXCEPTION
376 WHEN OTHERS THEN
377 Okc_Debug.Log('2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
378 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
379 p_msg_name => G_UNEXPECTED_ERROR,
380 p_token1 => G_SQLCODE_TOKEN,
381 p_token1_value => sqlcode,
382 p_token2 => G_SQLERRM_TOKEN,
383 p_token2_value => sqlerrm);
384
385 IF l_doc_type_csr%ISOPEN THEN
386 CLOSE l_doc_type_csr ;
387 END IF;
388
389 RETURN G_RET_STS_UNEXP_ERROR;
390
391 END Validate_Attributes;
392
393
394 ---------------------------------------------------------------------------
395 -- PROCEDURE Validate_Record
396 -- It calls Item Level Validations and then makes Record Level Validations
397 ---------------------------------------------------------------------------
398 ------------------------------------------
399 -- Validate_Record for:OKC_DOC_QA_LISTS --
400 ------------------------------------------
401 FUNCTION Validate_Record (
402 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
403
404 p_qa_code IN VARCHAR2,
405 p_document_type IN VARCHAR2,
406 p_severity_flag IN VARCHAR2,
407 p_enable_qa_yn IN VARCHAR2
408 ) RETURN VARCHAR2 IS
409 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
410 BEGIN
411
412 IF (l_debug = 'Y') THEN
413 Okc_Debug.Log('2600: Entered Validate_Record', 2);
414 END IF;
415
416 --- Validate all non-missing attributes (Item Level Validation)
417 l_return_status := Validate_Attributes(
418 p_validation_level => p_validation_level,
419
420 p_qa_code => p_qa_code,
421 p_document_type => p_document_type,
422 p_severity_flag => p_severity_flag,
423 p_enable_qa_yn => p_enable_qa_yn
424 );
425 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
426 IF (l_debug = 'Y') THEN
427 Okc_Debug.Log('2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
428 END IF;
429 RETURN G_RET_STS_UNEXP_ERROR;
430 END IF;
431
432 --- Record Level Validation
433 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
434 IF (l_debug = 'Y') THEN
435 Okc_Debug.Log('2800: Entered Record Level Validations', 2);
436 END IF;
437 /*+++++++++++++start of hand code +++++++++++++++++++*/
438 -- ?? manual coding for Record Level Validations if required ??
439 /*+++++++++++++End of hand code +++++++++++++++++++*/
440 END IF;
441
442 IF (l_debug = 'Y') THEN
443 Okc_Debug.Log('2900: Leaving Validate_Record : '||sqlerrm, 2);
444 END IF;
445 RETURN l_return_status ;
446
447 EXCEPTION
448 WHEN OTHERS THEN
449
450 IF (l_debug = 'Y') THEN
451 Okc_Debug.Log('3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
452 END IF;
453
454 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
455 p_msg_name => G_UNEXPECTED_ERROR,
456 p_token1 => G_SQLCODE_TOKEN,
457 p_token1_value => sqlcode,
458 p_token2 => G_SQLERRM_TOKEN,
459 p_token2_value => sqlerrm);
460 RETURN G_RET_STS_UNEXP_ERROR ;
461
462 END Validate_Record;
463
464 ---------------------------------------------------------------------------
465 -- PROCEDURE validate_row
466 ---------------------------------------------------------------------------
467 ---------------------------------------
468 -- validate_row for:OKC_DOC_QA_LISTS --
469 ---------------------------------------
470 PROCEDURE validate_row(
471 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
472
473 x_return_status OUT NOCOPY VARCHAR2,
474
475 p_qa_code IN VARCHAR2,
476 p_document_type IN VARCHAR2,
477 p_severity_flag IN VARCHAR2,
478 p_enable_qa_yn IN VARCHAR2,
479
480
481
482 p_object_version_number IN NUMBER
483 ) IS
484 l_severity_flag OKC_DOC_QA_LISTS.SEVERITY_FLAG%TYPE;
485 l_enable_qa_yn OKC_DOC_QA_LISTS.enable_qa_yn%TYPE;
486 l_object_version_number OKC_DOC_QA_LISTS.OBJECT_VERSION_NUMBER%TYPE;
487 l_created_by OKC_DOC_QA_LISTS.CREATED_BY%TYPE;
488 l_creation_date OKC_DOC_QA_LISTS.CREATION_DATE%TYPE;
489 l_last_updated_by OKC_DOC_QA_LISTS.LAST_UPDATED_BY%TYPE;
490 l_last_update_login OKC_DOC_QA_LISTS.LAST_UPDATE_LOGIN%TYPE;
491 l_last_update_date OKC_DOC_QA_LISTS.LAST_UPDATE_DATE%TYPE;
492 BEGIN
493
494 IF (l_debug = 'Y') THEN
495 Okc_Debug.Log('3100: Entered validate_row', 2);
496 END IF;
497
498 -- Setting attributes
499 x_return_status := Set_Attributes(
500 p_qa_code => p_qa_code,
501 p_document_type => p_document_type,
502 p_severity_flag => p_severity_flag,
503 p_enable_qa_yn => p_enable_qa_yn,
504 p_object_version_number => p_object_version_number,
505 x_object_version_number => l_object_version_number,
506 x_severity_flag => l_severity_flag,
507 x_enable_qa_yn => l_enable_qa_yn
508 );
509 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
511 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
512 RAISE FND_API.G_EXC_ERROR;
513 END IF;
514
515 -- Validate all non-missing attributes (Item Level Validation)
516 x_return_status := Validate_Record(
517 p_validation_level => p_validation_level,
518 p_qa_code => p_qa_code,
519 p_document_type => p_document_type,
520 p_severity_flag => l_severity_flag,
521 p_enable_qa_yn => l_enable_qa_yn
522 );
523
524 IF (l_debug = 'Y') THEN
525 Okc_Debug.Log('3200: Leaving validate_row', 2);
526 END IF;
527
528 EXCEPTION
529 WHEN FND_API.G_EXC_ERROR THEN
530 IF (l_debug = 'Y') THEN
531 Okc_Debug.Log('3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception', 2);
532 END IF;
533 x_return_status := G_RET_STS_ERROR;
534
535 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
536 IF (l_debug = 'Y') THEN
537 Okc_Debug.Log('3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
538 END IF;
539 x_return_status := G_RET_STS_UNEXP_ERROR;
540
541 WHEN OTHERS THEN
542 IF (l_debug = 'Y') THEN
543 Okc_Debug.Log('3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
544 END IF;
545 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
546 p_msg_name => G_UNEXPECTED_ERROR,
547 p_token1 => G_SQLCODE_TOKEN,
548 p_token1_value => sqlcode,
549 p_token2 => G_SQLERRM_TOKEN,
550 p_token2_value => sqlerrm);
551 x_return_status := G_RET_STS_UNEXP_ERROR;
552
553 END Validate_Row;
554
555 ---------------------------------------------------------------------------
556 -- PROCEDURE Insert_Row
557 ---------------------------------------------------------------------------
558 -------------------------------------
559 -- Insert_Row for:OKC_DOC_QA_LISTS --
560 -------------------------------------
561 FUNCTION Insert_Row(
562 p_qa_code IN VARCHAR2,
563 p_document_type IN VARCHAR2,
564 p_severity_flag IN VARCHAR2,
565 p_enable_qa_yn IN VARCHAR2,
566 p_object_version_number IN NUMBER,
567 p_created_by IN NUMBER,
568 p_creation_date IN DATE,
569 p_last_updated_by IN NUMBER,
570 p_last_update_login IN NUMBER,
571 p_last_update_date IN DATE
572
573 ) RETURN VARCHAR2 IS
574
575 BEGIN
576
577 IF (l_debug = 'Y') THEN
578 Okc_Debug.Log('3600: Entered Insert_Row function', 2);
579 END IF;
580
581 INSERT INTO OKC_DOC_QA_LISTS(
582 QA_CODE,
583 DOCUMENT_TYPE,
584 SEVERITY_FLAG,
585 ENABLE_QA_YN,
586 OBJECT_VERSION_NUMBER,
587 CREATED_BY,
588 CREATION_DATE,
589 LAST_UPDATED_BY,
590 LAST_UPDATE_LOGIN,
591 LAST_UPDATE_DATE)
592 VALUES (
593 p_qa_code,
594 p_document_type,
595 p_severity_flag,
596 p_enable_qa_yn,
597 p_object_version_number,
598 p_created_by,
599 p_creation_date,
600 p_last_updated_by,
601 p_last_update_login,
602 p_last_update_date);
603
604 IF (l_debug = 'Y') THEN
605 Okc_Debug.Log('3700: Leaving Insert_Row', 2);
606 END IF;
607
608 RETURN( G_RET_STS_SUCCESS );
609
610 EXCEPTION
611 WHEN OTHERS THEN
612
613 IF (l_debug = 'Y') THEN
614 Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
615 END IF;
616
617 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
618 p_msg_name => G_UNEXPECTED_ERROR,
619 p_token1 => G_SQLCODE_TOKEN,
620 p_token1_value => sqlcode,
621 p_token2 => G_SQLERRM_TOKEN,
622 p_token2_value => sqlerrm);
623
624 RETURN( G_RET_STS_UNEXP_ERROR );
625
626 END Insert_Row;
627
628
629 -------------------------------------
630 -- Insert_Row for:OKC_DOC_QA_LISTS --
631 -------------------------------------
632 PROCEDURE Insert_Row(
633 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
634 x_return_status OUT NOCOPY VARCHAR2,
635
636 p_qa_code IN VARCHAR2,
637 p_document_type IN VARCHAR2,
638 p_severity_flag IN VARCHAR2,
639 p_enable_qa_yn IN VARCHAR2,
640
641
642
643 x_qa_code OUT NOCOPY VARCHAR2,
644 x_document_type OUT NOCOPY VARCHAR2
645
646 ) IS
647
648 l_object_version_number OKC_DOC_QA_LISTS.OBJECT_VERSION_NUMBER%TYPE;
649 l_created_by OKC_DOC_QA_LISTS.CREATED_BY%TYPE;
650 l_creation_date OKC_DOC_QA_LISTS.CREATION_DATE%TYPE;
651 l_last_updated_by OKC_DOC_QA_LISTS.LAST_UPDATED_BY%TYPE;
652 l_last_update_login OKC_DOC_QA_LISTS.LAST_UPDATE_LOGIN%TYPE;
653 l_last_update_date OKC_DOC_QA_LISTS.LAST_UPDATE_DATE%TYPE;
654 BEGIN
655
656 x_return_status := G_RET_STS_SUCCESS;
657
658 IF (l_debug = 'Y') THEN
659 Okc_Debug.Log('4200: Entered Insert_Row', 2);
660 END IF;
661
662 --- Setting item attributes
663 -- Set primary key value
664 x_qa_code := p_qa_code;
665 x_document_type := p_document_type;
666 -- Set Internal columns
667 l_object_version_number := 1;
668 l_creation_date := Sysdate;
669 l_created_by := Fnd_Global.User_Id;
670 l_last_update_date := l_creation_date;
671 l_last_updated_by := l_created_by;
672 l_last_update_login := Fnd_Global.Login_Id;
673
674
675 --- Validate all non-missing attributes
676 x_return_status := Validate_Record(
677 p_validation_level => p_validation_level,
678 p_qa_code => x_qa_code,
679 p_document_type => x_document_type,
680 p_severity_flag => p_severity_flag,
681 p_enable_qa_yn => p_enable_qa_yn
682 );
683 --- If any errors happen abort API
684 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
686 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
687 RAISE FND_API.G_EXC_ERROR;
688 END IF;
689
690 --------------------------------------------
691 -- Call the internal Insert_Row for each child record
692 --------------------------------------------
693 IF (l_debug = 'Y') THEN
694 Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
695 END IF;
696
697 x_return_status := Insert_Row(
698 p_qa_code => x_qa_code,
699 p_document_type => x_document_type,
700 p_severity_flag => p_severity_flag,
701 p_enable_qa_yn => p_enable_qa_yn,
702 p_object_version_number => l_object_version_number,
703 p_created_by => l_created_by,
704 p_creation_date => l_creation_date,
705 p_last_updated_by => l_last_updated_by,
706 p_last_update_login => l_last_update_login,
707 p_last_update_date => l_last_update_date
708 );
709 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
711 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
712 RAISE FND_API.G_EXC_ERROR;
713 END IF;
714
715
716
717 IF (l_debug = 'Y') THEN
718 Okc_Debug.Log('4500: Leaving Insert_Row', 2);
719 END IF;
720
721 EXCEPTION
722 WHEN FND_API.G_EXC_ERROR THEN
723 IF (l_debug = 'Y') THEN
724 Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
725 END IF;
726 x_return_status := G_RET_STS_ERROR;
727
728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
729 IF (l_debug = 'Y') THEN
730 Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
731 END IF;
732 x_return_status := G_RET_STS_UNEXP_ERROR;
733
734 WHEN OTHERS THEN
735 IF (l_debug = 'Y') THEN
736 Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
737 END IF;
738 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
739 p_msg_name => G_UNEXPECTED_ERROR,
740 p_token1 => G_SQLCODE_TOKEN,
741 p_token1_value => sqlcode,
742 p_token2 => G_SQLERRM_TOKEN,
743 p_token2_value => sqlerrm);
744 x_return_status := G_RET_STS_UNEXP_ERROR;
745
746 END Insert_Row;
747 ---------------------------------------------------------------------------
748 -- PROCEDURE Lock_Row
749 ---------------------------------------------------------------------------
750 -----------------------------------
751 -- Lock_Row for:OKC_DOC_QA_LISTS --
752 -----------------------------------
753 FUNCTION Lock_Row(
754 p_qa_code IN VARCHAR2,
755 p_document_type IN VARCHAR2,
756 p_object_version_number IN NUMBER
757 ) RETURN VARCHAR2 IS
758
759 E_Resource_Busy EXCEPTION;
760 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
761
762 CURSOR lock_csr (cp_qa_code VARCHAR2, cp_document_type VARCHAR2, cp_object_version_number NUMBER) IS
763 SELECT object_version_number
764 FROM OKC_DOC_QA_LISTS
765 WHERE QA_CODE = cp_qa_code AND DOCUMENT_TYPE = cp_document_type
766 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
767 FOR UPDATE OF object_version_number NOWAIT;
768
769 CURSOR lchk_csr (cp_qa_code VARCHAR2, cp_document_type VARCHAR2) IS
770 SELECT object_version_number
771 FROM OKC_DOC_QA_LISTS
772 WHERE QA_CODE = cp_qa_code AND DOCUMENT_TYPE = cp_document_type;
773
774 l_return_status VARCHAR2(1);
775
776 l_object_version_number OKC_DOC_QA_LISTS.OBJECT_VERSION_NUMBER%TYPE;
777
778 l_row_notfound BOOLEAN := FALSE;
779 BEGIN
780
781 IF (l_debug = 'Y') THEN
782 Okc_Debug.Log('4900: Entered Lock_Row', 2);
783 END IF;
784
785
786 BEGIN
787
788 OPEN lock_csr( p_qa_code, p_document_type, p_object_version_number );
789 FETCH lock_csr INTO l_object_version_number;
790 l_row_notfound := lock_csr%NOTFOUND;
791 CLOSE lock_csr;
792
793 EXCEPTION
794 WHEN E_Resource_Busy THEN
795
796 IF (l_debug = 'Y') THEN
797 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
798 END IF;
799
800 IF (lock_csr%ISOPEN) THEN
801 CLOSE lock_csr;
802 END IF;
803 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
804 RETURN( G_RET_STS_ERROR );
805 END;
806
807 IF ( l_row_notfound ) THEN
808 l_return_status := G_RET_STS_ERROR;
809
810 OPEN lchk_csr(p_qa_code, p_document_type);
811 FETCH lchk_csr INTO l_object_version_number;
812 l_row_notfound := lchk_csr%NOTFOUND;
813 CLOSE lchk_csr;
814
815 IF (l_row_notfound) THEN
816 Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
817 ELSIF l_object_version_number > p_object_version_number THEN
818 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
819 ELSIF l_object_version_number = -1 THEN
820 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
821 ELSE -- it can be the only above condition. It can happen after restore version
822 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
823 END IF;
824 ELSE
825 l_return_status := G_RET_STS_SUCCESS;
826 END IF;
827
828 IF (l_debug = 'Y') THEN
829 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
830 END IF;
831
832 RETURN( l_return_status );
833
834 EXCEPTION
835 WHEN OTHERS THEN
836
837 IF (lock_csr%ISOPEN) THEN
838 CLOSE lock_csr;
839 END IF;
840 IF (lchk_csr%ISOPEN) THEN
841 CLOSE lchk_csr;
842 END IF;
843
844 IF (l_debug = 'Y') THEN
845 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
846 END IF;
847
848 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
849 p_msg_name => G_UNEXPECTED_ERROR,
850 p_token1 => G_SQLCODE_TOKEN,
851 p_token1_value => sqlcode,
852 p_token2 => G_SQLERRM_TOKEN,
853 p_token2_value => sqlerrm);
854
855 RETURN( G_RET_STS_UNEXP_ERROR );
856 END Lock_Row;
857
858 -----------------------------------
859 -- Lock_Row for:OKC_DOC_QA_LISTS --
860 -----------------------------------
861 PROCEDURE Lock_Row(
862 x_return_status OUT NOCOPY VARCHAR2,
863
864 p_qa_code IN VARCHAR2,
865 p_document_type IN VARCHAR2,
866 p_object_version_number IN NUMBER
867 ) IS
868 BEGIN
869
870 IF (l_debug = 'Y') THEN
871 Okc_Debug.Log('5700: Entered Lock_Row', 2);
872 Okc_Debug.Log('5800: Locking Row for Base Table', 2);
873 END IF;
874
875 --------------------------------------------
876 -- Call the LOCK_ROW for each _B child record
877 --------------------------------------------
878 x_return_status := Lock_Row(
879 p_qa_code => p_qa_code,
880 p_document_type => p_document_type,
881 p_object_version_number => p_object_version_number
882 );
883 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888
889
890
891 IF (l_debug = 'Y') THEN
892 Okc_Debug.Log('6000: Leaving Lock_Row', 2);
893 END IF;
894
895 EXCEPTION
896 WHEN FND_API.G_EXC_ERROR THEN
897 IF (l_debug = 'Y') THEN
898 Okc_Debug.Log('6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception', 2);
899 END IF;
900 x_return_status := G_RET_STS_ERROR;
901
902 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
903 IF (l_debug = 'Y') THEN
904 Okc_Debug.Log('6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
905 END IF;
906 x_return_status := G_RET_STS_UNEXP_ERROR;
907
908 WHEN OTHERS THEN
909 IF (l_debug = 'Y') THEN
910 Okc_Debug.Log('6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
911 END IF;
912 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
913 p_msg_name => G_UNEXPECTED_ERROR,
914 p_token1 => G_SQLCODE_TOKEN,
915 p_token1_value => sqlcode,
916 p_token2 => G_SQLERRM_TOKEN,
917 p_token2_value => sqlerrm);
918 x_return_status := G_RET_STS_UNEXP_ERROR;
919
920 END Lock_Row;
921 ---------------------------------------------------------------------------
922 -- PROCEDURE Update_Row
923 ---------------------------------------------------------------------------
924 -------------------------------------
925 -- Update_Row for:OKC_DOC_QA_LISTS --
926 -------------------------------------
927 FUNCTION Update_Row(
928 p_qa_code IN VARCHAR2,
929 p_document_type IN VARCHAR2,
930 p_severity_flag IN VARCHAR2,
931 p_enable_qa_yn IN VARCHAR2,
932 p_object_version_number IN NUMBER,
933 p_last_updated_by IN NUMBER,
934 p_last_update_login IN NUMBER,
935 p_last_update_date IN DATE
936 ) RETURN VARCHAR2 IS
937
938 BEGIN
939
940 IF (l_debug = 'Y') THEN
941 Okc_Debug.Log('6400: Entered Update_Row', 2);
942 END IF;
943
944 UPDATE OKC_DOC_QA_LISTS
945 SET SEVERITY_FLAG = p_severity_flag,
946 ENABLE_QA_YN = p_enable_qa_yn,
947 OBJECT_VERSION_NUMBER = p_object_version_number,
948 LAST_UPDATED_BY = p_last_updated_by,
949 LAST_UPDATE_LOGIN = p_last_update_login,
950 LAST_UPDATE_DATE = p_last_update_date
951 WHERE QA_CODE = p_qa_code AND DOCUMENT_TYPE = p_document_type;
952
953 IF (l_debug = 'Y') THEN
954 Okc_Debug.Log('6500: Leaving Update_Row', 2);
955 END IF;
956
957 RETURN G_RET_STS_SUCCESS ;
958
959 EXCEPTION
960 WHEN OTHERS THEN
961
962 IF (l_debug = 'Y') THEN
963 Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
964 END IF;
965
966 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
967 p_msg_name => G_UNEXPECTED_ERROR,
968 p_token1 => G_SQLCODE_TOKEN,
969 p_token1_value => sqlcode,
970 p_token2 => G_SQLERRM_TOKEN,
971 p_token2_value => sqlerrm);
972
973 RETURN G_RET_STS_UNEXP_ERROR ;
974
975 END Update_Row;
976
977 -------------------------------------
978 -- Update_Row for:OKC_DOC_QA_LISTS --
979 -------------------------------------
980 PROCEDURE Update_Row(
981 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
982
983 x_return_status OUT NOCOPY VARCHAR2,
984
985 p_qa_code IN VARCHAR2,
986 p_document_type IN VARCHAR2,
987 p_severity_flag IN VARCHAR2 := NULL,
988 p_enable_qa_yn IN VARCHAR2 := NULL,
989
990 p_object_version_number IN NUMBER
991
992 ) IS
993
994 l_severity_flag OKC_DOC_QA_LISTS.SEVERITY_FLAG%TYPE;
995 l_enable_qa_yn OKC_DOC_QA_LISTS.ENABLE_QA_YN%TYPE;
996 l_object_version_number OKC_DOC_QA_LISTS.OBJECT_VERSION_NUMBER%TYPE;
997 l_last_updated_by OKC_DOC_QA_LISTS.LAST_UPDATED_BY%TYPE;
998 l_last_update_login OKC_DOC_QA_LISTS.LAST_UPDATE_LOGIN%TYPE;
999 l_last_update_date OKC_DOC_QA_LISTS.LAST_UPDATE_DATE%TYPE;
1000
1001 BEGIN
1002
1003 IF (l_debug = 'Y') THEN
1004 Okc_Debug.Log('7000: Entered Update_Row', 2);
1005 Okc_Debug.Log('7100: Locking _B row', 2);
1006 END IF;
1007
1008 x_return_status := Lock_row(
1009 p_qa_code => p_qa_code,
1010 p_document_type => p_document_type,
1011 p_object_version_number => p_object_version_number
1012 );
1013 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1014 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1015 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1016 RAISE FND_API.G_EXC_ERROR;
1017 END IF;
1018
1019
1020 IF (l_debug = 'Y') THEN
1021 Okc_Debug.Log('7300: Setting attributes', 2);
1022 END IF;
1023
1024 x_return_status := Set_Attributes(
1025 p_qa_code => p_qa_code,
1026 p_document_type => p_document_type,
1027 p_severity_flag => p_severity_flag,
1028 p_enable_qa_yn => p_enable_qa_yn,
1029 p_object_version_number => p_object_version_number,
1030 x_object_version_number => l_object_version_number,
1031 x_severity_flag => l_severity_flag,
1032 x_enable_qa_yn => l_enable_qa_yn
1033 );
1034 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1035 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1036 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1037 RAISE FND_API.G_EXC_ERROR;
1038 END IF;
1039
1040 IF (l_debug = 'Y') THEN
1041 Okc_Debug.Log('7400: Record Validation', 2);
1042 END IF;
1043
1044 --- Validate all non-missing attributes
1045 x_return_status := Validate_Record(
1046 p_validation_level => p_validation_level,
1047 p_qa_code => p_qa_code,
1048 p_document_type => p_document_type,
1049 p_severity_flag => l_severity_flag,
1050 p_enable_qa_yn => l_enable_qa_yn
1051 );
1052 --- If any errors happen abort API
1053 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1055 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1056 RAISE FND_API.G_EXC_ERROR;
1057 END IF;
1058
1059 IF (l_debug = 'Y') THEN
1060 Okc_Debug.Log('7500: Filling WHO columns', 2);
1061 END IF;
1062
1063 -- Filling who columns
1064 l_last_update_date := SYSDATE;
1065 l_last_updated_by := FND_GLOBAL.USER_ID;
1066 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1067
1068 -- Object version increment
1069 IF Nvl(l_object_version_number, 0) >= 0 THEN
1070 l_object_version_number := Nvl(l_object_version_number, 0) + 1;
1071 END IF;
1072
1073 --------------------------------------------
1074 -- Call the Update_Row for each child record
1075 --------------------------------------------
1076 IF (l_debug = 'Y') THEN
1077 Okc_Debug.Log('7600: Updating Row', 2);
1078 END IF;
1079
1080 x_return_status := Update_Row(
1081 p_qa_code => p_qa_code,
1082 p_document_type => p_document_type,
1083 p_severity_flag => l_severity_flag,
1084 p_enable_qa_yn => l_enable_qa_yn,
1085 p_object_version_number => l_object_version_number,
1086 p_last_updated_by => l_last_updated_by,
1087 p_last_update_login => l_last_update_login,
1088 p_last_update_date => l_last_update_date
1089 );
1090 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1092 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1093 RAISE FND_API.G_EXC_ERROR;
1094 END IF;
1095
1096
1097 IF (l_debug = 'Y') THEN
1098 Okc_Debug.Log('7800: Leaving Update_Row', 2);
1099 END IF;
1100
1101 EXCEPTION
1102 WHEN FND_API.G_EXC_ERROR THEN
1103 IF (l_debug = 'Y') THEN
1104 Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
1105 END IF;
1106 x_return_status := G_RET_STS_ERROR;
1107
1108 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1109 IF (l_debug = 'Y') THEN
1110 Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1111 END IF;
1112 x_return_status := G_RET_STS_UNEXP_ERROR;
1113
1114 WHEN OTHERS THEN
1115 IF (l_debug = 'Y') THEN
1116 Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1117 END IF;
1118 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1119 p_msg_name => G_UNEXPECTED_ERROR,
1120 p_token1 => G_SQLCODE_TOKEN,
1121 p_token1_value => sqlcode,
1122 p_token2 => G_SQLERRM_TOKEN,
1123 p_token2_value => sqlerrm);
1124 x_return_status := G_RET_STS_UNEXP_ERROR;
1125
1126 END Update_Row;
1127
1128 ---------------------------------------------------------------------------
1129 -- PROCEDURE Delete_Row
1130 ---------------------------------------------------------------------------
1131 -------------------------------------
1132 -- Delete_Row for:OKC_DOC_QA_LISTS --
1133 -------------------------------------
1134 FUNCTION Delete_Row(
1135 p_qa_code IN VARCHAR2,
1136 p_document_type IN VARCHAR2
1137 ) RETURN VARCHAR2 IS
1138
1139 BEGIN
1140
1141 IF (l_debug = 'Y') THEN
1142 Okc_Debug.Log('8200: Entered Delete_Row', 2);
1143 END IF;
1144
1145 DELETE FROM OKC_DOC_QA_LISTS
1146 WHERE QA_CODE = p_QA_CODE AND DOCUMENT_TYPE = p_DOCUMENT_TYPE;
1147
1148 IF (l_debug = 'Y') THEN
1149 Okc_Debug.Log('8300: Leaving Delete_Row', 2);
1150 END IF;
1151
1152 RETURN( G_RET_STS_SUCCESS );
1153
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156
1157 IF (l_debug = 'Y') THEN
1158 Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1159 END IF;
1160
1161 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1162 p_msg_name => G_UNEXPECTED_ERROR,
1163 p_token1 => G_SQLCODE_TOKEN,
1164 p_token1_value => sqlcode,
1165 p_token2 => G_SQLERRM_TOKEN,
1166 p_token2_value => sqlerrm);
1167
1168 RETURN( G_RET_STS_UNEXP_ERROR );
1169
1170 END Delete_Row;
1171
1172 -------------------------------------
1173 -- Delete_Row for:OKC_DOC_QA_LISTS --
1174 -------------------------------------
1175 PROCEDURE Delete_Row(
1176 x_return_status OUT NOCOPY VARCHAR2,
1177 p_qa_code IN VARCHAR2,
1178 p_document_type IN VARCHAR2,
1179 p_object_version_number IN NUMBER
1180 ) IS
1181 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
1182 BEGIN
1183
1184 IF (l_debug = 'Y') THEN
1185 Okc_Debug.Log('8800: Entered Delete_Row', 2);
1186 Okc_Debug.Log('8900: Locking _B row', 2);
1187 END IF;
1188
1189 x_return_status := Lock_row(
1190 p_qa_code => p_qa_code,
1191 p_document_type => p_document_type,
1192 p_object_version_number => p_object_version_number
1193 );
1194 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1196 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1197 RAISE FND_API.G_EXC_ERROR;
1198 END IF;
1199
1200
1201 IF (l_debug = 'Y') THEN
1202 Okc_Debug.Log('9100: Removing _B row', 2);
1203 END IF;
1204 x_return_status := Delete_Row( p_qa_code => p_qa_code,p_document_type => p_document_type );
1205 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1207 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1208 RAISE FND_API.G_EXC_ERROR;
1209 END IF;
1210
1211
1212 IF (l_debug = 'Y') THEN
1213 Okc_Debug.Log('9300: Leaving Delete_Row', 2);
1214 END IF;
1215
1216 EXCEPTION
1217 WHEN FND_API.G_EXC_ERROR THEN
1218 IF (l_debug = 'Y') THEN
1219 Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
1220 END IF;
1221 x_return_status := G_RET_STS_ERROR;
1222
1223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1224 IF (l_debug = 'Y') THEN
1225 Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1226 END IF;
1227 x_return_status := G_RET_STS_UNEXP_ERROR;
1228
1229 WHEN OTHERS THEN
1230 IF (l_debug = 'Y') THEN
1231 Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1232 END IF;
1233 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1234 p_msg_name => G_UNEXPECTED_ERROR,
1235 p_token1 => G_SQLCODE_TOKEN,
1236 p_token1_value => sqlcode,
1237 p_token2 => G_SQLERRM_TOKEN,
1238 p_token2_value => sqlerrm);
1239 x_return_status := G_RET_STS_UNEXP_ERROR;
1240
1241 END Delete_Row;
1242
1243
1244
1245 END OKC_DOC_QA_LIST_PVT;