[Home] [Help]
PACKAGE BODY: APPS.OKL_VENDOR_PROGRAM_PVT
Source
1 PACKAGE BODY OKL_VENDOR_PROGRAM_PVT AS
2 /* $Header: OKLRPRMB.pls 120.16 2008/02/15 11:05:10 abhsaxen noship $ */
3
4
5 /*NEW CODE BEGIN MARCH 20*/
6 --SUBTYPE chrv_rec_type IS OKL_OKC_MIGRATION_PVT.chrv_rec_type;
7 --SUBTYPE khrv_rec_type IS OKL_CONTRACT_PUB.khrv_rec_type;
8 SUBTYPE govern_rec_type IS OKC_CONTRACT_PUB.gvev_rec_type;
9 SUBTYPE process_rec_type IS OKC_CONTRACT_PUB.cpsv_rec_type;
10 SUBTYPE gvev_rec_type IS OKC_CONTRACT_PUB.gvev_rec_type;
11
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
13 G_SQLERRM_TOKEN CONSTANT VARCHAR(200) := 'ERROR_MESSAGE';
14
15 G_BUY_OR_SELL VARCHAR2(20) DEFAULT 'S';
16 G_LESSOR_RLE_CODE VARCHAR(100) DEFAULT 'LESSOR';
17 G_VENDOR_RLE_CODE VARCHAR(100) DEFAULT 'OKL_VENDOR';
18
19 G_VENDOR_RES_SHARE_RG CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'VGLRS';
20 G_VENDOR_RES_PECENT_RL CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'VGLRSP';
21
22 CURSOR l_chr_csr2(contract_no VARCHAR2,contract_no_modifier VARCHAR2) IS
23 SELECT 'x'
24 FROM okc_k_headers_b
25 WHERE contract_number = contract_no
26 AND contract_number_modifier = contract_no_modifier;
27
28 --Murthy Added Cursors for JTOT OBJECT CODE and check for CONTRACT ID
29 CURSOR cur_jtot_object_code(p_rle_code IN VARCHAR2, p_scs_code IN VARCHAR2, p_buy_or_sell IN VARCHAR2) IS
30 SELECT jtot_object_code
31 FROM okc_role_sources rs, okc_subclass_roles sr
32 WHERE rs.rle_code = sr.rle_code AND SYSDATE BETWEEN rs.start_date AND NVL(rs.end_date,SYSDATE)
33 AND SYSDATE BETWEEN sr.start_date AND NVL(sr.end_date,SYSDATE)
34 AND rs.rle_code = p_rle_code
35 AND rs.buy_or_sell = p_buy_or_sell AND sr.scs_code = p_scs_code;
36
37 --Murthy
38 CURSOR cur_k_party_roles(contract_id NUMBER) IS
39 SELECT id FROM okc_k_party_roles_b
40 WHERE chr_id = contract_id AND rle_code = g_vendor_rle_code;
41
42 CURSOR cur_parent_object_id(contract_id NUMBER) IS
43 SELECT object1_id1,object1_id2 FROM okc_k_party_roles_b
44 WHERE chr_id = contract_id AND rle_code = g_vendor_rle_code;
45
46 CURSOR cur_k_header(parent_agreement_number VARCHAR2) IS
47 SELECT v.id
48 FROM okc_k_headers_v v
49 WHERE v.contract_number = parent_agreement_number;
50
51 /*---------------------------------------------------------------------------+
52 | |
53 | PROCEDURE: CREATE_HEADER |
54 | DESC : Contract Header Record Creation |
55 | HISTORY: 20 March 2002 Created by Murthy |
56 *-------------------------------------------------------------------------- */
57 PROCEDURE create_header(
58 p_api_version IN NUMBER,
59 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2,
63 p_hdr_rec IN program_header_rec_type,
64 p_parent_agreement_number IN okl_k_headers_full_v.contract_number%TYPE DEFAULT NULL,
65 p_chrv_rec IN chrv_rec_type,
66 p_khrv_rec IN khrv_rec_type,
67 x_chrv_rec OUT NOCOPY chrv_rec_type,
68 x_khrv_rec OUT NOCOPY khrv_rec_type) IS
69
70 l_header_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
71 x_header_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
72 l_k_header_rec OKL_CONTRACT_PUB.khrv_rec_type;
73 x_k_header_rec OKL_CONTRACT_PUB.khrv_rec_type;
74 -- sjalasut, added local pl/sql table for vendor residual share enhancement
75 lv_rgr_tbl OKL_RGRP_RULES_PROCESS_PVT.rgr_tbl_type;
76
77 cpsv_rec_type1 process_rec_type;
78 cpsv_rec_type2 process_rec_type;
79 gvev_rec_type1 govern_rec_type;
80 gvev_rec_type2 govern_rec_type;
81
82 --Murthy
83 l_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
84 x_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
85 l_jtot_object_code VARCHAR(200);
86 l_object1_id1 VARCHAR(200);
87 l_object1_id2 VARCHAR(200);
88 l_agreement_id NUMBER;
89
90 l_copy_rec okl_vp_copy_contract_pub.copy_header_rec_type;
91 l_update_rec OKL_VENDOR_PROGRAM_PUB.program_header_rec_type;
92
93 l_contract_id NUMBER;
94 l_parent_id NUMBER;
95
96 l_new_contract_id NUMBER;
97 x_new_contract_id NUMBER;
98
99 l_msg_count NUMBER;
100 l_msg_data VARCHAR2(2000);
101 l1_return_status VARCHAR2(3);
102 l2_return_status VARCHAR2(3);
103 l3_return_status VARCHAR2(3);
104
105 l_api_version NUMBER := 1.0;
106
107 l_api_name CONSTANT VARCHAR2(30) := 'create_header';
108
109 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
110
111 l_dummy VARCHAR2(1);
112 l_found BOOLEAN;
113
114 /* --Murthy Added Cursors for JTOT OBJECT CODE and check for CONTRACT ID
115 CURSOR cur_jtot_object_code(p_rle_code IN VARCHAR2, p_scs_code IN VARCHAR2, p_buy_or_sell IN VARCHAR2) IS
116 SELECT jtot_object_code
117 FROM okc_role_sources rs, okc_subclass_roles sr
118 WHERE rs.rle_code = sr.rle_code AND SYSDATE BETWEEN rs.start_date AND NVL(rs.end_date,SYSDATE)
119 AND SYSDATE BETWEEN sr.start_date AND NVL(sr.end_date,SYSDATE)
120 AND rs.rle_code = p_rle_code
121 AND rs.buy_or_sell = p_buy_or_sell AND sr.scs_code = p_scs_code;
122 */
123 CURSOR cur_object_id(khr_id NUMBER) IS
124 SELECT jtot_object1_code, object1_id1, object1_id2
125 FROM okc_k_party_roles_b
126 WHERE dnz_chr_id = khr_id
127 AND rle_code = g_vendor_rle_code;
128
129 --TYPE cur_contract_id_type IS REF CURSOR;
130 --cur_contract_id cur_contract_id_type;
131
132 -- begin of block
133 -- chr_type is hard coded.
134 -- to be removed as and when things are clarified.
135
136 CURSOR role_csr(p_rle_code VARCHAR2) IS
137 Select access_level
138 from OKC_ROLE_SOURCES
139 where rle_code = p_rle_code
140 and buy_or_sell = 'S';
141
142 --Manu : 14-Jun-2005
143 -- Cursor to get the QA Process ID for Operating/Program Agreement
144 CURSOR qa_csr(p_qa_name VARCHAR2) IS
145 SELECT id FROM OKC_QA_CHECK_LISTS_V WHERE name = p_qa_name;
146
147 l_qa_id OKC_QA_CHECK_LISTS_V .id%TYPE := NULL;
148
149 l_access_level OKC_ROLE_SOURCES.access_level%TYPE;
150
151 BEGIN
152
153 /* l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
154 ,p_init_msg_list => p_init_msg_list
155 ,p_api_type => '_PVT'
156 ,x_return_status => x_return_status
157 );
158 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
159 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
160 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
161 RAISE OKL_API.G_EXCEPTION_ERROR;
162 END IF;
163 */
164 -- setting the authoring org id and inv organization id
165
166 OKL_CONTEXT.SET_OKC_ORG_CONTEXT;
167 l_header_rec.inv_organization_id := OKC_CONTEXT.Get_OKC_Organization_Id;
168 l_header_rec.authoring_org_id := OKC_CONTEXT.get_okc_org_id;
169
170 l_header_rec.sts_code := 'NEW';
171 l_header_rec.qcl_id := p_hdr_rec.p_qcl_id;
172 l_header_rec.scs_code := p_hdr_rec.p_contract_category;
173 l_header_rec.contract_number := p_hdr_rec.p_agreement_number;
174 l_header_rec.archived_yn := 'N';
175 l_header_rec.deleted_yn := 'N';
176 l_header_rec.template_yn := p_hdr_rec.p_template_yn;
177 -- Fix Bug 3388759 --
178 -- l_header_rec.currency_code := 'USD';
179 l_header_rec.currency_code := okl_accounting_util.get_func_curr_code();
180 l_header_rec.chr_type := 'SELL';
181 l_header_rec.CONTRACT_NUMBER_MODIFIER := '1.0';
182
183 l_header_rec.short_description := p_hdr_rec.p_short_description;
184 l_header_rec.description := p_hdr_rec.p_description;
185 l_header_rec.comments := p_hdr_rec.p_comments;
186
187 l_header_rec.start_date := p_hdr_rec.p_start_date;
188 l_header_rec.end_date := p_hdr_rec.p_end_date;
189
190 -- abindal start --
191 l_k_header_rec.attribute_category := p_hdr_rec.p_attribute_category;
192 l_k_header_rec.attribute1 := p_hdr_rec.p_attribute1;
193 l_k_header_rec.attribute2 := p_hdr_rec.p_attribute2;
194 l_k_header_rec.attribute3 := p_hdr_rec.p_attribute3;
195 l_k_header_rec.attribute4 := p_hdr_rec.p_attribute4;
196 l_k_header_rec.attribute5 := p_hdr_rec.p_attribute5;
197 l_k_header_rec.attribute6 := p_hdr_rec.p_attribute6;
198 l_k_header_rec.attribute7 := p_hdr_rec.p_attribute7;
199 l_k_header_rec.attribute8 := p_hdr_rec.p_attribute8;
200 l_k_header_rec.attribute9 := p_hdr_rec.p_attribute9;
201 l_k_header_rec.attribute10 := p_hdr_rec.p_attribute10;
202 l_k_header_rec.attribute11 := p_hdr_rec.p_attribute11;
203 l_k_header_rec.attribute12 := p_hdr_rec.p_attribute12;
204 l_k_header_rec.attribute13 := p_hdr_rec.p_attribute13;
205 l_k_header_rec.attribute14 := p_hdr_rec.p_attribute14;
206 l_k_header_rec.attribute15 := p_hdr_rec.p_attribute15;
207 /* sosharma ,31 oct 2006
208 Build:R12
209 Assigning Legal entity value to l_k_header_rec
210 Start Changes*/
211 l_k_header_rec.legal_entity_id := p_hdr_rec.p_legal_entity_id;
212 /* End Changes*/
213 -- abindal end --
214
215
216 --Murthy
217 l_header_rec.buy_or_sell := g_buy_or_sell;
218
219 --fmiao added issue_or_receive defaulting qcl_id (qa checker)--
220 l_header_rec.issue_or_receive := 'I';
221
222 -- Manu : 14-Jun-2005
223 -- Default the QA Check process for Program/Operating Agreement.
224 IF (l_header_rec.scs_code = 'OPERATING') THEN
225 OPEN qa_csr(p_qa_name => 'OKL OA QA CHECK LIST');
226 FETCH qa_csr INTO l_qa_id;
227 CLOSE qa_csr;
228 l_header_rec.qcl_id := l_qa_id;
229 ELSIF (l_header_rec.scs_code = 'PROGRAM') THEN
230 OPEN qa_csr(p_qa_name => 'OKL PA QA CHECK LIST');
231 FETCH qa_csr INTO l_qa_id;
232 CLOSE qa_csr;
233 l_header_rec.qcl_id := l_qa_id;
234 END IF;
235
236 IF (l_header_rec.qcl_id IS NULL) THEN
237 l_header_rec.qcl_id := 1;
238 END IF;
239
240 -- setting for okl_contract_pub for creation of referred program template
241 --fmiao--l_k_header_rec.khr_id := p_hdr_rec.p_referred_id;
242
243 -- call the create_contract_header
244 OKL_CONTRACT_PUB.create_contract_header(
245 p_api_version => l_api_version,
246 x_return_status => l_return_status,
247 p_init_msg_list => OKL_API.G_TRUE,
248 x_msg_count => l_msg_count,
249 x_msg_data => l_msg_data,
250 p_chrv_rec => l_header_rec,
251 p_khrv_rec => l_k_header_rec,
252 x_chrv_rec => x_header_rec,
253 x_khrv_rec => x_k_header_rec);
254 x_chrv_rec := x_header_rec;
255 x_khrv_rec := x_k_header_rec;
256 -- if the insert in the okc_k_headers_v is successful,then insert a record into okc_k_processes_v
257
258 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
259
260 IF (((p_hdr_rec.p_workflow_process) <> OKL_API.G_MISS_NUM) AND ((p_hdr_rec.p_workflow_process) IS NOT NULL))THEN
261
262 l_contract_id :=x_header_rec.id;
263 cpsv_rec_type1.chr_id :=l_contract_id;
264 cpsv_rec_type1.pdf_id :=p_hdr_rec.p_workflow_process;
265
266 okc_contract_pub.create_contract_process(
267 p_api_version => l_api_version,
268 p_init_msg_list => OKL_API.G_FALSE,
269 x_return_status => l_return_status,
270 x_msg_count => l_msg_count,
271 x_msg_data => l_msg_data,
272 p_cpsv_rec => cpsv_rec_type1,
273 x_cpsv_rec => cpsv_rec_type2);
274
275 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
276 NULL;
277 ELSE
278 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
279 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
280 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
281 RAISE OKL_API.G_EXCEPTION_ERROR;
282 END IF;
283 END IF;
284 END IF;
285
286 ELSE
287 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
288 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
289 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
290 RAISE OKL_API.G_EXCEPTION_ERROR;
291 END IF;
292
293 END IF;
294
295 -- Begin Creating Lessor
296 OPEN cur_jtot_object_code(g_lessor_rle_code,p_hdr_rec.p_contract_category,g_buy_or_sell);
297 FETCH cur_jtot_object_code INTO l_jtot_object_code;
298 IF(cur_jtot_object_code%found) THEN
299 NULL;
300 CLOSE cur_jtot_object_code;
301 ELSE
302 CLOSE cur_jtot_object_code;
303 -- Murthy Set message to be registered
304 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_JTOT_CODE_NOT_FOUND');
305 RAISE OKL_API.G_EXCEPTION_ERROR;
306 END IF;
307
308 l_agreement_id := x_header_rec.id;
309 l_cplv_rec.chr_id := l_agreement_id;
310 l_cplv_rec.dnz_chr_id := l_agreement_id;
311 l_cplv_rec.jtot_object1_code := l_jtot_object_code;
312 l_cplv_rec.rle_code := g_lessor_rle_code;
313 l_cplv_rec.object1_id1 := OKC_CONTEXT.get_okc_org_id;
314 l_cplv_rec.object1_id2 := '#';
315 l_cplv_rec.cpl_id:= NULL;
316 l_cplv_rec.cle_id:= NULL;
317
318 ----- Changes by Kanti
319 ----- Validate the JTOT Object code, ID1 and ID2
320
321 OPEN role_csr(l_cplv_rec.rle_code);
322 FETCH role_csr INTO l_access_level;
323 CLOSE role_csr;
324
325 IF (l_access_level = 'S') THEN
326
327 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
328 p_init_msg_list => OKC_API.G_FALSE,
329 x_return_status => x_return_status,
330 x_msg_count => x_msg_count,
331 x_msg_data => x_msg_data,
332 p_object_name => l_cplv_rec.jtot_object1_code,
333 p_id1 => l_cplv_rec.object1_id1,
334 p_id2 => l_cplv_rec.object1_id2);
335
336 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
337 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
338 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
339 RAISE OKL_API.G_EXCEPTION_ERROR;
340 END IF;
341
342 END IF;
343
344 ---- Changes End
345
346
347 OKC_CONTRACT_PARTY_PUB.create_k_party_role(p_api_version => p_api_version,
348 p_init_msg_list => OKL_API.G_FALSE,
349 x_return_status => l_return_status,
350 x_msg_count => x_msg_count,
351 x_msg_data => x_msg_data,
352 p_cplv_rec => l_cplv_rec,
353 x_cplv_rec => x_cplv_rec
354 );
355
356 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
357 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
358 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
359 RAISE OKL_API.G_EXCEPTION_ERROR;
360 END IF;
361 -- End Creating Lessor
362
363 -- sjalasut, added creation of a rule group and rule for Lessor Residual Share percent. START
364 -- first determine the Party Role Id for this Rule Group
365 -- Manu 19-Oct-2005 Restricting the Residual Share to PROGRAM.
366 IF(x_cplv_rec.id IS NOT NULL AND p_hdr_rec.p_contract_category = 'PROGRAM')THEN
367 lv_rgr_tbl(1).rgd_code := G_VENDOR_RES_SHARE_RG;
368 lv_rgr_tbl(1).dnz_chr_id := x_header_rec.id;
369 lv_rgr_tbl(1).rule_information_category := G_VENDOR_RES_PECENT_RL;
370 lv_rgr_tbl(1).rule_information1 := x_cplv_rec.id;
371 -- this is to indicate that the default share of 100% goes to the Lessor
372 lv_rgr_tbl(1).rule_information2 := 100;
373 lv_rgr_tbl(1).std_template_yn := 'N';
374 lv_rgr_tbl(1).warn_yn := 'N';
375
376 /*
377 lv_rgr_tbl(2).rgd_code := G_VENDOR_RES_SHARE_RG;
378 lv_rgr_tbl(2).dnz_chr_id := x_header_rec.id;
379 lv_rgr_tbl(2).rule_information_category := 'VGLRSF';
380 lv_rgr_tbl(2).rule_information1 := 'VENDOR_RESIDUAL_SHARE';
381 lv_rgr_tbl(2).std_template_yn := 'N';
382 lv_rgr_tbl(2).warn_yn := 'N';
383 */
384
385 okl_rgrp_rules_process_pub.process_rule_group_rules(p_api_version => p_api_version
386 ,p_init_msg_list => OKL_API.G_FALSE
387 ,x_return_status => x_return_status
388 ,x_msg_count => x_msg_count
389 ,x_msg_data => x_msg_data
390 ,p_chr_id => x_header_rec.id
391 ,p_line_id => NULL
392 ,p_cpl_id => NULL
393 ,p_rrd_id => NULL
394 ,p_rgr_tbl => lv_rgr_tbl
395 );
396 IF(x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
397 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
398 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
399 RAISE OKC_API.G_EXCEPTION_ERROR;
400 END IF;
401 END IF;
402
403 -- sjalasut, added creation of a rule group and rule for Lessor Residual Share percent. END
404
405 /* OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
406 ,x_msg_data => x_msg_data
407 );
408 */
409 EXCEPTION
410
411 WHEN OKL_API.G_EXCEPTION_ERROR THEN
412 x_return_status := OKL_API.G_RET_STS_ERROR;
413 /*x_return_status := OKL_API.HANDLE_EXCEPTIONS
414 (p_api_name => l_api_name
415 ,p_pkg_name => G_PKG_NAME
416 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
417 ,x_msg_count => x_msg_count
418 ,x_msg_data => x_msg_data
419 ,p_api_type => '_PVT'
420 );*/
421
422 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
423 x_return_status := OKL_API.G_RET_STS_ERROR;
424 /* x_return_status := OKL_API.HANDLE_EXCEPTIONS(
425 l_api_name
426 ,g_pkg_name
427 ,'OKL_API.G_RET_STS_ERROR'
428 ,x_msg_count
429 ,x_msg_data
430 ,'_PVT'
431 );*/
432
433 WHEN OTHERS THEN
434 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
435 /* x_return_status := OKL_API.HANDLE_EXCEPTIONS(
436 l_api_name
437 ,g_pkg_name
438 ,'OTHERS'
439 ,x_msg_count
440 ,x_msg_data
441 ,'_PVT'
442 );*/
443 END;
444
445 /*---------------------------------------------------------------------------+
446 | |
447 | FUNCTION : VALIDATE_ATTRIBUTES |
448 | DESC : Validation of Attributes |
449 | HISTORY: 20 March 2002 Created by Murthy |
450 *-------------------------------------------------------------------------- */
451 FUNCTION validate_attributes(p_hdr_rec program_header_rec_type,
452 p_parent_agreement_number IN okl_k_headers_full_v.contract_number%TYPE DEFAULT NULL) RETURN VARCHAR2 IS
453 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
454 l_dummy VARCHAR2(1);
455 l_found BOOLEAN;
456 BEGIN
457
458 IF ((p_hdr_rec.p_agreement_number = OKL_API.G_MISS_CHAR) OR (p_hdr_rec.p_agreement_number IS NULL)) THEN
459 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_AGREEMENT_NO_REQUIRED');
460 l_return_status :=okl_api.g_ret_sts_error;
461 return OKL_API.G_RET_STS_ERROR;
462 END IF;
463
464 -- check whether the contract already exists before calling the api.
465
466 OPEN l_chr_csr2(p_hdr_rec.p_agreement_number,'1.0');
467 FETCH l_chr_csr2 into l_dummy;
468 l_found := l_chr_csr2%FOUND;
469 CLOSE l_chr_csr2;
470
471 IF (l_found) THEN
472 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
473 p_msg_name => 'OKL_VP_CONTRACT_EXISTS',
474 p_token1 => 'NUMBER',
475 p_token1_value => p_hdr_rec.p_agreement_number
476 );
477 return OKL_API.G_RET_STS_ERROR;
478 END IF;
479
480 IF ((p_hdr_rec.p_contract_category = OKL_API.G_MISS_CHAR) OR (p_hdr_rec.p_contract_category IS NULL)) THEN
481 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_CATEGORY_REQUIRED');
482 l_return_status :=okl_api.g_ret_sts_error;
483 return OKL_API.G_RET_STS_ERROR;
484 END IF;
485
486
487
488 -- Category is OPERATING
489 IF (p_hdr_rec.p_contract_category = 'OPERATING') THEN
490
491 IF (p_hdr_rec.p_template_yn = 'Y') THEN
492 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_OP_AGMT_NOT_SET_TMPL');
493 return OKL_API.G_RET_STS_ERROR;
494 END IF;
495
496 IF(p_parent_agreement_number IS NOT NULL AND p_parent_agreement_number <> OKL_API.G_MISS_CHAR) THEN
497 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_OP_AGMT_NOT_HAVE_PRNT');
498 return OKL_API.G_RET_STS_ERROR;
499 END IF;
500 /*--fmiao--
501 IF (p_hdr_rec.p_referred_id IS NOT NULL AND p_hdr_rec.p_referred_id <> OKL_API.G_MISS_NUM ) THEN
502 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_OP_AGMT_NOT_BE_REFRD');
503 return OKL_API.G_RET_STS_ERROR;
504 END IF; --fmiao*/
505 /* IF (p_hdr_rec.p_object1_id1 <> OKL_API.G_MISS_CHAR AND p_hdr_rec.p_object1_id1 IS NOT NULL AND
506 p_hdr_rec.p_object1_id2 <> OKL_API.G_MISS_CHAR AND p_hdr_rec.p_object1_id2 IS NOT NULL) OR
507 ( p_hdr_rec.p_template_yn <> 'N' AND p_hdr_rec.p_template_yn <> OKL_API.G_MISS_CHAR ) THEN
508 NULL;
509 ELSE
510 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_VENDOR_REQUIRED');
511 return OKL_API.G_RET_STS_ERROR;
512 END IF;
513 */
514 IF ((p_hdr_rec.p_object1_id1 = OKL_API.G_MISS_CHAR) OR (p_hdr_rec.p_object1_id1 IS NULL) OR
515 (p_hdr_rec.p_object1_id2 = OKL_API.G_MISS_CHAR) OR (p_hdr_rec.p_object1_id2 IS NULL)) AND
516 (p_hdr_rec.p_template_yn = 'N' OR p_hdr_rec.p_template_yn = OKL_API.G_MISS_CHAR ) THEN
517 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_VENDOR_REQUIRED');
518 return OKL_API.G_RET_STS_ERROR;
519 END IF;
520 -- Category is PROGRAM
521 ELSIF (p_hdr_rec.p_contract_category = 'PROGRAM') THEN
522
523 -- IF(p_hdr_rec.p_template_yn = 'N') THEN
524 /*--fmiao--
525 IF (p_parent_agreement_number IS NOT NULL AND p_parent_agreement_number <> OKL_API.G_MISS_CHAR AND
526 p_hdr_rec.p_referred_id IS NOT NULL AND p_hdr_rec.p_referred_id <> OKL_API.G_MISS_NUM ) THEN
527 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_PRG_NOT_HAVE_BOTH_OP_PRG');
528 return OKL_API.G_RET_STS_ERROR;
529 ELSIF ( (p_parent_agreement_number IS NULL OR p_parent_agreement_number = OKL_API.G_MISS_CHAR) AND
530 (p_hdr_rec.p_referred_id IS NULL OR p_hdr_rec.p_referred_id = OKL_API.G_MISS_NUM) ) THEN
531 IF(p_hdr_rec.p_template_yn = 'Y') THEN
532 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_EITHER_PRNT_OR_REF');
533 return OKL_API.G_RET_STS_ERROR;
534 END IF;
535 END IF; --fmiao--*/
536 IF ((p_hdr_rec.p_object1_id1 = OKL_API.G_MISS_CHAR) OR (p_hdr_rec.p_object1_id1 IS NULL) OR
537 (p_hdr_rec.p_object1_id2 = OKL_API.G_MISS_CHAR) OR (p_hdr_rec.p_object1_id2 IS NULL)) AND
538 (p_hdr_rec.p_template_yn = 'N' OR p_hdr_rec.p_template_yn = OKL_API.G_MISS_CHAR ) THEN
539 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_VENDOR_REQUIRED');
540 return OKL_API.G_RET_STS_ERROR;
541 END IF;
542 END IF;
543
544 -- Start and End Date Validations
545 IF ((p_hdr_rec.p_start_date = OKL_API.G_MISS_DATE) OR (p_hdr_rec.p_start_date IS NULL)) THEN
546 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_START_DATE_REQUIRED');
547 l_return_status :=okl_api.g_ret_sts_error;
548 return OKL_API.G_RET_STS_ERROR;
549 END IF;
550
551 IF ((p_hdr_rec.p_end_date = OKL_API.G_MISS_DATE) OR (p_hdr_rec.p_end_date IS NULL)) THEN
552 NULL;
553 ELSIF (p_hdr_rec.p_end_date < p_hdr_rec.p_start_date)THEN
554 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
555 p_msg_name => 'OKL_INVALID_TO_DATE');
556 return OKL_API.G_RET_STS_ERROR;
557 -- sjalasut, added trunc on both sides
558 ELSIF (TRUNC(p_hdr_rec.p_end_date) < TRUNC(SYSDATE)) THEN
559 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
560 p_msg_name => 'OKL_INVALID_EFF_TO_DATE');
561 return OKL_API.G_RET_STS_ERROR;
562 END IF;
563
564 --bug#2460595
565 -- QA Check List Validation
566 /* --fmiao qa chechlist not needed for the new oa page --
567 IF ((p_hdr_rec.p_qcl_id = OKL_API.G_MISS_NUM) OR (p_hdr_rec.p_qcl_id IS NULL)) THEN
568 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_QACHECK_REQUIRED');
569 l_return_status :=okl_api.g_ret_sts_error;
570 return OKL_API.G_RET_STS_ERROR;
571 END IF;
572 */
573 return l_return_status;
574 EXCEPTION
575 WHEN OTHERS THEN
576 /* OKL_API.SET_MESSAGE(p_app_name => g_app_name,
577 p_msg_name => g_unexpected_error,
578 p_token1 => g_sqlcode_token,
579 p_token1_value => sqlcode,
580 p_token2 => g_sqlerrm_token,
581 p_token2_value => sqlerrm
582 );*/
583 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
584 return l_return_status;
585 END;
586
587 /*---------------------------------------------------------------------------+
588 | |
589 | PROCEDURE: CREATE_PROGRAM |
590 | DESC : Program Creation |
591 | HISTORY: 20 March 2002 Created by Murthy |
592 *-------------------------------------------------------------------------- */
593 PROCEDURE create_program(p_api_version IN NUMBER,
594 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
595 x_return_status OUT NOCOPY VARCHAR2,
596 x_msg_count OUT NOCOPY NUMBER,
597 x_msg_data OUT NOCOPY VARCHAR2,
598 p_hdr_rec IN program_header_rec_type,
599 p_parent_agreement_number IN okl_k_headers_full_v.contract_number%TYPE DEFAULT NULL,
600 x_header_rec OUT NOCOPY chrv_rec_type,
601 x_k_header_rec OUT NOCOPY khrv_rec_type)
602 IS
603 l_api_version NUMBER := 1.0;
604 l_api_name CONSTANT VARCHAR2(30) := 'create_program';
605 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
606 parent_is_not_null BOOLEAN;
607 --fmiao--referred_is_not_null BOOLEAN;
608 l_jtot_object_code VARCHAR(200);
609 l_object1_id1 VARCHAR(200);
610 l_object1_id2 VARCHAR(200);
611 l_prnt_object1_id1 VARCHAR(200);
612 l_prnt_object1_id2 VARCHAR(200);
613 --l_vendor_name VARCHAR2(200);
614 --Bug# 2706328 (utf8 compliance)
615 l_vendor_name VARCHAR2(240);
616 l_agreement_id NUMBER;
617 l_parent_id NUMBER;
618 l_header_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
619 --x_header_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
620 l_k_header_rec OKL_CONTRACT_PUB.khrv_rec_type;
621 --x_k_header_rec OKL_CONTRACT_PUB.khrv_rec_type;
622 l_gvev_rec gvev_rec_type;
623 x_gvev_rec gvev_rec_type;
624 l_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
625 x_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
626
627 l_hdr_rec program_header_rec_type;
628
629 /*CURSOR cur_k_header(parent_agreement_number varchar2) is
630 SELECT v.id
631 FROM okc_k_headers_v v
632 WHERE v.contract_number = parent_agreement_number;
633 */
634
635 CURSOR cur_object_id(khr_id NUMBER) IS
636 SELECT jtot_object1_code, object1_id1, object1_id2
637 FROM okc_k_party_roles_b
638 WHERE dnz_chr_id = khr_id
639 AND rle_code = g_vendor_rle_code;
640
641 --Changed the cursor query to use base tables than uv --dkagrawa
642 CURSOR cur_vendor_name(chr_id NUMBER) IS
643 SELECT pov.vendor_name vendor_name
644 FROM okc_k_headers_b chrb,
645 okc_k_party_roles_b kpr,
646 po_vendors pov
647 WHERE chrb.id = kpr.dnz_chr_id
648 AND kpr.rle_code = 'OKL_VENDOR'
649 AND kpr.object1_id1 = pov.vendor_id
650 AND chrb.id = chr_id;
651
652
653
654 l_new_contract_id NUMBER;
655 l_msg_count NUMBER;
656 l_msg_data VARCHAR2(2000);
657
658 CURSOR role_csr(p_rle_code VARCHAR2) IS
659 Select access_level
660 from OKC_ROLE_SOURCES
661 where rle_code = p_rle_code
662 and buy_or_sell = 'S';
663
664 l_access_level OKC_ROLE_SOURCES.access_level%TYPE;
665
666 --Added by abhsxen for bug 6487870
667 l_kplv_rec okl_kpl_pvt.kplv_rec_type;
668 x_kplv_rec okl_kpl_pvt.kplv_rec_type;
669 --end abhsxen
670 BEGIN
671 x_return_status := OKC_API.G_RET_STS_SUCCESS;
672 -- Call start_activity to create savepoint, check compatibility
673 -- and initialize message list
674 x_return_status := OKC_API.START_ACTIVITY (l_api_name
675 ,p_init_msg_list
676 ,'_PVT'
677 ,x_return_status);
678 -- Check if activity started successfully
679 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
680 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
681 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
682 RAISE OKC_API.G_EXCEPTION_ERROR;
683 END IF;
684
685 parent_is_not_null := p_parent_agreement_number IS NOT NULL AND p_parent_agreement_number <> OKL_API.G_MISS_CHAR;
686 --fmiao--referred_is_not_null := p_hdr_rec.p_referred_id IS NOT NULL AND p_hdr_rec.p_referred_id <> OKL_API.G_MISS_NUM;
687
688 l_return_status := validate_attributes(p_hdr_rec => p_hdr_rec,
689 p_parent_agreement_number =>p_parent_agreement_number);
690
691 l_hdr_rec := p_hdr_rec;
692
693 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
694 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
695 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
696 RAISE OKL_API.G_EXCEPTION_ERROR;
697 END IF;
698
699 /* fmiao --
700 --The following call is not needed. If creating template, template_yn = 'Y'--
701 IF (p_hdr_rec.p_template_yn = 'Y') THEN
702 create_header(p_api_version => l_api_version,
703 x_return_status => l_return_status,
704 p_init_msg_list => OKL_API.G_TRUE,
705 x_msg_count => l_msg_count,
706 x_msg_data => l_msg_data,
707 p_hdr_rec => p_hdr_rec,
708 p_parent_agreement_number => p_parent_agreement_number,
709 p_chrv_rec => l_header_rec,
710 p_khrv_rec => l_k_header_rec,
711 x_chrv_rec => x_header_rec,
712 x_khrv_rec => x_k_header_rec);
713 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
714 NULL;
715 ELSE
716 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
717 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
718 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
719 RAISE OKL_API.G_EXCEPTION_ERROR;
720 END IF;
721 END IF;
722
723 IF (parent_is_not_null) THEN
724 OPEN cur_k_header(p_parent_agreement_number);
725 FETCH cur_k_header INTO l_parent_id;
726 IF(cur_k_header%found) THEN
727 CLOSE cur_k_header;
728 ELSE
729 CLOSE cur_k_header;
730 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_INVALID_PARENT_AGRMNT');
731 RAISE OKL_API.G_EXCEPTION_ERROR;
732 END IF;
733 l_new_contract_id := x_header_rec.id;
734 l_agreement_id := x_header_rec.id;
735 l_gvev_rec.chr_id := l_new_contract_id;
736 l_gvev_rec.dnz_chr_id := l_new_contract_id;
737 --fmiao--l_gvev_rec.chr_id_referred := l_parent_id;
738 l_gvev_rec.copied_only_yn := 'N';
739 OKC_CONTRACT_PUB.create_governance( p_api_version => l_api_version,
740 p_init_msg_list => OKL_API.G_TRUE,
741 x_return_status => l_return_status,
742 x_msg_count => l_msg_count,
743 x_msg_data => l_msg_data,
744 p_gvev_rec => l_gvev_rec,
745 x_gvev_rec => x_gvev_rec );
746 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
747 NULL;
748 ELSE
749 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
750 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
751 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
752 RAISE OKL_API.G_EXCEPTION_ERROR;
753 END IF;
754 END IF;
755
756 -- Setting jtot stuff and party info.
757 -- call for insert into vendor party role
758 OPEN cur_jtot_object_code(g_vendor_rle_code, p_hdr_rec.p_contract_category,g_buy_or_sell);
759 FETCH cur_jtot_object_code INTO l_jtot_object_code;
760 IF(cur_jtot_object_code%found) THEN
761 CLOSE cur_jtot_object_code;
762 ELSE
763 CLOSE cur_jtot_object_code;
764 -- Murthy Set message to be registered
765 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_JTOT_CODE_NOT_FOUND');
766 RAISE OKL_API.G_EXCEPTION_ERROR;
767 END IF;
768
769 -- set jtot stuff
770 OPEN cur_object_id(l_parent_id);
771 FETCH cur_object_id INTO l_jtot_object_code, l_object1_id1, l_object1_id2;
772 IF(cur_object_id%found) THEN
773 CLOSE cur_object_id;
774 ELSE
775 CLOSE cur_object_id;
776 -- Murthy Set message to be registered
777 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_JTOT_CODE_NOT_FOUND');
778 RAISE OKL_API.G_EXCEPTION_ERROR;
779 END IF;
780 -- Setting jtot stuff and party info.
781 l_agreement_id := x_header_rec.id;
782 l_cplv_rec.chr_id := l_agreement_id;
783 l_cplv_rec.dnz_chr_id := l_agreement_id;
784 l_cplv_rec.rle_code := g_vendor_rle_code;
785 l_cplv_rec.jtot_object1_code := l_jtot_object_code;
786 l_cplv_rec.object1_id1 := l_object1_id1;
787 l_cplv_rec.object1_id2 := l_object1_id2;
788
789 END IF; */
790 /*--fmiao--
791 IF (referred_is_not_null) THEN
792 -- set jtot stuff
793 OPEN cur_object_id(p_hdr_rec.p_referred_id);
794 FETCH cur_object_id INTO l_jtot_object_code, l_object1_id1, l_object1_id2;
795 IF(cur_object_id%found) THEN
796 CLOSE cur_object_id;
797 ELSE
798 CLOSE cur_object_id;
799 -- Murthy Set message to be registered
800 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_JTOT_CODE_NOT_FOUND');
801 RAISE OKL_API.G_EXCEPTION_ERROR;
802 END IF;
803 -- Setting jtot stuff and party info.
804 l_agreement_id := x_header_rec.id;
805 l_cplv_rec.chr_id := l_agreement_id;
806 l_cplv_rec.dnz_chr_id := l_agreement_id;
807 l_cplv_rec.rle_code := g_vendor_rle_code;
808 l_cplv_rec.jtot_object1_code := l_jtot_object_code;
809 l_cplv_rec.object1_id1 := l_object1_id1;
810 l_cplv_rec.object1_id2 := l_object1_id2;
811 END IF; --fmiao--*/
812 --ELSIF (p_hdr_rec.p_template_yn = 'N') THEN
813 IF (l_hdr_rec.p_template_yn IS NULL) THEN
814 l_hdr_rec.p_template_yn := 'N';
815 END IF;
816 create_header(p_api_version => l_api_version,
817 x_return_status => l_return_status,
818 p_init_msg_list => OKL_API.G_TRUE,
819 x_msg_count => l_msg_count,
820 x_msg_data => l_msg_data,
821 p_hdr_rec => l_hdr_rec,
822 p_parent_agreement_number => p_parent_agreement_number,
823 p_chrv_rec => l_header_rec,
824 p_khrv_rec => l_k_header_rec,
825 x_chrv_rec => x_header_rec,
826 x_khrv_rec => x_k_header_rec);
827 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
828 NULL;
829 ELSE
830 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
831 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
832 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
833 RAISE OKL_API.G_EXCEPTION_ERROR;
834 END IF;
835 END IF;
836 -- Setting jtot stuff and party info.
837 -- call for insert into vendor party role
838 OPEN cur_jtot_object_code(g_vendor_rle_code, p_hdr_rec.p_contract_category,g_buy_or_sell);
839 FETCH cur_jtot_object_code INTO l_jtot_object_code;
840 IF(cur_jtot_object_code%found) THEN
841 CLOSE cur_jtot_object_code;
842 ELSE
843 CLOSE cur_jtot_object_code;
844 -- Murthy Set message to be registered
845 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_JTOT_CODE_NOT_FOUND');
846 RAISE OKL_API.G_EXCEPTION_ERROR;
847 END IF;
848 l_agreement_id := x_header_rec.id;
849 l_cplv_rec.chr_id := l_agreement_id;
850 l_cplv_rec.dnz_chr_id := l_agreement_id;
851 l_cplv_rec.rle_code := g_vendor_rle_code;
852 l_cplv_rec.jtot_object1_code := l_jtot_object_code;
853 l_cplv_rec.object1_id1 := p_hdr_rec.p_object1_id1;
854 l_cplv_rec.object1_id2 := p_hdr_rec.p_object1_id2;
855 IF (parent_is_not_null) THEN
856 OPEN cur_k_header(p_parent_agreement_number);
857 FETCH cur_k_header INTO l_parent_id;
858 IF(cur_k_header%found) THEN
859 CLOSE cur_k_header;
860 ELSE
861 CLOSE cur_k_header;
862 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_INVALID_PARENT_AGRMNT');
863 RAISE OKL_API.G_EXCEPTION_ERROR;
864 END IF;
865 l_agreement_id := x_header_rec.id;
866 l_gvev_rec.chr_id := l_agreement_id;
867 l_gvev_rec.dnz_chr_id := l_agreement_id;
868 l_gvev_rec.chr_id_referred := l_parent_id;
869 l_gvev_rec.copied_only_yn := 'N';
870 OKC_CONTRACT_PUB.create_governance( p_api_version => l_api_version,
871 p_init_msg_list => OKL_API.G_TRUE,
872 x_return_status => l_return_status,
873 x_msg_count => l_msg_count,
874 x_msg_data => l_msg_data,
875 p_gvev_rec => l_gvev_rec,
876 x_gvev_rec => x_gvev_rec );
877 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
878 NULL;
879 ELSE
880 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
881 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
882 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
883 RAISE OKL_API.G_EXCEPTION_ERROR;
884 END IF;
885 END IF;
886
887 END IF;
888 /*--fmiao--
889 IF (l_jtot_object_code IS NOT NULL) THEN
890 IF (parent_is_not_null AND p_hdr_rec.p_template_yn = 'N') THEN
891 --OPEN cur_vendor_name(l_cplv_rec.dnz_chr_id);
892 OPEN cur_vendor_name(l_parent_id);
893 FETCH cur_vendor_name INTO l_vendor_name;
894 IF(cur_vendor_name%found) THEN
895 CLOSE cur_vendor_name;
896 ELSE
897 CLOSE cur_vendor_name;
898 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
899 p_msg_name => 'OKL_PRNT_AGMT_MATCH_VENDOR',
900 p_token1 => 'token1',
901 p_token1_value => l_vendor_name
902 );
903 RAISE OKL_API.G_EXCEPTION_ERROR;
904 END IF;
905 OPEN cur_parent_object_id(l_parent_id);
906 FETCH cur_parent_object_id INTO l_prnt_object1_id1, l_prnt_object1_id2;
907 IF(cur_parent_object_id%found) THEN
908 CLOSE cur_parent_object_id;
909 ELSE
910 CLOSE cur_parent_object_id;
911 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
912 p_msg_name => 'OKL_PRNT_AGMT_MATCH_VENDOR',
913 p_token1 => 'token1',
914 p_token1_value => l_vendor_name
915 );
916 RAISE OKL_API.G_EXCEPTION_ERROR;
917 END IF;
918 IF (l_prnt_object1_id1 <> p_hdr_rec.p_object1_id1 OR l_prnt_object1_id2 <>
919 p_hdr_rec.p_object1_id2) THEN
920 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
921 p_msg_name => 'OKL_PRNT_AGMT_MATCH_VENDOR',
922 p_token1 => 'token1',
923 p_token1_value => l_vendor_name
924 );
925 RAISE OKL_API.G_EXCEPTION_ERROR;
926
927 END IF;
928 END IF;
929 END IF; --fmiao--*/
930
931 --END IF;
932
933 ----- Changes by Kanti
934 ----- Validate the JTOT Object code, ID1 and ID2
935
936 OPEN role_csr(l_cplv_rec.rle_code);
937 FETCH role_csr INTO l_access_level;
938 CLOSE role_csr;
939
940 IF (l_access_level = 'S') THEN
941 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
942 p_init_msg_list => OKC_API.G_FALSE,
943 x_return_status => x_return_status,
944 x_msg_count => x_msg_count,
945 x_msg_data => x_msg_data,
946 p_object_name => l_cplv_rec.jtot_object1_code,
947 p_id1 => l_cplv_rec.object1_id1,
948 p_id2 => l_cplv_rec.object1_id2);
949 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
950 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
951 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
952 RAISE OKL_API.G_EXCEPTION_ERROR;
953 END IF;
954
955 END IF;
956
957
958 ---- Changes End
959
960
961 OKC_CONTRACT_PARTY_PUB.create_k_party_role(p_api_version => l_api_version,
962 p_init_msg_list => OKL_API.G_TRUE,
963 x_return_status => l_return_status,
964 x_msg_count => l_msg_count,
965 x_msg_data => l_msg_data,
966 p_cplv_rec => l_cplv_rec,
967 x_cplv_rec => x_cplv_rec );
968 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
969 NULL;
970 ELSE
971 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
972 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
973 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
974 RAISE OKL_API.G_EXCEPTION_ERROR;
975 END IF;
976 END IF;
977
978 --Added by abhsxen for bug 6487870
979 l_kplv_rec.ID := x_cplv_rec.ID;
980
981 OKL_KPL_PVT.Insert_Row(
982 p_api_version => p_api_version,
983 p_init_msg_list => p_init_msg_list,
984 x_return_status => x_return_status,
985 x_msg_count => x_msg_count,
986 x_msg_data => x_msg_data,
987 p_kplv_rec => l_kplv_rec,
988 x_kplv_rec => x_kplv_rec);
989
990 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
991 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
992 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
993 RAISE OKL_API.G_EXCEPTION_ERROR;
994 END IF;
995 --end abhsxen
996
997 -- Call end_activity
998 OKC_API.END_ACTIVITY (x_msg_count,
999 x_msg_data );
1000
1001 EXCEPTION
1002
1003 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1004 x_return_status := OKL_API.G_RET_STS_ERROR;
1005 /*
1006 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1007 (p_api_name => l_api_name
1008 ,p_pkg_name => G_PKG_NAME
1009 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1010 ,x_msg_count => x_msg_count
1011 ,x_msg_data => x_msg_data
1012 ,p_api_type => '_PVT'
1013 );
1014
1015 */
1016 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1017 x_return_status := OKL_API.G_RET_STS_ERROR;
1018 /* x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1019 ,g_pkg_name
1020 ,'OKL_API.G_RET_STS_ERROR'
1021 ,x_msg_count
1022 ,x_msg_data
1023 ,'_PVT'
1024 );
1025
1026 */
1027 WHEN OTHERS THEN
1028 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1029 /* x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1030 ,g_pkg_name
1031 ,'OTHERS'
1032 ,x_msg_count
1033 ,x_msg_data
1034 ,'_PVT'
1035 );
1036 */
1037 END;
1038
1039
1040
1041 /*NEW CODE END*/
1042
1043 PROCEDURE update_program(p_api_version IN NUMBER,
1044 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1045 x_return_status OUT NOCOPY VARCHAR2,
1046 x_msg_count OUT NOCOPY NUMBER,
1047 x_msg_data OUT NOCOPY VARCHAR2,
1048 p_hdr_rec IN program_header_rec_type,
1049 p_program_id IN okl_k_headers_full_v.id%TYPE,
1050 p_parent_agreement_id IN okc_k_headers_v.ID%TYPE DEFAULT NULL)
1051 IS
1052
1053 -- check for whether update is allowed for this contract
1054 -- check for whether workflow process is active for this contract
1055 -- depending on these,call the update_contract_header
1056 -- not clear on p_restrict_update parameter in the update_contract_header api ?????????
1057 -- templates are not updateable
1058
1059 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1060
1061 l_msg_count NUMBER;
1062 l_msg_data VARCHAR2(2000);
1063 l_api_version NUMBER := 1.0;
1064
1065 l_api_name CONSTANT VARCHAR2(30) := 'update_program';
1066
1067 --l_return_status VARCHAR2(3);
1068
1069 l1_header_rec chrv_rec_type;
1070 l2_header_rec chrv_rec_type;
1071 l_k_header_rec khrv_rec_type;
1072 x_k_header_rec khrv_rec_type;
1073
1074 --Murthy
1075 l1_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
1076 l2_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
1077
1078 cpsv_rec_type3 process_rec_type;
1079 cpsv_rec_type4 process_rec_type;
1080
1081 CURSOR l_chr_csr1(contract_id NUMBER, contract_no VARCHAR2,contract_no_modifier VARCHAR2) IS
1082 SELECT 'x'
1083 FROM okc_k_headers_b
1084 WHERE contract_number = contract_no
1085 AND contract_number_modifier = contract_no_modifier
1086 AND id <> NVL(contract_id,-99999);
1087
1088 CURSOR cur_k_header(program_id number) IS
1089 SELECT v.contract_number FROM okc_k_headers_b v
1090 WHERE v.id =program_id;
1091
1092 CURSOR cur_k_process_id(program_id number) IS
1093 SELECT p.id
1094 FROM okc_k_processes_v p
1095 WHERE p.chr_id=program_id;
1096
1097 l_party_id NUMBER;
1098
1099 l_contract_id VARCHAR2(50);
1100 l_process_id NUMBER;
1101 l_dummy VARCHAR2(1);
1102 l_found BOOLEAN;
1103
1104 l_return_value VARCHAR2(1) := 'N';
1105
1106 CURSOR role_csr(p_rle_code VARCHAR2) IS
1107 Select access_level
1108 from OKC_ROLE_SOURCES
1109 where rle_code = p_rle_code
1110 and buy_or_sell = 'S';
1111
1112 l_access_level OKC_ROLE_SOURCES.access_level%TYPE;
1113
1114 /* Manu Bug #4671978 27-Oct-2005 Begin */
1115 CURSOR cl_get_gov_id(p_program_id OKC_K_HEADERS_V.ID%TYPE) IS
1116 SELECT ID FROM OKC_GOVERNANCES WHERE DNZ_CHR_ID = p_program_id;
1117
1118 l_gov_id OKC_GOVERNANCES.ID%TYPE;
1119 parent_is_not_null BOOLEAN;
1120 l_gvev_rec gvev_rec_type;
1121 x_gvev_rec gvev_rec_type;
1122 /* Manu Bug #4671978 27-Oct-2005 End */
1123
1124 --Added by abhsxen for bug 6487870
1125 l_kplv_rec okl_kpl_pvt.kplv_rec_type;
1126 x_kplv_rec okl_kpl_pvt.kplv_rec_type;
1127 --end abhsxen
1128 BEGIN
1129
1130 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
1131 ,p_init_msg_list => p_init_msg_list
1132 ,p_api_type => '_PVT'
1133 ,x_return_status => x_return_status
1134 );
1135
1136 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1137 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1138 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1139 RAISE OKL_API.G_EXCEPTION_ERROR;
1140 END IF;
1141
1142 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1143
1144 IF ((p_hdr_rec.p_agreement_number = OKL_API.G_MISS_CHAR) OR (p_hdr_rec.p_agreement_number IS NULL)) THEN
1145 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_AGREEMENT_NO_REQUIRED');
1146 RAISE OKL_API.G_EXCEPTION_ERROR;
1147 END IF;
1148
1149 -- check whether the contract already exists before calling the api.
1150
1151 OPEN l_chr_csr1(p_program_id, p_hdr_rec.p_agreement_number,'1.0');
1152 FETCH l_chr_csr1 into l_dummy;
1153 l_found := l_chr_csr1%FOUND;
1154 CLOSE l_chr_csr1;
1155
1156 IF (l_found) THEN
1157 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1158 p_msg_name => 'OKL_VP_CONTRACT_EXISTS',
1159 p_token1 => 'NUMBER',
1160 p_token1_value => p_hdr_rec.p_agreement_number
1161 );
1162 RAISE OKL_API.G_EXCEPTION_ERROR;
1163 END IF;
1164
1165 IF ((p_hdr_rec.p_start_date = OKL_API.G_MISS_DATE) OR (p_hdr_rec.p_start_date IS NULL)) THEN
1166 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_START_DATE_REQUIRED');
1167 RAISE OKL_API.G_EXCEPTION_ERROR;
1168 END IF;
1169
1170 IF ((p_hdr_rec.p_end_date = OKL_API.G_MISS_DATE) OR (p_hdr_rec.p_end_date IS NULL)) THEN
1171 NULL;
1172 ELSIF (trunc(p_hdr_rec.p_end_date) < trunc(p_hdr_rec.p_start_date))THEN
1173 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1174 p_msg_name => 'OKL_INVALID_TO_DATE');
1175 RAISE OKL_API.G_EXCEPTION_ERROR;
1176 ELSIF (trunc(p_hdr_rec.p_end_date) < trunc(sysdate)) THEN
1177 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1178 p_msg_name => 'OKL_INVALID_EFF_TO_DATE');
1179 RAISE OKL_API.G_EXCEPTION_ERROR;
1180 END IF;
1181
1182 IF ((p_program_id = OKL_API.G_MISS_NUM) OR (p_program_id IS NULL)) THEN
1183 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'ID');
1184 RAISE OKL_API.G_EXCEPTION_ERROR;
1185 END IF;
1186
1187 -- to get the contract_number for the given program_id
1188
1189 OPEN cur_k_header(p_program_id);
1190 FETCH cur_k_header INTO l_contract_id;
1191 CLOSE cur_k_header;
1192
1193 l1_header_rec.id := p_program_id;
1194 l1_header_rec.contract_number := p_hdr_rec.p_agreement_number;
1195 l1_header_rec.qcl_id := p_hdr_rec.p_qcl_id;
1196
1197 l1_header_rec.short_description := p_hdr_rec.p_short_description;
1198 l1_header_rec.description := p_hdr_rec.p_description;
1199 l1_header_rec.comments := p_hdr_rec.p_comments;
1200
1201
1202 l1_header_rec.start_date := p_hdr_rec.p_start_date;
1203 l1_header_rec.end_date := p_hdr_rec.p_end_date;
1204
1205 -- abindal start --
1206 l_k_header_rec.attribute_category := p_hdr_rec.p_attribute_category;
1207 l_k_header_rec.attribute1 := p_hdr_rec.p_attribute1;
1208 l_k_header_rec.attribute2 := p_hdr_rec.p_attribute2;
1209 l_k_header_rec.attribute3 := p_hdr_rec.p_attribute3;
1210 l_k_header_rec.attribute4 := p_hdr_rec.p_attribute4;
1211 l_k_header_rec.attribute5 := p_hdr_rec.p_attribute5;
1212 l_k_header_rec.attribute6 := p_hdr_rec.p_attribute6;
1213 l_k_header_rec.attribute7 := p_hdr_rec.p_attribute7;
1214 l_k_header_rec.attribute8 := p_hdr_rec.p_attribute8;
1215 l_k_header_rec.attribute9 := p_hdr_rec.p_attribute9;
1216 l_k_header_rec.attribute10 := p_hdr_rec.p_attribute10;
1217 l_k_header_rec.attribute11 := p_hdr_rec.p_attribute11;
1218 l_k_header_rec.attribute12 := p_hdr_rec.p_attribute12;
1219 l_k_header_rec.attribute13 := p_hdr_rec.p_attribute13;
1220 l_k_header_rec.attribute14 := p_hdr_rec.p_attribute14;
1221 l_k_header_rec.attribute15 := p_hdr_rec.p_attribute15;
1222 /* sosharma ,31 oct 2006
1223 Build:R12
1224 Assigning Legal entity value to l_k_header_rec
1225 Start Changes*/
1226 l_k_header_rec.legal_entity_id := p_hdr_rec.p_legal_entity_id;
1227 /* End changes */
1228 -- abindal end --
1229
1230
1231 --Murthy
1232 --Added so that when a agreement with parent is created, the new agreement
1233 --must have category of PROGRAM only. Setting intent to BUY_OR_SELL
1234 l1_header_rec.buy_or_sell := g_buy_or_sell;
1235 l1_header_rec.scs_code := p_hdr_rec.p_contract_category;
1236 IF (OKC_CONTRACT_PUB.Update_Allowed(p_program_id) <> 'Y') THEN
1237 l_return_status :=OKL_API.G_RET_STS_ERROR;
1238 OKL_API.set_message(p_app_name => g_app_name,
1239 p_msg_name => 'OKL_VP_UPDATE_NOT_ALLOWED'
1240 );
1241 RAISE OKL_API.G_EXCEPTION_ERROR;
1242 END IF;
1243
1244 IF (is_process_active(p_program_id) <> 'N') THEN
1245 l_return_status :=OKL_API.G_RET_STS_ERROR;
1246 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1247 p_msg_name => 'OKL_VP_APPROVAL_PROCESS_ACTV'
1248 );
1249 RAISE OKL_API.G_EXCEPTION_ERROR;
1250 END IF;
1251
1252 OKL_CONTRACT_PUB.update_contract_header(
1253 p_api_version => l_api_version,
1254 x_return_status => l_return_status,
1255 p_init_msg_list => OKL_API.G_TRUE,
1256 x_msg_count => l_msg_count,
1257 x_msg_data => l_msg_data,
1258 p_restricted_update => OKL_API.G_FALSE,
1259 p_chrv_rec => l1_header_rec,
1260 p_khrv_rec => l_k_header_rec,
1261 x_chrv_rec => l2_header_rec,
1262 x_khrv_rec => x_k_header_rec);
1263
1264
1265 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1266 /* Manu 29-Jun-2005 Begin */
1267 passed_to_incomplete(p_api_version => p_api_version
1268 ,p_init_msg_list => p_init_msg_list
1269 ,x_return_status => x_return_status
1270 ,x_msg_count => x_msg_count
1271 ,x_msg_data => x_msg_data
1272 ,p_program_id => p_program_id
1273 );
1274 /****
1275 IF (l2_header_rec.STS_CODE = 'PASSED') THEN
1276
1277 okl_contract_status_pub.update_contract_status(p_api_version => p_api_version
1278 ,p_init_msg_list => p_init_msg_list
1279 ,x_return_status => x_return_status
1280 ,x_msg_count => x_msg_count
1281 ,x_msg_data => x_msg_data
1282 ,p_khr_status => l_incomplete_status_code
1283 ,p_chr_id => p_program_id
1284 );
1285 END IF;
1286 ****/
1287 /* Manu 29-Jun-2005 End */
1288
1289 /* Manu Bug #4671978 27-Oct-2005 Begin */
1290 parent_is_not_null := p_parent_agreement_id IS NOT NULL AND p_parent_agreement_id <> OKL_API.G_MISS_NUM;
1291
1292 OPEN cl_get_gov_id(p_program_id);
1293 FETCH cl_get_gov_id INTO l_gov_id;
1294 CLOSE cl_get_gov_id;
1295
1296 IF (parent_is_not_null) THEN
1297 IF (l_gov_id is not null) THEN
1298 -- Governances record exists update with the new parent agreement.
1299 l_gvev_rec.id := l_gov_id;
1300 l_gvev_rec.chr_id := p_program_id;
1301 l_gvev_rec.dnz_chr_id := p_program_id;
1302 l_gvev_rec.chr_id_referred := p_parent_agreement_id;
1303 l_gvev_rec.copied_only_yn := 'N';
1304 OKC_CONTRACT_PUB.update_governance( p_api_version => p_api_version,
1305 p_init_msg_list => p_init_msg_list,
1306 x_return_status => l_return_status,
1307 x_msg_count => l_msg_count,
1308 x_msg_data => l_msg_data,
1309 p_gvev_rec => l_gvev_rec,
1310 x_gvev_rec => x_gvev_rec );
1311 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1312 NULL;
1313 ELSE
1314 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1315 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1316 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1317 RAISE OKL_API.G_EXCEPTION_ERROR;
1318 END IF;
1319 END IF;
1320 ELSE
1321 -- Parent Agreement does not already exists create a new record.
1322 l_gvev_rec.chr_id := p_program_id;
1323 l_gvev_rec.dnz_chr_id := p_program_id;
1324 l_gvev_rec.chr_id_referred := p_parent_agreement_id;
1325 l_gvev_rec.copied_only_yn := 'N';
1326 OKC_CONTRACT_PUB.create_governance( p_api_version => p_api_version,
1327 p_init_msg_list => p_init_msg_list,
1328 x_return_status => l_return_status,
1329 x_msg_count => l_msg_count,
1330 x_msg_data => l_msg_data,
1331 p_gvev_rec => l_gvev_rec,
1332 x_gvev_rec => x_gvev_rec );
1333 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1334 NULL;
1335 ELSE
1336 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1337 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1338 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1339 RAISE OKL_API.G_EXCEPTION_ERROR;
1340 END IF;
1341 END IF;
1342 END IF;
1343 ELSE
1344 IF (l_gov_id is not null) THEN
1345 -- Governances exists and since the Parent Agreement is NULL
1346 -- delete the governances record for this agreement.
1347 l_gvev_rec.id := l_gov_id;
1348 OKC_CONTRACT_PUB.delete_governance( p_api_version => p_api_version,
1349 p_init_msg_list => p_init_msg_list,
1350 x_return_status => l_return_status,
1351 x_msg_count => l_msg_count,
1352 x_msg_data => l_msg_data,
1353 p_gvev_rec => l_gvev_rec);
1354 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1355 NULL;
1356 ELSE
1357 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1358 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1359 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1360 RAISE OKL_API.G_EXCEPTION_ERROR;
1361 END IF;
1362 END IF;
1363 END IF;
1364 END IF;
1365 /* Manu Bug #4671978 27-Oct-2005 End */
1366
1367
1368 IF (((p_hdr_rec.p_workflow_process) <> OKL_API.G_MISS_NUM) AND ((p_hdr_rec.p_workflow_process) IS NOT NULL)) THEN
1369
1370 -- update the okc_processes_v with the new values
1371 OPEN cur_k_process_id(p_program_id);
1372 FETCH cur_k_process_id INTO l_process_id;
1373
1374 IF(cur_k_process_id%FOUND) THEN
1375 cpsv_rec_type3.id :=l_process_id;
1376 close cur_k_process_id;
1377 cpsv_rec_type3.chr_id :=p_program_id;
1378 cpsv_rec_type3.pdf_id :=p_hdr_rec.p_workflow_process;
1379
1380 okc_contract_pub.update_contract_process(
1381 p_api_version => l_api_version,
1382 x_return_status => l_return_status,
1383 p_init_msg_list => OKL_API.G_FALSE,
1384 x_msg_count => l_msg_count,
1385 x_msg_data => l_msg_data,
1386 p_cpsv_rec => cpsv_rec_type3,
1387 x_cpsv_rec => cpsv_rec_type4);
1388
1389 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1390 NULL;
1391 ELSE
1392 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1393 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1394 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1395 RAISE OKL_API.G_EXCEPTION_ERROR;
1396 END IF;
1397 END IF;
1398
1399 ELSE
1400
1401 -- create a record in the okc_k_process
1402 cpsv_rec_type3.chr_id :=p_program_id;
1403 cpsv_rec_type3.pdf_id :=p_hdr_rec.p_workflow_process;
1404
1405 okc_contract_pub.create_contract_process(
1406 p_api_version => l_api_version,
1407 p_init_msg_list => OKL_API.G_FALSE,
1408 x_return_status => l_return_status,
1409 x_msg_count => l_msg_count,
1410 x_msg_data => l_msg_data,
1411 p_cpsv_rec => cpsv_rec_type3,
1412 x_cpsv_rec => cpsv_rec_type4);
1413
1414 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1415 NULL;
1416 ELSE
1417 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1418 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1419 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1420 RAISE OKL_API.G_EXCEPTION_ERROR;
1421 END IF;
1422 END IF;
1423
1424 END IF; -- cursor cur_k_process end if
1425
1426 ELSE -- this else for workflow process null checking
1427 NULL;
1428 END IF;
1429
1430 ELSE
1431
1432 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1433 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1434 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1435 RAISE OKL_API.G_EXCEPTION_ERROR;
1436 END IF;
1437
1438 END IF;
1439
1440 -- Murthy
1441 OPEN cur_k_party_roles(p_program_id);
1442 FETCH cur_k_party_roles INTO l_party_id;
1443 IF (cur_k_party_roles%NOTFOUND) THEN
1444 CLOSE cur_k_party_roles;
1445 -- Murthy Set message to be registered
1446 OKL_API.set_message(p_app_name => g_app_name,
1447 p_msg_name => 'OKL_JTOT_CODE_NOT_FOUND'
1448 );
1449 RAISE OKL_API.G_EXCEPTION_ERROR;
1450 END IF;
1451 CLOSE cur_k_party_roles;
1452
1453 l1_cplv_rec.id := l_party_id;
1454 IF (p_hdr_rec.p_object1_id1 IS NULL OR p_hdr_rec.p_object1_id1 = OKL_API.G_MISS_CHAR) OR
1455 (p_hdr_rec.p_object1_id2 IS NULL OR p_hdr_rec.p_object1_id2 = OKL_API.G_MISS_CHAR) THEN
1456 NULL;
1457 ELSE
1458 l1_cplv_rec.object1_id1 := p_hdr_rec.p_object1_id1;
1459 l1_cplv_rec.object1_id2 := p_hdr_rec.p_object1_id2;
1460 END IF;
1461 l1_cplv_rec.cognomen:= null;
1462 l1_cplv_rec.alias:= null;
1463
1464 ----- Changes by Kanti
1465 ----- Validate the JTOT Object code, ID1 and ID2
1466
1467
1468 OPEN role_csr(l1_cplv_rec.rle_code);
1469 FETCH role_csr INTO l_access_level;
1470 CLOSE role_csr;
1471
1472 IF (l_access_level = 'S') THEN
1473
1474 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
1475 p_init_msg_list => OKC_API.G_FALSE,
1476 x_return_status => x_return_status,
1477 x_msg_count => x_msg_count,
1478 x_msg_data => x_msg_data,
1479 p_object_name => l1_cplv_rec.jtot_object1_code,
1480 p_id1 => l1_cplv_rec.object1_id1,
1481 p_id2 => l1_cplv_rec.object1_id2);
1482 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1483 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1484 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1485 RAISE OKL_API.G_EXCEPTION_ERROR;
1486 END IF;
1487
1488 END IF;
1489
1490
1491 ---- Changes End
1492
1493
1494 OKC_CONTRACT_PARTY_PUB.update_k_party_role(p_api_version => p_api_version,
1495 p_init_msg_list => OKL_API.G_FALSE,
1496 x_return_status => l_return_status,
1497 x_msg_count => x_msg_count,
1498 x_msg_data => x_msg_data,
1499 p_cplv_rec => l1_cplv_rec,
1500 x_cplv_rec => l2_cplv_rec
1501 );
1502
1503 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1504 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1505 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1506 RAISE OKL_API.G_EXCEPTION_ERROR;
1507 END IF;
1508
1509 --Added by abhsxen for bug 6487870
1510 l_kplv_rec.ID := l2_cplv_rec.ID;
1511
1512 OKL_KPL_PVT.update_row(
1513 p_api_version => p_api_version,
1514 p_init_msg_list => p_init_msg_list,
1515 x_return_status => x_return_status,
1516 x_msg_count => x_msg_count,
1517 x_msg_data => x_msg_data,
1518 p_kplv_rec => l_kplv_rec,
1519 x_kplv_rec => x_kplv_rec);
1520
1521 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1522 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1523 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1524 RAISE OKL_API.G_EXCEPTION_ERROR;
1525 END IF;
1526 --end abhsxen
1527
1528 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
1529 ,x_msg_data => x_msg_data
1530 );
1531
1532 EXCEPTION
1533
1534 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1535
1536 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1537 (p_api_name => l_api_name
1538 ,p_pkg_name => G_PKG_NAME
1539 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1540 ,x_msg_count => x_msg_count
1541 ,x_msg_data => x_msg_data
1542 ,p_api_type => '_PVT'
1543 );
1544
1545 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1546
1547 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1548 ,g_pkg_name
1549 ,'OKL_API.G_RET_STS_ERROR'
1550 ,x_msg_count
1551 ,x_msg_data
1552 ,'_PVT'
1553 );
1554
1555 WHEN OTHERS THEN
1556
1557 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1558 ,g_pkg_name
1559 ,'OTHERS'
1560 ,x_msg_count
1561 ,x_msg_data
1562 ,'_PVT'
1563 );
1564
1565 -- end of update_agreement
1566 END;
1567
1568
1569 -- Function to check if a workflow is active for a contract
1570 -- Function Name: Is_Process_Active
1571 -- An item is considered active if its end_date is NULL
1572
1573 FUNCTION Is_Process_Active(p_chr_id IN okl_k_headers_full_v.id%TYPE) RETURN VARCHAR2 IS
1574
1575 l_wf_name OKC_PROCESS_DEFS_B.WF_NAME%TYPE;
1576 l_item_key OKC_K_PROCESSES.PROCESS_ID%TYPE;
1577 l_return_code VARCHAR2(1) := 'N';
1578 l_end_date DATE;
1579
1580 -- cursor for item type and item key
1581 CURSOR l_pdfv_csr Is
1582 SELECT pdfv.wf_name, cpsv.process_id
1583 FROM okc_process_defs_b pdfv,
1584 okc_k_processes cpsv
1585 WHERE pdfv.id = cpsv.pdf_id
1586 AND cpsv.chr_id = p_chr_id;
1587
1588 -- cursor to check active process
1589 Cursor l_wfitems_csr IS
1590 SELECT end_date
1591 FROM wf_items
1592 WHERE item_type = l_wf_name
1593 AND item_key = l_item_key;
1594
1595 BEGIN
1596
1597 -- get item type and item key
1598 OPEN l_pdfv_csr;
1599 FETCH l_pdfv_csr into l_wf_name, l_item_key;
1600 IF (l_pdfv_csr%NOTFOUND OR l_wf_name IS NULL OR l_item_key IS NULL) THEN
1601 CLOSE l_pdfv_csr;
1602 RETURN l_return_code;
1603 END IF;
1604 CLOSE l_pdfv_csr;
1605
1606 -- check whether process is active or not
1607 OPEN l_wfitems_csr;
1608 FETCH l_wfitems_csr into l_end_date;
1609 IF (l_wfitems_csr%NOTFOUND or l_end_date IS NOT NULL) THEN
1610 l_return_code := 'N';
1611 ELSE
1612 l_return_code := 'Y';
1613 END IF;
1614 CLOSE l_wfitems_csr;
1615
1616 RETURN l_return_code;
1617 EXCEPTION
1618 WHEN NO_DATA_FOUND THEN
1619 RETURN (l_return_code);
1620
1621 END Is_Process_Active;
1622
1623
1624 -- Procedure to change the status of the a given agreement
1625 -- from the status PASSED to INCOMPLETE.
1626 -- Procedure Name: passed_to_incomplete
1627 -- Date Created : 29-June-2005
1628 -- Ignores if the status of the given agreement(contract) is in any other statys
1629 -- other than status PASSED.
1630
1631 PROCEDURE passed_to_incomplete(p_api_version IN NUMBER,
1632 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1633 x_return_status OUT NOCOPY VARCHAR2,
1634 x_msg_count OUT NOCOPY NUMBER,
1635 x_msg_data OUT NOCOPY VARCHAR2,
1636 p_program_id IN OKC_K_HEADERS_V.ID%TYPE) IS
1637
1638 l_sts_code OKC_K_HEADERS_V.STS_CODE%TYPE := NULL;
1639 l_cr_id OKL_VP_CHANGE_REQUESTS.ID%TYPE := NULL;
1640 l_cr_ret_sts_code okl_vp_change_requests.status_code%TYPE := NULL;
1641 l_cr_status_code okl_vp_change_requests.status_code%TYPE := NULL;
1642 l_cr_type okl_vp_change_requests.change_type_code%TYPE := NULL;
1643 l_incomplete_status_code OKC_K_HEADERS_V.STS_CODE%TYPE := 'INCOMPLETE';
1644
1645 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1646
1647 l_msg_count NUMBER;
1648 l_msg_data VARCHAR2(2000);
1649 l_api_version NUMBER := 1.0;
1650
1651 l_api_name CONSTANT VARCHAR2(30) := 'passed_to_incomplete';
1652
1653 -- cursor to get contract status
1654 CURSOR l_sts_code_csr(p_chr_id OKC_K_HEADERS_V.ID%TYPE) is
1655 SELECT sts_code
1656 FROM okc_k_headers_v
1657 WHERE id = p_chr_id;
1658
1659 -- cursor to get the corresponding Change Request record
1660 CURSOR l_cr_csr(p_chr_id OKC_K_HEADERS_V.ID%TYPE) is
1661 SELECT id, status_code, change_type_code
1662 FROM OKL_VP_CHANGE_REQUESTS
1663 WHERE id = (SELECT crs_id FROM okl_k_headers WHERE id = p_chr_id);
1664
1665 BEGIN
1666
1667 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
1668 ,p_init_msg_list => p_init_msg_list
1669 ,p_api_type => '_PVT'
1670 ,x_return_status => x_return_status
1671 );
1672
1673 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1674 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1675 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1676 RAISE OKL_API.G_EXCEPTION_ERROR;
1677 END IF;
1678
1679 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1680
1681 --
1682 IF (p_program_id <> OKL_API.G_MISS_NUM OR p_program_id IS NOT NULL) THEN
1683 -- Get the agreement Status
1684 OPEN l_sts_code_csr(p_chr_id => p_program_id);
1685 FETCH l_sts_code_csr INTO l_sts_code;
1686 -- IF (l_sts_code_csr%NOTFOUND OR l_sts_code IS NULL) THEN
1687 -- CLOSE l_sts_code_csr;
1688 -- END IF;
1689 CLOSE l_sts_code_csr;
1690
1691 -- Check if the given Change Request is of Association type of CR
1692 OPEN l_cr_csr(p_chr_id => p_program_id);
1693 FETCH l_cr_csr INTO l_cr_id, l_cr_status_code, l_cr_type;
1694 CLOSE l_cr_csr;
1695
1696 END IF;
1697
1698 IF (l_sts_code = 'PASSED') THEN
1699 okl_contract_status_pub.update_contract_status(p_api_version => p_api_version
1700 ,p_init_msg_list => p_init_msg_list
1701 ,x_return_status => x_return_status
1702 ,x_msg_count => x_msg_count
1703 ,x_msg_data => x_msg_data
1704 ,p_khr_status => l_incomplete_status_code
1705 ,p_chr_id => p_program_id
1706 );
1707 IF (l_cr_type = 'AGREEMENT' AND l_cr_id IS NOT NULL) THEN
1708 -- If the Change Request is of type AGREEMENT.
1709 okl_vp_change_request_pvt.cascade_request_status_edit(p_api_version => p_api_version
1710 ,p_init_msg_list => p_init_msg_list
1711 ,x_return_status => x_return_status
1712 ,x_msg_count => x_msg_count
1713 ,x_msg_data => x_msg_data
1714 ,p_vp_crq_id => l_cr_id
1715 ,x_status_code => l_cr_ret_sts_code
1716 );
1717 END IF;
1718 ELSIF (l_sts_code = 'ACTIVE') THEN
1719 -- If the Change Request is of type ASSOCIATION.
1720 IF (l_cr_type = 'ASSOCIATION' AND l_cr_status_code = 'PASSED'
1721 AND l_cr_id IS NOT NULL) THEN
1722 okl_vp_change_request_pvt.cascade_request_status_edit(p_api_version => p_api_version
1723 ,p_init_msg_list => p_init_msg_list
1724 ,x_return_status => x_return_status
1725 ,x_msg_count => x_msg_count
1726 ,x_msg_data => x_msg_data
1727 ,p_vp_crq_id => l_cr_id
1728 ,x_status_code => l_cr_ret_sts_code
1729 );
1730 END IF;
1731 END IF;
1732
1733 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1734 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1735 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1736 RAISE OKL_API.G_EXCEPTION_ERROR;
1737 END IF;
1738
1739 -- Call end_activity
1740 OKC_API.END_ACTIVITY (x_msg_count,
1741 x_msg_data );
1742
1743
1744 EXCEPTION
1745
1746 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1747 x_return_status := OKL_API.G_RET_STS_ERROR;
1748 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1749 (p_api_name => l_api_name
1750 ,p_pkg_name => G_PKG_NAME
1751 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1752 ,x_msg_count => x_msg_count
1753 ,x_msg_data => x_msg_data
1754 ,p_api_type => '_PVT'
1755 );
1756
1757 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1758 x_return_status := OKL_API.G_RET_STS_ERROR;
1759 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1760 l_api_name
1761 ,g_pkg_name
1762 ,'OKL_API.G_RET_STS_ERROR'
1763 ,x_msg_count
1764 ,x_msg_data
1765 ,'_PVT'
1766 );
1767
1768 WHEN OTHERS THEN
1769 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1770 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1771 l_api_name
1772 ,g_pkg_name
1773 ,'OTHERS'
1774 ,x_msg_count
1775 ,x_msg_data
1776 ,'_PVT'
1777 );
1778
1779 END passed_to_incomplete;
1780
1781
1782 END;