[Home] [Help]
PACKAGE BODY: APPS.OKC_REP_QA_CHECK_PVT
Source
1 PACKAGE BODY OKC_REP_QA_CHECK_PVT AS
2 /* $Header: OKCVREPQACHKB.pls 120.9 2008/01/23 12:17:02 harchand ship $ */
3
4 -- Start of comments
5 --API name : check_no_external_party
6 --Type : Private.
7 --Function : This procedure checks for the presence of an external party in a given contract.
8 -- : and modifies px_qa_result_tbl table of records that contains validation
9 -- : errors and warnings
10 --Pre-reqs : None.
11 --Parameters :
12 --IN : p_api_version IN NUMBER Required
13 -- : p_init_msg_list IN VARCHAR2 Optional
14 -- Default = FND_API.G_FALSE
15 -- : p_contract_id IN NUMBER Required
16 -- ID of the contract to be checked
17 -- : p_severity IN VARCHAR2 Required
18 -- Severity level for this check. Possible values are ERROR, WARNING.
19 --INOUT : px_qa_result_tbl
20 -- The table of records that contains validation errors and warnings
21 --OUT : x_return_status OUT VARCHAR2(1)
22 -- : x_msg_count OUT NUMBER
23 -- : x_msg_data OUT VARCHAR2(2000)
24 --Note :
25 -- End of comments
26 PROCEDURE check_no_external_party (
27 p_api_version IN NUMBER,
28 p_init_msg_list IN VARCHAR2,
29 p_contract_id IN NUMBER,
30 p_severity IN VARCHAR2,
31 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
32 x_msg_data OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_return_status OUT NOCOPY VARCHAR2
35 ) IS
36
37 l_party_role_code CONSTANT VARCHAR2(40) := 'INTERNAL_ORG';
38 l_api_version CONSTANT NUMBER := 1;
39 l_api_name CONSTANT VARCHAR2(30) := 'check_no_external_party';
40 l_index PLS_INTEGER := 0;
41
42 CURSOR party_csr IS
43 SELECT 'X'
44 FROM okc_rep_contract_parties
45 WHERE contract_id = p_contract_id
46 and party_role_code <> l_party_role_code;
47
48 party_rec party_csr%ROWTYPE;
49
50
51 BEGIN
52 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
53 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
54 'Entered OKC_REP_QA_CHECK_PVT.check_no_external_party ');
55 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
56 'Contract Id is: ' || p_contract_id);
57 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
58 'Severity level is: ' || p_severity);
59 END IF;
60 -- Standard call to check for call compatibility.
61 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64 -- Initialize message list if p_init_msg_list is set to TRUE.
65 IF FND_API.to_Boolean( p_init_msg_list ) THEN
66 FND_MSG_PUB.initialize;
67 END IF;
68
69 -- Initialize API return status to success
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 OPEN party_csr;
73 FETCH party_csr INTO party_rec;
74 IF party_csr%NOTFOUND THEN
75 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
76 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
77 'External Party is not found');
78 END IF;
79 -- Set the Qa Table index.
80 l_index := px_qa_result_tbl.count + 1;
81 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
82 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_EXT_PARTY_T);
83 -- Need to verify the qa_code with Sanjay
84 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_NO_EXT_PARTY;
85 px_qa_result_tbl(l_index).message_name := G_OKC_REP_NO_EXT_PARTY;
86 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_NO_EXT_PARTY_S);
87 px_qa_result_tbl(l_index).error_severity := p_severity;
88 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_EXT_PARTY_SD);
89 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_EXT_PARTY);
90 END IF;
91 -- Standard call to get message count and if count is 1, get message info.
92 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
93
94 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
95 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
96 'Leaving OKC_REP_QA_CHECK_PVT.check_no_external_party');
97 END IF;
98 EXCEPTION
99 WHEN FND_API.G_EXC_ERROR THEN
100 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
101 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
102 'Leaving OKC_REP_QA_CHECK_PVT.check_no_external_party with G_EXC_ERROR');
103 END IF;
104 x_return_status := G_RET_STS_ERROR;
105 --close cursors
106 IF (party_csr%ISOPEN) THEN
107 CLOSE party_csr ;
108 END IF;
109 FND_MSG_PUB.Count_And_Get(
110 p_count => x_msg_count,
111 p_data => x_msg_data
112 );
113
114 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
115 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
116 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
117 'Leaving OKC_REP_QA_CHECK_PVT.check_no_external_party with G_EXC_UNEXPECTED_ERROR');
118 END IF;
119 x_return_status := G_RET_STS_UNEXP_ERROR;
120 --close cursors
121 IF (party_csr%ISOPEN) THEN
122 CLOSE party_csr ;
123 END IF;
124 FND_MSG_PUB.Count_And_Get(
125 p_count => x_msg_count,
126 p_data => x_msg_data
127 );
128
129 WHEN OTHERS THEN
130 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
131 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
132 G_MODULE || l_api_name,
133 'Leaving OKC_REP_QA_CHECK_PVT.check_no_external_party because of EXCEPTION: ' || sqlerrm);
134 END IF;
135 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
136 p_msg_name => G_UNEXPECTED_ERROR,
137 p_token1 => G_SQLCODE_TOKEN,
138 p_token1_value => sqlcode,
139 p_token2 => G_SQLERRM_TOKEN,
140 p_token2_value => sqlerrm);
141 --close cursors
142 IF (party_csr%ISOPEN) THEN
143 CLOSE party_csr ;
144 END IF;
145 x_return_status := G_RET_STS_UNEXP_ERROR;
146 FND_MSG_PUB.Count_And_Get(
147 p_count => x_msg_count,
148 p_data => x_msg_data
149 );
150 END check_no_external_party;
151
152
153
154 -- Start of comments
155 --API name : check_no_eff_date
156 --Type : Private.
157 --Function : This procedure checks for the presence of effective date in a given contract.
158 -- : and modifies px_qa_result_tbl table of records that contains validation
159 -- : errors and warnings
160 --Pre-reqs : None.
161 --Parameters :
162 --IN : p_api_version IN NUMBER Required
163 -- : p_init_msg_list IN VARCHAR2 Optional
164 -- Default = FND_API.G_FALSE
165 -- : p_effective_date IN NUMBER Required
166 -- Effective date of the contract to be checked
167 -- : p_severity IN VARCHAR2 Required
168 -- Severity level for this check. Possible values are ERROR, WARNING.
169 --INOUT : px_qa_result_tbl
170 -- The table of records that contains validation errors and warnings
171 --OUT : x_return_status OUT VARCHAR2(1)
172 -- : x_msg_count OUT NUMBER
173 -- : x_msg_data OUT VARCHAR2(2000)
174 --Note :
175 -- End of comments
176 PROCEDURE check_no_eff_date (
177 p_api_version IN NUMBER,
178 p_init_msg_list IN VARCHAR2,
179 p_effective_date IN DATE,
180 p_severity IN VARCHAR2,
181 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 x_msg_count OUT NOCOPY NUMBER,
184 x_return_status OUT NOCOPY VARCHAR2
185 ) IS
186
187 l_api_version CONSTANT NUMBER := 1;
188 l_api_name CONSTANT VARCHAR2(30) := 'check_no_eff_date';
189 l_index PLS_INTEGER := 0;
190
191
192 BEGIN
193
194 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
195 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
196 'Entered OKC_REP_QA_CHECK_PVT.check_no_eff_date ');
197 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
198 'Effective Date is: ' || p_effective_date);
199 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
200 'Severity level is: ' || p_severity);
201 END IF;
202
203 -- Standard call to check for call compatibility.
204 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
206 END IF;
207 -- Initialize message list if p_init_msg_list is set to TRUE.
208 IF FND_API.to_Boolean( p_init_msg_list ) THEN
209 FND_MSG_PUB.initialize;
210 END IF;
211
212 -- Initialize API return status to success
213 x_return_status := FND_API.G_RET_STS_SUCCESS;
214
215 --Check for effective date
216 IF (p_effective_date IS NULL) THEN
217 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
218 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
219 'Effective Date is NULL');
220 END IF;
221 l_index := px_qa_result_tbl.count + 1;
222 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
223 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_EFF_DATE_T);
224 -- Need to verify the qa_code with Sanjay
225 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_NO_EFF_DATE;
226 px_qa_result_tbl(l_index).message_name := G_OKC_REP_NO_EFF_DATE;
227 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_NO_EFF_DATE_S);
228 px_qa_result_tbl(l_index).error_severity := p_severity;
229 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_EFF_DATE_SD);
230 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_EFF_DATE);
231 END IF; -- p_effective_date IS NULL
232 -- Standard call to get message count and if count is 1, get message info.
233 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
234
235 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
236 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
237 'Leaving OKC_REP_QA_CHECK_PVT.check_no_eff_date');
238 END IF;
239 EXCEPTION
240 WHEN FND_API.G_EXC_ERROR THEN
241 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
242 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
243 'Leaving OKC_REP_QA_CHECK_PVT.check_no_eff_date with G_EXC_ERROR');
244 END IF;
245 x_return_status := G_RET_STS_ERROR;
246 FND_MSG_PUB.Count_And_Get(
247 p_count => x_msg_count,
248 p_data => x_msg_data
249 );
250
251 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
252 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
253 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
254 'Leaving OKC_REP_QA_CHECK_PVT.check_no_eff_date with G_EXC_UNEXPECTED_ERROR');
255 END IF;
256 x_return_status := G_RET_STS_UNEXP_ERROR;
257 FND_MSG_PUB.Count_And_Get(
258 p_count => x_msg_count,
259 p_data => x_msg_data
260 );
261
262 WHEN OTHERS THEN
263 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
264 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
265 G_MODULE || l_api_name,
266 'Leaving OKC_REP_QA_CHECK_PVT.check_no_eff_date because of EXCEPTION: ' || sqlerrm);
267 END IF;
268 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
269 p_msg_name => G_UNEXPECTED_ERROR,
270 p_token1 => G_SQLCODE_TOKEN,
271 p_token1_value => sqlcode,
272 p_token2 => G_SQLERRM_TOKEN,
273 p_token2_value => sqlerrm);
274 x_return_status := G_RET_STS_UNEXP_ERROR;
275 FND_MSG_PUB.Count_And_Get(
276 p_count => x_msg_count,
277 p_data => x_msg_data
278 );
279 END check_no_eff_date;
280
281 -- Start of comments
282 --API name : validate_contract_type
283 --Type : Private.
284 --Function : This procedure checks for validity of the contract type being passed as an input param
285 -- : and modifies px_qa_result_tbl table of records that contains validation
286 -- : errors and warnings
287 --Pre-reqs : None.
288 --Parameters :
289 --IN : p_api_version IN NUMBER Required
290 -- : p_init_msg_list IN VARCHAR2 Optional
291 -- Default = FND_API.G_FALSE
292 -- : p_contract_type IN NUMBER Required
293 -- Contract type to be validated
294 -- : p_severity IN VARCHAR2 Required
295 -- Severity level for this check. Possible values are ERROR, WARNING.
296 --INOUT : px_qa_result_tbl
297 -- The table of records that contains validation errors and warnings
298 --OUT : x_return_status OUT VARCHAR2(1)
299 -- : x_msg_count OUT NUMBER
300 -- : x_msg_data OUT VARCHAR2(2000)
301 --Note :
302 -- End of comments
303 PROCEDURE validate_contract_type (
304 p_api_version IN NUMBER,
305 p_init_msg_list IN VARCHAR2,
306 p_contract_type IN VARCHAR2,
307 p_severity IN VARCHAR2,
308 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
309 x_msg_data OUT NOCOPY VARCHAR2,
310 x_msg_count OUT NOCOPY NUMBER,
311 x_return_status OUT NOCOPY VARCHAR2
312 ) IS
313
314 l_api_version CONSTANT NUMBER := 1;
315 l_api_name CONSTANT VARCHAR2(30) := 'validate_contract_type';
316 l_index PLS_INTEGER := 0;
317 l_valid_contract_type_flag VARCHAR2(1);
318 l_contract_type_name OKC_BUS_DOC_TYPES_TL.NAME%TYPE;
319
320 CURSOR contract_type_csr IS
321 SELECT name, start_date, end_date
322 FROM okc_bus_doc_types_vl
323 WHERE document_type = p_contract_type;
324 contract_type_rec contract_type_csr%ROWTYPE;
325 BEGIN
326
327 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
328 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
329 'Entered OKC_REP_QA_CHECK_PVT.validate_contract_type ');
330 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
331 'Contract type is: ' || p_contract_type);
332 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
333 'Severity level is: ' || p_severity);
334 END IF;
335
336 -- Standard call to check for call compatibility.
337 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
339 END IF;
340 -- Initialize message list if p_init_msg_list is set to TRUE.
341 IF FND_API.to_Boolean( p_init_msg_list ) THEN
342 FND_MSG_PUB.initialize;
343 END IF;
344
345 -- Initialize API return status to success
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347 l_valid_contract_type_flag := 'Y';
348
349 OPEN contract_type_csr;
350 FETCH contract_type_csr INTO contract_type_rec;
351 IF contract_type_csr%NOTFOUND THEN
352 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
353 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
354 G_MODULE||l_api_name, 'Contract Type: '|| p_contract_type || ' does not exist');
355 END IF;
356 RAISE FND_API.G_EXC_ERROR;
357 END IF; -- contract_type_csr%NOTFOUND
358 l_contract_type_name := contract_type_rec.name;
359 IF (sysdate BETWEEN nvl(contract_type_rec.start_date, sysdate) AND nvl(contract_type_rec.end_date, sysdate)) THEN
360 l_valid_contract_type_flag := 'Y';
361 ELSE
362 l_valid_contract_type_flag := 'N';
363 END IF;
364 CLOSE contract_type_csr;
365
366 --Check for contract type
367 IF (l_valid_contract_type_flag = 'N') THEN
368 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
369 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
370 'Contract Type is invalid');
371 END IF;
372 l_index := px_qa_result_tbl.count + 1;
373 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
374 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_CONTRACT_TYPE_T);
375 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_CONTRACT_TYPE;
376 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_CONTRACT_TYPE;
377 -- Bug 4702590. Removed the suggetion message
378 --px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_CONTRACT_TYPE_S);
379 px_qa_result_tbl(l_index).error_severity := p_severity;
380 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_CONTRACT_TYPE_SD);
381 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
382 p_app_name => G_OKC,
383 p_msg_name => G_OKC_REP_INV_CONTRACT_TYPE,
384 p_token1 => G_CONTRACT_TYPE_TOKEN,
385 p_token1_value => l_contract_type_name);
386 END IF; -- l_valid_contract_type_flag = 'N'
387 -- Standard call to get message count and if count is 1, get message info.
388 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
389
390 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
391 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
392 'Leaving OKC_REP_QA_CHECK_PVT.validate_contract_type');
393 END IF;
394 EXCEPTION
395 WHEN FND_API.G_EXC_ERROR THEN
396 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
397 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
398 'Leaving OKC_REP_QA_CHECK_PVT.validate_contract_type with G_EXC_ERROR');
399 END IF;
400 IF (contract_type_csr%ISOPEN) THEN
401 CLOSE contract_type_csr ;
402 END IF;
403 x_return_status := G_RET_STS_ERROR;
404 FND_MSG_PUB.Count_And_Get(
405 p_count => x_msg_count,
406 p_data => x_msg_data
407 );
408
409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
410 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
411 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
412 'Leaving OKC_REP_QA_CHECK_PVT.validate_contract_type with G_EXC_UNEXPECTED_ERROR');
413 END IF;
414 IF (contract_type_csr%ISOPEN) THEN
415 CLOSE contract_type_csr ;
416 END IF;
417 x_return_status := G_RET_STS_UNEXP_ERROR;
418 FND_MSG_PUB.Count_And_Get(
419 p_count => x_msg_count,
420 p_data => x_msg_data
421 );
422
423 WHEN OTHERS THEN
424 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
425 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
426 G_MODULE || l_api_name,
427 'Leaving OKC_REP_QA_CHECK_PVT.validate_contract_type because of EXCEPTION: ' || sqlerrm);
428 END IF;
429 IF (contract_type_csr%ISOPEN) THEN
430 CLOSE contract_type_csr ;
431 END IF;
432 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
433 p_msg_name => G_UNEXPECTED_ERROR,
434 p_token1 => G_SQLCODE_TOKEN,
435 p_token1_value => sqlcode,
436 p_token2 => G_SQLERRM_TOKEN,
437 p_token2_value => sqlerrm);
438 x_return_status := G_RET_STS_UNEXP_ERROR;
439 FND_MSG_PUB.Count_And_Get(
440 p_count => x_msg_count,
441 p_data => x_msg_data
442 );
443 END validate_contract_type;
444
445
446 -- Start of comments
447 --API name : validate_external_party
448 --Type : Private.
449 --Function : This procedure checks for validity of the external party
450 -- : and modifies px_qa_result_tbl table of records that contains validation
451 -- : errors and warnings
452 --Pre-reqs : None.
453 --Parameters :
454 --IN : p_api_version IN NUMBER Required
455 -- : p_init_msg_list IN VARCHAR2 Optional
456 -- Default = FND_API.G_FALSE
457 -- : p_party_role_code IN VARCHER Required
458 -- external party role code
459 -- : p_party_id IN NUMBER Required
460 -- party id of the external party.
461 -- : p_severity IN VARCHAR2 Required
462 -- Severity level for this check. Possible values are ERROR, WARNING.
463 --INOUT : px_qa_result_tbl
464 -- The table of records that contains validation errors and warnings
465 --OUT : x_return_status OUT VARCHAR2(1)
466 -- : x_msg_count OUT NUMBER
467 -- : x_msg_data OUT VARCHAR2(2000)
468 --Note :
469 -- End of comments
470 PROCEDURE validate_external_party (
471 p_api_version IN NUMBER,
472 p_init_msg_list IN VARCHAR2,
473 p_party_role_code IN VARCHAR2,
474 p_party_id IN NUMBER,
475 p_severity IN VARCHAR2,
476 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
477 x_msg_data OUT NOCOPY VARCHAR2,
478 x_msg_count OUT NOCOPY NUMBER,
479 x_return_status OUT NOCOPY VARCHAR2
480 ) IS
481
482 l_api_version CONSTANT NUMBER := 1;
483 l_api_name CONSTANT VARCHAR2(30) := 'validate_external_party';
484 l_index PLS_INTEGER := 0;
485 l_valid_external_party_flag VARCHAR2(1);
486 l_party_name VARCHAR2(450); -- set to 360 in OKC_REP_IMP_PARTIES_T
487 -- For HZ_PARTY validation, using document "TCA Usage Guideline" Version 3.0
488 CURSOR partner_csr IS
489 SELECT party_name, status
490 FROM hz_parties
491 WHERE party_id = p_party_id
492 AND party_type = 'ORGANIZATION';
493 partner_rec partner_csr%ROWTYPE;
494
495 CURSOR vendor_csr IS --enabled flag should be Y for active vendors
496 SELECT vendor_name, enabled_flag,
497 start_date_active,
498 end_date_active
499 FROM po_vendors
500 WHERE vendor_id = p_party_id;
501 vendor_rec vendor_csr%ROWTYPE;
502
503 BEGIN
504 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
505 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
506 'Entered OKC_REP_QA_CHECK_PVT.validate_external_party ');
507 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
508 'Party Role Code is: ' || p_party_role_code);
509 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
510 'Party Id is: ' || p_party_id);
511 END IF;
512
513 -- Standard call to check for call compatibility.
514 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
516 END IF;
517 -- Initialize message list if p_init_msg_list is set to TRUE.
518 IF FND_API.to_Boolean( p_init_msg_list ) THEN
519 FND_MSG_PUB.initialize;
520 END IF;
521
522 -- Initialize API return status to success
523 x_return_status := FND_API.G_RET_STS_SUCCESS;
524 l_valid_external_party_flag := 'Y';
525
526 IF (p_party_role_code = G_PARTY_ROLE_PARTNER OR
527 p_party_role_code = G_PARTY_ROLE_CUSTOMER) THEN
528 OPEN partner_csr;
529 FETCH partner_csr INTO partner_rec;
530 IF partner_csr%NOTFOUND THEN
531 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
532 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
533 G_MODULE||l_api_name, 'Party with Party Id: '|| p_party_id || ' does not exist');
534 END IF;
535 RAISE FND_API.G_EXC_ERROR;
536 END IF; -- partner_csr%NOTFOUND
537 l_party_name := partner_rec.party_name;
538 if (partner_rec.status <> 'A') THEN
539 l_valid_external_party_flag := 'N';
540 END IF; -- partner_rec.status <> 'A'
541 CLOSE partner_csr;
542 ELSE
543 OPEN vendor_csr;
544 FETCH vendor_csr INTO vendor_rec;
545 IF vendor_csr%NOTFOUND THEN
546 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
547 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
548 G_MODULE||l_api_name, 'Vendor with vendor Id: '|| p_party_id || ' does not exist');
549 END IF;
550 RAISE FND_API.G_EXC_ERROR;
551 END IF; -- vendor_csr%NOTFOUND
552 l_party_name := vendor_rec.vendor_name;
553 if (vendor_rec.enabled_flag <> 'Y' AND
554 SYSDATE BETWEEN NVL(vendor_rec.start_date_active, SYSDATE - 1) AND NVL(vendor_rec.end_date_active, SYSDATE + 1)) THEN
555 l_valid_external_party_flag := 'N';
556 END IF;
557 CLOSE vendor_csr;
558 END IF; -- p_party_role_code = G_PARTY_ROLE_PARTNER
559
560 --Check for external party
561 IF (l_valid_external_party_flag = 'N') THEN
562 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
563 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
564 'External party is invalid: ' || l_party_name);
565 END IF;
566 l_index := px_qa_result_tbl.count + 1;
567 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
568 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_EXT_PARTY_T);
569 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_EXT_PARTY;
570 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_EXT_PARTY;
571 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_EXT_PARTY_S);
572 px_qa_result_tbl(l_index).error_severity := p_severity;
573 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_EXT_PARTY_SD);
574 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
575 p_app_name => G_OKC,
576 p_msg_name => G_OKC_REP_INV_EXT_PARTY,
577 p_token1 => G_PARTY_NAME_TOKEN,
578 p_token1_value => l_party_name);
579 END IF; -- l_valid_external_party_flag = 'N'
580
581 -- Standard call to get message count and if count is 1, get message info.
582 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
583
584 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
585 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
586 'Leaving OKC_REP_QA_CHECK_PVT.validate_external_party');
587 END IF;
588 EXCEPTION
589 WHEN FND_API.G_EXC_ERROR THEN
590 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
591 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
592 'Leaving OKC_REP_QA_CHECK_PVT.validate_external_party with G_EXC_ERROR');
593 END IF;
594 IF (partner_csr%ISOPEN) THEN
595 CLOSE partner_csr ;
596 END IF;
597 IF (vendor_csr%ISOPEN) THEN
598 CLOSE vendor_csr ;
599 END IF;
600 x_return_status := G_RET_STS_ERROR;
601 FND_MSG_PUB.Count_And_Get(
602 p_count => x_msg_count,
603 p_data => x_msg_data
604 );
605
606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
607 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
608 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
609 'Leaving OKC_REP_QA_CHECK_PVT.validate_external_party with G_EXC_UNEXPECTED_ERROR');
610 END IF;
611 IF (partner_csr%ISOPEN) THEN
612 CLOSE partner_csr ;
613 END IF;
614 IF (vendor_csr%ISOPEN) THEN
615 CLOSE vendor_csr ;
616 END IF;
617 x_return_status := G_RET_STS_UNEXP_ERROR;
618 FND_MSG_PUB.Count_And_Get(
619 p_count => x_msg_count,
620 p_data => x_msg_data
621 );
622
623 WHEN OTHERS THEN
624 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
625 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
626 G_MODULE || l_api_name,
627 'Leaving OKC_REP_QA_CHECK_PVT.validate_external_party because of EXCEPTION: ' || sqlerrm);
628 END IF;
629 IF (partner_csr%ISOPEN) THEN
630 CLOSE partner_csr ;
631 END IF;
632 IF (vendor_csr%ISOPEN) THEN
633 CLOSE vendor_csr ;
634 END IF;
635 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
636 p_msg_name => G_UNEXPECTED_ERROR,
637 p_token1 => G_SQLCODE_TOKEN,
638 p_token1_value => sqlcode,
639 p_token2 => G_SQLERRM_TOKEN,
640 p_token2_value => sqlerrm);
641 x_return_status := G_RET_STS_UNEXP_ERROR;
642 FND_MSG_PUB.Count_And_Get(
643 p_count => x_msg_count,
644 p_data => x_msg_data
645 );
646 END validate_external_party;
647
648
649
650
651 -- Start of comments
652 --API name : validate_contact
653 --Type : Private.
654 --Function : This procedure checks for validity of the party contact
655 -- : and modifies px_qa_result_tbl table of records that contains validation
656 -- : errors and warnings
657 --Pre-reqs : None.
658 --Parameters :
659 --IN : p_api_version IN NUMBER Required
660 -- : p_init_msg_list IN VARCHAR2 Optional
661 -- Default = FND_API.G_FALSE
662 -- : p_party_role_code IN VARCHER Required
663 -- external party role code
664 -- : p_party_id IN NUMBER Required
665 -- party id of the external party.
666 -- : p_contact_id IN NUMBER Required
667 -- contact id of the party conatct.
668 -- : p_severity IN VARCHAR2 Required
669 -- Severity level for this check. Possible values are ERROR, WARNING.
670 --INOUT : px_qa_result_tbl
671 -- The table of records that contains validation errors and warnings
672 --OUT : x_return_status OUT VARCHAR2(1)
673 -- : x_msg_count OUT NUMBER
674 -- : x_msg_data OUT VARCHAR2(2000)
675 --Note :
676 -- End of comments
677 PROCEDURE validate_contact (
678 p_api_version IN NUMBER,
679 p_init_msg_list IN VARCHAR2,
680 p_party_role_code IN VARCHAR2,
681 p_party_id IN NUMBER,
682 p_contact_id IN NUMBER,
683 p_severity IN VARCHAR2,
684 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
685 x_msg_data OUT NOCOPY VARCHAR2,
686 x_msg_count OUT NOCOPY NUMBER,
687 x_return_status OUT NOCOPY VARCHAR2
688 ) IS
689
690 l_api_version CONSTANT NUMBER := 1;
691 l_api_name CONSTANT VARCHAR2(30) := 'validate_contact';
692 l_index PLS_INTEGER := 0;
693 l_valid_contact_flag VARCHAR2(1);
694 l_contact_name VARCHAR2(450); -- set to 360 in OKC_REP_IMP_PARTIES_T
695
696 CURSOR tca_contact_csr IS
697 SELECT hz.party_name contact_name,
698 hr.status relationship_status,
699 hr.start_date start_date,
700 hr.end_date end_date
701 FROM hz_parties hz,
702 hz_relationships hr
703 WHERE hr.object_id = p_party_id -- The party being passsed
704 AND hz.party_id = p_contact_id -- The contact id
705 AND hr.object_type = 'ORGANIZATION'
706 AND hr.object_table_name = 'HZ_PARTIES'
707 AND hr.subject_type = 'PERSON'
708 AND ((hr.relationship_code = 'CONTACT_OF') OR (hr.relationship_code = 'EMPLOYEE_OF'))
709 AND hz.party_id = hr.party_id;
710
711 tca_contact_rec tca_contact_csr%ROWTYPE;
712
713 CURSOR vendor_contact_csr IS
714 SELECT (first_name || ' ' || middle_name || ' ' || last_name) contact_name,
715 inactive_date
716 FROM po_vendor_contacts
717 WHERE vendor_contact_id=p_contact_id;
718
719 vendor_contact_rec vendor_contact_csr%ROWTYPE;
720
721 -- Bug 6598261.Changed per_all_workforce_v to per_workforce_v.
722
723 CURSOR employee_contact_csr IS
724 SELECT full_name contact_name
725 FROM per_workforce_v
726 WHERE person_id = p_contact_id;
727
728 CURSOR employee_name_csr IS
729 SELECT per.full_name contact_name
730 FROM per_all_people_f per
731 WHERE per.person_id = p_contact_id
732 AND per.effective_start_date = (SELECT MAX(effective_start_date)
733 FROM per_all_people_f
734 WHERE person_id = per.person_id);
735
736
737 employee_contact_rec employee_contact_csr%ROWTYPE;
738
739
740 BEGIN
741 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
743 'Entered OKC_REP_QA_CHECK_PVT.validate_contact ');
744 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
745 'Party Role Code is: ' || p_party_role_code);
746 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
747 'Party Id is: ' || p_party_id);
748 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
749 'Contact Id is: ' || p_contact_id);
750 END IF;
751
752 -- Standard call to check for call compatibility.
753 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
755 END IF;
756 -- Initialize message list if p_init_msg_list is set to TRUE.
757 IF FND_API.to_Boolean( p_init_msg_list ) THEN
758 FND_MSG_PUB.initialize;
759 END IF;
760
761 -- Initialize API return status to success
762 x_return_status := FND_API.G_RET_STS_SUCCESS;
763 l_valid_contact_flag := 'Y';
764
765 IF ((p_party_role_code = G_PARTY_ROLE_PARTNER) OR (p_party_role_code = G_PARTY_ROLE_CUSTOMER)) THEN
766
767 OPEN tca_contact_csr;
768 FETCH tca_contact_csr INTO tca_contact_rec;
769
770 IF tca_contact_csr%NOTFOUND THEN
771 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
772 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
773 G_MODULE||l_api_name, 'Contact with Contact Id: '|| p_contact_id || ' does not exist');
774 END IF;
775 RAISE FND_API.G_EXC_ERROR;
776 END IF; -- partner_csr%NOTFOUND
777
778 l_contact_name := tca_contact_rec.contact_name;
779
780 IF ((tca_contact_rec.relationship_status = 'A') AND
781 (sysdate BETWEEN nvl(tca_contact_rec.start_date, sysdate) AND nvl(tca_contact_rec.end_date, sysdate))) THEN -- Need to check for date as well.
782 l_valid_contact_flag := 'Y';
783 ELSE
784 l_valid_contact_flag := 'N';
785 END IF; -- partner_rec.relationship_status = 'A'
786
787 CLOSE tca_contact_csr;
788
789 ELSIF (p_party_role_code = G_PARTY_ROLE_SUPPLIER) THEN -- Vendor Party Contact
790
791 OPEN vendor_contact_csr;
792 FETCH vendor_contact_csr INTO vendor_contact_rec;
793
794 IF vendor_contact_csr%NOTFOUND THEN
795 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
796 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
797 G_MODULE||l_api_name, 'Vendor contact Id: '|| p_contact_id || ' does not exist');
798 END IF;
799 RAISE FND_API.G_EXC_ERROR;
800 END IF; -- vendor_contact_csr%NOTFOUND
801
802 l_contact_name := vendor_contact_rec.contact_name;
803
804 IF (vendor_contact_rec.inactive_date <= sysdate) THEN
805 l_valid_contact_flag := 'N';
806 END IF; -- vendor_contact_rec.status <> 'A'
807
808 CLOSE vendor_contact_csr;
809
810 ELSE -- Internal Party Contact
811
812 -- Fetch Internal contact record
813 OPEN employee_contact_csr;
814 FETCH employee_contact_csr INTO employee_contact_rec;
815
816 -- If row doesn't exist then flag the current contact as invalid
817 IF employee_contact_csr%NOTFOUND THEN
818 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
819 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
820 G_MODULE||l_api_name, 'Contact with Contact Id: '|| p_contact_id || ' does not exist');
821 END IF;
822
823 l_valid_contact_flag := 'N';
824 END IF; -- employee_contact_csr%ROWCOUNT <= 0
825
826 l_contact_name := employee_contact_rec.contact_name;
827
828 CLOSE employee_contact_csr;
829 END IF; -- p_party_role_code = G_PARTY_ROLE_PARTNER
830
831 -- If the current contact is not valid, then log the error message
832 IF (l_valid_contact_flag = 'N') THEN
833
834 -- Get name of the contact
835 OPEN employee_name_csr;
836 FETCH employee_name_csr INTO l_contact_name;
837 CLOSE employee_name_csr;
838
839 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
840 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
841 'Party Contact is invalid: ' || l_contact_name);
842 END IF;
843 l_index := px_qa_result_tbl.count + 1;
844 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
845 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_CONTACT_T);
846 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_CONTACT;
847 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_CONTACT;
848 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_CONTACT_S);
849 px_qa_result_tbl(l_index).error_severity := p_severity;
850 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_CONTACT_SD);
851 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
852 p_app_name => G_OKC,
853 p_msg_name => G_OKC_REP_INV_CONTACT,
854 p_token1 => G_CONTACT_NAME_TOKEN,
855 p_token1_value => l_contact_name);
856 END IF; -- _valid_external_party_flag = 'N'
857
858 -- Standard call to get message count and if count is 1, get message info.
859 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
860
861 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
862 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
863 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact');
864 END IF;
865 EXCEPTION
866 WHEN FND_API.G_EXC_ERROR THEN
867 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
868 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
869 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact with G_EXC_ERROR');
870 END IF;
871 IF (tca_contact_csr%ISOPEN) THEN
872 CLOSE tca_contact_csr ;
873 END IF;
874 IF (vendor_contact_csr%ISOPEN) THEN
875 CLOSE vendor_contact_csr ;
876 END IF;
877 IF (employee_contact_csr%ISOPEN) THEN
878 CLOSE employee_contact_csr ;
879 END IF;
880 IF (employee_name_csr%ISOPEN) THEN
881 CLOSE employee_name_csr ;
882 END IF;
883 x_return_status := G_RET_STS_ERROR;
884 FND_MSG_PUB.Count_And_Get(
885 p_count => x_msg_count,
886 p_data => x_msg_data
887 );
888
889 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
890 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
891 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
892 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact with G_EXC_UNEXPECTED_ERROR');
893 END IF;
894 IF (tca_contact_csr%ISOPEN) THEN
895 CLOSE tca_contact_csr ;
896 END IF;
897 IF (vendor_contact_csr%ISOPEN) THEN
898 CLOSE vendor_contact_csr ;
899 END IF;
900 IF (employee_contact_csr%ISOPEN) THEN
901 CLOSE employee_contact_csr ;
902 END IF;
903 IF (employee_name_csr%ISOPEN) THEN
904 CLOSE employee_name_csr ;
905 END IF;
906 x_return_status := G_RET_STS_UNEXP_ERROR;
907 FND_MSG_PUB.Count_And_Get(
908 p_count => x_msg_count,
909 p_data => x_msg_data
910 );
911
912 WHEN OTHERS THEN
913 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
914 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
915 G_MODULE || l_api_name,
916 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact because of EXCEPTION: ' || sqlerrm);
917 END IF;
918 IF (tca_contact_csr%ISOPEN) THEN
919 CLOSE tca_contact_csr ;
920 END IF;
921 IF (vendor_contact_csr%ISOPEN) THEN
922 CLOSE vendor_contact_csr ;
923 END IF;
924 IF (employee_contact_csr%ISOPEN) THEN
925 CLOSE employee_contact_csr ;
926 END IF;
927 IF (employee_name_csr%ISOPEN) THEN
928 CLOSE employee_name_csr ;
929 END IF;
930 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
931 p_msg_name => G_UNEXPECTED_ERROR,
932 p_token1 => G_SQLCODE_TOKEN,
933 p_token1_value => sqlcode,
934 p_token2 => G_SQLERRM_TOKEN,
935 p_token2_value => sqlerrm);
936 x_return_status := G_RET_STS_UNEXP_ERROR;
937 FND_MSG_PUB.Count_And_Get(
938 p_count => x_msg_count,
939 p_data => x_msg_data
940 );
941 END validate_contact;
942
943
944
945
946
947 -- Start of comments
948 --API name : validate_contact_role
949 --Type : Private.
950 --Function : This procedure checks for validity of the contract role being passed as an input param.
951 -- : and modifies px_qa_result_tbl table of records that contains validation
952 -- : errors and warnings
953 --Pre-reqs : None.
954 --Parameters :
955 --IN : p_api_version IN NUMBER Required
956 -- : p_init_msg_list IN VARCHAR2 Optional
957 -- Default = FND_API.G_FALSE
958 -- : p_contact_role_id IN NUMBER Required
959 -- Contract Role Id to be validated
960 -- : p_severity IN VARCHAR2 Required
961 -- Severity level for this check. Possible values are ERROR, WARNING.
962 --INOUT : px_qa_result_tbl
963 -- The table of records that contains validation errors and warnings
964 --OUT : x_return_status OUT VARCHAR2(1)
965 -- : x_msg_count OUT NUMBER
966 -- : x_msg_data OUT VARCHAR2(2000)
967 --Note :
968 -- End of comments
969 PROCEDURE validate_contact_role (
970 p_api_version IN NUMBER,
971 p_init_msg_list IN VARCHAR2,
972 p_contact_role_id IN NUMBER,
973 p_severity IN VARCHAR2,
974 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
975 x_msg_data OUT NOCOPY VARCHAR2,
976 x_msg_count OUT NOCOPY NUMBER,
977 x_return_status OUT NOCOPY VARCHAR2
978 ) IS
979
980 l_api_version CONSTANT NUMBER := 1;
981 l_api_name CONSTANT VARCHAR2(30) := 'validate_contact_role';
982 l_index PLS_INTEGER := 0;
983 l_valid_contact_role_flag VARCHAR2(1);
984 l_contact_role_name OKC_REP_CONTACT_ROLES_TL.NAME%TYPE;
985
986 CURSOR contact_role_csr IS
987 SELECT name, start_date, end_date
988 FROM okc_rep_contact_roles_vl
989 WHERE contact_role_id = p_contact_role_id;
990 contact_role_rec contact_role_csr%ROWTYPE;
991 BEGIN
992
993 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
994 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
995 'Entered OKC_REP_QA_CHECK_PVT.validate_contact_role ');
996 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
997 'Contact Role Id is: ' || p_contact_role_id);
998 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
999 'Severity level is: ' || p_severity);
1000 END IF;
1001
1002 -- Standard call to check for call compatibility.
1003 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1005 END IF;
1006 -- Initialize message list if p_init_msg_list is set to TRUE.
1007 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1008 FND_MSG_PUB.initialize;
1009 END IF;
1010
1011 -- Initialize API return status to success
1012 x_return_status := FND_API.G_RET_STS_SUCCESS;
1013 l_valid_contact_role_flag := 'Y';
1014
1015 OPEN contact_role_csr;
1016 FETCH contact_role_csr INTO contact_role_rec;
1017 IF contact_role_csr%NOTFOUND THEN
1018 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1020 G_MODULE||l_api_name, 'Contact role with id : '|| p_contact_role_id || ' does not exist');
1021 END IF;
1022 RAISE FND_API.G_EXC_ERROR;
1023 END IF; -- contact_role_csr%NOTFOUND
1024 l_contact_role_name := contact_role_rec.name;
1025 IF (sysdate BETWEEN nvl(contact_role_rec.start_date, sysdate) AND nvl(contact_role_rec.end_date, sysdate)) THEN
1026 l_valid_contact_role_flag := 'Y';
1027 ELSE
1028 l_valid_contact_role_flag := 'N';
1029 END IF;
1030 CLOSE contact_role_csr;
1031
1032 --Check for Contract Role
1033 IF (l_valid_contact_role_flag = 'N') THEN
1034 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1035 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
1036 'Contract Role is invalid');
1037 END IF;
1038 l_index := px_qa_result_tbl.count + 1;
1039 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
1040 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_CONTACT_ROLE_T);
1041 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_CONTACT_ROLE;
1042 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_CONTACT_ROLE;
1043 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_CONTACT_ROLE_S);
1044 px_qa_result_tbl(l_index).error_severity := p_severity;
1045 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_CONTACT_ROLE_SD);
1046 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
1047 p_app_name => G_OKC,
1048 p_msg_name => G_OKC_REP_INV_CONTACT_ROLE,
1049 p_token1 => G_CONTACT_ROLE_TOKEN,
1050 p_token1_value => l_contact_role_name);
1051 END IF; -- l_valid_contact_role_flag = 'N'
1052 -- Standard call to get message count and if count is 1, get message info.
1053 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1054
1055 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1056 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1057 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact_role');
1058 END IF;
1059 EXCEPTION
1060 WHEN FND_API.G_EXC_ERROR THEN
1061 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1062 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
1063 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact_role with G_EXC_ERROR');
1064 END IF;
1065 IF (contact_role_csr%ISOPEN) THEN
1066 CLOSE contact_role_csr ;
1067 END IF;
1068 x_return_status := G_RET_STS_ERROR;
1069 FND_MSG_PUB.Count_And_Get(
1070 p_count => x_msg_count,
1071 p_data => x_msg_data
1072 );
1073
1074 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1075 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1076 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1077 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact_role with G_EXC_UNEXPECTED_ERROR');
1078 END IF;
1079 IF (contact_role_csr%ISOPEN) THEN
1080 CLOSE contact_role_csr ;
1081 END IF;
1082 x_return_status := G_RET_STS_UNEXP_ERROR;
1083 FND_MSG_PUB.Count_And_Get(
1084 p_count => x_msg_count,
1085 p_data => x_msg_data
1086 );
1087
1088 WHEN OTHERS THEN
1089 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1090 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1091 G_MODULE || l_api_name,
1092 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact_role because of EXCEPTION: ' || sqlerrm);
1093 END IF;
1094 IF (contact_role_csr%ISOPEN) THEN
1095 CLOSE contact_role_csr ;
1096 END IF;
1097 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1098 p_msg_name => G_UNEXPECTED_ERROR,
1099 p_token1 => G_SQLCODE_TOKEN,
1100 p_token1_value => sqlcode,
1101 p_token2 => G_SQLERRM_TOKEN,
1102 p_token2_value => sqlerrm);
1103 x_return_status := G_RET_STS_UNEXP_ERROR;
1104 FND_MSG_PUB.Count_And_Get(
1105 p_count => x_msg_count,
1106 p_data => x_msg_data
1107 );
1108 END validate_contact_role;
1109
1110
1111
1112
1113 -- Start of comments
1114 --API name : validate_risk_event
1115 --Type : Private.
1116 --Function : This procedure checks for validity of the risk event being passed as an input param.
1117 -- : and modifies px_qa_result_tbl table of records that contains validation
1118 -- : errors and warnings
1119 --Pre-reqs : None.
1120 --Parameters :
1121 --IN : p_api_version IN NUMBER Required
1122 -- : p_init_msg_list IN VARCHAR2 Optional
1123 -- Default = FND_API.G_FALSE
1124 -- : p_contact_role IN NUMBER Required
1125 -- Contract type to be validated
1126 -- : p_severity IN VARCHAR2 Required
1127 -- Severity level for this check. Possible values are ERROR, WARNING.
1128 --INOUT : px_qa_result_tbl
1129 -- The table of records that contains validation errors and warnings
1130 --OUT : x_return_status OUT VARCHAR2(1)
1131 -- : x_msg_count OUT NUMBER
1132 -- : x_msg_data OUT VARCHAR2(2000)
1133 --Note :
1134 -- End of comments
1135 PROCEDURE validate_risk_event (
1136 p_api_version IN NUMBER,
1137 p_init_msg_list IN VARCHAR2,
1138 p_risk_event_id IN NUMBER,
1139 p_severity IN VARCHAR2,
1140 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
1141 x_msg_data OUT NOCOPY VARCHAR2,
1142 x_msg_count OUT NOCOPY NUMBER,
1143 x_return_status OUT NOCOPY VARCHAR2
1144 ) IS
1145
1146 l_api_version CONSTANT NUMBER := 1;
1147 l_api_name CONSTANT VARCHAR2(30) := 'validate_risk_event';
1148 l_index PLS_INTEGER := 0;
1149 l_valid_risk_event_flag VARCHAR2(1);
1150 l_risk_event_name OKC_RISK_EVENTS_TL.NAME%TYPE;
1151
1152 CURSOR risk_event_csr IS
1153 SELECT name, start_date, end_date
1154 FROM okc_risk_events_vl
1155 WHERE risk_event_id = p_risk_event_id;
1156 risk_event_rec risk_event_csr%ROWTYPE;
1157 BEGIN
1158
1159 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1160 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1161 'Entered OKC_REP_QA_CHECK_PVT.validate_risk_event ');
1162 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1163 'Risk Event Id is: ' || p_risk_event_id);
1164 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1165 'Severity level is: ' || p_severity);
1166 END IF;
1167
1168 -- Standard call to check for call compatibility.
1169 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1171 END IF;
1172 -- Initialize message list if p_init_msg_list is set to TRUE.
1173 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1174 FND_MSG_PUB.initialize;
1175 END IF;
1176
1177 -- Initialize API return status to success
1178 x_return_status := FND_API.G_RET_STS_SUCCESS;
1179 l_valid_risk_event_flag := 'Y';
1180
1181 OPEN risk_event_csr;
1182 FETCH risk_event_csr INTO risk_event_rec;
1183 IF risk_event_csr%NOTFOUND THEN
1184 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1185 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1186 G_MODULE||l_api_name, 'Risk Event with id : '|| p_risk_event_id || ' does not exist');
1187 END IF;
1188 RAISE FND_API.G_EXC_ERROR;
1189 END IF; -- risk_event_csr%NOTFOUND
1190 l_risk_event_name := risk_event_rec.name;
1191 IF (sysdate BETWEEN nvl(risk_event_rec.start_date, sysdate) AND nvl(risk_event_rec.end_date, sysdate)) THEN
1192 l_valid_risk_event_flag := 'Y';
1193 ELSE
1194 l_valid_risk_event_flag := 'N';
1195 END IF;
1196 CLOSE risk_event_csr;
1197
1198 --Check for Risk Event
1199 IF (l_valid_risk_event_flag = 'N') THEN
1200 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1201 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
1202 'Risk Event is invalid');
1203 END IF;
1204 l_index := px_qa_result_tbl.count + 1;
1205 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
1206 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_RISK_EVENT_T);
1207 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_RISK_EVENT;
1208 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_RISK_EVENT;
1209 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_RISK_EVENT_S);
1210 px_qa_result_tbl(l_index).error_severity := p_severity;
1211 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_RISK_EVENT_SD);
1212 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
1213 p_app_name => G_OKC,
1214 p_msg_name => G_OKC_REP_INV_RISK_EVENT,
1215 p_token1 => G_RISK_EVENT_TOKEN,
1216 p_token1_value => l_risk_event_name);
1217 END IF; -- l_valid_risk_event_flag = 'N'
1218 -- Standard call to get message count and if count is 1, get message info.
1219 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1220
1221 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1222 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1223 'Leaving OKC_REP_QA_CHECK_PVT.validate_risk_event');
1224 END IF;
1225 EXCEPTION
1226 WHEN FND_API.G_EXC_ERROR THEN
1227 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1228 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
1229 'Leaving OKC_REP_QA_CHECK_PVT.validate_risk_event with G_EXC_ERROR');
1230 END IF;
1231 IF (risk_event_csr%ISOPEN) THEN
1232 CLOSE risk_event_csr ;
1233 END IF;
1234 x_return_status := G_RET_STS_ERROR;
1235 FND_MSG_PUB.Count_And_Get(
1236 p_count => x_msg_count,
1237 p_data => x_msg_data
1238 );
1239
1240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1241 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1242 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1243 'Leaving OKC_REP_QA_CHECK_PVT.validate_risk_event with G_EXC_UNEXPECTED_ERROR');
1244 END IF;
1245 IF (risk_event_csr%ISOPEN) THEN
1246 CLOSE risk_event_csr ;
1247 END IF;
1248 x_return_status := G_RET_STS_UNEXP_ERROR;
1249 FND_MSG_PUB.Count_And_Get(
1250 p_count => x_msg_count,
1251 p_data => x_msg_data
1252 );
1253
1254 WHEN OTHERS THEN
1255 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1256 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1257 G_MODULE || l_api_name,
1258 'Leaving OKC_REP_QA_CHECK_PVT.validate_risk_event because of EXCEPTION: ' || sqlerrm);
1259 END IF;
1260 IF (risk_event_csr%ISOPEN) THEN
1261 CLOSE risk_event_csr ;
1262 END IF;
1263 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1264 p_msg_name => G_UNEXPECTED_ERROR,
1265 p_token1 => G_SQLCODE_TOKEN,
1266 p_token1_value => sqlcode,
1267 p_token2 => G_SQLERRM_TOKEN,
1268 p_token2_value => sqlerrm);
1269 x_return_status := G_RET_STS_UNEXP_ERROR;
1270 FND_MSG_PUB.Count_And_Get(
1271 p_count => x_msg_count,
1272 p_data => x_msg_data
1273 );
1274 END validate_risk_event;
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285 -- Start of comments
1286 --API name : validate_repository_for_qa
1287 --Type : Private.
1288 --Function : This API performs QA check on a Repository Contract. The API check for:
1289 -- 1. Check contract for no external party (Warning)
1290 -- 2. Check contract for no effective date (Error)
1291 -- 3. Check contract for invalid contract type
1292 -- 4. Check contract for invalid external party type
1293 -- 5. Check contract for invalid contact
1294 -- 6. Check contract for invalid Risk Event
1295 -- 7. Check contract for invalid Risk Event
1296 -- 8. Calls OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa to qa check the
1297 -- deliverables.
1298 --Pre-reqs : None.
1299 --Parameters :
1300 --IN : p_api_version IN NUMBER Required
1301 -- : p_init_msg_list IN VARCHAR2 Optional
1302 -- : Default = FND_API.G_FALSE
1303 -- : p_contract_id IN NUMBER Required
1304 -- : Contract ID of the contract to be QA checked
1305 -- : p_contract_type IN NUMBER Required
1306 -- : Type of the contract to be QA checked
1307 --INOUT : p_qa_result_tbl IN OUT
1308 -- : The table of records that contains validation errors and warnings
1309 --OUT : x_return_status OUT VARCHAR2(1)
1310 -- : x_msg_count OUT NUMBER
1311 -- : x_msg_data OUT VARCHAR2(2000)
1312 -- Note :
1313 -- End of comments
1314
1315 PROCEDURE validate_repository_for_qa (
1316 p_api_version IN NUMBER,
1317 p_init_msg_list IN VARCHAR2,
1318 p_contract_type IN VARCHAR2,
1319 p_contract_id IN NUMBER,
1320 p_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
1321 x_msg_data OUT NOCOPY VARCHAR2,
1322 x_msg_count OUT NOCOPY NUMBER,
1323 x_return_status OUT NOCOPY VARCHAR2)
1324 IS
1325
1326 l_api_version CONSTANT NUMBER := 1;
1327 l_api_name CONSTANT VARCHAR2(30) := 'validate_repository_for_qa';
1328 -- Contract cursor
1329 CURSOR contract_csr IS
1330 SELECT contract_effective_date
1331 FROM okc_rep_contracts_all
1332 WHERE contract_id = p_contract_id;
1333 contract_rec contract_csr%ROWTYPE;
1334 -- Contract parties cursor, used for validating parties
1335 CURSOR party_csr IS
1336 SELECT party_id, party_role_code
1337 FROM okc_rep_contract_parties
1338 WHERE contract_id = p_contract_id
1339 AND party_role_code <> 'INTERNAL_ORG';
1340 party_rec party_csr%ROWTYPE;
1341 -- Contract contacts cursor, used for validating contacts, contact roles
1342 CURSOR party_contact_csr IS
1343 SELECT party_id, party_role_code, contact_id, contact_role_id
1344 FROM okc_rep_party_contacts
1345 WHERE contract_id = p_contract_id;
1346 party_contact_rec party_contact_csr%ROWTYPE;
1347 -- Contract risks cursor, used for validating risk events
1348 CURSOR risk_csr IS
1349 SELECT risk_event_id
1350 FROM okc_contract_risks
1351 WHERE business_document_type = p_contract_type
1352 AND business_document_id = p_contract_id;
1353 risk_rec risk_csr%ROWTYPE;
1354
1355 BEGIN
1356
1357 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1358 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1359 'Entered validate_repository_for_qa');
1360 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1361 'Contract Type is: ' || p_contract_type);
1362 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1363 'Contract Id is: ' || p_contract_id);
1364 END IF;
1365 x_return_status := G_RET_STS_SUCCESS;
1366 x_return_status := OKC_API.START_ACTIVITY(l_api_name,
1367 p_init_msg_list,
1368 '_PVT',
1369 x_return_status);
1370
1371 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1372 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1373 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1374 RAISE OKC_API.G_EXCEPTION_ERROR;
1375 END IF;
1376
1377 -- Initialize message list if p_init_msg_list is set to TRUE.
1378 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1379 FND_MSG_PUB.initialize;
1380 END IF;
1381
1382 --Check for external party
1383 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1384 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1385 'Calling OKC_REP_QA_CHECK_PVT.check_no_exteral_party() API');
1386 END IF;
1387 check_no_external_party (
1388 p_api_version => 1,
1389 p_init_msg_list => FND_API.G_FALSE,
1390 p_contract_id => p_contract_id,
1391 p_severity => G_QA_STS_WARNING,
1392 px_qa_result_tbl => p_qa_result_tbl,
1393 x_msg_data => x_msg_data,
1394 x_msg_count => x_msg_count,
1395 x_return_status => x_return_status
1396 );
1397 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1398 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1399 'Completed OKC_REP_QA_CHECK_PVT.check_no_exteral_party with returned status: ' || x_return_status);
1400 END IF;
1401 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1402 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1403 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1404 RAISE OKC_API.G_EXCEPTION_ERROR;
1405 END IF;
1406
1407 -- Get effective_date column
1408 OPEN contract_csr;
1409 FETCH contract_csr into contract_rec;
1410 IF(contract_csr%NOTFOUND) THEN
1411 RAISE NO_DATA_FOUND;
1412 END IF;
1413 -- Log effective date columns
1414 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1415 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
1416 G_MODULE||l_api_name,'Contract Effective Date is : '
1417 || contract_rec.contract_effective_date);
1418 END IF;
1419 --Check for null effective date
1420 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1421 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1422 'Calling OKC_REP_QA_CHECK_PVT.check_no_eff_date() API');
1423 END IF;
1424 check_no_eff_date (
1425 p_api_version => 1,
1426 p_init_msg_list => FND_API.G_FALSE,
1427 p_effective_date => contract_rec.contract_effective_date,
1428 p_severity => G_QA_STS_ERROR,
1429 px_qa_result_tbl => p_qa_result_tbl,
1430 x_msg_data => x_msg_data,
1431 x_msg_count => x_msg_count,
1432 x_return_status => x_return_status);
1433
1434 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1435 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1436 'Completed OKC_REP_QA_CHECK_PVT.check_no_eff_date with returned status: ' || x_return_status);
1437 END IF;
1438 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1439 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1440 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1441 RAISE OKC_API.G_EXCEPTION_ERROR;
1442 END IF;
1443
1444 --Validate contract type
1445 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1446 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1447 'Calling OKC_REP_QA_CHECK_PVT.validate_contract_type() API');
1448 END IF;
1449 validate_contract_type (
1450 p_api_version => 1,
1451 p_init_msg_list => FND_API.G_FALSE,
1452 p_contract_type => p_contract_type,
1453 p_severity => G_QA_STS_WARNING,
1454 px_qa_result_tbl => p_qa_result_tbl,
1455 x_msg_data => x_msg_data,
1456 x_msg_count => x_msg_count,
1457 x_return_status => x_return_status
1458 );
1459 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1460 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1461 'Completed OKC_REP_QA_CHECK_PVT.validate_contract_type with returned status: ' || x_return_status);
1462 END IF;
1463 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1464 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1465 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1466 RAISE OKC_API.G_EXCEPTION_ERROR;
1467 END IF;
1468
1469 -- Validate Parties
1470 -- Loop through all the external parties and check if these are still valid.
1471 FOR party_rec IN party_csr LOOP
1472 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1473 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1474 'Calling OKC_REP_QA_CHECK_PVT.validate_external_party() API');
1475 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1476 'Party Id is: '|| party_rec.party_id);
1477 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1478 'Party role code is: '|| party_rec.party_role_code);
1479 END IF;
1480 validate_external_party (
1481 p_api_version => 1,
1482 p_init_msg_list => FND_API.G_FALSE,
1483 p_party_role_code => party_rec.party_role_code,
1484 p_party_id => party_rec.party_id,
1485 p_severity => G_QA_STS_WARNING,
1486 px_qa_result_tbl => p_qa_result_tbl,
1487 x_msg_data => x_msg_data,
1488 x_msg_count => x_msg_count,
1489 x_return_status => x_return_status
1490 );
1491 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1492 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1493 'Completed OKC_REP_QA_CHECK_PVT.validate_external_party with returned status: ' || x_return_status);
1494 END IF;
1495 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1496 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1497 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1498 RAISE OKC_API.G_EXCEPTION_ERROR;
1499 END IF;
1500 END LOOP;
1501
1502 -- Validate contacts and contact roles
1503 -- Loop through all the external contacts and check if these are still valid.
1504 FOR party_contact_rec IN party_contact_csr LOOP
1505 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1506 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1507 'Calling OKC_REP_QA_CHECK_PVT.validate_contact() and validate_contact_role() APIs');
1508 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1509 'Party Id is: '|| party_contact_rec.party_id);
1510 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1511 'Party role code is: '|| party_contact_rec.party_role_code);
1512 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1513 'Contact Id is: '|| party_contact_rec.contact_id);
1514 END IF;
1515 -- Validate contact
1516 validate_contact (
1517 p_api_version => 1,
1518 p_init_msg_list => FND_API.G_FALSE,
1519 p_party_role_code => party_contact_rec.party_role_code,
1520 p_party_id => party_contact_rec.party_id,
1521 p_contact_id => party_contact_rec.contact_id,
1522 p_severity => G_QA_STS_WARNING,
1523 px_qa_result_tbl => p_qa_result_tbl,
1524 x_msg_data => x_msg_data,
1525 x_msg_count => x_msg_count,
1526 x_return_status => x_return_status
1527 );
1528 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1529 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1530 'Completed OKC_REP_QA_CHECK_PVT.validate_contact with returned status: ' || x_return_status);
1531 END IF;
1532 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1533 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1534 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1535 RAISE OKC_API.G_EXCEPTION_ERROR;
1536 END IF;
1537
1538 -- Validate contact role
1539 validate_contact_role (
1540 p_api_version => 1,
1541 p_init_msg_list => FND_API.G_FALSE,
1542 p_contact_role_id => party_contact_rec.contact_role_id,
1543 p_severity => G_QA_STS_WARNING,
1544 px_qa_result_tbl => p_qa_result_tbl,
1545 x_msg_data => x_msg_data,
1546 x_msg_count => x_msg_count,
1547 x_return_status => x_return_status
1548 );
1549 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1550 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1551 'Completed OKC_REP_QA_CHECK_PVT.validate_contact_role with returned status: ' || x_return_status);
1552 END IF;
1553 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1554 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1555 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1556 RAISE OKC_API.G_EXCEPTION_ERROR;
1557 END IF;
1558 END LOOP;
1559
1560
1561 -- Validate Parties
1562 -- Loop through all contract risk events and check if these are still valid.
1563 FOR risk_rec IN risk_csr LOOP
1564 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1565 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1566 'Calling OKC_REP_QA_CHECK_PVT.validate_risk_event() API');
1567 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1568 'Risk Event Id is: '|| risk_rec.risk_event_id);
1569 END IF;
1570 validate_risk_event (
1571 p_api_version => 1,
1572 p_init_msg_list => FND_API.G_FALSE,
1573 p_risk_event_id => risk_rec.risk_event_id,
1574 p_severity => G_QA_STS_WARNING,
1575 px_qa_result_tbl => p_qa_result_tbl,
1576 x_msg_data => x_msg_data,
1577 x_msg_count => x_msg_count,
1578 x_return_status => x_return_status
1579 );
1580 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1581 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1582 'Completed OKC_REP_QA_CHECK_PVT.validate_risk_event with returned status: ' || x_return_status);
1583 END IF;
1584 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1585 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1586 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1587 RAISE OKC_API.G_EXCEPTION_ERROR;
1588 END IF;
1589 END LOOP;
1590
1591 -- Close all cursors.
1592 CLOSE contract_csr;
1593 -- Standard call to get message count and if count is 1, get message info.
1594 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1595 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1596 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
1597 G_MODULE||l_api_name,'leaving OKC_REP_QA_CHECK_PVT.validate_repository_for_qa');
1598 END IF;
1599
1600 EXCEPTION
1601 WHEN FND_API.G_EXC_ERROR THEN
1602 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1603 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
1604 'Leaving OKC_REP_QA_CHECK_PVT.validate_repository_for_qa with G_EXC_ERROR');
1605 END IF;
1606 x_return_status := G_RET_STS_ERROR;
1607 --close cursors
1608 IF (contract_csr%ISOPEN) THEN
1609 CLOSE contract_csr ;
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 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1617 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1618 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1619 'Leaving OKC_REP_QA_CHECK_PVT.validate_repository_for_qa with G_EXC_UNEXPECTED_ERROR');
1620 END IF;
1621 x_return_status := G_RET_STS_UNEXP_ERROR;
1622 --close cursors
1623 IF (contract_csr%ISOPEN) THEN
1624 CLOSE contract_csr ;
1625 END IF;
1626 FND_MSG_PUB.Count_And_Get(
1627 p_count => x_msg_count,
1628 p_data => x_msg_data
1629 );
1630
1631 WHEN OTHERS THEN
1632 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1633 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1634 G_MODULE || l_api_name,
1635 'Leaving OKC_REP_QA_CHECK_PVT.validate_repository_for_qa because of EXCEPTION: ' || sqlerrm);
1636 END IF;
1637 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1638 p_msg_name => G_UNEXPECTED_ERROR,
1639 p_token1 => G_SQLCODE_TOKEN,
1640 p_token1_value => sqlcode,
1641 p_token2 => G_SQLERRM_TOKEN,
1642 p_token2_value => sqlerrm);
1643 --close cursors
1644 IF (contract_csr%ISOPEN) THEN
1645 CLOSE contract_csr ;
1646 END IF;
1647 x_return_status := G_RET_STS_UNEXP_ERROR;
1648 FND_MSG_PUB.Count_And_Get(
1649 p_count => x_msg_count,
1650 p_data => x_msg_data
1651 );
1652
1653 END validate_repository_for_qa;
1654
1655
1656
1657 -- Start of comments
1658 --API name : perform_contract_qa_check
1659 --Type : Private.
1660 --Function : This API performs QA check on a Repository Contract. The API check for:
1661 -- 1. Calls OKC_REP_QA_CHECK_PVT.validate_repository_for_qa() to qa check
1662 -- repository contracts.
1663 -- 2. Calls OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa to check the
1664 -- deliverables.
1665 --Pre-reqs : None.
1666 --Parameters :
1667 --IN : p_api_version IN NUMBER Required
1668 -- : p_init_msg_list IN VARCHAR2 Optional
1669 -- Default = FND_API.G_FALSE
1670 -- : p_contract_id IN NUMBER Required
1671 -- Contract ID of the contract to be QA checked
1672 --OUT : x_return_status OUT VARCHAR2(1)
1673 -- : x_msg_count OUT NUMBER
1674 -- : x_msg_data OUT VARCHAR2(2000)
1675 -- : x_qa_return_status OUT VARCHAR2 (1)
1676 -- QA Check return status. Possible values are S, W, E
1677 -- : x_sequence_id OUT NUMBER
1678 -- Sequence id of the qa check errors in OKC_QA_ERRORS_T table
1679 -- Note :
1680 -- End of comments
1681
1682 PROCEDURE perform_contract_qa_check (
1683 p_api_version IN NUMBER,
1684 p_init_msg_list IN VARCHAR2,
1685 p_contract_id IN NUMBER,
1686 x_msg_count OUT NOCOPY NUMBER,
1687 x_msg_data OUT NOCOPY VARCHAR2,
1688 x_return_status OUT NOCOPY VARCHAR2,
1689 x_qa_return_status OUT NOCOPY VARCHAR2,
1690 x_sequence_id OUT NOCOPY NUMBER)
1691
1692 IS
1693
1694 l_api_version CONSTANT NUMBER := 1;
1695 l_api_name CONSTANT VARCHAR2(30) := 'perform_contract_qa_check';
1696
1697 l_qa_result_tbl OKC_TERMS_QA_PVT.qa_result_tbl_type;
1698 l_bus_doc_date_events_tbl EVENT_TBL_TYPE;
1699 l_error_found Boolean := FALSE;
1700 l_warning_found Boolean := FALSE;
1701
1702 CURSOR contract_csr IS
1703 SELECT contract_type, contract_expiration_date, contract_effective_date
1704 FROM okc_rep_contracts_all
1705 WHERE contract_id = p_contract_id;
1706
1707 contract_rec contract_csr%ROWTYPE;
1708
1709 BEGIN
1710
1711 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1712 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1713 'Entered perform_contract_qa_check');
1714 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1715 'Contract Id is: ' || p_contract_id);
1716 END IF;
1717 x_qa_return_status := G_QA_STS_SUCCESS;
1718 x_return_status := G_RET_STS_SUCCESS;
1719 x_return_status := OKC_API.START_ACTIVITY(l_api_name,
1720 p_init_msg_list,
1721 '_PVT',
1722 x_return_status);
1723
1724 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1725 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1726 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1727 RAISE OKC_API.G_EXCEPTION_ERROR;
1728 END IF;
1729
1730 -- Initialize message list if p_init_msg_list is set to TRUE.
1731 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1732 FND_MSG_PUB.initialize;
1733 END IF;
1734
1735 -- Get effective dates and contract_type of the contract.
1736 OPEN contract_csr;
1737 FETCH contract_csr INTO contract_rec;
1738 IF(contract_csr%NOTFOUND) THEN
1739 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1740 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1741 G_MODULE||l_api_name,
1742 'Invalid Contract Id: '|| p_contract_id);
1743 END IF;
1744 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1745 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
1746 p_token1 => G_CONTRACT_ID_TOKEN,
1747 p_token1_value => to_char(p_contract_id));
1748 RAISE FND_API.G_EXC_ERROR;
1749 -- RAISE NO_DATA_FOUND;
1750 END IF;
1751
1752
1753 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1754 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1755 'Contract Type is: ' || contract_rec.contract_type);
1756 END IF;
1757
1758 -- Repository Enhancement 12.1 (For Validate Action)
1759
1760 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1761 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1762 'Calling OKC_TERMS_QA_GRP.QA_Doc API');
1763 END IF;
1764
1765
1766 FND_MSG_PUB.initialize;
1767 OKC_TERMS_QA_GRP.QA_Doc (p_api_version => 1,
1768 p_init_msg_list => 'T',
1769 x_return_status => x_return_status,
1770 x_msg_data => x_msg_data,
1771 x_msg_count => x_msg_count,
1772 p_qa_mode => G_NORMAL_QA,
1773 p_doc_type => contract_rec.contract_type,
1774 p_doc_id => p_contract_id,
1775 x_qa_result_tbl => l_qa_result_tbl,
1776 x_qa_return_status => x_qa_return_status);
1777
1778 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1779 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1780 'Completed OKC_TERMS_QA_GRP.QA_Doc with returned status: ' || x_return_status);
1781 END IF;
1782
1783 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1784 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1785 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1786 RAISE OKC_API.G_EXCEPTION_ERROR;
1787 END IF;
1788 -- Repository Enhancement 12.1 Ends (For Validate Action)
1789
1790 -- Make call for Repository QA check
1791 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1792 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1793 'Calling OKC_REP_QA_CHECK_PVT.validate_repository_for_qa() API');
1794 END IF;
1795 validate_repository_for_qa (
1796 p_api_version => 1,
1797 p_init_msg_list => FND_API.G_FALSE,
1798 p_contract_type => contract_rec.contract_type,
1799 p_contract_id => p_contract_id,
1800 p_qa_result_tbl => l_qa_result_tbl,
1801 x_msg_data => x_msg_data,
1802 x_msg_count => x_msg_count,
1803 x_return_status => x_return_status);
1804 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1805 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1806 'Completed OKC_REP_QA_CHECK_PVT.validate_repository_for_qa with returned status: ' || x_return_status);
1807 END IF;
1808 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1809 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1810 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1811 RAISE OKC_API.G_EXCEPTION_ERROR;
1812 END IF;
1813
1814 -- Make call for deliverables QA check
1815
1816 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1817 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1818 'Calling OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa() API');
1819 END IF;
1820 l_bus_doc_date_events_tbl(1).event_code := G_CONTRACT_EFFECTIVE_EVENT;
1821 l_bus_doc_date_events_tbl(1).event_date := contract_rec.contract_effective_date;
1822
1823 l_bus_doc_date_events_tbl(2).event_code := G_CONTRACT_EXPIRE_EVENT;
1824 l_bus_doc_date_events_tbl(2).event_date := contract_rec.contract_expiration_date;
1825
1826 OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa (
1827 p_api_version => 1,
1828 p_init_msg_list => FND_API.G_FALSE,
1829 p_doc_type => contract_rec.contract_type,
1830 p_doc_id => p_contract_id,
1831 p_mode => G_NORMAL_QA,
1832 p_bus_doc_date_events_tbl => l_bus_doc_date_events_tbl,
1833 p_qa_result_tbl => l_qa_result_tbl,
1834 x_msg_data => x_msg_data,
1835 x_msg_count => x_msg_count,
1836 x_return_status => x_return_status,
1837 x_qa_return_status => x_qa_return_status);
1838
1839 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1840 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
1841 'Completed OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa with returned status: ' || x_return_status);
1842 END IF;
1843 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1844 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1845 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1846 RAISE OKC_API.G_EXCEPTION_ERROR;
1847 END IF;
1848
1849 --------------------------------------------
1850 -- VALIDATIONS are done for Repository and Deliverables.
1851 -- Now insert into Temp table.
1852 --------------------------------------------
1853 -- Save result from PLSQL table into DB table
1854 --------------------------------------------
1855 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1856 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
1857 G_MODULE||l_api_name,'1015: Save result from PLSQL table into DB table');
1858 END IF;
1859
1860
1861 -- After calling the validation APIs we need to find out about the x_qa_return_status. We should loop through
1862 -- this only if we get
1863 IF l_qa_result_tbl.COUNT > 0 THEN
1864 FOR i IN l_qa_result_tbl.FIRST..l_qa_result_tbl.LAST LOOP
1865 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1866 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1867 'Updating pl/sql table record: ' || i);
1868 END IF;
1869 l_qa_result_tbl(i).error_record_type_name := okc_util.decode_lookup('OKC_ERROR_RECORD_TYPE',l_qa_result_tbl(i).error_record_type);
1870 l_qa_result_tbl(i).error_severity_name := okc_util.decode_lookup('OKC_QA_SEVERITY',l_qa_result_tbl(i).error_severity);
1871 l_qa_result_tbl(i).document_type := contract_rec.contract_type;
1872 l_qa_result_tbl(i).document_id := p_contract_id;
1873 l_qa_result_tbl(i).creation_date := sysdate;
1874 IF l_qa_result_tbl(i).error_severity = G_QA_STS_ERROR THEN
1875 l_error_found := true;
1876 END IF;
1877 IF l_qa_result_tbl(i).error_severity = G_QA_STS_WARNING THEN
1878 l_warning_found := true;
1879 END IF;
1880
1881 END LOOP;
1882 IF l_error_found THEN
1883 x_qa_return_status := G_QA_STS_ERROR;
1884 ELSIF l_warning_found THEN
1885 x_qa_return_status := G_QA_STS_WARNING;
1886 END IF;
1887 END IF; -- l_qa_result_tbl.COUNT > 0 THEN
1888
1889 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1890 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1891 'Calling OKC_TERMS_QA_PVT.Log_QA_Messages');
1892 END IF;
1893 -- Load eror in the DB table
1894 OKC_TERMS_QA_PVT.Log_QA_Messages(
1895 x_return_status => x_return_status,
1896
1897 p_qa_result_tbl => l_qa_result_tbl,
1898 x_sequence_id => x_sequence_id
1899 );
1900 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1901 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1902 'Completed OKC_TERMS_QA_PVT.Log_QA_Messages');
1903 END IF;
1904 --------------------------------------------
1905 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1907 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1908 RAISE FND_API.G_EXC_ERROR ;
1909 END IF;
1910 --------------------------------------------
1911
1912 -- We should commit work now
1913 COMMIT WORK;
1914
1915 CLOSE contract_csr;
1916
1917 -- Standard call to get message count and if count is 1, get message info.
1918 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1919
1920 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1921 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
1922 G_MODULE||l_api_name,'Leaving OKC_REP_QA_CHECK_PVT.perform_contract_qa_check');
1923 END IF;
1924
1925 EXCEPTION
1926 WHEN FND_API.G_EXC_ERROR THEN
1927
1928 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1929 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1930 G_MODULE||l_api_name,'Leaving OKC_REP_QA_CHECK_PVT.perform_contract_qa_check with G_EXC_ERROR');
1931 END IF;
1932 --close cursors
1933 IF (contract_csr%ISOPEN) THEN
1934 CLOSE contract_csr ;
1935 END IF;
1936 x_return_status := G_RET_STS_ERROR;
1937 FND_MSG_PUB.Count_And_Get(
1938 p_count => x_msg_count,
1939 p_data => x_msg_data
1940 );
1941
1942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1943
1944 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1945 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1946 G_MODULE||l_api_name,'Leaving OKC_REP_QA_CHECK_PVT.perform_contract_qa_check with G_EXC_UNEXPECTED_ERROR');
1947 END IF;
1948 --close cursors
1949 IF (contract_csr%ISOPEN) THEN
1950 CLOSE contract_csr ;
1951 END IF;
1952 x_return_status := G_RET_STS_UNEXP_ERROR;
1953 FND_MSG_PUB.Count_And_Get(
1954 p_count => x_msg_count,
1955 p_data => x_msg_data
1956 );
1957
1958 WHEN OTHERS THEN
1959
1960 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1961 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1962 G_MODULE||l_api_name,'Leaving OKC_REP_QA_CHECK_PVT.perform_contract_qa_check with OTHERS EXCEPTION');
1963 END IF;
1964 --close cursors
1965 IF (contract_csr%ISOPEN) THEN
1966 CLOSE contract_csr ;
1967 END IF;
1968 x_return_status := G_RET_STS_UNEXP_ERROR;
1969 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1970 p_msg_name => G_UNEXPECTED_ERROR,
1971 p_token1 => G_SQLCODE_TOKEN,
1972 p_token1_value => sqlcode,
1973 p_token2 => G_SQLERRM_TOKEN,
1974 p_token2_value => sqlerrm);
1975 FND_MSG_PUB.Count_And_Get(
1976 p_count => x_msg_count,
1977 p_data => x_msg_data
1978 );
1979
1980 END perform_contract_qa_check;
1981
1982
1983 -- Start of comments
1984 --API name : insert_deliverables_qa_check_list
1985 --Type : Private.
1986 --Function : This API inserts QA check list of Deliverables for the specified
1987 -- Contract Type into the table OKC_DOC_QA_LISTS
1988 --Pre-reqs : None.
1989 --Parameters :
1990 --IN : p_api_version IN NUMBER Required
1991 -- : p_init_msg_list IN VARCHAR2 Required
1992 -- : p_contract_type IN VARCHAR2 Required
1993 -- Contract Type for which the QA checkes to be added
1994 --OUT : x_return_status OUT VARCHAR2(1)
1995 -- : x_msg_count OUT NUMBER
1996 -- : x_msg_data OUT VARCHAR2(2000)
1997 -- Note :
1998 -- End of comments
1999
2000 PROCEDURE insert_deliverables_qa_checks (
2001 p_api_version IN NUMBER,
2002 p_init_msg_list IN VARCHAR2,
2003 p_contract_type IN VARCHAR2,
2004 x_msg_count OUT NOCOPY NUMBER,
2005 x_msg_data OUT NOCOPY VARCHAR2,
2006 x_return_status OUT NOCOPY VARCHAR2)
2007 IS
2008
2009 l_api_version CONSTANT NUMBER := 1;
2010 l_api_name CONSTANT VARCHAR2(30) := 'insert_deliverables_qa_checks';
2011 l_okc_doc_qa_lists_tbl okc_doc_qa_lists_tbl_type;
2012 l_user_id FND_USER.USER_ID%TYPE;
2013
2014 BEGIN
2015
2016 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2017 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2018 'Entered insert_deliverables_qa_checks');
2019 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2020 'Contract Type is: ' || p_contract_type);
2021 END IF;
2022
2023 x_return_status := G_RET_STS_SUCCESS;
2024
2025 -- Initialize the Deliverables QA Check table of records
2026 l_okc_doc_qa_lists_tbl(1).qa_code := 'CHECK_AMENDMENT';
2027 l_okc_doc_qa_lists_tbl(1).severity_flag := 'W';
2028 l_okc_doc_qa_lists_tbl(1).enable_qa_yn := 'N';
2029
2030 l_okc_doc_qa_lists_tbl(2).qa_code := 'CHECK_NOTIFICATIONS';
2031 l_okc_doc_qa_lists_tbl(2).severity_flag := 'W';
2032 l_okc_doc_qa_lists_tbl(2).enable_qa_yn := 'Y';
2033
2034 l_okc_doc_qa_lists_tbl(3).qa_code := 'CHECK_BUYER_CONTACT';
2035 l_okc_doc_qa_lists_tbl(3).severity_flag := 'E';
2036 l_okc_doc_qa_lists_tbl(3).enable_qa_yn := 'Y';
2037
2038 l_okc_doc_qa_lists_tbl(4).qa_code := 'CHECK_SUPPLIER_CONTACT';
2039 l_okc_doc_qa_lists_tbl(4).severity_flag := 'E';
2040 l_okc_doc_qa_lists_tbl(4).enable_qa_yn := 'Y';
2041
2042 l_okc_doc_qa_lists_tbl(5).qa_code := 'CHECK_DUE_DATES';
2043 l_okc_doc_qa_lists_tbl(5).severity_flag := 'E';
2044 l_okc_doc_qa_lists_tbl(5).enable_qa_yn := 'Y';
2045
2046 l_okc_doc_qa_lists_tbl(6).qa_code := 'CHECK_DELIVERABLES_VAR_USAGE';
2047 l_okc_doc_qa_lists_tbl(6).severity_flag := 'W';
2048 l_okc_doc_qa_lists_tbl(6).enable_qa_yn := 'N';
2049
2050 l_okc_doc_qa_lists_tbl(7).qa_code := 'CHECK_INTERNAL_CONTACT_VALID';
2051 l_okc_doc_qa_lists_tbl(7).severity_flag := 'W';
2052 l_okc_doc_qa_lists_tbl(7).enable_qa_yn := 'Y';
2053
2054 l_okc_doc_qa_lists_tbl(8).qa_code := 'CHECK_EXTERNAL_PARTY_EXISTS';
2055 l_okc_doc_qa_lists_tbl(8).severity_flag := 'E';
2056 l_okc_doc_qa_lists_tbl(8).enable_qa_yn := 'Y';
2057
2058 l_user_id := FND_GLOBAL.user_id();
2059
2060 FOR i IN l_okc_doc_qa_lists_tbl.FIRST..l_okc_doc_qa_lists_tbl.LAST LOOP
2061
2062 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2063 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2064 'Inserting pl/sql table record: ' || i);
2065 END IF;
2066
2067 insert into OKC_DOC_QA_LISTS(
2068 QA_CODE,
2069 DOCUMENT_TYPE,
2070 SEVERITY_FLAG,
2071 OBJECT_VERSION_NUMBER,
2072 CREATED_BY,
2073 CREATION_DATE,
2074 LAST_UPDATED_BY,
2075 LAST_UPDATE_DATE,
2076 ENABLE_QA_YN)
2077 values(
2078 l_okc_doc_qa_lists_tbl(i).qa_code,
2079 p_contract_type,
2080 l_okc_doc_qa_lists_tbl(i).severity_flag,
2081 1,
2082 l_user_id,
2083 sysdate,
2084 l_user_id,
2085 sysdate,
2086 l_okc_doc_qa_lists_tbl(i).enable_qa_yn);
2087
2088 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2089 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
2090 'After inserting a row into OKC_DOC_QA_LISTS');
2091 END IF;
2092
2093 END LOOP;
2094
2095 -- Standard call to get message count and if count is 1, get message info.
2096 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2097
2098 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2099 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
2100 'Leaving OKC_REP_QA_CHECK_PVT.insert_deliverables_qa_checks');
2101 END IF;
2102
2103 EXCEPTION
2104 WHEN FND_API.G_EXC_ERROR THEN
2105
2106 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2107 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
2108 'Leaving OKC_REP_QA_CHECK_PVT.insert_deliverables_qa_checks with G_EXC_ERROR');
2109 END IF;
2110
2111 x_return_status := G_RET_STS_ERROR;
2112
2113 FND_MSG_PUB.Count_And_Get(
2114 p_count => x_msg_count,
2115 p_data => x_msg_data
2116 );
2117
2118 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2119
2120 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2121 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
2122 'Leaving OKC_REP_QA_CHECK_PVT.insert_deliverables_qa_checks with G_EXC_UNEXPECTED_ERROR');
2123 END IF;
2124
2125 x_return_status := G_RET_STS_UNEXP_ERROR;
2126
2127 FND_MSG_PUB.Count_And_Get(
2128 p_count => x_msg_count,
2129 p_data => x_msg_data
2130 );
2131
2132 WHEN OTHERS THEN
2133
2134 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2135 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
2136 'Leaving OKC_REP_QA_CHECK_PVT.insert_deliverables_qa_checks with OTHERS EXCEPTION');
2137 END IF;
2138
2139 x_return_status := G_RET_STS_UNEXP_ERROR;
2140
2141 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2142 p_msg_name => G_UNEXPECTED_ERROR,
2143 p_token1 => G_SQLCODE_TOKEN,
2144 p_token1_value => sqlcode,
2145 p_token2 => G_SQLERRM_TOKEN,
2146 p_token2_value => sqlerrm);
2147
2148 FND_MSG_PUB.Count_And_Get(
2149 p_count => x_msg_count,
2150 p_data => x_msg_data
2151 );
2152
2153 END insert_deliverables_qa_checks;
2154
2155 END OKC_REP_QA_CHECK_PVT;