[Home] [Help]
372: l_run_from_xml varchar2(1); -- added by ks for 5906546
373: i number;
374:
375: CURSOR c_ccr_data IS
376: SELECT * from fv_ccr_process_gt g
377: WHERE ( extract_code in ('A','2','3')
378: or ( l_run_from_xml = 'Y' and extract_code = '4' -- modified by ks 5906546.
379: and not exists (select 'first run'
380: from fv_ccr_orgs o
415: AND fcv.plus_four IS NULL
416: AND fcv.taxpayer_id IS NOT NULL
417: AND fcv.vendor_id is not null
418: AND fcv.ccr_status not in ('E','D')
419: AND exists (select 1 from fv_ccr_process_gt fcpg
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
691: RETURN;
692: END IF; -- end of extract type check in 'S'
693: END IF; -- end of file type validation
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';
706: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
707: END IF ; --end of xml_import <> Y
708:
709: l_run_from_xml := p_xml_import;
710: -- push data into fv_ccr_process_gt based on the update type
711: IF (l_update_type ='A') THEN
712:
713: l_errbuf :='Update type A - Inserting into second temp table';
714: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
718: --need to process all data if insert is Yes.
719: IF (p_xml_import ='Y' and p_insert_data ='Y') THEN
720: l_errbuf := 'Copying info - xml import';
721: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
722: INSERT INTO fv_ccr_process_gt ( FILE_DATE
723: ,DUNS
724: ,PLUS_FOUR
725: ,CAGE_CODE
726: ,EXTRACT_CODE
1195: ELSE
1196: l_errbuf := 'Copying info - Standalone / insert as N';
1197: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
1198:
1199: INSERT INTO fv_ccr_process_gt ( FILE_DATE
1200: ,DUNS
1201: ,PLUS_FOUR
1202: ,CAGE_CODE
1203: ,EXTRACT_CODE
1683: ELSIF (l_update_type ='N') THEN
1684: l_errbuf :='Update Type as N - inserting into second temp table';
1685: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
1686:
1687: INSERT INTO fv_ccr_process_gt ( FILE_DATE
1688: ,DUNS
1689: ,PLUS_FOUR
1690: ,CAGE_CODE
1691: ,EXTRACT_CODE
2166: ELSIF (l_update_type ='S') THEN
2167:
2168: l_errbuf := 'Update type as S - inserting into seciond temp table';
2169: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
2170: INSERT INTO fv_ccr_process_gt ( FILE_DATE
2171: ,DUNS
2172: ,PLUS_FOUR
2173: ,CAGE_CODE
2174: ,EXTRACT_CODE
2650: fcv.extract_code ='1' ,
2651: fcv.last_update_date = sysdate,
2652: fcv.last_import_date = nvl(l_file_date,sysdate),
2653: fcv.last_updated_by = fnd_global.user_id
2654: WHERE exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
2655: WHERE fcv.duns = fcpg.duns
2656: AND nvl(fcv.plus_four,-99)= nvl(fcpg.plus_four,-99)
2657: AND fcpg.extract_code = '1');
2658:
2663: extract_code ='4',
2664: last_update_date = sysdate,
2665: last_updated_by = fnd_global.user_id,
2666: last_import_date = nvl(l_file_date,sysdate)
2667: WHERE exists ( SELECT 1 FROM fv_ccr_process_gt fcpg
2668: WHERE fcv.duns = fcpg.duns
2669: AND nvl(fcv.plus_four,-99)= nvl(fcpg.plus_four,-99)
2670: AND fcpg.extract_code = '4');
2671:
2671:
2672: -- Fixed as part of BUG 3960809 for showing deleted/expired DUNS returned
2673: insert into fv_ccr_process_report(duns_info,record_type,reference1,reference2,reference3,reference4)
2674: SELECT DUNS||nvl(plus_four,''),'1',legal_bus_name,' ',' ',decode(fcpg.extract_code,'1','Deleted','4','Expired')
2675: FROM fv_ccr_process_gt fcpg
2676: WHERE fcpg.extract_code IN ('1','4');
2677:
2678:
2679: l_errbuf := 'Processing CCR Data for a,2,3';
3749: COUNTRY_OF_INC,
3750: -- Added for bug 6339382
3751: l_code(71).code
3752:
3753: FROM FV_CCR_PROCESS_GT fcpg
3754: WHERE fcpg.duns = l_ccr_data.duns
3755: AND fcpg.extract_code=l_ccr_data.extract_code
3756: AND fcpg.plus_four = l_ccr_data.plus_four;
3757:
4654: state_of_inc,
4655: COUNTRY_OF_INC,
4656: -- Added for bug 6339382
4657: l_code(71).code
4658: FROM FV_CCR_PROCESS_GT fcpg
4659: WHERE fcpg.duns = l_ccr_data.duns
4660: AND fcpg.extract_code=l_ccr_data.extract_code
4661: AND fcpg.plus_four is null ;
4662:
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') =
4749: NVL(fcpg.plus_four, 'NO_PLUS4'))
4750: AND (fcv.ccr_status ='A' OR fcv.ccr_status = 'N');
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') =
4757: NVL(fcpg.plus_four, 'NO_PLUS4'))
4758: AND (fcv.ccr_status ='A' OR fcv.ccr_status = 'N')
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') =
4769: NVL(fcpg.plus_four, 'NO_PLUS4'))
4770: AND fcv.ccr_status = 'N';
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:
4780: --bug 3931200
4787: -- BUG 3989083
4788:
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')
4891:
4892:
4893: -- purge the temporary tables
4894: delete from fv_ccr_file_temp;
4895: delete from fv_ccr_process_gt;
4896: delete from fv_ccr_process_report ;
4897: commit;
4898:
4899: exception when others then