[Home] [Help]
PACKAGE BODY: APPS.CSI_JAVA_INTERFACE_PKG
Source
1 PACKAGE BODY CSI_JAVA_INTERFACE_PKG AS
2 /* $Header: csivjib.pls 120.13 2006/09/18 09:38:59 bnarayan noship $ */
3
4 /*----------------------------------------------------*/
5 /* ****************Important***************************/
6 /* This package is created for JAVA Interface to */
7 /* Installed Base(CSI). The procedures here are */
8 /* subject to change without notice. */
9 /* History:
10 5/6/2005 115.45 bug 4348762, should store status name in status_text,
11 not sts_code, Xiangyang Li
12 */
13 /*----------------------------------------------------*/
14
15 -- --------------------------------------------------------
16 -- Define global variables
17 -- --------------------------------------------------------
18
19 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_JAVA_INTERFACE_PKG';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csivjib.pls';
21
22 /*----------------------------------------------------*/
23 /* procedure name: create_item_instance */
24 /* description : procedure used to */
25 /* create item instances */
26 /*----------------------------------------------------*/
27
28 TYPE connected_relationship_rec IS RECORD
29 (
30 object_id number,
31 subject_id number,
32 swapflag varchar2(1)
33 );
34 TYPE instanceid_rec IS RECORD
35 (
36 instance_id number
37 );
38 TYPE connected_relationship_tbl IS TABLE OF connected_relationship_rec INDEX BY BINARY_INTEGER;
39 TYPE instanceid_tbl1 IS TABLE OF instanceid_rec INDEX BY BINARY_INTEGER;
40 TYPE instanceid_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
41
42 PROCEDURE create_item_instance
43 (
44 p_api_version IN NUMBER
45 ,p_commit IN VARCHAR2
46 ,p_init_msg_list IN VARCHAR2
47 ,p_validation_level IN NUMBER
48 ,p_instance_rec IN OUT NOCOPY csi_datastructures_pub.instance_rec
49 ,p_party_tbl IN OUT NOCOPY csi_datastructures_pub.party_tbl
50 ,p_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
51 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
52 ,x_return_status OUT NOCOPY VARCHAR2
53 ,x_msg_count OUT NOCOPY NUMBER
54 ,x_msg_data OUT NOCOPY VARCHAR2
55 )
56 IS
57 l_api_name CONSTANT VARCHAR2(30) := 'create_item_instance';
58 l_api_version CONSTANT NUMBER := 1.0;
59 p_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
60 p_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
61 p_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
62 p_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
63 BEGIN
64
65 SAVEPOINT create_item_instance;
66
67 -- Now call the stored program
68 csi_item_instance_pub.create_item_instance(
69 p_api_version,
70 p_commit,
71 p_init_msg_list,
72 p_validation_level,
73 p_instance_rec,
74 p_ext_attrib_values_tbl,
75 p_party_tbl,
76 p_account_tbl,
77 p_pricing_attrib_tbl,
78 p_org_assignments_tbl,
79 p_asset_assignment_tbl,
80 p_txn_rec,
81 x_return_status,
82 x_msg_count,
83 x_msg_data);
84
85 FND_MSG_PUB.Count_And_Get
86 (p_count => x_msg_count ,
87 p_data => x_msg_data
88 );
89
90 EXCEPTION
91
92 WHEN FND_API.G_EXC_ERROR THEN
93 x_return_status := FND_API.G_RET_STS_ERROR ;
94 ROLLBACK TO create_item_instance;
95 FND_MSG_PUB.Count_And_Get
96 ( p_count => x_msg_count,
97 p_data => x_msg_data
98 );
99
100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
102 ROLLBACK TO create_item_instance;
103 FND_MSG_PUB.Count_And_Get
104 ( p_count => x_msg_count,
105 p_data => x_msg_data
106 );
107
108 WHEN OTHERS THEN
109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
110 ROLLBACK TO create_item_instance;
111 IF FND_MSG_PUB.Check_Msg_Level
112 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
113 THEN
114 FND_MSG_PUB.Add_Exc_Msg
115 (G_PKG_NAME,
116 l_api_name
117 );
118 END IF;
119 FND_MSG_PUB.Count_And_Get
120 ( p_count => x_msg_count,
121 p_data => x_msg_data
122 );
123
124 END create_item_instance;
125
126 /*----------------------------------------------------*/
127 /* procedure name: getContracts */
128 /* description : procedure used to */
129 /* get the contract details */
130 /*----------------------------------------------------*/
131 PROCEDURE getContracts
132 (
133 product_id IN Number
134 ,x_return_status OUT NOCOPY Varchar2
135 ,x_msg_count OUT NOCOPY Number
136 ,x_msg_data OUT NOCOPY Varchar2
137 ,x_output_contracts OUT NOCOPY csi_output_tbl_ib
138 )
139 IS
140 l_api_name CONSTANT VARCHAR2(30) := 'getContracts';
141 l_api_version CONSTANT NUMBER := 1.0;
142 l_inp_rec oks_entitlements_pub.input_rec_ib;
143 l_output_contracts oks_entitlements_pub.output_tbl_ib;
144 l_index number;
145 l_flag VARCHAR2(2);
146 l_debug_level NUMBER;
147
148 Begin
149 -- Check the profile option debug_level for debug message reporting
150 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
151
152 fnd_profile.get('CSI_IB_SHOW_ALL_CONTRACTS', l_flag );
153 l_flag := nvl(l_flag, 'N');
154 l_inp_rec.validate_flag := l_flag;
155 l_inp_rec.product_id := product_id;
156 l_inp_rec.calc_resptime_flag := 'N';
157
158 -- If debug_level = 1 then dump the procedure name
159 IF (l_debug_level > 0) THEN
160 csi_gen_utility_pvt.put_line( 'getContracts');
161 END IF;
162
163 -- If the debug level = 2 then dump all the parameters values.
164 IF (l_debug_level > 1) THEN
165 csi_gen_utility_pvt.put_line( 'getContracts');
166 csi_gen_utility_pvt.put_line('Dumping the values passed to OKS_ENTITLEMENTS_PUB.GET_CONTRACTS:');
167 csi_gen_utility_pvt.put_line('Instance_id :'||l_inp_rec.product_id);
168 csi_gen_utility_pvt.put_line('validate_flag :'||l_inp_rec.validate_flag);
169 csi_gen_utility_pvt.put_line('calc_resptime_flag :'||l_inp_rec.calc_resptime_flag);
170 END IF;
171
172 OKS_ENTITLEMENTS_PUB.GET_CONTRACTS( p_api_version => 1.0,
173 p_init_msg_list => 'T',
174 p_inp_rec => l_inp_rec,
175 x_return_status => x_return_status,
176 x_msg_count => x_msg_count,
177 x_msg_data => x_msg_data,
178 x_ent_contracts => l_output_contracts);
179 IF 0 < l_output_contracts.count() THEN
180 l_index := l_output_contracts.FIRST;
181 LOOP
182 x_output_contracts(l_index).contract_id := l_output_contracts(l_index).contract_id;
183 x_output_contracts(l_index).contract_number := l_output_contracts(l_index).contract_number;
184 x_output_contracts(l_index).contract_number_modifier := l_output_contracts(l_index).contract_number_modifier;
185 -- x_output_contracts(l_index).sts_code := l_output_contracts(l_index).sts_code;
186 -- bug# 2620148, need to return translated status, not status id
187 -- xili 10/24/2002
188 -- bug 4348762, should store status meaning in status_text, not sts_code which has 30 char limit
189 -- xili 5/6/2005
190 x_output_contracts(l_index).sts_code := l_output_contracts(l_index).sts_code;
191 select meaning into x_output_contracts(l_index).status_text from OKC_STATUSES_V where code = l_output_contracts(l_index).sts_code;
192 x_output_contracts(l_index).service_line_id := l_output_contracts(l_index).service_line_id;
193 x_output_contracts(l_index).service_name := l_output_contracts(l_index).service_name;
194 x_output_contracts(l_index).service_description := l_output_contracts(l_index).service_description;
195 x_output_contracts(l_index).coverage_term_line_id := l_output_contracts(l_index).coverage_term_line_id;
196 x_output_contracts(l_index).Coverage_term_name := l_output_contracts(l_index).Coverage_term_name;
197 x_output_contracts(l_index).coverage_term_description := l_output_contracts(l_index).coverage_term_description;
198 x_output_contracts(l_index).service_start_date := l_output_contracts(l_index).service_start_date;
199 x_output_contracts(l_index).service_END_date := l_output_contracts(l_index).service_END_date;
200 x_output_contracts(l_index).warranty_flag := l_output_contracts(l_index).warranty_flag;
201 x_output_contracts(l_index).eligible_for_entitlement := l_output_contracts(l_index).eligible_for_entitlement;
202 x_output_contracts(l_index).date_terminated := l_output_contracts(l_index).date_terminated;
203
204 EXIT WHEN l_index = l_output_contracts.LAST;
205 l_index := l_output_contracts.NEXT(l_index);
206 END LOOP;
207 END IF;
208 FND_MSG_PUB.Count_And_Get
209 (p_count => x_msg_count ,
210 p_data => x_msg_data
211 );
212
213 EXCEPTION
214
215 WHEN NO_DATA_FOUND THEN
216 NULL;
217
218 WHEN FND_API.G_EXC_ERROR THEN
219 x_return_status := FND_API.G_RET_STS_ERROR ;
220 -- ROLLBACK TO create_item_instance;
221 FND_MSG_PUB.Count_And_Get
222 ( p_count => x_msg_count,
223 p_data => x_msg_data
224 );
225
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
228 -- ROLLBACK TO create_item_instance;
229 FND_MSG_PUB.Count_And_Get
230 ( p_count => x_msg_count,
231 p_data => x_msg_data
232 );
233 WHEN OTHERS THEN
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
235 -- ROLLBACK TO create_item_instance;
236 IF FND_MSG_PUB.Check_Msg_Level
237 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
238 THEN
239 FND_MSG_PUB.Add_Exc_Msg
240 (G_PKG_NAME,
241 l_api_name
242 );
243 END IF;
244 FND_MSG_PUB.Count_And_Get
245 ( p_count => x_msg_count,
246 p_data => x_msg_data
247 );
248 END getContracts;
249
250 /*------------------------------------------------------*/
251 /* procedure name: copy_item_instance */
252 /* description : Copies an instace from an instance */
253 /* */
254 /* */
255 /*------------------------------------------------------*/
256
257 PROCEDURE copy_item_instance
258 (
259 p_api_version IN NUMBER
260 ,p_commit IN VARCHAR2
261 ,p_init_msg_list IN VARCHAR2
262 ,p_validation_level IN NUMBER
263 ,p_source_instance_rec IN csi_datastructures_pub.instance_rec
264 ,p_copy_ext_attribs IN VARCHAR2
265 ,p_copy_org_assignments IN VARCHAR2
266 ,p_copy_parties IN VARCHAR2
267 ,p_copy_contacts IN VARCHAR2
268 ,p_copy_accounts IN VARCHAR2
269 ,p_copy_asset_assignments IN VARCHAR2
270 ,p_copy_pricing_attribs IN VARCHAR2
271 ,p_copy_inst_children IN VARCHAR2
272 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
273 ,x_new_instance_tbl OUT NOCOPY csi_datastructures_pub.instance_tbl
274 ,x_return_status OUT NOCOPY VARCHAR2
275 ,x_msg_count OUT NOCOPY NUMBER
276 ,x_msg_data OUT NOCOPY VARCHAR2
277 )
278 IS
279 l_api_name CONSTANT VARCHAR2(30) := 'copy_item_instance';
280 l_api_version CONSTANT NUMBER := 1.0;
281 BEGIN
282 SAVEPOINT copy_item_instance;
283
284 csi_item_instance_pub.copy_item_instance(
285 p_api_version => p_api_version,
286 p_commit => p_commit,
287 p_init_msg_list => p_init_msg_list,
288 p_validation_level => p_validation_level,
289 p_source_instance_rec => p_source_instance_rec,
290 p_copy_ext_attribs => p_copy_ext_attribs,
291 p_copy_org_assignments => p_copy_org_assignments,
292 p_copy_parties => p_copy_parties,
293 p_copy_party_contacts => p_copy_contacts,
294 p_copy_accounts => p_copy_accounts,
295 p_copy_asset_assignments => p_copy_asset_assignments,
296 p_copy_pricing_attribs => p_copy_pricing_attribs,
297 p_copy_inst_children => p_copy_inst_children,
298 p_txn_rec => p_txn_rec,
299 x_new_instance_tbl => x_new_instance_tbl,
300 x_return_status => x_return_status,
301 x_msg_count => x_msg_count,
302 x_msg_data => x_msg_data);
303
304 FND_MSG_PUB.Count_And_Get
305 (p_count => x_msg_count ,
306 p_data => x_msg_data
307 );
308
309 EXCEPTION
310
311 WHEN FND_API.G_EXC_ERROR THEN
312 x_return_status := FND_API.G_RET_STS_ERROR ;
313 ROLLBACK TO copy_item_instance;
314 FND_MSG_PUB.Count_And_Get
315 ( p_count => x_msg_count,
316 p_data => x_msg_data
317 );
318
319 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
321 ROLLBACK TO copy_item_instance;
322 FND_MSG_PUB.Count_And_Get
323 ( p_count => x_msg_count,
324 p_data => x_msg_data
325 );
326
327 WHEN OTHERS THEN
328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
329 ROLLBACK TO copy_item_instance;
330 IF FND_MSG_PUB.Check_Msg_Level
331 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
332 THEN
333 FND_MSG_PUB.Add_Exc_Msg
334 (G_PKG_NAME,
335 l_api_name
336 );
337 END IF;
338 FND_MSG_PUB.Count_And_Get
339 ( p_count => x_msg_count,
340 p_data => x_msg_data
341 );
342 END copy_item_instance;
343
344
345 /*--------------------------------------------------------*/
346 /* Procedure name: Split_Item_Instance */
347 /* Description : This procedure is used to create split*/
348 /* lines for instance */
349 /*--------------------------------------------------------*/
350
351
352 PROCEDURE Split_Item_Instance
353 (
354 p_api_version IN NUMBER
355 ,p_commit IN VARCHAR2
356 ,p_init_msg_list IN VARCHAR2
357 ,p_validation_level IN NUMBER
358 ,p_source_instance_rec IN OUT NOCOPY csi_datastructures_pub.instance_rec
359 ,p_quantity1 IN NUMBER
360 ,p_quantity2 IN NUMBER
361 ,p_copy_ext_attribs IN VARCHAR2
362 ,p_copy_org_assignments IN VARCHAR2
363 ,p_copy_parties IN VARCHAR2
364 ,p_copy_accounts IN VARCHAR2
365 ,p_copy_asset_assignments IN VARCHAR2
366 ,p_copy_pricing_attribs IN VARCHAR2
367 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
368 ,x_new_instance_rec OUT NOCOPY csi_datastructures_pub.instance_rec
369 ,x_return_status OUT NOCOPY VARCHAR2
370 ,x_msg_count OUT NOCOPY NUMBER
371 ,x_msg_data OUT NOCOPY VARCHAR2
372 )
373 IS
374 l_api_name CONSTANT VARCHAR2(30) := 'SPLIT_ITEM_INSTANCE';
375 l_api_version CONSTANT NUMBER := 1.0;
376
377 BEGIN
378 SAVEPOINT split_item_instance;
379
380 csi_item_instance_pvt.split_item_instance(
381 p_api_version,
382 p_commit,
383 p_init_msg_list,
384 p_validation_level,
385 p_source_instance_rec,
386 p_quantity1,
387 p_quantity2,
388 p_copy_ext_attribs,
389 p_copy_org_assignments,
390 p_copy_parties,
391 p_copy_accounts,
392 p_copy_asset_assignments,
393 p_copy_pricing_attribs,
394 p_txn_rec,
395 x_new_instance_rec,
396 x_return_status,
397 x_msg_count,
398 x_msg_data);
399
400 FND_MSG_PUB.Count_And_Get
401 (p_count => x_msg_count ,
402 p_data => x_msg_data
403 );
404
405 EXCEPTION
406
407 WHEN FND_API.G_EXC_ERROR THEN
408 x_return_status := FND_API.G_RET_STS_ERROR ;
409 ROLLBACK TO split_item_instance;
410 FND_MSG_PUB.Count_And_Get
411 ( p_count => x_msg_count,
412 p_data => x_msg_data
413 );
414
415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
417 ROLLBACK TO split_item_instance;
418 FND_MSG_PUB.Count_And_Get
419 ( p_count => x_msg_count,
420 p_data => x_msg_data
421 );
422
423 WHEN OTHERS THEN
424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
425 ROLLBACK TO split_item_instance;
426 IF FND_MSG_PUB.Check_Msg_Level
427 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
428 THEN
429 FND_MSG_PUB.Add_Exc_Msg
430 (G_PKG_NAME,
431 l_api_name
432 );
433 END IF;
434 FND_MSG_PUB.Count_And_Get
435 ( p_count => x_msg_count,
436 p_data => x_msg_data
437 );
438
439 END Split_Item_Instance;
440
441 /*---------------------------------------------------*/
442 /* Procedure name: Split_Item_Instance_lines */
443 /* Description : This procedure is used to create */
444 /* split lines for instance */
445 /*---------------------------------------------------*/
446 PROCEDURE Split_Item_Instance_Lines
447 (
448 p_api_version IN NUMBER
449 ,p_commit IN VARCHAR2
450 ,p_init_msg_list IN VARCHAR2
451 ,p_validation_level IN NUMBER
452 ,p_source_instance_rec IN OUT NOCOPY csi_datastructures_pub.instance_rec
453 ,p_copy_ext_attribs IN VARCHAR2
454 ,p_copy_org_assignments IN VARCHAR2
455 ,p_copy_parties IN VARCHAR2
456 ,p_copy_accounts IN VARCHAR2
457 ,p_copy_asset_assignments IN VARCHAR2
458 ,p_copy_pricing_attribs IN VARCHAR2
459 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
460 ,x_new_instance_tbl OUT NOCOPY csi_datastructures_pub.instance_tbl
461 ,x_return_status OUT NOCOPY VARCHAR2
462 ,x_msg_count OUT NOCOPY NUMBER
463 ,x_msg_data OUT NOCOPY VARCHAR2
464 )
465 IS
466 l_api_name CONSTANT VARCHAR2(30) := 'SPLIT_ITEM_INSTANCE_LINES';
467 l_api_version CONSTANT NUMBER := 1.0;
468 BEGIN
469
470 SAVEPOINT split_item_instance_lines;
471
472 csi_item_instance_pvt.split_item_instance_lines(
473 p_api_version,
474 p_commit,
475 p_init_msg_list,
476 p_validation_level,
477 p_source_instance_rec,
478 p_copy_ext_attribs,
479 p_copy_org_assignments,
480 p_copy_parties,
481 p_copy_accounts,
482 p_copy_asset_assignments,
483 p_copy_pricing_attribs,
484 p_txn_rec,
485 x_new_instance_tbl,
486 x_return_status,
487 x_msg_count,
488 x_msg_data);
489
490 FND_MSG_PUB.Count_And_Get
491 (p_count => x_msg_count ,
492 p_data => x_msg_data
493 );
494
495 EXCEPTION
496
497 WHEN FND_API.G_EXC_ERROR THEN
498 x_return_status := FND_API.G_RET_STS_ERROR ;
499 ROLLBACK TO split_item_instance_lines;
500 FND_MSG_PUB.Count_And_Get
501 ( p_count => x_msg_count,
502 p_data => x_msg_data
503 );
504
505 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
507 ROLLBACK TO split_item_instance_lines;
508 FND_MSG_PUB.Count_And_Get
509 ( p_count => x_msg_count,
510 p_data => x_msg_data
511 );
512
513 WHEN OTHERS THEN
514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
515 ROLLBACK TO split_item_instance_lines;
516 IF FND_MSG_PUB.Check_Msg_Level
517 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
518 THEN
519 FND_MSG_PUB.Add_Exc_Msg
520 (G_PKG_NAME,
521 l_api_name
522 );
523 END IF;
524 FND_MSG_PUB.Count_And_Get
525 ( p_count => x_msg_count,
526 p_data => x_msg_data
527 );
528
529 END Split_Item_Instance_Lines;
530
531 /*----------------------------------------------------*/
532 /* procedure name: Get_Coverage_For_Prod_Sch */
533 /* description : procedure used to get contract */
534 /* coverage info for product search on*/
535 /* a given contract number */
536 /*----------------------------------------------------*/
537 PROCEDURE Get_Coverage_For_Prod_Sch
538 (
539 contract_number IN VARCHAR2
540 ,x_coverage_tbl OUT NOCOPY csi_coverage_tbl_ib
541 ,x_sequence_id OUT NOCOPY NUMBER
542 ,x_return_status OUT NOCOPY Varchar2
543 ,x_msg_count OUT NOCOPY Number
544 ,x_msg_data OUT NOCOPY Varchar2
545 )
546 IS
547 l_api_name CONSTANT VARCHAR2(30) := 'Get_Coverage_For_Prod_Sch';
548 l_api_version CONSTANT NUMBER := 1.0;
549 l_count NUMBER := 0;
550 l_flag VARCHAR2(2);
551 l_rec_count NUMBER := 1;
552 l_return_status VARCHAR2(1);
553 l_ent_contracts OKS_ENTITLEMENTS_PUB.ent_cont_tbl;
554 l_inp_rec OKS_ENTITLEMENTS_PUB.inp_cont_rec;
555 l_debug_level NUMBER;
556
557 l_Seq Number;
558 l_Creation_Date DATE ;
559 l_search_oks_temp csi_search_oks_temp%ROWTYPE;
560 l_found BOOLEAN;
561
562 Cursor c_search_oks_temp IS
563 Select *
564 From csi_search_oks_temp
565 Where creation_date < sysdate
566 and rownum <= 1
567 For Update NoWait;
568
569 BEGIN
570 fnd_profile.get('CSI_IB_SHOW_ALL_CONTRACTS', l_flag );
571
572 -- Check the profile option debug_level for debug message reporting
573 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
574
575 l_flag := nvl(l_flag, 'N');
576
577 l_inp_rec.contract_number := contract_number;
578 l_inp_rec.party_id := NULL;
579 l_inp_rec.site_id := NULL;
580 l_inp_rec.cust_acct_id := NULL;
581 l_inp_rec.system_id := NULL;
582 l_inp_rec.item_id := NULL;
583 l_inp_rec.product_id := NULL;
584 l_inp_rec.request_date := sysdate;
585 l_inp_rec.validate_flag := l_flag;
586
587 -- If debug_level = 1 then dump the procedure name
588 IF (l_debug_level > 0) THEN
589 csi_gen_utility_pvt.put_line( 'Get_Coverage_For_Prod_Sch');
590 END IF;
591
592 /*-- Purging Temp Table before Further Operation bug 4736062-- */
593 OPEN c_search_oks_temp;
594 FETCH c_search_oks_temp INTO l_search_oks_temp;
595 IF c_search_oks_temp%FOUND THEN
596 DELETE csi_search_oks_temp
597 WHERE creation_date < sysdate -1;
598 END IF;
599 CLOSE c_search_oks_temp;
600 --- End changes for bug 4736062
601
602 OKS_ENTITLEMENTS_PUB.GET_CONTRACTS( p_api_version => 1.0,
603 p_init_msg_list => 'T',
604 p_inp_rec => l_inp_rec,
605 x_return_status => l_return_status,
606 x_msg_count => x_msg_count,
607 x_msg_data => x_msg_data,
608 x_ent_contracts => l_ent_contracts);
609 x_return_status := l_return_status;
610 IF ( l_return_status ) <> 'S' Then
611 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
612 End IF;
613 /*-- bug 4736062
614 -- throw exception when there are too many coverage lines
615 IF ( l_ent_contracts.LAST ) > 5000 Then
616 FND_MESSAGE.SET_NAME('CSI','CSI_CANT_SEARCH_BY_CONTR_NUM');
617 FND_MSG_PUB.ADD;
618 RAISE FND_API.G_EXC_ERROR;
619 End IF;
620 */
621 -- If the debug level = 2 then dump all the output data.
622 IF (l_debug_level > 1) THEN
623 csi_gen_utility_pvt.put_line('OKS_ENTITLEMENTS_PUB.GET_CONTRACTS() call succeeds:');
624 csi_gen_utility_pvt.put_line('Dumping the values gotten from OKS_ENTITLEMENTS_PUB.GET_CONTRACTS():');
625 csi_gen_utility_pvt.put_line('l_ent_contracts.count :'||l_ent_contracts.count);
626 END IF;
627
628 l_rec_count := l_ent_contracts.FIRST;
629
630 IF l_rec_count > 0 THEN
631 SELECT csi_search_oks_temp_S.NEXTVAL, sysdate
632 INTO l_Seq, l_Creation_Date
633 FROM dual;
634 x_sequence_id := l_Seq;
635 END IF;
636
637 WHILE l_rec_count is not null
638 LOOP
639 -- x_coverage_tbl(l_rec_count).covered_level_code := l_ent_contracts(l_rec_count).coverage_level_code;
640 -- x_coverage_tbl(l_rec_count).covered_level_id := l_ent_contracts(l_rec_count).coverage_level_id;
641
642 l_found := FALSE;
643 IF x_coverage_tbl.count > 0 THEN
644 FOR j in x_coverage_tbl.first..x_coverage_tbl.last LOOP
645 IF x_coverage_tbl(j).covered_level_code = l_ent_contracts(l_rec_count).coverage_level_code THEN
646 l_found := TRUE;
647 END IF;
648 END LOOP;
649 IF NOT l_found THEN
650 x_coverage_tbl(x_coverage_tbl.count + 1).covered_level_code := l_ent_contracts(l_rec_count).coverage_level_code;
651 END IF;
652
653 ELSE
654 x_coverage_tbl(1).covered_level_code := l_ent_contracts(l_rec_count).coverage_level_code;
655 END IF;
656
657 Insert into csi_search_oks_temp
658 (
659 id,
660 creation_date,
661 covered_level_id,
662 covered_level_code
663 )
664 Values
665 (
666 l_Seq
667 ,l_Creation_Date
668 ,l_ent_contracts(l_rec_count).coverage_level_id
669 ,l_ent_contracts(l_rec_count).coverage_level_code
670 );
671
672 EXIT WHEN l_rec_count = l_ent_contracts.LAST;
673 l_rec_count := l_ent_contracts.NEXT(l_rec_count);
674 END LOOP;
675 COMMIT;
676 FND_MSG_PUB.Count_And_Get
677 (p_count => x_msg_count ,
678 p_data => x_msg_data
679 );
680
681 EXCEPTION
682
683 WHEN FND_API.G_EXC_ERROR THEN
684 x_return_status := FND_API.G_RET_STS_ERROR ;
685 FND_MSG_PUB.Count_And_Get
686 ( p_count => x_msg_count,
687 p_data => x_msg_data
688 );
689
690 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
692 FND_MSG_PUB.Count_And_Get
693 ( p_count => x_msg_count,
694 p_data => x_msg_data
695 );
696
697 WHEN OTHERS THEN
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
699 IF FND_MSG_PUB.Check_Msg_Level
700 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
701 THEN
702 FND_MSG_PUB.Add_Exc_Msg
703 (G_PKG_NAME,
704 l_api_name
705 );
706 END IF;
707 FND_MSG_PUB.Count_And_Get
708 ( p_count => x_msg_count,
709 p_data => x_msg_data
710 );
711
712 END Get_Coverage_For_Prod_Sch;
713
714 /*----------------------------------------------------*/
715 /* procedure name: Get_Contract_Where_Clause */
716 /* description : procedure used to get Product */
717 /* Search where clause for a given */
718 /* contract number */
719 /*----------------------------------------------------*/
720 PROCEDURE Get_Contract_Where_Clause
721 (
722 contract_number IN VARCHAR2
723 ,instance_table_name IN VARCHAR2
724 ,x_where_clause OUT NOCOPY VARCHAR2
725 ,x_return_status OUT NOCOPY Varchar2
726 ,x_msg_count OUT NOCOPY Number
727 ,x_msg_data OUT NOCOPY Varchar2
728 )
729 IS
730 l_api_name CONSTANT VARCHAR2(30) := 'Get_Contract_Where_Clause';
731 l_api_version CONSTANT NUMBER := 1.0;
732 l_count NUMBER := 0;
733 l_contract_where_clause VARCHAR2(4000) DEFAULT NULL;
734 l_covered_party_id VARCHAR2(4000) DEFAULT NULL;
735 l_covered_site_id VARCHAR2(4000) DEFAULT NULL;
736 l_covered_acct_id VARCHAR2(4000) DEFAULT NULL;
737 l_covered_system_id VARCHAR2(4000) DEFAULT NULL;
738 l_covered_item_id VARCHAR2(4000) DEFAULT NULL;
739 l_covered_cp_id VARCHAR2(4000) DEFAULT NULL;
740 l_covered_level_code OKC_LINE_STYLES_B.LTY_CODE%TYPE;
741 l_coverage_level_id NUMBER;
742 l_flag VARCHAR2(2);
743 l_rec_count NUMBER := 1;
744 l_row_count NUMBER;
745 l_return_status VARCHAR2(1);
746 l_ent_contracts OKS_ENTITLEMENTS_PUB.ent_cont_tbl;
747 l_inp_rec OKS_ENTITLEMENTS_PUB.inp_cont_rec;
748 l_debug_level NUMBER;
749 l_instance_table_name VARCHAR2(200);
750 BEGIN
751 fnd_profile.get('CSI_IB_SHOW_ALL_CONTRACTS', l_flag );
752
753 -- Check the profile option debug_level for debug message reporting
754 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
755
756 l_flag := nvl(l_flag, 'N');
757
758 l_inp_rec.contract_number := contract_number;
759 --l_inp_rec.service_line_id := NULL;
760 l_inp_rec.party_id := NULL;
761 l_inp_rec.site_id := NULL;
762 l_inp_rec.cust_acct_id := NULL;
763 l_inp_rec.system_id := NULL;
764 l_inp_rec.item_id := NULL;
765 l_inp_rec.product_id := NULL;
766 l_inp_rec.request_date := sysdate;
767 l_inp_rec.validate_flag := l_flag;
768
769 -- If debug_level = 1 then dump the procedure name
770 IF (l_debug_level > 0) THEN
771 csi_gen_utility_pvt.put_line( 'Get_Contract_Where_Clause');
772 END IF;
773
774 -- If the debug level = 2 then dump all the parameters values.
775 IF (l_debug_level > 1) THEN
776 csi_gen_utility_pvt.put_line( 'Get_Contract_Where_Clause');
777 csi_gen_utility_pvt.put_line('Dumping the values passed to OKS_ENTITLEMENTS_PUB.GET_CONTRACTS():');
778 csi_gen_utility_pvt.put_line('contract_number :'||l_inp_rec.contract_number);
779 csi_gen_utility_pvt.put_line('request_date :'||l_inp_rec.request_date);
780 csi_gen_utility_pvt.put_line('validate_flag :'||l_inp_rec.validate_flag);
781 END IF;
782
783
784 OKS_ENTITLEMENTS_PUB.GET_CONTRACTS( p_api_version => 1.0,
785 p_init_msg_list => 'T',
786 p_inp_rec => l_inp_rec,
787 x_return_status => l_return_status,
788 x_msg_count => x_msg_count,
789 x_msg_data => x_msg_data,
790 x_ent_contracts => l_ent_contracts);
791 x_return_status := l_return_status;
792 IF ( l_return_status ) <> 'S' Then
793 /*
794 IF ( FND_MSG_PUB.Count_Msg > 0 ) Then
795 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
796 FND_MSG_PUB.Get(p_msg_index => i,
797 p_encoded => 'F',
798 p_data => l_msg_data,
799 p_msg_index_out => l_msg_index_out );
800 fnd_message.set_string(l_msg_data);
801 fnd_message.error;
802 End LOOP;
803 End IF;
804 */
805 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
806 End IF;
807
808 -- fnd_message.debug('l_rec_count = '||to_char(l_rec_count));
809
810 -- If the debug level = 2 then dump all the output data.
811 IF (l_debug_level > 1) THEN
812 csi_gen_utility_pvt.put_line('OKS_ENTITLEMENTS_PUB.GET_CONTRACTS() call succeeds:');
813 csi_gen_utility_pvt.put_line('Dumping the values gotten from OKS_ENTITLEMENTS_PUB.GET_CONTRACTS():');
814 csi_gen_utility_pvt.put_line('l_ent_contracts.count :'||l_ent_contracts.count);
815 END IF;
816
817 l_row_count := 1;
818 l_rec_count := l_ent_contracts.FIRST;
819
820 -- Bug 1783709 srramakr. When queried by contract #, system gives value error.
821 -- This is becoz the CPs returned by OKS may not fit in the valriable used to build the WHERE clause.
822 -- So, we just give a meaningful message.
823 WHILE l_rec_count is not null
824 LOOP
825 Begin
826
827 l_covered_level_code := l_ent_contracts(l_rec_count).coverage_level_code ;
828 l_coverage_level_id := l_ent_contracts(l_rec_count).coverage_level_id ;
829
830 -- If the debug level = 2 then dump each output record.
831 IF (l_debug_level > 1) THEN
832 csi_gen_utility_pvt.put_line('---- Contract Record ' || l_rec_count || '----');
833 csi_gen_utility_pvt.put_line('l_covered_level_code :'||l_covered_level_code);
834 csi_gen_utility_pvt.put_line('l_coverage_level_id :'||to_char(l_coverage_level_id));
835 END IF;
836 -- fnd_message.debug('l_covered_level_code = '||l_ent_contracts(l_rec_count).coverage_level_code);
837 -- fnd_message.debug('l_covered_level_id = '||l_ent_contracts(l_rec_count).coverage_level_id);
838
839 IF (l_covered_level_code = 'COVER_PTY') Then
840
841 IF l_covered_party_id is NULL THEN
842
843 l_covered_party_id := to_char(l_coverage_level_id) ;
844 ELSE
845 l_covered_party_id := l_covered_party_id ||','||to_char(l_coverage_level_id) ;
846
847 END IF;
848 Elsif (l_covered_level_code = 'COVER_SITE') Then
849
850 IF l_covered_site_id IS NULL THEN
851
852 l_covered_site_id := to_char(l_coverage_level_id) ;
853
854 ELSE
855 l_covered_site_id := l_covered_site_id ||','||to_char(l_coverage_level_id) ;
856
857 END IF;
858 Elsif (l_covered_level_code = 'COVER_CUST') Then
859
860 IF l_covered_acct_id IS NULL THEN
861
862 l_covered_acct_id := to_char(l_coverage_level_id) ;
863 ELSE
864 l_covered_acct_id := l_covered_acct_id ||','||to_char(l_coverage_level_id) ;
865
866 END IF;
867 Elsif (l_covered_level_code = 'COVER_SYS') Then
868
869 IF l_covered_system_id IS NULL THEN
870 l_covered_system_id := to_char(l_coverage_level_id) ;
871 ELSE
872 l_covered_system_id := l_covered_system_id ||','||to_char(l_coverage_level_id) ;
873 END IF;
874
875 Elsif (l_covered_level_code = 'COVER_ITEM') Then
876
877 IF l_covered_item_id IS NULL THEN
878 l_covered_item_id := to_char(l_coverage_level_id ) ;
879 ELSE
880 l_covered_item_id := l_covered_item_id ||','||to_char(l_coverage_level_id ) ;
881 END IF;
882
883 Elsif (l_covered_level_code = 'COVER_PROD') Then
884
885 IF l_covered_cp_id IS NULL THEN
886 l_covered_cp_id := to_char(l_coverage_level_id) ;
887 ELSE
888 l_covered_cp_id := l_covered_cp_id ||','||to_char(l_coverage_level_id) ;
889 END IF;
890 End IF;
891
892 /*
893 fnd_message.debug('l_covered_party_id = '||l_covered_party_id);
894 fnd_message.debug('l_covered_site_id = '||l_covered_site_id);
895 fnd_message.debug('l_covered_acct_id = '||l_covered_acct_id);
896 fnd_message.debug('l_covered_system_id= '||l_covered_system_id);
897 fnd_message.debug('l_covered_item_id = '||l_covered_item_id);
898 */
899 l_rec_count := l_ent_contracts.NEXT(l_rec_count);
900 End;
901 End LOOP;
902
903 -- If the debug level = 2 then dump each output record.
904 IF (l_debug_level > 1) THEN
905 csi_gen_utility_pvt.put_line('l_covered_party_id :'||l_covered_party_id);
906 csi_gen_utility_pvt.put_line('l_covered_site_id :'||l_covered_site_id);
907 csi_gen_utility_pvt.put_line('l_covered_acct_id :'||l_covered_acct_id);
908 csi_gen_utility_pvt.put_line('l_covered_system_id :'||l_covered_system_id);
909 csi_gen_utility_pvt.put_line('l_covered_item_id :'||l_covered_item_id);
910 END IF;
911
912 l_instance_table_name := ' ' || instance_table_name;
913 IF (length(instance_table_name) > 0 ) THEN
914 l_instance_table_name := l_instance_table_name || '.';
915 END IF;
916
917 IF (l_covered_party_id IS NOT NULL) Then
918
919 IF l_contract_where_clause IS NULL THEN
920
921 l_contract_where_clause := l_instance_table_name || 'instance_id in ( select instance_id from csi_i_parties where party_id in ( '
922 ||l_covered_party_id||' ) )' ;
923
924 ELSE
925 l_contract_where_clause := l_contract_where_clause ||' '||' AND'||
926 l_instance_table_name || 'instance_id in ( select instance_id from csi_i_parties where party_id in ( '
927 ||l_covered_party_id||' ) )' ;
928 END IF;
929 END IF;
930
931 IF (l_covered_site_id IS NOT NULL ) Then
932
933 IF l_contract_where_clause IS NULL THEN
934 l_contract_where_clause := l_instance_table_name || 'install_location_type_code = ''HZ_PARTY_SITES'' and '
935 || l_instance_table_name || 'install_location_id IN ( '
936 ||l_covered_site_id ||' )' ;
937
938 ELSE
939 l_contract_where_clause := l_contract_where_clause ||' '||' AND'||
940 l_instance_table_name || 'install_location_type_code = ''HZ_PARTY_SITES'' and '
941 || l_instance_table_name || 'install_location_id IN ( '
942 ||l_covered_site_id ||' )' ;
943 END IF;
944
945 END IF;
946
947 IF (l_covered_acct_id IS NOT NULL) Then
948
949 IF l_contract_where_clause IS NULL THEN
950 l_contract_where_clause := l_instance_table_name || 'instance_id in ( select instance_id from ' ||
951 ' csi_i_parties p, csi_ip_accounts a where a.instance_party_id = p.instance_party_id ' ||
952 ' and a.party_account_id IN ( '||l_covered_acct_id ||' ) )' ;
953 ELSE
954 l_contract_where_clause := l_contract_where_clause ||' '||' AND'||
955 l_instance_table_name || 'instance_id in ( select instance_id from ' ||
956 ' csi_i_parties p, csi_ip_accounts a where a.instance_party_id = p.instance_party_id ' ||
957 ' and a.party_account_id IN ( '||l_covered_acct_id ||' ) )' ;
958 END IF;
959
960 END IF;
961
962 IF (l_covered_system_id IS NOT NULL) Then
963
964 IF l_contract_where_clause IS NULL THEN
965
966 l_contract_where_clause := l_instance_table_name || 'system_id IN ( '||l_covered_system_id ||' )' ;
967 ELSE
968 l_contract_where_clause := l_contract_where_clause ||' '||' AND'||
969 l_instance_table_name || 'system_id IN ( '||l_covered_system_id ||' )' ;
970 END IF;
971 END IF;
972
973 IF (l_covered_item_id IS NOT NULL) Then
974 IF l_contract_where_clause IS NULL THEN
975 l_contract_where_clause := l_instance_table_name || 'inventory_item_id IN ( '||l_covered_item_id ||' )' ;
976 ELSE
977 l_contract_where_clause := l_contract_where_clause ||' '||' AND'||
978 l_instance_table_name || 'inventory_item_id IN ( '||l_covered_item_id ||' )' ;
979 END IF;
980 END IF;
981
982 IF (l_covered_cp_id IS NOT NULL) Then
983 IF l_contract_where_clause IS NULL THEN
984 l_contract_where_clause := l_instance_table_name || 'instance_id IN ( ' ||l_covered_cp_id ||' )' ;
985 ELSE
986 l_contract_where_clause := l_contract_where_clause ||' '||' AND'||
987 l_instance_table_name || 'instance_id IN ( ' ||l_covered_cp_id ||' )' ;
988 END IF;
989 End IF;
990
991 IF l_contract_where_clause is NULL THEN
992
993 l_contract_where_clause := l_instance_table_name || 'instance_id = -999 ';
994
995
996 ELSE
997 l_contract_where_clause := ' ( '||l_contract_where_clause||' )' ;
998
999 END IF;
1000
1001 -- fnd_message.debug(' l_contract_where_clause ='|| l_contract_where_clause );
1002
1003 -- contract where clause has been successfully contructed
1004 x_where_clause := l_contract_where_clause;
1005
1006 EXCEPTION
1007
1008 WHEN FND_API.G_EXC_ERROR THEN
1009 x_return_status := FND_API.G_RET_STS_ERROR ;
1010 FND_MSG_PUB.Count_And_Get
1011 ( p_count => x_msg_count,
1012 p_data => x_msg_data
1013 );
1014
1015 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1016 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1017 FND_MSG_PUB.Count_And_Get
1018 ( p_count => x_msg_count,
1019 p_data => x_msg_data
1020 );
1021
1022 WHEN OTHERS THEN
1023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1024 IF FND_MSG_PUB.Check_Msg_Level
1025 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1026 THEN
1027 FND_MSG_PUB.Add_Exc_Msg
1028 (G_PKG_NAME,
1029 l_api_name
1030 );
1031 END IF;
1032 FND_MSG_PUB.Count_And_Get
1033 ( p_count => x_msg_count,
1034 p_data => x_msg_data
1035 );
1036
1037 END Get_Contract_Where_Clause;
1038
1039 /*---------------------------------------------------*/
1040 /* procedure name: get_history_transactions */
1041 /* description : Retreive history transactions */
1042 /* */
1043 /*---------------------------------------------------*/
1044
1045 PROCEDURE get_history_transactions
1046 ( p_api_version IN NUMBER
1047 ,p_commit IN VARCHAR2
1048 ,p_init_msg_list IN VARCHAR2
1049 ,p_validation_level IN NUMBER
1050 ,p_transaction_id IN NUMBER
1051 ,p_instance_id IN NUMBER
1052 ,x_instance_history_tbl OUT NOCOPY csi_datastructures_pub.instance_history_tbl
1053 ,x_party_history_tbl OUT NOCOPY csi_datastructures_pub.party_history_tbl
1054 ,x_account_history_tbl OUT NOCOPY csi_datastructures_pub.account_history_tbl
1055 ,x_org_unit_history_tbl OUT NOCOPY csi_datastructures_pub.org_units_history_tbl
1056 ,x_ins_asset_hist_tbl OUT NOCOPY csi_datastructures_pub.ins_asset_history_tbl
1057 ,x_ext_attrib_val_hist_tbl OUT NOCOPY csi_datastructures_pub.ext_attrib_val_history_tbl
1058 ,x_version_label_hist_tbl OUT NOCOPY csi_datastructures_pub.version_label_history_tbl
1059 ,x_rel_history_tbl OUT NOCOPY csi_datastructures_pub.relationship_history_tbl
1060 ,x_return_status OUT NOCOPY VARCHAR2
1061 ,x_msg_count OUT NOCOPY NUMBER
1062 ,x_msg_data OUT NOCOPY VARCHAR2
1063 ) IS
1064 l_dummy VARCHAR2(1);
1065 l_api_name CONSTANT VARCHAR2(30) := 'get_history_transactions';
1066 l_api_version CONSTANT NUMBER := 1.0;
1067 temp_instance_history_tbl csi_datastructures_pub.instance_history_tbl;
1068 temp_ins_asset_hist_tbl csi_datastructures_pub.ins_asset_history_tbl;
1069 temp_party_history_tbl csi_datastructures_pub.party_history_tbl;
1070 temp_account_history_tbl csi_datastructures_pub.account_history_tbl;
1071 temp_org_unit_history_tbl csi_datastructures_pub.org_units_history_tbl;
1072 temp_ext_attrib_val_hist_tbl csi_datastructures_pub.ext_attrib_val_history_tbl;
1073 temp_rel_history_tbl csi_datastructures_pub.relationship_history_tbl;
1074 x_index number;
1075
1076 BEGIN
1077
1078 IF fnd_api.to_boolean(p_commit)
1079 THEN
1080 SAVEPOINT get_history_transactions;
1081 END IF;
1082
1083 -- Standard call to check for call compatibility.
1084 IF NOT FND_API.Compatible_API_Call (l_api_version ,
1085 p_api_version ,
1086 l_api_name ,
1087 G_PKG_NAME )
1088 THEN
1089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1090 END IF;
1091
1092 -- Initialize message list if p_init_msg_list is set to TRUE.
1093 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1094 FND_MSG_PUB.initialize;
1095 END IF;
1096
1097 -- Initialize API return status to success
1098 x_return_status := FND_API.G_RET_STS_SUCCESS;
1099 /** bug 3304439
1100 -- Check for the profile option and enable trace
1101 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
1102 dbms_session.set_sql_trace(TRUE);
1103 END IF;
1104 **/
1105
1106 -- End enable trace
1107
1108 -- Start API body
1109 --
1110
1111 x_index := 1;
1112 BEGIN
1113 SELECT 'x'
1114 INTO l_dummy
1115 FROM csi_item_instances_h
1116 WHERE transaction_id = p_transaction_id
1117 AND ROWNUM = 1;
1118
1119 csi_item_instance_pvt.get_instance_hist
1120 ( p_api_version => 1.0
1121 ,p_commit => fnd_api.g_false
1122 ,p_init_msg_list => fnd_api.g_false
1123 ,p_validation_level => fnd_api.g_valid_level_full
1124 ,p_transaction_id => p_transaction_id
1125 ,x_instance_history_tbl => temp_instance_history_tbl
1126 ,x_return_status => x_return_status
1127 ,x_msg_count => x_msg_count
1128 ,x_msg_data => x_msg_data
1129 );
1130
1131 IF temp_instance_history_tbl.count > 0 THEN
1132 FOR l_ind IN temp_instance_history_tbl.FIRST .. temp_instance_history_tbl.LAST
1133 LOOP
1134 if (temp_instance_history_tbl(l_ind).instance_id = p_instance_id) then
1135 x_instance_history_tbl(x_index) := temp_instance_history_tbl(l_ind);
1136 x_index := x_index +1;
1137 end if;
1138 END LOOP;
1139 END IF;
1140
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 NULL;
1144 END;
1145
1146 x_index := 1;
1147 BEGIN
1148 SELECT 'x'
1149 INTO l_dummy
1150 FROM csi_i_assets_h
1151 WHERE transaction_id = p_transaction_id
1152 AND ROWNUM = 1;
1153 csi_asset_pvt.get_instance_asset_hist
1154 ( p_api_version => 1.0
1155 ,p_commit => fnd_api.g_false
1156 ,p_init_msg_list => fnd_api.g_false
1157 ,p_validation_level => fnd_api.g_valid_level_full
1158 ,p_transaction_id => p_transaction_id
1159 ,x_ins_asset_hist_tbl => temp_ins_asset_hist_tbl
1160 ,x_return_status => x_return_status
1161 ,x_msg_count => x_msg_count
1162 ,x_msg_data => x_msg_data
1163 );
1164
1165 IF temp_ins_asset_hist_tbl.count > 0 THEN
1166 FOR l_ind IN temp_ins_asset_hist_tbl.FIRST .. temp_ins_asset_hist_tbl.LAST
1167 LOOP
1168 if (temp_ins_asset_hist_tbl(l_ind).instance_id = p_instance_id) then
1169 x_ins_asset_hist_tbl(x_index) := temp_ins_asset_hist_tbl(l_ind);
1170 x_index := x_index +1;
1171 end if;
1172 END LOOP;
1173 END IF;
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 NULL;
1177 END;
1178
1179 x_index := 1;
1180 BEGIN
1181 SELECT 'x'
1182 INTO l_dummy
1183 FROM csi_i_parties_h
1184 WHERE transaction_id = p_transaction_id
1185 AND ROWNUM = 1;
1186 csi_party_relationships_pvt.get_inst_party_rel_hist
1187 ( p_api_version => 1.0
1188 ,p_commit => fnd_api.g_false
1189 ,p_init_msg_list => fnd_api.g_false
1190 ,p_validation_level => fnd_api.g_valid_level_full
1191 ,p_transaction_id => p_transaction_id
1192 ,x_party_history_tbl => temp_party_history_tbl
1193 ,x_return_status => x_return_status
1194 ,x_msg_count => x_msg_count
1195 ,x_msg_data => x_msg_data
1196 );
1197
1198 IF temp_party_history_tbl.count > 0 THEN
1199 FOR l_ind IN temp_party_history_tbl.FIRST .. temp_party_history_tbl.LAST
1200 LOOP
1201 if (temp_party_history_tbl(l_ind).instance_id = p_instance_id) then
1202 x_party_history_tbl(x_index) := temp_party_history_tbl(l_ind);
1203 x_index := x_index +1;
1204 end if;
1205 END LOOP;
1206 END IF;
1207
1208 EXCEPTION
1209 WHEN OTHERS THEN
1210 NULL;
1211 END;
1212
1213 x_index := 1;
1214 BEGIN
1215 SELECT 'x'
1216 INTO l_dummy
1217 FROM csi_ip_accounts_h
1218 WHERE transaction_id = p_transaction_id
1219 AND rownum=1;
1220
1221 csi_party_relationships_pvt.get_inst_party_account_hist
1222 ( p_api_version => 1.0
1223 ,p_commit => fnd_api.g_false
1224 ,p_init_msg_list => fnd_api.g_false
1225 ,p_validation_level => fnd_api.g_valid_level_full
1226 ,p_transaction_id => p_transaction_id
1227 ,x_account_history_tbl => temp_account_history_tbl
1228 ,x_return_status => x_return_status
1229 ,x_msg_count => x_msg_count
1230 ,x_msg_data => x_msg_data
1231 );
1232
1233 IF temp_account_history_tbl.count > 0 THEN
1234 FOR l_ind IN temp_account_history_tbl.FIRST .. temp_account_history_tbl.LAST
1235 LOOP
1236 if (temp_account_history_tbl(l_ind).instance_id = p_instance_id) then
1237 x_account_history_tbl(x_index) := temp_account_history_tbl(l_ind);
1238 x_index := x_index +1;
1239 end if;
1240 END LOOP;
1241 END IF;
1242
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 NULL;
1246 END;
1247
1248 x_index := 1;
1249 BEGIN
1250 SELECT 'x'
1251 INTO l_dummy
1252 FROM csi_i_org_assignments_h
1253 WHERE transaction_id = p_transaction_id
1254 AND ROWNUM = 1;
1255
1256 csi_organization_unit_pvt.get_org_unit_history
1257 ( p_api_version => 1.0
1258 ,p_commit => fnd_api.g_false
1259 ,p_init_msg_list => fnd_api.g_false
1260 ,p_validation_level => fnd_api.g_valid_level_full
1261 ,p_transaction_id => p_transaction_id
1262 ,x_org_unit_history_tbl => temp_org_unit_history_tbl
1263 ,x_return_status => x_return_status
1264 ,x_msg_count => x_msg_count
1265 ,x_msg_data => x_msg_data
1266 );
1267
1268 IF temp_org_unit_history_tbl.count > 0 THEN
1269 FOR l_ind IN temp_org_unit_history_tbl.FIRST .. temp_org_unit_history_tbl.LAST
1270 LOOP
1271 if (temp_org_unit_history_tbl(l_ind).instance_id = p_instance_id) then
1272 x_org_unit_history_tbl(x_index) := temp_org_unit_history_tbl(l_ind);
1273 x_index := x_index +1;
1274 end if;
1275 END LOOP;
1276 END IF;
1277
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 NULL;
1281 END;
1282
1283 x_index := 1;
1284 BEGIN
1285 SELECT 'x'
1286 INTO l_dummy
1287 FROM csi_iea_values_h
1288 WHERE transaction_id = p_transaction_id
1289 AND ROWNUM = 1;
1290
1291 csi_item_instance_pvt.get_ext_attrib_val_hist
1292 ( p_api_version => 1.0
1293 ,p_commit => fnd_api.g_false
1294 ,p_init_msg_list => fnd_api.g_false
1295 ,p_validation_level => fnd_api.g_valid_level_full
1296 ,p_transaction_id => p_transaction_id
1297 ,x_ext_attrib_val_hist_tbl => temp_ext_attrib_val_hist_tbl
1298 ,x_return_status => x_return_status
1299 ,x_msg_count => x_msg_count
1300 ,x_msg_data => x_msg_data
1301 );
1302
1303 IF temp_ext_attrib_val_hist_tbl.count > 0 THEN
1304 FOR l_ind IN temp_ext_attrib_val_hist_tbl.FIRST .. temp_ext_attrib_val_hist_tbl.LAST
1305 LOOP
1306 if (temp_ext_attrib_val_hist_tbl(l_ind).instance_id = p_instance_id) then
1307 x_ext_attrib_val_hist_tbl(x_index) := temp_ext_attrib_val_hist_tbl(l_ind);
1308 x_index := x_index +1;
1309 end if;
1310 END LOOP;
1311 END IF;
1312
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 NULL;
1316 END;
1317
1318 x_index := 1;
1319 BEGIN
1320 SELECT 'x'
1321 INTO l_dummy
1322 FROM csi_ii_relationships_h
1323 WHERE transaction_id = p_transaction_id
1324 AND rownum=1;
1325
1326 csi_ii_relationships_pvt.get_inst_relationship_hist
1327 ( p_api_version => 1.0
1328 ,p_commit => fnd_api.g_false
1329 ,p_init_msg_list => fnd_api.g_false
1330 ,p_validation_level => fnd_api.g_valid_level_full
1331 ,p_transaction_id => p_transaction_id
1332 ,x_rel_history_tbl => temp_rel_history_tbl
1333 ,x_return_status => x_return_status
1334 ,x_msg_count => x_msg_count
1335 ,x_msg_data => x_msg_data
1336 );
1337
1338 IF temp_rel_history_tbl.count > 0 THEN
1339 FOR l_ind IN temp_rel_history_tbl.FIRST .. temp_rel_history_tbl.LAST
1340 LOOP
1341 if (temp_rel_history_tbl(l_ind).object_id = p_instance_id) then
1342 x_rel_history_tbl(x_index) := temp_rel_history_tbl(l_ind);
1343 x_index := x_index +1;
1344 end if;
1345 END LOOP;
1346 END IF;
1347
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350 NULL;
1351 END;
1352 /*
1353 BEGIN
1354 SELECT 'x'
1355 INTO l_dummy
1356 FROM csi_i_version_labels_h
1357 WHERE transaction_id = p_transaction_id
1358 AND ROWNUM = 1;
1359
1360 csi_item_instance_pvt.get_version_label_history
1361 ( p_api_version => 1.0
1362 ,p_commit => fnd_api.g_false
1363 ,p_init_msg_list => fnd_api.g_false
1364 ,p_validation_level => fnd_api.g_valid_level_full
1365 ,p_transaction_id => p_transaction_id
1366 ,x_version_label_hist_tbl => x_version_label_hist_tbl
1367 ,x_return_status => x_return_status
1368 ,x_msg_count => x_msg_count
1369 ,x_msg_data => x_msg_data
1370 );
1371
1372 EXCEPTION
1373 WHEN OTHERS THEN
1374 NULL;
1375 END;
1376 */
1377 -- End of API body
1378
1379 -- Standard check of p_commit.
1380 IF FND_API.To_Boolean( p_commit ) THEN
1381 COMMIT WORK;
1382 END IF;
1383 /** bug 3304439
1384 -- Check for the profile option and disable the trace
1385 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
1386 dbms_session.set_sql_trace(false);
1387 END IF;
1388 **/
1389 -- End disable trace
1390
1391 -- Standard call to get message count and if count is get message info.
1392 FND_MSG_PUB.Count_And_Get
1393 (p_count => x_msg_count ,
1394 p_data => x_msg_data );
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1398 IF fnd_api.to_boolean(p_commit)
1399 THEN
1400 ROLLBACK TO get_history_transactions;
1401 END IF;
1402 IF FND_MSG_PUB.Check_Msg_Level
1403 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1404 THEN
1405 FND_MSG_PUB.Add_Exc_Msg
1406 ( G_PKG_NAME, l_api_name );
1407 END IF;
1408 FND_MSG_PUB.Count_And_Get
1409 ( p_count => x_msg_count,
1410 p_data => x_msg_data);
1411
1412 END get_history_transactions ;
1413
1414 Procedure CSI_CONFIG_LAUNCH_PRMS
1415 ( p_api_version IN NUMBER,
1416 p_init_msg_list IN VARCHAR2,
1417 p_commit IN VARCHAR2,
1418 p_validation_level IN NUMBER,
1419 x_return_status OUT NOCOPY VARCHAR2,
1420 x_msg_count OUT NOCOPY NUMBER,
1421 x_msg_data OUT NOCOPY VARCHAR2,
1422 x_configurable OUT NOCOPY VARCHAR2,
1423 x_icx_sessn_tkt OUT NOCOPY VARCHAR2,
1424 x_db_id OUT NOCOPY VARCHAR2,
1425 x_servlet_url OUT NOCOPY VARCHAR2,
1426 x_sysdate OUT NOCOPY VARCHAR2
1427 ) is
1428 l_api_name CONSTANT VARCHAR2(30) := 'Get_Config_Launch_Info';
1429 l_api_version CONSTANT NUMBER := 1.0;
1430
1431 l_resp_id NUMBER;
1432 l_resp_appl_id NUMBER;
1433 l_user_id NUMBER;
1434
1435 BEGIN
1436 l_user_id := fnd_global.user_id;
1437
1438 SAVEPOINT CSI_CONFIG_LAUNCH_PRMS;
1439 -- Standard call to check for call compatibility.
1440 /*IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1441 p_api_version ,
1442 l_api_name ,
1443 G_PKG_NAME )
1444 THEN
1445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1446 END IF;*/
1447
1448 -- Initialize message list if p_init_msg_list is set to TRUE.
1449 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1450 FND_MSG_PUB.initialize;
1451 END IF;
1452
1453 -- Initialize API rturn status to success
1454 x_return_status := FND_API.g_ret_sts_success;
1455
1456
1457 l_resp_id := fnd_profile.value('RESP_ID');
1458 l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
1459
1460 -- get icx session ticket
1461 x_icx_sessn_tkt := CZ_CF_API.ICX_SESSION_TICKET;
1462
1463 -- get the dbc file name
1464 x_db_id := FND_WEB_CONFIG.DATABASE_ID;
1465
1466 -- get the URL for servlet
1467 x_servlet_url := fnd_profile.value('CZ_UIMGR_URL');
1468
1469 -- get the SYSDATE
1470 x_sysdate := to_char(sysdate,'mm-dd-yyyy-hh24-mi-ss');
1471
1472
1473 IF FND_API.To_Boolean( p_commit ) THEN
1474 COMMIT WORK;
1475 END IF;
1476 FND_MSG_PUB.Count_And_Get
1477 ( p_encoded => FND_API.G_FALSE,
1478 p_count => x_msg_count,
1479 p_data => x_msg_data
1480 );
1481 EXCEPTION
1482 WHEN FND_API.G_EXC_ERROR THEN
1483 ROLLBACK TO CSI_CONFIG_LAUNCH_PRMS;
1484 x_return_status := FND_API.G_RET_STS_ERROR ;
1485 FND_MSG_PUB.Count_And_Get
1486 ( p_encoded => FND_API.G_FALSE,
1487 p_count => x_msg_count,
1488 p_data => x_msg_data
1489 );
1490 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1491 csi_gen_utility_pvt.put_line('ibe_cfg_config_pvt.Get_Config_Launch_Info: UNEXPECTED ERROR EXCEPTION ');
1492 ROLLBACK TO Get_Config_Launch_Info_Pvt;
1493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1494 FND_MSG_PUB.Count_And_Get
1495 ( p_encoded => FND_API.G_FALSE,
1496 p_count => x_msg_count,
1497 p_data => x_msg_data
1498 );
1499 WHEN OTHERS THEN
1500 csi_gen_utility_pvt.put_line('ibe_cfg_config_pvt.Get_Config_Launch_Info: OTHER EXCEPTION ');
1501 ROLLBACK TO Get_Config_Launch_Info_Pvt;
1502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1503 /*IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1504 THEN
1505 FND_MSG_PUB.Add_Exc_Msg
1506 ( G_PKG_NAME,
1507 l_api_name
1508 );
1509 END IF;*/
1510 FND_MSG_PUB.Count_And_Get
1511 ( p_encoded => FND_API.G_FALSE,
1512 p_count => x_msg_count,
1513 p_data => x_msg_data
1514 );
1515 /*ibe_util.disable_debug;*/
1516 END; -- Procedure CSI_CONFIG_LAUNCH_PRMS
1517
1518
1519
1520 PROCEDURE IS_CONFIGURABLE(p_api_version IN NUMBER
1521 ,p_config_hdr_id IN NUMBER
1522 ,p_config_rev_nbr IN NUMBER
1523 ,p_config_item_id IN NUMBER
1524 ,x_return_value OUT NOCOPY VARCHAR2
1525 ,x_return_status OUT NOCOPY VARCHAR2
1526 ,x_msg_count OUT NOCOPY NUMBER
1527 ,x_msg_data OUT NOCOPY VARCHAR2
1528 ) IS
1529 BEGIN
1530 cz_network_api_pub.IS_CONFIGURABLE(p_api_version
1531 ,p_config_hdr_id
1532 ,p_config_rev_nbr
1533 ,p_config_item_id
1534 ,x_return_value
1535 ,x_return_status
1536 ,x_msg_count
1537 ,x_msg_data);
1538 /*EXCEPTION
1539 WHEN exception_name THEN
1540 statements ;*/
1541 END;
1542
1543 PROCEDURE get_instance_link_locations
1544 (
1545 p_api_version IN NUMBER
1546 ,p_commit IN VARCHAR2
1547 ,p_init_msg_list IN VARCHAR2
1548 ,p_validation_level IN NUMBER
1549 ,p_instance_id IN NUMBER
1550 ,x_instance_link_rec OUT NOCOPY csi_datastructures_pub.instance_link_rec
1551 ,x_return_status OUT NOCOPY VARCHAR2
1552 ,x_msg_count OUT NOCOPY NUMBER
1553 ,x_msg_data OUT NOCOPY VARCHAR2
1554 )
1555 IS
1556 l_api_name CONSTANT VARCHAR2(30) := 'GET_INSTANCE_LINK_LOCATIONS';
1557 l_api_version CONSTANT NUMBER := 1.0;
1558 l_debug_level NUMBER;
1559 --l_instance_header_tbl csi_datastructures_pub.instance_header_tbl;
1560
1561 BEGIN
1562 SAVEPOINT get_instance_link_location;
1563
1564 csi_item_instance_pvt.get_instance_link_locations(
1565 p_api_version
1566 ,p_commit
1567 ,p_init_msg_list
1568 ,p_validation_level
1569 ,p_instance_id
1570 ,x_instance_link_rec
1571 ,x_return_status
1572 ,x_msg_count
1573 ,x_msg_data
1574 );
1575
1576 FND_MSG_PUB.Count_And_Get
1577 (p_count => x_msg_count ,
1578 p_data => x_msg_data
1579 );
1580
1581 EXCEPTION
1582
1583 WHEN FND_API.G_EXC_ERROR THEN
1584 x_return_status := FND_API.G_RET_STS_ERROR ;
1585 ROLLBACK TO get_instance_link_location;
1586 FND_MSG_PUB.Count_And_Get
1587 ( p_count => x_msg_count,
1588 p_data => x_msg_data
1589 );
1590
1591 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1593 ROLLBACK TO get_instance_link_location;
1594 FND_MSG_PUB.Count_And_Get
1595 ( p_count => x_msg_count,
1596 p_data => x_msg_data
1597 );
1598
1599 WHEN OTHERS THEN
1600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1601 ROLLBACK TO get_instance_link_location;
1602 IF FND_MSG_PUB.Check_Msg_Level
1603 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1604 THEN
1605 FND_MSG_PUB.Add_Exc_Msg
1606 (G_PKG_NAME,
1607 l_api_name
1608 );
1609 END IF;
1610 FND_MSG_PUB.Count_And_Get
1611 ( p_count => x_msg_count,
1612 p_data => x_msg_data
1613 );
1614
1615 END get_instance_link_locations;
1616
1617
1618 Procedure bld_data_for_conn_rec
1619 (
1620 p_connected_relationship_tbl in out NOCOPY connected_relationship_tbl,
1621 p_rel_tbl1 in csi_datastructures_pub.ii_relationship_tbl,
1622 p_instanceid_tbl in out NOCOPY instanceid_tbl,
1623 p_instanceid_tbl1 in out NOCOPY instanceid_tbl1,
1624 i in number
1625 )
1626 is
1627 temp number;
1628 count1 number;
1629 begin
1630 count1 := p_instanceid_tbl1.count + 1;
1631 p_connected_relationship_tbl(i).object_id := p_rel_tbl1(i).object_id;
1632 p_connected_relationship_tbl(i).subject_id := p_rel_tbl1(i).subject_id;
1633 p_connected_relationship_tbl(i).swapflag := 'N';
1634 temp := p_rel_tbl1(i).object_id;
1635 if p_instanceid_tbl.exists(temp) then
1636 null;
1637 else
1638 p_instanceid_tbl(temp) := p_rel_tbl1(i).object_id;
1639 p_instanceid_tbl1(count1).instance_id := p_rel_tbl1(i).object_id;
1640 count1 := count1 + 1;
1641 end if;
1642 temp := p_rel_tbl1(i).subject_id;
1643 if p_instanceid_tbl.exists(temp) then
1644 null;
1645 else
1646 p_instanceid_tbl(temp) := p_rel_tbl1(i).subject_id;
1647 p_instanceid_tbl1(count1).instance_id := p_rel_tbl1(i).subject_id;
1648 count1 := count1 + 1;
1649 end if;
1650 end bld_data_for_conn_rec;
1651
1652
1653 Procedure modify_data_for_conn_relship
1654 (
1655 p_connected_relationship_tbl in out NOCOPY connected_relationship_tbl,
1656 p_instanceid_tbl1 in out NOCOPY instanceid_tbl1
1657
1658 ) is
1659 temp number;
1660 begin
1661 for outer in p_instanceid_tbl1.first..p_instanceid_tbl1.last
1662 loop
1663 for inner in p_connected_relationship_tbl.first..p_connected_relationship_tbl.last
1664 loop
1665 if p_connected_relationship_tbl(inner).swapflag = 'N'
1666 and p_connected_relationship_tbl(inner).object_id = p_instanceid_tbl1(outer).instance_id
1667 then
1668 p_connected_relationship_tbl(inner).swapflag := 'Y';
1669 end if;
1670 end loop;
1671 for inner in p_connected_relationship_tbl.first..p_connected_relationship_tbl.last
1672 loop
1673 if p_connected_relationship_tbl(inner).swapflag = 'N'
1674 and p_connected_relationship_tbl(inner).subject_id = p_instanceid_tbl1(outer).instance_id
1675 then
1676 temp := p_connected_relationship_tbl(inner).object_id;
1677 p_connected_relationship_tbl(inner).object_id := p_connected_relationship_tbl(inner).subject_id;
1678 p_connected_relationship_tbl(inner).subject_id := temp;
1679 p_connected_relationship_tbl(inner).swapflag := 'Y';
1680 end if;
1681 end loop;
1682 end loop;
1683 for i in p_connected_relationship_tbl.first .. p_connected_relationship_tbl.last
1684 loop
1685 insert into csi_configuration_temp_tbl (object_id,subject_id) values ( p_connected_relationship_tbl(i).object_id, p_connected_relationship_tbl(i).subject_id);
1686 end loop;
1687
1688 end modify_data_for_conn_relship;
1689
1690 Procedure bld_instance_all_parents_tbl
1691 (
1692 p_subject_id IN NUMBER,
1693 p_relationship_type_code IN VARCHAR2,
1694 p_time_stamp IN DATE
1695 ) IS
1696 l_api_version number := 1.0;
1697 l_commit varchar2(1) := fnd_api.g_false;
1698 l_init_msg_list varchar2(1) := fnd_api.g_false;
1699 l_validation_level number := fnd_api.g_valid_level_full;
1700 l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
1701 l_rel_tbl1 csi_datastructures_pub.ii_relationship_tbl;
1702 l_msg_count number;
1703 l_msg_data varchar2(240);
1704 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1705 l_depth NUMBER := null;
1706 l_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
1707 l_relationship_query_rec1 csi_datastructures_pub.relationship_query_rec;
1708 l_all_parents_id varchar2(4000);
1709 l_time_stamp DATE := p_time_stamp;
1710 l_active_relationship_only VARCHAR2(1) := fnd_api.g_true; --fix for bug5222952
1711 l_var integer;
1712 l_connected_relationship_tbl connected_relationship_tbl;
1713 l_instanceid_tbl instanceid_tbl;
1714 l_instanceid_tbl1 instanceid_tbl1;
1715 BEGIN
1716 l_relationship_query_rec.Subject_id:=p_subject_id ;
1717 l_relationship_query_rec.relationship_type_code:= p_relationship_type_code;
1718 delete from csi_configuration_temp_tbl;
1719 if p_relationship_type_code <> 'CONNECTED-TO' then
1720 LOOP
1721 csi_ii_relationships_pub.get_relationships
1722 (
1723 l_api_version,
1724 l_commit,
1725 l_init_msg_list,
1726 l_validation_level,
1727 l_relationship_query_rec,
1728 l_depth,
1729 l_time_stamp,
1730 l_active_relationship_only,
1731 l_rel_tbl,
1732 l_return_status,
1733 l_msg_count,
1734 l_msg_data
1735 );
1736 if l_rel_tbl.count > 0 then
1737 l_var := l_rel_tbl.first;
1738 insert into csi_configuration_temp_tbl
1739 (
1740 object_id,
1741 subject_id
1742 )
1743 values
1744 (
1745 l_rel_tbl(l_var).object_id,
1746 l_rel_tbl(l_var).subject_id
1747 );
1748
1749 l_relationship_query_rec.Subject_id := l_rel_tbl(l_var).object_id;
1750 else
1751 exit;
1752 end if;
1753 END LOOP;
1754 else
1755 --put selected instanceid_rec by default in validation array
1756 l_instanceid_tbl(1) := p_subject_id;
1757 l_instanceid_tbl1(1).instance_id := p_subject_id;
1758 end if;
1759 -- Now construct childs of actual input
1760 l_relationship_query_rec1.object_id:=p_subject_id ;
1761 l_relationship_query_rec1.relationship_type_code:= p_relationship_type_code;
1762 csi_ii_relationships_pub.get_relationships
1763 (
1764 l_api_version,
1765 l_commit,
1766 l_init_msg_list,
1767 l_validation_level,
1768 l_relationship_query_rec1,
1769 l_depth,
1770 l_time_stamp,
1771 l_active_relationship_only,
1772 l_rel_tbl1,
1773 l_return_status,
1774 l_msg_count,
1775 l_msg_data
1776 );
1777 if l_rel_tbl1.count > 0 then
1778 for i in l_rel_tbl1.first .. l_rel_tbl1.last
1779 loop
1780 if p_relationship_type_code = 'CONNECTED-TO' then
1781 bld_data_for_conn_rec(
1782 p_connected_relationship_tbl => l_connected_relationship_tbl,
1783 p_rel_tbl1 => l_rel_tbl1,
1784 p_instanceid_tbl => l_instanceid_tbl,
1785 p_instanceid_tbl1 => l_instanceid_tbl1,
1786 i => i);
1787 else
1788 insert into csi_configuration_temp_tbl (object_id,subject_id) values ( l_rel_tbl1(i).object_id, l_rel_tbl1(i).subject_id);
1789 end if;
1790 end loop;
1791 end if;
1792 if p_relationship_type_code = 'CONNECTED-TO' then
1793 if l_connected_relationship_tbl.count > 0 then
1794 modify_data_for_conn_relship(
1795 p_connected_relationship_tbl => l_connected_relationship_tbl,
1796 p_instanceid_tbl1 => l_instanceid_tbl1);
1797 end if;
1798 end if;
1799 END bld_instance_all_parents_tbl;
1800
1801
1802 FUNCTION get_instance_all_parents
1803 (
1804 p_subject_id IN NUMBER,
1805 p_time_stamp IN DATE
1806 ) RETURN VARCHAR2 IS
1807 l_api_version number := 1.0;
1808 l_commit varchar2(1) := fnd_api.g_false;
1809 l_init_msg_list varchar2(1) := fnd_api.g_false;
1810 l_validation_level number := fnd_api.g_valid_level_full;
1811 l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
1812 l_msg_count number;
1813 l_msg_data varchar2(240);
1814 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1815 l_depth NUMBER := null;
1816 l_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
1817 l_all_parents_id varchar2(4000);
1818 l_time_stamp DATE :=p_time_stamp;
1819 l_active_relationship_only VARCHAR2(1) := fnd_api.g_false;
1820 l_output varchar2(4000);
1821 l_var integer;
1822 BEGIN
1823 l_relationship_query_rec.Subject_id:=p_subject_id ;
1824 l_output := p_subject_id ;
1825
1826 LOOP
1827
1828 l_relationship_query_rec.relationship_type_code:='COMPONENT-OF';
1829
1830
1831 csi_ii_relationships_pub.get_relationships
1832 (
1833 l_api_version,
1834 l_commit,
1835 l_init_msg_list,
1836 l_validation_level,
1837 l_relationship_query_rec,
1838 l_depth,
1839 l_time_stamp,
1840 l_active_relationship_only,
1841 l_rel_tbl,
1842 l_return_status,
1843 l_msg_count,
1844 l_msg_data
1845 );
1846 if l_rel_tbl.count > 0 then
1847 l_var := l_rel_tbl.first;
1848 l_output := to_char(l_rel_tbl(l_var).object_id) || ',' || l_output;
1849 l_relationship_query_rec.Subject_id := l_rel_tbl(l_var).object_id;
1850 else
1851 exit;
1852 end if;
1853 END LOOP;
1854 return l_output;
1855 END;
1856 /* Function added to get the Item Validation Org Id for the Configurator Flow */
1857
1858 FUNCTION get_config_org_id(
1859 p_instance_id IN NUMBER,
1860 p_last_oe_order_line_id IN NUMBER)
1861 RETURN VARCHAR2 IS
1862 l_org_id NUMBER := -1;
1863 l_config_id VARCHAR2(100) := '-1';
1864 BEGIN
1865
1866 BEGIN
1867 SELECT
1868 org_id into l_org_id
1869 FROM
1870 oe_order_lines_all oeol
1871 WHERE
1872 line_id=p_last_oe_order_line_id;
1873 EXCEPTION
1874 WHEN NO_DATA_FOUND THEN
1875 l_org_id:=-1;
1876 END;
1877
1878 IF l_org_id <> -1 THEN
1879 mo_global.set_policy_context('S',l_org_id);
1880
1881 SELECT
1882 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', l_org_id)
1883 INTO l_config_id
1884 FROM DUAL;
1885
1886 END IF;
1887
1888 Return l_config_id;
1889
1890 END;
1891
1892
1893
1894
1895 /* Function to get new non serial instances for deployment */
1896
1897 FUNCTION get_instance_ids
1898 (
1899 P_instance_tbl IN OUT NOCOPY dpl_instance_tbl
1900
1901 ) RETURN VARCHAR2 IS
1902
1903 l_output varchar2(4000):='';
1904 l_instance_id NUMBER;
1905 n_instance_id NUMBER;
1906 found_flag varchar2(1) :='N';
1907
1908 CURSOR c_item_instances(l_instance_id NUMBER) IS
1909 SELECT serial_number
1910 FROM csi_item_instances
1911 WHERE instance_id =l_instance_id ;
1912
1913 CURSOR c_nsrl_item_instances(n_instance_id NUMBER) IS
1914 SELECT CIIH.INSTANCE_ID FROM
1915 CSI_ITEM_INSTANCES_H ciih,
1916 csi_item_instances cii
1917 WHERE
1918 cii.instance_id =ciih.instance_id
1919 AND cii.SERIAL_NUMBER IS NULL
1920 and ciiH.INSTANCE_ID NOT IN n_instance_id
1921 AND TRANSACTION_ID IN
1922 ( SELECT MAX(TRANSACTION_ID) FROM CSI_ITEM_INSTANCES_H WHERE INSTANCE_ID
1923 = n_instance_id);
1924
1925 BEGIN
1926
1927 For idx in P_instance_tbl.first..P_instance_tbl.last
1928
1929 LOOP
1930 For c_rec in c_item_instances(P_instance_tbl(idx).instance_id)
1931
1932 LOOP
1933
1934 IF C_REC.SERIAL_NUMBER IS NOT NULL THEN
1935
1936 IF (l_output='' OR l_output IS NULL) THEN
1937 l_output :=to_char(P_instance_tbl(idx).instance_id);
1938 ELSE
1939 l_output := l_output||','||to_char(P_instance_tbl(idx).instance_id) ;
1940 END IF;
1941
1942 END IF;
1943 END LOOP;
1944 END LOOP;
1945
1946
1947
1948 N_instance_id :=P_instance_tbl(1).instance_id;
1949
1950 FOR c_rec in c_nsrl_item_instances(N_instance_id)
1951 LOOP
1952 found_flag :='N';
1953 FOR i_dx in P_instance_tbl.first..P_instance_tbl.last
1954 LOOP
1955 IF c_rec.instance_id=p_instance_tbl(i_dx).instance_id THEN
1956 found_flag :='Y';
1957 --p_instance_tbl.DELETE(i_dx);
1958 EXIT;
1959 END IF;
1960 END LOOP;
1961
1962 IF Found_flag='N' THEN
1963
1964 IF (l_output='' OR l_output IS NULL) THEN
1965 l_output :=to_char(c_rec.instance_id);
1966 ELSE
1967 l_output := l_output||','||to_char(c_rec.instance_id) ;
1968 END IF;
1969 END IF;
1970 END LOOP;
1971
1972
1973 RETURN l_output;
1974 END;
1975
1976
1977
1978 PROCEDURE get_contact_details
1979 (
1980 p_api_version IN NUMBER
1981 ,p_commit IN VARCHAR2
1982 ,p_init_msg_list IN VARCHAR2
1983 ,p_validation_level IN NUMBER
1984 ,p_contact_party_id IN NUMBER
1985 ,p_contact_flag IN VARCHAR2
1986 ,p_party_tbl IN VARCHAR2
1987 ,x_contact_details OUT NOCOPY csi_datastructures_pub.contact_details_rec
1988 ,x_return_status OUT NOCOPY VARCHAR2
1989 ,x_msg_count OUT NOCOPY NUMBER
1990 ,x_msg_data OUT NOCOPY VARCHAR2
1991 )
1992 IS
1993 begin
1994 csi_party_relationships_pvt.get_contact_details
1995 (
1996 p_api_version
1997 ,p_commit
1998 ,p_init_msg_list
1999 ,p_validation_level
2000 ,p_contact_party_id
2001 ,p_contact_flag
2002 ,p_party_tbl
2003 ,x_contact_details
2004 ,x_return_status
2005 ,x_msg_count
2006 ,x_msg_data
2007 );
2008
2009 End get_contact_details;
2010
2011 PROCEDURE delete_search_oks_temp
2012 (
2013 p_sequence_id IN NUMBER
2014 ,x_return_status OUT NOCOPY VARCHAR2
2015 ,x_msg_count OUT NOCOPY NUMBER
2016 ,x_msg_data OUT NOCOPY VARCHAR2
2017 ) IS
2018
2019 l_api_name CONSTANT VARCHAR2(30) := 'delete_search_oks_temp';
2020
2021 BEGIN
2022 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2023
2024 IF p_sequence_id IS NULL THEN
2025 x_return_status := FND_API.G_RET_STS_ERROR ;
2026 FND_MSG_PUB.Count_And_Get
2027 ( p_count => x_msg_count,
2028 p_data => x_msg_data
2029 );
2030 ELSE
2031 DELETE csi_search_oks_temp where id = p_sequence_id ;
2032 COMMIT;
2033 END IF;
2034
2035 EXCEPTION
2036 WHEN OTHERS THEN
2037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2038 IF FND_MSG_PUB.Check_Msg_Level
2039 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2040 THEN
2041 FND_MSG_PUB.Add_Exc_Msg
2042 (G_PKG_NAME,
2043 l_api_name
2044 );
2045 END IF;
2046 FND_MSG_PUB.Count_And_Get
2047 ( p_count => x_msg_count,
2048 p_data => x_msg_data
2049 );
2050 END delete_search_oks_temp;
2051
2052 PROCEDURE expire_relationship
2053 (
2054 p_api_version IN NUMBER
2055 ,p_commit IN VARCHAR2
2056 ,p_init_msg_list IN VARCHAR2
2057 ,p_validation_level IN NUMBER
2058 ,p_subject_id IN NUMBER
2059 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
2060 ,x_instance_id_lst OUT NOCOPY csi_datastructures_pub.id_tbl
2061 ,x_return_status OUT NOCOPY VARCHAR2
2062 ,x_msg_count OUT NOCOPY NUMBER
2063 ,x_msg_data OUT NOCOPY VARCHAR2
2064 ) IS
2065 l_relationship_id NUMBER;
2066 l_object_version_number NUMBER;
2067 l_relationship_rec csi_datastructures_pub.ii_relationship_rec;
2068 BEGIN
2069 SELECT
2070 relationship_id, object_version_number INTO l_relationship_id, l_object_version_number
2071 FROM
2072 csi_ii_relationships
2073 WHERE
2074 subject_id = p_subject_id
2075 and (active_end_date IS NULL OR active_end_date > sysdate);
2076
2077 l_relationship_rec.relationship_id := l_relationship_id;
2078 l_relationship_rec.object_version_number := l_object_version_number;
2079
2080 CSI_II_RELATIONSHIPS_PUB.expire_relationship
2081 (
2082 p_api_version => p_api_version,
2083 p_commit => p_commit,
2084 p_init_msg_list => p_init_msg_list,
2085 p_validation_level => p_validation_level,
2086 p_relationship_rec => l_relationship_rec,
2087 p_txn_rec => p_txn_rec,
2088 x_instance_id_lst => x_instance_id_lst,
2089 x_return_status => x_return_status,
2090 x_msg_count => x_msg_count,
2091 x_msg_data => x_msg_data
2092 );
2093 END expire_relationship;
2094
2095 END CSI_JAVA_INTERFACE_PKG;