[Home] [Help]
58: p_reference5
59: );
60:
61: EXCEPTION WHEN OTHERS THEN
62: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Insert into temp table',SQLERRM);
63: END;
64:
65: PROCEDURE find_code ( p_lookup IN OUT NOCOPY lookup_data
66: )
82: l_lookup_code fnd_lookup_values.lookup_code%type;
83:
84: BEGIN
85: l_errbuf := 'Start - > find code ';
86: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find code ',l_errbuf);
87:
88: FOR i in p_lookup.first..p_lookup.last
89: LOOP
90: code_exist := false ;
110:
111: message_action := FND_MESSAGE.get;
112:
113: l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
114: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
115: insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
116: END IF;
117: ELSIF (p_lookup(i).rec_type ='B' and p_lookup(i).code = ' ') THEN
118: p_lookup(i).code := null;
141:
142: message_action := FND_MESSAGE.get;
143:
144: l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
145: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
146: insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
147: END IF;
148: ELSIF (p_lookup(i).rec_type ='S' and p_lookup(i).code = ' ') THEN
149: p_lookup(i).code := null;
168:
169: message_action := FND_MESSAGE.get;
170:
171: l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
172: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
173: insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
174: END IF;
175: ELSIF (p_lookup(i).rec_type ='N' and p_lookup(i).code = ' ') THEN
176: p_lookup(i).code := null;
196:
197: message_action := FND_MESSAGE.get;
198:
199: l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
200: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
201: insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
202: END IF;
203: ELSIF (p_lookup(i).rec_type ='F' and p_lookup(i).code = ' ') THEN
204: p_lookup(i).code := null;
224:
225: message_action := FND_MESSAGE.get;
226:
227: l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
228: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
229: insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
230: END IF;
231: ELSIF (p_lookup(i).rec_type ='P' and p_lookup(i).code = ' ') THEN
232: p_lookup(i).code := null;
250:
251: message_action := FND_MESSAGE.get;
252:
253: l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
254: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
255: insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
256: END IF;
257: ELSIF (p_lookup(i).rec_type ='O' and p_lookup(i).code = ' ') THEN
258: p_lookup(i).code := null;
275:
276: message_action := FND_MESSAGE.get;
277:
278: l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
279: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
280: insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
281: END IF;
282: ELSIF (p_lookup(i).rec_type ='C' and p_lookup(i).code = ' ') THEN
283: p_lookup(i).code := null;
305:
306: message_action := FND_MESSAGE.get;
307:
308: l_errbuf :=p_lookup(i).code||' -> Code does not exist' ;
309: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
310: insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
311: END IF;
312: ELSIF ( (p_lookup(i).rec_type ='CS' or p_lookup(i).rec_type ='ES' )
313: and p_lookup(i).code = ' '
320: IF c_lookup_info%isopen then
321: close c_lookup_info;
322: END IF;
323: l_errbuf := 'unexpected exception ' || SQLERRM;
324: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find code',l_errbuf);
325: END;
326:
327: PROCEDURE MAIN( errbuf OUT NOCOPY VARCHAR2,
328: retcode OUT NOCOPY NUMBER,
461:
462: l_module_name := 'CCR Data Load Tfr';
463: l_errbuf :='Start of Transfer';
464:
465: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
466:
467: -- construct the file name from the file location and name
468: -- file name ( '\\' --> for WINDOWS NT , '/' --> UNIX )
469:
491:
492: IF (INSTR(p_file_location, '/') <> 0 ) THEN
493: l_data_file := p_file_location || '/' || p_file_name;
494: l_errbuf := 'Unix directory -'||l_data_file;
495: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
496: ELSE
497: l_data_file := p_file_location || '\\' || p_file_name;
498: l_errbuf := 'NT Directory file name - '||l_data_file;
499: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
495: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
496: ELSE
497: l_data_file := p_file_location || '\\' || p_file_name;
498: l_errbuf := 'NT Directory file name - '||l_data_file;
499: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
500:
501: END IF;
502:
503:
505: -- Position #1
506: IF (substr(p_file_name,1,1) <> 'B') THEN
507: l_invalid_file_name := TRUE;
508: l_errbuf := 'Error in Position#1 of filename';
509: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
510:
511: FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_NAME');
512: FND_MESSAGE.SET_TOKEN('FILE',p_file_name);
513: l_msg_inv_file_name := FND_MESSAGE.GET;
520: l_pos23 := substr(p_file_name,2,2) ;
521: IF (l_pos23 not in ('CR','CD')) THEN
522: l_invalid_file_name := TRUE;
523: l_errbuf := 'Error in Position#2-3 of filename';
524: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
525:
526: FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_NAME');
527: FND_MESSAGE.SET_TOKEN('FILE',p_file_name);
528: l_msg_inv_file_name := FND_MESSAGE.GET;
531:
532: --find out if master/sensitive
533: /*
534: l_errbuf := 'File Type '|| p_file_type;
535: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
536:
537: IF ((p_file_type ='M' and l_pos23 not in ('CR','CD'))
538: OR
539: (p_file_type ='S' and l_pos23 not in ('SR','SD')) ) THEN
538: OR
539: (p_file_type ='S' and l_pos23 not in ('SR','SD')) ) THEN
540: l_invalid_file_name := TRUE;
541: l_errbuf := 'The file name does not match the file type';
542: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
543:
544: FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_NAME');
545: FND_MESSAGE.SET_TOKEN('FILE',p_file_name);
546: l_msg_inv_file_name := FND_MESSAGE.GET;
550:
551: -- Throw error if invalid file name
552: IF l_invalid_file_name THEN
553: retcode :=-1;
554: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
555: RETURN;
556: END IF;
557:
558: -- Julian date validation
561: select to_date(l_juliandate,'YYDDD') into l_file_date from dual;
562: exception when others THEN
563: retcode :=-1;
564: l_errbuf := SQLERRM;
565: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
566:
567: FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_NAME');
568: FND_MESSAGE.SET_TOKEN('FILE',p_file_name);
569: l_msg_inv_file_name := FND_MESSAGE.GET;
583:
584: --submit the request for SQLLOAD
585: -- IF (p_file_type ='M' ) THEN
586: l_errbuf := 'Submitting the Master Complete Load request ';
587: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
588:
589: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
590: application => 'FV',
591: program => 'FVCCRLDC',
594: sub_request => FALSE ,
595: argument1 => l_data_file ) ;
596: commit;
597: l_errbuf :='Request Id - >'||l_request_id||SQLERRM;
598: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
599:
600: /* ELSIF (p_file_type ='S') THEN
601: l_errbuf :='Submitting Sensitive Load Request';
602: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
598: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
599:
600: /* ELSIF (p_file_type ='S') THEN
601: l_errbuf :='Submitting Sensitive Load Request';
602: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
603:
604: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
605: application => 'FV',
606: program => 'FVCCRLDS',
609: sub_request => FALSE ,
610: argument1 => l_data_file ) ;
611: commit;
612: l_errbuf :='Request Id - >'||l_request_id||SQLERRM;
613: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
614:
615: END IF;*/
616:
617:
617:
618: IF (l_request_id = 0) THEN
619: retcode := -1;
620: l_errbuf := 'Failed to submit request for SQL*LOADER';
621: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
622: RETURN;
623: ELSE
624: COMMIT;
625: END IF;
638:
639: -- end of SQL LOAD
640:
641: l_errbuf := 'SQL Loader completed upload successfully in Status '||p_status;
642: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
643:
644: -- incase the request completed in error , parent request also should be errored
645: IF p_status ='Error' THEN
646: retcode := -1;
644: -- incase the request completed in error , parent request also should be errored
645: IF p_status ='Error' THEN
646: retcode := -1;
647: l_errbuf := 'Loader Request Errored out';
648: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
649: RETURN;
650: END IF;
651: /*
652: -- find out the type of file - monthly extract /daily extract
652: -- find out the type of file - monthly extract /daily extract
653: IF l_pos23 = 'CR' THEN
654: --TODO check for exception thrown by this select statement
655: l_errbuf := 'Checking the info in Extract code';
656: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
657:
658: IF p_status ='Warning' THEN
659: SELECT count(1) into dummy from fv_ccr_file_temp;
660: IF dummy >=1 THEN
661: SELECT extract_code into l_extract_type from fv_ccr_file_temp WHERE rownum=1;
662: ELSE
663: retcode := -1;
664: l_errbuf := 'Not even a single row was processed in Loader. Please verify';
665: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
666: RETURN;
667: END IF;
668: END IF;
669:
671: -- if extract_code is not 'A' for a monthly file type then error out
672: IF l_extract_type <>'A' THEN
673: retcode := -1;
674: l_errbuf := 'Not a valid Monthly refresh file ';
675: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
676: insert_temp_data(3,null,'FV_CCR_INVALID_FILE_TYPE',l_message_action2,null,null,null);
677: RETURN;
678: END IF; -- end of extract type check in 'M'
679:
678: END IF; -- end of extract type check in 'M'
679:
680: ELSIF l_pos23 = 'CD' THEN
681: l_errbuf := 'Checking the info in Extract code';
682: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
683:
684: IF p_status ='Warning' THEN
685: SELECT count(1) into dummy from fv_ccr_file_temp;
686: IF dummy >=1 THEN
687: SELECT extract_code into l_extract_type from fv_ccr_file_temp WHERE rownum=1;
688: ELSE
689: retcode := -1;
690: l_errbuf := 'Not even a single row was processed in Loader. Please verify';
691: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
692: RETURN;
693: END IF;
694: END IF;
695:
697: -- if extract_code is 'A' for sensitive data file type then error out
698: IF l_extract_type = 'A' THEN
699: retcode := -1;
700: l_errbuf := 'Not a valid Daily Refresh file';
701: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
702:
703: FND_MESSAGE.SET_NAME('FV','FV_CCR_INVALID_FILE_TYPE');
704: l_msg_inv_file_type := FND_MESSAGE.GET;
705: insert_temp_data(3,null,l_msg_inv_file_type,l_message_action2,null,null,null);
713: SELECT count(1) into dummy from fv_ccr_bpn_clob_data;
714: IF dummy <=0 THEN
715: retcode := -1;
716: l_errbuf := 'PLEASE VERIFY DATA LOADER LOG AS ALL ROWS ARE NOT LOADED.DATA FILE HAS FEW BAD ROWS NOT LOADED.';
717: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
718: RETURN;
719: END IF;
720: END IF;
721:
720: END IF;
721:
722:
723: l_errbuf := 'Calling BPN Load package to load data into fv_ccr_file_temp';
724: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
725: FV_CCR_BPN_LOAD_PKG.MAIN();
726:
727: l_errbuf := 'Push data into fv_ccr_process_gt based on the update type';
728: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
724: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
725: FV_CCR_BPN_LOAD_PKG.MAIN();
726:
727: l_errbuf := 'Push data into fv_ccr_process_gt based on the update type';
728: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
729:
730: -- reset the status in fv_ccr_vendors as 'N' for extract code
731: UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
732:
734: --This program is called from xml import
735: -- Added the below code as Data Load will be called from xml with update_type A
736: UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
737: l_errbuf := 'The program is being called from xml import -> xml-import parameter '|| p_xml_import;
738: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
739: END IF ; --end of xml_import <> Y
740:
741: l_run_from_xml := p_xml_import;
742: -- push data into fv_ccr_process_gt based on the update type
742: -- push data into fv_ccr_process_gt based on the update type
743: IF (l_update_type ='A') THEN
744:
745: l_errbuf :='Update type A - Inserting into second temp table';
746: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
747: --check if atleast one record exists
748: Begin
749:
750: --need to process all data if insert is Yes.
749:
750: --need to process all data if insert is Yes.
751: IF (p_xml_import ='Y' and p_insert_data ='Y') THEN
752: l_errbuf := 'Copying info - xml import';
753: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
754: INSERT INTO fv_ccr_process_gt ( FILE_DATE
755: ,DUNS
756: ,PLUS_FOUR
757: ,CAGE_CODE
1201: FROM fv_ccr_file_temp fcft
1202: order by rowid;
1203: ELSE
1204: l_errbuf := 'Copying info - Standalone / insert as N';
1205: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
1206:
1207: INSERT INTO fv_ccr_process_gt ( FILE_DATE
1208: ,DUNS
1209: ,PLUS_FOUR
1660: END IF;
1661:
1662: exception when no_data_found then
1663: l_errbuf :='No records found for DUNS in FV Extension Tables';
1664: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
1665: insert_temp_data(3,null,l_msg_no_duns,l_message_action3,null,null,null);
1666: end;
1667: ELSIF (l_update_type ='N') THEN
1668: l_errbuf :='Update Type as N - inserting into second temp table';
1665: insert_temp_data(3,null,l_msg_no_duns,l_message_action3,null,null,null);
1666: end;
1667: ELSIF (l_update_type ='N') THEN
1668: l_errbuf :='Update Type as N - inserting into second temp table';
1669: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
1670:
1671: INSERT INTO fv_ccr_process_gt ( FILE_DATE
1672: ,DUNS
1673: ,PLUS_FOUR
2125:
2126: ELSIF (l_update_type ='S') THEN
2127:
2128: l_errbuf := 'Update type as S - inserting into seciond temp table';
2129: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2130: INSERT INTO fv_ccr_process_gt ( FILE_DATE
2131: ,DUNS
2132: ,PLUS_FOUR
2133: ,CAGE_CODE
2582:
2583: END IF;
2584:
2585: l_errbuf := 'Processing data for Extract Code as 1 ';
2586: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2587:
2588: -- set the status to disabled for code as 1
2589: UPDATE fv_ccr_vendors fcv SET fcv.ccr_status ='D',
2590: fcv.enabled ='N' ,
2597: AND nvl(fcv.plus_four,-99)= nvl(fcpg.plus_four,-99)
2598: AND fcpg.extract_code = '1');
2599:
2600: l_errbuf := 'Processing data for Extract Code as 4 ';
2601: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2602: UPDATE fv_ccr_vendors fcv SET ccr_status ='E',
2603: enabled='N',
2604: extract_code ='4',
2605: last_update_date = sysdate,
2617: WHERE fcpg.extract_code IN ('1','4');
2618:
2619:
2620: l_errbuf := 'Processing CCR Data for a,2,3';
2621: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2622:
2623: FND_MESSAGE.SET_NAME('FV','FV_CCR_ASSIGN_PAY_OBJ');
2624: l_msg_pay_obj := FND_MESSAGE.GET;
2625:
2627: LOOP
2628:
2629:
2630: l_errbuf := 'Processing DUNS - > '|| l_ccr_data.duns;
2631: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2632:
2633: l_valid_tin := nvl(l_ccr_data.taxpayer_id,l_ccr_data.social_security_number);
2634: IF((l_valid_tin IS NULL or length(l_valid_tin)<>9)
2635: AND l_ccr_data.country = 'USA') THEN
2635: AND l_ccr_data.country = 'USA') THEN
2636: FND_MESSAGE.set_NAME('FV','FV_CCR_INVALID_TAXPAYER_NUMBER');
2637: message_text := FND_MESSAGE.get;
2638: l_errbuf :='Invalid Taxpayer Number: '||nvl(l_valid_tin,'null');
2639: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'main',l_errbuf);
2640: insert_temp_data(3,null,message_text ,null,l_ccr_data.duns||nvl(l_ccr_data.plus_four,''),null,null);
2641:
2642: ELSE
2643:
2793: l_code(74).rec_type:='ES' ;
2794:
2795: /*bug 3897523, no longer need to check codes against lookups.
2796: l_errbuf := 'calling - > find code ';
2797: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2798:
2799: find_code(l_code);
2800: l_errbuf := 'after -> find code';
2801: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2797: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2798:
2799: find_code(l_code);
2800: l_errbuf := 'after -> find code';
2801: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2802: */
2803: -- set the status as active
2804: l_status :='A';
2805:
2811: if l_ccr_data.renewal_date > trunc(sysdate) then
2812:
2813: if l_active_vendor_id is not null then
2814: l_errbuf := 'Found New Active Vendor Id:'||l_active_vendor_id||' For DUNS: '||l_ccr_data.duns;
2815: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2816:
2817: l_active_vendor_exists := 0;
2818:
2819: if l_vendor_cnt > 0 then
2833:
2834: else
2835:
2836: l_errbuf := 'Found No Vendor Id:'||l_active_vendor_id||' For New Active DUNS: '||l_ccr_data.duns;
2837: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2838:
2839: end if; --if vendor id is null
2840: end if; --renewal date
2841:
2845:
2846: IF l_ccr_data.plus_four is not null THEN
2847: BEGIN
2848: l_errbuf := 'Processing -> DUN+4 now '||l_ccr_data.plus_four;
2849: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2850:
2851: SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv
2852: WHERE fcv.duns = l_ccr_data.duns
2853: AND fcv.plus_four= l_ccr_data.plus_four ;
2852: WHERE fcv.duns = l_ccr_data.duns
2853: AND fcv.plus_four= l_ccr_data.plus_four ;
2854: -- if this select does not return rows ,we need to update DUNS and duns+4 information
2855: l_errbuf := 'DUNS+4 exists';
2856: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2857:
2858: --validate for the renewal date)
2859: IF (l_ccr_data.renewal_date < trunc(sysdate) ) THEN
2860: l_status:='E';
2861: END IF; --end of renewal date
2862:
2863: --call to update the DUNS record
2864: l_errbuf := 'Updating DUNS+4 info ';
2865: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2866:
2867: -- BUG 3839843
2868: IF (l_lbe_change <> l_ccr_data.legal_bus_name) THEN
2869:
2870: FND_MESSAGE.set_NAME('FV','FV_CCR_LBE_CHANGED');
2871: message_text := FND_MESSAGE.get;
2872:
2873: l_errbuf :='Legal Bus Name changed from '||l_lbe_change||' -> '||l_ccr_data.legal_bus_name;
2874: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
2875: insert_temp_data(3,null,message_text ,null,l_ccr_data.duns||l_ccr_data.plus_four,null,null);
2876:
2877: END IF;
2878:
3118: ,fcv.GLOBAL_PARENT_PHONE =l_ccr_data.GLOBAL_PARENT_PHONE
3119: WHERE fcv.duns= l_ccr_data.duns
3120: AND nvl(fcv.plus_four,-99)= nvl(l_ccr_data.plus_four,-99) ;
3121: l_errbuf := 'Updating DUNS+4 info - done ';
3122: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3123:
3124: --add into the plus four processed section of the report
3125: insert_temp_data(1,l_ccr_data.duns||l_ccr_data.plus_four,l_ccr_data.legal_bus_name,l_ccr_data.cage_code,nvl(l_ccr_data.taxpayer_id,l_ccr_data.social_security_number),l_status,null);
3126:
3126:
3127: exception
3128: when no_data_found then
3129: l_errbuf := 'No data found for duns plus four ';
3130: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3131:
3132: -- This case DUns+4 doesn ot exist
3133: l_errbuf := 'DUNS+4 does not exist in FV_CCR_VENDORS';
3134: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3130: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3131:
3132: -- This case DUns+4 doesn ot exist
3133: l_errbuf := 'DUNS+4 does not exist in FV_CCR_VENDORS';
3134: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3135:
3136: IF (p_xml_import = 'N' OR p_insert_data = 'Y') THEN -- bug 3931251
3137:
3138: IF (l_ccr_data.extract_code ='3') THEN
3136: IF (p_xml_import = 'N' OR p_insert_data = 'Y') THEN -- bug 3931251
3137:
3138: IF (l_ccr_data.extract_code ='3') THEN
3139: l_errbuf := 'Error - the DUNS+4 does not exist in FV_CCR_VENDORS';
3140: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3141: ELSE
3142:
3143: --validate for the renewal date)
3144: IF (l_ccr_data.renewal_date < trunc(sysdate) ) THEN
3149:
3150:
3151: --call procedureto insert duns+4 info
3152: l_errbuf := 'Insert DUNS+4' || l_ccr_data.plus_four;
3153: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3154: INSERT INTO FV_CCR_VENDORS (
3155: CCR_ID ,
3156: ENABLED ,
3157: CCR_FLAG ,
3649: -- call to update business type validation
3650: END IF; -- end of extractcode as '3'
3651: END IF;
3652: when others then
3653: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,SQLERRM);
3654: END; -- end of select for dummy
3655: ELSE
3656: -- this is root DUNS record !!!
3657: BEGIN
3655: ELSE
3656: -- this is root DUNS record !!!
3657: BEGIN
3658: l_errbuf := 'Processing root DUNS -> '||l_ccr_data.duns;
3659: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3660:
3661:
3662: SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv
3663: WHERE fcv.duns = l_ccr_data.duns
3664: AND fcv.plus_four is null;
3665:
3666: -- if this select does not return rows ,we need to update DUNS and duns+4 information
3667: l_errbuf := 'DUNS exists';
3668: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3669:
3670: --BUG 3839843
3671: IF (l_lbe_change <> l_ccr_data.legal_bus_name) THEN
3672:
3673: FND_MESSAGE.set_NAME('FV','FV_CCR_LBE_CHANGED');
3674: message_text := FND_MESSAGE.get;
3675:
3676: l_errbuf :='Legal Bus Name changed from '||l_lbe_change||' -> '||l_ccr_data.legal_bus_name;
3677: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
3678: insert_temp_data(3,null,message_text ,null,l_ccr_data.duns||l_ccr_data.plus_four,null,null);
3679:
3680: END IF;
3681:
3686: END IF; --end of renewal date
3687:
3688: --call to update the DUNS record
3689: l_errbuf := 'Updating Root DUNS info '||l_ccr_data.duns;
3690: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3691:
3692: update fv_ccr_vendors fcv set
3693: fcv.CCR_FLAG ='R' ,
3694: fcv.CCR_STATUS =l_status ,
3938: --add into the duns processed section
3939: insert_temp_data(1,l_ccr_data.duns||l_ccr_data.plus_four,l_ccr_data.legal_bus_name,l_ccr_data.cage_code,nvl(l_ccr_data.taxpayer_id,l_ccr_data.social_security_number),l_status,null);
3940:
3941: l_errbuf := 'Updating Root DUNS info '||l_ccr_data.duns || 'done';
3942: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3943: exception
3944: when NO_data_found THEN
3945: IF (p_xml_import ='N' or p_insert_data='Y') THEN
3946: l_errbuf := 'exception no data found for duns ';
3943: exception
3944: when NO_data_found THEN
3945: IF (p_xml_import ='N' or p_insert_data='Y') THEN
3946: l_errbuf := 'exception no data found for duns ';
3947: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3948:
3949: -- This case DUns doesn ot exist
3950: l_errbuf := 'DUNS does not exist in FV_CCR_VENDORS';
3951: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3947: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3948:
3949: -- This case DUns doesn ot exist
3950: l_errbuf := 'DUNS does not exist in FV_CCR_VENDORS';
3951: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3952:
3953: IF (l_ccr_data.extract_code ='3') THEN
3954: l_errbuf := 'Error - the DUNS does not exist in FV_CCR_VENDORS';
3955: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3951: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3952:
3953: IF (l_ccr_data.extract_code ='3') THEN
3954: l_errbuf := 'Error - the DUNS does not exist in FV_CCR_VENDORS';
3955: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3956: ELSE
3957:
3958: --validate for the renewal date)
3959: IF (l_ccr_data.renewal_date < trunc(sysdate) ) THEN
3963: END IF; -- end of renewal date val
3964:
3965: --call procedureto insert duns info
3966: l_errbuf := 'Insert DUNS' || l_ccr_data.duns;
3967: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3968: INSERT INTO FV_CCR_VENDORS (
3969: CCR_ID ,
3970: ENABLED ,
3971: CCR_FLAG ,
4460: END IF ; -- end of extractcode as '3' root duns
4461: END IF ; -- xml import =y for no data found
4462: when others THEN
4463: l_errbuf := 'Exception occurred while updating root duns '||SQLERRM;
4464: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4465:
4466: END; -- end of begin for root duns
4467:
4468: END IF; -- end of duns+4 not null
4474: for i in 1..l_vendor_cnt
4475: loop
4476: ap_approval_pkg.BATCH_APPROVAL_FOR_VENDOR(vendor_ids(l_vendor_cnt), l_module_name);
4477: l_errbuf := 'New Active Vendor Id: '||vendor_ids(i);
4478: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4479: FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_errbuf);
4480: select vendor_name into l_vendor_name from po_vendors where vendor_id = vendor_ids(i);
4481: insert_temp_data(4,duns_ids(i),vendor_ids(i),l_vendor_name,null,null,null);
4482:
4514: END LOOP;
4515:
4516: -- bug 3849198
4517: l_errbuf := 'Updating the duns+4 as expired/deleted based on DUNS';
4518: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4519:
4520: update fv_ccr_vendors fcvp set fcvp.ccr_status='E' ,
4521: fcvp.enabled='N',
4522: fcvp.extract_code=decode(fcvp.ccr_status,'E',fcvp.extract_code,'4')
4534: and fcvp.ccr_status<>'N';
4535:
4536:
4537: l_errbuf := 'Updating the duns+4 as expired/deleted based on DUNS - Done';
4538: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4539:
4540: IF (p_xml_import = 'Y' AND l_xml_opt_param_set = 'Y') THEN -- Bug 3872908
4541: l_verify_existence := 'N';
4542: ELSIF (l_pos23 IN ('SR', 'CR') OR p_xml_import = 'Y') THEN
4606: LOOP
4607: FND_MESSAGE.set_NAME('FV','FV_CCR_XML_INACTIVE_DUNS');
4608: message_text := FND_MESSAGE.get;
4609: l_errbuf :='Expired/inactive DUNS/DUNS+4: '||crec.duns||'-'||crec.plus_four;
4610: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'main',l_errbuf);
4611: insert_temp_data(3,null,message_text ,null,crec.duns||crec.plus_four,null,null);
4612: END LOOP;
4613: END IF;
4614:
4729: exception
4730: when others then
4731: retcode := -1;
4732: l_errbuf := 'Exception : Inside CCR MAIN ->'||SQLERRM;
4733: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside CCR MAIN ',l_errbuf);
4734: FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'====================================================================== DUNS/DUNS+4 Processed ====================================================================== ');
4735: FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'PROCESS ENDING WITH EXCEPTION:'|| l_errbuf);
4736: END Main;
4737:
4749: l_2char_territory_code VARCHAR2(2);
4750:
4751: BEGIN
4752:
4753: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'In: '||l_module_name);
4754:
4755: SELECT territory_code
4756: INTO l_2char_territory_code
4757: FROM fnd_territories
4764: --the passed 3 digit code send back the 3 digit code
4765: WHEN NO_DATA_FOUND THEN RETURN p_iso_territory_code;
4766:
4767: WHEN OTHERS THEN
4768: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'When others error in: '||
4769: l_module_name||':'||SQLERRM);
4770: END;
4771: -------------------------------------------------------------------------------
4772: