[Home] [Help]
382: l_run_from_xml varchar2(1); -- added by ks for 5906546
383: i number;
384:
385: CURSOR c_ccr_data IS
386: SELECT * from fv_ccr_process_gt g
387: WHERE ( extract_code in ('A','2','3')
388: or ( l_run_from_xml = 'Y' and extract_code = '4' -- modified by ks 5906546.
389: and not exists (select 'first run'
390: from fv_ccr_orgs o
425: AND fcv.plus_four IS NULL
426: AND fcv.taxpayer_id IS NOT NULL
427: AND fcv.vendor_id is not null
428: AND fcv.ccr_status not in ('E','D')
429: AND exists (select 1 from fv_ccr_process_gt fcpg
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
723: l_errbuf := 'Calling BPN Load package to load data into fv_ccr_file_temp';
724: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
725: FV_CCR_BPN_LOAD_PKG.MAIN();
726:
727: l_errbuf := 'Push data into fv_ccr_process_gt based on the update type';
728: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
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';
738: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
739: END IF ; --end of xml_import <> Y
740:
741: l_run_from_xml := p_xml_import;
742: -- push data into fv_ccr_process_gt based on the update type
743: IF (l_update_type ='A') THEN
744:
745: l_errbuf :='Update type A - Inserting into second temp table';
746: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
750: --need to process all data if insert is Yes.
751: IF (p_xml_import ='Y' and p_insert_data ='Y') THEN
752: l_errbuf := 'Copying info - xml import';
753: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
754: INSERT INTO fv_ccr_process_gt ( FILE_DATE
755: ,DUNS
756: ,PLUS_FOUR
757: ,CAGE_CODE
758: ,EXTRACT_CODE
1203: ELSE
1204: l_errbuf := 'Copying info - Standalone / insert as N';
1205: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
1206:
1207: INSERT INTO fv_ccr_process_gt ( FILE_DATE
1208: ,DUNS
1209: ,PLUS_FOUR
1210: ,CAGE_CODE
1211: ,EXTRACT_CODE
1667: ELSIF (l_update_type ='N') THEN
1668: l_errbuf :='Update Type as N - inserting into second temp table';
1669: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
1670:
1671: INSERT INTO fv_ccr_process_gt ( FILE_DATE
1672: ,DUNS
1673: ,PLUS_FOUR
1674: ,CAGE_CODE
1675: ,EXTRACT_CODE
2126: ELSIF (l_update_type ='S') THEN
2127:
2128: l_errbuf := 'Update type as S - inserting into seciond temp table';
2129: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2130: INSERT INTO fv_ccr_process_gt ( FILE_DATE
2131: ,DUNS
2132: ,PLUS_FOUR
2133: ,CAGE_CODE
2134: ,EXTRACT_CODE
2591: fcv.extract_code ='1' ,
2592: fcv.last_update_date = sysdate,
2593: fcv.last_import_date = nvl(l_file_date,sysdate),
2594: fcv.last_updated_by = fnd_global.user_id
2595: WHERE exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
2596: WHERE fcv.duns = fcpg.duns
2597: AND nvl(fcv.plus_four,-99)= nvl(fcpg.plus_four,-99)
2598: AND fcpg.extract_code = '1');
2599:
2604: extract_code ='4',
2605: last_update_date = sysdate,
2606: last_updated_by = fnd_global.user_id,
2607: last_import_date = nvl(l_file_date,sysdate)
2608: WHERE exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
2609: WHERE fcv.duns = fcpg.duns
2610: AND nvl(fcv.plus_four,-99)= nvl(fcpg.plus_four,-99)
2611: AND fcpg.extract_code = '4');
2612:
2612:
2613: -- Fixed as part of BUG 3960809 for showing deleted/expired DUNS returned
2614: insert into fv_ccr_process_report(duns_info,record_type,reference1,reference2,reference3,reference4)
2615: SELECT DUNS||nvl(plus_four,''),'1',legal_bus_name,' ',' ',decode(fcpg.extract_code,'1','Deleted','4','Expired')
2616: FROM fv_ccr_process_gt fcpg
2617: WHERE fcpg.extract_code IN ('1','4');
2618:
2619:
2620: l_errbuf := 'Processing CCR Data for a,2,3';
3637: ,GLOBAL_PARENT_COUNTRY
3638: ,GLOBAL_PARENT_STATE
3639: ,GLOBAL_PARENT_PHONE
3640:
3641: FROM FV_CCR_PROCESS_GT fcpg
3642: WHERE fcpg.duns = l_ccr_data.duns
3643: AND fcpg.extract_code=l_ccr_data.extract_code
3644: AND fcpg.plus_four = l_ccr_data.plus_four;
3645:
4448: ,GLOBAL_PARENT_POSTAL_CODE
4449: ,GLOBAL_PARENT_COUNTRY
4450: ,GLOBAL_PARENT_STATE
4451: ,GLOBAL_PARENT_PHONE
4452: FROM FV_CCR_PROCESS_GT fcpg
4453: WHERE fcpg.duns = l_ccr_data.duns
4454: AND fcpg.extract_code=l_ccr_data.extract_code
4455: AND fcpg.plus_four is null ;
4456:
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') =
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') =
4566: NVL(fcpg.plus_four, 'NO_PLUS4'))
4567: AND (fcv.ccr_status ='A' OR fcv.ccr_status = 'N')
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') =
4578: NVL(fcpg.plus_four, 'NO_PLUS4'))
4579: AND fcv.ccr_status = 'N';
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:
4589: --bug 3931200
4596: -- BUG 3989083
4597:
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')
4721:
4722:
4723: -- purge the temporary tables
4724: delete from fv_ccr_file_temp;
4725: delete from fv_ccr_process_gt;
4726: delete from fv_ccr_process_report ;
4727: commit;
4728:
4729: exception