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