[Home] [Help]
PACKAGE BODY: APPS.OKC_CHANGE_REQUEST_PVT
Source
1 package body OKC_CHANGE_REQUEST_PVT as
2 /* $Header: OKCCCRTB.pls 120.0 2005/05/26 09:37:55 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
7 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_CHANGE_REQUEST_PVT';
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
10 G_CRT_ON_APPROVAL CONSTANT varchar2(200) := 'OKC_IS_ON_APPROVAL';
11 G_WF_NAME_TOKEN CONSTANT varchar2(200) := 'WF_ITEM';
12 G_KEY_TOKEN CONSTANT varchar2(200) := 'WF_KEY';
13 G_CHILD_RECORD_FOUND CONSTANT varchar2(200) := 'OKC_CHILD_RECORD_FOUND';
14 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
15 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
16 G_DUPLICATE_CR CONSTANT varchar2(200) := 'OKC_DUPLICATE_CHANGE_REQUEST';
17 G_CR_TOKEN CONSTANT varchar2(200) := 'CR_NAME';
18
19 -- Start of comments
20 --
21 -- Procedure Name : add_language_change_request
22 -- Description :
23 -- Business Rules :
24 -- Parameters :
25 -- Version : 1.0
26 -- End of comments
27 procedure add_language_change_request is
28 begin
29 okc_crt_pvt.add_language;
30 end add_language_change_request;
31
32 -- Start of comments
33 --
34 -- Procedure Name : create_change_request
35 -- Description :
36 -- Business Rules :
37 -- Parameters :
38 -- Version : 1.0
39 -- End of comments
40 procedure create_change_request(p_api_version IN NUMBER,
41 p_init_msg_list IN VARCHAR2 ,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2,
45 p_crtv_rec IN crtv_rec_type,
46 x_crtv_rec OUT NOCOPY crtv_rec_type) is
47
48 -- Bug 3162918 Following cursor is used to check for duplicate CR name for a contract.
49
50 l_dummy varchar2(1);
51
52 cursor chk_dup_cr_csr is
53 select '!' from okc_change_requests_v
54 where chr_id = p_crtv_rec.chr_id
55 and name = p_crtv_rec.name
56 and datetime_applied is null;
57
58 -- added for Bug 3162918
59
60 begin
61
62 -- added for Bug 3162918
63 l_dummy := '?' ;
64 open chk_dup_cr_csr;
65 fetch chk_dup_cr_csr into l_dummy;
66 close chk_dup_cr_csr;
67 If (l_dummy = '!') then
68 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
69 p_msg_name => G_DUPLICATE_CR,
70 p_token1 => G_CR_TOKEN,
71 p_token1_value => p_crtv_rec.name
72 );
73 x_return_status := OKC_API.G_RET_STS_ERROR;
74 return;
75 End If;
76 -- added for Bug 3162918
77
78 okc_crt_pvt.insert_row(p_api_version => p_api_version,
79 p_init_msg_list => p_init_msg_list,
80 x_msg_count => x_msg_count,
81 x_msg_data => x_msg_data,
82 x_return_status => x_return_status,
83 p_crtv_rec => p_crtv_rec,
84 x_crtv_rec => x_crtv_rec);
85 end create_change_request;
86
87 -- Start of comments
88 --
89 -- Procedure Name : update_change_request
90 -- Description :
91 -- Business Rules :
92 -- Parameters :
93 -- Version : 1.0
94 -- End of comments
95 procedure update_change_request(p_api_version IN NUMBER,
96 p_init_msg_list IN VARCHAR2 ,
97 x_return_status OUT NOCOPY VARCHAR2,
98 x_msg_count OUT NOCOPY NUMBER,
99 x_msg_data OUT NOCOPY VARCHAR2,
100 p_crtv_rec IN crtv_rec_type,
101 x_crtv_rec OUT NOCOPY crtv_rec_type) is
102 begin
103 okc_crt_pvt.update_row(p_api_version => p_api_version,
104 p_init_msg_list => p_init_msg_list,
105 x_msg_count => x_msg_count,
106 x_msg_data => x_msg_data,
107 x_return_status => x_return_status,
108 p_crtv_rec => p_crtv_rec,
109 x_crtv_rec => x_crtv_rec);
110 end update_change_request;
111
112 -- Start of comments
113 --
114 -- Procedure Name : delete_change_request
115 -- Description :
116 -- Business Rules :
117 -- Parameters :
118 -- Version : 1.0
119 -- End of comments
120 procedure delete_change_request(p_api_version IN NUMBER,
121 p_init_msg_list IN VARCHAR2 ,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_msg_count OUT NOCOPY NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 p_crtv_rec IN crtv_rec_type) is
126 l_dummy varchar2(1);
127 cursor l1_csr is
128 select '!'
129 from OKC_CHANGES_B
130 where crt_id = p_crtv_rec.id;
131 cursor l2_csr is
132 select '!'
133 from OKC_CHANGE_PARTY_ROLE
134 where crt_id = p_crtv_rec.id;
135 begin
136 --
137 l_dummy := '?';
138 open l1_csr;
139 fetch l1_csr into l_dummy;
140 close l1_csr;
141 if (l_dummy = '!') then
142 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
143 p_msg_name => G_CHILD_RECORD_FOUND,
144 p_token1 => G_PARENT_TABLE_TOKEN,
145 p_token1_value => 'OKC_CHANGE_REQUESTS_V',
146 p_token2 => G_CHILD_TABLE_TOKEN,
147 p_token2_value => 'OKC_CHANGES_V');
148 x_return_status := OKC_API.G_RET_STS_ERROR;
149 return;
150 end if;
151 --
152 l_dummy := '?';
153 open l2_csr;
154 fetch l2_csr into l_dummy;
155 close l2_csr;
156 if (l_dummy = '!') then
157 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
158 p_msg_name => G_CHILD_RECORD_FOUND,
159 p_token1 => G_PARENT_TABLE_TOKEN,
160 p_token1_value => 'OKC_CHANGE_REQUESTS_V',
161 p_token2 => G_CHILD_TABLE_TOKEN,
162 p_token2_value => 'OKC_CHANGE_PARTY_ROLE_V');
163 x_return_status := OKC_API.G_RET_STS_ERROR;
164 return;
165 end if;
166 --
167 okc_crt_pvt.delete_row(p_api_version => p_api_version,
168 p_init_msg_list => p_init_msg_list,
169 x_msg_count => x_msg_count,
170 x_msg_data => x_msg_data,
171 x_return_status => x_return_status,
172 p_crtv_rec => p_crtv_rec);
173 end delete_change_request;
174
175 -- Start of comments
176 --
177 -- Procedure Name : lock_change_request
178 -- Description :
179 -- Business Rules :
180 -- Parameters :
181 -- Version : 1.0
182 -- End of comments
183 procedure lock_change_request(p_api_version IN NUMBER,
184 p_init_msg_list IN VARCHAR2 ,
185 x_return_status OUT NOCOPY VARCHAR2,
186 x_msg_count OUT NOCOPY NUMBER,
187 x_msg_data OUT NOCOPY VARCHAR2,
188 p_restricted IN VARCHAR2 ,
189 p_crtv_rec IN crtv_rec_type) is
190 --
191 l_key varchar2(240);
192 l_wf_name_active varchar2(150);
193 --
194 l_crtv_rec OKC_CHANGE_REQUEST_PUB.crtv_rec_type;
195
196 Cursor cur_crt_details is
197 select id,name,chr_id,crs_code,datetime_applied,object_version_number
198 from okc_change_requests_v
199 where id = p_crtv_rec.id;
200
201 cursor key_csr is
202 select substr(CONTRACT_NUMBER||CONTRACT_NUMBER_MODIFIER||l_crtv_rec.NAME,1,240) key
203 from OKC_K_HDR_AGREEDS_V
204 where ID = l_crtv_rec.chr_id;
205 --
206 cursor approval_active_csr is
207 select item_type
208 from WF_ITEMS
209 where item_type in
210 ( select wf_name
211 from OKC_PROCESS_DEFS_B
212 where USAGE='CHG_REQ_APPROVE' and PDF_TYPE='WPS')
213 and item_key = l_key
214 and end_date is NULL;
215 --
216 begin
217 open cur_crt_details;
218 fetch cur_crt_details into l_crtv_rec.id,l_crtv_rec.name,l_crtv_rec.chr_id,l_crtv_rec.crs_code,l_crtv_rec.datetime_applied,l_crtv_rec.object_version_number;
219 close cur_crt_details;
220 if (l_crtv_rec.crs_code='APP') then
221 if (p_restricted = OKC_API.G_TRUE
222 or l_crtv_rec.datetime_applied is not NULL) then
223 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
224 x_return_status := OKC_API.G_RET_STS_ERROR;
225 return;
226 end if;
227 end if;
228 open key_csr;
229 fetch key_csr into L_KEY;
230 close key_csr;
231 open approval_active_csr;
232 fetch approval_active_csr into l_wf_name_active;
233 close approval_active_csr;
234 if (L_WF_NAME_ACTIVE is not NULL) then
235 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
236 p_msg_name => G_CRT_ON_APPROVAL,
237 p_token1 => G_WF_NAME_TOKEN,
238 p_token1_value => L_WF_NAME_ACTIVE,
239 p_token2 => G_KEY_TOKEN,
240 p_token2_value => L_KEY);
241 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
242 x_return_status := OKC_API.G_RET_STS_ERROR;
243 return;
244 end if;
245 okc_crt_pvt.lock_row(p_api_version => p_api_version,
246 p_init_msg_list => p_init_msg_list,
247 x_msg_count => x_msg_count,
248 x_msg_data => x_msg_data,
249 x_return_status => x_return_status,
250 p_crtv_rec => l_crtv_rec);
251 end lock_change_request;
252
253 -- Start of comments
254 --
255 -- Procedure Name : validate_change_request
256 -- Description :
257 -- Business Rules :
258 -- Parameters :
259 -- Version : 1.0
260 -- End of comments
261 procedure validate_change_request(p_api_version IN NUMBER,
262 p_init_msg_list IN VARCHAR2 ,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2,
266 p_crtv_rec IN crtv_rec_type) is
267 begin
268 okc_crt_pvt.validate_row(p_api_version => p_api_version,
269 p_init_msg_list => p_init_msg_list,
270 x_msg_count => x_msg_count,
271 x_msg_data => x_msg_data,
272 x_return_status => x_return_status,
273 p_crtv_rec => p_crtv_rec);
274 end validate_change_request;
275
276 -- Start of comments
277 --
278 -- Procedure Name : add_language_change
279 -- Description :
280 -- Business Rules :
281 -- Parameters :
282 -- Version : 1.0
283 -- End of comments
284 procedure add_language_change is
285 begin
286 okc_cor_pvt.add_language;
287 end add_language_change;
288
289 -- Start of comments
290 --
291 -- Procedure Name : create_change
292 -- Description :
293 -- Business Rules :
294 -- Parameters :
295 -- Version : 1.0
296 -- End of comments
297 procedure create_change(p_api_version IN NUMBER,
298 p_init_msg_list IN VARCHAR2 ,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2,
302 p_corv_rec IN corv_rec_type,
303 x_corv_rec OUT NOCOPY corv_rec_type) is
304 begin
305 okc_cor_pvt.insert_row(p_api_version => p_api_version,
306 p_init_msg_list => p_init_msg_list,
307 x_msg_count => x_msg_count,
308 x_msg_data => x_msg_data,
309 x_return_status => x_return_status,
310 p_corv_rec => p_corv_rec,
311 x_corv_rec => x_corv_rec);
312 end create_change;
313
314 -- Start of comments
315 --
316 -- Procedure Name : update_change
317 -- Description :
318 -- Business Rules :
319 -- Parameters :
320 -- Version : 1.0
321 -- End of comments
322 procedure update_change(p_api_version IN NUMBER,
323 p_init_msg_list IN VARCHAR2 ,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2,
327 p_corv_rec IN corv_rec_type,
328 x_corv_rec OUT NOCOPY corv_rec_type) is
329 begin
330 okc_cor_pvt.update_row(p_api_version => p_api_version,
331 p_init_msg_list => p_init_msg_list,
332 x_msg_count => x_msg_count,
333 x_msg_data => x_msg_data,
334 x_return_status => x_return_status,
335 p_corv_rec => p_corv_rec,
336 x_corv_rec => x_corv_rec);
337 end update_change;
338
339 -- Start of comments
340 --
341 -- Procedure Name : delete_change
342 -- Description :
343 -- Business Rules :
344 -- Parameters :
345 -- Version : 1.0
346 -- End of comments
347 procedure delete_change(p_api_version IN NUMBER,
348 p_init_msg_list IN VARCHAR2 ,
349 x_return_status OUT NOCOPY VARCHAR2,
350 x_msg_count OUT NOCOPY NUMBER,
351 x_msg_data OUT NOCOPY VARCHAR2,
352 p_corv_rec IN corv_rec_type) is
353 begin
354 okc_cor_pvt.delete_row(p_api_version => p_api_version,
355 p_init_msg_list => p_init_msg_list,
356 x_msg_count => x_msg_count,
357 x_msg_data => x_msg_data,
358 x_return_status => x_return_status,
359 p_corv_rec => p_corv_rec);
360 end delete_change;
361
362 -- Start of comments
363 --
364 -- Procedure Name : lock_change
365 -- Description :
366 -- Business Rules :
367 -- Parameters :
368 -- Version : 1.0
369 -- End of comments
370 procedure lock_change(p_api_version IN NUMBER,
371 p_init_msg_list IN VARCHAR2 ,
372 x_return_status OUT NOCOPY VARCHAR2,
373 x_msg_count OUT NOCOPY NUMBER,
374 x_msg_data OUT NOCOPY VARCHAR2,
375 p_corv_rec IN corv_rec_type) is
376 --
377 l_status varchar2(20);
378 l_key varchar2(240);
379 l_wf_name_active varchar2(150);
380 --
381 cursor key_csr is
382 select C.crs_code,
383 substr(K.CONTRACT_NUMBER||K.CONTRACT_NUMBER_MODIFIER||C.NAME,1,240) key
384 from
385 OKC_CHANGE_REQUESTS_V C,
386 OKC_K_HDR_AGREEDS_V K
387 where C.ID = p_corv_rec.CRT_ID
388 and K.ID = C.chr_id;
389 --
390 cursor approval_active_csr is
391 select item_type
392 from WF_ITEMS
393 where item_type in
394 ( select wf_name
395 from OKC_PROCESS_DEFS_B
396 where USAGE='CHG_REQ_APPROVE' and PDF_TYPE='WPS')
397 and item_key = l_key
398 and end_date is NULL;
399 --
400 begin
401 open key_csr;
402 fetch key_csr into l_status, L_KEY;
403 close key_csr;
404 if (l_status='APP') then
405 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
406 x_return_status := OKC_API.G_RET_STS_ERROR;
407 return;
408 end if;
409 open approval_active_csr;
410 fetch approval_active_csr into l_wf_name_active;
411 close approval_active_csr;
412 if (L_WF_NAME_ACTIVE is not NULL) then
413 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
414 p_msg_name => G_CRT_ON_APPROVAL,
415 p_token1 => G_WF_NAME_TOKEN,
416 p_token1_value => L_WF_NAME_ACTIVE,
417 p_token2 => G_KEY_TOKEN,
418 p_token2_value => L_KEY);
419 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
420 x_return_status := OKC_API.G_RET_STS_ERROR;
421 return;
422 end if;
423 okc_cor_pvt.lock_row(p_api_version => p_api_version,
424 p_init_msg_list => p_init_msg_list,
425 x_msg_count => x_msg_count,
426 x_msg_data => x_msg_data,
427 x_return_status => x_return_status,
428 p_corv_rec => p_corv_rec);
429 end lock_change;
430
431 -- Start of comments
432 --
433 -- Procedure Name : validate_change
434 -- Description :
435 -- Business Rules :
436 -- Parameters :
437 -- Version : 1.0
438 -- End of comments
439 procedure validate_change(p_api_version IN NUMBER,
440 p_init_msg_list IN VARCHAR2 ,
441 x_return_status OUT NOCOPY VARCHAR2,
442 x_msg_count OUT NOCOPY NUMBER,
443 x_msg_data OUT NOCOPY VARCHAR2,
444 p_corv_rec IN corv_rec_type) is
445 begin
446 okc_cor_pvt.validate_row(p_api_version => p_api_version,
447 p_init_msg_list => p_init_msg_list,
448 x_msg_count => x_msg_count,
449 x_msg_data => x_msg_data,
450 x_return_status => x_return_status,
451 p_corv_rec => p_corv_rec);
452 end validate_change;
453
454 -- Start of comments
455 --
456 -- Procedure Name : create_change_party_role
457 -- Description :
458 -- Business Rules :
459 -- Parameters :
460 -- Version : 1.0
461 -- End of comments
462 procedure create_change_party_role(p_api_version IN NUMBER,
463 p_init_msg_list IN VARCHAR2 ,
464 x_return_status OUT NOCOPY VARCHAR2,
465 x_msg_count OUT NOCOPY NUMBER,
466 x_msg_data OUT NOCOPY VARCHAR2,
467 p_cprv_rec IN cprv_rec_type,
468 x_cprv_rec OUT NOCOPY cprv_rec_type) is
469 begin
470 okc_cpr_pvt.insert_row(p_api_version => p_api_version,
471 p_init_msg_list => p_init_msg_list,
472 x_msg_count => x_msg_count,
473 x_msg_data => x_msg_data,
474 x_return_status => x_return_status,
475 p_cprv_rec => p_cprv_rec,
476 x_cprv_rec => x_cprv_rec);
477 end create_change_party_role;
478
479 -- Start of comments
480 --
481 -- Procedure Name : update_change_party_role
482 -- Description :
483 -- Business Rules :
484 -- Parameters :
485 -- Version : 1.0
486 -- End of comments
487 procedure update_change_party_role(p_api_version IN NUMBER,
488 p_init_msg_list IN VARCHAR2 ,
489 x_return_status OUT NOCOPY VARCHAR2,
490 x_msg_count OUT NOCOPY NUMBER,
491 x_msg_data OUT NOCOPY VARCHAR2,
492 p_cprv_rec IN cprv_rec_type,
493 x_cprv_rec OUT NOCOPY cprv_rec_type) is
494 begin
495 okc_cpr_pvt.update_row(p_api_version => p_api_version,
496 p_init_msg_list => p_init_msg_list,
497 x_msg_count => x_msg_count,
498 x_msg_data => x_msg_data,
499 x_return_status => x_return_status,
500 p_cprv_rec => p_cprv_rec,
501 x_cprv_rec => x_cprv_rec);
502 end update_change_party_role;
503
504 -- Start of comments
505 --
506 -- Procedure Name : delete_change_party_role
507 -- Description :
508 -- Business Rules :
509 -- Parameters :
510 -- Version : 1.0
511 -- End of comments
512 procedure delete_change_party_role(p_api_version IN NUMBER,
513 p_init_msg_list IN VARCHAR2 ,
514 x_return_status OUT NOCOPY VARCHAR2,
515 x_msg_count OUT NOCOPY NUMBER,
516 x_msg_data OUT NOCOPY VARCHAR2,
517 p_cprv_rec IN cprv_rec_type) is
518 begin
519 okc_cpr_pvt.delete_row(p_api_version => p_api_version,
520 p_init_msg_list => p_init_msg_list,
521 x_msg_count => x_msg_count,
522 x_msg_data => x_msg_data,
523 x_return_status => x_return_status,
524 p_cprv_rec => p_cprv_rec);
525 end delete_change_party_role;
526
527 -- Start of comments
528 --
529 -- Procedure Name : lock_change_party_role
530 -- Description :
531 -- Business Rules :
532 -- Parameters :
533 -- Version : 1.0
534 -- End of comments
535 procedure lock_change_party_role(p_api_version IN NUMBER,
536 p_init_msg_list IN VARCHAR2 ,
537 x_return_status OUT NOCOPY VARCHAR2,
538 x_msg_count OUT NOCOPY NUMBER,
539 x_msg_data OUT NOCOPY VARCHAR2,
540 p_cprv_rec IN cprv_rec_type) is
541 begin
542 okc_cpr_pvt.lock_row(p_api_version => p_api_version,
543 p_init_msg_list => p_init_msg_list,
544 x_msg_count => x_msg_count,
545 x_msg_data => x_msg_data,
546 x_return_status => x_return_status,
547 p_cprv_rec => p_cprv_rec);
548 end lock_change_party_role;
549
550 -- Start of comments
551 --
552 -- Procedure Name : validate_change_party_role
553 -- Description :
554 -- Business Rules :
555 -- Parameters :
556 -- Version : 1.0
557 -- End of comments
558 procedure validate_change_party_role(p_api_version IN NUMBER,
559 p_init_msg_list IN VARCHAR2 ,
560 x_return_status OUT NOCOPY VARCHAR2,
561 x_msg_count OUT NOCOPY NUMBER,
562 x_msg_data OUT NOCOPY VARCHAR2,
563 p_cprv_rec IN cprv_rec_type) is
564 begin
565 okc_cpr_pvt.validate_row(p_api_version => p_api_version,
566 p_init_msg_list => p_init_msg_list,
567 x_msg_count => x_msg_count,
568 x_msg_data => x_msg_data,
569 x_return_status => x_return_status,
570 p_cprv_rec => p_cprv_rec);
571 end validate_change_party_role;
572
573 end OKC_CHANGE_REQUEST_PVT;