[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR c_bus_codes IS SELECT lookup_code from fnd_lookup_values where lookup_type = 'FV_BUSINESS_TYPE' and language = userenv('LANG');
CURSOR c_sic_codes IS SELECT lookup_code from fnd_lookup_values where lookup_type = 'FV_SIC_TYPE' and language = userenv('LANG');
CURSOR c_naic_codes IS SELECT lookup_code from fnd_lookup_values where lookup_type = 'FV_NAICS_TYPE' and language = userenv('LANG');
CURSOR c_fsc_codes IS SELECT lookup_code from fnd_lookup_values where lookup_type = 'FV_FSC_TYPE' and language = userenv('LANG');
CURSOR c_psc_codes IS SELECT lookup_code from fnd_lookup_values where lookup_type = 'FV_PSC_TYPE' and language = userenv('LANG');
PROCEDURE INSERT_TEMP_DATA( p_record_type number,
p_duns varchar2,
p_reference1 varchar2,
p_reference2 varchar2,
p_reference3 varchar2 ,
p_reference4 varchar2 ,
p_reference5 varchar2 )
IS
BEGIN
INSERT INTO FV_CCR_PROCESS_REPORT
(record_type,
duns_info,
reference1,
reference2,
reference3,
reference4,
reference5
)
VALUES
(p_record_type,
p_duns ,
p_reference1,
p_reference2,
p_reference3,
p_reference4,
p_reference5
);
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Insert into temp table',SQLERRM);
select lookup_code from fnd_lookup_values
where lookup_type = c_type
and lookup_code = c_code
and language = userenv('LANG');
insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
select decode(p_lookup(i).code,'CS','Corporate Security Code', 'Employee Security Code') into l_token
from dual;
insert_temp_data(3,null,message_text ,message_action,'ORACLE',null,null);
p_update_type IN VARCHAR2,
p_dummy IN NUMBER,
p_duns IN VARCHAR2 ,
p_xml_import IN VARCHAR2 ,
p_insert_data IN VARCHAR2
) IS
l_data_file VARCHAR2(100);
l_update_type varchar2(1);
SELECT * from fv_ccr_process_gt g
WHERE ( extract_code in ('A','2','3')
or ( l_run_from_xml = 'Y' and extract_code = '4' -- modified by ks 5906546.
and not exists (select 'first run'
from fv_ccr_orgs o
where o.duns = g.duns)
)
)
order by rowid;
SELECT
DUNS_INFO,
RECORD_TYPE,
NVL(REFERENCE1,' ') REFERENCE1,
NVL(REFERENCE2,' ') REFERENCE2,
NVL(REFERENCE3,' ') REFERENCE3,
DECODE(REFERENCE4,'A','Active','E','Expired','N','Unknown','U','Unregistered',REFERENCE4) REFERENCE4,
REFERENCE5,
REFERENCE6,
REFERENCE7,
REFERENCE8,
REFERENCE9,
REFERENCE10
from fv_ccr_process_report order by record_type,rowid;
select distinct fcv.taxpayer_id,fcv.vendor_id
from fv_ccr_vendors fcv
where exists (SELECT 1 FROM fv_ccr_vendors fcv_in
WHERE fcv_in.taxpayer_id=fcv.taxpayer_id
AND fcv_in.vendor_id = fcv.vendor_id
AND fcv_in.legal_bus_name<>fcv.legal_bus_name
AND fcv_in.taxpayer_id is not null
AND fcv_in.plus_four is null
AND fcv_in.vendor_id is not null
AND fcv_in.ccr_status not in ('E','D'))
AND fcv.plus_four IS NULL
AND fcv.taxpayer_id IS NOT NULL
AND fcv.vendor_id is not null
AND fcv.ccr_status not in ('E','D')
AND exists (select 1 from fv_ccr_process_gt fcpg
where fcpg.duns = fcv.duns and fcpg.plus_four IS NULL);
select fcv.duns,fcv.plus_four ,fcv.legal_bus_name,fcv.taxpayer_id
from fv_ccr_vendors fcv
where fcv.plus_four is null
and fcv.taxpayer_id=p_taxpayer_id
and fcv.vendor_id=p_vendor_id;
select vendor_name from po_vendors
where vendor_id =p_vid;
l_xml_opt_param_set := SUBSTR(p_update_type, 2, 1);
l_update_type := SUBSTR(p_update_type, 1, 1);
SELECT sysdate into l_file_date FROM dual; -- Bug 3931555, 3936532
l_update_type := p_update_type;
insert_temp_data(3,null,l_msg_inv_file_name,l_message_action1,null,null,null);
insert_temp_data(3,null,l_msg_inv_file_name,l_message_action1,null,null,null);
insert_temp_data(3,null,l_msg_inv_file_name,l_message_action4,null,null,null);
select to_date(l_juliandate,'YYDDD') into l_file_date from dual;
insert_temp_data(3,null,l_msg_inv_file_name,l_msg_julian_date,null,null,null);
SELECT count(1) into dummy from fv_ccr_file_temp;
SELECT extract_code into l_extract_type from fv_ccr_file_temp WHERE rownum=1;
insert_temp_data(3,null,'FV_CCR_INVALID_FILE_TYPE',l_message_action2,null,null,null);
SELECT count(1) into dummy from fv_ccr_file_temp;
SELECT extract_code into l_extract_type from fv_ccr_file_temp WHERE rownum=1;
insert_temp_data(3,null,l_msg_inv_file_type,l_message_action2,null,null,null);
l_errbuf := 'Push data into fv_ccr_process_gt based on the update type';
UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
IF (l_update_type ='A') THEN
l_errbuf :='Update type A - Inserting into second temp table';
IF (p_xml_import ='Y' and p_insert_data ='Y') THEN
l_errbuf := 'Copying info - xml import';
INSERT INTO fv_ccr_process_gt ( FILE_DATE
,DUNS
,PLUS_FOUR
,CAGE_CODE
,EXTRACT_CODE
,REGISTRATION_DATE
,RENEWAL_DATE
,LEGAL_BUS_NAME
,DBA_NAME
,DIVISION_NAME
,DIVISION_NUMBER
,ST_ADDRESS1
,ST_ADDRESS2
,CITY
,STATE
,POSTAL_CODE
,COUNTRY
,BUSINESS_START_DATE
,FISCAL_YR_CLOSE_DATE
,CORP_SECURITY_LEVEL
,EMP_SECURITY_LEVEL
,WEB_SITE
,ORGANIZATIONAL_TYPE
,STATE_OF_INC
,COUNTRY_OF_INC
,BUSINESS_TYPES
,SIC_CODES
,NAICS_CODES
,FSC_CODES
,PSC_CODES
,CREDIT_CARD_FLAG
,CORRESPONDENCE_FLAG
,MAIL_POC
,MAIL_ADD1
,MAIL_ADD2
,MAIL_CITY
,MAIL_POSTAL_CODE
,MAIL_COUNTRY
,MAIL_STATE
,PREV_BUS_POC
,PREV_BUS_ADD1
,PREV_BUS_ADD2
,PREV_BUS_CITY
,PREV_BUS_POSTAL_CODE
,PREV_BUS_COUNTRY
,PREV_BUS_STATE
,PARENT_POC
,PARENT_DUNS
,PARENT_ADD1
,PARENT_ADD2
,PARENT_CITY
,PARENT_POSTAL_CODE
,PARENT_COUNTRY
,PARENT_STATE
,PARTY_PERF_POC
,PARTY_PERF_ADD1
,PARTY_PERF_ADD2
,PARTY_PERF_CITY
,PARTY_PERF_POSTAL_CODE
,PARTY_PERF_COUNTRY
,PARTY_PERF_STATE
,GOV_PARENT_POC
,GOV_PARENT_ADD1
,GOV_PARENT_ADD2
,GOV_PARENT_CITY
,GOV_PARENT_POSTAL_CODE
,GOV_PARENT_COUNTRY
,GOV_PARENT_STATE
,GOV_BUS_POC
,GOV_BUS_ADD1
,GOV_BUS_ADD2
,GOV_BUS_CITY
,GOV_BUS_POSTAL_CODE
,GOV_BUS_COUNTRY
,GOV_BUS_STATE
,GOV_BUS_US_PHONE
,GOV_BUS_US_PHONE_EX
,GOV_BUS_NON_US_PHONE
,GOV_BUS_FAX
,GOV_BUS_EMAIL
,ALT_GOV_BUS_POC
,ALT_GOV_BUS_ADD1
,ALT_GOV_BUS_ADD2
,ALT_GOV_BUS_CITY
,ALT_GOV_BUS_POSTAL_CODE
,ALT_GOV_BUS_COUNTRY
,ALT_GOV_BUS_STATE
,ALT_GOV_BUS_US_PHONE
,ALT_GOV_BUS_US_PHONE_EX
,ALT_GOV_BUS_NON_US_PHONE
,ALT_GOV_BUS_FAX
,ALT_GOV_BUS_EMAIL
,PAST_PERF_POC
,PAST_PERF_ADD1
,PAST_PERF_ADD2
,PAST_PERF_CITY
,PAST_PERF_POSTAL_CODE
,PAST_PERF_COUNTRY
,PAST_PERF_STATE
,PAST_PERF_US_PHONE
,PAST_PERF_US_PHONE_EX
,PAST_PERF_NON_US_PHONE
,PAST_PERF_FAX
,PAST_PERF_EMAIL
,ALT_PAST_PERF_POC
,ALT_PAST_PERF_ADD1
,ALT_PAST_PERF_ADD2
,ALT_PAST_PERF_CITY
,ALT_PAST_PERF_POSTAL_CODE
,ALT_PAST_PERF_COUNTRY
,ALT_PAST_PERF_STATE
,ALT_PAST_PERF_US_PHONE
,ALT_PAST_PERF_US_PHONE_EX
,ALT_PAST_PERF_NON_US_PHONE
,ALT_PAST_PERF_FAX
,ALT_PAST_PERF_EMAIL
,ELEC_BUS_POC
,ELEC_BUS_ADD1
,ELEC_BUS_ADD2
,ELEC_BUS_CITY
,ELEC_BUS_POSTAL_CODE
,ELEC_BUS_COUNTRY
,ELEC_BUS_STATE
,ELEC_BUS_US_PHONE
,ELEC_BUS_US_PHONE_EX
,ELEC_BUS_NON_US_PHONE
,ELEC_BUS_FAX
,ELEC_BUS_EMAIL
,ALT_ELEC_BUS_POC
,ALT_ELEC_BUS_ADD1
,ALT_ELEC_BUS_ADD2
,ALT_ELEC_BUS_CITY
,ALT_ELEC_BUS_POSTAL_CODE
,ALT_ELEC_BUS_COUNTRY
,ALT_ELEC_BUS_STATE
,ALT_ELEC_BUS_US_PHONE
,ALT_ELEC_BUS_US_PHONE_EX
,ALT_ELEC_BUS_NON_US_PHONE
,ALT_ELEC_BUS_FAX
,ALT_ELEC_BUS_EMAIL
,CERTIFIER_POC
,CERTIFIER_US_PHONE
,CERTIFIER_US_PHONE_EX
,CERTIFIER_NON_US_PHONE
,CERTIFIER_FAX
,CERTIFIER_EMAIL
,ALT_CERTIFIER_POC
,ALT_CERTIFIER_US_PHONE
,ALT_CERTIFIER_US_PHONE_EX
,ALT_CERTIFIER_NON_US_PHONE
,CORP_INFO_POC
,CORP_INFO_US_PHONE
,CORP_INFO_US_PHONE_EX
,CORP_INFO_NON_US_PHONE
,CORP_INFO_FAX
,CORP_INFO_EMAIL
,OWNER_INFO_POC
,OWNER_INFO_US_PHONE
,OWNER_INFO_US_PHONE_EX
,OWNER_INFO_NON_US_PHONE
,OWNER_INFO_FAX
,OWNER_INFO_EMAIL
,EDI
,TAXPAYER_ID
,AVG_NUM_EMPLOYEES
,ANNUAL_REVENUE
,SOCIAL_SECURITY_NUMBER
,FINANCIAL_INSTITUTE
,BANK_ACCT_NUMBER
,ABA_ROUTING
,BANK_ACCT_TYPE
,LOCKBOX_NUMBER
,AUTHORIZATION_DATE
,EFT_WAIVER
,ACH_US_PHONE
,ACH_NON_US_PHONE
,ACH_FAX
,ACH_EMAIL
,REMIT_POC
,REMIT_ADD1
,REMIT_ADD2
,REMIT_CITY
,REMIT_STATE
,REMIT_POSTAL_CODE
,REMIT_COUNTRY
,AR_POC
,AR_US_PHONE
,AR_US_PHONE_EX
,AR_NON_US_PHONE
,AR_FAX
,AR_EMAIL
,MPIN
,EDI_COORDINATOR
,EDI_US_PHONE
,EDI_US_PHONE_EX
,EDI_NON_US_PHONE
,EDI_FAX
,EDI_EMAIL
,VAN
,ISA_QUALIFIER
,ISA_SENDER_QUALIFIER
,GS02_IDENTIFIER
,RECEIVE_820
,HQ_PARENT_NAME
,HQ_PARENT_DUNS
,HQ_PARENT_ADD1
,HQ_PARENT_ADD2
,HQ_PARENT_CITY
,HQ_PARENT_STATE
,HQ_PARENT_POSTAL_CODE
,HQ_PARENT_COUNTRY
,HQ_PARENT_PHONE
,HQ_PARENT_RECORD_DATE
,GLOBAL_ULT_NAME
,GLOBAL_ULT_DUNS
,GLOBAL_ULT_ADD1
,GLOBAL_ULT_ADD2
,GLOBAL_ULT_CITY
,GLOBAL_ULT_POSTAL_CODE
,GLOBAL_ULT_COUNTRY
,GLOBAL_ULT_STATE
,GLOBAL_ULT_PHONE
,GLOBAL_ULT_RECORD_DATE
,DOMESTIC_ULT_NAME
,DOMESTIC_ULT_DUNS
,DOMESTIC_ULT_ADD1
,DOMESTIC_ULT_ADD2
,DOMESTIC_ULT_CITY
,DOMESTIC_ULT_POSTAL_CODE
,DOMESTIC_ULT_COUNTRY
,DOMESTIC_ULT_STATE
,DOMESTIC_ULT_PHONE
,DOMESTIC_ULT_RECORD_DATE
,ALT_CERTIFIER_FAX
,ALT_CERTIFIER_EMAIL)
SELECT l_file_date
,fcft.DUNS
,replace(fcft.PLUS_FOUR,' ',null)
,fcft.CAGE_CODE
,fcft.EXTRACT_CODE
,fcft.REGISTRATION_DATE
,fcft.RENEWAL_DATE
,fcft.LEGAL_BUS_NAME
,fcft.DBA_NAME
,fcft.DIVISION_NAME
,fcft.DIVISION_NUMBER
,fcft.ST_ADDRESS1
,fcft.ST_ADDRESS2
,fcft.CITY
,fcft.STATE
,fcft.POSTAL_CODE
,fcft.COUNTRY
,fcft.BUSINESS_START_DATE
,fcft.FISCAL_YR_CLOSE_DATE
,fcft.CORP_SECURITY_LEVEL
,fcft.EMP_SECURITY_LEVEL
,fcft.WEB_SITE
,fcft.ORGANIZATIONAL_TYPE
,fcft.STATE_OF_INC
,fcft.COUNTRY_OF_INC
,fcft.BUSINESS_TYPES
,fcft.SIC_CODES
,fcft.NAICS_CODES
,fcft.FSC_CODES
,fcft.PSC_CODES
,fcft.CREDIT_CARD_FLAG
,fcft.CORRESPONDENCE_FLAG
,fcft.MAIL_POC
,fcft.MAIL_ADD1
,fcft.MAIL_ADD2
,fcft.MAIL_CITY
,fcft.MAIL_POSTAL_CODE
,fcft.MAIL_COUNTRY
,fcft.MAIL_STATE
,fcft.PREV_BUS_POC
,fcft.PREV_BUS_ADD1
,fcft.PREV_BUS_ADD2
,fcft.PREV_BUS_CITY
,fcft.PREV_BUS_POSTAL_CODE
,fcft.PREV_BUS_COUNTRY
,fcft.PREV_BUS_STATE
,fcft.PARENT_POC
,fcft.PARENT_DUNS
,fcft.PARENT_ADD1
,fcft.PARENT_ADD2
,fcft.PARENT_CITY
,fcft.PARENT_POSTAL_CODE
,fcft.PARENT_COUNTRY
,fcft.PARENT_STATE
,fcft.PARTY_PERF_POC
,fcft.PARTY_PERF_ADD1
,fcft.PARTY_PERF_ADD2
,fcft.PARTY_PERF_CITY
,fcft.PARTY_PERF_POSTAL_CODE
,fcft.PARTY_PERF_COUNTRY
,fcft.PARTY_PERF_STATE
,fcft.GOV_PARENT_POC
,fcft.GOV_PARENT_ADD1
,fcft.GOV_PARENT_ADD2
,fcft.GOV_PARENT_CITY
,fcft.GOV_PARENT_POSTAL_CODE
,fcft.GOV_PARENT_COUNTRY
,fcft.GOV_PARENT_STATE
,fcft.GOV_BUS_POC
,fcft.GOV_BUS_ADD1
,fcft.GOV_BUS_ADD2
,fcft.GOV_BUS_CITY
,fcft.GOV_BUS_POSTAL_CODE
,fcft.GOV_BUS_COUNTRY
,fcft.GOV_BUS_STATE
,fcft.GOV_BUS_US_PHONE
,fcft.GOV_BUS_US_PHONE_EX
,fcft.GOV_BUS_NON_US_PHONE
,fcft.GOV_BUS_FAX
,fcft.GOV_BUS_EMAIL
,fcft.ALT_GOV_BUS_POC
,fcft.ALT_GOV_BUS_ADD1
,fcft.ALT_GOV_BUS_ADD2
,fcft.ALT_GOV_BUS_CITY
,fcft.ALT_GOV_BUS_POSTAL_CODE
,fcft.ALT_GOV_BUS_COUNTRY
,fcft.ALT_GOV_BUS_STATE
,fcft.ALT_GOV_BUS_US_PHONE
,fcft.ALT_GOV_BUS_US_PHONE_EX
,fcft.ALT_GOV_BUS_NON_US_PHONE
,fcft.ALT_GOV_BUS_FAX
,fcft.ALT_GOV_BUS_EMAIL
,fcft.PAST_PERF_POC
,fcft.PAST_PERF_ADD1
,fcft.PAST_PERF_ADD2
,fcft.PAST_PERF_CITY
,fcft.PAST_PERF_POSTAL_CODE
,fcft.PAST_PERF_COUNTRY
,fcft.PAST_PERF_STATE
,fcft.PAST_PERF_US_PHONE
,fcft.PAST_PERF_US_PHONE_EX
,fcft.PAST_PERF_NON_US_PHONE
,fcft.PAST_PERF_FAX
,fcft.PAST_PERF_EMAIL
,fcft.ALT_PAST_PERF_POC
,fcft.ALT_PAST_PERF_ADD1
,fcft.ALT_PAST_PERF_ADD2
,fcft.ALT_PAST_PERF_CITY
,fcft.ALT_PAST_PERF_POSTAL_CODE
,fcft.ALT_PAST_PERF_COUNTRY
,fcft.ALT_PAST_PERF_STATE
,fcft.ALT_PAST_PERF_US_PHONE
,fcft.ALT_PAST_PERF_US_PHONE_EX
,fcft.ALT_PAST_PERF_NON_US_PHONE
,fcft.ALT_PAST_PERF_FAX
,fcft.ALT_PAST_PERF_EMAIL
,fcft.ELEC_BUS_POC
,fcft.ELEC_BUS_ADD1
,fcft.ELEC_BUS_ADD2
,fcft.ELEC_BUS_CITY
,fcft.ELEC_BUS_POSTAL_CODE
,fcft.ELEC_BUS_COUNTRY
,fcft.ELEC_BUS_STATE
,fcft.ELEC_BUS_US_PHONE
,fcft.ELEC_BUS_US_PHONE_EX
,fcft.ELEC_BUS_NON_US_PHONE
,fcft.ELEC_BUS_FAX
,fcft.ELEC_BUS_EMAIL
,fcft.ALT_ELEC_BUS_POC
,fcft.ALT_ELEC_BUS_ADD1
,fcft.ALT_ELEC_BUS_ADD2
,fcft.ALT_ELEC_BUS_CITY
,fcft.ALT_ELEC_BUS_POSTAL_CODE
,fcft.ALT_ELEC_BUS_COUNTRY
,fcft.ALT_ELEC_BUS_STATE
,fcft.ALT_ELEC_BUS_US_PHONE
,fcft.ALT_ELEC_BUS_US_PHONE_EX
,fcft.ALT_ELEC_BUS_NON_US_PHONE
,fcft.ALT_ELEC_BUS_FAX
,fcft.ALT_ELEC_BUS_EMAIL
,fcft.CERTIFIER_POC
,fcft.CERTIFIER_US_PHONE
,fcft.CERTIFIER_US_PHONE_EX
,fcft.CERTIFIER_NON_US_PHONE
,fcft.CERTIFIER_FAX
,fcft.CERTIFIER_EMAIL
,fcft.ALT_CERTIFIER_POC
,fcft.ALT_CERTIFIER_US_PHONE
,fcft.ALT_CERTIFIER_US_PHONE_EX
,fcft.ALT_CERTIFIER_NON_US_PHONE
,fcft.CORP_INFO_POC
,fcft.CORP_INFO_US_PHONE
,fcft.CORP_INFO_US_PHONE_EX
,fcft.CORP_INFO_NON_US_PHONE
,fcft.CORP_INFO_FAX
,fcft.CORP_INFO_EMAIL
,fcft.OWNER_INFO_POC
,fcft.OWNER_INFO_US_PHONE
,fcft.OWNER_INFO_US_PHONE_EX
,fcft.OWNER_INFO_NON_US_PHONE
,fcft.OWNER_INFO_FAX
,fcft.OWNER_INFO_EMAIL
,fcft.EDI
,fcft.TAXPAYER_ID
,fcft.AVG_NUM_EMPLOYEES
,fcft.ANNUAL_REVENUE
,fcft.SOCIAL_SECURITY_NUMBER
,fcft.FINANCIAL_INSTITUTE
,fcft.BANK_ACCT_NUMBER
,fcft.ABA_ROUTING
,fcft.BANK_ACCT_TYPE
,fcft.LOCKBOX_NUMBER
,fcft.AUTHORIZATION_DATE
,fcft.EFT_WAIVER
,fcft.ACH_US_PHONE
,fcft.ACH_NON_US_PHONE
,fcft.ACH_FAX
,fcft.ACH_EMAIL
,fcft.REMIT_POC
,fcft.REMIT_ADD1
,fcft.REMIT_ADD2
,fcft.REMIT_CITY
,fcft.REMIT_STATE
,fcft.REMIT_POSTAL_CODE
,fcft.REMIT_COUNTRY
,fcft.AR_POC
,fcft.AR_US_PHONE
,fcft.AR_US_PHONE_EX
,fcft.AR_NON_US_PHONE
,fcft.AR_FAX
,fcft.AR_EMAIL
,fcft.MPIN
,fcft.EDI_COORDINATOR
,fcft.EDI_US_PHONE
,fcft.EDI_US_PHONE_EX
,fcft.EDI_NON_US_PHONE
,fcft.EDI_FAX
,fcft.EDI_EMAIL
,fcft.VAN
,fcft.ISA_QUALIFIER
,fcft.ISA_SENDER_QUALIFIER
,fcft.GS02_IDENTIFIER
,fcft.RECEIVE_820
,fcft.HQ_PARENT_NAME
,fcft.HQ_PARENT_DUNS
,fcft.HQ_PARENT_ADD1
,fcft.HQ_PARENT_ADD2
,fcft.HQ_PARENT_CITY
,fcft.HQ_PARENT_STATE
,fcft.HQ_PARENT_POSTAL_CODE
,fcft.HQ_PARENT_COUNTRY
,fcft.HQ_PARENT_PHONE
,fcft.HQ_PARENT_RECORD_DATE
,fcft.GLOBAL_ULT_NAME
,fcft.GLOBAL_ULT_DUNS
,fcft.GLOBAL_ULT_ADD1
,fcft.GLOBAL_ULT_ADD2
,fcft.GLOBAL_ULT_CITY
,fcft.GLOBAL_ULT_POSTAL_CODE
,fcft.GLOBAL_ULT_COUNTRY
,fcft.GLOBAL_ULT_STATE
,fcft.GLOBAL_ULT_PHONE
,fcft.GLOBAL_ULT_RECORD_DATE
,fcft.DOMESTIC_ULT_NAME
,fcft.DOMESTIC_ULT_DUNS
,fcft.DOMESTIC_ULT_ADD1
,fcft.DOMESTIC_ULT_ADD2
,fcft.DOMESTIC_ULT_CITY
,fcft.DOMESTIC_ULT_POSTAL_CODE
,fcft.DOMESTIC_ULT_COUNTRY
,fcft.DOMESTIC_ULT_STATE
,fcft.DOMESTIC_ULT_PHONE
,fcft.DOMESTIC_ULT_RECORD_DATE
,fcft.ALT_CERTIFIER_FAX
,fcft.ALT_CERTIFIER_EMAIL
FROM fv_ccr_file_temp fcft
order by rowid;
l_errbuf := 'Copying info - Standalone / insert as N';
INSERT INTO fv_ccr_process_gt ( FILE_DATE
,DUNS
,PLUS_FOUR
,CAGE_CODE
,EXTRACT_CODE
,REGISTRATION_DATE
,RENEWAL_DATE
,LEGAL_BUS_NAME
,DBA_NAME
,DIVISION_NAME
,DIVISION_NUMBER
,ST_ADDRESS1
,ST_ADDRESS2
,CITY
,STATE
,POSTAL_CODE
,COUNTRY
,BUSINESS_START_DATE
,FISCAL_YR_CLOSE_DATE
,CORP_SECURITY_LEVEL
,EMP_SECURITY_LEVEL
,WEB_SITE
,ORGANIZATIONAL_TYPE
,STATE_OF_INC
,COUNTRY_OF_INC
,BUSINESS_TYPES
,SIC_CODES
,NAICS_CODES
,FSC_CODES
,PSC_CODES
,CREDIT_CARD_FLAG
,CORRESPONDENCE_FLAG
,MAIL_POC
,MAIL_ADD1
,MAIL_ADD2
,MAIL_CITY
,MAIL_POSTAL_CODE
,MAIL_COUNTRY
,MAIL_STATE
,PREV_BUS_POC
,PREV_BUS_ADD1
,PREV_BUS_ADD2
,PREV_BUS_CITY
,PREV_BUS_POSTAL_CODE
,PREV_BUS_COUNTRY
,PREV_BUS_STATE
,PARENT_POC
,PARENT_DUNS
,PARENT_ADD1
,PARENT_ADD2
,PARENT_CITY
,PARENT_POSTAL_CODE
,PARENT_COUNTRY
,PARENT_STATE
,PARTY_PERF_POC
,PARTY_PERF_ADD1
,PARTY_PERF_ADD2
,PARTY_PERF_CITY
,PARTY_PERF_POSTAL_CODE
,PARTY_PERF_COUNTRY
,PARTY_PERF_STATE
,GOV_PARENT_POC
,GOV_PARENT_ADD1
,GOV_PARENT_ADD2
,GOV_PARENT_CITY
,GOV_PARENT_POSTAL_CODE
,GOV_PARENT_COUNTRY
,GOV_PARENT_STATE
,GOV_BUS_POC
,GOV_BUS_ADD1
,GOV_BUS_ADD2
,GOV_BUS_CITY
,GOV_BUS_POSTAL_CODE
,GOV_BUS_COUNTRY
,GOV_BUS_STATE
,GOV_BUS_US_PHONE
,GOV_BUS_US_PHONE_EX
,GOV_BUS_NON_US_PHONE
,GOV_BUS_FAX
,GOV_BUS_EMAIL
,ALT_GOV_BUS_POC
,ALT_GOV_BUS_ADD1
,ALT_GOV_BUS_ADD2
,ALT_GOV_BUS_CITY
,ALT_GOV_BUS_POSTAL_CODE
,ALT_GOV_BUS_COUNTRY
,ALT_GOV_BUS_STATE
,ALT_GOV_BUS_US_PHONE
,ALT_GOV_BUS_US_PHONE_EX
,ALT_GOV_BUS_NON_US_PHONE
,ALT_GOV_BUS_FAX
,ALT_GOV_BUS_EMAIL
,PAST_PERF_POC
,PAST_PERF_ADD1
,PAST_PERF_ADD2
,PAST_PERF_CITY
,PAST_PERF_POSTAL_CODE
,PAST_PERF_COUNTRY
,PAST_PERF_STATE
,PAST_PERF_US_PHONE
,PAST_PERF_US_PHONE_EX
,PAST_PERF_NON_US_PHONE
,PAST_PERF_FAX
,PAST_PERF_EMAIL
,ALT_PAST_PERF_POC
,ALT_PAST_PERF_ADD1
,ALT_PAST_PERF_ADD2
,ALT_PAST_PERF_CITY
,ALT_PAST_PERF_POSTAL_CODE
,ALT_PAST_PERF_COUNTRY
,ALT_PAST_PERF_STATE
,ALT_PAST_PERF_US_PHONE
,ALT_PAST_PERF_US_PHONE_EX
,ALT_PAST_PERF_NON_US_PHONE
,ALT_PAST_PERF_FAX
,ALT_PAST_PERF_EMAIL
,ELEC_BUS_POC
,ELEC_BUS_ADD1
,ELEC_BUS_ADD2
,ELEC_BUS_CITY
,ELEC_BUS_POSTAL_CODE
,ELEC_BUS_COUNTRY
,ELEC_BUS_STATE
,ELEC_BUS_US_PHONE
,ELEC_BUS_US_PHONE_EX
,ELEC_BUS_NON_US_PHONE
,ELEC_BUS_FAX
,ELEC_BUS_EMAIL
,ALT_ELEC_BUS_POC
,ALT_ELEC_BUS_ADD1
,ALT_ELEC_BUS_ADD2
,ALT_ELEC_BUS_CITY
,ALT_ELEC_BUS_POSTAL_CODE
,ALT_ELEC_BUS_COUNTRY
,ALT_ELEC_BUS_STATE
,ALT_ELEC_BUS_US_PHONE
,ALT_ELEC_BUS_US_PHONE_EX
,ALT_ELEC_BUS_NON_US_PHONE
,ALT_ELEC_BUS_FAX
,ALT_ELEC_BUS_EMAIL
,CERTIFIER_POC
,CERTIFIER_US_PHONE
,CERTIFIER_US_PHONE_EX
,CERTIFIER_NON_US_PHONE
,CERTIFIER_FAX
,CERTIFIER_EMAIL
,ALT_CERTIFIER_POC
,ALT_CERTIFIER_US_PHONE
,ALT_CERTIFIER_US_PHONE_EX
,ALT_CERTIFIER_NON_US_PHONE
,CORP_INFO_POC
,CORP_INFO_US_PHONE
,CORP_INFO_US_PHONE_EX
,CORP_INFO_NON_US_PHONE
,CORP_INFO_FAX
,CORP_INFO_EMAIL
,OWNER_INFO_POC
,OWNER_INFO_US_PHONE
,OWNER_INFO_US_PHONE_EX
,OWNER_INFO_NON_US_PHONE
,OWNER_INFO_FAX
,OWNER_INFO_EMAIL
,EDI
,TAXPAYER_ID
,AVG_NUM_EMPLOYEES
,ANNUAL_REVENUE
,SOCIAL_SECURITY_NUMBER
,FINANCIAL_INSTITUTE
,BANK_ACCT_NUMBER
,ABA_ROUTING
,BANK_ACCT_TYPE
,LOCKBOX_NUMBER
,AUTHORIZATION_DATE
,EFT_WAIVER
,ACH_US_PHONE
,ACH_NON_US_PHONE
,ACH_FAX
,ACH_EMAIL
,REMIT_POC
,REMIT_ADD1
,REMIT_ADD2
,REMIT_CITY
,REMIT_STATE
,REMIT_POSTAL_CODE
,REMIT_COUNTRY
,AR_POC
,AR_US_PHONE
,AR_US_PHONE_EX
,AR_NON_US_PHONE
,AR_FAX
,AR_EMAIL
,MPIN
,EDI_COORDINATOR
,EDI_US_PHONE
,EDI_US_PHONE_EX
,EDI_NON_US_PHONE
,EDI_FAX
,EDI_EMAIL
,VAN
,ISA_QUALIFIER
,ISA_SENDER_QUALIFIER
,GS02_IDENTIFIER
,RECEIVE_820
,HQ_PARENT_NAME
,HQ_PARENT_DUNS
,HQ_PARENT_ADD1
,HQ_PARENT_ADD2
,HQ_PARENT_CITY
,HQ_PARENT_STATE
,HQ_PARENT_POSTAL_CODE
,HQ_PARENT_COUNTRY
,HQ_PARENT_PHONE
,HQ_PARENT_RECORD_DATE
,GLOBAL_ULT_NAME
,GLOBAL_ULT_DUNS
,GLOBAL_ULT_ADD1
,GLOBAL_ULT_ADD2
,GLOBAL_ULT_CITY
,GLOBAL_ULT_POSTAL_CODE
,GLOBAL_ULT_COUNTRY
,GLOBAL_ULT_STATE
,GLOBAL_ULT_PHONE
,GLOBAL_ULT_RECORD_DATE
,DOMESTIC_ULT_NAME
,DOMESTIC_ULT_DUNS
,DOMESTIC_ULT_ADD1
,DOMESTIC_ULT_ADD2
,DOMESTIC_ULT_CITY
,DOMESTIC_ULT_POSTAL_CODE
,DOMESTIC_ULT_COUNTRY
,DOMESTIC_ULT_STATE
,DOMESTIC_ULT_PHONE
,DOMESTIC_ULT_RECORD_DATE
,ALT_CERTIFIER_FAX
,ALT_CERTIFIER_EMAIL)
SELECT l_file_date
,fcft.DUNS
,replace(fcft.PLUS_FOUR,' ',null)
,fcft.CAGE_CODE
,fcft.EXTRACT_CODE
,fcft.REGISTRATION_DATE
,fcft.RENEWAL_DATE
,fcft.LEGAL_BUS_NAME
,fcft.DBA_NAME
,fcft.DIVISION_NAME
,fcft.DIVISION_NUMBER
,fcft.ST_ADDRESS1
,fcft.ST_ADDRESS2
,fcft.CITY
,fcft.STATE
,fcft.POSTAL_CODE
,fcft.COUNTRY
,fcft.BUSINESS_START_DATE
,fcft.FISCAL_YR_CLOSE_DATE
,fcft.CORP_SECURITY_LEVEL
,fcft.EMP_SECURITY_LEVEL
,fcft.WEB_SITE
,fcft.ORGANIZATIONAL_TYPE
,fcft.STATE_OF_INC
,fcft.COUNTRY_OF_INC
,fcft.BUSINESS_TYPES
,fcft.SIC_CODES
,fcft.NAICS_CODES
,fcft.FSC_CODES
,fcft.PSC_CODES
,fcft.CREDIT_CARD_FLAG
,fcft.CORRESPONDENCE_FLAG
,fcft.MAIL_POC
,fcft.MAIL_ADD1
,fcft.MAIL_ADD2
,fcft.MAIL_CITY
,fcft.MAIL_POSTAL_CODE
,fcft.MAIL_COUNTRY
,fcft.MAIL_STATE
,fcft.PREV_BUS_POC
,fcft.PREV_BUS_ADD1
,fcft.PREV_BUS_ADD2
,fcft.PREV_BUS_CITY
,fcft.PREV_BUS_POSTAL_CODE
,fcft.PREV_BUS_COUNTRY
,fcft.PREV_BUS_STATE
,fcft.PARENT_POC
,fcft.PARENT_DUNS
,fcft.PARENT_ADD1
,fcft.PARENT_ADD2
,fcft.PARENT_CITY
,fcft.PARENT_POSTAL_CODE
,fcft.PARENT_COUNTRY
,fcft.PARENT_STATE
,fcft.PARTY_PERF_POC
,fcft.PARTY_PERF_ADD1
,fcft.PARTY_PERF_ADD2
,fcft.PARTY_PERF_CITY
,fcft.PARTY_PERF_POSTAL_CODE
,fcft.PARTY_PERF_COUNTRY
,fcft.PARTY_PERF_STATE
,fcft.GOV_PARENT_POC
,fcft.GOV_PARENT_ADD1
,fcft.GOV_PARENT_ADD2
,fcft.GOV_PARENT_CITY
,fcft.GOV_PARENT_POSTAL_CODE
,fcft.GOV_PARENT_COUNTRY
,fcft.GOV_PARENT_STATE
,fcft.GOV_BUS_POC
,fcft.GOV_BUS_ADD1
,fcft.GOV_BUS_ADD2
,fcft.GOV_BUS_CITY
,fcft.GOV_BUS_POSTAL_CODE
,fcft.GOV_BUS_COUNTRY
,fcft.GOV_BUS_STATE
,fcft.GOV_BUS_US_PHONE
,fcft.GOV_BUS_US_PHONE_EX
,fcft.GOV_BUS_NON_US_PHONE
,fcft.GOV_BUS_FAX
,fcft.GOV_BUS_EMAIL
,fcft.ALT_GOV_BUS_POC
,fcft.ALT_GOV_BUS_ADD1
,fcft.ALT_GOV_BUS_ADD2
,fcft.ALT_GOV_BUS_CITY
,fcft.ALT_GOV_BUS_POSTAL_CODE
,fcft.ALT_GOV_BUS_COUNTRY
,fcft.ALT_GOV_BUS_STATE
,fcft.ALT_GOV_BUS_US_PHONE
,fcft.ALT_GOV_BUS_US_PHONE_EX
,fcft.ALT_GOV_BUS_NON_US_PHONE
,fcft.ALT_GOV_BUS_FAX
,fcft.ALT_GOV_BUS_EMAIL
,fcft.PAST_PERF_POC
,fcft.PAST_PERF_ADD1
,fcft.PAST_PERF_ADD2
,fcft.PAST_PERF_CITY
,fcft.PAST_PERF_POSTAL_CODE
,fcft.PAST_PERF_COUNTRY
,fcft.PAST_PERF_STATE
,fcft.PAST_PERF_US_PHONE
,fcft.PAST_PERF_US_PHONE_EX
,fcft.PAST_PERF_NON_US_PHONE
,fcft.PAST_PERF_FAX
,fcft.PAST_PERF_EMAIL
,fcft.ALT_PAST_PERF_POC
,fcft.ALT_PAST_PERF_ADD1
,fcft.ALT_PAST_PERF_ADD2
,fcft.ALT_PAST_PERF_CITY
,fcft.ALT_PAST_PERF_POSTAL_CODE
,fcft.ALT_PAST_PERF_COUNTRY
,fcft.ALT_PAST_PERF_STATE
,fcft.ALT_PAST_PERF_US_PHONE
,fcft.ALT_PAST_PERF_US_PHONE_EX
,fcft.ALT_PAST_PERF_NON_US_PHONE
,fcft.ALT_PAST_PERF_FAX
,fcft.ALT_PAST_PERF_EMAIL
,fcft.ELEC_BUS_POC
,fcft.ELEC_BUS_ADD1
,fcft.ELEC_BUS_ADD2
,fcft.ELEC_BUS_CITY
,fcft.ELEC_BUS_POSTAL_CODE
,fcft.ELEC_BUS_COUNTRY
,fcft.ELEC_BUS_STATE
,fcft.ELEC_BUS_US_PHONE
,fcft.ELEC_BUS_US_PHONE_EX
,fcft.ELEC_BUS_NON_US_PHONE
,fcft.ELEC_BUS_FAX
,fcft.ELEC_BUS_EMAIL
,fcft.ALT_ELEC_BUS_POC
,fcft.ALT_ELEC_BUS_ADD1
,fcft.ALT_ELEC_BUS_ADD2
,fcft.ALT_ELEC_BUS_CITY
,fcft.ALT_ELEC_BUS_POSTAL_CODE
,fcft.ALT_ELEC_BUS_COUNTRY
,fcft.ALT_ELEC_BUS_STATE
,fcft.ALT_ELEC_BUS_US_PHONE
,fcft.ALT_ELEC_BUS_US_PHONE_EX
,fcft.ALT_ELEC_BUS_NON_US_PHONE
,fcft.ALT_ELEC_BUS_FAX
,fcft.ALT_ELEC_BUS_EMAIL
,fcft.CERTIFIER_POC
,fcft.CERTIFIER_US_PHONE
,fcft.CERTIFIER_US_PHONE_EX
,fcft.CERTIFIER_NON_US_PHONE
,fcft.CERTIFIER_FAX
,fcft.CERTIFIER_EMAIL
,fcft.ALT_CERTIFIER_POC
,fcft.ALT_CERTIFIER_US_PHONE
,fcft.ALT_CERTIFIER_US_PHONE_EX
,fcft.ALT_CERTIFIER_NON_US_PHONE
,fcft.CORP_INFO_POC
,fcft.CORP_INFO_US_PHONE
,fcft.CORP_INFO_US_PHONE_EX
,fcft.CORP_INFO_NON_US_PHONE
,fcft.CORP_INFO_FAX
,fcft.CORP_INFO_EMAIL
,fcft.OWNER_INFO_POC
,fcft.OWNER_INFO_US_PHONE
,fcft.OWNER_INFO_US_PHONE_EX
,fcft.OWNER_INFO_NON_US_PHONE
,fcft.OWNER_INFO_FAX
,fcft.OWNER_INFO_EMAIL
,fcft.EDI
,fcft.TAXPAYER_ID
,fcft.AVG_NUM_EMPLOYEES
,fcft.ANNUAL_REVENUE
,fcft.SOCIAL_SECURITY_NUMBER
,fcft.FINANCIAL_INSTITUTE
,fcft.BANK_ACCT_NUMBER
,fcft.ABA_ROUTING
,fcft.BANK_ACCT_TYPE
,fcft.LOCKBOX_NUMBER
,fcft.AUTHORIZATION_DATE
,fcft.EFT_WAIVER
,fcft.ACH_US_PHONE
,fcft.ACH_NON_US_PHONE
,fcft.ACH_FAX
,fcft.ACH_EMAIL
,fcft.REMIT_POC
,fcft.REMIT_ADD1
,fcft.REMIT_ADD2
,fcft.REMIT_CITY
,fcft.REMIT_STATE
,fcft.REMIT_POSTAL_CODE
,fcft.REMIT_COUNTRY
,fcft.AR_POC
,fcft.AR_US_PHONE
,fcft.AR_US_PHONE_EX
,fcft.AR_NON_US_PHONE
,fcft.AR_FAX
,fcft.AR_EMAIL
,fcft.MPIN
,fcft.EDI_COORDINATOR
,fcft.EDI_US_PHONE
,fcft.EDI_US_PHONE_EX
,fcft.EDI_NON_US_PHONE
,fcft.EDI_FAX
,fcft.EDI_EMAIL
,fcft.VAN
,fcft.ISA_QUALIFIER
,fcft.ISA_SENDER_QUALIFIER
,fcft.GS02_IDENTIFIER
,fcft.RECEIVE_820
,fcft.HQ_PARENT_NAME
,fcft.HQ_PARENT_DUNS
,fcft.HQ_PARENT_ADD1
,fcft.HQ_PARENT_ADD2
,fcft.HQ_PARENT_CITY
,fcft.HQ_PARENT_STATE
,fcft.HQ_PARENT_POSTAL_CODE
,fcft.HQ_PARENT_COUNTRY
,fcft.HQ_PARENT_PHONE
,fcft.HQ_PARENT_RECORD_DATE
,fcft.GLOBAL_ULT_NAME
,fcft.GLOBAL_ULT_DUNS
,fcft.GLOBAL_ULT_ADD1
,fcft.GLOBAL_ULT_ADD2
,fcft.GLOBAL_ULT_CITY
,fcft.GLOBAL_ULT_POSTAL_CODE
,fcft.GLOBAL_ULT_COUNTRY
,fcft.GLOBAL_ULT_STATE
,fcft.GLOBAL_ULT_PHONE
,fcft.GLOBAL_ULT_RECORD_DATE
,fcft.DOMESTIC_ULT_NAME
,fcft.DOMESTIC_ULT_DUNS
,fcft.DOMESTIC_ULT_ADD1
,fcft.DOMESTIC_ULT_ADD2
,fcft.DOMESTIC_ULT_CITY
,fcft.DOMESTIC_ULT_POSTAL_CODE
,fcft.DOMESTIC_ULT_COUNTRY
,fcft.DOMESTIC_ULT_STATE
,fcft.DOMESTIC_ULT_PHONE
,fcft.DOMESTIC_ULT_RECORD_DATE
,fcft.ALT_CERTIFIER_FAX
,fcft.ALT_CERTIFIER_EMAIL
FROM fv_ccr_file_temp fcft
WHERE exists ( select 1 from fv_ccr_vendors fcv
where fcft.duns = fcv.duns)
order by rowid;
insert_temp_data(3,null,l_msg_no_duns,l_message_action3,null,null,null);
ELSIF (l_update_type ='N') THEN
l_errbuf :='Update Type as N - inserting into second temp table';
INSERT INTO fv_ccr_process_gt ( FILE_DATE
,DUNS
,PLUS_FOUR
,CAGE_CODE
,EXTRACT_CODE
,REGISTRATION_DATE
,RENEWAL_DATE
,LEGAL_BUS_NAME
,DBA_NAME
,DIVISION_NAME
,DIVISION_NUMBER
,ST_ADDRESS1
,ST_ADDRESS2
,CITY
,STATE
,POSTAL_CODE
,COUNTRY
,BUSINESS_START_DATE
,FISCAL_YR_CLOSE_DATE
,CORP_SECURITY_LEVEL
,EMP_SECURITY_LEVEL
,WEB_SITE
,ORGANIZATIONAL_TYPE
,STATE_OF_INC
,COUNTRY_OF_INC
,BUSINESS_TYPES
,SIC_CODES
,NAICS_CODES
,FSC_CODES
,PSC_CODES
,CREDIT_CARD_FLAG
,CORRESPONDENCE_FLAG
,MAIL_POC
,MAIL_ADD1
,MAIL_ADD2
,MAIL_CITY
,MAIL_POSTAL_CODE
,MAIL_COUNTRY
,MAIL_STATE
,PREV_BUS_POC
,PREV_BUS_ADD1
,PREV_BUS_ADD2
,PREV_BUS_CITY
,PREV_BUS_POSTAL_CODE
,PREV_BUS_COUNTRY
,PREV_BUS_STATE
,PARENT_POC
,PARENT_DUNS
,PARENT_ADD1
,PARENT_ADD2
,PARENT_CITY
,PARENT_POSTAL_CODE
,PARENT_COUNTRY
,PARENT_STATE
,PARTY_PERF_POC
,PARTY_PERF_ADD1
,PARTY_PERF_ADD2
,PARTY_PERF_CITY
,PARTY_PERF_POSTAL_CODE
,PARTY_PERF_COUNTRY
,PARTY_PERF_STATE
,GOV_PARENT_POC
,GOV_PARENT_ADD1
,GOV_PARENT_ADD2
,GOV_PARENT_CITY
,GOV_PARENT_POSTAL_CODE
,GOV_PARENT_COUNTRY
,GOV_PARENT_STATE
,GOV_BUS_POC
,GOV_BUS_ADD1
,GOV_BUS_ADD2
,GOV_BUS_CITY
,GOV_BUS_POSTAL_CODE
,GOV_BUS_COUNTRY
,GOV_BUS_STATE
,GOV_BUS_US_PHONE
,GOV_BUS_US_PHONE_EX
,GOV_BUS_NON_US_PHONE
,GOV_BUS_FAX
,GOV_BUS_EMAIL
,ALT_GOV_BUS_POC
,ALT_GOV_BUS_ADD1
,ALT_GOV_BUS_ADD2
,ALT_GOV_BUS_CITY
,ALT_GOV_BUS_POSTAL_CODE
,ALT_GOV_BUS_COUNTRY
,ALT_GOV_BUS_STATE
,ALT_GOV_BUS_US_PHONE
,ALT_GOV_BUS_US_PHONE_EX
,ALT_GOV_BUS_NON_US_PHONE
,ALT_GOV_BUS_FAX
,ALT_GOV_BUS_EMAIL
,PAST_PERF_POC
,PAST_PERF_ADD1
,PAST_PERF_ADD2
,PAST_PERF_CITY
,PAST_PERF_POSTAL_CODE
,PAST_PERF_COUNTRY
,PAST_PERF_STATE
,PAST_PERF_US_PHONE
,PAST_PERF_US_PHONE_EX
,PAST_PERF_NON_US_PHONE
,PAST_PERF_FAX
,PAST_PERF_EMAIL
,ALT_PAST_PERF_POC
,ALT_PAST_PERF_ADD1
,ALT_PAST_PERF_ADD2
,ALT_PAST_PERF_CITY
,ALT_PAST_PERF_POSTAL_CODE
,ALT_PAST_PERF_COUNTRY
,ALT_PAST_PERF_STATE
,ALT_PAST_PERF_US_PHONE
,ALT_PAST_PERF_US_PHONE_EX
,ALT_PAST_PERF_NON_US_PHONE
,ALT_PAST_PERF_FAX
,ALT_PAST_PERF_EMAIL
,ELEC_BUS_POC
,ELEC_BUS_ADD1
,ELEC_BUS_ADD2
,ELEC_BUS_CITY
,ELEC_BUS_POSTAL_CODE
,ELEC_BUS_COUNTRY
,ELEC_BUS_STATE
,ELEC_BUS_US_PHONE
,ELEC_BUS_US_PHONE_EX
,ELEC_BUS_NON_US_PHONE
,ELEC_BUS_FAX
,ELEC_BUS_EMAIL
,ALT_ELEC_BUS_POC
,ALT_ELEC_BUS_ADD1
,ALT_ELEC_BUS_ADD2
,ALT_ELEC_BUS_CITY
,ALT_ELEC_BUS_POSTAL_CODE
,ALT_ELEC_BUS_COUNTRY
,ALT_ELEC_BUS_STATE
,ALT_ELEC_BUS_US_PHONE
,ALT_ELEC_BUS_US_PHONE_EX
,ALT_ELEC_BUS_NON_US_PHONE
,ALT_ELEC_BUS_FAX
,ALT_ELEC_BUS_EMAIL
,CERTIFIER_POC
,CERTIFIER_US_PHONE
,CERTIFIER_US_PHONE_EX
,CERTIFIER_NON_US_PHONE
,CERTIFIER_FAX
,CERTIFIER_EMAIL
,ALT_CERTIFIER_POC
,ALT_CERTIFIER_US_PHONE
,ALT_CERTIFIER_US_PHONE_EX
,ALT_CERTIFIER_NON_US_PHONE
,CORP_INFO_POC
,CORP_INFO_US_PHONE
,CORP_INFO_US_PHONE_EX
,CORP_INFO_NON_US_PHONE
,CORP_INFO_FAX
,CORP_INFO_EMAIL
,OWNER_INFO_POC
,OWNER_INFO_US_PHONE
,OWNER_INFO_US_PHONE_EX
,OWNER_INFO_NON_US_PHONE
,OWNER_INFO_FAX
,OWNER_INFO_EMAIL
,EDI
,TAXPAYER_ID
,AVG_NUM_EMPLOYEES
,ANNUAL_REVENUE
,SOCIAL_SECURITY_NUMBER
,FINANCIAL_INSTITUTE
,BANK_ACCT_NUMBER
,ABA_ROUTING
,BANK_ACCT_TYPE
,LOCKBOX_NUMBER
,AUTHORIZATION_DATE
,EFT_WAIVER
,ACH_US_PHONE
,ACH_NON_US_PHONE
,ACH_FAX
,ACH_EMAIL
,REMIT_POC
,REMIT_ADD1
,REMIT_ADD2
,REMIT_CITY
,REMIT_STATE
,REMIT_POSTAL_CODE
,REMIT_COUNTRY
,AR_POC
,AR_US_PHONE
,AR_US_PHONE_EX
,AR_NON_US_PHONE
,AR_FAX
,AR_EMAIL
,MPIN
,EDI_COORDINATOR
,EDI_US_PHONE
,EDI_US_PHONE_EX
,EDI_NON_US_PHONE
,EDI_FAX
,EDI_EMAIL
,VAN
,ISA_QUALIFIER
,ISA_SENDER_QUALIFIER
,GS02_IDENTIFIER
,RECEIVE_820
,HQ_PARENT_NAME
,HQ_PARENT_DUNS
,HQ_PARENT_ADD1
,HQ_PARENT_ADD2
,HQ_PARENT_CITY
,HQ_PARENT_STATE
,HQ_PARENT_POSTAL_CODE
,HQ_PARENT_COUNTRY
,HQ_PARENT_PHONE
,HQ_PARENT_RECORD_DATE
,GLOBAL_ULT_NAME
,GLOBAL_ULT_DUNS
,GLOBAL_ULT_ADD1
,GLOBAL_ULT_ADD2
,GLOBAL_ULT_CITY
,GLOBAL_ULT_POSTAL_CODE
,GLOBAL_ULT_COUNTRY
,GLOBAL_ULT_STATE
,GLOBAL_ULT_PHONE
,GLOBAL_ULT_RECORD_DATE
,DOMESTIC_ULT_NAME
,DOMESTIC_ULT_DUNS
,DOMESTIC_ULT_ADD1
,DOMESTIC_ULT_ADD2
,DOMESTIC_ULT_CITY
,DOMESTIC_ULT_POSTAL_CODE
,DOMESTIC_ULT_COUNTRY
,DOMESTIC_ULT_STATE
,DOMESTIC_ULT_PHONE
,DOMESTIC_ULT_RECORD_DATE
,ALT_CERTIFIER_FAX
,ALT_CERTIFIER_EMAIL)
SELECT l_file_date
,fcft.DUNS
,replace(FCFT.PLUS_FOUR,' ',null)
,fcft.CAGE_CODE
,fcft.EXTRACT_CODE
,fcft.REGISTRATION_DATE
,fcft.RENEWAL_DATE
,fcft.LEGAL_BUS_NAME
,fcft.DBA_NAME
,fcft.DIVISION_NAME
,fcft.DIVISION_NUMBER
,fcft.ST_ADDRESS1
,fcft.ST_ADDRESS2
,fcft.CITY
,fcft.STATE
,fcft.POSTAL_CODE
,fcft.COUNTRY
,fcft.BUSINESS_START_DATE
,fcft.FISCAL_YR_CLOSE_DATE
,fcft.CORP_SECURITY_LEVEL
,fcft.EMP_SECURITY_LEVEL
,fcft.WEB_SITE
,fcft.ORGANIZATIONAL_TYPE
,fcft.STATE_OF_INC
,fcft.COUNTRY_OF_INC
,fcft.BUSINESS_TYPES
,fcft.SIC_CODES
,fcft.NAICS_CODES
,fcft.FSC_CODES
,fcft.PSC_CODES
,fcft.CREDIT_CARD_FLAG
,fcft.CORRESPONDENCE_FLAG
,fcft.MAIL_POC
,fcft.MAIL_ADD1
,fcft.MAIL_ADD2
,fcft.MAIL_CITY
,fcft.MAIL_POSTAL_CODE
,fcft.MAIL_COUNTRY
,fcft.MAIL_STATE
,fcft.PREV_BUS_POC
,fcft.PREV_BUS_ADD1
,fcft.PREV_BUS_ADD2
,fcft.PREV_BUS_CITY
,fcft.PREV_BUS_POSTAL_CODE
,fcft.PREV_BUS_COUNTRY
,fcft.PREV_BUS_STATE
,fcft.PARENT_POC
,fcft.PARENT_DUNS
,fcft.PARENT_ADD1
,fcft.PARENT_ADD2
,fcft.PARENT_CITY
,fcft.PARENT_POSTAL_CODE
,fcft.PARENT_COUNTRY
,fcft.PARENT_STATE
,fcft.PARTY_PERF_POC
,fcft.PARTY_PERF_ADD1
,fcft.PARTY_PERF_ADD2
,fcft.PARTY_PERF_CITY
,fcft.PARTY_PERF_POSTAL_CODE
,fcft.PARTY_PERF_COUNTRY
,fcft.PARTY_PERF_STATE
,fcft.GOV_PARENT_POC
,fcft.GOV_PARENT_ADD1
,fcft.GOV_PARENT_ADD2
,fcft.GOV_PARENT_CITY
,fcft.GOV_PARENT_POSTAL_CODE
,fcft.GOV_PARENT_COUNTRY
,fcft.GOV_PARENT_STATE
,fcft.GOV_BUS_POC
,fcft.GOV_BUS_ADD1
,fcft.GOV_BUS_ADD2
,fcft.GOV_BUS_CITY
,fcft.GOV_BUS_POSTAL_CODE
,fcft.GOV_BUS_COUNTRY
,fcft.GOV_BUS_STATE
,fcft.GOV_BUS_US_PHONE
,fcft.GOV_BUS_US_PHONE_EX
,fcft.GOV_BUS_NON_US_PHONE
,fcft.GOV_BUS_FAX
,fcft.GOV_BUS_EMAIL
,fcft.ALT_GOV_BUS_POC
,fcft.ALT_GOV_BUS_ADD1
,fcft.ALT_GOV_BUS_ADD2
,fcft.ALT_GOV_BUS_CITY
,fcft.ALT_GOV_BUS_POSTAL_CODE
,fcft.ALT_GOV_BUS_COUNTRY
,fcft.ALT_GOV_BUS_STATE
,fcft.ALT_GOV_BUS_US_PHONE
,fcft.ALT_GOV_BUS_US_PHONE_EX
,fcft.ALT_GOV_BUS_NON_US_PHONE
,fcft.ALT_GOV_BUS_FAX
,fcft.ALT_GOV_BUS_EMAIL
,fcft.PAST_PERF_POC
,fcft.PAST_PERF_ADD1
,fcft.PAST_PERF_ADD2
,fcft.PAST_PERF_CITY
,fcft.PAST_PERF_POSTAL_CODE
,fcft.PAST_PERF_COUNTRY
,fcft.PAST_PERF_STATE
,fcft.PAST_PERF_US_PHONE
,fcft.PAST_PERF_US_PHONE_EX
,fcft.PAST_PERF_NON_US_PHONE
,fcft.PAST_PERF_FAX
,fcft.PAST_PERF_EMAIL
,fcft.ALT_PAST_PERF_POC
,fcft.ALT_PAST_PERF_ADD1
,fcft.ALT_PAST_PERF_ADD2
,fcft.ALT_PAST_PERF_CITY
,fcft.ALT_PAST_PERF_POSTAL_CODE
,fcft.ALT_PAST_PERF_COUNTRY
,fcft.ALT_PAST_PERF_STATE
,fcft.ALT_PAST_PERF_US_PHONE
,fcft.ALT_PAST_PERF_US_PHONE_EX
,fcft.ALT_PAST_PERF_NON_US_PHONE
,fcft.ALT_PAST_PERF_FAX
,fcft.ALT_PAST_PERF_EMAIL
,fcft.ELEC_BUS_POC
,fcft.ELEC_BUS_ADD1
,fcft.ELEC_BUS_ADD2
,fcft.ELEC_BUS_CITY
,fcft.ELEC_BUS_POSTAL_CODE
,fcft.ELEC_BUS_COUNTRY
,fcft.ELEC_BUS_STATE
,fcft.ELEC_BUS_US_PHONE
,fcft.ELEC_BUS_US_PHONE_EX
,fcft.ELEC_BUS_NON_US_PHONE
,fcft.ELEC_BUS_FAX
,fcft.ELEC_BUS_EMAIL
,fcft.ALT_ELEC_BUS_POC
,fcft.ALT_ELEC_BUS_ADD1
,fcft.ALT_ELEC_BUS_ADD2
,fcft.ALT_ELEC_BUS_CITY
,fcft.ALT_ELEC_BUS_POSTAL_CODE
,fcft.ALT_ELEC_BUS_COUNTRY
,fcft.ALT_ELEC_BUS_STATE
,fcft.ALT_ELEC_BUS_US_PHONE
,fcft.ALT_ELEC_BUS_US_PHONE_EX
,fcft.ALT_ELEC_BUS_NON_US_PHONE
,fcft.ALT_ELEC_BUS_FAX
,fcft.ALT_ELEC_BUS_EMAIL
,fcft.CERTIFIER_POC
,fcft.CERTIFIER_US_PHONE
,fcft.CERTIFIER_US_PHONE_EX
,fcft.CERTIFIER_NON_US_PHONE
,fcft.CERTIFIER_FAX
,fcft.CERTIFIER_EMAIL
,fcft.ALT_CERTIFIER_POC
,fcft.ALT_CERTIFIER_US_PHONE
,fcft.ALT_CERTIFIER_US_PHONE_EX
,fcft.ALT_CERTIFIER_NON_US_PHONE
,fcft.CORP_INFO_POC
,fcft.CORP_INFO_US_PHONE
,fcft.CORP_INFO_US_PHONE_EX
,fcft.CORP_INFO_NON_US_PHONE
,fcft.CORP_INFO_FAX
,fcft.CORP_INFO_EMAIL
,fcft.OWNER_INFO_POC
,fcft.OWNER_INFO_US_PHONE
,fcft.OWNER_INFO_US_PHONE_EX
,fcft.OWNER_INFO_NON_US_PHONE
,fcft.OWNER_INFO_FAX
,fcft.OWNER_INFO_EMAIL
,fcft.EDI
,fcft.TAXPAYER_ID
,fcft.AVG_NUM_EMPLOYEES
,fcft.ANNUAL_REVENUE
,fcft.SOCIAL_SECURITY_NUMBER
,fcft.FINANCIAL_INSTITUTE
,fcft.BANK_ACCT_NUMBER
,fcft.ABA_ROUTING
,fcft.BANK_ACCT_TYPE
,fcft.LOCKBOX_NUMBER
,fcft.AUTHORIZATION_DATE
,fcft.EFT_WAIVER
,fcft.ACH_US_PHONE
,fcft.ACH_NON_US_PHONE
,fcft.ACH_FAX
,fcft.ACH_EMAIL
,fcft.REMIT_POC
,fcft.REMIT_ADD1
,fcft.REMIT_ADD2
,fcft.REMIT_CITY
,fcft.REMIT_STATE
,fcft.REMIT_POSTAL_CODE
,fcft.REMIT_COUNTRY
,fcft.AR_POC
,fcft.AR_US_PHONE
,fcft.AR_US_PHONE_EX
,fcft.AR_NON_US_PHONE
,fcft.AR_FAX
,fcft.AR_EMAIL
,fcft.MPIN
,fcft.EDI_COORDINATOR
,fcft.EDI_US_PHONE
,fcft.EDI_US_PHONE_EX
,fcft.EDI_NON_US_PHONE
,fcft.EDI_FAX
,fcft.EDI_EMAIL
,fcft.VAN
,fcft.ISA_QUALIFIER
,fcft.ISA_SENDER_QUALIFIER
,fcft.GS02_IDENTIFIER
,fcft.RECEIVE_820
,fcft.HQ_PARENT_NAME
,fcft.HQ_PARENT_DUNS
,fcft.HQ_PARENT_ADD1
,fcft.HQ_PARENT_ADD2
,fcft.HQ_PARENT_CITY
,fcft.HQ_PARENT_STATE
,fcft.HQ_PARENT_POSTAL_CODE
,fcft.HQ_PARENT_COUNTRY
,fcft.HQ_PARENT_PHONE
,fcft.HQ_PARENT_RECORD_DATE
,fcft.GLOBAL_ULT_NAME
,fcft.GLOBAL_ULT_DUNS
,fcft.GLOBAL_ULT_ADD1
,fcft.GLOBAL_ULT_ADD2
,fcft.GLOBAL_ULT_CITY
,fcft.GLOBAL_ULT_POSTAL_CODE
,fcft.GLOBAL_ULT_COUNTRY
,fcft.GLOBAL_ULT_STATE
,fcft.GLOBAL_ULT_PHONE
,fcft.GLOBAL_ULT_RECORD_DATE
,fcft.DOMESTIC_ULT_NAME
,fcft.DOMESTIC_ULT_DUNS
,fcft.DOMESTIC_ULT_ADD1
,fcft.DOMESTIC_ULT_ADD2
,fcft.DOMESTIC_ULT_CITY
,fcft.DOMESTIC_ULT_POSTAL_CODE
,fcft.DOMESTIC_ULT_COUNTRY
,fcft.DOMESTIC_ULT_STATE
,fcft.DOMESTIC_ULT_PHONE
,fcft.DOMESTIC_ULT_RECORD_DATE
,fcft.ALT_CERTIFIER_FAX
,fcft.ALT_CERTIFIER_EMAIL
FROM fv_ccr_file_temp fcft
WHERE ( (p_xml_import = 'N' AND exists ( select 1 from fv_ccr_vendors fcv
where fcft.duns = fcv.duns
and fcv.ccr_status ='N'))
OR p_xml_import='Y')
order by rowid;
ELSIF (l_update_type ='S') THEN
l_errbuf := 'Update type as S - inserting into seciond temp table';
INSERT INTO fv_ccr_process_gt ( FILE_DATE
,DUNS
,PLUS_FOUR
,CAGE_CODE
,EXTRACT_CODE
,REGISTRATION_DATE
,RENEWAL_DATE
,LEGAL_BUS_NAME
,DBA_NAME
,DIVISION_NAME
,DIVISION_NUMBER
,ST_ADDRESS1
,ST_ADDRESS2
,CITY
,STATE
,POSTAL_CODE
,COUNTRY
,BUSINESS_START_DATE
,FISCAL_YR_CLOSE_DATE
,CORP_SECURITY_LEVEL
,EMP_SECURITY_LEVEL
,WEB_SITE
,ORGANIZATIONAL_TYPE
,STATE_OF_INC
,COUNTRY_OF_INC
,BUSINESS_TYPES
,SIC_CODES
,NAICS_CODES
,FSC_CODES
,PSC_CODES
,CREDIT_CARD_FLAG
,CORRESPONDENCE_FLAG
,MAIL_POC
,MAIL_ADD1
,MAIL_ADD2
,MAIL_CITY
,MAIL_POSTAL_CODE
,MAIL_COUNTRY
,MAIL_STATE
,PREV_BUS_POC
,PREV_BUS_ADD1
,PREV_BUS_ADD2
,PREV_BUS_CITY
,PREV_BUS_POSTAL_CODE
,PREV_BUS_COUNTRY
,PREV_BUS_STATE
,PARENT_POC
,PARENT_DUNS
,PARENT_ADD1
,PARENT_ADD2
,PARENT_CITY
,PARENT_POSTAL_CODE
,PARENT_COUNTRY
,PARENT_STATE
,PARTY_PERF_POC
,PARTY_PERF_ADD1
,PARTY_PERF_ADD2
,PARTY_PERF_CITY
,PARTY_PERF_POSTAL_CODE
,PARTY_PERF_COUNTRY
,PARTY_PERF_STATE
,GOV_PARENT_POC
,GOV_PARENT_ADD1
,GOV_PARENT_ADD2
,GOV_PARENT_CITY
,GOV_PARENT_POSTAL_CODE
,GOV_PARENT_COUNTRY
,GOV_PARENT_STATE
,GOV_BUS_POC
,GOV_BUS_ADD1
,GOV_BUS_ADD2
,GOV_BUS_CITY
,GOV_BUS_POSTAL_CODE
,GOV_BUS_COUNTRY
,GOV_BUS_STATE
,GOV_BUS_US_PHONE
,GOV_BUS_US_PHONE_EX
,GOV_BUS_NON_US_PHONE
,GOV_BUS_FAX
,GOV_BUS_EMAIL
,ALT_GOV_BUS_POC
,ALT_GOV_BUS_ADD1
,ALT_GOV_BUS_ADD2
,ALT_GOV_BUS_CITY
,ALT_GOV_BUS_POSTAL_CODE
,ALT_GOV_BUS_COUNTRY
,ALT_GOV_BUS_STATE
,ALT_GOV_BUS_US_PHONE
,ALT_GOV_BUS_US_PHONE_EX
,ALT_GOV_BUS_NON_US_PHONE
,ALT_GOV_BUS_FAX
,ALT_GOV_BUS_EMAIL
,PAST_PERF_POC
,PAST_PERF_ADD1
,PAST_PERF_ADD2
,PAST_PERF_CITY
,PAST_PERF_POSTAL_CODE
,PAST_PERF_COUNTRY
,PAST_PERF_STATE
,PAST_PERF_US_PHONE
,PAST_PERF_US_PHONE_EX
,PAST_PERF_NON_US_PHONE
,PAST_PERF_FAX
,PAST_PERF_EMAIL
,ALT_PAST_PERF_POC
,ALT_PAST_PERF_ADD1
,ALT_PAST_PERF_ADD2
,ALT_PAST_PERF_CITY
,ALT_PAST_PERF_POSTAL_CODE
,ALT_PAST_PERF_COUNTRY
,ALT_PAST_PERF_STATE
,ALT_PAST_PERF_US_PHONE
,ALT_PAST_PERF_US_PHONE_EX
,ALT_PAST_PERF_NON_US_PHONE
,ALT_PAST_PERF_FAX
,ALT_PAST_PERF_EMAIL
,ELEC_BUS_POC
,ELEC_BUS_ADD1
,ELEC_BUS_ADD2
,ELEC_BUS_CITY
,ELEC_BUS_POSTAL_CODE
,ELEC_BUS_COUNTRY
,ELEC_BUS_STATE
,ELEC_BUS_US_PHONE
,ELEC_BUS_US_PHONE_EX
,ELEC_BUS_NON_US_PHONE
,ELEC_BUS_FAX
,ELEC_BUS_EMAIL
,ALT_ELEC_BUS_POC
,ALT_ELEC_BUS_ADD1
,ALT_ELEC_BUS_ADD2
,ALT_ELEC_BUS_CITY
,ALT_ELEC_BUS_POSTAL_CODE
,ALT_ELEC_BUS_COUNTRY
,ALT_ELEC_BUS_STATE
,ALT_ELEC_BUS_US_PHONE
,ALT_ELEC_BUS_US_PHONE_EX
,ALT_ELEC_BUS_NON_US_PHONE
,ALT_ELEC_BUS_FAX
,ALT_ELEC_BUS_EMAIL
,CERTIFIER_POC
,CERTIFIER_US_PHONE
,CERTIFIER_US_PHONE_EX
,CERTIFIER_NON_US_PHONE
,CERTIFIER_FAX
,CERTIFIER_EMAIL
,ALT_CERTIFIER_POC
,ALT_CERTIFIER_US_PHONE
,ALT_CERTIFIER_US_PHONE_EX
,ALT_CERTIFIER_NON_US_PHONE
,CORP_INFO_POC
,CORP_INFO_US_PHONE
,CORP_INFO_US_PHONE_EX
,CORP_INFO_NON_US_PHONE
,CORP_INFO_FAX
,CORP_INFO_EMAIL
,OWNER_INFO_POC
,OWNER_INFO_US_PHONE
,OWNER_INFO_US_PHONE_EX
,OWNER_INFO_NON_US_PHONE
,OWNER_INFO_FAX
,OWNER_INFO_EMAIL
,EDI
,TAXPAYER_ID
,AVG_NUM_EMPLOYEES
,ANNUAL_REVENUE
,SOCIAL_SECURITY_NUMBER
,FINANCIAL_INSTITUTE
,BANK_ACCT_NUMBER
,ABA_ROUTING
,BANK_ACCT_TYPE
,LOCKBOX_NUMBER
,AUTHORIZATION_DATE
,EFT_WAIVER
,ACH_US_PHONE
,ACH_NON_US_PHONE
,ACH_FAX
,ACH_EMAIL
,REMIT_POC
,REMIT_ADD1
,REMIT_ADD2
,REMIT_CITY
,REMIT_STATE
,REMIT_POSTAL_CODE
,REMIT_COUNTRY
,AR_POC
,AR_US_PHONE
,AR_US_PHONE_EX
,AR_NON_US_PHONE
,AR_FAX
,AR_EMAIL
,MPIN
,EDI_COORDINATOR
,EDI_US_PHONE
,EDI_US_PHONE_EX
,EDI_NON_US_PHONE
,EDI_FAX
,EDI_EMAIL
,VAN
,ISA_QUALIFIER
,ISA_SENDER_QUALIFIER
,GS02_IDENTIFIER
,RECEIVE_820
,HQ_PARENT_NAME
,HQ_PARENT_DUNS
,HQ_PARENT_ADD1
,HQ_PARENT_ADD2
,HQ_PARENT_CITY
,HQ_PARENT_STATE
,HQ_PARENT_POSTAL_CODE
,HQ_PARENT_COUNTRY
,HQ_PARENT_PHONE
,HQ_PARENT_RECORD_DATE
,GLOBAL_ULT_NAME
,GLOBAL_ULT_DUNS
,GLOBAL_ULT_ADD1
,GLOBAL_ULT_ADD2
,GLOBAL_ULT_CITY
,GLOBAL_ULT_POSTAL_CODE
,GLOBAL_ULT_COUNTRY
,GLOBAL_ULT_STATE
,GLOBAL_ULT_PHONE
,GLOBAL_ULT_RECORD_DATE
,DOMESTIC_ULT_NAME
,DOMESTIC_ULT_DUNS
,DOMESTIC_ULT_ADD1
,DOMESTIC_ULT_ADD2
,DOMESTIC_ULT_CITY
,DOMESTIC_ULT_POSTAL_CODE
,DOMESTIC_ULT_COUNTRY
,DOMESTIC_ULT_STATE
,DOMESTIC_ULT_PHONE
,DOMESTIC_ULT_RECORD_DATE )
SELECT l_file_date
,fcft.DUNS
,replace(PLUS_FOUR,' ',null)
,CAGE_CODE
,EXTRACT_CODE
,REGISTRATION_DATE
,RENEWAL_DATE
,LEGAL_BUS_NAME
,DBA_NAME
,DIVISION_NAME
,DIVISION_NUMBER
,ST_ADDRESS1
,ST_ADDRESS2
,CITY
,STATE
,POSTAL_CODE
,COUNTRY
,BUSINESS_START_DATE
,FISCAL_YR_CLOSE_DATE
,CORP_SECURITY_LEVEL
,EMP_SECURITY_LEVEL
,WEB_SITE
,ORGANIZATIONAL_TYPE
,STATE_OF_INC
,COUNTRY_OF_INC
,BUSINESS_TYPES
,SIC_CODES
,NAICS_CODES
,FSC_CODES
,PSC_CODES
,CREDIT_CARD_FLAG
,CORRESPONDENCE_FLAG
,MAIL_POC
,MAIL_ADD1
,MAIL_ADD2
,MAIL_CITY
,MAIL_POSTAL_CODE
,MAIL_COUNTRY
,MAIL_STATE
,PREV_BUS_POC
,PREV_BUS_ADD1
,PREV_BUS_ADD2
,PREV_BUS_CITY
,PREV_BUS_POSTAL_CODE
,PREV_BUS_COUNTRY
,PREV_BUS_STATE
,PARENT_POC
,PARENT_DUNS
,PARENT_ADD1
,PARENT_ADD2
,PARENT_CITY
,PARENT_POSTAL_CODE
,PARENT_COUNTRY
,PARENT_STATE
,PARTY_PERF_POC
,PARTY_PERF_ADD1
,PARTY_PERF_ADD2
,PARTY_PERF_CITY
,PARTY_PERF_POSTAL_CODE
,PARTY_PERF_COUNTRY
,PARTY_PERF_STATE
,GOV_PARENT_POC
,GOV_PARENT_ADD1
,GOV_PARENT_ADD2
,GOV_PARENT_CITY
,GOV_PARENT_POSTAL_CODE
,GOV_PARENT_COUNTRY
,GOV_PARENT_STATE
,GOV_BUS_POC
,GOV_BUS_ADD1
,GOV_BUS_ADD2
,GOV_BUS_CITY
,GOV_BUS_POSTAL_CODE
,GOV_BUS_COUNTRY
,GOV_BUS_STATE
,GOV_BUS_US_PHONE
,GOV_BUS_US_PHONE_EX
,GOV_BUS_NON_US_PHONE
,GOV_BUS_FAX
,GOV_BUS_EMAIL
,ALT_GOV_BUS_POC
,ALT_GOV_BUS_ADD1
,ALT_GOV_BUS_ADD2
,ALT_GOV_BUS_CITY
,ALT_GOV_BUS_POSTAL_CODE
,ALT_GOV_BUS_COUNTRY
,ALT_GOV_BUS_STATE
,ALT_GOV_BUS_US_PHONE
,ALT_GOV_BUS_US_PHONE_EX
,ALT_GOV_BUS_NON_US_PHONE
,ALT_GOV_BUS_FAX
,ALT_GOV_BUS_EMAIL
,PAST_PERF_POC
,PAST_PERF_ADD1
,PAST_PERF_ADD2
,PAST_PERF_CITY
,PAST_PERF_POSTAL_CODE
,PAST_PERF_COUNTRY
,PAST_PERF_STATE
,PAST_PERF_US_PHONE
,PAST_PERF_US_PHONE_EX
,PAST_PERF_NON_US_PHONE
,PAST_PERF_FAX
,PAST_PERF_EMAIL
,ALT_PAST_PERF_POC
,ALT_PAST_PERF_ADD1
,ALT_PAST_PERF_ADD2
,ALT_PAST_PERF_CITY
,ALT_PAST_PERF_POSTAL_CODE
,ALT_PAST_PERF_COUNTRY
,ALT_PAST_PERF_STATE
,ALT_PAST_PERF_US_PHONE
,ALT_PAST_PERF_US_PHONE_EX
,ALT_PAST_PERF_NON_US_PHONE
,ALT_PAST_PERF_FAX
,ALT_PAST_PERF_EMAIL
,ELEC_BUS_POC
,ELEC_BUS_ADD1
,ELEC_BUS_ADD2
,ELEC_BUS_CITY
,ELEC_BUS_POSTAL_CODE
,ELEC_BUS_COUNTRY
,ELEC_BUS_STATE
,ELEC_BUS_US_PHONE
,ELEC_BUS_US_PHONE_EX
,ELEC_BUS_NON_US_PHONE
,ELEC_BUS_FAX
,ELEC_BUS_EMAIL
,ALT_ELEC_BUS_POC
,ALT_ELEC_BUS_ADD1
,ALT_ELEC_BUS_ADD2
,ALT_ELEC_BUS_CITY
,ALT_ELEC_BUS_POSTAL_CODE
,ALT_ELEC_BUS_COUNTRY
,ALT_ELEC_BUS_STATE
,ALT_ELEC_BUS_US_PHONE
,ALT_ELEC_BUS_US_PHONE_EX
,ALT_ELEC_BUS_NON_US_PHONE
,ALT_ELEC_BUS_FAX
,ALT_ELEC_BUS_EMAIL
,CERTIFIER_POC
,CERTIFIER_US_PHONE
,CERTIFIER_US_PHONE_EX
,CERTIFIER_NON_US_PHONE
,CERTIFIER_FAX
,CERTIFIER_EMAIL
,ALT_CERTIFIER_POC
,ALT_CERTIFIER_US_PHONE
,ALT_CERTIFIER_US_PHONE_EX
,ALT_CERTIFIER_NON_US_PHONE
,CORP_INFO_POC
,CORP_INFO_US_PHONE
,CORP_INFO_US_PHONE_EX
,CORP_INFO_NON_US_PHONE
,CORP_INFO_FAX
,CORP_INFO_EMAIL
,OWNER_INFO_POC
,OWNER_INFO_US_PHONE
,OWNER_INFO_US_PHONE_EX
,OWNER_INFO_NON_US_PHONE
,OWNER_INFO_FAX
,OWNER_INFO_EMAIL
,EDI
,TAXPAYER_ID
,AVG_NUM_EMPLOYEES
,ANNUAL_REVENUE
,SOCIAL_SECURITY_NUMBER
,FINANCIAL_INSTITUTE
,BANK_ACCT_NUMBER
,ABA_ROUTING
,BANK_ACCT_TYPE
,LOCKBOX_NUMBER
,AUTHORIZATION_DATE
,EFT_WAIVER
,ACH_US_PHONE
,ACH_NON_US_PHONE
,ACH_FAX
,ACH_EMAIL
,REMIT_POC
,REMIT_ADD1
,REMIT_ADD2
,REMIT_CITY
,REMIT_STATE
,REMIT_POSTAL_CODE
,REMIT_COUNTRY
,AR_POC
,AR_US_PHONE
,AR_US_PHONE_EX
,AR_NON_US_PHONE
,AR_FAX
,AR_EMAIL
,MPIN
,EDI_COORDINATOR
,EDI_US_PHONE
,EDI_US_PHONE_EX
,EDI_NON_US_PHONE
,EDI_FAX
,EDI_EMAIL
,VAN
,ISA_QUALIFIER
,ISA_SENDER_QUALIFIER
,GS02_IDENTIFIER
,RECEIVE_820
,HQ_PARENT_NAME
,HQ_PARENT_DUNS
,HQ_PARENT_ADD1
,HQ_PARENT_ADD2
,HQ_PARENT_CITY
,HQ_PARENT_STATE
,HQ_PARENT_POSTAL_CODE
,HQ_PARENT_COUNTRY
,HQ_PARENT_PHONE
,HQ_PARENT_RECORD_DATE
,GLOBAL_ULT_NAME
,GLOBAL_ULT_DUNS
,GLOBAL_ULT_ADD1
,GLOBAL_ULT_ADD2
,GLOBAL_ULT_CITY
,GLOBAL_ULT_POSTAL_CODE
,GLOBAL_ULT_COUNTRY
,GLOBAL_ULT_STATE
,GLOBAL_ULT_PHONE
,GLOBAL_ULT_RECORD_DATE
,DOMESTIC_ULT_NAME
,DOMESTIC_ULT_DUNS
,DOMESTIC_ULT_ADD1
,DOMESTIC_ULT_ADD2
,DOMESTIC_ULT_CITY
,DOMESTIC_ULT_POSTAL_CODE
,DOMESTIC_ULT_COUNTRY
,DOMESTIC_ULT_STATE
,DOMESTIC_ULT_PHONE
,DOMESTIC_ULT_RECORD_DATE
FROM fv_ccr_file_temp fcft
WHERE fcft.duns = substr(p_duns, 1, 9)
order by rowid;
UPDATE fv_ccr_vendors fcv SET fcv.ccr_status ='D',
fcv.enabled ='N' ,
fcv.extract_code ='1' ,
fcv.last_update_date = sysdate,
fcv.last_import_date = nvl(l_file_date,sysdate),
fcv.last_updated_by = fnd_global.user_id
WHERE exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
WHERE fcv.duns = fcpg.duns
AND nvl(fcv.plus_four,-99)= nvl(fcpg.plus_four,-99)
AND fcpg.extract_code = '1');
UPDATE fv_ccr_vendors fcv SET ccr_status ='E',
enabled='N',
extract_code ='4',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_import_date = nvl(l_file_date,sysdate)
WHERE exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
WHERE fcv.duns = fcpg.duns
AND nvl(fcv.plus_four,-99)= nvl(fcpg.plus_four,-99)
AND fcpg.extract_code = '4');
insert into fv_ccr_process_report(duns_info,record_type,reference1,reference2,reference3,reference4)
SELECT DUNS||nvl(plus_four,''),'1',legal_bus_name,' ',' ',decode(fcpg.extract_code,'1','Deleted','4','Expired')
FROM fv_ccr_process_gt fcpg
WHERE fcpg.extract_code IN ('1','4');
insert_temp_data(3,null,message_text ,null,l_ccr_data.duns||nvl(l_ccr_data.plus_four,''),null,null);
SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv
WHERE fcv.duns = l_ccr_data.duns
AND fcv.plus_four= l_ccr_data.plus_four ;
insert_temp_data(3,null,message_text ,null,l_ccr_data.duns||l_ccr_data.plus_four,null,null);
update fv_ccr_vendors fcv set
fcv.CCR_FLAG ='R' ,
fcv.CCR_STATUS =l_status ,
fcv.DUNS =l_ccr_data.DUNS ,
fcv.PLUS_FOUR =l_ccr_data.PLUS_FOUR ,
fcv.CAGE_CODE =l_ccr_data.CAGE_CODE ,
fcv.EXTRACT_CODE =l_ccr_data.EXTRACT_CODE ,
fcv.REGISTRATION_DATE =l_ccr_data.REGISTRATION_DATE ,
fcv.RENEWAL_DATE =l_ccr_data.RENEWAL_DATE ,
fcv.LEGAL_BUS_NAME =l_ccr_data.LEGAL_BUS_NAME ,
fcv.DBA_NAME =l_ccr_data.DBA_NAME ,
fcv.DIVISION_NAME =l_ccr_data.DIVISION_NAME ,
fcv.DIVISION_NUMBER =l_ccr_data.DIVISION_NUMBER ,
fcv.ST_ADDRESS1 =l_ccr_data.ST_ADDRESS1 ,
fcv.ST_ADDRESS2 =l_ccr_data.ST_ADDRESS2 ,
fcv.CITY =l_ccr_data.CITY ,
fcv.STATE =l_ccr_data.STATE ,
fcv.POSTAL_CODE =l_ccr_data.POSTAL_CODE ,
fcv.COUNTRY =l_ccr_data.COUNTRY ,
fcv.BUSINESS_START_DATE =l_ccr_data.BUSINESS_START_DATE ,
fcv.FISCAL_YR_CLOSE_DATE =l_ccr_data.FISCAL_YR_CLOSE_DATE ,
fcv.WEB_SITE =l_ccr_data.WEB_SITE ,
fcv.CREDIT_CARD_FLAG =l_ccr_data.CREDIT_CARD_FLAG ,
fcv.MAIL_POC =l_ccr_data.MAIL_POC ,
fcv.MAIL_ADD1 =l_ccr_data.MAIL_ADD1 ,
fcv.MAIL_ADD2 =l_ccr_data.MAIL_ADD2 ,
fcv.MAIL_CITY =l_ccr_data.MAIL_CITY ,
fcv.MAIL_POSTAL_CODE =l_ccr_data.MAIL_POSTAL_CODE ,
fcv.MAIL_COUNTRY =l_ccr_data.MAIL_COUNTRY ,
fcv.MAIL_STATE =l_ccr_data.MAIL_STATE ,
fcv.PREV_BUS_POC =l_ccr_data.PREV_BUS_POC ,
fcv.PREV_BUS_ADD1 =l_ccr_data.PREV_BUS_ADD1 ,
fcv.PREV_BUS_ADD2 =l_ccr_data.PREV_BUS_ADD2 ,
fcv.PREV_BUS_CITY =l_ccr_data.PREV_BUS_CITY ,
fcv.PREV_BUS_POSTAL_CODE =l_ccr_data.PREV_BUS_POSTAL_CODE ,
fcv.PREV_BUS_COUNTRY =l_ccr_data.PREV_BUS_COUNTRY ,
fcv.PREV_BUS_STATE =l_ccr_data.PREV_BUS_STATE ,
fcv.PARENT_POC =l_ccr_data.PARENT_POC ,
fcv.PARENT_DUNS =l_ccr_data.PARENT_DUNS ,
fcv.PARENT_ADD1 =l_ccr_data.PARENT_ADD1 ,
fcv.PARENT_ADD2 =l_ccr_data.PARENT_ADD2 ,
fcv.PARENT_CITY =l_ccr_data.PARENT_CITY ,
fcv.PARENT_POSTAL_CODE =l_ccr_data.PARENT_POSTAL_CODE ,
fcv.PARENT_COUNTRY =l_ccr_data.PARENT_COUNTRY ,
fcv.PARENT_STATE =l_ccr_data.PARENT_STATE ,
fcv.PARTY_PERF_POC =l_ccr_data.PARTY_PERF_POC ,
fcv.PARTY_PERF_ADD1 =l_ccr_data.PARTY_PERF_ADD1 ,
fcv.PARTY_PERF_ADD2 =l_ccr_data.PARTY_PERF_ADD2 ,
fcv.PARTY_PERF_CITY =l_ccr_data.PARTY_PERF_CITY ,
fcv.PARTY_PERF_POSTAL_CODE =l_ccr_data.PARTY_PERF_POSTAL_CODE ,
fcv.PARTY_PERF_COUNTRY =l_ccr_data.PARTY_PERF_COUNTRY ,
fcv.PARTY_PERF_STATE =l_ccr_data.PARTY_PERF_STATE ,
fcv.GOV_PARENT_POC =l_ccr_data.GOV_PARENT_POC ,
fcv.GOV_PARENT_ADD1 =l_ccr_data.GOV_PARENT_ADD1 ,
fcv.GOV_PARENT_ADD2 =l_ccr_data.GOV_PARENT_ADD2 ,
fcv.GOV_PARENT_CITY =l_ccr_data.GOV_PARENT_CITY ,
fcv.GOV_PARENT_POSTAL_CODE =l_ccr_data.GOV_PARENT_POSTAL_CODE ,
fcv.GOV_PARENT_COUNTRY =l_ccr_data.GOV_PARENT_COUNTRY ,
fcv.GOV_PARENT_STATE =l_ccr_data.GOV_PARENT_STATE ,
fcv.GOV_BUS_POC =l_ccr_data.GOV_BUS_POC ,
fcv.GOV_BUS_ADD1 =l_ccr_data.GOV_BUS_ADD1 ,
fcv.GOV_BUS_ADD2 =l_ccr_data.GOV_BUS_ADD2 ,
fcv.GOV_BUS_CITY =l_ccr_data.GOV_BUS_CITY ,
fcv.GOV_BUS_POSTAL_CODE =l_ccr_data.GOV_BUS_POSTAL_CODE ,
fcv.GOV_BUS_COUNTRY =l_ccr_data.GOV_BUS_COUNTRY ,
fcv.GOV_BUS_STATE =l_ccr_data.GOV_BUS_STATE ,
fcv.GOV_BUS_US_PHONE =l_ccr_data.GOV_BUS_US_PHONE ,
fcv.GOV_BUS_US_PHONE_EX =l_ccr_data.GOV_BUS_US_PHONE_EX ,
fcv.GOV_BUS_NON_US_PHONE =l_ccr_data.GOV_BUS_NON_US_PHONE ,
fcv.GOV_BUS_FAX =l_ccr_data.GOV_BUS_FAX ,
fcv.GOV_BUS_EMAIL =l_ccr_data.GOV_BUS_EMAIL ,
fcv.ALT_GOV_BUS_POC =l_ccr_data.ALT_GOV_BUS_POC ,
fcv.ALT_GOV_BUS_ADD1 =l_ccr_data.ALT_GOV_BUS_ADD1 ,
fcv.ALT_GOV_BUS_ADD2 =l_ccr_data.ALT_GOV_BUS_ADD2 ,
fcv.ALT_GOV_BUS_CITY =l_ccr_data.ALT_GOV_BUS_CITY ,
fcv.ALT_GOV_BUS_POSTAL_CODE =l_ccr_data.ALT_GOV_BUS_POSTAL_CODE ,
fcv.ALT_GOV_BUS_COUNTRY =l_ccr_data.ALT_GOV_BUS_COUNTRY ,
fcv.ALT_GOV_BUS_STATE =l_ccr_data.ALT_GOV_BUS_STATE ,
fcv.ALT_GOV_BUS_US_PHONE =l_ccr_data.ALT_GOV_BUS_US_PHONE ,
fcv.ALT_GOV_BUS_US_PHONE_EX =l_ccr_data.ALT_GOV_BUS_US_PHONE_EX ,
fcv.ALT_GOV_BUS_NON_US_PHONE =l_ccr_data.ALT_GOV_BUS_NON_US_PHONE ,
fcv.ALT_GOV_BUS_FAX =l_ccr_data.ALT_GOV_BUS_FAX ,
fcv.ALT_GOV_BUS_EMAIL =l_ccr_data.ALT_GOV_BUS_EMAIL ,
fcv.PAST_PERF_POC =l_ccr_data.PAST_PERF_POC ,
fcv.PAST_PERF_ADD1 =l_ccr_data.PAST_PERF_ADD1 ,
fcv.PAST_PERF_ADD2 =l_ccr_data.PAST_PERF_ADD2 ,
fcv.PAST_PERF_CITY =l_ccr_data.PAST_PERF_CITY ,
fcv.PAST_PERF_POSTAL_CODE =l_ccr_data.PAST_PERF_POSTAL_CODE ,
fcv.PAST_PERF_COUNTRY =l_ccr_data.PAST_PERF_COUNTRY ,
fcv.PAST_PERF_STATE =l_ccr_data.PAST_PERF_STATE ,
fcv.PAST_PERF_US_PHONE =l_ccr_data.PAST_PERF_US_PHONE ,
fcv.PAST_PERF_US_PHONE_EX =l_ccr_data.PAST_PERF_US_PHONE_EX ,
fcv.PAST_PERF_NON_US_PHONE =l_ccr_data.PAST_PERF_NON_US_PHONE ,
fcv.PAST_PERF_FAX =l_ccr_data.PAST_PERF_FAX ,
fcv.PAST_PERF_EMAIL =l_ccr_data.PAST_PERF_EMAIL ,
fcv.ALT_PAST_PERF_POC =l_ccr_data.ALT_PAST_PERF_POC ,
fcv.ALT_PAST_PERF_ADD1 =l_ccr_data.ALT_PAST_PERF_ADD1 ,
fcv.ALT_PAST_PERF_ADD2 =l_ccr_data.ALT_PAST_PERF_ADD2 ,
fcv.ALT_PAST_PERF_CITY =l_ccr_data.ALT_PAST_PERF_CITY ,
fcv.ALT_PAST_PERF_POSTAL_CODE =l_ccr_data.ALT_PAST_PERF_POSTAL_CODE ,
fcv.ALT_PAST_PERF_COUNTRY =l_ccr_data.ALT_PAST_PERF_COUNTRY ,
fcv.ALT_PAST_PERF_STATE =l_ccr_data.ALT_PAST_PERF_STATE ,
fcv.ALT_PAST_PERF_US_PHONE =l_ccr_data.ALT_PAST_PERF_US_PHONE ,
fcv.ALT_PAST_PERF_US_PHONE_EX =l_ccr_data.ALT_PAST_PERF_US_PHONE_EX ,
fcv.ALT_PAST_PERF_NON_US_PHONE =l_ccr_data.ALT_PAST_PERF_NON_US_PHONE ,
fcv.ALT_PAST_PERF_FAX =l_ccr_data.ALT_PAST_PERF_FAX ,
fcv.ALT_PAST_PERF_EMAIL =l_ccr_data.ALT_PAST_PERF_EMAIL ,
fcv.ELEC_BUS_POC =l_ccr_data.ELEC_BUS_POC ,
fcv.ELEC_BUS_ADD1 =l_ccr_data.ELEC_BUS_ADD1 ,
fcv.ELEC_BUS_ADD2 =l_ccr_data.ELEC_BUS_ADD2 ,
fcv.ELEC_BUS_CITY =l_ccr_data.ELEC_BUS_CITY ,
fcv.ELEC_BUS_POSTAL_CODE =l_ccr_data.ELEC_BUS_POSTAL_CODE ,
fcv.ELEC_BUS_COUNTRY =l_ccr_data.ELEC_BUS_COUNTRY ,
fcv.ELEC_BUS_STATE =l_ccr_data.ELEC_BUS_STATE ,
fcv.ELEC_BUS_US_PHONE =l_ccr_data.ELEC_BUS_US_PHONE ,
fcv.ELEC_BUS_US_PHONE_EX =l_ccr_data.ELEC_BUS_US_PHONE_EX ,
fcv.ELEC_BUS_NON_US_PHONE =l_ccr_data.ELEC_BUS_NON_US_PHONE ,
fcv.ELEC_BUS_FAX =l_ccr_data.ELEC_BUS_FAX ,
fcv.ELEC_BUS_EMAIL =l_ccr_data.ELEC_BUS_EMAIL ,
fcv.ALT_ELEC_BUS_POC =l_ccr_data.ALT_ELEC_BUS_POC ,
fcv.ALT_ELEC_BUS_ADD1 =l_ccr_data.ALT_ELEC_BUS_ADD1 ,
fcv.ALT_ELEC_BUS_ADD2 =l_ccr_data.ALT_ELEC_BUS_ADD2 ,
fcv.ALT_ELEC_BUS_CITY =l_ccr_data.ALT_ELEC_BUS_CITY ,
fcv.ALT_ELEC_BUS_POSTAL_CODE =l_ccr_data.ALT_ELEC_BUS_POSTAL_CODE ,
fcv.ALT_ELEC_BUS_COUNTRY =l_ccr_data.ALT_ELEC_BUS_COUNTRY ,
fcv.ALT_ELEC_BUS_STATE =l_ccr_data.ALT_ELEC_BUS_STATE ,
fcv.ALT_ELEC_BUS_US_PHONE =l_ccr_data.ALT_ELEC_BUS_US_PHONE ,
fcv.ALT_ELEC_BUS_US_PHONE_EX =l_ccr_data.ALT_ELEC_BUS_US_PHONE_EX ,
fcv.ALT_ELEC_BUS_NON_US_PHONE =l_ccr_data.ALT_ELEC_BUS_NON_US_PHONE ,
fcv.ALT_ELEC_BUS_FAX =l_ccr_data.ALT_ELEC_BUS_FAX ,
fcv.ALT_ELEC_BUS_EMAIL =l_ccr_data.ALT_ELEC_BUS_EMAIL ,
fcv.CERTIFIER_POC =l_ccr_data.CERTIFIER_POC ,
fcv.CERTIFIER_US_PHONE =l_ccr_data.CERTIFIER_US_PHONE ,
fcv.CERTIFIER_US_PHONE_EX =l_ccr_data.CERTIFIER_US_PHONE_EX ,
fcv.CERTIFIER_NON_US_PHONE =l_ccr_data.CERTIFIER_NON_US_PHONE ,
fcv.CERTIFIER_FAX =l_ccr_data.CERTIFIER_FAX ,
fcv.CERTIFIER_EMAIL =l_ccr_data.CERTIFIER_EMAIL ,
fcv.ALT_CERTIFIER_POC =l_ccr_data.ALT_CERTIFIER_POC ,
fcv.ALT_CERTIFIER_US_PHONE =l_ccr_data.ALT_CERTIFIER_US_PHONE ,
fcv.ALT_CERTIFIER_US_PHONE_EX =l_ccr_data.ALT_CERTIFIER_US_PHONE_EX ,
fcv.ALT_CERTIFIER_NON_US_PHONE =l_ccr_data.ALT_CERTIFIER_NON_US_PHONE ,
fcv.CORP_INFO_POC =l_ccr_data.CORP_INFO_POC ,
fcv.CORP_INFO_US_PHONE =l_ccr_data.CORP_INFO_US_PHONE ,
fcv.CORP_INFO_US_PHONE_EX =l_ccr_data.CORP_INFO_US_PHONE_EX ,
fcv.CORP_INFO_NON_US_PHONE =l_ccr_data.CORP_INFO_NON_US_PHONE ,
fcv.CORP_INFO_FAX =l_ccr_data.CORP_INFO_FAX ,
fcv.CORP_INFO_EMAIL =l_ccr_data.CORP_INFO_EMAIL ,
fcv.OWNER_INFO_POC =l_ccr_data.OWNER_INFO_POC ,
fcv.OWNER_INFO_US_PHONE =l_ccr_data.OWNER_INFO_US_PHONE ,
fcv.OWNER_INFO_US_PHONE_EX =l_ccr_data.OWNER_INFO_US_PHONE_EX ,
fcv.OWNER_INFO_NON_US_PHONE =l_ccr_data.OWNER_INFO_NON_US_PHONE ,
fcv.OWNER_INFO_FAX =l_ccr_data.OWNER_INFO_FAX ,
fcv.OWNER_INFO_EMAIL =l_ccr_data.OWNER_INFO_EMAIL ,
fcv.EDI =l_ccr_data.EDI ,
fcv.TAXPAYER_ID =l_ccr_data.TAXPAYER_ID ,
fcv.AVG_NUM_EMPLOYEES =l_ccr_data.AVG_NUM_EMPLOYEES ,
fcv.ANNUAL_REVENUE =l_ccr_data.ANNUAL_REVENUE ,
fcv.SOCIAL_SECURITY_NUMBER =l_ccr_data.SOCIAL_SECURITY_NUMBER ,
fcv.FINANCIAL_INSTITUTE =l_ccr_data.FINANCIAL_INSTITUTE ,
fcv.BANK_ACCT_NUMBER =l_ccr_data.BANK_ACCT_NUMBER ,
fcv.ABA_ROUTING =l_ccr_data.ABA_ROUTING ,
fcv.BANK_ACCT_TYPE =l_ccr_data.BANK_ACCT_TYPE ,
fcv.LOCKBOX_NUMBER =l_ccr_data.LOCKBOX_NUMBER ,
fcv.AUTHORIZATION_DATE =l_ccr_data.AUTHORIZATION_DATE ,
fcv.EFT_WAIVER =l_ccr_data.EFT_WAIVER ,
fcv.ACH_US_PHONE =l_ccr_data.ACH_US_PHONE ,
fcv.ACH_NON_US_PHONE =l_ccr_data.ACH_NON_US_PHONE ,
fcv.ACH_FAX =l_ccr_data.ACH_FAX ,
fcv.ACH_EMAIL =l_ccr_data.ACH_EMAIL ,
fcv.REMIT_POC =l_ccr_data.REMIT_POC ,
fcv.REMIT_ADD1 =l_ccr_data.REMIT_ADD1 ,
fcv.REMIT_ADD2 =l_ccr_data.REMIT_ADD2 ,
fcv.REMIT_CITY =l_ccr_data.REMIT_CITY ,
fcv.REMIT_STATE =l_ccr_data.REMIT_STATE ,
fcv.REMIT_POSTAL_CODE =l_ccr_data.REMIT_POSTAL_CODE ,
fcv.REMIT_COUNTRY =l_ccr_data.REMIT_COUNTRY ,
fcv.AR_POC =l_ccr_data.AR_POC ,
fcv.AR_US_PHONE =l_ccr_data.AR_US_PHONE ,
fcv.AR_US_PHONE_EX =l_ccr_data.AR_US_PHONE_EX ,
fcv.AR_NON_US_PHONE =l_ccr_data.AR_NON_US_PHONE ,
fcv.AR_FAX =l_ccr_data.AR_FAX ,
fcv.AR_EMAIL =l_ccr_data.AR_EMAIL ,
fcv.MPIN =l_ccr_data.MPIN ,
fcv.EDI_COORDINATOR =l_ccr_data.EDI_COORDINATOR ,
fcv.EDI_US_PHONE =l_ccr_data.EDI_US_PHONE ,
fcv.EDI_US_PHONE_EX =l_ccr_data.EDI_US_PHONE_EX ,
fcv.EDI_NON_US_PHONE =l_ccr_data.EDI_NON_US_PHONE ,
fcv.EDI_FAX =l_ccr_data.EDI_FAX ,
fcv.EDI_EMAIL =l_ccr_data.EDI_EMAIL ,
fcv.STATE_OF_INC =l_ccr_data.state_of_inc ,
fcv.COUNTRY_OF_INC =l_ccr_data.country_of_inc ,
fcv.BUSINESS_TYPE1 =l_code(1).code ,
fcv.BUSINESS_TYPE2 =l_code(2).code ,
fcv.BUSINESS_TYPE3 =l_code(3).code ,
fcv.BUSINESS_TYPE4 =l_code(4).code ,
fcv.BUSINESS_TYPE5 =l_code(5).code ,
fcv.BUSINESS_TYPE6 =l_code(6).code ,
fcv.BUSINESS_TYPE7 =l_code(7).code ,
fcv.BUSINESS_TYPE8 =l_code(8).code ,
fcv.BUSINESS_TYPE9 =l_code(9).code ,
fcv.BUSINESS_TYPE10 =l_code(10).code ,
fcv.SIC_CODE1 =l_code(11).code ,
fcv.SIC_CODE2 =l_code(12).code ,
fcv.SIC_CODE3 =l_code(13).code ,
fcv.SIC_CODE4 =l_code(14).code ,
fcv.SIC_CODE5 =l_code(15).code ,
fcv.SIC_CODE6 =l_code(16).code ,
fcv.SIC_CODE7 =l_code(17).code ,
fcv.SIC_CODE8 =l_code(18).code ,
fcv.SIC_CODE9 =l_code(19).code ,
fcv.SIC_CODE10 =l_code(20).code ,
fcv.SIC_CODE11 =l_code(21).code ,
fcv.SIC_CODE12 =l_code(22).code ,
fcv.SIC_CODE13 =l_code(23).code ,
fcv.SIC_CODE14 =l_code(24).code ,
fcv.SIC_CODE15 =l_code(25).code ,
fcv.SIC_CODE16 =l_code(26).code ,
fcv.SIC_CODE17 =l_code(27).code ,
fcv.SIC_CODE18 =l_code(28).code ,
fcv.SIC_CODE19 =l_code(29).code ,
fcv.SIC_CODE20 =l_code(30).code ,
fcv.NAICS_CODE1 =l_code(31).code ,
fcv.NAICS_CODE2 =l_code(32).code ,
fcv.NAICS_CODE3 =l_code(33).code ,
fcv.NAICS_CODE4 =l_code(34).code ,
fcv.NAICS_CODE5 =l_code(35).code ,
fcv.NAICS_CODE6 =l_code(36).code ,
fcv.NAICS_CODE7 =l_code(37).code ,
fcv.NAICS_CODE8 =l_code(38).code ,
fcv.NAICS_CODE9 =l_code(39).code ,
fcv.NAICS_CODE10 =l_code(40).code ,
fcv.NAICS_CODE11 =l_code(41).code ,
fcv.NAICS_CODE12 =l_code(42).code ,
fcv.NAICS_CODE13 =l_code(43).code ,
fcv.NAICS_CODE14 =l_code(44).code ,
fcv.NAICS_CODE15 =l_code(45).code ,
fcv.NAICS_CODE16 =l_code(46).code ,
fcv.NAICS_CODE17 =l_code(47).code ,
fcv.NAICS_CODE18 =l_code(48).code ,
fcv.NAICS_CODE19 =l_code(49).code ,
fcv.NAICS_CODE20 =l_code(50).code ,
fcv.FSC_CODE1 =l_code(51).code ,
fcv.FSC_CODE2 =l_code(52).code ,
fcv.FSC_CODE3 =l_code(53).code ,
fcv.FSC_CODE4 =l_code(54).code ,
fcv.FSC_CODE5 =l_code(55).code ,
fcv.FSC_CODE6 =l_code(56).code ,
fcv.FSC_CODE7 =l_code(57).code ,
fcv.FSC_CODE8 =l_code(58).code ,
fcv.FSC_CODE9 =l_code(59).code ,
fcv.FSC_CODE10 =l_code(60).code ,
fcv.PSC_CODE1 =l_code(61).code ,
fcv.PSC_CODE2 =l_code(62).code ,
fcv.PSC_CODE3 =l_code(63).code ,
fcv.PSC_CODE4 =l_code(64).code ,
fcv.PSC_CODE5 =l_code(65).code ,
fcv.PSC_CODE6 =l_code(66).code ,
fcv.PSC_CODE7 =l_code(67).code ,
fcv.PSC_CODE8 =l_code(68).code ,
fcv.PSC_CODE9 =l_code(69).code ,
fcv.PSC_CODE10 =l_code(70).code ,
fcv.organizational_type =l_code(71).code ,
fcv.correspondence_flag =l_code(72).code ,
fcv.corp_security_level =l_code(73).code ,
fcv.emp_security_level =l_code(74).code ,
fcv.last_update_date =sysdate,
fcv.last_updated_by =l_user_id,
fcv.last_import_date =l_ccr_data.file_date,
fcv.alt_certifier_email =l_ccr_data.alt_certifier_email,
fcv.alt_certifier_fax =l_ccr_data.alt_certifier_fax
WHERE fcv.duns= l_ccr_data.duns
AND nvl(fcv.plus_four,-99)= nvl(l_ccr_data.plus_four,-99) ;
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);
IF (p_xml_import = 'N' OR p_insert_data = 'Y') THEN -- bug 3931251
IF (l_ccr_data.extract_code ='3') THEN
l_errbuf := 'Error - the DUNS+4 does not exist in FV_CCR_VENDORS';
l_errbuf := 'Insert DUNS+4' || l_ccr_data.plus_four;
INSERT INTO FV_CCR_VENDORS (
CCR_ID ,
ENABLED ,
CCR_FLAG ,
CCR_STATUS ,
DUNS ,
PLUS_FOUR ,
CAGE_CODE ,
EXTRACT_CODE ,
REGISTRATION_DATE ,
RENEWAL_DATE ,
LEGAL_BUS_NAME ,
DBA_NAME ,
DIVISION_NAME ,
DIVISION_NUMBER ,
ST_ADDRESS1 ,
ST_ADDRESS2 ,
CITY ,
STATE ,
POSTAL_CODE ,
COUNTRY ,
BUSINESS_START_DATE ,
FISCAL_YR_CLOSE_DATE ,
CORP_SECURITY_LEVEL ,
EMP_SECURITY_LEVEL ,
WEB_SITE ,
CREDIT_CARD_FLAG ,
CORRESPONDENCE_FLAG ,
MAIL_POC ,
MAIL_ADD1 ,
MAIL_ADD2 ,
MAIL_CITY ,
MAIL_POSTAL_CODE ,
MAIL_COUNTRY ,
MAIL_STATE ,
PREV_BUS_POC ,
PREV_BUS_ADD1 ,
PREV_BUS_ADD2 ,
PREV_BUS_CITY ,
PREV_BUS_POSTAL_CODE ,
PREV_BUS_COUNTRY ,
PREV_BUS_STATE ,
PARENT_POC ,
PARENT_DUNS ,
PARENT_ADD1 ,
PARENT_ADD2 ,
PARENT_CITY ,
PARENT_POSTAL_CODE ,
PARENT_COUNTRY ,
PARENT_STATE ,
PARTY_PERF_POC ,
PARTY_PERF_ADD1 ,
PARTY_PERF_ADD2 ,
PARTY_PERF_CITY ,
PARTY_PERF_POSTAL_CODE ,
PARTY_PERF_COUNTRY ,
PARTY_PERF_STATE ,
GOV_PARENT_POC ,
GOV_PARENT_ADD1 ,
GOV_PARENT_ADD2 ,
GOV_PARENT_CITY ,
GOV_PARENT_POSTAL_CODE ,
GOV_PARENT_COUNTRY ,
GOV_PARENT_STATE ,
GOV_BUS_POC ,
GOV_BUS_ADD1 ,
GOV_BUS_ADD2 ,
GOV_BUS_CITY ,
GOV_BUS_POSTAL_CODE ,
GOV_BUS_COUNTRY ,
GOV_BUS_STATE ,
GOV_BUS_US_PHONE ,
GOV_BUS_US_PHONE_EX ,
GOV_BUS_NON_US_PHONE ,
GOV_BUS_FAX ,
GOV_BUS_EMAIL ,
ALT_GOV_BUS_POC ,
ALT_GOV_BUS_ADD1 ,
ALT_GOV_BUS_ADD2 ,
ALT_GOV_BUS_CITY ,
ALT_GOV_BUS_POSTAL_CODE ,
ALT_GOV_BUS_COUNTRY ,
ALT_GOV_BUS_STATE ,
ALT_GOV_BUS_US_PHONE ,
ALT_GOV_BUS_US_PHONE_EX ,
ALT_GOV_BUS_NON_US_PHONE ,
ALT_GOV_BUS_FAX ,
ALT_GOV_BUS_EMAIL ,
PAST_PERF_POC ,
PAST_PERF_ADD1 ,
PAST_PERF_ADD2 ,
PAST_PERF_CITY ,
PAST_PERF_POSTAL_CODE ,
PAST_PERF_COUNTRY ,
PAST_PERF_STATE ,
PAST_PERF_US_PHONE ,
PAST_PERF_US_PHONE_EX ,
PAST_PERF_NON_US_PHONE ,
PAST_PERF_FAX ,
PAST_PERF_EMAIL ,
ALT_PAST_PERF_POC ,
ALT_PAST_PERF_ADD1 ,
ALT_PAST_PERF_ADD2 ,
ALT_PAST_PERF_CITY ,
ALT_PAST_PERF_POSTAL_CODE ,
ALT_PAST_PERF_COUNTRY ,
ALT_PAST_PERF_STATE ,
ALT_PAST_PERF_US_PHONE ,
ALT_PAST_PERF_US_PHONE_EX ,
ALT_PAST_PERF_NON_US_PHONE ,
ALT_PAST_PERF_FAX ,
ALT_PAST_PERF_EMAIL ,
ELEC_BUS_POC ,
ELEC_BUS_ADD1 ,
ELEC_BUS_ADD2 ,
ELEC_BUS_CITY ,
ELEC_BUS_POSTAL_CODE ,
ELEC_BUS_COUNTRY ,
ELEC_BUS_STATE ,
ELEC_BUS_US_PHONE ,
ELEC_BUS_US_PHONE_EX ,
ELEC_BUS_NON_US_PHONE ,
ELEC_BUS_FAX ,
ELEC_BUS_EMAIL ,
ALT_ELEC_BUS_POC ,
ALT_ELEC_BUS_ADD1 ,
ALT_ELEC_BUS_ADD2 ,
ALT_ELEC_BUS_CITY ,
ALT_ELEC_BUS_POSTAL_CODE ,
ALT_ELEC_BUS_COUNTRY ,
ALT_ELEC_BUS_STATE ,
ALT_ELEC_BUS_US_PHONE ,
ALT_ELEC_BUS_US_PHONE_EX ,
ALT_ELEC_BUS_NON_US_PHONE ,
ALT_ELEC_BUS_FAX ,
ALT_ELEC_BUS_EMAIL ,
CERTIFIER_POC ,
CERTIFIER_US_PHONE ,
CERTIFIER_US_PHONE_EX ,
CERTIFIER_NON_US_PHONE ,
CERTIFIER_FAX ,
CERTIFIER_EMAIL ,
ALT_CERTIFIER_POC ,
ALT_CERTIFIER_US_PHONE ,
ALT_CERTIFIER_US_PHONE_EX ,
ALT_CERTIFIER_NON_US_PHONE ,
CORP_INFO_POC ,
CORP_INFO_US_PHONE ,
CORP_INFO_US_PHONE_EX ,
CORP_INFO_NON_US_PHONE ,
CORP_INFO_FAX ,
CORP_INFO_EMAIL ,
OWNER_INFO_POC ,
OWNER_INFO_US_PHONE ,
OWNER_INFO_US_PHONE_EX ,
OWNER_INFO_NON_US_PHONE ,
OWNER_INFO_FAX ,
OWNER_INFO_EMAIL ,
EDI ,
TAXPAYER_ID ,
AVG_NUM_EMPLOYEES ,
ANNUAL_REVENUE ,
SOCIAL_SECURITY_NUMBER ,
FINANCIAL_INSTITUTE ,
BANK_ACCT_NUMBER ,
ABA_ROUTING ,
BANK_ACCT_TYPE ,
LOCKBOX_NUMBER ,
AUTHORIZATION_DATE ,
EFT_WAIVER ,
ACH_US_PHONE ,
ACH_NON_US_PHONE ,
ACH_FAX ,
ACH_EMAIL ,
REMIT_POC ,
REMIT_ADD1 ,
REMIT_ADD2 ,
REMIT_CITY ,
REMIT_STATE ,
REMIT_POSTAL_CODE ,
REMIT_COUNTRY ,
AR_POC ,
AR_US_PHONE ,
AR_US_PHONE_EX ,
AR_NON_US_PHONE ,
AR_FAX ,
AR_EMAIL ,
MPIN ,
EDI_COORDINATOR ,
EDI_US_PHONE ,
EDI_US_PHONE_EX ,
EDI_NON_US_PHONE ,
EDI_FAX ,
EDI_EMAIL ,
BUSINESS_TYPE1 ,
BUSINESS_TYPE2 ,
BUSINESS_TYPE3 ,
BUSINESS_TYPE4 ,
BUSINESS_TYPE5 ,
BUSINESS_TYPE6 ,
BUSINESS_TYPE7 ,
BUSINESS_TYPE8 ,
BUSINESS_TYPE9 ,
BUSINESS_TYPE10 ,
SIC_CODE1 ,
SIC_CODE2 ,
SIC_CODE3 ,
SIC_CODE4 ,
SIC_CODE5 ,
SIC_CODE6 ,
SIC_CODE7 ,
SIC_CODE8 ,
SIC_CODE9 ,
SIC_CODE10 ,
SIC_CODE11 ,
SIC_CODE12 ,
SIC_CODE13 ,
SIC_CODE14 ,
SIC_CODE15 ,
SIC_CODE16 ,
SIC_CODE17 ,
SIC_CODE18 ,
SIC_CODE19 ,
SIC_CODE20 ,
NAICS_CODE1 ,
NAICS_CODE2 ,
NAICS_CODE3 ,
NAICS_CODE4 ,
NAICS_CODE5 ,
NAICS_CODE6 ,
NAICS_CODE7 ,
NAICS_CODE8 ,
NAICS_CODE9 ,
NAICS_CODE10 ,
NAICS_CODE11 ,
NAICS_CODE12 ,
NAICS_CODE13 ,
NAICS_CODE14 ,
NAICS_CODE15 ,
NAICS_CODE16 ,
NAICS_CODE17 ,
NAICS_CODE18 ,
NAICS_CODE19 ,
NAICS_CODE20 ,
FSC_CODE1 ,
FSC_CODE2 ,
FSC_CODE3 ,
FSC_CODE4 ,
FSC_CODE5 ,
FSC_CODE6 ,
FSC_CODE7 ,
FSC_CODE8 ,
FSC_CODE9 ,
FSC_CODE10 ,
PSC_CODE1 ,
PSC_CODE2 ,
PSC_CODE3 ,
PSC_CODE4 ,
PSC_CODE5 ,
PSC_CODE6 ,
PSC_CODE7 ,
PSC_CODE8 ,
PSC_CODE9 ,
PSC_CODE10 ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
last_import_date ,
ALT_CERTIFIER_FAX ,
ALT_CERTIFIER_EMAIL ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
STATE_OF_INC,
COUNTRY_OF_INC,
-- Added for bug 6339382
ORGANIZATIONAL_TYPE
)
SELECT FV_CCR_VENDORS_S.nextval ,'Y','R',l_status,
DUNS ,
PLUS_FOUR ,
CAGE_CODE ,
EXTRACT_CODE ,
REGISTRATION_DATE ,
RENEWAL_DATE ,
LEGAL_BUS_NAME ,
DBA_NAME ,
DIVISION_NAME ,
DIVISION_NUMBER ,
ST_ADDRESS1 ,
ST_ADDRESS2 ,
CITY ,
STATE ,
POSTAL_CODE ,
COUNTRY ,
BUSINESS_START_DATE ,
FISCAL_YR_CLOSE_DATE ,
CORP_SECURITY_LEVEL ,
EMP_SECURITY_LEVEL ,
WEB_SITE ,
CREDIT_CARD_FLAG ,
CORRESPONDENCE_FLAG ,
MAIL_POC ,
MAIL_ADD1 ,
MAIL_ADD2 ,
MAIL_CITY ,
MAIL_POSTAL_CODE ,
MAIL_COUNTRY ,
MAIL_STATE ,
PREV_BUS_POC ,
PREV_BUS_ADD1 ,
PREV_BUS_ADD2 ,
PREV_BUS_CITY ,
PREV_BUS_POSTAL_CODE ,
PREV_BUS_COUNTRY ,
PREV_BUS_STATE ,
PARENT_POC ,
PARENT_DUNS ,
PARENT_ADD1 ,
PARENT_ADD2 ,
PARENT_CITY ,
PARENT_POSTAL_CODE ,
PARENT_COUNTRY ,
PARENT_STATE ,
PARTY_PERF_POC ,
PARTY_PERF_ADD1 ,
PARTY_PERF_ADD2 ,
PARTY_PERF_CITY ,
PARTY_PERF_POSTAL_CODE ,
PARTY_PERF_COUNTRY ,
PARTY_PERF_STATE ,
GOV_PARENT_POC ,
GOV_PARENT_ADD1 ,
GOV_PARENT_ADD2 ,
GOV_PARENT_CITY ,
GOV_PARENT_POSTAL_CODE ,
GOV_PARENT_COUNTRY ,
GOV_PARENT_STATE ,
GOV_BUS_POC ,
GOV_BUS_ADD1 ,
GOV_BUS_ADD2 ,
GOV_BUS_CITY ,
GOV_BUS_POSTAL_CODE ,
GOV_BUS_COUNTRY ,
GOV_BUS_STATE ,
GOV_BUS_US_PHONE ,
GOV_BUS_US_PHONE_EX ,
GOV_BUS_NON_US_PHONE ,
GOV_BUS_FAX ,
GOV_BUS_EMAIL ,
ALT_GOV_BUS_POC ,
ALT_GOV_BUS_ADD1 ,
ALT_GOV_BUS_ADD2 ,
ALT_GOV_BUS_CITY ,
ALT_GOV_BUS_POSTAL_CODE ,
ALT_GOV_BUS_COUNTRY ,
ALT_GOV_BUS_STATE ,
ALT_GOV_BUS_US_PHONE ,
ALT_GOV_BUS_US_PHONE_EX ,
ALT_GOV_BUS_NON_US_PHONE ,
ALT_GOV_BUS_FAX ,
ALT_GOV_BUS_EMAIL ,
PAST_PERF_POC ,
PAST_PERF_ADD1 ,
PAST_PERF_ADD2 ,
PAST_PERF_CITY ,
PAST_PERF_POSTAL_CODE ,
PAST_PERF_COUNTRY ,
PAST_PERF_STATE ,
PAST_PERF_US_PHONE ,
PAST_PERF_US_PHONE_EX ,
PAST_PERF_NON_US_PHONE ,
PAST_PERF_FAX ,
PAST_PERF_EMAIL ,
ALT_PAST_PERF_POC ,
ALT_PAST_PERF_ADD1 ,
ALT_PAST_PERF_ADD2 ,
ALT_PAST_PERF_CITY ,
ALT_PAST_PERF_POSTAL_CODE ,
ALT_PAST_PERF_COUNTRY ,
ALT_PAST_PERF_STATE ,
ALT_PAST_PERF_US_PHONE ,
ALT_PAST_PERF_US_PHONE_EX ,
ALT_PAST_PERF_NON_US_PHONE ,
ALT_PAST_PERF_FAX ,
ALT_PAST_PERF_EMAIL ,
ELEC_BUS_POC ,
ELEC_BUS_ADD1 ,
ELEC_BUS_ADD2 ,
ELEC_BUS_CITY ,
ELEC_BUS_POSTAL_CODE ,
ELEC_BUS_COUNTRY ,
ELEC_BUS_STATE ,
ELEC_BUS_US_PHONE ,
ELEC_BUS_US_PHONE_EX ,
ELEC_BUS_NON_US_PHONE ,
ELEC_BUS_FAX ,
ELEC_BUS_EMAIL ,
ALT_ELEC_BUS_POC ,
ALT_ELEC_BUS_ADD1 ,
ALT_ELEC_BUS_ADD2 ,
ALT_ELEC_BUS_CITY ,
ALT_ELEC_BUS_POSTAL_CODE ,
ALT_ELEC_BUS_COUNTRY ,
ALT_ELEC_BUS_STATE ,
ALT_ELEC_BUS_US_PHONE ,
ALT_ELEC_BUS_US_PHONE_EX ,
ALT_ELEC_BUS_NON_US_PHONE ,
ALT_ELEC_BUS_FAX ,
ALT_ELEC_BUS_EMAIL ,
CERTIFIER_POC ,
CERTIFIER_US_PHONE ,
CERTIFIER_US_PHONE_EX ,
CERTIFIER_NON_US_PHONE ,
CERTIFIER_FAX ,
CERTIFIER_EMAIL ,
ALT_CERTIFIER_POC ,
ALT_CERTIFIER_US_PHONE ,
ALT_CERTIFIER_US_PHONE_EX ,
ALT_CERTIFIER_NON_US_PHONE ,
CORP_INFO_POC ,
CORP_INFO_US_PHONE ,
CORP_INFO_US_PHONE_EX ,
CORP_INFO_NON_US_PHONE ,
CORP_INFO_FAX ,
CORP_INFO_EMAIL ,
OWNER_INFO_POC ,
OWNER_INFO_US_PHONE ,
OWNER_INFO_US_PHONE_EX ,
OWNER_INFO_NON_US_PHONE ,
OWNER_INFO_FAX ,
OWNER_INFO_EMAIL ,
EDI ,
TAXPAYER_ID ,
AVG_NUM_EMPLOYEES ,
ANNUAL_REVENUE ,
SOCIAL_SECURITY_NUMBER ,
FINANCIAL_INSTITUTE ,
BANK_ACCT_NUMBER ,
ABA_ROUTING ,
BANK_ACCT_TYPE ,
LOCKBOX_NUMBER ,
AUTHORIZATION_DATE ,
EFT_WAIVER ,
ACH_US_PHONE ,
ACH_NON_US_PHONE ,
ACH_FAX ,
ACH_EMAIL ,
REMIT_POC ,
REMIT_ADD1 ,
REMIT_ADD2 ,
REMIT_CITY ,
REMIT_STATE ,
REMIT_POSTAL_CODE ,
REMIT_COUNTRY ,
AR_POC ,
AR_US_PHONE ,
AR_US_PHONE_EX ,
AR_NON_US_PHONE ,
AR_FAX ,
AR_EMAIL ,
MPIN ,
EDI_COORDINATOR ,
EDI_US_PHONE ,
EDI_US_PHONE_EX ,
EDI_NON_US_PHONE ,
EDI_FAX ,
EDI_EMAIL ,
l_code(1).code,
l_code(2).code,
l_code(3).code,
l_code(4).code,
l_code(5).code,
l_code(6).code,
l_code(7).code,
l_code(8).code,
l_code(9).code,
l_code(10).code,
l_code(11).code,
l_code(12).code,
l_code(13).code,
l_code(14).code,
l_code(15).code,
l_code(16).code,
l_code(17).code,
l_code(18).code,
l_code(19).code,
l_code(20).code,
l_code(21).code,
l_code(22).code,
l_code(23).code,
l_code(24).code,
l_code(25).code,
l_code(26).code,
l_code(27).code,
l_code(28).code,
l_code(29).code,
l_code(30).code,
l_code(31).code,
l_code(32).code,
l_code(33).code,
l_code(34).code,
l_code(35).code,
l_code(36).code,
l_code(37).code,
l_code(38).code,
l_code(39).code,
l_code(40).code,
l_code(41).code,
l_code(42).code,
l_code(43).code,
l_code(44).code,
l_code(45).code,
l_code(46).code,
l_code(47).code,
l_code(48).code,
l_code(49).code,
l_code(50).code,
l_code(51).code,
l_code(52).code,
l_code(53).code,
l_code(54).code,
l_code(55).code,
l_code(56).code,
l_code(57).code,
l_code(58).code,
l_code(59).code,
l_code(60).code,
l_code(61).code,
l_code(62).code,
l_code(63).code,
l_code(64).code,
l_code(65).code,
l_code(66).code,
l_code(67).code,
l_code(68).code,
l_code(69).code,
l_code(70).code,
sysdate ,
l_user_id,
file_date ,
ALT_CERTIFIER_FAX ,
ALT_CERTIFIER_EMAIL,
sysdate,
l_user_id,
l_user_id ,
State_of_inc,
COUNTRY_OF_INC,
-- Added for bug 6339382
l_code(71).code
FROM FV_CCR_PROCESS_GT fcpg
WHERE fcpg.duns = l_ccr_data.duns
AND fcpg.extract_code=l_ccr_data.extract_code
AND fcpg.plus_four = l_ccr_data.plus_four;
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);
END; -- end of select for dummy
SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv
WHERE fcv.duns = l_ccr_data.duns
AND fcv.plus_four is null;
insert_temp_data(3,null,message_text ,null,l_ccr_data.duns||l_ccr_data.plus_four,null,null);
update fv_ccr_vendors fcv set
fcv.CCR_FLAG ='R' ,
fcv.CCR_STATUS =l_status ,
fcv.DUNS =l_ccr_data.DUNS ,
fcv.PLUS_FOUR =null ,
fcv.CAGE_CODE =l_ccr_data.CAGE_CODE ,
fcv.EXTRACT_CODE =l_ccr_data.EXTRACT_CODE ,
fcv.REGISTRATION_DATE =l_ccr_data.REGISTRATION_DATE ,
fcv.RENEWAL_DATE =l_ccr_data.RENEWAL_DATE ,
fcv.LEGAL_BUS_NAME =l_ccr_data.LEGAL_BUS_NAME ,
fcv.DBA_NAME =l_ccr_data.DBA_NAME ,
fcv.DIVISION_NAME =l_ccr_data.DIVISION_NAME ,
fcv.DIVISION_NUMBER =l_ccr_data.DIVISION_NUMBER ,
fcv.ST_ADDRESS1 =l_ccr_data.ST_ADDRESS1 ,
fcv.ST_ADDRESS2 =l_ccr_data.ST_ADDRESS2 ,
fcv.CITY =l_ccr_data.CITY ,
fcv.STATE =l_ccr_data.STATE ,
fcv.POSTAL_CODE =l_ccr_data.POSTAL_CODE ,
fcv.COUNTRY =l_ccr_data.COUNTRY ,
fcv.BUSINESS_START_DATE =l_ccr_data.BUSINESS_START_DATE ,
fcv.FISCAL_YR_CLOSE_DATE =l_ccr_data.FISCAL_YR_CLOSE_DATE ,
fcv.WEB_SITE =l_ccr_data.WEB_SITE ,
fcv.CREDIT_CARD_FLAG =l_ccr_data.CREDIT_CARD_FLAG ,
fcv.MAIL_POC =l_ccr_data.MAIL_POC ,
fcv.MAIL_ADD1 =l_ccr_data.MAIL_ADD1 ,
fcv.MAIL_ADD2 =l_ccr_data.MAIL_ADD2 ,
fcv.MAIL_CITY =l_ccr_data.MAIL_CITY ,
fcv.MAIL_POSTAL_CODE =l_ccr_data.MAIL_POSTAL_CODE ,
fcv.MAIL_COUNTRY =l_ccr_data.MAIL_COUNTRY ,
fcv.MAIL_STATE =l_ccr_data.MAIL_STATE ,
fcv.PREV_BUS_POC =l_ccr_data.PREV_BUS_POC ,
fcv.PREV_BUS_ADD1 =l_ccr_data.PREV_BUS_ADD1 ,
fcv.PREV_BUS_ADD2 =l_ccr_data.PREV_BUS_ADD2 ,
fcv.PREV_BUS_CITY =l_ccr_data.PREV_BUS_CITY ,
fcv.PREV_BUS_POSTAL_CODE =l_ccr_data.PREV_BUS_POSTAL_CODE ,
fcv.PREV_BUS_COUNTRY =l_ccr_data.PREV_BUS_COUNTRY ,
fcv.PREV_BUS_STATE =l_ccr_data.PREV_BUS_STATE ,
fcv.PARENT_POC =l_ccr_data.PARENT_POC ,
fcv.PARENT_DUNS =l_ccr_data.PARENT_DUNS ,
fcv.PARENT_ADD1 =l_ccr_data.PARENT_ADD1 ,
fcv.PARENT_ADD2 =l_ccr_data.PARENT_ADD2 ,
fcv.PARENT_CITY =l_ccr_data.PARENT_CITY ,
fcv.PARENT_POSTAL_CODE =l_ccr_data.PARENT_POSTAL_CODE ,
fcv.PARENT_COUNTRY =l_ccr_data.PARENT_COUNTRY ,
fcv.PARENT_STATE =l_ccr_data.PARENT_STATE ,
fcv.PARTY_PERF_POC =l_ccr_data.PARTY_PERF_POC ,
fcv.PARTY_PERF_ADD1 =l_ccr_data.PARTY_PERF_ADD1 ,
fcv.PARTY_PERF_ADD2 =l_ccr_data.PARTY_PERF_ADD2 ,
fcv.PARTY_PERF_CITY =l_ccr_data.PARTY_PERF_CITY ,
fcv.PARTY_PERF_POSTAL_CODE =l_ccr_data.PARTY_PERF_POSTAL_CODE ,
fcv.PARTY_PERF_COUNTRY =l_ccr_data.PARTY_PERF_COUNTRY ,
fcv.PARTY_PERF_STATE =l_ccr_data.PARTY_PERF_STATE ,
fcv.GOV_PARENT_POC =l_ccr_data.GOV_PARENT_POC ,
fcv.GOV_PARENT_ADD1 =l_ccr_data.GOV_PARENT_ADD1 ,
fcv.GOV_PARENT_ADD2 =l_ccr_data.GOV_PARENT_ADD2 ,
fcv.GOV_PARENT_CITY =l_ccr_data.GOV_PARENT_CITY ,
fcv.GOV_PARENT_POSTAL_CODE =l_ccr_data.GOV_PARENT_POSTAL_CODE ,
fcv.GOV_PARENT_COUNTRY =l_ccr_data.GOV_PARENT_COUNTRY ,
fcv.GOV_PARENT_STATE =l_ccr_data.GOV_PARENT_STATE ,
fcv.GOV_BUS_POC =l_ccr_data.GOV_BUS_POC ,
fcv.GOV_BUS_ADD1 =l_ccr_data.GOV_BUS_ADD1 ,
fcv.GOV_BUS_ADD2 =l_ccr_data.GOV_BUS_ADD2 ,
fcv.GOV_BUS_CITY =l_ccr_data.GOV_BUS_CITY ,
fcv.GOV_BUS_POSTAL_CODE =l_ccr_data.GOV_BUS_POSTAL_CODE ,
fcv.GOV_BUS_COUNTRY =l_ccr_data.GOV_BUS_COUNTRY ,
fcv.GOV_BUS_STATE =l_ccr_data.GOV_BUS_STATE ,
fcv.GOV_BUS_US_PHONE =l_ccr_data.GOV_BUS_US_PHONE ,
fcv.GOV_BUS_US_PHONE_EX =l_ccr_data.GOV_BUS_US_PHONE_EX ,
fcv.GOV_BUS_NON_US_PHONE =l_ccr_data.GOV_BUS_NON_US_PHONE ,
fcv.GOV_BUS_FAX =l_ccr_data.GOV_BUS_FAX ,
fcv.GOV_BUS_EMAIL =l_ccr_data.GOV_BUS_EMAIL ,
fcv.ALT_GOV_BUS_POC =l_ccr_data.ALT_GOV_BUS_POC ,
fcv.ALT_GOV_BUS_ADD1 =l_ccr_data.ALT_GOV_BUS_ADD1 ,
fcv.ALT_GOV_BUS_ADD2 =l_ccr_data.ALT_GOV_BUS_ADD2 ,
fcv.ALT_GOV_BUS_CITY =l_ccr_data.ALT_GOV_BUS_CITY ,
fcv.ALT_GOV_BUS_POSTAL_CODE =l_ccr_data.ALT_GOV_BUS_POSTAL_CODE ,
fcv.ALT_GOV_BUS_COUNTRY =l_ccr_data.ALT_GOV_BUS_COUNTRY ,
fcv.ALT_GOV_BUS_STATE =l_ccr_data.ALT_GOV_BUS_STATE ,
fcv.ALT_GOV_BUS_US_PHONE =l_ccr_data.ALT_GOV_BUS_US_PHONE ,
fcv.ALT_GOV_BUS_US_PHONE_EX =l_ccr_data.ALT_GOV_BUS_US_PHONE_EX ,
fcv.ALT_GOV_BUS_NON_US_PHONE =l_ccr_data.ALT_GOV_BUS_NON_US_PHONE ,
fcv.ALT_GOV_BUS_FAX =l_ccr_data.ALT_GOV_BUS_FAX ,
fcv.ALT_GOV_BUS_EMAIL =l_ccr_data.ALT_GOV_BUS_EMAIL ,
fcv.PAST_PERF_POC =l_ccr_data.PAST_PERF_POC ,
fcv.PAST_PERF_ADD1 =l_ccr_data.PAST_PERF_ADD1 ,
fcv.PAST_PERF_ADD2 =l_ccr_data.PAST_PERF_ADD2 ,
fcv.PAST_PERF_CITY =l_ccr_data.PAST_PERF_CITY ,
fcv.PAST_PERF_POSTAL_CODE =l_ccr_data.PAST_PERF_POSTAL_CODE ,
fcv.PAST_PERF_COUNTRY =l_ccr_data.PAST_PERF_COUNTRY ,
fcv.PAST_PERF_STATE =l_ccr_data.PAST_PERF_STATE ,
fcv.PAST_PERF_US_PHONE =l_ccr_data.PAST_PERF_US_PHONE ,
fcv.PAST_PERF_US_PHONE_EX =l_ccr_data.PAST_PERF_US_PHONE_EX ,
fcv.PAST_PERF_NON_US_PHONE =l_ccr_data.PAST_PERF_NON_US_PHONE ,
fcv.PAST_PERF_FAX =l_ccr_data.PAST_PERF_FAX ,
fcv.PAST_PERF_EMAIL =l_ccr_data.PAST_PERF_EMAIL ,
fcv.ALT_PAST_PERF_POC =l_ccr_data.ALT_PAST_PERF_POC ,
fcv.ALT_PAST_PERF_ADD1 =l_ccr_data.ALT_PAST_PERF_ADD1 ,
fcv.ALT_PAST_PERF_ADD2 =l_ccr_data.ALT_PAST_PERF_ADD2 ,
fcv.ALT_PAST_PERF_CITY =l_ccr_data.ALT_PAST_PERF_CITY ,
fcv.ALT_PAST_PERF_POSTAL_CODE =l_ccr_data.ALT_PAST_PERF_POSTAL_CODE ,
fcv.ALT_PAST_PERF_COUNTRY =l_ccr_data.ALT_PAST_PERF_COUNTRY ,
fcv.ALT_PAST_PERF_STATE =l_ccr_data.ALT_PAST_PERF_STATE ,
fcv.ALT_PAST_PERF_US_PHONE =l_ccr_data.ALT_PAST_PERF_US_PHONE ,
fcv.ALT_PAST_PERF_US_PHONE_EX =l_ccr_data.ALT_PAST_PERF_US_PHONE_EX ,
fcv.ALT_PAST_PERF_NON_US_PHONE =l_ccr_data.ALT_PAST_PERF_NON_US_PHONE ,
fcv.ALT_PAST_PERF_FAX =l_ccr_data.ALT_PAST_PERF_FAX ,
fcv.ALT_PAST_PERF_EMAIL =l_ccr_data.ALT_PAST_PERF_EMAIL ,
fcv.ELEC_BUS_POC =l_ccr_data.ELEC_BUS_POC ,
fcv.ELEC_BUS_ADD1 =l_ccr_data.ELEC_BUS_ADD1 ,
fcv.ELEC_BUS_ADD2 =l_ccr_data.ELEC_BUS_ADD2 ,
fcv.ELEC_BUS_CITY =l_ccr_data.ELEC_BUS_CITY ,
fcv.ELEC_BUS_POSTAL_CODE =l_ccr_data.ELEC_BUS_POSTAL_CODE ,
fcv.ELEC_BUS_COUNTRY =l_ccr_data.ELEC_BUS_COUNTRY ,
fcv.ELEC_BUS_STATE =l_ccr_data.ELEC_BUS_STATE ,
fcv.ELEC_BUS_US_PHONE =l_ccr_data.ELEC_BUS_US_PHONE ,
fcv.ELEC_BUS_US_PHONE_EX =l_ccr_data.ELEC_BUS_US_PHONE_EX ,
fcv.ELEC_BUS_NON_US_PHONE =l_ccr_data.ELEC_BUS_NON_US_PHONE ,
fcv.ELEC_BUS_FAX =l_ccr_data.ELEC_BUS_FAX ,
fcv.ELEC_BUS_EMAIL =l_ccr_data.ELEC_BUS_EMAIL ,
fcv.ALT_ELEC_BUS_POC =l_ccr_data.ALT_ELEC_BUS_POC ,
fcv.ALT_ELEC_BUS_ADD1 =l_ccr_data.ALT_ELEC_BUS_ADD1 ,
fcv.ALT_ELEC_BUS_ADD2 =l_ccr_data.ALT_ELEC_BUS_ADD2 ,
fcv.ALT_ELEC_BUS_CITY =l_ccr_data.ALT_ELEC_BUS_CITY ,
fcv.ALT_ELEC_BUS_POSTAL_CODE =l_ccr_data.ALT_ELEC_BUS_POSTAL_CODE ,
fcv.ALT_ELEC_BUS_COUNTRY =l_ccr_data.ALT_ELEC_BUS_COUNTRY ,
fcv.ALT_ELEC_BUS_STATE =l_ccr_data.ALT_ELEC_BUS_STATE ,
fcv.ALT_ELEC_BUS_US_PHONE =l_ccr_data.ALT_ELEC_BUS_US_PHONE ,
fcv.ALT_ELEC_BUS_US_PHONE_EX =l_ccr_data.ALT_ELEC_BUS_US_PHONE_EX ,
fcv.ALT_ELEC_BUS_NON_US_PHONE =l_ccr_data.ALT_ELEC_BUS_NON_US_PHONE ,
fcv.ALT_ELEC_BUS_FAX =l_ccr_data.ALT_ELEC_BUS_FAX ,
fcv.ALT_ELEC_BUS_EMAIL =l_ccr_data.ALT_ELEC_BUS_EMAIL ,
fcv.CERTIFIER_POC =l_ccr_data.CERTIFIER_POC ,
fcv.CERTIFIER_US_PHONE =l_ccr_data.CERTIFIER_US_PHONE ,
fcv.CERTIFIER_US_PHONE_EX =l_ccr_data.CERTIFIER_US_PHONE_EX ,
fcv.CERTIFIER_NON_US_PHONE =l_ccr_data.CERTIFIER_NON_US_PHONE ,
fcv.CERTIFIER_FAX =l_ccr_data.CERTIFIER_FAX ,
fcv.CERTIFIER_EMAIL =l_ccr_data.CERTIFIER_EMAIL ,
fcv.ALT_CERTIFIER_POC =l_ccr_data.ALT_CERTIFIER_POC ,
fcv.ALT_CERTIFIER_US_PHONE =l_ccr_data.ALT_CERTIFIER_US_PHONE ,
fcv.ALT_CERTIFIER_US_PHONE_EX =l_ccr_data.ALT_CERTIFIER_US_PHONE_EX ,
fcv.ALT_CERTIFIER_NON_US_PHONE =l_ccr_data.ALT_CERTIFIER_NON_US_PHONE ,
fcv.CORP_INFO_POC =l_ccr_data.CORP_INFO_POC ,
fcv.CORP_INFO_US_PHONE =l_ccr_data.CORP_INFO_US_PHONE ,
fcv.CORP_INFO_US_PHONE_EX =l_ccr_data.CORP_INFO_US_PHONE_EX ,
fcv.CORP_INFO_NON_US_PHONE =l_ccr_data.CORP_INFO_NON_US_PHONE ,
fcv.CORP_INFO_FAX =l_ccr_data.CORP_INFO_FAX ,
fcv.CORP_INFO_EMAIL =l_ccr_data.CORP_INFO_EMAIL ,
fcv.OWNER_INFO_POC =l_ccr_data.OWNER_INFO_POC ,
fcv.OWNER_INFO_US_PHONE =l_ccr_data.OWNER_INFO_US_PHONE ,
fcv.OWNER_INFO_US_PHONE_EX =l_ccr_data.OWNER_INFO_US_PHONE_EX ,
fcv.OWNER_INFO_NON_US_PHONE =l_ccr_data.OWNER_INFO_NON_US_PHONE ,
fcv.OWNER_INFO_FAX =l_ccr_data.OWNER_INFO_FAX ,
fcv.OWNER_INFO_EMAIL =l_ccr_data.OWNER_INFO_EMAIL ,
fcv.EDI =l_ccr_data.EDI ,
fcv.TAXPAYER_ID =l_ccr_data.TAXPAYER_ID ,
fcv.AVG_NUM_EMPLOYEES =l_ccr_data.AVG_NUM_EMPLOYEES ,
fcv.ANNUAL_REVENUE =l_ccr_data.ANNUAL_REVENUE ,
fcv.SOCIAL_SECURITY_NUMBER =l_ccr_data.SOCIAL_SECURITY_NUMBER ,
fcv.FINANCIAL_INSTITUTE =l_ccr_data.FINANCIAL_INSTITUTE ,
fcv.BANK_ACCT_NUMBER =l_ccr_data.BANK_ACCT_NUMBER ,
fcv.ABA_ROUTING =l_ccr_data.ABA_ROUTING ,
fcv.BANK_ACCT_TYPE =l_ccr_data.BANK_ACCT_TYPE ,
fcv.LOCKBOX_NUMBER =l_ccr_data.LOCKBOX_NUMBER ,
fcv.AUTHORIZATION_DATE =l_ccr_data.AUTHORIZATION_DATE ,
fcv.EFT_WAIVER =l_ccr_data.EFT_WAIVER ,
fcv.ACH_US_PHONE =l_ccr_data.ACH_US_PHONE ,
fcv.ACH_NON_US_PHONE =l_ccr_data.ACH_NON_US_PHONE ,
fcv.ACH_FAX =l_ccr_data.ACH_FAX ,
fcv.ACH_EMAIL =l_ccr_data.ACH_EMAIL ,
fcv.REMIT_POC =l_ccr_data.REMIT_POC ,
fcv.REMIT_ADD1 =l_ccr_data.REMIT_ADD1 ,
fcv.REMIT_ADD2 =l_ccr_data.REMIT_ADD2 ,
fcv.REMIT_CITY =l_ccr_data.REMIT_CITY ,
fcv.REMIT_STATE =l_ccr_data.REMIT_STATE ,
fcv.REMIT_POSTAL_CODE =l_ccr_data.REMIT_POSTAL_CODE ,
fcv.REMIT_COUNTRY =l_ccr_data.REMIT_COUNTRY ,
fcv.AR_POC =l_ccr_data.AR_POC ,
fcv.AR_US_PHONE =l_ccr_data.AR_US_PHONE ,
fcv.AR_US_PHONE_EX =l_ccr_data.AR_US_PHONE_EX ,
fcv.AR_NON_US_PHONE =l_ccr_data.AR_NON_US_PHONE ,
fcv.AR_FAX =l_ccr_data.AR_FAX ,
fcv.AR_EMAIL =l_ccr_data.AR_EMAIL ,
fcv.MPIN =l_ccr_data.MPIN ,
fcv.EDI_COORDINATOR =l_ccr_data.EDI_COORDINATOR ,
fcv.EDI_US_PHONE =l_ccr_data.EDI_US_PHONE ,
fcv.EDI_US_PHONE_EX =l_ccr_data.EDI_US_PHONE_EX ,
fcv.EDI_NON_US_PHONE =l_ccr_data.EDI_NON_US_PHONE ,
fcv.EDI_FAX =l_ccr_data.EDI_FAX ,
fcv.EDI_EMAIL =l_ccr_data.EDI_EMAIL ,
fcv.last_update_date =sysdate ,
fcv.last_updated_by =l_user_id ,
fcv.BUSINESS_TYPE1 =l_code(1).code ,
fcv.BUSINESS_TYPE2 =l_code(2).code ,
fcv.BUSINESS_TYPE3 =l_code(3).code ,
fcv.BUSINESS_TYPE4 =l_code(4).code ,
fcv.BUSINESS_TYPE5 =l_code(5).code ,
fcv.BUSINESS_TYPE6 =l_code(6).code ,
fcv.BUSINESS_TYPE7 =l_code(7).code ,
fcv.BUSINESS_TYPE8 =l_code(8).code ,
fcv.BUSINESS_TYPE9 =l_code(9).code ,
fcv.BUSINESS_TYPE10 =l_code(10).code ,
fcv.SIC_CODE1 =l_code(11).code ,
fcv.SIC_CODE2 =l_code(12).code ,
fcv.SIC_CODE3 =l_code(13).code ,
fcv.SIC_CODE4 =l_code(14).code ,
fcv.SIC_CODE5 =l_code(15).code ,
fcv.SIC_CODE6 =l_code(16).code ,
fcv.SIC_CODE7 =l_code(17).code ,
fcv.SIC_CODE8 =l_code(18).code ,
fcv.SIC_CODE9 =l_code(19).code ,
fcv.SIC_CODE10 =l_code(20).code ,
fcv.SIC_CODE11 =l_code(21).code ,
fcv.SIC_CODE12 =l_code(22).code ,
fcv.SIC_CODE13 =l_code(23).code ,
fcv.SIC_CODE14 =l_code(24).code ,
fcv.SIC_CODE15 =l_code(25).code ,
fcv.SIC_CODE16 =l_code(26).code ,
fcv.SIC_CODE17 =l_code(27).code ,
fcv.SIC_CODE18 =l_code(28).code ,
fcv.SIC_CODE19 =l_code(29).code ,
fcv.SIC_CODE20 =l_code(30).code ,
fcv.NAICS_CODE1 =l_code(31).code ,
fcv.NAICS_CODE2 =l_code(32).code ,
fcv.NAICS_CODE3 =l_code(33).code ,
fcv.NAICS_CODE4 =l_code(34).code ,
fcv.NAICS_CODE5 =l_code(35).code ,
fcv.NAICS_CODE6 =l_code(36).code ,
fcv.NAICS_CODE7 =l_code(37).code ,
fcv.NAICS_CODE8 =l_code(38).code ,
fcv.NAICS_CODE9 =l_code(39).code ,
fcv.NAICS_CODE10 =l_code(40).code ,
fcv.NAICS_CODE11 =l_code(41).code ,
fcv.NAICS_CODE12 =l_code(42).code ,
fcv.NAICS_CODE13 =l_code(43).code ,
fcv.NAICS_CODE14 =l_code(44).code ,
fcv.NAICS_CODE15 =l_code(45).code ,
fcv.NAICS_CODE16 =l_code(46).code ,
fcv.NAICS_CODE17 =l_code(47).code ,
fcv.NAICS_CODE18 =l_code(48).code ,
fcv.NAICS_CODE19 =l_code(49).code ,
fcv.NAICS_CODE20 =l_code(50).code ,
fcv.FSC_CODE1 =l_code(51).code ,
fcv.FSC_CODE2 =l_code(52).code ,
fcv.FSC_CODE3 =l_code(53).code ,
fcv.FSC_CODE4 =l_code(54).code ,
fcv.FSC_CODE5 =l_code(55).code ,
fcv.FSC_CODE6 =l_code(56).code ,
fcv.FSC_CODE7 =l_code(57).code ,
fcv.FSC_CODE8 =l_code(58).code ,
fcv.FSC_CODE9 =l_code(59).code ,
fcv.FSC_CODE10 =l_code(60).code ,
fcv.PSC_CODE1 =l_code(61).code ,
fcv.PSC_CODE2 =l_code(62).code ,
fcv.PSC_CODE3 =l_code(63).code ,
fcv.PSC_CODE4 =l_code(64).code ,
fcv.PSC_CODE5 =l_code(65).code ,
fcv.PSC_CODE6 =l_code(66).code ,
fcv.PSC_CODE7 =l_code(67).code ,
fcv.PSC_CODE8 =l_code(68).code ,
fcv.PSC_CODE9 =l_code(69).code ,
fcv.PSC_CODE10 =l_code(70).code ,
fcv.organizational_type =l_code(71).code ,
fcv.correspondence_flag =l_code(72).code ,
fcv.corp_security_level =l_code(73).code ,
fcv.emp_security_level =l_code(74).code ,
fcv.last_import_date =l_ccr_data.file_date ,
fcv.STATE_OF_INC =l_ccr_data.state_of_inc ,
fcv.COUNTRY_OF_INC =l_ccr_data.country_of_inc ,
fcv.alt_certifier_email =l_ccr_data.alt_certifier_email,
fcv.alt_certifier_fax =l_ccr_data.alt_certifier_fax
WHERE fcv.duns= l_ccr_data.duns
and (fcv.plus_four is null
and l_ccr_data.plus_four is null);
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);
IF (p_xml_import ='N' or p_insert_data='Y') THEN
l_errbuf := 'exception no data found for duns ';
l_errbuf := 'Insert DUNS' || l_ccr_data.duns;
INSERT INTO FV_CCR_VENDORS (
CCR_ID ,
ENABLED ,
CCR_FLAG ,
CCR_STATUS ,
DUNS ,
PLUS_FOUR ,
CAGE_CODE ,
EXTRACT_CODE ,
REGISTRATION_DATE ,
RENEWAL_DATE ,
LEGAL_BUS_NAME ,
DBA_NAME ,
DIVISION_NAME ,
DIVISION_NUMBER ,
ST_ADDRESS1 ,
ST_ADDRESS2 ,
CITY ,
STATE ,
POSTAL_CODE ,
COUNTRY ,
BUSINESS_START_DATE ,
FISCAL_YR_CLOSE_DATE ,
CORP_SECURITY_LEVEL ,
EMP_SECURITY_LEVEL ,
WEB_SITE ,
CREDIT_CARD_FLAG ,
CORRESPONDENCE_FLAG ,
MAIL_POC ,
MAIL_ADD1 ,
MAIL_ADD2 ,
MAIL_CITY ,
MAIL_POSTAL_CODE ,
MAIL_COUNTRY ,
MAIL_STATE ,
PREV_BUS_POC ,
PREV_BUS_ADD1 ,
PREV_BUS_ADD2 ,
PREV_BUS_CITY ,
PREV_BUS_POSTAL_CODE ,
PREV_BUS_COUNTRY ,
PREV_BUS_STATE ,
PARENT_POC ,
PARENT_DUNS ,
PARENT_ADD1 ,
PARENT_ADD2 ,
PARENT_CITY ,
PARENT_POSTAL_CODE ,
PARENT_COUNTRY ,
PARENT_STATE ,
PARTY_PERF_POC ,
PARTY_PERF_ADD1 ,
PARTY_PERF_ADD2 ,
PARTY_PERF_CITY ,
PARTY_PERF_POSTAL_CODE ,
PARTY_PERF_COUNTRY ,
PARTY_PERF_STATE ,
GOV_PARENT_POC ,
GOV_PARENT_ADD1 ,
GOV_PARENT_ADD2 ,
GOV_PARENT_CITY ,
GOV_PARENT_POSTAL_CODE ,
GOV_PARENT_COUNTRY ,
GOV_PARENT_STATE ,
GOV_BUS_POC ,
GOV_BUS_ADD1 ,
GOV_BUS_ADD2 ,
GOV_BUS_CITY ,
GOV_BUS_POSTAL_CODE ,
GOV_BUS_COUNTRY ,
GOV_BUS_STATE ,
GOV_BUS_US_PHONE ,
GOV_BUS_US_PHONE_EX ,
GOV_BUS_NON_US_PHONE ,
GOV_BUS_FAX ,
GOV_BUS_EMAIL ,
ALT_GOV_BUS_POC ,
ALT_GOV_BUS_ADD1 ,
ALT_GOV_BUS_ADD2 ,
ALT_GOV_BUS_CITY ,
ALT_GOV_BUS_POSTAL_CODE ,
ALT_GOV_BUS_COUNTRY ,
ALT_GOV_BUS_STATE ,
ALT_GOV_BUS_US_PHONE ,
ALT_GOV_BUS_US_PHONE_EX ,
ALT_GOV_BUS_NON_US_PHONE ,
ALT_GOV_BUS_FAX ,
ALT_GOV_BUS_EMAIL ,
PAST_PERF_POC ,
PAST_PERF_ADD1 ,
PAST_PERF_ADD2 ,
PAST_PERF_CITY ,
PAST_PERF_POSTAL_CODE ,
PAST_PERF_COUNTRY ,
PAST_PERF_STATE ,
PAST_PERF_US_PHONE ,
PAST_PERF_US_PHONE_EX ,
PAST_PERF_NON_US_PHONE ,
PAST_PERF_FAX ,
PAST_PERF_EMAIL ,
ALT_PAST_PERF_POC ,
ALT_PAST_PERF_ADD1 ,
ALT_PAST_PERF_ADD2 ,
ALT_PAST_PERF_CITY ,
ALT_PAST_PERF_POSTAL_CODE ,
ALT_PAST_PERF_COUNTRY ,
ALT_PAST_PERF_STATE ,
ALT_PAST_PERF_US_PHONE ,
ALT_PAST_PERF_US_PHONE_EX ,
ALT_PAST_PERF_NON_US_PHONE ,
ALT_PAST_PERF_FAX ,
ALT_PAST_PERF_EMAIL ,
ELEC_BUS_POC ,
ELEC_BUS_ADD1 ,
ELEC_BUS_ADD2 ,
ELEC_BUS_CITY ,
ELEC_BUS_POSTAL_CODE ,
ELEC_BUS_COUNTRY ,
ELEC_BUS_STATE ,
ELEC_BUS_US_PHONE ,
ELEC_BUS_US_PHONE_EX ,
ELEC_BUS_NON_US_PHONE ,
ELEC_BUS_FAX ,
ELEC_BUS_EMAIL ,
ALT_ELEC_BUS_POC ,
ALT_ELEC_BUS_ADD1 ,
ALT_ELEC_BUS_ADD2 ,
ALT_ELEC_BUS_CITY ,
ALT_ELEC_BUS_POSTAL_CODE ,
ALT_ELEC_BUS_COUNTRY ,
ALT_ELEC_BUS_STATE ,
ALT_ELEC_BUS_US_PHONE ,
ALT_ELEC_BUS_US_PHONE_EX ,
ALT_ELEC_BUS_NON_US_PHONE ,
ALT_ELEC_BUS_FAX ,
ALT_ELEC_BUS_EMAIL ,
CERTIFIER_POC ,
CERTIFIER_US_PHONE ,
CERTIFIER_US_PHONE_EX ,
CERTIFIER_NON_US_PHONE ,
CERTIFIER_FAX ,
CERTIFIER_EMAIL ,
ALT_CERTIFIER_POC ,
ALT_CERTIFIER_US_PHONE ,
ALT_CERTIFIER_US_PHONE_EX ,
ALT_CERTIFIER_NON_US_PHONE ,
CORP_INFO_POC ,
CORP_INFO_US_PHONE ,
CORP_INFO_US_PHONE_EX ,
CORP_INFO_NON_US_PHONE ,
CORP_INFO_FAX ,
CORP_INFO_EMAIL ,
OWNER_INFO_POC ,
OWNER_INFO_US_PHONE ,
OWNER_INFO_US_PHONE_EX ,
OWNER_INFO_NON_US_PHONE ,
OWNER_INFO_FAX ,
OWNER_INFO_EMAIL ,
EDI ,
TAXPAYER_ID ,
AVG_NUM_EMPLOYEES ,
ANNUAL_REVENUE ,
SOCIAL_SECURITY_NUMBER ,
FINANCIAL_INSTITUTE ,
BANK_ACCT_NUMBER ,
ABA_ROUTING ,
BANK_ACCT_TYPE ,
LOCKBOX_NUMBER ,
AUTHORIZATION_DATE ,
EFT_WAIVER ,
ACH_US_PHONE ,
ACH_NON_US_PHONE ,
ACH_FAX ,
ACH_EMAIL ,
REMIT_POC ,
REMIT_ADD1 ,
REMIT_ADD2 ,
REMIT_CITY ,
REMIT_STATE ,
REMIT_POSTAL_CODE ,
REMIT_COUNTRY ,
AR_POC ,
AR_US_PHONE ,
AR_US_PHONE_EX ,
AR_NON_US_PHONE ,
AR_FAX ,
AR_EMAIL ,
MPIN ,
EDI_COORDINATOR ,
EDI_US_PHONE ,
EDI_US_PHONE_EX ,
EDI_NON_US_PHONE ,
EDI_FAX ,
EDI_EMAIL ,
BUSINESS_TYPE1 ,
BUSINESS_TYPE2 ,
BUSINESS_TYPE3 ,
BUSINESS_TYPE4 ,
BUSINESS_TYPE5 ,
BUSINESS_TYPE6 ,
BUSINESS_TYPE7 ,
BUSINESS_TYPE8 ,
BUSINESS_TYPE9 ,
BUSINESS_TYPE10 ,
SIC_CODE1 ,
SIC_CODE2 ,
SIC_CODE3 ,
SIC_CODE4 ,
SIC_CODE5 ,
SIC_CODE6 ,
SIC_CODE7 ,
SIC_CODE8 ,
SIC_CODE9 ,
SIC_CODE10 ,
SIC_CODE11 ,
SIC_CODE12 ,
SIC_CODE13 ,
SIC_CODE14 ,
SIC_CODE15 ,
SIC_CODE16 ,
SIC_CODE17 ,
SIC_CODE18 ,
SIC_CODE19 ,
SIC_CODE20 ,
NAICS_CODE1 ,
NAICS_CODE2 ,
NAICS_CODE3 ,
NAICS_CODE4 ,
NAICS_CODE5 ,
NAICS_CODE6 ,
NAICS_CODE7 ,
NAICS_CODE8 ,
NAICS_CODE9 ,
NAICS_CODE10 ,
NAICS_CODE11 ,
NAICS_CODE12 ,
NAICS_CODE13 ,
NAICS_CODE14 ,
NAICS_CODE15 ,
NAICS_CODE16 ,
NAICS_CODE17 ,
NAICS_CODE18 ,
NAICS_CODE19 ,
NAICS_CODE20 ,
FSC_CODE1 ,
FSC_CODE2 ,
FSC_CODE3 ,
FSC_CODE4 ,
FSC_CODE5 ,
FSC_CODE6 ,
FSC_CODE7 ,
FSC_CODE8 ,
FSC_CODE9 ,
FSC_CODE10 ,
PSC_CODE1 ,
PSC_CODE2 ,
PSC_CODE3 ,
PSC_CODE4 ,
PSC_CODE5 ,
PSC_CODE6 ,
PSC_CODE7 ,
PSC_CODE8 ,
PSC_CODE9 ,
PSC_CODE10 ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
last_import_date ,
ALT_CERTIFIER_FAX ,
ALT_CERTIFIER_EMAIL,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
state_of_inc ,
COUNTRY_OF_INC,
-- Added for bug 6339382
ORGANIZATIONAL_TYPE
)
SELECT FV_CCR_VENDORS_S.nextval ,'Y','R',l_status,
DUNS ,
PLUS_FOUR ,
CAGE_CODE ,
EXTRACT_CODE ,
REGISTRATION_DATE ,
RENEWAL_DATE ,
LEGAL_BUS_NAME ,
DBA_NAME ,
DIVISION_NAME ,
DIVISION_NUMBER ,
ST_ADDRESS1 ,
ST_ADDRESS2 ,
CITY ,
STATE ,
POSTAL_CODE ,
COUNTRY ,
BUSINESS_START_DATE ,
FISCAL_YR_CLOSE_DATE ,
CORP_SECURITY_LEVEL ,
EMP_SECURITY_LEVEL ,
WEB_SITE ,
CREDIT_CARD_FLAG ,
CORRESPONDENCE_FLAG ,
MAIL_POC ,
MAIL_ADD1 ,
MAIL_ADD2 ,
MAIL_CITY ,
MAIL_POSTAL_CODE ,
MAIL_COUNTRY ,
MAIL_STATE ,
PREV_BUS_POC ,
PREV_BUS_ADD1 ,
PREV_BUS_ADD2 ,
PREV_BUS_CITY ,
PREV_BUS_POSTAL_CODE ,
PREV_BUS_COUNTRY ,
PREV_BUS_STATE ,
PARENT_POC ,
PARENT_DUNS ,
PARENT_ADD1 ,
PARENT_ADD2 ,
PARENT_CITY ,
PARENT_POSTAL_CODE ,
PARENT_COUNTRY ,
PARENT_STATE ,
PARTY_PERF_POC ,
PARTY_PERF_ADD1 ,
PARTY_PERF_ADD2 ,
PARTY_PERF_CITY ,
PARTY_PERF_POSTAL_CODE ,
PARTY_PERF_COUNTRY ,
PARTY_PERF_STATE ,
GOV_PARENT_POC ,
GOV_PARENT_ADD1 ,
GOV_PARENT_ADD2 ,
GOV_PARENT_CITY ,
GOV_PARENT_POSTAL_CODE ,
GOV_PARENT_COUNTRY ,
GOV_PARENT_STATE ,
GOV_BUS_POC ,
GOV_BUS_ADD1 ,
GOV_BUS_ADD2 ,
GOV_BUS_CITY ,
GOV_BUS_POSTAL_CODE ,
GOV_BUS_COUNTRY ,
GOV_BUS_STATE ,
GOV_BUS_US_PHONE ,
GOV_BUS_US_PHONE_EX ,
GOV_BUS_NON_US_PHONE ,
GOV_BUS_FAX ,
GOV_BUS_EMAIL ,
ALT_GOV_BUS_POC ,
ALT_GOV_BUS_ADD1 ,
ALT_GOV_BUS_ADD2 ,
ALT_GOV_BUS_CITY ,
ALT_GOV_BUS_POSTAL_CODE ,
ALT_GOV_BUS_COUNTRY ,
ALT_GOV_BUS_STATE ,
ALT_GOV_BUS_US_PHONE ,
ALT_GOV_BUS_US_PHONE_EX ,
ALT_GOV_BUS_NON_US_PHONE ,
ALT_GOV_BUS_FAX ,
ALT_GOV_BUS_EMAIL ,
PAST_PERF_POC ,
PAST_PERF_ADD1 ,
PAST_PERF_ADD2 ,
PAST_PERF_CITY ,
PAST_PERF_POSTAL_CODE ,
PAST_PERF_COUNTRY ,
PAST_PERF_STATE ,
PAST_PERF_US_PHONE ,
PAST_PERF_US_PHONE_EX ,
PAST_PERF_NON_US_PHONE ,
PAST_PERF_FAX ,
PAST_PERF_EMAIL ,
ALT_PAST_PERF_POC ,
ALT_PAST_PERF_ADD1 ,
ALT_PAST_PERF_ADD2 ,
ALT_PAST_PERF_CITY ,
ALT_PAST_PERF_POSTAL_CODE ,
ALT_PAST_PERF_COUNTRY ,
ALT_PAST_PERF_STATE ,
ALT_PAST_PERF_US_PHONE ,
ALT_PAST_PERF_US_PHONE_EX ,
ALT_PAST_PERF_NON_US_PHONE ,
ALT_PAST_PERF_FAX ,
ALT_PAST_PERF_EMAIL ,
ELEC_BUS_POC ,
ELEC_BUS_ADD1 ,
ELEC_BUS_ADD2 ,
ELEC_BUS_CITY ,
ELEC_BUS_POSTAL_CODE ,
ELEC_BUS_COUNTRY ,
ELEC_BUS_STATE ,
ELEC_BUS_US_PHONE ,
ELEC_BUS_US_PHONE_EX ,
ELEC_BUS_NON_US_PHONE ,
ELEC_BUS_FAX ,
ELEC_BUS_EMAIL ,
ALT_ELEC_BUS_POC ,
ALT_ELEC_BUS_ADD1 ,
ALT_ELEC_BUS_ADD2 ,
ALT_ELEC_BUS_CITY ,
ALT_ELEC_BUS_POSTAL_CODE ,
ALT_ELEC_BUS_COUNTRY ,
ALT_ELEC_BUS_STATE ,
ALT_ELEC_BUS_US_PHONE ,
ALT_ELEC_BUS_US_PHONE_EX ,
ALT_ELEC_BUS_NON_US_PHONE ,
ALT_ELEC_BUS_FAX ,
ALT_ELEC_BUS_EMAIL ,
CERTIFIER_POC ,
CERTIFIER_US_PHONE ,
CERTIFIER_US_PHONE_EX ,
CERTIFIER_NON_US_PHONE ,
CERTIFIER_FAX ,
CERTIFIER_EMAIL ,
ALT_CERTIFIER_POC ,
ALT_CERTIFIER_US_PHONE ,
ALT_CERTIFIER_US_PHONE_EX ,
ALT_CERTIFIER_NON_US_PHONE ,
CORP_INFO_POC ,
CORP_INFO_US_PHONE ,
CORP_INFO_US_PHONE_EX ,
CORP_INFO_NON_US_PHONE ,
CORP_INFO_FAX ,
CORP_INFO_EMAIL ,
OWNER_INFO_POC ,
OWNER_INFO_US_PHONE ,
OWNER_INFO_US_PHONE_EX ,
OWNER_INFO_NON_US_PHONE ,
OWNER_INFO_FAX ,
OWNER_INFO_EMAIL ,
EDI ,
TAXPAYER_ID ,
AVG_NUM_EMPLOYEES ,
ANNUAL_REVENUE ,
SOCIAL_SECURITY_NUMBER ,
FINANCIAL_INSTITUTE ,
BANK_ACCT_NUMBER ,
ABA_ROUTING ,
BANK_ACCT_TYPE ,
LOCKBOX_NUMBER ,
AUTHORIZATION_DATE ,
EFT_WAIVER ,
ACH_US_PHONE ,
ACH_NON_US_PHONE ,
ACH_FAX ,
ACH_EMAIL ,
REMIT_POC ,
REMIT_ADD1 ,
REMIT_ADD2 ,
REMIT_CITY ,
REMIT_STATE ,
REMIT_POSTAL_CODE ,
REMIT_COUNTRY ,
AR_POC ,
AR_US_PHONE ,
AR_US_PHONE_EX ,
AR_NON_US_PHONE ,
AR_FAX ,
AR_EMAIL ,
MPIN ,
EDI_COORDINATOR ,
EDI_US_PHONE ,
EDI_US_PHONE_EX ,
EDI_NON_US_PHONE ,
EDI_FAX ,
EDI_EMAIL ,
l_code(1).code,
l_code(2).code,
l_code(3).code,
l_code(4).code,
l_code(5).code,
l_code(6).code,
l_code(7).code,
l_code(8).code,
l_code(9).code,
l_code(10).code,
l_code(11).code,
l_code(12).code,
l_code(13).code,
l_code(14).code,
l_code(15).code,
l_code(16).code,
l_code(17).code,
l_code(18).code,
l_code(19).code,
l_code(20).code,
l_code(21).code,
l_code(22).code,
l_code(23).code,
l_code(24).code,
l_code(25).code,
l_code(26).code,
l_code(27).code,
l_code(28).code,
l_code(29).code,
l_code(30).code,
l_code(31).code,
l_code(32).code,
l_code(33).code,
l_code(34).code,
l_code(35).code,
l_code(36).code,
l_code(37).code,
l_code(38).code,
l_code(39).code,
l_code(40).code,
l_code(41).code,
l_code(42).code,
l_code(43).code,
l_code(44).code,
l_code(45).code,
l_code(46).code,
l_code(47).code,
l_code(48).code,
l_code(49).code,
l_code(50).code,
l_code(51).code,
l_code(52).code,
l_code(53).code,
l_code(54).code,
l_code(55).code,
l_code(56).code,
l_code(57).code,
l_code(58).code,
l_code(59).code,
l_code(60).code,
l_code(61).code,
l_code(62).code,
l_code(63).code,
l_code(64).code,
l_code(65).code,
l_code(66).code,
l_code(67).code,
l_code(68).code,
l_code(69).code,
l_code(70).code,
sysdate ,
l_user_id,
file_date,
ALT_CERTIFIER_FAX ,
ALT_CERTIFIER_EMAIL,
sysdate,
l_user_id,
l_user_id ,
state_of_inc,
COUNTRY_OF_INC,
-- Added for bug 6339382
l_code(71).code
FROM FV_CCR_PROCESS_GT fcpg
WHERE fcpg.duns = l_ccr_data.duns
AND fcpg.extract_code=l_ccr_data.extract_code
AND fcpg.plus_four is null ;
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);
insert_temp_data(3,null,message_text ,null,'ORACLE',null,null);
l_errbuf := 'Updating the duns+4 as expired/deleted based on DUNS';
update fv_ccr_vendors fcvp set fcvp.ccr_status='E' ,
fcvp.enabled='N',
fcvp.extract_code=decode(fcvp.ccr_status,'E',fcvp.extract_code,'4')
where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
and fcvr.plus_four is null
and fcvr.ccr_status='E' )
and fcvp.ccr_status<>'N';
update fv_ccr_vendors fcvp set fcvp.ccr_status='D' ,
fcvp.enabled='N',
fcvp.extract_code=decode(fcvp.ccr_status,'D',fcvp.extract_code,'1')
where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
and fcvr.plus_four is null
and fcvr.ccr_status='D' )
and fcvp.ccr_status<>'N';
l_errbuf := 'Updating the duns+4 as expired/deleted based on DUNS - Done';
IF l_update_type = 'A' THEN
UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',
CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
WHERE fcv.duns = fcpg.duns
AND NVL(fcv.plus_four, 'NO_PLUS4') =
NVL(fcpg.plus_four, 'NO_PLUS4'))
AND (fcv.ccr_status ='A' OR fcv.ccr_status = 'N');
ELSIF l_update_type = 'S' THEN
UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',
CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
WHERE fcv.duns = fcpg.duns
AND NVL(fcv.plus_four, 'NO_PLUS4') =
NVL(fcpg.plus_four, 'NO_PLUS4'))
AND (fcv.ccr_status ='A' OR fcv.ccr_status = 'N')
AND fcv.duns = SUBSTR(p_duns, 1, 9)
AND (p_xml_import <> 'Y' OR
((NVL(fcv.plus_four, 'NO_PLUS4') = NVL(SUBSTR(p_duns, 10, 4), 'NO_PLUS4'))
OR fcv.plus_four IS NULL));
ELSIF l_update_type = 'N' THEN
UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',
CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
WHERE fcv.duns = fcpg.duns
AND NVL(fcv.plus_four, 'NO_PLUS4') =
NVL(fcpg.plus_four, 'NO_PLUS4'))
AND fcv.ccr_status = 'N';
update fv_ccr_vendors set extract_code=decode(l_file_type,'M','A','2')
where duns in ( select distinct duns from fv_ccr_process_gt)
and extract_code ='N'
and plus_four is null;
update fv_ccr_vendors fcv set fcv.enabled='Y'
where fcv.enabled='N' and fcv.ccr_status='A'
and not exists (select 1 from fv_ccr_orgs fco
where fco.ccr_id = fcv.ccr_id);
IF (p_xml_import='Y' and p_insert_data='Y') THEN
FOR crec in (SELECT duns, plus_four from fv_ccr_process_gt fcpg
WHERE fcpg.extract_code in ('1', '4')
AND not exists (SELECT 1 FROM fv_ccr_vendors
WHERE duns = fcpg.duns
AND nvl(plus_four,'N') = nvl(fcpg.plus_four,'N')
))
LOOP
FND_MESSAGE.set_NAME('FV','FV_CCR_XML_INACTIVE_DUNS');
insert_temp_data(3,null,message_text ,null,crec.duns||crec.plus_four,null,null);
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);
delete from fv_ccr_file_temp;
delete from fv_ccr_process_gt;
delete from fv_ccr_process_report ;