[Home] [Help]
PACKAGE BODY: APPS.FV_CCR_DATA_LOAD_PKG
Source
1 package body FV_CCR_DATA_LOAD_PKG AS
2 /* $Header: FVCCRLDB.pls 120.11.12020000.3 2013/02/12 21:10:00 snama ship $*/
3
4 type l_bus_codes is table of fnd_lookup_values.lookup_code%type index by binary_integer;
5
6 --sthota
7 type l_vendor_ids is table of fv_ccr_vendors.vendor_id%type index by binary_integer;
8 vendor_ids l_vendor_ids;
9
10 type l_duns is table of fv_ccr_vendors.duns%type index by binary_integer;
11 duns_ids l_duns;
12
13
14 type lookup_info is record
15 ( rec_type varchar2(2),
16 code varchar2(10)
17 );
18
19 type lookup_data is table of lookup_info index by binary_integer;
20
21 bus_code l_bus_codes;
22 sic_code l_bus_codes;
23 naic_code l_bus_codes;
24 fsc_code l_bus_codes;
25 psc_code l_bus_codes;
26
27 CURSOR c_bus_codes IS SELECT lookup_code from fnd_lookup_values where lookup_type = 'FV_BUSINESS_TYPE' and language = userenv('LANG');
28 CURSOR c_sic_codes IS SELECT lookup_code from fnd_lookup_values where lookup_type = 'FV_SIC_TYPE' and language = userenv('LANG');
29 CURSOR c_naic_codes IS SELECT lookup_code from fnd_lookup_values where lookup_type = 'FV_NAICS_TYPE' and language = userenv('LANG');
30 CURSOR c_fsc_codes IS SELECT lookup_code from fnd_lookup_values where lookup_type = 'FV_FSC_TYPE' and language = userenv('LANG');
31 CURSOR c_psc_codes IS SELECT lookup_code from fnd_lookup_values where lookup_type = 'FV_PSC_TYPE' and language = userenv('LANG');
32
33 PROCEDURE INSERT_TEMP_DATA( p_record_type number,
34 p_duns varchar2,
35 p_reference1 varchar2,
36 p_reference2 varchar2,
37 p_reference3 varchar2 ,
38 p_reference4 varchar2 ,
39 p_reference5 varchar2 )
40 IS
41 BEGIN
42 INSERT INTO FV_CCR_PROCESS_REPORT
43 (record_type,
44 duns_info,
45 reference1,
46 reference2,
47 reference3,
48 reference4,
49 reference5
50 )
51 VALUES
52 (p_record_type,
53 p_duns ,
54 p_reference1,
55 p_reference2,
56 p_reference3,
57 p_reference4,
58 p_reference5
59 );
60
61 EXCEPTION WHEN OTHERS THEN
62 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Insert into temp table',SQLERRM);
63 END;
64
65 PROCEDURE find_code ( p_lookup IN OUT NOCOPY lookup_data
66 )
67 IS
68
69 CURSOR c_lookup_info(c_type varchar2, c_code varchar2) IS
70 select lookup_code from fnd_lookup_values
71 where lookup_type = c_type
72 and lookup_code = c_code
73 and language = userenv('LANG');
74
75 code_exist boolean;
76 idx binary_integer;
77 l_errbuf varchar2(1000);
78 message_text varchar2(2000);
79 message_action varchar(2000);
80 l_token varchar2(100);
81
82 l_lookup_code fnd_lookup_values.lookup_code%type;
83
84 BEGIN
85 l_errbuf := 'Start - > find code ';
86 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find code ',l_errbuf);
87
88 FOR i in p_lookup.first..p_lookup.last
89 LOOP
90 code_exist := false ;
91 idx:= 1;
92 IF (p_lookup(i).rec_type ='B' and p_lookup(i).code <> ' ') THEN
93 FOR idx in 1 ..bus_code.count
94 LOOP
95 IF bus_code.exists( idx ) THEN
96 IF bus_code(idx) = p_lookup(i).code THEN
97 code_exist := true;
98 END IF;
99 END IF;
100 END LOOP;
101 IF not code_exist THEN
102
103 FND_MESSAGE.set_NAME('FV','FV_CCR_TYPE_INEXISTS');
104 FND_MESSAGE.set_TOKEN('TYPE','Business Type');
105 FND_MESSAGE.SET_TOKEN('CODE',p_lookup(i).code);
106 message_text := FND_MESSAGE.get;
107
108 FND_MESSAGE.set_NAME('FV','FV_CCR_ACTION5');
109 FND_MESSAGE.set_token('TYPE',p_lookup(i).code);
110
111 message_action := FND_MESSAGE.get;
112
113 l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
114 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
115 insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
116 END IF;
117 ELSIF (p_lookup(i).rec_type ='B' and p_lookup(i).code = ' ') THEN
118 p_lookup(i).code := null;
119 ELSIF (p_lookup(i).rec_type ='S' and p_lookup(i).code <> ' ') THEN
120 IF substr(p_lookup(i).code,5,4) =' ' THEN
121 p_lookup(i).code := substr(p_lookup(i).code,1,4);
122 END IF;
123 FOR idx in 1 ..sic_code.count
124 LOOP
125 IF sic_code.exists( idx ) THEN
126 IF sic_code(idx) = p_lookup(i).code THEN
127 code_exist := true;
128 END IF;
129 END IF;
130 END LOOP;
131 IF not code_exist THEN
132
133 FND_MESSAGE.set_NAME('FV','FV_CCR_TYPE_INEXISTS');
134 FND_MESSAGE.set_TOKEN('TYPE','SIC Code');
135 FND_MESSAGE.SET_TOKEN('CODE',p_lookup(i).code);
136
137 message_text := FND_MESSAGE.get;
138
139 FND_MESSAGE.set_NAME('FV','FV_CCR_ACTION5');
140 FND_MESSAGE.set_token('TYPE',p_lookup(i).code);
141
142 message_action := FND_MESSAGE.get;
143
144 l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
145 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
146 insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
147 END IF;
148 ELSIF (p_lookup(i).rec_type ='S' and p_lookup(i).code = ' ') THEN
149 p_lookup(i).code := null;
150 ELSIF (p_lookup(i).rec_type ='N' and p_lookup(i).code <> ' ') THEN
151 FOR idx in 1 ..naic_code.count
152 LOOP
153 IF naic_code.exists( idx ) THEN
154 IF naic_code(idx) = p_lookup(i).code THEN
155 code_exist := true;
156 END IF;
157 END IF;
158 END LOOP;
159 IF not code_exist THEN
160
161 FND_MESSAGE.set_NAME('FV','FV_CCR_TYPE_INEXISTS');
162 FND_MESSAGE.set_TOKEN('TYPE','NAICS Code');
163 FND_MESSAGE.SET_TOKEN('CODE',p_lookup(i).code);
164 message_text := FND_MESSAGE.get;
165
166 FND_MESSAGE.set_NAME('FV','FV_CCR_ACTION5');
167 FND_MESSAGE.set_token('TYPE',p_lookup(i).code);
168
169 message_action := FND_MESSAGE.get;
170
171 l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
172 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
173 insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
174 END IF;
175 ELSIF (p_lookup(i).rec_type ='N' and p_lookup(i).code = ' ') THEN
176 p_lookup(i).code := null;
177 ELSIF (p_lookup(i).rec_type ='F' and p_lookup(i).code <> ' ') THEN
178 FOR idx in 1 ..fsc_code.count
179 LOOP
180 IF fsc_code.exists( idx ) THEN
181 IF fsc_code(idx) = p_lookup(i).code THEN
182 code_exist := true;
183 END IF;
184 END IF;
185 END LOOP;
186 IF not code_exist THEN
187
188 FND_MESSAGE.set_NAME('FV','FV_CCR_TYPE_INEXISTS');
189 FND_MESSAGE.set_TOKEN('TYPE','FSC code');
190 FND_MESSAGE.SET_TOKEN('CODE',p_lookup(i).code);
191
192 message_text := FND_MESSAGE.get;
193
194 FND_MESSAGE.set_NAME('FV','FV_CCR_ACTION5');
195 FND_MESSAGE.set_token('TYPE',p_lookup(i).code);
196
197 message_action := FND_MESSAGE.get;
198
199 l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
200 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
201 insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
202 END IF;
203 ELSIF (p_lookup(i).rec_type ='F' and p_lookup(i).code = ' ') THEN
204 p_lookup(i).code := null;
205 ELSIF (p_lookup(i).rec_type ='P' and p_lookup(i).code <> ' ') THEN
206 FOR idx in 1 ..psc_code.count
207 LOOP
208 IF psc_code.exists( idx ) THEN
209 IF psc_code(idx) = p_lookup(i).code THEN
210 code_exist := true;
211 END IF;
212 END IF;
213 END LOOP;
214 IF not code_exist THEN
215
216 FND_MESSAGE.set_NAME('FV','FV_CCR_TYPE_INEXISTS');
217 FND_MESSAGE.set_TOKEN('TYPE','PSC Code');
218 FND_MESSAGE.SET_TOKEN('CODE',p_lookup(i).code);
219
220 message_text := FND_MESSAGE.get;
221
222 FND_MESSAGE.set_NAME('FV','FV_CCR_ACTION5');
223 FND_MESSAGE.set_token('TYPE',p_lookup(i).code);
224
225 message_action := FND_MESSAGE.get;
226
227 l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
228 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
229 insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
230 END IF;
231 ELSIF (p_lookup(i).rec_type ='P' and p_lookup(i).code = ' ') THEN
232 p_lookup(i).code := null;
233
234 ELSIF (p_lookup(i).rec_type ='O' and p_lookup(i).code <> ' ') THEN
235 OPEN c_lookup_info('FV_ORGANIZATION_TYPE',p_lookup(i).code);
236 FETCH c_lookup_info into l_lookup_code;
237 IF c_lookup_info%FOUND THEN
238 code_exist := true;
239 END IF;
240 CLOSE c_lookup_info;
241 IF not code_exist THEN
242
243 FND_MESSAGE.set_NAME('FV','FV_CCR_TYPE_INEXISTS');
244 FND_MESSAGE.set_TOKEN('TYPE','Organization Type');
245 FND_MESSAGE.SET_TOKEN('CODE',p_lookup(i).code);
246 message_text := FND_MESSAGE.get;
247
248 FND_MESSAGE.set_NAME('FV','FV_CCR_ACTION5');
249 FND_MESSAGE.set_token('TYPE',p_lookup(i).code);
250
251 message_action := FND_MESSAGE.get;
252
253 l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
254 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
255 insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
256 END IF;
257 ELSIF (p_lookup(i).rec_type ='O' and p_lookup(i).code = ' ') THEN
258 p_lookup(i).code := null;
259 ELSIF (p_lookup(i).rec_type ='C' and p_lookup(i).code <> ' ') THEN
260 OPEN c_lookup_info('FV_CORRESPOND_TYPE',p_lookup(i).code);
261 FETCH c_lookup_info into l_lookup_code;
262 IF c_lookup_info%FOUND THEN
263 code_exist := true;
264 END IF;
265 CLOSE c_lookup_info;
266 IF not code_exist THEN
267
268 FND_MESSAGE.set_NAME('FV','FV_CCR_TYPE_INEXISTS');
269 FND_MESSAGE.set_TOKEN('TYPE','Correspondence Type');
270 FND_MESSAGE.SET_TOKEN('CODE',p_lookup(i).code);
271 message_text := FND_MESSAGE.get;
272
273 FND_MESSAGE.set_NAME('FV','FV_CCR_ACTION5');
274 FND_MESSAGE.set_token('TYPE',p_lookup(i).code);
275
276 message_action := FND_MESSAGE.get;
277
278 l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
279 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
280 insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
281 END IF;
282 ELSIF (p_lookup(i).rec_type ='C' and p_lookup(i).code = ' ') THEN
283 p_lookup(i).code := null;
284 ELSIF ( (p_lookup(i).rec_type ='CS' or p_lookup(i).rec_type ='ES' )
285 and p_lookup(i).code <> ' '
286 ) THEN
287 OPEN c_lookup_info('FV_SECURITY_LEVEL',p_lookup(i).code);
288 FETCH c_lookup_info into l_lookup_code;
289 IF c_lookup_info%FOUND THEN
290 code_exist := true;
291 END IF;
292 CLOSE c_lookup_info;
293 IF not code_exist THEN
294
295 select decode(p_lookup(i).code,'CS','Corporate Security Code', 'Employee Security Code') into l_token
296 from dual;
297
298 FND_MESSAGE.set_NAME('FV','FV_CCR_TYPE_INEXISTS');
299 FND_MESSAGE.set_TOKEN('TYPE',p_lookup(i).rec_type);
300 FND_MESSAGE.SET_TOKEN('CODE',l_token);
301 message_text := FND_MESSAGE.get;
302
303 FND_MESSAGE.set_NAME('FV','FV_CCR_ACTION5');
304 FND_MESSAGE.set_token('TYPE',p_lookup(i).code);
305
306 message_action := FND_MESSAGE.get;
307
308 l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
309 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
310 insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
311 END IF;
312 ELSIF ( (p_lookup(i).rec_type ='CS' or p_lookup(i).rec_type ='ES' )
313 and p_lookup(i).code = ' '
314 ) THEN
315 p_lookup(i).code := null;
316 END IF; -- end of type -B
317 END LOOP;
318
319 exception when others then
320 IF c_lookup_info%isopen then
321 close c_lookup_info;
322 END IF;
323 l_errbuf := 'unexpected exception ' || SQLERRM;
324 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find code',l_errbuf);
325 END;
326
327 PROCEDURE MAIN( errbuf OUT NOCOPY VARCHAR2,
328 retcode OUT NOCOPY NUMBER,
329 p_file_location IN VARCHAR2,
330 p_file_Name IN VARCHAR2,
331 p_file_type IN VARCHAR2,
332 p_update_type IN VARCHAR2,
333 p_dummy IN NUMBER,
334 p_duns IN VARCHAR2 ,
335 p_xml_import IN VARCHAR2 ,
336 p_insert_data IN VARCHAR2
337 ) IS
338
339 l_data_file VARCHAR2(100);
340 p_phase VARCHAR2(100);
341 p_status VARCHAR2(100);
342 p_dev_phase VARCHAR2(100);
343 p_dev_status VARCHAR2(100);
344 p_message VARCHAR2(100);
345 l_request_id NUMBER;
346 l_request_wait_status BOOLEAN;
347 l_module_name VARCHAR2(1000);
348 l_errbuf VARCHAR2(1000);
349 l_file_type varchar2(1);
350 l_invalid_file_name boolean;
351 l_file_date DATE;
352 l_juliandate VARCHAR2(5);
353 l_extract_code varchar2(1);
354 l_extract_type varchar2(1);
355 l_status varchar2(1);
356 l_pos23 varchar2(2);
357 l_message_action1 varchar2(2000);
358 l_message_action2 varchar2(2000);
359 l_message_action3 varchar2(2000);
360 l_message_action4 varchar2(2000);
361 l_msg_inv_file_name varchar2(2000);
362 l_msg_inv_file_type varchar2(2000);
363 l_msg_julian_date varchar2(2000);
364 l_msg_no_duns varchar2(2000);
365 l_msg_pay_obj varchar2(2000);
366 l_verify_existence VARCHAR2(1);
367 l_title1set boolean :=false;
368 l_title2set boolean :=false;
369 l_title3set boolean :=false;
370 --sthota
371 l_title4set boolean :=false;
372 l_lbe_change varchar2(120);
373 message_text varchar2(1000);
374 dummy number;
375 l_duns_list varchar2(1000);
376 l_counter number;
377 l_valid_tin varchar2(9);
378 l_user_id CONSTANT number := fnd_global.user_id;
379 l_xml_opt_param_set varchar2(1);
380 l_update_type varchar2(1);
381 l_report_count number;
382 l_run_from_xml varchar2(1); -- added by ks for 5906546
383 i number;
384
385 CURSOR c_ccr_data IS
386 SELECT * from fv_ccr_process_gt g
387 WHERE ( extract_code in ('A','2','3')
388 or ( l_run_from_xml = 'Y' and extract_code = '4' -- modified by ks 5906546.
389 and not exists (select 'first run'
390 from fv_ccr_orgs o
391 where o.duns = g.duns)
392 )
393 )
394 order by rowid;
395
396 CURSOR c_ccr_rep IS
397 SELECT
398 DUNS_INFO,
399 RECORD_TYPE,
400 NVL(REFERENCE1,' ') REFERENCE1,
401 NVL(REFERENCE2,' ') REFERENCE2,
402 NVL(REFERENCE3,' ') REFERENCE3,
403 DECODE(REFERENCE4,'A','Active','E','Expired','N','Unknown','U','Unregistered',REFERENCE4) REFERENCE4,
404 REFERENCE5,
405 REFERENCE6,
406 REFERENCE7,
407 REFERENCE8,
408 REFERENCE9,
409 REFERENCE10
410 from fv_ccr_process_report order by record_type,rowid;
411
412
413
414 CURSOR c_taxpayer is
415 select distinct fcv.taxpayer_id,fcv.vendor_id
416 from fv_ccr_vendors fcv
417 where exists (SELECT 1 FROM fv_ccr_vendors fcv_in
418 WHERE fcv_in.taxpayer_id=fcv.taxpayer_id
419 AND fcv_in.vendor_id = fcv.vendor_id
420 AND fcv_in.legal_bus_name<>fcv.legal_bus_name
421 AND fcv_in.taxpayer_id is not null
422 AND fcv_in.plus_four is null
423 AND fcv_in.vendor_id is not null
424 AND fcv_in.ccr_status not in ('E','D'))
425 AND fcv.plus_four IS NULL
426 AND fcv.taxpayer_id IS NOT NULL
427 AND fcv.vendor_id is not null
428 AND fcv.ccr_status not in ('E','D')
429 AND exists (select 1 from fv_ccr_process_gt fcpg
430 where fcpg.duns = fcv.duns and fcpg.plus_four IS NULL);
431
432 CURSOR c_duns_info(p_taxpayer_id varchar2,p_vendor_id number) IS
433 select fcv.duns,fcv.plus_four ,fcv.legal_bus_name,fcv.taxpayer_id
434 from fv_ccr_vendors fcv
435 where fcv.plus_four is null
436 and fcv.taxpayer_id=p_taxpayer_id
437 and fcv.vendor_id=p_vendor_id;
438
439 cursor c_vendor_info(p_vid number) is
440 select vendor_name from po_vendors
441 where vendor_id =p_vid;
442
443 l_ccr_data c_ccr_data%rowtype;
444 l_code lookup_data;
445 l_vendor_id varchar2(240);
446 --sthota
447 l_active_vendor_id varchar2(240);
448 l_vendor_cnt number := 0;
449 l_active_vendor_exists number;
450 l_vendor_name varchar2(240);
451
452 BEGIN
453 -- Bug 3872908
454 IF (p_xml_import = 'Y') THEN
455 l_xml_opt_param_set := SUBSTR(p_update_type, 2, 1);
456 l_update_type := SUBSTR(p_update_type, 1, 1);
457 SELECT sysdate into l_file_date FROM dual; -- Bug 3931555, 3936532
458 ELSE
459 l_update_type := p_update_type;
460 END IF;
461
462 l_module_name := 'CCR Data Load Tfr';
463 l_errbuf :='Start of Transfer';
464
465 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
466
467 -- construct the file name from the file location and name
468 -- file name ( '\\' --> for WINDOWS NT , '/' --> UNIX )
469
470
471 FND_MESSAGE.SET_NAME('FV','FV_CCR_ACTION1');
472 l_message_action1 := FND_MESSAGE.GET;
473
474 FND_MESSAGE.SET_NAME('FV','FV_CCR_ACTION2');
475 l_message_action2 := FND_MESSAGE.GET;
476
477 FND_MESSAGE.SET_NAME('FV','FV_CCR_ACTION3');
478 l_message_action3 := FND_MESSAGE.GET;
479
480 FND_MESSAGE.SET_NAME('FV','FV_CCR_ACTION4');
481 l_message_action4 := FND_MESSAGE.GET;
482
483 FND_MESSAGE.SET_NAME('FV','FV_CCR_NO_DUNS');
484 l_msg_no_duns := FND_MESSAGE.GET;
485
486 -- need to perform file name validations only if we are intending to call direct
487 IF p_xml_import <> 'Y' THEN
488
489 l_errbuf :='Compare the Directory - unix/NT ';
490 l_invalid_file_name := FALSE;
491
492 IF (INSTR(p_file_location, '/') <> 0 ) THEN
493 l_data_file := p_file_location || '/' || p_file_name;
494 l_errbuf := 'Unix directory -'||l_data_file;
495 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
496 ELSE
497 l_data_file := p_file_location || '\\' || p_file_name;
498 l_errbuf := 'NT Directory file name - '||l_data_file;
499 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
500
501 END IF;
502
503
504 -- check for file naming convention
505 -- Position #1
506 IF (substr(p_file_name,1,1) <> 'B') THEN
507 l_invalid_file_name := TRUE;
508 l_errbuf := 'Error in Position#1 of filename';
509 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
510
511 FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_NAME');
512 FND_MESSAGE.SET_TOKEN('FILE',p_file_name);
513 l_msg_inv_file_name := FND_MESSAGE.GET;
514
515 insert_temp_data(3,null,l_msg_inv_file_name,l_message_action1,null,null,null);
516 END IF;
517
518
519 -- Position #2-3
520 l_pos23 := substr(p_file_name,2,2) ;
521 IF (l_pos23 not in ('CR','CD')) THEN
522 l_invalid_file_name := TRUE;
523 l_errbuf := 'Error in Position#2-3 of filename';
524 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
525
526 FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_NAME');
527 FND_MESSAGE.SET_TOKEN('FILE',p_file_name);
528 l_msg_inv_file_name := FND_MESSAGE.GET;
529 insert_temp_data(3,null,l_msg_inv_file_name,l_message_action1,null,null,null);
530 END IF;
531
532 --find out if master/sensitive
533 /*
534 l_errbuf := 'File Type '|| p_file_type;
535 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
536
537 IF ((p_file_type ='M' and l_pos23 not in ('CR','CD'))
538 OR
539 (p_file_type ='S' and l_pos23 not in ('SR','SD')) ) THEN
540 l_invalid_file_name := TRUE;
541 l_errbuf := 'The file name does not match the file type';
542 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
543
544 FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_NAME');
545 FND_MESSAGE.SET_TOKEN('FILE',p_file_name);
546 l_msg_inv_file_name := FND_MESSAGE.GET;
547 insert_temp_data(3,null,l_msg_inv_file_name,l_message_action4,null,null,null);
548
549 END IF;*/
550
551 -- Throw error if invalid file name
552 IF l_invalid_file_name THEN
553 retcode :=-1;
554 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
555 RETURN;
556 END IF;
557
558 -- Julian date validation
559 l_juliandate := substr(p_file_name,10,5);--Changed 4 to 9
560 begin
561 select to_date(l_juliandate,'YYDDD') into l_file_date from dual;
562 exception when others THEN
563 retcode :=-1;
564 l_errbuf := SQLERRM;
565 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
566
567 FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_NAME');
568 FND_MESSAGE.SET_TOKEN('FILE',p_file_name);
569 l_msg_inv_file_name := FND_MESSAGE.GET;
570
571 FND_MESSAGE.SET_NAME('FV','FV_CCR_JULIAN_DATE_ACTION');
572 FND_MESSAGE.SET_TOKEN('DATE',l_juliandate);
573 l_msg_julian_date := FND_MESSAGE.GET;
574
575 insert_temp_data(3,null,l_msg_inv_file_name,l_msg_julian_date,null,null,null);
576 RETURN;
577 end; -- end of julian date check
578
579
580 -- Check for file format to decide which Loader to Run
581 --FVCCRLDC -> for Complete
582 --FVCCRLDS -> for Sensitive
583
584 --submit the request for SQLLOAD
585 -- IF (p_file_type ='M' ) THEN
586 l_errbuf := 'Submitting the Master Complete Load request ';
587 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
588
589 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
590 application => 'FV',
591 program => 'FVCCRLDC',
592 description => 'CCR Data Load Complete',
593 start_time => '',
594 sub_request => FALSE ,
595 argument1 => l_data_file ) ;
596 commit;
597 l_errbuf :='Request Id - >'||l_request_id||SQLERRM;
598 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
599
600 /* ELSIF (p_file_type ='S') THEN
601 l_errbuf :='Submitting Sensitive Load Request';
602 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
603
604 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
605 application => 'FV',
606 program => 'FVCCRLDS',
607 description => 'CCR Data Load Sensitive',
608 start_time => '',
609 sub_request => FALSE ,
610 argument1 => l_data_file ) ;
611 commit;
612 l_errbuf :='Request Id - >'||l_request_id||SQLERRM;
613 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
614
615 END IF;*/
616
617
618 IF (l_request_id = 0) THEN
619 retcode := -1;
620 l_errbuf := 'Failed to submit request for SQL*LOADER';
621 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
622 RETURN;
623 ELSE
624 COMMIT;
625 END IF;
626
627 -- wait for request to get completed
628 l_request_wait_status := fnd_concurrent.wait_for_request(
629 request_id => l_request_id,
630 interval => 20,
631 max_wait => 0,
632 phase => p_phase,
633 status => p_status,
634 dev_phase => p_dev_phase,
635 dev_status => p_dev_status,
636 message => p_message);
637
638
639 -- end of SQL LOAD
640
641 l_errbuf := 'SQL Loader completed upload successfully in Status '||p_status;
642 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
643
644 -- incase the request completed in error , parent request also should be errored
645 IF p_status ='Error' THEN
646 retcode := -1;
647 l_errbuf := 'Loader Request Errored out';
648 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
649 RETURN;
650 END IF;
651 /*
652 -- find out the type of file - monthly extract /daily extract
653 IF l_pos23 = 'CR' THEN
654 --TODO check for exception thrown by this select statement
655 l_errbuf := 'Checking the info in Extract code';
656 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
657
658 IF p_status ='Warning' THEN
659 SELECT count(1) into dummy from fv_ccr_file_temp;
660 IF dummy >=1 THEN
661 SELECT extract_code into l_extract_type from fv_ccr_file_temp WHERE rownum=1;
662 ELSE
663 retcode := -1;
664 l_errbuf := 'Not even a single row was processed in Loader. Please verify';
665 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
666 RETURN;
667 END IF;
668 END IF;
669
670
671 -- if extract_code is not 'A' for a monthly file type then error out
672 IF l_extract_type <>'A' THEN
673 retcode := -1;
674 l_errbuf := 'Not a valid Monthly refresh file ';
675 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
676 insert_temp_data(3,null,'FV_CCR_INVALID_FILE_TYPE',l_message_action2,null,null,null);
677 RETURN;
678 END IF; -- end of extract type check in 'M'
679
680 ELSIF l_pos23 = 'CD' THEN
681 l_errbuf := 'Checking the info in Extract code';
682 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
683
684 IF p_status ='Warning' THEN
685 SELECT count(1) into dummy from fv_ccr_file_temp;
686 IF dummy >=1 THEN
687 SELECT extract_code into l_extract_type from fv_ccr_file_temp WHERE rownum=1;
688 ELSE
689 retcode := -1;
690 l_errbuf := 'Not even a single row was processed in Loader. Please verify';
691 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
692 RETURN;
693 END IF;
694 END IF;
695
696
697 -- if extract_code is 'A' for sensitive data file type then error out
698 IF l_extract_type = 'A' THEN
699 retcode := -1;
700 l_errbuf := 'Not a valid Daily Refresh file';
701 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
702
703 FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_TYPE');
704 l_msg_inv_file_type := FND_MESSAGE.GET;
705 insert_temp_data(3,null,l_msg_inv_file_type,l_message_action2,null,null,null);
706 RETURN;
707 END IF; -- end of extract type check in 'S'
708 END IF; -- end of file type validation
709 --End of change for CP test run
710 */
711
712 IF p_status ='Warning' THEN
713 SELECT count(1) into dummy from fv_ccr_bpn_clob_data;
714 IF dummy <=0 THEN
715 retcode := -1;
716 l_errbuf := 'PLEASE VERIFY DATA LOADER LOG AS ALL ROWS ARE NOT LOADED.DATA FILE HAS FEW BAD ROWS NOT LOADED.';
717 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
718 RETURN;
719 END IF;
720 END IF;
721
722
723 l_errbuf := 'Calling BPN Load package to load data into fv_ccr_file_temp';
724 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
725 FV_CCR_BPN_LOAD_PKG.MAIN();
726
727 l_errbuf := 'Push data into fv_ccr_process_gt based on the update type';
728 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
729
730 -- reset the status in fv_ccr_vendors as 'N' for extract code
731 UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
732
733 ELSE
734 --This program is called from xml import
735 -- Added the below code as Data Load will be called from xml with update_type A
736 UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
737 l_errbuf := 'The program is being called from xml import -> xml-import parameter '|| p_xml_import;
738 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
739 END IF ; --end of xml_import <> Y
740
741 l_run_from_xml := p_xml_import;
742 -- push data into fv_ccr_process_gt based on the update type
743 IF (l_update_type ='A') THEN
744
745 l_errbuf :='Update type A - Inserting into second temp table';
746 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
747 --check if atleast one record exists
748 Begin
749
750 --need to process all data if insert is Yes.
751 IF (p_xml_import ='Y' and p_insert_data ='Y') THEN
752 l_errbuf := 'Copying info - xml import';
753 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
754 INSERT INTO fv_ccr_process_gt ( FILE_DATE
755 ,DUNS
756 ,PLUS_FOUR
757 ,CAGE_CODE
758 ,EXTRACT_CODE
759 ,REGISTRATION_DATE
760 ,RENEWAL_DATE
761 ,LEGAL_BUS_NAME
762 ,DBA_NAME
763 ,DIVISION_NAME
764 ,DIVISION_NUMBER
765 ,ST_ADDRESS1
766 ,ST_ADDRESS2
767 ,CITY
768 ,STATE
769 ,POSTAL_CODE
770 ,COUNTRY
771 ,BUSINESS_START_DATE
772 ,FISCAL_YR_CLOSE_DATE
773 ,CORP_SECURITY_LEVEL
774 ,EMP_SECURITY_LEVEL
775 ,WEB_SITE
776 ,ORGANIZATIONAL_TYPE
777 ,STATE_OF_INC
778 ,COUNTRY_OF_INC
779 ,CREDIT_CARD_FLAG
780 ,CORRESPONDENCE_FLAG
781 ,MAIL_POC
782 ,MAIL_ADD1
783 ,MAIL_ADD2
784 ,MAIL_CITY
785 ,MAIL_POSTAL_CODE
786 ,MAIL_COUNTRY
787 ,MAIL_STATE
788 ,PREV_BUS_POC
789 ,PREV_BUS_ADD1
790 ,PREV_BUS_ADD2
791 ,PREV_BUS_CITY
792 ,PREV_BUS_POSTAL_CODE
793 ,PREV_BUS_COUNTRY
794 ,PREV_BUS_STATE
795 ,PARENT_POC
796 ,PARENT_DUNS
797 ,PARENT_ADD1
798 ,PARENT_ADD2
799 ,PARENT_CITY
800 ,PARENT_POSTAL_CODE
801 ,PARENT_COUNTRY
802 ,PARENT_STATE
803 ,GOV_PARENT_POC
804 ,GOV_PARENT_ADD1
805 ,GOV_PARENT_ADD2
806 ,GOV_PARENT_CITY
807 ,GOV_PARENT_POSTAL_CODE
808 ,GOV_PARENT_COUNTRY
809 ,GOV_PARENT_STATE
810 ,GOV_BUS_POC
811 ,GOV_BUS_ADD1
812 ,GOV_BUS_ADD2
813 ,GOV_BUS_CITY
814 ,GOV_BUS_POSTAL_CODE
815 ,GOV_BUS_COUNTRY
816 ,GOV_BUS_STATE
817 ,GOV_BUS_US_PHONE
818 ,GOV_BUS_US_PHONE_EX
819 ,GOV_BUS_NON_US_PHONE
820 ,GOV_BUS_FAX
821 ,GOV_BUS_EMAIL
822 ,ALT_GOV_BUS_POC
823 ,ALT_GOV_BUS_ADD1
824 ,ALT_GOV_BUS_ADD2
825 ,ALT_GOV_BUS_CITY
826 ,ALT_GOV_BUS_POSTAL_CODE
827 ,ALT_GOV_BUS_COUNTRY
828 ,ALT_GOV_BUS_STATE
829 ,ALT_GOV_BUS_US_PHONE
830 ,ALT_GOV_BUS_US_PHONE_EX
831 ,ALT_GOV_BUS_NON_US_PHONE
832 ,ALT_GOV_BUS_FAX
833 ,ALT_GOV_BUS_EMAIL
834 ,PAST_PERF_POC
835 ,PAST_PERF_ADD1
836 ,PAST_PERF_ADD2
837 ,PAST_PERF_CITY
838 ,PAST_PERF_POSTAL_CODE
839 ,PAST_PERF_COUNTRY
840 ,PAST_PERF_STATE
841 ,PAST_PERF_US_PHONE
842 ,PAST_PERF_US_PHONE_EX
843 ,PAST_PERF_NON_US_PHONE
844 ,PAST_PERF_FAX
845 ,PAST_PERF_EMAIL
846 ,ALT_PAST_PERF_POC
847 ,ALT_PAST_PERF_ADD1
848 ,ALT_PAST_PERF_ADD2
849 ,ALT_PAST_PERF_CITY
850 ,ALT_PAST_PERF_POSTAL_CODE
851 ,ALT_PAST_PERF_COUNTRY
852 ,ALT_PAST_PERF_STATE
853 ,ALT_PAST_PERF_US_PHONE
854 ,ALT_PAST_PERF_US_PHONE_EX
855 ,ALT_PAST_PERF_NON_US_PHONE
856 ,ALT_PAST_PERF_FAX
857 ,ALT_PAST_PERF_EMAIL
858 ,ELEC_BUS_POC
859 ,ELEC_BUS_ADD1
860 ,ELEC_BUS_ADD2
861 ,ELEC_BUS_CITY
862 ,ELEC_BUS_POSTAL_CODE
863 ,ELEC_BUS_COUNTRY
864 ,ELEC_BUS_STATE
865 ,ELEC_BUS_US_PHONE
866 ,ELEC_BUS_US_PHONE_EX
867 ,ELEC_BUS_NON_US_PHONE
868 ,ELEC_BUS_FAX
869 ,ELEC_BUS_EMAIL
870 ,ALT_ELEC_BUS_POC
871 ,ALT_ELEC_BUS_ADD1
872 ,ALT_ELEC_BUS_ADD2
873 ,ALT_ELEC_BUS_CITY
874 ,ALT_ELEC_BUS_POSTAL_CODE
875 ,ALT_ELEC_BUS_COUNTRY
876 ,ALT_ELEC_BUS_STATE
877 ,ALT_ELEC_BUS_US_PHONE
878 ,ALT_ELEC_BUS_US_PHONE_EX
879 ,ALT_ELEC_BUS_NON_US_PHONE
880 ,ALT_ELEC_BUS_FAX
881 ,ALT_ELEC_BUS_EMAIL
882 ,CERTIFIER_POC
883 ,CERTIFIER_US_PHONE
884 ,CERTIFIER_US_PHONE_EX
885 ,CERTIFIER_NON_US_PHONE
886 ,CERTIFIER_FAX
887 ,CERTIFIER_EMAIL
888 ,ALT_CERTIFIER_POC
889 ,ALT_CERTIFIER_US_PHONE
890 ,ALT_CERTIFIER_US_PHONE_EX
891 ,ALT_CERTIFIER_NON_US_PHONE
892 ,CORP_INFO_POC
893 ,CORP_INFO_US_PHONE
894 ,CORP_INFO_US_PHONE_EX
895 ,CORP_INFO_NON_US_PHONE
896 ,CORP_INFO_FAX
897 ,CORP_INFO_EMAIL
898 ,OWNER_INFO_POC
899 ,OWNER_INFO_US_PHONE
900 ,OWNER_INFO_US_PHONE_EX
901 ,OWNER_INFO_NON_US_PHONE
902 ,OWNER_INFO_FAX
903 ,OWNER_INFO_EMAIL
904 ,EDI
905 ,TAXPAYER_ID
906 ,AVG_NUM_EMPLOYEES
907 ,SOCIAL_SECURITY_NUMBER
908 ,FINANCIAL_INSTITUTE
909 ,BANK_ACCT_NUMBER
910 ,ABA_ROUTING
911 ,LOCKBOX_NUMBER
912 ,AUTHORIZATION_DATE
913 ,EFT_WAIVER
914 ,ACH_US_PHONE
915 ,ACH_NON_US_PHONE
916 ,ACH_FAX
917 ,ACH_EMAIL
918 ,REMIT_POC
919 ,REMIT_ADD1
920 ,REMIT_ADD2
921 ,REMIT_CITY
922 ,REMIT_STATE
923 ,REMIT_POSTAL_CODE
924 ,REMIT_COUNTRY
925 ,AR_POC
926 ,AR_US_PHONE
927 ,AR_US_PHONE_EX
928 ,AR_NON_US_PHONE
929 ,AR_FAX
930 ,AR_EMAIL
931 ,MPIN
932 ,HQ_PARENT_DUNS
933 ,HQ_PARENT_ADD1
934 ,HQ_PARENT_ADD2
935 ,HQ_PARENT_CITY
936 ,HQ_PARENT_STATE
937 ,HQ_PARENT_POSTAL_CODE
938 ,HQ_PARENT_COUNTRY
939 ,HQ_PARENT_PHONE
940 ,AUSTIN_TETRA_NUMBER
941 ,AUSTIN_TETRA_PARENT_NUMBER
942 ,AUSTIN_TETRA_ULTIMATE_NUMBER
943 ,AUSTIN_TETRA_PCARD_FLAG
944 ,DNB_MONITOR_LAST_UPDATED
945 ,DNB_MONITOR_STATUS
946 ,DNB_MONITOR_CORP_NAME
947 ,DNB_MONITOR_DBA
948 ,DNB_MONITOR_ST_ADD1
949 ,DNB_MONITOR_ST_ADD2
950 ,DNB_MONITOR_CITY
951 ,DNB_MONITOR_POSTAL_CODE
952 ,DNB_MONITOR_COUNTRY_CODE
953 ,DNB_MONITOR_STATE
954 ,HQ_PARENT_POC
955 ,PAYMENT_TYPE
956 ,ANNUAL_RECEIPTS
957 ,DOMESTIC_PARENT_POC
958 ,DOMESTIC_PARENT_DUNS
959 ,DOMESTIC_PARENT_ADD1
960 ,DOMESTIC_PARENT_ADD2
961 ,DOMESTIC_PARENT_CITY
962 ,DOMESTIC_PARENT_POSTAL_CODE
963 ,DOMESTIC_PARENT_COUNTRY
964 ,DOMESTIC_PARENT_STATE
965 ,DOMESTIC_PARENT_PHONE
966 ,GLOBAL_PARENT_POC
967 ,GLOBAL_PARENT_DUNS
968 ,GLOBAL_PARENT_ADD1
969 ,GLOBAL_PARENT_ADD2
970 ,GLOBAL_PARENT_CITY
971 ,GLOBAL_PARENT_POSTAL_CODE
972 ,GLOBAL_PARENT_COUNTRY
973 ,GLOBAL_PARENT_STATE
974 ,GLOBAL_PARENT_PHONE
975 ,bank_acct_type
976
977 )
978 SELECT l_file_date
979 ,fcft.DUNS
980 ,replace(fcft.PLUS_FOUR,' ',null)
981 ,fcft.CAGE_CODE
982 ,fcft.EXTRACT_CODE
983 ,fcft.REGISTRATION_DATE
984 ,fcft.RENEWAL_DATE
985 ,fcft.LEGAL_BUS_NAME
986 ,fcft.DBA_NAME
987 ,fcft.DIVISION_NAME
988 ,fcft.DIVISION_NUMBER
989 ,fcft.ST_ADDRESS1
990 ,fcft.ST_ADDRESS2
991 ,fcft.CITY
992 ,fcft.STATE
993 ,fcft.POSTAL_CODE
994 ,fcft.COUNTRY
995 ,fcft.BUSINESS_START_DATE
996 ,fcft.FISCAL_YR_CLOSE_DATE
997 ,fcft.CORP_SECURITY_LEVEL
998 ,fcft.EMP_SECURITY_LEVEL
999 ,fcft.WEB_SITE
1000 ,fcft.ORGANIZATIONAL_TYPE
1001 ,fcft.STATE_OF_INC
1002 ,fcft.COUNTRY_OF_INC
1003 ,fcft.CREDIT_CARD_FLAG
1004 ,fcft.CORRESPONDENCE_FLAG
1005 ,fcft.MAIL_POC
1006 ,fcft.MAIL_ADD1
1007 ,fcft.MAIL_ADD2
1008 ,fcft.MAIL_CITY
1009 ,fcft.MAIL_POSTAL_CODE
1010 ,fcft.MAIL_COUNTRY
1011 ,fcft.MAIL_STATE
1012 ,fcft.PREV_BUS_POC
1013 ,fcft.PREV_BUS_ADD1
1014 ,fcft.PREV_BUS_ADD2
1015 ,fcft.PREV_BUS_CITY
1016 ,fcft.PREV_BUS_POSTAL_CODE
1017 ,fcft.PREV_BUS_COUNTRY
1018 ,fcft.PREV_BUS_STATE
1019 ,fcft.PARENT_POC
1020 ,fcft.PARENT_DUNS
1021 ,fcft.PARENT_ADD1
1022 ,fcft.PARENT_ADD2
1023 ,fcft.PARENT_CITY
1024 ,fcft.PARENT_POSTAL_CODE
1025 ,fcft.PARENT_COUNTRY
1026 ,fcft.PARENT_STATE
1027 ,fcft.GOV_PARENT_POC
1028 ,fcft.GOV_PARENT_ADD1
1029 ,fcft.GOV_PARENT_ADD2
1030 ,fcft.GOV_PARENT_CITY
1031 ,fcft.GOV_PARENT_POSTAL_CODE
1032 ,fcft.GOV_PARENT_COUNTRY
1033 ,fcft.GOV_PARENT_STATE
1034 ,fcft.GOV_BUS_POC
1035 ,fcft.GOV_BUS_ADD1
1036 ,fcft.GOV_BUS_ADD2
1037 ,fcft.GOV_BUS_CITY
1038 ,fcft.GOV_BUS_POSTAL_CODE
1039 ,fcft.GOV_BUS_COUNTRY
1040 ,fcft.GOV_BUS_STATE
1041 ,fcft.GOV_BUS_US_PHONE
1042 ,fcft.GOV_BUS_US_PHONE_EX
1043 ,fcft.GOV_BUS_NON_US_PHONE
1044 ,fcft.GOV_BUS_FAX
1045 ,fcft.GOV_BUS_EMAIL
1046 ,fcft.ALT_GOV_BUS_POC
1047 ,fcft.ALT_GOV_BUS_ADD1
1048 ,fcft.ALT_GOV_BUS_ADD2
1049 ,fcft.ALT_GOV_BUS_CITY
1050 ,fcft.ALT_GOV_BUS_POSTAL_CODE
1051 ,fcft.ALT_GOV_BUS_COUNTRY
1052 ,fcft.ALT_GOV_BUS_STATE
1053 ,fcft.ALT_GOV_BUS_US_PHONE
1054 ,fcft.ALT_GOV_BUS_US_PHONE_EX
1055 ,fcft.ALT_GOV_BUS_NON_US_PHONE
1056 ,fcft.ALT_GOV_BUS_FAX
1057 ,fcft.ALT_GOV_BUS_EMAIL
1058 ,fcft.PAST_PERF_POC
1059 ,fcft.PAST_PERF_ADD1
1060 ,fcft.PAST_PERF_ADD2
1061 ,fcft.PAST_PERF_CITY
1062 ,fcft.PAST_PERF_POSTAL_CODE
1063 ,fcft.PAST_PERF_COUNTRY
1064 ,fcft.PAST_PERF_STATE
1065 ,fcft.PAST_PERF_US_PHONE
1066 ,fcft.PAST_PERF_US_PHONE_EX
1067 ,fcft.PAST_PERF_NON_US_PHONE
1068 ,fcft.PAST_PERF_FAX
1069 ,fcft.PAST_PERF_EMAIL
1070 ,fcft.ALT_PAST_PERF_POC
1071 ,fcft.ALT_PAST_PERF_ADD1
1072 ,fcft.ALT_PAST_PERF_ADD2
1073 ,fcft.ALT_PAST_PERF_CITY
1074 ,fcft.ALT_PAST_PERF_POSTAL_CODE
1075 ,fcft.ALT_PAST_PERF_COUNTRY
1076 ,fcft.ALT_PAST_PERF_STATE
1077 ,fcft.ALT_PAST_PERF_US_PHONE
1078 ,fcft.ALT_PAST_PERF_US_PHONE_EX
1079 ,fcft.ALT_PAST_PERF_NON_US_PHONE
1080 ,fcft.ALT_PAST_PERF_FAX
1081 ,fcft.ALT_PAST_PERF_EMAIL
1082 ,fcft.ELEC_BUS_POC
1083 ,fcft.ELEC_BUS_ADD1
1084 ,fcft.ELEC_BUS_ADD2
1085 ,fcft.ELEC_BUS_CITY
1086 ,fcft.ELEC_BUS_POSTAL_CODE
1087 ,fcft.ELEC_BUS_COUNTRY
1088 ,fcft.ELEC_BUS_STATE
1089 ,fcft.ELEC_BUS_US_PHONE
1090 ,fcft.ELEC_BUS_US_PHONE_EX
1091 ,fcft.ELEC_BUS_NON_US_PHONE
1092 ,fcft.ELEC_BUS_FAX
1093 ,fcft.ELEC_BUS_EMAIL
1094 ,fcft.ALT_ELEC_BUS_POC
1095 ,fcft.ALT_ELEC_BUS_ADD1
1096 ,fcft.ALT_ELEC_BUS_ADD2
1097 ,fcft.ALT_ELEC_BUS_CITY
1098 ,fcft.ALT_ELEC_BUS_POSTAL_CODE
1099 ,fcft.ALT_ELEC_BUS_COUNTRY
1100 ,fcft.ALT_ELEC_BUS_STATE
1101 ,fcft.ALT_ELEC_BUS_US_PHONE
1102 ,fcft.ALT_ELEC_BUS_US_PHONE_EX
1103 ,fcft.ALT_ELEC_BUS_NON_US_PHONE
1104 ,fcft.ALT_ELEC_BUS_FAX
1105 ,fcft.ALT_ELEC_BUS_EMAIL
1106 ,fcft.CERTIFIER_POC
1107 ,fcft.CERTIFIER_US_PHONE
1108 ,fcft.CERTIFIER_US_PHONE_EX
1109 ,fcft.CERTIFIER_NON_US_PHONE
1110 ,fcft.CERTIFIER_FAX
1111 ,fcft.CERTIFIER_EMAIL
1112 ,fcft.ALT_CERTIFIER_POC
1113 ,fcft.ALT_CERTIFIER_US_PHONE
1114 ,fcft.ALT_CERTIFIER_US_PHONE_EX
1115 ,fcft.ALT_CERTIFIER_NON_US_PHONE
1116 ,fcft.CORP_INFO_POC
1117 ,fcft.CORP_INFO_US_PHONE
1118 ,fcft.CORP_INFO_US_PHONE_EX
1119 ,fcft.CORP_INFO_NON_US_PHONE
1120 ,fcft.CORP_INFO_FAX
1121 ,fcft.CORP_INFO_EMAIL
1122 ,fcft.OWNER_INFO_POC
1123 ,fcft.OWNER_INFO_US_PHONE
1124 ,fcft.OWNER_INFO_US_PHONE_EX
1125 ,fcft.OWNER_INFO_NON_US_PHONE
1126 ,fcft.OWNER_INFO_FAX
1127 ,fcft.OWNER_INFO_EMAIL
1128 ,fcft.EDI
1129 ,fcft.TAXPAYER_ID
1130 ,fcft.AVG_NUM_EMPLOYEES
1131 ,fcft.SOCIAL_SECURITY_NUMBER
1132 ,fcft.FINANCIAL_INSTITUTE
1133 ,fcft.BANK_ACCT_NUMBER
1134 ,fcft.ABA_ROUTING
1135 ,fcft.LOCKBOX_NUMBER
1136 ,fcft.AUTHORIZATION_DATE
1137 ,fcft.EFT_WAIVER
1138 ,fcft.ACH_US_PHONE
1139 ,fcft.ACH_NON_US_PHONE
1140 ,fcft.ACH_FAX
1141 ,fcft.ACH_EMAIL
1142 ,fcft.REMIT_POC
1143 ,fcft.REMIT_ADD1
1144 ,fcft.REMIT_ADD2
1145 ,fcft.REMIT_CITY
1146 ,fcft.REMIT_STATE
1147 ,fcft.REMIT_POSTAL_CODE
1148 ,fcft.REMIT_COUNTRY
1149 ,fcft.AR_POC
1150 ,fcft.AR_US_PHONE
1151 ,fcft.AR_US_PHONE_EX
1152 ,fcft.AR_NON_US_PHONE
1153 ,fcft.AR_FAX
1154 ,fcft.AR_EMAIL
1155 ,fcft.MPIN
1156 ,fcft.HQ_PARENT_DUNS
1157 ,fcft.HQ_PARENT_ADD1
1158 ,fcft.HQ_PARENT_ADD2
1159 ,fcft.HQ_PARENT_CITY
1160 ,fcft.HQ_PARENT_STATE
1161 ,fcft.HQ_PARENT_POSTAL_CODE
1162 ,fcft.HQ_PARENT_COUNTRY
1163 ,fcft.HQ_PARENT_PHONE
1164 ,fcft.AUSTIN_TETRA_NUMBER
1165 ,fcft.AUSTIN_TETRA_PARENT_NUMBER
1166 ,fcft.AUSTIN_TETRA_ULTIMATE_NUMBER
1167 ,fcft.AUSTIN_TETRA_PCARD_FLAG
1168 ,fcft.DNB_MONITOR_LAST_UPDATED
1169 ,fcft.DNB_MONITOR_STATUS
1170 ,fcft.DNB_MONITOR_CORP_NAME
1171 ,fcft.DNB_MONITOR_DBA
1172 ,fcft.DNB_MONITOR_ST_ADD1
1173 ,fcft.DNB_MONITOR_ST_ADD2
1174 ,fcft.DNB_MONITOR_CITY
1175 ,fcft.DNB_MONITOR_POSTAL_CODE
1176 ,fcft.DNB_MONITOR_COUNTRY_CODE
1177 ,fcft.DNB_MONITOR_STATE
1178 ,fcft.HQ_PARENT_POC
1179 ,fcft.PAYMENT_TYPE
1180 ,fcft.ANNUAL_RECEIPTS
1181 ,fcft.DOMESTIC_PARENT_POC
1182 ,fcft.DOMESTIC_PARENT_DUNS
1183 ,fcft.DOMESTIC_PARENT_ADD1
1184 ,fcft.DOMESTIC_PARENT_ADD2
1185 ,fcft.DOMESTIC_PARENT_CITY
1186 ,fcft.DOMESTIC_PARENT_POSTAL_CODE
1187 ,fcft.DOMESTIC_PARENT_COUNTRY
1188 ,fcft.DOMESTIC_PARENT_STATE
1189 ,fcft.DOMESTIC_PARENT_PHONE
1190 ,fcft.GLOBAL_PARENT_POC
1191 ,fcft.GLOBAL_PARENT_DUNS
1192 ,fcft.GLOBAL_PARENT_ADD1
1193 ,fcft.GLOBAL_PARENT_ADD2
1194 ,fcft.GLOBAL_PARENT_CITY
1195 ,fcft.GLOBAL_PARENT_POSTAL_CODE
1196 ,fcft.GLOBAL_PARENT_COUNTRY
1197 ,fcft.GLOBAL_PARENT_STATE
1198 ,fcft.GLOBAL_PARENT_PHONE
1199 ,bank_acct_type
1200
1201 FROM fv_ccr_file_temp fcft
1202 order by rowid;
1203 ELSE
1204 l_errbuf := 'Copying info - Standalone / insert as N';
1205 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
1206
1207 INSERT INTO fv_ccr_process_gt ( FILE_DATE
1208 ,DUNS
1209 ,PLUS_FOUR
1210 ,CAGE_CODE
1211 ,EXTRACT_CODE
1212 ,REGISTRATION_DATE
1213 ,RENEWAL_DATE
1214 ,LEGAL_BUS_NAME
1215 ,DBA_NAME
1216 ,DIVISION_NAME
1217 ,DIVISION_NUMBER
1218 ,ST_ADDRESS1
1219 ,ST_ADDRESS2
1220 ,CITY
1221 ,STATE
1222 ,POSTAL_CODE
1223 ,COUNTRY
1224 ,BUSINESS_START_DATE
1225 ,FISCAL_YR_CLOSE_DATE
1226 ,CORP_SECURITY_LEVEL
1227 ,EMP_SECURITY_LEVEL
1228 ,WEB_SITE
1229 ,ORGANIZATIONAL_TYPE
1230 ,STATE_OF_INC
1231 ,COUNTRY_OF_INC
1232 ,CREDIT_CARD_FLAG
1233 ,CORRESPONDENCE_FLAG
1234 ,MAIL_POC
1235 ,MAIL_ADD1
1236 ,MAIL_ADD2
1237 ,MAIL_CITY
1238 ,MAIL_POSTAL_CODE
1239 ,MAIL_COUNTRY
1240 ,MAIL_STATE
1241 ,PREV_BUS_POC
1242 ,PREV_BUS_ADD1
1243 ,PREV_BUS_ADD2
1244 ,PREV_BUS_CITY
1245 ,PREV_BUS_POSTAL_CODE
1246 ,PREV_BUS_COUNTRY
1247 ,PREV_BUS_STATE
1248 ,PARENT_POC
1249 ,PARENT_DUNS
1250 ,PARENT_ADD1
1251 ,PARENT_ADD2
1252 ,PARENT_CITY
1253 ,PARENT_POSTAL_CODE
1254 ,PARENT_COUNTRY
1255 ,PARENT_STATE
1256 ,GOV_PARENT_POC
1257 ,GOV_PARENT_ADD1
1258 ,GOV_PARENT_ADD2
1259 ,GOV_PARENT_CITY
1260 ,GOV_PARENT_POSTAL_CODE
1261 ,GOV_PARENT_COUNTRY
1262 ,GOV_PARENT_STATE
1263 ,GOV_BUS_POC
1264 ,GOV_BUS_ADD1
1265 ,GOV_BUS_ADD2
1266 ,GOV_BUS_CITY
1267 ,GOV_BUS_POSTAL_CODE
1268 ,GOV_BUS_COUNTRY
1269 ,GOV_BUS_STATE
1270 ,GOV_BUS_US_PHONE
1271 ,GOV_BUS_US_PHONE_EX
1272 ,GOV_BUS_NON_US_PHONE
1273 ,GOV_BUS_FAX
1274 ,GOV_BUS_EMAIL
1275 ,ALT_GOV_BUS_POC
1276 ,ALT_GOV_BUS_ADD1
1277 ,ALT_GOV_BUS_ADD2
1278 ,ALT_GOV_BUS_CITY
1279 ,ALT_GOV_BUS_POSTAL_CODE
1280 ,ALT_GOV_BUS_COUNTRY
1281 ,ALT_GOV_BUS_STATE
1282 ,ALT_GOV_BUS_US_PHONE
1283 ,ALT_GOV_BUS_US_PHONE_EX
1284 ,ALT_GOV_BUS_NON_US_PHONE
1285 ,ALT_GOV_BUS_FAX
1286 ,ALT_GOV_BUS_EMAIL
1287 ,PAST_PERF_POC
1288 ,PAST_PERF_ADD1
1289 ,PAST_PERF_ADD2
1290 ,PAST_PERF_CITY
1291 ,PAST_PERF_POSTAL_CODE
1292 ,PAST_PERF_COUNTRY
1293 ,PAST_PERF_STATE
1294 ,PAST_PERF_US_PHONE
1295 ,PAST_PERF_US_PHONE_EX
1296 ,PAST_PERF_NON_US_PHONE
1297 ,PAST_PERF_FAX
1298 ,PAST_PERF_EMAIL
1299 ,ALT_PAST_PERF_POC
1300 ,ALT_PAST_PERF_ADD1
1301 ,ALT_PAST_PERF_ADD2
1302 ,ALT_PAST_PERF_CITY
1303 ,ALT_PAST_PERF_POSTAL_CODE
1304 ,ALT_PAST_PERF_COUNTRY
1305 ,ALT_PAST_PERF_STATE
1306 ,ALT_PAST_PERF_US_PHONE
1307 ,ALT_PAST_PERF_US_PHONE_EX
1308 ,ALT_PAST_PERF_NON_US_PHONE
1309 ,ALT_PAST_PERF_FAX
1310 ,ALT_PAST_PERF_EMAIL
1311 ,ELEC_BUS_POC
1312 ,ELEC_BUS_ADD1
1313 ,ELEC_BUS_ADD2
1314 ,ELEC_BUS_CITY
1315 ,ELEC_BUS_POSTAL_CODE
1316 ,ELEC_BUS_COUNTRY
1317 ,ELEC_BUS_STATE
1318 ,ELEC_BUS_US_PHONE
1319 ,ELEC_BUS_US_PHONE_EX
1320 ,ELEC_BUS_NON_US_PHONE
1321 ,ELEC_BUS_FAX
1322 ,ELEC_BUS_EMAIL
1323 ,ALT_ELEC_BUS_POC
1324 ,ALT_ELEC_BUS_ADD1
1325 ,ALT_ELEC_BUS_ADD2
1326 ,ALT_ELEC_BUS_CITY
1327 ,ALT_ELEC_BUS_POSTAL_CODE
1328 ,ALT_ELEC_BUS_COUNTRY
1329 ,ALT_ELEC_BUS_STATE
1330 ,ALT_ELEC_BUS_US_PHONE
1331 ,ALT_ELEC_BUS_US_PHONE_EX
1332 ,ALT_ELEC_BUS_NON_US_PHONE
1333 ,ALT_ELEC_BUS_FAX
1334 ,ALT_ELEC_BUS_EMAIL
1335 ,CERTIFIER_POC
1336 ,CERTIFIER_US_PHONE
1337 ,CERTIFIER_US_PHONE_EX
1338 ,CERTIFIER_NON_US_PHONE
1339 ,CERTIFIER_FAX
1340 ,CERTIFIER_EMAIL
1341 ,ALT_CERTIFIER_POC
1342 ,ALT_CERTIFIER_US_PHONE
1343 ,ALT_CERTIFIER_US_PHONE_EX
1344 ,ALT_CERTIFIER_NON_US_PHONE
1345 ,CORP_INFO_POC
1346 ,CORP_INFO_US_PHONE
1347 ,CORP_INFO_US_PHONE_EX
1348 ,CORP_INFO_NON_US_PHONE
1349 ,CORP_INFO_FAX
1350 ,CORP_INFO_EMAIL
1351 ,OWNER_INFO_POC
1352 ,OWNER_INFO_US_PHONE
1353 ,OWNER_INFO_US_PHONE_EX
1354 ,OWNER_INFO_NON_US_PHONE
1355 ,OWNER_INFO_FAX
1356 ,OWNER_INFO_EMAIL
1357 ,EDI
1358 ,TAXPAYER_ID
1359 ,AVG_NUM_EMPLOYEES
1360 ,SOCIAL_SECURITY_NUMBER
1361 ,FINANCIAL_INSTITUTE
1362 ,BANK_ACCT_NUMBER
1363 ,ABA_ROUTING
1364 ,LOCKBOX_NUMBER
1365 ,AUTHORIZATION_DATE
1366 ,EFT_WAIVER
1367 ,ACH_US_PHONE
1368 ,ACH_NON_US_PHONE
1369 ,ACH_FAX
1370 ,ACH_EMAIL
1371 ,REMIT_POC
1372 ,REMIT_ADD1
1373 ,REMIT_ADD2
1374 ,REMIT_CITY
1375 ,REMIT_STATE
1376 ,REMIT_POSTAL_CODE
1377 ,REMIT_COUNTRY
1378 ,AR_POC
1379 ,AR_US_PHONE
1380 ,AR_US_PHONE_EX
1381 ,AR_NON_US_PHONE
1382 ,AR_FAX
1383 ,AR_EMAIL
1384 ,MPIN
1385 ,HQ_PARENT_DUNS
1386 ,HQ_PARENT_ADD1
1387 ,HQ_PARENT_ADD2
1388 ,HQ_PARENT_CITY
1389 ,HQ_PARENT_STATE
1390 ,HQ_PARENT_POSTAL_CODE
1391 ,HQ_PARENT_COUNTRY
1392 ,HQ_PARENT_PHONE
1393 ,AUSTIN_TETRA_NUMBER
1394 ,AUSTIN_TETRA_PARENT_NUMBER
1395 ,AUSTIN_TETRA_ULTIMATE_NUMBER
1396 ,AUSTIN_TETRA_PCARD_FLAG
1397 ,DNB_MONITOR_LAST_UPDATED
1398 ,DNB_MONITOR_STATUS
1399 ,DNB_MONITOR_CORP_NAME
1400 ,DNB_MONITOR_DBA
1401 ,DNB_MONITOR_ST_ADD1
1402 ,DNB_MONITOR_ST_ADD2
1403 ,DNB_MONITOR_CITY
1404 ,DNB_MONITOR_POSTAL_CODE
1405 ,DNB_MONITOR_COUNTRY_CODE
1406 ,DNB_MONITOR_STATE
1407 ,HQ_PARENT_POC
1408 ,PAYMENT_TYPE
1409 ,ANNUAL_RECEIPTS
1410 ,DOMESTIC_PARENT_POC
1411 ,DOMESTIC_PARENT_DUNS
1412 ,DOMESTIC_PARENT_ADD1
1413 ,DOMESTIC_PARENT_ADD2
1414 ,DOMESTIC_PARENT_CITY
1415 ,DOMESTIC_PARENT_POSTAL_CODE
1416 ,DOMESTIC_PARENT_COUNTRY
1417 ,DOMESTIC_PARENT_STATE
1418 ,DOMESTIC_PARENT_PHONE
1419 ,GLOBAL_PARENT_POC
1420 ,GLOBAL_PARENT_DUNS
1421 ,GLOBAL_PARENT_ADD1
1422 ,GLOBAL_PARENT_ADD2
1423 ,GLOBAL_PARENT_CITY
1424 ,GLOBAL_PARENT_POSTAL_CODE
1425 ,GLOBAL_PARENT_COUNTRY
1426 ,GLOBAL_PARENT_STATE
1427 ,GLOBAL_PARENT_PHONE
1428 ,bank_acct_type
1429
1430 )
1431
1432 SELECT l_file_date
1433 ,fcft.DUNS
1434 ,replace(fcft.PLUS_FOUR,' ',null)
1435 ,fcft.CAGE_CODE
1436 ,fcft.EXTRACT_CODE
1437 ,fcft.REGISTRATION_DATE
1438 ,fcft.RENEWAL_DATE
1439 ,fcft.LEGAL_BUS_NAME
1440 ,fcft.DBA_NAME
1441 ,fcft.DIVISION_NAME
1442 ,fcft.DIVISION_NUMBER
1443 ,fcft.ST_ADDRESS1
1444 ,fcft.ST_ADDRESS2
1445 ,fcft.CITY
1446 ,fcft.STATE
1447 ,fcft.POSTAL_CODE
1448 ,fcft.COUNTRY
1449 ,fcft.BUSINESS_START_DATE
1450 ,fcft.FISCAL_YR_CLOSE_DATE
1451 ,fcft.CORP_SECURITY_LEVEL
1452 ,fcft.EMP_SECURITY_LEVEL
1453 ,fcft.WEB_SITE
1454 ,fcft.ORGANIZATIONAL_TYPE
1455 ,fcft.STATE_OF_INC
1456 ,fcft.COUNTRY_OF_INC
1457 ,fcft.CREDIT_CARD_FLAG
1458 ,fcft.CORRESPONDENCE_FLAG
1459 ,fcft.MAIL_POC
1460 ,fcft.MAIL_ADD1
1461 ,fcft.MAIL_ADD2
1462 ,fcft.MAIL_CITY
1463 ,fcft.MAIL_POSTAL_CODE
1464 ,fcft.MAIL_COUNTRY
1465 ,fcft.MAIL_STATE
1466 ,fcft.PREV_BUS_POC
1467 ,fcft.PREV_BUS_ADD1
1468 ,fcft.PREV_BUS_ADD2
1469 ,fcft.PREV_BUS_CITY
1470 ,fcft.PREV_BUS_POSTAL_CODE
1471 ,fcft.PREV_BUS_COUNTRY
1472 ,fcft.PREV_BUS_STATE
1473 ,fcft.PARENT_POC
1474 ,fcft.PARENT_DUNS
1475 ,fcft.PARENT_ADD1
1476 ,fcft.PARENT_ADD2
1477 ,fcft.PARENT_CITY
1478 ,fcft.PARENT_POSTAL_CODE
1479 ,fcft.PARENT_COUNTRY
1480 ,fcft.PARENT_STATE
1481 ,fcft.GOV_PARENT_POC
1482 ,fcft.GOV_PARENT_ADD1
1483 ,fcft.GOV_PARENT_ADD2
1484 ,fcft.GOV_PARENT_CITY
1485 ,fcft.GOV_PARENT_POSTAL_CODE
1486 ,fcft.GOV_PARENT_COUNTRY
1487 ,fcft.GOV_PARENT_STATE
1488 ,fcft.GOV_BUS_POC
1489 ,fcft.GOV_BUS_ADD1
1490 ,fcft.GOV_BUS_ADD2
1491 ,fcft.GOV_BUS_CITY
1492 ,fcft.GOV_BUS_POSTAL_CODE
1493 ,fcft.GOV_BUS_COUNTRY
1494 ,fcft.GOV_BUS_STATE
1495 ,fcft.GOV_BUS_US_PHONE
1496 ,fcft.GOV_BUS_US_PHONE_EX
1497 ,fcft.GOV_BUS_NON_US_PHONE
1498 ,fcft.GOV_BUS_FAX
1499 ,fcft.GOV_BUS_EMAIL
1500 ,fcft.ALT_GOV_BUS_POC
1501 ,fcft.ALT_GOV_BUS_ADD1
1502 ,fcft.ALT_GOV_BUS_ADD2
1503 ,fcft.ALT_GOV_BUS_CITY
1504 ,fcft.ALT_GOV_BUS_POSTAL_CODE
1505 ,fcft.ALT_GOV_BUS_COUNTRY
1506 ,fcft.ALT_GOV_BUS_STATE
1507 ,fcft.ALT_GOV_BUS_US_PHONE
1508 ,fcft.ALT_GOV_BUS_US_PHONE_EX
1509 ,fcft.ALT_GOV_BUS_NON_US_PHONE
1510 ,fcft.ALT_GOV_BUS_FAX
1511 ,fcft.ALT_GOV_BUS_EMAIL
1512 ,fcft.PAST_PERF_POC
1513 ,fcft.PAST_PERF_ADD1
1514 ,fcft.PAST_PERF_ADD2
1515 ,fcft.PAST_PERF_CITY
1516 ,fcft.PAST_PERF_POSTAL_CODE
1517 ,fcft.PAST_PERF_COUNTRY
1518 ,fcft.PAST_PERF_STATE
1519 ,fcft.PAST_PERF_US_PHONE
1520 ,fcft.PAST_PERF_US_PHONE_EX
1521 ,fcft.PAST_PERF_NON_US_PHONE
1522 ,fcft.PAST_PERF_FAX
1523 ,fcft.PAST_PERF_EMAIL
1524 ,fcft.ALT_PAST_PERF_POC
1525 ,fcft.ALT_PAST_PERF_ADD1
1526 ,fcft.ALT_PAST_PERF_ADD2
1527 ,fcft.ALT_PAST_PERF_CITY
1528 ,fcft.ALT_PAST_PERF_POSTAL_CODE
1529 ,fcft.ALT_PAST_PERF_COUNTRY
1530 ,fcft.ALT_PAST_PERF_STATE
1531 ,fcft.ALT_PAST_PERF_US_PHONE
1532 ,fcft.ALT_PAST_PERF_US_PHONE_EX
1533 ,fcft.ALT_PAST_PERF_NON_US_PHONE
1534 ,fcft.ALT_PAST_PERF_FAX
1535 ,fcft.ALT_PAST_PERF_EMAIL
1536 ,fcft.ELEC_BUS_POC
1537 ,fcft.ELEC_BUS_ADD1
1538 ,fcft.ELEC_BUS_ADD2
1539 ,fcft.ELEC_BUS_CITY
1540 ,fcft.ELEC_BUS_POSTAL_CODE
1541 ,fcft.ELEC_BUS_COUNTRY
1542 ,fcft.ELEC_BUS_STATE
1543 ,fcft.ELEC_BUS_US_PHONE
1544 ,fcft.ELEC_BUS_US_PHONE_EX
1545 ,fcft.ELEC_BUS_NON_US_PHONE
1546 ,fcft.ELEC_BUS_FAX
1547 ,fcft.ELEC_BUS_EMAIL
1548 ,fcft.ALT_ELEC_BUS_POC
1549 ,fcft.ALT_ELEC_BUS_ADD1
1550 ,fcft.ALT_ELEC_BUS_ADD2
1551 ,fcft.ALT_ELEC_BUS_CITY
1552 ,fcft.ALT_ELEC_BUS_POSTAL_CODE
1553 ,fcft.ALT_ELEC_BUS_COUNTRY
1554 ,fcft.ALT_ELEC_BUS_STATE
1555 ,fcft.ALT_ELEC_BUS_US_PHONE
1556 ,fcft.ALT_ELEC_BUS_US_PHONE_EX
1557 ,fcft.ALT_ELEC_BUS_NON_US_PHONE
1558 ,fcft.ALT_ELEC_BUS_FAX
1559 ,fcft.ALT_ELEC_BUS_EMAIL
1560 ,fcft.CERTIFIER_POC
1561 ,fcft.CERTIFIER_US_PHONE
1562 ,fcft.CERTIFIER_US_PHONE_EX
1563 ,fcft.CERTIFIER_NON_US_PHONE
1564 ,fcft.CERTIFIER_FAX
1565 ,fcft.CERTIFIER_EMAIL
1566 ,fcft.ALT_CERTIFIER_POC
1567 ,fcft.ALT_CERTIFIER_US_PHONE
1568 ,fcft.ALT_CERTIFIER_US_PHONE_EX
1569 ,fcft.ALT_CERTIFIER_NON_US_PHONE
1570 ,fcft.CORP_INFO_POC
1571 ,fcft.CORP_INFO_US_PHONE
1572 ,fcft.CORP_INFO_US_PHONE_EX
1573 ,fcft.CORP_INFO_NON_US_PHONE
1574 ,fcft.CORP_INFO_FAX
1575 ,fcft.CORP_INFO_EMAIL
1576 ,fcft.OWNER_INFO_POC
1577 ,fcft.OWNER_INFO_US_PHONE
1578 ,fcft.OWNER_INFO_US_PHONE_EX
1579 ,fcft.OWNER_INFO_NON_US_PHONE
1580 ,fcft.OWNER_INFO_FAX
1581 ,fcft.OWNER_INFO_EMAIL
1582 ,fcft.EDI
1583 ,fcft.TAXPAYER_ID
1584 ,fcft.AVG_NUM_EMPLOYEES
1585 ,fcft.SOCIAL_SECURITY_NUMBER
1586 ,fcft.FINANCIAL_INSTITUTE
1587 ,fcft.BANK_ACCT_NUMBER
1588 ,fcft.ABA_ROUTING
1589 ,fcft.LOCKBOX_NUMBER
1590 ,fcft.AUTHORIZATION_DATE
1591 ,fcft.EFT_WAIVER
1592 ,fcft.ACH_US_PHONE
1593 ,fcft.ACH_NON_US_PHONE
1594 ,fcft.ACH_FAX
1595 ,fcft.ACH_EMAIL
1596 ,fcft.REMIT_POC
1597 ,fcft.REMIT_ADD1
1598 ,fcft.REMIT_ADD2
1599 ,fcft.REMIT_CITY
1600 ,fcft.REMIT_STATE
1601 ,fcft.REMIT_POSTAL_CODE
1602 ,fcft.REMIT_COUNTRY
1603 ,fcft.AR_POC
1604 ,fcft.AR_US_PHONE
1605 ,fcft.AR_US_PHONE_EX
1606 ,fcft.AR_NON_US_PHONE
1607 ,fcft.AR_FAX
1608 ,fcft.AR_EMAIL
1609 ,fcft.MPIN
1610 ,fcft.HQ_PARENT_DUNS
1611 ,fcft.HQ_PARENT_ADD1
1612 ,fcft.HQ_PARENT_ADD2
1613 ,fcft.HQ_PARENT_CITY
1614 ,fcft.HQ_PARENT_STATE
1615 ,fcft.HQ_PARENT_POSTAL_CODE
1616 ,fcft.HQ_PARENT_COUNTRY
1617 ,fcft.HQ_PARENT_PHONE
1618 ,fcft.AUSTIN_TETRA_NUMBER
1619 ,fcft.AUSTIN_TETRA_PARENT_NUMBER
1620 ,fcft.AUSTIN_TETRA_ULTIMATE_NUMBER
1621 ,fcft.AUSTIN_TETRA_PCARD_FLAG
1622 ,fcft.DNB_MONITOR_LAST_UPDATED
1623 ,fcft.DNB_MONITOR_STATUS
1624 ,fcft.DNB_MONITOR_CORP_NAME
1625 ,fcft.DNB_MONITOR_DBA
1626 ,fcft.DNB_MONITOR_ST_ADD1
1627 ,fcft.DNB_MONITOR_ST_ADD2
1628 ,fcft.DNB_MONITOR_CITY
1629 ,fcft.DNB_MONITOR_POSTAL_CODE
1630 ,fcft.DNB_MONITOR_COUNTRY_CODE
1631 ,fcft.DNB_MONITOR_STATE
1632 ,fcft.HQ_PARENT_POC
1633 ,fcft.PAYMENT_TYPE
1634 ,fcft.ANNUAL_RECEIPTS
1635 ,fcft.DOMESTIC_PARENT_POC
1636 ,fcft.DOMESTIC_PARENT_DUNS
1637 ,fcft.DOMESTIC_PARENT_ADD1
1638 ,fcft.DOMESTIC_PARENT_ADD2
1639 ,fcft.DOMESTIC_PARENT_CITY
1640 ,fcft.DOMESTIC_PARENT_POSTAL_CODE
1641 ,fcft.DOMESTIC_PARENT_COUNTRY
1642 ,fcft.DOMESTIC_PARENT_STATE
1643 ,fcft.DOMESTIC_PARENT_PHONE
1644 ,fcft.GLOBAL_PARENT_POC
1645 ,fcft.GLOBAL_PARENT_DUNS
1646 ,fcft.GLOBAL_PARENT_ADD1
1647 ,fcft.GLOBAL_PARENT_ADD2
1648 ,fcft.GLOBAL_PARENT_CITY
1649 ,fcft.GLOBAL_PARENT_POSTAL_CODE
1650 ,fcft.GLOBAL_PARENT_COUNTRY
1651 ,fcft.GLOBAL_PARENT_STATE
1652 ,fcft.GLOBAL_PARENT_PHONE
1653 ,bank_acct_type
1654
1655 FROM fv_ccr_file_temp fcft
1656 WHERE exists ( select 1 from fv_ccr_vendors fcv
1657 where fcft.duns = fcv.duns)
1658 order by rowid;
1659
1660 END IF;
1661
1662 exception when no_data_found then
1663 l_errbuf :='No records found for DUNS in FV Extension Tables';
1664 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
1665 insert_temp_data(3,null,l_msg_no_duns,l_message_action3,null,null,null);
1666 end;
1667 ELSIF (l_update_type ='N') THEN
1668 l_errbuf :='Update Type as N - inserting into second temp table';
1669 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
1670
1671 INSERT INTO fv_ccr_process_gt ( FILE_DATE
1672 ,DUNS
1673 ,PLUS_FOUR
1674 ,CAGE_CODE
1675 ,EXTRACT_CODE
1676 ,REGISTRATION_DATE
1677 ,RENEWAL_DATE
1678 ,LEGAL_BUS_NAME
1679 ,DBA_NAME
1680 ,DIVISION_NAME
1681 ,DIVISION_NUMBER
1682 ,ST_ADDRESS1
1683 ,ST_ADDRESS2
1684 ,CITY
1685 ,STATE
1686 ,POSTAL_CODE
1687 ,COUNTRY
1688 ,BUSINESS_START_DATE
1689 ,FISCAL_YR_CLOSE_DATE
1690 ,CORP_SECURITY_LEVEL
1691 ,EMP_SECURITY_LEVEL
1692 ,WEB_SITE
1693 ,ORGANIZATIONAL_TYPE
1694 ,STATE_OF_INC
1695 ,COUNTRY_OF_INC
1696 ,CREDIT_CARD_FLAG
1697 ,CORRESPONDENCE_FLAG
1698 ,MAIL_POC
1699 ,MAIL_ADD1
1700 ,MAIL_ADD2
1701 ,MAIL_CITY
1702 ,MAIL_POSTAL_CODE
1703 ,MAIL_COUNTRY
1704 ,MAIL_STATE
1705 ,PREV_BUS_POC
1706 ,PREV_BUS_ADD1
1707 ,PREV_BUS_ADD2
1708 ,PREV_BUS_CITY
1709 ,PREV_BUS_POSTAL_CODE
1710 ,PREV_BUS_COUNTRY
1711 ,PREV_BUS_STATE
1712 ,PARENT_POC
1713 ,PARENT_DUNS
1714 ,PARENT_ADD1
1715 ,PARENT_ADD2
1716 ,PARENT_CITY
1717 ,PARENT_POSTAL_CODE
1718 ,PARENT_COUNTRY
1719 ,PARENT_STATE
1720 ,GOV_PARENT_POC
1721 ,GOV_PARENT_ADD1
1722 ,GOV_PARENT_ADD2
1723 ,GOV_PARENT_CITY
1724 ,GOV_PARENT_POSTAL_CODE
1725 ,GOV_PARENT_COUNTRY
1726 ,GOV_PARENT_STATE
1727 ,GOV_BUS_POC
1728 ,GOV_BUS_ADD1
1729 ,GOV_BUS_ADD2
1730 ,GOV_BUS_CITY
1731 ,GOV_BUS_POSTAL_CODE
1732 ,GOV_BUS_COUNTRY
1733 ,GOV_BUS_STATE
1734 ,GOV_BUS_US_PHONE
1735 ,GOV_BUS_US_PHONE_EX
1736 ,GOV_BUS_NON_US_PHONE
1737 ,GOV_BUS_FAX
1738 ,GOV_BUS_EMAIL
1739 ,ALT_GOV_BUS_POC
1740 ,ALT_GOV_BUS_ADD1
1741 ,ALT_GOV_BUS_ADD2
1742 ,ALT_GOV_BUS_CITY
1743 ,ALT_GOV_BUS_POSTAL_CODE
1744 ,ALT_GOV_BUS_COUNTRY
1745 ,ALT_GOV_BUS_STATE
1746 ,ALT_GOV_BUS_US_PHONE
1747 ,ALT_GOV_BUS_US_PHONE_EX
1748 ,ALT_GOV_BUS_NON_US_PHONE
1749 ,ALT_GOV_BUS_FAX
1750 ,ALT_GOV_BUS_EMAIL
1751 ,PAST_PERF_POC
1752 ,PAST_PERF_ADD1
1753 ,PAST_PERF_ADD2
1754 ,PAST_PERF_CITY
1755 ,PAST_PERF_POSTAL_CODE
1756 ,PAST_PERF_COUNTRY
1757 ,PAST_PERF_STATE
1758 ,PAST_PERF_US_PHONE
1759 ,PAST_PERF_US_PHONE_EX
1760 ,PAST_PERF_NON_US_PHONE
1761 ,PAST_PERF_FAX
1762 ,PAST_PERF_EMAIL
1763 ,ALT_PAST_PERF_POC
1764 ,ALT_PAST_PERF_ADD1
1765 ,ALT_PAST_PERF_ADD2
1766 ,ALT_PAST_PERF_CITY
1767 ,ALT_PAST_PERF_POSTAL_CODE
1768 ,ALT_PAST_PERF_COUNTRY
1769 ,ALT_PAST_PERF_STATE
1770 ,ALT_PAST_PERF_US_PHONE
1771 ,ALT_PAST_PERF_US_PHONE_EX
1772 ,ALT_PAST_PERF_NON_US_PHONE
1773 ,ALT_PAST_PERF_FAX
1774 ,ALT_PAST_PERF_EMAIL
1775 ,ELEC_BUS_POC
1776 ,ELEC_BUS_ADD1
1777 ,ELEC_BUS_ADD2
1778 ,ELEC_BUS_CITY
1779 ,ELEC_BUS_POSTAL_CODE
1780 ,ELEC_BUS_COUNTRY
1781 ,ELEC_BUS_STATE
1782 ,ELEC_BUS_US_PHONE
1783 ,ELEC_BUS_US_PHONE_EX
1784 ,ELEC_BUS_NON_US_PHONE
1785 ,ELEC_BUS_FAX
1786 ,ELEC_BUS_EMAIL
1787 ,ALT_ELEC_BUS_POC
1788 ,ALT_ELEC_BUS_ADD1
1789 ,ALT_ELEC_BUS_ADD2
1790 ,ALT_ELEC_BUS_CITY
1791 ,ALT_ELEC_BUS_POSTAL_CODE
1792 ,ALT_ELEC_BUS_COUNTRY
1793 ,ALT_ELEC_BUS_STATE
1794 ,ALT_ELEC_BUS_US_PHONE
1795 ,ALT_ELEC_BUS_US_PHONE_EX
1796 ,ALT_ELEC_BUS_NON_US_PHONE
1797 ,ALT_ELEC_BUS_FAX
1798 ,ALT_ELEC_BUS_EMAIL
1799 ,CERTIFIER_POC
1800 ,CERTIFIER_US_PHONE
1801 ,CERTIFIER_US_PHONE_EX
1802 ,CERTIFIER_NON_US_PHONE
1803 ,CERTIFIER_FAX
1804 ,CERTIFIER_EMAIL
1805 ,ALT_CERTIFIER_POC
1806 ,ALT_CERTIFIER_US_PHONE
1807 ,ALT_CERTIFIER_US_PHONE_EX
1808 ,ALT_CERTIFIER_NON_US_PHONE
1809 ,CORP_INFO_POC
1810 ,CORP_INFO_US_PHONE
1811 ,CORP_INFO_US_PHONE_EX
1812 ,CORP_INFO_NON_US_PHONE
1813 ,CORP_INFO_FAX
1814 ,CORP_INFO_EMAIL
1815 ,OWNER_INFO_POC
1816 ,OWNER_INFO_US_PHONE
1817 ,OWNER_INFO_US_PHONE_EX
1818 ,OWNER_INFO_NON_US_PHONE
1819 ,OWNER_INFO_FAX
1820 ,OWNER_INFO_EMAIL
1821 ,EDI
1822 ,TAXPAYER_ID
1823 ,AVG_NUM_EMPLOYEES
1824 ,SOCIAL_SECURITY_NUMBER
1825 ,FINANCIAL_INSTITUTE
1826 ,BANK_ACCT_NUMBER
1827 ,ABA_ROUTING
1828 ,LOCKBOX_NUMBER
1829 ,AUTHORIZATION_DATE
1830 ,EFT_WAIVER
1831 ,ACH_US_PHONE
1832 ,ACH_NON_US_PHONE
1833 ,ACH_FAX
1834 ,ACH_EMAIL
1835 ,REMIT_POC
1836 ,REMIT_ADD1
1837 ,REMIT_ADD2
1838 ,REMIT_CITY
1839 ,REMIT_STATE
1840 ,REMIT_POSTAL_CODE
1841 ,REMIT_COUNTRY
1842 ,AR_POC
1843 ,AR_US_PHONE
1844 ,AR_US_PHONE_EX
1845 ,AR_NON_US_PHONE
1846 ,AR_FAX
1847 ,AR_EMAIL
1848 ,MPIN
1849 ,HQ_PARENT_DUNS
1850 ,HQ_PARENT_ADD1
1851 ,HQ_PARENT_ADD2
1852 ,HQ_PARENT_CITY
1853 ,HQ_PARENT_STATE
1854 ,HQ_PARENT_POSTAL_CODE
1855 ,HQ_PARENT_COUNTRY
1856 ,HQ_PARENT_PHONE
1857 ,AUSTIN_TETRA_NUMBER
1858 ,AUSTIN_TETRA_PARENT_NUMBER
1859 ,AUSTIN_TETRA_ULTIMATE_NUMBER
1860 ,AUSTIN_TETRA_PCARD_FLAG
1861 ,DNB_MONITOR_LAST_UPDATED
1862 ,DNB_MONITOR_STATUS
1863 ,DNB_MONITOR_CORP_NAME
1864 ,DNB_MONITOR_DBA
1865 ,DNB_MONITOR_ST_ADD1
1866 ,DNB_MONITOR_ST_ADD2
1867 ,DNB_MONITOR_CITY
1868 ,DNB_MONITOR_POSTAL_CODE
1869 ,DNB_MONITOR_COUNTRY_CODE
1870 ,DNB_MONITOR_STATE
1871 ,HQ_PARENT_POC
1872 ,PAYMENT_TYPE
1873 ,ANNUAL_RECEIPTS
1874 ,DOMESTIC_PARENT_POC
1875 ,DOMESTIC_PARENT_DUNS
1876 ,DOMESTIC_PARENT_ADD1
1877 ,DOMESTIC_PARENT_ADD2
1878 ,DOMESTIC_PARENT_CITY
1879 ,DOMESTIC_PARENT_POSTAL_CODE
1880 ,DOMESTIC_PARENT_COUNTRY
1881 ,DOMESTIC_PARENT_STATE
1882 ,DOMESTIC_PARENT_PHONE
1883 ,GLOBAL_PARENT_POC
1884 ,GLOBAL_PARENT_DUNS
1885 ,GLOBAL_PARENT_ADD1
1886 ,GLOBAL_PARENT_ADD2
1887 ,GLOBAL_PARENT_CITY
1888 ,GLOBAL_PARENT_POSTAL_CODE
1889 ,GLOBAL_PARENT_COUNTRY
1890 ,GLOBAL_PARENT_STATE
1891 ,GLOBAL_PARENT_PHONE
1892 ,bank_acct_type
1893 )
1894
1895 SELECT l_file_date
1896 ,fcft.DUNS
1897 ,replace(FCFT.PLUS_FOUR,' ',null)
1898 ,fcft.CAGE_CODE
1899 ,fcft.EXTRACT_CODE
1900 ,fcft.REGISTRATION_DATE
1901 ,fcft.RENEWAL_DATE
1902 ,fcft.LEGAL_BUS_NAME
1903 ,fcft.DBA_NAME
1904 ,fcft.DIVISION_NAME
1905 ,fcft.DIVISION_NUMBER
1906 ,fcft.ST_ADDRESS1
1907 ,fcft.ST_ADDRESS2
1908 ,fcft.CITY
1909 ,fcft.STATE
1910 ,fcft.POSTAL_CODE
1911 ,fcft.COUNTRY
1912 ,fcft.BUSINESS_START_DATE
1913 ,fcft.FISCAL_YR_CLOSE_DATE
1914 ,fcft.CORP_SECURITY_LEVEL
1915 ,fcft.EMP_SECURITY_LEVEL
1916 ,fcft.WEB_SITE
1917 ,fcft.ORGANIZATIONAL_TYPE
1918 ,fcft.STATE_OF_INC
1919 ,fcft.COUNTRY_OF_INC
1920 ,fcft.CREDIT_CARD_FLAG
1921 ,fcft.CORRESPONDENCE_FLAG
1922 ,fcft.MAIL_POC
1923 ,fcft.MAIL_ADD1
1924 ,fcft.MAIL_ADD2
1925 ,fcft.MAIL_CITY
1926 ,fcft.MAIL_POSTAL_CODE
1927 ,fcft.MAIL_COUNTRY
1928 ,fcft.MAIL_STATE
1929 ,fcft.PREV_BUS_POC
1930 ,fcft.PREV_BUS_ADD1
1931 ,fcft.PREV_BUS_ADD2
1932 ,fcft.PREV_BUS_CITY
1933 ,fcft.PREV_BUS_POSTAL_CODE
1934 ,fcft.PREV_BUS_COUNTRY
1935 ,fcft.PREV_BUS_STATE
1936 ,fcft.PARENT_POC
1937 ,fcft.PARENT_DUNS
1938 ,fcft.PARENT_ADD1
1939 ,fcft.PARENT_ADD2
1940 ,fcft.PARENT_CITY
1941 ,fcft.PARENT_POSTAL_CODE
1942 ,fcft.PARENT_COUNTRY
1943 ,fcft.PARENT_STATE
1944 ,fcft.GOV_PARENT_POC
1945 ,fcft.GOV_PARENT_ADD1
1946 ,fcft.GOV_PARENT_ADD2
1947 ,fcft.GOV_PARENT_CITY
1948 ,fcft.GOV_PARENT_POSTAL_CODE
1949 ,fcft.GOV_PARENT_COUNTRY
1950 ,fcft.GOV_PARENT_STATE
1951 ,fcft.GOV_BUS_POC
1952 ,fcft.GOV_BUS_ADD1
1953 ,fcft.GOV_BUS_ADD2
1954 ,fcft.GOV_BUS_CITY
1955 ,fcft.GOV_BUS_POSTAL_CODE
1956 ,fcft.GOV_BUS_COUNTRY
1957 ,fcft.GOV_BUS_STATE
1958 ,fcft.GOV_BUS_US_PHONE
1959 ,fcft.GOV_BUS_US_PHONE_EX
1960 ,fcft.GOV_BUS_NON_US_PHONE
1961 ,fcft.GOV_BUS_FAX
1962 ,fcft.GOV_BUS_EMAIL
1963 ,fcft.ALT_GOV_BUS_POC
1964 ,fcft.ALT_GOV_BUS_ADD1
1965 ,fcft.ALT_GOV_BUS_ADD2
1966 ,fcft.ALT_GOV_BUS_CITY
1967 ,fcft.ALT_GOV_BUS_POSTAL_CODE
1968 ,fcft.ALT_GOV_BUS_COUNTRY
1969 ,fcft.ALT_GOV_BUS_STATE
1970 ,fcft.ALT_GOV_BUS_US_PHONE
1971 ,fcft.ALT_GOV_BUS_US_PHONE_EX
1972 ,fcft.ALT_GOV_BUS_NON_US_PHONE
1973 ,fcft.ALT_GOV_BUS_FAX
1974 ,fcft.ALT_GOV_BUS_EMAIL
1975 ,fcft.PAST_PERF_POC
1976 ,fcft.PAST_PERF_ADD1
1977 ,fcft.PAST_PERF_ADD2
1978 ,fcft.PAST_PERF_CITY
1979 ,fcft.PAST_PERF_POSTAL_CODE
1980 ,fcft.PAST_PERF_COUNTRY
1981 ,fcft.PAST_PERF_STATE
1982 ,fcft.PAST_PERF_US_PHONE
1983 ,fcft.PAST_PERF_US_PHONE_EX
1984 ,fcft.PAST_PERF_NON_US_PHONE
1985 ,fcft.PAST_PERF_FAX
1986 ,fcft.PAST_PERF_EMAIL
1987 ,fcft.ALT_PAST_PERF_POC
1988 ,fcft.ALT_PAST_PERF_ADD1
1989 ,fcft.ALT_PAST_PERF_ADD2
1990 ,fcft.ALT_PAST_PERF_CITY
1991 ,fcft.ALT_PAST_PERF_POSTAL_CODE
1992 ,fcft.ALT_PAST_PERF_COUNTRY
1993 ,fcft.ALT_PAST_PERF_STATE
1994 ,fcft.ALT_PAST_PERF_US_PHONE
1995 ,fcft.ALT_PAST_PERF_US_PHONE_EX
1996 ,fcft.ALT_PAST_PERF_NON_US_PHONE
1997 ,fcft.ALT_PAST_PERF_FAX
1998 ,fcft.ALT_PAST_PERF_EMAIL
1999 ,fcft.ELEC_BUS_POC
2000 ,fcft.ELEC_BUS_ADD1
2001 ,fcft.ELEC_BUS_ADD2
2002 ,fcft.ELEC_BUS_CITY
2003 ,fcft.ELEC_BUS_POSTAL_CODE
2004 ,fcft.ELEC_BUS_COUNTRY
2005 ,fcft.ELEC_BUS_STATE
2006 ,fcft.ELEC_BUS_US_PHONE
2007 ,fcft.ELEC_BUS_US_PHONE_EX
2008 ,fcft.ELEC_BUS_NON_US_PHONE
2009 ,fcft.ELEC_BUS_FAX
2010 ,fcft.ELEC_BUS_EMAIL
2011 ,fcft.ALT_ELEC_BUS_POC
2012 ,fcft.ALT_ELEC_BUS_ADD1
2013 ,fcft.ALT_ELEC_BUS_ADD2
2014 ,fcft.ALT_ELEC_BUS_CITY
2015 ,fcft.ALT_ELEC_BUS_POSTAL_CODE
2016 ,fcft.ALT_ELEC_BUS_COUNTRY
2017 ,fcft.ALT_ELEC_BUS_STATE
2018 ,fcft.ALT_ELEC_BUS_US_PHONE
2019 ,fcft.ALT_ELEC_BUS_US_PHONE_EX
2020 ,fcft.ALT_ELEC_BUS_NON_US_PHONE
2021 ,fcft.ALT_ELEC_BUS_FAX
2022 ,fcft.ALT_ELEC_BUS_EMAIL
2023 ,fcft.CERTIFIER_POC
2024 ,fcft.CERTIFIER_US_PHONE
2025 ,fcft.CERTIFIER_US_PHONE_EX
2026 ,fcft.CERTIFIER_NON_US_PHONE
2027 ,fcft.CERTIFIER_FAX
2028 ,fcft.CERTIFIER_EMAIL
2029 ,fcft.ALT_CERTIFIER_POC
2030 ,fcft.ALT_CERTIFIER_US_PHONE
2031 ,fcft.ALT_CERTIFIER_US_PHONE_EX
2032 ,fcft.ALT_CERTIFIER_NON_US_PHONE
2033 ,fcft.CORP_INFO_POC
2034 ,fcft.CORP_INFO_US_PHONE
2035 ,fcft.CORP_INFO_US_PHONE_EX
2036 ,fcft.CORP_INFO_NON_US_PHONE
2037 ,fcft.CORP_INFO_FAX
2038 ,fcft.CORP_INFO_EMAIL
2039 ,fcft.OWNER_INFO_POC
2040 ,fcft.OWNER_INFO_US_PHONE
2041 ,fcft.OWNER_INFO_US_PHONE_EX
2042 ,fcft.OWNER_INFO_NON_US_PHONE
2043 ,fcft.OWNER_INFO_FAX
2044 ,fcft.OWNER_INFO_EMAIL
2045 ,fcft.EDI
2046 ,fcft.TAXPAYER_ID
2047 ,fcft.AVG_NUM_EMPLOYEES
2048 ,fcft.SOCIAL_SECURITY_NUMBER
2049 ,fcft.FINANCIAL_INSTITUTE
2050 ,fcft.BANK_ACCT_NUMBER
2051 ,fcft.ABA_ROUTING
2052 ,fcft.LOCKBOX_NUMBER
2053 ,fcft.AUTHORIZATION_DATE
2054 ,fcft.EFT_WAIVER
2055 ,fcft.ACH_US_PHONE
2056 ,fcft.ACH_NON_US_PHONE
2057 ,fcft.ACH_FAX
2058 ,fcft.ACH_EMAIL
2059 ,fcft.REMIT_POC
2060 ,fcft.REMIT_ADD1
2061 ,fcft.REMIT_ADD2
2062 ,fcft.REMIT_CITY
2063 ,fcft.REMIT_STATE
2064 ,fcft.REMIT_POSTAL_CODE
2065 ,fcft.REMIT_COUNTRY
2066 ,fcft.AR_POC
2067 ,fcft.AR_US_PHONE
2068 ,fcft.AR_US_PHONE_EX
2069 ,fcft.AR_NON_US_PHONE
2070 ,fcft.AR_FAX
2071 ,fcft.AR_EMAIL
2072 ,fcft.MPIN
2073 ,fcft.HQ_PARENT_DUNS
2074 ,fcft.HQ_PARENT_ADD1
2075 ,fcft.HQ_PARENT_ADD2
2076 ,fcft.HQ_PARENT_CITY
2077 ,fcft.HQ_PARENT_STATE
2078 ,fcft.HQ_PARENT_POSTAL_CODE
2079 ,fcft.HQ_PARENT_COUNTRY
2080 ,fcft.HQ_PARENT_PHONE
2081 ,fcft.AUSTIN_TETRA_NUMBER
2082 ,fcft.AUSTIN_TETRA_PARENT_NUMBER
2083 ,fcft.AUSTIN_TETRA_ULTIMATE_NUMBER
2084 ,fcft.AUSTIN_TETRA_PCARD_FLAG
2085 ,fcft.DNB_MONITOR_LAST_UPDATED
2086 ,fcft.DNB_MONITOR_STATUS
2087 ,fcft.DNB_MONITOR_CORP_NAME
2088 ,fcft.DNB_MONITOR_DBA
2089 ,fcft.DNB_MONITOR_ST_ADD1
2090 ,fcft.DNB_MONITOR_ST_ADD2
2091 ,fcft.DNB_MONITOR_CITY
2092 ,fcft.DNB_MONITOR_POSTAL_CODE
2093 ,fcft.DNB_MONITOR_COUNTRY_CODE
2094 ,fcft.DNB_MONITOR_STATE
2095 ,fcft.HQ_PARENT_POC
2096 ,fcft.PAYMENT_TYPE
2097 ,fcft.ANNUAL_RECEIPTS
2098 ,fcft.DOMESTIC_PARENT_POC
2099 ,fcft.DOMESTIC_PARENT_DUNS
2100 ,fcft.DOMESTIC_PARENT_ADD1
2101 ,fcft.DOMESTIC_PARENT_ADD2
2102 ,fcft.DOMESTIC_PARENT_CITY
2103 ,fcft.DOMESTIC_PARENT_POSTAL_CODE
2104 ,fcft.DOMESTIC_PARENT_COUNTRY
2105 ,fcft.DOMESTIC_PARENT_STATE
2106 ,fcft.DOMESTIC_PARENT_PHONE
2107 ,fcft.GLOBAL_PARENT_POC
2108 ,fcft.GLOBAL_PARENT_DUNS
2109 ,fcft.GLOBAL_PARENT_ADD1
2110 ,fcft.GLOBAL_PARENT_ADD2
2111 ,fcft.GLOBAL_PARENT_CITY
2112 ,fcft.GLOBAL_PARENT_POSTAL_CODE
2113 ,fcft.GLOBAL_PARENT_COUNTRY
2114 ,fcft.GLOBAL_PARENT_STATE
2115 ,fcft.GLOBAL_PARENT_PHONE
2116 ,bank_acct_type
2117
2118 FROM fv_ccr_file_temp fcft
2119 WHERE ( (p_xml_import = 'N' AND exists ( select 1 from fv_ccr_vendors fcv
2120 where fcft.duns = fcv.duns
2121 and fcv.ccr_status ='N'))
2122 OR p_xml_import='Y')
2123 order by rowid;
2124
2125
2126 ELSIF (l_update_type ='S') THEN
2127
2128 l_errbuf := 'Update type as S - inserting into seciond temp table';
2129 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2130 INSERT INTO fv_ccr_process_gt ( FILE_DATE
2131 ,DUNS
2132 ,PLUS_FOUR
2133 ,CAGE_CODE
2134 ,EXTRACT_CODE
2135 ,REGISTRATION_DATE
2136 ,RENEWAL_DATE
2137 ,LEGAL_BUS_NAME
2138 ,DBA_NAME
2139 ,DIVISION_NAME
2140 ,DIVISION_NUMBER
2141 ,ST_ADDRESS1
2142 ,ST_ADDRESS2
2143 ,CITY
2144 ,STATE
2145 ,POSTAL_CODE
2146 ,COUNTRY
2147 ,BUSINESS_START_DATE
2148 ,FISCAL_YR_CLOSE_DATE
2149 ,CORP_SECURITY_LEVEL
2150 ,EMP_SECURITY_LEVEL
2151 ,WEB_SITE
2152 ,ORGANIZATIONAL_TYPE
2153 ,STATE_OF_INC
2154 ,COUNTRY_OF_INC
2155 --,BUSINESS_TYPES
2156 ,CREDIT_CARD_FLAG
2157 ,CORRESPONDENCE_FLAG
2158 ,MAIL_POC
2159 ,MAIL_ADD1
2160 ,MAIL_ADD2
2161 ,MAIL_CITY
2162 ,MAIL_POSTAL_CODE
2163 ,MAIL_COUNTRY
2164 ,MAIL_STATE
2165 ,PREV_BUS_POC
2166 ,PREV_BUS_ADD1
2167 ,PREV_BUS_ADD2
2168 ,PREV_BUS_CITY
2169 ,PREV_BUS_POSTAL_CODE
2170 ,PREV_BUS_COUNTRY
2171 ,PREV_BUS_STATE
2172 ,PARENT_POC
2173 ,PARENT_DUNS
2174 ,PARENT_ADD1
2175 ,PARENT_ADD2
2176 ,PARENT_CITY
2177 ,PARENT_POSTAL_CODE
2178 ,PARENT_COUNTRY
2179 ,PARENT_STATE
2180 ,GOV_PARENT_POC
2181 ,GOV_PARENT_ADD1
2182 ,GOV_PARENT_ADD2
2183 ,GOV_PARENT_CITY
2184 ,GOV_PARENT_POSTAL_CODE
2185 ,GOV_PARENT_COUNTRY
2186 ,GOV_PARENT_STATE
2187 ,GOV_BUS_POC
2188 ,GOV_BUS_ADD1
2189 ,GOV_BUS_ADD2
2190 ,GOV_BUS_CITY
2191 ,GOV_BUS_POSTAL_CODE
2192 ,GOV_BUS_COUNTRY
2193 ,GOV_BUS_STATE
2194 ,GOV_BUS_US_PHONE
2195 ,GOV_BUS_US_PHONE_EX
2196 ,GOV_BUS_NON_US_PHONE
2197 ,GOV_BUS_FAX
2198 ,GOV_BUS_EMAIL
2199 ,ALT_GOV_BUS_POC
2200 ,ALT_GOV_BUS_ADD1
2201 ,ALT_GOV_BUS_ADD2
2202 ,ALT_GOV_BUS_CITY
2203 ,ALT_GOV_BUS_POSTAL_CODE
2204 ,ALT_GOV_BUS_COUNTRY
2205 ,ALT_GOV_BUS_STATE
2206 ,ALT_GOV_BUS_US_PHONE
2207 ,ALT_GOV_BUS_US_PHONE_EX
2208 ,ALT_GOV_BUS_NON_US_PHONE
2209 ,ALT_GOV_BUS_FAX
2210 ,ALT_GOV_BUS_EMAIL
2211 ,PAST_PERF_POC
2212 ,PAST_PERF_ADD1
2213 ,PAST_PERF_ADD2
2214 ,PAST_PERF_CITY
2215 ,PAST_PERF_POSTAL_CODE
2216 ,PAST_PERF_COUNTRY
2217 ,PAST_PERF_STATE
2218 ,PAST_PERF_US_PHONE
2219 ,PAST_PERF_US_PHONE_EX
2220 ,PAST_PERF_NON_US_PHONE
2221 ,PAST_PERF_FAX
2222 ,PAST_PERF_EMAIL
2223 ,ALT_PAST_PERF_POC
2224 ,ALT_PAST_PERF_ADD1
2225 ,ALT_PAST_PERF_ADD2
2226 ,ALT_PAST_PERF_CITY
2227 ,ALT_PAST_PERF_POSTAL_CODE
2228 ,ALT_PAST_PERF_COUNTRY
2229 ,ALT_PAST_PERF_STATE
2230 ,ALT_PAST_PERF_US_PHONE
2231 ,ALT_PAST_PERF_US_PHONE_EX
2232 ,ALT_PAST_PERF_NON_US_PHONE
2233 ,ALT_PAST_PERF_FAX
2234 ,ALT_PAST_PERF_EMAIL
2235 ,ELEC_BUS_POC
2236 ,ELEC_BUS_ADD1
2237 ,ELEC_BUS_ADD2
2238 ,ELEC_BUS_CITY
2239 ,ELEC_BUS_POSTAL_CODE
2240 ,ELEC_BUS_COUNTRY
2241 ,ELEC_BUS_STATE
2242 ,ELEC_BUS_US_PHONE
2243 ,ELEC_BUS_US_PHONE_EX
2244 ,ELEC_BUS_NON_US_PHONE
2245 ,ELEC_BUS_FAX
2246 ,ELEC_BUS_EMAIL
2247 ,ALT_ELEC_BUS_POC
2248 ,ALT_ELEC_BUS_ADD1
2249 ,ALT_ELEC_BUS_ADD2
2250 ,ALT_ELEC_BUS_CITY
2251 ,ALT_ELEC_BUS_POSTAL_CODE
2252 ,ALT_ELEC_BUS_COUNTRY
2253 ,ALT_ELEC_BUS_STATE
2254 ,ALT_ELEC_BUS_US_PHONE
2255 ,ALT_ELEC_BUS_US_PHONE_EX
2256 ,ALT_ELEC_BUS_NON_US_PHONE
2257 ,ALT_ELEC_BUS_FAX
2258 ,ALT_ELEC_BUS_EMAIL
2259 ,CERTIFIER_POC
2260 ,CERTIFIER_US_PHONE
2261 ,CERTIFIER_US_PHONE_EX
2262 ,CERTIFIER_NON_US_PHONE
2263 ,CERTIFIER_FAX
2264 ,CERTIFIER_EMAIL
2265 ,ALT_CERTIFIER_POC
2266 ,ALT_CERTIFIER_US_PHONE
2267 ,ALT_CERTIFIER_US_PHONE_EX
2268 ,ALT_CERTIFIER_NON_US_PHONE
2269 ,CORP_INFO_POC
2270 ,CORP_INFO_US_PHONE
2271 ,CORP_INFO_US_PHONE_EX
2272 ,CORP_INFO_NON_US_PHONE
2273 ,CORP_INFO_FAX
2274 ,CORP_INFO_EMAIL
2275 ,OWNER_INFO_POC
2276 ,OWNER_INFO_US_PHONE
2277 ,OWNER_INFO_US_PHONE_EX
2278 ,OWNER_INFO_NON_US_PHONE
2279 ,OWNER_INFO_FAX
2280 ,OWNER_INFO_EMAIL
2281 ,EDI
2282 ,TAXPAYER_ID
2283 ,AVG_NUM_EMPLOYEES
2284 ,SOCIAL_SECURITY_NUMBER
2285 ,FINANCIAL_INSTITUTE
2286 ,BANK_ACCT_NUMBER
2287 ,ABA_ROUTING
2288 ,LOCKBOX_NUMBER
2289 ,AUTHORIZATION_DATE
2290 ,EFT_WAIVER
2291 ,ACH_US_PHONE
2292 ,ACH_NON_US_PHONE
2293 ,ACH_FAX
2294 ,ACH_EMAIL
2295 ,REMIT_POC
2296 ,REMIT_ADD1
2297 ,REMIT_ADD2
2298 ,REMIT_CITY
2299 ,REMIT_STATE
2300 ,REMIT_POSTAL_CODE
2301 ,REMIT_COUNTRY
2302 ,AR_POC
2303 ,AR_US_PHONE
2304 ,AR_US_PHONE_EX
2305 ,AR_NON_US_PHONE
2306 ,AR_FAX
2307 ,AR_EMAIL
2308 ,MPIN
2309 ,HQ_PARENT_DUNS
2310 ,HQ_PARENT_ADD1
2311 ,HQ_PARENT_ADD2
2312 ,HQ_PARENT_CITY
2313 ,HQ_PARENT_STATE
2314 ,HQ_PARENT_POSTAL_CODE
2315 ,HQ_PARENT_COUNTRY
2316 ,HQ_PARENT_PHONE
2317 ,AUSTIN_TETRA_NUMBER
2318 ,AUSTIN_TETRA_PARENT_NUMBER
2319 ,AUSTIN_TETRA_ULTIMATE_NUMBER
2320 ,AUSTIN_TETRA_PCARD_FLAG
2321 ,DNB_MONITOR_LAST_UPDATED
2322 ,DNB_MONITOR_STATUS
2323 ,DNB_MONITOR_CORP_NAME
2324 ,DNB_MONITOR_DBA
2325 ,DNB_MONITOR_ST_ADD1
2326 ,DNB_MONITOR_ST_ADD2
2327 ,DNB_MONITOR_CITY
2328 ,DNB_MONITOR_POSTAL_CODE
2329 ,DNB_MONITOR_COUNTRY_CODE
2330 ,DNB_MONITOR_STATE
2331 ,HQ_PARENT_POC
2332 ,PAYMENT_TYPE
2333 ,ANNUAL_RECEIPTS
2334 ,DOMESTIC_PARENT_POC
2335 ,DOMESTIC_PARENT_DUNS
2336 ,DOMESTIC_PARENT_ADD1
2337 ,DOMESTIC_PARENT_ADD2
2338 ,DOMESTIC_PARENT_CITY
2339 ,DOMESTIC_PARENT_POSTAL_CODE
2340 ,DOMESTIC_PARENT_COUNTRY
2341 ,DOMESTIC_PARENT_STATE
2342 ,DOMESTIC_PARENT_PHONE
2343 ,GLOBAL_PARENT_POC
2344 ,GLOBAL_PARENT_DUNS
2345 ,GLOBAL_PARENT_ADD1
2346 ,GLOBAL_PARENT_ADD2
2347 ,GLOBAL_PARENT_CITY
2348 ,GLOBAL_PARENT_POSTAL_CODE
2349 ,GLOBAL_PARENT_COUNTRY
2350 ,GLOBAL_PARENT_STATE
2351 ,GLOBAL_PARENT_PHONE
2352 ,bank_acct_type
2353 )
2354 SELECT l_file_date
2355 ,fcft.DUNS
2356 ,replace(PLUS_FOUR,' ',null)
2357 ,CAGE_CODE
2358 ,EXTRACT_CODE
2359 ,REGISTRATION_DATE
2360 ,RENEWAL_DATE
2361 ,LEGAL_BUS_NAME
2362 ,DBA_NAME
2363 ,DIVISION_NAME
2364 ,DIVISION_NUMBER
2365 ,ST_ADDRESS1
2366 ,ST_ADDRESS2
2367 ,CITY
2368 ,STATE
2369 ,POSTAL_CODE
2370 ,COUNTRY
2371 ,BUSINESS_START_DATE
2372 ,FISCAL_YR_CLOSE_DATE
2373 ,CORP_SECURITY_LEVEL
2374 ,EMP_SECURITY_LEVEL
2375 ,WEB_SITE
2376 ,ORGANIZATIONAL_TYPE
2377 ,STATE_OF_INC
2378 ,COUNTRY_OF_INC
2379 --,BUSINESS_TYPES
2380 ,CREDIT_CARD_FLAG
2381 ,CORRESPONDENCE_FLAG
2382 ,MAIL_POC
2383 ,MAIL_ADD1
2384 ,MAIL_ADD2
2385 ,MAIL_CITY
2386 ,MAIL_POSTAL_CODE
2387 ,MAIL_COUNTRY
2388 ,MAIL_STATE
2389 ,PREV_BUS_POC
2390 ,PREV_BUS_ADD1
2391 ,PREV_BUS_ADD2
2392 ,PREV_BUS_CITY
2393 ,PREV_BUS_POSTAL_CODE
2394 ,PREV_BUS_COUNTRY
2395 ,PREV_BUS_STATE
2396 ,PARENT_POC
2397 ,PARENT_DUNS
2398 ,PARENT_ADD1
2399 ,PARENT_ADD2
2400 ,PARENT_CITY
2401 ,PARENT_POSTAL_CODE
2402 ,PARENT_COUNTRY
2403 ,PARENT_STATE
2404 ,GOV_PARENT_POC
2405 ,GOV_PARENT_ADD1
2406 ,GOV_PARENT_ADD2
2407 ,GOV_PARENT_CITY
2408 ,GOV_PARENT_POSTAL_CODE
2409 ,GOV_PARENT_COUNTRY
2410 ,GOV_PARENT_STATE
2411 ,GOV_BUS_POC
2412 ,GOV_BUS_ADD1
2413 ,GOV_BUS_ADD2
2414 ,GOV_BUS_CITY
2415 ,GOV_BUS_POSTAL_CODE
2416 ,GOV_BUS_COUNTRY
2417 ,GOV_BUS_STATE
2418 ,GOV_BUS_US_PHONE
2419 ,GOV_BUS_US_PHONE_EX
2420 ,GOV_BUS_NON_US_PHONE
2421 ,GOV_BUS_FAX
2422 ,GOV_BUS_EMAIL
2423 ,ALT_GOV_BUS_POC
2424 ,ALT_GOV_BUS_ADD1
2425 ,ALT_GOV_BUS_ADD2
2426 ,ALT_GOV_BUS_CITY
2427 ,ALT_GOV_BUS_POSTAL_CODE
2428 ,ALT_GOV_BUS_COUNTRY
2429 ,ALT_GOV_BUS_STATE
2430 ,ALT_GOV_BUS_US_PHONE
2431 ,ALT_GOV_BUS_US_PHONE_EX
2432 ,ALT_GOV_BUS_NON_US_PHONE
2433 ,ALT_GOV_BUS_FAX
2434 ,ALT_GOV_BUS_EMAIL
2435 ,PAST_PERF_POC
2436 ,PAST_PERF_ADD1
2437 ,PAST_PERF_ADD2
2438 ,PAST_PERF_CITY
2439 ,PAST_PERF_POSTAL_CODE
2440 ,PAST_PERF_COUNTRY
2441 ,PAST_PERF_STATE
2442 ,PAST_PERF_US_PHONE
2443 ,PAST_PERF_US_PHONE_EX
2444 ,PAST_PERF_NON_US_PHONE
2445 ,PAST_PERF_FAX
2446 ,PAST_PERF_EMAIL
2447 ,ALT_PAST_PERF_POC
2448 ,ALT_PAST_PERF_ADD1
2449 ,ALT_PAST_PERF_ADD2
2450 ,ALT_PAST_PERF_CITY
2451 ,ALT_PAST_PERF_POSTAL_CODE
2452 ,ALT_PAST_PERF_COUNTRY
2453 ,ALT_PAST_PERF_STATE
2454 ,ALT_PAST_PERF_US_PHONE
2455 ,ALT_PAST_PERF_US_PHONE_EX
2456 ,ALT_PAST_PERF_NON_US_PHONE
2457 ,ALT_PAST_PERF_FAX
2458 ,ALT_PAST_PERF_EMAIL
2459 ,ELEC_BUS_POC
2460 ,ELEC_BUS_ADD1
2461 ,ELEC_BUS_ADD2
2462 ,ELEC_BUS_CITY
2463 ,ELEC_BUS_POSTAL_CODE
2464 ,ELEC_BUS_COUNTRY
2465 ,ELEC_BUS_STATE
2466 ,ELEC_BUS_US_PHONE
2467 ,ELEC_BUS_US_PHONE_EX
2468 ,ELEC_BUS_NON_US_PHONE
2469 ,ELEC_BUS_FAX
2470 ,ELEC_BUS_EMAIL
2471 ,ALT_ELEC_BUS_POC
2472 ,ALT_ELEC_BUS_ADD1
2473 ,ALT_ELEC_BUS_ADD2
2474 ,ALT_ELEC_BUS_CITY
2475 ,ALT_ELEC_BUS_POSTAL_CODE
2476 ,ALT_ELEC_BUS_COUNTRY
2477 ,ALT_ELEC_BUS_STATE
2478 ,ALT_ELEC_BUS_US_PHONE
2479 ,ALT_ELEC_BUS_US_PHONE_EX
2480 ,ALT_ELEC_BUS_NON_US_PHONE
2481 ,ALT_ELEC_BUS_FAX
2482 ,ALT_ELEC_BUS_EMAIL
2483 ,CERTIFIER_POC
2484 ,CERTIFIER_US_PHONE
2485 ,CERTIFIER_US_PHONE_EX
2486 ,CERTIFIER_NON_US_PHONE
2487 ,CERTIFIER_FAX
2488 ,CERTIFIER_EMAIL
2489 ,ALT_CERTIFIER_POC
2490 ,ALT_CERTIFIER_US_PHONE
2491 ,ALT_CERTIFIER_US_PHONE_EX
2492 ,ALT_CERTIFIER_NON_US_PHONE
2493 ,CORP_INFO_POC
2494 ,CORP_INFO_US_PHONE
2495 ,CORP_INFO_US_PHONE_EX
2496 ,CORP_INFO_NON_US_PHONE
2497 ,CORP_INFO_FAX
2498 ,CORP_INFO_EMAIL
2499 ,OWNER_INFO_POC
2500 ,OWNER_INFO_US_PHONE
2501 ,OWNER_INFO_US_PHONE_EX
2502 ,OWNER_INFO_NON_US_PHONE
2503 ,OWNER_INFO_FAX
2504 ,OWNER_INFO_EMAIL
2505 ,EDI
2506 ,TAXPAYER_ID
2507 ,AVG_NUM_EMPLOYEES
2508 ,SOCIAL_SECURITY_NUMBER
2509 ,FINANCIAL_INSTITUTE
2510 ,BANK_ACCT_NUMBER
2511 ,ABA_ROUTING
2512 ,LOCKBOX_NUMBER
2513 ,AUTHORIZATION_DATE
2514 ,EFT_WAIVER
2515 ,ACH_US_PHONE
2516 ,ACH_NON_US_PHONE
2517 ,ACH_FAX
2518 ,ACH_EMAIL
2519 ,REMIT_POC
2520 ,REMIT_ADD1
2521 ,REMIT_ADD2
2522 ,REMIT_CITY
2523 ,REMIT_STATE
2524 ,REMIT_POSTAL_CODE
2525 ,REMIT_COUNTRY
2526 ,AR_POC
2527 ,AR_US_PHONE
2528 ,AR_US_PHONE_EX
2529 ,AR_NON_US_PHONE
2530 ,AR_FAX
2531 ,AR_EMAIL
2532 ,MPIN
2533 ,HQ_PARENT_DUNS
2534 ,HQ_PARENT_ADD1
2535 ,HQ_PARENT_ADD2
2536 ,HQ_PARENT_CITY
2537 ,HQ_PARENT_STATE
2538 ,HQ_PARENT_POSTAL_CODE
2539 ,HQ_PARENT_COUNTRY
2540 ,HQ_PARENT_PHONE
2541 ,AUSTIN_TETRA_NUMBER
2542 ,AUSTIN_TETRA_PARENT_NUMBER
2543 ,AUSTIN_TETRA_ULTIMATE_NUMBER
2544 ,AUSTIN_TETRA_PCARD_FLAG
2545 ,DNB_MONITOR_LAST_UPDATED
2546 ,DNB_MONITOR_STATUS
2547 ,DNB_MONITOR_CORP_NAME
2548 ,DNB_MONITOR_DBA
2549 ,DNB_MONITOR_ST_ADD1
2550 ,DNB_MONITOR_ST_ADD2
2551 ,DNB_MONITOR_CITY
2552 ,DNB_MONITOR_POSTAL_CODE
2553 ,DNB_MONITOR_COUNTRY_CODE
2554 ,DNB_MONITOR_STATE
2555 ,HQ_PARENT_POC
2556 ,PAYMENT_TYPE
2557 ,ANNUAL_RECEIPTS
2558 ,DOMESTIC_PARENT_POC
2559 ,DOMESTIC_PARENT_DUNS
2560 ,DOMESTIC_PARENT_ADD1
2561 ,DOMESTIC_PARENT_ADD2
2562 ,DOMESTIC_PARENT_CITY
2563 ,DOMESTIC_PARENT_POSTAL_CODE
2564 ,DOMESTIC_PARENT_COUNTRY
2565 ,DOMESTIC_PARENT_STATE
2566 ,DOMESTIC_PARENT_PHONE
2567 ,GLOBAL_PARENT_POC
2568 ,GLOBAL_PARENT_DUNS
2569 ,GLOBAL_PARENT_ADD1
2570 ,GLOBAL_PARENT_ADD2
2571 ,GLOBAL_PARENT_CITY
2572 ,GLOBAL_PARENT_POSTAL_CODE
2573 ,GLOBAL_PARENT_COUNTRY
2574 ,GLOBAL_PARENT_STATE
2575 ,GLOBAL_PARENT_PHONE
2576 ,bank_acct_type
2577 FROM fv_ccr_file_temp fcft
2578 WHERE fcft.duns = substr(p_duns, 1, 9)
2579 order by rowid;
2580
2581
2582
2583 END IF;
2584
2585 l_errbuf := 'Processing data for Extract Code as 1 ';
2586 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2587
2588 -- set the status to disabled for code as 1
2589 UPDATE fv_ccr_vendors fcv SET fcv.ccr_status ='D',
2590 fcv.enabled ='N' ,
2591 fcv.extract_code ='1' ,
2592 fcv.last_update_date = sysdate,
2593 fcv.last_import_date = nvl(l_file_date,sysdate),
2594 fcv.last_updated_by = fnd_global.user_id
2595 WHERE exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
2596 WHERE fcv.duns = fcpg.duns
2597 AND nvl(fcv.plus_four,-99)= nvl(fcpg.plus_four,-99)
2598 AND fcpg.extract_code = '1');
2599
2600 l_errbuf := 'Processing data for Extract Code as 4 ';
2601 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2602 UPDATE fv_ccr_vendors fcv SET ccr_status ='E',
2603 enabled='N',
2604 extract_code ='4',
2605 last_update_date = sysdate,
2606 last_updated_by = fnd_global.user_id,
2607 last_import_date = nvl(l_file_date,sysdate)
2608 WHERE exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
2609 WHERE fcv.duns = fcpg.duns
2610 AND nvl(fcv.plus_four,-99)= nvl(fcpg.plus_four,-99)
2611 AND fcpg.extract_code = '4');
2612
2613 -- Fixed as part of BUG 3960809 for showing deleted/expired DUNS returned
2614 insert into fv_ccr_process_report(duns_info,record_type,reference1,reference2,reference3,reference4)
2615 SELECT DUNS||nvl(plus_four,''),'1',legal_bus_name,' ',' ',decode(fcpg.extract_code,'1','Deleted','4','Expired')
2616 FROM fv_ccr_process_gt fcpg
2617 WHERE fcpg.extract_code IN ('1','4');
2618
2619
2620 l_errbuf := 'Processing CCR Data for a,2,3';
2621 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2622
2623 FND_MESSAGE.SET_NAME('FV','FV_CCR_ASSIGN_PAY_OBJ');
2624 l_msg_pay_obj := FND_MESSAGE.GET;
2625
2626 FOR l_ccr_data IN c_ccr_data
2627 LOOP
2628
2629
2630 l_errbuf := 'Processing DUNS - > '|| l_ccr_data.duns;
2631 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2632
2633 l_valid_tin := nvl(l_ccr_data.taxpayer_id,l_ccr_data.social_security_number);
2634 IF((l_valid_tin IS NULL or length(l_valid_tin)<>9)
2635 AND l_ccr_data.country = 'USA') THEN
2636 FND_MESSAGE.set_NAME('FV','FV_CCR_INVALID_TAXPAYER_NUMBER');
2637 message_text := FND_MESSAGE.get;
2638 l_errbuf :='Invalid Taxpayer Number: '||nvl(l_valid_tin,'null');
2639 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'main',l_errbuf);
2640 insert_temp_data(3,null,message_text ,null,l_ccr_data.duns||nvl(l_ccr_data.plus_four,''),null,null);
2641
2642 ELSE
2643
2644 /*l_code(1).code:=substr(l_ccr_data.business_types,1,2) ;
2645 l_code(2).code:=substr(l_ccr_data.business_types,3,2) ;
2646 l_code(3).code:=substr(l_ccr_data.business_types,5,2) ;
2647 l_code(4).code:=substr(l_ccr_data.business_types,7,2) ;
2648 l_code(5).code:=substr(l_ccr_data.business_types,9,2) ;
2649 l_code(6).code:=substr(l_ccr_data.business_types,11,2) ;
2650 l_code(7).code:=substr(l_ccr_data.business_types,13,2) ;
2651 l_code(8).code:=substr(l_ccr_data.business_types,15,2) ;
2652 l_code(9).code:=substr(l_ccr_data.business_types,17,2) ;
2653 l_code(10).code:=substr(l_ccr_data.business_types,19,2) ;
2654 l_code(11).code:=substr(l_ccr_data.sic_codes,1,8) ;
2655 l_code(12).code:=substr(l_ccr_data.sic_codes,9,8) ;
2656 l_code(13).code:=substr(l_ccr_data.sic_codes,17,8) ;
2657 l_code(14).code:=substr(l_ccr_data.sic_codes,25,8) ;
2658 l_code(15).code:=substr(l_ccr_data.sic_codes,33,8) ;
2659 l_code(16).code:=substr(l_ccr_data.sic_codes,41,8) ;
2660 l_code(17).code:=substr(l_ccr_data.sic_codes,49,8) ;
2661 l_code(18).code:=substr(l_ccr_data.sic_codes,57,8) ;
2662 l_code(19).code:=substr(l_ccr_data.sic_codes,65,8) ;
2663 l_code(20).code:=substr(l_ccr_data.sic_codes,73,8) ;
2664 l_code(21).code:=substr(l_ccr_data.sic_codes,81,8) ;
2665 l_code(22).code:=substr(l_ccr_data.sic_codes,89,8) ;
2666 l_code(23).code:=substr(l_ccr_data.sic_codes,97,8) ;
2667 l_code(24).code:=substr(l_ccr_data.sic_codes,105,8) ;
2668 l_code(25).code:=substr(l_ccr_data.sic_codes,113,8) ;
2669 l_code(26).code:=substr(l_ccr_data.sic_codes,121,8) ;
2670 l_code(27).code:=substr(l_ccr_data.sic_codes,129,8) ;
2671 l_code(28).code:=substr(l_ccr_data.sic_codes,137,8) ;
2672 l_code(29).code:=substr(l_ccr_data.sic_codes,145,8) ;
2673 l_code(30).code:=substr(l_ccr_data.sic_codes,153,8) ;
2674 l_code(31).code:=substr(l_ccr_data.naics_codes,1,6) ;
2675 l_code(32).code:=substr(l_ccr_data.naics_codes,7,6) ;
2676 l_code(33).code:=substr(l_ccr_data.naics_codes,13,6) ;
2677 l_code(34).code:=substr(l_ccr_data.naics_codes,19,6) ;
2678 l_code(35).code:=substr(l_ccr_data.naics_codes,25,6) ;
2679 l_code(36).code:=substr(l_ccr_data.naics_codes,31,6) ;
2680 l_code(37).code:=substr(l_ccr_data.naics_codes,37,6) ;
2681 l_code(38).code:=substr(l_ccr_data.naics_codes,43,6) ;
2682 l_code(39).code:=substr(l_ccr_data.naics_codes,49,6) ;
2683 l_code(40).code:=substr(l_ccr_data.naics_codes,55,6) ;
2684 l_code(41).code:=substr(l_ccr_data.naics_codes,61,6) ;
2685 l_code(42).code:=substr(l_ccr_data.naics_codes,67,6) ;
2686 l_code(43).code:=substr(l_ccr_data.naics_codes,73,6) ;
2687 l_code(44).code:=substr(l_ccr_data.naics_codes,79,6) ;
2688 l_code(45).code:=substr(l_ccr_data.naics_codes,85,6) ;
2689 l_code(46).code:=substr(l_ccr_data.naics_codes,91,6) ;
2690 l_code(47).code:=substr(l_ccr_data.naics_codes,97,6) ;
2691 l_code(48).code:=substr(l_ccr_data.naics_codes,103,6) ;
2692 l_code(49).code:=substr(l_ccr_data.naics_codes,109,6) ;
2693 l_code(50).code:=substr(l_ccr_data.naics_codes,115,6) ;
2694 l_code(51).code:=substr(l_ccr_data.fsc_codes,1,4) ;
2695 l_code(52).code:=substr(l_ccr_data.fsc_codes,5,4) ;
2696 l_code(53).code:=substr(l_ccr_data.fsc_codes,9,4) ;
2697 l_code(54).code:=substr(l_ccr_data.fsc_codes,13,4) ;
2698 l_code(55).code:=substr(l_ccr_data.fsc_codes,17,4) ;
2699 l_code(56).code:=substr(l_ccr_data.fsc_codes,21,4) ;
2700 l_code(57).code:=substr(l_ccr_data.fsc_codes,25,4) ;
2701 l_code(58).code:=substr(l_ccr_data.fsc_codes,29,4) ;
2702 l_code(59).code:=substr(l_ccr_data.fsc_codes,34,4) ;
2703 l_code(60).code:=substr(l_ccr_data.fsc_codes,37,4) ;
2704 l_code(61).code:=substr(l_ccr_data.psc_codes,1,4) ;
2705 l_code(62).code:=substr(l_ccr_data.psc_codes,5,4) ;
2706 l_code(63).code:=substr(l_ccr_data.psc_codes,9,4) ;
2707 l_code(64).code:=substr(l_ccr_data.psc_codes,13,4) ;
2708 l_code(65).code:=substr(l_ccr_data.psc_codes,17,4) ;
2709 l_code(66).code:=substr(l_ccr_data.psc_codes,21,4) ;
2710 l_code(67).code:=substr(l_ccr_data.psc_codes,25,4) ;
2711 l_code(68).code:=substr(l_ccr_data.psc_codes,29,4) ;
2712 l_code(69).code:=substr(l_ccr_data.psc_codes,33,4) ;
2713 l_code(70).code:=substr(l_ccr_data.psc_codes,37,4) ;*/
2714 l_code(71).code:=l_ccr_data.ORGANIZATIONAL_TYPE;
2715 l_code(72).code:=l_ccr_data.CORRESPONDENCE_FLAG ;
2716 l_code(73).code:=l_ccr_data.CORP_SECURITY_LEVEL ;
2717 l_code(74).code:=l_ccr_data.EMP_SECURITY_LEVEL ;
2718
2719
2720 /*l_code(1).rec_type:='B' ;
2721 l_code(2).rec_type:='B' ;
2722 l_code(3).rec_type:='B' ;
2723 l_code(4).rec_type:='B' ;
2724 l_code(5).rec_type:='B' ;
2725 l_code(6).rec_type:='B' ;
2726 l_code(7).rec_type:='B' ;
2727 l_code(8).rec_type:='B' ;
2728 l_code(9).rec_type:='B' ;
2729 l_code(10).rec_type:='B' ;
2730 l_code(11).rec_type:='S' ;
2731 l_code(12).rec_type:='S' ;
2732 l_code(13).rec_type:='S' ;
2733 l_code(14).rec_type:='S' ;
2734 l_code(15).rec_type:='S' ;
2735 l_code(16).rec_type:='S' ;
2736 l_code(17).rec_type:='S' ;
2737 l_code(18).rec_type:='S' ;
2738 l_code(19).rec_type:='S' ;
2739 l_code(20).rec_type:='S' ;
2740 l_code(21).rec_type:='S' ;
2741 l_code(22).rec_type:='S' ;
2742 l_code(23).rec_type:='S' ;
2743 l_code(24).rec_type:='S' ;
2744 l_code(25).rec_type:='S' ;
2745 l_code(26).rec_type:='S' ;
2746 l_code(27).rec_type:='S' ;
2747 l_code(28).rec_type:='S' ;
2748 l_code(29).rec_type:='S' ;
2749 l_code(30).rec_type:='S' ;
2750 l_code(31).rec_type:='N' ;
2751 l_code(32).rec_type:='N' ;
2752 l_code(33).rec_type:='N' ;
2753 l_code(34).rec_type:='N' ;
2754 l_code(35).rec_type:='N' ;
2755 l_code(36).rec_type:='N' ;
2756 l_code(37).rec_type:='N' ;
2757 l_code(38).rec_type:='N' ;
2758 l_code(39).rec_type:='N' ;
2759 l_code(40).rec_type:='N' ;
2760 l_code(41).rec_type:='N' ;
2761 l_code(42).rec_type:='N' ;
2762 l_code(43).rec_type:='N' ;
2763 l_code(44).rec_type:='N' ;
2764 l_code(45).rec_type:='N' ;
2765 l_code(46).rec_type:='N' ;
2766 l_code(47).rec_type:='N' ;
2767 l_code(48).rec_type:='N' ;
2768 l_code(49).rec_type:='N' ;
2769 l_code(50).rec_type:='N' ;
2770 l_code(51).rec_type:='F' ;
2771 l_code(52).rec_type:='F' ;
2772 l_code(53).rec_type:='F' ;
2773 l_code(54).rec_type:='F' ;
2774 l_code(55).rec_type:='F' ;
2775 l_code(56).rec_type:='F' ;
2776 l_code(57).rec_type:='F' ;
2777 l_code(58).rec_type:='F' ;
2778 l_code(59).rec_type:='F' ;
2779 l_code(60).rec_type:='F' ;
2780 l_code(61).rec_type:='P' ;
2781 l_code(62).rec_type:='P' ;
2782 l_code(63).rec_type:='P' ;
2783 l_code(64).rec_type:='P' ;
2784 l_code(65).rec_type:='P' ;
2785 l_code(66).rec_type:='P' ;
2786 l_code(67).rec_type:='P' ;
2787 l_code(68).rec_type:='P' ;
2788 l_code(69).rec_type:='P' ;
2789 l_code(70).rec_type:='P' ;*/
2790 l_code(71).rec_type:='O' ;
2791 l_code(72).rec_type:='C' ;
2792 l_code(73).rec_type:='CS' ;
2793 l_code(74).rec_type:='ES' ;
2794
2795 /*bug 3897523, no longer need to check codes against lookups.
2796 l_errbuf := 'calling - > find code ';
2797 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2798
2799 find_code(l_code);
2800 l_errbuf := 'after -> find code';
2801 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2802 */
2803 -- set the status as active
2804 l_status :='A';
2805
2806 --sthota
2807 begin
2808 select vendor_id into l_active_vendor_id from fv_ccr_vendors
2809 where DUNS = l_ccr_data.duns and plus_four is null and ccr_status in ('E');
2810
2811 if l_ccr_data.renewal_date > trunc(sysdate) then
2812
2813 if l_active_vendor_id is not null then
2814 l_errbuf := 'Found New Active Vendor Id:'||l_active_vendor_id||' For DUNS: '||l_ccr_data.duns;
2815 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2816
2817 l_active_vendor_exists := 0;
2818
2819 if l_vendor_cnt > 0 then
2820 for i in 1..l_vendor_cnt
2821 loop
2822 if vendor_ids(i) = l_active_vendor_id then
2823 l_active_vendor_exists := 1;
2824 end if;
2825 end loop;
2826 end if;
2827
2828 if l_active_vendor_exists <> 1 then
2829 l_vendor_cnt := l_vendor_cnt + 1;
2830 vendor_ids(l_vendor_cnt) := l_active_vendor_id;
2831 duns_ids(l_vendor_cnt) := l_ccr_data.duns;
2832 end if;
2833
2834 else
2835
2836 l_errbuf := 'Found No Vendor Id:'||l_active_vendor_id||' For New Active DUNS: '||l_ccr_data.duns;
2837 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2838
2839 end if; --if vendor id is null
2840 end if; --renewal date
2841
2842 exception when no_data_found then
2843 null;
2844 end;
2845
2846 IF l_ccr_data.plus_four is not null THEN
2847 BEGIN
2848 l_errbuf := 'Processing -> DUN+4 now '||l_ccr_data.plus_four;
2849 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2850
2851 SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv
2852 WHERE fcv.duns = l_ccr_data.duns
2853 AND fcv.plus_four= l_ccr_data.plus_four ;
2854 -- if this select does not return rows ,we need to update DUNS and duns+4 information
2855 l_errbuf := 'DUNS+4 exists';
2856 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2857
2858 --validate for the renewal date)
2859 IF (l_ccr_data.renewal_date < trunc(sysdate) ) THEN
2860 l_status:='E';
2861 END IF; --end of renewal date
2862
2863 --call to update the DUNS record
2864 l_errbuf := 'Updating DUNS+4 info ';
2865 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2866
2867 -- BUG 3839843
2868 IF (l_lbe_change <> l_ccr_data.legal_bus_name) THEN
2869
2870 FND_MESSAGE.set_NAME('FV','FV_CCR_LBE_CHANGED');
2871 message_text := FND_MESSAGE.get;
2872
2873 l_errbuf :='Legal Bus Name changed from '||l_lbe_change||' -> '||l_ccr_data.legal_bus_name;
2874 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
2875 insert_temp_data(3,null,message_text ,null,l_ccr_data.duns||l_ccr_data.plus_four,null,null);
2876
2877 END IF;
2878
2879
2880 update fv_ccr_vendors fcv set
2881 fcv.CCR_FLAG ='R' ,
2882 fcv.CCR_STATUS =l_status ,
2883 fcv.DUNS =l_ccr_data.DUNS ,
2884 fcv.PLUS_FOUR =l_ccr_data.PLUS_FOUR ,
2885 fcv.CAGE_CODE =l_ccr_data.CAGE_CODE ,
2886 fcv.EXTRACT_CODE =l_ccr_data.EXTRACT_CODE ,
2887 fcv.REGISTRATION_DATE =l_ccr_data.REGISTRATION_DATE ,
2888 fcv.RENEWAL_DATE =l_ccr_data.RENEWAL_DATE ,
2889 fcv.LEGAL_BUS_NAME =l_ccr_data.LEGAL_BUS_NAME ,
2890 fcv.DBA_NAME =l_ccr_data.DBA_NAME ,
2891 fcv.DIVISION_NAME =l_ccr_data.DIVISION_NAME ,
2892 fcv.DIVISION_NUMBER =l_ccr_data.DIVISION_NUMBER ,
2893 fcv.ST_ADDRESS1 =l_ccr_data.ST_ADDRESS1 ,
2894 fcv.ST_ADDRESS2 =l_ccr_data.ST_ADDRESS2 ,
2895 fcv.CITY =l_ccr_data.CITY ,
2896 fcv.STATE =l_ccr_data.STATE ,
2897 fcv.POSTAL_CODE =l_ccr_data.POSTAL_CODE ,
2898 --Bug8335551
2899 --fcv.COUNTRY =l_ccr_data.COUNTRY ,
2900 fcv.COUNTRY =get_territory_code(l_ccr_data.COUNTRY) ,
2901 fcv.BUSINESS_START_DATE =l_ccr_data.BUSINESS_START_DATE ,
2902 fcv.FISCAL_YR_CLOSE_DATE =l_ccr_data.FISCAL_YR_CLOSE_DATE ,
2903 fcv.WEB_SITE =l_ccr_data.WEB_SITE ,
2904 fcv.CREDIT_CARD_FLAG =l_ccr_data.CREDIT_CARD_FLAG ,
2905 fcv.MAIL_POC =l_ccr_data.MAIL_POC ,
2906 fcv.MAIL_ADD1 =l_ccr_data.MAIL_ADD1 ,
2907 fcv.MAIL_ADD2 =l_ccr_data.MAIL_ADD2 ,
2908 fcv.MAIL_CITY =l_ccr_data.MAIL_CITY ,
2909 fcv.MAIL_POSTAL_CODE =l_ccr_data.MAIL_POSTAL_CODE ,
2910 --Bug8335551
2911 --fcv.MAIL_COUNTRY =l_ccr_data.MAIL_COUNTRY ,
2912 fcv.MAIL_COUNTRY =get_territory_code(l_ccr_data.MAIL_COUNTRY) ,
2913 fcv.MAIL_STATE =l_ccr_data.MAIL_STATE ,
2914 fcv.PREV_BUS_POC =l_ccr_data.PREV_BUS_POC ,
2915 fcv.PREV_BUS_ADD1 =l_ccr_data.PREV_BUS_ADD1 ,
2916 fcv.PREV_BUS_ADD2 =l_ccr_data.PREV_BUS_ADD2 ,
2917 fcv.PREV_BUS_CITY =l_ccr_data.PREV_BUS_CITY ,
2918 fcv.PREV_BUS_POSTAL_CODE =l_ccr_data.PREV_BUS_POSTAL_CODE ,
2919 fcv.PREV_BUS_COUNTRY =l_ccr_data.PREV_BUS_COUNTRY ,
2920 fcv.PREV_BUS_STATE =l_ccr_data.PREV_BUS_STATE ,
2921 fcv.PARENT_POC =l_ccr_data.PARENT_POC ,
2922 fcv.PARENT_DUNS =l_ccr_data.PARENT_DUNS ,
2923 fcv.PARENT_ADD1 =l_ccr_data.PARENT_ADD1 ,
2924 fcv.PARENT_ADD2 =l_ccr_data.PARENT_ADD2 ,
2925 fcv.PARENT_CITY =l_ccr_data.PARENT_CITY ,
2926 fcv.PARENT_POSTAL_CODE =l_ccr_data.PARENT_POSTAL_CODE ,
2927 fcv.PARENT_COUNTRY =l_ccr_data.PARENT_COUNTRY ,
2928 fcv.PARENT_STATE =l_ccr_data.PARENT_STATE ,
2929 fcv.PARTY_PERF_POC =l_ccr_data.PARTY_PERF_POC ,
2930 fcv.PARTY_PERF_ADD1 =l_ccr_data.PARTY_PERF_ADD1 ,
2931 fcv.PARTY_PERF_ADD2 =l_ccr_data.PARTY_PERF_ADD2 ,
2932 fcv.PARTY_PERF_CITY =l_ccr_data.PARTY_PERF_CITY ,
2933 fcv.PARTY_PERF_POSTAL_CODE =l_ccr_data.PARTY_PERF_POSTAL_CODE ,
2934 fcv.PARTY_PERF_COUNTRY =l_ccr_data.PARTY_PERF_COUNTRY ,
2935 fcv.PARTY_PERF_STATE =l_ccr_data.PARTY_PERF_STATE ,
2936 fcv.GOV_PARENT_POC =l_ccr_data.GOV_PARENT_POC ,
2937 fcv.GOV_PARENT_ADD1 =l_ccr_data.GOV_PARENT_ADD1 ,
2938 fcv.GOV_PARENT_ADD2 =l_ccr_data.GOV_PARENT_ADD2 ,
2939 fcv.GOV_PARENT_CITY =l_ccr_data.GOV_PARENT_CITY ,
2940 fcv.GOV_PARENT_POSTAL_CODE =l_ccr_data.GOV_PARENT_POSTAL_CODE ,
2941 fcv.GOV_PARENT_COUNTRY =l_ccr_data.GOV_PARENT_COUNTRY ,
2942 fcv.GOV_PARENT_STATE =l_ccr_data.GOV_PARENT_STATE ,
2943 fcv.GOV_BUS_POC =l_ccr_data.GOV_BUS_POC ,
2944 fcv.GOV_BUS_ADD1 =l_ccr_data.GOV_BUS_ADD1 ,
2945 fcv.GOV_BUS_ADD2 =l_ccr_data.GOV_BUS_ADD2 ,
2946 fcv.GOV_BUS_CITY =l_ccr_data.GOV_BUS_CITY ,
2947 fcv.GOV_BUS_POSTAL_CODE =l_ccr_data.GOV_BUS_POSTAL_CODE ,
2948 fcv.GOV_BUS_COUNTRY =l_ccr_data.GOV_BUS_COUNTRY ,
2949 fcv.GOV_BUS_STATE =l_ccr_data.GOV_BUS_STATE ,
2950 fcv.GOV_BUS_US_PHONE =l_ccr_data.GOV_BUS_US_PHONE ,
2951 fcv.GOV_BUS_US_PHONE_EX =l_ccr_data.GOV_BUS_US_PHONE_EX ,
2952 fcv.GOV_BUS_NON_US_PHONE =l_ccr_data.GOV_BUS_NON_US_PHONE ,
2953 fcv.GOV_BUS_FAX =l_ccr_data.GOV_BUS_FAX ,
2954 fcv.GOV_BUS_EMAIL =l_ccr_data.GOV_BUS_EMAIL ,
2955 fcv.ALT_GOV_BUS_POC =l_ccr_data.ALT_GOV_BUS_POC ,
2956 fcv.ALT_GOV_BUS_ADD1 =l_ccr_data.ALT_GOV_BUS_ADD1 ,
2957 fcv.ALT_GOV_BUS_ADD2 =l_ccr_data.ALT_GOV_BUS_ADD2 ,
2958 fcv.ALT_GOV_BUS_CITY =l_ccr_data.ALT_GOV_BUS_CITY ,
2959 fcv.ALT_GOV_BUS_POSTAL_CODE =l_ccr_data.ALT_GOV_BUS_POSTAL_CODE ,
2960 fcv.ALT_GOV_BUS_COUNTRY =l_ccr_data.ALT_GOV_BUS_COUNTRY ,
2961 fcv.ALT_GOV_BUS_STATE =l_ccr_data.ALT_GOV_BUS_STATE ,
2962 fcv.ALT_GOV_BUS_US_PHONE =l_ccr_data.ALT_GOV_BUS_US_PHONE ,
2963 fcv.ALT_GOV_BUS_US_PHONE_EX =l_ccr_data.ALT_GOV_BUS_US_PHONE_EX ,
2964 fcv.ALT_GOV_BUS_NON_US_PHONE =l_ccr_data.ALT_GOV_BUS_NON_US_PHONE ,
2965 fcv.ALT_GOV_BUS_FAX =l_ccr_data.ALT_GOV_BUS_FAX ,
2966 fcv.ALT_GOV_BUS_EMAIL =l_ccr_data.ALT_GOV_BUS_EMAIL ,
2967 fcv.PAST_PERF_POC =l_ccr_data.PAST_PERF_POC ,
2968 fcv.PAST_PERF_ADD1 =l_ccr_data.PAST_PERF_ADD1 ,
2969 fcv.PAST_PERF_ADD2 =l_ccr_data.PAST_PERF_ADD2 ,
2970 fcv.PAST_PERF_CITY =l_ccr_data.PAST_PERF_CITY ,
2971 fcv.PAST_PERF_POSTAL_CODE =l_ccr_data.PAST_PERF_POSTAL_CODE ,
2972 fcv.PAST_PERF_COUNTRY =l_ccr_data.PAST_PERF_COUNTRY ,
2973 fcv.PAST_PERF_STATE =l_ccr_data.PAST_PERF_STATE ,
2974 fcv.PAST_PERF_US_PHONE =l_ccr_data.PAST_PERF_US_PHONE ,
2975 fcv.PAST_PERF_US_PHONE_EX =l_ccr_data.PAST_PERF_US_PHONE_EX ,
2976 fcv.PAST_PERF_NON_US_PHONE =l_ccr_data.PAST_PERF_NON_US_PHONE ,
2977 fcv.PAST_PERF_FAX =l_ccr_data.PAST_PERF_FAX ,
2978 fcv.PAST_PERF_EMAIL =l_ccr_data.PAST_PERF_EMAIL ,
2979 fcv.ALT_PAST_PERF_POC =l_ccr_data.ALT_PAST_PERF_POC ,
2980 fcv.ALT_PAST_PERF_ADD1 =l_ccr_data.ALT_PAST_PERF_ADD1 ,
2981 fcv.ALT_PAST_PERF_ADD2 =l_ccr_data.ALT_PAST_PERF_ADD2 ,
2982 fcv.ALT_PAST_PERF_CITY =l_ccr_data.ALT_PAST_PERF_CITY ,
2983 fcv.ALT_PAST_PERF_POSTAL_CODE =l_ccr_data.ALT_PAST_PERF_POSTAL_CODE ,
2984 fcv.ALT_PAST_PERF_COUNTRY =l_ccr_data.ALT_PAST_PERF_COUNTRY ,
2985 fcv.ALT_PAST_PERF_STATE =l_ccr_data.ALT_PAST_PERF_STATE ,
2986 fcv.ALT_PAST_PERF_US_PHONE =l_ccr_data.ALT_PAST_PERF_US_PHONE ,
2987 fcv.ALT_PAST_PERF_US_PHONE_EX =l_ccr_data.ALT_PAST_PERF_US_PHONE_EX ,
2988 fcv.ALT_PAST_PERF_NON_US_PHONE =l_ccr_data.ALT_PAST_PERF_NON_US_PHONE ,
2989 fcv.ALT_PAST_PERF_FAX =l_ccr_data.ALT_PAST_PERF_FAX ,
2990 fcv.ALT_PAST_PERF_EMAIL =l_ccr_data.ALT_PAST_PERF_EMAIL ,
2991 fcv.ELEC_BUS_POC =l_ccr_data.ELEC_BUS_POC ,
2992 fcv.ELEC_BUS_ADD1 =l_ccr_data.ELEC_BUS_ADD1 ,
2993 fcv.ELEC_BUS_ADD2 =l_ccr_data.ELEC_BUS_ADD2 ,
2994 fcv.ELEC_BUS_CITY =l_ccr_data.ELEC_BUS_CITY ,
2995 fcv.ELEC_BUS_POSTAL_CODE =l_ccr_data.ELEC_BUS_POSTAL_CODE ,
2996 fcv.ELEC_BUS_COUNTRY =l_ccr_data.ELEC_BUS_COUNTRY ,
2997 fcv.ELEC_BUS_STATE =l_ccr_data.ELEC_BUS_STATE ,
2998 fcv.ELEC_BUS_US_PHONE =l_ccr_data.ELEC_BUS_US_PHONE ,
2999 fcv.ELEC_BUS_US_PHONE_EX =l_ccr_data.ELEC_BUS_US_PHONE_EX ,
3000 fcv.ELEC_BUS_NON_US_PHONE =l_ccr_data.ELEC_BUS_NON_US_PHONE ,
3001 fcv.ELEC_BUS_FAX =l_ccr_data.ELEC_BUS_FAX ,
3002 fcv.ELEC_BUS_EMAIL =l_ccr_data.ELEC_BUS_EMAIL ,
3003 fcv.ALT_ELEC_BUS_POC =l_ccr_data.ALT_ELEC_BUS_POC ,
3004 fcv.ALT_ELEC_BUS_ADD1 =l_ccr_data.ALT_ELEC_BUS_ADD1 ,
3005 fcv.ALT_ELEC_BUS_ADD2 =l_ccr_data.ALT_ELEC_BUS_ADD2 ,
3006 fcv.ALT_ELEC_BUS_CITY =l_ccr_data.ALT_ELEC_BUS_CITY ,
3007 fcv.ALT_ELEC_BUS_POSTAL_CODE =l_ccr_data.ALT_ELEC_BUS_POSTAL_CODE ,
3008 fcv.ALT_ELEC_BUS_COUNTRY =l_ccr_data.ALT_ELEC_BUS_COUNTRY ,
3009 fcv.ALT_ELEC_BUS_STATE =l_ccr_data.ALT_ELEC_BUS_STATE ,
3010 fcv.ALT_ELEC_BUS_US_PHONE =l_ccr_data.ALT_ELEC_BUS_US_PHONE ,
3011 fcv.ALT_ELEC_BUS_US_PHONE_EX =l_ccr_data.ALT_ELEC_BUS_US_PHONE_EX ,
3012 fcv.ALT_ELEC_BUS_NON_US_PHONE =l_ccr_data.ALT_ELEC_BUS_NON_US_PHONE ,
3013 fcv.ALT_ELEC_BUS_FAX =l_ccr_data.ALT_ELEC_BUS_FAX ,
3014 fcv.ALT_ELEC_BUS_EMAIL =l_ccr_data.ALT_ELEC_BUS_EMAIL ,
3015 fcv.CERTIFIER_POC =l_ccr_data.CERTIFIER_POC ,
3016 fcv.CERTIFIER_US_PHONE =l_ccr_data.CERTIFIER_US_PHONE ,
3017 fcv.CERTIFIER_US_PHONE_EX =l_ccr_data.CERTIFIER_US_PHONE_EX ,
3018 fcv.CERTIFIER_NON_US_PHONE =l_ccr_data.CERTIFIER_NON_US_PHONE ,
3019 fcv.CERTIFIER_FAX =l_ccr_data.CERTIFIER_FAX ,
3020 fcv.CERTIFIER_EMAIL =l_ccr_data.CERTIFIER_EMAIL ,
3021 fcv.ALT_CERTIFIER_POC =l_ccr_data.ALT_CERTIFIER_POC ,
3022 fcv.ALT_CERTIFIER_US_PHONE =l_ccr_data.ALT_CERTIFIER_US_PHONE ,
3023 fcv.ALT_CERTIFIER_US_PHONE_EX =l_ccr_data.ALT_CERTIFIER_US_PHONE_EX ,
3024 fcv.ALT_CERTIFIER_NON_US_PHONE =l_ccr_data.ALT_CERTIFIER_NON_US_PHONE ,
3025 fcv.CORP_INFO_POC =l_ccr_data.CORP_INFO_POC ,
3026 fcv.CORP_INFO_US_PHONE =l_ccr_data.CORP_INFO_US_PHONE ,
3027 fcv.CORP_INFO_US_PHONE_EX =l_ccr_data.CORP_INFO_US_PHONE_EX ,
3028 fcv.CORP_INFO_NON_US_PHONE =l_ccr_data.CORP_INFO_NON_US_PHONE ,
3029 fcv.CORP_INFO_FAX =l_ccr_data.CORP_INFO_FAX ,
3030 fcv.CORP_INFO_EMAIL =l_ccr_data.CORP_INFO_EMAIL ,
3031 fcv.OWNER_INFO_POC =l_ccr_data.OWNER_INFO_POC ,
3032 fcv.OWNER_INFO_US_PHONE =l_ccr_data.OWNER_INFO_US_PHONE ,
3033 fcv.OWNER_INFO_US_PHONE_EX =l_ccr_data.OWNER_INFO_US_PHONE_EX ,
3034 fcv.OWNER_INFO_NON_US_PHONE =l_ccr_data.OWNER_INFO_NON_US_PHONE ,
3035 fcv.OWNER_INFO_FAX =l_ccr_data.OWNER_INFO_FAX ,
3036 fcv.OWNER_INFO_EMAIL =l_ccr_data.OWNER_INFO_EMAIL ,
3037 fcv.EDI =l_ccr_data.EDI ,
3038 fcv.TAXPAYER_ID =l_ccr_data.TAXPAYER_ID ,
3039 fcv.AVG_NUM_EMPLOYEES =l_ccr_data.AVG_NUM_EMPLOYEES ,
3040 fcv.ANNUAL_REVENUE =l_ccr_data.ANNUAL_REVENUE ,
3041 fcv.SOCIAL_SECURITY_NUMBER =l_ccr_data.SOCIAL_SECURITY_NUMBER ,
3042 fcv.FINANCIAL_INSTITUTE =l_ccr_data.FINANCIAL_INSTITUTE ,
3043 fcv.BANK_ACCT_NUMBER =l_ccr_data.BANK_ACCT_NUMBER ,
3044 fcv.ABA_ROUTING =l_ccr_data.ABA_ROUTING ,
3045 fcv.BANK_ACCT_TYPE =l_ccr_data.BANK_ACCT_TYPE ,
3046 fcv.LOCKBOX_NUMBER =l_ccr_data.LOCKBOX_NUMBER ,
3047 fcv.AUTHORIZATION_DATE =l_ccr_data.AUTHORIZATION_DATE ,
3048 fcv.EFT_WAIVER =l_ccr_data.EFT_WAIVER ,
3049 fcv.ACH_US_PHONE =l_ccr_data.ACH_US_PHONE ,
3050 fcv.ACH_NON_US_PHONE =l_ccr_data.ACH_NON_US_PHONE ,
3051 fcv.ACH_FAX =l_ccr_data.ACH_FAX ,
3052 fcv.ACH_EMAIL =l_ccr_data.ACH_EMAIL ,
3053 fcv.REMIT_POC =l_ccr_data.REMIT_POC ,
3054 fcv.REMIT_ADD1 =l_ccr_data.REMIT_ADD1 ,
3055 fcv.REMIT_ADD2 =l_ccr_data.REMIT_ADD2 ,
3056 fcv.REMIT_CITY =l_ccr_data.REMIT_CITY ,
3057 fcv.REMIT_STATE =l_ccr_data.REMIT_STATE ,
3058 fcv.REMIT_POSTAL_CODE =l_ccr_data.REMIT_POSTAL_CODE ,
3059 --Bug8335551
3060 --fcv.REMIT_COUNTRY =l_ccr_data.REMIT_COUNTRY ,
3061 fcv.REMIT_COUNTRY =get_territory_code(l_ccr_data.REMIT_COUNTRY) ,
3062 fcv.AR_POC =l_ccr_data.AR_POC ,
3063 fcv.AR_US_PHONE =l_ccr_data.AR_US_PHONE ,
3064 fcv.AR_US_PHONE_EX =l_ccr_data.AR_US_PHONE_EX ,
3065 fcv.AR_NON_US_PHONE =l_ccr_data.AR_NON_US_PHONE ,
3066 fcv.AR_FAX =l_ccr_data.AR_FAX ,
3067 fcv.AR_EMAIL =l_ccr_data.AR_EMAIL ,
3068 fcv.MPIN =l_ccr_data.MPIN ,
3069 fcv.EDI_COORDINATOR =l_ccr_data.EDI_COORDINATOR ,
3070 fcv.EDI_US_PHONE =l_ccr_data.EDI_US_PHONE ,
3071 fcv.EDI_US_PHONE_EX =l_ccr_data.EDI_US_PHONE_EX ,
3072 fcv.EDI_NON_US_PHONE =l_ccr_data.EDI_NON_US_PHONE ,
3073 fcv.EDI_FAX =l_ccr_data.EDI_FAX ,
3074 fcv.EDI_EMAIL =l_ccr_data.EDI_EMAIL ,
3075 fcv.STATE_OF_INC =l_ccr_data.state_of_inc ,
3076 fcv.COUNTRY_OF_INC =l_ccr_data.country_of_inc ,
3077 fcv.organizational_type =l_code(71).code ,
3078 fcv.correspondence_flag =l_code(72).code ,
3079 fcv.corp_security_level =l_code(73).code ,
3080 fcv.emp_security_level =l_code(74).code ,
3081 fcv.last_update_date =sysdate,
3082 fcv.last_updated_by =l_user_id,
3083 fcv.last_import_date =l_ccr_data.file_date
3084 ,fcv.AUSTIN_TETRA_NUMBER =l_ccr_data.AUSTIN_TETRA_NUMBER
3085 ,fcv.AUSTIN_TETRA_PARENT_NUMBER =l_ccr_data.AUSTIN_TETRA_PARENT_NUMBER
3086 ,fcv.AUSTIN_TETRA_ULTIMATE_NUMBER =l_ccr_data.AUSTIN_TETRA_ULTIMATE_NUMBER
3087 ,fcv.AUSTIN_TETRA_PCARD_FLAG =l_ccr_data.AUSTIN_TETRA_PCARD_FLAG
3088 ,fcv.DNB_MONITOR_LAST_UPDATED =l_ccr_data.DNB_MONITOR_LAST_UPDATED
3089 ,fcv.DNB_MONITOR_STATUS =l_ccr_data.DNB_MONITOR_STATUS
3090 ,fcv.DNB_MONITOR_CORP_NAME =l_ccr_data.DNB_MONITOR_CORP_NAME
3091 ,fcv.DNB_MONITOR_DBA =l_ccr_data.DNB_MONITOR_DBA
3092 ,fcv.DNB_MONITOR_ST_ADD1 =l_ccr_data.DNB_MONITOR_ST_ADD1
3093 ,fcv.DNB_MONITOR_ST_ADD2 =l_ccr_data.DNB_MONITOR_ST_ADD2
3094 ,fcv.DNB_MONITOR_CITY =l_ccr_data.DNB_MONITOR_CITY
3095 ,fcv.DNB_MONITOR_POSTAL_CODE =l_ccr_data.DNB_MONITOR_POSTAL_CODE
3096 ,fcv.DNB_MONITOR_COUNTRY_CODE =l_ccr_data.DNB_MONITOR_COUNTRY_CODE
3097 ,fcv.DNB_MONITOR_STATE =l_ccr_data.DNB_MONITOR_STATE
3098 ,fcv.HQ_PARENT_POC =l_ccr_data.HQ_PARENT_POC
3099 ,fcv.PAYMENT_TYPE =l_ccr_data.PAYMENT_TYPE
3100 ,fcv.ANNUAL_RECEIPTS =l_ccr_data.ANNUAL_RECEIPTS
3101 ,fcv.DOMESTIC_PARENT_POC =l_ccr_data.DOMESTIC_PARENT_POC
3102 ,fcv.DOMESTIC_PARENT_DUNS =l_ccr_data.DOMESTIC_PARENT_DUNS
3103 ,fcv.DOMESTIC_PARENT_ADD1 =l_ccr_data.DOMESTIC_PARENT_ADD1
3104 ,fcv.DOMESTIC_PARENT_ADD2 =l_ccr_data.DOMESTIC_PARENT_ADD2
3105 ,fcv.DOMESTIC_PARENT_CITY =l_ccr_data.DOMESTIC_PARENT_CITY
3106 ,fcv.DOMESTIC_PARENT_POSTAL_CODE =l_ccr_data.DOMESTIC_PARENT_POSTAL_CODE
3107 ,fcv.DOMESTIC_PARENT_COUNTRY =l_ccr_data.DOMESTIC_PARENT_COUNTRY
3108 ,fcv.DOMESTIC_PARENT_STATE =l_ccr_data.DOMESTIC_PARENT_STATE
3109 ,fcv.DOMESTIC_PARENT_PHONE =l_ccr_data.DOMESTIC_PARENT_PHONE
3110 ,fcv.GLOBAL_PARENT_POC =l_ccr_data.GLOBAL_PARENT_POC
3111 ,fcv.GLOBAL_PARENT_DUNS =l_ccr_data.GLOBAL_PARENT_DUNS
3112 ,fcv.GLOBAL_PARENT_ADD1 =l_ccr_data.GLOBAL_PARENT_ADD1
3113 ,fcv.GLOBAL_PARENT_ADD2 =l_ccr_data.GLOBAL_PARENT_ADD2
3114 ,fcv.GLOBAL_PARENT_CITY =l_ccr_data.GLOBAL_PARENT_CITY
3115 ,fcv.GLOBAL_PARENT_POSTAL_CODE =l_ccr_data.GLOBAL_PARENT_POSTAL_CODE
3116 ,fcv.GLOBAL_PARENT_COUNTRY =l_ccr_data.GLOBAL_PARENT_COUNTRY
3117 ,fcv.GLOBAL_PARENT_STATE =l_ccr_data.GLOBAL_PARENT_STATE
3118 ,fcv.GLOBAL_PARENT_PHONE =l_ccr_data.GLOBAL_PARENT_PHONE
3119 WHERE fcv.duns= l_ccr_data.duns
3120 AND nvl(fcv.plus_four,-99)= nvl(l_ccr_data.plus_four,-99) ;
3121 l_errbuf := 'Updating DUNS+4 info - done ';
3122 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3123
3124 --add into the plus four processed section of the report
3125 insert_temp_data(1,l_ccr_data.duns||l_ccr_data.plus_four,l_ccr_data.legal_bus_name,l_ccr_data.cage_code,nvl(l_ccr_data.taxpayer_id,l_ccr_data.social_security_number),l_status,null);
3126
3127 exception
3128 when no_data_found then
3129 l_errbuf := 'No data found for duns plus four ';
3130 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3131
3132 -- This case DUns+4 doesn ot exist
3133 l_errbuf := 'DUNS+4 does not exist in FV_CCR_VENDORS';
3134 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3135
3136 IF (p_xml_import = 'N' OR p_insert_data = 'Y') THEN -- bug 3931251
3137
3138 IF (l_ccr_data.extract_code ='3') THEN
3139 l_errbuf := 'Error - the DUNS+4 does not exist in FV_CCR_VENDORS';
3140 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3141 ELSE
3142
3143 --validate for the renewal date)
3144 IF (l_ccr_data.renewal_date < trunc(sysdate) ) THEN
3145 l_status:='E';
3146 ELSE
3147 l_status :='A';
3148 END IF; -- end of renewal date val
3149
3150
3151 --call procedureto insert duns+4 info
3152 l_errbuf := 'Insert DUNS+4' || l_ccr_data.plus_four;
3153 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3154 INSERT INTO FV_CCR_VENDORS (
3155 CCR_ID ,
3156 ENABLED ,
3157 CCR_FLAG ,
3158 CCR_STATUS ,
3159 DUNS ,
3160 PLUS_FOUR ,
3161 CAGE_CODE ,
3162 EXTRACT_CODE ,
3163 REGISTRATION_DATE ,
3164 RENEWAL_DATE ,
3165 LEGAL_BUS_NAME ,
3166 DBA_NAME ,
3167 DIVISION_NAME ,
3168 DIVISION_NUMBER ,
3169 ST_ADDRESS1 ,
3170 ST_ADDRESS2 ,
3171 CITY ,
3172 STATE ,
3173 POSTAL_CODE ,
3174 COUNTRY ,
3175 BUSINESS_START_DATE ,
3176 FISCAL_YR_CLOSE_DATE ,
3177 CORP_SECURITY_LEVEL ,
3178 EMP_SECURITY_LEVEL ,
3179 WEB_SITE ,
3180 CREDIT_CARD_FLAG ,
3181 CORRESPONDENCE_FLAG ,
3182 MAIL_POC ,
3183 MAIL_ADD1 ,
3184 MAIL_ADD2 ,
3185 MAIL_CITY ,
3186 MAIL_POSTAL_CODE ,
3187 MAIL_COUNTRY ,
3188 MAIL_STATE ,
3189 PREV_BUS_POC ,
3190 PREV_BUS_ADD1 ,
3191 PREV_BUS_ADD2 ,
3192 PREV_BUS_CITY ,
3193 PREV_BUS_POSTAL_CODE ,
3194 PREV_BUS_COUNTRY ,
3195 PREV_BUS_STATE ,
3196 PARENT_POC ,
3197 PARENT_DUNS ,
3198 PARENT_ADD1 ,
3199 PARENT_ADD2 ,
3200 PARENT_CITY ,
3201 PARENT_POSTAL_CODE ,
3202 PARENT_COUNTRY ,
3203 PARENT_STATE ,
3204 PARTY_PERF_POC ,
3205 PARTY_PERF_ADD1 ,
3206 PARTY_PERF_ADD2 ,
3207 PARTY_PERF_CITY ,
3208 PARTY_PERF_POSTAL_CODE ,
3209 PARTY_PERF_COUNTRY ,
3210 PARTY_PERF_STATE ,
3211 GOV_PARENT_POC ,
3212 GOV_PARENT_ADD1 ,
3213 GOV_PARENT_ADD2 ,
3214 GOV_PARENT_CITY ,
3215 GOV_PARENT_POSTAL_CODE ,
3216 GOV_PARENT_COUNTRY ,
3217 GOV_PARENT_STATE ,
3218 GOV_BUS_POC ,
3219 GOV_BUS_ADD1 ,
3220 GOV_BUS_ADD2 ,
3221 GOV_BUS_CITY ,
3222 GOV_BUS_POSTAL_CODE ,
3223 GOV_BUS_COUNTRY ,
3224 GOV_BUS_STATE ,
3225 GOV_BUS_US_PHONE ,
3226 GOV_BUS_US_PHONE_EX ,
3227 GOV_BUS_NON_US_PHONE ,
3228 GOV_BUS_FAX ,
3229 GOV_BUS_EMAIL ,
3230 ALT_GOV_BUS_POC ,
3231 ALT_GOV_BUS_ADD1 ,
3232 ALT_GOV_BUS_ADD2 ,
3233 ALT_GOV_BUS_CITY ,
3234 ALT_GOV_BUS_POSTAL_CODE ,
3235 ALT_GOV_BUS_COUNTRY ,
3236 ALT_GOV_BUS_STATE ,
3237 ALT_GOV_BUS_US_PHONE ,
3238 ALT_GOV_BUS_US_PHONE_EX ,
3239 ALT_GOV_BUS_NON_US_PHONE ,
3240 ALT_GOV_BUS_FAX ,
3241 ALT_GOV_BUS_EMAIL ,
3242 PAST_PERF_POC ,
3243 PAST_PERF_ADD1 ,
3244 PAST_PERF_ADD2 ,
3245 PAST_PERF_CITY ,
3246 PAST_PERF_POSTAL_CODE ,
3247 PAST_PERF_COUNTRY ,
3248 PAST_PERF_STATE ,
3249 PAST_PERF_US_PHONE ,
3250 PAST_PERF_US_PHONE_EX ,
3251 PAST_PERF_NON_US_PHONE ,
3252 PAST_PERF_FAX ,
3253 PAST_PERF_EMAIL ,
3254 ALT_PAST_PERF_POC ,
3255 ALT_PAST_PERF_ADD1 ,
3256 ALT_PAST_PERF_ADD2 ,
3257 ALT_PAST_PERF_CITY ,
3258 ALT_PAST_PERF_POSTAL_CODE ,
3259 ALT_PAST_PERF_COUNTRY ,
3260 ALT_PAST_PERF_STATE ,
3261 ALT_PAST_PERF_US_PHONE ,
3262 ALT_PAST_PERF_US_PHONE_EX ,
3263 ALT_PAST_PERF_NON_US_PHONE ,
3264 ALT_PAST_PERF_FAX ,
3265 ALT_PAST_PERF_EMAIL ,
3266 ELEC_BUS_POC ,
3267 ELEC_BUS_ADD1 ,
3268 ELEC_BUS_ADD2 ,
3269 ELEC_BUS_CITY ,
3270 ELEC_BUS_POSTAL_CODE ,
3271 ELEC_BUS_COUNTRY ,
3272 ELEC_BUS_STATE ,
3273 ELEC_BUS_US_PHONE ,
3274 ELEC_BUS_US_PHONE_EX ,
3275 ELEC_BUS_NON_US_PHONE ,
3276 ELEC_BUS_FAX ,
3277 ELEC_BUS_EMAIL ,
3278 ALT_ELEC_BUS_POC ,
3279 ALT_ELEC_BUS_ADD1 ,
3280 ALT_ELEC_BUS_ADD2 ,
3281 ALT_ELEC_BUS_CITY ,
3282 ALT_ELEC_BUS_POSTAL_CODE ,
3283 ALT_ELEC_BUS_COUNTRY ,
3284 ALT_ELEC_BUS_STATE ,
3285 ALT_ELEC_BUS_US_PHONE ,
3286 ALT_ELEC_BUS_US_PHONE_EX ,
3287 ALT_ELEC_BUS_NON_US_PHONE ,
3288 ALT_ELEC_BUS_FAX ,
3289 ALT_ELEC_BUS_EMAIL ,
3290 CERTIFIER_POC ,
3291 CERTIFIER_US_PHONE ,
3292 CERTIFIER_US_PHONE_EX ,
3293 CERTIFIER_NON_US_PHONE ,
3294 CERTIFIER_FAX ,
3295 CERTIFIER_EMAIL ,
3296 ALT_CERTIFIER_POC ,
3297 ALT_CERTIFIER_US_PHONE ,
3298 ALT_CERTIFIER_US_PHONE_EX ,
3299 ALT_CERTIFIER_NON_US_PHONE ,
3300 CORP_INFO_POC ,
3301 CORP_INFO_US_PHONE ,
3302 CORP_INFO_US_PHONE_EX ,
3303 CORP_INFO_NON_US_PHONE ,
3304 CORP_INFO_FAX ,
3305 CORP_INFO_EMAIL ,
3306 OWNER_INFO_POC ,
3307 OWNER_INFO_US_PHONE ,
3308 OWNER_INFO_US_PHONE_EX ,
3309 OWNER_INFO_NON_US_PHONE ,
3310 OWNER_INFO_FAX ,
3311 OWNER_INFO_EMAIL ,
3312 EDI ,
3313 TAXPAYER_ID ,
3314 AVG_NUM_EMPLOYEES ,
3315 ANNUAL_REVENUE ,
3316 SOCIAL_SECURITY_NUMBER ,
3317 FINANCIAL_INSTITUTE ,
3318 BANK_ACCT_NUMBER ,
3319 ABA_ROUTING ,
3320 BANK_ACCT_TYPE ,
3321 LOCKBOX_NUMBER ,
3322 AUTHORIZATION_DATE ,
3323 EFT_WAIVER ,
3324 ACH_US_PHONE ,
3325 ACH_NON_US_PHONE ,
3326 ACH_FAX ,
3327 ACH_EMAIL ,
3328 REMIT_POC ,
3329 REMIT_ADD1 ,
3330 REMIT_ADD2 ,
3331 REMIT_CITY ,
3332 REMIT_STATE ,
3333 REMIT_POSTAL_CODE ,
3334 REMIT_COUNTRY ,
3335 AR_POC ,
3336 AR_US_PHONE ,
3337 AR_US_PHONE_EX ,
3338 AR_NON_US_PHONE ,
3339 AR_FAX ,
3340 AR_EMAIL ,
3341 MPIN ,
3342 EDI_COORDINATOR ,
3343 EDI_US_PHONE ,
3344 EDI_US_PHONE_EX ,
3345 EDI_NON_US_PHONE ,
3346 EDI_FAX ,
3347 EDI_EMAIL ,
3348 LAST_UPDATE_DATE ,
3349 LAST_UPDATED_BY ,
3350 last_import_date ,
3351 ALT_CERTIFIER_FAX ,
3352 ALT_CERTIFIER_EMAIL ,
3353 CREATION_DATE ,
3354 CREATED_BY ,
3355 LAST_UPDATE_LOGIN ,
3356 STATE_OF_INC,
3357 COUNTRY_OF_INC,
3358 -- Added for bug 6339382
3359 ORGANIZATIONAL_TYPE
3360 ,AUSTIN_TETRA_NUMBER
3361 ,AUSTIN_TETRA_PARENT_NUMBER
3362 ,AUSTIN_TETRA_ULTIMATE_NUMBER
3363 ,AUSTIN_TETRA_PCARD_FLAG
3364 ,DNB_MONITOR_LAST_UPDATED
3365 ,DNB_MONITOR_STATUS
3366 ,DNB_MONITOR_CORP_NAME
3367 ,DNB_MONITOR_DBA
3368 ,DNB_MONITOR_ST_ADD1
3369 ,DNB_MONITOR_ST_ADD2
3370 ,DNB_MONITOR_CITY
3371 ,DNB_MONITOR_POSTAL_CODE
3372 ,DNB_MONITOR_COUNTRY_CODE
3373 ,DNB_MONITOR_STATE
3374 ,HQ_PARENT_POC
3375 ,PAYMENT_TYPE
3376 ,ANNUAL_RECEIPTS
3377 ,DOMESTIC_PARENT_POC
3378 ,DOMESTIC_PARENT_DUNS
3379 ,DOMESTIC_PARENT_ADD1
3380 ,DOMESTIC_PARENT_ADD2
3381 ,DOMESTIC_PARENT_CITY
3382 ,DOMESTIC_PARENT_POSTAL_CODE
3383 ,DOMESTIC_PARENT_COUNTRY
3384 ,DOMESTIC_PARENT_STATE
3385 ,DOMESTIC_PARENT_PHONE
3386 ,GLOBAL_PARENT_POC
3387 ,GLOBAL_PARENT_DUNS
3388 ,GLOBAL_PARENT_ADD1
3389 ,GLOBAL_PARENT_ADD2
3390 ,GLOBAL_PARENT_CITY
3391 ,GLOBAL_PARENT_POSTAL_CODE
3392 ,GLOBAL_PARENT_COUNTRY
3393 ,GLOBAL_PARENT_STATE
3394 ,GLOBAL_PARENT_PHONE
3395
3396 )
3397 SELECT FV_CCR_VENDORS_S.nextval ,'Y','R',l_status,
3398 DUNS ,
3399 PLUS_FOUR ,
3400 CAGE_CODE ,
3401 EXTRACT_CODE ,
3402 REGISTRATION_DATE ,
3403 RENEWAL_DATE ,
3404 LEGAL_BUS_NAME ,
3405 DBA_NAME ,
3406 DIVISION_NAME ,
3407 DIVISION_NUMBER ,
3408 ST_ADDRESS1 ,
3409 ST_ADDRESS2 ,
3410 CITY ,
3411 STATE ,
3412 POSTAL_CODE ,
3413 --Bug8335551
3414 --COUNTRY ,
3415 get_territory_code(COUNTRY) ,
3416 BUSINESS_START_DATE ,
3417 FISCAL_YR_CLOSE_DATE ,
3418 CORP_SECURITY_LEVEL ,
3419 EMP_SECURITY_LEVEL ,
3420 WEB_SITE ,
3421 CREDIT_CARD_FLAG ,
3422 CORRESPONDENCE_FLAG ,
3423 MAIL_POC ,
3424 MAIL_ADD1 ,
3425 MAIL_ADD2 ,
3426 MAIL_CITY ,
3427 MAIL_POSTAL_CODE ,
3428 --Bug8335551
3429 --MAIL_COUNTRY ,
3430 get_territory_code(MAIL_COUNTRY) ,
3431 MAIL_STATE ,
3432 PREV_BUS_POC ,
3433 PREV_BUS_ADD1 ,
3434 PREV_BUS_ADD2 ,
3435 PREV_BUS_CITY ,
3436 PREV_BUS_POSTAL_CODE ,
3437 PREV_BUS_COUNTRY ,
3438 PREV_BUS_STATE ,
3439 PARENT_POC ,
3440 PARENT_DUNS ,
3441 PARENT_ADD1 ,
3442 PARENT_ADD2 ,
3443 PARENT_CITY ,
3444 PARENT_POSTAL_CODE ,
3445 PARENT_COUNTRY ,
3446 PARENT_STATE ,
3447 PARTY_PERF_POC ,
3448 PARTY_PERF_ADD1 ,
3449 PARTY_PERF_ADD2 ,
3450 PARTY_PERF_CITY ,
3451 PARTY_PERF_POSTAL_CODE ,
3452 PARTY_PERF_COUNTRY ,
3453 PARTY_PERF_STATE ,
3454 GOV_PARENT_POC ,
3455 GOV_PARENT_ADD1 ,
3456 GOV_PARENT_ADD2 ,
3457 GOV_PARENT_CITY ,
3458 GOV_PARENT_POSTAL_CODE ,
3459 GOV_PARENT_COUNTRY ,
3460 GOV_PARENT_STATE ,
3461 GOV_BUS_POC ,
3462 GOV_BUS_ADD1 ,
3463 GOV_BUS_ADD2 ,
3464 GOV_BUS_CITY ,
3465 GOV_BUS_POSTAL_CODE ,
3466 GOV_BUS_COUNTRY ,
3467 GOV_BUS_STATE ,
3468 GOV_BUS_US_PHONE ,
3469 GOV_BUS_US_PHONE_EX ,
3470 GOV_BUS_NON_US_PHONE ,
3471 GOV_BUS_FAX ,
3472 GOV_BUS_EMAIL ,
3473 ALT_GOV_BUS_POC ,
3474 ALT_GOV_BUS_ADD1 ,
3475 ALT_GOV_BUS_ADD2 ,
3476 ALT_GOV_BUS_CITY ,
3477 ALT_GOV_BUS_POSTAL_CODE ,
3478 ALT_GOV_BUS_COUNTRY ,
3479 ALT_GOV_BUS_STATE ,
3480 ALT_GOV_BUS_US_PHONE ,
3481 ALT_GOV_BUS_US_PHONE_EX ,
3482 ALT_GOV_BUS_NON_US_PHONE ,
3483 ALT_GOV_BUS_FAX ,
3484 ALT_GOV_BUS_EMAIL ,
3485 PAST_PERF_POC ,
3486 PAST_PERF_ADD1 ,
3487 PAST_PERF_ADD2 ,
3488 PAST_PERF_CITY ,
3489 PAST_PERF_POSTAL_CODE ,
3490 PAST_PERF_COUNTRY ,
3491 PAST_PERF_STATE ,
3492 PAST_PERF_US_PHONE ,
3493 PAST_PERF_US_PHONE_EX ,
3494 PAST_PERF_NON_US_PHONE ,
3495 PAST_PERF_FAX ,
3496 PAST_PERF_EMAIL ,
3497 ALT_PAST_PERF_POC ,
3498 ALT_PAST_PERF_ADD1 ,
3499 ALT_PAST_PERF_ADD2 ,
3500 ALT_PAST_PERF_CITY ,
3501 ALT_PAST_PERF_POSTAL_CODE ,
3502 ALT_PAST_PERF_COUNTRY ,
3503 ALT_PAST_PERF_STATE ,
3504 ALT_PAST_PERF_US_PHONE ,
3505 ALT_PAST_PERF_US_PHONE_EX ,
3506 ALT_PAST_PERF_NON_US_PHONE ,
3507 ALT_PAST_PERF_FAX ,
3508 ALT_PAST_PERF_EMAIL ,
3509 ELEC_BUS_POC ,
3510 ELEC_BUS_ADD1 ,
3511 ELEC_BUS_ADD2 ,
3512 ELEC_BUS_CITY ,
3513 ELEC_BUS_POSTAL_CODE ,
3514 ELEC_BUS_COUNTRY ,
3515 ELEC_BUS_STATE ,
3516 ELEC_BUS_US_PHONE ,
3517 ELEC_BUS_US_PHONE_EX ,
3518 ELEC_BUS_NON_US_PHONE ,
3519 ELEC_BUS_FAX ,
3520 ELEC_BUS_EMAIL ,
3521 ALT_ELEC_BUS_POC ,
3522 ALT_ELEC_BUS_ADD1 ,
3523 ALT_ELEC_BUS_ADD2 ,
3524 ALT_ELEC_BUS_CITY ,
3525 ALT_ELEC_BUS_POSTAL_CODE ,
3526 ALT_ELEC_BUS_COUNTRY ,
3527 ALT_ELEC_BUS_STATE ,
3528 ALT_ELEC_BUS_US_PHONE ,
3529 ALT_ELEC_BUS_US_PHONE_EX ,
3530 ALT_ELEC_BUS_NON_US_PHONE ,
3531 ALT_ELEC_BUS_FAX ,
3532 ALT_ELEC_BUS_EMAIL ,
3533 CERTIFIER_POC ,
3534 CERTIFIER_US_PHONE ,
3535 CERTIFIER_US_PHONE_EX ,
3536 CERTIFIER_NON_US_PHONE ,
3537 CERTIFIER_FAX ,
3538 CERTIFIER_EMAIL ,
3539 ALT_CERTIFIER_POC ,
3540 ALT_CERTIFIER_US_PHONE ,
3541 ALT_CERTIFIER_US_PHONE_EX ,
3542 ALT_CERTIFIER_NON_US_PHONE ,
3543 CORP_INFO_POC ,
3544 CORP_INFO_US_PHONE ,
3545 CORP_INFO_US_PHONE_EX ,
3546 CORP_INFO_NON_US_PHONE ,
3547 CORP_INFO_FAX ,
3548 CORP_INFO_EMAIL ,
3549 OWNER_INFO_POC ,
3550 OWNER_INFO_US_PHONE ,
3551 OWNER_INFO_US_PHONE_EX ,
3552 OWNER_INFO_NON_US_PHONE ,
3553 OWNER_INFO_FAX ,
3554 OWNER_INFO_EMAIL ,
3555 EDI ,
3556 TAXPAYER_ID ,
3557 AVG_NUM_EMPLOYEES ,
3558 ANNUAL_REVENUE ,
3559 SOCIAL_SECURITY_NUMBER ,
3560 FINANCIAL_INSTITUTE ,
3561 BANK_ACCT_NUMBER ,
3562 ABA_ROUTING ,
3563 BANK_ACCT_TYPE ,
3564 LOCKBOX_NUMBER ,
3565 AUTHORIZATION_DATE ,
3566 EFT_WAIVER ,
3567 ACH_US_PHONE ,
3568 ACH_NON_US_PHONE ,
3569 ACH_FAX ,
3570 ACH_EMAIL ,
3571 REMIT_POC ,
3572 REMIT_ADD1 ,
3573 REMIT_ADD2 ,
3574 REMIT_CITY ,
3575 REMIT_STATE ,
3576 REMIT_POSTAL_CODE ,
3577 --Bug8335551
3578 --REMIT_COUNTRY ,
3579 get_territory_code(REMIT_COUNTRY) ,
3580 AR_POC ,
3581 AR_US_PHONE ,
3582 AR_US_PHONE_EX ,
3583 AR_NON_US_PHONE ,
3584 AR_FAX ,
3585 AR_EMAIL ,
3586 MPIN ,
3587 EDI_COORDINATOR ,
3588 EDI_US_PHONE ,
3589 EDI_US_PHONE_EX ,
3590 EDI_NON_US_PHONE ,
3591 EDI_FAX ,
3592 EDI_EMAIL
3593 ,sysdate ,
3594 l_user_id,
3595 file_date ,
3596 ALT_CERTIFIER_FAX ,
3597 ALT_CERTIFIER_EMAIL,
3598 sysdate,
3599 l_user_id,
3600 l_user_id ,
3601 State_of_inc,
3602 COUNTRY_OF_INC,
3603 -- Added for bug 6339382
3604 l_code(71).code
3605 ,AUSTIN_TETRA_NUMBER
3606 ,AUSTIN_TETRA_PARENT_NUMBER
3607 ,AUSTIN_TETRA_ULTIMATE_NUMBER
3608 ,AUSTIN_TETRA_PCARD_FLAG
3609 ,DNB_MONITOR_LAST_UPDATED
3610 ,DNB_MONITOR_STATUS
3611 ,DNB_MONITOR_CORP_NAME
3612 ,DNB_MONITOR_DBA
3613 ,DNB_MONITOR_ST_ADD1
3614 ,DNB_MONITOR_ST_ADD2
3615 ,DNB_MONITOR_CITY
3616 ,DNB_MONITOR_POSTAL_CODE
3617 ,DNB_MONITOR_COUNTRY_CODE
3618 ,DNB_MONITOR_STATE
3619 ,HQ_PARENT_POC
3620 ,PAYMENT_TYPE
3621 ,ANNUAL_RECEIPTS
3622 ,DOMESTIC_PARENT_POC
3623 ,DOMESTIC_PARENT_DUNS
3624 ,DOMESTIC_PARENT_ADD1
3625 ,DOMESTIC_PARENT_ADD2
3626 ,DOMESTIC_PARENT_CITY
3627 ,DOMESTIC_PARENT_POSTAL_CODE
3628 ,DOMESTIC_PARENT_COUNTRY
3629 ,DOMESTIC_PARENT_STATE
3630 ,DOMESTIC_PARENT_PHONE
3631 ,GLOBAL_PARENT_POC
3632 ,GLOBAL_PARENT_DUNS
3633 ,GLOBAL_PARENT_ADD1
3634 ,GLOBAL_PARENT_ADD2
3635 ,GLOBAL_PARENT_CITY
3636 ,GLOBAL_PARENT_POSTAL_CODE
3637 ,GLOBAL_PARENT_COUNTRY
3638 ,GLOBAL_PARENT_STATE
3639 ,GLOBAL_PARENT_PHONE
3640
3641 FROM FV_CCR_PROCESS_GT fcpg
3642 WHERE fcpg.duns = l_ccr_data.duns
3643 AND fcpg.extract_code=l_ccr_data.extract_code
3644 AND fcpg.plus_four = l_ccr_data.plus_four;
3645
3646 -- add into the dunplus four inserted section of report
3647 insert_temp_data(2,l_ccr_data.duns||l_ccr_data.plus_four,l_ccr_data.legal_bus_name,l_msg_pay_obj,null,null,null);
3648
3649 -- call to update business type validation
3650 END IF; -- end of extractcode as '3'
3651 END IF;
3652 when others then
3653 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,SQLERRM);
3654 END; -- end of select for dummy
3655 ELSE
3656 -- this is root DUNS record !!!
3657 BEGIN
3658 l_errbuf := 'Processing root DUNS -> '||l_ccr_data.duns;
3659 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3660
3661
3662 SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv
3663 WHERE fcv.duns = l_ccr_data.duns
3664 AND fcv.plus_four is null;
3665
3666 -- if this select does not return rows ,we need to update DUNS and duns+4 information
3667 l_errbuf := 'DUNS exists';
3668 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3669
3670 --BUG 3839843
3671 IF (l_lbe_change <> l_ccr_data.legal_bus_name) THEN
3672
3673 FND_MESSAGE.set_NAME('FV','FV_CCR_LBE_CHANGED');
3674 message_text := FND_MESSAGE.get;
3675
3676 l_errbuf :='Legal Bus Name changed from '||l_lbe_change||' -> '||l_ccr_data.legal_bus_name;
3677 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
3678 insert_temp_data(3,null,message_text ,null,l_ccr_data.duns||l_ccr_data.plus_four,null,null);
3679
3680 END IF;
3681
3682
3683 --validate for the renewal date)
3684 IF (l_ccr_data.renewal_date < trunc(sysdate) ) THEN
3685 l_status:='E';
3686 END IF; --end of renewal date
3687
3688 --call to update the DUNS record
3689 l_errbuf := 'Updating Root DUNS info '||l_ccr_data.duns;
3690 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3691
3692 update fv_ccr_vendors fcv set
3693 fcv.CCR_FLAG ='R' ,
3694 fcv.CCR_STATUS =l_status ,
3695 fcv.DUNS =l_ccr_data.DUNS ,
3696 fcv.PLUS_FOUR =null ,
3697 fcv.CAGE_CODE =l_ccr_data.CAGE_CODE ,
3698 fcv.EXTRACT_CODE =l_ccr_data.EXTRACT_CODE ,
3699 fcv.REGISTRATION_DATE =l_ccr_data.REGISTRATION_DATE ,
3700 fcv.RENEWAL_DATE =l_ccr_data.RENEWAL_DATE ,
3701 fcv.LEGAL_BUS_NAME =l_ccr_data.LEGAL_BUS_NAME ,
3702 fcv.DBA_NAME =l_ccr_data.DBA_NAME ,
3703 fcv.DIVISION_NAME =l_ccr_data.DIVISION_NAME ,
3704 fcv.DIVISION_NUMBER =l_ccr_data.DIVISION_NUMBER ,
3705 fcv.ST_ADDRESS1 =l_ccr_data.ST_ADDRESS1 ,
3706 fcv.ST_ADDRESS2 =l_ccr_data.ST_ADDRESS2 ,
3707 fcv.CITY =l_ccr_data.CITY ,
3708 fcv.STATE =l_ccr_data.STATE ,
3709 fcv.POSTAL_CODE =l_ccr_data.POSTAL_CODE ,
3710 --Bug8335551
3711 --fcv.COUNTRY =l_ccr_data.COUNTRY ,
3712 fcv.COUNTRY =get_territory_code(l_ccr_data.COUNTRY) ,
3713 fcv.BUSINESS_START_DATE =l_ccr_data.BUSINESS_START_DATE ,
3714 fcv.FISCAL_YR_CLOSE_DATE =l_ccr_data.FISCAL_YR_CLOSE_DATE ,
3715 fcv.WEB_SITE =l_ccr_data.WEB_SITE ,
3716 fcv.CREDIT_CARD_FLAG =l_ccr_data.CREDIT_CARD_FLAG ,
3717 fcv.MAIL_POC =l_ccr_data.MAIL_POC ,
3718 fcv.MAIL_ADD1 =l_ccr_data.MAIL_ADD1 ,
3719 fcv.MAIL_ADD2 =l_ccr_data.MAIL_ADD2 ,
3720 fcv.MAIL_CITY =l_ccr_data.MAIL_CITY ,
3721 fcv.MAIL_POSTAL_CODE =l_ccr_data.MAIL_POSTAL_CODE ,
3722 --Bug8335551
3723 --fcv.MAIL_COUNTRY =l_ccr_data.MAIL_COUNTRY ,
3724 fcv.MAIL_COUNTRY =get_territory_code(l_ccr_data.MAIL_COUNTRY) ,
3725 fcv.MAIL_STATE =l_ccr_data.MAIL_STATE ,
3726 fcv.PREV_BUS_POC =l_ccr_data.PREV_BUS_POC ,
3727 fcv.PREV_BUS_ADD1 =l_ccr_data.PREV_BUS_ADD1 ,
3728 fcv.PREV_BUS_ADD2 =l_ccr_data.PREV_BUS_ADD2 ,
3729 fcv.PREV_BUS_CITY =l_ccr_data.PREV_BUS_CITY ,
3730 fcv.PREV_BUS_POSTAL_CODE =l_ccr_data.PREV_BUS_POSTAL_CODE ,
3731 fcv.PREV_BUS_COUNTRY =l_ccr_data.PREV_BUS_COUNTRY ,
3732 fcv.PREV_BUS_STATE =l_ccr_data.PREV_BUS_STATE ,
3733 fcv.PARENT_POC =l_ccr_data.PARENT_POC ,
3734 fcv.PARENT_DUNS =l_ccr_data.PARENT_DUNS ,
3735 fcv.PARENT_ADD1 =l_ccr_data.PARENT_ADD1 ,
3736 fcv.PARENT_ADD2 =l_ccr_data.PARENT_ADD2 ,
3737 fcv.PARENT_CITY =l_ccr_data.PARENT_CITY ,
3738 fcv.PARENT_POSTAL_CODE =l_ccr_data.PARENT_POSTAL_CODE ,
3739 fcv.PARENT_COUNTRY =l_ccr_data.PARENT_COUNTRY ,
3740 fcv.PARENT_STATE =l_ccr_data.PARENT_STATE ,
3741 fcv.PARTY_PERF_POC =l_ccr_data.PARTY_PERF_POC ,
3742 fcv.PARTY_PERF_ADD1 =l_ccr_data.PARTY_PERF_ADD1 ,
3743 fcv.PARTY_PERF_ADD2 =l_ccr_data.PARTY_PERF_ADD2 ,
3744 fcv.PARTY_PERF_CITY =l_ccr_data.PARTY_PERF_CITY ,
3745 fcv.PARTY_PERF_POSTAL_CODE =l_ccr_data.PARTY_PERF_POSTAL_CODE ,
3746 fcv.PARTY_PERF_COUNTRY =l_ccr_data.PARTY_PERF_COUNTRY ,
3747 fcv.PARTY_PERF_STATE =l_ccr_data.PARTY_PERF_STATE ,
3748 fcv.GOV_PARENT_POC =l_ccr_data.GOV_PARENT_POC ,
3749 fcv.GOV_PARENT_ADD1 =l_ccr_data.GOV_PARENT_ADD1 ,
3750 fcv.GOV_PARENT_ADD2 =l_ccr_data.GOV_PARENT_ADD2 ,
3751 fcv.GOV_PARENT_CITY =l_ccr_data.GOV_PARENT_CITY ,
3752 fcv.GOV_PARENT_POSTAL_CODE =l_ccr_data.GOV_PARENT_POSTAL_CODE ,
3753 fcv.GOV_PARENT_COUNTRY =l_ccr_data.GOV_PARENT_COUNTRY ,
3754 fcv.GOV_PARENT_STATE =l_ccr_data.GOV_PARENT_STATE ,
3755 fcv.GOV_BUS_POC =l_ccr_data.GOV_BUS_POC ,
3756 fcv.GOV_BUS_ADD1 =l_ccr_data.GOV_BUS_ADD1 ,
3757 fcv.GOV_BUS_ADD2 =l_ccr_data.GOV_BUS_ADD2 ,
3758 fcv.GOV_BUS_CITY =l_ccr_data.GOV_BUS_CITY ,
3759 fcv.GOV_BUS_POSTAL_CODE =l_ccr_data.GOV_BUS_POSTAL_CODE ,
3760 fcv.GOV_BUS_COUNTRY =l_ccr_data.GOV_BUS_COUNTRY ,
3761 fcv.GOV_BUS_STATE =l_ccr_data.GOV_BUS_STATE ,
3762 fcv.GOV_BUS_US_PHONE =l_ccr_data.GOV_BUS_US_PHONE ,
3763 fcv.GOV_BUS_US_PHONE_EX =l_ccr_data.GOV_BUS_US_PHONE_EX ,
3764 fcv.GOV_BUS_NON_US_PHONE =l_ccr_data.GOV_BUS_NON_US_PHONE ,
3765 fcv.GOV_BUS_FAX =l_ccr_data.GOV_BUS_FAX ,
3766 fcv.GOV_BUS_EMAIL =l_ccr_data.GOV_BUS_EMAIL ,
3767 fcv.ALT_GOV_BUS_POC =l_ccr_data.ALT_GOV_BUS_POC ,
3768 fcv.ALT_GOV_BUS_ADD1 =l_ccr_data.ALT_GOV_BUS_ADD1 ,
3769 fcv.ALT_GOV_BUS_ADD2 =l_ccr_data.ALT_GOV_BUS_ADD2 ,
3770 fcv.ALT_GOV_BUS_CITY =l_ccr_data.ALT_GOV_BUS_CITY ,
3771 fcv.ALT_GOV_BUS_POSTAL_CODE =l_ccr_data.ALT_GOV_BUS_POSTAL_CODE ,
3772 fcv.ALT_GOV_BUS_COUNTRY =l_ccr_data.ALT_GOV_BUS_COUNTRY ,
3773 fcv.ALT_GOV_BUS_STATE =l_ccr_data.ALT_GOV_BUS_STATE ,
3774 fcv.ALT_GOV_BUS_US_PHONE =l_ccr_data.ALT_GOV_BUS_US_PHONE ,
3775 fcv.ALT_GOV_BUS_US_PHONE_EX =l_ccr_data.ALT_GOV_BUS_US_PHONE_EX ,
3776 fcv.ALT_GOV_BUS_NON_US_PHONE =l_ccr_data.ALT_GOV_BUS_NON_US_PHONE ,
3777 fcv.ALT_GOV_BUS_FAX =l_ccr_data.ALT_GOV_BUS_FAX ,
3778 fcv.ALT_GOV_BUS_EMAIL =l_ccr_data.ALT_GOV_BUS_EMAIL ,
3779 fcv.PAST_PERF_POC =l_ccr_data.PAST_PERF_POC ,
3780 fcv.PAST_PERF_ADD1 =l_ccr_data.PAST_PERF_ADD1 ,
3781 fcv.PAST_PERF_ADD2 =l_ccr_data.PAST_PERF_ADD2 ,
3782 fcv.PAST_PERF_CITY =l_ccr_data.PAST_PERF_CITY ,
3783 fcv.PAST_PERF_POSTAL_CODE =l_ccr_data.PAST_PERF_POSTAL_CODE ,
3784 fcv.PAST_PERF_COUNTRY =l_ccr_data.PAST_PERF_COUNTRY ,
3785 fcv.PAST_PERF_STATE =l_ccr_data.PAST_PERF_STATE ,
3786 fcv.PAST_PERF_US_PHONE =l_ccr_data.PAST_PERF_US_PHONE ,
3787 fcv.PAST_PERF_US_PHONE_EX =l_ccr_data.PAST_PERF_US_PHONE_EX ,
3788 fcv.PAST_PERF_NON_US_PHONE =l_ccr_data.PAST_PERF_NON_US_PHONE ,
3789 fcv.PAST_PERF_FAX =l_ccr_data.PAST_PERF_FAX ,
3790 fcv.PAST_PERF_EMAIL =l_ccr_data.PAST_PERF_EMAIL ,
3791 fcv.ALT_PAST_PERF_POC =l_ccr_data.ALT_PAST_PERF_POC ,
3792 fcv.ALT_PAST_PERF_ADD1 =l_ccr_data.ALT_PAST_PERF_ADD1 ,
3793 fcv.ALT_PAST_PERF_ADD2 =l_ccr_data.ALT_PAST_PERF_ADD2 ,
3794 fcv.ALT_PAST_PERF_CITY =l_ccr_data.ALT_PAST_PERF_CITY ,
3795 fcv.ALT_PAST_PERF_POSTAL_CODE =l_ccr_data.ALT_PAST_PERF_POSTAL_CODE ,
3796 fcv.ALT_PAST_PERF_COUNTRY =l_ccr_data.ALT_PAST_PERF_COUNTRY ,
3797 fcv.ALT_PAST_PERF_STATE =l_ccr_data.ALT_PAST_PERF_STATE ,
3798 fcv.ALT_PAST_PERF_US_PHONE =l_ccr_data.ALT_PAST_PERF_US_PHONE ,
3799 fcv.ALT_PAST_PERF_US_PHONE_EX =l_ccr_data.ALT_PAST_PERF_US_PHONE_EX ,
3800 fcv.ALT_PAST_PERF_NON_US_PHONE =l_ccr_data.ALT_PAST_PERF_NON_US_PHONE ,
3801 fcv.ALT_PAST_PERF_FAX =l_ccr_data.ALT_PAST_PERF_FAX ,
3802 fcv.ALT_PAST_PERF_EMAIL =l_ccr_data.ALT_PAST_PERF_EMAIL ,
3803 fcv.ELEC_BUS_POC =l_ccr_data.ELEC_BUS_POC ,
3804 fcv.ELEC_BUS_ADD1 =l_ccr_data.ELEC_BUS_ADD1 ,
3805 fcv.ELEC_BUS_ADD2 =l_ccr_data.ELEC_BUS_ADD2 ,
3806 fcv.ELEC_BUS_CITY =l_ccr_data.ELEC_BUS_CITY ,
3807 fcv.ELEC_BUS_POSTAL_CODE =l_ccr_data.ELEC_BUS_POSTAL_CODE ,
3808 fcv.ELEC_BUS_COUNTRY =l_ccr_data.ELEC_BUS_COUNTRY ,
3809 fcv.ELEC_BUS_STATE =l_ccr_data.ELEC_BUS_STATE ,
3810 fcv.ELEC_BUS_US_PHONE =l_ccr_data.ELEC_BUS_US_PHONE ,
3811 fcv.ELEC_BUS_US_PHONE_EX =l_ccr_data.ELEC_BUS_US_PHONE_EX ,
3812 fcv.ELEC_BUS_NON_US_PHONE =l_ccr_data.ELEC_BUS_NON_US_PHONE ,
3813 fcv.ELEC_BUS_FAX =l_ccr_data.ELEC_BUS_FAX ,
3814 fcv.ELEC_BUS_EMAIL =l_ccr_data.ELEC_BUS_EMAIL ,
3815 fcv.ALT_ELEC_BUS_POC =l_ccr_data.ALT_ELEC_BUS_POC ,
3816 fcv.ALT_ELEC_BUS_ADD1 =l_ccr_data.ALT_ELEC_BUS_ADD1 ,
3817 fcv.ALT_ELEC_BUS_ADD2 =l_ccr_data.ALT_ELEC_BUS_ADD2 ,
3818 fcv.ALT_ELEC_BUS_CITY =l_ccr_data.ALT_ELEC_BUS_CITY ,
3819 fcv.ALT_ELEC_BUS_POSTAL_CODE =l_ccr_data.ALT_ELEC_BUS_POSTAL_CODE ,
3820 fcv.ALT_ELEC_BUS_COUNTRY =l_ccr_data.ALT_ELEC_BUS_COUNTRY ,
3821 fcv.ALT_ELEC_BUS_STATE =l_ccr_data.ALT_ELEC_BUS_STATE ,
3822 fcv.ALT_ELEC_BUS_US_PHONE =l_ccr_data.ALT_ELEC_BUS_US_PHONE ,
3823 fcv.ALT_ELEC_BUS_US_PHONE_EX =l_ccr_data.ALT_ELEC_BUS_US_PHONE_EX ,
3824 fcv.ALT_ELEC_BUS_NON_US_PHONE =l_ccr_data.ALT_ELEC_BUS_NON_US_PHONE ,
3825 fcv.ALT_ELEC_BUS_FAX =l_ccr_data.ALT_ELEC_BUS_FAX ,
3826 fcv.ALT_ELEC_BUS_EMAIL =l_ccr_data.ALT_ELEC_BUS_EMAIL ,
3827 fcv.CERTIFIER_POC =l_ccr_data.CERTIFIER_POC ,
3828 fcv.CERTIFIER_US_PHONE =l_ccr_data.CERTIFIER_US_PHONE ,
3829 fcv.CERTIFIER_US_PHONE_EX =l_ccr_data.CERTIFIER_US_PHONE_EX ,
3830 fcv.CERTIFIER_NON_US_PHONE =l_ccr_data.CERTIFIER_NON_US_PHONE ,
3831 fcv.CERTIFIER_FAX =l_ccr_data.CERTIFIER_FAX ,
3832 fcv.CERTIFIER_EMAIL =l_ccr_data.CERTIFIER_EMAIL ,
3833 fcv.ALT_CERTIFIER_POC =l_ccr_data.ALT_CERTIFIER_POC ,
3834 fcv.ALT_CERTIFIER_US_PHONE =l_ccr_data.ALT_CERTIFIER_US_PHONE ,
3835 fcv.ALT_CERTIFIER_US_PHONE_EX =l_ccr_data.ALT_CERTIFIER_US_PHONE_EX ,
3836 fcv.ALT_CERTIFIER_NON_US_PHONE =l_ccr_data.ALT_CERTIFIER_NON_US_PHONE ,
3837 fcv.CORP_INFO_POC =l_ccr_data.CORP_INFO_POC ,
3838 fcv.CORP_INFO_US_PHONE =l_ccr_data.CORP_INFO_US_PHONE ,
3839 fcv.CORP_INFO_US_PHONE_EX =l_ccr_data.CORP_INFO_US_PHONE_EX ,
3840 fcv.CORP_INFO_NON_US_PHONE =l_ccr_data.CORP_INFO_NON_US_PHONE ,
3841 fcv.CORP_INFO_FAX =l_ccr_data.CORP_INFO_FAX ,
3842 fcv.CORP_INFO_EMAIL =l_ccr_data.CORP_INFO_EMAIL ,
3843 fcv.OWNER_INFO_POC =l_ccr_data.OWNER_INFO_POC ,
3844 fcv.OWNER_INFO_US_PHONE =l_ccr_data.OWNER_INFO_US_PHONE ,
3845 fcv.OWNER_INFO_US_PHONE_EX =l_ccr_data.OWNER_INFO_US_PHONE_EX ,
3846 fcv.OWNER_INFO_NON_US_PHONE =l_ccr_data.OWNER_INFO_NON_US_PHONE ,
3847 fcv.OWNER_INFO_FAX =l_ccr_data.OWNER_INFO_FAX ,
3848 fcv.OWNER_INFO_EMAIL =l_ccr_data.OWNER_INFO_EMAIL ,
3849 fcv.EDI =l_ccr_data.EDI ,
3850 fcv.TAXPAYER_ID =l_ccr_data.TAXPAYER_ID ,
3851 fcv.AVG_NUM_EMPLOYEES =l_ccr_data.AVG_NUM_EMPLOYEES ,
3852 fcv.ANNUAL_REVENUE =l_ccr_data.ANNUAL_REVENUE ,
3853 fcv.SOCIAL_SECURITY_NUMBER =l_ccr_data.SOCIAL_SECURITY_NUMBER ,
3854 fcv.FINANCIAL_INSTITUTE =l_ccr_data.FINANCIAL_INSTITUTE ,
3855 fcv.BANK_ACCT_NUMBER =l_ccr_data.BANK_ACCT_NUMBER ,
3856 fcv.ABA_ROUTING =l_ccr_data.ABA_ROUTING ,
3857 fcv.BANK_ACCT_TYPE =l_ccr_data.BANK_ACCT_TYPE ,
3858 fcv.LOCKBOX_NUMBER =l_ccr_data.LOCKBOX_NUMBER ,
3859 fcv.AUTHORIZATION_DATE =l_ccr_data.AUTHORIZATION_DATE ,
3860 fcv.EFT_WAIVER =l_ccr_data.EFT_WAIVER ,
3861 fcv.ACH_US_PHONE =l_ccr_data.ACH_US_PHONE ,
3862 fcv.ACH_NON_US_PHONE =l_ccr_data.ACH_NON_US_PHONE ,
3863 fcv.ACH_FAX =l_ccr_data.ACH_FAX ,
3864 fcv.ACH_EMAIL =l_ccr_data.ACH_EMAIL ,
3865 fcv.REMIT_POC =l_ccr_data.REMIT_POC ,
3866 fcv.REMIT_ADD1 =l_ccr_data.REMIT_ADD1 ,
3867 fcv.REMIT_ADD2 =l_ccr_data.REMIT_ADD2 ,
3868 fcv.REMIT_CITY =l_ccr_data.REMIT_CITY ,
3869 fcv.REMIT_STATE =l_ccr_data.REMIT_STATE ,
3870 fcv.REMIT_POSTAL_CODE =l_ccr_data.REMIT_POSTAL_CODE ,
3871 --Bug8335551
3872 --fcv.REMIT_COUNTRY =l_ccr_data.REMIT_COUNTRY ,
3873 fcv.REMIT_COUNTRY =get_territory_code(l_ccr_data.REMIT_COUNTRY) ,
3874 fcv.AR_POC =l_ccr_data.AR_POC ,
3875 fcv.AR_US_PHONE =l_ccr_data.AR_US_PHONE ,
3876 fcv.AR_US_PHONE_EX =l_ccr_data.AR_US_PHONE_EX ,
3877 fcv.AR_NON_US_PHONE =l_ccr_data.AR_NON_US_PHONE ,
3878 fcv.AR_FAX =l_ccr_data.AR_FAX ,
3879 fcv.AR_EMAIL =l_ccr_data.AR_EMAIL ,
3880 fcv.MPIN =l_ccr_data.MPIN ,
3881 fcv.EDI_COORDINATOR =l_ccr_data.EDI_COORDINATOR ,
3882 fcv.EDI_US_PHONE =l_ccr_data.EDI_US_PHONE ,
3883 fcv.EDI_US_PHONE_EX =l_ccr_data.EDI_US_PHONE_EX ,
3884 fcv.EDI_NON_US_PHONE =l_ccr_data.EDI_NON_US_PHONE ,
3885 fcv.EDI_FAX =l_ccr_data.EDI_FAX ,
3886 fcv.EDI_EMAIL =l_ccr_data.EDI_EMAIL ,
3887 fcv.last_update_date =sysdate ,
3888 fcv.last_updated_by =l_user_id ,
3889 fcv.organizational_type =l_code(71).code ,
3890 fcv.correspondence_flag =l_code(72).code ,
3891 fcv.corp_security_level =l_code(73).code ,
3892 fcv.emp_security_level =l_code(74).code ,
3893 fcv.last_import_date =l_ccr_data.file_date ,
3894 fcv.STATE_OF_INC =l_ccr_data.state_of_inc ,
3895 fcv.COUNTRY_OF_INC =l_ccr_data.country_of_inc ,
3896 fcv.alt_certifier_email =l_ccr_data.alt_certifier_email,
3897 fcv.alt_certifier_fax =l_ccr_data.alt_certifier_fax
3898 ,fcv.AUSTIN_TETRA_NUMBER =l_ccr_data.AUSTIN_TETRA_NUMBER
3899 ,fcv.AUSTIN_TETRA_PARENT_NUMBER =l_ccr_data.AUSTIN_TETRA_PARENT_NUMBER
3900 ,fcv.AUSTIN_TETRA_ULTIMATE_NUMBER =l_ccr_data.AUSTIN_TETRA_ULTIMATE_NUMBER
3901 ,fcv.AUSTIN_TETRA_PCARD_FLAG =l_ccr_data.AUSTIN_TETRA_PCARD_FLAG
3902 ,fcv.DNB_MONITOR_LAST_UPDATED =l_ccr_data.DNB_MONITOR_LAST_UPDATED
3903 ,fcv.DNB_MONITOR_STATUS =l_ccr_data.DNB_MONITOR_STATUS
3904 ,fcv.DNB_MONITOR_CORP_NAME =l_ccr_data.DNB_MONITOR_CORP_NAME
3905 ,fcv.DNB_MONITOR_DBA =l_ccr_data.DNB_MONITOR_DBA
3906 ,fcv.DNB_MONITOR_ST_ADD1 =l_ccr_data.DNB_MONITOR_ST_ADD1
3907 ,fcv.DNB_MONITOR_ST_ADD2 =l_ccr_data.DNB_MONITOR_ST_ADD2
3908 ,fcv.DNB_MONITOR_CITY =l_ccr_data.DNB_MONITOR_CITY
3909 ,fcv.DNB_MONITOR_POSTAL_CODE =l_ccr_data.DNB_MONITOR_POSTAL_CODE
3910 ,fcv.DNB_MONITOR_COUNTRY_CODE =l_ccr_data.DNB_MONITOR_COUNTRY_CODE
3911 ,fcv.DNB_MONITOR_STATE =l_ccr_data.DNB_MONITOR_STATE
3912 ,fcv.HQ_PARENT_POC =l_ccr_data.HQ_PARENT_POC
3913 ,fcv.PAYMENT_TYPE =l_ccr_data.PAYMENT_TYPE
3914 ,fcv.ANNUAL_RECEIPTS =l_ccr_data.ANNUAL_RECEIPTS
3915 ,fcv.DOMESTIC_PARENT_POC =l_ccr_data.DOMESTIC_PARENT_POC
3916 ,fcv.DOMESTIC_PARENT_DUNS =l_ccr_data.DOMESTIC_PARENT_DUNS
3917 ,fcv.DOMESTIC_PARENT_ADD1 =l_ccr_data.DOMESTIC_PARENT_ADD1
3918 ,fcv.DOMESTIC_PARENT_ADD2 =l_ccr_data.DOMESTIC_PARENT_ADD2
3919 ,fcv.DOMESTIC_PARENT_CITY =l_ccr_data.DOMESTIC_PARENT_CITY
3920 ,fcv.DOMESTIC_PARENT_POSTAL_CODE =l_ccr_data.DOMESTIC_PARENT_POSTAL_CODE
3921 ,fcv.DOMESTIC_PARENT_COUNTRY =l_ccr_data.DOMESTIC_PARENT_COUNTRY
3922 ,fcv.DOMESTIC_PARENT_STATE =l_ccr_data.DOMESTIC_PARENT_STATE
3923 ,fcv.DOMESTIC_PARENT_PHONE =l_ccr_data.DOMESTIC_PARENT_PHONE
3924 ,fcv.GLOBAL_PARENT_POC =l_ccr_data.GLOBAL_PARENT_POC
3925 ,fcv.GLOBAL_PARENT_DUNS =l_ccr_data.GLOBAL_PARENT_DUNS
3926 ,fcv.GLOBAL_PARENT_ADD1 =l_ccr_data.GLOBAL_PARENT_ADD1
3927 ,fcv.GLOBAL_PARENT_ADD2 =l_ccr_data.GLOBAL_PARENT_ADD2
3928 ,fcv.GLOBAL_PARENT_CITY =l_ccr_data.GLOBAL_PARENT_CITY
3929 ,fcv.GLOBAL_PARENT_POSTAL_CODE =l_ccr_data.GLOBAL_PARENT_POSTAL_CODE
3930 ,fcv.GLOBAL_PARENT_COUNTRY =l_ccr_data.GLOBAL_PARENT_COUNTRY
3931 ,fcv.GLOBAL_PARENT_STATE =l_ccr_data.GLOBAL_PARENT_STATE
3932 ,fcv.GLOBAL_PARENT_PHONE =l_ccr_data.GLOBAL_PARENT_PHONE
3933
3934 WHERE fcv.duns= l_ccr_data.duns
3935 and (fcv.plus_four is null
3936 and l_ccr_data.plus_four is null);
3937
3938 --add into the duns processed section
3939 insert_temp_data(1,l_ccr_data.duns||l_ccr_data.plus_four,l_ccr_data.legal_bus_name,l_ccr_data.cage_code,nvl(l_ccr_data.taxpayer_id,l_ccr_data.social_security_number),l_status,null);
3940
3941 l_errbuf := 'Updating Root DUNS info '||l_ccr_data.duns || 'done';
3942 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3943 exception
3944 when NO_data_found THEN
3945 IF (p_xml_import ='N' or p_insert_data='Y') THEN
3946 l_errbuf := 'exception no data found for duns ';
3947 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3948
3949 -- This case DUns doesn ot exist
3950 l_errbuf := 'DUNS does not exist in FV_CCR_VENDORS';
3951 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3952
3953 IF (l_ccr_data.extract_code ='3') THEN
3954 l_errbuf := 'Error - the DUNS does not exist in FV_CCR_VENDORS';
3955 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3956 ELSE
3957
3958 --validate for the renewal date)
3959 IF (l_ccr_data.renewal_date < trunc(sysdate) ) THEN
3960 l_status:='E';
3961 ELSE
3962 l_status :='A';
3963 END IF; -- end of renewal date val
3964
3965 --call procedureto insert duns info
3966 l_errbuf := 'Insert DUNS' || l_ccr_data.duns;
3967 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3968 INSERT INTO FV_CCR_VENDORS (
3969 CCR_ID ,
3970 ENABLED ,
3971 CCR_FLAG ,
3972 CCR_STATUS ,
3973 DUNS ,
3974 PLUS_FOUR ,
3975 CAGE_CODE ,
3976 EXTRACT_CODE ,
3977 REGISTRATION_DATE ,
3978 RENEWAL_DATE ,
3979 LEGAL_BUS_NAME ,
3980 DBA_NAME ,
3981 DIVISION_NAME ,
3982 DIVISION_NUMBER ,
3983 ST_ADDRESS1 ,
3984 ST_ADDRESS2 ,
3985 CITY ,
3986 STATE ,
3987 POSTAL_CODE ,
3988 COUNTRY ,
3989 BUSINESS_START_DATE ,
3990 FISCAL_YR_CLOSE_DATE ,
3991 CORP_SECURITY_LEVEL ,
3992 EMP_SECURITY_LEVEL ,
3993 WEB_SITE ,
3994 CREDIT_CARD_FLAG ,
3995 CORRESPONDENCE_FLAG ,
3996 MAIL_POC ,
3997 MAIL_ADD1 ,
3998 MAIL_ADD2 ,
3999 MAIL_CITY ,
4000 MAIL_POSTAL_CODE ,
4001 MAIL_COUNTRY ,
4002 MAIL_STATE ,
4003 PREV_BUS_POC ,
4004 PREV_BUS_ADD1 ,
4005 PREV_BUS_ADD2 ,
4006 PREV_BUS_CITY ,
4007 PREV_BUS_POSTAL_CODE ,
4008 PREV_BUS_COUNTRY ,
4009 PREV_BUS_STATE ,
4010 PARENT_POC ,
4011 PARENT_DUNS ,
4012 PARENT_ADD1 ,
4013 PARENT_ADD2 ,
4014 PARENT_CITY ,
4015 PARENT_POSTAL_CODE ,
4016 PARENT_COUNTRY ,
4017 PARENT_STATE ,
4018 PARTY_PERF_POC ,
4019 PARTY_PERF_ADD1 ,
4020 PARTY_PERF_ADD2 ,
4021 PARTY_PERF_CITY ,
4022 PARTY_PERF_POSTAL_CODE ,
4023 PARTY_PERF_COUNTRY ,
4024 PARTY_PERF_STATE ,
4025 GOV_PARENT_POC ,
4026 GOV_PARENT_ADD1 ,
4027 GOV_PARENT_ADD2 ,
4028 GOV_PARENT_CITY ,
4029 GOV_PARENT_POSTAL_CODE ,
4030 GOV_PARENT_COUNTRY ,
4031 GOV_PARENT_STATE ,
4032 GOV_BUS_POC ,
4033 GOV_BUS_ADD1 ,
4034 GOV_BUS_ADD2 ,
4035 GOV_BUS_CITY ,
4036 GOV_BUS_POSTAL_CODE ,
4037 GOV_BUS_COUNTRY ,
4038 GOV_BUS_STATE ,
4039 GOV_BUS_US_PHONE ,
4040 GOV_BUS_US_PHONE_EX ,
4041 GOV_BUS_NON_US_PHONE ,
4042 GOV_BUS_FAX ,
4043 GOV_BUS_EMAIL ,
4044 ALT_GOV_BUS_POC ,
4045 ALT_GOV_BUS_ADD1 ,
4046 ALT_GOV_BUS_ADD2 ,
4047 ALT_GOV_BUS_CITY ,
4048 ALT_GOV_BUS_POSTAL_CODE ,
4049 ALT_GOV_BUS_COUNTRY ,
4050 ALT_GOV_BUS_STATE ,
4051 ALT_GOV_BUS_US_PHONE ,
4052 ALT_GOV_BUS_US_PHONE_EX ,
4053 ALT_GOV_BUS_NON_US_PHONE ,
4054 ALT_GOV_BUS_FAX ,
4055 ALT_GOV_BUS_EMAIL ,
4056 PAST_PERF_POC ,
4057 PAST_PERF_ADD1 ,
4058 PAST_PERF_ADD2 ,
4059 PAST_PERF_CITY ,
4060 PAST_PERF_POSTAL_CODE ,
4061 PAST_PERF_COUNTRY ,
4062 PAST_PERF_STATE ,
4063 PAST_PERF_US_PHONE ,
4064 PAST_PERF_US_PHONE_EX ,
4065 PAST_PERF_NON_US_PHONE ,
4066 PAST_PERF_FAX ,
4067 PAST_PERF_EMAIL ,
4068 ALT_PAST_PERF_POC ,
4069 ALT_PAST_PERF_ADD1 ,
4070 ALT_PAST_PERF_ADD2 ,
4071 ALT_PAST_PERF_CITY ,
4072 ALT_PAST_PERF_POSTAL_CODE ,
4073 ALT_PAST_PERF_COUNTRY ,
4074 ALT_PAST_PERF_STATE ,
4075 ALT_PAST_PERF_US_PHONE ,
4076 ALT_PAST_PERF_US_PHONE_EX ,
4077 ALT_PAST_PERF_NON_US_PHONE ,
4078 ALT_PAST_PERF_FAX ,
4079 ALT_PAST_PERF_EMAIL ,
4080 ELEC_BUS_POC ,
4081 ELEC_BUS_ADD1 ,
4082 ELEC_BUS_ADD2 ,
4083 ELEC_BUS_CITY ,
4084 ELEC_BUS_POSTAL_CODE ,
4085 ELEC_BUS_COUNTRY ,
4086 ELEC_BUS_STATE ,
4087 ELEC_BUS_US_PHONE ,
4088 ELEC_BUS_US_PHONE_EX ,
4089 ELEC_BUS_NON_US_PHONE ,
4090 ELEC_BUS_FAX ,
4091 ELEC_BUS_EMAIL ,
4092 ALT_ELEC_BUS_POC ,
4093 ALT_ELEC_BUS_ADD1 ,
4094 ALT_ELEC_BUS_ADD2 ,
4095 ALT_ELEC_BUS_CITY ,
4096 ALT_ELEC_BUS_POSTAL_CODE ,
4097 ALT_ELEC_BUS_COUNTRY ,
4098 ALT_ELEC_BUS_STATE ,
4099 ALT_ELEC_BUS_US_PHONE ,
4100 ALT_ELEC_BUS_US_PHONE_EX ,
4101 ALT_ELEC_BUS_NON_US_PHONE ,
4102 ALT_ELEC_BUS_FAX ,
4103 ALT_ELEC_BUS_EMAIL ,
4104 CERTIFIER_POC ,
4105 CERTIFIER_US_PHONE ,
4106 CERTIFIER_US_PHONE_EX ,
4107 CERTIFIER_NON_US_PHONE ,
4108 CERTIFIER_FAX ,
4109 CERTIFIER_EMAIL ,
4110 ALT_CERTIFIER_POC ,
4111 ALT_CERTIFIER_US_PHONE ,
4112 ALT_CERTIFIER_US_PHONE_EX ,
4113 ALT_CERTIFIER_NON_US_PHONE ,
4114 CORP_INFO_POC ,
4115 CORP_INFO_US_PHONE ,
4116 CORP_INFO_US_PHONE_EX ,
4117 CORP_INFO_NON_US_PHONE ,
4118 CORP_INFO_FAX ,
4119 CORP_INFO_EMAIL ,
4120 OWNER_INFO_POC ,
4121 OWNER_INFO_US_PHONE ,
4122 OWNER_INFO_US_PHONE_EX ,
4123 OWNER_INFO_NON_US_PHONE ,
4124 OWNER_INFO_FAX ,
4125 OWNER_INFO_EMAIL ,
4126 EDI ,
4127 TAXPAYER_ID ,
4128 AVG_NUM_EMPLOYEES ,
4129 ANNUAL_REVENUE ,
4130 SOCIAL_SECURITY_NUMBER ,
4131 FINANCIAL_INSTITUTE ,
4132 BANK_ACCT_NUMBER ,
4133 ABA_ROUTING ,
4134 BANK_ACCT_TYPE ,
4135 LOCKBOX_NUMBER ,
4136 AUTHORIZATION_DATE ,
4137 EFT_WAIVER ,
4138 ACH_US_PHONE ,
4139 ACH_NON_US_PHONE ,
4140 ACH_FAX ,
4141 ACH_EMAIL ,
4142 REMIT_POC ,
4143 REMIT_ADD1 ,
4144 REMIT_ADD2 ,
4145 REMIT_CITY ,
4146 REMIT_STATE ,
4147 REMIT_POSTAL_CODE ,
4148 REMIT_COUNTRY ,
4149 AR_POC ,
4150 AR_US_PHONE ,
4151 AR_US_PHONE_EX ,
4152 AR_NON_US_PHONE ,
4153 AR_FAX ,
4154 AR_EMAIL ,
4155 MPIN ,
4156 EDI_COORDINATOR ,
4157 EDI_US_PHONE ,
4158 EDI_US_PHONE_EX ,
4159 EDI_NON_US_PHONE ,
4160 EDI_FAX ,
4161 EDI_EMAIL ,
4162 LAST_UPDATE_DATE ,
4163 LAST_UPDATED_BY ,
4164 last_import_date ,
4165 ALT_CERTIFIER_FAX ,
4166 ALT_CERTIFIER_EMAIL,
4167 CREATION_DATE ,
4168 CREATED_BY ,
4169 LAST_UPDATE_LOGIN ,
4170 state_of_inc ,
4171 COUNTRY_OF_INC,
4172 -- Added for bug 6339382
4173 ORGANIZATIONAL_TYPE ,AUSTIN_TETRA_NUMBER
4174 ,AUSTIN_TETRA_PARENT_NUMBER
4175 ,AUSTIN_TETRA_ULTIMATE_NUMBER
4176 ,AUSTIN_TETRA_PCARD_FLAG
4177 ,DNB_MONITOR_LAST_UPDATED
4178 ,DNB_MONITOR_STATUS
4179 ,DNB_MONITOR_CORP_NAME
4180 ,DNB_MONITOR_DBA
4181 ,DNB_MONITOR_ST_ADD1
4182 ,DNB_MONITOR_ST_ADD2
4183 ,DNB_MONITOR_CITY
4184 ,DNB_MONITOR_POSTAL_CODE
4185 ,DNB_MONITOR_COUNTRY_CODE
4186 ,DNB_MONITOR_STATE
4187 ,HQ_PARENT_POC
4188 ,PAYMENT_TYPE
4189 ,ANNUAL_RECEIPTS
4190 ,DOMESTIC_PARENT_POC
4191 ,DOMESTIC_PARENT_DUNS
4192 ,DOMESTIC_PARENT_ADD1
4193 ,DOMESTIC_PARENT_ADD2
4194 ,DOMESTIC_PARENT_CITY
4195 ,DOMESTIC_PARENT_POSTAL_CODE
4196 ,DOMESTIC_PARENT_COUNTRY
4197 ,DOMESTIC_PARENT_STATE
4198 ,DOMESTIC_PARENT_PHONE
4199 ,GLOBAL_PARENT_POC
4200 ,GLOBAL_PARENT_DUNS
4201 ,GLOBAL_PARENT_ADD1
4202 ,GLOBAL_PARENT_ADD2
4203 ,GLOBAL_PARENT_CITY
4204 ,GLOBAL_PARENT_POSTAL_CODE
4205 ,GLOBAL_PARENT_COUNTRY
4206 ,GLOBAL_PARENT_STATE
4207 ,GLOBAL_PARENT_PHONE
4208 )
4209 SELECT FV_CCR_VENDORS_S.nextval ,'Y','R',l_status,
4210 DUNS ,
4211 PLUS_FOUR ,
4212 CAGE_CODE ,
4213 EXTRACT_CODE ,
4214 REGISTRATION_DATE ,
4215 RENEWAL_DATE ,
4216 LEGAL_BUS_NAME ,
4217 DBA_NAME ,
4218 DIVISION_NAME ,
4219 DIVISION_NUMBER ,
4220 ST_ADDRESS1 ,
4221 ST_ADDRESS2 ,
4222 CITY ,
4223 STATE ,
4224 POSTAL_CODE ,
4225 --Bug8335551
4226 --COUNTRY ,
4227 get_territory_code(COUNTRY) ,
4228 BUSINESS_START_DATE ,
4229 FISCAL_YR_CLOSE_DATE ,
4230 CORP_SECURITY_LEVEL ,
4231 EMP_SECURITY_LEVEL ,
4232 WEB_SITE ,
4233 CREDIT_CARD_FLAG ,
4234 CORRESPONDENCE_FLAG ,
4235 MAIL_POC ,
4236 MAIL_ADD1 ,
4237 MAIL_ADD2 ,
4238 MAIL_CITY ,
4239 MAIL_POSTAL_CODE ,
4240 --Bug8335551
4241 --MAIL_COUNTRY ,
4242 get_territory_code(MAIL_COUNTRY) ,
4243 MAIL_STATE ,
4244 PREV_BUS_POC ,
4245 PREV_BUS_ADD1 ,
4246 PREV_BUS_ADD2 ,
4247 PREV_BUS_CITY ,
4248 PREV_BUS_POSTAL_CODE ,
4249 PREV_BUS_COUNTRY ,
4250 PREV_BUS_STATE ,
4251 PARENT_POC ,
4252 PARENT_DUNS ,
4253 PARENT_ADD1 ,
4254 PARENT_ADD2 ,
4255 PARENT_CITY ,
4256 PARENT_POSTAL_CODE ,
4257 PARENT_COUNTRY ,
4258 PARENT_STATE ,
4259 PARTY_PERF_POC ,
4260 PARTY_PERF_ADD1 ,
4261 PARTY_PERF_ADD2 ,
4262 PARTY_PERF_CITY ,
4263 PARTY_PERF_POSTAL_CODE ,
4264 PARTY_PERF_COUNTRY ,
4265 PARTY_PERF_STATE ,
4266 GOV_PARENT_POC ,
4267 GOV_PARENT_ADD1 ,
4268 GOV_PARENT_ADD2 ,
4269 GOV_PARENT_CITY ,
4270 GOV_PARENT_POSTAL_CODE ,
4271 GOV_PARENT_COUNTRY ,
4272 GOV_PARENT_STATE ,
4273 GOV_BUS_POC ,
4274 GOV_BUS_ADD1 ,
4275 GOV_BUS_ADD2 ,
4276 GOV_BUS_CITY ,
4277 GOV_BUS_POSTAL_CODE ,
4278 GOV_BUS_COUNTRY ,
4279 GOV_BUS_STATE ,
4280 GOV_BUS_US_PHONE ,
4281 GOV_BUS_US_PHONE_EX ,
4282 GOV_BUS_NON_US_PHONE ,
4283 GOV_BUS_FAX ,
4284 GOV_BUS_EMAIL ,
4285 ALT_GOV_BUS_POC ,
4286 ALT_GOV_BUS_ADD1 ,
4287 ALT_GOV_BUS_ADD2 ,
4288 ALT_GOV_BUS_CITY ,
4289 ALT_GOV_BUS_POSTAL_CODE ,
4290 ALT_GOV_BUS_COUNTRY ,
4291 ALT_GOV_BUS_STATE ,
4292 ALT_GOV_BUS_US_PHONE ,
4293 ALT_GOV_BUS_US_PHONE_EX ,
4294 ALT_GOV_BUS_NON_US_PHONE ,
4295 ALT_GOV_BUS_FAX ,
4296 ALT_GOV_BUS_EMAIL ,
4297 PAST_PERF_POC ,
4298 PAST_PERF_ADD1 ,
4299 PAST_PERF_ADD2 ,
4300 PAST_PERF_CITY ,
4301 PAST_PERF_POSTAL_CODE ,
4302 PAST_PERF_COUNTRY ,
4303 PAST_PERF_STATE ,
4304 PAST_PERF_US_PHONE ,
4305 PAST_PERF_US_PHONE_EX ,
4306 PAST_PERF_NON_US_PHONE ,
4307 PAST_PERF_FAX ,
4308 PAST_PERF_EMAIL ,
4309 ALT_PAST_PERF_POC ,
4310 ALT_PAST_PERF_ADD1 ,
4311 ALT_PAST_PERF_ADD2 ,
4312 ALT_PAST_PERF_CITY ,
4313 ALT_PAST_PERF_POSTAL_CODE ,
4314 ALT_PAST_PERF_COUNTRY ,
4315 ALT_PAST_PERF_STATE ,
4316 ALT_PAST_PERF_US_PHONE ,
4317 ALT_PAST_PERF_US_PHONE_EX ,
4318 ALT_PAST_PERF_NON_US_PHONE ,
4319 ALT_PAST_PERF_FAX ,
4320 ALT_PAST_PERF_EMAIL ,
4321 ELEC_BUS_POC ,
4322 ELEC_BUS_ADD1 ,
4323 ELEC_BUS_ADD2 ,
4324 ELEC_BUS_CITY ,
4325 ELEC_BUS_POSTAL_CODE ,
4326 ELEC_BUS_COUNTRY ,
4327 ELEC_BUS_STATE ,
4328 ELEC_BUS_US_PHONE ,
4329 ELEC_BUS_US_PHONE_EX ,
4330 ELEC_BUS_NON_US_PHONE ,
4331 ELEC_BUS_FAX ,
4332 ELEC_BUS_EMAIL ,
4333 ALT_ELEC_BUS_POC ,
4334 ALT_ELEC_BUS_ADD1 ,
4335 ALT_ELEC_BUS_ADD2 ,
4336 ALT_ELEC_BUS_CITY ,
4337 ALT_ELEC_BUS_POSTAL_CODE ,
4338 ALT_ELEC_BUS_COUNTRY ,
4339 ALT_ELEC_BUS_STATE ,
4340 ALT_ELEC_BUS_US_PHONE ,
4341 ALT_ELEC_BUS_US_PHONE_EX ,
4342 ALT_ELEC_BUS_NON_US_PHONE ,
4343 ALT_ELEC_BUS_FAX ,
4344 ALT_ELEC_BUS_EMAIL ,
4345 CERTIFIER_POC ,
4346 CERTIFIER_US_PHONE ,
4347 CERTIFIER_US_PHONE_EX ,
4348 CERTIFIER_NON_US_PHONE ,
4349 CERTIFIER_FAX ,
4350 CERTIFIER_EMAIL ,
4351 ALT_CERTIFIER_POC ,
4352 ALT_CERTIFIER_US_PHONE ,
4353 ALT_CERTIFIER_US_PHONE_EX ,
4354 ALT_CERTIFIER_NON_US_PHONE ,
4355 CORP_INFO_POC ,
4356 CORP_INFO_US_PHONE ,
4357 CORP_INFO_US_PHONE_EX ,
4358 CORP_INFO_NON_US_PHONE ,
4359 CORP_INFO_FAX ,
4360 CORP_INFO_EMAIL ,
4361 OWNER_INFO_POC ,
4362 OWNER_INFO_US_PHONE ,
4363 OWNER_INFO_US_PHONE_EX ,
4364 OWNER_INFO_NON_US_PHONE ,
4365 OWNER_INFO_FAX ,
4366 OWNER_INFO_EMAIL ,
4367 EDI ,
4368 TAXPAYER_ID ,
4369 AVG_NUM_EMPLOYEES ,
4370 ANNUAL_REVENUE ,
4371 SOCIAL_SECURITY_NUMBER ,
4372 FINANCIAL_INSTITUTE ,
4373 BANK_ACCT_NUMBER ,
4374 ABA_ROUTING ,
4375 BANK_ACCT_TYPE ,
4376 LOCKBOX_NUMBER ,
4377 AUTHORIZATION_DATE ,
4378 EFT_WAIVER ,
4379 ACH_US_PHONE ,
4380 ACH_NON_US_PHONE ,
4381 ACH_FAX ,
4382 ACH_EMAIL ,
4383 REMIT_POC ,
4384 REMIT_ADD1 ,
4385 REMIT_ADD2 ,
4386 REMIT_CITY ,
4387 REMIT_STATE ,
4388 REMIT_POSTAL_CODE ,
4389 --Bug8335551
4390 --REMIT_COUNTRY ,
4391 get_territory_code(REMIT_COUNTRY) ,
4392 AR_POC ,
4393 AR_US_PHONE ,
4394 AR_US_PHONE_EX ,
4395 AR_NON_US_PHONE ,
4396 AR_FAX ,
4397 AR_EMAIL ,
4398 MPIN ,
4399 EDI_COORDINATOR ,
4400 EDI_US_PHONE ,
4401 EDI_US_PHONE_EX ,
4402 EDI_NON_US_PHONE ,
4403 EDI_FAX ,
4404 EDI_EMAIL ,
4405 sysdate ,
4406 l_user_id,
4407 file_date,
4408 ALT_CERTIFIER_FAX ,
4409 ALT_CERTIFIER_EMAIL,
4410 sysdate,
4411 l_user_id,
4412 l_user_id ,
4413 state_of_inc,
4414 COUNTRY_OF_INC,
4415 -- Added for bug 6339382
4416 l_code(71).code
4417 ,AUSTIN_TETRA_NUMBER
4418 ,AUSTIN_TETRA_PARENT_NUMBER
4419 ,AUSTIN_TETRA_ULTIMATE_NUMBER
4420 ,AUSTIN_TETRA_PCARD_FLAG
4421 ,DNB_MONITOR_LAST_UPDATED
4422 ,DNB_MONITOR_STATUS
4423 ,DNB_MONITOR_CORP_NAME
4424 ,DNB_MONITOR_DBA
4425 ,DNB_MONITOR_ST_ADD1
4426 ,DNB_MONITOR_ST_ADD2
4427 ,DNB_MONITOR_CITY
4428 ,DNB_MONITOR_POSTAL_CODE
4429 ,DNB_MONITOR_COUNTRY_CODE
4430 ,DNB_MONITOR_STATE
4431 ,HQ_PARENT_POC
4432 ,PAYMENT_TYPE
4433 ,ANNUAL_RECEIPTS
4434 ,DOMESTIC_PARENT_POC
4435 ,DOMESTIC_PARENT_DUNS
4436 ,DOMESTIC_PARENT_ADD1
4437 ,DOMESTIC_PARENT_ADD2
4438 ,DOMESTIC_PARENT_CITY
4439 ,DOMESTIC_PARENT_POSTAL_CODE
4440 ,DOMESTIC_PARENT_COUNTRY
4441 ,DOMESTIC_PARENT_STATE
4442 ,DOMESTIC_PARENT_PHONE
4443 ,GLOBAL_PARENT_POC
4444 ,GLOBAL_PARENT_DUNS
4445 ,GLOBAL_PARENT_ADD1
4446 ,GLOBAL_PARENT_ADD2
4447 ,GLOBAL_PARENT_CITY
4448 ,GLOBAL_PARENT_POSTAL_CODE
4449 ,GLOBAL_PARENT_COUNTRY
4450 ,GLOBAL_PARENT_STATE
4451 ,GLOBAL_PARENT_PHONE
4452 FROM FV_CCR_PROCESS_GT fcpg
4453 WHERE fcpg.duns = l_ccr_data.duns
4454 AND fcpg.extract_code=l_ccr_data.extract_code
4455 AND fcpg.plus_four is null ;
4456
4457 --add into new section of exception report
4458 insert_temp_data(2,l_ccr_data.duns||l_ccr_data.plus_four,l_ccr_data.legal_bus_name,l_msg_pay_obj,null,null,null);
4459
4460 END IF ; -- end of extractcode as '3' root duns
4461 END IF ; -- xml import =y for no data found
4462 when others THEN
4463 l_errbuf := 'Exception occurred while updating root duns '||SQLERRM;
4464 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4465
4466 END; -- end of begin for root duns
4467
4468 END IF; -- end of duns+4 not null
4469 END IF; -- end of if l_valid_tin IS NULL
4470 END LOOP;
4471
4472 --sthota
4473 if l_vendor_cnt>0 then
4474 for i in 1..l_vendor_cnt
4475 loop
4476 ap_approval_pkg.BATCH_APPROVAL_FOR_VENDOR(vendor_ids(l_vendor_cnt), l_module_name);
4477 l_errbuf := 'New Active Vendor Id: '||vendor_ids(i);
4478 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4479 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_errbuf);
4480 select vendor_name into l_vendor_name from po_vendors where vendor_id = vendor_ids(i);
4481 insert_temp_data(4,duns_ids(i),vendor_ids(i),l_vendor_name,null,null,null);
4482
4483 end loop;
4484 end if;
4485
4486 -- bug 3838149
4487 FOR l_taxpayer in c_taxpayer
4488 LOOP
4489 l_counter:=1;
4490 l_duns_list:='';
4491
4492 FOR l_duns_info in c_duns_info( l_taxpayer.taxpayer_id,l_taxpayer.vendor_id)
4493 LOOP
4494 IF l_counter > 1 THEN
4495 l_duns_list:= l_duns_list||',';
4496 l_counter := l_counter+1;
4497 END IF;
4498 l_duns_list:= l_duns_list|| l_duns_info.duns;
4499 l_counter := l_counter+1;
4500
4501 END LOOP;
4502
4503 OPEN c_vendor_info(l_taxpayer.vendor_id);
4504 FETCH c_vendor_info into l_vendor_id;
4505 CLOSE c_vendor_info;
4506
4507 FND_MESSAGE.set_NAME('FV','FV_CCR_DUPLICATE_TAXPAYER_NUM');
4508 FND_MESSAGE.set_TOKEN('LISTDUNS',l_duns_list);
4509 FND_MESSAGE.SET_TOKEN('VENDOR', l_vendor_id);
4510 message_text := FND_MESSAGE.get;
4511
4512
4513 insert_temp_data(3,null,message_text ,null,'ORACLE',null,null);
4514 END LOOP;
4515
4516 -- bug 3849198
4517 l_errbuf := 'Updating the duns+4 as expired/deleted based on DUNS';
4518 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4519
4520 update fv_ccr_vendors fcvp set fcvp.ccr_status='E' ,
4521 fcvp.enabled='N',
4522 fcvp.extract_code=decode(fcvp.ccr_status,'E',fcvp.extract_code,'4')
4523 where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
4524 and fcvr.plus_four is null
4525 and fcvr.ccr_status='E' )
4526 and fcvp.ccr_status<>'N';
4527
4528 update fv_ccr_vendors fcvp set fcvp.ccr_status='D' ,
4529 fcvp.enabled='N',
4530 fcvp.extract_code=decode(fcvp.ccr_status,'D',fcvp.extract_code,'1')
4531 where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
4532 and fcvr.plus_four is null
4533 and fcvr.ccr_status='D' )
4534 and fcvp.ccr_status<>'N';
4535
4536
4537 l_errbuf := 'Updating the duns+4 as expired/deleted based on DUNS - Done';
4538 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4539
4540 IF (p_xml_import = 'Y' AND l_xml_opt_param_set = 'Y') THEN -- Bug 3872908
4541 l_verify_existence := 'N';
4542 ELSIF (l_pos23 IN ('SR', 'CR') OR p_xml_import = 'Y') THEN
4543 l_verify_existence := 'Y';
4544 ELSE
4545 l_verify_existence := 'N';
4546 END IF;
4547
4548
4549
4550 IF l_verify_existence = 'Y' THEN
4551 IF l_update_type = 'A' THEN
4552 UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',
4553 CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
4554 WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
4555 WHERE fcv.duns = fcpg.duns
4556 AND NVL(fcv.plus_four, 'NO_PLUS4') =
4557 NVL(fcpg.plus_four, 'NO_PLUS4'))
4558 AND (fcv.ccr_status ='A' OR fcv.ccr_status = 'N');
4559 ELSIF l_update_type = 'S' THEN
4560 --sthota need to check
4561 UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U', 'A', 'A',
4562 CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
4563 WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
4564 WHERE fcv.duns = fcpg.duns
4565 AND NVL(fcv.plus_four, 'NO_PLUS4') =
4566 NVL(fcpg.plus_four, 'NO_PLUS4'))
4567 AND (fcv.ccr_status ='A' OR fcv.ccr_status = 'N')
4568 AND fcv.duns = SUBSTR(p_duns, 1, 9)
4569 AND (p_xml_import <> 'Y' OR
4570 ((NVL(fcv.plus_four, 'NO_PLUS4') = NVL(SUBSTR(p_duns, 10, 4), 'NO_PLUS4'))
4571 OR fcv.plus_four IS NULL));
4572 ELSIF l_update_type = 'N' THEN
4573 UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',
4574 CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
4575 WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
4576 WHERE fcv.duns = fcpg.duns
4577 AND NVL(fcv.plus_four, 'NO_PLUS4') =
4578 NVL(fcpg.plus_four, 'NO_PLUS4'))
4579 AND fcv.ccr_status = 'N';
4580 END IF;
4581 END IF;
4582
4583 -- made this change a part of bug 3872249
4584 update fv_ccr_vendors set extract_code=decode(l_file_type,'M','A','2')
4585 where duns in ( select distinct duns from fv_ccr_process_gt)
4586 and extract_code ='N'
4587 and plus_four is null;
4588
4589 --bug 3931200
4590
4591 -- made this change as part of BUG 3989083
4592 update fv_ccr_vendors fcv set fcv.enabled='Y'
4593 where fcv.enabled='N' and fcv.ccr_status='A'
4594 and not exists (select 1 from fv_ccr_orgs fco
4595 where fco.ccr_id = fcv.ccr_id);
4596 -- BUG 3989083
4597
4598 IF (p_xml_import='Y' and p_insert_data='Y') THEN
4599
4600 FOR crec in (SELECT duns, plus_four from fv_ccr_process_gt fcpg
4601 WHERE fcpg.extract_code in ('1', '4')
4602 AND not exists (SELECT 1 FROM fv_ccr_vendors
4603 WHERE duns = fcpg.duns
4604 AND nvl(plus_four,'N') = nvl(fcpg.plus_four,'N')
4605 ))
4606 LOOP
4607 FND_MESSAGE.set_NAME('FV','FV_CCR_XML_INACTIVE_DUNS');
4608 message_text := FND_MESSAGE.get;
4609 l_errbuf :='Expired/inactive DUNS/DUNS+4: '||crec.duns||'-'||crec.plus_four;
4610 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'main',l_errbuf);
4611 insert_temp_data(3,null,message_text ,null,crec.duns||crec.plus_four,null,null);
4612 END LOOP;
4613 END IF;
4614
4615 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' CCR Data Load Report');
4616 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
4617
4618 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Concurrent Request ID: '||l_request_id);
4619 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Extract File Parameters ');
4620 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
4621 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('File Location: '||p_file_location,75,' ')||rpad('File Name: '||p_file_name,75,' ')||'File Type: '||p_file_type);
4622 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('DUNS Numbers To Query: '||nvl(p_duns,'All'),75,' ')||rpad('Enter DUNS Number: '||nvl(p_duns,'N/A'),75,' '));
4623 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
4624
4625 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'XML Parameters');
4626 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('DUNS Numbers To Query: '||nvl(p_duns,'All'),75,' ')||rpad('Enter DUNS Number: '||nvl(p_duns,'N/A'),35,' ')||'CAGE Code: '||'N/A');
4627 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('Taxpayer Number: '||'N/A',75,' ')||'Registration Status: '||'N/A');
4628 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('Start Date: '||'N/A',75,' ')||'End Date: '||'N/A');
4629
4630 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('DUNS Numbers To Query: '||nvl(p_duns,'All'),75,' ')||rpad('Enter DUNS Number: '||nvl(p_duns,'N/A'),75,' ')||'Insert New Records: '||p_insert_data);
4631
4632 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
4633 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
4634
4635 l_report_count := 0;
4636
4637 FOR l_ccr_rep in c_ccr_rep
4638 LOOP
4639 l_report_count := l_report_count + 1;
4640 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');
4641 IF l_ccr_rep.record_type ='1' THEN
4642 IF (l_title1set = false) THEN
4643 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'====================================================================== DUNS/DUNS+4 Returned ======================================================================= ');
4644 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
4645 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('DUNS/DUNS+4 Number',20,' ')||' '||rpad('Legal Business Name',90,' ')||' '||rpad('CAGE Code',10,' ')||' '||rpad('SSN/TIN',20,' ')||' '||rpad('Registration Status',30,' '));
4646 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('------------------',20,' ')||' '||rpad('-------------------',90,' ')||' '||rpad('---------',10,' ')||' '||rpad('-------',20,' ')||' '||rpad('-------------------',30,' '));
4647 l_title1set := true;
4648 END IF;
4649 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(l_ccr_rep.duns_info,20,' ')||' '||rpad(substr(l_ccr_rep.reference1,1,90),90,' ')||' '||
4650 rpad(l_ccr_rep.reference2,10,' ')||' '||rpad(l_ccr_rep.reference3,20,' ')||' '||l_ccr_rep.reference4);
4651 ELSIF l_ccr_rep.record_type ='2' THEN
4652 IF (l_title2set = false) THEN
4653 if p_xml_import = 'Y' then
4654 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'========================================================================== DUN/DUNS+4 ============================================================================ ');
4655 else
4656 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'====================================================================== New DUN/DUNS+4 ============================================================================ ');
4657 end if;
4658 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
4659 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('DUNS/DUNS+4 Number',20,' ')||' '||rpad('Legal Business Name',90,' ')||' '||'Notes');
4660 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('------------------',20,' ')||' '||rpad('-------------------',90,' ')||' '||'-----');
4661 l_title2set := true;
4662 END IF;
4663 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(l_ccr_rep.duns_info,20,' ')||' '||rpad(substr(l_ccr_rep.reference1,1,90),90,' ')||' '||l_ccr_rep.reference2);
4664
4665 ELSIF l_ccr_rep.record_type ='3' THEN
4666 IF (l_title3set = false) THEN
4667 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'====================================================================== Messages Reported ========================================================================== ');
4668 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
4669 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('Source',20,' ')||' '||rpad('Message',90 ,' ')||' '||'Action');
4670 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('------',20,' ')||' '||rpad('-------',90 ,' ')||' '||'------');
4671 l_title3set := true;
4672 END IF;
4673
4674 IF length(l_ccr_rep.reference1) > 90 THEN
4675 i := 1;
4676 WHILE i < length(l_ccr_rep.reference1)
4677 LOOP
4678 if i = 1 then
4679 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(l_ccr_rep.reference3,20,' ')||' '||substr(l_ccr_rep.reference1,i,90)||' '||l_ccr_rep.reference2);
4680 else
4681 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(' ',20,' ')||' '||substr(l_ccr_rep.reference1,i,90));
4682 end if;
4683 i := i + 90;
4684 END LOOP;
4685 ELSE
4686 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(l_ccr_rep.reference3,20,' ')||' '||rpad(l_ccr_rep.reference1,length(l_ccr_rep.reference1),' ')||' '||l_ccr_rep.reference2);
4687 END IF;
4688
4689 --sthota
4690 ELSIF l_ccr_rep.record_type ='4' THEN
4691 IF (l_title4set = false) THEN
4692 if p_xml_import = 'Y' then
4693 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'====================================================================== Active Vendors - DUN/DUNS+4 ============================================================ ');
4694 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'The following vendors are made active with the current Data Transfer program ');
4695 FND_FILE.put_line(FND_FILE.OUTPUT,'and an Invoice Validation program will be submitted automatically ');
4696 else
4697 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'====================================================================== Active Vendors - DUN/DUNS+4 ============================================================ ');
4698 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'The following vendors are made active with the current Data Transfer program ');
4699 FND_FILE.put_line(FND_FILE.OUTPUT,'and an Invoice Validation program will be submitted automatically ');
4700
4701 end if;
4702 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
4703 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('DUNS/DUNS+4 Number',20,' ')||' '||rpad('Vendor Id',90,' ')||' '||'Vendor Name');
4704 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('------------------',20,' ')||' '||rpad('---------',90,' ')||' '||'-----------');
4705 l_title4set := true;
4706 END IF;
4707 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(l_ccr_rep.duns_info,20,' ')||' '||rpad(substr(l_ccr_rep.reference1,1,90),90,' ')||' '||l_ccr_rep.reference2);
4708
4709
4710 END IF;
4711 END LOOP;
4712
4713 --bug3958492
4714 if l_report_count = 0 then
4715 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'====================================================================== DUNS/DUNS+4 Processed ====================================================================== ');
4716 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
4717 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('DUNS/DUNS+4 Number',20,' ')||' '||rpad('Legal Business Name',90,' ')||' '||rpad('CAGE Code',10,' ')||' '||rpad('Taxpayer ID',20,' ')||' '||rpad('Registration Status',30,' '));
4718 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('------------------',20,' ')||' '||rpad('-------------------',90,' ')||' '||rpad('---------',10,' ')||' '||rpad('-----------',20,' ')||' '||rpad('-------------------',30,' '));
4719 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'None');
4720 end if;
4721
4722
4723 -- purge the temporary tables
4724 delete from fv_ccr_file_temp;
4725 delete from fv_ccr_process_gt;
4726 delete from fv_ccr_process_report ;
4727 commit;
4728
4729 exception
4730 when others then
4731 retcode := -1;
4732 l_errbuf := 'Exception : Inside CCR MAIN ->'||SQLERRM;
4733 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside CCR MAIN ',l_errbuf);
4734 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'====================================================================== DUNS/DUNS+4 Processed ====================================================================== ');
4735 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'PROCESS ENDING WITH EXCEPTION:'|| l_errbuf);
4736 END Main;
4737
4738 -------------------------------------------------------------------------------
4739 --Bug 8335551
4740 --Retrieve the 2 digit territory code from the passed 3 digit
4741 --iso territory code.
4742 --If a row is not found in fnd_territories for the passed
4743 --3 digit iso territory code, then pass back the 2 digit iso territory code so
4744 --that the CCR code will behave as it does before this fix was done.
4745 FUNCTION get_territory_code(p_iso_territory_code IN VARCHAR2)
4746 RETURN varchar2 IS
4747
4748 l_module_name VARCHAR2(1000):= 'fv_ccr_data_load_pkg.get_territory_code';
4749 l_2char_territory_code VARCHAR2(2);
4750
4751 BEGIN
4752
4753 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'In: '||l_module_name);
4754
4755 SELECT territory_code
4756 INTO l_2char_territory_code
4757 FROM fnd_territories
4758 WHERE iso_territory_code = p_iso_territory_code;
4759
4760 RETURN l_2char_territory_code;
4761
4762 EXCEPTION
4763 --In case a 2 digit code is not found in the table for
4764 --the passed 3 digit code send back the 3 digit code
4765 WHEN NO_DATA_FOUND THEN RETURN p_iso_territory_code;
4766
4767 WHEN OTHERS THEN
4768 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'When others error in: '||
4769 l_module_name||':'||SQLERRM);
4770 END;
4771 -------------------------------------------------------------------------------
4772
4773
4774 begin
4775 open c_bus_codes;
4776 fetch c_bus_codes bulk collect into bus_code;
4777 close c_bus_codes;
4778
4779 open c_sic_codes;
4780 fetch c_sic_codes bulk collect into sic_code;
4781 close c_sic_codes;
4782
4783 open c_naic_codes;
4784 fetch c_naic_codes bulk collect into naic_code;
4785 close c_naic_codes;
4786
4787 open c_fsc_codes;
4788 fetch c_fsc_codes bulk collect into fsc_code;
4789 close c_fsc_codes;
4790
4791 open c_psc_codes;
4792 fetch c_psc_codes bulk collect into psc_code;
4793 close c_psc_codes;
4794
4795 end FV_CCR_DATA_LOAD_PKG;