1 PACKAGE BODY okl_vp_sync_cr_pvt AS
2 /* $Header: OKLRCRSB.pls 120.15 2006/09/26 11:24:46 varangan noship $ */
3
4 -- Global Message Constants
5 G_INVALID_STS_APPROVED CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_INVAL_STS_APPROVED'; -- token CHANGE_REQ_NUM
6
7 -- Global lookup_code constants
8 G_PENDING_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'PENDING_APPROVAL';
9 G_APPROVED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'APPROVED';
10 G_ARGREEMENT_TYPE_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'AGREEMENT';
11 G_ASSOCIATE_TYPE_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'ASSOCIATION';
12 G_PROGRAM_SCS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'PROGRAM';
13 G_OPERATING_SCS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'OPERATING';
14 G_VENDOR_PROGRAM_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'VENDOR_PROGRAM';
15 G_ABANDONED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'ABANDONED';
16 G_COMPLETED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'COMPLETED';
17
18 SUBTYPE khrv_rec_type IS okl_contract_pub.khrv_rec_type;
19 SUBTYPE chrv_rec_type IS okl_okc_migration_pvt.chrv_rec_type;
20 SUBTYPE cplv_rec_type IS okl_okc_migration_pvt.cplv_rec_type;
21 SUBTYPE catv_rec_type IS okl_okc_migration_a_pvt.catv_rec_type;
22 SUBTYPE ctcv_tbl_type IS okl_okc_migration_pvt.ctcv_tbl_type;
23 SUBTYPE vasv_rec_type IS okl_vas_pvt.vasv_rec_type;
24 SUBTYPE ech_rec_type IS okl_ech_pvt.okl_ech_rec;
25 SUBTYPE ecl_tbl_type IS okl_ecl_pvt.okl_ecl_tbl;
26 SUBTYPE ecv_tbl_type IS okl_ecv_pvt.okl_ecv_tbl;
27
28
29 PROCEDURE sync_change_request(p_api_version IN NUMBER
30 ,p_init_msg_list IN VARCHAR2
31 ,x_return_status OUT NOCOPY VARCHAR2
32 ,x_msg_count OUT NOCOPY NUMBER
33 ,x_msg_data OUT NOCOPY VARCHAR2
34 ,p_change_request_id IN okl_vp_change_requests.id%TYPE) IS
35 -- cursor that determines the change request and associated agreement details
36 -- for an AGREEMENT type of change request, the newly created agreement id is returned
37 -- and for ASSOCIATION type of change request, the original agreement id is returned.
38 CURSOR c_get_cr_details_csr (cp_change_request_id okl_vp_change_requests.id%TYPE)IS
39 SELECT creq.change_type_code
40 ,creq.status_code
41 ,change_request_number
42 ,creq.chr_id orig_agr_chr_id
43 ,chr.scs_code agreement_category
44 FROM okl_vp_change_requests creq
45 ,okc_k_headers_b chr
46 WHERE creq.id = cp_change_request_id
47 AND creq.chr_id = chr.id;
48
49 -- cursor to find out the agreement id that was created for the change request. this agreement id
50 -- is not the parent agreement id, but the one that was created using the copy api
51 CURSOR c_get_creq_chr_id (cp_change_request_id okl_vp_change_requests.id%TYPE)IS
52 SELECT id
53 FROM okl_k_headers
54 WHERE crs_id = cp_change_request_id;
55
56 cv_get_cr_details c_get_cr_details_csr%ROWTYPE;
57 lv_vcrv_rec okl_vcr_pvt.vcrv_rec_type;
58 x_vcrv_rec okl_vcr_pvt.vcrv_rec_type;
59
60 lv_creq_chr_id okc_k_headers_b.id%TYPE;
61 lv_orig_chr_id okc_k_headers_b.id%TYPE;
62
63 l_api_version CONSTANT NUMBER DEFAULT 1.0;
64 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_CHANGE_REQUEST';
65 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_CHANGE_REQUEST';
66 l_debug_enabled VARCHAR2(10);
67
68 BEGIN
69 x_return_status := OKL_API.G_RET_STS_SUCCESS;
70
71 l_debug_enabled := okl_debug_pub.check_log_enabled;
72
73 IF(l_debug_enabled='Y') THEN
74 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_change_request');
75 END IF;
76
77 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
78 x_return_status := OKL_API.START_ACTIVITY(
79 p_api_name => l_api_name
80 ,p_pkg_name => G_PKG_NAME
81 ,p_init_msg_list => p_init_msg_list
82 ,l_api_version => l_api_version
83 ,p_api_version => p_api_version
84 ,p_api_type => g_api_type
85 ,x_return_status => x_return_status);
86 -- check if activity started successfully
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
93 -- fetch the change request information
94 OPEN c_get_cr_details_csr(p_change_request_id); FETCH c_get_cr_details_csr INTO cv_get_cr_details;
95 CLOSE c_get_cr_details_csr;
96
97 -- if the change request is not in Pending Approval status, error out
98 IF(cv_get_cr_details.status_code NOT IN (G_PENDING_STS_CODE, G_APPROVED_STS_CODE))THEN
99 OKL_API.set_message(G_APP_NAME, G_INVALID_STS_APPROVED, 'CHANGE_REQ_NUM',cv_get_cr_details.change_request_number);
100 RAISE OKL_API.G_EXCEPTION_ERROR;
101 END IF;
102
103 -- store the value of the original agreement id here. this would be useful in calling the sync procedures
104 lv_orig_chr_id := cv_get_cr_details.orig_agr_chr_id;
105 -- now fork based on change request type
106 IF(G_ARGREEMENT_TYPE_CODE = cv_get_cr_details.change_type_code)THEN
107 -- get the agreement associated with the change request. this is achieved by getting the id from okl_k_headers
108 -- that has the supplied change request in the crs_id column, this cursor results in a full table scan of okl_k_headers
109 -- the agreement associated with the change request is the agreement that was created from the parent agreement by calling the copy api
110 OPEN c_get_creq_chr_id (p_change_request_id); FETCH c_get_creq_chr_id INTO lv_creq_chr_id;
111 CLOSE c_get_creq_chr_id;
112
113 IF(l_debug_enabled='Y') THEN
114 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT, l_module, 'p_orig_chr_id '|| lv_orig_chr_id||' p_creq_chr_id '||lv_creq_chr_id);
115 END IF;
116
117 -- there are some steps common between AGREEMENT and PROGRAM, perform them first
118 IF(G_OPERATING_SCS_CODE = cv_get_cr_details.agreement_category OR G_PROGRAM_SCS_CODE = cv_get_cr_details.agreement_category)THEN
119
120 -- 1. synchronize header changes
121 sync_agr_header(p_api_version => p_api_version
122 ,p_init_msg_list => p_init_msg_list
123 ,x_return_status => x_return_status
124 ,x_msg_count => x_msg_count
125 ,x_msg_data => x_msg_data
126 ,p_orig_chr_id => lv_orig_chr_id
127 ,p_creq_chr_id => lv_creq_chr_id
128 );
129 IF(l_debug_enabled='Y') THEN
130 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
131 l_module,
132 'sync_agr_header returned with status '||x_return_status
133 );
134 END IF;
135 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
136 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
137 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
138 RAISE OKL_API.G_EXCEPTION_ERROR;
139 END IF;
140
141 -- 2. synchronize non primary parties
142 sync_non_primary_parties(p_api_version => p_api_version
143 ,p_init_msg_list => p_init_msg_list
144 ,x_return_status => x_return_status
145 ,x_msg_count => x_msg_count
146 ,x_msg_data => x_msg_data
147 ,p_orig_chr_id => lv_orig_chr_id
148 ,p_creq_chr_id => lv_creq_chr_id
149 );
150 IF(l_debug_enabled='Y') THEN
151 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
152 l_module,
153 'sync_non_primary_parties returned with status '||x_return_status
154 );
155 END IF;
156 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
157 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
158 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
159 RAISE OKL_API.G_EXCEPTION_ERROR;
160 END IF;
161
162 -- 3. synchronize contacts for all parties
163 sync_party_contacts(p_api_version => p_api_version
164 ,p_init_msg_list => p_init_msg_list
165 ,x_return_status => x_return_status
166 ,x_msg_count => x_msg_count
167 ,x_msg_data => x_msg_data
168 ,p_orig_chr_id => lv_orig_chr_id
169 ,p_creq_chr_id => lv_creq_chr_id
170 );
171 IF(l_debug_enabled='Y') THEN
172 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
173 l_module,
174 'sync_party_contacts returned with status '||x_return_status
175 );
176 END IF;
177 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
178 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
179 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
180 RAISE OKL_API.G_EXCEPTION_ERROR;
181 END IF;
182
183 -- 4. synchronize changes to articles
184 sync_article_changes(p_api_version => p_api_version
185 ,p_init_msg_list => p_init_msg_list
186 ,x_return_status => x_return_status
187 ,x_msg_count => x_msg_count
188 ,x_msg_data => x_msg_data
189 ,p_orig_chr_id => lv_orig_chr_id
190 ,p_creq_chr_id => lv_creq_chr_id
191 );
192 IF(l_debug_enabled='Y') THEN
193 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
194 l_module,
195 'sync_article_changes returned with status '||x_return_status
196 );
197 END IF;
198 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
199 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
200 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
201 RAISE OKL_API.G_EXCEPTION_ERROR;
202 END IF;
203
204 -- 5. synchronize changes to the Vendor Billing Information
205 sync_vendor_billing(p_api_version => p_api_version
206 ,p_init_msg_list => p_init_msg_list
207 ,x_return_status => x_return_status
208 ,x_msg_count => x_msg_count
209 ,x_msg_data => x_msg_data
210 ,p_orig_chr_id => lv_orig_chr_id
211 ,p_creq_chr_id => lv_creq_chr_id
212 );
213 IF(l_debug_enabled='Y') THEN
214 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
215 l_module,
216 'sync_vendor_billing returned with status '||x_return_status
217 );
218 END IF;
219 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
220 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
221 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
222 RAISE OKL_API.G_EXCEPTION_ERROR;
223 END IF;
224
225 END IF; -- end of G_OPERATING_SCS_CODE = cv_get_cr_details.agreement_category OR G_PROGRAM_SCS_CODE = cv_get_cr_details.agreement_category
226
227 IF(G_PROGRAM_SCS_CODE = cv_get_cr_details.agreement_category)THEN
228 -- these are PROGRAM specific changes only
229 -- 1. synchronize changes to associations
230 sync_agr_associations(p_api_version => p_api_version
231 ,p_init_msg_list => p_init_msg_list
232 ,x_return_status => x_return_status
233 ,x_msg_count => x_msg_count
234 ,x_msg_data => x_msg_data
235 ,p_orig_chr_id => lv_orig_chr_id
236 ,p_creq_chr_id => lv_creq_chr_id
237 ,p_change_request_id => p_change_request_id
238 );
239 IF(l_debug_enabled='Y') THEN
240 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
241 l_module,
242 'sync_agr_associations returned with status '||x_return_status
243 );
244 END IF;
245 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
246 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
247 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
248 RAISE OKL_API.G_EXCEPTION_ERROR;
249 END IF;
250
251 -- 2. synchronize changes to eligibility criteria
252 sync_elig_criteria(p_api_version => p_api_version
253 ,p_init_msg_list => p_init_msg_list
254 ,x_return_status => x_return_status
255 ,x_msg_count => x_msg_count
256 ,x_msg_data => x_msg_data
257 ,p_orig_chr_id => lv_orig_chr_id
258 ,p_creq_chr_id => lv_creq_chr_id
259 );
260 IF(l_debug_enabled='Y') THEN
261 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
262 l_module,
263 'sync_elig_criteria returned with status '||x_return_status
264 );
265 END IF;
266 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
267 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
268 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
269 RAISE OKL_API.G_EXCEPTION_ERROR;
270 END IF;
271
272
273 -- 3. synchronize changes terms and conditions
274 sync_terms(p_api_version => p_api_version
275 ,p_init_msg_list => p_init_msg_list
276 ,x_return_status => x_return_status
277 ,x_msg_count => x_msg_count
278 ,x_msg_data => x_msg_data
279 ,p_orig_chr_id => lv_orig_chr_id
280 ,p_creq_chr_id => lv_creq_chr_id
281 );
282 IF(l_debug_enabled='Y') THEN
283 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
284 l_module,
285 'sync_terms returned with status '||x_return_status
286 );
287 END IF;
288 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
289 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
290 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
291 RAISE OKL_API.G_EXCEPTION_ERROR;
292 END IF;
293
294
295 -- 4. synchronize changes to the disbursement setup
296 sync_vendor_disb_setup(p_api_version => p_api_version
297 ,p_init_msg_list => p_init_msg_list
298 ,x_return_status => x_return_status
299 ,x_msg_count => x_msg_count
300 ,x_msg_data => x_msg_data
301 ,p_orig_chr_id => lv_orig_chr_id
302 ,p_creq_chr_id => lv_creq_chr_id
303 );
304 IF(l_debug_enabled='Y') THEN
305 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
306 l_module,
307 'sync_vendor_disb_setup returned with status '||x_return_status
308 );
309 END IF;
310 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
311 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
312 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
313 RAISE OKL_API.G_EXCEPTION_ERROR;
314 END IF;
315
316 END IF; -- end of G_PROGRAM_SCS_CODE = cv_get_cr_details_csr.agreement_category
317 -- now that synchronization has happened, the agreement status has to be updated to ABANDONED
318 okl_contract_status_pub.update_contract_status(p_api_version => p_api_version
319 ,p_init_msg_list => p_init_msg_list
320 ,x_return_status => x_return_status
321 ,x_msg_count => x_msg_count
322 ,x_msg_data => x_msg_data
323 ,p_khr_status => G_ABANDONED_STS_CODE
324 ,p_chr_id => lv_creq_chr_id
325 );
326 IF(l_debug_enabled='Y') THEN
327 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
328 l_module,
329 'okl_contract_status_pub.update_contract_status G_ABANDONED_STS_CODE returned with status '||x_return_status
330 );
331 END IF;
332 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
333 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
334 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
335 RAISE OKL_API.G_EXCEPTION_ERROR;
336 END IF;
337 ELSIF(G_ASSOCIATE_TYPE_CODE = cv_get_cr_details.change_type_code)THEN
338 -- 1. synchronize changes to the associations
339 sync_associations(p_api_version => p_api_version
340 ,p_init_msg_list => p_init_msg_list
341 ,x_return_status => x_return_status
342 ,x_msg_count => x_msg_count
343 ,x_msg_data => x_msg_data
344 ,p_orig_chr_id => lv_orig_chr_id
345 ,p_creq_chr_id => lv_orig_chr_id -- for ASSOCIATION type of change request, there is no additional agreement created
346 ,p_change_request_id => p_change_request_id
347 );
348 IF(l_debug_enabled='Y') THEN
349 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
350 l_module,
351 'sync_associations for ASSOCIATION returned with status '||x_return_status
352 );
353 END IF;
354 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
355 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
356 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
357 RAISE OKL_API.G_EXCEPTION_ERROR;
358 END IF;
359 END IF; -- end of G_ARGREEMENT_TYPE_CODE = cv_get_cr_details_csr.change_type_code
360
361 -- set the status of the change request to COMPLETED
362 lv_vcrv_rec.id := p_change_request_id;
363 lv_vcrv_rec.status_code := G_COMPLETED_STS_CODE;
364 -- we should also record the applied date, the date on which the sync happens on the change request
365 lv_vcrv_rec.applied_date := TRUNC(SYSDATE);
366 okl_vp_change_request_pvt.update_change_request_header(p_api_version => p_api_version
367 ,p_init_msg_list => p_init_msg_list
368 ,x_return_status => x_return_status
369 ,x_msg_count => x_msg_count
370 ,x_msg_data => x_msg_data
371 ,p_vcrv_rec => lv_vcrv_rec
372 ,x_vcrv_rec => x_vcrv_rec
373 );
374 IF(l_debug_enabled='Y') THEN
375 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
376 l_module,
377 'okl_vp_change_request_pvt.update_change_request_header returned with status '||x_return_status
378 );
379 END IF;
380
381 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
382 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
383 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
384 RAISE OKL_API.G_EXCEPTION_ERROR;
385 END IF;
386
387 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
388
389 IF(l_debug_enabled='Y') THEN
390 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_change_request');
391 END IF;
392 EXCEPTION
393 WHEN OKL_API.G_EXCEPTION_ERROR THEN
394 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
395 p_api_name => l_api_name,
396 p_pkg_name => G_PKG_NAME,
397 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
398 x_msg_count => x_msg_count,
399 x_msg_data => x_msg_data,
400 p_api_type => g_api_type);
401 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
402 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
403 p_api_name => l_api_name,
404 p_pkg_name => G_PKG_NAME,
405 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
406 x_msg_count => x_msg_count,
407 x_msg_data => x_msg_data,
408 p_api_type => g_api_type);
409 WHEN OTHERS THEN
410 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
411 p_api_name => l_api_name,
412 p_pkg_name => G_PKG_NAME,
413 p_exc_name => 'OTHERS',
414 x_msg_count => x_msg_count,
415 x_msg_data => x_msg_data,
416 p_api_type => g_api_type);
417 END sync_change_request;
418
419 PROCEDURE sync_agr_header(p_api_version IN NUMBER
420 ,p_init_msg_list IN VARCHAR2
421 ,x_return_status OUT NOCOPY VARCHAR2
422 ,x_msg_count OUT NOCOPY NUMBER
423 ,x_msg_data OUT NOCOPY VARCHAR2
424 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
425 ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
426 ) IS
427 CURSOR c_hdr_attribs_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
428 SELECT chr.short_description
429 ,chr.comments
430 ,chr.description
431 ,chr.end_date
432 ,chr.start_date
433 ,chr.qcl_id
434 ,chr.contract_number
435 ,chr.scs_code
436 ,chr.template_yn
437 ,gov.chr_id_referred
438 ,khr.attribute_category
439 ,khr.attribute1
440 ,khr.attribute2
441 ,khr.attribute3
442 ,khr.attribute4
443 ,khr.attribute5
444 ,khr.attribute6
445 ,khr.attribute7
446 ,khr.attribute8
447 ,khr.attribute9
448 ,khr.attribute10
449 ,khr.attribute11
450 ,khr.attribute12
451 ,khr.attribute13
452 ,khr.attribute14
453 ,khr.attribute15
454 FROM okc_k_headers_v chr
455 ,okc_governances gov
456 ,okl_k_headers khr
457 WHERE chr.id = cp_chr_id
458 AND chr.id = khr.id
459 AND gov.dnz_chr_id(+) = chr.id;
460 cv_hdr_attribs_orig c_hdr_attribs_csr%ROWTYPE;
461 cv_hdr_attribs_new c_hdr_attribs_csr%ROWTYPE;
462
463 l_agr_hdr_rec okl_vendor_program_pvt.program_header_rec_type;
464
465 l_api_version CONSTANT NUMBER DEFAULT 1.0;
466 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_AGR_HEADER';
467 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_AGR_HEADER';
468 l_debug_enabled VARCHAR2(10);
469
470 BEGIN
471 x_return_status := OKL_API.G_RET_STS_SUCCESS;
472
473 l_debug_enabled := okl_debug_pub.check_log_enabled;
474
475 IF(l_debug_enabled='Y') THEN
476 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_agr_header');
477 END IF;
478
479 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
480 x_return_status := OKL_API.START_ACTIVITY(
481 p_api_name => l_api_name
482 ,p_pkg_name => G_PKG_NAME
483 ,p_init_msg_list => p_init_msg_list
484 ,l_api_version => l_api_version
485 ,p_api_version => p_api_version
486 ,p_api_type => g_api_type
487 ,x_return_status => x_return_status);
488 -- check if activity started successfully
489 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
490 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
491 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
492 RAISE OKL_API.G_EXCEPTION_ERROR;
493 END IF;
494
495 -- get the old and new values into the cursor variables
496 OPEN c_hdr_attribs_csr(p_orig_chr_id); FETCH c_hdr_attribs_csr INTO cv_hdr_attribs_orig;
497 CLOSE c_hdr_attribs_csr;
498
499 OPEN c_hdr_attribs_csr(p_creq_chr_id); FETCH c_hdr_attribs_csr INTO cv_hdr_attribs_new;
500 CLOSE c_hdr_attribs_csr;
501
502 -- now compare the attributes, and if there are any differences only then call the API.
503 -- the comparision would save a costly DML call
504 IF(
505 (NVL(cv_hdr_attribs_orig.short_description, OKL_API.G_MISS_CHAR) <> NVL(cv_hdr_attribs_new.short_description, OKL_API.G_MISS_CHAR))
506 OR (NVL(cv_hdr_attribs_orig.comments, OKL_API.G_MISS_CHAR) <> NVL(cv_hdr_attribs_new.comments, OKL_API.G_MISS_CHAR))
507 OR (NVL(cv_hdr_attribs_orig.description, OKL_API.G_MISS_CHAR) <> NVL(cv_hdr_attribs_new.description, OKL_API.G_MISS_CHAR))
508 OR (NVL(cv_hdr_attribs_orig.end_date, OKL_API.G_MISS_DATE) <> NVL(cv_hdr_attribs_new.end_date, OKL_API.G_MISS_DATE))
509 OR (NVL(cv_hdr_attribs_orig.chr_id_referred, OKL_API.G_MISS_NUM) <> NVL(cv_hdr_attribs_new.chr_id_referred, OKL_API.G_MISS_NUM))
510 )THEN
511
512 l_agr_hdr_rec.p_agreement_number := cv_hdr_attribs_orig.contract_number;
513 l_agr_hdr_rec.p_contract_category := cv_hdr_attribs_orig.scs_code;
514 l_agr_hdr_rec.p_start_date := cv_hdr_attribs_orig.start_date;
515 l_agr_hdr_rec.p_end_date := TRUNC(cv_hdr_attribs_new.end_date);
516 l_agr_hdr_rec.p_short_description := cv_hdr_attribs_new.short_description;
517 l_agr_hdr_rec.p_description := cv_hdr_attribs_new.description;
518 l_agr_hdr_rec.p_comments := cv_hdr_attribs_new.comments;
519 l_agr_hdr_rec.p_template_yn := cv_hdr_attribs_orig.template_yn;
520 l_agr_hdr_rec.p_qcl_id := cv_hdr_attribs_orig.qcl_id;
521 l_agr_hdr_rec.p_referred_id := NULL; -- guess this is not being used anymore
522
523 -- added to enable sync dff from the change request to the pa. START
524 -- since dff is not on the primary list of fields that could be modified, the dff sync up will only
525 -- happen if one of the primary fields are modified. viz. short_desription, comments, description, end_date and/or chr_id_referred
526 l_agr_hdr_rec.p_attribute_category := cv_hdr_attribs_new.attribute_category;
527 l_agr_hdr_rec.p_attribute1 := cv_hdr_attribs_new.attribute1;
528 l_agr_hdr_rec.p_attribute2 := cv_hdr_attribs_new.attribute2;
529 l_agr_hdr_rec.p_attribute3 := cv_hdr_attribs_new.attribute3;
530 l_agr_hdr_rec.p_attribute4 := cv_hdr_attribs_new.attribute4;
531 l_agr_hdr_rec.p_attribute5 := cv_hdr_attribs_new.attribute5;
532 l_agr_hdr_rec.p_attribute6 := cv_hdr_attribs_new.attribute6;
533 l_agr_hdr_rec.p_attribute7 := cv_hdr_attribs_new.attribute7;
534 l_agr_hdr_rec.p_attribute8 := cv_hdr_attribs_new.attribute8;
535 l_agr_hdr_rec.p_attribute9 := cv_hdr_attribs_new.attribute9;
536 l_agr_hdr_rec.p_attribute10 := cv_hdr_attribs_new.attribute10;
537 l_agr_hdr_rec.p_attribute11 := cv_hdr_attribs_new.attribute11;
538 l_agr_hdr_rec.p_attribute12 := cv_hdr_attribs_new.attribute12;
539 l_agr_hdr_rec.p_attribute13 := cv_hdr_attribs_new.attribute13;
540 l_agr_hdr_rec.p_attribute14 := cv_hdr_attribs_new.attribute14;
541 l_agr_hdr_rec.p_attribute15 := cv_hdr_attribs_new.attribute15;
542 -- added to enable sync dff from the change request to the pa. END
543
544 okl_vendor_program_pvt.update_program(p_api_version => p_api_version
545 ,p_init_msg_list => p_init_msg_list
546 ,x_return_status => x_return_status
547 ,x_msg_count => x_msg_count
548 ,x_msg_data => x_msg_data
549 ,p_hdr_rec => l_agr_hdr_rec
550 ,p_program_id => p_orig_chr_id
551 ,p_parent_agreement_id => cv_hdr_attribs_new.chr_id_referred
552 );
553 IF(l_debug_enabled='Y') THEN
554 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
555 l_module,
556 'okl_vendor_program_pvt.update_program returned with status '||x_return_status||' x_msg_data '||x_msg_data
557 );
558 END IF;
559
560 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
561 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
562 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
563 RAISE OKL_API.G_EXCEPTION_ERROR;
564 END IF;
565 END IF; -- end of difference check
566
567
568 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
569
570 IF(l_debug_enabled='Y') THEN
571 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_agr_header');
572 END IF;
573 EXCEPTION
574 WHEN OKL_API.G_EXCEPTION_ERROR THEN
575 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
576 p_api_name => l_api_name,
577 p_pkg_name => G_PKG_NAME,
578 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
579 x_msg_count => x_msg_count,
580 x_msg_data => x_msg_data,
581 p_api_type => g_api_type);
582 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
583 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
584 p_api_name => l_api_name,
585 p_pkg_name => G_PKG_NAME,
586 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
587 x_msg_count => x_msg_count,
588 x_msg_data => x_msg_data,
589 p_api_type => g_api_type);
590 WHEN OTHERS THEN
591 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
592 p_api_name => l_api_name,
593 p_pkg_name => G_PKG_NAME,
594 p_exc_name => 'OTHERS',
595 x_msg_count => x_msg_count,
596 x_msg_data => x_msg_data,
597 p_api_type => g_api_type);
598 END sync_agr_header;
599
600 PROCEDURE sync_non_primary_parties(p_api_version IN NUMBER
601 ,p_init_msg_list IN VARCHAR2
602 ,x_return_status OUT NOCOPY VARCHAR2
603 ,x_msg_count OUT NOCOPY NUMBER
604 ,x_msg_data OUT NOCOPY VARCHAR2
605 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
606 ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
607 ) IS
608 -- cursor that finds new non primary parties on the change request
609 CURSOR new_parties_csr(cp_creq_chr_id okc_k_headers_b.id%TYPE
610 ,cp_orig_chr_id okc_k_headers_b.id%TYPE) IS
611 SELECT cpl.id,
612 cpl.object1_id1,
613 cpl.object1_id2,
614 cpl.jtot_object1_code,
615 cpl.rle_code
616 FROM okc_k_party_roles_b cpl
617 WHERE cpl.chr_id = cp_creq_chr_id
618 AND cpl.jtot_object1_code = 'OKX_PARTY'
619 AND NOT EXISTS (
620 SELECT 'Y'
621 FROM okc_k_party_roles_b orig
622 WHERE orig.object1_id1 = cpl.object1_id1
623 AND orig.object1_id2 = cpl.object1_id2
624 AND orig.jtot_object1_code = cpl.jtot_object1_code
625 AND orig.rle_code = cpl.rle_code
626 AND orig.chr_id = cp_orig_chr_id
627 AND orig.jtot_object1_code = 'OKX_PARTY'
628 );
629 CURSOR c_get_parties_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
630 SELECT id
631 ,chr_id
632 ,rle_code
633 ,dnz_chr_id
634 ,object1_id1
635 ,object1_id2
636 ,jtot_object1_code
637 ,cognomen party_known_as
638 ,alias
639 ,created_by
640 ,creation_date
641 ,last_updated_by
642 ,last_update_date
643 ,last_update_login
644 FROM okc_k_party_roles_v
645 WHERE dnz_chr_id = cp_chr_id
646 AND jtot_object1_code = 'OKX_PARTY'; -- this indicates non primary parties
647
648 CURSOR c_get_party_info_csr(cp_chr_id okc_k_headers_b.id%TYPE
649 ,cp_party_id hz_parties.party_id%TYPE) IS
650 SELECT cpl.id
651 ,cpl.chr_id
652 ,cpl.rle_code
653 ,cpl.dnz_chr_id
654 ,cpl.object1_id1
655 ,cpl.object1_id2
656 ,cpl.jtot_object1_code
657 ,cpl.cognomen party_known_as
658 ,cpl.alias
659 /*
660 ,kpl.attribute_category
661 ,kpl.attribute1
662 ,kpl.attribute2
663 ,kpl.attribute3
664 ,kpl.attribute4
665 ,kpl.attribute5
666 ,kpl.attribute6
667 ,kpl.attribute7
668 ,kpl.attribute8
669 ,kpl.attribute9
670 ,kpl.attribute10
671 ,kpl.attribute11
672 ,kpl.attribute12
673 ,kpl.attribute13
674 ,kpl.attribute14
675 ,kpl.attribute15
676 */
677 FROM okc_k_party_roles_v cpl
678 /* ,okl_k_party_roles_v kpl */
679 WHERE cpl.dnz_chr_id = cp_chr_id
680 AND cpl.object1_id1 = cp_party_id
681 AND cpl.jtot_object1_code = 'OKX_PARTY'; -- this indicates non primary parties
682 /* AND cpl.id = kpl.id;*/
683
684 cv_get_party_info_rec c_get_party_info_csr%ROWTYPE;
685
686 CURSOR c_get_role_contacts_csr(cp_cpl_id okc_k_party_roles_b.id%TYPE)IS
687 SELECT id
688 FROM okc_contacts
689 WHERE cpl_id = cp_cpl_id;
690
691 lv_cplv_rec cplv_rec_type;
692 x_cplv_rec cplv_rec_type;
693 lv_ctcv_tbl ctcv_tbl_type;
694 contact_tbl_idx PLS_INTEGER;
695
696 x_cpl_id okc_k_party_roles_b.cpl_id%TYPE;
697 l_api_version CONSTANT NUMBER DEFAULT 1.0;
698 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_NON_PRIMARY_PARTIES';
699 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_NON_PRIMARY_PARTIES';
700 l_debug_enabled VARCHAR2(10);
701
702 BEGIN
703 x_return_status := OKL_API.G_RET_STS_SUCCESS;
704
705 l_debug_enabled := okl_debug_pub.check_log_enabled;
706
707 IF(l_debug_enabled='Y') THEN
708 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_non_primary_parties');
709 END IF;
710
711 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
712 x_return_status := OKL_API.START_ACTIVITY(
713 p_api_name => l_api_name
714 ,p_pkg_name => G_PKG_NAME
715 ,p_init_msg_list => p_init_msg_list
716 ,l_api_version => l_api_version
717 ,p_api_version => p_api_version
718 ,p_api_type => g_api_type
719 ,x_return_status => x_return_status);
720 -- check if activity started successfully
721 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
722 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
723 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
724 RAISE OKL_API.G_EXCEPTION_ERROR;
725 END IF;
726
727 -- find any changes that have happened on the non primary parties on the agreement
728 -- this includes deletion of non primary parties on the change request. these non primary parties have to be
729 -- deleted from the originating agreement also
730
731 -- first get the original party role record
732 FOR parties_rec IN c_get_parties_csr(p_orig_chr_id) LOOP
733 lv_cplv_rec := NULL;
734 x_cplv_rec := NULL;
735 -- since the object1_id1 is the same for the change request and the original pa, get the change request record from these values
736 OPEN c_get_party_info_csr(p_creq_chr_id, parties_rec.object1_id1); FETCH c_get_party_info_csr INTO cv_get_party_info_rec;
737 IF(c_get_party_info_csr%FOUND)THEN
738 CLOSE c_get_party_info_csr;
739 -- compare if the values of known as or alias have changed on the new change request from the agreement.
740 -- call the update api only if there are changes to these two fields
741 IF(
742 (NVL(cv_get_party_info_rec.party_known_as, OKL_API.G_MISS_CHAR) <> NVL(parties_rec.party_known_as,OKL_API.G_MISS_CHAR))
743 OR (NVL(cv_get_party_info_rec.alias, OKL_API.G_MISS_CHAR) <> NVL(parties_rec.alias, OKL_API.G_MISS_CHAR))
744 )THEN
745 lv_cplv_rec.id := parties_rec.id;
746 lv_cplv_rec.chr_id := parties_rec.chr_id;
747 lv_cplv_rec.rle_code := parties_rec.rle_code;
748 lv_cplv_rec.dnz_chr_id := parties_rec.dnz_chr_id;
749 lv_cplv_rec.object1_id1 := parties_rec.object1_id1;
750 lv_cplv_rec.object1_id2 := parties_rec.object1_id2;
751 lv_cplv_rec.jtot_object1_code := parties_rec.jtot_object1_code;
752 lv_cplv_rec.cognomen := cv_get_party_info_rec.party_known_as;
753 lv_cplv_rec.alias := cv_get_party_info_rec.alias;
754 /*
755 -- party role dff added for dff implementation for party role. the flex field segment values are synchronized only when
756 -- the significant fields on the party are changed. just changing the dff values would not sync on the change request
757 lv_cplv_rec.attribute_category := cv_get_party_info_rec.attribute_category;
758 lv_cplv_rec.attribute1 := cv_get_party_info_rec.attribute1;
759 lv_cplv_rec.attribute2 := cv_get_party_info_rec.attribute2;
760 lv_cplv_rec.attribute3:= cv_get_party_info_rec.attribute3;
761 lv_cplv_rec.attribute4 := cv_get_party_info_rec.attribute4;
762 lv_cplv_rec.attribute5 := cv_get_party_info_rec.attribute5;
763 lv_cplv_rec.attribute6 := cv_get_party_info_rec.attribute6;
764 lv_cplv_rec.attribute7 := cv_get_party_info_rec.attribute7;
765 lv_cplv_rec.attribute8 := cv_get_party_info_rec.attribute8;
766 lv_cplv_rec.attribute9 := cv_get_party_info_rec.attribute9;
767 lv_cplv_rec.attribute10 := cv_get_party_info_rec.attribute10;
768 lv_cplv_rec.attribute11 := cv_get_party_info_rec.attribute11;
769 lv_cplv_rec.attribute12 := cv_get_party_info_rec.attribute12;
770 lv_cplv_rec.attribute13 := cv_get_party_info_rec.attribute13;
771 lv_cplv_rec.attribute14 := cv_get_party_info_rec.attribute14;
772 lv_cplv_rec.attribute15 := cv_get_party_info_rec.attribute15;
773 */
774 lv_cplv_rec.created_by := parties_rec.created_by;
775 lv_cplv_rec.creation_date := parties_rec.creation_date;
776 lv_cplv_rec.last_updated_by := parties_rec.last_updated_by;
777 lv_cplv_rec.last_update_date := parties_rec.last_update_date;
778 lv_cplv_rec.last_update_login := parties_rec.last_update_login;
779
780 okl_contract_party_pub.update_k_party_role(p_api_version => p_api_version
781 ,p_init_msg_list => p_init_msg_list
782 ,x_return_status => x_return_status
783 ,x_msg_count => x_msg_count
784 ,x_msg_data => x_msg_data
785 ,p_cplv_rec => lv_cplv_rec
786 ,x_cplv_rec => x_cplv_rec
787 );
788 IF(l_debug_enabled='Y') THEN
789 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
790 l_module,
791 'okl_contract_party_pub.update_k_party_role returned with status '||x_return_status
792 );
793 END IF;
794 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
795 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
796 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
797 RAISE OKL_API.G_EXCEPTION_ERROR;
798 END IF;
799 END IF; -- end of comparision
800 ELSE -- cursor not found case
801 -- this is the case of the party record is not on the change request. this implies that the non primary party has been
802 -- deleted. call the delete_k_party_role api on the original agreement. But before that make sure that the party role does not have
803 -- any contacts. First delete the children and then delete the parent
804 CLOSE c_get_party_info_csr;
805 contact_tbl_idx := 0;
806 FOR party_contacts_rec IN c_get_role_contacts_csr(parties_rec.id) LOOP
807 contact_tbl_idx := contact_tbl_idx + 1;
808 lv_ctcv_tbl(contact_tbl_idx).id := party_contacts_rec.id;
809 END LOOP;
810 IF(contact_tbl_idx > 0)THEN
811 okl_contract_party_pub.delete_contact(p_api_version => p_api_version
812 ,p_init_msg_list => p_init_msg_list
813 ,x_return_status => x_return_status
814 ,x_msg_count => x_msg_count
815 ,x_msg_data => x_msg_data
816 ,p_ctcv_tbl => lv_ctcv_tbl
817 );
818 IF(l_debug_enabled='Y') THEN
819 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
820 l_module,
821 'okl_contract_party_pub.delete_contact returned with status '||x_return_status||' contact_tbl_idx '||contact_tbl_idx
822 );
823 END IF;
824 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
825 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
826 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
827 RAISE OKL_API.G_EXCEPTION_ERROR;
828 END IF;
829 END IF;
830 -- now its safe to delete the parent
831 lv_cplv_rec.id := parties_rec.id;
832 okl_contract_party_pub.delete_k_party_role(p_api_version => p_api_version
833 ,p_init_msg_list => p_init_msg_list
834 ,x_return_status => x_return_status
835 ,x_msg_count => x_msg_count
836 ,x_msg_data => x_msg_data
837 ,p_cplv_rec => lv_cplv_rec
838 );
839 IF(l_debug_enabled='Y') THEN
840 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
841 l_module,
842 'okl_contract_party_pub.delete_k_party_role returned with status '||x_return_status
843 );
844 END IF;
845 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
846 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
847 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
848 RAISE OKL_API.G_EXCEPTION_ERROR;
849 END IF;
850 END IF; -- end of cursor found case
851 END LOOP;
852
853 -- synchronize all the non primary parties that have been created on the change request
854 -- cannot rely on primary_yn of okc_k_party_roles_b as this value is null always
855 FOR new_parties_rec IN new_parties_csr(p_creq_chr_id, p_orig_chr_id) LOOP
856 okl_copy_contract_pub.copy_party_roles(p_api_version => 1.0
857 ,p_init_msg_list => OKL_API.G_FALSE
858 ,x_return_status => x_return_status
859 ,x_msg_count => x_msg_count
860 ,x_msg_data => x_msg_data
861 ,p_cpl_id => new_parties_rec.id
862 ,p_cle_id => NULL
863 ,p_chr_id => p_orig_chr_id
864 ,p_rle_code => new_parties_rec.rle_code
865 ,x_cpl_id => x_cpl_id
866 );
867 IF(l_debug_enabled='Y') THEN
868 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
869 l_module,
870 'okl_copy_contract_pub.copy_party_roles returned with status '||x_return_status
871 );
872 END IF;
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 LOOP;
879
880 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
881
882 IF(l_debug_enabled='Y') THEN
883 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_non_primary_parties');
884 END IF;
885 EXCEPTION
886 WHEN OKL_API.G_EXCEPTION_ERROR THEN
887 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
888 p_api_name => l_api_name,
889 p_pkg_name => G_PKG_NAME,
890 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
891 x_msg_count => x_msg_count,
892 x_msg_data => x_msg_data,
893 p_api_type => g_api_type);
894 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
895 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
896 p_api_name => l_api_name,
897 p_pkg_name => G_PKG_NAME,
898 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
899 x_msg_count => x_msg_count,
900 x_msg_data => x_msg_data,
901 p_api_type => g_api_type);
902 WHEN OTHERS THEN
903 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
904 p_api_name => l_api_name,
905 p_pkg_name => G_PKG_NAME,
906 p_exc_name => 'OTHERS',
907 x_msg_count => x_msg_count,
908 x_msg_data => x_msg_data,
909 p_api_type => g_api_type);
910 END sync_non_primary_parties;
911
912 PROCEDURE sync_party_contacts(p_api_version IN NUMBER
913 ,p_init_msg_list IN VARCHAR2
914 ,x_return_status OUT NOCOPY VARCHAR2
915 ,x_msg_count OUT NOCOPY NUMBER
916 ,x_msg_data OUT NOCOPY VARCHAR2
917 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
918 ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
919 ) IS
920 l_api_version CONSTANT NUMBER DEFAULT 1.0;
921 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_PARTY_CONTACTS';
922 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_PARTY_CONTACTS';
923 l_debug_enabled VARCHAR2(10);
924
925 CURSOR c_get_diff_contact_csr(cp_chr_id_1 okc_k_headers_b.id%TYPE
926 ,cp_chr_id_2 okc_k_headers_b.id%TYPE)IS
927 SELECT role.object1_id1 role_party_id
928 ,role.object1_id2 role_object1_id2
929 ,role.rle_code
930 ,role.jtot_object1_code role_object
931 ,role.dnz_chr_id
932 ,contact.object1_id1 contact_party_id
933 ,contact.object1_id2 contact_object1_id2
934 ,contact.cro_code
935 ,contact.jtot_object1_code contact_object
936 ,contact.id
937 FROM okc_k_party_roles_b role
938 ,okc_contacts contact
939 WHERE role.id = contact.cpl_id
940 AND role.dnz_chr_id = contact.dnz_chr_id
941 AND role.dnz_chr_id = cp_chr_id_1
942 AND NOT EXISTS(SELECT 'X'
943 FROM okc_k_party_roles_b role_new
944 ,okc_contacts contact_new
945 WHERE role_new.id = contact_new.cpl_id
946 AND role_new.chr_id = contact_new.dnz_chr_id
947 AND role_new.object1_id1 = role.object1_id1
948 AND role_new.object1_id2 = role.object1_id2
949 AND role_new.jtot_object1_code = role.jtot_object1_code
950 AND role_new.rle_code = role.rle_code
951 AND contact_new.object1_id1 = contact.object1_id1
952 AND contact_new.object1_id2 = contact.object1_id2
953 AND contact_new.cro_code = contact.cro_code
954 AND contact_new.jtot_object1_code = contact.jtot_object1_code
955 AND role_new.chr_id = cp_chr_id_2
956 );
957 CURSOR c_get_cpl_csr(cp_dnz_chr_id okc_k_headers_b.id%TYPE
958 ,cp_rle_code okc_k_party_roles_b.rle_code%TYPE
959 ,cp_object1_id1 okc_k_party_roles_b.object1_id1%TYPE
960 ,cp_object1_id2 okc_k_party_roles_b.object1_id2%TYPE
961 ,cp_jtot_object1_code okc_k_party_roles_b.jtot_object1_code%TYPE
962 ) IS
963 SELECT id
964 FROM okc_k_party_roles_b
965 WHERE dnz_chr_id = cp_dnz_chr_id
966 AND rle_code = cp_rle_code
967 AND object1_id1 = cp_object1_id1
968 AND object1_id2 = cp_object1_id2
969 AND jtot_object1_code = cp_jtot_object1_code;
970
971 lv_ctcv_tbl ctcv_tbl_type;
972 x_ctcv_tbl ctcv_tbl_type;
973 contact_tbl_idx PLS_INTEGER;
974 lv_cpl_id okc_k_party_roles_b.id%TYPE;
975
976 BEGIN
977 x_return_status := OKL_API.G_RET_STS_SUCCESS;
978
979 l_debug_enabled := okl_debug_pub.check_log_enabled;
980
981 IF(l_debug_enabled='Y') THEN
982 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_party_contacts');
983 END IF;
984
985 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
986 x_return_status := OKL_API.START_ACTIVITY(
987 p_api_name => l_api_name
988 ,p_pkg_name => G_PKG_NAME
989 ,p_init_msg_list => p_init_msg_list
990 ,l_api_version => l_api_version
991 ,p_api_version => p_api_version
992 ,p_api_type => g_api_type
993 ,x_return_status => x_return_status);
994 -- check if activity started successfully
995 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
996 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
997 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
998 RAISE OKL_API.G_EXCEPTION_ERROR;
999 END IF;
1000
1001 /* Logic for synchronization: All the Contacts at the Role level present on the Original Agreement and not present on the
1002 * Change Request Agreement are to be deleted. All the Contacts present on the Change Request Agreement and not on the
1003 * Original Agreement are to be added to the Original Contact for that role. Since the Party Roles have already been
1004 * merged in the previous step, there would not be a case where a party on the change request is not found on the original
1005 * agreement.
1006 *
1007 * Technically the same cursor is being used to find the diff between the Contacts by interchanging the values of the
1008 * agreement ids
1009 */
1010 contact_tbl_idx := 0;
1011 FOR old_contacts_orig_rec IN c_get_diff_contact_csr(p_orig_chr_id, p_creq_chr_id) LOOP
1012 -- these records are to be deleted from the original contract as they are missing on the change request counter part
1013 contact_tbl_idx := contact_tbl_idx + 1;
1014 lv_ctcv_tbl(contact_tbl_idx).id := old_contacts_orig_rec.id;
1015 END LOOP;
1016
1017 IF(contact_tbl_idx > 0)THEN
1018 okl_contract_party_pub.delete_contact(p_api_version => p_api_version
1019 ,p_init_msg_list => p_init_msg_list
1020 ,x_return_status => x_return_status
1021 ,x_msg_count => x_msg_count
1022 ,x_msg_data => x_msg_data
1023 ,p_ctcv_tbl => lv_ctcv_tbl
1024 );
1025 IF(l_debug_enabled='Y') THEN
1026 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1027 l_module,
1028 'okl_contract_party_pub.delete_contact returned with status '||x_return_status
1029 );
1030 END IF;
1031 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1032 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1033 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1034 RAISE OKL_API.G_EXCEPTION_ERROR;
1035 END IF;
1036 END IF;
1037 contact_tbl_idx := 0;
1038 -- finding new contacts that are present on the change request and not on the original agreement
1039 FOR new_contacts_rec IN c_get_diff_contact_csr(p_creq_chr_id, p_orig_chr_id) LOOP
1040 contact_tbl_idx := contact_tbl_idx + 1;
1041 -- note the change in the reversal of the cursor parameters
1042 -- these records are to be added onto the original agreement under the same role
1043 OPEN c_get_cpl_csr(p_orig_chr_id
1044 ,new_contacts_rec.rle_code
1045 ,new_contacts_rec.role_party_id
1046 ,new_contacts_rec.role_object1_id2
1047 ,new_contacts_rec.role_object); FETCH c_get_cpl_csr INTO lv_cpl_id;
1048 CLOSE c_get_cpl_csr;
1049 lv_ctcv_tbl(contact_tbl_idx).cpl_id := lv_cpl_id;
1050 lv_ctcv_tbl(contact_tbl_idx).dnz_chr_id := p_orig_chr_id;
1051 lv_ctcv_tbl(contact_tbl_idx).object1_id1 := new_contacts_rec.contact_party_id;
1052 lv_ctcv_tbl(contact_tbl_idx).object1_id2 := new_contacts_rec.contact_object1_id2;
1053 lv_ctcv_tbl(contact_tbl_idx).cro_code := new_contacts_rec.cro_code;
1054 lv_ctcv_tbl(contact_tbl_idx).jtot_object1_code := new_contacts_rec.contact_object;
1055 END LOOP;
1056 IF(contact_tbl_idx > 0)THEN
1057 okl_contract_party_pub.create_contact(p_api_version => p_api_version
1058 ,p_init_msg_list => p_init_msg_list
1059 ,x_return_status => x_return_status
1060 ,x_msg_count => x_msg_count
1061 ,x_msg_data => x_msg_data
1062 ,p_ctcv_tbl => lv_ctcv_tbl
1063 ,x_ctcv_tbl => x_ctcv_tbl
1064 );
1065 IF(l_debug_enabled='Y') THEN
1066 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1067 l_module,
1068 'okl_contract_party_pub.create_contact returned with status '||x_return_status
1069 );
1070 END IF;
1071 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1072 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1073 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1074 RAISE OKL_API.G_EXCEPTION_ERROR;
1075 END IF;
1076 END IF;
1077
1078 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1079
1080 IF(l_debug_enabled='Y') THEN
1081 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_party_contacts');
1082 END IF;
1083 EXCEPTION
1084 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1085 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1086 p_api_name => l_api_name,
1087 p_pkg_name => G_PKG_NAME,
1088 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1089 x_msg_count => x_msg_count,
1090 x_msg_data => x_msg_data,
1091 p_api_type => g_api_type);
1092 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1093 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1094 p_api_name => l_api_name,
1095 p_pkg_name => G_PKG_NAME,
1096 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1097 x_msg_count => x_msg_count,
1098 x_msg_data => x_msg_data,
1099 p_api_type => g_api_type);
1100 WHEN OTHERS THEN
1101 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1102 p_api_name => l_api_name,
1103 p_pkg_name => G_PKG_NAME,
1104 p_exc_name => 'OTHERS',
1105 x_msg_count => x_msg_count,
1106 x_msg_data => x_msg_data,
1107 p_api_type => g_api_type);
1108 END sync_party_contacts;
1109
1110 PROCEDURE sync_article_changes(p_api_version IN NUMBER
1111 ,p_init_msg_list IN VARCHAR2
1112 ,x_return_status OUT NOCOPY VARCHAR2
1113 ,x_msg_count OUT NOCOPY NUMBER
1114 ,x_msg_data OUT NOCOPY VARCHAR2
1115 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
1116 ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
1117 ) IS
1118 -- cursor that fetches the new articles on the agreement (original or change request)
1119 CURSOR c_get_articles_csr (cp_chr_id okc_k_headers_b.id%TYPE)IS
1120 SELECT change_req.id
1121 ,change_req.chr_id
1122 ,change_req.sav_sae_id
1123 ,change_req.sav_sav_release
1124 ,change_req.sbt_code
1125 ,change_req.name
1126 ,change_req.cat_type
1127 ,change_req.text
1128 FROM okc_k_articles_v change_req
1129 where change_req.dnz_chr_id = cp_chr_id -- updated the code
1130 and change_req.chr_id=change_req.dnz_chr_id ; -- for performance issue bug#5484903
1131 -- WHERE change_req.chr_id = cp_chr_id; --commented out the old filter condition
1132
1133 lv_catv_rec catv_rec_type;
1134 x_catv_rec catv_rec_type;
1135 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1136 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_ARTICLE_CHANGES';
1137 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_ARTICLE_CHANGES';
1138 l_debug_enabled VARCHAR2(10);
1139
1140 BEGIN
1141 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1142
1143 l_debug_enabled := okl_debug_pub.check_log_enabled;
1144
1145 IF(l_debug_enabled='Y') THEN
1146 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_article_changes');
1147 END IF;
1148
1149 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1150 x_return_status := OKL_API.START_ACTIVITY(
1151 p_api_name => l_api_name
1152 ,p_pkg_name => G_PKG_NAME
1153 ,p_init_msg_list => p_init_msg_list
1154 ,l_api_version => l_api_version
1155 ,p_api_version => p_api_version
1156 ,p_api_type => g_api_type
1157 ,x_return_status => x_return_status);
1158 -- check if activity started successfully
1159 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1160 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1161 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1162 RAISE OKL_API.G_EXCEPTION_ERROR;
1163 END IF;
1164
1165 /*
1166 * Logic for deleting Articles: Article text is stored as CLOB. It is costlier to extract the Article text
1167 * and inspect the text to decide if the article text on the change request needs to be synced up with the
1168 * Article text on the Original Agreement. So avoid this comparision and text extraction from CLOB, all the articles
1169 * on the original agreement are deleted and the articles from the change request are recorded against the original
1170 * agreement
1171 */
1172
1173 FOR orig_articles_rec IN c_get_articles_csr(p_orig_chr_id) LOOP
1174 lv_catv_rec := NULL;
1175 lv_catv_rec.id := orig_articles_rec.id;
1176 okl_vp_k_article_pub.delete_k_article(p_api_version => p_api_version
1177 ,p_init_msg_list => p_init_msg_list
1178 ,x_return_status => x_return_status
1179 ,x_msg_count => x_msg_count
1180 ,x_msg_data => x_msg_data
1181 ,p_catv_rec => lv_catv_rec
1182 );
1183 IF(l_debug_enabled='Y') THEN
1184 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1185 l_module,
1186 'okl_vp_k_article_pub.delete_k_article returned with status '||x_return_status
1187 );
1188 END IF;
1189 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1190 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1191 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1192 RAISE OKL_API.G_EXCEPTION_ERROR;
1193 END IF;
1194 END LOOP;
1195
1196 -- now that the articles from the original agreement have been deleted, we need to copy all the articles from the
1197 -- change request back on to the original agreement
1198 FOR creq_articles_rec IN c_get_articles_csr(p_creq_chr_id) LOOP
1199 lv_catv_rec := NULL;
1200 x_catv_rec := NULL;
1201 lv_catv_rec.chr_id := p_orig_chr_id;
1202 lv_catv_rec.sbt_code := creq_articles_rec.sbt_code;
1203 lv_catv_rec.name := creq_articles_rec.name;
1204 lv_catv_rec.cat_type := creq_articles_rec.cat_type;
1205
1206 -- for non standard articles, since the record is not comming from the ui to sync, we do not have the sae_id and sav_release
1207 -- this is the reason for copying the code from the create_article_by copy local procedure of okl_vp_k_article_pvt
1208 IF(creq_articles_rec.cat_type = 'NSD')THEN
1209 lv_catv_rec.cle_id := null;
1210 lv_catv_rec.dnz_chr_id := p_orig_chr_id;
1211 lv_catv_rec.object_version_number := 1.0;
1212 lv_catv_rec.sfwt_flag := OKC_API.G_TRUE;
1213 lv_catv_rec.comments := NULL;
1214 lv_catv_rec.fulltext_yn := NULL;
1215 lv_catv_rec.variation_description := NULL;
1216 lv_catv_rec.sav_sae_id := NULL;
1217 lv_catv_rec.sav_sav_release := NULL;
1218 okl_okc_migration_a_pvt.insert_row(
1219 p_api_version => l_api_version,
1220 p_init_msg_list => p_init_msg_list,
1221 x_return_status => x_return_status,
1222 x_msg_count => x_msg_count,
1223 x_msg_data => x_msg_data,
1224 p_catv_rec => lv_catv_rec,
1225 x_catv_rec => x_catv_rec);
1226 IF(l_debug_enabled='Y') THEN
1227 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1228 l_module,
1229 'okl_okc_migration_a_pvt.insert_row returned with status '||x_return_status
1230 );
1231 END IF;
1232 IF(x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1233 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1234 ELSIF(x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1235 RAISE OKC_API.G_EXCEPTION_ERROR;
1236 END IF;
1237 -- now copy the text of the Non Standard Article into the new record
1238 x_return_status := okc_util.copy_articles_text(p_id => x_catv_rec.id
1239 ,lang => USERENV('LANG')
1240 ,p_text => creq_articles_rec.text);
1241 IF(x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1242 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1243 ELSIF(x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1244 RAISE OKC_API.G_EXCEPTION_ERROR;
1245 END IF;
1246
1247 ELSIF(creq_articles_rec.cat_type = 'STA')THEN
1248 lv_catv_rec.sav_sae_id := creq_articles_rec.sav_sae_id;
1249 lv_catv_rec.sav_sav_release := creq_articles_rec.sav_sav_release;
1250
1251 okl_vp_k_article_pub.create_k_article(p_api_version => p_api_version
1252 ,p_init_msg_list => p_init_msg_list
1253 ,x_return_status => x_return_status
1254 ,x_msg_count => x_msg_count
1255 ,x_msg_data => x_msg_data
1256 ,p_catv_rec => lv_catv_rec
1257 ,x_catv_rec => x_catv_rec
1258 );
1259 IF(l_debug_enabled='Y') THEN
1260 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1261 l_module,
1262 'okl_vp_k_article_pub.create_k_article returned with status '||x_return_status
1263 );
1264 END IF;
1265 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1266 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1267 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1268 RAISE OKL_API.G_EXCEPTION_ERROR;
1269 END IF;
1270 END IF;
1271 END LOOP;
1272
1273 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1274
1275 IF(l_debug_enabled='Y') THEN
1276 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_article_changes');
1277 END IF;
1278 EXCEPTION
1279 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1280 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1281 p_api_name => l_api_name,
1282 p_pkg_name => G_PKG_NAME,
1283 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1284 x_msg_count => x_msg_count,
1285 x_msg_data => x_msg_data,
1286 p_api_type => g_api_type);
1287 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1288 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1289 p_api_name => l_api_name,
1290 p_pkg_name => G_PKG_NAME,
1291 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1292 x_msg_count => x_msg_count,
1293 x_msg_data => x_msg_data,
1294 p_api_type => g_api_type);
1295 WHEN OTHERS THEN
1296 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1297 p_api_name => l_api_name,
1298 p_pkg_name => G_PKG_NAME,
1299 p_exc_name => 'OTHERS',
1300 x_msg_count => x_msg_count,
1301 x_msg_data => x_msg_data,
1302 p_api_type => g_api_type);
1303 END sync_article_changes;
1304
1305 PROCEDURE sync_vendor_billing(p_api_version IN NUMBER
1306 ,p_init_msg_list IN VARCHAR2
1307 ,x_return_status OUT NOCOPY VARCHAR2
1308 ,x_msg_count OUT NOCOPY NUMBER
1309 ,x_msg_data OUT NOCOPY VARCHAR2
1310 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
1311 ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
1312 ) IS
1313 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1314 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_VENDOR_BILLING';
1315 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_VENDOR_BILLING';
1316 l_debug_enabled VARCHAR2(10);
1317
1318 -- cursor to fetch the rule group role definitions id for the source Vendor
1319 CURSOR c_get_rrd_id_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
1320 SELECT rrd.id
1321 FROM okc_k_headers_b chr
1322 ,okc_subclass_roles sre
1323 ,okc_role_sources rse
1324 ,okc_subclass_rg_defs srd
1325 ,okc_rg_role_defs rrd
1326 WHERE chr.id = cp_chr_id
1327 AND sre.scs_code = chr.scs_code
1328 AND sre.rle_code = rse.rle_code
1329 AND rse.rle_code = 'OKL_VENDOR'
1330 AND rse.buy_or_sell = chr.buy_or_sell
1331 AND srd.scs_code = chr.scs_code
1332 AND srd.rgd_code = 'LAVENB'
1333 AND rrd.srd_id = srd.id
1334 AND rrd.sre_id = sre.id;
1335 lv_orig_rrd_id okc_rg_role_defs.id%TYPE;
1336
1337 -- cursor that fetches party role id of Lease Vendor
1338 CURSOR c_get_cpl_id_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
1339 SELECT id
1340 ,object1_id1
1341 FROM okc_k_party_roles_b
1342 WHERE dnz_chr_id = cp_chr_id
1343 AND rle_code = 'OKL_VENDOR'
1344 AND jtot_object1_code = 'OKX_VENDOR';
1345 lv_orig_chr_cpl_id okc_k_party_roles_b.id%TYPE;
1346 lv_creq_chr_cpl_id okc_k_party_roles_b.id%TYPE;
1347
1348 lv_orig_chr_object1 okc_k_party_roles_b.object1_id1%TYPE;
1349 lv_creq_chr_object1 okc_k_party_roles_b.object1_id1%TYPE;
1350
1351 -- cursor to fetch customer information from the billing page
1352 CURSOR c_get_cust_info_csr(cp_chr_id okc_k_headers_b.id%TYPE, cp_cpl_id okc_k_party_roles_b.id%TYPE) IS
1353 SELECT chrb.id chr_id,
1354 rgpb.id rgp_id,
1355 cplv.id cpl_id,
1356 rulb.id cust_rule_id,
1357 rulb.object1_id1 cust_id,
1358 hzp.party_name cust_name,
1359 cplv.cust_acct_id cust_acct_id,
1360 hzc.account_number cust_account_number,
1361 hzc.account_name cust_account_name,
1362 cplv.bill_to_site_use_id bill_to_site_use_id,
1363 hzcsu.location bill_to_site_use_name,
1364 cplv.cognomen cognomen,
1365 cplv.alias alias,
1366 chrb.authoring_org_id authoring_org_id
1367 FROM okc_k_headers_b chrb,
1368 okc_k_party_roles_v cplv,
1369 hz_cust_accounts hzc,
1370 hz_cust_site_uses_all hzcsu,
1371 okc_rg_party_roles rgrp,
1372 okc_rule_groups_b rgpb,
1373 hz_parties hzp,
1374 okc_rules_b rulb
1375 WHERE cplv.dnz_chr_id = chrb.id
1376 and cplv.chr_id = chrb.id
1377 and hzc.cust_account_id(+) = cplv.cust_acct_id
1378 and hzcsu.site_use_id(+) = cplv.bill_to_site_use_id
1379 and rgpb.rgd_code(+) = 'LAVENB'
1380 and rgrp.rgp_id = rgpb.id(+)
1381 and rgrp.cpl_id(+) = cplv.id
1382 and rulb.rule_information_category(+) = 'LAVENC'
1383 and rulb.rgp_id(+) = rgpb.id
1384 and hzp.party_id(+) = rulb.object1_id1
1385 and chrb.id = cp_chr_id
1386 and cplv.id = cp_cpl_id;
1387 c_get_cust_info_orig_rec c_get_cust_info_csr%ROWTYPE;
1388 c_get_cust_info_creq_rec c_get_cust_info_csr%ROWTYPE;
1389
1390 -- cursor to fetch other billing details from the billing page
1391 -- Updated the sql for performance issue - bug#5484903 - sql id: 20567146
1392 -- varangan - 26-9-06
1393 CURSOR c_get_cust_bill_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
1394 SELECT chr.id chr_id
1395 ,rgp_lavenb.id lavenb_rgp_id
1396 ,rul_lainpr.id lainpr_rul_id
1397 ,rul_lainpr.rule_information1 rul_lainpr_ri1
1398 -- ,to_date(rul_lainpr.rule_information2,'YYYY/MM/DD HH24:MI:SS') rul_lainpr_ri2
1399 ,rul_lainpr.rule_information2 rul_lainpr_ri2
1400 ,rul_lainvd.id lainvd_rul_id
1401 ,rul_lainvd.rule_information1 rul_lainvd_ri1
1402 ,rul_lainvd.rule_information4 rul_lainvd_ri4
1403 ,rul_labacc.id labacc_rul_id
1404 ,rul_labacc.object1_id1 rul_labacc_o1id1
1405 ,rul_labacc.object1_id2 rul_labacc_o1id2
1406 ,rul_lapmth.id lapmth_rul_id
1407 ,rul_lapmth.object1_id1 rul_lapmth_o1id1
1408 ,rul_lapmth.object1_id2 rul_lapmth_o1id2
1409 FROM okc_k_headers_b chr
1410 ,okl_k_headers khr
1411 ,okc_rule_groups_b rgp_lavenb
1412 ,okc_rules_b rul_lainpr
1413 ,okc_rules_b rul_lainvd
1414 ,okc_rules_b rul_labacc
1415 ,okc_rules_b rul_lapmth
1416 WHERE chr.id = khr.id
1417 AND rgp_lavenb.dnz_chr_id(+) = chr.id
1418 AND rgp_lavenb.rgd_code(+) = 'LAVENB'
1419 AND rgp_lavenb.id = rul_lainpr.rgp_id(+)
1420 AND rul_lainpr.rule_information_category(+) = 'LAINPR'
1421 AND rgp_lavenb.id = rul_lainvd.rgp_id(+)
1422 AND rul_lainvd.rule_information_category(+) = 'LAINVD'
1423 AND rgp_lavenb.id = rul_labacc.rgp_id(+)
1424 AND rul_labacc.rule_information_category(+) = 'LABACC'
1425 AND rgp_lavenb.id = rul_lapmth.rgp_id(+)
1426 AND rul_lapmth.rule_information_category(+) = 'LAPMTH'
1427 AND chr.id = cp_chr_id;
1428
1429 c_get_cust_bill_orig_rec c_get_cust_bill_csr%ROWTYPE;
1430 c_get_cust_bill_creq_rec c_get_cust_bill_csr%ROWTYPE;
1431
1432 lv_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
1433 x_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
1434
1435 lv_rgr_tbl okl_rgrp_rules_process_pvt.rgr_tbl_type;
1436 idx PLS_INTEGER;
1437
1438 lv_review_until_date DATE;
1439
1440 BEGIN
1441 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1442
1443 l_debug_enabled := okl_debug_pub.check_log_enabled;
1444
1445 IF(l_debug_enabled='Y') THEN
1446 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_vendor_billing');
1447 END IF;
1448
1449 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1450 x_return_status := OKL_API.START_ACTIVITY(
1451 p_api_name => l_api_name
1452 ,p_pkg_name => G_PKG_NAME
1453 ,p_init_msg_list => p_init_msg_list
1454 ,l_api_version => l_api_version
1455 ,p_api_version => p_api_version
1456 ,p_api_type => g_api_type
1457 ,x_return_status => x_return_status);
1458 -- check if activity started successfully
1459 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1460 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1461 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1462 RAISE OKL_API.G_EXCEPTION_ERROR;
1463 END IF;
1464
1465 -- first fetch the party role information for original agreement and the change request.
1466 -- cpl_id of these two entities should be the same as primary party information is not changed
1467 OPEN c_get_cpl_id_csr(p_orig_chr_id); FETCH c_get_cpl_id_csr INTO lv_orig_chr_cpl_id,lv_orig_chr_object1;
1468 CLOSE c_get_cpl_id_csr;
1469
1470 OPEN c_get_cpl_id_csr(p_creq_chr_id); FETCH c_get_cpl_id_csr INTO lv_creq_chr_cpl_id,lv_creq_chr_object1;
1471 CLOSE c_get_cpl_id_csr;
1472
1473 -- second, get the customer information from the chr_id and the party role id
1474 OPEN c_get_cust_info_csr(p_orig_chr_id, lv_orig_chr_cpl_id); FETCH c_get_cust_info_csr INTO c_get_cust_info_orig_rec;
1475 CLOSE c_get_cust_info_csr;
1476
1477 OPEN c_get_cust_info_csr(p_creq_chr_id, lv_creq_chr_cpl_id); FETCH c_get_cust_info_csr INTO c_get_cust_info_creq_rec;
1478 CLOSE c_get_cust_info_csr;
1479
1480 -- update customer information only if the customer has changed on the change request, or customer account has been changed
1481 -- or the bill to site info has been changed on the change request. for all other changes, the update is not necessary
1482 IF( NVL(c_get_cust_info_orig_rec.cust_id,OKL_API.G_MISS_NUM) <> NVL(c_get_cust_info_creq_rec.cust_id,OKL_API.G_MISS_NUM) OR
1483 NVL(c_get_cust_info_orig_rec.cust_acct_id,OKL_API.G_MISS_NUM) <> NVL(c_get_cust_info_creq_rec.cust_acct_id,OKL_API.G_MISS_NUM) OR
1484 NVL(c_get_cust_info_orig_rec.bill_to_site_use_id,OKL_API.G_MISS_NUM) <> NVL(c_get_cust_info_creq_rec.bill_to_site_use_id,OKL_API.G_MISS_NUM)
1485 )THEN
1486 lv_cplv_rec.id := lv_orig_chr_cpl_id;
1487 lv_cplv_rec.chr_id := p_orig_chr_id;
1488 lv_cplv_rec.dnz_chr_id := p_orig_chr_id;
1489 lv_cplv_rec.rle_code := 'OKL_VENDOR';
1490 lv_cplv_rec.bill_to_site_use_id := c_get_cust_info_creq_rec.bill_to_site_use_id;
1491 lv_cplv_rec.cust_acct_id := c_get_cust_info_creq_rec.cust_acct_id;
1492 lv_cplv_rec.cognomen := c_get_cust_info_creq_rec.cognomen;
1493 lv_cplv_rec.alias := c_get_cust_info_creq_rec.alias;
1494 lv_cplv_rec.object1_id1 := lv_orig_chr_object1;
1495 lv_cplv_rec.object1_id2 := '#';
1496 lv_cplv_rec.jtot_object1_code := 'OKX_VENDOR';
1497 lv_cplv_rec.created_by := fnd_global.user_id;
1498 lv_cplv_rec.creation_date := trunc(sysdate);
1499 lv_cplv_rec.last_updated_by := fnd_global.user_id;
1500 lv_cplv_rec.last_update_date := trunc(sysdate);
1501 lv_cplv_rec.last_update_login := fnd_global.login_id;
1502
1503 okl_contract_party_pub.update_k_party_role(p_api_version => p_api_version
1504 ,p_init_msg_list => p_init_msg_list
1505 ,x_return_status => x_return_status
1506 ,x_msg_count => x_msg_count
1507 ,x_msg_data => x_msg_data
1508 ,p_cplv_rec => lv_cplv_rec
1509 ,x_cplv_rec => x_cplv_rec
1510 );
1511 IF(l_debug_enabled='Y') THEN
1512 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1513 l_module,
1514 'okl_contract_party_pub.update_k_party_role returned with status '||x_return_status
1515 );
1516 END IF;
1517
1518 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1519 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1520 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1521 RAISE OKL_API.G_EXCEPTION_ERROR;
1522 END IF;
1523
1524 -- if the customer has changed, then update the rule info too
1525 IF( NVL(c_get_cust_info_orig_rec.cust_id,OKL_API.G_MISS_NUM) <> NVL(c_get_cust_info_creq_rec.cust_id,OKL_API.G_MISS_NUM))THEN
1526 -- now we need to update the rule information with the above changes
1527 idx := 1;
1528 lv_rgr_tbl(idx).rgd_code := 'LAVENB';
1529 lv_rgr_tbl(idx).rule_information_category := 'LAVENC';
1530 lv_rgr_tbl(idx).dnz_chr_id := p_orig_chr_id;
1531 lv_rgr_tbl(idx).sfwt_flag := 'N';
1532 lv_rgr_tbl(idx).std_template_yn := 'N';
1533 lv_rgr_tbl(idx).warn_yn := 'N';
1534 lv_rgr_tbl(idx).created_by := fnd_global.user_id;
1535 lv_rgr_tbl(idx).creation_date := trunc(sysdate);
1536 lv_rgr_tbl(idx).last_updated_by := fnd_global.user_id;
1537 lv_rgr_tbl(idx).last_update_date := trunc(sysdate);
1538 lv_rgr_tbl(idx).last_update_login := fnd_global.login_id;
1539
1540 IF(c_get_cust_info_orig_rec.rgp_id IS NOT NULL AND c_get_cust_info_orig_rec.rgp_id <> OKL_API.G_MISS_NUM)THEN
1541 lv_rgr_tbl(idx).rgp_id := c_get_cust_info_orig_rec.rgp_id;
1542 END IF;
1543
1544 IF(c_get_cust_info_orig_rec.cust_rule_id IS NOT NULL AND c_get_cust_info_orig_rec.cust_rule_id <> OKL_API.G_MISS_NUM)THEN
1545 lv_rgr_tbl(idx).rule_id := c_get_cust_info_orig_rec.cust_rule_id;
1546 END IF;
1547
1548 lv_rgr_tbl(idx).object1_id1 := c_get_cust_info_creq_rec.cust_id;
1549 lv_rgr_tbl(idx).jtot_object1_code := 'OKX_PARTY';
1550
1551 OPEN c_get_rrd_id_csr(p_orig_chr_id); FETCH c_get_rrd_id_csr INTO lv_orig_rrd_id;
1552 CLOSE c_get_rrd_id_csr;
1553
1554 okl_rgrp_rules_process_pvt.process_rule_group_rules(p_api_version => p_api_version
1555 ,p_init_msg_list => p_init_msg_list
1556 ,x_return_status => x_return_status
1557 ,x_msg_count => x_msg_count
1558 ,x_msg_data => x_msg_data
1559 ,p_chr_id => p_orig_chr_id
1560 ,p_line_id => null
1561 ,p_cpl_id => lv_orig_chr_cpl_id
1562 ,p_rrd_id => lv_orig_rrd_id
1563 ,p_rgr_tbl => lv_rgr_tbl
1564 );
1565 IF(l_debug_enabled='Y') THEN
1566 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1567 l_module,
1568 'okl_rgrp_rules_process_pvt.process_rule_group_rules for customer info returned with status '||x_return_status
1569 );
1570 END IF;
1571 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1572 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1573 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1574 RAISE OKL_API.G_EXCEPTION_ERROR;
1575 END IF;
1576
1577 END IF; -- end of NVL(c_get_cust_info_orig_rec.cust_id,OKL_API.G_MISS_NUM) <> NVL(c_get_cust_info_creq_rec.cust_id,OKL_API.G_MISS_NUM)
1578 END IF; -- end of value comparision
1579
1580 -- now fetch the billing information like bank account, payment method, review invoice from original agreement and change request
1581 -- and compare.
1582 OPEN c_get_cust_bill_csr(p_orig_chr_id); FETCH c_get_cust_bill_csr INTO c_get_cust_bill_orig_rec;
1583 CLOSE c_get_cust_bill_csr;
1584
1585 OPEN c_get_cust_bill_csr(p_creq_chr_id); FETCH c_get_cust_bill_csr INTO c_get_cust_bill_creq_rec;
1586 CLOSE c_get_cust_bill_csr;
1587
1588 OPEN c_get_rrd_id_csr(p_orig_chr_id); FETCH c_get_rrd_id_csr INTO lv_orig_rrd_id;
1589 CLOSE c_get_rrd_id_csr;
1590
1591 -- initialize the rule group pl/sql table
1592 lv_rgr_tbl.DELETE;
1593 idx := 0;
1594 -- compare the Vendor Billing information and update the rules information only if there is a change
1595 IF(
1596 NVL(c_get_cust_bill_orig_rec.rul_lainpr_ri1,OKL_API.G_MISS_CHAR) <> NVL(c_get_cust_bill_creq_rec.rul_lainpr_ri1,OKL_API.G_MISS_CHAR) OR
1597 NVL(c_get_cust_bill_orig_rec.rul_lainpr_ri2,OKL_API.G_MISS_DATE) <> NVL(c_get_cust_bill_creq_rec.rul_lainpr_ri2,OKL_API.G_MISS_DATE)
1598 )THEN
1599 idx := idx + 1;
1600
1601 -- check if the original record had vendor billing informaation and populate the rule group and rule ids accordingly.
1602 IF(c_get_cust_bill_orig_rec.lavenb_rgp_id IS NOT NULL AND c_get_cust_bill_orig_rec.lavenb_rgp_id <> OKL_API.G_MISS_NUM)THEN
1603 lv_rgr_tbl(idx).rgp_id := c_get_cust_bill_orig_rec.lavenb_rgp_id;
1604 END IF;
1605
1606 IF(c_get_cust_bill_orig_rec.lainpr_rul_id IS NOT NULL AND c_get_cust_bill_orig_rec.lainpr_rul_id <> OKL_API.G_MISS_NUM)THEN
1607 lv_rgr_tbl(idx).rule_id := c_get_cust_bill_orig_rec.lainpr_rul_id;
1608 END IF;
1609 lv_rgr_tbl(idx).rgd_code := 'LAVENB';
1610 lv_rgr_tbl(idx).rule_information_category := 'LAINPR';
1611 lv_rgr_tbl(idx).dnz_chr_id := p_orig_chr_id;
1612 lv_rgr_tbl(idx).sfwt_flag := 'N';
1613 lv_rgr_tbl(idx).std_template_yn := 'N';
1614 lv_rgr_tbl(idx).warn_yn := 'N';
1615
1616 lv_rgr_tbl(idx).rule_information1 := c_get_cust_bill_creq_rec.rul_lainpr_ri1;
1617 lv_rgr_tbl(idx).rule_information2 := c_get_cust_bill_creq_rec.rul_lainpr_ri2;
1618 END IF;
1619
1620 IF(
1621 NVL(c_get_cust_bill_orig_rec.rul_lainvd_ri1,OKL_API.G_MISS_CHAR) <> NVL(c_get_cust_bill_creq_rec.rul_lainvd_ri1,OKL_API.G_MISS_CHAR) OR
1622 NVL(c_get_cust_bill_orig_rec.rul_lainvd_ri4,OKL_API.G_MISS_CHAR) <> NVL(c_get_cust_bill_creq_rec.rul_lainvd_ri4,OKL_API.G_MISS_CHAR)
1623 )THEN
1624 idx := idx + 1;
1625
1626 -- check if the original record had vendor billing informaation and populate the rule group and rule ids accordingly.
1627 IF(c_get_cust_bill_orig_rec.lavenb_rgp_id IS NOT NULL AND c_get_cust_bill_orig_rec.lavenb_rgp_id <> OKL_API.G_MISS_NUM)THEN
1628 lv_rgr_tbl(idx).rgp_id := c_get_cust_bill_orig_rec.lavenb_rgp_id;
1629 END IF;
1630
1631 IF(c_get_cust_bill_orig_rec.lainvd_rul_id IS NOT NULL AND c_get_cust_bill_orig_rec.lainvd_rul_id <> OKL_API.G_MISS_NUM)THEN
1632 lv_rgr_tbl(idx).rule_id := c_get_cust_bill_orig_rec.lainvd_rul_id;
1633 END IF;
1634 lv_rgr_tbl(idx).rgd_code := 'LAVENB';
1635 lv_rgr_tbl(idx).rule_information_category := 'LAINVD';
1636 lv_rgr_tbl(idx).dnz_chr_id := p_orig_chr_id;
1637 lv_rgr_tbl(idx).sfwt_flag := 'N';
1638 lv_rgr_tbl(idx).std_template_yn := 'N';
1639 lv_rgr_tbl(idx).warn_yn := 'N';
1640
1641 lv_rgr_tbl(idx).rule_information1 := c_get_cust_bill_creq_rec.rul_lainvd_ri1;
1642 lv_rgr_tbl(idx).rule_information4 := c_get_cust_bill_creq_rec.rul_lainvd_ri4;
1643 END IF;
1644
1645 IF(
1646 NVL(c_get_cust_bill_orig_rec.rul_labacc_o1id1,OKL_API.G_MISS_NUM) <> NVL(c_get_cust_bill_creq_rec.rul_labacc_o1id1, OKL_API.G_MISS_NUM)
1647 )THEN
1648 idx := idx + 1;
1649
1650 -- check if the original record had vendor billing informaation and populate the rule group and rule ids accordingly.
1651 IF(c_get_cust_bill_orig_rec.lavenb_rgp_id IS NOT NULL AND c_get_cust_bill_orig_rec.lavenb_rgp_id <> OKL_API.G_MISS_NUM)THEN
1652 lv_rgr_tbl(idx).rgp_id := c_get_cust_bill_orig_rec.lavenb_rgp_id;
1653 END IF;
1654
1655 IF(c_get_cust_bill_orig_rec.labacc_rul_id IS NOT NULL AND c_get_cust_bill_orig_rec.labacc_rul_id <> OKL_API.G_MISS_NUM)THEN
1656 lv_rgr_tbl(idx).rule_id := c_get_cust_bill_orig_rec.labacc_rul_id;
1657 END IF;
1658 lv_rgr_tbl(idx).rgd_code := 'LAVENB';
1659 lv_rgr_tbl(idx).rule_information_category := 'LABACC';
1660 lv_rgr_tbl(idx).dnz_chr_id := p_orig_chr_id;
1661 lv_rgr_tbl(idx).sfwt_flag := 'N';
1662 lv_rgr_tbl(idx).std_template_yn := 'N';
1663 lv_rgr_tbl(idx).warn_yn := 'N';
1664
1665 lv_rgr_tbl(idx).object1_id1 := c_get_cust_bill_creq_rec.rul_labacc_o1id1;
1666 lv_rgr_tbl(idx).object1_id2 := c_get_cust_bill_creq_rec.rul_labacc_o1id2;
1667 lv_rgr_tbl(idx).jtot_object1_code := 'OKX_CUSTBKAC';
1668
1669 END IF;
1670
1671 IF(
1672 NVL(c_get_cust_bill_orig_rec.rul_lapmth_o1id1,OKL_API.G_MISS_NUM) <> NVL(c_get_cust_bill_creq_rec.rul_lapmth_o1id1, OKL_API.G_MISS_NUM)
1673 )THEN
1674 idx := idx + 1;
1675
1676 -- check if the original record had vendor billing informaation and populate the rule group and rule ids accordingly.
1677 IF(c_get_cust_bill_orig_rec.lavenb_rgp_id IS NOT NULL AND c_get_cust_bill_orig_rec.lavenb_rgp_id <> OKL_API.G_MISS_NUM)THEN
1678 lv_rgr_tbl(idx).rgp_id := c_get_cust_bill_orig_rec.lavenb_rgp_id;
1679 END IF;
1680
1681 IF(c_get_cust_bill_orig_rec.lapmth_rul_id IS NOT NULL AND c_get_cust_bill_orig_rec.lapmth_rul_id <> OKL_API.G_MISS_NUM)THEN
1682 lv_rgr_tbl(idx).rule_id := c_get_cust_bill_orig_rec.lapmth_rul_id;
1683 END IF;
1684 lv_rgr_tbl(idx).rgd_code := 'LAVENB';
1685 lv_rgr_tbl(idx).rule_information_category := 'LAPMTH';
1686 lv_rgr_tbl(idx).dnz_chr_id := p_orig_chr_id;
1687 lv_rgr_tbl(idx).sfwt_flag := 'N';
1688 lv_rgr_tbl(idx).std_template_yn := 'N';
1689 lv_rgr_tbl(idx).warn_yn := 'N';
1690
1691 lv_rgr_tbl(idx).object1_id1 := c_get_cust_bill_creq_rec.rul_lapmth_o1id1;
1692 lv_rgr_tbl(idx).object1_id2 := c_get_cust_bill_creq_rec.rul_lapmth_o1id2;
1693 lv_rgr_tbl(idx).jtot_object1_code := 'OKX_RCPTMTH';
1694 END IF;
1695
1696 IF(idx > 0)THEN
1697 okl_rgrp_rules_process_pvt.process_rule_group_rules(p_api_version => p_api_version
1698 ,p_init_msg_list => p_init_msg_list
1699 ,x_return_status => x_return_status
1700 ,x_msg_count => x_msg_count
1701 ,x_msg_data => x_msg_data
1702 ,p_chr_id => p_orig_chr_id
1703 ,p_line_id => null
1704 ,p_cpl_id => lv_orig_chr_cpl_id
1705 ,p_rrd_id => lv_orig_rrd_id
1706 ,p_rgr_tbl => lv_rgr_tbl
1707 );
1708 IF(l_debug_enabled='Y') THEN
1709 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1710 l_module,
1711 'okl_rgrp_rules_process_pvt.process_rule_group_rules for billing info returned with status '||x_return_status
1712 );
1713 END IF;
1714 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1715 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1716 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1717 RAISE OKL_API.G_EXCEPTION_ERROR;
1718 END IF;
1719 END IF;
1720
1721 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1722
1723 IF(l_debug_enabled='Y') THEN
1724 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_vendor_billing');
1725 END IF;
1726
1727 EXCEPTION
1728 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1729 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1730 p_api_name => l_api_name,
1731 p_pkg_name => G_PKG_NAME,
1732 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1733 x_msg_count => x_msg_count,
1734 x_msg_data => x_msg_data,
1735 p_api_type => g_api_type);
1736 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1737 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1738 p_api_name => l_api_name,
1739 p_pkg_name => G_PKG_NAME,
1740 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1741 x_msg_count => x_msg_count,
1742 x_msg_data => x_msg_data,
1743 p_api_type => g_api_type);
1744 WHEN OTHERS THEN
1745 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1746 p_api_name => l_api_name,
1747 p_pkg_name => G_PKG_NAME,
1748 p_exc_name => 'OTHERS',
1749 x_msg_count => x_msg_count,
1750 x_msg_data => x_msg_data,
1751 p_api_type => g_api_type);
1752 END sync_vendor_billing;
1753
1754 PROCEDURE sync_associations(p_api_version IN NUMBER
1755 ,p_init_msg_list IN VARCHAR2
1756 ,x_return_status OUT NOCOPY VARCHAR2
1757 ,x_msg_count OUT NOCOPY NUMBER
1758 ,x_msg_data OUT NOCOPY VARCHAR2
1759 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
1760 ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
1761 ,p_change_request_id IN okl_vp_change_requests.id%TYPE
1762 ) IS
1763
1764 -- cursor to get all the associations for the given change request
1765 CURSOR c_get_creq_assoc(cp_chr_id okc_k_headers_b.id%TYPE
1766 ,cp_change_request_id okl_vp_change_requests.id%TYPE
1767 ,cp_assoc_object_id okl_vp_associations.assoc_object_id%TYPE
1768 ,cp_assoc_object_code okl_vp_associations.assoc_object_type_code%TYPE
1769 ,cp_assoc_object_version okl_vp_associations.assoc_object_version%TYPE
1770 )IS
1771 SELECT crs_id
1772 ,start_date
1773 ,end_date
1774 ,description
1775 ,assoc_object_type_code
1776 ,assoc_object_id
1777 ,assoc_object_version
1778 FROM okl_vp_associations
1779 WHERE crs_id = cp_change_request_id
1780 AND chr_id = cp_chr_id
1781 AND assoc_object_id = cp_assoc_object_id
1782 AND assoc_object_type_code = cp_assoc_object_code
1783 AND nvl(assoc_object_version,1) = nvl(cp_assoc_object_version,1);
1784 cv_creq_assoc_rec c_get_creq_assoc%ROWTYPE;
1785
1786 -- get the associations on the original agreement. this cursor is valid
1787 -- for both ASSOCIATION change requests and AGREEMENT change requests
1788 CURSOR c_get_orig_assoc(cp_chr_id okc_k_headers_b.id%TYPE)IS
1789 SELECT chr_id
1790 ,start_date
1791 ,end_date
1792 ,description
1793 ,assoc_object_type_code
1794 ,assoc_object_id
1795 ,assoc_object_version
1796 ,id
1797 FROM okl_vp_associations
1798 WHERE crs_id IS NULL
1799 AND chr_id = cp_chr_id;
1800
1801 CURSOR c_new_creq_assoc_csr(cp_change_request_id okl_vp_change_requests.id%TYPE
1802 ,cp_chr_id okc_k_headers_b.id%TYPE) IS
1803 SELECT creq.chr_id
1804 ,creq.crs_id
1805 ,creq.start_date
1806 ,creq.end_date
1807 ,creq.description
1808 ,creq.assoc_object_type_code
1809 ,creq.assoc_object_id
1810 ,creq.assoc_object_version
1811 FROM okl_vp_associations creq
1812 WHERE crs_id = cp_change_request_id
1813 AND NOT EXISTS (
1814 SELECT 'X'
1815 FROM okl_vp_associations orig
1816 WHERE orig.chr_id = cp_chr_id
1817 AND orig.crs_id IS NULL
1818 AND orig.start_date = creq.start_date
1819 AND NVL(orig.end_date, TRUNC(SYSDATE)) = NVL(creq.end_date,TRUNC(SYSDATE))
1820 AND NVL(orig.description, 'X') = NVL(creq.description, 'X')
1821 AND orig.assoc_object_type_code = creq.assoc_object_type_code
1822 AND orig.assoc_object_id = creq.assoc_object_id
1823 AND NVL(orig.assoc_object_version,1) = NVL(creq.assoc_object_version,1)
1824 );
1825 lv_vasv_rec vasv_rec_type;
1826 x_vasv_rec vasv_rec_type;
1827 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1828 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_ASSOCIATIONS';
1829 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_ASSOCIATIONS';
1830 l_debug_enabled VARCHAR2(10);
1831
1832 BEGIN
1833 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1834
1835 l_debug_enabled := okl_debug_pub.check_log_enabled;
1836
1837 IF(l_debug_enabled='Y') THEN
1838 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_associations');
1839 END IF;
1840
1841 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1842 x_return_status := OKL_API.START_ACTIVITY(
1843 p_api_name => l_api_name
1844 ,p_pkg_name => G_PKG_NAME
1845 ,p_init_msg_list => p_init_msg_list
1846 ,l_api_version => l_api_version
1847 ,p_api_version => p_api_version
1848 ,p_api_type => g_api_type
1849 ,x_return_status => x_return_status);
1850 -- check if activity started successfully
1851 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1852 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1853 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1854 RAISE OKL_API.G_EXCEPTION_ERROR;
1855 END IF;
1856
1857 -- technical note: for a change request of type AGREEMENT, the parameters p_orig_chr_id and p_creq_chr_id
1858 -- point to the original agreement and the agreement associated with the change request respectively.
1859 -- but for an ASSOCIATION type of change request, the parameter values p_orig_chr_id and p_creq_chr_id are the
1860 -- same.
1861
1862 -- first find out if the original associations are on the change request too
1863 FOR orig_assoc_rec IN c_get_orig_assoc(p_orig_chr_id) LOOP
1864 -- now check if this association record exists on the change request too.
1865 OPEN c_get_creq_assoc(p_creq_chr_id
1866 ,p_change_request_id
1867 ,orig_assoc_rec.assoc_object_id
1868 ,orig_assoc_rec.assoc_object_type_code
1869 ,orig_assoc_rec.assoc_object_version);
1870 FETCH c_get_creq_assoc INTO cv_creq_assoc_rec;
1871 IF(c_get_creq_assoc%FOUND)THEN
1872 CLOSE c_get_creq_assoc;
1873 -- compare other attributes of the template now that it has not been removed. other attribs include start date, end date, comments
1874 IF((TRUNC(orig_assoc_rec.start_date) <> TRUNC(cv_creq_assoc_rec.start_date))
1875 OR(NVL(orig_assoc_rec.end_date,OKL_API.G_MISS_DATE) <> NVL(cv_creq_assoc_rec.end_date,OKL_API.G_MISS_DATE))
1876 OR(NVL(orig_assoc_rec.description,OKL_API.G_MISS_CHAR) <> NVL(cv_creq_assoc_rec.description,OKL_API.G_MISS_CHAR)))THEN
1877 -- if either of the start date, end date or comments have been updated on the change request, we need to sync them back to the
1878 -- agreement
1879 lv_vasv_rec := NULL;
1880 x_vasv_rec := NULL;
1881 lv_vasv_rec.id := orig_assoc_rec.id;
1882 lv_vasv_rec.assoc_object_id := orig_assoc_rec.assoc_object_id;
1883 lv_vasv_rec.assoc_object_type_code := orig_assoc_rec.assoc_object_type_code;
1884 lv_vasv_rec.assoc_object_version := cv_creq_assoc_rec.assoc_object_version;
1885 lv_vasv_rec.start_date := TRUNC(cv_creq_assoc_rec.start_date);
1886 lv_vasv_rec.end_date := cv_creq_assoc_rec.end_date;
1887 lv_vasv_rec.description := cv_creq_assoc_rec.description;
1888 lv_vasv_rec.chr_id := p_orig_chr_id;
1889 okl_vp_associations_pvt.update_vp_associations(p_api_version => p_api_version
1890 ,p_init_msg_list => p_init_msg_list
1891 ,x_return_status => x_return_status
1892 ,x_msg_count => x_msg_count
1893 ,x_msg_data => x_msg_data
1894 ,p_vasv_rec => lv_vasv_rec
1895 ,x_vasv_rec => x_vasv_rec
1896 );
1897 IF(l_debug_enabled='Y') THEN
1898 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1899 l_module,
1900 'okl_vp_associations_pvt.update_vp_associations returned with status '||x_return_status
1901 );
1902 END IF;
1903 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1904 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1905 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1906 RAISE OKL_API.G_EXCEPTION_ERROR;
1907 END IF;
1908 END IF; -- end of attrib comparision
1909 ELSE
1910 CLOSE c_get_creq_assoc;
1911 -- since the record is not present on the change request, delete this record from the original agreement too
1912 lv_vasv_rec := NULL;
1913 lv_vasv_rec.id := orig_assoc_rec.id;
1914 okl_vp_associations_pvt.delete_vp_associations(p_api_version => p_api_version
1915 ,p_init_msg_list => p_init_msg_list
1916 ,x_return_status => x_return_status
1917 ,x_msg_count => x_msg_count
1918 ,x_msg_data => x_msg_data
1919 ,p_vasv_rec => lv_vasv_rec
1920 );
1921 IF(l_debug_enabled='Y') THEN
1922 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1923 l_module,
1924 'okl_vp_associations_pvt.delete_vp_associations returned with status '||x_return_status
1925 );
1926 END IF;
1927 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1928 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1929 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1930 RAISE OKL_API.G_EXCEPTION_ERROR;
1931 END IF;
1932 END IF; -- end of attribute comparision
1933 END LOOP;
1934 -- now we need to add back all the new records that have been created or whose associated object has been changed by updating the
1935 -- LOV field on the change request
1936 FOR new_creq_rec IN c_new_creq_assoc_csr(p_change_request_id, p_creq_chr_id) LOOP
1937 lv_vasv_rec := NULL;
1938 x_vasv_rec := NULL;
1939 lv_vasv_rec.chr_id := p_orig_chr_id;
1940 lv_vasv_rec.start_date := new_creq_rec.start_date;
1941 lv_vasv_rec.end_date := new_creq_rec.end_date;
1942 lv_vasv_rec.description := new_creq_rec.description;
1943 lv_vasv_rec.assoc_object_type_code := new_creq_rec.assoc_object_type_code;
1944 lv_vasv_rec.assoc_object_id := new_creq_rec.assoc_object_id;
1945 lv_vasv_rec.assoc_object_version := new_creq_rec.assoc_object_version;
1946 okl_vp_associations_pvt.create_vp_associations(p_api_version => p_api_version
1947 ,p_init_msg_list => p_init_msg_list
1948 ,x_return_status => x_return_status
1949 ,x_msg_count => x_msg_count
1950 ,x_msg_data => x_msg_data
1951 ,p_vasv_rec => lv_vasv_rec
1952 ,x_vasv_rec => x_vasv_rec
1953 );
1954 IF(l_debug_enabled='Y') THEN
1955 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1956 l_module,
1957 'okl_vp_associations_pvt.create_vp_associations returned with status '||x_return_status
1958 );
1959 END IF;
1960 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1961 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1962 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1963 RAISE OKL_API.G_EXCEPTION_ERROR;
1964 END IF;
1965 END LOOP;
1966
1967 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1968
1969 IF(l_debug_enabled='Y') THEN
1970 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_associations');
1971 END IF;
1972 EXCEPTION
1973 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1974 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1975 p_api_name => l_api_name,
1976 p_pkg_name => G_PKG_NAME,
1977 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1978 x_msg_count => x_msg_count,
1979 x_msg_data => x_msg_data,
1980 p_api_type => g_api_type);
1981 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1982 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1983 p_api_name => l_api_name,
1984 p_pkg_name => G_PKG_NAME,
1985 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1986 x_msg_count => x_msg_count,
1987 x_msg_data => x_msg_data,
1988 p_api_type => g_api_type);
1989 WHEN OTHERS THEN
1990 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1991 p_api_name => l_api_name,
1992 p_pkg_name => G_PKG_NAME,
1993 p_exc_name => 'OTHERS',
1994 x_msg_count => x_msg_count,
1995 x_msg_data => x_msg_data,
1996 p_api_type => g_api_type);
1997 END sync_associations;
1998
1999 PROCEDURE sync_agr_associations(p_api_version IN NUMBER
2000 ,p_init_msg_list IN VARCHAR2
2001 ,x_return_status OUT NOCOPY VARCHAR2
2002 ,x_msg_count OUT NOCOPY NUMBER
2003 ,x_msg_data OUT NOCOPY VARCHAR2
2004 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2005 ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2006 ,p_change_request_id IN okl_vp_change_requests.id%TYPE
2007 ) IS
2008
2009 -- cursor to get all the associations for the given change request of type AGREEMENT
2010 CURSOR c_get_creq_assoc(cp_chr_id okc_k_headers_b.id%TYPE
2011 ,cp_assoc_object_id okl_vp_associations.assoc_object_id%TYPE
2012 ,cp_assoc_object_code okl_vp_associations.assoc_object_type_code%TYPE
2013 ,cp_assoc_object_version okl_vp_associations.assoc_object_version%TYPE
2014 )IS
2015 SELECT crs_id
2016 ,start_date
2017 ,end_date
2018 ,description
2019 ,assoc_object_type_code
2020 ,assoc_object_id
2021 ,assoc_object_version
2022 FROM okl_vp_associations
2023 WHERE chr_id = cp_chr_id
2024 AND assoc_object_id = cp_assoc_object_id
2025 AND assoc_object_type_code = cp_assoc_object_code
2026 AND nvl(assoc_object_version,1) = nvl(cp_assoc_object_version,1);
2027 cv_creq_assoc_rec c_get_creq_assoc%ROWTYPE;
2028
2029 -- get the associations on the original agreement. this cursor is valid
2030 -- for both ASSOCIATION change requests and AGREEMENT change requests
2031 CURSOR c_get_orig_assoc(cp_chr_id okc_k_headers_b.id%TYPE)IS
2032 SELECT chr_id
2033 ,start_date
2034 ,end_date
2035 ,description
2036 ,assoc_object_type_code
2037 ,assoc_object_id
2038 ,assoc_object_version
2039 ,id
2040 FROM okl_vp_associations
2041 WHERE crs_id IS NULL
2042 AND chr_id = cp_chr_id;
2043
2044 CURSOR c_new_creq_assoc_csr(cp_chr_id_orig okl_vp_change_requests.id%TYPE
2045 ,cp_chr_id_creq okc_k_headers_b.id%TYPE) IS
2046 SELECT creq.chr_id
2047 ,creq.crs_id
2048 ,creq.start_date
2049 ,creq.end_date
2050 ,creq.description
2051 ,creq.assoc_object_type_code
2052 ,creq.assoc_object_id
2053 ,creq.assoc_object_version
2054 FROM okl_vp_associations creq
2055 WHERE chr_id = cp_chr_id_creq
2056 AND NOT EXISTS (
2057 SELECT 'X'
2058 FROM okl_vp_associations orig
2059 WHERE orig.chr_id = cp_chr_id_orig
2060 AND orig.crs_id IS NULL
2061 AND orig.start_date = creq.start_date
2062 AND NVL(orig.end_date, TRUNC(SYSDATE)) = NVL(creq.end_date,TRUNC(SYSDATE))
2063 AND NVL(orig.description, 'X') = NVL(creq.description, 'X')
2064 AND orig.assoc_object_type_code = creq.assoc_object_type_code
2065 AND orig.assoc_object_id = creq.assoc_object_id
2066 AND NVL(orig.assoc_object_version,1) = NVL(creq.assoc_object_version,1)
2067 );
2068 lv_vasv_rec vasv_rec_type;
2069 x_vasv_rec vasv_rec_type;
2070 l_api_version CONSTANT NUMBER DEFAULT 1.0;
2071 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_ASSOCIATIONS';
2072 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_ASSOCIATIONS';
2073 l_debug_enabled VARCHAR2(10);
2074
2075 BEGIN
2076 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2077
2078 l_debug_enabled := okl_debug_pub.check_log_enabled;
2079
2080 IF(l_debug_enabled='Y') THEN
2081 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_associations');
2082 END IF;
2083
2084 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
2085 x_return_status := OKL_API.START_ACTIVITY(
2086 p_api_name => l_api_name
2087 ,p_pkg_name => G_PKG_NAME
2088 ,p_init_msg_list => p_init_msg_list
2089 ,l_api_version => l_api_version
2090 ,p_api_version => p_api_version
2091 ,p_api_type => g_api_type
2092 ,x_return_status => x_return_status);
2093 -- check if activity started successfully
2094 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2095 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2096 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2097 RAISE OKL_API.G_EXCEPTION_ERROR;
2098 END IF;
2099
2100 -- first find out if the original associations are on the change request too
2101 FOR orig_assoc_rec IN c_get_orig_assoc(p_orig_chr_id) LOOP
2102 -- now check if this association record exists on the change request too.
2103 OPEN c_get_creq_assoc(p_creq_chr_id
2104 ,orig_assoc_rec.assoc_object_id
2105 ,orig_assoc_rec.assoc_object_type_code
2106 ,orig_assoc_rec.assoc_object_version);
2107 FETCH c_get_creq_assoc INTO cv_creq_assoc_rec;
2108 IF(c_get_creq_assoc%FOUND)THEN
2109 CLOSE c_get_creq_assoc;
2110 -- compare other attributes of the template now that it has not been removed. other attribs include start date, end date, comments
2111 IF((TRUNC(orig_assoc_rec.start_date) <> TRUNC(cv_creq_assoc_rec.start_date))
2112 OR(NVL(orig_assoc_rec.end_date,OKL_API.G_MISS_DATE) <> NVL(cv_creq_assoc_rec.end_date,OKL_API.G_MISS_DATE))
2113 OR(NVL(orig_assoc_rec.description,OKL_API.G_MISS_CHAR) <> NVL(cv_creq_assoc_rec.description,OKL_API.G_MISS_CHAR)))THEN
2114 -- if either of the start date, end date or comments have been updated on the change request, we need to sync them back to the
2115 -- agreement
2116 lv_vasv_rec := NULL;
2117 x_vasv_rec := NULL;
2118 lv_vasv_rec.id := orig_assoc_rec.id;
2119 lv_vasv_rec.assoc_object_id := orig_assoc_rec.assoc_object_id;
2120 lv_vasv_rec.assoc_object_type_code := orig_assoc_rec.assoc_object_type_code;
2121 lv_vasv_rec.assoc_object_version := cv_creq_assoc_rec.assoc_object_version;
2122 lv_vasv_rec.start_date := TRUNC(cv_creq_assoc_rec.start_date);
2123 lv_vasv_rec.end_date := cv_creq_assoc_rec.end_date;
2124 lv_vasv_rec.description := cv_creq_assoc_rec.description;
2125 lv_vasv_rec.chr_id := p_orig_chr_id;
2126 okl_vp_associations_pvt.update_vp_associations(p_api_version => p_api_version
2127 ,p_init_msg_list => p_init_msg_list
2128 ,x_return_status => x_return_status
2129 ,x_msg_count => x_msg_count
2130 ,x_msg_data => x_msg_data
2131 ,p_vasv_rec => lv_vasv_rec
2132 ,x_vasv_rec => x_vasv_rec
2133 );
2134 IF(l_debug_enabled='Y') THEN
2135 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2136 l_module,
2137 'okl_vp_associations_pvt.update_vp_associations returned with status '||x_return_status
2138 );
2139 END IF;
2140 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2141 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2142 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2143 RAISE OKL_API.G_EXCEPTION_ERROR;
2144 END IF;
2145 END IF; -- end of attrib comparision
2146 ELSE
2147 CLOSE c_get_creq_assoc;
2148 -- since the record is not present on the change request, delete this record from the original agreement too
2149 lv_vasv_rec := NULL;
2150 lv_vasv_rec.id := orig_assoc_rec.id;
2151 okl_vp_associations_pvt.delete_vp_associations(p_api_version => p_api_version
2152 ,p_init_msg_list => p_init_msg_list
2153 ,x_return_status => x_return_status
2154 ,x_msg_count => x_msg_count
2155 ,x_msg_data => x_msg_data
2156 ,p_vasv_rec => lv_vasv_rec
2157 );
2158 IF(l_debug_enabled='Y') THEN
2159 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2160 l_module,
2161 'okl_vp_associations_pvt.delete_vp_associations returned with status '||x_return_status
2162 );
2163 END IF;
2164 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2165 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2166 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2167 RAISE OKL_API.G_EXCEPTION_ERROR;
2168 END IF;
2169 END IF; -- end of attribute comparision
2170 END LOOP;
2171 -- now we need to add back all the new records that have been created or whose associated object has been changed by updating the
2172 -- LOV field on the change request
2173 FOR new_creq_rec IN c_new_creq_assoc_csr(p_orig_chr_id, p_creq_chr_id) LOOP
2174 lv_vasv_rec := NULL;
2175 x_vasv_rec := NULL;
2176 lv_vasv_rec.chr_id := p_orig_chr_id;
2177 lv_vasv_rec.start_date := new_creq_rec.start_date;
2178 lv_vasv_rec.end_date := new_creq_rec.end_date;
2179 lv_vasv_rec.description := new_creq_rec.description;
2180 lv_vasv_rec.assoc_object_type_code := new_creq_rec.assoc_object_type_code;
2181 lv_vasv_rec.assoc_object_id := new_creq_rec.assoc_object_id;
2182 lv_vasv_rec.assoc_object_version := new_creq_rec.assoc_object_version;
2183 okl_vp_associations_pvt.create_vp_associations(p_api_version => p_api_version
2184 ,p_init_msg_list => p_init_msg_list
2185 ,x_return_status => x_return_status
2186 ,x_msg_count => x_msg_count
2187 ,x_msg_data => x_msg_data
2188 ,p_vasv_rec => lv_vasv_rec
2189 ,x_vasv_rec => x_vasv_rec
2190 );
2191 IF(l_debug_enabled='Y') THEN
2192 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2193 l_module,
2194 'okl_vp_associations_pvt.create_vp_associations returned with status '||x_return_status
2195 );
2196 END IF;
2197 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2198 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2199 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2200 RAISE OKL_API.G_EXCEPTION_ERROR;
2201 END IF;
2202 END LOOP;
2203
2204 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2205
2206 IF(l_debug_enabled='Y') THEN
2207 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_associations');
2208 END IF;
2209 EXCEPTION
2210 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2211 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2212 p_api_name => l_api_name,
2213 p_pkg_name => G_PKG_NAME,
2214 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2215 x_msg_count => x_msg_count,
2216 x_msg_data => x_msg_data,
2217 p_api_type => g_api_type);
2218 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2219 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2220 p_api_name => l_api_name,
2221 p_pkg_name => G_PKG_NAME,
2222 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2223 x_msg_count => x_msg_count,
2224 x_msg_data => x_msg_data,
2225 p_api_type => g_api_type);
2226 WHEN OTHERS THEN
2227 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2228 p_api_name => l_api_name,
2229 p_pkg_name => G_PKG_NAME,
2230 p_exc_name => 'OTHERS',
2231 x_msg_count => x_msg_count,
2232 x_msg_data => x_msg_data,
2233 p_api_type => g_api_type);
2234 END sync_agr_associations;
2235
2236
2237 PROCEDURE sync_elig_criteria(p_api_version IN NUMBER
2238 ,p_init_msg_list IN VARCHAR2
2239 ,x_return_status OUT NOCOPY VARCHAR2
2240 ,x_msg_count OUT NOCOPY NUMBER
2241 ,x_msg_data OUT NOCOPY VARCHAR2
2242 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2243 ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2244 )IS
2245 CURSOR c_get_agrmt_dates_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
2246 SELECT start_date
2247 ,end_date
2248 FROM okc_k_headers_b
2249 WHERE id = cp_chr_id;
2250 cv_get_agrmnt_dates c_get_agrmt_dates_csr%ROWTYPE;
2251
2252 lx_ech_rec ech_rec_type;
2253 lx_ecl_tbl ecl_tbl_type;
2254 lx_ecv_tbl ecv_tbl_type;
2255
2256 x_ech_rec ech_rec_type;
2257 x_ecl_tbl ecl_tbl_type;
2258 x_ecv_tbl ecv_tbl_type;
2259
2260 l_api_version CONSTANT NUMBER DEFAULT 1.0;
2261 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_ELIG_CRITERIA';
2262 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_ELIG_CRITERIA';
2263 l_debug_enabled VARCHAR2(10);
2264
2265 BEGIN
2266 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2267
2268 l_debug_enabled := okl_debug_pub.check_log_enabled;
2269
2270 IF(l_debug_enabled='Y') THEN
2271 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_elig_criteria');
2272 END IF;
2273
2274 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
2275 x_return_status := OKL_API.START_ACTIVITY(
2276 p_api_name => l_api_name
2277 ,p_pkg_name => G_PKG_NAME
2278 ,p_init_msg_list => p_init_msg_list
2279 ,l_api_version => l_api_version
2280 ,p_api_version => p_api_version
2281 ,p_api_type => g_api_type
2282 ,x_return_status => x_return_status);
2283 -- check if activity started successfully
2284 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2285 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2286 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2287 RAISE OKL_API.G_EXCEPTION_ERROR;
2288 END IF;
2289
2290 -- the effective start and end dates of the original agreement are derived to pass to the eligibility criteria API
2291 -- note that by the time control reaches this place, the extended effective end date on the change request would
2292 -- have been synced on to the original agreement. this is the reason for not picking up the effective dates from the
2293 -- change request.
2294 OPEN c_get_agrmt_dates_csr(p_orig_chr_id); FETCH c_get_agrmt_dates_csr INTO cv_get_agrmnt_dates;
2295 CLOSE c_get_agrmt_dates_csr;
2296
2297 -- first, delete the eligibility criteria on the original agreement
2298 okl_ecc_values_pvt.delete_eligibility_criteria(p_api_version => p_api_version
2299 ,p_init_msg_list => p_init_msg_list
2300 ,x_return_status => x_return_status
2301 ,x_msg_count => x_msg_count
2302 ,x_msg_data => x_msg_data
2303 ,p_source_id => p_orig_chr_id
2304 ,p_source_type => G_VENDOR_PROGRAM_CODE
2305 );
2306 IF(l_debug_enabled='Y') THEN
2307 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2308 l_module,
2309 'okl_ecc_values_pvt.delete_eligibility_criteria returned with status '||x_return_status
2310 );
2311 END IF;
2312 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2313 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2314 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2315 RAISE OKL_API.G_EXCEPTION_ERROR;
2316 END IF;
2317
2318 -- now that the eligibility criteria has been deleted successfully,
2319 -- get the eligibility criteria from the change request agreement
2320 okl_ecc_values_pvt.get_eligibility_criteria(p_api_version => p_api_version
2321 ,p_init_msg_list => p_init_msg_list
2322 ,x_return_status => x_return_status
2323 ,x_msg_count => x_msg_count
2324 ,x_msg_data => x_msg_data
2325 ,p_source_id => p_creq_chr_id
2326 ,p_source_type => G_VENDOR_PROGRAM_CODE
2327 ,x_ech_rec => lx_ech_rec
2328 ,x_ecl_tbl => lx_ecl_tbl
2329 ,x_ecv_tbl => lx_ecv_tbl
2330 );
2331 IF(l_debug_enabled='Y') THEN
2332 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2333 l_module,
2334 'okl_ecc_values_pvt.get_eligibility_criteria returned with status '||x_return_status
2335 );
2336 END IF;
2337 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2338 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2339 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2340 RAISE OKL_API.G_EXCEPTION_ERROR;
2341 END IF;
2342
2343 IF(lx_ecl_tbl.count > 0)THEN
2344 -- set the eligibility criteria headers id to the original agreement id
2345 -- and the source to VENDOR_PROGRAM
2346 lx_ech_rec.source_id := p_orig_chr_id;
2347 lx_ech_rec.source_object_code := G_VENDOR_PROGRAM_CODE;
2348
2349 -- pass the criteria set id as null to indicate creation of the eligibility criteria on the original agreement
2350 lx_ech_rec.criteria_set_id := NULL;
2351
2352 FOR i IN lx_ecl_tbl.FIRST..lx_ecl_tbl.LAST LOOP
2353 -- is_new_flag = Y indicates create mode
2354 lx_ecl_tbl(i).is_new_flag := 'Y';
2355 END LOOP;
2356
2357 FOR i IN lx_ecv_tbl.FIRST..lx_ecv_tbl.LAST LOOP
2358 lx_ecv_tbl(i).criterion_value_id := NULL;
2359 -- validate_record = N indicates that the values in crit_cat_value1 and crit_cat_value2 will not be
2360 -- validated again. since this is the case of synchronization, the validation would have happened while
2361 -- saving the criteria values on the change request
2362 lx_ecv_tbl(i).validate_record := 'N';
2363 END LOOP;
2364
2365 --call handle_eligibility_criteria
2366 okl_ecc_values_pvt.handle_eligibility_criteria(p_api_version => p_api_version
2367 ,p_init_msg_list => p_init_msg_list
2368 ,x_return_status => x_return_status
2369 ,x_msg_count => x_msg_count
2370 ,x_msg_data => x_msg_data
2371 ,p_source_eff_from => cv_get_agrmnt_dates.start_date
2372 ,p_source_eff_to => cv_get_agrmnt_dates.end_date
2373 ,x_ech_rec => x_ech_rec -- OUT
2374 ,x_ecl_tbl => x_ecl_tbl -- OUT
2375 ,x_ecv_tbl => x_ecv_tbl -- OUT
2376 ,p_ech_rec => lx_ech_rec -- IN
2377 ,p_ecl_tbl => lx_ecl_tbl -- IN
2378 ,p_ecv_tbl => lx_ecv_tbl -- IN
2379 );
2380 IF(l_debug_enabled='Y') THEN
2381 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2382 l_module,
2383 'okl_ecc_values_pvt.handle_eligibility_criteria returned with status '||x_return_status
2384 );
2385 END IF;
2386 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2387 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2388 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2389 RAISE OKL_API.G_EXCEPTION_ERROR;
2390 END IF;
2391 END IF;
2392
2393 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2394
2395 IF(l_debug_enabled='Y') THEN
2396 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_elig_criteria');
2397 END IF;
2398 EXCEPTION
2399 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2400 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2401 p_api_name => l_api_name,
2402 p_pkg_name => G_PKG_NAME,
2403 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2404 x_msg_count => x_msg_count,
2405 x_msg_data => x_msg_data,
2406 p_api_type => g_api_type);
2407 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2408 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2409 p_api_name => l_api_name,
2410 p_pkg_name => G_PKG_NAME,
2411 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2412 x_msg_count => x_msg_count,
2413 x_msg_data => x_msg_data,
2414 p_api_type => g_api_type);
2415 WHEN OTHERS THEN
2416 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2417 p_api_name => l_api_name,
2418 p_pkg_name => G_PKG_NAME,
2419 p_exc_name => 'OTHERS',
2420 x_msg_count => x_msg_count,
2421 x_msg_data => x_msg_data,
2422 p_api_type => g_api_type);
2423 END sync_elig_criteria;
2424
2425 PROCEDURE sync_terms(p_api_version IN NUMBER
2426 ,p_init_msg_list IN VARCHAR2
2427 ,x_return_status OUT NOCOPY VARCHAR2
2428 ,x_msg_count OUT NOCOPY NUMBER
2429 ,x_msg_data OUT NOCOPY VARCHAR2
2430 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2431 ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2432 ) IS
2433
2434 -- cursor that finds out missing rule group records from the change request
2435 -- so that these records can be deleted from the original program agreement too
2436 CURSOR c_get_miss_rl_csr(cp_orig_chr_id okc_k_headers_b.id%TYPE
2437 ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2438 SELECT orig.id
2439 ,orig.dnz_chr_id
2440 ,orig.rgd_code
2441 FROM okc_rule_groups_v orig
2442 WHERE chr_id = cp_orig_chr_id
2443 AND cle_id IS NULL
2444 AND dnz_chr_id = cp_orig_chr_id
2445 AND NOT EXISTS (
2446 SELECT 'X'
2447 FROM okc_rule_groups_v creq
2448 WHERE creq.chr_id = cp_creq_chr_id
2449 AND creq.rgd_code = orig.rgd_code
2450 AND creq.dnz_chr_id = cp_creq_chr_id
2451 AND creq.cle_id IS NULL);
2452
2453 -- cursor that finds common rule group records on the change request as well as the originating
2454 -- program agreement
2455 CURSOR c_get_comm_rl_csr(cp_orig_chr_id okc_k_headers_b.id%TYPE
2456 ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2457 SELECT creq.id
2458 ,creq.dnz_chr_id
2459 ,creq.rgd_code
2460 FROM okc_rule_groups_v creq
2461 WHERE chr_id = cp_creq_chr_id
2462 AND cle_id IS NULL
2463 AND dnz_chr_id = cp_creq_chr_id
2464 AND EXISTS (
2465 SELECT 'X'
2466 FROM okc_rule_groups_v orig
2467 WHERE orig.chr_id = cp_orig_chr_id
2468 AND orig.rgd_code = creq.rgd_code
2469 AND orig.dnz_chr_id = cp_orig_chr_id
2470 AND orig.cle_id IS NULL);
2471
2472 CURSOR c_rl_exist_csr (cp_rul_info_cat okc_rules_b.rule_information_category%TYPE,
2473 cp_chr_id okc_k_headers_b.id%TYPE,
2474 cp_rgd_code okc_rule_groups_b.rgd_code%TYPE) IS
2475 SELECT rul.id
2476 ,rul.rgp_id
2477 ,rul.object_version_number
2478 FROM okc_rules_b rul
2479 ,okc_rule_groups_b rgp
2480 WHERE rgp.id = rul.rgp_id
2481 AND rgp.chr_id = rul.dnz_chr_id
2482 AND rul.rule_information_category = cp_rul_info_cat
2483 AND rgp.chr_id = cp_chr_id
2484 AND rgp.rgd_code = cp_rgd_code;
2485
2486 -- cursor that finds out new rule group records from the change request
2487 -- so that these records can be added to the original program agreement
2488 CURSOR c_get_new_rl_csr(cp_orig_chr_id okc_k_headers_b.id%TYPE
2489 ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2490 SELECT creq.id
2491 ,creq.dnz_chr_id
2492 ,creq.rgd_code
2493 ,creq.rgp_type
2494 FROM okc_rule_groups_v creq
2495 WHERE chr_id = cp_creq_chr_id
2496 AND cle_id IS NULL
2497 AND dnz_chr_id = cp_creq_chr_id
2498 AND NOT EXISTS (
2499 SELECT 'X'
2500 FROM okc_rule_groups_v orig
2501 WHERE orig.chr_id = cp_orig_chr_id
2502 AND orig.rgd_code = creq.rgd_code
2503 AND orig.dnz_chr_id = cp_orig_chr_id
2504 AND orig.cle_id IS NULL);
2505
2506 CURSOR c_get_residual_grp(cp_chr_id okc_k_headers_b.id%TYPE) IS
2507 SELECT id
2508 FROM okc_rule_groups_b
2509 WHERE dnz_chr_id = cp_chr_id
2510 AND rgd_code = 'VGLRS';
2511
2512 lv_rule_group_id okc_rule_groups_b.id%TYPE;
2513
2514 cv_rl_exist_rec c_rl_exist_csr%ROWTYPE;
2515
2516 lv_rule_info_tbl okl_rgrp_rules_process_pvt.rgr_tbl_type;
2517
2518 lv_rgpv_r1_rec OKL_RULE_PUB.rgpv_rec_type;
2519 lv_rgpv_r2_rec OKL_RULE_PUB.rgpv_rec_type;
2520 lx_rulv1_tbl OKL_RULE_PUB.rulv_tbl_type;
2521 lx_new_rgp_tbl okl_okc_migration_pvt.rgpv_tbl_type;
2522 lv_new_rgp_tbl okl_okc_migration_pvt.rgpv_tbl_type;
2523 x_new_rgp_rec okl_rule_pub.rgpv_rec_type;
2524 x_rulv2_tbl okl_rule_pub.rulv_tbl_type;
2525 lv_rgp_id okc_rule_groups_b.id%TYPE;
2526 lx_rulv2_tbl OKL_RULE_PUB.rulv_tbl_type;
2527 lv_rgpv_del_tbl okl_vp_rule_pub.rgpv_tbl_type;
2528 lv_rule_information1 okc_rules_b.rule_information1%TYPE;
2529
2530 lx_rulv_count NUMBER;
2531 lv_rulv2_count NUMBER;
2532 lv_process_idx PLS_INTEGER;
2533 lx_new_rg_count NUMBER;
2534
2535 l_api_version CONSTANT NUMBER DEFAULT 1.0;
2536 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_TERMS';
2537 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_TERMS';
2538 l_debug_enabled VARCHAR2(10);
2539
2540 FUNCTION get_original_cpl_id(p_new_cpl_id okc_k_party_roles_b.id%TYPE
2541 ,p_orig_chr_id okc_k_headers_b.id%TYPE
2542 ,p_creq_chr_id okc_k_headers_b.id%TYPE
2543 ) RETURN NUMBER IS
2544 CURSOR c_get_cpl_id_csr (cp_cpl_id okc_k_party_roles_b.id%TYPE
2545 ,cp_orig_chr_id okc_k_headers_b.id%TYPE
2546 ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2547 SELECT cpl.id
2548 FROM okc_k_party_roles_b cpl
2549 ,okc_k_party_roles_b cpl1
2550 WHERE cpl.chr_id = cp_orig_chr_id
2551 AND cpl.rle_code = cpl1.rle_code
2552 AND cpl1.chr_id = cp_creq_chr_id
2553 AND cpl.object1_id1 = cpl1.object1_id1
2554 AND cpl.object1_id2 = cpl1.object1_id2
2555 AND cpl.jtot_object1_code = cpl1.jtot_object1_code
2556 AND cpl1.id = cp_cpl_id;
2557
2558 lv_return_cpl_id okc_k_party_roles_b.id%TYPE;
2559 BEGIN
2560 -- the value is always guaranteed in this cursor as the terms are being synced after the parties
2561 -- sync. so even if the party was not on the original agreement, we would still get the id from okc_k_party_roles_b
2562 -- as that record would have been inserted in the table before executing sync_terms api
2563 OPEN c_get_cpl_id_csr (cp_cpl_id => p_new_cpl_id
2564 ,cp_orig_chr_id => p_orig_chr_id
2565 ,cp_creq_chr_id => p_creq_chr_id);
2566 FETCH c_get_cpl_id_csr INTO lv_return_cpl_id;
2567 CLOSE c_get_cpl_id_csr;
2568 RETURN lv_return_cpl_id;
2569 END get_original_cpl_id;
2570
2571 BEGIN
2572 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2573
2574 l_debug_enabled := okl_debug_pub.check_log_enabled;
2575
2576 IF(l_debug_enabled='Y') THEN
2577 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_terms');
2578 END IF;
2579
2580 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
2581 x_return_status := OKL_API.START_ACTIVITY(
2582 p_api_name => l_api_name
2583 ,p_pkg_name => G_PKG_NAME
2584 ,p_init_msg_list => p_init_msg_list
2585 ,l_api_version => l_api_version
2586 ,p_api_version => p_api_version
2587 ,p_api_type => g_api_type
2588 ,x_return_status => x_return_status);
2589 -- check if activity started successfully
2590 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2591 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2592 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2593 RAISE OKL_API.G_EXCEPTION_ERROR;
2594 END IF;
2595
2596 -- since it is possible for the user to go and delete a party after associating a residual share percent to that party,
2597 -- we have to delete the rule group and the information from the parent contract and get it re-created from the change request
2598 -- get the rule group information for the VGLRS rule group
2599 OPEN c_get_residual_grp(cp_chr_id => p_orig_chr_id); FETCH c_get_residual_grp INTO lv_rule_group_id;
2600 IF(c_get_residual_grp%FOUND)THEN
2601 CLOSE c_get_residual_grp;
2602 lv_rgpv_del_tbl(1).id := lv_rule_group_id;
2603 lv_rgpv_del_tbl(1).chr_id := p_orig_chr_id;
2604 lv_rgpv_del_tbl(1).rgd_code := 'VGLRS';
2605 okl_vp_rule_pub.delete_rule_group(p_api_version => p_api_version
2606 ,p_init_msg_list => p_init_msg_list
2607 ,x_return_status => x_return_status
2608 ,x_msg_count => x_msg_count
2609 ,x_msg_data => x_msg_data
2610 ,p_rgpv_tbl => lv_rgpv_del_tbl
2611 );
2612 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2613 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2614 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2615 RAISE OKL_API.G_EXCEPTION_ERROR;
2616 END IF;
2617 ELSE
2618 CLOSE c_get_residual_grp;
2619 END IF;
2620
2621 -- process all the rule groups that have been deleted from the change request, but are present on the
2622 -- originating agreement. these rule groups have to be deleted from the originating agreement
2623
2624 /*
2625 FOR cv_get_miss_rl_rec IN c_get_miss_rl_csr(cp_orig_chr_id => p_orig_chr_id, cp_creq_chr_id => p_creq_chr_id) LOOP
2626 lv_del_idx := lv_del_idx + 1;
2627 lv_rgpv_del_tbl(lv_del_idx).id := cv_get_miss_rl_rec.id;
2628 lv_rgpv_del_tbl(lv_del_idx).chr_id := cv_get_miss_rl_rec.dnz_chr_id;
2629 lv_rgpv_del_tbl(lv_del_idx).rgd_code := cv_get_miss_rl_rec.rgd_code;
2630 END LOOP;
2631 -- see if there are any records to delete from the originating agreement
2632 IF(lv_rgpv_del_tbl.COUNT > 0)THEN
2633 okl_vp_rule_pub.delete_rule_group(p_api_version => p_api_version
2634 ,p_init_msg_list => p_init_msg_list
2635 ,x_return_status => x_return_status
2636 ,x_msg_count => x_msg_count
2637 ,x_msg_data => x_msg_data
2638 ,p_rgpv_tbl => lv_rgpv_del_tbl
2639 );
2640 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2641 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2642 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2643 RAISE OKL_API.G_EXCEPTION_ERROR;
2644 END IF;
2645 END IF;
2646 */
2647 -- now find the rules that have been modified/created on the change request and whose rule groups
2648 -- exist on the original agreement, since the rule group information is not updateable, we process
2649 -- only the rule information here
2650
2651 FOR cv_get_comm_rl_rec IN c_get_comm_rl_csr(cp_orig_chr_id => p_orig_chr_id, cp_creq_chr_id => p_creq_chr_id) LOOP
2652 -- populate the rule group information record required to be passed to the rule retrieval api
2653 lv_rgpv_r1_rec.id := cv_get_comm_rl_rec.id;
2654 lv_rgpv_r1_rec.rgd_code := cv_get_comm_rl_rec.rgd_code;
2655 lv_rgpv_r1_rec.chr_id := cv_get_comm_rl_rec.dnz_chr_id;
2656 lv_rgpv_r1_rec.dnz_chr_id := cv_get_comm_rl_rec.dnz_chr_id;
2657 lx_rulv_count := 0;
2658 -- now get the rule(s) for this rule group
2659 okl_rule_apis_pvt.get_contract_rules(p_api_version => p_api_version
2660 ,p_init_msg_list => p_init_msg_list
2661 ,p_rgpv_rec => lv_rgpv_r1_rec
2662 ,p_rdf_code => null -- we want all the rules under this rule group
2663 ,x_return_status => x_return_status
2664 ,x_msg_count => x_msg_count
2665 ,x_msg_data => x_msg_data
2666 ,x_rulv_tbl => lx_rulv1_tbl
2667 ,x_rule_count => lx_rulv_count
2668 );
2669 IF(l_debug_enabled='Y') THEN
2670 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2671 l_module,
2672 'okl_rule_apis_pvt.get_contract_rules returned with status '||x_return_status
2673 );
2674 END IF;
2675 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2676 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2677 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2678 RAISE OKL_API.G_EXCEPTION_ERROR;
2679 END IF;
2680
2681 lv_process_idx := 0;
2682 IF(lx_rulv_count > 0)THEN
2683 FOR idx1 IN 1..lx_rulv_count LOOP
2684 OPEN c_rl_exist_csr(lx_rulv1_tbl(idx1).rule_information_category, p_orig_chr_id, cv_get_comm_rl_rec.rgd_code);
2685 FETCH c_rl_exist_csr INTO cv_rl_exist_rec;
2686 IF(c_rl_exist_csr%FOUND)THEN
2687 -- this is the case of update
2688 lv_process_idx := lv_process_idx + 1;
2689 lv_rule_info_tbl(lv_process_idx).rule_id := cv_rl_exist_rec.id;
2690 lv_rule_info_tbl(lv_process_idx).dnz_chr_id := p_orig_chr_id;
2691 lv_rule_info_tbl(lv_process_idx).rgp_id := cv_rl_exist_rec.rgp_id;
2692 lv_rule_info_tbl(lv_process_idx).object_version_number := cv_rl_exist_rec.object_version_number;
2693 ELSE -- c_rl_exist_csr NOTFOUND case
2694 lv_process_idx := lv_process_idx + 1;
2695 -- this is the case of inserting a new rule record under an existing rule group
2696 -- find the header rule group id for the parent agreement
2697 lv_rgp_id := okl_rgrp_rules_process_pvt.get_header_rule_group_id(p_api_version => p_api_version
2698 ,p_init_msg_list => p_init_msg_list
2699 ,x_return_status => x_return_status
2700 ,x_msg_count => x_msg_count
2701 ,x_msg_data => x_msg_data
2702 ,p_chr_id => p_orig_chr_id
2703 ,p_rgd_code => cv_get_comm_rl_rec.rgd_code
2704 );
2705 IF(l_debug_enabled='Y') THEN
2706 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2707 l_module,
2708 'okl_rgrp_rules_process_pvt.get_header_rule_group_id returned with status '||x_return_status||' lv_rgp_id '||lv_rgp_id
2709 );
2710 END IF;
2711 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2712 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2713 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2714 RAISE OKL_API.G_EXCEPTION_ERROR;
2715 END IF;
2716 -- since we are creating the rule record, pass the rule_id as null
2717 lv_rule_info_tbl(lv_process_idx).rule_id := null;
2718 lv_rule_info_tbl(lv_process_idx).dnz_chr_id := p_orig_chr_id;
2719 -- the rgp id should be the rgp_id of the original agreement
2720 lv_rule_info_tbl(lv_process_idx).rgp_id := lv_rgp_id;
2721 lv_rule_info_tbl(lv_process_idx).object_version_number := 1;
2722 END IF;
2723 CLOSE c_rl_exist_csr;
2724 lv_rule_info_tbl(lv_process_idx).rgd_code := cv_get_comm_rl_rec.rgd_code;
2725 lv_rule_info_tbl(lv_process_idx).object1_id1 := lx_rulv1_tbl(idx1).object1_id1;
2726 lv_rule_info_tbl(lv_process_idx).object2_id1 := lx_rulv1_tbl(idx1).object2_id1;
2727 lv_rule_info_tbl(lv_process_idx).object3_id1 := lx_rulv1_tbl(idx1).object3_id1;
2728 lv_rule_info_tbl(lv_process_idx).object1_id2 := lx_rulv1_tbl(idx1).object1_id2;
2729 lv_rule_info_tbl(lv_process_idx).object2_id2 := lx_rulv1_tbl(idx1).object2_id2;
2730 lv_rule_info_tbl(lv_process_idx).object3_id2 := lx_rulv1_tbl(idx1).object3_id2;
2731 lv_rule_info_tbl(lv_process_idx).jtot_object1_code := lx_rulv1_tbl(idx1).jtot_object1_code;
2732 lv_rule_info_tbl(lv_process_idx).jtot_object2_code := lx_rulv1_tbl(idx1).jtot_object2_code;
2733 lv_rule_info_tbl(lv_process_idx).jtot_object3_code := lx_rulv1_tbl(idx1).jtot_object3_code;
2734 lv_rule_info_tbl(lv_process_idx).priority := lx_rulv1_tbl(idx1).priority;
2735 lv_rule_info_tbl(lv_process_idx).std_template_yn := lx_rulv1_tbl(idx1).std_template_yn;
2736 lv_rule_info_tbl(lv_process_idx).comments := lx_rulv1_tbl(idx1).comments;
2737 lv_rule_info_tbl(lv_process_idx).warn_yn := lx_rulv1_tbl(idx1).warn_yn;
2738 lv_rule_info_tbl(lv_process_idx).rule_information_category := lx_rulv1_tbl(idx1).rule_information_category;
2739 lv_rule_info_tbl(lv_process_idx).rule_information1 := lx_rulv1_tbl(idx1).rule_information1;
2740 lv_rule_info_tbl(lv_process_idx).rule_information2 := lx_rulv1_tbl(idx1).rule_information2;
2741 lv_rule_info_tbl(lv_process_idx).rule_information3 := lx_rulv1_tbl(idx1).rule_information3;
2742 lv_rule_info_tbl(lv_process_idx).rule_information4 := lx_rulv1_tbl(idx1).rule_information4;
2743 lv_rule_info_tbl(lv_process_idx).rule_information5 := lx_rulv1_tbl(idx1).rule_information5;
2744 lv_rule_info_tbl(lv_process_idx).rule_information6 := lx_rulv1_tbl(idx1).rule_information6;
2745 lv_rule_info_tbl(lv_process_idx).rule_information7 := lx_rulv1_tbl(idx1).rule_information7;
2746 lv_rule_info_tbl(lv_process_idx).rule_information8 := lx_rulv1_tbl(idx1).rule_information8;
2747 lv_rule_info_tbl(lv_process_idx).rule_information9 := lx_rulv1_tbl(idx1).rule_information9;
2748 lv_rule_info_tbl(lv_process_idx).rule_information10 := lx_rulv1_tbl(idx1).rule_information10;
2749 lv_rule_info_tbl(lv_process_idx).rule_information11 := lx_rulv1_tbl(idx1).rule_information11;
2750 lv_rule_info_tbl(lv_process_idx).rule_information12 := lx_rulv1_tbl(idx1).rule_information12;
2751 lv_rule_info_tbl(lv_process_idx).rule_information13 := lx_rulv1_tbl(idx1).rule_information13;
2752 lv_rule_info_tbl(lv_process_idx).rule_information14 := lx_rulv1_tbl(idx1).rule_information14;
2753 lv_rule_info_tbl(lv_process_idx).rule_information15 := lx_rulv1_tbl(idx1).rule_information15;
2754 lv_rule_info_tbl(lv_process_idx).template_yn := lx_rulv1_tbl(idx1).template_yn;
2755 lv_rule_info_tbl(lv_process_idx).ans_set_jtot_object_code := lx_rulv1_tbl(idx1).ans_set_jtot_object_code;
2756 lv_rule_info_tbl(lv_process_idx).ans_set_jtot_object_id1 := lx_rulv1_tbl(idx1).ans_set_jtot_object_id1;
2757 lv_rule_info_tbl(lv_process_idx).ans_set_jtot_object_id2 := lx_rulv1_tbl(idx1).ans_set_jtot_object_id2;
2758 lv_rule_info_tbl(lv_process_idx).display_sequence := lx_rulv1_tbl(idx1).display_sequence;
2759 EXIT WHEN (idx1 = lx_rulv_count);
2760 END LOOP; -- end of loop next to lx_rulv_count > 0
2761 IF(lv_process_idx > 0)THEN
2762 okl_rgrp_rules_process_pvt.process_rule_group_rules(p_api_version => p_api_version
2763 ,p_init_msg_list => p_init_msg_list
2764 ,x_return_status => x_return_status
2765 ,x_msg_count => x_msg_count
2766 ,x_msg_data => x_msg_data
2767 ,p_chr_id => p_orig_chr_id
2768 ,p_line_id => null
2769 ,p_cpl_id => null
2770 ,p_rrd_id => null
2771 ,p_rgr_tbl => lv_rule_info_tbl
2772 );
2773 IF(l_debug_enabled='Y') THEN
2774 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2775 l_module,
2776 'okl_rgrp_rules_process_pvt.process_rule_group_rules returned with status '||x_return_status
2777 );
2778 END IF;
2779 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2780 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2781 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2782 RAISE OKL_API.G_EXCEPTION_ERROR;
2783 END IF;
2784 END IF;
2785 END IF; -- end of lx_rulv_count > 0
2786 END LOOP; -- end of for loop
2787
2788 -- now process for the new rule group records that have been added and their child rules
2789 FOR cv_get_new_rl_rec IN c_get_new_rl_csr(p_orig_chr_id, p_creq_chr_id) LOOP
2790 okl_rule_apis_pvt.get_contract_rgs(p_api_version => p_api_version
2791 ,p_init_msg_list => p_init_msg_list
2792 ,p_chr_id => p_creq_chr_id
2793 ,p_cle_id => null
2794 ,p_rgd_code => cv_get_new_rl_rec.rgd_code
2795 ,x_return_status => x_return_status
2796 ,x_msg_count => x_msg_count
2797 ,x_msg_data => x_msg_data
2798 ,x_rgpv_tbl => lx_new_rgp_tbl
2799 ,x_rg_count => lx_new_rg_count
2800 );
2801 IF(l_debug_enabled='Y') THEN
2802 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2803 l_module,
2804 'okl_rule_apis_pvt.get_contract_rgs returned with status '||x_return_status
2805 );
2806 END IF;
2807 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2808 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2809 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2810 RAISE OKL_API.G_EXCEPTION_ERROR;
2811 END IF;
2812 IF(lx_new_rg_count > 0)THEN
2813 lv_new_rgp_tbl := lx_new_rgp_tbl;
2814 FOR i IN 1..lv_new_rgp_tbl.COUNT LOOP
2815 lv_new_rgp_tbl(i).id := NULL;
2816 lv_new_rgp_tbl(i).chr_id := p_orig_chr_id;
2817 lv_new_rgp_tbl(i).dnz_chr_id := p_orig_chr_id;
2818 lv_new_rgp_tbl(i).cle_id := NULL;
2819 okl_okc_migration_pvt.create_rule_group(p_api_version => p_api_version
2820 ,p_init_msg_list => p_init_msg_list
2821 ,x_return_status => x_return_status
2822 ,x_msg_count => x_msg_count
2823 ,x_msg_data => x_msg_data
2824 ,p_rgpv_rec => lv_new_rgp_tbl(i)
2825 ,x_rgpv_rec => x_new_rgp_rec
2826 );
2827 IF(l_debug_enabled='Y') THEN
2828 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2829 l_module,
2830 'okl_okc_migration_pvt.create_rule_group returned with status '||x_return_status
2831 );
2832 END IF;
2833 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2834 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2835 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2836 RAISE OKL_API.G_EXCEPTION_ERROR;
2837 END IF;
2838 -- this is a new rule group all together that has to be created on the original agreement
2839 -- for the older rule group on the change request, we need to determine the rules that
2840 -- were present on the change request and copy those rules under this new rule group
2841 lv_rgpv_r2_rec.id := cv_get_new_rl_rec.id;
2842 lv_rgpv_r2_rec.rgd_code := cv_get_new_rl_rec.rgd_code;
2843 lv_rgpv_r2_rec.chr_id := p_creq_chr_id;
2844 lv_rgpv_r2_rec.dnz_chr_id := p_creq_chr_id;
2845 -- now create the rule based on the rule group record creation status
2846 -- for the rule record, the rgp_id should be the generated id of the new rule group record
2847 -- fetch the rule information for this rule group record
2848 okl_rule_apis_pvt.get_contract_rules(p_api_version => p_api_version
2849 ,p_init_msg_list => p_init_msg_list
2850 ,p_rgpv_rec => lv_rgpv_r2_rec
2851 ,p_rdf_code => null -- we want all the rules under this rule group
2852 ,x_return_status => x_return_status
2853 ,x_msg_count => x_msg_count
2854 ,x_msg_data => x_msg_data
2855 ,x_rulv_tbl => lx_rulv2_tbl
2856 ,x_rule_count => lv_rulv2_count
2857 );
2858 IF(l_debug_enabled='Y') THEN
2859 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2860 l_module,
2861 'okl_rule_apis_pvt.get_contract_rules1 returned with status '||x_return_status
2862 );
2863 END IF;
2864 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2865 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2866 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2867 RAISE OKL_API.G_EXCEPTION_ERROR;
2868 END IF;
2869 FOR j IN 1..lv_rulv2_count LOOP
2870 lx_rulv2_tbl(j).rgp_id := x_new_rgp_rec.id;
2871 lx_rulv2_tbl(j).dnz_chr_id := p_orig_chr_id;
2872 -- sjalasut: take care of the rule_information1 column that we populate with cpl_id for the vendor
2873 -- residual shre. this column now needs to point to the id of okc_k_party_roles on the original
2874 -- program agreement. the value from the get_contract_rules gives us the cpl_id that points to the
2875 -- change request
2876 -- vendor residual share percent is a very specific case. test if the rule belongs to vendor residual
2877 -- percentage before assigning the new cpl_id to the rule_information1 field.
2878 IF(lx_rulv2_tbl(j).rule_information_category = 'VGLRSP')THEN
2879 lv_rule_information1 := null;
2880 lv_rule_information1 := get_original_cpl_id(p_new_cpl_id => lx_rulv2_tbl(j).rule_information1
2881 ,p_orig_chr_id => p_orig_chr_id
2882 ,p_creq_chr_id => p_creq_chr_id
2883 );
2884 lx_rulv2_tbl(j).rule_information1 :=lv_rule_information1;
2885 END IF;
2886 END LOOP;
2887 IF(lv_rulv2_count > 0)THEN
2888 okl_rule_pub.create_rule(p_api_version => p_api_version
2889 ,p_init_msg_list => p_init_msg_list
2890 ,x_return_status => x_return_status
2891 ,x_msg_count => x_msg_count
2892 ,x_msg_data => x_msg_data
2893 ,p_rulv_tbl => lx_rulv2_tbl
2894 ,x_rulv_tbl => x_rulv2_tbl
2895 );
2896 IF(l_debug_enabled='Y') THEN
2897 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2898 l_module,
2899 'okl_rule_pub.create_rule returned with status '||x_return_status
2900 );
2901 END IF;
2902 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2903 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2904 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2905 RAISE OKL_API.G_EXCEPTION_ERROR;
2906 END IF;
2907 END IF;
2908 END LOOP;
2909 END IF;
2910 END LOOP; -- end of for loop for processing new rule group and new rules
2911
2912 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2913
2914 IF(l_debug_enabled='Y') THEN
2915 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_terms');
2916 END IF;
2917 EXCEPTION
2918 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2919 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2920 p_api_name => l_api_name,
2921 p_pkg_name => G_PKG_NAME,
2922 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2923 x_msg_count => x_msg_count,
2924 x_msg_data => x_msg_data,
2925 p_api_type => g_api_type);
2926 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2927 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2928 p_api_name => l_api_name,
2929 p_pkg_name => G_PKG_NAME,
2930 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2931 x_msg_count => x_msg_count,
2932 x_msg_data => x_msg_data,
2933 p_api_type => g_api_type);
2934 WHEN OTHERS THEN
2935 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2936 p_api_name => l_api_name,
2937 p_pkg_name => G_PKG_NAME,
2938 p_exc_name => 'OTHERS',
2939 x_msg_count => x_msg_count,
2940 x_msg_data => x_msg_data,
2941 p_api_type => g_api_type);
2942 END sync_terms;
2943
2944 PROCEDURE sync_vendor_disb_setup(p_api_version IN NUMBER
2945 ,p_init_msg_list IN VARCHAR2
2946 ,x_return_status OUT NOCOPY VARCHAR2
2947 ,x_msg_count OUT NOCOPY NUMBER
2948 ,x_msg_data OUT NOCOPY VARCHAR2
2949 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2950 ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2951 ) IS
2952 CURSOR c_get_disb_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
2953 SELECT pay.pay_site_id
2954 ,pay.payment_term_id
2955 ,pay.payment_method_code
2956 ,pay.pay_group_code
2957 ,pay.vendor_id
2958 ,pay.id
2959 ,role.rle_code
2960 ,role.object1_id1
2961 ,role.object1_id2
2962 ,role.jtot_object1_code
2963 ,role.id cpl_id
2964 FROM okl_party_payment_dtls pay
2965 ,okc_k_party_roles_b role
2966 WHERE pay.cpl_id = role.id
2967 AND role.cle_id IS NULL
2968 AND role.dnz_chr_id = cp_chr_id;
2969
2970 CURSOR c_get_orig_disb_csr(cp_chr_id okc_k_headers_b.id%TYPE
2971 ,cp_rle_code okc_k_party_roles_b.rle_code%TYPE
2972 ,cp_object1_id1 okc_k_party_roles_b.object1_id1%TYPE
2973 ,cp_object1_id2 okc_k_party_roles_b.object1_id2%TYPE
2974 ,cp_jtot_code okc_k_party_roles_b.jtot_object1_code%TYPE)IS
2975 SELECT role.id
2976 ,payment.id payment_id
2977 FROM okc_k_party_roles_b role
2978 ,okl_party_payment_dtls payment
2979 WHERE role.dnz_chr_id = p_orig_chr_id
2980 AND role.chr_id IS NOT NULL
2981 AND role.chr_id = cp_chr_id
2982 AND role.rle_code = cp_rle_code
2983 AND role.object1_id1 = cp_object1_id1
2984 AND role.object1_id2 = cp_object1_id2
2985 AND role.jtot_object1_code = cp_jtot_code
2986 AND role.id = payment.cpl_id;
2987 --udhenuko Bug 5201243 Commenting as the vendor and party need not be same
2988 --AND payment.vendor_id = cp_object1_id1;
2989
2990 CURSOR c_get_party_role_csr(cp_chr_id okc_k_headers_b.id%TYPE
2991 ,cp_rle_code okc_k_party_roles_b.rle_code%TYPE
2992 ,cp_object1_id1 okc_k_party_roles_b.object1_id1%TYPE
2993 ,cp_object1_id2 okc_k_party_roles_b.object1_id2%TYPE
2994 ,cp_jtot_code okc_k_party_roles_b.jtot_object1_code%TYPE)IS
2995 SELECT role.id
2996 FROM okc_k_party_roles_b role
2997 WHERE role.dnz_chr_id = cp_chr_id
2998 AND role.chr_id IS NOT NULL
2999 AND role.chr_id = p_orig_chr_id
3000 AND role.rle_code = cp_rle_code
3001 AND role.object1_id1 = cp_object1_id1
3002 AND role.object1_id2 = cp_object1_id2
3003 AND role.jtot_object1_code = cp_jtot_code;
3004
3005 lv_cpl_id okc_k_party_roles_b.id%TYPE;
3006 lv_payment_id okl_party_payment_dtls.id%TYPE;
3007
3008 lv_srfvv_rec okl_subsidy_rfnd_dtls_pvt.srfvv_rec_type;
3009 x_srfvv_rec okl_subsidy_rfnd_dtls_pvt.srfvv_rec_type;
3010 lv_party_role_id okc_k_party_roles_b.id%TYPE;
3011
3012 l_api_version CONSTANT NUMBER DEFAULT 1.0;
3013 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_VENDOR_DISB_SETUP';
3014 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_VENDOR_DISB_SETUP';
3015 l_debug_enabled VARCHAR2(10);
3016
3017 BEGIN
3018 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3019
3020 l_debug_enabled := okl_debug_pub.check_log_enabled;
3021
3022 IF(l_debug_enabled='Y') THEN
3023 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRCRSB.pls call sync_vendor_disb_setup');
3024 END IF;
3025
3026 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
3027 x_return_status := OKL_API.START_ACTIVITY(
3028 p_api_name => l_api_name
3029 ,p_pkg_name => G_PKG_NAME
3030 ,p_init_msg_list => p_init_msg_list
3031 ,l_api_version => l_api_version
3032 ,p_api_version => p_api_version
3033 ,p_api_type => g_api_type
3034 ,x_return_status => x_return_status);
3035 -- check if activity started successfully
3036 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3037 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3038 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3039 RAISE OKL_API.G_EXCEPTION_ERROR;
3040 END IF;
3041
3042 -- by the time control reaches here, the party records would have been merged. therefore the logic of merging the
3043 -- Disbursement setup is, to find the correct party record on PA and see if that party has disbursement info set, if so then update that info
3044 -- if something has changed between the PA and the CR. if the PA does not have a disbursement info, then create it from the CR (if CR has one)
3045
3046 -- get all the disbursement setup records from the change request parties
3047 FOR creq_disb_rec IN c_get_disb_csr(cp_chr_id => p_creq_chr_id) LOOP
3048 -- now find the cpl_id on the original agreement for this party information
3049 OPEN c_get_orig_disb_csr(cp_chr_id => p_orig_chr_id
3050 ,cp_rle_code => creq_disb_rec.rle_code
3051 ,cp_object1_id1 => creq_disb_rec.object1_id1
3052 ,cp_object1_id2 => creq_disb_rec.object1_id2
3053 ,cp_jtot_code => creq_disb_rec.jtot_object1_code
3054 );
3055 FETCH c_get_orig_disb_csr INTO lv_cpl_id, lv_payment_id;
3056 IF(c_get_orig_disb_csr%FOUND)THEN
3057 CLOSE c_get_orig_disb_csr;
3058 -- this is the case of update the original disbursement record on the Program Agreement
3059 lv_srfvv_rec.cpl_id := lv_cpl_id;
3060 lv_srfvv_rec.id := lv_payment_id;
3061
3062 lv_srfvv_rec.vendor_id := creq_disb_rec.vendor_id;
3063 lv_srfvv_rec.pay_site_id := creq_disb_rec.pay_site_id;
3064 lv_srfvv_rec.payment_term_id := creq_disb_rec.payment_term_id;
3065 lv_srfvv_rec.payment_method_code := creq_disb_rec.payment_method_code;
3066 lv_srfvv_rec.pay_group_code := creq_disb_rec.pay_group_code;
3067
3068 --udhenuko Bug 5201243 Calling OKL_VP_PARTY_PAYMENT_PVT API for update
3069 OKL_VP_PARTY_PAYMENT_PVT.update_party_pymnt_dtls(p_api_version => p_api_version
3070 ,p_init_msg_list => p_init_msg_list
3071 ,x_return_status => x_return_status
3072 ,x_msg_count => x_msg_count
3073 ,x_msg_data => x_msg_data
3074 ,p_srfvv_rec => lv_srfvv_rec
3075 ,x_srfvv_rec => x_srfvv_rec
3076 );
3077 IF(l_debug_enabled='Y') THEN
3078 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT, l_module, 'okl_subsidy_rfnd_dtls_pvt.update_refund_dtls '|| x_return_status);
3079 END IF;
3080
3081 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3082 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3083 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3084 RAISE OKL_API.G_EXCEPTION_ERROR;
3085 END IF;
3086 ELSE
3087 CLOSE c_get_orig_disb_csr;
3088 -- we need to create a Disbursement Setup Record for the Program Agreement
3089 -- we need to determine the id of okc_k_headers_b for the party in context. this value will be used to populate the cpl_id
3090 -- in okl_party_payment_dtls
3091 OPEN c_get_party_role_csr(cp_chr_id => p_orig_chr_id
3092 ,cp_rle_code => creq_disb_rec.rle_code
3093 ,cp_object1_id1 => creq_disb_rec.object1_id1
3094 ,cp_object1_id2 => creq_disb_rec.object1_id2
3095 ,cp_jtot_code => creq_disb_rec.jtot_object1_code
3096 );
3097 FETCH c_get_party_role_csr INTO lv_party_role_id;
3098 IF(c_get_party_role_csr%FOUND)THEN
3099 CLOSE c_get_party_role_csr;
3100 lv_srfvv_rec.id := NULL;
3101 lv_srfvv_rec.cpl_id := lv_party_role_id;
3102 lv_srfvv_rec.vendor_id := creq_disb_rec.vendor_id;
3103 lv_srfvv_rec.pay_site_id := creq_disb_rec.pay_site_id;
3104 lv_srfvv_rec.payment_term_id := creq_disb_rec.payment_term_id;
3105 lv_srfvv_rec.payment_method_code := creq_disb_rec.payment_method_code;
3106 lv_srfvv_rec.pay_group_code := creq_disb_rec.pay_group_code;
3107
3108 --udhenuko Bug 5201243 Calling OKL_VP_PARTY_PAYMENT_PVT API for create
3109 OKL_VP_PARTY_PAYMENT_PVT.create_party_pymnt_dtls(p_api_version => p_api_version
3110 ,p_init_msg_list => p_init_msg_list
3111 ,x_return_status => x_return_status
3112 ,x_msg_count => x_msg_count
3113 ,x_msg_data => x_msg_data
3114 ,p_srfvv_rec => lv_srfvv_rec
3115 ,x_srfvv_rec => x_srfvv_rec
3116 );
3117 IF(l_debug_enabled='Y') THEN
3118 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT, l_module, 'okl_subsidy_rfnd_dtls_pvt.create_refund_dtls '|| x_return_status);
3119 END IF;
3120
3121 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3122 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3123 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3124 RAISE OKL_API.G_EXCEPTION_ERROR;
3125 END IF;
3126 ELSE
3127 CLOSE c_get_party_role_csr;
3128 IF(l_debug_enabled='Y') THEN
3129 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT, l_module, 'could not determine the original party on the parent pa '||
3130 creq_disb_rec.rle_code||' '||creq_disb_rec.object1_id1||' '||creq_disb_rec.object1_id2||' '||creq_disb_rec.jtot_object1_code );
3131 END IF;
3132 -- need to log here because this is an exception case - unable to find the original party on the PA
3133 END IF; -- end of c_get_party_role_csr%FOUND
3134
3135 END IF;
3136 END LOOP;
3137
3138 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3139
3140 IF(l_debug_enabled='Y') THEN
3141 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRCRSB.pls call sync_vendor_disb_setup');
3142 END IF;
3143
3144 EXCEPTION
3145 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3146 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3147 p_api_name => l_api_name,
3148 p_pkg_name => G_PKG_NAME,
3149 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3150 x_msg_count => x_msg_count,
3151 x_msg_data => x_msg_data,
3152 p_api_type => g_api_type);
3153 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3154 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3155 p_api_name => l_api_name,
3156 p_pkg_name => G_PKG_NAME,
3157 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3158 x_msg_count => x_msg_count,
3159 x_msg_data => x_msg_data,
3160 p_api_type => g_api_type);
3161 WHEN OTHERS THEN
3162 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3163 p_api_name => l_api_name,
3164 p_pkg_name => G_PKG_NAME,
3165 p_exc_name => 'OTHERS',
3166 x_msg_count => x_msg_count,
3167 x_msg_data => x_msg_data,
3168 p_api_type => g_api_type);
3169 END sync_vendor_disb_setup;
3170
3171 END okl_vp_sync_cr_pvt;
[Home] [Help]