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