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