[Home] [Help]
PACKAGE BODY: APPS.ECE_RULES_PKG
Source
1 package body ECE_RULES_PKG AS
2 -- $Header: ECERULEB.pls 120.4.12010000.2 2008/11/24 17:21:59 akemiset ship $
3
4 translator_code VARCHAR2(30);
5
6 PROCEDURE Update_Status (
7 p_transaction_type IN VARCHAR2,
8 p_level IN NUMBER,
9 p_valid_rule IN VARCHAR2,
10 p_action IN VARCHAR2,
11 p_interface_column_id IN NUMBER DEFAULT NULL,
12 p_rule_id IN NUMBER,
13 p_stage_id IN NUMBER,
14 p_document_id IN NUMBER,
15 p_violation_level IN VARCHAR2,
16 p_document_number IN VARCHAR2,
17 p_msg_text IN VARCHAR2) IS
18
19 xProgress VARCHAR2(80);
20 l_cur_status ece_stage.status%TYPE;
21 l_new_status ece_stage.status%TYPE;
22 l_cur_index NUMBER;
23 l_seq NUMBER;
24
25 BEGIN
26
27 if ec_debug.G_debug_level >= 2 then
28 ec_debug.push ('ECE_RULES_PKG.UPDATE_STATUS');
29 ec_debug.pl (3, 'p_transaction_type', p_transaction_type);
30 ec_debug.pl (3, 'p_level', p_level);
31 ec_debug.pl (3, 'p_valid_rule', p_valid_rule);
32 ec_debug.pl (3, 'p_action', p_action);
33 ec_debug.pl (3, 'p_interface_column_id', p_interface_column_id);
34 ec_debug.pl (3, 'p_rule_id', p_rule_id);
35 ec_debug.pl (3, 'p_stage_id', p_stage_id);
36 ec_debug.pl (3, 'p_document_id', p_document_id);
37 ec_debug.pl (3, 'p_document_number', p_document_number);
38 ec_debug.pl (3, 'p_msg_text', p_msg_text);
39 end if;
40
41 xProgress := 'ECERULEB-10-1000';
42 l_cur_status := ec_utils.g_ext_levels(p_level).status;
43 if ec_debug.G_debug_level =3 then
44 ec_debug.pl (3, 'Current Status', l_cur_status);
45 end if;
46
47 if (p_valid_rule = 'Y') then
48
49 -- If the current status is NEW or RE_PROCESS, then we have
50 -- to update it to 'INSERT'.
51 -- Otherwise, we should keep the current status.
52 if (l_cur_status = g_new or l_cur_status = g_reprocess) then
53 ec_utils.g_ext_levels(p_level).status := g_insert;
54 end if;
55
56 elsif (p_action <> g_log_only) then
57
58 -- We have to compare the staging table status with the current action
59 -- and update the status with the higher priority action.
60 -- For example, if the staging table already has a status to abort,
61 -- and the current action is to skip document, then we should keep
62 -- the status as 'ABORT' since 'ABORT' has higher priority.
63 -- The prioritized list: 'ABORT', 'SKIP_DOCUMENT', 'INSERT', 'NEW',
64 -- 'RE_PROCESS'.
65
66 xProgress := 'ECERULEB-10-1010';
67 if (p_action = g_abort or l_cur_status = g_abort) then
68 l_new_status := g_abort;
69 elsif (p_action = g_skip_doc or l_cur_status = g_skip_doc) then
70 l_new_status := g_skip_doc;
71 else
72 l_new_status := g_insert;
73 end if;
74
75 ec_utils.g_ext_levels(p_level).status := l_new_status;
76
77 xProgress := 'ECERULEB-10-1020';
78 -- insert new violation information into ece_rule_violations table.
79
80 l_cur_index := g_rule_violation_tbl.count + 1;
81 select ece_rule_violations_s.nextval into l_seq from dual;
82
83 if SQL%NOTFOUND then
84 ec_debug.pl (0, 'EC', 'ECE_GET_NEXT_SEQ_FAILED',
85 'PROGRESS_LEVEL', xProgress,
86 'SEQ', 'ECE_RULE_VIOLATIONS_S');
87 end if;
88
89 g_rule_violation_tbl(l_cur_index).violation_id := l_seq;
90 g_rule_violation_tbl(l_cur_index).document_id := p_document_id;
91 g_rule_violation_tbl(l_cur_index).stage_id := p_stage_id;
92 g_rule_violation_tbl(l_cur_index).interface_column_id := p_interface_column_id;
93 g_rule_violation_tbl(l_cur_index).rule_id := p_rule_id;
94 g_rule_violation_tbl(l_cur_index).transaction_type := p_transaction_type;
95 g_rule_violation_tbl(l_cur_index).document_number := p_document_number;
96 g_rule_violation_tbl(l_cur_index).violation_level := p_violation_level;
97 g_rule_violation_tbl(l_cur_index).ignore_flag := 'N';
98 g_rule_violation_tbl(l_cur_index).message_text := p_msg_text;
99
100 if ec_debug.G_debug_level =3 then
101 ec_debug.pl (3, 'EC', 'ECE_VIOLATIONS_INSERTED');
102 end if;
103
104 else
105
106 -- If the action is log only and current status is 'NEW' or
107 -- 'RE_PROCESS, then we only need to update the status to
108 -- 'INSERT' since the violation message is written to the
109 -- log file within the corresponding rule validate procedure.
110 -- Otherwise, we should keep the same status.
111
112 xProgress := 'ECERULEB-10-1030';
113 if (l_cur_status = g_new or l_cur_status = g_reprocess) then
114 ec_utils.g_ext_levels(p_level).status := g_insert;
115 end if;
116
117 end if;
118
119 if ec_debug.G_debug_level >= 2 then
120 ec_debug.pl (3, 'Updated status',
121 ec_utils.g_ext_levels(p_level).status);
122 ec_debug.pop ('ECE_RULES_PKG.UPDATE_STATUS');
123 end if;
124
125 EXCEPTION
126 WHEN OTHERS then
127 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
128 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
129 ec_debug.pop ('ECE_RULES_PKG.UPDATE_STATUS');
130 raise fnd_api.g_exc_unexpected_error;
131
132 END Update_Status;
133
134
135 PROCEDURE Validate_Process_Rules(
136 p_api_version_number IN NUMBER,
137 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
138 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
139 p_commit IN VARCHAR2 := FND_API.G_FALSE,
140 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2,
144 p_transaction_type IN VARCHAR2,
145 p_address_type IN VARCHAR2,
146 p_stage_id IN NUMBER,
147 p_document_id IN NUMBER,
148 p_document_number IN VARCHAR2,
149 p_level IN NUMBER,
150 p_map_id IN NUMBER,
151 p_staging_tbl IN OUT NOCOPY ec_utils.mapping_tbl) IS
152
153 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Process_Rules';
154 l_api_version_number CONSTANT NUMBER := 1.0;
155
156 xProgress VARCHAR2(80);
157 l_rule_type ece_process_rules.rule_type%TYPE;
158 l_rule_id NUMBER;
159 l_action_code ece_process_rules.action_code%TYPE;
160 l_valid_rule VARCHAR2(1):= 'Y';
161 l_tp_detail_id NUMBER;
162 l_violation_level VARCHAR2(10) := g_process_rule;
163 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
164 l_msg_text ece_rule_violations.message_text%TYPE;
165 no_process_rule_info EXCEPTION;
166
167 CURSOR c_rule_info(
168 p_rule_type VARCHAR2,
169 p_transaction_type VARCHAR2) IS
170 select process_rule_id, action_code
171 from ece_process_rules
172 where transaction_type = p_transaction_type and
173 map_id = p_map_id and
174 rule_type = p_rule_type;
175
176 CURSOR c_ignore_flag(
177 p_document_id NUMBER,
178 p_rule_id NUMBER) IS
179 select ignore_flag
180 from ece_rule_violations
181 where document_id = p_document_id and
182 rule_id = p_rule_id and
183 violation_level = l_violation_level;
184
185 BEGIN
186 if ec_debug.G_debug_level >= 2 then
187 ec_debug.push ('ECE_RULES_PKG.VALIDATE_PROCESS_RULES');
188 ec_debug.pl (3, 'p_transaction_type', p_transaction_type);
189 ec_debug.pl (3, 'p_address_type', p_address_type);
190 ec_debug.pl (3, 'p_stage_id', p_stage_id);
191 ec_debug.pl (3, 'p_document_id', p_document_id);
192 ec_debug.pl (3, 'p_document_number', p_document_number);
193 ec_debug.pl (3, 'p_level', p_level);
194 ec_debug.pl (3, 'p_map_id', p_map_id);
195 end if;
196
197 -- Standard call to check for call compatibility.
198 if not fnd_api.compatible_api_call (l_api_version_number,
199 p_api_version_number, l_api_name,
200 g_pkg_name) then
201 raise fnd_api.g_exc_unexpected_error;
202 end if;
203
204 -- Initialize message list if p_init_msg_list is set to TRUE.
205 if fnd_api.to_boolean(p_init_msg_list) then
206 fnd_msg_pub.initialize;
207 end if;
208
209 -- Initialize API return status to success
210 x_return_status := fnd_api.g_ret_sts_success;
211
212 xProgress := 'ECERULEB-20-1000';
213 l_rule_type := g_p_trading_partner;
214
215 open c_rule_info (l_rule_type, p_transaction_type);
216 fetch c_rule_info into l_rule_id, l_action_code;
217
218 if c_rule_info%NOTFOUND then
219 raise no_process_rule_info;
220 end if;
221 close c_rule_info;
222
223 if ec_debug.G_debug_level =3 then
224 ec_debug.pl (3, 'rule_type', l_rule_type);
225 ec_debug.pl (3, 'action_code', l_action_code);
226 end if;
227
228 xProgress := 'ECERULEB-20-1004';
229 open c_ignore_flag (p_document_id, l_rule_id);
230 fetch c_ignore_flag into l_ignore_flag;
231
232 if c_ignore_flag%NOTFOUND then
233 l_ignore_flag := 'N';
234 end if;
235 close c_ignore_flag;
236
237 if ec_debug.G_debug_level =3 then
238 ec_debug.pl (3, 'ignore_flag', l_ignore_flag);
239 end if;
240
241
242 xProgress := 'ECERULEB-20-1010';
243 Validate_Trading_Partner(p_transaction_type, p_address_type, p_level,
244 p_map_id, p_staging_tbl, l_tp_detail_id,
245 l_msg_text, l_valid_rule);
246
247 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
248 xProgress := 'ECERULEB-20-1020';
249 Update_Status(p_transaction_type, p_level, l_valid_rule, l_action_code, NULL,
250 l_rule_id, p_stage_id, p_document_id, l_violation_level,
251 p_document_number, l_msg_text);
252 end if;
253
254 if (l_tp_detail_id <> -1) then
255 xProgress := 'ECERULEB-20-1030';
256 l_rule_type := g_p_test_prod;
257 open c_rule_info (l_rule_type, p_transaction_type);
258 fetch c_rule_info into l_rule_id, l_action_code;
259
260 if c_rule_info%NOTFOUND then
261 raise no_process_rule_info;
262 end if;
263 close c_rule_info;
264
265 if ec_debug.G_debug_level =3 then
266 ec_debug.pl (3, 'rule_type', l_rule_type);
267 ec_debug.pl (3, 'action_code', l_action_code);
268 end if;
269
270 xProgress := 'ECERULEB-20-1040';
271 open c_ignore_flag (p_document_id, l_rule_id);
272 fetch c_ignore_flag into l_ignore_flag;
273
274 if c_ignore_flag%NOTFOUND then
275 l_ignore_flag := 'N';
276 end if;
277 close c_ignore_flag;
278
279 if ec_debug.G_debug_level =3 then
280 ec_debug.pl (3, 'ignore_flag', l_ignore_flag);
281 end if;
282
283 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
284 xProgress := 'ECERULEB-20-1050';
285 Validate_Test_Prod (l_tp_detail_id, p_level, p_staging_tbl,
286 l_msg_text, l_valid_rule);
287
288 xProgress := 'ECERULEB-20-1060';
289 Update_Status(p_transaction_type, p_level, l_valid_rule, l_action_code,
290 NULL, l_rule_id, p_stage_id, p_document_id,
291 l_violation_level, p_document_number, l_msg_text);
292
293 end if;
294 end if;
295
296 if (fnd_api.to_boolean(p_simulate)) then
297 null;
298 elsif (fnd_api.to_boolean(p_commit)) then
299 commit work;
300 end if;
301
302 -- Standard call to get message count and if count is 1, get message info.
303 fnd_msg_pub.count_and_get(p_count => x_msg_count,
304 p_data => x_msg_data);
305 if ec_debug.G_debug_level >= 2 then
306 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_PROCESS_RULES');
307 end if;
308
309 EXCEPTION
310 WHEN fnd_api.g_exc_error then
311 x_return_status := fnd_api.g_ret_sts_error;
312 fnd_msg_pub.count_and_get (p_count => x_msg_count,
313 p_data => x_msg_data);
314 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_PROCESS_RULES');
315
316 WHEN fnd_api.g_exc_unexpected_error then
317 x_return_status := fnd_api.g_ret_sts_error;
318 fnd_msg_pub.count_and_get (p_count => x_msg_count,
319 p_data => x_msg_data);
320 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_PROCESS_RULES');
321
322 WHEN no_process_rule_info then
323 if (c_rule_info%ISOPEN) then
324 close c_rule_info;
325 end if;
326 ec_debug.pl (0, 'EC', 'ECE_NO_PROCESS_RULE',
327 'TRANSACTION_TYPE', p_transaction_type,
328 'RULE_TYPE', l_rule_type);
329 x_return_status := fnd_api.g_ret_sts_error;
330 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_PROCESS_RULES');
331
332 WHEN OTHERS THEN
333 if (c_rule_info%ISOPEN) then
334 close c_rule_info;
335 end if;
336 if (c_ignore_flag%ISOPEN) then
337 close c_ignore_flag;
338 end if;
339 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
340 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
341 x_return_status := fnd_api.g_ret_sts_unexp_error;
342 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
343 fnd_msg_pub.add_exc_msg(g_file_name, g_pkg_name, l_api_name);
344 end if;
345 fnd_msg_pub.count_and_get (p_count => x_msg_count,
346 p_data => x_msg_data);
347 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_PROCESS_RULES');
348
349 END Validate_Process_Rules;
350
351
352 PROCEDURE Validate_Trading_Partner(
353 p_transaction_type IN VARCHAR2,
354 p_address_type IN VARCHAR2,
355 p_level IN NUMBER,
356 p_map_id IN NUMBER,
357 p_staging_tbl IN ec_utils.mapping_tbl,
358 x_tp_detail_id OUT NOCOPY NUMBER,
359 x_msg_text OUT NOCOPY VARCHAR2,
360 x_valid_rule OUT NOCOPY VARCHAR2) IS
361
362 xProgress VARCHAR2(80);
363 n_location_code_pos NUMBER;
364 n_translator_code_pos NUMBER;
365 l_location_code VARCHAR2(35);
366 l_translator_code ece_tp_details.translator_code%TYPE;
367 l_map_code ece_mappings.map_code%TYPE;
368 loop_count NUMBER:=0;
369 n_org_id NUMBER;
370 l_org_id NUMBER ;
371 l_edi_flag VARCHAR2(1);
372
373 /* bug 2151462: Modified the cursors to refer to base tables */
374
375 CURSOR c_cust_addr IS
376 select td.tp_detail_id,
377 nvl(td.edi_flag,'N')
378 from ece_tp_details td,
379 hz_cust_acct_sites_all ra
380 where td.translator_code = l_translator_code and
381 ra.ece_tp_location_code = l_location_code and
382 ra.tp_header_id = td.tp_header_id and
383 td.document_id = p_transaction_type and
384 td.map_id = p_map_id and
385 nvl(ra.org_id,-99) = nvl(l_org_id,nvl(ra.org_id,-99)) and
386 nvl(ra.status,'ZZ') = 'A'; -- fix for bug 6401982
387 --rownum = 1;
388
389 CURSOR c_supplier_addr IS
390 select td.tp_detail_id,
391 nvl(td.edi_flag,'N')
392 from ece_tp_details td,
393 po_vendor_sites_all pvs
394 where td.translator_code = l_translator_code and
395 pvs.ece_tp_location_code = l_location_code and
396 pvs.tp_header_id = td.tp_header_id and
397 td.document_id = p_transaction_type and
398 td.map_id = p_map_id and
399 nvl(pvs.org_id,-99) = nvl(l_org_id,nvl(pvs.org_id,-99));
400 --rownum = 1;
401
402 CURSOR c_bank_addr IS
403 select td.tp_detail_id,
404 nvl(td.edi_flag,'N')
405 from ece_tp_details td,
406 ce_bank_branches_v cbb,
407 hz_contact_points hcp
408 where td.translator_code = l_translator_code and
409 hcp.edi_ece_tp_location_code = l_location_code and
410 hcp.edi_tp_header_id = td.tp_header_id and
411 hcp.owner_table_id = cbb.branch_party_id and
412 hcp.owner_table_name = 'HZ_PARTIES' and
413 hcp.contact_point_type = 'EDI' and
414 td.document_id = p_transaction_type and
415 td.map_id = p_map_id ;
416 --rownum = 1;
417
418 CURSOR c_hr_addr IS
419 select td.tp_detail_id,
420 nvl(td.edi_flag,'N')
421 from ece_tp_details td,
422 hr_locations hrl
423 where td.translator_code = l_translator_code and
424 hrl.ece_tp_location_code = l_location_code and
425 hrl.tp_header_id = td.tp_header_id and
426 td.document_id = p_transaction_type and
427 td.map_id = p_map_id ;
428 --rownum = 1;
429
430 BEGIN
431 if ec_debug.G_debug_level >= 2 then
432 ec_debug.push ('ECE_RULES_PKG.VALIDATE_TRADING_PARTNER');
433 ec_debug.pl (3, 'p_transaction_type', p_transaction_type);
434 ec_debug.pl (3, 'p_address_type', p_address_type);
435 ec_debug.pl (3, 'p_level', p_level);
436 ec_debug.pl (3, 'p_map_id', p_map_id);
437 end if;
438
439 xProgress := 'ECERULEB-30-1000';
440 x_msg_text := NULL;
441 x_valid_rule := 'Y';
442
443 ec_utils.find_pos (
444 1,
445 'TP_LOCATION_CODE',
446 n_location_code_pos);
447
448 xProgress := 'ECERULEB-30-1010';
449 ec_utils.find_pos (
450 1,
451 'TP_TRANSLATOR_CODE',
452 n_translator_code_pos);
453
454 xProgress := 'ECERULEB-30-1010';
455 ec_utils.find_pos (
456 1,
457 'ORG_ID',
458 n_org_id);
459
460 --bug 2151462
461
462 xProgress := 'ECERULEB-30-1020';
463 l_location_code := p_staging_tbl (n_location_code_pos).value;
464 l_translator_code := p_staging_tbl (n_translator_code_pos).value;
465 translator_code := l_translator_code;
466 l_org_id := p_staging_tbl (n_org_id).value;
467
468 if ec_debug.G_debug_level =3 then
469 ec_debug.pl (3, 'translator_code', l_translator_code);
470 ec_debug.pl (3, 'location_code', l_location_code);
471 ec_debug.pl (3, 'org_id', l_org_id);
472 end if;
473
474 if (p_address_type = g_customer) then
475
476 xProgress := 'ECERULEB-30-1030';
477 open c_cust_addr;
478 loop
479 fetch c_cust_addr into x_tp_detail_id, l_edi_flag;
480 /*if c_cust_addr%NOTFOUND then
481 x_tp_detail_id := -1;
482 exit;
483 end if; */
484 exit when c_cust_addr%NOTFOUND;
485 loop_count := loop_count + 1;
486 end loop;
487 close c_cust_addr;
488
489 elsif (p_address_type = g_supplier) then
490 xProgress := 'ECERULEB-30-1040';
491 open c_supplier_addr;
492 loop
493 fetch c_supplier_addr into x_tp_detail_id, l_edi_flag;
494 /*if c_supplier_addr%NOTFOUND then
495 x_tp_detail_id := -1;
496 exit;
497 end if; */
498 exit when c_supplier_addr%NOTFOUND;
499 loop_count := loop_count + 1;
500 end loop;
501 close c_supplier_addr;
502
503 elsif (p_address_type = g_bank) then
504 xProgress := 'ECERULEB-30-1050';
505 open c_bank_addr;
506 loop
507 fetch c_bank_addr into x_tp_detail_id, l_edi_flag;
508 /*if c_bank_addr%NOTFOUND then
509 x_tp_detail_id := -1;
510 exit;
511 end if; */
512 exit when c_bank_addr%NOTFOUND;
513 loop_count := loop_count + 1;
514 end loop;
515 close c_bank_addr;
516
517 elsif (p_address_type = g_hr_location) then
518 xProgress := 'ECERULEB-30-1060';
519 open c_hr_addr;
520 loop
521 fetch c_hr_addr into x_tp_detail_id, l_edi_flag;
522 /*if c_hr_addr%NOTFOUND then
523 x_tp_detail_id := -1;
524 exit;
525 end if; */
526 exit when c_hr_addr%NOTFOUND;
527 loop_count := loop_count + 1;
528 end loop;
529 close c_hr_addr;
530
531 else
532 xProgress := 'ECERULEB-30-1070';
533 x_tp_detail_id := -1;
534 end if;
535
536 if ec_debug.G_debug_level =3 then
537 ec_debug.pl (3, 'loop_count', loop_count);
538 end if;
539
540 if (loop_count = 0) then
541 xProgress := 'ECERULEB-30-1080';
542 x_valid_rule := 'N';
543
544 /*select map_code into l_map_code
545 from ece_mappings
546 where map_id = p_map_id;
547 bug 2151462 */
548
549 fnd_message.set_name ('EC', 'ECE_TP_NOT_FOUND');
550 fnd_message.set_token ('TRANSLATOR_CODE', l_translator_code);
551 fnd_message.set_token ('LOCATION_CODE', l_location_code);
552 fnd_message.set_token ('TRANSACTION_TYPE', p_transaction_type);
553 fnd_message.set_token ('ORG_ID', to_char(l_org_id));
554 x_msg_text := fnd_message.get;
555 ec_debug.pl (0, x_msg_text);
556 elsif loop_count > 1 then --bug2151462
557
558 xProgress := 'ECERULEB-30-1090';
559 x_valid_rule := 'N';
560 fnd_message.set_name ('EC', 'ECE_MULTIPLE_TP_FOUND');
561 fnd_message.set_token ('TRANSLATOR_CODE', l_translator_code);
562 fnd_message.set_token ('LOCATION_CODE', l_location_code);
563 fnd_message.set_token ('TRANSACTION_TYPE', p_transaction_type);
564 fnd_message.set_token ('ORG_ID', to_char(l_org_id));
565 x_msg_text := fnd_message.get;
566 ec_debug.pl (0, x_msg_text);
567 elsif loop_count = 1 and l_edi_flag = 'N' then
568
569 xProgress := 'ECERULEB-30-1090';
570 x_valid_rule := 'N';
571 fnd_message.set_name ('EC', 'ECE_TP_NOT_ENABLED');
572 fnd_message.set_token ('TRANSLATOR_CODE', l_translator_code);
573 fnd_message.set_token ('LOCATION_CODE', l_location_code);
574 fnd_message.set_token ('TRANSACTION_TYPE', p_transaction_type);
575 fnd_message.set_token ('ORG_ID', to_char(l_org_id));
576 x_msg_text := fnd_message.get;
577 ec_debug.pl (0, x_msg_text);
578 end if;
579
580 if ec_debug.G_debug_level >= 2 then
581 ec_debug.pl (3, 'x_msg_text', x_msg_text);
582 ec_debug.pl (3, 'x_valid_rule', x_valid_rule);
583 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_TRADING_PARTNER');
584 end if;
585
586 EXCEPTION
587 WHEN OTHERS THEN
588 if (c_cust_addr%ISOPEN) then
589 close c_cust_addr;
590 end if;
591 if (c_supplier_addr%ISOPEN) then
592 close c_supplier_addr;
593 end if;
594 if (c_bank_addr%ISOPEN) then
595 close c_bank_addr;
596 end if;
597 if (c_hr_addr%ISOPEN) then
598 close c_hr_addr;
599 end if;
600
601 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
602 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
603 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_TRADING_PARTNER');
604 raise fnd_api.g_exc_unexpected_error;
605
606 END Validate_Trading_Partner;
607
608
609 PROCEDURE Validate_Test_Prod(
610 p_tp_detail_id IN NUMBER,
611 p_level IN NUMBER,
612 p_staging_tbl IN ec_utils.mapping_tbl,
613 x_msg_text OUT NOCOPY VARCHAR2,
614 x_valid_rule OUT NOCOPY VARCHAR2) IS
615
616 xProgress VARCHAR2(80);
617 n_test_flag_pos NUMBER;
618 l_file_test_flag ece_tp_details.test_flag%TYPE;
619 l_setup_test_flag ece_tp_details.test_flag%TYPE;
620 no_row_selected EXCEPTION;
621
622 CURSOR c_test_flag IS
623 select test_flag
624 from ece_tp_details
625 where tp_detail_id = p_tp_detail_id and
626 rownum = 1;
627
628 BEGIN
629 if ec_debug.G_debug_level >= 2 then
630 ec_debug.push ('ECE_RULES_PKG.VALIDATE_TEST_PROD');
631 ec_debug.pl (3, 'p_tp_detail_id', p_tp_detail_id);
632 ec_debug.pl (3, 'p_level', p_level);
633 end if;
634
635 xProgress := 'ECERULEB-40-1000';
636 x_msg_text := NULL;
637 x_valid_rule := 'Y';
638
639 ec_utils.find_pos (
640 1,
641 'TEST_INDICATOR',
642 n_test_flag_pos);
643
644 l_file_test_flag := p_staging_tbl (n_test_flag_pos).value;
645
646 if ec_debug.G_debug_level = 3 then
647 ec_debug.pl (3, 'file_test_flag', l_file_test_flag);
648 end if;
649
650 xProgress := 'ECERULEB-40-1010';
651 open c_test_flag;
652 fetch c_test_flag into l_setup_test_flag;
653 if c_test_flag%NOTFOUND then
654 raise no_row_selected;
655 end if;
656 close c_test_flag;
657
658 ec_debug.pl (3, 'setup_test_flag', l_setup_test_flag);
659
660 if (l_file_test_flag <> l_setup_test_flag) or
661 (l_file_test_flag is null) or
662 (l_setup_test_flag is null) then
663 xProgress := 'ECERULEB-40-1020';
664 x_valid_rule := 'N';
665 fnd_message.set_name ('EC', 'ECE_TEST_PROD');
666 x_msg_text := fnd_message.get;
667 ec_debug.pl (0, x_msg_text);
668 end if;
669
670 if ec_debug.G_debug_level >= 2 then
671 ec_debug.pl (3, 'x_msg_text', x_msg_text);
672 ec_debug.pl (3, 'x_valid_rule', x_valid_rule);
673 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_TEST_PROD');
674 end if;
675 EXCEPTION
676 WHEN no_row_selected then
677 if (c_test_flag%ISOPEN) then
678 close c_test_flag;
679 end if;
680 ec_debug.pl (0, 'EC', 'ECE_NO_ROW_SELECTED',
681 'PROGRESS_LEVEL', xProgress,
682 'INFO', 'TEST_FLAG',
683 'TABLE_NAME', 'ECE_TP_DETAILS');
684 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_TEST_PROD');
685 raise fnd_api.g_exc_error;
686
687 WHEN OTHERS THEN
688 if (c_test_flag%ISOPEN) then
689 close c_test_flag;
690 end if;
691 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
692 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
693 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_TEST_PROD');
694 raise fnd_api.g_exc_unexpected_error;
695
696 END Validate_Test_Prod;
697
698
699 PROCEDURE Validate_Column_Rules(
700 p_api_version_number IN NUMBER,
701 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
702 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
703 p_commit IN VARCHAR2 := FND_API.G_FALSE,
704 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
705 x_return_status OUT NOCOPY VARCHAR2,
706 x_msg_count OUT NOCOPY NUMBER,
707 x_msg_data OUT NOCOPY VARCHAR2,
708 p_transaction_type IN VARCHAR2,
709 p_stage_id IN NUMBER,
710 p_document_id IN NUMBER,
711 p_document_number IN VARCHAR2,
712 p_level IN NUMBER,
713 p_staging_tbl IN OUT NOCOPY ec_utils.mapping_tbl) IS
714
715 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Column_Rules';
716 l_api_version_number CONSTANT NUMBER := 1.0;
717
718 xProgress VARCHAR2(80);
719 l_interface_column_id NUMBER;
720 l_interface_column_name ece_interface_columns.interface_column_name%TYPE;
721 l_interface_column_value VARCHAR2(500);
722 l_interface_column_datatype ece_interface_columns.data_type%TYPE;
723 l_rule_type ece_column_rules.rule_type%TYPE;
724 l_rule_id NUMBER;
725 l_action_code ece_column_rules.action_code%TYPE;
726 l_valid_rule VARCHAR2(1):= 'Y';
727 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
728 l_violation_level VARCHAR2(10):= g_column_rule;
729 l_msg_text ece_rule_violations.message_text%TYPE;
730 l_existing_rule VARCHAR2(1) := 'Y';
731 l_temp_status VARCHAR2(10);
732 i pls_integer; -- Bug 2708573
733
734 /* Bug 2708573
735 CURSOR c_col_rule_info (
736 p_interface_column_id NUMBER) IS
737 select column_rule_id, rule_type, action_code
738 from ece_column_rules
739 where interface_column_id = p_interface_column_id
740 order by sequence;
741 */
742
743 /* CURSOR c_ignore_flag (
744 p_document_id NUMBER,
745 p_interface_column_id NUMBER,
746 p_stage_id NUMBER,
747 p_rule_id NUMBER) IS
748 select ignore_flag
749 from ece_rule_violations
750 where document_id = p_document_id and
751 interface_column_id = p_interface_column_id and
752 stage_id = p_stage_id and
753 rule_id = p_rule_id and
754 violation_level = l_violation_level; */
755
756 BEGIN
757 if ec_debug.G_debug_level >= 2 then
758 ec_debug.push ('ECE_RULES_PKG.VALIDATE_COLUMN_RULES');
759 ec_debug.pl (3, 'p_transaction_type', p_transaction_type);
760 ec_debug.pl (3, 'p_stage_id', p_stage_id);
761 ec_debug.pl (3, 'p_document_id', p_document_id);
762 ec_debug.pl (3, 'p_document_number', p_document_number);
763 ec_debug.pl (3, 'p_level', p_level);
764 end if;
765
766 -- Standard call to check for call compatibility.
767 if not fnd_api.compatible_api_call (l_api_version_number,
768 p_api_version_number, l_api_name,
769 g_pkg_name) then
770 raise fnd_api.g_exc_unexpected_error;
771 end if;
772
773 -- Initialize message list if p_init_msg_list is set to TRUE.
774 if fnd_api.to_boolean(p_init_msg_list) then
775 fnd_msg_pub.initialize;
776 end if;
777
778 -- Initialize API return status to success
779 x_return_status := fnd_api.g_ret_sts_success;
780
781 xProgress := 'ECERULEB-50-1000';
782
783 /* bug 2500898
784 for i in 1..p_staging_tbl.count loop
785 for i in ec_utils.g_ext_levels(p_level).file_start_pos..ec_utils.g_ext_levels(p_level).file_end_pos
786 */
787
788 -- Bug 2708573
789 -- Replaced the above for loop with while loop.
790 i:= ec_utils.g_column_rule_tbl.FIRST;
791 while i <> ec_utils.g_column_rule_tbl.LAST
792 loop
793
794 -- Bug 1853627 Added a check on the column_rule_flag in the following condition
795
796 -- Bug 2708573
797 -- if (p_staging_tbl(i).external_level = p_level AND p_staging_tbl(i).column_rule_flag ='Y') then
798
799 if (ec_utils.g_column_rule_tbl(i).level = p_level) then
800
801 xProgress := 'ECERULEB-50-1010';
802 l_interface_column_id := p_staging_tbl(i).interface_column_id;
803 l_interface_column_name := p_staging_tbl(i).interface_column_name;
804 l_interface_column_datatype := p_staging_tbl(i).data_type;
805
806 if ec_debug.G_debug_level = 3 then
807 ec_debug.pl (3, 'interface_column_id', l_interface_column_id);
808 ec_debug.pl (3, 'interface_column_name', l_interface_column_name);
809 ec_debug.pl (3, 'interface_column_datatype', l_interface_column_datatype);
810 end if;
811
812 /* Bug 2708573
813 open c_col_rule_info (l_interface_column_id);
814 loop
815
816 xProgress := 'ECERULEB-50-1030';
817 fetch c_col_rule_info into l_rule_id, l_rule_type, l_action_code;
818 exit when c_col_rule_info%NOTFOUND;
819 */
820
821 xProgress := 'ECERULEB-50-1020';
822 l_rule_id :=ec_utils.g_column_rule_tbl(i).column_rule_id;
823 l_rule_type :=ec_utils.g_column_rule_tbl(i).rule_type;
824 l_action_code:=ec_utils.g_column_rule_tbl(i).action_code;
825
826 if ec_debug.G_debug_level = 3 then
827 ec_debug.pl (3, 'rule_id', l_rule_id);
828 ec_debug.pl (3, 'rule_type', l_rule_type);
829 ec_debug.pl (3, 'action_code', l_action_code);
830 end if;
831
832 xProgress := 'ECERULEB-50-1040';
833 /* open c_ignore_flag (p_document_id, l_interface_column_id, p_stage_id, l_rule_id);
834
835 xProgress := 'ECERULEB-50-1050';
836 fetch c_ignore_flag into l_ignore_flag;
837 if c_ignore_flag%NOTFOUND then
838 l_ignore_flag := 'N';
839 end if;
840
841 if ec_debug.G_debug_level = 3 then
842 ec_debug.pl (3, 'ignore_flag', l_ignore_flag);
843 end if; */
844 l_ignore_flag := 'N';
845 for i in 1 .. ece_inbound.g_col_rule_viol_tbl.count
846 loop
847 if (ece_inbound.g_col_rule_viol_tbl(i).stage_id = p_stage_id) and
848 (ece_inbound.g_col_rule_viol_tbl(i).rule_id = l_rule_id) and
849 (ece_inbound.g_col_rule_viol_tbl(i).interface_col_id = l_interface_column_id)then
850 l_ignore_flag := 'Y';
851 exit;
852 end if;
853 end loop;
854
855
856
857
858 l_interface_column_value := p_staging_tbl(i).value;
859
860 if ec_debug.G_debug_level = 3 then
861 ec_debug.pl (3, 'interface_column_value', l_interface_column_value);
862 end if;
863
864 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
865 if (l_rule_type = g_c_value_required) then
866
867 xProgress := 'ECERULEB-50-1060';
868 Value_Required_Rule (l_interface_column_name,
869 l_interface_column_value, l_valid_rule,
870 l_msg_text);
871
872 elsif (l_rule_type = g_c_simple_lookup) then
873
874 xProgress := 'ECERULEB-50-1070';
875 Simple_Lookup_Rule (l_interface_column_name,
876 l_interface_column_value, l_rule_id,
877 l_valid_rule, l_msg_text);
878
879 elsif (l_rule_type = g_c_valueset) then
880
881 xProgress := 'ECERULEB-50-1080';
882 Valueset_Rule (l_interface_column_name,
883 l_interface_column_value, l_rule_id,
884 l_valid_rule, l_msg_text);
885
886 elsif (l_rule_type = g_c_null_dependency) then
887
888 xProgress := 'ECERULEB-50-1090';
889 Null_Dependency_Rule (l_interface_column_name,
890 l_interface_column_value, l_rule_id,
891 p_staging_tbl, p_level,
892 l_valid_rule, l_msg_text);
893
894 elsif (l_rule_type = g_c_predefined_list) then
895
896 xProgress := 'ECERULEB-50-1100';
897 Predefined_List_Rule (l_interface_column_name,
898 l_interface_column_value, l_rule_id,
899 l_valid_rule, l_msg_text);
900
901 elsif (l_rule_type = g_c_null_default) then
902
903 xProgress := 'ECERULEB-50-1110';
904 Null_Default_Rule (l_interface_column_name,
905 l_interface_column_value, l_rule_id,
906 p_level, p_staging_tbl,
907 l_valid_rule, l_msg_text);
908
909 elsif (l_rule_type = g_c_datatype_checking) then
910
911 xProgress := 'ECERULEB-50-1120';
912 Datatype_Checking_Rule (l_interface_column_datatype,
913 l_interface_column_name,
914 l_interface_column_value,
915 l_valid_rule, l_msg_text);
916
917 else
918
919 xProgress := 'ECERULEB-50-1130';
920 ec_debug.pl(0, 'EC', 'ECE_INVALID_RULE_TYPE',
921 'RULE_TYPE', l_rule_type);
922 l_existing_rule := 'N';
923 end if;
924
925 if (l_existing_rule = 'Y') then
926
927 xProgress := 'ECERULEB-50-1140';
928 Update_Status(p_transaction_type, p_level, l_valid_rule,
929 l_action_code, l_interface_column_id, l_rule_id,
930 p_stage_id, p_document_id, l_violation_level,
931 p_document_number, l_msg_text);
932
933 end if;
934
935 end if;
936
937 xProgress := 'ECERULEB-50-1150';
938 -- close c_ignore_flag;
939
940 /* Bug 2708573
941 end loop;
942 close c_col_rule_info;
943 */
944
945 end if;
946
947 xProgress := 'ECERULEB-50-1160';
948 i := ec_utils.g_column_rule_tbl.NEXT(i); -- Bug 2708573
949
950 end loop;
951
952 if (fnd_api.to_boolean(p_simulate)) then
953 null;
954 elsif (fnd_api.to_boolean(p_commit)) then
955 commit work;
956 end if;
957
958 -- Standard call to get message count and if count is 1, get message info.
959 fnd_msg_pub.count_and_get(p_count => x_msg_count,
960 p_data => x_msg_data);
961
962 if ec_debug.G_debug_level = 3 then
963 ec_debug.pl (3, 'x_return_status', x_return_status);
964 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_COLUMN_RULES');
965 end if;
966
967 EXCEPTION
968 WHEN fnd_api.g_exc_error then
969 /* Bug 2708573
970 if (c_col_rule_info%ISOPEN) then
971 close c_col_rule_info;
972 end if;
973 */
974 -- if (c_ignore_flag%ISOPEN) then
975 -- close c_ignore_flag;
976 -- end if;
977 x_return_status := fnd_api.g_ret_sts_error;
978 fnd_msg_pub.count_and_get (p_count => x_msg_count,
979 p_data => x_msg_data);
980 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_COLUMN_RULES');
981
982 WHEN fnd_api.g_exc_unexpected_error then
983 /* Bug 2708573
984 if (c_col_rule_info%ISOPEN) then
985 close c_col_rule_info;
986 end if;
987 */
988
989 /* if (c_ignore_flag%ISOPEN) then
990 close c_ignore_flag;
991 end if; */
992 x_return_status := fnd_api.g_ret_sts_error;
993 fnd_msg_pub.count_and_get (p_count => x_msg_count,
994 p_data => x_msg_data);
995 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_COLUMN_RULES');
996
997 WHEN OTHERS THEN
998 /* Bug 2708573
999 if (c_col_rule_info%ISOPEN) then
1000 close c_col_rule_info;
1001 end if;
1002 */
1003
1004 /* if (c_ignore_flag%ISOPEN) then
1005 close c_ignore_flag;
1006 end if; */
1007 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
1008 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1009 x_return_status := fnd_api.g_ret_sts_unexp_error;
1010 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
1011 fnd_msg_pub.add_exc_msg(g_file_name, g_pkg_name, l_api_name);
1012 end if;
1013 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1014 p_data => x_msg_data);
1015 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_COLUMN_RULES');
1016
1017 END Validate_Column_Rules;
1018
1019
1020 PROCEDURE Value_Required_Rule (
1021 p_column_name IN VARCHAR2,
1022 p_column_value IN VARCHAR2,
1023 x_valid_rule OUT NOCOPY VARCHAR2,
1024 x_msg_text OUT NOCOPY VARCHAR2) IS
1025
1026
1027 xProgress VARCHAR2(80);
1028
1029 BEGIN
1030 if ec_debug.G_debug_level >= 2 then
1031 ec_debug.push ('ECE_RULES_PKG.VALUE_REQUIRED_RULE');
1032 ec_debug.pl (3, 'p_column_name', p_column_name);
1033 ec_debug.pl (3, 'p_column_value', p_column_value);
1034 end if;
1035
1036 xProgress := 'ECERULEB-60-1000';
1037 x_msg_text := NULL;
1038 x_valid_rule := 'Y';
1039
1040 if (p_column_value is NULL) then
1041 xProgress := 'ECERULEB-60-1010';
1042 x_valid_rule := 'N';
1043 fnd_message.set_name ('EC', 'ECE_VALUE_REQUIRED');
1044 fnd_message.set_token ('COLUMN_NAME', p_column_name);
1045 x_msg_text := fnd_message.get;
1046 ec_debug.pl (0, x_msg_text);
1047 end if;
1048
1049 if ec_debug.G_debug_level >= 2 then
1050 ec_debug.pl (3, 'x_valid_rule', x_valid_rule);
1051 ec_debug.pl (3, 'x_msg_text', x_msg_text);
1052 ec_debug.pop ('ECE_RULES_PKG.VALUE_REQUIRED_RULE');
1053 end if;
1054
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
1058 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1059 ec_debug.pop ('ECE_RULES_PKG.VALUE_REQUIRED_RULE');
1060 raise fnd_api.g_exc_unexpected_error;
1061
1062 END Value_Required_Rule;
1063
1064
1065 PROCEDURE Simple_Lookup_Rule (
1066 p_column_name IN VARCHAR2,
1067 p_column_value IN VARCHAR2,
1068 p_rule_id IN NUMBER,
1069 x_valid_rule OUT NOCOPY VARCHAR2,
1070 x_msg_text OUT NOCOPY VARCHAR2) IS
1071
1072 xProgress VARCHAR2(80);
1073 l_column ece_rule_simple_lookup.lookup_column%TYPE;
1074 l_table ece_rule_simple_lookup.lookup_table%TYPE;
1075 l_where_clause ece_rule_simple_lookup.lookup_where_clause%TYPE;
1076 l_select VARCHAR2(32000);
1077 l_from VARCHAR2(32000);
1078 l_where VARCHAR2(32000);
1079 l_sel_c INTEGER;
1080 l_match_column NUMBER;
1081 l_dummy INTEGER;
1082
1083 CURSOR c_simple_lookup_rule IS
1084 select lookup_column, lookup_table, lookup_where_clause
1085 from ece_rule_simple_lookup
1086 where column_rule_id = p_rule_id;
1087
1088 BEGIN
1089
1090 if ec_debug.G_debug_level >= 2 then
1091 ec_debug.push ('ECE_RULES_PKG.SIMPLE_LOOKUP_RULE');
1092 ec_debug.pl (3, 'p_column_name', p_column_name);
1093 ec_debug.pl (3, 'p_column_value', p_column_value);
1094 ec_debug.pl (3, 'p_rule_id', p_rule_id);
1095 end if;
1096
1097 xProgress := 'ECERULEB-70-1000';
1098
1099 x_msg_text := NULL;
1100 x_valid_rule := 'Y';
1101 l_column := NULL;
1102 l_table := NULL;
1103 l_where_clause := NULL;
1104
1105 xProgress := 'ECERULEB-70-1010';
1106 open c_simple_lookup_rule;
1107 fetch c_simple_lookup_rule into l_column, l_table, l_where_clause;
1108 close c_simple_lookup_rule;
1109
1110 if ec_debug.G_debug_level = 3 then
1111 ec_debug.pl (3, 'lookup_table', l_table);
1112 ec_debug.pl (3, 'lookup_column', l_column);
1113 ec_debug.pl (3, 'lookup_where_clause', l_where_clause);
1114 end if;
1115
1116 xProgress := 'ECERULEB-70-1020';
1117 l_select := ' SELECT count(*)';
1118 l_from := ' FROM ' || l_table;
1119 l_where := ' WHERE ' || l_column || '= ' || ':l_p_column_value';
1120
1121 if (l_where_clause is not null) then
1122 xProgress := 'ECERULEB-70-1030';
1123 l_where := l_where || ' AND ' || l_where_clause;
1124 end if;
1125
1126 xProgress := 'ECERULEB-70-1040';
1127 l_select := l_select || l_from || l_where;
1128
1129 if ec_debug.G_debug_level = 3 then
1130 ec_debug.pl (3, l_select);
1131 end if;
1132
1133 xProgress := 'ECERULEB-70-1050';
1134 l_sel_c := dbms_sql.open_cursor;
1135
1136 BEGIN
1137 xProgress := 'ECERULEB-70-1060';
1138 dbms_sql.parse (l_sel_c, l_select, dbms_sql.native);
1139 EXCEPTION
1140 WHEN OTHERS then
1141 ece_error_handling_pvt.print_parse_error
1142 ( dbms_sql.last_error_position, l_select);
1143 raise;
1144 END;
1145
1146 xProgress := 'ECERULEB-70-1070';
1147 dbms_sql.define_column(l_sel_c, 1, l_match_column);
1148
1149 xProgress := 'ECERULEB-70-1075';
1150 dbms_sql.bind_variable(l_sel_c,'l_p_column_value',p_column_value);
1151
1152 xProgress := 'ECERULEB-70-1080';
1153 l_dummy := dbms_sql.execute(l_sel_c);
1154
1155 if (dbms_sql.fetch_rows(l_sel_c) > 0) then
1156 xProgress := 'ECERULEB-70-1090';
1157 dbms_sql.column_value (l_sel_c, 1, l_match_column);
1158 if (l_match_column = 0) then
1159 xProgress := 'ECERULEB-70-1100';
1160 x_valid_rule := 'N';
1161 end if;
1162 end if;
1163
1164 xProgress := 'ECERULEB-70-1110';
1165 dbms_sql.close_cursor (l_sel_c);
1166
1167 if (x_valid_rule = 'N') then
1168 xProgress := 'ECERULEB-70-1120';
1169 fnd_message.set_name ('EC', 'ECE_SIMPLE_LOOKUP');
1170 fnd_message.set_token ('COLUMN_NAME', p_column_name);
1171 fnd_message.set_token ('COLUMN_VALUE', p_column_value);
1172 fnd_message.set_token ('LOOKUP_SELECT', l_select);
1173 x_msg_text := fnd_message.get;
1174 ec_debug.pl (0, x_msg_text);
1175 end if;
1176
1177 if ec_debug.G_debug_level >= 2 then
1178 ec_debug.pl (3, 'x_valid_rule', x_valid_rule);
1179 ec_debug.pl (3, 'x_msg_text', x_msg_text);
1180 ec_debug.pop ('ECE_RULES_PKG.SIMPLE_LOOKUP_RULE');
1181 end if;
1182
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 if (c_simple_lookup_rule%ISOPEN) then
1186 close c_simple_lookup_rule;
1187 end if;
1188 if (dbms_sql.is_open(l_sel_c)) then
1189 dbms_sql.close_cursor (l_sel_c);
1190 end if;
1191 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
1192 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1193 ec_debug.pop ('ECE_RULES_PKG.SIMPLE_LOOKUP_RULE');
1194 raise fnd_api.g_exc_unexpected_error;
1195
1196 END Simple_Lookup_Rule;
1197
1198
1199 PROCEDURE Valueset_Rule (
1200 p_column_name IN VARCHAR2,
1201 p_column_value IN VARCHAR2,
1202 p_rule_id IN NUMBER,
1203 x_valid_rule OUT NOCOPY VARCHAR2,
1204 x_msg_text OUT NOCOPY VARCHAR2) IS
1205
1206 xProgress VARCHAR2(80);
1207 l_valueset_name ece_rule_valueset.valueset_name%TYPE;
1208 l_valueset_id NUMBER := -1;
1209 l_valueset fnd_vset.valueset_r;
1210 l_format fnd_vset.valueset_dr;
1211 l_rowcount NUMBER;
1212 l_value fnd_vset.value_dr;
1213
1214 found BOOLEAN;
1215 match BOOLEAN := false;
1216 no_value_set EXCEPTION;
1217
1218 CURSOR c_valueset IS
1219 select valueset_name
1220 from ece_rule_valueset
1221 where column_rule_id = p_rule_id;
1222
1223 CURSOR c_flex_valueset_id IS
1224 select flex_value_set_id
1225 from fnd_flex_value_sets
1226 where flex_value_set_name = l_valueset_name;
1227
1228 BEGIN
1229 if ec_debug.G_debug_level >= 2 then
1230 ec_debug.push ('ECE_RULES_PKG.VALUESET_RULE');
1231 ec_debug.pl (3, 'p_column_name', p_column_name);
1232 ec_debug.pl (3, 'p_column_value', p_column_value);
1233 ec_debug.pl (3, 'p_rule_id', p_rule_id);
1234 end if;
1235
1236 xProgress := 'ECERULEB-80-1000';
1237 x_msg_text := NULL;
1238 x_valid_rule := 'Y';
1239
1240 open c_valueset;
1241 fetch c_valueset into l_valueset_name;
1242 close c_valueset;
1243
1244 if ec_debug.G_debug_level = 3 then
1245 ec_debug.pl (3, 'valueset_name', l_valueset_name);
1246 end if;
1247
1248 xProgress := 'ECERULEB-80-1010';
1249 open c_flex_valueset_id;
1250
1251 xProgress := 'ECERULEB-80-1020';
1252 fetch c_flex_valueset_id into l_valueset_id;
1253 if ec_debug.G_debug_level = 3 then
1254 ec_debug.pl (3, 'valueset_id', l_valueset_id);
1255 end if;
1256
1257 if (c_flex_valueset_id%NOTFOUND) then
1258 raise no_value_set;
1259 end if;
1260
1261 xProgress := 'ECERULEB-80-1030';
1262 close c_flex_valueset_id;
1263
1264 xProgress := 'ECERULEB-80-1040';
1265 fnd_vset.get_valueset (l_valueset_id, l_valueset, l_format);
1266
1267 xProgress := 'ECERULEB-80-1050';
1268 fnd_vset.get_value_init (l_valueset, TRUE);
1269
1270 xProgress := 'ECERULEB-80-1060';
1271 fnd_vset.get_value (l_valueset, l_rowcount, found, l_value);
1272
1273 while (found) loop
1274 if ec_debug.G_debug_level = 3 then
1275 ec_debug.pl (3, 'valueset_value', l_value.value);
1276 end if;
1277
1278 if (p_column_value = l_value.value) then
1279 xProgress := 'ECERULEB-80-1070';
1280 match := true;
1281 exit;
1282 end if;
1283
1284 xProgress := 'ECERULEB-80-1080';
1285 fnd_vset.get_value (l_valueset, l_rowcount, found, l_value);
1286 end loop;
1287
1288 xProgress := 'ECERULEB-80-1090';
1289 if (match) then
1290 x_valid_rule := 'Y';
1291 ec_debug.pl (3, 'EC', 'ECE_VALUE_IN_VALUESET');
1292 else
1293 x_valid_rule := 'N';
1294 ec_debug.pl (3, 'EC', 'ECE_VALUE_NOT_IN_VALUESET');
1295 end if;
1296
1297 xProgress := 'ECERULEB-80-1100';
1298 fnd_vset.get_value_end (l_valueset);
1299
1300 if (x_valid_rule = 'N') then
1301 xProgress := 'ECERULEB-80-1110';
1302 fnd_message.set_name ('EC', 'ECE_VALUESET');
1303 fnd_message.set_token ('COLUMN_NAME', p_column_name);
1304 fnd_message.set_token ('COLUMN_VALUE', p_column_value);
1305 fnd_message.set_token ('VALUESET', l_valueset_name);
1306 x_msg_text := fnd_message.get;
1307 ec_debug.pl (0, x_msg_text);
1308 end if;
1309
1310 if ec_debug.G_debug_level >= 2 then
1311 ec_debug.pl (3, 'x_valid_rule', x_valid_rule);
1312 ec_debug.pl (3, 'x_msg_text', x_msg_text);
1313 ec_debug.pop ('ECE_RULES_PKG.VALUESET_RULE');
1314 end if;
1315
1316 EXCEPTION
1317 WHEN no_value_set then
1318 if (c_valueset%ISOPEN) then
1319 close c_valueset;
1320 end if;
1321 if (c_flex_valueset_id%ISOPEN) then
1322 close c_flex_valueset_id;
1323 end if;
1324 ec_debug.pl (0, 'EC', 'ECE_VALUESET_NOT_FOUND',
1325 'VALUESET', l_valueset_name);
1326 ec_debug.pop ('ECE_RULES_PKG.VALUESET_RULE');
1327 raise fnd_api.g_exc_error;
1328
1329 WHEN OTHERS THEN
1330 if (c_valueset%ISOPEN) then
1331 close c_valueset;
1332 end if;
1333 if (c_flex_valueset_id%ISOPEN) then
1334 close c_flex_valueset_id;
1335 end if;
1336 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
1337 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1338 ec_debug.pop ('ECE_RULES_PKG.VALUESET_RULE');
1339 raise fnd_api.g_exc_unexpected_error;
1340
1341 END Valueset_Rule;
1342
1343
1344 PROCEDURE Null_Dependency_Rule (
1345 p_column_name IN VARCHAR2,
1346 p_column_value IN VARCHAR2,
1347 p_rule_id IN NUMBER,
1348 p_staging_tbl IN ec_utils.mapping_tbl,
1349 p_level IN NUMBER,
1350 x_valid_rule OUT NOCOPY VARCHAR2,
1351 x_msg_text OUT NOCOPY VARCHAR2) IS
1352
1353 xProgress VARCHAR2(80);
1354 l_null_rule_id NUMBER;
1355 l_comp_code ece_rule_null_dep.comparison_code%TYPE;
1356 l_rule_column ece_rule_null_dep_details.interface_column%TYPE;
1357 l_column_pos NUMBER;
1358 l_detail_comp_code ece_rule_null_dep_details.comparison_code%TYPE;
1359 l_rule_value ece_rule_null_dep_details.value%TYPE;
1360 l_column_value VARCHAR2(500);
1361 condition BOOLEAN := true;
1362
1363 CURSOR c_null_dependency IS
1364 select null_dependency_rule_id, comparison_code
1365 from ece_rule_null_dep
1366 where column_rule_id = p_rule_id;
1367
1368 CURSOR c_null_dependency_detail IS
1369 select interface_column, comparison_code, value
1370 from ece_rule_null_dep_details
1371 where null_dependency_rule_id = l_null_rule_id
1372 order by null_dependency_detail_id;
1373
1374 BEGIN
1375 if ec_debug.G_debug_level >= 2 then
1376 ec_debug.push ('ECE_RULES_PKG.NULL_DEPENDENCY_RULE');
1377 ec_debug.pl (3, 'p_column_name', p_column_name);
1378 ec_debug.pl (3, 'p_column_value', p_column_value);
1379 ec_debug.pl (3, 'p_rule_id', p_rule_id);
1380 ec_debug.pl (3, 'p_level', p_level);
1381 end if;
1382
1383 xProgress := 'ECERULEB-90-1000';
1384 x_msg_text := NULL;
1385 x_valid_rule := 'Y';
1386
1387 open c_null_dependency;
1388 fetch c_null_dependency into l_null_rule_id, l_comp_code;
1389 close c_null_dependency;
1390
1391 if ec_debug.G_debug_level = 3 then
1392 ec_debug.pl (3, 'null_rule_id', l_null_rule_id);
1393 ec_debug.pl (3, 'comparison_code', l_comp_code);
1394 end if;
1395
1396
1397 xProgress := 'ECERULEB-90-1010';
1398 open c_null_dependency_detail;
1399 loop
1400 if (condition = true) then
1401 xProgress := 'ECERULEB-90-1020';
1402 fetch c_null_dependency_detail into l_rule_column, l_detail_comp_code,
1403 l_rule_value;
1404 exit when c_null_dependency_detail%NOTFOUND;
1405
1406 if ec_debug.G_debug_level = 3 then
1407 ec_debug.pl (3, 'rule_column', l_rule_column);
1408 ec_debug.pl (3, 'detail_comparison_code', l_detail_comp_code);
1409 ec_debug.pl (3, 'rule_value', l_rule_value);
1410 end if;
1411
1412 xProgress := 'ECERULEB-90-1030';
1413 ec_utils.find_pos (
1414 1,
1415 p_level,
1416 l_rule_column,
1417 l_column_pos);
1418
1419 xProgress := 'ECERULEB-90-1040';
1420 l_column_value := p_staging_tbl(l_column_pos).value;
1421
1422 if ec_debug.G_debug_level = 3 then
1423 ec_debug.pl (3, 'column_value', l_column_value);
1424 end if;
1425
1426 if ((l_detail_comp_code = 'IS_NULL') and (l_column_value is NULL)) then
1427 condition := true;
1428 elsif ((l_detail_comp_code = 'IS_NOT_NULL') and
1429 (l_column_value is not NULL)) then
1430 condition := true;
1431 elsif ((l_detail_comp_code = 'EQUAL') and
1432 (l_column_value = l_rule_value)) then
1433 condition := true;
1434 else
1435 condition := false;
1436 end if;
1437 if ec_debug.G_debug_level = 3 then
1438 ec_debug.pl (3, 'condition', condition);
1439 end if;
1440
1441 else
1442 xProgress := 'ECERULEB-90-1050';
1443 exit;
1444 end if;
1445 end loop;
1446
1447 xProgress := 'ECERULEB-90-1060';
1448 close c_null_dependency_detail;
1449
1450 if ec_debug.G_debug_level = 3 then
1451 ec_debug.pl (3, 'meet all conditions', condition);
1452 end if;
1453
1454 if (condition) then
1455
1456 xProgress := 'ECERULEB-90-1070';
1457 if ((l_comp_code = 'MUST_BE_NULL') and (p_column_value is NULL)) then
1458 xProgress := 'ECERULEB-90-1080';
1459 x_valid_rule := 'Y';
1460
1461 elsif ((l_comp_code = 'CANNOT_BE_NULL') and
1462 (p_column_value is not null)) then
1463 xProgress := 'ECERULEB-90-1090';
1464 x_valid_rule := 'Y';
1465
1466 else
1467 xProgress := 'ECERULEB-90-1100';
1468 x_valid_rule := 'N';
1469 fnd_message.set_name ('EC', 'ECE_NULL_DEPENDENCY');
1470 fnd_message.set_token ('COLUMN_NAME', p_column_name);
1471 fnd_message.set_token ('COMPARISON_CODE', l_comp_code);
1472 x_msg_text := fnd_message.get;
1473 ec_debug.pl (0, x_msg_text);
1474 end if;
1475
1476 end if;
1477
1478 if ec_debug.G_debug_level >= 2 then
1479 ec_debug.pl (3, 'x_valid_rule', x_valid_rule);
1480 ec_debug.pl (3, 'x_msg_text', x_msg_text);
1481 ec_debug.pop ('ECE_RULES_PKG.NULL_DEPENDENCY_RULE');
1482 end if;
1483
1484 EXCEPTION
1485 WHEN OTHERS THEN
1486 if (c_null_dependency%ISOPEN) then
1487 close c_null_dependency;
1488 end if;
1489 if (c_null_dependency_detail%ISOPEN) then
1490 close c_null_dependency_detail;
1491 end if;
1492 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
1493 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1494 ec_debug.pop ('ECE_RULES_PKG.NULL_DEPENDENCY_RULE');
1495 raise fnd_api.g_exc_unexpected_error;
1496
1497 END Null_Dependency_Rule;
1498
1499
1500 PROCEDURE Predefined_List_Rule (
1501 p_column_name IN VARCHAR2,
1502 p_column_value IN VARCHAR2,
1503 p_rule_id IN NUMBER,
1504 x_valid_rule OUT NOCOPY VARCHAR2,
1505 x_msg_text OUT NOCOPY VARCHAR2) IS
1506
1507 xProgress VARCHAR2(80);
1508 l_list_rule_id NUMBER;
1509 l_comp_code ece_rule_list.comparison_code%TYPE;
1510 l_match NUMBER;
1511
1512 CURSOR c_predefined_list IS
1513 select list_rule_id, comparison_code
1514 from ece_rule_list
1515 where column_rule_id = p_rule_id;
1516
1517 CURSOR c_list_count IS
1518 select count(*)
1519 from ece_rule_list_details
1520 where list_rule_id = l_list_rule_id and
1521 value = p_column_value;
1522
1523 BEGIN
1524 if ec_debug.G_debug_level >= 2 then
1525 ec_debug.push ('ECE_RULES_PKG.PREDEFINED_LIST_RULE');
1526 ec_debug.pl (3, 'p_column_name', p_column_name);
1527 ec_debug.pl (3, 'p_column_value', p_column_value);
1528 ec_debug.pl (3, 'p_rule_id', p_rule_id);
1529 end if;
1530
1531 xProgress := 'ECERULEB-100-1000';
1532 x_msg_text := NULL;
1533 x_valid_rule := 'Y';
1534
1535 open c_predefined_list;
1536 fetch c_predefined_list into l_list_rule_id, l_comp_code;
1537 close c_predefined_list;
1538
1539 if ec_debug.G_debug_level = 3 then
1540 ec_debug.pl (3, 'list_rule_id', l_list_rule_id);
1541 ec_debug.pl (3, 'comparison_code', l_comp_code);
1542 end if;
1543
1544 xProgress := 'ECERULEB-100-1000';
1545 open c_list_count;
1546 fetch c_list_count into l_match;
1547 close c_list_count;
1548
1549 if ec_debug.G_debug_level = 3 then
1550 ec_debug.pl (3, 'match', l_match);
1551 end if;
1552
1553 xProgress := 'ECERULEB-100-1010';
1554 if ((l_comp_code = 'MUST_EQUAL') and (l_match > 0)) then
1555 x_valid_rule := 'Y';
1556 elsif ((l_comp_code = 'CANNOT_EQUAL') and (l_match = 0)) then
1557 x_valid_rule := 'Y';
1558 else
1559 x_valid_rule := 'N';
1560 end if;
1561
1562 if (x_valid_rule = 'N') then
1563 xProgress := 'ECERULEB-100-1020';
1564 fnd_message.set_name ('EC', 'ECE_PREDEFINED_LIST');
1565 fnd_message.set_token ('COLUMN_NAME', p_column_name);
1566 fnd_message.set_token ('VALUE', p_column_value);
1567
1568 if (l_comp_code = 'MUST_EQUAL') then
1569 xProgress := 'ECERULEB-100-1030';
1570 fnd_message.set_token ('COMPARISON', 'MUST EXISTS');
1571
1572 elsif (l_comp_code = 'CANNOT_EQUAL') then
1573 xProgress := 'ECERULEB-100-1040';
1574 fnd_message.set_token ('COMPARISON', 'CANNOT EXISTS');
1575 end if;
1576 x_msg_text := fnd_message.get;
1577 ec_debug.pl (0, x_msg_text);
1578 end if;
1579
1580 if ec_debug.G_debug_level >= 2 then
1581 ec_debug.pl (3, 'x_valid_rule', x_valid_rule);
1582 ec_debug.pl (3, 'x_msg_text', x_msg_text);
1583 ec_debug.pop ('ECE_RULES_PKG.PREDEFINED_LIST_RULE');
1584 end if;
1585
1586 EXCEPTION
1587 WHEN OTHERS THEN
1588 if (c_predefined_list%ISOPEN) then
1589 close c_predefined_list;
1590 end if;
1591 if (c_list_count%ISOPEN) then
1592 close c_list_count;
1593 end if;
1594 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
1595 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1596 ec_debug.pop ('ECE_RULES_PKG.PREDEFINED_LIST_RULE');
1597 raise fnd_api.g_exc_unexpected_error;
1598
1599 END Predefined_List_Rule;
1600
1601
1602 PROCEDURE Null_Default_Rule (
1603 p_column_name IN VARCHAR2,
1604 p_column_value IN VARCHAR2,
1605 p_rule_id IN NUMBER,
1606 p_level IN NUMBER,
1607 p_staging_tbl IN OUT NOCOPY ec_utils.mapping_tbl,
1608 x_valid_rule OUT NOCOPY VARCHAR2,
1609 x_msg_text OUT NOCOPY VARCHAR2) IS
1610
1611 xProgress VARCHAR2(80);
1612 l_list_rule_id NUMBER;
1613 n_column_pos NUMBER;
1614 n_rule_column_pos NUMBER;
1615 l_type_code ece_rule_null_default.default_type_code%TYPE;
1616 l_value_column_name ece_rule_null_default.value_column_name%TYPE;
1617 l_column_value VARCHAR2(500);
1618
1619 CURSOR c_null_default IS
1620 select default_type_code, value_column_name
1621 from ece_rule_null_default
1622 where column_rule_id = p_rule_id;
1623
1624 BEGIN
1625 if ec_debug.G_debug_level >= 2 then
1626 ec_debug.push ('ECE_RULES_PKG.NULL_DEFAULT_RULE');
1627 ec_debug.pl (3, 'p_column_name', p_column_name);
1628 ec_debug.pl (3, 'p_column_value', p_column_value);
1629 ec_debug.pl (3, 'p_rule_id', p_rule_id);
1630 ec_debug.pl (3, 'p_level', p_level);
1631 end if;
1632
1633 xProgress := 'ECERULEB-110-1000';
1634 x_msg_text := NULL;
1635 x_valid_rule := 'Y';
1636
1637 if (p_column_value is NULL) then
1638
1639 xProgress := 'ECERULEB-110-1010';
1640 open c_null_default;
1641 fetch c_null_default into l_type_code, l_value_column_name;
1642 close c_null_default;
1643
1644 if ec_debug.G_debug_level = 3 then
1645 ec_debug.pl (3, 'type_code', l_type_code);
1646 end if;
1647
1648 if (l_type_code = 'LITERAL') then
1649 xProgress := 'ECERULEB-110-1020';
1650 l_column_value := l_value_column_name;
1651
1652 if ec_debug.G_debug_level = 3 then
1653 ec_debug.pl (3, 'value', l_column_value);
1654 end if;
1655
1656 elsif (l_type_code = 'COLUMN') then
1657 xProgress := 'ECERULEB-110-1030';
1658 ec_utils.find_pos (
1659 1,
1660 p_level,
1661 l_value_column_name,
1662 n_rule_column_pos);
1663 l_column_value := p_staging_tbl(n_rule_column_pos).value;
1664
1665 ec_debug.pl (3, 'column_name', l_value_column_name);
1666 ec_debug.pl (3, 'column_value', l_column_value);
1667 end if;
1668
1669 xProgress := 'ECERULEB-110-1040';
1670 ec_utils.find_pos (
1671 p_level,
1672 p_column_name,
1673 n_column_pos);
1674
1675 xProgress := 'ECERULEB-110-1050';
1676 p_staging_tbl(n_column_pos).value := l_column_value;
1677
1678 if ec_debug.G_debug_level = 3 then
1679 ec_debug.pl (3, 'Column Position', n_column_pos);
1680 ec_debug.pl (3, 'Updated Column Value', p_staging_tbl(n_column_pos).value);
1681 end if;
1682
1683 xProgress := 'ECERULEB-110-1060';
1684 x_valid_rule := 'N';
1685 fnd_message.set_name ('EC', 'ECE_NULL_DEFAULT');
1686 fnd_message.set_token ('COLUMN_NAME', p_column_name);
1687 fnd_message.set_token ('VALUE', l_column_value);
1688 x_msg_text := fnd_message.get;
1689 ec_debug.pl (0, x_msg_text);
1690
1691 end if;
1692
1693 if ec_debug.G_debug_level >= 2 then
1694 ec_debug.pl (3, 'x_valid_rule', x_valid_rule);
1695 ec_debug.pl (3, 'x_msg_text', x_msg_text);
1696 ec_debug.pop ('ECE_RULES_PKG.NULL_DEFAULT_RULE');
1697 end if;
1698
1699 EXCEPTION
1700 WHEN OTHERS THEN
1701 if (c_null_default%ISOPEN) then
1702 close c_null_default;
1703 end if;
1704 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
1705 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1706 ec_debug.pop ('ECE_RULES_PKG.NULL_DEFAULT_RULE');
1707 raise fnd_api.g_exc_unexpected_error;
1708
1709 END Null_Default_Rule;
1710
1711
1712 PROCEDURE Datatype_Checking_Rule (
1713 p_column_datatype IN VARCHAR2,
1714 p_column_name IN VARCHAR2,
1715 p_column_value IN VARCHAR2,
1716 x_valid_rule OUT NOCOPY VARCHAR2,
1717 x_msg_text OUT NOCOPY VARCHAR2) IS
1718
1719 xProgress VARCHAR2(80);
1720 l_temp_num NUMBER;
1721 l_temp_date DATE;
1722
1723 BEGIN
1724 if ec_debug.G_debug_level >= 2 then
1725 ec_debug.push ('ECE_RULES_PKG.DATATYPE_CHECKING_RULE');
1726 ec_debug.pl (3, 'p_column_datatype', p_column_datatype);
1727 ec_debug.pl (3, 'p_column_name', p_column_name);
1728 ec_debug.pl (3, 'p_column_value', p_column_value);
1729 end if;
1730
1731 xProgress := 'ECERULEB-120-1000';
1732 x_msg_text := NULL;
1733 x_valid_rule := 'Y';
1734
1735 BEGIN
1736 if (p_column_datatype = 'NUMBER') then
1737 xProgress := 'ECERULEB-120-1010';
1738 l_temp_num := TO_NUMBER(p_column_value);
1739
1740 elsif (p_column_datatype = 'DATE') then
1741 xProgress := 'ECERULEB-120-1020';
1742 l_temp_date := TO_DATE(p_column_value , 'YYYYMMDD HH24MISS');
1743
1744 else
1745 xProgress := 'ECERULEB-120-1030';
1746 x_valid_rule := 'Y';
1747 end if;
1748
1749 EXCEPTION
1750 WHEN others then
1751 x_valid_rule := 'N';
1752 END;
1753
1754 if (x_valid_rule = 'N') then
1755 xProgress := 'ECERULEB-120-1040';
1756 fnd_message.set_name ('EC', 'ECE_DATATYPE_CHECKING');
1757 fnd_message.set_token ('COLUMN_NAME', p_column_name);
1758 fnd_message.set_token ('VALUE', p_column_value);
1759 fnd_message.set_token ('DATATYPE', p_column_datatype);
1760 x_msg_text := fnd_message.get;
1761 ec_debug.pl (0, x_msg_text);
1762 end if;
1763
1764 if ec_debug.G_debug_level >= 2 then
1765 ec_debug.pl (3, 'x_valid_rule', x_valid_rule);
1766 ec_debug.pl (3, 'x_msg_text', x_msg_text);
1767 ec_debug.pop ('ECE_RULES_PKG.DATATYPE_CHECKING_RULE');
1768 end if;
1769
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
1773 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1774 ec_debug.pop ('ECE_RULES_PKG.DATATYPE_CHECKING_RULE');
1775 raise fnd_api.g_exc_unexpected_error;
1776
1777 END Datatype_Checking_Rule;
1778
1779
1780 PROCEDURE Validate_Get_Address_Info (
1781 p_entity_id_pos IN NUMBER,
1782 p_org_id_pos IN NUMBER,
1783 p_addr_id_pos IN NUMBER,
1784 p_tp_location_code_pos IN NUMBER,
1785 p_tp_translator_code_pos IN NUMBER,
1786 p_tp_location_name_pos IN NUMBER,
1787 p_addr1_pos IN NUMBER,
1788 p_addr2_pos IN NUMBER,
1789 p_addr3_pos IN NUMBER,
1790 p_addr4_pos IN NUMBER,
1791 p_addr_alt_pos IN NUMBER,
1792 p_city_pos IN NUMBER,
1793 p_county_pos IN NUMBER,
1794 p_state_pos IN NUMBER,
1795 p_zip_pos IN NUMBER,
1796 p_province_pos IN NUMBER,
1797 p_country_pos IN NUMBER,
1798 p_region1_pos IN NUMBER DEFAULT NULL,
1799 p_region2_pos IN NUMBER DEFAULT NULL,
1800 p_region3_pos IN NUMBER DEFAULT NULL,
1801 p_address IN VARCHAR2) IS
1802
1803 xProgress VARCHAR2(80);
1804 l_entity_id NUMBER := NULL;
1805 l_address_type NUMBER;
1806 l_msg_count NUMBER;
1807 l_msg_data VARCHAR2(80);
1808 l_status_code NUMBER;
1809 l_org_id NUMBER := NULL;
1810 l_addr_id NUMBER := NULL;
1811 l_tp_location_code VARCHAR2(3200) := NULL;
1812 l_tp_translator_code VARCHAR2(3200) := NULL;
1813 l_tp_location_name VARCHAR2(3200) := NULL;
1814 l_addr1 VARCHAR2(3200) := NULL;
1815 l_addr2 VARCHAR2(3200) := NULL;
1816 l_addr3 VARCHAR2(3200) := NULL;
1817 l_addr4 VARCHAR2(3200) := NULL;
1818 l_addr_alt VARCHAR2(3200) := NULL;
1819 l_city VARCHAR2(3200) := NULL;
1820 l_county VARCHAR2(3200) := NULL;
1821 l_state VARCHAR2(3200) := NULL;
1822 l_zip VARCHAR2(3200) := NULL;
1823 l_province VARCHAR2(3200) := NULL;
1824 l_country VARCHAR2(3200) := NULL;
1825 l_region1 VARCHAR2(3200) := NULL;
1826 l_region2 VARCHAR2(3200) := NULL;
1827 l_region3 VARCHAR2(3200) := NULL;
1828 x_entity_id VARCHAR2(3200) := NULL;
1829 x_org_id VARCHAR2(3200) := NULL;
1830 x_addr_id VARCHAR2(3200) := NULL;
1831 x_tp_location_code VARCHAR2(3200) := NULL;
1832 x_tp_translator_code VARCHAR2(3200) := NULL;
1833 x_tp_location_name VARCHAR2(3200) := NULL;
1834 x_addr1 VARCHAR2(3200) := NULL;
1835 x_addr2 VARCHAR2(3200) := NULL;
1836 x_addr3 VARCHAR2(3200) := NULL;
1837 x_addr4 VARCHAR2(3200) := NULL;
1838 x_addr_alt VARCHAR2(3200) := NULL;
1839 x_city VARCHAR2(3200) := NULL;
1840 x_county VARCHAR2(3200) := NULL;
1841 x_state VARCHAR2(3200) := NULL;
1842 x_zip VARCHAR2(3200) := NULL;
1843 x_province VARCHAR2(3200) := NULL;
1844 x_country VARCHAR2(3200) := NULL;
1845 x_region1 VARCHAR2(3200) := NULL;
1846 x_region2 VARCHAR2(3200) := NULL;
1847 x_region3 VARCHAR2(3200) := NULL;
1848 l_msg_text VARCHAR2(2000):= NULL;
1849 l_valid_rule VARCHAR2(1):= 'Y';
1850 l_rule_type VARCHAR2(80):= g_p_invalid_addr;
1851 l_transaction_type VARCHAR2(40);
1852 l_map_id NUMBER;
1853 l_stage_id NUMBER(15);
1854 l_document_id NUMBER(15);
1855 l_document_number ece_stage.document_number%TYPE;
1856 l_rule_id NUMBER;
1857 l_action_code ece_process_rules.action_code%TYPE;
1858 l_return_status VARCHAR2(20);
1859 var_exists BOOLEAN := FALSE;
1860 l_stack_pos NUMBER;
1861 l_plsql_pos NUMBER;
1862 stack_variable_not_found EXCEPTION;
1863 no_addr_rule_info EXCEPTION;
1864 addr_unexp_error EXCEPTION;
1865 l_found_on_tbl VARCHAR2(1):= 'N'; --Bug 2617428
1866 j pls_integer;
1867 no_of_searches pls_integer :=1;
1868 v_precedence_code VARCHAR2(240);
1869 v_profile_name VARCHAR2(80);
1870 v_pcode VARCHAR2(10);
1871
1872 CURSOR c_addr_rule IS
1873 select process_rule_id, action_code
1874 from ece_process_rules
1875 where transaction_type = l_transaction_type and
1876 map_id = l_map_id and
1877 rule_type = l_rule_type;
1878
1879 BEGIN
1880 /* Bug 2151462 - commented out the debug messages and code that is not reqd. for
1881 ** Address derivation
1882 */
1883 if ec_debug.G_debug_level >= 2 then
1884 ec_debug.push ('ECE_RULES_PKG.VALIDATE_GET_ADDRESS_INFO');
1885
1886 xProgress := 'ECERULEB-130-1000';
1887
1888 ec_debug.pl (3,'p_org_id_pos', p_org_id_pos);
1889 ec_debug.pl (3,'p_addr_id_pos', p_addr_id_pos);
1890 ec_debug.pl (3,'p_tp_location_code_pos', p_tp_location_code_pos);
1891 ec_debug.pl (3,'p_tp_translator_code_pos', p_tp_translator_code_pos);
1892 ec_debug.pl (3,'p_tp_location_name_pos', p_tp_location_name_pos);
1893 ec_debug.pl (3,'p_addr1_pos', p_addr1_pos);
1894 ec_debug.pl (3,'p_addr2_pos', p_addr2_pos);
1895 ec_debug.pl (3,'p_addr3_pos', p_addr3_pos);
1896 ec_debug.pl (3,'p_addr4_pos', p_addr4_pos);
1897 ec_debug.pl (3,'p_addr_alt_pos', p_addr_alt_pos);
1898 ec_debug.pl (3,'p_city_pos', p_city_pos);
1899 ec_debug.pl (3,'p_county_pos', p_county_pos);
1900 ec_debug.pl (3,'p_state_pos', p_state_pos);
1901 ec_debug.pl (3,'p_zip_pos', p_zip_pos);
1902 ec_debug.pl (3,'p_province_pos', p_province_pos);
1903 ec_debug.pl (3,'p_country_pos', p_country_pos);
1904 ec_debug.pl (3,'p_region1_pos', p_region1_pos);
1905 ec_debug.pl (3,'p_region2_pos', p_region2_pos);
1906 ec_debug.pl (3,'p_region3_pos', p_region3_pos);
1907 end if;
1908 ECE_RULES_PKG.G_PARTY_NAME := null;
1909 ECE_RULES_PKG.G_PARTY_NUMBER := null;
1910 -- get address information if it exists in the pl/sql table.
1911 if (p_org_id_pos is not null) then
1912 xProgress := 'ECERULEB-130-1010';
1913 l_org_id := ec_utils.g_file_tbl (p_org_id_pos).value;
1914 end if;
1915
1916 if (p_addr_id_pos is not null) then
1917 xProgress := 'ECERULEB-130-1020';
1918 l_addr_id := ec_utils.g_file_tbl (p_addr_id_pos).value;
1919 end if;
1920
1921 if (p_tp_location_code_pos is not null) then
1922 xProgress := 'ECERULEB-130-1030';
1923 l_tp_location_code := ec_utils.g_file_tbl (p_tp_location_code_pos).value;
1924 end if;
1925
1926 /*if (p_tp_translator_code_pos is not null) then
1927 xProgress := 'ECERULEB-130-1040';
1928 l_tp_translator_code := ec_utils.g_file_tbl (p_tp_translator_code_pos).value;
1929 end if;
1930
1931 IF (p_tp_location_name_pos IS NOT NULL) THEN
1932 xProgress := 'ECERULEB-130-1045';
1933 l_tp_location_name := ec_utils.g_file_tbl(p_tp_location_name_pos).value;
1934 END IF;
1935 bug2151462*/
1936
1937 if (p_tp_translator_code_pos is not null) then
1938 xProgress := 'ECERULEB-130-1040';
1939 ECE_RULES_PKG.G_PARTY_NUMBER := ec_utils.g_file_tbl (p_tp_translator_code_pos).value;
1940 end if;
1941
1942 IF (p_tp_location_name_pos IS NOT NULL) THEN
1943 xProgress := 'ECERULEB-130-1045';
1944 ECE_RULES_PKG.G_PARTY_NAME := ec_utils.g_file_tbl(p_tp_location_name_pos).value;
1945 END IF;
1946
1947 if (p_addr1_pos is not null) then
1948 xProgress := 'ECERULEB-130-1050';
1949 l_addr1 := ec_utils.g_file_tbl (p_addr1_pos).value;
1950 end if;
1951
1952 if (p_addr2_pos is not null) then
1953 xProgress := 'ECERULEB-130-1060';
1954 l_addr2 := ec_utils.g_file_tbl (p_addr2_pos).value;
1955 end if;
1956
1957 if (p_addr3_pos is not null) then
1958 xProgress := 'ECERULEB-130-1070';
1959 l_addr3 := ec_utils.g_file_tbl (p_addr3_pos).value;
1960 end if;
1961
1962 if (p_addr4_pos is not null) then
1963 xProgress := 'ECERULEB-130-1080';
1964 l_addr4 := ec_utils.g_file_tbl (p_addr4_pos).value;
1965 end if;
1966
1967 if (p_city_pos is not null) then
1968 xProgress := 'ECERULEB-130-1090';
1969 l_city := ec_utils.g_file_tbl (p_city_pos).value;
1970 end if;
1971
1972 /* if (p_county_pos is not null) then
1973 xProgress := 'ECERULEB-130-1100';
1974 l_county := ec_utils.g_file_tbl (p_county_pos).value;
1975 end if;
1976
1977 if (p_state_pos is not null) then
1978 xProgress := 'ECERULEB-130-1110';
1979 l_state := ec_utils.g_file_tbl (p_state_pos).value;
1980 end if;
1981 bug2151462 */
1982
1983 if (p_zip_pos is not null) then
1984 xProgress := 'ECERULEB-130-1120';
1985 l_zip := ec_utils.g_file_tbl (p_zip_pos).value;
1986 end if;
1987
1988 /* if (p_country_pos is not null) then
1989 xProgress := 'ECERULEB-130-1130';
1990 l_country := ec_utils.g_file_tbl (p_country_pos).value;
1991 end if;
1992
1993 if (p_region1_pos is not null) then
1994 xProgress := 'ECERULEB-130-1140';
1995 l_region1 := ec_utils.g_file_tbl (p_region1_pos).value;
1996 end if;
1997
1998 if (p_region2_pos is not null) then
1999 xProgress := 'ECERULEB-130-1150';
2000 l_region2 := ec_utils.g_file_tbl (p_region2_pos).value;
2001 end if;
2002
2003 if (p_region3_pos is not null) then
2004 xProgress := 'ECERULEB-130-1160';
2005 l_region3 := ec_utils.g_file_tbl (p_region3_pos).value;
2006 end if;
2007 bug2151462 */
2008
2009 xProgress := 'ECERULEB-130-1170';
2010 var_exists := ec_utils.find_variable(0, 'I_ADDRESS_TYPE',
2011 l_stack_pos, l_plsql_pos);
2012
2013 -- get the address type and transaction type information.
2014 if NOT (var_exists) then
2015 xProgress := 'ECERULEB-130-1180';
2016 raise stack_variable_not_found;
2017 else
2018 xProgress := 'ECERULEB-130-1190';
2019 ec_debug.pl(3,'Address Type',ec_utils.g_stack(l_stack_pos).variable_value);
2020 select DECODE (ec_utils.g_stack(l_stack_pos).variable_value,
2021 g_bank, ece_trading_partners_pub.g_bank,
2022 g_customer, ece_trading_partners_pub.g_customer,
2023 g_supplier, ece_trading_partners_pub.g_supplier,
2024 g_hr_location, ece_trading_partners_pub.g_hr_location,
2025 NULL) into l_address_type from dual;
2026 end if;
2027
2028 xProgress := 'ECERULEB-130-1200';
2029 l_transaction_type := ec_utils.g_transaction_type;
2030 l_map_id := ec_utils.g_map_id;
2031
2032 -- print out the address value before address derivation.
2033 xProgress := 'ECERULEB-130-1210';
2034 if ec_debug.G_debug_level = 3 then
2035 ec_debug.pl (3, 'Input to Address Derivation');
2036 ec_debug.pl (3, 'l_return_status', l_return_status);
2037 ec_debug.pl (3, 'l_msg_count', l_msg_count);
2038 ec_debug.pl (3, 'l_msg_data', l_msg_data);
2039 ec_debug.pl (3, 'l_status_code', l_status_code);
2040 ec_debug.pl (3, 'l_address_type', l_address_type);
2041 ec_debug.pl (3, 'l_transaction_type', l_transaction_type);
2042 ec_debug.pl (3, 'l_tp_translator_code', translator_code);
2043 ec_debug.pl (3, 'l_org_id', l_org_id);
2044 ec_debug.pl (3, 'l_addr_id', l_addr_id);
2045 ec_debug.pl (3, 'l_tp_location_code', l_tp_location_code);
2046 ec_debug.pl (3, 'l_addr1', l_addr1);
2047 ec_debug.pl (3, 'l_addr2', l_addr2);
2048 ec_debug.pl (3, 'l_addr3', l_addr3);
2049 ec_debug.pl (3, 'l_addr4', l_addr4);
2050 ec_debug.pl (3, 'l_addr_alt', l_addr_alt);
2051 ec_debug.pl (3, 'l_city', l_city);
2052 ec_debug.pl (3, 'l_zip', l_zip);
2053 ec_debug.pl (3, 'party_name', ece_rules_pkg.g_party_name);
2054 ec_debug.pl (3, 'party_number', ece_rules_pkg.g_party_number);
2055 end if;
2056
2057 /* hgandiko1
2058 ec_debug.pl (3, 'l_tp_translator_code', l_tp_translator_code);
2059 ec_debug.pl (3, 'l_tp_location_name', l_tp_location_name);
2060 ec_debug.pl (3, 'l_county', l_county);
2061 ec_debug.pl (3, 'l_state', l_state);
2062 ec_debug.pl (3, 'l_zip', l_zip);
2063 ec_debug.pl (3, 'l_province', l_province);
2064 ec_debug.pl (3, 'l_country', l_country);
2065 ec_debug.pl (3, 'l_region1', l_region1);
2066 ec_debug.pl (3, 'l_region2', l_region2);
2067 ec_debug.pl (3, 'l_region3', l_region3);
2068 Bug 2617428
2069 */
2070 v_profile_name := 'ECE_' || NVL(LTRIM(RTRIM(UPPER(NVL(l_transaction_type,'NULL VALUE')))),'') || '_ADDRESS_PRECEDENCE';
2071 fnd_profile.get(v_profile_name,v_precedence_code);
2072 v_pcode := NVL(v_precedence_code,'0');
2073 xProgress := 'ECERULEB-130-1211';
2074
2075 IF g_address_tbl.COUNT<>0 then
2076 IF v_pcode = '0' then
2077 for k in 1..g_address_tbl.COUNT
2078 loop
2079 IF (upper(g_address_tbl(k).address_type) =upper(l_address_type)) THEN
2080 IF (nvl(upper(g_address_tbl(k).tp_location_code),' ') = nvl(upper(l_tp_location_code),' ')) AND
2081 (nvl(upper(g_address_tbl(k).address_line1),' ')= nvl(UPPER(l_addr1),' ')) AND
2082 (nvl(upper(g_address_tbl(k).address_line2),' ')= nvl(UPPER(l_addr2),' ')) AND
2083 (nvl(upper(g_address_tbl(k).address_line3),' ')= nvl(UPPER(l_addr3),' ')) AND
2084 (nvl(upper(g_address_tbl(k).city),' ')= nvl(UPPER(l_city),' ')) AND
2085 (nvl(upper(g_address_tbl(k).zip),' ')= nvl(UPPER(l_zip),' ')) THEN
2086
2087 if (l_org_id is null) OR (nvl(l_org_id,-99) = g_address_tbl(k).org_id) --3608171
2088 then
2089 l_address_type :=g_address_tbl(k).address_type;
2090 x_org_id :=g_address_tbl(k).org_id;
2091 x_addr_id :=g_address_tbl(k).address_id;
2092 x_tp_translator_code:=g_address_tbl(k).address_code;
2093 x_entity_id :=g_address_tbl(k).parent_id;
2094 x_tp_location_code :=g_address_tbl(k).tp_location_code;
2095 x_tp_location_name :=g_address_tbl(k).tp_location_name;
2096 x_addr1 :=g_address_tbl(k).address_line1;
2097 x_addr2 :=g_address_tbl(k).address_line2;
2098 x_addr3 :=g_address_tbl(k).address_line3;
2099 x_addr4 :=g_address_tbl(k).address_line4;
2100 x_addr_alt :=g_address_tbl(k).address_line_alt;
2101 x_city :=g_address_tbl(k).city;
2102 x_county :=g_address_tbl(k).county;
2103 x_state :=g_address_tbl(k).state;
2104 x_zip :=g_address_tbl(k).zip;
2105 x_province :=g_address_tbl(k).province;
2106 x_country :=g_address_tbl(k).country;
2107 x_region1 :=g_address_tbl(k).region_1;
2108 x_region2 :=g_address_tbl(k).region_2;
2109 x_region3 :=g_address_tbl(k).region_3;
2110 l_return_status := fnd_api.G_RET_STS_SUCCESS;
2111 l_status_code := 0;
2112 l_found_on_tbl := 'Y';
2113 exit;
2114 END if;
2115 END IF;
2116 END IF;
2117 END LOOP;
2118 ELSIF v_pcode = '2' THEN
2119 for k in 1..g_address_tbl.COUNT
2120 loop
2121 IF (upper(g_address_tbl(k).address_type) =upper(l_address_type)) THEN
2122 IF (nvl(upper(g_address_tbl(k).tp_location_code),' ') = nvl(upper(l_tp_location_code),' ')) AND
2123 (nvl(upper(g_address_tbl(k).address_line1),' ')= nvl(UPPER(l_addr1),' ')) AND
2124 (nvl(upper(g_address_tbl(k).address_line2),' ')= nvl(UPPER(l_addr2),' ')) AND
2125 (nvl(upper(g_address_tbl(k).address_line3),' ')= nvl(UPPER(l_addr3),' ')) AND
2126 (nvl(upper(g_address_tbl(k).city),' ')= nvl(UPPER(l_city),' ')) AND
2127 (nvl(upper(g_address_tbl(k).zip),' ')= nvl(UPPER(l_zip),' ')) AND
2128 (nvl(upper(g_address_tbl(k).tp_location_name),' ')= nvl(UPPER(ece_rules_pkg.g_party_name),' ')) AND
2129 (nvl(upper(g_address_tbl(k).tp_translator_code),' ')= nvl(UPPER(ece_rules_pkg.g_party_number),' '))
2130 THEN
2131
2132 if (l_org_id is null) OR (nvl(l_org_id,-99) = g_address_tbl(k).org_id) --3608171
2133 then
2134 l_address_type :=g_address_tbl(k).address_type;
2135 x_org_id :=g_address_tbl(k).org_id;
2136 x_addr_id :=g_address_tbl(k).address_id;
2137 x_tp_translator_code:=g_address_tbl(k).address_code;
2138 x_entity_id :=g_address_tbl(k).parent_id;
2139 x_tp_location_code :=g_address_tbl(k).tp_location_code;
2140 x_tp_location_name :=g_address_tbl(k).tp_location_name;
2141 x_addr1 :=g_address_tbl(k).address_line1;
2142 x_addr2 :=g_address_tbl(k).address_line2;
2143 x_addr3 :=g_address_tbl(k).address_line3;
2144 x_addr4 :=g_address_tbl(k).address_line4;
2145 x_addr_alt :=g_address_tbl(k).address_line_alt;
2146 x_city :=g_address_tbl(k).city;
2147 x_county :=g_address_tbl(k).county;
2148 x_state :=g_address_tbl(k).state;
2149 x_zip :=g_address_tbl(k).zip;
2150 x_province :=g_address_tbl(k).province;
2151 x_country :=g_address_tbl(k).country;
2152 x_region1 :=g_address_tbl(k).region_1;
2153 x_region2 :=g_address_tbl(k).region_2;
2154 x_region3 :=g_address_tbl(k).region_3;
2155 l_return_status := fnd_api.G_RET_STS_SUCCESS;
2156 l_status_code := 0;
2157 l_found_on_tbl := 'Y';
2158 exit;
2159 END if;
2160 END IF;
2161 END IF;
2162 END LOOP;
2163 ELSE
2164 for k in 1..g_address_tbl.COUNT
2165 loop
2166 IF (upper(g_address_tbl(k).address_type) =upper(l_address_type)) THEN
2167 IF upper(g_address_tbl(k).tp_translator_code) = UPPER(translator_code) AND
2168 upper(g_address_tbl(k).tp_location_code)= UPPER(l_tp_location_code) THEN
2169 if (l_org_id is null) OR (nvl(l_org_id,-99) = g_address_tbl(k).org_id) --3608171
2170 then
2171 l_address_type :=g_address_tbl(k).address_type;
2172 x_org_id :=g_address_tbl(k).org_id;
2173 x_addr_id :=g_address_tbl(k).address_id;
2174 x_tp_translator_code:=g_address_tbl(k).address_code;
2175 x_entity_id :=g_address_tbl(k).parent_id;
2176 x_tp_location_code :=g_address_tbl(k).tp_location_code;
2177 x_tp_location_name :=g_address_tbl(k).tp_location_name;
2178 x_addr1 :=g_address_tbl(k).address_line1;
2179 x_addr2 :=g_address_tbl(k).address_line2;
2180 x_addr3 :=g_address_tbl(k).address_line3;
2181 x_addr4 :=g_address_tbl(k).address_line4;
2182 x_addr_alt :=g_address_tbl(k).address_line_alt;
2183 x_city :=g_address_tbl(k).city;
2184 x_county :=g_address_tbl(k).county;
2185 x_state :=g_address_tbl(k).state;
2186 x_zip :=g_address_tbl(k).zip;
2187 x_province :=g_address_tbl(k).province;
2188 x_country :=g_address_tbl(k).country;
2189 x_region1 :=g_address_tbl(k).region_1;
2190 x_region2 :=g_address_tbl(k).region_2;
2191 x_region3 :=g_address_tbl(k).region_3;
2192 l_return_status := fnd_api.G_RET_STS_SUCCESS;
2193 l_status_code := 0;
2194 l_found_on_tbl := 'Y';
2195 exit;
2196 END if;
2197 END IF;
2198 END IF;
2199 END LOOP;
2200 END IF;
2201 END IF;
2202
2203 if l_found_on_tbl = 'N' then -- Bug 2614728
2204
2205 -- Call the address derivation api to get the address information. hgandiko1
2206 xProgress := 'ECERULEB-130-1220';
2207 ece_trading_partners_pub.ece_get_address_wrapper(
2208 p_api_version_number => 1.0,
2209 x_return_status => l_return_status,
2210 x_msg_count => l_msg_count,
2211 x_msg_data => l_msg_data,
2212 x_status_code => l_status_code,
2213 p_address_type => l_address_type,
2214 p_transaction_type => l_transaction_type,
2215 p_org_id_in => l_org_id,
2216 p_address_id_in => l_addr_id,
2217 p_tp_location_code_in => l_tp_location_code,
2218 p_translator_code_in => translator_code,
2219 p_tp_location_name_in => l_tp_location_name,
2220 p_address_line1_in => l_addr1,
2221 p_address_line2_in => l_addr2,
2222 p_address_line3_in => l_addr3,
2223 p_address_line4_in => l_addr4,
2224 p_address_line_alt_in => l_addr_alt,
2225 p_city_in => l_city,
2226 p_county_in => l_county,
2227 p_state_in => l_state,
2228 p_zip_in => l_zip,
2229 p_province_in => l_province,
2230 p_country_in => l_country,
2231 p_region_1_in => l_region1,
2232 p_region_2_in => l_region2,
2233 p_region_3_in => l_region3,
2234 x_entity_id_out => x_entity_id,
2235 x_org_id_out => x_org_id,
2236 x_address_id_out => x_addr_id,
2237 x_tp_location_code_out => x_tp_location_code,
2238 x_translator_code_out => x_tp_translator_code,
2239 x_tp_location_name_out => x_tp_location_name,
2240 x_address_line1_out => x_addr1,
2241 x_address_line2_out => x_addr2,
2242 x_address_line3_out => x_addr3,
2243 x_address_line4_out => x_addr4,
2244 x_address_line_alt_out => x_addr_alt,
2245 x_city_out => x_city,
2246 x_county_out => x_county,
2247 x_state_out => x_state,
2248 x_zip_out => x_zip,
2249 x_province_out => x_province,
2250 x_country_out => x_country,
2251 x_region_1_out => x_region1,
2252 x_region_2_out => x_region2,
2253 x_region_3_out => x_region3);
2254
2255 xProgress := 'ECERULEB-130-1221';
2256 if x_addr_id IS NOT NULL then -- Bug 2617428
2257 j := g_address_tbl.COUNT + 1;
2258 g_address_tbl(j).address_type := l_address_type;
2259 g_address_tbl(j).org_id := x_org_id;
2260 g_address_tbl(j).address_id := x_addr_id;
2261 g_address_tbl(j).parent_id := x_entity_id;
2262 g_address_tbl(j).tp_location_code := x_tp_location_code;
2263 g_address_tbl(j).tp_location_name := x_tp_location_name;
2264 g_address_tbl(j).tp_translator_code:= translator_code;
2265 g_address_tbl(j).address_code := x_tp_translator_code;
2266 g_address_tbl(j).address_line1 := x_addr1;
2267 g_address_tbl(j).address_line2 := x_addr2;
2268 g_address_tbl(j).address_line3 := x_addr3;
2269 g_address_tbl(j).address_line4 := x_addr4;
2270 g_address_tbl(j).address_line_alt := x_addr_alt;
2271 g_address_tbl(j).city := x_city;
2272 g_address_tbl(j).county := x_county;
2273 g_address_tbl(j).state := x_state;
2274 g_address_tbl(j).zip := x_zip;
2275 g_address_tbl(j).province := x_province;
2276 g_address_tbl(j).country := x_country;
2277 g_address_tbl(j).region_1 := x_region1;
2278 g_address_tbl(j).region_2 := x_region2;
2279 g_address_tbl(j).region_3 := x_region3;
2280 end if;
2281 end if;
2282
2283 xProgress := 'ECERULEB-130-1230';
2284 if (l_return_status = fnd_api.g_ret_sts_success) then
2285
2286 -- assign the address info that got from address derivation to
2287 -- pl/sql table.
2288 xProgress := 'ECERULEB-130-1240';
2289 if (l_status_code = ece_trading_partners_pub.g_no_errors) then
2290 xProgress := 'ECERULEB-130-1250';
2291 l_valid_rule := 'Y';
2292
2293 if (p_entity_id_pos is not null) and (x_entity_id is not null) then
2294 xProgress := 'ECERULEB-130-1254';
2295 ec_utils.g_file_tbl (p_entity_id_pos).value := x_entity_id;
2296 end if;
2297
2298 if (p_org_id_pos is not null) and (x_org_id is not null) then
2299 xProgress := 'ECERULEB-130-1260';
2300 ec_utils.g_file_tbl (p_org_id_pos).value := x_org_id;
2301 end if;
2302
2303 if (p_addr_id_pos is not null) and (x_addr_id is not null) then
2304 xProgress := 'ECERULEB-130-1270';
2305 ec_utils.g_file_tbl (p_addr_id_pos).value := x_addr_id;
2306 end if;
2307
2308 if (p_tp_location_code_pos is not null) and (x_tp_location_code is not null) then
2309 xProgress := 'ECERULEB-130-1280';
2310 ec_utils.g_file_tbl (p_tp_location_code_pos).value := x_tp_location_code;
2311 end if;
2312
2313 -- Bug 2570369: Uncommented the following if condition because the x_tp_translator_code
2314 -- is used as a placeholder to store new values from the AD code such as
2315 -- inventory_organization_id from hr_locations.
2316
2317 if (p_tp_translator_code_pos is not null) and (x_tp_translator_code is not null) then
2318 xProgress := 'ECERULEB-130-1290';
2319 ec_utils.g_file_tbl (p_tp_translator_code_pos).value := x_tp_translator_code;
2320 end if;
2321
2322 if (p_tp_location_name_pos is not null) and (x_tp_location_name is not null) then
2323 xProgress := 'ECERULEB-130-1300';
2324 ec_utils.g_file_tbl (p_tp_location_name_pos).value := x_tp_location_name;
2325 end if;
2326
2327 if (p_addr1_pos is not null) and (x_addr1 is not null) then
2328 xProgress := 'ECERULEB-130-1310';
2329 ec_utils.g_file_tbl (p_addr1_pos).value := x_addr1;
2330 end if;
2331
2332 if (p_addr2_pos is not null) and (x_addr2 is not null) then
2333 xProgress := 'ECERULEB-130-1320';
2334 ec_utils.g_file_tbl (p_addr2_pos).value := x_addr2;
2335 end if;
2336
2337 if (p_addr3_pos is not null) and (x_addr3 is not null) then
2338 xProgress := 'ECERULEB-130-1330';
2339 ec_utils.g_file_tbl (p_addr3_pos).value := x_addr3;
2340 end if;
2341
2342 if (p_addr4_pos is not null) and (x_addr4 is not null) then
2343 xProgress := 'ECERULEB-130-1340';
2344 ec_utils.g_file_tbl (p_addr4_pos).value := x_addr4;
2345 end if;
2346
2347 if (p_addr_alt_pos is not null) and (x_addr_alt is not null) then
2348 xProgress := 'ECERULEB-130-1350';
2349 ec_utils.g_file_tbl (p_addr_alt_pos).value := x_addr_alt;
2350 end if;
2351
2352 if (p_city_pos is not null) and (x_city is not null) then
2353 xProgress := 'ECERULEB-130-1360';
2354 ec_utils.g_file_tbl (p_city_pos).value := x_city;
2355 end if;
2356
2357 if (p_county_pos is not null) and (x_county is not null) then
2358 xProgress := 'ECERULEB-130-1370';
2359 ec_utils.g_file_tbl (p_county_pos).value := x_county;
2360 end if;
2361
2362 if (p_state_pos is not null) and (x_state is not null) then
2363 xProgress := 'ECERULEB-130-1380';
2364 ec_utils.g_file_tbl (p_state_pos).value := x_state;
2365 end if;
2366
2367 if (p_zip_pos is not null) and (x_zip is not null) then
2368 xProgress := 'ECERULEB-130-1390';
2369 ec_utils.g_file_tbl (p_zip_pos).value := x_zip;
2370 end if;
2371
2372 if (p_province_pos is not null) and (x_province is not null) then
2373 xProgress := 'ECERULEB-130-1400';
2374 ec_utils.g_file_tbl (p_province_pos).value := x_province;
2375 end if;
2376
2377 if (p_country_pos is not null) and (x_country is not null) then
2378 xProgress := 'ECERULEB-130-1410';
2379 ec_utils.g_file_tbl (p_country_pos).value := x_country;
2380 end if;
2381
2382 if (p_region1_pos is not null) and (x_region1 is not null) then
2383 xProgress := 'ECERULEB-130-1420';
2384 ec_utils.g_file_tbl (p_region1_pos).value := x_region1;
2385 end if;
2386
2387 if (p_region2_pos is not null) and (x_region2 is not null) then
2388 xProgress := 'ECERULEB-130-1430';
2389 ec_utils.g_file_tbl (p_region2_pos).value := x_region2;
2390 end if;
2391
2392 if (p_region3_pos is not null) and (x_region3 is not null) then
2393 xProgress := 'ECERULEB-130-1440';
2394 ec_utils.g_file_tbl (p_region3_pos).value := x_region3;
2395 end if;
2396
2397 /* bug 2151462 Added new messages and modified the existing ones */
2398
2399 elsif (l_status_code = ece_trading_partners_pub.g_inconsistent_addr_comp) then
2400 xProgress := 'ECERULEB-130-1450';
2401 fnd_message.set_name ('EC', 'ECE_INCONSISENT_ADDRESS');
2402 fnd_message.set_token ('ADDRESS', p_address);
2403 fnd_message.set_token ('A1', l_addr1);
2404 fnd_message.set_token ('A2', l_addr2);
2405 fnd_message.set_token ('A3', l_addr3);
2406 fnd_message.set_token ('A4', l_addr4);
2407 fnd_message.set_token ('CITY', l_city);
2408 fnd_message.set_token ('POSTAL_CODE', l_zip);
2409 fnd_message.set_token ('ORG_ID', l_org_id);
2410 fnd_message.set_token ('LOCATION_CODE', l_tp_location_code);
2411 l_msg_text := fnd_message.get;
2412 ec_debug.pl (0, l_msg_text);
2413 l_valid_rule := 'N';
2414
2415 elsif (l_status_code = ece_trading_partners_pub.g_multiple_addr_found) then
2416 xProgress := 'ECERULEB-130-1460';
2417 fnd_message.set_name ('EC', 'ECE_MULTIPLE_ADDR_FOUND');
2418 fnd_message.set_token ('ADDRESS', p_address);
2419 fnd_message.set_token ('A1', l_addr1);
2420 fnd_message.set_token ('A2', l_addr2);
2421 fnd_message.set_token ('A3', l_addr3);
2422 fnd_message.set_token ('A4', l_addr4);
2423 fnd_message.set_token ('CITY', l_city);
2424 fnd_message.set_token ('POSTAL_CODE', l_zip);
2425 fnd_message.set_token ('ORG_ID', l_org_id);
2426 fnd_message.set_token ('LOCATION_CODE', l_tp_location_code);
2427 l_msg_text := fnd_message.get;
2428 ec_debug.pl (0, l_msg_text);
2429 l_valid_rule := 'N';
2430
2431 elsif (l_status_code = ece_trading_partners_pub.g_cannot_derive_addr) then
2432 xProgress := 'ECERULEB-130-1470';
2433 fnd_message.set_name ('EC', 'ECE_CANNOT_DERIVE_ADDRESS');
2434 fnd_message.set_token ('ADDRESS', p_address);
2435 fnd_message.set_token ('LOCATION_CODE', l_tp_location_code);
2436 fnd_message.set_token ('ORG_ID', l_org_id);
2437 l_msg_text := fnd_message.get;
2438 ec_debug.pl (0, l_msg_text);
2439 l_valid_rule := 'N';
2440
2441 elsif (l_status_code = ece_trading_partners_pub.g_multiple_loc_found) then
2442 xProgress := 'ECERULEB-130-1480';
2443 fnd_message.set_name ('EC', 'ECE_MULTIPLE_LOC_FOUND');
2444 fnd_message.set_token ('ADDRESS', p_address);
2445 fnd_message.set_token ('LOCATION_CODE', l_tp_location_code);
2446 fnd_message.set_token ('ORG_ID', l_org_id);
2447 l_msg_text := fnd_message.get;
2448 ec_debug.pl (0, l_msg_text);
2449 l_valid_rule := 'N';
2450
2451 elsif (l_status_code = ece_trading_partners_pub.g_cannot_derive_addr_id) then
2452 xProgress := 'ECERULEB-130-1490';
2453 fnd_message.set_name ('EC', 'ECE_CANNOT_DERIVE_ADDRESS_ID');
2454 fnd_message.set_token ('ADDRESS', p_address);
2455 l_msg_text := fnd_message.get;
2456 ec_debug.pl (0, l_msg_text);
2457 l_valid_rule := 'N';
2458 end if;
2459
2460 else
2461 if (l_status_code = ece_trading_partners_pub.g_invalid_addr_id) then
2462 xProgress := 'ECERULEB-130-1500';
2463 fnd_message.set_name ('EC', 'ECE_INVALID_ADDR_ID');
2464 fnd_message.set_token ('ADDRESS', p_address);
2465 fnd_message.set_token ('ADDRESS_ID', l_addr_id);
2466 fnd_message.set_token ('ORG_ID', l_org_id);
2467 l_msg_text := fnd_message.get;
2468 ec_debug.pl (0, l_msg_text);
2469 l_valid_rule := 'N';
2470
2471 elsif (l_status_code = ece_trading_partners_pub.g_invalid_org_id) then
2472 xProgress := 'ECERULEB-130-1510';
2473 fnd_message.set_name ('EC', 'ECE_INVALID_ORG_ID');
2474 fnd_message.set_token ('ORG_ID', l_org_id);
2475 l_msg_text := fnd_message.get;
2476 ec_debug.pl (0, l_msg_text);
2477 l_valid_rule := 'N';
2478
2479 elsif (l_status_code = ece_trading_partners_pub.g_invalid_parameter) then
2480 xProgress := 'ECERULEB-130-1520';
2481 fnd_message.set_name ('EC', 'ECE_INVALID_ADDR_PARAMETER');
2482 fnd_message.set_token ('ADDRESS', p_address);
2483 l_msg_text := fnd_message.get;
2484 ec_debug.pl (0, l_msg_text);
2485 l_valid_rule := 'N';
2486
2487 else
2488 xProgress := 'ECERULEB-130-1530';
2489 raise addr_unexp_error;
2490 end if;
2491
2492 end if;
2493
2494 -- print out the address value after address derivation.
2495 xProgress := 'ECERULEB-130-1540';
2496 if ec_debug.G_debug_level = 3 then
2497 ec_debug.pl (3, 'Output of Address Derivation');
2498 ec_debug.pl (3, 'l_return_status', l_return_status);
2499 ec_debug.pl (3, 'l_msg_count', l_msg_count);
2500 ec_debug.pl (3, 'l_msg_data', l_msg_data);
2501 ec_debug.pl (3, 'l_status_code', l_status_code);
2502 ec_debug.pl (3, 'l_address_type', l_address_type);
2503 ec_debug.pl (3, 'l_transaction_type', l_transaction_type);
2504 ec_debug.pl (3, 'x_org_id', x_org_id);
2505 ec_debug.pl (3, 'x_addr_id', x_addr_id);
2506 ec_debug.pl (3, 'x_tp_location_code', x_tp_location_code);
2507 ec_debug.pl (3, 'x_tp_translator_code', x_tp_translator_code);
2508 ec_debug.pl (3, 'x_tp_location_name', x_tp_location_name);
2509 ec_debug.pl (3, 'x_addr1', x_addr1);
2510 ec_debug.pl (3, 'x_addr2', x_addr2);
2511 ec_debug.pl (3, 'x_addr3', x_addr3);
2512 ec_debug.pl (3, 'x_addr4', x_addr4);
2513 ec_debug.pl (3, 'x_addr_alt', x_addr_alt);
2514 ec_debug.pl (3, 'x_city', x_city);
2515 ec_debug.pl (3, 'x_county', x_county);
2516 ec_debug.pl (3, 'x_state', x_state);
2517 ec_debug.pl (3, 'x_zip', x_zip);
2518 ec_debug.pl (3, 'x_province', x_province);
2519 ec_debug.pl (3, 'x_country', x_country);
2520 ec_debug.pl (3, 'x_region1', x_region1);
2521 ec_debug.pl (3, 'x_region2', x_region2);
2522 ec_debug.pl (3, 'x_region3', x_region3);
2523 ec_debug.pl (3, 'l_valid_rule', l_valid_rule);
2524 end if;
2525 xProgress := 'ECERULEB-130-1550';
2526 l_stage_id := ec_utils.g_ext_levels(ec_utils.g_current_level).stage_id;
2527 l_document_id := ec_utils.g_ext_levels(ec_utils.g_current_level).document_id;
2528 l_document_number := ec_utils.g_ext_levels(ec_utils.g_current_level).document_number;
2529
2530 xProgress := 'ECERULEB-130-1560';
2531 if ec_debug.G_debug_level = 3 then
2532 ec_debug.pl (3, 'l_stage_id', l_stage_id);
2533 ec_debug.pl (3, 'l_document_id', l_document_id);
2534 ec_debug.pl (3, 'l_document_number', l_document_number);
2535 end if;
2536
2537 -- get the defined process rule 'INVALID_ADDRESS' information.
2538 xProgress := 'ECERULEB-130-1570';
2539 open c_addr_rule;
2540 fetch c_addr_rule into l_rule_id, l_action_code;
2541 if c_addr_rule%NOTFOUND then
2542 raise no_addr_rule_info;
2543 end if;
2544
2545 xProgress := 'ECERULEB-130-1580';
2546 Update_Status (l_transaction_type, ec_utils.g_current_level, l_valid_rule,
2547 l_action_code, NULL, l_rule_id, l_stage_id, l_document_id,
2548 g_process_rule, l_document_number, l_msg_text);
2549
2550 xProgress := 'ECERULEB-130-1590';
2551 if ec_debug.G_debug_level >= 2 then
2552 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_GET_ADDRESS_INFO');
2553 end if;
2554
2555 EXCEPTION
2556 WHEN addr_unexp_error then
2557 ec_debug.pl (0, 'EC', 'ECE_ADDR_UNEXP_ERROR',
2558 'ADDRESS', p_address);
2559 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_GET_ADDRESS_INFO');
2560 raise ec_utils.program_exit;
2561
2562 WHEN stack_variable_not_found then
2563 ec_debug.pl (0, 'EC', 'ECE_VARIABLE_NOT_ON_STACK',
2564 'VARIABLE_NAME', 'I_ADDRESS_TYPE');
2565 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_GET_ADDRESS_INFO');
2566 raise ec_utils.program_exit;
2567
2568 WHEN no_addr_rule_info then
2569 if (c_addr_rule%ISOPEN) then
2570 close c_addr_rule;
2571 end if;
2572 ec_debug.pl (0, 'EC', 'ECE_NO_PROCESS_RULE',
2573 'TRANSACTION_TYPE', l_transaction_type,
2574 'RULE_TYPE', l_rule_type);
2575 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_GET_ADDRESS_INFO');
2576 raise ec_utils.program_exit;
2577
2578 WHEN OTHERS THEN
2579 if (c_addr_rule%ISOPEN) then
2580 close c_addr_rule;
2581 end if;
2582 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
2583 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
2584 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_GET_ADDRESS_INFO');
2585 raise ec_utils.program_exit;
2586
2587 END Validate_Get_Address_Info;
2588
2589
2590 PROCEDURE Validate_Ship_To_Address IS
2591
2592 xProgress VARCHAR2(80);
2593 n_entity_id_pos NUMBER := NULL;
2594 n_org_id_pos NUMBER := NULL;
2595 n_addr_id_pos NUMBER := NULL;
2596 n_edi_location_code_pos NUMBER := NULL;
2597 n_tp_translator_code_pos NUMBER := NULL;
2598 n_tp_location_name_pos NUMBER := NULL;
2599 n_addr1_pos NUMBER := NULL;
2600 n_addr2_pos NUMBER := NULL;
2601 n_addr3_pos NUMBER := NULL;
2602 n_addr4_pos NUMBER := NULL;
2603 n_addr_alt_pos NUMBER := NULL;
2604 n_city_pos NUMBER := NULL;
2605 n_county_pos NUMBER := NULL;
2606 n_state_pos NUMBER := NULL;
2607 n_zip_pos NUMBER := NULL;
2608 n_province_pos NUMBER := NULL;
2609 n_country_pos NUMBER := NULL;
2610 n_region1_pos NUMBER := NULL;
2611 n_region2_pos NUMBER := NULL;
2612 n_region3_pos NUMBER := NULL;
2613 l_action_code ece_process_rules.action_code%TYPE;
2614 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
2615 l_address VARCHAR2(50) := 'ship to';
2616
2617 BEGIN
2618
2619 xProgress := 'ECERULEB-140-1000';
2620 if ec_debug.G_debug_level >= 2 then
2621 ec_debug.push ('ECE_RULES_PKG.VALIDATE_SHIP_TO_ADDRESS');
2622 end if;
2623 Get_Action_Ignore_Flag (l_action_code, l_ignore_flag);
2624
2625 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
2626
2627 -- get the pl/sql position for address columns.
2628 xProgress := 'ECERULEB-140-1002';
2629 ec_utils.find_pos (
2630 ec_utils.g_current_level,
2631 'SHIP_TO_CUSTOMER_ID',
2632 n_entity_id_pos);
2633
2634 xProgress := 'ECERULEB-140-1004';
2635 ec_utils.find_pos (
2636 1,
2637 'ORG_ID',
2638 n_org_id_pos);
2639
2640 xProgress := 'ECERULEB-140-1010';
2641 ec_utils.find_pos (
2642 ec_utils.g_current_level,
2643 'SHIP_TO_ADDRESS_ID',
2644 n_addr_id_pos);
2645
2646 xProgress := 'ECERULEB-140-1020';
2647 ec_utils.find_pos (
2648 ec_utils.g_current_level,
2649 'SHIP_TO_EDI_LOCATION_CODE',
2650 n_edi_location_code_pos);
2651
2652 /* ec_utils.find_pos (
2653 1,
2654 'TP_TRANSLATOR_CODE',
2655 n_tp_translator_code_pos);
2656 bug2151462 */
2657
2658 /* Bug 2570369
2659 Using the variable n_tp_translator_code_pos to store the value of
2660 customer number since a new variable would require a change in the
2661 validate_get_address_info.
2662 */
2663 /* Bug 2663632 : Swapped the column names ship_to_address_code and
2664 ship_to_address_name
2665 */
2666 xProgress := 'ECERULEB-140-1030';
2667 ec_utils.find_pos (
2668 ec_utils.g_current_level,
2669 'SHIP_TO_ADDRESS_CODE',
2670 n_tp_translator_code_pos);
2671
2672 xProgress := 'ECERULEB-140-1040';
2673 ec_utils.find_pos (
2674 ec_utils.g_current_level,
2675 'SHIP_TO_ADDRESS_NAME',
2676 n_tp_location_name_pos);
2677
2678 xProgress := 'ECERULEB-140-1050';
2679 ec_utils.find_pos (
2680 ec_utils.g_current_level,
2681 'SHIP_TO_ADDRESS1',
2682 n_addr1_pos);
2683
2684 xProgress := 'ECERULEB-140-1060';
2685 ec_utils.find_pos (
2686 ec_utils.g_current_level,
2687 'SHIP_TO_ADDRESS2',
2688 n_addr2_pos);
2689
2690 xProgress := 'ECERULEB-140-1070';
2691 ec_utils.find_pos (
2692 ec_utils.g_current_level,
2693 'SHIP_TO_ADDRESS3',
2694 n_addr3_pos);
2695
2696 xProgress := 'ECERULEB-140-1080';
2697 ec_utils.find_pos (
2698 ec_utils.g_current_level,
2699 'SHIP_TO_ADDRESS4',
2700 n_addr4_pos);
2701
2702 xProgress := 'ECERULEB-140-1090';
2703 ec_utils.find_pos (
2704 ec_utils.g_current_level,
2705 'SHIP_TO_CITY',
2706 n_city_pos);
2707
2708 xProgress := 'ECERULEB-140-1100';
2709 ec_utils.find_pos (
2710 ec_utils.g_current_level,
2711 'SHIP_TO_POSTAL_CODE',
2712 n_zip_pos);
2713
2714 xProgress := 'ECERULEB-140-1110';
2715 ec_utils.find_pos (
2716 ec_utils.g_current_level,
2717 'SHIP_TO_COUNTRY_INT',
2718 n_country_pos);
2719
2720 xProgress := 'ECERULEB-140-1120';
2721 ec_utils.find_pos (
2722 ec_utils.g_current_level,
2723 'SHIP_TO_STATE_INT',
2724 n_state_pos);
2725
2726 xProgress := 'ECERULEB-140-1130';
2727 ec_utils.find_pos (
2728 ec_utils.g_current_level,
2729 'SHIP_TO_PROVINCE_INT',
2730 n_province_pos);
2731
2732 xProgress := 'ECERULEB-140-1140';
2733 ec_utils.find_pos (
2734 ec_utils.g_current_level,
2735 'SHIP_TO_COUNTY',
2736 n_county_pos);
2737
2738 xProgress := 'ECERULEB-140-1150';
2739 Validate_Get_Address_Info (n_entity_id_pos, n_org_id_pos, n_addr_id_pos,
2740 n_edi_location_code_pos, n_tp_translator_code_pos,
2741 n_tp_location_name_pos, n_addr1_pos, n_addr2_pos,
2742 n_addr3_pos, n_addr4_pos, n_addr_alt_pos,
2743 n_city_pos, n_county_pos, n_state_pos, n_zip_pos,
2744 n_province_pos, n_country_pos, n_region1_pos,
2745 n_region2_pos, n_region3_pos, l_address);
2746
2747 end if;
2748
2749 xProgress := 'ECERULEB-140-1160';
2750 if ec_debug.G_debug_level >= 2 then
2751 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_TO_ADDRESS');
2752 end if;
2753
2754 EXCEPTION
2755 WHEN ec_utils.program_exit then
2756 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_TO_ADDRESS');
2757 raise;
2758
2759 WHEN OTHERS THEN
2760 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
2761 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
2762 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_TO_ADDRESS');
2763 raise ec_utils.program_exit;
2764
2765 END Validate_Ship_To_Address;
2766
2767
2768 PROCEDURE Validate_Bill_To_Address IS
2769
2770 xProgress VARCHAR2(80);
2771 n_entity_id_pos NUMBER := NULL;
2772 n_org_id_pos NUMBER := NULL;
2773 n_addr_id_pos NUMBER := NULL;
2774 n_edi_location_code_pos NUMBER := NULL;
2775 n_tp_translator_code_pos NUMBER := NULL;
2776 n_tp_location_name_pos NUMBER := NULL;
2777 n_addr1_pos NUMBER := NULL;
2778 n_addr2_pos NUMBER := NULL;
2779 n_addr3_pos NUMBER := NULL;
2780 n_addr4_pos NUMBER := NULL;
2781 n_addr_alt_pos NUMBER := NULL;
2782 n_city_pos NUMBER := NULL;
2783 n_county_pos NUMBER := NULL;
2784 n_state_pos NUMBER := NULL;
2785 n_zip_pos NUMBER := NULL;
2786 n_province_pos NUMBER := NULL;
2787 n_country_pos NUMBER := NULL;
2788 n_region1_pos NUMBER := NULL;
2789 n_region2_pos NUMBER := NULL;
2790 n_region3_pos NUMBER := NULL;
2791 l_action_code ece_process_rules.action_code%TYPE;
2792 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
2793 l_address VARCHAR2(50) := 'bill to';
2794
2795 BEGIN
2796
2797 xProgress := 'ECERULEB-150-1000';
2798 if ec_debug.G_debug_level >= 2 then
2799 ec_debug.push ('ECE_RULES_PKG.VALIDATE_BILL_TO_ADDRESS');
2800 end if;
2801
2802 Get_Action_Ignore_Flag (l_action_code, l_ignore_flag);
2803
2804 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
2805
2806 -- get the pl/sql position for address columns.
2807 xProgress := 'ECERULEB-150-1002';
2808 ec_utils.find_pos (
2809 ec_utils.g_current_level,
2810 'BILL_TO_CUSTOMER_ID',
2811 n_entity_id_pos);
2812
2813 xProgress := 'ECERULEB-150-1004';
2814 ec_utils.find_pos (
2815 1,
2816 'ORG_ID',
2817 n_org_id_pos);
2818
2819 xProgress := 'ECERULEB-150-1010';
2820 ec_utils.find_pos (
2821 ec_utils.g_current_level,
2822 'BILL_TO_ADDRESS_ID',
2823 n_addr_id_pos);
2824
2825 xProgress := 'ECERULEB-150-1020';
2826 ec_utils.find_pos (
2827 ec_utils.g_current_level,
2828 'BILL_TO_EDI_LOCATION_CODE',
2829 n_edi_location_code_pos);
2830
2831 /* ec_utils.find_pos (
2832 1,
2833 'TP_TRANSLATOR_CODE',
2834 n_tp_translator_code_pos);
2835 bug2151462 */
2836
2837 /* Bug 2663632 : Create a new Column for retreiving Bill_to_address_name.
2838 Also added code to retreive bill to customer number in
2839 bill_to_address_code.
2840 */
2841 xProgress := 'ECERULEB-150-1030';
2842 ec_utils.find_pos (
2843 ec_utils.g_current_level,
2844 'BILL_TO_ADDRESS_CODE',
2845 n_tp_translator_code_pos);
2846
2847 xProgress := 'ECERULEB-150-1040';
2848 ec_utils.find_pos (
2849 ec_utils.g_current_level,
2850 'BILL_TO_ADDRESS_NAME',
2851 n_tp_location_name_pos);
2852
2853 xProgress := 'ECERULEB-150-1050';
2854 ec_utils.find_pos (
2855 ec_utils.g_current_level,
2856 'BILL_TO_ADDRESS1',
2857 n_addr1_pos);
2858
2859 xProgress := 'ECERULEB-150-1060';
2860 ec_utils.find_pos (
2861 ec_utils.g_current_level,
2862 'BILL_TO_ADDRESS2',
2863 n_addr2_pos);
2864
2865 xProgress := 'ECERULEB-150-1070';
2866 ec_utils.find_pos (
2867 ec_utils.g_current_level,
2868 'BILL_TO_ADDRESS3',
2869 n_addr3_pos);
2870
2871 xProgress := 'ECERULEB-150-1080';
2872 ec_utils.find_pos (
2873 ec_utils.g_current_level,
2874 'BILL_TO_ADDRESS4',
2875 n_addr4_pos);
2876
2877 xProgress := 'ECERULEB-150-1090';
2878 ec_utils.find_pos (
2879 ec_utils.g_current_level,
2880 'BILL_TO_CITY',
2881 n_city_pos);
2882
2883 xProgress := 'ECERULEB-150-1100';
2884 ec_utils.find_pos (
2885 ec_utils.g_current_level,
2886 'BILL_TO_POSTAL_CODE',
2887 n_zip_pos);
2888
2889 xProgress := 'ECERULEB-150-1110';
2890 ec_utils.find_pos (
2891 ec_utils.g_current_level,
2892 'BILL_TO_COUNTRY_INT',
2893 n_country_pos);
2894
2895 xProgress := 'ECERULEB-150-1120';
2896 ec_utils.find_pos (
2897 ec_utils.g_current_level,
2898 'BILL_TO_STATE_INT',
2899 n_state_pos);
2900
2901 xProgress := 'ECERULEB-150-1130';
2902 ec_utils.find_pos (
2903 ec_utils.g_current_level,
2904 'BILL_TO_PROVINCE_INT',
2905 n_province_pos);
2906
2907 xProgress := 'ECERULEB-150-1140';
2908 ec_utils.find_pos (
2909 ec_utils.g_current_level,
2910 'BILL_TO_COUNTY',
2911 n_county_pos);
2912
2913 xProgress := 'ECERULEB-150-1150';
2914 Validate_Get_Address_Info (n_entity_id_pos, n_org_id_pos, n_addr_id_pos,
2915 n_edi_location_code_pos, n_tp_translator_code_pos,
2916 n_tp_location_name_pos, n_addr1_pos, n_addr2_pos,
2917 n_addr3_pos, n_addr4_pos, n_addr_alt_pos,
2918 n_city_pos, n_county_pos, n_state_pos, n_zip_pos,
2919 n_province_pos, n_country_pos, n_region1_pos,
2920 n_region2_pos, n_region3_pos, l_address);
2921
2922 end if;
2923
2924 xProgress := 'ECERULEB-150-1160';
2925 if ec_debug.G_debug_level >= 2 then
2926 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_TO_ADDRESS');
2927 end if;
2928 EXCEPTION
2929 WHEN ec_utils.program_exit then
2930 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_TO_ADDRESS');
2931 raise;
2932
2933 WHEN OTHERS THEN
2934 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
2935 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
2936 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_TO_ADDRESS');
2937 raise ec_utils.program_exit;
2938
2939 END Validate_Bill_To_Address;
2940
2941
2942 PROCEDURE Validate_Sold_To_Address IS
2943
2944 xProgress VARCHAR2(80);
2945 n_entity_id_pos NUMBER := NULL;
2946 n_org_id_pos NUMBER := NULL;
2947 n_addr_id_pos NUMBER := NULL;
2948 n_edi_location_code_pos NUMBER := NULL;
2949 n_tp_translator_code_pos NUMBER := NULL;
2950 n_tp_location_name_pos NUMBER := NULL;
2951 n_addr1_pos NUMBER := NULL;
2952 n_addr2_pos NUMBER := NULL;
2953 n_addr3_pos NUMBER := NULL;
2954 n_addr4_pos NUMBER := NULL;
2955 n_addr_alt_pos NUMBER := NULL;
2956 n_city_pos NUMBER := NULL;
2957 n_county_pos NUMBER := NULL;
2958 n_state_pos NUMBER := NULL;
2959 n_zip_pos NUMBER := NULL;
2960 n_province_pos NUMBER := NULL;
2961 n_country_pos NUMBER := NULL;
2962 n_region1_pos NUMBER := NULL;
2963 n_region2_pos NUMBER := NULL;
2964 n_region3_pos NUMBER := NULL;
2965 l_action_code ece_process_rules.action_code%TYPE;
2966 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
2967 l_address VARCHAR2(50) := 'Sold to';
2968
2969 BEGIN
2970
2971 xProgress := 'ECERULEB-230-1000';
2972 if ec_debug.G_debug_level >= 2 then
2973 ec_debug.push ('ECE_RULES_PKG.VALIDATE_SOLD_TO_ADDRESS');
2974 end if;
2975
2976 Get_Action_Ignore_Flag (l_action_code, l_ignore_flag);
2977
2978 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
2979
2980 -- get the pl/sql position for address columns.
2981 xProgress := 'ECERULEB-230-1002';
2982 ec_utils.find_pos (
2983 ec_utils.g_current_level,
2984 'SOLD_TO_CUSTOMER_ID',
2985 n_entity_id_pos);
2986
2987 xProgress := 'ECERULEB-230-1004';
2988 ec_utils.find_pos (
2989 1,
2990 'ORG_ID',
2991 n_org_id_pos);
2992
2993 xProgress := 'ECERULEB-230-1010';
2994 ec_utils.find_pos (
2995 ec_utils.g_current_level,
2996 'SOLD_TO_ADDRESS_ID',
2997 n_addr_id_pos);
2998
2999 xProgress := 'ECERULEB-230-1020';
3000 ec_utils.find_pos (
3001 ec_utils.g_current_level,
3002 'SOLD_TO_EDI_LOCATION_CODE',
3003 n_edi_location_code_pos);
3004
3005 /* ec_utils.find_pos (
3006 1,
3007 'TP_TRANSLATOR_CODE',
3008 n_tp_translator_code_pos);
3009 bug2151462 */
3010
3011 /* Bug 2570369. This is a fix for the bug 2641276.
3012 Using the variable n_tp_translator_code_pos to store the value of
3013 customer number since a new variable would require a change in the
3014 validate_get_address_info.
3015 */
3016 xProgress := 'ECERULEB-230-1030';
3017 ec_utils.find_pos (
3018 ec_utils.g_current_level,
3019 'SOLD_TO_ADDRESS_CODE',
3020 n_tp_translator_code_pos);
3021
3022 /* Bug 2432974 Changed the column name SOLD_TO_ADDRESS_CODE to
3023 SOLD_TO_ADDRESS_NAME
3024 */
3025 xProgress := 'ECERULEB-230-1040';
3026 ec_utils.find_pos (
3027 ec_utils.g_current_level,
3028 'SOLD_TO_ADDRESS_NAME',
3029 n_tp_location_name_pos);
3030
3031 xProgress := 'ECERULEB-230-1050';
3032 ec_utils.find_pos (
3033 ec_utils.g_current_level,
3034 'SOLD_TO_ADDRESS1',
3035 n_addr1_pos);
3036
3037 xProgress := 'ECERULEB-230-1060';
3038 ec_utils.find_pos (
3039 ec_utils.g_current_level,
3040 'SOLD_TO_ADDRESS2',
3041 n_addr2_pos);
3042
3043 xProgress := 'ECERULEB-230-1070';
3044 ec_utils.find_pos (
3045 ec_utils.g_current_level,
3046 'SOLD_TO_ADDRESS3',
3047 n_addr3_pos);
3048
3049 xProgress := 'ECERULEB-230-1080';
3050 ec_utils.find_pos (
3051 ec_utils.g_current_level,
3052 'SOLD_TO_ADDRESS4',
3053 n_addr4_pos);
3054
3055 xProgress := 'ECERULEB-230-1090';
3056 ec_utils.find_pos (
3057 ec_utils.g_current_level,
3058 'SOLD_TO_CITY',
3059 n_city_pos);
3060
3061 xProgress := 'ECERULEB-230-1100';
3062 ec_utils.find_pos (
3063 ec_utils.g_current_level,
3064 'SOLD_TO_POSTAL_CODE',
3065 n_zip_pos);
3066
3067 xProgress := 'ECERULEB-230-1110';
3068 ec_utils.find_pos (
3069 ec_utils.g_current_level,
3070 'SOLD_TO_COUNTRY_INT',
3071 n_country_pos);
3072
3073 xProgress := 'ECERULEB-230-1120';
3074 ec_utils.find_pos (
3075 ec_utils.g_current_level,
3076 'SOLD_TO_STATE_INT',
3077 n_state_pos);
3078
3079 xProgress := 'ECERULEB-230-1130';
3080 ec_utils.find_pos (
3081 ec_utils.g_current_level,
3082 'SOLD_TO_PROVINCE_INT',
3083 n_province_pos);
3084
3085 xProgress := 'ECERULEB-230-1140';
3086 ec_utils.find_pos (
3087 ec_utils.g_current_level,
3088 'SOLD_TO_COUNTY',
3089 n_county_pos);
3090
3091 xProgress := 'ECERULEB-230-1150';
3092 Validate_Get_Address_Info (n_entity_id_pos, n_org_id_pos, n_addr_id_pos,
3093 n_edi_location_code_pos, n_tp_translator_code_pos,
3094 n_tp_location_name_pos, n_addr1_pos, n_addr2_pos,
3095 n_addr3_pos, n_addr4_pos, n_addr_alt_pos,
3096 n_city_pos, n_county_pos, n_state_pos, n_zip_pos,
3097 n_province_pos, n_country_pos, n_region1_pos,
3098 n_region2_pos, n_region3_pos, l_address);
3099
3100 end if;
3101
3102 xProgress := 'ECERULEB-230-1160';
3103 if ec_debug.G_debug_level >= 2 then
3104 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SOLD_TO_ADDRESS');
3105 end if;
3106
3107 EXCEPTION
3108 WHEN ec_utils.program_exit then
3109 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SOLD_TO_ADDRESS');
3110 raise;
3111
3112 WHEN OTHERS THEN
3113 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
3114 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
3115 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SOLD_TO_ADDRESS');
3116 raise ec_utils.program_exit;
3117
3118 END Validate_Sold_To_Address;
3119
3120 PROCEDURE Validate_Ship_From_Address IS
3121
3122 xProgress VARCHAR2(80);
3123 n_entity_id_pos NUMBER := NULL;
3124 n_org_id_pos NUMBER := NULL;
3125 n_addr_id_pos NUMBER := NULL;
3126 n_edi_location_code_pos NUMBER := NULL;
3127 n_tp_translator_code_pos NUMBER := NULL;
3128 n_tp_location_name_pos NUMBER := NULL;
3129 n_addr1_pos NUMBER := NULL;
3130 n_addr2_pos NUMBER := NULL;
3131 n_addr3_pos NUMBER := NULL;
3132 n_addr4_pos NUMBER := NULL;
3133 n_addr_alt_pos NUMBER := NULL;
3134 n_city_pos NUMBER := NULL;
3135 n_county_pos NUMBER := NULL;
3136 n_state_pos NUMBER := NULL;
3137 n_zip_pos NUMBER := NULL;
3138 n_province_pos NUMBER := NULL;
3139 n_country_pos NUMBER := NULL;
3140 n_region1_pos NUMBER := NULL;
3141 n_region2_pos NUMBER := NULL;
3142 n_region3_pos NUMBER := NULL;
3143 l_action_code ece_process_rules.action_code%TYPE;
3144 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
3145 l_address VARCHAR2(50) := 'ship from';
3146
3147 BEGIN
3148
3149 xProgress := 'ECERULEB-160-1000';
3150 if ec_debug.G_debug_level >= 2 then
3151 ec_debug.push ('ECE_RULES_PKG.VALIDATE_SHIP_FROM_ADDRESS');
3152 end if;
3153
3154 Get_Action_Ignore_Flag (l_action_code, l_ignore_flag);
3155
3156 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
3157
3158 -- get the pl/sql position for address columns.
3159 xProgress := 'ECERULEB-160-1002';
3160 ec_utils.find_pos (
3161 ec_utils.g_current_level,
3162 'SHIP_FROM_CUSTOMER_ID',
3163 n_entity_id_pos);
3164
3165 xProgress := 'ECERULEB-160-1004';
3166 ec_utils.find_pos (
3167 1,
3168 'ORG_ID',
3169 n_org_id_pos);
3170
3171 xProgress := 'ECERULEB-160-1010';
3172 ec_utils.find_pos (
3173 ec_utils.g_current_level,
3174 'SHIP_FROM_ADDRESS_ID',
3175 n_addr_id_pos);
3176
3177 xProgress := 'ECERULEB-160-1020';
3178 ec_utils.find_pos (
3179 ec_utils.g_current_level,
3180 'SHIP_FROM_EDI_LOCATION_CODE',
3181 n_edi_location_code_pos);
3182
3183 xProgress := 'ECERULEB-160-1030';
3184 /* ec_utils.find_pos (
3185 1,
3186 'TP_TRANSLATOR_CODE',
3187 n_tp_translator_code_pos);
3188
3189 xProgress := 'ECERULEB-160-1040';
3190 ec_utils.find_pos (
3191 ec_utils.g_current_level,
3192 'SHIP_FROM_ADDRESS_CODE',
3193 n_tp_location_name_pos);
3194 bug2151462 */
3195
3196 xProgress := 'ECERULEB-160-1050';
3197 ec_utils.find_pos (
3198 ec_utils.g_current_level,
3199 'SHIP_FROM_ADDRESS1',
3200 n_addr1_pos);
3201
3202 xProgress := 'ECERULEB-160-1060';
3203 ec_utils.find_pos (
3204 ec_utils.g_current_level,
3205 'SHIP_FROM_ADDRESS2',
3206 n_addr2_pos);
3207
3208 xProgress := 'ECERULEB-160-1070';
3209 ec_utils.find_pos (
3210 ec_utils.g_current_level,
3211 'SHIP_FROM_ADDRESS3',
3212 n_addr3_pos);
3213
3214 xProgress := 'ECERULEB-160-1080';
3215 ec_utils.find_pos (
3216 ec_utils.g_current_level,
3217 'SHIP_FROM_ADDRESS4',
3218 n_addr4_pos);
3219
3220 xProgress := 'ECERULEB-160-1090';
3221 ec_utils.find_pos (
3222 ec_utils.g_current_level,
3223 'SHIP_FROM_CITY',
3224 n_city_pos);
3225
3226 xProgress := 'ECERULEB-160-1100';
3227 ec_utils.find_pos (
3228 ec_utils.g_current_level,
3229 'SHIP_FROM_POSTAL_CODE',
3230 n_zip_pos);
3231
3232 xProgress := 'ECERULEB-160-1110';
3233 ec_utils.find_pos (
3234 ec_utils.g_current_level,
3235 'SHIP_FROM_COUNTRY_INT',
3236 n_country_pos);
3237
3238 xProgress := 'ECERULEB-160-1120';
3239 ec_utils.find_pos (
3240 ec_utils.g_current_level,
3241 'SHIP_FROM_STATE_INT',
3242 n_state_pos);
3243
3244 xProgress := 'ECERULEB-160-1130';
3245 ec_utils.find_pos (
3246 ec_utils.g_current_level,
3247 'SHIP_FROM_PROVINCE_INT',
3248 n_province_pos);
3249
3250 xProgress := 'ECERULEB-160-1140';
3251 ec_utils.find_pos (
3252 ec_utils.g_current_level,
3253 'SHIP_FROM_COUNTY',
3254 n_county_pos);
3255
3256 xProgress := 'ECERULEB-160-1150';
3257 Validate_Get_Address_Info (n_entity_id_pos, n_org_id_pos, n_addr_id_pos,
3258 n_edi_location_code_pos, n_tp_translator_code_pos,
3259 n_tp_location_name_pos, n_addr1_pos, n_addr2_pos,
3260 n_addr3_pos, n_addr4_pos, n_addr_alt_pos,
3261 n_city_pos, n_county_pos, n_state_pos, n_zip_pos,
3262 n_province_pos, n_country_pos, n_region1_pos,
3263 n_region2_pos, n_region3_pos, l_address);
3264
3265 end if;
3266
3267 xProgress := 'ECERULEB-160-1160';
3268 if ec_debug.G_debug_level >= 2 then
3269 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_FROM_ADDRESS');
3270 end if;
3271
3272 EXCEPTION
3273 WHEN ec_utils.program_exit then
3274 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_FROM_ADDRESS');
3275 raise;
3276
3277 WHEN OTHERS THEN
3278 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
3279 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
3280 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_FROM_ADDRESS');
3281 raise ec_utils.program_exit;
3282
3283 END Validate_Ship_From_Address;
3284
3285
3286 PROCEDURE Validate_Bill_From_Address IS
3287
3288 xProgress VARCHAR2(80);
3289 n_entity_id_pos NUMBER := NULL;
3290 n_org_id_pos NUMBER := NULL;
3291 n_addr_id_pos NUMBER := NULL;
3292 n_edi_location_code_pos NUMBER := NULL;
3293 n_tp_translator_code_pos NUMBER := NULL;
3294 n_tp_location_name_pos NUMBER := NULL;
3295 n_addr1_pos NUMBER := NULL;
3296 n_addr2_pos NUMBER := NULL;
3297 n_addr3_pos NUMBER := NULL;
3298 n_addr4_pos NUMBER := NULL;
3299 n_addr_alt_pos NUMBER := NULL;
3300 n_city_pos NUMBER := NULL;
3301 n_county_pos NUMBER := NULL;
3302 n_state_pos NUMBER := NULL;
3303 n_zip_pos NUMBER := NULL;
3304 n_province_pos NUMBER := NULL;
3305 n_country_pos NUMBER := NULL;
3306 n_region1_pos NUMBER := NULL;
3307 n_region2_pos NUMBER := NULL;
3308 n_region3_pos NUMBER := NULL;
3309 l_action_code ece_process_rules.action_code%TYPE;
3310 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
3311 l_address VARCHAR2(50) := 'bill from';
3312
3313 BEGIN
3314
3315 xProgress := 'ECERULEB-170-1000';
3316 if ec_debug.G_debug_level >= 2 then
3317 ec_debug.push ('ECE_RULES_PKG.VALIDATE_BILL_FROM_ADDRESS');
3318 end if;
3319
3320 Get_Action_Ignore_Flag (l_action_code, l_ignore_flag);
3321
3322 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
3323
3324 -- get the pl/sql position for address columns.
3325 xProgress := 'ECERULEB-170-1002';
3326 ec_utils.find_pos (
3327 ec_utils.g_current_level,
3328 'BILL_FROM_CUSTOMER_ID',
3329 n_entity_id_pos);
3330
3331 xProgress := 'ECERULEB-170-1004';
3332 ec_utils.find_pos (
3333 1,
3334 'ORG_ID',
3335 n_org_id_pos);
3336
3337 xProgress := 'ECERULEB-170-1010';
3338 ec_utils.find_pos (
3339 ec_utils.g_current_level,
3340 'BILL_FROM_ADDRESS_ID',
3341 n_addr_id_pos);
3342
3343 xProgress := 'ECERULEB-170-1020';
3344 ec_utils.find_pos (
3345 ec_utils.g_current_level,
3346 'BILL_FROM_EDI_LOCATION_CODE',
3347 n_edi_location_code_pos);
3348
3349 xProgress := 'ECERULEB-170-1030';
3350 /* ec_utils.find_pos (
3351 1,
3352 'TP_TRANSLATOR_CODE',
3353 n_tp_translator_code_pos);
3354
3355 xProgress := 'ECERULEB-170-1040';
3356 ec_utils.find_pos (
3357 ec_utils.g_current_level,
3358 'BILL_FROM_ADDRESS_CODE',
3359 n_tp_location_name_pos);
3360 bug2151462 */
3361
3362 xProgress := 'ECERULEB-170-1050';
3363 ec_utils.find_pos (
3364 ec_utils.g_current_level,
3365 'BILL_FROM_ADDRESS1',
3366 n_addr1_pos);
3367
3368 xProgress := 'ECERULEB-170-1060';
3369 ec_utils.find_pos (
3370 ec_utils.g_current_level,
3371 'BILL_FROM_ADDRESS2',
3372 n_addr2_pos);
3373
3374 xProgress := 'ECERULEB-170-1070';
3375 ec_utils.find_pos (
3376 ec_utils.g_current_level,
3377 'BILL_FROM_ADDRESS3',
3378 n_addr3_pos);
3379
3380 xProgress := 'ECERULEB-170-1080';
3381 ec_utils.find_pos (
3382 ec_utils.g_current_level,
3383 'BILL_FROM_ADDRESS4',
3384 n_addr4_pos);
3385
3386 xProgress := 'ECERULEB-170-1090';
3387 ec_utils.find_pos (
3388 ec_utils.g_current_level,
3389 'BILL_FROM_CITY',
3390 n_city_pos);
3391
3392 xProgress := 'ECERULEB-170-1100';
3393 ec_utils.find_pos (
3394 ec_utils.g_current_level,
3395 'BILL_FROM_POSTAL_CODE',
3396 n_zip_pos);
3397
3398 xProgress := 'ECERULEB-170-1110';
3399 ec_utils.find_pos (
3400 ec_utils.g_current_level,
3401 'BILL_FROM_COUNTRY_INT',
3402 n_country_pos);
3403
3404 xProgress := 'ECERULEB-170-1120';
3405 ec_utils.find_pos (
3406 ec_utils.g_current_level,
3407 'BILL_FROM_STATE_INT',
3408 n_state_pos);
3409
3410 xProgress := 'ECERULEB-170-1130';
3411 ec_utils.find_pos (
3412 ec_utils.g_current_level,
3413 'BILL_FROM_PROVINCE_INT',
3414 n_province_pos);
3415
3416 xProgress := 'ECERULEB-170-1140';
3417 ec_utils.find_pos (
3418 ec_utils.g_current_level,
3419 'BILL_FROM_COUNTY',
3420 n_county_pos);
3421
3422 xProgress := 'ECERULEB-170-1150';
3423 Validate_Get_Address_Info (n_entity_id_pos, n_org_id_pos, n_addr_id_pos,
3424 n_edi_location_code_pos, n_tp_translator_code_pos,
3425 n_tp_location_name_pos, n_addr1_pos, n_addr2_pos,
3426 n_addr3_pos, n_addr4_pos, n_addr_alt_pos,
3427 n_city_pos, n_county_pos, n_state_pos, n_zip_pos,
3428 n_province_pos, n_country_pos, n_region1_pos,
3429 n_region2_pos, n_region3_pos, l_address);
3430
3431 end if;
3432
3433 xProgress := 'ECERULEB-170-1160';
3434 if ec_debug.G_debug_level >= 2 then
3435 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_FROM_ADDRESS');
3436 end if;
3437
3438 EXCEPTION
3439 WHEN ec_utils.program_exit then
3440 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_FROM_ADDRESS');
3441 raise;
3442
3443 WHEN OTHERS THEN
3444 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
3445 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
3446 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_FROM_ADDRESS');
3447 raise ec_utils.program_exit;
3448
3449 END Validate_Bill_From_Address;
3450
3451
3452 PROCEDURE Validate_Ship_To_Intrmd_Add IS
3453
3454 xProgress VARCHAR2(80);
3455 n_entity_id_pos NUMBER := NULL;
3456 n_org_id_pos NUMBER := NULL;
3457 n_addr_id_pos NUMBER := NULL;
3458 n_edi_location_code_pos NUMBER := NULL;
3459 n_tp_translator_code_pos NUMBER := NULL;
3460 n_tp_location_name_pos NUMBER := NULL;
3461 n_addr1_pos NUMBER := NULL;
3462 n_addr2_pos NUMBER := NULL;
3463 n_addr3_pos NUMBER := NULL;
3464 n_addr4_pos NUMBER := NULL;
3465 n_addr_alt_pos NUMBER := NULL;
3466 n_city_pos NUMBER := NULL;
3467 n_county_pos NUMBER := NULL;
3468 n_state_pos NUMBER := NULL;
3469 n_zip_pos NUMBER := NULL;
3470 n_province_pos NUMBER := NULL;
3471 n_country_pos NUMBER := NULL;
3472 n_region1_pos NUMBER := NULL;
3473 n_region2_pos NUMBER := NULL;
3474 n_region3_pos NUMBER := NULL;
3475 l_action_code ece_process_rules.action_code%TYPE;
3476 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
3477 l_address VARCHAR2(50) := 'ship to intrmd';
3478
3479 BEGIN
3480
3481 xProgress := 'ECERULEB-240-1000';
3482 if ec_debug.G_debug_level >= 2 then
3483 ec_debug.push ('ECE_RULES_PKG.VALIDATE_SHIP_TO_INTRMD_ADD');
3484 end if;
3485
3486 Get_Action_Ignore_Flag (l_action_code, l_ignore_flag);
3487
3488 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
3489
3490 -- get the pl/sql position for address columns.
3491 xProgress := 'ECERULEB-240-1002';
3492 ec_utils.find_pos (
3493 ec_utils.g_current_level,
3494 'SHIP_TO_INTRMD_CUSTOMER_ID',
3495 n_entity_id_pos);
3496
3497 xProgress := 'ECERULEB-240-1004';
3498 ec_utils.find_pos (
3499 1,
3500 'ORG_ID',
3501 n_org_id_pos);
3502
3503 xProgress := 'ECERULEB-240-1010';
3504 ec_utils.find_pos (
3505 ec_utils.g_current_level,
3506 'SHIP_TO_INTRMD_ADDRESS_ID',
3507 n_addr_id_pos);
3508
3509 xProgress := 'ECERULEB-240-1020';
3510 ec_utils.find_pos (
3511 ec_utils.g_current_level,
3512 'SHIP_TO_INTRMD_EDI_LOCATION_CODE',
3513 n_edi_location_code_pos);
3514
3515 xProgress := 'ECERULEB-240-1030';
3516 /* ec_utils.find_pos (
3517 1,
3518 'TP_TRANSLATOR_CODE',
3519 n_tp_translator_code_pos);
3520 bug2151462 */
3521 xProgress := 'ECERULEB-240-1040';
3522 ec_utils.find_pos (
3523 ec_utils.g_current_level,
3524 'SHIP_TO_INTRMD_ADDRESS_CODE',
3525 n_tp_location_name_pos);
3526
3527 xProgress := 'ECERULEB-240-1050';
3528 ec_utils.find_pos (
3529 ec_utils.g_current_level,
3530 'SHIP_TO_INTRMD_ADDRESS1',
3531 n_addr1_pos);
3532
3533 xProgress := 'ECERULEB-240-1060';
3534 ec_utils.find_pos (
3535 ec_utils.g_current_level,
3536 'SHIP_TO_INTRMD_ADDRESS2',
3537 n_addr2_pos);
3538
3539 xProgress := 'ECERULEB-240-1070';
3540 ec_utils.find_pos (
3541 ec_utils.g_current_level,
3542 'SHIP_TO_INTRMD_ADDRESS3',
3543 n_addr3_pos);
3544
3545 xProgress := 'ECERULEB-240-1080';
3546 ec_utils.find_pos (
3547 ec_utils.g_current_level,
3548 'SHIP_TO_INTRMD_ADDRESS4',
3549 n_addr4_pos);
3550
3551 xProgress := 'ECERULEB-240-1090';
3552 ec_utils.find_pos (
3553 ec_utils.g_current_level,
3554 'SHIP_TO_INTRMD_CITY',
3555 n_city_pos);
3556
3557 xProgress := 'ECERULEB-240-1100';
3558 ec_utils.find_pos (
3559 ec_utils.g_current_level,
3560 'SHIP_TO_INTRMD_POSTAL_CODE',
3561 n_zip_pos);
3562
3563 xProgress := 'ECERULEB-240-1110';
3564 ec_utils.find_pos (
3565 ec_utils.g_current_level,
3566 'SHIP_TO_INTRMD_COUNTRY_INT',
3567 n_country_pos);
3568
3569 xProgress := 'ECERULEB-240-1120';
3570 ec_utils.find_pos (
3571 ec_utils.g_current_level,
3572 'SHIP_TO_INTRMD_STATE_INT',
3573 n_state_pos);
3574
3575 xProgress := 'ECERULEB-240-1130';
3576 ec_utils.find_pos (
3577 ec_utils.g_current_level,
3578 'SHIP_TO_INTRMD_PROVINCE_INT',
3579 n_province_pos);
3580
3581 xProgress := 'ECERULEB-240-1140';
3582 ec_utils.find_pos (
3583 ec_utils.g_current_level,
3584 'SHIP_TO_INTRMD_COUNTY',
3585 n_county_pos);
3586
3587 xProgress := 'ECERULEB-240-1150';
3588 Validate_Get_Address_Info (n_entity_id_pos, n_org_id_pos, n_addr_id_pos,
3589 n_edi_location_code_pos, n_tp_translator_code_pos,
3590 n_tp_location_name_pos, n_addr1_pos, n_addr2_pos,
3591 n_addr3_pos, n_addr4_pos, n_addr_alt_pos,
3592 n_city_pos, n_county_pos, n_state_pos, n_zip_pos,
3593 n_province_pos, n_country_pos, n_region1_pos,
3594 n_region2_pos, n_region3_pos, l_address);
3595
3596 end if;
3597
3598 xProgress := 'ECERULEB-240-1160';
3599 if ec_debug.G_debug_level >= 2 then
3600 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_TO_INTRMD_ADD');
3601 end if;
3602
3603 EXCEPTION
3604 WHEN ec_utils.program_exit then
3605 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_TO_INTRMD_ADD');
3606 raise;
3607
3608 WHEN OTHERS THEN
3609 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
3610 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
3611 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_TO_INTRMD_ADD');
3612 raise ec_utils.program_exit;
3613
3614 END Validate_Ship_To_Intrmd_Add;
3615
3616 PROCEDURE Validate_Ship_To_Int_Address IS
3617
3618 xProgress VARCHAR2(80);
3619 n_entity_id_pos NUMBER := NULL;
3620 n_org_id_pos NUMBER := NULL;
3621 n_addr_id_pos NUMBER := NULL;
3622 n_edi_location_code_pos NUMBER := NULL;
3623 n_tp_translator_code_pos NUMBER := NULL;
3624 n_tp_location_name_pos NUMBER := NULL;
3625 n_addr1_pos NUMBER := NULL;
3626 n_addr2_pos NUMBER := NULL;
3627 n_addr3_pos NUMBER := NULL;
3628 n_addr4_pos NUMBER := NULL;
3629 n_addr_alt_pos NUMBER := NULL;
3630 n_city_pos NUMBER := NULL;
3631 n_county_pos NUMBER := NULL;
3632 n_state_pos NUMBER := NULL;
3633 n_zip_pos NUMBER := NULL;
3634 n_province_pos NUMBER := NULL;
3635 n_country_pos NUMBER := NULL;
3636 n_region1_pos NUMBER := NULL;
3637 n_region2_pos NUMBER := NULL;
3638 n_region3_pos NUMBER := NULL;
3639 l_action_code ece_process_rules.action_code%TYPE;
3640 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
3641 l_address VARCHAR2(50) := 'ship to internal';
3642
3643 BEGIN
3644
3645 xProgress := 'ECERULEB-180-1000';
3646 if ec_debug.G_debug_level >= 2 then
3647 ec_debug.push ('ECE_RULES_PKG.VALIDATE_SHIP_TO_INT_ADDRESS');
3648 end if;
3649
3650 Get_Action_Ignore_Flag (l_action_code, l_ignore_flag);
3651
3652 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
3653
3654 -- get the pl/sql position for address columns.
3655 xProgress := 'ECERULEB-180-1004';
3656 ec_utils.find_pos (
3657 1,
3658 'ORG_ID',
3659 n_org_id_pos,
3660 FALSE);
3661
3662 xProgress := 'ECERULEB-180-1010';
3663 ec_utils.find_pos (
3664 ec_utils.g_current_level,
3665 'SHIP_TO_INT_LOCATION_ID',
3666 n_addr_id_pos);
3667
3668 xProgress := 'ECERULEB-180-1020';
3669 ec_utils.find_pos (
3670 ec_utils.g_current_level,
3671 'SHIP_TO_INT_EDI_LOCATION_CODE',
3672 n_edi_location_code_pos);
3673
3674 xProgress := 'ECERULEB-180-1030';
3675 /* ec_utils.find_pos (
3676 1,
3677 'TP_TRANSLATOR_CODE',
3678 n_tp_translator_code_pos);
3679 bug2151462 */
3680 xProgress := 'ECERULEB-180-1040';
3681 ec_utils.find_pos (
3682 ec_utils.g_current_level,
3683 'SHIP_TO_INT_LOCATION_NAME',
3684 n_tp_location_name_pos);
3685
3686 xProgress := 'ECERULEB-180-1050';
3687 ec_utils.find_pos (
3688 ec_utils.g_current_level,
3689 'SHIP_TO_INT_ADDRESS1',
3690 n_addr1_pos);
3691
3692 xProgress := 'ECERULEB-180-1060';
3693 ec_utils.find_pos (
3694 ec_utils.g_current_level,
3695 'SHIP_TO_INT_ADDRESS2',
3696 n_addr2_pos);
3697
3698 xProgress := 'ECERULEB-180-1070';
3699 ec_utils.find_pos (
3700 ec_utils.g_current_level,
3701 'SHIP_TO_INT_ADDRESS3',
3702 n_addr3_pos);
3703
3704 xProgress := 'ECERULEB-180-1090';
3705 ec_utils.find_pos (
3706 ec_utils.g_current_level,
3707 'SHIP_TO_INT_CITY',
3708 n_city_pos);
3709
3710 xProgress := 'ECERULEB-180-1100';
3711 ec_utils.find_pos (
3712 ec_utils.g_current_level,
3713 'SHIP_TO_INT_POSTAL_CODE',
3714 n_zip_pos);
3715
3716 xProgress := 'ECERULEB-180-1110';
3717 ec_utils.find_pos (
3718 ec_utils.g_current_level,
3719 'SHIP_TO_INT_COUNTRY',
3720 n_country_pos);
3721
3722 xProgress := 'ECERULEB-180-1120';
3723 ec_utils.find_pos (
3724 ec_utils.g_current_level,
3725 'SHIP_TO_INT_REGION1',
3726 n_region1_pos);
3727
3728 xProgress := 'ECERULEB-180-1130';
3729 ec_utils.find_pos (
3730 ec_utils.g_current_level,
3731 'SHIP_TO_INT_REGION2',
3732 n_region2_pos);
3733
3734 xProgress := 'ECERULEB-180-1140';
3735 ec_utils.find_pos (
3736 ec_utils.g_current_level,
3737 'SHIP_TO_INT_REGION3',
3738 n_region3_pos);
3739
3740 xProgress := 'ECERULEB-180-1150';
3741 Validate_Get_Address_Info (n_entity_id_pos, n_org_id_pos, n_addr_id_pos,
3742 n_edi_location_code_pos, n_tp_translator_code_pos,
3743 n_tp_location_name_pos, n_addr1_pos, n_addr2_pos,
3744 n_addr3_pos, n_addr4_pos, n_addr_alt_pos,
3745 n_city_pos, n_county_pos, n_state_pos, n_zip_pos,
3746 n_province_pos, n_country_pos, n_region1_pos,
3747 n_region2_pos, n_region3_pos, l_address);
3748
3749 end if;
3750
3751 xProgress := 'ECERULEB-180-1160';
3752 if ec_debug.G_debug_level >= 2 then
3753 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_TO_INT_ADDRESS');
3754 end if;
3755
3756 EXCEPTION
3757 WHEN ec_utils.program_exit then
3758 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_TO_INT_ADDRESS');
3759 raise;
3760
3761 WHEN OTHERS THEN
3762 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
3763 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
3764 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_TO_INT_ADDRESS');
3765 raise ec_utils.program_exit;
3766
3767 END Validate_Ship_To_Int_Address;
3768
3769
3770 PROCEDURE Validate_Bill_To_Int_Address IS
3771
3772 xProgress VARCHAR2(80);
3773 n_entity_id_pos NUMBER := NULL;
3774 n_org_id_pos NUMBER := NULL;
3775 n_addr_id_pos NUMBER := NULL;
3776 n_edi_location_code_pos NUMBER := NULL;
3777 n_tp_translator_code_pos NUMBER := NULL;
3778 n_tp_location_name_pos NUMBER := NULL;
3779 n_addr1_pos NUMBER := NULL;
3780 n_addr2_pos NUMBER := NULL;
3781 n_addr3_pos NUMBER := NULL;
3782 n_addr4_pos NUMBER := NULL;
3783 n_addr_alt_pos NUMBER := NULL;
3784 n_city_pos NUMBER := NULL;
3785 n_county_pos NUMBER := NULL;
3786 n_state_pos NUMBER := NULL;
3787 n_zip_pos NUMBER := NULL;
3788 n_province_pos NUMBER := NULL;
3789 n_country_pos NUMBER := NULL;
3790 n_region1_pos NUMBER := NULL;
3791 n_region2_pos NUMBER := NULL;
3792 n_region3_pos NUMBER := NULL;
3793 l_action_code ece_process_rules.action_code%TYPE;
3794 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
3795 l_address VARCHAR2(50) := 'bill to internal';
3796
3797 BEGIN
3798
3799 xProgress := 'ECERULEB-190-1000';
3800 if ec_debug.G_debug_level >= 2 then
3801 ec_debug.push ('ECE_RULES_PKG.VALIDATE_BILL_TO_INT_ADDRESS');
3802 end if;
3803
3804 Get_Action_Ignore_Flag (l_action_code, l_ignore_flag);
3805
3806 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
3807
3808 -- get the pl/sql position for address columns.
3809 xProgress := 'ECERULEB-190-1004';
3810 ec_utils.find_pos (
3811 1,
3812 'ORG_ID',
3813 n_org_id_pos,
3814 FALSE);
3815
3816 xProgress := 'ECERULEB-190-1010';
3817 ec_utils.find_pos (
3818 ec_utils.g_current_level,
3819 'BILL_TO_INT_LOCATION_ID',
3820 n_addr_id_pos);
3821
3822 xProgress := 'ECERULEB-190-1020';
3823 ec_utils.find_pos (
3824 ec_utils.g_current_level,
3825 'BILL_TO_INT_EDI_LOCATION_CODE',
3826 n_edi_location_code_pos);
3827
3828 xProgress := 'ECERULEB-190-1030';
3829 /* ec_utils.find_pos (
3830 1,
3831 'TP_TRANSLATOR_CODE',
3832 n_tp_translator_code_pos);
3833 bug2151462 */
3834 xProgress := 'ECERULEB-190-1040';
3835 ec_utils.find_pos (
3836 ec_utils.g_current_level,
3837 'BILL_TO_INT_LOCATION_NAME',
3838 n_tp_location_name_pos);
3839
3840 xProgress := 'ECERULEB-190-1050';
3841 ec_utils.find_pos (
3842 ec_utils.g_current_level,
3843 'BILL_TO_INT_ADDRESS1',
3844 n_addr1_pos);
3845
3846 xProgress := 'ECERULEB-190-1060';
3847 ec_utils.find_pos (
3848 ec_utils.g_current_level,
3849 'BILL_TO_INT_ADDRESS2',
3850 n_addr2_pos);
3851
3852 xProgress := 'ECERULEB-190-1070';
3853 ec_utils.find_pos (
3854 ec_utils.g_current_level,
3855 'BILL_TO_INT_ADDRESS3',
3856 n_addr3_pos);
3857
3858 xProgress := 'ECERULEB-190-1090';
3859 ec_utils.find_pos (
3860 ec_utils.g_current_level,
3861 'BILL_TO_INT_CITY',
3862 n_city_pos);
3863
3864 xProgress := 'ECERULEB-190-1100';
3865 ec_utils.find_pos (
3866 ec_utils.g_current_level,
3867 'BILL_TO_INT_POSTAL_CODE',
3868 n_zip_pos);
3869
3870 xProgress := 'ECERULEB-190-1110';
3871 ec_utils.find_pos (
3872 ec_utils.g_current_level,
3873 'BILL_TO_INT_COUNTRY',
3874 n_country_pos);
3875
3876 xProgress := 'ECERULEB-190-1120';
3877 ec_utils.find_pos (
3878 ec_utils.g_current_level,
3879 'BILL_TO_INT_REGION1',
3880 n_region1_pos);
3881
3882 xProgress := 'ECERULEB-190-1130';
3883 ec_utils.find_pos (
3884 ec_utils.g_current_level,
3885 'BILL_TO_INT_REGION2',
3886 n_region2_pos);
3887
3888 xProgress := 'ECERULEB-190-1140';
3889 ec_utils.find_pos (
3890 ec_utils.g_current_level,
3891 'BILL_TO_INT_REGION3',
3892 n_region3_pos);
3893
3894 xProgress := 'ECERULEB-190-1150';
3895 Validate_Get_Address_Info (n_entity_id_pos, n_org_id_pos, n_addr_id_pos,
3896 n_edi_location_code_pos, n_tp_translator_code_pos,
3897 n_tp_location_name_pos, n_addr1_pos, n_addr2_pos,
3898 n_addr3_pos, n_addr4_pos, n_addr_alt_pos,
3899 n_city_pos, n_county_pos, n_state_pos, n_zip_pos,
3900 n_province_pos, n_country_pos, n_region1_pos,
3901 n_region2_pos, n_region3_pos, l_address);
3902
3903 end if;
3904
3905 xProgress := 'ECERULEB-190-1160';
3906 if ec_debug.G_debug_level >= 2 then
3907 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_TO_INT_ADDRESS');
3908 end if;
3909
3910 EXCEPTION
3911 WHEN ec_utils.program_exit then
3912 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_TO_INT_ADDRESS');
3913 raise;
3914
3915 WHEN OTHERS THEN
3916 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
3917 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
3918 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_TO_INT_ADDRESS');
3919 raise ec_utils.program_exit;
3920
3921 END Validate_Bill_To_Int_Address;
3922
3923
3924 PROCEDURE Validate_Ship_From_Int_Address IS
3925
3926 xProgress VARCHAR2(80);
3927 n_entity_id_pos NUMBER := NULL;
3928 n_org_id_pos NUMBER := NULL;
3929 n_addr_id_pos NUMBER := NULL;
3930 n_edi_location_code_pos NUMBER := NULL;
3931 n_tp_translator_code_pos NUMBER := NULL;
3932 n_tp_location_name_pos NUMBER := NULL;
3933 n_addr1_pos NUMBER := NULL;
3934 n_addr2_pos NUMBER := NULL;
3935 n_addr3_pos NUMBER := NULL;
3936 n_addr4_pos NUMBER := NULL;
3937 n_addr_alt_pos NUMBER := NULL;
3938 n_city_pos NUMBER := NULL;
3939 n_county_pos NUMBER := NULL;
3940 n_state_pos NUMBER := NULL;
3941 n_zip_pos NUMBER := NULL;
3942 n_province_pos NUMBER := NULL;
3943 n_country_pos NUMBER := NULL;
3944 n_region1_pos NUMBER := NULL;
3945 n_region2_pos NUMBER := NULL;
3946 n_region3_pos NUMBER := NULL;
3947 l_action_code ece_process_rules.action_code%TYPE;
3948 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
3949 l_address VARCHAR2(50) := 'ship from internal';
3950
3951 BEGIN
3952
3953 xProgress := 'ECERULEB-200-1000';
3954
3955 if ec_debug.G_debug_level >= 2 then
3956 ec_debug.push ('ECE_RULES_PKG.VALIDATE_SHIP_FROM_INT_ADDRESS');
3957 end if;
3958
3959 Get_Action_Ignore_Flag (l_action_code, l_ignore_flag);
3960
3961 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
3962
3963 -- get the pl/sql position for address columns.
3964 xProgress := 'ECERULEB-200-1004';
3965 ec_utils.find_pos (
3966 1,
3967 'ORG_ID',
3968 n_org_id_pos,
3969 FALSE);
3970
3971 xProgress := 'ECERULEB-200-1010';
3972 ec_utils.find_pos (
3973 ec_utils.g_current_level,
3974 'SHIP_FROM_INT_LOCATION_ID',
3975 n_addr_id_pos);
3976
3977 xProgress := 'ECERULEB-200-1020';
3978 ec_utils.find_pos (
3979 ec_utils.g_current_level,
3980 'SHIP_FROM_INT_EDI_LOCATION_CODE',
3981 n_edi_location_code_pos);
3982
3983 xProgress := 'ECERULEB-200-1030';
3984 /* ec_utils.find_pos (
3985 1,
3986 'TP_TRANSLATOR_CODE',
3987 n_tp_translator_code_pos);
3988 bug 2151462*/
3989
3990 /* Bug 2570369 : Using the n_tp_translator_code_pos variable to store the position of
3991 column SHIP_FROM_INT_ORGANIZATION_ID
3992 Creating a new variable to store the position will require a change in
3993 in the validate_get_address_info procedure also
3994 */
3995 ec_utils.find_pos (
3996 ec_utils.g_current_level,
3997 'SHIP_FROM_INT_ORGANIZATION_ID',
3998 n_tp_translator_code_pos);
3999
4000 xProgress := 'ECERULEB-200-1040';
4001 ec_utils.find_pos (
4002 ec_utils.g_current_level,
4003 'SHIP_FROM_INT_LOCATION_NAME',
4004 n_tp_location_name_pos);
4005
4006 xProgress := 'ECERULEB-200-1050';
4007 ec_utils.find_pos (
4008 ec_utils.g_current_level,
4009 'SHIP_FROM_INT_ADDRESS1',
4010 n_addr1_pos);
4011
4012 xProgress := 'ECERULEB-200-1060';
4013 ec_utils.find_pos (
4014 ec_utils.g_current_level,
4015 'SHIP_FROM_INT_ADDRESS2',
4016 n_addr2_pos);
4017
4018 xProgress := 'ECERULEB-200-1070';
4019 ec_utils.find_pos (
4020 ec_utils.g_current_level,
4021 'SHIP_FROM_INT_ADDRESS3',
4022 n_addr3_pos);
4023
4024 xProgress := 'ECERULEB-200-1090';
4025 ec_utils.find_pos (
4026 ec_utils.g_current_level,
4027 'SHIP_FROM_INT_CITY',
4028 n_city_pos);
4029
4030 xProgress := 'ECERULEB-200-1100';
4031 ec_utils.find_pos (
4032 ec_utils.g_current_level,
4033 'SHIP_FROM_INT_POSTAL_CODE',
4034 n_zip_pos);
4035
4036 xProgress := 'ECERULEB-200-1110';
4037 ec_utils.find_pos (
4038 ec_utils.g_current_level,
4039 'SHIP_FROM_INT_COUNTRY',
4040 n_country_pos);
4041
4042 xProgress := 'ECERULEB-200-1120';
4043 ec_utils.find_pos (
4044 ec_utils.g_current_level,
4045 'SHIP_FROM_INT_REGION1',
4046 n_region1_pos);
4047
4048 xProgress := 'ECERULEB-200-1130';
4049 ec_utils.find_pos (
4050 ec_utils.g_current_level,
4051 'SHIP_FROM_INT_REGION2',
4052 n_region2_pos);
4053
4054 xProgress := 'ECERULEB-200-1140';
4055 ec_utils.find_pos (
4056 ec_utils.g_current_level,
4057 'SHIP_FROM_INT_REGION3',
4058 n_region3_pos);
4059
4060 xProgress := 'ECERULEB-200-1150';
4061 Validate_Get_Address_Info (n_entity_id_pos, n_org_id_pos, n_addr_id_pos,
4062 n_edi_location_code_pos, n_tp_translator_code_pos,
4063 n_tp_location_name_pos, n_addr1_pos, n_addr2_pos,
4064 n_addr3_pos, n_addr4_pos, n_addr_alt_pos,
4065 n_city_pos, n_county_pos, n_state_pos, n_zip_pos,
4066 n_province_pos, n_country_pos, n_region1_pos,
4067 n_region2_pos, n_region3_pos, l_address);
4068
4069 end if;
4070
4071 xProgress := 'ECERULEB-200-1160';
4072 if ec_debug.G_debug_level >= 2 then
4073 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_FROM_INT_ADDRESS');
4074 end if;
4075 EXCEPTION
4076 WHEN ec_utils.program_exit then
4077 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_FROM_INT_ADDRESS');
4078 raise;
4079
4080 WHEN OTHERS THEN
4081 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
4082 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
4083 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_SHIP_FROM_INT_ADDRESS');
4084 raise ec_utils.program_exit;
4085
4086 END Validate_Ship_From_Int_Address;
4087
4088
4089 PROCEDURE Validate_Bill_From_Int_Address IS
4090
4091 xProgress VARCHAR2(80);
4092 n_entity_id_pos NUMBER := NULL;
4093 n_org_id_pos NUMBER := NULL;
4094 n_addr_id_pos NUMBER := NULL;
4095 n_edi_location_code_pos NUMBER := NULL;
4096 n_tp_translator_code_pos NUMBER := NULL;
4097 n_tp_location_name_pos NUMBER := NULL;
4098 n_addr1_pos NUMBER := NULL;
4099 n_addr2_pos NUMBER := NULL;
4100 n_addr3_pos NUMBER := NULL;
4101 n_addr4_pos NUMBER := NULL;
4102 n_addr_alt_pos NUMBER := NULL;
4103 n_city_pos NUMBER := NULL;
4104 n_county_pos NUMBER := NULL;
4105 n_state_pos NUMBER := NULL;
4106 n_zip_pos NUMBER := NULL;
4107 n_province_pos NUMBER := NULL;
4108 n_country_pos NUMBER := NULL;
4109 n_region1_pos NUMBER := NULL;
4110 n_region2_pos NUMBER := NULL;
4111 n_region3_pos NUMBER := NULL;
4112 l_action_code ece_process_rules.action_code%TYPE;
4113 l_ignore_flag ece_rule_violations.ignore_flag%TYPE;
4114 l_address VARCHAR2(50) := 'bill from internal';
4115
4116 BEGIN
4117
4118 xProgress := 'ECERULEB-210-1000';
4119 if ec_debug.G_debug_level >= 2 then
4120 ec_debug.push ('ECE_RULES_PKG.VALIDATE_BILL_FROM_INT_ADDRESS');
4121 end if;
4122
4123 Get_Action_Ignore_Flag (l_action_code, l_ignore_flag);
4124
4125 if (l_action_code <> g_disabled and l_ignore_flag = 'N') then
4126
4127 -- get the pl/sql position for address columns.
4128 xProgress := 'ECERULEB-210-1004';
4129 ec_utils.find_pos (
4130 1,
4131 'ORG_ID',
4132 n_org_id_pos,
4133 FALSE);
4134
4135 xProgress := 'ECERULEB-210-1010';
4136 ec_utils.find_pos (
4137 ec_utils.g_current_level,
4138 'BILL_FROM_INT_LOCATION_ID',
4139 n_addr_id_pos);
4140
4141 xProgress := 'ECERULEB-210-1020';
4142 ec_utils.find_pos (
4143 ec_utils.g_current_level,
4144 'BILL_FROM_INT_EDI_LOCATION_CODE',
4145 n_edi_location_code_pos);
4146
4147 xProgress := 'ECERULEB-210-1030';
4148 /* ec_utils.find_pos (
4149 1,
4150 'TP_TRANSLATOR_CODE',
4151 n_tp_translator_code_pos);
4152 bug2151462 */
4153
4154 xProgress := 'ECERULEB-210-1040';
4155 ec_utils.find_pos (
4156 ec_utils.g_current_level,
4157 'BILL_FROM_INT_LOCATION_NAME',
4158 n_tp_location_name_pos);
4159
4160 xProgress := 'ECERULEB-210-1050';
4161 ec_utils.find_pos (
4162 ec_utils.g_current_level,
4163 'BILL_FROM_INT_ADDRESS1',
4164 n_addr1_pos);
4165
4166 xProgress := 'ECERULEB-210-1060';
4167 ec_utils.find_pos (
4168 ec_utils.g_current_level,
4169 'BILL_FROM_INT_ADDRESS2',
4170 n_addr2_pos);
4171
4172 xProgress := 'ECERULEB-210-1070';
4173 ec_utils.find_pos (
4174 ec_utils.g_current_level,
4175 'BILL_FROM_INT_ADDRESS3',
4176 n_addr3_pos);
4177
4178 xProgress := 'ECERULEB-210-1090';
4179 ec_utils.find_pos (
4180 ec_utils.g_current_level,
4181 'BILL_FROM_INT_CITY',
4182 n_city_pos);
4183
4184 xProgress := 'ECERULEB-210-1100';
4185 ec_utils.find_pos (
4186 ec_utils.g_current_level,
4187 'BILL_FROM_INT_POSTAL_CODE',
4188 n_zip_pos);
4189
4190 xProgress := 'ECERULEB-210-1110';
4191 ec_utils.find_pos (
4192 ec_utils.g_current_level,
4193 'BILL_FROM_INT_COUNTRY',
4194 n_country_pos);
4195
4196 xProgress := 'ECERULEB-210-1120';
4197 ec_utils.find_pos (
4198 ec_utils.g_current_level,
4199 'BILL_FROM_INT_REGION1',
4200 n_region1_pos);
4201
4202 xProgress := 'ECERULEB-210-1130';
4203 ec_utils.find_pos (
4204 ec_utils.g_current_level,
4205 'BILL_FROM_INT_REGION2',
4206 n_region2_pos);
4207
4208 xProgress := 'ECERULEB-210-1140';
4209 ec_utils.find_pos (
4210 ec_utils.g_current_level,
4211 'BILL_FROM_INT_REGION3',
4212 n_region3_pos);
4213
4214 xProgress := 'ECERULEB-210-1150';
4215 Validate_Get_Address_Info (n_entity_id_pos, n_org_id_pos, n_addr_id_pos,
4216 n_edi_location_code_pos, n_tp_translator_code_pos,
4217 n_tp_location_name_pos, n_addr1_pos, n_addr2_pos,
4218 n_addr3_pos, n_addr4_pos, n_addr_alt_pos,
4219 n_city_pos, n_county_pos, n_state_pos, n_zip_pos,
4220 n_province_pos, n_country_pos, n_region1_pos,
4221 n_region2_pos, n_region3_pos, l_address);
4222
4223 end if;
4224
4225 xProgress := 'ECERULEB-210-1160';
4226 if ec_debug.G_debug_level >= 2 then
4227 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_FROM_INT_ADDRESS');
4228 end if;
4229
4230 EXCEPTION
4231 WHEN ec_utils.program_exit then
4232 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_FROM_INT_ADDRESS');
4233 raise;
4234
4235 WHEN OTHERS THEN
4236 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
4237 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
4238 ec_debug.pop ('ECE_RULES_PKG.VALIDATE_BILL_FROM_INT_ADDRESS');
4239 raise ec_utils.program_exit;
4240
4241 END Validate_Bill_From_Int_Address;
4242
4243
4244 PROCEDURE Validate_Form_Simple_Lookup (
4245 p_column_name IN VARCHAR2,
4246 p_table_name IN VARCHAR2,
4247 p_where_clause IN VARCHAR2,
4248 x_valid OUT NOCOPY BOOLEAN) IS
4249
4250 l_select VARCHAR2(32000);
4251 l_from VARCHAR2(32000);
4252 l_where VARCHAR2(32000);
4253 l_sel_c INTEGER;
4254
4255 BEGIN
4256
4257 x_valid := True;
4258
4259 l_select := ' SELECT ' || p_column_name;
4260 l_from := ' FROM ' || p_table_name;
4261 l_select := l_select || l_from;
4262
4263 if (p_where_clause is not NULL) then
4264 l_where := ' WHERE ' || p_where_clause;
4265 l_select := l_select || l_where;
4266 end if;
4267
4268 l_sel_c := dbms_sql.open_cursor;
4269
4270 BEGIN
4271 dbms_sql.parse (l_sel_c, l_select, dbms_sql.native);
4272 EXCEPTION
4273 WHEN OTHERS then
4274 x_valid := False;
4275 END;
4276
4277 dbms_sql.close_cursor (l_sel_c);
4278
4279 EXCEPTION
4280 WHEN OTHERS THEN
4281 x_valid := False;
4282 if (dbms_sql.is_open(l_sel_c)) then
4283 dbms_sql.close_cursor (l_sel_c);
4284 end if;
4285 app_exception.raise_exception;
4286
4287 END Validate_Form_Simple_Lookup;
4288
4289
4290 PROCEDURE Get_Action_Ignore_Flag (
4291 x_action_code OUT NOCOPY VARCHAR2,
4292 x_ignore_flag OUT NOCOPY VARCHAR2) IS
4293
4294 xProgress VARCHAR2(80);
4295 l_rule_type VARCHAR2(80) := g_p_invalid_addr;
4296 l_rule_id NUMBER;
4297 l_violation_level VARCHAR2(10) := g_process_rule;
4298 no_process_rule_info EXCEPTION;
4299
4300 CURSOR c_rule_info(
4301 p_rule_type VARCHAR2,
4302 p_transaction_type VARCHAR2,
4303 p_map_id NUMBER) IS
4304 select process_rule_id, action_code
4305 from ece_process_rules
4306 where transaction_type = p_transaction_type and
4307 map_id = p_map_id and
4308 rule_type = p_rule_type;
4309
4310 CURSOR c_ignore_flag(
4311 p_document_id NUMBER,
4312 p_rule_id NUMBER) IS
4313 select ignore_flag
4314 from ece_rule_violations
4315 where document_id = p_document_id and
4316 rule_id = p_rule_id and
4317 violation_level = l_violation_level;
4318
4319 BEGIN
4320 if ec_debug.G_debug_level >= 2 then
4321 ec_debug.push ('ECE_RULES_PKG.GET_ACTION_IGNORE_FLAG');
4322 ec_debug.pl (3, 'g_transaction_type', ec_utils.g_transaction_type);
4323 ec_debug.pl (3, 'g_document_id', ec_utils.g_document_id);
4324 end if;
4325
4326 xProgress := 'ECERULEB-220-1000';
4327 x_action_code := g_skip_doc;
4328
4329 xProgress := 'ECERULEB-220-1010';
4330 open c_rule_info (l_rule_type, ec_utils.g_transaction_type, ec_utils.g_map_id);
4331 fetch c_rule_info into l_rule_id, x_action_code;
4332
4333 if c_rule_info%NOTFOUND then
4334 raise no_process_rule_info;
4335 end if;
4336 close c_rule_info;
4337
4338 if ec_debug.G_debug_level = 3 then
4339 ec_debug.pl (3, 'rule_type', l_rule_type);
4340 ec_debug.pl (3, 'action_code', x_action_code);
4341 end if;
4342
4343 xProgress := 'ECERULEB-220-1020';
4344 open c_ignore_flag (ec_utils.g_document_id, l_rule_id);
4345 fetch c_ignore_flag into x_ignore_flag;
4346
4347 if c_ignore_flag%NOTFOUND then
4348 x_ignore_flag := 'N';
4349 end if;
4350 close c_ignore_flag;
4351
4352 if ec_debug.G_debug_level >= 2 then
4353 ec_debug.pl (3, 'ignore_flag', x_ignore_flag);
4354
4355 ec_debug.pop ('ECE_RULES_PKG.GET_ACTION_IGNORE_FLAG');
4356 end if;
4357
4358 EXCEPTION
4359 WHEN no_process_rule_info then
4360 if (c_rule_info%ISOPEN) then
4361 close c_rule_info;
4362 end if;
4363 ec_debug.pl (0, 'EC', 'ECE_NO_PROCESS_RULE',
4364 'TRANSACTION_TYPE', ec_utils.g_transaction_type,
4365 'RULE_TYPE', l_rule_type);
4366 ec_debug.pop ('ECE_RULES_PKG.GET_ACTION_IGNORE_FLAG');
4367 raise ec_utils.program_exit;
4368
4369 WHEN OTHERS THEN
4370 if (c_rule_info%ISOPEN) then
4371 close c_rule_info;
4372 end if;
4373 if (c_ignore_flag%ISOPEN) then
4374 close c_ignore_flag;
4375 end if;
4376 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR','PROGRESS_LEVEL', xProgress);
4377 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
4378 ec_debug.pop ('ECE_RULES_PKG.GET_ACTION_IGNORE_FLAG');
4379 raise ec_utils.program_exit;
4380
4381 END Get_Action_Ignore_Flag;
4382
4383
4384 END ECE_RULES_PKG;