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