1 package body OE_AGREEMENTS_PKG as
2 /* $Header: QPXAGGRB.pls 120.2 2005/12/14 16:24:43 shulin noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_AGREEMENT_ID in NUMBER,
6 X_TP_ATTRIBUTE2 in VARCHAR2,
7 X_TP_ATTRIBUTE3 in VARCHAR2,
8 X_TP_ATTRIBUTE4 in VARCHAR2,
9 X_TP_ATTRIBUTE5 in VARCHAR2,
10 X_TP_ATTRIBUTE6 in VARCHAR2,
11 X_TP_ATTRIBUTE7 in VARCHAR2,
12 X_TP_ATTRIBUTE8 in VARCHAR2,
13 X_TP_ATTRIBUTE9 in VARCHAR2,
14 X_TP_ATTRIBUTE10 in VARCHAR2,
15 X_TP_ATTRIBUTE11 in VARCHAR2,
16 X_TP_ATTRIBUTE12 in VARCHAR2,
17 X_TP_ATTRIBUTE13 in VARCHAR2,
18 X_TP_ATTRIBUTE14 in VARCHAR2,
19 X_TP_ATTRIBUTE15 in VARCHAR2,
20 X_TP_ATTRIBUTE_CATEGORY in VARCHAR2,
21 X_AGREEMENT_TYPE_CODE in VARCHAR2,
22 X_PRICE_LIST_ID in NUMBER,
23 X_TERM_ID in NUMBER,
24 X_OVERRIDE_IRULE_FLAG in VARCHAR2,
25 X_OVERRIDE_ARULE_FLAG in VARCHAR2,
26 X_SIGNATURE_DATE in DATE,
27 X_AGREEMENT_NUM in VARCHAR2,
28 X_TP_ATTRIBUTE1 in VARCHAR2,
29 X_ATTRIBUTE12 in VARCHAR2,
30 X_ATTRIBUTE13 in VARCHAR2,
31 X_ATTRIBUTE14 in VARCHAR2,
32 X_ATTRIBUTE15 in VARCHAR2,
33 X_ATTRIBUTE11 in VARCHAR2,
34 X_ATTRIBUTE9 in VARCHAR2,
35 X_ATTRIBUTE10 in VARCHAR2,
36 X_REVISION in VARCHAR2,
37 X_REVISION_DATE in DATE,
38 X_REVISION_REASON_CODE in VARCHAR2,
39 X_FREIGHT_TERMS_CODE in VARCHAR2,
40 X_SHIP_METHOD_CODE in VARCHAR2,
41 X_INVOICING_RULE_ID in NUMBER,
42 X_ACCOUNTING_RULE_ID in NUMBER,
43 X_SOLD_TO_ORG_ID in NUMBER,
44 X_PURCHASE_ORDER_NUM in VARCHAR2,
45 X_INVOICE_CONTACT_ID in NUMBER,
46 X_AGREEMENT_CONTACT_ID in NUMBER,
47 X_INVOICE_TO_ORG_ID in NUMBER,
48 X_SALESREP_ID in NUMBER,
49 X_START_DATE_ACTIVE in DATE,
50 X_END_DATE_ACTIVE in DATE,
51 X_COMMENTS in VARCHAR2,
52 X_CONTEXT in VARCHAR2,
53 X_ATTRIBUTE1 in VARCHAR2,
54 X_ATTRIBUTE2 in VARCHAR2,
55 X_ATTRIBUTE3 in VARCHAR2,
56 X_ATTRIBUTE4 in VARCHAR2,
57 X_ATTRIBUTE5 in VARCHAR2,
58 X_ATTRIBUTE6 in VARCHAR2,
59 X_ATTRIBUTE7 in VARCHAR2,
60 X_ATTRIBUTE8 in VARCHAR2,
61 X_NAME in VARCHAR2,
62 X_CREATION_DATE in DATE,
63 X_CREATED_BY in NUMBER,
64 X_LAST_UPDATE_DATE in DATE,
65 X_LAST_UPDATED_BY in NUMBER,
66 X_LAST_UPDATE_LOGIN in NUMBER,
67 X_AGREEMENT_SOURCE_CODE in VARCHAR2, --Added by rchellam for OKC
68 X_ORIG_SYSTEM_AGR_ID in NUMBER, --Added by rchellam for OKC
69 X_INVOICE_TO_CUSTOMER_ID in NUMBER -- Added for bug#4029589
70 ) is
71 cursor C is select ROWID from OE_AGREEMENTS_B
72 where AGREEMENT_ID = X_AGREEMENT_ID
73 ;
74 l_x_rowid VARCHAR2(240); /* file.sql.39 changes */
75 begin
76 insert into OE_AGREEMENTS_B (
77 TP_ATTRIBUTE2,
78 TP_ATTRIBUTE3,
79 TP_ATTRIBUTE4,
80 TP_ATTRIBUTE5,
81 TP_ATTRIBUTE6,
82 TP_ATTRIBUTE7,
83 TP_ATTRIBUTE8,
84 TP_ATTRIBUTE9,
85 TP_ATTRIBUTE10,
86 TP_ATTRIBUTE11,
87 TP_ATTRIBUTE12,
88 TP_ATTRIBUTE13,
89 TP_ATTRIBUTE14,
90 TP_ATTRIBUTE15,
91 TP_ATTRIBUTE_CATEGORY,
92 AGREEMENT_ID,
93 AGREEMENT_TYPE_CODE,
94 PRICE_LIST_ID,
95 TERM_ID,
96 OVERRIDE_IRULE_FLAG,
97 OVERRIDE_ARULE_FLAG,
98 SIGNATURE_DATE,
99 AGREEMENT_NUM,
100 TP_ATTRIBUTE1,
101 ATTRIBUTE12,
102 ATTRIBUTE13,
103 ATTRIBUTE14,
104 ATTRIBUTE15,
105 ATTRIBUTE11,
106 ATTRIBUTE9,
107 ATTRIBUTE10,
108 REVISION,
109 REVISION_DATE,
110 REVISION_REASON_CODE,
111 FREIGHT_TERMS_CODE,
112 SHIP_METHOD_CODE,
113 INVOICING_RULE_ID,
114 ACCOUNTING_RULE_ID,
115 SOLD_TO_ORG_ID,
116 PURCHASE_ORDER_NUM,
117 INVOICE_CONTACT_ID,
118 AGREEMENT_CONTACT_ID,
119 INVOICE_TO_ORG_ID,
120 SALESREP_ID,
121 START_DATE_ACTIVE,
122 END_DATE_ACTIVE,
123 COMMENTS,
124 CONTEXT,
125 ATTRIBUTE1,
126 ATTRIBUTE2,
127 ATTRIBUTE3,
128 ATTRIBUTE4,
129 ATTRIBUTE5,
130 ATTRIBUTE6,
131 ATTRIBUTE7,
132 ATTRIBUTE8,
133 CREATION_DATE,
134 CREATED_BY,
135 LAST_UPDATE_DATE,
136 LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN,
138 AGREEMENT_SOURCE_CODE, --added by rchellam for OKC
139 ORIG_SYSTEM_AGR_ID, --added by rchellam for OKC
140 INVOICE_TO_CUSTOMER_ID --Added for bug#4029589
141 ) values (
142 X_TP_ATTRIBUTE2,
143 X_TP_ATTRIBUTE3,
144 X_TP_ATTRIBUTE4,
145 X_TP_ATTRIBUTE5,
146 X_TP_ATTRIBUTE6,
147 X_TP_ATTRIBUTE7,
148 X_TP_ATTRIBUTE8,
149 X_TP_ATTRIBUTE9,
150 X_TP_ATTRIBUTE10,
151 X_TP_ATTRIBUTE11,
152 X_TP_ATTRIBUTE12,
153 X_TP_ATTRIBUTE13,
154 X_TP_ATTRIBUTE14,
155 X_TP_ATTRIBUTE15,
156 X_TP_ATTRIBUTE_CATEGORY,
157 X_AGREEMENT_ID,
158 X_AGREEMENT_TYPE_CODE,
159 X_PRICE_LIST_ID,
160 X_TERM_ID,
161 X_OVERRIDE_IRULE_FLAG,
162 X_OVERRIDE_ARULE_FLAG,
163 X_SIGNATURE_DATE,
164 X_AGREEMENT_NUM,
165 X_TP_ATTRIBUTE1,
166 X_ATTRIBUTE12,
167 X_ATTRIBUTE13,
168 X_ATTRIBUTE14,
169 X_ATTRIBUTE15,
170 X_ATTRIBUTE11,
171 X_ATTRIBUTE9,
172 X_ATTRIBUTE10,
173 X_REVISION,
174 X_REVISION_DATE,
175 X_REVISION_REASON_CODE,
176 X_FREIGHT_TERMS_CODE,
177 X_SHIP_METHOD_CODE,
178 X_INVOICING_RULE_ID,
179 X_ACCOUNTING_RULE_ID,
180 X_SOLD_TO_ORG_ID,
181 X_PURCHASE_ORDER_NUM,
182 X_INVOICE_CONTACT_ID,
183 X_AGREEMENT_CONTACT_ID,
184 X_INVOICE_TO_ORG_ID,
185 X_SALESREP_ID,
186 X_START_DATE_ACTIVE,
187 X_END_DATE_ACTIVE,
188 X_COMMENTS,
189 X_CONTEXT,
190 X_ATTRIBUTE1,
191 X_ATTRIBUTE2,
192 X_ATTRIBUTE3,
193 X_ATTRIBUTE4,
194 X_ATTRIBUTE5,
195 X_ATTRIBUTE6,
196 X_ATTRIBUTE7,
197 X_ATTRIBUTE8,
198 X_CREATION_DATE,
199 X_CREATED_BY,
200 X_LAST_UPDATE_DATE,
201 X_LAST_UPDATED_BY,
202 X_LAST_UPDATE_LOGIN,
203 X_AGREEMENT_SOURCE_CODE, --added by rchellam for OKC
204 X_ORIG_SYSTEM_AGR_ID, --added by rchellam for OKC
205 X_INVOICE_TO_CUSTOMER_ID --Added for bug#4029589
206 );
207
208 insert into OE_AGREEMENTS_TL (
209 CREATION_DATE,
210 CREATED_BY,
211 LAST_UPDATE_LOGIN,
212 NAME,
213 REVISION,
214 LAST_UPDATE_DATE,
215 LAST_UPDATED_BY,
216 AGREEMENT_ID,
217 LANGUAGE,
218 SOURCE_LANG,
219 AGREEMENT_SOURCE_CODE --added by rchellam for OKC
220 ) select
221 X_CREATION_DATE,
222 X_CREATED_BY,
223 X_LAST_UPDATE_LOGIN,
224 X_NAME,
225 X_REVISION,
226 X_LAST_UPDATE_DATE,
227 X_LAST_UPDATED_BY,
228 X_AGREEMENT_ID,
229 L.LANGUAGE_CODE,
230 userenv('LANG'),
231 X_AGREEMENT_SOURCE_CODE --added by rchellam for OKC
232 from FND_LANGUAGES L
233 where L.INSTALLED_FLAG in ('I', 'B')
234 and not exists
235 (select NULL
236 from OE_AGREEMENTS_TL T
237 where T.AGREEMENT_ID = X_AGREEMENT_ID
238 and T.LANGUAGE = L.LANGUAGE_CODE);
239
240 open c;
241 --fetch c into X_ROWID; /* file.sql.39 changes */
242 fetch c into l_x_rowid;
243 if (c%notfound) then
244 close c;
245 raise no_data_found;
246 else
247 X_ROWID := l_x_rowid; /* file.sql.39 changes */
248 end if;
249 close c;
250
251 end INSERT_ROW;
252
253 procedure LOCK_ROW (
254 X_AGREEMENT_ID in NUMBER,
255 X_TP_ATTRIBUTE2 in VARCHAR2,
256 X_TP_ATTRIBUTE3 in VARCHAR2,
257 X_TP_ATTRIBUTE4 in VARCHAR2,
258 X_TP_ATTRIBUTE5 in VARCHAR2,
259 X_TP_ATTRIBUTE6 in VARCHAR2,
260 X_TP_ATTRIBUTE7 in VARCHAR2,
261 X_TP_ATTRIBUTE8 in VARCHAR2,
262 X_TP_ATTRIBUTE9 in VARCHAR2,
263 X_TP_ATTRIBUTE10 in VARCHAR2,
264 X_TP_ATTRIBUTE11 in VARCHAR2,
265 X_TP_ATTRIBUTE12 in VARCHAR2,
266 X_TP_ATTRIBUTE13 in VARCHAR2,
267 X_TP_ATTRIBUTE14 in VARCHAR2,
268 X_TP_ATTRIBUTE15 in VARCHAR2,
269 X_TP_ATTRIBUTE_CATEGORY in VARCHAR2,
270 X_AGREEMENT_TYPE_CODE in VARCHAR2,
271 X_PRICE_LIST_ID in NUMBER,
272 X_TERM_ID in NUMBER,
273 X_OVERRIDE_IRULE_FLAG in VARCHAR2,
274 X_OVERRIDE_ARULE_FLAG in VARCHAR2,
275 X_SIGNATURE_DATE in DATE,
276 X_AGREEMENT_NUM in VARCHAR2,
277 X_TP_ATTRIBUTE1 in VARCHAR2,
278 X_ATTRIBUTE12 in VARCHAR2,
279 X_ATTRIBUTE13 in VARCHAR2,
280 X_ATTRIBUTE14 in VARCHAR2,
281 X_ATTRIBUTE15 in VARCHAR2,
282 X_ATTRIBUTE11 in VARCHAR2,
283 X_ATTRIBUTE9 in VARCHAR2,
284 X_ATTRIBUTE10 in VARCHAR2,
285 X_REVISION in VARCHAR2,
286 X_REVISION_DATE in DATE,
287 X_REVISION_REASON_CODE in VARCHAR2,
288 X_FREIGHT_TERMS_CODE in VARCHAR2,
289 X_SHIP_METHOD_CODE in VARCHAR2,
290 X_INVOICING_RULE_ID in NUMBER,
291 X_ACCOUNTING_RULE_ID in NUMBER,
292 X_SOLD_TO_ORG_ID in NUMBER,
293 X_PURCHASE_ORDER_NUM in VARCHAR2,
294 X_INVOICE_CONTACT_ID in NUMBER,
295 X_AGREEMENT_CONTACT_ID in NUMBER,
296 X_INVOICE_TO_ORG_ID in NUMBER,
297 X_SALESREP_ID in NUMBER,
298 X_START_DATE_ACTIVE in DATE,
299 X_END_DATE_ACTIVE in DATE,
300 X_COMMENTS in VARCHAR2,
301 X_CONTEXT in VARCHAR2,
302 X_ATTRIBUTE1 in VARCHAR2,
303 X_ATTRIBUTE2 in VARCHAR2,
304 X_ATTRIBUTE3 in VARCHAR2,
305 X_ATTRIBUTE4 in VARCHAR2,
306 X_ATTRIBUTE5 in VARCHAR2,
307 X_ATTRIBUTE6 in VARCHAR2,
308 X_ATTRIBUTE7 in VARCHAR2,
309 X_ATTRIBUTE8 in VARCHAR2,
310 X_NAME in VARCHAR2,
311 X_AGREEMENT_SOURCE_CODE in VARCHAR2, --added by rchellam for OKC
312 X_ORIG_SYSTEM_AGR_ID in NUMBER, --added by rchellam for OKC
313 X_INVOICE_TO_CUSTOMER_ID in NUMBER -- Added for bug#4029589
314 ) is
315 cursor c is select
316 TP_ATTRIBUTE2,
317 TP_ATTRIBUTE3,
318 TP_ATTRIBUTE4,
319 TP_ATTRIBUTE5,
320 TP_ATTRIBUTE6,
321 TP_ATTRIBUTE7,
322 TP_ATTRIBUTE8,
323 TP_ATTRIBUTE9,
324 TP_ATTRIBUTE10,
325 TP_ATTRIBUTE11,
326 TP_ATTRIBUTE12,
327 TP_ATTRIBUTE13,
328 TP_ATTRIBUTE14,
329 TP_ATTRIBUTE15,
330 TP_ATTRIBUTE_CATEGORY,
331 AGREEMENT_TYPE_CODE,
332 PRICE_LIST_ID,
333 TERM_ID,
334 OVERRIDE_IRULE_FLAG,
335 OVERRIDE_ARULE_FLAG,
336 SIGNATURE_DATE,
337 AGREEMENT_NUM,
338 TP_ATTRIBUTE1,
339 ATTRIBUTE12,
340 ATTRIBUTE13,
341 ATTRIBUTE14,
342 ATTRIBUTE15,
343 ATTRIBUTE11,
344 ATTRIBUTE9,
345 ATTRIBUTE10,
346 REVISION,
347 REVISION_DATE,
348 REVISION_REASON_CODE,
349 FREIGHT_TERMS_CODE,
350 SHIP_METHOD_CODE,
351 INVOICING_RULE_ID,
352 ACCOUNTING_RULE_ID,
353 SOLD_TO_ORG_ID,
354 PURCHASE_ORDER_NUM,
355 INVOICE_CONTACT_ID,
356 AGREEMENT_CONTACT_ID,
357 INVOICE_TO_ORG_ID,
358 SALESREP_ID,
359 START_DATE_ACTIVE,
360 END_DATE_ACTIVE,
361 COMMENTS,
362 CONTEXT,
363 ATTRIBUTE1,
364 ATTRIBUTE2,
365 ATTRIBUTE3,
366 ATTRIBUTE4,
367 ATTRIBUTE5,
368 ATTRIBUTE6,
369 ATTRIBUTE7,
370 ATTRIBUTE8,
371 AGREEMENT_SOURCE_CODE, --added by rchellam for OKC
372 ORIG_SYSTEM_AGR_ID, --added by rchellam for OKC
373 INVOICE_TO_CUSTOMER_ID -- Added for bug#4029589
374 from OE_AGREEMENTS_B
375 where AGREEMENT_ID = X_AGREEMENT_ID
376 for update of AGREEMENT_ID nowait;
377 recinfo c%rowtype;
378
379 cursor c1 is select
380 NAME,
381 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
382 from OE_AGREEMENTS_TL
383 where AGREEMENT_ID = X_AGREEMENT_ID
384 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
385 for update of AGREEMENT_ID nowait;
386 begin
387 open c;
388 fetch c into recinfo;
389 if (c%notfound) then
390 close c;
391 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
392 app_exception.raise_exception;
393 end if;
394 close c;
395 if ( ((recinfo.TP_ATTRIBUTE2 = X_TP_ATTRIBUTE2)
396 OR ((recinfo.TP_ATTRIBUTE2 is null) AND (X_TP_ATTRIBUTE2 is null)))
397 AND ((recinfo.TP_ATTRIBUTE3 = X_TP_ATTRIBUTE3)
398 OR ((recinfo.TP_ATTRIBUTE3 is null) AND (X_TP_ATTRIBUTE3 is null)))
399 AND ((recinfo.TP_ATTRIBUTE4 = X_TP_ATTRIBUTE4)
400 OR ((recinfo.TP_ATTRIBUTE4 is null) AND (X_TP_ATTRIBUTE4 is null)))
401 AND ((recinfo.TP_ATTRIBUTE5 = X_TP_ATTRIBUTE5)
402 OR ((recinfo.TP_ATTRIBUTE5 is null) AND (X_TP_ATTRIBUTE5 is null)))
403 AND ((recinfo.TP_ATTRIBUTE6 = X_TP_ATTRIBUTE6)
404 OR ((recinfo.TP_ATTRIBUTE6 is null) AND (X_TP_ATTRIBUTE6 is null)))
405 AND ((recinfo.TP_ATTRIBUTE7 = X_TP_ATTRIBUTE7)
406 OR ((recinfo.TP_ATTRIBUTE7 is null) AND (X_TP_ATTRIBUTE7 is null)))
407 AND ((recinfo.TP_ATTRIBUTE8 = X_TP_ATTRIBUTE8)
408 OR ((recinfo.TP_ATTRIBUTE8 is null) AND (X_TP_ATTRIBUTE8 is null)))
409 AND ((recinfo.TP_ATTRIBUTE9 = X_TP_ATTRIBUTE9)
410 OR ((recinfo.TP_ATTRIBUTE9 is null) AND (X_TP_ATTRIBUTE9 is null)))
411 AND ((recinfo.TP_ATTRIBUTE10 = X_TP_ATTRIBUTE10)
412 OR ((recinfo.TP_ATTRIBUTE10 is null) AND (X_TP_ATTRIBUTE10 is null)))
413 AND ((recinfo.TP_ATTRIBUTE11 = X_TP_ATTRIBUTE11)
414 OR ((recinfo.TP_ATTRIBUTE11 is null) AND (X_TP_ATTRIBUTE11 is null)))
415 AND ((recinfo.TP_ATTRIBUTE12 = X_TP_ATTRIBUTE12)
416 OR ((recinfo.TP_ATTRIBUTE12 is null) AND (X_TP_ATTRIBUTE12 is null)))
417 AND ((recinfo.TP_ATTRIBUTE13 = X_TP_ATTRIBUTE13)
418 OR ((recinfo.TP_ATTRIBUTE13 is null) AND (X_TP_ATTRIBUTE13 is null)))
419 AND ((recinfo.TP_ATTRIBUTE14 = X_TP_ATTRIBUTE14)
423 AND ((recinfo.TP_ATTRIBUTE_CATEGORY = X_TP_ATTRIBUTE_CATEGORY)
420 OR ((recinfo.TP_ATTRIBUTE14 is null) AND (X_TP_ATTRIBUTE14 is null)))
421 AND ((recinfo.TP_ATTRIBUTE15 = X_TP_ATTRIBUTE15)
422 OR ((recinfo.TP_ATTRIBUTE15 is null) AND (X_TP_ATTRIBUTE15 is null)))
424 OR ((recinfo.TP_ATTRIBUTE_CATEGORY is null) AND (X_TP_ATTRIBUTE_CATEGORY is null)))
425 AND (recinfo.AGREEMENT_TYPE_CODE = X_AGREEMENT_TYPE_CODE)
426 AND (recinfo.PRICE_LIST_ID = X_PRICE_LIST_ID)
427 AND (recinfo.TERM_ID = X_TERM_ID)
428 AND (recinfo.OVERRIDE_IRULE_FLAG = X_OVERRIDE_IRULE_FLAG)
429 AND (recinfo.OVERRIDE_ARULE_FLAG = X_OVERRIDE_ARULE_FLAG)
430 AND ((recinfo.SIGNATURE_DATE = X_SIGNATURE_DATE)
431 OR ((recinfo.SIGNATURE_DATE is null) AND (X_SIGNATURE_DATE is null)))
432 AND ((recinfo.AGREEMENT_NUM = X_AGREEMENT_NUM)
433 OR ((recinfo.AGREEMENT_NUM is null) AND (X_AGREEMENT_NUM is null)))
434 AND ((recinfo.TP_ATTRIBUTE1 = X_TP_ATTRIBUTE1)
435 OR ((recinfo.TP_ATTRIBUTE1 is null) AND (X_TP_ATTRIBUTE1 is null)))
436 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
437 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
438 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
439 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
440 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
441 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
442 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
443 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
444 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
445 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
446 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
447 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
448 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
449 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
450 AND (recinfo.REVISION = X_REVISION)
451 AND (recinfo.REVISION_DATE = X_REVISION_DATE)
452 AND ((recinfo.REVISION_REASON_CODE = X_REVISION_REASON_CODE)
453 OR ((recinfo.REVISION_REASON_CODE is null) AND (X_REVISION_REASON_CODE is null)))
454 AND ((recinfo.FREIGHT_TERMS_CODE = X_FREIGHT_TERMS_CODE)
455 OR ((recinfo.FREIGHT_TERMS_CODE is null) AND (X_FREIGHT_TERMS_CODE is null)))
456 AND ((recinfo.SHIP_METHOD_CODE = X_SHIP_METHOD_CODE)
457 OR ((recinfo.SHIP_METHOD_CODE is null) AND (X_SHIP_METHOD_CODE is null)))
458 AND ((recinfo.INVOICING_RULE_ID = X_INVOICING_RULE_ID)
459 OR ((recinfo.INVOICING_RULE_ID is null) AND (X_INVOICING_RULE_ID is null)))
460 AND ((recinfo.ACCOUNTING_RULE_ID = X_ACCOUNTING_RULE_ID)
461 OR ((recinfo.ACCOUNTING_RULE_ID is null) AND (X_ACCOUNTING_RULE_ID is null)))
462 AND ((recinfo.SOLD_TO_ORG_ID = X_SOLD_TO_ORG_ID)
463 OR ((recinfo.SOLD_TO_ORG_ID is null) AND (X_SOLD_TO_ORG_ID is null)))
464 AND ((recinfo.PURCHASE_ORDER_NUM = X_PURCHASE_ORDER_NUM)
465 OR ((recinfo.PURCHASE_ORDER_NUM is null) AND (X_PURCHASE_ORDER_NUM is null)))
466 AND ((recinfo.INVOICE_CONTACT_ID = X_INVOICE_CONTACT_ID)
467 OR ((recinfo.INVOICE_CONTACT_ID is null) AND (X_INVOICE_CONTACT_ID is null)))
468 AND ((recinfo.AGREEMENT_CONTACT_ID = X_AGREEMENT_CONTACT_ID)
469 OR ((recinfo.AGREEMENT_CONTACT_ID is null) AND (X_AGREEMENT_CONTACT_ID is null)))
470 AND ((recinfo.INVOICE_TO_ORG_ID = X_INVOICE_TO_ORG_ID)
471 OR ((recinfo.INVOICE_TO_ORG_ID is null) AND (X_INVOICE_TO_ORG_ID is null)))
472 AND ((recinfo.SALESREP_ID = X_SALESREP_ID)
473 OR ((recinfo.SALESREP_ID is null) AND (X_SALESREP_ID is null)))
474 --Begin code added by rchellam for OKC
475 AND ((recinfo.AGREEMENT_SOURCE_CODE = X_AGREEMENT_SOURCE_CODE)
476 OR ((recinfo.AGREEMENT_SOURCE_CODE is null) AND (X_AGREEMENT_SOURCE_CODE is null)))
477 AND ((recinfo.ORIG_SYSTEM_AGR_ID = X_ORIG_SYSTEM_AGR_ID)
478 OR ((recinfo.ORIG_SYSTEM_AGR_ID is null) AND (X_ORIG_SYSTEM_AGR_ID is null)))
479 --END code added by rchellam for OKC
480 -- Added for bug#4029589
481 AND ((recinfo.INVOICE_TO_CUSTOMER_ID = X_INVOICE_TO_CUSTOMER_ID)
482 OR ((recinfo.INVOICE_TO_CUSTOMER_ID is null) AND (X_INVOICE_TO_CUSTOMER_ID is null)))
483 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
484 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
485 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
486 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
487 AND ((recinfo.COMMENTS = X_COMMENTS)
488 OR ((recinfo.COMMENTS is null) AND (X_COMMENTS is null)))
489 AND ((recinfo.CONTEXT = X_CONTEXT)
490 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
491 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
492 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
493 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
494 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
495 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
496 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
497 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
498 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
499 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
500 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
501 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
502 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
506 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
503 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
504 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
505 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
507 ) then
508 null;
509 else
510 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
511 app_exception.raise_exception;
512 end if;
513
514 for tlinfo in c1 loop
515 if (tlinfo.BASELANG = 'Y') then
516 if ( (tlinfo.NAME = X_NAME)
517 ) then
518 null;
519 else
520 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
521 app_exception.raise_exception;
522 end if;
523 end if;
524 end loop;
525 return;
526 end LOCK_ROW;
527
528 procedure UPDATE_ROW (
529 X_AGREEMENT_ID in NUMBER,
530 X_TP_ATTRIBUTE2 in VARCHAR2,
531 X_TP_ATTRIBUTE3 in VARCHAR2,
532 X_TP_ATTRIBUTE4 in VARCHAR2,
533 X_TP_ATTRIBUTE5 in VARCHAR2,
534 X_TP_ATTRIBUTE6 in VARCHAR2,
535 X_TP_ATTRIBUTE7 in VARCHAR2,
536 X_TP_ATTRIBUTE8 in VARCHAR2,
537 X_TP_ATTRIBUTE9 in VARCHAR2,
538 X_TP_ATTRIBUTE10 in VARCHAR2,
539 X_TP_ATTRIBUTE11 in VARCHAR2,
540 X_TP_ATTRIBUTE12 in VARCHAR2,
541 X_TP_ATTRIBUTE13 in VARCHAR2,
542 X_TP_ATTRIBUTE14 in VARCHAR2,
543 X_TP_ATTRIBUTE15 in VARCHAR2,
544 X_TP_ATTRIBUTE_CATEGORY in VARCHAR2,
545 X_AGREEMENT_TYPE_CODE in VARCHAR2,
546 X_PRICE_LIST_ID in NUMBER,
547 X_TERM_ID in NUMBER,
548 X_OVERRIDE_IRULE_FLAG in VARCHAR2,
549 X_OVERRIDE_ARULE_FLAG in VARCHAR2,
550 X_SIGNATURE_DATE in DATE,
551 X_AGREEMENT_NUM in VARCHAR2,
552 X_TP_ATTRIBUTE1 in VARCHAR2,
553 X_ATTRIBUTE12 in VARCHAR2,
554 X_ATTRIBUTE13 in VARCHAR2,
555 X_ATTRIBUTE14 in VARCHAR2,
556 X_ATTRIBUTE15 in VARCHAR2,
557 X_ATTRIBUTE11 in VARCHAR2,
558 X_ATTRIBUTE9 in VARCHAR2,
559 X_ATTRIBUTE10 in VARCHAR2,
560 X_REVISION in VARCHAR2,
561 X_REVISION_DATE in DATE,
562 X_REVISION_REASON_CODE in VARCHAR2,
563 X_FREIGHT_TERMS_CODE in VARCHAR2,
564 X_SHIP_METHOD_CODE in VARCHAR2,
565 X_INVOICING_RULE_ID in NUMBER,
566 X_ACCOUNTING_RULE_ID in NUMBER,
567 X_SOLD_TO_ORG_ID in NUMBER,
568 X_PURCHASE_ORDER_NUM in VARCHAR2,
569 X_INVOICE_CONTACT_ID in NUMBER,
570 X_AGREEMENT_CONTACT_ID in NUMBER,
571 X_INVOICE_TO_ORG_ID in NUMBER,
572 X_SALESREP_ID in NUMBER,
573 X_START_DATE_ACTIVE in DATE,
574 X_END_DATE_ACTIVE in DATE,
575 X_COMMENTS in VARCHAR2,
576 X_CONTEXT in VARCHAR2,
577 X_ATTRIBUTE1 in VARCHAR2,
578 X_ATTRIBUTE2 in VARCHAR2,
579 X_ATTRIBUTE3 in VARCHAR2,
580 X_ATTRIBUTE4 in VARCHAR2,
581 X_ATTRIBUTE5 in VARCHAR2,
582 X_ATTRIBUTE6 in VARCHAR2,
583 X_ATTRIBUTE7 in VARCHAR2,
584 X_ATTRIBUTE8 in VARCHAR2,
585 X_NAME in VARCHAR2,
586 X_LAST_UPDATE_DATE in DATE,
587 X_LAST_UPDATED_BY in NUMBER,
588 X_LAST_UPDATE_LOGIN in NUMBER,
589 X_AGREEMENT_SOURCE_CODE in VARCHAR2, --added by rchellam for OKC
590 X_ORIG_SYSTEM_AGR_ID in NUMBER, --added by rchellam for OKC
591 X_INVOICE_TO_CUSTOMER_ID in NUMBER -- Added for bug#4029589
592 ) is
593 begin
594 update OE_AGREEMENTS_B set
595 TP_ATTRIBUTE2 = X_TP_ATTRIBUTE2,
596 TP_ATTRIBUTE3 = X_TP_ATTRIBUTE3,
597 TP_ATTRIBUTE4 = X_TP_ATTRIBUTE4,
598 TP_ATTRIBUTE5 = X_TP_ATTRIBUTE5,
599 TP_ATTRIBUTE6 = X_TP_ATTRIBUTE6,
600 TP_ATTRIBUTE7 = X_TP_ATTRIBUTE7,
601 TP_ATTRIBUTE8 = X_TP_ATTRIBUTE8,
602 TP_ATTRIBUTE9 = X_TP_ATTRIBUTE9,
603 TP_ATTRIBUTE10 = X_TP_ATTRIBUTE10,
604 TP_ATTRIBUTE11 = X_TP_ATTRIBUTE11,
605 TP_ATTRIBUTE12 = X_TP_ATTRIBUTE12,
606 TP_ATTRIBUTE13 = X_TP_ATTRIBUTE13,
607 TP_ATTRIBUTE14 = X_TP_ATTRIBUTE14,
608 TP_ATTRIBUTE15 = X_TP_ATTRIBUTE15,
609 TP_ATTRIBUTE_CATEGORY = X_TP_ATTRIBUTE_CATEGORY,
610 AGREEMENT_TYPE_CODE = X_AGREEMENT_TYPE_CODE,
611 PRICE_LIST_ID = X_PRICE_LIST_ID,
612 TERM_ID = X_TERM_ID,
613 OVERRIDE_IRULE_FLAG = X_OVERRIDE_IRULE_FLAG,
614 OVERRIDE_ARULE_FLAG = X_OVERRIDE_ARULE_FLAG,
615 SIGNATURE_DATE = X_SIGNATURE_DATE,
616 AGREEMENT_NUM = X_AGREEMENT_NUM,
617 TP_ATTRIBUTE1 = X_TP_ATTRIBUTE1,
618 ATTRIBUTE12 = X_ATTRIBUTE12,
619 ATTRIBUTE13 = X_ATTRIBUTE13,
620 ATTRIBUTE14 = X_ATTRIBUTE14,
621 ATTRIBUTE15 = X_ATTRIBUTE15,
622 ATTRIBUTE11 = X_ATTRIBUTE11,
623 ATTRIBUTE9 = X_ATTRIBUTE9,
624 ATTRIBUTE10 = X_ATTRIBUTE10,
625 REVISION = X_REVISION,
626 REVISION_DATE = X_REVISION_DATE,
627 REVISION_REASON_CODE = X_REVISION_REASON_CODE,
628 FREIGHT_TERMS_CODE = X_FREIGHT_TERMS_CODE,
629 SHIP_METHOD_CODE = X_SHIP_METHOD_CODE,
630 INVOICING_RULE_ID = X_INVOICING_RULE_ID,
631 ACCOUNTING_RULE_ID = X_ACCOUNTING_RULE_ID,
632 SOLD_TO_ORG_ID = X_SOLD_TO_ORG_ID,
633 PURCHASE_ORDER_NUM = X_PURCHASE_ORDER_NUM,
634 INVOICE_CONTACT_ID = X_INVOICE_CONTACT_ID,
635 AGREEMENT_CONTACT_ID = X_AGREEMENT_CONTACT_ID,
636 INVOICE_TO_ORG_ID = X_INVOICE_TO_ORG_ID,
637 SALESREP_ID = X_SALESREP_ID,
638 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
639 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
643 ATTRIBUTE2 = X_ATTRIBUTE2,
640 COMMENTS = X_COMMENTS,
641 CONTEXT = X_CONTEXT,
642 ATTRIBUTE1 = X_ATTRIBUTE1,
644 ATTRIBUTE3 = X_ATTRIBUTE3,
645 ATTRIBUTE4 = X_ATTRIBUTE4,
646 ATTRIBUTE5 = X_ATTRIBUTE5,
647 ATTRIBUTE6 = X_ATTRIBUTE6,
648 ATTRIBUTE7 = X_ATTRIBUTE7,
649 ATTRIBUTE8 = X_ATTRIBUTE8,
650 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
651 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
652 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
653 AGREEMENT_SOURCE_CODE = X_AGREEMENT_SOURCE_CODE, --added by rchellam for OKC
654 ORIG_SYSTEM_AGR_ID = X_ORIG_SYSTEM_AGR_ID, --added by rchellam for OKC
655 INVOICE_TO_CUSTOMER_ID = X_INVOICE_TO_CUSTOMER_ID -- Added for bug#4029589
656 where AGREEMENT_ID = X_AGREEMENT_ID;
657
658 if (sql%notfound) then
659 raise no_data_found;
660 end if;
661
662 update OE_AGREEMENTS_TL set
663 NAME = X_NAME,
664 REVISION = X_REVISION,
665 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
666 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
667 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
668 SOURCE_LANG = userenv('LANG'),
669 AGREEMENT_SOURCE_CODE = X_AGREEMENT_SOURCE_CODE --added by rchellam for OKC
670 where AGREEMENT_ID = X_AGREEMENT_ID
671 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
672
673 if (sql%notfound) then
674 raise no_data_found;
675 end if;
676 end UPDATE_ROW;
677
678 procedure DELETE_ROW (
679 X_AGREEMENT_ID in NUMBER
680 ) is
681 begin
682 delete from OE_AGREEMENTS_TL
683 where AGREEMENT_ID = X_AGREEMENT_ID;
684
685 if (sql%notfound) then
686 raise no_data_found;
687 end if;
688
689 delete from OE_AGREEMENTS_B
690 where AGREEMENT_ID = X_AGREEMENT_ID;
691
692 if (sql%notfound) then
693 raise no_data_found;
694 end if;
695 end DELETE_ROW;
696
697 procedure ADD_LANGUAGE
698 is
699 begin
700 delete from OE_AGREEMENTS_TL T
701 where not exists
702 (select NULL
703 from OE_AGREEMENTS_B B
704 where B.AGREEMENT_ID = T.AGREEMENT_ID
705 );
706
707 update OE_AGREEMENTS_TL T set (
708 NAME
709 ) = (select
710 B.NAME
711 from OE_AGREEMENTS_TL B
712 where B.AGREEMENT_ID = T.AGREEMENT_ID
713 and B.LANGUAGE = T.SOURCE_LANG)
714 where (
715 T.AGREEMENT_ID,
716 T.LANGUAGE
717 ) in (select
718 SUBT.AGREEMENT_ID,
719 SUBT.LANGUAGE
720 from OE_AGREEMENTS_TL SUBB, OE_AGREEMENTS_TL SUBT
721 where SUBB.AGREEMENT_ID = SUBT.AGREEMENT_ID
722 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
723 and (SUBB.NAME <> SUBT.NAME
724 ));
725
726 insert into OE_AGREEMENTS_TL (
727 CREATION_DATE,
728 CREATED_BY,
729 LAST_UPDATE_LOGIN,
730 NAME,
731 REVISION,
732 LAST_UPDATE_DATE,
733 LAST_UPDATED_BY,
734 AGREEMENT_ID,
735 LANGUAGE,
736 SOURCE_LANG,
737 AGREEMENT_SOURCE_CODE --added by rchellam for OKC
738 ) select
739 B.CREATION_DATE,
740 B.CREATED_BY,
741 B.LAST_UPDATE_LOGIN,
742 B.NAME,
743 B.REVISION,
744 B.LAST_UPDATE_DATE,
745 B.LAST_UPDATED_BY,
746 B.AGREEMENT_ID,
747 L.LANGUAGE_CODE,
748 B.SOURCE_LANG,
749 B.AGREEMENT_SOURCE_CODE --added by rchellam for OKC
750 from OE_AGREEMENTS_TL B, FND_LANGUAGES L
751 where L.INSTALLED_FLAG in ('I', 'B')
752 and B.LANGUAGE = userenv('LANG')
753 and not exists
754 (select NULL
755 from OE_AGREEMENTS_TL T
756 where T.AGREEMENT_ID = B.AGREEMENT_ID
757 and T.LANGUAGE = L.LANGUAGE_CODE);
758 end ADD_LANGUAGE;
759
760 end OE_AGREEMENTS_PKG;
[Home] [Help]