[Home] [Help]
PACKAGE BODY: APPS.HZ_LOCATION_SERVICES_PUB
Source
1 PACKAGE BODY HZ_LOCATION_SERVICES_PUB AS
2 /*$Header: ARHLCSVB.pls 120.35 2007/12/06 06:30:43 rarajend ship $*/
3
4 -- fix bug 4271311 - max length of VARCHAR2 is 32767
5 -- for UTF-8 character set, max length should be 32767/3 = 10922
6 MAX_LENGTH CONSTANT NUMBER := 10922;
7
8 TYPE outstreams_type IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
9
10 PROCEDURE save_adapter_log(
11 p_create_or_update IN VARCHAR2,
12 px_rowid IN OUT NOCOPY ROWID,
13 px_adapter_log_id IN OUT NOCOPY NUMBER,
14 p_created_by_module IN VARCHAR2,
15 p_created_by_module_id IN NUMBER,
16 p_http_status_code IN VARCHAR2,
17 p_request_id IN NUMBER,
18 p_object_version_number IN NUMBER,
19 p_inout_doc IN CLOB );
20
21 PROCEDURE validate_mandatory_column(
22 p_location_rec IN HZ_LOCATION_V2PUB.location_rec_type,
23 x_return_status IN OUT NOCOPY VARCHAR2);
24
25 PROCEDURE log(
26 message IN VARCHAR2,
27 newline IN BOOLEAN DEFAULT TRUE);
28
29 PROCEDURE add_wf_parameters(
30 p_adapter_id IN NUMBER,
31 p_overwrite_threshold IN VARCHAR2,
32 p_country IN VARCHAR2,
33 p_nvl_vsc IN VARCHAR2,
34 p_from_vsc IN VARCHAR2,
35 p_to_vsc IN VARCHAR2,
36 p_from_lud IN VARCHAR2,
37 p_to_lud IN VARCHAR2,
38 p_nvl_dv IN VARCHAR2,
39 p_from_dv IN VARCHAR2,
40 p_to_dv IN VARCHAR2,
41 p_num_batch IN NUMBER,
42 p_batch_seq IN NUMBER,
43 p_parameter_list OUT NOCOPY wf_parameter_list_t );
44
45 PROCEDURE get_fromnto_value(
46 p_max IN VARCHAR2,
47 p_min IN VARCHAR2,
48 p_op IN VARCHAR2,
49 p_in IN VARCHAR2,
50 p_nvl_out OUT NOCOPY VARCHAR2,
51 p_from_out OUT NOCOPY VARCHAR2,
52 p_to_out OUT NOCOPY VARCHAR2 );
53
54 PROCEDURE set_loc_assign_id(
55 p_location_rec IN hz_location_v2pub.location_rec_type,
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_msg_count OUT NOCOPY NUMBER,
58 x_msg_data OUT NOCOPY VARCHAR2);
59
60 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
61
62 FUNCTION get_adapter_link(p_adapter_id NUMBER) RETURN VARCHAR2;
63
64 -- This procedure set proxy
65 PROCEDURE set_proxy (
66 p_proxy_host VARCHAR2 DEFAULT NULL,
67 p_proxy_port VARCHAR2 DEFAULT NULL,
68 p_proxy_bypass VARCHAR2 DEFAULT NULL)
69 IS
70 l_proxy_host VARCHAR2(240);
71 l_proxy_port VARCHAR2(240);
72 l_proxy_bypass VARCHAR2(240);
73 BEGIN
74
75 log('Set Proxy Begin');
76 -- can set proxy base on the following profiles if pass in value is null
77 -- all profiles are site level only
78 --
79 -- 1) WEB_PROXY_HOST
80 -- 2) WEB_PROXY_PORT
81 -- 3) WEB_PROXY_BYPASS_DOMAINS
82
83 IF(p_proxy_host IS NULL) THEN
84 l_proxy_host := FND_PROFILE.VALUE('WEB_PROXY_HOST');
85 l_proxy_port := FND_PROFILE.VALUE('WEB_PROXY_PORT');
86 l_proxy_bypass := FND_PROFILE.VALUE('WEB_PROXY_BYPASS_DOMAINS');
87 ELSE
88 l_proxy_host := p_proxy_host;
89 l_proxy_port := p_proxy_port;
90 l_proxy_bypass := p_proxy_bypass;
91 END IF;
92
93 IF(l_proxy_host is not null) THEN
94 UTL_HTTP.SET_PROXY(ltrim(rtrim(l_proxy_host))||':'||ltrim(rtrim(l_proxy_port)),l_proxy_bypass);
95 log('Proxy base on profile setting');
96 log('Proxy Host: '||l_proxy_host);
97 log('Proxy Port: '||l_proxy_port);
98 log('Proxy Bypass Domains: '||l_proxy_bypass);
99 END IF;
100
101 log('Set Proxy End');
102
103 END set_proxy;
104
105 -- This procedure set proxy
106 PROCEDURE set_authentication (
107 p_req IN OUT NOCOPY UTL_HTTP.REQ,
108 p_adapter_id IN NUMBER )
109 IS
110 l_username VARCHAR2(100);
111 l_password VARCHAR2(100);
112
113 CURSOR get_username_password(l_adapter_id NUMBER) IS
114 SELECT username, encrypted_password
115 FROM HZ_ADAPTERS
116 WHERE adapter_id = l_adapter_id;
117 BEGIN
118
119 -- as password is encrypted, we may need to decrypt it
120 log('Set Authentication Begin');
121
122 OPEN get_username_password(p_adapter_id);
123 FETCH get_username_password INTO l_username, l_password;
124 CLOSE get_username_password;
125
126 IF((l_username IS NOT NULL) AND (l_password IS NOT NULL)) THEN
127 UTL_HTTP.SET_AUTHENTICATION(p_req, l_username, l_password, 'Basic', FALSE);
128 END IF;
129
130 log('Set Authentication End');
131
132 END set_authentication;
133
134 -----------------------------------------------------------------------
135 -- Called from address validation conc program (ARHADDRV)
136 -----------------------------------------------------------------------
137 -- It will do the following
138 -- 1) Accept parameters from conc program and retrieve rows from
139 -- HZ_LOCATIONS which meet the parameters passed in
140 -- 2) Raise wf event to enerate xml document base on the rows retrieved.
141 -- It may split up all rows into different batch due to the maximum batch
142 -- size defined for each adapter.
143 ------------------------------------------------------------------------
144 PROCEDURE address_validation (
145 Errbuf OUT NOCOPY VARCHAR2,
146 Retcode OUT NOCOPY VARCHAR2,
147 p_validation_status_op IN VARCHAR2,
148 p_validation_status_code IN VARCHAR2,
149 p_date_validated_op IN VARCHAR2,
150 p_date_validated IN VARCHAR2,
151 p_last_update_date_op IN VARCHAR2,
152 p_last_update_date IN VARCHAR2,
153 p_country IN VARCHAR2,
154 p_adapter_content_source IN VARCHAR2,
155 p_overwrite_threshold IN VARCHAR2 )
156 IS
157 l_return_status VARCHAR2(30);
158 l_msg_count NUMBER;
159 l_msg_data VARCHAR2(2000);
160 l_where_clause VARCHAR2(2000);
161 l_def_batch_size NUMBER;
162 l_total_loc NUMBER;
163 l_adapter_id NUMBER;
164 l_num_batch NUMBER;
165 l_batch_seq NUMBER;
166 l_max_vsc NUMBER;
167 l_min_vsc NUMBER;
168 l_nvl_vsc VARCHAR2(30);
169 l_from_vsc VARCHAR2(30);
170 l_to_vsc VARCHAR2(30);
171 l_nvl_lud VARCHAR2(11);
172 l_from_lud VARCHAR2(11);
173 l_to_lud VARCHAR2(11);
174 l_nvl_dv VARCHAR2(11);
175 l_from_dv VARCHAR2(11);
176 l_to_dv VARCHAR2(11);
177 --l_event_key VARCHAR2(100);
178 l_eot VARCHAR2(11);
179 l_sot VARCHAR2(11);
180
181 l_request_id NUMBER;
182 l_tmp_dv VARCHAR2(11);
183 l_tmp_lud VARCHAR2(11);
184 l_in_dv VARCHAR2(11);
185 l_in_lud VARCHAR2(11);
186 l_vsc VARCHAR2(30);
187
188 i NUMBER;
189 req_data VARCHAR2(10);
190 r NUMBER;
191 l_err_msg FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
192 l_err_count NUMBER;
193 l_req_id NUMBER;
194
195 --l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
196
197 -- get highest validation status code
198 -- the higher the code, the poor the data.
199 -- e.g: 0 means validated successfully
200 CURSOR get_maxmin_scode IS
201 SELECT max(to_number(lookup_code)), min(to_number(lookup_code))
202 FROM ar_lookups
203 WHERE lookup_type = 'HZ_ADDR_VAL_STATUS'
204 AND enabled_flag = 'Y';
205
206 -- get adapter_id base on adapter_content_source
207 CURSOR get_adapter_id_from_cont(l_adapter_content_source VARCHAR2) IS
208 SELECT adapter_id
209 FROM hz_adapters
210 WHERE ltrim(rtrim(adapter_content_source)) = ltrim(rtrim(l_adapter_content_source));
211
212 -- get default batch size base on adapter_id
213 CURSOR get_def_batch(l_adapter_id NUMBER) IS
214 SELECT maximum_batch_size
215 FROM hz_adapters
216 WHERE adapter_id = l_adapter_id;
217
218 -- get the min creation_date from HZ_LOCATIONS
219 CURSOR get_min_cr_date IS
220 SELECT to_char(min(creation_date), 'DD-MON-YYYY')
221 FROM hz_locations;
222
223 -- get worker status
224 CURSOR get_worker_status(l_request_id NUMBER) IS
225 SELECT COUNT(1)
226 FROM FND_CONCURRENT_REQUESTS
227 WHERE priority_request_id = l_request_id
228 AND request_id <> l_request_id
229 AND phase_code = 'C'
230 AND status_code = 'E';
231
232 BEGIN
233
234 savepoint address_validation_pub;
235
236 FND_MSG_PUB.initialize;
237
238 req_data := fnd_conc_global.request_data;
239
240 --
241 -- If this is the first run, we well set i = 1.
242 -- Otherwise, we will set i = request_data + 1, and we will
243 -- exit if we are done.
244 --
245
246 IF(req_data is not null) THEN
247 l_req_id := FND_GLOBAL.CONC_REQUEST_ID;
248 OPEN get_worker_status(l_req_id);
249 FETCH get_worker_status INTO l_err_count;
250 CLOSE get_worker_status;
251 IF(l_err_count > 0) THEN
252 errbuf := 'Error on worker';
253 retcode := 2;
254 ELSE
255 errbuf := 'All worker done';
256 retcode := 0;
257 END IF;
258 RETURN;
259 ELSE
260 i := 1;
261 END IF;
262
263 log('Starting Location Service');
264 log('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
265 log('NEWLINE');
266
267 log('p_validation_status_op :'||p_validation_status_op);
268 log('p_validation_status_code :'||p_validation_status_code);
269 log('p_date_validated_op :'||p_date_validated_op);
270 log('p_date_validated :'||p_date_validated);
271 log('p_last_update_date_op :'||p_last_update_date_op);
272 log('p_last_update_date :'||p_last_update_date);
273 log('p_country :'||p_country);
274 log('p_adapter_content_source :'||p_adapter_content_source);
275 log('p_overwrite_threshold :'||p_overwrite_threshold);
276
277 IF(p_date_validated IS NOT NULL) THEN
278 l_in_dv := to_char(fnd_date.canonical_to_date(p_date_validated),'DD-MON-YYYY');
279 IF(p_date_validated_op = '>') THEN
280 l_tmp_dv := to_char(fnd_date.canonical_to_date(p_date_validated)+1,'DD-MON-YYYY');
281 ELSIF(p_date_validated_op = '<') THEN
282 l_tmp_dv := to_char(fnd_date.canonical_to_date(p_date_validated)-1,'DD-MON-YYYY');
283 ELSE
284 l_tmp_dv := to_char(fnd_date.canonical_to_date(p_date_validated),'DD-MON-YYYY');
285 END IF;
286 log('temp date_validated: '||l_tmp_dv);
287 END IF;
288
289 IF(p_last_update_date IS NOT NULL) THEN
290 l_in_lud := to_char(fnd_date.canonical_to_date(p_last_update_date),'DD-MON-YYYY');
291 IF(p_last_update_date_op = '>') THEN
292 l_tmp_lud := to_char(fnd_date.canonical_to_date(p_last_update_date)+1,'DD-MON-YYYY');
293 ELSIF(p_last_update_date_op = '<') THEN
294 l_tmp_lud := to_char(fnd_date.canonical_to_date(p_last_update_date)-1,'DD-MON-YYYY');
295 ELSE
296 l_tmp_lud := to_char(fnd_date.canonical_to_date(p_last_update_date),'DD-MON-YYYY');
297 END IF;
298 log('temp last_update_date: '||l_tmp_lud);
299 END IF;
300
301 -- get adapter id
302 IF(p_adapter_content_source IS NOT NULL) THEN
303 OPEN get_adapter_id_from_cont(p_adapter_content_source);
304 FETCH get_adapter_id_from_cont INTO l_adapter_id;
305 CLOSE get_adapter_id_from_cont;
306 ELSE
307 l_adapter_id := get_adapter_id(null, p_country);
308 IF(l_adapter_id IS NULL) THEN
309 log('Invalid adapter id: '||l_adapter_id);
310 log('p_adapter_content_source: '||p_adapter_content_source);
311 log('p_country_code: '||p_country);
312 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_INVALID_ADAPTER');
313 FND_MSG_PUB.ADD;
314 RAISE FND_API.G_EXC_ERROR;
315 END IF;
316 END IF;
317
318 -- get default batch size
319 OPEN get_def_batch(l_adapter_id);
320 FETCH get_def_batch INTO l_def_batch_size;
321 CLOSE get_def_batch;
322
323 -- get the max and min validation_status_code
324 OPEN get_maxmin_scode;
325 FETCH get_maxmin_scode INTO l_max_vsc, l_min_vsc;
326 CLOSE get_maxmin_scode;
327
328 -- construct where clause to find out the total number of locations
329 l_where_clause := 'select count(1) from HZ_LOCATIONS '||
330 'where actual_content_source = ''USER_ENTERED'' and nvl(do_not_validate_flag,''N'') = ''N''';
331 IF((p_validation_status_op IS NOT NULL) AND (p_validation_status_code IS NOT NULL)) THEN
332 l_where_clause := l_where_clause||' and validation_status_code '
333 ||p_validation_status_op||' :p_val_status_code';
334 ELSE
335 l_where_clause := l_where_clause||' and :p_val_status_code is null';
336 END IF;
337
338 -- base on pass in op and value to find out max and min of the parameter
339 -- e.g.: if op is '>' and status code is '0'. Then the return l_from_vsc
340 -- will be '1' and l_to_vsc will be '6' (which is max vsc in seed data)
341 -- l_nvl_vsc will be set to min, i.e. '1'
342 l_vsc := p_validation_status_code;
343 IF((p_validation_status_op IS NOT NULL) AND (p_validation_status_code IS NOT NULL)) THEN
344 IF(p_validation_status_op = '>') THEN
345 l_max_vsc := l_max_vsc + 1;
346 l_min_vsc := l_min_vsc + 1;
347 l_vsc := to_number(p_validation_status_code)+1;
348 ELSIF(p_validation_status_op = '<') THEN
349 l_max_vsc := l_max_vsc - 1;
350 l_min_vsc := l_min_vsc - 1;
351 l_vsc := to_number(p_validation_status_code)-1;
352 END IF;
353 END IF;
354
355 log('Max VSC: '||l_max_vsc);
356 log('Min VSC: '||l_min_vsc);
357 log('VSC: '||l_vsc);
358
359 get_fromnto_value(
360 p_max => to_char(l_max_vsc),
361 p_min => to_char(l_min_vsc),
362 p_op => p_validation_status_op,
363 p_in => l_vsc,
364 p_nvl_out => l_nvl_vsc,
365 p_from_out => l_from_vsc,
366 p_to_out => l_to_vsc );
367
368 -- unset the nvl parameter if the input for validation_status_code and operator
369 -- are not null
370 IF((p_validation_status_op IS NOT NULL) AND (p_validation_status_code IS NOT NULL)) THEN
371 l_nvl_vsc := '-99';
372 END IF;
373
374 IF((p_date_validated_op IS NOT NULL) AND (p_date_validated IS NOT NULL)) THEN
375 l_where_clause := l_where_clause||' and trunc(date_validated) '
376 ||p_date_validated_op
377 ||' to_date(:p_date_val,''DD-MON-YYYY'')';
378 ELSE
379 l_where_clause := l_where_clause||' and :p_date_val is null';
380 END IF;
381
382 l_eot := to_char(add_months(sysdate,12),'DD-MON-YYYY');
383 OPEN get_min_cr_date;
384 FETCH get_min_cr_date INTO l_sot;
385 CLOSE get_min_cr_date;
386
387 get_fromnto_value(
388 p_max => l_eot,
389 p_min => l_sot,
390 p_op => p_date_validated_op,
391 p_in => l_tmp_dv,
392 p_nvl_out => l_nvl_dv,
393 p_from_out => l_from_dv,
394 p_to_out => l_to_dv );
395
396 -- unset the nvl parameter if the input for date_validated and operator are not null
397 IF((p_date_validated_op IS NOT NULL) AND (p_date_validated IS NOT NULL)) THEN
398 l_nvl_dv := to_char(add_months(sysdate,24),'DD-MON-YYYY');
399 END IF;
400
401 IF((p_last_update_date_op IS NOT NULL) AND (p_last_update_date IS NOT NULL)) THEN
402 l_where_clause := l_where_clause||' and trunc(last_update_date) '
403 ||p_last_update_date_op
404 ||' to_date(:p_last_upd_date,''DD-MON-YYYY'')';
405 ELSE
406 l_where_clause := l_where_clause||' and :p_last_upd_date is null';
407 END IF;
408
409 get_fromnto_value(
410 p_max => l_eot,
411 p_min => l_sot,
412 p_op => p_last_update_date_op,
413 --p_in => p_last_update_date,
414 p_in => l_tmp_lud,
415 p_nvl_out => l_nvl_lud,
416 p_from_out => l_from_lud,
417 p_to_out => l_to_lud );
418
419 IF(p_country IS NOT NULL) THEN
420 l_where_clause := l_where_clause||' and country = :p_cntry';
421 ELSE
422 l_where_clause := l_where_clause||' and :p_cntry is null';
423 END IF;
424 log('where clause :'||l_where_clause);
425
426 execute immediate l_where_clause into l_total_loc
427 using p_validation_status_code, l_in_dv, l_in_lud, p_country;
428
429 log('Total number of locations: '||l_total_loc);
430 log('Maximum batch size: '||l_def_batch_size);
431
432 -- find out how many batches required
433 l_num_batch := ceil(l_total_loc/l_def_batch_size);
434 log('The number of batch required: '||l_num_batch);
435
436 log('Pass these parameters to worker');
437 log('Adapter ID: '||l_adapter_id);
438 log('Overwrite Threshold: '||p_overwrite_threshold);
439 log('Country: '||p_country);
440 log('NVL VSC: '||l_nvl_vsc);
441 log('FROM VSC: '||l_from_vsc);
442 log('TO VSC: '||l_to_vsc);
443 log('FROM LUD: '||l_from_lud);
444 log('TO LUD: '||l_to_lud);
445 log('NVL DV: '||l_nvl_dv);
446 log('FROM DV: '||l_from_dv);
447 log('TO DV: '||l_to_dv);
448 log('NUM BATCH: '||l_num_batch);
449
450 -- submit each batch will one address validation request
451 -- pass number of batch to ecx call
452 -- use loop to make ecx call and add parameters for each ecx call
453 FOR j in 0..l_num_batch-1 LOOP
454
455 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
456 'AR', 'ARHADDRW', 'Address Validation Worker '||j,
457 SYSDATE, TRUE, l_adapter_id, p_overwrite_threshold,
458 p_country, l_nvl_vsc, l_from_vsc, l_to_vsc,
459 l_from_lud, l_to_lud, l_nvl_dv, l_from_dv, l_to_dv,
460 l_num_batch, j);
461
462 log('NEWLINE');
463 log('Submit Address Validation Worker '||j);
464
465 IF(l_request_id IS NULL or l_request_id = 0) THEN
466 l_err_msg := FND_MESSAGE.get;
467 errbuf := l_err_msg;
468 retcode := 2;
469 RETURN;
470 END IF;
471 END LOOP;
472
473 log('NEWLINE');
474 log('Concurrent Program Execution completed ');
475 log('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
476
477 IF(l_total_loc > 0) THEN
478 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
479 request_data => TO_CHAR(i));
480 errbuf := 'Concurrent Program Execution completed ';
481 retcode := 0;
482 RETURN;
483 END IF;
484
485 EXCEPTION
486 WHEN FND_API.G_EXC_ERROR THEN
487 log('Error: Aborting Location Service');
488 ROLLBACK TO address_validation_pub;
489 Retcode := 2;
490 Errbuf := logerror(SQLERRM);
491 FND_FILE.close;
492 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
493 log('Error: Aborting Location Service');
494 ROLLBACK TO address_validation_pub;
495 Retcode := 2;
496 Errbuf := logerror(SQLERRM);
497 FND_FILE.close;
498 WHEN OTHERS THEN
499 log('Error: Aborting Location Service');
500 ROLLBACK TO address_validation_pub;
501 Retcode := 2;
502 Errbuf := logerror(SQLERRM);
503 FND_FILE.close;
504 END address_validation;
505
506 -----------------------------------------------------------------------
507 -- Called from address validation conc program (ARHADDRV)
508 -----------------------------------------------------------------------
509 -- It will do the following
510 -- 1) Accept parameters from conc program and retrieve rows from
511 -- HZ_LOCATIONS which meet the parameters passed in
512 -- 2) Raise wf event to enerate xml document base on the rows retrieved.
513 -- It may split up all rows into different batch due to the maximum batch
514 -- size defined for each adapter.
515 ------------------------------------------------------------------------
516 PROCEDURE address_validation_worker (
517 Errbuf OUT NOCOPY VARCHAR2,
518 Retcode OUT NOCOPY VARCHAR2,
519 p_adapter_id IN NUMBER,
520 p_overwrite_threshold IN VARCHAR2,
521 p_country IN VARCHAR2,
522 p_nvl_vsc IN VARCHAR2,
523 p_from_vsc IN VARCHAR2,
524 p_to_vsc IN VARCHAR2,
525 p_from_lud IN VARCHAR2,
526 p_to_lud IN VARCHAR2,
527 p_nvl_dv IN VARCHAR2,
528 p_from_dv IN VARCHAR2,
529 p_to_dv IN VARCHAR2,
530 p_num_batch IN NUMBER,
531 p_batch_sequence IN NUMBER )
532 IS
533
534 l_event_key VARCHAR2(100);
535 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
536
537 BEGIN
538
539 savepoint address_validation_w_pub;
540
541 FND_MSG_PUB.initialize;
542
543 log('Starting Location Service Worker');
544 log('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
545 log('NEWLINE');
546
547 log('p_adapter_id :'||p_adapter_id);
548 log('p_overwrite_threshold :'||p_overwrite_threshold);
549 log('p_country :'||p_country);
550 log('p_nvl_vsc :'||p_nvl_vsc);
551 log('p_from_vsc :'||p_from_vsc);
552 log('p_to_vsc :'||p_to_vsc);
553 log('p_from_lud :'||p_from_lud);
554 log('p_to_lud :'||p_to_lud);
555 log('p_nvl_dv :'||p_nvl_dv);
556 log('p_from_dv :'||p_from_dv);
557 log('p_to_dv :'||p_to_dv);
558 log('p_num_batch :'||p_num_batch);
559 log('p_batch_seq :'||p_batch_sequence);
560 log('Batch #: '||p_batch_sequence);
561
562 l_parameter_list := wf_parameter_list_t();
563
564 log('Adding workflow parameters');
565
566 add_wf_parameters(
567 p_adapter_id => p_adapter_id,
568 p_overwrite_threshold => p_overwrite_threshold,
569 p_country => p_country,
570 p_nvl_vsc => p_nvl_vsc,
571 p_from_vsc => p_from_vsc,
572 p_to_vsc => p_to_vsc,
573 p_from_lud => p_from_lud,
574 p_to_lud => p_to_lud,
575 p_nvl_dv => p_nvl_dv,
576 p_from_dv => p_from_dv,
577 p_to_dv => p_to_dv,
578 p_num_batch => p_num_batch,
579 p_batch_seq => p_batch_sequence,
580 p_parameter_list => l_parameter_list );
581
582 l_event_key := 'HZ_LOCSERVICE_OUTBOUND-'||hz_utility_v2pub.request_id||'-'||p_adapter_id||'-'||p_batch_sequence;
583
584 -- raise wf event, this will call procedure outdoc_rule
585 log('Raise Workflow Event, event key is: '||l_event_key);
586
587 wf_event.raise(
588 p_event_name => 'oracle.apps.ar.hz.locservice.generatexml',
589 p_event_key => l_event_key,
590 p_event_data => NULL,
591 p_parameters => l_parameter_list,
592 p_send_date => NULL);
593
594 l_parameter_list.DELETE;
595
596 EXCEPTION
597 WHEN FND_API.G_EXC_ERROR THEN
598 log('Error: Aborting Location Service at worker level');
599 ROLLBACK TO address_validation_w_pub;
600 Retcode := 2;
601 Errbuf := logerror(SQLERRM);
602 FND_FILE.close;
603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604 log('Error: Aborting Location Service at worker level');
605 ROLLBACK TO address_validation_w_pub;
606 Retcode := 2;
607 Errbuf := logerror(SQLERRM);
608 FND_FILE.close;
609 WHEN OTHERS THEN
610 log('Error: Aborting Location Service at worker level');
611 ROLLBACK TO address_validation_w_pub;
612 Retcode := 2;
613 Errbuf := logerror(SQLERRM);
614 FND_FILE.close;
615 END address_validation_worker;
616
617 -----------------------------------------------------------------------
618 -- Called from function rule outdoc_rule
619 -----------------------------------------------------------------------
620 -- As outdoc_rule raise wf event to parse xml, this function
621 -- rule which is defined for a wf event will be called. The wf event
622 -- is called oracle.apps.ar.hz.locservice.parsexml
623 -- This function rule will do the following
624 -- 1) Get the parsed xml doc
625 -- 2) Check for tax validation
626 -- 3) Base on tax validation result, to determine whether to create/update
627 -- the validated addresses
628 ------------------------------------------------------------------------
629 PROCEDURE get_validated_xml (
630 p_adapter_id IN NUMBER,
631 p_overwrite_threshold IN VARCHAR2,
632 p_location_id IN NUMBER,
633 p_country IN VARCHAR2,
634 p_address1 IN VARCHAR2,
635 p_address2 IN VARCHAR2,
636 p_address3 IN VARCHAR2,
637 p_address4 IN VARCHAR2,
638 p_county IN VARCHAR2,
639 p_city IN VARCHAR2,
640 p_prov_state_admin_code IN VARCHAR2,
641 p_postal_code IN VARCHAR2,
642 p_validation_status_code IN VARCHAR2 )
643 IS
644 l_location_rec hz_location_v2pub.location_rec_type;
645 l_location_profile_rec hz_location_profile_pvt.location_profile_rec_type;
646 l_location_profile_id NUMBER;
647 l_state VARCHAR2(60);
648 l_province VARCHAR2(60);
649 l_country VARCHAR2(60);
650 l_county VARCHAR2(60);
651 l_postal_code VARCHAR2(60);
652 l_city VARCHAR2(60);
653 l_obj_version_number NUMBER;
654 l_adapter_content_source VARCHAR2(30);
655 l_rowid ROWID := NULL;
656 l_allow_update_std VARCHAR2(1);
657 l_maintain_history VARCHAR2(1);
658 l_dummy VARCHAR2(1);
659 l_return_status VARCHAR2(30);
660 l_msg_count NUMBER;
661 l_msg_data VARCHAR2(2000);
662 l_loc_id NUMBER;
663 l_validation_status_code NUMBER;
664 l_overwrite_threshold NUMBER;
665 l_highest_score NUMBER;
666 l_validation_sst_flag VARCHAR2(1);
667 l_key VARCHAR2(500);
668 l_party_id NUMBER;
669
670 CURSOR get_highest_score IS
671 select max(to_number(lookup_code))
672 from AR_LOOKUPS
673 where lookup_type = 'HZ_ADDR_VAL_STATUS';
674
675 CURSOR get_loc_obj_version_number(l_location_id NUMBER) IS
676 select object_version_number, state, province, country,
677 county, postal_code, city, rowid
678 from HZ_LOCATIONS
679 where location_id = l_location_id;
680
681 CURSOR get_content_source(l_adapter_id NUMBER) IS
682 select adapter_content_source
683 from HZ_ADAPTERS
684 where adapter_id = l_adapter_id;
685
686 CURSOR is_active_profile(l_location_id NUMBER, l_adapter_content_source VARCHAR2) IS
687 select 'X'
688 from HZ_LOCATION_PROFILES
689 where sysdate between effective_start_date and nvl(effective_end_date, sysdate)
690 and actual_content_source = l_adapter_content_source
691 and location_id = l_location_id;
692
693 -- check if the current location record has been validated
694 CURSOR is_standardized(l_location_id NUMBER) IS
695 SELECT 'X'
696 FROM hz_locations
697 WHERE location_id = l_location_id
698 AND date_validated IS NOT NULL
699 AND validation_status_code IS NOT NULL;
700
701 -- check if location has been used as identifying address
702 CURSOR ident_address(l_location_id NUMBER) IS
703 SELECT hps.party_id
704 FROM hz_party_sites hps
705 WHERE hps.location_id = l_location_id
706 AND hps.identifying_address_flag = 'Y';
707
708 BEGIN
709
710 l_return_status := fnd_api.g_ret_sts_success;
711
712 log('p_adapter_id: '||p_adapter_id);
713 log('p_overwrite_threshold: '||p_overwrite_threshold);
714 log('p_location_id: '||p_location_id);
715 log('p_address1: '||p_address1);
716 log('p_address2: '||p_address2);
717 log('p_address3: '||p_address3);
718 log('p_address4: '||p_address4);
719 log('p_country: '||upper(p_country));
720 log('p_county: '||p_county);
721 log('p_city: '||p_city);
722 log('p_prov_state_admin_code: '||p_prov_state_admin_code);
723 log('p_postal_code: '||p_postal_code);
724 log('p_validation_status_code: '||p_validation_status_code);
725
726 OPEN get_highest_score;
727 FETCH get_highest_score INTO l_highest_score;
728 CLOSE get_highest_score;
729
730 IF(p_validation_status_code IS NOT NULL) THEN
731 l_validation_status_code := to_number(p_validation_status_code);
732 ELSE
733 log('validation_status_code is null');
734 --l_validation_status_code := l_highest_score + 1;
735 END IF;
736
737 l_overwrite_threshold := to_number(p_overwrite_threshold);
738 l_maintain_history := nvl(fnd_profile.value('HZ_MAINTAIN_LOC_HISTORY'), 'Y');
739
740 -- if validation_status_code is less than or equal to overwrite threshold
741 -- continue, otherwise do nothing.
742
743 OPEN get_loc_obj_version_number(p_location_id);
744 FETCH get_loc_obj_version_number INTO l_obj_version_number, l_state, l_province,
745 l_country, l_county, l_postal_code, l_city, l_rowid;
746 CLOSE get_loc_obj_version_number;
747
748 log('country code in TCA: '||l_country);
749
750 IF (NOT((upper(ltrim(rtrim(l_country)))) = (upper(ltrim(rtrim(p_country)))))) THEN
751 log('Returning country does not match. Ignore this address.');
752 RETURN;
753 END IF;
754
755 OPEN get_content_source(p_adapter_id);
756 FETCH get_content_source INTO l_adapter_content_source;
757 CLOSE get_content_source;
758
759 l_location_rec.location_id := p_location_id;
760 l_location_rec.address1 := p_address1;
761 l_location_rec.address2 := p_address2;
762 l_location_rec.address3 := p_address3;
763 l_location_rec.address4 := p_address4;
764 /* Bug 3527919: get country code from database, don't get it from Trillium */
765 --l_location_rec.country := upper(p_country);
766 l_location_rec.country := l_country;
767 l_location_rec.county := p_county;
768 l_location_rec.city := p_city;
769 l_location_rec.postal_code := p_postal_code;
770
771 -- base on the existing location to find out
772 -- whether prov_state_admin_code is state or province
773 IF(l_state IS NOT NULL) THEN
774 l_location_rec.state := p_prov_state_admin_code;
775 l_location_rec.province := NULL;
776 ELSIF(l_province IS NOT NULL) THEN
777 l_location_rec.state := NULL;
778 l_location_rec.province := p_prov_state_admin_code;
779 ELSE
780 l_location_rec.state := p_prov_state_admin_code;
781 l_location_rec.province := NULL;
782 END IF;
783
784 validate_mandatory_column(l_location_rec, l_return_status);
785
786 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
787 log('Expected error at update_location_profile. Mandatory column checking failed.');
788 RETURN;
789 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
790 log('Unexpected error at update_location_profile. Mandatory column checking failed.');
791 RETURN;
792 END IF;
793
794 -- Fix bug 3395521, if returned value is NULL, then pass it as g_miss to
795 -- hz_location_profile_pvt
796 l_location_profile_rec.location_id := p_location_id;
797 l_location_profile_rec.address1 := nvl(p_address1,fnd_api.g_miss_char);
798 l_location_profile_rec.address2 := nvl(p_address2,fnd_api.g_miss_char);
799 l_location_profile_rec.address3 := nvl(p_address3,fnd_api.g_miss_char);
800 l_location_profile_rec.address4 := nvl(p_address4,fnd_api.g_miss_char);
801 /* Bug 3527919: get country code from database, don't get it from Trillium */
802 --l_location_profile_rec.country := nvl(upper(p_country),fnd_api.g_miss_char);
803 l_location_profile_rec.country := nvl(upper(l_country),fnd_api.g_miss_char);
804 l_location_profile_rec.county := nvl(p_county,fnd_api.g_miss_char);
805 l_location_profile_rec.city := nvl(p_city,fnd_api.g_miss_char);
806 l_location_profile_rec.postal_code := nvl(p_postal_code,fnd_api.g_miss_char);
807 l_location_profile_rec.prov_state_admin_code := nvl(p_prov_state_admin_code,fnd_api.g_miss_char);
808 l_location_profile_rec.actual_content_source := nvl(l_adapter_content_source,fnd_api.g_miss_char);
809 l_location_profile_rec.validation_status_code := nvl(p_validation_status_code,fnd_api.g_miss_char);
810 l_location_profile_rec.date_validated := nvl(sysdate,fnd_api.g_miss_date);
811
812 IF(l_validation_status_code <= l_overwrite_threshold) THEN
813
814 log('Record has status code less than or equal to overwrite threshold. Accept returned location.');
815 hz_registry_validate_v2pub.tax_location_validation (
816 p_location_rec => l_location_rec,
817 p_create_update_flag => 'U',
818 x_return_status => l_return_status );
819
820 -- not doing any update on HZ_LOCATIONS, but only
821 -- put location profile to keep history if needed
822 IF l_return_status = fnd_api.g_ret_sts_error THEN
823
824 log('Tax validation not passed.');
825 -- check if the adapter content source has active record
826 -- with SST flag='Y' in location profile already
827 -- 1) if not maintain history, don't do anything
828 -- 2) if maintain history, create a new profile with end date = sysdate
829 -- and sst flag = 'N'
830
831 OPEN is_active_profile(p_location_id, l_adapter_content_source);
832 FETCH is_active_profile INTO l_dummy;
833 CLOSE is_active_profile;
834
835 -- if maintain history is "No" and find existing actual_content_source
836 -- then don't do anything, otherwise insert a new location profile
837 -- but the location profile will have sst flag as "No" since it
838 -- does not pass tax validation
839 IF(NOT((l_maintain_history = 'N') AND (l_dummy IS NOT NULL))) THEN
840 /*
841 l_location_profile_rec.validation_sst_flag := 'N';
842 l_location_profile_rec.effective_start_date := sysdate;
843 l_location_profile_rec.effective_end_date := sysdate;
844
845 -- as this location does not pass tax validation, we only need to create
846 -- an entry in location profiles. Therefore, call create_location_profile
847 hz_location_profile_pvt.create_location_profile (
848 p_location_profile_rec => l_location_profile_rec,
849 x_location_profile_id => l_location_profile_id,
850 x_return_status => l_return_status,
851 x_msg_count => l_msg_count,
852 x_msg_data => l_msg_data
853 );
854 */
855 -- use update_location_profile instead. location profile pvt can find out
856 -- whether to do create or update on location profile table base on
857 -- maintain history profile option and if adapter content source already exist
858
859 l_location_profile_rec.validation_sst_flag := 'N';
860
861 hz_location_profile_pvt.update_location_profile (
862 p_location_profile_rec => l_location_profile_rec,
863 x_return_status => l_return_status,
864 x_msg_count => l_msg_count,
865 x_msg_data => l_msg_data
866 );
867
868 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
869 -- write to log file about the error
870 -- not raising error, continue the next record
871 log('Expected error at update_location_profile');
872 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
873 -- write to log file about the error
874 -- not raising error, continue the next record
875 log('Unexpected error at update_location_profile');
876 END IF;
877 log('Location profile updated.');
878 END IF; -- maintain history
879 ELSIF l_return_status = fnd_api.g_ret_sts_success THEN
880
881 log('Tax validation passed.');
882 BEGIN
883
884 savepoint get_validated_xml_pub;
885
886 l_allow_update_std := nvl(fnd_profile.value('HZ_UPDATE_STD_ADDRESS'), 'Y');
887 l_dummy := NULL;
888
889 OPEN is_standardized(l_location_rec.location_id);
890 FETCH is_standardized INTO l_dummy;
891 CLOSE is_standardized;
892
893 -- location has been validated before and profile is set to 'N'
894 -- only if validation_sst_flag is not passed
895 IF((l_allow_update_std = 'N') AND (l_dummy IS NOT NULL)) THEN
896 l_validation_sst_flag := 'N';
897 ELSE
898 l_validation_sst_flag := 'Y';
899 END IF;
900
901 -- set validation_sst_flag to null, let update_location_profile
902 -- to determine the sst flag by checking profile option
903 -- HZ_UPDATE_STD_ADDRESS and existing actual_content_source
904 l_location_profile_rec.validation_sst_flag := l_validation_sst_flag;
905
906 hz_location_profile_pvt.update_location_profile (
907 p_location_profile_rec => l_location_profile_rec,
908 x_return_status => l_return_status,
909 x_msg_count => l_msg_count,
910 x_msg_data => l_msg_data
911 );
912
913 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
914 -- write to log file about the error
915 -- not raising error, continue the next record
916 log('Expected error at update_location_profile. Rollback changes.');
917 rollback to get_validated_xml_pub;
918 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
919 -- write to log file about the error
920 -- not raising error, continue the next record
921 log('Unexpected error at update_location_profile. Rollback changes.');
922 rollback to get_validated_xml_pub;
923 ELSIF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
924 -- update HZ_LOCATIONS directly only if the allow update profile is yes
925 -- AND validation_status_code is below threshold
926 IF(l_validation_sst_flag = 'Y') THEN
927 BEGIN
928
929 log('Generate new address key.');
930
931 -- call address key generation program
932 l_key := hz_fuzzy_pub.generate_key (
933 'ADDRESS', NULL,
934 l_location_rec.address1,
935 l_location_rec.address2,
936 l_location_rec.address3,
937 l_location_rec.address4,
938 l_location_rec.postal_code,
939 NULL, NULL);
940 log('Update location.');
941
942 UPDATE hz_locations
943 SET address1 = l_location_rec.address1,
944 address2 = l_location_rec.address2,
945 address3 = l_location_rec.address3,
946 address4 = l_location_rec.address4,
947 city = l_location_rec.city,
948 country = l_location_rec.country,
949 county = l_location_rec.county,
950 state = l_location_rec.state,
951 province = l_location_rec.province,
952 postal_code = l_location_rec.postal_code,
953 address_key = l_key,
954 last_update_date = hz_utility_v2pub.last_update_date,
955 last_updated_by = hz_utility_v2pub.last_updated_by,
956 last_update_login = hz_utility_v2pub.last_update_login,
957 validation_status_code = l_location_profile_rec.validation_status_code,
958 date_validated = l_location_profile_rec.date_validated,
959 object_version_number = nvl(object_version_number,1)+1
960 WHERE location_id = l_location_rec.location_id;
961
962 -- Fix bug 4169728. Set address_text to null.
963 UPDATE hz_cust_acct_sites_all cas
964 SET cas.address_text = null
965 WHERE cas.address_text IS NOT NULL
966 AND EXISTS
967 ( SELECT 1
968 FROM HZ_PARTY_SITES ps
969 WHERE ps.location_id = l_location_rec.location_id
970 AND cas.party_site_id = ps.party_site_id );
971
972 -- denormalize location if it has been used as identifying address
973 BEGIN
974 OPEN ident_address(l_location_rec.location_id);
975 LOOP
976 FETCH ident_address INTO l_party_id;
977 EXIT WHEN ident_address%NOTFOUND;
978
979 IF(l_party_id <> -1) THEN
980
981 SELECT party_id
982 INTO l_party_id
983 FROM hz_parties
984 WHERE party_id = l_party_id
985 FOR UPDATE NOWAIT;
986
987 log('Denormalize location record to party: '||l_party_id);
988
989 UPDATE HZ_PARTIES
990 SET country = l_location_rec.country,
991 address1 = l_location_rec.address1,
992 address2 = l_location_rec.address2,
993 address3 = l_location_rec.address3,
994 address4 = l_location_rec.address4,
995 city = l_location_rec.city,
996 county = l_location_rec.county,
997 postal_code = l_location_rec.postal_code,
998 state = l_location_rec.state,
999 province = l_location_rec.province,
1000 last_update_date = hz_utility_v2pub.last_update_date,
1001 last_updated_by = hz_utility_v2pub.last_updated_by,
1002 last_update_login = hz_utility_v2pub.last_update_login,
1003 request_id = hz_utility_v2pub.request_id,
1004 program_id = hz_utility_v2pub.program_id,
1005 program_application_id = hz_utility_v2pub.program_application_id,
1006 program_update_date = hz_utility_v2pub.program_update_date
1007 WHERE party_id = l_party_id;
1008 END IF;
1009 END LOOP;
1010 CLOSE ident_address;
1011
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 log('Cannot update party due to record change.');
1015 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
1016 fnd_message.set_token('TABLE', 'HZ_PARTIES');
1017 fnd_msg_pub.add;
1018 CLOSE ident_address;
1019 RAISE fnd_api.g_exc_error;
1020 END;
1021
1022 IF((l_location_rec.country IS NOT NULL AND
1023 NVL(l_country, fnd_api.g_miss_char) <> l_location_rec.country)
1024 OR (l_location_rec.city IS NOT NULL AND
1025 NVL(l_city, fnd_api.g_miss_char) <> l_location_rec.city)
1026 OR (l_location_rec.postal_code IS NOT NULL AND
1027 NVL(l_postal_code, fnd_api.g_miss_char) <> l_location_rec.postal_code)
1028 OR (l_location_rec.state IS NOT NULL AND
1029 NVL(l_state, fnd_api.g_miss_char) <> l_location_rec.state)
1030 OR (l_location_rec.province IS NOT NULL AND
1031 NVL(l_province,fnd_api.g_miss_char) <> l_location_rec.province)
1032 OR (l_location_rec.county IS NOT NULL AND
1033 NVL(l_county, fnd_api.g_miss_char) <> l_location_rec.county)) THEN
1034 /*
1035 log('Start ARP_ADDS.Set_Location_CCID call.');
1036 -- call ARP_ADDS.Set_Location_CCID, according to bug 2983977
1037 -- this should be replaced by new api call that we don't need
1038 -- set org context before calling this api.
1039 -- related bug is 3105634. This is replaced by 3124266.
1040 -- According to bug 3105634, the original api will change
1041 -- org context and pass org_id to the new api
1042 ARP_ADDS.Set_Location_CCID(l_location_rec.country,
1043 l_location_rec.city,
1044 l_location_rec.state,
1045 l_location_rec.county,
1046 l_location_rec.province,
1047 l_location_rec.postal_code,
1048 l_location_rec.attribute1,
1049 l_location_rec.attribute2,
1050 l_location_rec.attribute3,
1051 l_location_rec.attribute4,
1052 l_location_rec.attribute5,
1053 l_location_rec.attribute6,
1054 l_location_rec.attribute7,
1055 l_location_rec.attribute8,
1056 l_location_rec.attribute9,
1057 l_location_rec.attribute10,
1058 l_loc_id,
1059 l_location_rec.location_id );
1060
1061 log('End ARP_ADDS.Set_Location_CCID call.');
1062 */
1063 set_loc_assign_id(p_location_rec => l_location_rec,
1064 x_return_status => l_return_status,
1065 x_msg_count => l_msg_count,
1066 x_msg_data => l_msg_data );
1067
1068 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
1069 -- write to log file about the error
1070 -- not raising error, continue the next record
1071 log('Expected error at set_loc_assign_id. Location Id: '||l_location_rec.location_id||'. Rollback changes.');
1072 rollback to get_validated_xml_pub;
1073 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1074 -- write to log file about the error
1075 -- not raising error, continue the next record
1076 log('UnExpected error at set_loc_assign_id. Location Id: '||l_location_rec.location_id||'. Rollback changes.');
1077 rollback to get_validated_xml_pub;
1078 END IF; -- check if fields are not null
1079 END IF;
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082 log('Error happens at update location. Rollback changes.');
1083 log('SQLERRM: '||SQLERRM);
1084 rollback to get_validated_xml_pub;
1085 END;
1086
1087 END IF; -- check allow update standardized address
1088 END IF; -- update location profiles
1089 END; -- begin tax if validation pass
1090 END IF; -- check return status of tax validation
1091
1092 ELSIF(l_validation_status_code > l_overwrite_threshold) THEN
1093
1094 log('Record has status code greater than threshold. Reject returned location.');
1095 -- A) if maintain history is 'Y', check if existing location profile exist or not
1096 -- 1) location profile exist, then check sst flag of the existing location profile
1097 -- a) Y: then create location profile with sst flag = 'N'
1098 -- b) N: then create location profile with sst flag = 'N' and end date
1099 -- existing record
1100 -- 2) location profile not exist, create location profile with sst flag = 'N'
1101 -- B) if maintain history is 'N', check if existing location profile exist or not
1102 -- 1) location profile exist, then check sst flag of the existing location profile
1103 -- a) Y: do nothing
1104 -- b) N: update existing location
1105 -- 2) location profile not exist, create location profile with sst flag = 'N'
1106
1107 l_location_profile_rec.validation_sst_flag := 'N';
1108 --l_location_profile_rec.effective_start_date := sysdate;
1109 --l_location_profile_rec.effective_end_date := sysdate;
1110
1111 hz_location_profile_pvt.update_location_profile (
1112 p_location_profile_rec => l_location_profile_rec,
1113 x_return_status => l_return_status,
1114 x_msg_count => l_msg_count,
1115 x_msg_data => l_msg_data
1116 );
1117
1118 ELSE
1119 log('Abnormal termination at this record.');
1120 END IF; -- compare validation_status_code and overwrite_threshold
1121
1122 END get_validated_xml;
1123
1124 PROCEDURE set_loc_assign_id (
1125 p_location_rec IN hz_location_v2pub.location_rec_type,
1126 x_return_status OUT NOCOPY VARCHAR2,
1127 x_msg_count OUT NOCOPY NUMBER,
1128 x_msg_data OUT NOCOPY VARCHAR2
1129 )
1130 IS
1131 /*
1132 CURSOR c_org(l_loc_id NUMBER) IS
1133 select distinct org_id
1134 from hz_loc_assignments
1135 where location_id = l_loc_id;
1136 */
1137 CURSOR chk_gnr(l_loc_id NUMBER) IS
1138 select 1
1139 from hz_geo_name_reference_log
1140 where location_table_name = 'HZ_LOCATIONS'
1141 and location_id = l_loc_id
1142 and rownum = 1;
1143
1144 l_location_rec hz_location_v2pub.location_rec_type;
1145 l_loc_id NUMBER;
1146 l_default_country VARCHAR2(2);
1147 l_org_id NUMBER;
1148 l_is_remit_to_location VARCHAR2(1) := 'N';
1149 l_loc_assignment_exist VARCHAR2(1) := 'N';
1150 l_dummy NUMBER;
1151 BEGIN
1152 --Initialize API return status to success.
1153 x_return_status := FND_API.G_RET_STS_SUCCESS;
1154
1155 l_location_rec := p_location_rec;
1156
1157 -- Fix bug 4539117
1158 OPEN chk_gnr(l_location_rec.location_id);
1159 FETCH chk_gnr INTO l_dummy;
1160 CLOSE chk_gnr;
1161
1162 IF(l_dummy IS NOT NULL) THEN
1163 log('Start HZ_GNR_PUB.PROCESS_GNR call.');
1164 HZ_GNR_PUB.PROCESS_GNR(p_location_table_name => 'HZ_LOCATIONS',
1165 p_location_id => l_location_rec.location_id,
1166 p_call_type => 'U',
1167 x_return_status => x_return_status,
1168 x_msg_count => x_msg_count,
1169 x_msg_data => x_msg_data);
1170 log('End HZ_GNR_PUB.PROCESS_GNR call.');
1171 ELSE
1172 log('No need to call HZ_GNR_PUB.PROCESS_GNR.');
1173 END IF;
1174 /*
1175 BEGIN
1176 SELECT 'Y'
1177 INTO l_loc_assignment_exist
1178 FROM DUAL
1179 WHERE EXISTS ( SELECT 1
1180 FROM HZ_LOC_ASSIGNMENTS la
1181 WHERE la.location_id = l_location_rec.location_id );
1182 SELECT 'Y'
1183 INTO l_is_remit_to_location
1184 FROM DUAL
1185 WHERE EXISTS ( SELECT 1
1186 FROM HZ_PARTY_SITES PS
1187 WHERE PS.LOCATION_ID = l_location_rec.location_id
1188 AND PS.PARTY_ID = -1);
1189 EXCEPTION
1190 WHEN NO_DATA_FOUND THEN
1191 NULL;
1192 END;
1193
1194 log('Loc Assignment Exist: '||l_loc_assignment_exist);
1195 log('Is Remit to Location: '||l_is_remit_to_location);
1196
1197 OPEN c_org(l_location_rec.location_id);
1198 LOOP
1199 FETCH c_org INTO l_org_id;
1200 IF c_org%NOTFOUND THEN
1201 EXIT;
1202 END IF;
1203
1204 log('Org id: '||l_org_id);
1205
1206 BEGIN
1207 SELECT default_country
1208 INTO l_default_country
1209 FROM ar_system_parameters_all
1210 WHERE org_id = l_org_id;
1211 EXCEPTION
1212 WHEN NO_DATA_FOUND THEN
1213 FND_MESSAGE.SET_NAME( 'AR','AR_NO_ROW_IN_SYSTEM_PARAMETERS');
1214 FND_MSG_PUB.ADD;
1215 --x_return_status := FND_API.G_RET_STS_ERROR;
1216 END;
1217
1218 log('Country: '||l_location_rec.country);
1219 log('Default country for the org: '||l_default_country);
1220
1221 IF l_location_rec.country = l_default_country
1222 AND l_is_remit_to_location = 'N'
1223 AND l_loc_assignment_exist = 'Y' THEN
1224 BEGIN
1225 log('Start ARP_ADDS.Set_Location_CCID call.');
1226 -- call ARP_ADDS.Set_Location_CCID, according to bug 2983977
1227 -- this should be replaced by new api call that we don't need
1228 -- set org context before calling this api.
1229 -- related bug is 3105634. This is replaced by 3124266.
1230 -- According to bug 3105634, the original api will change
1231 -- org context and pass org_id to the new api
1232 ARP_ADDS.Set_Location_CCID(l_location_rec.country,
1233 l_location_rec.city,
1234 l_location_rec.state,
1235 l_location_rec.county,
1236 l_location_rec.province,
1237 l_location_rec.postal_code,
1238 l_location_rec.attribute1,
1239 l_location_rec.attribute2,
1240 l_location_rec.attribute3,
1241 l_location_rec.attribute4,
1242 l_location_rec.attribute5,
1243 l_location_rec.attribute6,
1244 l_location_rec.attribute7,
1245 l_location_rec.attribute8,
1246 l_location_rec.attribute9,
1247 l_location_rec.attribute10,
1248 l_loc_id,
1249 l_location_rec.location_id,
1250 l_org_id );
1251
1252 log('End ARP_ADDS.Set_Location_CCID call.');
1253 END;
1254 ELSE
1255 log('No need to do Set_Location_CCID');
1256 END IF;
1257 END LOOP;
1258 CLOSE c_org;
1259 */
1260 END set_loc_assign_id;
1261
1262 PROCEDURE submit_addrval_request (
1263 p_adapter_log_id IN NUMBER,
1264 p_adapter_id IN NUMBER DEFAULT NULL,
1265 p_country_code IN VARCHAR2 DEFAULT NULL,
1266 p_module IN VARCHAR2 DEFAULT NULL,
1267 p_module_id IN NUMBER DEFAULT NULL,
1268 x_return_status OUT NOCOPY VARCHAR2,
1269 x_msg_count OUT NOCOPY NUMBER,
1270 x_msg_data OUT NOCOPY VARCHAR2 )
1271 IS
1272
1273 out_doc NCLOB;
1274
1275 CURSOR get_xml(l_adapter_log_id NUMBER) IS
1276 select to_nclob(out_doc)
1277 from HZ_ADAPTER_LOGS
1278 where adapter_log_id = l_adapter_log_id;
1279
1280 BEGIN
1281 null;
1282 /*
1283 --Initialize API return status to success.
1284 x_return_status := FND_API.G_RET_STS_SUCCESS;
1285
1286 OPEN get_xml(p_adapter_log_id);
1287 FETCH get_xml INTO out_doc;
1288 CLOSE get_xml;
1289
1290 submit_addrval_doc (
1291 p_addrval_doc => out_doc,
1292 p_adapter_id => p_adapter_id,
1293 p_country_code => p_country_code,
1294 p_module => p_module,
1295 p_module_id => p_module_id,
1296 x_return_status => x_return_status,
1297 x_msg_count => x_msg_count,
1298 x_msg_data => x_msg_data );
1299
1300 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
1301 RAISE FND_API.G_EXC_ERROR;
1302 ELSIF(x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1304 END IF;
1305
1306 EXCEPTION
1307 WHEN FND_API.G_EXC_ERROR THEN
1308 log('Error: Aborting Location Service at submit request: '||p_adapter_log_id);
1309 FND_FILE.close;
1310 -- Retcode := 2;
1311 -- Errbuf := logerror(SQLERRM);
1312
1313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1314 log('Error: Aborting Location Service at submit request: '||p_adapter_log_id);
1315 FND_FILE.close;
1316 -- Retcode := 2;
1317 -- Errbuf := logerror(SQLERRM);
1318
1319 WHEN OTHERS THEN
1320 log('Error: Aborting Location Service at submit request: '||p_adapter_log_id);
1321 log('SQL Error: '||SQLERRM);
1322 FND_FILE.close;
1323 -- Retcode := 2;
1324 -- Errbuf := logerror(SQLERRM);
1325 */
1326 END submit_addrval_request;
1327
1328 -----------------------------------------------------------------------
1329 -- Called from function rule outdoc_rule
1330 -----------------------------------------------------------------------
1331 -- This procedure is used to submit address validation request to vendor
1332 -- if adapter code is not passed, country code must exist. Then api
1333 -- will find the default adapter from profile.
1334 -- if adapter code is passed, api will call address validation service
1335 -- by using the pass in adapter.
1336 -- if both adapter code and country code are not passed, raise error
1337 -- It does the following
1338 -- 1) Set timeout
1339 -- 2) Begin request
1340 -- 3) Send XML doc to vendor
1341 -- 4) Receive response from vendor
1342 -- 5) Return XML doc
1343 ------------------------------------------------------------------------
1344 PROCEDURE submit_addrval_doc (
1345 p_addrval_doc IN OUT NOCOPY NCLOB,
1346 p_adapter_id IN NUMBER DEFAULT NULL,
1347 p_country_code IN VARCHAR2 DEFAULT NULL,
1348 p_module IN VARCHAR2 DEFAULT NULL,
1349 p_module_id IN NUMBER DEFAULT NULL,
1350 x_return_status OUT NOCOPY VARCHAR2,
1351 x_msg_count OUT NOCOPY NUMBER,
1352 x_msg_data OUT NOCOPY VARCHAR2 )
1353 IS
1354 l_return_status VARCHAR2(30);
1355 l_msg_count NUMBER;
1356 l_msg_data VARCHAR2(2000);
1357 out_xml CLOB;
1358 in_xml CLOB;
1359 http_req UTL_HTTP.REQ;
1360 http_resp UTL_HTTP.RESP;
1361 instream VARCHAR2(32767);
1362 outstream VARCHAR2(32767);
1363 outlength NUMBER;
1364 fl NUMBER;
1365 l_adapter_id NUMBER;
1366 l_adapter_log_id NUMBER := NULL;
1367 l_dummy VARCHAR2(1);
1368 l_resp_status NUMBER;
1369 l_rowid ROWID;
1370 l_write_log VARCHAR2(1);
1371 l_timeout VARCHAR2(30);
1372 l_timeout_num NUMBER;
1373 offset_var INTEGER;
1374 amount_var INTEGER;
1375
1376 outstreams outstreams_type;
1377 outlengthb NUMBER;
1378
1379 BEGIN
1380
1381 savepoint submit_addrval_doc_pub;
1382 --FND_MSG_PUB.initialize;
1383
1384 --Initialize API return status to success.
1385 x_return_status := FND_API.G_RET_STS_SUCCESS;
1386
1387 log('Adapter ID: '||p_adapter_id);
1388 log('Country Code: '||p_country_code);
1389 l_adapter_id := get_adapter_id(p_adapter_id, p_country_code);
1390 IF(l_adapter_id IS NULL) THEN
1391 log('Invalid adapter id: '||l_adapter_id);
1392 log('p_adapter_id: '||p_adapter_id);
1393 log('p_country_code: '||p_country_code);
1394 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_INVALID_ADAPTER');
1395 FND_MSG_PUB.ADD;
1396 RAISE FND_API.G_EXC_ERROR;
1397 END IF;
1398
1399 l_write_log := nvl(fnd_profile.value('HZ_WRITE_ADAPTER_LOG'), 'N');
1400
1401 -- base on profile option value to determine if write log is required
1402 IF(l_write_log = 'Y') THEN
1403 save_adapter_log(
1404 p_create_or_update => 'C',
1405 px_rowid => l_rowid,
1406 px_adapter_log_id => l_adapter_log_id,
1407 p_created_by_module => p_module,
1408 p_created_by_module_id => p_module_id,
1409 p_http_status_code => NULL,
1410 p_request_id => hz_utility_v2pub.request_id,
1411 p_object_version_number => 1,
1412 p_inout_doc => to_clob(p_addrval_doc) );
1413
1414 log('Adapter Log ID: '||l_adapter_log_id);
1415 END IF;
1416
1417 IF(l_adapter_log_id IS NOT NULL) THEN
1418 BEGIN
1419 SELECT rowid INTO l_rowid
1420 FROM HZ_ADAPTER_LOGS
1421 WHERE adapter_log_id = l_adapter_log_id;
1422 EXCEPTION
1423 WHEN NO_DATA_FOUND THEN
1424 log('Cannot find adapter log: '||l_adapter_log_id);
1425 log('x_created_by_module: '||p_module);
1426 log('x_created_by_module_id: '||p_module_id);
1427 RAISE FND_API.G_EXC_ERROR;
1428 END;
1429 END IF;
1430
1431 BEGIN
1432
1433 -- set proxy
1434 --as currently, all vendor are install base, we don't need to specify proxy
1435 --log('Set Proxy');
1436 set_proxy();--bug 6412174
1437
1438 -- set to activate detail exception support
1439 log('Set Detailed Exception Support');
1440 UTL_HTTP.SET_DETAILED_EXCP_SUPPORT(enable=>true);
1441
1442 -- get timeout profile option value
1443 -- default value is 300 seconds
1444 l_timeout := FND_PROFILE.VALUE('HZ_LOC_TIMEOUT');
1445 log('Profile option value of Location Timeout: '||l_timeout);
1446 IF(l_timeout IS NULL) THEN
1447 l_timeout_num := 300;
1448 ELSE
1449 BEGIN
1450 l_timeout_num := to_number(l_timeout);
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 log('Timeout Limit is not numeric. Set timeout limit to 2000 seconds.');
1454 l_timeout_num := 300;
1455 END;
1456 END IF;
1457 log('Set Transfer Timeout to '||l_timeout_num||' second(s).');
1458 UTL_HTTP.SET_TRANSFER_TIMEOUT(l_timeout_num);
1459
1460 -- set response error check
1461 log('Set Response Error Check');
1462 UTL_HTTP.SET_RESPONSE_ERROR_CHECK(TRUE);
1463
1464 -- begin request
1465 log('Begin Location Service Request');
1466 -- get port and url from adapter setup
1467 http_req := UTL_HTTP.BEGIN_REQUEST(get_adapter_link(l_adapter_id), 'POST', UTL_HTTP.HTTP_VERSION_1_1);
1468
1469 -- user authentication
1470 -- will get error after get response, error code should be 401 (HTTP_UNAUTHORIZED)
1471 -- ?? should have begin_request get action and get_response for authentication first and
1472 -- ?? then do another begin request post action for sending out xml doc
1473 -- according to HLD, authentication is necessary for some vendor with pay per use solution outside the
1474 -- users' firewall. For those who install locally will not require this
1475 -- check if not install locally, then do authentication
1476 -- ********************************* --
1477 -- log('Begin User Authentication');
1478 -- set_authentication (http_req, l_adapter_id);
1479 -- ********************************* --
1480
1481 -- set header
1482 -- since we know the length of the document, we don't need to do chunk on the output
1483 -- Content-Length will be set if output xml found
1484 log('Set Header for HTTP Connection');
1485 UTL_HTTP.SET_HEADER(http_req, 'Content-Type', 'application/x-www-form-urlencoded');
1486
1487 -- set transfer encoding to chunked
1488 --UTL_HTTP.SET_HEADER(http_req, 'Transfer-Encoding', 'chunked');
1489
1490 -- fix bug 4271311 - multi-bytes characters garbled
1491 UTL_HTTP.SET_BODY_CHARSET(http_req, 'UTF-8');
1492
1493 out_xml := to_clob(p_addrval_doc);
1494
1495 amount_var := MAX_LENGTH;
1496
1497 -- loop through the length of clob and do write_text
1498 outlength := dbms_lob.getlength(out_xml);
1499 -- initialize outlengthb. outlengthb is the actual length of character
1500 -- based on charset. For double byte character, the actual length should
1501 -- be half of the outlength.
1502 outlengthb := 0;
1503
1504 -- ** Oracle charset is 'UTF8' (without a dash, not 'UTF-8')
1505 IF(outlength <= MAX_LENGTH) THEN
1506 -- fix bug 3754442
1507 --outstream := out_xml;
1508 dbms_lob.read(out_xml,outlength,1,outstream);
1509 -- Count the actual length of characters to be sent by converting to UTF8 charset
1510 -- this outlengthb is used to set the content length
1511 outlengthb := LENGTHB(convert(outstream, 'UTF8'));
1512 log('Length of xml: '||outlengthb);
1513 UTL_HTTP.SET_HEADER(http_req, 'Content-Length', to_char(outlengthb));
1514 UTL_HTTP.WRITE_TEXT(http_req, outstream);
1515 ELSE
1516 -- Fix bug 4271311
1517 -- First get all data to outstreams ARRAY
1518 -- Count the total length of data based on UTF8 charset
1519 -- Set content-length
1520 -- Write text
1521 fl := floor(outlength/MAX_LENGTH);
1522 FOR i in 1..fl LOOP
1523 offset_var := ((i-1)*MAX_LENGTH)+1;
1524 dbms_lob.read(out_xml,amount_var,offset_var,outstream);
1525 --UTL_HTTP.WRITE_TEXT(http_req, outstream);
1526 log('Set outstreams');
1527 outstreams(i) := outstream;
1528 log('In Loop: '||i);
1529 outlengthb := outlengthb + LENGTHB(convert(outstream, 'UTF8'));
1530 END LOOP;
1531 amount_var := outlength-(fl*MAX_LENGTH);
1532 offset_var := (fl*MAX_LENGTH)+1;
1533 -- read only if the amount is larger than 0
1534 IF(amount_var > 0) THEN
1535 dbms_lob.read(out_xml,amount_var,offset_var,outstream);
1536 END IF;
1537
1538 --UTL_HTTP.WRITE_TEXT(http_req, outstream);
1539 outstreams(fl+1) := outstream;
1540 outlengthb := outlengthb + LENGTHB(convert(outstream, 'UTF8'));
1541 log('Out of Loop: ');
1542 log('Length of xml: '||outlengthb);
1543 -- Base on charset to set the content length
1544 UTL_HTTP.SET_HEADER(http_req, 'Content-Length', to_char(outlengthb));
1545 FOR i IN 1..fl+1 LOOP
1546 UTL_HTTP.WRITE_TEXT(http_req, outstreams(i));
1547 END LOOP;
1548 END IF;
1549
1550 -- wait for response from vendor
1551 log('Get Response from Vendor');
1552 http_resp := UTL_HTTP.GET_RESPONSE(http_req);
1553 log('- Resp.status_code: '||http_resp.status_code);
1554 log('- Resp.reason_phrase: '||http_resp.reason_phrase);
1555 log('- Resp.http_version: '||http_resp.http_version);
1556 log('- Resp.private_hndl: '||http_resp.private_hndl);
1557 l_resp_status := http_resp.status_code;
1558
1559 -- ?? only accept response status code 200 ??
1560 IF(http_resp.status_code = 200) THEN
1561 BEGIN
1562 LOOP
1563 UTL_HTTP.READ_TEXT(http_resp, instream);
1564 in_xml := in_xml||instream;
1565 END LOOP;
1566 EXCEPTION
1567 WHEN UTL_HTTP.END_OF_BODY THEN
1568 log('Location Service Transfer Finished');
1569 WHEN UTL_HTTP.TRANSFER_TIMEOUT THEN
1570 log('Location Service Timeout Occur');
1571 -- need message for invalid adapter
1572 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_TIMEOUT');
1573 RAISE FND_API.G_EXC_ERROR;
1574 WHEN OTHERS THEN
1575 log('Location Service Others Error Occur');
1576 -- need message for invalid adapter
1577 RAISE FND_API.G_EXC_ERROR;
1578 END;
1579 END IF;
1580
1581 UTL_HTTP.END_RESPONSE(http_resp);
1582
1583 EXCEPTION
1584 /* The exception handling illustrates the use of "pragma-ed" exceptions
1585 like Utl_Http.Http_Client_Error. In a realistic example, the program
1586 would use these when it coded explicit recovery actions.
1587 Request_Failed is raised for all exceptions after calling
1588 Utl_Http.Set_Detailed_Excp_Support ( enable=>false )
1589 And it is NEVER raised after calling with enable=>true */
1590 WHEN UTL_HTTP.REQUEST_FAILED THEN
1591 log('REQUEST_FAILED: ' || UTL_HTTP.GET_DETAILED_SQLERRM);
1592 --FND_FILE.close;
1593 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_ERROR');
1594 RAISE FND_API.G_EXC_ERROR;
1595
1596 -- raised by URL http://xxx.oracle.com/
1597 WHEN UTL_HTTP.HTTP_SERVER_ERROR THEN
1598 log('HTTP_SERVER_ERROR: ' || UTL_HTTP.GET_DETAILED_SQLERRM);
1599 --FND_FILE.close;
1600 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_ERROR');
1601 RAISE FND_API.G_EXC_ERROR;
1602
1603 -- raised by URL http://otn.oracle.com/xxx
1604 WHEN UTL_HTTP.HTTP_CLIENT_ERROR THEN
1605 log('HTTP_CLIENT_ERROR: ' || UTL_HTTP.GET_DETAILED_SQLERRM);
1606 --FND_FILE.close;
1607 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_ERROR');
1608 RAISE FND_API.G_EXC_ERROR;
1609
1610 WHEN UTL_HTTP.TRANSFER_TIMEOUT THEN
1611 log('HTTP TRANSFER TIMEOUT: '|| UTL_HTTP.GET_DETAILED_SQLERRM);
1612 --FND_FILE.close;
1613 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_ERROR');
1614 RAISE FND_API.G_EXC_ERROR;
1615
1616 WHEN OTHERS THEN
1617 log('HTTP OTHER EXCEPTION: '|| UTL_HTTP.GET_DETAILED_SQLERRM);
1618 log('Check SQL EXCEPTION: '|| SQLERRM);
1619 --FND_FILE.close;
1620 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_ERROR');
1621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1622 END;
1623
1624 -- ************************************************************************************ --
1625 -- update log for this transaction, from the id of the log and will update the inbound doc
1626 -- ************************************************************************************ --
1627 IF(l_adapter_log_id IS NOT NULL) THEN
1628 save_adapter_log(
1629 p_create_or_update => 'U',
1630 px_rowid => l_rowid,
1631 px_adapter_log_id => l_adapter_log_id,
1632 p_created_by_module => NULL,
1633 p_created_by_module_id => NULL,
1634 p_http_status_code => l_resp_status,
1635 p_request_id => NULL,
1636 p_object_version_number => 2,
1637 p_inout_doc => in_xml );
1638 END IF;
1639
1640 p_addrval_doc := to_nclob(in_xml);
1641
1642 EXCEPTION
1643 WHEN FND_API.G_EXC_ERROR THEN
1644 log('Error: Aborting Location Service');
1645 --FND_FILE.close;
1646 ROLLBACK TO submit_addrval_doc_pub;
1647 x_return_status := FND_API.G_RET_STS_ERROR;
1648 FND_MSG_PUB.Count_And_Get(
1649 p_encoded => FND_API.G_FALSE,
1650 p_count => x_msg_count,
1651 p_data => x_msg_data);
1652
1653 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1654 log('Error: Aborting Location Service');
1655 --FND_FILE.close;
1656 ROLLBACK TO submit_addrval_doc_pub;
1657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1658 FND_MSG_PUB.Count_And_Get(
1659 p_encoded => FND_API.G_FALSE,
1660 p_count => x_msg_count,
1661 p_data => x_msg_data);
1662
1663 WHEN OTHERS THEN
1664 log('Error: Aborting Location Service');
1665 log('SQL Error: '||SQLERRM);
1666 --FND_FILE.close;
1667 ROLLBACK TO submit_addrval_doc_pub;
1668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1669 FND_MSG_PUB.Count_And_Get(
1670 p_encoded => FND_API.G_FALSE,
1671 p_count => x_msg_count,
1672 p_data => x_msg_data);
1673
1674 END submit_addrval_doc;
1675
1676 FUNCTION get_adapter_id(
1677 p_adapter_id IN NUMBER DEFAULT NULL,
1678 p_country_code IN VARCHAR2 DEFAULT NULL
1679 ) RETURN NUMBER IS
1680 l_adapter_id NUMBER;
1681 l_dummy VARCHAR2(1);
1682
1683 CURSOR get_country_adapter(l_country_code VARCHAR2) IS
1684 select la.adapter_id
1685 from HZ_ADAPTERS la, HZ_ADAPTER_TERRITORIES t
1686 where la.adapter_id = t.adapter_id
1687 and t.territory_code = l_country_code
1688 and t.enabled_flag = 'Y'
1689 and t.default_flag = 'Y'
1690 and la.enabled_flag = 'Y'
1691 and rownum = 1;
1692
1693 CURSOR is_adapter_good(l_adapter_id NUMBER) IS
1694 select 'X'
1695 from HZ_ADAPTERS
1696 where adapter_id = l_adapter_id
1697 and enabled_flag = 'Y';
1698
1699 BEGIN
1700
1701 -- pass adapter_id
1702 IF(p_adapter_id IS NOT NULL) THEN
1703 OPEN is_adapter_good(p_adapter_id);
1704 FETCH is_adapter_good INTO l_dummy;
1705 CLOSE is_adapter_good;
1706 IF(l_dummy IS NULL) THEN
1707 l_adapter_id := NULL;
1708 ELSE
1709 l_adapter_id := p_adapter_id;
1710 END IF;
1711 ELSE
1712 IF(p_country_code IS NOT NULL) THEN
1713 -- get default adapter of the country
1714 OPEN get_country_adapter(p_country_code);
1715 FETCH get_country_adapter INTO l_adapter_id;
1716 CLOSE get_country_adapter;
1717 END IF;
1718
1719 -- try to get adapter_id from profile if not found for country_code
1720 IF(l_adapter_id IS NULL) THEN
1721 -- get default adapter from profile base
1722 l_adapter_id := fnd_profile.value('HZ_DEFAULT_LOC_ADAPTER');
1723 END IF;
1724
1725 END IF;
1726
1727 RETURN l_adapter_id;
1728
1729 END get_adapter_id;
1730
1731 PROCEDURE get_fromnto_value(
1732 p_max IN VARCHAR2,
1733 p_min IN VARCHAR2,
1734 p_op IN VARCHAR2,
1735 p_in IN VARCHAR2,
1736 p_nvl_out OUT NOCOPY VARCHAR2,
1737 p_from_out OUT NOCOPY VARCHAR2,
1738 p_to_out OUT NOCOPY VARCHAR2 ) IS
1739
1740 BEGIN
1741
1742 IF((p_op IS NULL) OR (p_in IS NULL)) THEN
1743 p_nvl_out := p_min;
1744 p_from_out := p_min;
1745 p_to_out := p_max;
1746 ELSE
1747 p_nvl_out := p_in;
1748 IF((p_op = '>') OR (p_op = '>=')) THEN
1749 p_from_out := p_in;
1750 p_to_out := p_max;
1751 ELSIF((p_op = '<') OR (p_op = '<=')) THEN
1752 p_from_out := p_min;
1753 p_to_out := p_in;
1754 ELSIF(p_op = '=') THEN
1755 p_from_out := p_in;
1756 p_to_out := p_in;
1757 END IF;
1758 END IF;
1759
1760 END get_fromnto_value;
1761
1762 /**
1763 * Procedure to add parameters to generate xml doc
1764 **/
1765
1766 PROCEDURE add_wf_parameters(
1767 p_adapter_id IN NUMBER,
1768 p_overwrite_threshold IN VARCHAR2,
1769 p_country IN VARCHAR2,
1770 p_nvl_vsc IN VARCHAR2,
1771 p_from_vsc IN VARCHAR2,
1772 p_to_vsc IN VARCHAR2,
1773 p_from_lud IN VARCHAR2,
1774 p_to_lud IN VARCHAR2,
1775 p_nvl_dv IN VARCHAR2,
1776 p_from_dv IN VARCHAR2,
1777 p_to_dv IN VARCHAR2,
1778 p_num_batch IN NUMBER,
1779 p_batch_seq IN NUMBER,
1780 p_parameter_list OUT NOCOPY wf_parameter_list_t ) IS
1781
1782 BEGIN
1783 -- map code
1784 wf_event.AddParameterToList(
1785 p_name => 'ECX_MAP_CODE',
1786 p_value => 'LOCSERV_OUT',
1787 p_parameterlist => p_parameter_list);
1788
1789 -- adapter_id
1790 wf_event.AddParameterToList(
1791 p_name => 'ADAPTER_ID',
1792 p_value => p_adapter_id,
1793 p_parameterlist => p_parameter_list);
1794
1795 -- overwrite_threshold
1796 wf_event.AddParameterToList(
1797 p_name => 'OVERWRITE_THRESHOLD',
1798 p_value => p_overwrite_threshold,
1799 p_parameterlist => p_parameter_list);
1800
1801 -- country
1802 wf_event.AddParameterToList(
1803 p_name => 'COUNTRY',
1804 p_value => p_country,
1805 p_parameterlist => p_parameter_list);
1806
1807 -- last_update_date
1808 wf_event.AddParameterToList(
1809 p_name => 'FROM_LUD',
1810 p_value => p_from_lud,
1811 p_parameterlist => p_parameter_list);
1812
1813 wf_event.AddParameterToList(
1814 p_name => 'TO_LUD',
1815 p_value => p_to_lud,
1816 p_parameterlist => p_parameter_list);
1817
1818 -- date_validated
1819 wf_event.AddParameterToList(
1820 p_name => 'NVL_DV',
1821 p_value => p_nvl_dv,
1822 p_parameterlist => p_parameter_list);
1823
1824 wf_event.AddParameterToList(
1825 p_name => 'FROM_DV',
1826 p_value => p_from_dv,
1827 p_parameterlist => p_parameter_list);
1828
1829 wf_event.AddParameterToList(
1830 p_name => 'TO_DV',
1831 p_value => p_to_dv,
1832 p_parameterlist => p_parameter_list);
1833
1834 -- validation_status_code
1835 wf_event.AddParameterToList(
1836 p_name => 'NVL_VSC',
1837 p_value => p_nvl_vsc,
1838 p_parameterlist => p_parameter_list);
1839
1840 wf_event.AddParameterToList(
1841 p_name => 'FROM_VSC',
1842 p_value => p_from_vsc,
1843 p_parameterlist => p_parameter_list);
1844
1845 wf_event.AddParameterToList(
1846 p_name => 'TO_VSC',
1847 p_value => p_to_vsc,
1848 p_parameterlist => p_parameter_list);
1849
1850 -- Total number of batch required
1851 wf_event.AddParameterToList(
1852 p_name => 'NUM_BATCH',
1853 p_value => p_num_batch,
1854 p_parameterlist => p_parameter_list);
1855
1856 -- The batch number
1857 wf_event.AddParameterToList(
1858 p_name => 'BATCH_SEQUENCE',
1859 p_value => p_batch_seq,
1860 p_parameterlist => p_parameter_list);
1861
1862 END add_wf_parameters;
1863
1864 PROCEDURE save_adapter_log(
1865 p_create_or_update IN VARCHAR2,
1866 px_rowid IN OUT NOCOPY ROWID,
1867 px_adapter_log_id IN OUT NOCOPY NUMBER,
1868 p_created_by_module IN VARCHAR2,
1869 p_created_by_module_id IN NUMBER,
1870 p_http_status_code IN VARCHAR2,
1871 p_request_id IN NUMBER,
1872 p_object_version_number IN NUMBER,
1873 p_inout_doc IN CLOB ) IS
1874 PRAGMA AUTONOMOUS_TRANSACTION;
1875 BEGIN
1876 IF(p_create_or_update = 'C') THEN
1877 HZ_ADAPTER_LOGS_PKG.Insert_Row(
1878 x_adapter_log_id => px_adapter_log_id,
1879 x_created_by_module => p_created_by_module,
1880 x_created_by_module_id => p_created_by_module_id,
1881 x_http_status_code => p_http_status_code,
1882 x_request_id => p_request_id,
1883 x_object_version_number => p_object_version_number );
1884
1885 UPDATE HZ_ADAPTER_LOGS
1886 SET out_doc = p_inout_doc
1887 WHERE adapter_log_id = px_adapter_log_id;
1888 ELSE
1889 HZ_ADAPTER_LOGS_PKG.Update_Row(
1890 x_rowid => px_rowid,
1891 x_adapter_log_id => px_adapter_log_id,
1892 x_created_by_module => NULL,
1893 x_created_by_module_id => NULL,
1894 x_http_status_code => p_http_status_code,
1895 x_request_id => NULL,
1896 x_OBJECT_VERSION_NUMBER => p_object_version_number );
1897
1898 UPDATE HZ_ADAPTER_LOGS
1899 SET in_doc = p_inout_doc
1900 WHERE adapter_log_id = px_adapter_log_id;
1901 END IF;
1902
1903 COMMIT;
1904 END save_adapter_log;
1905
1906 /**
1907 * Procedure to write a message to the log file
1908 **/
1909 PROCEDURE log(
1910 message IN VARCHAR2,
1911 newline IN BOOLEAN DEFAULT TRUE) IS
1912 BEGIN
1913 IF message = 'NEWLINE' THEN
1914 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1915 ELSIF (newline) THEN
1916 FND_FILE.put_line(fnd_file.log,message);
1917 ELSE
1918 FND_FILE.put_line(fnd_file.log,message);
1919 END IF;
1920 END log;
1921
1922 /*-----------------------------------------------------------------------
1923 | Function to fetch messages of the stack and log the error
1924 | Also returns the error
1925 |-----------------------------------------------------------------------*/
1926 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
1927 RETURN VARCHAR2 IS
1928 l_msg_data VARCHAR2(2000);
1929 BEGIN
1930 FND_MSG_PUB.Reset;
1931
1932 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1933 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
1934 END LOOP;
1935 IF (SQLERRM IS NOT NULL) THEN
1936 l_msg_data := l_msg_data || SQLERRM;
1937 END IF;
1938 log(l_msg_data);
1939 RETURN l_msg_data;
1940 END logerror;
1941
1942 FUNCTION get_adapter_link(p_adapter_id NUMBER)
1943 RETURN VARCHAR2 IS
1944 l_adapter_url VARCHAR2(2000);
1945 l_adapter_link VARCHAR2(2000);
1946 BEGIN
1947 SELECT host_address
1948 INTO l_adapter_url
1949 FROM HZ_ADAPTERS
1950 WHERE ADAPTER_ID = p_adapter_id;
1951
1952 IF(l_adapter_url IS NOT NULL) THEN
1953 l_adapter_link := rtrim(ltrim(l_adapter_url));
1954 END IF;
1955
1956 RETURN l_adapter_link;
1957 END get_adapter_link;
1958
1959 FUNCTION indoc_rule (
1960 p_subscription_guid IN RAW,
1961 p_event IN OUT NOCOPY wf_event_t )
1962 RETURN VARCHAR2 IS
1963 l_event_data CLOB := NULL;
1964 l_ecx_map_code VARCHAR2(30);
1965 l_adapter_id NUMBER;
1966 l_overwrite_threshold VARCHAR2(30);
1967 l_batch_sequence NUMBER;
1968 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1969 BEGIN
1970 Log('indoc_rule called');
1971 l_event_data := p_event.getEventData;
1972 IF(l_event_data IS NOT NULL) THEN
1973 l_ecx_map_code := p_event.getValueForParameter('ECX_MAP_CODE');
1974 l_adapter_id := p_event.getValueForParameter('ADAPTER_ID');
1975 l_overwrite_threshold := p_event.getValueForParameter('OVERWRITE_THRESHOLD');
1976 l_batch_sequence := p_event.getValueForParameter('BATCH_SEQUENCE');
1977 Log('ECX Map Code: '||l_ecx_map_code);
1978 Log('Adapter_Id: '||l_adapter_id);
1979 Log('Overwrite_Threshold: '||l_overwrite_threshold);
1980 Log('Batch_Sequence: '||l_batch_sequence);
1981
1982 ecx_standard.processXMLCover(
1983 i_map_code =>l_ecx_map_code,
1984 i_inpayload =>l_event_data,
1985 i_debug_level =>3
1986 );
1987
1988 END IF;
1989 RETURN 'SUCCESS';
1990 END indoc_rule;
1991
1992 PROCEDURE validate_mandatory_column(
1993 p_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
1994 x_return_status IN OUT NOCOPY VARCHAR2) IS
1995 BEGIN
1996
1997 IF(p_location_rec.address1 IS NULL OR p_location_rec.address1 = FND_API.G_MISS_CHAR) THEN
1998 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1999 FND_MESSAGE.SET_TOKEN('COLUMN' ,'ADDRESS1');
2000 FND_MSG_PUB.ADD;
2001 log('Address1 is mandatory');
2002 RAISE FND_API.G_EXC_ERROR;
2003 END IF;
2004
2005 IF(p_location_rec.country IS NULL OR p_location_rec.country = FND_API.G_MISS_CHAR) THEN
2006 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2007 FND_MESSAGE.SET_TOKEN('COLUMN' ,'COUNTRY');
2008 FND_MSG_PUB.ADD;
2009 log('Country is mandatory');
2010 RAISE FND_API.G_EXC_ERROR;
2011 END IF;
2012 EXCEPTION
2013 WHEN FND_API.G_EXC_ERROR THEN
2014 x_return_status := FND_API.G_RET_STS_ERROR;
2015
2016 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2018
2019 WHEN OTHERS THEN
2020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2021 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2022 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2023 FND_MSG_PUB.ADD;
2024 END validate_mandatory_column;
2025
2026 -----------------------------------------------------------------------
2027 -- Called from address_validation
2028 -----------------------------------------------------------------------
2029 -- As address_validation raise wf event to generate xml, this function
2030 -- rule which is defined for a wf event will be called. The wf event
2031 -- is called oracle.apps.ar.hz.locservice.generatexml
2032 -- This function rule will do the following
2033 -- 1) Get the generated xml doc
2034 -- 2) Pass the xml doc to submit_addrval_doc
2035 -- 3) Get returned validated xml doc, raise another wf event to parse
2036 -- the validated addresses.
2037 ------------------------------------------------------------------------
2038 FUNCTION outdoc_rule (
2039 p_subscription_guid IN RAW,
2040 p_event IN OUT NOCOPY wf_event_t )
2041 RETURN VARCHAR2 IS
2042 l_event_data CLOB := NULL;
2043 l_event_nclob_data NCLOB := NULL;
2044 l_adapter_id NUMBER;
2045 l_overwrite_threshold VARCHAR2(30);
2046 l_batch_sequence NUMBER;
2047 l_adapter_log_id NUMBER;
2048 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
2049 errmsg VARCHAR2(2000);
2050 l_return_status VARCHAR2(30);
2051 l_msg_count NUMBER;
2052 l_msg_data VARCHAR2(2000);
2053 l_event_key VARCHAR2(100);
2054 BEGIN
2055 Log('NEWLINE');
2056 Log('outdoc_rule called');
2057 l_event_data := p_event.getEventData;
2058 IF(l_event_data IS NOT NULL) THEN
2059 l_adapter_id := p_event.getValueForParameter('ADAPTER_ID');
2060 l_overwrite_threshold := p_event.getValueForParameter('OVERWRITE_THRESHOLD');
2061 l_batch_sequence := p_event.getValueForParameter('BATCH_SEQUENCE');
2062 Log('Adapter Id: '||l_adapter_id);
2063 Log('Overwrite Threshold: '||l_overwrite_threshold);
2064 Log('Batch Sequence: '||l_batch_sequence);
2065 END IF;
2066
2067 l_event_nclob_data := to_nclob(l_event_data);
2068
2069 submit_addrval_doc (
2070 p_addrval_doc => l_event_nclob_data,
2071 p_adapter_id => l_adapter_id,
2072 p_country_code => NULL,
2073 p_module => 'HZ_LOCSERVICE',
2074 p_module_id => hz_utility_v2pub.request_id,
2075 x_return_status => l_return_status,
2076 x_msg_count => l_msg_count,
2077 x_msg_data => l_msg_data );
2078
2079 l_event_data := to_clob(l_event_nclob_data);
2080
2081 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
2082 RAISE FND_API.G_EXC_ERROR;
2083 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2084 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2085 END IF;
2086
2087 l_parameter_list := wf_parameter_list_t();
2088
2089 wf_event.AddParameterToList(
2090 p_name => 'ECX_MAP_CODE',
2091 p_value => 'LOCSERV_IN',
2092 p_parameterlist => l_parameter_list);
2093
2094 wf_event.AddParameterToList(
2095 p_name => 'ADAPTER_ID',
2096 p_value => l_adapter_id,
2097 p_parameterlist => l_parameter_list);
2098
2099 wf_event.AddParameterToList(
2100 p_name => 'OVERWRITE_THRESHOLD',
2101 p_value => l_overwrite_threshold,
2102 p_parameterlist => l_parameter_list);
2103
2104 wf_event.AddParameterToList(
2105 p_name => 'BATCH_SEQUENCE',
2106 p_value => l_batch_sequence,
2107 p_parameterlist => l_parameter_list);
2108
2109 -- raise event to retrieve inbound xml doc, which is indoc_rule
2110 l_event_key := 'HZ_LOCSERVICE_INBOUND-'||hz_utility_v2pub.request_id||'-'||l_adapter_id||'-'||to_char(sysdate,'DD-MON-YYYY HH:MI:SS') ;
2111
2112 wf_event.raise(
2113 p_event_name => 'oracle.apps.ar.hz.locservice.parsexml',
2114 p_event_key => l_event_key,
2115 p_event_data => l_event_data,
2116 p_parameters => l_parameter_list,
2117 p_send_date => NULL);
2118
2119 l_parameter_list.DELETE;
2120
2121 RETURN 'SUCCESS';
2122 EXCEPTION
2123 WHEN FND_API.G_EXC_ERROR THEN
2124 log('Expected Error: Aborting Location Service for this batch');
2125 Wf_Core.Context('ECX_RULE', 'GENERATEXML', p_event.getEventName(), p_subscription_guid);
2126 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2127 --return 'ERROR';
2128 RAISE FND_API.G_EXC_ERROR;
2129 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2130 log('Unexpected Error: Aborting Location Service for this batch');
2131 Wf_Core.Context('ECX_RULE', 'GENERATEXML', p_event.getEventName(), p_subscription_guid);
2132 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2133 --return 'ERROR';
2134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2135 WHEN OTHERS THEN
2136 log('Others Error: Aborting Location Service for this batch');
2137 log('SQL Error: '||SQLERRM);
2138 Wf_Core.Context('ECX_RULE', 'GENERATEXML', p_event.getEventName(), p_subscription_guid);
2139 wf_event.setErrorInfo(p_event, 'ERROR');
2140 --return 'ERROR';
2141 RAISE;
2142 END outdoc_rule;
2143
2144 END HZ_LOCATION_SERVICES_PUB;