[Home] [Help]
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: )
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 ;
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;
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;
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;
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;
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;
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;
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;
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 = ' '
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,
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:
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);
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:
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;
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;
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
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;
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
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;
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',
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);
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',
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:
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;
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;
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
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:
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:
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:
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);
687:
688: FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_TYPE');
689: l_msg_inv_file_type := FND_MESSAGE.GET;
690: insert_temp_data(3,null,l_msg_inv_file_type,l_message_action2,null,null,null);
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:
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
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.
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
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
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';
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
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
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' ,
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,
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:
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
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:
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);
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:
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 ;
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
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:
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);
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
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
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 ,
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
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:
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 ,
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 ';
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);
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);
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
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 ,
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
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')
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
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;
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;
4804: END IF;
4805:
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: