1 PACKAGE BODY IBE_INSTALLBASE_PVT AS
2 /* $Header: IBEVINSTB.pls 120.3 2005/11/24 04:23:47 cshivaru noship $ */
3 PROCEDURE Get_Connected_Instances(
4 p_instance_id IN NUMBER,
5 p_owner_party_id IN NUMBER,
6 p_owner_party_account_id IN NUMBER,
7 p_key_bind_value IN NUMBER,
8 x_parse_key OUT NOCOPY VARCHAR2,
9 x_query_inst_id OUT NOCOPY VARCHAR2,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER,
12 x_return_message OUT NOCOPY VARCHAR2
13 )
14 IS
15 l_config_query_table csi_cz_int.config_query_table;
16 l_config_pair_table csi_cz_int.config_pair_table;
17
18 l_config_inst_header_id number;
19 l_config_inst_revision_number number;
20 l_true VARCHAR2(1);
21
22 CURSOR c_connected_to_inst_id
23 (c_inst_hdr_id IN NUMBER, c_inst_rev_num IN NUMBER, c_inst_item_id IN NUMBER)
24 IS
25 SELECT INST.INSTANCE_ID
26 FROM CSI_ITEM_INSTANCES INST
27 WHERE INST.OWNER_PARTY_ID = p_owner_party_id
28 AND INST.OWNER_PARTY_ACCOUNT_ID = p_owner_party_account_id
29 AND INST.CONFIG_INST_HDR_ID = c_inst_hdr_id
30 AND INST.CONFIG_INST_REV_NUM = c_inst_rev_num
31 AND INST.CONFIG_INST_ITEM_ID = c_inst_item_id ;
32
33 l_instance_id VARCHAR2(20);
34 l_instance_string VARCHAR2(1000);
35 BEGIN
36 l_instance_string := null;
37 l_true := FND_API.G_TRUE;
38 x_parse_key := '';
39 -- Get the config inst header id and config inst revision number for
40 -- this instance id.
41 SELECT CSII.CONFIG_INST_HDR_ID, CSII.CONFIG_INST_REV_NUM
42 INTO l_config_inst_header_id, l_config_inst_revision_number
43 FROM CSI_ITEM_INSTANCES CSII
44 WHERE CSII.INSTANCE_ID = p_instance_id;
45
46 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
47 IBE_Util.Debug('l_config_inst_header_id : '||l_config_inst_header_id);
48 IBE_Util.Debug('l_config_inst_revision_number : '||l_config_inst_revision_number);
49 END IF;
50
51
52 l_config_query_table(0).config_header_id := l_config_inst_header_id ;
53 l_config_query_table(0).config_revision_number := l_config_inst_revision_number ;
54
55 --Call Instance Base API to get all the connected instances.
56 csi_cz_int.get_connected_configurations(
57 p_config_query_table => l_config_query_table,
58 p_instance_level => 'INSTALLED',
59 x_config_pair_table => l_config_pair_table,
60 x_return_status => x_return_status,
61 x_return_message => x_return_message
62 );
63
64 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
65 IBE_Util.Debug('After Call to CSI API::x_return_status:'||x_return_status);
66 END IF;
67
68 -- If Ret Status is true and there is atleast one connected-to instance
69 IF (x_return_status = FND_API.G_RET_STS_SUCCESS)
70 THEN
71 -- then construct a comma separated string of Instance Id
72 IF l_config_pair_table.first is not null THEN
73
74 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
75 IBE_Util.Debug('Ret Status is success and config pair table exists l_config_pair_table.first is not null');
76 END IF;
77
78 FOR l_iteration in l_config_pair_table.first .. l_config_pair_table.last
79 LOOP
80 IF ( l_config_pair_table(l_iteration).object_header_id is not null and
81 l_config_pair_table(l_iteration).subject_header_id is not null)
82 THEN
83 OPEN c_connected_to_inst_id(l_config_pair_table(l_iteration).root_header_id,l_config_pair_table(l_iteration).root_revision_number,l_config_pair_table(l_iteration).root_item_id);
84 FETCH c_connected_to_inst_id into l_instance_id;
85 IF (l_instance_string is null) THEN
86 l_instance_string := l_instance_id;
87 ELSE
88 l_instance_string := l_instance_string||','||l_instance_id;
89 END IF;
90 CLOSE c_connected_to_inst_id;
91 END IF;
92 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
93 IBE_Util.Debug('l_iteration::'||l_iteration||'l_instance_string::'||l_instance_string);
94 END IF;
95 END LOOP;
96 END IF;
97
98 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
99 IBE_Util.Debug('After Loop : l_instance_string :: '||l_instance_string);
100 END IF;
101 -- If there is atleast one Connected-to Instance
102 IF(l_instance_string is not null)
103 THEN
104 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
105 IBE_Util.Debug('Inside if bef call to lead import');
106 END IF;
107 -- Initialise key and call the API to put the values in the temp table
108 -- Out var would be a query which can be used with the key bound to
109 -- value of p_number
110 x_parse_key := 'PARSE_INSTANCE_IDS';
111 IBE_LEAD_IMPORT_PVT.parseInput(
112 p_inString => l_instance_string,
113 p_type => 'CHAR',
114 p_keyString => x_parse_key,
115 p_number => p_key_bind_value,
116 x_QueryString => x_query_inst_id
117 );
118 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
119 IBE_Util.Debug('After call to parse Input,x_QueryString : '||x_query_inst_id);
120 END IF;
121 END IF;
122 ELSE
123 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
124 IBE_Util.Debug('Return Status is not successful, Message : '||x_return_message);
125 END IF;
126 END IF;
127
128 EXCEPTION
129 WHEN others THEN
130 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
131 IBE_Util.Debug('Exception caught : ' ||sqlerrm);
132 RAISE;
133 END IF;
134 END Get_Connected_Instances;
135
136 FUNCTION IS_ITEM_IN_MSITE(
137 p_inventory_item_id IN NUMBER,
138 p_minisite_id IN NUMBER
139 )
140 RETURN VARCHAR2
141 IS
142 l_return_value VARCHAR2(1);
143 l_true VARCHAR2(1);
144 BEGIN
145 l_true := FND_API.G_TRUE;
146 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
147 IBE_Util.Debug('IS_ITEM_IN_MSITE BEGIN ');
148 END IF;
149
150 l_return_value := 'N'; -- Initialize return value to be N.
151
152 -- If inventory item id and minisite id is passed
153 IF(p_inventory_item_id is not null and p_minisite_id is not null)
154 THEN
155 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
156 IBE_Util.Debug('p_inventory_item_id : '||p_inventory_item_id||' :: p_minisite_id : '||p_minisite_id);
157 END IF;
158
159 SELECT 'Y' INTO l_return_value -- Set return val to Y if item exists in Msite
160 FROM DUAL
161 WHERE EXISTS
162 (
163 SELECT si.inventory_item_id
164 FROM ibe_dsp_msite_sct_items msi, ibe_dsp_section_items si
165 WHERE msi.section_item_id = si.section_item_id
166 AND msi.mini_site_id = p_minisite_id
167 AND si.inventory_item_id = p_inventory_item_id
168 );
169
170 END IF;
171
172 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
173 IBE_Util.Debug('Retrun Value : '||l_return_value);
174 END IF;
175
176 RETURN(l_return_value);
177 -- Handle any exception, return 'N'
178 EXCEPTION
179 WHEN OTHERS THEN
180 BEGIN
181 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
182 IBE_Util.Debug('Exception caught'||sqlerrm);
183 END IF;
184 RETURN(l_return_value) ;
185 END;
186 END IS_ITEM_IN_MSITE;
187
188 END IBE_INSTALLBASE_PVT;
[Home] [Help]