[Home] [Help]
PACKAGE BODY: APPS.OKS_MASS_UPD_PVT
Source
1 PACKAGE BODY OKS_MASS_UPD_PVT AS
2 /* $Header: OKSRMUSB.pls 120.0.12020000.2 2013/02/06 09:19:24 spingali noship $ */
3
4 --========================================================================
5 -- PROCEDURE : DELETE_CONTRACT_DATA
6 --
7 -- PARAMETERS:
8 -- p_contract_id Id for the Contract Header to be deleted
9 -- x_return_status Return status
10 --
11 -- COMMENT : This procedure deletes the record from the temp tables for contract
12 --========================================================================
13 PROCEDURE DELETE_CONTRACT_DATA(
14 p_contract_id IN NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2)
16 IS
17 BEGIN
18
19 DELETE FROM oks_headers_temp
20 WHERE chr_id = p_contract_id;
21
22 DELETE FROM oks_lines_temp
23 WHERE dnz_chr_id = p_contract_id;
24
25 DELETE FROM oks_sublines_temp
26 WHERE dnz_chr_id = p_contract_id;
27
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29 EXCEPTION
30 WHEN OTHERS
31 THEN
32 x_return_status := FND_API.G_RET_STS_ERROR;
33 END DELETE_CONTRACT_DATA;
34
35
36 /*validate the status change related data*/
37 PROCEDURE vld_massupd_status_prc(p_contract_id IN VARCHAR2,
38 x_return_status out nocopy varchar2) IS
39 -- PRAGMA autonomous_transaction;
40
41 l_mass_update_status_tbl oks_mass_upd_pvt.mass_update_status_tbl;
42
43 CURSOR get_stecode(p_sts_code VARCHAR2) IS
44 select ste_code from okc_statuses_b where code = p_sts_code;
45
46 l_return_status VARCHAR2(10);
47 l_old_ste_code VARCHAR2(100);
48 l_new_ste_code VARCHAR2(100);
49 l_valid_status VARCHAR2(100);
50 l_api_name VARCHAR2(100) := 'oks_mass_upd_pvt.vld_massupd_status_prc';
51
52 FUNCTION check_ste_code(p_old_ste_code IN VARCHAR2,
53 p_new_ste_code IN VARCHAR) RETURN VARCHAR2 IS
54
55 return_status VARCHAR2(10);
56 l_string VARCHAR2(300);
57
58 CURSOR get_statuses(p_old_ste_code VARCHAR2) IS
59 select S.STE_CODE STE_CODE1
60 from okc_statuses_v S, fnd_lookups ST
61 where S.STE_CODE in (NVL(p_old_ste_code, 'ENTERED'),
62 decode(p_old_ste_code,
63 NULL,
64 'CANCELLED',
65 'ENTERED',
66 'CANCELLED',
67 'ACTIVE',
68 'HOLD',
69 'SIGNED',
70 'HOLD'
71
72 ))
73 and sysdate between s.start_date and nvl(s.end_date, sysdate)
74 and st.lookup_type = 'OKC_STATUS_TYPE'
75 and st.lookup_code = s.ste_code
76 and sysdate between st.start_date_active and
77 nvl(st.end_date_active, sysdate)
78 and ST.enabled_flag = 'Y'
79 and S.code not like 'QA%HOLD'
80 AND p_old_ste_code <> 'CANCELLED';
81
82 BEGIN
83
84 IF p_old_ste_code = 'HOLD' THEN
85 IF p_new_ste_code IN ('ACTIVE', 'EXPIRED', 'SIGNED') THEN
86 return_status := 'S';
87 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
88 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
89 G_MODULE || l_api_name,
90 'p_new_ste_code ' || p_new_ste_code ||
91 ' is a valid status type for the current status ' ||
92 p_old_ste_code);
93 END IF;
94
95 ELSE
96 return_status := 'E';
97 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
98 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
99 G_MODULE || l_api_name,
100 'p_new_ste_code ' || p_new_ste_code ||
101 ' is not a valid status type for the current status ' ||
102 p_old_ste_code);
103 END IF;
104 END IF;
105 ELSIF p_old_ste_code = 'CANCELLED' THEN
106 IF p_new_ste_code IN ('ENTERED', 'CANCELLED') THEN
107 return_status := 'S';
108 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
109 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
110 G_MODULE || l_api_name,
111 'p_new_ste_code ' || p_new_ste_code ||
112 ' is a valid status type for the current status ' ||
113 p_old_ste_code);
114 END IF;
115
116 ELSE
117 return_status := 'E';
118 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
119 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
120 G_MODULE || l_api_name,
121 'Status type ' || p_new_ste_code ||
122 ' is not a valid status type for the current status ' ||
123 p_old_ste_code);
124 END IF;
125
126 END IF;
127 ELSE
128 OPEN get_statuses(p_old_ste_code);
129 LOOP
130 FETCH get_statuses
131 INTO l_valid_status;
132 EXIT WHEN get_statuses%NOTFOUND;
133 IF p_new_ste_code = l_valid_status THEN
134 return_status := 'S';
135 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
137 G_MODULE || l_api_name,
138 'Status type ' || l_valid_status ||
139 ' is a valid status type for the current status ' ||
140 p_old_ste_code);
141 END IF;
142 EXIT;
143 ELSE
144 return_status := 'E';
145 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
146 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
147 G_MODULE || l_api_name,
148 'Status type ' || l_valid_status ||
149 ' is not a valid status type for the current status ' ||
150 p_old_ste_code);
151 END IF;
152
153 END IF;
154 END LOOP;
155 END IF;
156 RETURN return_status;
157 END;
158
159 BEGIN
160
161 SELECT * BULK COLLECT
162 INTO l_mass_update_status_tbl
163 FROM (SELECT ID line_id,
164 To_Number(NULL) subline_id,
165 dnz_chr_id,
166 lse_id,
167 line_number,
168 reason_code,
169 status,
170 new_status,
171 comments
172 FROM oks_lines_temp
173 WHERE status_change_flag = 'Y'
174 AND dnz_chr_id = To_Number(p_contract_id)
175 UNION ALL
176 SELECT cle_id line_id,
177 id subline_id,
178 dnz_chr_id,
179 lse_id,
180 line_number,
181 reason_code,
182 status,
183 new_status,
184 comments
185 FROM oks_sublines_temp
186 WHERE status_change_flag = 'Y'
187 AND dnz_chr_id = to_number(p_contract_id));
188
189 IF l_mass_update_status_tbl.Count > 0 THEN
190 for i in l_mass_update_status_tbl.first .. l_mass_update_status_tbl.last loop
191
192 open get_stecode(l_mass_update_status_tbl(i).old_sts_code);
193 fetch get_stecode
194 into l_old_ste_code;
195 close get_stecode;
196
197 open get_stecode(l_mass_update_status_tbl(i).new_sts_code);
198 fetch get_stecode
199 into l_new_ste_code;
200 close get_stecode;
201
202 l_return_status := check_ste_code(l_old_ste_code, l_new_ste_code);
203
204 IF l_return_status <> 'S' THEN
205 IF l_mass_update_status_tbl(i).sub_line_id is null then
206 UPDATE oks_lines_temp
207 SET status_error_flag = 'Y',
208 status_err_message = l_new_ste_code ||
209 ' is not a valid status for the existing status type ' ||
210 l_old_ste_code
211 WHERE id = l_mass_update_status_tbl(i).line_id;
212 ELSE
213 UPDATE oks_sublines_temp
214 SET status_error_flag = 'Y',
215 status_err_message = l_new_ste_code ||
216 ' is not a valid status for the existing status type ' ||
217 l_old_ste_code
218 WHERE id = l_mass_update_status_tbl(i).sub_line_id;
219 END IF;
220 ELSE
221 IF l_mass_update_status_tbl(i).sub_line_id is null then
222 UPDATE oks_lines_temp
223 SET status_error_flag = 'N', error_message = NULL
224 WHERE id = l_mass_update_status_tbl(i).line_id;
225 ELSE
226 UPDATE oks_sublines_temp
227 SET status_error_flag = 'N', error_message = NULL
228 WHERE id = l_mass_update_status_tbl(i).sub_line_id;
229 END IF;
230
231 END IF;
232
233 END LOOP;
234 END IF;
235
236 x_return_status := 'S';
237
238 END;
239
240 /*validate the effectivity change related data*/
241 PROCEDURE vld_massupd_eff_prc(p_contract_id IN VARCHAR2,
242 x_return_status out nocopy varchar2) IS
243 --PRAGMA autonomous_transaction;
244 l_mass_update_eff_tbl oks_mass_upd_pvt.mass_update_effectivity_tbl;
245 l_k_start_date DATE;
246 l_k_end_date DATE;
247 l_kl_start_date DATE;
248 l_kl_end_date DATE;
249
250 BEGIN
251
252 SELECT * BULK COLLECT
253 INTO l_mass_update_eff_tbl
254 FROM (SELECT ID line_id,
255 To_Number(NULL) sub_line_id,
256 dnz_chr_id chr_id,
257 lse_id,
258 line_number,
259 start_date,
260 end_date
261 FROM oks_lines_temp
262 WHERE (start_dt_eff_change_flag = 'Y' OR
263 end_dt_eff_change_flag = 'Y')
264 AND dnz_chr_id = to_number(p_contract_id)
265 UNION ALL
266 SELECT cle_id line_id,
267 id sub_line_id,
268 dnz_chr_id chr_id,
269 lse_id,
270 line_number,
271 start_date,
272 end_date
273 FROM oks_sublines_temp
274 WHERE (start_dt_eff_change_flag = 'Y' OR
275 end_dt_eff_change_flag = 'Y')
276 AND dnz_chr_id = to_number(p_contract_id));
277
278 IF l_mass_update_eff_tbl.Count > 0 THEN
279 FOR i IN l_mass_update_eff_tbl.first .. l_mass_update_eff_tbl.last LOOP
280
281 IF l_mass_update_eff_tbl(i).sub_line_id IS NULL THEN
282
283 SELECT start_date, end_date
284 INTO l_k_start_date, l_k_end_date
285 FROM okc_k_headers_all_b
286 where id = l_mass_update_eff_tbl(i).chr_id;
287
288 IF (l_mass_update_eff_tbl(i)
289 .start_date < l_k_start_date OR l_mass_update_eff_tbl(i)
290 .start_date > l_k_end_date) THEN
291 UPDATE oks_lines_temp
292 SET start_dt_error_flag = 'Y',
293 st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message ||'- Line Start Date is out of Contract''s Effectivity, line_number: ' || l_mass_update_eff_tbl(i).line_number,
294 'Line Start Date is out of Contract''s Effectivity, line_number: ' || l_mass_update_eff_tbl(i).line_number)
295 WHERE id = l_mass_update_eff_tbl(i).line_id;
296
297 --x_return_status := 'E';
298 --RETURN;
299 END IF;
300
301 IF (l_mass_update_eff_tbl(i)
302 .end_date < l_k_start_date OR l_mass_update_eff_tbl(i)
303 .end_date > l_k_end_date) THEN
304 UPDATE oks_lines_temp
305 SET end_dt_error_flag = 'Y',
306 end_dt_err_message = Nvl2(end_dt_err_message, end_dt_err_message || ' - Line End Date is out of Contract''s Effectivity, line_number: '|| l_mass_update_eff_tbl(i).line_number,
307 'Line End Date is out of Contract''s Effectivity, line_number: '|| l_mass_update_eff_tbl(i).line_number)
308 WHERE id = l_mass_update_eff_tbl(i).line_id;
309
310 --x_return_status := 'E';
311 END IF;
312
313 ELSE
314 --l_mass_update_eff_tbl(i).subline_id
315
316 SELECT l.START_DATE, l.END_DATE
317 INTO l_kl_start_date, l_kl_end_date
318 FROM oks_lines_temp l
319 WHERE l.id = l_mass_update_eff_tbl(i).line_id;
320
321 IF (l_mass_update_eff_tbl(i)
322 .start_date < l_kl_start_date OR l_mass_update_eff_tbl(i)
323 .start_date > l_kl_end_date) THEN
324
325 UPDATE oks_sublines_temp
326 SET start_dt_error_flag = 'Y',
327 st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message || ' - Subline Start Date is out of Contract line''s Effectivity, subline_number: ' || l_mass_update_eff_tbl(i).line_number,
328 'Subline Start Date is out of Contract line''s Effectivity, subline_number: ' || l_mass_update_eff_tbl(i).line_number )
329 WHERE id = l_mass_update_eff_tbl(i).sub_line_id;
330
331 --x_return_status := 'E';
332 END IF;
333
334 IF (l_mass_update_eff_tbl(i)
335 .end_date < l_kl_start_date OR l_mass_update_eff_tbl(i)
336 .end_date > l_kl_end_date) THEN
337
338 UPDATE oks_sublines_temp
339 SET end_dt_error_flag = 'Y',
340 end_dt_err_message = Nvl2(end_dt_err_message , end_dt_err_message || ' - Subline End Date is out of Contract line''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number,
341 'Subline End Date is out of Contract line''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number)
342 WHERE id = l_mass_update_eff_tbl(i).sub_line_id;
343
344 --x_return_status := 'E';
345 END IF;
346
347 SELECT start_date, end_date
348 INTO l_k_start_date, l_k_end_date
349 FROM okc_k_headers_all_b
350 WHERE id = l_mass_update_eff_tbl(i).chr_id;
351
352 IF (l_mass_update_eff_tbl(i)
353 .start_date < l_k_start_date OR l_mass_update_eff_tbl(i)
354 .start_date > l_k_end_date) THEN
355
356 UPDATE oks_sublines_temp
357 SET start_dt_error_flag = 'Y',
358 st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message ||' - Subline Start Date is out of Contract''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number,
359 'Subline Start Date is out of Contract''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number)
360 WHERE id = l_mass_update_eff_tbl(i).sub_line_id;
361
362 --x_return_status := 'E';
363 END IF;
364
365 IF (l_mass_update_eff_tbl(i)
366 .end_date < l_k_start_date OR l_mass_update_eff_tbl(i)
367 .end_date > l_k_end_date) THEN
368
369 UPDATE oks_sublines_temp
370 SET end_dt_error_flag = 'Y',
371 end_dt_err_message = Nvl2(end_dt_err_message, end_dt_err_message || ' - Subline End Date is out of Contract''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number,
372 'Subline End Date is out of Contract''s Effectivity, subline_number: '|| l_mass_update_eff_tbl(i).line_number)
373 WHERE id = l_mass_update_eff_tbl(i).sub_line_id;
374
375 --x_return_status := 'E';
376 END IF;
377
378 END IF; --l_mass_update_eff_tbl(i).subline_id
379
380 END LOOP;
381 END IF;
382
383 /*Overall status should be 'S' to indicate that the validation process is done*/
384 x_return_status := 'S';
385
386 END;
387
388 /*validate the mass update data*/
389 PROCEDURE vld_massupd_data_prc(RETCODE OUT NOCOPY NUMBER,
390 p_contract_id IN VARCHAR2) IS
391
392 l_contract_id VARCHAR2(50) := p_contract_id;
393
394 x_return_status VARCHAR2(1) := 'W';
395
396 BEGIN
397
398 retcode := 3; --0 for success, 1 for warning, 2 for error
399
400 vld_massupd_status_prc(l_contract_id, x_return_status);
401
402 vld_massupd_eff_prc(l_contract_id, x_return_status);
403
404 /*IF x_return_status = 'S' THEN
405 RETCODE := 0;
406 END IF; */
407
408 END;
409
410 /*
411 This procedure is a concurrent program, that trigger mass update wrapper for all
412 the three changes done through mass update UI
413 */
414 PROCEDURE launch_massupd_conc_prog(ERRBUF OUT NOCOPY VARCHAR2,
415 RETCODE OUT NOCOPY NUMBER,
416 p_contract_id IN VARCHAR2) IS
417
418 l_massupd_return_status VARCHAR2(1);
419 l_return_status VARCHAR2(1);
420 l_overall_status VARCHAR2(1);
421 l_msg VARCHAR2(2000);
422 l_no_of_lines VARCHAR2(2000);
423 l_no_of_success_lines VARCHAR2(2000);
424 l_no_of_rejected_lines VARCHAR2(2000);
425 l_k_n_display VARCHAR2(240);
426
427 CURSOR k_details_csr IS
428 SELECT Decode(contract_number_modifier, NULL, contract_number, contract_number ||' - '||contract_number_modifier) contract_number
429 FROM okc_k_headers_all_b
430 WHERE id = p_contract_id;
431
432 BEGIN
433 --retcode := 2; --0 for success, 1 for warning, 2 for error
434
435 OPEN k_details_csr;
436 FETCH k_details_csr INTO l_k_n_display;
437 CLOSE k_details_csr;
438
439 l_msg := 'Service Contracts Mass Update Report for Contract '||l_k_n_display;
440 l_no_of_lines := 'No Of Lines Processed';
441 l_no_of_success_lines := 'No Of Successful Lines';
442 l_no_of_rejected_lines :='No Of Rejected Lines';
443
444 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
445 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
446 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
447 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' '|| l_msg ||' ') ;
448 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ****************************************************** ') ;
449
450 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
451 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
452
453 /*LOG*/
454 FND_FILE.PUT_LINE( FND_FILE.log, ' '|| l_msg ||' ') ;
455 FND_FILE.PUT_LINE( FND_FILE.log, ' ****************************************************** ') ;
456
457 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
458 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
459
460 /*LOG*/
461
462 mass_update_status_wrapper(p_contract_id,l_return_status);
463 IF l_return_status <> 'S' THEN
464 l_overall_status := l_return_status;
465 END IF;
466 fnd_file.put_line (fnd_file.log,'mass_update_status_wrapper: '||l_overall_status);
467
468 mass_update_eff_wrapper(p_contract_id,l_return_status);
469 IF l_return_status <> 'S' THEN
470 l_overall_status := l_return_status;
471 END IF;
472 fnd_file.put_line (fnd_file.log,'mass_update_eff_wrapper: '||l_overall_status);
473
474 mass_update_price_wrapper(p_contract_id,l_return_status);
475 IF l_return_status <> 'S' THEN
476 l_overall_status := l_return_status;
477 END IF;
478 fnd_file.put_line (fnd_file.log,'mass_update_price_wrapper: '||l_overall_status);
479
480
481 /*
482 OKS_AUTH_UTIL_PVT.CHECK_MASSUPD_REQUEST(p_contract_id,l_massupd_return_status);
483
484 IF l_massupd_return_status = 'S' THEN
485 DELETE_CONTRACT_DATA(p_contract_id,l_return_status);
486 END IF;
487 */
488 IF l_overall_status IS NOT NULL THEN
489 retcode := 1;
490 ELSE
491 retcode := 0;
492 END IF;
493
494 COMMIT;
495
496 --retcode := 0;
497
498 EXCEPTION
499 WHEN OTHERS THEN
500 retcode := 2;
501 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' Entered exception block'||sqlerrm) ;
502 FND_FILE.PUT_LINE( FND_FILE.log, ' Entered exception block'||sqlerrm) ;
503
504 END;
505
506 procedure mass_update_price_prc(p_mass_update_price_tbl in mass_update_price_tbl,
507 x_return_status out nocopy VARCHAR2,
508 x_msg_data OUT NOCOPY VARCHAR2,
509 x_msg_count OUT NOCOPY NUMBER) IS
510
511 CURSOR header_details(p_contract_id NUMBER) IS
512 SELECT authoring_org_id, inv_organization_id
513 FROM okc_k_headers_all_b
514 WHERE id = p_contract_id;
515
516 cursor cur_line(p_subline_id in number) is
517 SELECT cleb.price_negotiated, kln.tax_amount
518 FROM okc_k_lines_b cleb, oks_k_lines_B kln
519 WHERE cleb.id = p_subline_id
520 AND cleb.id = kln.cle_id;
521
522 -- Header Tax Total
523 Cursor hdrtax_cur(p_chr_id In Number) Is
524 SELECT SUM(kln.tax_amount)
525 FROM okc_k_lines_b cle, oks_k_lines_b kln
526 WHERE cle.dnz_chr_id = p_chr_id
527 AND cle.id = kln.cle_id
528 AND cle.lse_id IN (7, 8, 9, 10, 11, 13, 35, 25, 46)
529 AND cle.date_cancelled IS NULL;
530
531 -- Topline Tax Total
532 Cursor toplinetax_cur(p_cle_id In Number) Is
533 SELECT SUM(tax_amount)
534 FROM okc_k_lines_b cle, oks_k_lines_b kln
535 WHERE cle.cle_id = p_cle_id
536 AND cle.id = kln.cle_id
537 AND cle.lse_id IN (7, 8, 9, 10, 11, 13, 35, 25, 46)
538 AND cle.date_cancelled IS NULL;
539
540 CURSOR Get_Header_details(p_chr_id In NUMBER) IS
541 SELECT id, object_version_number
542 FROM OKS_K_HEADERS_B
543 WHERE chr_id = p_chr_id;
544
545 CURSOR check_line_status(p_hdr_id IN NUMBER,p_line_id IN NUMBER ) IS
546 SELECT 'Y'
547 FROM OKC_K_LINES_B
548 WHERE dnz_chr_id = p_hdr_id
549 AND id = p_line_id
550 AND (date_terminated IS NULL OR date_terminated > TRUNC(SYSDATE))
551 AND date_cancelled IS NULL
552 AND sts_code NOT IN ('CANCELLED', 'TERMINATED') ;
553
554 l_api_name Varchar2(100) := 'Mass_Update_Price_prc';
555 i NUMBER;
556 l_return_status Varchar2(20);
557 l_msg_count Number;
558 l_msg_data Varchar2(2000);
559 l_api_version CONSTANT NUMBER := 1.0;
560 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
561 l_msg_index_out NUMBER;
562
563 l_mu_price_tbl OKS_MASS_UPD_PVT.mass_update_price_tbl;
564 l_authoring_org_id NUMBER;
565 l_inv_org_id NUMBER;
566 l_input_details OKS_QP_PKG.INPUT_DETAILS;
567 l_output_details OKS_QP_PKG.PRICE_DETAILS;
568 l_modif_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
569 l_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
570 l_clev_rec_in okc_contract_pub.clev_rec_type;
571 l_clev_rec_out okc_contract_pub.clev_rec_type;
572 l_k_det_rec oks_qp_int_pvt.k_details_rec;
573 l_khrv_tbl_type_in oks_contract_hdr_pub.khrv_tbl_type;
574 l_khrv_tbl_type_out oks_contract_hdr_pub.khrv_tbl_type;
575 l_kln_rec_in oks_contract_line_pub.klnv_rec_type;
576 l_kln_rec_out oks_contract_line_pub.klnv_rec_type;
577 l_get_hdr_details get_header_details%ROWTYPE;
578 l_old_price_negotiated NUMBER;
579 l_old_tax_amount NUMBER;
580 l_OKC_LINE CONSTANT VARCHAR2(4) := 'CLE';
581 l_OKS_LINE CONSTANT VARCHAR2(4) := 'KLE';
582 l_line_eligible VARCHAR2(1):='N';
583 l_EXC_ERROR EXCEPTION;
584 x_overall_status VARCHAR2(1);
585
586 TYPE err_mu_price_rec IS RECORD
587 (
588 line_number VARCHAR2(10),
589 err_msg VARCHAR2(1000)
590 );
591
592 Type err_mu_price is TABLE of err_mu_price_rec index by binary_integer;
593
594 err_mu_price_tbl err_mu_price;
595
596 l_err_idx NUMBER:=0;
597 l_success_count NUMBER;
598
599 begin
600 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
601 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
602 G_MODULE || l_api_name,
603 'Entered mass_update_price_prc');
604 END IF;
605
606 err_mu_price_tbl.DELETE;
607 x_return_status := OKC_API.G_RET_STS_SUCCESS;
608
609 l_mu_price_tbl := p_mass_update_price_tbl;
610
611 /*Fetch Org context details for the contract*/
612 OPEN header_details(l_mu_price_tbl(1).chr_id);
613 FETCH header_details
614 INTO l_authoring_org_id, l_inv_org_id;
615 CLOSE header_details;
616
617 /*Set org context*/
618 okc_context.set_okc_org_context(l_authoring_org_id, l_inv_org_id);
619
620 i := 1;
621
622 FOR i in l_mu_price_tbl.FIRST .. l_mu_price_tbl.LAST loop
623
624 begin
625 -- Make a save point, in case of error rollback
626 DBMS_TRANSACTION.SAVEPOINT('MASS_UPDATE_PRICE');
627
628 l_line_eligible :='N';
629 l_input_details.line_id := l_mu_price_tbl(i).line_id;
630 l_input_details.subline_id := l_mu_price_tbl(i).sub_line_id;
631 l_input_details.intent := 'OA';
632
633 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
634 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
635 G_MODULE || l_api_name,
636 ' l_input_details.line_id ' ||
637 l_input_details.line_id ||
638 ' l_input_details.subline_id ' ||
639 l_input_details.subline_id ||
640 ' l_input_details.intent ' ||
641 l_input_details.intent);
642 END IF;
643
644 FND_FILE.PUT_LINE(FND_FILE.Log,' l_mu_price_tbl(i).line_number ' ||l_mu_price_tbl(i).line_number||' l_input_details.intent ' ||l_input_details.intent);
645
646 ---check the eligibility of the line
647 open check_line_status(l_mu_price_tbl(i).chr_id,l_mu_price_tbl(i).sub_line_id ) ;
648 FETCH check_line_status INTO l_line_eligible;
649 CLOSE check_line_status;
650
651 IF Nvl(l_line_eligible,'N') <> 'Y' THEN
652
653 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
654 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
655 G_MODULE || l_api_name,
656 'line is not eligible for price change ');
657 END IF;
658
659 FND_FILE.PUT_LINE(FND_FILE.Log,' line is not eligible for price change and the line number is - ' ||l_mu_price_tbl(i).line_number);
660
661 l_err_idx := l_err_idx +1;
662 err_mu_price_tbl(l_err_idx).line_number := l_mu_price_tbl(i).line_number ;
663 err_mu_price_tbl(l_err_idx).err_msg := 'Sub line status is not eligible for price change';
664 x_return_status := 'E';
665 RAISE l_exc_error;
666 END IF;
667
668 ---before updating query for the old price negotiated value and old tax value
669 open cur_line(l_input_details.subline_id);
670 fetch cur_line
671 into l_old_price_negotiated, l_old_tax_amount;
672 close cur_line;
673
674 l_clev_rec_in.id := l_input_details.subline_id;
675 l_clev_rec_in.price_negotiated := l_mu_price_tbl(i).new_amount;
676 oks_qp_int_pvt.get_k_details(p_id => l_input_details.line_id,
677 p_type => l_okc_line,
678 x_k_det_rec => l_k_det_rec);
679 l_clev_rec_in.object_version_number := l_k_det_rec.object_version_number;
680
681 --update the new price amount
682 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
683 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
684 G_MODULE || l_api_name,
685 'before call to okc_contract_pub.update_contract_line ');
686 END IF;
687 okc_contract_pub.update_contract_line(p_api_version => l_api_version,
688 p_init_msg_list => l_init_msg_list,
689 x_return_status => l_return_status,
690 x_msg_count => l_msg_count,
691 x_msg_data => l_msg_data,
692 p_clev_rec => l_clev_rec_in,
693 x_clev_rec => l_clev_rec_out);
694
695 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
696 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
697 G_MODULE || l_api_name,
698 'after call to okc_contract_pub.update_contract_line ' ||
699 'l_return_status -' || l_return_status);
700 END IF;
701
702 FND_FILE.PUT_LINE(FND_FILE.Log,'after call to okc_contract_pub.update_contract_line AND l_return_status - ' ||l_return_status);
703
704 IF (l_return_status <> 'S') then
705 x_return_status := l_return_status;
706 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
707 l_msg_data => x_msg_data);
708 l_err_idx := l_err_idx +1;
709 err_mu_price_tbl(l_err_idx).line_number := l_mu_price_tbl(i).line_number ;
710 err_mu_price_tbl(l_err_idx).err_msg := x_msg_data ;
711 RAISE l_exc_error;
712 END IF;
713
714 ----call oks_qp_int_pvt to compute the new price
715 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
716 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
717 G_MODULE || l_api_name,
718 'before call to oks_qp_int_pvt.compute_Price ');
719 END IF;
720 oks_qp_int_pvt.compute_Price(p_api_version => 1.0,
721 p_init_msg_list => 'T',
722 p_detail_rec => l_input_details,
723 x_price_details => l_output_details,
724 x_modifier_details => l_modif_details,
725 x_price_break_details => l_pb_details,
726 x_return_status => l_return_status,
727 x_msg_count => l_msg_count,
728 x_msg_data => l_msg_data);
729
730 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
731 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
732 G_MODULE || l_api_name,
733 'after call to oks_qp_int_pvt.compute_Price ' ||
734 'l_return_status ' || l_return_status);
735 END IF;
736
737 FND_FILE.PUT_LINE(FND_FILE.Log,'after call to oks_qp_int_pvt.compute_Price AND l_return_status - ' ||l_return_status);
738
739 IF (l_return_status <> 'S') then
740 x_return_status := l_return_status;
741 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
742 l_msg_data => x_msg_data);
743 l_err_idx := l_err_idx +1;
744 err_mu_price_tbl(l_err_idx).line_number := l_mu_price_tbl(i).line_number ;
745 err_mu_price_tbl(l_err_idx).err_msg := x_msg_data ;
746
747 RAISE l_exc_error;
748 END IF;
749
750 ----call oks_bill_sch.Create_Bill_Sch_CP
751 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
752 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
753 G_MODULE || l_api_name,
754 'before call to oks_bill_sch.Create_Bill_Sch_CP ');
755 END IF;
756 ---- call create bill sch to refresh the level elements
757 oks_bill_sch.Create_Bill_Sch_CP(p_top_line_id => l_input_details.line_id,
758 p_cp_line_id => l_input_details.subline_id,
759 p_cp_new => 'N',
760 x_return_status => l_return_status,
761 x_msg_count => l_msg_count,
762 x_msg_data => l_msg_data);
763
764 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
765 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
766 G_MODULE || l_api_name,
767 ' after call to oks_bill_sch.Create_Bill_Sch_CP ' ||
768 'l_return_status ' || l_return_status);
769 END IF;
770
771 FND_FILE.PUT_LINE(FND_FILE.Log,' after call to oks_bill_sch.Create_Bill_Sch_CP AND l_return_status IS ' ||l_return_status);
772
773 IF (l_return_status <> 'S') then
774 x_return_status := l_return_status;
775 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
776 l_msg_data => x_msg_data);
777
778 l_err_idx := l_err_idx +1;
779 err_mu_price_tbl(l_err_idx).line_number := l_mu_price_tbl(i).line_number ;
780 err_mu_price_tbl(l_err_idx).err_msg := x_msg_data ;
781
782 RAISE l_exc_error;
783 END IF;
784
785 ---Update line tax and header tax
786
787 oks_qp_int_pvt.get_k_details(p_id => l_input_details.line_id,
788 p_type => l_oks_line,
789 x_k_det_rec => l_k_det_rec);
790 l_kln_rec_in.id := l_k_det_rec.id;
791 l_kln_rec_in.object_version_number := l_k_det_rec.object_version_number;
792
793 open toplinetax_cur(l_input_details.line_id);
794 fetch toplinetax_cur
795 into l_kln_rec_in.tax_amount;
796 close toplinetax_cur;
797
798 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
799 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
800 G_MODULE || l_api_name,
801 'before call to oks_contract_line_pub.update_line ');
802 END IF;
803
804 oks_contract_line_pub.update_line(p_api_version => l_api_version,
805 p_init_msg_list => l_init_msg_list,
806 x_return_status => l_return_status,
807 x_msg_count => l_msg_count,
808 x_msg_data => l_msg_data,
809 p_klnv_rec => l_kln_rec_in,
810 x_klnv_rec => l_kln_rec_out,
811 p_validate_yn => 'N');
812 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
813 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
814 G_MODULE || l_api_name,
815 ' after call to oks_contract_line_pub.update_line ' ||
816 'l_return_status ' || l_return_status);
817 END IF;
818
819 FND_FILE.PUT_LINE(FND_FILE.Log,' after call to oks_contract_line_pub.update_line AND l_return_status IS ' ||l_return_status);
820
821 IF (l_return_status <> 'S') then
822 x_return_status := l_return_status;
823 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
824 l_msg_data => x_msg_data);
825 l_err_idx := l_err_idx +1;
826 err_mu_price_tbl(l_err_idx).line_number := l_mu_price_tbl(i).line_number ;
827 err_mu_price_tbl(l_err_idx).err_msg := x_msg_data ;
828
829 RAISE l_exc_error;
830 END IF;
831
832 OPEN hdrtax_cur(l_mu_price_tbl(i).chr_id);
833 FETCH hdrtax_cur
834 INTO l_khrv_tbl_type_in(1) .tax_amount;
835 CLOSE hdrtax_cur;
836
837 -- Updating Header level tax Amount --
838 OPEN get_header_details(l_mu_price_tbl(i).chr_id);
839 FETCH get_header_details
840 INTO l_get_hdr_details;
841 CLOSE get_header_details;
842
843 l_khrv_tbl_type_in(1).id := l_get_hdr_details.id;
844 l_khrv_tbl_type_in(1).chr_id := l_mu_price_tbl(i).chr_id;
845 l_khrv_tbl_type_in(1).object_version_number := l_get_hdr_details.object_version_number;
846
847 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
849 G_MODULE || l_api_name,
850 'before call to oks_contract_hdr_pub.update_header ');
851 END IF;
852
853 oks_contract_hdr_pub.update_header(p_api_version => l_api_version,
854 p_init_msg_list => l_init_msg_list,
855 x_return_status => l_return_status,
856 x_msg_count => l_msg_count,
857 x_msg_data => l_msg_data,
858 p_khrv_tbl => l_khrv_tbl_type_in,
859 x_khrv_tbl => l_khrv_tbl_type_out,
860 p_validate_yn => 'N');
861
862 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
863 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
864 G_MODULE || l_api_name,
865 ' after call to oks_contract_hdr_pub.update_header ' ||
866 'l_return_status ' || l_return_status);
867 END IF;
868
869 FND_FILE.PUT_LINE(FND_FILE.Log,' after call to oks_contract_hdr_pub.update_header AND l_return_status IS ' ||l_return_status);
870
871 IF (l_return_status <> 'S') then
872 x_return_status := l_return_status;
873 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
874 l_msg_data => x_msg_data);
875 l_err_idx := l_err_idx +1;
876 err_mu_price_tbl(l_err_idx).line_number := l_mu_price_tbl(i).line_number;
877 err_mu_price_tbl(l_err_idx).err_msg := x_msg_data ;
878
879 RAISE l_exc_error;
880 END IF;
881
882 EXCEPTION
883 wHEN l_EXC_ERROR THEN
884 DBMS_TRANSACTION.rollback_savepoint('MASS_UPDATE_PRICE');
885 x_overall_status := 'W';
886 END;
887 end loop;
888
889 l_success_count := l_mu_price_tbl.Count - Nvl(err_mu_price_tbl.Count,0);
890
891 fnd_file.put_line (fnd_file.log,'Subtotal Change: Total Number of Successful Lines/Sublines: '||l_success_count);
892 fnd_file.put_line (fnd_file.output,'Subtotal Change: Total Number of Successful Lines/Sublines: '||l_success_count);
893
894 fnd_file.put_line (fnd_file.log,'Subtotal Change: Total Number of Rejected Lines/Sublines: '||err_mu_price_tbl.count);
895 fnd_file.put_line (fnd_file.output,'Subtotal Change: Total Number of Rejected Lines/Sublines: '||err_mu_price_tbl.count);
896
897
898 IF err_mu_price_tbl.Count > 0 THEN
899
900 fnd_file.put_line (fnd_file.output,' ');
901 fnd_file.put_line (fnd_file.output,'Error Records');
902 fnd_file.put_line (fnd_file.output,' ');
903
904 fnd_file.put_line (fnd_file.log,' ');
905 fnd_file.put_line (fnd_file.log,'Error Records');
906 fnd_file.put_line (fnd_file.log,' ');
907
908 fnd_file.put_line (fnd_file.log,'Line Number '||' - '|| 'Error Message ');
909 fnd_file.put_line (fnd_file.log,'*********** '||' - '|| '************************************** ');
910
911 fnd_file.put_line (fnd_file.output,'Line Number '||' - '|| 'Error Message ');
912 fnd_file.put_line (fnd_file.output,'*********** '||' - '|| '************************************** ');
913
914 FOR i in err_mu_price_tbl.FIRST .. err_mu_price_tbl.LAST LOOP
915
916 fnd_file.put_line (fnd_file.log,RPad(err_mu_price_tbl(i).line_number,11, ' ') ||' - '||
917 err_mu_price_tbl(i).err_msg );
918
919 fnd_file.put_line (fnd_file.output,RPad(err_mu_price_tbl(i).line_number,11, ' ') ||' - '||
920 err_mu_price_tbl(i).err_msg );
921
922 END LOOP;
923 END IF;
924
925 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
926 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
927 G_MODULE || l_api_name,
928 'Leaving mass_update_price_prc');
929 END IF;
930 IF x_overall_status IS NOT NULL THEN
931 x_return_status := x_overall_status;
932 END IF;
933 EXCEPTION
934
935 WHEN OTHERS THEN
936
937 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
938 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
939 G_MODULE || l_api_name,
940 'Leaving mass_update_price_prc because of EXCEPTION: ' ||
941 sqlerrm);
942 END IF;
943
944 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
945 --fnd_file.put_line (fnd_file.log,'Unexpected error in mass_update_price_prc: '||x_return_status||' sqlerrm: '||sqlerrm);
946
947 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
948 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
949 END IF;
950
951 end mass_update_price_prc;
952
953 procedure mass_update_price_wrapper(p_contract_id IN VARCHAR2,
954 x_return_status out nocopy varchar2) IS
955
956 --x_return_status VARCHAR2(1);
957
958 l_mass_update_price_tbl oks_mass_upd_pvt.mass_update_price_tbl;
959 x_msg_data VARCHAR2(1000);
960 x_msg_count NUMBER;
961
962 begin
963
964 SELECT cle_id, id, dnz_chr_id, lse_id,line_number,price_negotiated BULK COLLECT
965 INTO l_mass_update_price_tbl
966 FROM oks_sublines_temp
967 WHERE price_change_flag = 'Y'
968 AND dnz_chr_id = To_Number(p_contract_id);
969
970 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
971 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
972 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
973 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, 'Following are the Lines Processed for Subtotal Change ') ;
974 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '*************************************************** ') ;
975 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
976 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
977
978 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
979 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
980 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
981 FND_FILE.PUT_LINE( FND_FILE.log, 'Start of Subtotal Change Processing') ;
982 FND_FILE.PUT_LINE( FND_FILE.log, '*********************************** ') ;
983 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
984 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
985
986 IF l_mass_update_price_tbl.Count > 0 THEN
987 fnd_file.put_line (fnd_file.Log, 'Subtotal Change: Total number of Lines/Sublines to process: '||l_mass_update_price_tbl.Count);
988 fnd_file.put_line (fnd_file.output, 'Subtotal Change: Total number of Lines/Sublines to process: '||l_mass_update_price_tbl.Count);
989 mass_update_price_prc(l_mass_update_price_tbl,
990 x_return_status,
991 x_msg_data,
992 x_msg_count);
993
994 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
995 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
996 FND_FILE.PUT_LINE( FND_FILE.log, 'End of Subtotal Change Processing') ;
997 FND_FILE.PUT_LINE( FND_FILE.log, '********************************* ') ;
998 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
999 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1000
1001 ELSE
1002 fnd_file.put_line (fnd_file.Log, 'Subtotal Change: No records to process');
1003 fnd_file.put_line (fnd_file.output, 'Subtotal Change: No records to process');
1004 x_return_status := 'S';
1005 END IF;
1006
1007
1008 end;
1009
1010 procedure mass_update_status_wrapper(p_contract_id IN VARCHAR2,
1011 x_return_status out nocopy varchar2) IS
1012 --x_return_status VARCHAR2(1);
1013
1014 l_mass_update_status_tbl oks_mass_upd_pvt.mass_update_status_tbl;
1015 x_msg_data VARCHAR2(1000);
1016 x_msg_count NUMBER;
1017 l_count NUMBER := 0;
1018
1019 BEGIN
1020
1021 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
1022 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
1023 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
1024 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, 'Following are the Lines Processed for Status Change ') ;
1025 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '*************************************************** ') ;
1026 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
1027 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
1028
1029
1030 /*log*/
1031 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1032 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1033 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1034 FND_FILE.PUT_LINE( FND_FILE.log, 'Start of Status Change Processing') ;
1035 FND_FILE.PUT_LINE( FND_FILE.log, '********************************* ') ;
1036 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1037 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1038
1039
1040 /*log*/
1041
1042 FOR i IN (SELECT line_number,
1043 status_err_message
1044 FROM oks_lines_temp
1045 WHERE (status_change_flag = 'Y' AND status_error_flag = 'Y')
1046 AND dnz_chr_id = To_Number(p_contract_id)
1047 UNION ALL
1048 SELECT line_number,
1049 status_err_message
1050 FROM oks_sublines_temp
1051 WHERE (status_change_flag = 'Y' AND status_error_flag = 'Y')
1052 AND dnz_chr_id = to_number(p_contract_id)) LOOP
1053
1054 IF l_count = 0 THEN
1055 FND_FILE.PUT_LINE( FND_FILE.output, 'Records rejected during validation') ;
1056 FND_FILE.PUT_LINE( FND_FILE.output, ' ') ;
1057 END IF;
1058 fnd_file.put_line (fnd_file.Log,'Line Number: ' || I.line_number || ' Error Message is: ' || i.status_err_message);
1059 fnd_file.put_line (fnd_file.output,'Line Number: ' || I.line_number || ' Error Message is: ' || i.status_err_message);
1060 l_count := l_count +1;
1061
1062 END LOOP;
1063 fnd_file.put_line (fnd_file.Log, 'Status Change: Total Number of Rejected lines during validation: ' || l_count);
1064
1065 FND_FILE.PUT_LINE( FND_FILE.output, ' ') ;
1066
1067
1068 SELECT * BULK COLLECT
1069 INTO l_mass_update_status_tbl
1070 FROM (SELECT ID line_id,
1071 To_Number(NULL) subline_id,
1072 dnz_chr_id,
1073 lse_id,
1074 line_number,
1075 reason_code,
1076 status,
1077 new_status,
1078 comments
1079 FROM oks_lines_temp
1080 WHERE (status_change_flag = 'Y' AND status_error_flag = 'N')
1081 AND dnz_chr_id = To_Number(p_contract_id)
1082 UNION ALL
1083 SELECT cle_id line_id,
1084 id subline_id,
1085 dnz_chr_id,
1086 lse_id,
1087 line_number,
1088 reason_code,
1089 status,
1090 new_status,
1091 comments
1092 FROM oks_sublines_temp
1093 WHERE (status_change_flag = 'Y' AND status_error_flag = 'N')
1094 AND dnz_chr_id = to_number(p_contract_id));
1095
1096 IF l_mass_update_status_tbl.Count > 0 THEN
1097 IF l_count <> 0 THEN
1098 fnd_file.put_line (fnd_file.output, 'Status Change: Total Number of Rejected Lines during validation: ' || l_count);
1099 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ') ;
1100 END IF;
1101
1102 fnd_file.put_line (fnd_file.Log, 'Status Change: Total Number of Lines/Sublines to process: '||l_mass_update_status_tbl.count);
1103 fnd_file.put_line (fnd_file.output, 'Status Change: Total Number of Lines/Sublines to process: '||l_mass_update_status_tbl.count);
1104
1105 mass_update_status_prc(l_mass_update_status_tbl,
1106 x_return_status,
1107 x_msg_data,
1108 x_msg_count);
1109
1110 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1111 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1112 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1113 FND_FILE.PUT_LINE( FND_FILE.log, 'End of Status Change Processing') ;
1114 FND_FILE.PUT_LINE( FND_FILE.log, '*************************************************** ') ;
1115 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1116 FND_FILE.PUT_LINE( FND_FILE.log, ' ') ;
1117
1118 ELSE
1119 fnd_file.put_line (fnd_file.Log, 'Status Change: No records to process');
1120 IF l_count <> 0 THEN
1121 fnd_file.put_line (fnd_file.output, 'Status Change: Total Number of Rejected Lines during validation: ' || l_count);
1122 END IF;
1123 fnd_file.put_line (fnd_file.output, 'Status Change: No records to process');
1124 x_return_status := 'S';
1125 END IF;
1126
1127 END;
1128
1129 procedure mass_update_eff_wrapper(p_contract_id IN VARCHAR2,
1130 x_return_status out nocopy varchar2) IS
1131 --x_return_status VARCHAR2(1);
1132
1133 l_mass_update_eff_tbl oks_mass_upd_pvt.mass_update_effectivity_tbl;
1134 --l_mass_update_eff_tbl_err oks_mass_upd_pvt.mass_update_effectivity_tbl;
1135 l_count NUMBER := 0;
1136 BEGIN
1137
1138 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1139 ' ');
1140 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1141 ' ');
1142 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1143 ' ');
1144 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1145 'Following are the Lines Processed for Effectivity Change ');
1146 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1147 '******************************************************** ');
1148 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1149 ' ');
1150 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1151 ' ');
1152
1153 /*log*/
1154
1155 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1156 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1157 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1158 FND_FILE.PUT_LINE(FND_FILE.log, 'Start of Effectivity Change Processing');
1159 FND_FILE.PUT_LINE(FND_FILE.log,
1160 '************************************** ');
1161 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1162 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1163
1164 /*log*/
1165
1166 FOR I IN (SELECT line_number,
1167 DECODE(start_dt_error_flag,
1168 'Y',
1169 'Start Date Error: ' || st_dt_err_message,
1170 '') st_dt_err,
1171 DECODE(end_dt_error_flag,
1172 'Y',
1173 'End Date Error: ' || end_dt_err_message,
1174 '') end_dt_err
1175 FROM oks_lines_temp
1176 WHERE ((start_dt_eff_change_flag = 'Y' AND
1177 start_dt_error_flag = 'Y') OR (end_dt_eff_change_flag = 'Y' AND
1178 end_dt_error_flag = 'Y'))
1179 AND dnz_chr_id = to_number(p_contract_id)
1180 UNION ALL
1181 SELECT line_number,
1182 DECODE(start_dt_error_flag,
1183 'Y',
1184 'Start Date Error: ' || st_dt_err_message,
1185 '') st_dt_err,
1186 DECODE(end_dt_error_flag,
1187 'Y',
1188 'End Date Error: ' || end_dt_err_message,
1189 '') end_dt_err
1190 FROM oks_sublines_temp
1191 WHERE ((start_dt_eff_change_flag = 'Y' AND
1192 start_dt_error_flag = 'Y') OR (end_dt_eff_change_flag = 'Y' AND
1193 end_dt_error_flag = 'Y'))
1194 AND dnz_chr_id = to_number(p_contract_id)) LOOP
1195
1196 IF l_count = 0 THEN
1197 FND_FILE.PUT_LINE(FND_FILE.output, 'Records rejected during validation');
1198 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1199 END IF;
1200 fnd_file.put_line(fnd_file.Log,
1201 'Line Number: ' || I.line_number ||
1202 ' Error Message is: ' || i.st_dt_err || ' ' ||
1203 i.end_dt_err);
1204 fnd_file.put_line(fnd_file.output,
1205 'Line Number: ' || I.line_number ||
1206 ' Error Message is: ' || i.st_dt_err || ' ' ||
1207 i.end_dt_err);
1208 l_count := l_count + 1;
1209 END LOOP;
1210
1211 IF l_count > 0 THEN
1212 fnd_file.put_line(fnd_file.Log,
1213 'Effectivity Change: Total Number of Rejected lines during validation: ' ||
1214 l_count);
1215 END IF;
1216
1217 SELECT * BULK COLLECT
1218 INTO l_mass_update_eff_tbl
1219 FROM (SELECT ID line_id,
1220 To_Number(NULL) subline_id,
1221 dnz_chr_id,
1222 lse_id,
1223 line_number,
1224 start_date,
1225 end_date
1226 FROM oks_lines_temp
1227 WHERE ((start_dt_eff_change_flag = 'Y' AND
1228 start_dt_error_flag = 'N') OR
1229 (end_dt_eff_change_flag = 'Y' AND end_dt_error_flag = 'N'))
1230 AND dnz_chr_id = to_number(p_contract_id)
1231 UNION ALL
1232 SELECT cle_id line_id,
1233 id subline_id,
1234 dnz_chr_id,
1235 lse_id,
1236 line_number,
1237 start_date,
1238 end_date
1239 FROM oks_sublines_temp lin
1240 WHERE ((start_dt_eff_change_flag = 'Y' AND
1241 start_dt_error_flag = 'N') OR
1242 (end_dt_eff_change_flag = 'Y' AND end_dt_error_flag = 'N'))
1243 AND dnz_chr_id = to_number(p_contract_id));
1244
1245 IF l_mass_update_eff_tbl.Count > 0 THEN
1246 IF l_count <> 0 THEN
1247 FND_FILE.PUT_LINE(FND_FILE.output, ' ');
1248 fnd_file.put_line(fnd_file.output,
1249 'Effectivity Change: Total Number of Rejected Lines during validation: ' ||
1250 l_count);
1251 FND_FILE.PUT_LINE(FND_FILE.output, ' ');
1252 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1253 ' ');
1254 END IF;
1255 mass_update_effectivity_prc(l_mass_update_eff_tbl, x_return_status);
1256
1257 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1258 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1259 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1260 FND_FILE.PUT_LINE(FND_FILE.log, 'End of Effectivity Change Processing');
1261 FND_FILE.PUT_LINE(FND_FILE.log,
1262 '************************************** ');
1263 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1264 FND_FILE.PUT_LINE(FND_FILE.log, ' ');
1265
1266 ELSE
1267 IF l_count <> 0 THEN
1268 fnd_file.put_line(fnd_file.output,
1269 'Effectivity Change: Total Number of Rejected Lines during validation: ' ||
1270 l_count);
1271 END IF;
1272 fnd_file.put_line(fnd_file.Log,
1273 'Effectivity Change: No records to process');
1274 fnd_file.put_line(fnd_file.output,
1275 'Effectivity Change: No records to process');
1276 x_return_status := 'S';
1277 END IF;
1278 END;
1279
1280
1281 procedure mass_update_status_prc(p_mass_update_status_tbl in mass_update_status_tbl,
1282 x_return_status out nocopy VARCHAR2,
1283 x_msg_data OUT NOCOPY VARCHAR2,
1284 x_msg_count OUT NOCOPY NUMBER) IS
1285 --PRAGMA autonomous_transaction;
1286
1287 l_return_status varchar2(20);
1288 l_authoring_org_id NUMBER;
1289 l_inv_org_id NUMBER;
1290 l_msg_count NUMBER;
1291 l_msg_data VARCHAR2(100);
1292 l_comments VARCHAR2(2000) := 'TEST';
1293 l_new_ste_code varchar2(100);
1294 l_old_ste_code varchar2(100);
1295 l_api_name Varchar2(100) := 'Mass_Update_Status_prc';
1296 i NUMBER;
1297 p_lines_tbl OKS_MASS_UPD_PVT.mass_update_status_tbl;
1298 x_overall_status VARCHAR2(1);
1299 l_error_count NUMBER := 0;
1300 l_EXC_ERROR EXCEPTION;
1301
1302
1303 CURSOR header_details(p_contract_id NUMBER) IS
1304 SELECT authoring_org_id, inv_organization_id
1305 FROM okc_k_headers_all_b
1306 WHERE id = p_contract_id;
1307
1308 Cursor ste_code_csr(p_sts_code in varchar2) is
1309 select ste_code from okc_statuses_b where code = p_sts_code;
1310
1311
1312 TYPE err_mu_status_rec IS RECORD
1313 (
1314 line_number VARCHAR2(10),
1315 err_msg VARCHAR2(1000)
1316 );
1317
1318 Type err_mu_status is TABLE of err_mu_status_rec index by binary_integer;
1319
1320 err_mu_status_tbl err_mu_status;
1321
1322 l_success_count NUMBER;
1323 BEGIN
1324 x_return_status :=OKC_API.G_RET_STS_SUCCESS;
1325
1326 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1327 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1328 G_MODULE || l_api_name,
1329 'Entered Mass_Update_Status_Prc');
1330 END IF;
1331
1332 /*Set the apps user_id which is useful to update the who columns in OKS tables.
1333 FND_GLOBAL.apps_initialize(p_user_id,p_resp_id,p_pgm_appl_id); */
1334
1335 p_lines_tbl := p_mass_update_status_tbl;
1336
1337 /*Fetch Org context details for the contract*/
1338 OPEN header_details(p_lines_tbl(1).chr_id);
1339 FETCH header_details
1340 INTO l_authoring_org_id, l_inv_org_id;
1341 CLOSE header_details;
1342
1343 /*Set org context*/
1344 okc_context.set_okc_org_context(l_authoring_org_id, l_inv_org_id);
1345
1346 i := 1;
1347
1348 FOR i in p_lines_tbl.FIRST .. p_lines_tbl.LAST LOOP
1349 BEGIN
1350 -- Make a save point, in case of error rollback
1351 DBMS_TRANSACTION.SAVEPOINT('MASS_UPDATE_STATUS');
1352
1353 OPEN ste_code_csr(p_lines_tbl(i).old_sts_code);
1354 FETCH ste_code_csr
1355 INTO l_old_ste_code;
1356 CLOSE ste_code_csr;
1357
1358 open ste_code_csr(p_lines_tbl(i).new_sts_code);
1359 fetch ste_code_csr
1360 INTO l_new_ste_code;
1361 close ste_code_csr;
1362
1363 IF p_lines_tbl(i).sub_line_id is not null then
1364
1365 p_lines_tbl(i).line_id := p_lines_tbl(i).sub_line_id;
1366
1367 END IF;
1368
1369 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1370 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1371 G_MODULE || l_api_name,
1372 'Before Validate_Status' || 'Line Id is -' ||
1373 p_lines_tbl(i)
1374 .line_id || 'Old_Ste_Code -' || l_old_ste_code ||
1375 'New_ste_Code -' || l_new_ste_code);
1376 END IF;
1377
1378 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Before OKS_CHANGE_STATUS_PVT.VALIDATE_STATUS for line_number - '||p_lines_tbl(i).line_number||' Old_Ste_Code - is '||l_old_ste_code||' New_ste_Code - '||l_new_ste_code);
1379
1380 OKS_CHANGE_STATUS_PVT.VALIDATE_STATUS(x_return_status => l_return_status,
1381 x_msg_count => l_msg_count,
1382 x_msg_data => l_msg_data,
1383 p_id => p_lines_tbl(i)
1384 .chr_id,
1385 p_new_ste_code => l_new_ste_code,
1386 p_old_ste_code => l_old_ste_code,
1387 p_new_sts_code => p_lines_tbl(i)
1388 .new_sts_code,
1389 p_old_sts_code => p_lines_tbl(i)
1390 .old_sts_code,
1391 p_cle_id => p_lines_tbl(i)
1392 .line_id,
1393 p_validate_status => 'Y');
1394
1395 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1396 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1397 G_MODULE || l_api_name,
1398 'After Validate_Status' || 'l_return_status -' ||
1399 l_return_status);
1400 END IF;
1401
1402 FND_FILE.PUT_LINE(FND_FILE.Log,'After Validate_Status - l_return_status - '||l_return_status);
1403
1404 IF (l_return_status NOT IN ('S','W')) then
1405 x_return_status := l_return_status;
1406 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
1407 l_msg_data => x_msg_data);
1408 l_error_count := l_error_count + 1;
1409 err_mu_status_tbl(l_error_count).line_number := p_lines_tbl(i).line_number;
1410 err_mu_status_tbl(l_error_count).err_msg := x_msg_data ;
1411
1412 END IF;
1413
1414 IF l_return_status IN ('S','W') THEN
1415
1416 OKS_CHANGE_STATUS_PVT.UPDATE_LINE_STATUS(x_return_status => l_return_status,
1417 x_msg_data => l_msg_data,
1418 x_msg_count => l_msg_count,
1419 p_init_msg_list => 'T',
1420 p_id => p_lines_tbl(i)
1421 .chr_id,
1422 p_cle_id => p_lines_tbl(i)
1423 .line_id,
1424 p_new_sts_code => p_lines_tbl(i)
1425 .new_sts_code,
1426 p_canc_reason_code => p_lines_tbl(i)
1427 .canc_reason_code,
1428 p_old_sts_code => p_lines_tbl(i)
1429 .old_sts_code,
1430 p_old_ste_code => l_old_ste_code,
1431 p_new_ste_code => l_new_ste_code,
1432 p_term_cancel_source => 'MANUAL',
1433 p_date_cancelled => sysdate,
1434 p_comments => p_lines_tbl(i)
1435 .comments,
1436 p_validate_status => 'N');
1437
1438 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1439 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1440 G_MODULE || l_api_name,
1441 'After Update_Line_Status' || 'l_return_status -' ||
1442 l_return_status);
1443 END IF;
1444
1445 FND_FILE.PUT_LINE(FND_FILE.LOG,'After Update_Line_Status - l_return_status is ' ||l_return_status);
1446
1447 IF (l_return_status <> 'S') then
1448 x_return_status := l_return_status;
1449 x_overall_status := l_return_status;
1450 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
1451 l_msg_data => x_msg_data);
1452 l_error_count := l_error_count + 1;
1453 err_mu_status_tbl(l_error_count).line_number := p_lines_tbl(i).line_number;
1454 err_mu_status_tbl(l_error_count).err_msg := x_msg_data ;
1455 RAISE l_exc_error;
1456
1457 END IF;
1458
1459 END IF;
1460
1461 EXCEPTION
1462 WHEN l_EXC_ERROR THEN
1463 fnd_file.put_line (fnd_file.log,'Exception raised for Line Number: '||err_mu_status_tbl(l_error_count).line_number ||
1464 'Error Msg : '||err_mu_status_tbl(l_error_count).err_msg );
1465 DBMS_TRANSACTION.rollback_savepoint('MASS_UPDATE_STATUS');
1466 x_overall_status := 'W';
1467 END;
1468
1469 END LOOP;
1470
1471 --COMMIT;
1472
1473 l_success_count := p_mass_update_status_tbl.Count - Nvl(err_mu_status_tbl.Count,0);
1474
1475 fnd_file.put_line (fnd_file.log,'Status Change: Total Number of Successful Lines/Sublines: '||l_success_count);
1476 fnd_file.put_line (fnd_file.output,'Status Change: Total Number of Successful Lines/Sublines: '||l_success_count);
1477
1478 fnd_file.put_line (fnd_file.log,'Status Change: Total Number of Rejected Lines/Sublines: '||l_error_count);
1479 fnd_file.put_line (fnd_file.output,'Status Change: Total Number of Rejected Lines/Sublines: '||l_error_count);
1480
1481 fnd_file.put_line (fnd_file.output,' ');
1482
1483
1484 IF err_mu_status_tbl.Count > 0 THEN
1485
1486 fnd_file.put_line (fnd_file.output,'Status Change: Error Records');
1487 fnd_file.put_line (fnd_file.output,' ');
1488
1489 fnd_file.put_line (fnd_file.log,'Status Change: Error Records');
1490 fnd_file.put_line (fnd_file.log,' ');
1491
1492 fnd_file.put_line (fnd_file.log,'Line Number '||' - '|| 'Error Message ');
1493 fnd_file.put_line (fnd_file.log,'*********** '||' - '|| '************************************** ');
1494
1495 fnd_file.put_line (fnd_file.output,'Line Number '||' - '|| 'Error Message ');
1496 fnd_file.put_line (fnd_file.output,'*********** '||' - '|| '************************************** ');
1497
1498 FOR i in err_mu_status_tbl.FIRST .. err_mu_status_tbl.LAST loop
1499
1500 fnd_file.put_line (fnd_file.log,RPad(err_mu_status_tbl(i).line_number,11, ' ') ||' - '||
1501 err_mu_status_tbl(i).err_msg );
1502
1503 fnd_file.put_line (fnd_file.output,RPad(err_mu_status_tbl(i).line_number,11, ' ') ||' - '||
1504 err_mu_status_tbl(i).err_msg );
1505
1506 END LOOP;
1507 END IF;
1508
1509
1510
1511 IF x_overall_status IS NOT NULL THEN
1512 x_return_status := x_overall_status;
1513 END IF;
1514
1515 FND_FILE.PUT_LINE(FND_FILE.Log,'Return Status in Mass_Update_Status_Prc is '||x_return_status);
1516
1517 EXCEPTION
1518
1519 WHEN OTHERS THEN
1520
1521 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1522 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1523 G_MODULE || l_api_name,
1524 'Leaving mass_update_status_prc because of EXCEPTION: ' ||
1525 sqlerrm);
1526 END IF;
1527
1528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1529 --fnd_file.put_line (fnd_file.log,'Unexpected error in mass_update_status_prc: '||x_return_status||' sqlerrm: '||sqlerrm);
1530
1531 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1532 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1533 END IF;
1534
1535 END;
1536
1537 PROCEDURE validate_date(p_api_version IN NUMBER,
1538 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1539 p_hdr_id IN NUMBER,
1540 p_top_line_id IN NUMBER,
1541 p_sub_line_id IN NUMBER,
1542 x_return_status OUT NOCOPY VARCHAR2,
1543 x_msg_count OUT NOCOPY NUMBER,
1544 x_msg_data OUT NOCOPY VARCHAR2,
1545 x_flag OUT NOCOPY BOOLEAN) IS
1546
1547 /******************************************
1548 Note 1: For header to top lines
1549 p_hdr_id = Header id .
1550 p_top_line_id = Top line id.
1551 p_sub_line_id = null
1552
1553 Note 2: For top line to sub lines
1554 p_hdr_id = null.
1555 p_top_line_id = Top line id.
1556 p_sub_line_id = Sub line id.
1557 *******************************************/
1558
1559 CURSOR L_GET_TOP_LINE_DATE_CSR(P_TOP_LINE_ID IN NUMBER) IS
1560 SELECT TRUNC(MIN(BCL.DATE_BILLED_FROM)) DATE_BILLED_FROM,
1561 TRUNC(MAX(BCL.DATE_BILLED_TO)) DATE_BILLED_TO
1562 FROM OKS_BILL_CONT_LINES BCL
1563 WHERE BCL.CLE_ID = P_TOP_LINE_ID;
1564
1565 L_GET_TOP_LINE_DATE_REC L_GET_TOP_LINE_DATE_CSR%ROWTYPE;
1566
1567 CURSOR L_GET_HDR_DATES_CSR(P_HDR_ID IN NUMBER) IS
1568 SELECT TRUNC(START_DATE) START_DATE, TRUNC(END_DATE) END_DATE
1569 FROM okc_k_headers_all_b
1570 WHERE ID = P_HDR_ID;
1571
1572 L_GET_HDR_DATES_REC L_GET_HDR_DATES_CSR%ROWTYPE;
1573
1574 CURSOR L_GET_SUB_LINE_DATE_CSR(P_SUB_LINE_ID IN NUMBER) IS
1575 SELECT TRUNC(MIN(BSL.DATE_BILLED_FROM)) DATE_BILLED_FROM,
1576 TRUNC(MAX(BSL.DATE_BILLED_TO)) DATE_BILLED_TO
1577 FROM OKS_BILL_SUB_LINES BSL
1578 WHERE BSL.CLE_ID = P_SUB_LINE_ID;
1579
1580 L_GET_SUB_LINE_DATE_REC L_GET_SUB_LINE_DATE_CSR%ROWTYPE;
1581
1582 CURSOR L_GET_TOP_LINE_DATES_CSR(P_TOP_LINE_ID IN NUMBER) IS
1583 SELECT TRUNC(START_DATE) START_DATE, TRUNC(END_DATE) END_DATE
1584 FROM OKC_K_LINES_B
1585 WHERE ID = P_TOP_LINE_ID;
1586
1587 L_GET_TOP_LINE_DATES_REC L_GET_TOP_LINE_DATES_CSR%ROWTYPE;
1588
1589 BEGIN
1590 x_flag := TRUE;
1591 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1592 -- Start of if for Header to Top line validation.
1593
1594 If (p_hdr_id is not null and p_hdr_id > 0) and
1595 (p_top_line_id is not null and p_top_line_id > 0) and
1596 (p_sub_line_id is null) then
1597 -- SQL call to get the max and min top line billing dates
1598 OPEN L_GET_TOP_LINE_DATE_CSR(p_top_line_id);
1599 FETCH L_GET_TOP_LINE_DATE_CSR
1600 INTO L_GET_TOP_LINE_DATE_REC;
1601 CLOSE L_GET_TOP_LINE_DATE_CSR;
1602
1603 -- SQL call to get header start date and end date
1604 OPEN L_GET_HDR_DATES_CSR(P_HDR_ID);
1605 FETCH L_GET_HDR_DATES_CSR
1606 INTO L_GET_HDR_DATES_REC;
1607 CLOSE L_GET_HDR_DATES_CSR;
1608
1609 -- If billed, and the start date of hdr and top line dosen't match then
1610 --return false.
1611 IF L_GET_TOP_LINE_DATE_REC.DATE_BILLED_FROM IS NOT NULL THEN
1612 IF L_GET_TOP_LINE_DATE_REC.DATE_BILLED_FROM <>
1613 L_GET_HDR_DATES_REC.START_DATE THEN
1614 X_FLAG := FALSE;
1615 RETURN;
1616 END IF;
1617 END IF;
1618
1619 -- If billed, and the end date of hdr < max bill to date of top line
1620 --then return false.
1621 IF L_GET_TOP_LINE_DATE_REC.DATE_BILLED_TO IS NOT NULL THEN
1622 IF L_GET_TOP_LINE_DATE_REC.DATE_BILLED_TO >
1623 L_GET_HDR_DATES_REC.END_DATE THEN
1624 X_FLAG := FALSE;
1625 RETURN;
1626 END IF;
1627 END IF;
1628 RETURN;
1629 End If;
1630
1631 -- Start of if for Top line to sub line validation.
1632 If (p_top_line_id is not null and p_top_line_id > 0) and
1633 (p_sub_line_id is not null and p_sub_line_id > 0) and
1634 (p_hdr_id is null) then
1635
1636 -- SQL call to get the max and min sub line billing dates
1637 OPEN L_GET_SUB_LINE_DATE_CSR(P_SUB_LINE_ID);
1638 FETCH L_GET_SUB_LINE_DATE_CSR
1639 INTO L_GET_SUB_LINE_DATE_REC;
1640 CLOSE L_GET_SUB_LINE_DATE_CSR;
1641
1642 -- SQL call to get top line start date and end date
1643 OPEN L_GET_TOP_LINE_DATES_CSR(P_TOP_LINE_ID);
1644 FETCH L_GET_TOP_LINE_DATES_CSR
1645 INTO L_GET_TOP_LINE_DATES_REC;
1646 CLOSE L_GET_TOP_LINE_DATES_CSR;
1647
1648 -- If billed, and the start date of top and sub line dosen't match then
1649 --return false.
1650
1651 IF L_GET_SUB_LINE_DATE_REC.DATE_BILLED_FROM IS NOT NULL THEN
1652 IF L_GET_SUB_LINE_DATE_REC.DATE_BILLED_FROM <>
1653 L_GET_TOP_LINE_DATES_REC.START_DATE THEN
1654 X_FLAG := FALSE;
1655
1656 RETURN;
1657 END IF;
1658 END IF;
1659
1660 -- If billed and the end date of top line < max bill to date of sub line
1661 --then return false.
1662 IF L_GET_TOP_LINE_DATE_REC.DATE_BILLED_TO IS NOT NULL THEN
1663 IF L_GET_SUB_LINE_DATE_REC.DATE_BILLED_TO >
1664 L_GET_TOP_LINE_DATES_REC.END_DATE THEN
1665 X_FLAG := FALSE;
1666
1667 RETURN;
1668 END IF;
1669 END IF;
1670 RETURN;
1671 END IF;
1672
1673 EXCEPTION
1674 WHEN OTHERS THEN
1675
1676 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1677 OKC_API.set_message('OKS',
1678 'OKC_CONTRACTS_UNEXP_ERROR',
1679 'SQLcode',
1680 SQLCODE,
1681 'SQLerrm',
1682 SQLERRM);
1683
1684 END;
1685
1686 PROCEDURE check_new_line_effectivity(p_chr_id IN NUMBER,
1687 p_line_id IN NUMBER,
1688 p_line_number IN VARCHAR2,
1689 p_start_date IN DATE,
1690 p_end_date IN DATE,
1691 exception_yn OUT NOCOPY VARCHAR2) IS
1692 l_k_start_date DATE;
1693 l_k_end_date DATE;
1694 l_api_name VARCHAR2(150) := 'oks_mass_upd_pvt.check_new_line_effectivity';
1695 --l_err_mu_eff_tbl err_mu_eff_tbl;
1696 BEGIN
1697 exception_yn := 'N'; -- Defaulting value
1698 fnd_file.put_line (fnd_file.log, l_api_name || ', Checking Contract Line Effectivity for line number: ' || p_line_number);
1699 SELECT start_date, end_date
1700 INTO l_k_start_date, l_k_end_date
1701 FROM okc_k_headers_all_b
1702 where id = p_chr_id;
1703
1704 IF (p_start_date < l_k_start_date OR p_start_date > l_k_end_date) THEN
1705 UPDATE oks_lines_temp
1706 SET start_dt_error_flag = 'Y',
1707 st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message ||'- Line Start Date is out of Contract''s Effectivity, line_number: ' || p_line_number,
1708 'Line Start Date is out of Contract''s Effectivity, line number: ' || p_line_number)
1709 WHERE id = p_line_id;
1710
1711 exception_yn := 'Y';
1712 --RETURN;
1713 END IF;
1714
1715 IF (p_end_date < l_k_start_date OR p_end_date > l_k_end_date) THEN
1716 UPDATE oks_lines_temp
1717 SET end_dt_error_flag = 'Y',
1718 end_dt_err_message = Nvl2(end_dt_err_message, end_dt_err_message || ' - Line End Date is out of Contract''s Effectivity, line_number: '|| p_line_number,
1719 'Line End Date is out of Contract''s Effectivity, line number: '|| p_line_number)
1720 WHERE id = p_line_id;
1721
1722 exception_yn := 'Y';
1723 END IF;
1724
1725 IF exception_yn = 'Y' THEN
1726 fnd_file.put_line (fnd_file.log, l_api_name || ', Line Start/End Date is out of Contract''s Effectivity, p_line_number: ' || p_line_number);
1727 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_line_number;
1728 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Line Start/End Date is out of Contract''s Effectivity';
1729 l_err_eff_idx := l_err_eff_idx +1;
1730 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1731 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1732 G_MODULE || l_api_name,
1733 'Line Start/End Date is out of Contract''s Effectivity, line_number: ' || p_line_number);
1734 END IF;
1735 RETURN;
1736 END IF;
1737
1738 EXCEPTION
1739 when OTHERS then
1740 exception_yn := 'Y';
1741 fnd_file.put_line (fnd_file.log, l_api_name ||
1742 ' Error while validating data in check_new_line_effectivity for line_number: ' || p_line_number);
1743 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1744 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1745 l_api_name,
1746 'Error while fetching data for check_new_line_effectivity for line_number: ' || p_line_number);
1747 END IF;
1748 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_line_number;
1749 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Error while fetching data for check_new_line_effectivity for line, Error: ' || SQLERRM;
1750 l_err_eff_idx := l_err_eff_idx +1;
1751 return;
1752 END check_new_line_effectivity;
1753
1754 PROCEDURE check_new_subline_effectivity(p_chr_id IN NUMBER,
1755 p_line_id IN NUMBER,
1756 p_subline_id IN NUMBER,
1757 p_line_number IN VARCHAR2,
1758 p_start_date IN DATE,
1759 p_end_date IN DATE,
1760 exception_yn OUT NOCOPY VARCHAR2) IS
1761 l_k_start_date DATE;
1762 l_k_end_date DATE;
1763 l_kl_start_date DATE;
1764 l_kl_end_date DATE;
1765 l_chr_id NUMBER;
1766 l_api_name VARCHAR2(150) := 'oks_mass_upd_pvt.check_new_subline_effectivity';
1767 --l_err_mu_eff_tbl err_mu_eff_tbl;
1768
1769 BEGIN
1770 exception_yn := 'N'; -- Defaulting value
1771 fnd_file.put_line (fnd_file.log, l_api_name || ', Checking Contract SubLine Effectivity for Subline Number: ' || p_line_number);
1772 select l.chr_id, l.START_DATE, l.END_DATE
1773 INTO l_chr_id, l_kl_start_date, l_kl_end_date
1774 FROM OKC_K_LINES_B sl, okc_k_lines_b l
1775 WHERE sl.id = p_subline_id
1776 AND sl.cle_id = l.id;
1777
1778 IF (p_start_date < l_kl_start_date OR p_start_date > l_kl_end_date) THEN
1779
1780 UPDATE oks_sublines_temp
1781 SET start_dt_error_flag = 'Y',
1782 st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message || ' - Subline Start Date is out of Contract line''s Effectivity, subline number: ' || p_line_number,
1783 'Subline Start Date is out of Contract line''s Effectivity, subline number: ' || p_line_number )
1784 WHERE id = p_subline_id;
1785
1786 exception_yn := 'Y';
1787 END IF;
1788 IF (p_end_date < l_kl_start_date OR p_end_date > l_kl_end_date) THEN
1789
1790 UPDATE oks_sublines_temp
1791 SET end_dt_error_flag = 'Y',
1792 end_dt_err_message = Nvl2(end_dt_err_message , end_dt_err_message || ' - Subline End Date is out of Contract line''s Effectivity, subline number: ' || p_line_number,
1793 'Subline End Date is out of Contract line''s Effectivity, subline number: ' || p_line_number)
1794 WHERE id = p_subline_id;
1795
1796 exception_yn := 'Y';
1797 END IF;
1798
1799 SELECT start_date, end_date
1800 INTO l_k_start_date, l_k_end_date
1801 FROM okc_k_headers_all_b
1802 WHERE id = l_chr_id;
1803
1804 IF (p_start_date < l_k_start_date OR p_start_date > l_k_end_date) THEN
1805
1806 UPDATE oks_sublines_temp
1807 SET start_dt_error_flag = 'Y',
1808 st_dt_err_message = Nvl2(st_dt_err_message, st_dt_err_message ||' - Subline Start Date is out of Contract''s Effectivity, subline number: ' || p_line_number,
1809 'Subline Start Date is out of Contract''s Effectivity, subline number: ' || p_line_number)
1810 WHERE id = p_subline_id;
1811
1812 exception_yn := 'Y';
1813 END IF;
1814
1815 IF (p_end_date < l_k_start_date OR p_end_date > l_k_end_date) THEN
1816
1817 UPDATE oks_sublines_temp
1818 SET end_dt_error_flag = 'Y',
1819 end_dt_err_message = Nvl2(end_dt_err_message, end_dt_err_message || ' - Subline End Date is out of Contract''s Effectivity, subline number: ' || p_line_number,
1820 'Subline End Date is out of Contract''s Effectivity, subline number: ' || p_line_number)
1821 WHERE id = p_subline_id;
1822
1823 exception_yn := 'Y';
1824 END IF;
1825
1826 IF (exception_yn <> 'N') THEN
1827 fnd_file.put_line (fnd_file.log, l_api_name ||
1828 ', Subline Start/End Date is out of Contract''s or Contract Line''s Effectivity, subline number: ' || p_line_number);
1829 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1830 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1831 l_api_name,
1832 'Subline Start/End Date is out of Contract''s or Contract Line''s Effectivity for Subline number: ' || p_line_number);
1833 END IF;
1834 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_line_number;
1835 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Subline Start/End Date is out of Contract''s or Contract Line''s Effectivity';
1836 l_err_eff_idx := l_err_eff_idx +1;
1837 RETURN;
1838 END IF;
1839 EXCEPTION
1840 WHEN OTHERS THEN
1841 exception_yn := 'Y';
1842 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_line_number;
1843 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Error while fetching data from okc_k_lines_b for subline, Error: ' || SQLERRM;
1844 l_err_eff_idx := l_err_eff_idx +1;
1845 fnd_file.put_line (fnd_file.output, l_api_name ||
1846 ' Error while validating data in check_new_subline_effectivity for subline number: ' || p_line_number);
1847 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1848 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1849 l_api_name,
1850 'Error while validating data in check_new_subline_effectivity for subline number: ' || p_line_number);
1851 END IF;
1852 END check_new_subline_effectivity;
1853
1854 procedure mass_update_effectivity_prc(p_mass_update_effectivity_tbl in mass_update_effectivity_tbl,
1855 x_return_status out nocopy varchar2) IS
1856 l_api_version CONSTANT NUMBER := 1.0;
1857 l_api_name VARCHAR2(100) := 'oks_mass_upd_pvt.mass_update_effectivity_prc';
1858 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
1859 l_return_status VARCHAR2(1);
1860 l_msg_count NUMBER;
1861 l_msg_data VARCHAR2(2000);
1862 --l_object_version_number NUMBER;
1863 l_clev_tbl_kl_in okc_contract_pub.clev_tbl_type;
1864 l_clev_tbl_kl_out okc_contract_pub.clev_tbl_type;
1865 l_clev_tbl_sl_in okc_contract_pub.clev_tbl_type;
1866 l_clev_tbl_sl_out okc_contract_pub.clev_tbl_type;
1867 L_KL_LSE_ID number;
1868 l_flag BOOLEAN;
1869 x_msg_tbl oks_attr_defaults_pvt.attr_msg_tbl_type;
1870 x_msg_data VARCHAR2(1000);
1871 x_msg_count NUMBER;
1872 l_msg_index number;
1873 l_tot_msg_count NUMBER;
1874 l_id NUMBER; -- Subline Id
1875 l_reprice_return_status VARCHAR2(10);
1876 --l_active_request VARCHAR2(10);
1877 -- Subline Parameters
1878 l_kl_start_date DATE;
1879 l_kl_end_date date;
1880 l_k_start_date DATE;
1881 l_k_end_date date;
1882 l_input_details OKS_QP_PKG.INPUT_DETAILS;
1883 l_output_details OKS_QP_PKG.PRICE_DETAILS;
1884 l_modif_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
1885 l_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
1886 l_currency VARCHAR2(10);
1887 l_eligible VARCHAR2(3) := 'Y';
1888 i NUMBER := 1;
1889 l_line_number NUMBER;
1890 p NUMBER := 1;
1891 q NUMBER := 1;
1892 l_no_of_prcd_ln NUMBER := 0;
1893 l_no_of_prcd_sln NUMBER := 0;
1894 x_overall_status VARCHAR2(1);
1895 --l_err_mu_eff_tbl err_mu_eff_tbl;
1896
1897 Cursor LineCov_cur(p_line_id IN Number) Is
1898 Select id From OKC_K_LINES_b Where cle_id = p_line_id;
1899
1900 CURSOR check_eligiblity_line(p_hdr_id NUMBER, p_line_id NUMBER, p_intent VARCHAR2) IS
1901 SELECT 'Y'
1902 FROM OKC_K_LINES_B
1903 WHERE dnz_chr_id = p_hdr_id
1904 AND id = p_line_id
1905 AND (date_terminated IS NULL OR date_terminated > TRUNC(SYSDATE))
1906 AND date_cancelled IS NULL
1907 AND sts_code NOT IN ('CANCELLED', 'TERMINATED')
1908 AND end_date > NVL(oks_bill_util_pub.get_billed_upto(id, p_intent),
1909 end_date - 1);
1910
1911 BEGIN
1912
1913 fnd_file.put_line (fnd_file.output, 'Effectivity Change: ' || ' Total Number of Lines/Sublines to process: ' || p_mass_update_effectivity_tbl.Count);
1914
1915 FOR i IN p_mass_update_effectivity_tbl.FIRST .. p_mass_update_effectivity_tbl.LAST LOOP
1916
1917 IF p_mass_update_effectivity_tbl(i).sub_line_id IS NULL THEN
1918 BEGIN
1919 SELECT sts_code
1920 INTO l_clev_tbl_kl_in(p) .sts_code
1921 FROM okc_k_lines_b
1922 where id = p_mass_update_effectivity_tbl(i).line_id;
1923 EXCEPTION
1924 when OTHERS THEN
1925 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
1926 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Error while fetching data from okc_k_lines_b for line';
1927 l_err_eff_idx := l_err_eff_idx +1;
1928
1929 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1930 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1931 l_api_name,
1932 ' Error while fetching data from okc_k_lines_b, line number: ' || p_mass_update_effectivity_tbl(i).line_number);
1933 END IF;
1934 END;
1935
1936 OPEN check_eligiblity_line(p_mass_update_effectivity_tbl(i).chr_id,
1937 p_mass_update_effectivity_tbl(i).line_id,
1938 'T');
1939 FETCH check_eligiblity_line
1940 INTO l_eligible;
1941 CLOSE check_eligiblity_line;
1942
1943 IF l_eligible = 'Y' THEN
1944 fnd_file.put_line (fnd_file.log, l_api_name || ' Line number : ' || p_mass_update_effectivity_tbl(i).line_number || 'is eligible');
1945 l_clev_tbl_kl_in(p).id := p_mass_update_effectivity_tbl(i).line_id;
1946 /* Commented for Bug#14335289 */
1947 -- l_clev_tbl_kl_in(p).line_number := p_mass_update_effectivity_tbl(i).line_number;
1948 l_clev_tbl_kl_in(p).start_date := p_mass_update_effectivity_tbl(i).START_DATE;
1949 l_clev_tbl_kl_in(p).end_date := p_mass_update_effectivity_tbl(i).end_date;
1950 l_clev_tbl_kl_in(p).lse_id := p_mass_update_effectivity_tbl(i).lse_id;
1951
1952 check_new_line_effectivity(p_mass_update_effectivity_tbl(i).chr_id,
1953 p_mass_update_effectivity_tbl(i).line_id,
1954 p_mass_update_effectivity_tbl(i).line_number,
1955 l_clev_tbl_kl_in(p).start_date,
1956 l_clev_tbl_kl_in(p).end_date,
1957 l_clev_tbl_kl_in(p).exception_yn);
1958
1959 If oks_extwar_util_pvt.check_already_billed(p_chr_id => null,
1960 p_cle_id => p_mass_update_effectivity_tbl(i)
1961 .line_id,
1962 p_lse_id => p_mass_update_effectivity_tbl(i)
1963 .lse_id,
1964 p_end_date => null) THEN
1965
1966 fnd_file.put_line (fnd_file.log,
1967 l_api_name ||
1968 ' Entering Validate_date for Line number: ' || p_mass_update_effectivity_tbl(i).line_number);
1969
1970 validate_date(p_api_version => l_api_version,
1971 p_init_msg_list => l_init_msg_list,
1972 p_hdr_id => p_mass_update_effectivity_tbl(i).chr_id,
1973 p_top_line_id => p_mass_update_effectivity_tbl(i).line_id,
1974 p_sub_line_id => NULL,
1975 x_return_status => l_return_status,
1976 x_msg_count => l_msg_count,
1977 x_msg_data => l_msg_data,
1978 x_flag => l_flag);
1979
1980 fnd_file.put_line (fnd_file.log,
1981 l_api_name ||
1982 ' After Validate_date for Line number: ' || p_mass_update_effectivity_tbl(i).line_number);
1983
1984 IF (l_return_status <> 'S') then
1985 x_return_status := l_return_status;
1986 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
1987 l_msg_data => x_msg_data);
1988 l_err_eff_idx := l_err_eff_idx +1;
1989 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number ;
1990 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := x_msg_data ;
1991 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1992 END IF;
1993
1994
1995 -- Bug 5227077 --
1996 /* IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1997 FOR a in 1 .. fnd_msg_pub.count_msg Loop
1998 fnd_msg_pub.get(p_msg_index => a,
1999 p_encoded => 'F',
2000 p_data => l_msg_data,
2001 p_msg_index_out => l_msg_index);
2002 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2003 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2004 l_tot_msg_count := l_tot_msg_count + 1;
2005 l_msg_data := NULL;
2006 end LOOP;
2007 x_overall_status := l_return_status;
2008 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
2009 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Validate_Date, Raised G_EXCEPTION_UNEXPECTED_ERROR';
2010 l_err_eff_idx := l_err_eff_idx +1;
2011 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2012 Else
2013 if L_FLAG <> true then
2014 fnd_msg_pub.initialize;
2015
2016 OKC_API.SET_MESSAGE(p_app_name => 'OKS', --G_APP_NAME_OKS,
2017 p_msg_name => 'OKS_BA_UPDATE_NOT_ALLOWED',
2018 p_token1 => 'Line No ',
2019 p_token1_value => l_line_number);
2020
2021 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2022 p_encoded => fnd_api.g_false);
2023 x_msg_tbl(l_tot_msg_count).status := 'E';
2024 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2025 l_tot_msg_count := l_tot_msg_count + 1;
2026 l_msg_data := NULL;
2027 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
2028 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Validate_Date L_FLAG is False, Raised G_EXCEPTION_UNEXPECTED_ERROR';
2029 l_err_eff_idx := l_err_eff_idx +1;
2030 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2031 End If;
2032 End If; */
2033
2034 End If; --oks_extwar_util_pvt.check_already_billed
2035
2036 IF l_clev_tbl_kl_in(p).STS_CODE = 'ACTIVE' THEN
2037 If l_clev_tbl_kl_in(p).start_date > SYSDATE THEN
2038 l_clev_tbl_kl_in(p).STS_CODE := 'SIGNED';
2039 Elsif l_clev_tbl_kl_in(p).start_date <= SYSDATE And l_clev_tbl_kl_in(p).end_date >= SYSDATE THEN
2040 l_clev_tbl_kl_in(p).STS_CODE := 'ACTIVE';
2041 ELSIF l_clev_tbl_kl_in(p).end_date < SYSDATE THEN
2042 l_clev_tbl_kl_in(p).sts_code := 'EXPIRED';
2043 End if;
2044 END IF;
2045
2046 END IF; -- l_eligible
2047
2048 fnd_file.put_line (fnd_file.log,
2049 l_api_name ||
2050 ' Line number: ' || p_mass_update_effectivity_tbl(i).line_number ||
2051 ', Status would be: ' || l_clev_tbl_kl_in(p).STS_CODE ||
2052 ', Start Date and End Date are: ' || l_clev_tbl_kl_in(p).start_date || ' and ' || l_clev_tbl_kl_in(p).end_date);
2053
2054
2055 p := p + 1; -- Incrementing count of Lines Table type
2056
2057 ------ Subline Mass Update
2058 ELSIF p_mass_update_effectivity_tbl(i).sub_line_id IS NOT NULL THEN
2059
2060 BEGIN
2061 SELECT sts_code
2062 INTO l_clev_tbl_sl_in(q).sts_code
2063 FROM okc_k_lines_b
2064 where id = p_mass_update_effectivity_tbl(i).sub_line_id;
2065 EXCEPTION
2066 when OTHERS THEN
2067 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
2068 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Error while fetching data from okc_k_lines_b for Subline';
2069 l_err_eff_idx := l_err_eff_idx +1;
2070 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2071 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2072 l_api_name,
2073 ' Error while fetching data from okc_k_lines_b');
2074 END IF;
2075 END;
2076
2077 OPEN check_eligiblity_line(p_mass_update_effectivity_tbl(i).chr_id,
2078 p_mass_update_effectivity_tbl(i)
2079 .sub_line_id,
2080 'S');
2081 FETCH check_eligiblity_line
2082 INTO l_eligible;
2083 CLOSE check_eligiblity_line;
2084
2085 IF l_eligible = 'Y' THEN
2086
2087 l_clev_tbl_sl_in(q).id := p_mass_update_effectivity_tbl(i).sub_line_id;
2088 /* Commented for Bug#14335289 */
2089 --l_clev_tbl_sl_in(q).line_number := p_mass_update_effectivity_tbl(i).line_number;
2090 l_clev_tbl_sl_in(q).cle_id := p_mass_update_effectivity_tbl(i).line_id;
2091 l_clev_tbl_sl_in(q).start_date := p_mass_update_effectivity_tbl(i).start_date;
2092 l_clev_tbl_sl_in(q).end_date := p_mass_update_effectivity_tbl(i).end_date;
2093 l_clev_tbl_sl_in(q).lse_id := p_mass_update_effectivity_tbl(i).lse_id;
2094 l_clev_tbl_sl_in(q).CHR_id := p_mass_update_effectivity_tbl(i).chr_id;
2095
2096 IF l_clev_tbl_sl_in(q).STS_CODE = 'ACTIVE' THEN
2097 If l_clev_tbl_sl_in(q).start_date > SYSDATE THen
2098 l_clev_tbl_sl_in(q).STS_CODE := 'SIGNED';
2099 Elsif l_clev_tbl_sl_in(q)
2100 .start_date <= SYSDATE And l_clev_tbl_sl_in(q).end_date >= SYSDATE THEN
2101 l_clev_tbl_sl_in(q).STS_CODE := 'ACTIVE';
2102 ELSIF l_clev_tbl_sl_in(q).end_date < SYSDATE Then
2103 l_clev_tbl_sl_in(q).sts_code := 'EXPIRED';
2104 End if;
2105 END IF;
2106
2107
2108 check_new_subline_effectivity(p_mass_update_effectivity_tbl(i).chr_id,
2109 p_mass_update_effectivity_tbl(i).line_id,
2110 p_mass_update_effectivity_tbl(i).sub_line_id,
2111 p_mass_update_effectivity_tbl(i).line_number,
2112 l_clev_tbl_sl_in(q).start_date,
2113 l_clev_tbl_sl_in(q).end_date,
2114 l_clev_tbl_sl_in(q).exception_yn);
2115
2116 fnd_file.put_line (fnd_file.log,
2117 l_api_name ||
2118 ' Subline ID : ' || l_clev_tbl_sl_in(q).id ||
2119 ' Line Id: ' || l_clev_tbl_sl_in(q).cle_id ||
2120 ' CHR Id: ' || l_clev_tbl_sl_in(q).CHR_id ||
2121 ' New Start Date: ' || l_clev_tbl_sl_in(q).START_DATE ||
2122 ' New End Date: ' || l_clev_tbl_sl_in(q).end_date ||
2123 ' Status Code: ' || l_clev_tbl_sl_in(q).sts_code);
2124
2125
2126 If oks_extwar_util_pvt.check_already_billed(p_chr_id => null,
2127 p_cle_id => p_mass_update_effectivity_tbl(i).line_id,
2128 p_lse_id => p_mass_update_effectivity_tbl(i).lse_id,
2129 p_end_date => null) THEN
2130 fnd_file.put_line (fnd_file.log,
2131 l_api_name ||
2132 ' Entering Validate_date for Sub Line Id: ' ||
2133 p_mass_update_effectivity_tbl(i).sub_line_id);
2134
2135 validate_date(p_api_version => l_api_version,
2136 p_init_msg_list => l_init_msg_list,
2137 p_hdr_id => NULL,
2138 p_top_line_id => p_mass_update_effectivity_tbl(i).line_id,
2139 p_sub_line_id => p_mass_update_effectivity_tbl(i).sub_line_id,
2140 x_return_status => l_return_status,
2141 x_msg_count => l_msg_count,
2142 x_msg_data => l_msg_data,
2143 x_flag => l_flag);
2144 IF (l_return_status <> 'S') then
2145 x_return_status := l_return_status;
2146 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
2147 l_msg_data => x_msg_data);
2148 l_err_eff_idx := l_err_eff_idx +1;
2149 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number ;
2150 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := x_msg_data ;
2151 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2152 END IF;
2153 -- Bug 5227077 --
2154 /* IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2155 FOR a in 1 .. fnd_msg_pub.count_msg Loop
2156 fnd_msg_pub.get(p_msg_index => a,
2157 p_encoded => 'F',
2158 p_data => l_msg_data,
2159 p_msg_index_out => l_msg_index);
2160 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2161 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2162 l_tot_msg_count := l_tot_msg_count + 1;
2163 l_msg_data := NULL;
2164 end LOOP;
2165 x_overall_status := l_return_status;
2166 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
2167 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Validate_date not success, Raised G_EXCEPTION_UNEXPECTED_ERROR';
2168 l_err_eff_idx := l_err_eff_idx +1;
2169 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2170 Else
2171 if L_FLAG <> true then
2172 fnd_msg_pub.initialize;
2173
2174 OKC_API.SET_MESSAGE(p_app_name => 'OKS', --G_APP_NAME_OKS,
2175 p_msg_name => 'OKS_BA_UPDATE_NOT_ALLOWED',
2176 p_token1 => 'Line No ',
2177 p_token1_value => l_line_number);
2178
2179 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2180 p_encoded => fnd_api.g_false);
2181 x_msg_tbl(l_tot_msg_count).status := 'E';
2182 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2183 l_tot_msg_count := l_tot_msg_count + 1;
2184 l_msg_data := NULL;
2185 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
2186 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Validate_date l_flag is FALSE, Raised G_EXCEPTION_UNEXPECTED_ERROR';
2187 l_err_eff_idx := l_err_eff_idx +1;
2188 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2189 End If;
2190 End If; */
2191
2192 End If;
2193 q := q + 1; -- Incrementing Count of Sublines table type
2194 END IF; -- l_clev_tbl_sl_in(q).sts_code
2195 END IF; --p_mass_update_effectivity_tbl(i).sub_line_id
2196 END LOOP;
2197
2198 fnd_file.put_line (fnd_file.output, 'Effectivity Change: ' || ' Number of Lines to Process : ' || l_clev_tbl_kl_in.Count);
2199
2200 --Processing Lines
2201 IF l_clev_tbl_kl_in.Count > 0 THEN
2202 FOR j IN l_clev_tbl_kl_in.FIRST .. l_clev_tbl_kl_in.LAST LOOP
2203
2204 fnd_file.put_line (fnd_file.log,
2205 l_api_name || ' Processing Line Id:' || l_clev_tbl_kl_in(j).id);
2206
2207 IF l_clev_tbl_kl_in(j).exception_yn <> 'Y' THEN
2208 BEGIN
2209 DBMS_TRANSACTION.SAVEPOINT('MASS_UPDATE_EFFECTIVITY');
2210 fnd_file.put_line (fnd_file.log,l_api_name || ' Before Calling : okc_contract_pub.update_contract_line');
2211
2212 okc_contract_pub.update_contract_line(p_api_version => l_api_version,
2213 p_init_msg_list => l_init_msg_list,
2214 x_return_status => l_return_status,
2215 x_msg_count => l_msg_count,
2216 x_msg_data => l_msg_data,
2217 p_restricted_update => 'F',
2218 p_clev_rec => l_clev_tbl_kl_in(j),
2219 X_CLEV_rec => l_clev_tbl_kl_out(j));
2220 fnd_file.put_line (fnd_file.log,l_api_name || ' After Calling : okc_contract_pub.update_contract_line');
2221
2222 IF (l_return_status <> 'S') then
2223 x_return_status := l_return_status;
2224 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
2225 l_msg_data => x_msg_data);
2226 l_err_eff_idx := l_err_eff_idx +1;
2227 l_err_mu_eff_tbl(l_err_eff_idx).line_number := l_clev_tbl_kl_out(j).line_number ;
2228 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := x_msg_data ;
2229 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2230 END IF;
2231
2232 /* IF (l_return_status <> 'S') THEN
2233 FOR a in 1 .. fnd_msg_pub.count_msg Loop
2234 fnd_msg_pub.get(p_msg_index => a,
2235 p_encoded => 'F',
2236 p_data => l_msg_data,
2237 p_msg_index_out => l_msg_index);
2238
2239 X_MSG_TBL(L_TOT_MSG_COUNT).STATUS := L_RETURN_STATUS;
2240 X_MSG_TBL(L_TOT_MSG_COUNT).DESCRIPTION := L_MSG_DATA;
2241 l_err_mu_eff_tbl(l_err_eff_idx).line_number := p_mass_update_effectivity_tbl(i).line_number;
2242 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := 'Error While updating line is' || X_MSG_TBL(L_TOT_MSG_COUNT).DESCRIPTION;
2243 l_err_eff_idx := l_err_eff_idx +1;
2244
2245 IF fnd_log.level_statement >=
2246 fnd_log.g_current_runtime_level THEN
2247 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2248 l_api_name,
2249 ' Error While updating line is: ' ||
2250 X_MSG_TBL(L_TOT_MSG_COUNT).DESCRIPTION);
2251 END IF;
2252 l_tot_msg_count := l_tot_msg_count + 1;
2253 l_msg_data := NULL;
2254 End Loop;
2255 x_overall_status := l_return_status;
2256 end if; */
2257
2258 EXCEPTION
2259 when OTHERS THEN
2260 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('MASS_UPDATE_EFFECTIVITY');
2261 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2262 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2263 l_api_name,
2264 ' Error from okc_contract_pub.update_contract_line procedure' ||
2265 SQLERRM);
2266 END IF;
2267 x_overall_status := 'W';
2268 END;
2269 --for coverage
2270 if l_return_status = 'S' then
2271 -- Updating Billing Schedule
2272 BEGIN
2273 fnd_file.put_line (fnd_file.log,l_api_name ||' Before Calling : OKS_BILL_SCH.Cascade_Dates_SLL');
2274
2275 OKS_BILL_SCH.Cascade_Dates_SLL(p_top_line_id => l_clev_tbl_kl_in(j).id,
2276 x_return_status => l_return_status,
2277 x_msg_count => l_msg_count,
2278 x_msg_data => l_msg_data);
2279
2280 fnd_file.put_line (fnd_file.log,l_api_name || ' After Calling : OKS_BILL_SCH.Cascade_Dates_SLL');
2281
2282 IF (l_return_status <> 'S') then
2283 x_return_status := l_return_status;
2284 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
2285 l_msg_data => x_msg_data);
2286 l_err_eff_idx := l_err_eff_idx +1;
2287 l_err_mu_eff_tbl(l_err_eff_idx).line_number := l_clev_tbl_kl_out(j).line_number ;
2288 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := x_msg_data ;
2289 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2290 END IF;
2291
2292 /* If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
2293
2294 FOR a in 1 .. fnd_msg_pub.count_msg Loop
2295 fnd_msg_pub.get(p_msg_index => a,
2296 p_encoded => 'F',
2297 p_data => l_msg_data,
2298 p_msg_index_out => l_msg_index);
2299
2300 X_MSG_TBL(L_TOT_MSG_COUNT).STATUS := L_RETURN_STATUS;
2301 X_MSG_TBL(L_TOT_MSG_COUNT).DESCRIPTION := L_MSG_DATA;
2302 IF fnd_log.level_statement >=
2303 fnd_log.g_current_runtime_level THEN
2304 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2305 l_api_name,
2306 ' Error While updating line is: ' ||
2307 X_MSG_TBL(L_TOT_MSG_COUNT).DESCRIPTION);
2308 END IF;
2309 l_tot_msg_count := l_tot_msg_count + 1;
2310 l_msg_data := NULL;
2311 End Loop;
2312
2313 End If; */
2314
2315 EXCEPTION
2316 when OTHERS THEN
2317 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('MASS_UPDATE_EFFECTIVITY');
2318 IF fnd_log.level_statement >=
2319 fnd_log.g_current_runtime_level THEN
2320 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2321 l_api_name,
2322 ' Error from OKS_BILL_SCH.Cascade_Dates_SLL: ' ||
2323 SQLERRM);
2324 END IF;
2325 END;
2326
2327 Open LineCov_cur(l_clev_tbl_kl_in(j).id);
2328 Fetch LineCov_cur
2329 into l_id;
2330 if LINECOV_CUR%FOUND then
2331 BEGIN
2332 fnd_file.put_line (fnd_file.log,l_api_name ||
2333 ' Before Calling : OKS_COVERAGES_PVT.Update_COVERAGE_Effectivity');
2334
2335 OKS_COVERAGES_PVT.Update_COVERAGE_Effectivity(p_api_version => l_api_version,
2336 p_init_msg_list => l_init_msg_list,
2337 x_return_status => l_return_status,
2338 x_msg_count => l_msg_count,
2339 X_MSG_DATA => L_MSG_DATA,
2340 p_service_Line_Id => l_clev_tbl_kl_in(j).cle_id,
2341 p_New_Start_Date => l_clev_tbl_kl_in(j).start_date,
2342 P_NEW_END_DATE => l_clev_tbl_kl_in(j).END_DATE);
2343 fnd_file.put_line (fnd_file.log,l_api_name || ' After Calling : OKS_COVERAGES_PVT.Update_COVERAGE_Effectivity');
2344 END;
2345
2346 IF (l_return_status <> 'S') then
2347 x_return_status := l_return_status;
2348 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
2349 l_msg_data => x_msg_data);
2350 l_err_eff_idx := l_err_eff_idx +1;
2351 l_err_mu_eff_tbl(l_err_eff_idx).line_number := l_clev_tbl_kl_out(j).line_number ;
2352 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := x_msg_data ;
2353 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2354 END IF;
2355
2356 END IF;
2357
2358 close LINEcov_cur;
2359
2360 if L_RETURN_STATUS = 'S' then
2361 fnd_file.put_line (fnd_file.log, l_api_name || ' Before Calling : OKS_PM_PROGRAMS_PVT.ADJUST_PM_PROGRAM_SCHEDULE');
2362
2363 BEGIN
2364 OKS_PM_PROGRAMS_PVT.ADJUST_PM_PROGRAM_SCHEDULE(p_api_version => l_api_version,
2365 P_INIT_MSG_LIST => L_INIT_MSG_LIST,
2366 p_contract_line_id => l_clev_tbl_kl_in(j).cle_id,
2367 p_new_start_date => l_clev_tbl_kl_in(j).start_date,
2368 p_new_end_date => l_clev_tbl_kl_in(j).end_date,
2369 x_return_status => l_return_status,
2370 x_msg_count => l_msg_count,
2371 x_msg_data => l_msg_data);
2372 fnd_file.put_line (fnd_file.log, l_api_name ||
2373 ' After Calling : OKS_PM_PROGRAMS_PVT.ADJUST_PM_PROGRAM_SCHEDULE');
2374 END;
2375
2376 IF (l_return_status <> 'S') then
2377 x_return_status := l_return_status;
2378 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
2379 l_msg_data => x_msg_data);
2380 l_err_eff_idx := l_err_eff_idx +1;
2381 l_err_mu_eff_tbl(l_err_eff_idx).line_number := l_clev_tbl_kl_out(j).line_number ;
2382 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := x_msg_data ;
2383 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2384 END IF;
2385
2386 End If;
2387 end if; --L_RETURN_STATUS = 'S'
2388
2389 l_no_of_prcd_ln := l_no_of_prcd_ln +1 ; -- Counting Number of Processed Lines
2390
2391 END IF; --IF l_clev_tbl_kl_in(j).exception_yn
2392 END LOOP; --l_clev_tbl_kl_in
2393 END IF; --l_clev_tbl_kl_in.Count
2394 -- End for Processing Lines
2395
2396 fnd_file.put_line (fnd_file.output, 'Effectivity Change: ' || ' Number of Sublines to process : ' || l_clev_tbl_sl_in.Count);
2397
2398 -- Processing Sublines
2399 IF l_clev_tbl_sl_in.Count > 0 THEN
2400 FOR k IN l_clev_tbl_sl_in.FIRST .. l_clev_tbl_sl_in.LAST LOOP
2401
2402 IF l_clev_tbl_sl_in(k).exception_yn <> 'Y' THEN
2403 BEGIN
2404 fnd_file.put_line (fnd_file.log,l_api_name || ' Processing SubLine Id:' || To_Char(l_clev_tbl_sl_in(k).id));
2405
2406 -- Resetting before calling the update package only for sublines
2407 l_clev_tbl_sl_in(k).CHR_id := NULL;
2408 fnd_file.put_line (fnd_file.log,l_api_name || ' Before Calling okc_contract_pub.update_contract_line');
2409 okc_contract_pub.update_contract_line(p_api_version => l_api_version,
2410 p_init_msg_list => l_init_msg_list,
2411 x_return_status => l_return_status,
2412 x_msg_count => l_msg_count,
2413 x_msg_data => l_msg_data,
2414 p_restricted_update => 'F',
2415 p_clev_rec => l_clev_tbl_sl_in(k),
2416 X_CLEV_rec => l_clev_tbl_sl_out(k));
2417 fnd_file.put_line (fnd_file.log,l_api_name || ' After Calling okc_contract_pub.update_contract_line');
2418
2419
2420 EXCEPTION
2421 when OTHERS THEN
2422 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2423 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2424 l_api_name,
2425 ' Exception from okc_contract_pub.update_contract_line, Error is: ' ||
2426 SQLERRM);
2427 END IF;
2428 END;
2429
2430 IF (l_return_status <> 'S') then
2431 x_return_status := l_return_status;
2432 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
2433 l_msg_data => x_msg_data);
2434 l_err_eff_idx := l_err_eff_idx +1;
2435 l_err_mu_eff_tbl(l_err_eff_idx).line_number := l_clev_tbl_sl_out(k).line_number ; --Modified for Bug#14335289
2436 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := x_msg_data ;
2437 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2438 END IF;
2439
2440 /*if (L_RETURN_STATUS <> 'S') then
2441 l_tot_msg_count := 0;
2442 FOR a in 1 .. fnd_msg_pub.count_msg Loop
2443 fnd_msg_pub.get(p_msg_index => a,
2444 p_encoded => 'F',
2445 p_data => l_msg_data,
2446 p_msg_index_out => l_msg_index);
2447
2448 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2449 X_MSG_TBL(L_TOT_MSG_COUNT).DESCRIPTION := L_MSG_DATA;
2450 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2451 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2452 l_api_name,
2453 ' Error message is: ' || x_msg_tbl(l_tot_msg_count).description);
2454 END IF;
2455 l_tot_msg_count := l_tot_msg_count + 1;
2456 L_MSG_DATA := null;
2457 End Loop;
2458 x_overall_status := L_RETURN_STATUS;
2459 end if;*/
2460
2461 IF l_clev_tbl_sl_in(k).lse_id IN (7, 9, 13, 18, 25) THEN
2462
2463 --OKS_AUTH_UTIL_PVT.CHECK_REPRICE_REQUEST(l_clev_tbl_sl_in(k).CHR_ID,l_reprice_return_status);
2464
2465 --l_input_details.chr_id := l_clev_tbl_sl_in(k).CHR_ID;
2466
2467 l_input_details.line_id := l_clev_tbl_sl_in(k).cle_ID;
2468 l_input_details.intent := 'SP';
2469 l_input_details.subline_id := l_clev_tbl_sl_in(k).id;
2470 l_input_details.currency := l_currency;
2471 fnd_file.put_line (fnd_file.log,l_api_name || ' Before Calling oks_qp_int_pvt.compute_Price');
2472
2473 oks_qp_int_pvt.compute_Price(p_api_version => 1.0,
2474 p_init_msg_list => 'T',
2475 p_detail_rec => l_input_details,
2476 x_price_details => l_output_details,
2477 x_modifier_details => l_modif_details,
2478 x_price_break_details => l_pb_details,
2479 x_return_status => l_return_status,
2480 x_msg_count => l_msg_count,
2481 x_msg_data => l_msg_data);
2482 fnd_file.put_line (fnd_file.log,l_api_name ||' After Calling oks_qp_int_pvt.compute_Price, l_return_status '||l_return_status);
2483
2484 IF (l_return_status <> 'S') then
2485 x_return_status := l_return_status;
2486 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
2487 l_msg_data => x_msg_data);
2488 l_err_eff_idx := l_err_eff_idx +1;
2489 l_err_mu_eff_tbl(l_err_eff_idx).line_number := l_clev_tbl_sl_out(k).line_number ; --Modified for Bug#14335289
2490 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := x_msg_data ;
2491 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2492 END IF;
2493
2494 END IF; --l_clev_tbl_sl_in(k).lse_id
2495 fnd_file.put_line (fnd_file.log,l_api_name || ' Before Calling OKS_BILL_SCH.Cascade_Dates_SLL');
2496
2497 OKS_BILL_SCH.Cascade_Dates_SLL(p_top_line_id => l_clev_tbl_sl_in(k).cle_id,
2498 x_return_status => l_return_status,
2499 x_msg_count => l_msg_count,
2500 x_msg_data => l_msg_data);
2501 fnd_file.put_line (fnd_file.log,l_api_name || ' After Calling OKS_BILL_SCH.Cascade_Dates_SLL, l_return_status '||l_return_status);
2502
2503 IF (l_return_status <> 'S') then
2504 x_return_status := l_return_status;
2505 oks_bill_rec_pub.get_message(l_msg_cnt => x_msg_count,
2506 l_msg_data => x_msg_data);
2507 l_err_eff_idx := l_err_eff_idx +1;
2508 l_err_mu_eff_tbl(l_err_eff_idx).line_number := l_clev_tbl_sl_out(k).line_number ; --Modified for Bug#14335289
2509 l_err_mu_eff_tbl(l_err_eff_idx).error_msg := x_msg_data ;
2510 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2511 END IF;
2512
2513 /*If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
2514
2515 FOR a in 1 .. fnd_msg_pub.count_msg Loop
2516 fnd_msg_pub.get(p_msg_index => a,
2517 p_encoded => 'F',
2518 p_data => l_msg_data,
2519 p_msg_index_out => l_msg_index);
2520
2521 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2522 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2523 l_tot_msg_count := l_tot_msg_count + 1;
2524 l_msg_data := NULL;
2525 End Loop;
2526 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2527
2528 End If;*/
2529 l_no_of_prcd_sln := l_no_of_prcd_sln +1 ;
2530 END IF; --l_clev_tbl_sl_in(k).exception_yn NOT IN ('E')
2531 END LOOP; --l_clev_tbl_sl_in
2532 END IF; --l_clev_tbl_sl_in.Count
2533 -- End for Processing Sublines
2534 --COMMIT; -- To commit the data to base tables
2535 fnd_file.put_line (fnd_file.output,'Effectivity Change: ' || ' No. of Successful lines : ' || l_no_of_prcd_ln);
2536 fnd_file.put_line (fnd_file.output,'Effectivity Change: ' || ' No. of Rejected lines : ' || to_number(l_clev_tbl_kl_in.Count - l_no_of_prcd_ln));
2537
2538 fnd_file.put_line (fnd_file.output,'Effectivity Change: ' || ' No. of Successful sublines : ' || l_no_of_prcd_sln);
2539 fnd_file.put_line (fnd_file.output,'Effectivity Change: ' || ' No. of Rejected sublines : ' || to_number(l_clev_tbl_sl_in.Count - l_no_of_prcd_sln));
2540
2541 IF l_err_mu_eff_tbl.Count > 0 THEN
2542 fnd_file.put_line (fnd_file.Log, 'Error Messages while updating effectivities');
2543 fnd_file.put_line (fnd_file.Log, ' ');
2544
2545 fnd_file.put_line (fnd_file.output, 'Error Messages while updating effectivities');
2546 fnd_file.put_line (fnd_file.output, ' ');
2547
2548 fnd_file.put_line (fnd_file.log,'Line Number '||' - '|| 'Error Message ');
2549 fnd_file.put_line (fnd_file.log,'*********** '||' - '|| '************************************** ');
2550
2551 fnd_file.put_line (fnd_file.output,'Line Number '||' - '|| 'Error Message ');
2552 fnd_file.put_line (fnd_file.output,'*********** '||' - '|| '************************************** ');
2553
2554 FOR i in l_err_mu_eff_tbl.FIRST .. l_err_mu_eff_tbl.LAST LOOP
2555
2556 fnd_file.put_line (fnd_file.log,RPad(l_err_mu_eff_tbl(i).line_number,11, ' ') ||' - '||
2557 l_err_mu_eff_tbl(i).error_msg );
2558
2559 fnd_file.put_line (fnd_file.output,RPad(l_err_mu_eff_tbl(i).line_number,11, ' ') ||' - '||
2560 l_err_mu_eff_tbl(i).error_msg );
2561
2562 END LOOP;
2563 fnd_file.put_line (fnd_file.Log, '***********************************************************');
2564 END IF;
2565
2566 IF x_overall_status IS NOT NULL THEN
2567 x_return_status := x_overall_status;
2568 END IF;
2569
2570 EXCEPTION
2571 when OTHERS THEN
2572 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2573 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2574 l_api_name, ' Error while updating line' || SQLERRM);
2575
2576 END IF;
2577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2578 --fnd_file.put_line (fnd_file.log,'Unexpected error in mass_update_effectivity_prc: '||x_return_status|| ' SQLERRM: '||SQLERRM);
2579 END mass_update_effectivity_prc;
2580
2581 /*functions to be used internally*/
2582 FUNCTION oks_model_check(p_contract_id NUMBER, p_customer_prod_id NUMBER)
2583 RETURN VARCHAR2 IS
2584
2585 l_model_prod_id NUMBER;
2586
2587 /*check model item*/
2588 CURSOR level1_csr IS
2589 SELECT a.customer_product_id
2590 FROM oks_ib_config_v a
2591 WHERE a.config_parent_id IS NULL
2592 START WITH a.customer_product_id = p_customer_prod_id
2593 CONNECT BY PRIOR a.config_parent_id = a.customer_product_id
2594 AND EXISTS
2595 (SELECT 1
2596 FROM okc_k_items itm
2597 where a.customer_product_id = itm.object1_id1
2598 AND itm.dnz_chr_id = p_contract_id);
2599
2600 BEGIN
2601 OPEN level1_csr;
2602 FETCH level1_csr
2603 INTO l_model_prod_id;
2604 CLOSE level1_csr;
2605
2606 IF l_model_prod_id IS NOT NULL THEN
2607 RETURN 'Y';
2608 ELSE
2609 RETURN 'N';
2610 END IF;
2611
2612 END oks_model_check;
2613
2614 FUNCTION oks_config_check(p_contract_id NUMBER, p_customer_prod_id NUMBER)
2615 RETURN VARCHAR2 IS
2616
2617 l_config_prod_id NUMBER;
2618
2619 /*check config item*/
2620 CURSOR level2_csr IS
2621 SELECT b.customer_product_id
2622 FROM oks_ib_config_v b
2623 WHERE LEVEL = 2
2624 START WITH b.customer_product_id =
2625 (SELECT a.customer_product_id
2626 FROM oks_ib_config_v a
2627 WHERE a.config_parent_id IS NULL
2628 START WITH a.customer_product_id = p_customer_prod_id
2629 CONNECT BY PRIOR a.config_parent_id = a.customer_product_id)
2630 CONNECT BY b.config_parent_id = PRIOR b.customer_product_id
2631 AND EXISTS
2632 (SELECT 1
2633 FROM okc_k_items itm
2634 where b.customer_product_id = itm.object1_id1
2635 AND itm.dnz_chr_id = p_contract_id);
2636
2637 BEGIN
2638 OPEN level2_csr;
2639 FETCH level2_csr
2640 INTO l_config_prod_id;
2641 CLOSE level2_csr;
2642
2643 IF l_config_prod_id IS NOT NULL THEN
2644 RETURN 'Y';
2645 ELSE
2646 RETURN 'N';
2647 END IF;
2648
2649 END oks_config_check;
2650
2651 FUNCTION oks_config_item_check(p_contract_id NUMBER,
2652 p_customer_prod_id NUMBER) RETURN VARCHAR2 IS
2653
2654 l_config_prod_id NUMBER;
2655
2656 /*check config item*/
2657 CURSOR level2_csr IS
2658 SELECT b.customer_product_id
2659 FROM oks_ib_config_v b
2660 WHERE LEVEL = 2
2661 START WITH b.customer_product_id =
2662 (SELECT a.customer_product_id
2663 FROM oks_ib_config_v a
2664 WHERE a.config_parent_id IS NULL
2665 START WITH a.customer_product_id = p_customer_prod_id
2666 CONNECT BY PRIOR a.config_parent_id = a.customer_product_id)
2667 CONNECT BY b.config_parent_id = PRIOR b.customer_product_id
2668 AND EXISTS
2669 (SELECT 1
2670 FROM okc_k_items itm
2671 where b.customer_product_id = itm.object1_id1
2672 AND itm.dnz_chr_id = p_contract_id);
2673
2674 BEGIN
2675 OPEN level2_csr;
2676 FETCH level2_csr
2677 INTO l_config_prod_id;
2678 CLOSE level2_csr;
2679
2680 IF l_config_prod_id IS NOT NULL THEN
2681 IF l_config_prod_id = p_customer_prod_id THEN
2682 RETURN 'Y';
2683 ELSE
2684 RETURN 'N';
2685 END IF;
2686 ELSE
2687 RETURN 'N';
2688 END IF;
2689
2690 END oks_config_item_check;
2691
2692 FUNCTION oks_model_item_check(p_contract_id NUMBER,
2693 p_customer_prod_id NUMBER) RETURN VARCHAR2 IS
2694
2695 l_model_prod_id NUMBER;
2696
2697 /*check model item*/
2698 CURSOR level1_csr IS
2699 SELECT a.customer_product_id
2700 FROM oks_ib_config_v a
2701 WHERE a.config_parent_id IS NULL
2702 START WITH a.customer_product_id = p_customer_prod_id
2703 CONNECT BY PRIOR a.config_parent_id = a.customer_product_id
2704 AND EXISTS
2705 (SELECT 1
2706 FROM okc_k_items itm
2707 where a.customer_product_id = itm.object1_id1
2708 AND itm.dnz_chr_id = p_contract_id);
2709
2710 BEGIN
2711 OPEN level1_csr;
2712 FETCH level1_csr
2713 INTO l_model_prod_id;
2714 CLOSE level1_csr;
2715
2716 IF l_model_prod_id IS NOT NULL THEN
2717 IF l_model_prod_id = p_customer_prod_id THEN
2718 RETURN 'Y';
2719 ELSE
2720 RETURN 'N';
2721 END IF;
2722 ELSE
2723 RETURN 'N';
2724 END IF;
2725
2726 END oks_model_item_check;
2727 /*functions to be used internally*/
2728
2729 PROCEDURE populate_temp_tbl(p_contract_id NUMBER,
2730 x_return_status out nocopy varchar2) IS
2731 PRAGMA autonomous_transaction;
2732
2733 CURSOR check_saved_flag_csr IS
2734 SELECT saved_flag FROM oks_headers_temp WHERE chr_id = p_contract_id;
2735
2736 l_saved_flag VARCHAR2(1) := 'N';
2737 l_return_status VARCHAR2(1);
2738
2739 BEGIN
2740
2741 x_return_status := 'W';
2742 /*check whether the contract data was already saved in earlier session
2743 if yes, do not populate the temp tables again*/
2744
2745 OPEN check_saved_flag_csr;
2746 FETCH check_saved_flag_csr
2747 INTO l_saved_flag;
2748 CLOSE check_saved_flag_csr;
2749
2750 IF l_saved_flag = 'N' THEN
2751
2752 DELETE_CONTRACT_DATA(p_contract_id,l_return_status);
2753
2754 INSERT INTO oks_headers_temp
2755 (chr_id,chr_id_char,
2756 contract_number,
2757 contract_number_modifier,
2758 contract_start_date,
2759 contract_end_date,
2760 contract_status,
2761 contract_amount,
2762 cascade_selection,
2763 created_by,
2764 creation_date,
2765 last_updated_by,
2766 last_update_date,
2767 last_update_login)
2768 SELECT id, To_Char(id),
2769 contract_number,
2770 contract_number_modifier,
2771 start_date,
2772 end_date,
2773 sts_code,
2774 ESTIMATED_AMOUNT,
2775 'Y',
2776 FND_GLOBAL.USER_ID,
2777 SYSDATE,
2778 FND_GLOBAL.USER_ID,
2779 SYSDATE,
2780 FND_GLOBAL.LOGIN_ID
2781 FROM okc_k_headers_all_b
2782 WHERE id = p_contract_id;
2783
2784 INSERT INTO oks_lines_temp
2785 (id,id_char,
2786 dnz_chr_id, dnz_chr_id_char,
2787 line_number,
2788 lse_id,
2789 name,
2790 description,
2791 old_status,
2792 undo_status,
2793 old_ste_code,
2794 status,
2795 ste_code,
2796 new_status,
2797 new_ste_code,
2798 start_date,
2799 end_date,
2800 old_start_date,
2801 old_end_date,
2802 Price_negotiated,
2803 undo_start_date,
2804 undo_end_date,
2805 undo_Price_negotiated)
2806 SELECT lin.id, To_Char(lin.id),
2807 lin.dnz_chr_id, To_Char(lin.dnz_chr_id),
2808 line_number,
2809 lse_id,
2810 SI.description NAME,
2811 SI.name DESCRIPTION,
2812 lin.sts_code old_Status,
2813 lin.sts_code undo_Status,
2814 sts.ste_code old_ste_code,
2815 lin.sts_code Status,
2816 sts.ste_code ste_code,
2817 lin.sts_code new_status,
2818 sts.ste_code new_ste_code,
2819 lin.start_date,
2820 lin.end_date,
2821 lin.start_date,
2822 lin.end_date,
2823 Price_negotiated,
2824 lin.start_date,
2825 lin.end_date,
2826 Price_negotiated
2827 FROM okc_k_lines_b lin,
2828 okc_k_items itm,
2829 OKX_SYSTEM_ITEMS_V SI,
2830 okc_statuses_b sts
2831 WHERE lin.id = itm.cle_id
2832 AND itm.object1_id1 = SI.id1
2833 AND SI.organization_id = itm.object1_id2
2834 AND lin.sts_code = sts.code
2835 AND lin.lse_id IN (1, 12, 14, 19)
2836 AND lin.dnz_chr_id = p_contract_id
2837 ORDER BY To_Number(lin.line_number);
2838
2839 INSERT INTO oks_sublines_temp
2840 (id,
2841 cle_id,
2842 dnz_chr_id,
2843 id_char,
2844 cle_id_char,
2845 dnz_chr_id_char,
2846 lse_id,
2847 line_number,
2848 customer_product_id,
2849 name,
2850 description,
2851 serial_number,
2852 system_name,
2853 old_status,
2854 undo_status,
2855 old_ste_code,
2856 status,
2857 ste_code,
2858 new_status,
2859 new_ste_code,
2860 reason_code,
2861 start_date,
2862 end_date,
2863 old_start_date,
2864 old_end_date,
2865 old_price_negotiated,
2866 undo_start_date,
2867 undo_end_date,
2868 undo_price_negotiated,
2869 price_negotiated,
2870 top_lvl_parent)
2871 SELECT lin.id,
2872 lin.cle_id,
2873 lin.dnz_chr_id,
2874 To_Char(lin.id),
2875 To_Char(lin.cle_id),
2876 To_Char(lin.dnz_chr_id),
2877 lin.lse_id,
2878 (SELECT line_number
2879 FROM okc_k_lines_b line
2880 WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
2881 cs_item.customer_product_id,
2882 SI.name,
2883 SI.description,
2884 cs_item.serial_number,
2885 CSTL.NAME system_name,
2886 lin.sts_code old_Status,
2887 lin.sts_code undo_Status,
2888 sts.ste_code old_ste_code,
2889 lin.sts_code Status,
2890 sts.ste_code ste_code,
2891 lin.sts_code new_status,
2892 sts.ste_code new_ste_code,
2893 null,
2894 lin.start_date,
2895 lin.end_date,
2896 lin.start_date,
2897 lin.end_date,
2898 lin.price_negotiated,
2899 lin.start_date,
2900 lin.end_date,
2901 lin.price_negotiated,
2902 lin.price_negotiated,
2903 DECODE((SELECT a1.customer_product_id
2904 FROM oks_ib_config_v a1
2905 WHERE a1.config_parent_id IS NULL
2906 START WITH a1.customer_product_id =
2907 CS_ITEM.CUSTOMER_PRODUCT_ID
2908 CONNECT BY PRIOR a1.config_parent_id = a1.customer_product_id
2909 AND EXISTS
2910 (SELECT 1
2911 FROM okc_k_items itm1
2912 WHERE a1.customer_product_id = itm1.object1_id1
2913 AND itm1.dnz_chr_id = ITM.DNZ_CHR_ID)),
2914 CS_ITEM.CUSTOMER_PRODUCT_ID,
2915 -99,
2916 NULL,
2917 DECODE((SELECT b1.customer_product_id
2918 FROM oks_ib_config_v b1
2919 WHERE LEVEL = 2
2920 START WITH b1.customer_product_id =
2921 (SELECT a.customer_product_id
2922 FROM oks_ib_config_v a
2923 WHERE a.config_parent_id IS NULL
2924 START WITH a.customer_product_id =
2925 CS_ITEM.CUSTOMER_PRODUCT_ID
2926 CONNECT BY PRIOR a.config_parent_id =
2927 a.customer_product_id)
2928 CONNECT BY b1.config_parent_id = PRIOR b1.customer_product_id
2929 AND EXISTS
2930 (SELECT 1
2931 FROM okc_k_items itm1
2932 where b1.customer_product_id =
2933 itm1.object1_id1
2934 AND itm1.dnz_chr_id = ITM.DNZ_CHR_ID)
2935 AND ROWNUM = 1)
2936 ,
2937 CS_ITEM.CUSTOMER_PRODUCT_ID,
2938 -99,
2939 NULL,
2940 (SELECT A.CUSTOMER_PRODUCT_ID
2941 FROM OKS_IB_CONFIG_V A
2942 WHERE A.CONFIG_PARENT_ID IS NULL
2943 START WITH A.CUSTOMER_PRODUCT_ID =
2944 CS_ITEM.CUSTOMER_PRODUCT_ID
2945 CONNECT BY A.CUSTOMER_PRODUCT_ID = PRIOR
2946 A.CONFIG_PARENT_ID),
2947 (SELECT b1.customer_product_id
2948 FROM oks_ib_config_v b1
2949 WHERE LEVEL = 2
2950 START WITH b1.customer_product_id =
2951 (SELECT a.customer_product_id
2952 FROM oks_ib_config_v a
2953 WHERE a.config_parent_id IS NULL
2954 START WITH a.customer_product_id =
2955 CS_ITEM.CUSTOMER_PRODUCT_ID
2956 CONNECT BY PRIOR a.config_parent_id =
2957 a.customer_product_id)
2958 CONNECT BY b1.config_parent_id = PRIOR
2959 b1.customer_product_id
2960 AND EXISTS
2961 (SELECT 1
2962 FROM okc_k_items itm1
2963 where b1.customer_product_id =
2964 itm1.object1_id1
2965 AND itm1.dnz_chr_id = ITM.DNZ_CHR_ID)
2966 AND ROWNUM = 1)),
2967 (SELECT a1.customer_product_id
2968 FROM oks_ib_config_v a1
2969 WHERE a1.config_parent_id IS NULL
2970 START WITH a1.customer_product_id =
2971 CS_ITEM.CUSTOMER_PRODUCT_ID
2972 CONNECT BY PRIOR a1.config_parent_id = a1.customer_product_id
2973 AND EXISTS
2974 (SELECT 1
2975 FROM okc_k_items itm1
2976 WHERE a1.customer_product_id = itm1.object1_id1
2977 AND itm1.dnz_chr_id = ITM.DNZ_CHR_ID)
2978 AND ROWNUM = 1)) TOP_LVL_PARENT
2979 /*commented and modified above for bug14470110
2980 CASE
2981 WHEN oks_model_item_check(itm.dnz_chr_id,
2982 cs_item.customer_product_id) = 'Y' THEN
2983 -99
2984 WHEN (oks_model_check(itm.dnz_chr_id,
2985 cs_item.customer_product_id) = 'N' AND
2986 oks_config_item_check(itm.dnz_chr_id,
2987 cs_item.customer_product_id) = 'Y') THEN
2988 -99
2989 ELSE
2990 (CASE
2991 WHEN oks_model_check(itm.dnz_chr_id,
2992 cs_item.customer_product_id) = 'Y' THEN
2993 (SELECT a.customer_product_id
2994 FROM oks_ib_config_v a
2995 WHERE a.config_parent_id IS null
2996 START WITH a.customer_product_id =
2997 cs_item.customer_product_id
2998 CONNECT BY a.customer_product_id = PRIOR a.config_parent_id)
2999 WHEN oks_config_check(itm.dnz_chr_id,
3000 cs_item.customer_product_id) = 'Y' THEN
3001 (SELECT b.customer_product_id
3002 FROM oks_ib_config_v b
3003 WHERE LEVEL = 2
3004 START WITH b.customer_product_id =
3005 (SELECT a.customer_product_id
3006 FROM oks_ib_config_v a
3007 WHERE a.config_parent_id IS NULL
3008 START WITH a.customer_product_id =
3009 cs_item.customer_product_id
3010 CONNECT BY PRIOR a.config_parent_id =
3011 a.customer_product_id)
3012 CONNECT BY b.config_parent_id = PRIOR b.customer_product_id
3013 AND EXISTS (SELECT 1 FROM okc_k_items
3014 WHERE object1_id1 = cs_item.customer_product_id
3015 AND cle_id = itm.cle_id)
3016 AND ROWNUM = 1)
3017 ELSE
3018 (SELECT a.customer_product_id
3019 FROM oks_ib_config_v a
3020 WHERE a.config_parent_id IS null
3021 START WITH a.customer_product_id =
3022 cs_item.customer_product_id
3023 CONNECT BY a.customer_product_id = PRIOR a.config_parent_id)
3024 END) END top_lvl_parent*/
3025 /*CASE
3026 WHEN cs_item.config_parent_id IS NULL THEN
3027 -99
3028 ELSE
3029 (SELECT a.customer_product_id
3030 FROM oks_ib_config_v a
3031 WHERE a.config_parent_id IS null
3032 START WITH a.customer_product_id =
3033 cs_item.customer_product_id
3034 CONNECT BY a.customer_product_id = PRIOR
3035 a.config_parent_id)
3036 END top_lvl_parent,*/
3037
3038 FROM okc_k_items itm,
3039 okc_k_lines_b lin,
3040 OKX_SYSTEM_ITEMS_V SI,
3041 oks_ib_config_v cs_item,
3042 okc_statuses_b sts,
3043 CSI_SYSTEMS_TL CSTL
3044 WHERE itm.object1_id1 = cs_item.customer_product_id
3045 AND cs_item.system_id = cstl.system_id(+)
3046 AND CSTL.LANGUAGE(+) = UserEnv('LANG')
3047 AND lin.id = itm.cle_id
3048 AND lin.dnz_chr_id = p_contract_id
3049 AND SI.id1 = cs_item.INVENTORY_ITEM_ID
3050 AND SI.ORGANIZATION_ID = cs_item.inv_master_organization_id
3051 AND lin.sts_code = sts.code
3052 AND lin.lse_id NOT IN (1, 7, 8, 11, 35, 10, 12, 14, 19)
3053 UNION
3054 SELECT lin.id,
3055 lin.cle_id,
3056 lin.dnz_chr_id,
3057 To_Char(lin.id),
3058 To_Char(lin.cle_id),
3059 To_Char(lin.dnz_chr_id),
3060 lin.lse_id,
3061 (SELECT line_number
3062 FROM okc_k_lines_b line
3063 WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
3064 To_Number(NULL) customer_product_id,
3065 SI.name,
3066 SI.description,
3067 To_char(NULL) serial_number,
3068 To_Char(NULL) system_name,
3069 lin.sts_code old_Status,
3070 lin.sts_code undo_Status,
3071 sts.ste_code old_ste_code,
3072 lin.sts_code Status,
3073 sts.ste_code ste_code,
3074 lin.sts_code new_status,
3075 sts.ste_code new_ste_code,
3076 null,
3077 lin.start_date,
3078 lin.end_date,
3079 lin.start_date,
3080 lin.end_date,
3081 lin.price_negotiated,
3082 lin.start_date,
3083 lin.end_date,
3084 lin.price_negotiated,
3085 lin.price_negotiated,
3086 -99 "top_lvl_parent"
3087 FROM okc_k_items itm,
3088 okc_k_lines_b lin,
3089 OKX_SYSTEM_ITEMS_V SI,
3090 okc_statuses_b sts
3091 WHERE lin.id = itm.cle_id
3092 AND lin.dnz_chr_id = p_contract_id
3093 AND itm.object1_id1 = SI.id1
3094 AND SI.id2 = itm.object1_id2
3095 AND lin.sts_code = sts.code
3096 AND lin.lse_id = 7
3097 UNION
3098 SELECT lin.id,
3099 lin.cle_id,
3100 lin.dnz_chr_id,
3101 To_Char(lin.id),
3102 To_Char(lin.cle_id),
3103 To_Char(lin.dnz_chr_id),
3104 lin.lse_id,
3105 (SELECT line_number
3106 FROM okc_k_lines_b line
3107 WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
3108 To_Number(NULL) customer_product_id,
3109 SI.name,
3110 SI.description,
3111 To_char(NULL) serial_number,
3112 To_Char(NULL) system_name,
3113 lin.sts_code old_Status,
3114 lin.sts_code undo_Status,
3115 sts.ste_code old_ste_code,
3116 lin.sts_code Status,
3117 sts.ste_code ste_code,
3118 lin.sts_code new_status,
3119 sts.ste_code new_ste_code,
3120 null,
3121 lin.start_date,
3122 lin.end_date,
3123 lin.start_date,
3124 lin.end_date,
3125 lin.price_negotiated,
3126 lin.start_date,
3127 lin.end_date,
3128 lin.price_negotiated,
3129 lin.price_negotiated,
3130 -99 "top_lvl_parent"
3131 FROM okc_k_items itm,
3132 okc_k_lines_b lin,
3133 okx_parties_v SI,
3134 okc_statuses_b sts
3135 WHERE lin.id = itm.cle_id
3136 AND lin.dnz_chr_id = p_contract_id
3137 AND itm.object1_id1 = SI.id1
3138 AND SI.id2 = '#'
3139 AND lin.sts_code = sts.code
3140 AND lin.lse_id = 8
3141 UNION
3142 SELECT lin.id,
3143 lin.cle_id,
3144 lin.dnz_chr_id,
3145 To_Char(lin.id),
3146 To_Char(lin.cle_id),
3147 To_Char(lin.dnz_chr_id),
3148 lin.lse_id,
3149 (SELECT line_number
3150 FROM okc_k_lines_b line
3151 WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
3152 To_Number(NULL) customer_product_id,
3153 SI.party_site_number || '-' || NVL(SI.name, ' '),
3154 SI.description,
3155 To_char(NULL) serial_number,
3156 To_Char(NULL) system_name,
3157 lin.sts_code old_Status,
3158 lin.sts_code undo_Status,
3159 sts.ste_code old_ste_code,
3160 lin.sts_code Status,
3161 sts.ste_code ste_code,
3162 lin.sts_code new_status,
3163 sts.ste_code new_ste_code,
3164 null,
3165 lin.start_date,
3166 lin.end_date,
3167 lin.start_date,
3168 lin.end_date,
3169 lin.price_negotiated,
3170 lin.start_date,
3171 lin.end_date,
3172 lin.price_negotiated,
3173 lin.price_negotiated,
3174 -99 "top_lvl_parent"
3175 FROM okc_k_items itm,
3176 okc_k_lines_b lin,
3177 okx_party_sites_v SI,
3178 okc_statuses_b sts
3179 WHERE lin.id = itm.cle_id
3180 AND lin.dnz_chr_id = p_contract_id
3181 AND itm.object1_id1 = SI.id1
3182 AND SI.id2 = '#'
3183 AND lin.sts_code = sts.code
3184 AND lin.lse_id = 10
3185 UNION
3186 SELECT lin.id,
3187 lin.cle_id,
3188 lin.dnz_chr_id,
3189 To_Char(lin.id),
3190 To_Char(lin.cle_id),
3191 To_Char(lin.dnz_chr_id),
3192 lin.lse_id,
3193 (SELECT line_number
3194 FROM okc_k_lines_b line
3195 WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
3196 To_Number(NULL) customer_product_id,
3197 SI.name,
3198 SI.description,
3199 To_char(NULL) serial_number,
3200 cstl.name system_name,
3201 lin.sts_code old_Status,
3202 lin.sts_code undo_Status,
3203 sts.ste_code old_ste_code,
3204 lin.sts_code Status,
3205 sts.ste_code ste_code,
3206 lin.sts_code new_status,
3207 sts.ste_code new_ste_code,
3208 null,
3209 lin.start_date,
3210 lin.end_date,
3211 lin.start_date,
3212 lin.end_date,
3213 lin.price_negotiated,
3214 lin.start_date,
3215 lin.end_date,
3216 lin.price_negotiated,
3217 lin.price_negotiated,
3218 -99 "top_lvl_parent"
3219 FROM okc_k_items itm,
3220 okc_k_lines_b lin,
3221 okx_systems_v SI,
3222 okc_statuses_b sts,
3223 CSI_SYSTEMS_TL CSTL
3224 WHERE lin.id = itm.cle_id
3225 AND lin.dnz_chr_id = p_contract_id
3226 AND itm.object1_id1 = SI.id1
3227 AND SI.id2 = '#'
3228 AND lin.sts_code = sts.code
3229 AND lin.lse_id = 11
3230 AND si.id1 = cstl.system_id
3231 AND CSTL.LANGUAGE = UserEnv('LANG')
3232 UNION
3233 SELECT lin.id,
3234 lin.cle_id,
3235 lin.dnz_chr_id,
3236 To_Char(lin.id),
3237 To_Char(lin.cle_id),
3238 To_Char(lin.dnz_chr_id),
3239 lin.lse_id,
3240 (SELECT line_number
3241 FROM okc_k_lines_b line
3242 WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
3243 To_Number(NULL) customer_product_id,
3244 DECODE(c.account_name, NULL, p.party_name, c.account_name) name,
3245 c.account_number description,
3246 To_char(NULL) serial_number,
3247 To_Char(NULL) system_name,
3248 lin.sts_code old_Status,
3249 lin.sts_code undo_Status,
3250 sts.ste_code old_ste_code,
3251 lin.sts_code Status,
3252 sts.ste_code ste_code,
3253 lin.sts_code new_status,
3254 sts.ste_code new_ste_code,
3255 null,
3256 lin.start_date,
3257 lin.end_date,
3258 lin.start_date,
3259 lin.end_date,
3260 lin.price_negotiated,
3261 lin.start_date,
3262 lin.end_date,
3263 lin.price_negotiated,
3264 lin.price_negotiated,
3265 -99 "top_lvl_parent"
3266 FROM okc_k_items itm,
3267 okc_k_lines_b lin,
3268 hz_cust_accounts c,
3269 hz_parties p,
3270 okc_statuses_b sts
3271 WHERE p.party_id = c.party_id
3272 AND c.cust_account_id = itm.object1_id1
3273 AND lin.id = itm.cle_id
3274 AND lin.dnz_chr_id = p_contract_id
3275 AND lin.sts_code = sts.code
3276 AND lin.lse_id = 35
3277 ORDER BY line_number;
3278
3279 /*SELECT ROWNUM, CASE
3280 WHEN cs_item.config_parent_id IS NULL THEN
3281 'Star Item'
3282 ELSE
3283 (SELECT To_Char(a.customer_product_id)
3284 FROM oks_ib_config_v a
3285 WHERE a.config_parent_id IS null
3286 START WITH a.customer_product_id =
3287 cs_item.customer_product_id
3288 CONNECT BY a.customer_product_id = PRIOR a.config_parent_id)
3289 END config_parent_id,
3290 lin.id,
3291 lin.cle_id,
3292 lin.dnz_chr_id,
3293 (SELECT line_number
3294 FROM okc_k_lines_b line
3295 WHERE line.id = lin.cle_id) || '.' || lin.line_number line_number,
3296 cs_item.customer_product_id,
3297 SI.name,
3298 SI.description,
3299 cs_item.serial_number,
3300 lin.sts_code Status,
3301 sts.ste_code ste_code,
3302 lin.sts_code new_status,
3303 sts.ste_code new_ste_code,
3304 null,
3305 lin.start_date,
3306 lin.end_date,
3307 lin.price_negotiated,
3308 CASE
3309 WHEN cs_item.config_parent_id IS NULL THEN
3310 -99
3311 ELSE
3312 (SELECT a.customer_product_id
3313 FROM oks_ib_config_v a
3314 WHERE a.config_parent_id IS null
3315 START WITH a.customer_product_id =
3316 cs_item.customer_product_id
3317 CONNECT BY a.customer_product_id = PRIOR a.config_parent_id)
3318 END top_lvl_parent,
3319 'N',
3320 'N'
3321 FROM okc_k_items itm,
3322 okc_k_lines_b lin,
3323 OKX_SYSTEM_ITEMS_V SI,
3324 oks_ib_config_v cs_item,
3325 okc_statuses_b sts
3326 WHERE itm.object1_id1 = cs_item.customer_product_id
3327 AND lin.id = itm.cle_id
3328 AND lin.dnz_chr_id = p_contract_id
3329 AND SI.id1 = cs_item.INVENTORY_ITEM_ID
3330 AND SI.ORGANIZATION_ID = cs_item.inv_master_organization_id
3331 AND lin.sts_code = sts.code
3332 ORDER BY To_Number((SELECT line_number
3333 FROM okc_k_lines_b line
3334 WHERE line.id = lin.cle_id)),To_Number(lin.line_number); */
3335
3336 UPDATE oks_sublines_temp
3337 SET top_lvl_parent = -99
3338 WHERE top_lvl_parent <> -99
3339 AND dnz_chr_id = p_contract_id
3340 AND top_lvl_parent NOT IN
3341 (SELECT customer_product_id
3342 FROM oks_sublines_temp
3343 WHERE dnz_chr_id = p_contract_id
3344 AND Nvl(top_lvl_parent, -99) = -99
3345 AND customer_product_id IS NOT NULL);
3346
3347 COMMIT;
3348 END IF;
3349
3350 /*flag to indicate that the processing is complete*/
3351 x_return_status := 'S';
3352
3353 END populate_temp_tbl;
3354
3355 PROCEDURE MASS_UPDATE_CASCADE_SEL(p_contract_id NUMBER, x_error_records OUT NOCOPY VARCHAR2 ) IS
3356
3357 CURSOR c_get_hdr_attrs IS
3358 SELECT cascade_selection,
3359 global_start_date,
3360 global_end_date,
3361 global_price_negotiated,
3362 global_status,
3363 (SELECT ste_code FROM okc_statuses_v WHERE code = global_status AND ROWNUM=1) global_status_code,
3364 global_reason_code,
3365 global_comments
3366 FROM oks_headers_temp
3367 WHERE chr_id = p_contract_id;
3368
3369 l_cascade VARCHAR2(1);
3370 l_global_start_date DATE;
3371 l_global_end_date DATE;
3372 l_global_price NUMBER;
3373 l_global_new_status VARCHAR2(30);
3374 l_global_status_code VARCHAR2(30);
3375 l_global_reason_code VARCHAR2(60);
3376 l_global_comments VARCHAR2(2000);
3377
3378 CURSOR c_sel_lines IS
3379 SELECT id, start_date, end_date, new_status, reason_code, comments, old_start_date, old_end_date, old_status
3380 FROM oks_lines_temp
3381 WHERE dnz_chr_id = p_contract_id
3382 AND (Nvl(select_flag,'N') = 'Y' OR Nvl(cascade_flag,'N') = 'Y');
3383
3384
3385 CURSOR c_sel_config is
3386 SELECT id,
3387 customer_product_id,
3388 start_date,
3389 end_date,
3390 price_negotiated,
3391 new_status,
3392 reason_code,
3393 comments,
3394 old_start_date,
3395 old_end_date,
3396 old_status,
3397 old_price_negotiated
3398 FROM oks_sublines_temp sublines
3399 WHERE dnz_chr_id = p_contract_id
3400 AND top_lvl_parent = -99
3401 AND (
3402 Nvl(select_flag,'N') = 'Y'
3403 OR Nvl(cascade_flag,'N') = 'Y'
3404 OR EXISTS (SELECT 1 FROM oks_lines_temp lines
3405 WHERE lines.id = sublines.cle_id
3406 AND lines.dnz_chr_id = sublines.dnz_chr_id
3407 AND Nvl(lines.cascade_flag,'N') = 'Y' )
3408 );
3409
3410
3411 CURSOR c_sel_component is
3412 SELECT id,
3413 customer_product_id,
3414 start_date,
3415 end_date,
3416 price_negotiated,
3417 new_status,
3418 reason_code,
3419 comments,
3420 old_start_date,
3421 old_end_date,
3422 old_status,
3423 old_price_negotiated
3424 FROM oks_sublines_temp sublineschild
3425 WHERE dnz_chr_id = p_contract_id
3426 AND top_lvl_parent <> -99
3427 AND (
3428 Nvl(select_flag,'N') = 'Y'
3429 OR EXISTS (SELECT 1 FROM oks_lines_temp lines
3430 WHERE lines.id = sublineschild.cle_id
3431 AND lines.dnz_chr_id = sublineschild.dnz_chr_id
3432 AND Nvl(lines.cascade_flag,'N') = 'Y' )
3433 OR EXISTS (SELECT 1 FROM oks_sublines_temp sublines
3434 WHERE sublines.customer_product_id = sublineschild.top_lvl_parent
3435 AND sublines.dnz_chr_id = sublineschild.dnz_chr_id
3436 AND sublines.cle_id = sublineschild.cle_id
3437 AND Nvl(sublines.cascade_flag,'N') = 'Y' )
3438 );
3439
3440
3441 CURSOR c_modified IS
3442 WITH a AS (select Nvl(PRECISION,2) prc
3443 from FND_CURRENCIES c
3444 where currency_code = (SELECT currency_code
3445 FROM okc_k_headers_all_b WHERE ID = p_contract_id AND rownum=1))
3446 SELECT tmp.id,
3447 Decode(tmp.start_date, lin.start_date, 'N', 'Y') start_date_change,
3448 Decode(tmp.end_date, lin.end_date, 'N', 'Y') end_date_change,
3449 Decode(Round(tmp.price_negotiated,(SELECT prc FROM a)), Round(lin.price_negotiated,(SELECT prc FROM a)), 'N', 'Y') Price_change,
3450 Decode(tmp.new_status,
3451 (SELECT sts.code
3452 FROM okc_statuses_b sts
3453 WHERE sts.code = lin.sts_code),
3454 'N',
3455 'Y') status_change
3456 FROM oks_sublines_temp tmp, okc_k_lines_b lin
3457 WHERE tmp.id = lin.id
3458 AND tmp.dnz_chr_id = p_contract_id;
3459
3460
3461 CURSOR c_modified_line IS
3462 SELECT tmp.id,
3463 Decode(tmp.start_date, lin.start_date, 'N', 'Y') start_date_change,
3464 Decode(tmp.end_date, lin.end_date, 'N', 'Y') end_date_change,
3465 Decode(tmp.new_status,
3466 (SELECT sts.code
3467 FROM okc_statuses_b sts
3468 WHERE sts.code = lin.sts_code),
3469 'N',
3470 'Y') status_change
3471 FROM oks_lines_temp tmp, okc_k_lines_b lin
3472 WHERE tmp.id = lin.id
3473 AND tmp.dnz_chr_id = p_contract_id;
3474
3475 CURSOR c_get_error_records IS
3476 SELECT 'SOME_WITH_ERROR'
3477 FROM dual
3478 WHERE EXISTS (SELECT 1
3479 FROM oks_lines_temp
3480 WHERE dnz_chr_id = p_contract_id
3481 AND (start_dt_error_flag = 'Y' OR end_dt_error_flag = 'Y' OR status_error_flag = 'Y')
3482 UNION
3483 SELECT 1
3484 FROM oks_sublines_temp
3485 WHERE dnz_chr_id = p_contract_id
3486 AND (start_dt_error_flag = 'Y' OR end_dt_error_flag = 'Y' OR status_error_flag = 'Y'));
3487
3488 l_sel_lines_exist VARCHAR2(1);
3489 l_retcode VARCHAR2(1);
3490 l_error_records VARCHAR2(50):= 'NONE';
3491
3492 BEGIN
3493
3494 OPEN c_get_hdr_attrs;
3495 FETCH c_get_hdr_attrs
3496 INTO l_cascade, l_global_start_date, l_global_end_date, l_global_price, l_global_new_status,l_global_status_code, l_global_reason_code, l_global_comments;
3497 CLOSE c_get_hdr_attrs;
3498
3499 FOR line IN c_sel_lines LOOP
3500
3501 UPDATE oks_lines_temp
3502 SET start_date = nvl(l_global_start_date,start_date),
3503 end_date = Nvl(l_global_end_date,end_date),
3504 new_status = Nvl(l_global_new_status,new_status),
3505 new_ste_code = Nvl(l_global_status_code,new_ste_code),
3506 reason_code = Nvl(l_global_reason_code, reason_code),
3507 comments = Nvl(l_global_comments, comments)
3508 WHERE dnz_chr_id = p_contract_id
3509 AND id = line.id;
3510
3511 END LOOP;
3512
3513 FOR subline IN c_sel_config LOOP
3514
3515 UPDATE oks_sublines_temp
3516 SET start_date = nvl(l_global_start_date,start_date),
3517 end_date = Nvl(l_global_end_date,end_date),
3518 price_negotiated = Nvl(l_global_price, price_negotiated),
3519 new_status = Nvl(l_global_new_status,new_status),
3520 new_ste_code = Nvl(l_global_status_code,new_ste_code),
3521 reason_code = Nvl(l_global_reason_code, reason_code),
3522 comments = Nvl(l_global_comments, comments)
3523 WHERE dnz_chr_id = p_contract_id
3524 AND id = subline.id;
3525
3526 END LOOP;
3527
3528 FOR sublinechild IN c_sel_component LOOP
3529
3530 UPDATE oks_sublines_temp
3531 SET start_date = nvl(l_global_start_date,start_date),
3532 end_date = Nvl(l_global_end_date,end_date),
3533 price_negotiated = Nvl(l_global_price, price_negotiated),
3534 new_status = Nvl(l_global_new_status,new_status),
3535 new_ste_code = Nvl(l_global_status_code,new_ste_code),
3536 reason_code = Nvl(l_global_reason_code, reason_code),
3537 comments = Nvl(l_global_comments, comments)
3538 WHERE dnz_chr_id = p_contract_id
3539 AND id = sublinechild.id;
3540
3541 END LOOP;
3542
3543
3544 FOR modified IN c_modified LOOP
3545
3546
3547 UPDATE oks_sublines_temp tmp
3548 SET start_dt_eff_change_flag = modified.start_date_change,
3549 end_dt_eff_change_flag = modified.end_date_change,
3550 price_change_flag = modified.price_change,
3551 status_change_flag = modified.status_change
3552 WHERE tmp.dnz_chr_id = p_contract_id
3553 AND tmp.id = modified.id;
3554
3555 END LOOP;
3556
3557 FOR modified IN c_modified_line LOOP
3558
3559 UPDATE oks_lines_temp tmp
3560 SET start_dt_eff_change_flag = modified.start_date_change,
3561 end_dt_eff_change_flag = modified.end_date_change,
3562 status_change_flag = modified.status_change
3563 WHERE tmp.dnz_chr_id = p_contract_id
3564 AND tmp.id = modified.id;
3565
3566 END LOOP;
3567
3568 UPDATE oks_lines_temp
3569 SET start_dt_error_flag = 'N',
3570 end_dt_error_flag = 'N',
3571 status_error_flag = 'N',
3572 st_dt_err_message = NULL,
3573 end_dt_err_message = NULL,
3574 status_err_message = NULL
3575 WHERE dnz_chr_id = p_contract_id;
3576
3577 UPDATE oks_sublines_temp
3578 SET start_dt_error_flag = 'N',
3579 end_dt_error_flag = 'N',
3580 status_error_flag = 'N',
3581 st_dt_err_message = NULL,
3582 end_dt_err_message = NULL,
3583 status_err_message = NULL
3584 WHERE dnz_chr_id = p_contract_id;
3585
3586 vld_massupd_data_prc(l_retcode, p_contract_id);
3587
3588 UPDATE oks_lines_temp
3589 SET undo_start_date = old_start_date,
3590 old_start_date = start_date
3591 WHERE dnz_chr_id = p_contract_id
3592 AND start_dt_eff_change_flag = 'Y'
3593 AND start_dt_error_flag = 'N';
3594
3595 UPDATE oks_lines_temp
3596 SET undo_start_date = old_start_date
3597 WHERE dnz_chr_id = p_contract_id
3598 AND start_dt_eff_change_flag = 'Y'
3599 AND start_dt_error_flag = 'Y';
3600
3601 UPDATE oks_lines_temp
3602 SET undo_end_date = old_end_date,
3603 old_end_date = end_date
3604 WHERE dnz_chr_id = p_contract_id
3605 AND end_dt_eff_change_flag = 'Y'
3606 AND end_dt_error_flag = 'N';
3607
3608 UPDATE oks_lines_temp
3609 SET undo_end_date = old_end_date
3610 WHERE dnz_chr_id = p_contract_id
3611 AND end_dt_eff_change_flag = 'Y'
3612 AND end_dt_error_flag = 'Y';
3613
3614 UPDATE oks_lines_temp
3615 SET undo_status = old_status,
3616 old_status = status
3617 WHERE dnz_chr_id = p_contract_id
3618 AND status_change_flag = 'Y'
3619 AND status_error_flag = 'N';
3620
3621 UPDATE oks_lines_temp
3622 SET undo_status = old_status
3623 WHERE dnz_chr_id = p_contract_id
3624 AND status_change_flag = 'Y'
3625 AND status_error_flag = 'Y';
3626
3627 UPDATE oks_sublines_temp
3628 SET undo_start_date = old_start_date,
3629 old_start_date = start_date
3630 WHERE dnz_chr_id = p_contract_id
3631 AND start_dt_eff_change_flag = 'Y'
3632 AND start_dt_error_flag = 'N';
3633
3634 UPDATE oks_sublines_temp
3635 SET undo_start_date = old_start_date
3636 WHERE dnz_chr_id = p_contract_id
3637 AND start_dt_eff_change_flag = 'Y'
3638 AND start_dt_error_flag = 'Y';
3639
3640 UPDATE oks_sublines_temp
3641 SET undo_end_date = old_end_date,
3642 old_end_date = end_date
3643 WHERE dnz_chr_id = p_contract_id
3644 AND end_dt_eff_change_flag = 'Y'
3645 AND end_dt_error_flag = 'N';
3646
3647 UPDATE oks_sublines_temp
3648 SET undo_end_date = old_end_date
3649 WHERE dnz_chr_id = p_contract_id
3650 AND end_dt_eff_change_flag = 'Y'
3651 AND end_dt_error_flag = 'Y';
3652
3653 UPDATE oks_sublines_temp
3654 SET undo_status = old_status,
3655 old_status = status
3656 WHERE dnz_chr_id = p_contract_id
3657 AND status_change_flag = 'Y'
3658 AND status_error_flag = 'N';
3659
3660 UPDATE oks_sublines_temp
3661 SET undo_status = old_status
3662 WHERE dnz_chr_id = p_contract_id
3663 AND status_change_flag = 'Y'
3664 AND status_error_flag = 'Y';
3665
3666 UPDATE oks_sublines_temp
3667 SET undo_price_negotiated = old_price_negotiated,
3668 old_price_negotiated = price_negotiated
3669 WHERE dnz_chr_id = p_contract_id
3670 AND price_change_flag = 'Y'
3671 AND top_lvl_parent <> -99;
3672
3673 UPDATE oks_headers_temp
3674 SET global_start_date = NULL,
3675 global_end_date = NULL,
3676 global_price_negotiated = NULL,
3677 global_status = NULL,
3678 global_reason_code = NULL,
3679 global_comments = NULL,
3680 saved_flag = 'Y'
3681 WHERE chr_id = p_contract_id;
3682
3683 UPDATE oks_sublines_temp
3684 SET select_flag = NULL,
3685 cascade_flag = NULL
3686 WHERE dnz_chr_id = p_contract_id;
3687
3688 UPDATE oks_lines_temp lin
3689 SET select_flag = NULL,
3690 cascade_flag = NULL,
3691 price_negotiated = (SELECT Sum(price_negotiated)
3692 FROM oks_sublines_temp sub
3693 WHERE sub.cle_id = lin.id)
3694 WHERE dnz_chr_id = p_contract_id;
3695
3696 OPEN c_get_error_records;
3697 FETCH c_get_error_records INTO l_error_records;
3698 CLOSE c_get_error_records;
3699
3700 x_error_records:= l_error_records;
3701
3702 COMMIT;
3703
3704 END MASS_UPDATE_CASCADE_SEL;
3705
3706 PROCEDURE MASS_UPDATE_SUBMIT(p_contract_id IN NUMBER, x_req_id OUT NOCOPY VARCHAR2) IS
3707
3708 l_request_id NUMBER;
3709
3710 BEGIN
3711
3712 l_request_id := fnd_request.submit_request(application => 'OKS',
3713 program => 'OKSLAUNCHMASSUPD',
3714 description => 'Service Contracts Mass Update',
3715 start_time => NULL,
3716 sub_request => FALSE,
3717 argument1 => p_contract_id
3718 );
3719
3720 UPDATE oks_headers_temp
3721 SET saved_flag = 'N', request_id = l_request_id
3722 WHERE chr_id = p_contract_id;
3723
3724 COMMIT;
3725
3726 x_req_id := l_request_id;
3727
3728 END MASS_UPDATE_SUBMIT;
3729
3730 PROCEDURE delete_changes(p_contract_id NUMBER) IS
3731 BEGIN
3732
3733 DELETE FROM oks_lines_temp WHERE dnz_chr_id = p_contract_id;
3734 DELETE FROM oks_sublines_temp WHERE dnz_chr_id = p_contract_id;
3735 DELETE FROM oks_headers_temp WHERE Chr_id = p_contract_id;
3736 COMMIT;
3737
3738 END delete_changes;
3739
3740 END OKS_MASS_UPD_PVT;