[Home] [Help]
PACKAGE BODY: APPS.OKC_BUSINESS_VARIABLES_PVT
Source
1 PACKAGE BODY okc_business_variables_pvt AS
2 /* $Header: OKCVBVBB.pls 120.6 2011/06/10 11:29:52 serukull 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, -- CLM Changes
80 X_CLM_SOURCE IN VARCHAR2, -- CLM Changes
81 X_CLM_REF1 IN VARCHAR2, -- CLM Changes
82 X_CLM_REF2 IN VARCHAR2, -- CLM Changes
83 X_CLM_REF3 IN VARCHAR2, -- CLM Changes
84 X_CLM_REF4 IN VARCHAR2, -- CLM Changes
85 X_CLM_REF5 IN VARCHAR2, -- CLM Changes
86 X_MRV_FLAG IN VARCHAR2, -- MRV Changes
87 X_MRV_TMPL_CODE IN VARCHAR2 -- MRV Changes
88 ) is
89 cursor C is select ROWID from OKC_BUS_VARIABLES_B
90 where VARIABLE_CODE = X_VARIABLE_CODE
91 ;
92 L_VARIABLE_DATATYPE OKC_BUS_VARIABLES_B.VARIABLE_DATATYPE%TYPE;
93
94
95 begin
96 L_VARIABLE_DATATYPE :=
97 Resolve_Var_Data_Type(X_VALUE_SET_ID,X_VARIABLE_TYPE,X_VARIABLE_DATATYPE);
98 insert into OKC_BUS_VARIABLES_B (
99 VARIABLE_DEFAULT_VALUE,
100 VARIABLE_DATATYPE,
101 VARIABLE_CODE,
102 OBJECT_VERSION_NUMBER,
103 VARIABLE_TYPE,
104 EXTERNAL_YN,
105 APPLICATION_ID,
106 VARIABLE_INTENT,
107 CONTRACT_EXPERT_YN,
108 DISABLED_YN,
109 VALUE_SET_ID,
110 ORIG_SYSTEM_REFERENCE_CODE,
111 ORIG_SYSTEM_REFERENCE_ID1,
112 ORIG_SYSTEM_REFERENCE_ID2,
113 DATE_PUBLISHED,
114 ATTRIBUTE_CATEGORY,
115 ATTRIBUTE1,
116 ATTRIBUTE2,
117 ATTRIBUTE3,
118 ATTRIBUTE4,
119 ATTRIBUTE5,
120 ATTRIBUTE6,
121 ATTRIBUTE7,
122 ATTRIBUTE8,
123 ATTRIBUTE9,
124 ATTRIBUTE10,
125 ATTRIBUTE11,
126 ATTRIBUTE12,
127 ATTRIBUTE13,
128 ATTRIBUTE14,
129 ATTRIBUTE15,
130 CREATION_DATE,
131 CREATED_BY,
132 LAST_UPDATE_DATE,
133 LAST_UPDATED_BY,
134 LAST_UPDATE_LOGIN,
135 XPRT_VALUE_SET_NAME,
136 LINE_LEVEL_FLAG,
137 PROCEDURE_NAME,
138 VARIABLE_SOURCE, -- CLM Changes
139 CLM_SOURCE, -- CLM Changes
140 CLM_REF1, -- CLM Changes
141 CLM_REF2, -- CLM Changes
142 CLM_REF3, -- CLM Changes
143 CLM_REF4, -- CLM Changes
144 CLM_REF5, -- CLM Changes
145 mrv_flag, -- MRV Changes
146 mrv_tmpl_code -- MRV Changes
147
148 ) values (
149 X_VARIABLE_DEFAULT_VALUE,
150 L_VARIABLE_DATATYPE,
151 X_VARIABLE_CODE,
152 X_OBJECT_VERSION_NUMBER,
153 X_VARIABLE_TYPE,
154 X_EXTERNAL_YN,
155 X_APPLICATION_ID,
156 X_VARIABLE_INTENT,
157 X_CONTRACT_EXPERT_YN,
158 X_DISABLED_YN,
159 X_VALUE_SET_ID,
160 X_ORIG_SYSTEM_REFERENCE_CODE,
161 X_ORIG_SYSTEM_REFERENCE_ID1,
162 X_ORIG_SYSTEM_REFERENCE_ID2,
163 X_DATE_PUBLISHED,
164 X_ATTRIBUTE_CATEGORY,
165 X_ATTRIBUTE1,
166 X_ATTRIBUTE2,
167 X_ATTRIBUTE3,
168 X_ATTRIBUTE4,
169 X_ATTRIBUTE5,
170 X_ATTRIBUTE6,
171 X_ATTRIBUTE7,
172 X_ATTRIBUTE8,
173 X_ATTRIBUTE9,
174 X_ATTRIBUTE10,
175 X_ATTRIBUTE11,
176 X_ATTRIBUTE12,
177 X_ATTRIBUTE13,
178 X_ATTRIBUTE14,
179 X_ATTRIBUTE15,
180 X_CREATION_DATE,
181 X_CREATED_BY,
182 X_LAST_UPDATE_DATE,
183 X_LAST_UPDATED_BY,
184 X_LAST_UPDATE_LOGIN,
185 X_XPRT_VALUE_SET_NAME,
186 X_LINE_LEVEL_FLAG,
187 X_PROCEDURE_NAME,
188 X_VARIABLE_SOURCE, -- CLM Changes
189 X_CLM_SOURCE, -- CLM Changes
190 X_CLM_REF1, -- CLM Changes
191 X_CLM_REF2, -- CLM Changes
192 X_CLM_REF3, -- CLM Changes
193 X_CLM_REF4, -- CLM Changes
194 X_CLM_REF5, -- CLM Changes
195 X_MRV_FLAG, -- MRV Changes
196 X_MRV_TMPL_CODE -- MRV Changes
197 );
198
199 insert into OKC_BUS_VARIABLES_TL (
200 VARIABLE_CODE,
201 VARIABLE_NAME,
202 DESCRIPTION,
203 CREATED_BY,
204 CREATION_DATE,
205 LAST_UPDATE_DATE,
206 LAST_UPDATED_BY,
207 LAST_UPDATE_LOGIN,
208 LANGUAGE,
209 SOURCE_LANG
210 ) select
211 X_VARIABLE_CODE,
212 X_VARIABLE_NAME,
213 X_DESCRIPTION,
214 X_CREATED_BY,
215 X_CREATION_DATE,
216 X_LAST_UPDATE_DATE,
217 X_LAST_UPDATED_BY,
218 X_LAST_UPDATE_LOGIN,
219 L.LANGUAGE_CODE,
220 userenv('LANG')
221 from FND_LANGUAGES L
222 where L.INSTALLED_FLAG in ('I', 'B')
223 and not exists
224 (select NULL
225 from OKC_BUS_VARIABLES_TL T
226 where T.VARIABLE_CODE = X_VARIABLE_CODE
227 and T.LANGUAGE = L.LANGUAGE_CODE);
228
229 open c;
230 fetch c into X_ROWID;
231 if (c%notfound) then
232 close c;
233 raise no_data_found;
234 end if;
235 close c;
236
237 end INSERT_ROW;
238
239 procedure LOCK_ROW (
240 X_VARIABLE_CODE in VARCHAR2,
241 X_VARIABLE_DEFAULT_VALUE in VARCHAR2,
242 X_VARIABLE_DATATYPE in VARCHAR2,
243 X_OBJECT_VERSION_NUMBER in NUMBER,
244 X_VARIABLE_TYPE in VARCHAR2,
245 X_EXTERNAL_YN in VARCHAR2,
246 X_APPLICATION_ID in NUMBER,
247 X_VARIABLE_INTENT in VARCHAR2,
248 X_CONTRACT_EXPERT_YN in VARCHAR2,
249 X_DISABLED_YN in VARCHAR2,
250 X_VALUE_SET_ID in NUMBER,
251 X_ORIG_SYSTEM_REFERENCE_CODE in VARCHAR2 DEFAULT NULL,
252 X_ORIG_SYSTEM_REFERENCE_ID1 in VARCHAR2 DEFAULT NULL,
253 X_ORIG_SYSTEM_REFERENCE_ID2 in VARCHAR2 DEFAULT NULL,
254 X_DATE_PUBLISHED in DATE DEFAULT NULL,
255 X_ATTRIBUTE_CATEGORY in VARCHAR2,
256 X_ATTRIBUTE1 in VARCHAR2,
257 X_ATTRIBUTE2 in VARCHAR2,
258 X_ATTRIBUTE3 in VARCHAR2,
259 X_ATTRIBUTE4 in VARCHAR2,
260 X_ATTRIBUTE5 in VARCHAR2,
261 X_ATTRIBUTE6 in VARCHAR2,
262 X_ATTRIBUTE7 in VARCHAR2,
263 X_ATTRIBUTE8 in VARCHAR2,
264 X_ATTRIBUTE9 in VARCHAR2,
265 X_ATTRIBUTE10 in VARCHAR2,
266 X_ATTRIBUTE11 in VARCHAR2,
267 X_ATTRIBUTE12 in VARCHAR2,
268 X_ATTRIBUTE13 in VARCHAR2,
269 X_ATTRIBUTE14 in VARCHAR2,
270 X_ATTRIBUTE15 in VARCHAR2,
271 X_VARIABLE_NAME in VARCHAR2,
272 X_DESCRIPTION in VARCHAR2,
273 X_XPRT_VALUE_SET_NAME in VARCHAR2,
274 X_LINE_LEVEL_FLAG in VARCHAR2,
275 X_PROCEDURE_NAME in VARCHAR2,
276 X_VARIABLE_SOURCE in VARCHAR2, -- CLM Changes
277 X_CLM_SOURCE IN VARCHAR2, -- CLM Changes
278 X_CLM_REF1 IN VARCHAR2, -- CLM Changes
279 X_CLM_REF2 IN VARCHAR2, -- CLM Changes
280 X_CLM_REF3 IN VARCHAR2, -- CLM Changes
281 X_CLM_REF4 IN VARCHAR2, -- CLM Changes
282 X_CLM_REF5 IN VARCHAR2, -- CLM Changes
283 X_MRV_FLAG IN VARCHAR2, -- MRV Changes
284 X_MRV_TMPL_CODE IN VARCHAR2 -- MRV Changes
285 ) is
286 cursor c is select
287 VARIABLE_DEFAULT_VALUE,
288 VARIABLE_DATATYPE,
289 OBJECT_VERSION_NUMBER,
290 VARIABLE_TYPE,
291 EXTERNAL_YN,
292 APPLICATION_ID,
293 VARIABLE_INTENT,
294 CONTRACT_EXPERT_YN,
295 DISABLED_YN,
296 VALUE_SET_ID,
297 ORIG_SYSTEM_REFERENCE_CODE,
298 ORIG_SYSTEM_REFERENCE_ID1,
299 ORIG_SYSTEM_REFERENCE_ID2,
300 DATE_PUBLISHED,
301 ATTRIBUTE_CATEGORY,
302 ATTRIBUTE1,
303 ATTRIBUTE2,
304 ATTRIBUTE3,
305 ATTRIBUTE4,
306 ATTRIBUTE5,
307 ATTRIBUTE6,
308 ATTRIBUTE7,
309 ATTRIBUTE8,
310 ATTRIBUTE9,
311 ATTRIBUTE10,
312 ATTRIBUTE11,
313 ATTRIBUTE12,
314 ATTRIBUTE13,
315 ATTRIBUTE14,
316 ATTRIBUTE15,
317 XPRT_VALUE_SET_NAME,
318 LINE_LEVEL_FLAG,
319 PROCEDURE_NAME,
320 VARIABLE_SOURCE, -- CLM Changes
321 CLM_SOURCE, -- CLM Changes
322 CLM_REF1, -- CLM Changes
323 CLM_REF2, -- CLM Changes
324 CLM_REF3, -- CLM Changes
325 CLM_REF4, -- CLM Changes
326 CLM_REF5, -- CLM Changes
327 MRV_FLAG, -- MRV Changes
328 MRV_TMPL_CODE -- MRV Changes
329 from OKC_BUS_VARIABLES_B
330 where VARIABLE_CODE = X_VARIABLE_CODE
331 for update of VARIABLE_CODE nowait;
332 recinfo c%rowtype;
333
334 cursor c1 is select
335 VARIABLE_NAME,
336 DESCRIPTION,
337 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
338 from OKC_BUS_VARIABLES_TL
339 where VARIABLE_CODE = X_VARIABLE_CODE
340 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
341 for update of VARIABLE_CODE nowait;
342 begin
343 open c;
344 fetch c into recinfo;
345 if (c%notfound) then
346 close c;
347 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
348 app_exception.raise_exception;
349 end if;
350 close c;
351 if ( ((recinfo.VARIABLE_DEFAULT_VALUE = X_VARIABLE_DEFAULT_VALUE)
352 OR ((recinfo.VARIABLE_DEFAULT_VALUE is null) AND (X_VARIABLE_DEFAULT_VALUE is null)))
353 AND ((recinfo.VARIABLE_DATATYPE = X_VARIABLE_DATATYPE)
354 OR ((recinfo.VARIABLE_DATATYPE is null) AND (X_VARIABLE_DATATYPE is null)))
355 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
356 AND (recinfo.VARIABLE_TYPE = X_VARIABLE_TYPE)
357 AND (recinfo.EXTERNAL_YN = X_EXTERNAL_YN)
358 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
359 OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
360 AND (recinfo.VARIABLE_INTENT = X_VARIABLE_INTENT)
361 AND (recinfo.CONTRACT_EXPERT_YN = X_CONTRACT_EXPERT_YN)
362 AND (recinfo.DISABLED_YN = X_DISABLED_YN)
363 AND ((recinfo.VALUE_SET_ID = X_VALUE_SET_ID)
364 OR ((recinfo.VALUE_SET_ID is null) AND (X_VALUE_SET_ID is null)))
365 AND ((recinfo.ORIG_SYSTEM_REFERENCE_CODE = X_ORIG_SYSTEM_REFERENCE_CODE)
366 OR ((recinfo.ORIG_SYSTEM_REFERENCE_CODE is null) AND (X_ORIG_SYSTEM_REFERENCE_CODE is null)))
367 AND ((recinfo.ORIG_SYSTEM_REFERENCE_ID1 = X_ORIG_SYSTEM_REFERENCE_ID1)
368 OR ((recinfo.ORIG_SYSTEM_REFERENCE_ID1 is null) AND (X_ORIG_SYSTEM_REFERENCE_ID1 is null)))
369 AND ((recinfo.ORIG_SYSTEM_REFERENCE_ID2 = X_ORIG_SYSTEM_REFERENCE_ID2)
370 OR ((recinfo.ORIG_SYSTEM_REFERENCE_ID2 is null) AND (X_ORIG_SYSTEM_REFERENCE_ID2 is null)))
371 AND ((recinfo.DATE_PUBLISHED = X_DATE_PUBLISHED)
372 OR ((recinfo.DATE_PUBLISHED is null) AND (X_DATE_PUBLISHED is null)))
373 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
374 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
375 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
376 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
377 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
378 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
379 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
380 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
381 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
382 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
383 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
384 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
385 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
386 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
387 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
388 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
389 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
390 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
391 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
392 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
393 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
394 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
395 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
396 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
397 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
398 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
399 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
400 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
401 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
402 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
403 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
404 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
405 AND ((recinfo.XPRT_VALUE_SET_NAME = X_XPRT_VALUE_SET_NAME)
406 OR ((recinfo.XPRT_VALUE_SET_NAME is null) AND (X_XPRT_VALUE_SET_NAME is null)))
407 AND ((recinfo.LINE_LEVEL_FLAG = X_LINE_LEVEL_FLAG)
408 OR ((recinfo.LINE_LEVEL_FLAG is null) AND (X_LINE_LEVEL_FLAG is null)))
409 AND ((recinfo.PROCEDURE_NAME = X_PROCEDURE_NAME)
410 OR ((recinfo.PROCEDURE_NAME is null) AND (X_PROCEDURE_NAME is null)))
411 AND ((recinfo.VARIABLE_SOURCE = X_VARIABLE_SOURCE)
412 OR ((recinfo.VARIABLE_SOURCE is null) AND (X_VARIABLE_SOURCE is null)))
413 -- CLM Changes Begins
414 AND ((recinfo.CLM_SOURCE = X_CLM_SOURCE)
415 OR ((recinfo.CLM_SOURCE is null) AND (X_CLM_SOURCE is null)))
416 AND ((recinfo.CLM_REF1 = X_CLM_REF1)
417 OR ((recinfo.CLM_REF1 is null) AND (X_CLM_REF1 is null)))
418 AND ((recinfo.CLM_REF2 = X_CLM_REF2)
419 OR ((recinfo.CLM_REF2 is null) AND (X_CLM_REF2 is null)))
420 AND ((recinfo.CLM_REF3 = X_CLM_REF3)
421 OR ((recinfo.CLM_REF3 is null) AND (X_CLM_REF3 is null)))
422 AND ((recinfo.CLM_REF4 = X_CLM_REF4)
423 OR ((recinfo.CLM_REF4 is null) AND (X_CLM_REF4 is null)))
424 AND ((recinfo.CLM_REF5 = X_CLM_REF5)
425 OR ((recinfo.CLM_REF5 is null) AND (X_CLM_REF5 is null)))
426 -- CLM Changes Ends
427 -- MRV Changes Start
428 AND ((recinfo.mrv_flag = X_mrv_flag)
429 OR ((recinfo.mrv_flag is null) AND (X_mrv_flag is null)))
430
431 AND ((recinfo.mrv_tmpl_code = X_mrv_tmpl_code)
432 OR ((recinfo.mrv_tmpl_code is null) AND (X_mrv_tmpl_code is null)))
433 -- MRV Changes End
434 ) then
435 null;
436 else
437 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
438 app_exception.raise_exception;
439 end if;
440
441 for tlinfo in c1 loop
442 if (tlinfo.BASELANG = 'Y') then
443 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
444 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
445 AND (tlinfo.VARIABLE_NAME = X_VARIABLE_NAME)
446 ) then
447 null;
448 else
449 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
450 app_exception.raise_exception;
451 end if;
452 end if;
453 end loop;
454 return;
455 end LOCK_ROW;
456
457 procedure UPDATE_ROW (
458 X_VARIABLE_CODE in VARCHAR2,
459 X_VARIABLE_DEFAULT_VALUE in VARCHAR2,
460 X_VARIABLE_DATATYPE in VARCHAR2,
461 X_OBJECT_VERSION_NUMBER in NUMBER,
462 X_VARIABLE_TYPE in VARCHAR2,
463 X_EXTERNAL_YN in VARCHAR2,
464 X_APPLICATION_ID in NUMBER,
465 X_VARIABLE_INTENT in VARCHAR2,
466 X_CONTRACT_EXPERT_YN in VARCHAR2,
467 X_DISABLED_YN in VARCHAR2,
468 X_VALUE_SET_ID in NUMBER,
469 X_ORIG_SYSTEM_REFERENCE_CODE in VARCHAR2,
470 X_ORIG_SYSTEM_REFERENCE_ID1 in VARCHAR2,
471 X_ORIG_SYSTEM_REFERENCE_ID2 in VARCHAR2,
472 X_DATE_PUBLISHED in DATE,
473 X_ATTRIBUTE_CATEGORY in VARCHAR2,
474 X_ATTRIBUTE1 in VARCHAR2,
475 X_ATTRIBUTE2 in VARCHAR2,
476 X_ATTRIBUTE3 in VARCHAR2,
477 X_ATTRIBUTE4 in VARCHAR2,
478 X_ATTRIBUTE5 in VARCHAR2,
479 X_ATTRIBUTE6 in VARCHAR2,
480 X_ATTRIBUTE7 in VARCHAR2,
481 X_ATTRIBUTE8 in VARCHAR2,
482 X_ATTRIBUTE9 in VARCHAR2,
483 X_ATTRIBUTE10 in VARCHAR2,
484 X_ATTRIBUTE11 in VARCHAR2,
485 X_ATTRIBUTE12 in VARCHAR2,
486 X_ATTRIBUTE13 in VARCHAR2,
487 X_ATTRIBUTE14 in VARCHAR2,
488 X_ATTRIBUTE15 in VARCHAR2,
489 X_VARIABLE_NAME in VARCHAR2,
490 X_DESCRIPTION in VARCHAR2,
491 X_LAST_UPDATE_DATE in DATE,
492 X_LAST_UPDATED_BY in NUMBER,
493 X_LAST_UPDATE_LOGIN in NUMBER,
494 X_XPRT_VALUE_SET_NAME in VARCHAR2,
495 X_LINE_LEVEL_FLAG in VARCHAR2,
496 X_PROCEDURE_NAME in VARCHAR2,
497 X_VARIABLE_SOURCE in VARCHAR2, -- CLM Changes
498 X_CLM_SOURCE IN VARCHAR2, -- CLM Changes
499 X_CLM_REF1 IN VARCHAR2, -- CLM Changes
500 X_CLM_REF2 IN VARCHAR2, -- CLM Changes
501 X_CLM_REF3 IN VARCHAR2, -- CLM Changes
502 X_CLM_REF4 IN VARCHAR2, -- CLM Changes
503 X_CLM_REF5 IN VARCHAR2, -- CLM Changes
504 X_MRV_FLAG IN VARCHAR2, -- MRV Changes
505 X_MRV_TMPL_CODE IN VARCHAR2 -- MRV Changes
506 ) is
507 L_VARIABLE_DATATYPE OKC_BUS_VARIABLES_B.VARIABLE_DATATYPE%TYPE;
508 begin
509 L_VARIABLE_DATATYPE :=
510 Resolve_Var_Data_Type(X_VALUE_SET_ID,X_VARIABLE_TYPE,X_VARIABLE_DATATYPE);
511 update OKC_BUS_VARIABLES_B set
512 VARIABLE_DEFAULT_VALUE = X_VARIABLE_DEFAULT_VALUE,
513 VARIABLE_DATATYPE = L_VARIABLE_DATATYPE,
514 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
515 VARIABLE_TYPE = X_VARIABLE_TYPE,
516 EXTERNAL_YN = X_EXTERNAL_YN,
517 APPLICATION_ID = X_APPLICATION_ID,
518 VARIABLE_INTENT = X_VARIABLE_INTENT,
519 CONTRACT_EXPERT_YN = X_CONTRACT_EXPERT_YN,
520 DISABLED_YN = X_DISABLED_YN,
521 VALUE_SET_ID = X_VALUE_SET_ID,
522 ORIG_SYSTEM_REFERENCE_CODE = X_ORIG_SYSTEM_REFERENCE_CODE,
523 ORIG_SYSTEM_REFERENCE_ID1 = X_ORIG_SYSTEM_REFERENCE_ID1,
524 ORIG_SYSTEM_REFERENCE_ID2 = X_ORIG_SYSTEM_REFERENCE_ID2,
525 DATE_PUBLISHED = X_DATE_PUBLISHED,
526 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
527 ATTRIBUTE1 = X_ATTRIBUTE1,
528 ATTRIBUTE2 = X_ATTRIBUTE2,
529 ATTRIBUTE3 = X_ATTRIBUTE3,
530 ATTRIBUTE4 = X_ATTRIBUTE4,
531 ATTRIBUTE5 = X_ATTRIBUTE5,
532 ATTRIBUTE6 = X_ATTRIBUTE6,
533 ATTRIBUTE7 = X_ATTRIBUTE7,
534 ATTRIBUTE8 = X_ATTRIBUTE8,
535 ATTRIBUTE9 = X_ATTRIBUTE9,
536 ATTRIBUTE10 = X_ATTRIBUTE10,
537 ATTRIBUTE11 = X_ATTRIBUTE11,
538 ATTRIBUTE12 = X_ATTRIBUTE12,
539 ATTRIBUTE13 = X_ATTRIBUTE13,
540 ATTRIBUTE14 = X_ATTRIBUTE14,
541 ATTRIBUTE15 = X_ATTRIBUTE15,
542 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
543 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
544 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
545 XPRT_VALUE_SET_NAME =X_XPRT_VALUE_SET_NAME,
546 LINE_LEVEL_FLAG = X_LINE_LEVEL_FLAG,
547 PROCEDURE_NAME = X_PROCEDURE_NAME,
548 VARIABLE_SOURCE = X_VARIABLE_SOURCE, -- CLM Changes
549 CLM_SOURCE = X_CLM_SOURCE, -- CLM Changes
550 CLM_REF1 = X_CLM_REF1, -- CLM Changes
551 CLM_REF2 = X_CLM_REF2, -- CLM Changes
552 CLM_REF3 = X_CLM_REF3, -- CLM Changes
553 CLM_REF4 = X_CLM_REF4, -- CLM Changes
554 CLM_REF5 = X_CLM_REF5, -- CLM Changes
555 MRV_FLAG = X_MRV_FLAG, -- MRV Changes
556 MRV_TMPL_CODE = X_MRV_TMPL_CODE -- MRV Changes
557 where VARIABLE_CODE = X_VARIABLE_CODE;
558
559 if (sql%notfound) then
560 raise no_data_found;
561 end if;
562
563 update OKC_BUS_VARIABLES_TL set
564 VARIABLE_NAME = X_VARIABLE_NAME,
565 DESCRIPTION = X_DESCRIPTION,
566 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
567 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
568 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
569 SOURCE_LANG = userenv('LANG')
570 where VARIABLE_CODE = X_VARIABLE_CODE
571 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
572
573 if (sql%notfound) then
574 raise no_data_found;
575 end if;
576 end UPDATE_ROW;
577
578 procedure DELETE_ROW (
579 X_VARIABLE_CODE in VARCHAR2
580 ) is
581 l_existing_variables_tbl variable_code_tbl_type;
582
583 CURSOR variable_doc_assoc_csr (cp_variable_code IN VARCHAR) IS
584 SELECT VARIABLE_CODE FROM OKC_VARIABLE_DOC_TYPES
585 WHERE VARIABLE_CODE = cp_variable_code;
586 begin
587 delete from OKC_BUS_VARIABLES_TL
588 where VARIABLE_CODE = X_VARIABLE_CODE;
589
590 if (sql%notfound) then
591 raise no_data_found;
592
593 end if;
594
595 delete from OKC_BUS_VARIABLES_B
596 where VARIABLE_CODE = X_VARIABLE_CODE;
597
598 if (sql%notfound) then
599 raise no_data_found;
600 end if;
601
602 OPEN variable_doc_assoc_csr(X_VARIABLE_CODE);
603 FETCH variable_doc_assoc_csr BULK COLLECT INTO l_existing_variables_tbl;
604 CLOSE variable_doc_assoc_csr;
605
606 IF l_existing_variables_tbl.COUNT > 0 Then
607 FORALL i in l_existing_variables_tbl.FIRST .. l_existing_variables_tbl.LAST
608 DELETE FROM OKC_VARIABLE_DOC_TYPES
609 WHERE VARIABLE_CODE = l_existing_variables_tbl(i);
610 END IF;
611
612
613 EXCEPTION
614 WHEN NO_DATA_FOUND
615 THEN
616 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_VAR_DEL_ERROR');
617 WHEN OTHERS THEN
618 IF (l_debug = 'Y') THEN
619 okc_debug.log('1750: Leaving DELETE_ROW in OKC_BUSINESS_VARIABLES_PVT because of EXCEPTION: '||sqlerrm, 2);
620 END IF;
621 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
622 p_msg_name => G_UNEXPECTED_ERROR,
623 p_token1 => G_SQLCODE_TOKEN,
624 p_token1_value => sqlcode,
625 p_token2 => G_SQLERRM_TOKEN,
626 p_token2_value => sqlerrm);
627
628 IF variable_doc_assoc_csr%ISOPEN THEN
629 CLOSE variable_doc_assoc_csr;
630 END IF;
631 end DELETE_ROW;
632
633 procedure ADD_LANGUAGE
634 is
635 begin
636 delete from OKC_BUS_VARIABLES_TL T
637 where not exists
638 (select NULL
639 from OKC_BUS_VARIABLES_B B
640 where B.VARIABLE_CODE = T.VARIABLE_CODE
641 );
642
643 update OKC_BUS_VARIABLES_TL T set (
644 VARIABLE_NAME,
645 DESCRIPTION
646 ) = (select
647 B.VARIABLE_NAME,
648 B.DESCRIPTION
649 from OKC_BUS_VARIABLES_TL B
650 where B.VARIABLE_CODE = T.VARIABLE_CODE
651 and B.LANGUAGE = T.SOURCE_LANG)
652 where (
653 T.VARIABLE_CODE,
654 T.LANGUAGE
655 ) in (select
656 SUBT.VARIABLE_CODE,
657 SUBT.LANGUAGE
658 from OKC_BUS_VARIABLES_TL SUBB, OKC_BUS_VARIABLES_TL SUBT
659 where SUBB.VARIABLE_CODE = SUBT.VARIABLE_CODE
660 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
661 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
662 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
663 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
664 or SUBB.VARIABLE_NAME <> SUBT.VARIABLE_NAME
665 ));
666
667 insert into OKC_BUS_VARIABLES_TL (
668 VARIABLE_CODE,
669 VARIABLE_NAME,
670 DESCRIPTION,
671 CREATED_BY,
672 CREATION_DATE,
673 LAST_UPDATE_DATE,
674 LAST_UPDATED_BY,
675 LAST_UPDATE_LOGIN,
676 LANGUAGE,
677 SOURCE_LANG
678 ) select
679 B.VARIABLE_CODE,
680 B.VARIABLE_NAME,
681 B.DESCRIPTION,
682 B.CREATED_BY,
683 B.CREATION_DATE,
684 B.LAST_UPDATE_DATE,
685 B.LAST_UPDATED_BY,
686 B.LAST_UPDATE_LOGIN,
687 L.LANGUAGE_CODE,
688 B.SOURCE_LANG
689 from OKC_BUS_VARIABLES_TL B, FND_LANGUAGES L
690 where L.INSTALLED_FLAG in ('I', 'B')
691 and B.LANGUAGE = userenv('LANG')
692 and not exists
693 (select NULL
694 from OKC_BUS_VARIABLES_TL T
695 where T.VARIABLE_CODE = B.VARIABLE_CODE
696 and T.LANGUAGE = L.LANGUAGE_CODE);
697 end ADD_LANGUAGE;
698
699
700
701
702
703 END OKC_BUSINESS_VARIABLES_PVT;
704