1 PACKAGE BODY okl_vp_change_request_pvt AS
2 /* $Header: OKLRVCRB.pls 120.4 2005/09/21 07:19:11 sjalasut noship $ */
3
4 -- Global Message Constants
5 G_PENDING_CHANGE_REQ_EXISTS CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_PEND_CREQ_EXIST'; -- token AGR_NUMBER
6 G_NOT_NEW_PASS_CR CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_NOT_NEW_PASS_CR'; -- token CHANGE_REQ_NUM
7 G_NO_AGR_COPY CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_NO_AGR_COPY_FOUND'; -- token CHANGE_REQ_NUM
8 G_CR_PARMS_MISSING CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_CR_PARAM_MISSING'; -- no token
9 G_NO_PARAM_STS_CODE CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_CR_NO_STS_PARAM'; -- no token
10 G_INVALID_STS_INCOMP CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_INVAL_STS_INCOMP'; -- token CHANGE_REQ_NUM
11 G_INVALID_STS_PENDING CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_INVAL_STS_PENDING'; -- token CHANGE_REQ_NUM
12 G_INVALID_STS_APPROVED CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_INVAL_STS_APPROVED'; -- token CHANGE_REQ_NUM
13 G_NOT_ACTIVE_AGREEMENT CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_NOT_ACTIVE_AGREEMENT'; -- token AGR_NUMBER
14 G_INVALID_STS_PASSED CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_INVAL_STS_PASSED'; -- token CHANGE_REQ_NUM
15 G_ONE_REASON_REQD CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_ONE_CR_REASON_REQD';
16 G_SYSTEM_COPY_MSG fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_CR_SYSTEM_COPY';
17
18 G_NO_ROWS_SELECTED CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_LLA_NO_ROW_SELECTED';
19 -- Global lookup_code constants
20 G_ARGREEMENT_TYPE_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'AGREEMENT';
21 G_ASSOCIATE_TYPE_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'ASSOCIATION';
22
23 G_ABANDONED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'ABANDONED';
24 G_APPROVED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'APPROVED';
25 G_COMPLETED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'COMPLETED';
26 G_INCOMPLETE_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'INCOMPLETE';
27 G_NEW_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'NEW';
28 G_PENDING_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'PENDING_APPROVAL';
29 G_REJECTED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'REJECTED';
30 G_PASSED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'PASSED';
31 G_ACTIVE_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'ACTIVE';
32 G_SYSTEM_REASON_CD CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'SYSTEM';
33
34 -- local procedures/functions. START
35
36 FUNCTION generate_change_req_num RETURN okl_vp_change_requests.change_request_number%TYPE IS
37 -- cursor to fetch the next available change request number
38 CURSOR c_get_next_cr_num IS
39 SELECT okl_vp_change_req_num_seq.NEXTVAL
40 FROM dual;
41 lv_new_change_request_number okl_vp_change_requests.change_request_number%TYPE;
42 BEGIN
43 OPEN c_get_next_cr_num; FETCH c_get_next_cr_num INTO lv_new_change_request_number;
44 CLOSE c_get_next_cr_num;
45 RETURN lv_new_change_request_number;
46 END generate_change_req_num;
47
48 PROCEDURE historize_agreement(p_api_version IN NUMBER
49 ,p_init_msg_list IN VARCHAR2
50 ,x_return_status OUT NOCOPY VARCHAR2
51 ,x_msg_count OUT NOCOPY NUMBER
52 ,x_msg_data OUT NOCOPY VARCHAR2
53 ,p_vcrv_rec IN vcrv_rec_type
54 ,p_agreement_number IN okc_k_headers_b.contract_number%TYPE
55 ,x_vcrv_rec OUT NOCOPY vcrv_rec_type
56 ) IS
57 lv_vcrv_rec vcrv_rec_type;
58 lv_calc_agr_number VARCHAR2(100);
59 lv_vagr_hdr_rec okl_vp_copy_contract_pvt.copy_header_rec_type;
60 lx_new_agr_id okc_k_headers_b.id%TYPE;
61 lv_khrv_rec okl_khr_pvt.khrv_rec_type;
62 x_khrv_rec okl_khr_pvt.khrv_rec_type;
63 lv_vrrv_rec okl_vrr_pvt.vrrv_rec_type;
64 x_vrrv_rec okl_vrr_pvt.vrrv_rec_type;
65
66 BEGIN
67 -- get the change request number from the sequence, this call is necessary as change request number is used
68 -- in the construction of the new agreement number
69 lv_vcrv_rec.change_request_number := generate_change_req_num;
70
71 lv_calc_agr_number := TRIM(RPAD(SUBSTR(LPAD((p_agreement_number || lv_vcrv_rec.change_request_number),120,' ') ,
72 length(LPAD((p_agreement_number || lv_vcrv_rec.change_request_number),120,' ')) - 120),120, ' '));
73 -- construct the record to call copy vendor program api
74 lv_vagr_hdr_rec := NULL;
75 lv_vagr_hdr_rec.p_id := p_vcrv_rec.chr_id; -- this is the original agreement id whose change request has been requested
76 lv_vagr_hdr_rec.p_to_agreement_number := lv_calc_agr_number;
77 lv_vagr_hdr_rec.p_template_yn := 'N';
78 -- create a new agreement from the existing agreement, this new agreement is created for the first time for tracking history
79 okl_vp_copy_contract_pub.copy_contract(p_api_version => p_api_version
80 ,p_init_msg_list => p_init_msg_list
81 ,x_return_status => x_return_status
82 ,x_msg_count => x_msg_count
83 ,x_msg_data => x_msg_data
84 ,p_copy_rec => lv_vagr_hdr_rec
85 ,x_new_contract_id => lx_new_agr_id
86 );
87 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
88 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
89 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
90 RAISE OKL_API.G_EXCEPTION_ERROR;
91 END IF;
92 okl_contract_status_pub.update_contract_status(p_api_version => p_api_version
93 ,p_init_msg_list => p_init_msg_list
94 ,x_return_status => x_return_status
95 ,x_msg_count => x_msg_count
96 ,x_msg_data => x_msg_data
97 ,p_khr_status => G_ABANDONED_STS_CODE
98 ,p_chr_id => lx_new_agr_id -- this is the id of the backup copy agmnt
99 );
100 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
101 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
102 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
103 RAISE OKL_API.G_EXCEPTION_ERROR;
104 END IF;
105 -- construct other parameters for the change request. the chr_id of this change request is the parent agreement
106 -- the new agreement can be derived via the crs_id value in the okl_k_headers table
107 lv_vcrv_rec.chr_id := p_vcrv_rec.chr_id;
108 lv_vcrv_rec.change_type_code := p_vcrv_rec.change_type_code;
109 lv_vcrv_rec.status_code := G_COMPLETED_STS_CODE;
110 -- the request date will be the date on which the first change request has been initiated
111 lv_vcrv_rec.request_date := TRUNC(SYSDATE);
112
113 create_change_request_header(p_api_version => p_api_version
114 ,p_init_msg_list => p_init_msg_list
115 ,x_return_status => x_return_status
116 ,x_msg_count => x_msg_count
117 ,x_msg_data => x_msg_data
118 ,p_vcrv_rec => lv_vcrv_rec
119 ,x_vcrv_rec => x_vcrv_rec
120 );
121 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
122 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
123 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
124 RAISE OKL_API.G_EXCEPTION_ERROR;
125 END IF;
126
127 -- since this is a system back up copy of the agreement, we need to mark this agreement as a SYSTEM copy
128 -- to achieve this, we create a reason code on the backed up change request and put the notes as
129 -- 'System generated change request to maintain history'
130 -- populate the reason record
131 lv_vrrv_rec.crs_id := x_vcrv_rec.id;
132 lv_vrrv_rec.reason_code := G_SYSTEM_REASON_CD;
133 -- now also populate the notes for this reason, the note is derived from fnd_new_messages
134 fnd_message.set_name(G_APP_NAME, G_SYSTEM_COPY_MSG);
135 lv_vrrv_rec.note := fnd_message.get;
136
137 okl_vrr_pvt.insert_row(p_api_version => p_api_version
138 ,p_init_msg_list => p_init_msg_list
139 ,x_return_status => x_return_status
140 ,x_msg_count => x_msg_count
141 ,x_msg_data => x_msg_data
142 ,p_vrrv_rec => lv_vrrv_rec
143 ,x_vrrv_rec => x_vrrv_rec
144 );
145 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
146 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
147 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
148 RAISE OKL_API.G_EXCEPTION_ERROR;
149 END IF;
150
151 -- now that the agreement copy is created, set the change request id in the okl_k_headers to the created change request
152 lv_khrv_rec.id := lx_new_agr_id;
153 lv_khrv_rec.crs_id := x_vcrv_rec.id;
154 okl_khr_pvt.update_row(p_api_version => p_api_version
155 ,p_init_msg_list => p_init_msg_list
156 ,x_return_status => x_return_status
157 ,x_msg_count => x_msg_count
158 ,x_msg_data => x_msg_data
159 ,p_khrv_rec => lv_khrv_rec
160 ,x_khrv_rec => x_khrv_rec);
161 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
162 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
163 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
164 RAISE OKL_API.G_EXCEPTION_ERROR;
165 END IF;
166
167 END historize_agreement;
168
169 -- local procedures/functions. END
170
171 PROCEDURE create_change_request_header(p_api_version IN NUMBER
172 ,p_init_msg_list IN VARCHAR2
173 ,x_return_status OUT NOCOPY VARCHAR2
174 ,x_msg_count OUT NOCOPY NUMBER
175 ,x_msg_data OUT NOCOPY VARCHAR2
176 ,p_vcrv_rec IN vcrv_rec_type
177 ,x_vcrv_rec OUT NOCOPY vcrv_rec_type
178 ) IS
179 l_api_version CONSTANT NUMBER DEFAULT 1.0;
180 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CREATE_CHANGE_REQUEST_HDR';
181 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CHANGE_REQUEST_PVT.CREATE_CHANGE_REQUEST_HEADER';
182 l_debug_enabled VARCHAR2(10);
183
184 BEGIN
185 x_return_status := OKL_API.G_RET_STS_SUCCESS;
186
187 l_debug_enabled := okl_debug_pub.check_log_enabled;
188
189 IF(l_debug_enabled='Y') THEN
190 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVCRB.pls call create_change_request_header');
191 END IF;
192
193 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
194 x_return_status := OKL_API.START_ACTIVITY(
195 p_api_name => l_api_name
196 ,p_pkg_name => G_PKG_NAME
197 ,p_init_msg_list => p_init_msg_list
198 ,l_api_version => l_api_version
199 ,p_api_version => p_api_version
200 ,p_api_type => g_api_type
201 ,x_return_status => x_return_status);
202 -- check if activity started successfully
203 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
204 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
205 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
206 RAISE OKL_API.G_EXCEPTION_ERROR;
207 END IF;
208
209 okl_vcr_pvt.insert_row(p_api_version => p_api_version
210 ,p_init_msg_list => p_init_msg_list
211 ,x_return_status => x_return_status
212 ,x_msg_count => x_msg_count
213 ,x_msg_data => x_msg_data
214 ,p_vcrv_rec => p_vcrv_rec
215 ,x_vcrv_rec => x_vcrv_rec
216 );
217 -- write to log
218 IF(NVL(l_debug_enabled,'N')='Y') THEN
219 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
220 l_module,
221 'okl_vcr_pvt.insert_row returned with status '||x_return_status||' x_msg_data '||x_msg_data||' id '||x_vcrv_rec.id
222 );
223 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
224 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
225 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
226 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
227 RAISE OKL_API.G_EXCEPTION_ERROR;
228 END IF;
229
230 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
231
232 IF(l_debug_enabled='Y') THEN
233 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVCRB.pls call create_change_request_header');
234 END IF;
235 EXCEPTION
236 WHEN OKL_API.G_EXCEPTION_ERROR THEN
237 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
238 p_api_name => l_api_name,
239 p_pkg_name => G_PKG_NAME,
240 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
241 x_msg_count => x_msg_count,
242 x_msg_data => x_msg_data,
243 p_api_type => g_api_type);
244
245 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
246 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
247 p_api_name => l_api_name,
248 p_pkg_name => G_PKG_NAME,
249 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
250 x_msg_count => x_msg_count,
251 x_msg_data => x_msg_data,
252 p_api_type => g_api_type);
253
254 WHEN OTHERS THEN
255 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
256 p_api_name => l_api_name,
257 p_pkg_name => G_PKG_NAME,
258 p_exc_name => 'OTHERS',
259 x_msg_count => x_msg_count,
260 x_msg_data => x_msg_data,
261 p_api_type => g_api_type);
262
263 END create_change_request_header;
264
265 PROCEDURE update_change_request_header(p_api_version IN NUMBER
266 ,p_init_msg_list IN VARCHAR2
267 ,x_return_status OUT NOCOPY VARCHAR2
268 ,x_msg_count OUT NOCOPY NUMBER
269 ,x_msg_data OUT NOCOPY VARCHAR2
270 ,p_vcrv_rec IN vcrv_rec_type
271 ,x_vcrv_rec OUT NOCOPY vcrv_rec_type
272 ) IS
273
274 CURSOR c_get_chr_id (cp_change_request_id okl_vp_change_requests.id%TYPE)IS
275 SELECT chr_id
276 FROM okl_vp_change_requests
277 WHERE id = cp_change_request_id;
278 lv_chr_id okc_k_headers_b.id%TYPE;
279
280 lv_vcrv_rec okl_vcr_pvt.vcrv_rec_type;
281
282 l_api_version CONSTANT NUMBER DEFAULT 1.0;
283 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'UPDATE_CHANGE_REQUEST_HDR';
284 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CHANGE_REQUEST_PVT.UPDATE_CHANGE_REQUEST_HEADER';
285 l_debug_enabled VARCHAR2(10);
286
287 BEGIN
288 x_return_status := OKL_API.G_RET_STS_SUCCESS;
289
290 l_debug_enabled := okl_debug_pub.check_log_enabled;
291
292 IF(l_debug_enabled='Y') THEN
293 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVCRB.pls call update_change_request_header');
294 END IF;
295
296 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
297 x_return_status := OKL_API.START_ACTIVITY(
298 p_api_name => l_api_name
299 ,p_pkg_name => G_PKG_NAME
300 ,p_init_msg_list => p_init_msg_list
301 ,l_api_version => l_api_version
302 ,p_api_version => p_api_version
303 ,p_api_type => g_api_type
304 ,x_return_status => x_return_status);
305 -- check if activity started successfully
306 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
307 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
308 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
309 RAISE OKL_API.G_EXCEPTION_ERROR;
310 END IF;
311
312 lv_vcrv_rec := p_vcrv_rec;
313 -- get the chr_id if not passed
314 IF(lv_vcrv_rec.chr_id IS NULL)THEN
315 OPEN c_get_chr_id(lv_vcrv_rec.id); FETCH c_get_chr_id INTO lv_chr_id;
316 CLOSE c_get_chr_id;
317 lv_vcrv_rec.chr_id := lv_chr_id;
318 END IF;
319 okl_vcr_pvt.update_row(p_api_version => p_api_version
320 ,p_init_msg_list => p_init_msg_list
321 ,x_return_status => x_return_status
322 ,x_msg_count => x_msg_count
323 ,x_msg_data => x_msg_data
324 ,p_vcrv_rec => lv_vcrv_rec
325 ,x_vcrv_rec => x_vcrv_rec
326 );
327 -- write to log
328 IF(NVL(l_debug_enabled,'N')='Y') THEN
329 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
330 l_module,
331 'okl_vcr_pvt.update_row returned with status '||x_return_status||' x_msg_data '||x_msg_data||' id '||x_vcrv_rec.id
332 );
333 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
334
335 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
336 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
337 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
338 RAISE OKL_API.G_EXCEPTION_ERROR;
339 END IF;
340
341 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
342
343 IF(l_debug_enabled='Y') THEN
344 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVCRB.pls call update_change_request_header');
345 END IF;
346 EXCEPTION
347 WHEN OKL_API.G_EXCEPTION_ERROR THEN
348 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
349 p_api_name => l_api_name,
350 p_pkg_name => G_PKG_NAME,
351 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
352 x_msg_count => x_msg_count,
353 x_msg_data => x_msg_data,
354 p_api_type => g_api_type);
355
356 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
357 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
358 p_api_name => l_api_name,
359 p_pkg_name => G_PKG_NAME,
360 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
361 x_msg_count => x_msg_count,
362 x_msg_data => x_msg_data,
363 p_api_type => g_api_type);
364
365 WHEN OTHERS THEN
366 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
367 p_api_name => l_api_name,
368 p_pkg_name => G_PKG_NAME,
369 p_exc_name => 'OTHERS',
370 x_msg_count => x_msg_count,
371 x_msg_data => x_msg_data,
372 p_api_type => g_api_type);
373 END update_change_request_header;
374
375 PROCEDURE create_change_request_lines(p_api_version IN NUMBER
376 ,p_init_msg_list IN VARCHAR2
377 ,x_return_status OUT NOCOPY VARCHAR2
378 ,x_msg_count OUT NOCOPY NUMBER
379 ,x_msg_data OUT NOCOPY VARCHAR2
380 ,p_vrrv_tbl IN vrrv_tbl_type
381 ,x_vrrv_tbl OUT NOCOPY vrrv_tbl_type
382 ,x_request_status OUT NOCOPY okl_vp_change_requests.status_code%TYPE
383 ) IS
384 lv_crs_id okl_vp_change_requests.id%TYPE;
385 l_api_version CONSTANT NUMBER DEFAULT 1.0;
386 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CREATE_CHANGE_REQUEST_LNS';
387 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CHANGE_REQUEST_PVT.CREATE_CHANGE_REQUEST_LINES';
388 l_debug_enabled VARCHAR2(10);
389
390 BEGIN
391 x_return_status := OKL_API.G_RET_STS_SUCCESS;
392
393 l_debug_enabled := okl_debug_pub.check_log_enabled;
394
395 IF(l_debug_enabled='Y') THEN
396 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVCRB.pls call create_change_request_lines');
397 END IF;
398
399 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
400 x_return_status := OKL_API.START_ACTIVITY(
401 p_api_name => l_api_name
402 ,p_pkg_name => G_PKG_NAME
403 ,p_init_msg_list => p_init_msg_list
404 ,l_api_version => l_api_version
405 ,p_api_version => p_api_version
406 ,p_api_type => g_api_type
407 ,x_return_status => x_return_status);
408 -- check if activity started successfully
409 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
410 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
411 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
412 RAISE OKL_API.G_EXCEPTION_ERROR;
413 END IF;
414
415 okl_vrr_pvt.insert_row(p_api_version => p_api_version
416 ,p_init_msg_list => p_init_msg_list
417 ,x_return_status => x_return_status
418 ,x_msg_count => x_msg_count
419 ,x_msg_data => x_msg_data
420 ,p_vrrv_tbl => p_vrrv_tbl
421 ,x_vrrv_tbl => x_vrrv_tbl
422 );
423 -- write to log
424 IF(NVL(l_debug_enabled,'N')='Y') THEN
425 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
426 l_module,
427 'okl_vrr_pvt.insert_row returned with status '||x_return_status||' x_msg_data '||x_msg_data
428 );
429 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
430
431 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
432 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
433 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
434 RAISE OKL_API.G_EXCEPTION_ERROR;
435 END IF;
436
437 -- get the master change request id and re-set the status of the change request if needed
438 -- this scenario could be possible if the user adds more reasons once the change request is validated successfully.
439 lv_crs_id := p_vrrv_tbl(p_vrrv_tbl.FIRST).crs_id;
440 IF(p_vrrv_tbl.COUNT > 0 AND lv_crs_id IS NOT NULL AND lv_crs_id <> OKL_API.G_MISS_NUM)THEN
441 cascade_request_status_edit(p_api_version => p_api_version
442 ,p_init_msg_list => p_init_msg_list
443 ,x_return_status => x_return_status
444 ,x_msg_count => x_msg_count
445 ,x_msg_data => x_msg_data
446 ,p_vp_crq_id => lv_crs_id
447 ,x_status_code => x_request_status
448 );
449 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
450 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
451 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
452 RAISE OKL_API.G_EXCEPTION_ERROR;
453 END IF;
454 END IF;
455
456 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
457
458 IF(l_debug_enabled='Y') THEN
459 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVCRB.pls call create_change_request_lines');
460 END IF;
461 EXCEPTION
462 WHEN OKL_API.G_EXCEPTION_ERROR THEN
463 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
464 p_api_name => l_api_name,
465 p_pkg_name => G_PKG_NAME,
466 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
467 x_msg_count => x_msg_count,
468 x_msg_data => x_msg_data,
469 p_api_type => g_api_type);
470
471 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
472 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
473 p_api_name => l_api_name,
474 p_pkg_name => G_PKG_NAME,
475 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
476 x_msg_count => x_msg_count,
477 x_msg_data => x_msg_data,
478 p_api_type => g_api_type);
479
480 WHEN OTHERS THEN
481 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
482 p_api_name => l_api_name,
483 p_pkg_name => G_PKG_NAME,
484 p_exc_name => 'OTHERS',
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data,
487 p_api_type => g_api_type);
488 END create_change_request_lines;
489
490 PROCEDURE update_change_request_lines(p_api_version IN NUMBER
491 ,p_init_msg_list IN VARCHAR2
492 ,x_return_status OUT NOCOPY VARCHAR2
493 ,x_msg_count OUT NOCOPY NUMBER
494 ,x_msg_data OUT NOCOPY VARCHAR2
495 ,p_vrrv_tbl IN vrrv_tbl_type
496 ,x_vrrv_tbl OUT NOCOPY vrrv_tbl_type
497 ,x_request_status OUT NOCOPY okl_vp_change_requests.status_code%TYPE
498 ) IS
499 lv_crs_id okl_vp_change_requests.id%TYPE;
500 l_api_version CONSTANT NUMBER DEFAULT 1.0;
501 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'UPDATE_CHANGE_REQUEST_LNS';
502 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CHANGE_REQUEST_PVT.UPDATE_CHANGE_REQUEST_LINES';
503 l_debug_enabled VARCHAR2(10);
504
505 BEGIN
506 x_return_status := OKL_API.G_RET_STS_SUCCESS;
507
508 l_debug_enabled := okl_debug_pub.check_log_enabled;
509
510 IF(l_debug_enabled='Y') THEN
511 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVCRB.pls call update_change_request_lines');
512 END IF;
513
514 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
515 x_return_status := OKL_API.START_ACTIVITY(
516 p_api_name => l_api_name
517 ,p_pkg_name => G_PKG_NAME
518 ,p_init_msg_list => p_init_msg_list
519 ,l_api_version => l_api_version
520 ,p_api_version => p_api_version
521 ,p_api_type => g_api_type
522 ,x_return_status => x_return_status);
523 -- check if activity started successfully
524 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
525 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
526 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
527 RAISE OKL_API.G_EXCEPTION_ERROR;
528 END IF;
529
530 okl_vrr_pvt.update_row(p_api_version => p_api_version
531 ,p_init_msg_list => p_init_msg_list
532 ,x_return_status => x_return_status
533 ,x_msg_count => x_msg_count
534 ,x_msg_data => x_msg_data
535 ,p_vrrv_tbl => p_vrrv_tbl
536 ,x_vrrv_tbl => x_vrrv_tbl
537 );
538 -- write to log
539 IF(NVL(l_debug_enabled,'N')='Y') THEN
540 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
541 l_module,
542 'okl_vrr_pvt.update_row returned with status '||x_return_status||' x_msg_data '||x_msg_data
543 );
544 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
545
546 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
547 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
548 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
549 RAISE OKL_API.G_EXCEPTION_ERROR;
550 END IF;
551
552 -- get the master change request id and re-set the status of the change request if needed
553 -- this scenario could be possible if the user updates one or more reasons once the change request is validated successfully.
554 lv_crs_id := p_vrrv_tbl(p_vrrv_tbl.FIRST).crs_id;
555 IF(p_vrrv_tbl.COUNT > 0 AND lv_crs_id IS NOT NULL AND lv_crs_id <> OKL_API.G_MISS_NUM)THEN
556 cascade_request_status_edit(p_api_version => p_api_version
557 ,p_init_msg_list => p_init_msg_list
558 ,x_return_status => x_return_status
559 ,x_msg_count => x_msg_count
560 ,x_msg_data => x_msg_data
561 ,p_vp_crq_id => lv_crs_id
562 ,x_status_code => x_request_status
563 );
564 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
565 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
566 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
567 RAISE OKL_API.G_EXCEPTION_ERROR;
568 END IF;
569 END IF;
570
571 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
572
573 IF(l_debug_enabled='Y') THEN
574 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVCRB.pls call update_change_request_lines');
575 END IF;
576 EXCEPTION
577 WHEN OKL_API.G_EXCEPTION_ERROR THEN
578 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
579 p_api_name => l_api_name,
580 p_pkg_name => G_PKG_NAME,
581 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
582 x_msg_count => x_msg_count,
583 x_msg_data => x_msg_data,
584 p_api_type => g_api_type);
585
586 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
587 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
588 p_api_name => l_api_name,
589 p_pkg_name => G_PKG_NAME,
590 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
591 x_msg_count => x_msg_count,
592 x_msg_data => x_msg_data,
593 p_api_type => g_api_type);
594
595 WHEN OTHERS THEN
596 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
597 p_api_name => l_api_name,
598 p_pkg_name => G_PKG_NAME,
599 p_exc_name => 'OTHERS',
600 x_msg_count => x_msg_count,
601 x_msg_data => x_msg_data,
602 p_api_type => g_api_type);
603 END update_change_request_lines;
604
605 PROCEDURE delete_change_request_lines(p_api_version IN NUMBER
606 ,p_init_msg_list IN VARCHAR2
607 ,x_return_status OUT NOCOPY VARCHAR2
608 ,x_msg_count OUT NOCOPY NUMBER
609 ,x_msg_data OUT NOCOPY VARCHAR2
610 ,p_vrrv_tbl IN vrrv_tbl_type
611 ,x_request_status OUT NOCOPY okl_vp_change_requests.status_code%TYPE
612 ) IS
613 CURSOR c_get_more_reasons_csr(cp_creq_id okl_vp_change_requests.id%TYPE)IS
614 SELECT 'X'
615 FROM okl_vp_cr_reasons
616 WHERE crs_id = cp_creq_id;
617 lv_dummy VARCHAR2(1);
618 lv_crs_id okl_vp_change_requests.id%TYPE;
619 l_api_version CONSTANT NUMBER DEFAULT 1.0;
620 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'DELETE_CHANGE_REQUEST_LNS';
621 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CHANGE_REQUEST_PVT.DELETE_CHANGE_REQUEST_LINES';
622 l_debug_enabled VARCHAR2(10);
623
624 BEGIN
625 x_return_status := OKL_API.G_RET_STS_SUCCESS;
626
627 l_debug_enabled := okl_debug_pub.check_log_enabled;
628
629 IF(l_debug_enabled='Y') THEN
630 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVCRB.pls call delete_change_request_lines');
631 END IF;
632
633 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
634 x_return_status := OKL_API.START_ACTIVITY(
635 p_api_name => l_api_name
636 ,p_pkg_name => G_PKG_NAME
637 ,p_init_msg_list => p_init_msg_list
638 ,l_api_version => l_api_version
639 ,p_api_version => p_api_version
640 ,p_api_type => g_api_type
641 ,x_return_status => x_return_status);
642 -- check if activity started successfully
643 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
644 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
645 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
646 RAISE OKL_API.G_EXCEPTION_ERROR;
647 END IF;
648 IF(p_vrrv_tbl.COUNT > 0)THEN
649 okl_vrr_pvt.delete_row(p_api_version => p_api_version
650 ,p_init_msg_list => p_init_msg_list
651 ,x_return_status => x_return_status
652 ,x_msg_count => x_msg_count
653 ,x_msg_data => x_msg_data
654 ,p_vrrv_tbl => p_vrrv_tbl
655 );
656 -- write to log
657 IF(NVL(l_debug_enabled,'N')='Y') THEN
658 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
659 l_module,
660 'okl_vrr_pvt.delete_row returned with status '||x_return_status||' x_msg_data '||x_msg_data
661 );
662 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
663 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
664 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
665 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
666 RAISE OKL_API.G_EXCEPTION_ERROR;
667 END IF;
668 -- now check if there are any change request reasons still for this change request. if there are no change request reasons
669 -- then raise an error. Atleast One Change Request Reason is mandatory for a Change Request
670 OPEN c_get_more_reasons_csr(p_vrrv_tbl(p_vrrv_tbl.FIRST).crs_id); FETCH c_get_more_reasons_csr INTO lv_dummy;
671 CLOSE c_get_more_reasons_csr;
672 IF(NVL(lv_dummy,'Y')<>'X')THEN
673 OKL_API.set_message(G_APP_NAME, G_ONE_REASON_REQD);
674 RAISE OKL_API.G_EXCEPTION_ERROR;
675 END IF;
676 -- get the master change request id and re-set the status of the change request if needed
677 -- this scenario could be possible if the user removes one or more reasons once the change request is validated successfully.
678 lv_crs_id := p_vrrv_tbl(p_vrrv_tbl.FIRST).crs_id;
679 IF(lv_crs_id IS NOT NULL AND lv_crs_id <> OKL_API.G_MISS_NUM)THEN
680 cascade_request_status_edit(p_api_version => p_api_version
681 ,p_init_msg_list => p_init_msg_list
682 ,x_return_status => x_return_status
683 ,x_msg_count => x_msg_count
684 ,x_msg_data => x_msg_data
685 ,p_vp_crq_id => lv_crs_id
686 ,x_status_code => x_request_status
687 );
688 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
689 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
690 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
691 RAISE OKL_API.G_EXCEPTION_ERROR;
692 END IF;
693 END IF;
694 ELSE
695 OKL_API.set_message(G_APP_NAME, G_NO_ROWS_SELECTED);
696 RAISE OKL_API.G_EXCEPTION_ERROR;
697 END IF;
698 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
699
700 IF(l_debug_enabled='Y') THEN
701 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVCRB.pls call delete_change_request_lines');
702 END IF;
703 EXCEPTION
704 WHEN OKL_API.G_EXCEPTION_ERROR THEN
705 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
706 p_api_name => l_api_name,
707 p_pkg_name => G_PKG_NAME,
708 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
709 x_msg_count => x_msg_count,
710 x_msg_data => x_msg_data,
711 p_api_type => g_api_type);
712
713 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
714 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
715 p_api_name => l_api_name,
716 p_pkg_name => G_PKG_NAME,
717 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
718 x_msg_count => x_msg_count,
719 x_msg_data => x_msg_data,
720 p_api_type => g_api_type);
721
722 WHEN OTHERS THEN
723 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
724 p_api_name => l_api_name,
725 p_pkg_name => G_PKG_NAME,
726 p_exc_name => 'OTHERS',
727 x_msg_count => x_msg_count,
728 x_msg_data => x_msg_data,
729 p_api_type => g_api_type);
730 END delete_change_request_lines;
731
732
733 /* Logic for Creating Change Request: If a Change Request has been requested for Agreement A1 (say)
734 * If this change request is the first one for this agreement, then a backup of the Agreement is taken
735 * and tied with a new change request. The status of the Agreement is set to ABANDONED and the status
736 * of the change request is set to COMPLETED. After this, another copy of the Agreement A1 is created (say A2)
737 * and is tied with a new change Request. The status of the Change Request and the Ageement is NEW. This
738 * is the Change Request which can be modified and submitted for Approval. If such a Change Request is
739 * approved, then the changes between the Agreement attached with this Change Request and the original
740 * agreement A1 are synchornized onto A1
741 *
742 * For the case when the Change Request is not the first one, new agreement is created from the originating
743 * agreement and attached with a new change request. The status of the new agreement copy and the change
744 * request is set to NEW. When this agreement attached with the change request is approved, the changes are
745 * synchronized on the main copy. Note that in this scenario, no back up of the original agreement is taken
746 *
747 */
748 PROCEDURE create_change_request(p_api_version IN NUMBER
749 ,p_init_msg_list IN VARCHAR2
750 ,x_return_status OUT NOCOPY VARCHAR2
751 ,x_msg_count OUT NOCOPY NUMBER
752 ,x_msg_data OUT NOCOPY VARCHAR2
753 ,p_vcrv_rec IN vcrv_rec_type
754 ,p_vrrv_tbl IN vrrv_tbl_type
755 ,x_vcrv_rec OUT NOCOPY vcrv_rec_type
756 ,x_vrrv_tbl OUT NOCOPY vrrv_tbl_type
757 ) IS
758 -- cursor to check if there are any existing change requests
759 CURSOR c_chk_pending_req_csr (cp_agreement_id okc_k_headers_b.id%TYPE) IS
760 SELECT 'X'
761 FROM okl_vp_change_requests creq
762 WHERE creq.chr_id = cp_agreement_id
763 AND creq.status_code IN ('NEW','INCOMPELTE','REJECTED','PENDING_APPROVAL','PASSED');
764
765 -- cursor to get the agreement number
766 CURSOR c_get_agr_num_csr (cp_agreement_id okc_k_headers_b.id%TYPE) IS
767 SELECT contract_number, sts_code
768 FROM okc_k_headers_b
769 WHERE id = cp_agreement_id;
770
771 -- cursor to check if this is the first change request of type AGREEMENT for the agreement
772 -- this can be derived by looking at change requests of type AGREEMENT in the context of the original agreement
773 CURSOR c_chk_first_cr_csr (cp_agreement_id okc_k_headers_b.id%TYPE)IS
774 SELECT 'X'
775 FROM okl_vp_change_requests crq
776 WHERE crq.chr_id = cp_agreement_id
777 -- AND crq.change_type_code <> 'ASSOCIATION'
778 AND crq.status_code = G_COMPLETED_STS_CODE;
779
780 lv_vcrv_rec vcrv_rec_type;
781 xc_vcrv_rec vcrv_rec_type;
782 lv_pending_req_cvar VARCHAR2(1);
783 lv_agreement_number okc_k_headers_b.contract_number%TYPE;
784 lv_agreement_status okc_k_headers_b.sts_code%TYPE;
785 lv_calc_agr_number VARCHAR2(100);
786 lv_vagr_hdr_rec okl_vp_copy_contract_pvt.copy_header_rec_type;
787 lx_new_agr_id okc_k_headers_b.id%TYPE;
788 lv_is_first_cr VARCHAR2(1);
789 lv_khrv_rec okl_khr_pvt.khrv_rec_type;
790 x_khrv_rec okl_khr_pvt.khrv_rec_type;
791 x_request_status okl_vp_change_requests.status_code%TYPE;
792 lv_vrrv_tbl vrrv_tbl_type;
793
794 l_api_version CONSTANT NUMBER DEFAULT 1.0;
795 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CREATE_CHANGE_REQUEST';
796 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CHANGE_REQUEST_PVT.CREATE_CHANGE_REQUEST';
797 l_debug_enabled VARCHAR2(10);
798
799 BEGIN
800 x_return_status := OKL_API.G_RET_STS_SUCCESS;
801
802 l_debug_enabled := okl_debug_pub.check_log_enabled;
803
804 IF(l_debug_enabled='Y') THEN
805 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVCRB.pls call create_change_request');
806 END IF;
807
808 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
809 x_return_status := OKL_API.START_ACTIVITY(
810 p_api_name => l_api_name
811 ,p_pkg_name => G_PKG_NAME
812 ,p_init_msg_list => p_init_msg_list
813 ,l_api_version => l_api_version
814 ,p_api_version => p_api_version
815 ,p_api_type => g_api_type
816 ,x_return_status => x_return_status);
817 -- check if activity started successfully
818 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
819 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
820 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
821 RAISE OKL_API.G_EXCEPTION_ERROR;
822 END IF;
823
824 lv_vcrv_rec := p_vcrv_rec;
825
826 -- first: verify if all the parameters required for processing are passed.
827 IF(lv_vcrv_rec.chr_id IS NULL OR lv_vcrv_rec.chr_id = OKL_API.G_MISS_NUM)THEN
828 OKL_API.set_message(G_APP_NAME, G_CR_PARMS_MISSING);
829 RAISE OKL_API.G_EXCEPTION_ERROR;
830 END IF;
831
832 -- second: check if there exists a change request already in status NEW, PENDING_APPROVAL, REJCTED, INCOMPLETE or PASSED
833 OPEN c_chk_pending_req_csr (lv_vcrv_rec.chr_id); FETCH c_chk_pending_req_csr INTO lv_pending_req_cvar;
834 CLOSE c_chk_pending_req_csr;
835
836 -- fetch the agreement number to display in the error message and also for later use
837 OPEN c_get_agr_num_csr(lv_vcrv_rec.chr_id); FETCH c_get_agr_num_csr INTO lv_agreement_number, lv_agreement_status;
838 CLOSE c_get_agr_num_csr;
839
840 IF(NVL(lv_pending_req_cvar,'Y') = 'X')THEN
841 OKL_API.set_message(G_APP_NAME, G_PENDING_CHANGE_REQ_EXISTS, 'AGR_NUMBER', lv_agreement_number);
842 RAISE OKL_API.G_EXCEPTION_ERROR;
843 END IF;
844
845 -- third: check if the agreement is ACTIVE. Change Requests cannot be created for non active agreements
846 IF(lv_agreement_status <> G_ACTIVE_STS_CODE)THEN
847 OKL_API.set_message(G_APP_NAME, G_NOT_ACTIVE_AGREEMENT, 'AGR_NUMBER', lv_agreement_number);
848 RAISE OKL_API.G_EXCEPTION_ERROR;
849 END IF;
850
851 -- fourth: check there is atleast one change request reason for the AGREEMENT or ASSOCIATION type of change request
852 IF(p_vrrv_tbl.COUNT <= 0)THEN
853 OKL_API.set_message(G_APP_NAME, G_ONE_REASON_REQD);
854 RAISE OKL_API.G_EXCEPTION_ERROR;
855 END IF;
856
857 -- check if this is the first change request for this vendor agreement and if so, take a backup
858 -- of the original agreement and associate with another change request whose status is created as COMPLETED
859 -- the status of this backed up agreement should be set to ABANDONED
860 OPEN c_chk_first_cr_csr(lv_vcrv_rec.chr_id); FETCH c_chk_first_cr_csr INTO lv_is_first_cr;
861 CLOSE c_chk_first_cr_csr;
862 -- take a backup of the Agreement
863 IF(NVL(lv_is_first_cr,'Y')<>'X')THEN
864 historize_agreement(p_api_version => p_api_version
865 ,p_init_msg_list => p_init_msg_list
866 ,x_return_status => x_return_status
867 ,x_msg_count => x_msg_count
868 ,x_msg_data => x_msg_data
869 ,p_vcrv_rec => lv_vcrv_rec
870 ,p_agreement_number => lv_agreement_number
871 ,x_vcrv_rec => xc_vcrv_rec
872 );
873 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
874 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
875 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
876 RAISE OKL_API.G_EXCEPTION_ERROR;
877 END IF;
878 END IF; -- end of NVL(lv_is_first_cr,'Y')<>'X'
879
880 IF(lv_vcrv_rec.change_type_code=G_ARGREEMENT_TYPE_CODE)THEN
881 -- get the change request number from the sequence
882 lv_vcrv_rec.change_request_number := generate_change_req_num;
883
884 -- construct the new internal agreement number that would serve as agreement number on the
885 -- new copied agreement record. the format is last 30 char of the concatenated string of
886 -- agreement number, and the change_request_number (using sequence okl_vp_change_req_num_seq)
887 -- lv_calc_agr_number finally returns last 30 characters of the concatenated value
888 -- if there are less than 30 char in the resultant string, then lv_calc_agr_number is as good as the resultant string
889 lv_calc_agr_number := TRIM(RPAD(SUBSTR(LPAD((lv_agreement_number || lv_vcrv_rec.change_request_number),120,' ') ,
890 length(LPAD((lv_agreement_number || lv_vcrv_rec.change_request_number),120,' ')) - 120),120, ' '));
891
892 -- construct the record to call copy vendor program api
893 lv_vagr_hdr_rec := NULL;
894 lv_vagr_hdr_rec.p_id := lv_vcrv_rec.chr_id;
895 lv_vagr_hdr_rec.p_to_agreement_number := lv_calc_agr_number;
896 lv_vagr_hdr_rec.p_template_yn := 'N';
897 okl_vp_copy_contract_pub.copy_contract(p_api_version => p_api_version
898 ,p_init_msg_list => p_init_msg_list
899 ,x_return_status => x_return_status
900 ,x_msg_count => x_msg_count
901 ,x_msg_data => x_msg_data
902 ,p_copy_rec => lv_vagr_hdr_rec
903 ,x_new_contract_id => lx_new_agr_id
904 );
905 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
906 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
907 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
908 RAISE OKL_API.G_EXCEPTION_ERROR;
909 END IF;
910
911 -- create change request header and change request reasons
912 lv_vcrv_rec.status_code := 'NEW';
913 lv_vcrv_rec.chr_id := lv_vcrv_rec.chr_id;
914
915 -- the request date of the change request is the date on which it was created successfully
916 lv_vcrv_rec.request_date := TRUNC(SYSDATE);
917
918 create_change_request_header(p_api_version => p_api_version
919 ,p_init_msg_list => p_init_msg_list
920 ,x_return_status => x_return_status
921 ,x_msg_count => x_msg_count
922 ,x_msg_data => x_msg_data
923 ,p_vcrv_rec => lv_vcrv_rec
924 ,x_vcrv_rec => x_vcrv_rec
925 );
926 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
927 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
928 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
929 RAISE OKL_API.G_EXCEPTION_ERROR;
930 END IF;
931
932 -- make sure there are reasons to record and call the api.
933 IF(p_vrrv_tbl.COUNT > 0)THEN
934 lv_vrrv_tbl := p_vrrv_tbl;
935 -- copy the change request id to the child records.
936 FOR idx IN 1 .. p_vrrv_tbl.COUNT LOOP
937 lv_vrrv_tbl(idx).crs_id := x_vcrv_rec.id;
938 END LOOP;
939 create_change_request_lines(p_api_version => p_api_version
940 ,p_init_msg_list => p_init_msg_list
941 ,x_return_status => x_return_status
942 ,x_msg_count => x_msg_count
943 ,x_msg_data => x_msg_data
944 ,p_vrrv_tbl => lv_vrrv_tbl
945 ,x_vrrv_tbl => x_vrrv_tbl
946 ,x_request_status => x_request_status
947 );
948 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
949 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
950 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
951 RAISE OKL_API.G_EXCEPTION_ERROR;
952 END IF;
953 END IF; -- end of p_vrrv_tbl.COUNT > 0
954
955 -- now that the agreement copy is created, set the change request id in the okl_k_headers to the created change request
956 -- commenting this call because okl_khr_pvt has not been modified to incl crs_id, template_type_code yet
957 lv_khrv_rec.id := lx_new_agr_id;
958 lv_khrv_rec.crs_id := x_vcrv_rec.id;
959 okl_khr_pvt.update_row(p_api_version => p_api_version
960 ,p_init_msg_list => p_init_msg_list
961 ,x_return_status => x_return_status
962 ,x_msg_count => x_msg_count
963 ,x_msg_data => x_msg_data
964 ,p_khrv_rec => lv_khrv_rec
965 ,x_khrv_rec => x_khrv_rec);
966 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
967 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
968 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
969 RAISE OKL_API.G_EXCEPTION_ERROR;
970 END IF;
971
972 -- now copy the out variable of x_vcrv_rec.chr_id to the new agreement id that was created for this
973 -- AGREEMENT type of change request. this is essential because the UI depends on this parameter.
974 -- this value need not be the same as parent agreement id in case of AGREEMENT type of change request
975 x_vcrv_rec.chr_id := lx_new_agr_id;
976
977 ELSIF(lv_vcrv_rec.change_type_code=G_ASSOCIATE_TYPE_CODE)THEN
978 -- create change request header and change request reasons
979 lv_vcrv_rec.status_code := 'NEW';
980 -- get the change request number from the sequence
981 lv_vcrv_rec.change_request_number := generate_change_req_num;
982
983 -- the request date of the change request is the date on which it was created successfully
984 lv_vcrv_rec.request_date := TRUNC(SYSDATE);
985
986 create_change_request_header(p_api_version => p_api_version
987 ,p_init_msg_list => p_init_msg_list
988 ,x_return_status => x_return_status
989 ,x_msg_count => x_msg_count
990 ,x_msg_data => x_msg_data
991 ,p_vcrv_rec => lv_vcrv_rec
992 ,x_vcrv_rec => x_vcrv_rec
993 );
994 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
995 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
996 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
997 RAISE OKL_API.G_EXCEPTION_ERROR;
998 END IF;
999
1000 -- make sure there are reasons to record and call the api.
1001 IF(p_vrrv_tbl.COUNT > 0)THEN
1002 lv_vrrv_tbl := p_vrrv_tbl;
1003 -- copy the change request id to the child records.
1004 FOR idx IN 1 .. p_vrrv_tbl.COUNT LOOP
1005 lv_vrrv_tbl(idx).crs_id := x_vcrv_rec.id;
1006 END LOOP;
1007 create_change_request_lines(p_api_version => p_api_version
1008 ,p_init_msg_list => p_init_msg_list
1009 ,x_return_status => x_return_status
1010 ,x_msg_count => x_msg_count
1011 ,x_msg_data => x_msg_data
1012 ,p_vrrv_tbl => lv_vrrv_tbl
1013 ,x_vrrv_tbl => x_vrrv_tbl
1014 ,x_request_status => x_request_status
1015 );
1016 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1017 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1018 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1019 RAISE OKL_API.G_EXCEPTION_ERROR;
1020 END IF;
1021 END IF; -- end of p_vrrv_tbl.COUNT > 0
1022
1023 -- for ASSOCIATION type of change requests, the associations on the original agreements are copied over to the
1024 -- new change request. the copy of the original agreement is not created and associated with the change request
1025 -- (backup is taken in this case too)
1026 OKL_VP_ASSOCIATIONS_PVT.copy_crs_associations(p_api_version => p_api_version
1027 ,p_init_msg_list => p_init_msg_list
1028 ,x_return_status => x_return_status
1029 ,x_msg_count => x_msg_count
1030 ,x_msg_data => x_msg_data
1031 ,p_chr_id => lv_vcrv_rec.chr_id
1032 ,p_crs_id => x_vcrv_rec.id
1033 );
1034 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1035 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1036 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1037 RAISE OKL_API.G_EXCEPTION_ERROR;
1038 END IF;
1039 END IF; -- end if for lv_vcrv_rec.change_type_code=G_ARGREEMENT_TYPE_CODE
1040
1041 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1042
1043 IF(l_debug_enabled='Y') THEN
1044 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVCRB.pls call create_change_request');
1045 END IF;
1046 EXCEPTION
1047 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1048 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1049 p_api_name => l_api_name,
1050 p_pkg_name => G_PKG_NAME,
1051 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1052 x_msg_count => x_msg_count,
1053 x_msg_data => x_msg_data,
1054 p_api_type => g_api_type);
1055
1056 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1057 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1058 p_api_name => l_api_name,
1059 p_pkg_name => G_PKG_NAME,
1060 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1061 x_msg_count => x_msg_count,
1062 x_msg_data => x_msg_data,
1063 p_api_type => g_api_type);
1064
1065 WHEN OTHERS THEN
1066 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1067 p_api_name => l_api_name,
1068 p_pkg_name => G_PKG_NAME,
1069 p_exc_name => 'OTHERS',
1070 x_msg_count => x_msg_count,
1071 x_msg_data => x_msg_data,
1072 p_api_type => g_api_type);
1073 END create_change_request;
1074
1075 PROCEDURE abandon_change_request(p_api_version IN NUMBER
1076 ,p_init_msg_list IN VARCHAR2
1077 ,x_return_status OUT NOCOPY VARCHAR2
1078 ,x_msg_count OUT NOCOPY NUMBER
1079 ,x_msg_data OUT NOCOPY VARCHAR2
1080 ,p_vcrv_rec IN vcrv_rec_type
1081 ,x_vcrv_rec OUT NOCOPY vcrv_rec_type
1082 ) IS
1083 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1084 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'ABANDON_CHANGE_REQUEST';
1085 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CHANGE_REQUEST_PVT.ABANDON_CHANGE_REQUEST';
1086 l_debug_enabled VARCHAR2(10);
1087
1088 BEGIN
1089 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1090
1091 l_debug_enabled := okl_debug_pub.check_log_enabled;
1092
1093 IF(l_debug_enabled='Y') THEN
1094 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVCRB.pls call abandon_change_request');
1095 END IF;
1096
1097 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1098 x_return_status := OKL_API.START_ACTIVITY(
1099 p_api_name => l_api_name
1100 ,p_pkg_name => G_PKG_NAME
1101 ,p_init_msg_list => p_init_msg_list
1102 ,l_api_version => l_api_version
1103 ,p_api_version => p_api_version
1104 ,p_api_type => g_api_type
1105 ,x_return_status => x_return_status);
1106 -- check if activity started successfully
1107 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1108 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1109 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1110 RAISE OKL_API.G_EXCEPTION_ERROR;
1111 END IF;
1112
1113 set_change_request_status(p_api_version => p_api_version
1114 ,p_init_msg_list => p_init_msg_list
1115 ,x_return_status => x_return_status
1116 ,x_msg_count => x_msg_count
1117 ,x_msg_data => x_msg_data
1118 ,p_vp_crq_id => p_vcrv_rec.id
1119 ,p_status_code => G_ABANDONED_STS_CODE
1120 );
1121 -- write to log
1122 IF(NVL(l_debug_enabled,'N')='Y') THEN
1123 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1124 l_module,
1125 'set_change_request_status for '||p_vcrv_rec.id||' from abandon_change_request returned with status '||x_return_status
1126 );
1127 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1128 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1129 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1130 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1131 RAISE OKL_API.G_EXCEPTION_ERROR;
1132 END IF;
1133
1134 -- now set the OUT variables for calling UI or API use
1135 x_vcrv_rec.id := p_vcrv_rec.id;
1136 x_vcrv_rec.status_code := G_ABANDONED_STS_CODE;
1137
1138 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1139
1140 IF(l_debug_enabled='Y') THEN
1141 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVCRB.pls call abandon_change_request');
1142 END IF;
1143 EXCEPTION
1144 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1145 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1146 p_api_name => l_api_name,
1147 p_pkg_name => G_PKG_NAME,
1148 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1149 x_msg_count => x_msg_count,
1150 x_msg_data => x_msg_data,
1151 p_api_type => g_api_type);
1152
1153 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1154 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1155 p_api_name => l_api_name,
1156 p_pkg_name => G_PKG_NAME,
1157 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1158 x_msg_count => x_msg_count,
1159 x_msg_data => x_msg_data,
1160 p_api_type => g_api_type);
1161
1162 WHEN OTHERS THEN
1163 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1164 p_api_name => l_api_name,
1165 p_pkg_name => G_PKG_NAME,
1166 p_exc_name => 'OTHERS',
1167 x_msg_count => x_msg_count,
1168 x_msg_data => x_msg_data,
1169 p_api_type => g_api_type);
1170 END abandon_change_request;
1171
1172 PROCEDURE set_change_request_status(p_api_version IN NUMBER
1173 ,p_init_msg_list IN VARCHAR2
1174 ,x_return_status OUT NOCOPY VARCHAR2
1175 ,x_msg_count OUT NOCOPY NUMBER
1176 ,x_msg_data OUT NOCOPY VARCHAR2
1177 ,p_vp_crq_id IN okl_vp_change_requests.id%TYPE
1178 ,p_status_code IN okl_vp_change_requests.status_code%TYPE
1179 ) IS
1180 -- cursor to fetch the status of the change request
1181 CURSOR c_get_cr_dtls_csr (cp_change_req_id okl_vp_change_requests.id%TYPE)IS
1182 SELECT status_code
1183 ,change_request_number
1184 ,change_type_code
1185 ,chr_id -- this chr_id is for only ASSOCIATION type of change request
1186 FROM okl_vp_change_requests
1187 WHERE id = cp_change_req_id;
1188 cv_get_cr_dtls c_get_cr_dtls_csr%ROWTYPE;
1189
1190 -- cursor to get the agreement id that is tied to the change request. this agreement id is not the parent agreement
1191 -- but the agreement that is copied from the parent agreement. this copy will have the crs_id value in okl_k_headers
1192 CURSOR c_get_creq_chr_id_csr (cp_change_req_id okl_vp_change_requests.id%TYPE)IS
1193 SELECT chr.id
1194 FROM okc_k_headers_b chr
1195 ,okl_k_headers khr
1196 WHERE chr.id = khr.id
1197 AND khr.crs_id = cp_change_req_id;
1198 lv_creq_chr_id okc_k_headers_b.id%TYPE;
1199
1200 lv_vcrv_rec vcrv_rec_type;
1201 x_vcrv_rec vcrv_rec_type;
1202 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1203 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SET_CHANGE_REQUEST_STS';
1204 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CHANGE_REQUEST_PVT.SET_CHANGE_REQUEST_STATUS';
1205 l_debug_enabled VARCHAR2(10);
1206
1207 BEGIN
1208 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1209
1210 l_debug_enabled := okl_debug_pub.check_log_enabled;
1211
1212 IF(l_debug_enabled='Y') THEN
1213 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVCRB.pls call set_change_request_status');
1214 END IF;
1215
1216 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1217 x_return_status := OKL_API.START_ACTIVITY(
1218 p_api_name => l_api_name
1219 ,p_pkg_name => G_PKG_NAME
1220 ,p_init_msg_list => p_init_msg_list
1221 ,l_api_version => l_api_version
1222 ,p_api_version => p_api_version
1223 ,p_api_type => g_api_type
1224 ,x_return_status => x_return_status);
1225 -- check if activity started successfully
1226 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1227 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1228 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1229 RAISE OKL_API.G_EXCEPTION_ERROR;
1230 END IF;
1231
1232 IF(p_vp_crq_id IS NULL OR p_vp_crq_id = OKL_API.G_MISS_NUM OR p_status_code IS NULL OR p_status_code = OKL_API.G_MISS_CHAR)THEN
1233 OKL_API.set_message(G_APP_NAME, G_NO_PARAM_STS_CODE);
1234 RAISE OKL_API.G_EXCEPTION_ERROR;
1235 END IF;
1236
1237 OPEN c_get_cr_dtls_csr(p_vp_crq_id); FETCH c_get_cr_dtls_csr INTO cv_get_cr_dtls;
1238 CLOSE c_get_cr_dtls_csr;
1239
1240 -- if status code is being set to incomplete then the status code in the db should be either PASSED or REJECTED
1241 -- error otherwise
1242 IF(G_INCOMPLETE_STS_CODE = p_status_code)THEN
1243 IF(cv_get_cr_dtls.status_code NOT IN(G_NEW_STS_CODE, G_PASSED_STS_CODE, G_REJECTED_STS_CODE))THEN
1244 OKL_API.set_message(G_APP_NAME, G_INVALID_STS_INCOMP, 'CHANGE_REQ_NUM',cv_get_cr_dtls.change_request_number);
1245 RAISE OKL_API.G_EXCEPTION_ERROR;
1246 END IF;
1247 ELSIF(G_PENDING_STS_CODE = p_status_code)THEN
1248 -- check useful from preventing the user from resubmitting the change request and also to prevent the same workflow
1249 -- being re-launched
1250 IF(cv_get_cr_dtls.status_code NOT IN(G_PASSED_STS_CODE))THEN
1251 OKL_API.set_message(G_APP_NAME, G_INVALID_STS_PENDING, 'CHANGE_REQ_NUM',cv_get_cr_dtls.change_request_number);
1252 RAISE OKL_API.G_EXCEPTION_ERROR;
1253 END IF;
1254 ELSIF(G_APPROVED_STS_CODE = p_status_code)THEN
1255 -- if a change request to be approved, the prior status should have been PENDING_APPROVAL, error otherwise
1256 IF(cv_get_cr_dtls.status_code NOT IN(G_PENDING_STS_CODE))THEN
1257 OKL_API.set_message(G_APP_NAME, G_INVALID_STS_APPROVED, 'CHANGE_REQ_NUM',cv_get_cr_dtls.change_request_number);
1258 RAISE OKL_API.G_EXCEPTION_ERROR;
1259 END IF;
1260 lv_vcrv_rec.approved_date := TRUNC(SYSDATE);
1261 ELSIF(G_ABANDONED_STS_CODE = p_status_code)THEN
1262 -- verify if the change request whose abandonment is requested is in status NEW, PASSED, INCOMPLETE or REJECTED. error otherwise
1263 -- this check will also prevent user from re-abandoning the change request by use of browser refresh button
1264 IF(cv_get_cr_dtls.status_code NOT IN ('NEW','PASSED','INCOMPLETE','REJECTED'))THEN
1265 OKL_API.set_message(G_APP_NAME, G_NOT_NEW_PASS_CR, 'CHANGE_REQ_NUM', cv_get_cr_dtls.change_request_number);
1266 RAISE OKL_API.G_EXCEPTION_ERROR;
1267 END IF;
1268 END IF;
1269
1270 -- for AGREEMENT type of change request, the referred agreement has also be set to status that was set
1271 -- at the change request level
1272 -- note that for ASSOCIATION type of change request, there is no agreement copy to update and the original agreement
1273 -- should not be updated
1274 IF(cv_get_cr_dtls.change_type_code = G_ARGREEMENT_TYPE_CODE)THEN
1275 -- now get the copied agreement from okl_k_headers using the change request id
1276 lv_creq_chr_id := NULL;
1277 OPEN c_get_creq_chr_id_csr (p_vp_crq_id);
1278 FETCH c_get_creq_chr_id_csr INTO lv_creq_chr_id; CLOSE c_get_creq_chr_id_csr;
1279 okl_contract_status_pub.update_contract_status(p_api_version => p_api_version
1280 ,p_init_msg_list => p_init_msg_list
1281 ,x_return_status => x_return_status
1282 ,x_msg_count => x_msg_count
1283 ,x_msg_data => x_msg_data
1284 ,p_khr_status => p_status_code
1285 ,p_chr_id => lv_creq_chr_id -- this is the id of the backup copy agmnt
1286 );
1287 -- write to log
1288 IF(NVL(l_debug_enabled,'N')='Y') THEN
1289 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1290 l_module,
1291 'okl_contract_status_pub.update_contract_status returned with status '||x_return_status||' x_msg_data '||x_msg_data||' id '||x_vcrv_rec.id
1292 );
1293 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1294 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1295 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1296 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1297 RAISE OKL_API.G_EXCEPTION_ERROR;
1298 END IF;
1299 END IF;
1300
1301 -- now set the status of the change request once the copy agreement has been changed
1302 -- the updation of this record is applicable to both AGREEMENT type of change request and ASSOCIATION type too.
1303 lv_vcrv_rec.id := p_vp_crq_id;
1304 lv_vcrv_rec.chr_id := cv_get_cr_dtls.chr_id; -- this chr_id is the parent agreement id
1305 lv_vcrv_rec.change_type_code := cv_get_cr_dtls.change_type_code;
1306 lv_vcrv_rec.change_request_number := cv_get_cr_dtls.change_request_number;
1307 lv_vcrv_rec.status_code := p_status_code;
1308
1309 update_change_request_header(p_api_version => p_api_version
1310 ,p_init_msg_list => p_init_msg_list
1311 ,x_return_status => x_return_status
1312 ,x_msg_count => x_msg_count
1313 ,x_msg_data => x_msg_data
1314 ,p_vcrv_rec => lv_vcrv_rec
1315 ,x_vcrv_rec => x_vcrv_rec
1316 );
1317 -- write to log
1318 IF(NVL(l_debug_enabled,'N')='Y') THEN
1319 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1320 l_module,
1321 'update_change_request_header returned with status '||x_return_status||' x_msg_data '||x_msg_data||' id '||x_vcrv_rec.id
1322 );
1323 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1324 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1325 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1326 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1327 RAISE OKL_API.G_EXCEPTION_ERROR;
1328 END IF;
1329
1330 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1331
1332 IF(l_debug_enabled='Y') THEN
1333 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVCRB.pls call set_change_request_status');
1334 END IF;
1335
1336 EXCEPTION
1337 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1338 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1339 p_api_name => l_api_name,
1340 p_pkg_name => G_PKG_NAME,
1341 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1342 x_msg_count => x_msg_count,
1343 x_msg_data => x_msg_data,
1344 p_api_type => g_api_type);
1345
1346 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1347 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1348 p_api_name => l_api_name,
1349 p_pkg_name => G_PKG_NAME,
1350 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1351 x_msg_count => x_msg_count,
1352 x_msg_data => x_msg_data,
1353 p_api_type => g_api_type);
1354
1355 WHEN OTHERS THEN
1356 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1357 p_api_name => l_api_name,
1358 p_pkg_name => G_PKG_NAME,
1359 p_exc_name => 'OTHERS',
1360 x_msg_count => x_msg_count,
1361 x_msg_data => x_msg_data,
1362 p_api_type => g_api_type);
1363 END set_change_request_status;
1364
1365 PROCEDURE cascade_request_status_edit(p_api_version IN NUMBER
1366 ,p_init_msg_list IN VARCHAR2
1367 ,x_return_status OUT NOCOPY VARCHAR2
1368 ,x_msg_count OUT NOCOPY NUMBER
1369 ,x_msg_data OUT NOCOPY VARCHAR2
1370 ,p_vp_crq_id IN okl_vp_change_requests.id%TYPE
1371 ,x_status_code OUT NOCOPY okl_vp_change_requests.status_code%TYPE
1372 ) IS
1373 -- cursor to fetch the status of the change request
1374 CURSOR c_get_cr_sts_csr (cp_change_req_id okl_vp_change_requests.id%TYPE)IS
1375 SELECT status_code
1376 FROM okl_vp_change_requests
1377 WHERE id = cp_change_req_id;
1378 c_get_cr_sts_rec c_get_cr_sts_csr%ROWTYPE;
1379
1380 lv_vcrv_rec vcrv_rec_type;
1381 x_vcrv_rec vcrv_rec_type;
1382 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1383 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CASCADE_REQUEST_STS';
1384 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CHANGE_REQUEST_PVT.CASCADE_REQUEST_STATUS_EDIT';
1385 l_debug_enabled VARCHAR2(10);
1386
1387 BEGIN
1388 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1389
1390 l_debug_enabled := okl_debug_pub.check_log_enabled;
1391
1392 IF(l_debug_enabled='Y') THEN
1393 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVCRB.pls call cascade_request_status_edit');
1394 END IF;
1395
1396 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1397 x_return_status := OKL_API.START_ACTIVITY(
1398 p_api_name => l_api_name
1399 ,p_pkg_name => G_PKG_NAME
1400 ,p_init_msg_list => p_init_msg_list
1401 ,l_api_version => l_api_version
1402 ,p_api_version => p_api_version
1403 ,p_api_type => g_api_type
1404 ,x_return_status => x_return_status);
1405 -- check if activity started successfully
1406 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1407 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1408 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1409 RAISE OKL_API.G_EXCEPTION_ERROR;
1410 END IF;
1411
1412 IF(p_vp_crq_id IS NULL OR p_vp_crq_id = OKL_API.G_MISS_NUM)THEN
1413 OKL_API.set_message(G_APP_NAME, OKL_API.G_REQUIRED_VALUE, OKL_API.G_COL_NAME_TOKEN, 'ID');
1414 RAISE OKL_API.G_EXCEPTION_ERROR;
1415 END IF;
1416 -- if the change request is in status PASSED then we need to set it back to INCOMPLETE
1417 OPEN c_get_cr_sts_csr(p_vp_crq_id); FETCH c_get_cr_sts_csr INTO c_get_cr_sts_rec;
1418 CLOSE c_get_cr_sts_csr;
1419 -- for a PASSED change request and REJECTED change request, when updated, the status should be toggled back
1420 -- to INCOMPLETE
1421 IF(c_get_cr_sts_rec.status_code = G_PASSED_STS_CODE OR c_get_cr_sts_rec.status_code = G_REJECTED_STS_CODE)THEN
1422 set_change_request_status(p_api_version => p_api_version
1423 ,p_init_msg_list => p_init_msg_list
1424 ,x_return_status => x_return_status
1425 ,x_msg_count => x_msg_count
1426 ,x_msg_data => x_msg_data
1427 ,p_vp_crq_id => p_vp_crq_id
1428 ,p_status_code => G_INCOMPLETE_STS_CODE
1429 );
1430 -- write to log
1431 IF(NVL(l_debug_enabled,'N')='Y') THEN
1432 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1433 l_module,
1434 'set_change_request_status returned with status '||x_return_status||' x_msg_data '||x_msg_data||' id '||x_vcrv_rec.id
1435 );
1436 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1437 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1438 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1439 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1440 RAISE OKL_API.G_EXCEPTION_ERROR;
1441 END IF;
1442 x_status_code := G_INCOMPLETE_STS_CODE;
1443 ELSE
1444 x_status_code := c_get_cr_sts_rec.status_code;
1445 END IF; -- end of c_get_cr_sts_rec.status_code = G_PASSED_STS_CODE
1446
1447 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1448
1449 IF(l_debug_enabled='Y') THEN
1450 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVCRB.pls call cascade_request_status_edit');
1451 END IF;
1452
1453 EXCEPTION
1454 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1455 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1456 p_api_name => l_api_name,
1457 p_pkg_name => G_PKG_NAME,
1458 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1459 x_msg_count => x_msg_count,
1460 x_msg_data => x_msg_data,
1461 p_api_type => g_api_type);
1462
1463 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1464 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1465 p_api_name => l_api_name,
1466 p_pkg_name => G_PKG_NAME,
1467 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1468 x_msg_count => x_msg_count,
1469 x_msg_data => x_msg_data,
1470 p_api_type => g_api_type);
1471
1472 WHEN OTHERS THEN
1473 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1474 p_api_name => l_api_name,
1475 p_pkg_name => G_PKG_NAME,
1476 p_exc_name => 'OTHERS',
1477 x_msg_count => x_msg_count,
1478 x_msg_data => x_msg_data,
1479 p_api_type => g_api_type);
1480 END cascade_request_status_edit;
1481
1482 PROCEDURE submit_cr_for_approval(p_api_version IN NUMBER
1483 ,p_init_msg_list IN VARCHAR2
1484 ,x_return_status OUT NOCOPY VARCHAR2
1485 ,x_msg_count OUT NOCOPY NUMBER
1486 ,x_msg_data OUT NOCOPY VARCHAR2
1487 ,p_chr_id IN okc_k_headers_b.id%TYPE
1488 ,x_status_code OUT NOCOPY okl_vp_change_requests.status_code%TYPE
1489 ) IS
1490
1491 -- cursor to identify the change request from the passed in agreement id
1492 CURSOR c_get_change_req_csr(cp_chr_id okl_k_headers.id%TYPE)IS
1493 SELECT id
1494 FROM okl_vp_change_requests
1495 WHERE chr_id = cp_chr_id
1496 AND status_code = G_PASSED_STS_CODE;
1497 lv_change_request_id okl_vp_change_requests.id%TYPE;
1498
1499 CURSOR c_get_crs_id_csr(cp_chr_id okl_k_headers.id%TYPE)IS
1500 SELECT khr.crs_id
1501 FROM okl_k_headers khr
1502 ,okc_k_headers_b chr
1503 WHERE chr.id = khr.id
1504 AND chr.sts_code = G_PASSED_STS_CODE
1505 AND chr.id = cp_chr_id;
1506
1507 l_approval_process VARCHAR2(30);
1508 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1509 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SUBMIT_CR_FOR_APPROVAL';
1510 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_CHANGE_REQUEST_PVT.SUBMIT_CR_FOR_APPROVAL';
1511 l_debug_enabled VARCHAR2(10);
1512
1513 BEGIN
1514 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1515
1516 l_debug_enabled := okl_debug_pub.check_log_enabled;
1517
1518 IF(l_debug_enabled='Y') THEN
1519 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVCRB.pls call submit_cr_for_approval');
1520 END IF;
1521
1522 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1523 x_return_status := OKL_API.START_ACTIVITY(
1524 p_api_name => l_api_name
1525 ,p_pkg_name => G_PKG_NAME
1526 ,p_init_msg_list => p_init_msg_list
1527 ,l_api_version => l_api_version
1528 ,p_api_version => p_api_version
1529 ,p_api_type => g_api_type
1530 ,x_return_status => x_return_status);
1531 -- check if activity started successfully
1532 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1533 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1534 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1535 RAISE OKL_API.G_EXCEPTION_ERROR;
1536 END IF;
1537
1538 -- get the change request id from the agreement id; error if no change request found
1539 OPEN c_get_change_req_csr(p_chr_id); FETCH c_get_change_req_csr INTO lv_change_request_id;
1540 IF(c_get_change_req_csr%NOTFOUND)THEN
1541 CLOSE c_get_change_req_csr;
1542 -- this is the case of AGREEMENT type of change request. from the chr_id, we need to derive the crs_id from okl_k_headers
1543 -- if no crs_id exists, then error
1544 OPEN c_get_crs_id_csr(p_chr_id); FETCH c_get_crs_id_csr INTO lv_change_request_id;
1545 IF(c_get_crs_id_csr%NOTFOUND)THEN
1546 CLOSE c_get_crs_id_csr;
1547 OKL_API.set_message(G_APP_NAME, G_CR_PARMS_MISSING);
1548 RAISE OKL_API.G_EXCEPTION_ERROR;
1549 ELSE
1550 CLOSE c_get_crs_id_csr;
1551 END IF;
1552 ELSE
1553 CLOSE c_get_change_req_csr;
1554 END IF;
1555
1556 -- write to log
1557 IF(NVL(l_debug_enabled,'N')='Y') THEN
1558 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1559 l_module,
1560 'lv_change_request_id '||lv_change_request_id||' from cursor '
1561 );
1562 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1563
1564 x_status_code := G_PENDING_STS_CODE;
1565 -- now set the change request to PENDING_APPROVAL
1566 -- this procedure will automatically set the ageement associated with the change request (only in case of AGREEMENT type of change request)
1567 -- to PENDING_APPROVAL
1568 set_change_request_status(p_api_version => p_api_version
1569 ,p_init_msg_list => p_init_msg_list
1570 ,x_return_status => x_return_status
1571 ,x_msg_count => x_msg_count
1572 ,x_msg_data => x_msg_data
1573 ,p_vp_crq_id => lv_change_request_id
1574 ,p_status_code => G_PENDING_STS_CODE
1575 );
1576 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1577 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1578 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1579 RAISE OKL_API.G_EXCEPTION_ERROR;
1580 END IF;
1581
1582 -- read the profile OKL: Change Request Approval Process
1583 l_approval_process := fnd_profile.value('OKL_VP_CR_APPROVAL_PROCESS');
1584 IF(NVL(l_approval_process,'NONE')='NONE')THEN
1585 -- since no option is set at the profile, approve the change request by default
1586 -- before approving the change request, we need to sync up the change request
1587 -- agreement and the original agreement. the details and combinations are handled in the API
1588 -- please note that the sync API is also responsible for updating the change request to COMPLETED
1589 -- and the associated agreement (only for AGREEMENT type of change request) to ABANDONED
1590 okl_vp_sync_cr_pvt.sync_change_request(p_api_version => p_api_version
1591 ,p_init_msg_list => p_init_msg_list
1592 ,x_return_status => x_return_status
1593 ,x_msg_count => x_msg_count
1594 ,x_msg_data => x_msg_data
1595 ,p_change_request_id => lv_change_request_id
1596 );
1597 -- write to log
1598 IF(NVL(l_debug_enabled,'N')='Y') THEN
1599 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1600 l_module,
1601 'okl_vp_sync_cr_pvt.sync_change_request on change request id '||lv_change_request_id||' returned with status '||x_return_status
1602 );
1603 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1604
1605 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1606 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1607 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1608 RAISE OKL_API.G_EXCEPTION_ERROR;
1609 END IF;
1610 -- when the change request is synced up successfully, the status of the change request is COMPLETED and the status of the agreement
1611 -- associated with the change request is set to ABANDONED.
1612 -- the OUT variable is assigned to COMPLETED so that this value can be utillized by the caller program or UI
1613 x_status_code := G_COMPLETED_STS_CODE;
1614
1615 ELSIF(l_approval_process in ('AME','WF'))THEN
1616 okl_vp_cr_wf.raise_cr_event_approval(p_api_version => p_api_version
1617 ,p_init_msg_list => p_init_msg_list
1618 ,x_return_status => x_return_status
1619 ,x_msg_count => x_msg_count
1620 ,x_msg_data => x_msg_data
1621 ,p_vp_crq_id => lv_change_request_id);
1622 -- write to log
1623 IF(NVL(l_debug_enabled,'N')='Y') THEN
1624 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1625 l_module,
1626 'okl_vp_cr_wf.raise_oa_event_approval on change request id '||lv_change_request_id||' returned with status '||x_return_status
1627 );
1628 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1629 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1630 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1631 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1632 RAISE OKL_API.G_EXCEPTION_ERROR;
1633 END IF;
1634
1635 -- copy the OUT variable as PENDING_APPROVAL
1636 x_status_code := G_PENDING_STS_CODE;
1637 END IF; -- end of NVL(l_approval_process,'NONE')='NONE'
1638
1639 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1640
1641 IF(l_debug_enabled='Y') THEN
1642 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVCRB.pls call submit_cr_for_approval');
1643 END IF;
1644
1645 EXCEPTION
1646 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1647 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1648 p_api_name => l_api_name,
1649 p_pkg_name => G_PKG_NAME,
1650 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1651 x_msg_count => x_msg_count,
1652 x_msg_data => x_msg_data,
1653 p_api_type => g_api_type);
1654
1655 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1656 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1657 p_api_name => l_api_name,
1658 p_pkg_name => G_PKG_NAME,
1659 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1660 x_msg_count => x_msg_count,
1661 x_msg_data => x_msg_data,
1662 p_api_type => g_api_type);
1663
1664 WHEN OTHERS THEN
1665 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1666 p_api_name => l_api_name,
1667 p_pkg_name => G_PKG_NAME,
1668 p_exc_name => 'OTHERS',
1669 x_msg_count => x_msg_count,
1670 x_msg_data => x_msg_data,
1671 p_api_type => g_api_type);
1672 END submit_cr_for_approval;
1673
1674 FUNCTION get_assoc_agr_number(p_change_request_id IN okl_vp_change_requests.id%TYPE) RETURN VARCHAR2 IS
1675 CURSOR c_get_chreq_csr (cp_ch_req_id okl_vp_change_requests.id%TYPE) IS
1676 SELECT chr_id
1677 ,change_type_code
1678 ,status_code
1679 FROM okl_vp_change_requests
1680 WHERE id = cp_ch_req_id;
1681 cv_chreq_rec c_get_chreq_csr%ROWTYPE;
1682
1683 CURSOR c_get_chr_num_csr(cp_ch_req_id okl_vp_change_requests.id%TYPE) IS
1684 SELECT contract_number
1685 FROM okc_k_headers_b chr
1686 ,okl_k_headers khr
1687 WHERE chr.id = khr.id
1688 AND khr.crs_id = cp_ch_req_id;
1689
1690 CURSOR c_get_chr (cp_chr_id okc_k_headers_b.id%TYPE)IS
1691 SELECT contract_number
1692 FROM okc_k_headers_b
1693 WHERE id = cp_chr_id;
1694
1695 lv_ageement_number okc_k_headers_b.contract_number%TYPE;
1696 BEGIN
1697 IF(p_change_request_id IS NOT NULL AND p_change_request_id <> OKL_API.G_MISS_NUM)THEN
1698 OPEN c_get_chreq_csr(cp_ch_req_id => p_change_request_id);
1699 FETCH c_get_chreq_csr INTO cv_chreq_rec;
1700 CLOSE c_get_chreq_csr;
1701
1702 -- find the agreement that is attached to this change request by looking into the okl_k_headers table
1703 IF(G_ARGREEMENT_TYPE_CODE = cv_chreq_rec.change_type_code)THEN
1704 OPEN c_get_chr_num_csr(cp_ch_req_id => p_change_request_id);
1705 FETCH c_get_chr_num_csr INTO lv_ageement_number;
1706 CLOSE c_get_chr_num_csr;
1707 -- for ASSOCIATION type of change request, first we need to check if an agreement exists for this
1708 -- change request in the okl_k_headers table (such record can exist which was created as a backup copy)
1709 -- if no such record found then the contract_number associated to the chr_id from the change requests table
1710 -- is what we are interested in
1711 ELSIF(G_ASSOCIATE_TYPE_CODE = cv_chreq_rec.change_type_code)THEN
1712 OPEN c_get_chr_num_csr(cp_ch_req_id => p_change_request_id);
1713 FETCH c_get_chr_num_csr INTO lv_ageement_number;
1714 IF(c_get_chr_num_csr%NOTFOUND)THEN
1715 CLOSE c_get_chr_num_csr;
1716 -- this implies that the change request is an working copy (or that was merged with the originating agreement)
1717 -- that was created for modifications.
1718 OPEN c_get_chr(cp_chr_id => cv_chreq_rec.chr_id);
1719 FETCH c_get_chr INTO lv_ageement_number;
1720 CLOSE c_get_chr;
1721 ELSIF(c_get_chr_num_csr%FOUND)THEN
1722 CLOSE c_get_chr_num_csr;
1723 END IF;
1724 END IF; -- end of G_ARGREEMENT_TYPE_CODE = cv_chreq_rec.change_type_code
1725 END IF; -- end of p_change_request_id IS NOT NULL AND p_change_request_id <> OKL_API.G_MISS_NUM
1726 RETURN lv_ageement_number;
1727 END get_assoc_agr_number;
1728
1729 FUNCTION get_assoc_agr_id(p_change_request_id IN okl_vp_change_requests.id%TYPE) RETURN NUMBER IS
1730 CURSOR c_get_chreq_csr (cp_ch_req_id okl_vp_change_requests.id%TYPE) IS
1731 SELECT chr_id
1732 ,change_type_code
1733 ,status_code
1734 FROM okl_vp_change_requests
1735 WHERE id = cp_ch_req_id;
1736 cv_chreq_rec c_get_chreq_csr%ROWTYPE;
1737
1738 CURSOR c_get_chr_id_csr(cp_ch_req_id okl_vp_change_requests.id%TYPE) IS
1739 SELECT chr.id
1740 FROM okc_k_headers_b chr
1741 ,okl_k_headers khr
1742 WHERE chr.id = khr.id
1743 AND khr.crs_id = cp_ch_req_id;
1744
1745 lv_chr_id okc_k_headers_b.id%TYPE;
1746 BEGIN
1747 IF(p_change_request_id IS NOT NULL AND p_change_request_id <> OKL_API.G_MISS_NUM)THEN
1748 OPEN c_get_chreq_csr(cp_ch_req_id => p_change_request_id);
1749 FETCH c_get_chreq_csr INTO cv_chreq_rec;
1750 CLOSE c_get_chreq_csr;
1751
1752 -- find the agreement that is attached to this change request by looking into the okl_k_headers table
1753 IF(G_ARGREEMENT_TYPE_CODE = cv_chreq_rec.change_type_code)THEN
1754 OPEN c_get_chr_id_csr(cp_ch_req_id => p_change_request_id);
1755 FETCH c_get_chr_id_csr INTO lv_chr_id;
1756 CLOSE c_get_chr_id_csr;
1757 ELSIF(G_ASSOCIATE_TYPE_CODE = cv_chreq_rec.change_type_code)THEN
1758 OPEN c_get_chr_id_csr(cp_ch_req_id => p_change_request_id);
1759 FETCH c_get_chr_id_csr INTO lv_chr_id;
1760 IF(c_get_chr_id_csr%NOTFOUND)THEN
1761 CLOSE c_get_chr_id_csr;
1762 -- this implies that the change request is an working copy (or that was merged with the originating agreement)
1763 -- that was created for modifications.
1764 lv_chr_id := cv_chreq_rec.chr_id;
1765 ELSIF(c_get_chr_id_csr%FOUND)THEN
1766 CLOSE c_get_chr_id_csr;
1767 END IF;
1768 END IF; -- end of G_ARGREEMENT_TYPE_CODE = cv_chreq_rec.change_type_code
1769 END IF; -- end of p_change_request_id IS NOT NULL AND p_change_request_id <> OKL_API.G_MISS_NUM
1770 RETURN lv_chr_id;
1771 END get_assoc_agr_id;
1772 END okl_vp_change_request_pvt;
[Home] [Help]