[Home] [Help]
PACKAGE BODY: APPS.OKS_RENCON_PVT
Source
1 PACKAGE BODY OKS_RENCON_PVT AS
2 /* $Header: OKSRENCB.pls 120.10.12000000.3 2007/06/06 17:18:39 skekkar ship $*/
3
4 l_conc_program VARCHAR2(200);
5 G_GCD_RENEWAL_TYPE VARCHAR2(30) := 'X';
6
7 --Bug#5981381: Cache the class_operation_id instead of deriving everytime;
8 G_RENCON_CLASS_OPERATION_ID NUMBER;
9 CURSOR cur_class_operations is
10 SELECT ID from OKC_CLASS_OPERATIONS
11 WHERE OPN_CODE = 'REN_CON' and CLS_CODE = 'SERVICE';
12
13 ----------------------------------------------------------------------------------------
14 ---This function is used to Check if the source_line_id has been processed with another
15 -- target id
16 ----------------------------------------------------------------------------------------
17 FUNCTION OBJECT_LINE_PROCESSED_BY_OTHER(target_id IN NUMBER,
18 source_line_id IN NUMBER) RETURN BOOLEAN
19 IS
20 CURSOR op_lines IS
21 SELECT subject_chr_id
22 FROM okc_operation_lines
23 WHERE object_cle_id = source_line_id
24 AND process_flag = 'P'
25 AND active_yn = 'Y';
26 x_return BOOLEAN := FALSE;
27 BEGIN
28 FOR cur_op_lines IN op_lines
29 LOOP
30 IF cur_op_lines.subject_chr_id <> target_id THEN
31 x_return := TRUE;
32 EXIT;
33 END IF;
34 END LOOP;
35 RETURN(x_return);
36 END OBJECT_LINE_PROCESSED_BY_OTHER;
37
38 ----------------------------------------------------------------------------------------
39 --This function is used to find if a source subline is already in operation_lines table
40 ----------------------------------------------------------------------------------------
41 FUNCTION ALREADY_IN_OL(p_object_cle_id IN NUMBER,
42 p_subject_chr_id IN NUMBER) RETURN BOOLEAN
43 IS
44 CURSOR oper_exist IS
45 SELECT 'X' x
46 FROM okc_operation_lines
47 WHERE subject_chr_id = p_subject_chr_id
48 AND object_cle_id = p_object_cle_id;
49 x_return BOOLEAN := FALSE;
50 BEGIN
51 FOR cur_oper_exist IN oper_exist LOOP
52 x_return := TRUE;
53 EXIT;
54 END LOOP;
55 RETURN(x_return);
56 END ALREADY_IN_OL;
57
58
59 PROCEDURE SET_OL_SELECTED(p_id IN NUMBER,
60 x_return_status OUT NOCOPY VARCHAR2) IS
61 CURSOR parent_cur IS
62 SELECT id, select_yn
63 FROM okc_operation_lines
64 WHERE id = (SELECT parent_ole_id
65 FROM okc_operation_lines
66 WHERE id = p_id);
67 l_api_version CONSTANT NUMBER := 1.0;
68 l_init_msg_list VARCHAR2(2000) := OKC_API.G_FALSE;
69 l_return_status VARCHAR2(1);
70 l_msg_count NUMBER;
71 l_msg_data VARCHAR2(2000);
72 l_msg_index_out NUMBER;
73 l_olev_tbl_in OKC_OPER_INST_PUB.olev_tbl_type;
74 l_olev_tbl_out OKC_OPER_INST_PUB.olev_tbl_type;
75
76 PROCEDURE SET_OL_SEL(p_ole_id IN NUMBER) IS
77 l_api_name CONSTANT VARCHAR2(30) := 'SET_OL_SEL';
78 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
79 BEGIN
80 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
81 FND_LOG.string(FND_LOG.level_statement, l_mod_name,' SET_OL_SELECTED p_ole_id = '|| p_ole_id);
82 END IF;
83 IF p_ole_id IS NULL THEN
84 RETURN;
85 END IF;
86 l_olev_tbl_in(1).id := p_ole_id;
87 l_olev_tbl_in(1).select_yn := 'Y';
88 OKC_OPER_INST_PUB.Update_Operation_Line(
89 p_api_version => l_api_version,
90 p_init_msg_list => l_init_msg_list,
91 x_return_status => l_return_status,
92 x_msg_count => l_msg_count,
93 x_msg_data => l_msg_data,
94 p_olev_tbl => l_olev_tbl_in,
95 x_olev_tbl => l_olev_tbl_out );
96 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
97 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Update_Operation_Line l_return_status = ' || l_return_status);
98 END IF;
99 x_return_status := l_return_status;
100 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
101 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
102 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Update_Operation_Line l_msg_data = ' || l_msg_data);
103 END IF;
104 RETURN;
105 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
106 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
107 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Update_Operation_Line l_msg_data = ' || l_msg_data);
108 END IF;
109 RETURN;
110 END IF;
111 END SET_OL_SEL;
112
113 BEGIN
114 x_return_status := OKC_API.G_RET_STS_SUCCESS;
115 SET_OL_SEL(p_id);
116 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
117 RAISE OKC_API.G_EXCEPTION_ERROR;
118 END IF;
119 FOR parent_rec IN parent_cur
120 LOOP
121 SET_OL_SEL(parent_rec.id);
122 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
123 RAISE OKC_API.G_EXCEPTION_ERROR;
124 END IF;
125 EXIT;
126 END LOOP;
127 END SET_OL_SELECTED;
128
129 ----------------------------------------------------------------------------------------
130 -- This procedure returns the valid source contract lines for a given target
131 ----------------------------------------------------------------------------------------
132 PROCEDURE GET_VALID_OPER_LINE_SOURCES (p_target_id IN NUMBER,
133 x_sources_tbl OUT NOCOPY sources_tbl_type,
134 x_return_status OUT NOCOPY VARCHAR2,
135 p_conc_program IN VARCHAR2,
136 p_select_yn IN VARCHAR2 DEFAULT 'N')
137 IS
138
139 l_api_name CONSTANT VARCHAR2(30) := 'GET_VALID_OPER_LINE_SOURCES';
140 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
141
142
143 -------------------------------------------------------------------
144 ----SOURCE HEADER RULES
145 -------------------------------------------------------------------
146 x_chr_type VARCHAR2(200) := 'CYA';
147 x_template_yn VARCHAR2(200) := 'N';
148 x_buy_or_sell VARCHAR2(200) := 'S';
149 x_issue_or_receive VARCHAR2(200) := 'I';
150 x_start_date DATE := NULL;
151 x_end_date DATE := NULL;
152 ------------------------------------------------------------------
153 ----TARGET HEADER MATCHING RULES
154 ------------------------------------------------------------------
155 x_scs_code VARCHAR2(200) := NULL;
156 x_org_id NUMBER := OKC_API.G_MISS_NUM; --mmadhavi MOAC : changed to org_id
157 x_inv_organization_id NUMBER := OKC_API.G_MISS_NUM;
158 x_party_id VARCHAR2(200) := NULL;
159 x_currency_code VARCHAR2(200) := NULL;
160
161 ------------------------------------------------------------------
162 ---IS TARGET VALID
163 ------------------------------------------------------------------
164 CURSOR valid_target (p_target_id IN NUMBER) IS
165 SELECT
166 h.scs_code,
167 h.start_date,
168 h.end_date,
169 h.org_id, --mmadhavi MOAC : changed to org_id
170 h.inv_organization_id,
171 pr.object1_id1,
172 h.currency_code
173 FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
174 okc_k_party_roles_b pr,
175 okc_k_party_roles_b pr1,
176 okc_statuses_b st
177 WHERE h.id = p_target_id
178 AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
179 AND pr.rle_code = 'CUSTOMER'
180 AND h.id = pr1.dnz_chr_id AND pr1.cle_id IS NULL
181 AND pr1.rle_code = 'VENDOR'
182 AND h.scs_code IN ('SERVICE', 'WARRANTY')
183 AND h.template_yn = x_template_yn
184 AND h.buy_or_sell = x_buy_or_sell
185 AND h.issue_or_receive = x_issue_or_receive
186 AND h.chr_type = x_chr_type
187 AND h.sts_code = st.code
188 AND st.ste_code = 'ENTERED'
189 FOR UPDATE OF h.scs_code NOWAIT;
190
191 ------------------------------------------------------------------
192 --- Get operation lines for the target contract sublines only
193 -- The cursor was changed by MSENGUPT on 11/07 to filter out DNR lines for bug4719668
194 -- and terminated lines. Also simplified the fetch for sublines.
195 ------------------------------------------------------------------
196 CURSOR operation_lines(p_target_id IN NUMBER) IS
197 SELECT opl.id, oie_id, object_chr_id, object_cle_id, parent_ole_id, select_yn
198 FROM okc_operation_lines OPL,
199 okc_k_lines_b cle
200 WHERE OPL.subject_chr_id = p_target_id
201 AND EXISTS (SELECT 'X'
202 FROM okc_operation_instances OPI
203 WHERE OPI.id = OPL.oie_id
204 AND OPI.cop_id = G_RENCON_CLASS_OPERATION_ID -- Bug#5981381: Use cached class_operation_id
205 /* (SELECT ID
206 FROM OKC_CLASS_OPERATIONS
207 WHERE OPN_CODE = 'REN_CON'
208 AND CLS_CODE = 'SERVICE'
209 )
210 */
211 )
212 and cle.id = opl.object_cle_id
213 and NVL(line_renewal_type_code,'FUL') not in ('KEP','DNR') -- bug 5078797
214 and date_terminated is null
215 and cle_id is not null
216 AND exists
217 (SELECT 1 from okc_k_lines_b cle1
218 WHERE cle1.id = cle.cle_id
219 AND NVL(cle1.line_renewal_type_code,'FUL') not in ('DNR', 'KEP'))
220 /*
221 MINUS
222 (SELECT id, oie_id, object_chr_id, object_cle_id, parent_ole_id, select_yn
223 FROM okc_operation_lines
224 WHERE subject_chr_id = p_target_id
225 AND parent_ole_id IS NULL
226 UNION
227 SELECT a.id, a.oie_id, a.object_chr_id, a.object_cle_id, a.parent_ole_id, a.select_yn
228 FROM okc_operation_lines a, okc_operation_lines b
229 WHERE a.subject_chr_id = p_target_id
230 AND b.subject_chr_id = p_target_id
231 AND a.id = b.parent_ole_id)
232 */
233 -- Added following code for bug#5981381 i.e. removed looping of function call of not_processed_by_other
234 -- and moved the entire code from the cursor to here
235 AND NOT EXISTS
236 ( SELECT '1' FROM okc_operation_lines
237 WHERE object_cle_id = opl.object_cle_id
238 AND subject_chr_id <> p_target_id
239 AND process_flag = 'P'
240 AND active_yn = 'Y')
241 ORDER BY parent_ole_id;
242
243
244
245 -- Added for bug # 2870380
246 CURSOR get_correct_status(l_source_chr_id IN NUMBER, l_source_subline_id IN NUMBER) IS
247 SELECT h.id
248 FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
249 okc_k_party_roles_b pr,
250 okc_statuses_b st
251 , okc_k_lines_b s
252 WHERE h.id = l_source_chr_id
253 AND h.scs_code IN ('WARRANTY', x_scs_code)
254 AND h.chr_type = x_chr_type
255 AND h.template_yn = x_template_yn
256 AND h.buy_or_sell = x_buy_or_sell
257 AND h.issue_or_receive = x_issue_or_receive
258 AND h.end_date BETWEEN x_start_date AND x_end_date
259 AND h.inv_organization_id = x_inv_organization_id
260 AND h.org_id = x_org_id
261 AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
262 AND pr.rle_code = 'CUSTOMER'
263 AND pr.object1_id1 = x_party_id
264 --and h.currency_code = x_currency_code
265 AND (
266 (x_currency_code <> 'EUR' AND x_currency_code = h.currency_code)
267 OR
268 (x_currency_code = 'EUR' AND (h.currency_code = 'EUR' OR
269 OKC_CURRENCY_API.IS_EURO_CONVERSION_NEEDED(h.currency_code) = 'Y' )
270 )
271 )
272 --and h.date_renewed is NULL
273 AND h.sts_code = st.code
274 AND st.ste_code IN ('ACTIVE', 'EXPIRED', 'SIGNED')
275 AND s.dnz_chr_id = h.id
276 AND s.id = l_source_subline_id
277 AND s.end_date BETWEEN x_start_date AND x_end_date
278 AND s.cle_id IS NOT NULL
279 --AND s.date_renewed is NULL
280 AND s.lse_id IN (35, 7, 8, 9, 10, 11, 25);
281
282 --------------------------------------------------------------------
283
284 --------------------------------------------------------------------
285 ---PROGRAM VARIABLES
286 --------------------------------------------------------------------
287 i NUMBER := 0;
288 x_oie_id NUMBER ;
289 x_program_status VARCHAR2(200) := G_TARGET_VALID;
290 ---------------------------------------------------------------------------------
291 --- Function to check operation lines for the target contract sublines which are not terminated
292 --- added for bug#3354678
293 ---------------------------------------------------------------------------------
294 FUNCTION opl_terminated(p_opl_id IN NUMBER) RETURN BOOLEAN IS
295
296 CURSOR get_terminated_opl(cp_opl_id IN NUMBER) IS
297 SELECT 1
298 FROM okc_operation_lines OPL,
299 okc_k_lines_b KLN
300 WHERE OPL.id = cp_opl_id
301 AND OPL.object_cle_id = KLN.id
302 AND KLN.date_terminated IS NOT NULL;
303
304 l_dummy NUMBER;
305
306 BEGIN
307 OPEN get_terminated_opl(p_opl_id);
308 FETCH get_terminated_opl INTO l_dummy;
309 IF get_terminated_opl%FOUND THEN
310 CLOSE get_terminated_opl;
311 RETURN TRUE;
312 END IF;
313 CLOSE get_terminated_opl;
314
315 RETURN FALSE;
316
317 END;
318
319 -----------------------------------------------------------------------------
320 ---PROGRAM STARTS HERE
321 -----------------------------------------------------------------------------
322 BEGIN
323 l_conc_program := p_conc_program;
324 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
325 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'l_conc_program = ' || l_conc_program);
326 END IF;
327
328 -----------------------------------------------------------------------------
329 ---Check for validity of target
330 -----------------------------------------------------------------------------
331 BEGIN
332 OPEN Valid_target(p_target_id) ;
333 FETCH valid_target INTO
334 x_scs_code,
335 x_start_date,
336 x_end_date,
337 x_org_id,
338 x_inv_organization_id,
339 x_party_id,
340 x_currency_code;
341 CLOSE valid_target;
342 x_start_date := x_start_date - 1;
343 x_end_date := x_end_date - 1;
344
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN
347 OKC_API.set_message(p_app_name => 'OKS',
348 p_msg_name => 'OKS_INVALID_TARGET',
349 p_token1 => NULL,
350 p_token1_value => NULL);
351 -- Bug#5981381: Performance avoid calling log messages if not called from UI
352 if p_conc_program IS NOT NULL THEN
353 LOG_MESSAGES('TARGET IS INVALID');
354 end if;
355 x_program_status := G_TARGET_INVALID;
356 x_return_status := OKC_API.G_RET_STS_ERROR;
357 END;
358 ------------------------------------------------------------------------------
359 ---Check Operation Lines to see if there are operations already in that table
360 ------------------------------------------------------------------------------
361 -- Bug#5981381: Performance avoid calling log messages if not called from UI
362 if p_conc_program IS NOT NULL THEN
363 LOG_MESSAGES('IS_TARGET_VALID x_program_status := ' || x_program_status );
364 end if;
365 BEGIN
366 i := 1;
367 IF x_program_status <> G_TARGET_INVALID THEN
368
369 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
370 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'FROM Operation Lines');
371 END IF;
372
373 FOR cur_operation_lines IN operation_lines(p_target_id)
374 LOOP
375 -- IF NOT opl_terminated(cur_operation_lines.id) THEN
376 -- Added following code for bug#5981381 i.e. removed looping of function call of not_processed_by_other
377 -- IF object_line_processed_by_other(target_id => p_target_id,
378 -- source_line_id => cur_operation_lines.object_cle_id) = FALSE THEN
379 -- Added for bug # 2870380
380 FOR get_correct_sts IN get_correct_status(cur_operation_lines.object_chr_id, cur_operation_lines.object_cle_id)
381 LOOP
382 x_program_status := 'S';
383 x_sources_tbl(i).operation_lines_id := cur_operation_lines.id;
384 x_sources_tbl(i).contract_id := get_correct_sts.id; --cur_operation_lines.object_chr_id;
385 x_sources_tbl(i).line_id := get_parent_line_id(cur_operation_lines.parent_ole_id);
386 x_sources_tbl(i).subline_id := cur_operation_lines.object_cle_id;
387 x_sources_tbl(i).parent_ole_id := cur_operation_lines.parent_ole_id;
388 x_sources_tbl(i).oie_id := cur_operation_lines.oie_id;
389 x_sources_tbl(i).select_yn := cur_operation_lines.select_yn;
390 x_sources_tbl(i).ol_status := find_ol_status(p_object_cle_id => cur_operation_lines.object_cle_id);
391 IF p_select_yn = 'Y' AND NVL(cur_operation_lines.select_yn, 'N') = 'N' THEN
392 set_ol_selected(p_id => cur_operation_lines.id,
393 x_return_status => x_return_status);
394
395 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
396 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'set_ol_selected l_return_status: '|| x_return_status);
397 END IF;
398
399 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
400 x_sources_tbl(i).select_yn := 'Y';
401 ELSE
402
403 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
404 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'Unable to set select_yn to Y ');
405 END IF;
406
407 x_return_status := OKC_API.G_RET_STS_ERROR;
408 RETURN;
409 END IF;
410 END IF;
411
412 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
413 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'i = ' || i ||'Contract ID = ' || x_sources_tbl(i).contract_id || ' Line ID = ' || x_sources_tbl(i).line_id || ' Subline ID = ' || x_sources_tbl(i).subline_id);
414 END IF;
415
416 i := i + 1;
417 END LOOP; -- get_correct_sts
418 -- END IF; -- if object_line_processed_by_other
419 -- END IF; -- not terminated
420 END LOOP; -- cur_operation_lines
421 END IF;
422 END;
423 END GET_VALID_OPER_LINE_SOURCES;
424
425 ----------------------------------------------------------------------------------------
426 -- This procedure returns the valid source contract lines for a given target
427 ----------------------------------------------------------------------------------------
428 PROCEDURE GET_VALID_LINE_SOURCES (p_target_id IN NUMBER,
429 x_sources_tbl OUT NOCOPY sources_tbl_type,
430 x_return_status OUT NOCOPY VARCHAR2,
431 p_conc_program IN VARCHAR2,
432 p_select_yn IN VARCHAR2 DEFAULT 'N')
433 IS
434
435 l_api_name CONSTANT VARCHAR2(30) := 'GET_VALID_LINE_SOURCES';
436 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
437
438 -------------------------------------------------------------------
439 ----SOURCE HEADER RULES
440 -------------------------------------------------------------------
441 x_chr_type VARCHAR2(200) := 'CYA';
442 x_template_yn VARCHAR2(200) := 'N';
443 x_buy_or_sell VARCHAR2(200) := 'S';
444 x_issue_or_receive VARCHAR2(200) := 'I';
445 x_start_date DATE := NULL;
446 x_end_date DATE := NULL;
447
448 ------------------------------------------------------------------
449 ----TARGET HEADER MATCHING RULES
450 ------------------------------------------------------------------
451 x_scs_code VARCHAR2(200) := NULL;
452 x_org_id NUMBER := OKC_API.G_MISS_NUM; --mmadhavi MOAC : changed to org_id
453 x_inv_organization_id NUMBER := OKC_API.G_MISS_NUM;
454 x_party_id VARCHAR2(200) := NULL;
455 x_currency_code VARCHAR2(200) := NULL;
456
457 ------------------------------------------------------------------
458 ---IS TARGET VALID
459 ------------------------------------------------------------------
460 CURSOR valid_target (p_target_id IN NUMBER) IS
461 SELECT
462 h.scs_code,
463 h.start_date,
464 h.end_date,
465 h.org_id, --mmadhavi MOAC : changed to org_id
466 h.inv_organization_id,
467 pr.object1_id1,
468 h.currency_code
469 FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
470 okc_k_party_roles_b pr,
471 okc_k_party_roles_b pr1,
472 okc_statuses_b st
473 WHERE h.id = p_target_id
474 AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
475 AND pr.rle_code = 'CUSTOMER'
476 AND h.id = pr1.dnz_chr_id AND pr1.cle_id IS NULL
477 AND pr1.rle_code = 'VENDOR'
478 AND h.scs_code IN ('SERVICE', 'WARRANTY')
479 AND h.template_yn = x_template_yn
480 AND h.buy_or_sell = x_buy_or_sell
481 AND h.issue_or_receive = x_issue_or_receive
482 AND h.chr_type = x_chr_type
483 AND h.sts_code = st.code
484 AND st.ste_code = 'ENTERED'
485 FOR UPDATE OF h.scs_code NOWAIT;
486
487 ------------------------------------------------------------------
488 ---Get the valid Source Header Contracts
489 ------------------------------------------------------------------
490
491 CURSOR valid_header_sources(p_target_id IN NUMBER) IS
492 SELECT
493 h.id
494 FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
495 okc_k_party_roles_b pr,
496 okc_statuses_b st
497 WHERE h.scs_code IN ('WARRANTY', x_scs_code)
498 AND h.chr_type = x_chr_type
499 AND h.template_yn = x_template_yn
500 AND h.buy_or_sell = x_buy_or_sell
501 AND h.issue_or_receive = x_issue_or_receive
502 AND h.end_date BETWEEN x_start_date AND x_end_date
503 AND h.inv_organization_id = x_inv_organization_id
504 AND h.org_id = x_org_id --mmadhavi MOAC : changed to org_id
505 AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
506 AND pr.rle_code = 'CUSTOMER'
507 AND pr.object1_id1 = x_party_id
508 AND h.currency_code = x_currency_code
509 --and h.date_renewed is NULL
510 AND h.sts_code = st.code
511 AND st.ste_code IN ('ACTIVE', 'EXPIRED', 'SIGNED');
512
513 -- Use this cursor only if the target contract currency is EUR
514 CURSOR valid_header_sources_eur(p_target_id IN NUMBER) IS
515 SELECT
516 h.id
517 FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
518 okc_k_party_roles_b pr,
519 okc_statuses_b st
520 WHERE h.scs_code IN ('WARRANTY', x_scs_code)
521 AND h.chr_type = x_chr_type
522 AND h.template_yn = x_template_yn
523 AND h.buy_or_sell = x_buy_or_sell
524 AND h.issue_or_receive = x_issue_or_receive
525 AND h.end_date BETWEEN x_start_date AND x_end_date
526 AND h.inv_organization_id = x_inv_organization_id
527 AND h.org_id = x_org_id --mmadhavi MOAC : changed to org_id
528 AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
529 AND pr.rle_code = 'CUSTOMER'
530 AND pr.object1_id1 = x_party_id
531 AND (h.currency_code = 'EUR' OR
532 OKC_CURRENCY_API.IS_EURO_CONVERSION_NEEDED(h.currency_code) = 'Y' )
533 --and h.date_renewed is NULL
534 AND h.sts_code = st.code
535 AND st.ste_code IN ('ACTIVE', 'EXPIRED', 'SIGNED');
536
537 ------------------------------------------------------------------
538 ---Get the Valid Sub lines given the contract id
539 -- LLC, modified the cursor by adding date_cancelled is NULL
540 -- condition to supress the cancelled sublines
541 ------------------------------------------------------------------
542 -- The cursor was changed by MSENGUPT on 11/07 to filter out DNR lines for bug4719668
543 -- and terminated lines.
544
545 CURSOR valid_subline_sources(p_chr_id IN NUMBER , p_target_chr_id IN NUMBER) IS
546 SELECT s.id,
547 s.cle_id
548 FROM okc_k_lines_b s
549 WHERE s.dnz_chr_id = p_chr_id
550 AND s.end_date BETWEEN x_start_date AND x_end_date
551 --AND s.date_renewed is NULL
552 AND s.date_terminated IS NULL
553 AND s.date_cancelled IS NULL
554 AND s.lse_id IN (35, 7, 8, 9, 10, 11, 25)
555 AND NVL(s.line_renewal_type_code,'FUL') not in ('DNR', 'KEP') -- Added by MKS
556 AND exists
557 (SELECT 1 from okc_k_lines_b cle
558 WHERE cle.id = s.cle_id
559 AND NVL(cle.line_renewal_type_code,'FUL') not in ('DNR', 'KEP')) -- Added by MKS
560 -- Added following code for bug#5981381 i.e. removed looping of function call of is_laready_in_ol
561 -- and moved the entire code from that function to here
562 AND NOT EXISTS
563 ( SELECT '1' FROM okc_operation_lines
564 WHERE subject_chr_id = p_target_chr_id
565 AND object_cle_id = s.id) ;
566
567 ------------------------------------------------------------------
568 ---Get the Operation Instance id
569 ------------------------------------------------------------------
570
571 CURSOR oper_inst(p_target_id IN NUMBER) IS
572 SELECT id FROM okc_operation_instances
573 WHERE target_chr_id = p_target_id
574 AND cop_id = G_RENCON_CLASS_OPERATION_ID; -- Bug#5981381: Use cached class_operation_id
575 /*(SELECT ID FROM OKC_CLASS_OPERATIONS
576 WHERE OPN_CODE = 'REN_CON' AND CLS_CODE = 'SERVICE');
577 */
578
579 --------------------------------------------------------------------
580 ---PROGRAM VARIABLES
581 --------------------------------------------------------------------
582 i NUMBER := 0;
583 x_oie_id NUMBER ;
584 x_program_status VARCHAR2(200) := G_TARGET_VALID;
585 -----------------------------------------------------------------------------
586 ---PROGRAM STARTS HERE
587 -----------------------------------------------------------------------------
588 BEGIN
589 l_conc_program := p_conc_program;
590 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
591 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'l_conc_program = ' || l_conc_program);
592 END IF;
593
594 -----------------------------------------------------------------------------
595 ---Check for validity of target
596 -----------------------------------------------------------------------------
597 BEGIN
598 OPEN Valid_target(p_target_id) ;
599 FETCH valid_target INTO
600 x_scs_code,
601 x_start_date,
602 x_end_date,
603 x_org_id,
604 x_inv_organization_id,
605 x_party_id,
606 x_currency_code;
607 CLOSE valid_target;
608 EXCEPTION
609 WHEN NO_DATA_FOUND THEN
610 OKC_API.set_message(p_app_name => 'OKS',
611 p_msg_name => 'OKS_INVALID_TARGET',
612 p_token1 => NULL,
613 p_token1_value => NULL);
614 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
615 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'TARGET IS INVALID');
616 END IF;
617 x_program_status := G_TARGET_INVALID;
618 x_return_status := OKC_API.G_RET_STS_ERROR;
619 END;
620 i := 0;
621
622 x_start_date := x_start_date - 1;
623 x_end_date := x_end_date - 1;
624 ----------------------------------------------------------------------------
625 ---Find the valid contract header sources for the above valid target and
626 ---populate a PLSQL table contract_id||line_id||subline_id
627 ----------------------------------------------------------------------------
628 IF (x_program_status = G_TARGET_VALID) THEN
629
630 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
631 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'NEW TARGET NOTHING FOUND IN OPERATION LINES x_program_status := ' || x_program_status || ' p_target_id = ' || p_target_id);
632 END IF;
633
634 BEGIN
635 i := 0;
636 -----------------------------------------------------------------------------------------
637 ---The following block of code is written for target contract currency 'EUR'
638 ---and others separately. Both codes are identical except two differenct cursors are used
639 ---The IF and ELSE sections of the following codes must be modified consistently.
640 -----------------------------------------------------------------------------------------
641 IF x_currency_code = 'EUR' THEN
642
643 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
644 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'EURO Target');
645 END IF;
646
647 FOR valid_header_sources_rec IN valid_header_sources_eur(p_target_id)
648 LOOP
649
650 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
651 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'SELECTING ELIGIBLE SUBLINES valid_header_sources_rec.id = ' || valid_header_sources_rec.id);
652 END IF;
653
654 FOR valid_subline_sources_rec IN valid_subline_sources(valid_header_sources_rec.id, p_target_id)
655 LOOP
656 -- bug#5981381 i.e. removed looping of function call of is_laready_in_ol
657 -- IF already_in_ol(valid_subline_sources_rec.id, p_target_id) = FALSE THEN
658 -----------------------------------------------------------------------------
659 ---Check the LRT Rule - Top Line LRT rule and subline LRT rule should be FULL
660 -----------------------------------------------------------------------------
661 -- IF NOT ((OKS_RENCON_PVT.GET_LRT_RULE(valid_subline_sources_rec.id) IN ('DNR', 'KEP'))
662 -- OR (OKS_RENCON_PVT.GET_LRT_RULE(valid_subline_sources_rec.cle_id) IN ('DNR', 'KEP')))THEN
663 x_sources_tbl(i).contract_id := valid_header_sources_rec.id;
664 x_sources_tbl(i).line_id := valid_subline_sources_rec.cle_id;
665 x_sources_tbl(i).subline_id := valid_subline_sources_rec.id;
666 x_sources_tbl(i).select_yn := p_select_yn;
667 x_sources_tbl(i).ol_status := 'A';
668
669 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
670 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'Contract ID = ' || x_sources_tbl(i).contract_id || ' Line ID = ' || x_sources_tbl(i).line_id || ' Subline ID = ' || x_sources_tbl(i).subline_id);
671 END IF;
672 i := i + 1;
673 -- END IF;
674 -- END IF;
675 END LOOP;
676 END LOOP;
677 ELSE
678
679 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
680 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'Non-EURO Target');
681 END IF;
682
683 FOR valid_header_sources_rec IN valid_header_sources(p_target_id)
684 LOOP
685
686 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
687 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'SELECTING ELIGIBLE SUBLINES valid_header_sources_rec.id = ' || valid_header_sources_rec.id);
688 END IF;
689
690 FOR valid_subline_sources_rec IN valid_subline_sources(valid_header_sources_rec.id, p_target_id)
691 LOOP
692 -- bug#5981381 i.e. removed looping of function call of is_laready_in_ol
693 -- IF already_in_ol(valid_subline_sources_rec.id, p_target_id) = FALSE THEN
694 -----------------------------------------------------------------------------
695 ---Check the LRT Rule - Top Line LRT rule and subline LRT rule should be FULL
696 -----------------------------------------------------------------------------
697 -- IF NOT ((OKS_RENCON_PVT.GET_LRT_RULE(valid_subline_sources_rec.id) IN ('DNR', 'KEP'))
698 -- OR (OKS_RENCON_PVT.GET_LRT_RULE(valid_subline_sources_rec.cle_id) IN ('DNR', 'KEP')))THEN
699 x_sources_tbl(i).contract_id := valid_header_sources_rec.id;
700 x_sources_tbl(i).line_id := valid_subline_sources_rec.cle_id;
701 x_sources_tbl(i).subline_id := valid_subline_sources_rec.id;
702 x_sources_tbl(i).select_yn := p_select_yn;
703 x_sources_tbl(i).ol_status := 'A';
704
705 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
706 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'Contract ID = ' || x_sources_tbl(i).contract_id || ' Line ID = ' || x_sources_tbl(i).line_id || ' Subline ID = ' || x_sources_tbl(i).subline_id);
707 END IF;
708 i := i + 1;
709 -- END IF;
710 --END IF;
711 END LOOP;
712 END LOOP;
713 END IF;
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
717 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'NO CONTRACT SOURCES FOUND');
718 END IF;
719 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'valid_header_sources');
720 END;
721 ----------------------------------------------------------------------------
722 ---Create one record in OKC_OPERATION_INSTANCES_V if x_sources_tbl is not empty
723 ----------------------------------------------------------------------------
724 BEGIN
725 IF x_sources_tbl.COUNT > 0 THEN
726 DBMS_TRANSACTION.SAVEPOINT('BEFORE_TRANSACTION');
727 BEGIN
728 OPEN oper_inst(p_target_id);
729 FETCH oper_inst INTO x_oie_id;
730 IF oper_inst%NOTFOUND THEN
731 OKS_RENCON_PVT.CREATE_OPERATION_INSTANCES (p_target_chr_id => p_target_id,
732 p_oie_id => x_oie_id);
733 END IF;
734 CLOSE oper_inst;
735 EXCEPTION
736 WHEN NO_DATA_FOUND THEN
737 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
738 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKS_RENCON_PVT.CREATE_OPERATION_INSTANCES NO_DATA_FOUND');
739 END IF;
740 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'CREATE_OPERATION_INSTANCES');
741 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
742 WHEN OTHERS THEN
743 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
744 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKS_RENCON_PVT.CREATE_OPERATION_INSTANCES OTHERS ');
745 END IF;
746 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
747 G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
748 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
749 END;
750 ----------------------------------------------------------------------------
751 ---Create OKC_OPERATIONS_LINES and update the PLSQL table with the oper_lines_id
752 ----------------------------------------------------------------------------
753
754 BEGIN
755 OKS_RENCON_PVT.CREATE_OPERATION_LINES(p_target_chr_id => p_target_id,
756 p_oie_id => x_oie_id,
757 p_sources_tbl_type => x_sources_tbl) ;
758 EXCEPTION
759 WHEN NO_DATA_FOUND THEN
760 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
761 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKS_RENCON_PVT.CREATE_OPERATION_LINES NO_DATA_FOUND');
762 END IF;
763 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'CREATE_OPERATION_LINES');
764 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
765 WHEN OTHERS THEN
766 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
767 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKS_RENCON_PVT.CREATE_OPERATION_LINES OTHERS ');
768 END IF;
769 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
770 G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
771 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
772 END;
773 END IF;
774 END;
775
776 x_sources_tbl.DELETE;
777 GET_VALID_OPER_LINE_SOURCES (p_target_id => p_target_id,
778 x_sources_tbl => x_sources_tbl,
779 x_return_status => x_return_status,
780 p_conc_program => p_conc_program,
781 p_select_yn => p_select_yn);
782 END IF;
783 END GET_VALID_LINE_SOURCES;
784
785 ----------------------------------------------------------------------------
786 ---Create OKC_OPERATION_INSTANCES_V
787 ----------------------------------------------------------------------------
788
789 PROCEDURE CREATE_OPERATION_INSTANCES (p_target_chr_id IN NUMBER,
790 p_oie_id OUT NOCOPY NUMBER) IS
791 ------------------------------------------------------------------
792 ---TAPI variables
793 ------------------------------------------------------------------
794 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_OPERATION_INSTANCES';
795 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
796
797 l_api_version CONSTANT NUMBER := 1.0;
798 l_init_msg_list VARCHAR2(2000) := OKC_API.G_FALSE;
799 l_return_status VARCHAR2(1);
800 l_msg_count NUMBER;
801 l_msg_data VARCHAR2(2000);
802
803 l_msg_index_out NUMBER;
804
805 l_oiev_tbl_in OKC_OPER_INST_PUB.oiev_tbl_type; --OPERATION INSTANCE
806 l_oiev_tbl_out OKC_OPER_INST_PUB.oiev_tbl_type; --OPERATION INSTANCE
807 --------------------------------------------------------------------
808 ---Program Variables
809 --------------------------------------------------------------------
810 p_class_operation_id NUMBER := 0;
811 -------------------------------------------------------------------------
812 ---Find the Class Operation ID to be used
813 --------------------------------------------------------------------------
814 CURSOR class_operations IS
815 SELECT ID FROM OKC_CLASS_OPERATIONS
816 WHERE OPN_CODE = 'REN_CON' AND CLS_CODE = 'SERVICE';
817
818 BEGIN
819 -- Bug#5981381: Use cached class_operation_id
820 /*
821 FOR cur_class_operations IN class_operations LOOP
822 p_class_operation_id := cur_class_operations.id;
823 EXIT;
824 END LOOP;
825 */
826
827 l_oiev_tbl_in(1).name := OKC_API.G_MISS_CHAR;
828 -- l_oiev_tbl_in(1).cop_id := p_class_operation_id;
829 l_oiev_tbl_in(1).cop_id := G_RENCON_CLASS_OPERATION_ID;
830 l_oiev_tbl_in(1).status_code := G_OI_STATUS_CODE;
831 l_oiev_tbl_in(1).target_chr_id := p_target_chr_id;
832 l_oiev_tbl_in(1).object_version_number := OKC_API.G_MISS_NUM;
833 l_oiev_tbl_in(1).created_by := OKC_API.G_MISS_NUM;
834 l_oiev_tbl_in(1).creation_date := SYSDATE;
835 l_oiev_tbl_in(1).last_updated_by := OKC_API.G_MISS_NUM;
836 l_oiev_tbl_in(1).last_update_date := SYSDATE;
837 l_oiev_tbl_in(1).last_update_login := OKC_API.G_MISS_NUM;
838
839 OKC_OPER_INST_PUB.Create_Operation_Instance(
840 p_api_version => l_api_version,
841 p_init_msg_list => l_init_msg_list,
842 x_return_status => l_return_status,
843 x_msg_count => l_msg_count,
844 x_msg_data => l_msg_data,
845 p_oiev_tbl => l_oiev_tbl_in,
846 x_oiev_tbl => l_oiev_tbl_out
847 );
848 p_oie_id := l_oiev_tbl_out(1).id;
849
850 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
851 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Create_Operation_Instance l_return_status = ' || l_return_status);
852 END IF;
853
854 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
855 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
856 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Create_Operation_Instance l_msg_data = ' || l_msg_data);
857 END IF;
858 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
859 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
860 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
861 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Create_Operation_Instance l_msg_data = ' || l_msg_data);
862 END IF;
863 RAISE OKC_API.G_EXCEPTION_ERROR;
864 END IF;
865
866 END CREATE_OPERATION_INSTANCES;
867
868
869 ----------------------------------------------------------------------------
870 ---Create OKC_OPERATIONS_LINES
871 ----------------------------------------------------------------------------
872
873 PROCEDURE CREATE_OPERATION_LINES (p_target_chr_id IN NUMBER,
874 p_oie_id IN NUMBER,
875 p_sources_tbl_type IN OUT NOCOPY OKS_RENCON_PVT.sources_tbl_type,
876 p_select_yn IN VARCHAR2 DEFAULT 'N') IS
877 ----------------------------------------------------------------------------
878 ---TAPI variables
879 ----------------------------------------------------------------------------
880 l_api_version CONSTANT NUMBER := 1.0;
881 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_OPERATION_LINES';
882 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
883
884 l_init_msg_list VARCHAR2(2000) := OKC_API.G_FALSE;
885 l_return_status VARCHAR2(1);
886 l_msg_count NUMBER;
887 l_msg_data VARCHAR2(2000);
888
889 l_msg_index_out NUMBER;
890
891 l_olev_tbl_in OKC_OPER_INST_PUB.olev_tbl_type; --OPERATION LINES
892 l_olev_tbl_out OKC_OPER_INST_PUB.olev_tbl_type; --OPERATION LINES
893
894
895 ------------------------------------------------------------------
896 ---PROGRAM variables
897 ------------------------------------------------------------------
898 i NUMBER := 0;
899 j NUMBER := 0;
900
901 TYPE t_id_table IS TABLE OF OKS_RENCON_PVT.sources_rec_type
902 INDEX BY BINARY_INTEGER;
903
904 v_id_list OKS_RENCON_PVT.sources_tbl_type;
905 v_id_list_k OKS_RENCON_PVT.sources_tbl_type;
906 v_ole_id NUMBER;
907
908 FUNCTION IS_K_PRESENT(p_contract_id IN NUMBER, p_operation_lines_id OUT NOCOPY NUMBER, p_id_list IN OKS_RENCON_PVT.sources_tbl_type) RETURN VARCHAR2 IS
909 x_return VARCHAR2(200) := 'N';
910 v_index BINARY_INTEGER;
911 BEGIN
912
913 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
914 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'p_id_list.COUNT = ' || p_id_list.COUNT);
915 END IF;
916
917 IF p_id_list.COUNT > 0 THEN
918 v_index := p_id_list.FIRST;
919 LOOP
920
921 IF p_id_list(v_index).contract_id = p_contract_id THEN
922 p_operation_lines_id := p_id_list(v_index).operation_lines_id;
923 x_return := 'Y';
924 -- Bug#5981381: Added following statement to avoid unnecessary looping once contract found.
925 exit;
926 END IF;
927 EXIT WHEN v_index = p_id_list.LAST;
928 v_index := p_id_list.NEXT(v_index);
929
930 END LOOP;
931 END IF;
932
933 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
934 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'IS_K_PRESENT p_contract_id = '|| p_contract_id || 'p_operation_lines_id = ' || p_operation_lines_id || ' x_return = ' || x_return);
935 END IF;
936
937 RETURN(x_return);
938 EXCEPTION
939 WHEN NO_DATA_FOUND THEN
940 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
941 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKS_RENCON_PVT.IS_K_PRESENT NO_DATA_FOUND');
942 END IF;
943 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'IS_PRESENT');
944 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
945 WHEN OTHERS THEN
946 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
947 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKS_RENCON_PVT.IS_K_PRESENT OTHERS ');
948 END IF;
949 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
950 G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
951 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
952 END IS_K_PRESENT;
953
954
955 FUNCTION IS_PRESENT(p_line_id IN NUMBER, p_operation_lines_id OUT NOCOPY NUMBER, p_id_list IN OKS_RENCON_PVT.sources_tbl_type) RETURN VARCHAR2 IS
956 x_return VARCHAR2(200) := 'N';
957 v_index BINARY_INTEGER;
958 BEGIN
959 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
960 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'p_id_list.COUNT = ' || p_id_list.COUNT);
961 END IF;
962 IF p_id_list.COUNT > 0 THEN
963 v_index := p_id_list.FIRST;
964 LOOP
965
966 IF p_id_list(v_index).line_id = p_line_id THEN
967 p_operation_lines_id := p_id_list(v_index).operation_lines_id;
968 x_return := 'Y';
969 -- Bug#5981381: Added following statement to avoid unnecessary looping once contract found.
970 exit;
971 END IF;
972 EXIT WHEN v_index = p_id_list.LAST;
973 v_index := p_id_list.NEXT(v_index);
974
975 END LOOP;
976 END IF;
977
978 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
979 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'IS_PRESENT p_line_id = '|| p_line_id || 'p_operation_lines_id = ' || p_operation_lines_id || ' x_return = ' || x_return);
980 END IF;
981
982 RETURN(x_return);
983 EXCEPTION
984 WHEN NO_DATA_FOUND THEN
985 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
986 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKS_RENCON_PVT.IS_PRESENT NO_DATA_FOUND');
987 END IF;
988 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'IS_PRESENT');
989 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
990 WHEN OTHERS THEN
991 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
992 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKS_RENCON_PVT.IS_PRESENT OTHERS ');
993 END IF;
994 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
995 G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
996 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
997 END IS_PRESENT;
998
999 FUNCTION GET_HDR_OLE_ID(p_header_id IN NUMBER) RETURN NUMBER IS
1000 CURSOR header_op_line IS
1001 SELECT id FROM OKC_OPERATION_LINES
1002 WHERE oie_id = p_oie_id
1003 AND object_chr_id = p_header_id
1004 AND object_cle_id IS NULL;
1005 x_ret_id NUMBER ;
1006 BEGIN
1007 OPEN header_op_line;
1008 FETCH header_op_line INTO x_ret_id;
1009 IF header_op_line%NOTFOUND THEN
1010 x_ret_id := 0;
1011 END IF;
1012 CLOSE header_op_line;
1013 RETURN(x_ret_id);
1014 END GET_HDR_OLE_ID;
1015
1016 FUNCTION GET_TOPLINE_OLE_ID(p_topline_id IN NUMBER) RETURN NUMBER IS
1017 CURSOR topline_op_line IS
1018 SELECT id FROM OKC_OPERATION_LINES
1019 WHERE oie_id = p_oie_id
1020 AND object_cle_id = p_topline_id;
1021 x_ret_id NUMBER ;
1022 BEGIN
1023 OPEN topline_op_line;
1024 FETCH topline_op_line INTO x_ret_id;
1025 IF topline_op_line%NOTFOUND THEN
1026 x_ret_id := 0;
1027 END IF;
1028 CLOSE topline_op_line;
1029 RETURN(x_ret_id);
1030 END GET_TOPLINE_OLE_ID;
1031
1032 ------------------------------------------------------------------------
1033 ---PROGRAM BEGINS HERE
1034 ------------------------------------------------------------------------
1035 BEGIN
1036 -----------------------------------------------------------------------
1037 ----EXTRACTING HEADER CONTRACT IDS FROM THE PLSQL TABLE
1038 -----------------------------------------------------------------------
1039 i := 0;
1040 j := 0;
1041 WHILE p_sources_tbl_type.EXISTS(j) LOOP
1042 IF IS_K_PRESENT(p_sources_tbl_type(j).contract_id, v_ole_id, v_id_list_k) = 'N' THEN
1043 v_id_list_k(i).contract_id := p_sources_tbl_type(j).contract_id;
1044 v_id_list_k(i).subline_id := NULL;
1045 v_id_list_k(i).operation_lines_id := GET_HDR_OLE_ID(v_id_list_k(i).contract_id);
1046 i := i + 1;
1047 END IF;
1048 j := j + 1;
1049 END LOOP;
1050
1051 i := 0;
1052 j := 0;
1053 -----------------------------------------------------------------------
1054 ----CREATE the header source CONTRACT HEADER in operation_lines
1055 -----------------------------------------------------------------------
1056 WHILE v_id_list_k.EXISTS(j) LOOP
1057 IF v_id_list_k(j).operation_lines_id = 0 THEN
1058 l_olev_tbl_in(i).select_yn := p_select_yn;
1059 l_olev_tbl_in(i).active_yn := 'Y';
1060 l_olev_tbl_in(i).process_flag := 'A';
1061 l_olev_tbl_in(i).oie_id := p_oie_id;
1062 l_olev_tbl_in(i).subject_chr_id := p_target_chr_id;
1063 l_olev_tbl_in(i).object_chr_id := v_id_list_k(j).contract_id;
1064 l_olev_tbl_in(i).subject_cle_id := NULL;
1065 l_olev_tbl_in(i).parent_ole_id := NULL;
1066 l_olev_tbl_in(i).object_cle_id := NULL;
1067 l_olev_tbl_in(i).object_version_number := OKC_API.G_MISS_NUM;
1068 l_olev_tbl_in(i).created_by := OKC_API.G_MISS_NUM;
1069 l_olev_tbl_in(i).creation_date := SYSDATE;
1070 l_olev_tbl_in(i).last_updated_by := OKC_API.G_MISS_NUM;
1071 l_olev_tbl_in(i).last_update_date := SYSDATE;
1072 l_olev_tbl_in(i).last_update_login := OKC_API.G_MISS_NUM;
1073 l_olev_tbl_in(i).request_id := FND_GLOBAL.CONC_REQUEST_ID;
1074 l_olev_tbl_in(i).program_application_id := FND_GLOBAL.PROG_APPL_ID;
1075 l_olev_tbl_in(i).program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1076 l_olev_tbl_in(i).program_update_date := OKC_API.G_MISS_DATE;
1077 l_olev_tbl_in(i).message_code := OKC_API.G_MISS_CHAR;
1078 i := i + 1;
1079 END IF;
1080 j := j + 1;
1081 END LOOP;
1082
1083 OKC_OPER_INST_PUB.Create_Operation_Line(
1084 p_api_version => l_api_version,
1085 p_init_msg_list => l_init_msg_list,
1086 x_return_status => l_return_status,
1087 x_msg_count => l_msg_count,
1088 x_msg_data => l_msg_data,
1089 p_olev_tbl => l_olev_tbl_in,
1090 x_olev_tbl => l_olev_tbl_out
1091 );
1092
1093 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1094 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'FOR CONTRACT HEADER OKC_OPER_INST_PUB.Create_Operation_Line l_return_status = ' || l_return_status);
1095 END IF;
1096 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1097 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1098 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Create_Operation_Line l_msg_data = ' || l_msg_data);
1099 END IF;
1100 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1101 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1102 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1103 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Create_Operation_Line l_msg_data = ' || l_msg_data);
1104 END IF;
1105 RAISE OKC_API.G_EXCEPTION_ERROR;
1106 END IF;
1107 ------------------------------------------------------------------------------------
1108 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1109 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'Update the v_id_list_k with the operation_lines_id');
1110 END IF;
1111 ------------------------------------------------------------------------------------
1112 i := 0;
1113 j := 0;
1114 WHILE v_id_list_k.EXISTS(j) LOOP
1115 IF v_id_list_k(j).operation_lines_id = 0 THEN
1116 v_id_list_k(j).operation_lines_id := l_olev_tbl_out(i).id;
1117 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1118 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'TOP CONTRACT HEADER LINE l_olev_tbl_out('|| i ||').id = ' || l_olev_tbl_out(i).id);
1119 END IF;
1120 i := i + 1;
1121 END IF;
1122 j := j + 1;
1123 END LOOP;
1124
1125 -----------------------------------------------------------------------
1126 -----------------------------------------------------------------------
1127 ----EXTRACT TOP lines FROM PLSQL TABLE
1128 -----------------------------------------------------------------------
1129 i := 0;
1130 j := 0;
1131 WHILE p_sources_tbl_type.EXISTS(j) LOOP
1132 IF IS_PRESENT(p_sources_tbl_type(j).line_id, v_ole_id, v_id_list) = 'N' THEN
1133 v_id_list(i).line_id := p_sources_tbl_type(j).line_id;
1134 v_id_list(i).contract_id := p_sources_tbl_type(j).contract_id;
1135 v_id_list(i).subline_id := NULL;
1136 v_id_list(i).operation_lines_id := GET_TOPLINE_OLE_ID(v_id_list(i).line_id);
1137 i := i + 1;
1138 END IF;
1139 j := j + 1;
1140 END LOOP;
1141
1142 i := 0;
1143 j := 0;
1144 -----------------------------------------------------------------------
1145 ----CREATE the header source TOP lines in operation_lines
1146 -----------------------------------------------------------------------
1147 WHILE v_id_list.EXISTS(j) LOOP
1148 IF v_id_list(j).operation_lines_id = 0 THEN
1149 l_olev_tbl_in(i).select_yn := p_select_yn;
1150 l_olev_tbl_in(i).active_yn := 'Y';
1151 l_olev_tbl_in(i).process_flag := 'A';
1152 l_olev_tbl_in(i).oie_id := p_oie_id;
1153 l_olev_tbl_in(i).subject_chr_id := p_target_chr_id;
1154 l_olev_tbl_in(i).object_chr_id := v_id_list(j).contract_id;
1155 l_olev_tbl_in(i).subject_cle_id := OKC_API.G_MISS_NUM;
1156 IF IS_K_PRESENT(v_id_list(j).contract_id, v_ole_id, v_id_list_k) = 'Y' THEN
1157 l_olev_tbl_in(i).parent_ole_id := v_ole_id;
1158 END IF;
1159 l_olev_tbl_in(i).object_cle_id := v_id_list(j).line_id;
1160 l_olev_tbl_in(i).object_version_number := OKC_API.G_MISS_NUM;
1161 l_olev_tbl_in(i).created_by := OKC_API.G_MISS_NUM;
1162 l_olev_tbl_in(i).creation_date := SYSDATE;
1163 l_olev_tbl_in(i).last_updated_by := OKC_API.G_MISS_NUM;
1164 l_olev_tbl_in(i).last_update_date := SYSDATE;
1165 l_olev_tbl_in(i).last_update_login := OKC_API.G_MISS_NUM;
1166 l_olev_tbl_in(i).request_id := FND_GLOBAL.CONC_REQUEST_ID;
1167 l_olev_tbl_in(i).program_application_id := FND_GLOBAL.PROG_APPL_ID;
1168 l_olev_tbl_in(i).program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1169 l_olev_tbl_in(i).program_update_date := OKC_API.G_MISS_DATE;
1170 l_olev_tbl_in(i).message_code := OKC_API.G_MISS_CHAR;
1171 i := i + 1;
1172 END IF;
1173 j := j + 1;
1174 END LOOP;
1175
1176 OKC_OPER_INST_PUB.Create_Operation_Line(
1177 p_api_version => l_api_version,
1178 p_init_msg_list => l_init_msg_list,
1179 x_return_status => l_return_status,
1180 x_msg_count => l_msg_count,
1181 x_msg_data => l_msg_data,
1182 p_olev_tbl => l_olev_tbl_in,
1183 x_olev_tbl => l_olev_tbl_out
1184 );
1185
1186 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1187 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'FOR TOP LINES OKC_OPER_INST_PUB.Create_Operation_Line l_return_status = ' || l_return_status);
1188 END IF;
1189
1190 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1191 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1192 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Create_Operation_Line l_msg_data = ' || l_msg_data);
1193 END IF;
1194 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1195 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1196 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1197 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Create_Operation_Line l_msg_data = ' || l_msg_data);
1198 END IF;
1199 RAISE OKC_API.G_EXCEPTION_ERROR;
1200 END IF;
1201 ------------------------------------------------------------------------------------
1202 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1203 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'Update the v_id_list with the operation_lines_id');
1204 END IF;
1205 ------------------------------------------------------------------------------------
1206 i := 0;
1207 j := 0;
1208 WHILE v_id_list.EXISTS(j) LOOP
1209 IF v_id_list(j).operation_lines_id = 0 THEN
1210 v_id_list(j).operation_lines_id := l_olev_tbl_out(i).id;
1211 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1212 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'TOP LINE l_olev_tbl_out('|| i ||').id = ' || l_olev_tbl_out(i).id);
1213 END IF;
1214 i := i + 1;
1215 END IF;
1216 j := j + 1;
1217 END LOOP;
1218
1219 -----------------------------------------------------------------------
1220 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1221 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'CREATE the header source sublines in operation_lines');
1222 END IF;
1223 -----------------------------------------------------------------------
1224 i := 0;
1225 j := 0;
1226 l_olev_tbl_in.DELETE;
1227 WHILE p_sources_tbl_type.EXISTS(j) LOOP
1228 l_olev_tbl_in(i).select_yn := p_select_yn;
1229 l_olev_tbl_in(i).active_yn := 'Y';
1230 l_olev_tbl_in(i).process_flag := 'A';
1231 l_olev_tbl_in(i).oie_id := p_oie_id;
1232 l_olev_tbl_in(i).subject_chr_id := p_target_chr_id;
1233 l_olev_tbl_in(i).object_chr_id := p_sources_tbl_type(j).contract_id;
1234 l_olev_tbl_in(i).subject_cle_id := OKC_API.G_MISS_NUM;
1235 IF IS_PRESENT(p_sources_tbl_type(j).line_id, v_ole_id, v_id_list) = 'Y' THEN
1236 l_olev_tbl_in(i).parent_ole_id := v_ole_id;
1237 END IF;
1238 l_olev_tbl_in(i).object_cle_id := p_sources_tbl_type(j).subline_id;
1239 l_olev_tbl_in(i).object_version_number := OKC_API.G_MISS_NUM;
1240 l_olev_tbl_in(i).created_by := OKC_API.G_MISS_NUM;
1241 l_olev_tbl_in(i).creation_date := SYSDATE;
1242 l_olev_tbl_in(i).last_updated_by := OKC_API.G_MISS_NUM;
1243 l_olev_tbl_in(i).last_update_date := SYSDATE;
1244 l_olev_tbl_in(i).last_update_login := OKC_API.G_MISS_NUM;
1245 l_olev_tbl_in(i).request_id := FND_GLOBAL.CONC_REQUEST_ID;
1246 l_olev_tbl_in(i).program_application_id := FND_GLOBAL.PROG_APPL_ID;
1247 l_olev_tbl_in(i).program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1248 l_olev_tbl_in(i).program_update_date := OKC_API.G_MISS_DATE;
1249 l_olev_tbl_in(i).message_code := OKC_API.G_MISS_CHAR;
1250 i := i + 1;
1251 j := j + 1;
1252 END LOOP;
1253
1254 OKC_OPER_INST_PUB.Create_Operation_Line(
1255 p_api_version => l_api_version,
1256 p_init_msg_list => l_init_msg_list,
1257 x_return_status => l_return_status,
1258 x_msg_count => l_msg_count,
1259 x_msg_data => l_msg_data,
1260 p_olev_tbl => l_olev_tbl_in,
1261 x_olev_tbl => l_olev_tbl_out
1262 );
1263
1264 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1265 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'FOR SUBLINES OKC_OPER_INST_PUB.Create_Operation_Line l_return_status = ' || l_return_status);
1266 END IF;
1267 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1268 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1269 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Create_Operation_Line l_msg_data = ' || l_msg_data);
1270 END IF;
1271 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1272 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1273 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1274 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Create_Operation_Line l_msg_data = ' || l_msg_data);
1275 END IF;
1276 RAISE OKC_API.G_EXCEPTION_ERROR;
1277 END IF;
1278 ------------------------------------------------------------------------------------
1279 --Update the p_sources_tbl_type with the operation_lines_id
1280 ------------------------------------------------------------------------------------
1281 i := 0;
1282 j := 0;
1283 WHILE p_sources_tbl_type.EXISTS(i) LOOP
1284 p_sources_tbl_type(i).operation_lines_id := l_olev_tbl_out(i).id;
1285 p_sources_tbl_type(i).parent_ole_id := l_olev_tbl_out(i).parent_ole_id;
1286 p_sources_tbl_type(i).oie_id := l_olev_tbl_out(i).oie_id;
1287 i := i + 1;
1288 END LOOP;
1289
1290 END CREATE_OPERATION_LINES;
1291
1292 ----------------------------------------------------------------------------------------
1293 --This function is used to find if a source subline is eligible for consolidation
1294 ----------------------------------------------------------------------------------------
1295 FUNCTION FIND_OL_STATUS(p_object_cle_id IN NUMBER) RETURN VARCHAR2
1296 IS
1297 CURSOR process_flag IS
1298 SELECT process_flag
1299 FROM okc_operation_lines
1300 WHERE object_cle_id = p_object_cle_id
1301 AND active_yn = 'Y';
1302
1303 x_return VARCHAR2(200) := 'A';
1304
1305 BEGIN
1306 FOR cur_process_flag IN process_flag LOOP
1307 x_return := cur_process_flag.process_flag;
1308 IF x_return = 'P' THEN
1309 EXIT;
1310 END IF;
1311 END LOOP;
1312 RETURN(x_return);
1313 END FIND_OL_STATUS;
1314
1315 ----------------------------------------------------------------------------------------
1316 ---This function is used to Check the Validity of target
1317 ----------------------------------------------------------------------------------------
1318 FUNCTION IS_VALID_TARGET(p_target_id IN NUMBER) RETURN BOOLEAN
1319 IS
1320
1321 l_api_name CONSTANT VARCHAR2(30) := 'IS_VALID_TARGET';
1322 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1323
1324 CURSOR valid_target (p_target_id IN NUMBER) IS
1325 SELECT
1326 'X'
1327 FROM okc_K_Headers_all_b h,
1328 okc_k_party_roles_b pr,
1329 okc_k_party_roles_b pr1,
1330 okc_statuses_b st
1331 WHERE h.id = p_target_id
1332 AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
1333 AND pr.rle_code = 'CUSTOMER'
1334 AND h.id = pr1.dnz_chr_id AND pr1.cle_id IS NULL
1335 AND pr1.rle_code = 'VENDOR'
1336 AND h.scs_code IN ('SERVICE', 'WARRANTY')
1337 AND h.template_yn = 'N'
1338 AND h.buy_or_sell = 'S'
1339 AND h.issue_or_receive = 'I'
1340 AND h.chr_type = 'CYA'
1341 AND h.sts_code = st.code
1342 AND st.ste_code = 'ENTERED';
1343
1344 --x_return VARCHAR2(200) := G_TARGET_INVALID;
1345 x_return BOOLEAN := FALSE;
1346 BEGIN
1347 BEGIN
1348 FOR cur_valid_target IN Valid_target(p_target_id) LOOP
1349 --x_return := G_TARGET_VALID;
1350 x_return := TRUE;
1351 END LOOP;
1352 EXCEPTION
1353 WHEN NO_DATA_FOUND THEN
1354 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1355 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'TARGET IS INVALID');
1356 END IF;
1357 --x_return := G_TARGET_INVALID;
1358 x_return := FALSE;
1359 END;
1360 IF x_return = FALSE THEN
1361 OKC_API.set_message(p_app_name => 'OKS',
1362 p_msg_name => 'OKS_INVALID_TARGET',
1363 p_token1 => NULL,
1364 p_token1_value => NULL);
1365 END IF;
1366 RETURN(x_return);
1367
1368 END IS_VALID_TARGET;
1369
1370
1371 FUNCTION GET_PARENT_LINE_ID(p_parent_ole_id IN NUMBER) RETURN NUMBER IS
1372
1373 l_api_name CONSTANT VARCHAR2(30) := 'GET_PARENT_LINE_ID';
1374 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1375
1376 CURSOR get_top_line IS
1377 SELECT object_cle_id
1378 FROM okc_operation_lines
1379 WHERE p_parent_ole_id = id;
1380 x_return NUMBER;
1381 BEGIN
1382 BEGIN
1383 FOR cur_get_top_line IN get_top_line LOOP
1384 x_return := cur_get_top_line.object_cle_id;
1385 exit; -- Added for bug#5981381
1386 END LOOP;
1387 EXCEPTION
1388 WHEN NO_DATA_FOUND THEN
1389 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1390 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'GET_PARENT_LINE_ID INVALID');
1391 END IF;
1392 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'GET_PARENT_LINE_ID');
1393 x_return := NULL;
1394 END;
1395 RETURN(x_return);
1396 END GET_PARENT_LINE_ID;
1397
1398
1399 FUNCTION GET_LRT_RULE(p_line_id IN NUMBER) RETURN VARCHAR2 IS
1400 CURSOR GET_RULE IS
1401 SELECT NVL(LINE_RENEWAL_TYPE_CODE,'FUL') LINE_RENEWAL_TYPE_CODE
1402 FROM OKC_K_LINES_B
1403 WHERE ID = p_line_id;
1404 x_return VARCHAR2(200) := 'NOLRTRULE';
1405 BEGIN
1406 FOR cur_get_rule IN get_rule LOOP
1407 x_return := cur_get_rule.LINE_RENEWAL_TYPE_CODE;
1408 END LOOP;
1409 RETURN(x_return);
1410 END GET_LRT_RULE;
1411
1412
1413 PROCEDURE GET_LINE_DETAILS(p_line_id IN NUMBER,
1414 x_line_details OUT NOCOPY OKS_RENCON_PVT.merge_rec_type) IS
1415
1416 l_api_name CONSTANT VARCHAR2(30) := 'GET_LINE_DETAILS';
1417 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1418
1419 CURSOR get_inv_and_organization_id IS
1420 SELECT object1_id1, object1_id2
1421 FROM okc_k_items
1422 WHERE cle_id = p_line_id;
1423
1424 CURSOR get_lrt_bto IS
1425 SELECT NVL(LINE_RENEWAL_TYPE_CODE,'FUL') LINE_RENEWAL_TYPE_CODE, BILL_TO_SITE_USE_ID, START_DATE, END_DATE
1426 FROM OKC_K_LINES_B
1427 WHERE ID = p_line_id;
1428
1429
1430 BEGIN
1431
1432 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1433 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','p_line_id='|| p_line_id);
1434 END IF;
1435
1436 x_line_details.line_id := p_line_id;
1437
1438 FOR cur_get_inv_organization IN get_inv_and_organization_id LOOP
1439 x_line_details.inventory_item_id := cur_get_inv_organization.object1_id1;
1440 x_line_details.inv_organization_id := cur_get_inv_organization.object1_id2;
1441 END LOOP;
1442
1443 FOR cur_get_lrt_bto IN get_lrt_bto LOOP
1444 x_line_details.lrt_rule := cur_get_lrt_bto.LINE_RENEWAL_TYPE_CODE;
1445 x_line_details.bto_id := cur_get_lrt_bto.BILL_TO_SITE_USE_ID;
1446 x_line_details.start_date := cur_get_lrt_bto.start_date;
1447 x_line_details.end_date := cur_get_lrt_bto.end_date;
1448 END LOOP;
1449
1450 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1451 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_line_details.lrt_rule='|| x_line_details.lrt_rule);
1452 END IF;
1453
1454 END GET_LINE_DETAILS;
1455
1456 ------------------------------------------------------------------------------------
1457
1458 FUNCTION MERGE_ELIGIBLE_YN(p_source_line_details IN OKS_RENCON_PVT.merge_rec_type,
1459 p_target_line_details IN OKS_RENCON_PVT.merge_rec_type)
1460 RETURN VARCHAR2 IS
1461
1462 l_api_name CONSTANT VARCHAR2(30) := 'MERGE_ELIGIBLE_YN';
1463 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1464
1465 x_return VARCHAR2(200) := 'N';
1466 x_continue VARCHAR2(200) := 'Y';
1467 l_target_duration NUMBER;
1468 l_target_timeunit VARCHAR2(200);
1469 l_source_duration NUMBER;
1470 l_source_timeunit VARCHAR2(200);
1471 l_return_status VARCHAR2(1);
1472 l_source_start_date DATE;
1473 l_source_end_date DATE;
1474
1475 l_api_version CONSTANT NUMBER := 1.0;
1476 l_init_msg_list VARCHAR2(2000) := OKC_API.G_FALSE;
1477 l_msg_count NUMBER;
1478 l_msg_data VARCHAR2(2000);
1479 l_coverage_match VARCHAR2(1);
1480 l_index NUMBER;
1481 i NUMBER;
1482
1483 BEGIN
1484
1485 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1486
1487 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin', 'begin');
1488
1489 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.input_details', 'src_line_dtls line_id='||p_source_line_details.line_id||' ,inventory_item_id='||p_source_line_details.inventory_item_id||
1490 ' ,inv_organization_id='||p_source_line_details.inv_organization_id||' ,lrt_rule='||p_source_line_details.lrt_rule||' ,bto_id='||p_source_line_details.bto_id||
1491 ' ,start_date='||p_source_line_details.start_date||' ,end_date='||p_source_line_details.end_date);
1492
1493 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.input_details', 'trg_line_dtls line_id='||p_target_line_details.line_id||' ,inventory_item_id='||p_target_line_details.inventory_item_id||
1494 ' ,inv_organization_id='||p_target_line_details.inv_organization_id||' ,lrt_rule='||p_target_line_details.lrt_rule||' ,bto_id='||p_target_line_details.bto_id||
1495 ' ,start_date='||p_target_line_details.start_date||' ,end_date='||p_target_line_details.end_date);
1496
1497 END IF;
1498
1499 --check if item id and inv org id are same
1500 IF x_continue = 'Y' THEN
1501 IF NOT (p_target_line_details.inventory_item_id = p_source_line_details.inventory_item_id
1502 AND p_target_line_details.inv_organization_id = p_source_line_details.inv_organization_id
1503 -- bug 3981824
1504 -- AND NVL(p_target_line_details.lrt_rule,'1') = NVL(p_source_line_details.lrt_rule,'1')
1505 -- end of bug 3981824
1506 -- AND NVL(p_target_line_details.bto_id,'1') = NVL(p_source_line_details.bto_id,'1')
1507 ) THEN
1508 x_continue := 'N';
1509 END IF;
1510 END IF;
1511
1512 --check if lrt, dates and coverages match
1513 IF (x_continue = 'Y') THEN
1514
1515 IF (p_source_line_details.lrt_rule = 'KEP') AND (p_target_line_details.lrt_rule = 'KEP') THEN
1516
1517 OKC_TIME_UTIL_PUB.get_duration (
1518 p_start_date => p_target_line_details.start_date,
1519 p_end_date => p_target_line_details.end_date,
1520 x_duration => l_target_duration,
1521 x_timeunit => l_target_timeunit,
1522 x_return_status => l_return_status);
1523
1524 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1525 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.lrt_KEP', 'l_target_duration='||l_target_duration||' ,l_target_timeunit='||l_target_timeunit);
1526 END IF;
1527
1528 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1529 OKC_TIME_UTIL_PUB.get_duration (
1530 p_start_date => p_source_line_details.start_date,
1531 p_end_date => p_source_line_details.end_date,
1532 x_duration => l_source_duration,
1533 x_timeunit => l_source_timeunit,
1534 x_return_status => l_return_status);
1535
1536 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1537 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.lrt_KEP', 'l_source_duration='||l_source_duration||' ,l_source_timeunit='||l_source_timeunit);
1538 END IF;
1539
1540 END IF;
1541
1542 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1543
1544 l_source_start_date := p_source_line_details.end_date + 1;
1545
1546 l_source_end_date := OKC_TIME_UTIL_PUB.get_enddate(
1547 p_start_date => l_source_start_date,
1548 p_duration => l_source_duration,
1549 p_timeunit => l_source_timeunit);
1550
1551 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1552 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.lrt_KEP', 'l_source_start_date='||l_source_start_date||' ,l_source_end_date='||l_source_end_date);
1553 END IF;
1554
1555 IF (l_source_start_date = p_target_line_details.start_date)
1556 AND (l_source_end_date = p_target_line_details.end_date) THEN
1557 x_return := 'Y';
1558 END IF;
1559
1560 END IF;
1561
1562 END IF; --of if src.lrt_rule=trg.lrt_rule=KEP
1563
1564
1565 IF (p_source_line_details.lrt_rule = 'FUL') AND (p_target_line_details.lrt_rule = 'FUL') THEN
1566
1567 l_source_start_date := p_source_line_details.end_date + 1;
1568 IF l_source_start_date >= p_target_line_details.start_date THEN
1569 x_return := 'Y';
1570 END IF;
1571 END IF;
1572
1573 -- bug 3981824
1574 -- default renewal type to FUL if null
1575 --IF (p_source_line_details.lrt_rule IS NULL) AND (p_target_line_details.lrt_rule IS NULL) THEN
1576 IF ( nvl(p_source_line_details.lrt_rule, 'FUL') = 'FUL') AND
1577 ( nvl(p_target_line_details.lrt_rule, 'FUL') = 'FUL') THEN
1578 l_source_start_date := p_source_line_details.end_date + 1;
1579 IF l_source_start_date >= p_target_line_details.start_date THEN
1580 x_return := 'Y';
1581 END IF;
1582 END IF;
1583
1584 IF (x_return = 'Y' AND
1585 FND_PROFILE.VALUE('OKS_CHECK_COV_MATCH') = 'Y') THEN
1586
1587 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1588 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.cov_match', 'profile OKS_CHECK_COV_MATCH=Y, calling OKS_COVERAGES_PUB.check_coverage_match');
1589 END IF;
1590
1591 OKS_COVERAGES_PUB.check_coverage_match(
1592 p_api_version => l_api_version,
1593 p_init_msg_list => l_init_msg_list,
1594 x_return_status => l_return_status,
1595 x_msg_count => l_msg_count,
1596 x_msg_data => l_msg_data,
1597 P_Source_contract_Line_Id => p_source_line_details.line_id,
1598 P_Target_contract_Line_Id => p_target_line_details.line_id,
1599 x_coverage_match => l_coverage_match);
1600
1601 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1602 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.cov_match', 'after call to OKS_COVERAGES_PUB.check_coverage_match, l_return_status='||l_return_status||' ,l_coverage_match='||l_coverage_match);
1603 END IF;
1604
1605 IF l_coverage_match <> 'Y' THEN
1606 x_return := 'N' ;
1607 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1608 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.cov_match','Coverage Mismatch occured');
1609 END IF;
1610 END IF;
1611
1612 END IF;
1613
1614 END IF;
1615
1616 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1617 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_return = ' || x_return);
1618 END IF;
1619
1620 RETURN(x_return);
1621 END MERGE_ELIGIBLE_YN;
1622
1623 ----------------------------------------------------------------------------------
1624 --- THIS PROCEDURE has 2 inputs. The output is used to decide whether to merge or
1625 --- to create a new TOP line
1626 ----------------------------------------------------------------------------------
1627 PROCEDURE MERGE(p_source_line_id IN NUMBER,
1628 p_target_contract_id IN NUMBER,
1629 x_target_line_id OUT NOCOPY NUMBER) IS
1630
1631 l_api_name CONSTANT VARCHAR2(30) := 'MERGE';
1632 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1633
1634 CURSOR target_line_id IS
1635 SELECT id
1636 FROM okc_k_lines_b
1637 WHERE dnz_chr_id = p_target_contract_id
1638 AND cle_id IS NULL
1639 AND lse_id IN (1, 12, 14, 19);
1640
1641 p_target_line_details OKS_RENCON_PVT.merge_rec_type;
1642 p_source_line_details OKS_RENCON_PVT.merge_rec_type;
1643
1644 BEGIN
1645
1646 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1647 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin', 'p_source_line_id=' || p_source_line_id ||' .p_target_contract_id=' || p_target_contract_id);
1648 END IF;
1649
1650 x_target_line_id := NULL;
1651
1652 get_line_details(p_line_id => p_source_line_id,
1653 x_line_details => p_source_line_details);
1654
1655 FOR cur_target_line_id IN target_line_id LOOP
1656
1657 get_line_details(p_line_id => cur_target_line_id.id,
1658 x_line_details => p_target_line_details);
1659
1660 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
1661 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.check_merge', 'Calling merge_eligible_yn with details of source top line id='||p_source_line_id||' and target top line id='||cur_target_line_id.id);
1662 END IF;
1663
1664 IF merge_eligible_yn(p_source_line_details, p_target_line_details) = 'Y' THEN
1665 x_target_line_id := cur_target_line_id.id;
1666 EXIT;
1667 END IF;
1668
1669 END LOOP;
1670
1671 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1672 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_target_line_id='|| x_target_line_id );
1673 END IF;
1674 END MERGE;
1675
1676
1677 FUNCTION GET_CURRENCY(p_chr_id IN NUMBER) RETURN VARCHAR2 IS
1678 CURSOR source_currency IS
1679 SELECT currency_code
1680 FROM okc_k_headers_all_b
1681 WHERE id = p_chr_id;
1682 x_return VARCHAR2(15);
1683 BEGIN
1684 FOR source_currency_rec IN source_currency
1685 LOOP
1686 x_return := source_currency_rec.currency_code;
1687 END LOOP;
1688 RETURN(x_return);
1689 END GET_CURRENCY;
1690
1691 --------------------------------------------------------------------------
1692 -- A source top line can only be copied to a target contract if it has
1693 -- at least one unprocessed, selected sub line.
1694 --
1695 -- This procedure goes theough the operation sub lines to find at least one
1696 -- unprocessed, selected sub line.
1697 --------------------------------------------------------------------------
1698 FUNCTION can_copy_topline(p_ole_id IN NUMBER) RETURN VARCHAR2 IS
1699
1700 l_can_copy VARCHAR2(1) := 'N';
1701 l_api_name CONSTANT VARCHAR2(30) := 'CAN_COPY_TOPLINE';
1702 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1703
1704
1705 CURSOR get_oper_sub_lines(p_id IN NUMBER) IS
1706 SELECT object_cle_id FROM okc_operation_lines ol, okc_k_lines_b cle
1707 WHERE ol.parent_ole_id = p_id
1708 and ol.process_flag IN ('A','E')
1709 and ol.select_yn = 'Y'
1710 and cle.id = ol.object_cle_id
1711 and cle.date_terminated is null
1712 and NVL(cle.line_renewal_type_code,'FUL') not in ('DNR', 'KEP'); -- bug 5078797
1713 /*
1714 SELECT * FROM okc_operation_lines
1715 WHERE parent_ole_id = p_id
1716 AND process_flag IN ('A', 'E')
1717 AND select_yn = 'Y';
1718 */
1719
1720 BEGIN
1721 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1722 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin', 'p_ole_id='||p_ole_id);
1723 END IF;
1724
1725 FOR cur_get_oper_sub_lines IN get_oper_sub_lines(p_ole_id)
1726 LOOP
1727 IF find_ol_status(cur_get_oper_sub_lines.object_cle_id) <> 'P' THEN
1728 l_can_copy := 'Y';
1729 END IF;
1730 END LOOP;
1731
1732 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1733 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end', 'l_can_copy='||l_can_copy);
1734 END IF;
1735
1736 RETURN l_can_copy;
1737 END can_copy_topline;
1738
1739 -----------------------------------------------------------------------------------------
1740 --CALL THIS FUNCTION IF U WANT TO SUBMIT CONC REQ FROM THE FORM
1741 -----------------------------------------------------------------------------------------
1742
1743 FUNCTION SUBMIT_FORM_CONC(p_oie_id IN NUMBER) RETURN NUMBER IS
1744
1745 --added for MOAC changes
1746 CURSOR c_org_id(cp_oie_id IN NUMBER) IS
1747 SELECT org_id FROM okc_k_headers_all_b WHERE
1748 id = (SELECT target_chr_id FROM okc_operation_instances WHERE id = cp_oie_id);
1749
1750 req_id NUMBER;
1751 l_mode BOOLEAN;
1752 l_org_id NUMBER;
1753
1754 BEGIN
1755
1756 OPEN c_org_id(p_oie_id);
1757 FETCH c_org_id INTO l_org_id;
1758 CLOSE c_org_id;
1759
1760 --CP OKSRENCO is marked as single, therefore need to set the org_id
1761 FND_REQUEST.set_org_id(l_org_id);
1762
1763 l_mode := FND_REQUEST.SET_MODE(TRUE);
1764 req_id := FND_REQUEST.submit_request('OKS', 'OKSRENCO', NULL, SYSDATE, FALSE,
1765 p_oie_id);
1766 RETURN(req_id);
1767
1768 END SUBMIT_FORM_CONC;
1769
1770 -----------------------------------------------------------------------------------------
1771 --SUBMIT CONC PROGRAM
1772 -----------------------------------------------------------------------------------------
1773 PROCEDURE SUBMIT_CONC(ERRBUF OUT NOCOPY VARCHAR2,
1774 RETCODE OUT NOCOPY NUMBER,
1775 p_oie_id IN NUMBER) IS
1776 l_errbuf VARCHAR2(200);
1777 l_retcode NUMBER;
1778 l_api_version CONSTANT NUMBER := 1.0;
1779 l_init_msg_list VARCHAR2(2000) := FND_API.G_TRUE;
1780 l_return_status VARCHAR2(1);
1781 l_msg_count NUMBER;
1782 l_msg_data VARCHAR2(2000);
1783
1784 l_msg_index_out NUMBER;
1785 l_msg_index NUMBER;
1786
1787 l_cle_id NUMBER;
1788
1789 BEGIN
1790
1791 OKS_RENCON_PVT.SUBMIT(
1792 errbuf => l_errbuf,
1793 retcode => l_retcode,
1794 p_api_version => l_api_version,
1795 p_init_msg_list => l_init_msg_list,
1796 x_return_status => l_return_status,
1797 x_msg_count => l_msg_count,
1798 x_msg_data => l_msg_data,
1799 p_conc_program => 'Y',
1800 p_oie_id => p_oie_id);
1801
1802 END SUBMIT_CONC;
1803
1804 -----------------------------------------------------------------------------------------
1805 --SUBMIT
1806 -----------------------------------------------------------------------------------------
1807 PROCEDURE SUBMIT(ERRBUF OUT NOCOPY VARCHAR2,
1808 RETCODE OUT NOCOPY NUMBER,
1809 p_api_version IN NUMBER,
1810 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1811 x_return_status OUT NOCOPY VARCHAR2,
1812 x_msg_count OUT NOCOPY NUMBER,
1813 x_msg_data OUT NOCOPY VARCHAR2,
1814 p_conc_program IN VARCHAR2,
1815 p_oie_id IN NUMBER) IS
1816
1817 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1818 TYPE chr_tbl_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
1819
1820 l_api_version CONSTANT NUMBER := 1.0;
1821 l_api_name CONSTANT VARCHAR2(30) := 'SUBMIT';
1822 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1823
1824
1825 /*
1826 CURSOR get_oper_top_lines IS
1827 SELECT b. * FROM okc_operation_lines a, okc_operation_lines b
1828 WHERE a.oie_id = p_oie_id
1829 AND b.oie_id = p_oie_id
1830 AND a.id = b.parent_ole_id
1831 AND a.parent_ole_id IS NULL
1832 AND b.process_flag IN ('A', 'E')
1833 AND b.select_yn = 'Y';
1834 */
1835 CURSOR get_oper_top_lines IS
1836 SELECT b.*,
1837 chr.inv_organization_id,
1838 chr.authoring_org_id,
1839 chr.currency_code
1840 FROM okc_operation_lines b,
1841 okc_k_lines_b cle,
1842 okc_k_headers_all_b chr,
1843 okc_statuses_b st
1844 WHERE b.oie_id = p_oie_id
1845 and cle.id = b.object_cle_id
1846 and cle.cle_id is null
1847 and b.process_flag IN ('A','E')
1848 and b.select_yn = 'Y'
1849 and cle.date_terminated is NULL
1850 and NVL(cle.line_renewal_type_code,'FUL') not in ('DNR', 'KEP') -- bug 5078797
1851 and chr.id = cle.dnz_chr_id
1852 and chr.sts_code = st.code
1853 and st.ste_code in ('ACTIVE','EXPIRED','SIGNED')
1854 and nvl(renewal_type_code, G_GCD_RENEWAL_TYPE) <> 'DNR';
1855
1856 /*
1857 CURSOR get_oper_sub_lines(p_id IN NUMBER) IS
1858 SELECT * FROM okc_operation_lines
1859 WHERE parent_ole_id = p_id
1860 AND process_flag IN ('A', 'E')
1861 AND select_yn = 'Y';
1862 */
1863 CURSOR get_oper_sub_lines(p_id IN NUMBER) IS
1864 SELECT ol.*, lse_id, start_date, end_date FROM okc_operation_lines ol,
1865 okc_k_lines_b cle
1866 WHERE parent_ole_id = p_id
1867 and process_flag IN ('A','E')
1868 and select_yn = 'Y'
1869 and cle.id = ol.object_cle_id
1870 and cle.date_terminated is NULL
1871 and NVL(cle.line_renewal_type_code,'FUL') not in ('DNR', 'KEP') -- bug 5078797
1872 and NOT EXISTS
1873 ( SELECT 'x'
1874 FROM okc_operation_lines
1875 WHERE object_cle_id = ol.object_cle_id
1876 AND subject_chr_id <> ol.subject_chr_id
1877 AND process_flag = 'P'
1878 AND active_yn = 'Y'
1879 ); -- bug 5085556
1880
1881
1882 CURSOR set_org(p_header_id IN NUMBER) IS
1883 SELECT inv_organization_id, org_id, currency_code --mmadhavi changed to Org_id for MOAC
1884 FROM okc_k_headers_all_b
1885 WHERE id = p_header_id;
1886
1887 -- MSENGUPT 11/07 the following cursor is not needed as it is taken care of in get subline csr as we have to join to okc_k_lines
1888 -- anyway
1889
1890 CURSOR get_subline_details(p_subline_id in NUMBER) IS
1891 select lse_id,start_date,end_date
1892 from okc_k_lines_b
1893 where id =p_subline_id;
1894
1895 -- The following cursor was added by MSENGUPT 11/07
1896 -- Need to check that target is valid as by the time the CP picks this up the target contract may not be in valid status
1897
1898 CURSOR valid_target (p_oie_id IN NUMBER) IS
1899 SELECT
1900 h.start_date,
1901 h.authoring_org_id,
1902 h.inv_organization_id,
1903 pr.object1_id1,
1904 h.currency_code
1905 FROM okc_K_Headers_all_b h,
1906 okc_k_party_roles_b pr,
1907 okc_statuses_b st
1908 WHERE
1909 h.id=pr.dnz_chr_id and pr.cle_id is null
1910 and pr.rle_code = 'CUSTOMER'
1911 and h.scs_code IN ('SERVICE','WARRANTY')
1912 and h.template_yn = 'N'
1913 and h.sts_code = st.code
1914 and st.ste_code = 'ENTERED'
1915 and h.id in
1916 (select target_chr_id from okc_operation_instances where id = p_oie_id);
1917
1918
1919 l_target_header_id NUMBER;
1920 l_source_header_id NUMBER;
1921 lt_source_header_id NUMBER := 0;
1922 l_target_line_id NUMBER;
1923 l_cle_id NUMBER := NULL;
1924 s_cle_id NUMBER := NULL;
1925 i NUMBER := 0;
1926 l_conc_status BOOLEAN := TRUE;
1927
1928 l_init_msg_list VARCHAR2(2000) := OKC_API.G_FALSE;
1929
1930 l_return_status VARCHAR2(1);
1931 l_msg_count NUMBER;
1932 l_msg_data VARCHAR2(4000);
1933
1934 l_target_curr VARCHAR2(15);
1935 l_source_curr VARCHAR2(15);
1936
1937 l_start_date DATE;
1938 l_authoring_org_id NUMBER;
1939 l_inv_organization_id NUMBER;
1940 l_party_id NUMBER;
1941 l_currency_code VARCHAR2(30);
1942 l_msg_index_out NUMBER;
1943
1944 l_subject_sub_line_tbl OKS_REPRICE_PVT.sub_line_tbl_type;
1945 l_need_conversion VARCHAR2(1); -- Currency conversion required? (Y/N)
1946 l_can_copy VARCHAR2(1) := 'Y';
1947
1948 l_opl_id_tbl num_tbl_type;
1949 l_opl_sub_cle_id_tbl num_tbl_type;
1950 l_opl_status_tbl chr_tbl_type;
1951 l_src_sub_line_id_tbl num_tbl_type;
1952 l_date_renewed DATE;
1953
1954 l_update_date DATE;
1955 l_user_id NUMBER;
1956 l_login_id NUMBER;
1957 l_request_id NUMBER;
1958 l_prog_appl_id NUMBER;
1959 l_prog_id NUMBER;
1960 l_warnings BOOLEAN := FALSE;
1961 l_errors BOOLEAN := FALSE;
1962 l_dummy BOOLEAN;
1963
1964 l_rnrl_rec_in OKS_RENEW_UTIL_PVT.RNRL_REC_TYPE;
1965 l_rnrl_rec_out OKS_RENEW_UTIL_PVT.RNRL_REC_TYPE;
1966
1967
1968 BEGIN
1969
1970 log_messages('Renewal Consolidation conurrent program - BEGIN, p_oie_id='|| p_oie_id);
1971 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1972 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin','l_conc_program = ' || l_conc_program ||' p_oie_id='|| p_oie_id);
1973 END IF;
1974
1975 --standard api initilization and checks
1976 SAVEPOINT submit_PVT;
1977 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
1978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1979 END IF;
1980 IF FND_API.to_boolean(p_init_msg_list ) THEN
1981 FND_MSG_PUB.initialize;
1982 END IF;
1983
1984 -- Added by MSENGUPT 11/07
1985 -- Need to check that target is valid as by the time the CP picks this up the target contract may not be in valid status
1986 -- For renewal consolidation, there can be only one target_contract_id for the oie_id
1987 retcode := 0;
1988 OPEN Valid_target(p_oie_id) ;
1989 FETCH valid_target INTO
1990 l_start_date,
1991 l_authoring_org_id,
1992 l_inv_organization_id,
1993 l_party_id,
1994 l_currency_code;
1995 IF valid_target%NOTFOUND THEN
1996 LOG_MESSAGES('The target contract is not found - maybe invalid status');
1997 -- this is a log message only for debugging.
1998 retcode := 2;
1999 END IF;
2000 CLOSE valid_target;
2001 IF retcode = 2 then
2002 return;
2003 END IF;
2004
2005 l_start_date := l_start_date - 1;
2006
2007 -- Since the renewal consolidation process ensures common customer/party/operating unit, we will cache the GCDrenewal type
2008 -- based on the target contracts info.
2009 -- This value will be used in the cursors NVL of header renewal type
2010
2011 LOG_MESSAGES('Calling OKS_RENEW_UTIL_PUB.GET_RENEW_RULES');
2012 LOG_MESSAGES('****** Parameters *********');
2013 LOG_MESSAGES('l_party_id : '||l_party_id);
2014 LOG_MESSAGES('l_authoring_org_id : '||l_authoring_org_id);
2015 LOG_MESSAGES('l_start_date : '||to_char(l_start_date,'DD-MON-YYYY'));
2016 LOG_MESSAGES(' ');
2017 OKS_RENEW_UTIL_PUB.GET_RENEW_RULES (
2018 p_api_version => 1.0,
2019 p_init_msg_list => OKC_API.G_FALSE,
2020 x_return_status => l_return_status,
2021 x_msg_count => l_msg_count,
2022 x_msg_data => l_msg_data,
2023 P_Chr_Id => NULL,
2024 P_PARTY_ID => l_party_id,
2025 P_ORG_ID => l_authoring_org_id,
2026 P_Date => l_start_date,
2027 P_RNRL_Rec => l_rnrl_rec_in,
2028 X_RNRL_Rec => l_rnrl_rec_out
2029 );
2030 IF l_return_status <> 'S' THEN
2031 LOG_MESSAGES('Error from getting gcd renewal type: '); -- this is a log message only for debugging.
2032 errbuf := substr(x_msg_data,1,200);
2033 LOG_MESSAGES(errbuf);
2034 retcode := 2;
2035 return;
2036 ELSE
2037 G_GCD_RENEWAL_TYPE := nvl(l_rnrl_rec_out.renewal_type, 'X');
2038 END IF;
2039 i:=0;
2040 LOG_MESSAGES('After Calling OKS_RENEW_UTIL_PUB.GET_RENEW_RULES');
2041 log_messages('Renewal Type derived from GCD is :'||G_GCD_RENEWAL_TYPE);
2042 LOG_MESSAGES(' ');
2043
2044 -- End of Added by MSENGUPT 11/07
2045
2046 x_return_status := FND_API.G_RET_STS_SUCCESS;
2047 l_return_status := FND_API.G_RET_STS_SUCCESS;
2048 retcode := 0; --0 for success, 1 for warning, 2 for error
2049 errbuf := NULL;
2050 l_conc_program := p_conc_program;
2051
2052 l_subject_sub_line_tbl.delete;
2053
2054 FOR cur_get_oper_top_lines IN get_oper_top_lines LOOP
2055
2056
2057 l_target_header_id := cur_get_oper_top_lines.subject_chr_id;
2058 l_target_curr := get_currency(l_target_header_id);
2059 l_source_curr := get_currency(cur_get_oper_top_lines.object_chr_id);
2060
2061 log_messages('Target Header Id: '||l_target_header_id);
2062 log_messages('Source Currency: '||l_source_curr);
2063 log_messages('Target Currency: '||l_target_curr);
2064 LOG_MESSAGES(' ');
2065
2066 IF l_target_curr <> l_source_curr THEN
2067 l_need_conversion := 'Y';
2068 ELSE
2069 l_need_conversion := 'N';
2070 END IF;
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 || '.check_curr','Source Currency='|| l_source_curr ||' ,Source hdr id='|| cur_get_oper_top_lines.object_chr_id
2074 ||' ,Target Currency='|| l_target_curr || ' ,Target hdr id='|| l_target_header_id ||' ,l_need_conversion='|| l_need_conversion);
2075 END IF;
2076
2077 FOR cur_set_org IN set_org(l_target_header_id) LOOP
2078
2079 OKC_CONTEXT.set_okc_org_context(cur_set_org.org_id, cur_set_org.inv_organization_id); --mmadhavi using org_id for MOAC
2080
2081 Log_Messages('Authoring Org: '||cur_set_org.org_id);
2082 Log_Messages('Inventory Org: '||cur_set_org.inv_organization_id);
2083 LOG_MESSAGES(' ');
2084
2085
2086 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2087 FND_LOG.string(FND_LOG.level_statement, l_mod_name ||'.set_context','Authoring Org='|| cur_set_org.org_id ||' Inventory Org='|| cur_set_org.inv_organization_id);
2088 END IF;
2089
2090 END LOOP;
2091
2092 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2093 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.check_topline_merge','Calling merge, p_source_line_id='|| cur_get_oper_top_lines.object_cle_id
2094 ||' ,p_target_contract_id='|| cur_get_oper_top_lines.subject_chr_id);
2095 END IF;
2096
2097 l_target_line_id := NULL;
2098 MERGE(p_source_line_id => cur_get_oper_top_lines.object_cle_id,
2099 p_target_contract_id => cur_get_oper_top_lines.subject_chr_id,
2100 x_target_line_id => l_target_line_id);
2101
2102 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2103 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.check_topline_merge','After call to merge, l_target_line_id='|| l_target_line_id);
2104 END IF;
2105
2106
2107 IF l_target_line_id IS NULL THEN
2108 -- Before copying top line to target, make sure top line has at least one
2109 -- unprocessed sub line.
2110 LOG_MESSAGES('Merge line NOT found');
2111 l_can_copy := can_copy_topline(cur_get_oper_top_lines.id);
2112 ELSE
2113 LOG_MESSAGES('Merge line found:'|| l_target_line_id);
2114 l_can_copy := 'N';
2115 END IF;
2116
2117 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2118 FND_LOG.string(FND_LOG.level_statement, l_mod_name, 'l_can_copy=' || l_can_copy);
2119 END IF;
2120
2121 log_messages('Source Topline id='|| cur_get_oper_top_lines.object_cle_id );
2122 log_messages('Target Header id='|| cur_get_oper_top_lines.subject_chr_id );
2123 log_messages('Target Topline id='|| l_target_line_id );
2124 log_messages('l_can_copy='|| l_can_copy);
2125 LOG_MESSAGES(' ');
2126
2127 IF l_can_copy = 'Y' THEN
2128 ------------------------------------------------------------
2129 ---COPY the TOP LINE FROM SOURCE TO TARGET
2130 ------------------------------------------------------------
2131
2132 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2133 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.top_line_copy','Calling OKS_RENCPY_PVT.copy_contract_line p_from_cle_id='||cur_get_oper_top_lines.object_cle_id||
2134 ' ,p_from_chr_id='||cur_get_oper_top_lines.object_chr_id||' ,p_to_cle_id=NULL ,p_to_chr_id='||cur_get_oper_top_lines.subject_chr_id ||' ,p_need_conversion='|| l_need_conversion);
2135 END IF;
2136
2137 LOG_MESSAGES('Calling OKS_RENCPY_PVT.COPY_CONTRACT_LINES 1: p_need_conversion = '|| l_need_conversion);
2138 LOG_MESSAGES(' ');
2139
2140 OKS_RENCPY_PVT.copy_contract_line(
2141 p_api_version => 1.0,
2142 p_init_msg_list => FND_API.G_FALSE,
2143 x_return_status => l_return_status,
2144 x_msg_count => l_msg_count,
2145 x_msg_data => l_msg_data,
2146 p_from_cle_id => cur_get_oper_top_lines.object_cle_id,
2147 p_from_chr_id => cur_get_oper_top_lines.object_chr_id,
2148 p_to_cle_id => NULL,
2149 p_to_chr_id => cur_get_oper_top_lines.subject_chr_id,
2150 p_lse_id => NULL,
2151 p_to_template_yn => 'N',
2152 p_copy_reference => 'COPY',
2153 p_copy_line_party_yn => 'Y',
2154 p_renew_ref_yn => 'N',
2155 p_need_conversion => l_need_conversion, -- currency code conversion needed for top lines
2156 x_cle_id => l_cle_id);
2157
2158 LOG_MESSAGES('COPY the TOP LINE FROM SOURCE TO TARGET l_return_status = ' || l_return_status);
2159 LOG_MESSAGES('x_cle_id = ' || l_cle_id);
2160 LOG_MESSAGES(' ');
2161
2162
2163 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2164 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.top_line_copy','After call to OKS_RENCPY_PVT.copy_contract_line l_return_status='|| l_return_status ||' ,x_cle_id='|| l_cle_id);
2165 END IF;
2166
2167 --for U:unexpected error stop, for E:regular error, try next line
2168 --for W:warnings or S:Success continue
2169 IF (l_return_status = FND_API.g_ret_sts_success) THEN
2170 NULL; --continue
2171 ELSIF(l_return_status = OKC_API.g_ret_sts_warning) THEN
2172 l_warnings := TRUE;
2173 ELSIF (l_return_status = FND_API.g_ret_sts_error) THEN
2174 l_errors := TRUE;
2175 ELSE --all others treated as unexpected
2176 x_return_status := l_return_status;
2177 RAISE FND_API.g_exc_unexpected_error;
2178 END IF;
2179
2180 ------------------------------------------------------------------------------
2181 ----DEPENDING on the return status u update the operation lines process flag
2182 ------------------------------------------------------------------------------
2183 l_opl_id_tbl(l_opl_id_tbl.count +1) := cur_get_oper_top_lines.id;
2184
2185 IF (l_return_status = FND_API.g_ret_sts_error) THEN
2186
2187 log_messages('Topline id='|| cur_get_oper_top_lines.object_cle_id ||' did not get copied');
2188
2189 l_opl_sub_cle_id_tbl(l_opl_id_tbl.count) := NULL;
2190 l_opl_status_tbl(l_opl_id_tbl.count) := 'E';
2191 ELSE
2192 --only come here if l_return_status in (S,W)
2193 log_messages('Topline id='|| cur_get_oper_top_lines.object_cle_id ||' copied to id='|| l_cle_id);
2194
2195 l_opl_sub_cle_id_tbl(l_opl_id_tbl.count) := l_cle_id;
2196 l_opl_status_tbl(l_opl_id_tbl.count) := 'P';
2197
2198 ------------------------------------------------------------------------------
2199 ----If Copy is successful Delete the SLH and SLL rules that were copied
2200 ------------------------------------------------------------------------------
2201 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2202 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.top_line_billing','Calling OKS_BILL_UTIL_PUB.delete_slh_rule, p_cle_id='|| l_cle_id);
2203 END IF;
2204
2205 LOG_MESSAGES('Calling OKS_BILL_UTIL_PUB.delete_slh_rule');
2206
2207 OKS_BILL_UTIL_PUB.delete_slh_rule(
2208 p_api_version => 1.0,
2209 p_init_msg_list => FND_API.G_FALSE,
2210 p_cle_id => l_cle_id,
2211 x_return_status => l_return_status,
2212 x_msg_count => l_msg_count,
2213 x_msg_data => l_msg_data);
2214
2215 LOG_MESSAGES('After Calling OKS_BILL_UTIL_PUB.delete_slh_rule l_return_status : '||l_return_status);
2216 LOG_MESSAGES(' ');
2217
2218 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2219 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.top_line_billing','After call to OKS_BILL_UTIL_PUB.delete_slh_rule, l_return_status='|| l_return_status);
2220 END IF;
2221
2222 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2223 RAISE FND_API.g_exc_unexpected_error;
2224 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2225 RAISE FND_API.g_exc_error;
2226 END IF;
2227
2228 s_cle_id := NULL;
2229 l_subject_sub_line_tbl.delete;
2230 i := 0;
2231
2232 ---------------------------------------------------------------------------
2233 ---COPY all sublines in operation lines under target top line created above
2234 ---------------------------------------------------------------------------
2235 FOR cur_get_oper_sub_lines IN get_oper_sub_lines(cur_get_oper_top_lines.id)
2236 LOOP
2237 IF find_ol_status(cur_get_oper_sub_lines.object_cle_id) <> 'P' THEN
2238
2239 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2240 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_copy','Calling OKS_RENCPY_PVT.copy_contract_line p_need_conversion = '|| l_need_conversion ||
2241 ' ,p_from_cle_id='|| cur_get_oper_sub_lines.object_cle_id ||' ,p_from_chr_id='|| cur_get_oper_sub_lines.object_chr_id||' ,p_to_cle_id='|| l_cle_id ||
2242 ' ,p_to_chr_id='|| cur_get_oper_sub_lines.subject_chr_id);
2243 END IF;
2244
2245
2246 LOG_MESSAGES('Calling OKS_RENCPY_PVT.copy_contract_line');
2247 LOG_MESSAGES('p_from_cle_id : '||cur_get_oper_sub_lines.object_cle_id);
2248
2249 OKS_RENCPY_PVT.copy_contract_line(
2250 p_api_version => 1.0,
2251 p_init_msg_list => FND_API.G_FALSE,
2252 x_return_status => l_return_status,
2253 x_msg_count => l_msg_count,
2254 x_msg_data => l_msg_data,
2255 p_from_cle_id => cur_get_oper_sub_lines.object_cle_id,
2256 p_from_chr_id => cur_get_oper_sub_lines.object_chr_id,
2257 p_to_cle_id => l_cle_id,
2258 p_to_chr_id => cur_get_oper_sub_lines.subject_chr_id,
2259 p_lse_id => NULL,
2260 p_to_template_yn => 'N',
2261 p_copy_reference => 'COPY',
2262 p_copy_line_party_yn => 'Y',
2263 p_renew_ref_yn => 'N',
2264 p_need_conversion => l_need_conversion,
2265 x_cle_id => s_cle_id);
2266
2267 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2268 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_copy','After call to OKS_RENCPY_PVT.copy_contract_line, l_return_status=' || l_return_status ||' ,x_cle_id=' || s_cle_id);
2269 END IF;
2270
2271 LOG_MESSAGES('After Calling OKS_RENCPY_PVT.copy_contract_line, l_return_status : '||l_return_status);
2272 LOG_MESSAGES(' ');
2273 IF (l_return_status = FND_API.g_ret_sts_success) THEN
2274 NULL;
2275 ELSIF(l_return_status = OKC_API.g_ret_sts_warning) THEN
2276 l_warnings := TRUE;
2277 ELSIF (l_return_status = FND_API.g_ret_sts_error) THEN
2278 l_errors := TRUE;
2279 ELSE
2280 x_return_status := l_return_status;
2281 RAISE FND_API.g_exc_unexpected_error;
2282 END IF;
2283
2284
2285 l_opl_id_tbl(l_opl_id_tbl.count +1) := cur_get_oper_sub_lines.id;
2286 --------------------------------------------------------------------------
2287 --DEPENDING on the return status u update the operation lines process flag
2288 --------------------------------------------------------------------------
2289 IF (l_return_status = FND_API.g_ret_sts_error) THEN
2290 log_messages(' Subline id='|| cur_get_oper_sub_lines.object_cle_id ||' did not get copied');
2291
2292 l_opl_sub_cle_id_tbl(l_opl_id_tbl.count) := NULL;
2293 l_opl_status_tbl(l_opl_id_tbl.count) := 'E';
2294
2295 ELSE
2296 --only come here if l_return_status in (S,W)
2297 log_messages(' Subline id='|| cur_get_oper_sub_lines.object_cle_id ||' copied to id='|| s_cle_id);
2298
2299 l_opl_sub_cle_id_tbl(l_opl_id_tbl.count) := s_cle_id;
2300 l_opl_status_tbl(l_opl_id_tbl.count) := 'P';
2301
2302 l_subject_sub_line_tbl(i) := s_cle_id;
2303
2304 --------------------------------------------------------------------------
2305 ----If Copy is successful Delete the SLH and SLL rules that were copied
2306 --------------------------------------------------------------------------
2307 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2308 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_billing','Calling OKS_BILL_UTIL_PUB.delete_slh_rule, p_cle_id='|| s_cle_id);
2309 END IF;
2310
2311
2312 LOG_MESSAGES('Calling OKS_BILL_UTIL_PUB.delete_slh_rule');
2313
2314 OKS_BILL_UTIL_PUB.delete_slh_rule(
2315 p_api_version => 1.0,
2316 p_init_msg_list => FND_API.G_FALSE,
2317 p_cle_id => s_cle_id,
2318 x_return_status => l_return_status,
2319 x_msg_count => l_msg_count,
2320 x_msg_data => l_msg_data);
2321
2322 LOG_MESSAGES('After Calling OKS_BILL_UTIL_PUB.delete_slh_rule, l_return_status: '||l_return_status);
2323 LOG_MESSAGES(' ');
2324
2325 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2326 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_billing','After call to OKS_BILL_UTIL_PUB.delete_slh_rule, l_return_status='|| l_return_status);
2327 END IF;
2328
2329 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2330 RAISE FND_API.g_exc_unexpected_error;
2331 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2332 RAISE FND_API.g_exc_error;
2333 END IF;
2334
2335 --------------------------------------------------------------------------
2336 ---- Update the source subline with the renewal date
2337 --------------------------------------------------------------------------
2338 l_src_sub_line_id_tbl(l_src_sub_line_id_tbl.count +1) := cur_get_oper_sub_lines.object_cle_id;
2339
2340 i := i + 1;
2341 END IF;
2342 END IF;
2343 END LOOP; --subline loop
2344
2345 -------------------------------------------------------------------------------
2346 ---CALL OKS_REPRICE_PVT.Call_Pricing_Api To adjust the price for those sublines
2347 -------------------------------------------------------------------------------
2348 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2349 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.top_line_repricing','Calling OKS_REPRICE_PVT.call_pricing_api p_subject_chr_id='||l_target_header_id||
2350 ' ,p_subject_top_line_id='||l_cle_id||' ,p_subject_sub_line_tbl.count='||l_subject_sub_line_tbl.count);
2351 END IF;
2352
2353 LOG_MESSAGES('Calling OKS_REPRICE_PVT.call_pricing_api');
2354 LOG_MESSAGES('p_subject_chr_id='||l_target_header_id);
2355 LOG_MESSAGES('p_subject_top_line_id='||l_cle_id);
2356 LOG_MESSAGES('p_subject_sub_line_tbl.count='||l_subject_sub_line_tbl.count);
2357
2358 /*
2359 * Bug 6114024 Call moved after update to operation lines
2360 OKS_REPRICE_PVT.call_pricing_api(
2361 p_api_version => 1.0,
2362 p_init_msg_list => FND_API.G_FALSE,
2363 x_return_status => l_return_status,
2364 x_msg_count => l_msg_count,
2365 x_msg_data => l_msg_data,
2366 p_subject_chr_id => l_target_header_id,
2367 p_subject_top_line_id => l_cle_id,
2368 p_subject_sub_line_tbl => l_subject_sub_line_tbl );
2369 */
2370
2371 LOG_MESSAGES('After Calling OKS_REPRICE_PVT.call_pricing_api l_return_status: '||l_return_status);
2372 LOG_MESSAGES(' ');
2373
2374 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2375 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.top_line_repricing','After call to OKS_REPRICE_PVT.call_pricing_api l_return_status='|| l_return_status);
2376 END IF;
2377
2378 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2379 RAISE FND_API.g_exc_unexpected_error;
2380 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2381 RAISE FND_API.g_exc_error;
2382 END IF;
2383
2384 log_messages('Top line id='||l_cle_id||' repriced');
2385 LOG_MESSAGES(' ');
2386
2387 END IF; --of if topline successfully copied
2388
2389 ELSE
2390
2391 -----------------------------------------------------------------------------
2392 ---COPY all sublines in operation lines under merge top line l_target_line_id
2393 -----------------------------------------------------------------------------
2394
2395 s_cle_id := NULL;
2396 l_subject_sub_line_tbl.delete;
2397 i := 0;
2398
2399 FOR cur_get_oper_sub_lines IN get_oper_sub_lines(cur_get_oper_top_lines.id) LOOP
2400 IF find_ol_status(cur_get_oper_sub_lines.object_cle_id) <> 'P' THEN
2401
2402
2403 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2404 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_copy','Calling OKS_RENCPY_PVT.copy_contract_line p_need_conversion = '|| l_need_conversion ||
2405 ' ,p_from_cle_id='|| cur_get_oper_sub_lines.object_cle_id ||' ,p_to_cle_id='|| l_target_line_id ||' ,p_to_chr_id='|| cur_get_oper_sub_lines.subject_chr_id);
2406 END IF;
2407
2408 LOG_MESSAGES('---COPY all sublines in operation lines under merge top line l_target_line_id----');
2409 LOG_MESSAGES('Calling OKS_RENCPY_PVT.copy_contract_line p_from_cle_id : '||cur_get_oper_sub_lines.object_cle_id);
2410 LOG_MESSAGES('p_need_conversion = '|| l_need_conversion);
2411 LOG_MESSAGES('p_from_cle_id='|| cur_get_oper_sub_lines.object_cle_id);
2412 LOG_MESSAGES('p_to_cle_id='|| l_target_line_id);
2413 LOG_MESSAGES('p_to_chr_id='|| cur_get_oper_sub_lines.subject_chr_id);
2414 LOG_MESSAGES(' ');
2415
2416 OKS_RENCPY_PVT.copy_contract_line(
2417 p_api_version => 1.0,
2418 p_init_msg_list => FND_API.G_FALSE,
2419 x_return_status => l_return_status,
2420 x_msg_count => l_msg_count,
2421 x_msg_data => l_msg_data,
2422 p_from_cle_id => cur_get_oper_sub_lines.object_cle_id,
2423 p_from_chr_id => cur_get_oper_sub_lines.object_chr_id,
2424 p_to_cle_id => l_target_line_id,
2425 p_to_chr_id => cur_get_oper_sub_lines.subject_chr_id,
2426 p_lse_id => NULL,
2427 p_to_template_yn => 'N',
2428 p_copy_reference => 'COPY',
2429 p_copy_line_party_yn => 'Y',
2430 p_renew_ref_yn => 'N',
2431 p_need_conversion => l_need_conversion,
2432 x_cle_id => s_cle_id);
2433
2434 LOG_MESSAGES('After Calling OKS_RENCPY_PVT.copy_contract_line l_return_status : '||l_return_status);
2435 LOG_MESSAGES(' ');
2436
2437 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2438 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_copy','After call to OKS_RENCPY_PVT.copy_contract_line, l_return_status=' || l_return_status ||' ,x_cle_id=' || s_cle_id);
2439 END IF;
2440
2441 IF (l_return_status = FND_API.g_ret_sts_success) THEN
2442 NULL;
2443 ELSIF(l_return_status = OKC_API.g_ret_sts_warning) THEN
2444 l_warnings := TRUE;
2445 ELSIF (l_return_status = FND_API.g_ret_sts_error) THEN
2446 l_errors := TRUE;
2447 ELSE
2448 x_return_status := l_return_status;
2449 RAISE FND_API.g_exc_unexpected_error;
2450 END IF;
2451
2452
2453 l_opl_id_tbl(l_opl_id_tbl.count +1) := cur_get_oper_sub_lines.id;
2454 ------------------------------------------------------------------------------
2455 ----DEPENDING on the return status u update the operation lines process flag
2456 ------------------------------------------------------------------------------
2457 IF (l_return_status = FND_API.g_ret_sts_error) THEN
2458 log_messages(' Subline id='|| cur_get_oper_sub_lines.object_cle_id ||' did not get copied');
2459
2460 l_opl_sub_cle_id_tbl(l_opl_id_tbl.count) := NULL;
2461 l_opl_status_tbl(l_opl_id_tbl.count) := 'E';
2462 ELSE
2463 --only come here if l_return_status in (S,W)
2464 log_messages(' Subline id='|| cur_get_oper_sub_lines.object_cle_id ||' copied to id='|| s_cle_id);
2465
2466 l_opl_sub_cle_id_tbl(l_opl_id_tbl.count) := s_cle_id;
2467 l_opl_status_tbl(l_opl_id_tbl.count) := 'P';
2468
2469 l_subject_sub_line_tbl(i) := s_cle_id;
2470
2471 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2472 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_billing','Calling OKS_BILL_UTIL_PUB.delete_slh_rule, p_cle_id='|| s_cle_id);
2473 END IF;
2474
2475 ------------------------------------------------------------------------------
2476 ----If Copy is successful Delete the SLH and SLL rules that were copied
2477 ------------------------------------------------------------------------------
2478 LOG_MESSAGES('Calling OKS_BILL_UTIL_PUB.delete_slh_rule');
2479
2480 OKS_BILL_UTIL_PUB.delete_slh_rule(
2481 p_api_version => 1.0,
2482 p_init_msg_list => FND_API.G_FALSE,
2483 p_cle_id => s_cle_id,
2484 x_return_status => l_return_status,
2485 x_msg_count => l_msg_count,
2486 x_msg_data => l_msg_data);
2487
2488 LOG_MESSAGES('After Calling OKS_BILL_UTIL_PUB.delete_slh_rule,l_return_status: '||l_return_status);
2489 LOG_MESSAGES(' ');
2490
2491 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2492 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_billing','After call to OKS_BILL_UTIL_PUB.delete_slh_rule, l_return_status='|| l_return_status);
2493 END IF;
2494
2495 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2496 RAISE FND_API.g_exc_unexpected_error;
2497 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2498 RAISE FND_API.g_exc_error;
2499 END IF;
2500
2501 l_src_sub_line_id_tbl(l_src_sub_line_id_tbl.count +1) := cur_get_oper_sub_lines.object_cle_id;
2502
2503 i := i + 1;
2504
2505 END IF;
2506 END IF;
2507 END LOOP; --subline loop
2508
2509 -------------------------------------------------------------------------------
2510 ---CALL OKS_REPRICE_PVT.Call_Pricing_Api To adjust the price for those sublines
2511 -------------------------------------------------------------------------------
2512
2513 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2514 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.top_line_repricing','Calling OKS_REPRICE_PVT.call_pricing_api p_subject_chr_id='||l_target_header_id||
2515 ' ,p_subject_top_line_id='||l_target_line_id||' ,p_subject_sub_line_tbl.count='||l_subject_sub_line_tbl.count);
2516 END IF;
2517
2518 LOG_MESSAGES('Calling OKS_REPRICE_PVT.call_pricing_api');
2519 LOG_MESSAGES('p_subject_chr_id='||l_target_header_id);
2520 LOG_MESSAGES('p_subject_top_line_id='||l_target_line_id);
2521 LOG_MESSAGES('p_subject_sub_line_tbl.count='||l_subject_sub_line_tbl.COUNT);
2522
2523 /*
2524 * Bug 6114024 Call moved after update to operation lines
2525 OKS_REPRICE_PVT.call_pricing_api(
2526 p_api_version => 1.0,
2527 p_init_msg_list => FND_API.G_FALSE,
2528 x_return_status => l_return_status,
2529 x_msg_count => l_msg_count,
2530 x_msg_data => l_msg_data,
2531 p_subject_chr_id => l_target_header_id,
2532 p_subject_top_line_id => l_target_line_id,
2533 p_subject_sub_line_tbl => l_subject_sub_line_tbl);
2534 */
2535 LOG_MESSAGES('After Calling OKS_REPRICE_PVT.call_pricing_api l_return_status: '||l_return_status);
2536 LOG_MESSAGES(' ');
2537
2538 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2539 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.top_line_repricing','After call to OKS_REPRICE_PVT.call_pricing_api l_return_status='|| l_return_status);
2540 END IF;
2541
2542 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2543 RAISE FND_API.g_exc_unexpected_error;
2544 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2545 RAISE FND_API.g_exc_error;
2546 END IF;
2547
2548 log_messages('Top line id='||l_target_line_id||' repriced');
2549
2550 END IF;
2551 END LOOP;
2552
2553 l_update_date := sysdate;
2554 l_user_id := FND_GLOBAL.USER_ID;
2555 l_login_id := FND_GLOBAL.LOGIN_ID;
2556 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2557 l_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
2558 l_prog_id := FND_GLOBAL.CONC_PROGRAM_ID;
2559
2560 log_messages('***** Number of Source Top Lines/Sublines processed : ***** '||l_opl_id_tbl.COUNT);
2561 LOG_MESSAGES(' ');
2562
2563 --update process status and subject cle id in okc_operation_lines for all
2564 --source toplines and sublines that where successfully copied to the target
2565 IF (l_opl_id_tbl.COUNT > 0 ) THEN
2566
2567 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2568 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_opn_lines','updating okc_operation_lines l_opl_id_tbl.COUNT='||l_opl_id_tbl.COUNT);
2569 END IF;
2570
2571 FORALL i IN l_opl_id_tbl.FIRST..l_opl_id_tbl.LAST
2572 UPDATE okc_operation_lines SET
2573 subject_cle_id = l_opl_sub_cle_id_tbl(i),
2574 process_flag = l_opl_status_tbl(i),
2575 object_version_number = object_version_number + 1,
2576 last_updated_by = l_user_id,
2577 last_update_date = l_update_date,
2578 last_update_login = l_login_id,
2579 request_id = l_request_id,
2580 program_application_id = l_prog_appl_id,
2581 program_id = l_prog_id,
2582 program_update_date = l_update_date
2583 WHERE id = l_opl_id_tbl(i);
2584 END IF;
2585
2586 log_messages('***** Number of Source Sublines processed : ***** '||l_src_sub_line_id_tbl.COUNT);
2587 LOG_MESSAGES(' ');
2588
2589 -- bug 6114024
2590 -- Call Pricing API after the update to okc_operation_lines
2591 IF (l_opl_id_tbl.COUNT > 0 ) THEN
2592 IF l_can_copy = 'Y' THEN
2593 LOG_MESSAGES('l_can_copy = Y ');
2594 LOG_MESSAGES('Calling OKS_REPRICE_PVT.call_pricing_api');
2595 LOG_MESSAGES('p_subject_chr_id='||l_target_header_id);
2596 LOG_MESSAGES('p_subject_top_line_id='||l_cle_id);
2597 LOG_MESSAGES('p_subject_sub_line_tbl.count='||l_subject_sub_line_tbl.count);
2598
2599 OKS_REPRICE_PVT.call_pricing_api(
2600 p_api_version => 1.0,
2601 p_init_msg_list => FND_API.G_FALSE,
2602 x_return_status => l_return_status,
2603 x_msg_count => l_msg_count,
2604 x_msg_data => l_msg_data,
2605 p_subject_chr_id => l_target_header_id,
2606 p_subject_top_line_id => l_cle_id,
2607 p_subject_sub_line_tbl => l_subject_sub_line_tbl );
2608
2609 LOG_MESSAGES('After Calling OKS_REPRICE_PVT.call_pricing_api l_return_status: '||l_return_status);
2610 LOG_MESSAGES(' ');
2611 ELSE
2612 LOG_MESSAGES('l_can_copy = N ');
2613 LOG_MESSAGES('Calling OKS_REPRICE_PVT.call_pricing_api');
2614 LOG_MESSAGES('p_subject_chr_id='||l_target_header_id);
2615 LOG_MESSAGES('p_subject_top_line_id='||l_target_line_id);
2616 LOG_MESSAGES('p_subject_sub_line_tbl.count='||l_subject_sub_line_tbl.COUNT);
2617
2618 OKS_REPRICE_PVT.call_pricing_api(
2619 p_api_version => 1.0,
2620 p_init_msg_list => FND_API.G_FALSE,
2621 x_return_status => l_return_status,
2622 x_msg_count => l_msg_count,
2623 x_msg_data => l_msg_data,
2624 p_subject_chr_id => l_target_header_id,
2625 p_subject_top_line_id => l_target_line_id,
2626 p_subject_sub_line_tbl => l_subject_sub_line_tbl);
2627
2628 LOG_MESSAGES('After Calling OKS_REPRICE_PVT.call_pricing_api l_return_status: '||l_return_status);
2629 LOG_MESSAGES(' ');
2630 END IF;
2631 END IF; -- l_opl_id_tbl.COUNT > 0
2632
2633 -- end added Bug 6114024
2634
2635 --update date_renewed for all the source sublines that where successfully copied
2636 IF (l_src_sub_line_id_tbl.COUNT > 0) THEN
2637
2638 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2639 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_sub_lines','updating date_renewed for source sublines l_src_sub_line_id_tbl.COUNT='||l_src_sub_line_id_tbl.COUNT);
2640 END IF;
2641
2642 l_date_renewed := sysdate;
2643 FORALL i IN l_src_sub_line_id_tbl.FIRST..l_src_sub_line_id_tbl.LAST
2644 UPDATE okc_k_lines_b SET
2645 date_renewed = l_date_renewed,
2646 object_version_number = object_version_number + 1,
2647 last_updated_by = l_user_id,
2648 last_update_date = l_update_date,
2649 last_update_login = l_login_id,
2650 request_id = l_request_id,
2651 program_application_id = l_prog_appl_id,
2652 program_id = l_prog_id,
2653 program_update_date = l_update_date
2654 WHERE id = l_src_sub_line_id_tbl(i);
2655
2656 --update date_renewed for all the source toplines if all sublines under them have been renewed
2657 --if any sublines under a topline are terminated or cancelled they are ignored
2658 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2659 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_top_lines','updating date_renewed for source toplines');
2660 END IF;
2661
2662 UPDATE okc_k_lines_b tl SET
2663 tl.date_renewed = l_date_renewed,
2664 tl.object_version_number = tl.object_version_number + 1,
2665 tl.last_updated_by = l_user_id,
2666 tl.last_update_date = l_update_date,
2667 tl.last_update_login = l_login_id,
2668 tl.request_id = l_request_id,
2669 tl.program_application_id = l_prog_appl_id,
2670 tl.program_id = l_prog_id,
2671 tl.program_update_date = l_update_date
2672 WHERE tl.id IN
2673 (SELECT b.object_cle_id FROM okc_operation_lines a, okc_operation_lines b
2674 WHERE a.oie_id = p_oie_id
2675 AND b.oie_id = p_oie_id
2676 AND a.id = b.parent_ole_id
2677 AND a.parent_ole_id IS NULL
2678 AND b.select_yn = 'Y')
2679 AND NOT EXISTS
2680 (SELECT 1 FROM okc_k_lines_b sl
2681 WHERE sl.cle_id = tl.id
2682 AND sl.date_terminated IS NULL
2683 AND sl.date_cancelled IS NULL
2684 AND sl.date_renewed IS NULL);
2685
2686 --update date_renewed for all the source headers if all toplines under them have been renewed
2687 --if any toplines are terminated or cancelled they are ignored
2688 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2689 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_headers','updating date_renewed for source headers');
2690 END IF;
2691
2692 UPDATE okc_k_headers_all_b h SET
2693 h.date_renewed = l_date_renewed,
2694 h.object_version_number = h.object_version_number + 1,
2695 h.last_updated_by = l_user_id,
2696 h.last_update_date = l_update_date,
2697 h.last_update_login = l_login_id,
2698 h.request_id = l_request_id,
2699 h.program_application_id = l_prog_appl_id,
2700 h.program_id = l_prog_id,
2701 h.program_update_date = l_update_date
2702 WHERE h.id IN
2703 (SELECT a.object_chr_id FROM okc_operation_lines a
2704 WHERE a.oie_id = p_oie_id
2705 AND a.object_cle_id IS NULL
2706 AND a.subject_cle_id IS NULL
2707 AND a.select_yn = 'Y')
2708 AND NOT EXISTS
2709 (SELECT 1 FROM okc_k_lines_b tl
2710 WHERE tl.dnz_chr_id = h.id
2711 AND tl.cle_id IS NULL
2712 AND tl.lse_id IN (1,12,14,19)
2713 AND tl.date_terminated IS NULL
2714 AND tl.date_cancelled IS NULL
2715 AND tl.date_renewed IS NULL);
2716
2717 END IF; --of IF (l_src_sub_line_id_tbl.COUNT > 0) THEN
2718
2719 l_opl_id_tbl.delete;
2720 l_opl_sub_cle_id_tbl.delete;
2721 l_opl_status_tbl.delete;
2722 l_src_sub_line_id_tbl.delete;
2723 l_subject_sub_line_tbl.delete;
2724
2725 log_messages('Processed selected lines');
2726 LOG_MESSAGES(' ');
2727
2728
2729 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2730 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_invoice_text','Calling OKS_RENEW_CONTRACT_PVT.update_invoice_text p_chr_id='||l_target_header_id);
2731 END IF;
2732
2733 LOG_MESSAGES('Calling OKS_RENEW_CONTRACT_PVT.update_invoice_text');
2734
2735 OKS_RENEW_CONTRACT_PVT.update_invoice_text(
2736 p_api_version => 1.0,
2737 p_init_msg_list => FND_API.G_FALSE,
2738 p_commit => FND_API.G_FALSE,
2739 x_return_status => l_return_status,
2740 x_msg_count => l_msg_count,
2741 x_msg_data => l_msg_data,
2742 p_chr_id => l_target_header_id);
2743
2744 LOG_MESSAGES('After Calling OKS_RENEW_CONTRACT_PVT.update_invoice_text, l_return_status: '||l_return_status);
2745 LOG_MESSAGES(' ');
2746
2747 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2748 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_invoice_text','After call to OKS_RENEW_CONTRACT_PVT.update_invoice_text l_return_status='||l_return_status);
2749 END IF;
2750
2751 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2752 RAISE FND_API.g_exc_unexpected_error;
2753 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2754 RAISE FND_API.g_exc_error;
2755 END IF;
2756 log_messages('Updated invoice text');
2757 LOG_MESSAGES(' ');LOG_MESSAGES(' ');
2758
2759 ------------------------------------------------------------------------------
2760 ---Since all operations are done update the contract amount
2761 ------------------------------------------------------------------------------
2762 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2763 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_contract_amount','calling update_contract_amount p_header_id='||l_target_header_id);
2764 END IF;
2765
2766 UPDATE_CONTRACT_AMOUNT(
2767 p_header_id => l_target_header_id,
2768 x_return_status => l_return_status);
2769
2770 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2771 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_contract_amount','after call to update_contract_amount l_return_status='||l_return_status);
2772 END IF;
2773
2774 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2775 RAISE FND_API.g_exc_unexpected_error;
2776 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2777 RAISE FND_API.g_exc_error;
2778 END IF;
2779 log_messages('Updated contract header and line amounts');
2780 LOG_MESSAGES(' ');
2781
2782
2783 --set the x_return_status depending on any warnings or errors during line copy
2784 IF l_errors THEN
2785 x_return_status := OKC_API.g_ret_sts_warning;
2786 l_dummy := FND_CONCURRENT.set_completion_status('WARNING', null);
2787 log_messages('Some lines where not copied');
2788 ELSIF l_warnings THEN
2789 x_return_status := OKC_API.g_ret_sts_warning;
2790 l_dummy := FND_CONCURRENT.set_completion_status('WARNING', null);
2791 log_messages('Some lines where copied with warnings');
2792 ELSE
2793 x_return_status := FND_API.g_ret_sts_success;
2794 l_dummy := FND_CONCURRENT.set_completion_status('NORMAL', null);
2795 END IF;
2796
2797 --log all the error and warning messages the CP log file
2798 IF (l_errors OR l_warnings) AND (p_conc_program = 'Y') THEN
2799 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
2800 log_messages(FND_MSG_PUB.get(i, 'F'));
2801 END LOOP;
2802 END IF;
2803
2804 COMMIT;
2805
2806 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
2807 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end', 'x_return_status=' || x_return_status ||' ,retcode='|| retcode);
2808 END IF;
2809 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2810
2811 log_messages('Renewal Consolidation conurrent program - END');
2812 LOG_MESSAGES(' ');
2813
2814 EXCEPTION
2815
2816 WHEN FND_API.g_exc_error THEN
2817 ROLLBACK TO submit_PVT;
2818 x_return_status := FND_API.g_ret_sts_error ;
2819 retcode := 2;
2820 l_dummy := FND_CONCURRENT.set_completion_status('ERROR', null);
2821
2822 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
2823 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_error', 'x_return_status='||x_return_status);
2824 END IF;
2825 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2826
2827 IF(get_oper_top_lines%isopen) THEN
2828 CLOSE get_oper_top_lines;
2829 END IF;
2830 IF(get_oper_sub_lines%isopen) THEN
2831 CLOSE get_oper_sub_lines;
2832 END IF;
2833 IF(set_org%isopen) THEN
2834 CLOSE set_org;
2835 END IF;
2836
2837 --log all the error messages the CP log file
2838 IF (p_conc_program = 'Y') THEN
2839 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
2840 log_messages(FND_MSG_PUB.get(i, 'F'));
2841 END LOOP;
2842 END IF;
2843 log_messages('Renewal Consolidation conurrent program - Error');
2844
2845 WHEN FND_API.g_exc_unexpected_error THEN
2846 ROLLBACK TO submit_PVT;
2847 x_return_status := FND_API.g_ret_sts_unexp_error ;
2848 retcode := 2;
2849 l_dummy := FND_CONCURRENT.set_completion_status('ERROR', null);
2850
2851 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
2852 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status='||x_return_status);
2853 END IF;
2854 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2855
2856 IF(get_oper_top_lines%isopen) THEN
2857 CLOSE get_oper_top_lines;
2858 END IF;
2859 IF(get_oper_sub_lines%isopen) THEN
2860 CLOSE get_oper_sub_lines;
2861 END IF;
2862 IF(set_org%isopen) THEN
2863 CLOSE set_org;
2864 END IF;
2865
2866 --log all the error messages the CP log file
2867 IF (p_conc_program = 'Y') THEN
2868 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
2869 log_messages(FND_MSG_PUB.get(i, 'F'));
2870 END LOOP;
2871 END IF;
2872 log_messages('Renewal Consolidation conurrent program - Unexpected Error');
2873
2874 WHEN OTHERS THEN
2875 ROLLBACK TO submit_PVT;
2876 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2877 retcode := 2;
2878 l_dummy := FND_CONCURRENT.set_completion_status('ERROR', null);
2879 errbuf := SQLCODE || SQLERRM;
2880
2881 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
2882 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', errbuf);
2883 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, errbuf);
2884 END IF;
2885 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2886
2887 IF(get_oper_top_lines%isopen) THEN
2888 CLOSE get_oper_top_lines;
2889 END IF;
2890 IF(get_oper_sub_lines%isopen) THEN
2891 CLOSE get_oper_sub_lines;
2892 END IF;
2893 IF(set_org%isopen) THEN
2894 CLOSE set_org;
2895 END IF;
2896
2897 --log all the error messages the CP log file
2898 IF (p_conc_program = 'Y') THEN
2899 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
2900 log_messages(FND_MSG_PUB.get(i, 'F'));
2901 END LOOP;
2902 END IF;
2903 log_messages('Renewal Consolidation conurrent program - Error - OTHERS '||errbuf);
2904
2905 END SUBMIT;
2906
2907
2908 --anjkumar : procedure rewritten to do direct updates
2909 --also for R12 need to roll up tax_amount for toplines and header
2910 PROCEDURE UPDATE_CONTRACT_AMOUNT(p_header_id IN NUMBER,
2911 x_return_status OUT NOCOPY VARCHAR2) IS
2912
2913 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2914
2915 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_AMOUNT';
2916 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
2917 l_error_text VARCHAR2(512);
2918
2919 CURSOR c_top_lines(cp_chr_id IN NUMBER) IS
2920 SELECT c.cle_id, SUM(nvl(c.price_negotiated, 0)), SUM(nvl(s.tax_amount, 0))
2921 FROM okc_k_lines_b c, oks_k_lines_b s
2922 WHERE c.dnz_chr_id = cp_chr_id
2923 --get only sublines for 1,12,19 (14:no renewal, 46:no sublines)
2924 AND c.lse_id IN (7, 8, 9, 10, 11, 35, 13, 25)
2925 AND s.cle_id = c.id
2926 GROUP BY c.cle_id;
2927
2928 l_id_tbl num_tbl_type;
2929 l_price_tbl num_tbl_type;
2930 l_tax_tbl num_tbl_type;
2931
2932 l_update_date DATE;
2933 l_user_id NUMBER;
2934 l_login_id NUMBER;
2935 l_request_id NUMBER;
2936 l_prog_appl_id NUMBER;
2937 l_prog_id NUMBER;
2938
2939 BEGIN
2940
2941 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
2942 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','p_header_id='||p_header_id);
2943 END IF;
2944
2945 SAVEPOINT update_contract_amount_PVT;
2946 x_return_status := FND_API.G_RET_STS_SUCCESS;
2947
2948 IF p_header_id IS NOT NULL THEN
2949
2950 l_update_date := sysdate;
2951 l_user_id := FND_GLOBAL.USER_ID;
2952 l_login_id := FND_GLOBAL.LOGIN_ID;
2953 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2954 l_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
2955 l_prog_id := FND_GLOBAL.CONC_PROGRAM_ID;
2956
2957 --update the topline price_negotiated(OKC) and tax_amount(OKS) columns
2958 --no need for warranty(14 - cannot be renewed) and subscription (46 - no toplines)
2959 OPEN c_top_lines(p_header_id);
2960 LOOP
2961 FETCH c_top_lines BULK COLLECT INTO l_id_tbl, l_price_tbl, l_tax_tbl LIMIT 1000;
2962
2963 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2964 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.top_line_loop','l_id_tbl.count='||l_id_tbl.count);
2965 END IF;
2966
2967 EXIT WHEN (l_id_tbl.COUNT = 0);
2968
2969 FORALL i IN l_id_tbl.first..l_id_tbl.last
2970 UPDATE okc_k_lines_b
2971 SET price_negotiated = l_price_tbl(i),
2972 object_version_number = object_version_number + 1,
2973 last_updated_by = l_user_id,
2974 last_update_date = l_update_date,
2975 last_update_login = l_login_id,
2976 request_id = l_request_id,
2977 program_application_id = l_prog_appl_id,
2978 program_id = l_prog_id,
2979 program_update_date = l_update_date
2980 WHERE id = l_id_tbl(i);
2981
2982 FORALL i IN l_id_tbl.first..l_id_tbl.last
2983 UPDATE oks_k_lines_b
2984 SET tax_amount = l_tax_tbl(i),
2985 object_version_number = object_version_number + 1,
2986 last_updated_by = l_user_id,
2987 last_update_date = l_update_date,
2988 last_update_login = l_login_id,
2989 request_id = l_request_id
2990 WHERE cle_id = l_id_tbl(i);
2991
2992 END LOOP;
2993 CLOSE c_top_lines;
2994
2995 l_id_tbl.delete;
2996 l_price_tbl.delete;
2997 l_tax_tbl.delete;
2998
2999 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
3000 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.top_lines','top lines price_negotiated and tax_amount updated');
3001 END IF;
3002
3003 --update the header
3004 UPDATE okc_k_headers_all_b h
3005 SET h.estimated_amount =
3006 (SELECT SUM(price_negotiated) FROM okc_k_lines_b tl
3007 WHERE tl.dnz_chr_id = p_header_id AND tl.cle_id IS NULL
3008 AND tl.lse_id IN (1, 12, 19, 46)),
3009 h.object_version_number = h.object_version_number + 1,
3010 h.last_updated_by = l_user_id,
3011 h.last_update_date = l_update_date,
3012 h.last_update_login = l_login_id,
3013 h.request_id = l_request_id,
3014 h.program_application_id = l_prog_appl_id,
3015 h.program_id = l_prog_id,
3016 h.program_update_date = l_update_date
3017 WHERE h.id = p_header_id;
3018
3019 UPDATE oks_k_headers_b h
3020 SET h.tax_amount =
3021 (SELECT SUM(stl.tax_amount) FROM okc_k_lines_b ctl, oks_k_lines_b stl
3022 WHERE ctl.dnz_chr_id = p_header_id AND ctl.cle_id IS NULL
3023 AND ctl.lse_id IN (1, 12, 19, 46) AND stl.cle_id = ctl.id),
3024 h.object_version_number = h.object_version_number + 1,
3025 h.last_updated_by = l_user_id,
3026 h.last_update_date = l_update_date,
3027 h.last_update_login = l_login_id,
3028 h.request_id = l_request_id
3029 WHERE h.chr_id = p_header_id;
3030
3031 IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
3032 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.header','header estimated_amount and tax_amount updated');
3033 END IF;
3034
3035 END IF;
3036
3037 IF(FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
3038 FND_LOG.string(FND_LOG.level_procedure,l_mod_name||'.end','x_return_status='||x_return_status);
3039 END IF;
3040
3041 EXCEPTION
3042
3043 WHEN OTHERS THEN
3044 ROLLBACK TO update_contract_amount_PVT;
3045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3046
3047 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
3048 l_error_text := substr (SQLERRM, 1, 240);
3049 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
3050 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
3051 END IF;
3052 IF (c_top_lines%isopen) THEN
3053 CLOSE c_top_lines;
3054 END IF;
3055
3056
3057 END UPDATE_CONTRACT_AMOUNT;
3058
3059
3060 PROCEDURE LOG_MESSAGES(p_mesg IN VARCHAR2) IS
3061 BEGIN
3062 IF l_conc_program = 'N' THEN
3063 NULL;
3064 ELSE
3065 fnd_file.put_line(FND_FILE.LOG, p_mesg);
3066 END IF;
3067 END LOG_MESSAGES;
3068
3069 -- Bug#5981381: Cache the class_operation_id instead of deriving everytime;
3070
3071 BEGIN
3072 OPEN cur_class_operations ;
3073 FETCH cur_class_operations INTO G_RENCON_CLASS_OPERATION_ID;
3074 CLOSE cur_class_operations ;
3075
3076 END OKS_RENCON_PVT ;
3077