[Home] [Help]
PACKAGE BODY: APPS.OZF_REASON_CODES_ALL_PKG
Source
1 PACKAGE BODY OZF_REASON_CODES_ALL_PKG as
2 /* $Header: ozftreab.pls 120.2 2005/07/08 07:07:05 appldev ship $ */
3 -- Start of Comments
4 -- Package name : OZF_REASON_CODES_ALL_PKG
5 -- Purpose :
6 -- History : 30-AUG-2001 MCHANG Add one more column: REASON_TYPE
7 -- 15-jul-2002 upoluri Sequnce generation check.
8 -- History : 28-SEP-2003 ANUJGUPT Add one more column: PARTNER_ACCESS_FLAG VARCHAR2(1)
9 -- History : 28-SEP-2003 ANUJGUPT Add one more column: PARTNER_ACCESS_FLAG VARCHAR2(1)
10 -- History : 22-Jun-2005 KDHULIPA Add one more column: INVOICING_REASON_CODE VARCHAR2(30)
11 -- NOTE :
12 -- End of Comments
13
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_REASON_CODES_ALL_PKG';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftreab.pls';
17
18 PROCEDURE Insert_Row(
19 px_REASON_CODE_ID IN OUT NOCOPY NUMBER,
20 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
21 p_LAST_UPDATE_DATE DATE,
22 p_LAST_UPDATED_BY NUMBER,
23 p_CREATION_DATE DATE,
24 p_CREATED_BY NUMBER,
25 p_LAST_UPDATE_LOGIN NUMBER,
26 p_REASON_CODE VARCHAR2,
27 p_START_DATE_ACTIVE DATE,
28 p_END_DATE_ACTIVE DATE,
29 p_ATTRIBUTE_CATEGORY VARCHAR2,
30 p_ATTRIBUTE1 VARCHAR2,
31 p_ATTRIBUTE2 VARCHAR2,
32 p_ATTRIBUTE3 VARCHAR2,
33 p_ATTRIBUTE4 VARCHAR2,
34 p_ATTRIBUTE5 VARCHAR2,
35 p_ATTRIBUTE6 VARCHAR2,
36 p_ATTRIBUTE7 VARCHAR2,
37 p_ATTRIBUTE8 VARCHAR2,
38 p_ATTRIBUTE9 VARCHAR2,
39 p_ATTRIBUTE10 VARCHAR2,
40 p_ATTRIBUTE11 VARCHAR2,
41 p_ATTRIBUTE12 VARCHAR2,
42 p_ATTRIBUTE13 VARCHAR2,
43 p_ATTRIBUTE14 VARCHAR2,
44 p_ATTRIBUTE15 VARCHAR2,
45 p_NAME VARCHAR2,
46 p_DESCRIPTION VARCHAR2,
47 px_ORG_ID IN OUT NOCOPY NUMBER,
48 P_REASON_TYPE VARCHAR2,
49 p_ADJUSTMENT_REASON_CODE VARCHAR2,
50 p_INVOICING_REASON_CODE VARCHAR2,
51 px_ORDER_TYPE_ID NUMBER,
52 p_PARTNER_ACCESS_FLAG VARCHAR2)
53
54 IS
55 X_ROWID VARCHAR2(30);
56 l_reason_count NUMBER;
57
58 CURSOR C IS SELECT rowid FROM OZF_REASON_CODES_ALL_B
59 WHERE REASON_CODE_ID = px_REASON_CODE_ID;
60 CURSOR C2 IS SELECT OZF_REASON_CODES_ALL_B_S.nextval FROM sys.dual;
61 CURSOR C_REASON_COUNT(l_reason_code_id NUMBER) IS
62 SELECT COUNT(REASON_CODE_ID)
63 FROM OZF_REASON_CODES_ALL_B
64 WHERE REASON_CODE_ID = l_reason_code_id;
65
66 BEGIN
67 /* IF (px_ORG_ID IS NULL OR px_ORG_ID = FND_API.G_MISS_NUM) THEN
68 select nvl(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99)
69 into px_ORG_ID
70 from dual;
71 END IF; */
72
73 IF (px_REASON_CODE_ID IS NULL) THEN
74 LOOP
75 OPEN C2;
76 FETCH C2 INTO px_REASON_CODE_ID;
77 CLOSE C2;
78
79 OPEN C_REASON_COUNT(px_REASON_CODE_ID);
80 FETCH C_REASON_COUNT INTO l_reason_count;
81 CLOSE C_REASON_COUNT;
82 EXIT WHEN l_reason_count = 0;
83 END LOOP;
84 END IF;
85
86 IF (px_OBJECT_VERSION_NUMBER IS NULL OR
87 px_OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM) THEN
88 px_OBJECT_VERSION_NUMBER := 1;
89 END IF;
90
91 INSERT INTO OZF_REASON_CODES_ALL_B(
92 REASON_CODE_ID,
93 OBJECT_VERSION_NUMBER,
94 LAST_UPDATE_DATE,
95 LAST_UPDATED_BY,
96 CREATION_DATE,
97 CREATED_BY,
98 LAST_UPDATE_LOGIN,
99 REASON_CODE,
100 START_DATE_ACTIVE,
101 END_DATE_ACTIVE,
102 ATTRIBUTE_CATEGORY,
103 ATTRIBUTE1,
104 ATTRIBUTE2,
105 ATTRIBUTE3,
106 ATTRIBUTE4,
107 ATTRIBUTE5,
108 ATTRIBUTE6,
109 ATTRIBUTE7,
110 ATTRIBUTE8,
111 ATTRIBUTE9,
112 ATTRIBUTE10,
113 ATTRIBUTE11,
114 ATTRIBUTE12,
115 ATTRIBUTE13,
116 ATTRIBUTE14,
117 ATTRIBUTE15,
118 ORG_ID,
119 REASON_TYPE,
120 ADJUSTMENT_REASON_CODE,
121 INVOICING_REASON_CODE,
122 ORDER_TYPE_ID,
123 PARTNER_ACCESS_FLAG
124 ) VALUES (
125 px_REASON_CODE_ID,
126 px_OBJECT_VERSION_NUMBER,
127 p_LAST_UPDATE_DATE,
128 p_LAST_UPDATED_BY,
129 p_CREATION_DATE,
130 p_CREATED_BY,
131 p_LAST_UPDATE_LOGIN,
132 p_REASON_CODE,
133 p_START_DATE_ACTIVE,
134 p_END_DATE_ACTIVE,
135 p_ATTRIBUTE_CATEGORY,
136 p_ATTRIBUTE1,
137 p_ATTRIBUTE2,
138 p_ATTRIBUTE3,
139 p_ATTRIBUTE4,
140 p_ATTRIBUTE5,
141 p_ATTRIBUTE6,
142 p_ATTRIBUTE7,
143 p_ATTRIBUTE8,
144 p_ATTRIBUTE9,
145 p_ATTRIBUTE10,
146 p_ATTRIBUTE11,
147 p_ATTRIBUTE12,
148 p_ATTRIBUTE13,
149 p_ATTRIBUTE14,
150 p_ATTRIBUTE15,
151 px_ORG_ID,
152 p_REASON_TYPE,
153 p_ADJUSTMENT_REASON_CODE,
154 p_INVOICING_REASON_CODE,
155 px_ORDER_TYPE_ID,
156 p_PARTNER_ACCESS_FLAG );
157
158 INSERT INTO OZF_REASON_CODES_ALL_TL (
159 REASON_CODE_ID,
160 LAST_UPDATE_DATE,
161 LAST_UPDATED_BY,
162 CREATION_DATE,
163 CREATED_BY,
164 LAST_UPDATE_LOGIN,
165 NAME,
166 DESCRIPTION,
167 ORG_ID,
168 LANGUAGE,
169 SOURCE_LANG
170 ) select
171 px_REASON_CODE_ID,
172 p_LAST_UPDATE_DATE,
173 p_LAST_UPDATED_BY,
174 p_CREATION_DATE,
175 p_CREATED_BY,
176 p_LAST_UPDATE_LOGIN,
177 p_NAME,
178 p_DESCRIPTION,
179 px_ORG_ID,
180 L.LANGUAGE_CODE,
181 userenv('LANG')
182 from FND_LANGUAGES L
183 where L.INSTALLED_FLAG in ('I', 'B')
184 and not exists
185 (select NULL
186 from OZF_REASON_CODES_ALL_TL T
187 where T.REASON_CODE_ID = px_REASON_CODE_ID
188 and T.LANGUAGE = L.LANGUAGE_CODE);
189
190 OPEN C;
191 FETCH C INTO x_rowid;
192 If (C%NOTFOUND) then
193 CLOSE C;
194 RAISE NO_DATA_FOUND;
195 End If;
196 End Insert_Row;
197
198 PROCEDURE Update_Row(
199 p_REASON_CODE_ID NUMBER,
200 p_OBJECT_VERSION_NUMBER NUMBER,
201 p_LAST_UPDATE_DATE DATE,
202 p_LAST_UPDATED_BY NUMBER,
203 -- p_CREATION_DATE DATE,
204 -- p_CREATED_BY NUMBER,
205 p_LAST_UPDATE_LOGIN NUMBER,
206 p_REASON_CODE VARCHAR2,
207 p_START_DATE_ACTIVE DATE,
208 p_END_DATE_ACTIVE DATE,
209 p_ATTRIBUTE_CATEGORY VARCHAR2,
210 p_ATTRIBUTE1 VARCHAR2,
211 p_ATTRIBUTE2 VARCHAR2,
212 p_ATTRIBUTE3 VARCHAR2,
213 p_ATTRIBUTE4 VARCHAR2,
214 p_ATTRIBUTE5 VARCHAR2,
215 p_ATTRIBUTE6 VARCHAR2,
216 p_ATTRIBUTE7 VARCHAR2,
217 p_ATTRIBUTE8 VARCHAR2,
218 p_ATTRIBUTE9 VARCHAR2,
219 p_ATTRIBUTE10 VARCHAR2,
220 p_ATTRIBUTE11 VARCHAR2,
221 p_ATTRIBUTE12 VARCHAR2,
222 p_ATTRIBUTE13 VARCHAR2,
223 p_ATTRIBUTE14 VARCHAR2,
224 p_ATTRIBUTE15 VARCHAR2,
225 p_NAME VARCHAR2,
226 p_DESCRIPTION VARCHAR2,
227 p_ORG_ID NUMBER,
228 P_REASON_TYPE VARCHAR2,
229 p_ADJUSTMENT_REASON_CODE VARCHAR2,
230 p_INVOICING_REASON_CODE VARCHAR2,
231 p_ORDER_TYPE_ID NUMBER,
232 p_PARTNER_ACCESS_FLAG VARCHAR2)
233
234 IS
235 BEGIN
236 Update OZF_REASON_CODES_ALL_B
237 SET
238 REASON_CODE_ID = p_REASON_CODE_ID,
239 OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER,
240 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
241 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
242 -- CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
243 -- CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
244 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
245 REASON_CODE = p_REASON_CODE,
246 START_DATE_ACTIVE = p_START_DATE_ACTIVE,
247 END_DATE_ACTIVE = p_END_DATE_ACTIVE,
248 ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY,
249 ATTRIBUTE1 = p_ATTRIBUTE1,
250 ATTRIBUTE2 = p_ATTRIBUTE2,
251 ATTRIBUTE3 = p_ATTRIBUTE3,
252 ATTRIBUTE4 = p_ATTRIBUTE4,
253 ATTRIBUTE5 = p_ATTRIBUTE5,
254 ATTRIBUTE6 = p_ATTRIBUTE6,
255 ATTRIBUTE7 = p_ATTRIBUTE7,
256 ATTRIBUTE8 = p_ATTRIBUTE8,
257 ATTRIBUTE9 = p_ATTRIBUTE9,
258 ATTRIBUTE10 = p_ATTRIBUTE10,
259 ATTRIBUTE11 = p_ATTRIBUTE11,
260 ATTRIBUTE12 = p_ATTRIBUTE12,
261 ATTRIBUTE13 = p_ATTRIBUTE13,
262 ATTRIBUTE14 = p_ATTRIBUTE14,
263 ATTRIBUTE15 = p_ATTRIBUTE15,
264 ORG_ID = p_ORG_ID,
265 REASON_TYPE = p_REASON_TYPE,
266 ADJUSTMENT_REASON_CODE = p_ADJUSTMENT_REASON_CODE,
267 INVOICING_REASON_CODE = p_INVOICING_REASON_CODE,
268 ORDER_TYPE_ID = p_ORDER_TYPE_ID,
269 PARTNER_ACCESS_FLAG = p_PARTNER_ACCESS_FLAG
270 where REASON_CODE_ID = p_REASON_CODE_ID;
271
272 If (SQL%NOTFOUND) then
273 RAISE NO_DATA_FOUND;
274 End If;
278 DESCRIPTION = p_DESCRIPTION,
275
276 update OZF_REASON_CODES_ALL_TL set
277 NAME = p_NAME,
279 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
280 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
281 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
282 SOURCE_LANG = userenv('LANG')
283 where REASON_CODE_ID = p_REASON_CODE_ID
284 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
285
286 if (sql%notfound) then
287 raise no_data_found;
288 end if;
289
290 END Update_Row;
291
292 PROCEDURE Delete_Row(
293 p_REASON_CODE_ID NUMBER)
294 IS
295 BEGIN
296 delete from OZF_REASON_CODES_ALL_TL
297 where REASON_CODE_ID = p_REASON_CODE_ID;
298
299 if (sql%notfound) then
300 raise no_data_found;
301 end if;
302
303 DELETE FROM OZF_REASON_CODES_ALL_B
304 WHERE REASON_CODE_ID = p_REASON_CODE_ID;
305
306 If (SQL%NOTFOUND) then
307 RAISE NO_DATA_FOUND;
308 End If;
309 END Delete_Row;
310
311 PROCEDURE Lock_Row(
312 p_REASON_CODE_ID NUMBER,
313 p_OBJECT_VERSION_NUMBER NUMBER,
314 p_LAST_UPDATE_DATE DATE,
315 p_LAST_UPDATED_BY NUMBER,
316 p_CREATION_DATE DATE,
317 p_CREATED_BY NUMBER,
318 p_LAST_UPDATE_LOGIN NUMBER,
319 p_REASON_CODE VARCHAR2,
320 p_START_DATE_ACTIVE DATE,
321 p_END_DATE_ACTIVE DATE,
322 p_ATTRIBUTE_CATEGORY VARCHAR2,
323 p_ATTRIBUTE1 VARCHAR2,
324 p_ATTRIBUTE2 VARCHAR2,
325 p_ATTRIBUTE3 VARCHAR2,
326 p_ATTRIBUTE4 VARCHAR2,
327 p_ATTRIBUTE5 VARCHAR2,
328 p_ATTRIBUTE6 VARCHAR2,
329 p_ATTRIBUTE7 VARCHAR2,
330 p_ATTRIBUTE8 VARCHAR2,
331 p_ATTRIBUTE9 VARCHAR2,
332 p_ATTRIBUTE10 VARCHAR2,
333 p_ATTRIBUTE11 VARCHAR2,
334 p_ATTRIBUTE12 VARCHAR2,
335 p_ATTRIBUTE13 VARCHAR2,
336 p_ATTRIBUTE14 VARCHAR2,
337 p_ATTRIBUTE15 VARCHAR2,
338 p_NAME VARCHAR2,
339 p_DESCRIPTION VARCHAR2,
340 p_ORG_ID NUMBER,
341 P_REASON_TYPE VARCHAR2,
342 p_ADJUSTMENT_REASON_CODE VARCHAR2,
343 p_INVOICING_REASON_CODE VARCHAR2,
344 p_ORDER_TYPE_ID NUMBER,
345 p_PARTNER_ACCESS_FLAG VARCHAR2)
346
347 IS
348 CURSOR C IS
349 SELECT *
350 FROM OZF_REASON_CODES_ALL_B
351 WHERE REASON_CODE_ID = p_REASON_CODE_ID
352 FOR UPDATE of REASON_CODE_ID NOWAIT;
353 Recinfo C%ROWTYPE;
354
355 cursor c1 is select
356 NAME,
357 DESCRIPTION,
358 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
359 from OZF_REASON_CODES_ALL_TL
360 where REASON_CODE_ID = p_REASON_CODE_ID
361 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
362 for update of REASON_CODE_ID nowait;
363 BEGIN
364 OPEN C;
365 FETCH C INTO Recinfo;
366 If (C%NOTFOUND) then
367 CLOSE C;
368 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_RECORD_NOT_FOUND');
369 APP_EXCEPTION.RAISE_EXCEPTION;
370 End If;
371 CLOSE C;
372 if (
373 ( Recinfo.REASON_CODE_ID = p_REASON_CODE_ID)
374 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
375 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
376 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
377 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
378 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
379 AND ( p_LAST_UPDATE_DATE IS NULL )))
380 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
381 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
382 AND ( p_LAST_UPDATED_BY IS NULL )))
383 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
384 OR ( ( Recinfo.CREATION_DATE IS NULL )
385 AND ( p_CREATION_DATE IS NULL )))
386 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
387 OR ( ( Recinfo.CREATED_BY IS NULL )
388 AND ( p_CREATED_BY IS NULL )))
389 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
390 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
391 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
392 AND ( ( Recinfo.REASON_CODE = p_REASON_CODE)
393 OR ( ( Recinfo.REASON_CODE IS NULL )
394 AND ( p_REASON_CODE IS NULL )))
395 AND ( ( Recinfo.START_DATE_ACTIVE = p_START_DATE_ACTIVE)
396 OR ( ( Recinfo.START_DATE_ACTIVE IS NULL )
397 AND ( p_START_DATE_ACTIVE IS NULL )))
398 AND ( ( Recinfo.END_DATE_ACTIVE = p_END_DATE_ACTIVE)
399 OR ( ( Recinfo.END_DATE_ACTIVE IS NULL )
400 AND ( p_END_DATE_ACTIVE IS NULL )))
401 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
402 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
403 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
404 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
408 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
405 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
406 AND ( p_ATTRIBUTE1 IS NULL )))
407 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
409 AND ( p_ATTRIBUTE2 IS NULL )))
410 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
411 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
412 AND ( p_ATTRIBUTE3 IS NULL )))
413 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
414 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
415 AND ( p_ATTRIBUTE4 IS NULL )))
416 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
417 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
418 AND ( p_ATTRIBUTE5 IS NULL )))
419 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
420 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
421 AND ( p_ATTRIBUTE6 IS NULL )))
422 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
423 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
424 AND ( p_ATTRIBUTE7 IS NULL )))
425 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
426 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
427 AND ( p_ATTRIBUTE8 IS NULL )))
428 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
429 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
430 AND ( p_ATTRIBUTE9 IS NULL )))
431 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
432 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
433 AND ( p_ATTRIBUTE10 IS NULL )))
434 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
435 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
436 AND ( p_ATTRIBUTE11 IS NULL )))
437 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
438 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
439 AND ( p_ATTRIBUTE12 IS NULL )))
440 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
441 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
442 AND ( p_ATTRIBUTE13 IS NULL )))
443 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
444 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
445 AND ( p_ATTRIBUTE14 IS NULL )))
446 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
447 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
448 AND ( p_ATTRIBUTE15 IS NULL )))
449 AND ( ( Recinfo.ORG_ID = p_ORG_ID)
450 OR ( ( Recinfo.ORG_ID IS NULL )
451 AND ( p_ORG_ID IS NULL )))
452 AND ( ( Recinfo.REASON_TYPE = p_REASON_TYPE)
456 OR ( ( Recinfo.ADJUSTMENT_REASON_CODE IS NULL )
453 OR ( ( Recinfo.REASON_TYPE IS NULL )
454 AND ( p_REASON_TYPE IS NULL )))
455 AND ( ( Recinfo.ADJUSTMENT_REASON_CODE = p_ADJUSTMENT_REASON_CODE)
457 AND ( p_ADJUSTMENT_REASON_CODE IS NULL )))
458 AND ( ( Recinfo.INVOICING_REASON_CODE = p_INVOICING_REASON_CODE)
459 OR ( ( Recinfo.INVOICING_REASON_CODE IS NULL )
460 AND ( p_INVOICING_REASON_CODE IS NULL )))
461 AND ( ( Recinfo.ORDER_TYPE_ID = p_ORDER_TYPE_ID)
462 OR ( ( Recinfo.ORDER_TYPE_ID IS NULL )
463 AND ( p_ORDER_TYPE_ID IS NULL )))
464 AND ( ( Recinfo.PARTNER_ACCESS_FLAG = p_PARTNER_ACCESS_FLAG)
465 OR ( ( Recinfo.PARTNER_ACCESS_FLAG IS NULL )
466 AND ( p_PARTNER_ACCESS_FLAG IS NULL )))
467
468 ) then
469 null;
470 else
471 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_RECORD_NOT_FOUND');
472 APP_EXCEPTION.RAISE_EXCEPTION;
473 End If;
474
475 for tlinfo in c1 loop
476 if (tlinfo.BASELANG = 'Y') then
477 if ( (tlinfo.NAME = p_NAME)
478 AND ((tlinfo.DESCRIPTION = p_DESCRIPTION)
479 OR ((tlinfo.DESCRIPTION is null) AND (p_DESCRIPTION is null)))
480 ) then
481 null;
482 else
483 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
484 app_exception.raise_exception;
485 end if;
486 end if;
487 end loop;
488 return;
489
490 END Lock_Row;
491
492 procedure ADD_LANGUAGE
493 is
494 begin
495 delete from OZF_REASON_CODES_ALL_TL T
496 where not exists
497 (select NULL
498 from OZF_REASON_CODES_ALL_B B
499 where B.REASON_CODE_ID = T.REASON_CODE_ID
500 and NVL(B.ORG_ID,NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
501 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
502 NVL(T.ORG_ID, NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
503 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
504 );
505
506 update OZF_REASON_CODES_ALL_TL T set (
507 NAME,
508 DESCRIPTION
509 ) = (select
510 B.NAME,
511 B.DESCRIPTION
512 from OZF_REASON_CODES_ALL_TL B
513 where B.REASON_CODE_ID = T.REASON_CODE_ID
514 and B.LANGUAGE = T.SOURCE_LANG
515 and NVL(B.ORG_ID,NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
516 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
517 NVL(T.ORG_ID, NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
518 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) )
519 where (
520 T.REASON_CODE_ID,
521 T.LANGUAGE
522 ) in (select
523 SUBT.REASON_CODE_ID,
524 SUBT.LANGUAGE
525 from OZF_REASON_CODES_ALL_TL SUBB, OZF_REASON_CODES_ALL_TL SUBT
526 where SUBB.REASON_CODE_ID = SUBT.REASON_CODE_ID
527 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
528 and NVL(SUBB.ORG_ID,NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
529 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
530 NVL(SUBT.ORG_ID, NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
531 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
532 and (SUBB.NAME <> SUBT.NAME
533 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
534 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
535 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
536 ));
537
538 insert into OZF_REASON_CODES_ALL_TL (
539 ORG_ID,
540 REASON_CODE_ID,
541 LAST_UPDATE_DATE,
542 LAST_UPDATED_BY,
543 CREATION_DATE,
544 CREATED_BY,
545 LAST_UPDATE_LOGIN,
546 NAME,
547 DESCRIPTION,
548 LANGUAGE,
549 SOURCE_LANG
550 ) select
551 B.ORG_ID,
552 B.REASON_CODE_ID,
553 B.LAST_UPDATE_DATE,
554 B.LAST_UPDATED_BY,
555 B.CREATION_DATE,
556 B.CREATED_BY,
557 B.LAST_UPDATE_LOGIN,
558 B.NAME,
559 B.DESCRIPTION,
560 L.LANGUAGE_CODE,
561 B.SOURCE_LANG
562 from OZF_REASON_CODES_ALL_TL B, FND_LANGUAGES L
563 where L.INSTALLED_FLAG in ('I', 'B')
564 and B.LANGUAGE = userenv('LANG')
565 and not exists
566 (select NULL
567 from OZF_REASON_CODES_ALL_TL T
568 where T.REASON_CODE_ID = B.REASON_CODE_ID
569 and T.LANGUAGE = L.LANGUAGE_CODE
570 and NVL(T.ORG_ID,NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
571 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
572 NVL(B.ORG_ID, NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
573 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) );
574 end ADD_LANGUAGE;
575
576 procedure TRANSLATE_ROW (
577 X_REASON_CODE_ID in NUMBER,
578 X_NAME in VARCHAR2,
579 X_DESCRIPTION in VARCHAR2,
580 X_OWNER in VARCHAR2) IS
581 begin
582
583 -- note org_id is not used here because in NLS mode it is important
584 -- update a line id across all orgs because data will be translated
585 -- only once for a single org
586
587 update ozf_reason_codes_all_tl
588 set name = X_NAME,
589 description = X_DESCRIPTION,
590 source_lang = userenv('LANG'),
591 last_update_date = sysdate,
592 last_updated_by = decode(X_OWNER, 'SEED', -1, 0),
593 last_update_login = 0
594 where reason_code_id = X_REASON_CODE_ID
595 and userenv('LANG') in (language, source_lang);
596
597 end TRANSLATE_ROW;
598
599 End OZF_REASON_CODES_ALL_PKG;