[Home] [Help]
PACKAGE BODY: APPS.HZ_ORG_INFO_VALIDATE
Source
1 package body HZ_ORG_INFO_VALIDATE as
2 /* $Header: ARHORIVB.pls 120.2 2005/06/16 21:13:11 jhuang ship $ */
3
4 procedure validate_stock_markets(
5 p_stock_markets_rec IN HZ_ORG_INFO_PUB.stock_markets_rec_type,
6 p_create_update_flag IN VARCHAR2,
7 x_return_status IN OUT NOCOPY VARCHAR2
8 )
9 IS
10 l_count NUMBER;
11
12 BEGIN
13 -- no foreign key validation for stock market.
14
15
16 -- validate stock_exchange_code, lookup_type with ?
17 -- comment out the validation below
18 -- because the lookup has not been creasted yet.
19 /* IF p_stock_markets_rec.stock_exchange_code is NOT NULL and
20 p_stock_markets_rec.stock_exchange_code <> FND_API.G_MISS_CHAR THEN
21 SELECT count(*)
22 INTO l_count
23 FROM AR_LOOKUPS
24 WHERE lookup_type = 'STOCK_SYMBOL'
25 AND lookup_code = p_stock_markets_rec.stock_exchange_code;
26
27 if l_count = 0 then
28 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_LOOKUP');
29 FND_MESSAGE.SET_TOKEN('COLUMN', 'STOCK_SYMBOL');
30 FND_MESSAGE.SET_TOKEN('LOOKUP_TYPE', 'YES/NO');
31 FND_MSG_PUB.ADD;
32 end if;
33 END IF; */
34
35 null;
36
37 END validate_stock_markets;
38
39 procedure validate_security_issued(
40 p_security_issued_rec IN HZ_ORG_INFO_PUB.security_issued_rec_type,
41 p_create_update_flag IN VARCHAR2,
42 x_return_status IN OUT NOCOPY VARCHAR2
43 )
44 IS
45 l_count NUMBER;
46 l_stock_exchange_id NUMBER;
47 l_rowid ROWID := NULL;
48 l_begin_date DATE;
49 l_end_date DATE;
50 BEGIN
51 -- check required field:
52 IF (p_create_update_flag = 'C' AND
53 (p_security_issued_rec.stock_exchange_id is NULL OR
54 p_security_issued_rec.stock_exchange_id = FND_API.G_MISS_NUM)) OR
55 (p_create_update_flag = 'U' AND
56 p_security_issued_rec.stock_exchange_id is NULL) THEN
57
58 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
59 FND_MESSAGE.SET_TOKEN('COLUMN', 'stock_exchange_id');
60 FND_MSG_PUB.ADD;
61 x_return_status := FND_API.G_RET_STS_ERROR;
62
63
64 END IF;
65
66 -- check non-updateable field: stock_exchange_id
67 if (p_create_update_flag = 'U' AND
68 (p_security_issued_rec.stock_exchange_id <> FND_API.G_MISS_NUM OR
69 p_security_issued_rec.begin_date <> FND_API.G_MISS_DATE OR
70 p_security_issued_rec.end_date <> FND_API.G_MISS_DATE )) THEN
71 BEGIN
72 SELECT stock_exchange_id, begin_date, end_date
73 INTO l_stock_exchange_id, l_begin_date, l_end_date
74 FROM HZ_SECURITY_ISSUED
75 WHERE security_issued_id = p_security_issued_rec.security_issued_id;
76
77 if l_stock_exchange_id <> p_security_issued_rec.stock_exchange_id AND
78 p_security_issued_rec.stock_exchange_id <> FND_API.G_MISS_NUM THEN
79 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
80 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
81 FND_MSG_PUB.ADD;
82 x_return_status := FND_API.G_RET_STS_ERROR;
83
84 end if;
85
86
87 EXCEPTION WHEN NO_DATA_FOUND THEN
88 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
89 FND_MESSAGE.SET_TOKEN('RECORD', 'security_issued');
90 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_security_issued_rec.stock_exchange_id));
91 FND_MSG_PUB.ADD;
92 x_return_status := FND_API.G_RET_STS_ERROR;
93
94 END;
95
96 END IF;
97
98 -- validate foreign key: party_id to hz_parties,
99 -- stock_exchange_id to hz_stock_markets,
100 -- security_currency_code to fnd_currencies.
101
102
103 IF p_security_issued_rec.party_id is NOT NULL AND
104 p_security_issued_rec.party_id <> FND_API.G_MISS_NUM THEN
105
106 SELECT COUNT(*) INTO l_count
107 FROM hz_parties
108 where party_id = p_security_issued_rec.party_id;
109
110 IF l_count = 0 THEN
111 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
112 FND_MESSAGE.SET_TOKEN('FK', 'party_id');
113 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
114 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_parties');
115 FND_MSG_PUB.ADD;
116 x_return_status := FND_API.G_RET_STS_ERROR;
117
118 END IF;
119
120 END IF;
121
122 IF p_security_issued_rec.stock_exchange_id is NOT NULL AND
123 p_security_issued_rec.stock_exchange_id <> FND_API.G_MISS_NUM THEN
124
125 SELECT COUNT(*) INTO l_count
126 FROM hz_stock_markets
127 where stock_exchange_id = p_security_issued_rec.stock_exchange_id;
128
129 IF l_count = 0 THEN
130 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
131 FND_MESSAGE.SET_TOKEN('FK', 'stock_exchange_id');
132 FND_MESSAGE.SET_TOKEN('COLUMN', 'stock_exchange_id');
133 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_stock_markets');
134 FND_MSG_PUB.ADD;
135 x_return_status := FND_API.G_RET_STS_ERROR;
136
137 END IF;
138
139 END IF;
140
141 IF p_security_issued_rec.security_currency_code is NOT NULL AND
142 p_security_issued_rec.security_currency_code <> FND_API.G_MISS_CHAR THEN
143
144 SELECT count(*)
145 INTO l_count
146 FROM fnd_currencies
147 WHERE currency_code = p_security_issued_rec.security_currency_code
148 AND currency_flag = 'Y'
149 AND enabled_flag in ('Y', 'N');
150
151 if l_count = 0 then
152 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
153 FND_MESSAGE.SET_TOKEN('FK', 'security_currency_code');
154 FND_MESSAGE.SET_TOKEN('COLUMN', 'currency_code');
155 FND_MESSAGE.SET_TOKEN('TABLE', 'fnd_currencies');
156 FND_MSG_PUB.ADD;
157 x_return_status := FND_API.G_RET_STS_ERROR;
158
159 end if;
160 END IF;
161
162 -- end_date should be greater than begin_date
163
164 IF p_create_update_flag = 'C' THEN
165 IF p_security_issued_rec.end_date is NOT NULL AND
166 p_security_issued_rec.end_date <> FND_API.G_MISS_DATE THEN
167 if (p_security_issued_rec.end_date < p_security_issued_rec.begin_date OR
168 p_security_issued_rec.begin_date IS NULL OR
169 p_security_issued_rec.begin_date = FND_API.G_MISS_DATE) THEN
170 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
171 FND_MSG_PUB.ADD;
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 end if;
174 END IF;
175
176 -- compare end_date with database data and user passed data.
177 ELSIF p_create_update_flag = 'U' THEN
178 if (p_security_issued_rec.end_date is NOT NULL AND
179 p_security_issued_rec.end_date <> FND_API.G_MISS_DATE) THEN
180 if p_security_issued_rec.begin_date is NOT NULL AND
181 p_security_issued_rec.begin_date <> FND_API.G_MISS_DATE then
182 if p_security_issued_rec.end_date < p_security_issued_rec.begin_date then
183 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
184 FND_MSG_PUB.ADD;
185 x_return_status := FND_API.G_RET_STS_ERROR;
186
187 end if;
188 elsif (p_security_issued_rec.end_date < l_begin_date OR
189 l_begin_date is NULL ) then
190 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
191 FND_MSG_PUB.ADD;
192 x_return_status := FND_API.G_RET_STS_ERROR;
193
194 end if;
195 elsif (p_security_issued_rec.begin_date is NOT NULL AND
196 p_security_issued_rec.begin_date <> FND_API.G_MISS_DATE) THEN
197 if l_end_date < p_security_issued_rec.begin_date then
198 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
199 FND_MSG_PUB.ADD;
200 x_return_status := FND_API.G_RET_STS_ERROR;
201
202 end if;
203 end if;
204 END IF;
205 --Status Validation
206 hz_common_pub.validate_lookup('REGISTRY_STATUS','status',p_security_issued_rec.status,x_return_status);
207
208 END validate_security_issued;
209
210
211 procedure validate_financial_reports(
212 p_financial_reports_rec IN HZ_ORG_INFO_PUB.financial_reports_rec_type,
213 p_create_update_flag IN VARCHAR2,
214 x_return_status IN OUT NOCOPY VARCHAR2
215 )
216 IS
217 l_count NUMBER;
218 l_party_id NUMBER;
219 l_report_start_date DATE;
220 l_report_end_date DATE;
221 l_content_source_type hz_financial_reports.content_source_type%TYPE;
222 db_actual_content_source hz_financial_reports.actual_content_source%TYPE;
223
224 BEGIN
225
226 -- mandatory fields
227 IF (p_create_update_flag = 'C' AND
228 (p_financial_reports_rec.party_id is NULL OR
229 p_financial_reports_rec.party_id = FND_API.G_MISS_NUM)) OR
230 (p_create_update_flag = 'U' AND
231 p_financial_reports_rec.party_id is NULL) THEN
232
233 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
234 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
235 FND_MSG_PUB.ADD;
236 x_return_status := FND_API.G_RET_STS_ERROR;
237
238 END IF;
239
240 -- non updateable field
241
242 IF (p_create_update_flag = 'U') THEN
243 BEGIN
244 SELECT party_id,report_start_date,
245 report_end_date, content_source_type, actual_content_source
246 INTO l_party_id, l_report_start_date,
247 l_report_end_date, l_content_source_type, db_actual_content_source
248 FROM HZ_FINANCIAL_REPORTS
249 WHERE financial_report_id= p_financial_reports_rec.financial_report_id;
250
251 IF p_financial_reports_rec.party_id <> FND_API.G_MISS_NUM OR
252 p_financial_reports_rec.report_end_date <> FND_API.G_MISS_DATE OR
253 p_financial_reports_rec.report_start_date <> FND_API.G_MISS_DATE THEN
254
255 if l_party_id <> p_financial_reports_rec.party_id AND
256 p_financial_reports_rec.party_id <> FND_API.G_MISS_NUM THEN
257 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
258 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
259 FND_MSG_PUB.ADD;
260 x_return_status := FND_API.G_RET_STS_ERROR;
261
262 end if;
263 END IF;
264
265 EXCEPTION WHEN NO_DATA_FOUND THEN
266 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
267 FND_MESSAGE.SET_TOKEN('RECORD', 'financial report');
268 FND_MESSAGE.SET_TOKEN('VALUE',
269 to_char(p_financial_reports_rec.financial_report_id));
270 FND_MSG_PUB.ADD;
271 x_return_status := FND_API.G_RET_STS_ERROR;
272
273 END;
274
275 END IF;
276
277 -- foreign keys: party_id to HZ_PARTIES table
278
279 IF p_financial_reports_rec.party_id is NOT NULL AND
280 p_financial_reports_rec.party_id <> FND_API.G_MISS_NUM THEN
281
282 SELECT COUNT(*) INTO l_count
283 FROM hz_parties
284 where party_id = p_financial_reports_rec.party_id;
285
286 IF l_count = 0 THEN
287 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
288 FND_MESSAGE.SET_TOKEN('FK', 'party_id');
289 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
290 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_parties');
291 FND_MSG_PUB.ADD;
292 x_return_status := FND_API.G_RET_STS_ERROR;
293
294 END IF;
295
296 END IF;
297
298 -- report_end_date should be greater than report_start_date
299
300 IF p_create_update_flag = 'C' THEN
301 IF p_financial_reports_rec.report_end_date is NOT NULL AND
302 p_financial_reports_rec.report_end_date <> FND_API.G_MISS_DATE THEN
303 if (p_financial_reports_rec.report_end_date
304 < p_financial_reports_rec.report_start_date OR
305 p_financial_reports_rec.report_start_date IS NULL OR
306 p_financial_reports_rec.report_start_date = FND_API.G_MISS_DATE) THEN
307 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
308 FND_MSG_PUB.ADD;
309 x_return_status := FND_API.G_RET_STS_ERROR;
310
311 end if;
312 END IF;
313
314 -- compare end_date with database data and user passed data.
315 ELSIF p_create_update_flag = 'U' THEN
316 if (p_financial_reports_rec.report_end_date is NOT NULL AND
317 p_financial_reports_rec.report_end_date <> FND_API.G_MISS_DATE) THEN
318 if p_financial_reports_rec.report_start_date is NOT NULL AND
319 p_financial_reports_rec.report_start_date <> FND_API.G_MISS_DATE then
320 if p_financial_reports_rec.report_end_date
321 < p_financial_reports_rec.report_start_date then
322 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
323 FND_MSG_PUB.ADD;
324 x_return_status := FND_API.G_RET_STS_ERROR;
325
326 end if;
327 elsif ( p_financial_reports_rec.report_end_date < l_report_start_date OR
328 l_report_start_date is NULL) then
329 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
330 FND_MSG_PUB.ADD;
331 x_return_status := FND_API.G_RET_STS_ERROR;
332
333 end if;
334 elsif (p_financial_reports_rec.report_start_date is NOT NULL AND
335 p_financial_reports_rec.report_start_date <> FND_API.G_MISS_DATE) THEN
336 if l_report_end_date < p_financial_reports_rec.report_start_date then
337 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
338 FND_MSG_PUB.ADD;
339 x_return_status := FND_API.G_RET_STS_ERROR;
340
341 end if;
342 end if;
343 END IF;
344
345 --
346 -- Bug 2197181: removed content_source_type validation as a part of mix-n-match Project.
347 --
348
349 /*
350 --content_source_type validations.
351 --Bug 1363124: validation#2 of content_source_type
352
353 IF p_create_update_flag = 'U' THEN
354 IF l_content_source_type <> p_financial_reports_rec.content_source_type
355 OR p_financial_reports_rec.content_source_type IS NULL THEN
356 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
357 FND_MESSAGE.SET_TOKEN('COLUMN', 'content_source_type');
358 FND_MSG_PUB.ADD;
359 x_return_status := FND_API.G_RET_STS_ERROR;
360 END IF;
361 END IF;
362
363 hz_common_pub.validate_lookup(
364 p_lookup_type => 'CONTENT_SOURCE_TYPE',
365 p_column => 'content_source_type',
366 p_column_value => p_financial_reports_rec.content_source_type,
367 x_return_status => x_return_status
368 );
369
370 IF p_create_update_flag = 'C'
371 AND (p_financial_reports_rec.content_source_type IS NULL
372 OR p_financial_reports_rec.content_source_type = FND_API.G_MISS_CHAR) THEN
373 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
374 FND_MESSAGE.SET_TOKEN('COLUMN', 'content_source_type');
375 FND_MSG_PUB.ADD;
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 END IF;
378
379 */
380
381 -- Bug 2197181: Added validation for mix-n-match
382
383 ----------------------------------------
384 -- validate content_source_type and actual_content_source_type
385 ----------------------------------------
386 -- SSM SST Integration and Extension
387 -- Pass HZ_FINANCIAL_REPORTS for new parameter p_entity_name
388 HZ_MIXNM_UTILITY.ValidateContentSource (
389 p_api_version => 'V1',
390 p_create_update_flag => p_create_update_flag,
391 p_check_update_privilege => 'Y',
392 p_content_source_type => p_financial_reports_rec.content_source_type,
393 p_old_content_source_type => l_content_source_type,
394 p_actual_content_source => p_financial_reports_rec.actual_content_source,
395 p_old_actual_content_source => db_actual_content_source,
396 p_entity_name => 'HZ_FINANCIAL_REPORTS',
397 x_return_status => x_return_status );
398
399 --Status Validation
400 hz_common_pub.validate_lookup('REGISTRY_STATUS','status',p_financial_reports_rec.status,x_return_status);
401
402
403 END validate_financial_reports;
404
405
406 procedure validate_financial_numbers(
407 p_financial_numbers_rec IN HZ_ORG_INFO_PUB.financial_numbers_rec_type,
408 p_create_update_flag IN VARCHAR2,
409 x_return_status IN OUT NOCOPY VARCHAR2,
410 x_rep_content_source_type OUT NOCOPY VARCHAR2,
411 x_rep_actual_content_source OUT NOCOPY VARCHAR2
412 )
413 IS
414 l_count NUMBER;
415 l_financial_report_id NUMBER;
416 db_content_source_type hz_financial_numbers.content_source_type%TYPE;
417 BEGIN
418 -- mandatory field: financial_report_id
419
420 IF (p_create_update_flag = 'C' AND
421 (p_financial_numbers_rec.financial_report_id is NULL OR
422 p_financial_numbers_rec.financial_report_id = FND_API.G_MISS_NUM)) OR
423 (p_create_update_flag = 'U' AND
424 p_financial_numbers_rec.financial_report_id is NULL) THEN
425
426 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
427 FND_MESSAGE.SET_TOKEN('COLUMN', 'financial_report_id');
428 FND_MSG_PUB.ADD;
429 x_return_status := FND_API.G_RET_STS_ERROR;
430
431
432 END IF;
433
434 -- non updateable field
435
436 IF (p_create_update_flag = 'U') THEN
437 SELECT financial_report_id, content_source_type
438 INTO l_financial_report_id, db_content_source_type
439 FROM HZ_FINANCIAL_NUMBERS
440 WHERE financial_number_id = p_financial_numbers_rec.financial_number_id;
441
442 if (p_financial_numbers_rec.financial_report_id <> FND_API.G_MISS_NUM) AND
443 (l_financial_report_id <> p_financial_numbers_rec.financial_report_id)
444 THEN
445 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
446 FND_MESSAGE.SET_TOKEN('COLUMN', 'financial_report_id');
447 FND_MSG_PUB.ADD;
448 x_return_status := FND_API.G_RET_STS_ERROR;
449
450 END IF;
451
452 END IF;
453
454 -- foreign keys: financial_report_id to HZ_FINANCIAL_REPORTS table
455
456 IF p_financial_numbers_rec.financial_report_id is NOT NULL AND
457 p_financial_numbers_rec.financial_report_id <> FND_API.G_MISS_NUM THEN
458
459 BEGIN
460 SELECT content_source_type, actual_content_source
461 INTO x_rep_content_source_type, x_rep_actual_content_source
462 FROM HZ_FINANCIAL_REPORTS
463 where financial_report_id = p_financial_numbers_rec.financial_report_id;
464
465 EXCEPTION WHEN NO_DATA_FOUND THEN
466 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
467 FND_MESSAGE.SET_TOKEN('FK', 'financial_report_id');
468 FND_MESSAGE.SET_TOKEN('COLUMN', 'financial_report_id');
469 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_financial_reports');
470 FND_MSG_PUB.ADD;
471 x_return_status := FND_API.G_RET_STS_ERROR;
472
473 END;
474
475 END IF;
476
477 --
478 -- Bug 2197181: removed content_source_type validation as a part of mix-n-match Project.
479 --
480
481 /*
482
483 --content_source_type validations.
484 --Bug 1363124: validation#2 of content_source_type
485
486 IF p_create_update_flag = 'U' THEN
487 IF l_content_source_type <> p_financial_numbers_rec.content_source_type
488 OR p_financial_numbers_rec.content_source_type IS NULL THEN
489 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
490 FND_MESSAGE.SET_TOKEN('COLUMN', 'content_source_type');
491 FND_MSG_PUB.ADD;
492 x_return_status := FND_API.G_RET_STS_ERROR;
493 END IF;
494 END IF;
495
496 hz_common_pub.validate_lookup(
497 p_lookup_type => 'CONTENT_SOURCE_TYPE',
498 p_column => 'content_source_type',
499 p_column_value => p_financial_numbers_rec.content_source_type,
500 x_return_status => x_return_status
501 );
502
503 IF p_create_update_flag = 'C'
504 AND (p_financial_numbers_rec.content_source_type IS NULL
505 OR p_financial_numbers_rec.content_source_type = FND_API.G_MISS_CHAR) THEN
506 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
507 FND_MESSAGE.SET_TOKEN('COLUMN', 'content_source_type');
508 FND_MSG_PUB.ADD;
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 END IF;
511
512 */
513
514
515 -- validate financial_number_currency ? (which message to display?)
516 --Status Validation
517 hz_common_pub.validate_lookup('REGISTRY_STATUS','status',p_financial_numbers_rec.status,x_return_status);
518
519 -- Bug 2197181 : obsolete content_source_type. Raise error in development
520 -- site if user tries to populate value into this column.
521
522 IF NVL(FND_PROFILE.value('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'N') = 'Y'
523 THEN
524 HZ_UTILITY_V2PUB.Check_ObsoleteColumn (
525 p_api_version => 'V1',
526 p_create_update_flag => p_create_update_flag,
527 p_column => 'content_source_type',
528 p_column_value => p_financial_numbers_rec.content_source_type,
529 p_default_value => 'USER_ENTERED',
530 p_old_column_value => db_content_source_type,
531 x_return_status => x_return_status);
532 END IF;
533
534 END validate_financial_numbers;
535
536 procedure validate_certifications(
537 p_certifications_rec IN HZ_ORG_INFO_PUB.certifications_rec_type,
538 p_create_update_flag IN VARCHAR2,
539 x_return_status IN OUT NOCOPY VARCHAR2
540 )
541 IS
542 l_count NUMBER;
543 l_party_id NUMBER;
544 BEGIN
545 -- check required field: party_id, certification_name
546 IF (p_create_update_flag = 'C' AND
547 (p_certifications_rec.party_id is NULL OR
548 p_certifications_rec.party_id = FND_API.G_MISS_NUM)) OR
549 (p_create_update_flag = 'U' AND
550 p_certifications_rec.party_id is NULL) THEN
551
552 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
553 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
554 FND_MSG_PUB.ADD;
555 x_return_status := FND_API.G_RET_STS_ERROR;
556
557 END IF;
558
559 IF (p_create_update_flag = 'C' AND
560 (p_certifications_rec.certification_name is NULL OR
561 p_certifications_rec.certification_name = FND_API.G_MISS_CHAR)) OR
562 (p_create_update_flag = 'U' AND
563 p_certifications_rec.certification_name is NULL) THEN
564
565 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
566 FND_MESSAGE.SET_TOKEN('COLUMN', 'certification_name');
567 FND_MSG_PUB.ADD;
568 x_return_status := FND_API.G_RET_STS_ERROR;
569
570 END IF;
571
572 -- non-updateable field: party_id
573
574 IF (p_create_update_flag = 'U' AND
575 p_certifications_rec.party_id <> FND_API.G_MISS_NUM) THEN
576
577 SELECT party_id
578 INTO l_party_id
579 FROM HZ_CERTIFICATIONS
580 WHERE certification_id = p_certifications_rec.certification_id;
581
582 if l_party_id <> p_certifications_rec.party_id then
583 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
584 FND_MESSAGE.SET_TOKEN('COLUMN', 'certification_id');
585 FND_MSG_PUB.ADD;
586 x_return_status := FND_API.G_RET_STS_ERROR;
587
588 end if;
589
590 END IF;
591
592 -- check foreign key : party_id to hz_parties.
593
594 IF p_certifications_rec.party_id is NOT NULL AND
595 p_certifications_rec.party_id <> FND_API.G_MISS_NUM THEN
596
597 SELECT count(*)
598 INTO l_count
599 FROM HZ_PARTIES
600 WHERE party_id = p_certifications_rec.party_id;
601
602 if l_count = 0 then
603 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
604 FND_MESSAGE.SET_TOKEN('FK', 'party_id');
605 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
606 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_parties');
607 FND_MSG_PUB.ADD;
608 x_return_status := FND_API.G_RET_STS_ERROR;
609
610 END IF;
611
612 END IF;
613
614 --Status Validation
615
616 hz_common_pub.validate_lookup('REGISTRY_STATUS','status',p_certifications_rec.status,x_return_status);
617 END validate_certifications;
618
619 procedure validate_industrial_reference(
620 p_industrial_reference_rec IN HZ_ORG_INFO_PUB.industrial_reference_rec_type,
621 p_create_update_flag IN VARCHAR2,
622 x_return_status IN OUT NOCOPY VARCHAR2
623 )
624 IS
625 l_count NUMBER;
626 l_party_id NUMBER;
627 BEGIN
628 -- mandatory fields : party_id, industrial_reference
629 IF (p_create_update_flag = 'C' AND
630 (p_industrial_reference_rec.party_id is NULL OR
631 p_industrial_reference_rec.party_id = FND_API.G_MISS_NUM)) OR
632 (p_create_update_flag = 'U' AND
633 p_industrial_reference_rec.party_id is NULL) THEN
634
635 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
636 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
637 FND_MSG_PUB.ADD;
638 x_return_status := FND_API.G_RET_STS_ERROR;
639
640
641 END IF;
642
643 IF (p_create_update_flag = 'C' AND
644 (p_industrial_reference_rec.industry_reference is NULL OR
645 p_industrial_reference_rec.industry_reference = FND_API.G_MISS_CHAR)) OR
646 (p_create_update_flag = 'U' AND
647 p_industrial_reference_rec.industry_reference is NULL) THEN
648
649 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
650 FND_MESSAGE.SET_TOKEN('COLUMN', 'industry_reference');
651 FND_MSG_PUB.ADD;
652 x_return_status := FND_API.G_RET_STS_ERROR;
653
654
655 END IF;
656
657 IF (p_create_update_flag = 'U' AND
658 p_industrial_reference_rec.party_id <> FND_API.G_MISS_NUM) THEN
659 SELECT party_id
660 INTO l_party_id
661 FROM HZ_INDUSTRIAL_REFERENCE
662 WHERE INDUSTRY_REFERENCE_ID = p_industrial_reference_rec.INDUSTRY_REFERENCE_ID;
663
664 if l_party_id <> p_industrial_reference_rec.party_id AND
665 p_industrial_reference_rec.party_id <> FND_API.G_MISS_NUM THEN
666 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
667 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
668 FND_MSG_PUB.ADD;
669 x_return_status := FND_API.G_RET_STS_ERROR;
670
671 end if;
672 END IF;
673
674 -- check foreign key : party_id to hz_parties.
675
676 IF p_industrial_reference_rec.party_id is NOT NULL AND
677 p_industrial_reference_rec.party_id <> FND_API.G_MISS_NUM THEN
678
679 SELECT count(*)
680 INTO l_count
681 FROM HZ_PARTIES
682 WHERE party_id = p_industrial_reference_rec.party_id;
683
684 if l_count = 0 then
685 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
686 FND_MESSAGE.SET_TOKEN('FK', 'party_id');
687 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
688 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_parties');
689 FND_MSG_PUB.ADD;
690 x_return_status := FND_API.G_RET_STS_ERROR;
691
692 END IF;
693
694 END IF;
695
696 --Status Validation
697
698 hz_common_pub.validate_lookup('REGISTRY_STATUS','status',p_industrial_reference_rec.status,x_return_status);
699
700 END validate_industrial_reference;
701
702 procedure validate_industrial_classes(
703 p_industrial_classes_rec IN HZ_ORG_INFO_PUB.industrial_classes_rec_type,
704 p_create_update_flag IN VARCHAR2,
705 x_return_status IN OUT NOCOPY VARCHAR2
706 )
707 IS
708 l_count NUMBER;
709 BEGIN
710
711 -- check required field: code_primary_segment
712
713 IF (p_create_update_flag = 'C' AND
714 (p_industrial_classes_rec.code_primary_segment is NULL OR
715 p_industrial_classes_rec.code_primary_segment = FND_API.G_MISS_CHAR)) OR
716 (p_create_update_flag = 'U' AND
717 p_industrial_classes_rec.code_primary_segment is NULL) THEN
718
719 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
720 FND_MESSAGE.SET_TOKEN('COLUMN', 'code_primary_segment');
721 FND_MSG_PUB.ADD;
722 x_return_status := FND_API.G_RET_STS_ERROR;
723
724
725 END IF;
726
727 END validate_industrial_classes;
728
729
730 procedure validate_industrial_class_app(
731 p_industrial_class_app_rec IN HZ_ORG_INFO_PUB.industrial_class_app_rec_type,
732 p_create_update_flag IN VARCHAR2,
733 x_return_status IN OUT NOCOPY VARCHAR2
734 )
735 IS
736 l_count NUMBER;
737 l_party_id NUMBER;
738 l_industrial_class_id NUMBER;
739 l_begin_date DATE;
740 l_end_date DATE;
741 BEGIN
742
743 -- mandatory fields : party_id, industrial_class_id
744 IF (p_create_update_flag = 'C' AND
745 (p_industrial_class_app_rec.party_id is NULL OR
746 p_industrial_class_app_rec.party_id = FND_API.G_MISS_NUM)) OR
747 (p_create_update_flag = 'U' AND
748 p_industrial_class_app_rec.party_id is NULL) THEN
749
750 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
751 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
752 FND_MSG_PUB.ADD;
753 x_return_status := FND_API.G_RET_STS_ERROR;
754
755 END IF;
756
757 IF (p_create_update_flag = 'C' AND
758 (p_industrial_class_app_rec.industrial_class_id is NULL OR
759 p_industrial_class_app_rec.industrial_class_id = FND_API.G_MISS_NUM)) OR
760 (p_create_update_flag = 'U' AND
761 p_industrial_class_app_rec.industrial_class_id is NULL) THEN
762
763 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
764 FND_MESSAGE.SET_TOKEN('COLUMN', 'industrial_class_id');
765 FND_MSG_PUB.ADD;
766 x_return_status := FND_API.G_RET_STS_ERROR;
767
768 END IF;
769
770
771
772 -- non updateable field
773
774 IF (p_create_update_flag = 'U' AND
775 (p_industrial_class_app_rec.party_id <> FND_API.G_MISS_NUM OR
776 p_industrial_class_app_rec.industrial_class_id <> FND_API.G_MISS_NUM OR
777 p_industrial_class_app_rec.end_date <> FND_API.G_MISS_DATE OR
778 p_industrial_class_app_rec.begin_date <> FND_API.G_MISS_DATE )) THEN
779 BEGIN
780 SELECT party_id, industrial_class_id, begin_date, end_date
781 INTO l_party_id, l_industrial_class_id, l_begin_date, l_end_date
782 FROM HZ_INDUSTRIAL_CLASS_APP
783 WHERE code_applied_id= p_industrial_class_app_rec.code_applied_id;
784
785 if l_party_id <> p_industrial_class_app_rec.party_id AND
786 p_industrial_class_app_rec.party_id <> FND_API.G_MISS_NUM THEN
787 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
788 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
789 FND_MSG_PUB.ADD;
790 x_return_status := FND_API.G_RET_STS_ERROR;
791
792 end if;
793
794 if l_industrial_class_id <> p_industrial_class_app_rec.industrial_class_id AND
795 p_industrial_class_app_rec.industrial_class_id <> FND_API.G_MISS_NUM THEN
796 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
797 FND_MESSAGE.SET_TOKEN('COLUMN', 'industrial_class_id');
798 FND_MSG_PUB.ADD;
799 x_return_status := FND_API.G_RET_STS_ERROR;
800
801 end if;
802
803
804 EXCEPTION WHEN NO_DATA_FOUND THEN
805 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
806 FND_MESSAGE.SET_TOKEN('RECORD', 'industrial class applied');
807 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_industrial_class_app_rec.code_applied_id));
808 FND_MSG_PUB.ADD;
809 x_return_status := FND_API.G_RET_STS_ERROR;
810
811 END;
812
813
814 END IF;
815
816
817
818 -- foreign keys: party_id to HZ_PARTIES table.
819 -- industrial_class_id to hz_industrial_class_app
820
821 IF p_industrial_class_app_rec.party_id is NOT NULL AND
822 p_industrial_class_app_rec.party_id <> FND_API.G_MISS_NUM THEN
823
824 SELECT COUNT(*) INTO l_count
825 FROM hz_parties
826 where party_id = p_industrial_class_app_rec.party_id;
827
828 IF l_count = 0 THEN
829 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
830 FND_MESSAGE.SET_TOKEN('FK', 'party_id');
831 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
832 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_parties');
833 FND_MSG_PUB.ADD;
834 x_return_status := FND_API.G_RET_STS_ERROR;
835
836 END IF;
837
838 END IF;
839
840 IF p_industrial_class_app_rec.industrial_class_id is NOT NULL AND
841 p_industrial_class_app_rec.industrial_class_id <> FND_API.G_MISS_NUM THEN
842
843 SELECT COUNT(*) INTO l_count
844 FROM hz_industrial_classes
845 where industrial_class_id = p_industrial_class_app_rec.industrial_class_id;
846
847 IF l_count = 0 THEN
848 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
849 FND_MESSAGE.SET_TOKEN('FK', 'industrial_class_id');
850 FND_MESSAGE.SET_TOKEN('COLUMN', 'industrial_class_id');
851 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_industrial_classes');
852 FND_MSG_PUB.ADD;
853 x_return_status := FND_API.G_RET_STS_ERROR;
854
855 END IF;
856
857 END IF;
858
859
860 -- end_date should be greater than begin_date
861
862 IF p_create_update_flag = 'C' THEN
863 IF p_industrial_class_app_rec.end_date is NOT NULL AND
864 p_industrial_class_app_rec.end_date <> FND_API.G_MISS_DATE THEN
865 if (p_industrial_class_app_rec.end_date
866 < p_industrial_class_app_rec.begin_date OR
867 p_industrial_class_app_rec.begin_date IS NULL OR
868 p_industrial_class_app_rec.begin_date = FND_API.G_MISS_DATE) THEN
869 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
870 FND_MSG_PUB.ADD;
871 x_return_status := FND_API.G_RET_STS_ERROR;
872
873 end if;
874 END IF;
875
876
877 -- compare end_date with database data and user passed data.
878 ELSIF p_create_update_flag = 'U' THEN
879 if (p_industrial_class_app_rec.end_date is NOT NULL AND
880 p_industrial_class_app_rec.end_date <> FND_API.G_MISS_DATE) THEN
881 if p_industrial_class_app_rec.begin_date is NOT NULL AND
882 p_industrial_class_app_rec.begin_date <> FND_API.G_MISS_DATE then
883 if p_industrial_class_app_rec.end_date
884 <p_industrial_class_app_rec.begin_date then
885 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
886 FND_MSG_PUB.ADD;
887 x_return_status := FND_API.G_RET_STS_ERROR;
888
889 end if;
890 elsif (p_industrial_class_app_rec.end_date < l_begin_date OR
891 l_begin_date is NULL) then
892 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
893 FND_MSG_PUB.ADD;
894 x_return_status := FND_API.G_RET_STS_ERROR;
895
896 end if;
897 elsif (p_industrial_class_app_rec.begin_date is NOT NULL AND
898 p_industrial_class_app_rec.begin_date <> FND_API.G_MISS_DATE) THEN
899 if l_end_date < p_industrial_class_app_rec.begin_date then
900 FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
901 FND_MSG_PUB.ADD;
902 x_return_status := FND_API.G_RET_STS_ERROR;
903 end if;
904 end if;
905 END IF;
906 END validate_industrial_class_app;
907
908
909 END HZ_ORG_INFO_VALIDATE;