1 package body HZ_IMP_ADDRESS_VAL_PKG as
2 /*$Header: ARHADRVB.pls 120.20 2005/10/30 04:16:56 appldev noship $*/
3
4 -------------------------------------------------------------------
5 -- The procedure,address_validation_child will be called by
6 -- address_validation_main procedure for each batch.
7 -- This procedure will intern call 'oracle.apps.ar.hz.import.outboundxml'
8 -- event subscription.
9 ------------------------------------------------------------------------
10 procedure address_validation_child(
11 Errbuf OUT NOCOPY VARCHAR2,
12 Retcode OUT NOCOPY VARCHAR2,
13 p_batch_id IN NUMBER,
14 P_VAL_SUBSET_ID IN NUMBER DEFAULT NULL,
15 p_country_code IN VARCHAR2 DEFAULT NULL,
16 p_module IN VARCHAR2 DEFAULT NULL,
17 p_module_id IN NUMBER DEFAULT NULL,
18 P_OVERWRITE_THRESHOLD IN VARCHAR2 DEFAULT NULL,
19 P_ORIG_SYSTEM IN VARCHAR2 DEFAULT NULL,
20 P_ADAPTER_ID IN NUMBER DEFAULT NULL)
21 is
22
23 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
24 l_return_status VARCHAR2(30);
25 l_msg_count NUMBER;
26 l_msg_data VARCHAR2(2000);
27 l_request_id NUMBER;
28 l_event_key VARCHAR2(30);
29 l_adapter_content_source hz_adapters.ADAPTER_CONTENT_SOURCE%type;
30 l_adapter_id hz_adapters.adapter_id%type;
31 p_subset_id NUMBER;
32 begin
33 --
34 ---Set parameter mapcode in parameter list
35 --
36 wf_event.AddParameterToList(
37 p_name => 'ECX_MAP_CODE',
38 p_value => 'TCA_IMP_OAG_OUTBOUND',
39 p_parameterlist => l_parameter_list);
40
41 --
42 ---Set parameter where-clause of mapcode in parameter list
43 --
44 wf_event.AddParameterToList(
45 p_name => 'P_BATCH_ID',
46 p_value => p_batch_id,
47 p_parameterlist => l_parameter_list);
48 --
49 ---Set parameter where-clause of mapcode in parameter list
50 --
51 wf_event.AddParameterToList(
52 p_name => 'P_ORIG_SYSTEM_REFERENCE',
53 p_value => P_ORIG_SYSTEM,
54 p_parameterlist => l_parameter_list);
55 --
56 ---Set parameter where-clause of mapcode in parameter list
57 --
58 wf_event.AddParameterToList(
59 p_name => 'P_VAL_SUBSET_ID',
60 p_value => P_VAL_SUBSET_ID,
61 p_parameterlist => l_parameter_list);
62
63 --
64 ---Set parameter where-clause of mapcode in parameter list
65 --
66 wf_event.AddParameterToList(
67 p_name => 'P_OVERWRITE_THRESHOLD',
68 p_value => P_OVERWRITE_THRESHOLD,
69 p_parameterlist => l_parameter_list);
70
71 --
72 ---Set parameter where-clause of mapcode in parameter list
73 --
74 wf_event.AddParameterToList(
75 p_name => 'P_ADAPTER_ID',
76 p_value => P_ADAPTER_ID,
77 p_parameterlist => l_parameter_list);
78
79 --
80 -- Raise event for outbound XML
81 --
82 l_event_key := 'HZ_IMP_ADDROUT-'||to_char(P_ADAPTER_ID)||'-'||to_char(p_batch_id)||'-'||to_char(p_subset_id);
83 wf_event.raise(
84 p_event_name => 'oracle.apps.ar.hz.import.outboundxml',
85 p_event_key => l_event_key,
86 p_event_data => NULL,
87 p_parameters => l_parameter_list,
88 p_send_date => null);
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 FND_FILE.put_line(fnd_file.log,'Others Error: Aborting Address Validation Child for this batch');
93 FND_FILE.put_line(fnd_file.log,'SQL Error: '||SQLERRM);
94 raise;
95 END address_validation_child;
96
97 -----------------------------------------------------------------------
98 -- The procedure,address_validation_main will be called by
99 -- UI Console wrapper concurrent program for each batch.
100 -- This procedure will intern call address_validation_child cp
101 --
102 ------------------------------------------------------------------------
103
104 procedure address_validation_main(
105 Errbuf OUT NOCOPY VARCHAR2,
106 Retcode OUT NOCOPY VARCHAR2,
107 p_batch_id IN NUMBER) is
108
109 TYPE BATCH_ID IS TABLE OF HZ_IMP_ADDRESSES_INT.BATCH_ID%TYPE;
110 TYPE ADAPTER_ID IS TABLE OF HZ_ADAPTERS.ADAPTER_ID%TYPE;
111 TYPE ADAPTER_CONTENT_SOURCE IS TABLE OF HZ_ADAPTERS.ADAPTER_CONTENT_SOURCE%TYPE;
112 TYPE BATCH_SIZE IS TABLE OF HZ_ADAPTERS.DEFAULT_BATCH_SIZE%TYPE;
113 TYPE ROWID IS TABLE OF VARCHAR2(50);
114 TYPE REQUEST_ID IS TABLE OF NUMBER;
115 TYPE THRESHOLD_STATUS IS TABLE OF HZ_ADAPTERS.DEFAULT_REPLACE_STATUS_LEVEL%TYPE;
116 l_adapter_id ADAPTER_ID;
117 l_adapter_content_source ADAPTER_CONTENT_SOURCE;
118 l_batch_size BATCH_SIZE;
119 l_batch_id NUMBER := p_batch_id;
120 l_row_id ROWID;
121 l_from_rowid ROWID;
122 l_to_rowid ROWID;
123 l_request_id number;--REQUEST_ID;
124 l_default_replace_level THRESHOLD_STATUS;
125
126 cursor verify_imp_adapter is select count(ADAPTER_CONTENT_SOURCE)
127 from hz_imp_adapters
128 where batch_id = p_batch_id
129 and send_flag = 'Y';
130
131 cursor imp_adapter_cur is select adapter_id,adapter_content_source,nvl(MAXIMUM_BATCH_SIZE,DEFAULT_BATCH_SIZE),
132 DEFAULT_REPLACE_STATUS_LEVEL
133 from hz_adapters
134 where enabled_flag = 'Y'
135 and adapter_content_source in
136 (select distinct ADAPTER_CONTENT_SOURCE
137 from hz_imp_adapters
138 where batch_id = p_batch_id
139 and send_flag = 'Y') ;
140
141 cursor imp_addresss_cur(v_adapter varchar2) is select rowid
142 from hz_imp_addresses_int
143 where batch_id = p_batch_id
144 and country in
145 (select country_code from hz_imp_adapters
146 where adapter_content_source = v_adapter
147 and batch_id = p_batch_id
148 and send_flag = 'Y');
149 /* or
150 not exists (select 'X' from fnd_territories
151 where TERRITORY_CODE = country)
152 );
153 OR
154 country not in
155 (select territory_code from hz_adapter_territories
156 where adapter_id = v_adapter_id
157 and default_flag = 'Y');*/
158
159 cursor adapter_cur is select adapter_id,adapter_content_source,nvl(MAXIMUM_BATCH_SIZE,DEFAULT_BATCH_SIZE),
160 DEFAULT_REPLACE_STATUS_LEVEL
161 from hz_adapters
162 where enabled_flag = 'Y'
163 and adapter_id in
164 (select distinct HZ_LOCATION_SERVICES_PUB.get_adapter_id(null,country)
165 from hz_imp_addresses_int
166 where batch_id = p_batch_id
167 --Bug No:3347996.Added conditions to overcome unnecessary calls to
168 --HZ_LOCATION_SERVICES_PUB.get_adapter_id.
169 and country in (select distinct territory_code
170 from hz_adapter_territories t,
171 hz_adapters ad
172 where ad.adapter_id =t.adapter_id
173 and ad.enabled_flag='Y'
174 and t.enabled_flag='Y'
175 and t.default_flag = 'Y')
176 and exists (select 'X' from fnd_territories
177 where TERRITORY_CODE = country)
178 ----End of Bug No:3347996
179 );
180
181 cursor addresss_cur(v_adapter_id number) is select rowid
182 from hz_imp_addresses_int
183 where batch_id = p_batch_id
184 and country in
185 (select territory_code from hz_adapter_territories t,
186 hz_adapters ad
187 where t.adapter_id = v_adapter_id
188 and ad.adapter_id =t.adapter_id
189 and ad.enabled_flag='Y'
190 and t.enabled_flag='Y' --Bug No:3347996
191 and t.default_flag = 'Y')
192 and exists (select 'X' from fnd_territories
193 where TERRITORY_CODE = country);
194
195 cursor addresss_cur_default is select rowid
196 from hz_imp_addresses_int
197 where batch_id = p_batch_id
198 --Bug No:3347996
199 and country not in ((select distinct territory_code
200 from hz_adapter_territories t,
201 hz_adapters ad
202 where ad.adapter_id=t.adapter_id
203 and ad.enabled_flag='Y'
204 and t.enabled_flag='Y'
205 and t.default_flag = 'Y'
206 )UNION
207 (select distinct country_code
208 from hz_imp_adapters
209 where batch_id = p_batch_id
210 and send_flag= 'Y')
211 );
212 --End of --Bug No:3347996
213
214 --Bug No:3347996--and not exists (select 'X' from fnd_territories
215 --Bug No:3347996 where TERRITORY_CODE = country);
216
217 cursor default_adapter is select adapter_id,adapter_content_source,nvl(MAXIMUM_BATCH_SIZE,DEFAULT_BATCH_SIZE),
218 DEFAULT_REPLACE_STATUS_LEVEL
219 from hz_adapters
220 where enabled_flag = 'Y'
221 and adapter_id = to_number(fnd_profile.value('HZ_DEFAULT_LOC_ADAPTER'));
222
223 cursor validated_address_cur is
224 select country,count(decode(ACCEPT_STANDARDIZED_FLAG,'Y','Y',null))Validated_rec,
225 count(decode(ACCEPT_STANDARDIZED_FLAG,'N','N',null))Failed_rec,
226 count(ACCEPT_STANDARDIZED_FLAG)Total_rec
227 from hz_imp_addresses_int
228 where batch_id = p_batch_id
229 and ACCEPT_STANDARDIZED_FLAG is not null
230 group by country;
231
232 cursor find_imp_country(p_country_code varchar2) is select 'X' from hz_imp_adapters
233 where batch_id = p_batch_id
234 and country_code = p_country_code;
235 l_count NUMBER;
236 l_counter NUMBER :=0;
237 l_error_count NUMBER :=0;
238 l_success_count NUMBER :=0;
239 l_last_fetch boolean;
240 l_adapter_log_id NUMBER;
241 l_boolean BOOLEAN;
242 l_request_status boolean;
243 l_dev_phase varchar2(30); --Bug No: 3778263
244
245 l_phase varchar2(80);
246 l_status varchar2(80);
247 l_dev_status varchar2(80);
248 l_message varchar2(250);
249 l_orig_system varchar2(30);
250 v_completion boolean;
251 l_completed_request number:=0;
252 l_total_request number;
253 l_m_request_id NUMBER;
254 l_request_data varchar2(25000);
255
256 l_posi1 NUMBER;
257 l_times NUMBER;
258 l_sub_request_ids VARCHAR2(200);
259 l_sub_request_id NUMBER;
260 l_adapter_last_fetch boolean := false;
261 l_import_adapter_def varchar2(1) :='N';
262 l_dummy1 varchar2(2);
263 l_adapter_found BOOLEAN := FALSE; --Bug No: 3535366
264 BEGIN
265 If p_batch_id is null then
266 FND_FILE.put_line(fnd_file.log,'Aborting Address Validation Main for this batch as no batch found');
267 return; --Nothing to process
268 end if;
269
270 l_request_data := FND_CONC_GLOBAL.REQUEST_DATA;
271 l_last_fetch := FALSE;
272 --l_request_id := REQUEST_ID() ;
273 l_counter := 1;
274
275 -- this is not first run
276 IF l_request_data IS NOT NULL THEN
277 l_success_count := 0;
278 --l_error_count :=0;
279 --l_completed_request :=0;
280 l_sub_request_ids := l_request_data;
281 /*
282 Loop -- start of checking status of child CP
283 l_posi1 := INSTRB(l_sub_request_ids, ' ', 1, 1);
284 l_sub_request_id := TO_NUMBER(SUBSTRB(l_request_data, 1, l_posi1-1));
285 --for k in l_request_id.first..l_request_id.last loop
286 l_request_status :=FND_CONCURRENT.get_request_status(l_sub_request_id,'','',l_phase,l_status,l_dev_phase,l_dev_status,l_message);
287 if l_request_status = FALSE or l_dev_phase ='COMPLETE' then
288 l_completed_request := l_completed_request +1;
289 if l_dev_status='ERROR' then
290 l_error_count := l_error_count +1;
291 else -- l_dev_status='NORMAL' then
292 l_success_count := l_success_count +1;
293 end if;
294 end if;
295 end loop;
296 exit when l_completed_request = l_total_request;
297 end loop; -- end of checking status of child CP
298 */
299 --------------------------
300
301 fnd_file.put_line(FND_FILE.LOG, 'Addrval : l_sub_request_ids='||l_sub_request_ids);
302
303 WHILE l_sub_request_ids IS NOT NULL LOOP
304 l_posi1 := INSTRB(l_sub_request_ids, ' ', 1, 1);
305 l_sub_request_id := TO_NUMBER(SUBSTRB(l_sub_request_ids, 1, l_posi1-1));
306 --fnd_file.put_line(FND_FILE.LOG, 'Addrval l_sub_request_id='||l_sub_request_id);
307 -- Check return status of validation request.
308 IF (FND_CONCURRENT.GET_REQUEST_STATUS(
309 request_id => l_sub_request_id,
310 phase => l_phase,
311 status => l_status,
312 dev_phase => l_dev_phase,
313 dev_status => l_dev_status,
314 message => l_message)) THEN
315 fnd_file.put_line(FND_FILE.LOG,'Addrval : l_sub_request_id='||l_sub_request_id||',l_dev_phase='||l_dev_phase||',l_dev_status='||l_dev_status);
316 IF l_dev_phase <> 'COMPLETE'
317 OR l_dev_status <> 'NORMAL' THEN
318 retcode := 2;
319 FND_FILE.PUT_LINE(FND_FILE.LOG,TO_CHAR( l_sub_request_id ) ||
320 ' : ' || l_phase || ':' || l_status ||
321 ' (' || l_message || ').' );
322 update HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'ERROR'
323 where batch_id = l_batch_id;
324 RETURN;
325 END IF;
326 else
327 if l_message is not null then
328 l_success_count := l_success_count+1;
329 end if;
330 retcode :=0;
331 END IF;
332 l_sub_request_ids := SUBSTRB( l_sub_request_ids, l_posi1 + 1 );
333 --Bug No: 3546295
334 /*if l_sub_request_ids is null then
335 return;
336 end if;
337 */
338 --End of 3546295--------
339 END LOOP;
340
341 --bug 3908043: Populate counts in HZ_IMP_ADAPTERS
342 OPEN verify_imp_adapter;
343 FETCH verify_imp_adapter into l_count;
344 CLOSE verify_imp_adapter;
345
346 IF l_count > 0 then
350
347 -- Adapter defined for import for this batch
348 l_import_adapter_def := 'Y';
349 END IF;
351 -- update/insert count to hz_imp_adapters table.
352 if l_import_adapter_def = 'Y' then
353 for rec in validated_address_cur loop
354 open find_imp_country(rec.country);
355 fetch find_imp_country into l_dummy1;
356 close find_imp_country;
357 if l_dummy1 is not null then
358 update hz_imp_adapters
359 set RECORDS_PASSED_VALIDATION = rec.Validated_rec,
360 RECORDS_FAILED_VALIDATION = rec.Failed_rec,
361 TOTAL_RECORDS_VALIDATED = rec.Total_rec
362 where batch_id = p_batch_id
363 and COUNTRY_CODE = rec.country;
364 else
365 insert into hz_imp_adapters
366 (batch_id,country_code,RECORDS_PASSED_VALIDATION,
367 RECORDS_FAILED_VALIDATION,TOTAL_RECORDS_VALIDATED,
368 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
369 LAST_UPDATE_DATE)
370 values(p_batch_id,rec.country,rec.Validated_rec,
371 rec.Failed_rec,rec.Total_rec,
372 -1,sysdate,-1,sysdate);
373 end if;
374 end loop;
375 end if;
376 --Bug No: 3546295
377 update HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'COMPLETED'
378 where batch_id = l_batch_id;
379 --End of 3546295--------
380 -------------------------------------
381 ELSE
382 /*
383 Logic to process the records to address validation is as follows
384 1.If any adapters are defined in hz_imp_adapters then
385 a) Get the adapters from hz_imp_adapters for this batch and process
386 those country records with the adapters defined in the hz_imp_adapters.
387 b) Process the remaining records(countries not in hz_imp_adapters and
388 not in hz_adapter_territories) with the default adapter.
389
390 Note: We should not process the countries in hz_adapter_territories
391 if adapters are defined in hz_imp_adapters for this batch.
392
393 2.If no adapters are defined in hz_imp_adapters then
394 a) Get the country's default adapter from hz_adapter_territories
395 and process those country records with the adapters defined in the hz_adapters.
396 b) Process the remaining records(countries not in hz_adapter_territories)
397 with the default adapter
398 3. If the above cases 1 and 2 fails then
399 a) Process all the records with the system default adapter
400 */
401 OPEN verify_imp_adapter;
402 FETCH verify_imp_adapter into l_count;
403 CLOSE verify_imp_adapter;
404
405 IF l_count > 0 then
406 -- Adapter defined for import for this batch;so get the adapters from hz_imp_adapters
407 l_import_adapter_def := 'Y';
408 OPEN imp_adapter_cur;
409 FETCH imp_adapter_cur BULK COLLECT into
410 l_adapter_id,l_adapter_content_source,l_batch_size,l_default_replace_level;
411 CLOSE imp_adapter_cur;
412 FND_FILE.put_line(fnd_file.log,'adapter count:'|| l_adapter_id.count);
413 ELSE
414
415 -- No Adapter defined for import in hz_imp_adapters for this batch;so get
416 -- the adapters from hz_adapters.
417 OPEN adapter_cur;
418 FETCH adapter_cur BULK COLLECT into
419 l_adapter_id,l_adapter_content_source,l_batch_size,l_default_replace_level;
420 FND_FILE.put_line(fnd_file.log,'l_adapter_id count:'|| l_adapter_id.count);
421 IF adapter_cur%NOTFOUND THEN
422 l_adapter_last_fetch := TRUE;
423 END IF;
424 IF l_adapter_id.count = 0 AND l_adapter_last_fetch THEN
425 FND_FILE.put_line(fnd_file.log,'No valid adapter found in hz_adapters for this batch.');
426 --RETURN; Bug no:3365035.Commented return to continue the execution.
427 END IF;
428 CLOSE adapter_cur;
429 END IF;
430 --Set the address validation status of batch summary table before submit the chaild CP
431 update HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'PROCESSING'
432 where batch_id = l_batch_id returning ORIGINAL_SYSTEM into l_orig_system;
433 IF l_adapter_id.COUNT >0 THEN --Bug no:3365035.To overcome numeric or value error
434 l_adapter_found := TRUE; --Bug No: 3535366
435 FOR i in l_adapter_id.first..l_adapter_id.last LOOP /*start of Adaptor loop */
436
437 IF l_import_adapter_def = 'Y' then
438 OPEN imp_addresss_cur(l_adapter_content_source(i));
439 ELSE
440 OPEN addresss_cur(l_adapter_id(i));
441 END IF;
442
443 LOOP /* start of batch creation loop */
444 IF l_import_adapter_def = 'Y' then
445 FETCH imp_addresss_cur BULK COLLECT into l_row_id limit l_batch_size(i);
446 IF imp_addresss_cur%NOTFOUND THEN
447 l_last_fetch := TRUE;
448 END IF;
449 IF l_row_id.COUNT = 0 AND l_last_fetch THEN
450 --CLOSE imp_addresss_cur; Bug No:3335211
451 EXIT;
452 END IF;
453 ELSE
454 FETCH addresss_cur BULK COLLECT into l_row_id limit l_batch_size(i);
455 IF addresss_cur%NOTFOUND THEN
456 l_last_fetch := TRUE;
457 END IF;
458 IF l_row_id.COUNT = 0 AND l_last_fetch THEN
459 --CLOSE addresss_cur; Bug No:3335211.
460 EXIT;
461 END IF;
462 END IF;
463
464 FORALL j IN l_row_id.FIRST..l_row_id.LAST
465 update hz_imp_addresses_int
466 set validation_subset_id = l_counter,
467 adapter_content_source = l_adapter_content_source(i)
468 where rowid = l_row_id(j);
469 commit;
470 --l_request_id.extend(1);
471 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHADDRC','',
472 to_char(sysdate,'DD-MON-YY HH:MI:SS'), TRUE,l_batch_id,
473 l_counter, null,'HZ_IMPORT',l_batch_id,
474 l_default_replace_level(i),l_orig_system,l_adapter_id(i));
475 IF l_request_id = 0 THEN
476 --Error submitting request
480 fnd_file.put_line(FND_FILE.LOG, 'Error submitting address_validation_child');
477 update HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'ERROR'
478 where batch_id = l_batch_id;
479 retcode :=2;
481 return;
482 -- l_error_count := l_error_count +1;
483 ELSE
484 --Submitted request
485 if l_request_data is null then
486 l_request_data := to_char(l_request_id)||' ' ;
487 else
488 l_request_data := l_request_data ||to_char(l_request_id)||' ' ;
489 end if;
490 fnd_file.put_line(FND_FILE.LOG, 'address_validation_main: child request submitted with request_id:'||l_request_id);
491 END IF;
492
493 IF l_last_fetch = TRUE THEN
494 EXIT;
495 END IF;
496 l_counter := l_counter+1; /* No of current batch request submitted */
497 END LOOP; /* end of batch creation loop */
498 IF l_import_adapter_def = 'Y' then
499 CLOSE imp_addresss_cur;
500 ELSE
501 CLOSE addresss_cur;
502 END IF;
503 END LOOP; /*End of Adaptor loop */
504
505 END IF;
506 --Start for Default Adapter
507 --IF l_import_adapter_def <> 'Y' then --Bug No:3347996. Commented the condition
508
509 -- default adapter
510 OPEN default_adapter;
511 FETCH default_adapter bulk collect into
512 l_adapter_id,l_adapter_content_source,l_batch_size,l_default_replace_level;
513 FND_FILE.put_line(fnd_file.log,'l_adapter_id count:'|| l_adapter_id.count);
514 IF default_adapter%NOTFOUND THEN
515 l_adapter_last_fetch := TRUE;
516 END IF;
517 IF l_adapter_id.count = 0 AND l_adapter_last_fetch THEN
518 FND_FILE.put_line(fnd_file.log,'No Valid Default Adapter found');
519 -----Bug No: 3535366----
520 IF NOT l_adapter_found THEN
521 update HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'ERROR'
522 where batch_id = l_batch_id
523 returning ORIGINAL_SYSTEM into l_orig_system;
524 retcode := 2;
525 FND_FILE.put_line(fnd_file.log,'Error: No adapters found to process the records, Aborting Address Validation Main for this batch');
526 FND_FILE.put_line(fnd_file.log,'Please run the import program by turning Run Address validation to ''NO'' or define at least default adapter');
527 END IF;
528 -----End of Bug No: 3535366----
529 END IF;
530 CLOSE default_adapter;
531
532 IF l_adapter_id.count >0 then
533 l_last_fetch := FALSE;
534 l_counter := l_counter+1;
535 FOR i in l_adapter_id.first..l_adapter_id.last LOOP /*start of Default Adaptor loop */
536 OPEN addresss_cur_default;
537 LOOP /* loop for batch creation */
538 FETCH addresss_cur_default BULK COLLECT into l_row_id limit l_batch_size(i);
539 IF addresss_cur_default%NOTFOUND THEN
540 l_last_fetch := TRUE;
541 END IF;
542 IF l_row_id.COUNT = 0 AND l_last_fetch THEN
543 fnd_file.put_line(FND_FILE.LOG, 'No records are found to process for Default Adapter in address_validation_child ');
544 --close addresss_cur_default;
545 EXIT;
546 END IF;
547
548 FORALL j IN l_row_id.FIRST..l_row_id.LAST
549 UPDATE hz_imp_addresses_int
550 SET validation_subset_id = l_counter,
551 adapter_content_source = l_adapter_content_source(i)
552 WHERE rowid = l_row_id(j);
553 COMMIT;
554 --l_request_id.extend(1);
555 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHADDRC','',
556 to_char(sysdate,'DD-MON-YY HH:MI:SS'), TRUE,l_batch_id,
557 l_counter, null,'HZ_IMPORT',l_batch_id,
558 l_default_replace_level(i),l_orig_system,l_adapter_id(i));
559 IF l_request_id = 0 THEN
560 --Error submitting request
561 UPDATE HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'ERROR'
562 WHERE batch_id = l_batch_id;
563 retcode :=2;
564 fnd_file.put_line(FND_FILE.LOG, 'Error submitting address_validation_child for Default Adapter');
565
566 RETURN;
567 -- l_error_count := l_error_count +1;
568 ELSE
569 --Submitted request
570 IF l_request_data is null then
571 l_request_data := to_char(l_request_id)||' ' ;
572 ELSE
573 l_request_data := l_request_data ||to_char(l_request_id)||' ' ;
574 END IF;
575 fnd_file.put_line(FND_FILE.LOG, 'address_validation_main: child request submitted for Default Adapter with request_id:'||l_request_id);
576 END IF;
577
578 IF l_last_fetch = TRUE THEN
579 EXIT;
580 END IF;
581 l_counter := l_counter+1; /* No of current batch request submitted */
582 END LOOP; /* end of batch creation loop */
583 CLOSE addresss_cur_default; /* close default adpater cur */
584 -------------------------------------------------
585 END LOOP; --end of Default Adapter Loop
586 END IF;
587 --END IF; --Bug No:3347996
588 --End for Default Adapter
589 IF l_request_data IS NOT NULL THEN -- Bug No:3359194
590 FND_CONC_GLOBAL.SET_REQ_GLOBALS(
591 conc_status => 'PAUSED',
592 request_data => l_request_data);
593 END IF;
594
595 END IF;
596 COMMIT;
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 FND_FILE.put_line(fnd_file.log,'Others Error: Aborting Address Validation Main for this batch');
601 FND_FILE.put_line(fnd_file.log,'SQL Error: '||SQLERRM);
602 RAISE ;
603 END address_validation_main;
604
605 -----------------------------------------------------------------------
606 -- This function will be called by update_validated_address procedure,
607 -- to compare the threshold and validated status code.
608 --
609 ------------------------------------------------------------------------
613 if p_value1 is not null and p_value2 is not null then
610 function compare_treshhold(p_value1 varchar2, p_value2 varchar2)
611 return varchar2 is
612 begin
614 if to_number(p_value2) >= to_number(p_value1) then
615 return 'Y';
616 else
617 return 'N';
618 end if;
619 else
620 return 'N';
621 end if;
622 exception
623 when others THEN
624 FND_FILE.put_line(fnd_file.log,'compare_treshhold: Aborting processing inboundxml for this batch');
625 FND_FILE.put_line(fnd_file.log,'compare_treshhold: p_value1,p_value2:-'||p_value1||'-'||p_value2);
626 FND_FILE.put_line(fnd_file.log,'SQL Error: '||SQLERRM);
627 RAISE FND_API.G_EXC_ERROR;
628 end;
629
630 -----------------------------------------------------------------------
631 -- This procedure will be called by xml gateway through mapcode,
632 -- as a procedure call.
633 --
634 ------------------------------------------------------------------------
635 Procedure update_validated_address(
636 p_SITE_ORIG_SYSTEM_REFERENCE in VARCHAR2 ,
637 p_SITE_ORIG_SYSTEM in VARCHAR2 ,
638 p_batch_id in NUMBER,
639 p_Address1 in VARCHAR2 DEFAULT NULL,
640 p_Address2 in VARCHAR2 DEFAULT NULL,
641 p_Address3 in VARCHAR2 DEFAULT NULL,
642 p_Address4 in VARCHAR2 DEFAULT NULL,
643 p_city in VARCHAR2 DEFAULT NULL,
644 p_county in VARCHAR2 DEFAULT NULL,
645 p_CountrySubEntity in VARCHAR2 DEFAULT NULL,
646 p_country in VARCHAR2 DEFAULT NULL,
647 p_postal_code in VARCHAR2 DEFAULT NULL,
648 p_status in VARCHAR2 DEFAULT NULL,
649 P_OVERWRITE_THRESHOLD in VARCHAR2 DEFAULT NULL )is
650
651 begin
652
653 UPDATE HZ_IMP_ADDRESSES_INT SET
654 Address1_std = p_Address1,
655 Address2_std = p_Address2,
656 Address3_std = p_Address3,
657 Address4_std = p_Address4,
658 city_std = p_city,
659 county_std = p_county,
660 PROV_STATE_ADMIN_CODE_STD = p_CountrySubEntity,
661 country_std = nvl(upper(p_country), upper(country)),
662 postal_code_std = p_postal_code,
663 ACCEPT_STANDARDIZED_FLAG = compare_treshhold(p_status,P_OVERWRITE_THRESHOLD),
664 DATE_VALIDATED = trunc(sysdate),
665 ADDR_VALID_STATUS_CODE = p_status
666 WHERE SITE_ORIG_SYSTEM_REFERENCE = P_SITE_ORIG_SYSTEM_REFERENCE
667 AND SITE_ORIG_SYSTEM = P_SITE_ORIG_SYSTEM
668 AND BATCH_ID = p_batch_id;
669
670 exception
671 WHEN NO_DATA_FOUND THEN
672 FND_FILE.put_line(fnd_file.log,'Can not find record for update HZ_IMP_ADDRESSES_INT: ');
673 FND_FILE.put_line(fnd_file.log,'Batch_Id,:'||p_batch_id);
674 FND_FILE.put_line(fnd_file.log,'SITE_ORIG_SYSTEM,:'||P_SITE_ORIG_SYSTEM);
675 FND_FILE.put_line(fnd_file.log,'SITE_ORIG_SYSTEM_REFERENCE:'||P_SITE_ORIG_SYSTEM_REFERENCE);
676 RAISE FND_API.G_EXC_ERROR;
677 WHEN others THEN
678 FND_FILE.put_line(fnd_file.log,'update_validated_address: Aborting processing inboundxml for this batch');
679 FND_FILE.put_line(fnd_file.log,'Batch_Id,:'||p_batch_id);
680 FND_FILE.put_line(fnd_file.log,'SITE_ORIG_SYSTEM,:'||P_SITE_ORIG_SYSTEM);
681 FND_FILE.put_line(fnd_file.log,'SITE_ORIG_SYSTEM_REFERENCE:'||P_SITE_ORIG_SYSTEM_REFERENCE);
682 FND_FILE.put_line(fnd_file.log,'SQL Error: '||SQLERRM);
683 RAISE FND_API.G_EXC_ERROR;
684 end;
685
686 -----------------------------------------------------------------------
687 -- Folowing Rule Function will be called from event subscription,
688 --'oracle.apps.ar.hz.import.inboundxml' which is raised by
689 -- another rule function outboundxml_rule.
690 --
691 -- This function rule will process the inbound xml doc and update
692 -- the hz_imp_addresses_int table with validated address components.
693 ------------------------------------------------------------------------
694 FUNCTION inboundxml_rule (
695 p_subscription_guid IN RAW,
696 p_event IN OUT NOCOPY wf_event_t )
697 RETURN VARCHAR2 IS
698 l_event_data CLOB := NULL;
699 l_ecx_map_code VARCHAR2(30);
700 l_adapter_id NUMBER;
701 l_overwrite_threshold VARCHAR2(30);
702 l_batch_sequence NUMBER;
703 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
704 BEGIN
705 FND_FILE.put_line(fnd_file.log,'inboundxml_rule called');
706 l_event_data := p_event.getEventData;
707 IF(l_event_data IS NOT NULL) THEN
708 l_ecx_map_code := p_event.getValueForParameter('ECX_MAP_CODE');
709 FND_FILE.put_line(fnd_file.log,'ECX Map Code: '||l_ecx_map_code);
710
711 ecx_standard.processXMLCover(
712 i_map_code =>l_ecx_map_code,
713 i_inpayload =>l_event_data,
714 i_debug_level =>3);
715 commit;
716 END IF;
717 RETURN 'SUCCESS';
718 EXCEPTION
719 WHEN FND_API.G_EXC_ERROR THEN
720 FND_FILE.put_line(fnd_file.log,'Expected Error: Aborting processing inboundxml for this batch');
721 Wf_Core.Context('ECX_RULE', 'OUTBOUNDXML', p_event.getEventName(), p_subscription_guid);
722 WF_EVENT.setErrorInfo(p_event, 'ERROR');
723 --return 'ERROR';
724 RAISE FND_API.G_EXC_ERROR; --Bug No: 3778263
725
726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
727 FND_FILE.put_line(fnd_file.log,'Unexpected Error: Aborting processing inboundxml for this batch');
728 Wf_Core.Context('ECX_RULE', 'OUTBOUNDXML', p_event.getEventName(), p_subscription_guid);
729 WF_EVENT.setErrorInfo(p_event, 'ERROR');
730 --return 'ERROR';
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; --Bug No: 3778263
732 WHEN OTHERS THEN
733 FND_FILE.put_line(fnd_file.log,'Others Error: Aborting processing inboundxml for this batch');
734 FND_FILE.put_line(fnd_file.log,'SQL Error: '||SQLERRM);
735 Wf_Core.Context('ECX_RULE', 'OUTBOUNDXML', p_event.getEventName(), p_subscription_guid);
736 wf_event.setErrorInfo(p_event, 'ERROR');
740
737 --return 'ERROR';
738 RAISE; --Bug No: 3778263
739 END inboundxml_rule;
741 -----------------------------------------------------------------------
742 -- Folowing Rule Function will be called from event subscription,
743 --'oracle.apps.ar.hz.import.outboundxml' which is raised by
744 -- address_validation_child Concurrent Program.
745 --
746 -- This function rule will do the following
747 -- 1) Get the generated xml doc by ecx_standard.generate
748 -- 2) Pass the xml doc to HZ_LOCATION_SERVICES_PUB.submit_addrval_doc
749 -- 3) Get returned validated xml doc, raise another wf event to parse
750 -- the validated addresses.
751 ------------------------------------------------------------------------
752 function outboundxml_rule(
753 p_subscription_guid in raw,
754 p_event in out nocopy wf_event_t
755 ) return varchar2
756 is
757 transaction_type varchar2(240);
758 transaction_subtype varchar2(240);
759 party_id varchar2(240);
760 party_site_id varchar2(240);
761 party_type varchar2(200); --Bug #2183619
762 document_number varchar2(240);
763 resultout boolean;
764 retcode pls_integer;
765 errmsg varchar2(2000);
766 debug_level varchar2(2000);
767 i_debug_level pls_integer;
768 parameterList varchar2(200);
769 ecx_exception_type varchar2(200) := null;
770 l_event_data nclob;
771 l_event_data1 clob;
772 l_batch_id NUMBER;
773 l_adapter_log_id NUMBER;
774 l_adapter_id NUMBER;
775 l_subset_id NUMBER;
776 l_return_status VARCHAR2(30);
777 l_msg_count NUMBER;
778 l_msg_data VARCHAR2(2000);
779 l_orig_system varchar2(30);
780 --l_xml1 clob;
781 l_event_key varchar2(30);
782 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
783
784 begin
785
786 l_event_data := to_nclob(p_event.getEventData);
787 --l_event_data1 := p_event.getEventData;
788 l_batch_id := p_event.getValueForParameter('P_BATCH_ID');
789 l_orig_system := p_event.getValueForParameter('P_ORIG_SYSTEM_REFERENCE');
790 l_subset_id := p_event.getValueForParameter('P_VAL_SUBSET_ID');
791 l_adapter_id := p_event.getValueForParameter('P_ADAPTER_ID');
792 --
793 ---Call address validation service API
794 --
795 FND_FILE.put_line(fnd_file.log,'calling HZ_LOCATION_SERVICES_PUB.submit_addrval_doc procedure ');
796 FND_FILE.put_line(fnd_file.log,'BATCH_ID: '||l_batch_id);
797 FND_FILE.put_line(fnd_file.log,'ADAPTER ID: '||l_adapter_id);
798 FND_FILE.put_line(fnd_file.log,'ORIG_SYSTEM: '||l_orig_system);
799 FND_FILE.put_line(fnd_file.log,'SUBSET ID '||l_subset_id);
800 HZ_LOCATION_SERVICES_PUB.submit_addrval_doc(
801 p_addrval_doc => l_event_data,
802 p_adapter_id => l_adapter_id,
803 p_country_code => null,
804 p_module => 'HZ_IMPORT',
805 p_module_id => l_batch_id,
806 x_return_status => l_return_status,
807 x_msg_count => l_msg_count,
808 x_msg_data => l_msg_data );
809
810
811 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
812 RAISE FND_API.G_EXC_ERROR;
813 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
815 END IF;
816
817 wf_event.AddParameterToList(
818 p_name => 'ECX_MAP_CODE',
819 p_value => 'TCA_IMP_OAG_INBOUND',
820 p_parameterlist => l_parameter_list);
821
822 l_event_key := 'HZ_IMP_IN'||l_batch_id||'-'||l_subset_id||'-'||to_char(sysdate,'HH:MI:SS');
823 --
824 --- Raise the event for inboundxml
825 --
826 FND_FILE.put_line(fnd_file.log,'outboundxml_rule: raising inboundxml event');
827 wf_event.raise(
828 p_event_name => 'oracle.apps.ar.hz.import.inboundxml',
829 p_event_key => l_event_key,
830 p_event_data => to_clob(l_event_data),
831 --p_event_data => l_event_data1,
832 p_parameters => l_parameter_list,
833 p_send_date => null);
834
835 l_parameter_list.DELETE;
836
837 RETURN 'SUCCESS';
838 EXCEPTION
839 WHEN FND_API.G_EXC_ERROR THEN
840 FND_FILE.put_line(fnd_file.log,'Expected Error: Aborting outboundxml process for this batch');
841 Wf_Core.Context('ECX_RULE', 'OUTBOUNDXML', p_event.getEventName(), p_subscription_guid);
842 WF_EVENT.setErrorInfo(p_event, 'ERROR');
843 --return 'ERROR';
844 RAISE FND_API.G_EXC_ERROR; --Bug No: 3778263
845
846 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
847 FND_FILE.put_line(fnd_file.log,'Unexpected Error: Aborting outboundxml process for this batch');
848 Wf_Core.Context('ECX_RULE', 'OUTBOUNDXML', p_event.getEventName(), p_subscription_guid);
849 WF_EVENT.setErrorInfo(p_event, 'ERROR');
850 --return 'ERROR';
851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; --Bug No: 3778263
852 WHEN OTHERS THEN
853 FND_FILE.put_line(fnd_file.log,'Others Error: Aborting outboundxml process for this batch');
854 FND_FILE.put_line(fnd_file.log,'SQL Error: '||SQLERRM);
855 Wf_Core.Context('ECX_RULE', 'OUTBOUNDXML', p_event.getEventName(), p_subscription_guid);
856 wf_event.setErrorInfo(p_event, 'ERROR');
857 --return 'ERROR';
858 RAISE; --Bug No: 3778263
859 END outboundxml_rule;
860
861 end HZ_IMP_ADDRESS_VAL_PKG;
[Home] [Help]