1 PACKAGE BODY OKC_BUSINESS_VARIABLES_PVT AS
2 /* $Header: OKCVBVBB.pls 120.4 2007/03/01 22:26:32 krallapa ship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
5
6 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
7 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
8 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
9
10 Function Resolve_Var_Data_Type(p_value_set_id IN NUMBER,
11 p_variable_type IN VARCHAR2,
12 p_variable_datatype IN VARCHAR2)
13 Return VARCHAR2 IS
14 l_variable_datatype OKC_BUS_VARIABLES_B.Variable_datatype%TYPE;
15 Cursor l_Data_Type_Csr(lc_value_set_id NUMBER) IS
16 Select Decode(format_type,'C','V','X','D',format_type) format_type
17 From fnd_flex_value_sets
18 Where flex_value_set_id = lc_value_set_id
19 And validation_type IN ('F','N','I');
20 Begin
21 l_variable_datatype := p_variable_datatype;
22 If p_variable_type = 'U' Then
23 Open l_Data_Type_Csr (p_value_set_id);
24 Fetch L_Data_Type_Csr INTO l_variable_datatype;
25 Close l_Data_Type_Csr;
26 End If;
27
28 Return l_variable_datatype;
29
30 Exception
31 When Others Then
32 Return l_variable_datatype;
33 End Resolve_Var_Data_Type;
34
35
36 procedure INSERT_ROW (
37 X_ROWID in out NOCOPY VARCHAR2,
38 X_VARIABLE_CODE in VARCHAR2,
39 X_VARIABLE_DEFAULT_VALUE in VARCHAR2,
40 X_VARIABLE_DATATYPE in VARCHAR2,
41 X_OBJECT_VERSION_NUMBER in NUMBER,
42 X_VARIABLE_TYPE in VARCHAR2,
43 X_EXTERNAL_YN in VARCHAR2,
44 X_APPLICATION_ID in NUMBER,
45 X_VARIABLE_INTENT in VARCHAR2,
46 X_CONTRACT_EXPERT_YN in VARCHAR2,
47 X_DISABLED_YN in VARCHAR2,
48 X_VALUE_SET_ID in NUMBER,
49 X_ORIG_SYSTEM_REFERENCE_CODE in VARCHAR2 DEFAULT NULL,
50 X_ORIG_SYSTEM_REFERENCE_ID1 in VARCHAR2 DEFAULT NULL,
51 X_ORIG_SYSTEM_REFERENCE_ID2 in VARCHAR2 DEFAULT NULL,
52 X_DATE_PUBLISHED in DATE DEFAULT NULL,
53 X_ATTRIBUTE_CATEGORY in VARCHAR2,
54 X_ATTRIBUTE1 in VARCHAR2,
55 X_ATTRIBUTE2 in VARCHAR2,
56 X_ATTRIBUTE3 in VARCHAR2,
57 X_ATTRIBUTE4 in VARCHAR2,
58 X_ATTRIBUTE5 in VARCHAR2,
59 X_ATTRIBUTE6 in VARCHAR2,
60 X_ATTRIBUTE7 in VARCHAR2,
61 X_ATTRIBUTE8 in VARCHAR2,
62 X_ATTRIBUTE9 in VARCHAR2,
63 X_ATTRIBUTE10 in VARCHAR2,
64 X_ATTRIBUTE11 in VARCHAR2,
65 X_ATTRIBUTE12 in VARCHAR2,
66 X_ATTRIBUTE13 in VARCHAR2,
67 X_ATTRIBUTE14 in VARCHAR2,
68 X_ATTRIBUTE15 in VARCHAR2,
69 X_VARIABLE_NAME in VARCHAR2,
70 X_DESCRIPTION in VARCHAR2,
71 X_CREATION_DATE in DATE,
72 X_CREATED_BY in NUMBER,
73 X_LAST_UPDATE_DATE in DATE,
74 X_LAST_UPDATED_BY in NUMBER,
75 X_LAST_UPDATE_LOGIN in NUMBER,
76 X_XPRT_VALUE_SET_NAME in VARCHAR2,
77 X_LINE_LEVEL_FLAG in VARCHAR2,
78 X_PROCEDURE_NAME in VARCHAR2,
79 X_VARIABLE_SOURCE in VARCHAR2
80 ) is
81 cursor C is select ROWID from OKC_BUS_VARIABLES_B
82 where VARIABLE_CODE = X_VARIABLE_CODE
83 ;
84 L_VARIABLE_DATATYPE OKC_BUS_VARIABLES_B.VARIABLE_DATATYPE%TYPE;
85
86
87 begin
88 L_VARIABLE_DATATYPE :=
89 Resolve_Var_Data_Type(X_VALUE_SET_ID,X_VARIABLE_TYPE,X_VARIABLE_DATATYPE);
90 insert into OKC_BUS_VARIABLES_B (
91 VARIABLE_DEFAULT_VALUE,
92 VARIABLE_DATATYPE,
93 VARIABLE_CODE,
94 OBJECT_VERSION_NUMBER,
95 VARIABLE_TYPE,
96 EXTERNAL_YN,
97 APPLICATION_ID,
98 VARIABLE_INTENT,
99 CONTRACT_EXPERT_YN,
100 DISABLED_YN,
101 VALUE_SET_ID,
102 ORIG_SYSTEM_REFERENCE_CODE,
103 ORIG_SYSTEM_REFERENCE_ID1,
104 ORIG_SYSTEM_REFERENCE_ID2,
105 DATE_PUBLISHED,
106 ATTRIBUTE_CATEGORY,
107 ATTRIBUTE1,
108 ATTRIBUTE2,
109 ATTRIBUTE3,
110 ATTRIBUTE4,
111 ATTRIBUTE5,
112 ATTRIBUTE6,
113 ATTRIBUTE7,
114 ATTRIBUTE8,
115 ATTRIBUTE9,
116 ATTRIBUTE10,
117 ATTRIBUTE11,
118 ATTRIBUTE12,
119 ATTRIBUTE13,
120 ATTRIBUTE14,
121 ATTRIBUTE15,
122 CREATION_DATE,
123 CREATED_BY,
124 LAST_UPDATE_DATE,
125 LAST_UPDATED_BY,
126 LAST_UPDATE_LOGIN,
127 XPRT_VALUE_SET_NAME,
128 LINE_LEVEL_FLAG,
129 PROCEDURE_NAME,
130 VARIABLE_SOURCE
131 ) values (
132 X_VARIABLE_DEFAULT_VALUE,
133 L_VARIABLE_DATATYPE,
134 X_VARIABLE_CODE,
135 X_OBJECT_VERSION_NUMBER,
136 X_VARIABLE_TYPE,
137 X_EXTERNAL_YN,
138 X_APPLICATION_ID,
139 X_VARIABLE_INTENT,
140 X_CONTRACT_EXPERT_YN,
141 X_DISABLED_YN,
142 X_VALUE_SET_ID,
143 X_ORIG_SYSTEM_REFERENCE_CODE,
144 X_ORIG_SYSTEM_REFERENCE_ID1,
145 X_ORIG_SYSTEM_REFERENCE_ID2,
146 X_DATE_PUBLISHED,
147 X_ATTRIBUTE_CATEGORY,
148 X_ATTRIBUTE1,
149 X_ATTRIBUTE2,
150 X_ATTRIBUTE3,
151 X_ATTRIBUTE4,
152 X_ATTRIBUTE5,
153 X_ATTRIBUTE6,
154 X_ATTRIBUTE7,
155 X_ATTRIBUTE8,
156 X_ATTRIBUTE9,
157 X_ATTRIBUTE10,
158 X_ATTRIBUTE11,
159 X_ATTRIBUTE12,
160 X_ATTRIBUTE13,
161 X_ATTRIBUTE14,
162 X_ATTRIBUTE15,
163 X_CREATION_DATE,
164 X_CREATED_BY,
165 X_LAST_UPDATE_DATE,
166 X_LAST_UPDATED_BY,
167 X_LAST_UPDATE_LOGIN,
168 X_XPRT_VALUE_SET_NAME,
169 X_LINE_LEVEL_FLAG,
170 X_PROCEDURE_NAME,
171 X_VARIABLE_SOURCE
172
173 );
174
175 insert into OKC_BUS_VARIABLES_TL (
176 VARIABLE_CODE,
177 VARIABLE_NAME,
178 DESCRIPTION,
179 CREATED_BY,
180 CREATION_DATE,
181 LAST_UPDATE_DATE,
182 LAST_UPDATED_BY,
183 LAST_UPDATE_LOGIN,
184 LANGUAGE,
185 SOURCE_LANG
186 ) select
187 X_VARIABLE_CODE,
188 X_VARIABLE_NAME,
189 X_DESCRIPTION,
190 X_CREATED_BY,
191 X_CREATION_DATE,
192 X_LAST_UPDATE_DATE,
193 X_LAST_UPDATED_BY,
194 X_LAST_UPDATE_LOGIN,
195 L.LANGUAGE_CODE,
196 userenv('LANG')
197 from FND_LANGUAGES L
198 where L.INSTALLED_FLAG in ('I', 'B')
199 and not exists
200 (select NULL
201 from OKC_BUS_VARIABLES_TL T
202 where T.VARIABLE_CODE = X_VARIABLE_CODE
203 and T.LANGUAGE = L.LANGUAGE_CODE);
204
205 open c;
206 fetch c into X_ROWID;
207 if (c%notfound) then
208 close c;
209 raise no_data_found;
210 end if;
211 close c;
212
213 end INSERT_ROW;
214
215 procedure LOCK_ROW (
216 X_VARIABLE_CODE in VARCHAR2,
217 X_VARIABLE_DEFAULT_VALUE in VARCHAR2,
218 X_VARIABLE_DATATYPE in VARCHAR2,
219 X_OBJECT_VERSION_NUMBER in NUMBER,
220 X_VARIABLE_TYPE in VARCHAR2,
221 X_EXTERNAL_YN in VARCHAR2,
222 X_APPLICATION_ID in NUMBER,
223 X_VARIABLE_INTENT in VARCHAR2,
224 X_CONTRACT_EXPERT_YN in VARCHAR2,
225 X_DISABLED_YN in VARCHAR2,
226 X_VALUE_SET_ID in NUMBER,
227 X_ORIG_SYSTEM_REFERENCE_CODE in VARCHAR2 DEFAULT NULL,
228 X_ORIG_SYSTEM_REFERENCE_ID1 in VARCHAR2 DEFAULT NULL,
229 X_ORIG_SYSTEM_REFERENCE_ID2 in VARCHAR2 DEFAULT NULL,
230 X_DATE_PUBLISHED in DATE DEFAULT NULL,
231 X_ATTRIBUTE_CATEGORY in VARCHAR2,
232 X_ATTRIBUTE1 in VARCHAR2,
233 X_ATTRIBUTE2 in VARCHAR2,
234 X_ATTRIBUTE3 in VARCHAR2,
235 X_ATTRIBUTE4 in VARCHAR2,
236 X_ATTRIBUTE5 in VARCHAR2,
237 X_ATTRIBUTE6 in VARCHAR2,
238 X_ATTRIBUTE7 in VARCHAR2,
239 X_ATTRIBUTE8 in VARCHAR2,
240 X_ATTRIBUTE9 in VARCHAR2,
241 X_ATTRIBUTE10 in VARCHAR2,
242 X_ATTRIBUTE11 in VARCHAR2,
243 X_ATTRIBUTE12 in VARCHAR2,
244 X_ATTRIBUTE13 in VARCHAR2,
245 X_ATTRIBUTE14 in VARCHAR2,
246 X_ATTRIBUTE15 in VARCHAR2,
247 X_VARIABLE_NAME in VARCHAR2,
248 X_DESCRIPTION in VARCHAR2,
249 X_XPRT_VALUE_SET_NAME in VARCHAR2,
250 X_LINE_LEVEL_FLAG in VARCHAR2,
251 X_PROCEDURE_NAME in VARCHAR2,
252 X_VARIABLE_SOURCE in VARCHAR2
253 ) is
254 cursor c is select
255 VARIABLE_DEFAULT_VALUE,
256 VARIABLE_DATATYPE,
257 OBJECT_VERSION_NUMBER,
258 VARIABLE_TYPE,
259 EXTERNAL_YN,
260 APPLICATION_ID,
261 VARIABLE_INTENT,
262 CONTRACT_EXPERT_YN,
263 DISABLED_YN,
264 VALUE_SET_ID,
265 ORIG_SYSTEM_REFERENCE_CODE,
266 ORIG_SYSTEM_REFERENCE_ID1,
267 ORIG_SYSTEM_REFERENCE_ID2,
268 DATE_PUBLISHED,
269 ATTRIBUTE_CATEGORY,
270 ATTRIBUTE1,
271 ATTRIBUTE2,
272 ATTRIBUTE3,
273 ATTRIBUTE4,
274 ATTRIBUTE5,
275 ATTRIBUTE6,
276 ATTRIBUTE7,
277 ATTRIBUTE8,
278 ATTRIBUTE9,
279 ATTRIBUTE10,
280 ATTRIBUTE11,
281 ATTRIBUTE12,
282 ATTRIBUTE13,
283 ATTRIBUTE14,
284 ATTRIBUTE15,
285 XPRT_VALUE_SET_NAME,
286 LINE_LEVEL_FLAG,
287 PROCEDURE_NAME,
288 VARIABLE_SOURCE
289 from OKC_BUS_VARIABLES_B
290 where VARIABLE_CODE = X_VARIABLE_CODE
291 for update of VARIABLE_CODE nowait;
292 recinfo c%rowtype;
293
294 cursor c1 is select
295 VARIABLE_NAME,
296 DESCRIPTION,
297 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
298 from OKC_BUS_VARIABLES_TL
299 where VARIABLE_CODE = X_VARIABLE_CODE
300 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
301 for update of VARIABLE_CODE nowait;
302 begin
303 open c;
304 fetch c into recinfo;
305 if (c%notfound) then
306 close c;
307 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
308 app_exception.raise_exception;
309 end if;
310 close c;
311 if ( ((recinfo.VARIABLE_DEFAULT_VALUE = X_VARIABLE_DEFAULT_VALUE)
312 OR ((recinfo.VARIABLE_DEFAULT_VALUE is null) AND (X_VARIABLE_DEFAULT_VALUE is null)))
313 AND ((recinfo.VARIABLE_DATATYPE = X_VARIABLE_DATATYPE)
314 OR ((recinfo.VARIABLE_DATATYPE is null) AND (X_VARIABLE_DATATYPE is null)))
315 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
316 AND (recinfo.VARIABLE_TYPE = X_VARIABLE_TYPE)
317 AND (recinfo.EXTERNAL_YN = X_EXTERNAL_YN)
318 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
319 OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
320 AND (recinfo.VARIABLE_INTENT = X_VARIABLE_INTENT)
321 AND (recinfo.CONTRACT_EXPERT_YN = X_CONTRACT_EXPERT_YN)
322 AND (recinfo.DISABLED_YN = X_DISABLED_YN)
323 AND ((recinfo.VALUE_SET_ID = X_VALUE_SET_ID)
324 OR ((recinfo.VALUE_SET_ID is null) AND (X_VALUE_SET_ID is null)))
325 AND ((recinfo.ORIG_SYSTEM_REFERENCE_CODE = X_ORIG_SYSTEM_REFERENCE_CODE)
326 OR ((recinfo.ORIG_SYSTEM_REFERENCE_CODE is null) AND (X_ORIG_SYSTEM_REFERENCE_CODE is null)))
327 AND ((recinfo.ORIG_SYSTEM_REFERENCE_ID1 = X_ORIG_SYSTEM_REFERENCE_ID1)
328 OR ((recinfo.ORIG_SYSTEM_REFERENCE_ID1 is null) AND (X_ORIG_SYSTEM_REFERENCE_ID1 is null)))
329 AND ((recinfo.ORIG_SYSTEM_REFERENCE_ID2 = X_ORIG_SYSTEM_REFERENCE_ID2)
330 OR ((recinfo.ORIG_SYSTEM_REFERENCE_ID2 is null) AND (X_ORIG_SYSTEM_REFERENCE_ID2 is null)))
331 AND ((recinfo.DATE_PUBLISHED = X_DATE_PUBLISHED)
332 OR ((recinfo.DATE_PUBLISHED is null) AND (X_DATE_PUBLISHED is null)))
333 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
334 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
335 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
336 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
337 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
338 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
339 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
340 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
341 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
342 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
343 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
344 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
345 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
346 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
347 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
348 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
349 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
350 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
351 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
352 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
353 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
354 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
355 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
356 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
357 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
358 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
359 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
360 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
361 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
362 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
363 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
364 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
365 AND ((recinfo.XPRT_VALUE_SET_NAME = X_XPRT_VALUE_SET_NAME)
366 OR ((recinfo.XPRT_VALUE_SET_NAME is null) AND (X_XPRT_VALUE_SET_NAME is null)))
367 AND ((recinfo.LINE_LEVEL_FLAG = X_LINE_LEVEL_FLAG)
368 OR ((recinfo.LINE_LEVEL_FLAG is null) AND (X_LINE_LEVEL_FLAG is null)))
369 AND ((recinfo.PROCEDURE_NAME = X_PROCEDURE_NAME)
370 OR ((recinfo.PROCEDURE_NAME is null) AND (X_PROCEDURE_NAME is null)))
371 AND ((recinfo.VARIABLE_SOURCE = X_VARIABLE_SOURCE)
372 OR ((recinfo.VARIABLE_SOURCE is null) AND (X_VARIABLE_SOURCE is null)))
373 ) then
374 null;
375 else
376 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
377 app_exception.raise_exception;
378 end if;
379
380 for tlinfo in c1 loop
381 if (tlinfo.BASELANG = 'Y') then
382 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
383 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
384 AND (tlinfo.VARIABLE_NAME = X_VARIABLE_NAME)
385 ) then
386 null;
387 else
388 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
389 app_exception.raise_exception;
390 end if;
391 end if;
392 end loop;
393 return;
394 end LOCK_ROW;
395
396 procedure UPDATE_ROW (
397 X_VARIABLE_CODE in VARCHAR2,
398 X_VARIABLE_DEFAULT_VALUE in VARCHAR2,
399 X_VARIABLE_DATATYPE in VARCHAR2,
400 X_OBJECT_VERSION_NUMBER in NUMBER,
401 X_VARIABLE_TYPE in VARCHAR2,
402 X_EXTERNAL_YN in VARCHAR2,
403 X_APPLICATION_ID in NUMBER,
404 X_VARIABLE_INTENT in VARCHAR2,
405 X_CONTRACT_EXPERT_YN in VARCHAR2,
406 X_DISABLED_YN in VARCHAR2,
407 X_VALUE_SET_ID in NUMBER,
408 X_ORIG_SYSTEM_REFERENCE_CODE in VARCHAR2,
409 X_ORIG_SYSTEM_REFERENCE_ID1 in VARCHAR2,
410 X_ORIG_SYSTEM_REFERENCE_ID2 in VARCHAR2,
411 X_DATE_PUBLISHED in DATE,
412 X_ATTRIBUTE_CATEGORY in VARCHAR2,
413 X_ATTRIBUTE1 in VARCHAR2,
414 X_ATTRIBUTE2 in VARCHAR2,
415 X_ATTRIBUTE3 in VARCHAR2,
416 X_ATTRIBUTE4 in VARCHAR2,
417 X_ATTRIBUTE5 in VARCHAR2,
418 X_ATTRIBUTE6 in VARCHAR2,
419 X_ATTRIBUTE7 in VARCHAR2,
420 X_ATTRIBUTE8 in VARCHAR2,
421 X_ATTRIBUTE9 in VARCHAR2,
422 X_ATTRIBUTE10 in VARCHAR2,
423 X_ATTRIBUTE11 in VARCHAR2,
424 X_ATTRIBUTE12 in VARCHAR2,
425 X_ATTRIBUTE13 in VARCHAR2,
426 X_ATTRIBUTE14 in VARCHAR2,
427 X_ATTRIBUTE15 in VARCHAR2,
428 X_VARIABLE_NAME in VARCHAR2,
429 X_DESCRIPTION in VARCHAR2,
430 X_LAST_UPDATE_DATE in DATE,
431 X_LAST_UPDATED_BY in NUMBER,
432 X_LAST_UPDATE_LOGIN in NUMBER,
433 X_XPRT_VALUE_SET_NAME in VARCHAR2,
434 X_LINE_LEVEL_FLAG in VARCHAR2,
435 X_PROCEDURE_NAME in VARCHAR2,
436 X_VARIABLE_SOURCE in VARCHAR2
437 ) is
438 L_VARIABLE_DATATYPE OKC_BUS_VARIABLES_B.VARIABLE_DATATYPE%TYPE;
439 begin
440 L_VARIABLE_DATATYPE :=
441 Resolve_Var_Data_Type(X_VALUE_SET_ID,X_VARIABLE_TYPE,X_VARIABLE_DATATYPE);
442 update OKC_BUS_VARIABLES_B set
443 VARIABLE_DEFAULT_VALUE = X_VARIABLE_DEFAULT_VALUE,
444 VARIABLE_DATATYPE = L_VARIABLE_DATATYPE,
445 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
446 VARIABLE_TYPE = X_VARIABLE_TYPE,
447 EXTERNAL_YN = X_EXTERNAL_YN,
448 APPLICATION_ID = X_APPLICATION_ID,
449 VARIABLE_INTENT = X_VARIABLE_INTENT,
450 CONTRACT_EXPERT_YN = X_CONTRACT_EXPERT_YN,
451 DISABLED_YN = X_DISABLED_YN,
452 VALUE_SET_ID = X_VALUE_SET_ID,
453 ORIG_SYSTEM_REFERENCE_CODE = X_ORIG_SYSTEM_REFERENCE_CODE,
454 ORIG_SYSTEM_REFERENCE_ID1 = X_ORIG_SYSTEM_REFERENCE_ID1,
455 ORIG_SYSTEM_REFERENCE_ID2 = X_ORIG_SYSTEM_REFERENCE_ID2,
456 DATE_PUBLISHED = X_DATE_PUBLISHED,
457 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
458 ATTRIBUTE1 = X_ATTRIBUTE1,
459 ATTRIBUTE2 = X_ATTRIBUTE2,
460 ATTRIBUTE3 = X_ATTRIBUTE3,
461 ATTRIBUTE4 = X_ATTRIBUTE4,
462 ATTRIBUTE5 = X_ATTRIBUTE5,
463 ATTRIBUTE6 = X_ATTRIBUTE6,
464 ATTRIBUTE7 = X_ATTRIBUTE7,
465 ATTRIBUTE8 = X_ATTRIBUTE8,
466 ATTRIBUTE9 = X_ATTRIBUTE9,
467 ATTRIBUTE10 = X_ATTRIBUTE10,
468 ATTRIBUTE11 = X_ATTRIBUTE11,
469 ATTRIBUTE12 = X_ATTRIBUTE12,
470 ATTRIBUTE13 = X_ATTRIBUTE13,
471 ATTRIBUTE14 = X_ATTRIBUTE14,
472 ATTRIBUTE15 = X_ATTRIBUTE15,
473 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
474 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
475 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
476 XPRT_VALUE_SET_NAME =X_XPRT_VALUE_SET_NAME,
477 LINE_LEVEL_FLAG = X_LINE_LEVEL_FLAG,
478 PROCEDURE_NAME = X_PROCEDURE_NAME,
479 VARIABLE_SOURCE = X_VARIABLE_SOURCE
480 where VARIABLE_CODE = X_VARIABLE_CODE;
481
482 if (sql%notfound) then
483 raise no_data_found;
484 end if;
485
486 update OKC_BUS_VARIABLES_TL set
487 VARIABLE_NAME = X_VARIABLE_NAME,
488 DESCRIPTION = X_DESCRIPTION,
489 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
490 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
491 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
492 SOURCE_LANG = userenv('LANG')
493 where VARIABLE_CODE = X_VARIABLE_CODE
494 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
495
496 if (sql%notfound) then
497 raise no_data_found;
498 end if;
499 end UPDATE_ROW;
500
501 procedure DELETE_ROW (
502 X_VARIABLE_CODE in VARCHAR2
503 ) is
504 l_existing_variables_tbl variable_code_tbl_type;
505
506 CURSOR variable_doc_assoc_csr (cp_variable_code IN VARCHAR) IS
507 SELECT VARIABLE_CODE FROM OKC_VARIABLE_DOC_TYPES
508 WHERE VARIABLE_CODE = cp_variable_code;
509 begin
510 delete from OKC_BUS_VARIABLES_TL
511 where VARIABLE_CODE = X_VARIABLE_CODE;
512
513 if (sql%notfound) then
514 raise no_data_found;
515
516 end if;
517
518 delete from OKC_BUS_VARIABLES_B
519 where VARIABLE_CODE = X_VARIABLE_CODE;
520
521 if (sql%notfound) then
522 raise no_data_found;
523 end if;
524
525 OPEN variable_doc_assoc_csr(X_VARIABLE_CODE);
526 FETCH variable_doc_assoc_csr BULK COLLECT INTO l_existing_variables_tbl;
527 CLOSE variable_doc_assoc_csr;
528
529 IF l_existing_variables_tbl.COUNT > 0 Then
530 FORALL i in l_existing_variables_tbl.FIRST .. l_existing_variables_tbl.LAST
531 DELETE FROM OKC_VARIABLE_DOC_TYPES
532 WHERE VARIABLE_CODE = l_existing_variables_tbl(i);
533 END IF;
534
535
536 EXCEPTION
537 WHEN NO_DATA_FOUND
538 THEN
539 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_VAR_DEL_ERROR');
540 WHEN OTHERS THEN
541 IF (l_debug = 'Y') THEN
542 okc_debug.log('1750: Leaving DELETE_ROW in OKC_BUSINESS_VARIABLES_PVT because of EXCEPTION: '||sqlerrm, 2);
543 END IF;
544 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
545 p_msg_name => G_UNEXPECTED_ERROR,
546 p_token1 => G_SQLCODE_TOKEN,
547 p_token1_value => sqlcode,
548 p_token2 => G_SQLERRM_TOKEN,
549 p_token2_value => sqlerrm);
550
551 IF variable_doc_assoc_csr%ISOPEN THEN
552 CLOSE variable_doc_assoc_csr;
553 END IF;
554 end DELETE_ROW;
555
556 procedure ADD_LANGUAGE
557 is
558 begin
559 delete from OKC_BUS_VARIABLES_TL T
560 where not exists
561 (select NULL
562 from OKC_BUS_VARIABLES_B B
563 where B.VARIABLE_CODE = T.VARIABLE_CODE
564 );
565
566 update OKC_BUS_VARIABLES_TL T set (
567 VARIABLE_NAME,
568 DESCRIPTION
569 ) = (select
570 B.VARIABLE_NAME,
571 B.DESCRIPTION
572 from OKC_BUS_VARIABLES_TL B
573 where B.VARIABLE_CODE = T.VARIABLE_CODE
574 and B.LANGUAGE = T.SOURCE_LANG)
575 where (
576 T.VARIABLE_CODE,
577 T.LANGUAGE
578 ) in (select
579 SUBT.VARIABLE_CODE,
580 SUBT.LANGUAGE
581 from OKC_BUS_VARIABLES_TL SUBB, OKC_BUS_VARIABLES_TL SUBT
582 where SUBB.VARIABLE_CODE = SUBT.VARIABLE_CODE
583 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
584 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
585 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
586 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
587 or SUBB.VARIABLE_NAME <> SUBT.VARIABLE_NAME
588 ));
589
590 insert into OKC_BUS_VARIABLES_TL (
591 VARIABLE_CODE,
592 VARIABLE_NAME,
593 DESCRIPTION,
594 CREATED_BY,
595 CREATION_DATE,
596 LAST_UPDATE_DATE,
597 LAST_UPDATED_BY,
598 LAST_UPDATE_LOGIN,
599 LANGUAGE,
600 SOURCE_LANG
601 ) select
602 B.VARIABLE_CODE,
603 B.VARIABLE_NAME,
604 B.DESCRIPTION,
605 B.CREATED_BY,
606 B.CREATION_DATE,
607 B.LAST_UPDATE_DATE,
608 B.LAST_UPDATED_BY,
609 B.LAST_UPDATE_LOGIN,
610 L.LANGUAGE_CODE,
611 B.SOURCE_LANG
612 from OKC_BUS_VARIABLES_TL B, FND_LANGUAGES L
613 where L.INSTALLED_FLAG in ('I', 'B')
614 and B.LANGUAGE = userenv('LANG')
615 and not exists
616 (select NULL
617 from OKC_BUS_VARIABLES_TL T
618 where T.VARIABLE_CODE = B.VARIABLE_CODE
619 and T.LANGUAGE = L.LANGUAGE_CODE);
620 end ADD_LANGUAGE;
621
622
623
624
625
626 END OKC_BUSINESS_VARIABLES_PVT;
627
[Home] [Help]