[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.12.12020000.16 2013/05/08 08:38:41 skavutha 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 l_OKC_REP_NO_EXT_PARTY VARCHAR2(2000) ;
51 l_OKC_REP_NO_EXT_PARTY_S VARCHAR2(2000) ;
52
53 l_resolved_msg_name VARCHAR2(30);
54 l_resolved_token VARCHAR2(100);
55
56 l_doc_type varchar2(60);
57
58 CURSOR c_get_doctype is
59 SELECT CONTRACT_TYPE
60 FROM okc_rep_contracts_all
61 WHERE CONTRACT_id = p_contract_id;
62
63 BEGIN
64 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
65 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
66 'Entered OKC_REP_QA_CHECK_PVT.check_no_external_party ');
67 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
68 'Contract Id is: ' || p_contract_id);
69 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
70 'Severity level is: ' || p_severity);
71 END IF;
72 -- Standard call to check for call compatibility.
73 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75 END IF;
76 -- Initialize message list if p_init_msg_list is set to TRUE.
77 IF FND_API.to_Boolean( p_init_msg_list ) THEN
78 FND_MSG_PUB.initialize;
79 END IF;
80
81 -- Initialize API return status to success
82 x_return_status := FND_API.G_RET_STS_SUCCESS;
83
84 open c_get_doctype;
85 fetch c_get_doctype into l_doc_type;
86 close c_get_doctype;
87
88 l_resolved_token := OKC_API.resolve_hdr_token(l_doc_type);
89 OPEN party_csr;
90 FETCH party_csr INTO party_rec;
91 IF (l_doc_type not in ('REP_SBCR', 'REP_ACQ', 'REP_CCT') )THEN
92
93 IF party_csr%NOTFOUND THEN
94 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
95 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
96 'External Party is not found');
97 END IF;
98 -- Set the Qa Table index.
99 l_resolved_msg_name := OKC_API.resolve_message(G_OKC_REP_NO_EXT_PARTY,l_doc_type);
100
101
102
103 l_OKC_REP_NO_EXT_PARTY := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, l_resolved_msg_name, p_token1 => 'HDR_TOKEN',
104 p_token1_value => l_resolved_token) ;
105
106 l_resolved_msg_name := OKC_API.resolve_message(G_OKC_REP_NO_EXT_PARTY_S,l_doc_type);
107
108
109 l_OKC_REP_NO_EXT_PARTY_S := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, l_resolved_msg_name, p_token1 => 'HDR_TOKEN',
110 p_token1_value => l_resolved_token) ;
111
112
113 -- Set the Qa Table index.
114
115
116 l_index := px_qa_result_tbl.count + 1;
117 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
118 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_EXT_PARTY_T);
119 -- Need to verify the qa_code with Sanjay
120 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_NO_EXT_PARTY;
121 px_qa_result_tbl(l_index).message_name := G_OKC_REP_NO_EXT_PARTY;
122 px_qa_result_tbl(l_index).suggestion := l_OKC_REP_NO_EXT_PARTY_S;
123 px_qa_result_tbl(l_index).error_severity := p_severity;
124 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_EXT_PARTY_SD);
125 px_qa_result_tbl(l_index).problem_details := l_OKC_REP_NO_EXT_PARTY;
126
127 END IF;
128
129 END IF;
130
131 -- Standard call to get message count and if count is 1, get message info.
132 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
133
134 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
135 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
136 'Leaving OKC_REP_QA_CHECK_PVT.check_no_external_party');
137 END IF;
138 EXCEPTION
139 WHEN FND_API.G_EXC_ERROR THEN
140 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
141 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
142 'Leaving OKC_REP_QA_CHECK_PVT.check_no_external_party with G_EXC_ERROR');
143 END IF;
144 x_return_status := G_RET_STS_ERROR;
145 --close cursors
146 IF (party_csr%ISOPEN) THEN
147 CLOSE party_csr ;
148 END IF;
149 FND_MSG_PUB.Count_And_Get(
150 p_count => x_msg_count,
151 p_data => x_msg_data
152 );
153
154 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
155 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
156 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
157 'Leaving OKC_REP_QA_CHECK_PVT.check_no_external_party with G_EXC_UNEXPECTED_ERROR');
158 END IF;
159 x_return_status := G_RET_STS_UNEXP_ERROR;
160 --close cursors
161
162 IF (party_csr%ISOPEN) THEN
163 CLOSE party_csr ;
164 END IF;
165 FND_MSG_PUB.Count_And_Get(
166 p_count => x_msg_count,
167 p_data => x_msg_data
168 );
169
170 WHEN OTHERS THEN
171
172 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
173 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
174 G_MODULE || l_api_name,
175 'Leaving OKC_REP_QA_CHECK_PVT.check_no_external_party because of EXCEPTION: ' || sqlerrm);
176 END IF;
177 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
178 p_msg_name => G_UNEXPECTED_ERROR,
179 p_token1 => G_SQLCODE_TOKEN,
180 p_token1_value => sqlcode,
181 p_token2 => G_SQLERRM_TOKEN,
182 p_token2_value => sqlerrm);
183 --close cursors
184 IF (party_csr%ISOPEN) THEN
185 CLOSE party_csr ;
186 END IF;
187 x_return_status := G_RET_STS_UNEXP_ERROR;
188 FND_MSG_PUB.Count_And_Get(
189 p_count => x_msg_count,
190 p_data => x_msg_data
191 );
192 END check_no_external_party;
193
194
195
196 -- Start of comments
197 --API name : check_no_eff_date
198 --Type : Private.
199 --Function : This procedure checks for the presence of effective date in a given contract.
200 -- : and modifies px_qa_result_tbl table of records that contains validation
201 -- : errors and warnings
202 --Pre-reqs : None.
203 --Parameters :
204 --IN : p_api_version IN NUMBER Required
205 -- : p_init_msg_list IN VARCHAR2 Optional
206 -- Default = FND_API.G_FALSE
207 -- : p_effective_date IN NUMBER Required
208 -- Effective date of the contract to be checked
209 -- : p_severity IN VARCHAR2 Required
210 -- Severity level for this check. Possible values are ERROR, WARNING.
211 --INOUT : px_qa_result_tbl
212 -- The table of records that contains validation errors and warnings
213 --OUT : x_return_status OUT VARCHAR2(1)
214 -- : x_msg_count OUT NUMBER
215 -- : x_msg_data OUT VARCHAR2(2000)
216 --Note :
217 -- End of comments
218 PROCEDURE check_no_eff_date (
219 p_api_version IN NUMBER,
220 p_init_msg_list IN VARCHAR2,
221 p_effective_date IN DATE,
222 p_contract_id IN NUMBER DEFAULT NULL,
223 p_severity IN VARCHAR2,
224 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
225 x_msg_data OUT NOCOPY VARCHAR2,
226 x_msg_count OUT NOCOPY NUMBER,
227 x_return_status OUT NOCOPY VARCHAR2
228 ) IS
229
230 l_api_version CONSTANT NUMBER := 1;
231 l_api_name CONSTANT VARCHAR2(30) := 'check_no_eff_date';
232 l_index PLS_INTEGER := 0;
233
234 l_OKC_REP_NO_EFF_DATE VARCHAR2(2000) ;
235 l_OKC_REP_NO_EFF_DATE_S VARCHAR2(2000) ;
236
237 l_resolved_msg_name VARCHAR2(30);
238 l_resolved_token VARCHAR2(100);
239
240 l_doc_type varchar2(60);
241
242 CURSOR c_get_doctype is
243 SELECT CONTRACT_TYPE
244 FROM okc_rep_contracts_all
245 WHERE CONTRACT_id = p_contract_id;
246
247
248
249 BEGIN
250
251 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
252 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
253 'Entered OKC_REP_QA_CHECK_PVT.check_no_eff_date ');
254 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
255 'Effective Date is: ' || p_effective_date);
256 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
257 'Severity level is: ' || p_severity);
258 END IF;
259
260 -- Standard call to check for call compatibility.
261 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263 END IF;
264 -- Initialize message list if p_init_msg_list is set to TRUE.
265 IF FND_API.to_Boolean( p_init_msg_list ) THEN
266 FND_MSG_PUB.initialize;
267 END IF;
268
269 open c_get_doctype;
270 fetch c_get_doctype into l_doc_type;
271 close c_get_doctype;
272
273 l_resolved_token := OKC_API.resolve_hdr_token(l_doc_type);
274
275 -- Initialize API return status to success
276 x_return_status := FND_API.G_RET_STS_SUCCESS;
277
278 --Check for effective date
279 IF (p_effective_date IS NULL) THEN
280 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
281 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
282 'Effective Date is NULL');
283 END IF;
284 l_resolved_msg_name := OKC_API.resolve_message(G_OKC_REP_NO_EFF_DATE,l_doc_type);
285
286
287 l_OKC_REP_NO_EFF_DATE := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, l_resolved_msg_name, p_token1 => 'HDR_TOKEN',
288 p_token1_value => l_resolved_token) ;
289
290 l_resolved_msg_name := OKC_API.resolve_message(G_OKC_REP_NO_EFF_DATE_S,l_doc_type);
291
292
293 l_OKC_REP_NO_EFF_DATE_S := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, l_resolved_msg_name, p_token1 => 'HDR_TOKEN',
294 p_token1_value => l_resolved_token) ;
295
296 l_index := px_qa_result_tbl.count + 1;
297 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
298 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_EFF_DATE_T);
299 -- Need to verify the qa_code with Sanjay
300 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_NO_EFF_DATE;
301 px_qa_result_tbl(l_index).message_name := G_OKC_REP_NO_EFF_DATE;
302 px_qa_result_tbl(l_index).suggestion := l_OKC_REP_NO_EFF_DATE_S;
303 px_qa_result_tbl(l_index).error_severity := p_severity;
304 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_EFF_DATE_SD);
305 px_qa_result_tbl(l_index).problem_details := l_OKC_REP_NO_EFF_DATE;
306
307 END IF; -- p_effective_date IS NULL
308
309 -- Standard call to get message count and if count is 1, get message info.
310 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
311
312 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
313 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
314 'Leaving OKC_REP_QA_CHECK_PVT.check_no_eff_date');
315 END IF;
316 EXCEPTION
317 WHEN FND_API.G_EXC_ERROR THEN
318 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
319 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
320 'Leaving OKC_REP_QA_CHECK_PVT.check_no_eff_date with G_EXC_ERROR');
321 END IF;
322 x_return_status := G_RET_STS_ERROR;
323
324 FND_MSG_PUB.Count_And_Get(
325 p_count => x_msg_count,
326 p_data => x_msg_data
327 );
328
329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
330 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
331 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
332 'Leaving OKC_REP_QA_CHECK_PVT.check_no_eff_date with G_EXC_UNEXPECTED_ERROR');
333 END IF;
334
335 x_return_status := G_RET_STS_UNEXP_ERROR;
336 FND_MSG_PUB.Count_And_Get(
337 p_count => x_msg_count,
338 p_data => x_msg_data
339 );
340
341 WHEN OTHERS THEN
342
343 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
344 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
345 G_MODULE || l_api_name,
346 'Leaving OKC_REP_QA_CHECK_PVT.check_no_eff_date because of EXCEPTION: ' || sqlerrm);
347 END IF;
348 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
349 p_msg_name => G_UNEXPECTED_ERROR,
350 p_token1 => G_SQLCODE_TOKEN,
351 p_token1_value => sqlcode,
352 p_token2 => G_SQLERRM_TOKEN,
353 p_token2_value => sqlerrm);
354 x_return_status := G_RET_STS_UNEXP_ERROR;
355 FND_MSG_PUB.Count_And_Get(
356 p_count => x_msg_count,
357 p_data => x_msg_data
358 );
359 END check_no_eff_date;
360
361 -- Start of comments
362 --API name : check_expiry_check
363 --Type : Private.
364 --Function : This procedure checks if the document has expired already at the time of validating/submitting for approval
365 --Pre-reqs : None.
366 --Parameters :
367 --IN : p_api_version IN NUMBER Required
368 -- : p_init_msg_list IN VARCHAR2 Optional
369 -- Default = FND_API.G_FALSE
370 -- : p_expiration_date IN NUMBER Required
371 -- Expiration date of the contract to be checked
372 -- : p_severity IN VARCHAR2 Required
373 -- Severity level for this check. Possible values are ERROR, WARNING.
374 --INOUT : px_qa_result_tbl
375 -- The table of records that contains validation errors and warnings
376 --OUT : x_return_status OUT VARCHAR2(1)
377 -- : x_msg_count OUT NUMBER
378 -- : x_msg_data OUT VARCHAR2(2000)
379 --Note :
380 -- End of comments
381
382 -- End of comments
383
384
385 PROCEDURE check_expiry_check (
386 p_api_version IN NUMBER,
387 p_init_msg_list IN VARCHAR2,
388 p_expiration_date IN DATE,
389 p_contract_id IN NUMBER DEFAULT NULL,
390 p_severity IN VARCHAR2,
391 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
392 x_msg_data OUT NOCOPY VARCHAR2,
393 x_msg_count OUT NOCOPY NUMBER,
394 x_return_status OUT NOCOPY VARCHAR2
395 ) IS
396
397 l_api_version CONSTANT NUMBER := 1;
398 l_api_name CONSTANT VARCHAR2(30) := 'check_expiry_check';
399 l_index PLS_INTEGER := 0;
400
401 l_OKC_REP_EXPIRED VARCHAR2(2000) ;
402 l_OKC_REP_EXPIRED_SD VARCHAR2(2000) ;
403
404 l_resolved_msg_name VARCHAR2(30);
405 l_resolved_token VARCHAR2(100);
406
407 l_doc_type varchar2(60);
408
409 CURSOR c_get_doctype is
410 SELECT CONTRACT_TYPE
411 FROM okc_rep_contracts_all
412 WHERE CONTRACT_id = p_contract_id;
413
414
415
416 BEGIN
417
418 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
419 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
420 'Entered OKC_REP_QA_CHECK_PVT.check_expiry_check ');
421 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
422 'Expiration Date is: ' || p_expiration_date);
423 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
424 'Severity level is: ' || p_severity);
425 END IF;
426
427 -- Standard call to check for call compatibility.
428 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
430 END IF;
431 -- Initialize message list if p_init_msg_list is set to TRUE.
432 IF FND_API.to_Boolean( p_init_msg_list ) THEN
433 FND_MSG_PUB.initialize;
434 END IF;
435
436 open c_get_doctype;
437 fetch c_get_doctype into l_doc_type;
438 close c_get_doctype;
439
440 l_resolved_token := OKC_API.resolve_hdr_token(l_doc_type);
441
442 -- Initialize API return status to success
443 x_return_status := FND_API.G_RET_STS_SUCCESS;
444
445 --Check for effective date
446 IF (p_expiration_date <= SYSDATE ) THEN
447 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
448 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
449 'Contract expired');
450 END IF;
451
452 l_resolved_msg_name := OKC_API.resolve_message(G_OKC_REP_EXPIRED,l_doc_type);
453
454
455 l_OKC_REP_EXPIRED := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, l_resolved_msg_name, p_token1 => 'HDR_TOKEN',
456 p_token1_value => l_resolved_token) ;
457
458 l_resolved_msg_name := OKC_API.resolve_message(G_OKC_REP_EXPIRED_SD,l_doc_type);
459
460
461 l_OKC_REP_EXPIRED_SD := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, l_resolved_msg_name, p_token1 => 'HDR_TOKEN',
462 p_token1_value => l_resolved_token) ;
463
464 l_index := px_qa_result_tbl.count + 1;
465 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
466 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_EXPIRED_T);
467 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_EXPIRED;
468 px_qa_result_tbl(l_index).message_name := G_OKC_REP_EXPIRED;
469 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_EXPIRED_S);
470 px_qa_result_tbl(l_index).error_severity := p_severity;
471 px_qa_result_tbl(l_index).problem_short_desc := l_OKC_REP_EXPIRED_SD;
472 px_qa_result_tbl(l_index).problem_details := l_OKC_REP_EXPIRED;
473
474 END IF;
475
476 -- Standard call to get message count and if count is 1, get message info.
477 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
478
479 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
480 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
481 'Leaving OKC_REP_QA_CHECK_PVT.check_expiry_check');
482 END IF;
483 EXCEPTION
484 WHEN FND_API.G_EXC_ERROR THEN
485 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
486 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
487 'Leaving OKC_REP_QA_CHECK_PVT.check_expiry_check with G_EXC_ERROR');
488 END IF;
489 x_return_status := G_RET_STS_ERROR;
490
491 FND_MSG_PUB.Count_And_Get(
492 p_count => x_msg_count,
493 p_data => x_msg_data
494 );
495
496 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
497 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
498 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
499 'Leaving OKC_REP_QA_CHECK_PVT.check_expiry_check with G_EXC_UNEXPECTED_ERROR');
500 END IF;
501
502 x_return_status := G_RET_STS_UNEXP_ERROR;
503 FND_MSG_PUB.Count_And_Get(
504 p_count => x_msg_count,
505 p_data => x_msg_data
506 );
507
508 WHEN OTHERS THEN
509
510 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
511 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
512 G_MODULE || l_api_name,
513 'Leaving OKC_REP_QA_CHECK_PVT.check_expiry_check because of EXCEPTION: ' || sqlerrm);
514 END IF;
515 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
516 p_msg_name => G_UNEXPECTED_ERROR,
517 p_token1 => G_SQLCODE_TOKEN,
518 p_token1_value => sqlcode,
519 p_token2 => G_SQLERRM_TOKEN,
520 p_token2_value => sqlerrm);
521 x_return_status := G_RET_STS_UNEXP_ERROR;
522 FND_MSG_PUB.Count_And_Get(
523 p_count => x_msg_count,
524 p_data => x_msg_data
525 );
526 END check_expiry_check;
527
528 -- Start of comments
529 --API name : validate_E_Signature
530 --Type : Private.
531 --Function : This procedure checks for validity E-Signature
532 -- : and modifies px_qa_result_tbl table of records that contains validation
533 -- : errors and warnings
534 --Pre-reqs : None.
535 --Parameters :
536 --IN : p_api_version IN NUMBER Required
537 -- : p_init_msg_list IN VARCHAR2 Optional
538 -- Default = FND_API.G_FALSE
539 -- : p_contract_type IN NUMBER Required
540 -- Contract type to be validated
541 -- : p_severity IN VARCHAR2 Required
542 -- Severity level for this check. Possible values are ERROR, WARNING.
543 --INOUT : px_qa_result_tbl
544 -- The table of records that contains validation errors and warnings
545 --OUT : x_return_status OUT VARCHAR2(1)
546 -- : x_msg_count OUT NUMBER
547 -- : x_msg_data OUT VARCHAR2(2000)
548 --Note :
549 -- End of comments
550
551 PROCEDURE validate_E_Signature (
552 p_api_version IN NUMBER,
553 p_init_msg_list IN VARCHAR2,
554 p_contract_id IN NUMBER,
555 p_contract_type IN VARCHAR2,
556 p_severity IN VARCHAR2,
557 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
558 x_msg_data OUT NOCOPY VARCHAR2,
559 x_msg_count OUT NOCOPY NUMBER,
560 x_return_status OUT NOCOPY VARCHAR2
561 ) IS
562
563 l_api_version CONSTANT NUMBER := 1;
564 l_api_name CONSTANT VARCHAR2(30) := 'validate_E_Signature';
565 l_index PLS_INTEGER := 0;
566 l_valid_E_sign_flag VARCHAR2(1);
567 l_contract_type_name OKC_BUS_DOC_TYPES_TL.NAME%TYPE;
568
569 CURSOR contract_type_csr IS
570 SELECT signature_workflow_name
571 FROM okc_bus_doc_types_b
572 WHERE document_type = p_contract_type;
573
574 CURSOR contacts_csr IS
575 SELECT Count(*) contact_count FROM okc_rep_party_contacts WHERE contract_id=p_contract_id AND
576 ESIGNATURE_TYPE IS NOT NULL AND SIGNATURE_SEQUENCE IS NOT NULL ;
577
578 CURSOR contact_detail_csr IS
579 select contact_id,party_id,PARTY_ROLE_CODE,SIGNATURE_SEQUENCE,ESIGNATURE_TYPE
580 from
581 okc_rep_party_contacts opc WHERE contract_id=p_contract_id AND
582 ESIGNATURE_TYPE IS NOT NULL AND SIGNATURE_SEQUENCE IS NOT NULL ;
583
584
585 CURSOR cur_supplier_user(p_contact_id IN number) IS
586 SELECT f.user_name ,(pvc.first_name || ' ' || pvc.middle_name || ' ' || pvc.last_name) contact_name
587 FROM po_vendor_contacts pvc,FND_USER f
588 where pvc.PER_PARTY_ID= f.person_party_id
589 AND pvc.vendor_contact_id=p_contact_id;
590
591 CURSOR cur_cust_user(p_contact_id IN NUMBER,p_object_id IN number) IS
592 SELECT f.user_name ,( hz.person_first_name||' '||hz.person_last_name ) contact_name FROM
593 hz_relationships hr,
594 hz_parties hz,
595 FND_USER f
596 WHERE
597 hr.party_id = p_contact_id
598 AND hr.subject_id = hz.party_id
599 AND hr.object_id = p_object_id
600 AND hr.object_type = 'ORGANIZATION'
601 AND hr.object_table_name = 'HZ_PARTIES'
602 AND hr.subject_type = 'PERSON'
603 AND hz.party_id = hr.subject_id
604 AND hr.relationship_code = 'CONTACT_OF'
605 AND hr.status = 'A'
606 AND hr.start_date <= sysdate
607 AND nvl(hr.end_date, sysdate + 1) > SYSDATE
608 AND f.person_party_id(+) = hz.party_id ;
609
610
611 CURSOR cur_int_user(p_contact_id IN NUMBER) is
612 SELECT f.user_name,p.full_name contact_name from
613 per_workforce_v p,
614 fnd_user f
615 WHERE f.employee_id(+)=p.person_id and
616 p.person_id=p_contact_id;
617
618 l_user_name VARCHAR2(40);
619 l_contact_name varchar2(500);
620 contract_type_rec contract_type_csr%ROWTYPE;
621 l_contact_cnt NUMBER;
622
623 BEGIN
624
625 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
626 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
627 'Entered OKC_REP_QA_CHECK_PVT.validate_E_Signature ');
628 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
629 'Contract type is: ' || p_contract_type);
630 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
631 'Severity level is: ' || p_severity);
632 END IF;
633
634 -- Standard call to check for call compatibility.
635 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
637 END IF;
638 -- Initialize message list if p_init_msg_list is set to TRUE.
639 IF FND_API.to_Boolean( p_init_msg_list ) THEN
640 FND_MSG_PUB.initialize;
641 END IF;
642
643 -- Initialize API return status to success
644 x_return_status := FND_API.G_RET_STS_SUCCESS;
645 l_valid_E_sign_flag := 'Y';
646
647 OPEN contacts_csr;
648 FETCH contacts_csr INTO l_contact_cnt;
649 CLOSE contacts_csr;
650
651 OPEN contract_type_csr;
652 FETCH contract_type_csr INTO contract_type_rec;
653 IF contract_type_csr%NOTFOUND THEN
654 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
655 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
656 G_MODULE||l_api_name, 'Contract Type: '|| p_contract_type || ' does not exist');
657 END IF;
658 RAISE FND_API.G_EXC_ERROR;
659 END IF; -- contract_type_csr%NOTFOUND
660
661 IF contract_type_rec.signature_workflow_name IS NULL THEN
662
663 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
665 'Contract E Signature validation failed');
666 END IF;
667 l_index := px_qa_result_tbl.count + 1;
668 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
669 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_E_SIGN_T);
670 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_E_SIGN;
671 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_E_SIGN;
672 -- Bug 4702590. Removed the suggetion message
673 --px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_CONTRACT_TYPE_S);
674 px_qa_result_tbl(l_index).error_severity := p_severity;
675 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_E_SIGN_SD);
676 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
677 p_app_name => G_OKC,
678 p_msg_name => G_OKC_REP_INV_E_SIGN_T);
679
680
681 END IF;
682
683 IF Nvl(l_contact_cnt,0) = 0 THEN
684
685 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
686 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
687 'Contract E Signature validation failed ');
688 END IF;
689
690 l_index := px_qa_result_tbl.count + 1;
691 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
692 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_E_SIGN_T1);
693 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_E_SIGN;
694 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_E_SIGN;
695 -- Bug 4702590. Removed the suggetion message
696 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_E_SIGN_S);
697 px_qa_result_tbl(l_index).error_severity := p_severity;
698 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_E_SIGN_SD);
699 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
700 p_app_name => G_OKC,
701 p_msg_name => G_OKC_REP_INV_E_SIGN_T1);
702
703 END IF;
704
705
706 CLOSE contract_type_csr;
707
708
709 FOR i IN contact_detail_csr LOOP
710
711 IF i.party_role_code='SUPPLIER_ORG' THEN
712 --
713 OPEN cur_supplier_user(i.contact_id) ;
714 FETCH cur_supplier_user INTO l_user_name,l_contact_name ;
715 CLOSE cur_supplier_user;
716
717 ELSIF i.party_role_code = 'CUSTOMER_ORG' OR i.party_role_code = 'PARTNER_ORG' THEN
718
719 OPEN cur_cust_user(i.contact_id,i.party_id) ;
720 FETCH cur_cust_user INTO l_user_name,l_contact_name;
721 CLOSE cur_cust_user;
722
723 ELSIF i.party_role_code='INTERNAL_ORG' THEN
724
725 OPEN cur_int_user(i.contact_id) ;
726 FETCH cur_int_user INTO l_user_name,l_contact_name;
727 CLOSE cur_int_user;
728
729 END IF;
730
731 IF l_user_name IS NULL THEN
732 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
733 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
734 'Contract E Signature validation failed at step3 ');
735 END IF;
736
737 l_index := px_qa_result_tbl.count + 1;
738 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
739 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_REP_INV_E_SIGN_CONTACT_T);
740 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_E_SIGN;
741 px_qa_result_tbl(l_index).message_name := G_REP_INV_E_SIGN_CONTACT_T1;
742 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(p_app_name => G_OKC,
743 p_msg_name => G_REP_INV_E_SIGN_CONTACT_S,
744 p_token1 => 'P_TOKEN',
745 p_token1_value => I.SIGNATURE_SEQUENCE);
746 px_qa_result_tbl(l_index).error_severity := p_severity;
747 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_REP_INV_E_SIGN_CONTACT_SD);
748 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(p_app_name => G_OKC,
749 p_msg_name => G_REP_INV_E_SIGN_CONTACT_T1,
750 p_token1 => 'P_TOKEN',
751 p_token1_value => I.SIGNATURE_SEQUENCE);
752
753
754
755 END IF;
756 END loop;
757
758
759
760 -- Standard call to get message count and if count is 1, get message info.
761 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
762
763 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
764 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
765 'Leaving OKC_REP_QA_CHECK_PVT.validate_contract_type');
766 END IF;
767 EXCEPTION
768 WHEN FND_API.G_EXC_ERROR THEN
769 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
770 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
771 'Leaving OKC_REP_QA_CHECK_PVT.validate_E_Signature with G_EXC_ERROR');
772 END IF;
773 IF (contract_type_csr%ISOPEN) THEN
774 CLOSE contract_type_csr ;
775 END IF;
776 x_return_status := G_RET_STS_ERROR;
777 FND_MSG_PUB.Count_And_Get(
778 p_count => x_msg_count,
779 p_data => x_msg_data
780 );
781
782 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
783 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
784 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
785 'Leaving OKC_REP_QA_CHECK_PVT.validate_E_Signature with G_EXC_UNEXPECTED_ERROR');
786 END IF;
787 IF (contract_type_csr%ISOPEN) THEN
788 CLOSE contract_type_csr ;
789 END IF;
790 x_return_status := G_RET_STS_UNEXP_ERROR;
791 FND_MSG_PUB.Count_And_Get(
792 p_count => x_msg_count,
793 p_data => x_msg_data
794 );
795
796 WHEN OTHERS THEN
797 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
798 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
799 G_MODULE || l_api_name,
800 'Leaving OKC_REP_QA_CHECK_PVT.validate_E_Signature because of EXCEPTION: ' || sqlerrm);
801 END IF;
802 IF (contract_type_csr%ISOPEN) THEN
803 CLOSE contract_type_csr ;
804 END IF;
805 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
806 p_msg_name => G_UNEXPECTED_ERROR,
807 p_token1 => G_SQLCODE_TOKEN,
808 p_token1_value => sqlcode,
809 p_token2 => G_SQLERRM_TOKEN,
810 p_token2_value => sqlerrm);
811 x_return_status := G_RET_STS_UNEXP_ERROR;
812 FND_MSG_PUB.Count_And_Get(
813 p_count => x_msg_count,
814 p_data => x_msg_data
815 );
816 END validate_E_Signature;
817
818
819
820 -- Start of comments
821 --API name : validate_contract_type
822 --Type : Private.
823 --Function : This procedure checks for validity of the contract type being passed as an input param
824 -- : and modifies px_qa_result_tbl table of records that contains validation
825 -- : errors and warnings
826 --Pre-reqs : None.
827 --Parameters :
828 --IN : p_api_version IN NUMBER Required
829 -- : p_init_msg_list IN VARCHAR2 Optional
830 -- Default = FND_API.G_FALSE
831 -- : p_contract_type IN NUMBER Required
832 -- Contract type to be validated
833 -- : p_severity IN VARCHAR2 Required
834 -- Severity level for this check. Possible values are ERROR, WARNING.
835 --INOUT : px_qa_result_tbl
836 -- The table of records that contains validation errors and warnings
837 --OUT : x_return_status OUT VARCHAR2(1)
838 -- : x_msg_count OUT NUMBER
839 -- : x_msg_data OUT VARCHAR2(2000)
840 --Note :
841 -- End of comments
842 PROCEDURE validate_contract_type (
843 p_api_version IN NUMBER,
844 p_init_msg_list IN VARCHAR2,
845 p_contract_type IN VARCHAR2,
846 p_severity IN VARCHAR2,
847 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
848 x_msg_data OUT NOCOPY VARCHAR2,
849 x_msg_count OUT NOCOPY NUMBER,
850 x_return_status OUT NOCOPY VARCHAR2
851 ) IS
852
853 l_api_version CONSTANT NUMBER := 1;
854 l_api_name CONSTANT VARCHAR2(30) := 'validate_contract_type';
855 l_index PLS_INTEGER := 0;
856 l_valid_contract_type_flag VARCHAR2(1);
857 l_contract_type_name OKC_BUS_DOC_TYPES_TL.NAME%TYPE;
858
859 CURSOR contract_type_csr IS
860 SELECT name, start_date, end_date
861 FROM okc_bus_doc_types_vl
862 WHERE document_type = p_contract_type;
863 contract_type_rec contract_type_csr%ROWTYPE;
864 BEGIN
865
866 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
867 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
868 'Entered OKC_REP_QA_CHECK_PVT.validate_contract_type ');
869 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
870 'Contract type is: ' || p_contract_type);
871 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
872 'Severity level is: ' || p_severity);
873 END IF;
874
875 -- Standard call to check for call compatibility.
876 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
878 END IF;
879 -- Initialize message list if p_init_msg_list is set to TRUE.
880 IF FND_API.to_Boolean( p_init_msg_list ) THEN
881 FND_MSG_PUB.initialize;
882 END IF;
883
884 -- Initialize API return status to success
885 x_return_status := FND_API.G_RET_STS_SUCCESS;
886 l_valid_contract_type_flag := 'Y';
887
888 OPEN contract_type_csr;
889 FETCH contract_type_csr INTO contract_type_rec;
890 IF contract_type_csr%NOTFOUND THEN
891 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
892 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
893 G_MODULE||l_api_name, 'Contract Type: '|| p_contract_type || ' does not exist');
894 END IF;
895 RAISE FND_API.G_EXC_ERROR;
896 END IF; -- contract_type_csr%NOTFOUND
897 l_contract_type_name := contract_type_rec.name;
898 IF (sysdate BETWEEN nvl(contract_type_rec.start_date, sysdate) AND nvl(contract_type_rec.end_date, sysdate)) THEN
899 l_valid_contract_type_flag := 'Y';
900 ELSE
901 l_valid_contract_type_flag := 'N';
902 END IF;
903 CLOSE contract_type_csr;
904
905 --Check for contract type
906 IF (l_valid_contract_type_flag = 'N') THEN
907 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
908 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
909 'Contract Type is invalid');
910 END IF;
911 l_index := px_qa_result_tbl.count + 1;
912 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
913 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_CONTRACT_TYPE_T);
914 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_CONTRACT_TYPE;
915 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_CONTRACT_TYPE;
916 -- Bug 4702590. Removed the suggetion message
917 --px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_CONTRACT_TYPE_S);
918 px_qa_result_tbl(l_index).error_severity := p_severity;
919 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_CONTRACT_TYPE_SD);
920 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
921 p_app_name => G_OKC,
922 p_msg_name => G_OKC_REP_INV_CONTRACT_TYPE,
923 p_token1 => G_CONTRACT_TYPE_TOKEN,
924 p_token1_value => l_contract_type_name);
925 END IF; -- l_valid_contract_type_flag = 'N'
926 -- Standard call to get message count and if count is 1, get message info.
927 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
928
929 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
930 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
931 'Leaving OKC_REP_QA_CHECK_PVT.validate_contract_type');
932 END IF;
933 EXCEPTION
934 WHEN FND_API.G_EXC_ERROR THEN
935 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
936 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
937 'Leaving OKC_REP_QA_CHECK_PVT.validate_contract_type with G_EXC_ERROR');
938 END IF;
939 IF (contract_type_csr%ISOPEN) THEN
940 CLOSE contract_type_csr ;
941 END IF;
942 x_return_status := G_RET_STS_ERROR;
943 FND_MSG_PUB.Count_And_Get(
944 p_count => x_msg_count,
945 p_data => x_msg_data
946 );
947
948 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
949 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
950 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
951 'Leaving OKC_REP_QA_CHECK_PVT.validate_contract_type with G_EXC_UNEXPECTED_ERROR');
952 END IF;
953 IF (contract_type_csr%ISOPEN) THEN
954 CLOSE contract_type_csr ;
955 END IF;
956 x_return_status := G_RET_STS_UNEXP_ERROR;
957 FND_MSG_PUB.Count_And_Get(
958 p_count => x_msg_count,
959 p_data => x_msg_data
960 );
961
962 WHEN OTHERS THEN
963 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
964 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
965 G_MODULE || l_api_name,
966 'Leaving OKC_REP_QA_CHECK_PVT.validate_contract_type because of EXCEPTION: ' || sqlerrm);
967 END IF;
968 IF (contract_type_csr%ISOPEN) THEN
969 CLOSE contract_type_csr ;
970 END IF;
971 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
972 p_msg_name => G_UNEXPECTED_ERROR,
973 p_token1 => G_SQLCODE_TOKEN,
974 p_token1_value => sqlcode,
975 p_token2 => G_SQLERRM_TOKEN,
976 p_token2_value => sqlerrm);
977 x_return_status := G_RET_STS_UNEXP_ERROR;
978 FND_MSG_PUB.Count_And_Get(
979 p_count => x_msg_count,
980 p_data => x_msg_data
981 );
982 END validate_contract_type;
983
984
985 -- Start of comments
986 --API name : validate_external_party
987 --Type : Private.
988 --Function : This procedure checks for validity of the external party
989 -- : and modifies px_qa_result_tbl table of records that contains validation
990 -- : errors and warnings
991 --Pre-reqs : None.
992 --Parameters :
993 --IN : p_api_version IN NUMBER Required
994 -- : p_init_msg_list IN VARCHAR2 Optional
995 -- Default = FND_API.G_FALSE
996 -- : p_party_role_code IN VARCHER Required
997 -- external party role code
998 -- : p_party_id IN NUMBER Required
999 -- party id of the external party.
1000 -- : p_severity IN VARCHAR2 Required
1001 -- Severity level for this check. Possible values are ERROR, WARNING.
1002 --INOUT : px_qa_result_tbl
1003 -- The table of records that contains validation errors and warnings
1004 --OUT : x_return_status OUT VARCHAR2(1)
1005 -- : x_msg_count OUT NUMBER
1006 -- : x_msg_data OUT VARCHAR2(2000)
1007 --Note :
1008 -- End of comments
1009 PROCEDURE validate_external_party (
1010 p_api_version IN NUMBER,
1011 p_init_msg_list IN VARCHAR2,
1012 p_party_role_code IN VARCHAR2,
1013 p_party_id IN NUMBER,
1014 p_severity IN VARCHAR2,
1015 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
1016 x_msg_data OUT NOCOPY VARCHAR2,
1017 x_msg_count OUT NOCOPY NUMBER,
1018 x_return_status OUT NOCOPY VARCHAR2
1019 ) IS
1020
1021 l_api_version CONSTANT NUMBER := 1;
1022 l_api_name CONSTANT VARCHAR2(30) := 'validate_external_party';
1023 l_index PLS_INTEGER := 0;
1024 l_valid_external_party_flag VARCHAR2(1);
1025 l_party_name VARCHAR2(450); -- set to 360 in OKC_REP_IMP_PARTIES_T
1026 -- For HZ_PARTY validation, using document "TCA Usage Guideline" Version 3.0
1027 CURSOR partner_csr IS
1028 SELECT party_name, status
1029 FROM hz_parties
1030 WHERE party_id = p_party_id
1031 AND party_type IN ('ORGANIZATION', 'PERSON'); /*--10334886: Added person party Type*/
1032 partner_rec partner_csr%ROWTYPE;
1033
1034 CURSOR vendor_csr IS --enabled flag should be Y for active vendors
1035 SELECT vendor_name, enabled_flag,
1036 start_date_active,
1037 end_date_active
1038 FROM po_vendors
1039 WHERE vendor_id = p_party_id;
1040 vendor_rec vendor_csr%ROWTYPE;
1041
1042 BEGIN
1043 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1044 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1045 'Entered OKC_REP_QA_CHECK_PVT.validate_external_party ');
1046 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1047 'Party Role Code is: ' || p_party_role_code);
1048 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1049 'Party Id is: ' || p_party_id);
1050 END IF;
1051
1052 -- Standard call to check for call compatibility.
1053 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1055 END IF;
1056 -- Initialize message list if p_init_msg_list is set to TRUE.
1057 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1058 FND_MSG_PUB.initialize;
1059 END IF;
1060
1061 -- Initialize API return status to success
1062 x_return_status := FND_API.G_RET_STS_SUCCESS;
1063 l_valid_external_party_flag := 'Y';
1064
1065 IF (p_party_role_code = G_PARTY_ROLE_PARTNER OR
1066 p_party_role_code = G_PARTY_ROLE_CUSTOMER) THEN
1067 OPEN partner_csr;
1068 FETCH partner_csr INTO partner_rec;
1069 IF partner_csr%NOTFOUND THEN
1070 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1071 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1072 G_MODULE||l_api_name, 'Party with Party Id: '|| p_party_id || ' does not exist');
1073 END IF;
1074 RAISE FND_API.G_EXC_ERROR;
1075 END IF; -- partner_csr%NOTFOUND
1076 l_party_name := partner_rec.party_name;
1077 if (partner_rec.status <> 'A') THEN
1078 l_valid_external_party_flag := 'N';
1079 END IF; -- partner_rec.status <> 'A'
1080 CLOSE partner_csr;
1081 ELSE
1082 OPEN vendor_csr;
1083 FETCH vendor_csr INTO vendor_rec;
1084 IF vendor_csr%NOTFOUND THEN
1085 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1086 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1087 G_MODULE||l_api_name, 'Vendor with vendor Id: '|| p_party_id || ' does not exist');
1088 END IF;
1089 RAISE FND_API.G_EXC_ERROR;
1090 END IF; -- vendor_csr%NOTFOUND
1091 l_party_name := vendor_rec.vendor_name;
1092 if (vendor_rec.enabled_flag <> 'Y' AND
1093 SYSDATE BETWEEN NVL(vendor_rec.start_date_active, SYSDATE - 1) AND NVL(vendor_rec.end_date_active, SYSDATE + 1)) THEN
1094 l_valid_external_party_flag := 'N';
1095 END IF;
1096 CLOSE vendor_csr;
1097 END IF; -- p_party_role_code = G_PARTY_ROLE_PARTNER
1098
1099 --Check for external party
1100 IF (l_valid_external_party_flag = 'N') THEN
1101 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1102 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
1103 'External party is invalid: ' || l_party_name);
1104 END IF;
1105 l_index := px_qa_result_tbl.count + 1;
1106 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
1107 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_EXT_PARTY_T);
1108 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_EXT_PARTY;
1109 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_EXT_PARTY;
1110 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_EXT_PARTY_S);
1111 px_qa_result_tbl(l_index).error_severity := p_severity;
1112 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_EXT_PARTY_SD);
1113 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
1114 p_app_name => G_OKC,
1115 p_msg_name => G_OKC_REP_INV_EXT_PARTY,
1116 p_token1 => G_PARTY_NAME_TOKEN,
1117 p_token1_value => l_party_name);
1118 END IF; -- l_valid_external_party_flag = 'N'
1119
1120 -- Standard call to get message count and if count is 1, get message info.
1121 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1122
1123 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1124 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1125 'Leaving OKC_REP_QA_CHECK_PVT.validate_external_party');
1126 END IF;
1127 EXCEPTION
1128 WHEN FND_API.G_EXC_ERROR THEN
1129 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1130 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
1131 'Leaving OKC_REP_QA_CHECK_PVT.validate_external_party with G_EXC_ERROR');
1132 END IF;
1133 IF (partner_csr%ISOPEN) THEN
1134 CLOSE partner_csr ;
1135 END IF;
1136 IF (vendor_csr%ISOPEN) THEN
1137 CLOSE vendor_csr ;
1138 END IF;
1139 x_return_status := G_RET_STS_ERROR;
1140 FND_MSG_PUB.Count_And_Get(
1141 p_count => x_msg_count,
1142 p_data => x_msg_data
1143 );
1144
1145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1146 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1147 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1148 'Leaving OKC_REP_QA_CHECK_PVT.validate_external_party with G_EXC_UNEXPECTED_ERROR');
1149 END IF;
1150 IF (partner_csr%ISOPEN) THEN
1151 CLOSE partner_csr ;
1152 END IF;
1153 IF (vendor_csr%ISOPEN) THEN
1154 CLOSE vendor_csr ;
1155 END IF;
1156 x_return_status := G_RET_STS_UNEXP_ERROR;
1157 FND_MSG_PUB.Count_And_Get(
1158 p_count => x_msg_count,
1159 p_data => x_msg_data
1160 );
1161
1162 WHEN OTHERS THEN
1163 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1164 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1165 G_MODULE || l_api_name,
1166 'Leaving OKC_REP_QA_CHECK_PVT.validate_external_party because of EXCEPTION: ' || sqlerrm);
1167 END IF;
1168 IF (partner_csr%ISOPEN) THEN
1169 CLOSE partner_csr ;
1170 END IF;
1171 IF (vendor_csr%ISOPEN) THEN
1172 CLOSE vendor_csr ;
1173 END IF;
1174 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1175 p_msg_name => G_UNEXPECTED_ERROR,
1176 p_token1 => G_SQLCODE_TOKEN,
1177 p_token1_value => sqlcode,
1178 p_token2 => G_SQLERRM_TOKEN,
1179 p_token2_value => sqlerrm);
1180 x_return_status := G_RET_STS_UNEXP_ERROR;
1181 FND_MSG_PUB.Count_And_Get(
1182 p_count => x_msg_count,
1183 p_data => x_msg_data
1184 );
1185 END validate_external_party;
1186
1187
1188
1189
1190 -- Start of comments
1191 --API name : validate_contact
1192 --Type : Private.
1193 --Function : This procedure checks for validity of the party contact
1194 -- : and modifies px_qa_result_tbl table of records that contains validation
1195 -- : errors and warnings
1196 --Pre-reqs : None.
1197 --Parameters :
1198 --IN : p_api_version IN NUMBER Required
1199 -- : p_init_msg_list IN VARCHAR2 Optional
1200 -- Default = FND_API.G_FALSE
1201 -- : p_party_role_code IN VARCHER Required
1202 -- external party role code
1203 -- : p_party_id IN NUMBER Required
1204 -- party id of the external party.
1205 -- : p_contact_id IN NUMBER Required
1206 -- contact id of the party conatct.
1207 -- : p_severity IN VARCHAR2 Required
1208 -- Severity level for this check. Possible values are ERROR, WARNING.
1209 --INOUT : px_qa_result_tbl
1210 -- The table of records that contains validation errors and warnings
1211 --OUT : x_return_status OUT VARCHAR2(1)
1212 -- : x_msg_count OUT NUMBER
1213 -- : x_msg_data OUT VARCHAR2(2000)
1214 --Note :
1215 -- End of comments
1216 PROCEDURE validate_contact (
1217 p_api_version IN NUMBER,
1218 p_init_msg_list IN VARCHAR2,
1219 p_party_role_code IN VARCHAR2,
1220 p_party_id IN NUMBER,
1221 p_contact_id IN NUMBER,
1222 p_severity IN VARCHAR2,
1223 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
1224 x_msg_data OUT NOCOPY VARCHAR2,
1225 x_msg_count OUT NOCOPY NUMBER,
1226 x_return_status OUT NOCOPY VARCHAR2
1227 ) IS
1228
1229 l_api_version CONSTANT NUMBER := 1;
1230 l_api_name CONSTANT VARCHAR2(30) := 'validate_contact';
1231 l_index PLS_INTEGER := 0;
1232 l_valid_contact_flag VARCHAR2(1);
1233 l_contact_name VARCHAR2(450); -- set to 360 in OKC_REP_IMP_PARTIES_T
1234
1235 CURSOR tca_contact_csr IS
1236 SELECT hz.party_name contact_name,
1237 hr.status relationship_status,
1238 hr.start_date start_date,
1239 hr.end_date end_date
1240 FROM hz_parties hz,
1241 hz_relationships hr
1242 WHERE hr.object_id = p_party_id -- The party being passsed
1243 AND hz.party_id = p_contact_id -- The contact id
1244 AND hr.object_type = 'ORGANIZATION'
1245 AND hr.object_table_name = 'HZ_PARTIES'
1246 AND hr.subject_type = 'PERSON'
1247 AND ((hr.relationship_code = 'CONTACT_OF') OR (hr.relationship_code = 'EMPLOYEE_OF'))
1248 AND hz.party_id = hr.party_id;
1249
1250 tca_contact_rec tca_contact_csr%ROWTYPE;
1251
1252 CURSOR vendor_contact_csr IS
1253 SELECT (first_name || ' ' || middle_name || ' ' || last_name) contact_name,
1254 inactive_date
1255 FROM po_vendor_contacts
1256 WHERE vendor_contact_id=p_contact_id;
1257
1258 vendor_contact_rec vendor_contact_csr%ROWTYPE;
1259
1260 -- Bug 6598261.Changed per_all_workforce_v to per_workforce_v.
1261
1262 CURSOR employee_contact_csr IS
1263 SELECT full_name contact_name
1264 FROM per_workforce_v
1265 WHERE person_id = p_contact_id;
1266
1267 CURSOR employee_name_csr IS
1268 SELECT per.full_name contact_name
1269 FROM per_all_people_f per
1270 WHERE per.person_id = p_contact_id
1271 AND per.effective_start_date = (SELECT MAX(effective_start_date)
1272 FROM per_all_people_f
1273 WHERE person_id = per.person_id);
1274
1275
1276 employee_contact_rec employee_contact_csr%ROWTYPE;
1277
1278
1279 BEGIN
1280 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1281 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1282 'Entered OKC_REP_QA_CHECK_PVT.validate_contact ');
1283 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1284 'Party Role Code is: ' || p_party_role_code);
1285 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1286 'Party Id is: ' || p_party_id);
1287 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1288 'Contact Id is: ' || p_contact_id);
1289 END IF;
1290
1291 -- Standard call to check for call compatibility.
1292 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1294 END IF;
1295 -- Initialize message list if p_init_msg_list is set to TRUE.
1296 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1297 FND_MSG_PUB.initialize;
1298 END IF;
1299
1300 -- Initialize API return status to success
1301 x_return_status := FND_API.G_RET_STS_SUCCESS;
1302 l_valid_contact_flag := 'Y';
1303
1304 IF ((p_party_role_code = G_PARTY_ROLE_PARTNER) OR (p_party_role_code = G_PARTY_ROLE_CUSTOMER)) THEN
1305
1306 OPEN tca_contact_csr;
1307 FETCH tca_contact_csr INTO tca_contact_rec;
1308
1309 IF tca_contact_csr%NOTFOUND THEN
1310 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1311 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1312 G_MODULE||l_api_name, 'Contact with Contact Id: '|| p_contact_id || ' does not exist');
1313 END IF;
1314 RAISE FND_API.G_EXC_ERROR;
1315 END IF; -- partner_csr%NOTFOUND
1316
1317 l_contact_name := tca_contact_rec.contact_name;
1318
1319 IF ((tca_contact_rec.relationship_status = 'A') AND
1320 (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.
1321 l_valid_contact_flag := 'Y';
1322 ELSE
1323 l_valid_contact_flag := 'N';
1324 END IF; -- partner_rec.relationship_status = 'A'
1325
1326 CLOSE tca_contact_csr;
1327
1328 ELSIF (p_party_role_code = G_PARTY_ROLE_SUPPLIER) THEN -- Vendor Party Contact
1329
1330 OPEN vendor_contact_csr;
1331 FETCH vendor_contact_csr INTO vendor_contact_rec;
1332
1333 IF vendor_contact_csr%NOTFOUND THEN
1334 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1335 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1336 G_MODULE||l_api_name, 'Vendor contact Id: '|| p_contact_id || ' does not exist');
1337 END IF;
1338 RAISE FND_API.G_EXC_ERROR;
1339 END IF; -- vendor_contact_csr%NOTFOUND
1340
1341 l_contact_name := vendor_contact_rec.contact_name;
1342
1343 IF (vendor_contact_rec.inactive_date <= sysdate) THEN
1344 l_valid_contact_flag := 'N';
1345 END IF; -- vendor_contact_rec.status <> 'A'
1346
1347 CLOSE vendor_contact_csr;
1348
1349 ELSE -- Internal Party Contact
1350
1351 -- Fetch Internal contact record
1352 OPEN employee_contact_csr;
1353 FETCH employee_contact_csr INTO employee_contact_rec;
1354
1355 -- If row doesn't exist then flag the current contact as invalid
1356 IF employee_contact_csr%NOTFOUND THEN
1357 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1358 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1359 G_MODULE||l_api_name, 'Contact with Contact Id: '|| p_contact_id || ' does not exist');
1360 END IF;
1361
1362 l_valid_contact_flag := 'N';
1363 END IF; -- employee_contact_csr%ROWCOUNT <= 0
1364
1365 l_contact_name := employee_contact_rec.contact_name;
1366
1367 CLOSE employee_contact_csr;
1368 END IF; -- p_party_role_code = G_PARTY_ROLE_PARTNER
1369
1370 -- If the current contact is not valid, then log the error message
1371 IF (l_valid_contact_flag = 'N') THEN
1372
1373 -- Get name of the contact
1374 OPEN employee_name_csr;
1375 FETCH employee_name_csr INTO l_contact_name;
1376 CLOSE employee_name_csr;
1377
1378 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1379 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
1380 'Party Contact is invalid: ' || l_contact_name);
1381 END IF;
1382 l_index := px_qa_result_tbl.count + 1;
1383 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
1384 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_CONTACT_T);
1385 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_CONTACT;
1386 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_CONTACT;
1387 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_CONTACT_S);
1388 px_qa_result_tbl(l_index).error_severity := p_severity;
1389 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_CONTACT_SD);
1390 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
1391 p_app_name => G_OKC,
1392 p_msg_name => G_OKC_REP_INV_CONTACT,
1393 p_token1 => G_CONTACT_NAME_TOKEN,
1394 p_token1_value => l_contact_name);
1395 END IF; -- _valid_external_party_flag = 'N'
1396
1397 -- Standard call to get message count and if count is 1, get message info.
1398 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1399
1400 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1401 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1402 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact');
1403 END IF;
1404 EXCEPTION
1405 WHEN FND_API.G_EXC_ERROR THEN
1406 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1407 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
1408 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact with G_EXC_ERROR');
1409 END IF;
1410 IF (tca_contact_csr%ISOPEN) THEN
1411 CLOSE tca_contact_csr ;
1412 END IF;
1413 IF (vendor_contact_csr%ISOPEN) THEN
1414 CLOSE vendor_contact_csr ;
1415 END IF;
1416 IF (employee_contact_csr%ISOPEN) THEN
1417 CLOSE employee_contact_csr ;
1418 END IF;
1419 IF (employee_name_csr%ISOPEN) THEN
1420 CLOSE employee_name_csr ;
1421 END IF;
1422 x_return_status := G_RET_STS_ERROR;
1423 FND_MSG_PUB.Count_And_Get(
1424 p_count => x_msg_count,
1425 p_data => x_msg_data
1426 );
1427
1428 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1429 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1430 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1431 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact with G_EXC_UNEXPECTED_ERROR');
1432 END IF;
1433 IF (tca_contact_csr%ISOPEN) THEN
1434 CLOSE tca_contact_csr ;
1435 END IF;
1436 IF (vendor_contact_csr%ISOPEN) THEN
1437 CLOSE vendor_contact_csr ;
1438 END IF;
1439 IF (employee_contact_csr%ISOPEN) THEN
1440 CLOSE employee_contact_csr ;
1441 END IF;
1442 IF (employee_name_csr%ISOPEN) THEN
1443 CLOSE employee_name_csr ;
1444 END IF;
1445 x_return_status := G_RET_STS_UNEXP_ERROR;
1446 FND_MSG_PUB.Count_And_Get(
1447 p_count => x_msg_count,
1448 p_data => x_msg_data
1449 );
1450
1451 WHEN OTHERS THEN
1452 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1453 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1454 G_MODULE || l_api_name,
1455 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact because of EXCEPTION: ' || sqlerrm);
1456 END IF;
1457 IF (tca_contact_csr%ISOPEN) THEN
1458 CLOSE tca_contact_csr ;
1459 END IF;
1460 IF (vendor_contact_csr%ISOPEN) THEN
1461 CLOSE vendor_contact_csr ;
1462 END IF;
1463 IF (employee_contact_csr%ISOPEN) THEN
1464 CLOSE employee_contact_csr ;
1465 END IF;
1466 IF (employee_name_csr%ISOPEN) THEN
1467 CLOSE employee_name_csr ;
1468 END IF;
1469 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1470 p_msg_name => G_UNEXPECTED_ERROR,
1471 p_token1 => G_SQLCODE_TOKEN,
1472 p_token1_value => sqlcode,
1473 p_token2 => G_SQLERRM_TOKEN,
1474 p_token2_value => sqlerrm);
1475 x_return_status := G_RET_STS_UNEXP_ERROR;
1476 FND_MSG_PUB.Count_And_Get(
1477 p_count => x_msg_count,
1478 p_data => x_msg_data
1479 );
1480 END validate_contact;
1481
1482
1483
1484
1485
1486 -- Start of comments
1487 --API name : validate_contact_role
1488 --Type : Private.
1489 --Function : This procedure checks for validity of the contract role being passed as an input param.
1490 -- : and modifies px_qa_result_tbl table of records that contains validation
1491 -- : errors and warnings
1492 --Pre-reqs : None.
1493 --Parameters :
1494 --IN : p_api_version IN NUMBER Required
1495 -- : p_init_msg_list IN VARCHAR2 Optional
1496 -- Default = FND_API.G_FALSE
1497 -- : p_contact_role_id IN NUMBER Required
1498 -- Contract Role Id to be validated
1499 -- : p_severity IN VARCHAR2 Required
1500 -- Severity level for this check. Possible values are ERROR, WARNING.
1501 --INOUT : px_qa_result_tbl
1502 -- The table of records that contains validation errors and warnings
1503 --OUT : x_return_status OUT VARCHAR2(1)
1504 -- : x_msg_count OUT NUMBER
1505 -- : x_msg_data OUT VARCHAR2(2000)
1506 --Note :
1507 -- End of comments
1508 PROCEDURE validate_contact_role (
1509 p_api_version IN NUMBER,
1510 p_init_msg_list IN VARCHAR2,
1511 p_contact_role_id IN NUMBER,
1512 p_severity IN VARCHAR2,
1513 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
1514 x_msg_data OUT NOCOPY VARCHAR2,
1515 x_msg_count OUT NOCOPY NUMBER,
1516 x_return_status OUT NOCOPY VARCHAR2
1517 ) IS
1518
1519 l_api_version CONSTANT NUMBER := 1;
1520 l_api_name CONSTANT VARCHAR2(30) := 'validate_contact_role';
1521 l_index PLS_INTEGER := 0;
1522 l_valid_contact_role_flag VARCHAR2(1);
1523 l_contact_role_name OKC_REP_CONTACT_ROLES_TL.NAME%TYPE;
1524
1525 CURSOR contact_role_csr IS
1526 SELECT name, start_date, end_date
1527 FROM okc_rep_contact_roles_vl
1528 WHERE contact_role_id = p_contact_role_id;
1529 contact_role_rec contact_role_csr%ROWTYPE;
1530 BEGIN
1531
1532 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1533 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1534 'Entered OKC_REP_QA_CHECK_PVT.validate_contact_role ');
1535 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1536 'Contact Role Id is: ' || p_contact_role_id);
1537 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1538 'Severity level is: ' || p_severity);
1539 END IF;
1540
1541 -- Standard call to check for call compatibility.
1542 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1544 END IF;
1545 -- Initialize message list if p_init_msg_list is set to TRUE.
1546 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1547 FND_MSG_PUB.initialize;
1548 END IF;
1549
1550 -- Initialize API return status to success
1551 x_return_status := FND_API.G_RET_STS_SUCCESS;
1552 l_valid_contact_role_flag := 'Y';
1553
1554 OPEN contact_role_csr;
1555 FETCH contact_role_csr INTO contact_role_rec;
1556 IF contact_role_csr%NOTFOUND THEN
1557 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1558 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1559 G_MODULE||l_api_name, 'Contact role with id : '|| p_contact_role_id || ' does not exist');
1560 END IF;
1561 RAISE FND_API.G_EXC_ERROR;
1562 END IF; -- contact_role_csr%NOTFOUND
1563 l_contact_role_name := contact_role_rec.name;
1564 IF (sysdate BETWEEN nvl(contact_role_rec.start_date, sysdate) AND nvl(contact_role_rec.end_date, sysdate)) THEN
1565 l_valid_contact_role_flag := 'Y';
1566 ELSE
1567 l_valid_contact_role_flag := 'N';
1568 END IF;
1569 CLOSE contact_role_csr;
1570
1571 --Check for Contract Role
1572 IF (l_valid_contact_role_flag = 'N') THEN
1573 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1574 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
1575 'Contract Role is invalid');
1576 END IF;
1577 l_index := px_qa_result_tbl.count + 1;
1578 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
1579 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_CONTACT_ROLE_T);
1580 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_CONTACT_ROLE;
1581 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_CONTACT_ROLE;
1582 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_CONTACT_ROLE_S);
1583 px_qa_result_tbl(l_index).error_severity := p_severity;
1584 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_CONTACT_ROLE_SD);
1585 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
1586 p_app_name => G_OKC,
1587 p_msg_name => G_OKC_REP_INV_CONTACT_ROLE,
1588 p_token1 => G_CONTACT_ROLE_TOKEN,
1589 p_token1_value => l_contact_role_name);
1590 END IF; -- l_valid_contact_role_flag = 'N'
1591 -- Standard call to get message count and if count is 1, get message info.
1592 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1593
1594 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1595 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1596 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact_role');
1597 END IF;
1598 EXCEPTION
1599 WHEN FND_API.G_EXC_ERROR THEN
1600 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1601 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
1602 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact_role with G_EXC_ERROR');
1603 END IF;
1604 IF (contact_role_csr%ISOPEN) THEN
1605 CLOSE contact_role_csr ;
1606 END IF;
1607 x_return_status := G_RET_STS_ERROR;
1608 FND_MSG_PUB.Count_And_Get(
1609 p_count => x_msg_count,
1610 p_data => x_msg_data
1611 );
1612
1613 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1614 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1615 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1616 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact_role with G_EXC_UNEXPECTED_ERROR');
1617 END IF;
1618 IF (contact_role_csr%ISOPEN) THEN
1619 CLOSE contact_role_csr ;
1620 END IF;
1621 x_return_status := G_RET_STS_UNEXP_ERROR;
1622 FND_MSG_PUB.Count_And_Get(
1623 p_count => x_msg_count,
1624 p_data => x_msg_data
1625 );
1626
1627 WHEN OTHERS THEN
1628 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1629 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1630 G_MODULE || l_api_name,
1631 'Leaving OKC_REP_QA_CHECK_PVT.validate_contact_role because of EXCEPTION: ' || sqlerrm);
1632 END IF;
1633 IF (contact_role_csr%ISOPEN) THEN
1634 CLOSE contact_role_csr ;
1635 END IF;
1636 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1637 p_msg_name => G_UNEXPECTED_ERROR,
1638 p_token1 => G_SQLCODE_TOKEN,
1639 p_token1_value => sqlcode,
1640 p_token2 => G_SQLERRM_TOKEN,
1641 p_token2_value => sqlerrm);
1642 x_return_status := G_RET_STS_UNEXP_ERROR;
1643 FND_MSG_PUB.Count_And_Get(
1644 p_count => x_msg_count,
1645 p_data => x_msg_data
1646 );
1647 END validate_contact_role;
1648
1649
1650
1651
1652 -- Start of comments
1653 --API name : validate_risk_event
1654 --Type : Private.
1655 --Function : This procedure checks for validity of the risk event being passed as an input param.
1656 -- : and modifies px_qa_result_tbl table of records that contains validation
1657 -- : errors and warnings
1658 --Pre-reqs : None.
1659 --Parameters :
1660 --IN : p_api_version IN NUMBER Required
1661 -- : p_init_msg_list IN VARCHAR2 Optional
1662 -- Default = FND_API.G_FALSE
1663 -- : p_contact_role IN NUMBER Required
1664 -- Contract type to be validated
1665 -- : p_severity IN VARCHAR2 Required
1666 -- Severity level for this check. Possible values are ERROR, WARNING.
1667 --INOUT : px_qa_result_tbl
1668 -- The table of records that contains validation errors and warnings
1669 --OUT : x_return_status OUT VARCHAR2(1)
1670 -- : x_msg_count OUT NUMBER
1671 -- : x_msg_data OUT VARCHAR2(2000)
1672 --Note :
1673 -- End of comments
1674 PROCEDURE validate_risk_event (
1675 p_api_version IN NUMBER,
1676 p_init_msg_list IN VARCHAR2,
1677 p_risk_event_id IN NUMBER,
1678 p_severity IN VARCHAR2,
1679 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
1680 x_msg_data OUT NOCOPY VARCHAR2,
1681 x_msg_count OUT NOCOPY NUMBER,
1682 x_return_status OUT NOCOPY VARCHAR2
1683 ) IS
1684
1685 l_api_version CONSTANT NUMBER := 1;
1686 l_api_name CONSTANT VARCHAR2(30) := 'validate_risk_event';
1687 l_index PLS_INTEGER := 0;
1688 l_valid_risk_event_flag VARCHAR2(1);
1689 l_risk_event_name OKC_RISK_EVENTS_TL.NAME%TYPE;
1690
1691 CURSOR risk_event_csr IS
1692 SELECT name, start_date, end_date
1693 FROM okc_risk_events_vl
1694 WHERE risk_event_id = p_risk_event_id;
1695 risk_event_rec risk_event_csr%ROWTYPE;
1696 BEGIN
1697
1698 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1699 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1700 'Entered OKC_REP_QA_CHECK_PVT.validate_risk_event ');
1701 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1702 'Risk Event Id is: ' || p_risk_event_id);
1703 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1704 'Severity level is: ' || p_severity);
1705 END IF;
1706
1707 -- Standard call to check for call compatibility.
1708 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1710 END IF;
1711 -- Initialize message list if p_init_msg_list is set to TRUE.
1712 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1713 FND_MSG_PUB.initialize;
1714 END IF;
1715
1716 -- Initialize API return status to success
1717 x_return_status := FND_API.G_RET_STS_SUCCESS;
1718 l_valid_risk_event_flag := 'Y';
1719
1720 OPEN risk_event_csr;
1721 FETCH risk_event_csr INTO risk_event_rec;
1722 IF risk_event_csr%NOTFOUND THEN
1723 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1724 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1725 G_MODULE||l_api_name, 'Risk Event with id : '|| p_risk_event_id || ' does not exist');
1726 END IF;
1727 RAISE FND_API.G_EXC_ERROR;
1728 END IF; -- risk_event_csr%NOTFOUND
1729 l_risk_event_name := risk_event_rec.name;
1730 IF (sysdate BETWEEN nvl(risk_event_rec.start_date, sysdate) AND nvl(risk_event_rec.end_date, sysdate)) THEN
1731 l_valid_risk_event_flag := 'Y';
1732 ELSE
1733 l_valid_risk_event_flag := 'N';
1734 END IF;
1735 CLOSE risk_event_csr;
1736
1737 --Check for Risk Event
1738 IF (l_valid_risk_event_flag = 'N') THEN
1739 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1740 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
1741 'Risk Event is invalid');
1742 END IF;
1743 l_index := px_qa_result_tbl.count + 1;
1744 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
1745 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_RISK_EVENT_T);
1746 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_INV_RISK_EVENT;
1747 px_qa_result_tbl(l_index).message_name := G_OKC_REP_INV_RISK_EVENT;
1748 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_INV_RISK_EVENT_S);
1749 px_qa_result_tbl(l_index).error_severity := p_severity;
1750 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_INV_RISK_EVENT_SD);
1751 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
1752 p_app_name => G_OKC,
1753 p_msg_name => G_OKC_REP_INV_RISK_EVENT,
1754 p_token1 => G_RISK_EVENT_TOKEN,
1755 p_token1_value => l_risk_event_name);
1756 END IF; -- l_valid_risk_event_flag = 'N'
1757 -- Standard call to get message count and if count is 1, get message info.
1758 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1759
1760 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1761 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1762 'Leaving OKC_REP_QA_CHECK_PVT.validate_risk_event');
1763 END IF;
1764 EXCEPTION
1765 WHEN FND_API.G_EXC_ERROR THEN
1766 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1767 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
1768 'Leaving OKC_REP_QA_CHECK_PVT.validate_risk_event with G_EXC_ERROR');
1769 END IF;
1770 IF (risk_event_csr%ISOPEN) THEN
1771 CLOSE risk_event_csr ;
1772 END IF;
1773 x_return_status := G_RET_STS_ERROR;
1774 FND_MSG_PUB.Count_And_Get(
1775 p_count => x_msg_count,
1776 p_data => x_msg_data
1777 );
1778
1779 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1780 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1781 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1782 'Leaving OKC_REP_QA_CHECK_PVT.validate_risk_event with G_EXC_UNEXPECTED_ERROR');
1783 END IF;
1784 IF (risk_event_csr%ISOPEN) THEN
1785 CLOSE risk_event_csr ;
1786 END IF;
1787 x_return_status := G_RET_STS_UNEXP_ERROR;
1788 FND_MSG_PUB.Count_And_Get(
1789 p_count => x_msg_count,
1790 p_data => x_msg_data
1791 );
1792
1793 WHEN OTHERS THEN
1794 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1795 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1796 G_MODULE || l_api_name,
1797 'Leaving OKC_REP_QA_CHECK_PVT.validate_risk_event because of EXCEPTION: ' || sqlerrm);
1798 END IF;
1799 IF (risk_event_csr%ISOPEN) THEN
1800 CLOSE risk_event_csr ;
1801 END IF;
1802 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1803 p_msg_name => G_UNEXPECTED_ERROR,
1804 p_token1 => G_SQLCODE_TOKEN,
1805 p_token1_value => sqlcode,
1806 p_token2 => G_SQLERRM_TOKEN,
1807 p_token2_value => sqlerrm);
1808 x_return_status := G_RET_STS_UNEXP_ERROR;
1809 FND_MSG_PUB.Count_And_Get(
1810 p_count => x_msg_count,
1811 p_data => x_msg_data
1812 );
1813 END validate_risk_event;
1814
1815 -- Start of comments
1816 --API name : contract_type_spec_validat
1817 --Type : Private.
1818 --Function : This API performs Contract specific QA check on a Repository Contract. The API check for:
1819 -- 1. REP_CCT - Error when no deliverables are present on teh contract
1820 -- 2. REP_SBCR - Each role should have only 1 contact
1821 -- 3. REP_SBCR - Check for UDA Acquisition History and Previous Acquisition
1822 --Pre-reqs : None.
1823 --Parameters :
1824 --IN : p_api_version IN NUMBER Required
1825 -- : p_init_msg_list IN VARCHAR2 Optional
1826 -- : Default = FND_API.G_FALSE
1827 -- : p_contract_id IN NUMBER Required
1828 -- : Contract ID of the contract to be QA checked
1829 -- : p_contract_type IN NUMBER Required
1830 -- : Type of the contract to be QA checked
1831 --INOUT : p_qa_result_tbl IN OUT
1832 -- : The table of records that contains validation errors and warnings
1833 --OUT : x_return_status OUT VARCHAR2(1)
1834 -- : x_msg_count OUT NUMBER
1835 -- : x_msg_data OUT VARCHAR2(2000)
1836 -- Note :
1837 -- End of comments
1838
1839 PROCEDURE contract_type_spec_validat (
1840 p_api_version IN NUMBER,
1841 p_init_msg_list IN VARCHAR2,
1842 p_contract_type IN VARCHAR2,
1843 p_contract_id IN NUMBER,
1844 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
1845 x_msg_data OUT NOCOPY VARCHAR2,
1846 x_msg_count OUT NOCOPY NUMBER,
1847 x_return_status OUT NOCOPY VARCHAR2)
1848 IS
1849
1850 l_api_version CONSTANT NUMBER := 1;
1851 l_api_name CONSTANT VARCHAR2(30) := 'validate_repository_for_qa';
1852 l_acq_type_col_name VARCHAR2(30);
1853 l_acq_hist_ag_id NUMBER;
1854 l_prev_acq_ag_id NUMBER;
1855 l_acq_type VARCHAR2(240);
1856 l_sql VARCHAR2(32567);
1857 l_index PLS_INTEGER := 0;
1858
1859 --Contract deliverables for Closeout
1860 cursor c_check_del_cct is
1861 select count(*) from okc_deliverables
1862 where business_document_type = p_contract_type
1863 and business_document_id = p_contract_id;
1864
1865 --Contacts to Role count
1866 CURSOR c_Contact_role_count IS
1867 SELECT Count(*)
1868 FROM (SELECT Count(contact_id) AS contact_count FROM OKC_REP_PARTY_CONTACTS
1869 WHERE contract_id = p_contract_id
1870 GROUP BY contact_role_id)
1871 WHERE contact_count >1;
1872
1873 l_del_count NUMBER;
1874 l_contact_count NUMBER;
1875 l_pre_acq_count NUMBER;
1876 l_con_count NUMBER;
1877 l_remarks_col_name VARCHAR2(30);
1878 l_rec_ag_id NUMBER;
1879 l_add_info_ag_id NUMBER;
1880 l_rec_count NUMBER;
1881 l_remarks VARCHAR2(2000);
1882 l_attr_count NUMBER;
1883
1884 BEGIN
1885
1886 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1887 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1888 'Entered contract_type_spec_validat');
1889 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1890 'Contract Type is: ' || p_contract_type);
1891 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1892 'Contract Id is: ' || p_contract_id);
1893 END IF;
1894 x_return_status := G_RET_STS_SUCCESS;
1895
1896 -- Initialize message list if p_init_msg_list is set to TRUE.
1897 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1898 FND_MSG_PUB.initialize;
1899 END IF;
1900
1901 --Validations for Closeout Checklist Template Contract Type
1902 IF (p_contract_type = 'REP_CCT') THEN
1903
1904 --Check for Deliverables
1905 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1906 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1907 'Check for Deliverables ');
1908 END IF;
1909
1910 OPEN c_check_del_cct;
1911 FETCH c_check_del_cct INTO l_del_count;
1912 CLOSE c_check_del_cct;
1913
1914 IF (l_del_count = 0) THEN
1915 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1916 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
1917 'No Deliverables exists for the contract');
1918 END IF;
1919 l_index := px_qa_result_tbl.count + 1;
1920 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
1921 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_CCT_NO_TASKS_T);
1922 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_CCT_NO_TASKS;
1923 px_qa_result_tbl(l_index).message_name := G_OKC_REP_CCT_NO_TASKS;
1924 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_CCT_NO_TASKS_S);
1925 px_qa_result_tbl(l_index).error_severity := 'E';
1926 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_CCT_NO_TASKS_SD);
1927 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
1928 p_app_name => G_OKC,
1929 p_msg_name => G_OKC_REP_CCT_NO_TASKS);
1930 END IF; -- l_del_count = 0
1931 --Check for Deliverables
1932
1933 END IF;--p_contract_type = 'REP_CCT'
1934
1935 --Validations for SBCR Contract Type
1936 IF (p_contract_type = 'REP_SBCR') THEN
1937
1938 --Check Role and Contacts
1939 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1940 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1941 'Check for Role and Contacts ');
1942 END IF;
1943
1944 SELECT Count(contact_id) INTO l_con_count FROM OKC_REP_PARTY_CONTACTS
1945 WHERE contract_id = p_contract_id ;
1946
1947 IF(l_con_count > 0) THEN
1948 OPEN c_contact_role_count;
1949 FETCH c_contact_role_count INTO l_contact_count;
1950 CLOSE c_contact_role_count;
1951
1952 IF (l_contact_count > 0) THEN
1953 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1954 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
1955 'No Deliverables exists for the contract');
1956 END IF;
1957 l_index := px_qa_result_tbl.count + 1;
1958 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
1959 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_SBCR_CONT_ROLES_T);
1960 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_SBCR_CONT_ROLES;
1961 px_qa_result_tbl(l_index).message_name := G_OKC_REP_SBCR_CONT_ROLES;
1962 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_SBCR_CONT_ROLES_S);
1963 px_qa_result_tbl(l_index).error_severity := 'E';
1964 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_SBCR_CONT_ROLES_SD);
1965 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
1966 p_app_name => G_OKC,
1967 p_msg_name => G_OKC_REP_SBCR_CONT_ROLES);
1968
1969 END IF; -- l_contact_count > 0
1970 END IF; -- l_con_count >0
1971 --End Check Role and Contacts
1972
1973
1974 --Check UDA Acquisition History and Previous Acquisition
1975
1976 SELECT Count(*) INTO l_attr_count
1977 FROM EGO_OBJ_AG_ASSOCS_B assoc, ego_attr_groups_v egv, EGO_PAGE_ENTRIES_B PG
1978 WHERE egv.ATTR_GROUP_NAME IN ( 'SBCR_ACQ_HIST', 'SBCR_PRE_ACQ')
1979 AND assoc.ATTR_GROUP_ID = egv.ATTR_GROUP_ID
1980 AND PG.ASSOCIATION_ID = assoc.ASSOCIATION_ID
1981 AND assoc.CLASSIFICATION_CODE = (SELECT To_Char(uda_template_id)
1982 FROM OKC_REP_CONTRACTS_ALL
1983 WHERE contract_id = p_contract_id)
1984 AND assoc.CLASSIFICATION_CODE = pg.CLASSIFICATION_CODE;
1985
1986 IF (l_attr_count = 2) THEN
1987
1988 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1989 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1990 'Check for UDA Acquisition History ');
1991 END IF;
1992
1993 SELECT fcu.application_column_name INTO l_acq_type_col_name
1994 FROM fnd_descr_flex_column_usages fcu
1995 WHERE fcu.descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
1996 AND fcu.descriptive_flex_context_code = 'SBCR_ACQ_HIST'
1997 AND fcu.end_user_column_name = 'SBCR_ACQ_TYPE';
1998
1999 SELECT attr_group_id INTO l_acq_hist_ag_id
2000 FROM ego_fnd_dsc_flx_ctx_ext
2001 WHERE descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
2002 AND descriptive_flex_context_code = 'SBCR_ACQ_HIST';
2003
2004 SELECT attr_group_id INTO l_prev_acq_ag_id
2005 FROM ego_fnd_dsc_flx_ctx_ext
2006 WHERE descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
2007 AND descriptive_flex_context_code = 'SBCR_PRE_ACQ';
2008
2009 l_sql := 'SELECT ' || l_acq_type_col_name ||
2010 ' FROM okc_rep_contracts_all_ext_b' ||
2011 ' WHERE contract_id = ' || p_contract_id ||
2012 ' AND attr_group_id = ' || l_acq_hist_ag_id;
2013 BEGIN
2014 EXECUTE IMMEDIATE l_sql INTO l_acq_type;
2015 EXCEPTION
2016 WHEN No_Data_Found THEN
2017 NULL;
2018 END;
2019
2020
2021 IF (l_acq_type = 'First Time Buy') THEN
2022
2023 SELECT Count(*) INTO l_pre_acq_count
2024 FROM okc_rep_contracts_all_ext_b
2025 WHERE contract_id = p_contract_id
2026 AND attr_group_id = l_prev_acq_ag_id;
2027
2028 IF (l_pre_acq_count > 0) THEN
2029 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2030 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
2031 'Acquisition History and Previous Acquisition do not match');
2032 END IF;
2033
2034 l_index := px_qa_result_tbl.count + 1;
2035 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
2036 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_SBCR_PREV_ACQ_T);
2037 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_SBCR_PREV_ACQ;
2038 px_qa_result_tbl(l_index).message_name := G_OKC_REP_SBCR_PREV_ACQ;
2039 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_SBCR_PREV_ACQ_S);
2040 px_qa_result_tbl(l_index).error_severity := 'E';
2041 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_SBCR_PREV_ACQ_SD);
2042 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
2043 p_app_name => G_OKC,
2044 p_msg_name => G_OKC_REP_SBCR_PREV_ACQ);
2045 END IF; -- l_pre_acq_count = 0
2046
2047 END IF; -- l_acq_type = 'First Time Buy'
2048 END IF; -- IF (l_attr_count = 2)
2049 --End Check UDA Acquisition History and Previous Acquisition
2050
2051 --Check UDA Recommendation and Remarks
2052
2053 SELECT Count(*) INTO l_attr_count
2054 FROM EGO_OBJ_AG_ASSOCS_B assoc, ego_attr_groups_v egv, EGO_PAGE_ENTRIES_B PG
2055 WHERE egv.ATTR_GROUP_NAME IN ( 'SBCR_ADD_INFO', 'SBCR_REC_AG')
2056 AND assoc.ATTR_GROUP_ID = egv.ATTR_GROUP_ID
2057 AND PG.ASSOCIATION_ID = assoc.ASSOCIATION_ID
2058 AND assoc.CLASSIFICATION_CODE = (SELECT To_Char(uda_template_id)
2059 FROM OKC_REP_CONTRACTS_ALL
2060 WHERE contract_id = p_contract_id)
2061 AND assoc.CLASSIFICATION_CODE = pg.CLASSIFICATION_CODE;
2062
2063 IF (l_attr_count = 2) THEN
2064
2065 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2066 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2067 'Check for UDA Recommendation ');
2068 END IF;
2069
2070 SELECT fcu.application_column_name INTO l_remarks_col_name
2071 FROM fnd_descr_flex_column_usages fcu
2072 WHERE fcu.descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
2073 AND fcu.descriptive_flex_context_code = 'SBCR_ADD_INFO'
2074 AND fcu.end_user_column_name = 'SBCR_REMARKS';
2075
2076 SELECT attr_group_id INTO l_rec_ag_id
2077 FROM ego_fnd_dsc_flx_ctx_ext
2078 WHERE descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
2079 AND descriptive_flex_context_code = 'SBCR_REC_AG';
2080
2081 SELECT attr_group_id INTO l_add_info_ag_id
2082 FROM ego_fnd_dsc_flx_ctx_ext
2083 WHERE descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
2084 AND descriptive_flex_context_code = 'SBCR_ADD_INFO';
2085
2086 SELECT Count(*) INTO l_rec_count
2087 FROM okc_rep_contracts_all_ext_b
2088 WHERE contract_id = p_contract_id
2089 AND attr_group_id = l_rec_ag_id;
2090
2091 IF(l_rec_count = 0) THEN
2092
2093
2094 l_sql := 'SELECT ' || l_remarks_col_name ||
2095 ' FROM okc_rep_contracts_all_ext_b' ||
2096 ' WHERE contract_id = ' || p_contract_id ||
2097 ' AND attr_group_id = ' || l_add_info_ag_id;
2098 BEGIN
2099 EXECUTE IMMEDIATE l_sql INTO l_remarks;
2100 EXCEPTION
2101 WHEN No_Data_Found THEN
2102 l_remarks := NULL;
2103 END;
2104
2105 IF (l_remarks IS NULL ) THEN
2106
2107 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2108 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
2109 'Both Recommendation and Remarks are not entered');
2110 END IF;
2111
2112 l_index := px_qa_result_tbl.count + 1;
2113 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
2114 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_SBCR_REC_T);
2115 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_SBCR_REC;
2116 px_qa_result_tbl(l_index).message_name := G_OKC_REP_SBCR_REC;
2117 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_SBCR_REC_S);
2118 px_qa_result_tbl(l_index).error_severity := 'E';
2119 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_SBCR_REC_SD);
2120 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
2121 p_app_name => G_OKC,
2122 p_msg_name => G_OKC_REP_SBCR_REC);
2123 END IF; -- l_remarks IS NULL
2124
2125 END IF; -- l_rec_count = 0
2126
2127 END IF; --IF (l_attr_count = 2)
2128 --End Check UDA Recommendation and Remarks
2129
2130 END IF;--p_contract_type = 'REP_SBCR'
2131
2132 -- Standard call to get message count and if count is 1, get message info.
2133 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2134 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2135 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
2136 G_MODULE||l_api_name,'leaving OKC_REP_QA_CHECK_PVT.contract_type_spec_validat');
2137 END IF;
2138
2139 EXCEPTION
2140 WHEN FND_API.G_EXC_ERROR THEN
2141 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2142 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
2143 'Leaving OKC_REP_QA_CHECK_PVT.contract_type_spec_validat with G_EXC_ERROR');
2144 END IF;
2145 x_return_status := G_RET_STS_ERROR;
2146 --close cursors
2147 IF (c_check_del_cct%ISOPEN) THEN
2148 CLOSE c_check_del_cct ;
2149 END IF;
2150 IF (c_contact_role_count%ISOPEN) THEN
2151 CLOSE c_contact_role_count ;
2152 END IF;
2153
2154 FND_MSG_PUB.Count_And_Get(
2155 p_count => x_msg_count,
2156 p_data => x_msg_data
2157 );
2158
2159 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2160 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2161 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
2162 'Leaving OKC_REP_QA_CHECK_PVT.contract_type_spec_validat with G_EXC_UNEXPECTED_ERROR');
2163 END IF;
2164 x_return_status := G_RET_STS_UNEXP_ERROR;
2165 --close cursors
2166 IF (c_check_del_cct%ISOPEN) THEN
2167 CLOSE c_check_del_cct ;
2168 END IF;
2169 IF (c_contact_role_count%ISOPEN) THEN
2170 CLOSE c_contact_role_count ;
2171 END IF;
2172
2173 FND_MSG_PUB.Count_And_Get(
2174 p_count => x_msg_count,
2175 p_data => x_msg_data
2176 );
2177
2178 WHEN OTHERS THEN
2179 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2180 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2181 G_MODULE || l_api_name,
2182 'Leaving OKC_REP_QA_CHECK_PVT.contract_type_spec_validat because of EXCEPTION: ' || sqlerrm);
2183 END IF;
2184 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2185 p_msg_name => G_UNEXPECTED_ERROR,
2186 p_token1 => G_SQLCODE_TOKEN,
2187 p_token1_value => sqlcode,
2188 p_token2 => G_SQLERRM_TOKEN,
2189 p_token2_value => sqlerrm);
2190 --close cursors
2191 IF (c_check_del_cct%ISOPEN) THEN
2192 CLOSE c_check_del_cct ;
2193 END IF;
2194 IF (c_contact_role_count%ISOPEN) THEN
2195 CLOSE c_contact_role_count ;
2196 END IF;
2197
2198 x_return_status := G_RET_STS_UNEXP_ERROR;
2199 FND_MSG_PUB.Count_And_Get(
2200 p_count => x_msg_count,
2201 p_data => x_msg_data
2202 );
2203
2204 END contract_type_spec_validat;
2205
2206
2207
2208 -- Start of comments
2209 --API name : validate_Approval_Workflow
2210 --Type : Private.
2211 --Function : This procedure verifies if approval workflow is present for the given contract type
2212 -- : errors and warnings
2213 --Pre-reqs : None.
2214 --Parameters :
2215 --IN : p_api_version IN NUMBER Required
2216 -- : p_init_msg_list IN VARCHAR2 Optional
2217 -- Default = FND_API.G_FALSE
2218 -- : p_contract_type IN NUMBER Required
2219 -- Contract type to be validated
2220 -- : p_severity IN VARCHAR2 Required
2221 -- Severity level for this check. Possible values are ERROR, WARNING.
2222 --INOUT : px_qa_result_tbl
2223 -- The table of records that contains validation errors and warnings
2224 --OUT : x_return_status OUT VARCHAR2(1)
2225 -- : x_msg_count OUT NUMBER
2226 -- : x_msg_data OUT VARCHAR2(2000)
2227 --Note :
2228 -- End of comments
2229
2230 PROCEDURE validate_Approval_Workflow (
2231 p_api_version IN NUMBER,
2232 p_init_msg_list IN VARCHAR2,
2233 p_contract_id IN NUMBER,
2234 p_contract_type IN VARCHAR2,
2235 p_severity IN VARCHAR2,
2236 px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
2237 x_msg_data OUT NOCOPY VARCHAR2,
2238 x_msg_count OUT NOCOPY NUMBER,
2239 x_return_status OUT NOCOPY VARCHAR2
2240 ) IS
2241
2242 l_api_version CONSTANT NUMBER := 1;
2243 l_api_name CONSTANT VARCHAR2(30) := 'validate_Approval_Workflow';
2244 l_index PLS_INTEGER := 0;
2245 l_contract_type_name OKC_BUS_DOC_TYPES_TL.NAME%TYPE;
2246
2247 CURSOR contract_type_csr IS
2248 SELECT approval_workflow_name
2249 FROM okc_bus_doc_types_b
2250 WHERE document_type = p_contract_type;
2251
2252 contract_type_rec contract_type_csr%ROWTYPE;
2253
2254 BEGIN
2255
2256 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2257 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2258 'Entered OKC_REP_QA_CHECK_PVT.validate_E_Signature ');
2259 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2260 'Contract type is: ' || p_contract_type);
2261 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2262 'Severity level is: ' || p_severity);
2263 END IF;
2264
2265 -- Standard call to check for call compatibility.
2266 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2268 END IF;
2269 -- Initialize message list if p_init_msg_list is set to TRUE.
2270 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2271 FND_MSG_PUB.initialize;
2272 END IF;
2273
2274 -- Initialize API return status to success
2275 x_return_status := FND_API.G_RET_STS_SUCCESS;
2276
2277 OPEN contract_type_csr;
2278 FETCH contract_type_csr INTO contract_type_rec;
2279 IF contract_type_csr%NOTFOUND THEN
2280 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2281 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
2282 G_MODULE||l_api_name, 'Contract Type: '|| p_contract_type || ' does not exist');
2283 END IF;
2284 RAISE FND_API.G_EXC_ERROR;
2285 END IF; -- contract_type_csr%NOTFOUND
2286
2287 IF contract_type_rec.approval_workflow_name IS NULL THEN
2288
2289 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2290 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, G_MODULE||l_api_name,
2291 'Contract Approval Workflow not available for the given contrcat type');
2292 END IF;
2293 l_index := px_qa_result_tbl.count + 1;
2294 px_qa_result_tbl(l_index).error_record_type := G_REP_QA_TYPE;
2295 px_qa_result_tbl(l_index).title := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_APP_WF_T);
2296 px_qa_result_tbl(l_index).qa_code := G_CHECK_REP_NO_APP_WF;
2297 px_qa_result_tbl(l_index).message_name := G_OKC_REP_NO_APP_WF;
2298 px_qa_result_tbl(l_index).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_REP_NO_APP_WF_S);
2299 px_qa_result_tbl(l_index).error_severity := p_severity;
2300 px_qa_result_tbl(l_index).problem_short_desc := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_REP_NO_APP_WF_SD);
2301 px_qa_result_tbl(l_index).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(
2302 p_app_name => G_OKC,
2303 p_msg_name => G_OKC_REP_NO_APP_WF);
2304
2305
2306 END IF;
2307
2308 -- Standard call to get message count and if count is 1, get message info.
2309 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2310
2311 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2312 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2313 'Leaving OKC_REP_QA_CHECK_PVT.validate_Approval_Workflow');
2314 END IF;
2315 EXCEPTION
2316 WHEN FND_API.G_EXC_ERROR THEN
2317 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2318 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
2319 'Leaving OKC_REP_QA_CHECK_PVT.validate_Approval_Workflow with G_EXC_ERROR');
2320 END IF;
2321 --close cursors
2322 IF (contract_type_csr%ISOPEN) THEN
2323 CLOSE contract_type_csr ;
2324 END IF;
2325
2326 x_return_status := G_RET_STS_ERROR;
2327
2328 FND_MSG_PUB.Count_And_Get(
2329 p_count => x_msg_count,
2330 p_data => x_msg_data
2331 );
2332
2333 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2334 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2335 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
2336 'Leaving OKC_REP_QA_CHECK_PVT.validate_Approval_Workflow with G_EXC_UNEXPECTED_ERROR');
2337 END IF;
2338 --close cursors
2339 IF (contract_type_csr%ISOPEN) THEN
2340 CLOSE contract_type_csr ;
2341 END IF;
2342 x_return_status := G_RET_STS_UNEXP_ERROR;
2343 FND_MSG_PUB.Count_And_Get(
2344 p_count => x_msg_count,
2345 p_data => x_msg_data
2346 );
2347
2348 WHEN OTHERS THEN
2349
2350 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2351 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2352 G_MODULE || l_api_name,
2353 'Leaving OKC_REP_QA_CHECK_PVT.validate_Approval_Workflow because of EXCEPTION: ' || sqlerrm);
2354 END IF;
2355 --close cursors
2356 IF (contract_type_csr%ISOPEN) THEN
2357 CLOSE contract_type_csr ;
2358 END IF;
2359
2360 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2361 p_msg_name => G_UNEXPECTED_ERROR,
2362 p_token1 => G_SQLCODE_TOKEN,
2363 p_token1_value => sqlcode,
2364 p_token2 => G_SQLERRM_TOKEN,
2365 p_token2_value => sqlerrm);
2366 x_return_status := G_RET_STS_UNEXP_ERROR;
2367 FND_MSG_PUB.Count_And_Get(
2368 p_count => x_msg_count,
2369 p_data => x_msg_data
2370 );
2371 END validate_Approval_Workflow;
2372
2373
2374
2375 -- Start of comments
2376 --API name : validate_repository_for_qa
2377 --Type : Private.
2378 --Function : This API performs QA check on a Repository Contract. The API check for:
2379 -- 1. Check contract for no external party (Warning)
2380 -- 2. Check contract for no effective date (Error)
2381 -- 3. Check contract for invalid contract type
2382 -- 4. Check contract for invalid external party type
2383 -- 5. Check contract for invalid contact
2384 -- 6. Check contract for invalid Risk Event
2385 -- 7. Check contract for invalid Risk Event
2386 -- 8. Calls OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa to qa check the
2387 -- deliverables.
2388 --Pre-reqs : None.
2389 --Parameters :
2390 --IN : p_api_version IN NUMBER Required
2391 -- : p_init_msg_list IN VARCHAR2 Optional
2392 -- : Default = FND_API.G_FALSE
2393 -- : p_contract_id IN NUMBER Required
2394 -- : Contract ID of the contract to be QA checked
2395 -- : p_contract_type IN NUMBER Required
2396 -- : Type of the contract to be QA checked
2397 --INOUT : p_qa_result_tbl IN OUT
2398 -- : The table of records that contains validation errors and warnings
2399 --OUT : x_return_status OUT VARCHAR2(1)
2400 -- : x_msg_count OUT NUMBER
2401 -- : x_msg_data OUT VARCHAR2(2000)
2402 -- Note :
2403 -- End of comments
2404
2405 PROCEDURE validate_repository_for_qa (
2406 p_api_version IN NUMBER,
2407 p_init_msg_list IN VARCHAR2,
2408 p_contract_type IN VARCHAR2,
2409 p_contract_id IN NUMBER,
2410 p_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
2411 x_msg_data OUT NOCOPY VARCHAR2,
2412 x_msg_count OUT NOCOPY NUMBER,
2413 x_return_status OUT NOCOPY VARCHAR2)
2414 IS
2415
2416 l_api_version CONSTANT NUMBER := 1;
2417 l_api_name CONSTANT VARCHAR2(30) := 'validate_repository_for_qa';
2418 -- Contract cursor
2419 CURSOR contract_csr IS
2420 SELECT contract_effective_date,esignature_required
2421 FROM okc_rep_contracts_all
2422 WHERE contract_id = p_contract_id;
2423 contract_rec contract_csr%ROWTYPE;
2424
2425 CURSOR contract_exp_csr IS
2426 SELECT contract_expiration_date
2427 FROM okc_rep_contracts_all
2428 WHERE contract_id = p_contract_id;
2429 contract_exp_rec contract_exp_csr%ROWTYPE;
2430
2431 -- Contract parties cursor, used for validating parties
2432 CURSOR party_csr IS
2433 SELECT party_id, party_role_code
2434 FROM okc_rep_contract_parties
2435 WHERE contract_id = p_contract_id
2436 AND party_role_code <> 'INTERNAL_ORG';
2437 party_rec party_csr%ROWTYPE;
2438 -- Contract contacts cursor, used for validating contacts, contact roles
2439 CURSOR party_contact_csr IS
2440 SELECT party_id, party_role_code, contact_id, contact_role_id
2441 FROM okc_rep_party_contacts
2442 WHERE contract_id = p_contract_id;
2443 party_contact_rec party_contact_csr%ROWTYPE;
2444 -- Contract risks cursor, used for validating risk events
2445 CURSOR risk_csr IS
2446 SELECT risk_event_id
2447 FROM okc_contract_risks
2448 WHERE business_document_type = p_contract_type
2449 AND business_document_id = p_contract_id;
2450 risk_rec risk_csr%ROWTYPE;
2451
2452 BEGIN
2453
2454 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2455 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2456 'Entered validate_repository_for_qa');
2457 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2458 'Contract Type is: ' || p_contract_type);
2459 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2460 'Contract Id is: ' || p_contract_id);
2461 END IF;
2462 x_return_status := G_RET_STS_SUCCESS;
2463 x_return_status := OKC_API.START_ACTIVITY(l_api_name,
2464 p_init_msg_list,
2465 '_PVT',
2466 x_return_status);
2467
2468 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2469 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2470 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2471 RAISE OKC_API.G_EXCEPTION_ERROR;
2472 END IF;
2473
2474 -- Initialize message list if p_init_msg_list is set to TRUE.
2475 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2476 FND_MSG_PUB.initialize;
2477 END IF;
2478
2479 --Check for external party
2480 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2481 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2482 'Calling OKC_REP_QA_CHECK_PVT.check_no_exteral_party() API');
2483 END IF;
2484 check_no_external_party (
2485 p_api_version => 1,
2486 p_init_msg_list => FND_API.G_FALSE,
2487 p_contract_id => p_contract_id,
2488 p_severity => G_QA_STS_WARNING,
2489 px_qa_result_tbl => p_qa_result_tbl,
2490 x_msg_data => x_msg_data,
2491 x_msg_count => x_msg_count,
2492 x_return_status => x_return_status
2493 );
2494 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2495 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2496 'Completed OKC_REP_QA_CHECK_PVT.check_no_exteral_party with returned status: ' || x_return_status);
2497 END IF;
2498 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2499 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2500 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2501 RAISE OKC_API.G_EXCEPTION_ERROR;
2502 END IF;
2503
2504 -- Get effective_date column
2505 OPEN contract_csr;
2506 FETCH contract_csr into contract_rec;
2507 IF(contract_csr%NOTFOUND) THEN
2508 RAISE NO_DATA_FOUND;
2509 END IF;
2510
2511 IF Nvl(contract_rec.esignature_required,'N') ='E' THEN
2512
2513 --Check for ESignature
2514 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2515 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2516 'Calling OKC_REP_QA_CHECK_PVT.validate_E_Signature() API');
2517 END IF;
2518
2519 validate_E_Signature (
2520 p_api_version => 1,
2521 p_init_msg_list => FND_API.G_FALSE,
2522 p_contract_id => p_contract_id,
2523 p_contract_type => p_contract_type,
2524 p_severity => G_QA_STS_ERROR,
2525 px_qa_result_tbl => p_qa_result_tbl,
2526 x_msg_data => x_msg_data,
2527 x_msg_count => x_msg_count,
2528 x_return_status => x_return_status
2529 );
2530 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2531 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2532 'Completed OKC_REP_QA_CHECK_PVT.validate_E_Signature with returned status: ' || x_return_status);
2533 END IF;
2534 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2535 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2536 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2537 RAISE OKC_API.G_EXCEPTION_ERROR;
2538 END IF;
2539
2540 END IF;
2541
2542 -- Log effective date columns
2543 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2544 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
2545 G_MODULE||l_api_name,'Contract Effective Date is : '
2546 || contract_rec.contract_effective_date);
2547 END IF;
2548 --Check for null effective date
2549 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2550 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2551 'Calling OKC_REP_QA_CHECK_PVT.check_no_eff_date() API');
2552 END IF;
2553
2554 if(p_contract_type <> 'REP_SBCR') then
2555 check_no_eff_date (
2556 p_api_version => 1,
2557 p_init_msg_list => FND_API.G_FALSE,
2558 p_effective_date => contract_rec.contract_effective_date,
2559 p_contract_id => p_contract_id,
2560 p_severity => G_QA_STS_ERROR,
2561 px_qa_result_tbl => p_qa_result_tbl,
2562 x_msg_data => x_msg_data,
2563 x_msg_count => x_msg_count,
2564 x_return_status => x_return_status);
2565 end if;
2566
2567 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2568 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2569 'Completed OKC_REP_QA_CHECK_PVT.check_no_eff_date with returned status: ' || x_return_status);
2570 END IF;
2571 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2572 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2573 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2574 RAISE OKC_API.G_EXCEPTION_ERROR;
2575 END IF;
2576
2577 -- Get expiration_date column
2578 OPEN contract_exp_csr;
2579 FETCH contract_exp_csr into contract_exp_rec;
2580 IF(contract_exp_csr%NOTFOUND) THEN
2581 RAISE NO_DATA_FOUND;
2582 END IF;
2583 -- Log expiration date columns
2584 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2585 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
2586 G_MODULE||l_api_name,'Contract Expiration Date is : '
2587 || contract_exp_rec.contract_expiration_date);
2588 END IF;
2589 --Check for expiry
2590 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2591 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2592 'Calling OKC_REP_QA_CHECK_PVT.check_expiry_check() API');
2593 END IF;
2594
2595
2596 check_expiry_check (
2597 p_api_version => 1,
2598 p_init_msg_list => FND_API.G_FALSE,
2599 p_expiration_date => contract_exp_rec.contract_expiration_date,
2600 p_contract_id => p_contract_id,
2601 p_severity => G_QA_STS_ERROR,
2602 px_qa_result_tbl => p_qa_result_tbl,
2603 x_msg_data => x_msg_data,
2604 x_msg_count => x_msg_count,
2605 x_return_status => x_return_status);
2606
2607
2608
2609
2610
2611 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2612 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2613 'Completed OKC_REP_QA_CHECK_PVT.check_expiry_check with returned status: ' || x_return_status);
2614 END IF;
2615 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2616 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2617 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2618 RAISE OKC_API.G_EXCEPTION_ERROR;
2619 END IF;
2620
2621 --Validate contract type
2622 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2623 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2624 'Calling OKC_REP_QA_CHECK_PVT.validate_contract_type() API');
2625 END IF;
2626 validate_contract_type (
2627 p_api_version => 1,
2628 p_init_msg_list => FND_API.G_FALSE,
2629 p_contract_type => p_contract_type,
2630 p_severity => G_QA_STS_WARNING,
2631 px_qa_result_tbl => p_qa_result_tbl,
2632 x_msg_data => x_msg_data,
2633 x_msg_count => x_msg_count,
2634 x_return_status => x_return_status
2635 );
2636 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2637 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2638 'Completed OKC_REP_QA_CHECK_PVT.validate_contract_type with returned status: ' || x_return_status);
2639 END IF;
2640 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2641 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2642 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2643 RAISE OKC_API.G_EXCEPTION_ERROR;
2644 END IF;
2645
2646 --Validate approval Workflow
2647 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2648 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2649 'Calling OKC_REP_QA_CHECK_PVT.validate_Approval_Workflow() API');
2650 END IF;
2651
2652 validate_Approval_Workflow (
2653 p_api_version => 1,
2654 p_init_msg_list => FND_API.G_FALSE,
2655 p_contract_id => p_contract_id,
2656 p_contract_type => p_contract_type,
2657 p_severity => G_QA_STS_ERROR,
2658 px_qa_result_tbl => p_qa_result_tbl,
2659 x_msg_data => x_msg_data,
2660 x_msg_count => x_msg_count,
2661 x_return_status => x_return_status
2662 );
2663
2664 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2665 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2666 'Completed OKC_REP_QA_CHECK_PVT.validate_Approval_Workflow with returned status: ' || x_return_status);
2667 END IF;
2668 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2669 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2670 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2671 RAISE OKC_API.G_EXCEPTION_ERROR;
2672 END IF;
2673
2674 -- Validate Parties
2675 -- Loop through all the external parties and check if these are still valid.
2676 FOR party_rec IN party_csr LOOP
2677 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2678 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2679 'Calling OKC_REP_QA_CHECK_PVT.validate_external_party() API');
2680 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2681 'Party Id is: '|| party_rec.party_id);
2682 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2683 'Party role code is: '|| party_rec.party_role_code);
2684 END IF;
2685 validate_external_party (
2686 p_api_version => 1,
2687 p_init_msg_list => FND_API.G_FALSE,
2688 p_party_role_code => party_rec.party_role_code,
2689 p_party_id => party_rec.party_id,
2690 p_severity => G_QA_STS_WARNING,
2691 px_qa_result_tbl => p_qa_result_tbl,
2692 x_msg_data => x_msg_data,
2693 x_msg_count => x_msg_count,
2694 x_return_status => x_return_status
2695 );
2696 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2697 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2698 'Completed OKC_REP_QA_CHECK_PVT.validate_external_party with returned status: ' || x_return_status);
2699 END IF;
2700 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2701 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2702 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2703 RAISE OKC_API.G_EXCEPTION_ERROR;
2704 END IF;
2705 END LOOP;
2706
2707 -- Validate contacts and contact roles
2708 -- Loop through all the external contacts and check if these are still valid.
2709 FOR party_contact_rec IN party_contact_csr LOOP
2710 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2711 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2712 'Calling OKC_REP_QA_CHECK_PVT.validate_contact() and validate_contact_role() APIs');
2713 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2714 'Party Id is: '|| party_contact_rec.party_id);
2715 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2716 'Party role code is: '|| party_contact_rec.party_role_code);
2717 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2718 'Contact Id is: '|| party_contact_rec.contact_id);
2719 END IF;
2720 -- Validate contact
2721 validate_contact (
2722 p_api_version => 1,
2723 p_init_msg_list => FND_API.G_FALSE,
2724 p_party_role_code => party_contact_rec.party_role_code,
2725 p_party_id => party_contact_rec.party_id,
2726 p_contact_id => party_contact_rec.contact_id,
2727 p_severity => G_QA_STS_WARNING,
2728 px_qa_result_tbl => p_qa_result_tbl,
2729 x_msg_data => x_msg_data,
2730 x_msg_count => x_msg_count,
2731 x_return_status => x_return_status
2732 );
2733 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2734 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2735 'Completed OKC_REP_QA_CHECK_PVT.validate_contact with returned status: ' || x_return_status);
2736 END IF;
2737 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2738 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2739 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2740 RAISE OKC_API.G_EXCEPTION_ERROR;
2741 END IF;
2742
2743 -- Validate contact role
2744 validate_contact_role (
2745 p_api_version => 1,
2746 p_init_msg_list => FND_API.G_FALSE,
2747 p_contact_role_id => party_contact_rec.contact_role_id,
2748 p_severity => G_QA_STS_WARNING,
2749 px_qa_result_tbl => p_qa_result_tbl,
2750 x_msg_data => x_msg_data,
2751 x_msg_count => x_msg_count,
2752 x_return_status => x_return_status
2753 );
2754 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2755 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2756 'Completed OKC_REP_QA_CHECK_PVT.validate_contact_role with returned status: ' || x_return_status);
2757 END IF;
2758 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2759 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2760 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2761 RAISE OKC_API.G_EXCEPTION_ERROR;
2762 END IF;
2763 END LOOP;
2764
2765
2766 -- Validate Parties
2767 -- Loop through all contract risk events and check if these are still valid.
2768 FOR risk_rec IN risk_csr LOOP
2769 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2770 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2771 'Calling OKC_REP_QA_CHECK_PVT.validate_risk_event() API');
2772 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2773 'Risk Event Id is: '|| risk_rec.risk_event_id);
2774 END IF;
2775 validate_risk_event (
2776 p_api_version => 1,
2777 p_init_msg_list => FND_API.G_FALSE,
2778 p_risk_event_id => risk_rec.risk_event_id,
2779 p_severity => G_QA_STS_WARNING,
2780 px_qa_result_tbl => p_qa_result_tbl,
2781 x_msg_data => x_msg_data,
2782 x_msg_count => x_msg_count,
2783 x_return_status => x_return_status
2784 );
2785 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2786 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2787 'Completed OKC_REP_QA_CHECK_PVT.validate_risk_event with returned status: ' || x_return_status);
2788 END IF;
2789 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2790 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2791 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2792 RAISE OKC_API.G_EXCEPTION_ERROR;
2793 END IF;
2794 END LOOP;
2795
2796 --Document Type Specific validations
2797
2798 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2799 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2800 'Calling OKC_REP_QA_CHECK_PVT.contract_type_spec_validat() API');
2801 END IF;
2802 contract_type_spec_validat(p_api_version => 1,
2803 p_init_msg_list => FND_API.G_FALSE,
2804 p_contract_type => p_contract_type,
2805 p_contract_id => p_contract_id,
2806 px_qa_result_tbl => p_qa_result_tbl,
2807 x_msg_data => x_msg_data,
2808 x_msg_count => x_msg_count,
2809 x_return_status => x_return_status );
2810
2811 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2812 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2813 'Completed OKC_REP_QA_CHECK_PVT.contract_type_spec_validat with returned status: ' || x_return_status);
2814 END IF;
2815 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2816 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2817 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2818 RAISE OKC_API.G_EXCEPTION_ERROR;
2819 END IF;
2820
2821 --Call Custom Code hook for additional validations
2822
2823 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2824 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2825 'Calling OKC_REP_VAL_CODE_HOOK.CUSTOM_CONT_VALIDATE() API');
2826 END IF;
2827 OKC_REP_VAL_CODE_HOOK.CUSTOM_CONT_VALIDATE(p_api_version => 1,
2828 p_init_msg_list => FND_API.G_FALSE,
2829 p_contract_type => p_contract_type,
2830 p_contract_id => p_contract_id,
2831 px_qa_result_tbl => p_qa_result_tbl,
2832 x_msg_data => x_msg_data,
2833 x_msg_count => x_msg_count,
2834 x_return_status => x_return_status );
2835
2836 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2837 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
2838 'Completed OKC_REP_VAL_CODE_HOOK.CUSTOM_CONT_VALIDATE with returned status: ' || x_return_status);
2839 END IF;
2840
2841 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2842 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2843 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2844 RAISE OKC_API.G_EXCEPTION_ERROR;
2845 END IF;
2846
2847 -- Close all cursors.
2848 CLOSE contract_csr;
2849 -- Standard call to get message count and if count is 1, get message info.
2850 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2851 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2852 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
2853 G_MODULE||l_api_name,'leaving OKC_REP_QA_CHECK_PVT.validate_repository_for_qa');
2854 END IF;
2855
2856 EXCEPTION
2857 WHEN FND_API.G_EXC_ERROR THEN
2858 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2859 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION , G_MODULE||l_api_name,
2860 'Leaving OKC_REP_QA_CHECK_PVT.validate_repository_for_qa with G_EXC_ERROR');
2861 END IF;
2862 x_return_status := G_RET_STS_ERROR;
2863 --close cursors
2864 IF (contract_csr%ISOPEN) THEN
2865 CLOSE contract_csr ;
2866 END IF;
2867 FND_MSG_PUB.Count_And_Get(
2868 p_count => x_msg_count,
2869 p_data => x_msg_data
2870 );
2871
2872 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2873 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2874 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
2875 'Leaving OKC_REP_QA_CHECK_PVT.validate_repository_for_qa with G_EXC_UNEXPECTED_ERROR');
2876 END IF;
2877 x_return_status := G_RET_STS_UNEXP_ERROR;
2878 --close cursors
2879 IF (contract_csr%ISOPEN) THEN
2880 CLOSE contract_csr ;
2881 END IF;
2882 FND_MSG_PUB.Count_And_Get(
2883 p_count => x_msg_count,
2884 p_data => x_msg_data
2885 );
2886
2887 WHEN OTHERS THEN
2888 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2889 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2890 G_MODULE || l_api_name,
2891 'Leaving OKC_REP_QA_CHECK_PVT.validate_repository_for_qa because of EXCEPTION: ' || sqlerrm);
2892 END IF;
2893 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2894 p_msg_name => G_UNEXPECTED_ERROR,
2895 p_token1 => G_SQLCODE_TOKEN,
2896 p_token1_value => sqlcode,
2897 p_token2 => G_SQLERRM_TOKEN,
2898 p_token2_value => sqlerrm);
2899 --close cursors
2900 IF (contract_csr%ISOPEN) THEN
2901 CLOSE contract_csr ;
2902 END IF;
2903 x_return_status := G_RET_STS_UNEXP_ERROR;
2904 FND_MSG_PUB.Count_And_Get(
2905 p_count => x_msg_count,
2906 p_data => x_msg_data
2907 );
2908
2909 END validate_repository_for_qa;
2910
2911
2912
2913 -- Start of comments
2914 --API name : perform_contract_qa_check
2915 --Type : Private.
2916 --Function : This API performs QA check on a Repository Contract. The API check for:
2917 -- 1. Calls OKC_REP_QA_CHECK_PVT.validate_repository_for_qa() to qa check
2918 -- repository contracts.
2919 -- 2. Calls OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa to check the
2920 -- deliverables.
2921 --Pre-reqs : None.
2922 --Parameters :
2923 --IN : p_api_version IN NUMBER Required
2924 -- : p_init_msg_list IN VARCHAR2 Optional
2925 -- Default = FND_API.G_FALSE
2926 -- : p_contract_id IN NUMBER Required
2927 -- Contract ID of the contract to be QA checked
2928 --OUT : x_return_status OUT VARCHAR2(1)
2929 -- : x_msg_count OUT NUMBER
2930 -- : x_msg_data OUT VARCHAR2(2000)
2931 -- : x_qa_return_status OUT VARCHAR2 (1)
2932 -- QA Check return status. Possible values are S, W, E
2933 -- : x_sequence_id OUT NUMBER
2934 -- Sequence id of the qa check errors in OKC_QA_ERRORS_T table
2935 -- Note :
2936 -- End of comments
2937
2938 PROCEDURE perform_contract_qa_check (
2939 p_api_version IN NUMBER,
2940 p_init_msg_list IN VARCHAR2,
2941 p_contract_id IN NUMBER,
2942 x_msg_count OUT NOCOPY NUMBER,
2943 x_msg_data OUT NOCOPY VARCHAR2,
2944 x_return_status OUT NOCOPY VARCHAR2,
2945 x_qa_return_status OUT NOCOPY VARCHAR2,
2946 x_sequence_id OUT NOCOPY NUMBER)
2947
2948 IS
2949
2950 l_api_version CONSTANT NUMBER := 1;
2951 l_api_name CONSTANT VARCHAR2(30) := 'perform_contract_qa_check';
2952
2953 l_qa_result_tbl OKC_TERMS_QA_PVT.qa_result_tbl_type;
2954 l_bus_doc_date_events_tbl EVENT_TBL_TYPE;
2955 l_error_found Boolean := FALSE;
2956 l_warning_found Boolean := FALSE;
2957
2958 CURSOR contract_csr IS
2959 SELECT contract_type, contract_expiration_date, contract_effective_date
2960 FROM okc_rep_contracts_all
2961 WHERE contract_id = p_contract_id;
2962
2963 contract_rec contract_csr%ROWTYPE;
2964
2965 BEGIN
2966
2967 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2968 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2969 'Entered perform_contract_qa_check');
2970 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2971 'Contract Id is: ' || p_contract_id);
2972 END IF;
2973 x_qa_return_status := G_QA_STS_SUCCESS;
2974 x_return_status := G_RET_STS_SUCCESS;
2975 x_return_status := OKC_API.START_ACTIVITY(l_api_name,
2976 p_init_msg_list,
2977 '_PVT',
2978 x_return_status);
2979
2980 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2981 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2982 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2983 RAISE OKC_API.G_EXCEPTION_ERROR;
2984 END IF;
2985
2986 -- Initialize message list if p_init_msg_list is set to TRUE.
2987 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2988 FND_MSG_PUB.initialize;
2989 END IF;
2990
2991 -- Get effective dates and contract_type of the contract.
2992 OPEN contract_csr;
2993 FETCH contract_csr INTO contract_rec;
2994 IF(contract_csr%NOTFOUND) THEN
2995 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2996 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
2997 G_MODULE||l_api_name,
2998 'Invalid Contract Id: '|| p_contract_id);
2999 END IF;
3000 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3001 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
3002 p_token1 => G_CONTRACT_ID_TOKEN,
3003 p_token1_value => to_char(p_contract_id));
3004 RAISE FND_API.G_EXC_ERROR;
3005 -- RAISE NO_DATA_FOUND;
3006 END IF;
3007
3008
3009 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3010 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3011 'Contract Type is: ' || contract_rec.contract_type);
3012 END IF;
3013
3014 -- Repository Enhancement 12.1 (For Validate Action)
3015
3016 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3017 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3018 'Calling OKC_TERMS_QA_GRP.QA_Doc API');
3019 END IF;
3020
3021
3022 FND_MSG_PUB.initialize;
3023 OKC_TERMS_QA_GRP.QA_Doc (p_api_version => 1,
3024 p_init_msg_list => 'T',
3025 x_return_status => x_return_status,
3026 x_msg_data => x_msg_data,
3027 x_msg_count => x_msg_count,
3028 p_qa_mode => G_NORMAL_QA,
3029 p_doc_type => contract_rec.contract_type,
3030 p_doc_id => p_contract_id,
3031 x_qa_result_tbl => l_qa_result_tbl,
3032 x_qa_return_status => x_qa_return_status);
3033
3034 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3035 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
3036 'Completed OKC_TERMS_QA_GRP.QA_Doc with returned status: ' || x_return_status);
3037 END IF;
3038
3039 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3040 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3041 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3042 RAISE OKC_API.G_EXCEPTION_ERROR;
3043 END IF;
3044 -- Repository Enhancement 12.1 Ends (For Validate Action)
3045
3046 -- Make call for Repository QA check
3047 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3048 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3049 'Calling OKC_REP_QA_CHECK_PVT.validate_repository_for_qa() API');
3050 END IF;
3051 validate_repository_for_qa (
3052 p_api_version => 1,
3053 p_init_msg_list => FND_API.G_FALSE,
3054 p_contract_type => contract_rec.contract_type,
3055 p_contract_id => p_contract_id,
3056 p_qa_result_tbl => l_qa_result_tbl,
3057 x_msg_data => x_msg_data,
3058 x_msg_count => x_msg_count,
3059 x_return_status => x_return_status);
3060 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3061 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
3062 'Completed OKC_REP_QA_CHECK_PVT.validate_repository_for_qa with returned status: ' || x_return_status);
3063 END IF;
3064 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3065 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3066 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3067 RAISE OKC_API.G_EXCEPTION_ERROR;
3068 END IF;
3069
3070 -- Make call for deliverables QA check
3071
3072 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3073 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3074 'Calling OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa() API');
3075 END IF;
3076 l_bus_doc_date_events_tbl(1).event_code := G_CONTRACT_EFFECTIVE_EVENT;
3077 l_bus_doc_date_events_tbl(1).event_date := contract_rec.contract_effective_date;
3078
3079 l_bus_doc_date_events_tbl(2).event_code := G_CONTRACT_EXPIRE_EVENT;
3080 l_bus_doc_date_events_tbl(2).event_date := contract_rec.contract_expiration_date;
3081
3082 OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa (
3083 p_api_version => 1,
3084 p_init_msg_list => FND_API.G_FALSE,
3085 p_doc_type => contract_rec.contract_type,
3086 p_doc_id => p_contract_id,
3087 p_mode => G_NORMAL_QA,
3088 p_bus_doc_date_events_tbl => l_bus_doc_date_events_tbl,
3089 p_qa_result_tbl => l_qa_result_tbl,
3090 x_msg_data => x_msg_data,
3091 x_msg_count => x_msg_count,
3092 x_return_status => x_return_status,
3093 x_qa_return_status => x_qa_return_status);
3094
3095 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3096 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_MODULE||l_api_name,
3097 'Completed OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa with returned status: ' || x_return_status);
3098 END IF;
3099 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3100 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3101 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3102 RAISE OKC_API.G_EXCEPTION_ERROR;
3103 END IF;
3104
3105 --------------------------------------------
3106 -- VALIDATIONS are done for Repository and Deliverables.
3107 -- Now insert into Temp table.
3108 --------------------------------------------
3109 -- Save result from PLSQL table into DB table
3110 --------------------------------------------
3111 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3112 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
3113 G_MODULE||l_api_name,'1015: Save result from PLSQL table into DB table');
3114 END IF;
3115
3116
3117 -- After calling the validation APIs we need to find out about the x_qa_return_status. We should loop through
3118 -- this only if we get
3119 IF l_qa_result_tbl.COUNT > 0 THEN
3120 FOR i IN l_qa_result_tbl.FIRST..l_qa_result_tbl.LAST LOOP
3121 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3122 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3123 'Updating pl/sql table record: ' || i);
3124 END IF;
3125 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);
3126 l_qa_result_tbl(i).error_severity_name := okc_util.decode_lookup('OKC_QA_SEVERITY',l_qa_result_tbl(i).error_severity);
3127 l_qa_result_tbl(i).document_type := contract_rec.contract_type;
3128 l_qa_result_tbl(i).document_id := p_contract_id;
3129 l_qa_result_tbl(i).creation_date := sysdate;
3130 IF l_qa_result_tbl(i).error_severity = G_QA_STS_ERROR THEN
3131 l_error_found := true;
3132 END IF;
3133 IF l_qa_result_tbl(i).error_severity = G_QA_STS_WARNING THEN
3134 l_warning_found := true;
3135 END IF;
3136
3137 END LOOP;
3138 IF l_error_found THEN
3139 x_qa_return_status := G_QA_STS_ERROR;
3140 ELSIF l_warning_found THEN
3141 x_qa_return_status := G_QA_STS_WARNING;
3142 END IF;
3143 END IF; -- l_qa_result_tbl.COUNT > 0 THEN
3144
3145 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3146 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3147 'Calling OKC_TERMS_QA_PVT.Log_QA_Messages');
3148 END IF;
3149 -- Load eror in the DB table
3150 OKC_TERMS_QA_PVT.Log_QA_Messages(
3151 x_return_status => x_return_status,
3152
3153 p_qa_result_tbl => l_qa_result_tbl,
3154 x_sequence_id => x_sequence_id
3155 );
3156 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3157 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3158 'Completed OKC_TERMS_QA_PVT.Log_QA_Messages');
3159 END IF;
3160 --------------------------------------------
3161 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3163 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3164 RAISE FND_API.G_EXC_ERROR ;
3165 END IF;
3166 --------------------------------------------
3167
3168 -- We should commit work now
3169 COMMIT WORK;
3170
3171 CLOSE contract_csr;
3172
3173 -- Standard call to get message count and if count is 1, get message info.
3174 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
3175
3176 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3177 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
3178 G_MODULE||l_api_name,'Leaving OKC_REP_QA_CHECK_PVT.perform_contract_qa_check');
3179 END IF;
3180
3181 EXCEPTION
3182 WHEN FND_API.G_EXC_ERROR THEN
3183
3184 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3185 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
3186 G_MODULE||l_api_name,'Leaving OKC_REP_QA_CHECK_PVT.perform_contract_qa_check with G_EXC_ERROR');
3187 END IF;
3188 --close cursors
3189 IF (contract_csr%ISOPEN) THEN
3190 CLOSE contract_csr ;
3191 END IF;
3192 x_return_status := G_RET_STS_ERROR;
3193 FND_MSG_PUB.Count_And_Get(
3194 p_count => x_msg_count,
3195 p_data => x_msg_data
3196 );
3197
3198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3199
3200 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3201 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
3202 G_MODULE||l_api_name,'Leaving OKC_REP_QA_CHECK_PVT.perform_contract_qa_check with G_EXC_UNEXPECTED_ERROR');
3203 END IF;
3204 --close cursors
3205 IF (contract_csr%ISOPEN) THEN
3206 CLOSE contract_csr ;
3207 END IF;
3208 x_return_status := G_RET_STS_UNEXP_ERROR;
3209 FND_MSG_PUB.Count_And_Get(
3210 p_count => x_msg_count,
3211 p_data => x_msg_data
3212 );
3213
3214 WHEN OTHERS THEN
3215
3216 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3217 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
3218 G_MODULE||l_api_name,'Leaving OKC_REP_QA_CHECK_PVT.perform_contract_qa_check with OTHERS EXCEPTION');
3219 END IF;
3220 --close cursors
3221 IF (contract_csr%ISOPEN) THEN
3222 CLOSE contract_csr ;
3223 END IF;
3224 x_return_status := G_RET_STS_UNEXP_ERROR;
3225 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3226 p_msg_name => G_UNEXPECTED_ERROR,
3227 p_token1 => G_SQLCODE_TOKEN,
3228 p_token1_value => sqlcode,
3229 p_token2 => G_SQLERRM_TOKEN,
3230 p_token2_value => sqlerrm);
3231 FND_MSG_PUB.Count_And_Get(
3232 p_count => x_msg_count,
3233 p_data => x_msg_data
3234 );
3235
3236 END perform_contract_qa_check;
3237
3238
3239 -- Start of comments
3240 --API name : insert_deliverables_qa_check_list
3241 --Type : Private.
3242 --Function : This API inserts QA check list of Deliverables for the specified
3243 -- Contract Type into the table OKC_DOC_QA_LISTS
3244 --Pre-reqs : None.
3245 --Parameters :
3246 --IN : p_api_version IN NUMBER Required
3247 -- : p_init_msg_list IN VARCHAR2 Required
3248 -- : p_contract_type IN VARCHAR2 Required
3249 -- Contract Type for which the QA checkes to be added
3250 --OUT : x_return_status OUT VARCHAR2(1)
3251 -- : x_msg_count OUT NUMBER
3252 -- : x_msg_data OUT VARCHAR2(2000)
3253 -- Note :
3254 -- End of comments
3255
3256 PROCEDURE insert_deliverables_qa_checks (
3257 p_api_version IN NUMBER,
3258 p_init_msg_list IN VARCHAR2,
3259 p_contract_type IN VARCHAR2,
3260 x_msg_count OUT NOCOPY NUMBER,
3261 x_msg_data OUT NOCOPY VARCHAR2,
3262 x_return_status OUT NOCOPY VARCHAR2)
3263 IS
3264
3265 l_api_version CONSTANT NUMBER := 1;
3266 l_api_name CONSTANT VARCHAR2(30) := 'insert_deliverables_qa_checks';
3267 l_okc_doc_qa_lists_tbl okc_doc_qa_lists_tbl_type;
3268 l_user_id FND_USER.USER_ID%TYPE;
3269
3270 BEGIN
3271
3272 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3273 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3274 'Entered insert_deliverables_qa_checks');
3275 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3276 'Contract Type is: ' || p_contract_type);
3277 END IF;
3278
3279 x_return_status := G_RET_STS_SUCCESS;
3280
3281 -- Initialize the Deliverables QA Check table of records
3282 l_okc_doc_qa_lists_tbl(1).qa_code := 'CHECK_AMENDMENT';
3283 l_okc_doc_qa_lists_tbl(1).severity_flag := 'W';
3284 l_okc_doc_qa_lists_tbl(1).enable_qa_yn := 'N';
3285
3286 l_okc_doc_qa_lists_tbl(2).qa_code := 'CHECK_NOTIFICATIONS';
3287 l_okc_doc_qa_lists_tbl(2).severity_flag := 'W';
3288 l_okc_doc_qa_lists_tbl(2).enable_qa_yn := 'Y';
3289
3290 l_okc_doc_qa_lists_tbl(3).qa_code := 'CHECK_BUYER_CONTACT';
3291 l_okc_doc_qa_lists_tbl(3).severity_flag := 'E';
3292 l_okc_doc_qa_lists_tbl(3).enable_qa_yn := 'Y';
3293
3294 l_okc_doc_qa_lists_tbl(4).qa_code := 'CHECK_SUPPLIER_CONTACT';
3295 l_okc_doc_qa_lists_tbl(4).severity_flag := 'E';
3296 l_okc_doc_qa_lists_tbl(4).enable_qa_yn := 'Y';
3297
3298 l_okc_doc_qa_lists_tbl(5).qa_code := 'CHECK_DUE_DATES';
3299 l_okc_doc_qa_lists_tbl(5).severity_flag := 'E';
3300 l_okc_doc_qa_lists_tbl(5).enable_qa_yn := 'Y';
3301
3302 l_okc_doc_qa_lists_tbl(6).qa_code := 'CHECK_DELIVERABLES_VAR_USAGE';
3303 l_okc_doc_qa_lists_tbl(6).severity_flag := 'W';
3304 l_okc_doc_qa_lists_tbl(6).enable_qa_yn := 'N';
3305
3306 l_okc_doc_qa_lists_tbl(7).qa_code := 'CHECK_INTERNAL_CONTACT_VALID';
3307 l_okc_doc_qa_lists_tbl(7).severity_flag := 'W';
3308 l_okc_doc_qa_lists_tbl(7).enable_qa_yn := 'Y';
3309
3310 l_okc_doc_qa_lists_tbl(8).qa_code := 'CHECK_EXTERNAL_PARTY_EXISTS';
3311 l_okc_doc_qa_lists_tbl(8).severity_flag := 'E';
3312 l_okc_doc_qa_lists_tbl(8).enable_qa_yn := 'Y';
3313
3314 l_user_id := FND_GLOBAL.user_id();
3315
3316 FOR i IN l_okc_doc_qa_lists_tbl.FIRST..l_okc_doc_qa_lists_tbl.LAST LOOP
3317
3318 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3319 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
3320 'Inserting pl/sql table record: ' || i);
3321 END IF;
3322
3323 insert into OKC_DOC_QA_LISTS(
3324 QA_CODE,
3325 DOCUMENT_TYPE,
3326 SEVERITY_FLAG,
3327 OBJECT_VERSION_NUMBER,
3328 CREATED_BY,
3329 CREATION_DATE,
3330 LAST_UPDATED_BY,
3331 LAST_UPDATE_DATE,
3332 ENABLE_QA_YN)
3333 values(
3334 l_okc_doc_qa_lists_tbl(i).qa_code,
3335 p_contract_type,
3336 l_okc_doc_qa_lists_tbl(i).severity_flag,
3337 1,
3338 l_user_id,
3339 sysdate,
3340 l_user_id,
3341 sysdate,
3342 l_okc_doc_qa_lists_tbl(i).enable_qa_yn);
3343
3344 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3345 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
3346 'After inserting a row into OKC_DOC_QA_LISTS');
3347 END IF;
3348
3349 END LOOP;
3350
3351 -- Standard call to get message count and if count is 1, get message info.
3352 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
3353
3354 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3355 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
3356 'Leaving OKC_REP_QA_CHECK_PVT.insert_deliverables_qa_checks');
3357 END IF;
3358
3359 EXCEPTION
3360 WHEN FND_API.G_EXC_ERROR THEN
3361
3362 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3363 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
3364 'Leaving OKC_REP_QA_CHECK_PVT.insert_deliverables_qa_checks with G_EXC_ERROR');
3365 END IF;
3366
3367 x_return_status := G_RET_STS_ERROR;
3368
3369 FND_MSG_PUB.Count_And_Get(
3370 p_count => x_msg_count,
3371 p_data => x_msg_data
3372 );
3373
3374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3375
3376 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3377 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
3378 'Leaving OKC_REP_QA_CHECK_PVT.insert_deliverables_qa_checks with G_EXC_UNEXPECTED_ERROR');
3379 END IF;
3380
3381 x_return_status := G_RET_STS_UNEXP_ERROR;
3382
3383 FND_MSG_PUB.Count_And_Get(
3384 p_count => x_msg_count,
3385 p_data => x_msg_data
3386 );
3387
3388 WHEN OTHERS THEN
3389
3390 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3391 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
3392 'Leaving OKC_REP_QA_CHECK_PVT.insert_deliverables_qa_checks with OTHERS EXCEPTION');
3393 END IF;
3394
3395 x_return_status := G_RET_STS_UNEXP_ERROR;
3396
3397 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3398 p_msg_name => G_UNEXPECTED_ERROR,
3399 p_token1 => G_SQLCODE_TOKEN,
3400 p_token1_value => sqlcode,
3401 p_token2 => G_SQLERRM_TOKEN,
3402 p_token2_value => sqlerrm);
3403
3404 FND_MSG_PUB.Count_And_Get(
3405 p_count => x_msg_count,
3406 p_data => x_msg_data
3407 );
3408
3409 END insert_deliverables_qa_checks;
3410
3411 END OKC_REP_QA_CHECK_PVT;