[Home] [Help]
PACKAGE BODY: APPS.OKC_QA_GENERAL_CHECKLISTS
Source
1 PACKAGE BODY OKC_QA_GENERAL_CHECKLISTS AS
2 /* $Header: OKCRQAGB.pls 120.0 2005/05/27 05:17:52 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --
7 G_PACKAGE Varchar2(33) := ' OKC_QA_GENERAL_CHECKLISTS.';
8 --
9 --
10 PROCEDURE check_euro_currency(
11 x_return_status OUT NOCOPY VARCHAR2,
12 p_chr_id IN NUMBER
13 ) IS
14 /*
15 This will check if the currency for an entered contract is not created in NCU and must
16 be EURO
17 */
18
19 cursor csr_contracts is
20 select currency_code, sts_code
21 from okc_k_headers_b
22 where id = p_chr_id;
23
24 l_currency_code VARCHAR2(10);
25 l_sts_code VARCHAR2(20);
26 l_euro_currency_code VARCHAR2(10);
27 l_return_status varchar2(1):='S';
28 --
29 l_proc varchar2(72) := g_package||'check_euro_currency';
30 --
31
32 BEGIN
33
34 IF (l_debug = 'Y') THEN
35 okc_debug.Set_Indentation(l_proc);
36 okc_debug.Log('100: Entering ',2);
37 END IF;
38
39 x_return_status := 'S';
40
41 OPEN csr_contracts;
42 FETCH csr_contracts INTO l_currency_code, l_sts_code;
43 l_euro_currency_code := OKC_CURRENCY_API.GET_EURO_CURRENCY_CODE(l_currency_code);
44 CLOSE csr_contracts;
45 if (l_euro_currency_code <> l_currency_code) and
46 l_sts_code = 'ENTERED' then
47 OKC_API.set_message(
48 p_app_name => G_APP_NAME,
49 p_msg_name => 'OKC_EURO_CHECK_FAIL');
50 x_return_status := OKC_API.G_RET_STS_ERROR;
51 ELSE
52 OKC_API.set_message(
53 p_app_name => G_APP_NAME,
54 p_msg_name => G_QA_SUCCESS);
55 END IF;
56 IF (l_debug = 'Y') THEN
57 okc_debug.Log('200: Leaving ',2);
58 okc_debug.Reset_Indentation;
59 END IF;
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 IF (l_debug = 'Y') THEN
64 okc_debug.Log('300: Leaving ',2);
65 okc_debug.Reset_Indentation;
66 END IF;
67 if csr_contracts%ISOPEN then
68 close csr_contracts;
69 end if;
70 OKC_API.SET_MESSAGE(
71 p_app_name => G_APP_NAME,
72 p_msg_name => G_UNEXPECTED_ERROR,
73 p_token1 => G_SQLCODE_TOKEN,
74 p_token1_value => SQLCODE,
75 p_token2 => G_SQLERRM_TOKEN,
76 p_token2_value => SQLERRM);
77 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
78 end check_euro_currency;
79
80 -- Bug 2041448, skekkar
81
82 FUNCTION GET_EMAIL_FROM_JTFV(
83 p_object_code IN VARCHAR2,
84 p_id1 IN VARCHAR2,
85 p_id2 IN VARCHAR2)
86 RETURN VARCHAR2 IS
87 l_email VARCHAR2(2000);
88 l_from_table VARCHAR2(200);
89 l_where_clause VARCHAR2(2000);
90 l_sql_stmt VARCHAR2(2000);
91 l_not_found BOOLEAN;
92
93 Cursor jtfv_csr IS
94 SELECT FROM_TABLE, WHERE_CLAUSE
95 FROM JTF_OBJECTS_B
96 WHERE OBJECT_CODE = p_object_code;
97
98 Type SOURCE_CSR IS REF CURSOR;
99 c SOURCE_CSR;
100
101 BEGIN
102 open jtfv_csr;
103 fetch jtfv_csr into l_from_table, l_where_clause;
104 l_not_found := jtfv_csr%NOTFOUND;
105 close jtfv_csr;
106
107 If (l_not_found) Then
108 return NULL;
109 End if;
110
111 l_sql_stmt := 'SELECT email_address FROM ' || l_from_table ||
112 ' WHERE ID1 = :id_1 AND ID2 = :id2';
113 If (l_where_clause is not null) Then
114 l_sql_stmt := l_sql_stmt || ' AND (' || l_where_clause || ')';
115 End If;
116 open c for l_sql_stmt using p_id1, p_id2;
117 fetch c into l_email;
118 l_not_found := c%NOTFOUND;
119 close c;
120
121 If (l_not_found) Then
122 return NULL;
123 End if;
124
125 return l_email;
126 EXCEPTION
127 when NO_DATA_FOUND then
128 If (jtfv_csr%ISOPEN) Then
129 Close jtfv_csr;
130 End If;
131 If (c%ISOPEN) Then
132 Close c;
133 End If;
134 return NULL;
135 END;
136
137 -- skekkar
138
139 -- Bug 2041448 , skekkar
140
141 -- Start of comments
142 --
143 -- Procedure Name : check_email_address
144 -- Description :
145 -- Business Rules :
146 -- Parameters :
147 -- Version : 1.0
148 -- End of comments
149
150 PROCEDURE check_email_address(
151 x_return_status OUT NOCOPY VARCHAR2,
152 p_chr_id IN NUMBER
153 ) IS
154 /*
155 This will check that for a contract there is alteast 1 party contact defined with an
156 email address. If not it will issue a warning message
157 */
158
159 cursor csr_contacts is
160 select c.jtot_object1_code, c.object1_id1, c.object1_id2
161 from okc_contacts c, okc_k_party_roles_b p
162 where c.cpl_id = p.id
163 and p.dnz_chr_id = p_chr_id
164 and p.cle_id is null;
165
166 l_jtot_object1_code VARCHAR2(200);
167 l_object1_id1 VARCHAR2(200);
168 l_object1_id2 VARCHAR2(200);
169
170 l_email_address VARCHAR2(2000);
171 l_return_status varchar2(1):='S';
172 l_email_found varchar2(1):= 'N';
173 --
174 l_proc varchar2(72) := g_package||'check_email_address';
175 --
176
177 BEGIN
178
179 IF (l_debug = 'Y') THEN
180 okc_debug.Set_Indentation(l_proc);
181 okc_debug.Log('400: Entering ',2);
182 END IF;
183
184 x_return_status := 'S';
185
186 OPEN csr_contacts;
187 LOOP
188 FETCH csr_contacts INTO l_jtot_object1_code, l_object1_id1, l_object1_id2 ;
189 EXIT WHEN csr_contacts%NOTFOUND;
190 IF (l_debug = 'Y') THEN
191 okc_debug.Log('410: l_jtot_object1_code : '||l_jtot_object1_code,2);
192 okc_debug.Log('420: l_object1_id1 : '||l_object1_id1,2);
193 okc_debug.Log('430: l_object1_id2 : '||l_object1_id2,2);
194 END IF;
195 -- get the email address
196 l_email_address := GET_EMAIL_FROM_JTFV( l_jtot_object1_code, l_object1_id1, l_object1_id2);
197 IF (l_debug = 'Y') THEN
198 okc_debug.Log('440: Email Address : '||l_email_address,2);
199 END IF;
200 IF l_email_address IS NOT NULL THEN
201 -- we got atleast 1 record with email address, so exit loop
202 l_email_found := 'Y';
203 EXIT;
204 END IF;
205 END LOOP;
206 CLOSE csr_contacts;
207
208 --
209 -- issue a warning if no records with email address
210 --
211 IF l_email_found = 'N' THEN
212 OKC_API.set_message(
213 p_app_name => G_APP_NAME,
214 p_msg_name => 'OKC_EMAIL_ADD_WARN');
215 -- notify caller of an error
216 -- x_return_status := OKC_API.G_RET_STS_WARNING;
217 x_return_status := OKC_API.G_RET_STS_ERROR;
218 ELSE
219 OKC_API.set_message(
220 p_app_name => G_APP_NAME,
221 p_msg_name => G_QA_SUCCESS);
222 END IF; -- l_email_found = 'N'
223
224 IF (l_debug = 'Y') THEN
225 okc_debug.Log('500: Leaving ',2);
226 okc_debug.Reset_Indentation;
227 END IF;
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 IF (l_debug = 'Y') THEN
232 okc_debug.Log('600: Leaving ',2);
233 okc_debug.Reset_Indentation;
234 END IF;
235 if csr_contacts%ISOPEN then
236 close csr_contacts;
237 end if;
238 OKC_API.SET_MESSAGE(
239 p_app_name => G_APP_NAME,
240 p_msg_name => G_UNEXPECTED_ERROR,
241 p_token1 => G_SQLCODE_TOKEN,
242 p_token1_value => SQLCODE,
243 p_token2 => G_SQLERRM_TOKEN,
244 p_token2_value => SQLERRM);
245 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
246 end check_email_address;
247 --
248 --
249 -- Bug 2077501 , skekkar
250
251 -- Start of comments
252 --
253 -- Procedure Name : check_email_address_role
254 -- Description :
255 -- Business Rules :
256 -- Parameters :
257 -- Version : 1.0
258 -- End of comments
259
260 PROCEDURE check_email_address_role(
261 x_return_status OUT NOCOPY VARCHAR2,
262 p_chr_id IN NUMBER,
263 p_rle_code IN VARCHAR2
264 ) IS
265 /*
266 This will check that for a contract there is alteast 1 party contact defined with an
267 email address. If not it will issue a warning message
268 */
269
270
271 cursor csr_contacts(p_cpl_id Number) is
272 select c.jtot_object1_code, c.object1_id1, c.object1_id2
273 from okc_contacts c
274 where c.cpl_id = p_cpl_id
275 and c.dnz_chr_id = p_chr_id;
276
277 cursor csr_rle is
278 select id
279 from okc_k_party_roles_b
280 where rle_code = p_rle_code
281 and dnz_chr_id = p_chr_id
282 and cle_id is null;
283
284 /* cursor csr_con_exist is
285 select 'X'
286 from okc_contacts c, okc_k_party_roles_b p
287 where p.id = c.cpl_id
288 and p.dnz_chr_id = p_chr_id
289 and p.rle_code = p_rle_code; */
290
291 l_cpl_id okc_k_party_roles_b.id%TYPE;
292 l_jtot_object1_code VARCHAR2(200);
293 l_object1_id1 VARCHAR2(200);
294 l_object1_id2 VARCHAR2(200);
295
296 l_rle_not_found Boolean := True;
297 l_ctc_not_found Boolean := True;
298 l_email_address VARCHAR2(2000);
299 l_return_status varchar2(1):='S';
300 l_email_found varchar2(1):= 'N';
301
302 --
303 l_proc varchar2(72) := g_package||'check_email_address_role';
304 --
305
306 BEGIN
307
308 IF (l_debug = 'Y') THEN
309 okc_debug.Set_Indentation(l_proc);
310 okc_debug.Log('700: Entering ',2);
311 END IF;
312
313 x_return_status := 'S';
314
315 -- check if the contract has that role
316 OPEN csr_rle;
317 Loop
318 FETCH csr_rle INTO l_cpl_id;
319 Exit When csr_rle%NotFound;
320 l_rle_not_found := False;
321 OPEN csr_contacts(l_cpl_id);
322 LOOP
323 FETCH csr_contacts INTO l_jtot_object1_code, l_object1_id1, l_object1_id2 ;
324 EXIT WHEN csr_contacts%NOTFOUND;
325 l_ctc_not_found := False;
326 IF (l_debug = 'Y') THEN
327 okc_debug.Log('710: l_jtot_object1_code : '||l_jtot_object1_code,2);
328 okc_debug.Log('720: l_object1_id1 : '||l_object1_id1,2);
329 okc_debug.Log('730: l_object1_id2 : '||l_object1_id2,2);
330 END IF;
331 -- get the email address
332 l_email_address := GET_EMAIL_FROM_JTFV(l_jtot_object1_code,
333 l_object1_id1,
334 l_object1_id2);
335 IF (l_debug = 'Y') THEN
336 okc_debug.Log('740: Email Address : '||l_email_address,2);
337 END IF;
338 IF l_email_address IS NOT NULL THEN
339 -- we got atleast 1 record with email address, so exit loop
340 l_email_found := 'Y';
341 EXIT;
342 END IF;
343 END LOOP;
344 CLOSE csr_contacts;
345 If l_email_found = 'Y' Then
346 Exit;
347 End If;
348 END LOOP;
349 CLOSE csr_rle;
350 --
351 -- Set the error message if no role for the contract
352 --
353 If l_rle_not_found Then
354 OKC_API.set_message(p_app_name => G_APP_NAME,
355 p_msg_name => 'OKC_EMAIL_NO_ROLE',
356 p_token1 => 'ROLE',
357 p_token1_value => okc_util.decode_lookup('OKC_ROLE',p_rle_code));
358 -- notify caller of an warning
359 x_return_status := OKC_API.G_RET_STS_WARNING;
360 Raise G_EXCEPTION_HALT_VALIDATION;
361 END IF; -- role exists
362 --
363 -- Set the error messages if no contact for the role
364 --
365 If l_ctc_not_found Then
366 OKC_API.set_message(p_app_name => G_APP_NAME,
367 p_msg_name => 'OKC_EMAIL_NO_CONTACT',
368 p_token1 => 'ROLE',
369 p_token1_value => okc_util.decode_lookup('OKC_ROLE',p_rle_code));
370 -- notify caller of an warning
371 x_return_status := OKC_API.G_RET_STS_WARNING;
372 Raise G_EXCEPTION_HALT_VALIDATION;
373 END IF; -- contact exists
374 --
375 -- issue a warning if no records with email address
376 --
377 IF l_email_found = 'N' THEN
378 OKC_API.set_message(p_app_name => G_APP_NAME,
379 p_msg_name => 'OKC_EMAIL_ADD_WARN_RLE',
380 p_token1 => 'ROLE',
381 p_token1_value => okc_util.decode_lookup('OKC_ROLE',p_rle_code));
382 -- notify caller of an error
383 x_return_status := OKC_API.G_RET_STS_ERROR;
384 Raise G_EXCEPTION_HALT_VALIDATION;
385 END IF; -- l_email_found = 'N'
386 -- QA passed
387 OKC_API.set_message(p_app_name => G_APP_NAME,
388 p_msg_name => 'OKC_EMAIL_ADD_SUCC_RLE',
389 p_token1 => 'ROLE',
390 p_token1_value => okc_util.decode_lookup('OKC_ROLE',p_rle_code));
391
392 IF (l_debug = 'Y') THEN
393 okc_debug.Log('800: Leaving ',2);
394 okc_debug.Reset_Indentation;
395 END IF;
396
397 EXCEPTION
398 WHEN G_EXCEPTION_HALT_VALIDATION Then
399 IF (l_debug = 'Y') THEN
400 okc_debug.Log('850: Leaving ',2);
401 okc_debug.Reset_Indentation;
402 END IF;
403 WHEN OTHERS THEN
404 IF (l_debug = 'Y') THEN
405 okc_debug.Log('900: Leaving ',2);
406 okc_debug.Reset_Indentation;
407 END IF;
408 if csr_rle%ISOPEN then
409 close csr_rle;
410 end if;
411 if csr_contacts%ISOPEN then
412 close csr_contacts;
413 end if;
414 OKC_API.SET_MESSAGE(
415 p_app_name => G_APP_NAME,
416 p_msg_name => G_UNEXPECTED_ERROR,
417 p_token1 => G_SQLCODE_TOKEN,
418 p_token1_value => SQLCODE,
419 p_token2 => G_SQLERRM_TOKEN,
420 p_token2_value => SQLERRM);
421 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
422 end check_email_address_role;
423
424 -- skekkar
425 --
426
427 END OKC_QA_GENERAL_CHECKLISTS;