1 package body AHL_OSP_ORDERS_PKG as
2 /* $Header: AHLLOSOB.pls 120.2 2008/01/30 22:23:55 jaramana ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_OSP_ORDER_ID in NUMBER,
6 X_VENDOR_CONTACT_ID in NUMBER,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_OSP_ORDER_NUMBER in NUMBER,
9 X_ORDER_TYPE_CODE in VARCHAR2,
10 X_STATUS_CODE in VARCHAR2,
11 X_SINGLE_INSTANCE_FLAG in VARCHAR2,
12 X_ORDER_DATE in DATE,
13 X_OPERATING_UNIT_ID in NUMBER,
14 X_VENDOR_ID in NUMBER,
15 X_VENDOR_SITE_ID in NUMBER,
16 X_CUSTOMER_ID in NUMBER,
17 X_CONTRACT_ID in NUMBER,
18 X_CONTRACT_TERMS in VARCHAR2,
19 X_PO_HEADER_ID in NUMBER,
20 X_PO_SYNCH_FLAG in VARCHAR2,
21 X_PO_BATCH_ID in NUMBER,
22 X_PO_REQUEST_ID in NUMBER,
23 X_PO_INTERFACE_HEADER_ID in NUMBER,
24 X_PO_REQ_HEADER_ID in NUMBER, -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
25 X_PO_AGENT_ID in NUMBER,
26 X_OE_HEADER_ID in NUMBER,
27 X_ATTRIBUTE_CATEGORY in VARCHAR2,
28 X_ATTRIBUTE1 in VARCHAR2,
29 X_ATTRIBUTE2 in VARCHAR2,
30 X_ATTRIBUTE3 in VARCHAR2,
31 X_ATTRIBUTE4 in VARCHAR2,
32 X_ATTRIBUTE5 in VARCHAR2,
33 X_ATTRIBUTE6 in VARCHAR2,
34 X_ATTRIBUTE7 in VARCHAR2,
35 X_ATTRIBUTE8 in VARCHAR2,
36 X_ATTRIBUTE9 in VARCHAR2,
37 X_ATTRIBUTE10 in VARCHAR2,
38 X_ATTRIBUTE11 in VARCHAR2,
39 X_ATTRIBUTE12 in VARCHAR2,
40 X_ATTRIBUTE13 in VARCHAR2,
41 X_ATTRIBUTE14 in VARCHAR2,
42 X_ATTRIBUTE15 in VARCHAR2,
43 X_DESCRIPTION in VARCHAR2,
44 X_CREATION_DATE in DATE,
45 X_CREATED_BY in NUMBER,
46 X_LAST_UPDATE_DATE in DATE,
47 X_LAST_UPDATED_BY in NUMBER,
48 X_LAST_UPDATE_LOGIN in NUMBER
49 ) is
50 cursor C is select ROWID from AHL_OSP_ORDERS_B
51 where OSP_ORDER_ID = X_OSP_ORDER_ID
52 ;
53 begin
54 insert into AHL_OSP_ORDERS_B (
55 VENDOR_CONTACT_ID,
56 OSP_ORDER_ID,
57 OBJECT_VERSION_NUMBER,
58 OSP_ORDER_NUMBER,
59 ORDER_TYPE_CODE,
60 STATUS_CODE,
61 SINGLE_INSTANCE_FLAG,
62 ORDER_DATE,
63 OPERATING_UNIT_ID,
64 VENDOR_ID,
65 VENDOR_SITE_ID,
66 CUSTOMER_ID,
67 CONTRACT_ID,
68 CONTRACT_TERMS,
69 PO_HEADER_ID,
70 PO_SYNCH_FLAG,
71 PO_BATCH_ID,
72 PO_REQUEST_ID,
73 PO_INTERFACE_HEADER_ID,
74 PO_REQ_HEADER_ID, -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
75 PO_AGENT_ID,
76 OE_HEADER_ID,
77 ATTRIBUTE_CATEGORY,
78 ATTRIBUTE1,
79 ATTRIBUTE2,
80 ATTRIBUTE3,
81 ATTRIBUTE4,
82 ATTRIBUTE5,
83 ATTRIBUTE6,
84 ATTRIBUTE7,
85 ATTRIBUTE8,
86 ATTRIBUTE9,
87 ATTRIBUTE10,
88 ATTRIBUTE11,
89 ATTRIBUTE12,
90 ATTRIBUTE13,
91 ATTRIBUTE14,
92 ATTRIBUTE15,
93 CREATION_DATE,
94 CREATED_BY,
95 LAST_UPDATE_DATE,
96 LAST_UPDATED_BY,
97 LAST_UPDATE_LOGIN
98 ) values (
99 X_VENDOR_CONTACT_ID,
100 X_OSP_ORDER_ID,
101 X_OBJECT_VERSION_NUMBER,
102 X_OSP_ORDER_NUMBER,
103 X_ORDER_TYPE_CODE,
104 X_STATUS_CODE,
105 X_SINGLE_INSTANCE_FLAG,
106 X_ORDER_DATE,
107 X_OPERATING_UNIT_ID,
108 X_VENDOR_ID,
109 X_VENDOR_SITE_ID,
110 X_CUSTOMER_ID,
111 X_CONTRACT_ID,
112 X_CONTRACT_TERMS,
113 X_PO_HEADER_ID,
114 X_PO_SYNCH_FLAG,
115 X_PO_BATCH_ID,
116 X_PO_REQUEST_ID,
117 X_PO_INTERFACE_HEADER_ID,
118 X_PO_REQ_HEADER_ID, -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
119 X_PO_AGENT_ID,
120 X_OE_HEADER_ID,
121 X_ATTRIBUTE_CATEGORY,
122 X_ATTRIBUTE1,
123 X_ATTRIBUTE2,
124 X_ATTRIBUTE3,
125 X_ATTRIBUTE4,
126 X_ATTRIBUTE5,
127 X_ATTRIBUTE6,
128 X_ATTRIBUTE7,
129 X_ATTRIBUTE8,
130 X_ATTRIBUTE9,
131 X_ATTRIBUTE10,
132 X_ATTRIBUTE11,
133 X_ATTRIBUTE12,
134 X_ATTRIBUTE13,
135 X_ATTRIBUTE14,
136 X_ATTRIBUTE15,
137 X_CREATION_DATE,
138 X_CREATED_BY,
139 X_LAST_UPDATE_DATE,
140 X_LAST_UPDATED_BY,
141 X_LAST_UPDATE_LOGIN
142 );
143
144 insert into AHL_OSP_ORDERS_TL (
145 OSP_ORDER_ID,
146 LAST_UPDATE_DATE,
147 LAST_UPDATED_BY,
148 CREATION_DATE,
149 CREATED_BY,
150 LAST_UPDATE_LOGIN,
151 DESCRIPTION,
152 LANGUAGE,
153 SOURCE_LANG
154 ) select
155 X_OSP_ORDER_ID,
156 X_LAST_UPDATE_DATE,
157 X_LAST_UPDATED_BY,
158 X_CREATION_DATE,
159 X_CREATED_BY,
160 X_LAST_UPDATE_LOGIN,
161 X_DESCRIPTION,
162 L.LANGUAGE_CODE,
163 userenv('LANG')
164 from FND_LANGUAGES L
165 where L.INSTALLED_FLAG in ('I', 'B')
166 and not exists
167 (select NULL
168 from AHL_OSP_ORDERS_TL T
169 where T.OSP_ORDER_ID = X_OSP_ORDER_ID
170 and T.LANGUAGE = L.LANGUAGE_CODE);
171
172 open c;
173 fetch c into X_ROWID;
174 if (c%notfound) then
175 close c;
176 raise no_data_found;
177 end if;
178 close c;
179
180 end INSERT_ROW;
181
182 procedure LOCK_ROW (
183 X_OSP_ORDER_ID in NUMBER,
184 X_VENDOR_CONTACT_ID in NUMBER,
185 X_OBJECT_VERSION_NUMBER in NUMBER,
186 X_OSP_ORDER_NUMBER in NUMBER,
187 X_ORDER_TYPE_CODE in VARCHAR2,
188 X_STATUS_CODE in VARCHAR2,
189 X_SINGLE_INSTANCE_FLAG in VARCHAR2,
190 X_ORDER_DATE in DATE,
191 X_OPERATING_UNIT_ID in NUMBER,
192 X_VENDOR_ID in NUMBER,
193 X_VENDOR_SITE_ID in NUMBER,
194 X_CUSTOMER_ID in NUMBER,
195 X_CONTRACT_ID in NUMBER,
196 X_CONTRACT_TERMS in VARCHAR2,
197 X_PO_HEADER_ID in NUMBER,
198 X_PO_SYNCH_FLAG in VARCHAR2,
199 X_PO_BATCH_ID in NUMBER,
200 X_PO_REQUEST_ID in NUMBER,
201 X_PO_INTERFACE_HEADER_ID in NUMBER,
202 X_PO_REQ_HEADER_ID in NUMBER, -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
203 X_PO_AGENT_ID in NUMBER,
204 X_OE_HEADER_ID in NUMBER,
205 X_ATTRIBUTE_CATEGORY in VARCHAR2,
206 X_ATTRIBUTE1 in VARCHAR2,
207 X_ATTRIBUTE2 in VARCHAR2,
208 X_ATTRIBUTE3 in VARCHAR2,
209 X_ATTRIBUTE4 in VARCHAR2,
210 X_ATTRIBUTE5 in VARCHAR2,
211 X_ATTRIBUTE6 in VARCHAR2,
212 X_ATTRIBUTE7 in VARCHAR2,
213 X_ATTRIBUTE8 in VARCHAR2,
214 X_ATTRIBUTE9 in VARCHAR2,
215 X_ATTRIBUTE10 in VARCHAR2,
216 X_ATTRIBUTE11 in VARCHAR2,
217 X_ATTRIBUTE12 in VARCHAR2,
218 X_ATTRIBUTE13 in VARCHAR2,
219 X_ATTRIBUTE14 in VARCHAR2,
220 X_ATTRIBUTE15 in VARCHAR2,
221 X_DESCRIPTION in VARCHAR2
222 ) is
223 cursor c is select
224 VENDOR_CONTACT_ID,
225 OBJECT_VERSION_NUMBER,
226 OSP_ORDER_NUMBER,
227 ORDER_TYPE_CODE,
228 STATUS_CODE,
229 SINGLE_INSTANCE_FLAG,
230 ORDER_DATE,
231 OPERATING_UNIT_ID,
232 VENDOR_ID,
233 VENDOR_SITE_ID,
234 CUSTOMER_ID,
235 CONTRACT_ID,
236 CONTRACT_TERMS,
237 PO_HEADER_ID,
238 PO_SYNCH_FLAG,
239 PO_BATCH_ID,
240 PO_REQUEST_ID,
241 PO_INTERFACE_HEADER_ID,
242 PO_REQ_HEADER_ID, -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
243 PO_AGENT_ID,
244 OE_HEADER_ID,
245 ATTRIBUTE_CATEGORY,
246 ATTRIBUTE1,
247 ATTRIBUTE2,
248 ATTRIBUTE3,
249 ATTRIBUTE4,
250 ATTRIBUTE5,
251 ATTRIBUTE6,
252 ATTRIBUTE7,
253 ATTRIBUTE8,
254 ATTRIBUTE9,
255 ATTRIBUTE10,
256 ATTRIBUTE11,
257 ATTRIBUTE12,
258 ATTRIBUTE13,
259 ATTRIBUTE14,
260 ATTRIBUTE15
261 from AHL_OSP_ORDERS_B
262 where OSP_ORDER_ID = X_OSP_ORDER_ID
263 for update of OSP_ORDER_ID nowait;
264 recinfo c%rowtype;
265
266 cursor c1 is select
267 DESCRIPTION,
268 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
269 from AHL_OSP_ORDERS_TL
270 where OSP_ORDER_ID = X_OSP_ORDER_ID
271 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
272 for update of OSP_ORDER_ID nowait;
273 begin
274 open c;
275 fetch c into recinfo;
276 if (c%notfound) then
277 close c;
278 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
279 app_exception.raise_exception;
280 end if;
281 close c;
282 if ( ((recinfo.VENDOR_CONTACT_ID = X_VENDOR_CONTACT_ID)
283 OR ((recinfo.VENDOR_CONTACT_ID is null) AND (X_VENDOR_CONTACT_ID is null)))
284 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
285 AND (recinfo.OSP_ORDER_NUMBER = X_OSP_ORDER_NUMBER)
286 AND (recinfo.ORDER_TYPE_CODE = X_ORDER_TYPE_CODE)
287 AND (recinfo.STATUS_CODE = X_STATUS_CODE)
288 AND (recinfo.SINGLE_INSTANCE_FLAG = X_SINGLE_INSTANCE_FLAG)
289 AND (recinfo.ORDER_DATE = X_ORDER_DATE)
290 AND (recinfo.OPERATING_UNIT_ID = X_OPERATING_UNIT_ID)
291 AND ((recinfo.VENDOR_ID = X_VENDOR_ID)
292 OR ((recinfo.VENDOR_ID is null) AND (X_VENDOR_ID is null)))
293 AND ((recinfo.VENDOR_SITE_ID = X_VENDOR_SITE_ID)
294 OR ((recinfo.VENDOR_SITE_ID is null) AND (X_VENDOR_SITE_ID is null)))
295 AND ((recinfo.CUSTOMER_ID = X_CUSTOMER_ID)
296 OR ((recinfo.CUSTOMER_ID is null) AND (X_CUSTOMER_ID is null)))
297 AND ((recinfo.CONTRACT_ID = X_CONTRACT_ID)
298 OR ((recinfo.CONTRACT_ID is null) AND (X_CONTRACT_ID is null)))
299 AND ((recinfo.CONTRACT_TERMS = X_CONTRACT_TERMS)
300 OR ((recinfo.CONTRACT_TERMS is null) AND (X_CONTRACT_TERMS is null)))
301 AND ((recinfo.PO_HEADER_ID = X_PO_HEADER_ID)
302 OR ((recinfo.PO_HEADER_ID is null) AND (X_PO_HEADER_ID is null)))
303 AND ((recinfo.PO_SYNCH_FLAG = X_PO_SYNCH_FLAG)
304 OR ((recinfo.PO_SYNCH_FLAG is null) AND (X_PO_SYNCH_FLAG is null)))
305 AND ((recinfo.PO_BATCH_ID = X_PO_BATCH_ID)
306 OR ((recinfo.PO_BATCH_ID is null) AND (X_PO_BATCH_ID is null)))
307 AND ((recinfo.PO_REQUEST_ID = X_PO_REQUEST_ID)
308 OR ((recinfo.PO_REQUEST_ID is null) AND (X_PO_REQUEST_ID is null)))
309 AND ((recinfo.PO_INTERFACE_HEADER_ID = X_PO_INTERFACE_HEADER_ID)
310 OR ((recinfo.PO_INTERFACE_HEADER_ID is null) AND (X_PO_INTERFACE_HEADER_ID is null)))
311 AND ((recinfo.PO_REQ_HEADER_ID = X_PO_REQ_HEADER_ID)
312 OR ((recinfo.PO_REQ_HEADER_ID is null) AND (X_PO_REQ_HEADER_ID is null)))
313 AND ((recinfo.PO_AGENT_ID = X_PO_AGENT_ID)
314 OR ((recinfo.PO_AGENT_ID is null) AND (X_PO_AGENT_ID is null)))
315 AND ((recinfo.OE_HEADER_ID = X_OE_HEADER_ID)
316 OR ((recinfo.OE_HEADER_ID is null) AND (X_OE_HEADER_ID is null)))
317 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
318 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
319 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
320 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
321 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
322 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
323 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
324 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
325 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
326 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
327 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
328 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
332 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
329 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
330 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
331 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
333 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
334 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
335 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
336 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
337 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
338 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
339 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
340 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
341 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
342 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
343 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
344 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
345 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
346 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
347 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
348 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
349 ) then
350 null;
351 else
352 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
353 app_exception.raise_exception;
354 end if;
355
356 for tlinfo in c1 loop
357 if (tlinfo.BASELANG = 'Y') then
358 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
359 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
360 ) then
361 null;
362 else
363 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
364 app_exception.raise_exception;
365 end if;
369 end LOCK_ROW;
366 end if;
367 end loop;
368 return;
370
371 procedure UPDATE_ROW (
372 X_OSP_ORDER_ID in NUMBER,
373 X_VENDOR_CONTACT_ID in NUMBER,
374 X_OBJECT_VERSION_NUMBER in NUMBER,
375 X_OSP_ORDER_NUMBER in NUMBER,
376 X_ORDER_TYPE_CODE in VARCHAR2,
377 X_STATUS_CODE in VARCHAR2,
378 X_SINGLE_INSTANCE_FLAG in VARCHAR2,
379 X_ORDER_DATE in DATE,
380 X_OPERATING_UNIT_ID in NUMBER,
381 X_VENDOR_ID in NUMBER,
382 X_VENDOR_SITE_ID in NUMBER,
383 X_CUSTOMER_ID in NUMBER,
384 X_CONTRACT_ID in NUMBER,
385 X_CONTRACT_TERMS in VARCHAR2,
386 X_PO_HEADER_ID in NUMBER,
387 X_PO_SYNCH_FLAG in VARCHAR2,
388 X_PO_BATCH_ID in NUMBER,
389 X_PO_REQUEST_ID in NUMBER,
390 X_PO_INTERFACE_HEADER_ID in NUMBER,
391 X_PO_REQ_HEADER_ID in NUMBER, -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
392 X_PO_AGENT_ID in NUMBER,
393 X_OE_HEADER_ID in NUMBER,
394 X_ATTRIBUTE_CATEGORY in VARCHAR2,
395 X_ATTRIBUTE1 in VARCHAR2,
396 X_ATTRIBUTE2 in VARCHAR2,
397 X_ATTRIBUTE3 in VARCHAR2,
398 X_ATTRIBUTE4 in VARCHAR2,
399 X_ATTRIBUTE5 in VARCHAR2,
400 X_ATTRIBUTE6 in VARCHAR2,
401 X_ATTRIBUTE7 in VARCHAR2,
402 X_ATTRIBUTE8 in VARCHAR2,
403 X_ATTRIBUTE9 in VARCHAR2,
404 X_ATTRIBUTE10 in VARCHAR2,
405 X_ATTRIBUTE11 in VARCHAR2,
406 X_ATTRIBUTE12 in VARCHAR2,
407 X_ATTRIBUTE13 in VARCHAR2,
408 X_ATTRIBUTE14 in VARCHAR2,
409 X_ATTRIBUTE15 in VARCHAR2,
410 X_DESCRIPTION in VARCHAR2,
411 X_LAST_UPDATE_DATE in DATE,
412 X_LAST_UPDATED_BY in NUMBER,
413 X_LAST_UPDATE_LOGIN in NUMBER
414 ) is
415 begin
416 update AHL_OSP_ORDERS_B set
417 VENDOR_CONTACT_ID = X_VENDOR_CONTACT_ID,
418 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
419 OSP_ORDER_NUMBER = X_OSP_ORDER_NUMBER,
420 ORDER_TYPE_CODE = X_ORDER_TYPE_CODE,
421 STATUS_CODE = X_STATUS_CODE,
425 VENDOR_ID = X_VENDOR_ID,
422 SINGLE_INSTANCE_FLAG = X_SINGLE_INSTANCE_FLAG,
423 ORDER_DATE = X_ORDER_DATE,
424 OPERATING_UNIT_ID = X_OPERATING_UNIT_ID,
426 VENDOR_SITE_ID = X_VENDOR_SITE_ID,
427 CUSTOMER_ID = X_CUSTOMER_ID,
428 CONTRACT_ID = X_CONTRACT_ID,
429 CONTRACT_TERMS = X_CONTRACT_TERMS,
430 PO_HEADER_ID = X_PO_HEADER_ID,
431 PO_SYNCH_FLAG = X_PO_SYNCH_FLAG,
432 PO_BATCH_ID = X_PO_BATCH_ID,
433 PO_REQUEST_ID = X_PO_REQUEST_ID,
434 PO_INTERFACE_HEADER_ID = X_PO_INTERFACE_HEADER_ID,
435 PO_REQ_HEADER_ID = X_PO_REQ_HEADER_ID, -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
436 PO_AGENT_ID = X_PO_AGENT_ID,
437 OE_HEADER_ID = X_OE_HEADER_ID,
438 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
439 ATTRIBUTE1 = X_ATTRIBUTE1,
440 ATTRIBUTE2 = X_ATTRIBUTE2,
441 ATTRIBUTE3 = X_ATTRIBUTE3,
442 ATTRIBUTE4 = X_ATTRIBUTE4,
443 ATTRIBUTE5 = X_ATTRIBUTE5,
444 ATTRIBUTE6 = X_ATTRIBUTE6,
445 ATTRIBUTE7 = X_ATTRIBUTE7,
446 ATTRIBUTE8 = X_ATTRIBUTE8,
447 ATTRIBUTE9 = X_ATTRIBUTE9,
448 ATTRIBUTE10 = X_ATTRIBUTE10,
449 ATTRIBUTE11 = X_ATTRIBUTE11,
450 ATTRIBUTE12 = X_ATTRIBUTE12,
451 ATTRIBUTE13 = X_ATTRIBUTE13,
452 ATTRIBUTE14 = X_ATTRIBUTE14,
453 ATTRIBUTE15 = X_ATTRIBUTE15,
454 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
455 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
456 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
457 where OSP_ORDER_ID = X_OSP_ORDER_ID;
458
459 if (sql%notfound) then
460 raise no_data_found;
461 end if;
462
463 update AHL_OSP_ORDERS_TL set
464 DESCRIPTION = X_DESCRIPTION,
465 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
466 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
467 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
468 SOURCE_LANG = userenv('LANG')
469 where OSP_ORDER_ID = X_OSP_ORDER_ID
470 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
471
472 if (sql%notfound) then
473 raise no_data_found;
474 end if;
475 end UPDATE_ROW;
476
477 procedure DELETE_ROW (
478 X_OSP_ORDER_ID in NUMBER
479 ) is
480 begin
481 delete from AHL_OSP_ORDERS_TL
482 where OSP_ORDER_ID = X_OSP_ORDER_ID;
483
484 if (sql%notfound) then
485 raise no_data_found;
486 end if;
487
488 delete from AHL_OSP_ORDERS_B
489 where OSP_ORDER_ID = X_OSP_ORDER_ID;
490
491 if (sql%notfound) then
492 raise no_data_found;
493 end if;
494 end DELETE_ROW;
495
496 procedure ADD_LANGUAGE
497 is
498 begin
499 delete from AHL_OSP_ORDERS_TL T
500 where not exists
501 (select NULL
502 from AHL_OSP_ORDERS_B B
503 where B.OSP_ORDER_ID = T.OSP_ORDER_ID
504 );
505
506 update AHL_OSP_ORDERS_TL T set (
507 DESCRIPTION
508 ) = (select
509 B.DESCRIPTION
510 from AHL_OSP_ORDERS_TL B
511 where B.OSP_ORDER_ID = T.OSP_ORDER_ID
512 and B.LANGUAGE = T.SOURCE_LANG)
513 where (
514 T.OSP_ORDER_ID,
515 T.LANGUAGE
516 ) in (select
517 SUBT.OSP_ORDER_ID,
518 SUBT.LANGUAGE
519 from AHL_OSP_ORDERS_TL SUBB, AHL_OSP_ORDERS_TL SUBT
520 where SUBB.OSP_ORDER_ID = SUBT.OSP_ORDER_ID
521 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
522 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
523 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
524 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
525 ));
526
527 insert into AHL_OSP_ORDERS_TL (
528 OSP_ORDER_ID,
529 LAST_UPDATE_DATE,
530 LAST_UPDATED_BY,
531 CREATION_DATE,
532 CREATED_BY,
533 LAST_UPDATE_LOGIN,
534 DESCRIPTION,
535 LANGUAGE,
536 SOURCE_LANG
537 ) select /*+ ORDERED */
538 B.OSP_ORDER_ID,
539 B.LAST_UPDATE_DATE,
540 B.LAST_UPDATED_BY,
541 B.CREATION_DATE,
542 B.CREATED_BY,
543 B.LAST_UPDATE_LOGIN,
544 B.DESCRIPTION,
545 L.LANGUAGE_CODE,
546 B.SOURCE_LANG
547 from AHL_OSP_ORDERS_TL B, FND_LANGUAGES L
548 where L.INSTALLED_FLAG in ('I', 'B')
549 and B.LANGUAGE = userenv('LANG')
550 and not exists
551 (select NULL
552 from AHL_OSP_ORDERS_TL T
553 where T.OSP_ORDER_ID = B.OSP_ORDER_ID
554 and T.LANGUAGE = L.LANGUAGE_CODE);
555 end ADD_LANGUAGE;
556
557 end AHL_OSP_ORDERS_PKG;
[Home] [Help]