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 7: type l_vendor_ids is table of fv_ccr_vendors.vendor_id%type index by binary_integer;

3:
4: type l_bus_codes is table of fnd_lookup_values.lookup_code%type index by binary_integer;
5:
6: --sthota
7: type l_vendor_ids is table of fv_ccr_vendors.vendor_id%type index by binary_integer;
8: vendor_ids l_vendor_ids;
9:
10: type l_duns is table of fv_ccr_vendors.duns%type index by binary_integer;
11: duns_ids l_duns;

Line 10: type l_duns is table of fv_ccr_vendors.duns%type index by binary_integer;

6: --sthota
7: type l_vendor_ids is table of fv_ccr_vendors.vendor_id%type index by binary_integer;
8: vendor_ids l_vendor_ids;
9:
10: type l_duns is table of fv_ccr_vendors.duns%type index by binary_integer;
11: duns_ids l_duns;
12:
13:
14: type lookup_info is record

Line 416: from fv_ccr_vendors fcv

412:
413:
414: CURSOR c_taxpayer is
415: select distinct fcv.taxpayer_id,fcv.vendor_id
416: from fv_ccr_vendors fcv
417: where exists (SELECT 1 FROM fv_ccr_vendors fcv_in
418: WHERE fcv_in.taxpayer_id=fcv.taxpayer_id
419: AND fcv_in.vendor_id = fcv.vendor_id
420: AND fcv_in.legal_bus_name<>fcv.legal_bus_name

Line 417: where exists (SELECT 1 FROM fv_ccr_vendors fcv_in

413:
414: CURSOR c_taxpayer is
415: select distinct fcv.taxpayer_id,fcv.vendor_id
416: from fv_ccr_vendors fcv
417: where exists (SELECT 1 FROM fv_ccr_vendors fcv_in
418: WHERE fcv_in.taxpayer_id=fcv.taxpayer_id
419: AND fcv_in.vendor_id = fcv.vendor_id
420: AND fcv_in.legal_bus_name<>fcv.legal_bus_name
421: AND fcv_in.taxpayer_id is not null

Line 434: from fv_ccr_vendors fcv

430: where fcpg.duns = fcv.duns and fcpg.plus_four IS NULL);
431:
432: CURSOR c_duns_info(p_taxpayer_id varchar2,p_vendor_id number) IS
433: select fcv.duns,fcv.plus_four ,fcv.legal_bus_name,fcv.taxpayer_id
434: from fv_ccr_vendors fcv
435: where fcv.plus_four is null
436: and fcv.taxpayer_id=p_taxpayer_id
437: and fcv.vendor_id=p_vendor_id;
438:

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

726:
727: l_errbuf := 'Push data into fv_ccr_process_gt based on the update type';
728: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
729:
730: -- reset the status in fv_ccr_vendors as 'N' for extract code
731: UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
732:
733: ELSE
734: --This program is called from xml import

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

727: l_errbuf := 'Push data into fv_ccr_process_gt based on the update type';
728: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
729:
730: -- reset the status in fv_ccr_vendors as 'N' for extract code
731: UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
732:
733: ELSE
734: --This program is called from xml import
735: -- Added the below code as Data Load will be called from xml with update_type A

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

732:
733: ELSE
734: --This program is called from xml import
735: -- Added the below code as Data Load will be called from xml with update_type A
736: UPDATE fv_ccr_vendors fcv SET fcv.extract_code ='N';
737: l_errbuf := 'The program is being called from xml import -> xml-import parameter '|| p_xml_import;
738: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
739: END IF ; --end of xml_import <> Y
740:

Line 1656: WHERE exists ( select 1 from fv_ccr_vendors fcv

1652: ,fcft.GLOBAL_PARENT_PHONE
1653: ,bank_acct_type
1654:
1655: FROM fv_ccr_file_temp fcft
1656: WHERE exists ( select 1 from fv_ccr_vendors fcv
1657: where fcft.duns = fcv.duns)
1658: order by rowid;
1659:
1660: END IF;

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

2115: ,fcft.GLOBAL_PARENT_PHONE
2116: ,bank_acct_type
2117:
2118: FROM fv_ccr_file_temp fcft
2119: WHERE ( (p_xml_import = 'N' AND exists ( select 1 from fv_ccr_vendors fcv
2120: where fcft.duns = fcv.duns
2121: and fcv.ccr_status ='N'))
2122: OR p_xml_import='Y')
2123: order by rowid;

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

2585: l_errbuf := 'Processing data for Extract Code as 1 ';
2586: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2587:
2588: -- set the status to disabled for code as 1
2589: UPDATE fv_ccr_vendors fcv SET fcv.ccr_status ='D',
2590: fcv.enabled ='N' ,
2591: fcv.extract_code ='1' ,
2592: fcv.last_update_date = sysdate,
2593: fcv.last_import_date = nvl(l_file_date,sysdate),

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

2598: AND fcpg.extract_code = '1');
2599:
2600: l_errbuf := 'Processing data for Extract Code as 4 ';
2601: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2602: UPDATE fv_ccr_vendors fcv SET ccr_status ='E',
2603: enabled='N',
2604: extract_code ='4',
2605: last_update_date = sysdate,
2606: last_updated_by = fnd_global.user_id,

Line 2808: select vendor_id into l_active_vendor_id from fv_ccr_vendors

2804: l_status :='A';
2805:
2806: --sthota
2807: begin
2808: select vendor_id into l_active_vendor_id from fv_ccr_vendors
2809: where DUNS = l_ccr_data.duns and plus_four is null and ccr_status in ('E');
2810:
2811: if l_ccr_data.renewal_date > trunc(sysdate) then
2812:

Line 2851: SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv

2847: BEGIN
2848: l_errbuf := 'Processing -> DUN+4 now '||l_ccr_data.plus_four;
2849: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2850:
2851: SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv
2852: WHERE fcv.duns = l_ccr_data.duns
2853: AND fcv.plus_four= l_ccr_data.plus_four ;
2854: -- if this select does not return rows ,we need to update DUNS and duns+4 information
2855: l_errbuf := 'DUNS+4 exists';

Line 2880: update fv_ccr_vendors fcv set

2876:
2877: END IF;
2878:
2879:
2880: update fv_ccr_vendors fcv set
2881: fcv.CCR_FLAG ='R' ,
2882: fcv.CCR_STATUS =l_status ,
2883: fcv.DUNS =l_ccr_data.DUNS ,
2884: fcv.PLUS_FOUR =l_ccr_data.PLUS_FOUR ,

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

3129: l_errbuf := 'No data found for duns plus four ';
3130: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3131:
3132: -- This case DUns+4 doesn ot exist
3133: l_errbuf := 'DUNS+4 does not exist in FV_CCR_VENDORS';
3134: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3135:
3136: IF (p_xml_import = 'N' OR p_insert_data = 'Y') THEN -- bug 3931251
3137:

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

3135:
3136: IF (p_xml_import = 'N' OR p_insert_data = 'Y') THEN -- bug 3931251
3137:
3138: IF (l_ccr_data.extract_code ='3') THEN
3139: l_errbuf := 'Error - the DUNS+4 does not exist in FV_CCR_VENDORS';
3140: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3141: ELSE
3142:
3143: --validate for the renewal date)

Line 3154: INSERT INTO FV_CCR_VENDORS (

3150:
3151: --call procedureto insert duns+4 info
3152: l_errbuf := 'Insert DUNS+4' || l_ccr_data.plus_four;
3153: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3154: INSERT INTO FV_CCR_VENDORS (
3155: CCR_ID ,
3156: ENABLED ,
3157: CCR_FLAG ,
3158: CCR_STATUS ,

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

3393: ,GLOBAL_PARENT_STATE
3394: ,GLOBAL_PARENT_PHONE
3395:
3396: )
3397: SELECT FV_CCR_VENDORS_S.nextval ,'Y','R',l_status,
3398: DUNS ,
3399: PLUS_FOUR ,
3400: CAGE_CODE ,
3401: EXTRACT_CODE ,

Line 3662: SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv

3658: l_errbuf := 'Processing root DUNS -> '||l_ccr_data.duns;
3659: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3660:
3661:
3662: SELECT legal_bus_name into l_lbe_change FROM fv_ccr_vendors fcv
3663: WHERE fcv.duns = l_ccr_data.duns
3664: AND fcv.plus_four is null;
3665:
3666: -- if this select does not return rows ,we need to update DUNS and duns+4 information

Line 3692: update fv_ccr_vendors fcv set

3688: --call to update the DUNS record
3689: l_errbuf := 'Updating Root DUNS info '||l_ccr_data.duns;
3690: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3691:
3692: update fv_ccr_vendors fcv set
3693: fcv.CCR_FLAG ='R' ,
3694: fcv.CCR_STATUS =l_status ,
3695: fcv.DUNS =l_ccr_data.DUNS ,
3696: fcv.PLUS_FOUR =null ,

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

3946: l_errbuf := 'exception no data found for duns ';
3947: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3948:
3949: -- This case DUns doesn ot exist
3950: l_errbuf := 'DUNS does not exist in FV_CCR_VENDORS';
3951: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3952:
3953: IF (l_ccr_data.extract_code ='3') THEN
3954: l_errbuf := 'Error - the DUNS does not exist in FV_CCR_VENDORS';

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

3950: l_errbuf := 'DUNS does not exist in FV_CCR_VENDORS';
3951: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3952:
3953: IF (l_ccr_data.extract_code ='3') THEN
3954: l_errbuf := 'Error - the DUNS does not exist in FV_CCR_VENDORS';
3955: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3956: ELSE
3957:
3958: --validate for the renewal date)

Line 3968: INSERT INTO FV_CCR_VENDORS (

3964:
3965: --call procedureto insert duns info
3966: l_errbuf := 'Insert DUNS' || l_ccr_data.duns;
3967: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
3968: INSERT INTO FV_CCR_VENDORS (
3969: CCR_ID ,
3970: ENABLED ,
3971: CCR_FLAG ,
3972: CCR_STATUS ,

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

4205: ,GLOBAL_PARENT_COUNTRY
4206: ,GLOBAL_PARENT_STATE
4207: ,GLOBAL_PARENT_PHONE
4208: )
4209: SELECT FV_CCR_VENDORS_S.nextval ,'Y','R',l_status,
4210: DUNS ,
4211: PLUS_FOUR ,
4212: CAGE_CODE ,
4213: EXTRACT_CODE ,

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

4516: -- bug 3849198
4517: l_errbuf := 'Updating the duns+4 as expired/deleted based on DUNS';
4518: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
4519:
4520: update fv_ccr_vendors fcvp set fcvp.ccr_status='E' ,
4521: fcvp.enabled='N',
4522: fcvp.extract_code=decode(fcvp.ccr_status,'E',fcvp.extract_code,'4')
4523: where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
4524: and fcvr.plus_four is null

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

4519:
4520: update fv_ccr_vendors fcvp set fcvp.ccr_status='E' ,
4521: fcvp.enabled='N',
4522: fcvp.extract_code=decode(fcvp.ccr_status,'E',fcvp.extract_code,'4')
4523: where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
4524: and fcvr.plus_four is null
4525: and fcvr.ccr_status='E' )
4526: and fcvp.ccr_status<>'N';
4527:

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

4524: and fcvr.plus_four is null
4525: and fcvr.ccr_status='E' )
4526: and fcvp.ccr_status<>'N';
4527:
4528: update fv_ccr_vendors fcvp set fcvp.ccr_status='D' ,
4529: fcvp.enabled='N',
4530: fcvp.extract_code=decode(fcvp.ccr_status,'D',fcvp.extract_code,'1')
4531: where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
4532: and fcvr.plus_four is null

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

4527:
4528: update fv_ccr_vendors fcvp set fcvp.ccr_status='D' ,
4529: fcvp.enabled='N',
4530: fcvp.extract_code=decode(fcvp.ccr_status,'D',fcvp.extract_code,'1')
4531: where duns in ( select duns from fv_ccr_vendors fcvr where fcvr.duns= fcvp.duns
4532: and fcvr.plus_four is null
4533: and fcvr.ccr_status='D' )
4534: and fcvp.ccr_status<>'N';
4535:

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

4548:
4549:
4550: IF l_verify_existence = 'Y' THEN
4551: IF l_update_type = 'A' THEN
4552: UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',
4553: CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
4554: WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
4555: WHERE fcv.duns = fcpg.duns
4556: AND NVL(fcv.plus_four, 'NO_PLUS4') =

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

4557: NVL(fcpg.plus_four, 'NO_PLUS4'))
4558: AND (fcv.ccr_status ='A' OR fcv.ccr_status = 'N');
4559: ELSIF l_update_type = 'S' THEN
4560: --sthota need to check
4561: UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U', 'A', 'A',
4562: CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
4563: WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
4564: WHERE fcv.duns = fcpg.duns
4565: AND NVL(fcv.plus_four, 'NO_PLUS4') =

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

4569: AND (p_xml_import <> 'Y' OR
4570: ((NVL(fcv.plus_four, 'NO_PLUS4') = NVL(SUBSTR(p_duns, 10, 4), 'NO_PLUS4'))
4571: OR fcv.plus_four IS NULL));
4572: ELSIF l_update_type = 'N' THEN
4573: UPDATE fv_ccr_vendors fcv SET ccr_status = DECODE(ccr_status, 'N', 'U',
4574: CASE WHEN renewal_date < trunc(sysdate) THEN 'E' ELSE 'D' END)
4575: WHERE not exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
4576: WHERE fcv.duns = fcpg.duns
4577: AND NVL(fcv.plus_four, 'NO_PLUS4') =

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

4580: END IF;
4581: END IF;
4582:
4583: -- made this change a part of bug 3872249
4584: update fv_ccr_vendors set extract_code=decode(l_file_type,'M','A','2')
4585: where duns in ( select distinct duns from fv_ccr_process_gt)
4586: and extract_code ='N'
4587: and plus_four is null;
4588:

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

4588:
4589: --bug 3931200
4590:
4591: -- made this change as part of BUG 3989083
4592: update fv_ccr_vendors fcv set fcv.enabled='Y'
4593: where fcv.enabled='N' and fcv.ccr_status='A'
4594: and not exists (select 1 from fv_ccr_orgs fco
4595: where fco.ccr_id = fcv.ccr_id);
4596: -- BUG 3989083

Line 4602: AND not exists (SELECT 1 FROM fv_ccr_vendors

4598: IF (p_xml_import='Y' and p_insert_data='Y') THEN
4599:
4600: FOR crec in (SELECT duns, plus_four from fv_ccr_process_gt fcpg
4601: WHERE fcpg.extract_code in ('1', '4')
4602: AND not exists (SELECT 1 FROM fv_ccr_vendors
4603: WHERE duns = fcpg.duns
4604: AND nvl(plus_four,'N') = nvl(fcpg.plus_four,'N')
4605: ))
4606: LOOP