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