eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FV_CCR_DATA_LOAD_PKG dependencies on FV_CCR_VENDORS

Line 406: from fv_ccr_vendors fcv

402:
403:
404: CURSOR c_taxpayer is
405: select distinct fcv.taxpayer_id,fcv.vendor_id
406: from fv_ccr_vendors fcv
407: where exists (SELECT 1 FROM fv_ccr_vendors fcv_in
408: WHERE fcv_in.taxpayer_id=fcv.taxpayer_id
409: AND fcv_in.vendor_id = fcv.vendor_id
410: AND fcv_in.legal_bus_name<>fcv.legal_bus_name

Line 407: where exists (SELECT 1 FROM fv_ccr_vendors fcv_in

403:
404: CURSOR c_taxpayer is
405: select distinct fcv.taxpayer_id,fcv.vendor_id
406: from fv_ccr_vendors fcv
407: where exists (SELECT 1 FROM fv_ccr_vendors fcv_in
408: WHERE fcv_in.taxpayer_id=fcv.taxpayer_id
409: AND fcv_in.vendor_id = fcv.vendor_id
410: AND fcv_in.legal_bus_name<>fcv.legal_bus_name
411: AND fcv_in.taxpayer_id is not null

Line 424: from fv_ccr_vendors fcv

420: where fcpg.duns = fcv.duns and fcpg.plus_four IS NULL);
421:
422: CURSOR c_duns_info(p_taxpayer_id varchar2,p_vendor_id number) IS
423: select fcv.duns,fcv.plus_four ,fcv.legal_bus_name,fcv.taxpayer_id
424: from fv_ccr_vendors fcv
425: where fcv.plus_four is null
426: and fcv.taxpayer_id=p_taxpayer_id
427: and fcv.vendor_id=p_vendor_id;
428:

Line 698: -- reset the status in fv_ccr_vendors as 'N' for extract code

694:
695: l_errbuf := 'Push data into fv_ccr_process_gt based on the update type';
696: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
697:
698: -- reset the status in fv_ccr_vendors as 'N' for extract code
699: UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
700:
701: ELSE
702: --This program is called from xml import

Line 699: UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';

695: l_errbuf := 'Push data into fv_ccr_process_gt based on the update type';
696: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
697:
698: -- reset the status in fv_ccr_vendors as 'N' for extract code
699: UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
700:
701: ELSE
702: --This program is called from xml import
703: -- Added the below code as Data Load will be called from xml with update_type A

Line 704: UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';

700:
701: ELSE
702: --This program is called from xml import
703: -- Added the below code as Data Load will be called from xml with update_type A
704: UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
705: l_errbuf := 'The program is being called from xml import -> xml-import parameter '|| p_xml_import;
706: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
707: END IF ; --end of xml_import <> Y
708:

Line 1672: WHERE exists ( select 1 from fv_ccr_vendors fcv

1668: ,fcft.ALT_CERTIFIER_FAX
1669: ,fcft.ALT_CERTIFIER_EMAIL
1670:
1671: FROM fv_ccr_file_temp fcft
1672: WHERE exists ( select 1 from fv_ccr_vendors fcv
1673: where fcft.duns = fcv.duns)
1674: order by rowid;
1675:
1676: END IF;

Line 2159: WHERE ( (p_xml_import = 'N' AND exists ( select 1 from fv_ccr_vendors fcv

2155: ,fcft.DOMESTIC_ULT_RECORD_DATE
2156: ,fcft.ALT_CERTIFIER_FAX
2157: ,fcft.ALT_CERTIFIER_EMAIL
2158: FROM fv_ccr_file_temp fcft
2159: WHERE ( (p_xml_import = 'N' AND exists ( select 1 from fv_ccr_vendors fcv
2160: where fcft.duns = fcv.duns
2161: and fcv.ccr_status ='N'))
2162: OR p_xml_import='Y')
2163: order by rowid;

Line 2648: UPDATE fv_ccr_vendors fcv SET fcv.ccr_status ='D',

2644: l_errbuf := 'Processing data for Extract Code as 1 ';
2645: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2646:
2647: -- set the status to disabled for code as 1
2648: UPDATE fv_ccr_vendors fcv SET fcv.ccr_status ='D',
2649: fcv.enabled ='N' ,
2650: fcv.extract_code ='1' ,
2651: fcv.last_update_date = sysdate,
2652: fcv.last_import_date = nvl(l_file_date,sysdate),

Line 2661: UPDATE fv_ccr_vendors fcv SET ccr_status ='E',

2657: AND fcpg.extract_code = '1');
2658:
2659: l_errbuf := 'Processing data for Extract Code as 4 ';
2660: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2661: UPDATE fv_ccr_vendors fcv SET ccr_status ='E',
2662: enabled='N',
2663: extract_code ='4',
2664: last_update_date = sysdate,
2665: last_updated_by = fnd_global.user_id,

Line 2870: SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv

2866: BEGIN
2867: l_errbuf := 'Processing -> DUN+4 now '||l_ccr_data.plus_four;
2868: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2869:
2870: SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv
2871: WHERE fcv.duns = l_ccr_data.duns
2872: AND fcv.plus_four= l_ccr_data.plus_four ;
2873: -- if this select does not return rows ,we need to update DUNS and duns+4 information
2874: l_errbuf := 'DUNS+4 exists';

Line 2897: update fv_ccr_vendors fcv set

2893: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'find_code',l_errbuf);
2894: insert_temp_data(3,null,message_text ,null,l_ccr_data.duns||l_ccr_data.plus_four,null,null);
2895:
2896: END IF;
2897: update fv_ccr_vendors fcv set
2898: fcv.CCR_FLAG ='R' ,
2899: fcv.CCR_STATUS =l_status ,
2900: fcv.DUNS =l_ccr_data.DUNS ,
2901: fcv.PLUS_FOUR =l_ccr_data.PLUS_FOUR ,

Line 3181: l_errbuf := 'DUNS+4 does not exist in FV_CCR_VENDORS';

3177: l_errbuf := 'No data found for duns plus four ';
3178: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3179:
3180: -- This case DUns+4 doesn ot exist
3181: l_errbuf := 'DUNS+4 does not exist in FV_CCR_VENDORS';
3182: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3183:
3184: IF (p_xml_import = 'N' OR p_insert_data = 'Y') THEN -- bug 3931251
3185:

Line 3187: l_errbuf := 'Error - the DUNS+4 does not exist in FV_CCR_VENDORS';

3183:
3184: IF (p_xml_import = 'N' OR p_insert_data = 'Y') THEN -- bug 3931251
3185:
3186: IF (l_ccr_data.extract_code ='3') THEN
3187: l_errbuf := 'Error - the DUNS+4 does not exist in FV_CCR_VENDORS';
3188: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3189: ELSE
3190:
3191: --validate for the renewal date)

Line 3202: INSERT INTO FV_CCR_VENDORS (

3198:
3199: --call procedureto insert duns+4 info
3200: l_errbuf := 'Insert DUNS+4' || l_ccr_data.plus_four;
3201: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3202: INSERT INTO FV_CCR_VENDORS (
3203: CCR_ID ,
3204: ENABLED ,
3205: CCR_FLAG ,
3206: CCR_STATUS ,

Line 3480: SELECT FV_CCR_VENDORS_S.nextval ,'Y','R',l_status,

3476: -- Added for bug 6339382
3477: ORGANIZATIONAL_TYPE
3478:
3479: )
3480: SELECT FV_CCR_VENDORS_S.nextval ,'Y','R',l_status,
3481: DUNS ,
3482: PLUS_FOUR ,
3483: CAGE_CODE ,
3484: EXTRACT_CODE ,

Line 3774: SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv

3770: l_errbuf := 'Processing root DUNS -> '||l_ccr_data.duns;
3771: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3772:
3773:
3774: SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv
3775: WHERE fcv.duns = l_ccr_data.duns
3776: AND fcv.plus_four is null;
3777:
3778: -- if this select does not return rows ,we need to update DUNS and duns+4 information

Line 3804: update fv_ccr_vendors fcv set

3800: --call to update the DUNS record
3801: l_errbuf := 'Updating Root DUNS info '||l_ccr_data.duns;
3802: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3803:
3804: update fv_ccr_vendors fcv set
3805: fcv.CCR_FLAG ='R' ,
3806: fcv.CCR_STATUS =l_status ,
3807: fcv.DUNS =l_ccr_data.DUNS ,
3808: fcv.PLUS_FOUR =null ,

Line 4090: l_errbuf := 'DUNS does not exist in FV_CCR_VENDORS';

4086: l_errbuf := 'exception no data found for duns ';
4087: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4088:
4089: -- This case DUns doesn ot exist
4090: l_errbuf := 'DUNS does not exist in FV_CCR_VENDORS';
4091: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4092:
4093: IF (l_ccr_data.extract_code ='3') THEN
4094: l_errbuf := 'Error - the DUNS does not exist in FV_CCR_VENDORS';

Line 4094: l_errbuf := 'Error - the DUNS does not exist in FV_CCR_VENDORS';

4090: l_errbuf := 'DUNS does not exist in FV_CCR_VENDORS';
4091: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4092:
4093: IF (l_ccr_data.extract_code ='3') THEN
4094: l_errbuf := 'Error - the DUNS does not exist in FV_CCR_VENDORS';
4095: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4096: ELSE
4097:
4098: --validate for the renewal date)

Line 4108: INSERT INTO FV_CCR_VENDORS (

4104:
4105: --call procedureto insert duns info
4106: l_errbuf := 'Insert DUNS' || l_ccr_data.duns;
4107: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4108: INSERT INTO FV_CCR_VENDORS (
4109: CCR_ID ,
4110: ENABLED ,
4111: CCR_FLAG ,
4112: CCR_STATUS ,

Line 4386: SELECT FV_CCR_VENDORS_S.nextval ,'Y','R',l_status,

4382: -- Added for bug 6339382
4383: ORGANIZATIONAL_TYPE
4384:
4385: )
4386: SELECT FV_CCR_VENDORS_S.nextval ,'Y','R',l_status,
4387: DUNS ,
4388: PLUS_FOUR ,
4389: CAGE_CODE ,
4390: EXTRACT_CODE ,

Line 4712: update fv_ccr_vendors fcvp set fcvp.ccr_status='E' ,

4708: -- bug 3849198
4709: l_errbuf := 'Updating the duns+4 as expired/deleted based on DUNS';
4710: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4711:
4712: update fv_ccr_vendors fcvp set fcvp.ccr_status='E' ,
4713: fcvp.enabled='N',
4714: fcvp.extract_code=decode(fcvp.ccr_status,'E',fcvp.extract_code,'4')
4715: where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
4716: and fcvr.plus_four is null

Line 4715: where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns

4711:
4712: update fv_ccr_vendors fcvp set fcvp.ccr_status='E' ,
4713: fcvp.enabled='N',
4714: fcvp.extract_code=decode(fcvp.ccr_status,'E',fcvp.extract_code,'4')
4715: where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
4716: and fcvr.plus_four is null
4717: and fcvr.ccr_status='E' )
4718: and fcvp.ccr_status<>'N';
4719:

Line 4720: update fv_ccr_vendors fcvp set fcvp.ccr_status='D' ,

4716: and fcvr.plus_four is null
4717: and fcvr.ccr_status='E' )
4718: and fcvp.ccr_status<>'N';
4719:
4720: update fv_ccr_vendors fcvp set fcvp.ccr_status='D' ,
4721: fcvp.enabled='N',
4722: fcvp.extract_code=decode(fcvp.ccr_status,'D',fcvp.extract_code,'1')
4723: where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
4724: and fcvr.plus_four is null

Line 4723: where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns

4719:
4720: update fv_ccr_vendors fcvp set fcvp.ccr_status='D' ,
4721: fcvp.enabled='N',
4722: fcvp.extract_code=decode(fcvp.ccr_status,'D',fcvp.extract_code,'1')
4723: where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
4724: and fcvr.plus_four is null
4725: and fcvr.ccr_status='D' )
4726: and fcvp.ccr_status<>'N';
4727:

Line 4744: UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',

4740:
4741:
4742: IF l_verify_existence = 'Y' THEN
4743: IF l_update_type = 'A' THEN
4744: UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',
4745: CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
4746: WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
4747: WHERE fcv.duns = fcpg.duns
4748: AND NVL(fcv.plus_four, 'NO_PLUS4') =

Line 4752: UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',

4748: AND NVL(fcv.plus_four, 'NO_PLUS4') =
4749: NVL(fcpg.plus_four, 'NO_PLUS4'))
4750: AND (fcv.ccr_status ='A' OR fcv.ccr_status = 'N');
4751: ELSIF l_update_type = 'S' THEN
4752: UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',
4753: CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
4754: WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
4755: WHERE fcv.duns = fcpg.duns
4756: AND NVL(fcv.plus_four, 'NO_PLUS4') =

Line 4764: UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',

4760: AND (p_xml_import <> 'Y' OR
4761: ((NVL(fcv.plus_four, 'NO_PLUS4') = NVL(SUBSTR(p_duns, 10, 4), 'NO_PLUS4'))
4762: OR fcv.plus_four IS NULL));
4763: ELSIF l_update_type = 'N' THEN
4764: UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',
4765: CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
4766: WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
4767: WHERE fcv.duns = fcpg.duns
4768: AND NVL(fcv.plus_four, 'NO_PLUS4') =

Line 4775: update fv_ccr_vendors set extract_code=decode(l_file_type,'M','A','2')

4771: END IF;
4772: END IF;
4773:
4774: -- made this change a part of bug 3872249
4775: update fv_ccr_vendors set extract_code=decode(l_file_type,'M','A','2')
4776: where duns in ( select distinct duns from fv_ccr_process_gt)
4777: and extract_code ='N'
4778: and plus_four is null;
4779:

Line 4783: update fv_ccr_vendors fcv set fcv.enabled='Y'

4779:
4780: --bug 3931200
4781:
4782: -- made this change as part of BUG 3989083
4783: update fv_ccr_vendors fcv set fcv.enabled='Y'
4784: where fcv.enabled='N' and fcv.ccr_status='A'
4785: and not exists (select 1 from fv_ccr_orgs fco
4786: where fco.ccr_id = fcv.ccr_id);
4787: -- BUG 3989083

Line 4793: AND not exists (SELECT 1 FROM fv_ccr_vendors

4789: IF (p_xml_import='Y' and p_insert_data='Y') THEN
4790:
4791: FOR crec in (SELECT duns, plus_four from fv_ccr_process_gt fcpg
4792: WHERE fcpg.extract_code in ('1', '4')
4793: AND not exists (SELECT 1 FROM fv_ccr_vendors
4794: WHERE duns = fcpg.duns
4795: AND nvl(plus_four,'N') = nvl(fcpg.plus_four,'N')
4796: ))
4797: LOOP