[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_REP_INT_PVT
Source
1 PACKAGE BODY OKC_XPRT_REP_INT_PVT AS
2 /* $Header: OKCVXREPINTB.pls 120.0 2008/03/28 12:05:48 kkolukul noship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ------------------------------------------------------------------------------
7
8 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
9 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
10
11 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
12 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
13 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
14
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKC_XPRT_REP_INT_PVT';
16 G_MODULE_NAME CONSTANT VARCHAR2(250) := 'OKC.PLSQL.'||G_PKG_NAME||'.';
17 G_STMT_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
18 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
19
20 --Repository Specific Constants
21
22 G_B_REP_CONTRACT_ADMIN CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_ADMIN' ;
23 G_B_REP_CONTRACT_AMOUNT CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_AMOUNT' ;
24 G_B_REP_CONTRACT_AUTH_PARTY CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_AUTH_PARTY' ;
25 G_B_REP_CONTRACT_CURRENCY CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_CURRENCY' ;
26 G_B_REP_CONTRACT_EFF_DATE CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_EFF_DATE' ;
27 G_B_REP_CONTRACT_EXP_DATE CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_EXP_DATE' ;
28 G_B_REP_CONTRACT_NAME CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_NAME' ;
29 G_B_REP_CONTRACT_NUMBER CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_NUMBER' ;
30 G_B_REP_CONTRACT_STATUS CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_STATUS' ;
31 G_B_REP_CONTRACT_TYPE CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_TYPE' ;
32 G_B_REP_CONTRACT_VERSION CONSTANT VARCHAR2(30) := 'OKC$B_REP_CONTRACT_VERSION' ;
33 G_B_REP_OPERATING_UNIT CONSTANT VARCHAR2(30) := 'OKC$B_REP_OPERATING_UNIT' ;
34 G_B_REP_OVERALL_RISK CONSTANT VARCHAR2(30) := 'OKC$B_REP_OVERALL_RISK' ;
35 G_B_REP_REF_DOC_TYPE CONSTANT VARCHAR2(30) := 'OKC$B_REP_REF_DOCUMENT_TYPE';
36 G_B_REP_REF_DOC_NUMBER CONSTANT VARCHAR2(30) := 'OKC$B_REP_REF_DOCUMENT_NUMBER' ;
37
38 G_S_REP_CONTRACT_ADMIN CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_ADMIN' ;
39 G_S_REP_CONTRACT_AMOUNT CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_AMOUNT' ;
40 G_S_REP_CONTRACT_AUTH_PARTY CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_AUTH_PARTY' ;
41 G_S_REP_CONTRACT_CURRENCY CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_CURRENCY' ;
42 G_S_REP_CONTRACT_EFF_DATE CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_EFF_DATE' ;
43 G_S_REP_CONTRACT_EXP_DATE CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_EXP_DATE' ;
44 G_S_REP_CONTRACT_NAME CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_NAME' ;
45 G_S_REP_CONTRACT_NUMBER CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_NUMBER' ;
46 G_S_REP_CONTRACT_STATUS CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_STATUS' ;
47 G_S_REP_CONTRACT_TYPE CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_TYPE' ;
48 G_S_REP_CONTRACT_VERSION CONSTANT VARCHAR2(30) := 'OKC$S_REP_CONTRACT_VERSION' ;
49 G_S_REP_OPERATING_UNIT CONSTANT VARCHAR2(30) := 'OKC$S_REP_OPERATING_UNIT' ;
50 G_S_REP_OVERALL_RISK CONSTANT VARCHAR2(30) := 'OKC$S_REP_OVERALL_RISK' ;
51 G_S_REP_REF_DOC_TYPE CONSTANT VARCHAR2(30) := 'OKC$S_REP_REF_DOCUMENT_TYPE' ;
52 G_S_REP_REF_DOC_NUMBER CONSTANT VARCHAR2(30) := 'OKC$S_REP_REF_DOCUMENT_NUMBER';
53
54
55
56 PROCEDURE get_clause_variable_values
57 (
58 p_api_version IN NUMBER,
59 p_init_msg_list IN VARCHAR2,
60
61 p_doc_type IN VARCHAR2,
62 p_doc_id IN NUMBER,
63 p_sys_var_value_tbl IN OUT NOCOPY OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type,
64
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_msg_count OUT NOCOPY NUMBER,
67 x_msg_data OUT NOCOPY VARCHAR2
68 )
69
70 IS
71
72 l_api_name VARCHAR2(30) := 'get_clause_variable_values';
73 l_package_procedure VARCHAR2(60);
74 l_api_version CONSTANT NUMBER := 1;
75 l_debug Boolean;
76 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
77
78 --
79 --cursor to fetch all the values of Repository header level variables.
80 --
81
82 cursor c_get_repository_variables IS
83
84 SELECT orc.contract_version_num,
85 orc.contract_name,
86 orc.contract_number,
87 orc.contract_type,
88 orc.contract_effective_date,
89 orc.contract_expiration_date,
90 orc.amount,
91 fl.meaning contract_status,
92 (SELECT name
93 FROM Hr_all_organization_units
94 WHERE organization_id = orc.org_id) contract_organization,
95 (SELECT nvl(pf.full_name, fu.user_name)
96 FROM Per_all_people_f pf,
97 fnd_user fu
98 WHERE fu.user_id = orc.owner_id
99 AND pf.person_id (+) = fu.employee_id
100 AND (fu.employee_id IS NULL OR pf.effective_start_date = (SELECT MAX(effective_start_date)
101 FROM per_all_people_f
102 WHERE person_id = fu.employee_id))) contract_owner,
103 obd.name contract_type_name,
104 fl1.meaning intent,
105 (SELECT meaning
106 FROM okc_lookups_v
107 WHERE lookup_type = 'OKC_RISK_LEVELS'
108 AND lookup_code = orc.OVERALL_RISK_CODE) overall_risk,
109 (SELECT meaning
110 FROM okc_lookups_v
111 WHERE lookup_type = 'OKC_AUTHORING_PARTY'
112 AND lookup_code = orc.AUTHORING_PARTY_CODE) authoring_party,
113 Curr.Name currency_name,
114 orc.reference_document_type,
115 orc.reference_document_number
116 FROM OKC_REP_CONTRACTS_ALL orc,
117 okc_lookups_v fl,
118 okc_bus_doc_types_vl obd,
119 okc_lookups_v fl1,
120 Fnd_Currencies_Tl Curr
121 WHERE orc.contract_id = p_doc_id
122 AND fl.lookup_type = 'OKC_REP_CONTRACT_STATUSES'
123 AND fl.lookup_code = orc.CONTRACT_STATUS_CODE
124 AND obd.document_type = orc.Contract_Type
125 AND fl1.lookup_type = 'OKC_REP_CONTRACT_INTENTS'
126 AND fl1.lookup_code = obd.intent
127 AND orc.Currency_Code = Curr.Currency_Code (+)
128 AND Curr.Language (+) = Userenv('LANG');
129
130 l_rep_header_variables c_get_repository_variables%ROWTYPE;
131
132 BEGIN
133
134 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
135 l_debug := true;
136 END IF;
137
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139
140 l_package_procedure := G_PKG_NAME || '.' || l_api_name;
141
142 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'10: Entered ' || l_package_procedure);
144 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'20: p_doc_type: ' || p_doc_type);
145 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'30: p_doc_id: ' || p_doc_id);
146 END IF;
147
148 --
149 -- Standard call to check for call compatibility.
150 --
151 IF NOT FND_API.Compatible_API_Call (l_api_version,
152 p_api_version,
153 l_api_name,
154 G_PKG_NAME)
155 THEN
156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
157 END IF;
158
159 --
160 -- Initialize message list if p_init_msg_list is set to TRUE.
161 --
162 IF FND_API.to_Boolean( p_init_msg_list ) THEN
163 FND_MSG_PUB.initialize;
164 END IF;
165
166 -- Query REPOSITORY tables to retrieve values against variable codes sent in by calling contract expert API.
167
168 IF p_sys_var_value_tbl.FIRST IS NOT NULL THEN
169
170 OPEN c_get_repository_variables;
171 FETCH c_get_repository_variables INTO l_rep_header_variables;
172
173 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
174 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'70: Contract Administrator = '||l_rep_header_variables.contract_owner );
175 END IF;
176
177 CLOSE c_get_repository_variables;
178
179 FOR i IN p_sys_var_value_tbl.FIRST..p_sys_var_value_tbl.LAST LOOP
180
181 --BUY intent Variables
182
183 IF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_ADMIN THEN
184 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_owner;
185
186 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_AMOUNT THEN
187 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.amount;
188
189 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_AUTH_PARTY THEN
190 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.authoring_party;
191
192 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_CURRENCY THEN
193 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.currency_name;
194
195 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_EFF_DATE THEN
196 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_effective_date;
197
198 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_EXP_DATE THEN
199 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_expiration_date;
200
201 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_NAME THEN
202 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_name;
203
204 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_NUMBER THEN
205 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_number;
206
207 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_STATUS THEN
208 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_status;
209
210 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_TYPE THEN
211 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_type_name;
212
213 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_CONTRACT_VERSION THEN
214 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_version_num;
215
216 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_OPERATING_UNIT THEN
217 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_organization;
218
219 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_OVERALL_RISK THEN
220 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.overall_risk;
221
222 ELSIF p_sys_var_value_tbl(i).variable_code = G_B_REP_REF_DOC_TYPE THEN
223 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.reference_document_type;
224
225 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_REF_DOC_NUMBER THEN
226 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.reference_document_number;
227
228 --SELL intent Variables
229
230 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_ADMIN THEN
231 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_owner;
232
233 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_AMOUNT THEN
234 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.amount;
235
236 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_AUTH_PARTY THEN
237 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables. authoring_party;
238
239 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_CURRENCY THEN
240 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.currency_name;
241
242 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_EFF_DATE THEN
243 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_effective_date;
244
245 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_EXP_DATE THEN
246 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_expiration_date;
247
248 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_NAME THEN
249 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_name;
250
251 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_NUMBER THEN
252 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_number;
253
254 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_STATUS THEN
255 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_status;
256
257 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_TYPE THEN
258 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_type_name;
259
260 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_CONTRACT_VERSION THEN
261 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_version_num;
262
263 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_OPERATING_UNIT THEN
264 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.contract_organization;
265
266 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_OVERALL_RISK THEN
267 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.overall_risk;
268
269 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_REF_DOC_TYPE THEN
270 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables. reference_document_type;
271
272 ELSIF p_sys_var_value_tbl(i).variable_code = G_S_REP_REF_DOC_NUMBER THEN
273 p_sys_var_value_tbl(i).variable_value_id := l_rep_header_variables.reference_document_number;
274
275
276 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
277 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'480: p_sys_var_value_tbl('||i||').variable_code : '||p_sys_var_value_tbl(i).variable_code);
278 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'485: p_sys_var_value_tbl('||i||').variable_value_id : '||p_sys_var_value_tbl(i).variable_value_id);
279
280 END IF;
281
282 END IF;
283
284 END LOOP;
285
286 END IF;
287
288 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
289 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'490: End of '||l_package_procedure||' for repository header level variables, x_return_status ' || x_return_status);
290 END IF;
291
292 EXCEPTION
293
294 WHEN FND_API.G_EXC_ERROR THEN
295 IF c_get_repository_variables%ISOPEN THEN
296 CLOSE c_get_repository_variables;
297 END IF;
298
299 x_return_status := FND_API.G_RET_STS_ERROR ;
300
301 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
302 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'500: '||l_package_procedure||' In the FND_API.G_EXC_ERROR section');
303 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'510: x_return_status = '||x_return_status);
304 END IF;
305
306 FND_MSG_PUB.Count_And_Get(
307 p_count => x_msg_count,
308 p_data => x_msg_data );
309
310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311
312 IF c_get_repository_variables%ISOPEN THEN
313 CLOSE c_get_repository_variables;
314 END IF;
315
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
317
318 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
319 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'520: '||l_package_procedure||' In the FND_API.G_RET_STS_UNEXP_ERROR section');
320 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'530: x_return_status = '||x_return_status);
321 END IF;
322
323 FND_MSG_PUB.Count_And_Get(
324 p_count => x_msg_count,
325 p_data => x_msg_data );
326
327 WHEN OTHERS THEN
328
329 IF c_get_repository_variables%ISOPEN THEN
330 CLOSE c_get_repository_variables;
331 END IF;
332
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
334 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
335 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'540: '||l_package_procedure||' In the OTHERS section');
336 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'550: x_return_status = '||x_return_status);
337 END IF;
338
339 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
340 FND_MSG_PUB.Add_Exc_Msg(
341 G_PKG_NAME ,
342 l_api_name );
343 END IF;
344
345 FND_MSG_PUB.Count_And_Get(
346 p_count => x_msg_count,
347 p_data => x_msg_data );
348
349 END get_clause_variable_values;
350
351 END OKC_XPRT_REP_INT_PVT;