1 package body OKE_CHG_REQUESTS_PKG as
2 /* $Header: OKEOCRXB.pls 120.0 2005/05/25 17:57:25 appldev noship $ */
3
4 PROCEDURE Start_WF_Process
5 ( X_LAST_CHG_LOG_ID IN NUMBER
6 ) IS
7
8 CURSOR c IS
9 SELECT wf_item_type
10 , wf_item_key
11 FROM oke_chg_logs
12 WHERE chg_log_id = X_Last_Chg_Log_ID;
13 crec c%rowtype;
14
15 BEGIN
16
17 OPEN c;
18 FETCH c INTO crec;
19 CLOSE c;
20
21 IF ( crec.wf_item_key IS NOT NULL ) THEN
22 --
23 -- Start the Workflow Process
24 --
25 WF_ENGINE.StartProcess( itemtype => crec.wf_item_type
26 , itemkey => crec.wf_item_key );
27 END IF;
28
29 EXCEPTION
30 WHEN OTHERS THEN
31 NULL;
32 END Start_WF_Process;
33
34
35 procedure INSERT_ROW
36 ( X_ROWID in out NOCOPY VARCHAR2
37 , X_CHG_REQUEST_ID in out NOCOPY NUMBER
38 , X_CREATION_DATE in DATE
39 , X_CREATED_BY in NUMBER
40 , X_LAST_UPDATE_DATE in DATE
41 , X_LAST_UPDATED_BY in NUMBER
42 , X_LAST_UPDATE_LOGIN in NUMBER
43 , X_K_HEADER_ID in NUMBER
44 , X_CHG_REQUEST_NUM in out NOCOPY VARCHAR2
45 , X_CHG_TYPE_CODE in VARCHAR2
46 , X_CHG_STATUS_CODE in VARCHAR2
47 , X_CHG_REASON_CODE in VARCHAR2
48 , X_IMPACT_FUNDING_FLAG in VARCHAR2
49 , X_EFFECTIVE_DATE in DATE
50 , X_REQUESTED_BY_PERSON_ID in NUMBER
51 , X_REQUESTED_DATE in DATE
52 , X_RECEIVE_DATE in DATE
53 , X_APPROVE_DATE in out NOCOPY DATE
54 , X_IMPLEMENT_DATE in out NOCOPY DATE
55 , X_PREV_VERSION in NUMBER
56 , X_NEW_VERSION in NUMBER
57 , X_DESCRIPTION in VARCHAR2
58 , X_CHG_TEXT in VARCHAR2
59 , X_LAST_CHG_LOG_ID in out NOCOPY NUMBER
60 , X_ATTRIBUTE_CATEGORY in VARCHAR2
61 , X_ATTRIBUTE1 in VARCHAR2
62 , X_ATTRIBUTE2 in VARCHAR2
63 , X_ATTRIBUTE3 in VARCHAR2
64 , X_ATTRIBUTE4 in VARCHAR2
65 , X_ATTRIBUTE5 in VARCHAR2
66 , X_ATTRIBUTE6 in VARCHAR2
67 , X_ATTRIBUTE7 in VARCHAR2
68 , X_ATTRIBUTE8 in VARCHAR2
69 , X_ATTRIBUTE9 in VARCHAR2
70 , X_ATTRIBUTE10 in VARCHAR2
71 , X_ATTRIBUTE11 in VARCHAR2
72 , X_ATTRIBUTE12 in VARCHAR2
73 , X_ATTRIBUTE13 in VARCHAR2
74 , X_ATTRIBUTE14 in VARCHAR2
75 , X_ATTRIBUTE15 in VARCHAR2
76 ) is
77
78 cursor C1 is
79 SELECT oke_chg_requests_s.nextval
80 FROM dual;
81
82 cursor C2 is
83 SELECT ROWID
84 , LAST_CHG_LOG_ID
85 , APPROVE_DATE
86 , IMPLEMENT_DATE
87 FROM oke_chg_requests
88 WHERE chg_request_id = X_CHG_REQUEST_ID
89 ;
90
91 Auto_ChgReq_Number BOOLEAN := FALSE;
92
93 begin
94
95 OPEN c1;
96 FETCH c1 INTO X_Chg_Request_ID;
97 CLOSE c1;
98
99 --
100 -- Assign a dummy Number first
101 --
102 IF ( X_Chg_Request_Num IS NULL ) THEN
103 Auto_ChgReq_Number := TRUE;
104 X_Chg_Request_Num := 'TEMP:' || rpad(X_Chg_Request_ID , 25 , '*');
105 END IF;
106
107 SAVEPOINT OKE_CHG_REQUESTS_INSERT;
108
109 insert into OKE_CHG_REQUESTS (
110 CHG_REQUEST_ID
111 , CREATION_DATE
112 , CREATED_BY
113 , LAST_UPDATE_DATE
114 , LAST_UPDATED_BY
115 , LAST_UPDATE_LOGIN
116 , K_HEADER_ID
117 , CHG_REQUEST_NUM
118 , CHG_TYPE_CODE
119 , CHG_STATUS_CODE
120 , CHG_REASON_CODE
121 , IMPACT_FUNDING_FLAG
122 , EFFECTIVE_DATE
123 , REQUESTED_BY_PERSON_ID
124 , REQUESTED_DATE
125 , RECEIVE_DATE
126 , APPROVE_DATE
127 , IMPLEMENT_DATE
128 , PREV_VERSION
129 , NEW_VERSION
130 , DESCRIPTION
131 , CHG_TEXT
132 , ATTRIBUTE_CATEGORY
133 , ATTRIBUTE1
134 , ATTRIBUTE2
135 , ATTRIBUTE3
136 , ATTRIBUTE4
137 , ATTRIBUTE5
138 , ATTRIBUTE6
139 , ATTRIBUTE7
140 , ATTRIBUTE8
141 , ATTRIBUTE9
142 , ATTRIBUTE10
143 , ATTRIBUTE11
144 , ATTRIBUTE12
145 , ATTRIBUTE13
146 , ATTRIBUTE14
147 , ATTRIBUTE15
148 ) values (
149 X_CHG_REQUEST_ID
150 , X_CREATION_DATE
151 , X_CREATED_BY
152 , X_LAST_UPDATE_DATE
153 , X_LAST_UPDATED_BY
154 , X_LAST_UPDATE_LOGIN
155 , X_K_HEADER_ID
156 , X_CHG_REQUEST_NUM
157 , X_CHG_TYPE_CODE
158 , X_CHG_STATUS_CODE
159 , X_CHG_REASON_CODE
160 , X_IMPACT_FUNDING_FLAG
161 , X_EFFECTIVE_DATE
162 , X_REQUESTED_BY_PERSON_ID
163 , X_REQUESTED_DATE
164 , X_RECEIVE_DATE
165 , X_APPROVE_DATE
166 , X_IMPLEMENT_DATE
167 , X_PREV_VERSION
168 , X_NEW_VERSION
169 , X_DESCRIPTION
170 , X_CHG_TEXT
171 , X_ATTRIBUTE_CATEGORY
172 , X_ATTRIBUTE1
173 , X_ATTRIBUTE2
174 , X_ATTRIBUTE3
175 , X_ATTRIBUTE4
176 , X_ATTRIBUTE5
177 , X_ATTRIBUTE6
178 , X_ATTRIBUTE7
179 , X_ATTRIBUTE8
180 , X_ATTRIBUTE9
181 , X_ATTRIBUTE10
182 , X_ATTRIBUTE11
183 , X_ATTRIBUTE12
184 , X_ATTRIBUTE13
185 , X_ATTRIBUTE14
186 , X_ATTRIBUTE15
187 );
188
189 open c2;
190 fetch c2 into X_ROWID
191 , X_LAST_CHG_LOG_ID
192 , X_APPROVE_DATE
193 , X_IMPLEMENT_DATE;
194 if (c2%notfound) then
195 close c2;
196 raise no_data_found;
197 end if;
198 close c2;
199
200 --
201 -- Now assign the real number to reduce lock time
202 --
203 IF ( Auto_ChgReq_Number ) THEN
204 X_Chg_Request_Num := OKE_NUMBER_SEQUENCES_PKG.Next_ChgReq_Number
205 ( X_CHG_TYPE_CODE , X_K_HEADER_ID );
206
207 UPDATE oke_chg_requests
208 SET Chg_Request_Num = X_Chg_Request_Num
209 WHERE Chg_Request_ID = X_Chg_Request_ID;
210
211 END IF;
212
213 Start_WF_Process( X_Last_Chg_Log_ID );
214
215 EXCEPTION
216 WHEN OTHERS THEN
217 ROLLBACK TO SAVEPOINT OKE_CHG_REQUESTS_INSERT;
218 RAISE;
219
220 end INSERT_ROW;
221
222 procedure LOCK_ROW
223 ( X_CHG_REQUEST_ID in NUMBER
224 , X_K_HEADER_ID in NUMBER
225 , X_CHG_REQUEST_NUM in VARCHAR2
226 , X_CHG_TYPE_CODE in VARCHAR2
227 , X_CHG_STATUS_CODE in VARCHAR2
228 , X_CHG_REASON_CODE in VARCHAR2
229 , X_IMPACT_FUNDING_FLAG in VARCHAR2
230 , X_EFFECTIVE_DATE in DATE
231 , X_REQUESTED_BY_PERSON_ID in NUMBER
232 , X_REQUESTED_DATE in DATE
233 , X_RECEIVE_DATE in DATE
234 , X_APPROVE_DATE in DATE
235 , X_IMPLEMENT_DATE in DATE
236 , X_PREV_VERSION in NUMBER
237 , X_NEW_VERSION in NUMBER
238 , X_DESCRIPTION in VARCHAR2
239 , X_CHG_TEXT in VARCHAR2
240 , X_ATTRIBUTE_CATEGORY in VARCHAR2
241 , X_ATTRIBUTE1 in VARCHAR2
242 , X_ATTRIBUTE2 in VARCHAR2
243 , X_ATTRIBUTE3 in VARCHAR2
244 , X_ATTRIBUTE4 in VARCHAR2
245 , X_ATTRIBUTE5 in VARCHAR2
246 , X_ATTRIBUTE6 in VARCHAR2
247 , X_ATTRIBUTE7 in VARCHAR2
248 , X_ATTRIBUTE8 in VARCHAR2
249 , X_ATTRIBUTE9 in VARCHAR2
250 , X_ATTRIBUTE10 in VARCHAR2
251 , X_ATTRIBUTE11 in VARCHAR2
252 , X_ATTRIBUTE12 in VARCHAR2
253 , X_ATTRIBUTE13 in VARCHAR2
254 , X_ATTRIBUTE14 in VARCHAR2
255 , X_ATTRIBUTE15 in VARCHAR2
256 ) is
257 cursor c is select
258 CHG_REQUEST_ID
259 , K_HEADER_ID
260 , CHG_REQUEST_NUM
261 , CHG_TYPE_CODE
262 , CHG_STATUS_CODE
263 , CHG_REASON_CODE
264 , IMPACT_FUNDING_FLAG
265 , EFFECTIVE_DATE
266 , REQUESTED_BY_PERSON_ID
267 , REQUESTED_DATE
268 , RECEIVE_DATE
269 , APPROVE_DATE
270 , IMPLEMENT_DATE
271 , PREV_VERSION
272 , NEW_VERSION
273 , DESCRIPTION
274 , CHG_TEXT
275 , ATTRIBUTE_CATEGORY
276 , ATTRIBUTE1
277 , ATTRIBUTE2
278 , ATTRIBUTE3
279 , ATTRIBUTE4
280 , ATTRIBUTE5
281 , ATTRIBUTE6
282 , ATTRIBUTE7
283 , ATTRIBUTE8
284 , ATTRIBUTE9
285 , ATTRIBUTE10
286 , ATTRIBUTE11
287 , ATTRIBUTE12
288 , ATTRIBUTE13
289 , ATTRIBUTE14
290 , ATTRIBUTE15
291 from OKE_CHG_REQUESTS
292 where CHG_REQUEST_ID = X_CHG_REQUEST_ID
293 for update of CHG_REQUEST_ID nowait;
294 recinfo c%rowtype;
295
296 begin
297 open c;
298 fetch c into recinfo;
299 if (c%notfound) then
300 close c;
301 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
302 app_exception.raise_exception;
303 end if;
304 close c;
305
306 if ( (recinfo.CHG_REQUEST_ID = X_CHG_REQUEST_ID)
307 AND ((recinfo.K_HEADER_ID = X_K_HEADER_ID)
308 OR ((recinfo.K_HEADER_ID is null) AND (X_K_HEADER_ID is null)))
309 AND ((recinfo.CHG_REQUEST_NUM = X_CHG_REQUEST_NUM)
310 OR ((recinfo.CHG_REQUEST_NUM is null) AND (X_CHG_REQUEST_NUM is null)))
311 AND ((recinfo.CHG_TYPE_CODE = X_CHG_TYPE_CODE)
312 OR ((recinfo.CHG_TYPE_CODE is null) AND (X_CHG_TYPE_CODE is null)))
313 AND ((recinfo.CHG_STATUS_CODE = X_CHG_STATUS_CODE)
314 OR ((recinfo.CHG_STATUS_CODE is null) AND (X_CHG_STATUS_CODE is null)))
315 AND ((recinfo.CHG_REASON_CODE = X_CHG_REASON_CODE)
316 OR ((recinfo.CHG_REASON_CODE is null) AND (X_CHG_REASON_CODE is null)))
317 AND ((recinfo.IMPACT_FUNDING_FLAG = X_IMPACT_FUNDING_FLAG)
318 OR ((recinfo.IMPACT_FUNDING_FLAG is null) AND (X_IMPACT_FUNDING_FLAG is null)))
319 AND ((recinfo.EFFECTIVE_DATE = X_EFFECTIVE_DATE)
320 OR ((recinfo.EFFECTIVE_DATE is null) AND (X_EFFECTIVE_DATE is null)))
321 AND ((recinfo.REQUESTED_BY_PERSON_ID = X_REQUESTED_BY_PERSON_ID)
322 OR ((recinfo.REQUESTED_BY_PERSON_ID is null) AND (X_REQUESTED_BY_PERSON_ID is null)))
323 AND ((recinfo.REQUESTED_DATE = X_REQUESTED_DATE)
324 OR ((recinfo.REQUESTED_DATE is null) AND (X_REQUESTED_DATE is null)))
325 AND ((recinfo.RECEIVE_DATE = X_RECEIVE_DATE)
326 OR ((recinfo.RECEIVE_DATE is null) AND (X_RECEIVE_DATE is null)))
327 AND ((recinfo.APPROVE_DATE = X_APPROVE_DATE)
328 OR ((recinfo.APPROVE_DATE is null) AND (X_APPROVE_DATE is null)))
329 AND ((recinfo.IMPLEMENT_DATE = X_IMPLEMENT_DATE)
330 OR ((recinfo.IMPLEMENT_DATE is null) AND (X_IMPLEMENT_DATE is null)))
331 AND ((recinfo.PREV_VERSION = X_PREV_VERSION)
332 OR ((recinfo.PREV_VERSION is null) AND (X_PREV_VERSION is null)))
333 AND ((recinfo.NEW_VERSION = X_NEW_VERSION)
334 OR ((recinfo.NEW_VERSION is null) AND (X_NEW_VERSION is null)))
335 AND ((recinfo.DESCRIPTION = X_DESCRIPTION)
336 OR ((recinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
337 AND ((recinfo.CHG_TEXT = X_CHG_TEXT)
338 OR ((recinfo.CHG_TEXT is null) AND (X_CHG_TEXT is null)))
339 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
340 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
341 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
342 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
343 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
344 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
345 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
346 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
347 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
348 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
349 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
350 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
351 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
352 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
353 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
354 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
355 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
356 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
357 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
358 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
359 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
360 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
361 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
362 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
363 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
364 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
365 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
366 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
367 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
368 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
369 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
370 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
371 ) then
372 null;
373 else
374 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
375 app_exception.raise_exception;
376 end if;
377
378 return;
379 end LOCK_ROW;
380
381 procedure UPDATE_ROW
382 ( X_CHG_REQUEST_ID in NUMBER
383 , X_LAST_UPDATE_DATE in DATE
384 , X_LAST_UPDATED_BY in NUMBER
385 , X_LAST_UPDATE_LOGIN in NUMBER
386 , X_K_HEADER_ID in NUMBER
387 , X_CHG_REQUEST_NUM in VARCHAR2
388 , X_CHG_TYPE_CODE in VARCHAR2
389 , X_CHG_STATUS_CODE in VARCHAR2
390 , X_CHG_REASON_CODE in VARCHAR2
391 , X_IMPACT_FUNDING_FLAG in VARCHAR2
392 , X_EFFECTIVE_DATE in DATE
393 , X_REQUESTED_BY_PERSON_ID in NUMBER
394 , X_REQUESTED_DATE in DATE
395 , X_RECEIVE_DATE in DATE
396 , X_APPROVE_DATE in out NOCOPY DATE
397 , X_IMPLEMENT_DATE in out NOCOPY DATE
398 , X_PREV_VERSION in NUMBER
399 , X_NEW_VERSION in NUMBER
400 , X_DESCRIPTION in VARCHAR2
401 , X_CHG_TEXT in VARCHAR2
402 , X_LAST_CHG_LOG_ID in out NOCOPY NUMBER
403 , X_ATTRIBUTE_CATEGORY in VARCHAR2
404 , X_ATTRIBUTE1 in VARCHAR2
405 , X_ATTRIBUTE2 in VARCHAR2
406 , X_ATTRIBUTE3 in VARCHAR2
407 , X_ATTRIBUTE4 in VARCHAR2
408 , X_ATTRIBUTE5 in VARCHAR2
409 , X_ATTRIBUTE6 in VARCHAR2
410 , X_ATTRIBUTE7 in VARCHAR2
411 , X_ATTRIBUTE8 in VARCHAR2
412 , X_ATTRIBUTE9 in VARCHAR2
413 , X_ATTRIBUTE10 in VARCHAR2
414 , X_ATTRIBUTE11 in VARCHAR2
415 , X_ATTRIBUTE12 in VARCHAR2
416 , X_ATTRIBUTE13 in VARCHAR2
417 , X_ATTRIBUTE14 in VARCHAR2
418 , X_ATTRIBUTE15 in VARCHAR2
419 ) is
420
421 cursor C is
422 SELECT LAST_CHG_LOG_ID
423 , APPROVE_DATE
424 , IMPLEMENT_DATE
425 FROM oke_chg_requests
426 WHERE chg_request_id = X_CHG_REQUEST_ID
427 ;
428
429 cursor C2 is
430 SELECT LAST_CHG_LOG_ID
431 FROM oke_chg_requests
432 WHERE chg_request_id = X_CHG_REQUEST_ID
433 ;
434
435 Prev_Chg_Log_ID NUMBER;
436
437 begin
438
439 open c2;
440 fetch c2 into Prev_Chg_Log_ID;
441 close c2;
442
443 update OKE_CHG_REQUESTS set
444 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
445 , LAST_UPDATED_BY = X_LAST_UPDATED_BY
446 , LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
447 , K_HEADER_ID = X_K_HEADER_ID
448 , CHG_REQUEST_NUM = X_CHG_REQUEST_NUM
449 , CHG_TYPE_CODE = X_CHG_TYPE_CODE
450 , CHG_STATUS_CODE = X_CHG_STATUS_CODE
451 , CHG_REASON_CODE = X_CHG_REASON_CODE
452 , IMPACT_FUNDING_FLAG = X_IMPACT_FUNDING_FLAG
453 , EFFECTIVE_DATE = X_EFFECTIVE_DATE
454 , REQUESTED_BY_PERSON_ID = X_REQUESTED_BY_PERSON_ID
455 , REQUESTED_DATE = X_REQUESTED_DATE
456 , RECEIVE_DATE = X_RECEIVE_DATE
457 , APPROVE_DATE = X_APPROVE_DATE
458 , IMPLEMENT_DATE = X_IMPLEMENT_DATE
459 , PREV_VERSION = X_PREV_VERSION
460 , NEW_VERSION = X_NEW_VERSION
461 , DESCRIPTION = X_DESCRIPTION
462 , CHG_TEXT = X_CHG_TEXT
463 , ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY
464 , ATTRIBUTE1 = X_ATTRIBUTE1
465 , ATTRIBUTE2 = X_ATTRIBUTE2
466 , ATTRIBUTE3 = X_ATTRIBUTE3
467 , ATTRIBUTE4 = X_ATTRIBUTE4
468 , ATTRIBUTE5 = X_ATTRIBUTE5
469 , ATTRIBUTE6 = X_ATTRIBUTE6
470 , ATTRIBUTE7 = X_ATTRIBUTE7
471 , ATTRIBUTE8 = X_ATTRIBUTE8
472 , ATTRIBUTE9 = X_ATTRIBUTE9
473 , ATTRIBUTE10 = X_ATTRIBUTE10
474 , ATTRIBUTE11 = X_ATTRIBUTE11
475 , ATTRIBUTE12 = X_ATTRIBUTE12
476 , ATTRIBUTE13 = X_ATTRIBUTE13
477 , ATTRIBUTE14 = X_ATTRIBUTE14
478 , ATTRIBUTE15 = X_ATTRIBUTE15
479 where CHG_REQUEST_ID = X_CHG_REQUEST_ID
480 ;
481
482 if (sql%notfound) then
483 raise no_data_found;
484 end if;
485
486 open c;
487 fetch c into X_LAST_CHG_LOG_ID
488 , X_APPROVE_DATE
489 , X_IMPLEMENT_DATE;
490 if (c%notfound) then
491 close c;
492 raise no_data_found;
493 end if;
494 close c;
495
496 if ( Prev_Chg_Log_ID <> X_Last_Chg_Log_ID ) then
497 Start_WF_Process( X_Last_Chg_Log_ID );
498 end if;
499
500 end UPDATE_ROW;
501
502 FUNCTION Validate_Chg_Request_Num
503 ( X_K_HEADER_ID in NUMBER,
504 X_CHG_REQ_NUM in VARCHAR2,
505 X_CHG_REQ_ID in NUMBER
506 )RETURN VARCHAR2
507 is
508 Chg_Req_Num_Type varchar2(30);
509 Rec_Count NUMBER;
510 Chg_Req_Num_OK varchar2(1);
511
512 CURSOR c1
513 ( C_K_Header_ID NUMBER
514 , C_Chg_Request_Num VARCHAR2
515 , C_Chg_Request_ID NUMBER
516 ) IS
517 SELECT count(1)
518 FROM oke_chg_requests
519 WHERE K_Header_ID = C_K_Header_ID
520 AND Chg_Request_Num = C_Chg_Request_Num
521 AND ( C_Chg_Request_ID IS NULL
522 OR Chg_Request_ID <> C_Chg_Request_ID );
523
524
525 begin
526
527 Chg_Req_Num_OK := 'T';
528
529 select Manual_ChgReq_Num_type
530 into Chg_Req_Num_Type
531 from oke_number_options N,
532 oke_k_headers EH,
533 okc_k_headers_b CH
534 WHERE EH.K_Header_ID = X_K_HEADER_ID
535 AND CH.ID = EH.K_Header_ID
536 AND N.K_Type_Code = EH.K_Type_Code
537 AND N.Buy_Or_Sell = CH.Buy_Or_Sell;
538
539 --
540 -- If Number Type is numeric, check if entry is numeric
541 --
542 IF ( Chg_Req_Num_Type = 'NUMERIC'
543 AND OKE_NUMBER_SEQUENCES_PKG.Value_Is_Numeric
544 (X_CHG_REQ_NUM) = 'N' ) THEN
545 FND_MESSAGE.SET_NAME('OKE','OKE_NUMSEQ_INVALID_NUMERIC');
546 Chg_Req_Num_OK := 'F';
547 -- FND_MESSAGE.Error;
548 -- RAISE Form_Trigger_Failure;
549 END IF;
550
551 --
552 -- Check if entered number is unique for given document
553 --
554 OPEN c1 ( X_K_HEADER_ID,
555 X_CHG_REQ_NUM,
556 X_CHG_REQ_ID);
557 FETCH c1 INTO Rec_Count;
558 CLOSE c1;
559
560 IF Rec_Count > 0 THEN
561
562 FND_MESSAGE.SET_NAME('OKE', 'OKE_CHGREQ_DUP_NUMBER');
563 Chg_Req_Num_OK := 'F';
564 -- FND_MESSAGE.Error;
565 -- RAISE Form_Trigger_Failure;
566
567 END IF;
568
569 return Chg_Req_Num_OK;
570
571 end Validate_Chg_Request_Num;
572
573 FUNCTION Chg_Req_Num_Type
574 (X_K_HEADER_ID in NUMBER
575 )RETURN VARCHAR2 is
576 Num_Type varchar2(30);
577 BEGIN
578 select Manual_ChgReq_Num_Type
579 into Num_Type
580 from OKE_NUMBER_OPTIONS N,
581 OKE_K_HEADERS EH,
582 OKC_K_HEADERS_B CH
583 WHERE EH.K_HEADER_ID = X_K_HEADER_ID
584 AND CH.ID = EH.K_HEADER_ID
585 AND N.K_TYPE_CODE = EH.K_TYPE_CODE
586 AND N.BUY_OR_SELL = CH.BUY_OR_SELL;
587
588 return Num_Type;
589 END Chg_Req_Num_Type;
590
591 FUNCTION Chg_Req_Num_Mode
592 (X_K_HEADER_ID in NUMBER
593 )RETURN VARCHAR2 is
594 Num_Mode varchar2(30);
595 BEGIN
596 select ChgReq_Num_Mode
597 into Num_Mode
598 from OKE_NUMBER_OPTIONS N,
599 OKE_K_HEADERS EH,
600 OKC_K_HEADERS_B CH
601 WHERE EH.K_HEADER_ID = X_K_HEADER_ID
602 AND CH.ID = EH.K_HEADER_ID
603 AND N.K_TYPE_CODE = EH.K_TYPE_CODE
604 AND N.BUY_OR_SELL = CH.BUY_OR_SELL;
605
606 return Num_Mode;
607 END Chg_Req_Num_Mode;
608
609 end OKE_CHG_REQUESTS_PKG;
[Home] [Help]