[Home] [Help]
PACKAGE BODY: APPS.OKS_ATTR_DEFAULTS_PVT
Source
1 Package body OKS_ATTR_DEFAULTS_PVT AS
2 /* $Header: OKSRDFTB.pls 120.33.12020000.3 2012/12/28 16:31:40 mchandak ship $*/
3 ---------------------------------------------------------------------------
4 -- GLOBAL DATASTRUCTURES
5 ---------------------------------------------------------------------------
6 --Fix for Bug# 3542273
7 FUNCTION FND_MEANING(P_LOOKUP_CODE IN VARCHAR2,P_LOOKUP_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
8 CURSOR C_LOOKUP_CODE IS
9 SELECT MEANING FROM FND_LOOKUPS
10 WHERE LOOKUP_CODE = P_LOOKUP_CODE
11 AND LOOKUP_TYPE = P_LOOKUP_TYPE;
12
13 V_FOUND BOOLEAN := FALSE;
14 V_NAME VARCHAR2(150);
15 BEGIN
16 FOR C_CUR_MEANING IN C_LOOKUP_CODE
17 LOOP
18 V_FOUND := TRUE;
19 V_NAME := C_CUR_MEANING.MEANING;
20 EXIT;
21 END LOOP;
22
23 IF (V_FOUND = TRUE)
24 THEN
25 RETURN(V_NAME);
26 ELSE
27 RETURN(NULL);
28 END IF;
29 END FND_MEANING;
30 --Fix for Bug# 3542273
31
32 PROCEDURE validate_date
33 ( p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
35 p_hdr_id IN NUMBER,
36 p_top_line_id IN NUMBER,
37 p_sub_line_id IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 x_flag OUT NOCOPY BOOLEAN ) IS
42
43 /******************************************
44 Note 1: For header to top lines
45 p_hdr_id = Header id .
46 p_top_line_id = Top line id.
47 p_sub_line_id = null
48
49 Note 2: For top line to sub lines
50 p_hdr_id = null.
51 p_top_line_id = Top line id.
52 p_sub_line_id = Sub line id.
53 *******************************************/
54
55 CURSOR L_GET_TOP_LINE_DATE_CSR ( P_TOP_LINE_ID IN NUMBER ) IS
56 SELECT TRUNC(MIN(BCL.DATE_BILLED_FROM)) DATE_BILLED_FROM
57 , TRUNC(MAX(BCL.DATE_BILLED_TO)) DATE_BILLED_TO
58 FROM OKS_BILL_CONT_LINES BCL
59 WHERE BCL.CLE_ID = P_TOP_LINE_ID ;
60
61 L_GET_TOP_LINE_DATE_REC L_GET_TOP_LINE_DATE_CSR%ROWTYPE;
62
63 CURSOR L_GET_HDR_DATES_CSR(P_HDR_ID IN NUMBER ) IS
64 SELECT TRUNC(START_DATE) START_DATE
65 , TRUNC(END_DATE) END_DATE
66 FROM OKC_K_HEADERS_B
67 WHERE ID = P_HDR_ID ;
68
69 L_GET_HDR_DATES_REC L_GET_HDR_DATES_CSR%ROWTYPE;
70
71 CURSOR L_GET_SUB_LINE_DATE_CSR ( P_SUB_LINE_ID IN NUMBER ) IS
72 SELECT TRUNC(MIN(BSL.DATE_BILLED_FROM)) DATE_BILLED_FROM
73 , TRUNC(MAX(BSL.DATE_BILLED_TO)) DATE_BILLED_TO
74 FROM OKS_BILL_SUB_LINES BSL
75 WHERE BSL.CLE_ID = P_SUB_LINE_ID;
76
77 L_GET_SUB_LINE_DATE_REC L_GET_SUB_LINE_DATE_CSR%ROWTYPE;
78
79 CURSOR L_GET_TOP_LINE_DATES_CSR(P_TOP_LINE_ID IN NUMBER ) IS
80 SELECT TRUNC(START_DATE) START_DATE
81 , TRUNC(END_DATE) END_DATE
82 FROM OKC_K_LINES_B
83 WHERE ID = P_TOP_LINE_ID;
84
85 L_GET_TOP_LINE_DATES_REC L_GET_TOP_LINE_DATES_CSR%ROWTYPE;
86
87
88 BEGIN
89 x_flag := TRUE ;
90 x_return_status := OKC_API.G_RET_STS_SUCCESS;
91 -- Start of if for Header to Top line validation.
92
93 If (p_hdr_id is not null and p_hdr_id > 0 ) and (p_top_line_id is not null
94 and p_top_line_id > 0 ) and (p_sub_line_id is null) then
95 -- SQL call to get the max and min top line billing dates
96 OPEN L_GET_TOP_LINE_DATE_CSR(p_top_line_id);
97 FETCH L_GET_TOP_LINE_DATE_CSR INTO L_GET_TOP_LINE_DATE_REC;
98 CLOSE L_GET_TOP_LINE_DATE_CSR;
99
100 -- SQL call to get header start date and end date
101 OPEN L_GET_HDR_DATES_CSR(P_HDR_ID);
102 FETCH L_GET_HDR_DATES_CSR INTO L_GET_HDR_DATES_REC;
103 CLOSE L_GET_HDR_DATES_CSR;
104
105 -- If billed, and the start date of hdr and top line dosen't match then
106 --return false.
107 IF L_GET_TOP_LINE_DATE_REC.DATE_BILLED_FROM IS NOT NULL THEN
108 IF L_GET_TOP_LINE_DATE_REC.DATE_BILLED_FROM <>
109 L_GET_HDR_DATES_REC.START_DATE THEN
110 X_FLAG := FALSE;
111 RETURN;
112 END IF;
113 END IF;
114
115 -- If billed, and the end date of hdr < max bill to date of top line
116 --then return false.
117 IF L_GET_TOP_LINE_DATE_REC.DATE_BILLED_TO IS NOT NULL THEN
118 IF L_GET_TOP_LINE_DATE_REC.DATE_BILLED_TO >
119 L_GET_HDR_DATES_REC.END_DATE THEN
120 X_FLAG := FALSE;
121 RETURN;
122 END IF;
123 END IF;
124 RETURN;
125 End If;
126
127
128 -- Start of if for Top line to sub line validation.
129 If (p_top_line_id is not null and p_top_line_id > 0 ) and (p_sub_line_id is
130 not null and p_sub_line_id > 0) and (p_hdr_id is null ) then
131
132 -- SQL call to get the max and min sub line billing dates
133 OPEN L_GET_SUB_LINE_DATE_CSR(P_SUB_LINE_ID);
134 FETCH L_GET_SUB_LINE_DATE_CSR INTO L_GET_SUB_LINE_DATE_REC;
135 CLOSE L_GET_SUB_LINE_DATE_CSR;
136
137 -- SQL call to get top line start date and end date
138 OPEN L_GET_TOP_LINE_DATES_CSR(P_TOP_LINE_ID);
139 FETCH L_GET_TOP_LINE_DATES_CSR INTO L_GET_TOP_LINE_DATES_REC;
140 CLOSE L_GET_TOP_LINE_DATES_CSR;
141
142 -- If billed, and the start date of top and sub line dosen't match then
143 --return false.
144
145 IF L_GET_SUB_LINE_DATE_REC.DATE_BILLED_FROM IS NOT NULL THEN
146 IF L_GET_SUB_LINE_DATE_REC.DATE_BILLED_FROM <>
147 L_GET_TOP_LINE_DATES_REC.START_DATE THEN
148 X_FLAG := FALSE;
149
150 RETURN;
151 END IF;
152 END IF;
153
154 -- If billed and the end date of top line < max bill to date of sub line
155 --then return false.
156 IF L_GET_TOP_LINE_DATE_REC.DATE_BILLED_TO IS NOT NULL THEN
157 IF L_GET_SUB_LINE_DATE_REC.DATE_BILLED_TO >
158 L_GET_TOP_LINE_DATES_REC.END_DATE THEN
159 X_FLAG := FALSE;
160
161 RETURN;
162 END IF;
163 END IF;
164 RETURN;
165 END IF;
166 x_flag := FALSE;
167 x_return_status := OKC_API.G_RET_STS_ERROR;
168
169 EXCEPTION
170 WHEN OTHERS THEN
171
172 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
173 OKC_API.set_message('OKS',
174 'OKC_CONTRACTS_UNEXP_ERROR',
175 'SQLcode',
176 SQLCODE,
177 'SQLerrm',
178 SQLERRM);
179
180 END;
181
182
183 PROCEDURE update_line
184 (
185 p_clev_tbl IN okc_contract_pub.clev_tbl_type
186 ,x_clev_tbl OUT NOCOPY okc_contract_pub.clev_tbl_type
187 ,x_return_status OUT NOCOPY Varchar2
188 ,x_msg_count OUT NOCOPY Number
189 ,x_msg_data OUT NOCOPY Varchar2
190 )
191 IS
192 l_api_version CONSTANT NUMBER := 1.0;
193 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
194 l_return_status VARCHAR2(1);
195 l_msg_count NUMBER;
196 l_msg_data VARCHAR2(2000);
197 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
198 l_clev_tbl_out okc_contract_pub.clev_tbl_type;
199 l_msg_index_out NUMBER;
200 i NUMBER; -- 5381082
201 BEGIN
202 x_return_status := l_return_status;
203 l_clev_tbl_in := p_clev_tbl;
204
205 -- Bug 5381082--
206 IF l_clev_tbl_in.count >0
207 THEN
208 i := l_clev_tbl_in.FIRST;
209 LOOP
210 l_clev_tbl_in(i).validate_yn :='N';
211 EXIT WHEN (i = l_clev_tbl_in.LAST);
212 i := l_clev_tbl_in.NEXT(i);
213 END LOOP;
214 END IF;
215 -- Bug 5381082--
216
217 okc_contract_pub.update_contract_line
218 (
219 p_api_version => l_api_version,
220 p_init_msg_list => l_init_msg_list,
221 x_return_status => l_return_status,
222 x_msg_count => l_msg_count,
223 x_msg_data => l_msg_data,
224 p_restricted_update => 'F',
225 p_clev_tbl => l_clev_tbl_in,
226 x_clev_tbl => l_clev_tbl_out
227 );
228
229 If l_return_status = 'S'
230 Then
231 x_clev_tbl := l_clev_tbl_out;
232 x_return_status := l_return_status;
233 Else
234 x_return_status := l_return_status;
235 End If;
236
237 Exception
238 When Others Then
239 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
240 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
241 END update_line;
242
243 FUNCTION CHECK_LINE_PARTY_ROLE(p_dnz_chr_id IN NUMBER,
244 P_Cle_Id IN NUMBER,
245 p_party_id IN NUMBER) RETURN NUMBER
246 IS
247 CURSOR l_role_csr IS
248 SELECT ID
249 FROM OKC_K_PARTY_ROLES_B
250 WHERE DNZ_CHR_ID = p_dnz_chr_id
251 AND CLE_ID = P_Cle_Id
252 AND OBJECT1_ID1 = p_party_id ; -- Bug 5219132
253
254
255 l_cle_id Number;
256 l_cpl_id Number;
257 l_chr_id Number;
258 BEGIN
259
260 OPEN l_role_csr;
261 FETCH l_role_csr INTO l_cpl_id;
262 CLOSE l_role_csr;
263
264 IF l_cpl_id IS NULL THEN
265 RETURN(l_cpl_id);
266 ELSE
267 RETURN(l_cpl_id);
268 END IF;
269 END CHECK_LINE_PARTY_ROLE;
270
271 PROCEDURE CREATE_LINE_PARTY_ROLE(p_dnz_chr_id IN NUMBER,
272 p_cle_id IN NUMBER,
273 P_billto_Id IN NUMBER,
274 p_can_id IN NUMBER,
275 x_cpl_id OUT NOCOPY NUMBER,
276 x_return_status OUT NOCOPY VARCHAR2) IS
277
278 l_can_id NUMBER;
279 l_msg_index NUMBER;
280
281 CURSOR get_party(p_can_id in number) IS
282 SELECT party_id
283 FROM okx_customer_accounts_v
284 WHERE id1=p_can_id;
285 l_party_id NUMBER;
286
287 CURSOR role_csr IS
288 SELECT rle_code,OBJECT1_ID1,object1_id2,JTOT_OBJECT1_CODE
289 FROM OKC_K_PARTY_ROLES_B
290 WHERE object1_id1= l_party_id
291 and dnz_chr_id = p_dnz_chr_id;
292
293 Cursor get_contract_type IS
294 Select SCS_CODE from OKC_K_HEADERS_B
295 where id = p_dnz_chr_id;
296
297
298 role_csr_Rec role_csr%rowtype;
299 l_api_version CONSTANT NUMBER := 1.0;
300 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
301 l_return_status VARCHAR2(1);
302 l_msg_count NUMBER;
303 l_msg_data VARCHAR2(2000);
304 l_msg_index_out NUMBER;
305
306 l_cplv_tbl_in okc_contract_party_pub.cplv_tbl_type;
307 l_cplv_tbl_out okc_contract_party_pub.cplv_tbl_type;
308
309 l_type OKC_K_HEADERS_B.SCS_CODE%TYPE;
310 l_rle_code VARCHAR2(2000);
311 BEGIN
312 l_can_id := p_can_id;
313 OPEN get_party(l_can_id) ;
314 FETCH get_party INTO l_party_id;
315 CLOSE get_party ;
316
317 open get_contract_type;
318 fetch get_contract_type into l_type;
319 close get_contract_type;
320
321 /* OPEN role_csr;
322 FETCH role_csr INTO role_csr_rec;
323 CLOSE role_csr; */
324
325 IF l_type = 'SUBSCRIPTION'
326 THEN
327 l_rle_code := 'SUBSCRIBER';
328 ELSE
329 l_rle_code:= 'CUSTOMER';
330 END IF;
331
332 l_cplv_tbl_in(1).sfwt_flag := 'N';
333 l_cplv_tbl_in(1).cle_id := p_cle_id;
334 l_cplv_tbl_in(1).dnz_chr_id := p_dnz_chr_id;
335 l_cplv_tbl_in(1).chr_id := null;
336 -- l_cplv_tbl_in(1).rle_code := role_csr_rec.RLE_CODE;
337 l_cplv_tbl_in(1).rle_code := l_rle_code;
338 --- l_cplv_tbl_in(1).OBJECT1_ID1 := role_csr_rec.OBJECT1_ID1;
339 l_cplv_tbl_in(1).OBJECT1_ID1 := l_party_id;
340 -- l_cplv_tbl_in(1).object1_id2 := role_csr_rec.object1_id2;
341 l_cplv_tbl_in(1).object1_id2 := '#';
342 -- l_cplv_tbl_in(1).JTOT_OBJECT1_CODE := role_csr_rec.JTOT_OBJECT1_CODE;
343 l_cplv_tbl_in(1).JTOT_OBJECT1_CODE := 'OKX_PARTY';
344 l_cplv_tbl_in(1).cognomen := Null;
345 l_cplv_tbl_in(1).SMALL_BUSINESS_FLAG := 'N';
346 l_cplv_tbl_in(1).women_owned_flag := 'N';
347 l_cplv_tbl_in(1).alias := Null;
348
349 okc_contract_party_pub.create_k_party_role
350 (p_api_version => l_api_version,
351 p_init_msg_list => l_init_msg_list,
352 x_return_status => l_return_status,
353 x_msg_count => l_msg_count,
354 x_msg_data => l_msg_data,
355 p_cplv_tbl => l_cplv_tbl_in,
356 x_cplv_tbl => l_cplv_tbl_out);
357 -- Bug 5227077 --
358 x_return_status :=l_return_status;
359 IF l_return_status = FND_API.G_RET_STS_SUCCESS
360 THEN
361 IF l_cplv_tbl_out.COUNT > 0 THEN
362 x_cpl_id := l_cplv_tbl_out(l_cplv_tbl_out.FIRST).id;
363 END IF;
364 END IF;
365
366 -- Bug 5227077 --
367
368 END CREATE_LINE_PARTY_ROLE;
369
370 FUNCTION CHECK_LINE_CONTACT(p_dnz_chr_id IN NUMBER,
371 P_CPL_Id IN NUMBER
372 ) RETURN NUMBER
373 IS
374
375 CURSOR l_contact_csr IS
376 SELECT ID
377 FROM OKC_CONTACTS
378 WHERE DNZ_CHR_ID = p_dnz_chr_id
379 AND CPL_ID = P_CPL_Id;
380
381 l_cle_id Number;
382 l_cpl_id Number;
383 l_chr_id Number;
384 l_contact_id Number;
385
386 BEGIN
387 OPEN l_contact_csr;
388 FETCH l_contact_csr INTO l_contact_id;
389 CLOSE l_contact_csr;
390 IF l_contact_id IS NULL THEN
391 RETURN(l_contact_id);
392 ELSE
393 RETURN(l_contact_id);
394 END IF;
395 END CHECK_LINE_CONTACT;
396
397 FUNCTION CHECK_LINE_BILL_CONTACT(p_dnz_chr_id IN NUMBER,
398 P_CPL_Id IN NUMBER,
399 p_jtot_code IN VARCHAR2,
400 p_cro_code IN VARCHAR2) RETURN NUMBER
401 IS
402
403 CURSOR l_contact_csr IS
404 SELECT ID
405 FROM OKC_CONTACTS
406 WHERE DNZ_CHR_ID = p_dnz_chr_id
407 AND CPL_ID = P_CPL_Id
408 AND JTOT_OBJECT1_CODE =p_jtot_code
409 AND cro_code =p_cro_code ;
410
411 l_cle_id Number;
412 l_cpl_id Number;
413 l_chr_id Number;
414 l_contact_id Number;
415
416 BEGIN
417 OPEN l_contact_csr;
418 FETCH l_contact_csr INTO l_contact_id;
419 CLOSE l_contact_csr;
420 IF l_contact_id IS NULL THEN
421 RETURN(l_contact_id);
422 ELSE
423 RETURN(l_contact_id);
424 END IF;
425 END CHECK_LINE_BILL_CONTACT;
426
427 PROCEDURE Default_header_to_lines
428 (header_lines_tbl IN header_lines_tbl_type
429 ,X_return_status OUT NOCOPY Varchar2
430 ,x_msg_tbl IN OUT NOCOPY attr_msg_tbl_type)
431 IS
432
433 Cursor cur_header_dates(p_chr_id in NUMBER) is
434 select start_date,
435 end_date,
436 inv_organization_id,
437 authoring_org_id
438 from okc_k_headers_v
439 where id = p_chr_id;
440 header_dates_rec cur_header_dates%ROWTYPE;
441
442 Cursor cur_line_dates(p_cle_id in NUMBER) is
443 select start_date,
444 end_date,
445 lse_id
446 from okc_k_lines_b
447 where id = p_cle_id;
448 line_dates_rec cur_line_dates%ROWTYPE;
449
450 Cursor LineCov_cur(p_cle_id IN Number) Is
451 Select id
452 From OKC_K_LINES_V
453 Where cle_id = p_cle_id
454 and lse_id in (2,13,15,20);
455
456 Cursor cur_okc_headers(p_chr_id in NUMBER) is
457 select
458 id,
459 inv_rule_id,
460 cust_acct_id,
461 bill_to_site_use_id,
462 ship_to_site_use_id,
463 cust_po_number,
464 cust_po_number_req_yn,
465 -- BANK ACCOUNT CONSOLIDATION --
466 payment_instruction_type,
467 authoring_org_id,
468 -- BANK ACCOUNT CONSOLIDATION --
469 price_list_id, -- hkamdar 8/23/05 R12 Partial Period
470 currency_code -- gchadha for Partial Period
471 from okc_k_headers_b
472 where id = p_chr_id;
473 l_okc_headers_rec cur_okc_headers%ROWTYPE;
474
475 Cursor cur_okc_lines(p_cle_id in NUMBER) is
476 select
477 id,
478 inv_rule_id,
479 cust_acct_id,
480 lse_id,
481 price_negotiated,
482 bill_to_site_use_id,
483 ship_to_site_use_id
484 from okc_k_lines_b
485 where id = p_cle_id;
486 l_okc_lines_rec cur_okc_lines%ROWTYPE;
487
488 Cursor cur_oks_headers(p_chr_id in NUMBER) is
489 select
490 id,
491 acct_rule_id, --for ARL rule
492 tax_status,
493 --Fixed bug#4026268 --gbgupta
494 -- tax_code,
495 tax_exemption_id,
496 payment_type, --for payment method
497 -- Bank Account Consolidation --
498 /* cc_no,
499 cc_expiry_date,
500 cc_bank_acct_id,
501 cc_auth_code, */
502 trxn_extension_id,
503 -- Bank Account Consolidation --
504 commitment_id,
505 grace_duration,
506 grace_period, --for payment method
507 object_version_number,
508 price_uom, -- hkamdar 8/23/05 R12 Partial Period
509 -- Ebtax --
510 exempt_certificate_number,
511 exempt_reason_code,
512 tax_classification_code
513 -- Ebtax --
514 from oks_k_headers_v
515 where chr_id = p_chr_id;
516
517 l_oks_headers_rec cur_oks_headers%ROWTYPE;
518
519 Cursor cur_oks_lines(p_cle_id in NUMBER) is
520 select
521 id,
522 acct_rule_id, --for ARL rule
523 tax_amount,
524 tax_inclusive_yn,
525 tax_status,
526 tax_code,
527 tax_exemption_id,
528 payment_type, --for payment method
529 -- Bank Account Consolidation --
530 /*cc_no,
531 cc_expiry_date,
532 cc_bank_acct_id,
533 cc_auth_code, */
534 trxn_extension_id,
535 -- Bank Account Consolidation --
536 commitment_id,
537 grace_duration,
538 grace_period, --for payment method
539 object_version_number,
540 invoice_text,
541 --Fix for Bug# 3542273
542 usage_type,
543 --Fix for Bug# 3542273
544 -- Ebtax --
545 tax_classification_code,
546 exempt_certificate_number,
547 exempt_reason_code,
548 standard_cov_yn -- Issue#14 Bug 4566346
549 -- Ebtax --
550 from oks_k_lines_v
551 where cle_id = p_cle_id;
552 l_oks_lines_rec cur_oks_lines%ROWTYPE;
553
554 Cursor cur_head_cust_acct(p_object1_id1 IN NUMBER) IS
555 select cust_account_id
556 from okx_cust_site_uses_v
557 where id1=p_object1_id1;
558
559 Cursor cur_line_cust_acct(p_cle_id IN NUMBER) IS
560 select cust_acct_id
561 from okc_k_lines_b
562 where id =p_cle_id;
563 --commenting out, because it is not beeing used any where, 05/04/2004
564 --Cursor line_contact(p_contact_id in number ) IS
565 -- select * from okc_contacts
566 -- where id = p_contact_id;
567 -- line_contact_rec line_contact%rowtype;
568
569
570 -- Gets sublines
571 -- Bug 4717842 --
572 -- Ignore the cancelled lines.
573 Cursor cur_sub_line(l_cle_id number) IS
574 select id, price_negotiated, cle_id, price_unit
575 from okc_k_lines_v
576 where lse_id in (7,8,9,10,11,13,18,25,35)
577 and date_cancelled is NULL -- new
578 and cle_id = l_cle_id;
579 -- Bug 4717842 --
580 l_sub_line_rec cur_sub_line%ROWTYPE;
581
582 Cursor cur_bpf_cust_acct(l_billing_profile_id IN NUMBER, l_org_id IN NUMBER) IS
583 select distinct cust_account_id
584 from okx_cust_site_uses_v
585 where id1 In
586 (
587 select bill_to_address_id1
588 from oks_billing_profiles_b
589 where OWNED_PARTY_ID1 IS NOT NULL
590 and id = l_billing_profile_id)
591 AND site_use_code = 'BILL_TO'
592 AND org_id = l_org_id;
593
594 Cursor cur_bill_to_address_id1 (l_billing_profile_id IN NUMBER) IS
595 select bill_to_address_id1
596 from oks_billing_profiles_b
597 where id = l_billing_profile_id
598 AND OWNED_PARTY_ID1 IS NOT NULL;
599
600 Cursor cur_billing_profile (l_billing_profile_id IN NUMBER) IS
601 select account_object1_id1
602 ,invoice_object1_id1
603 from oks_billing_profiles_b
604 where id = l_billing_profile_id;
605
606 -- Bug 5191587 --
607 -- Added condition to verify Service and Extended Warranty Lines
608 Cursor cur_service_req_number (p_cle_id IN NUMBER, p_chr_id IN NUMBER) IS
609 select distinct incident_id,
610 contract_number,
611 contract_service_id,
612 incident_number
613 from cs_incidents_all_b cs , okc_k_lines_b line
614 where cs.contract_service_id = p_cle_id
615 and cs.contract_id =p_chr_id
616 and cs.contract_id =line.dnz_chr_id
617 and line.cle_id = p_cle_id
618 and line.lse_id in ('18','9','25');
619 -- Bug 5191587 --
620
621 l_service_req_rec cur_service_req_number%ROWTYPE;
622 l_sr_number VARCHAR2(2000);
623 l_sr_flag NUMBER:=0;
624
625 --new
626 cursor Scredit_csr(P_header_id IN NUMBER) Is
627 Select id
628 ,Percent
629 ,Ctc_id
630 ,Sales_credit_type_id1
631 ,Sales_credit_type_id2
632 --new
633 ,sales_group_id
634 --new
635 From OKS_K_SALES_CREDITS_V
636 Where chr_id = p_header_id
637 And cle_id Is NULL;
638
639 l_sales_credit_rec Scredit_csr%ROWTYPE;
640
641
642 Cursor Scredit_csr_line(p_header_id IN NUMBEr,P_line_id IN NUMBER) Is
643 Select count(*),id
644 From OKS_K_SALES_CREDITS_V
645 Where chr_id = p_header_id
646 And cle_id =p_line_id
647 group by id;
648
649 Cursor cur_line_number(p_cle_id IN VARCHAR2) Is
650 select line_number
651 from okc_k_lines_b
652 where id = p_cle_id;
653 ----new
654 Cursor cur_cust_acct_id(p_chr_id in NUMBER,
655 p_billto_id in NUMBER) is
656 Select cust_acct_id
657 from okx_cust_contacts_v
658 where party_id in
659 (select object1_id1 from okc_k_party_roles_b
660 where dnz_chr_id = p_chr_id--:PARAMETER.OKSDEFLT_CHR_ID
661 and chr_id = p_chr_id--:PARAMETER.OKSDEFLT_CHR_ID
662 and cle_id IS NULL
663 and rle_code <> 'VENDOR'
664 )
665 and status = 'A'
666 and id1 = p_billto_id;
667 -- IKON ENHANCEMENT --
668 -- 2/11/2005 --
669 -- GCHADHA --
670
671 -- GET CUST ACCT --
672 CURSOR CUR_CUST_ACCT (l_object1_id1 IN NUMBER,use IN VARCHAR2) IS
673 SELECT DISTINCT b.id1, a.party_id
674 FROM okx_cust_site_uses_v a, okx_customer_accounts_v b
675 WHERE a.id1 = l_object1_id1
676 AND b.id1 = a.cust_account_id
677 AND a.site_use_code = use;
678
679 CURSOR Party_role_csr (p_dnz_chr_id IN NUMBER,p_cle_id IN NUMBER) IS
680 SELECT id, cle_id, dnz_chr_id
681 FROM OKC_K_PARTY_ROLES_B
682 WHERE DNZ_CHR_ID = p_dnz_chr_id
683 AND CLE_ID = P_Cle_Id;
684 -- AND OBJECT1_ID1 =l_party_id;
685
686 l_party_role Party_role_csr%ROWTYPE;
687
688
689 Cursor Get_Contact_Cust_Acct ( l_contact_id IN nUMBER)IS
690 Select cust_acct_id
691 FROM OKX_CUST_CONTACTS_V
692 WHERE ROLE_TYPE = 'CONTACT'
693 AND ID1 = l_contact_id ;
694
695
696 CURSOR CUR_LINE_PARTY_ROLE ( P_CHR_ID IN NUMBER, P_CLE_ID IN NUMBER, l_party_id IN NUMBER) IS
697 SELECT id
698 FROM OKC_K_PARTY_ROLES_B
699 WHERE DNZ_CHR_ID = p_chr_id
700 AND CLE_ID = p_cle_id
701 AND OBJECT1_ID1 =l_party_id;
702
703 Cursor Get_Party_Id (p_cust_acct_id IN NUMBER) IS
704 Select Party_id,name
705 from okx_customer_accounts_v
706 where id1= p_cust_acct_id;
707
708
709
710 Cursor line_contact_jtot(p_cpl_id in number,p_chr_id IN NUMBER, p_jtot_object_code IN VARCHAR2) IS
711 select * from okc_contacts
712 where cpl_id = p_cpl_id
713 AND JTOT_OBJECT1_CODE <> p_jtot_object_code
714 AND DNZ_CHR_ID = p_chr_id;
715
716 Cursor cur_get_line_number(p_cle_id in NUMBER, p_chr_id IN NUMBER) IS
717 Select line_number
718 from okc_k_lines_v
719 where cle_id =p_cle_id
720 and dnz_chr_id = p_chr_id;
721
722
723 -- BUG 4394382 --
724 -- GCHADHA --
725 -- 5/30/2005 --
726 Cursor cur_oks_comm_line(p_cle_id in NUMBER) is
727 select
728 cust_po_number,
729 cust_po_number_req_yn,
730 payment_instruction_type,
731 trxn_extension_id
732 from oks_k_lines_b oks, okc_k_lines_b okc
733 where okc.id = p_cle_id
734 and oks.cle_id = okc.id;
735 -- END GCHADHA --
736
737 l_oks_comm_line_rec cur_oks_comm_line%ROWTYPE;
738
739 -- Bank Account Consolidation --
740 Cursor cur_get_party_id (p_cust_acct_id IN NUMBER) IS
741 Select party_id
742 FROM HZ_CUST_ACCOUNTS CA
743 WHERE CA.cust_account_ID = p_cust_acct_id;
744
745 l_oks_party_rec cur_get_party_id%ROWTYPE;
746
747 -- Bank Account Consolidation --
748
749 -- hkamdar 8/23/05 R12 Partial Period
750 -- Cursor for Checking price list lock
751
752 Cursor cur_get_lines_details (p_chr_id IN NUMBER, p_cle_id IN NUMBER) IS
753 Select locked_price_list_id ,
754 locked_price_list_line_id
755 FROM OKS_k_LINES_B
756 WHERE dnz_chr_id = p_chr_id
757 and cle_id = p_cle_id ;
758
759 -- Ebtax --
760 CURSOR tax_exemption_number_csr(p_tax_exemption_id IN NUMBER) IS
761 Select ex.exempt_certificate_number CUSTOMER_EXCEPTION_NUMBER, nvl(lk.lookup_code,ex.exempt_reason_code) Description
762 from zx_exemptions ex, fnd_lookups lk
763 where ex.tax_exemption_id = p_tax_exemption_id
764 and lk.lookup_type(+) = 'ZX_EXEMPTION_REASON_CODE'
765 and lk.lookup_code(+) = ex.exempt_reason_code;
766
767 tax_exemption_number_rec tax_exemption_number_csr%rowtype;
768 -- Recalculate Tax --
769 -- Bug 4717842 --
770 CURSOR get_topline_tax_amt_csr (p_cle_id IN NUMBER) IS
771 Select nvl(sum(nvl(tax_amount,0)),0) tax_amount
772 from okc_k_lines_b cle, oks_k_lines_b sle
773 where cle.cle_id = p_cle_id
774 and cle.lse_id in (7,8,9,10,11,13,25,35)
775 and cle.id = sle.cle_id
776 and cle.dnz_chr_id = sle.dnz_chr_id
777 and cle.date_cancelled is null;
778
779 Cursor get_hdr_tax_amt_csr (p_chr_id in number) IS
780 Select nvl(sum(nvl(tax_amount,0)),0) tax_amount
781 from okc_k_lines_b cle, oks_k_lines_b sle
782 where cle.chr_id = p_chr_id
783 and cle.id = sle.cle_id
784 and cle.dnz_chr_id = sle.dnz_chr_id
785 and cle.date_cancelled is null;
786
787 l_line_tax_amt get_topline_tax_amt_csr%RowType;
788 l_hdr_tax_amt get_hdr_tax_amt_csr%RowType;
789
790
791 l_khrv_tbl_type_in oks_contract_hdr_pub.khrv_tbl_type;
792 l_khrv_tbl_type_out oks_contract_hdr_pub.khrv_tbl_type;
793 -- Recalculate Tax --
794
795 -- Ebtax --
796 -- End hkamdar 8/23/05 R12 Partial Period
797
798 l_header_sca VARCHAR2(3);
799 l_header_bca VARCHAR2(3) ;
800 l_header_cust_acct NUMBER;
801 line_contact_cust_acct NUMBER;
802 l_contact NUMBER;
803 l_party_id NUMBER;
804 l_line_party_id NUMBER;
805 l_temp_contact_id NUMBER;
806 l_temp_party_name VARCHAR2(150); -- Stores the party Name for Lines Level BTO/STO Account
807 l_can_success NUMBER :=0;
808 l_temp_line_number NUMBER ; -- Temporary Variable used to Keep the Line Number
809 l_tax_exempt_status NUMBER :=0;
810 l_oks_header_id NUMBER :=0; --Variable to ascertain whether OKS_K_HEADERS_B should be
811 -- Updated or not.
812
813 -- END GCHADHA --
814 -- IKON ENHANCEMENT --
815 ----new
816
817
818
819 l_sc_index NUMBER;
820 l_scr_rec Scredit_csr%rowtype;
821 l_scrv_tbl_in OKS_SALES_CREDIT_PUB.SCRV_TBL_TYPE;
822 l_scrv_tbl_out OKS_SALES_CREDIT_PUB.SCRV_TBL_TYPE;
823 l_scredit_count NUMBER;
824 l_scredit_id NUMBER;
825 l_sales_group_id NUMBER;
826 --new
827
828 l_index NUMBER;
829 i NUMBER;
830 l_chr_id NUMBER;
831 l_cle_id NUMBER;
832 l_subline_id NUMBER;
833 l_cpl_id NUMBER;
834 l_contact_id NUMBER;
835 l_header_sto VARCHAR2(150);
836 l_header_bto VARCHAR2(150);
837 l_header_dates VARCHAR2(150);
838 l_header_arl VARCHAR2(150);
839 l_header_ire VARCHAR2(150);
840 l_header_tax VARCHAR2(150);
841 l_header_sales_credits VARCHAR2(150);
842 l_header_exception_number VARCHAR2(150);
843 l_header_tax_code VARCHAR2(150);
844 -- Ebtax --
845 l_header_tax_cls_code VARCHAR2(150);
846 -- Ebtax --
847 --Fixed bug#4026268 --gbgupta
848 l_header_tax_code_id NUMBER;
849
850 l_header_billto_contact VARCHAR2(150);
851 l_line_tax_status VARCHAR2(150);
852 l_head_cust_acct NUMBER;
853 l_line_cust_acct NUMBER;
854 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
855 l_clev_tbl_out okc_contract_pub.clev_tbl_type;
856 l_api_version Number := 1.0;
857 l_init_msg_list Varchar2(1) := 'F';
858 l_msg_count Number;
859 l_msg_data Varchar2(1000);
860 l_return_status Varchar2(1) := 'S';
861 l_tot_msg_count NUMBER:=0;
862 tbl_index NUMBER:=1;
863 l_bill_sch_out_tbl OKS_BILL_SCH.ItemBillSch_tbl;
864 l_id Number;
865 x_cpl_id NUMBER;
866 l_billto_id NUMBEr;
867 l_cplv_tbl_in okc_contract_party_pub.cplv_tbl_type;
868 l_cplv_tbl_out okc_contract_party_pub.cplv_tbl_type;
869 l_ctcv_tbl_in okc_contract_party_pub.ctcv_tbl_type;
870 l_ctcv_tbl_out okc_contract_party_pub.ctcv_tbl_type;
871 --l_insupd_flag BOOLEAN := FALSE;
872 l_msg_index NUMBER;
873 l_Invoice_Rule_Id NUMBER;
874 l_Account_Rule_Id NUMBER;
875 l_rec OKS_BILLING_PROFILES_PUB.Billing_profile_rec;
876 l_sll_tbl_out OKS_BILLING_PROFILES_PUB.Stream_Level_tbl;
877 l_top_line_id NUMBER;
878 l_calculate_tax VARCHAR2(150);
879 x_msg_count NUMBER;
880 x_msg_data VARCHAR2(2000);
881 G_RAIL_REC OKS_TAX_UTIL_PVT.ra_rec_type;
882 l_UNIT_SELLING_PRICE G_RAIL_REC.UNIT_SELLING_PRICE%TYPE;
883 l_QUANTITY G_RAIL_REC.QUANTITY%TYPE;
884 l_sub_total G_RAIL_REC.AMOUNT%TYPE;
885 l_total G_RAIL_REC.AMOUNT%TYPE;
886 l_total_amt G_RAIL_REC.AMOUNT%TYPE;
887 l_AMOUNT_INCLUDES_TAX_FLAG G_RAIL_REC.AMOUNT_INCLUDES_TAX_FLAG%TYPE;
888 l_Tax_Code G_RAIL_REC.TAX_CODE%TYPE;
889 l_TAX_RATE G_RAIL_REC.TAX_RATE%TYPE;
890 l_Tax_Value G_RAIL_REC.TAX_VALUE%TYPE;
891 l_price_negotiated G_RAIL_REC.AMOUNT%TYPE;
892 l_lse_id NUMBER;
893 l_bill_to_address_id1 VARCHAR2(40) ;
894 l_bpf_cust_acct NUMBER;
895 l_can_id NUMBER;
896 l_billing_profile VARCHAR2(150);
897 l_billing_profile_id NUMBER;
898 l_bpf_start_date DATE;
899 l_bpf_end_date DATE;
900 l_bpf_lse_id NUMBER;
901 L_bil_sch_out_tbl OKS_BILL_SCH.ItemBillSch_tbl;
902 l_top_bs_tbl oks_bill_level_elements_pvt.letv_tbl_type;
903 l_sll_tbl OKS_BILL_SCH.StreamLvl_tbl;
904 l_error_tbl OKC_API.ERROR_TBL_TYPE;
905 p_klnv_tbl oks_kln_pvt.klnv_tbl_type;
906 l_klnv_tbl oks_kln_pvt.klnv_tbl_type := p_klnv_tbl;
907 x_klnv_tbl oks_kln_pvt.klnv_tbl_type;
908 l_payment_method VARCHAR2(150);--for payment method
909 l_klnv_tbl_in oks_contract_line_pub.klnv_tbl_type;
910 l_klnv_tbl_out oks_contract_line_pub.klnv_tbl_type;
911
912 /*added for bug 7387293*/
913 l_kslnv_tbl_in oks_contract_line_pub.klnv_tbl_type;
914 l_kslnv_tbl_out oks_contract_line_pub.klnv_tbl_type;
915 /*added for bug 7387293*/
916
917 l_billing_type VARCHAR2 (450);
918 l_bpf_acct_rule_id NUMBER;
919 l_bpf_invoice_rule_id NUMBER;
920 l_line_number VARCHAR2 (150);
921 l_flag BOOLEAN;
922
923 --Fix for Bug# 3542273
924 l_usage_type VARCHAR2 (10);
925 l_token1_value VARCHAR2 (1000);
926 --Fix for Bug# 3542273
927 --new....
928 l_lov_cust_acct NUMBER;
929 --new....
930 --BUG#4089834
931 l_status_flag BOOLEAN := FALSE;
932 l_exmpt_num_flag BOOLEAN := FALSE;
933
934 -- GCHADHA --
935 -- BUG 4394382 --
936 l_cust_po_flag NUMBER := 0; -- Flag to mark PO number change
937 l_payment_method_com NUMBER := 0; -- Flag to mark Commitment number details
938 l_payment_method_ccr NUMBER := 0; -- Flag to mark Credit card Change
939 l_trxn_extension_id NUMBER := 0; -- Bank Account Consolidation
940 -- END GCHADHA --
941 l_line_irt VARCHAR2(1) := NULL; -- Added for Bug#15936043
942
943 -- hkamdar 8/23/05 R12 Partial Period
944 l_price_uom VARCHAR2(100);
945 l_error VARCHAR2(1);
946 l_locked_prl_cnt NUMBER := 0;
947 l_header_price_uom VARCHAR2(150);
948 l_header_price_list VARCHAR2(150);
949 l_source_price_list_line_id NUMBER;
950 l_locked_price_list_id NUMBER;
951 l_locked_price_list_line_id NUMBER;
952
953 l_input_details OKS_QP_PKG.INPUT_DETAILS;
954 l_output_details OKS_QP_PKG.PRICE_DETAILS;
955 l_modif_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
956 l_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
957 l_status_tbl oks_qp_int_pvt.PRICING_STATUS_TBL;
958 l_pricing_effective_date DATE;
959 l_validate_result VARCHAR2(10);
960 -- End hkamdar 8/23/05 R12 Partial Period
961
962 -- mkarra 06/21/07 Import Contracts Bug 6128632
963 l_bill_y_n VARCHAR2(1);
964 l_disp_warning VARCHAR2(1) ;
965
966 Begin
967
968 fnd_msg_pub.initialize;
969 IF NOT header_lines_tbl.COUNT=0 THEN
970
971
972 i:=header_lines_tbl.FIRST;
973 LOOP
974 l_error := 'N'; -- 8/23/2005 hkamdar R12 Partial Period
975
976 l_chr_id := header_lines_tbl(i).chr_id;
977 l_cle_id := header_lines_tbl(i).cle_id;
978 l_header_sto := header_lines_tbl(i).header_sto;
979 l_header_bto := header_lines_tbl(i).header_bto;
980 l_header_dates := header_lines_tbl(i).header_dates;
981 l_header_arl := header_lines_tbl(i).header_arl;
982 l_header_ire := header_lines_tbl(i).header_ire;
983 l_header_tax := header_lines_tbl(i).header_tax;
984 l_header_exception_number := header_lines_tbl(i).header_exception_number;
985 l_header_tax_code := header_lines_tbl(i).header_tax_code;
986 --Fixed bug#4026268 --gbgupta
987
988 l_header_tax_code_id := header_lines_tbl(i).header_tax_code_id;
989
990 l_header_sales_credits := header_lines_tbl(i).header_sales_credits;
991 l_header_billto_contact := header_lines_tbl(i).header_billto_contact;
992 l_billto_id := header_lines_tbl(i).billto_id;
993 l_billing_profile := header_lines_tbl(i).billing_profile;
994 l_billing_profile_id := header_lines_tbl(i).billing_profile_id;
995 l_calculate_tax := header_lines_tbl(i).calculate_tax;
996 l_payment_method := header_lines_tbl(i).payment_method;
997 -- GCHADHA --
998 -- IKON --
999 l_header_bca := header_lines_tbl(i).header_bca;
1000 l_header_sca := header_lines_tbl(i).header_sca;
1001 -- END GCHADHA --
1002
1003 -- 8/23/2005 hkamdar R12 Partial Period
1004 l_header_price_uom := header_lines_tbl(i).price_uom;
1005 l_header_price_list := header_lines_tbl(i).price_list;
1006 -- End 8/23/2005 hkamdar R12 Partial Period
1007 --Ebtax --
1008 l_header_tax_cls_code := header_lines_tbl(i).header_tax_cls_code;
1009 --Ebtax --
1010
1011 l_klnv_tbl_in.DELETE;
1012 l_clev_tbl_in.DELETE;
1013 l_kslnv_tbl_in.DELETE;-- /* Added for 7387293 */
1014
1015 -- check if the contract has been imported and fully billed at source or not
1016
1017 IF(l_chr_id IS NOT NULL) THEN
1018 select billed_at_source INTO l_bill_y_n FROM okc_k_headers_all_b where id = l_chr_id ;
1019 END IF;
1020
1021 IF ((l_bill_y_n IS NOT NULL) AND (l_bill_y_n = 'Y')) THEN
1022 l_disp_warning :='Y';
1023 ELSE
1024 l_disp_warning :='N';
1025 END IF;
1026
1027
1028
1029 OPEN cur_okc_headers(l_chr_id );
1030 FETCH cur_okc_headers INTO l_okc_headers_rec;
1031 IF cur_okc_headers%NOTFOUND then
1032 CLOSE cur_okc_headers;
1033 x_return_status := 'E';
1034 RAISE G_EXCEPTION_HALT_VALIDATION;
1035 ELSE
1036 CLOSE cur_okc_headers;
1037 END IF;
1038
1039 OPEN cur_okc_lines(l_cle_id );
1040 FETCH cur_okc_lines INTO l_okc_lines_rec;
1041 IF cur_okc_lines%NOTFOUND then
1042 CLOSE cur_okc_lines;
1043 x_return_status := 'E';
1044 RAISE G_EXCEPTION_HALT_VALIDATION;
1045 ELSE
1046 CLOSE cur_okc_lines;
1047 END IF;
1048
1049 OPEN cur_oks_headers(l_chr_id );
1050 FETCH cur_oks_headers INTO l_oks_headers_rec;
1051 IF cur_oks_headers%NOTFOUND then
1052 CLOSE cur_oks_headers;
1053 x_return_status := 'E';
1054 RAISE G_EXCEPTION_HALT_VALIDATION;
1055 ELSE
1056 CLOSE cur_oks_headers;
1057 END IF;
1058
1059 OPEN cur_oks_lines(l_cle_id );
1060 FETCH cur_oks_lines INTO l_oks_lines_rec;
1061 IF cur_oks_lines%NOTFOUND then
1062 CLOSE cur_oks_lines;
1063 x_return_status := 'E';
1064 RAISE G_EXCEPTION_HALT_VALIDATION;
1065 ELSE
1066 CLOSE cur_oks_lines;
1067 END IF;
1068
1069 OPEN cur_header_dates(l_chr_id );
1070 FETCH cur_header_dates INTO header_dates_rec;
1071 IF cur_header_dates%NOTFOUND then
1072 CLOSE cur_header_dates;
1073 x_return_status := 'E';
1074 RAISE G_EXCEPTION_HALT_VALIDATION;
1075 ELSE
1076 CLOSE cur_header_dates;
1077 END IF;
1078
1079 OPEN cur_line_number(l_cle_id );
1080 FETCH cur_line_number INTO l_line_number;
1081 IF cur_line_number%NOTFOUND then
1082 CLOSE cur_line_number;
1083 x_return_status := 'E';
1084 RAISE G_EXCEPTION_HALT_VALIDATION;
1085 ELSE
1086 CLOSE cur_line_number;
1087 END IF;
1088
1089 -- Bug 5191587 --
1090 If l_okc_lines_rec.lse_id in ('1','14','19') then
1091 l_sr_flag :=0;
1092 FOR cur_rec in cur_service_req_number(l_cle_id,l_chr_id)
1093 LOOP
1094 IF l_sr_flag <> 0 THEN
1095 l_sr_number := l_sr_number || ' ; ';
1096 END IF;
1097 l_sr_number := l_sr_number || cur_rec.incident_number;
1098 l_sr_flag :=1;
1099 END LOOP;
1100
1101 /* OPEN cur_service_req_number(l_cle_id );
1102 FETCH cur_service_req_number INTO l_service_req_rec;
1103 IF cur_service_req_number%NOTFOUND then
1104 CLOSE cur_service_req_number;
1105 ELSE
1106 l_sr_number := l_service_req_rec.incident_number;
1107 CLOSE cur_service_req_number;
1108 END IF;
1109 */
1110 End If;
1111 -- Bug 5191587 --
1112
1113 l_klnv_tbl_in(1).OBJECT_VERSION_NUMBER := l_oks_lines_rec.OBJECT_VERSION_NUMBER;
1114
1115 If l_header_dates is not null then
1116
1117 --new
1118
1119 If oks_extwar_util_pvt.check_already_billed(
1120 p_chr_id => null,
1121 p_cle_id => header_lines_tbl(i).cle_id,
1122 p_lse_id => l_okc_lines_rec.lse_id,
1123 p_end_date => null) Then
1124
1125
1126 validate_date
1127 (p_api_version => l_api_version
1128 ,p_init_msg_list => l_init_msg_list
1129 ,p_hdr_id => l_chr_id
1130 ,p_top_line_id => l_cle_id
1131 ,p_sub_line_id => NULL
1132 ,x_return_status => l_return_status
1133 ,x_msg_count => l_msg_count
1134 ,x_msg_data => l_msg_data
1135 ,x_flag => l_flag);
1136
1137 -- Bug 5227077 --
1138 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1139 FOR i in 1..fnd_msg_pub.count_msg
1140 Loop
1141 fnd_msg_pub.get
1142 (
1143 p_msg_index => i,
1144 p_encoded => 'F',
1145 p_data => l_msg_data,
1146 p_msg_index_out => l_msg_index
1147 );
1148 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1149 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1150 l_tot_msg_count := l_tot_msg_count + 1;
1151 l_msg_data := NULL;
1152 End Loop;
1153 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1154 Else
1155 If l_flag <> TRUE then
1156 fnd_msg_pub.initialize;
1157
1158 OKC_API.SET_MESSAGE(
1159 p_app_name => 'OKS', --G_APP_NAME_OKS,
1160 p_msg_name => 'OKS_BA_UPDATE_NOT_ALLOWED',
1161 p_token1 => 'Line No ',
1162 p_token1_value => l_line_number);
1163
1164 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1165 p_encoded => fnd_api.g_false);
1166 x_msg_tbl(l_tot_msg_count).status := 'E';
1167 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1168 l_tot_msg_count := l_tot_msg_count + 1;
1169 l_msg_data := NULL;
1170 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1171 End If;
1172 End If;
1173
1174 End If;
1175
1176 --new
1177
1178 l_clev_tbl_in(1).id := l_cle_id;
1179 l_clev_tbl_in(1).chr_id := l_chr_id;
1180 l_clev_tbl_in(1).start_date := header_dates_rec.start_date;
1181 l_clev_tbl_in(1).end_date := header_dates_rec.end_date;
1182 l_clev_tbl_in(1).dnz_chr_id := l_chr_id;
1183
1184 If l_okc_lines_rec.lse_id = '14' Then
1185 If l_clev_tbl_in(1).start_date > SYSDATE THen
1186 l_clev_tbl_in(1).sts_code := 'SIGNED';
1187 Elsif l_clev_tbl_in(1).start_date <= SYSDATE And l_clev_tbl_in(1).end_date >= SYSDATE THEN
1188 l_clev_tbl_in(1).sts_code := 'ACTIVE';
1189 ELSIF l_clev_tbl_in(1).end_date < SYSDATE Then
1190 l_clev_tbl_in(1).sts_code :='EXPIRED';
1191 End if;
1192 End if;
1193
1194 -- Added for Bug#15936043
1195 l_line_irt := 'X';
1196 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
1197 l_klnv_tbl_in(1).invoice_text :=
1198 substr(SubStr(l_oks_lines_rec.invoice_text,1,InStr(l_oks_lines_rec.invoice_text,':')-1)
1199 ||':'|| header_dates_rec.start_date
1200 ||':'|| header_dates_rec.end_date,1,450);
1201
1202 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1203 fnd_msg_pub.initialize;
1204 OKC_API.SET_MESSAGE(
1205 p_app_name => G_APP_NAME_OKS,
1206 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1207 p_token1 => 'ATTRIBUTE',
1208 p_token1_value => 'Invoicing Text');
1209
1210 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1211 p_encoded => fnd_api.g_false);
1212
1213 x_msg_tbl(l_tot_msg_count).status := 'S';
1214 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1215 l_tot_msg_count := l_tot_msg_count + 1;
1216 l_msg_data := NULL;
1217
1218 END IF;
1219
1220 -- End of code added for Bug#15936043
1221
1222 END IF;
1223
1224 If l_header_bto is not null then
1225
1226 Open cur_head_cust_acct(l_okc_headers_rec.bill_to_site_use_id);
1227 fetch cur_head_cust_acct into l_head_cust_acct;
1228 close cur_head_cust_acct;
1229
1230 Open cur_line_cust_acct(l_okc_lines_rec.id);
1231 fetch cur_line_cust_acct into l_line_cust_acct;
1232 close cur_line_cust_acct;
1233 If l_head_cust_acct = l_line_cust_acct or
1234 l_line_cust_acct is NULL and l_head_cust_acct is not NULL Then
1235
1236 l_clev_tbl_in(1).id := l_cle_id;
1237 l_clev_tbl_in(1).bill_to_site_use_id := l_okc_headers_rec.bill_to_site_use_id;
1238 l_clev_tbl_in(1).cust_acct_id := l_head_cust_acct;
1239
1240 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1241 fnd_msg_pub.initialize;
1242 OKC_API.SET_MESSAGE(
1243 p_app_name => G_APP_NAME_OKS,
1244 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1245 p_token1 => 'ATTRIBUTE',
1246 p_token1_value => 'Bill To Address');
1247
1248 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1249 p_encoded => fnd_api.g_false);
1250
1251 x_msg_tbl(l_tot_msg_count).status := 'S';
1252 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1253 l_tot_msg_count := l_tot_msg_count + 1;
1254 l_msg_data := NULL;
1255
1256 END IF;
1257 -- IKON ENHANCEMENT
1258 /* ElsIf l_head_cust_acct <> l_line_cust_acct and
1259 l_line_cust_acct is not NULL and l_head_cust_acct is not NULL Then
1260 fnd_msg_pub.initialize;
1261
1262 OKC_API.SET_MESSAGE(
1263 p_app_name => G_APP_NAME_OKS,
1264 p_msg_name => 'OKS_CASCADE_ACCOUNT_MISMATCH',
1265 p_token1 => 'Line No ',
1266 p_token1_value => l_line_number);
1267
1268 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1269 p_encoded => fnd_api.g_false);
1270
1271 x_msg_tbl(l_tot_msg_count).status := 'E';
1272 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1273 l_tot_msg_count := l_tot_msg_count + 1;
1274 l_msg_data := NULL;
1275 If header_lines_tbl.FIRST = header_lines_tbl.LAST Then
1276 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1277 End If; */
1278
1279
1280 END IF; --l_head_cust_acct check
1281 END IF;
1282
1283 If l_header_sto is not null then
1284 -- IKON ENHANCEMENT --
1285 -- 2/11/2005
1286 Open cur_head_cust_acct(l_okc_headers_rec.ship_to_site_use_id);
1287 fetch cur_head_cust_acct into l_header_cust_acct;
1288 close cur_head_cust_acct;
1289
1290 Open CUR_CUST_ACCT(l_okc_lines_rec.ship_to_site_use_id,'SHIP_TO');
1291 fetch CUR_CUST_ACCT into l_line_cust_acct,l_party_id;
1292 close CUR_CUST_ACCT;
1293
1294 --npalepu modified on 22-FEB-2007 for bug # 5742807. Added extra condition.
1295 /* If l_header_cust_acct = l_line_cust_acct THEN */
1296
1297 If (l_header_cust_acct = l_line_cust_acct) or
1298 (l_line_cust_acct is NULL and l_header_cust_acct is not NULL) Then
1299 --end bug # 5742807
1300
1301 l_clev_tbl_in(1).id := l_cle_id;
1302 l_clev_tbl_in(1).ship_to_site_use_id := l_okc_headers_rec.ship_to_site_use_id;
1303
1304 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1305 fnd_msg_pub.initialize;
1306 OKC_API.SET_MESSAGE(
1307 p_app_name => G_APP_NAME_OKS,
1308 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1309 p_token1 => 'ATTRIBUTE',
1310 p_token1_value => 'Ship To Address');
1311
1312 -- fnd_msg_pub.initialize;
1313 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1314 p_encoded => fnd_api.g_false);
1315 x_msg_tbl(l_tot_msg_count).status := 'S';
1316 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1317 l_tot_msg_count := l_tot_msg_count + 1;
1318 l_msg_data := NULL;
1319 END IF;
1320 END IF;
1321 -- IKON ENHANCEMENT --
1322 END IF;
1323
1324 -- *******************************************************************
1325 -- IKON ENHANCEMENT --
1326 IF l_header_bca is not null then
1327
1328 -- l_oks_header_id is used to mark whether
1329 -- OKS_K_HEADERS_B table should be update or not
1330 -- If l_oks_header_id is 0 then
1331 -- OKS_K_HEADERS_B should not be updated.
1332 -- Else should be Updated.
1333 -- Used only with l_header_bca variable.
1334 l_oks_header_id :=0;
1335
1336 Open cur_head_cust_acct(l_okc_headers_rec.bill_to_site_use_id);
1337 fetch cur_head_cust_acct into l_header_cust_acct;
1338 close cur_head_cust_acct;
1339
1340 Open cur_line_cust_acct(l_okc_lines_rec.id);
1341 fetch cur_line_cust_acct into l_line_cust_acct;
1342 close cur_line_cust_acct;
1343
1344
1345 -- CASE I If no BTO rule is there then create a BTO rule
1346 -- CASE II If there is a BTO RULE then
1347 -- Update the BTO rule at lines leve with header
1348 -- level Customer account if the accounts are
1349 -- not same.
1350 -- Delete all contacts which are not
1351 -- billing contact and are belonging to
1352 -- ship to account Party Role
1353 -- CASE I
1354 IF l_header_cust_acct <> nvl(l_line_cust_acct, -1) THEN
1355 -- Create/UPDATE a CAN RULE and Create/UPDATE the BTO Rule --
1356 l_clev_tbl_in(1).id := l_cle_id;
1357 l_clev_tbl_in(1).bill_to_site_use_id := l_okc_headers_rec.bill_to_site_use_id;
1358 l_clev_tbl_in(1).cust_acct_id := l_header_cust_acct;
1359 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
1360 l_oks_header_id := 1;
1361
1362 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1363 IF l_line_cust_acct IS NULL THEN
1364 fnd_msg_pub.initialize;
1365 OKC_API.SET_MESSAGE(
1366 p_app_name => G_APP_NAME_OKS,
1367 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1368 p_token1 => 'ATTRIBUTE',
1369 p_token1_value => 'Bill To Address');
1370
1371 -- fnd_msg_pub.initialize;
1372 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1373 p_encoded => fnd_api.g_false);
1374 x_msg_tbl(l_tot_msg_count).status := 'S';
1375 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1376 l_tot_msg_count := l_tot_msg_count + 1;
1377 l_msg_data := NULL;
1378 END IF;
1379
1380
1381
1382 fnd_msg_pub.initialize;
1383 OKC_API.SET_MESSAGE(
1384 p_app_name => G_APP_NAME_OKS, -- bug 5468539 G_APP_NAME,
1385 p_msg_name =>'OKS_DEFAULT_ATTR_SUCCESS_NEW',
1386 p_token1 => 'TOKEN1' , -- bug 5468539 'RULE',
1387 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'));
1388
1389 -- fnd_msg_pub.initialize;
1390 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1391 p_encoded => fnd_api.g_false);
1392 x_msg_tbl(l_tot_msg_count).status := 'S';
1393 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1394 l_tot_msg_count := l_tot_msg_count + 1;
1395 l_msg_data := NULL;
1396
1397 END IF;
1398 END IF;
1399 IF l_line_cust_acct is not NULL AND l_header_cust_acct <> l_line_cust_acct THEN
1400 open Get_Party_Id (l_line_cust_acct);
1401 fetch Get_party_id into l_line_party_id,l_temp_party_name;
1402 close get_party_id;
1403 -- DELETE THE TAX EXEMPTION IF THERE
1404 -- Bug 5202208 --
1405 -- Modified code to keep a check for both Exempt_certificate_number
1406 -- and tax_exemption_id for contracts migrated from 11.5.10.
1407 -- IF l_oks_lines_rec.TAX_STATUS ='E' AND l_oks_lines_rec.TAX_EXEMPTION_ID IS NOT NULL THEN
1408 IF (l_oks_lines_rec.TAX_STATUS ='E' AND ( l_oks_lines_rec.EXEMPT_CERTIFICATE_NUMBER IS NOT NULL
1409 OR l_oks_lines_rec.TAX_EXEMPTION_ID IS NOT NULL))
1410 THEN
1411 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
1412 l_klnv_tbl_in(1).TAX_STATUS := NUll;
1413 l_klnv_tbl_in(1).TAX_EXEMPTION_ID := NULL;
1414 l_klnv_tbl_in(1).EXEMPT_REASON_CODE := NULL;
1415 l_klnv_tbl_in(1).EXEMPT_CERTIFICATE_NUMBER:= NULL;
1416 l_klnv_tbl_in(1).CLE_ID := l_cle_id;
1417 l_oks_header_id := 1;
1418 -- Display a message --
1419 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1420 fnd_msg_pub.initialize;
1421 OKC_API.SET_MESSAGE(
1422 p_app_name => G_APP_NAME,
1423 p_msg_name =>'OKS_CASCADE_DELETE_EXEMPTION',
1424 p_token1 =>'TOKEN1' ,
1425 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'),
1426 p_token2 => 'TOKEN2',
1427 p_token2_value => l_temp_party_name,
1428 p_token3 => 'TOKEN3',
1429 p_token3_value => l_temp_line_number);
1430 -- fnd_msg_pub.initialize;
1431 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1432 p_encoded => fnd_api.g_false);
1433 x_msg_tbl(l_tot_msg_count).status := 'S';
1434 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1435 l_tot_msg_count := l_tot_msg_count + 1;
1436 l_msg_data := NULL;
1437 END IF;
1438 END IF;
1439 -- Bug 5202208 --
1440 --GCHADHA --
1441 -- BUG 4394382 --
1442 -- DELETE COMMITMENT NUMBER
1443 IF l_oks_lines_rec.payment_type = 'COM' THEN
1444 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
1445 l_klnv_tbl_in(1).CLE_ID := l_cle_id;
1446 l_klnv_tbl_in(1).commitment_id := NULL ;
1447 l_klnv_tbl_in(1).payment_type := NULL ;
1448 -- Display a message --
1449 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1450 fnd_msg_pub.initialize;
1451 OKC_API.SET_MESSAGE(
1452 p_app_name => G_APP_NAME,
1453 p_msg_name =>'OKS_CASCADE_DELETE_COMMITMENT',
1454 p_token1 =>'TOKEN1' ,
1455 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'),
1456 p_token2 => 'TOKEN2',
1457 p_token2_value => l_temp_party_name,
1458 p_token3 => 'TOKEN3',
1459 p_token3_value => l_temp_line_number);
1460 -- fnd_msg_pub.initialize;
1461 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1462 p_encoded => fnd_api.g_false);
1463 x_msg_tbl(l_tot_msg_count).status := 'S';
1464 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1465 l_tot_msg_count := l_tot_msg_count + 1;
1466 l_msg_data := NULL;
1467 END IF;
1468
1469 END IF;
1470
1471 -- END GCHADHA --
1472
1473 -- DELETE CONTACTS --
1474
1475 FOR CUR_PARTY_REC IN CUR_LINE_PARTY_ROLE(l_chr_id, l_cle_id,l_line_party_id ) LOOP
1476 l_cpl_id := CUR_PARTY_REC.ID;
1477
1478 IF l_cpl_id is NOT NULL THEN
1479 l_contact_id := CHECK_LINE_CONTACT(l_chr_id,l_cpl_id); -- 5485054
1480 IF l_contact_id is NOT NULL THEN
1481 l_contact :=0;
1482 -- When Cascading Bill to Cust Account then Don't check for Shipping Contact
1483 FOR contact_rec in line_contact_jtot(l_cpl_id,l_chr_id,'OKX_CONTSHIP') LOOP
1484
1485 Open Get_Contact_Cust_Acct(contact_rec.object1_id1);
1486 fetch Get_Contact_Cust_Acct into l_line_cust_acct;
1487 close Get_Contact_Cust_Acct;
1488 -- Delete the contact when Contact Account Id is not same as
1489 -- header Account Id
1490 IF l_header_cust_acct <> l_line_cust_acct THEN
1491
1492 l_ctcv_tbl_in(l_contact).id := contact_rec.id;
1493 l_ctcv_tbl_in(l_contact).cpl_id := l_cpl_id;
1494 l_ctcv_tbl_in(l_contact).dnz_chr_id := contact_rec.dnz_chr_ID;
1495
1496 l_ctcv_tbl_in(l_contact).cro_code := contact_rec.cro_code;
1497 l_ctcv_tbl_in(l_contact).OBJECT1_ID1 := contact_rec.object1_id1;
1498 l_ctcv_tbl_in(l_contact).object1_id2 := contact_rec.OBJECT1_ID2;
1499 l_ctcv_tbl_in(l_contact).JTOT_OBJECT1_CODE := contact_rec.JTOT_OBJECT1_CODE ;
1500 l_ctcv_tbl_in(l_contact).start_date := contact_rec.START_DATE;
1501 l_ctcv_tbl_in(l_contact).end_date := contact_rec.END_DATE;
1502 l_ctcv_tbl_in(l_contact).attribute_category := contact_rec.ATTRIBUTE_CATEGORY;
1503 l_ctcv_tbl_in(l_contact).attribute1 := contact_rec.ATTRIBUTE1;
1504 l_ctcv_tbl_in(l_contact).attribute2 := contact_rec.ATTRIBUTE2;
1505 l_ctcv_tbl_in(l_contact).attribute3 := contact_rec.ATTRIBUTE3;
1506 l_ctcv_tbl_in(l_contact).attribute4 := contact_rec.ATTRIBUTE4;
1507 l_ctcv_tbl_in(l_contact).attribute5 := contact_rec.ATTRIBUTE5;
1508 l_ctcv_tbl_in(l_contact).attribute6 := contact_rec.ATTRIBUTE6;
1509 l_ctcv_tbl_in(l_contact).attribute7 := contact_rec.ATTRIBUTE7;
1510 l_ctcv_tbl_in(l_contact).attribute8 := contact_rec.ATTRIBUTE8;
1511 l_ctcv_tbl_in(l_contact).attribute9 := contact_rec.ATTRIBUTE9;
1512 l_ctcv_tbl_in(l_contact).attribute10 := contact_rec.ATTRIBUTE10;
1513 l_ctcv_tbl_in(l_contact).attribute11 := contact_rec.ATTRIBUTE11;
1514 l_ctcv_tbl_in(l_contact).attribute12 := contact_rec.ATTRIBUTE12;
1515 l_ctcv_tbl_in(l_contact).attribute13 := contact_rec.ATTRIBUTE13;
1516 l_ctcv_tbl_in(l_contact).attribute14 := contact_rec.ATTRIBUTE14;
1517 l_ctcv_tbl_in(l_contact).attribute15 := contact_rec.ATTRIBUTE15;
1518 l_ctcv_tbl_in(l_contact).object_version_number := contact_rec.object_version_number;
1519 l_ctcv_tbl_in(l_contact).created_by := contact_rec.created_by;
1520 l_ctcv_tbl_in(l_contact).creation_date := contact_rec.creation_date;
1521 l_ctcv_tbl_in(l_contact).last_updated_by := contact_rec.last_updated_by;
1522 l_ctcv_tbl_in(l_contact).last_update_date := contact_rec.last_update_date;
1523 l_ctcv_tbl_in(l_contact).last_update_login := contact_rec.last_update_login;
1524 l_contact := l_contact+1;
1525 END IF ;-- line_contact_cust_acct
1526 END LOOP;
1527
1528 IF l_header_cust_acct <> l_line_cust_acct OR l_contact > 1 THEN
1529
1530 okc_contract_party_pub.delete_contact
1531 (p_api_version => l_api_version,
1532 p_init_msg_list => l_init_msg_list,
1533 x_return_status => l_return_status,
1534 x_msg_count => l_msg_count,
1535 x_msg_data => l_msg_data,
1536 p_ctcv_tbl => l_ctcv_tbl_in
1537 );
1538
1539 -- Delete Party Role --
1540 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1541 -- Check if there is any contaact Present belonging to the party role ---
1542 l_temp_contact_id := CHECK_LINE_CONTACT(l_chr_id,l_cpl_id);-- 5485054
1543 IF nvl(l_temp_contact_id,0) = 0 THEN
1544
1545
1546 l_cplv_tbl_in(i).id := l_cpl_id ;--l_party_role.id;
1547 l_cplv_tbl_in(i).cle_id := l_cle_id ;-- l_party_role.cle_id;
1548 l_cplv_tbl_in(i).dnz_chr_id := l_chr_id ; --l_party_role.dnz_chr_id;
1549 okc_contract_party_pub.delete_k_party_role
1550 (p_api_version => l_api_version,
1551 p_init_msg_list => l_init_msg_list,
1552 x_return_status => l_return_status,
1553 x_msg_count => l_msg_count,
1554 x_msg_data => l_msg_data,
1555 p_cplv_tbl => l_cplv_tbl_in);
1556
1557
1558 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1559 Open cur_get_line_number(l_cle_id, l_chr_id);
1560 fetch cur_get_line_number into l_temp_line_number;
1561 close cur_get_line_number;
1562 fnd_msg_pub.initialize;
1563 OKC_API.SET_MESSAGE(
1564 p_app_name => G_APP_NAME,
1565 p_msg_name =>'OKS_CASCADE_CHANGE_ACCOUNT',
1566 p_token1 =>'TOKEN1' ,
1567 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'),
1568 p_token2 => 'TOKEN2',
1569 p_token2_value => l_temp_party_name,
1570 p_token3 => 'TOKEN3',
1571 p_token3_value => l_temp_line_number);
1572 -- fnd_msg_pub.initialize;
1573 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1574 p_encoded => fnd_api.g_false);
1575 x_msg_tbl(l_tot_msg_count).status := 'S';
1576 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1577 l_tot_msg_count := l_tot_msg_count + 1;
1578 l_msg_data := NULL;
1579 END IF;
1580 ELSE
1581 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1582 Open cur_get_line_number(l_cle_id, l_chr_id);
1583 fetch cur_get_line_number into l_temp_line_number;
1584 close cur_get_line_number;
1585 fnd_msg_pub.initialize;
1586 OKC_API.SET_MESSAGE(
1587 p_app_name => G_APP_NAME,
1588 p_msg_name =>'OKS_CASCADE_CHANGE_ACCOUNT',
1589 p_token1 =>'TOKEN1' ,
1590 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'),
1591 p_token2 => 'TOKEN2',
1592 p_token2_value => l_temp_party_name,
1593 p_token3 => 'TOKEN3',
1594 p_token3_value => l_temp_line_number);
1595 -- fnd_msg_pub.initialize;
1596 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1597 p_encoded => fnd_api.g_false);
1598 x_msg_tbl(l_tot_msg_count).status := 'S';
1599 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1600 l_tot_msg_count := l_tot_msg_count + 1;
1601 l_msg_data := NULL;
1602 END IF;
1603
1604 END IF; -- IF nvl(l_temp_contact_id,0) = 0 THEN
1605 -- Bug 5227077 --
1606 ELSIF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1607 FOR i in 1..fnd_msg_pub.count_msg
1608 Loop
1609 fnd_msg_pub.get
1610 (
1611 p_msg_index => i,
1612 p_encoded => 'F',
1613 p_data => l_msg_data,
1614 p_msg_index_out => l_msg_index
1615 );
1616 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1617 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1618 l_tot_msg_count := l_tot_msg_count + 1;
1619 l_msg_data := NULL;
1620 End Loop;
1621 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1622 END IF;
1623 -- Bug 5227077 --
1624 END IF;
1625 END IF; -- IF l_contact_id is NOT NULL THEN
1626 END IF; -- IF l_cpl_id is NOT NULL THEN
1627 END LOOP; -- CUR_PARTY_REC
1628 END IF;
1629
1630
1631 END IF;
1632 -- IKON ENHANCEMENT --
1633 -- *******************************************************************
1634 -- *******************************************************************
1635 -- IKON ENHANCEMENT --
1636 IF l_header_sca is not null then
1637
1638 Open cur_head_cust_acct(l_okc_headers_rec.ship_to_site_use_id);
1639 fetch cur_head_cust_acct into l_header_cust_acct;
1640 close cur_head_cust_acct;
1641
1642
1643 Open CUR_CUST_ACCT(l_okc_lines_rec.ship_to_site_use_id,'SHIP_TO');
1644 fetch CUR_CUST_ACCT into l_line_cust_acct,l_party_id;
1645 close CUR_CUST_ACCT;
1646
1647
1648
1649 -- CASE I If no STO rule is there then create a BTO rule
1650 -- CASE II If there is a STO RULE then
1651 -- Update the STO rule at lines leve with header
1652 -- level Customer account if the accounts are
1653 -- not same.
1654 -- Delete all contacts which are not
1655 -- billing contact and are belonging to
1656 -- ship to account Party Role
1657 -- CASE I
1658 IF l_header_cust_acct <> nvl(l_line_cust_acct, -1) THEN
1659 -- Create/UPDATE a CAN RULE and Create/UPDATE the BTO Rule --
1660 l_clev_tbl_in(1).id := l_cle_id;
1661 l_clev_tbl_in(1).ship_to_site_use_id := l_okc_headers_rec.ship_to_site_use_id;
1662 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1663
1664 IF l_line_cust_acct IS NULL THEN
1665 fnd_msg_pub.initialize;
1666 OKC_API.SET_MESSAGE(
1667 p_app_name => G_APP_NAME_OKS,
1668 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1669 p_token1 => 'ATTRIBUTE',
1670 p_token1_value => 'Ship To Address');
1671
1672 -- fnd_msg_pub.initialize;
1673 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1674 p_encoded => fnd_api.g_false);
1675 x_msg_tbl(l_tot_msg_count).status := 'S';
1676 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1677 l_tot_msg_count := l_tot_msg_count + 1;
1678 l_msg_data := NULL;
1679 END IF;
1680
1681 fnd_msg_pub.initialize;
1682 OKC_API.SET_MESSAGE(
1683 p_app_name => G_APP_NAME_OKS, -- bug 5468539 G_APP_NAME,
1684 p_msg_name =>'OKS_DEFAULT_ATTR_SUCCESS_NEW',
1685 p_token1 => 'TOKEN1', -- bug 5468539 'RULE',
1686 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_SHIP_TO_CUSTOMER_ACCOUNT'));
1687
1688 -- fnd_msg_pub.initialize;
1689 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1690 p_encoded => fnd_api.g_false);
1691 x_msg_tbl(l_tot_msg_count).status := 'S';
1692 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1693 l_tot_msg_count := l_tot_msg_count + 1;
1694 l_msg_data := NULL;
1695
1696 END IF;
1697
1698 -- CASE II
1699 END IF;
1700 IF l_line_cust_acct is not NULL AND l_header_cust_acct <> l_line_cust_acct THEN
1701 open Get_Party_Id (l_line_cust_acct);
1702 fetch Get_party_id into l_line_party_id,l_temp_party_name;
1703 close get_party_id;
1704
1705 -- DELETE CONTACTS --
1706
1707 FOR CUR_PARTY_REC IN CUR_LINE_PARTY_ROLE(l_chr_id, l_cle_id,l_line_party_id ) LOOP
1708 l_cpl_id := CUR_PARTY_REC.ID;
1709
1710 IF l_cpl_id is NOT NULL THEN
1711 l_contact_id := CHECK_LINE_CONTACT(l_chr_id,l_cpl_id );-- 5485054
1712 IF l_contact_id is NOT NULL THEN
1713 l_contact :=0;
1714 -- When Cascading Bill to Cust Account then Don't check for Shipping Contact
1715 FOR contact_rec in line_contact_jtot(l_cpl_id,l_chr_id,'OKX_CONTBILL') LOOP
1716
1717 Open Get_Contact_Cust_Acct(contact_rec.object1_id1);
1718 fetch Get_Contact_Cust_Acct into l_line_cust_acct;
1719 close Get_Contact_Cust_Acct;
1720 -- Delete the contact when Contact Account Id is not same as
1721 -- header Account Id
1722 IF l_header_cust_acct <> l_line_cust_acct THEN
1723
1724 l_ctcv_tbl_in(l_contact).id := contact_rec.id;
1725 l_ctcv_tbl_in(l_contact).cpl_id := l_cpl_id;
1726 l_ctcv_tbl_in(l_contact).dnz_chr_id := contact_rec.dnz_chr_ID;
1727
1728 l_ctcv_tbl_in(l_contact).cro_code := contact_rec.cro_code;
1729 l_ctcv_tbl_in(l_contact).OBJECT1_ID1 := contact_rec.object1_id1;
1730 l_ctcv_tbl_in(l_contact).object1_id2 := contact_rec.OBJECT1_ID2;
1731 l_ctcv_tbl_in(l_contact).JTOT_OBJECT1_CODE := contact_rec.JTOT_OBJECT1_CODE ;
1732 l_ctcv_tbl_in(l_contact).start_date := contact_rec.START_DATE;
1733 l_ctcv_tbl_in(l_contact).end_date := contact_rec.END_DATE;
1734 l_ctcv_tbl_in(l_contact).attribute_category := contact_rec.ATTRIBUTE_CATEGORY;
1735 l_ctcv_tbl_in(l_contact).attribute1 := contact_rec.ATTRIBUTE1;
1736 l_ctcv_tbl_in(l_contact).attribute2 := contact_rec.ATTRIBUTE2;
1737 l_ctcv_tbl_in(l_contact).attribute3 := contact_rec.ATTRIBUTE3;
1738 l_ctcv_tbl_in(l_contact).attribute4 := contact_rec.ATTRIBUTE4;
1739 l_ctcv_tbl_in(l_contact).attribute5 := contact_rec.ATTRIBUTE5;
1740 l_ctcv_tbl_in(l_contact).attribute6 := contact_rec.ATTRIBUTE6;
1741 l_ctcv_tbl_in(l_contact).attribute7 := contact_rec.ATTRIBUTE7;
1742 l_ctcv_tbl_in(l_contact).attribute8 := contact_rec.ATTRIBUTE8;
1743 l_ctcv_tbl_in(l_contact).attribute9 := contact_rec.ATTRIBUTE9;
1744 l_ctcv_tbl_in(l_contact).attribute10 := contact_rec.ATTRIBUTE10;
1745 l_ctcv_tbl_in(l_contact).attribute11 := contact_rec.ATTRIBUTE11;
1746 l_ctcv_tbl_in(l_contact).attribute12 := contact_rec.ATTRIBUTE12;
1747 l_ctcv_tbl_in(l_contact).attribute13 := contact_rec.ATTRIBUTE13;
1748 l_ctcv_tbl_in(l_contact).attribute14 := contact_rec.ATTRIBUTE14;
1749 l_ctcv_tbl_in(l_contact).attribute15 := contact_rec.ATTRIBUTE15;
1750 l_ctcv_tbl_in(l_contact).object_version_number := contact_rec.object_version_number;
1751 l_ctcv_tbl_in(l_contact).created_by := contact_rec.created_by;
1752 l_ctcv_tbl_in(l_contact).creation_date := contact_rec.creation_date;
1753 l_ctcv_tbl_in(l_contact).last_updated_by := contact_rec.last_updated_by;
1754 l_ctcv_tbl_in(l_contact).last_update_date := contact_rec.last_update_date;
1755 l_ctcv_tbl_in(l_contact).last_update_login := contact_rec.last_update_login;
1756 l_contact := l_contact+1;
1757 END IF ;-- line_contact_cust_acct
1758 END LOOP;
1759
1760 IF l_header_cust_acct <> l_line_cust_acct OR l_contact > 1 THEN
1761 okc_contract_party_pub.delete_contact
1762 (p_api_version => l_api_version,
1763 p_init_msg_list => l_init_msg_list,
1764 x_return_status => l_return_status,
1765 x_msg_count => l_msg_count,
1766 x_msg_data => l_msg_data,
1767 p_ctcv_tbl => l_ctcv_tbl_in
1768 );
1769
1770 -- Delete Party Role --
1771 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1772 -- Check if there is any contaact Present belonging to the party role ---
1773 l_temp_contact_id := CHECK_LINE_CONTACT(l_chr_id,l_cpl_id);-- 5485054
1774 IF nvl(l_temp_contact_id,0) = 0 THEN
1775 -- Open Party_role_csr(l_chr_id,l_cle_id);
1776 -- fetch Party_role_csr into l_party_role;
1777 -- close Party_role_csr;
1778
1779 l_cplv_tbl_in(i).id := l_cpl_id ;--l_party_role.id;
1780 l_cplv_tbl_in(i).cle_id := l_cle_id ;-- l_party_role.cle_id;
1781 l_cplv_tbl_in(i).dnz_chr_id := l_chr_id ; --l_party_role.dnz_chr_id;
1782 okc_contract_party_pub.delete_k_party_role
1783 (p_api_version => l_api_version,
1784 p_init_msg_list => l_init_msg_list,
1785 x_return_status => l_return_status,
1786 x_msg_count => l_msg_count,
1787 x_msg_data => l_msg_data,
1788 p_cplv_tbl => l_cplv_tbl_in);
1789
1790
1791 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1792 Open cur_get_line_number(l_cle_id, l_chr_id);
1793 fetch cur_get_line_number into l_temp_line_number;
1794 close cur_get_line_number;
1795 fnd_msg_pub.initialize;
1796 OKC_API.SET_MESSAGE(
1797 p_app_name => G_APP_NAME,
1798 p_msg_name =>'OKS_CASCADE_CHANGE_ACCOUNT',
1799 p_token1 =>'TOKEN1' ,
1800 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'),
1801 p_token2 => 'TOKEN2',
1802 p_token2_value => l_temp_party_name,
1803 p_token3 => 'TOKEN3',
1804 p_token3_value => l_temp_line_number);
1805 -- fnd_msg_pub.initialize;
1806 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1807 p_encoded => fnd_api.g_false);
1808 x_msg_tbl(l_tot_msg_count).status := 'S';
1809 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1810 l_tot_msg_count := l_tot_msg_count + 1;
1811 l_msg_data := NULL;
1812 END IF;
1813 ELSE
1814 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1815 Open cur_get_line_number(l_cle_id, l_chr_id);
1816 fetch cur_get_line_number into l_temp_line_number;
1817 close cur_get_line_number;
1818 fnd_msg_pub.initialize;
1819 OKC_API.SET_MESSAGE(
1820 p_app_name => G_APP_NAME,
1821 p_msg_name =>'OKS_CASCADE_CHANGE_ACCOUNT',
1822 p_token1 =>'TOKEN1' ,
1823 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_SHIP_TO_CUSTOMER_ACCOUNT'),
1824 p_token2 => 'TOKEN2',
1825 p_token2_value => l_temp_party_name,
1826 p_token3 => 'TOKEN3',
1827 p_token3_value => l_temp_line_number);
1828 -- fnd_msg_pub.initialize;
1829 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1830 p_encoded => fnd_api.g_false);
1831 x_msg_tbl(l_tot_msg_count).status := 'S';
1832 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1833 l_tot_msg_count := l_tot_msg_count + 1;
1834 l_msg_data := NULL;
1835 END IF;
1836
1837 END IF; -- IF nvl(l_temp_contact_id,0) = 0 THEN
1838 -- Bug 5227077 --
1839 ELSIF l_return_status <> OKC_API.G_RET_STS_SUCCESS
1840 THEN
1841 FOR i in 1..fnd_msg_pub.count_msg
1842 Loop
1843 fnd_msg_pub.get
1844 (
1845 p_msg_index => i,
1846 p_encoded => 'F',
1847 p_data => l_msg_data,
1848 p_msg_index_out => l_msg_index
1849 );
1850 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1851 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1852 l_tot_msg_count := l_tot_msg_count + 1;
1853 l_msg_data := NULL;
1854 End Loop;
1855 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1856
1857 END IF;
1858 -- Bug 5227077 --
1859 END IF;
1860 END IF; -- IF l_contact_id is NOT NULL THEN
1861 END IF; -- IF l_cpl_id is NOT NULL THEN
1862 END LOOP; -- CUR_PARTY_REC
1863 END IF;
1864
1865
1866 END IF;
1867 -- IKON ENHANCEMENT --
1868 -- *******************************************************************
1869 If (l_header_billto_contact is not null) and (l_billto_id is not null) then -- GC
1870
1871 /*OPEN cur_cust_acct_id(l_chr_id, l_billto_id);
1872 FETCH cur_cust_acct_id INTO l_lov_cust_acct;
1873 CLOSE cur_cust_acct_id; */
1874
1875 Open cur_head_cust_acct(l_okc_headers_rec.bill_to_site_use_id);
1876 fetch cur_head_cust_acct into l_header_cust_acct;
1877 close cur_head_cust_acct;
1878
1879 Open cur_line_cust_acct(l_okc_lines_rec.id);
1880 fetch cur_line_cust_acct into l_line_cust_acct;
1881 close cur_line_cust_acct;
1882
1883
1884 --l_header_cust_acct is the customer account associated with the header level
1885 --l_line_cust_acct is the customer account associated with the contract line
1886
1887 IF ( l_header_cust_acct = l_line_cust_acct) then
1888 Open cur_get_party_id(l_line_cust_acct);
1889 fetch cur_get_party_id into l_party_id;
1890 close cur_get_party_id;
1891
1892 l_cpl_id := CHECK_LINE_PARTY_ROLE(P_DNZ_Chr_Id =>l_chr_id,
1893 P_cle_id =>l_cle_id,
1894 p_party_id => l_party_id);
1895
1896 l_can_id := l_line_cust_acct;
1897
1898 If l_cpl_id IS NULL Then -- GC --00
1899 CREATE_LINE_PARTY_ROLE
1900 (P_DNZ_Chr_Id => l_chr_id ,
1901 P_Cle_Id => l_cle_id,
1902 P_billto_Id => l_billto_id,
1903 p_can_id => l_can_id,
1904 x_cpl_id => l_cpl_id,
1905 x_return_status => l_return_status -- 5219132 --
1906 );
1907 -- Bug 5219132 --
1908
1909 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS)
1910 THEN
1911 FOR i in 1..fnd_msg_pub.count_msg
1912 Loop
1913 fnd_msg_pub.get
1914 (
1915 p_msg_index => i,
1916 p_encoded => 'F',
1917 p_data => l_msg_data,
1918 p_msg_index_out => l_msg_index
1919 );
1920 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1921 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1922 l_tot_msg_count := l_tot_msg_count + 1;
1923 l_msg_data := NULL;
1924 End Loop;
1925 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1926 END IF;
1927
1928 -- Bug 5219132 --
1929 l_contact_id := CHECK_LINE_BILL_CONTACT(l_chr_id,l_cpl_id, 'OKX_CONTBILL', 'CUST_BILLING');-- Bug 5485054
1930
1931 ELSE
1932 l_contact_id := CHECK_LINE_BILL_CONTACT(l_chr_id,l_cpl_id, 'OKX_CONTBILL', 'CUST_BILLING'); -- 5485054
1933 END IF; -- GC --00
1934
1935 IF l_contact_id IS NULL AND l_header_billto_contact IS NOT NULL THEN -- GC-01
1936
1937 l_ctcv_tbl_in(1).cpl_id := l_cpl_id;
1938 l_ctcv_tbl_in(1).dnz_chr_id := l_chr_id ;
1939 l_ctcv_tbl_in(1).cro_code := 'CUST_BILLING';
1940 l_ctcv_tbl_in(1).OBJECT1_ID1 := l_billto_id;
1941 l_ctcv_tbl_in(1).object1_id2 := '#';
1942 --- l_ctcv_tbl_in(1).JTOT_OBJECT1_CODE := cur_header_contact_rec.JTOT_OBJECT1_CODE;
1943 l_ctcv_tbl_in(1).JTOT_OBJECT1_CODE :='OKX_CONTBILL';
1944 okc_contract_party_pub.create_contact
1945 ( p_api_version => l_api_version,
1946 p_init_msg_list => l_init_msg_list,
1947 x_return_status => l_return_status,
1948 x_msg_count => l_msg_count,
1949 x_msg_data => l_msg_data,
1950 p_ctcv_tbl => l_ctcv_tbl_in,
1951 x_ctcv_tbl => l_ctcv_tbl_out);
1952
1953
1954 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1955 -- Bug 5227077 --
1956 FOR i in 1..fnd_msg_pub.count_msg
1957 Loop
1958 fnd_msg_pub.get
1959 (
1960 p_msg_index => i,
1961 p_encoded => 'F',
1962 p_data => l_msg_data,
1963 p_msg_index_out => l_msg_index
1964 );
1965 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1966 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1967 l_tot_msg_count := l_tot_msg_count + 1;
1968 l_msg_data := NULL;
1969 End Loop;
1970 -- Bug 5227077 --
1971 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1972 ELSE
1973 fnd_msg_pub.initialize;
1974 OKC_API.SET_MESSAGE(
1975 p_app_name => G_APP_NAME_OKS,
1976 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1977 p_token1 => 'ATTRIBUTE',
1978 p_token1_value => 'Bill To Contact');
1979 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1980 p_encoded => fnd_api.g_false);
1981 x_msg_tbl(l_tot_msg_count).status := 'S';
1982 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1983 l_tot_msg_count := l_tot_msg_count + 1;
1984 l_msg_data := NULL;
1985 END IF;
1986 ELSIF (l_contact_id IS NOT NULL) AND (l_header_billto_contact IS NOT NULL) THEN
1987 l_ctcv_tbl_in(1).id := l_contact_id;
1988 l_ctcv_tbl_in(1).cpl_id := l_cpl_id;
1989 l_ctcv_tbl_in(1).OBJECT1_ID1 := l_billto_id;
1990 okc_contract_party_pub.update_contact
1991 (p_api_version => l_api_version,
1992 p_init_msg_list => l_init_msg_list,
1993 x_return_status => l_return_status,
1994 x_msg_count => l_msg_count,
1995 x_msg_data => l_msg_data,
1996 p_ctcv_tbl => l_ctcv_tbl_in,
1997 x_ctcv_tbl => l_ctcv_tbl_out);
1998
1999 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2000 -- Bug 5227077 --
2001 FOR i in 1..fnd_msg_pub.count_msg
2002 Loop
2003 fnd_msg_pub.get
2004 (
2005 p_msg_index => i,
2006 p_encoded => 'F',
2007 p_data => l_msg_data,
2008 p_msg_index_out => l_msg_index
2009 );
2010 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2011 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2012 l_tot_msg_count := l_tot_msg_count + 1;
2013 l_msg_data := NULL;
2014 End Loop;
2015 -- Bug 5227077 --
2016 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2017 ELSE
2018 fnd_msg_pub.initialize;
2019 OKC_API.SET_MESSAGE(
2020 p_app_name => G_APP_NAME_OKS,
2021 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2022 p_token1 => 'ATTRIBUTE',
2023 p_token1_value => 'Bill To Contact');
2024 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2025 p_encoded => fnd_api.g_false);
2026 x_msg_tbl(l_tot_msg_count).status := 'S';
2027 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2028 l_tot_msg_count := l_tot_msg_count + 1;
2029 l_msg_data := NULL;
2030
2031 END IF;
2032
2033
2034 END IF; --End GC-01
2035
2036 ELSIF(l_header_cust_acct <> l_line_cust_acct)
2037 THEN
2038 fnd_msg_pub.initialize;
2039 OKC_API.SET_MESSAGE(
2040 p_app_name => G_APP_NAME,
2041 p_msg_name => 'OKS_CASCADE_BTOACCT_MISMATCH',
2042 p_token1 => 'Line No ',
2043 p_token1_value => l_line_number);
2044
2045 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2046 p_encoded => fnd_api.g_false);
2047
2048 x_msg_tbl(l_tot_msg_count).status := 'W'; -- Bug 5219132
2049 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2050 l_tot_msg_count := l_tot_msg_count + 1;
2051 l_msg_data := NULL;
2052
2053 END IF; -- l_header_cust_acct
2054
2055 END IF; --GC
2056
2057 --l_line_tax_status := 'Z'; --BUG#4089834 hkamdar
2058 l_line_tax_status := l_oks_lines_rec.tax_status;
2059
2060 IF l_header_tax IS NOT NULL OR l_header_exception_number IS NOT NULL THEN
2061 -- IKON ENHANCEMENT --
2062 Open cur_head_cust_acct(l_okc_headers_rec.bill_to_site_use_id);
2063 fetch cur_head_cust_acct into l_header_cust_acct;
2064 close cur_head_cust_acct;
2065 l_line_cust_acct := l_okc_lines_rec.cust_acct_id;
2066 -- IKON ENHANCEMENT --
2067 IF l_header_tax IS NOT NULL THEN
2068 --l_insupd_flag := TRUE;
2069 l_status_flag := TRUE;
2070 --Fixed bug#4026268 --gbgupta
2071 --IF l_oks_headers_rec.tax_status = 'R' THEN
2072 --l_oks_headers_rec.tax_code := l_header_tax_code;
2073
2074 --ELSIF l_oks_headers_rec.tax_status = 'E' THEN
2075
2076 --IF l_oks_headers_rec.tax_status = 'E' THEN
2077 IF l_oks_headers_rec.tax_status = 'E' THEN
2078
2079 l_klnv_tbl_in(1).tax_code := NULL;
2080 -- Ebtax --
2081 /*
2082 IF l_header_exception_number IS NULL THEN
2083 l_line_tax_status := l_oks_lines_rec.tax_status;
2084 IF NVL(l_line_tax_status,'X') = 'E' THEN --hkamdar added NVL BUG# 4089834
2085 -- l_insupd_flag := FALSE;
2086 l_status_flag := FALSE;
2087 END IF;
2088 END IF; -- For l_header_exception_number
2089 */
2090 -- 12/1/2005 --
2091 --GCHADHA --
2092 /*
2093 -- When the Tax Status is Exempt then Tax Classification Code should
2094 -- be Nullified.
2095 l_klnv_tbl_in(1).tax_classification_code := NULL;
2096 -- Ebtax --
2097 */
2098 -- END GCHADHA --
2099 END IF;
2100 ELSIF l_header_exception_number IS NOT NULL THEN
2101 l_line_tax_status := l_oks_lines_rec.tax_status;
2102 --Fixed Tax exemption is cascading to line along with bug#4026268 --gbgupta
2103 --Checking else part first. so that it can handle null tax status
2104 /*
2105 IF l_line_tax_status IN('R','S') THEN
2106 l_insupd_flag := FALSE;
2107 ELSE
2108 l_insupd_flag := TRUE;
2109 END IF;
2110 */
2111 IF NVL(l_line_tax_status,'X') = 'E' THEN--hkamdar added NVL BUG# 4089834
2112 --l_insupd_flag := TRUE;
2113 l_exmpt_num_flag := TRUE;
2114 ELSE
2115 --l_insupd_flag := FALSE;
2116 l_exmpt_num_flag := FALSE;
2117 END IF;
2118 ELSE
2119 --l_insupd_flag := FALSE;
2120 l_status_flag := FALSE;
2121 l_exmpt_num_flag := FALSE;
2122 END IF; --l_header_tax
2123
2124 -- IF l_insupd_flag THEN
2125 -- IKON ENHANCEMENT --
2126 IF l_header_cust_acct <> l_line_cust_acct AND l_oks_headers_rec.tax_status = 'E'
2127 AND l_header_bca is NULL -- 5/30/2005
2128 THEN
2129 l_status_flag := FALSE;
2130 l_exmpt_num_flag := FALSE;
2131 END IF ;
2132 -- IKON ENHANCEMENT --
2133
2134 IF l_status_flag or l_exmpt_num_flag THEN
2135
2136 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
2137 --Fixed bug#4026268 --gbgupta
2138 -- l_klnv_tbl_in(1).tax_code := l_oks_headers_rec.tax_code;
2139 --BUG# 4089834 HKAMDAR
2140 -- IKON ENHANCEMENT --
2141 -- if l_status_flag then
2142 If l_status_flag then
2143 -- IKON ENHANCEMENT --
2144 l_klnv_tbl_in(1).tax_status := l_oks_headers_rec.tax_status;
2145 -- If l_header_tax_code is not null THEN
2146 If l_header_tax_cls_code is not null THEN
2147 -- ebtax --
2148 l_klnv_tbl_in(1).tax_classification_code := l_header_tax_cls_code;
2149 l_klnv_tbl_in(1).tax_code := NULL;
2150 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2151 -- l_klnv_tbl_in(1).exempt_certificate_number := NULL;
2152 -- l_klnv_tbl_in(1).exempt_reason_code := NULL;
2153
2154 ELSE
2155 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2156 -- l_klnv_tbl_in(1).exempt_certificate_number := NULL;
2157 -- l_klnv_tbl_in(1).exempt_reason_code := NULL;
2158 End if;
2159 -- Ebtax --
2160 If NVL(l_oks_headers_rec.tax_status, 'X') = 'E' THEN
2161 -- Ebtax --
2162 -- l_klnv_tbl_in(1).tax_exemption_id := l_oks_headers_rec.tax_exemption_id;
2163 IF l_oks_headers_rec.tax_exemption_id IS NOT NULL
2164 THEN
2165
2166 OPEN tax_exemption_number_csr(l_oks_headers_rec.tax_exemption_id);
2167 FETCH tax_exemption_number_csr INTO tax_exemption_number_rec;
2168 CLOSE tax_exemption_number_csr;
2169 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2170 l_klnv_tbl_in(1).exempt_reason_code := tax_exemption_number_rec.description;
2171 l_klnv_tbl_in(1).exempt_certificate_number := tax_exemption_number_rec.customer_exception_number;
2172
2173
2174 ELSIF l_oks_headers_rec.exempt_certificate_number is NOT NULL
2175 THEN
2176 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2177 l_klnv_tbl_in(1).exempt_reason_code := l_oks_headers_rec.exempt_reason_code;
2178 l_klnv_tbl_in(1).exempt_certificate_number := l_oks_headers_rec.exempt_certificate_number;
2179 END IF;
2180 -- Ebtax --
2181
2182 End if;
2183 End if;
2184 -- IKON ENHANCEMENT --
2185 IF l_exmpt_num_flag then
2186 -- IKON ENHANCEMENT --
2187
2188 -- Ebtax --
2189 -- l_klnv_tbl_in(1).tax_exemption_id := l_oks_headers_rec.tax_exemption_id;
2190 IF l_oks_headers_rec.tax_exemption_id IS NOT NULL
2191 THEN
2192 OPEN tax_exemption_number_csr(l_oks_headers_rec.tax_exemption_id);
2193 FETCH tax_exemption_number_csr INTO tax_exemption_number_rec;
2194 CLOSE tax_exemption_number_csr;
2195 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2196 l_klnv_tbl_in(1).exempt_reason_code := tax_exemption_number_rec.description;
2197 l_klnv_tbl_in(1).exempt_certificate_number := tax_exemption_number_rec.customer_exception_number;
2198 ELSIF l_oks_headers_rec.exempt_certificate_number is NOT NULL
2199 THEN
2200 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2201 l_klnv_tbl_in(1).exempt_reason_code := l_oks_headers_rec.exempt_reason_code;
2202 l_klnv_tbl_in(1).exempt_certificate_number := l_oks_headers_rec.exempt_certificate_number;
2203 END IF;
2204 -- Ebtax --
2205 end if;
2206 /*hkamdar
2207 IF NVL(l_oks_headers_rec.tax_status, 'X') = 'E' THEN
2208 l_klnv_tbl_in(1).tax_code := NULL;
2209 END IF;
2210 l_klnv_tbl_in(1).tax_status := l_oks_headers_rec.tax_status;
2211 --Fixed Tax exemption is cascading to line along with bug#4026268 --gbgupta
2212 --assigning tax exemption id
2213 l_klnv_tbl_in(1).tax_exemption_id := l_oks_headers_rec.tax_exemption_id;
2214 */
2215 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2216 fnd_msg_pub.initialize;
2217 OKC_API.SET_MESSAGE(
2218 p_app_name => G_APP_NAME_OKS,
2219 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2220 p_token1 => 'ATTRIBUTE',
2221 p_token1_value => 'Tax');
2222 --fnd_msg_pub.initialize;
2223 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2224 p_encoded => fnd_api.g_false);
2225 x_msg_tbl(l_tot_msg_count).status := 'S';
2226 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2227 l_tot_msg_count := l_tot_msg_count + 1;
2228 l_msg_data := NULL;
2229
2230 END IF;
2231 END IF;-- For l_insupd_flag
2232 END IF; -- For l_header_tax
2233
2234 --Fixed bug#4026268 --gbgupta
2235 IF l_header_tax_code IS NOT NULL THEN
2236 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
2237 -- IF NVL(l_line_tax_status,'X') <> 'E' AND l_exmpt_num_flag THEN --BUG#4089834 hkamdar
2238
2239 IF l_status_flag AND (NVL(l_oks_headers_rec.tax_status, 'X') = 'E') THEN
2240 l_klnv_tbl_in(1).tax_code := NULL;
2241 -- GCHADHA --
2242 -- l_klnv_tbl_in(1).tax_classification_code := NULL;
2243 -- GCHADHA --
2244 -- Ebtax --
2245 -- l_klnv_tbl_in(1).tax_exemption_id := l_oks_headers_rec.tax_exemption_id;
2246 IF l_oks_headers_rec.tax_exemption_id IS NOT NULL
2247 THEN
2248
2249 OPEN tax_exemption_number_csr(l_oks_headers_rec.tax_exemption_id);
2250 FETCH tax_exemption_number_csr INTO tax_exemption_number_rec;
2251 CLOSE tax_exemption_number_csr;
2252 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2253 l_klnv_tbl_in(1).exempt_reason_code := tax_exemption_number_rec.description;
2254 l_klnv_tbl_in(1).exempt_certificate_number := tax_exemption_number_rec.customer_exception_number;
2255
2256
2257 ELSIF l_oks_headers_rec.exempt_certificate_number is NOT NULL
2258 THEN
2259 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2260 l_klnv_tbl_in(1).exempt_reason_code := l_oks_headers_rec.exempt_reason_code;
2261 l_klnv_tbl_in(1).exempt_certificate_number := l_oks_headers_rec.exempt_certificate_number;
2262 END IF;
2263 -- Ebtax --
2264 ELSE
2265 -- IF NVL(l_line_tax_status,'X') <> 'E' THEN -- GCHADHA 12/1/2005
2266 -- l_klnv_tbl_in(1).tax_code := l_header_tax_code_id;
2267 -- Ebtax --
2268 l_klnv_tbl_in(1).tax_classification_code := l_header_tax_cls_code;
2269 -- Ebtax --
2270
2271 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2272 fnd_msg_pub.initialize;
2273 OKC_API.SET_MESSAGE(
2274 p_app_name => G_APP_NAME_OKS,
2275 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2276 p_token1 => 'ATTRIBUTE',
2277 p_token1_value => 'Tax Code');
2278 --fnd_msg_pub.initialize;
2279 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2280 p_encoded => fnd_api.g_false);
2281 x_msg_tbl(l_tot_msg_count).status := 'S';
2282 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2283 l_tot_msg_count := l_tot_msg_count + 1;
2284 l_msg_data := NULL;
2285
2286 END IF;
2287 -- END IF; -- Gchadha 12/1/2005
2288 END IF;-- BUG#4089834 hkamdar
2289 END IF;
2290
2291 IF l_billing_profile IS NOT NULL THEN
2292 IF l_billing_profile_id IS NOT NULL THEN
2293 --Fix for Bug# 3542273
2294 OPEN cur_billing_profile(l_billing_profile_id);
2295 FETCH cur_billing_profile INTO l_bpf_acct_rule_id,l_bpf_invoice_rule_id;
2296 IF cur_billing_profile%NOTFOUND then
2297 CLOSE cur_billing_profile;
2298 x_return_status := 'E';
2299 RAISE G_EXCEPTION_HALT_VALIDATION;
2300 ELSE
2301 CLOSE cur_billing_profile;
2302 END IF;
2303
2304 If l_okc_lines_rec.lse_id = '12' then
2305
2306 l_usage_type := l_oks_lines_rec.usage_type;
2307
2308 If l_usage_type in ('VRT','QTY') then
2309 IF l_bpf_invoice_rule_id = -2 THEN
2310 l_token1_value := fnd_meaning(l_usage_type,'OKS_USAGE_TYPE');
2311
2312 fnd_msg_pub.initialize;
2313 OKC_API.SET_MESSAGE
2314 (
2315 p_app_name => G_APP_NAME_OKS,
2316 p_msg_name => 'OKS_USAGE_ATTR_CHECK',
2317 p_token1 => 'TOKEN1',
2318 p_token1_value => l_token1_value,
2319 p_token2 => 'Line No ',
2320 p_token2_value => l_line_number
2321 );
2322
2323 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2324 p_encoded => fnd_api.g_false);
2325 x_msg_tbl(l_tot_msg_count).status := 'E';
2326 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2327 l_tot_msg_count := l_tot_msg_count + 1;
2328 l_msg_data := NULL;
2329 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2330 End If;
2331 End If;
2332 End If;
2333 --Fix for Bug# 3542273
2334 OPEN cur_line_dates(header_lines_tbl(i).cle_id);
2335 FETCH cur_line_dates INTO l_bpf_start_date, l_bpf_end_Date, l_bpf_lse_id;
2336 CLOSE cur_line_dates;
2337
2338 OPEN cur_sub_line(l_cle_id);
2339 FETCH cur_sub_line INTO l_sub_line_rec;
2340 CLOSE cur_sub_line;
2341
2342 If NOT oks_extwar_util_pvt.check_already_billed(
2343 p_chr_id => null,
2344 p_cle_id => header_lines_tbl(i).cle_id,
2345 p_lse_id => l_bpf_lse_id,
2346 p_end_date => null
2347 )
2348
2349 THEN
2350
2351 l_rec.start_date := l_bpf_start_date;
2352 l_rec.end_date := l_bpf_end_Date;
2353 l_rec.cle_Id := header_lines_tbl(i).cle_id;
2354 l_rec.chr_Id := header_lines_tbl(i).chr_id;
2355 l_rec.Billing_Profile_Id := header_lines_tbl(i).billing_profile_id;
2356
2357 OKS_BILLING_PROFILES_PUB.Get_Billing_Schedule
2358 (p_api_version => 1.0,
2359 p_init_msg_list =>'T',
2360 p_billing_profile_rec => l_rec,
2361 x_sll_tbl_out => l_sll_tbl_out,
2362 x_return_status => l_return_status,
2363 x_msg_count => l_msg_count,
2364 x_msg_data => l_msg_data);
2365
2366 -- Bug 5227077 --
2367 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2368 FOR i in 1..fnd_msg_pub.count_msg
2369 Loop
2370 fnd_msg_pub.get
2371 (
2372 p_msg_index => i,
2373 p_encoded => 'F',
2374 p_data => l_msg_data,
2375 p_msg_index_out => l_msg_index
2376 );
2377 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2378 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2379 l_tot_msg_count := l_tot_msg_count + 1;
2380 l_msg_data := NULL;
2381 End Loop;
2382 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2383 END IF;
2384 -- Bug 5227077 --
2385
2386
2387 -- Delete_Sll_If_Exists(l_rec.cle_id);
2388 l_top_line_id := l_rec.cle_id;
2389
2390 OKS_BILL_SCH.Del_rul_elements
2391 (
2392 p_top_line_id => l_top_line_id,
2393 x_return_status => l_return_status,
2394 x_msg_count => l_msg_count,
2395 x_msg_data => l_msg_data
2396 );
2397
2398 -- Bug 5227077 --
2399 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2400 FOR i in 1..fnd_msg_pub.count_msg
2401 Loop
2402 fnd_msg_pub.get
2403 (
2404 p_msg_index => i,
2405 p_encoded => 'F',
2406 p_data => l_msg_data,
2407 p_msg_index_out => l_msg_index
2408 );
2409 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2410 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2411 l_tot_msg_count := l_tot_msg_count + 1;
2412 l_msg_data := NULL;
2413 End Loop;
2414 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2415 END IF;
2416 -- Bug 5227077 --
2417
2418
2419
2420 l_invoice_rule_id := l_bpf_invoice_rule_id;
2421
2422 --new
2423 l_clev_tbl_in(1).inv_rule_id := l_invoice_rule_id;
2424 --new
2425
2426 l_klnv_tbl_in(1).acct_rule_id := l_bpf_acct_rule_id;
2427 l_sll_tbl(1).Sequence_no := l_sll_tbl_out(1).seq_no;
2428 l_sll_tbl(1).uom_code := l_sll_tbl_out(1).timeunit;
2429 l_sll_tbl(1).uom_per_period := l_sll_tbl_out(1).duration;
2430 l_sll_tbl(1).level_periods := l_sll_tbl_out(1).target_quantity;
2431 l_sll_tbl(1).invoice_offset_days := l_sll_tbl_out(1).Invoice_Offset;
2432 l_sll_tbl(1).interface_offset_days := l_sll_tbl_out(1).Interface_Offset;
2433 -- Bug 5406141 --
2434 -- l_sll_tbl(1).Chr_Id := l_sll_tbl_out(1).Chr_Id;
2435 l_sll_tbl(1).Cle_Id := l_sll_tbl_out(1).Cle_Id;
2436 l_billing_type := l_sll_tbl_out(1).billing_type;
2437 -- Bug 5406141 --
2438
2439 OKS_BILL_SCH.Create_Bill_Sch_Rules
2440 (
2441 p_billing_type => l_billing_type
2442 ,p_sll_tbl => l_sll_tbl
2443 ,p_invoice_rule_id => l_invoice_rule_id
2444 ,x_bil_sch_out_tbl => l_bil_sch_out_tbl
2445 ,x_return_status => l_return_status
2446 );
2447
2448 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2449 FOR i in 1..fnd_msg_pub.count_msg
2450 Loop
2451 fnd_msg_pub.get
2452 (
2453 p_msg_index => i,
2454 p_encoded => 'F',
2455 p_data => l_msg_data,
2456 p_msg_index_out => l_msg_index
2457 );
2458 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2459 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2460 l_tot_msg_count := l_tot_msg_count + 1;
2461 l_msg_data := NULL;
2462 End Loop;
2463 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2464 ELSE
2465 fnd_msg_pub.initialize;
2466 OKC_API.SET_MESSAGE(
2467 p_app_name => G_APP_NAME_OKS,
2468 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2469 p_token1 => 'ATTRIBUTE',
2470 p_token1_value => 'Billing Schedule');
2471 --fnd_msg_pub.initialize;
2472 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2473 p_encoded => fnd_api.g_false);
2474 x_msg_tbl(l_tot_msg_count).status := 'S';
2475 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2476 l_tot_msg_count := l_tot_msg_count + 1;
2477 l_msg_data := NULL;
2478
2479 END IF;
2480
2481 END IF; --for check already billed
2482
2483 Open cur_bill_to_address_id1(l_billing_profile_id);
2484 fetch cur_bill_to_address_id1 into l_bill_to_address_id1;
2485 close cur_bill_to_address_id1;
2486
2487 IF (l_bill_to_address_id1 IS NOT NULL) THEN
2488
2489 l_cle_Id := header_lines_tbl(i).cle_id;
2490 l_chr_Id := header_lines_tbl(i).chr_id;
2491 l_line_cust_acct := l_okc_headers_rec.cust_acct_id;
2492
2493 Open cur_bpf_cust_acct(l_billing_profile_id, header_dates_rec.authoring_org_id);
2494 fetch cur_bpf_cust_acct into l_bpf_cust_acct;
2495 close cur_bpf_cust_acct;
2496
2497 IF l_line_cust_acct = l_bpf_cust_acct THEN
2498
2499 l_clev_tbl_in(1).id := l_cle_id;
2500 l_clev_tbl_in(1).bill_to_site_use_id := l_okc_headers_rec.bill_to_site_use_id;
2501
2502 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2503 fnd_msg_pub.initialize;
2504 OKC_API.SET_MESSAGE(
2505 p_app_name => G_APP_NAME_OKS,
2506 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2507 p_token1 => 'ATTRIBUTE',
2508 p_token1_value => 'Bill To Address');
2509 --fnd_msg_pub.initialize;
2510 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2511 p_encoded => fnd_api.g_false);
2512 x_msg_tbl(l_tot_msg_count).status := 'S';
2513 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2514 l_tot_msg_count := l_tot_msg_count + 1;
2515 l_msg_data := NULL;
2516
2517 END IF;
2518 END IF;
2519 END IF;
2520 Elsif l_billing_profile_id is NULL then
2521 --Display error message, saying required value missing.
2522 fnd_msg_pub.initialize;
2523 OKC_API.SET_MESSAGE(
2524 p_app_name => G_APP_NAME_OKS,
2525 p_msg_name => 'OKS_CASCADE_MISSING_REQD_ATTR',
2526 p_token1 => 'ATTRIBUTE',
2527 p_token1_value => 'Billing Profile');
2528
2529 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2530 p_encoded => fnd_api.g_false);
2531 x_msg_tbl(l_tot_msg_count).status := 'E';
2532 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2533 l_tot_msg_count := l_tot_msg_count + 1;
2534 l_msg_data := NULL;
2535
2536 END IF; --l_billing_profile_id
2537 END IF;--l_billing_profile
2538
2539 ---------------------------------------------------------------------------------
2540 IF l_header_arl is not null then
2541 IF l_billing_profile IS NOT NULL and
2542 l_billing_profile_id is not null THEN
2543
2544 l_klnv_tbl_in(1).acct_rule_id :=l_bpf_acct_rule_id;
2545 ELSE
2546 l_klnv_tbl_in(1).acct_rule_id := l_oks_headers_rec.acct_rule_id;
2547 END IF;
2548 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
2549
2550 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2551 fnd_msg_pub.initialize;
2552 OKC_API.SET_MESSAGE(
2553 p_app_name => G_APP_NAME_OKS,
2554 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2555 p_token1 => 'ATTRIBUTE',
2556 p_token1_value => 'Accounting Rule');
2557
2558 --fnd_msg_pub.initialize;
2559 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2560 p_encoded => fnd_api.g_false);
2561 x_msg_tbl(l_tot_msg_count).status := 'S';
2562 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2563 l_tot_msg_count := l_tot_msg_count + 1;
2564 l_msg_data := NULL;
2565
2566 END IF;
2567
2568 ELSIF l_header_arl is NULL AND l_billing_profile IS NOT NULL and
2569 l_billing_profile_id is not null then
2570
2571 --Fix for Bug# 3542273
2572
2573 If l_okc_lines_rec.lse_id = '12' then
2574
2575 l_usage_type := l_oks_lines_rec.usage_type;
2576
2577 If l_usage_type in ('VRT','QTY') then
2578 IF l_bpf_invoice_rule_id = -2 THEN
2579 l_token1_value := fnd_meaning(l_usage_type,'OKS_USAGE_TYPE');
2580
2581 fnd_msg_pub.initialize;
2582 OKC_API.SET_MESSAGE
2583 (
2584 p_app_name => G_APP_NAME_OKS,
2585 p_msg_name => 'OKS_USAGE_ATTR_CHECK',
2586 p_token1 => 'TOKEN1',
2587 p_token1_value => l_token1_value,
2588 p_token2 => 'Line No ',
2589 p_token2_value => l_line_number
2590 );
2591
2592 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2593 p_encoded => fnd_api.g_false);
2594 x_msg_tbl(l_tot_msg_count).status := 'E';
2595 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2596 l_tot_msg_count := l_tot_msg_count + 1;
2597 l_msg_data := NULL;
2598 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2599 End If;
2600 End If;
2601 End If;
2602 --Fix for Bug# 3542273
2603
2604 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
2605 l_klnv_tbl_in(1).acct_rule_id := l_bpf_acct_rule_id;
2606
2607 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2608 fnd_msg_pub.initialize;
2609 OKC_API.SET_MESSAGE(
2610 p_app_name => G_APP_NAME_OKS,
2611 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2612 p_token1 => 'ATTRIBUTE',
2613 p_token1_value => 'Accounting Rule');
2614
2615 --fnd_msg_pub.initialize;
2616 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2617 p_encoded => fnd_api.g_false);
2618 x_msg_tbl(l_tot_msg_count).status := 'S';
2619 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2620 l_tot_msg_count := l_tot_msg_count + 1;
2621 l_msg_data := NULL;
2622 END IF;
2623 END IF;
2624
2625
2626 -----------------------------------------------------------------------
2627 IF l_header_ire is not null then
2628 --Fix for Bug# 3542273
2629 IF l_billing_profile IS NOT NULL and
2630 l_billing_profile_id is not null THEN
2631
2632 l_clev_tbl_in(1).inv_rule_id :=l_invoice_rule_id;
2633 ELSE
2634 l_clev_tbl_in(1).inv_rule_id := l_okc_headers_rec.inv_rule_id;
2635 END IF;
2636
2637 If l_okc_lines_rec.lse_id = '12' then
2638
2639 l_usage_type := l_oks_lines_rec.usage_type;
2640
2641 If l_usage_type in ('VRT','QTY') then
2642 If l_clev_tbl_in(1).inv_rule_id = -2 then
2643 l_token1_value := fnd_meaning(l_usage_type,'OKS_USAGE_TYPE');
2644
2645 fnd_msg_pub.initialize;
2646 OKC_API.SET_MESSAGE
2647 (
2648 p_app_name => G_APP_NAME_OKS,
2649 p_msg_name => 'OKS_USAGE_ATTR_CHECK',
2650 p_token1 => 'TOKEN1',
2651 p_token1_value => l_token1_value,
2652 p_token2 => 'Line No ',
2653 p_token2_value => l_line_number
2654 );
2655
2656 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2657 p_encoded => fnd_api.g_false);
2658 x_msg_tbl(l_tot_msg_count).status := 'E';
2659 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2660 l_tot_msg_count := l_tot_msg_count + 1;
2661 l_msg_data := NULL;
2662 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2663 End If;
2664 End If;
2665 End If;
2666 --Fix for Bug# 3542273
2667
2668 l_clev_tbl_in(1).id := l_cle_id;
2669
2670 OKS_BILL_SCH.update_bs_interface_date
2671 (
2672 p_top_line_id => l_cle_id,
2673 p_invoice_rule_id => l_clev_tbl_in(1).inv_rule_id,
2674 x_return_status => l_return_status,
2675 x_msg_count => l_msg_count,
2676 x_msg_data => l_msg_data
2677 );
2678
2679 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2680 fnd_msg_pub.initialize;
2681 OKC_API.SET_MESSAGE(
2682 p_app_name => G_APP_NAME_OKS,
2683 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2684 p_token1 => 'ATTRIBUTE',
2685 p_token1_value => 'Invoicing Rule');
2686
2687 --fnd_msg_pub.initialize;
2688 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2689 p_encoded => fnd_api.g_false);
2690 x_msg_tbl(l_tot_msg_count).status := 'S';
2691 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2692 l_tot_msg_count := l_tot_msg_count + 1;
2693 l_msg_data := NULL;
2694 -- Bug 5227077 --
2695 Elsif NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2696 FOR i in 1..fnd_msg_pub.count_msg
2697 Loop
2698 fnd_msg_pub.get
2699 (
2700 p_msg_index => i,
2701 p_encoded => 'F',
2702 p_data => l_msg_data,
2703 p_msg_index_out => l_msg_index
2704 );
2705 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2706 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2707 l_tot_msg_count := l_tot_msg_count + 1;
2708 l_msg_data := NULL;
2709 End Loop;
2710 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2711 END IF;
2712 -- Bug 5227077 --
2713
2714 ELSIF l_header_ire is NULL AND l_billing_profile IS NOT NULL and
2715 l_billing_profile_id is not null then
2716 --Fix for Bug# 3542273
2717
2718 If l_okc_lines_rec.lse_id = '12' then
2719
2720 l_usage_type := l_oks_lines_rec.usage_type;
2721
2722 If l_usage_type in ('VRT','QTY') then
2723 IF l_bpf_invoice_rule_id = -2 THEN
2724 l_token1_value := fnd_meaning(l_usage_type,'OKS_USAGE_TYPE');
2725 fnd_msg_pub.initialize;
2726 OKC_API.SET_MESSAGE
2727 (
2728 p_app_name => G_APP_NAME_OKS,
2729 p_msg_name => 'OKS_USAGE_ATTR_CHECK',
2730 p_token1 => 'TOKEN1',
2731 p_token1_value => l_token1_value,
2732 p_token2 => 'Line No ',
2733 p_token2_value => l_line_number
2734 );
2735 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2736 p_encoded => fnd_api.g_false);
2737 x_msg_tbl(l_tot_msg_count).status := 'E';
2738 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2739 l_tot_msg_count := l_tot_msg_count + 1;
2740 l_msg_data := NULL;
2741 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2742 End If;
2743 End If;
2744 End If;
2745 --Fix for Bug# 3542273
2746 l_clev_tbl_in(1).id := l_cle_id;
2747 -- changed for Bug 4064138
2748 -- for billed contractflow dosenot go inside check if billed
2749 -- hence the value for l_invoice_rule_id dosenot get assigned.
2750 l_clev_tbl_in(1).inv_rule_id :=l_bpf_invoice_rule_id; --l_invoice_rule_id;
2751 -- change for bug 4064138.
2752
2753 OKS_BILL_SCH.update_bs_interface_date
2754 (
2755 p_top_line_id => l_cle_id,
2756 p_invoice_rule_id => l_clev_tbl_in(1).inv_rule_id,
2757 x_return_status => l_return_status,
2758 x_msg_count => l_msg_count,
2759 x_msg_data => l_msg_data
2760 );
2761 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2762 FOR i in 1..fnd_msg_pub.count_msg
2763 Loop
2764 fnd_msg_pub.get
2765 (
2766 p_msg_index => i,
2767 p_encoded => 'F',
2768 p_data => l_msg_data,
2769 p_msg_index_out => l_msg_index
2770 );
2771 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2772 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2773 l_tot_msg_count := l_tot_msg_count + 1;
2774 l_msg_data := NULL;
2775 End Loop;
2776 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2777
2778 ELSE
2779 fnd_msg_pub.initialize;
2780 OKC_API.SET_MESSAGE(
2781 p_app_name => G_APP_NAME_OKS,
2782 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2783 p_token1 => 'ATTRIBUTE',
2784 p_token1_value => 'Invoicing Rule');
2785
2786 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2787 p_encoded => fnd_api.g_false);
2788 x_msg_tbl(l_tot_msg_count).status := 'S';
2789 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2790 l_tot_msg_count := l_tot_msg_count + 1;
2791 l_msg_data := NULL;
2792 END IF;
2793 END IF;
2794
2795 If l_header_sales_credits is not null then
2796 --Fetch was doing only for one line. Now the Fetch is in a loop(Bug#2374793)
2797 l_sc_index := 1;
2798 l_scrv_tbl_in.DELETE;
2799
2800
2801 OPEN Scredit_csr_line(l_chr_id,L_cle_id);
2802 LOOP
2803 FETCH Scredit_csr_line into l_scredit_count,l_scredit_id;
2804
2805 EXIT WHEN Scredit_csr_line%NOTFOUND;
2806 l_scrv_tbl_in(l_sc_index).id := l_scredit_id;
2807
2808 l_sc_index := l_sc_index + 1;
2809 END LOOP;
2810 CLOSE Scredit_csr_line;
2811
2812 IF l_scrv_tbl_in.COUNT > 0 THEN
2813 OKS_SALES_CREDIT_PUB.delete_Sales_credit
2814 (
2815 p_api_version => 1.0,
2816 p_init_msg_list => 'T',
2817 x_return_status => l_return_status,
2818 x_msg_count => l_msg_count,
2819 x_msg_data => l_msg_data,
2820 p_scrv_tbl => l_scrv_tbl_in);
2821 -- Bug 527077 --
2822 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2823 FOR i in 1..fnd_msg_pub.count_msg
2824 Loop
2825 fnd_msg_pub.get
2826 (
2827 p_msg_index => i,
2828 p_encoded => 'F',
2829 p_data => l_msg_data,
2830 p_msg_index_out => l_msg_index
2831 );
2832 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2833 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2834 l_tot_msg_count := l_tot_msg_count + 1;
2835 l_msg_data := NULL;
2836 End Loop;
2837 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2838 End if;
2839 -- Bug 5227077 --
2840
2841 END IF; -- End if for plsql table count.
2842
2843 OKS_EXTWAR_UTIL_PVT.CREATE_SALES_CREDITS
2844 (
2845 P_header_id =>l_chr_id
2846 ,P_line_id => l_cle_id
2847 ,X_return_status => l_return_status
2848 );
2849
2850
2851 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2852 fnd_msg_pub.initialize;
2853 OKC_API.SET_MESSAGE(
2854 p_app_name => G_APP_NAME_OKS,
2855 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2856 p_token1 => 'ATTRIBUTE',
2857 p_token1_value => 'Sales Credit');
2858
2859 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2860 p_encoded => fnd_api.g_false);
2861 x_msg_tbl(l_tot_msg_count).status := 'S';
2862 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2863 l_tot_msg_count := l_tot_msg_count + 1;
2864 l_msg_data := NULL;
2865 -- Bug 5227077 --
2866 Elsif not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2867 FOR i in 1..fnd_msg_pub.count_msg
2868 Loop
2869 fnd_msg_pub.get
2870 (
2871 p_msg_index => i,
2872 p_encoded => 'F',
2873 p_data => l_msg_data,
2874 p_msg_index_out => l_msg_index
2875 );
2876 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2877 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2878 l_tot_msg_count := l_tot_msg_count + 1;
2879 l_msg_data := NULL;
2880 End Loop;
2881 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2882 End if;
2883 -- Bug 5227077 --
2884
2885 END IF;
2886
2887 -- Made changes for bug 4394382 --
2888 -- When we cascade payment method from header to line in case of Commitment number
2889 -- verify the bill to cust Account on header and lines level. If same then cascaded
2890 -- the commitment number.
2891
2892 IF l_payment_method IS NOT NULL THEN
2893
2894 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
2895 -- l_klnv_tbl_in(1).payment_type := l_oks_headers_rec.payment_type; -- BUG 4394382
2896
2897 -- BUG 3691147 AND 3675745 --
2898 -- PROVIDE FIX FOR ISSUE 3675745 AS WELL --
2899 -- GCHADHA 15 - JUN - 2004 --
2900 l_klnv_tbl_in(1).cust_po_number := l_okc_headers_rec.cust_po_number;
2901 l_klnv_tbl_in(1).cust_po_number_req_yn := l_okc_headers_rec.cust_po_number_req_yn;
2902 -- Bank Account Consolidation --
2903 l_clev_tbl_in(1).id := l_cle_id;
2904 l_clev_tbl_in(1).payment_instruction_type := l_okc_headers_rec.payment_instruction_type;
2905 -- Bank Account Consolidation --
2906
2907 Open cur_oks_comm_line(l_okc_lines_rec.id);
2908 fetch cur_oks_comm_line into l_oks_comm_line_rec;
2909 close cur_oks_comm_line;
2910
2911 Open cur_get_Party_Id (l_okc_lines_rec.cust_acct_id);
2912 fetch cur_get_party_id into l_oks_party_rec ;
2913 close cur_get_party_id;
2914
2915 IF nvl(l_oks_comm_line_rec.cust_po_number,-1) = nvl(l_okc_headers_rec.cust_po_number, -1)
2916 AND Nvl(l_oks_comm_line_rec.cust_po_number_req_yn,'X') = nvl(l_okc_headers_rec.cust_po_number_req_yn, 'X')
2917 AND Nvl(l_oks_comm_line_rec.payment_instruction_type,'X') = nvl(l_okc_headers_rec.payment_instruction_type, 'X')
2918 THEN
2919 l_cust_po_flag := 1;
2920 END IF ;
2921 Open cur_head_cust_acct(l_okc_headers_rec.bill_to_site_use_id);
2922 fetch cur_head_cust_acct into l_header_cust_acct;
2923 close cur_head_cust_acct;
2924 l_line_cust_acct := l_okc_lines_rec.cust_acct_id;
2925
2926 IF l_oks_headers_rec.payment_type = 'COM' THEN
2927
2928 IF l_oks_headers_rec.commitment_id is NULL THEN
2929 l_klnv_tbl_in(1).payment_type := l_oks_headers_rec.payment_type;
2930 -- Bank Account Consolidation --
2931 /* l_klnv_tbl_in(1).cc_no := NULL;
2932 l_klnv_tbl_in(1).cc_expiry_date := NULL;
2933 l_klnv_tbl_in(1).cc_bank_acct_id := NULL;
2934 l_klnv_tbl_in(1).cc_auth_code := NULL; */
2935 -- Bank Account Consolidation --
2936 -- Call the delete API to Delete Credit card details
2937 -- if the credit card details are present on the line level
2938 IF l_oks_comm_line_rec.trxn_extension_id IS NOT NULL
2939 THEN
2940 Delete_credit_Card
2941 (p_trnx_ext_id => l_oks_comm_line_rec.trxn_extension_id,
2942 p_line_id => l_cle_id,
2943 p_party_id => l_oks_party_rec.party_id ,
2944 p_cust_account_id => l_line_cust_acct,
2945 x_return_status => l_return_status,
2946 x_msg_data => l_msg_data );
2947 -- Bug 5227077 --
2948 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2949 FOR i in 1..fnd_msg_pub.count_msg
2950 Loop
2951 fnd_msg_pub.get
2952 (
2953 p_msg_index => i,
2954 p_encoded => 'F',
2955 p_data => l_msg_data,
2956 p_msg_index_out => l_msg_index
2957 );
2958 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2959 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2960 l_tot_msg_count := l_tot_msg_count + 1;
2961 l_msg_data := NULL;
2962 End Loop;
2963 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2964 end if;
2965 -- Bug 5227077 --
2966
2967 END IF;
2968 l_klnv_tbl_in(1).trxn_extension_id := NULL;
2969 l_klnv_tbl_in(1).commitment_id := NULL;
2970 l_payment_method_com := 1; -- 4394382
2971 ELSIF nvl(l_header_cust_acct,-1) = nvl(l_line_cust_acct , -9) or l_header_bca is NOT NULL
2972 THEN
2973 l_klnv_tbl_in(1).payment_type := l_oks_headers_rec.payment_type;
2974 -- Bank Account Consolidation --
2975 /* l_klnv_tbl_in(1).cc_no := NULL;
2976 l_klnv_tbl_in(1).cc_expiry_date := NULL;
2977 l_klnv_tbl_in(1).cc_bank_acct_id := NULL;
2978 l_klnv_tbl_in(1).cc_auth_code := NULL; */
2979 -- Call the delete API to Delete Credit card details--
2980 IF l_oks_comm_line_rec.trxn_extension_id IS NOT NULL
2981 THEN
2982 Delete_credit_Card
2983 (p_trnx_ext_id => l_oks_comm_line_rec.trxn_extension_id,
2984 p_line_id => l_cle_id,
2985 p_party_id => l_oks_party_rec.party_id ,
2986 p_cust_account_id => l_header_cust_acct,
2987 x_return_status => l_return_status,
2988 x_msg_data => l_msg_data );
2989
2990 -- Bug 5227077 --
2991 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2992 FOR i in 1..fnd_msg_pub.count_msg
2993 Loop
2994 fnd_msg_pub.get
2995 (
2996 p_msg_index => i,
2997 p_encoded => 'F',
2998 p_data => l_msg_data,
2999 p_msg_index_out => l_msg_index
3000 );
3001 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3002 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3003 l_tot_msg_count := l_tot_msg_count + 1;
3004 l_msg_data := NULL;
3005 End Loop;
3006 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3007 End if;
3008 -- Bug 5227077 --
3009
3010 END IF;
3011 l_klnv_tbl_in(1).trxn_extension_id := NULL;
3012 -- Bank Account Consolidation --
3013 l_klnv_tbl_in(1).commitment_id := l_oks_headers_rec.commitment_id;
3014 l_payment_method_com := 1; -- 4394382
3015 END IF;
3016 -- END GCHADHA --
3017
3018 -- BUG 3691147 --
3019 -- GCHADHA 3691147 --
3020 /*
3021 -- FIX FOR THE BUG 3675745
3022 -- GCHADHA 9-06-2004 --
3023 l_klnv_tbl_in(1).cust_po_number := l_okc_headers_rec.cust_po_number;
3024 - l_klnv_tbl_in(1).cust_po_number_req_yn := l_okc_headers_rec.cust_po_number_req_yn;
3025 -- END GCHADHA --
3026 */
3027 -- END GCHADHA --
3028 -- l_klnv_tbl_in(1).cust_po_number := NULL;
3029 -- l_klnv_tbl_in(1).cust_po_number_req_yn := NULL;
3030
3031
3032 ELSIF l_oks_headers_rec.payment_type = 'CCR' THEN
3033 -- Bank Account consolidation --
3034 IF l_oks_headers_rec.trxn_extension_id is NULL THEN
3035 IF l_oks_comm_line_rec.trxn_extension_id IS NOT NULL
3036 THEN
3037 Delete_credit_Card
3038 (p_trnx_ext_id => l_oks_comm_line_rec.trxn_extension_id,
3039 p_line_id => l_cle_id,
3040 -- p_party_id => l_party_id ,
3041 p_party_id =>l_oks_party_rec.party_id,
3042 p_cust_account_id => l_line_cust_acct,
3043 x_return_status => l_return_status,
3044 x_msg_data => l_msg_data );
3045
3046 -- Bug 5227077 --
3047 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3048 FOR i in 1..fnd_msg_pub.count_msg
3049 Loop
3050 fnd_msg_pub.get
3051 (
3052 p_msg_index => i,
3053 p_encoded => 'F',
3054 p_data => l_msg_data,
3055 p_msg_index_out => l_msg_index
3056 );
3057 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3058 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3059 l_tot_msg_count := l_tot_msg_count + 1;
3060 l_msg_data := NULL;
3061 End Loop;
3062 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3063 End if;
3064 -- Bug 5227077 --
3065
3066 END IF;
3067 l_klnv_tbl_in(1).payment_type := l_oks_headers_rec.payment_type;
3068 l_klnv_tbl_in(1).trxn_extension_id := NULL;
3069 l_klnv_tbl_in(1).commitment_id := NULL;
3070 l_payment_method_ccr := 1; -- 4394382
3071 -- Cascade the credit card number only when the header and lines level
3072 -- bill to accounts are same or if we have already marked cascade of
3073 -- billto account from header to lines.
3074 ELSIF nvl(l_header_cust_acct,-1) = nvl(l_line_cust_acct , -9) or l_header_bca is NOT NULL
3075 THEN
3076 l_klnv_tbl_in(1).payment_type := l_oks_headers_rec.payment_type;
3077
3078 /* l_klnv_tbl_in(1).cc_no := l_oks_headers_rec.cc_no;
3079 l_klnv_tbl_in(1).cc_expiry_date := l_oks_headers_rec.cc_expiry_date;
3080 l_klnv_tbl_in(1).cc_bank_acct_id := l_oks_headers_rec.cc_bank_acct_id;
3081 l_klnv_tbl_in(1).cc_auth_code := l_oks_headers_rec.cc_auth_code; */
3082 IF l_oks_comm_line_rec.trxn_extension_id IS NOT NULL
3083 THEN
3084 Delete_credit_Card
3085 (p_trnx_ext_id => l_oks_comm_line_rec.trxn_extension_id,
3086 p_line_id => l_cle_id,
3087 -- p_party_id => l_party_id ,
3088 p_party_id =>l_oks_party_rec.party_id,
3089 p_cust_account_id => l_header_cust_acct,
3090 x_return_status => l_return_status,
3091 x_msg_data => l_msg_data );
3092 -- Bug 5227077 --
3093 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3094 FOR i in 1..fnd_msg_pub.count_msg
3095 Loop
3096 fnd_msg_pub.get
3097 (
3098 p_msg_index => i,
3099 p_encoded => 'F',
3100 p_data => l_msg_data,
3101 p_msg_index_out => l_msg_index
3102 );
3103 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3104 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3105 l_tot_msg_count := l_tot_msg_count + 1;
3106 l_msg_data := NULL;
3107 End Loop;
3108 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3109 End if;
3110 -- Bug 5227077 --
3111
3112 END IF;
3113 -- Create Credit Card Details --
3114 l_trxn_extension_id := Create_credit_Card
3115 ( p_line_id => l_cle_id ,
3116 p_party_id => l_oks_party_rec.party_id ,
3117 p_org => l_okc_headers_rec.authoring_org_id,
3118 p_account_site_id => l_okc_lines_rec.bill_to_site_use_id ,
3119 p_cust_account_id => l_header_cust_acct,
3120 p_trnx_ext_id => l_oks_headers_rec.trxn_extension_id,
3121 x_return_status => l_return_status,
3122 x_msg_data => l_msg_data);
3123
3124 -- Bug 5227077 --
3125 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3126 FOR i in 1..fnd_msg_pub.count_msg
3127 Loop
3128 fnd_msg_pub.get
3129 (
3130 p_msg_index => i,
3131 p_encoded => 'F',
3132 p_data => l_msg_data,
3133 p_msg_index_out => l_msg_index
3134 );
3135 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3136 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3137 l_tot_msg_count := l_tot_msg_count + 1;
3138 l_msg_data := NULL;
3139 End Loop;
3140 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3141 End if;
3142 -- Bug 5227077 --
3143
3144
3145 l_klnv_tbl_in(1).trxn_extension_id := l_trxn_extension_id;
3146 l_klnv_tbl_in(1).commitment_id := NULL;
3147 l_payment_method_ccr := 1; -- 4394382
3148 END IF;
3149 -- Bank Account Consolidation --
3150 -- BUG 3691147 --
3151 -- GCHADHA 3691147 --
3152 /*
3153 -- FIX FOR THE BUG 3675745
3154 -- GCHADHA 9-06-2004 --
3155 l_klnv_tbl_in(1).cust_po_number := l_okc_headers_rec.cust_po_number;
3156 l_klnv_tbl_in(1).cust_po_number_req_yn := l_okc_headers_rec.cust_po_number_req_yn;
3157 -- END GCHADHA --
3158 */
3159 -- GCHADHA --
3160 -- l_klnv_tbl_in(1).cust_po_number := NULL;
3161 -- l_klnv_tbl_in(1).cust_po_number_req_yn := NULL;
3162 -- GCHADHA --
3163 -- Case when the payment method at header level is null
3164 -- and at line level payment method is not null
3165 ELSIF l_oks_headers_rec.payment_type is NULL THEN
3166
3167 l_klnv_tbl_in(1).payment_type := NULL;
3168 -- Bank Account Consolidation --
3169 /* l_klnv_tbl_in(1).cc_no := NULL;
3170 l_klnv_tbl_in(1).cc_expiry_date := NULL;
3171 l_klnv_tbl_in(1).cc_bank_acct_id := NULL;
3172 l_klnv_tbl_in(1).cc_auth_code := NULL; */
3173
3174 IF l_oks_comm_line_rec.trxn_extension_id IS NOT NULL
3175 THEN
3176 Delete_credit_Card
3177 (p_trnx_ext_id => l_oks_comm_line_rec.trxn_extension_id,
3178 p_line_id => l_cle_id,
3179 p_party_id => l_oks_party_rec.party_id ,
3180 p_cust_account_id =>l_line_cust_acct,
3181 x_return_status => l_return_status,
3182 x_msg_data => l_msg_data );
3183
3184 -- Bug 5227077 --
3185 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3186 FOR i in 1..fnd_msg_pub.count_msg
3187 Loop
3188 fnd_msg_pub.get
3189 (
3190 p_msg_index => i,
3191 p_encoded => 'F',
3192 p_data => l_msg_data,
3193 p_msg_index_out => l_msg_index
3194 );
3195 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3196 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3197 l_tot_msg_count := l_tot_msg_count + 1;
3198 l_msg_data := NULL;
3199 End Loop;
3200 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3201 End if;
3202 -- Bug 5227077 --
3203
3204 END IF;
3205 l_klnv_tbl_in(1).trxn_extension_id :=NULL;
3206 -- Bank Account Consolidation --
3207 l_klnv_tbl_in(1).commitment_id := NULL;
3208 l_payment_method_ccr := 1; -- 4394382
3209
3210 END IF;
3211
3212 IF l_payment_method_com = 1 OR l_cust_po_flag = 0 OR l_payment_method_ccr = 1 THEN
3213 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3214
3215 fnd_msg_pub.initialize;
3216 OKC_API.SET_MESSAGE(
3217 p_app_name => G_APP_NAME_OKS,
3218 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3219 p_token1 => 'ATTRIBUTE',
3220 p_token1_value => 'Payment Method');
3221
3222 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3223 p_encoded => fnd_api.g_false);
3224 x_msg_tbl(l_tot_msg_count).status := 'S';
3225 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3226 l_tot_msg_count := l_tot_msg_count + 1;
3227 l_msg_data := NULL;
3228 END IF;
3229 END IF;
3230
3231 END IF;
3232
3233 -- 8/23/2005 hkamdar R12 Partial Period
3234 -- Price UOM Cascading
3235 IF l_header_price_uom is not null then
3236 ----errorout('Price uom not null');
3237
3238 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
3239 l_klnv_tbl_in(1).price_uom := l_oks_headers_rec.price_uom;
3240 ----errorout('UOM-'||l_klnv_tbl_in(1).price_uom);
3241 fnd_msg_pub.initialize;
3242 OKC_API.SET_MESSAGE(
3243 p_app_name => G_APP_NAME_OKS,
3244 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3245 p_token1 => 'ATTRIBUTE',
3246 p_token1_value => 'Price UOM');
3247
3248 l_msg_data := fnd_msg_pub.get( p_msg_index => fnd_msg_pub.g_first,
3249 p_encoded => fnd_api.g_false);
3250
3251 x_msg_tbl(l_tot_msg_count).status := 'S';
3252 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3253 l_tot_msg_count := l_tot_msg_count + 1;
3254 l_msg_data := NULL;
3255
3256 END IF;
3257
3258 --- price list cascading
3259
3260 IF l_header_price_list is not null then
3261
3262 -- Validate before cascading the Price List wheather the
3263 -- price list is valid or not.
3264 QP_UTIL_PUB.Validate_Price_list_Curr_code(
3265 l_price_list_id => l_okc_headers_rec.price_list_id
3266 ,l_currency_code => l_okc_headers_rec.currency_code
3267 ,l_pricing_effective_date => l_pricing_effective_date
3268 ,l_validate_result => l_validate_result);
3269
3270 IF NVL(l_validate_result,'N') IN ('U' ,'N') then -- Unexpected Error
3271 fnd_msg_pub.initialize;
3272 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,p_encoded => fnd_api.g_false);
3273 OKC_API.SET_MESSAGE(
3274 p_app_name => 'OKS', --G_APP_NAME_OKS,
3275 p_msg_name => 'OKS_INVALID_PRICE_LIST');
3276 x_msg_tbl(l_tot_msg_count).status := 'E';
3277 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3278 l_tot_msg_count := l_tot_msg_count + 1;
3279 l_msg_data := NULL;
3280 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3281 END IF;
3282
3283 l_clev_tbl_in(1).ID := l_okc_lines_rec.ID;
3284 l_clev_tbl_in(1).price_list_id := l_okc_headers_rec.price_list_id;
3285
3286 If l_okc_lines_rec.lse_id = '12' then -- Usage line
3287 open cur_get_lines_details (l_chr_id , l_okc_lines_rec.ID);
3288 fetch cur_get_lines_details into
3289 l_locked_price_list_id,l_locked_price_list_line_id;
3290 close cur_get_lines_details;
3291
3292 l_source_price_list_line_id := l_locked_price_list_line_id;
3293
3294 If l_source_price_list_line_id IS NOT NULL THEN
3295 oks_qp_pkg.delete_locked_pricebreaks(l_api_version,
3296 l_source_price_list_line_id,
3297 l_init_msg_list,
3298 l_return_status,
3299 l_msg_count,
3300 l_msg_data);
3301 -- Bug 5227077 --
3302 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3303 FOR i in 1..fnd_msg_pub.count_msg
3304 Loop
3305 fnd_msg_pub.get
3306 (
3307 p_msg_index => i,
3308 p_encoded => 'F',
3309 p_data => l_msg_data,
3310 p_msg_index_out => l_msg_index
3311 );
3312 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3313 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3314 l_tot_msg_count := l_tot_msg_count + 1;
3315 l_msg_data := NULL;
3316 End Loop;
3317 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3318 End if;
3319 -- Bug 5227077 --
3320
3321 l_locked_prl_cnt := l_locked_prl_cnt + 1;
3322
3323 l_klnv_tbl_in(1).ID := l_oks_lines_rec.id;
3324 l_klnv_tbl_in(1).locked_price_list_id := null;
3325 l_klnv_tbl_in(1).locked_price_list_line_id := null;
3326
3327
3328 fnd_msg_pub.initialize;
3329 OKC_API.SET_MESSAGE(
3330 p_app_name => G_APP_NAME_OKS,
3331 p_msg_name => 'OKS_HEADER_CASCADE_WARNING',
3332 p_token1 => 'ATTRIBUTE',
3333 p_token1_value => 'Price List');
3334
3335 l_msg_data := fnd_msg_pub.get( p_msg_index => fnd_msg_pub.g_first,
3336 p_encoded => fnd_api.g_false);
3337 x_msg_tbl(l_tot_msg_count).status := 'S';
3338 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3339 l_tot_msg_count := l_tot_msg_count + 1;
3340 l_msg_data := NULL;
3341 ELSE
3342 -- To display the Cascade Message when
3343 -- Usage lines is not locked and the cascade take place --
3344 fnd_msg_pub.initialize;
3345 OKC_API.SET_MESSAGE(
3346 p_app_name => G_APP_NAME_OKS,
3347 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3348 p_token1 => 'ATTRIBUTE',
3349 p_token1_value => 'Price List');
3350
3351 l_msg_data := fnd_msg_pub.get( p_msg_index => fnd_msg_pub.g_first,
3352 p_encoded => fnd_api.g_false);
3353 x_msg_tbl(l_tot_msg_count).status := 'S';
3354 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3355 l_tot_msg_count := l_tot_msg_count + 1;
3356 l_msg_data := NULL;
3357
3358 END IF;
3359 Else
3360 fnd_msg_pub.initialize;
3361 OKC_API.SET_MESSAGE(
3362 p_app_name => G_APP_NAME_OKS,
3363 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3364 p_token1 => 'ATTRIBUTE',
3365 p_token1_value => 'Price List');
3366
3367 l_msg_data := fnd_msg_pub.get( p_msg_index => fnd_msg_pub.g_first,
3368 p_encoded => fnd_api.g_false);
3369 x_msg_tbl(l_tot_msg_count).status := 'S';
3370 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3371 l_tot_msg_count := l_tot_msg_count + 1;
3372 l_msg_data := NULL;
3373
3374 End If;
3375
3376 END IF;
3377
3378 -- End 8/23/2005 hkamdar R12 Partial Period
3379
3380 --Fixed Tax exemption is cascading to line along with bug#4026268 --gbgupta
3381 --checking l_insupd_flag flag along with l_header_tax IS NOT NULL
3382 --l_header_exception_number IS NOT NULL
3383 -- IKON ENHANCEMENT --
3384 IF (l_header_tax IS NOT NULL AND (l_status_flag or l_exmpt_num_flag)) OR --l_insupd_flag) OR
3385 l_header_tax_code IS NOT NULL OR
3386 ( l_header_bca IS NOT NULL AND l_oks_header_id = 1) OR
3387 (l_billing_profile IS NOT NULL AND
3388 l_billing_profile_id IS NOT NULL) OR
3389 l_header_arl IS NOT NULL OR
3390 (l_header_exception_number IS NOT NULL AND (l_status_flag or l_exmpt_num_flag)) OR --l_insupd_flag) OR
3391 l_payment_method IS NOT NULL OR
3392 -- 8/23/2005 hkamdar R12 Partial Period
3393 -- additional condition is added to the if statement to update the oks_k_lines.
3394 -- if price_uom is changed
3395 l_header_price_uom IS NOT NULL
3396 OR (l_header_price_list is not null and l_locked_prl_cnt > 0)
3397 -- and l_locked_prl_cnt > 0 THEN
3398 OR l_line_irt IS NOT NULL -- Added for Bug#15936043
3399 THEN
3400 -- Resetting the locked price counter.
3401 l_locked_prl_cnt := 0;
3402 -- End 8/23/2005 hkamdar R12 Partial Period
3403 --Update oks lines
3404 oks_contract_line_pub.update_line
3405 (
3406 p_api_version => l_api_version,
3407 p_init_msg_list => l_init_msg_list,
3408 x_return_status => l_return_status,
3409 x_msg_count => l_msg_count,
3410 x_msg_data => l_msg_data,
3411 p_klnv_tbl => l_klnv_tbl_in,
3412 x_klnv_tbl => l_klnv_tbl_out,
3413 p_validate_yn => 'N'
3414 );
3415 --errorout('Return status -'||l_return_status);
3416 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3417
3418 FOR i in 1..fnd_msg_pub.count_msg
3419 Loop
3420 fnd_msg_pub.get
3421 (
3422 p_msg_index => i,
3423 p_encoded => 'F',
3424 p_data => l_msg_data,
3425 p_msg_index_out => l_msg_index
3426 );
3427
3428 x_msg_tbl(l_tot_msg_count).status := l_return_status; --'E';
3429 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3430 l_tot_msg_count := l_tot_msg_count + 1;
3431 l_msg_data := NULL;
3432 End Loop;
3433 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3434 End If;
3435
3436
3437 END IF;
3438 -- IKON ENHANCEMENT --
3439 IF l_header_sto IS NOT NULL OR
3440 l_header_bto IS NOT NULL OR
3441 l_header_sca IS NOT NULL OR
3442 l_header_bca IS NOT NULL OR
3443 l_header_billto_contact IS NOT NULL OR
3444 l_header_ire IS NOT NULL OR
3445 (l_billing_profile IS NOT NULL AND
3446 l_billing_profile_id IS NOT NULL) OR
3447 l_header_dates IS NOT NULL OR
3448 l_payment_method IS NOT NULL OR-- Payment Instruction Type
3449 -- 8/23/2005 hkamdar R12 Partial Period
3450 -- additional condition is added to the if statement to update the okc_k_lines.
3451 -- if price list is changed
3452 l_header_price_list IS NOT NULL
3453
3454
3455 THEN
3456 update_line(
3457 p_clev_tbl => l_clev_tbl_in,
3458 x_clev_tbl => l_clev_tbl_out,
3459 x_return_status => l_return_status,
3460 x_msg_count => l_msg_count,
3461 x_msg_data => l_msg_data
3462 );
3463
3464
3465 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3466
3467 FOR i in 1..fnd_msg_pub.count_msg
3468 Loop
3469 fnd_msg_pub.get
3470 (
3471 p_msg_index => i,
3472 p_encoded => 'F',
3473 p_data => l_msg_data,
3474 p_msg_index_out => l_msg_index
3475 );
3476
3477 x_msg_tbl(l_tot_msg_count).status := l_return_status; --'E';
3478 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3479 l_tot_msg_count := l_tot_msg_count + 1;
3480 l_msg_data := NULL;
3481 End Loop;
3482 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3483 End If;
3484
3485 END IF;
3486
3487 -- 8/23/2005 hkamdar R12 Partial Period
3488
3489 IF l_header_price_list IS NOT NULL and l_return_status = FND_API.G_RET_STS_SUCCESS
3490 then
3491 -- Bug 4668385 --
3492 l_input_details.line_id := l_cle_id;
3493 If l_okc_lines_rec.lse_id = '46' Then
3494 l_input_details.intent := 'SB_P';
3495 Else
3496 l_input_details.intent := 'LP';
3497 End If;
3498 -- Bug 4668385 --
3499
3500 oks_qp_int_pvt.compute_Price
3501 (
3502 p_api_version => 1.0,
3503 p_init_msg_list => 'T',
3504 p_detail_rec => l_input_details,
3505 x_price_details => l_output_details,
3506 x_modifier_details => l_modif_details,
3507 x_price_break_details => l_pb_details,
3508 x_return_status => l_return_status,
3509 x_msg_count => l_msg_count,
3510 x_msg_data => l_msg_data
3511 );
3512
3513
3514
3515
3516 l_status_tbl := oks_qp_int_pvt.get_Pricing_Messages;
3517
3518 if l_status_tbl.Count > 0 Then
3519 For i in l_status_tbl.FIRST..l_status_tbl.LAST
3520 Loop
3521 x_msg_tbl(l_tot_msg_count).status := l_status_tbl(i).Status_Code;
3522 x_msg_tbl(l_tot_msg_count).description := l_status_tbl(i).Status_Text;
3523 l_tot_msg_count := l_tot_msg_count + 1;
3524
3525 End Loop;
3526 end if;
3527 -- Bug 5381082 --
3528 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
3529 FOR i in 1..fnd_msg_pub.count_msg
3530 Loop
3531 fnd_msg_pub.get
3532 (
3533 p_msg_index => i,
3534 p_encoded => 'F',
3535 p_data => l_msg_data,
3536 p_msg_index_out => l_msg_index
3537 );
3538 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3539 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3540 l_tot_msg_count := l_tot_msg_count + 1;
3541 l_msg_data := NULL;
3542 End Loop;
3543 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3544 End If;
3545 -- BUg 5381082 --
3546 END IF;
3547 -- End 8/23/2005 hkamdar R12 Partial Period
3548
3549 If l_header_dates is not null and l_return_status = FND_API.G_RET_STS_SUCCESS then
3550 --Fix for bug#3424479
3551 -- FIX DONE FOR USAGE CONTRACT SHOWING INCORRECT MESSAGE WHEN CASCADING DATES.
3552 -- ADDED CHECK FOR USAGE LINE lse_id <> 12
3553 -- GCHADHA 8-JULY-2004
3554 If l_okc_lines_rec.lse_id not in (46, 12) AND nvl(l_oks_lines_rec.standard_cov_yn, 'N') <> 'Y' then
3555
3556 Open LineCov_cur(l_cle_id);
3557 Fetch LineCov_cur into l_id;
3558 If LineCov_cur%Found Then
3559
3560 OKS_COVERAGES_PVT.Update_COVERAGE_Effectivity(
3561 p_api_version => l_api_version,
3562 p_init_msg_list => l_init_msg_list,
3563 x_return_status => l_return_status,
3564 x_msg_count => l_msg_count,
3565 x_msg_data => l_msg_data,
3566 p_service_Line_Id => l_cle_id,
3567 p_New_Start_Date => header_dates_rec.start_date,
3568 p_New_End_Date => header_dates_rec.end_date );
3569
3570 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3571
3572 FOR i in 1..fnd_msg_pub.count_msg
3573 Loop
3574 fnd_msg_pub.get
3575 (
3576 p_msg_index => i,
3577 p_encoded => 'F',
3578 p_data => l_msg_data,
3579 p_msg_index_out => l_msg_index
3580 );
3581
3582 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3583 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3584 l_tot_msg_count := l_tot_msg_count + 1;
3585 l_msg_data := NULL;
3586 End Loop;
3587 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3588
3589 ELSE
3590 -- Bug 5191587 --
3591 If l_okc_lines_rec.lse_id in ('1','14', '19') and l_sr_number IS NOT NULL
3592 then
3593 fnd_msg_pub.initialize;
3594
3595 OKC_API.SET_MESSAGE(
3596 p_app_name => G_APP_NAME_OKS,
3597 p_msg_name => 'OKS_HEADER_CASCADE_SR_SUCCESS',
3598 p_token1 => 'SR#',
3599 p_token1_value => l_sr_number,
3600 p_token2 => 'Line No ',
3601 p_token2_value => l_line_number
3602 );
3603
3604 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3605 p_encoded => fnd_api.g_false);
3606 x_msg_tbl(l_tot_msg_count).status := 'W';
3607 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3608 l_tot_msg_count := l_tot_msg_count + 1;
3609 l_msg_data := NULL;
3610
3611 fnd_msg_pub.initialize;
3612
3613 -- Issue a warning message if dates are cascaded from header to lines for a fully billed contract.
3614 IF(l_disp_warning ='Y') THEN
3615
3616 OKC_API.SET_MESSAGE(
3617 p_app_name => G_APP_NAME_OKS,
3618 p_msg_name => 'OKS_HEADER_CASCADE_DATES_WARN');
3619
3620 x_msg_tbl(l_tot_msg_count).status := 'W';
3621 ELSE
3622
3623 OKC_API.SET_MESSAGE(
3624 p_app_name => G_APP_NAME_OKS,
3625 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3626 p_token1 => 'ATTRIBUTE',
3627 p_token1_value => 'Date');
3628 x_msg_tbl(l_tot_msg_count).status := 'S';
3629 END IF;
3630
3631 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3632 p_encoded => fnd_api.g_false);
3633 -- x_msg_tbl(l_tot_msg_count).status := 'S';
3634 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3635 l_tot_msg_count := l_tot_msg_count + 1;
3636 l_msg_data := NULL;
3637
3638 Else
3639 fnd_msg_pub.initialize;
3640
3641 -- Issue a warning message if dates are cascaded from header to lines for a fully billed contract.
3642 IF(l_disp_warning ='Y') THEN
3643
3644 OKC_API.SET_MESSAGE(
3645 p_app_name => G_APP_NAME_OKS,
3646 p_msg_name => 'OKS_HEADER_CASCADE_DATES_WARN');
3647
3648 x_msg_tbl(l_tot_msg_count).status := 'W';
3649 ELSE
3650 OKC_API.SET_MESSAGE(
3651 p_app_name => G_APP_NAME_OKS,
3652 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3653 p_token1 => 'ATTRIBUTE',
3654 p_token1_value => 'Date');
3655 x_msg_tbl(l_tot_msg_count).status := 'S';
3656 END IF;
3657 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3658 p_encoded => fnd_api.g_false);
3659 -- x_msg_tbl(l_tot_msg_count).status := 'S';
3660 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3661 l_tot_msg_count := l_tot_msg_count + 1;
3662 l_msg_data := NULL;
3663
3664 End If;
3665 END IF;
3666
3667 Else --LineCov_cur% not Found Then
3668 fnd_msg_pub.initialize;
3669 OKC_API.SET_MESSAGE
3670 (
3671 p_app_name => G_APP_NAME_OKS,
3672 p_msg_name => 'OKS_CASCADE_COV_NOT_FOUND',
3673 p_token2 => 'Line No ',
3674 p_token2_value => l_line_number
3675 );
3676
3677 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3678 p_encoded => fnd_api.g_false);
3679 x_msg_tbl(l_tot_msg_count).status := 'E';
3680 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3681 l_tot_msg_count := l_tot_msg_count + 1;
3682 l_msg_data := NULL;
3683
3684 END IF;
3685
3686 close LINEcov_cur;
3687
3688 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3689
3690 OKS_PM_PROGRAMS_PVT.ADJUST_PM_PROGRAM_SCHEDULE
3691 (p_api_version => l_api_version,
3692 p_init_msg_list => l_init_msg_list,
3693 p_contract_line_id => l_cle_id,
3694 p_new_start_date => header_dates_rec.start_date,
3695 p_new_end_date => header_dates_rec.end_date,
3696 x_return_status => l_return_status,
3697 x_msg_count => l_msg_count,
3698 x_msg_data => l_msg_data
3699 );
3700 -- Bug 5227077 --
3701 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3702 FOR i in 1..fnd_msg_pub.count_msg
3703 Loop
3704 fnd_msg_pub.get
3705 (
3706 p_msg_index => i,
3707 p_encoded => 'F',
3708 p_data => l_msg_data,
3709 p_msg_index_out => l_msg_index
3710 );
3711 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3712 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3713 l_tot_msg_count := l_tot_msg_count + 1;
3714 l_msg_data := NULL;
3715 End Loop;
3716 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3717 end if;
3718 -- Bug 5227077 --
3719
3720 End If;
3721 -- Bug 5191587 --
3722 ELSIf l_okc_lines_rec.lse_id in ('1','14','19') AND nvl(l_oks_lines_rec.standard_cov_yn, 'N') = 'Y' then
3723
3724 /*added for bug 10132468*/
3725
3726 OKS_PM_PROGRAMS_PVT.ADJUST_PM_PROGRAM_SCHEDULE
3727 (p_api_version => l_api_version,
3728 p_init_msg_list => l_init_msg_list,
3729 p_contract_line_id => l_cle_id,
3730 p_new_start_date => header_dates_rec.start_date,
3731 p_new_end_date => header_dates_rec.end_date,
3732 x_return_status => l_return_status,
3733 x_msg_count => l_msg_count,
3734 x_msg_data => l_msg_data
3735 );
3736 -- Bug 5227077 --
3737 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3738 FOR i in 1..fnd_msg_pub.count_msg
3739 Loop
3740 fnd_msg_pub.get
3741 (
3742 p_msg_index => i,
3743 p_encoded => 'F',
3744 p_data => l_msg_data,
3745 p_msg_index_out => l_msg_index
3746 );
3747 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3748 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3749 l_tot_msg_count := l_tot_msg_count + 1;
3750 l_msg_data := NULL;
3751 End Loop;
3752 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3753 end if;
3754
3755 /*added for bug 10132468*/
3756
3757 If l_sr_number IS NOT NULL then
3758 fnd_msg_pub.initialize;
3759 OKC_API.SET_MESSAGE(
3760 p_app_name => G_APP_NAME_OKS,
3761 p_msg_name => 'OKS_HEADER_CASCADE_SR_SUCCESS',
3762 p_token1 => 'SR#',
3763 p_token1_value => l_sr_number,
3764 p_token2 => 'Line No ',
3765 p_token2_value => l_line_number
3766 );
3767
3768 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3769 p_encoded => fnd_api.g_false);
3770 x_msg_tbl(l_tot_msg_count).status := 'W';
3771 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3772 l_tot_msg_count := l_tot_msg_count + 1;
3773 l_msg_data := NULL;
3774
3775 fnd_msg_pub.initialize;
3776 -- Issue a warning message if dates are cascaded from header to lines for a fully billed contract.
3777 IF(l_disp_warning ='Y') THEN
3778
3779 OKC_API.SET_MESSAGE(
3780 p_app_name => G_APP_NAME_OKS,
3781 p_msg_name => 'OKS_HEADER_CASCADE_DATES_WARN');
3782
3783 x_msg_tbl(l_tot_msg_count).status := 'W';
3784 ELSE
3785
3786 OKC_API.SET_MESSAGE(
3787 p_app_name => G_APP_NAME_OKS,
3788 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3789 p_token1 => 'ATTRIBUTE',
3790 p_token1_value => 'Date');
3791 x_msg_tbl(l_tot_msg_count).status := 'S';
3792 END IF;
3793
3794 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3795 p_encoded => fnd_api.g_false);
3796 -- x_msg_tbl(l_tot_msg_count).status := 'S';
3797 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3798 l_tot_msg_count := l_tot_msg_count + 1;
3799 l_msg_data := NULL;
3800
3801 Else
3802 fnd_msg_pub.initialize;
3803
3804 -- Issue a warning message if dates are cascaded from header to lines for a fully billed contract.
3805 IF(l_disp_warning ='Y') THEN
3806
3807 OKC_API.SET_MESSAGE(
3808 p_app_name => G_APP_NAME_OKS,
3809 p_msg_name => 'OKS_HEADER_CASCADE_DATES_WARN');
3810
3811 x_msg_tbl(l_tot_msg_count).status := 'W';
3812 ELSE
3813
3814 OKC_API.SET_MESSAGE(
3815 p_app_name => G_APP_NAME_OKS,
3816 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3817 p_token1 => 'ATTRIBUTE',
3818 p_token1_value => 'Date');
3819 x_msg_tbl(l_tot_msg_count).status := 'S';
3820
3821 END IF;
3822 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3823 p_encoded => fnd_api.g_false);
3824 --x_msg_tbl(l_tot_msg_count).status := 'S';
3825 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3826 l_tot_msg_count := l_tot_msg_count + 1;
3827 l_msg_data := NULL;
3828
3829 End If;
3830 -- Bug 5191587 --
3831 Else --lse_id = 46
3832 fnd_msg_pub.initialize;
3833
3834 -- Issue a warning message if dates are cascaded from header to lines for a fully billed contract.
3835 IF(l_disp_warning ='Y') THEN
3836
3837 OKC_API.SET_MESSAGE(
3838 p_app_name => G_APP_NAME_OKS,
3839 p_msg_name => 'OKS_HEADER_CASCADE_DATES_WARN');
3840
3841 x_msg_tbl(l_tot_msg_count).status := 'W';
3842 ELSE
3843
3844 OKC_API.SET_MESSAGE(
3845 p_app_name => G_APP_NAME_OKS,
3846 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3847 p_token1 => 'ATTRIBUTE',
3848 p_token1_value => 'Date');
3849 x_msg_tbl(l_tot_msg_count).status := 'S';
3850 END IF;
3851 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3852 p_encoded => fnd_api.g_false);
3853 --x_msg_tbl(l_tot_msg_count).status := 'S';
3854 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3855 l_tot_msg_count := l_tot_msg_count + 1;
3856 l_msg_data := NULL;
3857
3858 End If; --lse_id <> 46
3859
3860 END IF;
3861
3862
3863 IF l_calculate_tax is not null then
3864 l_lse_id := l_okc_lines_rec.lse_id;
3865 l_price_negotiated := l_okc_lines_rec.price_negotiated;
3866
3867 G_RAIL_REC.amount := l_price_negotiated;
3868 IF l_lse_id <> 46 THEN
3869
3870 --get all sublines and call tax engine and update IRT rule
3871 -------------------------------------------------------------------
3872 For sub_line_rec IN cur_sub_line(l_cle_id)
3873 Loop
3874 G_RAIL_REC.amount := sub_line_rec.price_negotiated;
3875 OKS_TAX_UTIL_PVT.Get_Tax
3876 (
3877 p_api_version => 1.0,
3878 p_init_msg_list => OKC_API.G_TRUE,
3879 p_chr_id => l_chr_id,
3880 p_cle_id => sub_line_rec.id, --l_cle_id,
3881 px_rail_rec => G_RAIL_REC,
3882 x_msg_count => x_msg_count,
3883 x_msg_data => x_msg_data,
3884 x_return_status => l_return_status
3885 );
3886
3887 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
3888 FOR i in 1..fnd_msg_pub.count_msg
3889 Loop
3890 fnd_msg_pub.get
3891 (
3892 p_msg_index => i,
3893 p_encoded => 'F',
3894 p_data => l_msg_data,
3895 p_msg_index_out => l_msg_index
3896 );
3897 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3898 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3899 l_tot_msg_count := l_tot_msg_count + 1;
3900 l_msg_data := NULL;
3901 End Loop;
3902 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3903 End If;
3904
3905 l_UNIT_SELLING_PRICE := G_RAIL_REC.UNIT_SELLING_PRICE;
3906 l_QUANTITY := G_RAIL_REC.QUANTITY;
3907 l_sub_total := G_RAIL_REC.AMOUNT;
3908 l_AMOUNT_INCLUDES_TAX_FLAG := G_RAIL_REC.AMOUNT_INCLUDES_TAX_FLAG; --Rule_information5
3909 l_Tax_Code := G_RAIL_REC.TAX_CODE;
3910 l_TAX_RATE := G_RAIL_REC.TAX_RATE ;
3911 l_Tax_Value := G_RAIL_REC.TAX_VALUE; --Rule_information4
3912
3913 IF l_AMOUNT_INCLUDES_TAX_FLAG IS NULL THEN
3914 l_AMOUNT_INCLUDES_TAX_FLAG := 'N';
3915 END IF;
3916
3917 IF l_AMOUNT_INCLUDES_TAX_FLAG = 'Y' THEN
3918 l_total_amt := 0;
3919 l_Tax_Value := 0;
3920 Else
3921 l_total := l_sub_total + l_Tax_Value;
3922 l_total_amt := l_total;
3923 END IF;
3924
3925 If sub_line_rec.id is not null THEN
3926 OPEN cur_oks_lines(sub_line_rec.id);
3927 FETCH cur_oks_lines INTO l_oks_lines_rec;
3928 IF cur_oks_lines%NOTFOUND then
3929 CLOSE cur_oks_lines;
3930 x_return_status := 'E';
3931 RAISE G_EXCEPTION_HALT_VALIDATION;
3932 ELSE
3933 CLOSE cur_oks_lines;
3934 END IF;
3935
3936 /*added for bug 7387293*/
3937
3938 l_kslnv_tbl_in(1).ID := l_oks_lines_rec.ID;
3939 l_kslnv_tbl_in(1).tax_amount := l_Tax_Value;
3940 l_kslnv_tbl_in(1).tax_inclusive_yn := l_AMOUNT_INCLUDES_TAX_FLAG;
3941 l_kslnv_tbl_in(1).OBJECT_VERSION_NUMBER := l_oks_lines_rec.OBJECT_VERSION_NUMBER;
3942
3943 /*added for bug 7387293*/
3944 --Update oks lines
3945
3946 oks_contract_line_pub.update_line
3947 (
3948 p_api_version => l_api_version,
3949 p_init_msg_list => l_init_msg_list,
3950 x_return_status => l_return_status,
3951 x_msg_count => l_msg_count,
3952 x_msg_data => l_msg_data,
3953 p_klnv_tbl => l_kslnv_tbl_in,
3954 x_klnv_tbl => l_kslnv_tbl_out,
3955 p_validate_yn => 'N'
3956 );
3957
3958
3959 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
3960 FOR i in 1..fnd_msg_pub.count_msg
3961 Loop
3962 fnd_msg_pub.get
3963 (
3964 p_msg_index => i,
3965 p_encoded => 'F',
3966 p_data => l_msg_data,
3967 p_msg_index_out => l_msg_index
3968 );
3969 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3970 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3971 l_tot_msg_count := l_tot_msg_count + 1;
3972 l_msg_data := NULL;
3973 End Loop;
3974
3975 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3976 End If;
3977 /*
3978 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3979 fnd_msg_pub.initialize;
3980 OKC_API.SET_MESSAGE(
3981 p_app_name => G_APP_NAME_OKS,
3982 p_msg_name => 'OKS_RECALCULATE_TAX_SUCCESS',
3983 p_token1 => 'ATTRIBUTE',
3984 p_token1_value => 'Recalculate Tax');
3985
3986 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3987 p_encoded => fnd_api.g_false);
3988 x_msg_tbl(l_tot_msg_count).status := 'S';
3989 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3990 l_tot_msg_count := l_tot_msg_count + 1;
3991 l_msg_data := NULL;
3992 END IF;
3993 */
3994
3995 END IF; --sub_line_rec.id is not null chk
3996 End Loop; --sub_line_rec
3997
3998 --
3999 -- Bug 4717842 --
4000 -- Update Lines Level Tax Amount --
4001
4002 OPEN get_topline_tax_amt_csr(l_cle_id);
4003 FETCH get_topline_tax_amt_csr INTO l_line_tax_amt;
4004 CLOSE get_topline_tax_amt_csr;
4005
4006 OPEN cur_oks_lines(l_cle_id);
4007 FETCH cur_oks_lines INTO l_oks_lines_rec;
4008 close cur_oks_lines;
4009
4010 l_klnv_tbl_in(1).ID := l_oks_lines_rec.id;
4011 l_klnv_tbl_in(1).tax_amount := l_line_tax_amt.tax_amount;
4012 l_klnv_tbl_in(1).tax_inclusive_yn := l_oks_lines_rec.tax_inclusive_yn;
4013 l_klnv_tbl_in(1).OBJECT_VERSION_NUMBER := l_oks_lines_rec.OBJECT_VERSION_NUMBER;
4014 oks_contract_line_pub.update_line
4015 (
4016 p_api_version => l_api_version,
4017 p_init_msg_list => l_init_msg_list,
4018 x_return_status => l_return_status,
4019 x_msg_count => l_msg_count,
4020 x_msg_data => l_msg_data,
4021 p_klnv_tbl => l_klnv_tbl_in,
4022 x_klnv_tbl => l_klnv_tbl_out,
4023 p_validate_yn => 'N'
4024 );
4025 -- Bug 5227077 --
4026 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4027 FOR i in 1..fnd_msg_pub.count_msg
4028 Loop
4029 fnd_msg_pub.get
4030 (
4031 p_msg_index => i,
4032 p_encoded => 'F',
4033 p_data => l_msg_data,
4034 p_msg_index_out => l_msg_index
4035 );
4036 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4037 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4038 l_tot_msg_count := l_tot_msg_count + 1;
4039 l_msg_data := NULL;
4040 End Loop;
4041 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4042 End If;
4043
4044 ELSE -- this is to check for lse_id = 46
4045 OKS_TAX_UTIL_PVT.Get_Tax
4046 (
4047 p_api_version => 1.0,
4048 p_init_msg_list => OKC_API.G_TRUE,
4049 p_chr_id => l_chr_id,
4050 p_cle_id => l_cle_id,
4051 px_rail_rec => G_RAIL_REC,
4052 x_msg_count => x_msg_count,
4053 x_msg_data => x_msg_data,
4054 x_return_status => l_return_status
4055 );
4056 -- Bug 5227077 --
4057 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4058 FOR i in 1..fnd_msg_pub.count_msg
4059 Loop
4060 fnd_msg_pub.get
4061 (
4062 p_msg_index => i,
4063 p_encoded => 'F',
4064 p_data => l_msg_data,
4065 p_msg_index_out => l_msg_index
4066 );
4067 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4068 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4069 l_tot_msg_count := l_tot_msg_count + 1;
4070 l_msg_data := NULL;
4071 End Loop;
4072 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4073 End If;
4074 -- Bug 5227077 --
4075 l_UNIT_SELLING_PRICE := G_RAIL_REC.UNIT_SELLING_PRICE;
4076 l_QUANTITY := G_RAIL_REC.QUANTITY;
4077 l_sub_total := G_RAIL_REC.AMOUNT;
4078 l_AMOUNT_INCLUDES_TAX_FLAG := G_RAIL_REC.AMOUNT_INCLUDES_TAX_FLAG; --Rule_information5
4079 l_Tax_Code := G_RAIL_REC.TAX_CODE;
4080 l_TAX_RATE := G_RAIL_REC.TAX_RATE ;
4081 l_Tax_Value := G_RAIL_REC.TAX_VALUE; --Rule_information4
4082 l_Return_Status := l_return_status;
4083
4084 If l_AMOUNT_INCLUDES_TAX_FLAG = 'Y' THEN
4085 l_total_amt := 0;
4086 l_Tax_Value := 0;
4087 Else
4088 l_total := l_sub_total + l_Tax_Value;
4089 l_total_amt := l_total;
4090 End If;
4091
4092 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
4093 l_klnv_tbl_in(1).tax_amount := l_Tax_Value;
4094 l_klnv_tbl_in(1).tax_inclusive_yn := l_AMOUNT_INCLUDES_TAX_FLAG;
4095 l_klnv_tbl_in(1).OBJECT_VERSION_NUMBER := l_oks_lines_rec.OBJECT_VERSION_NUMBER;
4096
4097
4098 oks_contract_line_pub.update_line
4099 (
4100 p_api_version => l_api_version,
4101 p_init_msg_list => l_init_msg_list,
4102 x_return_status => l_return_status,
4103 x_msg_count => l_msg_count,
4104 x_msg_data => l_msg_data,
4105 p_klnv_tbl => l_klnv_tbl_in,
4106 x_klnv_tbl => l_klnv_tbl_out,
4107 p_validate_yn => 'N'
4108 );
4109
4110
4111 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4112 FOR i in 1..fnd_msg_pub.count_msg
4113 Loop
4114 fnd_msg_pub.get
4115 (
4116 p_msg_index => i,
4117 p_encoded => 'F',
4118 p_data => l_msg_data,
4119 p_msg_index_out => l_msg_index
4120 );
4121 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4122 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4123 l_tot_msg_count := l_tot_msg_count + 1;
4124 l_msg_data := NULL;
4125 End Loop;
4126 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4127 End If;
4128 /*
4129 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4130 fnd_msg_pub.initialize;
4131 OKC_API.SET_MESSAGE(
4132 p_app_name => G_APP_NAME_OKS,
4133 p_msg_name => 'OKS_RECALCULATE_TAX_SUCCESS',
4134 p_token1 => 'ATTRIBUTE',
4135 p_token1_value => 'Recalculate Tax');
4136
4137 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4138 p_encoded => fnd_api.g_false);
4139 x_msg_tbl(l_tot_msg_count).status := 'S';
4140 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4141 l_tot_msg_count := l_tot_msg_count + 1;
4142 l_msg_data := NULL;
4143
4144
4145 END IF;
4146 */
4147 END IF; --lse_id
4148
4149 -- Update header level tax amount
4150
4151 OPEN get_hdr_tax_amt_csr(l_chr_id);
4152 FETCH get_hdr_tax_amt_csr INTO l_hdr_tax_amt;
4153 CLOSE get_hdr_tax_amt_csr;
4154
4155 l_khrv_tbl_type_in(1).id := l_oks_headers_rec.id;
4156 l_khrv_tbl_type_in(1).tax_amount := l_hdr_tax_amt.tax_amount;
4157 l_khrv_tbl_type_in(1).object_version_number := l_oks_headers_rec.object_version_number;
4158
4159 oks_contract_hdr_pub.update_header(
4160 p_api_version => l_api_version,
4161 p_init_msg_list => l_init_msg_list,
4162 x_return_status => l_return_status,
4163 x_msg_count => l_msg_count,
4164 x_msg_data => l_msg_data,
4165 p_khrv_tbl => l_khrv_tbl_type_in,
4166 x_khrv_tbl => l_khrv_tbl_type_out,
4167 p_validate_yn => 'N');
4168 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4169 FOR i in 1..fnd_msg_pub.count_msg
4170 Loop
4171 fnd_msg_pub.get
4172 (
4173 p_msg_index => i,
4174 p_encoded => 'F',
4175 p_data => l_msg_data,
4176 p_msg_index_out => l_msg_index
4177 );
4178 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4179 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4180 l_tot_msg_count := l_tot_msg_count + 1;
4181 l_msg_data := NULL;
4182 End Loop;
4183 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4184 End If;
4185 -- Bug 5227077 --
4186
4187
4188 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4189 fnd_msg_pub.initialize;
4190 OKC_API.SET_MESSAGE(
4191 p_app_name => G_APP_NAME_OKS,
4192 p_msg_name => 'OKS_RECALCULATE_TAX_SUCCESS',
4193 p_token1 => 'ATTRIBUTE',
4194 p_token1_value => 'Recalculate Tax');
4195
4196 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4197 p_encoded => fnd_api.g_false);
4198 x_msg_tbl(l_tot_msg_count).status := 'S';
4199 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4200 l_tot_msg_count := l_tot_msg_count + 1;
4201 l_msg_data := NULL;
4202 END IF;
4203
4204 -- Update Header Level Tax Amount --
4205 -- Bug 4717842 --
4206 END IF;
4207
4208 --Fix for Bug#3635291;
4209 OKS_BILL_SCH.Cascade_Dates_SLL
4210 (
4211 p_top_line_id => l_cle_id,
4212 x_return_status => l_return_status,
4213 x_msg_count => l_msg_count,
4214 x_msg_data => l_msg_data);
4215 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4216
4217 FOR i in 1..fnd_msg_pub.count_msg
4218 Loop
4219 fnd_msg_pub.get
4220 (
4221 p_msg_index => i,
4222 p_encoded => 'F',
4223 p_data => l_msg_data,
4224 p_msg_index_out => l_msg_index
4225 );
4226
4227 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4228 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4229 l_tot_msg_count := l_tot_msg_count + 1;
4230 l_msg_data := NULL;
4231 End Loop;
4232 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4233
4234 End If;
4235
4236 --Fix for Bug#3635291;
4237
4238 exit when i=header_lines_tbl.last;
4239 i:=header_lines_tbl.next(i);
4240 END LOOP;
4241
4242 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4243 p_encoded => fnd_api.g_false);
4244
4245 /*
4246 If l_okc_lines_rec.lse_id <> '14' then
4247
4248 WHILE l_msg_data IS NOT NULL
4249 LOOP
4250 IF x_msg_tbl.count=0 THEN
4251 l_tot_msg_count:=1 ;
4252 ELSE
4253 l_tot_msg_count := x_msg_tbl.count + 1;
4254 END IF;
4255 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4256 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4257
4258 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_next,
4259 p_encoded => fnd_api.g_false
4260 );
4261 END LOOP;
4262 End If;
4263
4264 */
4265 End If;
4266 Exception
4267 When G_EXCEPTION_HALT_VALIDATION Then
4268 x_return_status := l_return_status;
4269 x_msg_data := l_msg_data;
4270 When Others Then
4271 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4272 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
4273 x_msg_data := l_msg_data;
4274 END Default_header_to_lines;
4275
4276 ----------------------------------------------------------------------------------------------
4277
4278 PROCEDURE Default_lines_to_sublines
4279 (lines_sublines_tbl IN lines_sublines_tbl_type
4280 ,X_return_status OUT NOCOPY Varchar2
4281 ,x_msg_tbl IN OUT NOCOPY attr_msg_tbl_type) IS
4282
4283 Cursor cur_line_dates(p_cle_id in NUMBER) is
4284 select start_date , end_date, lse_id
4285 from okc_k_lines_v
4286 where id = p_cle_id;
4287 line_dates_rec cur_line_dates%ROWTYPE;
4288
4289 Cursor cur_okc_lines(p_cle_id in NUMBER) is
4290 select
4291 id,
4292 line_number,
4293 line_renewal_type_code
4294 from okc_k_lines_b
4295 where id = p_cle_id;
4296 l_okc_lines_rec cur_okc_lines%ROWTYPE;
4297
4298 Cursor cur_oks_lines(p_cle_id in NUMBER) is
4299 select
4300 id,
4301 sfwt_flag,
4302 object_version_number,
4303 inv_print_flag,
4304 price_uom, -- 8/23/05 hkamdar R12 Partial Period
4305 invoice_text,
4306 standard_cov_yn -- Issue# 14 Bug 4566346
4307 from oks_k_lines_v
4308 where cle_id = p_cle_id;
4309 l_oks_lines_rec cur_oks_lines%ROWTYPE;
4310 l_oks_sub_lines_rec cur_oks_lines%ROWTYPE;
4311
4312
4313 Cursor cur_sub_line(p_cle_id in NUMBER) IS
4314 select id,lse_id
4315 from okc_k_lines_v
4316 where cle_id =p_cle_id
4317 and chr_id is null
4318 and lse_id in (7,8,9,10,11,13,18,25,35)
4319 and date_cancelled is null; --[llc]
4320
4321 l_sub_line_rec cur_sub_line%ROWTYPE;
4322
4323 Cursor cur_sub_line_lse(p_subline_id in NUMBER) IS
4324 select id,
4325 lse_id,
4326 line_number,
4327 date_terminated -- new
4328 from okc_k_lines_v
4329 where id =p_subline_id;
4330
4331 l_sub_line_lse_rec cur_sub_line_lse%ROWTYPE;
4332
4333
4334
4335 i NUMBER;
4336 l_chr_id NUMBER;
4337 --l_cle_id NUMBER;
4338 l_lse_id NUMBER;
4339 l_subline_id NUMBER;
4340 p_subline_id NUMBER;
4341 l_line_irt VARCHAR2(150) ;
4342 l_line_renewal VARCHAR2(150);
4343 l_line_inv_print VARCHAR2(150) ;
4344 l_line_dates VARCHAR2(150) ;
4345 l_line_cov_eff VARCHAR2(150) ;
4346 l_api_version Number := 1.0;
4347 l_init_msg_list Varchar2(1) := 'F';
4348 l_msg_count Number;
4349 l_msg_data Varchar2(1000);
4350 l_return_status Varchar2(1) := 'S';
4351 l_tot_msg_count NUMBER:=0;
4352 l_cle_id_old NUMBER:=0;
4353
4354 l_can_id NUMBER;
4355 p_klnv_rec oks_kln_pvt.klnv_rec_type;
4356 l_klnv_rec oks_kln_pvt.klnv_rec_type := p_klnv_rec;
4357 p_klnv_rec_in oks_kln_pvt.klnv_rec_type;
4358 l_klnv_rec_in oks_kln_pvt.klnv_rec_type := p_klnv_rec_in;
4359
4360 p_klnv_rec_out oks_kln_pvt.klnv_rec_type;
4361 l_klnv_rec_out oks_kln_pvt.klnv_rec_type := p_klnv_rec_out;
4362
4363 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
4364 l_clev_tbl_out okc_contract_pub.clev_tbl_type;
4365 l_cle_id NUMBER;
4366 p_cle_id NUMBER;
4367
4368 l_error_tbl OKC_API.ERROR_TBL_TYPE;
4369 p_klnv_tbl oks_kln_pvt.klnv_tbl_type;
4370 l_klnv_tbl oks_kln_pvt.klnv_tbl_type := p_klnv_tbl;
4371 x_klnv_tbl oks_kln_pvt.klnv_tbl_type;
4372 l_klnv_tbl_in oks_contract_line_pub.klnv_tbl_type;
4373 l_klnv_tbl_out oks_contract_line_pub.klnv_tbl_type;
4374 l_msg_index NUMBER;
4375 l_flag BOOLEAN;
4376 l_line_number VARCHAR2 (150);
4377 l_sub_line_number VARCHAR2 (150);
4378 l_sub_line_seq_number VARCHAR2 (310);
4379 x_msg_data VARCHAR2(2000);
4380
4381 -- GCHADHA --
4382 -- BUG 4093005 --
4383 l_line_id_tbl OKS_ATTR_DEFAULTS_PVT.lines_id_tbl_type;
4384 l_id NUMBER;
4385 l_count NUMBER;
4386 -- END GCHADHA --
4387
4388 l_line_price_uom VARCHAR2 (150); -- 8/23/05 hkamdar R12 Partial Period
4389
4390
4391 l_input_details OKS_QP_PKG.INPUT_DETAILS;
4392 l_output_details OKS_QP_PKG.PRICE_DETAILS;
4393 l_modif_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
4394 l_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
4395 l_status_tbl oks_qp_int_pvt.PRICING_STATUS_TBL;
4396
4397 l_bill_y_n VARCHAR2(1);
4398 l_disp_warning VARCHAR2(1) :='N';
4399
4400 PROCEDURE sublines_common_attributes
4401 (p_line_irt IN VARCHAR2
4402 ,p_line_renewal IN VARCHAR2
4403 ,p_line_inv_print IN VARCHAR2
4404 ,p_line_dates IN VARCHAR2
4405 ,p_subline_id IN NUMBER
4406 ,p_price_uom IN VARCHAR2 -- 8/23/05 hkamdar R12 Partial Period
4407 ,x_return_status OUT NOCOPY VARCHAR2
4408 ) IS
4409
4410 BEGIN
4411 l_return_status := 'S';
4412 OPEN cur_oks_lines(p_subline_id);
4413 FETCH cur_oks_lines INTO l_oks_sub_lines_rec;
4414 CLOSE cur_oks_lines;
4415 --new
4416 OPEN cur_sub_line_lse(p_subline_id);
4417 FETCH cur_sub_line_lse INTO l_sub_line_lse_rec;
4418 CLOSE cur_sub_line_lse;
4419 --new
4420
4421
4422
4423 IF l_line_irt is not null then
4424
4425 l_klnv_tbl_in(1).ID := l_oks_sub_lines_rec.id;--l_subline_id;
4426 l_klnv_tbl_in(1).invoice_text := l_oks_lines_rec.invoice_text;
4427 l_klnv_tbl_in(1).object_version_number := l_oks_sub_lines_rec.object_version_number;
4428
4429 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4430 OKC_API.SET_MESSAGE(
4431 p_app_name => G_APP_NAME_OKS,
4432 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4433 p_token1 => 'ATTRIBUTE',
4434 p_token1_value => 'Invoicing Text');
4435
4436 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4437 p_encoded => fnd_api.g_false);
4438 x_msg_tbl(l_tot_msg_count).status := 'S';
4439 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4440 l_tot_msg_count := l_tot_msg_count + 1;
4441 l_msg_data := NULL;
4442 fnd_msg_pub.initialize;
4443
4444 END IF;
4445
4446 END IF;
4447 -------------------------------------------------------------------
4448
4449 IF l_line_renewal IS NOT NULL THEN
4450
4451 l_clev_tbl_in(1).id := l_subline_id;
4452 l_clev_tbl_in(1).line_renewal_type_code := l_okc_lines_rec.line_renewal_type_code;
4453
4454 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4455 OKC_API.SET_MESSAGE(
4456 p_app_name => G_APP_NAME_OKS,
4457 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4458 p_token1 => 'ATTRIBUTE',
4459 p_token1_value => 'Renewal');
4460
4461 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4462 p_encoded => fnd_api.g_false);
4463 x_msg_tbl(l_tot_msg_count).status := 'S';
4464 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4465 l_tot_msg_count := l_tot_msg_count + 1;
4466 l_msg_data := NULL;
4467 fnd_msg_pub.initialize;
4468
4469
4470
4471 END IF;
4472 END IF;
4473
4474 If l_line_dates is not null then
4475
4476 l_sub_line_number := l_sub_line_lse_rec.line_number;
4477 l_line_number := l_okc_lines_rec.line_number;
4478 l_sub_line_seq_number := (l_line_number || '.' || l_sub_line_number);
4479
4480 If oks_extwar_util_pvt.check_already_billed(
4481 p_chr_id => null,
4482 p_cle_id => l_cle_id,
4483 p_lse_id => line_dates_rec.lse_id,
4484 p_end_date => null) Then
4485 fnd_msg_pub.initialize;
4486 validate_date
4487
4488 (p_api_version => l_api_version
4489 ,p_init_msg_list => l_init_msg_list
4490 ,p_hdr_id => NULL
4491 ,p_top_line_id => l_cle_id
4492 ,p_sub_line_id => l_subline_id
4493 ,x_return_status => l_return_status
4494 ,x_msg_count => l_msg_count
4495 ,x_msg_data => l_msg_data
4496 ,x_flag => l_flag);
4497
4498
4499 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4500 FOR i in 1..fnd_msg_pub.count_msg
4501 Loop
4502 fnd_msg_pub.get
4503 (
4504 p_msg_index => i,
4505 p_encoded => 'F',
4506 p_data => l_msg_data,
4507 p_msg_index_out => l_msg_index
4508 );
4509 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4510 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4511 l_tot_msg_count := l_tot_msg_count + 1;
4512 l_msg_data := NULL;
4513 End Loop;
4514 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4515 Else
4516 If l_flag <> TRUE then
4517 fnd_msg_pub.initialize;
4518 OKC_API.SET_MESSAGE(
4519 p_app_name => 'OKS', --G_APP_NAME_OKS,
4520 p_msg_name => 'OKS_BA_UPDATE_NOT_ALLOWED',
4521 p_token1 => 'Sub Line No ',
4522 p_token1_value => l_sub_line_seq_number);
4523
4524
4525 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4526 p_encoded => fnd_api.g_false);
4527 x_msg_tbl(l_tot_msg_count).status := 'E';
4528 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4529 l_tot_msg_count := l_tot_msg_count + 1;
4530 l_msg_data := NULL;
4531 fnd_msg_pub.initialize;
4532 Raise G_EXCEPTION_HALT_VALIDATION;
4533
4534 End If;
4535 End If;
4536 End If;
4537
4538 --new
4539 OPEN cur_line_dates(l_cle_id );
4540 FETCH cur_line_dates INTO line_dates_rec;
4541 CLOSE cur_line_dates;
4542
4543
4544 l_clev_tbl_in(1).id := l_subline_id;
4545 l_clev_tbl_in(1).chr_id := NULL;
4546 l_clev_tbl_in(1).cle_id := l_cle_id;
4547 l_clev_tbl_in(1).start_date := line_dates_rec.start_date;
4548 l_clev_tbl_in(1).end_date := line_dates_rec.end_date;
4549 l_clev_tbl_in(1).dnz_chr_id := l_chr_id;
4550 If line_dates_rec.lse_id = '14' Then
4551 If l_clev_tbl_in(1).start_date > SYSDATE THen
4552 l_clev_tbl_in(1).sts_code := 'SIGNED';
4553 Elsif l_clev_tbl_in(1).start_date <= SYSDATE And l_clev_tbl_in(1).end_date >= SYSDATE THEN
4554 l_clev_tbl_in(1).sts_code := 'ACTIVE';
4555 ELSIF l_clev_tbl_in(1).end_date < SYSDATE Then
4556 l_clev_tbl_in(1).sts_code :='EXPIRED';
4557 End if;
4558 End if;
4559
4560 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4561 -- Issue a warning message if dates are cascaded from lines to sublines for a fully billed contract.
4562 IF(l_disp_warning ='Y') THEN
4563 OKC_API.SET_MESSAGE(
4564 p_app_name => G_APP_NAME_OKS,
4565 p_msg_name => 'OKS_LINES_CASCADE_DATES_WARN');
4566 x_msg_tbl(l_tot_msg_count).status := 'W';
4567
4568 ELSE
4569
4570 OKC_API.SET_MESSAGE(
4571 p_app_name => G_APP_NAME_OKS,
4572 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4573 p_token1 => 'ATTRIBUTE',
4574 p_token1_value => 'Date');
4575 x_msg_tbl(l_tot_msg_count).status := 'S';
4576
4577 END IF;
4578 END IF;
4579 ----new
4580 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4581 p_encoded => fnd_api.g_false);
4582 -- x_msg_tbl(l_tot_msg_count).status := 'S';
4583 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4584 l_tot_msg_count := l_tot_msg_count + 1;
4585 l_msg_data := NULL;
4586 fnd_msg_pub.initialize;
4587 ----new
4588 -- Added for Bug#15936043
4589 l_klnv_tbl_in(1).ID := l_oks_sub_lines_rec.id;
4590 l_klnv_tbl_in(1).invoice_text :=
4591 substr(SubStr(l_oks_sub_lines_rec.invoice_text,1,InStr(l_oks_sub_lines_rec.invoice_text,':',1,3)-1)
4592 ||':'|| line_dates_rec.start_date
4593 ||':'|| line_dates_rec.end_date,1,450);
4594
4595 l_line_irt := 'X';
4596 l_klnv_tbl_in(1).object_version_number := l_oks_sub_lines_rec.object_version_number;
4597
4598 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4599 OKC_API.SET_MESSAGE(
4600 p_app_name => G_APP_NAME_OKS,
4601 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4602 p_token1 => 'ATTRIBUTE',
4603 p_token1_value => 'Subline Invoicing Text');
4604
4605 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4606 p_encoded => fnd_api.g_false);
4607 x_msg_tbl(l_tot_msg_count).status := 'S';
4608 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4609 l_tot_msg_count := l_tot_msg_count + 1;
4610 l_msg_data := NULL;
4611 fnd_msg_pub.initialize;
4612
4613 END IF;
4614 -- End of Code added for Bug#15936043
4615
4616 END IF;
4617 -------------------------------------------------------------------
4618 IF l_line_inv_print IS NOT NULL THEN
4619
4620 l_klnv_tbl_in(1).ID := l_oks_sub_lines_rec.id;--l_subline_id;
4621 l_klnv_tbl_in(1).inv_print_flag := l_oks_lines_rec.inv_print_flag;
4622 l_klnv_tbl_in(1).object_version_number := l_oks_sub_lines_rec.object_version_number;
4623
4624 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4625 OKC_API.SET_MESSAGE(
4626 p_app_name => G_APP_NAME_OKS,
4627 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4628 p_token1 => 'ATTRIBUTE',
4629 p_token1_value => 'Invoicing Print Flag');
4630 END IF;
4631
4632 ----new
4633 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4634 p_encoded => fnd_api.g_false);
4635 x_msg_tbl(l_tot_msg_count).status := 'S';
4636 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4637 l_tot_msg_count := l_tot_msg_count + 1;
4638 l_msg_data := NULL;
4639 fnd_msg_pub.initialize;
4640 ----new
4641
4642
4643
4644 END IF;
4645
4646 -------------------------------------------------------------------
4647 -- 8/23/2005 hkamdar R12 Partial Period
4648 IF l_line_price_uom is not null THEN
4649 If l_sub_line_lse_rec.lse_id IN (7,9,25)
4650 AND OKS_AUTH_UTIL_PVT.Is_Line_Eligible(p_api_version => 1.0,
4651 p_init_msg_list => 'T',
4652 p_contract_hdr_id => l_chr_id,
4653 p_contract_line_id => l_subline_id,
4654 p_price_list_id => '',
4655 p_intent => 'S',
4656 x_msg_count => l_msg_count,
4657 x_msg_data => l_msg_data)
4658 THEN
4659 -- Covered Item, Product, Convered product in extended warraties
4660 l_klnv_tbl_in(1).ID := l_oks_sub_lines_rec.id;--l_subline_id;
4661 l_klnv_tbl_in(1).price_uom := l_oks_lines_rec.price_uom;
4662 l_klnv_tbl_in(1).object_version_number := l_oks_sub_lines_rec.object_version_number;
4663
4664 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4665 OKC_API.SET_MESSAGE(
4666 p_app_name => G_APP_NAME_OKS,
4667 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4668 p_token1 => 'ATTRIBUTE',
4669 p_token1_value => 'Price UOM');
4670
4671 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4672 p_encoded => fnd_api.g_false);
4673 x_msg_tbl(l_tot_msg_count).status := 'S';
4674 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4675 l_tot_msg_count := l_tot_msg_count + 1;
4676 l_msg_data := NULL;
4677 fnd_msg_pub.initialize;
4678 END IF;
4679 Else
4680 /* OKC_API.SET_MESSAGE(
4681 p_app_name => G_APP_NAME_OKS,
4682 p_msg_name => 'OKS_LINE_CASCADE_ERROR',
4683 p_token1 => 'ATTRIBUTE',
4684 p_token1_value => 'Price UOM');
4685
4686 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4687 p_encoded => fnd_api.g_false);
4688 ----errorout_gsi('Subline_common_attribute UOM msg '||l_msg_data);
4689 x_msg_tbl(l_tot_msg_count).status := 'E';
4690 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4691 l_tot_msg_count := l_tot_msg_count + 1;
4692 l_msg_data := NULL;
4693 fnd_msg_pub.initialize; */
4694 null;
4695 End If;
4696 END IF;
4697 --new
4698 Exception
4699 When G_EXCEPTION_HALT_VALIDATION Then
4700 x_return_status := 'E';
4701 x_msg_data := l_msg_data;
4702 When Others Then
4703 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4704 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
4705 x_msg_data := l_msg_data;
4706
4707 --new
4708
4709 END sublines_common_attributes;
4710
4711 Begin
4712 fnd_msg_pub.initialize;
4713
4714 IF NOT lines_sublines_tbl.COUNT=0 THEN
4715 i:=lines_sublines_tbl.FIRST;
4716 -- GCHADHA --
4717 -- BUG 4093005 --
4718 l_id := 0;
4719 l_count := 0;
4720 -- END GCHADHA --
4721 --FOR i IN lines_sublines_tbl.FIRST .. lines_sublines_tbl.LAST
4722 LOOP
4723 l_chr_id := lines_sublines_tbl(i).chr_id;
4724 l_cle_id := lines_sublines_tbl(i).cle_id;
4725 l_subline_id := lines_sublines_tbl(i).subline_id;
4726 l_line_irt := lines_sublines_tbl(i).line_irt;
4727 l_line_renewal := lines_sublines_tbl(i).line_renewal;
4728 l_line_inv_print := lines_sublines_tbl(i).line_inv_print;
4729 l_line_dates := lines_sublines_tbl(i).line_dates;
4730 l_line_cov_eff := lines_sublines_tbl(i).line_cov_eff;
4731 l_line_price_uom := lines_sublines_tbl(i).price_uom; -- 8/23/05 hkamdar R12 Partial Period
4732 -- errorout_vg('Line_Price Uom ' || lines_sublines_tbl(i).price_uom);
4733
4734 -- GCHADHA --
4735 -- BUG 4093005 --
4736 IF l_id <> l_cle_id THEN
4737 l_line_id_tbl(l_count).id := l_cle_id;
4738 l_id := l_cle_id;
4739 l_count :=l_count + 1;
4740 END IF;
4741 -- END GCHADHA --
4742
4743 l_klnv_tbl_in.DELETE;
4744 l_clev_tbl_in.DELETE;
4745
4746 -- check if the contract has been imported and fully billed at source
4747 IF(l_chr_id IS NOT NULL) THEN
4748 select billed_at_source INTO l_bill_y_n FROM okc_k_headers_all_b where id = l_chr_id ;
4749 END IF;
4750
4751 IF ((l_bill_y_n IS NOT NULL) AND (l_bill_y_n = 'Y')) THEN
4752 l_disp_warning :='Y';
4753 ELSE
4754 l_disp_warning :='N';
4755 END IF;
4756
4757
4758
4759 p_cle_id := l_cle_id;
4760 OPEN cur_oks_lines(p_cle_id);
4761 FETCH cur_oks_lines INTO l_oks_lines_rec;
4762 CLOSE cur_oks_lines;
4763
4764 OPEN cur_okc_lines(p_cle_id);
4765 FETCH cur_okc_lines INTO l_okc_lines_rec;
4766 CLOSE cur_okc_lines;
4767 --new
4768 OPEN cur_sub_line_lse(l_subline_id);
4769 FETCH cur_sub_line_lse INTO l_sub_line_lse_rec;
4770 CLOSE cur_sub_line_lse;
4771 --new
4772
4773 If l_line_dates is not null then
4774
4775 IF l_cle_id_old <> l_cle_id AND nvl(l_oks_lines_rec.standard_cov_yn, 'N') <> 'Y' Then
4776 ----errorout_gsi('Line date is not null, calling update_coverage_effectivity');
4777 OPEN cur_line_dates(l_cle_id );
4778 FETCH cur_line_dates INTO line_dates_rec;
4779 CLOSE cur_line_dates;
4780 OKS_COVERAGES_PVT.Update_COVERAGE_Effectivity
4781 (
4782 p_api_version => l_api_version,
4783 p_init_msg_list => l_init_msg_list,
4784 x_return_status => l_return_status,
4785 x_msg_count => l_msg_count,
4786 x_msg_data => l_msg_data,
4787 p_service_Line_Id => l_cle_id,
4788 p_New_Start_Date => line_dates_rec.start_date,
4789 p_New_End_Date => line_dates_rec.end_date );
4790
4791 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4792 FOR i in 1..fnd_msg_pub.count_msg
4793 Loop
4794 fnd_msg_pub.get
4795 (
4796 p_msg_index => i,
4797 p_encoded => 'F',
4798 p_data => l_msg_data,
4799 p_msg_index_out => l_msg_index
4800 );
4801 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4802 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4803 l_tot_msg_count := l_tot_msg_count + 1;
4804 l_msg_data := NULL;
4805 End Loop;
4806 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4807 End If;
4808
4809 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4810
4811 -- Issue a warning message if dates are cascaded from lines to sublines for a fully billed contract.
4812 IF(l_disp_warning ='Y') THEN
4813
4814 OKC_API.SET_MESSAGE(
4815 p_app_name => G_APP_NAME_OKS,
4816 p_msg_name => 'OKS_LINES_CASCADE_DATES_WARN');
4817
4818 ELSE
4819
4820 OKC_API.SET_MESSAGE(
4821 p_app_name => G_APP_NAME_OKS,
4822 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4823 p_token1 => 'ATTRIBUTE',
4824 p_token1_value => 'Date');
4825 END IF;
4826 END IF;
4827 l_cle_id_old:=l_cle_id ;
4828 END IF;
4829
4830 END IF;
4831
4832 IF l_subline_id is not null then
4833
4834 -- errorout_vg('Calling subline_common_attributes');
4835
4836 sublines_common_attributes
4837 (p_line_irt => l_line_irt
4838 ,p_line_renewal => l_line_renewal
4839 ,p_line_inv_print => l_line_inv_print
4840 ,p_line_dates => l_line_dates
4841 ,p_subline_id => l_subline_id
4842 ,p_price_uom => l_line_price_uom -- 8/23/05 hkamdar R12 Partial Period
4843 ,x_return_status => l_return_status
4844 );
4845 -- Bug 5227077 --
4846 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4847
4848 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4849 end if;
4850 -- Bug 5227077 --
4851
4852
4853 --errorout_gsi('After sublines_common_attributes status '||l_return_status);
4854 --new
4855 IF l_line_irt IS NOT NULL or
4856 l_line_inv_print IS NOT NULL or
4857 l_line_price_uom IS NOT NULL THEN -- 8/23/05 hkamdar R12 Partial Period Added new condition
4858
4859 --Update oks lines
4860 -- errorout_vg('Calling oks_contract_line_pub.update_line');
4861 oks_contract_line_pub.update_line
4862 (
4863 p_api_version => l_api_version,
4864 p_init_msg_list => l_init_msg_list,
4865 x_return_status => l_return_status,
4866 x_msg_count => l_msg_count,
4867 x_msg_data => l_msg_data,
4868 p_klnv_tbl => l_klnv_tbl_in,
4869 x_klnv_tbl => l_klnv_tbl_out,
4870 p_validate_yn => 'N'
4871 );
4872
4873 -- errorout_vg('After oks_contract_line_pub.update_line status '||l_return_status);
4874 --errorout_gsi('After oks_contract_line_pub.update_line Message '||l_msg_data);
4875
4876 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4877
4878 FOR i in 1..fnd_msg_pub.count_msg
4879 Loop
4880 fnd_msg_pub.get
4881 (
4882 p_msg_index => i,
4883 p_encoded => 'F',
4884 p_data => l_msg_data,
4885 p_msg_index_out => l_msg_index
4886 );
4887
4888 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4889 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4890 l_tot_msg_count := l_tot_msg_count + 1;
4891 l_msg_data := NULL;
4892 End Loop;
4893 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4894 End If;
4895
4896 END IF;
4897
4898 -------------------------------------------------------------------
4899 IF l_line_renewal IS NOT NULL OR
4900 l_line_dates IS NOT NULL OR
4901 l_line_cov_eff IS NOT NULL THEN
4902 --errorout_gsi('Calling local update_line');
4903 update_line(
4904 p_clev_tbl => l_clev_tbl_in,
4905 x_clev_tbl => l_clev_tbl_out,
4906 x_return_status => l_return_status,
4907 x_msg_count => l_msg_count,
4908 x_msg_data => l_msg_data
4909 );
4910
4911 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4912 FOR i in 1..fnd_msg_pub.count_msg
4913 Loop
4914 fnd_msg_pub.get
4915 (
4916 p_msg_index => i,
4917 p_encoded => 'F',
4918 p_data => l_msg_data,
4919 p_msg_index_out => l_msg_index
4920 );
4921
4922 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4923 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4924 l_tot_msg_count := l_tot_msg_count + 1;
4925 l_msg_data := NULL;
4926 End Loop;
4927 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4928
4929 End If;
4930
4931 END IF;
4932 --new
4933
4934 ELSIF l_subline_id is null then
4935 -- errorout_vg('l_subline_id is null');
4936
4937 OPEN cur_sub_line(p_cle_id);
4938 LOOP
4939 FETCH cur_sub_line INTO l_sub_line_rec;
4940 l_subline_id := l_sub_line_rec.id;
4941
4942 EXIT WHEN cur_sub_line%NOTFOUND;
4943
4944 l_klnv_tbl_in.DELETE;
4945 l_clev_tbl_in.DELETE;
4946 -- GCHADHA --
4947 -- NCR WARRANTY CASCADE DATE MINOR ENHANCEMENT --
4948 -- 5/19/2005 --
4949
4950 IF l_sub_line_rec.lse_id = 18
4951 THEN
4952
4953 --new
4954 OPEN cur_sub_line_lse(l_subline_id);
4955 FETCH cur_sub_line_lse INTO l_sub_line_lse_rec;
4956 CLOSE cur_sub_line_lse;
4957 --new
4958 IF l_sub_line_lse_rec.date_terminated IS NULL
4959 THEN
4960 -- errorout_vg('Calling subline_common_attributes Second');
4961 sublines_common_attributes
4962 (p_line_irt => l_line_irt
4963 ,p_line_renewal => l_line_renewal
4964 ,p_line_inv_print => l_line_inv_print
4965 ,p_line_dates => l_line_dates
4966 ,p_subline_id => l_subline_id
4967 ,p_price_uom => l_line_price_uom -- 8/23/05 hkamdar R12 Partial Period
4968 ,x_return_status => l_return_status
4969 );
4970 --- errorout_vg('After sublines_common_attributes second status '||l_return_status);
4971 FOR i in 1..fnd_msg_pub.count_msg
4972 Loop
4973 fnd_msg_pub.get
4974 (
4975 p_msg_index => i,
4976 p_encoded => 'F',
4977 p_data => l_msg_data,
4978 p_msg_index_out => l_msg_index
4979 );
4980
4981 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4982 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4983 l_tot_msg_count := l_tot_msg_count + 1;
4984 l_msg_data := NULL;
4985 End Loop;
4986 IF ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4987 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4988 End If;
4989 END IF ;
4990 ELSE
4991 -- errorout_vg('Calling subline_common_attributes Third');
4992 sublines_common_attributes
4993 (p_line_irt => l_line_irt
4994 ,p_line_renewal => l_line_renewal
4995 ,p_line_inv_print => l_line_inv_print
4996 ,p_line_dates => l_line_dates
4997 ,p_subline_id => l_subline_id
4998 ,p_price_uom => l_line_price_uom -- 8/23/05 hkamdar R12 Partial Period
4999 ,x_return_status => l_return_status
5000 );
5001 -- errorout_vg('After sublines_common_attributes Third status '||l_return_status);
5002 FOR i in 1..fnd_msg_pub.count_msg
5003 Loop
5004 fnd_msg_pub.get
5005 (
5006 p_msg_index => i,
5007 p_encoded => 'F',
5008 p_data => l_msg_data,
5009 p_msg_index_out => l_msg_index
5010 );
5011
5012 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5013 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5014 l_tot_msg_count := l_tot_msg_count + 1;
5015 l_msg_data := NULL;
5016 End Loop;
5017 IF ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
5018 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5019 End If;
5020 END IF;
5021
5022 -- END GCAHDHA --
5023 IF l_line_irt IS NOT NULL or
5024 l_line_inv_print IS NOT NULL OR
5025 l_line_price_uom IS NOT NULL THEN -- 8/23/05 hkamdar R12 Partial Period Added new condition
5026
5027
5028 --Update oks lines
5029 --errorout_gsi('Calling oks_contract_line_pub.update_line Second');
5030 oks_contract_line_pub.update_line
5031 (
5032 p_api_version => l_api_version,
5033 p_init_msg_list => l_init_msg_list,
5034 x_return_status => l_return_status,
5035 x_msg_count => l_msg_count,
5036 x_msg_data => l_msg_data,
5037 p_klnv_tbl => l_klnv_tbl_in,
5038 x_klnv_tbl => l_klnv_tbl_out,
5039 p_validate_yn => 'N'
5040 );
5041
5042 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
5043 FOR i in 1..fnd_msg_pub.count_msg
5044 Loop
5045 fnd_msg_pub.get
5046 (
5047 p_msg_index => i,
5048 p_encoded => 'F',
5049 p_data => l_msg_data,
5050 p_msg_index_out => l_msg_index
5051 );
5052
5053 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5054 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5055 l_tot_msg_count := l_tot_msg_count + 1;
5056 l_msg_data := NULL;
5057 End Loop;
5058 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5059
5060 End If;
5061
5062 END IF;
5063
5064 IF l_line_renewal IS NOT NULL OR
5065 l_line_dates IS NOT NULL OR
5066 l_line_cov_eff IS NOT NULL THEN
5067
5068 --errorout_gsi('Calling local update_line Second');
5069
5070 update_line(
5071 p_clev_tbl => l_clev_tbl_in,
5072 x_clev_tbl => l_clev_tbl_out,
5073 x_return_status => l_return_status,
5074 x_msg_count => l_msg_count,
5075 x_msg_data => l_msg_data
5076 );
5077 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
5078 FOR i in 1..fnd_msg_pub.count_msg
5079 Loop
5080 fnd_msg_pub.get
5081 (
5082 p_msg_index => i,
5083 p_encoded => 'F',
5084 p_data => l_msg_data,
5085 p_msg_index_out => l_msg_index
5086 );
5087
5088 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5089 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5090 l_tot_msg_count := l_tot_msg_count + 1;
5091 l_msg_data := NULL;
5092 End Loop;
5093 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5094 End If;
5095
5096 END IF;
5097 -- errorout_vg('I -- Before Calling oks_qp_int_pvt.compute_Price lse_id ' || l_sub_line_rec.lse_id );
5098 IF l_line_price_uom is NOT NULL and l_sub_line_rec.lse_id in (7,9,25)
5099 AND OKS_AUTH_UTIL_PVT.Is_Line_Eligible(p_api_version => 1.0,
5100 p_init_msg_list => 'T',
5101 p_contract_hdr_id => l_chr_id,
5102 p_contract_line_id => l_subline_id,
5103 p_price_list_id => '',
5104 p_intent => 'S',
5105 x_msg_count => l_msg_count,
5106 x_msg_data => l_msg_data)
5107 THEN
5108 -- errorout_vg('I -- Calling oks_qp_int_pvt.compute_Price');
5109 l_input_details.line_id := l_cle_id;
5110 l_input_details.subline_id := l_subline_id;
5111 l_input_details.intent := 'SP';
5112 oks_qp_int_pvt.compute_Price
5113 (
5114 p_api_version => 1.0,
5115 p_init_msg_list => 'T',
5116 p_detail_rec => l_input_details,
5117 x_price_details => l_output_details,
5118 x_modifier_details => l_modif_details,
5119 x_price_break_details => l_pb_details,
5120 x_return_status => l_return_status,
5121 x_msg_count => l_msg_count,
5122 x_msg_data => l_msg_data
5123 );
5124
5125 l_status_tbl := oks_qp_int_pvt.get_Pricing_Messages;
5126 IF l_status_tbl.Count > 0 Then
5127
5128 l_count:= l_status_tbl.FIRST;
5129 For i in l_status_tbl.FIRST..l_status_tbl.LAST
5130 Loop
5131 x_msg_tbl(l_count).status := l_status_tbl(i).Status_Code;
5132 x_msg_tbl(l_count).description := l_status_tbl(i).Status_Text;
5133 EXIT WHEN l_count = l_status_tbl.LAST;
5134 l_count := l_status_tbl.NEXT(l_count);
5135 End Loop;
5136 END IF;
5137 -- Bug 5381082 --
5138 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
5139 FOR i in 1..fnd_msg_pub.count_msg
5140 Loop
5141 fnd_msg_pub.get
5142 (
5143 p_msg_index => i,
5144 p_encoded => 'F',
5145 p_data => l_msg_data,
5146 p_msg_index_out => l_msg_index
5147 );
5148 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5149 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5150 l_tot_msg_count := l_tot_msg_count + 1;
5151 l_msg_data := NULL;
5152 End Loop;
5153 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5154 End If;
5155 -- Bug 5381082 --
5156 End if;
5157
5158 END LOOP;
5159 CLOSE cur_sub_line;
5160 l_subline_id := l_sub_line_rec.id;
5161
5162 End If;
5163 -- Bug 5205136 --
5164 -- errorout_vg('Before Calling oks_qp_int_pvt.compute_Price lse_id ' || l_sub_line_rec.lse_id );
5165 IF l_line_price_uom is NOT NULL and (l_sub_line_rec.lse_id in (7,9,25)
5166 OR l_sub_line_lse_rec.lse_id in (7,9,25))
5167 AND OKS_AUTH_UTIL_PVT.Is_Line_Eligible(p_api_version => 1.0,
5168 p_init_msg_list => 'T',
5169 p_contract_hdr_id => l_chr_id,
5170 p_contract_line_id => l_subline_id,
5171 p_price_list_id => '',
5172 p_intent => 'S',
5173 x_msg_count => l_msg_count,
5174 x_msg_data => l_msg_data)
5175 THEN
5176 -- Bug 5205136 --
5177 -- errorout_vg('Calling oks_qp_int_pvt.compute_Price');
5178 l_input_details.line_id := l_cle_id;
5179 l_input_details.subline_id := l_subline_id;
5180 l_input_details.intent := 'SP';
5181 oks_qp_int_pvt.compute_Price
5182 (
5183 p_api_version => 1.0,
5184 p_init_msg_list => 'T',
5185 p_detail_rec => l_input_details,
5186 x_price_details => l_output_details,
5187 x_modifier_details => l_modif_details,
5188 x_price_break_details => l_pb_details,
5189 x_return_status => l_return_status,
5190 x_msg_count => l_msg_count,
5191 x_msg_data => l_msg_data
5192 );
5193
5194 l_status_tbl := oks_qp_int_pvt.get_Pricing_Messages;
5195
5196 IF l_status_tbl.Count > 0 Then
5197
5198 l_count:= l_status_tbl.FIRST;
5199 For i in l_status_tbl.FIRST..l_status_tbl.LAST
5200 Loop
5201 x_msg_tbl(l_count).status := l_status_tbl(i).Status_Code;
5202 x_msg_tbl(l_count).description := l_status_tbl(i).Status_Text;
5203 EXIT WHEN l_count = l_status_tbl.LAST;
5204 l_count := l_status_tbl.NEXT(l_count);
5205 End Loop;
5206 END IF;
5207 -- Bug 5381082 --
5208 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
5209 FOR i in 1..fnd_msg_pub.count_msg
5210 Loop
5211 fnd_msg_pub.get
5212 (
5213 p_msg_index => i,
5214 p_encoded => 'F',
5215 p_data => l_msg_data,
5216 p_msg_index_out => l_msg_index
5217 );
5218 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5219 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5220 l_tot_msg_count := l_tot_msg_count + 1;
5221 l_msg_data := NULL;
5222 End Loop;
5223 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5224 End If;
5225 -- Bug 5381082 --
5226 End if;
5227
5228 exit when i=lines_sublines_tbl.last;
5229 i:=lines_sublines_tbl.next(i);
5230
5231 END LOOP;
5232 -- GCHADHA --
5233 -- BUG 4093005 --
5234 -- 05-JAN-2005 --
5235 IF l_line_id_tbl.COUNT > 0 THEN
5236 FOR j in l_line_id_tbl.FIRST..l_line_id_tbl.LAST LOOP
5237 OKS_BILL_SCH.Cascade_Dates_SLL
5238 (
5239 p_top_line_id => l_line_id_tbl(j).id,
5240 x_return_status => l_return_status,
5241 x_msg_count => l_msg_count,
5242 x_msg_data => l_msg_data);
5243 --errorout_gsi('After OKS_BILL_SCH.Cascade_Dates_SLL status '||l_return_status);
5244 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
5245 FOR i in 1..fnd_msg_pub.count_msg
5246 Loop
5247 fnd_msg_pub.get
5248 (
5249 p_msg_index => i,
5250 p_encoded => 'F',
5251 p_data => l_msg_data,
5252 p_msg_index_out => l_msg_index
5253 );
5254
5255 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5256 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5257 l_tot_msg_count := l_tot_msg_count + 1;
5258 l_msg_data := NULL;
5259 End Loop;
5260 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5261
5262 End If;
5263 END LOOP;
5264 END IF;
5265
5266 -- END GCHADHA --
5267
5268 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
5269 p_encoded => fnd_api.g_false);
5270 WHILE l_msg_data IS NOT NULL
5271 LOOP
5272 IF x_msg_tbl.count=0 THEN
5273 l_tot_msg_count:=1 ;
5274 ELSE
5275 l_tot_msg_count := x_msg_tbl.count + 1;
5276 END IF;
5277
5278 -- store the program results
5279
5280 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5281 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5282
5283 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_next,
5284 p_encoded => fnd_api.g_false);
5285 END LOOP;
5286
5287 END IF;
5288 --new
5289 Exception
5290 When G_EXCEPTION_HALT_VALIDATION Then
5291 x_return_status := 'E';
5292 x_msg_data := l_msg_data;
5293 When Others Then
5294 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5295 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
5296 x_msg_data := l_msg_data;
5297
5298 --new
5299
5300 END Default_lines_to_sublines;
5301
5302
5303 PROCEDURE Rollback_work IS
5304 Begin
5305 Rollback;
5306 End;
5307 -- Bank Account Consolidation --
5308
5309 -- Added New Procedure Called to delete/ Create
5310 -- Credit Card details
5311 Procedure Delete_credit_Card
5312 (p_trnx_ext_id IN NUMBER,
5313 p_line_id IN NUMBER,
5314 p_party_id IN NUMBER,
5315 p_cust_account_id IN NUMBER ,
5316 x_return_status OUT NOCOPY VARCHAR2 ,
5317 x_msg_data OUT NOCOPY VARCHAR2) IS
5318
5319 l_msg_count Number;
5320 l_msg_data Varchar2(1000);
5321 l_return_status Varchar2(1) := 'S';
5322 l_payer IBY_FNDCPT_Common_Pub.PayerContext_rec_type;
5323 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
5324
5325 l_api_name CONSTANT VARCHAR2(30) := 'Delete_credit_Card';
5326
5327 Begin
5328
5329 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5330 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
5331 END IF;
5332
5333 l_payer.Payment_Function :='CUSTOMER_PAYMENT';
5334 l_payer.Party_Id := p_party_id;
5335 l_payer.cust_account_id := p_cust_account_id;
5336
5337
5338 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5339 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension.');
5340 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.payment_function: ' || l_payer.payment_function);
5341 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.party_id: ' || l_payer.party_id);
5342 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.cust_account_id: ' || l_payer.cust_account_id);
5343 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'p_trnx_ext_id: ' || p_trnx_ext_id);
5344 END IF;
5345
5346
5347 IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension
5348 (
5349 p_api_version => 1.0,
5350 p_init_msg_list => FND_API.G_FALSE,
5351 p_commit => FND_API.G_FALSE,
5352 x_return_status => l_return_status,
5353 x_msg_count => l_msg_count,
5354 x_msg_data => l_msg_data,
5355 p_payer => l_payer,
5356 --p_payer_equivalency=> IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD, bug 5439978
5357 p_payer_equivalency=> IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_FULL,
5358 p_entity_id => p_trnx_ext_id,
5359 x_response => l_response
5360 );
5361
5362 x_return_status :=l_return_status; --change
5363
5364 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5365 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Finished deleting transaction extension. l_return_status: '|| l_return_status);
5366 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'x_msg_data: '|| l_msg_data);
5367 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'x_msg_count: '|| l_msg_count);
5368 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'result_code: '|| l_response.Result_Code);
5369 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Category: '|| l_response.Result_Category);
5370 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Message: '|| l_response.Result_Message);
5371 END IF;
5372
5373
5374 Exception
5375 When Others Then
5376 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5377 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
5378 x_msg_data := l_msg_data;
5379
5380 End Delete_Credit_Card;
5381
5382 Function Create_credit_Card
5383 (p_line_id IN NUMBER,
5384 p_party_id IN NUMBER,
5385 p_org IN NUMBER,
5386 p_account_site_id IN NUMBER,
5387 p_cust_account_id IN NUMBER,
5388 p_trnx_ext_id IN NUMBER,
5389 x_return_status OUT NOCOPY VARCHAR2,
5390 x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER IS
5391
5392
5393 l_payer IBY_FNDCPT_Common_Pub.PayerContext_rec_type;
5394 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
5395 l_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
5396
5397
5398
5399
5400 l_instrument_id NUMBER;
5401 l_msg_count Number;
5402 l_msg_data Varchar2(1000);
5403 l_return_status Varchar2(1) := 'S';
5404 l_entity_id NUMBER;
5405
5406 Cursor get_instru_assigned_csr(l_trnx_ext_ID IN NUMBER) IS
5407 SELECT instr_assignment_id
5408 FROM IBY_TRXN_EXTENSIONS_V
5409 WHERE TRXN_EXTENSION_ID = l_trnx_ext_id;
5410
5411 get_instru_assigned_rec get_instru_assigned_csr%ROWTYPE;
5412
5413 l_api_name CONSTANT VARCHAR2(30) := 'Create_credit_Card';
5414
5415 Begin
5416
5417 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5418 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
5419 END IF;
5420
5421
5422 l_payer.payment_function := 'CUSTOMER_PAYMENT';
5423 l_payer.party_id := p_party_id; -- Id of the Lines Level bill to Party
5424 -- GCHADHA --
5425 -- 01-SEP-2005 --
5426 /*
5427 l_payer.org_type := 'OPERATING_UNIT';
5428 l_payer.org_id := p_org; -- Organization Id
5429 l_payer.account_site_id := p_account_site_id; -- Bill to Location --
5430 */
5431 -- END GCHADHA --
5432 l_payer.cust_account_id:=p_cust_account_id; -- Cust Account Id --
5433
5434 l_trxn_attribs.Originating_Application_Id := 515; --service contracts OKS
5435 l_trxn_attribs.Order_Id := p_line_id; -- line Id
5436 -- Bug 4866090 --
5437 l_trxn_attribs.trxn_ref_number1 := to_char(SYSDATE,'ddmmyyyyhhmmssss'); --to make order id and trx ref 1 unique
5438 -- Bug 4866090 --
5439
5440 Open get_instru_assigned_csr(p_trnx_ext_id);
5441 FETCH get_instru_assigned_csr INTO get_instru_assigned_rec;
5442 CLOSE get_instru_assigned_csr;
5443
5444
5445 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5446 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Calling IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension.');
5447 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.payment_function: ' || l_payer.payment_function);
5448 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.party_id: ' || l_payer.party_id);
5449 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.cust_account_id: ' || l_payer.cust_account_id);
5450 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_trxn_attribs.Originating_Application_Id: ' || l_trxn_attribs.Originating_Application_Id);
5451 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_trxn_attribs.Order_Id: ' || l_trxn_attribs.Order_Id);
5452 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_trxn_attribs.trxn_ref_number1: ' || l_trxn_attribs.trxn_ref_number1);
5453 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'get_instru_assigned_rec.instr_assignment_id: ' || get_instru_assigned_rec.instr_assignment_id);
5454 END IF;
5455
5456
5457 -- Create Transaction Extension Id
5458 IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension
5459 (
5460 p_api_version => 1.0
5461 ,p_init_msg_list => FND_API.G_TRUE
5462 ,p_commit => FND_API.G_FALSE
5463 ,x_return_status => l_return_status
5464 ,x_msg_count => l_msg_count
5465 ,x_msg_data => l_msg_data
5466 ,p_payer => l_payer
5467 --,p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD -- UPWARD
5468 ,p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_FULL -- FULL, bug 5439978
5469 ,p_pmt_channel => IBY_FNDCPT_SETUP_PUB.G_CHANNEL_CREDIT_CARD -- CREDIT_CARD
5470 ,p_instr_assignment => get_instru_assigned_rec.instr_assignment_id
5471 ,p_trxn_attribs => l_trxn_attribs
5472 ,x_entity_id => l_entity_id -- transaction_extension_id
5473 ,x_response => l_response
5474 );
5475 x_return_status := l_return_status;
5476
5477
5478 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5479 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Finished creating transaction extension. l_return_status: '|| l_return_status);
5480 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'x_msg_data: '|| l_msg_data);
5481 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'x_msg_count: '|| l_msg_count);
5482 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'result_code: '|| l_response.Result_Code);
5483 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Category: '|| l_response.Result_Category);
5484 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Message: '|| l_response.Result_Message);
5485 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_entity_id: ' || l_entity_id);
5486 END IF;
5487
5488
5489 return(l_entity_id);
5490 Exception
5491 When Others Then
5492 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5493 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
5494 x_msg_data := l_msg_data;
5495 End Create_Credit_Card;
5496
5497 /*Added for CASCADE_ER*/
5498 /*============================================================================+
5499 | Procedure: Add_Concurrent
5500 |
5501 | Purpose: Insert cascade attributes data into OKC_CASCADE_CONC_REQ
5502 |
5503 | In Parameters: header_lines_tbl Cascade header attributes
5504 | Out Parameters: x_return_status standard return status
5505 |
5506 +============================================================================*/
5507
5508 PROCEDURE ADD_CONCURRENT(header_lines_tbl IN OKS_ATTR_DEFAULTS_PVT.header_lines_tbl_type,
5509 X_return_status OUT NOCOPY Varchar2) IS
5510 l_id NUMBER;
5511 i NUMBER;
5512 BEGIN
5513 x_return_status := OKC_API.G_RET_STS_SUCCESS;
5514
5515 IF NOT header_lines_tbl.COUNT=0 THEN
5516
5517 i:=header_lines_tbl.FIRST;
5518 LOOP
5519 INSERT INTO OKS_CASCADE_CONC_REQ( chr_id,
5520 cle_id,
5521 header_sto,
5522 header_bto,
5523 header_dates,
5524 header_arl,
5525 header_ire,
5526 header_tax,
5527 header_exception_number,
5528 header_bca,
5529 header_sca,
5530 header_tax_code_id,
5531 header_tax_code,
5532 header_sales_credits,
5533 header_billto_contact,
5534 billto_id,
5535 billing_profile,
5536 billing_profile_id,
5537 calculate_tax,
5538 payment_method,
5539 price_uom,
5540 price_list,
5541 header_tax_cls_code)
5542 VALUES(header_lines_tbl(i).chr_id,
5543 header_lines_tbl(i).cle_id,
5544 header_lines_tbl(i).header_sto,
5545 header_lines_tbl(i).header_bto,
5546 header_lines_tbl(i).header_dates,
5547 header_lines_tbl(i).header_arl,
5548 header_lines_tbl(i).header_ire,
5549 header_lines_tbl(i).header_tax,
5550 header_lines_tbl(i).header_exception_number,
5551 header_lines_tbl(i).header_bca,
5552 header_lines_tbl(i).header_sca,
5553 header_lines_tbl(i).header_tax_code_id,
5554 header_lines_tbl(i).header_tax_code,
5555 header_lines_tbl(i).header_sales_credits,
5556 header_lines_tbl(i).header_billto_contact,
5557 header_lines_tbl(i).billto_id,
5558 header_lines_tbl(i).billing_profile,
5559 header_lines_tbl(i).billing_profile_id,
5560 header_lines_tbl(i).calculate_tax,
5561 header_lines_tbl(i).payment_method,
5562 header_lines_tbl(i).price_uom,
5563 header_lines_tbl(i).price_list,
5564 header_lines_tbl(i).header_tax_cls_code
5565 );
5566 exit when i=header_lines_tbl.last;
5567 i:=header_lines_tbl.next(i);
5568 END LOOP;
5569 END IF;
5570 COMMIT;
5571 END ADD_CONCURRENT;
5572
5573 /*============================================================================+
5574 | Procedure: Add_Concurrent_SUB |
5575 | Purpose: Insert cascade attributes data into OKC_CASCADE_CONC_REQ_SUB
5576 |
5577 | In Parameters: l_line_table Cascade lines attributes
5578 | Out Parameters: x_return_status standard return status
5579 |
5580 +============================================================================*/
5581
5582 PROCEDURE ADD_CONCURRENT_SUB(lines_sublines_tbl IN OKS_ATTR_DEFAULTS_PVT.lines_sublines_tbl_type,
5583 X_return_status OUT NOCOPY Varchar2) IS
5584 l_id NUMBER;
5585 i NUMBER :=0;
5586 BEGIN
5587 x_return_status := OKC_API.G_RET_STS_SUCCESS;
5588
5589 IF NOT lines_sublines_tbl.COUNT=0 THEN
5590
5591 i:=lines_sublines_tbl.FIRST;
5592 LOOP
5593 INSERT INTO OKS_CASCADE_CONC_REQ_SUB(chr_id ,
5594 cle_id ,
5595 subline_id ,
5596 line_irt ,
5597 line_renewal ,
5598 line_inv_print ,
5599 line_dates ,
5600 line_cov_eff ,
5601 header_to_lines ,
5602 price_uom )
5603 VALUES(lines_sublines_tbl(i).chr_id,
5604 lines_sublines_tbl(i).cle_id,
5605 lines_sublines_tbl(i).subline_id,
5606 lines_sublines_tbl(i).line_irt,
5607 lines_sublines_tbl(i).line_renewal,
5608 lines_sublines_tbl(i).line_inv_print,
5609 lines_sublines_tbl(i).line_dates,
5610 lines_sublines_tbl(i).line_cov_eff,
5611 lines_sublines_tbl(i).header_to_lines,
5612 lines_sublines_tbl(i).price_uom);
5613 exit when i=lines_sublines_tbl.last;
5614 i:=lines_sublines_tbl.next(i);
5615 END LOOP;
5616 END IF;
5617 COMMIT;
5618 END ADD_CONCURRENT_SUB;
5619
5620
5621 /*============================================================================+
5622 | Procedure: Concurrent_Temp_Purge
5623 |
5624 | Purpose: Purge data in tables OKS_CASCADE_CONC_REq and OKS_CASCADE_CONC_REQ_SUB.
5625 |
5626 | In Parameters: p_chr_id Contract Header Id
5627 |
5628 +============================================================================*/
5629
5630 PROCEDURE Concurrent_Temp_Purge(p_chr_id IN NUMBER) IS
5631 PRAGMA AUTONOMOUS_TRANSACTION;
5632
5633 BEGIN
5634
5635 DELETE FROM OKS_CASCADE_CONC_REQ WHERE CHR_ID=p_chr_id;
5636 DELETE FROM OKS_CASCADE_CONC_REQ_SUB WHERE CHR_ID=p_chr_id;
5637
5638 COMMIT;
5639
5640 END Concurrent_Temp_Purge;
5641
5642 /*============================================================================+
5643 | Procedure: Cascade_Concurrent
5644 |
5645 | Purpose: Invoke cascade api to cascade the attributes from header to lines
5646 |
5647 | In Parameters: p_chr_id the contract id
5648 | Out Parameters: x_return_status standard return status
5649 |
5650 +============================================================================*/
5651
5652 PROCEDURE Cascade_concurrent(errbuf OUT NOCOPY VARCHAR2
5653 ,retcode OUT NOCOPY NUMBER
5654 ,p_chr_id IN NUMBER
5655 )
5656 IS
5657 CURSOR CASCADE_DATA_HEADER_TO_LINES IS
5658 SELECT *
5659 FROM OKS_CASCADE_CONC_REQ
5660 WHERE chr_id=p_chr_id;
5661
5662 CURSOR CASCADE_DATA_LINES_TO_SUBLINES IS
5663 SELECT *
5664 FROM OKS_CASCADE_CONC_REQ_SUB
5665 WHERE chr_id=p_chr_id;
5666
5667 l_header_table header_lines_tbl_type;
5668 i NUMBER :=1;
5669 l_return_status VARCHAR2(10000);
5670 l_msg_tbl OKS_ATTR_DEFAULTS_PVT.attr_msg_tbl_type;
5671 idx NUMBER :=0;
5672 l_line_msg_tbl OKS_ATTR_DEFAULTS_PVT.attr_msg_tbl_type;
5673 l_line_table lines_sublines_tbl_type;
5674 BEGIN
5675 FND_FILE.PUT_LINE(FND_FILE.Log,'Entered Cascade Concurrent');
5676 FOR CASCADE_DATA_H_TO_L_REC IN CASCADE_DATA_HEADER_TO_LINES
5677 LOOP
5678 l_header_table(i).chr_id :=p_chr_id;
5679 l_header_table(i).cle_id :=CASCADE_DATA_H_TO_L_REC.cle_id;
5680 l_header_table(i).header_sto :=CASCADE_DATA_H_TO_L_REC.header_sto;
5681 l_header_table(i).header_bto :=CASCADE_DATA_H_TO_L_REC.header_bto;
5682 l_header_table(i).header_dates :=CASCADE_DATA_H_TO_L_REC.header_dates;
5683 l_header_table(i).header_arl:=CASCADE_DATA_H_TO_L_REC.header_arl;
5684 l_header_table(i).header_ire :=CASCADE_DATA_H_TO_L_REC.header_ire;
5685 l_header_table(i).header_tax :=CASCADE_DATA_H_TO_L_REC.header_tax;
5686 l_header_table(i).header_exception_number :=CASCADE_DATA_H_TO_L_REC.header_exception_number;
5687 l_header_table(i).header_bca :=CASCADE_DATA_H_TO_L_REC.header_bca;
5688 l_header_table(i).header_sca :=CASCADE_DATA_H_TO_L_REC.header_sca;
5689 l_header_table(i).header_tax_code_id :=CASCADE_DATA_H_TO_L_REC.header_tax_code_id;
5690 l_header_table(i).header_tax_code :=CASCADE_DATA_H_TO_L_REC.header_tax_code;
5691 l_header_table(i).header_sales_credits :=CASCADE_DATA_H_TO_L_REC.header_sales_credits;
5692 l_header_table(i).header_billto_contact :=CASCADE_DATA_H_TO_L_REC.header_billto_contact;
5693 l_header_table(i).billto_id :=CASCADE_DATA_H_TO_L_REC.billto_id;
5694 l_header_table(i).billing_profile :=CASCADE_DATA_H_TO_L_REC.billing_profile;
5695 l_header_table(i).billing_profile_id :=CASCADE_DATA_H_TO_L_REC.billing_profile_id;
5696 l_header_table(i).calculate_tax :=CASCADE_DATA_H_TO_L_REC.calculate_tax;
5697 l_header_table(i).payment_method :=CASCADE_DATA_H_TO_L_REC.payment_method;
5698 l_header_table(i).price_uom :=CASCADE_DATA_H_TO_L_REC.price_uom;
5699 l_header_table(i).price_list :=CASCADE_DATA_H_TO_L_REC.price_list;
5700 l_header_table(i).header_tax_cls_code :=CASCADE_DATA_H_TO_L_REC.header_tax_cls_code;
5701
5702 i := i + 1;
5703 END LOOP;
5704
5705 FOR CASCADE_DATA_L_TO_S_REC IN CASCADE_DATA_LINES_TO_SUBLINES
5706 LOOP
5707 l_line_table(i).chr_id :=p_chr_id;
5708 l_line_table(i).cle_id :=CASCADE_DATA_L_TO_S_REC.cle_id;
5709 l_line_table(i).subline_id :=CASCADE_DATA_L_TO_S_REC.subline_id;
5710 l_line_table(i).line_irt :=CASCADE_DATA_L_TO_S_REC.line_irt;
5711 l_line_table(i).line_renewal :=CASCADE_DATA_L_TO_S_REC.line_renewal;
5712 l_line_table(i).line_inv_print:=CASCADE_DATA_L_TO_S_REC.line_inv_print;
5713 l_line_table(i).line_dates :=CASCADE_DATA_L_TO_S_REC.line_dates;
5714 l_line_table(i).line_cov_eff :=CASCADE_DATA_L_TO_S_REC.line_cov_eff;
5715 l_line_table(i).header_to_lines :='Y';
5716 l_line_table(i).price_uom :=CASCADE_DATA_L_TO_S_REC.price_uom;
5717
5718 i := i + 1;
5719 END LOOP;
5720
5721 FND_FILE.PUT_LINE(FND_FILE.Log,'Before entering procedure default_header_to_lines');
5722
5723 IF l_header_table.count >0 then
5724 OKS_ATTR_DEFAULTS_PVT.DEFAULT_HEADER_TO_LINES
5725 (header_lines_tbl => l_header_table,
5726 x_return_status => l_return_status,
5727 x_msg_tbl => l_msg_tbl );
5728 END IF;
5729
5730
5731 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS
5732 THEN
5733 idx := l_msg_tbl.COUNT;
5734 IF idx <> 0 Then
5735 idx := l_msg_tbl.FIRST;
5736 Loop
5737 IF NVL(l_msg_tbl(idx).status,'X') NOT IN ('S','E','U','W') Then
5738 l_msg_tbl(idx).status := 'U';
5739 END IF;
5740 IF l_msg_tbl(idx).status not in('S','W') Then
5741 FND_FILE.PUT_LINE(FND_FILE.Log,l_msg_tbl(idx).description||' '||l_msg_tbl(idx).status);
5742 END IF;
5743 Exit When idx = l_msg_tbl.LAST;
5744 idx := l_msg_tbl.NEXT(idx);
5745 END LOOP;
5746 END IF;
5747 FND_FILE.PUT_LINE(FND_FILE.Log,'Failed to complete cascade program as error occured in procedure default_header_to_lines');
5748 retcode := 2;
5749 ELSE
5750 idx := l_msg_tbl.COUNT;
5751 IF idx <> 0 Then
5752 idx := l_msg_tbl.FIRST;
5753 Loop
5754 IF l_msg_tbl(idx).status IN('S','W') Then
5755 FND_FILE.PUT_LINE(FND_FILE.Log,l_msg_tbl(idx).description||' '||l_msg_tbl(idx).status);
5756 END IF;
5757 Exit When idx = l_msg_tbl.LAST;
5758 idx := l_msg_tbl.NEXT(idx);
5759 END LOOP;
5760 END IF;
5761 FND_FILE.PUT_LINE(FND_FILE.Log,'Cascade Attributes for Header to Lines completed succesfully');
5762 retcode := 0;
5763 END IF;
5764
5765 FND_FILE.PUT_LINE(FND_FILE.Log,'Before Entering procedure default_lines_to_sublines');
5766
5767 IF l_line_table.count >0 then
5768
5769
5770 OKS_ATTR_DEFAULTS_PVT.DEFAULT_LINES_TO_SUBLINES
5771 (lines_sublines_tbl => l_line_table,
5772 x_return_status => l_return_status,
5773 x_msg_tbl => l_line_msg_tbl);
5774
5775 END IF;
5776
5777
5778 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS
5779 THEN
5780 idx := l_line_msg_tbl.COUNT;
5781 IF idx <> 0 Then
5782 idx := l_line_msg_tbl.FIRST;
5783 Loop
5784 IF NVL(l_line_msg_tbl(idx).status,'X') NOT IN ('S','E','U','W') Then
5785 l_msg_tbl(idx).status := 'U';
5786 END IF;
5787 IF l_line_msg_tbl(idx).status not in('S','W') Then
5788 FND_FILE.PUT_LINE(FND_FILE.Log,l_line_msg_tbl(idx).description||' '||l_line_msg_tbl(idx).status);
5789 END IF;
5790 Exit When idx = l_line_msg_tbl.LAST;
5791 idx := l_line_msg_tbl.NEXT(idx);
5792 END LOOP;
5793 END IF;
5794 FND_FILE.PUT_LINE(FND_FILE.Log,'Failed to complete cascade program as error occured in procedure default_header_to_lines');
5795 retcode := 2;
5796 ELSE
5797 idx := l_line_msg_tbl.COUNT;
5798 IF idx <> 0 Then
5799 idx := l_line_msg_tbl.FIRST;
5800 Loop
5801 IF l_line_msg_tbl(idx).status IN('S','W') Then
5802 FND_FILE.PUT_LINE(FND_FILE.Log,l_line_msg_tbl(idx).description||' '||l_line_msg_tbl(idx).status);
5803 END IF;
5804 Exit When idx = l_line_msg_tbl.LAST;
5805 idx := l_line_msg_tbl.NEXT(idx);
5806 END LOOP;
5807 END IF;
5808 FND_FILE.PUT_LINE(FND_FILE.Log,'Cascade Attributes procedure for lines to sublines succesfully');
5809 IF retcode NOT IN (1,2) THEN
5810 retcode := 0;
5811 END IF;
5812 END IF;
5813
5814 Concurrent_Temp_Purge(p_chr_id);
5815
5816 FND_FILE.PUT_LINE(FND_FILE.Log,'Cascade Attributes Concurrent Program Completed succesfully');
5817
5818 errbuf := '1';
5819
5820
5821 END Cascade_concurrent;
5822
5823 /*Added for CASCADE_ER*/
5824
5825
5826 -- Bank Account Consolidation --
5827 END OKS_ATTR_DEFAULTS_PVT;