[Home] [Help]
PACKAGE BODY: APPS.OKC_OM_INT_GRP
Source
1 PACKAGE BODY OKC_OM_INT_GRP AS
2 /* $Header: OKCGIOMB.pls 120.0 2005/05/25 18:21:26 appldev noship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 ---------------------------------------------------------------------------
7 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
8 ---------------------------------------------------------------------------
9 -- GLOBAL VARIABLES
10 ---------------------------------------------------------------------------
11 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_OM_INT_GRP';
12 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
13 G_STMT_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
14 G_MODULE_NAME CONSTANT VARCHAR2(250) := 'OKC.PLSQL.'||G_PKG_NAME||'.';
15
16 ------------------------------------------------------------------------------
17 -- GLOBAL CONSTANTS
18 ------------------------------------------------------------------------------
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
22 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR ;
23 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
24 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
25 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
26 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
27
28 Procedure get_article_variable_values(
29 p_api_version IN Number,
30 p_init_msg_list IN Varchar2,
31 p_doc_type IN VARCHAR2,
32 p_doc_id IN Number,
33 p_sys_var_value_tbl IN OUT NOCOPY sys_var_value_tbl_type,
34 x_return_status OUT NOCOPY Varchar2,
35 x_msg_data OUT NOCOPY Varchar2,
36 x_msg_count OUT NOCOPY Number) IS
37
38 l_api_version CONSTANT NUMBER := 1;
39 l_api_name CONSTANT VARCHAR2(30) := 'g_get_article_variable_values';
40 l_debug Boolean;
41 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
42
43 BEGIN
44 x_return_status := FND_API.G_RET_STS_SUCCESS;
45
46 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
47 l_debug := true;
48 END IF;
49
50 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
51 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'10:Entering OKC_OM_INT_GRP.get_article_variable_values');
52 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'20:Calling OKC_XPRT_OM_INT_PVT.get_clause_variable_values');
53 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,' ');
54 END IF;
55
56 OKC_XPRT_OM_INT_PVT.get_clause_variable_values(
57 p_api_version => l_api_version,
58 p_init_msg_list => FND_API.G_FALSE,
59 p_doc_type => p_doc_type,
60 p_doc_id => p_doc_id,
61 p_sys_var_value_tbl => p_sys_var_value_tbl,
62 x_return_status => x_return_status,
63 x_msg_data => x_msg_data,
64 x_msg_count => x_msg_count);
65
66 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
67 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,' ');
68 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'25:Status from OKC_XPRT_OM_INT_PVT.get_clause_variable_values = '||x_return_status);
69 END IF;
70
71 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
72 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
73 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
74 RAISE FND_API.G_EXC_ERROR ;
75 END IF;
76
77 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
78 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'30:Leaving OKC_OM_INT_GRP.get_article_variable_values');
79 END IF;
80
81 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
82
83 EXCEPTION
84 WHEN FND_API.G_EXC_ERROR THEN
85
86 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
87 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'40: Leaving get_article_variable_values: OKC_API.G_EXCEPTION_ERROR Exception');
88 END IF;
89
90 x_return_status := G_RET_STS_ERROR ;
91 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
92
93 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
94 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
95 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'50: Leaving get_article_variable_values: OKC_API.G_EXCEPT ION_UNEXPECTED_ERROR Exception');
96 END IF;
97
98 x_return_status := G_RET_STS_UNEXP_ERROR ;
99 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
100 WHEN OTHERS THEN
101 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
102 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'60: Leaving get_article_variable_values because of EXCEPTION: '||sqlerrm);
103 END IF;
104
105 x_return_status := G_RET_STS_UNEXP_ERROR ;
106
107 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
108 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
109 END IF;
110 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
111
112 END;
113
114
115 Function ok_to_commit (
116 p_api_version IN Number,
117 p_init_msg_list IN Varchar2,
118 p_doc_id IN Number,
119 p_tmpl_change IN Varchar2,
120 p_validation_string IN Varchar2,
121 x_return_status OUT NOCOPY Varchar2,
122 x_msg_data OUT NOCOPY Varchar2,
123 x_msg_count OUT NOCOPY Number
124 )
125 Return Varchar2 IS
126 l_api_version CONSTANT NUMBER := 1;
127 l_api_name CONSTANT VARCHAR2(30) := 'g_ok_to_commit';
128 l_debug Boolean;
129 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
130
131 BEGIN
132
133 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
134 l_debug := true;
135 END IF;
136
137 Return FND_API.G_TRUE;
138
139 EXCEPTION
140
141 WHEN FND_API.G_EXC_ERROR THEN
142 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'70: Leaving ok_to_commit: OKC_API.G_EXCEPTION_ERROR Exception');
144 END IF;
145
146 x_return_status := G_RET_STS_ERROR ;
147 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
148
149 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
150 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
151 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'80: Leaving ok_to_commit: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
152 END IF;
153
154 x_return_status := G_RET_STS_UNEXP_ERROR ;
155 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
156 WHEN OTHERS THEN
157 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
158 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'90: Leaving ok_to_commit because of EXCEPTION: '||sqlerrm);
159 END IF;
160
161 x_return_status := G_RET_STS_UNEXP_ERROR ;
162
163 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
164 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
165 END IF;
166 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
167
168 END ok_to_commit;
169
170 Procedure get_item_dtl_for_expert(
171 p_api_version IN Number,
172 p_init_msg_list IN Varchar2,
173 p_doc_type IN Varchar2,
174 p_doc_id IN Number,
175 x_category_tbl OUT NOCOPY item_tbl_type,
176 x_item_tbl OUT NOCOPY item_tbl_type,
177 x_return_status OUT NOCOPY Varchar2,
178 x_msg_data OUT NOCOPY Varchar2,
179 x_msg_count OUT NOCOPY Number
180 ) IS
181 l_api_version CONSTANT NUMBER := 1;
182 l_api_name CONSTANT VARCHAR2(30) := 'g_get_item_dtl_for_expert';
183
184 l_debug Boolean;
185 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
186
187 l_line_var_tbl OKC_XPRT_OM_INT_PVT.line_var_tbl_type;
188 l_sys_var_value_tbl sys_var_value_tbl_type;
189 l_cat_index NUMBER := -1;
190 l_item_index NUMBER := -1;
191
192 BEGIN
193 x_return_status := FND_API.G_RET_STS_SUCCESS;
194
195 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
196 l_debug := true;
197 END IF;
198
199 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
200 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'100:Entering OKC_OM_INT_GRP.g_get_item_dtl_for_expert');
201 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'110:Calling OKC_XPRT_OM_INT_PVT.get_clause_variable_values - 2');
202 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,' ');
203 END IF;
204
205 l_line_var_tbl(0) :='OKC$S_ITEM_CATEGORY';
206 l_line_var_tbl(1) :='OKC$S_ITEM';
207
208 OKC_XPRT_OM_INT_PVT.get_clause_variable_values(
209 p_api_version => l_api_version,
210 p_init_msg_list => FND_API.G_FALSE,
211 p_doc_type => p_doc_type,
212 p_doc_id => p_doc_id,
213 p_line_var_tbl => l_line_var_tbl,
214 x_line_var_value_tbl => l_sys_var_value_tbl,
215 x_return_status => x_return_status,
216 x_msg_data => x_msg_data,
217 x_msg_count => x_msg_count);
218
219 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
220 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,' ');
221 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'115:Status from OKC_XPRT_OM_INT_PVT.Get_clause_Variable_Values - 2 = '||x_return_status);
222 END IF;
223
224 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
225 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
226 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
227 RAISE FND_API.G_EXC_ERROR ;
228 END IF;
229
230 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
231 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,' ');
232 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'117: l_sys_var_value_tbl count = '||l_sys_var_value_tbl.COUNT);
233 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,' ');
234 END IF;
235
236 IF l_sys_var_value_tbl.COUNT > 0 THEN
237 FOR i in l_sys_var_value_tbl.FIRST..l_sys_var_value_tbl.LAST LOOP
238 IF l_sys_var_value_tbl(i).variable_code = 'OKC$S_ITEM_CATEGORY' then
239 l_cat_index :=x_category_tbl.count + 1;
240 x_category_tbl(l_cat_index).name := l_sys_var_value_tbl(i).variable_value_id;
241 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
242 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'120:x_category_tbl('||l_cat_index||').name = '||x_category_tbl(l_cat_index).name);
243 END IF;
244 ELSE
245 l_item_index :=x_item_tbl.count + 1;
246 x_item_tbl(l_item_index).name := l_sys_var_value_tbl(i).variable_value_id;
247 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
248 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'130:x_item_tbl('||l_item_index||').name = '||x_item_tbl(l_item_index).name);
249 END IF;
250 END IF;
251
252 END LOOP;
253 END IF;
254
255 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
256 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,' ');
257 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'140:Leaving OKC_OM_INT_GRP.get_item_dtl_for_expert');
258 END IF;
259 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
260
261 EXCEPTION
262 WHEN FND_API.G_EXC_ERROR THEN
263
264 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
265 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'150: Leaving get_item_dtl_for_expert: OKC_API.G_EXCEPTION_ERROR Exception');
266 END IF;
267
268 x_return_status := G_RET_STS_ERROR ;
269 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
270
271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
272 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
273 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'160: Leaving get_item_dtl_for_expert: OKC_API.G_EXCEPT ION_UNEXPECTED_ERROR Exception');
274 END IF;
275
276 x_return_status := G_RET_STS_UNEXP_ERROR ;
277 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
278 WHEN OTHERS THEN
279 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
280 fnd_log.string(FND_LOG.LEVEL_STATEMENT,l_module,'170: Leaving get_item_dtl_for_expert because of EXCEPTION: '||sqlerrm);
281 END IF;
282
283 x_return_status := G_RET_STS_UNEXP_ERROR ;
284
285 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
286 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
287 END IF;
288 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
289 END get_item_dtl_for_expert;
290
291 END OKC_OM_INT_GRP;