[Home] [Help]
PACKAGE BODY: APPS.OKS_IBINT_PUB
Source
1 Package Body OKS_IBINT_PUB As
2 /* $Header: OKSPIBIB.pls 120.60.12020000.2 2012/07/23 07:45:49 spingali ship $ */
3
4
5 -- Constants used for Message Logging
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_LEVEL_CURRENT CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
13 G_MODULE_CURRENT CONSTANT VARCHAR2(255) := 'oks.plsql.oks_int_ibint_pub';
14
15
16 -- created for bom issues
17 FUNCTION CHECK_PARENT_CP_K(
18 p_order_line_id number,
19 p_srvord_line_id number)
20 Return BOOLEAN
21 IS
22 CURSOR l_parent_cp_csr IS
23 Select csi.instance_id
24 from csi_item_instances csi , oe_order_lines_all ol
25 where ol.line_id = csi.last_oe_order_line_id
26 and ol.inventory_item_id = csi.inventory_item_id
27 and ol.line_id = p_order_line_id ;
28
29 CURSOR l_k_exists (p_item_id NUMBER)IS
30 Select 'x'
31 From okc_k_rel_objs rel
32 ,okc_k_lines_b line
33 ,okc_k_items item
34 Where rel.Object1_Id1 = to_char(p_srvord_line_id)
35 And rel.jtot_object1_code = 'OKX_ORDERLINE'
36 And item.cle_id = line.id
37 And item.object1_id1 = to_char(p_item_id)
38 And item.jtot_object1_code = 'OKX_CUSTPROD'
39 And line.id = rel.cle_id
40 And line.lse_id in (9,25)
41 And line.dnz_chr_id = item.dnz_chr_id;
42
43
44 l_parent_cp_id NUMBER;
45 v_flag BOOLEAN := TRUE;
46 v_temp VARCHAR2( 5 );
47 Begin
48
49 OPEN l_parent_cp_csr;
50 FETCH l_parent_cp_csr into l_parent_cp_id;
51 CLOSE l_parent_cp_csr;
52
53 OPEN l_k_exists (l_parent_cp_id);
54 FETCH l_k_exists into v_temp;
55 IF ( l_k_exists%FOUND ) THEN
56 v_flag := TRUE;
57 ELSE
58 v_flag := FALSE;
59 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
60 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.CHECK_PARENT_AP_K',
61 'Contract not created for parent item yet' );
62 END IF;
63
64 END IF;
65
66 CLOSE l_k_exists;
67 RETURN (v_flag);
68 END;
69
70 Function get_party_id (p_custid Number) Return Number
71 Is
72 Cursor l_party_csr Is
73 Select Party_Id from OKX_CUSTOMER_ACCOUNTS_V
74 Where Id1 = p_custid;
75 l_party_id NUMBER;
76
77 Begin
78
79 Open l_party_csr;
80 Fetch l_party_csr Into l_party_id;
81 If l_party_csr%notfound Then
82 Close l_Party_Csr ;
83 Return(NULL);
84 OKC_API.set_message(G_APP_NAME,'OKS_PARTY_ID_NOT_FOUND','CUSTOMER_ID',P_custid); --message changed Vigandhi
85 Raise G_EXCEPTION_HALT_VALIDATION;
86 End if;
87
88 Close l_party_csr;
89
90 Return (l_party_id);
91
92
93 End;
94 ----------------------------------------------------------------------------
95 -- Update Contract Details
96 ----------------------------------------------------------------------------
97
98 Procedure Update_Contract_Details
99 ( p_hdr_id Number,
100 p_order_line_id number,
101 x_return_status Out NOCOPY Varchar2
102 )
103 Is
104
105 Cursor l_link_csr1 Is
106 Select NVL(link_ord_line_id1, order_line_id1)
107 From Oks_k_order_details
108 Where order_line_id1 = to_char(p_ordeR_line_id);
109
110 Cursor l_link_csr_a(l_link_ord_id varchar2) Is
111 Select id ,ordeR_line_id1,object_version_number
112 From Oks_k_order_details
113 Where link_ord_line_id1 = l_link_ord_id
114 And Chr_id Is NULL;
115
116 Cursor l_link_csr_b(l_link_ord_id varchar2) Is
117 Select id ,ordeR_line_id1,object_version_number
118 From Oks_k_order_details
119 Where order_line_id1 = l_link_ord_id
120 And Chr_id Is NULL;
121
122
123 l_link_rec1 l_link_csr_a%rowtype;
124 l_link_rec2 l_link_csr_b%rowtype;
125 l_link_to_order_id Varchar2(40);
126 l_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
127 l_msg_count Number;
128 l_msg_data Varchar2(2000);
129 link_flag Number := 0;
130 l_codv_tbl_in OKS_COD_PVT.codv_tbl_type;
131 l_codv_tbl_out OKS_COD_PVT.codv_tbl_type;
132
133 Begin
134
135 x_return_status := l_return_status;
136
137 Open l_link_csr1;
138 Fetch l_link_csr1 Into l_link_to_order_id;
139
140 If l_link_csr1%notfound Then
141 Close l_link_csr1 ;
142 OKC_API.set_message(G_APP_NAME,'OKS_ORDER_DETAILS','ORDER_DETAILS',p_order_line_id);
143 Raise G_EXCEPTION_HALT_VALIDATION;
144 End if;
145
146 Close l_link_csr1;
147
148 link_flag := 0;
149
150 Open l_link_csr_a(l_link_to_order_id);
151 Loop
152 Fetch l_link_csr_a into l_link_rec1;
153 Exit WHEN l_link_csr_a%NOTFOUND;
154
155 l_codv_tbl_in(1).id := l_link_rec1.id;
156 l_codv_tbl_in(1).chr_id := p_hdr_id;
157 l_codv_tbl_in(1).object_version_number := l_link_rec1.object_version_number; ----bugfix 2458974
158
159 OKS_COD_PVT.update_row
160 (
161 p_api_version => 1.0 ,
162 p_init_msg_list => 'T',
163 x_return_status => l_return_status,
164 x_msg_count => l_msg_count,
165 x_msg_data => l_msg_data,
166 p_codv_tbl => l_codv_tbl_in,
167 x_codv_tbl => l_codv_tbl_out
168 );
169 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
170 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.UPDATE_CONTRACT_DETAILS',
171 'oks_cod_pvt.update_row(Return status = '|| l_return_status || ')');
172 END IF;
173
174 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
175 x_return_status := l_return_status;
176 Raise G_EXCEPTION_HALT_VALIDATION;
177 End if;
178
179 link_flag := 1;
180
181 End Loop;
182 Close l_link_csr_a;
183
184 --If link_flag = 0 Then
185 Open l_link_csr_b(l_link_to_order_id);
186 Loop
187
188 Fetch l_link_csr_b into l_link_rec2;
189 Exit WHEN l_link_csr_b%NOTFOUND;
190
191 l_codv_tbl_in(1).id := l_link_rec2.id;
192 l_codv_tbl_in(1).chr_id := p_hdr_id;
193 l_codv_tbl_in(1).object_version_number := l_link_rec2.object_version_number; --bugfix 2458974
194
195 OKS_COD_PVT.update_row
196 (
197 p_api_version => 1.0 ,
198 p_init_msg_list => 'T',
199 x_return_status => l_return_status,
200 x_msg_count => l_msg_count,
201 x_msg_data => l_msg_data,
202 p_codv_tbl => l_codv_tbl_in,
203 x_codv_tbl => l_codv_tbl_out
204 );
205
206 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
207 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.UPDATE_CONTRACT_DETAILS',
208 'oks_cod_pvt.update_row(Return status = '|| l_return_status || ')');
209 END IF;
210
211 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
212 x_return_status := l_return_status;
213 Raise G_EXCEPTION_HALT_VALIDATION;
214 End If;
215
216 End Loop;
217 Close l_link_csr_b;
218
219 ---End if;
220
221
222 Exception
223 When G_EXCEPTION_HALT_VALIDATION Then
224 x_return_status := l_return_status;
225
226 When Others Then
227 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
228 OKC_API.set_message(G_APP_NAME,G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE,G_SQLERRM_TOKEN, SQLERRM);
229 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
230 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.UPDATE_CONTRACT_DETAILS.UNEXPECTED',
231 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
232 END IF;
233
234 End;
235
236 -------------------------------------------------------------------------------------------------
237 -- Get Order header Id
238 -------------------------------------------------------------------------------------------------
239
240 Function get_order_header_id (p_order_line_id Number) Return Number
241 Is
242 Cursor l_header_csr Is
243 Select Header_id from OKX_ORDER_LINES_V
244 Where Id1 = p_order_line_id;
245
246 l_header_id NUMBER;
247
248 Begin
249
250 Open l_header_csr;
251 Fetch l_header_csr Into l_header_id;
252
253 If l_header_csr%notfound Then
254 Close l_header_csr;
255 Return(NULL);
256 OKC_API.set_message(G_APP_NAME,'OKS_ORD_LINE-DTLS_NOT_FOUND','ORDER_LINE',p_order_line_id); --message changed -Vigandhi
257 Raise G_EXCEPTION_HALT_VALIDATION;
258 End if;
259
260 Close l_header_csr;
261
262 Return (l_header_id);
263
264 End;
265
266 -----------------------------------------------------------------------------------------------
267 --Get Contract Details
268 -----------------------------------------------------------------------------------------------
269
270 Procedure get_contract_details
271 (
272 p_order_line_id IN Number,
273 l_renewal_rec OUT NOCOPY renewal_rec_type,
274 x_return_status OUT NOCOPY Varchar2
275 )
276 Is
277 Cursor l_chr_csr Is
278 Select Chr_id
279 ,Renewal_type
280 ,po_required_yn
281 ,Renewal_pricing_type
282 ,Markup_percent
283 ,Price_list_id1
284 ,link_chr_id
285 ,contact_id
286 ,email_id
287 ,phone_id
288 ,fax_id
289 ,site_id
290 ,cod_type
291 ,billing_profile_id --new parameter added -vigandhi (May29-02)
292 ,line_renewal_type
293 From Oks_K_Order_Details_V
294 Where ORDER_LINE_ID1 = to_char(p_order_line_id);
295
296 l_chdr_id NUMBER;
297
298 Begin
299 x_return_status := OKC_API.G_RET_STS_SUCCESS;
300 Open l_chr_csr;
301 Fetch l_chr_csr Into l_renewal_rec;
302 Close l_chr_csr;
303
304 Exception
305 When Others Then
306 x_return_status := OKC_API.G_RET_STS_ERROR;
307 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
308 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.GET_CONTRACT_DETAILS.UNEXPECTED',
309 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
310 END IF;
311 OKC_API.set_message(G_APP_NAME,G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE,G_SQLERRM_TOKEN, SQLERRM);
312 End;
313
314 PROCEDURE IB_New(
315 P_instance_rec Instance_rec_type,
316 x_inst_dtls_tbl OUT NOCOPY OKS_IHD_PVT.ihdv_tbl_type,
317 x_return_status OUT NoCopy Varchar2,
318 x_msg_count OUT NOCOPY Number,
319 x_msg_data OUT NOCOPY VARCHAR2
320 )
321 Is
322 l_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
323 l_msg_count NUMBER;
324 l_parameters Varchar2(2000);
325 l_Header_rec OKS_EXTWAR_UTIL_PVT.Header_Rec_Type;
326 l_line_rec OKS_EXTWAR_UTIL_PVT.Line_Rec_Type;
327 War_tbl OKS_EXTWAR_UTIL_PVT.War_tbl;
328
329 Cursor l_line_Csr(p_inventory_id Number) Is
330 Select MTL.Name
331 ,MTL.Description
332 ,MTL.Primary_UOM_Code
333 ,MTL.Service_starting_delay
334 From OKX_SYSTEM_ITEMS_V MTL
335 Where MTL.id1 = p_Inventory_id
336 And MTL.Organization_id = okc_context.get_okc_organization_id;
337
338 Cursor l_org_csr (p_line_id Number) Is
339 select org_id
340 ,sold_from_org_id
341 ,ship_from_org_id
342 from oe_order_lines_all
343 where Line_id = p_line_id;
344
345 Cursor l_organization_csr(p_org_id Number) Is
346 Select master_organization_id
347 From oe_system_parameters_all
348 where org_id = p_org_id;
349
350 Cursor l_refnum_csr(p_cp_id NUMBER) IS
351 select instance_number
352 from csi_item_instances
353 where instance_id = p_cp_id;
354
355 Cursor l_subscr_csr(p_instance_id Number) Is
356 Select instance_id
357 From Oks_subscr_header_b
358 Where instance_id = p_instance_id
359 And rownum < 2;
360
361
362 l_organization_id Number;
363 l_ref_num Varchar2(30);
364 l_temp number;
365
366
367 l_line_dtl_rec l_line_csr%rowtype;
368 l_org_rec l_org_csr%rowtype;
369
370
371
372 l_instparent_id Number;
373 l_instance_id Number;
374
375 Cursor Cust_csr(p_id Number) is
376
377 Select csi.last_oe_agreement_id Product_agreement_id
378 ,oh.transactional_curr_code Original_order_currency_code
379 From CSI_ITEM_INSTANCES Csi,
380 OE_ORDER_HEADERS_ALL OH,
381 OE_ORDER_LINES_ALL OL
382 Where csi.instance_id = p_id
383 And csi.last_oe_order_line_id = ol.line_id
384 And oh.header_id = ol.header_id;
385
386 Cursor l_contact_csr(p_line_id Number) Is
387 Select OC.Object1_id1
388 ,OC.cro_code
389 From OKS_K_ORDER_CONTACTS_V OC
390 ,OKS_K_ORDER_DETAILS_V OD
391 Where OC.cod_id = OD.id
392 -- And OD.order_line_id1 = p_line_id;
393 And OD.order_line_id1 = to_char(p_line_id); -- Bug Fix #4896051
394
395 Cursor l_Qty_csr(p_id Number) Is
396 Select fulfilled_quantity
397 From OKX_ORDER_LINES_V
398 Where Id1 = p_id;
399
400 -- warranty consolidation
401 -- cursor to get chr_id of existing warranty contract for an order. vigandhi
402
403 Cursor get_chr_id_csr(p_object_id NUmber) IS
404 Select Chr_id
405 From OKC_K_REL_OBJS RO, OkC_K_HEADERS_V OH
406 Where RO.OBJECT1_ID1 = to_char(P_Object_id)
407 And RO.jtot_object1_code = 'OKX_ORDERHEAD'
408 And RO.rty_code = 'CONTRACTWARRANTYORDER'
409 And RO.chr_id = OH.id
410 And OH.sts_code not in ('TERMINATED','CANCELLED');
411
412
413 Cursor l_hdr_scs_csr(p_chr_id Number) Is
414 Select scs_code
415 From OKC_K_HEADERS_V
416 Where id = p_chr_id;
417
418 Cursor l_rel_csr(p_ordlineid Number, p_serv_ordline_id Number,p_item_id Number) Is
419 Select rel.cle_id
420 From okc_k_rel_objs rel
421 ,okc_k_lines_b line
422 ,okc_k_items item
423 Where rel.Object1_Id1 in (to_char(p_ordlineid),to_char(p_serv_ordline_id))
424 And rel.jtot_object1_code = 'OKX_ORDERLINE'
425 And item.cle_id = line.id
426 And item.object1_id1 = to_char(p_item_id)
427 And item.jtot_object1_code = 'OKX_CUSTPROD'
428 And line.id = rel.cle_id
429 And line.lse_id in (9, 25)
430 And line.dnz_chr_id = item.dnz_chr_id;
431
432
433
434 Cursor l_cle_csr(p_ordlineid Number, p_serv_ordline_id Number) Is
435 Select rel.cle_id
436 From okc_k_rel_objs_v rel
437 ,okc_k_lines_b line
438 Where rel.Object1_Id1 in (to_char(p_ordlineid),to_char(p_serv_ordline_id))
439 And rel.jtot_object1_code = 'OKX_ORDERLINE'
440 And line.id = rel.cle_id
441 And line.lse_id in (1,19);
442
443 Cursor l_object_csr(p_cle_id Number, p_cp_id Number) Is
444 Select line.id
445 From Okc_k_lines_b line
446 ,okc_k_items item
447 WHere item.cle_id = line.id
448 ANd line.cle_id = p_cle_id
449 And line.lse_id in (9, 25)
450 And item.object1_id1 = to_char(p_cp_id)
451 ANd item.jtot_object1_code = 'OKX_CUSTPROD'
452 And item.dnz_chr_id = line.dnz_chr_id;
453
454 -- Added: Vigandhi : 18-nov-2004
455 -- Cursor added to derive the sold to org id in order to get
456 -- the GCD rules for sold to customer
457 Cursor l_Ord_Hdr_csr(p_line_id NUMBER) Is
458 Select OH.SOLD_TO_ORG_ID
459 From OE_Order_Headers_ALL OH,
460 OE_ORDER_LINES_ALL OL
461 Where OH.Header_id = OL.Header_id
462 AND OL.line_id = p_line_id;
463
464 l_item_id Number;
465 l_rel_id Number;
466 l_line_id Number;
467
468
469
470 l_contact_rec l_contact_csr%rowtype;
471 l_cust_rec Cust_csr%rowtype;
472
473
474 l_msg_data VARCHAR2(2000);
475 l_service_status Varchar2(20);
476 l_ptr Number;
477 l_ctr Number :=1;
478 l_line_dtl_rec l_line_csr%rowtype;
479 l_war_date DATE;
480 l_itm_rec l_line_csr%ROWTYPE;
481 l_date DATE;
482 p_order_header_id NUMBER;
483 p_chdr_id NUMBER;
484 p_party_id NUMBER;
485 l_ptr1 NUMBER;
486 l_rnrl_rec_out oks_renew_util_pvt.rnrl_rec_type;
487 l_chr_id NUMBER := Null;
488 l_fulfilled_qty NUMBER;
489 l_hdr_scs_code Varchar2(30);
490 i number;
491 l_index number;
492 l_order_error VARCHAR2(2000);
493 l_curr_code VARCHAR2(15);
494 l_process BOOLEAN := TRUE;
495 l_Ord_hdr_rec l_Ord_Hdr_csr%rowtype ;
496 l_extwar_rec OKS_EXTWARPRGM_PVT .extwar_rec_Type;
497 p_contact_tbl OKS_EXTWARPRGM_PVT .contact_tbl;
498 l_SalesCredit_tbl OKS_EXTWARPRGM_PVT.SalesCredit_tbl;
499 l_SalesCredit_tbl_hdr OKS_EXTWARPRGM_PVT.SalesCredit_tbl; --mmadhavi bug 4174921
500 l_pricing_attributes_in OKS_EXTWARPRGM_PVT.Pricing_attributes_Type;
501 l_inst_dtls_tbl OKS_IHD_PVT.ihdv_tbl_type ;
502 l_insthist_rec OKS_INS_PVT.insv_rec_type;
503
504
505
506 Begin
507
508 If p_instance_rec.order_line_id Is NULL Or (p_instance_rec.order_line_id = Okc_api.g_miss_num) Then
509
510 -- Added: Vigandhi : 18-nov-2004
511 -- Get the renewal rules defined in GCD
512 p_party_id := Get_Party_id(p_instance_rec.old_CustomeR_acct_id);
513
514 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
515 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW',
516 'party id(Legacy) = ' ||p_party_id );
517 END IF;
518
519 OKS_RENEW_UTIL_PUB.GET_RENEW_RULES
520 (
521 p_api_version => 1.0,
522 p_init_msg_list => 'T',
523 x_return_status => l_return_status,
524 x_msg_count => l_msg_count,
525 x_msg_data => l_msg_data,
526 P_Chr_Id => null,
527 P_PARTY_ID => p_party_id ,
528 P_ORG_ID => p_instance_rec.org_id,
529 P_Date => SYSDATE ,
530 P_RNRL_Rec => null,
531 X_RNRL_Rec => l_rnrl_rec_out
532 );
533
534 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
535 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
536 'oks_renew_util_pub.get_renew_rules(Return status = ' ||l_return_status );
537 END IF;
538
539 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
540 x_return_status := l_return_status;
541 Raise G_EXCEPTION_HALT_VALIDATION;
542 End If;
543
544 -- Setting the org context
545 Okc_context.set_okc_org_context (p_instance_rec.org_id, p_instance_rec.organization_id);
546
547 Else
548
549 -- Vigandhi : 08-Dec-2005
550 --- Fix for Bug 4295015
551 IF nvl(fnd_profile.value('OKS_WARRANTY_CONSOLIDATION'), 'Y') = 'Y'
552 THEN
553 Update Okc_k_headers_all_b
554 Set last_updated_by = last_updated_by
555 Where id = -1;
556
557 If SQL%ROWCOUNT < 1 Then
558
559 l_return_status := OKC_API.G_RET_STS_ERROR;
560 OKC_API.set_message(G_APP_NAME,'OKS_INVD_COV_TMPLT_HDR');
561 Raise G_EXCEPTION_HALT_VALIDATION;
562
563 END IF;
564 End If;
565
566 -- Added: Vigandhi : 18-nov-2004
567 -- Get the renewal rules defined in GCD
568
569 Open l_Ord_Hdr_csr(p_instance_rec.Order_line_id);
570 Fetch l_Ord_Hdr_csr into l_Ord_hdr_rec;
571 If l_Ord_Hdr_csr%notfound then
572 Close l_Ord_Hdr_csr;
573 l_return_status := OKC_API.G_RET_STS_ERROR;
574 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
575 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
576 'l_ord_hdr_csr failed. No details found for line id ' ||p_instance_rec.Order_line_id );
577 END IF;
578 OKC_API.set_message(G_APP_NAME, 'OKS_ORD_HDR_DTLS_NOT_FOUND','ORDER_HEADER_ID',p_instance_rec.Order_line_id);
579 Raise G_EXCEPTION_HALT_VALIDATION;
580 End if;
581 Close l_Ord_Hdr_csr;
582
583 p_party_id := Get_Party_id(l_Ord_hdr_rec.Sold_to_Org_id);
584
585 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
586 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW',
587 'party id(OM Generated) = ' ||p_party_id );
588 END IF;
589
590 OKS_RENEW_UTIL_PUB.GET_RENEW_RULES
591 (
592 p_api_version => 1.0,
593 p_init_msg_list => 'T',
594 x_return_status => l_return_status,
595 x_msg_count => l_msg_count,
596 x_msg_data => l_msg_data,
597 P_Chr_Id => null,
598 P_PARTY_ID => p_party_id ,
599 P_ORG_ID => p_instance_rec.org_id,
600 P_Date => SYSDATE ,
601 P_RNRL_Rec => null,
602 X_RNRL_Rec => l_rnrl_rec_out
603 );
604
605 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
606 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
607 'oks_renew_util_pub.get_renew_rules(Return status = ' ||l_return_status );
608 END IF;
609
610 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
611 x_return_status := l_return_status;
612 Raise G_EXCEPTION_HALT_VALIDATION;
613 End If;
614
615 -- Setting the org context
616 Open l_org_csr( p_instance_rec.order_line_id);
617 fetch l_org_csr into l_org_rec ;
618 Close l_org_Csr ;
619
620 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
621 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW.l_org_csr',
622 'Org id ='||l_org_rec.org_id ||
623 ',Sold from org id = '|| l_org_rec.sold_from_org_id ||
624 ',Ship from org id = '||l_org_rec.ship_from_org_id );
625 END IF;
626 If Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'IB' OR Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') Is NULL Then
627 Okc_context.set_okc_org_context (p_instance_rec.org_id, p_instance_rec.organization_id);
628
629 Elsif Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'MO' Then
630 Okc_context.set_okc_org_context (l_org_rec.org_id, NULL );
631
632 Elsif Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'SO' Then
633
634 l_organization_id := Null;
635 If l_org_rec.sold_from_org_id Is Not Null Then
636 Open l_organization_csr(l_org_rec.sold_from_org_id);
637 Fetch l_organization_csr into l_organization_id;
638 Close l_organization_csr;
639 Else
640 l_organization_id := Null;
641 End If;
642
643 Okc_context.set_okc_org_context (l_org_rec.org_id, l_organization_id);
644
645 Elsif Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'SH' Then
646 Okc_context.set_okc_org_context (l_org_rec.org_id, l_org_rec.ship_from_org_id);
647
648 End If;
649
650
651 End If;
652
653 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
654 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW.after org setup',
655 'Org id = '||okc_context.get_okc_org_id ||
656 ',Organization id = '|| okc_context.get_okc_organization_id );
657 END IF;
658
659 OKS_EXTWAR_UTIL_PVT.Get_Warranty_Info
660 (
661 p_prod_item_id => p_instance_rec.old_inventory_item_id,
662 P_customer_product_id => p_instance_rec.old_Customer_product_id,
663 x_return_status => l_return_status,
664 p_Ship_date => p_instance_rec.shipped_date,
665 p_installation_date => p_instance_rec.installation_date,
666 x_warranty_tbl => war_tbl
667 ) ;
668 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
669 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
670 'oks_extwar_util_pvt.get_warranty_info(Return status = '||l_return_status ||',Count = '|| war_tbl.count ||')' );
671 END IF;
672
673 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
674 x_return_status := l_return_status;
675 Raise G_EXCEPTION_HALT_VALIDATION;
676 End If;
677
678
679
680 --If No warranty and if legacy ..Skip immediate And delayed service routine
681
682
683 If war_tbl.count = 0 And p_instance_rec.order_line_id = okc_api.g_miss_num Then
684
685 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
686 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW',
687 ' Legacy no order line ' );
688 END IF;
689 Raise G_EXCEPTION_HALT_VALIDATION;
690
691 ElsIf p_instance_rec.order_line_id <> okc_api.g_miss_num Then
692 p_order_header_id := Get_order_header_id(p_instance_rec.order_line_id);
693 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
694 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW',
695 ' Order Line Routine. '|| ';'|| 'Order header id = '||p_order_header_id ||'Before update statement' );
696 END IF;
697
698 /* Fix for Bug 2240584.
699 If the procedure OKS_IBINT_PUB is invoked simultaneously for
700 two order lines of the same Order, two contracts were getting created.
701 To avoid that a update statement is included, so that the second order
702 line waits until the first OL is processed.
703 */
704
705 -- Commented out for the fix of bug# 5088409 (JVARGHES)
706 --
707 -- Update Oe_Order_Headers_all
708 -- Set last_updated_by = last_updated_by
709 -- Where header_id = p_order_header_id;
710 --
711 -- Commented out for the fix of bug# 5088409 (JVARGHES)
712
713 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
714 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW',
715 ' After update statement' );
716 END IF;
717
718 OKS_INTEGRATION_UTIL_PUB.Create_K_Order_Details
719 (
720 p_header_id => p_order_header_id
721 , x_return_status => l_return_status
722 , x_msg_count => l_msg_count
723 , x_msg_data => l_msg_data
724 );
725
726 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
727 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
728 'oks_integration_util_pub.create_k_order_details(Return status = '||l_return_status ||')' );
729 END IF;
730
731 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
732 x_return_status := l_return_status;
733 Raise G_EXCEPTION_HALT_VALIDATION;
734 End If;
735
736 End If;
737
738 -- Warranty Check
739
740 If Not war_tbl.Count = 0 Then
741
742 If p_instance_rec.order_line_id = okc_api.g_miss_num Then -- Legacy Warranty Routine
743 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
744 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW',
745 ' Legacy Warranty Routine' );
746 END IF;
747
748 l_chr_id := null;
749
750 For l_ptr in 1..war_tbl.count
751 Loop
752
753 Open l_line_csr (war_tbl(l_ptr).service_item_id);
754 Fetch l_line_csr Into l_itm_rec;
755 Close l_line_csr;
756 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
757 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW.l_line_csr',
758 ' Name = '||l_itm_rec.name ||',Description = '||l_itm_rec.description ||
759 ',Primary UOM code = '||l_itm_rec.Primary_UOM_Code ||',Service starting delay = '||l_itm_rec.Service_starting_delay);
760 END IF;
761
762
763 If fnd_profile.value('OKS_ITEM_DISPLAY_PREFERENCE') = 'DISPLAY_NAME' Then
764 l_extwar_rec.srv_desc := l_itm_rec.description;
765 l_extwar_rec.srv_name := l_itm_rec.name;
766 Else
767 l_extwar_rec.srv_desc := l_itm_rec.name;
768 l_extwar_rec.srv_name := l_itm_rec.description;
769 End If;
770
771 -- warranty consolidation
772 -- If customer product has two or more warraties attached to it in BOM, only one contract will get created
773 -- with as many warranty lines.
774 -- profile option created for warranty consolidation --23-apr-2003
775
776 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
777 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW',
778 ' Warranty consolidation profile option '|| fnd_profile.value('OKS_WARRANTY_CONSOLIDATION'));
779 END IF;
780
781 If l_chr_id IS NOT NULL AND NVL(fnd_profile.value('OKS_WARRANTY_CONSOLIDATION'),'Y') = 'Y' Then
782 l_extwar_rec.Merge_Type := 'LTC';
783 l_extwar_rec.merge_Object_Id := l_chr_id;
784 Else
785 l_extwar_rec.Merge_Type := 'WARR';
786 l_extwar_rec.merge_Object_Id := NULL;
787 End if;
788
789 l_extwar_rec.hdr_scs_code := 'WARRANTY';
790 l_extwar_rec.Warranty_flag := 'W';
791 l_extwar_rec.rty_code := 'CONTRACTWARRANTYORDER';
792 l_extwar_rec.hdr_sdt := war_tbl(1).Warranty_start_date;
793 l_extwar_rec.hdr_edt := war_tbl(1).Warranty_end_date;
794 l_extwar_rec.hdr_org_id := p_instance_rec.org_id;
795 l_extwar_rec.hdr_party_id := Get_Party_id(p_instance_rec.old_CustomeR_acct_id);
796 --l_extwar_rec.hdr_inv_to_contact_id := NULL;
797 l_extwar_rec.hdr_bill_2_id := p_instance_rec.bill_to_site_use_id;
798 l_extwar_rec.hdr_ship_2_id := p_instance_rec.ship_to_site_use_id;
799 l_extwar_rec.hdr_cust_po_number := NULL;
800 --l_extwar_rec.hdr_agreement_id := l_cust_rec.Product_agreement_id;
801 --l_extwar_rec.hdr_currency := Nvl(l_cust_rec.Original_ordeR_currency_code,'USD');
802 l_extwar_rec.hdr_agreement_id := NULL;
803 l_extwar_rec.hdr_currency := OKC_CURRENCY_API.GET_OU_CURRENCY(p_instance_rec.org_id);
804 l_extwar_rec.hdr_acct_rule_id := NULL;
805 l_extwar_rec.hdr_inv_rule_id := NULL;
806 l_extwar_rec.hdr_ordeR_hdr_id := NULL;
807 l_extwar_rec.hdr_third_party_role := l_rnrl_rec_out.rle_code;
808 --l_extwar_rec.hdr_price_list_id := l_cust_rec.Price_list_id;
809 l_extwar_rec.hdr_status := NULL;
810 l_extwar_rec.hdr_payment_term_id := NULL;
811 l_extwar_rec.hdr_cvn_type := NULL;
812 l_extwar_rec.hdr_cvn_rate := NULL;
813 l_extwar_rec.hdr_cvn_date := NULL;
814 l_extwar_rec.hdr_cvn_euro_rate := NULL;
815 l_extwar_rec.hdr_chr_group := l_rnrl_rec_out.cgp_new_id;
816 l_extwar_rec.hdr_pdf_id := NULL;
817 l_extwar_rec.hdr_tax_exemption_id := NULL;
818 l_extwar_rec.hdr_tax_status_flag := NULL;
819 l_extwar_rec.hdr_tax_exemption_id := NULL;
820 l_extwar_rec.hdr_renewal_type := 'DNR';
821 l_extwar_rec.hdr_renewal_pricing_type := NULL;
822 l_extwar_rec.hdr_renewal_price_list_id := NULL;
823 l_extwar_rec.hdr_renewal_markup := NULL;
824 l_extwar_rec.hdr_renewal_po := NULL;
825 l_extwar_rec.line_invoicing_rule_id := NULL;
826 l_extwar_rec.line_accounting_rule_id := NULL;
827 l_extwar_rec.srv_sdt := war_tbl(l_ptr).Warranty_start_date;
828 l_extwar_rec.srv_edt := war_tbl(l_ptr).Warranty_end_date;
829 l_extwar_rec.srv_id := war_tbl(l_ptr).Service_item_id;
830 l_extwar_rec.srv_cov_template_id := war_tbl(l_ptr).coverage_schedule_id;
831 --l_extwar_rec.srv_desc := l_itm_rec.description;
832 --l_extwar_rec.srv_name := l_itm_rec.name;
833 l_extwar_rec.srv_bill_2_id := p_instance_rec.bill_to_site_use_id;
834 l_extwar_rec.srv_ship_2_id := p_instance_rec.ship_to_site_use_id;
835 l_extwar_rec.srv_order_line_id := NULL;
836 l_extwar_rec.lvl_quantity := p_instance_rec.old_quantity;
837 l_extwar_rec.Srv_amount := Null;
838 l_extwar_rec.srv_unit_price := Null;
839 --l_extwar_rec.srv_currency := Nvl(l_cust_rec.original_order_currency_code,OKC_CURRENCY_API.GET_OU_CURRENCY(p_instance_rec.org_id));
840 l_extwar_rec.srv_currency := OKC_CURRENCY_API.GET_OU_CURRENCY(p_instance_rec.org_id);
841 l_extwar_rec.lvl_cp_id := p_instance_rec.old_customer_product_id;
842 l_extwar_rec.lvl_inventory_id := p_instance_rec.old_Inventory_item_id;
843 --l_extwar_rec.lvl_inventory_name := l_line_dtl_rec.name;
844 l_extwar_rec.lvl_UOM_code := p_instance_rec.old_Unit_of_measure;
845 l_extwar_rec.Cust_account := p_instance_rec.old_CustomeR_acct_id;
846 --l_extwar_rec.Merge_Type := 'WARR';
847 --l_extwar_rec.merge_Object_Id := NULL;
848 l_extwar_rec.qto_contact_id := Null;
849 l_extwar_rec.qto_email_id := Null;
850 l_extwar_rec.qto_phone_id := Null;
851 l_extwar_rec.qto_fax_id := Null;
852 l_extwar_rec.qto_site_id := Null;
853 l_extwar_rec.billing_profile_id := Null;
854 l_extwar_rec.line_renewal_type := 'DNR';
855 l_extwar_rec.lvl_line_renewal_type := 'DNR';
856 l_extwar_rec.tax_amount := 0;
857 l_extwar_rec.renewal_status := 'COMPLETE';
858
859 x_inst_dtls_tbl(l_ptr).TRANSACTION_DATE := p_instance_rec.transaction_date;
860 x_inst_dtls_tbl(l_ptr).TRANSACTION_TYPE := 'NEW';
861 x_inst_dtls_tbl(l_ptr).INSTANCE_ID_NEW := p_instance_rec.old_customer_product_id;
862 x_inst_dtls_tbl(l_ptr).INSTANCE_QTY_NEW := p_instance_rec.old_quantity;
863
864 OKS_EXTWARPRGM_PVT .Create_Contract_IBNEW
865 (
866 p_extwar_rec => l_ExtWar_Rec
867 , p_contact_tbl_in => p_contact_tbl
868 , p_salescredit_tbl_hdr_in => l_SalesCredit_tbl_hdr --mmadhavi bug 4174921
869 , p_salescredit_tbl_line_in => l_salescredit_tbl
870 , p_price_attribs_in => l_pricing_attributes_in
871 , x_inst_dtls_tbl => x_inst_dtls_tbl
872 , x_chrid => l_chr_id
873 , x_return_status => l_return_status
874 , x_msg_count => x_msg_count
875 ,x_msg_data => x_msg_data
876 );
877 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
878 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
879 'oks_extwarprgm_pvt .create_contract_ibnew(Return status = '||l_return_status ||
880 ',Chr_id = ' || l_chr_Id || ')');
881 END IF;
882
883 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
884
885 FOR i in 1..fnd_msg_pub.count_msg
886 Loop
887 fnd_msg_pub.get
888 (
889 p_msg_index => i,
890 p_encoded => 'F',
891 p_data => l_msg_data,
892 p_msg_index_out => l_index
893 );
894 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
895 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW','Legacy warranty error : ' || l_msg_data);
896 END IF;
897
898 If(G_FND_LOG_OPTION = 'Y') Then
899 FND_FILE.PUT_LINE (FND_FILE.LOG, 'LEGACY WARRANTY ERROR : ' || l_msg_data );
900 End If;
901 End Loop;
902
903 x_return_status := l_return_status;
904 Raise G_EXCEPTION_HALT_VALIDATION;
905 End if;
906
907 End Loop;
908
909
910 Raise G_EXCEPTION_HALT_VALIDATION;
911
912 End If; -- Legacy Warranty Routine End
913 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
914 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW',
915 'Order based Warranty Routine' );
916 END IF;
917
918 --OM Originated Warranty Routine Starts Here
919
920 OKS_EXTWAR_UTIL_PVT.Get_Contract_Header_Info
921 (
922 P_Order_line_id => p_instance_rec.Order_line_id,
923 P_CP_Id => p_instance_rec.Old_Customer_product_id,
924 p_caller => 'IB',
925 x_order_error => l_order_error,
926 X_Return_Status => l_return_status,
927 X_Header_Rec => l_header_rec
928 );
929
930 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
931 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
932 'oks_extwar_util_pvt.get_contract_header_info(Return status = '||l_return_status || ')');
933 END IF;
934
935 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
936 x_return_status := l_return_status;
937 Raise G_EXCEPTION_HALT_VALIDATION;
938 End If;
939
940 For l_ptr in 1..war_tbl.count --OM Warranty Loop
941 Loop
942
943 OKS_EXTWAR_UTIL_PVT.Get_Contract_Line_Info
944 (
945 P_Order_line_id => p_instance_rec.Order_line_id,
946 P_CP_Id => p_instance_rec.old_Customer_product_id,
947 P_Product_Item => p_instance_rec.old_inventory_item_id,
948 X_Return_Status => l_return_status,
949 X_Line_Rec => l_line_rec
950 );
951
952 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
953 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
954 'oks_extwar_util_pvt.get_contract_line_info(Return status = '||l_return_status || ')');
955 END IF;
956
957 -- Exception to be written
958 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
959 x_return_status := l_return_status;
960 Raise G_EXCEPTION_HALT_VALIDATION;
961 End If;
962
963
964 OKS_EXTWAR_UTIL_PVT.Check_Service_Duplicate
965 (
966 X_Return_Status => l_return_status,
967 P_Order_Line_Id => p_instance_rec.order_line_id,
968 P_Serv_Id => war_tbl(l_ptr).Service_item_id,
969 P_Customer_Product_id => p_instance_rec.old_customer_product_id,
970 P_Serv_start_date => war_tbl(l_ptr).Warranty_start_date,
971 P_Serv_end_date => war_tbl(l_ptr).Warranty_end_date,
972 X_Service_Status =>l_service_status
973 );
974 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
975 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
976 'oks_extwar_util_pvt.check_service_duplicate(Return status = '||l_return_status ||
977 'Service status = ' || l_service_status || ')');
978 END IF;
979
980 -- Exception to be written
981 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
982 x_return_status := l_return_status;
983 Raise G_EXCEPTION_HALT_VALIDATION;
984 End If;
985
986 -- l_service_status := 'N';
987 If l_service_status = 'N' Then -- Duplicate Check
988
989 -- warranty consolidation -Vigandhi
990 -- If existing contract is terminated new warraty contract will be created.
991 -- If existing contract is expired its sts_code and effectivity will be changed.
992 -- profile option created for warranty consolidation 23-apr-2003
993
994 p_order_header_id := Get_order_header_id(p_instance_rec.order_line_id);
995 l_chr_id := Null;
996
997 Open get_chr_id_csr(p_order_header_id);
998 Fetch get_chr_id_csr into l_chr_id;
999 close get_chr_id_csr;
1000
1001 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1002 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_NEW',
1003 'Warranty consolidation profile option '|| fnd_profile.value('OKS_WARRANTY_CONSOLIDATION'));
1004 END IF;
1005
1006 If l_chr_id Is not null AND NVL(fnd_profile.value('OKS_WARRANTY_CONSOLIDATION'),'Y') = 'Y' then
1007 l_extwar_rec.Merge_Type := 'LTC';
1008 l_extwar_rec.merge_Object_Id := l_chr_id;
1009 Else
1010 l_extwar_rec.Merge_Type := 'WARR';
1011 l_extwar_rec.merge_Object_Id := NULL;
1012 end if;
1013
1014
1015 l_extwar_rec.Warranty_flag := 'W';
1016 l_extwar_rec.hdr_scs_code := 'WARRANTY';
1017 l_extwar_rec.rty_code := 'CONTRACTWARRANTYORDER';
1018 l_extwar_rec.hdr_sdt := war_tbl(1).Warranty_start_date;
1019 l_extwar_rec.hdr_edt := war_tbl(1).Warranty_end_date;
1020 l_extwar_rec.hdr_org_id := l_header_rec.authoring_org_id;
1021 l_extwar_rec.hdr_party_id := l_header_rec.party_id;
1022 -- l_extwar_rec.hdr_inv_to_contact_id := l_header_rec.invoice_to_contact_id;
1023 l_extwar_rec.hdr_bill_2_id := l_header_rec.bill_to_id;
1024 l_extwar_rec.hdr_ship_2_id := l_header_rec.ship_to_id;
1025 l_extwar_rec.hdr_cust_po_number := l_header_rec.cust_po_number;
1026 l_extwar_rec.hdr_agreement_id := l_header_rec.agreement_id;
1027 l_extwar_rec.hdr_currency := nvl(l_header_rec.currency,OKC_CURRENCY_API.GET_OU_CURRENCY(p_instance_rec.org_id));
1028 l_extwar_rec.hdr_acct_rule_id := NVL(l_header_rec.accounting_rule_id,1);
1029 l_extwar_rec.hdr_inv_rule_id := NVL(l_header_rec.invoice_rule_id,-2);
1030 l_extwar_rec.hdr_ordeR_hdr_id := l_header_rec.order_hdr_id;
1031 l_extwar_rec.hdr_third_party_role := l_rnrl_rec_out.rle_code;
1032 l_extwar_rec.hdr_price_list_id := l_header_rec.Price_list_id;
1033 l_extwar_rec.hdr_status := NULL;
1034 l_extwar_rec.hdr_payment_term_id := l_header_rec.hdr_Payment_term_id;
1035 l_extwar_rec.hdr_cvn_type := l_header_rec.hdr_cvn_type;
1036 l_extwar_rec.hdr_cvn_rate := l_header_rec.hdr_cvn_rate;
1037 l_extwar_rec.hdr_cvn_date := l_header_rec.hdr_cvn_date;
1038 l_extwar_rec.hdr_cvn_euro_rate := NULL;
1039 l_extwar_rec.hdr_chr_group := l_rnrl_rec_out.cgp_new_id;
1040 l_extwar_rec.hdr_pdf_id := NULL;
1041 l_extwar_rec.hdr_tax_exemption_id := l_header_rec.hdr_tax_exemption_id;
1042 l_extwar_rec.hdr_tax_status_flag := l_header_rec.hdr_tax_status_flag;
1043 l_extwar_rec.hdr_renewal_type := 'DNR';
1044 l_extwar_rec.hdr_renewal_pricing_type := NULL;
1045 l_extwar_rec.hdr_renewal_price_list_id := NULL;
1046 l_extwar_rec.hdr_renewal_markup := NULL;
1047 l_extwar_rec.hdr_renewal_po := NULL;
1048 l_extwar_rec.srv_sdt := war_tbl(l_ptr).Warranty_start_date;
1049 l_extwar_rec.srv_edt := war_tbl(l_ptr).Warranty_end_date;
1050 l_extwar_rec.srv_id := war_tbl(l_ptr).Service_item_id;
1051 l_extwar_rec.srv_cov_template_id := war_tbl(l_ptr).coverage_schedule_id;
1052 l_extwar_rec.srv_desc := l_line_rec.srv_desc;
1053 l_extwar_rec.srv_name := l_line_rec.srv_segment1;
1054 l_extwar_rec.srv_bill_2_id := l_line_rec.bill_to_id;
1055 l_extwar_rec.srv_ship_2_id := l_line_rec.ship_to_id;
1056 l_extwar_rec.srv_order_line_id := l_line_rec.order_line_id;
1057 l_extwar_rec.lvl_quantity := p_instance_rec.old_quantity;
1058 l_extwar_rec.Srv_amount := Null;
1059 l_extwar_rec.srv_unit_price := Null;
1060 --l_extwar_rec.srv_currency := Null;
1061 l_extwar_rec.srv_currency := OKC_CURRENCY_API.GET_OU_CURRENCY(p_instance_rec.org_id);
1062 l_extwar_rec.lvl_cp_id := p_instance_rec.old_customer_product_id;
1063 l_extwar_rec.lvl_inventory_id := p_instance_rec.old_Inventory_item_id;
1064 -- l_extwar_rec.lvl_inventory_desc := l_line_dtl_rec.description;
1065 -- l_extwar_rec.lvl_inventory_name := l_line_dtl_rec.name;
1066 l_extwar_rec.lvl_UOM_code := p_instance_rec.old_Unit_of_measure;
1067 l_extwar_rec.Cust_account := l_line_rec.CustomeR_acct_id;
1068 --l_extwar_rec.Cust_account := p_instance_rec.CustomeR_acct_id;
1069 l_extwar_rec.line_Invoicing_rule_id := NVL(l_line_rec.Invoicing_rule_id,-2);
1070 l_extwar_rec.line_Accounting_rule_id := NVL(l_line_rec.Accounting_rule_id,1);
1071 -- l_extwar_rec.Merge_Type := 'WARR';
1072 -- l_extwar_rec.merge_Object_Id := NULL;
1073 l_extwar_rec.qto_contact_id := Null;
1074 l_extwar_rec.qto_email_id := Null;
1075 l_extwar_rec.qto_phone_id := Null;
1076 l_extwar_rec.qto_fax_id := Null;
1077 l_extwar_rec.qto_site_id := Null;
1078 l_extwar_rec.billing_profile_id := Null;
1079 l_extwar_rec.salesrep_id := l_header_rec.salesrep_id;
1080 l_extwar_rec.commitment_id := l_line_rec.commitment_id;
1081 l_extwar_rec.line_renewal_type := 'DNR';
1082 l_extwar_rec.lvl_line_renewal_type := 'DNR';
1083 l_extwar_rec.tax_amount := 0;
1084 l_extwar_rec.renewal_status := 'COMPLETE';
1085
1086 p_contact_tbl.delete;
1087
1088 x_inst_dtls_tbl(l_ptr).TRANSACTION_DATE := p_instance_rec.transaction_date;
1089 x_inst_dtls_tbl(l_ptr).TRANSACTION_TYPE := 'NEW';
1090 x_inst_dtls_tbl(l_ptr).INSTANCE_ID_NEW := p_instance_rec.old_customer_product_id;
1091 x_inst_dtls_tbl(l_ptr).INSTANCE_QTY_NEW := p_instance_rec.old_quantity;
1092
1093 OKS_EXTWARPRGM_PVT .Create_Contract_IBNEW
1094 (
1095 p_extwar_rec => l_ExtWar_Rec
1096 , p_contact_tbl_in => p_contact_tbl
1097 , p_salescredit_tbl_hdr_in => l_SalesCredit_tbl_hdr --mmadhavi bug 4174921
1098 , p_salescredit_tbl_line_in => l_salescredit_tbl
1099 , p_price_attribs_in => l_pricing_attributes_in
1100 , x_inst_dtls_tbl => x_inst_dtls_tbl
1101 , x_chrid => l_chr_id
1102 , x_return_status => l_return_status
1103 , x_msg_count => x_msg_count
1104 , x_msg_data => x_msg_data
1105 );
1106 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1107 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
1108 'oks_extwarprgm_pvt .create_contract_ibnew(Return status = '||l_return_status || ')');
1109 END IF;
1110
1111 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1112
1113 FOR i in 1..fnd_msg_pub.count_msg
1114 Loop
1115 fnd_msg_pub.get
1116 (
1117 p_msg_index => i,
1118 p_encoded => 'F',
1119 p_data => l_msg_data,
1120 p_msg_index_out => l_index
1121 );
1122
1123 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1124 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW','OM WARRANTY ERROR : ' || l_msg_data);
1125 END IF;
1126
1127 If(G_FND_LOG_OPTION = 'Y') Then
1128 FND_FILE.PUT_LINE (FND_FILE.LOG, 'OM WARRANTY ERROR : ' || l_msg_data );
1129 End If;
1130 End Loop;
1131
1132 x_return_status := l_return_status;
1133 Raise G_EXCEPTION_HALT_VALIDATION;
1134
1135 End if;
1136
1137 End if; -- Duplicate Check Ends
1138
1139 End Loop; --OM Warranty Loop
1140
1141 End If; -- Warranty Check
1142
1143
1144 Exception
1145 When G_EXCEPTION_HALT_VALIDATION Then
1146 x_return_status := l_return_status;
1147
1148 End IB_NEW;
1149
1150
1151
1152 Procedure IB_Interface
1153 (P_Api_Version NUMBER,
1154 P_init_msg_list VARCHAR2,
1155 P_single_txn_date_flag VARCHAR2,
1156 P_Batch_type VARCHAR2,
1157 P_Batch_ID NUMBER,
1158 P_OKS_Txn_Inst_tbl TXN_INSTANCE_tbl,
1159 x_return_status OUT NOCOPY VARCHAR2,
1160 x_msg_count OUT NOCOPY NUMBER,
1161 x_msg_data OUT NOCOPY VARCHAR2) Is
1162
1163
1164
1165 Cursor get_Instances_for_new_csr Is
1166 Select old_customer_product_id
1167 ,old_quantity
1168 ,Bom_explosion_flag
1169 ,Old_Unit_of_measure
1170 ,Old_Inventory_item_id
1171 ,Old_Customer_acct_id
1172 ,Organization_id
1173 ,Bill_to_site_use_id
1174 ,Ship_to_site_use_id
1175 ,Org_id
1176 ,Order_line_id
1177 ,Shipped_date
1178 ,Installation_date
1179 ,transaction_date
1180 From Oks_instance_temp
1181 Where New = 'Y';
1182
1183
1184
1185
1186 Cursor get_Contracts_for_transfer_csr Is
1187 Select KI.Dnz_Chr_Id
1188 ,KH.authoring_org_id
1189 ,KH.start_date hdr_sdt
1190 ,KH.end_date hdr_edt
1191 ,KH.sts_code hdr_sts
1192 ,KH.Contract_number
1193 ,KH.scs_code
1194 ,OL.Id LineId
1195 ,OL.line_NUMBER
1196 ,KIS.object1_id1
1197 ,KI.CLE_ID
1198 ,OL.Start_date Line_start_date
1199 ,OL.End_Date line_end_date
1200 ,OL.bill_to_site_use_id
1201 ,OL.Ship_to_site_use_id
1202 ,KL.Price_Negotiated Service_amount
1203 ,OKL.tax_amount
1204 ,KSL.tax_code
1205 ,KL.Price_unit
1206 ,OL.Currency_Code Service_Currency
1207 ,KI.NUMBER_OF_ITEMS
1208 ,OL.cust_acct_id
1209 ,KSL.Acct_rule_id line_acct_rule_id
1210 ,OL.inv_rule_id line_inv_rule_id
1211 ,Kh.price_list_id
1212 ,KH.payment_term_id
1213 ,KS.acct_rule_id
1214 ,KH.Inv_rule_id
1215 ,KS.AR_interface_yn
1216 ,KS.Summary_trx_yn
1217 ,KS.Hold_billing
1218 ,KS.Inv_trx_type
1219 ,KS.Payment_type
1220 ,KH.inv_organization_id
1221 ,KH.Conversion_type
1222 ,KH.Conversion_rate
1223 ,KH.COnversion_rate_date
1224 ,KH.Conversion_euro_rate
1225 ,KH.Billed_at_source
1226 ,Kl.cle_id_renewed
1227 ,OL.sts_code line_sts_code
1228 ,KL.sts_code
1229 ,KL.start_date
1230 ,KL.end_date
1231 ,KL.date_terminated
1232 ,KL.lse_id
1233 ,KL.Name
1234 ,KL.Item_description
1235 ,KL.line_renewal_type_code
1236 ,KL.upg_orig_system_ref
1237 ,KL.upg_orig_system_ref_id
1238 ,KH.cust_po_number
1239 ,KH.currency_code
1240 ,PARTY.Object1_id1 Party_id
1241 ,tmp.old_customer_product_id
1242 ,tmp.old_inventory_item_id
1243 ,tmp.transfer_date
1244 ,tmp.transaction_date
1245 ,tmp.old_customer_acct_id
1246 ,tmp.new_customer_acct_id
1247 ,tmp.System_id
1248 ,tmp.old_customer_product_id
1249 ,KSL.Coverage_Id
1250 ,KSL.standard_cov_yn
1251 ,KS.Period_Start
1252 ,KS.Period_type
1253 ,tmp.old_unit_of_measure
1254 ,okl.price_uom sl_price_uom
1255 ,ksl.price_uom tl_price_uom
1256 ,ks.price_uom hdr_price_uom
1257 ,okl.toplvl_uom_code
1258 ,okl.toplvl_price_qty
1259 From OKC_K_ITEMS KI
1260 ,OKC_K_HEADERS_ALL_B KH
1261 ,OKS_K_HEADERS_B KS
1262 ,OKC_K_LINES_V KL
1263 ,OKS_K_LINES_B OKL
1264 ,OKC_STATUSES_B ST
1265 ,OKC_K_PARTY_ROLES_B PARTY
1266 , OKC_K_LINES_B OL
1267 ,OKS_K_LINES_B KSL
1268 ,OKC_K_ITEMS KIS
1269 , OKS_INSTANCE_TEMP tmp
1270
1271 Where tmp.trf = 'Y'
1272 And nvl(tmp.trm,'N') = 'N'
1273 And KI.Object1_id1 = to_char(tmp.old_customer_product_id)
1274 And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
1275 And KI.dnz_chr_id = KH.ID
1276 And KS.Chr_id(+) = KH.ID
1277 And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' ) -- supp
1278 And KI.Cle_id = KL.id
1279 And OKL.CLE_ID(+) = KL.ID
1280 And KL.sts_code = ST.code
1281 And ST.ste_code not in ('TERMINATED','CANCELLED')
1282 And KL.date_terminated Is Null
1283 And KH.template_yn = 'N'
1284 And OL.Id = KL.cle_id
1285 And KSL.cle_id(+) = OL.Id
1286 And KIS.cle_id = OL.Id
1287 And KIS.dnz_chr_id = OL.dnz_chr_id
1288 AND PARTY.dnz_chr_id = kH.ID
1289 AND PARTY.chr_id is not null
1290 AND PARTY.cle_id is null
1291 And PARTY.rle_code in ('CUSTOMER','SUBSCRIBER')
1292 And PARTY.jtot_object1_code = 'OKX_PARTY'
1293 And ( (trunc(tmp.transfer_date) <= trunc(KL.end_date)And trunc(tmp.transfer_date) >= trunc(KL.start_date))
1294 OR (trunc(tmp.transfer_date) <= trunc(kl.start_date))
1295 OR ( trunc(KL.end_date) < trunc(tmp.transfer_date)
1296 And kl.lse_id <> 18
1297 And not exists (Select 'x'
1298 from okc_operation_instances ois,
1299 okc_operation_lines opl,
1300 okc_class_operations cls,
1301 okc_subclasses_b sl
1302 where ois.id=opl.oie_id
1303 And cls.opn_code in ('RENEWAL','REN_CON')
1304 And sl.code= 'SERVICE'
1305 And sl.cls_code = cls.cls_code
1306 and ois.cop_id = cls.id
1307 and object_cle_id=kl.id
1308 )
1309 )
1310 )
1311 order by tmp.old_customer_product_id, kh.creation_date; --KI.Dnz_Chr_Id;
1312
1313 Cursor get_k_for_trm_csr Is
1314 Select tmp.old_customer_product_id
1315 ,tmp.termination_date
1316 ,tmp.installation_date
1317 ,tmp.transaction_date
1318 ,tmp.old_Customer_acct_id
1319 ,tmp.new_customer_acct_id
1320 ,tmp.System_id
1321 ,tmp.old_quantity
1322 ,tmp.new_quantity
1323 ,tmp.new_customer_product_id
1324 ,KI.CLE_ID SubLine_id
1325 , KI.Dnz_Chr_Id
1326 ,KH.start_date hdr_sdt
1327 ,KH.end_date hdr_edt
1328 ,KH.sts_code hdr_sts
1329 , KL.Cle_Id
1330 ,KL.Price_negotiated
1331 , KL.Start_date
1332 , KL.end_date
1333 , KL.sts_code prod_sts
1334 , KL.Cust_acct_id
1335 , TL.start_date Srv_sdt
1336 , TL.end_date Srv_edt
1337 , KH.sts_code
1338 , KH.Contract_number
1339 , KI.number_of_items
1340 , TL.price_negotiated
1341 , KL.date_terminated
1342 ,tmp.old_inventory_item_id
1343 , KH.authoring_org_id
1344 ,KH.inv_organization_id
1345 ,KL.lse_id
1346 , KH.scs_code
1347 , tmp.new_customer_product_id
1348 , KIS.Object1_id1
1349 , TL.Currency_code
1350 ,tmp.old_unit_of_measure
1351 , KL.line_renewal_type_code
1352 , tmp.raise_credit
1353 ,PARTY.Object1_id1 Party_id
1354 , Null
1355 , Null
1356 , Null
1357 , Null
1358 , Null
1359 , Null
1360 , Null
1361 , tmp.return_reason_code
1362 , tmp.order_line_id
1363 , Null
1364 , Null
1365 , Null
1366
1367 From OKC_K_ITEMS KI
1368 , OKC_K_HEADERS_ALL_B KH
1369 , OKC_K_LINES_B KL
1370 , OKC_STATUSES_B ST
1371 , OKS_INSTANCE_TEMP tmp
1372 , OKC_K_LINES_B TL
1373 ,OKC_K_ITEMS KIS
1374 ,OKC_K_PARTY_ROLES_B PARTY
1375 Where tmp.trm = 'Y'
1376 And KI.Object1_id1 = to_char(tmp.old_customer_product_id)
1377 And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
1378 And KI.dnz_chr_id = KH.ID
1379 And KH.scs_code in ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
1380 And KI.Cle_id = KL.id
1381 And TL.Id = KL.cle_id
1382 And KIS.cle_id = TL.id
1383 And KIS.dnz_chr_id = TL.dnz_chr_id
1384 And KL.sts_code = ST.code
1385 And ST.ste_code not in ('TERMINATED','CANCELLED')
1386 And KL.date_terminated Is Null
1387 And KH.template_yn = 'N'
1388 AND PARTY.dnz_chr_id = kH.ID
1389 AND PARTY.chr_id is not null
1390 AND PARTY.cle_id is null
1391 And PARTY.rle_code in ('CUSTOMER','SUBSCRIBER')
1392 And PARTY.jtot_object1_code = 'OKX_PARTY'
1393
1394 And ( (trunc(tmp.Termination_date) <= trunc(KL.end_date)
1395 And trunc(tmp.Termination_date) >= trunc(KL.start_date))
1396 OR (trunc(tmp.Termination_date) <= trunc(kl.start_date))
1397 OR ( trunc(KL.end_date) < trunc(tmp.Termination_date)
1398 And kl.lse_id <> 18
1399 And not exists (Select 'x'
1400 from okc_operation_instances ois,
1401 okc_operation_lines opl,
1402 okc_class_operations cls,
1403 okc_subclasses_b sl
1404 where ois.id=opl.oie_id
1405 And cls.opn_code in ('RENEWAL','REN_CON')
1406 And sl.code= 'SERVICE'
1407 And sl.cls_code = cls.cls_code
1408 and ois.cop_id = cls.id
1409 and object_cle_id=kl.id)
1410 ))
1411
1412
1413
1414 Union
1415
1416 Select tmp.old_customer_product_id Instance_id
1417 ,tmp.termination_date
1418 ,tmp.installation_date
1419 ,tmp.transaction_date
1420 ,tmp.old_Customer_acct_id
1421 ,tmp.new_customer_acct_id
1422 ,tmp.System_id
1423 ,tmp.old_quantity
1424 ,tmp.new_quantity
1425 ,tmp.new_customer_product_id
1426 ,KI.CLE_ID SubLine_id
1427 , KI.Dnz_Chr_Id
1428 ,KH.start_date hdr_sdt
1429 ,KH.end_date hdr_edt
1430 ,KH.sts_code hdr_sts
1431 , KL.Cle_Id
1432 ,KL.Price_negotiated
1433 , KL.Start_date
1434 , KL.end_date
1435 , KL.sts_code prod_sts
1436 , KL.Cust_acct_id
1437 , TL.start_date Srv_sdt
1438 , TL.end_date Srv_edt
1439 , KH.sts_code
1440 , KH.Contract_number
1441 , KI.number_of_items
1442 , TL.price_negotiated
1443 , KL.date_terminated
1444 ,tmp.old_inventory_item_id
1445 , KH.authoring_org_id
1446 ,KH.inv_organization_id
1447 ,KL.lse_id
1448 , KH.scs_code
1449 , tmp.new_customer_product_id
1450 , KIS.Object1_id1
1451 , TL.Currency_code
1452 ,tmp.old_unit_of_measure
1453 , KL.line_renewal_type_code
1454 , tmp.raise_credit
1455 ,PARTY.Object1_id1 Party_id
1456 , Null
1457 , Null
1458 , Null
1459 , Null
1460 , Null
1461 , Null
1462 , Null
1463 , tmp.return_reason_code
1464 , tmp.order_line_id
1465 , Null
1466 , Null
1467 , Null
1468
1469
1470 From OKC_K_ITEMS KI
1471 ,OKC_K_HEADERS_ALL_B KH
1472 ,OKC_K_LINES_B KL
1473 ,OKC_K_LINES_B TL
1474 ,OKC_STATUSES_B ST
1475 ,csi_counter_associations CTRASC
1476 ,OKS_INSTANCE_TEMP tmp
1477 ,OKC_K_ITEMS KIS
1478 ,OKC_K_PARTY_ROLES_B PARTY
1479 Where tmp.trm = 'Y'
1480 And KI.object1_id1 = TO_CHAR (CTRASC.Counter_id)
1481 And ctrasc.source_object_id = tmp.old_customer_product_id
1482 And ctrAsc.source_object_code = 'CP'
1483 And KI.jtot_object1_code = 'OKX_COUNTER'
1484 And KI.dnz_chr_id = KH.ID
1485 And KH.scs_code in ('SERVICE','SUBSCRIPTION')
1486 And KI.Cle_id = KL.id
1487 And TL.Id = KL.cle_id
1488 And KIS.cle_id = TL.id
1489 And KIS.dnz_chr_id = TL.dnz_chr_id
1490 And KL.sts_code = ST.code
1491 And ST.ste_code not in ('TERMINATED','CANCELLED')
1492 And KL.date_terminated Is Null
1493 And KH.template_yn = 'N'
1494 AND PARTY.dnz_chr_id = kH.ID
1495 AND PARTY.chr_id is not null
1496 AND PARTY.cle_id is null
1497 And PARTY.rle_code in ('CUSTOMER','SUBSCRIBER')
1498 And PARTY.jtot_object1_code = 'OKX_PARTY'
1499
1500 And ( (trunc(tmp.Termination_date) <= trunc(KL.end_date)
1501 And trunc(tmp.Termination_date) >= trunc(KL.start_date))
1502 OR (trunc(tmp.Termination_date) <= trunc(kl.start_date))
1503 OR ( trunc(KL.end_date) < trunc(tmp.Termination_date)
1504 And not exists (Select 'x'
1505 from okc_operation_instances ois,
1506 okc_operation_lines opl,
1507 okc_class_operations cls,
1508 okc_subclasses_b sl
1509 where ois.id=opl.oie_id
1510 And cls.opn_code in ('RENEWAL','REN_CON')
1511 And sl.code= 'SERVICE'
1512 And sl.cls_code = cls.cls_code
1513 and ois.cop_id = cls.id
1514 and object_cle_id=kl.id)
1515 ));
1516
1517 Cursor get_k_for_ret_csr Is
1518 Select tmp.old_customer_product_id Instance_id
1519 ,tmp.termination_date
1520 ,tmp.installation_date
1521 ,tmp.transaction_date
1522 ,tmp.old_Customer_acct_id
1523 ,tmp.new_customer_acct_id
1524 ,tmp.System_id
1525 ,tmp.old_quantity
1526 ,tmp.new_quantity
1527 ,tmp.new_customer_product_id
1528 ,KI.CLE_ID SubLine_id
1529 , KI.Dnz_Chr_Id
1530 ,KH.start_date hdr_sdt
1531 ,KH.end_date hdr_edt
1532 ,KH.sts_code hdr_sts
1533 , KL.Cle_Id
1534 ,KL.Price_negotiated
1535 , KL.Start_date
1536 , KL.end_date
1537 , KL.sts_code prod_sts
1538 , KL.Cust_acct_id
1539 , TL.start_date Srv_sdt
1540 , TL.end_date Srv_edt
1541 , KH.sts_code
1542 , KH.Contract_number
1543 , KI.number_of_items
1544 , TL.price_negotiated
1545 , KL.date_terminated
1546 ,tmp.old_inventory_item_id
1547 , KH.authoring_org_id
1548 ,KH.inv_organization_id
1549 ,KL.lse_id
1550 , KH.scs_code
1551 , tmp.new_customer_product_id
1552 , KIS.Object1_id1
1553 , TL.Currency_code
1554 ,tmp.old_unit_of_measure
1555 , KL.line_renewal_type_code
1556 , tmp.raise_credit
1557 ,PARTY.Object1_id1 Party_id
1558 , Null
1559 , Null
1560 , Null
1561 , Null
1562 , Null
1563 , Null
1564 , Null
1565 , tmp.return_reason_code
1566 , tmp.order_line_id
1567 , Null
1568 , Null
1569 , Null
1570 From OKC_K_ITEMS KI
1571 , OKC_K_HEADERS_ALL_B KH
1572 , OKC_K_LINES_B KL
1573 , OKC_STATUSES_B ST
1574 , OKS_INSTANCE_TEMP tmp
1575 , OKC_K_LINES_B TL
1576 ,OKC_K_ITEMS KIS
1577 ,OKC_K_PARTY_ROLES_B PARTY
1578
1579 Where tmp.ret = 'Y'
1580 And KI.Object1_id1 = to_char(tmp.old_customer_product_id)
1581 And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
1582 And KI.dnz_chr_id = KH.ID
1583 And KH.scs_code in ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
1584 And KI.Cle_id = KL.id
1585 And TL.Id = KL.cle_id
1586 And KIS.cle_id = TL.id
1587 And KIS.dnz_chr_id = TL.dnz_chr_id
1588 And KL.sts_code = ST.code
1589 And ST.ste_code not in ('TERMINATED','CANCELLED')
1590 And KL.date_terminated Is Null
1591 AND PARTY.dnz_chr_id = kH.ID
1592 AND PARTY.chr_id is not null
1593 AND PARTY.cle_id is null
1594 And PARTY.rle_code in ('CUSTOMER','SUBSCRIBER')
1595 And PARTY.jtot_object1_code = 'OKX_PARTY'
1596
1597 And KH.template_yn = 'N'
1598 And ( (trunc(tmp.Termination_date) <= trunc(KL.end_date)
1599 And trunc(tmp.Termination_date) >= trunc(KL.start_date))
1600 OR (trunc(tmp.Termination_date) <= trunc(kl.start_date))
1601 OR ( trunc(KL.end_date) < trunc(tmp.Termination_date)
1602 And kl.lse_id <> 18
1603 And not exists (Select 'x'
1604 from okc_operation_instances ois,
1605 okc_operation_lines opl,
1606 okc_class_operations cls,
1607 okc_subclasses_b sl
1608 where ois.id=opl.oie_id
1609 And cls.opn_code in ('RENEWAL','REN_CON')
1610 And sl.code= 'SERVICE'
1611 And sl.cls_code = cls.cls_code
1612 and ois.cop_id = cls.id
1613 and object_cle_id=kl.id)
1614 ))
1615
1616
1617
1618 Union
1619
1620 Select tmp.old_customer_product_id
1621 ,tmp.termination_date
1622 ,tmp.installation_date
1623 ,tmp.transaction_date
1624 ,tmp.old_Customer_acct_id
1625 ,tmp.new_customer_acct_id
1626 ,tmp.System_id
1627 ,tmp.old_quantity
1628 ,tmp.new_quantity
1629 ,tmp.new_customer_product_id
1630 ,KI.CLE_ID SubLine_id
1631 , KI.Dnz_Chr_Id
1632 ,KH.start_date hdr_sdt
1633 ,KH.end_date hdr_edt
1634 ,KH.sts_code hdr_sts
1635 , KL.Cle_Id
1636 ,KL.Price_negotiated
1637 , KL.Start_date
1638 , KL.end_date
1639 , KL.sts_code prod_sts
1640 , KL.Cust_acct_id
1641 , TL.start_date Srv_sdt
1642 , TL.end_date Srv_edt
1643 , KH.sts_code
1644 , KH.Contract_number
1645 , KI.number_of_items
1646 , TL.price_negotiated
1647 , KL.date_terminated
1648 ,tmp.old_inventory_item_id
1649 , KH.authoring_org_id
1650 ,KH.inv_organization_id
1651 ,KL.lse_id
1652 , KH.scs_code
1653 , tmp.new_customer_product_id
1654 , KIS.Object1_id1
1655 , TL.Currency_code
1656 ,tmp.old_unit_of_measure
1657 , KL.line_renewal_type_code
1658 , tmp.raise_credit
1659 , PARTY.Object1_id1 Party_id
1660 , Null
1661 , Null
1662 , Null
1663 , Null
1664 , Null
1665 , Null
1666 , Null
1667 , tmp.return_reason_code
1668 , tmp.order_line_id
1669 , null
1670 , null
1671 , null
1672 From OKC_K_ITEMS KI
1673 ,OKC_K_HEADERS_ALL_B KH
1674 ,OKC_K_LINES_B KL
1675 ,OKC_K_LINES_B TL
1676 ,OKC_STATUSES_B ST
1677 ,csi_counter_associations CTRASC
1678 , OKS_INSTANCE_TEMP tmp
1679 ,OKC_K_ITEMS KIS
1680 ,OKC_K_PARTY_ROLES_B PARTY
1681
1682 Where tmp.ret = 'Y'
1683 And KI.object1_id1 = to_char(CTRASC.Counter_id)
1684 And ctrasc.source_object_id = tmp.old_customer_product_id
1685 And ctrAsc.source_object_code = 'CP'
1686 And ki.jtot_object1_code = 'OKX_COUNTER'
1687 And KI.dnz_chr_id = KH.ID
1688 And KH.scs_code in ('SERVICE','SUBSCRIPTION')
1689 And KI.Cle_id = KL.id
1690 And TL.Id = KL.cle_id
1691 And KIS.cle_id = TL.id
1692 And KIS.dnz_chr_id = TL.dnz_chr_id
1693 And KL.sts_code = ST.code
1694 And ST.ste_code not in ('TERMINATED','CANCELLED')
1695 And KL.date_terminated Is Null
1696 AND PARTY.dnz_chr_id = kH.ID
1697 AND PARTY.chr_id is not null
1698 AND PARTY.cle_id is null
1699 And PARTY.rle_code in ('CUSTOMER','SUBSCRIBER')
1700 And PARTY.jtot_object1_code = 'OKX_PARTY'
1701 And KH.template_yn = 'N'
1702 And ( (trunc(tmp.Termination_date) <= trunc(KL.end_date)
1703 And trunc(tmp.Termination_date) >= trunc(KL.start_date))
1704 OR (trunc(tmp.Termination_date) <= trunc(kl.start_date))
1705 OR ( trunc(KL.end_date) < trunc(tmp.Termination_date)
1706 And not exists (Select 'x'
1707 from okc_operation_instances ois,
1708 okc_operation_lines opl,
1709 okc_class_operations cls,
1710 okc_subclasses_b sl
1711 where ois.id=opl.oie_id
1712 And cls.opn_code in ('RENEWAL','REN_CON')
1713 And sl.code= 'SERVICE'
1714 And sl.cls_code = cls.cls_code
1715 and ois.cop_id = cls.id
1716 and object_cle_id=kl.id)
1717 ));
1718
1719
1720
1721 Cursor get_k_for_idc_csr Is
1722
1723 Select tmp.old_customer_product_id
1724 ,tmp.termination_date
1725 ,tmp.installation_date
1726 ,tmp.transaction_date
1727 ,tmp.old_Customer_acct_id
1728 ,tmp.new_customer_acct_id
1729 ,tmp.System_id
1730 ,tmp.old_quantity
1731 ,tmp.new_quantity
1732 ,tmp.new_customer_product_id
1733 ,KI.CLE_ID SubLine_id
1734 , KI.Dnz_Chr_Id
1735 ,KH.start_date hdr_sdt
1736 ,KH.end_date hdr_edt
1737 ,KH.sts_code hdr_sts
1738 , KL.Cle_Id
1739 ,KL.Price_negotiated
1740 , KL.Start_date
1741 , KL.end_date
1742 , KL.sts_code prod_sts
1743 , KL.Cust_acct_id
1744 , TL.start_date Srv_sdt
1745 , TL.end_date Srv_edt
1746 , KH.sts_code
1747 , KH.Contract_number
1748 , KI.number_of_items
1749 , TL.price_negotiated
1750 , KL.date_terminated
1751 ,tmp.old_inventory_item_id
1752 , KH.authoring_org_id
1753 ,KH.inv_organization_id
1754 ,KL.lse_id
1755 , KH.scs_code
1756 , tmp.new_customer_product_id
1757 , KIS.Object1_id1
1758 , TL.Currency_code
1759 ,tmp.old_unit_of_measure
1760 , KL.line_renewal_type_code
1761 , tmp.raise_credit
1762 , null
1763 , Null
1764 , Null
1765 , Null
1766 , Null
1767 , Null
1768 , null
1769 , Null
1770 , tmp.return_reason_code
1771 , tmp.order_line_id
1772 , null
1773 , null
1774 , null
1775 From OKC_K_ITEMS KI
1776 , OKC_K_HEADERS_ALL_B KH
1777 , OKC_K_LINES_B KL
1778 , OKC_STATUSES_B ST
1779 , OKS_INSTANCE_TEMP tmp
1780 , OKC_K_LINES_B TL
1781 ,OKC_K_ITEMS KIS
1782 Where tmp.idc = 'Y'
1783 And nvl(tmp.trm,'N') = 'N'
1784 And KI.Object1_id1 = to_char(tmp.old_customer_product_id)
1785 And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
1786 And KI.dnz_chr_id = KH.ID
1787 And KH.scs_code in ('WARRANTY')
1788 And KI.Cle_id = KL.id
1789 And TL.Id = KL.cle_id
1790 And KIS.cle_id = TL.id
1791 And KIS.dnz_chr_id = TL.dnz_chr_id
1792 And KL.sts_code = ST.code
1793 And ST.ste_code not in ('TERMINATED','CANCELLED')
1794 And KL.date_terminated Is Null
1795 And KH.template_yn = 'N'
1796 And KL.lse_id = 18;
1797
1798
1799 Cursor get_k_for_Spl_csr Is
1800 Select tmp.old_customer_product_id
1801 ,tmp.termination_date
1802 ,tmp.installation_date
1803 ,tmp.transaction_date
1804 ,tmp.old_Customer_acct_id
1805 ,tmp.new_customer_acct_id
1806 ,tmp.System_id
1807 ,tmp.old_quantity
1808 ,tmp.new_quantity
1809 ,tmp.new_customer_product_id
1810 ,KI.CLE_ID SubLine_id
1811 , KI.Dnz_Chr_Id
1812 ,KH.start_date hdr_sdt
1813 ,KH.end_date hdr_edt
1814 ,KH.sts_code hdr_sts
1815 , KL.Cle_Id
1816 ,KL.Price_negotiated
1817 , KL.Start_date
1818 , KL.end_date
1819 , KL.sts_code prod_sts
1820 , KL.Cust_acct_id
1821 , TL.start_date Srv_sdt
1822 , TL.end_date Srv_edt
1823 , KH.sts_code
1824 , KH.Contract_number
1825 , KI.number_of_items
1826 , TL.price_negotiated
1827 , KL.date_terminated
1828 ,tmp.old_inventory_item_id
1829 , KH.authoring_org_id
1830 ,KH.inv_organization_id
1831 ,KL.lse_id
1832 , KH.scs_code
1833 , tmp.new_customer_product_id
1834 , KIS.Object1_id1
1835 , TL.Currency_code
1836 ,tmp.old_unit_of_measure
1837 , KL.line_renewal_type_code
1838 , tmp.raise_credit
1839 , null
1840 , OKL.tax_amount
1841 , KL.Price_unit
1842 , KL.Name
1843 , KL.Item_description
1844 , KL.upg_orig_system_ref
1845 , KL.upg_orig_system_ref_id
1846 , tmp.new_inventory_item_id
1847 , tmp.return_reason_code
1848 , tmp.order_line_id
1849 , okl.price_uom
1850 , okl.toplvl_uom_code
1851 , okl.toplvl_price_qty
1852 From OKC_K_ITEMS KI
1853 , OKC_K_HEADERS_ALL_B KH
1854 , OKC_K_LINES_v KL
1855 , OKC_STATUSES_B ST
1856 , OKS_INSTANCE_TEMP tmp
1857 , OKC_K_LINES_B TL
1858 ,OKC_K_ITEMS KIS
1859 , OKS_K_LINES_B OKL
1860 Where tmp.spl = 'Y'
1861 And nvl(tmp.trm,'N') = 'N'
1862 And KI.Object1_id1 = to_char(tmp.old_customer_product_id)
1863 And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
1864 And KI.dnz_chr_id = KH.ID
1865 And KH.scs_code in ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
1866 And KI.Cle_id = KL.id
1867 And TL.Id = KL.cle_id
1868 And KIS.cle_id = TL.id
1869 And KIS.dnz_chr_id = TL.dnz_chr_id
1870 And KL.sts_code = ST.code
1871 And ST.ste_code not in ('TERMINATED','CANCELLED','HOLD')
1872 And KL.date_terminated Is Null
1873 And KH.template_yn = 'N'
1874 And OKL.cle_id = kl.id
1875 And ((trunc(tmp.transaction_date) <= trunc(KL.end_date)And trunc(tmp.transaction_date) >= trunc(KL.start_date))
1876 OR (trunc(tmp.transaction_date) <= trunc(kl.start_date)) )
1877 order by tmp.old_customer_product_id, kh.creation_date; --KI.Dnz_Chr_Id;
1878
1879
1880 Cursor get_k_for_rpl_csr Is
1881 Select tmp.old_customer_product_id Instance_id
1882 ,tmp.termination_date
1883 ,tmp.installation_date
1884 ,tmp.transaction_date
1885 ,tmp.old_Customer_acct_id
1886 ,tmp.new_customer_acct_id
1887 ,tmp.System_id
1888 ,tmp.old_quantity
1889 ,tmp.new_quantity
1890 ,tmp.new_customer_product_id
1891 ,KI.CLE_ID SubLine_id
1892 , KI.Dnz_Chr_Id
1893 ,KH.start_date hdr_sdt
1894 ,KH.end_date hdr_edt
1895 ,KH.sts_code hdr_sts
1896 , KL.Cle_Id
1897 ,KL.Price_negotiated
1898 , KL.Start_date
1899 , KL.end_date
1900 , KL.sts_code prod_sts
1901 , KL.Cust_acct_id
1902 , TL.start_date Srv_sdt
1903 , TL.end_date Srv_edt
1904 , KH.sts_code
1905 , KH.Contract_number
1906 , KI.number_of_items
1907 , TL.price_negotiated
1908 , KL.date_terminated
1909 , tmp.old_inventory_item_id
1910 , KH.authoring_org_id
1911 , KH.inv_organization_id
1912 , KL.lse_id
1913 , KH.scs_code
1914 , tmp.new_customer_product_id
1915 , KIS.Object1_id1
1916 , TL.Currency_code
1917 , tmp.old_unit_of_measure
1918 , KL.line_renewal_type_code
1919 , tmp.raise_credit
1920 , null
1921 , OKL.tax_amount
1922 , KL.Price_unit
1923 , KL.Name
1924 , KL.Item_description
1925 , KL.upg_orig_system_ref
1926 , KL.upg_orig_system_ref_id
1927 , tmp.new_inventory_item_id
1928 , tmp.return_reason_code
1929 , tmp.order_line_id
1930 , okl.price_uom
1931 , okl.toplvl_uom_code
1932 , okl.toplvl_price_qty
1933 From OKC_K_ITEMS KI
1934 , OKC_K_HEADERS_ALL_B KH
1935 , OKC_K_LINES_v KL
1936 , OKC_STATUSES_B ST
1937 , OKS_INSTANCE_TEMP tmp
1938 , OKC_K_LINES_B TL
1939 ,OKC_K_ITEMS KIS
1940 , OKS_K_LINES_B OKL
1941
1942 Where tmp.rpl = 'Y'
1943 And KI.Object1_id1 = to_char(tmp.old_customer_product_id)
1944 And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
1945 And KI.dnz_chr_id = KH.ID
1946 And KH.scs_code in ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
1947 And KI.Cle_id = KL.id
1948 And TL.Id = KL.cle_id
1949 And KIS.cle_id = TL.id
1950 And KIS.dnz_chr_id = TL.dnz_chr_id
1951 And KL.sts_code = ST.code
1952 And ST.ste_code not in ('TERMINATED','CANCELLED','HOLD')
1953 And KL.date_terminated Is Null
1954 And OKL.cle_id = kl.id
1955 And KH.template_yn = 'N'
1956 And ((trunc(tmp.transaction_date) <= trunc(KL.end_date)And trunc(tmp.transaction_date) >= trunc(KL.start_date))
1957 OR (trunc(tmp.transaction_date) <= trunc(kl.start_date)) )
1958 order by tmp.old_customer_product_id, kh.creation_date; -- KI.Dnz_Chr_Id;
1959
1960
1961 Cursor get_k_for_upd_csr Is
1962 Select tmp.old_customer_product_id Instance_id
1963 ,tmp.termination_date
1964 ,tmp.installation_date
1965 ,tmp.transaction_date
1966 ,tmp.old_Customer_acct_id
1967 ,tmp.new_customer_acct_id
1968 ,tmp.System_id
1969 ,KI.number_of_items
1970 ,tmp.new_quantity
1971 ,tmp.new_customer_product_id
1972 ,KI.CLE_ID SubLine_id
1973 , KI.Dnz_Chr_Id
1974 ,KH.start_date hdr_sdt
1975 ,KH.end_date hdr_edt
1976 ,KH.sts_code hdr_sts
1977 , KL.Cle_Id
1978 ,KL.Price_negotiated
1979 , KL.Start_date
1980 , KL.end_date
1981 , KL.sts_code prod_sts
1982 , KL.Cust_acct_id
1983 , TL.start_date Srv_sdt
1984 , TL.end_date Srv_edt
1985 , KH.sts_code
1986 , KH.Contract_number
1987 , KI.number_of_items
1988 , TL.price_negotiated
1989 , KL.date_terminated
1990 , tmp.old_inventory_item_id
1991 , KH.authoring_org_id
1992 , KH.inv_organization_id
1993 , KL.lse_id
1994 , KH.scs_code
1995 , tmp.old_customer_product_id
1996 , KIS.object1_id1
1997 , TL.Currency_code
1998 , tmp.old_unit_of_measure
1999 , KL.line_renewal_type_code
2000 , tmp.raise_credit
2001 , null
2002 , Null
2003 , Null
2004 , Null
2005 , Null
2006 , Null
2007 , Null
2008 , Null
2009 , tmp.return_reason_code
2010 , tmp.order_line_id
2011 , okl.price_uom
2012 , okl.toplvl_uom_code
2013 , okl.toplvl_price_qty
2014 From OKC_K_ITEMS KI
2015 , OKC_K_HEADERS_ALL_B KH
2016 , OKC_K_LINES_B KL
2017 , OKC_STATUSES_B ST
2018 , OKS_INSTANCE_TEMP tmp
2019 , OKC_K_LINES_B TL
2020 , OKC_K_ITEMS KIS
2021 , OKS_K_LINES_B OKL
2022 Where tmp.upd = 'Y'
2023 And Nvl(tmp.trm,'N') = 'N'
2024 And KI.Object1_id1 = to_char(tmp.old_customer_product_id)
2025 And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
2026 And KI.dnz_chr_id = KH.ID
2027 And KH.scs_code in ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
2028 And KI.Cle_id = KL.id
2029 And TL.Id = KL.cle_id
2030 And KIS.cle_id = TL.id
2031 And KIS.dnz_chr_id = TL.dnz_chr_id
2032 And KL.sts_code = ST.code
2033 And ST.ste_code not in ('TERMINATED','CANCELLED')
2034 And KL.date_terminated Is Null
2035 And KH.template_yn = 'N'
2036 AND OKL.cle_id = KI.cle_id
2037 And ((trunc(tmp.transaction_date) <= trunc(KL.end_date)And trunc(tmp.transaction_date) >= trunc(KL.start_date))
2038 OR (trunc(tmp.transaction_date) <= trunc(kl.start_date)) );
2039
2040 Cursor check_subscription_instance Is
2041 Select 'Y'
2042 From Oks_subscr_header_b oks, Oks_instance_temp tmp
2043 Where oks.instance_id = tmp.old_customer_product_id;
2044
2045 Cursor l_refnum_csr(p_cp_id NUMBER) IS
2046 select instance_number
2047 from csi_item_instances
2048 where instance_id = p_cp_id;
2049
2050 k_trf_tbl OKS_EXTWARPRGM_PVT.Contract_trf_tbl;
2051 k_trm_tbl OKS_EXTWARPRGM_PVT.Contract_tbl;
2052 k_ret_tbl OKS_EXTWARPRGM_PVT.Contract_tbl;
2053 k_idc_tbl OKS_EXTWARPRGM_PVT.Contract_tbl;
2054 k_spl_tbl OKS_EXTWARPRGM_PVT.Contract_tbl;
2055 k_rpl_tbl OKS_EXTWARPRGM_PVT.Contract_tbl;
2056 k_upd_tbl OKS_EXTWARPRGM_PVT.Contract_tbl;
2057
2058
2059 l_return_status Varchar2(1):= OKC_API.G_RET_STS_SUCCESS;
2060 l_inst_dtls_tbl OKS_IHD_PVT.ihdv_tbl_type ;
2061 l_subscr_instance Varchar2(1);
2062 l_ref_num Varchar2(30);
2063 l_temp Number;
2064 l_parameters Varchar2(2000);
2065 l_insthist_rec OKS_INS_PVT.insv_rec_type;
2066 x_insthist_rec OKS_INS_PVT.insv_rec_type;
2067 l_instparent_id Number;
2068 x_inst_dtls_tbl OKS_IHD_PVT.ihdv_tbl_type ;
2069
2070 l_access_mode Varchar2(10);
2071 l_org_id Number;
2072 l_process_status Varchar2(1);
2073 Begin
2074 /*
2075 FND_GLOBAL.APPS_INITIALIZE(1005214, 21708, 515);
2076 fnd_profile.put('AFLOG_ENABLED', 'Y');
2077 fnd_profile.put('AFLOG_MODULE', '%');
2078 fnd_profile.put('AFLOG_LEVEL', '1');
2079 fnd_profile.put('AFLOG_FILENAME', '');
2080 fnd_log_repository.init;
2081
2082 */
2083 x_return_status := l_return_status;
2084
2085 l_access_mode := MO_GLOBAL.Get_access_mode;
2086 l_org_id := MO_GLOBAL.Get_current_org_id;
2087
2088 MO_Global.set_policy_context('A', null);
2089
2090 Delete from Oks_Instance_temp;
2091
2092 FORALL i in P_oks_txn_inst_tbl.FIRST..P_oks_txn_inst_tbl.LAST
2093 INSERT INTO oks_instance_temp
2094 Values P_oks_txn_inst_tbl(i);
2095
2096 --If instance is a subscription instance throw an error
2097 Open check_subscription_instance;
2098 Fetch check_subscription_instance into l_subscr_instance;
2099 Close check_subscription_instance;
2100
2101 If l_subscr_instance = 'Y' Then
2102 l_return_status := OKC_API.G_RET_STS_ERROR;
2103 OKC_API.set_message(G_APP_NAME,'OKS_SUBSCRIPTION_INST_ERR');
2104 Raise G_EXCEPTION_HALT_VALIDATION;
2105
2106 End If;
2107 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2108 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2109 'Batch Id'||p_batch_id ||')' );
2110 End If;
2111 If p_batch_id Is not Null Then
2112
2113 OKS_MASS_UPDATE_PVT.update_contracts (
2114 p_api_version => 1.0,
2115 p_init_msg_list => 'T',
2116 p_batch_type => P_batch_type,
2117 p_batch_id => P_batch_id,
2118 p_new_acct_id => P_oks_txn_inst_tbl(1).New_Customer_acct_id,
2119 p_old_acct_id => P_oks_txn_inst_tbl(1).Old_Customer_acct_id,
2120 x_return_status => l_return_status,
2121 x_msg_count => x_msg_count,
2122 x_msg_data => x_msg_data
2123 );
2124
2125 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2126 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2127 'OKS_MASS_UPDATE.update_contracts(Return status = ' ||l_return_status );
2128 END IF;
2129
2130 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2131 x_return_status := l_return_status;
2132 Raise G_EXCEPTION_HALT_VALIDATION;
2133 End If;
2134 Else
2135
2136
2137
2138 --New Transaction
2139 -- Get all the instances for new transaction
2140 Open get_instances_for_new_csr;
2141 Fetch get_instances_for_new_csr Bulk Collect into instance_tbl;
2142 Close get_instances_for_new_csr;
2143 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2144 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2145 'Number of instances with transaction New =' ||instance_tbl.count);
2146 End If;
2147 If instance_tbl.count > 0 Then
2148 For i in 1..instance_tbl.count
2149 Loop
2150
2151 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2152
2153 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.IB_INTERFACE.Begin.parameters',
2154 'Transaction Type = '|| 'NEW'|| 'Transaction date = '|| instance_tbl(i).transaction_date
2155 ||'Bom explosion flag = '|| instance_tbl(i).bom_explosion_flag );
2156 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.IB_INTERFACE.Begin.parameter',
2157 'Old Org = '||instance_tbl(i).org_id||',Old Customer Product Id = '||instance_tbl(i).old_customer_product_id
2158 ||',Old Order Line Id = '||instance_tbl(i).order_line_id ||',Old Shipped Date = '|| instance_tbl(i).shipped_date
2159 ||',Old Bill To = '|| instance_tbl(i).bill_to_site_use_id ||',Old Ship To = '||instance_tbl(i).ship_to_site_use_id
2160 ||',Old Quantity = '|| instance_tbl(i).old_quantity || ',Old Unit Of Measurement = '|| instance_tbl(i).old_unit_of_measure
2161 ||',Old Inventory Item Id = '||instance_tbl(i).old_inventory_item_id || ',Old Customer acount Id = '||instance_tbl(i).old_customer_acct_id
2162 ||',Old Organization Id = ' ||instance_tbl(i).organization_id );
2163
2164 END IF;
2165
2166 Ib_new(
2167 P_instance_rec => instance_tbl(i),
2168 x_inst_dtls_tbl => l_inst_dtls_tbl,
2169 x_return_status => l_return_status,
2170 x_msg_count => x_msg_count,
2171 x_msg_data => x_msg_data
2172 );
2173
2174
2175
2176
2177 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2178 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2179 'Ib_new(Return status = '||l_return_status ||')' );
2180 End If;
2181 x_return_status := l_return_status;
2182 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2183 x_return_status := l_return_status;
2184 Raise G_EXCEPTION_HALT_VALIDATION;
2185 End if;
2186
2187 ------------------------------------------------------
2188 -- Inserting instance details into history and
2189 -- history details table
2190 ------------------------------------------------------
2191
2192 If l_inst_dtls_tbl.count = 0 OR l_return_status <> 'S' Then
2193
2194 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2195 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2196 'No records for history details or status invalid ' );
2197 End If;
2198 Null;
2199 Else
2200 OPEN l_refnum_csr (instance_tbl(i).old_customer_product_id);
2201 FETCH l_refnum_csr into l_ref_num;
2202 Close l_refnum_csr;
2203
2204 If instance_tbl(i).order_line_id = okc_api.g_miss_num Then
2205 l_temp := null;
2206 Else
2207 l_temp := instance_tbl(i).order_line_id ;
2208 End If;
2209
2210 l_parameters :=
2211 ' Org Id:' || instance_tbl(i).org_id ||',Old CP:' || instance_tbl(i).old_customer_product_id ||',Order line id:' || l_temp
2212 ||',Ship Date:' || instance_tbl(i).shipped_date ||',Bill to:' || instance_tbl(i).bill_to_site_use_id ||',Ship to:' || instance_tbl(i).ship_to_site_use_id
2213 ||',Old Qty:' || instance_tbl(i).old_quantity ||',UOM:' || instance_tbl(i).old_unit_of_measure ||',Old Inv id:' || instance_tbl(i).old_inventory_item_id
2214 ||',Old cust acct:'|| instance_tbl(i).old_customer_acct_id ||',Old Organization id:'|| instance_tbl(i).organization_id ||',Installation date:' || instance_tbl(i).installation_date
2215 || ',Trxn type:' || 'NEW'
2216 ||',Trxn date:' || instance_tbl(i).transaction_date
2217 ||',Bom Expl flag:'|| instance_tbl(i).bom_explosion_flag ;
2218
2219 --oks_instance_history
2220 l_insthist_rec.INSTANCE_ID := instance_tbl(i).old_customer_product_id;
2221 l_insthist_rec.TRANSACTION_TYPE := 'NEW';
2222 l_insthist_rec.TRANSACTION_DATE := instance_tbl(i).transaction_date ;
2223 l_insthist_rec.REFERENCE_NUMBER := l_ref_num;
2224 l_insthist_rec.parameters := l_parameters;
2225
2226
2227 OKS_INS_PVT.insert_row(
2228 p_api_version => 1.0,
2229 p_init_msg_list => 'T',
2230 x_return_status => l_return_status,
2231 x_msg_count => x_msg_count,
2232 x_msg_data => x_msg_data,
2233 p_insv_rec => l_insthist_rec,
2234 x_insv_rec => x_insthist_rec
2235 );
2236 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2237 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE.instance history',
2238 'oks_ins_pvt.insert_row(Return status = '||l_return_status ||')' );
2239 End If;
2240 x_return_status := l_return_status;
2241 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2242 x_return_status := l_return_status;
2243 Raise G_EXCEPTION_HALT_VALIDATION;
2244 End if;
2245 l_instparent_id := x_insthist_rec.id;
2246
2247 For l_ctr in 1..l_inst_dtls_tbl.count
2248 Loop
2249 l_inst_dtls_tbl(l_ctr).ins_id := l_instparent_id;
2250 End loop;
2251
2252 --oks_inst_history_details
2253 OKS_IHD_PVT.insert_row(
2254 p_api_version => 1.0 ,
2255 p_init_msg_list => 'T',
2256 x_return_status => l_return_status,
2257 x_msg_count => x_msg_count,
2258 x_msg_data => x_msg_data,
2259 p_ihdv_tbl => l_inst_dtls_tbl,
2260 x_ihdv_tbl => x_inst_dtls_tbl
2261 );
2262 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2263 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE.instance history details',
2264 'oks_ihd_pvt.insert_row(Return status = '||l_return_status ||')' );
2265 End If;
2266 x_return_status := l_return_status;
2267 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2268 x_return_status := l_return_status;
2269 Raise G_EXCEPTION_HALT_VALIDATION;
2270 End if;
2271
2272 End If;
2273
2274
2275
2276 End Loop;
2277 End If;
2278
2279 -- Transfer transaction
2280 Open get_Contracts_for_transfer_csr;
2281 Fetch get_Contracts_for_transfer_csr Bulk Collect into k_trf_tbl;
2282 Close get_Contracts_for_transfer_csr;
2283 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2284 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2285 'Number of instances with transaction Transfer=' ||k_trf_tbl.count);
2286 End If;
2287
2288
2289
2290 If K_trf_tbl.count > 0 Then
2291
2292
2293 OKS_EXTWARPRGM_PVT .Create_k_System_TRANSFER
2294 (
2295 p_kdtl_tbl => k_trf_tbl,
2296 x_return_status => l_return_status,
2297 x_msg_count => x_msg_count,
2298 x_msg_data => x_msg_data
2299 );
2300 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2301 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2302 'oks_extwarprgm_pvt .create_k_system_transfer(Return status = '||l_return_status ||')' );
2303 End If;
2304
2305 x_return_status := l_return_status;
2306 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2307 x_return_status := l_return_status;
2308 Raise G_EXCEPTION_HALT_VALIDATION;
2309 End if;
2310
2311 End If;
2312
2313 -- Terminate transaction
2314 Open get_k_for_trm_csr;
2315 Fetch get_k_for_trm_csr Bulk Collect into k_trm_tbl;
2316 Close get_k_for_trm_csr;
2317 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2318 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2319 'Number of instances with transaction Terminate=' ||k_trm_tbl.count);
2320 End If;
2321
2322
2323 If K_trm_tbl.count > 0 Then
2324
2325 OKS_EXTWARPRGM_PVT.Create_COntract_Terminate
2326 (
2327 p_kdtl_tbl => k_trm_tbl,
2328 x_return_status => l_return_status,
2329 x_msg_count => x_msg_count,
2330 x_msg_data => x_msg_data
2331 );
2332 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2333 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2334 'Create_contract_terminate(Return status = '||l_return_status ||')' );
2335 End If;
2336 x_return_status := l_return_status;
2337 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2338 x_return_status := l_return_status;
2339 Raise G_EXCEPTION_HALT_VALIDATION;
2340 End if;
2341 End If;
2342 -- Return transaction
2343
2344 Open get_k_for_ret_csr;
2345 Fetch get_k_for_ret_csr Bulk Collect into k_ret_tbl;
2346 Close get_k_for_ret_csr;
2347
2348 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2349 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2350 'Number of instances with transaction Return=' ||k_ret_tbl.count);
2351 End If;
2352
2353 --errorout_n('in return count'||K_ret_tbl.count);
2354
2355 If K_ret_tbl.count > 0 Then
2356
2357 OKS_EXTWARPRGM_PVT.Create_Contract_IBReturn
2358 (
2359 p_kdtl_tbl => k_ret_tbl,
2360 x_return_status => l_return_status,
2361 x_msg_count => x_msg_count,
2362 x_msg_data => x_msg_data
2363 );
2364
2365 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2366 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2367 'Create_Contract_IBreturn(Return status = '||l_return_status ||')' );
2368 End If;
2369 x_return_status := l_return_status;
2370 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2371 x_return_status := l_return_status;
2372 Raise G_EXCEPTION_HALT_VALIDATION;
2373 End if;
2374 End If;
2375 -- Update transaction
2376 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2377 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2378 'Instance quantity Update Profile=' ||fnd_profile.value('OKS_INSTANCE_QUANTITY_UPDATE'));
2379 End If;
2380
2381 If NVL(fnd_profile.value('OKS_INSTANCE_QUANTITY_UPDATE'),'N') = 'Y' Then
2382 Open get_k_for_upd_csr;
2383 Fetch get_k_for_upd_csr Bulk Collect into k_upd_tbl;
2384 Close get_k_for_upd_csr;
2385
2386 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2387 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2388 'Number of instances with transaction Update=' ||k_upd_tbl.count);
2389 End If;
2390
2391
2392 If K_upd_tbl.count > 0 Then
2393
2394 OKS_EXTWARPRGM_PVT.Create_Contract_IBupdate
2395 (
2396 p_kdtl_tbl => k_upd_tbl,
2397 x_return_status => l_return_status,
2398 x_msg_count => x_msg_count,
2399 x_msg_data => x_msg_data
2400 );
2401 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2402 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2403 'Create_contract_IBupdate(Return status = '||l_return_status ||')' );
2404 End If;
2405 x_return_status := l_return_status;
2406 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2407 x_return_status := l_return_status;
2408 Raise G_EXCEPTION_HALT_VALIDATION;
2409 End if;
2410 End If;
2411
2412 End If;
2413
2414 -- IDC transaction
2415
2416 Open get_k_for_idc_csr;
2417 Fetch get_k_for_idc_csr Bulk Collect into k_idc_tbl;
2418 Close get_k_for_idc_csr;
2419 --errorout_n('idc'||k_idc_tbl.count );
2420
2421 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2422 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2423 'Number of instances with transaction Idc=' ||k_idc_tbl.count);
2424 End If;
2425
2426
2427 If K_idc_tbl.count > 0 Then
2428 OKS_EXTWARPRGM_PVT.Update_Contract_IDC
2429 (
2430 p_kdtl_tbl => k_idc_tbl,
2431 x_return_status => l_return_status,
2432 x_msg_count => x_msg_count,
2433 x_msg_data => x_msg_data
2434 );
2435 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2436 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2437 'Update_contract_idc(Return status = '||l_return_status ||')' );
2438 End If;
2439 x_return_status := l_return_status;
2440
2441 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2442 x_return_status := l_return_status;
2443 Raise G_EXCEPTION_HALT_VALIDATION;
2444 End if;
2445
2446 End If;
2447
2448
2449 -- Split transaction
2450
2451 Open get_k_for_spl_csr;
2452 Fetch get_k_for_spl_csr Bulk Collect into k_spl_tbl;
2453 Close get_k_for_spl_csr;
2454 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2455 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2456 'Number of instances with transaction Split=' ||k_spl_tbl.count);
2457 End If;
2458
2459
2460 If K_spl_tbl.count > 0 Then
2461
2462 OKS_EXTWARPRGM_PVT.Create_Contract_IBSPLIT
2463 (
2464 p_kdtl_tbl => k_spl_tbl,
2465 x_return_status => l_return_status,
2466 x_msg_count => x_msg_count,
2467 x_msg_data => x_msg_data
2468 );
2469 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2470 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2471 'Create_contract_ibsplit(Return status = '||l_return_status ||')' );
2472 End If;
2473 x_return_status := l_return_status;
2474 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2475 x_return_status := l_return_status;
2476 Raise G_EXCEPTION_HALT_VALIDATION;
2477 End if;
2478 End If;
2479
2480 -- Replace transaction
2481
2482 Open get_k_for_rpl_csr;
2483 Fetch get_k_for_rpl_csr Bulk Collect into k_rpl_tbl;
2484 Close get_k_for_rpl_csr;
2485
2486 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2487 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2488 'Number of instances with transaction Replace=' ||k_rpl_tbl.count);
2489 End If;
2490
2491
2492
2493 If K_rpl_tbl.count > 0 Then
2494 --Call out to Pre-Integration
2495 --This is done as part of License Migration
2496 --Call out starts here
2497 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2498 THEN
2499 fnd_log.string(FND_LOG.LEVEL_STATEMENT
2500 ,G_MODULE_CURRENT||'.IB_INTERFACE'
2501 ,'Before OKS_OMIB_EXTNS_PUB.pre_integration call: ' ||
2502 ' ,p_api_version = '|| p_api_version ||
2503 ' ,p_init_msg_list = ' || p_init_msg_list ||
2504 ' ,p_from_integration = IBINT' ||
2505 ' ,p_transaction_type = ' || 'RPL' ||
2506 ' ,p_transaction_date = ' || K_rpl_tbl(1).transaction_date||
2507 ' ,p_order_line_id = ' || K_rpl_tbl(1).order_line_id ||
2508 ' ,p_old_instance_id = ' || K_rpl_tbl(1).old_cp_id ||
2509 ' ,p_new_instance_id = ' || K_rpl_tbl(1).new_cp_id);
2510 END IF;
2511
2512
2513 OKS_OMIB_INT_EXTNS_PUB.pre_integration
2514 (p_api_version => 1.0
2515 ,p_init_msg_list => 'T'
2516 ,p_from_integration => 'IBINT'
2517 ,p_transaction_type => 'RPL'
2518 ,p_transaction_date => K_rpl_tbl(1).transaction_date
2519 ,p_order_line_id => K_rpl_tbl(1).order_line_id
2520 ,p_old_instance_id => K_rpl_tbl(1).old_cp_id
2521 ,p_new_instance_id => K_rpl_tbl(1).new_cp_id
2522 ,x_process_status => l_process_status
2523 ,x_return_status => x_return_status
2524 ,x_msg_count => x_msg_count
2525 ,x_msg_data => x_msg_data);
2526
2527 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
2528 THEN
2529 fnd_log.string(FND_LOG.LEVEL_EVENT
2530 ,G_MODULE_CURRENT||'.IB_INTERFACE'
2531 ,'After OKS_OMIB_INT_EXTNS_PUB.pre_integration Call: ' ||
2532 ' ,x_process_status = ' || l_process_status ||
2533 ' ,x_return_status = ' || x_return_status);
2534 END IF;
2535 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2536 THEN
2537 RAISE G_EXCEPTION_HALT_VALIDATION;
2538 END IF;
2539 --Call out ends here
2540 IF l_process_status = 'C' THEN
2541
2542 OKS_EXTWARPRGM_PVT.Update_Contract_IBReplace
2543 (
2544 p_kdtl_tbl => k_rpl_tbl,
2545 x_return_status => l_return_status,
2546 x_msg_count => x_msg_count,
2547 x_msg_data => x_msg_data
2548 );
2549 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2550 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_INTERFACE',
2551 'Update_Contract_IBReplace(Return status = '||l_return_status ||')' );
2552 End If;
2553 x_return_status := l_return_status;
2554 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2555 x_return_status := l_return_status;
2556 Raise G_EXCEPTION_HALT_VALIDATION;
2557 End if;
2558 End If;
2559 --Call out to post integration starts here
2560 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2561 THEN
2562 fnd_log.string(FND_LOG.LEVEL_STATEMENT
2563 ,G_MODULE_CURRENT||'.IB_INTERFACE'
2564 ,'Before OKS_OMIB_EXTNS_PUB.post_integration call: ' ||
2565 ' ,p_transaction_type = ' || 'RPL'||
2566 ' ,p_transaction_date = ' || K_rpl_tbl(1).transaction_date||
2567 ' ,p_order_line_id = ' || K_rpl_tbl(1).order_line_id ||
2568 ' ,p_old_instance_id = ' || K_rpl_tbl(1).old_cp_id ||
2569 ' ,p_new_instance_id = ' || K_rpl_tbl(1).new_cp_id ||
2570 ' ,p_chr_id = ' || NULL ||
2571 ' ,p_topline_id = ' || NULL ||
2572 ' ,p_subline_id = ' || NULL);
2573 END IF;
2574 OKS_OMIB_INT_EXTNS_PUB.post_integration
2575 (p_api_version => 1.0
2576 ,p_init_msg_list => 'T'
2577 ,p_from_integration => 'IBINT'
2578 ,p_transaction_type => 'RPL'
2579 ,p_transaction_date => K_rpl_tbl(1).transaction_date
2580 ,p_order_line_id => K_rpl_tbl(1).order_line_id
2581 ,p_old_instance_id => K_rpl_tbl(1).old_cp_id
2582 ,p_new_instance_id => K_rpl_tbl(1).new_cp_id
2583 ,p_chr_id => NULL
2584 ,p_topline_id => NULL
2585 ,p_subline_id => NULL
2586 ,x_return_status => x_return_status
2587 ,x_msg_count => x_msg_count
2588 ,x_msg_data => x_msg_data);
2589 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
2590 THEN
2591 fnd_log.string(FND_LOG.LEVEL_EVENT
2592 ,G_MODULE_CURRENT||'.IB_INTERFACE'
2593 ,'After OKS_OMIB_INT_EXTNS_PUB.post_integration Call: ' ||
2594 ' ,x_return_status = ' || x_return_status);
2595 END IF;
2596 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2597 THEN
2598 RAISE G_EXCEPTION_HALT_VALIDATION;
2599 END IF;
2600
2601
2602
2603
2604 End If;
2605 End If;
2606 --Reset the policy context
2607 MO_GLOBAL.set_policy_context(l_access_mode,l_org_id);
2608 x_return_status := l_return_status;
2609
2610 Exception
2611 When G_EXCEPTION_HALT_VALIDATION Then
2612 x_return_status := l_return_status;
2613 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2614 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.IB_INTERFACE.UNEXPECTED',
2615 'Return status = '||x_return_status );
2616 END IF;
2617 --Fix for bug 4947476
2618 FND_MSG_PUB.Count_And_Get
2619 (
2620 p_count => x_msg_count,
2621 p_data => x_msg_data
2622 );
2623
2624 When Others Then
2625 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2626 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2627 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.IB_INTERFACE.UNEXPECTED',
2628 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
2629 END IF;
2630 OKC_API.set_message(G_APP_NAME,G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE,G_SQLERRM_TOKEN, SQLERRM);
2631 --Fix for bug 4947476
2632 FND_MSG_PUB.Count_And_Get
2633 (
2634 p_count => x_msg_count,
2635 p_data => x_msg_data
2636 );
2637
2638 End;
2639
2640 -- Procedure to delete a batch.
2641 procedure delete_batch
2642 (
2643 P_Api_Version IN NUMBER,
2644 P_init_msg_list IN VARCHAR2,
2645 P_Batch_ID IN NUMBER,
2646 x_return_status OUT NOCOPY VARCHAR2,
2647 x_msg_count OUT NOCOPY NUMBER,
2648 x_msg_data OUT NOCOPY VARCHAR2)
2649 Is
2650
2651 Begin
2652
2653 x_return_status := 'S';
2654 DELETE FROM OKS_BATCH_RULES
2655 WHERE batch_ID = P_Batch_ID;
2656 Exception
2657 When Others Then
2658 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2659 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2660 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.IB_INTERFACE.UNEXPECTED',
2661 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
2662 END IF;
2663 OKC_API.set_message(G_APP_NAME,G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE,G_SQLERRM_TOKEN, SQLERRM);
2664
2665 End;
2666
2667 -- Procedure to validate batch rules before submitting
2668 -- the batch for processing.
2669
2670 procedure Validate_new_owner
2671 (
2672 P_Api_Version IN NUMBER,
2673 P_init_msg_list IN VARCHAR2,
2674 P_Batch_ID IN NUMBER,
2675 P_new_owner_id IN NUMBER,
2676 x_return_status OUT NOCOPY VARCHAR2,
2677 x_msg_count OUT NOCOPY NUMBER,
2678 x_msg_data OUT NOCOPY VARCHAR2)
2679 IS
2680
2681 Cursor l_check_csr IS
2682 SELECT 'x'
2683 FROM oks_batch_rules
2684 WHERE batch_id = p_batch_id
2685 AND NVL(new_account_id, p_new_owner_id) = p_new_owner_id;
2686
2687 l_dummy_var VARCHAR2(1) := '?';
2688
2689 BEGIN
2690 x_return_status := 'S';
2691 IF p_new_owner_id IS NOT NULL THEN
2692 OPEN l_check_csr;
2693 FETCH l_check_csr INTO l_dummy_var;
2694 CLOSE l_check_csr;
2695
2696 IF l_dummy_var = '?'
2697 THEN
2698 x_return_status := OKC_API.G_RET_STS_ERROR;
2699 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2700 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
2701 'Accounts mismatch ' ||p_batch_id ||'Account id'|| p_new_owner_id );
2702 END IF;
2703 OKC_API.set_message(G_APP_NAME, 'OKS_BATCH_RULES_MISMATCH');
2704 END IF;
2705 ELSE
2706 IF p_batch_id IS NOT NULL
2707 THEN
2708 delete_batch(
2709 P_Api_Version => p_api_version,
2710 P_init_msg_list => p_init_msg_list,
2711 P_Batch_ID => p_batch_id,
2712 x_return_status => x_return_status,
2713 x_msg_count => x_msg_count,
2714 x_msg_data => x_msg_data);
2715
2716 If x_return_status <> OKC_API.G_RET_STS_SUCCESS
2717 THEN
2718 x_return_status := OKC_API.G_RET_STS_ERROR;
2719 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2720 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.IB_NEW',
2721 'Error while deleting the batch ' ||p_batch_id);
2722 END IF;
2723 END IF;
2724 END IF;
2725 END IF;
2726 END;
2727
2728
2729 -- Procedure to create batch rules
2730 procedure create_batch_rules
2731 (
2732 P_Api_Version IN NUMBER,
2733 P_init_msg_list IN VARCHAR2,
2734 P_Batch_ID IN NUMBER,
2735 p_batch_type IN VARCHAR2,
2736 x_return_status OUT NOCOPY VARCHAR2,
2737 x_msg_count OUT NOCOPY NUMBER,
2738 x_msg_data OUT NOCOPY VARCHAR2)
2739 Is
2740 l_batch_rule oks_brl_pvt.oks_batch_rules_v_rec_type;
2741 x_batch_rule oks_brl_pvt.oks_batch_rules_v_rec_type;
2742 l_reason_type VARCHAR2(3);
2743 l_return_status VARCHAR2(1) := 'S';
2744 begin
2745
2746 IF p_batch_type = 'XFER'
2747 THEN
2748 l_reason_type := 'TRF';
2749 ELSE
2750 l_reason_type := 'EXP';
2751 END IF;
2752
2753 l_batch_rule.batch_id := P_Batch_ID;
2754 l_batch_rule.batch_type := p_batch_type;
2755 l_batch_rule.batch_source := 'IB';
2756 l_batch_rule.transaction_date := SYSDATE;
2757 l_batch_rule.credit_option := NULL ;
2758 l_batch_rule.termination_reason_code := l_reason_type ;
2759 l_batch_rule.billing_profile_id := NULL ;
2760 l_batch_rule.retain_contract_number_flag := 'N' ;
2761 l_batch_rule.contract_modifier := NULL ;
2762 l_batch_rule.contract_status := NVL(fnd_profile.value('OKS_TRANSFER_STATUS'),'ENTERED') ;
2763 l_batch_rule.transfer_notes_flag := 'N';
2764 l_batch_rule.transfer_attachments_flag := 'N';
2765 l_batch_rule.bill_lines_flag := 'Y' ;
2766 l_batch_rule.transfer_option_code := 'COVERAGE' ;
2767 l_batch_rule.bill_account_id := NULL ;
2768 l_batch_rule.ship_account_id := NULL ;
2769 l_batch_rule.bill_address_id := NULL ;
2770 l_batch_rule.ship_address_id := NULL ;
2771 l_batch_rule.bill_contact_id := NULL ;
2772 l_batch_rule.new_account_id := NULL ;
2773 l_batch_rule.object_version_number := 1 ;
2774
2775 oks_brl_pvt.insert_row(
2776 p_api_version => p_api_version,
2777 p_init_msg_list => p_init_msg_list,
2778 x_return_status => l_return_status,
2779 x_msg_count => x_msg_count,
2780 x_msg_data => x_msg_data,
2781 p_oks_batch_rules_v_rec => l_batch_rule,
2782 x_oks_batch_rules_v_rec => x_batch_rule);
2783
2784 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2785 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.CREATE_BATCH',
2786 'oks_brl_pvt.insert_row(Return status = '|| l_return_status || ')');
2787 END IF;
2788
2789 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2790 x_return_status := l_return_status;
2791 Raise G_EXCEPTION_HALT_VALIDATION;
2792 End if;
2793 x_return_status := l_return_status;
2794 Exception
2795 When G_EXCEPTION_HALT_VALIDATION Then
2796 x_return_status := l_return_status;
2797 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2798 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.CARETE_BATCH_RULES.UNEXPECTED',
2799 'No Batch rules created');
2800 END IF;
2801
2802 When Others Then
2803 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2804 OKC_API.set_message(G_APP_NAME,G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE,G_SQLERRM_TOKEN, SQLERRM);
2805 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2806 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.CARETE_BATCH_RULES.UNEXPECTED',
2807 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
2808 END IF;
2809 End;
2810
2811 FUNCTION CHECK_SUBSCR_INSTANCE( p_instance_id NUMBER)
2812 RETURN VARCHAR2 IS
2813
2814 Cursor l_subscr_csr Is
2815 Select 'Y' subscr_instance
2816 From Oks_subscr_header_b
2817 Where instance_id = p_instance_id
2818 And rownum < 2;
2819
2820 l_subscr_instance VARCHAR2(1):= 'N';
2821 BEGIN
2822 FOR subcr_rec in l_subscr_csr LOOP
2823 l_subscr_instance := subcr_rec.subscr_instance;
2824 END LOOP;
2825 RETURN l_subscr_instance;
2826 END CHECK_SUBSCR_INSTANCE;
2827
2828 PROCEDURE POPULATE_CHILD_INSTANCES (p_api_version IN Number,
2829 p_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
2830 p_instance_id IN NUMBER,
2831 p_transaction_type IN VARCHAR2,
2832 x_msg_Count OUT NOCOPY Number,
2833 x_msg_Data OUT NOCOPY Varchar2,
2834 x_return_status OUT NOCOPY Varchar2)
2835 IS
2836
2837 l_txn_rec CSI_UTILITY_GRP.txn_oks_rec;
2838 l_txn_inst_tbl CSI_UTILITY_GRP.txn_inst_tbl;
2839 i number := 1;
2840
2841 BEGIN
2842 --Call IB API to get child instances
2843 l_txn_rec.transaction_type(1) := p_transaction_type;
2844 l_txn_rec.instance_id := p_instance_id;
2845
2846 CSI_UTILITY_GRP.Get_impacted_item_instances
2847 (
2848 p_api_version => p_api_version
2849 ,p_commit => okc_api.g_false
2850 ,p_init_msg_list => p_init_msg_list
2851 ,p_validation_level => fnd_api.g_valid_level_full
2852 ,x_txn_inst_tbl => l_txn_inst_tbl
2853 ,p_txn_oks_rec => l_txn_rec
2854 ,x_return_status => x_return_status
2855 ,x_msg_count => x_msg_Count
2856 ,x_msg_data => x_msg_Data
2857 );
2858
2859
2860 -- Delete all the rows in temp table
2861
2862 delete from oks_instance_k_dtls_temp;
2863
2864
2865 --insert all the instances returned returned by IB API
2866
2867 INSERT INTO oks_instance_k_dtls_temp (parent_id,instance_id)
2868 values(p_instance_id,p_instance_id);
2869
2870 /* FORALL i in 1..l_txn_inst_tbl(1).instance_tbl.count */
2871 /* cgopinee bugfix for bug 9289886*/
2872 FORALL i IN indices OF l_txn_inst_tbl(1).instance_tbl
2873 INSERT INTO oks_instance_k_dtls_temp (parent_id,instance_id)
2874 values(p_instance_id,l_txn_inst_tbl(1).instance_tbl(i));
2875
2876 END POPULATE_CHILD_INSTANCES;
2877
2878
2879 PROCEDURE GET_CONTRACTS(p_api_version IN Number,
2880 p_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
2881 p_instance_id IN NUMBER,
2882 p_validate_yn IN VARCHAR2 ,
2883 x_msg_Count OUT NOCOPY Number,
2884 x_msg_Data OUT NOCOPY Varchar2,
2885 x_return_status OUT NOCOPY Varchar2)
2886 IS
2887
2888 l_api_name CONSTANT VARCHAR2(30) := 'Get_Coverage_For_Prod_Sch';
2889 l_count NUMBER := 0;
2890 l_flag VARCHAR2(2);
2891 l_rec_count NUMBER := 1;
2892 l_return_status VARCHAR2(1);
2893 l_ent_contracts OKS_ENTITLEMENTS_PUB.output_tbl_ib;
2894 l_inp_rec OKS_ENTITLEMENTS_PUB.input_rec_ib;
2895 i NUMBER := 1;
2896 j NUMBER := 1;
2897 x NUMBER := 1;
2898
2899 k_number_tbl VAR120_TBL_TYPE ;
2900 k_modifier_tbl VAR120_TBL_TYPE ;
2901 k_id_tbl num_TBL_TYPe;
2902 k_line_num_tbl VAR150_TBL_TYPE ;
2903 k_line_id_tbl num_TBL_TYPe;
2904 k_cov_level_type_tbl VAR300_TBL_TYPE;
2905 k_cov_level_name_tbl VAR300_TBL_TYPE;
2906 k_serv_type_tbl VAR300_TBL_TYPE;
2907 k_serv_name_tbl VAR300_TBL_TYPE;
2908 k_serv_line_id num_TBL_TYPe;
2909 k_line_status VAR90_TBL_TYPE;
2910 k_line_start_date DATE_TBL_TYPE;
2911 k_line_end_date DATE_TBL_TYPE;
2912 k_line_amount num_TBL_TYPe;
2913 k_line_curr VAR150_TBL_TYPE ;
2914 k_line_trm_date DATE_TBL_TYPE;
2915
2916
2917 Cursor line_details(p_covered_line_id VARCHAR2, p_service_line_id VARCHAR2) IS
2918 Select tl.line_number|| '. '||sl.line_number line_number,
2919 lst.name cov_level_type,
2920 oks_ib_util_pvt.get_covlvl_name(sli.jtot_object1_code,
2921 sli.object1_id1,
2922 sli.object1_id2
2923 ) cov_level_name,
2924 tlst.name service_type,
2925 sts.meaning status,
2926 sl.start_date,
2927 sl.end_date,
2928 (NVL(sl.price_negotiated,0)+NVL(ksl.tax_amount,0)) price_negotiated,
2929 sl.currency_code ,
2930 sl.date_terminated
2931 from okc_k_lines_b sl,
2932 okc_k_lines_b tl,
2933 okc_statuses_v sts,
2934 okc_k_items sli,
2935 okc_line_styles_v lst,
2936 okc_line_styles_v tlst,
2937 oks_k_lines_b ksl
2938 where sl.id = p_covered_line_id
2939 and sl.sts_code=sts.code
2940 and sl.lse_id = lst.id
2941 and tl.lse_id = tlst.id
2942 and sl.id = sli.cle_id
2943 and ksl.cle_id = sl.id
2944 and tl.id = p_service_line_id;
2945
2946
2947 BEGIN
2948
2949 --call entitlements api to get the contracts
2950 l_inp_rec.product_id := p_instance_id;
2951 l_inp_rec.validate_flag := nvl(p_validate_yn,'N');
2952
2953 OKS_ENTITLEMENTS_PUB.GET_CONTRACTS( p_api_version => 1.0,
2954 p_init_msg_list => 'T',
2955 p_inp_rec => l_inp_rec,
2956 x_return_status => l_return_status,
2957 x_msg_count => x_msg_count,
2958 x_msg_data => x_msg_data,
2959 x_ent_contracts => l_ent_contracts);
2960 i:= l_ent_contracts.FIRST;
2961
2962 WHILE i is not null LOOP
2963 OPEN line_details(l_ent_contracts(i).CovLvl_Line_Id,l_ent_contracts(i).service_line_id);
2964 FETCH line_details into
2965 k_line_num_tbl(j),k_cov_level_type_tbl(j),k_cov_level_name_tbl(j),k_serv_type_tbl(j),
2966 k_line_status(j),k_line_start_date(j),k_line_end_date(j),k_line_amount(j),k_line_curr(j),
2967 k_line_trm_date(j);
2968
2969 CLOSE line_details;
2970
2971 k_number_tbl(j) := l_ent_contracts(i).contract_number;
2972 k_modifier_tbl(j) := l_ent_contracts(i).contract_number_modifier;
2973 k_id_tbl(j) := l_ent_contracts(i).contract_id;
2974 k_line_id_tbl(j) := l_ent_contracts(i).CovLvl_Line_Id;
2975 --304974183043424478303663115769271900942;
2976 /* -- 9791220 --start */
2977 IF NVL(Fnd_Profile.Value('OKS_LINE_MIRR_NAME_OR_DESC'), 'DISPLAY_NAME')= 'DISPLAY_DESC' THEN
2978 k_serv_name_tbl(j) := l_ent_contracts(i).service_description;
2979 ELSE
2980 k_serv_name_tbl(j) := l_ent_contracts(i).service_name;
2981 END IF;
2982 /* -- 9791220 --end */
2983 k_serv_line_id(j) := l_ent_contracts(i).service_line_id;
2984
2985 i :=l_ent_contracts.next(i);
2986 j:=j+1;
2987 END LOOP;
2988 --delete records in the table
2989 delete OKS_INSTANCE_CONTRACTS_TEMP;
2990 --insert into table
2991 FORALL j in 1..l_ent_contracts.count
2992 INSERT INTO OKS_INSTANCE_CONTRACTS_TEMP
2993 (CONTRACT_NUMBER ,
2994 CONTRACT_NUMBER_MODIFIER ,
2995 CHR_ID ,
2996 LINE_NUMBER ,
2997 COVERED_LINE_ID ,
2998 COVERED_LEVEL_TYPE ,
2999 COVERED_LEVEL_NAME ,
3000 SERVICE_TYPE ,
3001 SERVICE_NAME ,
3002 SERVICE_LINE_ID ,
3003 STATUS_MEANING ,
3004 START_DATE ,
3005 END_DATE ,
3006 AMOUNT ,
3007 CURRENCY_CODE ,
3008 DATE_TERMINATED)
3009 VALUES
3010 ( k_number_tbl(j) ,
3011 k_modifier_tbl(j) ,
3012 k_id_tbl(j) ,
3013 k_line_num_tbl(j) ,
3014 k_line_id_tbl(j) ,
3015 k_cov_level_type_tbl(j) ,
3016 k_cov_level_name_tbl(j) ,
3017 k_serv_type_tbl (j) ,
3018 k_serv_name_tbl(j) ,
3019 k_serv_line_id(j) ,
3020 k_line_status(j) ,
3021 k_line_start_date(j) ,
3022 k_line_end_date(j) ,
3023 k_line_amount(j) ,
3024 k_line_curr(j) ,
3025 k_line_trm_date(j) );
3026
3027 commit;
3028
3029 END GET_CONTRACTS ;
3030
3031 PROCEDURE create_item_instance
3032 (
3033 p_api_version IN NUMBER,
3034 p_commit IN VARCHAR2,
3035 p_init_msg_list IN VARCHAR2,
3036 p_validation_level IN NUMBER,
3037 p_instance_rec IN OUT NOCOPY instance_rec,
3038 x_return_status OUT NOCOPY VARCHAR2,
3039 x_msg_count OUT NOCOPY NUMBER,
3040 x_msg_data OUT NOCOPY VARCHAR2
3041 ) IS
3042
3043 lp_instance_rec csi_datastructures_pub.instance_rec;
3044 lp_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
3045 lp_party_tbl csi_datastructures_pub.party_tbl;
3046 lp_account_tbl csi_datastructures_pub.party_account_tbl;
3047 lp_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
3048 lp_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
3049 lp_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
3050 lp_txn_rec csi_datastructures_pub.transaction_rec;
3051
3052 BEGIN
3053
3054 --Assign instance information
3055 lp_instance_rec.EXTERNAL_REFERENCE := p_instance_rec.EXTERNAL_REFERENCE;
3056 lp_instance_rec.INVENTORY_ITEM_ID := p_instance_rec.INVENTORY_ITEM_ID;
3057 lp_instance_rec.VLD_ORGANIZATION_ID := p_instance_rec.VLD_ORGANIZATION_ID;
3058 lp_instance_rec.INVENTORY_REVISION := p_instance_rec.INVENTORY_REVISION;
3059 lp_instance_rec.SERIAL_NUMBER := p_instance_rec.SERIAL_NUMBER;
3060 lp_instance_rec.LOT_NUMBER := p_instance_rec.LOT_NUMBER;
3061 lp_instance_rec.QUANTITY := p_instance_rec.QUANTITY;
3062 lp_instance_rec.UNIT_OF_MEASURE := p_instance_rec.UNIT_OF_MEASURE;
3063 lp_instance_rec.ACTIVE_START_DATE := p_instance_rec.ACTIVE_START_DATE;
3064 lp_instance_rec.LOCATION_TYPE_CODE := p_instance_rec.LOCATION_TYPE_CODE;
3065 lp_instance_rec.LOCATION_ID := p_instance_rec.LOCATION_ID;
3066 lp_instance_rec.INSTALL_DATE := p_instance_rec.INSTALL_DATE;
3067 lp_instance_rec.CONTEXT := p_instance_rec.CONTEXT;
3068 lp_instance_rec.ATTRIBUTE1 := p_instance_rec.ATTRIBUTE1;
3069 lp_instance_rec. ATTRIBUTE2 := p_instance_rec.ATTRIBUTE2;
3070 lp_instance_rec.ATTRIBUTE3 := p_instance_rec.ATTRIBUTE3;
3071 lp_instance_rec.ATTRIBUTE4 := p_instance_rec.ATTRIBUTE4;
3072 lp_instance_rec.ATTRIBUTE5 := p_instance_rec.ATTRIBUTE5;
3073 lp_instance_rec.ATTRIBUTE6 := p_instance_rec.ATTRIBUTE6;
3074 lp_instance_rec.ATTRIBUTE7 := p_instance_rec.ATTRIBUTE7;
3075 lp_instance_rec.ATTRIBUTE8 := p_instance_rec.ATTRIBUTE8;
3076 lp_instance_rec.ATTRIBUTE9 := p_instance_rec.ATTRIBUTE9;
3077 lp_instance_rec.ATTRIBUTE10 := p_instance_rec.ATTRIBUTE10;
3078 lp_instance_rec.ATTRIBUTE11 := p_instance_rec.ATTRIBUTE11;
3079 lp_instance_rec.ATTRIBUTE12 := p_instance_rec.ATTRIBUTE12;
3080 lp_instance_rec.ATTRIBUTE13 := p_instance_rec.ATTRIBUTE13;
3081 lp_instance_rec.ATTRIBUTE14 := p_instance_rec.ATTRIBUTE14;
3082 lp_instance_rec.ATTRIBUTE15 := p_instance_rec.ATTRIBUTE15;
3083 lp_instance_rec.INSTALL_LOCATION_TYPE_CODE := p_instance_rec.INSTALL_LOCATION_TYPE_CODE;
3084 lp_instance_rec.INSTALL_LOCATION_ID := p_instance_rec.INSTALL_LOCATION_ID;
3085 IF p_instance_rec.CALL_CONTRACTS = 'N' THEN
3086 lp_instance_rec.CALL_CONTRACTS := 'F';
3087 ELSE
3088 lp_instance_rec.CALL_CONTRACTS := 'T';
3089 END IF;
3090 -- lp_instance_rec.CALL_CONTRACTS := p_instance_rec.CALL_CONTRACTS;
3091
3092 --Populate owner party info
3093 lp_party_tbl(1).party_source_table := 'HZ_PARTIES';
3094 lp_party_tbl(1).party_id := p_instance_rec.PARTY_ID;
3095 lp_party_tbl(1).relationship_type_code := 'OWNER';
3096 lp_party_tbl(1).contact_flag := 'N';
3097 --Populate owner party account info
3098 lp_account_tbl(1).parent_tbl_index := 1;
3099 lp_account_tbl(1).party_account_id := p_instance_rec.ACCOUNT_ID;
3100 lp_account_tbl(1).relationship_type_code := 'OWNER';
3101 --Populate transaction table
3102 lp_txn_rec.transaction_date := sysdate;
3103 lp_txn_rec.source_transaction_date := sysdate;
3104 lp_txn_rec.transaction_type_id:=1;
3105 --Populate org related information based upon IB team suggestion - bug12981731
3106 lp_org_assignments_tbl(1).operating_unit_id := okc_context.get_okc_org_id;
3107 lp_org_assignments_tbl(1).relationship_type_code := 'SOLD_FROM';
3108 lp_org_assignments_tbl(1).active_start_date := sysdate;
3109 lp_org_assignments_tbl(1).active_end_date := null;
3110
3111 -- Call IB to create item instance
3112
3113 CSI_ITEM_INSTANCE_PUB.CREATE_ITEM_INSTANCE(
3114 p_api_version =>p_api_version,
3115 p_commit => p_commit,
3116 p_init_msg_list =>p_init_msg_list,
3117 p_instance_rec => lp_instance_rec,
3118 p_ext_attrib_values_tbl => lp_ext_attrib_values_tbl,
3119 p_party_tbl => lp_party_tbl,
3120 p_account_tbl => lp_account_tbl,
3121 p_pricing_attrib_tbl => lp_pricing_attrib_tbl,
3122 p_org_assignments_tbl => lp_org_assignments_tbl,
3123 p_asset_assignment_tbl => lp_asset_assignment_tbl,
3124 p_txn_rec => lp_txn_rec,
3125 x_return_status => x_return_status,
3126 x_msg_count => x_msg_count,
3127 x_msg_data => x_msg_data);
3128
3129 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3130 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.Create_item_instance',
3131 'Csi PAI status = ('|| x_return_status || ')');
3132 END IF;
3133
3134 p_instance_rec.instance_id :=lp_instance_rec.instance_id;
3135 p_instance_rec.instance_number :=lp_instance_rec.instance_number;
3136
3137
3138 EXCEPTION
3139 WHEN OTHERS Then
3140 NULL;
3141
3142 END CREATE_ITEM_INSTANCE;
3143
3144
3145
3146
3147 PROCEDURE CHECK_CONTRACTS_IMPACTED(
3148 P_Api_Version IN NUMBER,
3149 P_init_msg_list IN VARCHAR2 Default OKC_API.G_FALSE,
3150 P_instance_id IN NUMBER,
3151 p_parent_instance_yn IN VARCHAR2,
3152 p_transaction_date IN DATE,
3153 p_new_install_date IN DATE,
3154 P_txn_tbl IN txn_tbl_type,
3155 x_contract_exists_yn OUT NOCOPY VARCHAR2,
3156 X_msg_Count OUT NOCOPY Number,
3157 X_msg_Data OUT NOCOPY Varchar2,
3158 x_return_status OUT NOCOPY Varchar2) IS
3159
3160 l_contracts_exists VARCHAR2(1) := 'N';
3161
3162 Cursor idc_contracts IS
3163
3164 Select CL.id
3165
3166 From OKC_K_ITEMS KI
3167 ,OKC_K_HEADERS_ALL_B KH
3168 ,OKC_K_LINES_B KL
3169 ,OKC_K_LINES_B CL
3170 ,OKC_STATUSES_b ST
3171
3172 Where
3173 KI.Jtot_Object1_code = 'OKX_CUSTPROD'
3174 AND KI.object1_id1 = to_char(p_instance_id)
3175 And KI.dnz_chr_id = KH.ID
3176 And KH.scs_code in ('WARRANTY')
3177 And KI.Cle_id = CL.id
3178 And CL.CLE_ID = KL.ID
3179 And CL.sts_code = ST.code
3180 And ST.ste_code not in ('TERMINATED','CANCELLED')
3181 And CL.date_terminated Is Null
3182 AND KL.date_terminated is null
3183 -- AND sysdate between cl.start_date and cl.end_date
3184 And KH.template_yn = 'N';
3185
3186 idc_contracts_REC idc_contracts%rowtype;
3187
3188 cursor trm_trf_contracts IS
3189 Select CL.id
3190 From OKC_K_ITEMS KI
3191 ,OKC_K_HEADERS_ALL_B KH
3192 ,OKC_K_LINES_B CL
3193 ,OKC_STATUSES_b ST
3194 Where KI.Jtot_Object1_code = 'OKX_CUSTPROD'
3195 AND KI.object1_id1 = to_char(p_instance_id )
3196 And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
3197 And KI.Cle_id = CL.id
3198 and cl.dnz_chr_id = kh.id
3199 And CL.sts_code = ST.code
3200 And ST.ste_code not in ('TERMINATED','CANCELLED')
3201 And CL.date_terminated Is Null
3202 And KH.template_yn = 'N'
3203 AND (( cl.end_date >= p_transaction_date) OR
3204 (cl.end_date < p_transaction_date
3205 and not exists (Select 'x'
3206 from okc_operation_instances ois,
3207 okc_operation_lines opl,
3208 okc_class_operations cls,
3209 okc_subclasses_b sl
3210 where ois.id=opl.oie_id
3211 And cls.opn_code in ('RENEWAL','REN_CON')
3212 And sl.code= 'SERVICE'
3213 And sl.cls_code = cls.cls_code
3214 and ois.cop_id = cls.id
3215 and object_cle_id=cl.id)));
3216
3217 cursor trm_trf_contracts1 IS
3218 Select CL.id
3219 From OKC_K_ITEMS KI
3220 ,OKC_K_HEADERS_ALL_B KH
3221 ,OKC_K_LINES_B CL
3222 ,OKC_STATUSES_b ST
3223 ,oks_instance_k_dtls_temp temp
3224 Where KI.Jtot_Object1_code = 'OKX_CUSTPROD'
3225 AND KI.object1_id1 = to_char(temp.instance_id)
3226 And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
3227 And KI.Cle_id = CL.id
3228 and cl.dnz_chr_id = kh.id
3229 And CL.sts_code = ST.code
3230 And ST.ste_code not in ('TERMINATED','CANCELLED')
3231 And CL.date_terminated Is Null
3232 And KH.template_yn = 'N'
3233 AND (( cl.end_date >= p_transaction_date) OR
3234 (cl.end_date < p_transaction_date
3235 and not exists (Select 'x'
3236 from okc_operation_instances ois,
3237 okc_operation_lines opl,
3238 okc_class_operations cls,
3239 okc_subclasses_b sl
3240 where ois.id=opl.oie_id
3241 And cls.opn_code in ('RENEWAL','REN_CON')
3242 And sl.code= 'SERVICE'
3243 And sl.cls_code = cls.cls_code
3244 and ois.cop_id = cls.id
3245 and object_cle_id=cl.id)));
3246
3247 trm_trf_contracts_rec trm_trf_contracts%rowtype;
3248
3249
3250 cursor trm_usages_contracts IS
3251 Select CL.id
3252 From OKC_K_ITEMS KI
3253 ,OKC_K_HEADERS_ALL_B KH
3254 ,OKC_K_LINES_B CL
3255 ,OKC_STATUSES_B ST
3256 ,csi_counter_associations CTRASC
3257
3258 where KI.object1_id1 = to_char(CTRASC.COunter_id)
3259 And KI.jtot_object1_code = 'OKX_COUNTER'
3260 And ctrasc.source_object_id = p_instance_id
3261 And ctrasc.source_object_code = 'CP'
3262 and kh.id=ki.dnz_chr_id
3263 And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
3264 And KH.template_yn = 'N'
3265 And KI.Cle_id = CL.id
3266 And CL.sts_code = ST.code
3267 And ST.ste_code not in ('TERMINATED','CANCELLED','ENTERED')
3268 And CL.date_terminated Is Null
3269 AND (( cl.end_date >= p_transaction_date) OR
3270 (cl.end_date < p_transaction_date
3271 and not exists (select 'x'
3272 from okc_operation_instances ois,
3273 okc_operation_lines opl,
3274 okc_class_operations cls,
3275 okc_subclasses_b sl
3276 where ois.id=opl.oie_id
3277 And cls.opn_code in ('RENEWAL','REN_CON')
3278 And sl.code= 'SERVICE'
3279 And sl.cls_code = cls.cls_code
3280 and ois.cop_id = cls.id
3281 and object_cle_id=cl.id)));
3282
3283 cursor trm_usages_contracts1 IS
3284 Select CL.id
3285 From OKC_K_ITEMS KI
3286 ,OKC_K_HEADERS_ALL_B KH
3287 ,OKC_K_LINES_B CL
3288 ,OKC_STATUSES_B ST
3289 ,csi_counter_associations CTRASC
3290 ,oks_instance_k_dtls_temp temp
3291 where KI.object1_id1 = to_char(CTRASC.COunter_id)
3292 And KI.jtot_object1_code = 'OKX_COUNTER'
3293 And ctrasc.source_object_id = temp.instance_id
3294 And ctrasc.source_object_code = 'CP'
3295 and kh.id=ki.dnz_chr_id
3296 And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
3297 And KH.template_yn = 'N'
3298 And KI.Cle_id = CL.id
3299 And CL.sts_code = ST.code
3300 And ST.ste_code not in ('TERMINATED','CANCELLED','ENTERED')
3301 And CL.date_terminated Is Null
3302 AND (( cl.end_date >= p_transaction_date) OR
3303 (cl.end_date < p_transaction_date
3304 and not exists (select 'x'
3305 from okc_operation_instances ois,
3306 okc_operation_lines opl,
3307 okc_class_operations cls,
3308 okc_subclasses_b sl
3309 where ois.id=opl.oie_id
3310 And cls.opn_code in ('RENEWAL','REN_CON')
3311 And sl.code= 'SERVICE'
3312 And sl.cls_code = cls.cls_code
3313 and ois.cop_id = cls.id
3314 and object_cle_id=cl.id)));
3315
3316
3317 trm_usages_contracts_rec trm_usages_contracts%rowtype;
3318
3319 cursor spl_upd_contracts IS
3320 Select CL.id
3321 From OKC_K_ITEMS KI
3322 ,OKC_K_HEADERS_ALL_B KH
3323 ,OKC_K_LINES_B KL
3324 ,OKC_K_LINES_B CL
3325 ,OKC_STATUSES_b ST
3326 Where
3327 KI.Jtot_Object1_code = 'OKX_CUSTPROD'
3328 AND KI.object1_id1 = to_char(p_instance_id)
3329 And KI.dnz_chr_id = KH.ID
3330 And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
3331 And KI.Cle_id = CL.id
3332 And CL.CLE_ID = KL.ID
3333 And CL.sts_code = ST.code
3334 And ST.ste_code not in ('TERMINATED','CANCELLED')
3335 And CL.date_terminated Is Null
3336 AND KL.date_terminated is null
3337 AND ((p_transaction_date between cl.start_date and cl.end_date)
3338 OR (p_transaction_date <= cl.start_date))
3339 And KH.template_yn = 'N';
3340
3341 spl_upd_contracts_rec spl_upd_contracts%rowtype;
3342 cursor rin_contracts IS
3343 Select cl.id
3344 From OKC_K_ITEMS KI
3345 ,OKC_K_HEADERS_ALL_B KH
3346 ,OKC_K_LINES_B CL
3347 Where
3348 KI.Jtot_Object1_code = 'OKX_CUSTPROD'
3349 AND KI.object1_id1 = to_char(p_instance_id)
3350 And KI.dnz_chr_id = KH.ID
3351 And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
3352 And KI.Cle_id = CL.id
3353 And CL.date_terminated Is not Null
3354 And KH.template_yn = 'N';
3355
3356 rin_contracts_rec rin_contracts%rowtype;
3357
3358
3359 i NUMBER :=1;
3360
3361
3362 BEGIN
3363
3364 -- Loop through and figure the operations
3365
3366 i:= P_txn_tbl.first;
3367
3368 WHILE i is not null loop
3369
3370 IF (P_txn_tbl(i)= 'RIN') THEN
3371 OPEN rin_contracts;
3372 FETCH rin_contracts into rin_contracts_rec;
3373 If rin_contracts%found THEN
3374 l_contracts_exists := 'Y';
3375 END IF;
3376 CLOSE rin_contracts;
3377
3378
3379 ELSIF (P_txn_tbl(i) in ('SPL','UPD')) THEN
3380 OPEN spl_upd_contracts;
3381 FETCH spl_upd_contracts into spl_upd_contracts_rec;
3382 If spl_upd_contracts%found THEN
3383 l_contracts_exists := 'Y';
3384 END IF;
3385 CLOSE spl_upd_contracts;
3386
3387 ELSIF (P_txn_tbl(i) in ('TRF','TRM')) THEN
3388
3389 if p_parent_instance_yn ='Y' Then
3390 --populate item instance table
3391 POPULATE_CHILD_INSTANCES (p_api_version =>p_api_version,
3392 p_init_msg_list=> p_init_msg_list,
3393 p_instance_id =>p_instance_id,
3394 p_transaction_type => P_txn_tbl(i),
3395 x_msg_Count => X_msg_Count,
3396 x_msg_Data => X_msg_data,
3397 x_return_status => x_return_status);
3398 end if;
3399
3400
3401 If p_parent_instance_yn = 'N'
3402 Then
3403
3404 OPEN trm_trf_contracts;
3405 FETCH trm_trf_contracts into trm_trf_contracts_rec;
3406 If trm_trf_contracts%found THEN
3407 l_contracts_exists := 'Y';
3408 END IF;
3409 CLOSE trm_trf_contracts;
3410
3411 Else
3412
3413 OPEN trm_trf_contracts1;
3414 FETCH trm_trf_contracts1 into trm_trf_contracts_rec;
3415 If trm_trf_contracts1%found THEN
3416 l_contracts_exists := 'Y';
3417 END IF;
3418 CLOSE trm_trf_contracts1;
3419
3420 End If;
3421
3422 IF l_contracts_exists = 'N' AND P_txn_tbl(i) ='TRM' THEN
3423 --Also check usages
3424 If p_parent_instance_yn = 'N'
3425 Then
3426 OPEN trm_usages_contracts;
3427 FETCH trm_usages_contracts into trm_usages_contracts_rec;
3428 If trm_usages_contracts%found THEN
3429 l_contracts_exists := 'Y';
3430 END IF;
3431 CLOSE trM_usages_contracts;
3432 Else
3433 OPEN trm_usages_contracts1;
3434 FETCH trm_usages_contracts1 into trm_usages_contracts_rec;
3435 If trm_usages_contracts1%found THEN
3436 l_contracts_exists := 'Y';
3437 END IF;
3438 CLOSE trM_usages_contracts1;
3439 END IF;
3440 End If;
3441
3442 ELSIF (P_txn_tbl(i) = 'IDC') THEN
3443 OPEN idc_contracts;
3444 FETCH idc_contracts into idc_contracts_rec;
3445 If idc_contracts%found THEN
3446 l_contracts_exists := 'Y';
3447 END IF;
3448 CLOSE idc_contracts;
3449 END IF;
3450
3451 IF ((l_contracts_exists = 'Y') OR (P_txn_tbl(i) not in ('UPD','IDC'))) THEN
3452 EXIT;
3453 END IF;
3454
3455 i := P_txn_tbl.next(i);
3456
3457
3458 END LOOP;
3459
3460 x_contract_exists_yn:= l_contracts_exists;
3461 x_return_status := 'S';
3462 END CHECK_CONTRACTS_IMPACTED;
3463
3464 /*cgopinee - added new procedure for webadi interface from IB*/
3465 PROCEDURE create_k_covered_levels_webadi
3466 (
3467 p_chr_id IN NUMBER,
3468 p_attach2_line_id IN NUMBER,
3469 p_k_covd_rec IN k_cov_line_rec_type,
3470 x_covlvl_id OUT NOCOPY NUMBER,
3471 x_return_status OUT NOCOPY VARCHAR2,
3472 x_msg_count OUT NOCOPY NUMBER,
3473 x_msg_data OUT NOCOPY VARCHAR2
3474 )
3475 IS
3476
3477 l_api_version CONSTANT NUMBER := 1.0;
3478 l_init_msg_list CONSTANT VARCHAR2 (1) := okc_api.g_false;
3479 l_return_status VARCHAR2 (1) := 'S';
3480 l_api_type CONSTANT VARCHAR2(50) := 'PUB';
3481 l_api_name CONSTANT VARCHAR2(50) := 'k_cov_webadi';
3482
3483 --Contract Line Table
3484 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
3485 l_clev_tbl_out okc_contract_pub.clev_tbl_type;
3486 l_klnv_tbl_in oks_kln_pvt.klnv_tbl_type;
3487 l_klnv_tbl_out oks_kln_pvt.klnv_tbl_type;
3488
3489 --Contract Item
3490 l_cimv_tbl okc_contract_item_pub.cimv_tbl_type;
3491
3492 l_invoice_text VARCHAR2 (2000);
3493
3494 l_cov_line_id NUMBER;
3495 l_line_item_id NUMBER;
3496
3497 kl_st_dt DATE;
3498 kl_end_dt DATE;
3499 kl_sts_code VARCHAR2(30);
3500 kl_lse_id NUMBER;
3501 kl_currency_code VARCHAR2(15);
3502 kl_ren_type VARCHAR2(30);
3503
3504 l_k_sts VARCHAR2(30);
3505 l_lsl_id NUMBER;
3506 l_valid1 VARCHAR2(1);
3507 l_valid2 VARCHAR2(1);
3508 l_valid3 VARCHAR2(1);
3509 l_valid4 VARCHAR2(1);
3510
3511 CURSOR chk_sts(p_chr_id NUMBER)
3512 IS
3513 SELECT sts.ste_code
3514 FROM okc_k_headers_all_b okc,
3515 okc_statuses_b sts
3516 WHERE okc.id = p_chr_id
3517 AND okc.sts_code = sts.code
3518 AND sts.ste_code ='ENTERED';
3519
3520 CURSOR top_line_dtls_csr(p_chr_id NUMBER)
3521 IS
3522 SELECT start_date, end_date, sts_code, lse_id,
3523 currency_code, line_renewal_type_code
3524 FROM okc_k_lines_b
3525 WHERE chr_id = p_chr_id
3526 AND id = p_attach2_line_id;
3527
3528 CURSOR prod_party_validity_csr (p_chr_id NUMBER,p_cust_prod_id NUMBER)
3529 IS
3530 SELECT 'Y'
3531 FROM CSI_ITEM_INSTANCES CII,
3532 CSI_I_ORG_ASSIGNMENTS CIOA,
3533 OE_ORDER_LINES_ALL OOL,
3534 OKC_K_PARTY_ROLES_B k_party,
3535 okc_k_headers_all_b okh
3536 WHERE CII.last_oe_order_line_id = OOL.line_id( + )
3537 AND CII.instance_id = CIOA.instance_id( + )
3538 AND CIOA.relationship_type_code ( + ) = 'SOLD_FROM'
3539 AND k_party.chr_id=p_chr_id
3540 AND k_party.JTOT_OBJECT1_CODE IN ('OKX_PARTY')
3541 AND okh.id=p_chr_id
3542 AND okh.id=k_party.dnz_chr_id
3543 AND EXISTS
3544 (SELECT 1
3545 FROM HZ_CUST_ACCOUNTS CA1
3546 WHERE CA1.party_id = k_party.object1_id1
3547 AND CA1.status = 'A'
3548 AND CA1.CUST_ACCOUNT_ID = CII.Owner_party_account_id
3549 UNION ALL
3550 SELECT 1
3551 FROM HZ_CUST_ACCOUNTS CA2,
3552 HZ_CUST_ACCT_RELATE_ALL REL
3553 WHERE CA2.party_id = k_party.object1_id1
3554 AND REL.cust_account_id = CA2.CUST_ACCOUNT_ID
3555 AND REL.org_id = okh.AUTHORING_ORG_ID
3556 AND REL.status = 'A'
3557 AND CA2.status = 'A'
3558 AND REL.related_cust_account_id = CII.Owner_party_account_id
3559 )
3560 AND CII.instance_id=p_cust_prod_id;
3561
3562 CURSOR prod_uom_validity_csr(p_cust_prod_id NUMBER)
3563 IS
3564 SELECT 'Y'
3565 FROM CSI_ITEM_INSTANCES CII,
3566 (SELECT UNIT_OF_MEASURE , UOM_CODE ,DISABLE_DATE
3567 FROM MTL_UNITS_OF_MEASURE_TL
3568 WHERE LANGUAGE = USERENV('LANG')) UOM
3569 WHERE CII.Unit_Of_Measure = UOM.uom_code
3570 AND TRUNC(NVL(UOM.disable_date, SYSDATE)) >= TRUNC(SYSDATE)
3571 AND cii.instance_id=p_cust_prod_id;
3572
3573 CURSOR prod_sts_chk_csr(p_cust_prod_id NUMBER)
3574 IS
3575 SELECT 'Y'
3576 FROM CSI_ITEM_INSTANCES CII
3577 WHERE CII.instance_status_id IN
3578 (SELECT CIS.instance_status_id
3579 FROM CSI_INSTANCE_STATUSES CIS
3580 WHERE CIS.service_order_allowed_flag = 'Y'
3581 )
3582 AND cii.instance_id=p_cust_prod_id;
3583
3584 CURSOR item_serviceable_csr(p_chr_id NUMBER,p_cust_prod_id NUMBER)
3585 IS
3586 SELECT 'Y'
3587 FROM CSI_ITEM_INSTANCES CII,
3588 MTL_SYSTEM_ITEMS_B_KFV IT,
3589 OKC_K_HEADERS_ALL_B OKH
3590 WHERE IT.inventory_item_id = CII.inventory_item_id
3591 AND IT.organization_id = OKH.inv_organization_id
3592 AND IT.serviceable_product_flag = 'Y'
3593 AND OKH.id = p_chr_id
3594 AND CII.instance_id = p_cust_prod_id;
3595
3596 FUNCTION priced_yn (p_lse_id IN NUMBER)
3597 RETURN VARCHAR2
3598 IS
3599 CURSOR c_priced_yn
3600 IS
3601 SELECT priced_yn
3602 FROM okc_line_styles_b
3603 WHERE ID = p_lse_id;
3604
3605 v_priced VARCHAR2 (50) := 'N';
3606 BEGIN
3607 FOR cur_c_priced_yn IN c_priced_yn
3608 LOOP
3609 v_priced := cur_c_priced_yn.priced_yn;
3610 EXIT;
3611 END LOOP;
3612
3613 RETURN (v_priced);
3614 END priced_yn;
3615
3616 FUNCTION get_sub_line_number (p_chr_id IN NUMBER, p_cle_id IN NUMBER)
3617 RETURN NUMBER
3618 IS
3619 max_line_number NUMBER;
3620 CURSOR get_line_number
3621 IS
3622 SELECT NVL (MAX (TO_NUMBER (line_number)), 0) + 1
3623 FROM okc_k_lines_b
3624 WHERE dnz_chr_id = p_chr_id
3625 AND cle_id = p_cle_id
3626 AND lse_id IN (35, 7, 8, 9, 10, 11, 13, 18, 25);
3627 BEGIN
3628 OPEN get_line_number;
3629 FETCH get_line_number INTO max_line_number;
3630 CLOSE get_line_number;
3631
3632 RETURN (max_line_number);
3633 END get_sub_line_number;
3634
3635 FUNCTION getformattedinvoicetext (
3636 p_product_item IN NUMBER,
3637 p_start_date IN DATE,
3638 p_end_date IN DATE,
3639 p_svc_line_id IN VARCHAR2,
3640 p_qty IN NUMBER
3641 )
3642 RETURN VARCHAR2
3643 IS
3644 CURSOR l_inv_csr (p_product_item NUMBER)
3645 IS
3646 SELECT t.description NAME, b.concatenated_segments description
3647 FROM mtl_system_items_b_kfv b, mtl_system_items_tl t
3648 WHERE b.inventory_item_id = t.inventory_item_id
3649 AND b.organization_id = t.organization_id
3650 AND t.LANGUAGE = USERENV ('LANG')
3651 AND b.inventory_item_id = p_product_item
3652 AND ROWNUM < 2;
3653
3654 l_object_code okc_k_items.jtot_object1_code%TYPE;
3655 l_object1_id1 okc_k_items.object1_id1%TYPE;
3656 l_object1_id2 okc_k_items.object1_id2%TYPE;
3657 l_no_of_items okc_k_items.number_of_items%TYPE;
3658 l_name VARCHAR2 (2000);
3659 l_desc VARCHAR2 (2000);
3660 l_formatted_invoice_text VARCHAR2 (2000);
3661
3662 CURSOR svc_desc_csr (p_svc_line_id NUMBER)
3663 IS
3664 SELECT MTL.Name,MTL.Description
3665 FROM okx_system_items_v mtl,
3666 okc_k_items oki
3667 WHERE mtl.id1 = oki.object1_id1
3668 AND mtl.organization_id = oki.object1_id2
3669 AND oki.cle_id = p_svc_line_id;
3670
3671 l_svc_item_name VARCHAR2 (2000);
3672 l_svc_name VARCHAR2 (2000);
3673 l_svc_desc VARCHAR2 (2000);
3674 l_svc_item_desc VARCHAR2 (2000);
3675
3676 BEGIN
3677 OPEN svc_desc_csr(p_svc_line_id);
3678 FETCH svc_desc_csr INTO l_svc_name, l_svc_desc;
3679 CLOSE svc_desc_csr;
3680
3681 If fnd_profile.value('OKS_ITEM_DISPLAY_PREFERENCE') = 'DISPLAY_NAME' Then
3682 l_svc_item_desc := l_svc_desc;
3683 l_svc_item_name := l_svc_name;
3684 ELSE
3685 l_svc_item_desc := l_svc_name;
3686 l_svc_item_name := l_svc_desc;
3687 End If;
3688
3689 OPEN l_inv_csr (p_product_item);
3690 FETCH l_inv_csr INTO l_name, l_desc;
3691 CLOSE l_inv_csr;
3692
3693 IF fnd_profile.VALUE ('OKS_ITEM_DISPLAY_PREFERENCE') = 'DISPLAY_DESC'
3694 THEN
3695 l_desc := l_name; --l_desc;
3696 ELSE
3697 l_desc := l_desc; --l_name;
3698 END IF;
3699
3700 l_formatted_invoice_text :=
3701 SUBSTR (l_svc_item_desc
3702 || ':'
3703 || p_qty
3704 || ':'
3705 || l_desc
3706 || ':'
3707 || to_char(p_start_date,'DD-MON-YYYY')
3708 || ':'
3709 || to_char(p_end_date,'DD-MON-YYYY'),
3710 1,
3711 450
3712 );
3713 RETURN (l_formatted_invoice_text);
3714 END getformattedinvoicetext;
3715
3716 BEGIN
3717 DBMS_TRANSACTION.SAVEPOINT(l_api_name || l_api_type);
3718 /*Ensure that the contract is in entered statuses ste_code.*/
3719 OPEN chk_sts(p_chr_id);
3720 FETCH chk_sts INTO l_k_sts;
3721 CLOSE chk_sts;
3722 IF l_k_sts <> 'ENTERED'
3723 THEN
3724 /*Subline addition to contracts in non-entered status is not allowed.*/
3725 IF fnd_log.LEVEL_EVENT >= fnd_log.g_current_runtime_level
3726 THEN
3727 fnd_log.STRING (fnd_log.LEVEL_EVENT,
3728 g_module_current
3729 || '.create_webadi_covered_levels',
3730 'Cannot add sublines to contract'
3731 || ' in current status'
3732 );
3733 END IF;
3734 x_msg_data := 'Cannot add the sublines to contract in current status';
3735 l_return_status := okc_api.g_ret_sts_error;
3736 Raise G_EXCEPTION_HALT_VALIDATION;
3737 END IF;
3738
3739 /*start of get line details*/
3740 OPEN top_line_dtls_csr(p_chr_id);
3741 FETCH top_line_dtls_csr INTO kl_st_dt,kl_end_dt,kl_sts_code,kl_lse_id,kl_currency_code, kl_ren_type;
3742 CLOSE top_line_dtls_csr;
3743 /*end of get line details*/
3744
3745 /*----------Start of validations----------------*/
3746
3747 /*Compare the owner of the product with that of contract*/
3748 OPEN prod_party_validity_csr(p_chr_id,p_k_covd_rec.customer_product_id);
3749 FETCH prod_party_validity_csr INTO l_valid1;
3750 CLOSE prod_party_validity_csr;
3751
3752 IF Nvl(l_valid1,'N') <> 'Y'
3753 THEN
3754 IF fnd_log.LEVEL_EVENT >= fnd_log.g_current_runtime_level
3755 THEN
3756 fnd_log.STRING (fnd_log.LEVEL_EVENT,
3757 g_module_current
3758 || '.create_webadi_covered_levels',
3759 'The Owner party of the contract subline and Owner party of the Instance are not the same.'
3760 );
3761 END IF;
3762
3763 x_msg_data := 'The Owner party of the contract subline and Owner party of the Instance are not the same.';
3764 l_return_status := okc_api.g_ret_sts_error;
3765 Raise G_EXCEPTION_HALT_VALIDATION;
3766 END IF;
3767
3768 /*Check if the product UOM code is valid*/
3769 OPEN prod_uom_validity_csr(p_k_covd_rec.customer_product_id);
3770 FETCH prod_uom_validity_csr INTO l_valid2;
3771 CLOSE prod_uom_validity_csr;
3772
3773 IF Nvl(l_valid2,'N') <> 'Y'
3774 THEN
3775
3776 IF fnd_log.LEVEL_EVENT >= fnd_log.g_current_runtime_level
3777 THEN
3778 fnd_log.STRING (fnd_log.LEVEL_EVENT,
3779 g_module_current
3780 || '.create_webadi_covered_levels',
3781 'The product UOM code is invalid'
3782 );
3783 END IF;
3784
3785 x_msg_data := 'The product UOM code is invalid';
3786 l_return_status := okc_api.g_ret_sts_error;
3787 Raise G_EXCEPTION_HALT_VALIDATION;
3788 END IF;
3789
3790 /*Check if the instance in the present status is serviceable.*/
3791 OPEN prod_sts_chk_csr(p_k_covd_rec.customer_product_id);
3792 FETCH prod_sts_chk_csr INTO l_valid3;
3793 CLOSE prod_sts_chk_csr;
3794
3795 IF Nvl(l_valid3,'N') <> 'Y'
3796 THEN
3797
3798 IF fnd_log.LEVEL_EVENT >= fnd_log.g_current_runtime_level
3799 THEN
3800 fnd_log.STRING (fnd_log.LEVEL_EVENT,
3801 g_module_current
3802 || '.create_webadi_covered_levels',
3803 'The contract subline cannot be associated to the Instance, with the specified Instance status.'
3804 );
3805 END IF;
3806
3807 x_msg_data := 'The contract subline cannot be associated to the Instance, with the specified Instance status.';
3808 l_return_status := okc_api.g_ret_sts_error;
3809 Raise G_EXCEPTION_HALT_VALIDATION;
3810 END IF;
3811
3812 /*Check if the item being added is serviceable*/
3813 OPEN item_serviceable_csr(p_chr_id,p_k_covd_rec.customer_product_id);
3814 FETCH item_serviceable_csr INTO l_valid4;
3815 CLOSE item_serviceable_csr;
3816
3817 IF Nvl(l_valid4,'N') <> 'Y'
3818 THEN
3819
3820 IF fnd_log.LEVEL_EVENT >= fnd_log.g_current_runtime_level
3821 THEN
3822 fnd_log.STRING (fnd_log.LEVEL_EVENT,
3823 g_module_current
3824 || '.create_webadi_covered_levels',
3825 'The Item is not a serviceable Item'
3826 );
3827 END IF;
3828
3829 x_msg_data := 'The Item is not a serviceable Item.';
3830 l_return_status := okc_api.g_ret_sts_error;
3831 RAISE G_EXCEPTION_HALT_VALIDATION;
3832 END IF;
3833
3834 /*---------------End of all Validations---------------------*/
3835
3836 l_clev_tbl_in (1).chr_id := NULL;
3837 l_clev_tbl_in (1).sfwt_flag := 'N';
3838
3839 IF kl_lse_id = 1 THEN
3840 l_lsl_id := 9;
3841 ELSIF kl_lse_id = 19 THEN
3842 l_lsl_id := 25;
3843 ELSE /*For any other line type the operation is not allowed.*/
3844 IF fnd_log.LEVEL_EVENT >= fnd_log.g_current_runtime_level
3845 THEN
3846 fnd_log.STRING (fnd_log.LEVEL_EVENT,
3847 g_module_current
3848 || '.create_webadi_covered_levels',
3849 'Cannot add sublines to lines other than'
3850 || ' service/warranty lines.'
3851 );
3852 END IF;
3853 x_msg_data := 'Cannot add sublines to non-service/warranty lines';
3854 l_return_status := okc_api.g_ret_sts_error;
3855 Raise G_EXCEPTION_HALT_VALIDATION;
3856 END IF;
3857 l_clev_tbl_in (1).lse_id := l_lsl_id;
3858 l_clev_tbl_in (1).line_number := get_sub_line_number (p_chr_id, p_attach2_line_id);
3859 l_clev_tbl_in (1).sts_code := kl_sts_code;
3860 l_clev_tbl_in (1).display_sequence := 2;
3861 l_clev_tbl_in (1).dnz_chr_id := p_chr_id;
3862 --l_clev_tbl_in (1).NAME := NULL;
3863 --l_clev_tbl_in (1).item_description := NULL;
3864 l_clev_tbl_in (1).start_date := kl_st_dt;
3865 l_clev_tbl_in (1).end_date := kl_end_dt;
3866 l_clev_tbl_in (1).exception_yn := 'N';
3867 l_clev_tbl_in (1).price_negotiated := 0;
3868 l_clev_tbl_in (1).currency_code := kl_currency_code;
3869 l_clev_tbl_in (1).price_unit := 0;
3870 l_clev_tbl_in (1).cle_id := p_attach2_line_id;
3871 l_clev_tbl_in (1).price_level_ind := priced_yn (l_lsl_id);
3872 l_clev_tbl_in (1).trn_code := NULL;
3873 l_clev_tbl_in (1).comments := NULL;
3874 l_clev_tbl_in (1).upg_orig_system_ref := NULL;
3875 l_clev_tbl_in (1).upg_orig_system_ref_id := NULL;
3876 l_clev_tbl_in (1).line_renewal_type_code := NVL (kl_ren_type, 'FUL');
3877
3878 okc_contract_pub.create_contract_line
3879 (p_api_version => l_api_version,
3880 p_init_msg_list => l_init_msg_list,
3881 p_restricted_update => okc_api.g_true,
3882 x_return_status => l_return_status,
3883 x_msg_count => x_msg_count,
3884 x_msg_data => x_msg_data,
3885 p_clev_tbl => l_clev_tbl_in,
3886 x_clev_tbl => l_clev_tbl_out
3887 );
3888 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
3889 THEN
3890 fnd_log.STRING
3891 (fnd_log.level_event,
3892 g_module_current
3893 || '.Create_K_Covered_Levels.after_create.okc_subline',
3894 'okc_contract_pub.create_contract_line(Return status = '
3895 || l_return_status
3896 || ')'
3897 );
3898 END IF;
3899
3900 IF l_return_status = 'S'
3901 THEN
3902 l_cov_line_id := l_clev_tbl_out (1).ID;
3903 x_covlvl_id := l_cov_line_id;
3904 ELSE
3905 okc_api.set_message (g_app_name,
3906 g_required_value,
3907 g_col_name_token,
3908 'K LINE (SUB LINE)'
3909 );
3910 x_msg_data := 'Erroring while creating OKC Contract COV LINE';
3911 Raise G_EXCEPTION_HALT_VALIDATION;
3912 END IF;
3913 /*Insertion of OKS_DETAILS- Start*/
3914 l_klnv_tbl_in (1).tax_amount := 0;
3915 l_klnv_tbl_in (1).cle_id := l_cov_line_id;
3916 l_klnv_tbl_in (1).dnz_chr_id := p_chr_id;
3917 l_klnv_tbl_in (1).created_by := okc_api.g_miss_num;
3918 l_klnv_tbl_in (1).creation_date := okc_api.g_miss_date;
3919 l_klnv_tbl_in (1).status_text := 'Subline created from IBWebADI';
3920
3921 l_invoice_text := getformattedinvoicetext
3922 (p_k_covd_rec.Inventory_item_id,
3923 kl_st_dt,
3924 kl_end_dt,
3925 p_attach2_line_id,
3926 p_k_covd_rec.quantity
3927 );
3928
3929 l_klnv_tbl_in (1).invoice_text := l_invoice_text;
3930 l_klnv_tbl_in (1).price_uom := NULL;
3931 l_klnv_tbl_in (1).toplvl_uom_code := NULL ;
3932 l_klnv_tbl_in (1).inv_print_flag := 'Y';
3933 l_klnv_tbl_in (1).toplvl_price_qty := NULL;
3934
3935 oks_contract_line_pub.create_line (p_api_version => l_api_version,
3936 p_init_msg_list => l_init_msg_list,
3937 x_return_status => l_return_status,
3938 x_msg_count => x_msg_count,
3939 x_msg_data => x_msg_data,
3940 p_klnv_tbl => l_klnv_tbl_in,
3941 x_klnv_tbl => l_klnv_tbl_out,
3942 p_validate_yn => 'N'
3943 );
3944 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
3945 THEN
3946 fnd_log.STRING
3947 (fnd_log.level_event,
3948 g_module_current
3949 || '.Create_K_Covered_Levels.after_create.oks_subline',
3950 'oks_contract_line_pub.create_line(Return status = '
3951 || l_return_status
3952 || ')'
3953 );
3954 END IF;
3955
3956 IF NOT l_return_status = 'S'
3957 THEN
3958 okc_api.set_message (g_app_name,
3959 g_required_value,
3960 g_col_name_token,
3961 'OKS Contract COV LINE'
3962 );
3963
3964 x_msg_data := 'Erroring while creating OKS Contract COV LINE';
3965 RAISE g_exception_halt_validation;
3966 END IF;
3967
3968 --Create Contract Item
3969 l_cimv_tbl (1).ID := okc_p_util.raw_to_number (SYS_GUID ());
3970 l_cimv_tbl (1).cle_id := l_cov_line_id;
3971 l_cimv_tbl (1).chr_id := NULL;
3972 l_cimv_tbl (1).cle_id_for := NULL;
3973 l_cimv_tbl (1).dnz_chr_id := p_chr_id;
3974 l_cimv_tbl (1).object1_id1 := p_k_covd_rec.customer_product_id;
3975 l_cimv_tbl (1).object1_id2 := '#';
3976 l_cimv_tbl (1).jtot_object1_code := 'OKX_CUSTPROD';
3977 l_cimv_tbl (1).uom_code := p_k_covd_rec.Unit_of_measure;
3978 l_cimv_tbl (1).exception_yn := 'N';
3979 l_cimv_tbl (1).number_of_items := p_k_covd_rec.quantity;
3980 l_cimv_tbl (1).priced_item_yn := '';
3981 l_cimv_tbl (1).upg_orig_system_ref := '';
3982 l_cimv_tbl (1).upg_orig_system_ref_id := NULL;
3983 l_cimv_tbl (1).object_version_number := 1;
3984 l_cimv_tbl (1).created_by := fnd_global.user_id;
3985 l_cimv_tbl (1).creation_date := SYSDATE;
3986 l_cimv_tbl (1).last_updated_by := fnd_global.user_id;
3987 l_cimv_tbl (1).last_update_date := SYSDATE;
3988 l_cimv_tbl (1).last_update_login := NULL;
3989 l_cimv_tbl (1).request_id := NULL;
3990 l_cimv_tbl (1).program_id := NULL;
3991 l_cimv_tbl (1).program_application_id := NULL;
3992 l_cimv_tbl (1).program_update_date := NULL;
3993
3994 okc_cim_pvt.insert_row_upg (x_return_status => l_return_status,
3995 p_cimv_tbl => l_cimv_tbl
3996 );
3997
3998 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
3999 THEN
4000 fnd_log.STRING
4001 (fnd_log.level_event,
4002 g_module_current
4003 || '.Create_K_Covered_Levels.after_create.contract_item',
4004 'okc_cim_pvt.insert_row_upg(Return status = '
4005 || l_return_status
4006 || ')'
4007 );
4008 END IF;
4009
4010 IF l_return_status = 'S'
4011 THEN
4012 l_line_item_id := l_cimv_tbl (1).ID;
4013 ELSE
4014 okc_api.set_message (g_app_name,
4015 g_required_value,
4016 g_col_name_token,
4017 'KItem (SUB LINE)'
4018 );
4019 x_msg_data := 'Cannot add the product as subline to the selected line';
4020 RAISE g_exception_halt_validation;
4021 END IF;
4022 x_return_status := l_return_status;
4023 COMMIT;
4024
4025 EXCEPTION
4026 WHEN g_exception_halt_validation
4027 THEN
4028 x_return_status := l_return_status;
4029 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || l_api_type);
4030 NULL;
4031 WHEN OTHERS
4032 THEN
4033 x_msg_data := 'Unexpected error during OKS cov level creation';
4034 x_return_status := okc_api.g_ret_sts_unexp_error;
4035 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || l_api_type);
4036 okc_api.set_message (g_app_name,
4037 g_unexpected_error,
4038 g_sqlcode_token,
4039 SQLCODE,
4040 g_sqlerrm_token,
4041 SQLERRM
4042 );
4043 END create_k_covered_levels_webadi;
4044 END OKS_IBINT_PUB;