[Home] [Help]
PACKAGE BODY: APPS.OKS_ATTR_DEFAULTS_PVT
Source
1 Package body OKS_ATTR_DEFAULTS_PVT AS
2 /* $Header: OKSRDFTB.pls 120.29 2007/06/22 10:32:33 mkarra 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 l_billing_type VARCHAR2 (450);
913 l_bpf_acct_rule_id NUMBER;
914 l_bpf_invoice_rule_id NUMBER;
915 l_line_number VARCHAR2 (150);
916 l_flag BOOLEAN;
917
918 --Fix for Bug# 3542273
919 l_usage_type VARCHAR2 (10);
920 l_token1_value VARCHAR2 (1000);
921 --Fix for Bug# 3542273
922 --new....
923 l_lov_cust_acct NUMBER;
924 --new....
925 --BUG#4089834
926 l_status_flag BOOLEAN := FALSE;
927 l_exmpt_num_flag BOOLEAN := FALSE;
928
929 -- GCHADHA --
930 -- BUG 4394382 --
931 l_cust_po_flag NUMBER := 0; -- Flag to mark PO number change
932 l_payment_method_com NUMBER := 0; -- Flag to mark Commitment number details
933 l_payment_method_ccr NUMBER := 0; -- Flag to mark Credit card Change
934 l_trxn_extension_id NUMBER := 0; -- Bank Account Consolidation
935 -- END GCHADHA --
936
937
938 -- hkamdar 8/23/05 R12 Partial Period
939 l_price_uom VARCHAR2(100);
940 l_error VARCHAR2(1);
941 l_locked_prl_cnt NUMBER := 0;
942 l_header_price_uom VARCHAR2(150);
943 l_header_price_list VARCHAR2(150);
944 l_source_price_list_line_id NUMBER;
945 l_locked_price_list_id NUMBER;
946 l_locked_price_list_line_id NUMBER;
947
948 l_input_details OKS_QP_PKG.INPUT_DETAILS;
949 l_output_details OKS_QP_PKG.PRICE_DETAILS;
950 l_modif_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
951 l_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
952 l_status_tbl oks_qp_int_pvt.PRICING_STATUS_TBL;
953 l_pricing_effective_date DATE;
954 l_validate_result VARCHAR2(10);
955 -- End hkamdar 8/23/05 R12 Partial Period
956
957 -- mkarra 06/21/07 Import Contracts Bug 6128632
958 l_bill_y_n VARCHAR2(1);
959 l_disp_warning VARCHAR2(1) ;
960
961 Begin
962
963 fnd_msg_pub.initialize;
964 IF NOT header_lines_tbl.COUNT=0 THEN
965
966
967 i:=header_lines_tbl.FIRST;
968 LOOP
969 l_error := 'N'; -- 8/23/2005 hkamdar R12 Partial Period
970
971 l_chr_id := header_lines_tbl(i).chr_id;
972 l_cle_id := header_lines_tbl(i).cle_id;
973 l_header_sto := header_lines_tbl(i).header_sto;
974 l_header_bto := header_lines_tbl(i).header_bto;
975 l_header_dates := header_lines_tbl(i).header_dates;
976 l_header_arl := header_lines_tbl(i).header_arl;
977 l_header_ire := header_lines_tbl(i).header_ire;
978 l_header_tax := header_lines_tbl(i).header_tax;
979 l_header_exception_number := header_lines_tbl(i).header_exception_number;
980 l_header_tax_code := header_lines_tbl(i).header_tax_code;
981 --Fixed bug#4026268 --gbgupta
982
983 l_header_tax_code_id := header_lines_tbl(i).header_tax_code_id;
984
985 l_header_sales_credits := header_lines_tbl(i).header_sales_credits;
986 l_header_billto_contact := header_lines_tbl(i).header_billto_contact;
987 l_billto_id := header_lines_tbl(i).billto_id;
988 l_billing_profile := header_lines_tbl(i).billing_profile;
989 l_billing_profile_id := header_lines_tbl(i).billing_profile_id;
990 l_calculate_tax := header_lines_tbl(i).calculate_tax;
991 l_payment_method := header_lines_tbl(i).payment_method;
992 -- GCHADHA --
993 -- IKON --
994 l_header_bca := header_lines_tbl(i).header_bca;
995 l_header_sca := header_lines_tbl(i).header_sca;
996 -- END GCHADHA --
997
998 -- 8/23/2005 hkamdar R12 Partial Period
999 l_header_price_uom := header_lines_tbl(i).price_uom;
1000 l_header_price_list := header_lines_tbl(i).price_list;
1001 -- End 8/23/2005 hkamdar R12 Partial Period
1002 --Ebtax --
1003 l_header_tax_cls_code := header_lines_tbl(i).header_tax_cls_code;
1004 --Ebtax --
1005
1006 l_klnv_tbl_in.DELETE;
1007 l_clev_tbl_in.DELETE;
1008
1009 -- check if the contract has been imported and fully billed at source or not
1010
1011 IF(l_chr_id IS NOT NULL) THEN
1012 select billed_at_source INTO l_bill_y_n FROM okc_k_headers_all_b where id = l_chr_id ;
1013 END IF;
1014
1015 IF ((l_bill_y_n IS NOT NULL) AND (l_bill_y_n = 'Y')) THEN
1016 l_disp_warning :='Y';
1017 ELSE
1018 l_disp_warning :='N';
1019 END IF;
1020
1021
1022
1023 OPEN cur_okc_headers(l_chr_id );
1024 FETCH cur_okc_headers INTO l_okc_headers_rec;
1025 IF cur_okc_headers%NOTFOUND then
1026 CLOSE cur_okc_headers;
1027 x_return_status := 'E';
1028 RAISE G_EXCEPTION_HALT_VALIDATION;
1029 ELSE
1030 CLOSE cur_okc_headers;
1031 END IF;
1032
1033 OPEN cur_okc_lines(l_cle_id );
1034 FETCH cur_okc_lines INTO l_okc_lines_rec;
1035 IF cur_okc_lines%NOTFOUND then
1036 CLOSE cur_okc_lines;
1037 x_return_status := 'E';
1038 RAISE G_EXCEPTION_HALT_VALIDATION;
1039 ELSE
1040 CLOSE cur_okc_lines;
1041 END IF;
1042
1043 OPEN cur_oks_headers(l_chr_id );
1044 FETCH cur_oks_headers INTO l_oks_headers_rec;
1045 IF cur_oks_headers%NOTFOUND then
1046 CLOSE cur_oks_headers;
1047 x_return_status := 'E';
1048 RAISE G_EXCEPTION_HALT_VALIDATION;
1049 ELSE
1050 CLOSE cur_oks_headers;
1051 END IF;
1052
1053 OPEN cur_oks_lines(l_cle_id );
1054 FETCH cur_oks_lines INTO l_oks_lines_rec;
1055 IF cur_oks_lines%NOTFOUND then
1056 CLOSE cur_oks_lines;
1057 x_return_status := 'E';
1058 RAISE G_EXCEPTION_HALT_VALIDATION;
1059 ELSE
1060 CLOSE cur_oks_lines;
1061 END IF;
1062
1063 OPEN cur_header_dates(l_chr_id );
1064 FETCH cur_header_dates INTO header_dates_rec;
1065 IF cur_header_dates%NOTFOUND then
1066 CLOSE cur_header_dates;
1067 x_return_status := 'E';
1068 RAISE G_EXCEPTION_HALT_VALIDATION;
1069 ELSE
1070 CLOSE cur_header_dates;
1071 END IF;
1072
1073 OPEN cur_line_number(l_cle_id );
1074 FETCH cur_line_number INTO l_line_number;
1075 IF cur_line_number%NOTFOUND then
1076 CLOSE cur_line_number;
1077 x_return_status := 'E';
1078 RAISE G_EXCEPTION_HALT_VALIDATION;
1079 ELSE
1080 CLOSE cur_line_number;
1081 END IF;
1082
1083 -- Bug 5191587 --
1084 If l_okc_lines_rec.lse_id in ('1','14','19') then
1085 l_sr_flag :=0;
1086 FOR cur_rec in cur_service_req_number(l_cle_id,l_chr_id)
1087 LOOP
1088 IF l_sr_flag <> 0 THEN
1089 l_sr_number := l_sr_number || ' ; ';
1090 END IF;
1091 l_sr_number := l_sr_number || cur_rec.incident_number;
1092 l_sr_flag :=1;
1093 END LOOP;
1094
1095 /* OPEN cur_service_req_number(l_cle_id );
1096 FETCH cur_service_req_number INTO l_service_req_rec;
1097 IF cur_service_req_number%NOTFOUND then
1098 CLOSE cur_service_req_number;
1099 ELSE
1100 l_sr_number := l_service_req_rec.incident_number;
1101 CLOSE cur_service_req_number;
1102 END IF;
1103 */
1104 End If;
1105 -- Bug 5191587 --
1106
1107 l_klnv_tbl_in(1).OBJECT_VERSION_NUMBER := l_oks_lines_rec.OBJECT_VERSION_NUMBER;
1108
1109 If l_header_dates is not null then
1110
1111 --new
1112
1113 If oks_extwar_util_pvt.check_already_billed(
1114 p_chr_id => null,
1115 p_cle_id => header_lines_tbl(i).cle_id,
1116 p_lse_id => l_okc_lines_rec.lse_id,
1117 p_end_date => null) Then
1118
1119
1120 validate_date
1121 (p_api_version => l_api_version
1122 ,p_init_msg_list => l_init_msg_list
1123 ,p_hdr_id => l_chr_id
1124 ,p_top_line_id => l_cle_id
1125 ,p_sub_line_id => NULL
1126 ,x_return_status => l_return_status
1127 ,x_msg_count => l_msg_count
1128 ,x_msg_data => l_msg_data
1129 ,x_flag => l_flag);
1130
1131 -- Bug 5227077 --
1132 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1133 FOR i in 1..fnd_msg_pub.count_msg
1134 Loop
1135 fnd_msg_pub.get
1136 (
1137 p_msg_index => i,
1138 p_encoded => 'F',
1139 p_data => l_msg_data,
1140 p_msg_index_out => l_msg_index
1141 );
1142 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1143 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1144 l_tot_msg_count := l_tot_msg_count + 1;
1145 l_msg_data := NULL;
1146 End Loop;
1147 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1148 Else
1149 If l_flag <> TRUE then
1150 fnd_msg_pub.initialize;
1151
1152 OKC_API.SET_MESSAGE(
1153 p_app_name => 'OKS', --G_APP_NAME_OKS,
1154 p_msg_name => 'OKS_BA_UPDATE_NOT_ALLOWED',
1155 p_token1 => 'Line No ',
1156 p_token1_value => l_line_number);
1157
1158 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1159 p_encoded => fnd_api.g_false);
1160 x_msg_tbl(l_tot_msg_count).status := 'E';
1161 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1162 l_tot_msg_count := l_tot_msg_count + 1;
1163 l_msg_data := NULL;
1164 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1165 End If;
1166 End If;
1167
1168 End If;
1169
1170 --new
1171
1172 l_clev_tbl_in(1).id := l_cle_id;
1173 l_clev_tbl_in(1).chr_id := l_chr_id;
1174 l_clev_tbl_in(1).start_date := header_dates_rec.start_date;
1175 l_clev_tbl_in(1).end_date := header_dates_rec.end_date;
1176 l_clev_tbl_in(1).dnz_chr_id := l_chr_id;
1177
1178 If l_okc_lines_rec.lse_id = '14' Then
1179 If l_clev_tbl_in(1).start_date > SYSDATE THen
1180 l_clev_tbl_in(1).sts_code := 'SIGNED';
1181 Elsif l_clev_tbl_in(1).start_date <= SYSDATE And l_clev_tbl_in(1).end_date >= SYSDATE THEN
1182 l_clev_tbl_in(1).sts_code := 'ACTIVE';
1183 ELSIF l_clev_tbl_in(1).end_date < SYSDATE Then
1184 l_clev_tbl_in(1).sts_code :='EXPIRED';
1185 End if;
1186 End if;
1187
1188 END IF;
1189
1190 If l_header_bto is not null then
1191
1192 Open cur_head_cust_acct(l_okc_headers_rec.bill_to_site_use_id);
1193 fetch cur_head_cust_acct into l_head_cust_acct;
1194 close cur_head_cust_acct;
1195
1196 Open cur_line_cust_acct(l_okc_lines_rec.id);
1197 fetch cur_line_cust_acct into l_line_cust_acct;
1198 close cur_line_cust_acct;
1199 If l_head_cust_acct = l_line_cust_acct or
1200 l_line_cust_acct is NULL and l_head_cust_acct is not NULL Then
1201
1202 l_clev_tbl_in(1).id := l_cle_id;
1203 l_clev_tbl_in(1).bill_to_site_use_id := l_okc_headers_rec.bill_to_site_use_id;
1204 l_clev_tbl_in(1).cust_acct_id := l_head_cust_acct;
1205
1206 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1207 fnd_msg_pub.initialize;
1208 OKC_API.SET_MESSAGE(
1209 p_app_name => G_APP_NAME_OKS,
1210 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1211 p_token1 => 'ATTRIBUTE',
1212 p_token1_value => 'Bill To Address');
1213
1214 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1215 p_encoded => fnd_api.g_false);
1216
1217 x_msg_tbl(l_tot_msg_count).status := 'S';
1218 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1219 l_tot_msg_count := l_tot_msg_count + 1;
1220 l_msg_data := NULL;
1221
1222 END IF;
1223 -- IKON ENHANCEMENT
1224 /* ElsIf l_head_cust_acct <> l_line_cust_acct and
1225 l_line_cust_acct is not NULL and l_head_cust_acct is not NULL Then
1226 fnd_msg_pub.initialize;
1227
1228 OKC_API.SET_MESSAGE(
1229 p_app_name => G_APP_NAME_OKS,
1230 p_msg_name => 'OKS_CASCADE_ACCOUNT_MISMATCH',
1231 p_token1 => 'Line No ',
1232 p_token1_value => l_line_number);
1233
1234 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1235 p_encoded => fnd_api.g_false);
1236
1237 x_msg_tbl(l_tot_msg_count).status := 'E';
1238 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1239 l_tot_msg_count := l_tot_msg_count + 1;
1240 l_msg_data := NULL;
1241 If header_lines_tbl.FIRST = header_lines_tbl.LAST Then
1242 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1243 End If; */
1244
1245
1246 END IF; --l_head_cust_acct check
1247 END IF;
1248
1249 If l_header_sto is not null then
1250 -- IKON ENHANCEMENT --
1251 -- 2/11/2005
1252 Open cur_head_cust_acct(l_okc_headers_rec.ship_to_site_use_id);
1253 fetch cur_head_cust_acct into l_header_cust_acct;
1254 close cur_head_cust_acct;
1255
1256 Open CUR_CUST_ACCT(l_okc_lines_rec.ship_to_site_use_id,'SHIP_TO');
1257 fetch CUR_CUST_ACCT into l_line_cust_acct,l_party_id;
1258 close CUR_CUST_ACCT;
1259
1260 --npalepu modified on 22-FEB-2007 for bug # 5742807. Added extra condition.
1261 /* If l_header_cust_acct = l_line_cust_acct THEN */
1262
1263 If (l_header_cust_acct = l_line_cust_acct) or
1264 (l_line_cust_acct is NULL and l_header_cust_acct is not NULL) Then
1265 --end bug # 5742807
1266
1267 l_clev_tbl_in(1).id := l_cle_id;
1268 l_clev_tbl_in(1).ship_to_site_use_id := l_okc_headers_rec.ship_to_site_use_id;
1269
1270 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1271 fnd_msg_pub.initialize;
1272 OKC_API.SET_MESSAGE(
1273 p_app_name => G_APP_NAME_OKS,
1274 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1275 p_token1 => 'ATTRIBUTE',
1276 p_token1_value => 'Ship To Address');
1277
1278 -- fnd_msg_pub.initialize;
1279 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1280 p_encoded => fnd_api.g_false);
1281 x_msg_tbl(l_tot_msg_count).status := 'S';
1282 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1283 l_tot_msg_count := l_tot_msg_count + 1;
1284 l_msg_data := NULL;
1285 END IF;
1286 END IF;
1287 -- IKON ENHANCEMENT --
1288 END IF;
1289
1290 -- *******************************************************************
1291 -- IKON ENHANCEMENT --
1292 IF l_header_bca is not null then
1293
1294 -- l_oks_header_id is used to mark whether
1295 -- OKS_K_HEADERS_B table should be update or not
1296 -- If l_oks_header_id is 0 then
1297 -- OKS_K_HEADERS_B should not be updated.
1298 -- Else should be Updated.
1299 -- Used only with l_header_bca variable.
1300 l_oks_header_id :=0;
1301
1302 Open cur_head_cust_acct(l_okc_headers_rec.bill_to_site_use_id);
1303 fetch cur_head_cust_acct into l_header_cust_acct;
1304 close cur_head_cust_acct;
1305
1306 Open cur_line_cust_acct(l_okc_lines_rec.id);
1307 fetch cur_line_cust_acct into l_line_cust_acct;
1308 close cur_line_cust_acct;
1309
1310
1311 -- CASE I If no BTO rule is there then create a BTO rule
1312 -- CASE II If there is a BTO RULE then
1313 -- Update the BTO rule at lines leve with header
1314 -- level Customer account if the accounts are
1315 -- not same.
1316 -- Delete all contacts which are not
1317 -- billing contact and are belonging to
1318 -- ship to account Party Role
1319 -- CASE I
1320 IF l_header_cust_acct <> nvl(l_line_cust_acct, -1) THEN
1321 -- Create/UPDATE a CAN RULE and Create/UPDATE the BTO Rule --
1322 l_clev_tbl_in(1).id := l_cle_id;
1323 l_clev_tbl_in(1).bill_to_site_use_id := l_okc_headers_rec.bill_to_site_use_id;
1324 l_clev_tbl_in(1).cust_acct_id := l_header_cust_acct;
1325 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
1326 l_oks_header_id := 1;
1327
1328 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1329 IF l_line_cust_acct IS NULL THEN
1330 fnd_msg_pub.initialize;
1331 OKC_API.SET_MESSAGE(
1332 p_app_name => G_APP_NAME_OKS,
1333 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1334 p_token1 => 'ATTRIBUTE',
1335 p_token1_value => 'Bill To Address');
1336
1337 -- fnd_msg_pub.initialize;
1338 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1339 p_encoded => fnd_api.g_false);
1340 x_msg_tbl(l_tot_msg_count).status := 'S';
1341 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1342 l_tot_msg_count := l_tot_msg_count + 1;
1343 l_msg_data := NULL;
1344 END IF;
1345
1346
1347
1348 fnd_msg_pub.initialize;
1349 OKC_API.SET_MESSAGE(
1350 p_app_name => G_APP_NAME_OKS, -- bug 5468539 G_APP_NAME,
1351 p_msg_name =>'OKS_DEFAULT_ATTR_SUCCESS_NEW',
1352 p_token1 => 'TOKEN1' , -- bug 5468539 'RULE',
1353 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'));
1354
1355 -- fnd_msg_pub.initialize;
1356 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1357 p_encoded => fnd_api.g_false);
1358 x_msg_tbl(l_tot_msg_count).status := 'S';
1359 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1360 l_tot_msg_count := l_tot_msg_count + 1;
1361 l_msg_data := NULL;
1362
1363 END IF;
1364 END IF;
1365 IF l_line_cust_acct is not NULL AND l_header_cust_acct <> l_line_cust_acct THEN
1366 open Get_Party_Id (l_line_cust_acct);
1367 fetch Get_party_id into l_line_party_id,l_temp_party_name;
1368 close get_party_id;
1369 -- DELETE THE TAX EXEMPTION IF THERE
1370 -- Bug 5202208 --
1371 -- Modified code to keep a check for both Exempt_certificate_number
1372 -- and tax_exemption_id for contracts migrated from 11.5.10.
1373 -- IF l_oks_lines_rec.TAX_STATUS ='E' AND l_oks_lines_rec.TAX_EXEMPTION_ID IS NOT NULL THEN
1374 IF (l_oks_lines_rec.TAX_STATUS ='E' AND ( l_oks_lines_rec.EXEMPT_CERTIFICATE_NUMBER IS NOT NULL
1375 OR l_oks_lines_rec.TAX_EXEMPTION_ID IS NOT NULL))
1376 THEN
1377 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
1378 l_klnv_tbl_in(1).TAX_STATUS := NUll;
1379 l_klnv_tbl_in(1).TAX_EXEMPTION_ID := NULL;
1380 l_klnv_tbl_in(1).EXEMPT_REASON_CODE := NULL;
1381 l_klnv_tbl_in(1).EXEMPT_CERTIFICATE_NUMBER:= NULL;
1382 l_klnv_tbl_in(1).CLE_ID := l_cle_id;
1383 l_oks_header_id := 1;
1384 -- Display a message --
1385 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1386 fnd_msg_pub.initialize;
1387 OKC_API.SET_MESSAGE(
1388 p_app_name => G_APP_NAME,
1389 p_msg_name =>'OKS_CASCADE_DELETE_EXEMPTION',
1390 p_token1 =>'TOKEN1' ,
1391 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'),
1392 p_token2 => 'TOKEN2',
1393 p_token2_value => l_temp_party_name,
1394 p_token3 => 'TOKEN3',
1395 p_token3_value => l_temp_line_number);
1396 -- fnd_msg_pub.initialize;
1397 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1398 p_encoded => fnd_api.g_false);
1399 x_msg_tbl(l_tot_msg_count).status := 'S';
1400 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1401 l_tot_msg_count := l_tot_msg_count + 1;
1402 l_msg_data := NULL;
1403 END IF;
1404 END IF;
1405 -- Bug 5202208 --
1406 --GCHADHA --
1407 -- BUG 4394382 --
1408 -- DELETE COMMITMENT NUMBER
1409 IF l_oks_lines_rec.payment_type = 'COM' THEN
1410 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
1411 l_klnv_tbl_in(1).CLE_ID := l_cle_id;
1412 l_klnv_tbl_in(1).commitment_id := NULL ;
1413 l_klnv_tbl_in(1).payment_type := NULL ;
1414 -- Display a message --
1415 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1416 fnd_msg_pub.initialize;
1417 OKC_API.SET_MESSAGE(
1418 p_app_name => G_APP_NAME,
1419 p_msg_name =>'OKS_CASCADE_DELETE_COMMITMENT',
1420 p_token1 =>'TOKEN1' ,
1421 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'),
1422 p_token2 => 'TOKEN2',
1423 p_token2_value => l_temp_party_name,
1424 p_token3 => 'TOKEN3',
1425 p_token3_value => l_temp_line_number);
1426 -- fnd_msg_pub.initialize;
1427 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1428 p_encoded => fnd_api.g_false);
1429 x_msg_tbl(l_tot_msg_count).status := 'S';
1430 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1431 l_tot_msg_count := l_tot_msg_count + 1;
1432 l_msg_data := NULL;
1433 END IF;
1434
1435 END IF;
1436
1437 -- END GCHADHA --
1438
1439 -- DELETE CONTACTS --
1440
1441 FOR CUR_PARTY_REC IN CUR_LINE_PARTY_ROLE(l_chr_id, l_cle_id,l_line_party_id ) LOOP
1442 l_cpl_id := CUR_PARTY_REC.ID;
1443
1444 IF l_cpl_id is NOT NULL THEN
1445 l_contact_id := CHECK_LINE_CONTACT(l_chr_id,l_cpl_id); -- 5485054
1446 IF l_contact_id is NOT NULL THEN
1447 l_contact :=0;
1448 -- When Cascading Bill to Cust Account then Don't check for Shipping Contact
1449 FOR contact_rec in line_contact_jtot(l_cpl_id,l_chr_id,'OKX_CONTSHIP') LOOP
1450
1451 Open Get_Contact_Cust_Acct(contact_rec.object1_id1);
1452 fetch Get_Contact_Cust_Acct into l_line_cust_acct;
1453 close Get_Contact_Cust_Acct;
1454 -- Delete the contact when Contact Account Id is not same as
1455 -- header Account Id
1456 IF l_header_cust_acct <> l_line_cust_acct THEN
1457
1458 l_ctcv_tbl_in(l_contact).id := contact_rec.id;
1459 l_ctcv_tbl_in(l_contact).cpl_id := l_cpl_id;
1460 l_ctcv_tbl_in(l_contact).dnz_chr_id := contact_rec.dnz_chr_ID;
1461
1462 l_ctcv_tbl_in(l_contact).cro_code := contact_rec.cro_code;
1463 l_ctcv_tbl_in(l_contact).OBJECT1_ID1 := contact_rec.object1_id1;
1464 l_ctcv_tbl_in(l_contact).object1_id2 := contact_rec.OBJECT1_ID2;
1465 l_ctcv_tbl_in(l_contact).JTOT_OBJECT1_CODE := contact_rec.JTOT_OBJECT1_CODE ;
1466 l_ctcv_tbl_in(l_contact).start_date := contact_rec.START_DATE;
1467 l_ctcv_tbl_in(l_contact).end_date := contact_rec.END_DATE;
1468 l_ctcv_tbl_in(l_contact).attribute_category := contact_rec.ATTRIBUTE_CATEGORY;
1469 l_ctcv_tbl_in(l_contact).attribute1 := contact_rec.ATTRIBUTE1;
1470 l_ctcv_tbl_in(l_contact).attribute2 := contact_rec.ATTRIBUTE2;
1471 l_ctcv_tbl_in(l_contact).attribute3 := contact_rec.ATTRIBUTE3;
1472 l_ctcv_tbl_in(l_contact).attribute4 := contact_rec.ATTRIBUTE4;
1473 l_ctcv_tbl_in(l_contact).attribute5 := contact_rec.ATTRIBUTE5;
1474 l_ctcv_tbl_in(l_contact).attribute6 := contact_rec.ATTRIBUTE6;
1475 l_ctcv_tbl_in(l_contact).attribute7 := contact_rec.ATTRIBUTE7;
1476 l_ctcv_tbl_in(l_contact).attribute8 := contact_rec.ATTRIBUTE8;
1477 l_ctcv_tbl_in(l_contact).attribute9 := contact_rec.ATTRIBUTE9;
1478 l_ctcv_tbl_in(l_contact).attribute10 := contact_rec.ATTRIBUTE10;
1479 l_ctcv_tbl_in(l_contact).attribute11 := contact_rec.ATTRIBUTE11;
1480 l_ctcv_tbl_in(l_contact).attribute12 := contact_rec.ATTRIBUTE12;
1481 l_ctcv_tbl_in(l_contact).attribute13 := contact_rec.ATTRIBUTE13;
1482 l_ctcv_tbl_in(l_contact).attribute14 := contact_rec.ATTRIBUTE14;
1483 l_ctcv_tbl_in(l_contact).attribute15 := contact_rec.ATTRIBUTE15;
1484 l_ctcv_tbl_in(l_contact).object_version_number := contact_rec.object_version_number;
1485 l_ctcv_tbl_in(l_contact).created_by := contact_rec.created_by;
1486 l_ctcv_tbl_in(l_contact).creation_date := contact_rec.creation_date;
1487 l_ctcv_tbl_in(l_contact).last_updated_by := contact_rec.last_updated_by;
1488 l_ctcv_tbl_in(l_contact).last_update_date := contact_rec.last_update_date;
1489 l_ctcv_tbl_in(l_contact).last_update_login := contact_rec.last_update_login;
1490 l_contact := l_contact+1;
1491 END IF ;-- line_contact_cust_acct
1492 END LOOP;
1493
1494 IF l_header_cust_acct <> l_line_cust_acct OR l_contact > 1 THEN
1495
1496 okc_contract_party_pub.delete_contact
1497 (p_api_version => l_api_version,
1498 p_init_msg_list => l_init_msg_list,
1499 x_return_status => l_return_status,
1500 x_msg_count => l_msg_count,
1501 x_msg_data => l_msg_data,
1502 p_ctcv_tbl => l_ctcv_tbl_in
1503 );
1504
1505 -- Delete Party Role --
1506 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1507 -- Check if there is any contaact Present belonging to the party role ---
1508 l_temp_contact_id := CHECK_LINE_CONTACT(l_chr_id,l_cpl_id);-- 5485054
1509 IF nvl(l_temp_contact_id,0) = 0 THEN
1510
1511
1512 l_cplv_tbl_in(i).id := l_cpl_id ;--l_party_role.id;
1513 l_cplv_tbl_in(i).cle_id := l_cle_id ;-- l_party_role.cle_id;
1514 l_cplv_tbl_in(i).dnz_chr_id := l_chr_id ; --l_party_role.dnz_chr_id;
1515 okc_contract_party_pub.delete_k_party_role
1516 (p_api_version => l_api_version,
1517 p_init_msg_list => l_init_msg_list,
1518 x_return_status => l_return_status,
1519 x_msg_count => l_msg_count,
1520 x_msg_data => l_msg_data,
1521 p_cplv_tbl => l_cplv_tbl_in);
1522
1523
1524 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1525 Open cur_get_line_number(l_cle_id, l_chr_id);
1526 fetch cur_get_line_number into l_temp_line_number;
1527 close cur_get_line_number;
1528 fnd_msg_pub.initialize;
1529 OKC_API.SET_MESSAGE(
1530 p_app_name => G_APP_NAME,
1531 p_msg_name =>'OKS_CASCADE_CHANGE_ACCOUNT',
1532 p_token1 =>'TOKEN1' ,
1533 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'),
1534 p_token2 => 'TOKEN2',
1535 p_token2_value => l_temp_party_name,
1536 p_token3 => 'TOKEN3',
1537 p_token3_value => l_temp_line_number);
1538 -- fnd_msg_pub.initialize;
1539 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1540 p_encoded => fnd_api.g_false);
1541 x_msg_tbl(l_tot_msg_count).status := 'S';
1542 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1543 l_tot_msg_count := l_tot_msg_count + 1;
1544 l_msg_data := NULL;
1545 END IF;
1546 ELSE
1547 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1548 Open cur_get_line_number(l_cle_id, l_chr_id);
1549 fetch cur_get_line_number into l_temp_line_number;
1550 close cur_get_line_number;
1551 fnd_msg_pub.initialize;
1552 OKC_API.SET_MESSAGE(
1553 p_app_name => G_APP_NAME,
1554 p_msg_name =>'OKS_CASCADE_CHANGE_ACCOUNT',
1555 p_token1 =>'TOKEN1' ,
1556 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'),
1557 p_token2 => 'TOKEN2',
1558 p_token2_value => l_temp_party_name,
1559 p_token3 => 'TOKEN3',
1560 p_token3_value => l_temp_line_number);
1561 -- fnd_msg_pub.initialize;
1562 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1563 p_encoded => fnd_api.g_false);
1564 x_msg_tbl(l_tot_msg_count).status := 'S';
1565 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1566 l_tot_msg_count := l_tot_msg_count + 1;
1567 l_msg_data := NULL;
1568 END IF;
1569
1570 END IF; -- IF nvl(l_temp_contact_id,0) = 0 THEN
1571 -- Bug 5227077 --
1572 ELSIF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1573 FOR i in 1..fnd_msg_pub.count_msg
1574 Loop
1575 fnd_msg_pub.get
1576 (
1577 p_msg_index => i,
1578 p_encoded => 'F',
1579 p_data => l_msg_data,
1580 p_msg_index_out => l_msg_index
1581 );
1582 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1583 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1584 l_tot_msg_count := l_tot_msg_count + 1;
1585 l_msg_data := NULL;
1586 End Loop;
1587 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1588 END IF;
1589 -- Bug 5227077 --
1590 END IF;
1591 END IF; -- IF l_contact_id is NOT NULL THEN
1592 END IF; -- IF l_cpl_id is NOT NULL THEN
1593 END LOOP; -- CUR_PARTY_REC
1594 END IF;
1595
1596
1597 END IF;
1598 -- IKON ENHANCEMENT --
1599 -- *******************************************************************
1600 -- *******************************************************************
1601 -- IKON ENHANCEMENT --
1602 IF l_header_sca is not null then
1603
1604 Open cur_head_cust_acct(l_okc_headers_rec.ship_to_site_use_id);
1605 fetch cur_head_cust_acct into l_header_cust_acct;
1606 close cur_head_cust_acct;
1607
1608
1609 Open CUR_CUST_ACCT(l_okc_lines_rec.ship_to_site_use_id,'SHIP_TO');
1610 fetch CUR_CUST_ACCT into l_line_cust_acct,l_party_id;
1611 close CUR_CUST_ACCT;
1612
1613
1614
1615 -- CASE I If no STO rule is there then create a BTO rule
1616 -- CASE II If there is a STO RULE then
1617 -- Update the STO rule at lines leve with header
1618 -- level Customer account if the accounts are
1619 -- not same.
1620 -- Delete all contacts which are not
1621 -- billing contact and are belonging to
1622 -- ship to account Party Role
1623 -- CASE I
1624 IF l_header_cust_acct <> nvl(l_line_cust_acct, -1) THEN
1625 -- Create/UPDATE a CAN RULE and Create/UPDATE the BTO Rule --
1626 l_clev_tbl_in(1).id := l_cle_id;
1627 l_clev_tbl_in(1).ship_to_site_use_id := l_okc_headers_rec.ship_to_site_use_id;
1628 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1629
1630 IF l_line_cust_acct IS NULL THEN
1631 fnd_msg_pub.initialize;
1632 OKC_API.SET_MESSAGE(
1633 p_app_name => G_APP_NAME_OKS,
1634 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1635 p_token1 => 'ATTRIBUTE',
1636 p_token1_value => 'Ship To Address');
1637
1638 -- fnd_msg_pub.initialize;
1639 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1640 p_encoded => fnd_api.g_false);
1641 x_msg_tbl(l_tot_msg_count).status := 'S';
1642 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1643 l_tot_msg_count := l_tot_msg_count + 1;
1644 l_msg_data := NULL;
1645 END IF;
1646
1647 fnd_msg_pub.initialize;
1648 OKC_API.SET_MESSAGE(
1649 p_app_name => G_APP_NAME_OKS, -- bug 5468539 G_APP_NAME,
1650 p_msg_name =>'OKS_DEFAULT_ATTR_SUCCESS_NEW',
1651 p_token1 => 'TOKEN1', -- bug 5468539 'RULE',
1652 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_SHIP_TO_CUSTOMER_ACCOUNT'));
1653
1654 -- fnd_msg_pub.initialize;
1655 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1656 p_encoded => fnd_api.g_false);
1657 x_msg_tbl(l_tot_msg_count).status := 'S';
1658 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1659 l_tot_msg_count := l_tot_msg_count + 1;
1660 l_msg_data := NULL;
1661
1662 END IF;
1663
1664 -- CASE II
1665 END IF;
1666 IF l_line_cust_acct is not NULL AND l_header_cust_acct <> l_line_cust_acct THEN
1667 open Get_Party_Id (l_line_cust_acct);
1668 fetch Get_party_id into l_line_party_id,l_temp_party_name;
1669 close get_party_id;
1670
1671 -- DELETE CONTACTS --
1672
1673 FOR CUR_PARTY_REC IN CUR_LINE_PARTY_ROLE(l_chr_id, l_cle_id,l_line_party_id ) LOOP
1674 l_cpl_id := CUR_PARTY_REC.ID;
1675
1676 IF l_cpl_id is NOT NULL THEN
1677 l_contact_id := CHECK_LINE_CONTACT(l_chr_id,l_cpl_id );-- 5485054
1678 IF l_contact_id is NOT NULL THEN
1679 l_contact :=0;
1680 -- When Cascading Bill to Cust Account then Don't check for Shipping Contact
1681 FOR contact_rec in line_contact_jtot(l_cpl_id,l_chr_id,'OKX_CONTBILL') LOOP
1682
1683 Open Get_Contact_Cust_Acct(contact_rec.object1_id1);
1684 fetch Get_Contact_Cust_Acct into l_line_cust_acct;
1685 close Get_Contact_Cust_Acct;
1686 -- Delete the contact when Contact Account Id is not same as
1687 -- header Account Id
1688 IF l_header_cust_acct <> l_line_cust_acct THEN
1689
1690 l_ctcv_tbl_in(l_contact).id := contact_rec.id;
1691 l_ctcv_tbl_in(l_contact).cpl_id := l_cpl_id;
1692 l_ctcv_tbl_in(l_contact).dnz_chr_id := contact_rec.dnz_chr_ID;
1693
1694 l_ctcv_tbl_in(l_contact).cro_code := contact_rec.cro_code;
1695 l_ctcv_tbl_in(l_contact).OBJECT1_ID1 := contact_rec.object1_id1;
1696 l_ctcv_tbl_in(l_contact).object1_id2 := contact_rec.OBJECT1_ID2;
1697 l_ctcv_tbl_in(l_contact).JTOT_OBJECT1_CODE := contact_rec.JTOT_OBJECT1_CODE ;
1698 l_ctcv_tbl_in(l_contact).start_date := contact_rec.START_DATE;
1699 l_ctcv_tbl_in(l_contact).end_date := contact_rec.END_DATE;
1700 l_ctcv_tbl_in(l_contact).attribute_category := contact_rec.ATTRIBUTE_CATEGORY;
1701 l_ctcv_tbl_in(l_contact).attribute1 := contact_rec.ATTRIBUTE1;
1702 l_ctcv_tbl_in(l_contact).attribute2 := contact_rec.ATTRIBUTE2;
1703 l_ctcv_tbl_in(l_contact).attribute3 := contact_rec.ATTRIBUTE3;
1704 l_ctcv_tbl_in(l_contact).attribute4 := contact_rec.ATTRIBUTE4;
1705 l_ctcv_tbl_in(l_contact).attribute5 := contact_rec.ATTRIBUTE5;
1706 l_ctcv_tbl_in(l_contact).attribute6 := contact_rec.ATTRIBUTE6;
1707 l_ctcv_tbl_in(l_contact).attribute7 := contact_rec.ATTRIBUTE7;
1708 l_ctcv_tbl_in(l_contact).attribute8 := contact_rec.ATTRIBUTE8;
1709 l_ctcv_tbl_in(l_contact).attribute9 := contact_rec.ATTRIBUTE9;
1710 l_ctcv_tbl_in(l_contact).attribute10 := contact_rec.ATTRIBUTE10;
1711 l_ctcv_tbl_in(l_contact).attribute11 := contact_rec.ATTRIBUTE11;
1712 l_ctcv_tbl_in(l_contact).attribute12 := contact_rec.ATTRIBUTE12;
1713 l_ctcv_tbl_in(l_contact).attribute13 := contact_rec.ATTRIBUTE13;
1714 l_ctcv_tbl_in(l_contact).attribute14 := contact_rec.ATTRIBUTE14;
1715 l_ctcv_tbl_in(l_contact).attribute15 := contact_rec.ATTRIBUTE15;
1716 l_ctcv_tbl_in(l_contact).object_version_number := contact_rec.object_version_number;
1717 l_ctcv_tbl_in(l_contact).created_by := contact_rec.created_by;
1718 l_ctcv_tbl_in(l_contact).creation_date := contact_rec.creation_date;
1719 l_ctcv_tbl_in(l_contact).last_updated_by := contact_rec.last_updated_by;
1720 l_ctcv_tbl_in(l_contact).last_update_date := contact_rec.last_update_date;
1721 l_ctcv_tbl_in(l_contact).last_update_login := contact_rec.last_update_login;
1722 l_contact := l_contact+1;
1723 END IF ;-- line_contact_cust_acct
1724 END LOOP;
1725
1726 IF l_header_cust_acct <> l_line_cust_acct OR l_contact > 1 THEN
1727 okc_contract_party_pub.delete_contact
1728 (p_api_version => l_api_version,
1729 p_init_msg_list => l_init_msg_list,
1730 x_return_status => l_return_status,
1731 x_msg_count => l_msg_count,
1732 x_msg_data => l_msg_data,
1733 p_ctcv_tbl => l_ctcv_tbl_in
1734 );
1735
1736 -- Delete Party Role --
1737 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1738 -- Check if there is any contaact Present belonging to the party role ---
1739 l_temp_contact_id := CHECK_LINE_CONTACT(l_chr_id,l_cpl_id);-- 5485054
1740 IF nvl(l_temp_contact_id,0) = 0 THEN
1741 -- Open Party_role_csr(l_chr_id,l_cle_id);
1742 -- fetch Party_role_csr into l_party_role;
1743 -- close Party_role_csr;
1744
1745 l_cplv_tbl_in(i).id := l_cpl_id ;--l_party_role.id;
1746 l_cplv_tbl_in(i).cle_id := l_cle_id ;-- l_party_role.cle_id;
1747 l_cplv_tbl_in(i).dnz_chr_id := l_chr_id ; --l_party_role.dnz_chr_id;
1748 okc_contract_party_pub.delete_k_party_role
1749 (p_api_version => l_api_version,
1750 p_init_msg_list => l_init_msg_list,
1751 x_return_status => l_return_status,
1752 x_msg_count => l_msg_count,
1753 x_msg_data => l_msg_data,
1754 p_cplv_tbl => l_cplv_tbl_in);
1755
1756
1757 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1758 Open cur_get_line_number(l_cle_id, l_chr_id);
1759 fetch cur_get_line_number into l_temp_line_number;
1760 close cur_get_line_number;
1761 fnd_msg_pub.initialize;
1762 OKC_API.SET_MESSAGE(
1763 p_app_name => G_APP_NAME,
1764 p_msg_name =>'OKS_CASCADE_CHANGE_ACCOUNT',
1765 p_token1 =>'TOKEN1' ,
1766 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_BILL_TO_CUSTOMER_ACCOUNT'),
1767 p_token2 => 'TOKEN2',
1768 p_token2_value => l_temp_party_name,
1769 p_token3 => 'TOKEN3',
1770 p_token3_value => l_temp_line_number);
1771 -- fnd_msg_pub.initialize;
1772 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1773 p_encoded => fnd_api.g_false);
1774 x_msg_tbl(l_tot_msg_count).status := 'S';
1775 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1776 l_tot_msg_count := l_tot_msg_count + 1;
1777 l_msg_data := NULL;
1778 END IF;
1779 ELSE
1780 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1781 Open cur_get_line_number(l_cle_id, l_chr_id);
1782 fetch cur_get_line_number into l_temp_line_number;
1783 close cur_get_line_number;
1784 fnd_msg_pub.initialize;
1785 OKC_API.SET_MESSAGE(
1786 p_app_name => G_APP_NAME,
1787 p_msg_name =>'OKS_CASCADE_CHANGE_ACCOUNT',
1788 p_token1 =>'TOKEN1' ,
1789 p_token1_value => FND_MESSAGE.GET_STRING('OKS','OKS_SHIP_TO_CUSTOMER_ACCOUNT'),
1790 p_token2 => 'TOKEN2',
1791 p_token2_value => l_temp_party_name,
1792 p_token3 => 'TOKEN3',
1793 p_token3_value => l_temp_line_number);
1794 -- fnd_msg_pub.initialize;
1795 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1796 p_encoded => fnd_api.g_false);
1797 x_msg_tbl(l_tot_msg_count).status := 'S';
1798 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1799 l_tot_msg_count := l_tot_msg_count + 1;
1800 l_msg_data := NULL;
1801 END IF;
1802
1803 END IF; -- IF nvl(l_temp_contact_id,0) = 0 THEN
1804 -- Bug 5227077 --
1805 ELSIF l_return_status <> OKC_API.G_RET_STS_SUCCESS
1806 THEN
1807 FOR i in 1..fnd_msg_pub.count_msg
1808 Loop
1809 fnd_msg_pub.get
1810 (
1811 p_msg_index => i,
1812 p_encoded => 'F',
1813 p_data => l_msg_data,
1814 p_msg_index_out => l_msg_index
1815 );
1816 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1817 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1818 l_tot_msg_count := l_tot_msg_count + 1;
1819 l_msg_data := NULL;
1820 End Loop;
1821 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1822
1823 END IF;
1824 -- Bug 5227077 --
1825 END IF;
1826 END IF; -- IF l_contact_id is NOT NULL THEN
1827 END IF; -- IF l_cpl_id is NOT NULL THEN
1828 END LOOP; -- CUR_PARTY_REC
1829 END IF;
1830
1831
1832 END IF;
1833 -- IKON ENHANCEMENT --
1834 -- *******************************************************************
1835 If (l_header_billto_contact is not null) and (l_billto_id is not null) then -- GC
1836
1837 /*OPEN cur_cust_acct_id(l_chr_id, l_billto_id);
1838 FETCH cur_cust_acct_id INTO l_lov_cust_acct;
1839 CLOSE cur_cust_acct_id; */
1840
1841 Open cur_head_cust_acct(l_okc_headers_rec.bill_to_site_use_id);
1842 fetch cur_head_cust_acct into l_header_cust_acct;
1843 close cur_head_cust_acct;
1844
1845 Open cur_line_cust_acct(l_okc_lines_rec.id);
1846 fetch cur_line_cust_acct into l_line_cust_acct;
1847 close cur_line_cust_acct;
1848
1849
1850 --l_header_cust_acct is the customer account associated with the header level
1851 --l_line_cust_acct is the customer account associated with the contract line
1852
1853 IF ( l_header_cust_acct = l_line_cust_acct) then
1854 Open cur_get_party_id(l_line_cust_acct);
1855 fetch cur_get_party_id into l_party_id;
1856 close cur_get_party_id;
1857
1858 l_cpl_id := CHECK_LINE_PARTY_ROLE(P_DNZ_Chr_Id =>l_chr_id,
1859 P_cle_id =>l_cle_id,
1860 p_party_id => l_party_id);
1861
1862 l_can_id := l_line_cust_acct;
1863
1864 If l_cpl_id IS NULL Then -- GC --00
1865 CREATE_LINE_PARTY_ROLE
1866 (P_DNZ_Chr_Id => l_chr_id ,
1867 P_Cle_Id => l_cle_id,
1868 P_billto_Id => l_billto_id,
1869 p_can_id => l_can_id,
1870 x_cpl_id => l_cpl_id,
1871 x_return_status => l_return_status -- 5219132 --
1872 );
1873 -- Bug 5219132 --
1874
1875 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS)
1876 THEN
1877 FOR i in 1..fnd_msg_pub.count_msg
1878 Loop
1879 fnd_msg_pub.get
1880 (
1881 p_msg_index => i,
1882 p_encoded => 'F',
1883 p_data => l_msg_data,
1884 p_msg_index_out => l_msg_index
1885 );
1886 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1887 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1888 l_tot_msg_count := l_tot_msg_count + 1;
1889 l_msg_data := NULL;
1890 End Loop;
1891 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1892 END IF;
1893
1894 -- Bug 5219132 --
1895 l_contact_id := CHECK_LINE_BILL_CONTACT(l_chr_id,l_cpl_id, 'OKX_CONTBILL', 'CUST_BILLING');-- Bug 5485054
1896
1897 ELSE
1898 l_contact_id := CHECK_LINE_BILL_CONTACT(l_chr_id,l_cpl_id, 'OKX_CONTBILL', 'CUST_BILLING'); -- 5485054
1899 END IF; -- GC --00
1900
1901 IF l_contact_id IS NULL AND l_header_billto_contact IS NOT NULL THEN -- GC-01
1902
1903 l_ctcv_tbl_in(1).cpl_id := l_cpl_id;
1904 l_ctcv_tbl_in(1).dnz_chr_id := l_chr_id ;
1905 l_ctcv_tbl_in(1).cro_code := 'CUST_BILLING';
1906 l_ctcv_tbl_in(1).OBJECT1_ID1 := l_billto_id;
1907 l_ctcv_tbl_in(1).object1_id2 := '#';
1908 --- l_ctcv_tbl_in(1).JTOT_OBJECT1_CODE := cur_header_contact_rec.JTOT_OBJECT1_CODE;
1909 l_ctcv_tbl_in(1).JTOT_OBJECT1_CODE :='OKX_CONTBILL';
1910 okc_contract_party_pub.create_contact
1911 ( p_api_version => l_api_version,
1912 p_init_msg_list => l_init_msg_list,
1913 x_return_status => l_return_status,
1914 x_msg_count => l_msg_count,
1915 x_msg_data => l_msg_data,
1916 p_ctcv_tbl => l_ctcv_tbl_in,
1917 x_ctcv_tbl => l_ctcv_tbl_out);
1918
1919
1920 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1921 -- Bug 5227077 --
1922 FOR i in 1..fnd_msg_pub.count_msg
1923 Loop
1924 fnd_msg_pub.get
1925 (
1926 p_msg_index => i,
1927 p_encoded => 'F',
1928 p_data => l_msg_data,
1929 p_msg_index_out => l_msg_index
1930 );
1931 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1932 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1933 l_tot_msg_count := l_tot_msg_count + 1;
1934 l_msg_data := NULL;
1935 End Loop;
1936 -- Bug 5227077 --
1937 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1938 ELSE
1939 fnd_msg_pub.initialize;
1940 OKC_API.SET_MESSAGE(
1941 p_app_name => G_APP_NAME_OKS,
1942 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1943 p_token1 => 'ATTRIBUTE',
1944 p_token1_value => 'Bill To Contact');
1945 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1946 p_encoded => fnd_api.g_false);
1947 x_msg_tbl(l_tot_msg_count).status := 'S';
1948 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1949 l_tot_msg_count := l_tot_msg_count + 1;
1950 l_msg_data := NULL;
1951 END IF;
1952 ELSIF (l_contact_id IS NOT NULL) AND (l_header_billto_contact IS NOT NULL) THEN
1953 l_ctcv_tbl_in(1).id := l_contact_id;
1954 l_ctcv_tbl_in(1).cpl_id := l_cpl_id;
1955 l_ctcv_tbl_in(1).OBJECT1_ID1 := l_billto_id;
1956 okc_contract_party_pub.update_contact
1957 (p_api_version => l_api_version,
1958 p_init_msg_list => l_init_msg_list,
1959 x_return_status => l_return_status,
1960 x_msg_count => l_msg_count,
1961 x_msg_data => l_msg_data,
1962 p_ctcv_tbl => l_ctcv_tbl_in,
1963 x_ctcv_tbl => l_ctcv_tbl_out);
1964
1965 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1966 -- Bug 5227077 --
1967 FOR i in 1..fnd_msg_pub.count_msg
1968 Loop
1969 fnd_msg_pub.get
1970 (
1971 p_msg_index => i,
1972 p_encoded => 'F',
1973 p_data => l_msg_data,
1974 p_msg_index_out => l_msg_index
1975 );
1976 x_msg_tbl(l_tot_msg_count).status := l_return_status;
1977 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1978 l_tot_msg_count := l_tot_msg_count + 1;
1979 l_msg_data := NULL;
1980 End Loop;
1981 -- Bug 5227077 --
1982 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1983 ELSE
1984 fnd_msg_pub.initialize;
1985 OKC_API.SET_MESSAGE(
1986 p_app_name => G_APP_NAME_OKS,
1987 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
1988 p_token1 => 'ATTRIBUTE',
1989 p_token1_value => 'Bill To Contact');
1990 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
1991 p_encoded => fnd_api.g_false);
1992 x_msg_tbl(l_tot_msg_count).status := 'S';
1993 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
1994 l_tot_msg_count := l_tot_msg_count + 1;
1995 l_msg_data := NULL;
1996
1997 END IF;
1998
1999
2000 END IF; --End GC-01
2001
2002 ELSIF(l_header_cust_acct <> l_line_cust_acct)
2003 THEN
2004 fnd_msg_pub.initialize;
2005 OKC_API.SET_MESSAGE(
2006 p_app_name => G_APP_NAME,
2007 p_msg_name => 'OKS_CASCADE_BTOACCT_MISMATCH',
2008 p_token1 => 'Line No ',
2009 p_token1_value => l_line_number);
2010
2011 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2012 p_encoded => fnd_api.g_false);
2013
2014 x_msg_tbl(l_tot_msg_count).status := 'W'; -- Bug 5219132
2015 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2016 l_tot_msg_count := l_tot_msg_count + 1;
2017 l_msg_data := NULL;
2018
2019 END IF; -- l_header_cust_acct
2020
2021 END IF; --GC
2022
2023 --l_line_tax_status := 'Z'; --BUG#4089834 hkamdar
2024 l_line_tax_status := l_oks_lines_rec.tax_status;
2025
2026 IF l_header_tax IS NOT NULL OR l_header_exception_number IS NOT NULL THEN
2027 -- IKON ENHANCEMENT --
2028 Open cur_head_cust_acct(l_okc_headers_rec.bill_to_site_use_id);
2029 fetch cur_head_cust_acct into l_header_cust_acct;
2030 close cur_head_cust_acct;
2031 l_line_cust_acct := l_okc_lines_rec.cust_acct_id;
2032 -- IKON ENHANCEMENT --
2033 IF l_header_tax IS NOT NULL THEN
2034 --l_insupd_flag := TRUE;
2035 l_status_flag := TRUE;
2036 --Fixed bug#4026268 --gbgupta
2037 --IF l_oks_headers_rec.tax_status = 'R' THEN
2038 --l_oks_headers_rec.tax_code := l_header_tax_code;
2039
2040 --ELSIF l_oks_headers_rec.tax_status = 'E' THEN
2041
2042 --IF l_oks_headers_rec.tax_status = 'E' THEN
2043 IF l_oks_headers_rec.tax_status = 'E' THEN
2044
2045 l_klnv_tbl_in(1).tax_code := NULL;
2046 -- Ebtax --
2047 /*
2048 IF l_header_exception_number IS NULL THEN
2049 l_line_tax_status := l_oks_lines_rec.tax_status;
2050 IF NVL(l_line_tax_status,'X') = 'E' THEN --hkamdar added NVL BUG# 4089834
2051 -- l_insupd_flag := FALSE;
2052 l_status_flag := FALSE;
2053 END IF;
2054 END IF; -- For l_header_exception_number
2055 */
2056 -- 12/1/2005 --
2057 --GCHADHA --
2058 /*
2059 -- When the Tax Status is Exempt then Tax Classification Code should
2060 -- be Nullified.
2061 l_klnv_tbl_in(1).tax_classification_code := NULL;
2062 -- Ebtax --
2063 */
2064 -- END GCHADHA --
2065 END IF;
2066 ELSIF l_header_exception_number IS NOT NULL THEN
2067 l_line_tax_status := l_oks_lines_rec.tax_status;
2068 --Fixed Tax exemption is cascading to line along with bug#4026268 --gbgupta
2069 --Checking else part first. so that it can handle null tax status
2070 /*
2071 IF l_line_tax_status IN('R','S') THEN
2072 l_insupd_flag := FALSE;
2073 ELSE
2074 l_insupd_flag := TRUE;
2075 END IF;
2076 */
2077 IF NVL(l_line_tax_status,'X') = 'E' THEN--hkamdar added NVL BUG# 4089834
2078 --l_insupd_flag := TRUE;
2079 l_exmpt_num_flag := TRUE;
2080 ELSE
2081 --l_insupd_flag := FALSE;
2082 l_exmpt_num_flag := FALSE;
2083 END IF;
2084 ELSE
2085 --l_insupd_flag := FALSE;
2086 l_status_flag := FALSE;
2087 l_exmpt_num_flag := FALSE;
2088 END IF; --l_header_tax
2089
2090 -- IF l_insupd_flag THEN
2091 -- IKON ENHANCEMENT --
2092 IF l_header_cust_acct <> l_line_cust_acct AND l_oks_headers_rec.tax_status = 'E'
2093 AND l_header_bca is NULL -- 5/30/2005
2094 THEN
2095 l_status_flag := FALSE;
2096 l_exmpt_num_flag := FALSE;
2097 END IF ;
2098 -- IKON ENHANCEMENT --
2099
2100 IF l_status_flag or l_exmpt_num_flag THEN
2101
2102 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
2103 --Fixed bug#4026268 --gbgupta
2104 -- l_klnv_tbl_in(1).tax_code := l_oks_headers_rec.tax_code;
2105 --BUG# 4089834 HKAMDAR
2106 -- IKON ENHANCEMENT --
2107 -- if l_status_flag then
2108 If l_status_flag then
2109 -- IKON ENHANCEMENT --
2110 l_klnv_tbl_in(1).tax_status := l_oks_headers_rec.tax_status;
2111 -- If l_header_tax_code is not null THEN
2112 If l_header_tax_cls_code is not null THEN
2113 -- ebtax --
2114 l_klnv_tbl_in(1).tax_classification_code := l_header_tax_cls_code;
2115 l_klnv_tbl_in(1).tax_code := NULL;
2116 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2117 -- l_klnv_tbl_in(1).exempt_certificate_number := NULL;
2118 -- l_klnv_tbl_in(1).exempt_reason_code := NULL;
2119
2120 ELSE
2121 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2122 -- l_klnv_tbl_in(1).exempt_certificate_number := NULL;
2123 -- l_klnv_tbl_in(1).exempt_reason_code := NULL;
2124 End if;
2125 -- Ebtax --
2126 If NVL(l_oks_headers_rec.tax_status, 'X') = 'E' THEN
2127 -- Ebtax --
2128 -- l_klnv_tbl_in(1).tax_exemption_id := l_oks_headers_rec.tax_exemption_id;
2129 IF l_oks_headers_rec.tax_exemption_id IS NOT NULL
2130 THEN
2131
2132 OPEN tax_exemption_number_csr(l_oks_headers_rec.tax_exemption_id);
2133 FETCH tax_exemption_number_csr INTO tax_exemption_number_rec;
2134 CLOSE tax_exemption_number_csr;
2135 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2136 l_klnv_tbl_in(1).exempt_reason_code := tax_exemption_number_rec.description;
2137 l_klnv_tbl_in(1).exempt_certificate_number := tax_exemption_number_rec.customer_exception_number;
2138
2139
2140 ELSIF l_oks_headers_rec.exempt_certificate_number is NOT NULL
2141 THEN
2142 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2143 l_klnv_tbl_in(1).exempt_reason_code := l_oks_headers_rec.exempt_reason_code;
2144 l_klnv_tbl_in(1).exempt_certificate_number := l_oks_headers_rec.exempt_certificate_number;
2145 END IF;
2146 -- Ebtax --
2147
2148 End if;
2149 End if;
2150 -- IKON ENHANCEMENT --
2151 IF l_exmpt_num_flag then
2152 -- IKON ENHANCEMENT --
2153
2154 -- Ebtax --
2155 -- l_klnv_tbl_in(1).tax_exemption_id := l_oks_headers_rec.tax_exemption_id;
2156 IF l_oks_headers_rec.tax_exemption_id IS NOT NULL
2157 THEN
2158 OPEN tax_exemption_number_csr(l_oks_headers_rec.tax_exemption_id);
2159 FETCH tax_exemption_number_csr INTO tax_exemption_number_rec;
2160 CLOSE tax_exemption_number_csr;
2161 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2162 l_klnv_tbl_in(1).exempt_reason_code := tax_exemption_number_rec.description;
2163 l_klnv_tbl_in(1).exempt_certificate_number := tax_exemption_number_rec.customer_exception_number;
2164 ELSIF l_oks_headers_rec.exempt_certificate_number is NOT NULL
2165 THEN
2166 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2167 l_klnv_tbl_in(1).exempt_reason_code := l_oks_headers_rec.exempt_reason_code;
2168 l_klnv_tbl_in(1).exempt_certificate_number := l_oks_headers_rec.exempt_certificate_number;
2169 END IF;
2170 -- Ebtax --
2171 end if;
2172 /*hkamdar
2173 IF NVL(l_oks_headers_rec.tax_status, 'X') = 'E' THEN
2174 l_klnv_tbl_in(1).tax_code := NULL;
2175 END IF;
2176 l_klnv_tbl_in(1).tax_status := l_oks_headers_rec.tax_status;
2177 --Fixed Tax exemption is cascading to line along with bug#4026268 --gbgupta
2178 --assigning tax exemption id
2179 l_klnv_tbl_in(1).tax_exemption_id := l_oks_headers_rec.tax_exemption_id;
2180 */
2181 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2182 fnd_msg_pub.initialize;
2183 OKC_API.SET_MESSAGE(
2184 p_app_name => G_APP_NAME_OKS,
2185 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2186 p_token1 => 'ATTRIBUTE',
2187 p_token1_value => 'Tax');
2188 --fnd_msg_pub.initialize;
2189 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2190 p_encoded => fnd_api.g_false);
2191 x_msg_tbl(l_tot_msg_count).status := 'S';
2192 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2193 l_tot_msg_count := l_tot_msg_count + 1;
2194 l_msg_data := NULL;
2195
2196 END IF;
2197 END IF;-- For l_insupd_flag
2198 END IF; -- For l_header_tax
2199
2200 --Fixed bug#4026268 --gbgupta
2201 IF l_header_tax_code IS NOT NULL THEN
2202 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
2203 -- IF NVL(l_line_tax_status,'X') <> 'E' AND l_exmpt_num_flag THEN --BUG#4089834 hkamdar
2204
2205 IF l_status_flag AND (NVL(l_oks_headers_rec.tax_status, 'X') = 'E') THEN
2206 l_klnv_tbl_in(1).tax_code := NULL;
2207 -- GCHADHA --
2208 -- l_klnv_tbl_in(1).tax_classification_code := NULL;
2209 -- GCHADHA --
2210 -- Ebtax --
2211 -- l_klnv_tbl_in(1).tax_exemption_id := l_oks_headers_rec.tax_exemption_id;
2212 IF l_oks_headers_rec.tax_exemption_id IS NOT NULL
2213 THEN
2214
2215 OPEN tax_exemption_number_csr(l_oks_headers_rec.tax_exemption_id);
2216 FETCH tax_exemption_number_csr INTO tax_exemption_number_rec;
2217 CLOSE tax_exemption_number_csr;
2218 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2219 l_klnv_tbl_in(1).exempt_reason_code := tax_exemption_number_rec.description;
2220 l_klnv_tbl_in(1).exempt_certificate_number := tax_exemption_number_rec.customer_exception_number;
2221
2222
2223 ELSIF l_oks_headers_rec.exempt_certificate_number is NOT NULL
2224 THEN
2225 l_klnv_tbl_in(1).tax_exemption_id := NULL;
2226 l_klnv_tbl_in(1).exempt_reason_code := l_oks_headers_rec.exempt_reason_code;
2227 l_klnv_tbl_in(1).exempt_certificate_number := l_oks_headers_rec.exempt_certificate_number;
2228 END IF;
2229 -- Ebtax --
2230 ELSE
2231 -- IF NVL(l_line_tax_status,'X') <> 'E' THEN -- GCHADHA 12/1/2005
2232 -- l_klnv_tbl_in(1).tax_code := l_header_tax_code_id;
2233 -- Ebtax --
2234 l_klnv_tbl_in(1).tax_classification_code := l_header_tax_cls_code;
2235 -- Ebtax --
2236
2237 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2238 fnd_msg_pub.initialize;
2239 OKC_API.SET_MESSAGE(
2240 p_app_name => G_APP_NAME_OKS,
2241 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2242 p_token1 => 'ATTRIBUTE',
2243 p_token1_value => 'Tax Code');
2244 --fnd_msg_pub.initialize;
2245 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2246 p_encoded => fnd_api.g_false);
2247 x_msg_tbl(l_tot_msg_count).status := 'S';
2248 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2249 l_tot_msg_count := l_tot_msg_count + 1;
2250 l_msg_data := NULL;
2251
2252 END IF;
2253 -- END IF; -- Gchadha 12/1/2005
2254 END IF;-- BUG#4089834 hkamdar
2255 END IF;
2256
2257 IF l_billing_profile IS NOT NULL THEN
2258 IF l_billing_profile_id IS NOT NULL THEN
2259 --Fix for Bug# 3542273
2260 OPEN cur_billing_profile(l_billing_profile_id);
2261 FETCH cur_billing_profile INTO l_bpf_acct_rule_id,l_bpf_invoice_rule_id;
2262 IF cur_billing_profile%NOTFOUND then
2263 CLOSE cur_billing_profile;
2264 x_return_status := 'E';
2265 RAISE G_EXCEPTION_HALT_VALIDATION;
2266 ELSE
2267 CLOSE cur_billing_profile;
2268 END IF;
2269
2270 If l_okc_lines_rec.lse_id = '12' then
2271
2272 l_usage_type := l_oks_lines_rec.usage_type;
2273
2274 If l_usage_type in ('VRT','QTY') then
2275 IF l_bpf_invoice_rule_id = -2 THEN
2276 l_token1_value := fnd_meaning(l_usage_type,'OKS_USAGE_TYPE');
2277
2278 fnd_msg_pub.initialize;
2279 OKC_API.SET_MESSAGE
2280 (
2281 p_app_name => G_APP_NAME_OKS,
2282 p_msg_name => 'OKS_USAGE_ATTR_CHECK',
2283 p_token1 => 'TOKEN1',
2284 p_token1_value => l_token1_value,
2285 p_token2 => 'Line No ',
2286 p_token2_value => l_line_number
2287 );
2288
2289 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2290 p_encoded => fnd_api.g_false);
2291 x_msg_tbl(l_tot_msg_count).status := 'E';
2292 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2293 l_tot_msg_count := l_tot_msg_count + 1;
2294 l_msg_data := NULL;
2295 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2296 End If;
2297 End If;
2298 End If;
2299 --Fix for Bug# 3542273
2300 OPEN cur_line_dates(header_lines_tbl(i).cle_id);
2301 FETCH cur_line_dates INTO l_bpf_start_date, l_bpf_end_Date, l_bpf_lse_id;
2302 CLOSE cur_line_dates;
2303
2304 OPEN cur_sub_line(l_cle_id);
2305 FETCH cur_sub_line INTO l_sub_line_rec;
2306 CLOSE cur_sub_line;
2307
2308 If NOT oks_extwar_util_pvt.check_already_billed(
2309 p_chr_id => null,
2310 p_cle_id => header_lines_tbl(i).cle_id,
2311 p_lse_id => l_bpf_lse_id,
2312 p_end_date => null
2313 )
2314
2315 THEN
2316
2317 l_rec.start_date := l_bpf_start_date;
2318 l_rec.end_date := l_bpf_end_Date;
2319 l_rec.cle_Id := header_lines_tbl(i).cle_id;
2320 l_rec.chr_Id := header_lines_tbl(i).chr_id;
2321 l_rec.Billing_Profile_Id := header_lines_tbl(i).billing_profile_id;
2322
2323 OKS_BILLING_PROFILES_PUB.Get_Billing_Schedule
2324 (p_api_version => 1.0,
2325 p_init_msg_list =>'T',
2326 p_billing_profile_rec => l_rec,
2327 x_sll_tbl_out => l_sll_tbl_out,
2328 x_return_status => l_return_status,
2329 x_msg_count => l_msg_count,
2330 x_msg_data => l_msg_data);
2331
2332 -- Bug 5227077 --
2333 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2334 FOR i in 1..fnd_msg_pub.count_msg
2335 Loop
2336 fnd_msg_pub.get
2337 (
2338 p_msg_index => i,
2339 p_encoded => 'F',
2340 p_data => l_msg_data,
2341 p_msg_index_out => l_msg_index
2342 );
2343 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2344 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2345 l_tot_msg_count := l_tot_msg_count + 1;
2346 l_msg_data := NULL;
2347 End Loop;
2348 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2349 END IF;
2350 -- Bug 5227077 --
2351
2352
2353 -- Delete_Sll_If_Exists(l_rec.cle_id);
2354 l_top_line_id := l_rec.cle_id;
2355
2356 OKS_BILL_SCH.Del_rul_elements
2357 (
2358 p_top_line_id => l_top_line_id,
2359 x_return_status => l_return_status,
2360 x_msg_count => l_msg_count,
2361 x_msg_data => l_msg_data
2362 );
2363
2364 -- Bug 5227077 --
2365 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2366 FOR i in 1..fnd_msg_pub.count_msg
2367 Loop
2368 fnd_msg_pub.get
2369 (
2370 p_msg_index => i,
2371 p_encoded => 'F',
2372 p_data => l_msg_data,
2373 p_msg_index_out => l_msg_index
2374 );
2375 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2376 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2377 l_tot_msg_count := l_tot_msg_count + 1;
2378 l_msg_data := NULL;
2379 End Loop;
2380 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2381 END IF;
2382 -- Bug 5227077 --
2383
2384
2385
2386 l_invoice_rule_id := l_bpf_invoice_rule_id;
2387
2388 --new
2389 l_clev_tbl_in(1).inv_rule_id := l_invoice_rule_id;
2390 --new
2391
2392 l_klnv_tbl_in(1).acct_rule_id := l_bpf_acct_rule_id;
2393 l_sll_tbl(1).Sequence_no := l_sll_tbl_out(1).seq_no;
2394 l_sll_tbl(1).uom_code := l_sll_tbl_out(1).timeunit;
2395 l_sll_tbl(1).uom_per_period := l_sll_tbl_out(1).duration;
2396 l_sll_tbl(1).level_periods := l_sll_tbl_out(1).target_quantity;
2397 l_sll_tbl(1).invoice_offset_days := l_sll_tbl_out(1).Invoice_Offset;
2398 l_sll_tbl(1).interface_offset_days := l_sll_tbl_out(1).Interface_Offset;
2399 -- Bug 5406141 --
2400 -- l_sll_tbl(1).Chr_Id := l_sll_tbl_out(1).Chr_Id;
2401 l_sll_tbl(1).Cle_Id := l_sll_tbl_out(1).Cle_Id;
2402 l_billing_type := l_sll_tbl_out(1).billing_type;
2403 -- Bug 5406141 --
2404
2405 OKS_BILL_SCH.Create_Bill_Sch_Rules
2406 (
2407 p_billing_type => l_billing_type
2408 ,p_sll_tbl => l_sll_tbl
2409 ,p_invoice_rule_id => l_invoice_rule_id
2410 ,x_bil_sch_out_tbl => l_bil_sch_out_tbl
2411 ,x_return_status => l_return_status
2412 );
2413
2414 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2415 FOR i in 1..fnd_msg_pub.count_msg
2416 Loop
2417 fnd_msg_pub.get
2418 (
2419 p_msg_index => i,
2420 p_encoded => 'F',
2421 p_data => l_msg_data,
2422 p_msg_index_out => l_msg_index
2423 );
2424 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2425 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2426 l_tot_msg_count := l_tot_msg_count + 1;
2427 l_msg_data := NULL;
2428 End Loop;
2429 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2430 ELSE
2431 fnd_msg_pub.initialize;
2432 OKC_API.SET_MESSAGE(
2433 p_app_name => G_APP_NAME_OKS,
2434 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2435 p_token1 => 'ATTRIBUTE',
2436 p_token1_value => 'Billing Schedule');
2437 --fnd_msg_pub.initialize;
2438 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2439 p_encoded => fnd_api.g_false);
2440 x_msg_tbl(l_tot_msg_count).status := 'S';
2441 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2442 l_tot_msg_count := l_tot_msg_count + 1;
2443 l_msg_data := NULL;
2444
2445 END IF;
2446
2447 END IF; --for check already billed
2448
2449 Open cur_bill_to_address_id1(l_billing_profile_id);
2450 fetch cur_bill_to_address_id1 into l_bill_to_address_id1;
2451 close cur_bill_to_address_id1;
2452
2453 IF (l_bill_to_address_id1 IS NOT NULL) THEN
2454
2455 l_cle_Id := header_lines_tbl(i).cle_id;
2456 l_chr_Id := header_lines_tbl(i).chr_id;
2457 l_line_cust_acct := l_okc_headers_rec.cust_acct_id;
2458
2459 Open cur_bpf_cust_acct(l_billing_profile_id, header_dates_rec.authoring_org_id);
2460 fetch cur_bpf_cust_acct into l_bpf_cust_acct;
2461 close cur_bpf_cust_acct;
2462
2463 IF l_line_cust_acct = l_bpf_cust_acct THEN
2464
2465 l_clev_tbl_in(1).id := l_cle_id;
2466 l_clev_tbl_in(1).bill_to_site_use_id := l_okc_headers_rec.bill_to_site_use_id;
2467
2468 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2469 fnd_msg_pub.initialize;
2470 OKC_API.SET_MESSAGE(
2471 p_app_name => G_APP_NAME_OKS,
2472 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2473 p_token1 => 'ATTRIBUTE',
2474 p_token1_value => 'Bill To Address');
2475 --fnd_msg_pub.initialize;
2476 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2477 p_encoded => fnd_api.g_false);
2478 x_msg_tbl(l_tot_msg_count).status := 'S';
2479 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2480 l_tot_msg_count := l_tot_msg_count + 1;
2481 l_msg_data := NULL;
2482
2483 END IF;
2484 END IF;
2485 END IF;
2486 Elsif l_billing_profile_id is NULL then
2487 --Display error message, saying required value missing.
2488 fnd_msg_pub.initialize;
2489 OKC_API.SET_MESSAGE(
2490 p_app_name => G_APP_NAME_OKS,
2491 p_msg_name => 'OKS_CASCADE_MISSING_REQD_ATTR',
2492 p_token1 => 'ATTRIBUTE',
2493 p_token1_value => 'Billing Profile');
2494
2495 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2496 p_encoded => fnd_api.g_false);
2497 x_msg_tbl(l_tot_msg_count).status := 'E';
2498 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2499 l_tot_msg_count := l_tot_msg_count + 1;
2500 l_msg_data := NULL;
2501
2502 END IF; --l_billing_profile_id
2503 END IF;--l_billing_profile
2504
2505 ---------------------------------------------------------------------------------
2506 IF l_header_arl is not null then
2507 IF l_billing_profile IS NOT NULL and
2508 l_billing_profile_id is not null THEN
2509
2510 l_klnv_tbl_in(1).acct_rule_id :=l_bpf_acct_rule_id;
2511 ELSE
2512 l_klnv_tbl_in(1).acct_rule_id := l_oks_headers_rec.acct_rule_id;
2513 END IF;
2514 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
2515
2516 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2517 fnd_msg_pub.initialize;
2518 OKC_API.SET_MESSAGE(
2519 p_app_name => G_APP_NAME_OKS,
2520 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2521 p_token1 => 'ATTRIBUTE',
2522 p_token1_value => 'Accounting Rule');
2523
2524 --fnd_msg_pub.initialize;
2525 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2526 p_encoded => fnd_api.g_false);
2527 x_msg_tbl(l_tot_msg_count).status := 'S';
2528 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2529 l_tot_msg_count := l_tot_msg_count + 1;
2530 l_msg_data := NULL;
2531
2532 END IF;
2533
2534 ELSIF l_header_arl is NULL AND l_billing_profile IS NOT NULL and
2535 l_billing_profile_id is not null then
2536
2537 --Fix for Bug# 3542273
2538
2539 If l_okc_lines_rec.lse_id = '12' then
2540
2541 l_usage_type := l_oks_lines_rec.usage_type;
2542
2543 If l_usage_type in ('VRT','QTY') then
2544 IF l_bpf_invoice_rule_id = -2 THEN
2545 l_token1_value := fnd_meaning(l_usage_type,'OKS_USAGE_TYPE');
2546
2547 fnd_msg_pub.initialize;
2548 OKC_API.SET_MESSAGE
2549 (
2550 p_app_name => G_APP_NAME_OKS,
2551 p_msg_name => 'OKS_USAGE_ATTR_CHECK',
2552 p_token1 => 'TOKEN1',
2553 p_token1_value => l_token1_value,
2554 p_token2 => 'Line No ',
2555 p_token2_value => l_line_number
2556 );
2557
2558 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2559 p_encoded => fnd_api.g_false);
2560 x_msg_tbl(l_tot_msg_count).status := 'E';
2561 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2562 l_tot_msg_count := l_tot_msg_count + 1;
2563 l_msg_data := NULL;
2564 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2565 End If;
2566 End If;
2567 End If;
2568 --Fix for Bug# 3542273
2569
2570 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
2571 l_klnv_tbl_in(1).acct_rule_id := l_bpf_acct_rule_id;
2572
2573 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2574 fnd_msg_pub.initialize;
2575 OKC_API.SET_MESSAGE(
2576 p_app_name => G_APP_NAME_OKS,
2577 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2578 p_token1 => 'ATTRIBUTE',
2579 p_token1_value => 'Accounting Rule');
2580
2581 --fnd_msg_pub.initialize;
2582 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2583 p_encoded => fnd_api.g_false);
2584 x_msg_tbl(l_tot_msg_count).status := 'S';
2585 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2586 l_tot_msg_count := l_tot_msg_count + 1;
2587 l_msg_data := NULL;
2588 END IF;
2589 END IF;
2590
2591
2592 -----------------------------------------------------------------------
2593 IF l_header_ire is not null then
2594 --Fix for Bug# 3542273
2595 IF l_billing_profile IS NOT NULL and
2596 l_billing_profile_id is not null THEN
2597
2598 l_clev_tbl_in(1).inv_rule_id :=l_invoice_rule_id;
2599 ELSE
2600 l_clev_tbl_in(1).inv_rule_id := l_okc_headers_rec.inv_rule_id;
2601 END IF;
2602
2603 If l_okc_lines_rec.lse_id = '12' then
2604
2605 l_usage_type := l_oks_lines_rec.usage_type;
2606
2607 If l_usage_type in ('VRT','QTY') then
2608 If l_clev_tbl_in(1).inv_rule_id = -2 then
2609 l_token1_value := fnd_meaning(l_usage_type,'OKS_USAGE_TYPE');
2610
2611 fnd_msg_pub.initialize;
2612 OKC_API.SET_MESSAGE
2613 (
2614 p_app_name => G_APP_NAME_OKS,
2615 p_msg_name => 'OKS_USAGE_ATTR_CHECK',
2616 p_token1 => 'TOKEN1',
2617 p_token1_value => l_token1_value,
2618 p_token2 => 'Line No ',
2619 p_token2_value => l_line_number
2620 );
2621
2622 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2623 p_encoded => fnd_api.g_false);
2624 x_msg_tbl(l_tot_msg_count).status := 'E';
2625 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2626 l_tot_msg_count := l_tot_msg_count + 1;
2627 l_msg_data := NULL;
2628 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2629 End If;
2630 End If;
2631 End If;
2632 --Fix for Bug# 3542273
2633
2634 l_clev_tbl_in(1).id := l_cle_id;
2635
2636 OKS_BILL_SCH.update_bs_interface_date
2637 (
2638 p_top_line_id => l_cle_id,
2639 p_invoice_rule_id => l_clev_tbl_in(1).inv_rule_id,
2640 x_return_status => l_return_status,
2641 x_msg_count => l_msg_count,
2642 x_msg_data => l_msg_data
2643 );
2644
2645 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2646 fnd_msg_pub.initialize;
2647 OKC_API.SET_MESSAGE(
2648 p_app_name => G_APP_NAME_OKS,
2649 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2650 p_token1 => 'ATTRIBUTE',
2651 p_token1_value => 'Invoicing Rule');
2652
2653 --fnd_msg_pub.initialize;
2654 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2655 p_encoded => fnd_api.g_false);
2656 x_msg_tbl(l_tot_msg_count).status := 'S';
2657 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2658 l_tot_msg_count := l_tot_msg_count + 1;
2659 l_msg_data := NULL;
2660 -- Bug 5227077 --
2661 Elsif NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2662 FOR i in 1..fnd_msg_pub.count_msg
2663 Loop
2664 fnd_msg_pub.get
2665 (
2666 p_msg_index => i,
2667 p_encoded => 'F',
2668 p_data => l_msg_data,
2669 p_msg_index_out => l_msg_index
2670 );
2671 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2672 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2673 l_tot_msg_count := l_tot_msg_count + 1;
2674 l_msg_data := NULL;
2675 End Loop;
2676 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2677 END IF;
2678 -- Bug 5227077 --
2679
2680 ELSIF l_header_ire is NULL AND l_billing_profile IS NOT NULL and
2681 l_billing_profile_id is not null then
2682 --Fix for Bug# 3542273
2683
2684 If l_okc_lines_rec.lse_id = '12' then
2685
2686 l_usage_type := l_oks_lines_rec.usage_type;
2687
2688 If l_usage_type in ('VRT','QTY') then
2689 IF l_bpf_invoice_rule_id = -2 THEN
2690 l_token1_value := fnd_meaning(l_usage_type,'OKS_USAGE_TYPE');
2691 fnd_msg_pub.initialize;
2692 OKC_API.SET_MESSAGE
2693 (
2694 p_app_name => G_APP_NAME_OKS,
2695 p_msg_name => 'OKS_USAGE_ATTR_CHECK',
2696 p_token1 => 'TOKEN1',
2697 p_token1_value => l_token1_value,
2698 p_token2 => 'Line No ',
2699 p_token2_value => l_line_number
2700 );
2701 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2702 p_encoded => fnd_api.g_false);
2703 x_msg_tbl(l_tot_msg_count).status := 'E';
2704 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2705 l_tot_msg_count := l_tot_msg_count + 1;
2706 l_msg_data := NULL;
2707 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2708 End If;
2709 End If;
2710 End If;
2711 --Fix for Bug# 3542273
2712 l_clev_tbl_in(1).id := l_cle_id;
2713 -- changed for Bug 4064138
2714 -- for billed contractflow dosenot go inside check if billed
2715 -- hence the value for l_invoice_rule_id dosenot get assigned.
2716 l_clev_tbl_in(1).inv_rule_id :=l_bpf_invoice_rule_id; --l_invoice_rule_id;
2717 -- change for bug 4064138.
2718
2719 OKS_BILL_SCH.update_bs_interface_date
2720 (
2721 p_top_line_id => l_cle_id,
2722 p_invoice_rule_id => l_clev_tbl_in(1).inv_rule_id,
2723 x_return_status => l_return_status,
2724 x_msg_count => l_msg_count,
2725 x_msg_data => l_msg_data
2726 );
2727 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2728 FOR i in 1..fnd_msg_pub.count_msg
2729 Loop
2730 fnd_msg_pub.get
2731 (
2732 p_msg_index => i,
2733 p_encoded => 'F',
2734 p_data => l_msg_data,
2735 p_msg_index_out => l_msg_index
2736 );
2737 x_msg_tbl(l_tot_msg_count).status := l_return_status;
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 End Loop;
2742 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2743
2744 ELSE
2745 fnd_msg_pub.initialize;
2746 OKC_API.SET_MESSAGE(
2747 p_app_name => G_APP_NAME_OKS,
2748 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2749 p_token1 => 'ATTRIBUTE',
2750 p_token1_value => 'Invoicing Rule');
2751
2752 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2753 p_encoded => fnd_api.g_false);
2754 x_msg_tbl(l_tot_msg_count).status := 'S';
2755 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2756 l_tot_msg_count := l_tot_msg_count + 1;
2757 l_msg_data := NULL;
2758 END IF;
2759 END IF;
2760
2761 If l_header_sales_credits is not null then
2762 --Fetch was doing only for one line. Now the Fetch is in a loop(Bug#2374793)
2763 l_sc_index := 1;
2764 l_scrv_tbl_in.DELETE;
2765
2766
2767 OPEN Scredit_csr_line(l_chr_id,L_cle_id);
2768 LOOP
2769 FETCH Scredit_csr_line into l_scredit_count,l_scredit_id;
2770
2771 EXIT WHEN Scredit_csr_line%NOTFOUND;
2772 l_scrv_tbl_in(l_sc_index).id := l_scredit_id;
2773
2774 l_sc_index := l_sc_index + 1;
2775 END LOOP;
2776 CLOSE Scredit_csr_line;
2777
2778 IF l_scrv_tbl_in.COUNT > 0 THEN
2779 OKS_SALES_CREDIT_PUB.delete_Sales_credit
2780 (
2781 p_api_version => 1.0,
2782 p_init_msg_list => 'T',
2783 x_return_status => l_return_status,
2784 x_msg_count => l_msg_count,
2785 x_msg_data => l_msg_data,
2786 p_scrv_tbl => l_scrv_tbl_in);
2787 -- Bug 527077 --
2788 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2789 FOR i in 1..fnd_msg_pub.count_msg
2790 Loop
2791 fnd_msg_pub.get
2792 (
2793 p_msg_index => i,
2794 p_encoded => 'F',
2795 p_data => l_msg_data,
2796 p_msg_index_out => l_msg_index
2797 );
2798 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2799 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2800 l_tot_msg_count := l_tot_msg_count + 1;
2801 l_msg_data := NULL;
2802 End Loop;
2803 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2804 End if;
2805 -- Bug 5227077 --
2806
2807 END IF; -- End if for plsql table count.
2808
2809 OKS_EXTWAR_UTIL_PVT.CREATE_SALES_CREDITS
2810 (
2811 P_header_id =>l_chr_id
2812 ,P_line_id => l_cle_id
2813 ,X_return_status => l_return_status
2814 );
2815
2816
2817 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2818 fnd_msg_pub.initialize;
2819 OKC_API.SET_MESSAGE(
2820 p_app_name => G_APP_NAME_OKS,
2821 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
2822 p_token1 => 'ATTRIBUTE',
2823 p_token1_value => 'Sales Credit');
2824
2825 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
2826 p_encoded => fnd_api.g_false);
2827 x_msg_tbl(l_tot_msg_count).status := 'S';
2828 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2829 l_tot_msg_count := l_tot_msg_count + 1;
2830 l_msg_data := NULL;
2831 -- Bug 5227077 --
2832 Elsif not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2833 FOR i in 1..fnd_msg_pub.count_msg
2834 Loop
2835 fnd_msg_pub.get
2836 (
2837 p_msg_index => i,
2838 p_encoded => 'F',
2839 p_data => l_msg_data,
2840 p_msg_index_out => l_msg_index
2841 );
2842 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2843 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2844 l_tot_msg_count := l_tot_msg_count + 1;
2845 l_msg_data := NULL;
2846 End Loop;
2847 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2848 End if;
2849 -- Bug 5227077 --
2850
2851 END IF;
2852
2853 -- Made changes for bug 4394382 --
2854 -- When we cascade payment method from header to line in case of Commitment number
2855 -- verify the bill to cust Account on header and lines level. If same then cascaded
2856 -- the commitment number.
2857
2858 IF l_payment_method IS NOT NULL THEN
2859
2860 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
2861 -- l_klnv_tbl_in(1).payment_type := l_oks_headers_rec.payment_type; -- BUG 4394382
2862
2863 -- BUG 3691147 AND 3675745 --
2864 -- PROVIDE FIX FOR ISSUE 3675745 AS WELL --
2865 -- GCHADHA 15 - JUN - 2004 --
2866 l_klnv_tbl_in(1).cust_po_number := l_okc_headers_rec.cust_po_number;
2867 l_klnv_tbl_in(1).cust_po_number_req_yn := l_okc_headers_rec.cust_po_number_req_yn;
2868 -- Bank Account Consolidation --
2869 l_clev_tbl_in(1).id := l_cle_id;
2870 l_clev_tbl_in(1).payment_instruction_type := l_okc_headers_rec.payment_instruction_type;
2871 -- Bank Account Consolidation --
2872
2873 Open cur_oks_comm_line(l_okc_lines_rec.id);
2874 fetch cur_oks_comm_line into l_oks_comm_line_rec;
2875 close cur_oks_comm_line;
2876
2877 Open cur_get_Party_Id (l_okc_lines_rec.cust_acct_id);
2878 fetch cur_get_party_id into l_oks_party_rec ;
2879 close cur_get_party_id;
2880
2881 IF nvl(l_oks_comm_line_rec.cust_po_number,-1) = nvl(l_okc_headers_rec.cust_po_number, -1)
2882 AND Nvl(l_oks_comm_line_rec.cust_po_number_req_yn,'X') = nvl(l_okc_headers_rec.cust_po_number_req_yn, 'X')
2883 AND Nvl(l_oks_comm_line_rec.payment_instruction_type,'X') = nvl(l_okc_headers_rec.payment_instruction_type, 'X')
2884 THEN
2885 l_cust_po_flag := 1;
2886 END IF ;
2887 Open cur_head_cust_acct(l_okc_headers_rec.bill_to_site_use_id);
2888 fetch cur_head_cust_acct into l_header_cust_acct;
2889 close cur_head_cust_acct;
2890 l_line_cust_acct := l_okc_lines_rec.cust_acct_id;
2891
2892 IF l_oks_headers_rec.payment_type = 'COM' THEN
2893
2894 IF l_oks_headers_rec.commitment_id is NULL THEN
2895 l_klnv_tbl_in(1).payment_type := l_oks_headers_rec.payment_type;
2896 -- Bank Account Consolidation --
2897 /* l_klnv_tbl_in(1).cc_no := NULL;
2898 l_klnv_tbl_in(1).cc_expiry_date := NULL;
2899 l_klnv_tbl_in(1).cc_bank_acct_id := NULL;
2900 l_klnv_tbl_in(1).cc_auth_code := NULL; */
2901 -- Bank Account Consolidation --
2902 -- Call the delete API to Delete Credit card details
2903 -- if the credit card details are present on the line level
2904 IF l_oks_comm_line_rec.trxn_extension_id IS NOT NULL
2905 THEN
2906 Delete_credit_Card
2907 (p_trnx_ext_id => l_oks_comm_line_rec.trxn_extension_id,
2908 p_line_id => l_cle_id,
2909 p_party_id => l_oks_party_rec.party_id ,
2910 p_cust_account_id => l_line_cust_acct,
2911 x_return_status => l_return_status,
2912 x_msg_data => l_msg_data );
2913 -- Bug 5227077 --
2914 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2915 FOR i in 1..fnd_msg_pub.count_msg
2916 Loop
2917 fnd_msg_pub.get
2918 (
2919 p_msg_index => i,
2920 p_encoded => 'F',
2921 p_data => l_msg_data,
2922 p_msg_index_out => l_msg_index
2923 );
2924 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2925 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2926 l_tot_msg_count := l_tot_msg_count + 1;
2927 l_msg_data := NULL;
2928 End Loop;
2929 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2930 end if;
2931 -- Bug 5227077 --
2932
2933 END IF;
2934 l_klnv_tbl_in(1).trxn_extension_id := NULL;
2935 l_klnv_tbl_in(1).commitment_id := NULL;
2936 l_payment_method_com := 1; -- 4394382
2937 ELSIF nvl(l_header_cust_acct,-1) = nvl(l_line_cust_acct , -9) or l_header_bca is NOT NULL
2938 THEN
2939 l_klnv_tbl_in(1).payment_type := l_oks_headers_rec.payment_type;
2940 -- Bank Account Consolidation --
2941 /* l_klnv_tbl_in(1).cc_no := NULL;
2942 l_klnv_tbl_in(1).cc_expiry_date := NULL;
2943 l_klnv_tbl_in(1).cc_bank_acct_id := NULL;
2944 l_klnv_tbl_in(1).cc_auth_code := NULL; */
2945 -- Call the delete API to Delete Credit card details--
2946 IF l_oks_comm_line_rec.trxn_extension_id IS NOT NULL
2947 THEN
2948 Delete_credit_Card
2949 (p_trnx_ext_id => l_oks_comm_line_rec.trxn_extension_id,
2950 p_line_id => l_cle_id,
2951 p_party_id => l_oks_party_rec.party_id ,
2952 p_cust_account_id => l_header_cust_acct,
2953 x_return_status => l_return_status,
2954 x_msg_data => l_msg_data );
2955
2956 -- Bug 5227077 --
2957 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2958 FOR i in 1..fnd_msg_pub.count_msg
2959 Loop
2960 fnd_msg_pub.get
2961 (
2962 p_msg_index => i,
2963 p_encoded => 'F',
2964 p_data => l_msg_data,
2965 p_msg_index_out => l_msg_index
2966 );
2967 x_msg_tbl(l_tot_msg_count).status := l_return_status;
2968 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
2969 l_tot_msg_count := l_tot_msg_count + 1;
2970 l_msg_data := NULL;
2971 End Loop;
2972 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2973 End if;
2974 -- Bug 5227077 --
2975
2976 END IF;
2977 l_klnv_tbl_in(1).trxn_extension_id := NULL;
2978 -- Bank Account Consolidation --
2979 l_klnv_tbl_in(1).commitment_id := l_oks_headers_rec.commitment_id;
2980 l_payment_method_com := 1; -- 4394382
2981 END IF;
2982 -- END GCHADHA --
2983
2984 -- BUG 3691147 --
2985 -- GCHADHA 3691147 --
2986 /*
2987 -- FIX FOR THE BUG 3675745
2988 -- GCHADHA 9-06-2004 --
2989 l_klnv_tbl_in(1).cust_po_number := l_okc_headers_rec.cust_po_number;
2990 - l_klnv_tbl_in(1).cust_po_number_req_yn := l_okc_headers_rec.cust_po_number_req_yn;
2991 -- END GCHADHA --
2992 */
2993 -- END GCHADHA --
2994 -- l_klnv_tbl_in(1).cust_po_number := NULL;
2995 -- l_klnv_tbl_in(1).cust_po_number_req_yn := NULL;
2996
2997
2998 ELSIF l_oks_headers_rec.payment_type = 'CCR' THEN
2999 -- Bank Account consolidation --
3000 IF l_oks_headers_rec.trxn_extension_id is NULL THEN
3001 IF l_oks_comm_line_rec.trxn_extension_id IS NOT NULL
3002 THEN
3003 Delete_credit_Card
3004 (p_trnx_ext_id => l_oks_comm_line_rec.trxn_extension_id,
3005 p_line_id => l_cle_id,
3006 -- p_party_id => l_party_id ,
3007 p_party_id =>l_oks_party_rec.party_id,
3008 p_cust_account_id => l_line_cust_acct,
3009 x_return_status => l_return_status,
3010 x_msg_data => l_msg_data );
3011
3012 -- Bug 5227077 --
3013 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3014 FOR i in 1..fnd_msg_pub.count_msg
3015 Loop
3016 fnd_msg_pub.get
3017 (
3018 p_msg_index => i,
3019 p_encoded => 'F',
3020 p_data => l_msg_data,
3021 p_msg_index_out => l_msg_index
3022 );
3023 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3024 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3025 l_tot_msg_count := l_tot_msg_count + 1;
3026 l_msg_data := NULL;
3027 End Loop;
3028 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3029 End if;
3030 -- Bug 5227077 --
3031
3032 END IF;
3033 l_klnv_tbl_in(1).payment_type := l_oks_headers_rec.payment_type;
3034 l_klnv_tbl_in(1).trxn_extension_id := NULL;
3035 l_klnv_tbl_in(1).commitment_id := NULL;
3036 l_payment_method_ccr := 1; -- 4394382
3037 -- Cascade the credit card number only when the header and lines level
3038 -- bill to accounts are same or if we have already marked cascade of
3039 -- billto account from header to lines.
3040 ELSIF nvl(l_header_cust_acct,-1) = nvl(l_line_cust_acct , -9) or l_header_bca is NOT NULL
3041 THEN
3042 l_klnv_tbl_in(1).payment_type := l_oks_headers_rec.payment_type;
3043
3044 /* l_klnv_tbl_in(1).cc_no := l_oks_headers_rec.cc_no;
3045 l_klnv_tbl_in(1).cc_expiry_date := l_oks_headers_rec.cc_expiry_date;
3046 l_klnv_tbl_in(1).cc_bank_acct_id := l_oks_headers_rec.cc_bank_acct_id;
3047 l_klnv_tbl_in(1).cc_auth_code := l_oks_headers_rec.cc_auth_code; */
3048 IF l_oks_comm_line_rec.trxn_extension_id IS NOT NULL
3049 THEN
3050 Delete_credit_Card
3051 (p_trnx_ext_id => l_oks_comm_line_rec.trxn_extension_id,
3052 p_line_id => l_cle_id,
3053 -- p_party_id => l_party_id ,
3054 p_party_id =>l_oks_party_rec.party_id,
3055 p_cust_account_id => l_header_cust_acct,
3056 x_return_status => l_return_status,
3057 x_msg_data => l_msg_data );
3058 -- Bug 5227077 --
3059 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3060 FOR i in 1..fnd_msg_pub.count_msg
3061 Loop
3062 fnd_msg_pub.get
3063 (
3064 p_msg_index => i,
3065 p_encoded => 'F',
3066 p_data => l_msg_data,
3067 p_msg_index_out => l_msg_index
3068 );
3069 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3070 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3071 l_tot_msg_count := l_tot_msg_count + 1;
3072 l_msg_data := NULL;
3073 End Loop;
3074 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3075 End if;
3076 -- Bug 5227077 --
3077
3078 END IF;
3079 -- Create Credit Card Details --
3080 l_trxn_extension_id := Create_credit_Card
3081 ( p_line_id => l_cle_id ,
3082 p_party_id => l_oks_party_rec.party_id ,
3083 p_org => l_okc_headers_rec.authoring_org_id,
3084 p_account_site_id => l_okc_lines_rec.bill_to_site_use_id ,
3085 p_cust_account_id => l_header_cust_acct,
3086 p_trnx_ext_id => l_oks_headers_rec.trxn_extension_id,
3087 x_return_status => l_return_status,
3088 x_msg_data => l_msg_data);
3089
3090 -- Bug 5227077 --
3091 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3092 FOR i in 1..fnd_msg_pub.count_msg
3093 Loop
3094 fnd_msg_pub.get
3095 (
3096 p_msg_index => i,
3097 p_encoded => 'F',
3098 p_data => l_msg_data,
3099 p_msg_index_out => l_msg_index
3100 );
3101 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3102 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3103 l_tot_msg_count := l_tot_msg_count + 1;
3104 l_msg_data := NULL;
3105 End Loop;
3106 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3107 End if;
3108 -- Bug 5227077 --
3109
3110
3111 l_klnv_tbl_in(1).trxn_extension_id := l_trxn_extension_id;
3112 l_klnv_tbl_in(1).commitment_id := NULL;
3113 l_payment_method_ccr := 1; -- 4394382
3114 END IF;
3115 -- Bank Account Consolidation --
3116 -- BUG 3691147 --
3117 -- GCHADHA 3691147 --
3118 /*
3119 -- FIX FOR THE BUG 3675745
3120 -- GCHADHA 9-06-2004 --
3121 l_klnv_tbl_in(1).cust_po_number := l_okc_headers_rec.cust_po_number;
3122 l_klnv_tbl_in(1).cust_po_number_req_yn := l_okc_headers_rec.cust_po_number_req_yn;
3123 -- END GCHADHA --
3124 */
3125 -- GCHADHA --
3126 -- l_klnv_tbl_in(1).cust_po_number := NULL;
3127 -- l_klnv_tbl_in(1).cust_po_number_req_yn := NULL;
3128 -- GCHADHA --
3129 -- Case when the payment method at header level is null
3130 -- and at line level payment method is not null
3131 ELSIF l_oks_headers_rec.payment_type is NULL THEN
3132
3133 l_klnv_tbl_in(1).payment_type := NULL;
3134 -- Bank Account Consolidation --
3135 /* l_klnv_tbl_in(1).cc_no := NULL;
3136 l_klnv_tbl_in(1).cc_expiry_date := NULL;
3137 l_klnv_tbl_in(1).cc_bank_acct_id := NULL;
3138 l_klnv_tbl_in(1).cc_auth_code := NULL; */
3139
3140 IF l_oks_comm_line_rec.trxn_extension_id IS NOT NULL
3141 THEN
3142 Delete_credit_Card
3143 (p_trnx_ext_id => l_oks_comm_line_rec.trxn_extension_id,
3144 p_line_id => l_cle_id,
3145 p_party_id => l_oks_party_rec.party_id ,
3146 p_cust_account_id =>l_line_cust_acct,
3147 x_return_status => l_return_status,
3148 x_msg_data => l_msg_data );
3149
3150 -- Bug 5227077 --
3151 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3152 FOR i in 1..fnd_msg_pub.count_msg
3153 Loop
3154 fnd_msg_pub.get
3155 (
3156 p_msg_index => i,
3157 p_encoded => 'F',
3158 p_data => l_msg_data,
3159 p_msg_index_out => l_msg_index
3160 );
3161 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3162 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3163 l_tot_msg_count := l_tot_msg_count + 1;
3164 l_msg_data := NULL;
3165 End Loop;
3166 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3167 End if;
3168 -- Bug 5227077 --
3169
3170 END IF;
3171 l_klnv_tbl_in(1).trxn_extension_id :=NULL;
3172 -- Bank Account Consolidation --
3173 l_klnv_tbl_in(1).commitment_id := NULL;
3174 l_payment_method_ccr := 1; -- 4394382
3175
3176 END IF;
3177
3178 IF l_payment_method_com = 1 OR l_cust_po_flag = 0 OR l_payment_method_ccr = 1 THEN
3179 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3180
3181 fnd_msg_pub.initialize;
3182 OKC_API.SET_MESSAGE(
3183 p_app_name => G_APP_NAME_OKS,
3184 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3185 p_token1 => 'ATTRIBUTE',
3186 p_token1_value => 'Payment Method');
3187
3188 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3189 p_encoded => fnd_api.g_false);
3190 x_msg_tbl(l_tot_msg_count).status := 'S';
3191 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3192 l_tot_msg_count := l_tot_msg_count + 1;
3193 l_msg_data := NULL;
3194 END IF;
3195 END IF;
3196
3197 END IF;
3198
3199 -- 8/23/2005 hkamdar R12 Partial Period
3200 -- Price UOM Cascading
3201 IF l_header_price_uom is not null then
3202 ----errorout('Price uom not null');
3203
3204 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
3205 l_klnv_tbl_in(1).price_uom := l_oks_headers_rec.price_uom;
3206 ----errorout('UOM-'||l_klnv_tbl_in(1).price_uom);
3207 fnd_msg_pub.initialize;
3208 OKC_API.SET_MESSAGE(
3209 p_app_name => G_APP_NAME_OKS,
3210 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3211 p_token1 => 'ATTRIBUTE',
3212 p_token1_value => 'Price UOM');
3213
3214 l_msg_data := fnd_msg_pub.get( p_msg_index => fnd_msg_pub.g_first,
3215 p_encoded => fnd_api.g_false);
3216
3217 x_msg_tbl(l_tot_msg_count).status := 'S';
3218 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3219 l_tot_msg_count := l_tot_msg_count + 1;
3220 l_msg_data := NULL;
3221
3222 END IF;
3223
3224 --- price list cascading
3225
3226 IF l_header_price_list is not null then
3227
3228 -- Validate before cascading the Price List wheather the
3229 -- price list is valid or not.
3230 QP_UTIL_PUB.Validate_Price_list_Curr_code(
3231 l_price_list_id => l_okc_headers_rec.price_list_id
3232 ,l_currency_code => l_okc_headers_rec.currency_code
3233 ,l_pricing_effective_date => l_pricing_effective_date
3234 ,l_validate_result => l_validate_result);
3235
3236 IF NVL(l_validate_result,'N') IN ('U' ,'N') then -- Unexpected Error
3237 fnd_msg_pub.initialize;
3238 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,p_encoded => fnd_api.g_false);
3239 OKC_API.SET_MESSAGE(
3240 p_app_name => 'OKS', --G_APP_NAME_OKS,
3241 p_msg_name => 'OKS_INVALID_PRICE_LIST');
3242 x_msg_tbl(l_tot_msg_count).status := 'E';
3243 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3244 l_tot_msg_count := l_tot_msg_count + 1;
3245 l_msg_data := NULL;
3246 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3247 END IF;
3248
3249 l_clev_tbl_in(1).ID := l_okc_lines_rec.ID;
3250 l_clev_tbl_in(1).price_list_id := l_okc_headers_rec.price_list_id;
3251
3252 If l_okc_lines_rec.lse_id = '12' then -- Usage line
3253 open cur_get_lines_details (l_chr_id , l_okc_lines_rec.ID);
3254 fetch cur_get_lines_details into
3255 l_locked_price_list_id,l_locked_price_list_line_id;
3256 close cur_get_lines_details;
3257
3258 l_source_price_list_line_id := l_locked_price_list_line_id;
3259
3260 If l_source_price_list_line_id IS NOT NULL THEN
3261 oks_qp_pkg.delete_locked_pricebreaks(l_api_version,
3262 l_source_price_list_line_id,
3263 l_init_msg_list,
3264 l_return_status,
3265 l_msg_count,
3266 l_msg_data);
3267 -- Bug 5227077 --
3268 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3269 FOR i in 1..fnd_msg_pub.count_msg
3270 Loop
3271 fnd_msg_pub.get
3272 (
3273 p_msg_index => i,
3274 p_encoded => 'F',
3275 p_data => l_msg_data,
3276 p_msg_index_out => l_msg_index
3277 );
3278 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3279 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3280 l_tot_msg_count := l_tot_msg_count + 1;
3281 l_msg_data := NULL;
3282 End Loop;
3283 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3284 End if;
3285 -- Bug 5227077 --
3286
3287 l_locked_prl_cnt := l_locked_prl_cnt + 1;
3288
3289 l_klnv_tbl_in(1).ID := l_oks_lines_rec.id;
3290 l_klnv_tbl_in(1).locked_price_list_id := null;
3291 l_klnv_tbl_in(1).locked_price_list_line_id := null;
3292
3293
3294 fnd_msg_pub.initialize;
3295 OKC_API.SET_MESSAGE(
3296 p_app_name => G_APP_NAME_OKS,
3297 p_msg_name => 'OKS_HEADER_CASCADE_WARNING',
3298 p_token1 => 'ATTRIBUTE',
3299 p_token1_value => 'Price List');
3300
3301 l_msg_data := fnd_msg_pub.get( p_msg_index => fnd_msg_pub.g_first,
3302 p_encoded => fnd_api.g_false);
3303 x_msg_tbl(l_tot_msg_count).status := 'S';
3304 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3305 l_tot_msg_count := l_tot_msg_count + 1;
3306 l_msg_data := NULL;
3307 ELSE
3308 -- To display the Cascade Message when
3309 -- Usage lines is not locked and the cascade take place --
3310 fnd_msg_pub.initialize;
3311 OKC_API.SET_MESSAGE(
3312 p_app_name => G_APP_NAME_OKS,
3313 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3314 p_token1 => 'ATTRIBUTE',
3315 p_token1_value => 'Price List');
3316
3317 l_msg_data := fnd_msg_pub.get( p_msg_index => fnd_msg_pub.g_first,
3318 p_encoded => fnd_api.g_false);
3319 x_msg_tbl(l_tot_msg_count).status := 'S';
3320 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3321 l_tot_msg_count := l_tot_msg_count + 1;
3322 l_msg_data := NULL;
3323
3324 END IF;
3325 Else
3326 fnd_msg_pub.initialize;
3327 OKC_API.SET_MESSAGE(
3328 p_app_name => G_APP_NAME_OKS,
3329 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3330 p_token1 => 'ATTRIBUTE',
3331 p_token1_value => 'Price List');
3332
3333 l_msg_data := fnd_msg_pub.get( p_msg_index => fnd_msg_pub.g_first,
3334 p_encoded => fnd_api.g_false);
3335 x_msg_tbl(l_tot_msg_count).status := 'S';
3336 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3337 l_tot_msg_count := l_tot_msg_count + 1;
3338 l_msg_data := NULL;
3339
3340 End If;
3341
3342 END IF;
3343
3344 -- End 8/23/2005 hkamdar R12 Partial Period
3345
3346 --Fixed Tax exemption is cascading to line along with bug#4026268 --gbgupta
3347 --checking l_insupd_flag flag along with l_header_tax IS NOT NULL
3348 --l_header_exception_number IS NOT NULL
3349 -- IKON ENHANCEMENT --
3350 IF (l_header_tax IS NOT NULL AND (l_status_flag or l_exmpt_num_flag)) OR --l_insupd_flag) OR
3351 l_header_tax_code IS NOT NULL OR
3352 ( l_header_bca IS NOT NULL AND l_oks_header_id = 1) OR
3353 (l_billing_profile IS NOT NULL AND
3354 l_billing_profile_id IS NOT NULL) OR
3355 l_header_arl IS NOT NULL OR
3356 (l_header_exception_number IS NOT NULL AND (l_status_flag or l_exmpt_num_flag)) OR --l_insupd_flag) OR
3357 l_payment_method IS NOT NULL OR
3358 -- 8/23/2005 hkamdar R12 Partial Period
3359 -- additional condition is added to the if statement to update the oks_k_lines.
3360 -- if price_uom is changed
3361 l_header_price_uom IS NOT NULL
3362 OR (l_header_price_list is not null and l_locked_prl_cnt > 0)
3363 -- and l_locked_prl_cnt > 0 THEN
3364 THEN
3365 -- Resetting the locked price counter.
3366 l_locked_prl_cnt := 0;
3367 -- End 8/23/2005 hkamdar R12 Partial Period
3368 --Update oks lines
3369 oks_contract_line_pub.update_line
3370 (
3371 p_api_version => l_api_version,
3372 p_init_msg_list => l_init_msg_list,
3373 x_return_status => l_return_status,
3374 x_msg_count => l_msg_count,
3375 x_msg_data => l_msg_data,
3376 p_klnv_tbl => l_klnv_tbl_in,
3377 x_klnv_tbl => l_klnv_tbl_out,
3378 p_validate_yn => 'N'
3379 );
3380 --errorout('Return status -'||l_return_status);
3381 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3382
3383 FOR i in 1..fnd_msg_pub.count_msg
3384 Loop
3385 fnd_msg_pub.get
3386 (
3387 p_msg_index => i,
3388 p_encoded => 'F',
3389 p_data => l_msg_data,
3390 p_msg_index_out => l_msg_index
3391 );
3392
3393 x_msg_tbl(l_tot_msg_count).status := l_return_status; --'E';
3394 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3395 l_tot_msg_count := l_tot_msg_count + 1;
3396 l_msg_data := NULL;
3397 End Loop;
3398 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3399 End If;
3400
3401
3402 END IF;
3403 -- IKON ENHANCEMENT --
3404 IF l_header_sto IS NOT NULL OR
3405 l_header_bto IS NOT NULL OR
3406 l_header_sca IS NOT NULL OR
3407 l_header_bca IS NOT NULL OR
3408 l_header_billto_contact IS NOT NULL OR
3409 l_header_ire IS NOT NULL OR
3410 (l_billing_profile IS NOT NULL AND
3411 l_billing_profile_id IS NOT NULL) OR
3412 l_header_dates IS NOT NULL OR
3413 l_payment_method IS NOT NULL OR-- Payment Instruction Type
3414 -- 8/23/2005 hkamdar R12 Partial Period
3415 -- additional condition is added to the if statement to update the okc_k_lines.
3416 -- if price list is changed
3417 l_header_price_list IS NOT NULL
3418
3419
3420 THEN
3421 update_line(
3422 p_clev_tbl => l_clev_tbl_in,
3423 x_clev_tbl => l_clev_tbl_out,
3424 x_return_status => l_return_status,
3425 x_msg_count => l_msg_count,
3426 x_msg_data => l_msg_data
3427 );
3428
3429
3430 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3431
3432 FOR i in 1..fnd_msg_pub.count_msg
3433 Loop
3434 fnd_msg_pub.get
3435 (
3436 p_msg_index => i,
3437 p_encoded => 'F',
3438 p_data => l_msg_data,
3439 p_msg_index_out => l_msg_index
3440 );
3441
3442 x_msg_tbl(l_tot_msg_count).status := l_return_status; --'E';
3443 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3444 l_tot_msg_count := l_tot_msg_count + 1;
3445 l_msg_data := NULL;
3446 End Loop;
3447 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3448 End If;
3449
3450 END IF;
3451
3452 -- 8/23/2005 hkamdar R12 Partial Period
3453
3454 IF l_header_price_list IS NOT NULL and l_return_status = FND_API.G_RET_STS_SUCCESS
3455 then
3456 -- Bug 4668385 --
3457 l_input_details.line_id := l_cle_id;
3458 If l_okc_lines_rec.lse_id = '46' Then
3459 l_input_details.intent := 'SB_P';
3460 Else
3461 l_input_details.intent := 'LP';
3462 End If;
3463 -- Bug 4668385 --
3464
3465 oks_qp_int_pvt.compute_Price
3466 (
3467 p_api_version => 1.0,
3468 p_init_msg_list => 'T',
3469 p_detail_rec => l_input_details,
3470 x_price_details => l_output_details,
3471 x_modifier_details => l_modif_details,
3472 x_price_break_details => l_pb_details,
3473 x_return_status => l_return_status,
3474 x_msg_count => l_msg_count,
3475 x_msg_data => l_msg_data
3476 );
3477
3478
3479
3480
3481 l_status_tbl := oks_qp_int_pvt.get_Pricing_Messages;
3482
3483 if l_status_tbl.Count > 0 Then
3484 For i in l_status_tbl.FIRST..l_status_tbl.LAST
3485 Loop
3486 x_msg_tbl(l_tot_msg_count).status := l_status_tbl(i).Status_Code;
3487 x_msg_tbl(l_tot_msg_count).description := l_status_tbl(i).Status_Text;
3488 l_tot_msg_count := l_tot_msg_count + 1;
3489
3490 End Loop;
3491 end if;
3492 -- Bug 5381082 --
3493 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
3494 FOR i in 1..fnd_msg_pub.count_msg
3495 Loop
3496 fnd_msg_pub.get
3497 (
3498 p_msg_index => i,
3499 p_encoded => 'F',
3500 p_data => l_msg_data,
3501 p_msg_index_out => l_msg_index
3502 );
3503 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3504 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3505 l_tot_msg_count := l_tot_msg_count + 1;
3506 l_msg_data := NULL;
3507 End Loop;
3508 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3509 End If;
3510 -- BUg 5381082 --
3511 END IF;
3512 -- End 8/23/2005 hkamdar R12 Partial Period
3513
3514 If l_header_dates is not null and l_return_status = FND_API.G_RET_STS_SUCCESS then
3515 --Fix for bug#3424479
3516 -- FIX DONE FOR USAGE CONTRACT SHOWING INCORRECT MESSAGE WHEN CASCADING DATES.
3517 -- ADDED CHECK FOR USAGE LINE lse_id <> 12
3518 -- GCHADHA 8-JULY-2004
3519 If l_okc_lines_rec.lse_id not in (46, 12) AND nvl(l_oks_lines_rec.standard_cov_yn, 'N') <> 'Y' then
3520
3521 Open LineCov_cur(l_cle_id);
3522 Fetch LineCov_cur into l_id;
3523 If LineCov_cur%Found Then
3524
3525 OKS_COVERAGES_PVT.Update_COVERAGE_Effectivity(
3526 p_api_version => l_api_version,
3527 p_init_msg_list => l_init_msg_list,
3528 x_return_status => l_return_status,
3529 x_msg_count => l_msg_count,
3530 x_msg_data => l_msg_data,
3531 p_service_Line_Id => l_cle_id,
3532 p_New_Start_Date => header_dates_rec.start_date,
3533 p_New_End_Date => header_dates_rec.end_date );
3534
3535 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3536
3537 FOR i in 1..fnd_msg_pub.count_msg
3538 Loop
3539 fnd_msg_pub.get
3540 (
3541 p_msg_index => i,
3542 p_encoded => 'F',
3543 p_data => l_msg_data,
3544 p_msg_index_out => l_msg_index
3545 );
3546
3547 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3548 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3549 l_tot_msg_count := l_tot_msg_count + 1;
3550 l_msg_data := NULL;
3551 End Loop;
3552 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3553
3554 ELSE
3555 -- Bug 5191587 --
3556 If l_okc_lines_rec.lse_id in ('1','14', '19') and l_sr_number IS NOT NULL
3557 then
3558 fnd_msg_pub.initialize;
3559
3560 OKC_API.SET_MESSAGE(
3561 p_app_name => G_APP_NAME_OKS,
3562 p_msg_name => 'OKS_HEADER_CASCADE_SR_SUCCESS',
3563 p_token1 => 'SR#',
3564 p_token1_value => l_sr_number,
3565 p_token2 => 'Line No ',
3566 p_token2_value => l_line_number
3567 );
3568
3569 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3570 p_encoded => fnd_api.g_false);
3571 x_msg_tbl(l_tot_msg_count).status := 'W';
3572 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3573 l_tot_msg_count := l_tot_msg_count + 1;
3574 l_msg_data := NULL;
3575
3576 fnd_msg_pub.initialize;
3577
3578 -- Issue a warning message if dates are cascaded from header to lines for a fully billed contract.
3579 IF(l_disp_warning ='Y') THEN
3580
3581 OKC_API.SET_MESSAGE(
3582 p_app_name => G_APP_NAME_OKS,
3583 p_msg_name => 'OKS_HEADER_CASCADE_DATES_WARN');
3584
3585 x_msg_tbl(l_tot_msg_count).status := 'W';
3586 ELSE
3587
3588 OKC_API.SET_MESSAGE(
3589 p_app_name => G_APP_NAME_OKS,
3590 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3591 p_token1 => 'ATTRIBUTE',
3592 p_token1_value => 'Date');
3593 x_msg_tbl(l_tot_msg_count).status := 'S';
3594 END IF;
3595
3596 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3597 p_encoded => fnd_api.g_false);
3598 -- x_msg_tbl(l_tot_msg_count).status := 'S';
3599 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3600 l_tot_msg_count := l_tot_msg_count + 1;
3601 l_msg_data := NULL;
3602
3603 Else
3604 fnd_msg_pub.initialize;
3605
3606 -- Issue a warning message if dates are cascaded from header to lines for a fully billed contract.
3607 IF(l_disp_warning ='Y') THEN
3608
3609 OKC_API.SET_MESSAGE(
3610 p_app_name => G_APP_NAME_OKS,
3611 p_msg_name => 'OKS_HEADER_CASCADE_DATES_WARN');
3612
3613 x_msg_tbl(l_tot_msg_count).status := 'W';
3614 ELSE
3615 OKC_API.SET_MESSAGE(
3616 p_app_name => G_APP_NAME_OKS,
3617 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3618 p_token1 => 'ATTRIBUTE',
3619 p_token1_value => 'Date');
3620 x_msg_tbl(l_tot_msg_count).status := 'S';
3621 END IF;
3622 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3623 p_encoded => fnd_api.g_false);
3624 -- x_msg_tbl(l_tot_msg_count).status := 'S';
3625 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3626 l_tot_msg_count := l_tot_msg_count + 1;
3627 l_msg_data := NULL;
3628
3629 End If;
3630 END IF;
3631
3632 Else --LineCov_cur% not Found Then
3633 fnd_msg_pub.initialize;
3634 OKC_API.SET_MESSAGE
3635 (
3636 p_app_name => G_APP_NAME_OKS,
3637 p_msg_name => 'OKS_CASCADE_COV_NOT_FOUND',
3638 p_token2 => 'Line No ',
3639 p_token2_value => l_line_number
3640 );
3641
3642 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3643 p_encoded => fnd_api.g_false);
3644 x_msg_tbl(l_tot_msg_count).status := 'E';
3645 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3646 l_tot_msg_count := l_tot_msg_count + 1;
3647 l_msg_data := NULL;
3648
3649 END IF;
3650
3651 close LINEcov_cur;
3652
3653 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3654
3655 OKS_PM_PROGRAMS_PVT.ADJUST_PM_PROGRAM_SCHEDULE
3656 (p_api_version => l_api_version,
3657 p_init_msg_list => l_init_msg_list,
3658 p_contract_line_id => l_cle_id,
3659 p_new_start_date => header_dates_rec.start_date,
3660 p_new_end_date => header_dates_rec.end_date,
3661 x_return_status => l_return_status,
3662 x_msg_count => l_msg_count,
3663 x_msg_data => l_msg_data
3664 );
3665 -- Bug 5227077 --
3666 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3667 FOR i in 1..fnd_msg_pub.count_msg
3668 Loop
3669 fnd_msg_pub.get
3670 (
3671 p_msg_index => i,
3672 p_encoded => 'F',
3673 p_data => l_msg_data,
3674 p_msg_index_out => l_msg_index
3675 );
3676 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3677 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3678 l_tot_msg_count := l_tot_msg_count + 1;
3679 l_msg_data := NULL;
3680 End Loop;
3681 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3682 end if;
3683 -- Bug 5227077 --
3684
3685 End If;
3686 -- Bug 5191587 --
3687 ELSIf l_okc_lines_rec.lse_id in ('1','14','19') AND nvl(l_oks_lines_rec.standard_cov_yn, 'N') = 'Y' then
3688 If l_sr_number IS NOT NULL then
3689 fnd_msg_pub.initialize;
3690 OKC_API.SET_MESSAGE(
3691 p_app_name => G_APP_NAME_OKS,
3692 p_msg_name => 'OKS_HEADER_CASCADE_SR_SUCCESS',
3693 p_token1 => 'SR#',
3694 p_token1_value => l_sr_number,
3695 p_token2 => 'Line No ',
3696 p_token2_value => l_line_number
3697 );
3698
3699 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3700 p_encoded => fnd_api.g_false);
3701 x_msg_tbl(l_tot_msg_count).status := 'W';
3702 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3703 l_tot_msg_count := l_tot_msg_count + 1;
3704 l_msg_data := NULL;
3705
3706 fnd_msg_pub.initialize;
3707 -- Issue a warning message if dates are cascaded from header to lines for a fully billed contract.
3708 IF(l_disp_warning ='Y') THEN
3709
3710 OKC_API.SET_MESSAGE(
3711 p_app_name => G_APP_NAME_OKS,
3712 p_msg_name => 'OKS_HEADER_CASCADE_DATES_WARN');
3713
3714 x_msg_tbl(l_tot_msg_count).status := 'W';
3715 ELSE
3716
3717 OKC_API.SET_MESSAGE(
3718 p_app_name => G_APP_NAME_OKS,
3719 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3720 p_token1 => 'ATTRIBUTE',
3721 p_token1_value => 'Date');
3722 x_msg_tbl(l_tot_msg_count).status := 'S';
3723 END IF;
3724
3725 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3726 p_encoded => fnd_api.g_false);
3727 -- x_msg_tbl(l_tot_msg_count).status := 'S';
3728 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3729 l_tot_msg_count := l_tot_msg_count + 1;
3730 l_msg_data := NULL;
3731
3732 Else
3733 fnd_msg_pub.initialize;
3734
3735 -- Issue a warning message if dates are cascaded from header to lines for a fully billed contract.
3736 IF(l_disp_warning ='Y') THEN
3737
3738 OKC_API.SET_MESSAGE(
3739 p_app_name => G_APP_NAME_OKS,
3740 p_msg_name => 'OKS_HEADER_CASCADE_DATES_WARN');
3741
3742 x_msg_tbl(l_tot_msg_count).status := 'W';
3743 ELSE
3744
3745 OKC_API.SET_MESSAGE(
3746 p_app_name => G_APP_NAME_OKS,
3747 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3748 p_token1 => 'ATTRIBUTE',
3749 p_token1_value => 'Date');
3750 x_msg_tbl(l_tot_msg_count).status := 'S';
3751
3752 END IF;
3753 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3754 p_encoded => fnd_api.g_false);
3755 --x_msg_tbl(l_tot_msg_count).status := 'S';
3756 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3757 l_tot_msg_count := l_tot_msg_count + 1;
3758 l_msg_data := NULL;
3759
3760 End If;
3761 -- Bug 5191587 --
3762 Else --lse_id = 46
3763 fnd_msg_pub.initialize;
3764
3765 -- Issue a warning message if dates are cascaded from header to lines for a fully billed contract.
3766 IF(l_disp_warning ='Y') THEN
3767
3768 OKC_API.SET_MESSAGE(
3769 p_app_name => G_APP_NAME_OKS,
3770 p_msg_name => 'OKS_HEADER_CASCADE_DATES_WARN');
3771
3772 x_msg_tbl(l_tot_msg_count).status := 'W';
3773 ELSE
3774
3775 OKC_API.SET_MESSAGE(
3776 p_app_name => G_APP_NAME_OKS,
3777 p_msg_name => 'OKS_HEADER_CASCADE_SUCCESS',
3778 p_token1 => 'ATTRIBUTE',
3779 p_token1_value => 'Date');
3780 x_msg_tbl(l_tot_msg_count).status := 'S';
3781 END IF;
3782 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3783 p_encoded => fnd_api.g_false);
3784 --x_msg_tbl(l_tot_msg_count).status := 'S';
3785 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3786 l_tot_msg_count := l_tot_msg_count + 1;
3787 l_msg_data := NULL;
3788
3789 End If; --lse_id <> 46
3790
3791 END IF;
3792
3793
3794 IF l_calculate_tax is not null then
3795 l_lse_id := l_okc_lines_rec.lse_id;
3796 l_price_negotiated := l_okc_lines_rec.price_negotiated;
3797
3798 G_RAIL_REC.amount := l_price_negotiated;
3799 IF l_lse_id <> 46 THEN
3800
3801 --get all sublines and call tax engine and update IRT rule
3802 -------------------------------------------------------------------
3803 For sub_line_rec IN cur_sub_line(l_cle_id)
3804 Loop
3805 G_RAIL_REC.amount := sub_line_rec.price_negotiated;
3806 OKS_TAX_UTIL_PVT.Get_Tax
3807 (
3808 p_api_version => 1.0,
3809 p_init_msg_list => OKC_API.G_TRUE,
3810 p_chr_id => l_chr_id,
3811 p_cle_id => sub_line_rec.id, --l_cle_id,
3812 px_rail_rec => G_RAIL_REC,
3813 x_msg_count => x_msg_count,
3814 x_msg_data => x_msg_data,
3815 x_return_status => l_return_status
3816 );
3817
3818 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
3819 FOR i in 1..fnd_msg_pub.count_msg
3820 Loop
3821 fnd_msg_pub.get
3822 (
3823 p_msg_index => i,
3824 p_encoded => 'F',
3825 p_data => l_msg_data,
3826 p_msg_index_out => l_msg_index
3827 );
3828 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3829 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3830 l_tot_msg_count := l_tot_msg_count + 1;
3831 l_msg_data := NULL;
3832 End Loop;
3833 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3834 End If;
3835
3836 l_UNIT_SELLING_PRICE := G_RAIL_REC.UNIT_SELLING_PRICE;
3837 l_QUANTITY := G_RAIL_REC.QUANTITY;
3838 l_sub_total := G_RAIL_REC.AMOUNT;
3839 l_AMOUNT_INCLUDES_TAX_FLAG := G_RAIL_REC.AMOUNT_INCLUDES_TAX_FLAG; --Rule_information5
3840 l_Tax_Code := G_RAIL_REC.TAX_CODE;
3841 l_TAX_RATE := G_RAIL_REC.TAX_RATE ;
3842 l_Tax_Value := G_RAIL_REC.TAX_VALUE; --Rule_information4
3843
3844 IF l_AMOUNT_INCLUDES_TAX_FLAG IS NULL THEN
3845 l_AMOUNT_INCLUDES_TAX_FLAG := 'N';
3846 END IF;
3847
3848 IF l_AMOUNT_INCLUDES_TAX_FLAG = 'Y' THEN
3849 l_total_amt := 0;
3850 l_Tax_Value := 0;
3851 Else
3852 l_total := l_sub_total + l_Tax_Value;
3853 l_total_amt := l_total;
3854 END IF;
3855
3856 If sub_line_rec.id is not null THEN
3857 OPEN cur_oks_lines(sub_line_rec.id);
3858 FETCH cur_oks_lines INTO l_oks_lines_rec;
3859 IF cur_oks_lines%NOTFOUND then
3860 CLOSE cur_oks_lines;
3861 x_return_status := 'E';
3862 RAISE G_EXCEPTION_HALT_VALIDATION;
3863 ELSE
3864 CLOSE cur_oks_lines;
3865 END IF;
3866
3867 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
3868 l_klnv_tbl_in(1).tax_amount := l_Tax_Value;
3869 l_klnv_tbl_in(1).tax_inclusive_yn := l_AMOUNT_INCLUDES_TAX_FLAG;
3870 l_klnv_tbl_in(1).OBJECT_VERSION_NUMBER := l_oks_lines_rec.OBJECT_VERSION_NUMBER;
3871
3872 --Update oks lines
3873
3874 oks_contract_line_pub.update_line
3875 (
3876 p_api_version => l_api_version,
3877 p_init_msg_list => l_init_msg_list,
3878 x_return_status => l_return_status,
3879 x_msg_count => l_msg_count,
3880 x_msg_data => l_msg_data,
3881 p_klnv_tbl => l_klnv_tbl_in,
3882 x_klnv_tbl => l_klnv_tbl_out,
3883 p_validate_yn => 'N'
3884 );
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
3903 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3904 End If;
3905 /*
3906 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3907 fnd_msg_pub.initialize;
3908 OKC_API.SET_MESSAGE(
3909 p_app_name => G_APP_NAME_OKS,
3910 p_msg_name => 'OKS_RECALCULATE_TAX_SUCCESS',
3911 p_token1 => 'ATTRIBUTE',
3912 p_token1_value => 'Recalculate Tax');
3913
3914 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
3915 p_encoded => fnd_api.g_false);
3916 x_msg_tbl(l_tot_msg_count).status := 'S';
3917 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3918 l_tot_msg_count := l_tot_msg_count + 1;
3919 l_msg_data := NULL;
3920 END IF;
3921 */
3922
3923 END IF; --sub_line_rec.id is not null chk
3924 End Loop; --sub_line_rec
3925
3926 --
3927 -- Bug 4717842 --
3928 -- Update Lines Level Tax Amount --
3929
3930 OPEN get_topline_tax_amt_csr(l_cle_id);
3931 FETCH get_topline_tax_amt_csr INTO l_line_tax_amt;
3932 CLOSE get_topline_tax_amt_csr;
3933
3934 OPEN cur_oks_lines(l_cle_id);
3935 FETCH cur_oks_lines INTO l_oks_lines_rec;
3936 close cur_oks_lines;
3937
3938 l_klnv_tbl_in(1).ID := l_oks_lines_rec.id;
3939 l_klnv_tbl_in(1).tax_amount := l_line_tax_amt.tax_amount;
3940 l_klnv_tbl_in(1).tax_inclusive_yn := l_oks_lines_rec.tax_inclusive_yn;
3941 l_klnv_tbl_in(1).OBJECT_VERSION_NUMBER := l_oks_lines_rec.OBJECT_VERSION_NUMBER;
3942 oks_contract_line_pub.update_line
3943 (
3944 p_api_version => l_api_version,
3945 p_init_msg_list => l_init_msg_list,
3946 x_return_status => l_return_status,
3947 x_msg_count => l_msg_count,
3948 x_msg_data => l_msg_data,
3949 p_klnv_tbl => l_klnv_tbl_in,
3950 x_klnv_tbl => l_klnv_tbl_out,
3951 p_validate_yn => 'N'
3952 );
3953 -- Bug 5227077 --
3954 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
3955 FOR i in 1..fnd_msg_pub.count_msg
3956 Loop
3957 fnd_msg_pub.get
3958 (
3959 p_msg_index => i,
3960 p_encoded => 'F',
3961 p_data => l_msg_data,
3962 p_msg_index_out => l_msg_index
3963 );
3964 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3965 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3966 l_tot_msg_count := l_tot_msg_count + 1;
3967 l_msg_data := NULL;
3968 End Loop;
3969 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3970 End If;
3971
3972 ELSE -- this is to check for lse_id = 46
3973 OKS_TAX_UTIL_PVT.Get_Tax
3974 (
3975 p_api_version => 1.0,
3976 p_init_msg_list => OKC_API.G_TRUE,
3977 p_chr_id => l_chr_id,
3978 p_cle_id => l_cle_id,
3979 px_rail_rec => G_RAIL_REC,
3980 x_msg_count => x_msg_count,
3981 x_msg_data => x_msg_data,
3982 x_return_status => l_return_status
3983 );
3984 -- Bug 5227077 --
3985 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
3986 FOR i in 1..fnd_msg_pub.count_msg
3987 Loop
3988 fnd_msg_pub.get
3989 (
3990 p_msg_index => i,
3991 p_encoded => 'F',
3992 p_data => l_msg_data,
3993 p_msg_index_out => l_msg_index
3994 );
3995 x_msg_tbl(l_tot_msg_count).status := l_return_status;
3996 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
3997 l_tot_msg_count := l_tot_msg_count + 1;
3998 l_msg_data := NULL;
3999 End Loop;
4000 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4001 End If;
4002 -- Bug 5227077 --
4003 l_UNIT_SELLING_PRICE := G_RAIL_REC.UNIT_SELLING_PRICE;
4004 l_QUANTITY := G_RAIL_REC.QUANTITY;
4005 l_sub_total := G_RAIL_REC.AMOUNT;
4006 l_AMOUNT_INCLUDES_TAX_FLAG := G_RAIL_REC.AMOUNT_INCLUDES_TAX_FLAG; --Rule_information5
4007 l_Tax_Code := G_RAIL_REC.TAX_CODE;
4008 l_TAX_RATE := G_RAIL_REC.TAX_RATE ;
4009 l_Tax_Value := G_RAIL_REC.TAX_VALUE; --Rule_information4
4010 l_Return_Status := l_return_status;
4011
4012 If l_AMOUNT_INCLUDES_TAX_FLAG = 'Y' THEN
4013 l_total_amt := 0;
4014 l_Tax_Value := 0;
4015 Else
4016 l_total := l_sub_total + l_Tax_Value;
4017 l_total_amt := l_total;
4018 End If;
4019
4020 l_klnv_tbl_in(1).ID := l_oks_lines_rec.ID;
4021 l_klnv_tbl_in(1).tax_amount := l_Tax_Value;
4022 l_klnv_tbl_in(1).tax_inclusive_yn := l_AMOUNT_INCLUDES_TAX_FLAG;
4023 l_klnv_tbl_in(1).OBJECT_VERSION_NUMBER := l_oks_lines_rec.OBJECT_VERSION_NUMBER;
4024
4025
4026 oks_contract_line_pub.update_line
4027 (
4028 p_api_version => l_api_version,
4029 p_init_msg_list => l_init_msg_list,
4030 x_return_status => l_return_status,
4031 x_msg_count => l_msg_count,
4032 x_msg_data => l_msg_data,
4033 p_klnv_tbl => l_klnv_tbl_in,
4034 x_klnv_tbl => l_klnv_tbl_out,
4035 p_validate_yn => 'N'
4036 );
4037
4038
4039 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4040 FOR i in 1..fnd_msg_pub.count_msg
4041 Loop
4042 fnd_msg_pub.get
4043 (
4044 p_msg_index => i,
4045 p_encoded => 'F',
4046 p_data => l_msg_data,
4047 p_msg_index_out => l_msg_index
4048 );
4049 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4050 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4051 l_tot_msg_count := l_tot_msg_count + 1;
4052 l_msg_data := NULL;
4053 End Loop;
4054 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4055 End If;
4056 /*
4057 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4058 fnd_msg_pub.initialize;
4059 OKC_API.SET_MESSAGE(
4060 p_app_name => G_APP_NAME_OKS,
4061 p_msg_name => 'OKS_RECALCULATE_TAX_SUCCESS',
4062 p_token1 => 'ATTRIBUTE',
4063 p_token1_value => 'Recalculate Tax');
4064
4065 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4066 p_encoded => fnd_api.g_false);
4067 x_msg_tbl(l_tot_msg_count).status := 'S';
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
4072
4073 END IF;
4074 */
4075 END IF; --lse_id
4076
4077 -- Update header level tax amount
4078
4079 OPEN get_hdr_tax_amt_csr(l_chr_id);
4080 FETCH get_hdr_tax_amt_csr INTO l_hdr_tax_amt;
4081 CLOSE get_hdr_tax_amt_csr;
4082
4083 l_khrv_tbl_type_in(1).id := l_oks_headers_rec.id;
4084 l_khrv_tbl_type_in(1).tax_amount := l_hdr_tax_amt.tax_amount;
4085 l_khrv_tbl_type_in(1).object_version_number := l_oks_headers_rec.object_version_number;
4086
4087 oks_contract_hdr_pub.update_header(
4088 p_api_version => l_api_version,
4089 p_init_msg_list => l_init_msg_list,
4090 x_return_status => l_return_status,
4091 x_msg_count => l_msg_count,
4092 x_msg_data => l_msg_data,
4093 p_khrv_tbl => l_khrv_tbl_type_in,
4094 x_khrv_tbl => l_khrv_tbl_type_out,
4095 p_validate_yn => 'N');
4096 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4097 FOR i in 1..fnd_msg_pub.count_msg
4098 Loop
4099 fnd_msg_pub.get
4100 (
4101 p_msg_index => i,
4102 p_encoded => 'F',
4103 p_data => l_msg_data,
4104 p_msg_index_out => l_msg_index
4105 );
4106 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4107 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4108 l_tot_msg_count := l_tot_msg_count + 1;
4109 l_msg_data := NULL;
4110 End Loop;
4111 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4112 End If;
4113 -- Bug 5227077 --
4114
4115
4116 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4117 fnd_msg_pub.initialize;
4118 OKC_API.SET_MESSAGE(
4119 p_app_name => G_APP_NAME_OKS,
4120 p_msg_name => 'OKS_RECALCULATE_TAX_SUCCESS',
4121 p_token1 => 'ATTRIBUTE',
4122 p_token1_value => 'Recalculate Tax');
4123
4124 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4125 p_encoded => fnd_api.g_false);
4126 x_msg_tbl(l_tot_msg_count).status := 'S';
4127 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4128 l_tot_msg_count := l_tot_msg_count + 1;
4129 l_msg_data := NULL;
4130 END IF;
4131
4132 -- Update Header Level Tax Amount --
4133 -- Bug 4717842 --
4134 END IF;
4135
4136 --Fix for Bug#3635291;
4137 OKS_BILL_SCH.Cascade_Dates_SLL
4138 (
4139 p_top_line_id => l_cle_id,
4140 x_return_status => l_return_status,
4141 x_msg_count => l_msg_count,
4142 x_msg_data => l_msg_data);
4143 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4144
4145 FOR i in 1..fnd_msg_pub.count_msg
4146 Loop
4147 fnd_msg_pub.get
4148 (
4149 p_msg_index => i,
4150 p_encoded => 'F',
4151 p_data => l_msg_data,
4152 p_msg_index_out => l_msg_index
4153 );
4154
4155 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4156 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4157 l_tot_msg_count := l_tot_msg_count + 1;
4158 l_msg_data := NULL;
4159 End Loop;
4160 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4161
4162 End If;
4163
4164 --Fix for Bug#3635291;
4165
4166 exit when i=header_lines_tbl.last;
4167 i:=header_lines_tbl.next(i);
4168 END LOOP;
4169
4170 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4171 p_encoded => fnd_api.g_false);
4172
4173 /*
4174 If l_okc_lines_rec.lse_id <> '14' then
4175
4176 WHILE l_msg_data IS NOT NULL
4177 LOOP
4178 IF x_msg_tbl.count=0 THEN
4179 l_tot_msg_count:=1 ;
4180 ELSE
4181 l_tot_msg_count := x_msg_tbl.count + 1;
4182 END IF;
4183 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4184 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4185
4186 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_next,
4187 p_encoded => fnd_api.g_false
4188 );
4189 END LOOP;
4190 End If;
4191
4192 */
4193 End If;
4194 Exception
4195 When G_EXCEPTION_HALT_VALIDATION Then
4196 x_return_status := l_return_status;
4197 x_msg_data := l_msg_data;
4198 When Others Then
4199 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4200 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
4201 x_msg_data := l_msg_data;
4202 END Default_header_to_lines;
4203
4204 ----------------------------------------------------------------------------------------------
4205
4206 PROCEDURE Default_lines_to_sublines
4207 (lines_sublines_tbl IN lines_sublines_tbl_type
4208 ,X_return_status OUT NOCOPY Varchar2
4209 ,x_msg_tbl IN OUT NOCOPY attr_msg_tbl_type) IS
4210
4211 Cursor cur_line_dates(p_cle_id in NUMBER) is
4212 select start_date , end_date, lse_id
4213 from okc_k_lines_v
4214 where id = p_cle_id;
4215 line_dates_rec cur_line_dates%ROWTYPE;
4216
4217 Cursor cur_okc_lines(p_cle_id in NUMBER) is
4218 select
4219 id,
4220 line_number,
4221 line_renewal_type_code
4222 from okc_k_lines_b
4223 where id = p_cle_id;
4224 l_okc_lines_rec cur_okc_lines%ROWTYPE;
4225
4226 Cursor cur_oks_lines(p_cle_id in NUMBER) is
4227 select
4228 id,
4229 sfwt_flag,
4230 object_version_number,
4231 inv_print_flag,
4232 price_uom, -- 8/23/05 hkamdar R12 Partial Period
4233 invoice_text,
4234 standard_cov_yn -- Issue# 14 Bug 4566346
4235 from oks_k_lines_v
4236 where cle_id = p_cle_id;
4237 l_oks_lines_rec cur_oks_lines%ROWTYPE;
4238 l_oks_sub_lines_rec cur_oks_lines%ROWTYPE;
4239
4240
4241 Cursor cur_sub_line(p_cle_id in NUMBER) IS
4242 select id,lse_id
4243 from okc_k_lines_v
4244 where cle_id =p_cle_id
4245 and chr_id is null
4246 and lse_id in (7,8,9,10,11,13,18,25,35)
4247 and date_cancelled is null; --[llc]
4248
4249 l_sub_line_rec cur_sub_line%ROWTYPE;
4250
4251 Cursor cur_sub_line_lse(p_subline_id in NUMBER) IS
4252 select id,
4253 lse_id,
4254 line_number,
4255 date_terminated -- new
4256 from okc_k_lines_v
4257 where id =p_subline_id;
4258
4259 l_sub_line_lse_rec cur_sub_line_lse%ROWTYPE;
4260
4261
4262
4263 i NUMBER;
4264 l_chr_id NUMBER;
4265 --l_cle_id NUMBER;
4266 l_lse_id NUMBER;
4267 l_subline_id NUMBER;
4268 p_subline_id NUMBER;
4269 l_line_irt VARCHAR2(150) ;
4270 l_line_renewal VARCHAR2(150);
4271 l_line_inv_print VARCHAR2(150) ;
4272 l_line_dates VARCHAR2(150) ;
4273 l_line_cov_eff VARCHAR2(150) ;
4274 l_api_version Number := 1.0;
4275 l_init_msg_list Varchar2(1) := 'F';
4276 l_msg_count Number;
4277 l_msg_data Varchar2(1000);
4278 l_return_status Varchar2(1) := 'S';
4279 l_tot_msg_count NUMBER:=0;
4280 l_cle_id_old NUMBER:=0;
4281
4282 l_can_id NUMBER;
4283 p_klnv_rec oks_kln_pvt.klnv_rec_type;
4284 l_klnv_rec oks_kln_pvt.klnv_rec_type := p_klnv_rec;
4285 p_klnv_rec_in oks_kln_pvt.klnv_rec_type;
4286 l_klnv_rec_in oks_kln_pvt.klnv_rec_type := p_klnv_rec_in;
4287
4288 p_klnv_rec_out oks_kln_pvt.klnv_rec_type;
4289 l_klnv_rec_out oks_kln_pvt.klnv_rec_type := p_klnv_rec_out;
4290
4291 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
4292 l_clev_tbl_out okc_contract_pub.clev_tbl_type;
4293 l_cle_id NUMBER;
4294 p_cle_id NUMBER;
4295
4296 l_error_tbl OKC_API.ERROR_TBL_TYPE;
4297 p_klnv_tbl oks_kln_pvt.klnv_tbl_type;
4298 l_klnv_tbl oks_kln_pvt.klnv_tbl_type := p_klnv_tbl;
4299 x_klnv_tbl oks_kln_pvt.klnv_tbl_type;
4300 l_klnv_tbl_in oks_contract_line_pub.klnv_tbl_type;
4301 l_klnv_tbl_out oks_contract_line_pub.klnv_tbl_type;
4302 l_msg_index NUMBER;
4303 l_flag BOOLEAN;
4304 l_line_number VARCHAR2 (150);
4305 l_sub_line_number VARCHAR2 (150);
4306 l_sub_line_seq_number VARCHAR2 (310);
4307 x_msg_data VARCHAR2(2000);
4308
4309 -- GCHADHA --
4310 -- BUG 4093005 --
4311 l_line_id_tbl OKS_ATTR_DEFAULTS_PVT.lines_id_tbl_type;
4312 l_id NUMBER;
4313 l_count NUMBER;
4314 -- END GCHADHA --
4315
4316 l_line_price_uom VARCHAR2 (150); -- 8/23/05 hkamdar R12 Partial Period
4317
4318
4319 l_input_details OKS_QP_PKG.INPUT_DETAILS;
4320 l_output_details OKS_QP_PKG.PRICE_DETAILS;
4321 l_modif_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
4322 l_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
4323 l_status_tbl oks_qp_int_pvt.PRICING_STATUS_TBL;
4324
4325 l_bill_y_n VARCHAR2(1);
4326 l_disp_warning VARCHAR2(1) :='N';
4327
4328 PROCEDURE sublines_common_attributes
4329 (p_line_irt IN VARCHAR2
4330 ,p_line_renewal IN VARCHAR2
4331 ,p_line_inv_print IN VARCHAR2
4332 ,p_line_dates IN VARCHAR2
4333 ,p_subline_id IN NUMBER
4334 ,p_price_uom IN VARCHAR2 -- 8/23/05 hkamdar R12 Partial Period
4335 ,x_return_status OUT NOCOPY VARCHAR2
4336 ) IS
4337
4338 BEGIN
4339 l_return_status := 'S';
4340 OPEN cur_oks_lines(p_subline_id);
4341 FETCH cur_oks_lines INTO l_oks_sub_lines_rec;
4342 CLOSE cur_oks_lines;
4343 --new
4344 OPEN cur_sub_line_lse(p_subline_id);
4345 FETCH cur_sub_line_lse INTO l_sub_line_lse_rec;
4346 CLOSE cur_sub_line_lse;
4347 --new
4348
4349
4350
4351 IF l_line_irt is not null then
4352
4353 l_klnv_tbl_in(1).ID := l_oks_sub_lines_rec.id;--l_subline_id;
4354 l_klnv_tbl_in(1).invoice_text := l_oks_lines_rec.invoice_text;
4355 l_klnv_tbl_in(1).object_version_number := l_oks_sub_lines_rec.object_version_number;
4356
4357 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4358 OKC_API.SET_MESSAGE(
4359 p_app_name => G_APP_NAME_OKS,
4360 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4361 p_token1 => 'ATTRIBUTE',
4362 p_token1_value => 'Invoicing Text');
4363
4364 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4365 p_encoded => fnd_api.g_false);
4366 x_msg_tbl(l_tot_msg_count).status := 'S';
4367 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4368 l_tot_msg_count := l_tot_msg_count + 1;
4369 l_msg_data := NULL;
4370 fnd_msg_pub.initialize;
4371
4372 END IF;
4373
4374 END IF;
4375 -------------------------------------------------------------------
4376
4377 IF l_line_renewal IS NOT NULL THEN
4378
4379 l_clev_tbl_in(1).id := l_subline_id;
4380 l_clev_tbl_in(1).line_renewal_type_code := l_okc_lines_rec.line_renewal_type_code;
4381
4382 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4383 OKC_API.SET_MESSAGE(
4384 p_app_name => G_APP_NAME_OKS,
4385 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4386 p_token1 => 'ATTRIBUTE',
4387 p_token1_value => 'Renewal');
4388
4389 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4390 p_encoded => fnd_api.g_false);
4391 x_msg_tbl(l_tot_msg_count).status := 'S';
4392 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4393 l_tot_msg_count := l_tot_msg_count + 1;
4394 l_msg_data := NULL;
4395 fnd_msg_pub.initialize;
4396
4397
4398
4399 END IF;
4400 END IF;
4401
4402 If l_line_dates is not null then
4403
4404 l_sub_line_number := l_sub_line_lse_rec.line_number;
4405 l_line_number := l_okc_lines_rec.line_number;
4406 l_sub_line_seq_number := (l_line_number || '.' || l_sub_line_number);
4407
4408 If oks_extwar_util_pvt.check_already_billed(
4409 p_chr_id => null,
4410 p_cle_id => l_cle_id,
4411 p_lse_id => line_dates_rec.lse_id,
4412 p_end_date => null) Then
4413 fnd_msg_pub.initialize;
4414 validate_date
4415
4416 (p_api_version => l_api_version
4417 ,p_init_msg_list => l_init_msg_list
4418 ,p_hdr_id => NULL
4419 ,p_top_line_id => l_cle_id
4420 ,p_sub_line_id => l_subline_id
4421 ,x_return_status => l_return_status
4422 ,x_msg_count => l_msg_count
4423 ,x_msg_data => l_msg_data
4424 ,x_flag => l_flag);
4425
4426
4427 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4428 FOR i in 1..fnd_msg_pub.count_msg
4429 Loop
4430 fnd_msg_pub.get
4431 (
4432 p_msg_index => i,
4433 p_encoded => 'F',
4434 p_data => l_msg_data,
4435 p_msg_index_out => l_msg_index
4436 );
4437 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4438 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4439 l_tot_msg_count := l_tot_msg_count + 1;
4440 l_msg_data := NULL;
4441 End Loop;
4442 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4443 Else
4444 If l_flag <> TRUE then
4445 fnd_msg_pub.initialize;
4446 OKC_API.SET_MESSAGE(
4447 p_app_name => 'OKS', --G_APP_NAME_OKS,
4448 p_msg_name => 'OKS_BA_UPDATE_NOT_ALLOWED',
4449 p_token1 => 'Sub Line No ',
4450 p_token1_value => l_sub_line_seq_number);
4451
4452
4453 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4454 p_encoded => fnd_api.g_false);
4455 x_msg_tbl(l_tot_msg_count).status := 'E';
4456 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4457 l_tot_msg_count := l_tot_msg_count + 1;
4458 l_msg_data := NULL;
4459 fnd_msg_pub.initialize;
4460 Raise G_EXCEPTION_HALT_VALIDATION;
4461
4462 End If;
4463 End If;
4464 End If;
4465
4466 --new
4467 OPEN cur_line_dates(l_cle_id );
4468 FETCH cur_line_dates INTO line_dates_rec;
4469 CLOSE cur_line_dates;
4470
4471
4472 l_clev_tbl_in(1).id := l_subline_id;
4473 l_clev_tbl_in(1).chr_id := NULL;
4474 l_clev_tbl_in(1).cle_id := l_cle_id;
4475 l_clev_tbl_in(1).start_date := line_dates_rec.start_date;
4476 l_clev_tbl_in(1).end_date := line_dates_rec.end_date;
4477 l_clev_tbl_in(1).dnz_chr_id := l_chr_id;
4478 If line_dates_rec.lse_id = '14' Then
4479 If l_clev_tbl_in(1).start_date > SYSDATE THen
4480 l_clev_tbl_in(1).sts_code := 'SIGNED';
4481 Elsif l_clev_tbl_in(1).start_date <= SYSDATE And l_clev_tbl_in(1).end_date >= SYSDATE THEN
4482 l_clev_tbl_in(1).sts_code := 'ACTIVE';
4483 ELSIF l_clev_tbl_in(1).end_date < SYSDATE Then
4484 l_clev_tbl_in(1).sts_code :='EXPIRED';
4485 End if;
4486 End if;
4487
4488 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4489 -- Issue a warning message if dates are cascaded from lines to sublines for a fully billed contract.
4490 IF(l_disp_warning ='Y') THEN
4491 OKC_API.SET_MESSAGE(
4492 p_app_name => G_APP_NAME_OKS,
4493 p_msg_name => 'OKS_LINES_CASCADE_DATES_WARN');
4494 x_msg_tbl(l_tot_msg_count).status := 'W';
4495
4496 ELSE
4497
4498 OKC_API.SET_MESSAGE(
4499 p_app_name => G_APP_NAME_OKS,
4500 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4501 p_token1 => 'ATTRIBUTE',
4502 p_token1_value => 'Date');
4503 x_msg_tbl(l_tot_msg_count).status := 'S';
4504
4505 END IF;
4506 END IF;
4507 ----new
4508 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4509 p_encoded => fnd_api.g_false);
4510 -- x_msg_tbl(l_tot_msg_count).status := 'S';
4511 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4512 l_tot_msg_count := l_tot_msg_count + 1;
4513 l_msg_data := NULL;
4514 fnd_msg_pub.initialize;
4515 ----new
4516
4517 END IF;
4518 -------------------------------------------------------------------
4519 IF l_line_inv_print IS NOT NULL THEN
4520
4521 l_klnv_tbl_in(1).ID := l_oks_sub_lines_rec.id;--l_subline_id;
4522 l_klnv_tbl_in(1).inv_print_flag := l_oks_lines_rec.inv_print_flag;
4523 l_klnv_tbl_in(1).object_version_number := l_oks_sub_lines_rec.object_version_number;
4524
4525 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4526 OKC_API.SET_MESSAGE(
4527 p_app_name => G_APP_NAME_OKS,
4528 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4529 p_token1 => 'ATTRIBUTE',
4530 p_token1_value => 'Invoicing Print Flag');
4531 END IF;
4532
4533 ----new
4534 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4535 p_encoded => fnd_api.g_false);
4536 x_msg_tbl(l_tot_msg_count).status := 'S';
4537 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4538 l_tot_msg_count := l_tot_msg_count + 1;
4539 l_msg_data := NULL;
4540 fnd_msg_pub.initialize;
4541 ----new
4542
4543
4544
4545 END IF;
4546
4547 -------------------------------------------------------------------
4548 -- 8/23/2005 hkamdar R12 Partial Period
4549 IF l_line_price_uom is not null THEN
4550 If l_sub_line_lse_rec.lse_id IN (7,9,25)
4551 AND OKS_AUTH_UTIL_PVT.Is_Line_Eligible(p_api_version => 1.0,
4552 p_init_msg_list => 'T',
4553 p_contract_hdr_id => l_chr_id,
4554 p_contract_line_id => l_subline_id,
4555 p_price_list_id => '',
4556 p_intent => 'S',
4557 x_msg_count => l_msg_count,
4558 x_msg_data => l_msg_data)
4559 THEN
4560 -- Covered Item, Product, Convered product in extended warraties
4561 l_klnv_tbl_in(1).ID := l_oks_sub_lines_rec.id;--l_subline_id;
4562 l_klnv_tbl_in(1).price_uom := l_oks_lines_rec.price_uom;
4563 l_klnv_tbl_in(1).object_version_number := l_oks_sub_lines_rec.object_version_number;
4564
4565 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4566 OKC_API.SET_MESSAGE(
4567 p_app_name => G_APP_NAME_OKS,
4568 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4569 p_token1 => 'ATTRIBUTE',
4570 p_token1_value => 'Price UOM');
4571
4572 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4573 p_encoded => fnd_api.g_false);
4574 x_msg_tbl(l_tot_msg_count).status := 'S';
4575 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4576 l_tot_msg_count := l_tot_msg_count + 1;
4577 l_msg_data := NULL;
4578 fnd_msg_pub.initialize;
4579 END IF;
4580 Else
4581 /* OKC_API.SET_MESSAGE(
4582 p_app_name => G_APP_NAME_OKS,
4583 p_msg_name => 'OKS_LINE_CASCADE_ERROR',
4584 p_token1 => 'ATTRIBUTE',
4585 p_token1_value => 'Price UOM');
4586
4587 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
4588 p_encoded => fnd_api.g_false);
4589 ----errorout_gsi('Subline_common_attribute UOM msg '||l_msg_data);
4590 x_msg_tbl(l_tot_msg_count).status := 'E';
4591 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4592 l_tot_msg_count := l_tot_msg_count + 1;
4593 l_msg_data := NULL;
4594 fnd_msg_pub.initialize; */
4595 null;
4596 End If;
4597 END IF;
4598 --new
4599 Exception
4600 When G_EXCEPTION_HALT_VALIDATION Then
4601 x_return_status := 'E';
4602 x_msg_data := l_msg_data;
4603 When Others Then
4604 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4605 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
4606 x_msg_data := l_msg_data;
4607
4608 --new
4609
4610 END sublines_common_attributes;
4611
4612 Begin
4613 fnd_msg_pub.initialize;
4614
4615 IF NOT lines_sublines_tbl.COUNT=0 THEN
4616 i:=lines_sublines_tbl.FIRST;
4617 -- GCHADHA --
4618 -- BUG 4093005 --
4619 l_id := 0;
4620 l_count := 0;
4621 -- END GCHADHA --
4622 --FOR i IN lines_sublines_tbl.FIRST .. lines_sublines_tbl.LAST
4623 LOOP
4624 l_chr_id := lines_sublines_tbl(i).chr_id;
4625 l_cle_id := lines_sublines_tbl(i).cle_id;
4626 l_subline_id := lines_sublines_tbl(i).subline_id;
4627 l_line_irt := lines_sublines_tbl(i).line_irt;
4628 l_line_renewal := lines_sublines_tbl(i).line_renewal;
4629 l_line_inv_print := lines_sublines_tbl(i).line_inv_print;
4630 l_line_dates := lines_sublines_tbl(i).line_dates;
4631 l_line_cov_eff := lines_sublines_tbl(i).line_cov_eff;
4632 l_line_price_uom := lines_sublines_tbl(i).price_uom; -- 8/23/05 hkamdar R12 Partial Period
4633 -- errorout_vg('Line_Price Uom ' || lines_sublines_tbl(i).price_uom);
4634
4635 -- GCHADHA --
4636 -- BUG 4093005 --
4637 IF l_id <> l_cle_id THEN
4638 l_line_id_tbl(l_count).id := l_cle_id;
4639 l_id := l_cle_id;
4640 l_count :=l_count + 1;
4641 END IF;
4642 -- END GCHADHA --
4643
4644 l_klnv_tbl_in.DELETE;
4645 l_clev_tbl_in.DELETE;
4646
4647 -- check if the contract has been imported and fully billed at source
4648 IF(l_chr_id IS NOT NULL) THEN
4649 select billed_at_source INTO l_bill_y_n FROM okc_k_headers_all_b where id = l_chr_id ;
4650 END IF;
4651
4652 IF ((l_bill_y_n IS NOT NULL) AND (l_bill_y_n = 'Y')) THEN
4653 l_disp_warning :='Y';
4654 ELSE
4655 l_disp_warning :='N';
4656 END IF;
4657
4658
4659
4660 p_cle_id := l_cle_id;
4661 OPEN cur_oks_lines(p_cle_id);
4662 FETCH cur_oks_lines INTO l_oks_lines_rec;
4663 CLOSE cur_oks_lines;
4664
4665 OPEN cur_okc_lines(p_cle_id);
4666 FETCH cur_okc_lines INTO l_okc_lines_rec;
4667 CLOSE cur_okc_lines;
4668 --new
4669 OPEN cur_sub_line_lse(l_subline_id);
4670 FETCH cur_sub_line_lse INTO l_sub_line_lse_rec;
4671 CLOSE cur_sub_line_lse;
4672 --new
4673
4674 If l_line_dates is not null then
4675
4676 IF l_cle_id_old <> l_cle_id AND nvl(l_oks_lines_rec.standard_cov_yn, 'N') <> 'Y' Then
4677 ----errorout_gsi('Line date is not null, calling update_coverage_effectivity');
4678 OPEN cur_line_dates(l_cle_id );
4679 FETCH cur_line_dates INTO line_dates_rec;
4680 CLOSE cur_line_dates;
4681 OKS_COVERAGES_PVT.Update_COVERAGE_Effectivity
4682 (
4683 p_api_version => l_api_version,
4684 p_init_msg_list => l_init_msg_list,
4685 x_return_status => l_return_status,
4686 x_msg_count => l_msg_count,
4687 x_msg_data => l_msg_data,
4688 p_service_Line_Id => l_cle_id,
4689 p_New_Start_Date => line_dates_rec.start_date,
4690 p_New_End_Date => line_dates_rec.end_date );
4691
4692 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4693 FOR i in 1..fnd_msg_pub.count_msg
4694 Loop
4695 fnd_msg_pub.get
4696 (
4697 p_msg_index => i,
4698 p_encoded => 'F',
4699 p_data => l_msg_data,
4700 p_msg_index_out => l_msg_index
4701 );
4702 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4703 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4704 l_tot_msg_count := l_tot_msg_count + 1;
4705 l_msg_data := NULL;
4706 End Loop;
4707 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4708 End If;
4709
4710 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4711
4712 -- Issue a warning message if dates are cascaded from lines to sublines for a fully billed contract.
4713 IF(l_disp_warning ='Y') THEN
4714
4715 OKC_API.SET_MESSAGE(
4716 p_app_name => G_APP_NAME_OKS,
4717 p_msg_name => 'OKS_LINES_CASCADE_DATES_WARN');
4718
4719 ELSE
4720
4721 OKC_API.SET_MESSAGE(
4722 p_app_name => G_APP_NAME_OKS,
4723 p_msg_name => 'OKS_LINE_CASCADE_SUCCESS',
4724 p_token1 => 'ATTRIBUTE',
4725 p_token1_value => 'Date');
4726 END IF;
4727 END IF;
4728 l_cle_id_old:=l_cle_id ;
4729 END IF;
4730
4731 END IF;
4732
4733 IF l_subline_id is not null then
4734
4735 -- errorout_vg('Calling subline_common_attributes');
4736
4737 sublines_common_attributes
4738 (p_line_irt => l_line_irt
4739 ,p_line_renewal => l_line_renewal
4740 ,p_line_inv_print => l_line_inv_print
4741 ,p_line_dates => l_line_dates
4742 ,p_subline_id => l_subline_id
4743 ,p_price_uom => l_line_price_uom -- 8/23/05 hkamdar R12 Partial Period
4744 ,x_return_status => l_return_status
4745 );
4746 -- Bug 5227077 --
4747 if not(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4748
4749 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4750 end if;
4751 -- Bug 5227077 --
4752
4753
4754 --errorout_gsi('After sublines_common_attributes status '||l_return_status);
4755 --new
4756 IF l_line_irt IS NOT NULL or
4757 l_line_inv_print IS NOT NULL or
4758 l_line_price_uom IS NOT NULL THEN -- 8/23/05 hkamdar R12 Partial Period Added new condition
4759
4760 --Update oks lines
4761 -- errorout_vg('Calling oks_contract_line_pub.update_line');
4762 oks_contract_line_pub.update_line
4763 (
4764 p_api_version => l_api_version,
4765 p_init_msg_list => l_init_msg_list,
4766 x_return_status => l_return_status,
4767 x_msg_count => l_msg_count,
4768 x_msg_data => l_msg_data,
4769 p_klnv_tbl => l_klnv_tbl_in,
4770 x_klnv_tbl => l_klnv_tbl_out,
4771 p_validate_yn => 'N'
4772 );
4773
4774 -- errorout_vg('After oks_contract_line_pub.update_line status '||l_return_status);
4775 --errorout_gsi('After oks_contract_line_pub.update_line Message '||l_msg_data);
4776
4777 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4778
4779 FOR i in 1..fnd_msg_pub.count_msg
4780 Loop
4781 fnd_msg_pub.get
4782 (
4783 p_msg_index => i,
4784 p_encoded => 'F',
4785 p_data => l_msg_data,
4786 p_msg_index_out => l_msg_index
4787 );
4788
4789 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4790 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4791 l_tot_msg_count := l_tot_msg_count + 1;
4792 l_msg_data := NULL;
4793 End Loop;
4794 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4795 End If;
4796
4797 END IF;
4798
4799 -------------------------------------------------------------------
4800 IF l_line_renewal IS NOT NULL OR
4801 l_line_dates IS NOT NULL OR
4802 l_line_cov_eff IS NOT NULL THEN
4803 --errorout_gsi('Calling local update_line');
4804 update_line(
4805 p_clev_tbl => l_clev_tbl_in,
4806 x_clev_tbl => l_clev_tbl_out,
4807 x_return_status => l_return_status,
4808 x_msg_count => l_msg_count,
4809 x_msg_data => l_msg_data
4810 );
4811
4812 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4813 FOR i in 1..fnd_msg_pub.count_msg
4814 Loop
4815 fnd_msg_pub.get
4816 (
4817 p_msg_index => i,
4818 p_encoded => 'F',
4819 p_data => l_msg_data,
4820 p_msg_index_out => l_msg_index
4821 );
4822
4823 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4824 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4825 l_tot_msg_count := l_tot_msg_count + 1;
4826 l_msg_data := NULL;
4827 End Loop;
4828 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4829
4830 End If;
4831
4832 END IF;
4833 --new
4834
4835 ELSIF l_subline_id is null then
4836 -- errorout_vg('l_subline_id is null');
4837
4838 OPEN cur_sub_line(p_cle_id);
4839 LOOP
4840 FETCH cur_sub_line INTO l_sub_line_rec;
4841 l_subline_id := l_sub_line_rec.id;
4842
4843 EXIT WHEN cur_sub_line%NOTFOUND;
4844
4845 l_klnv_tbl_in.DELETE;
4846 l_clev_tbl_in.DELETE;
4847 -- GCHADHA --
4848 -- NCR WARRANTY CASCADE DATE MINOR ENHANCEMENT --
4849 -- 5/19/2005 --
4850
4851 IF l_sub_line_rec.lse_id = 18
4852 THEN
4853
4854 --new
4855 OPEN cur_sub_line_lse(l_subline_id);
4856 FETCH cur_sub_line_lse INTO l_sub_line_lse_rec;
4857 CLOSE cur_sub_line_lse;
4858 --new
4859 IF l_sub_line_lse_rec.date_terminated IS NULL
4860 THEN
4861 -- errorout_vg('Calling subline_common_attributes Second');
4862 sublines_common_attributes
4863 (p_line_irt => l_line_irt
4864 ,p_line_renewal => l_line_renewal
4865 ,p_line_inv_print => l_line_inv_print
4866 ,p_line_dates => l_line_dates
4867 ,p_subline_id => l_subline_id
4868 ,p_price_uom => l_line_price_uom -- 8/23/05 hkamdar R12 Partial Period
4869 ,x_return_status => l_return_status
4870 );
4871 --- errorout_vg('After sublines_common_attributes second status '||l_return_status);
4872 FOR i in 1..fnd_msg_pub.count_msg
4873 Loop
4874 fnd_msg_pub.get
4875 (
4876 p_msg_index => i,
4877 p_encoded => 'F',
4878 p_data => l_msg_data,
4879 p_msg_index_out => l_msg_index
4880 );
4881
4882 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4883 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4884 l_tot_msg_count := l_tot_msg_count + 1;
4885 l_msg_data := NULL;
4886 End Loop;
4887 IF ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4888 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4889 End If;
4890 END IF ;
4891 ELSE
4892 -- errorout_vg('Calling subline_common_attributes Third');
4893 sublines_common_attributes
4894 (p_line_irt => l_line_irt
4895 ,p_line_renewal => l_line_renewal
4896 ,p_line_inv_print => l_line_inv_print
4897 ,p_line_dates => l_line_dates
4898 ,p_subline_id => l_subline_id
4899 ,p_price_uom => l_line_price_uom -- 8/23/05 hkamdar R12 Partial Period
4900 ,x_return_status => l_return_status
4901 );
4902 -- errorout_vg('After sublines_common_attributes Third status '||l_return_status);
4903 FOR i in 1..fnd_msg_pub.count_msg
4904 Loop
4905 fnd_msg_pub.get
4906 (
4907 p_msg_index => i,
4908 p_encoded => 'F',
4909 p_data => l_msg_data,
4910 p_msg_index_out => l_msg_index
4911 );
4912
4913 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4914 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4915 l_tot_msg_count := l_tot_msg_count + 1;
4916 l_msg_data := NULL;
4917 End Loop;
4918 IF ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4919 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4920 End If;
4921 END IF;
4922
4923 -- END GCAHDHA --
4924 IF l_line_irt IS NOT NULL or
4925 l_line_inv_print IS NOT NULL OR
4926 l_line_price_uom IS NOT NULL THEN -- 8/23/05 hkamdar R12 Partial Period Added new condition
4927
4928
4929 --Update oks lines
4930 --errorout_gsi('Calling oks_contract_line_pub.update_line Second');
4931 oks_contract_line_pub.update_line
4932 (
4933 p_api_version => l_api_version,
4934 p_init_msg_list => l_init_msg_list,
4935 x_return_status => l_return_status,
4936 x_msg_count => l_msg_count,
4937 x_msg_data => l_msg_data,
4938 p_klnv_tbl => l_klnv_tbl_in,
4939 x_klnv_tbl => l_klnv_tbl_out,
4940 p_validate_yn => 'N'
4941 );
4942
4943 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4944 FOR i in 1..fnd_msg_pub.count_msg
4945 Loop
4946 fnd_msg_pub.get
4947 (
4948 p_msg_index => i,
4949 p_encoded => 'F',
4950 p_data => l_msg_data,
4951 p_msg_index_out => l_msg_index
4952 );
4953
4954 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4955 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4956 l_tot_msg_count := l_tot_msg_count + 1;
4957 l_msg_data := NULL;
4958 End Loop;
4959 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4960
4961 End If;
4962
4963 END IF;
4964
4965 IF l_line_renewal IS NOT NULL OR
4966 l_line_dates IS NOT NULL OR
4967 l_line_cov_eff IS NOT NULL THEN
4968
4969 --errorout_gsi('Calling local update_line Second');
4970
4971 update_line(
4972 p_clev_tbl => l_clev_tbl_in,
4973 x_clev_tbl => l_clev_tbl_out,
4974 x_return_status => l_return_status,
4975 x_msg_count => l_msg_count,
4976 x_msg_data => l_msg_data
4977 );
4978 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
4979 FOR i in 1..fnd_msg_pub.count_msg
4980 Loop
4981 fnd_msg_pub.get
4982 (
4983 p_msg_index => i,
4984 p_encoded => 'F',
4985 p_data => l_msg_data,
4986 p_msg_index_out => l_msg_index
4987 );
4988
4989 x_msg_tbl(l_tot_msg_count).status := l_return_status;
4990 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
4991 l_tot_msg_count := l_tot_msg_count + 1;
4992 l_msg_data := NULL;
4993 End Loop;
4994 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4995 End If;
4996
4997 END IF;
4998 -- errorout_vg('I -- Before Calling oks_qp_int_pvt.compute_Price lse_id ' || l_sub_line_rec.lse_id );
4999 IF l_line_price_uom is NOT NULL and l_sub_line_rec.lse_id in (7,9,25)
5000 AND OKS_AUTH_UTIL_PVT.Is_Line_Eligible(p_api_version => 1.0,
5001 p_init_msg_list => 'T',
5002 p_contract_hdr_id => l_chr_id,
5003 p_contract_line_id => l_subline_id,
5004 p_price_list_id => '',
5005 p_intent => 'S',
5006 x_msg_count => l_msg_count,
5007 x_msg_data => l_msg_data)
5008 THEN
5009 -- errorout_vg('I -- Calling oks_qp_int_pvt.compute_Price');
5010 l_input_details.line_id := l_cle_id;
5011 l_input_details.subline_id := l_subline_id;
5012 l_input_details.intent := 'SP';
5013 oks_qp_int_pvt.compute_Price
5014 (
5015 p_api_version => 1.0,
5016 p_init_msg_list => 'T',
5017 p_detail_rec => l_input_details,
5018 x_price_details => l_output_details,
5019 x_modifier_details => l_modif_details,
5020 x_price_break_details => l_pb_details,
5021 x_return_status => l_return_status,
5022 x_msg_count => l_msg_count,
5023 x_msg_data => l_msg_data
5024 );
5025
5026 l_status_tbl := oks_qp_int_pvt.get_Pricing_Messages;
5027 IF l_status_tbl.Count > 0 Then
5028
5029 l_count:= l_status_tbl.FIRST;
5030 For i in l_status_tbl.FIRST..l_status_tbl.LAST
5031 Loop
5032 x_msg_tbl(l_count).status := l_status_tbl(i).Status_Code;
5033 x_msg_tbl(l_count).description := l_status_tbl(i).Status_Text;
5034 EXIT WHEN l_count = l_status_tbl.LAST;
5035 l_count := l_status_tbl.NEXT(l_count);
5036 End Loop;
5037 END IF;
5038 -- Bug 5381082 --
5039 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
5040 FOR i in 1..fnd_msg_pub.count_msg
5041 Loop
5042 fnd_msg_pub.get
5043 (
5044 p_msg_index => i,
5045 p_encoded => 'F',
5046 p_data => l_msg_data,
5047 p_msg_index_out => l_msg_index
5048 );
5049 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5050 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5051 l_tot_msg_count := l_tot_msg_count + 1;
5052 l_msg_data := NULL;
5053 End Loop;
5054 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5055 End If;
5056 -- Bug 5381082 --
5057 End if;
5058
5059 END LOOP;
5060 CLOSE cur_sub_line;
5061 l_subline_id := l_sub_line_rec.id;
5062
5063 End If;
5064 -- Bug 5205136 --
5065 -- errorout_vg('Before Calling oks_qp_int_pvt.compute_Price lse_id ' || l_sub_line_rec.lse_id );
5066 IF l_line_price_uom is NOT NULL and (l_sub_line_rec.lse_id in (7,9,25)
5067 OR l_sub_line_lse_rec.lse_id in (7,9,25))
5068 AND OKS_AUTH_UTIL_PVT.Is_Line_Eligible(p_api_version => 1.0,
5069 p_init_msg_list => 'T',
5070 p_contract_hdr_id => l_chr_id,
5071 p_contract_line_id => l_subline_id,
5072 p_price_list_id => '',
5073 p_intent => 'S',
5074 x_msg_count => l_msg_count,
5075 x_msg_data => l_msg_data)
5076 THEN
5077 -- Bug 5205136 --
5078 -- errorout_vg('Calling oks_qp_int_pvt.compute_Price');
5079 l_input_details.line_id := l_cle_id;
5080 l_input_details.subline_id := l_subline_id;
5081 l_input_details.intent := 'SP';
5082 oks_qp_int_pvt.compute_Price
5083 (
5084 p_api_version => 1.0,
5085 p_init_msg_list => 'T',
5086 p_detail_rec => l_input_details,
5087 x_price_details => l_output_details,
5088 x_modifier_details => l_modif_details,
5089 x_price_break_details => l_pb_details,
5090 x_return_status => l_return_status,
5091 x_msg_count => l_msg_count,
5092 x_msg_data => l_msg_data
5093 );
5094
5095 l_status_tbl := oks_qp_int_pvt.get_Pricing_Messages;
5096
5097 IF l_status_tbl.Count > 0 Then
5098
5099 l_count:= l_status_tbl.FIRST;
5100 For i in l_status_tbl.FIRST..l_status_tbl.LAST
5101 Loop
5102 x_msg_tbl(l_count).status := l_status_tbl(i).Status_Code;
5103 x_msg_tbl(l_count).description := l_status_tbl(i).Status_Text;
5104 EXIT WHEN l_count = l_status_tbl.LAST;
5105 l_count := l_status_tbl.NEXT(l_count);
5106 End Loop;
5107 END IF;
5108 -- Bug 5381082 --
5109 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
5110 FOR i in 1..fnd_msg_pub.count_msg
5111 Loop
5112 fnd_msg_pub.get
5113 (
5114 p_msg_index => i,
5115 p_encoded => 'F',
5116 p_data => l_msg_data,
5117 p_msg_index_out => l_msg_index
5118 );
5119 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5120 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5121 l_tot_msg_count := l_tot_msg_count + 1;
5122 l_msg_data := NULL;
5123 End Loop;
5124 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5125 End If;
5126 -- Bug 5381082 --
5127 End if;
5128
5129 exit when i=lines_sublines_tbl.last;
5130 i:=lines_sublines_tbl.next(i);
5131
5132 END LOOP;
5133 -- GCHADHA --
5134 -- BUG 4093005 --
5135 -- 05-JAN-2005 --
5136 IF l_line_id_tbl.COUNT > 0 THEN
5137 FOR j in l_line_id_tbl.FIRST..l_line_id_tbl.LAST LOOP
5138 OKS_BILL_SCH.Cascade_Dates_SLL
5139 (
5140 p_top_line_id => l_line_id_tbl(j).id,
5141 x_return_status => l_return_status,
5142 x_msg_count => l_msg_count,
5143 x_msg_data => l_msg_data);
5144 --errorout_gsi('After OKS_BILL_SCH.Cascade_Dates_SLL status '||l_return_status);
5145 If ( l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
5146 FOR i in 1..fnd_msg_pub.count_msg
5147 Loop
5148 fnd_msg_pub.get
5149 (
5150 p_msg_index => i,
5151 p_encoded => 'F',
5152 p_data => l_msg_data,
5153 p_msg_index_out => l_msg_index
5154 );
5155
5156 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5157 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5158 l_tot_msg_count := l_tot_msg_count + 1;
5159 l_msg_data := NULL;
5160 End Loop;
5161 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5162
5163 End If;
5164 END LOOP;
5165 END IF;
5166
5167 -- END GCHADHA --
5168
5169 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
5170 p_encoded => fnd_api.g_false);
5171 WHILE l_msg_data IS NOT NULL
5172 LOOP
5173 IF x_msg_tbl.count=0 THEN
5174 l_tot_msg_count:=1 ;
5175 ELSE
5176 l_tot_msg_count := x_msg_tbl.count + 1;
5177 END IF;
5178
5179 -- store the program results
5180
5181 x_msg_tbl(l_tot_msg_count).status := l_return_status;
5182 x_msg_tbl(l_tot_msg_count).description := l_msg_data;
5183
5184 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_next,
5185 p_encoded => fnd_api.g_false);
5186 END LOOP;
5187
5188 END IF;
5189 --new
5190 Exception
5191 When G_EXCEPTION_HALT_VALIDATION Then
5192 x_return_status := 'E';
5193 x_msg_data := l_msg_data;
5194 When Others Then
5195 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5196 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
5197 x_msg_data := l_msg_data;
5198
5199 --new
5200
5201 END Default_lines_to_sublines;
5202
5203
5204 PROCEDURE Rollback_work IS
5205 Begin
5206 Rollback;
5207 End;
5208 -- Bank Account Consolidation --
5209
5210 -- Added New Procedure Called to delete/ Create
5211 -- Credit Card details
5212 Procedure Delete_credit_Card
5213 (p_trnx_ext_id IN NUMBER,
5214 p_line_id IN NUMBER,
5215 p_party_id IN NUMBER,
5216 p_cust_account_id IN NUMBER ,
5217 x_return_status OUT NOCOPY VARCHAR2 ,
5218 x_msg_data OUT NOCOPY VARCHAR2) IS
5219
5220 l_msg_count Number;
5221 l_msg_data Varchar2(1000);
5222 l_return_status Varchar2(1) := 'S';
5223 l_payer IBY_FNDCPT_Common_Pub.PayerContext_rec_type;
5224 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
5225
5226 l_api_name CONSTANT VARCHAR2(30) := 'Delete_credit_Card';
5227
5228 Begin
5229
5230 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5231 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
5232 END IF;
5233
5234 l_payer.Payment_Function :='CUSTOMER_PAYMENT';
5235 l_payer.Party_Id := p_party_id;
5236 l_payer.cust_account_id := p_cust_account_id;
5237
5238
5239 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5240 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension.');
5241 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.payment_function: ' || l_payer.payment_function);
5242 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.party_id: ' || l_payer.party_id);
5243 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.cust_account_id: ' || l_payer.cust_account_id);
5244 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'p_trnx_ext_id: ' || p_trnx_ext_id);
5245 END IF;
5246
5247
5248 IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension
5249 (
5250 p_api_version => 1.0,
5251 p_init_msg_list => FND_API.G_FALSE,
5252 p_commit => FND_API.G_FALSE,
5253 x_return_status => l_return_status,
5254 x_msg_count => l_msg_count,
5255 x_msg_data => l_msg_data,
5256 p_payer => l_payer,
5257 --p_payer_equivalency=> IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD, bug 5439978
5258 p_payer_equivalency=> IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_FULL,
5259 p_entity_id => p_trnx_ext_id,
5260 x_response => l_response
5261 );
5262
5263 x_return_status :=l_return_status; --change
5264
5265 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5266 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Finished deleting transaction extension. l_return_status: '|| l_return_status);
5267 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'x_msg_data: '|| l_msg_data);
5268 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'x_msg_count: '|| l_msg_count);
5269 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'result_code: '|| l_response.Result_Code);
5270 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Category: '|| l_response.Result_Category);
5271 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Message: '|| l_response.Result_Message);
5272 END IF;
5273
5274
5275 Exception
5276 When Others Then
5277 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5278 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
5279 x_msg_data := l_msg_data;
5280
5281 End Delete_Credit_Card;
5282
5283 Function Create_credit_Card
5284 (p_line_id IN NUMBER,
5285 p_party_id IN NUMBER,
5286 p_org IN NUMBER,
5287 p_account_site_id IN NUMBER,
5288 p_cust_account_id IN NUMBER,
5289 p_trnx_ext_id IN NUMBER,
5290 x_return_status OUT NOCOPY VARCHAR2,
5291 x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER IS
5292
5293
5294 l_payer IBY_FNDCPT_Common_Pub.PayerContext_rec_type;
5295 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
5296 l_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
5297
5298
5299
5300
5301 l_instrument_id NUMBER;
5302 l_msg_count Number;
5303 l_msg_data Varchar2(1000);
5304 l_return_status Varchar2(1) := 'S';
5305 l_entity_id NUMBER;
5306
5307 Cursor get_instru_assigned_csr(l_trnx_ext_ID IN NUMBER) IS
5308 SELECT instr_assignment_id
5309 FROM IBY_TRXN_EXTENSIONS_V
5310 WHERE TRXN_EXTENSION_ID = l_trnx_ext_id;
5311
5312 get_instru_assigned_rec get_instru_assigned_csr%ROWTYPE;
5313
5314 l_api_name CONSTANT VARCHAR2(30) := 'Create_credit_Card';
5315
5316 Begin
5317
5318 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5319 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
5320 END IF;
5321
5322
5323 l_payer.payment_function := 'CUSTOMER_PAYMENT';
5324 l_payer.party_id := p_party_id; -- Id of the Lines Level bill to Party
5325 -- GCHADHA --
5326 -- 01-SEP-2005 --
5327 /*
5328 l_payer.org_type := 'OPERATING_UNIT';
5329 l_payer.org_id := p_org; -- Organization Id
5330 l_payer.account_site_id := p_account_site_id; -- Bill to Location --
5331 */
5332 -- END GCHADHA --
5333 l_payer.cust_account_id:=p_cust_account_id; -- Cust Account Id --
5334
5335 l_trxn_attribs.Originating_Application_Id := 515; --service contracts OKS
5336 l_trxn_attribs.Order_Id := p_line_id; -- line Id
5337 -- Bug 4866090 --
5338 l_trxn_attribs.trxn_ref_number1 := to_char(SYSDATE,'ddmmyyyyhhmmssss'); --to make order id and trx ref 1 unique
5339 -- Bug 4866090 --
5340
5341 Open get_instru_assigned_csr(p_trnx_ext_id);
5342 FETCH get_instru_assigned_csr INTO get_instru_assigned_rec;
5343 CLOSE get_instru_assigned_csr;
5344
5345
5346 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5347 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Calling IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension.');
5348 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.payment_function: ' || l_payer.payment_function);
5349 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.party_id: ' || l_payer.party_id);
5350 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_payer.cust_account_id: ' || l_payer.cust_account_id);
5351 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);
5352 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_trxn_attribs.Order_Id: ' || l_trxn_attribs.Order_Id);
5353 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);
5354 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);
5355 END IF;
5356
5357
5358 -- Create Transaction Extension Id
5359 IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension
5360 (
5361 p_api_version => 1.0
5362 ,p_init_msg_list => FND_API.G_TRUE
5363 ,p_commit => FND_API.G_FALSE
5364 ,x_return_status => l_return_status
5365 ,x_msg_count => l_msg_count
5366 ,x_msg_data => l_msg_data
5367 ,p_payer => l_payer
5368 --,p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD -- UPWARD
5369 ,p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_FULL -- FULL, bug 5439978
5370 ,p_pmt_channel => IBY_FNDCPT_SETUP_PUB.G_CHANNEL_CREDIT_CARD -- CREDIT_CARD
5371 ,p_instr_assignment => get_instru_assigned_rec.instr_assignment_id
5372 ,p_trxn_attribs => l_trxn_attribs
5373 ,x_entity_id => l_entity_id -- transaction_extension_id
5374 ,x_response => l_response
5375 );
5376 x_return_status := l_return_status;
5377
5378
5379 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5380 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Finished creating transaction extension. l_return_status: '|| l_return_status);
5381 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'x_msg_data: '|| l_msg_data);
5382 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'x_msg_count: '|| l_msg_count);
5383 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'result_code: '|| l_response.Result_Code);
5384 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Category: '|| l_response.Result_Category);
5385 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Message: '|| l_response.Result_Message);
5386 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_entity_id: ' || l_entity_id);
5387 END IF;
5388
5389
5390 return(l_entity_id);
5391 Exception
5392 When Others Then
5393 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5394 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
5395 x_msg_data := l_msg_data;
5396 End Create_Credit_Card;
5397
5398
5399 -- Bank Account Consolidation --
5400 END OKS_ATTR_DEFAULTS_PVT;