[Home] [Help]
PACKAGE BODY: APPS.OKL_MLA_CREATE_UPDATE_PUB
Source
1 PACKAGE BODY Okl_Mla_Create_Update_Pub AS
2 /* $Header: OKLPMCUB.pls 120.0 2006/11/22 12:14:06 zrehman noship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
6 G_API_TYPE VARCHAR2(3) := 'PUB';
7
8 G_RLE_CODE VARCHAR2(10) := 'LESSEE';
9 G_STS_CODE VARCHAR2(10) := 'NEW';
10 G_LEASE_VENDOR VARCHAR2(10) := 'OKL_VENDOR';
11
12 SUBTYPE chrv_rec_type IS OKL_OKC_MIGRATION_PVT.chrv_rec_type;
13 SUBTYPE khrv_rec_type IS OKL_CONTRACT_PUB.khrv_rec_type;
14
15 G_WF_EVT_KHR_CREATED CONSTANT VARCHAR2(41) := 'oracle.apps.okl.la.lease_contract.created';
16 G_WF_EVT_KHR_UPDATED CONSTANT VARCHAR2(41) := 'oracle.apps.okl.la.lease_contract.updated';
17
18 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(11) := 'CONTRACT_ID';
19
20
21
22
23 FUNCTION GET_AK_PROMPT(p_ak_region IN VARCHAR2, p_ak_attribute IN VARCHAR2)
24 RETURN VARCHAR2 IS
25
26 CURSOR ak_prompt_csr(p_ak_region VARCHAR2, p_ak_attribute VARCHAR2) IS
27 select a.attribute_label_long
28 from ak_region_items ri, ak_regions r, ak_attributes_vl a
29 where ri.region_code = r.region_code
30 and ri.region_application_id = r.region_application_id
31 and ri.attribute_code = a.attribute_code
32 and ri.attribute_application_id = a.attribute_application_id
33 and ri.region_code = p_ak_region
34 and ri.attribute_code = p_ak_attribute
35 ;
36
37 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
38 BEGIN
39 OPEN ak_prompt_csr(p_ak_region, p_ak_attribute);
40 FETCH ak_prompt_csr INTO l_ak_prompt;
41 CLOSE ak_prompt_csr;
42 RETURN(l_ak_prompt);
43 END;
44
45 PROCEDURE create_party(
46 p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 p_kpl_rec IN party_rec_type,
52 x_kpl_rec OUT NOCOPY party_rec_type
53 ) AS
54
55 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
56 l_api_name CONSTANT varchar2(30) := 'create_party';
57 l_api_version CONSTANT NUMBER := 1.0;
58
59 lp_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
60 lx_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
61 lp_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
62 lx_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
63
64 Begin
65 x_return_status := OKL_API.G_RET_STS_SUCCESS;
66 -- Call start_activity to create savepoint, check compatibility
67 -- and initialize message list
68 x_return_status := OKL_API.START_ACTIVITY (
69 l_api_name
70 ,p_init_msg_list
71 ,'_PVT'
72 ,x_return_status);
73 -- Check if activity started successfully
74 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
75 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
76 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
77 RAISE OKL_API.G_EXCEPTION_ERROR;
78 END IF;
79
80 lp_cplv_rec.id := p_kpl_rec.id;
81 lp_cplv_rec.object1_id1 := p_kpl_rec.object1_id1;
82 lp_cplv_rec.object1_id2 := p_kpl_rec.object1_id2;
83 lp_cplv_rec.jtot_object1_code := p_kpl_rec.jtot_object1_code;
84 lp_cplv_rec.rle_code := p_kpl_rec.rle_code;
85 lp_cplv_rec.dnz_chr_id := p_kpl_rec.dnz_chr_id;
86 lp_cplv_rec.chr_id := p_kpl_rec.chr_id;
87 lp_kplv_rec.attribute_category := p_kpl_rec.attribute_category;
88 lp_kplv_rec.attribute1 := p_kpl_rec.attribute1;
89 lp_kplv_rec.attribute2 := p_kpl_rec.attribute2;
90 lp_kplv_rec.attribute3 := p_kpl_rec.attribute3;
91 lp_kplv_rec.attribute4 := p_kpl_rec.attribute4;
92 lp_kplv_rec.attribute5 := p_kpl_rec.attribute5;
93 lp_kplv_rec.attribute6 := p_kpl_rec.attribute6;
94 lp_kplv_rec.attribute7 := p_kpl_rec.attribute7;
95 lp_kplv_rec.attribute8 := p_kpl_rec.attribute8;
96 lp_kplv_rec.attribute9 := p_kpl_rec.attribute9;
97 lp_kplv_rec.attribute10 := p_kpl_rec.attribute10;
98 lp_kplv_rec.attribute11 := p_kpl_rec.attribute11;
99 lp_kplv_rec.attribute12 := p_kpl_rec.attribute12;
100 lp_kplv_rec.attribute13 := p_kpl_rec.attribute13;
101 lp_kplv_rec.attribute14 := p_kpl_rec.attribute14;
102 lp_kplv_rec.attribute15 := p_kpl_rec.attribute15;
103
104 IF(p_kpl_rec.rle_code IS NOT NULL AND
105 NOT (p_kpl_rec.rle_code = 'LESSEE' OR p_kpl_rec.rle_code = 'LESSOR')) THEN
106 lp_kplv_rec.validate_dff_yn := 'Y';
107 END IF;
108
109 okl_k_party_roles_pvt.create_k_party_role(
110 p_api_version => p_api_version,
111 p_init_msg_list => p_init_msg_list,
112 x_return_status => x_return_status,
113 x_msg_count => x_msg_count,
114 x_msg_data => x_msg_data,
115 p_cplv_rec => lp_cplv_rec,
116 x_cplv_rec => lx_cplv_rec,
117 p_kplv_rec => lp_kplv_rec,
118 x_kplv_rec => lx_kplv_rec);
119
120 x_kpl_rec.id := lx_cplv_rec.id;
121
122 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
123 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
124 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
125 RAISE OKL_API.G_EXCEPTION_ERROR;
126 END IF;
127
128 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
129
130 EXCEPTION
131 WHEN OKL_API.G_EXCEPTION_ERROR THEN
132 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
133 l_api_name,
134 G_PKG_NAME,
135 'OKL_API.G_RET_STS_ERROR',
136 x_msg_count,
137 x_msg_data,
138 '_PVT');
139 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
140 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
141 l_api_name,
142 G_PKG_NAME,
143 'OKL_API.G_RET_STS_UNEXP_ERROR',
144 x_msg_count,
145 x_msg_data,
146 '_PVT');
147 WHEN OTHERS THEN
148 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
149 l_api_name,
150 G_PKG_NAME,
151 'OTHERS',
152 x_msg_count,
153 x_msg_data,
154 '_PVT');
155
156 end;
157
158
159 PROCEDURE create_from_template(
160 p_api_version IN NUMBER,
161 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
162 x_return_status OUT NOCOPY VARCHAR2,
163 x_msg_count OUT NOCOPY NUMBER,
164 x_msg_data OUT NOCOPY VARCHAR2,
165 p_contract_number IN VARCHAR2,
166 p_source_chr_id IN NUMBER,
167 x_chr_id OUT NOCOPY NUMBER) AS
168
169 l_end_date OKL_K_HEADERS_FULL_V.END_DATE%TYPE DEFAULT NULL;
170 l_start_date OKL_K_HEADERS_FULL_V.START_DATE%TYPE DEFAULT NULL;
171 l_term_duration OKL_K_HEADERS_FULL_V.TERM_DURATION%TYPE DEFAULT NULL;
172
173 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
174 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
175 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
176 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
177
178 CURSOR get_k_start_date_and_term_csr(l_chr_id NUMBER) IS
179 SELECT chr.start_date, khr.term_duration
180 FROM okl_k_headers khr,
181 okc_k_headers_b chr
182 WHERE khr.id = chr.id
183 AND chr.id = l_chr_id;
184 l_scs_code VARCHAR2(30);
185
186 Cursor l_scs_csr is
187 Select scs_code
188 From okc_k_headers_b
189 where id = p_source_chr_id;
190
191 BEGIN
192
193 OPEN l_scs_csr;
194 FETCH l_scs_csr into l_scs_code;
195 CLOSE l_scs_csr;
196
197 IF (l_scs_code IS NOT NULL) AND (l_scs_code = 'MASTER_LEASE') Then
198 OKL_COPY_CONTRACT_PUB.copy_lease_contract(
199 p_api_version => p_api_version,
200 p_init_msg_list => p_init_msg_list,
201 x_return_status => x_return_status,
202 x_msg_count => x_msg_count,
203 x_msg_data => x_msg_data,
204 p_chr_id => p_source_chr_id,
205 p_contract_number => p_contract_number,
206 p_contract_number_modifier => null,
207 p_renew_ref_yn => OKC_API.G_FALSE,
208 p_trans_type => 'CFA',
209 x_chr_id => x_chr_id);
210 ELSE
211 OKL_COPY_CONTRACT_PUB.copy_lease_contract_new(
212 p_api_version => p_api_version,
213 p_init_msg_list => p_init_msg_list,
214 x_return_status => x_return_status,
215 x_msg_count => x_msg_count,
216 x_msg_data => x_msg_data,
217 p_chr_id => p_source_chr_id,
218 p_contract_number => p_contract_number,
219 p_contract_number_modifier => NULL,
220 p_renew_ref_yn => OKC_API.G_FALSE,
221 p_trans_type => 'CFA',
222 x_chr_id => x_chr_id);
223 END IF;
224
225 FOR get_k_start_date_and_term_rec IN get_k_start_date_and_term_csr(x_chr_id)
226 LOOP
227 l_end_date := OKL_LLA_UTIL_PVT.calculate_end_date(get_k_start_date_and_term_rec.start_date,get_k_start_date_and_term_rec.term_duration);
228 END LOOP;
229 lp_chrv_rec.id := x_chr_id;
230 lp_khrv_rec.id := x_chr_id;
231 lp_khrv_rec.template_type_code := OKL_TEMP_TYPE_CONTRACT;
232 lp_chrv_rec.end_date := l_end_date;
233
234
235 IF l_end_date IS NOT NULL THEN
236 OKL_CONTRACT_PUB.update_contract_header(
237 p_api_version => p_api_version,
238 p_init_msg_list => p_init_msg_list,
239 x_return_status => x_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data,
242 p_chrv_rec => lp_chrv_rec,
243 p_khrv_rec => lp_khrv_rec,
244 x_chrv_rec => lx_chrv_rec,
245 x_khrv_rec => lx_khrv_rec);
246 END IF;
247
248 END;
249
250
251 -- Start of comments
252 --
253 -- Procedure Name : create_from_contract
254 -- Description : creates a deal from a template
255 -- Business Rules :
256 -- Parameters :
257 -- Version : 1.0
258 -- End of comments
259 PROCEDURE create_from_contract(
260 p_api_version IN NUMBER,
261 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER,
264 x_msg_data OUT NOCOPY VARCHAR2,
265 p_contract_number IN VARCHAR2,
266 p_source_chr_id IN NUMBER,
267 x_chr_id OUT NOCOPY NUMBER) AS
268
269
270 l_scs_code VARCHAR2(30);
271
272 Cursor l_scs_csr is
273 Select scs_code
274 From okc_k_headers_b
275 where id = p_source_chr_id;
276 BEGIN
277
278 --Call the old api in case of MASTER_LEASE agreement
279 OPEN l_scs_csr;
280 FETCH l_scs_csr into l_scs_code;
281 CLOSE l_scs_csr;
282
283 IF (l_scs_code IS NOT NULL) AND (l_scs_code = 'MASTER_LEASE') Then
284 OKL_COPY_CONTRACT_PUB.copy_lease_contract(
285 p_api_version => p_api_version,
286 p_init_msg_list => p_init_msg_list,
287 x_return_status => x_return_status,
288 x_msg_count => x_msg_count,
289 x_msg_data => x_msg_data,
290 p_chr_id => p_source_chr_id,
291 p_contract_number => p_contract_number,
292 p_contract_number_modifier => null,
293 p_renew_ref_yn => OKC_API.G_FALSE,
294 p_trans_type => 'CFA',
295 x_chr_id => x_chr_id);
296 ELSE
297 OKL_COPY_CONTRACT_PUB.copy_lease_contract_new(
298 p_api_version => p_api_version,
299 p_init_msg_list => p_init_msg_list,
300 x_return_status => x_return_status,
301 x_msg_count => x_msg_count,
302 x_msg_data => x_msg_data,
303 p_chr_id => p_source_chr_id,
304 p_contract_number => p_contract_number,
305 p_contract_number_modifier => NULL,
306 p_renew_ref_yn => OKC_API.G_FALSE,
307 p_trans_type => 'CFA',
308 x_chr_id => x_chr_id);
309 END IF;
310
311 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
312 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
313 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
314 RAISE OKC_API.G_EXCEPTION_ERROR;
315 END IF;
316
317 END;
318
319
320 PROCEDURE create_new_deal(
321 p_api_version IN NUMBER,
322 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
323 x_return_status OUT NOCOPY VARCHAR2,
324 x_msg_count OUT NOCOPY NUMBER,
325 x_msg_data OUT NOCOPY VARCHAR2,
326 p_contract_number IN VARCHAR2,
327 p_scs_code IN VARCHAR2,
328 p_customer_id1 IN OUT NOCOPY VARCHAR2,
329 p_customer_id2 IN OUT NOCOPY VARCHAR2,
330 p_customer_code IN VARCHAR2,
331 p_customer_name IN VARCHAR2,
332 p_template_yn IN VARCHAR2,
333 p_template_type IN VARCHAR2,
334 p_effective_from IN DATE,
335 p_program_name IN VARCHAR2,
336 p_program_id IN NUMBER,
337 x_chr_id OUT NOCOPY NUMBER,
338 p_legal_entity_id IN NUMBER) AS
339
340 SUBTYPE l_cplv_tbl_type is OKL_OKC_MIGRATION_PVT.cplv_tbl_type;
341 SUBTYPE l_kplv_tbl_type is okl_kpl_pvt.kplv_tbl_type;
342
343 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
344 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
345 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
346 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
347
348 lp_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
349 lx_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
350
351 l_cplv_tbl l_cplv_tbl_type;
352 l_kplv_tbl l_kplv_tbl_type;
353 lx_cplv_tbl l_cplv_tbl_type;
354 lx_kplv_tbl l_kplv_tbl_type;
355
356 l_api_version CONSTANT NUMBER := 1.0;
357 l_api_name VARCHAR2(30) := 'CREATE_NEW_DEAL';
358
359 row_count NUMBER DEFAULT 0;
360
361 CURSOR check_party_csr(p_chr_id NUMBER) IS
362 SELECT COUNT(1)
363 FROM okc_k_party_roles_B
364 WHERE dnz_chr_id = p_chr_id
365 AND chr_id = p_chr_id
366 AND rle_code = G_RLE_CODE
367 AND object1_id1 = p_customer_id1
368 AND object1_id2 = p_customer_id2
369 ;
370
371 CURSOR role_csr(p_rle_code VARCHAR2) IS
372 SELECT access_level
373 FROM OKC_ROLE_SOURCES
374 WHERE rle_code = p_rle_code
375 AND buy_or_sell = 'S';
376
377 l_access_level OKC_ROLE_SOURCES.access_level%TYPE;
378
379 lp_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
380 lx_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
381
382 l_vp_cpl_id okc_k_party_roles_v.id%TYPE := NULL;
383 x_cpl_id okc_k_party_roles_v.id%TYPE := NULL;
384 l_chr_id okc_k_headers_b.id%type := NULL;
385
386 CURSOR c_vp_cpl_csr(p_source_id NUMBER) IS
387 SELECT id, object_version_number, sfwt_flag,
388 cpl_id, chr_id, cle_id,
389 rle_code, dnz_chr_id, object1_id1,
390 object1_id2, jtot_object1_code, cognomen,
391 code, facility, minority_group_lookup_code,
392 small_business_flag, women_owned_flag, alias,
393 attribute_category, attribute1, attribute2,
394 attribute3, attribute4, attribute5,
395 attribute6, attribute7, attribute8,
396 attribute9, attribute10, attribute11,
397 attribute12, attribute13, attribute14,
398 attribute15, created_by, creation_date,
399 last_updated_by, last_update_date, last_update_login,
400 cust_acct_id, bill_to_site_use_id
401 FROM okc_k_party_roles_v cplv
402 WHERE cplv.rle_code = G_LEASE_VENDOR
403 AND cplv.chr_id = p_source_id; -- vendor program id
404
405
406 BEGIN
407 x_return_status := OKC_API.START_ACTIVITY(
408 p_api_name => l_api_name,
409 p_pkg_name => g_pkg_name,
410 p_init_msg_list => p_init_msg_list,
411 l_api_version => l_api_version,
412 p_api_version => p_api_version,
413 p_api_type => g_api_type,
414 x_return_status => x_return_status);
415
416 -- check if activity started successfully
417 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
418 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
419 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
420 RAISE OKC_API.G_EXCEPTION_ERROR;
421 END IF;
422
423 lp_chrv_rec.sfwt_flag := 'N';
424 lp_chrv_rec.object_version_number := 1.0;
425 lp_chrv_rec.sts_code := G_STS_CODE; -- 'ENTERED';
426 lp_chrv_rec.scs_code := p_scs_code;
427 lp_chrv_rec.contract_number := p_contract_number;
428 lp_chrv_rec.authoring_org_id := OKL_CONTEXT.GET_OKC_ORG_ID;
429 lp_chrv_rec.inv_organization_id := OKL_CONTEXT.get_okc_organization_id;
430 lp_chrv_rec.currency_code := OKL_ACCOUNTING_UTIL.get_func_curr_code;
431 lp_chrv_rec.currency_code_renewed := NULL;
432 lp_chrv_rec.template_yn := 'N';
433 lp_chrv_rec.chr_type := 'CYA';
434 lp_chrv_rec.archived_yn := 'N';
435 lp_chrv_rec.deleted_yn := 'N';
436 lp_chrv_rec.buy_or_sell := 'S';
437 lp_chrv_rec.issue_or_receive := 'I';
438 lp_chrv_rec.start_date := p_effective_from;
439 lp_khrv_rec.object_version_number := 1.0;
440
441 IF ( p_program_name IS NOT NULL ) THEN
442 lp_khrv_rec.khr_id := p_program_id;
443 END IF;
444
445 IF ( p_template_type IS NOT NULL ) THEN
446 lp_khrv_rec.template_type_code := p_template_type;
447 lp_chrv_rec.template_yn := 'Y';
448 END IF;
449 lp_khrv_rec.legal_entity_id := p_legal_entity_id;
450 OKL_CONTRACT_PUB.create_contract_header(
451 p_api_version => p_api_version,
452 p_init_msg_list => p_init_msg_list,
453 x_return_status => x_return_status,
454 x_msg_count => x_msg_count,
455 x_msg_data => x_msg_data,
456 p_chrv_rec => lp_chrv_rec,
457 p_khrv_rec => lp_khrv_rec,
458 x_chrv_rec => lx_chrv_rec,
459 x_khrv_rec => lx_khrv_rec);
460
461 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
462 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
463 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
464 RAISE OKC_API.G_EXCEPTION_ERROR;
465 END IF;
466
467 x_chr_id := lx_chrv_rec.id;
468
469 IF ( p_program_name IS NOT NULL ) THEN
470
471 -- copy vp party lease vendor to lease contract
472 l_vp_cpl_id := NULL;
473
474 OPEN c_vp_cpl_csr(p_program_id);
475 FETCH c_vp_cpl_csr BULK COLLECT INTO l_cplv_tbl;
476 CLOSE c_vp_cpl_csr;
477
478 IF( l_cplv_tbl.COUNT > 0 ) THEN
479
480 FOR i IN l_cplv_tbl.FIRST..l_cplv_tbl.LAST
481 LOOP
482 l_cplv_tbl(i).ID := null;
483 IF (l_cplv_tbl(i).CHR_ID IS NOT NULL) THEN
484 l_cplv_tbl(i).CHR_ID := x_chr_id;
485 END IF;
486 IF (l_cplv_tbl(i).DNZ_CHR_ID IS NOT NULL) THEN
487 l_cplv_tbl(i).DNZ_CHR_ID := x_chr_id;
488 END IF;
489 l_kplv_tbl(i).attribute_category := null;
490 END LOOP;
491
492 IF okl_context.get_okc_org_id IS NULL THEN
493 l_chr_id := x_chr_id;
494 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
495 END IF;
496 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
497 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cplv_tbl.count=' || l_cplv_tbl.count);
498 END IF;
499 okl_k_party_roles_pvt.create_k_party_role(
500 p_api_version => p_api_version,
501 p_init_msg_list => p_init_msg_list,
502 x_return_status => x_return_status,
503 x_msg_count => x_msg_count,
504 x_msg_data => x_msg_data,
505 p_cplv_tbl => l_cplv_tbl,
506 x_cplv_tbl => lx_cplv_tbl,
507 p_kplv_tbl => l_kplv_tbl,
508 x_kplv_tbl => lx_kplv_tbl);
509
510 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
511 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
512 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
513 RAISE OKC_API.G_EXCEPTION_ERROR;
514 END IF;
515
516 END IF;
517
518 END IF;
519 IF ( p_customer_name IS NOT NULL ) THEN
520
521 -- now we attach the party to the header
522 lp_cplv_rec.object_version_number := 1.0;
523 lp_cplv_rec.sfwt_flag := OKC_API.G_FALSE;
524 lp_cplv_rec.dnz_chr_id := x_chr_id;
525 lp_cplv_rec.chr_id := x_chr_id;
526 lp_cplv_rec.cle_id := NULL;
527 lp_cplv_rec.object1_id1 := p_customer_id1;
528 lp_cplv_rec.object1_id2 := p_customer_id2;
529 lp_cplv_rec.jtot_object1_code := p_customer_code;
530 lp_cplv_rec.rle_code := G_RLE_CODE;
531
532 OPEN check_party_csr(x_chr_id);
533 FETCH check_party_csr INTO row_count;
534 CLOSE check_party_csr;
535 IF row_count = 1 THEN
536 x_return_status := OKC_API.g_ret_sts_error;
537 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'Party_already_exists');
538 RAISE OKC_API.G_EXCEPTION_ERROR;
539 END IF;
540
541
542 OPEN role_csr(lp_cplv_rec.rle_code);
543 FETCH role_csr INTO l_access_level;
544 CLOSE role_csr;
545
546 IF (l_access_level = 'S') THEN
547
548 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
549 p_init_msg_list => OKC_API.G_FALSE,
550 x_return_status => x_return_status,
551 x_msg_count => x_msg_count,
552 x_msg_data => x_msg_data,
553 p_object_name => lp_cplv_rec.jtot_object1_code,
554 p_id1 => lp_cplv_rec.object1_id1,
555 p_id2 => lp_cplv_rec.object1_id2);
556 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
557 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
558 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
559 RAISE OKC_API.G_EXCEPTION_ERROR;
560 END IF;
561
562 END IF;
563
564 okl_k_party_roles_pvt.create_k_party_role(
565 p_api_version => p_api_version,
566 p_init_msg_list => p_init_msg_list,
567 x_return_status => x_return_status,
568 x_msg_count => x_msg_count,
569 x_msg_data => x_msg_data,
570 p_cplv_rec => lp_cplv_rec,
571 x_cplv_rec => lx_cplv_rec,
572 p_kplv_rec => lp_kplv_rec,
573 x_kplv_rec => lx_kplv_rec);
574
575 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
576 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
577 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
578 RAISE OKC_API.G_EXCEPTION_ERROR;
579 END IF;
580
581 END IF;
582
583
584 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
585
586 EXCEPTION
587 WHEN OKC_API.G_EXCEPTION_ERROR THEN
588 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
589 p_api_name => l_api_name,
590 p_pkg_name => g_pkg_name,
591 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
592 x_msg_count => x_msg_count,
593 x_msg_data => x_msg_data,
594 p_api_type => g_api_type);
595
596 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
597 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
598 p_api_name => l_api_name,
599 p_pkg_name => g_pkg_name,
600 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
601 x_msg_count => x_msg_count,
602 x_msg_data => x_msg_data,
603 p_api_type => g_api_type);
604
605 WHEN OTHERS THEN
606 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
607 p_api_name => l_api_name,
608 p_pkg_name => g_pkg_name,
609 p_exc_name => 'OTHERS',
610 x_msg_count => x_msg_count,
611 x_msg_data => x_msg_data,
612 p_api_type => g_api_type);
613 END;
614
615 -- Start of comments
616 --
617 -- Procedure Name : create_deal
618 -- Description : creates a deal based on the information that comes
619 -- from the deal creation screen
620 -- Business Rules :
621 -- Parameters :
622 -- Version : 1.0
623 -- End of comments
624 PROCEDURE create_deal(
625 p_api_version IN NUMBER,
626 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
627 x_return_status OUT NOCOPY VARCHAR2,
628 x_msg_count OUT NOCOPY NUMBER,
629 x_msg_data OUT NOCOPY VARCHAR2,
630 p_source_code IN VARCHAR2,
631 p_template_type IN VARCHAR2,
632 p_contract_number IN VARCHAR2,
633 p_scs_code IN VARCHAR2,
634 p_customer_id1 IN OUT NOCOPY VARCHAR2,
635 p_customer_id2 IN OUT NOCOPY VARCHAR2,
636 p_customer_code IN VARCHAR2,
637 p_customer_name IN VARCHAR2,
638 p_effective_from IN DATE,
639 p_program_name IN VARCHAR2,
640 p_program_id IN NUMBER,
641 p_org_id IN NUMBER,
642 p_organization_id IN NUMBER,
643 p_source_chr_id IN OUT NOCOPY NUMBER,
644 p_source_contract_number IN VARCHAR2,
645 x_chr_id OUT NOCOPY NUMBER,
646 p_legal_entity_id IN NUMBER) AS
647
648 l_api_name VARCHAR2(30) := 'CREATE_DEAL';
649 l_api_version CONSTANT NUMBER := 1.0;
650 l_program_id NUMBER;
651
652 -- cursor when only customer is selected
653 CURSOR l_source_chr_id_crs(p_scs_code VARCHAR2, p_temp_yn VARCHAR2, p_object1_id1 VARCHAR2, p_object1_id2 VARCHAR2, p_customer_code VARCHAR2) IS
654 SELECT CHR.id
655 FROM okc_k_headers_b CHR
656 , okc_k_party_roles_b prl
657 WHERE prl.dnz_chr_id = CHR.id
658 AND prl.chr_id = CHR.id
659 AND CHR.scs_code = p_scs_code
660 AND CHR.chr_type = 'CYA'
661 AND NVL(chr.template_yn,'N') = p_temp_yn
662 AND prl.rle_code = G_RLE_CODE
663 AND prl.object1_id1 = p_object1_id1
664 AND prl.object1_id2 = p_object1_id2
665 AND prl.jtot_object1_code = p_customer_code
666 AND CHR.contract_number = p_source_contract_number;
667
668 -- cursor when only customer and program is selected
669 CURSOR l_source_chr_prog_id_crs(p_scs_code VARCHAR2, p_temp_yn VARCHAR2, p_object1_id1 VARCHAR2, p_object1_id2 VARCHAR2, p_customer_code VARCHAR2, l_prog_id NUMBER) IS
670 SELECT CHR.id
671 FROM okc_k_headers_b CHR,
672 okl_k_headers khr
673 , okc_k_party_roles_b prl
674 WHERE chr.id = khr.id
675 AND prl.dnz_chr_id = CHR.id
676 AND prl.chr_id = CHR.id
677 AND CHR.scs_code = p_scs_code
678 AND CHR.chr_type = 'CYA'
679 AND NVL(CHR.template_yn,'N') = p_temp_yn
680 AND prl.rle_code = G_RLE_CODE
681 AND prl.object1_id1 = p_object1_id1
682 AND prl.object1_id2 = p_object1_id2
683 AND prl.jtot_object1_code = p_customer_code
684 AND CHR.contract_number = p_source_contract_number
685 AND exists( select 1 from okl_vp_associations vpaso
686 where vpaso.chr_id = l_prog_id);
687
688 -- cursor when only program is selected
689 CURSOR l_source_prog_crs(p_scs_code VARCHAR2, p_temp_yn VARCHAR2, l_prog_id NUMBER) IS
690 SELECT CHR.id
691 FROM okc_k_headers_b CHR,
692 okl_k_headers khr
693 WHERE chr.id = khr.id
694 AND CHR.scs_code = p_scs_code
695 AND CHR.chr_type = 'CYA'
696 AND NVL(CHR.template_yn,'N') = p_temp_yn
697 AND CHR.contract_number = p_source_contract_number
698 AND exists( select 1 from okl_vp_associations vpaso
699 where vpaso.chr_id = l_prog_id);
700
701 CURSOR l_src_chr_id_crs(p_scs_code VARCHAR2, p_temp_yn VARCHAR2) IS
702 SELECT CHR.id
703 FROM okc_k_headers_b CHR
704 WHERE CHR.scs_code = p_scs_code
705 AND CHR.chr_type = 'CYA'
706 AND CHR.template_yn = p_temp_yn
707 AND CHR.contract_number = p_source_contract_number;
708
709
710 CURSOR l_program_csr IS
711 SELECT chr.id
712 FROM okl_k_headers_full_v chr
713 WHERE chr.scs_code = 'PROGRAM'
714 AND nvl(chr.template_yn, 'N') = 'N'
715 AND chr.sts_code = 'ACTIVE'
716 AND chr.authoring_org_id = p_org_id
717 AND NVL(chr.start_date,p_effective_from) <= p_effective_from
718 AND NVL(chr.end_date,p_effective_from) >= p_effective_from
719 AND chr.contract_number = p_program_name;
720
721
722 l_template_yn OKC_K_HEADERS_B.TEMPLATE_YN%TYPE;
723 l_object_code VARCHAR2(30) DEFAULT NULL;
724 l_chr_id OKC_K_HEADERS_B.ID%TYPE;
725
726 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
727
728 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
729 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
730 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
731 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
732
733 PROCEDURE raise_business_event(
734 p_chr_id IN NUMBER
735 ,x_return_status OUT NOCOPY VARCHAR2
736 )
737 IS
738 l_check VARCHAR2(1);
739 l_parameter_list wf_parameter_list_t;
740 BEGIN
741
742 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
743 IF (p_source_code = 'new') THEN
744 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,p_chr_id,l_parameter_list);
745
746 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
747 p_init_msg_list => p_init_msg_list,
748 x_return_status => x_return_status,
749 x_msg_count => x_msg_count,
750 x_msg_data => x_msg_data,
751 p_event_name => G_WF_EVT_KHR_CREATED,
752 p_parameters => l_parameter_list);
753
754 END IF;
755
756 EXCEPTION
757 WHEN OTHERS THEN
758 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
759 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
760 END raise_business_event;
761
762
763 BEGIN
764
765 OKC_CONTEXT.SET_OKC_ORG_CONTEXT(
766 p_org_id => p_org_id,
767 p_organization_id => p_organization_id);
768
769 x_return_status := OKC_API.START_ACTIVITY(
770 p_api_name => l_api_name,
771 p_pkg_name => g_pkg_name,
772 p_init_msg_list => p_init_msg_list,
773 l_api_version => l_api_version,
774 p_api_version => p_api_version,
775 p_api_type => g_api_type,
776 x_return_status => x_return_status);
777
778 -- check if activity started successfully
779 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
780 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
781 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
782 RAISE OKC_API.G_EXCEPTION_ERROR;
783 END IF;
784
785 IF(p_customer_name IS NULL) THEN
786 IF p_scs_code = 'MASTER_LEASE' THEN
787 x_return_status := OKC_API.g_ret_sts_error;
788 l_ak_prompt := GET_AK_PROMPT('OKL_LA_DEAL_CREAT', 'OKL_CUSTOMER_NAME');
789 OKC_API.SET_MESSAGE( p_app_name => g_app_name
790 , p_msg_name => 'OKL_REQUIRED_VALUE'
791 , p_token1 => 'COL_NAME'
792 , p_token1_value => l_ak_prompt
793 );
794 RAISE OKC_API.G_EXCEPTION_ERROR;
795 END IF;
796 END IF;
797
798 IF(p_program_name IS NOT NULL AND p_scs_code <> 'MASTER_LEASE' ) THEN
799 l_program_id := null;
800 open l_program_csr;
801 fetch l_program_csr into l_program_id;
802 close l_program_csr;
803
804 IF( l_program_id IS NULL ) THEN
805
806 x_return_status := OKC_API.g_ret_sts_error;
807 l_ak_prompt := GET_AK_PROMPT('OKL_LA_DEAL_CREAT', 'OKL_PROGRAM');
808 OKC_API.SET_MESSAGE( p_app_name => g_app_name
809 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
810 , p_token1 => 'COL_NAME'
811 , p_token1_value => l_ak_prompt
812 );
813 RAISE OKC_API.G_EXCEPTION_ERROR;
814
815 END IF;
816 END IF;
817
818 IF(p_source_code <> 'new' AND p_source_contract_number IS NULL) THEN
819 x_return_status := OKC_API.g_ret_sts_error;
820 l_ak_prompt := GET_AK_PROMPT('OKL_LA_DEAL_CREAT', 'OKL_CONTRACT_SOURCE');
821 OKC_API.SET_MESSAGE( p_app_name => g_app_name
822 , p_msg_name => 'OKL_REQUIRED_VALUE'
823 , p_token1 => 'COL_NAME'
824 , p_token1_value => l_ak_prompt
825 );
826 RAISE OKC_API.G_EXCEPTION_ERROR;
827 END IF;
828
829 IF(p_customer_name IS NOT NULL) THEN
830
831 okl_la_validation_util_pvt.Get_Party_Jtot_data (
832 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_scs_code => p_scs_code,
838 p_buy_or_sell => 'S',
839 p_rle_code => G_RLE_CODE,
840 p_id1 => p_customer_id1,
841 p_id2 => p_customer_id2,
842 p_name => p_customer_name,
843 p_object_code => l_object_code,
844 p_ak_region => 'OKL_LA_DEAL_CREAT',
845 p_ak_attribute => 'OKL_CUSTOMER_NAME'
846 );
847
848 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
849 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
850 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
851 RAISE OKC_API.G_EXCEPTION_ERROR;
852 END IF;
853
854 END IF;
855
856 IF(p_source_code <> 'new' AND p_source_contract_number IS NOT NULL) THEN
857
858 IF(p_customer_name IS NULL AND p_program_name IS NULL) THEN
859
860 IF (p_source_code = 'template') THEN
861 OPEN l_src_chr_id_crs(p_scs_code,'Y');
862 FETCH l_src_chr_id_crs INTO p_source_chr_id;
863 CLOSE l_src_chr_id_crs;
864 ELSE
865 OPEN l_src_chr_id_crs(p_scs_code,'N');
866 FETCH l_src_chr_id_crs INTO p_source_chr_id;
867 CLOSE l_src_chr_id_crs;
868 END IF;
869
870 ELSIF( p_customer_name IS NOT NULL AND p_program_name IS NULL) THEN
871
872 IF (p_source_code = 'template') THEN
873 OPEN l_source_chr_id_crs(p_scs_code,'Y',p_customer_id1,p_customer_id2,l_object_code);
874 FETCH l_source_chr_id_crs INTO p_source_chr_id;
875 CLOSE l_source_chr_id_crs;
876 ELSE
877 OPEN l_source_chr_id_crs(p_scs_code,'N',p_customer_id1,p_customer_id2,l_object_code);
878 FETCH l_source_chr_id_crs INTO p_source_chr_id;
879 CLOSE l_source_chr_id_crs;
880 END IF;
881
882 ELSIF( p_customer_name IS NOT NULL AND p_program_name IS NOT NULL) THEN
883
884 IF (p_source_code = 'template') THEN
885 OPEN l_source_chr_prog_id_crs(p_scs_code,'Y',p_customer_id1,p_customer_id2,l_object_code, l_program_id);
886 FETCH l_source_chr_prog_id_crs INTO p_source_chr_id;
887 CLOSE l_source_chr_prog_id_crs;
888 ELSE
889 OPEN l_source_chr_prog_id_crs(p_scs_code,'N',p_customer_id1,p_customer_id2,l_object_code, l_program_id);
890 FETCH l_source_chr_prog_id_crs INTO p_source_chr_id;
891 CLOSE l_source_chr_prog_id_crs;
892 END IF;
893
894 ELSIF( p_customer_name IS NULL AND p_program_name IS NOT NULL) THEN
895
896 IF (p_source_code = 'template') THEN
897 OPEN l_source_prog_crs(p_scs_code,'Y',l_program_id);
898 FETCH l_source_prog_crs INTO p_source_chr_id;
899 CLOSE l_source_prog_crs;
900 ELSE
901 OPEN l_source_prog_crs(p_scs_code,'N',l_program_id);
902 FETCH l_source_prog_crs INTO p_source_chr_id;
903 CLOSE l_source_prog_crs;
904 END IF;
905
906 END IF;
907
908 IF(p_source_chr_id IS NULL) THEN
909 x_return_status := OKC_API.g_ret_sts_error;
910 l_ak_prompt := GET_AK_PROMPT('OKL_LA_DEAL_CREAT', 'OKL_CONTRACT_SOURCE');
911 OKC_API.SET_MESSAGE( p_app_name => g_app_name
912 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
913 , p_token1 => 'COL_NAME'
914 , p_token1_value => l_ak_prompt
915 );
916 RAISE OKC_API.G_EXCEPTION_ERROR;
917 END IF;
918
919 END IF;
920
921 IF (p_source_code = 'new') THEN
922
923 create_new_deal(
924 p_api_version => l_api_version,
925 p_init_msg_list => p_init_msg_list,
926 x_return_status => x_return_status,
927 x_msg_count => x_msg_count,
928 x_msg_data => x_msg_data,
929 p_contract_number => p_contract_number,
930 p_scs_code => p_scs_code,
931 p_customer_id1 => p_customer_id1,
932 p_customer_id2 => p_customer_id2,
933 p_customer_code => l_object_code,
934 p_customer_name => p_customer_name,
935 p_template_yn => l_template_yn,
936 p_template_type => p_template_type,
937 p_effective_from => p_effective_from,
938 p_program_name => p_program_name,
939 p_program_id => p_program_id,
940 x_chr_id => x_chr_id,
941 p_legal_entity_id => p_legal_entity_id);
942
943 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
944 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
945 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
946 RAISE OKC_API.G_EXCEPTION_ERROR;
947 END IF;
948
949 l_chr_id := x_chr_id;
950
951 IF okl_context.get_okc_org_id IS NULL THEN
952 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
953 END IF;
954
955
956 IF ( p_scs_code = 'LEASE') THEN
957
958 OKL_LA_PROPERTY_TAX_PVT.create_est_prop_tax_rules(
959 p_api_version => l_api_version,
960 p_init_msg_list => p_init_msg_list,
961 x_return_status => x_return_status,
962 x_msg_count => x_msg_count,
963 x_msg_data => x_msg_data,
964 p_chr_id => l_chr_id);
965
966 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
967 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
968 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
969 RAISE OKC_API.G_EXCEPTION_ERROR;
970 END IF;
971
972 END IF;
973
974 -- copy from template
975 ELSIF (p_source_code = 'template') THEN
976
977 create_from_template(
978 p_api_version => l_api_version,
979 p_init_msg_list => p_init_msg_list,
980 x_return_status => x_return_status,
981 x_msg_count => x_msg_count,
982 x_msg_data => x_msg_data,
983 p_contract_number => p_contract_number,
984 p_source_chr_id => p_source_chr_id,
985 x_chr_id => x_chr_id);
986
987 ELSIF (p_source_code = 'copy' ) THEN
988
989 create_from_contract(
990 p_api_version => l_api_version,
991 p_init_msg_list => p_init_msg_list,
992 x_return_status => x_return_status,
993 x_msg_count => x_msg_count,
994 x_msg_data => x_msg_data,
995 p_contract_number => p_contract_number,
996 p_source_chr_id => p_source_chr_id,
997 x_chr_id => x_chr_id);
998
999 END IF;
1000
1001 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1002 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1003 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1004 RAISE OKC_API.G_EXCEPTION_ERROR;
1005 END IF;
1006
1007 -- update contract header for template_yn
1008 IF ( p_template_type IS NOT NULL AND ( p_source_code = 'copy' OR p_source_code = 'template')) THEN
1009
1010 lp_chrv_rec.id := x_chr_id;
1011 lp_khrv_rec.id := x_chr_id;
1012
1013 IF(p_template_type = OKL_TEMP_TYPE_PROGRAM) THEN
1014 lp_khrv_rec.template_type_code := OKL_TEMP_TYPE_PROGRAM;
1015 lp_chrv_rec.template_yn := 'Y';
1016 ELSIF(p_template_type = OKL_TEMP_TYPE_CONTRACT) THEN
1017 lp_khrv_rec.template_type_code := OKL_TEMP_TYPE_CONTRACT;
1018 lp_chrv_rec.template_yn := 'Y';
1019 ELSIF(p_template_type = OKL_TEMP_TYPE_LEASEAPP) THEN
1020 lp_khrv_rec.template_type_code := OKL_TEMP_TYPE_LEASEAPP;
1021 lp_chrv_rec.template_yn := 'Y';
1022 ELSE
1023 lp_khrv_rec.template_type_code := NULL;
1024 END IF;
1025
1026 IF(p_effective_from IS NOT NULL) THEN
1027
1028 lp_chrv_rec.start_date := p_effective_from;
1029
1030 END IF;
1031
1032 IF(l_program_id IS NOT NULL) THEN
1033
1034 lp_khrv_rec.khr_id := l_program_id;
1035
1036 END IF;
1037
1038 OKL_CONTRACT_PUB.update_contract_header(
1039 p_api_version => p_api_version,
1040 p_init_msg_list => p_init_msg_list,
1041 x_return_status => x_return_status,
1042 x_msg_count => x_msg_count,
1043 x_msg_data => x_msg_data,
1044 p_chrv_rec => lp_chrv_rec,
1045 p_khrv_rec => lp_khrv_rec,
1046 x_chrv_rec => lx_chrv_rec,
1047 x_khrv_rec => lx_khrv_rec);
1048
1049 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1050 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1051 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1052 RAISE OKC_API.G_EXCEPTION_ERROR;
1053 END IF;
1054
1055 ELSIF ( p_template_type IS NULL AND ( p_source_code = 'copy' OR p_source_code = 'template')) THEN
1056
1057 lp_chrv_rec.id := x_chr_id;
1058 lp_khrv_rec.id := x_chr_id;
1059 lp_chrv_rec.template_yn := 'N';
1060 lp_khrv_rec.template_type_code := NULL;
1061
1062 IF(p_effective_from IS NOT NULL) THEN
1063
1064 lp_chrv_rec.start_date := p_effective_from;
1065
1066 END IF;
1067
1068 IF(l_program_id IS NOT NULL) THEN
1069
1070 lp_khrv_rec.khr_id := l_program_id;
1071
1072 END IF;
1073
1074 OKL_CONTRACT_PUB.update_contract_header(
1075 p_api_version => p_api_version,
1076 p_init_msg_list => p_init_msg_list,
1077 x_return_status => x_return_status,
1078 x_msg_count => x_msg_count,
1079 x_msg_data => x_msg_data,
1080 p_chrv_rec => lp_chrv_rec,
1081 p_khrv_rec => lp_khrv_rec,
1082 x_chrv_rec => lx_chrv_rec,
1083 x_khrv_rec => lx_khrv_rec);
1084
1085 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1086 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1087 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1088 RAISE OKC_API.G_EXCEPTION_ERROR;
1089 END IF;
1090
1091 END IF;
1092
1093 raise_business_event(p_chr_id => x_chr_id
1094 ,x_return_status => x_return_status);
1095 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1096 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1097 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1098 RAISE OKL_API.G_EXCEPTION_ERROR;
1099 END IF;
1100
1101 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
1102 x_msg_data => x_msg_data);
1103 EXCEPTION
1104 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1105 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1106 p_api_name => l_api_name,
1107 p_pkg_name => g_pkg_name,
1108 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1109 x_msg_count => x_msg_count,
1110 x_msg_data => x_msg_data,
1111 p_api_type => g_api_type);
1112
1113 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1114 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1115 p_api_name => l_api_name,
1116 p_pkg_name => g_pkg_name,
1117 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1118 x_msg_count => x_msg_count,
1119 x_msg_data => x_msg_data,
1120 p_api_type => g_api_type);
1121
1122 WHEN OTHERS THEN
1123 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1124 p_api_name => l_api_name,
1125 p_pkg_name => g_pkg_name,
1126 p_exc_name => 'OTHERS',
1127 x_msg_count => x_msg_count,
1128 x_msg_data => x_msg_data,
1129 p_api_type => g_api_type);
1130 END;
1131
1132
1133 PROCEDURE update_deal(
1134 p_api_version IN NUMBER,
1135 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1136 x_return_status OUT NOCOPY VARCHAR2,
1137 x_msg_count OUT NOCOPY NUMBER,
1138 x_msg_data OUT NOCOPY VARCHAR2,
1139 p_durv_rec IN upd_deal_rec_type,
1140 x_durv_rec OUT NOCOPY upd_deal_rec_type
1141 ) AS
1142
1143 l_api_name VARCHAR2(30) := 'update_deal';
1144 l_api_version CONSTANT NUMBER := 1.0;
1145
1146 l_template_yn OKC_K_HEADERS_B.TEMPLATE_YN%TYPE;
1147 l_chr_id NUMBER;
1148 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
1149 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
1150
1151 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
1152 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
1153
1154 PROCEDURE raise_business_event(
1155 p_chr_id IN NUMBER
1156 ,x_return_status OUT NOCOPY VARCHAR2
1157 )
1158 IS
1159 l_check VARCHAR2(1);
1160 l_parameter_list wf_parameter_list_t;
1161 BEGIN
1162 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1163 -- Raise the event if it is a new Contract
1164 l_check := Okl_Lla_Util_Pvt.check_new_contract(p_chr_id);
1165 IF (l_check= OKL_API.G_TRUE) THEN
1166 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,p_chr_id,l_parameter_list);
1167
1168 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
1169 p_init_msg_list => p_init_msg_list,
1170 x_return_status => x_return_status,
1171 x_msg_count => x_msg_count,
1172 x_msg_data => x_msg_data,
1173 p_event_name => G_WF_EVT_KHR_UPDATED,
1174 p_parameters => l_parameter_list);
1175
1176 END IF;
1177
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1181 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1182 END raise_business_event;
1183
1184 BEGIN
1185 IF okl_context.get_okc_org_id IS NULL THEN
1186 l_chr_id := p_durv_rec.chr_id;
1187 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1188 END IF;
1189
1190 x_return_status := OKC_API.START_ACTIVITY(
1191 p_api_name => l_api_name,
1192 p_pkg_name => g_pkg_name,
1193 p_init_msg_list => p_init_msg_list,
1194 l_api_version => l_api_version,
1195 p_api_version => p_api_version,
1196 p_api_type => g_api_type,
1197 x_return_status => x_return_status);
1198
1199 -- check if activity started successfully
1200 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1201 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1202 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1203 RAISE OKC_API.G_EXCEPTION_ERROR;
1204 END IF;
1205
1206 lp_chrv_rec.id := p_durv_rec.chr_id;
1207 lp_khrv_rec.id := p_durv_rec.chr_id;
1208 lp_chrv_rec.contract_number := p_durv_rec.chr_contract_number;
1209 lp_chrv_rec.description := p_durv_rec.chr_description;
1210 lp_chrv_rec.short_description := p_durv_rec.chr_description;
1211 lp_chrv_rec.start_date := p_durv_rec.chr_start_date;
1212 lp_chrv_rec.end_date := p_durv_rec.chr_end_date;
1213 lp_khrv_rec.CONVERTED_ACCOUNT_YN := p_durv_rec.khr_CONVERTED_ACCOUNT_YN;
1214 lp_chrv_rec.TEMPLATE_YN := p_durv_rec.chr_TEMPLATE_YN;
1215 lp_chrv_rec.DATE_SIGNED := p_durv_rec.chr_DATE_SIGNED;
1216 lp_chrv_rec.currency_code := x_durv_rec.chr_currency_code;
1217 lp_khrv_rec.legal_entity_id :=p_durv_rec.legal_entity_id;
1218
1219 OKL_CONTRACT_PUB.update_contract_header(
1220 p_api_version => p_api_version,
1221 p_init_msg_list => p_init_msg_list,
1222 x_return_status => x_return_status,
1223 x_msg_count => x_msg_count,
1224 x_msg_data => x_msg_data,
1225 p_restricted_update => 'F',
1226 p_chrv_rec => lp_chrv_rec,
1227 p_khrv_rec => lp_khrv_rec,
1228 x_chrv_rec => lx_chrv_rec,
1229 x_khrv_rec => lx_khrv_rec);
1230 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1231 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1232 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1233 RAISE OKC_API.G_EXCEPTION_ERROR;
1234 END IF;
1235 raise_business_event(p_chr_id => p_durv_rec.chr_id
1236 ,x_return_status => x_return_status);
1237 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1238 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1239 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1240 RAISE OKL_API.G_EXCEPTION_ERROR;
1241 END IF;
1242 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1243
1244 EXCEPTION
1245 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1246 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1247 p_api_name => l_api_name,
1248 p_pkg_name => g_pkg_name,
1249 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1250 x_msg_count => x_msg_count,
1251 x_msg_data => x_msg_data,
1252 p_api_type => g_api_type);
1253
1254 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1255 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1256 p_api_name => l_api_name,
1257 p_pkg_name => g_pkg_name,
1258 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1259 x_msg_count => x_msg_count,
1260 x_msg_data => x_msg_data,
1261 p_api_type => g_api_type);
1262
1263 WHEN OTHERS THEN
1264 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1265 p_api_name => l_api_name,
1266 p_pkg_name => g_pkg_name,
1267 p_exc_name => 'OTHERS',
1268 x_msg_count => x_msg_count,
1269 x_msg_data => x_msg_data,
1270 p_api_type => g_api_type);
1271 END;
1272 END Okl_Mla_Create_Update_Pub;