[Home] [Help]
PACKAGE BODY: APPS.OKS_EXTWARPRGM_OSO_PVT
Source
1 Package Body OKS_EXTWARPRGM_OSO_PVT AS
2 /* $Header: OKSRXRXB.pls 120.3 2005/08/10 03:41:36 hkamdar ship $ */
3
4
5
6 Procedure Party_Role ( p_ChrId IN Number,
7 p_cleid IN Number,
8 p_Rle_Code IN Varchar2,
9 p_PartyId IN Number,
10 p_Object_Code IN Varchar2,
11 x_roleid OUT NOCOPY Number,
12 x_msg_count OUT NOCOPY Number,
13 x_msg_data OUT NOCOPY Varchar2
14 )
15 Is
16
17 l_api_version CONSTANT NUMBER := 1.0;
18 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
19 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
20 l_index VARCHAR2(240);
21
22 --Party Role
23 l_cplv_tbl_in okc_contract_party_pub.cplv_tbl_type;
24 l_cplv_tbl_out okc_contract_party_pub.cplv_tbl_type;
25
26 Cursor l_party_csr Is Select Id From OKC_K_PARTY_ROLES_V
27 Where dnz_chr_id = p_chrid
28 And cle_id Is Null
29 And chr_id = p_chrid
30 And rle_code = p_rle_code;
31
32 Cursor l_lparty_csr Is Select Id From OKC_K_PARTY_ROLES_V
33 Where dnz_chr_id = p_chrid
34 And chr_id Is Null
35 And cle_id = p_cleid
36 And rle_code = p_rle_code;
37
38 l_roleid Number;
39
40 Begin
41
42 If p_cleid Is Null Then
43
44 Open l_party_csr;
45 Fetch l_party_csr Into l_roleid;
46 Close l_party_csr;
47
48 If l_roleid Is Not Null Then
49 x_roleid := l_roleid;
50 Return;
51 End If;
52
53 l_cplv_tbl_in(1).chr_id := p_chrid;
54
55 Else
56
57 Open l_lparty_csr;
58 Fetch l_lparty_csr Into l_roleid;
59 Close l_lparty_csr;
60
61 If l_roleid Is Not Null Then
62 x_roleid := l_roleid;
63 Return;
64 End If;
65
66 l_cplv_tbl_in(1).cle_id := p_cleid;
67
68 End If;
69
70 l_cplv_tbl_in(1).sfwt_flag := 'N';
71
72 l_cplv_tbl_in(1).rle_code := p_rle_code;
73 l_cplv_tbl_in(1).object1_id1 := p_partyid;
74 l_cplv_tbl_in(1).Object1_id2 := '#';
75 l_cplv_tbl_in(1).jtot_object1_code := p_object_code;
76 l_cplv_tbl_in(1).dnz_chr_id := p_chrid;
77
78 okc_contract_party_pub.create_k_party_role
79 (
80 p_api_version => l_api_version,
81 p_init_msg_list => l_init_msg_list,
82 x_return_status => l_return_status,
83 x_msg_count => x_msg_count,
84 x_msg_data => x_msg_data,
85 p_cplv_tbl => l_cplv_tbl_in,
86 x_cplv_tbl => l_cplv_tbl_out
87 );
88
89 if l_return_status = 'S' then
90 x_roleid := l_cplv_tbl_out(1).id;
91 else
92 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, p_rle_code || ' Party Role (HEADER)');
93 Raise G_EXCEPTION_HALT_VALIDATION;
94 end if;
95
96 End;
97
98
99 Function Get_K_Hdr_Id
100 (
101 P_Type Varchar2
102 , P_Object_ID IN NUMBER
103 , P_EndDate IN Date
104
105 )
106 Return Number
107 Is
108
109 Cursor l_kexists_csr (p_jtf_id VARCHAR2) is
110 Select Chr_id
111 From OKC_K_REL_OBJS
112 Where OBJECT1_ID1 = P_Object_id
113 And jtot_object1_code = p_jtf_id;
114
115
116
117 Cursor l_wexists_csr (p_jtf_id VARCHAR2) is
118 Select Chr_id
119 From OKC_K_REL_OBJS
120 Where OBJECT1_ID1 = P_Object_id
121 And jtot_object1_code = p_jtf_id
122 And rty_code = 'CONTRACTWARRANTYORDER';
123
124 l_wchrid Number;
125 l_kchrid Number;
126 l_jtf_id Varchar2(30);
127
128 Cursor l_hdr_csr Is
129 Select Id Chr_Id
130 From OKC_K_HEADERS_V
131 Where Attribute1 = p_Object_Id
132 And End_date = p_EndDate ;
133
134 Begin
135
136 If P_type = 'ORDER' Then
137
138 l_jtf_id := G_JTF_ORDER_HDR;
139
140 Open l_kexists_csr (l_jtf_id);
141 Fetch l_kexists_csr into l_kchrid;
142
143 If l_kexists_csr%Notfound then
144 Close l_kexists_csr;
145 Return (Null);
146 End If;
147
148 Close l_kexists_csr;
149 Return (l_kchrid);
150
151 ElsIf P_Type = 'RENEW' Then
152 Open l_hdr_csr;
153 Fetch l_hdr_csr Into l_kchrid;
154
155 If l_hdr_csr%Notfound then
156 Close l_hdr_csr;
157 Return (Null);
158 End If;
159
160 close l_hdr_csr;
161 Return (l_kchrid);
162 ElsIf P_Type = 'WARR' Then
163 l_jtf_id := G_JTF_ORDER_HDR;
164
165 Open l_wexists_csr (l_jtf_id);
166 Fetch l_wexists_csr into l_wchrid;
167
168 If l_wexists_csr%Notfound then
169 Close l_wexists_csr;
170 Return (Null);
171 End If;
172
173 Close l_wexists_csr;
174 Return (l_wchrid);
175 End If;
176
177 End Get_K_Hdr_Id;
178
179
180
181 Function Get_K_Cle_Id (p_ChrId Number
182 , p_InvServiceId Number
183 , p_StartDate Date
184 , p_EndDate Date ) Return Number Is
185
186 Cursor l_Service_Csr Is
187 Select KL.Id Cle_Id
188 From OKC_K_LINES_V KL
189 ,OKC_K_ITEMS_V KI
190 Where KL.dnz_chr_id = p_ChrId
191 And KL.lse_id In (14, 19)
192 And KL.Id = KI.cle_Id
193 And KI.Object1_Id1 = p_InvServiceId
194 -- And KL.StartDate >= p_StartDate
195 And KL.End_Date = p_EndDate;
196
197
198 l_cle_Id Number;
199
200 Begin
201 l_cle_id := Null;
202
203 Open l_Service_Csr;
204 Fetch l_Service_Csr Into l_cle_Id;
205 Close l_Service_csr;
206
207 Return (l_cle_id);
208
209 End Get_K_Cle_Id;
210
211 Function Priced_YN(P_LSE_ID IN NUMBER) RETURN VARCHAR2 IS
212 Cursor C_PRICED_YN IS
213 Select PRICED_YN
214 From okc_line_styles_B
215 Where ID = P_LSE_ID;
216 V_PRICED VARCHAR2(50) := 'N';
217 Begin
218 For CUR_C_PRICED_YN IN C_PRICED_YN
219 Loop
220 V_PRICED := CUR_C_PRICED_YN.PRICED_YN;
221 EXIT;
222 End Loop;
223 Return (V_PRICED);
224 End Priced_YN;
225
226
227 Function CHECK_RULE_Group_EXISTS
228 (
229 p_chr_id IN NUMBER,
230 p_cle_id IN NUMBER
231 ) Return NUMBER
232 Is
233 v_id NUMBER;
234 Begin
235 If (p_chr_id IS NOT NULL) Then
236 SELECT ID INTO V_ID FROM OKC_RULE_GROUPS_V WHERE Dnz_CHR_ID = p_chr_id And cle_id Is Null;
237 If V_ID IS NULL Then
238 return(NULL);
239 Else
240 return(V_ID);
241 End If;
242 End If;
243
244 If (p_cle_id IS NOT NULL) Then
245 SELECT ID INTO V_ID FROM OKC_RULE_GROUPS_V WHERE CLE_ID = p_cle_id;
246 If V_ID IS NULL Then
247 return(NULL);
248 Else
249 return(V_ID);
250 End If;
251 End If;
252
253 Exception
254 When OTHERS Then
255 RETURN(NULL);
256
257 End CHECK_RULE_Group_EXISTS;
258
259
260 Procedure Create_Rule_Group
261 (
262 p_rgpv_tbl IN okc_rule_pub.rgpv_tbl_type
263 ,x_rgpv_tbl OUT NOCOPY okc_rule_pub.rgpv_tbl_type
264 ,x_return_status OUT NOCOPY Varchar2
265 ,x_msg_count OUT NOCOPY Number
266 ,x_msg_data OUT NOCOPY Varchar2
267 )
268 Is
269 l_rgpv_tbl_in okc_rule_pub.rgpv_tbl_type;
270 l_rgpv_tbl_out okc_rule_pub.rgpv_tbl_type;
271
272 l_api_version CONSTANT NUMBER := 1.0;
273 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
274 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
275 l_index VARCHAR2(240);
276
277 Begin
278
279 x_return_status := l_return_status;
280
281 l_rgpv_tbl_in := p_rgpv_tbl;
282
283 okc_rule_pub.create_rule_group
284 (
285 p_api_version => l_api_version,
286 p_init_msg_list => l_init_msg_list,
287 x_return_status => l_return_status,
288 x_msg_count => x_msg_count,
289 x_msg_data => x_msg_data,
290 p_rgpv_tbl => l_rgpv_tbl_in,
291 x_rgpv_tbl => l_rgpv_tbl_out
292 );
293
294 If l_return_status = 'S'
295 Then
296 x_rgpv_tbl := l_rgpv_tbl_out;
297 Else
298 x_return_status := l_return_status;
299 End If;
300
301 Exception
302 When Others Then
303 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
304 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
305
306 End Create_Rule_Group;
307
308
309 Function Check_Rule_Exists
310 (
311 p_rgp_id IN NUMBER,
312 p_rule_type IN VARCHAR2
313 ) Return NUMBER
314 Is
315 v_id NUMBER;
316 Begin
317 If p_rgp_id is null Then
318 Return(null);
319 Else
320 Select ID Into V_ID From OKC_RULES_V
321 Where rgp_id = p_rgp_id
322 And Rule_information_category = p_rule_type;
323
324 If v_id Is NULL Then
325 return(null);
326 Else
327 return(V_ID);
328 End If;
329 End if;
330
331
332 Exception
333 WHEN No_Data_Found Then
334 Return (null);
335
336 End Check_Rule_Exists;
337
338
339 Procedure create_rules
340 (
341 p_rulv_tbl IN okc_rule_pub.rulv_tbl_type
342 ,x_rulv_tbl OUT NOCOPY okc_rule_pub.rulv_tbl_type
343 ,x_return_status OUT NOCOPY Varchar2
344 ,x_msg_count OUT NOCOPY Number
345 ,x_msg_data OUT NOCOPY Varchar2
346 )
347 Is
348 l_rulv_tbl_in okc_rule_pub.rulv_tbl_type;
349 l_rulv_tbl_out okc_rule_pub.rulv_tbl_type;
350
351 l_api_version CONSTANT NUMBER := 1.0;
352 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
353 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
354 l_index NUMBER;
355 l_module VARCHAR2(50) := 'TBL_RULE.CREATE_ROWS';
356 l_debug BOOLEAN := TRUE;
357
358 Begin
359
360 x_return_status := l_return_status;
361
362 l_rulv_tbl_in := p_rulv_tbl;
363
364 okc_rule_pub.create_rule
365 (
366 p_api_version => l_api_version,
367 p_init_msg_list => l_init_msg_list,
368 x_return_status => l_return_status,
369 x_msg_count => x_msg_count,
370 x_msg_data => x_msg_data,
371 p_rulv_tbl => l_rulv_tbl_in,
372 x_rulv_tbl => l_rulv_tbl_out
373 );
374
375 If l_return_status = 'S'
376 Then
377 x_rulv_tbl := l_rulv_tbl_out;
378 Else
379 x_return_status := l_return_status;
380 End If;
381
382
383 Exception
384 When Others Then
385 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
386 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
387
388 End create_rules;
389
390 Procedure Check_Line_Effectivity
391 (
392 p_cle_id IN NUMBER,
393 p_srv_sdt IN DATE,
394 p_srv_edt IN DATE,
395 x_line_sdt OUT NOCOPY DATE,
396 x_line_edt OUT NOCOPY DATE,
397 x_status OUT NOCOPY VarChar2
398 )
399 Is
400 Cursor l_line_csr Is
401 Select Start_Date, End_Date From OKC_K_LINES_V
402 Where id = p_cle_id;
403
404 l_line_csr_rec l_line_csr%ROWTYPE;
405
406 Begin
407
408 Open l_line_csr;
409 Fetch l_line_csr Into l_line_csr_rec;
410
411 If l_line_csr%FOUND Then
412 If p_srv_sdt >= l_line_csr_rec.Start_Date And p_srv_edt <= l_line_csr_rec.End_Date Then
413 x_Status := 'N';
414 Else
415 If p_srv_sdt >= l_line_csr_rec.Start_Date Then
416 x_line_sdt := l_line_csr_rec.Start_Date;
417 Else
418 x_line_sdt := p_srv_sdt;
419 End If;
420
421 If p_srv_edt >= l_line_csr_rec.End_Date Then
422 x_line_edt := p_srv_edt;
423 Else
424 x_line_edt := l_line_csr_rec.End_Date;
425 End If;
426 x_Status := 'Y';
427
428 End If;
429 Else
430 x_Status := 'E';
431 End If;
432
433 End;
434
435 Procedure Update_Line_Dates
436 (
437 p_cle_id IN Number,
438 p_new_sdt IN Date,
439 p_new_edt IN Date,
440 p_warranty_flag IN Varchar2,
441 x_status OUT NOCOPY VarChar2,
442 x_msg_count OUT NOCOPY Number,
443 x_msg_data OUT NOCOPY Number
444 )
445 Is
446 Cursor l_rulegroup_csr Is
447 Select Id
448 From okc_rule_groups_v rg
449 Where rg.cle_id = p_cle_id;
450
451 --General
452 l_api_version CONSTANT NUMBER := 1.0;
453 l_init_msg_list CONSTANT VARCHAR2(1) := OKC_API.G_FALSE;
454 l_return_status VARCHAR2(1) := 'S';
455 l_index VARCHAR2(2000);
456
457 --Contract Line
458 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
459 l_clev_tbl_out okc_contract_pub.clev_tbl_type;
460
461 --Rule Related
462 l_rulv_tbl_in okc_rule_pub.rulv_tbl_type;
463 l_rulv_tbl_out okc_rule_pub.rulv_tbl_type;
464
465
466 l_cleid NUMBER;
467 l_rgp_id NUMBER;
468 l_rule_id NUMBER;
469
470 Begin
471
472 x_status := OKC_API.G_RET_STS_SUCCESS;
473
474 --Contract Header Date Update
475
476 l_clev_tbl_in(1).id := p_cle_id;
477 l_clev_tbl_in(1).Start_Date := p_new_sdt;
478 l_clev_tbl_in(1).End_Date := p_new_edt;
479
480 okc_contract_pub.update_contract_line
481 (
482 p_api_version => l_api_version,
483 p_init_msg_list => l_init_msg_list,
484 x_return_status => l_return_status,
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data,
487 p_clev_tbl => l_clev_tbl_in,
488 x_clev_tbl => l_clev_tbl_out
489 );
490
491 If l_return_status = 'S' then
492 l_cleid := l_clev_tbl_out(1).id;
493 Else
494 x_status := 'E';
495 Raise G_EXCEPTION_HALT_VALIDATION;
496 End if;
497
498 --Schedule Billing Update
499
500 IF p_warranty_flag <> 'W' THEN
501
502 Open l_rulegroup_csr;
503 Fetch l_rulegroup_csr Into l_rgp_id;
504
505 If l_rulegroup_csr%NOTFOUND Then
506 x_status := 'E';
507 Raise G_EXCEPTION_HALT_VALIDATION;
508 End If;
509
510 Close l_rulegroup_csr;
511
512 l_rule_id := Check_Rule_Exists(l_rgp_id, 'SBG');
513
514 If l_rule_id Is Not NULL Then
515
516 l_rulv_tbl_in(1).id := l_rule_id;
517 l_rulv_tbl_in(1).rule_information4 := to_char(p_new_sdt,'YYYY/MM/DD HH24:MI:SS');
518 l_rulv_tbl_in(1).rule_information3 := to_char(p_new_edt,'YYYY/MM/DD HH24:MI:SS');
519
520 okc_rule_pub.update_rule
521 (
522 p_api_version => l_api_version,
523 p_init_msg_list => l_init_msg_list,
524 x_return_status => l_return_status,
525 x_msg_count => x_msg_count,
526 x_msg_data => x_msg_data,
527 p_rulv_tbl => l_rulv_tbl_in,
528 x_rulv_tbl => l_rulv_tbl_out
529 );
530
531 If l_return_status = 'S' Then
532 l_rule_id := l_rulv_tbl_out(1).id;
533 Else
534 x_status := 'E';
535 Raise G_EXCEPTION_HALT_VALIDATION;
536 End If;
537 Else
538 x_status := 'E';
539 Raise G_EXCEPTION_HALT_VALIDATION;
540 End If;
541
542 END IF; -- warranty flag check;
543
544
545 Exception
546 When G_EXCEPTION_HALT_VALIDATION Then
547 Null;
548 When Others Then
549 x_status := OKC_API.G_RET_STS_UNEXP_ERROR;
550 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
551 End;
552
553
554
555
556 Procedure Create_Obj_Rel
557 (
558 p_K_id IN Number
559 , p_line_id IN Number
560 , p_orderhdrid IN Number
561 , p_orderlineid IN Number
562 , x_return_status OUT NOCOPY Varchar2
563 , x_msg_count OUT NOCOPY Number
564 , x_msg_data OUT NOCOPY Varchar2
565 , x_crjv_tbl_out OUT NOCOPY OKC_K_REL_OBJS_PUB.crjv_tbl_type
566 )
567 Is
568
569 l_api_version CONSTANT NUMBER := 1.0;
570 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
571 l_return_status VARCHAR2(1) := 'S';
572 l_crjv_tbl_in OKC_K_REL_OBJS_PUB.crjv_tbl_type;
573 l_crjv_tbl_out OKC_K_REL_OBJS_PUB.crjv_tbl_type;
574
575 Begin
576
577 x_return_status := l_return_status;
578
579 If p_orderhdrid Is Not Null Then
580
581 l_crjv_tbl_in(1).chr_id := p_K_id;
582 l_crjv_tbl_in(1).object1_id1 := p_orderhdrid;
583 l_crjv_tbl_in(1).jtot_object1_code := 'OKX_ORDERHEAD';
584 l_crjv_tbl_in(1).rty_code := 'CONTRACTSERVICESORDER';
585
586 OKC_K_REL_OBJS_PUB.CREATE_ROW
587 (
588 P_API_VERSION => l_api_version,
589 P_INIT_MSG_LIST => l_init_msg_list,
590 X_RETURN_STATUS => l_return_status,
591 X_MSG_COUNT => x_msg_count,
592 X_MSG_DATA => x_msg_data,
593 P_CRJV_TBL => l_crjv_tbl_in,
594 X_CRJV_TBL => l_crjv_tbl_out
595 );
596
597 If l_return_status = 'S' Then
598 x_crjv_tbl_out := l_crjv_tbl_out;
599 Else
600 x_return_status := l_return_status;
601 End If;
602
603 ElsIf p_orderlineid Is Not Null Then
604
605 l_crjv_tbl_in(1).cle_id := p_line_id;
606 l_crjv_tbl_in(1).object1_id1 := p_orderlineid;
607 l_crjv_tbl_in(1).jtot_object1_code := 'OKX_ORDERLINE';
608 l_crjv_tbl_in(1).rty_code := 'CONTRACTSERVICESORDER';
609
610 OKC_K_REL_OBJS_PUB.CREATE_ROW
611 (
612 P_API_VERSION => l_api_version,
613 P_INIT_MSG_LIST => l_init_msg_list,
614 X_RETURN_STATUS => l_return_status,
615 X_MSG_COUNT => x_msg_count,
616 X_MSG_DATA => x_msg_data,
617 P_CRJV_TBL => l_crjv_tbl_in,
618 X_CRJV_TBL => l_crjv_tbl_out
619 );
620
621 If l_return_status = 'S' Then
622 x_crjv_tbl_out := l_crjv_tbl_out;
623 Else
624 x_return_status := l_return_status;
625 End If;
626
627 End If;
628
629 Exception
630 When Others Then
631 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
632 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
633 End;
634
635 Procedure Check_hdr_Effectivity
636 (
637 p_chr_id IN NUMBER,
638 p_srv_sdt IN DATE,
639 p_srv_edt IN DATE,
640 x_hdr_sdt OUT NOCOPY DATE,
641 x_hdr_edt OUT NOCOPY DATE,
642 x_org_id OUT NOCOPY Number,
643 x_status OUT NOCOPY VarChar2
644 )
645 Is
646 Cursor l_hdr_csr Is
647 Select Start_Date, End_Date ,
648 Authoring_Org_Id From OKC_K_HEADERS_V
649 Where id = p_chr_id;
650
651 l_hdr_csr_rec l_hdr_csr%ROWTYPE;
652
653 Begin
654
655 Open l_hdr_csr;
656 Fetch l_hdr_csr Into l_hdr_csr_rec;
657
658 If l_hdr_csr%FOUND Then
659 x_org_id := l_hdr_csr_rec.authoring_org_id;
660 If p_srv_sdt >= l_hdr_csr_rec.Start_Date And p_srv_edt <= l_hdr_csr_rec.End_Date Then
661 x_Status := 'N';
662 Else
663 If p_srv_sdt >= l_hdr_csr_rec.Start_Date Then
664 x_hdr_sdt := l_hdr_csr_rec.Start_Date;
665 Else
666 x_hdr_sdt := p_srv_sdt;
667 End If;
668
669 If p_srv_edt >= l_hdr_csr_rec.End_Date Then
670 x_hdr_edt := p_srv_edt;
671 Else
672 x_hdr_edt := l_hdr_csr_rec.End_Date;
673 End If;
674 x_Status := 'Y';
675
676 End If;
677 Else
678 x_Status := 'E';
679 End If;
680
681 End;
682
683 Procedure Update_Hdr_Dates
684 (
685 p_chr_id IN Number,
686 p_new_sdt IN Date,
687 p_new_edt IN Date,
688 x_status OUT NOCOPY VarChar2,
689 x_msg_count OUT NOCOPY Number,
690 x_msg_data OUT NOCOPY Number
691 )
692 Is
693 Cursor l_timevalue_csr Is
694 Select rule_information1
695 From okc_rules_v rl
696 , okc_rule_groups_v rg
697 , okc_k_headers_v hd
698 Where hd.id = p_chr_id
699 And rg.chr_id = hd.id
700 And rl.rgp_id = rg.id
701 And rl.rule_information_category = 'EFY';
702
703 Cursor l_rulegroup_csr Is
704 Select Id
705 From okc_rule_groups_v rg
706 Where rg.dnz_chr_id = p_chr_id
707 And rg.cle_id Is Null;
708
709
710 --General
711 l_api_version CONSTANT NUMBER := 1.0;
712 l_init_msg_list CONSTANT VARCHAR2(1) := OKC_API.G_FALSE;
713 l_return_status VARCHAR2(1) := 'S';
714 l_index VARCHAR2(2000);
715
716 --Contract Header
717 l_chrv_tbl_in okc_contract_pub.chrv_tbl_type;
718 l_chrv_tbl_out okc_contract_pub.chrv_tbl_type;
719
720 --Rule Related
721 l_rulv_tbl_in okc_rule_pub.rulv_tbl_type;
722 l_rulv_tbl_out okc_rule_pub.rulv_tbl_type;
723
724
725 --Time Value Related
726 l_isev_ext_tbl_in okc_time_pub.isev_ext_tbl_type;
727 l_isev_ext_tbl_out okc_time_pub.isev_ext_tbl_type;
728
729 l_chrid NUMBER;
730 l_timevalue_id NUMBER;
731 l_rgp_id NUMBER;
732 l_rule_id NUMBER;
733
734 Begin
735
736 x_status := OKC_API.G_RET_STS_SUCCESS;
737
738 --Contract Header Date Update
739
740 l_chrv_tbl_in(1).id := p_chr_id;
741 l_chrv_tbl_in(1).Start_Date := p_new_sdt;
742 l_chrv_tbl_in(1).End_Date := p_new_edt;
743
744 okc_contract_pub.update_contract_header
745 (
746 p_api_version => l_api_version,
747 p_init_msg_list => l_init_msg_list,
748 x_return_status => l_return_status,
749 x_msg_count => x_msg_count,
750 x_msg_data => x_msg_data,
751 p_chrv_tbl => l_chrv_tbl_in,
752 x_chrv_tbl => l_chrv_tbl_out
753 );
754
755 If l_return_status = 'S' then
756 l_chrid := l_chrv_tbl_out(1).id;
757 Else
758 x_status := 'E';
759 Raise G_EXCEPTION_HALT_VALIDATION;
760 End if;
761
762 --Schedule Billing Update
763
764 Open l_rulegroup_csr;
765 Fetch l_rulegroup_csr Into l_rgp_id;
766
767 If l_rulegroup_csr%NOTFOUND Then
768 x_status := 'E';
769 Raise G_EXCEPTION_HALT_VALIDATION;
770 End If;
771
772 Close l_rulegroup_csr;
773
774 l_rule_id := Check_Rule_Exists(l_rgp_id, 'SBG');
775
776 If l_rule_id Is Not NULL Then
777
778 l_rulv_tbl_in(1).id := l_rule_id;
779 l_rulv_tbl_in(1).rule_information4 := to_char(p_new_sdt,'YYYY/MM/DD HH24:MI:SS');
780 l_rulv_tbl_in(1).rule_information3 := to_char(p_new_edt,'YYYY/MM/DD HH24:MI:SS');
781
782 okc_rule_pub.update_rule
783 (
784 p_api_version => l_api_version,
785 p_init_msg_list => l_init_msg_list,
786 x_return_status => l_return_status,
787 x_msg_count => x_msg_count,
788 x_msg_data => x_msg_data,
789 p_rulv_tbl => l_rulv_tbl_in,
790 x_rulv_tbl => l_rulv_tbl_out
791 );
792
793 If l_return_status = 'S' Then
794 l_rule_id := l_rulv_tbl_out(1).id;
795 Else
796 x_status := 'E';
797 Raise G_EXCEPTION_HALT_VALIDATION;
798 End If;
799 Else
800 x_status := 'E';
801 Raise G_EXCEPTION_HALT_VALIDATION;
802 End If;
803
804
805 Exception
806 When G_EXCEPTION_HALT_VALIDATION Then
807 Null;
808 When Others Then
809 x_status := OKC_API.G_RET_STS_UNEXP_ERROR;
810 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
811 End;
812
813
814 Procedure Clear_Rule_Table (X_rulv_tbl OUT NOCOPY okc_rule_pub.rulv_tbl_type)
815 Is
816 Begin
817
818 x_rulv_tbl(1).id := OKC_API.G_MISS_NUM;
819 x_rulv_tbl(1).rgp_id := OKC_API.G_MISS_NUM;
820 x_rulv_tbl(1).object1_id1 := OKC_API.G_MISS_CHAR;
821 x_rulv_tbl(1).object2_id1 := OKC_API.G_MISS_CHAR;
822 x_rulv_tbl(1).object3_id1 := OKC_API.G_MISS_CHAR;
823 x_rulv_tbl(1).object1_id2 := OKC_API.G_MISS_CHAR;
824 x_rulv_tbl(1).object2_id2 := OKC_API.G_MISS_CHAR;
825 x_rulv_tbl(1).object3_id2 := OKC_API.G_MISS_CHAR;
826 x_rulv_tbl(1).jtot_object1_code := OKC_API.G_MISS_CHAR;
827 x_rulv_tbl(1).jtot_object2_code := OKC_API.G_MISS_CHAR;
828 x_rulv_tbl(1).jtot_object3_code := OKC_API.G_MISS_CHAR;
829 x_rulv_tbl(1).dnz_chr_id := OKC_API.G_MISS_NUM;
830 x_rulv_tbl(1).std_template_yn := OKC_API.G_MISS_CHAR;
831 x_rulv_tbl(1).warn_yn := OKC_API.G_MISS_CHAR;
832 x_rulv_tbl(1).priority := OKC_API.G_MISS_NUM;
833 x_rulv_tbl(1).object_version_number := OKC_API.G_MISS_NUM;
834 x_rulv_tbl(1).created_by := OKC_API.G_MISS_NUM;
835 x_rulv_tbl(1).creation_date := OKC_API.G_MISS_DATE;
836 x_rulv_tbl(1).last_updated_by := OKC_API.G_MISS_NUM;
837 x_rulv_tbl(1).last_update_date := OKC_API.G_MISS_DATE;
838 x_rulv_tbl(1).last_update_login := OKC_API.G_MISS_NUM;
839 x_rulv_tbl(1).attribute_category := OKC_API.G_MISS_CHAR;
840 x_rulv_tbl(1).attribute1 := OKC_API.G_MISS_CHAR;
841 x_rulv_tbl(1).attribute2 := OKC_API.G_MISS_CHAR;
842 x_rulv_tbl(1).attribute3 := OKC_API.G_MISS_CHAR;
843 x_rulv_tbl(1).attribute4 := OKC_API.G_MISS_CHAR;
844 x_rulv_tbl(1).attribute5 := OKC_API.G_MISS_CHAR;
845 x_rulv_tbl(1).attribute6 := OKC_API.G_MISS_CHAR;
846 x_rulv_tbl(1).attribute7 := OKC_API.G_MISS_CHAR;
847 x_rulv_tbl(1).attribute8 := OKC_API.G_MISS_CHAR;
848 x_rulv_tbl(1).attribute9 := OKC_API.G_MISS_CHAR;
849 x_rulv_tbl(1).attribute10 := OKC_API.G_MISS_CHAR;
850 x_rulv_tbl(1).attribute11 := OKC_API.G_MISS_CHAR;
851 x_rulv_tbl(1).attribute12 := OKC_API.G_MISS_CHAR;
852 x_rulv_tbl(1).attribute13 := OKC_API.G_MISS_CHAR;
853 x_rulv_tbl(1).attribute14 := OKC_API.G_MISS_CHAR;
854 x_rulv_tbl(1).attribute15 := OKC_API.G_MISS_CHAR;
855 x_rulv_tbl(1).rule_information_category := OKC_API.G_MISS_CHAR;
856 x_rulv_tbl(1).rule_information1 := OKC_API.G_MISS_CHAR;
857 x_rulv_tbl(1).rule_information2 := OKC_API.G_MISS_CHAR;
858 x_rulv_tbl(1).rule_information3 := OKC_API.G_MISS_CHAR;
859 x_rulv_tbl(1).rule_information4 := OKC_API.G_MISS_CHAR;
860 x_rulv_tbl(1).rule_information5 := OKC_API.G_MISS_CHAR;
861 x_rulv_tbl(1).rule_information6 := OKC_API.G_MISS_CHAR;
862 x_rulv_tbl(1).rule_information7 := OKC_API.G_MISS_CHAR;
863 x_rulv_tbl(1).rule_information8 := OKC_API.G_MISS_CHAR;
864 x_rulv_tbl(1).rule_information9 := OKC_API.G_MISS_CHAR;
865 x_rulv_tbl(1).rule_information10 := OKC_API.G_MISS_CHAR;
866 x_rulv_tbl(1).rule_information11 := OKC_API.G_MISS_CHAR;
867 x_rulv_tbl(1).rule_information12 := OKC_API.G_MISS_CHAR;
868 x_rulv_tbl(1).rule_information13 := OKC_API.G_MISS_CHAR;
869 x_rulv_tbl(1).rule_information14 := OKC_API.G_MISS_CHAR;
870 x_rulv_tbl(1).rule_information15 := OKC_API.G_MISS_CHAR;
871 End;
872
873
874 FUNCTION GET_CONTRACT_NUMBER (p_hdrid IN Number) RETURN VARCHAR2
875 Is
876 Cursor l_hdr_Csr is
877 Select contract_number From OKC_K_HEADERS_V
878 Where id = p_hdrid;
879
880 l_contract_number VARCHAR2(120);
881
882 Begin
883 Open l_hdr_csr;
884 Fetch l_hdr_csr Into l_contract_number;
885 Close l_hdr_csr;
886
887 Return l_contract_number;
888 End;
889
890
891 PROCEDURE LAUNCH_WORKFLOW (P_MSG IN VARCHAR2) Is
892
893
894 --Workflow attributes
895
896 l_itemtype Varchar2(40) := 'OKSWARWF';
897 l_itemkey Varchar2(240) := 'OKS-'||to_char(sysdate,'MMDDYYYYHH24MISS');
898 l_process Varchar2(40) := 'OKSWARPROC';
899 l_notify Varchar2(10) := 'Y';
900 l_receiver Varchar2(30);
901
902 BEGIN
903
904 l_notify := Nvl(Fnd_Profile.Value ('OKS_INTEGRATION_NOTIFY_YN'),'NO');
905 l_receiver := Nvl(Fnd_Profile.Value ('OKS_INTEGRATION_NOTIFY_TO'),'SYSADMIN');
906
907
908 If upper(l_notify) = 'YES' Then
909
910 l_itemkey := 'OKS-'||to_char(sysdate,'MMDDYYYYHH24MISS');
911
912 WF_ENGINE.CreateProcess
913 (
914 itemtype => l_itemtype,
915 itemkey => l_itemkey,
916 process => l_process
917 );
918
919 WF_ENGINE.SetItemAttrText
920 (
921 itemtype => l_itemtype,
922 itemkey => l_itemkey,
923 aname => 'MSG_TXT',
924 avalue => p_msg
925 );
926
927 WF_ENGINE.SetItemAttrText
928 (
929 itemtype => l_itemtype,
930 itemkey => l_itemkey,
931 aname => 'MSG_RECV',
932 avalue => l_receiver
933 );
934
935
936 WF_ENGINE.StartProcess
937 (
938 itemtype => l_itemtype,
939 itemkey => l_itemkey
940 );
941
942 End If;
943
944 END;
945
946
947 Procedure Update_Cov_level
948 (
949 p_covered_line_id IN Number,
950 p_new_end_date IN Date,
951 p_K_item_id IN Number,
952 p_new_negotiated_amt IN Number,
953 p_new_cp_qty IN Number,
954 x_return_status OUT NOCOPY Varchar2,
955 x_msg_count OUT NOCOPY Number,
956 x_msg_data OUT NOCOPY Varchar2
957 )
958 Is
959 Cursor l_parent_line_Csr is
960 Select cle_id From OKC_K_LINES_V
961 Where id = p_covered_line_id;
962
963 l_api_version CONSTANT NUMBER := 1.0;
964 l_init_msg_list CONSTANT VARCHAR2(1) := OKC_API.G_FALSE;
965 l_return_status VARCHAR2(1) := 'S';
966 l_index VARCHAR2(2000);
967
968 --Contract Line Table
969 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
970 l_clev_tbl_out okc_contract_pub.clev_tbl_type;
971
972 --Contract Item
973 l_cimv_tbl_in okc_contract_item_pub.cimv_tbl_type;
974 l_cimv_tbl_out okc_contract_item_pub.cimv_tbl_type;
975
976
977 l_parent_line_id NUMBER;
978 l_line_id NUMBER;
979 l_line_item_id NUMBER;
980
981
982 Begin
983
984 If p_new_end_date Is Not Null Then
985
986 x_return_status := OKC_API.G_RET_STS_SUCCESS;
987 l_clev_tbl_in(1).id := p_covered_line_id;
988 l_clev_tbl_in(1).end_date := p_new_end_date;
989
990 okc_contract_pub.update_contract_line
991 (
992 p_api_version => l_api_version,
993 p_init_msg_list => l_init_msg_list,
994 x_return_status => l_return_status,
995 x_msg_count => x_msg_count,
996 x_msg_data => x_msg_data,
997 p_clev_tbl => l_clev_tbl_in,
998 x_clev_tbl => l_clev_tbl_out
999 );
1000 FND_FILE.PUT_LINE (FND_FILE.LOG,'UPDATE COV LVL : update contract line status: '||l_return_status );
1001 If l_return_status = 'S' then
1002 l_line_id := l_clev_tbl_out(1).id;
1003 Open l_parent_line_csr;
1004 Fetch l_parent_line_csr Into l_parent_line_id;
1005 Close l_parent_line_csr;
1006
1007 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1008 l_clev_tbl_in(1).id := l_parent_line_id;
1009 l_clev_tbl_in(1).end_date := p_new_end_date;
1010
1011 okc_contract_pub.update_contract_line
1012 (
1013 p_api_version => l_api_version,
1014 p_init_msg_list => l_init_msg_list,
1015 x_return_status => l_return_status,
1016 x_msg_count => x_msg_count,
1017 x_msg_data => x_msg_data,
1018 p_clev_tbl => l_clev_tbl_in,
1019 x_clev_tbl => l_clev_tbl_out
1020 );
1021
1022 If l_return_status = 'S' then
1023 l_line_id := l_clev_tbl_out(1).id;
1024 Else
1025 Raise G_EXCEPTION_HALT_VALIDATION;
1026 End if;
1027 Else
1028 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Contract Line Update(UPDATE SUB LINE)');
1029 Raise G_EXCEPTION_HALT_VALIDATION;
1030 End if;
1031
1032 End If;
1033
1034 If p_new_negotiated_amt Is Not Null Then
1035
1036 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1037 l_clev_tbl_in(1).id := p_covered_line_id;
1038 l_clev_tbl_in(1).price_negotiated := p_new_negotiated_amt;
1039
1040 okc_contract_pub.update_contract_line
1041 (
1042 p_api_version => l_api_version,
1043 p_init_msg_list => l_init_msg_list,
1044 x_return_status => l_return_status,
1045 x_msg_count => x_msg_count,
1046 x_msg_data => x_msg_data,
1047 p_clev_tbl => l_clev_tbl_in,
1048 x_clev_tbl => l_clev_tbl_out
1049 );
1050
1051 If l_return_status = 'S' then
1052 l_line_id := l_clev_tbl_out(1).id;
1053 Else
1054 Raise G_EXCEPTION_HALT_VALIDATION;
1055 End if;
1056
1057 End If;
1058
1059 If p_new_cp_qty Is Not Null Then
1060
1061 l_cimv_tbl_in(1).id := p_k_item_id;
1062 l_cimv_tbl_in(1).number_of_items := p_new_cp_qty;
1063
1064 okc_contract_item_pub.update_contract_item
1065 (
1066 p_api_version => l_api_version,
1067 p_init_msg_list => l_init_msg_list,
1068 x_return_status => l_return_status,
1069 x_msg_count => x_msg_count,
1070 x_msg_data => x_msg_data,
1071 p_cimv_tbl => l_cimv_tbl_in,
1072 x_cimv_tbl => l_cimv_tbl_out
1073 );
1074 FND_FILE.PUT_LINE (FND_FILE.LOG,'UPDATE COV LVL : update contract item status: '||l_return_status );
1075 If l_return_status = 'S' then
1076 l_line_item_id := l_cimv_tbl_out(1).id;
1077 Else
1078 Raise G_EXCEPTION_HALT_VALIDATION;
1079 End if;
1080
1081 End If;
1082
1083 Exception
1084 When G_EXCEPTION_HALT_VALIDATION Then
1085 x_return_status := l_return_status;
1086 Null;
1087 When Others Then
1088 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1089 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1090 End;
1091
1092
1093
1094 PROCEDURE Create_K_Hdr
1095 (
1096 p_k_header_rec IN K_HEADER_REC_TYPE
1097 , p_Contact_tbl IN Contact_Tbl
1098 , x_chr_id OUT NOCOPY Number
1099 , x_return_status OUT NOCOPY Varchar2
1100 , x_msg_count OUT NOCOPY Number
1101 , x_msg_data OUT NOCOPY Varchar2
1102 )
1103 IS
1104
1105
1106 Cursor l_thirdparty_csr (p_id Number)
1107 Is
1108 Select Party_Id From OKX_CUST_SITE_USES_V
1109 Where ID1 = p_id;
1110
1111 Cursor l_cust_csr (p_contactid Number)
1112 Is
1113 Select Party_Id From OKX_CUST_CONTACTS_V
1114 Where Id1 = p_contactid And id2 = '#';
1115
1116 Cursor l_ra_hcontacts_cur (p_contact_id number) Is
1117 Select hzr.object_id
1118 --, subject_id
1119 , hzr.party_id
1120 --NPALEPU
1121 --18-JUN-2005,08-AUG-2005
1122 --TCA Project
1123 --Replaced hz_party_relationships table with hz_relationships table and ra_hcontacts with OKS_RA_HCONTACTS_V
1124 --Replaced hzr.party_relationship_id column with hzr.relationship_id column and added new conditions
1125 /* From
1126 ra_hcontacts rah,
1127 hz_party_relationships hzr
1128 Where rah.contact_id = p_contact_id
1129 and rah.party_relationship_id = hzr.party_relationship_id;*/
1130 From
1131 OKS_RA_HCONTACTS_V rah,
1132 hz_relationships hzr
1133 Where rah.contact_id = p_contact_id
1134 and rah.party_relationship_id = hzr.relationship_id
1135 AND hzr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1136 AND hzr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1137 AND hzr.DIRECTIONAL_FLAG = 'F';
1138 --END NPALEPU
1139
1140
1141 l_rah_party_id NUMBER;
1142 l_rah_hdr_object1_id1 NUMBER;
1143
1144 l_thirdparty_id NUMBER;
1145 l_thirdparty_role VARCHAR2(30);
1146
1147 l_api_version CONSTANT NUMBER := 1.0;
1148 l_init_msg_list CONSTANT VARCHAR2(1) := OKC_API.G_FALSE;
1149 l_return_status VARCHAR2(1) := 'S';
1150 l_index VARCHAR2(2000);
1151
1152 i Number;
1153 --Contract Header
1154 l_chrv_tbl_in okc_contract_pub.chrv_tbl_type;
1155 l_chrv_tbl_out okc_contract_pub.chrv_tbl_type;
1156
1157 --Contract Groupings
1158 l_cgcv_tbl_in okc_contract_group_pub.cgcv_tbl_type;
1159 l_cgcv_tbl_out okc_contract_group_pub.cgcv_tbl_type;
1160
1161 --Contacts
1162 l_ctcv_tbl_in okc_contract_party_pub.ctcv_tbl_type;
1163 l_ctcv_tbl_out okc_contract_party_pub.ctcv_tbl_type;
1164
1165 --Agreements/Governance
1166
1167 l_gvev_tbl_in okc_contract_pub.gvev_tbl_type;
1168 l_gvev_tbl_out okc_contract_pub.gvev_tbl_type;
1169
1170 --Rule Related
1171
1172 l_rgpv_tbl_in okc_rule_pub.rgpv_tbl_type;
1173 l_rgpv_tbl_out okc_rule_pub.rgpv_tbl_type;
1174
1175 l_rulv_tbl_in okc_rule_pub.rulv_tbl_type;
1176 l_rulv_tbl_out okc_rule_pub.rulv_tbl_type;
1177
1178 --Time Value Related
1179 l_isev_ext_tbl_in okc_time_pub.isev_ext_tbl_type;
1180 l_isev_ext_tbl_out okc_time_pub.isev_ext_tbl_type;
1181
1182 --Approval WorkFlow
1183 l_cpsv_tbl_in okc_contract_pub.cpsv_tbl_type;
1184 l_cpsv_tbl_out okc_contract_pub.cpsv_tbl_type;
1185
1186 --REL OBJS
1187 l_crjv_tbl_out okc_k_rel_objs_pub.crjv_tbl_type;
1188
1189
1190 --Return IDs
1191
1192 l_chrid NUMBER;
1193 l_partyid NUMBER;
1194 l_partyid_v NUMBER;
1195 l_partyid_t NUMBER;
1196 l_add2partyid NUMBER;
1197 l_rule_group_id NUMBER;
1198 l_rule_id NUMBER;
1199 l_govern_id NUMBER;
1200 l_time_value_id NUMBER;
1201 l_contact_id NUMBER;
1202 l_grpid NUMBER;
1203 l_pdfid NUMBER;
1204 l_ctrgrp NUMBER;
1205
1206 l_cust_partyid NUMBER;
1207 l_findparty_id NUMBER;
1208
1209 l_hdr_contactid NUMBER;
1210
1211 BEGIN
1212
1213 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1214
1215 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- MERGE TYPE ' || p_k_header_rec.merge_type );
1216 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- MERGE ID ' || p_k_header_rec.merge_object_id);
1217
1218
1219 If p_k_header_rec.merge_type = 'NEW' Then
1220 l_chrid := Null;
1221 ElsIf p_k_header_rec.merge_type = 'LTC' Then
1222 l_chrid := p_k_header_rec.merge_object_id;
1223 ElsIf p_k_header_rec.merge_type Is Not Null Then
1224 l_chrid := GET_K_HDR_ID (
1225 p_type => p_k_header_rec.merge_type
1226 ,p_object_id => p_k_header_rec.merge_object_id
1227 ,p_enddate => p_k_header_rec.end_date
1228 );
1229 End If;
1230
1231
1232 If l_chrid Is Not Null Then
1233 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- CHR ID IS NULL ');
1234 x_chr_id := l_chrid;
1235 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1236 Raise G_EXCEPTION_HALT_VALIDATION;
1237 End If;
1238
1239 --Contract Header Routine
1240
1241 If Nvl(p_k_header_rec.sts_code,'ENTERED') = 'ACTIVE' Then
1242 l_chrv_tbl_in(1).date_signed := p_k_header_rec.start_date;
1243 l_chrv_tbl_in(1).date_approved := p_k_header_rec.start_date;
1244 Else
1245 l_chrv_tbl_in(1).date_signed := Null;
1246 l_chrv_tbl_in(1).date_approved := Null;
1247 End If;
1248
1249 If p_k_header_rec.cust_po_number is not null then
1250 l_chrv_tbl_in(1).cust_po_number_req_yn := 'Y';
1251 Else
1252 l_chrv_tbl_in(1).cust_po_number_req_yn := 'N';
1253 End If;
1254
1255
1256 l_chrv_tbl_in(1).sfwt_flag := 'N';
1257 l_chrv_tbl_in(1).contract_number := p_k_header_rec.contract_number;
1258 l_chrv_tbl_in(1).sts_code := p_k_header_rec.sts_code;
1259 l_chrv_tbl_in(1).scs_code := 'WARRANTY';
1260 l_chrv_tbl_in(1).authoring_org_id := p_k_header_rec.authoring_org_id;
1261 l_chrv_tbl_in(1).inv_organization_id := okc_context.get_okc_organization_id;
1262 l_chrv_tbl_in(1).pre_pay_req_yn := 'N';
1263 l_chrv_tbl_in(1).cust_po_number := p_k_header_rec.cust_po_number;
1264 l_chrv_tbl_in(1).qcl_id := OKC_API.G_MISS_NUM;
1265 l_chrv_tbl_in(1).short_description := Nvl(p_k_header_rec.short_description,'Warranty/Extended Warranty');
1266 l_chrv_tbl_in(1).template_yn := 'N';
1267 l_chrv_tbl_in(1).start_date := p_k_header_rec.start_date;
1268 l_chrv_tbl_in(1).end_date := p_k_header_rec.end_date;
1269 l_chrv_tbl_in(1).chr_type := OKC_API.G_MISS_CHAR;
1270 l_chrv_tbl_in(1).archived_yn := 'N';
1271 l_chrv_tbl_in(1).deleted_yn := 'N';
1272 l_chrv_tbl_in(1).created_by := OKC_API.G_MISS_NUM;
1273 l_chrv_tbl_in(1).creation_date := OKC_API.G_MISS_DATE;
1274 l_chrv_tbl_in(1).currency_code := p_k_header_rec.currency;
1275 l_chrv_tbl_in(1).buy_or_sell := 'S';
1276 l_chrv_tbl_in(1).issue_or_receive := 'I';
1277 l_chrv_tbl_in(1).Attribute1 := p_k_header_rec.attribute1;
1278 l_chrv_tbl_in(1).Attribute2 := p_k_header_rec.attribute2;
1279 l_chrv_tbl_in(1).Attribute3 := p_k_header_rec.attribute3;
1280 l_chrv_tbl_in(1).Attribute4 := p_k_header_rec.attribute4;
1281 l_chrv_tbl_in(1).Attribute5 := p_k_header_rec.attribute5;
1282 l_chrv_tbl_in(1).Attribute6 := p_k_header_rec.attribute6;
1283 l_chrv_tbl_in(1).Attribute7 := p_k_header_rec.attribute7;
1284 l_chrv_tbl_in(1).Attribute8 := p_k_header_rec.attribute8;
1285 l_chrv_tbl_in(1).Attribute9 := p_k_header_rec.attribute9;
1286 l_chrv_tbl_in(1).Attribute10 := p_k_header_rec.attribute10;
1287 l_chrv_tbl_in(1).Attribute11 := p_k_header_rec.attribute11;
1288 l_chrv_tbl_in(1).Attribute12 := p_k_header_rec.attribute12;
1289 l_chrv_tbl_in(1).Attribute13 := p_k_header_rec.attribute13;
1290 l_chrv_tbl_in(1).Attribute14 := p_k_header_rec.attribute14;
1291 l_chrv_tbl_in(1).Attribute15 := p_k_header_rec.attribute15;
1292
1293 If p_k_header_rec.merge_type = 'RENEW' Then
1294 l_chrv_tbl_in(1).Attribute1 := p_k_header_rec.merge_object_id;
1295 End If;
1296
1297 okc_contract_pub.create_contract_header
1298 (
1299 p_api_version => l_api_version,
1300 p_init_msg_list => l_init_msg_list,
1301 x_return_status => l_return_status,
1302 x_msg_count => x_msg_count,
1303 x_msg_data => x_msg_data,
1304 p_chrv_tbl => l_chrv_tbl_in,
1305 x_chrv_tbl => l_chrv_tbl_out
1306 );
1307
1308 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- HDR STATUS ' || l_return_status);
1309
1310 If l_return_status = 'S' then
1311 l_chrid := l_chrv_tbl_out(1).id;
1312 Else
1313 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'HEADER (HEADER)');
1314 Raise G_EXCEPTION_HALT_VALIDATION;
1315 end if;
1316
1317
1318 --Party Role Routine ('VENDOR')
1319
1320 Party_Role ( p_ChrId => l_chrid,
1321 p_cleId => Null,
1322 p_Rle_Code => 'VENDOR',
1323 p_PartyId => p_k_header_rec.authoring_org_id,
1324 p_Object_Code => G_JTF_PARTY_VENDOR,
1325 x_roleid => l_partyid_v,
1326 x_msg_count => x_msg_count,
1327 x_msg_data => x_msg_data
1328 );
1329
1330 FND_FILE.PUT_LINE (FND_FILE.LOG,' VENDOR ROLE CREATION :- CPL ID ' || l_partyid_v);
1331
1332 --Party Role Routine ('CUSTOMER')
1333
1334 Party_Role ( p_ChrId => l_chrid,
1335 p_cleId => Null,
1336 p_Rle_Code => 'CUSTOMER',
1337 p_PartyId => p_k_header_rec.party_id,
1338 p_Object_Code => G_JTF_PARTY,
1339 x_roleid => l_partyid,
1340 x_msg_count => x_msg_count,
1341 x_msg_data => x_msg_data
1342 );
1343
1344 FND_FILE.PUT_LINE (FND_FILE.LOG,' CUSTOMER ROLE CREATION :- CPL ID ' || l_partyid);
1345
1346
1347 Open l_thirdparty_csr (p_k_header_rec.bill_to_id);
1348 Fetch l_thirdparty_csr Into l_thirdparty_id;
1349 Close l_thirdparty_csr;
1350
1351 If l_thirdparty_Id Is Not Null Then
1352 If Not l_thirdparty_Id = p_k_header_rec.party_Id Then
1353 --Party Role Routine ('THIRD_PARTY')
1354 l_thirdparty_role := Nvl(p_k_header_rec.third_party_role, 'THIRD_PARTY');
1355
1356 Party_Role ( p_ChrId => l_chrid,
1357 p_cleId => Null,
1358 p_Rle_Code => l_thirdparty_role,
1359 p_PartyId => l_thirdparty_id,
1360 p_Object_Code => G_JTF_PARTY,
1361 x_roleid => l_partyid_t,
1362 x_msg_count => x_msg_count,
1363 x_msg_data => x_msg_data
1364 );
1365
1366 FND_FILE.PUT_LINE (FND_FILE.LOG,' THIRD PARTY ROLE CREATION :- CPL ID ' || l_partyid_t);
1367
1368 End If;
1369 End If;
1370
1371 If p_contact_tbl.count > 0 Then
1372
1373 i := p_Contact_Tbl.First;
1374
1375 Loop
1376
1377 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- PARTY ROLE ' ||p_Contact_tbl (i).party_role);
1378 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- CONTACT ID ' ||p_Contact_tbl (i).contact_id );
1379
1380
1381 If p_Contact_tbl (i).party_role = 'VENDOR' And l_partyid_v Is Not Null Then
1382 l_add2partyid := l_partyid_v;
1383 l_hdr_contactid := p_Contact_tbl (i).contact_id;
1384 Else
1385
1386 Open l_ra_hcontacts_cur (p_Contact_tbl (i).contact_id);
1387 fetch l_ra_hcontacts_cur into l_rah_party_id, l_hdr_contactid;
1388 close l_ra_hcontacts_cur;
1389
1390 /*
1391 Ramesh commented this portion due to TCA change
1392
1393 Open l_cust_csr (p_Contact_tbl (i).contact_id);
1394 Fetch l_cust_csr Into l_findparty_id;
1395 Close l_cust_csr;
1396 */
1397 -- if l_findparty_id = l_thirdparty_id Then
1398
1399 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- THIRDP ID ' ||l_thirdparty_id);
1400 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- CUSTMR ID ' ||p_k_header_rec.party_id);
1401 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- ORG CTC ID ' ||p_Contact_tbl (i).contact_id );
1402 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- RAH PTY ID ' ||l_rah_party_id );
1403 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- RAH CTC ID ' ||l_hdr_contactid);
1404
1405 if l_rah_party_id = l_thirdparty_id And l_partyid_t Is Not Null Then
1406 l_add2partyid := l_partyid_t;
1407 else
1408 l_add2partyid := l_partyid;
1409 end if;
1410
1411 End If;
1412
1413 If l_add2partyid Is Null Then
1414 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, p_Contact_tbl (i).contact_role
1415 || ' Contact (HEADER) Missing Role Id ' || p_Contact_tbl (i).contact_object_code);
1416 Raise G_EXCEPTION_HALT_VALIDATION;
1417 End If;
1418
1419 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- CPL ID ' || l_add2partyid);
1420 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- CRO CD ' || p_Contact_tbl (i).contact_role);
1421 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- CTC CD ' || l_hdr_contactid);
1422 FND_FILE.PUT_LINE (FND_FILE.LOG,' CONTACT CREATION :- JTO CD ' || p_Contact_tbl (i).contact_object_code);
1423
1424
1425
1426 l_ctcv_tbl_in(1).cpl_id := l_add2partyid;
1427 l_ctcv_tbl_in(1).dnz_chr_id := l_chrid;
1428 l_ctcv_tbl_in(1).cro_code := p_Contact_tbl (i).contact_role;
1429 l_ctcv_tbl_in(1).object1_id1 := l_hdr_contactid;
1430 l_ctcv_tbl_in(1).object1_id2 := '#';
1431 l_ctcv_tbl_in(1).jtot_object1_code:= p_Contact_tbl (i).contact_object_code;
1432
1433 okc_contract_party_pub.create_contact
1434 (
1435 p_api_version => l_api_version,
1436 p_init_msg_list => l_init_msg_list,
1437 x_return_status => l_return_status,
1438 x_msg_count => x_msg_count,
1439 x_msg_data => x_msg_data,
1440 p_ctcv_tbl => l_ctcv_tbl_in,
1441 x_ctcv_tbl => l_ctcv_tbl_out
1442 );
1443
1444 If l_return_status = 'S' then
1445 l_contact_id := l_ctcv_tbl_out(1).id;
1446 Else
1447 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, p_Contact_tbl (i).contact_role
1448 || ' Contact (HEADER) ' || p_Contact_tbl (i).contact_object_code);
1449 Raise G_EXCEPTION_HALT_VALIDATION;
1450 End if;
1451
1452 Exit When i = p_Contact_Tbl.Last;
1453
1454 i := p_Contact_Tbl.Next(i);
1455
1456 End Loop;
1457
1458 End If;
1459
1460
1461 --Grouping Routine
1462
1463 l_ctrgrp := Nvl(p_k_header_rec.chr_group, Nvl(Fnd_Profile.Value ('OKS_WARR_CONTRACT_GROUP'),2));
1464 l_cgcv_tbl_in(1).cgp_parent_id := l_ctrgrp;
1465 l_cgcv_tbl_in(1).included_chr_id := l_chrid;
1466 l_cgcv_tbl_in(1).object_version_number := OKC_API.G_MISS_NUM;
1467 l_cgcv_tbl_in(1).created_by := OKC_API.G_MISS_NUM;
1468 l_cgcv_tbl_in(1).creation_date := OKC_API.G_MISS_DATE;
1469 l_cgcv_tbl_in(1).last_updated_by := OKC_API.G_MISS_NUM;
1470 l_cgcv_tbl_in(1).last_update_date := OKC_API.G_MISS_DATE;
1471 l_cgcv_tbl_in(1).last_update_login := OKC_API.G_MISS_NUM;
1472 l_cgcv_tbl_in(1).included_cgp_id := NULL;
1473
1474 okc_contract_group_pub.create_contract_grpngs
1475 (
1476 p_api_version => l_api_version,
1477 p_init_msg_list => l_init_msg_list,
1478 x_return_status => l_return_status,
1479 x_msg_count => x_msg_count,
1480 x_msg_data => x_msg_data,
1481 p_cgcv_tbl => l_cgcv_tbl_in,
1482 x_cgcv_tbl => l_cgcv_tbl_out
1483 );
1484
1485 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- GROUPING STATUS ' || l_return_status);
1486
1487 If l_return_status = 'S' then
1488 l_grpid := l_cgcv_tbl_out(1).id;
1489 Else
1490 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Contract Group (HEADER)');
1491 Raise G_EXCEPTION_HALT_VALIDATION;
1492 end if;
1493
1494
1495 If p_k_header_rec.pdf_id Is Not Null Then
1496
1497 l_cpsv_tbl_in(1).pdf_id := p_k_header_rec.pdf_id;
1498 l_cpsv_tbl_in(1).CHR_ID := l_chrid;
1499 l_cpsv_tbl_in(1).USER_ID := FND_global.user_id;
1500 l_cpsv_tbl_in(1).IN_PROCESS_YN := OKC_API.G_MISS_CHAR;
1501 l_cpsv_tbl_in(1).object_version_number := OKC_API.G_MISS_NUM;
1502 l_cpsv_tbl_in(1).created_by := OKC_API.G_MISS_NUM;
1503 l_cpsv_tbl_in(1).creation_date := OKC_API.G_MISS_DATE;
1504 l_cpsv_tbl_in(1).last_updated_by := OKC_API.G_MISS_NUM;
1505 l_cpsv_tbl_in(1).last_update_date := OKC_API.G_MISS_DATE;
1506 l_cpsv_tbl_in(1).last_update_login := OKC_API.G_MISS_NUM;
1507
1508 okc_contract_pub.create_contract_process
1509 (
1510 p_api_version => l_api_version,
1511 p_init_msg_list => l_init_msg_list,
1512 x_return_status => l_return_status,
1513 x_msg_count => x_msg_count,
1514 x_msg_data => x_msg_data,
1515 p_cpsv_tbl => l_cpsv_tbl_in,
1516 x_cpsv_tbl => l_cpsv_tbl_out
1517 );
1518
1519
1520 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- PROCESS DEF STATUS ' || l_return_status);
1521
1522 If l_return_status = 'S' then
1523 l_pdfid := l_cpsv_tbl_out(1).id;
1524 Else
1525 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Contract WorkFlow (HEADER)');
1526 Raise G_EXCEPTION_HALT_VALIDATION;
1527 end if;
1528
1529
1530
1531 End If;
1532
1533
1534 --Rule Group Routine
1535
1536 l_rule_group_id := Check_Rule_Group_Exists(l_chrid,NULL);
1537
1538 If l_rule_group_id Is NULL Then
1539
1540 l_rgpv_tbl_in(1).chr_id := l_chrid;
1541 l_rgpv_tbl_in(1).sfwt_flag := 'N';
1542 l_rgpv_tbl_in(1).rgd_code := G_RULE_GROUP_CODE;
1543 l_rgpv_tbl_in(1).dnz_chr_id := l_chrid;
1544 l_rgpv_tbl_in(1).rgp_type := 'KRG';
1545
1546 create_rule_group
1547 (
1548 p_rgpv_tbl => l_rgpv_tbl_in,
1549 x_rgpv_tbl => l_rgpv_tbl_out,
1550 x_return_status => l_return_status,
1551 x_msg_count => x_msg_count,
1552 x_msg_data => x_msg_data
1553 );
1554
1555 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- RULE GROUP STATUS '||l_return_status);
1556
1557
1558 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1559 l_rule_group_id := l_rgpv_tbl_out(1).id;
1560 Else
1561 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Rule Group (HEADER)');
1562 Raise G_EXCEPTION_HALT_VALIDATION;
1563 End If;
1564 End If;
1565
1566 if p_k_header_rec.tax_exemption_id Is Not Null Then
1567
1568 clear_rule_table (l_rulv_tbl_in);
1569
1570 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'TAX');
1571
1572 If l_rule_id Is NULL Then
1573
1574 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
1575 l_rulv_tbl_in(1).sfwt_flag := 'N';
1576 l_rulv_tbl_in(1).std_template_yn := 'N';
1577 l_rulv_tbl_in(1).warn_yn := 'N';
1578 l_rulv_tbl_in(1).rule_information_category := 'TAX';
1579 l_rulv_tbl_in(1).object1_id1 := p_k_header_rec.tax_exemption_id;
1580 l_rulv_tbl_in(1).object1_id2 := '#';
1581 l_rulv_tbl_in(1).JTOT_OBJECT1_CODE := G_JTF_TAXEXEMP;
1582 l_rulv_tbl_in(1).object2_id1 := 'TAX_CONTROL_FLAG';
1583 l_rulv_tbl_in(1).object2_id2 := p_k_header_rec.tax_status_flag;
1584 l_rulv_tbl_in(1).JTOT_OBJECT2_CODE := G_JTF_TAXCTRL;
1585 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
1586
1587 create_rules (
1588 p_rulv_tbl => l_rulv_tbl_in,
1589 x_rulv_tbl => l_rulv_tbl_out,
1590 x_return_status => l_return_status,
1591 x_msg_count => x_msg_count,
1592 x_msg_data => x_msg_data
1593 );
1594
1595 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- TAX RULE STATUS ' || l_return_Status);
1596
1597
1598 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1599 l_rule_id := l_rulv_tbl_out(1).id;
1600 Else
1601 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'TAX EXEMPTION (HEADER)');
1602 Raise G_EXCEPTION_HALT_VALIDATION;
1603 End If;
1604
1605 End If;
1606
1607 End If;
1608
1609
1610
1611
1612 if p_k_header_rec.price_list_id Is Not Null Then
1613
1614 clear_rule_table (l_rulv_tbl_in);
1615
1616 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'PRE');
1617
1618 If l_rule_id Is NULL Then
1619
1620 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
1621 l_rulv_tbl_in(1).sfwt_flag := 'N';
1622 l_rulv_tbl_in(1).std_template_yn := 'N';
1623 l_rulv_tbl_in(1).warn_yn := 'N';
1624 l_rulv_tbl_in(1).rule_information_category := 'PRE';
1625 l_rulv_tbl_in(1).object1_id1 := p_k_header_rec.price_list_id;
1626 l_rulv_tbl_in(1).object1_id2 := '#';
1627 l_rulv_tbl_in(1).JTOT_OBJECT1_CODE := G_JTF_PRICE;
1628 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
1629
1630 create_rules (
1631 p_rulv_tbl => l_rulv_tbl_in,
1632 x_rulv_tbl => l_rulv_tbl_out,
1633 x_return_status => l_return_status,
1634 x_msg_count => x_msg_count,
1635 x_msg_data => x_msg_data
1636 );
1637
1638 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- PRE RULE STATUS ' || l_return_Status);
1639
1640 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1641 l_rule_id := l_rulv_tbl_out(1).id;
1642 Else
1643 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'PRICE LIST (HEADER)');
1644 Raise G_EXCEPTION_HALT_VALIDATION;
1645 End If;
1646
1647 End If;
1648
1649 End If;
1650
1651
1652 if p_k_header_rec.payment_term_id Is Not Null Then
1653
1654 clear_rule_table (l_rulv_tbl_in);
1655
1656 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'PTR');
1657
1658 If l_rule_id Is NULL Then
1659
1660 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
1661 l_rulv_tbl_in(1).sfwt_flag := 'N';
1662 l_rulv_tbl_in(1).std_template_yn := 'N';
1663 l_rulv_tbl_in(1).warn_yn := 'N';
1664 l_rulv_tbl_in(1).rule_information_category := 'PTR';
1665 l_rulv_tbl_in(1).object1_id1 := p_k_header_rec.payment_term_id;
1666 l_rulv_tbl_in(1).object1_id2 := '#';
1667 l_rulv_tbl_in(1).JTOT_OBJECT1_CODE := G_JTF_PAYMENT_TERM;
1668 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
1669
1670 create_rules (
1671 p_rulv_tbl => l_rulv_tbl_in,
1672 x_rulv_tbl => l_rulv_tbl_out,
1673 x_return_status => l_return_status,
1674 x_msg_count => x_msg_count,
1675 x_msg_data => x_msg_data
1676 );
1677 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- PTR RULE STATUS ' || l_return_Status);
1678
1679
1680 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1681 l_rule_id := l_rulv_tbl_out(1).id;
1682 Else
1683 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'PAYMENT TERM (HEADER)');
1684 Raise G_EXCEPTION_HALT_VALIDATION;
1685 End If;
1686
1687 End If;
1688
1689 End If;
1690
1691
1692 if p_k_header_rec.cvn_type Is Not Null Then
1693
1694 clear_rule_table (l_rulv_tbl_in);
1695
1696 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'CVN');
1697
1698 If l_rule_id Is NULL Then
1699
1700 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
1701 l_rulv_tbl_in(1).sfwt_flag := 'N';
1702 l_rulv_tbl_in(1).std_template_yn := 'N';
1703 l_rulv_tbl_in(1).warn_yn := 'N';
1704 l_rulv_tbl_in(1).rule_information_category := 'CVN';
1705 l_rulv_tbl_in(1).object1_id1 := p_k_header_rec.cvn_type;
1706 l_rulv_tbl_in(1).object1_id2 := '#';
1707 l_rulv_tbl_in(1).JTOT_OBJECT1_CODE := G_JTF_CONV_TYPE;
1708 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
1709 l_rulv_tbl_in(1).rule_information1 := p_k_header_rec.cvn_rate;
1710 l_rulv_tbl_in(1).rule_information2 := p_k_header_rec.cvn_date;
1711 l_rulv_tbl_in(1).rule_information3 := p_k_header_rec.cvn_euro_rate;
1712
1713 create_rules (
1714 p_rulv_tbl => l_rulv_tbl_in,
1715 x_rulv_tbl => l_rulv_tbl_out,
1716 x_return_status => l_return_status,
1717 x_msg_count => x_msg_count,
1718 x_msg_data => x_msg_data
1719 );
1720
1721 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- CVN RULE STATUS ' || l_return_Status);
1722
1723 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1724 l_rule_id := l_rulv_tbl_out(1).id;
1725 Else
1726 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'CONVERSION TYPE (HEADER)');
1727 Raise G_EXCEPTION_HALT_VALIDATION;
1728 End If;
1729
1730 End If;
1731
1732 End If;
1733
1734 --Schedule Billing Routine
1735
1736 clear_rule_table (l_rulv_tbl_in);
1737
1738 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'SBG');
1739
1740 If l_rule_id Is NULL Then
1741
1742 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
1743 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
1744
1745 l_rulv_tbl_in(1).sfwt_flag := 'N';
1746 l_rulv_tbl_in(1).std_template_yn := 'N';
1747 l_rulv_tbl_in(1).warn_yn := 'N';
1748 l_rulv_tbl_in(1).rule_information_category := 'SBG';
1749 l_rulv_tbl_in(1).rule_information1 := 1;
1750 l_rulv_tbl_in(1).rule_information2 := p_k_header_rec.Billing_freq;
1751 l_rulv_tbl_in(1).rule_information3 := p_k_header_rec.first_billupto_date;
1752 l_rulv_tbl_in(1).rule_information4 := p_k_header_rec.first_billon_date;
1753
1754 l_rulv_tbl_in(1).rule_information5 := 1;
1755 l_rulv_tbl_in(1).rule_information6 := p_k_header_rec.Billing_freq;
1756 l_rulv_tbl_in(1).rule_information7 := p_k_header_rec.offset_duration;
1757 l_rulv_tbl_in(1).rule_information8 := 'DAY';
1758 l_rulv_tbl_in(1).rule_information9 := 'N';
1759
1760 create_rules (
1761 p_rulv_tbl => l_rulv_tbl_in,
1762 x_rulv_tbl => l_rulv_tbl_out,
1763 x_return_status => l_return_status,
1764 x_msg_count => x_msg_count,
1765 x_msg_data => x_msg_data
1766 );
1767
1768
1769 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- SBG RULE STATUS ' || l_return_Status);
1770
1771
1772 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1773 l_rule_id := l_rulv_tbl_out(1).id;
1774 Else
1775 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Schedule Billing Rule (HEADER)');
1776 Raise G_EXCEPTION_HALT_VALIDATION;
1777 End If;
1778
1779 End If;
1780
1781
1782
1783 --Bill To Routine
1784
1785 If p_k_header_rec.bill_to_id Is Not Null Then
1786
1787 clear_rule_table (l_rulv_tbl_in);
1788
1789 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'BTO');
1790
1791 If l_rule_id Is NULL Then
1792
1793 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
1794 l_rulv_tbl_in(1).sfwt_flag := 'N';
1795 l_rulv_tbl_in(1).std_template_yn := 'N';
1796 l_rulv_tbl_in(1).warn_yn := 'N';
1797 l_rulv_tbl_in(1).rule_information_category := 'BTO';
1798 l_rulv_tbl_in(1).object1_id1 := p_k_header_rec.bill_to_id;
1799 l_rulv_tbl_in(1).object1_id2 := '#';
1800 l_rulv_tbl_in(1).jtot_object1_code := G_JTF_BILLTO;
1801 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
1802
1803 create_rules (
1804 p_rulv_tbl => l_rulv_tbl_in,
1805 x_rulv_tbl => l_rulv_tbl_out,
1806 x_return_status => l_return_status,
1807 x_msg_count => x_msg_count,
1808 x_msg_data => x_msg_data
1809 );
1810
1811 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- BTO RULE STATUS ' || l_return_Status);
1812
1813 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1814 l_rule_id := l_rulv_tbl_out(1).id;
1815 Else
1816 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'BillTo Id (HEADER)');
1817 Raise G_EXCEPTION_HALT_VALIDATION;
1818 End If;
1819
1820 End If;
1821
1822
1823 End If;
1824
1825 --Ship To Routine
1826
1827 If p_k_header_rec.ship_to_id Is Not Null Then
1828
1829 clear_rule_table (l_rulv_tbl_in);
1830
1831 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'STO');
1832
1833 If l_rule_id Is NULL Then
1834
1835 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
1836 l_rulv_tbl_in(1).sfwt_flag := 'N';
1837 l_rulv_tbl_in(1).std_template_yn := 'N';
1838 l_rulv_tbl_in(1).warn_yn := 'N';
1839 l_rulv_tbl_in(1).rule_information_category := 'STO';
1840 l_rulv_tbl_in(1).object1_id1 := p_k_header_rec.ship_to_id;
1841 l_rulv_tbl_in(1).object1_id2 := '#';
1842 l_rulv_tbl_in(1).jtot_object1_code := G_JTF_SHIPTO;
1843 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
1844
1845 create_rules (
1846 p_rulv_tbl => l_rulv_tbl_in,
1847 x_rulv_tbl => l_rulv_tbl_out,
1848 x_return_status => l_return_status,
1849 x_msg_count => x_msg_count,
1850 x_msg_data => x_msg_data
1851 );
1852
1853 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- STO RULE STATUS ' || l_return_Status);
1854
1855
1856 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1857 l_rule_id := l_rulv_tbl_out(1).id;
1858 Else
1859 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ShipTo Id (HEADER)');
1860 Raise G_EXCEPTION_HALT_VALIDATION;
1861 End If;
1862
1863 End If;
1864
1865
1866 End If;
1867
1868 If p_k_header_rec.agreement_id Is Not Null Then
1869
1870 --Agreement ID Routine
1871
1872 l_gvev_tbl_in(1).chr_id := l_chrid;
1873 l_gvev_tbl_in(1).isa_agreement_id := p_k_header_rec.agreement_id;
1874 l_gvev_tbl_in(1).copied_only_yn := 'Y';
1875 l_gvev_tbl_in(1).dnz_chr_id := l_chrid;
1876
1877 okc_contract_pub.create_governance
1878 (
1879 p_api_version => l_api_version,
1880 p_init_msg_list => l_init_msg_list,
1881 x_return_status => l_return_status,
1882 x_msg_count => x_msg_count,
1883 x_msg_data => x_msg_data,
1884 p_gvev_tbl => l_gvev_tbl_in,
1885 x_gvev_tbl => l_gvev_tbl_out
1886 );
1887
1888 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- AGREEMENT RULE STATUS ' || l_return_Status);
1889
1890
1891 If l_return_status = 'S' then
1892 l_govern_id := l_gvev_tbl_out(1).id;
1893 Else
1894 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Agreement Id (HEADER)');
1895 Raise G_EXCEPTION_HALT_VALIDATION;
1896 End if;
1897
1898 End If;
1899
1900 --Accounting Rule ID Routine
1901
1902 If p_k_header_rec.accounting_rule_id Is Not Null Then
1903
1904 clear_rule_table (l_rulv_tbl_in);
1905
1906 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'ARL');
1907
1908 If l_rule_id Is NULL Then
1909
1910 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
1911 l_rulv_tbl_in(1).sfwt_flag := 'N';
1912 l_rulv_tbl_in(1).std_template_yn := 'N';
1913 l_rulv_tbl_in(1).warn_yn := 'N';
1914 l_rulv_tbl_in(1).rule_information_category := 'ARL';
1915 l_rulv_tbl_in(1).object1_id1 := p_k_header_rec.accounting_rule_id;
1916 l_rulv_tbl_in(1).object1_id2 := '#';
1917 l_rulv_tbl_in(1).jtot_object1_code := G_JTF_ARL;
1918 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
1919
1920 create_rules (
1921 p_rulv_tbl => l_rulv_tbl_in,
1922 x_rulv_tbl => l_rulv_tbl_out,
1923 x_return_status => l_return_status,
1924 x_msg_count => x_msg_count,
1925 x_msg_data => x_msg_data
1926 );
1927
1928 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- ARL RULE STATUS ' || l_return_Status);
1929
1930 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1931 l_rule_id := l_rulv_tbl_out(1).id;
1932 Else
1933 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Accounting Id (HEADER)');
1934 Raise G_EXCEPTION_HALT_VALIDATION;
1935 End If;
1936 End If;
1937
1938 End If;
1939
1940 --Invoice Rule ID Routine
1941
1942 If p_k_header_rec.invoice_rule_id Is Not Null Then
1943
1944 clear_rule_table (l_rulv_tbl_in);
1945
1946 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'IRE');
1947
1948 If l_rule_id Is NULL Then
1949
1950 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
1951 l_rulv_tbl_in(1).sfwt_flag := 'N';
1952 l_rulv_tbl_in(1).std_template_yn := 'N';
1953 l_rulv_tbl_in(1).warn_yn := 'N';
1954 l_rulv_tbl_in(1).rule_information_category := 'IRE';
1955 l_rulv_tbl_in(1).object1_id1 := p_k_header_rec.invoice_rule_id;
1956 l_rulv_tbl_in(1).object1_id2 := '#';
1957 l_rulv_tbl_in(1).jtot_object1_code := G_JTF_IRE;
1958 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
1959
1960 create_rules (
1961 p_rulv_tbl => l_rulv_tbl_in,
1962 x_rulv_tbl => l_rulv_tbl_out,
1963 x_return_status => l_return_status,
1964 x_msg_count => x_msg_count,
1965 x_msg_data => x_msg_data
1966 );
1967
1968
1969 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- IRE RULE STATUS ' || l_return_Status);
1970
1971 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1972 l_rule_id := l_rulv_tbl_out(1).id;
1973 Else
1974 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Invoice Id (HEADER)');
1975 Raise G_EXCEPTION_HALT_VALIDATION;
1976 End If;
1977
1978 End If;
1979 End If;
1980
1981
1982 --Renewal Type Routine
1983
1984 If p_k_header_rec.renewal_type Is Not Null Then
1985
1986 clear_rule_table (l_rulv_tbl_in);
1987
1988 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'REN');
1989
1990 If l_rule_id Is NULL Then
1991
1992 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
1993 l_rulv_tbl_in(1).sfwt_flag := 'N';
1994 l_rulv_tbl_in(1).std_template_yn := 'N';
1995 l_rulv_tbl_in(1).warn_yn := 'N';
1996 l_rulv_tbl_in(1).rule_information_category := 'REN';
1997 l_rulv_tbl_in(1).rule_information1 := p_k_header_rec.renewal_type;
1998 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
1999
2000 create_rules (
2001 p_rulv_tbl => l_rulv_tbl_in,
2002 x_rulv_tbl => l_rulv_tbl_out,
2003 x_return_status => l_return_status,
2004 x_msg_count => x_msg_count,
2005 x_msg_data => x_msg_data
2006 );
2007
2008 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- REN RULE STATUS ' || l_return_Status);
2009
2010 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2011 l_rule_id := l_rulv_tbl_out(1).id;
2012 Else
2013 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Renewal Type (HEADER)');
2014 Raise G_EXCEPTION_HALT_VALIDATION;
2015 End If;
2016
2017 End If;
2018
2019 End If;
2020
2021
2022 --Renewal Pricing Type
2023
2024 If p_k_header_rec.renewal_pricing_type Is Not Null Then
2025
2026 clear_rule_table (l_rulv_tbl_in);
2027
2028 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'RPT');
2029
2030 If l_rule_id Is NULL Then
2031
2032 If p_k_header_rec.renewal_pricing_type = 'PCT' Then
2033 l_rulv_tbl_in(1).rule_information2 := p_k_header_rec.renewal_markup;
2034 Else
2035 l_rulv_tbl_in(1).rule_information2 := Null;
2036 End If;
2037
2038 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2039 l_rulv_tbl_in(1).sfwt_flag := 'N';
2040 l_rulv_tbl_in(1).std_template_yn := 'N';
2041 l_rulv_tbl_in(1).warn_yn := 'N';
2042 l_rulv_tbl_in(1).rule_information_category := 'RPT';
2043 l_rulv_tbl_in(1).object1_id1 := p_k_header_rec.renewal_price_list_id;
2044 l_rulv_tbl_in(1).object1_id2 := '#';
2045 l_rulv_tbl_in(1).JTOT_OBJECT1_CODE := G_JTF_PRICE;
2046 l_rulv_tbl_in(1).rule_information1 := p_k_header_rec.renewal_pricing_type;
2047 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
2048
2049 create_rules (
2050 p_rulv_tbl => l_rulv_tbl_in,
2051 x_rulv_tbl => l_rulv_tbl_out,
2052 x_return_status => l_return_status,
2053 x_msg_count => x_msg_count,
2054 x_msg_data => x_msg_data
2055 );
2056
2057 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- RPT RULE STATUS ' || l_return_Status);
2058
2059
2060 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2061 l_rule_id := l_rulv_tbl_out(1).id;
2062 Else
2063 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Renewal Pricing (HEADER)');
2064 Raise G_EXCEPTION_HALT_VALIDATION;
2065 End If;
2066
2067 End If;
2068 End If;
2069
2070 --Renewal PO Required
2071
2072 If p_k_header_rec.renewal_po Is Not Null Then
2073
2074 clear_rule_table (l_rulv_tbl_in);
2075
2076 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'RPO');
2077
2078 If l_rule_id Is NULL Then
2079
2080 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2081 l_rulv_tbl_in(1).sfwt_flag := 'N';
2082 l_rulv_tbl_in(1).std_template_yn := 'N';
2083 l_rulv_tbl_in(1).warn_yn := 'N';
2084 l_rulv_tbl_in(1).rule_information_category := 'RPO';
2085 l_rulv_tbl_in(1).rule_information1 := p_k_header_rec.renewal_po;
2086 l_rulv_tbl_in(1).dnz_chr_id := l_chrid;
2087
2088 create_rules (
2089 p_rulv_tbl => l_rulv_tbl_in,
2090 x_rulv_tbl => l_rulv_tbl_out,
2091 x_return_status => l_return_status,
2092 x_msg_count => x_msg_count,
2093 x_msg_data => x_msg_data
2094 );
2095
2096 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- RPO RULE STATUS ' || l_return_Status);
2097
2098
2099 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2100 l_rule_id := l_rulv_tbl_out(1).id;
2101 Else
2102 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Renewal PO (HEADER)');
2103 Raise G_EXCEPTION_HALT_VALIDATION;
2104 End If;
2105
2106 End If;
2107
2108 End If;
2109 If p_k_header_rec.order_hdr_id Is Not Null Then
2110 Create_Obj_Rel
2111 (
2112 p_K_id => l_chrid,
2113 p_line_id => Null,
2114 p_orderhdrid => p_k_header_rec.order_hdr_id,
2115 p_orderlineid => Null,
2116 x_return_status => l_return_status,
2117 x_msg_count => x_msg_count,
2118 x_msg_data => x_msg_data,
2119 x_crjv_tbl_out => l_crjv_tbl_out
2120 );
2121 End If;
2122 FND_FILE.PUT_LINE (FND_FILE.LOG,'K HDR CREATION :- OBJ RULE STATUS ' || l_return_Status);
2123
2124
2125 If Not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2126 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Order Header Id (HEADER)');
2127 Raise G_EXCEPTION_HALT_VALIDATION;
2128 End If;
2129
2130
2131 x_chr_id := l_chrid;
2132
2133 Exception
2134 When G_EXCEPTION_HALT_VALIDATION Then
2135 x_return_status := l_return_status;
2136 When Others Then
2137 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2138 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2139
2140 END Create_K_Hdr;
2141
2142
2143 Procedure Create_OSO_K_Service_Lines
2144 (
2145 p_k_line_rec IN K_line_Service_Rec_type
2146 , p_Contact_tbl IN Contact_Tbl
2147 , p_salescredit_tbl_in IN SalesCredit_Tbl
2148 , x_service_line_id OUT NOCOPY Number
2149 , x_return_status OUT NOCOPY Varchar2
2150 , x_msg_count OUT NOCOPY Number
2151 , x_msg_data OUT NOCOPY Varchar2
2152 )
2153 Is
2154
2155 l_api_version CONSTANT NUMBER := 1.0;
2156 l_init_msg_list CONSTANT VARCHAR2(1) := OKC_API.G_FALSE;
2157 l_return_status VARCHAR2(1) := 'S';
2158 l_index VARCHAR2(2000);
2159
2160 l_ctcv_tbl_in okc_contract_party_pub.ctcv_tbl_type;
2161 l_ctcv_tbl_out okc_contract_party_pub.ctcv_tbl_type;
2162
2163
2164
2165 Cursor l_mtl_csr(p_inventory_id Number, p_organization_id Number) Is
2166 Select MTL.SERVICE_ITEM_FLAG
2167 ,MTL.USAGE_ITEM_FLAG
2168 From OKX_SYSTEM_ITEMS_V MTL
2169 Where MTL.id1 = p_Inventory_id
2170 And MTL.Organization_id = p_organization_id;
2171
2172 l_mtl_rec l_mtl_csr%rowtype;
2173
2174 Cursor l_ctr_csr (p_id Number) Is
2175 Select Counter_Group_id
2176 From OKX_CTR_ASSOCIATIONS_V
2177 Where Source_Object_Id = p_id;
2178
2179 Cursor l_billto_csr (p_billto Number) Is
2180 Select cust_account_id from OKX_CUST_SITE_USES_V
2181 where id1 = p_billto and id2 = '#';
2182
2183 Cursor l_ra_hcontacts_cur (p_contact_id number) Is
2184 Select hzr.object_id
2185 , hzr.party_id
2186 --NPALEPU
2187 --18-JUN-2005
2188 --TCA Project
2189 --Replaced hz_party_relationships table with hz_relationships table and ra_hcontacts view with OKS_RA_HCONTACTS_V
2190 --Replaced hzr.party_relationship_id column with hzr.relationship_id column and added new conditions
2191 /* From
2192 ra_hcontacts rah,
2193 hz_party_relationships hzr
2194 Where rah.contact_id = p_contact_id
2195 and rah.party_relationship_id = hzr.party_relationship_id;*/
2196 From OKS_RA_HCONTACTS_V rah,
2197 hz_relationships hzr
2198 WHERE rah.contact_id = p_contact_id
2199 and rah.party_relationship_id = hzr.relationship_id
2200 AND hzr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2201 AND hzr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2202 AND hzr.DIRECTIONAL_FLAG = 'F';
2203 --END NPALEPU
2204
2205 l_ctr_grpid Varchar2(40);
2206
2207 --Contract Line Table
2208 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
2209 l_clev_tbl_out okc_contract_pub.clev_tbl_type;
2210
2211 --Contract Item
2212 l_cimv_tbl_in okc_contract_item_pub.cimv_tbl_type;
2213 l_cimv_tbl_out okc_contract_item_pub.cimv_tbl_type;
2214
2215 --Rule Related
2216
2217 l_rgpv_tbl_in okc_rule_pub.rgpv_tbl_type;
2218 l_rgpv_tbl_out okc_rule_pub.rgpv_tbl_type;
2219
2220 l_rulv_tbl_in okc_rule_pub.rulv_tbl_type;
2221 l_rulv_tbl_out okc_rule_pub.rulv_tbl_type;
2222
2223 --Time Value Related
2224 l_isev_ext_tbl_in okc_time_pub.isev_ext_tbl_type;
2225 l_isev_ext_tbl_out okc_time_pub.isev_ext_tbl_type;
2226
2227 --SalesCredit
2228 l_scrv_tbl_in oks_sales_credit_pub.scrv_tbl_type;
2229 l_scrv_tbl_out oks_sales_credit_pub.scrv_tbl_type;
2230
2231 --Obj Rel
2232 l_crjv_tbl_out OKC_K_REL_OBJS_PUB.crjv_tbl_type;
2233
2234 --Coverage
2235 l_cov_rec OKS_COVERAGES_PUB.ac_rec_type;
2236
2237 --Counters
2238 l_ctr_grp_id_template NUMBER;
2239 l_ctr_grp_id_instance NUMBER;
2240
2241 --Return IDs
2242
2243 l_line_id NUMBER;
2244 l_rule_group_id NUMBER;
2245 l_rule_id NUMBER;
2246 l_line_item_id NUMBER;
2247 l_time_value_id NUMBER;
2248 l_cov_id NUMBER;
2249 l_salescredit_id NUMBER;
2250
2251
2252 --TimeUnits
2253 l_duration NUMBER;
2254 l_timeunits VARCHAR2(240);
2255
2256
2257 --General
2258 l_hdrsdt DATE;
2259 l_hdredt DATE;
2260 l_hdrstatus CHAR;
2261 l_hdrorgid Number;
2262 l_lsl_id NUMBER;
2263 l_jtot_object VARCHAR2(30) := NULL;
2264 i NUMBER;
2265 l_can_object NUMBER;
2266 l_line_party_role_id NUMBER;
2267 l_lin_party_id NUMBER;
2268 l_lin_contactid NUMBER;
2269 l_line_contact_id NUMBER;
2270 l_role VARCHAR2(40);
2271 l_obj VARCHAR2(40);
2272
2273 Begin
2274
2275 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2276 Okc_context.set_okc_org_context (p_k_line_rec.org_id, p_k_line_rec.organization_id);
2277 l_Line_id := Get_K_Cle_Id
2278 (
2279 p_ChrId => p_k_line_rec.k_id
2280 , p_InvServiceId => p_k_line_rec.srv_id
2281 , p_StartDate => p_k_line_rec.srv_sdt
2282 , p_EndDate => p_k_line_rec.srv_edt
2283 );
2284
2285
2286 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- LINE ID ' || l_line_id);
2287
2288 If l_line_id Is Not Null Then
2289 x_Service_Line_id := l_line_id;
2290 l_return_status := OKC_API.G_RET_STS_SUCCESS;
2291 Raise G_EXCEPTION_HALT_VALIDATION;
2292 End If;
2293
2294 check_hdr_effectivity
2295 (
2296 p_chr_id => p_k_line_rec.k_id,
2297 p_srv_sdt => p_k_line_rec.srv_sdt,
2298 p_srv_edt => p_k_line_rec.srv_edt,
2299 x_hdr_sdt => l_hdrsdt,
2300 x_hdr_edt => l_hdredt,
2301 x_org_id => l_hdrorgid,
2302 x_status => l_hdrstatus
2303 );
2304
2305 If l_hdrstatus = 'E' Then
2306 l_return_status := OKC_API.G_RET_STS_ERROR;
2307 Raise G_EXCEPTION_HALT_VALIDATION;
2308
2309 ElsIf l_hdrstatus = 'Y' Then
2310
2311 Update_Hdr_Dates
2312 (
2313 p_chr_id => p_k_line_rec.k_id,
2314 p_new_sdt => l_hdrsdt,
2315 p_new_edt => l_hdredt,
2316 x_status => l_return_status,
2317 x_msg_count => x_msg_count,
2318 x_msg_data => x_msg_data
2319 );
2320
2321 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- UPDATE HDR ' || l_return_status);
2322
2323
2324 If Not l_return_Status = 'S' Then
2325 l_return_status := OKC_API.G_RET_STS_ERROR;
2326 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Header Effectivity Update (LINE)');
2327 Raise G_EXCEPTION_HALT_VALIDATION;
2328 End If;
2329
2330 End If;
2331
2332 Open l_mtl_csr(p_k_line_rec.srv_id,p_k_line_rec.organization_id);
2333
2334 Fetch l_mtl_csr into l_mtl_rec;
2335
2336 If l_mtl_csr%notfound Then
2337 FND_FILE.PUT_LINE (FND_FILE.LOG,'L_LINE_CSR FAILURE');
2338 Close l_mtl_csr ;
2339 l_return_status := OKC_API.G_RET_STS_ERROR;
2340 OKC_API.set_message(G_APP_NAME,'OKS_CUST_PROD_DTLS_NOT_FOUND','INVENTORY_ITEM',p_k_line_rec.srv_id);
2341 Raise G_EXCEPTION_HALT_VALIDATION;
2342 End if;
2343 Close l_mtl_csr;
2344
2345 l_lsl_id := 12;
2346 l_jtot_object := 'OKX_USAGE';
2347
2348 l_clev_tbl_in(1).chr_id := p_k_line_rec.k_id;
2349 l_clev_tbl_in(1).sfwt_flag := 'N';
2350 l_clev_tbl_in(1).lse_id := l_lsl_id;
2351 l_clev_tbl_in(1).line_number := p_k_line_rec.k_line_number;
2352 l_clev_tbl_in(1).sts_code := NVL(p_k_line_rec.line_sts_code, 'ACTIVE');
2353 l_clev_tbl_in(1).display_sequence := 1;
2354 l_clev_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2355 --l_clev_tbl_in(1).name := Substr(p_k_line_rec.srv_segment1,1,50);
2356 l_clev_tbl_in(1).name := Null;
2357 l_clev_tbl_in(1).item_description := p_k_line_rec.srv_desc;
2358 l_clev_tbl_in(1).start_date := p_k_line_rec.srv_sdt;
2359 l_clev_tbl_in(1).end_date := p_k_line_rec.srv_edt;
2360 l_clev_tbl_in(1).exception_yn := 'N';
2361 l_clev_tbl_in(1).currency_code := p_k_line_rec.currency;
2362 l_clev_tbl_in(1).price_level_ind := Priced_YN(l_lsl_id);
2363 l_clev_tbl_in(1).trn_code := p_k_line_rec.reason_code;
2364 l_clev_tbl_in(1).comments := p_k_line_rec.reason_comments;
2365 l_clev_tbl_in(1).Attribute1 := p_k_line_rec.attribute1;
2366 l_clev_tbl_in(1).Attribute2 := p_k_line_rec.attribute2;
2367 l_clev_tbl_in(1).Attribute3 := p_k_line_rec.attribute3;
2368 l_clev_tbl_in(1).Attribute4 := p_k_line_rec.attribute4;
2369 l_clev_tbl_in(1).Attribute5 := p_k_line_rec.attribute5;
2370 l_clev_tbl_in(1).Attribute6 := p_k_line_rec.attribute6;
2371 l_clev_tbl_in(1).Attribute7 := p_k_line_rec.attribute7;
2372 l_clev_tbl_in(1).Attribute8 := p_k_line_rec.attribute8;
2373 l_clev_tbl_in(1).Attribute9 := p_k_line_rec.attribute9;
2374 l_clev_tbl_in(1).Attribute10 := p_k_line_rec.attribute10;
2375 l_clev_tbl_in(1).Attribute11 := p_k_line_rec.attribute11;
2376 l_clev_tbl_in(1).Attribute12 := p_k_line_rec.attribute12;
2377 l_clev_tbl_in(1).Attribute13 := p_k_line_rec.attribute13;
2378 l_clev_tbl_in(1).Attribute14 := p_k_line_rec.attribute14;
2379 l_clev_tbl_in(1).Attribute15 := p_k_line_rec.attribute15;
2380
2381
2382 okc_contract_pub.create_contract_line
2383 (
2384 p_api_version => l_api_version,
2385 p_init_msg_list => l_init_msg_list,
2386 x_return_status => l_return_status,
2387 x_msg_count => x_msg_count,
2388 x_msg_data => x_msg_data,
2389 p_clev_tbl => l_clev_tbl_in,
2390 x_clev_tbl => l_clev_tbl_out
2391 );
2392
2393
2394 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- LINE STATUS ' || l_return_status);
2395
2396 If l_return_status = 'S' then
2397 l_line_id := l_clev_tbl_out(1).id;
2398 Else
2399 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Line (LINE)');
2400 Raise G_EXCEPTION_HALT_VALIDATION;
2401 End if;
2402
2403 okc_time_util_pub.get_duration
2404 (
2405 p_start_date => p_k_line_rec.srv_sdt,
2406 p_end_date => p_k_line_rec.srv_edt,
2407 x_duration => l_duration,
2408 x_timeunit => l_timeunits,
2409 x_return_status => l_return_status
2410 );
2411
2412 If Not l_return_status = 'S' Then
2413 Raise G_EXCEPTION_HALT_VALIDATION;
2414 End If;
2415
2416 --Create Contract Item
2417
2418 l_cimv_tbl_in(1).cle_id := l_line_id;
2419 l_cimv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2420 l_cimv_tbl_in(1).object1_id1 := p_k_line_rec.srv_id;
2421 l_cimv_tbl_in(1).object1_id2 := p_k_line_rec.organization_id;
2422 l_cimv_tbl_in(1).jtot_object1_code := l_jtot_object;
2423 l_cimv_tbl_in(1).exception_yn := 'N';
2424 l_cimv_tbl_in(1).number_of_items := l_duration;
2425 l_cimv_tbl_in(1).uom_code := l_timeunits;
2426
2427 okc_contract_item_pub.create_contract_item
2428 (
2429 p_api_version => l_api_version,
2430 p_init_msg_list => l_init_msg_list,
2431 x_return_status => l_return_status,
2432 x_msg_count => x_msg_count,
2433 x_msg_data => x_msg_data,
2434 p_cimv_tbl => l_cimv_tbl_in,
2435 x_cimv_tbl => l_cimv_tbl_out
2436 );
2437
2438 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- KITEM STATUS ' || l_return_status);
2439
2440
2441 If l_return_status = 'S' then
2442 l_line_item_id := l_cimv_tbl_out(1).id;
2443 Else
2444 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Service Inventory Item ID ' || p_k_line_rec.srv_id || ' ORG ' + okc_context.get_okc_organization_id);
2445 Raise G_EXCEPTION_HALT_VALIDATION;
2446 End if;
2447
2448 --Create SalesCredits
2449
2450 If p_salescredit_tbl_in.count > 0 Then
2451
2452 i := p_salescredit_Tbl_in.First;
2453
2454 Loop
2455
2456 l_scrv_tbl_in (1).percent := p_salescredit_tbl_in(i).percent;
2457 l_scrv_tbl_in (1).chr_id := p_k_line_rec.k_id;
2458 l_scrv_tbl_in (1).cle_id := l_line_id;
2459 l_scrv_tbl_in (1).ctc_id := p_salescredit_tbl_in(i).ctc_id;
2460 l_scrv_tbl_in (1).sales_credit_type_id1 := p_salescredit_tbl_in(i).sales_credit_type_id;
2461 l_scrv_tbl_in (1).sales_credit_type_id2 := '#';
2462
2463
2464 OKS_SALES_CREDIT_PUB.Insert_Sales_Credit(
2465 p_api_version => 1.0,
2466 p_init_msg_list => OKC_API.G_FALSE,
2467 x_return_status => x_return_status,
2468 x_msg_count => x_msg_count,
2469 x_msg_data => x_msg_data,
2470 p_scrv_tbl => l_scrv_tbl_in,
2471 x_scrv_tbl => l_scrv_tbl_out);
2472
2473 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- SALESCREDIT STATUS ' || l_return_status);
2474
2475
2476 If l_return_status = 'S' then
2477 l_salescredit_id := l_scrv_tbl_out(1).id;
2478 Else
2479 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Sales Credit Failure');
2480 Raise G_EXCEPTION_HALT_VALIDATION;
2481 End if;
2482
2483 Exit When i = p_salescredit_Tbl_in.Last;
2484
2485 i := p_SalesCredit_Tbl_in.Next(i);
2486
2487 End Loop;
2488
2489 End If;
2490
2491
2492 --Rule Group Routine
2493
2494 l_rule_group_id := Check_Rule_Group_Exists(NULL,l_line_id);
2495
2496 If l_rule_group_id Is NULL Then
2497
2498 l_rgpv_tbl_in(1).chr_id := NULL;
2499 l_rgpv_tbl_in(1).cle_id := l_line_id;
2500 l_rgpv_tbl_in(1).sfwt_flag := 'N';
2501 l_rgpv_tbl_in(1).rgd_code := G_RULE_GROUP_CODE;
2502 l_rgpv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2503 l_rgpv_tbl_in(1).rgp_type := 'KRG';
2504
2505 create_rule_group
2506 (
2507 p_rgpv_tbl => l_rgpv_tbl_in,
2508 x_rgpv_tbl => l_rgpv_tbl_out,
2509 x_return_status => l_return_status,
2510 x_msg_count => x_msg_count,
2511 x_msg_data => x_msg_data
2512 );
2513
2514 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- RULE GROUP STATUS ' || l_return_status);
2515
2516
2517 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2518 l_rule_group_id := l_rgpv_tbl_out(1).id;
2519 Else
2520 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Rule Group (LINE)');
2521 Raise G_EXCEPTION_HALT_VALIDATION;
2522 End If;
2523
2524 End If;
2525
2526 --Customer Account
2527
2528
2529 open l_billto_csr (p_k_line_rec.bill_to_id);
2530 fetch l_billto_csr into l_can_object;
2531 close l_billto_csr;
2532
2533 clear_rule_table (l_rulv_tbl_in);
2534
2535 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'CAN');
2536
2537 If l_rule_id Is NULL Then
2538
2539 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2540 l_rulv_tbl_in(1).sfwt_flag := 'N';
2541 l_rulv_tbl_in(1).std_template_yn := 'N';
2542 l_rulv_tbl_in(1).warn_yn := 'N';
2543 l_rulv_tbl_in(1).rule_information_category := 'CAN';
2544 l_rulv_tbl_in(1).object1_id1 := l_can_object;
2545 l_rulv_tbl_in(1).object1_id2 := '#';
2546 l_rulv_tbl_in(1).jtot_object1_code := G_JTF_CUSTACCT;
2547 l_rulv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2548
2549 create_rules (
2550 p_rulv_tbl => l_rulv_tbl_in,
2551 x_rulv_tbl => l_rulv_tbl_out,
2552 x_return_status => l_return_status,
2553 x_msg_count => x_msg_count,
2554 x_msg_data => x_msg_data
2555 );
2556
2557 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- CAN RULE STATUS ' || l_return_status);
2558
2559
2560 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2561 l_rule_id := l_rulv_tbl_out(1).id;
2562 Else
2563 OKC_API.set_message(
2564 p_app_name => 'OKS',
2565 p_msg_name => G_REQUIRED_VALUE,
2566 p_token1 => G_COL_NAME_TOKEN,
2567 p_token1_value => 'Customer Account (LINE)'
2568 );
2569
2570
2571 Raise G_EXCEPTION_HALT_VALIDATION;
2572 End If;
2573
2574 End If;
2575
2576
2577 --CONTACT CREATION ROUTINE STARTS
2578
2579
2580 If p_contact_tbl.count > 0 Then
2581
2582 i := p_Contact_Tbl.First;
2583
2584 Loop
2585 Open l_ra_hcontacts_cur (p_Contact_tbl (i).contact_id);
2586 fetch l_ra_hcontacts_cur into l_lin_party_id, l_lin_contactid;
2587 close l_ra_hcontacts_cur;
2588
2589 if i = p_contact_tbl.first Then
2590
2591 Party_Role ( p_chrId => p_k_line_rec.k_id,
2592 p_cleId => l_line_id,
2593 p_Rle_Code => 'CUSTOMER',
2594 p_PartyId => l_lin_party_id,
2595 p_Object_Code => G_JTF_PARTY,
2596 x_roleid => l_line_party_role_id,
2597 x_msg_count => x_msg_count,
2598 x_msg_data => x_msg_data
2599 );
2600
2601 FND_FILE.PUT_LINE (FND_FILE.LOG,' LINE PARTY ROLE CREATION :- CPL ID ' || l_line_party_role_id);
2602
2603 End If;
2604
2605
2606 if p_contact_tbl(i).contact_role like '%BILLING%' Then
2607 l_role := 'CUST_BILLING';
2608 l_obj := 'OKX_CONTBILL';
2609 elsif p_contact_tbl(i).contact_role like '%ADMIN%' Then
2610 l_role := 'CUST_ADMIN';
2611 l_obj := 'OKX_CONTADMN';
2612 elsif p_contact_tbl(i).contact_role like '%SHIP%' Then
2613 l_role := 'CUST_SHIPPING';
2614 l_obj := 'OKX_CONTSHIP';
2615 end if;
2616
2617 FND_FILE.PUT_LINE (FND_FILE.LOG,'LINE CONTACT CREATION :- LIN PARTY ID ' ||l_lin_party_id);
2618 FND_FILE.PUT_LINE (FND_FILE.LOG,'LINE CONTACT CREATION :- ORG CTC ID ' ||p_Contact_tbl (i).contact_id );
2619 FND_FILE.PUT_LINE (FND_FILE.LOG,'LINE CONTACT CREATION :- RAH CTC ID ' ||l_lin_contactid);
2620
2621 FND_FILE.PUT_LINE (FND_FILE.LOG,'LINE CONTACT CREATION :- CONT ROLE ' ||p_Contact_tbl (i).contact_role);
2622 FND_FILE.PUT_LINE (FND_FILE.LOG,'LINE CONTACT CREATION :- CON OBJ CD ' ||p_Contact_tbl (i).contact_object_code);
2623
2624 l_ctcv_tbl_in(1).cpl_id := l_line_party_role_id;
2625 l_ctcv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2626 l_ctcv_tbl_in(1).cro_code := l_role;
2627 l_ctcv_tbl_in(1).object1_id1 := p_contact_tbl(i).contact_id;
2628 l_ctcv_tbl_in(1).object1_id2 := '#';
2629 l_ctcv_tbl_in(1).jtot_object1_code:= l_obj;
2630
2631 okc_contract_party_pub.create_contact
2632 (
2633 p_api_version => l_api_version,
2634 p_init_msg_list => l_init_msg_list,
2635 x_return_status => l_return_status,
2636 x_msg_count => x_msg_count,
2637 x_msg_data => x_msg_data,
2638 p_ctcv_tbl => l_ctcv_tbl_in,
2639 x_ctcv_tbl => l_ctcv_tbl_out
2640 );
2641
2642 If l_return_status = 'S' then
2643 l_line_contact_id := l_ctcv_tbl_out(1).id;
2644 Else
2645 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, p_Contact_tbl (i).contact_role
2646 || ' Contact (LINE) ' || p_Contact_tbl (i).contact_object_code);
2647 Raise G_EXCEPTION_HALT_VALIDATION;
2648 End if;
2649
2650 Exit When i = p_Contact_Tbl.Last;
2651
2652 i := p_Contact_Tbl.Next(i);
2653
2654 End Loop;
2655
2656 End If;
2657
2658
2659
2660 --CONTACT CREATION ROUTINE ENDS
2661
2662
2663 --Accounting Rule ID Routine
2664
2665 If p_k_line_rec.accounting_rule_id Is Not Null Then
2666
2667 clear_rule_table (l_rulv_tbl_in);
2668
2669 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'ARL');
2670
2671 If l_rule_id Is NULL Then
2672
2673 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2674 l_rulv_tbl_in(1).sfwt_flag := 'N';
2675 l_rulv_tbl_in(1).std_template_yn := 'N';
2676 l_rulv_tbl_in(1).warn_yn := 'N';
2677 l_rulv_tbl_in(1).rule_information_category := 'ARL';
2678 l_rulv_tbl_in(1).object1_id1 := p_k_line_rec.accounting_rule_id;
2679 l_rulv_tbl_in(1).object1_id2 := '#';
2680 l_rulv_tbl_in(1).jtot_object1_code := G_JTF_ARL;
2681 l_rulv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2682
2683 create_rules (
2684 p_rulv_tbl => l_rulv_tbl_in,
2685 x_rulv_tbl => l_rulv_tbl_out,
2686 x_return_status => l_return_status,
2687 x_msg_count => x_msg_count,
2688 x_msg_data => x_msg_data
2689 );
2690
2691 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- ARL RULE STATUS ' || l_return_status);
2692
2693
2694 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2695 l_rule_id := l_rulv_tbl_out(1).id;
2696 Else
2697 OKC_API.set_message(
2698 p_app_name => 'OKS',
2699 p_msg_name => G_REQUIRED_VALUE,
2700 p_token1 => G_COL_NAME_TOKEN,
2701 p_token1_value => 'Customer Account (LINE)'
2702 );
2703
2704
2705 Raise G_EXCEPTION_HALT_VALIDATION;
2706 End If;
2707
2708 End If;
2709
2710 End If;
2711
2712
2713
2714 --QRE rule for usage only
2715
2716 If l_lsl_id = 12 THEN
2717
2718 clear_rule_table (l_rulv_tbl_in);
2719
2720 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'QRE');
2721
2722 If l_rule_id Is NULL Then
2723
2724 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2725 l_rulv_tbl_in(1).sfwt_flag := 'N';
2726 l_rulv_tbl_in(1).std_template_yn := 'N';
2727 l_rulv_tbl_in(1).warn_yn := 'N';
2728 l_rulv_tbl_in(1).rule_information_category := 'QRE';
2729 -- l_rulv_tbl_in(1).rule_information1 := Substr(p_k_line_rec.srv_desc,1,50);
2730 l_rulv_tbl_in(1).rule_information11 := 'DAY';
2731 l_rulv_tbl_in(1).rule_information2 := p_k_line_rec.period;
2732 l_rulv_tbl_in(1).rule_information6 := p_k_line_rec.amcv_flag;
2733 l_rulv_tbl_in(1).rule_information9 := p_k_line_rec.level_yn;
2734 l_rulv_tbl_in(1).ATTRIBUTE11 := 'DAY';
2735
2736 l_rulv_tbl_in(1).rule_information10 := p_k_line_rec.l_usage_type;
2737 l_rulv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2738
2739 create_rules (
2740 p_rulv_tbl => l_rulv_tbl_in,
2741 x_rulv_tbl => l_rulv_tbl_out,
2742 x_return_status => l_return_status,
2743 x_msg_count => x_msg_count,
2744 x_msg_data => x_msg_data
2745 );
2746
2747 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- QRE RULE STATUS ' || l_return_status);
2748
2749
2750 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2751 l_rule_id := l_rulv_tbl_out(1).id;
2752 Else
2753 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'USAGE ITEM (LINE)');
2754 Raise G_EXCEPTION_HALT_VALIDATION;
2755 End If;
2756
2757 End If;
2758
2759 End if;
2760 -- Line Invoicing Rule
2761 If p_k_line_rec.invoicing_rule_id Is Not Null Then
2762
2763 clear_rule_table (l_rulv_tbl_in);
2764
2765 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'IRE');
2766
2767 If l_rule_id Is NULL Then
2768
2769 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2770 l_rulv_tbl_in(1).sfwt_flag := 'N';
2771 l_rulv_tbl_in(1).std_template_yn := 'N';
2772 l_rulv_tbl_in(1).warn_yn := 'N';
2773 l_rulv_tbl_in(1).rule_information_category := 'IRE';
2774 l_rulv_tbl_in(1).object1_id1 := p_k_line_rec.invoicing_rule_id;
2775 l_rulv_tbl_in(1).object1_id2 := '#';
2776 l_rulv_tbl_in(1).jtot_object1_code := G_JTF_IRE;
2777 l_rulv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2778
2779 create_rules (
2780 p_rulv_tbl => l_rulv_tbl_in,
2781 x_rulv_tbl => l_rulv_tbl_out,
2782 x_return_status => l_return_status,
2783 x_msg_count => x_msg_count,
2784 x_msg_data => x_msg_data
2785 );
2786
2787
2788 FND_FILE.PUT_LINE (FND_FILE.LOG, 'K Line CREATION :- IRE RULE STATUS ' || l_return_Status);
2789
2790 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2791 l_rule_id := l_rulv_tbl_out(1).id;
2792 Else
2793 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Invoice Id (LINE)');
2794 Raise G_EXCEPTION_HALT_VALIDATION;
2795 End If;
2796
2797 End If;
2798
2799 End If;
2800
2801 --Invoice Text Routine
2802
2803 clear_rule_table (l_rulv_tbl_in);
2804
2805 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'IRT');
2806
2807 If l_rule_id Is NULL Then
2808
2809 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2810 l_rulv_tbl_in(1).sfwt_flag := 'N';
2811 l_rulv_tbl_in(1).std_template_yn := 'N';
2812 l_rulv_tbl_in(1).warn_yn := 'N';
2813 l_rulv_tbl_in(1).rule_information_category := 'IRT';
2814 l_rulv_tbl_in(1).rule_information1 := Substr(p_k_line_rec.srv_desc,1,50);
2815 l_rulv_tbl_in(1).rule_information2 := 'Y';
2816
2817 l_rulv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2818
2819 create_rules (
2820 p_rulv_tbl => l_rulv_tbl_in,
2821 x_rulv_tbl => l_rulv_tbl_out,
2822 x_return_status => l_return_status,
2823 x_msg_count => x_msg_count,
2824 x_msg_data => x_msg_data
2825 );
2826
2827 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- IRT RULE STATUS ' || l_return_status);
2828
2829
2830 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2831 l_rule_id := l_rulv_tbl_out(1).id;
2832 Else
2833 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'INVOICE TEXT (LINE)');
2834 Raise G_EXCEPTION_HALT_VALIDATION;
2835 End If;
2836
2837 End If;
2838
2839
2840 --Line Renewal Type To Routine
2841
2842 clear_rule_table (l_rulv_tbl_in);
2843
2844 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'LRT');
2845
2846 If l_rule_id Is NULL Then
2847
2848 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2849 l_rulv_tbl_in(1).sfwt_flag := 'N';
2850 l_rulv_tbl_in(1).std_template_yn := 'N';
2851 l_rulv_tbl_in(1).warn_yn := 'N';
2852 l_rulv_tbl_in(1).rule_information_category := 'LRT';
2853 l_rulv_tbl_in(1).rule_information1 := Nvl(p_k_line_rec.line_renewal_type,'FUL');
2854 l_rulv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2855
2856 create_rules (
2857 p_rulv_tbl => l_rulv_tbl_in,
2858 x_rulv_tbl => l_rulv_tbl_out,
2859 x_return_status => l_return_status,
2860 x_msg_count => x_msg_count,
2861 x_msg_data => x_msg_data
2862 );
2863
2864 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- LRT RULE STATUS ' || l_return_status);
2865
2866
2867 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2868 l_rule_id := l_rulv_tbl_out(1).id;
2869 Else
2870 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'LINE RENEWAL TYPE (LINE)');
2871 Raise G_EXCEPTION_HALT_VALIDATION;
2872 End If;
2873
2874 End If;
2875
2876 --Bill To Routine
2877
2878 If p_k_line_rec.bill_to_id Is Not Null Then
2879
2880 clear_rule_table (l_rulv_tbl_in);
2881
2882 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'BTO');
2883
2884 If l_rule_id Is NULL Then
2885
2886 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2887 l_rulv_tbl_in(1).sfwt_flag := 'N';
2888 l_rulv_tbl_in(1).std_template_yn := 'N';
2889 l_rulv_tbl_in(1).warn_yn := 'N';
2890 l_rulv_tbl_in(1).rule_information_category := 'BTO';
2891 l_rulv_tbl_in(1).object1_id1 := p_k_line_rec.bill_to_id;
2892 l_rulv_tbl_in(1).object1_id2 := '#';
2893 l_rulv_tbl_in(1).jtot_object1_code := G_JTF_BILLTO;
2894 l_rulv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2895
2896 create_rules (
2897 p_rulv_tbl => l_rulv_tbl_in,
2898 x_rulv_tbl => l_rulv_tbl_out,
2899 x_return_status => l_return_status,
2900 x_msg_count => x_msg_count,
2901 x_msg_data => x_msg_data
2902 );
2903
2904 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- BTO RULE STATUS ' || l_return_status);
2905
2906
2907 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2908 l_rule_id := l_rulv_tbl_out(1).id;
2909 Else
2910 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'BillTo ID (LINE)');
2911 Raise G_EXCEPTION_HALT_VALIDATION;
2912 End If;
2913
2914 End If;
2915
2916
2917 End If;
2918
2919 --Ship To Routine
2920
2921 If p_k_line_rec.ship_to_id Is Not Null Then
2922
2923 clear_rule_table (l_rulv_tbl_in);
2924
2925 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'STO');
2926
2927 If l_rule_id Is NULL Then
2928
2929 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2930 l_rulv_tbl_in(1).sfwt_flag := 'N';
2931 l_rulv_tbl_in(1).std_template_yn := 'N';
2932 l_rulv_tbl_in(1).warn_yn := 'N';
2933 l_rulv_tbl_in(1).rule_information_category := 'STO';
2934 l_rulv_tbl_in(1).object1_id1 := p_k_line_rec.ship_to_id;
2935 l_rulv_tbl_in(1).object1_id2 := '#';
2936 l_rulv_tbl_in(1).jtot_object1_code := G_JTF_SHIPTO;
2937 l_rulv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2938
2939 create_rules (
2940 p_rulv_tbl => l_rulv_tbl_in,
2941 x_rulv_tbl => l_rulv_tbl_out,
2942 x_return_status => l_return_status,
2943 x_msg_count => x_msg_count,
2944 x_msg_data => x_msg_data
2945 );
2946
2947 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- STO RULE STATUS ' || l_return_status);
2948
2949
2950 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2951 l_rule_id := l_rulv_tbl_out(1).id;
2952 Else
2953 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ShipTo ID (LINE)');
2954 Raise G_EXCEPTION_HALT_VALIDATION;
2955 End If;
2956
2957 End If;
2958
2959
2960 End If;
2961
2962
2963 If p_k_line_rec.warranty_flag <> 'W' OR
2964 l_mtl_rec.USAGE_ITEM_FLAG = 'Y' OR
2965 l_mtl_rec.SERVICE_ITEM_FLAG = 'Y' THEN
2966
2967 --Schedule Billing Routine
2968
2969 clear_rule_table (l_rulv_tbl_in);
2970
2971 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'SBG');
2972
2973 If l_rule_id Is NULL Then
2974
2975 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
2976 l_rulv_tbl_in(1).sfwt_flag := 'N';
2977 l_rulv_tbl_in(1).std_template_yn := 'N';
2978 l_rulv_tbl_in(1).warn_yn := 'N';
2979 l_rulv_tbl_in(1).rule_information_category := 'SBG';
2980 l_rulv_tbl_in(1).dnz_chr_id := p_k_line_rec.k_id;
2981 l_rulv_tbl_in(1).rule_information1 := 1;
2982 l_rulv_tbl_in(1).rule_information2 := p_k_line_rec.Billing_freq;
2983 l_rulv_tbl_in(1).rule_information3 := p_k_line_rec.first_billupto_date;
2984 l_rulv_tbl_in(1).rule_information4 := p_k_line_rec.first_billon_date;
2985
2986 l_rulv_tbl_in(1).rule_information5 := 1;
2987 l_rulv_tbl_in(1).rule_information6 := p_k_line_rec.Billing_freq;
2988 l_rulv_tbl_in(1).rule_information7 := p_k_line_rec.offset_duration;
2989 l_rulv_tbl_in(1).rule_information8 := 'DAY';
2990 l_rulv_tbl_in(1).rule_information9 := 'N';
2991
2992
2993 create_rules (
2994 p_rulv_tbl => l_rulv_tbl_in,
2995 x_rulv_tbl => l_rulv_tbl_out,
2996 x_return_status => l_return_status,
2997 x_msg_count => x_msg_count,
2998 x_msg_data => x_msg_data
2999 );
3000
3001 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- SBG RULE STATUS ' || l_return_status);
3002
3003
3004 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
3005 l_rule_id := l_rulv_tbl_out(1).id;
3006 Else
3007 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Sched Billing Rule (LINE)');
3008 Raise G_EXCEPTION_HALT_VALIDATION;
3009 End If;
3010
3011 End If;
3012
3013 If p_k_line_rec.order_line_id Is Not Null Then
3014
3015 Create_Obj_Rel
3016 (
3017 p_K_id => Null,
3018 p_line_id => l_line_id,
3019 p_orderhdrid => Null,
3020 p_orderlineid => p_k_line_rec.order_line_id,
3021 x_return_status => l_return_status,
3022 x_msg_count => x_msg_count,
3023 x_msg_data => x_msg_data,
3024 x_crjv_tbl_out => l_crjv_tbl_out
3025 );
3026
3027 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- OBJ REL STATUS ' || l_return_status);
3028 End If;
3029
3030 If Not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
3031 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Order Line Id (LINE)');
3032 Raise G_EXCEPTION_HALT_VALIDATION;
3033 End If;
3034
3035
3036 End If;
3037
3038
3039 IF l_lsl_id <> 12 THEN --- No coverages for usage lines
3040
3041 l_cov_rec.Svc_cle_Id := l_line_id;
3042 l_cov_rec.Tmp_cle_Id := p_k_line_rec.coverage_template_id;
3043 l_cov_rec.Start_date := p_k_line_rec.srv_sdt;
3044 l_cov_rec.End_Date := p_k_line_rec.srv_edt;
3045
3046 OKS_COVERAGES_PUB.CREATE_ACTUAL_COVERAGE
3047 (
3048 p_api_version => 1.0,
3049 p_init_msg_list => OKC_API.G_FALSE,
3050 x_return_status => l_return_status,
3051 x_msg_count => x_msg_count,
3052 x_msg_data => x_msg_data,
3053 P_ac_rec_in => l_cov_rec,
3054 x_Actual_coverage_id => l_cov_id
3055 );
3056
3057 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- COV STATUS ' || l_return_status);
3058
3059 If Not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
3060 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'CoverageInstantiate (LINE)');
3061 Raise G_EXCEPTION_HALT_VALIDATION;
3062 End If;
3063
3064 End if; -- for coverages
3065
3066 l_ctr_grpid := Null;
3067
3068 Open l_ctr_csr (p_k_line_rec.srv_id);
3069 Fetch l_ctr_csr Into l_ctr_grpid;
3070 Close l_ctr_csr;
3071
3072 If l_ctr_grpid Is Not Null Then
3073
3074 cs_counters_pub.autoinstantiate_counters
3075 (
3076 P_API_VERSION => 1.0,
3077 P_INIT_MSG_LIST => 'T',
3078 P_COMMIT => 'F',
3079 X_RETURN_STATUS => l_return_status,
3080 X_MSG_COUNT => x_msg_count,
3081 X_MSG_DATA => x_msg_data,
3082 P_SOURCE_OBJECT_ID_TEMPLATE => p_k_line_rec.srv_id,
3083 P_SOURCE_OBJECT_ID_INSTANCE => l_line_id,
3084 X_CTR_GRP_ID_TEMPLATE => l_ctr_grp_id_template,
3085 X_CTR_GRP_ID_INSTANCE => l_ctr_grp_id_instance
3086 );
3087
3088 FND_FILE.PUT_LINE (FND_FILE.LOG,'K LINE CREATION :- CTR STATUS ' || l_return_status);
3089
3090 If Not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
3091 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Counter Instantiate (LINE)');
3092 Raise G_EXCEPTION_HALT_VALIDATION;
3093 End If;
3094
3095 End If;
3096
3097 x_service_line_id := l_line_id;
3098
3099 Exception
3100
3101 When G_EXCEPTION_HALT_VALIDATION Then
3102 x_return_status := l_return_status;
3103 When Others Then
3104 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3105 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
3106
3107 End Create_OSO_K_Service_Lines;
3108
3109 Procedure Create_OSO_K_Covered_Levels
3110 (
3111 p_k_covd_rec IN K_line_Covered_Level_Rec_type
3112 , p_PRICE_ATTRIBS IN Pricing_attributes_Type
3113 , x_return_status OUT NOCOPY Varchar2
3114 , x_msg_count OUT NOCOPY Number
3115 , x_msg_data OUT NOCOPY Varchar2
3116 )
3117 Is
3118
3119 l_api_version CONSTANT NUMBER := 1.0;
3120 l_init_msg_list CONSTANT VARCHAR2(1) := OKC_API.G_FALSE;
3121 l_return_status VARCHAR2(1) := 'S';
3122 l_index VARCHAR2(2000);
3123
3124 --Contract Line Table
3125 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
3126 l_clev_tbl_out okc_contract_pub.clev_tbl_type;
3127
3128 --Contract Item
3129 l_cimv_tbl_in okc_contract_item_pub.cimv_tbl_type;
3130 l_cimv_tbl_out okc_contract_item_pub.cimv_tbl_type;
3131
3132 --Pricing Attributes
3133 l_pavv_tbl_in okc_price_adjustment_pvt.pavv_tbl_type;
3134 l_pavv_tbl_out okc_price_adjustment_pvt.pavv_tbl_type;
3135
3136
3137 --Rule Related
3138
3139 l_rgpv_tbl_in okc_rule_pub.rgpv_tbl_type;
3140 l_rgpv_tbl_out okc_rule_pub.rgpv_tbl_type;
3141
3142 l_rulv_tbl_in okc_rule_pub.rulv_tbl_type;
3143 l_rulv_tbl_out okc_rule_pub.rulv_tbl_type;
3144
3145 --Return IDs
3146 l_line_id NUMBER;
3147 l_rule_group_id NUMBER;
3148 l_rule_id NUMBER;
3149 l_line_item_id NUMBER;
3150
3151 l_lsl_id NUMBER;
3152 l_jtot_object VARCHAR2(30) := NULL;
3153 l_hdrsdt DATE;
3154 l_hdredt DATE;
3155 l_hdrorgid Number;
3156 l_line_sdt DATE;
3157 l_line_edt DATE;
3158 l_hdrstatus VARCHAR2(3);
3159 l_line_status VARCHAR2(3);
3160 l_priceattrib_id NUMBER;
3161
3162 Cursor l_mtl_csr(p_inventory_id Number, p_organization_id Number) Is
3163 Select MTL.SERVICE_ITEM_FLAG
3164 ,MTL.USAGE_ITEM_FLAG
3165 From OKX_SYSTEM_ITEMS_V MTL
3166 Where MTL.id1 = p_Inventory_id
3167 And MTL.Organization_id = p_organization_id;
3168
3169 l_mtl_rec l_mtl_csr%rowtype;
3170
3171 Begin
3172
3173 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3174
3175 check_line_effectivity
3176 (
3177 p_cle_id => p_k_covd_rec.Attach_2_Line_Id,
3178 p_srv_sdt => p_k_covd_rec.Product_start_date,
3179 p_srv_edt => p_k_covd_rec.Product_end_date,
3180 x_line_sdt => l_line_sdt,
3181 x_line_edt => l_line_edt,
3182 x_status => l_line_status
3183 );
3184
3185 If l_line_status = 'E' Then
3186
3187 l_return_status := OKC_API.G_RET_STS_ERROR;
3188 Raise G_EXCEPTION_HALT_VALIDATION;
3189
3190 ElsIf l_line_status = 'Y' Then
3191
3192 check_hdr_effectivity
3193 (
3194 p_chr_id => p_k_covd_rec.k_id,
3195 p_srv_sdt => l_line_sdt,
3196 p_srv_edt => l_line_edt,
3197 x_hdr_sdt => l_hdrsdt,
3198 x_hdr_edt => l_hdredt,
3199 x_org_id => l_hdrorgid,
3200 x_status => l_hdrstatus
3201 );
3202
3203 If l_hdrstatus = 'E' Then
3204
3205 l_return_status := OKC_API.G_RET_STS_ERROR;
3206 Raise G_EXCEPTION_HALT_VALIDATION;
3207
3208 ElsIf l_hdrstatus = 'Y' Then
3209
3210 Update_Hdr_Dates
3211 (
3212 p_chr_id => p_k_covd_rec.k_id,
3213 p_new_sdt => l_hdrsdt,
3214 p_new_edt => l_hdredt,
3215 x_status => l_return_status,
3216 x_msg_count => x_msg_count,
3217 x_msg_data => x_msg_data
3218 );
3219
3220 FND_FILE.PUT_LINE (FND_FILE.LOG,'K COVD LINE CREATION :- UPDATE HDR STATUS ' || l_return_status);
3221
3222
3223 If Not l_return_Status = 'S' Then
3224 l_return_status := OKC_API.G_RET_STS_ERROR;
3225 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Header Effectivity Update (SUB LINE)');
3226 Raise G_EXCEPTION_HALT_VALIDATION;
3227 End If;
3228 End If;
3229
3230 Update_Line_Dates
3231 (
3232 p_cle_id => p_k_covd_rec.Attach_2_Line_Id,
3233 p_new_sdt => l_line_sdt,
3234 p_new_edt => l_line_edt,
3235 p_warranty_flag => p_k_covd_rec.warranty_flag,
3236 x_status => l_return_status,
3237 x_msg_count => x_msg_count,
3238 x_msg_data => x_msg_data
3239 );
3240
3241 FND_FILE.PUT_LINE (FND_FILE.LOG,'K COVD LINE CREATION :- UPDATE LINE STATUS ' || l_return_status);
3242
3243 If Not l_return_Status = 'S' Then
3244 l_return_status := OKC_API.G_RET_STS_ERROR;
3245 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'LINE Effectivity Update (SUB LINE)');
3246 Raise G_EXCEPTION_HALT_VALIDATION;
3247 End If;
3248
3249 End If;
3250
3251 /* Open l_mtl_csr(p_k_covd_rec.srv_id,l_hdrorgid);
3252
3253 Fetch l_mtl_csr into l_mtl_rec;
3254
3255 If l_mtl_csr%notfound Then
3256 FND_FILE.PUT_LINE (FND_FILE.LOG,'L_LINE_CSR FAILURE');
3257 Close l_mtl_csr ;
3258 l_return_status := OKC_API.G_RET_STS_ERROR;
3259 OKC_API.set_message(G_APP_NAME,'OKS_CUST_PROD_DTLS_NOT_FOUND','CUSTOMER_PRODUCT',p_k_covd_rec.Customer_Product_Id);
3260 Raise G_EXCEPTION_HALT_VALIDATION;
3261 End if;
3262 Close l_mtl_csr;
3263 */
3264
3265 l_lsl_id := 13;
3266 l_jtot_object := G_JTF_COUNTER;
3267
3268
3269 l_clev_tbl_in(1).chr_id := Null;
3270 l_clev_tbl_in(1).sfwt_flag := 'N';
3271 l_clev_tbl_in(1).lse_id := l_lsl_id;
3272 l_clev_tbl_in(1).line_number := p_k_covd_rec.line_number;
3273 l_clev_tbl_in(1).sts_code := Nvl(p_k_covd_rec.product_sts_code,'ACTIVE');
3274 l_clev_tbl_in(1).display_sequence := 2;
3275 l_clev_tbl_in(1).dnz_chr_id := p_k_covd_rec.k_id;
3276 --l_clev_tbl_in(1).name := Substr(p_k_covd_rec.Product_segment1,1,50);
3277 l_clev_tbl_in(1).name := Null;
3278 l_clev_tbl_in(1).item_description := p_k_covd_rec.Product_desc;
3279 l_clev_tbl_in(1).start_date := p_k_covd_rec.Product_start_date;
3280 l_clev_tbl_in(1).end_date := p_k_covd_rec.Product_end_date;
3281 l_clev_tbl_in(1).exception_yn := 'N';
3282 l_clev_tbl_in(1).price_negotiated := p_k_covd_rec.negotiated_amount;
3283 l_clev_tbl_in(1).currency_code := p_k_covd_rec.currency_code;
3284 l_clev_tbl_in(1).price_unit := p_k_covd_rec.list_price;
3285 l_clev_tbl_in(1).cle_id := p_k_covd_rec.Attach_2_Line_Id;
3286 l_clev_tbl_in(1).price_level_ind := Priced_YN(l_lsl_id);
3287 l_clev_tbl_in(1).trn_code := p_k_covd_rec.reason_code;
3288 l_clev_tbl_in(1).comments := p_k_covd_rec.reason_comments;
3289 l_clev_tbl_in(1).Attribute1 := p_k_covd_rec.attribute1;
3290 l_clev_tbl_in(1).Attribute2 := p_k_covd_rec.attribute2;
3291 l_clev_tbl_in(1).Attribute3 := p_k_covd_rec.attribute3;
3292 l_clev_tbl_in(1).Attribute4 := p_k_covd_rec.attribute4;
3293 l_clev_tbl_in(1).Attribute5 := p_k_covd_rec.attribute5;
3294 l_clev_tbl_in(1).Attribute6 := p_k_covd_rec.attribute6;
3295 l_clev_tbl_in(1).Attribute7 := p_k_covd_rec.attribute7;
3296 l_clev_tbl_in(1).Attribute8 := p_k_covd_rec.attribute8;
3297 l_clev_tbl_in(1).Attribute9 := p_k_covd_rec.attribute9;
3298 l_clev_tbl_in(1).Attribute10 := p_k_covd_rec.attribute10;
3299 l_clev_tbl_in(1).Attribute11 := p_k_covd_rec.attribute11;
3300 l_clev_tbl_in(1).Attribute12 := p_k_covd_rec.attribute12;
3301 l_clev_tbl_in(1).Attribute13 := p_k_covd_rec.attribute13;
3302 l_clev_tbl_in(1).Attribute14 := p_k_covd_rec.attribute14;
3303 l_clev_tbl_in(1).Attribute15 := p_k_covd_rec.attribute15;
3304
3305
3306 okc_contract_pub.create_contract_line
3307 (
3308 p_api_version => l_api_version,
3309 p_init_msg_list => l_init_msg_list,
3310 x_return_status => l_return_status,
3311 x_msg_count => x_msg_count,
3312 x_msg_data => x_msg_data,
3313 p_clev_tbl => l_clev_tbl_in,
3314 x_clev_tbl => l_clev_tbl_out
3315 );
3316
3317 FND_FILE.PUT_LINE (FND_FILE.LOG,'K COVD LINE CREATION :- LINE STATUS ' || l_return_status);
3318
3319
3320 If l_return_status = 'S' then
3321 l_line_id := l_clev_tbl_out(1).id;
3322 Else
3323 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'K LINE (SUB LINE)');
3324 Raise G_EXCEPTION_HALT_VALIDATION;
3325 End if;
3326
3327
3328 --Create Contract Item
3329
3330 l_cimv_tbl_in(1).cle_id := l_line_id;
3331 l_cimv_tbl_in(1).dnz_chr_id := p_k_covd_rec.k_id;
3332 l_cimv_tbl_in(1).object1_id1 := p_k_covd_rec.Customer_Product_Id;
3333 l_cimv_tbl_in(1).object1_id2 := '#';
3334 l_cimv_tbl_in(1).jtot_object1_code := l_jtot_object;
3335 l_cimv_tbl_in(1).exception_yn := 'N';
3336 l_cimv_tbl_in(1).number_of_items := p_k_covd_rec.quantity;
3337 l_cimv_tbl_in(1).uom_code := p_k_covd_rec.uom_code;
3338
3339 okc_contract_item_pub.create_contract_item
3340 (
3341 p_api_version => l_api_version,
3342 p_init_msg_list => l_init_msg_list,
3343 x_return_status => l_return_status,
3344 x_msg_count => x_msg_count,
3345 x_msg_data => x_msg_data,
3346 p_cimv_tbl => l_cimv_tbl_in,
3347 x_cimv_tbl => l_cimv_tbl_out
3348 );
3349
3350 FND_FILE.PUT_LINE (FND_FILE.LOG,'K COVD LINE CREATION :- KITEM STATUS ' || l_return_status);
3351
3352
3353 If l_return_status = 'S' then
3354 l_line_item_id := l_cimv_tbl_out(1).id;
3355 Else
3356 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'KItem (SUB LINE)');
3357 Raise G_EXCEPTION_HALT_VALIDATION;
3358 End if;
3359
3360
3361 --Rule Group Routine
3362
3363 l_rule_group_id := Check_Rule_Group_Exists(NULL,l_line_id);
3364
3365 If l_rule_group_id Is NULL Then
3366
3367 l_rgpv_tbl_in(1).chr_id := NULL;
3368 l_rgpv_tbl_in(1).cle_id := l_line_id;
3369 l_rgpv_tbl_in(1).sfwt_flag := 'N';
3370 l_rgpv_tbl_in(1).rgd_code := G_RULE_GROUP_CODE;
3371 l_rgpv_tbl_in(1).dnz_chr_id := p_k_covd_rec.k_id;
3372 l_rgpv_tbl_in(1).rgp_type := 'KRG';
3373
3374 create_rule_group
3375 (
3376 p_rgpv_tbl => l_rgpv_tbl_in,
3377 x_rgpv_tbl => l_rgpv_tbl_out,
3378 x_return_status => l_return_status,
3379 x_msg_count => x_msg_count,
3380 x_msg_data => x_msg_data
3381 );
3382
3383 FND_FILE.PUT_LINE (FND_FILE.LOG,'K COVD LINE CREATION :- RGP STATUS ' || l_return_status);
3384
3385
3386 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
3387 l_rule_group_id := l_rgpv_tbl_out(1).id;
3388 Else
3389 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Rule Group (SUB LINE)');
3390 Raise G_EXCEPTION_HALT_VALIDATION;
3391 End If;
3392
3393 End If;
3394
3395
3396 --Invoice Text Routine
3397
3398 clear_rule_table (l_rulv_tbl_in);
3399 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'IRT');
3400
3401 If l_rule_id Is NULL Then
3402
3403 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
3404 l_rulv_tbl_in(1).sfwt_flag := 'N';
3405 l_rulv_tbl_in(1).std_template_yn := 'N';
3406 l_rulv_tbl_in(1).warn_yn := 'N';
3407 l_rulv_tbl_in(1).rule_information_category := 'IRT';
3408 l_rulv_tbl_in(1).rule_information1 := 'Counter'||Substr(p_k_covd_rec.Product_desc,1,50);
3409 l_rulv_tbl_in(1).rule_information2 := 'Y';
3410
3411 l_rulv_tbl_in(1).dnz_chr_id := p_k_covd_rec.k_id;
3412
3413 create_rules (
3414 p_rulv_tbl => l_rulv_tbl_in,
3415 x_rulv_tbl => l_rulv_tbl_out,
3416 x_return_status => l_return_status,
3417 x_msg_count => x_msg_count,
3418 x_msg_data => x_msg_data
3419 );
3420
3421 FND_FILE.PUT_LINE (FND_FILE.LOG,'K COVD LINE CREATION :- IRT RULE STATUS ' || l_return_status);
3422
3423 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
3424 l_rule_id := l_rulv_tbl_out(1).id;
3425 Else
3426 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'INVOICE TEXT (SUB LINE)');
3427 Raise G_EXCEPTION_HALT_VALIDATION;
3428 End If;
3429
3430 End If;
3431
3432
3433 IF l_lsl_id = 13 THEN -- validation for usage rules
3434
3435 --- QRE rule for usages
3436 clear_rule_table (l_rulv_tbl_in);
3437 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'QRE');
3438
3439 If l_rule_id Is NULL Then
3440
3441 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
3442 l_rulv_tbl_in(1).sfwt_flag := 'N';
3443 l_rulv_tbl_in(1).std_template_yn := 'N';
3444 l_rulv_tbl_in(1).warn_yn := 'N';
3445 l_rulv_tbl_in(1).rule_information_category := 'QRE';
3446 -- l_rulv_tbl_in(1).rule_information1 := Substr(p_k_covd_rec.Product_desc,1,50);
3447 l_rulv_tbl_in(1).rule_information2 := p_k_covd_rec.period;
3448 l_rulv_tbl_in(1).rule_information4 := p_k_covd_rec.minimum_qty;
3449 l_rulv_tbl_in(1).rule_information5 := p_k_covd_rec.default_qty;
3450 l_rulv_tbl_in(1).rule_information6 := p_k_covd_rec.amcv_flag;
3451 l_rulv_tbl_in(1).rule_information7 := p_k_covd_rec.fixed_qty;
3452 -- l_rulv_tbl_in(1).rule_information8 := p_k_covd_rec.duration;
3453 l_rulv_tbl_in(1).rule_information9 := p_k_covd_rec.level_yn;
3454 l_rulv_tbl_in(1).rule_information12 := p_k_covd_rec.base_reading;
3455
3456 l_rulv_tbl_in(1).dnz_chr_id := p_k_covd_rec.k_id;
3457
3458 create_rules (
3459 p_rulv_tbl => l_rulv_tbl_in,
3460 x_rulv_tbl => l_rulv_tbl_out,
3461 x_return_status => l_return_status,
3462 x_msg_count => x_msg_count,
3463 x_msg_data => x_msg_data
3464 );
3465
3466 FND_FILE.PUT_LINE (FND_FILE.LOG,'K COVD LINE CREATION :- QRE RULE STATUS ' || l_return_status);
3467
3468 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
3469 l_rule_id := l_rulv_tbl_out(1).id;
3470 Else
3471 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'USAGE ITEM (SUB LINE)');
3472 Raise G_EXCEPTION_HALT_VALIDATION;
3473 End If;
3474
3475 End If; -- end if for QRE rule
3476
3477 ELSE
3478
3479 --Line Renewal Type To Routine
3480
3481 clear_rule_table (l_rulv_tbl_in);
3482
3483 l_rule_id := Check_Rule_Exists(l_rule_group_id, 'LRT');
3484
3485 If l_rule_id Is NULL Then
3486
3487 l_rulv_tbl_in(1).rgp_id := l_rule_group_id;
3488 l_rulv_tbl_in(1).sfwt_flag := 'N';
3489 l_rulv_tbl_in(1).std_template_yn := 'N';
3490 l_rulv_tbl_in(1).warn_yn := 'N';
3491 l_rulv_tbl_in(1).rule_information_category := 'LRT';
3492 l_rulv_tbl_in(1).rule_information1 := Nvl(p_k_covd_rec.line_renewal_type,'FUL');
3493 l_rulv_tbl_in(1).dnz_chr_id := p_k_covd_rec.k_id;
3494
3495 create_rules (
3496 p_rulv_tbl => l_rulv_tbl_in,
3497 x_rulv_tbl => l_rulv_tbl_out,
3498 x_return_status => l_return_status,
3499 x_msg_count => x_msg_count,
3500 x_msg_data => x_msg_data
3501 );
3502
3503 FND_FILE.PUT_LINE (FND_FILE.LOG,'K COVD LINE CREATION :- LRT RULE STATUS ' || l_return_status);
3504
3505 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
3506 l_rule_id := l_rulv_tbl_out(1).id;
3507 Else
3508 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'LINE RENEWAL TYPE (SUB LINE)');
3509 Raise G_EXCEPTION_HALT_VALIDATION;
3510 End If;
3511
3512 End If;
3513
3514 End if; -- validation for usage rules
3515
3516 --Create Pricing Attributes
3517
3518 If p_price_attribs.pricing_context Is Not Null Then
3519
3520 l_pavv_tbl_in(1).cle_id := l_line_id;
3521 l_pavv_tbl_in(1).flex_title := 'QP_ATTR_DEFNS_PRICING';
3522 l_pavv_tbl_in(1).pricing_context := p_price_attribs.PRICING_CONTEXT;
3523 l_pavv_tbl_in(1).pricing_attribute1 := p_price_attribs.PRICING_ATTRIBUTE1;
3524 l_pavv_tbl_in(1).pricing_attribute2 := p_price_attribs.PRICING_ATTRIBUTE2;
3525 l_pavv_tbl_in(1).pricing_attribute3 := p_price_attribs.PRICING_ATTRIBUTE3;
3526 l_pavv_tbl_in(1).pricing_attribute4 := p_price_attribs.PRICING_ATTRIBUTE4;
3527 l_pavv_tbl_in(1).pricing_attribute5 := p_price_attribs.PRICING_ATTRIBUTE5;
3528 l_pavv_tbl_in(1).pricing_attribute6 := p_price_attribs.PRICING_ATTRIBUTE6;
3529 l_pavv_tbl_in(1).pricing_attribute7 := p_price_attribs.PRICING_ATTRIBUTE7;
3530 l_pavv_tbl_in(1).pricing_attribute8 := p_price_attribs.PRICING_ATTRIBUTE8;
3531 l_pavv_tbl_in(1).pricing_attribute9 := p_price_attribs.PRICING_ATTRIBUTE9;
3532 l_pavv_tbl_in(1).pricing_attribute10 := p_price_attribs.PRICING_ATTRIBUTE10;
3533 l_pavv_tbl_in(1).pricing_attribute11 := p_price_attribs.PRICING_ATTRIBUTE11;
3534 l_pavv_tbl_in(1).pricing_attribute12 := p_price_attribs.PRICING_ATTRIBUTE12;
3535 l_pavv_tbl_in(1).pricing_attribute13 := p_price_attribs.PRICING_ATTRIBUTE13;
3536 l_pavv_tbl_in(1).pricing_attribute14 := p_price_attribs.PRICING_ATTRIBUTE14;
3537 l_pavv_tbl_in(1).pricing_attribute15 := p_price_attribs.PRICING_ATTRIBUTE15;
3538 l_pavv_tbl_in(1).pricing_attribute16 := p_price_attribs.PRICING_ATTRIBUTE16;
3539 l_pavv_tbl_in(1).pricing_attribute17 := p_price_attribs.PRICING_ATTRIBUTE17;
3540 l_pavv_tbl_in(1).pricing_attribute18 := p_price_attribs.PRICING_ATTRIBUTE18;
3541 l_pavv_tbl_in(1).pricing_attribute19 := p_price_attribs.PRICING_ATTRIBUTE19;
3542 l_pavv_tbl_in(1).pricing_attribute20 := p_price_attribs.PRICING_ATTRIBUTE20;
3543 l_pavv_tbl_in(1).pricing_attribute21 := p_price_attribs.PRICING_ATTRIBUTE21;
3544 l_pavv_tbl_in(1).pricing_attribute22 := p_price_attribs.PRICING_ATTRIBUTE22;
3545 l_pavv_tbl_in(1).pricing_attribute23 := p_price_attribs.PRICING_ATTRIBUTE23;
3546 l_pavv_tbl_in(1).pricing_attribute24 := p_price_attribs.PRICING_ATTRIBUTE24;
3547 l_pavv_tbl_in(1).pricing_attribute25 := p_price_attribs.PRICING_ATTRIBUTE25;
3548 l_pavv_tbl_in(1).pricing_attribute26 := p_price_attribs.PRICING_ATTRIBUTE26;
3549 l_pavv_tbl_in(1).pricing_attribute27 := p_price_attribs.PRICING_ATTRIBUTE27;
3550 l_pavv_tbl_in(1).pricing_attribute28 := p_price_attribs.PRICING_ATTRIBUTE28;
3551 l_pavv_tbl_in(1).pricing_attribute29 := p_price_attribs.PRICING_ATTRIBUTE29;
3552 l_pavv_tbl_in(1).pricing_attribute30 := p_price_attribs.PRICING_ATTRIBUTE30;
3553 l_pavv_tbl_in(1).pricing_attribute31 := p_price_attribs.PRICING_ATTRIBUTE31;
3554 l_pavv_tbl_in(1).pricing_attribute32 := p_price_attribs.PRICING_ATTRIBUTE32;
3555 l_pavv_tbl_in(1).pricing_attribute33 := p_price_attribs.PRICING_ATTRIBUTE33;
3556 l_pavv_tbl_in(1).pricing_attribute34 := p_price_attribs.PRICING_ATTRIBUTE34;
3557 l_pavv_tbl_in(1).pricing_attribute35 := p_price_attribs.PRICING_ATTRIBUTE35;
3558 l_pavv_tbl_in(1).pricing_attribute36 := p_price_attribs.PRICING_ATTRIBUTE36;
3559 l_pavv_tbl_in(1).pricing_attribute37 := p_price_attribs.PRICING_ATTRIBUTE37;
3560 l_pavv_tbl_in(1).pricing_attribute38 := p_price_attribs.PRICING_ATTRIBUTE38;
3561 l_pavv_tbl_in(1).pricing_attribute39 := p_price_attribs.PRICING_ATTRIBUTE39;
3562 l_pavv_tbl_in(1).pricing_attribute40 := p_price_attribs.PRICING_ATTRIBUTE40;
3563 l_pavv_tbl_in(1).pricing_attribute41 := p_price_attribs.PRICING_ATTRIBUTE41;
3564 l_pavv_tbl_in(1).pricing_attribute42 := p_price_attribs.PRICING_ATTRIBUTE42;
3565 l_pavv_tbl_in(1).pricing_attribute43 := p_price_attribs.PRICING_ATTRIBUTE43;
3566 l_pavv_tbl_in(1).pricing_attribute44 := p_price_attribs.PRICING_ATTRIBUTE44;
3567 l_pavv_tbl_in(1).pricing_attribute45 := p_price_attribs.PRICING_ATTRIBUTE45;
3568 l_pavv_tbl_in(1).pricing_attribute46 := p_price_attribs.PRICING_ATTRIBUTE46;
3569 l_pavv_tbl_in(1).pricing_attribute47 := p_price_attribs.PRICING_ATTRIBUTE47;
3570 l_pavv_tbl_in(1).pricing_attribute48 := p_price_attribs.PRICING_ATTRIBUTE48;
3571 l_pavv_tbl_in(1).pricing_attribute49 := p_price_attribs.PRICING_ATTRIBUTE49;
3572 l_pavv_tbl_in(1).pricing_attribute50 := p_price_attribs.PRICING_ATTRIBUTE50;
3573 l_pavv_tbl_in(1).pricing_attribute51 := p_price_attribs.PRICING_ATTRIBUTE51;
3574 l_pavv_tbl_in(1).pricing_attribute52 := p_price_attribs.PRICING_ATTRIBUTE52;
3575 l_pavv_tbl_in(1).pricing_attribute53 := p_price_attribs.PRICING_ATTRIBUTE53;
3576 l_pavv_tbl_in(1).pricing_attribute54 := p_price_attribs.PRICING_ATTRIBUTE54;
3577 l_pavv_tbl_in(1).pricing_attribute55 := p_price_attribs.PRICING_ATTRIBUTE55;
3578 l_pavv_tbl_in(1).pricing_attribute56 := p_price_attribs.PRICING_ATTRIBUTE56;
3579 l_pavv_tbl_in(1).pricing_attribute57 := p_price_attribs.PRICING_ATTRIBUTE57;
3580 l_pavv_tbl_in(1).pricing_attribute58 := p_price_attribs.PRICING_ATTRIBUTE58;
3581 l_pavv_tbl_in(1).pricing_attribute59 := p_price_attribs.PRICING_ATTRIBUTE59;
3582 l_pavv_tbl_in(1).pricing_attribute60 := p_price_attribs.PRICING_ATTRIBUTE60;
3583 l_pavv_tbl_in(1).pricing_attribute61 := p_price_attribs.PRICING_ATTRIBUTE61;
3584 l_pavv_tbl_in(1).pricing_attribute62 := p_price_attribs.PRICING_ATTRIBUTE62;
3585 l_pavv_tbl_in(1).pricing_attribute63 := p_price_attribs.PRICING_ATTRIBUTE63;
3586 l_pavv_tbl_in(1).pricing_attribute64 := p_price_attribs.PRICING_ATTRIBUTE64;
3587 l_pavv_tbl_in(1).pricing_attribute65 := p_price_attribs.PRICING_ATTRIBUTE65;
3588 l_pavv_tbl_in(1).pricing_attribute66 := p_price_attribs.PRICING_ATTRIBUTE66;
3589 l_pavv_tbl_in(1).pricing_attribute67 := p_price_attribs.PRICING_ATTRIBUTE67;
3590 l_pavv_tbl_in(1).pricing_attribute68 := p_price_attribs.PRICING_ATTRIBUTE68;
3591 l_pavv_tbl_in(1).pricing_attribute69 := p_price_attribs.PRICING_ATTRIBUTE69;
3592 l_pavv_tbl_in(1).pricing_attribute70 := p_price_attribs.PRICING_ATTRIBUTE70;
3593 l_pavv_tbl_in(1).pricing_attribute71 := p_price_attribs.PRICING_ATTRIBUTE71;
3594 l_pavv_tbl_in(1).pricing_attribute72 := p_price_attribs.PRICING_ATTRIBUTE72;
3595 l_pavv_tbl_in(1).pricing_attribute73 := p_price_attribs.PRICING_ATTRIBUTE73;
3596 l_pavv_tbl_in(1).pricing_attribute74 := p_price_attribs.PRICING_ATTRIBUTE74;
3597 l_pavv_tbl_in(1).pricing_attribute75 := p_price_attribs.PRICING_ATTRIBUTE75;
3598 l_pavv_tbl_in(1).pricing_attribute76 := p_price_attribs.PRICING_ATTRIBUTE76;
3599 l_pavv_tbl_in(1).pricing_attribute77 := p_price_attribs.PRICING_ATTRIBUTE77;
3600 l_pavv_tbl_in(1).pricing_attribute78 := p_price_attribs.PRICING_ATTRIBUTE78;
3601 l_pavv_tbl_in(1).pricing_attribute79 := p_price_attribs.PRICING_ATTRIBUTE79;
3602 l_pavv_tbl_in(1).pricing_attribute80 := p_price_attribs.PRICING_ATTRIBUTE80;
3603 l_pavv_tbl_in(1).pricing_attribute81 := p_price_attribs.PRICING_ATTRIBUTE81;
3604 l_pavv_tbl_in(1).pricing_attribute82 := p_price_attribs.PRICING_ATTRIBUTE82;
3605 l_pavv_tbl_in(1).pricing_attribute83 := p_price_attribs.PRICING_ATTRIBUTE83;
3606 l_pavv_tbl_in(1).pricing_attribute84 := p_price_attribs.PRICING_ATTRIBUTE84;
3607 l_pavv_tbl_in(1).pricing_attribute85 := p_price_attribs.PRICING_ATTRIBUTE85;
3608 l_pavv_tbl_in(1).pricing_attribute86 := p_price_attribs.PRICING_ATTRIBUTE86;
3609 l_pavv_tbl_in(1).pricing_attribute87 := p_price_attribs.PRICING_ATTRIBUTE87;
3610 l_pavv_tbl_in(1).pricing_attribute88 := p_price_attribs.PRICING_ATTRIBUTE88;
3611 l_pavv_tbl_in(1).pricing_attribute89 := p_price_attribs.PRICING_ATTRIBUTE89;
3612 l_pavv_tbl_in(1).pricing_attribute90 := p_price_attribs.PRICING_ATTRIBUTE90;
3613 l_pavv_tbl_in(1).pricing_attribute91 := p_price_attribs.PRICING_ATTRIBUTE91;
3614 l_pavv_tbl_in(1).pricing_attribute92 := p_price_attribs.PRICING_ATTRIBUTE92;
3615 l_pavv_tbl_in(1).pricing_attribute93 := p_price_attribs.PRICING_ATTRIBUTE93;
3616 l_pavv_tbl_in(1).pricing_attribute94 := p_price_attribs.PRICING_ATTRIBUTE94;
3617 l_pavv_tbl_in(1).pricing_attribute95 := p_price_attribs.PRICING_ATTRIBUTE95;
3618 l_pavv_tbl_in(1).pricing_attribute96 := p_price_attribs.PRICING_ATTRIBUTE96;
3619 l_pavv_tbl_in(1).pricing_attribute97 := p_price_attribs.PRICING_ATTRIBUTE97;
3620 l_pavv_tbl_in(1).pricing_attribute98 := p_price_attribs.PRICING_ATTRIBUTE98;
3621 l_pavv_tbl_in(1).pricing_attribute99 := p_price_attribs.PRICING_ATTRIBUTE99;
3622 l_pavv_tbl_in(1).pricing_attribute100 := p_price_attribs.PRICING_ATTRIBUTE100;
3623
3624 okc_price_adjustment_pvt.create_price_att_value
3625 (
3626 p_api_version => l_api_version,
3627 p_init_msg_list => l_init_msg_list,
3628 x_return_status => l_return_status,
3629 x_msg_count => x_msg_count,
3630 x_msg_data => x_msg_data,
3631 p_pavv_tbl => l_pavv_tbl_in,
3632 x_pavv_tbl => l_pavv_tbl_out
3633 );
3634
3635 FND_FILE.PUT_LINE (FND_FILE.LOG,'K COVD LINE CREATION :- PRICE ATTRIB STATUS ' || l_return_status);
3636
3637 If l_return_status = 'S' then
3638 l_priceattrib_id := l_pavv_tbl_out(1).id;
3639 Else
3640 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'PRICE ATTRIBUTES (SUB LINE)');
3641 Raise G_EXCEPTION_HALT_VALIDATION;
3642 End if;
3643
3644 End If;
3645
3646 Exception
3647 When G_EXCEPTION_HALT_VALIDATION Then
3648 x_return_status := l_return_status;
3649 Null;
3650 When Others Then
3651 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3652 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
3653
3654 End Create_OSO_K_Covered_Levels;
3655
3656
3657 Procedure Create_OSO_Contract_IBNEW
3658 (
3659 p_extwar_rec IN ExtWar_Rec_Type
3660 , p_contact_tbl_in IN OKS_EXTWARPRGM_OSO_PVT.contact_tbl
3661 , p_salescredit_tbl_in IN OKS_EXTWARPRGM_OSO_PVT.salescredit_tbl
3662 , p_price_attribs_in IN OKS_EXTWARPRGM_OSO_PVT.pricing_attributes_type
3663 , x_chrid OUT NOCOPY Number
3664 , x_return_status OUT NOCOPY Varchar2
3665 , x_msg_count OUT NOCOPY Number
3666 , x_msg_data OUT NOCOPY Varchar2
3667 )
3668 Is
3669
3670
3671 l_hdr_rec K_Header_Rec_Type;
3672 l_line_rec K_Line_Service_Rec_Type;
3673 l_covd_rec K_Line_Covered_level_Rec_Type;
3674
3675 l_return_status Varchar2(5) := OKC_API.G_RET_STS_SUCCESS;
3676 l_chrid NUMBER := NULL;
3677 l_lineid NUMBER := NULL;
3678
3679 Cursor l_party_csr Is Select Name From OKX_PARTIES_V
3680 Where ID1 = p_extwar_rec.hdr_party_id;
3681
3682 l_party_name OKX_PARTIES_V.name%TYPE;
3683
3684 --Contact
3685 l_Contact_tbl_in OKS_EXTWARPRGM_OSO_PVT.Contact_tbl;
3686
3687 --SalesCredit
3688 l_salescredit_tbl_in OKS_EXTWARPRGM_OSO_PVT.SalesCredit_Tbl;
3689
3690 Begin
3691 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3692 Okc_context.set_okc_org_context (p_extwar_rec.hdr_org_id, p_extwar_rec.organization_id);
3693
3694 Open l_party_csr;
3695 Fetch l_party_csr Into l_party_name;
3696 Close l_party_csr;
3697
3698 l_hdr_rec.contract_number := OKC_API.G_MISS_CHAR;
3699 l_hdr_rec.start_date := p_extwar_rec.hdr_sdt;
3700 l_hdr_rec.end_date := p_extwar_rec.hdr_edt;
3701 l_hdr_rec.sts_code := 'ACTIVE';
3702 l_hdr_rec.class_code := 'SVC';
3703 l_hdr_rec.authoring_org_id := p_extwar_rec.hdr_org_id;
3704 l_hdr_rec.party_id := p_extwar_rec.hdr_party_id;
3705 l_hdr_rec.third_party_role := p_extwar_rec.hdr_third_party_role;
3706 l_hdr_rec.bill_to_id := p_extwar_rec.hdr_bill_2_id;
3707 l_hdr_rec.ship_to_id := p_extwar_rec.hdr_ship_2_id;
3708 l_hdr_rec.chr_group := p_extwar_rec.hdr_chr_group;
3709 l_hdr_rec.short_description := 'CUSTOMER : ' || l_party_name || ' Warranty/Extended Warranty Contract';
3710 l_hdr_rec.price_list_id := p_extwar_rec.hdr_price_list_id;
3711 l_hdr_rec.cust_po_number := p_extwar_rec.hdr_cust_po_number;
3712 l_hdr_rec.agreement_id := p_extwar_rec.hdr_agreement_id;
3713 l_hdr_rec.currency := p_extwar_rec.hdr_currency;
3714 l_hdr_rec.accounting_rule_id := p_extwar_rec.hdr_acct_rule_id;
3715 l_hdr_rec.invoice_rule_id := p_extwar_rec.hdr_inv_rule_id;
3716 l_hdr_rec.order_hdr_id := p_extwar_rec.hdr_order_hdr_id;
3717 l_hdr_rec.payment_term_id := p_extwar_rec.hdr_payment_term_id;
3718
3719 l_hdr_rec.merge_type := p_extwar_rec.merge_type;
3720 l_hdr_rec.merge_object_id := p_extwar_rec.merge_object_id;
3721 l_hdr_rec.first_billon_date := p_extwar_rec.first_billon_date;
3722 l_hdr_rec.first_billupto_date := p_extwar_rec.first_billupto_date ;
3723 l_hdr_rec.billing_freq := p_extwar_rec.billing_freq;
3724 l_hdr_rec.offset_duration := p_extwar_rec.offset_duration;
3725
3726
3727
3728 FND_FILE.PUT_LINE (FND_FILE.LOG,'OSO IBNEW :- CREATE HDR STATUS ' || l_return_status );
3729
3730
3731 OKS_EXTWARPRGM_OSO_PVT.create_k_hdr
3732 ( p_k_header_rec => l_hdr_rec
3733 , p_Contact_Tbl => p_Contact_tbl_in
3734 , x_chr_id => l_chrid
3735 , x_return_status => l_return_status
3736 , x_msg_count => x_msg_count
3737 , x_msg_data => x_msg_data
3738 );
3739
3740 If Not l_return_status = 'S' then
3741 Raise G_EXCEPTION_HALT_VALIDATION;
3742 End if;
3743
3744 x_chrid := l_chrid;
3745 FND_FILE.PUT_LINE (FND_FILE.LOG,'OSO IBNEW :- CREATE HDR STATUS l_chrid' || l_chrid);
3746
3747 l_line_rec.k_id := l_chrid;
3748 l_line_rec.k_line_number:= OKC_API.G_MISS_CHAR;
3749 l_line_rec.org_id := p_extwar_rec.hdr_org_id;
3750 l_line_rec.accounting_rule_id := p_extwar_rec.hdr_acct_rule_id;
3751 l_line_rec.srv_id := p_extwar_rec.srv_id;
3752 l_line_rec.srv_segment1 := p_extwar_rec.srv_name;
3753 l_line_rec.srv_desc := p_extwar_rec.srv_desc;
3754 l_line_rec.srv_sdt := p_extwar_rec.srv_sdt;
3755 l_line_rec.srv_edt := p_extwar_rec.srv_edt;
3756 l_line_rec.bill_to_id := p_extwar_rec.srv_bill_2_id;
3757 l_line_rec.ship_to_id := p_extwar_rec.srv_ship_2_id;
3758 l_line_rec.order_line_id:= p_extwar_rec.srv_order_line_id;
3759 l_line_rec.warranty_flag:= p_extwar_rec.warranty_flag;
3760 l_line_rec.currency := p_extwar_rec.srv_currency;
3761 l_line_rec.coverage_template_id := p_extwar_rec.srv_Cov_template_id;
3762 l_line_rec.cust_account := p_extwar_rec.cust_account;
3763 l_line_rec.l_usage_type := p_extwar_rec.l_usage_type;
3764 l_line_rec.first_billon_date := p_extwar_rec.first_billon_date;
3765 l_line_rec.first_billupto_date := p_extwar_rec.first_billupto_date;
3766 l_line_rec.billing_freq := p_extwar_rec.billing_freq;
3767 l_line_rec.offset_duration := p_extwar_rec.offset_duration;
3768 l_line_rec.organization_id := p_extwar_rec.organization_id;
3769 l_line_rec.period := p_extwar_rec.period;
3770 OKS_EXTWARPRGM_OSO_PVT.create_OSO_k_Service_lines
3771 ( p_k_line_rec => l_line_rec
3772 , p_Contact_Tbl => p_Contact_tbl_in
3773 , p_salescredit_tbl_in => l_SalesCredit_Tbl_in
3774 , x_service_line_id => l_lineid
3775 , x_return_status => l_return_status
3776 , x_msg_count => x_msg_count
3777 , x_msg_data => x_msg_data
3778 );
3779
3780 FND_FILE.PUT_LINE (FND_FILE.LOG,'IBNEW :- CREATE LINE STATUS ' || l_return_status );
3781 If Not l_return_status = 'S' then
3782 Raise G_EXCEPTION_HALT_VALIDATION;
3783 End if;
3784
3785 l_covd_rec.k_id := l_chrid;
3786 l_covd_rec.Attach_2_Line_id := l_lineid;
3787 l_covd_rec.line_number := OKC_API.G_MISS_CHAR;
3788 l_covd_rec.Customer_Product_Id := p_extwar_rec.lvl_cp_id;
3789 l_covd_rec.Product_Segment1 := p_extwar_rec.lvl_inventory_name;
3790 l_covd_rec.Product_Desc := p_extwar_rec.lvl_inventory_desc;
3791 l_covd_rec.Product_Start_Date := p_extwar_rec.srv_sdt;
3792 l_covd_rec.Product_End_Date := p_extwar_rec.srv_edt;
3793 l_covd_rec.Quantity := p_extwar_rec.lvl_quantity;
3794 l_covd_rec.list_price := p_extwar_rec.srv_unit_price;
3795 l_covd_rec.uom_code := p_extwar_rec.lvl_uom_code;
3796 l_covd_rec.negotiated_amount := p_extwar_rec.srv_amount;
3797 l_covd_rec.warranty_flag := p_extwar_rec.warranty_flag;
3798 l_covd_rec.product_sts_code := p_extwar_rec.lvl_sts_code;
3799 l_covd_rec.line_renewal_type := p_extwar_rec.lvl_line_renewal_type;
3800 l_covd_rec.currency_code := p_extwar_rec.srv_currency;
3801 l_covd_rec.period := p_extwar_rec.period;
3802 l_covd_rec.minimum_qty := p_extwar_rec.minimum_qty;
3803 l_covd_rec.default_qty := p_extwar_rec.default_qty;
3804 l_covd_rec.amcv_flag := p_extwar_rec.amcv_flag;
3805 l_covd_rec.fixed_qty := p_extwar_rec.fixed_qty;
3806 l_covd_rec.duration := p_extwar_rec.duration;
3807 l_covd_rec.level_yn := p_extwar_rec.level_yn;
3808 l_covd_rec.base_reading := p_extwar_rec.base_reading;
3809 l_covd_rec.srv_id := p_extwar_rec.srv_id;
3810 l_covd_rec.org_id := p_extwar_rec.hdr_org_id;
3811
3812 OKS_EXTWARPRGM_OSO_PVT.Create_OSO_K_Covered_Levels
3813 (
3814 p_k_covd_rec => l_covd_rec
3815 , p_PRICE_ATTRIBS => p_price_attribs_in
3816 , x_return_status => l_return_status
3817 , x_msg_count => x_msg_count
3818 , x_msg_data => x_msg_data
3819 );
3820
3821 FND_FILE.PUT_LINE (FND_FILE.LOG,'IBNEW :- CREATE COV STATUS ' || l_return_status );
3822
3823
3824 If Not l_return_status = 'S' then
3825 Raise G_EXCEPTION_HALT_VALIDATION;
3826 End if;
3827 Update Okc_k_headers_b
3828 Set Estimated_amount = (Select sum(price_negotiated) from Okc_k_lines_v
3829 Where dnz_chr_id = l_chrid)
3830 Where id = l_chrid ;
3831 Launch_Workflow ( 'INSTALL BASE ACTIVITY : NEW ' || fnd_global.local_chr(10) ||
3832 'Contract Number : ' || get_contract_number (l_chrid) || fnd_global.local_chr(10) ||
3833 'Service Added : ' || p_extwar_rec.srv_name || fnd_global.local_chr(10) ||
3834 'Customer Product : ' || p_extwar_rec.lvl_cp_id
3835 );
3836
3837 Exception
3838 When G_EXCEPTION_HALT_VALIDATION Then
3839 x_return_status := l_return_status;
3840 Null;
3841 When Others Then
3842 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3843 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
3844
3845 End Create_OSO_Contract_IBNEW;
3846
3847
3848
3849
3850 END OKS_EXTWARPRGM_OSO_PVT;