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