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