[Home] [Help]
PACKAGE BODY: APPS.OKS_RENCPY_PVT
Source
1 PACKAGE BODY OKS_RENCPY_PVT AS
2 /* $Header: OKSRCPYB.pls 120.6.12020000.2 2013/04/05 08:30:54 vgujarat ship $*/
3
4 SUBTYPE pavv_rec_type IS OKC_PRICE_ADJUSTMENT_PUB.pavv_rec_type;
5 SUBTYPE scnv_rec_type IS OKC_SECTIONS_PUB.scnv_rec_type;
6 SUBTYPE sccv_rec_type IS OKC_SECTIONS_PUB.sccv_rec_type;
7 SUBTYPE gvev_rec_type IS OKC_CONTRACT_PUB.gvev_rec_type;
8 g_chrv_rec chrv_rec_type;
9 ----------------------------------------------------------------------------
10 --PL/SQL Table to check the sections has already copied.
11 --If Yes give the new scn_id
12 ----------------------------------------------------------------------------
13 TYPE sections_rec_type IS RECORD (
14 old_scn_id NUMBER := OKC_API.G_MISS_NUM,
15 new_scn_id NUMBER := OKC_API.G_MISS_NUM);
16
17 TYPE sections_tbl_type IS TABLE OF sections_rec_type INDEX BY BINARY_INTEGER;
18 g_sections sections_tbl_type;
19
20 ----------------------------------------------------------------------------
21 --PL/SQL Table to check the party has already copied.
22 --If Yes give the new cpl_id ----Begins
23 ----------------------------------------------------------------------------
24 TYPE party_rec_type IS RECORD (
25 old_cpl_id NUMBER := OKC_API.G_MISS_NUM,
26 new_cpl_id NUMBER := OKC_API.G_MISS_NUM);
27 TYPE party_tbl_type IS TABLE OF party_rec_type INDEX BY BINARY_INTEGER;
28 g_party party_tbl_type;
29
30 ----------------------------------------------------------------------------
31 -- PL/SQL table to keep line/header id and corresponding ole_id
32 -- This table will store the following combinations
33 -- Header Id - OLE_ID for Header
34 -- Line ID - OLE_ID for the Line
35 -- To get PARENT_OLE_ID for top line, search for ID = header_id
36 -- for sub line, search for ID = Parent Line Id
37 ----------------------------------------------------------------------------
38 TYPE line_op_rec_type IS RECORD (
39 id NUMBER := OKC_API.G_MISS_NUM,
40 ole_id NUMBER := OKC_API.G_MISS_NUM);
41
42 TYPE line_op_tbl_type IS TABLE OF line_op_rec_type INDEX BY BINARY_INTEGER;
43
44 g_op_lines line_op_tbl_type;
45
46 FUNCTION Is_Number(p_string VARCHAR2) RETURN BOOLEAN IS
47 n NUMBER;
48 BEGIN
49 n := to_number(p_string);
50 RETURN TRUE;
51 EXCEPTION
52 WHEN OTHERS THEN
53 RETURN FALSE;
54 END;
55
56 ----------------------------------------------------------------------------
57 --Logic to check the sections has already copied.
58 --If Yes give the new scn_id ----Begins
59 ----------------------------------------------------------------------------
60
61 PROCEDURE add_sections(p_old_scn_id IN NUMBER,
62 p_new_scn_id IN NUMBER) IS
63 i NUMBER := 0;
64 BEGIN
65 IF g_sections.COUNT > 0 THEN
66 i := g_sections.LAST;
67 END IF;
68 g_sections(i + 1).old_scn_id := p_old_scn_id;
69 g_sections(i + 1).new_scn_id := p_new_scn_id;
70 END add_sections;
71
72 FUNCTION get_new_scn_id(p_old_scn_id IN NUMBER,
73 p_new_scn_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
74 i NUMBER := 0;
75 BEGIN
76 IF g_sections.COUNT > 0 THEN
77 i := g_sections.FIRST;
78 LOOP
79 IF g_sections(i).old_scn_id = p_old_scn_id THEN
80 p_new_scn_id := g_sections(i).new_scn_id;
81 RETURN TRUE;
82 END IF;
83 EXIT WHEN (i = g_sections.LAST);
84 i := g_sections.NEXT(i);
85 END LOOP;
86 RETURN FALSE;
87 END IF;
88 RETURN FALSE;
89 END get_new_scn_id;
90
91 ----------------------------------------------------------------------------
92 --Logic to check the party has already copied.
93 --If Yes give the new cpl_id ----Begins
94 ----------------------------------------------------------------------------
95
96 PROCEDURE add_party(p_old_cpl_id IN NUMBER,
97 p_new_cpl_id IN NUMBER) IS
98 i NUMBER := 0;
99 BEGIN
100 IF g_party.COUNT > 0 THEN
101 i := g_party.LAST;
102 END IF;
103 g_party(i + 1).old_cpl_id := p_old_cpl_id;
104 g_party(i + 1).new_cpl_id := p_new_cpl_id;
105 END add_party;
106
107 FUNCTION get_new_cpl_id(p_old_cpl_id IN NUMBER,
108 p_new_cpl_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
109 i NUMBER := 0;
110 BEGIN
111 IF g_party.COUNT > 0 THEN
112 i := g_party.FIRST;
113 LOOP
114 IF g_party(i).old_cpl_id = p_old_cpl_id THEN
115 p_new_cpl_id := g_party(i).new_cpl_id;
116 RETURN TRUE;
117 END IF;
118 EXIT WHEN (i = g_party.LAST);
119 i := g_party.NEXT(i);
120 END LOOP;
121 RETURN FALSE;
122 END IF;
123 RETURN FALSE;
124 END get_new_cpl_id;
125 ----------------------------------------------------------------------------
126 --Logic to check the party has already copied.
127 --If Yes give the new cpl_id ----Ends.
128 ----------------------------------------------------------------------------
129 ----------------------------------------------------------------------------
130 --Function specs to populate pl/sql record with database values begins
131 ----------------------------------------------------------------------------
132 FUNCTION get_atnv_rec(p_atn_id IN NUMBER,
133 x_atnv_rec OUT NOCOPY atnv_rec_type) RETURN VARCHAR2;
134 FUNCTION get_catv_rec(p_cat_id IN NUMBER,
135 x_catv_rec OUT NOCOPY catv_rec_type) RETURN VARCHAR2;
136 FUNCTION get_cimv_rec(p_cim_id IN NUMBER,
137 x_cimv_rec OUT NOCOPY cimv_rec_type) RETURN VARCHAR2;
138 FUNCTION get_cacv_rec(p_cac_id IN NUMBER,
139 x_cacv_rec OUT NOCOPY cacv_rec_type) RETURN VARCHAR2;
140 FUNCTION get_cplv_rec(p_cpl_id IN NUMBER,
141 x_cplv_rec OUT NOCOPY cplv_rec_type) RETURN VARCHAR2;
142 FUNCTION get_cpsv_rec(p_cps_id IN NUMBER,
143 x_cpsv_rec OUT NOCOPY cpsv_rec_type) RETURN VARCHAR2;
144 FUNCTION get_cgcv_rec(p_cgc_id IN NUMBER,
145 x_cgcv_rec OUT NOCOPY cgcv_rec_type) RETURN VARCHAR2;
146 FUNCTION get_cnhv_rec(p_cnh_id IN NUMBER,
147 x_cnhv_rec OUT NOCOPY cnhv_rec_type) RETURN VARCHAR2;
148 FUNCTION get_cnlv_rec(p_cnl_id IN NUMBER,
149 x_cnlv_rec OUT NOCOPY cnlv_rec_type) RETURN VARCHAR2;
150
151 FUNCTION get_klnv_rec(p_old_cle_id IN NUMBER,
152 x_klnv_rec OUT NOCOPY klnv_rec_type)
153 RETURN VARCHAR2;
154 FUNCTION get_clev_rec(p_cle_id IN NUMBER,
155 x_clev_rec OUT NOCOPY clev_rec_type) RETURN VARCHAR2;
156
157 FUNCTION get_ctcv_rec(p_ctc_id IN NUMBER,
158 x_ctcv_rec OUT NOCOPY ctcv_rec_type) RETURN VARCHAR2;
159 FUNCTION get_pavv_rec(p_pav_id IN NUMBER,
160 x_pavv_rec OUT NOCOPY pavv_rec_type) RETURN VARCHAR2;
161 FUNCTION get_scnv_rec(p_scn_id IN NUMBER,
162 x_scnv_rec OUT NOCOPY scnv_rec_type) RETURN VARCHAR2;
163 FUNCTION get_sccv_rec(p_scc_id IN NUMBER,
164 x_sccv_rec OUT NOCOPY sccv_rec_type) RETURN VARCHAR2;
165 ----------------------------------------------------------------------------
166 --Function specs to populate pl/sql record with database values ends
167 ----------------------------------------------------------------------------
168 ----------------------------------------------------------------------------
169 --Proceudre copy_sections - Makes a copy of the okc_sections and okc_section_contents.
170 ----------------------------------------------------------------------------
171 PROCEDURE copy_sections(
172 p_api_version IN NUMBER,
173 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
174 x_return_status OUT NOCOPY VARCHAR2,
175 x_msg_count OUT NOCOPY NUMBER,
176 x_msg_data OUT NOCOPY VARCHAR2,
177 p_scc_id IN NUMBER,
178 p_to_cat_id IN NUMBER,
179 p_to_chr_id IN NUMBER) IS
180
181 l_scn_id NUMBER;
182 l_scn_id_new NUMBER;
183 l_scn_id_out NUMBER;
184 l_scn_count NUMBER := 0;
185
186 l_sccv_rec sccv_rec_type;
187 x_sccv_rec sccv_rec_type;
188 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
189
190 TYPE sec_rec_type IS RECORD (
191 scn_id NUMBER := OKC_API.G_MISS_NUM);
192 TYPE sec_tbl_type IS TABLE OF sec_rec_type
193 INDEX BY BINARY_INTEGER;
194 l_sec sec_tbl_type;
195
196 CURSOR c_scc IS
197 SELECT scn_id
198 FROM okc_section_contents
199 WHERE id = p_scc_id;
200
201 CURSOR c_scn(p_scn_id IN NUMBER) IS
202 SELECT id, LEVEL
203 FROM okc_sections_b
204 CONNECT BY PRIOR scn_id = id
205 START WITH id = p_scn_id;
206
207 PROCEDURE copy_section(
208 p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2,
213 p_scn_id IN NUMBER,
214 p_to_chr_id IN NUMBER,
215 x_scn_id OUT NOCOPY NUMBER) IS
216
217
218 l_new_scn_id NUMBER;
219
220 l_scnv_rec scnv_rec_type;
221 x_scnv_rec scnv_rec_type;
222 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
223
224 BEGIN
225 x_return_status := l_return_status;
226 IF get_new_scn_id(p_scn_id, l_new_scn_id) THEN
227 x_scn_id := l_new_scn_id;
228 RAISE G_EXCEPTION_HALT_VALIDATION;
229 END IF;
230
231 l_return_status := get_scnv_rec(p_scn_id => p_scn_id,
232 x_scnv_rec => l_scnv_rec);
233
234 l_scnv_rec.chr_id := p_to_chr_id;
235
236 IF get_new_scn_id(l_scnv_rec.scn_id, l_new_scn_id) THEN
237 l_scnv_rec.scn_id := l_new_scn_id;
238 ELSE
239 l_scnv_rec.scn_id := NULL;
240 END IF;
241
242 OKC_SECTIONS_PUB.create_section(
243 p_api_version => p_api_version,
244 p_init_msg_list => p_init_msg_list,
245 x_return_status => l_return_status,
246 x_msg_count => x_msg_count,
247 x_msg_data => x_msg_data,
248 p_scnv_rec => l_scnv_rec,
249 x_scnv_rec => x_scnv_rec);
250
251 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
252 x_return_status := l_return_status;
253 RAISE G_EXCEPTION_HALT_VALIDATION;
254 END IF;
255
256 x_scn_id := x_scnv_rec.id;
257
258 add_sections(p_scn_id, x_scnv_rec.id); --adds the new section id in the global PL/SQL table.
259
260 EXCEPTION
261 WHEN G_EXCEPTION_HALT_VALIDATION THEN
262 NULL;
263 WHEN OTHERS THEN
264 -- store SQL error message on message stack for caller
265 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
266 -- notify caller of an UNEXPECTED error
267 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
268 END copy_section;
269
270 BEGIN
271
272 x_return_status := l_return_status;
273
274 OPEN c_scc;
275 FETCH c_scc INTO l_scn_id;
276 CLOSE c_scc;
277
278 FOR l_c_scn IN c_scn(l_scn_id) LOOP
279 l_sec(l_c_scn.LEVEL).scn_id := l_c_scn.id;
280 l_scn_count := l_c_scn.LEVEL;
281 END LOOP;
282
283 FOR i IN REVERSE 1 .. l_scn_count LOOP
284 copy_section (
285 p_api_version => p_api_version,
286 p_init_msg_list => p_init_msg_list,
287 x_return_status => l_return_status,
288 x_msg_count => x_msg_count,
289 x_msg_data => x_msg_data,
290 p_scn_id => l_sec(i).scn_id,
291 p_to_chr_id => p_to_chr_id,
292 x_scn_id => l_scn_id_out);
293
294 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
295 x_return_status := l_return_status;
296 RAISE G_EXCEPTION_HALT_VALIDATION;
297 END IF;
298 END LOOP;
299
300
301 l_return_status := get_sccv_rec(p_scc_id => p_scc_id,
302 x_sccv_rec => l_sccv_rec);
303
304 IF get_new_scn_id(l_scn_id, l_scn_id_new) THEN
305 l_sccv_rec.scn_id := l_scn_id_new;
306 ELSE
307 RAISE G_EXCEPTION_HALT_VALIDATION;
308 END IF;
309
310 l_sccv_rec.cat_id := p_to_cat_id;
311
312 OKC_SECTIONS_PUB.create_section_content(
313 p_api_version => p_api_version,
314 p_init_msg_list => p_init_msg_list,
315 x_return_status => l_return_status,
316 x_msg_count => x_msg_count,
317 x_msg_data => x_msg_data,
318 p_sccv_rec => l_sccv_rec,
319 x_sccv_rec => x_sccv_rec);
320
321 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
322 x_return_status := l_return_status;
323 RAISE G_EXCEPTION_HALT_VALIDATION;
324 END IF;
325
326
327 EXCEPTION
328 WHEN G_EXCEPTION_HALT_VALIDATION THEN
329 NULL;
330 WHEN OTHERS THEN
331 -- store SQL error message on message stack for caller
332 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
333 -- notify caller of an UNEXPECTED error
334 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
335 END copy_sections;
336
337 --------------------------------------------------------------------------
338 --Proceudre copy_accesses - Makes a copy of the okc_k_accesses.
339 --------------------------------------------------------------------------
340 PROCEDURE copy_accesses(
341 p_api_version IN NUMBER,
342 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
343 x_return_status OUT NOCOPY VARCHAR2,
344 x_msg_count OUT NOCOPY NUMBER,
345 x_msg_data OUT NOCOPY VARCHAR2,
346 p_from_chr_id IN NUMBER,
347 p_to_chr_id IN NUMBER) IS
348
349 l_cacv_rec cacv_rec_type;
350 x_cacv_rec cacv_rec_type;
351 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
352
353 CURSOR c_access IS
354 SELECT id
355 FROM okc_k_accesses_v
356 WHERE chr_id = p_from_chr_id;
357
358 BEGIN
359 x_return_status := l_return_status;
360 FOR l_c_access IN c_access LOOP
361 l_return_status := get_cacv_rec(p_cac_id => l_c_access.id,
362 x_cacv_rec => l_cacv_rec);
363 l_cacv_rec.chr_id := p_to_chr_id;
364
365 OKC_CONTRACT_PUB.create_contract_access(
366 p_api_version => p_api_version,
367 p_init_msg_list => p_init_msg_list,
368 x_return_status => l_return_status,
369 x_msg_count => x_msg_count,
370 x_msg_data => x_msg_data,
371 p_cacv_rec => l_cacv_rec,
372 x_cacv_rec => x_cacv_rec);
373
374 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
375 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
376 x_return_status := l_return_status;
377 RAISE G_EXCEPTION_HALT_VALIDATION;
378 ELSE
379 x_return_status := l_return_status;
380 END IF;
381 END IF;
382 END LOOP;
383
384 EXCEPTION
385 WHEN G_EXCEPTION_HALT_VALIDATION THEN
386 NULL;
387 WHEN OTHERS THEN
388 -- store SQL error message on message stack for caller
389 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
390 -- notify caller of an UNEXPECTED error
391 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
392
393 END copy_accesses;
394
395 ----------------------------------------------------------------------------
396 --Proceudre copy_processes - Makes a copy of the okc_k_processes.
397 ----------------------------------------------------------------------------
398 PROCEDURE copy_processes(
399 p_api_version IN NUMBER,
400 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
401 x_return_status OUT NOCOPY VARCHAR2,
402 x_msg_count OUT NOCOPY NUMBER,
403 x_msg_data OUT NOCOPY VARCHAR2,
404 p_from_chr_id IN NUMBER,
405 p_to_chr_id IN NUMBER) IS
406
407 l_cpsv_rec cpsv_rec_type;
408 x_cpsv_rec cpsv_rec_type;
409 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
410
411 CURSOR c_process IS
412 SELECT id
413 FROM okc_k_processes
414 WHERE chr_id = p_from_chr_id;
415
416 BEGIN
417 x_return_status := l_return_status;
418 FOR l_c_process IN c_process LOOP
419 l_return_status := get_cpsv_rec(p_cps_id => l_c_process.id,
420 x_cpsv_rec => l_cpsv_rec);
421 l_cpsv_rec.chr_id := p_to_chr_id;
422 l_cpsv_rec.process_id := NULL;
423
424 OKC_CONTRACT_PUB.create_contract_process(
425 p_api_version => p_api_version,
426 p_init_msg_list => p_init_msg_list,
427 x_return_status => l_return_status,
428 x_msg_count => x_msg_count,
429 x_msg_data => x_msg_data,
430 p_cpsv_rec => l_cpsv_rec,
431 x_cpsv_rec => x_cpsv_rec);
432
433 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
434 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
435 x_return_status := l_return_status;
436 RAISE G_EXCEPTION_HALT_VALIDATION;
437 ELSE
438 x_return_status := l_return_status;
439 END IF;
440 END IF;
441 END LOOP;
442
443 EXCEPTION
444 WHEN G_EXCEPTION_HALT_VALIDATION THEN
445 NULL;
446 WHEN OTHERS THEN
447 -- store SQL error message on message stack for caller
448 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
449 -- notify caller of an UNEXPECTED error
450 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
451
452 END copy_processes;
453
454 ----------------------------------------------------------------------------
455 --Proceudre copy_grpings - Makes a copy of the okc_k_grpings.
456 ----------------------------------------------------------------------------
457 PROCEDURE copy_grpings(
458 p_api_version IN NUMBER,
459 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
460 x_return_status OUT NOCOPY VARCHAR2,
461 x_msg_count OUT NOCOPY NUMBER,
462 x_msg_data OUT NOCOPY VARCHAR2,
463 p_from_chr_id IN NUMBER,
464 p_to_chr_id IN NUMBER) IS
465
466 l_cgcv_rec cgcv_rec_type;
467 x_cgcv_rec cgcv_rec_type;
468 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
469
470 CURSOR c_grpings IS
471 SELECT cgcv.id
472 FROM okc_k_grpings_v cgcv,
473 okc_k_groups_b cgpv
474 WHERE cgcv.included_chr_id = p_from_chr_id
475 AND cgcv.cgp_parent_id = cgpv.id
476 AND (cgpv.public_yn = 'Y' OR cgpv.user_id = fnd_global.user_id);
477
478 BEGIN
479 x_return_status := l_return_status;
480 FOR l_c_grpings IN c_grpings LOOP
481 l_return_status := get_cgcv_rec(p_cgc_id => l_c_grpings.id,
482 x_cgcv_rec => l_cgcv_rec);
483 l_cgcv_rec.included_chr_id := p_to_chr_id;
484
485 OKC_CONTRACT_GROUP_PUB.create_contract_grpngs(
486 p_api_version => p_api_version,
487 p_init_msg_list => p_init_msg_list,
488 x_return_status => l_return_status,
489 x_msg_count => x_msg_count,
490 x_msg_data => x_msg_data,
491 p_cgcv_rec => l_cgcv_rec,
492 x_cgcv_rec => x_cgcv_rec);
493
494 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
495 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
496 x_return_status := l_return_status;
497 RAISE G_EXCEPTION_HALT_VALIDATION;
498 ELSE
499 x_return_status := l_return_status;
500 END IF;
501 END IF;
502 END LOOP;
503
504 EXCEPTION
505 WHEN G_EXCEPTION_HALT_VALIDATION THEN
506 NULL;
507 WHEN OTHERS THEN
508 -- store SQL error message on message stack for caller
509 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
510 -- notify caller of an UNEXPECTED error
511 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
512
513 END copy_grpings;
514
515 --------------------------------------------------------------------------
516 --Proceudre copy_governances - Makes a copy of the okc_governances.
517 --------------------------------------------------------------------------
518 PROCEDURE copy_governances(
519 p_api_version IN NUMBER,
520 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
521 x_return_status OUT NOCOPY VARCHAR2,
522 x_msg_count OUT NOCOPY NUMBER,
523 x_msg_data OUT NOCOPY VARCHAR2,
524 p_from_chr_id IN NUMBER,
525 p_to_chr_id IN NUMBER) IS
526
527 l_gvev_rec gvev_rec_type;
528 x_gvev_rec gvev_rec_type;
529 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
530
531 CURSOR c_governances IS
532 SELECT id
533 FROM okc_governances
534 WHERE dnz_chr_id = p_from_chr_id
535 AND cle_id IS NULL;
536
537 ----------------------------------------------------------------------------
538 --Function to populate the contract governance record to be copied.
539 ----------------------------------------------------------------------------
540 FUNCTION get_gvev_rec(p_gve_id IN NUMBER,
541 x_gvev_rec OUT NOCOPY gvev_rec_type)
542 RETURN VARCHAR2 IS
543 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
544 l_no_data_found BOOLEAN := TRUE;
545
546 CURSOR c_gvev_rec IS
547 SELECT
548 DNZ_CHR_ID,
549 ISA_AGREEMENT_ID,
550 CHR_ID,
551 CLE_ID,
552 CHR_ID_REFERRED,
553 CLE_ID_REFERRED,
554 COPIED_ONLY_YN
555 FROM OKC_GOVERNANCES
556 WHERE ID = p_gve_id;
557 BEGIN
558 OPEN c_gvev_rec;
559 FETCH c_gvev_rec
560 INTO x_gvev_rec.DNZ_CHR_ID,
561 x_gvev_rec.ISA_AGREEMENT_ID,
562 x_gvev_rec.CHR_ID,
563 x_gvev_rec.CLE_ID,
564 x_gvev_rec.CHR_ID_REFERRED,
565 x_gvev_rec.CLE_ID_REFERRED,
566 x_gvev_rec.COPIED_ONLY_YN;
567
568 l_no_data_found := c_gvev_rec%NOTFOUND;
569 CLOSE c_gvev_rec;
570 IF l_no_data_found THEN
571 l_return_status := OKC_API.G_RET_STS_ERROR;
572 RETURN(l_return_status);
573 ELSE
574 RETURN(l_return_status);
575 END IF;
576 EXCEPTION
577 WHEN OTHERS THEN
578 -- store SQL error message on message stack for caller
579 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
580 -- notify caller of an UNEXPECTED error
581 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
582 RETURN(l_return_status);
583
584 END get_gvev_rec;
585 BEGIN
586 x_return_status := l_return_status;
587 FOR l_c_governances IN c_governances LOOP
588 l_return_status := get_gvev_rec(p_gve_id => l_c_governances.id,
589 x_gvev_rec => l_gvev_rec);
590 l_gvev_rec.chr_id := p_to_chr_id;
591 l_gvev_rec.dnz_chr_id := p_to_chr_id;
592
593 OKC_CONTRACT_PUB.create_governance(
594 p_api_version => p_api_version,
595 p_init_msg_list => p_init_msg_list,
596 x_return_status => l_return_status,
597 x_msg_count => x_msg_count,
598 x_msg_data => x_msg_data,
599 p_gvev_rec => l_gvev_rec,
600 x_gvev_rec => x_gvev_rec);
601
602 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
603 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
604 x_return_status := l_return_status;
605 RAISE G_EXCEPTION_HALT_VALIDATION;
606 ELSE
607 x_return_status := l_return_status;
608 END IF;
609 END IF;
610 END LOOP;
611
612 EXCEPTION
613 WHEN G_EXCEPTION_HALT_VALIDATION THEN
614 NULL;
615 WHEN OTHERS THEN
616 -- store SQL error message on message stack for caller
617 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
618 -- notify caller of an UNEXPECTED error
619 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
620
621 END copy_governances;
622 ----------------------------------------------------------------------------
623 --Proceudre copy_articles - Makes a copy of the articles.
624 ----------------------------------------------------------------------------
625 PROCEDURE copy_articles(
626 p_api_version IN NUMBER,
627 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
628 x_return_status OUT NOCOPY VARCHAR2,
629 x_msg_count OUT NOCOPY NUMBER,
630 x_msg_data OUT NOCOPY VARCHAR2,
631 p_cat_id IN NUMBER,
632 p_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
633 p_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
634 x_cat_id OUT NOCOPY NUMBER) IS
635
636 l_catv_rec catv_rec_type;
637 x_catv_rec catv_rec_type;
638 l_atnv_rec atnv_rec_type;
639 x_atnv_rec atnv_rec_type;
640
641 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
642 l_cle_id NUMBER := OKC_API.G_MISS_NUM;
643 l_new_rul_id NUMBER := OKC_API.G_MISS_NUM;
644
645 CURSOR c_dnz_chr_id(p_id IN NUMBER) IS
646 SELECT dnz_chr_id
647 FROM okc_k_lines_b
648 WHERE id = p_id;
649
650 CURSOR c_atn(p_id IN NUMBER) IS
651 SELECT id
652 FROM okc_article_trans
653 WHERE cat_id = p_id;
654
655 CURSOR c_scc IS
656 SELECT id
657 FROM okc_section_contents
658 WHERE cat_id = p_cat_id;
659
660 BEGIN
661 x_return_status := l_return_status;
662 l_return_status := get_catv_rec(p_cat_id => p_cat_id,
663 x_catv_rec => l_catv_rec);
664
665 IF p_chr_id IS NULL OR p_chr_id = OKC_API.G_MISS_NUM THEN
666 OPEN c_dnz_chr_id(p_cle_id);
667 FETCH c_dnz_chr_id INTO l_catv_rec.dnz_chr_id;
668 CLOSE c_dnz_chr_id;
669 ELSE
670 l_catv_rec.dnz_chr_id := p_chr_id;
671 END IF;
672
673 l_catv_rec.chr_id := p_chr_id;
674 l_catv_rec.cle_id := p_cle_id;
675
676 OKC_K_ARTICLE_PUB.create_k_article(
677 p_api_version => p_api_version,
678 p_init_msg_list => p_init_msg_list,
679 x_return_status => l_return_status,
680 x_msg_count => x_msg_count,
681 x_msg_data => x_msg_data,
682 p_catv_rec => l_catv_rec,
683 x_catv_rec => x_catv_rec);
684
685 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
686 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
687 x_return_status := l_return_status;
688 RAISE G_EXCEPTION_HALT_VALIDATION;
689 ELSE
690 x_return_status := l_return_status;
691 END IF;
692 END IF;
693
694 FOR l_c_atn IN c_atn(l_catv_rec.id)
695 LOOP
696 l_return_status := get_atnv_rec(p_atn_id => l_c_atn.id,
697 x_atnv_rec => l_atnv_rec);
698 l_atnv_rec.rul_id := l_new_rul_id;
699 l_atnv_rec.cat_id := x_catv_rec.id;
700 l_atnv_rec.dnz_chr_id := x_catv_rec.dnz_chr_id;
701
702 OKC_K_ARTICLE_PUB.create_article_translation(
703 p_api_version => p_api_version,
704 p_init_msg_list => p_init_msg_list,
705 x_return_status => l_return_status,
706 x_msg_count => x_msg_count,
707 x_msg_data => x_msg_data,
708 p_atnv_rec => l_atnv_rec,
709 x_atnv_rec => x_atnv_rec);
710
711 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
712 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
713 x_return_status := l_return_status;
714 RAISE G_EXCEPTION_HALT_VALIDATION;
715 ELSE
716 x_return_status := l_return_status;
717 END IF;
718 END IF;
719
720 -- END IF;
721 END LOOP;
722
723 x_cat_id := x_catv_rec.id; -- passes the new generated id to the caller.
724
725 FOR l_c_scc IN c_scc LOOP
726 copy_sections (
727 p_api_version => p_api_version,
728 p_init_msg_list => p_init_msg_list,
729 x_return_status => l_return_status,
730 x_msg_count => x_msg_count,
731 x_msg_data => x_msg_data,
732 p_scc_id => l_c_scc.id,
733 p_to_cat_id => x_catv_rec.id,
734 p_to_chr_id => x_catv_rec.dnz_chr_id);
735
736 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
737 x_return_status := l_return_status;
738 RAISE G_EXCEPTION_HALT_VALIDATION;
739 END IF;
740 END LOOP;
741
742 EXCEPTION
743 WHEN G_EXCEPTION_HALT_VALIDATION THEN
744 NULL;
745 WHEN OTHERS THEN
746 -- store SQL error message on message stack for caller
747 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
748 -- notify caller of an UNEXPECTED error
749 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
750
751 END copy_articles;
752
753
754
755 ----------------------------------------------------------------------------
756 --Proceudre copy_price_att_values - Makes a copy of the price attribute values.
757 ----------------------------------------------------------------------------
758 PROCEDURE copy_price_att_values(
759 p_api_version IN NUMBER,
760 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
761 x_return_status OUT NOCOPY VARCHAR2,
762 x_msg_count OUT NOCOPY NUMBER,
763 x_msg_data OUT NOCOPY VARCHAR2,
764 p_pav_id IN NUMBER,
765 p_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
766 p_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
767 x_pav_id OUT NOCOPY NUMBER) IS
768
769 l_pavv_rec pavv_rec_type;
770 x_pavv_rec pavv_rec_type;
771
772 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
773 l_cle_id NUMBER := OKC_API.G_MISS_NUM;
774
775 BEGIN
776 x_return_status := l_return_status;
777 l_return_status := get_pavv_rec(p_pav_id => p_pav_id,
778 x_pavv_rec => l_pavv_rec);
779
780 l_pavv_rec.chr_id := p_chr_id;
781 l_pavv_rec.cle_id := p_cle_id;
782
783 OKC_PRICE_ADJUSTMENT_PUB.create_price_att_value(
784 p_api_version => p_api_version,
785 p_init_msg_list => p_init_msg_list,
786 x_return_status => l_return_status,
787 x_msg_count => x_msg_count,
788 x_msg_data => x_msg_data,
789 p_pavv_rec => l_pavv_rec,
790 x_pavv_rec => x_pavv_rec);
791
792 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
793 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
794 x_return_status := l_return_status;
795 RAISE G_EXCEPTION_HALT_VALIDATION;
796 ELSE
797 x_return_status := l_return_status;
798 END IF;
799 END IF;
800
801 x_pav_id := x_pavv_rec.id; -- passes the new generated id to the caller.
802
803 EXCEPTION
804 WHEN G_EXCEPTION_HALT_VALIDATION THEN
805 NULL;
806 WHEN OTHERS THEN
807 -- store SQL error message on message stack for caller
808 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
809 -- notify caller of an UNEXPECTED error
810 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
811
812 END copy_price_att_values;
813
814 ----------------------------------------------------------------------------
815 --Proceudre copy_party_roles - Makes a copy of the party_roles.
816 ----------------------------------------------------------------------------
817 PROCEDURE copy_party_roles(
818 p_api_version IN NUMBER,
819 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
820 x_return_status OUT NOCOPY VARCHAR2,
821 x_msg_count OUT NOCOPY NUMBER,
822 x_msg_data OUT NOCOPY VARCHAR2,
823 p_cpl_id IN NUMBER,
824 p_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
825 p_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
826 P_rle_code IN VARCHAR2,
827 x_cpl_id OUT NOCOPY NUMBER) IS
828
829 l_cplv_rec cplv_rec_type;
830 x_cplv_rec cplv_rec_type;
831 l_ctcv_rec ctcv_rec_type;
832 x_ctcv_rec ctcv_rec_type;
833
834 l_party_name VARCHAR2(200);
835 l_party_desc VARCHAR2(2000);
836 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
837 l_cle_id NUMBER := OKC_API.G_MISS_NUM;
838
839 CURSOR c_dnz_chr_id(p_id IN NUMBER) IS
840 SELECT dnz_chr_id
841 FROM okc_k_lines_b
842 WHERE id = p_id;
843
844 CURSOR c_ctcv IS
845 SELECT id
846 FROM okc_contacts
847 WHERE cpl_id = p_cpl_id;
848
849 BEGIN
850 x_return_status := l_return_status;
851 l_return_status := get_cplv_rec(p_cpl_id => p_cpl_id,
852 x_cplv_rec => l_cplv_rec);
853
854 IF p_chr_id IS NULL OR p_chr_id = OKC_API.G_MISS_NUM THEN
855 OPEN c_dnz_chr_id(p_cle_id);
856 FETCH c_dnz_chr_id INTO l_cplv_rec.dnz_chr_id;
857 CLOSE c_dnz_chr_id;
858 ELSE
859 l_cplv_rec.dnz_chr_id := p_chr_id;
860 END IF;
861
862 l_cplv_rec.chr_id := p_chr_id;
863 l_cplv_rec.cle_id := p_cle_id;
864 IF p_rle_code IS NOT NULL THEN
865 l_cplv_rec.rle_code := p_rle_code;
866 END IF;
867
868 OKC_CONTRACT_PARTY_PUB.create_k_party_role(
869 p_api_version => p_api_version,
870 p_init_msg_list => p_init_msg_list,
871 x_return_status => l_return_status,
872 x_msg_count => x_msg_count,
873 x_msg_data => x_msg_data,
874 p_cplv_rec => l_cplv_rec,
875 x_cplv_rec => x_cplv_rec);
876
877 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
878 x_return_status := l_return_status;
879 RAISE G_EXCEPTION_HALT_VALIDATION;
880 END IF;
881
882 x_cpl_id := x_cplv_rec.id; -- passes the new generated id to the caller.
883
884 --stores the new rul_id in a global pl/sql table.
885 add_party(l_cplv_rec.id, x_cplv_rec.id);
886
887
888 FOR l_c_ctcv IN c_ctcv LOOP
889 l_return_status := get_ctcv_rec(p_ctc_id => l_c_ctcv.id,
890 x_ctcv_rec => l_ctcv_rec);
891
892 l_ctcv_rec.dnz_chr_id := l_cplv_rec.dnz_chr_id;
893 l_ctcv_rec.cpl_id := x_cplv_rec.id;
894
895 OKC_CONTRACT_PARTY_PUB.create_contact(
896 p_api_version => p_api_version,
897 p_init_msg_list => p_init_msg_list,
898 x_return_status => l_return_status,
899 x_msg_count => x_msg_count,
900 x_msg_data => x_msg_data,
901 p_ctcv_rec => l_ctcv_rec,
902 x_ctcv_rec => x_ctcv_rec);
903
904 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
905 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
906 x_return_status := l_return_status;
907 RAISE G_EXCEPTION_HALT_VALIDATION;
908 ELSE
909 x_return_status := OKC_API.G_RET_STS_WARNING;
910 okc_util.get_name_desc_from_jtfv(
911 p_object_code => x_cplv_rec.jtot_object1_code,
912 p_id1 => x_cplv_rec.object1_id1,
913 p_id2 => x_cplv_rec.object1_id2,
914 x_name => l_party_name,
915 x_description => l_party_desc);
916
917 OKC_API.set_message(G_APP_NAME, 'OKC_CONTACT_NOT_COPIED', 'PARTY_NAME', l_party_name);
918 END IF;
919 END IF;
920 END LOOP;
921
922 EXCEPTION
923 WHEN G_EXCEPTION_HALT_VALIDATION THEN
924 NULL;
925 WHEN OTHERS THEN
926 -- store SQL error message on message stack for caller
927 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
928 -- notify caller of an UNEXPECTED error
929 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
930
931 END copy_party_roles;
932
933 ----------------------------------------------------------------------------
934 --Proceudre copy_rules - Makes a copy of all the rules for a given line
935 --parameters :
936 -- p_old_cle_id => for old okc line id being renewed
937 -- p_cle_id => id for new created okc line
938 -- p_chr_id => contract header id
939 ----------------------------------------------------------------------------
940 PROCEDURE copy_rules(
941 p_api_version IN NUMBER,
942 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
943 x_return_status OUT NOCOPY VARCHAR2,
944 x_msg_count OUT NOCOPY NUMBER,
945 x_msg_data OUT NOCOPY VARCHAR2,
946 p_old_cle_id IN NUMBER,
947 p_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
948 p_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
949 p_cust_acct_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
950 p_bill_to_site_use_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
951 p_to_template_yn IN VARCHAR2) IS
952
953 l_api_name CONSTANT VARCHAR2(30) := 'COPY_RULES';
954 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
955
956 l_klnv_rec klnv_rec_type;
957 x_klnv_rec klnv_rec_type;
958
959 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
960 l_dnz_chr_id NUMBER;
961 l_trx_ext_id NUMBER;
962
963 CURSOR c_dnz_chr_id(p_id IN NUMBER) IS
964 SELECT dnz_chr_id
965 FROM okc_k_lines_b
966 WHERE id = p_id;
967
968 -- bug 5139719
969 CURSOR cur_hdr_uom IS
970 SELECT price_uom
971 FROM oks_k_headers_b
972 WHERE chr_id = p_chr_id;
973
974 l_price_uom oks_k_headers_b.price_uom%TYPE;
975 -- end added bug 5139719
976
977 BEGIN
978
979 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
980 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','p_old_cle_id='||p_old_cle_id||' ,p_cle_id='||p_cle_id||' ,p_chr_id='||p_chr_id);
981 END IF;
982
983 x_return_status := l_return_status;
984 IF FND_API.to_boolean(p_init_msg_list ) THEN
985 FND_MSG_PUB.initialize;
986 END IF;
987
988 IF p_chr_id IS NULL OR p_chr_id = OKC_API.G_MISS_NUM THEN
989 OPEN c_dnz_chr_id(p_cle_id);
990 FETCH c_dnz_chr_id INTO l_dnz_chr_id;
991 CLOSE c_dnz_chr_id;
992 ELSE
993 l_dnz_chr_id := p_chr_id;
994 END IF;
995
996 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
997 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.get_line_details', 'calling get_klnv_rec' );
998 END IF;
999
1000 l_return_status := get_klnv_rec(
1001 p_old_cle_id => p_old_cle_id,
1002 x_klnv_rec => l_klnv_rec);
1003
1004 l_klnv_rec.cle_id := p_cle_id;
1005 l_klnv_rec.dnz_chr_id := l_dnz_chr_id;
1006 l_klnv_rec.orig_system_id1 := l_klnv_rec.id;
1007 l_klnv_rec.orig_system_reference1 := 'COPY';
1008 l_klnv_rec.orig_system_source_code := 'OKC_LINE';
1009 l_klnv_rec.cust_po_number := NULL; -- null out payment instructions
1010 l_klnv_rec.cust_po_number_req_yn := NULL;
1011
1012 -- bug 5139719
1013 OPEN cur_hdr_uom;
1014 FETCH cur_hdr_uom INTO l_price_uom;
1015 CLOSE cur_hdr_uom;
1016 l_klnv_rec.price_uom := l_price_uom;
1017 -- end added bug 5139719
1018
1019 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1020 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_oks_line', 'calling OKS_CONTRACT_LINE_PUB.create_line' );
1021 END IF;
1022
1023 OKS_CONTRACT_LINE_PUB.create_line(
1024 p_api_version => 1.0,
1025 p_init_msg_list => OKC_API.G_FALSE,
1026 x_return_status => l_return_status,
1027 x_msg_count => x_msg_count,
1028 x_msg_data => x_msg_data,
1029 p_klnv_rec => l_klnv_rec,
1030 x_klnv_rec => x_klnv_rec,
1031 p_validate_yn => 'N');
1032
1033 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1034 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_oks_line', 'after call to OKS_CONTRACT_LINE_PUB.create_line, l_return_status='||l_return_status);
1035 END IF;
1036
1037 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1038 x_return_status := l_return_status;
1039 RAISE G_EXCEPTION_HALT_VALIDATION;
1040 ELSE
1041 x_return_status := l_return_status;
1042 END IF;
1043
1044 --add call to copy trxn_extension_id
1045 IF (l_klnv_rec.trxn_extension_id IS NOT NULL) THEN
1046
1047 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1048 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.copy_cc', 'calling create_tansaction_extension, p_order_id='||p_cle_id||' ,p_old_trx_ext_id='||l_klnv_rec.trxn_extension_id||
1049 ' ,p_cust_acct_id='||p_cust_acct_id||' ,p_bill_to_site_use_id='||p_bill_to_site_use_id);
1050 END IF;
1051
1052 create_trxn_extn(
1053 p_api_version => 1.0,
1054 p_init_msg_list => OKC_API.G_FALSE,
1055 x_return_status => l_return_status,
1056 x_msg_count => x_msg_count,
1057 x_msg_data => x_msg_data,
1058 p_old_trx_ext_id => l_klnv_rec.trxn_extension_id,
1059 p_order_id => p_cle_id,
1060 p_cust_acct_id => p_cust_acct_id,
1061 p_bill_to_site_use_id => p_bill_to_site_use_id,
1062 x_trx_ext_id => l_trx_ext_id);
1063
1064 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1065 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.copy_cc', 'after call to create_tansaction_extension, x_return_status='||l_return_status||' ,x_trx_ext_id='||l_trx_ext_id);
1066 END IF;
1067
1068 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1069 x_return_status := l_return_status;
1070 RAISE G_EXCEPTION_HALT_VALIDATION;
1071 ELSE
1072 x_return_status := l_return_status;
1073 END IF;
1074
1075 UPDATE oks_k_lines_b SET
1076 trxn_extension_id = l_trx_ext_id
1077 WHERE cle_id = p_cle_id;
1078
1079 END IF;
1080
1081 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1082 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_return_status='||x_return_status);
1083 END IF;
1084
1085 EXCEPTION
1086 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1087 NULL;
1088 WHEN OTHERS THEN
1089 -- store SQL error message on message stack for caller
1090 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
1091 -- notify caller of an UNEXPECTED error
1092 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1093
1094 IF(FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level)THEN
1095 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name||'.end_other_error','x_return_status='||x_return_status);
1096 END IF;
1097
1098 END copy_rules;
1099
1100 ----------------------------------------------------------------------------
1101 --Proceudre copy_items
1102 ----------------------------------------------------------------------------
1103 PROCEDURE copy_items(
1104 p_api_version IN NUMBER,
1105 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1106 x_return_status OUT NOCOPY VARCHAR2,
1107 x_msg_count OUT NOCOPY NUMBER,
1108 x_msg_data OUT NOCOPY VARCHAR2,
1109 p_from_cle_id IN NUMBER,
1110 p_copy_reference IN VARCHAR2 DEFAULT 'COPY',
1111 p_to_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM) IS
1112
1113 l_api_name CONSTANT VARCHAR2(30) := 'COPY_ITEMS';
1114 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1115
1116 l_cimv_rec cimv_rec_type;
1117 x_cimv_rec cimv_rec_type;
1118
1119 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1120 l_dnz_chr_id NUMBER := OKC_API.G_MISS_NUM;
1121 l_price_level_ind VARCHAR2(20);
1122 l_item_name VARCHAR2(2000);
1123 l_item_desc VARCHAR2(2000);
1124
1125 CURSOR c_dnz_chr_id IS
1126 SELECT dnz_chr_id, price_level_ind
1127 FROM okc_k_lines_b
1128 WHERE id = p_to_cle_id;
1129
1130 CURSOR c_cimv IS
1131 SELECT id
1132 FROM okc_k_items
1133 WHERE cle_id = p_from_cle_id;
1134
1135 BEGIN
1136
1137 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1138 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin', 'p_from_cle_id='||p_from_cle_id||' ,p_to_cle_id='||p_to_cle_id||' ,p_copy_reference='||p_copy_reference);
1139 END IF;
1140
1141 x_return_status := l_return_status;
1142 IF FND_API.to_boolean(p_init_msg_list ) THEN
1143 FND_MSG_PUB.initialize;
1144 END IF;
1145
1146 OPEN c_dnz_chr_id;
1147 FETCH c_dnz_chr_id INTO l_dnz_chr_id, l_price_level_ind;
1148 CLOSE c_dnz_chr_id;
1149
1150 FOR l_c_cimv IN c_cimv LOOP
1151 l_return_status := get_cimv_rec(p_cim_id => l_c_cimv.id,
1152 x_cimv_rec => l_cimv_rec);
1153
1154 l_cimv_rec.cle_id := p_to_cle_id;
1155 l_cimv_rec.dnz_chr_id := l_dnz_chr_id;
1156
1157 IF p_copy_reference = 'REFERENCE' THEN
1158 l_cimv_rec.cle_id_for := p_from_cle_id;
1159 l_cimv_rec.chr_id := NULL;
1160 ELSE
1161 l_cimv_rec.cle_id_for := NULL;
1162 END IF;
1163
1164 IF l_price_level_ind = 'N' THEN
1165 l_cimv_rec.priced_item_yn := 'N';
1166 END IF;
1167
1168
1169 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1170 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.item_details','l_dnz_chr_id='|| l_dnz_chr_id||' ,object1_id1='||l_cimv_rec.OBJECT1_ID1||' ,object1_id2='||l_cimv_rec.OBJECT1_ID2||
1171 ' ,jtot_object1_code='||l_cimv_rec.JTOT_OBJECT1_CODE||' ,uom_code='|| l_cimv_rec.UOM_CODE||' ,exception_yn='||l_cimv_rec.EXCEPTION_YN||' ,number_of_items='||l_cimv_rec.NUMBER_OF_ITEMS||' ,priced_item_yn='||l_cimv_rec.PRICED_ITEM_YN);
1172 END IF;
1173
1174 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1175 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_item','calling OKC_CONTRACT_ITEM_PUB.create_contract_item');
1176 END IF;
1177
1178 OKC_CONTRACT_ITEM_PUB.create_contract_item(
1179 p_api_version => p_api_version,
1180 p_init_msg_list => OKC_API.G_FALSE,
1181 x_return_status => l_return_status,
1182 x_msg_count => x_msg_count,
1183 x_msg_data => x_msg_data,
1184 p_cimv_rec => l_cimv_rec,
1185 x_cimv_rec => x_cimv_rec);
1186
1187 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1188 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_item','after call to OKC_CONTRACT_ITEM_PUB.create_contract_item, l_return_status='||l_return_status);
1189 END IF;
1190
1191 x_return_status := l_return_status;
1192 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1193 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1194 x_return_status := l_return_status;
1195 RAISE G_EXCEPTION_HALT_VALIDATION;
1196 ELSE
1197 okc_util.get_name_desc_from_jtfv(p_object_code => l_cimv_rec.jtot_object1_code,
1198 p_id1 => l_cimv_rec.object1_id1,
1199 p_id2 => l_cimv_rec.object1_id2,
1200 x_name => l_item_name,
1201 x_description => l_item_desc);
1202
1203 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1204 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_item_error','okc_util.get_name_desc_from_jtfv l_item_name='|| l_item_name);
1205 END IF;
1206
1207 OKC_API.set_message(G_APP_NAME, 'OKC_ITEM_NOT_COPIED', 'ITEM_NAME', l_item_name);
1208
1209 x_return_status := l_return_status;
1210
1211 END IF;
1212 END IF;
1213 END LOOP;
1214
1215 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1216 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_return_status='||x_return_status);
1217 END IF;
1218
1219 EXCEPTION
1220 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1221 NULL;
1222 WHEN OTHERS THEN
1223 -- store SQL error message on message stack for caller
1224 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
1225 -- notify caller of an UNEXPECTED error
1226 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1227
1228 IF(FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level)THEN
1229 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name||'.end_other_error','x_return_status='||x_return_status);
1230 END IF;
1231
1232 END copy_items;
1233
1234 --
1235 -- Procedure to set attachement session variables if they are null
1236 -- Currently set only set for OKCAUDET and OKSAUDET
1237 --
1238 -- If want to get rid of this hard coding, COPY should add
1239 -- parameters and user should pass attachement_funtion_name
1240 -- and attachment_funtion_type
1241 --
1242 PROCEDURE Set_Attach_Session_Vars(p_chr_id NUMBER) IS
1243 l_app_id NUMBER;
1244 CURSOR l_chr_csr IS
1245 SELECT application_id
1246 FROM okc_k_headers_b
1247 WHERE id = p_chr_id;
1248 BEGIN
1249 IF (p_chr_id IS NOT NULL AND
1250 FND_ATTACHMENT_UTIL_PKG.function_name IS NULL
1251 )
1252 THEN
1253 OPEN l_chr_csr;
1254 FETCH l_chr_csr INTO l_app_id;
1255 CLOSE l_chr_csr;
1256 IF (l_app_id = 510) THEN
1257 FND_ATTACHMENT_UTIL_PKG.function_name := 'OKCAUDET';
1258 FND_ATTACHMENT_UTIL_PKG.function_type := 'O';
1259 ELSIF (l_app_id = 515) THEN
1260 FND_ATTACHMENT_UTIL_PKG.function_name := 'OKSAUDET';
1261 FND_ATTACHMENT_UTIL_PKG.function_type := 'O';
1262 END IF;
1263 END IF;
1264 END;
1265
1266 ----------------------------------------------------------------------------
1267 -- Function to return the major version of the contract
1268 -- Major version is required to while copying attachments for
1269 -- header and line
1270 ----------------------------------------------------------------------------
1271 FUNCTION Get_Major_Version(p_chr_id NUMBER) RETURN VARCHAR2 IS
1272
1273 CURSOR l_cvm_csr IS
1274 SELECT to_char(major_version)
1275 FROM okc_k_vers_numbers
1276 WHERE chr_id = p_chr_id;
1277
1278 x_from_version FND_ATTACHED_DOCUMENTS.PK2_VALUE%TYPE := NULL;
1279
1280 BEGIN
1281 OPEN l_cvm_csr;
1282 FETCH l_cvm_csr INTO x_from_version;
1283 CLOSE l_cvm_csr;
1284
1285 RETURN x_from_version;
1286 EXCEPTION
1287 WHEN OTHERS THEN
1288 RETURN OKC_API.G_RET_STS_UNEXP_ERROR;
1289
1290 END Get_Major_Version;
1291
1292 ----------------------------------------------------------------------------
1293 -- Proceudre copy_contract_line will copy all the attributes of line i.e, rules
1294 -- articles, counters etc.Will copy every attr. of old line to new line
1295 -- Parameters :
1296 -- p_from_cle_id => old line id
1297 -- p_from_chr_id => old header id
1298 -- p_to_cle_id => new line id
1299 -- p_to_chr_id => new header id
1300
1301 ----------------------------------------------------------------------------
1302 PROCEDURE copy_contract_line(
1303 p_api_version IN NUMBER,
1304 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1305 x_return_status OUT NOCOPY VARCHAR2,
1306 x_msg_count OUT NOCOPY NUMBER,
1307 x_msg_data OUT NOCOPY VARCHAR2,
1308 p_from_cle_id IN NUMBER,
1309 p_from_chr_id IN NUMBER,
1310 p_to_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
1311 p_to_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
1312 p_lse_id IN NUMBER,
1313 p_to_template_yn IN VARCHAR2,
1314 p_copy_reference IN VARCHAR2 DEFAULT 'COPY',
1315 p_copy_line_party_yn IN VARCHAR2,
1316 p_renew_ref_yn IN VARCHAR2,
1317 p_need_conversion IN VARCHAR2 DEFAULT 'N',
1318 x_cle_id OUT NOCOPY NUMBER)
1319 IS
1320
1321 l_api_name CONSTANT VARCHAR2(30) := 'COPY_CONTRACT_LINE';
1322 l_api_version CONSTANT NUMBER := 1.0;
1323 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1324 l_error_text VARCHAR2(512);
1325
1326 l_clev_rec clev_rec_type;
1327 x_clev_rec clev_rec_type;
1328
1329 l_sts_code VARCHAR2(30);
1330 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1331 l_old_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1332 l_cle_id NUMBER := OKC_API.G_MISS_NUM;
1333 l_rgp_id NUMBER;
1334 l_cat_id NUMBER;
1335 l_pav_id NUMBER;
1336 l_cpl_id NUMBER;
1337 l_start_date DATE;
1338 l_end_date DATE;
1339 l_old_lse_id NUMBER;
1340
1341
1342 CURSOR c_dnz_chr_id IS
1343 SELECT dnz_chr_id
1344 FROM okc_k_lines_b
1345 WHERE id = p_to_cle_id;
1346
1347 CURSOR c_pavv IS
1348 SELECT id
1349 FROM okc_price_att_values
1350 WHERE cle_id = p_from_cle_id;
1351
1352 -- Pkoganti 08/31, Bug 1392336
1353 -- Added rle_code <> 'LICENCEE_ACCT'
1354 -- When the user chooses to copy only the lines, the LICENCEE_ACCT
1355 -- party role should not be copied, because the target contract
1356 -- may not have the constraining party information. This is a temp
1357 -- fix for GSR.
1358 --
1359 CURSOR c_cplv IS
1360 SELECT id
1361 FROM okc_k_party_roles_b
1362 WHERE cle_id = p_from_cle_id
1363 AND rle_code <> 'LICENCEE_ACCT'
1364 AND dnz_chr_id = p_from_chr_id;
1365
1366 --------
1367 -- Procedure to get priced line information
1368 --------
1369 PROCEDURE get_priced_line_rec(px_clev_rec IN OUT NOCOPY clev_rec_type) IS
1370
1371 l_api_name CONSTANT VARCHAR2(50) := 'COPY_CONTRACT_LINE.GET_PRICED_LINE_REC';
1372 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1373
1374 l_priced_yn VARCHAR2(3);
1375 l_cim_id NUMBER;
1376 l_lty_code VARCHAR2(90);
1377 --l_clev_rec clev_rec_type := px_clev_rec;
1378 l_cimv_rec cimv_rec_type;
1379
1380 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1381
1382 CURSOR c_lse(p_id IN NUMBER) IS
1383 SELECT lty_code,
1384 priced_yn
1385 FROM okc_line_styles_b
1386 WHERE id = p_id;
1387
1388 CURSOR c_cim(p_cle_id IN NUMBER) IS
1389 SELECT id
1390 FROM okc_k_items
1391 WHERE cle_id = p_cle_id
1392 AND priced_item_yn = 'Y';
1393
1394 BEGIN
1395
1396 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1397 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','begin');
1398 END IF;
1399
1400 OPEN c_lse(px_clev_rec.lse_id);
1401 FETCH c_lse INTO l_lty_code, l_priced_yn;
1402 CLOSE c_lse;
1403
1404 IF px_clev_rec.price_level_ind = 'N' THEN
1405 IF l_priced_yn = 'N' THEN
1406 px_clev_rec.price_negotiated := NULL;
1407 ELSE
1408 px_clev_rec.price_negotiated := NULL;
1409 IF l_lty_code <> 'FREE_FORM' THEN
1410 px_clev_rec.name := NULL;
1411 END IF;
1412 END IF;
1413 ELSE
1414 IF l_priced_yn = 'N' THEN
1415 px_clev_rec.price_negotiated := NULL;
1416 px_clev_rec.PRICE_UNIT := NULL;
1417 IF l_lty_code <> 'FREE_FORM' THEN
1418 px_clev_rec.name := NULL;
1419 END IF;
1420 ELSE
1421 OPEN c_cim(l_clev_rec.id);
1422 FETCH c_cim INTO l_cim_id;
1423 CLOSE c_cim;
1424
1425 IF l_cim_id IS NOT NULL THEN
1426 l_return_status := get_cimv_rec(p_cim_id => l_cim_id,
1427 x_cimv_rec => l_cimv_rec);
1428
1429 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1430 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.validate_item','calling OKC_CONTRACT_ITEM_PUB.validate_contract_item');
1431 END IF;
1432
1433 OKC_CONTRACT_ITEM_PUB.validate_contract_item(
1434 p_api_version => 1.0,
1435 p_init_msg_list => FND_API.G_FALSE,
1436 x_return_status => l_return_status,
1437 x_msg_count => x_msg_count,
1438 x_msg_data => x_msg_data,
1439 p_cimv_rec => l_cimv_rec);
1440
1441 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1442 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.validate_item','after call to OKC_CONTRACT_ITEM_PUB.validate_contract_item, x_return_status='||l_return_status);
1443 END IF;
1444
1445 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1446 px_clev_rec.price_negotiated := NULL;
1447 px_clev_rec.PRICE_UNIT := NULL;
1448 px_clev_rec.name := NULL;
1449 END IF;
1450 END IF;
1451 END IF;
1452 END IF;
1453 --x_clev_rec := l_clev_rec;
1454
1455 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1456 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','l_return_status='||l_return_status);
1457 END IF;
1458
1459 EXCEPTION
1460 WHEN OTHERS THEN
1461 --x_clev_rec := l_clev_rec;
1462 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1463
1464 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1465 l_error_text := substr (SQLERRM, 1, 512);
1466 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
1467 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1468 END IF;
1469
1470 END get_priced_line_rec;
1471
1472 --------
1473 -- procedure to instantiate counter events for a given line
1474 --------
1475
1476 PROCEDURE instantiate_counters_events (
1477 x_return_status OUT NOCOPY VARCHAR2,
1478 x_msg_count OUT NOCOPY NUMBER,
1479 x_msg_data OUT NOCOPY VARCHAR2,
1480 p_old_cle_id IN NUMBER,
1481 p_old_lse_id IN NUMBER,
1482 p_start_date IN DATE,
1483 p_end_date IN DATE,
1484 p_new_cle_id IN NUMBER) IS
1485
1486 l_api_name CONSTANT VARCHAR2(50) := 'COPY_CONTRACT_LINE.INSTANTIATE_COUNTERS_EVENTS';
1487 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1488
1489 l_item_id VARCHAR2(40);
1490 l_standard_cov_yn VARCHAR2(1);
1491 l_counter_grp_id NUMBER;
1492 l_found BOOLEAN;
1493 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1494 l_ctr_grp_id_template NUMBER;
1495 l_ctr_grp_id_instance NUMBER;
1496 l_instcnd_inp_rec OKC_INST_CND_PUB.instcnd_inp_rec;
1497 l_actual_coverage_id NUMBER;
1498
1499 CURSOR c_item IS
1500 SELECT a.object1_id1, b.standard_cov_yn
1501 FROM okc_k_items a, oks_k_lines_b b
1502 WHERE a.cle_id = p_old_cle_id
1503 AND b.cle_id = p_old_cle_id;
1504
1505 CURSOR l_ctr_csr (p_id NUMBER) IS
1506 SELECT Counter_Group_id
1507 FROM OKX_CTR_ASSOCIATIONS_V
1508 WHERE Source_Object_Id = p_id;
1509
1510 BEGIN
1511
1512 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1513 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','p_old_cle_id='||p_old_cle_id||' ,p_old_lse_id='||p_old_lse_id||' ,p_start_date='||p_start_date||' ,p_end_date='||p_end_date||' ,p_new_cle_id='||p_new_cle_id);
1514 END IF;
1515
1516 x_return_status := l_return_status;
1517 OPEN c_item;
1518 FETCH c_item INTO l_item_id, l_standard_cov_yn;
1519 CLOSE c_item;
1520
1521 IF l_item_id IS NOT NULL AND Is_Number(l_item_id) THEN
1522
1523 -- Check whether counters are attached to the item
1524 OPEN l_ctr_csr(l_item_id);
1525 FETCH l_ctr_csr INTO l_counter_grp_id;
1526 l_found := l_ctr_csr%FOUND;
1527 CLOSE l_ctr_csr;
1528
1529
1530 IF (l_found) THEN -- if counter attachted, instantiate it
1531
1532 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1533 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.inst_ctrs', 'Calling CS_COUNTERS_PUB.autoinstantiate_counters, p_source_object_id_template='||l_item_id||
1534 ' ,p_source_object_id_instance='||p_new_cle_id);
1535 END IF;
1536
1537 CS_COUNTERS_PUB.autoinstantiate_counters(
1538 p_api_version => 1.0,
1539 p_init_msg_list => FND_API.G_FALSE,
1540 x_return_status => l_return_status,
1541 x_msg_count => x_msg_count,
1542 x_msg_data => x_msg_data,
1543 p_commit => FND_API.G_FALSE,
1544 p_source_object_id_template => l_item_id,
1545 p_source_object_id_instance => p_new_cle_id,
1546 x_ctr_grp_id_template => l_ctr_grp_id_template,
1547 x_ctr_grp_id_instance => l_ctr_grp_id_instance);
1548
1549 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1550 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.inst_ctrs', 'After call to CS_COUNTERS_PUB.autoinstantiate_counters, x_return_status='||l_return_status||' ,x_ctr_grp_id_template='||l_ctr_grp_id_template||
1551 ' ,x_ctr_grp_id_instance='||l_ctr_grp_id_instance);
1552 END IF;
1553
1554 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1555 x_return_status := l_return_status;
1556 RAISE G_EXCEPTION_HALT_VALIDATION;
1557 END IF;
1558
1559 l_instcnd_inp_rec.ins_ctr_grp_id := l_ctr_grp_id_instance;
1560 l_instcnd_inp_rec.tmp_ctr_grp_id := l_ctr_grp_id_template;
1561 l_instcnd_inp_rec.jtot_object_code := 'OKC_K_LINE';
1562 l_instcnd_inp_rec.cle_id := p_new_cle_id;
1563
1564 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1565 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.inst_cond', 'Calling OKC_INST_CND_PUB.inst_condition');
1566 END IF;
1567
1568 OKC_INST_CND_PUB.inst_condition(
1569 p_api_version => 1.0,
1570 p_init_msg_list => FND_API.G_FALSE,
1571 x_return_status => l_return_status,
1572 x_msg_count => x_msg_count,
1573 x_msg_data => x_msg_data,
1574 p_instcnd_inp_rec => l_instcnd_inp_rec);
1575
1576 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1577 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.inst_cond', 'After call to OKC_INST_CND_PUB.inst_condition, x_return_status='||l_return_status);
1578 END IF;
1579
1580 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1581 x_return_status := l_return_status;
1582 RAISE G_EXCEPTION_HALT_VALIDATION;
1583 END IF;
1584
1585 END IF;
1586 END IF;
1587
1588
1589 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1590 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.chk_cov_type','p_old_lse_id='||p_old_lse_id||' ,l_standard_cov_yn='||l_standard_cov_yn);
1591 END IF;
1592
1593 IF p_old_lse_id IN (1,19) THEN
1594
1595 --Instantiate the coverage, if only if it's a non standard coverage
1596 IF (l_standard_cov_yn = 'N') THEN
1597
1598 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1599 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_adj_cov','calling OKS_COVERAGES_PUB.create_adjusted_coverage, P_Source_contract_Line_Id='||p_old_cle_id||' ,P_Target_contract_Line_Id='||p_new_cle_id);
1600 END IF;
1601
1602 OKS_COVERAGES_PUB.create_adjusted_coverage(
1603 p_api_version => 1.0,
1604 p_init_msg_list => FND_API.G_FALSE,
1605 x_return_status => l_return_status,
1606 x_msg_count => x_msg_count,
1607 x_msg_data => x_msg_data,
1608 P_Source_contract_Line_Id => p_old_cle_id,
1609 P_Target_contract_Line_Id => p_new_cle_id,
1610 x_Actual_coverage_id => l_actual_coverage_id);
1611
1612 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1613 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_adj_cov','after call to OKS_COVERAGES_PUB.create_adjusted_coverage, x_return_status='||l_return_status||' ,x_Actual_coverage_id='||l_actual_coverage_id);
1614 END IF;
1615
1616 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1617 x_return_status := l_return_status;
1618 RAISE G_EXCEPTION_HALT_VALIDATION;
1619 END IF;
1620
1621 --update oks top line with the newly created non-std coverage
1622 --the oks top line must be present before this can be done
1623 UPDATE oks_k_lines_b SET
1624 coverage_id = l_actual_coverage_id
1625 WHERE cle_id = p_new_cle_id;
1626 END IF;
1627
1628
1629 --for both standard and non standard coverage, need to copy
1630 --coverage notes and pm schedules. These coverage entities are
1631 --associated to the topline and the not the coverage
1632
1633 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1634 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_cov_extn','calling OKS_COVERAGES_PVT.create_k_coverage_ext, p_src_line_id='||p_old_cle_id||' ,p_tgt_line_id='||p_new_cle_id);
1635 END IF;
1636
1637 OKS_COVERAGES_PVT.create_k_coverage_ext(
1638 p_api_version => 1.0,
1639 p_init_msg_list => FND_API.G_FALSE,
1640 x_return_status => l_return_status,
1641 x_msg_count => x_msg_count,
1642 x_msg_data => x_msg_data,
1643 p_src_line_id => p_old_cle_id,
1644 p_tgt_line_id => p_new_cle_id);
1645
1646 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1647 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_cov_extn','after call to OKS_COVERAGES_PVT.create_k_coverage_ext, x_return_status='||l_return_status);
1648 END IF;
1649
1650 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1651 x_return_status := l_return_status;
1652 RAISE G_EXCEPTION_HALT_VALIDATION;
1653 END IF;
1654
1655 END IF;
1656
1657 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1658 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end', 'x_return_status='||x_return_status);
1659 END IF;
1660
1661 EXCEPTION
1662 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1663 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1664 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_error', 'x_return_status='||x_return_status);
1665 END IF;
1666
1667 WHEN OTHERS THEN
1668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1669
1670 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1671 l_error_text := substr (SQLERRM, 1, 512);
1672 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
1673 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1674 END IF;
1675
1676 END instantiate_counters_events;
1677
1678 ------
1679 -- Function to get parent's start_date and end_date for child lines
1680 -- parameters :
1681 -- p_from_start_date => start date of old contract header/line
1682 -- p_from_end_date => end date of old contract header/line
1683 -- p_to_cle_id => id of the new line
1684 -- p_to_chr_id => id of the new header
1685 -- x_start_date => calculated start date for new lines
1686 -- x_end_date => calculated end date fon new lines
1687 ------
1688
1689 FUNCTION get_parent_date(
1690 p_from_start_date IN DATE,
1691 p_from_end_date IN DATE,
1692 p_to_cle_id IN NUMBER,
1693 p_to_chr_id IN NUMBER,
1694 x_start_date OUT NOCOPY DATE,
1695 x_end_date OUT NOCOPY DATE) RETURN BOOLEAN IS
1696
1697 l_api_name CONSTANT VARCHAR2(50) := 'COPY_CONTRACT_LINE.GET_PARENT_DATE';
1698 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1699
1700 l_parent_start_date DATE;
1701 l_parent_end_date DATE;
1702
1703 CURSOR c_cle IS
1704 SELECT start_date, end_date
1705 FROM okc_k_lines_b
1706 WHERE id = p_to_cle_id;
1707
1708 CURSOR c_chr IS
1709 SELECT start_date, end_date
1710 FROM okc_k_headers_b
1711 WHERE id = p_to_chr_id;
1712
1713 BEGIN
1714
1715 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1716 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','p_from_start_date='||p_from_start_date||' ,p_from_end_date='||p_from_end_date||' ,p_to_cle_id='||p_to_cle_id||' ,p_to_chr_id='||p_to_chr_id);
1717 END IF;
1718
1719 IF NOT (p_to_cle_id IS NULL OR p_to_cle_id = OKC_API.G_MISS_NUM) THEN
1720 OPEN c_cle;
1721 FETCH c_cle INTO l_parent_start_date, l_parent_end_date;
1722 CLOSE c_cle;
1723 x_start_date := p_from_end_date + 1;
1724 x_end_date := l_parent_end_date;
1725 ELSE
1726 OPEN c_chr;
1727 FETCH c_chr INTO l_parent_start_date, l_parent_end_date;
1728 CLOSE c_chr;
1729 x_start_date := p_from_end_date + 1;
1730 x_end_date := l_parent_end_date;
1731 END IF;
1732
1733 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1734 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_start_date='||x_start_date||' ,x_end_date='||x_end_date);
1735 END IF;
1736
1737 RETURN(TRUE);
1738
1739 END get_parent_date;
1740
1741 -------
1742 -- procedure for price conversion for renewed header
1743 -------
1744
1745 PROCEDURE do_price_conversion (
1746 px_clev_rec IN OUT NOCOPY clev_rec_type,
1747 x_return_status OUT NOCOPY VARCHAR2) IS
1748
1749 l_api_name CONSTANT VARCHAR2(50) := 'COPY_CONTRACT_LINE.DO_PRICE_CONVERSION';
1750 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1751
1752 CURSOR header_cur(p_cle_id IN NUMBER) IS
1753 SELECT id, currency_code
1754 FROM okc_k_headers_b
1755 WHERE id = (SELECT dnz_chr_id
1756 FROM okc_k_lines_b
1757 WHERE id = p_cle_id);
1758
1759 l_hdr_id NUMBER;
1760 l_curr_code VARCHAR2(15);
1761 l_old_amount NUMBER;
1762 l_cvn_type VARCHAR2(30);
1763 l_cvn_date DATE;
1764 l_cvn_rate NUMBER;
1765 l_return_status VARCHAR2(20);
1766
1767 ----
1768 -- procedure will get convertion details for a given header id
1769 ----
1770 PROCEDURE GET_CVN_DTLS(
1771 p_chr_id IN NUMBER,
1772 x_cvn_type OUT NOCOPY VARCHAR2,
1773 x_cvn_date OUT NOCOPY DATE,
1774 x_cvn_rate OUT NOCOPY NUMBER,
1775 x_return_status OUT NOCOPY VARCHAR2) IS
1776
1777 CURSOR cvn_cur IS
1778 SELECT CONVERSION_TYPE, CONVERSION_RATE, CONVERSION_RATE_DATE
1779 FROM okc_k_headers_b
1780 WHERE id = p_chr_id;
1781 /*
1782 cursor cvn_type_cur(p_id in varchar2) is
1783 select name
1784 from okx_conversion_types_v
1785 where id1 = p_id;
1786 */
1787
1788 l_cvn_exists BOOLEAN := FALSE;
1789 l_cvn_type_exists BOOLEAN := FALSE;
1790 BEGIN
1791 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1792
1793 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1794 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.GET_CVN_DTLS.begin', 'p_chr_id='||p_chr_id);
1795 END IF;
1796
1797 FOR cvn_rec IN cvn_cur
1798 LOOP
1799 l_cvn_exists := TRUE;
1800 x_cvn_rate := cvn_rec.CONVERSION_RATE;
1801 x_cvn_date := to_date(cvn_rec.CONVERSION_RATE_DATE,'YYYY/MM/DD HH24:MI:SS');
1802 x_cvn_type := cvn_rec.CONVERSION_TYPE;
1803
1804 END LOOP;
1805
1806 IF l_cvn_type_exists THEN
1807 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1808 ELSE
1809 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1810 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.GET_CVN_DTLS.cvn_type_chk','no cvn rule');
1811 END IF;
1812 RAISE G_EXCEPTION_HALT_VALIDATION;
1813 END IF;
1814
1815 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1816 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.GET_CVN_DTLS.end', 'x_return_status='||x_return_status||' ,x_cvn_type='||x_cvn_type||' ,x_cvn_date='||x_cvn_date||' ,x_cvn_rate='||x_cvn_rate);
1817 END IF;
1818
1819 EXCEPTION
1820 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1821 x_return_status := OKC_API.G_RET_STS_ERROR;
1822 IF(FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level)THEN
1823 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name||'.GET_CVN_DTLS.end_error', 'x_return_status='||x_return_status);
1824 END IF;
1825 WHEN OTHERS THEN
1826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1827
1828 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1829 l_error_text := substr (SQLERRM, 1, 512);
1830 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.GET_CVN_DTLS.end_other_error', l_error_text);
1831 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1832 END IF;
1833
1834 END GET_CVN_DTLS;
1835
1836 BEGIN
1837
1838 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1839 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','p_from_cle_id='||p_from_cle_id);
1840 END IF;
1841
1842 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1843
1844 OPEN header_cur(p_from_cle_id);
1845 l_old_amount := px_clev_rec.price_negotiated;
1846 FETCH header_cur INTO l_hdr_id, l_curr_code;
1847
1848 get_cvn_dtls(
1849 p_chr_id => l_hdr_id,
1850 x_cvn_type => l_cvn_type,
1851 x_cvn_date => l_cvn_date,
1852 x_cvn_rate => l_cvn_rate,
1853 x_return_status => l_return_status);
1854
1855 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1856 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_get_cvn_dtls','l_return_status='|| l_return_status);
1857 END IF;
1858
1859 x_return_status := l_return_status;
1860
1861 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1862
1863 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1864 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.convert','Calling OKC_CURRENCY_API.convert_amount, p_from_currency='||l_curr_code||' ,p_to_currency=EUR ,p_conversion_date='||l_cvn_date||
1865 ' ,p_conversion_type='||l_cvn_type||' ,p_amount='||l_old_amount);
1866 END IF;
1867
1868 OKC_CURRENCY_API.convert_amount(
1869 p_from_currency => l_curr_code,
1870 p_to_currency => 'EUR',
1871 p_conversion_date => l_cvn_date,
1872 p_conversion_type => l_cvn_type,
1873 p_amount => l_old_amount,
1874 x_conversion_rate => l_cvn_rate,
1875 x_converted_amount => px_clev_rec.price_negotiated);
1876
1877 px_clev_rec.currency_code := 'EUR';
1878
1879 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1880 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.convert','after call to OKC_CURRENCY_API.convert_amount, x_conversion_rate='||l_cvn_rate||' ,x_converted_amount='||px_clev_rec.price_negotiated);
1881 END IF;
1882
1883 END IF;
1884
1885 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1886 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_return_status='||x_return_status);
1887 END IF;
1888
1889 EXCEPTION
1890
1891 WHEN OTHERS THEN
1892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1893
1894 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1895 l_error_text := substr (SQLERRM, 1, 512);
1896 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
1897 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1898 END IF;
1899
1900 END do_price_conversion;
1901
1902 BEGIN
1903
1904 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1905 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','p_from_cle_id='||p_from_cle_id||' ,p_from_chr_id='||p_from_chr_id||' ,p_to_cle_id='||p_to_cle_id||' ,p_to_chr_id='||p_to_chr_id||' ,p_lse_id='||p_lse_id||
1906 ' ,p_to_template_yn='||p_to_template_yn||' ,p_copy_reference='||p_copy_reference||' ,p_copy_line_party_yn='||p_copy_line_party_yn||' ,p_renew_ref_yn='||p_renew_ref_yn||' ,p_need_conversion='||p_need_conversion);
1907 END IF;
1908
1909 --standard api initilization and checks
1910 SAVEPOINT copy_contract_line_PVT;
1911 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
1912 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1913 END IF;
1914 IF FND_API.to_boolean(p_init_msg_list) THEN
1915 FND_MSG_PUB.initialize;
1916 END IF;
1917 x_return_status := FND_API.G_RET_STS_SUCCESS;
1918
1919 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1920 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.get_clev_rec', 'Calling get_clev_rec p_cle_id='||p_from_cle_id);
1921 END IF;
1922
1923 l_return_status := get_clev_rec(p_cle_id => p_from_cle_id,
1924 x_clev_rec => l_clev_rec);
1925
1926 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1927 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.get_clev_rec', 'After get_clev_rec l_clev_rec.date_renewed='||l_clev_rec.date_renewed);
1928 END IF;
1929
1930 --
1931 -- If copy called for renewal, do not copy renewed lines
1932 --
1933 IF p_renew_ref_yn = 'Y' AND l_clev_rec.date_renewed IS NOT NULL THEN
1934 RETURN;
1935 END IF;
1936
1937 IF p_to_chr_id IS NULL OR p_to_chr_id = OKC_API.G_MISS_NUM THEN
1938 OPEN c_dnz_chr_id;
1939 FETCH c_dnz_chr_id INTO l_clev_rec.dnz_chr_id;
1940 CLOSE c_dnz_chr_id;
1941 ELSE
1942 l_clev_rec.dnz_chr_id := p_to_chr_id;
1943 END IF;
1944
1945 DECLARE -- Added by Jacob K. on 09/07/01 for the line numbering functionality
1946 CURSOR c_toplinenum(p_id IN NUMBER) IS
1947 SELECT MAX(to_number(line_number))
1948 FROM okc_k_lines_b
1949 WHERE dnz_chr_id = p_id
1950 AND cle_id IS NULL;
1951 CURSOR c_sublinenum(p_id IN NUMBER) IS
1952 SELECT MAX(to_number(line_number))
1953 FROM okc_k_lines_b
1954 WHERE cle_id = p_id
1955 AND lse_id IN (7, 8, 9, 10, 11, 35, 25);
1956 BEGIN
1957 IF l_clev_rec.lse_id IN (1, 12, 14, 19) THEN
1958 OPEN c_toplinenum(l_clev_rec.dnz_chr_id);
1959 FETCH c_toplinenum INTO l_clev_rec.line_number;
1960 CLOSE c_toplinenum;
1961 ELSE
1962 OPEN c_sublinenum(p_to_cle_id);
1963 FETCH c_sublinenum INTO l_clev_rec.line_number;
1964 CLOSE c_sublinenum;
1965 END IF;
1966 l_clev_rec.line_number := nvl(l_clev_rec.line_number, 0) + 1;
1967
1968 END; -- End of line numbering
1969
1970 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1971 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.line_numbering', 'l_clev_rec.line_number='||l_clev_rec.line_number);
1972 END IF;
1973
1974 l_clev_rec.payment_instruction_type := NULL; --null out the payment instructions
1975 l_clev_rec.orig_system_id1 := l_clev_rec.id;
1976 l_clev_rec.orig_system_reference1 := 'COPY';
1977 l_clev_rec.orig_system_source_code := 'OKC_LINE';
1978
1979 l_clev_rec.cle_id := p_to_cle_id;
1980 l_clev_rec.chr_id := p_to_chr_id;
1981 l_clev_rec.trn_code := NULL;
1982 l_clev_rec.date_terminated := NULL;
1983
1984 -- get status code for the line
1985 OKC_ASSENT_PUB.get_default_status(
1986 x_return_status => l_return_status,
1987 p_status_type => 'ENTERED',
1988 x_status_code => l_sts_code);
1989
1990 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1991 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.default_status', 'after call to OKC_ASSENT_PUB.get_default_status, p_status_type=ENTERED, x_status_code='||l_sts_code);
1992 END IF;
1993
1994 l_clev_rec.sts_code := l_sts_code;
1995
1996 -- get parent date
1997 IF get_parent_date(p_from_start_date => l_clev_rec.start_date,
1998 p_from_end_date => l_clev_rec.end_date,
1999 p_to_cle_id => p_to_cle_id,
2000 p_to_chr_id => p_to_chr_id,
2001 x_start_date => l_start_date,
2002 x_end_date => l_end_date) THEN
2003 -- If the line dates are not in between its parents date default to parent date.
2004 l_clev_rec.start_date := l_start_date;
2005 l_clev_rec.end_date := l_end_date;
2006 END IF;
2007
2008 l_old_lse_id := l_clev_rec.lse_id;
2009
2010 -- for renewal populate following fields
2011 IF p_renew_ref_yn = 'Y' THEN
2012 l_clev_rec.PRICE_NEGOTIATED_RENEWED := l_clev_rec.PRICE_NEGOTIATED;
2013 l_clev_rec.CURRENCY_CODE_RENEWED := l_clev_rec.CURRENCY_CODE;
2014 END IF;
2015
2016 -- populate lse_id field with the parameter passed
2017 IF p_lse_id IS NOT NULL THEN
2018 l_clev_rec.lse_id := p_lse_id;
2019 END IF;
2020
2021 -- for non top lines, the chr_id field should be null
2022 IF l_clev_rec.lse_id NOT IN (1, 12, 14, 19) THEN
2023 l_clev_rec.chr_id := NULL;
2024 l_clev_rec.cle_id := p_to_cle_id;
2025 END IF;
2026
2027 get_priced_line_rec(l_clev_rec);
2028
2029 -- For EURO CONVERSION
2030 -- Change for bug # 2455295
2031 IF p_need_conversion = 'Y' THEN
2032 IF (l_clev_rec.price_negotiated IS NULL OR
2033 l_clev_rec.price_negotiated = 0 ) THEN
2034 --- only change the currency code
2035 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2036 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.curr_conv','price_negotiated is 0, only changing currency code to EUR');
2037 END IF;
2038 l_clev_rec.currency_code := 'EUR';
2039 ELSE
2040
2041 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2042 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.curr_conv','Calling do_price_conversion');
2043 END IF;
2044 do_price_conversion(l_clev_rec, l_return_status);
2045
2046 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2047 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.curr_conv','after call to do_price_conversion, l_return_status='||l_return_status);
2048 END IF;
2049
2050 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2051 x_return_status := l_return_status;
2052 RAISE G_EXCEPTION_HALT_VALIDATION;
2053 END IF;
2054 END IF;
2055 END IF;
2056 -- For EURO CONVERSION
2057
2058 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2059 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_okc_line','calling OKC_CONTRACT_PUB.create_contract_line, l_clev_rec: lse_id='||l_clev_rec.lse_id||
2060 ' ,start_date='||l_clev_rec.start_date||' ,end_date='||l_clev_rec.end_date||' ,cle_id='||l_clev_rec.cle_id||' ,dnz_chr_id='||l_clev_rec.dnz_chr_id);
2061 END IF;
2062
2063 OKC_CONTRACT_PUB.create_contract_line(
2064 p_api_version => 1.0,
2065 p_init_msg_list => FND_API.G_FALSE,
2066 x_return_status => l_return_status,
2067 x_msg_count => x_msg_count,
2068 x_msg_data => x_msg_data,
2069 p_clev_rec => l_clev_rec,
2070 x_clev_rec => x_clev_rec);
2071
2072 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2073 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_okc_line','after call to OKC_CONTRACT_PUB.create_contract_line, x_return_status='|| l_return_status||' ,x_clev_rec.id='||x_clev_rec.id);
2074 END IF;
2075
2076 IF (l_return_status NOT IN (OKC_API.G_RET_STS_SUCCESS, OKC_API.G_RET_STS_WARNING)) THEN
2077 x_return_status := l_return_status;
2078 RAISE G_EXCEPTION_HALT_VALIDATION;
2079 ELSE
2080 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS AND x_return_status = OKC_API.G_RET_STS_WARNING) THEN
2081 NULL; --do not overwrite warning with success
2082 ELSE
2083 x_return_status := l_return_status;
2084 END IF;
2085 END IF;
2086
2087 x_cle_id := x_clev_rec.id; -- passes the new generated id to the caller.
2088
2089 --we need to create the oks line before creating non-std cov in the following call to
2090 --instantiate_counters_events
2091 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2092 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_oks_line','Calling copy_rules, p_old_cle_id='||p_from_cle_id||' ,p_cle_id='||x_clev_rec.id||' ,p_chr_id='||l_clev_rec.dnz_chr_id||' ,p_to_template_yn='||p_to_template_yn);
2093 END IF;
2094
2095 copy_rules (
2096 p_api_version => 1.0,
2097 p_init_msg_list => FND_API.G_FALSE,
2098 x_return_status => l_return_status,
2099 x_msg_count => x_msg_count,
2100 x_msg_data => x_msg_data,
2101 p_old_cle_id => p_from_cle_id,
2102 p_cle_id => x_clev_rec.id,
2103 p_chr_id => l_clev_rec.dnz_chr_id,
2104 p_cust_acct_id => l_clev_rec.cust_acct_id,
2105 p_bill_to_site_use_id => l_clev_rec.bill_to_site_use_id,
2106 p_to_template_yn => p_to_template_yn);
2107
2108 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2109 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.create_oks_line','after call to copy_rules, x_return_status='||l_return_status);
2110 END IF;
2111
2112 IF (l_return_status NOT IN (OKC_API.G_RET_STS_SUCCESS, OKC_API.G_RET_STS_WARNING)) THEN
2113 x_return_status := l_return_status;
2114 RAISE G_EXCEPTION_HALT_VALIDATION;
2115 ELSE
2116 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS AND x_return_status = OKC_API.G_RET_STS_WARNING) THEN
2117 NULL; --do not overwrite warning with success
2118 ELSE
2119 x_return_status := l_return_status;
2120 END IF;
2121 END IF;
2122
2123 -- instantiate counters
2124 IF l_clev_rec.lse_id IN (1, 14, 19) THEN
2125
2126 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2127 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.instantiate_ctr_evts','Calling instantiate_counters_events, p_old_cle_id='||l_clev_rec.id||' ,p_old_lse_id='||l_old_lse_id||
2128 ' ,p_start_date='||x_clev_rec.start_date||' ,p_end_date='||x_clev_rec.end_date||' ,p_new_cle_id='||x_clev_rec.id);
2129 END IF;
2130
2131 instantiate_counters_events(
2132 x_return_status => l_return_status,
2133 x_msg_count => x_msg_count,
2134 x_msg_data => x_msg_data,
2135 p_old_cle_id => l_clev_rec.id,
2136 p_old_lse_id => l_old_lse_id,
2137 p_start_date => x_clev_rec.start_date,
2138 p_end_date => x_clev_rec.end_date,
2139 p_new_cle_id => x_clev_rec.id);
2140
2141 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2142 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'instantiate_ctr_evts','after call to instantiate_counters_events, x_return_status='||l_return_status);
2143 END IF;
2144
2145 IF (l_return_status NOT IN (OKC_API.G_RET_STS_SUCCESS, OKC_API.G_RET_STS_WARNING)) THEN
2146 x_return_status := l_return_status;
2147 RAISE G_EXCEPTION_HALT_VALIDATION;
2148 ELSE
2149 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS AND x_return_status = OKC_API.G_RET_STS_WARNING) THEN
2150 NULL; --do not overwrite warning with success
2151 ELSE
2152 x_return_status := l_return_status;
2153 END IF;
2154 END IF;
2155
2156 END IF;
2157
2158
2159 FOR l_c_pavv IN c_pavv LOOP
2160
2161 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2162 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.price_att_values','Calling copy_price_att_values, p_pav_id='||l_c_pavv.id||' ,p_cle_id='||x_clev_rec.id||' ,p_chr_id=NULL');
2163 END IF;
2164
2165 copy_price_att_values(
2166 p_api_version => 1.0,
2167 p_init_msg_list => FND_API.G_FALSE,
2168 x_return_status => l_return_status,
2169 x_msg_count => x_msg_count,
2170 x_msg_data => x_msg_data,
2171 p_pav_id => l_c_pavv.id,
2172 p_cle_id => x_clev_rec.id,
2173 p_chr_id => NULL,
2174 x_pav_id => l_pav_id);
2175
2176 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2177 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.price_att_values','after call to copy_price_att_values, x_return_status='||l_return_status||' ,x_pav_id='||l_pav_id);
2178 END IF;
2179
2180 IF (l_return_status NOT IN (OKC_API.G_RET_STS_SUCCESS, OKC_API.G_RET_STS_WARNING)) THEN
2181 x_return_status := l_return_status;
2182 RAISE G_EXCEPTION_HALT_VALIDATION;
2183 ELSE
2184 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS AND x_return_status = OKC_API.G_RET_STS_WARNING) THEN
2185 NULL; --do not overwrite warning with success
2186 ELSE
2187 x_return_status := l_return_status;
2188 END IF;
2189 END IF;
2190
2191 END LOOP;
2192
2193
2194 FOR l_c_cplv IN c_cplv LOOP
2195 l_old_return_status := l_return_status;
2196
2197 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2198 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.party_roles', 'Calling copy_party_roles, p_cpl_id='||l_c_cplv.id||' p_cle_id='||x_clev_rec.id||' ,p_chr_id=NULL ,p_rle_code=NULL');
2199 END IF;
2200
2201 copy_party_roles(
2202 p_api_version => 1.0,
2203 p_init_msg_list => FND_API.G_FALSE,
2204 x_return_status => l_return_status,
2205 x_msg_count => x_msg_count,
2206 x_msg_data => x_msg_data,
2207 p_cpl_id => l_c_cplv.id,
2208 p_cle_id => x_clev_rec.id,
2209 p_chr_id => NULL,
2210 p_rle_code => NULL,
2211 x_cpl_id => l_cpl_id);
2212
2213 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2214 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.party_roles', 'after call to copy_party_roles, x_return_status='||l_return_status||' ,x_cpl_id='||l_cpl_id);
2215 END IF;
2216
2217 IF (l_return_status NOT IN (OKC_API.G_RET_STS_SUCCESS, OKC_API.G_RET_STS_WARNING)) THEN
2218 x_return_status := l_return_status;
2219 RAISE G_EXCEPTION_HALT_VALIDATION;
2220 ELSE
2221 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS AND x_return_status = OKC_API.G_RET_STS_WARNING) THEN
2222 NULL; --do not overwrite warning with success
2223 ELSE
2224 x_return_status := l_return_status;
2225 END IF;
2226 END IF;
2227
2228 END LOOP;
2229
2230 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2231 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.items', 'Calling copy_items, p_from_cle_id='||p_from_cle_id||' ,p_copy_reference='||p_copy_reference||' ,p_to_cle_id='||x_clev_rec.id);
2232 END IF;
2233
2234 copy_items(
2235 p_api_version => 1.0,
2236 p_init_msg_list => FND_API.G_FALSE,
2237 x_return_status => l_return_status,
2238 x_msg_count => x_msg_count,
2239 x_msg_data => x_msg_data,
2240 p_from_cle_id => p_from_cle_id,
2241 p_copy_reference => p_copy_reference,
2242 p_to_cle_id => x_clev_rec.id);
2243
2244 IF (l_return_status NOT IN (OKC_API.G_RET_STS_SUCCESS, OKC_API.G_RET_STS_WARNING)) THEN
2245 x_return_status := l_return_status;
2246 RAISE G_EXCEPTION_HALT_VALIDATION;
2247 ELSE
2248 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS AND x_return_status = OKC_API.G_RET_STS_WARNING) THEN
2249 NULL; --do not overwrite warning with success
2250 ELSE
2251 x_return_status := l_return_status;
2252 END IF;
2253 END IF;
2254
2255 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2256
2257 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
2258 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end', 'x_return_status='||x_return_status);
2259 END IF;
2260
2261 EXCEPTION
2262 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2263 ROLLBACK TO copy_contract_line_PVT;
2264
2265 IF(FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level)THEN
2266 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name||'.end_halt_validation', 'x_return_status='||x_return_status);
2267 END IF;
2268 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2269
2270 WHEN OTHERS THEN
2271 ROLLBACK TO copy_contract_line_PVT;
2272
2273 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2274
2275 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
2276 l_error_text := substr (SQLERRM, 1, 512);
2277 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
2278 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
2279 END IF;
2280 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2281
2282 END copy_contract_line;
2283
2284
2285
2286 ----------------------------------------------------------------------------
2287 -- Proceduere to create operation instance and operation lines for
2288 -- contract header in case of RENEW
2289 -- Parameters: p_chrv_rec - in header record for object_chr_id and scs_code
2290 -- p_to_chr_id - subject_chr_id
2291 ----------------------------------------------------------------------------
2292 PROCEDURE Create_Renewal_Header_Link (
2293 p_api_version IN NUMBER,
2294 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2295 x_return_status OUT NOCOPY VARCHAR2,
2296 x_msg_count OUT NOCOPY NUMBER,
2297 x_msg_data OUT NOCOPY VARCHAR2,
2298 p_chrv_rec IN OKC_CONTRACT_PUB.chrv_rec_type,
2299 p_to_chr_id IN NUMBER)
2300 IS
2301 -- bug 5262302
2302 -- below cursor is not needed as this code is only called for ren_con with id = 41
2303 -- Cursor to get class operation id
2304 -- CURSOR cop_csr IS
2305 -- SELECT id
2306 -- FROM okc_class_operations
2307 -- WHERE cls_code = (SELECT cls_code
2308 -- FROM okc_subclasses_b
2309 -- WHERE code = p_chrv_rec.scs_code );
2310
2311 l_cop_id NUMBER;
2312 l_oiev_rec OKC_OPER_INST_PUB.oiev_rec_type;
2313 lx_oiev_rec OKC_OPER_INST_PUB.oiev_rec_type;
2314 l_olev_rec OKC_OPER_INST_PUB.olev_rec_type;
2315 lx_olev_rec OKC_OPER_INST_PUB.olev_rec_type;
2316 l_count NUMBER := 0;
2317 BEGIN
2318 -- bug 5262302
2319 -- get class operation id
2320 -- OPEN cop_csr;
2321 -- FETCH cop_csr INTO l_cop_id;
2322 -- CLOSE cop_csr;
2323
2324 l_oiev_rec.cop_id := 41; -- l_cop_id; bug 5262302
2325 l_oiev_rec.target_chr_id := p_to_chr_id;
2326 l_oiev_rec.status_code := 'ENTERED';
2327
2328 OKC_OPER_INST_PUB.Create_Operation_Instance (
2329 p_api_version => p_api_version,
2330 p_init_msg_list => p_init_msg_list,
2331 x_return_status => x_return_status,
2332 x_msg_count => x_msg_count,
2333 x_msg_data => x_msg_data,
2334 p_oiev_rec => l_oiev_rec,
2335 x_oiev_rec => lx_oiev_rec);
2336
2337 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2338 l_olev_rec.SELECT_YN := 'Y';
2339 l_olev_rec.OIE_ID := lx_oiev_rec.id;
2340 l_olev_rec.SUBJECT_CHR_ID := p_to_chr_id;
2341 l_olev_rec.OBJECT_CHR_ID := p_chrv_rec.id;
2342
2343 OKC_OPER_INST_PUB.Create_Operation_Line (
2344 p_api_version => p_api_version,
2345 p_init_msg_list => p_init_msg_list,
2346 x_return_status => x_return_status,
2347 x_msg_count => x_msg_count,
2348 x_msg_data => x_msg_data,
2349 p_olev_rec => l_olev_rec,
2350 x_olev_rec => lx_olev_rec);
2351 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2352 -- set g_op_lines table
2353 l_count := g_op_lines.COUNT + 1;
2354 g_op_lines(l_count).id := p_chrv_rec.ID;
2355 g_op_lines(l_count).ole_id := lx_olev_rec.ID;
2356 END IF;
2357 END IF;
2358
2359 EXCEPTION
2360 WHEN NO_DATA_FOUND THEN
2361 -- store SQL error message on message stack
2362 x_return_status := OKC_API.G_RET_STS_ERROR;
2363 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
2364 p_msg_name => 'OKC_NOT_FOUND',
2365 p_token1 => 'VALUE1',
2366 p_token1_value => 'Status Code',
2367 p_token2 => 'VALUE2',
2368 p_token2_value => 'OKC_CLASS_OPERATIONS_V');
2369 WHEN OTHERS THEN
2370 -- store SQL error message on message stack
2371 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
2372 p_msg_name => g_unexpected_error,
2373 p_token1 => g_sqlcode_token,
2374 p_token1_value => SQLCODE,
2375 p_token2 => g_sqlerrm_token,
2376 p_token2_value => SQLERRM);
2377 -- notify caller of an error as UNEXPETED error
2378 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2379 END Create_Renewal_Header_Link;
2380
2381
2382 ----------------------------------------------------------------------------
2383 --Function to populate the articles translation record to be copied.
2384 ----------------------------------------------------------------------------
2385 FUNCTION get_atnv_rec(p_atn_id IN NUMBER,
2386 x_atnv_rec OUT NOCOPY atnv_rec_type)
2387 RETURN VARCHAR2 IS
2388 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2389 l_no_data_found BOOLEAN := TRUE;
2390
2391 CURSOR c_atnv_rec IS
2392 SELECT ID,
2393 CAT_ID,
2394 CLE_ID,
2395 RUL_ID,
2396 DNZ_CHR_ID
2397 FROM OKC_ARTICLE_TRANS_V
2398 WHERE ID = p_atn_id;
2399 BEGIN
2400 OPEN c_atnv_rec;
2401 FETCH c_atnv_rec
2402 INTO x_atnv_rec.ID,
2403 x_atnv_rec.CAT_ID,
2404 x_atnv_rec.CLE_ID,
2405 x_atnv_rec.RUL_ID,
2406 x_atnv_rec.DNZ_CHR_ID;
2407
2408 l_no_data_found := c_atnv_rec%NOTFOUND;
2409 CLOSE c_atnv_rec;
2410 IF l_no_data_found THEN
2411 l_return_status := OKC_API.G_RET_STS_ERROR;
2412 RETURN(l_return_status);
2413 ELSE
2414 RETURN(l_return_status);
2415 END IF;
2416 EXCEPTION
2417 WHEN OTHERS THEN
2418 -- store SQL error message on message stack for caller
2419 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
2420 -- notify caller of an UNEXPECTED error
2421 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2422 RETURN(l_return_status);
2423
2424 END get_atnv_rec;
2425
2426 ----------------------------------------------------------------------------
2427 --Function to populate the articles record to be copied.
2428 ----------------------------------------------------------------------------
2429 FUNCTION get_catv_rec(p_cat_id IN NUMBER,
2430 x_catv_rec OUT NOCOPY catv_rec_type)
2431 RETURN VARCHAR2 IS
2432 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2433 l_no_data_found BOOLEAN := TRUE;
2434
2435 CURSOR c_catv_rec IS
2436 SELECT ID,
2437 CHR_ID,
2438 CLE_ID,
2439 CAT_ID,
2440 SFWT_FLAG,
2441 SAV_SAE_ID,
2442 SAV_SAV_RELEASE,
2443 SBT_CODE,
2444 DNZ_CHR_ID,
2445 COMMENTS,
2446 FULLTEXT_YN,
2447 VARIATION_DESCRIPTION,
2448 NAME,
2449 TEXT,
2450 ATTRIBUTE_CATEGORY,
2451 ATTRIBUTE1,
2452 ATTRIBUTE2,
2453 ATTRIBUTE3,
2454 ATTRIBUTE4,
2455 ATTRIBUTE5,
2456 ATTRIBUTE6,
2457 ATTRIBUTE7,
2458 ATTRIBUTE8,
2459 ATTRIBUTE9,
2460 ATTRIBUTE10,
2461 ATTRIBUTE11,
2462 ATTRIBUTE12,
2463 ATTRIBUTE13,
2464 ATTRIBUTE14,
2465 ATTRIBUTE15,
2466 CAT_TYPE
2467 FROM OKC_K_ARTICLES_V
2468 WHERE ID = p_cat_id;
2469 BEGIN
2470 OPEN c_catv_rec;
2471 FETCH c_catv_rec
2472 INTO x_catv_rec.ID,
2473 x_catv_rec.CHR_ID,
2474 x_catv_rec.CLE_ID,
2475 x_catv_rec.CAT_ID,
2476 x_catv_rec.SFWT_FLAG,
2477 x_catv_rec.SAV_SAE_ID,
2478 x_catv_rec.SAV_SAV_RELEASE,
2479 x_catv_rec.SBT_CODE,
2480 x_catv_rec.DNZ_CHR_ID,
2481 x_catv_rec.COMMENTS,
2482 x_catv_rec.FULLTEXT_YN,
2483 x_catv_rec.VARIATION_DESCRIPTION,
2484 x_catv_rec.NAME,
2485 x_catv_rec.TEXT,
2486 x_catv_rec.ATTRIBUTE_CATEGORY,
2487 x_catv_rec.ATTRIBUTE1,
2488 x_catv_rec.ATTRIBUTE2,
2489 x_catv_rec.ATTRIBUTE3,
2490 x_catv_rec.ATTRIBUTE4,
2491 x_catv_rec.ATTRIBUTE5,
2492 x_catv_rec.ATTRIBUTE6,
2493 x_catv_rec.ATTRIBUTE7,
2494 x_catv_rec.ATTRIBUTE8,
2495 x_catv_rec.ATTRIBUTE9,
2496 x_catv_rec.ATTRIBUTE10,
2497 x_catv_rec.ATTRIBUTE11,
2498 x_catv_rec.ATTRIBUTE12,
2499 x_catv_rec.ATTRIBUTE13,
2500 x_catv_rec.ATTRIBUTE14,
2501 x_catv_rec.ATTRIBUTE15,
2502 x_catv_rec.CAT_TYPE;
2503
2504 l_no_data_found := c_catv_rec%NOTFOUND;
2505 CLOSE c_catv_rec;
2506 IF l_no_data_found THEN
2507 l_return_status := OKC_API.G_RET_STS_ERROR;
2508 RETURN(l_return_status);
2509 ELSE
2510 RETURN(l_return_status);
2511 END IF;
2512 EXCEPTION
2513 WHEN OTHERS THEN
2514 -- store SQL error message on message stack for caller
2515 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
2516 -- notify caller of an UNEXPECTED error
2517 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2518 RETURN(l_return_status);
2519
2520 END get_catv_rec;
2521
2522 ----------------------------------------------------------------------------
2523 --Function to populate the contract items record to be copied.
2524 ----------------------------------------------------------------------------
2525 FUNCTION get_cimv_rec(p_cim_id IN NUMBER,
2526 x_cimv_rec OUT NOCOPY cimv_rec_type) RETURN VARCHAR2 IS
2527 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2528 l_no_data_found BOOLEAN := TRUE;
2529
2530 CURSOR c_cimv_rec IS
2531 SELECT ID,
2532 CLE_ID,
2533 CHR_ID,
2534 CLE_ID_FOR,
2535 DNZ_CHR_ID,
2536 OBJECT1_ID1,
2537 OBJECT1_ID2,
2538 JTOT_OBJECT1_CODE,
2539 UOM_CODE,
2540 EXCEPTION_YN,
2541 NUMBER_OF_ITEMS,
2542 PRICED_ITEM_YN
2543 FROM OKC_K_ITEMS_V
2544 WHERE ID = p_cim_id;
2545
2546 BEGIN
2547 OPEN c_cimv_rec;
2548 FETCH c_cimv_rec
2549 INTO x_cimv_rec.ID,
2550 x_cimv_rec.CLE_ID,
2551 x_cimv_rec.CHR_ID,
2552 x_cimv_rec.CLE_ID_FOR,
2553 x_cimv_rec.DNZ_CHR_ID,
2554 x_cimv_rec.OBJECT1_ID1,
2555 x_cimv_rec.OBJECT1_ID2,
2556 x_cimv_rec.JTOT_OBJECT1_CODE,
2557 x_cimv_rec.UOM_CODE,
2558 x_cimv_rec.EXCEPTION_YN,
2559 x_cimv_rec.NUMBER_OF_ITEMS,
2560 x_cimv_rec.PRICED_ITEM_YN;
2561
2562
2563 l_no_data_found := c_cimv_rec%NOTFOUND;
2564 CLOSE c_cimv_rec;
2565 IF l_no_data_found THEN
2566 l_return_status := OKC_API.G_RET_STS_ERROR;
2567 RETURN(l_return_status);
2568 ELSE
2569 RETURN(l_return_status);
2570 END IF;
2571 EXCEPTION
2572 WHEN OTHERS THEN
2573 -- store SQL error message on message stack for caller
2574 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
2575 -- notify caller of an UNEXPECTED error
2576 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2577 RETURN(l_return_status);
2578
2579 END get_cimv_rec;
2580 ----------------------------------------------------------------------------
2581 --Function to populate the contract access record to be copied.
2582 ----------------------------------------------------------------------------
2583 FUNCTION get_cacv_rec(p_cac_id IN NUMBER,
2584 x_cacv_rec OUT NOCOPY cacv_rec_type) RETURN VARCHAR2 IS
2585 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2586 l_no_data_found BOOLEAN := TRUE;
2587
2588 CURSOR c_cacv_rec IS
2589 SELECT ID,
2590 GROUP_ID,
2591 CHR_ID,
2592 RESOURCE_ID,
2593 ACCESS_LEVEL
2594 FROM OKC_K_ACCESSES_V
2595 WHERE ID = p_cac_id;
2596 BEGIN
2597 OPEN c_cacv_rec;
2598 FETCH c_cacv_rec
2599 INTO x_cacv_rec.ID,
2600 x_cacv_rec.GROUP_ID,
2601 x_cacv_rec.CHR_ID,
2602 x_cacv_rec.RESOURCE_ID,
2603 x_cacv_rec.ACCESS_LEVEL;
2604
2605 l_no_data_found := c_cacv_rec%NOTFOUND;
2606 CLOSE c_cacv_rec;
2607 IF l_no_data_found THEN
2608 l_return_status := OKC_API.G_RET_STS_ERROR;
2609 RETURN(l_return_status);
2610 ELSE
2611 RETURN(l_return_status);
2612 END IF;
2613 EXCEPTION
2614 WHEN OTHERS THEN
2615 -- store SQL error message on message stack for caller
2616 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
2617 -- notify caller of an UNEXPECTED error
2618 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2619 RETURN(l_return_status);
2620
2621 END get_cacv_rec;
2622 ----------------------------------------------------------------------------
2623 --Function to populate the contract party roles record to be copied.
2624 ----------------------------------------------------------------------------
2625 FUNCTION get_cplv_rec(p_cpl_id IN NUMBER,
2626 x_cplv_rec OUT NOCOPY cplv_rec_type) RETURN VARCHAR2 IS
2627 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2628 l_no_data_found BOOLEAN := TRUE;
2629
2630 CURSOR c_cplv_rec IS
2631 SELECT ID,
2632 SFWT_FLAG,
2633 CHR_ID,
2634 CLE_ID,
2635 RLE_CODE,
2636 DNZ_CHR_ID,
2637 OBJECT1_ID1,
2638 OBJECT1_ID2,
2639 JTOT_OBJECT1_CODE,
2640 COGNOMEN,
2641 CODE,
2642 FACILITY,
2643 MINORITY_GROUP_LOOKUP_CODE,
2644 SMALL_BUSINESS_FLAG,
2645 WOMEN_OWNED_FLAG,
2646 ALIAS,
2647 ATTRIBUTE_CATEGORY,
2648 ATTRIBUTE1,
2649 ATTRIBUTE2,
2650 ATTRIBUTE3,
2651 ATTRIBUTE4,
2652 ATTRIBUTE5,
2653 ATTRIBUTE6,
2654 ATTRIBUTE7,
2655 ATTRIBUTE8,
2656 ATTRIBUTE9,
2657 ATTRIBUTE10,
2658 ATTRIBUTE11,
2659 ATTRIBUTE12,
2660 ATTRIBUTE13,
2661 ATTRIBUTE14,
2662 ATTRIBUTE15
2663 FROM OKC_K_PARTY_ROLES_V
2664 WHERE ID = p_cpl_id;
2665 BEGIN
2666 OPEN c_cplv_rec;
2667 FETCH c_cplv_rec
2668 INTO x_cplv_rec.ID,
2669 x_cplv_rec.SFWT_FLAG,
2670 x_cplv_rec.CHR_ID,
2671 x_cplv_rec.CLE_ID,
2672 x_cplv_rec.RLE_CODE,
2673 x_cplv_rec.DNZ_CHR_ID,
2674 x_cplv_rec.OBJECT1_ID1,
2675 x_cplv_rec.OBJECT1_ID2,
2676 x_cplv_rec.JTOT_OBJECT1_CODE,
2677 x_cplv_rec.COGNOMEN,
2678 x_cplv_rec.CODE,
2679 x_cplv_rec.FACILITY,
2680 x_cplv_rec.MINORITY_GROUP_LOOKUP_CODE,
2681 x_cplv_rec.SMALL_BUSINESS_FLAG,
2682 x_cplv_rec.WOMEN_OWNED_FLAG,
2683 x_cplv_rec.ALIAS,
2684 x_cplv_rec.ATTRIBUTE_CATEGORY,
2685 x_cplv_rec.ATTRIBUTE1,
2686 x_cplv_rec.ATTRIBUTE2,
2687 x_cplv_rec.ATTRIBUTE3,
2688 x_cplv_rec.ATTRIBUTE4,
2689 x_cplv_rec.ATTRIBUTE5,
2690 x_cplv_rec.ATTRIBUTE6,
2691 x_cplv_rec.ATTRIBUTE7,
2692 x_cplv_rec.ATTRIBUTE8,
2693 x_cplv_rec.ATTRIBUTE9,
2694 x_cplv_rec.ATTRIBUTE10,
2695 x_cplv_rec.ATTRIBUTE11,
2696 x_cplv_rec.ATTRIBUTE12,
2697 x_cplv_rec.ATTRIBUTE13,
2698 x_cplv_rec.ATTRIBUTE14,
2699 x_cplv_rec.ATTRIBUTE15;
2700
2701 l_no_data_found := c_cplv_rec%NOTFOUND;
2702 CLOSE c_cplv_rec;
2703 IF l_no_data_found THEN
2704 l_return_status := OKC_API.G_RET_STS_ERROR;
2705 RETURN(l_return_status);
2706 ELSE
2707 RETURN(l_return_status);
2708 END IF;
2709 EXCEPTION
2710 WHEN OTHERS THEN
2711 -- store SQL error message on message stack for caller
2712 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
2713 -- notify caller of an UNEXPECTED error
2714 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2715 RETURN(l_return_status);
2716
2717 END get_cplv_rec;
2718 ----------------------------------------------------------------------------
2719 --Function to populate the contract process record to be copied.
2720 ----------------------------------------------------------------------------
2721 FUNCTION get_cpsv_rec(p_cps_id IN NUMBER,
2722 x_cpsv_rec OUT NOCOPY cpsv_rec_type) RETURN VARCHAR2 IS
2723 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2724 l_no_data_found BOOLEAN := TRUE;
2725
2726 CURSOR c_cpsv_rec IS
2727 SELECT ID,
2728 PDF_ID,
2729 CHR_ID,
2730 USER_ID,
2731 CRT_ID,
2732 PROCESS_ID,
2733 IN_PROCESS_YN,
2734 ATTRIBUTE_CATEGORY,
2735 ATTRIBUTE1,
2736 ATTRIBUTE2,
2737 ATTRIBUTE3,
2738 ATTRIBUTE4,
2739 ATTRIBUTE5,
2740 ATTRIBUTE6,
2741 ATTRIBUTE7,
2742 ATTRIBUTE8,
2743 ATTRIBUTE9,
2744 ATTRIBUTE10,
2745 ATTRIBUTE11,
2746 ATTRIBUTE12,
2747 ATTRIBUTE13,
2748 ATTRIBUTE14,
2749 ATTRIBUTE15
2750 FROM OKC_K_PROCESSES_V
2751 WHERE ID = p_cps_id;
2752 BEGIN
2753 OPEN c_cpsv_rec;
2754 FETCH c_cpsv_rec
2755 INTO x_cpsv_rec.ID,
2756 x_cpsv_rec.PDF_ID,
2757 x_cpsv_rec.CHR_ID,
2758 x_cpsv_rec.USER_ID,
2759 x_cpsv_rec.CRT_ID,
2760 x_cpsv_rec.PROCESS_ID,
2761 x_cpsv_rec.IN_PROCESS_YN,
2762 x_cpsv_rec.ATTRIBUTE_CATEGORY,
2763 x_cpsv_rec.ATTRIBUTE1,
2764 x_cpsv_rec.ATTRIBUTE2,
2765 x_cpsv_rec.ATTRIBUTE3,
2766 x_cpsv_rec.ATTRIBUTE4,
2767 x_cpsv_rec.ATTRIBUTE5,
2768 x_cpsv_rec.ATTRIBUTE6,
2769 x_cpsv_rec.ATTRIBUTE7,
2770 x_cpsv_rec.ATTRIBUTE8,
2771 x_cpsv_rec.ATTRIBUTE9,
2772 x_cpsv_rec.ATTRIBUTE10,
2773 x_cpsv_rec.ATTRIBUTE11,
2774 x_cpsv_rec.ATTRIBUTE12,
2775 x_cpsv_rec.ATTRIBUTE13,
2776 x_cpsv_rec.ATTRIBUTE14,
2777 x_cpsv_rec.ATTRIBUTE15;
2778
2779 l_no_data_found := c_cpsv_rec%NOTFOUND;
2780 CLOSE c_cpsv_rec;
2781 IF l_no_data_found THEN
2782 l_return_status := OKC_API.G_RET_STS_ERROR;
2783 RETURN(l_return_status);
2784 ELSE
2785 RETURN(l_return_status);
2786 END IF;
2787 EXCEPTION
2788 WHEN OTHERS THEN
2789 -- store SQL error message on message stack for caller
2790 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
2791 -- notify caller of an UNEXPECTED error
2792 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2793 RETURN(l_return_status);
2794
2795 END get_cpsv_rec;
2796 ----------------------------------------------------------------------------
2797 --Function to populate the contract group record to be copied.
2798 ----------------------------------------------------------------------------
2799 FUNCTION get_cgcv_rec(p_cgc_id IN NUMBER,
2800 x_cgcv_rec OUT NOCOPY cgcv_rec_type) RETURN VARCHAR2 IS
2801 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2802 l_no_data_found BOOLEAN := TRUE;
2803
2804 CURSOR c_cgcv_rec IS
2805 SELECT ID,
2806 CGP_PARENT_ID,
2807 INCLUDED_CHR_ID,
2808 INCLUDED_CGP_ID
2809 FROM OKC_K_GRPINGS_V
2810 WHERE ID = p_cgc_id;
2811 BEGIN
2812 OPEN c_cgcv_rec;
2813 FETCH c_cgcv_rec
2814 INTO x_cgcv_rec.ID,
2815 x_cgcv_rec.CGP_PARENT_ID,
2816 x_cgcv_rec.INCLUDED_CHR_ID,
2817 x_cgcv_rec.INCLUDED_CGP_ID;
2818 l_no_data_found := c_cgcv_rec%NOTFOUND;
2819 CLOSE c_cgcv_rec;
2820 IF l_no_data_found THEN
2821 l_return_status := OKC_API.G_RET_STS_ERROR;
2822 RETURN(l_return_status);
2823 ELSE
2824 RETURN(l_return_status);
2825 END IF;
2826 EXCEPTION
2827 WHEN OTHERS THEN
2828 -- store SQL error message on message stack for caller
2829 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
2830 -- notify caller of an UNEXPECTED error
2831 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2832 RETURN(l_return_status);
2833
2834 END get_cgcv_rec;
2835 ----------------------------------------------------------------------------
2836 --Function to populate the condition headers record to be copied.
2837 ----------------------------------------------------------------------------
2838 FUNCTION get_cnhv_rec(p_cnh_id IN NUMBER,
2839 x_cnhv_rec OUT NOCOPY cnhv_rec_type) RETURN VARCHAR2 IS
2840 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2841 l_no_data_found BOOLEAN := TRUE;
2842
2843 CURSOR c_cnhv_rec IS
2844 SELECT ID,
2845 SFWT_FLAG,
2846 ACN_ID,
2847 COUNTER_GROUP_ID,
2848 DESCRIPTION,
2849 SHORT_DESCRIPTION,
2850 COMMENTS,
2851 ONE_TIME_YN,
2852 NAME,
2853 CONDITION_VALID_YN,
2854 BEFORE_AFTER,
2855 TRACKED_YN,
2856 CNH_VARIANCE,
2857 DNZ_CHR_ID,
2858 TEMPLATE_YN,
2859 DATE_ACTIVE,
2860 OBJECT_ID,
2861 DATE_INACTIVE,
2862 JTOT_OBJECT_CODE,
2863 TASK_OWNER_ID,
2864 CNH_TYPE,
2865 ATTRIBUTE_CATEGORY,
2866 ATTRIBUTE1,
2867 ATTRIBUTE2,
2868 ATTRIBUTE3,
2869 ATTRIBUTE4,
2870 ATTRIBUTE5,
2871 ATTRIBUTE6,
2872 ATTRIBUTE7,
2873 ATTRIBUTE8,
2874 ATTRIBUTE9,
2875 ATTRIBUTE10,
2876 ATTRIBUTE11,
2877 ATTRIBUTE12,
2878 ATTRIBUTE13,
2879 ATTRIBUTE14,
2880 ATTRIBUTE15
2881 FROM OKC_CONDITION_HEADERS_V
2882 WHERE ID = p_cnh_id;
2883 BEGIN
2884 OPEN c_cnhv_rec;
2885 FETCH c_cnhv_rec
2886 INTO x_cnhv_rec.ID,
2887 x_cnhv_rec.SFWT_FLAG,
2888 x_cnhv_rec.ACN_ID,
2889 x_cnhv_rec.COUNTER_GROUP_ID,
2890 x_cnhv_rec.DESCRIPTION,
2891 x_cnhv_rec.SHORT_DESCRIPTION,
2892 x_cnhv_rec.COMMENTS,
2893 x_cnhv_rec.ONE_TIME_YN,
2894 x_cnhv_rec.NAME,
2895 x_cnhv_rec.CONDITION_VALID_YN,
2896 x_cnhv_rec.BEFORE_AFTER,
2897 x_cnhv_rec.TRACKED_YN,
2898 x_cnhv_rec.CNH_VARIANCE,
2899 x_cnhv_rec.DNZ_CHR_ID,
2900 x_cnhv_rec.TEMPLATE_YN,
2901 x_cnhv_rec.DATE_ACTIVE,
2902 x_cnhv_rec.OBJECT_ID,
2903 x_cnhv_rec.DATE_INACTIVE,
2904 x_cnhv_rec.JTOT_OBJECT_CODE,
2905 x_cnhv_rec.TASK_OWNER_ID,
2906 x_cnhv_rec.CNH_TYPE,
2907 x_cnhv_rec.ATTRIBUTE_CATEGORY,
2908 x_cnhv_rec.ATTRIBUTE1,
2909 x_cnhv_rec.ATTRIBUTE2,
2910 x_cnhv_rec.ATTRIBUTE3,
2911 x_cnhv_rec.ATTRIBUTE4,
2912 x_cnhv_rec.ATTRIBUTE5,
2913 x_cnhv_rec.ATTRIBUTE6,
2914 x_cnhv_rec.ATTRIBUTE7,
2915 x_cnhv_rec.ATTRIBUTE8,
2916 x_cnhv_rec.ATTRIBUTE9,
2917 x_cnhv_rec.ATTRIBUTE10,
2918 x_cnhv_rec.ATTRIBUTE11,
2919 x_cnhv_rec.ATTRIBUTE12,
2920 x_cnhv_rec.ATTRIBUTE13,
2921 x_cnhv_rec.ATTRIBUTE14,
2922 x_cnhv_rec.ATTRIBUTE15;
2923
2924 l_no_data_found := c_cnhv_rec%NOTFOUND;
2925 CLOSE c_cnhv_rec;
2926 IF l_no_data_found THEN
2927 l_return_status := OKC_API.G_RET_STS_ERROR;
2928 RETURN(l_return_status);
2929 ELSE
2930 RETURN(l_return_status);
2931 END IF;
2932 EXCEPTION
2933 WHEN OTHERS THEN
2934 -- store SQL error message on message stack for caller
2935 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
2936 -- notify caller of an UNEXPECTED error
2937 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2938 RETURN(l_return_status);
2939
2940 END get_cnhv_rec;
2941 ----------------------------------------------------------------------------
2942 --Function to populate the condition lines record to be copied.
2943 ----------------------------------------------------------------------------
2944 FUNCTION get_cnlv_rec(p_cnl_id IN NUMBER,
2945 x_cnlv_rec OUT NOCOPY cnlv_rec_type) RETURN VARCHAR2 IS
2946 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2947 l_no_data_found BOOLEAN := TRUE;
2948
2949 CURSOR c_cnlv_rec IS
2950 SELECT ID,
2951 SFWT_FLAG,
2952 START_AT,
2953 CNH_ID,
2954 PDF_ID,
2955 AAE_ID,
2956 LEFT_CTR_MASTER_ID,
2957 RIGHT_CTR_MASTER_ID,
2958 LEFT_COUNTER_ID,
2959 RIGHT_COUNTER_ID,
2960 DNZ_CHR_ID,
2961 SORTSEQ,
2962 CNL_TYPE,
2963 DESCRIPTION,
2964 LEFT_PARENTHESIS,
2965 RELATIONAL_OPERATOR,
2966 RIGHT_PARENTHESIS,
2967 LOGICAL_OPERATOR,
2968 TOLERANCE,
2969 RIGHT_OPERAND,
2970 ATTRIBUTE_CATEGORY,
2971 ATTRIBUTE1,
2972 ATTRIBUTE2,
2973 ATTRIBUTE3,
2974 ATTRIBUTE4,
2975 ATTRIBUTE5,
2976 ATTRIBUTE6,
2977 ATTRIBUTE7,
2978 ATTRIBUTE8,
2979 ATTRIBUTE9,
2980 ATTRIBUTE10,
2981 ATTRIBUTE11,
2982 ATTRIBUTE12,
2983 ATTRIBUTE13,
2984 ATTRIBUTE14,
2985 ATTRIBUTE15
2986 FROM OKC_CONDITION_LINES_V
2987 WHERE ID = p_cnl_id;
2988 BEGIN
2989 OPEN c_cnlv_rec;
2990 FETCH c_cnlv_rec
2991 INTO x_cnlv_rec.ID,
2992 x_cnlv_rec.SFWT_FLAG,
2993 x_cnlv_rec.START_AT,
2994 x_cnlv_rec.CNH_ID,
2995 x_cnlv_rec.PDF_ID,
2996 x_cnlv_rec.AAE_ID,
2997 x_cnlv_rec.LEFT_CTR_MASTER_ID,
2998 x_cnlv_rec.RIGHT_CTR_MASTER_ID,
2999 x_cnlv_rec.LEFT_COUNTER_ID,
3000 x_cnlv_rec.RIGHT_COUNTER_ID,
3001 x_cnlv_rec.DNZ_CHR_ID,
3002 x_cnlv_rec.SORTSEQ,
3003 x_cnlv_rec.CNL_TYPE,
3004 x_cnlv_rec.DESCRIPTION,
3005 x_cnlv_rec.LEFT_PARENTHESIS,
3006 x_cnlv_rec.RELATIONAL_OPERATOR,
3007 x_cnlv_rec.RIGHT_PARENTHESIS,
3008 x_cnlv_rec.LOGICAL_OPERATOR,
3009 x_cnlv_rec.TOLERANCE,
3010 x_cnlv_rec.RIGHT_OPERAND,
3011 x_cnlv_rec.ATTRIBUTE_CATEGORY,
3012 x_cnlv_rec.ATTRIBUTE1,
3013 x_cnlv_rec.ATTRIBUTE2,
3014 x_cnlv_rec.ATTRIBUTE3,
3015 x_cnlv_rec.ATTRIBUTE4,
3016 x_cnlv_rec.ATTRIBUTE5,
3017 x_cnlv_rec.ATTRIBUTE6,
3018 x_cnlv_rec.ATTRIBUTE7,
3019 x_cnlv_rec.ATTRIBUTE8,
3020 x_cnlv_rec.ATTRIBUTE9,
3021 x_cnlv_rec.ATTRIBUTE10,
3022 x_cnlv_rec.ATTRIBUTE11,
3023 x_cnlv_rec.ATTRIBUTE12,
3024 x_cnlv_rec.ATTRIBUTE13,
3025 x_cnlv_rec.ATTRIBUTE14,
3026 x_cnlv_rec.ATTRIBUTE15;
3027
3028 l_no_data_found := c_cnlv_rec%NOTFOUND;
3029 CLOSE c_cnlv_rec;
3030 IF l_no_data_found THEN
3031 l_return_status := OKC_API.G_RET_STS_ERROR;
3032 RETURN(l_return_status);
3033 ELSE
3034 RETURN(l_return_status);
3035 END IF;
3036 EXCEPTION
3037 WHEN OTHERS THEN
3038 -- store SQL error message on message stack for caller
3039 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
3040 -- notify caller of an UNEXPECTED error
3041 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3042 RETURN(l_return_status);
3043
3044 END get_cnlv_rec;
3045 ----------------------------------------------------------------------------
3046 --Function to populate the contacts record to be copied.
3047 ----------------------------------------------------------------------------
3048
3049 FUNCTION get_ctcv_rec(p_ctc_id IN NUMBER,
3050 x_ctcv_rec OUT NOCOPY ctcv_rec_type) RETURN VARCHAR2 IS
3051
3052 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3053 l_no_data_found BOOLEAN := TRUE;
3054
3055 CURSOR c_ctcv_rec IS
3056 SELECT ID,
3057 CPL_ID,
3058 CRO_CODE,
3059 DNZ_CHR_ID,
3060 CONTACT_SEQUENCE,
3061 OBJECT1_ID1,
3062 OBJECT1_ID2,
3063 JTOT_OBJECT1_CODE,
3064 ATTRIBUTE_CATEGORY,
3065 ATTRIBUTE1,
3066 ATTRIBUTE2,
3067 ATTRIBUTE3,
3068 ATTRIBUTE4,
3069 ATTRIBUTE5,
3070 ATTRIBUTE6,
3071 ATTRIBUTE7,
3072 ATTRIBUTE8,
3073 ATTRIBUTE9,
3074 ATTRIBUTE10,
3075 ATTRIBUTE11,
3076 ATTRIBUTE12,
3077 ATTRIBUTE13,
3078 ATTRIBUTE14,
3079 ATTRIBUTE15
3080 FROM OKC_CONTACTS_V
3081 WHERE ID = p_ctc_id;
3082 BEGIN
3083 OPEN c_ctcv_rec;
3084 FETCH c_ctcv_rec
3085 INTO x_ctcv_rec.ID,
3086 x_ctcv_rec.CPL_ID,
3087 x_ctcv_rec.CRO_CODE,
3088 x_ctcv_rec.DNZ_CHR_ID,
3089 x_ctcv_rec.CONTACT_SEQUENCE,
3090 x_ctcv_rec.OBJECT1_ID1,
3091 x_ctcv_rec.OBJECT1_ID2,
3092 x_ctcv_rec.JTOT_OBJECT1_CODE,
3093 x_ctcv_rec.ATTRIBUTE_CATEGORY,
3094 x_ctcv_rec.ATTRIBUTE1,
3095 x_ctcv_rec.ATTRIBUTE2,
3096 x_ctcv_rec.ATTRIBUTE3,
3097 x_ctcv_rec.ATTRIBUTE4,
3098 x_ctcv_rec.ATTRIBUTE5,
3099 x_ctcv_rec.ATTRIBUTE6,
3100 x_ctcv_rec.ATTRIBUTE7,
3101 x_ctcv_rec.ATTRIBUTE8,
3102 x_ctcv_rec.ATTRIBUTE9,
3103 x_ctcv_rec.ATTRIBUTE10,
3104 x_ctcv_rec.ATTRIBUTE11,
3105 x_ctcv_rec.ATTRIBUTE12,
3106 x_ctcv_rec.ATTRIBUTE13,
3107 x_ctcv_rec.ATTRIBUTE14,
3108 x_ctcv_rec.ATTRIBUTE15;
3109
3110 l_no_data_found := c_ctcv_rec%NOTFOUND;
3111 CLOSE c_ctcv_rec;
3112 IF l_no_data_found THEN
3113 l_return_status := OKC_API.G_RET_STS_ERROR;
3114 RETURN(l_return_status);
3115 ELSE
3116 RETURN(l_return_status);
3117 END IF;
3118 EXCEPTION
3119 WHEN OTHERS THEN
3120 -- store SQL error message on message stack for caller
3121 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
3122 -- notify caller of an UNEXPECTED error
3123 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3124 RETURN(l_return_status);
3125
3126 END get_ctcv_rec;
3127
3128 ----------------------------------------------------------------------------
3129 --Function to populate the rules record to be copied.
3130 --p_old_cle_id is for passing old line id
3131 --x_klnv_rec will hold the table of records with new rules to be copied
3132 ----------------------------------------------------------------------------
3133 FUNCTION get_klnv_rec(p_old_cle_id IN NUMBER,
3134 x_klnv_rec OUT NOCOPY klnv_rec_type)
3135 RETURN VARCHAR2 IS
3136 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3137 l_no_data_found BOOLEAN := TRUE;
3138 l_count NUMBER := 1;
3139
3140 CURSOR c_klnv_rec(cp_cle_id IN NUMBER) IS
3141 SELECT
3142 id
3143 ,cle_id
3144 ,dnz_chr_id
3145 ,discount_list
3146 ,acct_rule_id
3147 ,payment_type
3148 ,cc_no
3149 ,cc_expiry_date
3150 ,cc_bank_acct_id
3151 ,cc_auth_code
3152 ,commitment_id
3153 ,locked_price_list_id
3154 ,usage_est_yn
3155 ,usage_est_method
3156 ,usage_est_start_date
3157 ,termn_method
3158 ,ubt_amount
3159 ,credit_amount
3160 ,suppressed_credit
3161 ,override_amount
3162 ,cust_po_number_req_yn
3163 ,cust_po_number
3164 ,grace_duration
3165 ,grace_period
3166 ,inv_print_flag
3167 ,price_uom
3168 ,tax_amount
3169 ,tax_inclusive_yn
3170 ,tax_status
3171 ,tax_code
3172 ,tax_exemption_id
3173 ,ib_trans_type
3174 ,ib_trans_date
3175 ,prod_price
3176 ,service_price
3177 ,clvl_list_price
3178 ,clvl_quantity
3179 ,clvl_extended_amt
3180 ,clvl_uom_code
3181 ,toplvl_operand_code
3182 ,toplvl_operand_val
3183 ,toplvl_quantity
3184 ,toplvl_uom_code
3185 ,toplvl_adj_price
3186 ,toplvl_price_qty
3187 ,averaging_interval
3188 ,settlement_interval
3189 ,minimum_quantity
3190 ,default_quantity
3191 ,amcv_flag
3192 ,fixed_quantity
3193 ,usage_duration
3194 ,usage_period
3195 ,level_yn
3196 ,usage_type
3197 ,uom_quantified
3198 ,base_reading
3199 ,billing_schedule_type
3200 ,full_credit
3201 ,locked_price_list_line_id
3202 ,break_uom
3203 ,prorate
3204 ,coverage_type
3205 ,exception_cov_id
3206 ,limit_uom_quantified
3207 ,discount_amount
3208 ,discount_percent
3209 ,offset_duration
3210 ,offset_period
3211 ,incident_severity_id
3212 ,pdf_id
3213 ,work_thru_yn
3214 ,react_active_yn
3215 ,transfer_option
3216 ,prod_upgrade_yn
3217 ,inheritance_type
3218 ,pm_program_id
3219 ,pm_conf_req_yn
3220 ,pm_sch_exists_yn
3221 ,allow_bt_discount
3222 ,apply_default_timezone
3223 ,sync_date_install
3224 ,sfwt_flag
3225 ,invoice_text
3226 ,ib_trx_details
3227 ,status_text
3228 ,react_time_name
3229 ,object_version_number
3230 ,security_group_id
3231 ,request_id
3232 ,created_by
3233 ,creation_date
3234 ,last_updated_by
3235 ,last_update_date
3236 ,last_update_login
3237 ,trxn_extension_id
3238 ,tax_classification_code
3239 ,exempt_certificate_number
3240 ,exempt_reason_code
3241 ,coverage_id
3242 ,standard_cov_yn
3243 ,orig_system_id1
3244 ,orig_system_reference1
3245 ,orig_system_source_code
3246 ,counter_value_id
3247 FROM OKS_K_LINES_V
3248 WHERE cle_id = cp_cle_id;
3249
3250 BEGIN
3251
3252 OPEN c_klnv_rec(p_old_cle_id);
3253 FETCH c_klnv_rec INTO
3254 x_klnv_rec.id
3255 ,x_klnv_rec.cle_id
3256 ,x_klnv_rec.dnz_chr_id
3257 ,x_klnv_rec.discount_list
3258 ,x_klnv_rec.acct_rule_id
3259 ,x_klnv_rec.payment_type
3260 ,x_klnv_rec.cc_no
3261 ,x_klnv_rec.cc_expiry_date
3262 ,x_klnv_rec.cc_bank_acct_id
3263 ,x_klnv_rec.cc_auth_code
3264 ,x_klnv_rec.commitment_id
3265 ,x_klnv_rec.locked_price_list_id
3266 ,x_klnv_rec.usage_est_yn
3267 ,x_klnv_rec.usage_est_method
3268 ,x_klnv_rec.usage_est_start_date
3269 ,x_klnv_rec.termn_method
3270 ,x_klnv_rec.ubt_amount
3271 ,x_klnv_rec.credit_amount
3272 ,x_klnv_rec.suppressed_credit
3273 ,x_klnv_rec.override_amount
3274 ,x_klnv_rec.cust_po_number_req_yn
3275 ,x_klnv_rec.cust_po_number
3276 ,x_klnv_rec.grace_duration
3277 ,x_klnv_rec.grace_period
3278 ,x_klnv_rec.inv_print_flag
3279 ,x_klnv_rec.price_uom
3280 ,x_klnv_rec.tax_amount
3281 ,x_klnv_rec.tax_inclusive_yn
3282 ,x_klnv_rec.tax_status
3283 ,x_klnv_rec.tax_code
3284 ,x_klnv_rec.tax_exemption_id
3285 ,x_klnv_rec.ib_trans_type
3286 ,x_klnv_rec.ib_trans_date
3287 ,x_klnv_rec.prod_price
3288 ,x_klnv_rec.service_price
3289 ,x_klnv_rec.clvl_list_price
3290 ,x_klnv_rec.clvl_quantity
3291 ,x_klnv_rec.clvl_extended_amt
3292 ,x_klnv_rec.clvl_uom_code
3293 ,x_klnv_rec.toplvl_operand_code
3294 ,x_klnv_rec.toplvl_operand_val
3295 ,x_klnv_rec.toplvl_quantity
3296 ,x_klnv_rec.toplvl_uom_code
3297 ,x_klnv_rec.toplvl_adj_price
3298 ,x_klnv_rec.toplvl_price_qty
3299 ,x_klnv_rec.averaging_interval
3300 ,x_klnv_rec.settlement_interval
3301 ,x_klnv_rec.minimum_quantity
3302 ,x_klnv_rec.default_quantity
3303 ,x_klnv_rec.amcv_flag
3304 ,x_klnv_rec.fixed_quantity
3305 ,x_klnv_rec.usage_duration
3306 ,x_klnv_rec.usage_period
3307 ,x_klnv_rec.level_yn
3308 ,x_klnv_rec.usage_type
3309 ,x_klnv_rec.uom_quantified
3310 ,x_klnv_rec.base_reading
3311 ,x_klnv_rec.billing_schedule_type
3312 ,x_klnv_rec.full_credit
3313 ,x_klnv_rec.locked_price_list_line_id
3314 ,x_klnv_rec.break_uom
3315 ,x_klnv_rec.prorate
3316 ,x_klnv_rec.coverage_type
3317 ,x_klnv_rec.exception_cov_id
3318 ,x_klnv_rec.limit_uom_quantified
3319 ,x_klnv_rec.discount_amount
3320 ,x_klnv_rec.discount_percent
3321 ,x_klnv_rec.offset_duration
3322 ,x_klnv_rec.offset_period
3323 ,x_klnv_rec.incident_severity_id
3324 ,x_klnv_rec.pdf_id
3325 ,x_klnv_rec.work_thru_yn
3326 ,x_klnv_rec.react_active_yn
3327 ,x_klnv_rec.transfer_option
3328 ,x_klnv_rec.prod_upgrade_yn
3329 ,x_klnv_rec.inheritance_type
3330 ,x_klnv_rec.pm_program_id
3331 ,x_klnv_rec.pm_conf_req_yn
3332 ,x_klnv_rec.pm_sch_exists_yn
3333 ,x_klnv_rec.allow_bt_discount
3334 ,x_klnv_rec.apply_default_timezone
3335 ,x_klnv_rec.sync_date_install
3336 ,x_klnv_rec.sfwt_flag
3337 ,x_klnv_rec.invoice_text
3338 ,x_klnv_rec.ib_trx_details
3339 ,x_klnv_rec.status_text
3340 ,x_klnv_rec.react_time_name
3341 ,x_klnv_rec.object_version_number
3342 ,x_klnv_rec.security_group_id
3343 ,x_klnv_rec.request_id
3344 ,x_klnv_rec.created_by
3345 ,x_klnv_rec.creation_date
3346 ,x_klnv_rec.last_updated_by
3347 ,x_klnv_rec.last_update_date
3348 ,x_klnv_rec.last_update_login
3349 ,x_klnv_rec.trxn_extension_id
3350 ,x_klnv_rec.tax_classification_code
3351 ,x_klnv_rec.exempt_certificate_number
3352 ,x_klnv_rec.exempt_reason_code
3353 ,x_klnv_rec.coverage_id
3354 ,x_klnv_rec.standard_cov_yn
3355 ,x_klnv_rec.orig_system_id1
3356 ,x_klnv_rec.orig_system_reference1
3357 ,x_klnv_rec.orig_system_source_code
3358 ,x_klnv_rec.counter_value_id;
3359
3360 l_no_data_found := c_klnv_rec%NOTFOUND;
3361
3362 CLOSE c_klnv_rec;
3363
3364 IF l_no_data_found THEN
3365 l_return_status := OKC_API.G_RET_STS_ERROR;
3366 RETURN(l_return_status);
3367 ELSE
3368 RETURN(l_return_status);
3369 END IF;
3370 EXCEPTION
3371 WHEN OTHERS THEN
3372 -- store SQL error message on message stack for caller
3373 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
3374 -- notify caller of an UNEXPECTED error
3375 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3376 RETURN(l_return_status);
3377 END get_klnv_rec;
3378 ----------------------------------------------------------------------------
3379 --Function to populate the lines record to be copied.
3380 ----------------------------------------------------------------------------
3381 FUNCTION get_clev_rec(p_cle_id IN NUMBER,
3382 x_clev_rec OUT NOCOPY clev_rec_type)
3383 RETURN VARCHAR2 IS
3384 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3385 l_no_data_found BOOLEAN := TRUE;
3386
3387
3388 CURSOR c_clev_rec IS
3389 SELECT
3390 id
3391 ,object_version_number
3392 ,sfwt_flag
3393 ,chr_id
3394 ,cle_id
3395 ,cle_id_renewed
3396 ,cle_id_renewed_to
3397 ,lse_id
3398 ,line_number
3399 ,sts_code
3400 ,display_sequence
3401 ,trn_code
3402 ,dnz_chr_id
3403 ,comments
3404 ,item_description
3405 ,oke_boe_description
3406 ,cognomen
3407 ,hidden_ind
3408 ,price_unit
3409 ,price_unit_percent
3410 ,price_negotiated
3411 ,price_negotiated_renewed
3412 ,price_level_ind
3413 ,invoice_line_level_ind
3414 ,dpas_rating
3415 ,block23text
3416 ,exception_yn
3417 ,template_used
3418 ,date_terminated
3419 ,name
3420 ,start_date
3421 ,end_date
3422 ,date_renewed
3423 ,upg_orig_system_ref
3424 ,upg_orig_system_ref_id
3425 ,orig_system_source_code
3426 ,orig_system_id1
3427 ,orig_system_reference1
3428 ,attribute_category
3429 ,attribute1
3430 ,attribute2
3431 ,attribute3
3432 ,attribute4
3433 ,attribute5
3434 ,attribute6
3435 ,attribute7
3436 ,attribute8
3437 ,attribute9
3438 ,attribute10
3439 ,attribute11
3440 ,attribute12
3441 ,attribute13
3442 ,attribute14
3443 ,attribute15
3444 ,created_by
3445 ,creation_date
3446 ,last_updated_by
3447 ,last_update_date
3448 ,price_type
3449 ,currency_code
3450 ,currency_code_renewed
3451 ,last_update_login
3452 ,request_id
3453 ,program_application_id
3454 ,program_id
3455 ,program_update_date
3456 ,price_list_id
3457 ,pricing_date
3458 ,price_list_line_id
3459 ,line_list_price
3460 ,item_to_price_yn
3461 ,price_basis_yn
3462 ,config_header_id
3463 ,config_revision_number
3464 ,config_complete_yn
3465 ,config_valid_yn
3466 ,config_top_model_line_id
3467 ,config_item_type
3468 ,CONFIG_ITEM_ID
3469 ,service_item_yn
3470 ,ph_pricing_type
3471 ,ph_price_break_basis
3472 ,ph_min_qty
3473 ,ph_min_amt
3474 ,ph_qp_reference_id
3475 ,ph_value
3476 ,ph_enforce_price_list_yn
3477 ,ph_adjustment
3478 ,ph_integrated_with_qp
3479 ,cust_acct_id
3480 ,bill_to_site_use_id
3481 ,inv_rule_id
3482 ,line_renewal_type_code
3483 ,ship_to_site_use_id
3484 ,payment_term_id
3485 ,date_cancelled
3486 ,term_cancel_source
3487 ,cancelled_amount
3488 ,annualized_factor
3489 ,payment_instruction_type
3490 FROM OKC_K_LINES_V
3491 WHERE id = p_cle_id;
3492
3493 BEGIN
3494
3495 OPEN c_clev_rec;
3496 FETCH c_clev_rec INTO
3497 x_clev_rec.id
3498 ,x_clev_rec.object_version_number
3499 ,x_clev_rec.sfwt_flag
3500 ,x_clev_rec.chr_id
3501 ,x_clev_rec.cle_id
3502 ,x_clev_rec.cle_id_renewed
3503 ,x_clev_rec.cle_id_renewed_to
3504 ,x_clev_rec.lse_id
3505 ,x_clev_rec.line_number
3506 ,x_clev_rec.sts_code
3507 ,x_clev_rec.display_sequence
3508 ,x_clev_rec.trn_code
3509 ,x_clev_rec.dnz_chr_id
3510 ,x_clev_rec.comments
3511 ,x_clev_rec.item_description
3512 ,x_clev_rec.oke_boe_description
3513 ,x_clev_rec.cognomen
3514 ,x_clev_rec.hidden_ind
3515 ,x_clev_rec.price_unit
3516 ,x_clev_rec.price_unit_percent
3517 ,x_clev_rec.price_negotiated
3518 ,x_clev_rec.price_negotiated_renewed
3519 ,x_clev_rec.price_level_ind
3520 ,x_clev_rec.invoice_line_level_ind
3521 ,x_clev_rec.dpas_rating
3522 ,x_clev_rec.block23text
3523 ,x_clev_rec.exception_yn
3524 ,x_clev_rec.template_used
3525 ,x_clev_rec.date_terminated
3526 ,x_clev_rec.name
3527 ,x_clev_rec.start_date
3528 ,x_clev_rec.end_date
3529 ,x_clev_rec.date_renewed
3530 ,x_clev_rec.upg_orig_system_ref
3531 ,x_clev_rec.upg_orig_system_ref_id
3532 ,x_clev_rec.orig_system_source_code
3533 ,x_clev_rec.orig_system_id1
3534 ,x_clev_rec.orig_system_reference1
3535 ,x_clev_rec.attribute_category
3536 ,x_clev_rec.attribute1
3537 ,x_clev_rec.attribute2
3538 ,x_clev_rec.attribute3
3539 ,x_clev_rec.attribute4
3540 ,x_clev_rec.attribute5
3541 ,x_clev_rec.attribute6
3542 ,x_clev_rec.attribute7
3543 ,x_clev_rec.attribute8
3544 ,x_clev_rec.attribute9
3545 ,x_clev_rec.attribute10
3546 ,x_clev_rec.attribute11
3547 ,x_clev_rec.attribute12
3548 ,x_clev_rec.attribute13
3549 ,x_clev_rec.attribute14
3550 ,x_clev_rec.attribute15
3551 ,x_clev_rec.created_by
3552 ,x_clev_rec.creation_date
3553 ,x_clev_rec.last_updated_by
3554 ,x_clev_rec.last_update_date
3555 ,x_clev_rec.price_type
3556 ,x_clev_rec.currency_code
3557 ,x_clev_rec.currency_code_renewed
3558 ,x_clev_rec.last_update_login
3559 ,x_clev_rec.request_id
3560 ,x_clev_rec.program_application_id
3561 ,x_clev_rec.program_id
3562 ,x_clev_rec.program_update_date
3563 ,x_clev_rec.price_list_id
3564 ,x_clev_rec.pricing_date
3565 ,x_clev_rec.price_list_line_id
3566 ,x_clev_rec.line_list_price
3567 ,x_clev_rec.item_to_price_yn
3568 ,x_clev_rec.price_basis_yn
3569 ,x_clev_rec.config_header_id
3570 ,x_clev_rec.config_revision_number
3571 ,x_clev_rec.config_complete_yn
3572 ,x_clev_rec.config_valid_yn
3573 ,x_clev_rec.config_top_model_line_id
3574 ,x_clev_rec.config_item_type
3575 ,x_clev_rec.CONFIG_ITEM_ID
3576 ,x_clev_rec.service_item_yn
3577 ,x_clev_rec.ph_pricing_type
3578 ,x_clev_rec.ph_price_break_basis
3579 ,x_clev_rec.ph_min_qty
3580 ,x_clev_rec.ph_min_amt
3581 ,x_clev_rec.ph_qp_reference_id
3582 ,x_clev_rec.ph_value
3583 ,x_clev_rec.ph_enforce_price_list_yn
3584 ,x_clev_rec.ph_adjustment
3585 ,x_clev_rec.ph_integrated_with_qp
3586 ,x_clev_rec.cust_acct_id
3587 ,x_clev_rec.bill_to_site_use_id
3588 ,x_clev_rec.inv_rule_id
3589 ,x_clev_rec.line_renewal_type_code
3590 ,x_clev_rec.ship_to_site_use_id
3591 ,x_clev_rec.payment_term_id
3592 ,x_clev_rec.date_cancelled
3593 ,x_clev_rec.term_cancel_source
3594 ,x_clev_rec.cancelled_amount
3595 ,x_clev_rec.annualized_factor
3596 ,x_clev_rec.payment_instruction_type;
3597
3598 l_no_data_found := c_clev_rec%NOTFOUND;
3599 CLOSE c_clev_rec;
3600 IF l_no_data_found THEN
3601 l_return_status := OKC_API.G_RET_STS_ERROR;
3602 RETURN(l_return_status);
3603 ELSE
3604 RETURN(l_return_status);
3605 END IF;
3606 EXCEPTION
3607 WHEN OTHERS THEN
3608 -- store SQL error message on message stack for caller
3609 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
3610 -- notify caller of an UNEXPECTED error
3611 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3612 RETURN(l_return_status);
3613 END get_clev_rec;
3614
3615
3616 ----------------------------------------------------------------------------
3617 --Function to populate the sections record to be copied.
3618 ----------------------------------------------------------------------------
3619
3620 FUNCTION get_scnv_rec(p_scn_id IN NUMBER,
3621 x_scnv_rec OUT NOCOPY scnv_rec_type) RETURN VARCHAR2 IS
3622
3623 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3624 l_no_data_found BOOLEAN := TRUE;
3625
3626 CURSOR c_scnv_rec IS
3627 SELECT ID,
3628 SCN_TYPE,
3629 CHR_ID,
3630 SAT_CODE,
3631 SECTION_SEQUENCE,
3632 LABEL,
3633 HEADING,
3634 SCN_ID,
3635 SFWT_FLAG,
3636 ATTRIBUTE_CATEGORY,
3637 ATTRIBUTE1,
3638 ATTRIBUTE2,
3639 ATTRIBUTE3,
3640 ATTRIBUTE4,
3641 ATTRIBUTE5,
3642 ATTRIBUTE6,
3643 ATTRIBUTE7,
3644 ATTRIBUTE8,
3645 ATTRIBUTE9,
3646 ATTRIBUTE10,
3647 ATTRIBUTE11,
3648 ATTRIBUTE12,
3649 ATTRIBUTE13,
3650 ATTRIBUTE14,
3651 ATTRIBUTE15
3652 FROM OKC_SECTIONS_V
3653 WHERE ID = p_scn_id;
3654 BEGIN
3655 OPEN c_scnv_rec;
3656 FETCH c_scnv_rec
3657 INTO x_scnv_rec.ID,
3658 x_scnv_rec.SCN_TYPE,
3659 x_scnv_rec.CHR_ID,
3660 x_scnv_rec.SAT_CODE,
3661 x_scnv_rec.SECTION_SEQUENCE,
3662 x_scnv_rec.LABEL,
3663 x_scnv_rec.HEADING,
3664 x_scnv_rec.SCN_ID,
3665 x_scnv_rec.SFWT_FLAG,
3666 x_scnv_rec.ATTRIBUTE_CATEGORY,
3667 x_scnv_rec.ATTRIBUTE1,
3668 x_scnv_rec.ATTRIBUTE2,
3669 x_scnv_rec.ATTRIBUTE3,
3670 x_scnv_rec.ATTRIBUTE4,
3671 x_scnv_rec.ATTRIBUTE5,
3672 x_scnv_rec.ATTRIBUTE6,
3673 x_scnv_rec.ATTRIBUTE7,
3674 x_scnv_rec.ATTRIBUTE8,
3675 x_scnv_rec.ATTRIBUTE9,
3676 x_scnv_rec.ATTRIBUTE10,
3677 x_scnv_rec.ATTRIBUTE11,
3678 x_scnv_rec.ATTRIBUTE12,
3679 x_scnv_rec.ATTRIBUTE13,
3680 x_scnv_rec.ATTRIBUTE14,
3681 x_scnv_rec.ATTRIBUTE15;
3682
3683 l_no_data_found := c_scnv_rec%NOTFOUND;
3684 CLOSE c_scnv_rec;
3685 IF l_no_data_found THEN
3686 l_return_status := OKC_API.G_RET_STS_ERROR;
3687 RETURN(l_return_status);
3688 ELSE
3689 RETURN(l_return_status);
3690 END IF;
3691 EXCEPTION
3692 WHEN OTHERS THEN
3693 -- store SQL error message on message stack for caller
3694 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
3695 -- notify caller of an UNEXPECTED error
3696 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3697 RETURN(l_return_status);
3698
3699 END get_scnv_rec;
3700
3701 ----------------------------------------------------------------------------
3702 --Function to populate the section contents record to be copied.
3703 ----------------------------------------------------------------------------
3704
3705 FUNCTION get_sccv_rec(p_scc_id IN NUMBER,
3706 x_sccv_rec OUT NOCOPY sccv_rec_type) RETURN VARCHAR2 IS
3707
3708 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3709 l_no_data_found BOOLEAN := TRUE;
3710
3711 CURSOR c_sccv_rec IS
3712 SELECT ID,
3713 SCN_ID,
3714 LABEL,
3715 CAT_ID,
3716 CLE_ID,
3717 SAE_ID,
3718 CONTENT_SEQUENCE,
3719 ATTRIBUTE_CATEGORY,
3720 ATTRIBUTE1,
3721 ATTRIBUTE2,
3722 ATTRIBUTE3,
3723 ATTRIBUTE4,
3724 ATTRIBUTE5,
3725 ATTRIBUTE6,
3726 ATTRIBUTE7,
3727 ATTRIBUTE8,
3728 ATTRIBUTE9,
3729 ATTRIBUTE10,
3730 ATTRIBUTE11,
3731 ATTRIBUTE12,
3732 ATTRIBUTE13,
3733 ATTRIBUTE14,
3734 ATTRIBUTE15
3735 FROM OKC_SECTION_CONTENTS_V
3736 WHERE ID = p_scc_id;
3737 BEGIN
3738 OPEN c_sccv_rec;
3739 FETCH c_sccv_rec
3740 INTO x_sccv_rec.ID,
3741 x_sccv_rec.SCN_ID,
3742 x_sccv_rec.LABEL,
3743 x_sccv_rec.CAT_ID,
3744 x_sccv_rec.CLE_ID,
3745 x_sccv_rec.SAE_ID,
3746 x_sccv_rec.CONTENT_SEQUENCE,
3747 x_sccv_rec.ATTRIBUTE_CATEGORY,
3748 x_sccv_rec.ATTRIBUTE1,
3749 x_sccv_rec.ATTRIBUTE2,
3750 x_sccv_rec.ATTRIBUTE3,
3751 x_sccv_rec.ATTRIBUTE4,
3752 x_sccv_rec.ATTRIBUTE5,
3753 x_sccv_rec.ATTRIBUTE6,
3754 x_sccv_rec.ATTRIBUTE7,
3755 x_sccv_rec.ATTRIBUTE8,
3756 x_sccv_rec.ATTRIBUTE9,
3757 x_sccv_rec.ATTRIBUTE10,
3758 x_sccv_rec.ATTRIBUTE11,
3759 x_sccv_rec.ATTRIBUTE12,
3760 x_sccv_rec.ATTRIBUTE13,
3761 x_sccv_rec.ATTRIBUTE14,
3762 x_sccv_rec.ATTRIBUTE15;
3763
3764 l_no_data_found := c_sccv_rec%NOTFOUND;
3765 CLOSE c_sccv_rec;
3766 IF l_no_data_found THEN
3767 l_return_status := OKC_API.G_RET_STS_ERROR;
3768 RETURN(l_return_status);
3769 ELSE
3770 RETURN(l_return_status);
3771 END IF;
3772 EXCEPTION
3773 WHEN OTHERS THEN
3774 -- store SQL error message on message stack for caller
3775 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
3776 -- notify caller of an UNEXPECTED error
3777 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3778 RETURN(l_return_status);
3779
3780 END get_sccv_rec;
3781
3782 ----------------------------------------------------------------------------
3783 --Function to populate the price_attributes record to be copied.
3784 ----------------------------------------------------------------------------
3785
3786 FUNCTION get_pavv_rec(p_pav_id IN NUMBER,
3787 x_pavv_rec OUT NOCOPY pavv_rec_type) RETURN VARCHAR2 IS
3788
3789 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3790 l_no_data_found BOOLEAN := TRUE;
3791
3792 CURSOR c_pavv_rec IS
3793 SELECT ID,
3794 CHR_ID,
3795 CLE_ID,
3796 FLEX_TITLE,
3797 PRICING_CONTEXT,
3798 PRICING_ATTRIBUTE1,
3799 PRICING_ATTRIBUTE2,
3800 PRICING_ATTRIBUTE3,
3801 PRICING_ATTRIBUTE4,
3802 PRICING_ATTRIBUTE5,
3803 PRICING_ATTRIBUTE6,
3804 PRICING_ATTRIBUTE7,
3805 PRICING_ATTRIBUTE8,
3806 PRICING_ATTRIBUTE9,
3807 PRICING_ATTRIBUTE10,
3808 PRICING_ATTRIBUTE11,
3809 PRICING_ATTRIBUTE12,
3810 PRICING_ATTRIBUTE13,
3811 PRICING_ATTRIBUTE14,
3812 PRICING_ATTRIBUTE15,
3813 PRICING_ATTRIBUTE16,
3814 PRICING_ATTRIBUTE17,
3815 PRICING_ATTRIBUTE18,
3816 PRICING_ATTRIBUTE19,
3817 PRICING_ATTRIBUTE20,
3818 PRICING_ATTRIBUTE21,
3819 PRICING_ATTRIBUTE22,
3820 PRICING_ATTRIBUTE23,
3821 PRICING_ATTRIBUTE24,
3822 PRICING_ATTRIBUTE25,
3823 PRICING_ATTRIBUTE26,
3824 PRICING_ATTRIBUTE27,
3825 PRICING_ATTRIBUTE28,
3826 PRICING_ATTRIBUTE29,
3827 PRICING_ATTRIBUTE30,
3828 PRICING_ATTRIBUTE31,
3829 PRICING_ATTRIBUTE32,
3830 PRICING_ATTRIBUTE33,
3831 PRICING_ATTRIBUTE34,
3832 PRICING_ATTRIBUTE35,
3833 PRICING_ATTRIBUTE36,
3834 PRICING_ATTRIBUTE37,
3835 PRICING_ATTRIBUTE38,
3836 PRICING_ATTRIBUTE39,
3837 PRICING_ATTRIBUTE40,
3838 PRICING_ATTRIBUTE41,
3839 PRICING_ATTRIBUTE42,
3840 PRICING_ATTRIBUTE43,
3841 PRICING_ATTRIBUTE44,
3842 PRICING_ATTRIBUTE45,
3843 PRICING_ATTRIBUTE46,
3844 PRICING_ATTRIBUTE47,
3845 PRICING_ATTRIBUTE48,
3846 PRICING_ATTRIBUTE49,
3847 PRICING_ATTRIBUTE50,
3848 PRICING_ATTRIBUTE51,
3849 PRICING_ATTRIBUTE52,
3850 PRICING_ATTRIBUTE53,
3851 PRICING_ATTRIBUTE54,
3852 PRICING_ATTRIBUTE55,
3853 PRICING_ATTRIBUTE56,
3854 PRICING_ATTRIBUTE57,
3855 PRICING_ATTRIBUTE58,
3856 PRICING_ATTRIBUTE59,
3857 PRICING_ATTRIBUTE60,
3858 PRICING_ATTRIBUTE61,
3859 PRICING_ATTRIBUTE62,
3860 PRICING_ATTRIBUTE63,
3861 PRICING_ATTRIBUTE64,
3862 PRICING_ATTRIBUTE65,
3863 PRICING_ATTRIBUTE66,
3864 PRICING_ATTRIBUTE67,
3865 PRICING_ATTRIBUTE68,
3866 PRICING_ATTRIBUTE69,
3867 PRICING_ATTRIBUTE70,
3868 PRICING_ATTRIBUTE71,
3869 PRICING_ATTRIBUTE72,
3870 PRICING_ATTRIBUTE73,
3871 PRICING_ATTRIBUTE74,
3872 PRICING_ATTRIBUTE75,
3873 PRICING_ATTRIBUTE76,
3874 PRICING_ATTRIBUTE77,
3875 PRICING_ATTRIBUTE78,
3876 PRICING_ATTRIBUTE79,
3877 PRICING_ATTRIBUTE80,
3878 PRICING_ATTRIBUTE81,
3879 PRICING_ATTRIBUTE82,
3880 PRICING_ATTRIBUTE83,
3881 PRICING_ATTRIBUTE84,
3882 PRICING_ATTRIBUTE85,
3883 PRICING_ATTRIBUTE86,
3884 PRICING_ATTRIBUTE87,
3885 PRICING_ATTRIBUTE88,
3886 PRICING_ATTRIBUTE89,
3887 PRICING_ATTRIBUTE90,
3888 PRICING_ATTRIBUTE91,
3889 PRICING_ATTRIBUTE92,
3890 PRICING_ATTRIBUTE93,
3891 PRICING_ATTRIBUTE94,
3892 PRICING_ATTRIBUTE95,
3893 PRICING_ATTRIBUTE96,
3894 PRICING_ATTRIBUTE97,
3895 PRICING_ATTRIBUTE98,
3896 PRICING_ATTRIBUTE99,
3897 PRICING_ATTRIBUTE100,
3898 QUALIFIER_CONTEXT,
3899 QUALIFIER_ATTRIBUTE1,
3900 QUALIFIER_ATTRIBUTE2,
3901 QUALIFIER_ATTRIBUTE3,
3902 QUALIFIER_ATTRIBUTE4,
3903 QUALIFIER_ATTRIBUTE5,
3904 QUALIFIER_ATTRIBUTE6,
3905 QUALIFIER_ATTRIBUTE7,
3906 QUALIFIER_ATTRIBUTE8,
3907 QUALIFIER_ATTRIBUTE9,
3908 QUALIFIER_ATTRIBUTE10,
3909 QUALIFIER_ATTRIBUTE11,
3910 QUALIFIER_ATTRIBUTE12,
3911 QUALIFIER_ATTRIBUTE13,
3912 QUALIFIER_ATTRIBUTE14,
3913 QUALIFIER_ATTRIBUTE15,
3914 QUALIFIER_ATTRIBUTE16,
3915 QUALIFIER_ATTRIBUTE17,
3916 QUALIFIER_ATTRIBUTE18,
3917 QUALIFIER_ATTRIBUTE19,
3918 QUALIFIER_ATTRIBUTE20,
3919 QUALIFIER_ATTRIBUTE21,
3920 QUALIFIER_ATTRIBUTE22,
3921 QUALIFIER_ATTRIBUTE23,
3922 QUALIFIER_ATTRIBUTE24,
3923 QUALIFIER_ATTRIBUTE25,
3924 QUALIFIER_ATTRIBUTE26,
3925 QUALIFIER_ATTRIBUTE27,
3926 QUALIFIER_ATTRIBUTE28,
3927 QUALIFIER_ATTRIBUTE29,
3928 QUALIFIER_ATTRIBUTE30,
3929 QUALIFIER_ATTRIBUTE31,
3930 QUALIFIER_ATTRIBUTE32,
3931 QUALIFIER_ATTRIBUTE33,
3932 QUALIFIER_ATTRIBUTE34,
3933 QUALIFIER_ATTRIBUTE35,
3934 QUALIFIER_ATTRIBUTE36,
3935 QUALIFIER_ATTRIBUTE37,
3936 QUALIFIER_ATTRIBUTE38,
3937 QUALIFIER_ATTRIBUTE39,
3938 QUALIFIER_ATTRIBUTE40,
3939 QUALIFIER_ATTRIBUTE41,
3940 QUALIFIER_ATTRIBUTE42,
3941 QUALIFIER_ATTRIBUTE43,
3942 QUALIFIER_ATTRIBUTE44,
3943 QUALIFIER_ATTRIBUTE45,
3944 QUALIFIER_ATTRIBUTE46,
3945 QUALIFIER_ATTRIBUTE47,
3946 QUALIFIER_ATTRIBUTE48,
3947 QUALIFIER_ATTRIBUTE49,
3948 QUALIFIER_ATTRIBUTE50,
3949 QUALIFIER_ATTRIBUTE51,
3950 QUALIFIER_ATTRIBUTE52,
3951 QUALIFIER_ATTRIBUTE53,
3952 QUALIFIER_ATTRIBUTE54,
3953 QUALIFIER_ATTRIBUTE55,
3954 QUALIFIER_ATTRIBUTE56,
3955 QUALIFIER_ATTRIBUTE57,
3956 QUALIFIER_ATTRIBUTE58,
3957 QUALIFIER_ATTRIBUTE59,
3958 QUALIFIER_ATTRIBUTE60,
3959 QUALIFIER_ATTRIBUTE61,
3960 QUALIFIER_ATTRIBUTE62,
3961 QUALIFIER_ATTRIBUTE63,
3962 QUALIFIER_ATTRIBUTE64,
3963 QUALIFIER_ATTRIBUTE65,
3964 QUALIFIER_ATTRIBUTE66,
3965 QUALIFIER_ATTRIBUTE67,
3966 QUALIFIER_ATTRIBUTE68,
3967 QUALIFIER_ATTRIBUTE69,
3968 QUALIFIER_ATTRIBUTE70,
3969 QUALIFIER_ATTRIBUTE71,
3970 QUALIFIER_ATTRIBUTE72,
3971 QUALIFIER_ATTRIBUTE73,
3972 QUALIFIER_ATTRIBUTE74,
3973 QUALIFIER_ATTRIBUTE75,
3974 QUALIFIER_ATTRIBUTE76,
3975 QUALIFIER_ATTRIBUTE77,
3976 QUALIFIER_ATTRIBUTE78,
3977 QUALIFIER_ATTRIBUTE79,
3978 QUALIFIER_ATTRIBUTE80,
3979 QUALIFIER_ATTRIBUTE81,
3980 QUALIFIER_ATTRIBUTE82,
3981 QUALIFIER_ATTRIBUTE83,
3982 QUALIFIER_ATTRIBUTE84,
3983 QUALIFIER_ATTRIBUTE85,
3984 QUALIFIER_ATTRIBUTE86,
3985 QUALIFIER_ATTRIBUTE87,
3986 QUALIFIER_ATTRIBUTE88,
3987 QUALIFIER_ATTRIBUTE89,
3988 QUALIFIER_ATTRIBUTE90,
3989 QUALIFIER_ATTRIBUTE91,
3990 QUALIFIER_ATTRIBUTE92,
3991 QUALIFIER_ATTRIBUTE93,
3992 QUALIFIER_ATTRIBUTE94,
3993 QUALIFIER_ATTRIBUTE95,
3994 QUALIFIER_ATTRIBUTE96,
3995 QUALIFIER_ATTRIBUTE97,
3996 QUALIFIER_ATTRIBUTE98,
3997 QUALIFIER_ATTRIBUTE99,
3998 QUALIFIER_ATTRIBUTE100
3999 FROM OKC_PRICE_ATT_VALUES_V
4000 WHERE ID = p_pav_id;
4001 BEGIN
4002 OPEN c_pavv_rec;
4003 FETCH c_pavv_rec
4004 INTO x_pavv_rec.ID,
4005 x_pavv_rec.CHR_ID,
4006 x_pavv_rec.CLE_ID,
4007 x_pavv_rec.FLEX_TITLE,
4008 x_pavv_rec.PRICING_CONTEXT,
4009 x_pavv_rec.PRICING_ATTRIBUTE1,
4010 x_pavv_rec.PRICING_ATTRIBUTE2,
4011 x_pavv_rec.PRICING_ATTRIBUTE3,
4012 x_pavv_rec.PRICING_ATTRIBUTE4,
4013 x_pavv_rec.PRICING_ATTRIBUTE5,
4014 x_pavv_rec.PRICING_ATTRIBUTE6,
4015 x_pavv_rec.PRICING_ATTRIBUTE7,
4016 x_pavv_rec.PRICING_ATTRIBUTE8,
4017 x_pavv_rec.PRICING_ATTRIBUTE9,
4018 x_pavv_rec.PRICING_ATTRIBUTE10,
4019 x_pavv_rec.PRICING_ATTRIBUTE11,
4020 x_pavv_rec.PRICING_ATTRIBUTE12,
4021 x_pavv_rec.PRICING_ATTRIBUTE13,
4022 x_pavv_rec.PRICING_ATTRIBUTE14,
4023 x_pavv_rec.PRICING_ATTRIBUTE15,
4024 x_pavv_rec.PRICING_ATTRIBUTE16,
4025 x_pavv_rec.PRICING_ATTRIBUTE17,
4026 x_pavv_rec.PRICING_ATTRIBUTE18,
4027 x_pavv_rec.PRICING_ATTRIBUTE19,
4028 x_pavv_rec.PRICING_ATTRIBUTE20,
4029 x_pavv_rec.PRICING_ATTRIBUTE21,
4030 x_pavv_rec.PRICING_ATTRIBUTE22,
4031 x_pavv_rec.PRICING_ATTRIBUTE23,
4032 x_pavv_rec.PRICING_ATTRIBUTE24,
4033 x_pavv_rec.PRICING_ATTRIBUTE25,
4034 x_pavv_rec.PRICING_ATTRIBUTE26,
4035 x_pavv_rec.PRICING_ATTRIBUTE27,
4036 x_pavv_rec.PRICING_ATTRIBUTE28,
4037 x_pavv_rec.PRICING_ATTRIBUTE29,
4038 x_pavv_rec.PRICING_ATTRIBUTE30,
4039 x_pavv_rec.PRICING_ATTRIBUTE31,
4040 x_pavv_rec.PRICING_ATTRIBUTE32,
4041 x_pavv_rec.PRICING_ATTRIBUTE33,
4042 x_pavv_rec.PRICING_ATTRIBUTE34,
4043 x_pavv_rec.PRICING_ATTRIBUTE35,
4044 x_pavv_rec.PRICING_ATTRIBUTE36,
4045 x_pavv_rec.PRICING_ATTRIBUTE37,
4046 x_pavv_rec.PRICING_ATTRIBUTE38,
4047 x_pavv_rec.PRICING_ATTRIBUTE39,
4048 x_pavv_rec.PRICING_ATTRIBUTE40,
4049 x_pavv_rec.PRICING_ATTRIBUTE41,
4050 x_pavv_rec.PRICING_ATTRIBUTE42,
4051 x_pavv_rec.PRICING_ATTRIBUTE43,
4052 x_pavv_rec.PRICING_ATTRIBUTE44,
4053 x_pavv_rec.PRICING_ATTRIBUTE45,
4054 x_pavv_rec.PRICING_ATTRIBUTE46,
4055 x_pavv_rec.PRICING_ATTRIBUTE47,
4056 x_pavv_rec.PRICING_ATTRIBUTE48,
4057 x_pavv_rec.PRICING_ATTRIBUTE49,
4058 x_pavv_rec.PRICING_ATTRIBUTE50,
4059 x_pavv_rec.PRICING_ATTRIBUTE51,
4060 x_pavv_rec.PRICING_ATTRIBUTE52,
4061 x_pavv_rec.PRICING_ATTRIBUTE53,
4062 x_pavv_rec.PRICING_ATTRIBUTE54,
4063 x_pavv_rec.PRICING_ATTRIBUTE55,
4064 x_pavv_rec.PRICING_ATTRIBUTE56,
4065 x_pavv_rec.PRICING_ATTRIBUTE57,
4066 x_pavv_rec.PRICING_ATTRIBUTE58,
4067 x_pavv_rec.PRICING_ATTRIBUTE59,
4068 x_pavv_rec.PRICING_ATTRIBUTE60,
4069 x_pavv_rec.PRICING_ATTRIBUTE61,
4070 x_pavv_rec.PRICING_ATTRIBUTE62,
4071 x_pavv_rec.PRICING_ATTRIBUTE63,
4072 x_pavv_rec.PRICING_ATTRIBUTE64,
4073 x_pavv_rec.PRICING_ATTRIBUTE65,
4074 x_pavv_rec.PRICING_ATTRIBUTE66,
4075 x_pavv_rec.PRICING_ATTRIBUTE67,
4076 x_pavv_rec.PRICING_ATTRIBUTE68,
4077 x_pavv_rec.PRICING_ATTRIBUTE69,
4078 x_pavv_rec.PRICING_ATTRIBUTE70,
4079 x_pavv_rec.PRICING_ATTRIBUTE71,
4080 x_pavv_rec.PRICING_ATTRIBUTE72,
4081 x_pavv_rec.PRICING_ATTRIBUTE73,
4082 x_pavv_rec.PRICING_ATTRIBUTE74,
4083 x_pavv_rec.PRICING_ATTRIBUTE75,
4084 x_pavv_rec.PRICING_ATTRIBUTE76,
4085 x_pavv_rec.PRICING_ATTRIBUTE77,
4086 x_pavv_rec.PRICING_ATTRIBUTE78,
4087 x_pavv_rec.PRICING_ATTRIBUTE79,
4088 x_pavv_rec.PRICING_ATTRIBUTE80,
4089 x_pavv_rec.PRICING_ATTRIBUTE81,
4090 x_pavv_rec.PRICING_ATTRIBUTE82,
4091 x_pavv_rec.PRICING_ATTRIBUTE83,
4092 x_pavv_rec.PRICING_ATTRIBUTE84,
4093 x_pavv_rec.PRICING_ATTRIBUTE85,
4094 x_pavv_rec.PRICING_ATTRIBUTE86,
4095 x_pavv_rec.PRICING_ATTRIBUTE87,
4096 x_pavv_rec.PRICING_ATTRIBUTE88,
4097 x_pavv_rec.PRICING_ATTRIBUTE89,
4098 x_pavv_rec.PRICING_ATTRIBUTE90,
4099 x_pavv_rec.PRICING_ATTRIBUTE91,
4100 x_pavv_rec.PRICING_ATTRIBUTE92,
4101 x_pavv_rec.PRICING_ATTRIBUTE93,
4102 x_pavv_rec.PRICING_ATTRIBUTE94,
4103 x_pavv_rec.PRICING_ATTRIBUTE95,
4104 x_pavv_rec.PRICING_ATTRIBUTE96,
4105 x_pavv_rec.PRICING_ATTRIBUTE97,
4106 x_pavv_rec.PRICING_ATTRIBUTE98,
4107 x_pavv_rec.PRICING_ATTRIBUTE99,
4108 x_pavv_rec.PRICING_ATTRIBUTE100,
4109 x_pavv_rec.QUALIFIER_CONTEXT,
4110 x_pavv_rec.QUALIFIER_ATTRIBUTE1,
4111 x_pavv_rec.QUALIFIER_ATTRIBUTE2,
4112 x_pavv_rec.QUALIFIER_ATTRIBUTE3,
4113 x_pavv_rec.QUALIFIER_ATTRIBUTE4,
4114 x_pavv_rec.QUALIFIER_ATTRIBUTE5,
4115 x_pavv_rec.QUALIFIER_ATTRIBUTE6,
4116 x_pavv_rec.QUALIFIER_ATTRIBUTE7,
4117 x_pavv_rec.QUALIFIER_ATTRIBUTE8,
4118 x_pavv_rec.QUALIFIER_ATTRIBUTE9,
4119 x_pavv_rec.QUALIFIER_ATTRIBUTE10,
4120 x_pavv_rec.QUALIFIER_ATTRIBUTE11,
4121 x_pavv_rec.QUALIFIER_ATTRIBUTE12,
4122 x_pavv_rec.QUALIFIER_ATTRIBUTE13,
4123 x_pavv_rec.QUALIFIER_ATTRIBUTE14,
4124 x_pavv_rec.QUALIFIER_ATTRIBUTE15,
4125 x_pavv_rec.QUALIFIER_ATTRIBUTE16,
4126 x_pavv_rec.QUALIFIER_ATTRIBUTE17,
4127 x_pavv_rec.QUALIFIER_ATTRIBUTE18,
4128 x_pavv_rec.QUALIFIER_ATTRIBUTE19,
4129 x_pavv_rec.QUALIFIER_ATTRIBUTE20,
4130 x_pavv_rec.QUALIFIER_ATTRIBUTE21,
4131 x_pavv_rec.QUALIFIER_ATTRIBUTE22,
4132 x_pavv_rec.QUALIFIER_ATTRIBUTE23,
4133 x_pavv_rec.QUALIFIER_ATTRIBUTE24,
4134 x_pavv_rec.QUALIFIER_ATTRIBUTE25,
4135 x_pavv_rec.QUALIFIER_ATTRIBUTE26,
4136 x_pavv_rec.QUALIFIER_ATTRIBUTE27,
4137 x_pavv_rec.QUALIFIER_ATTRIBUTE28,
4138 x_pavv_rec.QUALIFIER_ATTRIBUTE29,
4139 x_pavv_rec.QUALIFIER_ATTRIBUTE30,
4140 x_pavv_rec.QUALIFIER_ATTRIBUTE31,
4141 x_pavv_rec.QUALIFIER_ATTRIBUTE32,
4142 x_pavv_rec.QUALIFIER_ATTRIBUTE33,
4143 x_pavv_rec.QUALIFIER_ATTRIBUTE34,
4144 x_pavv_rec.QUALIFIER_ATTRIBUTE35,
4145 x_pavv_rec.QUALIFIER_ATTRIBUTE36,
4146 x_pavv_rec.QUALIFIER_ATTRIBUTE37,
4147 x_pavv_rec.QUALIFIER_ATTRIBUTE38,
4148 x_pavv_rec.QUALIFIER_ATTRIBUTE39,
4149 x_pavv_rec.QUALIFIER_ATTRIBUTE40,
4150 x_pavv_rec.QUALIFIER_ATTRIBUTE41,
4151 x_pavv_rec.QUALIFIER_ATTRIBUTE42,
4152 x_pavv_rec.QUALIFIER_ATTRIBUTE43,
4153 x_pavv_rec.QUALIFIER_ATTRIBUTE44,
4154 x_pavv_rec.QUALIFIER_ATTRIBUTE45,
4155 x_pavv_rec.QUALIFIER_ATTRIBUTE46,
4156 x_pavv_rec.QUALIFIER_ATTRIBUTE47,
4157 x_pavv_rec.QUALIFIER_ATTRIBUTE48,
4158 x_pavv_rec.QUALIFIER_ATTRIBUTE49,
4159 x_pavv_rec.QUALIFIER_ATTRIBUTE50,
4160 x_pavv_rec.QUALIFIER_ATTRIBUTE51,
4161 x_pavv_rec.QUALIFIER_ATTRIBUTE52,
4162 x_pavv_rec.QUALIFIER_ATTRIBUTE53,
4163 x_pavv_rec.QUALIFIER_ATTRIBUTE54,
4164 x_pavv_rec.QUALIFIER_ATTRIBUTE55,
4165 x_pavv_rec.QUALIFIER_ATTRIBUTE56,
4166 x_pavv_rec.QUALIFIER_ATTRIBUTE57,
4167 x_pavv_rec.QUALIFIER_ATTRIBUTE58,
4168 x_pavv_rec.QUALIFIER_ATTRIBUTE59,
4169 x_pavv_rec.QUALIFIER_ATTRIBUTE60,
4170 x_pavv_rec.QUALIFIER_ATTRIBUTE61,
4171 x_pavv_rec.QUALIFIER_ATTRIBUTE62,
4172 x_pavv_rec.QUALIFIER_ATTRIBUTE63,
4173 x_pavv_rec.QUALIFIER_ATTRIBUTE64,
4174 x_pavv_rec.QUALIFIER_ATTRIBUTE65,
4175 x_pavv_rec.QUALIFIER_ATTRIBUTE66,
4176 x_pavv_rec.QUALIFIER_ATTRIBUTE67,
4177 x_pavv_rec.QUALIFIER_ATTRIBUTE68,
4178 x_pavv_rec.QUALIFIER_ATTRIBUTE69,
4179 x_pavv_rec.QUALIFIER_ATTRIBUTE70,
4180 x_pavv_rec.QUALIFIER_ATTRIBUTE71,
4181 x_pavv_rec.QUALIFIER_ATTRIBUTE72,
4182 x_pavv_rec.QUALIFIER_ATTRIBUTE73,
4183 x_pavv_rec.QUALIFIER_ATTRIBUTE74,
4184 x_pavv_rec.QUALIFIER_ATTRIBUTE75,
4185 x_pavv_rec.QUALIFIER_ATTRIBUTE76,
4186 x_pavv_rec.QUALIFIER_ATTRIBUTE77,
4187 x_pavv_rec.QUALIFIER_ATTRIBUTE78,
4188 x_pavv_rec.QUALIFIER_ATTRIBUTE79,
4189 x_pavv_rec.QUALIFIER_ATTRIBUTE80,
4190 x_pavv_rec.QUALIFIER_ATTRIBUTE81,
4191 x_pavv_rec.QUALIFIER_ATTRIBUTE82,
4192 x_pavv_rec.QUALIFIER_ATTRIBUTE83,
4193 x_pavv_rec.QUALIFIER_ATTRIBUTE84,
4194 x_pavv_rec.QUALIFIER_ATTRIBUTE85,
4195 x_pavv_rec.QUALIFIER_ATTRIBUTE86,
4196 x_pavv_rec.QUALIFIER_ATTRIBUTE87,
4197 x_pavv_rec.QUALIFIER_ATTRIBUTE88,
4198 x_pavv_rec.QUALIFIER_ATTRIBUTE89,
4199 x_pavv_rec.QUALIFIER_ATTRIBUTE90,
4200 x_pavv_rec.QUALIFIER_ATTRIBUTE91,
4201 x_pavv_rec.QUALIFIER_ATTRIBUTE92,
4202 x_pavv_rec.QUALIFIER_ATTRIBUTE93,
4203 x_pavv_rec.QUALIFIER_ATTRIBUTE94,
4204 x_pavv_rec.QUALIFIER_ATTRIBUTE95,
4205 x_pavv_rec.QUALIFIER_ATTRIBUTE96,
4206 x_pavv_rec.QUALIFIER_ATTRIBUTE97,
4207 x_pavv_rec.QUALIFIER_ATTRIBUTE98,
4208 x_pavv_rec.QUALIFIER_ATTRIBUTE99,
4209 x_pavv_rec.QUALIFIER_ATTRIBUTE100;
4210
4211 l_no_data_found := c_pavv_rec%NOTFOUND;
4212 CLOSE c_pavv_rec;
4213 IF l_no_data_found THEN
4214 l_return_status := OKC_API.G_RET_STS_ERROR;
4215 RETURN(l_return_status);
4216 ELSE
4217 RETURN(l_return_status);
4218 END IF;
4219 EXCEPTION
4220 WHEN OTHERS THEN
4221 -- store SQL error message on message stack for caller
4222 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
4223 -- notify caller of an UNEXPECTED error
4224 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4225 RETURN(l_return_status);
4226
4227 END get_pavv_rec;
4228
4229 PROCEDURE create_trxn_extn(
4230 p_api_version IN NUMBER,
4231 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4232 x_return_status OUT NOCOPY VARCHAR2,
4233 x_msg_count OUT NOCOPY NUMBER,
4234 x_msg_data OUT NOCOPY VARCHAR2,
4235 p_old_trx_ext_id IN NUMBER,
4236 p_order_id IN NUMBER,
4237 p_cust_acct_id IN NUMBER,
4238 p_bill_to_site_use_id IN NUMBER,
4239 x_trx_ext_id OUT NOCOPY NUMBER)
4240 IS
4241 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TRXN_EXTN';
4242 l_api_version CONSTANT NUMBER := 1;
4243 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
4244 l_error_text VARCHAR2(512);
4245
4246 CURSOR c_party_from_billto(cp_bill_to_site_use_id IN NUMBER) IS
4247 SELECT cas.cust_account_id cust_account_id, ca.party_id party_id
4248 FROM hz_cust_site_uses_all csu, hz_cust_acct_sites_all cas, hz_cust_accounts_all ca
4249 WHERE csu.site_use_id = cp_bill_to_site_use_id
4250 AND cas.cust_acct_site_id = csu.cust_acct_site_id
4251 AND ca.cust_account_id = cas.cust_account_id;
4252
4253 CURSOR c_party_from_cust(cp_cust_acct_id IN NUMBER) IS
4254 SELECT ca.party_id party_id
4255 FROM hz_cust_accounts_all ca
4256 WHERE ca.cust_account_id = cp_cust_acct_id;
4257
4258 CURSOR c_instr(cp_trx_ext_id IN NUMBER) IS
4259 SELECT instr_assignment_id
4260 FROM iby_trxn_extensions_v
4261 WHERE trxn_extension_id = cp_trx_ext_id;
4262
4263 l_cust_account_id NUMBER;
4264 l_party_id NUMBER;
4265 l_instr_assignment NUMBER;
4266
4267 l_payer IBY_FNDCPT_COMMON_PUB.payercontext_rec_type;
4268 l_trxn_attribs IBY_FNDCPT_TRXN_PUB.trxnextension_rec_type;
4269 l_response IBY_FNDCPT_COMMON_PUB.result_rec_type;
4270
4271 BEGIN
4272
4273 --log key input parameters
4274 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
4275 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_old_trx_ext_id='||p_old_trx_ext_id||' ,p_order_id='||p_order_id||' ,p_cust_acct_id='||p_cust_acct_id||' ,p_bill_to_site_use_id='||p_bill_to_site_use_id);
4276 END IF;
4277
4278 --standard api initilization and checks
4279 SAVEPOINT create_trxn_extn_PVT;
4280 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
4281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4282 END IF;
4283 IF FND_API.to_boolean(p_init_msg_list ) THEN
4284 FND_MSG_PUB.initialize;
4285 END IF;
4286 x_return_status := FND_API.G_RET_STS_SUCCESS;
4287
4288 --first get the payer info
4289 IF (p_cust_acct_id IS NOT NULL) THEN
4290
4291 l_cust_account_id := p_cust_acct_id;
4292 OPEN c_party_from_cust(p_cust_acct_id);
4293 FETCH c_party_from_cust INTO l_party_id;
4294 CLOSE c_party_from_cust;
4295
4296 ELSIF (p_bill_to_site_use_id IS NOT NULL) THEN
4297
4298 OPEN c_party_from_billto(p_bill_to_site_use_id);
4299 FETCH c_party_from_billto INTO l_cust_account_id, l_party_id;
4300 CLOSE c_party_from_billto;
4301
4302 ELSE
4303
4304 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, 'Either p_cust_acct_id or p_bill_to_site_use_id is mandatory');
4305 RAISE FND_API.g_exc_error;
4306
4307 END IF;
4308
4309 --get the credit card (instrument assignment) info
4310 OPEN c_instr(p_old_trx_ext_id);
4311 FETCH c_instr INTO l_instr_assignment;
4312 CLOSE c_instr;
4313
4314 l_payer.payment_function := IBY_FNDCPT_COMMON_PUB.G_PMT_FUNCTION_CUST_PMT; --CUSTOMER_PAYMENT
4315 l_payer.party_id := l_party_id;
4316 l_payer.cust_account_id := l_cust_account_id;
4317
4318 l_trxn_attribs.originating_application_id := 515; --service contracts OKS
4319 l_trxn_attribs.order_id := p_order_id; --contract id or line id
4320 l_trxn_attribs.trxn_ref_number1 := to_char(SYSDATE,'ddmmyyyyhhmmssss'); --to make order id and trx ref 1 unique
4321
4322 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
4323 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.create_trxn_extn', 'calling IBY_FNDCPT_TRXN_PUB.create_transaction_extension, p_payer.party_id='||l_party_id||' ,p_payer.cust_account_id='||l_cust_account_id||
4324 ' ,p_instr_assignment='||l_instr_assignment||' ,p_trxn_attribs.originating_application_id=515'||' ,p_trxn_attribs.order_id='||p_order_id);
4325 END IF;
4326
4327
4328 IBY_FNDCPT_TRXN_PUB.create_transaction_extension(
4329 p_api_version => 1.0,
4330 p_init_msg_list => FND_API.G_FALSE,
4331 p_commit => FND_API.G_FALSE,
4332 x_return_status => x_return_status,
4333 x_msg_count => x_msg_count,
4334 x_msg_data => x_msg_data,
4335 p_payer => l_Payer,
4336 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD, --UPWARD
4337 p_pmt_channel => IBY_FNDCPT_SETUP_PUB.G_CHANNEL_CREDIT_CARD, --CREDIT_CARD
4338 p_instr_assignment => l_instr_assignment,
4339 p_trxn_attribs => l_trxn_attribs,
4340 x_entity_id => x_trx_ext_id,
4341 x_response => l_response);
4342
4343 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
4344 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.create_trxn_extn', 'after call to IBY_FNDCPT_TRXN_PUB.create_transaction_extension, x_return_status='||x_return_status||' ,x_entity_id='||x_trx_ext_id||
4345 ' ,result_code='||l_response.result_code||' ,result_category='||l_response.result_category||' ,result_message='||l_response.result_message);
4346 END IF;
4347
4348 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4349 RAISE FND_API.g_exc_unexpected_error;
4350 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
4351 RAISE FND_API.g_exc_error;
4352 END IF;
4353
4354 --also check the pmt api result code
4355 IF (l_response.result_code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
4356 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_response.result_message||'('||l_response.result_code||':'||l_response.result_category||')');
4357 RAISE FND_API.g_exc_error;
4358 END IF;
4359
4360 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
4361 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end', 'x_return_status='|| x_return_status||' ,x_trx_ext_id='||x_trx_ext_id);
4362 END IF;
4363 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
4364
4365 EXCEPTION
4366 WHEN FND_API.g_exc_error THEN
4367 ROLLBACK TO create_trxn_extn_PVT;
4368 x_return_status := FND_API.g_ret_sts_error ;
4369
4370 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
4371 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'x_return_status=' || x_return_status);
4372 END IF;
4373 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
4374
4375 IF (c_party_from_billto%isopen) THEN
4376 CLOSE c_party_from_billto;
4377 END IF;
4378 IF (c_party_from_cust%isopen) THEN
4379 CLOSE c_party_from_cust;
4380 END IF;
4381 IF (c_instr%isopen) THEN
4382 CLOSE c_instr;
4383 END IF;
4384
4385 WHEN FND_API.g_exc_unexpected_error THEN
4386 ROLLBACK TO create_trxn_extn_PVT;
4387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4388
4389 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
4390 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status=' || x_return_status);
4391 END IF;
4392 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
4393
4394 IF (c_party_from_billto%isopen) THEN
4395 CLOSE c_party_from_billto;
4396 END IF;
4397 IF (c_party_from_cust%isopen) THEN
4398 CLOSE c_party_from_cust;
4399 END IF;
4400 IF (c_instr%isopen) THEN
4401 CLOSE c_instr;
4402 END IF;
4403
4404 WHEN OTHERS THEN
4405 ROLLBACK TO create_trxn_extn_PVT;
4406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4407
4408 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
4409 l_error_text := substr (SQLERRM, 1, 240);
4410 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
4411 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
4412 END IF;
4413 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
4414
4415 IF (c_party_from_billto%isopen) THEN
4416 CLOSE c_party_from_billto;
4417 END IF;
4418 IF (c_party_from_cust%isopen) THEN
4419 CLOSE c_party_from_cust;
4420 END IF;
4421 IF (c_instr%isopen) THEN
4422 CLOSE c_instr;
4423 END IF;
4424
4425 END create_trxn_extn;
4426
4427 END OKS_RENCPY_PVT ;
4428
4429