--创建表空间
CREATE SMALLFILE TABLESPACE NNC DATAFILE 'E:\app\Administrator\oradata\orcl\
--创建表空间
CREATE SMALLFILE TABLESPACE NNC DATAFILE 'E:\app\Administrator\oradata\orcl\NNC.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
--在表空间上创建用户
create user testuser identified by testuser default tablespace NNC temporary tablespace temp;
--给用户授权
grant connect,resource to testuser;
-- 查看是否支持高级复制的权限
SELECT * FROM v$option WHERE parameter LIKE 'Advanced replication%';
--查看数据库 实例名称 也就是服务 ，后面要基于服务创建dblink
SELECT * FROM GLOBAL_NAME;
--连接远程数据库
sqlplus testuser/testuser@127.0.0.1:1521/orcl
--命令行下查看
show PARAMETER global_name; 如果为true 则dblink要同实例名
--sqlplus 用户名/密码@ip地址[:端口]/service_name [as sysdba]
--创建dblink
CREATE DATABASE LINK testlink CONNECT TO zjjz IDENTIFIED BY zjjz USING 'orcl';
--查询是否创建成功
SELECT * FROM dual@testlink;
--测试dblink连接 抓取数据
select * from yls_inout_plan_c@testlink;
--数据拷贝
delete from us_ac
insert into us_ac(names)
select a.source_bill from yls_inout_plan_c@testlink a;
--创建存储过程
create or replace procedure testp as
begin
insert into us_ac(names)
select pk_inout_plan from yls_inout_plan_c@testlink;
--跟新数据库 要记得提交
commit;
end ;
---创建定时任务
declare
job1 number;
begin
--job1 系统自动分配的定时任务编号
--testp 调用的存储过程
--sysdate 下一次执行的时间
--sysdate+1/1440 时间间隔
dbms_job.submit(job1,'TESTP;',sysdate,'sysdate+1/(1440*60)');
commit;
end;
--查看定时任务编号
select * from user_jobs
--启动定时任务
begin
dbms_job.run(84);
commit;
end;
--暂停定时任务
begin
dbms_job.broken(84,true);
commit;
end;
--删除定时任务
begin
dbms_job.remove(84);
commit;
end;
--修改定时任务
修改：要执行的操作:job:dbms_job.what(jobno,what);
修改：下次执行时间：dbms_job.next_date(job,next_date);
修改：间隔时间：dbms_job.interval(job,interval);
(暂)停止定时执行：job:dbms.broken(job,broken,nextdate);
启动定时执行：job:dbms_job.run(jobno);
sys.dbms_job.remove(job1);
NNC.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
--在表空间上创建用户
create user testuser identified by testuser default tablespace NNC temporary tablespace temp;
--给用户授权
grant connect,resource to testuser;
-- 查看是否支持高级复制的权限
SELECT * FROM v$option WHERE parameter LIKE 'Advanced replication%';
--查看数据库 实例名称 也就是服务 ，后面要基于服务创建dblink
SELECT * FROM GLOBAL_NAME;
--连接远程数据库
sqlplus testuser/testuser@127.0.0.1:1521/orcl
--命令行下查看
show PARAMETER global_name; 如果为true 则dblink要同实例名
--sqlplus 用户名/密码@ip地址[:端口]/service_name [as sysdba]
--创建dblink
CREATE DATABASE LINK testlink CONNECT TO zjjz IDENTIFIED BY zjjz USING 'orcl';
--查询是否创建成功
SELECT * FROM dual@testlink;
--测试dblink连接 抓取数据
select * from yls_inout_plan_c@testlink;
--数据拷贝
delete from us_ac
insert into us_ac(names)
select a.source_bill from yls_inout_plan_c@testlink a;
--创建存储过程
create or replace procedure testp as
begin
insert into us_ac(names)
select pk_inout_plan from yls_inout_plan_c@testlink;
--跟新数据库 要记得提交
commit;
end ;
---创建定时任务
declare
job1 number;
begin
--job1 系统自动分配的定时任务编号
--testp 调用的存储过程
--sysdate 下一次执行的时间
--sysdate+1/1440 时间间隔
dbms_job.submit(job1,'TESTP;',sysdate,'sysdate+1/(1440*60)');
commit;
end;
--查看定时任务编号
select * from user_jobs
--启动定时任务
begin
dbms_job.run(84);
commit;
end;
--暂停定时任务
begin
dbms_job.broken(84,true);
commit;
end;
--删除定时任务
begin
dbms_job.remove(84);
commit;
end;
--修改定时任务
修改：要执行的操作:job:dbms_job.what(jobno,what);
修改：下次执行时间：dbms_job.next_date(job,next_date);
修改：间隔时间：dbms_job.interval(job,interval);
(暂)停止定时执行：job:dbms.broken(job,broken,nextdate);
启动定时执行：job:dbms_job.run(jobno);
sys.dbms_job.remove(job1);
--例子--
--创建出租方信息表--
--出租人
CREATE TABLE "YC_YLS_LESSOR"
( "YC_CUSTOMER_NAME" VARCHAR2(300 BYTE),
"YC_CUSTOMER_CODE" VARCHAR2(40 BYTE),
"YC_LEGAL_REP" VARCHAR2(256 BYTE),
"YC_NATIONAL_TAX" VARCHAR2(50 BYTE),
"YC_INFORM_ADDRESS" VARCHAR2(256 BYTE),
"YC_INFORM_ADDRESS_ZIP" VARCHAR2(256 BYTE),
"YC_MEMO" VARCHAR2(1024 BYTE)
);
select * from YC_YLS_LESSOR;
delete from YC_YLS_LESSOR;
insert into YC_YLS_LESSOR(yc_customer_name,yc_customer_code,yc_legal_rep,yc_national_tax,yc_inform_address,yc_inform_address_zip,yc_memo)
select distinct
customer_name yc_customer_name,
customer_code yc_customer_code,
legal_rep yc_legal_rep,
national_tax yc_national_tax,
inform_address yc_inform_address,
inform_address_zip yc_inform_address_zip,
memo yc_memo
from YLS_CONTRACT_C@testlink co
join yls_project_orginfo_c@testlink pr
on co.pk_customer_lessor=pr.pk_customer
join V_CUSTOMER_NCCORP@testlink vc
on co.pk_customer_lessor=vc.pk_customer;
commit;
--承租人信息表
create table yls_lessee(
yc_CONT_CODE VARCHAR2(256 BYTE),
yc_CONT_NAME VARCHAR2(256 BYTE),
yc_CUSTOMER_PROPERTY VARCHAR2(256),
yc_CUSTOMER_NAME VARCHAR2(256),
yc_CUSTOMER_CODE VARCHAR2(32),
yc_PROJECT_NAME VARCHAR2(256),
yc_PROJECT_CODE VARCHAR2(32),
yc_LEGAL_REPRESENTATIVE VARCHAR2(256),
yc_INFORM_ADDRESS VARCHAR2(256),
yc_INFORM_ADDRESS_ZIP CHAR(6)
);
select * from yls_lessee;
--承租人信息数据拷贝
delete from YLS_LESSEE;
insert into YLS_LESSEE
(yc_CONT_CODE,yc_CONT_NAME,
yc_CUSTOMER_PROPERTY,
yc_CUSTOMER_NAME,
yc_CUSTOMER_CODE,
yc_PROJECT_NAME,
yc_PROJECT_CODE,
yc_LEGAL_REPRESENTATIVE,
yc_INFORM_ADDRESS,
yc_INFORM_ADDRESS_ZIP)
select distinct
co.CONT_CODE,
co.CONT_NAME,
vc.CUSTOMER_PROPERTY,
yc.CUSTOMER_NAME,
yc.CUSTOMER_CODE,
yp.PROJECT_NAME,
yp.PROJECT_CODE,
vc.LEGAL_REPRESENTATIVE,
vc.INFORM_ADDRESS,
vc.INFORM_ADDRESS_ZIP
from YLS_CONTRACT_C@testlink co
join yls_customer@testlink yc
on yc.pk_customer=co.pk_customer_lessee
join v_customer@testlink vc
on vc.customer_code = yc.customer_code
join yls_project_info@testlink yp
on co.pk_project=yp.pk_project_info;
commit;
select * from yls_cont_provider_c@testlink;
--供应商信息表
create table yls_conprovider(
yc_CONTRACT_SIGNED CHAR(10),
yc_APPOINT_CONT_DATE CHAR(10),
yc_CONTRACT_AMOUNT NUMBER(18,2),
yc_LESSEE_ASSIGNMENT NUMBER(18,2),
yc_FACILITY_AMOUNT NUMBER(18,2),
yc_CUSTOMER_NAME VARCHAR2(256),
yc_CUSTOMER_CODE VARCHAR2(32),
yc_LEGAL_REP VARCHAR2(256),
yc_INFORM_ADDRESS VARCHAR2(256),
yc_INFORM_ADDRESS_ZIP CHAR(6),
yc_NATIONAL_TAX VARCHAR2(32),
yc_INVOICE_WAY NUMBER(38),
yc_TAXES_MAIN NUMBER(38),
yc_INVOICE_NATURE NUMBER(38),
yc_TAX_RATE NUMBER(38),
yc_CONT_TYPE NUMBER(38),
yc_CONT_STATUS NUMBER(38),
yc_checkNAME VARCHAR2(300),
yc_currentNAME VARCHAR2(300),
yc_MEMO VARCHAR2(1024),
yc_prov_ACCOUNT_NAME VARCHAR2(300),
yc_prov_ACCOUNT_NO VARCHAR2(40),
yc_prov_ACCOUNT_BANK VARCHAR2(300),
yc_prov_BANK_NO VARCHAR2(80),
yc_lessee_ACCOUNT_NAME VARCHAR2(300),
yc_lessee_ACCOUNT_NO VARCHAR2(40),
yc_lessee_ACCOUNT_BANK VARCHAR2(300),
yc_lessee_BANK_NO VARCHAR2(80),
yc_lessor_ACCOUNT_NAME VARCHAR2(300),
yc_lessor_ACCOUNT_NO VARCHAR2(40),
yc_lessor_ACCOUNT_BANK VARCHAR2(300),
yc_lessor_BANK_NO VARCHAR2(80)
);
--供应商数据拷贝
delete from YLS_CONPROVIDER;
insert into YLS_CONPROVIDER(yc_CONTRACT_SIGNED,
yc_APPOINT_CONT_DATE,
yc_CONTRACT_AMOUNT,
yc_LESSEE_ASSIGNMENT,
yc_FACILITY_AMOUNT,
yc_CUSTOMER_NAME,
yc_CUSTOMER_CODE,
yc_LEGAL_REP,
yc_INFORM_ADDRESS,
yc_INFORM_ADDRESS_ZIP,
yc_NATIONAL_TAX,
yc_INVOICE_WAY,
yc_TAXES_MAIN,
yc_INVOICE_NATURE,
yc_TAX_RATE,
yc_CONT_TYPE,
yc_CONT_STATUS,
yc_checkNAME,
yc_currentNAME,
yc_MEMO,
yc_prov_ACCOUNT_NAME,
yc_prov_ACCOUNT_NO,
yc_prov_ACCOUNT_BANK,
yc_prov_BANK_NO,
yc_lessee_ACCOUNT_NAME,
yc_lessee_ACCOUNT_NO,
yc_lessee_ACCOUNT_BANK,
yc_lessee_BANK_NO,
yc_lessor_ACCOUNT_NAME,
yc_lessor_ACCOUNT_NO,
yc_lessor_ACCOUNT_BANK,
yc_lessor_BANK_NO)
select distinct
CONTRACT_SIGNED,
APPOINT_CONT_DATE,
CONTRACT_AMOUNT,
LESSEE_ASSIGNMENT,
FACILITY_AMOUNT,
vc.CUSTOMER_NAME,
vc.CUSTOMER_CODE,
vc.LEGAL_REP,
vc.INFORM_ADDRESS,
vc.INFORM_ADDRESS_ZIP,
vc.NATIONAL_TAX,
INVOICE_WAY,
TAXES_MAIN,
INVOICE_NATURE,
TAX_RATE,
CONT_TYPE,
CONT_STATUS,
os.NAME,
bc.NAME,
MEMO,
pa.ACCOUNT_NAME,
pa.ACCOUNT_NO,
pa.ACCOUNT_BANK,
pa.BANK_NO,
le.ACCOUNT_NAME,
le.ACCOUNT_NO,
le.ACCOUNT_BANK,
le.BANK_NO,
re.ACCOUNT_NAME,
re.ACCOUNT_NO,
re.ACCOUNT_BANK,
re.BANK_NO
from yls_cont_provider_c@testlink ycp
join yls_customer@testlink yc on ycp.pk_customer_sales=yc.pk_customer
join v_customer@testlink vc on yc.customer_code=vc.customer_code
join ORG_ACCOUNTINGBOOK@testlink os on ycp.pk_glorgbook=os.PK_ACCOUNTINGBOOK
join bd_currtype@testlink bc on ycp.pk_currency = bc.pk_currtype
join v_bank_account@testlink pa on ycp.pk_prov_account=pa.pk_cust_bank_account
join v_bank_account@testlink le on ycp.pk_lessee_account=le.pk_cust_bank_account
join v_bank_account@testlink re on ycp.pk_rent_account=re.pk_cust_bank_account;
commit;
--实际收支计划表
create table yls_realInOut(
yc_event_name VARCHAR2(256),
yc_CONT_NAME VARCHAR2(256 BYTE),
yc_CONT_CODE VARCHAR2(256 BYTE),
yc_direction NUMBER(38),
yc_lease_time VARCHAR2(128),
yc_plan_date CHAR(10),
yc_inte_date CHAR(10),
yc_trade_date CHAR(10),
yc_gather_cash NUMBER(18,2),
yc_gather_cash_tax NUMBER(18,2),
yc_gather_corpus NUMBER(18,2),
yc_gather_corpus_tax NUMBER(18,2),
yc_gather_interest NUMBER(18,2),
yc_gather_interest_tax NUMBER(18,2),
yc_lease_cash NUMBER(18,2),
yc_lease_corpus NUMBER(18,2),
yc_lease_interest NUMBER(18,2),
yc_lease_cash_fin NUMBER(18,2),
yc_lease_corpus_fin NUMBER(18,2),
yc_lease_interest_fin NUMBER(18,2)
);
--收支计划实际表数据拷贝
select * from yls_realInOut;
insert into yls_realInOut(
yc_event_name,
yc_CONT_NAME,
yc_CONT_CODE,
yc_direction,
yc_lease_time,
yc_plan_date,
yc_inte_date,
yc_trade_date,
yc_gather_cash,
yc_gather_cash_tax,
yc_gather_corpus,
yc_gather_corpus_tax,
yc_gather_interest,
yc_gather_interest_tax,
yc_lease_cash,
yc_lease_corpus,
yc_lease_interest,
yc_lease_cash_fin,
yc_lease_corpus_fin,
yc_lease_interest_fin)
select
et.event_name,
ylsc.CONT_NAME,
ylsc.CONT_CODE,
ip.direction,
ip.lease_time,
ip.plan_date,
ip.inte_date,
trade_date,
mt.gather_cash,
mt.gather_cash_tax,
mt.gather_corpus,
mt.gather_corpus_tax,
mt.gather_interest,
mt.gather_interest_tax,
ip.lease_cash - mt.gather_cash bl_lease_cash,
ip.lease_corpus - mt.gather_corpus bl_lease_corpus,
ip.lease_interest - mt.gather_interest bl_lease_interest,
ip.lease_cash_fin - mt.gather_cash bl_lease_cash_fin,
ip.lease_corpus_fin - mt.gather_corpus bl_lease_corpus_fin,
ip.lease_interest_fin - mt.gather_interest bl_lease_interest_fin
from (select sum(nvl(gather_cash,0.00)) gather_cash ,
sum(nvl(gather_cash_tax,0.00)) gather_cash_tax ,
sum(nvl(gather_corpus,0.00)) gather_corpus ,
sum(nvl(gather_corpus_tax,0.00)) gather_corpus_tax ,
sum(nvl(gather_interest,0.00)) gather_interest ,
sum(nvl(gather_interest_tax,0.00)) gather_interest_tax ,
max(gab.trade_date) trade_date ,
pk_inout_plan
/* pk_gather_account_b*/
from yls_gather_account_b@testlink gab
left join yls_gather_audit@testlink ga
on gab.pk_gather_audit = ga.pk_gather_audit
where ga.billstatus = 9 GROUP BY gab.pk_inout_plan
union all
select lp.real_pay_cash gather_cash,
0 gather_cash_tax,
0 gather_corpus,
0 gather_corpus_tax,
0 gather_interest,
0 gather_interest_tax,
lp.real_pay_date as trade_date,
pk_inout_plan
/* '' pk_gather_account_b*/
from yls_loan_plan@testlink lp
left join yls_loan_deal@testlink ld
on lp.pk_loan_deal = ld.pk_loan_deal
where lp.if_cancel = 1
and lp.if_approve_cancel = 1) mt
right join yls_inout_plan_c@testlink ip
on mt.pk_inout_plan = ip.pk_inout_plan
left join yls_event_type@testlink et
on et.pk_event_type = ip.trans_type
left join yls_project_account_c@testlink acct
on ip.lease_time = acct.in_out_batch
and ip.trans_type = acct.event_type
and acct.pk_lease_calculator = ip.source_bill
left join yls_contract@testlink ylsc
on ylsc.pk_contract=ip.pk_contract
where et.event_code != '10103'
and mt.pk_inout_plan is not null;
--收支计划表
create table yls_inout(
yc_pk_customer CHAR(20),
yc_CONT_NAME VARCHAR2(256 BYTE),
yc_CONT_CODE VARCHAR2(256 BYTE),
yc_lease_time VARCHAR2(128),
yc_cal_date CHAR(10),
yc_plan_date CHAR(10),
yc_inte_date CHAR(10),
yc_trans_type CHAR(20),
yc_direction NUMBER(38),
yc_lease_cash NUMBER(18,2),
yc_lease_cash_tax NUMBER(18,2),
yc_lease_balance NUMBER(18,2),
yc_fact_cash NUMBER(18,2),
yc_lease_interest NUMBER(18,2),
yc_lease_interest_tax NUMBER(18,2),
yc_lease_corpus NUMBER(18,2),
yc_lease_corpus_tax NUMBER(18,2),
yc_corpus_balance NUMBER(18,2),
yc_srvfee_share NUMBER(18,2),
yc_rent_float_ratio NUMBER(10,6),
yc_lease_cash_in NUMBER(18,2),
yc_no_tax_interest NUMBER(18,2),
yc_no_tax_srvfee NUMBER(18,2),
yc_no_tax_otherout NUMBER(18,2),
yc_no_tax_otherin NUMBER(18,2),
yc_sum_tax NUMBER(18,2),
yc_lease_cash_fin NUMBER(18,2),
yc_lease_cash_tax_fin NUMBER(18,2),
yc_lease_interest_fin NUMBER(18,2),
yc_lease_interest_tax_fin NUMBER(18,2),
yc_lease_corpus_fin NUMBER(18,2),
yc_lease_corpus_tax_fin NUMBER(18,2),
yc_corpus_balance_fin NUMBER(18,2),
yc_rate_up NUMBER(10,6),
yc_ticket_freeze_corpus NUMBER(18,2),
yc_ticket_fact_corpus NUMBER(18,2),
yc_ticket_freeze_interest NUMBER(18,2),
yc_ticket_fact_interest NUMBER(18,2),
yc_f_overdue_cash NUMBER(18,2),
yc_f_advance_against_cash NUMBER(18,2),
yc_f_deposit_against_cash NUMBER(18,2),
yc_interest_affirm_cash NUMBER(18,2),
yc_del_penalty_cash NUMBER(18,2),
yc_repayment_times NUMBER(38),
yc_repayment_ratio NUMBER(10,6),
yc_freeze_cash NUMBER(18,2),
yc_trade_discount NUMBER(18,2),
yc_asset_status NUMBER(38)
);
--收支计划表数据拷贝
delete from yls_inout;
insert into yls_inout(
yc_pk_customer,
yc_CONT_NAME,
yc_CONT_CODE,
yc_lease_time,
yc_cal_date,
yc_plan_date,
yc_inte_date,
yc_trans_type,
yc_direction,
yc_lease_cash,
yc_lease_cash_tax,
yc_lease_balance,
yc_fact_cash,
yc_lease_interest,
yc_lease_interest_tax,
yc_lease_corpus,
yc_lease_corpus_tax,
yc_corpus_balance,
yc_srvfee_share,
yc_rent_float_ratio,
yc_lease_cash_in,
yc_no_tax_interest,
yc_no_tax_srvfee,
yc_no_tax_otherout,
yc_no_tax_otherin,
yc_sum_tax,
yc_lease_cash_fin,
yc_lease_cash_tax_fin,
yc_lease_interest_fin,
yc_lease_interest_tax_fin,
yc_lease_corpus_fin,
yc_lease_corpus_tax_fin,
yc_corpus_balance_fin,
yc_rate_up,
yc_ticket_freeze_corpus,
yc_ticket_fact_corpus,
yc_ticket_freeze_interest,
yc_ticket_fact_interest,
yc_f_overdue_cash,
yc_f_advance_against_cash,
yc_f_deposit_against_cash,
yc_interest_affirm_cash,
yc_del_penalty_cash,
yc_repayment_times,
yc_repayment_ratio,
yc_freeze_cash,
yc_trade_discount,
yc_asset_status
)
select distinct
pk_customer,
CONT_NAME,
CONT_CODE,
lease_time,
cal_date,
plan_date,
inte_date,
trans_type,
direction,
lease_cash,
lease_cash_tax,
lease_balance,
fact_cash,
lease_interest,
lease_interest_tax,
lease_corpus,
lease_corpus_tax,
corpus_balance,
srvfee_share,
rent_float_ratio,
lease_cash_in,
no_tax_interest,
no_tax_srvfee,
no_tax_otherout,
no_tax_otherin,
sum_tax,
lease_cash_fin,
lease_cash_tax_fin,
lease_interest_fin,
lease_interest_tax_fin,
lease_corpus_fin,
lease_corpus_tax_fin,
corpus_balance_fin,
rate_up,
ticket_freeze_corpus,
ticket_fact_corpus,
ticket_freeze_interest,
ticket_fact_interest,
f_overdue_cash,
f_advance_against_cash,
f_deposit_against_cash,
interest_affirm_cash,
del_penalty_cash,
repayment_times,
repayment_ratio,
freeze_cash,
trade_discount,
asset_status
from yls_inout_plan_c@testlink yip join yls_contract_c@testlink yc
on yip.pk_contract=yc.pk_contract;
--租赁物清单表
create table yls_rent_thing(
yc_customer_name VARCHAR2(256),
yc_customer_code VARCHAR2(32),
yc_PROJECT_NAME VARCHAR2(256),
yc_PROJECT_CODE VARCHAR2(32),
yc_CONT_NAME VARCHAR2(256),
yc_CONT_CODE VARCHAR2(256),
yc_thing_type CHAR(20),
yc_record_code VARCHAR2(32),
yc_thing_code VARCHAR2(32),
yc_thing_name VARCHAR2(512),
yc_brand VARCHAR2(256),
yc_specification VARCHAR2(256),
yc_model VARCHAR2(256),
yc_unit NUMBER(38),
yc_thing_number NUMBER(18,2),
yc_unit_cost NUMBER(18,2),
yc_total_cost NUMBER(18,2),
yc_buy_time CHAR(10),
yc_original_value NUMBER(18,2),
yc_net_worth NUMBER(18,2),
yc_valuation NUMBER(18,2),
yc_tax_rate NUMBER(38),
yc_value_notax NUMBER(18,2),
yc_value_tax NUMBER(18,2),
yc_delivery_date CHAR(10),
yc_delivery_address VARCHAR2(256),
yc_use_address VARCHAR2(256),
yc_invoice_number VARCHAR2(32),
yc_invoice_util VARCHAR2(256),
yc_is_charge NUMBER(38),
yc_is_rent_core NUMBER(38),
yc_is_removable NUMBER(38),
yc_realization_capacity NUMBER(38),
yc_use_months NUMBER(38),
yc_used_years NUMBER(38),
yc_touse_months NUMBER(38),
yc_acceptor VARCHAR2(256),
yc_invoice_nature NUMBER(38),
yc_purchase_time CHAR(10),
yc_is_reply NUMBER(38),
yc_is_ref_main NUMBER(38),
yc_management_difficulty NUMBER(38),
yc_if_installation_gps NUMBER(38),
yc_if_stick_label NUMBER(38),
yc_memo VARCHAR2(1024),
yc_back_rate NUMBER(10,6)
)
--租赁物清单表数据拷贝
delete from yls_rent_thing
insert into yls_rent_thing(
yc_customer_name,
yc_customer_code,
yc_PROJECT_NAME,
yc_PROJECT_CODE,
yc_CONT_NAME,
yc_CONT_CODE,
yc_thing_type,
yc_record_code,
yc_thing_code,
yc_thing_name,
yc_brand,
yc_specification,
yc_model,
yc_unit,
yc_thing_number,
yc_unit_cost,
yc_total_cost,
yc_buy_time,
yc_original_value,
yc_net_worth,
yc_valuation,
yc_tax_rate,
yc_value_notax,
yc_value_tax,
yc_delivery_date,
yc_delivery_address,
yc_use_address,
yc_invoice_number,
yc_invoice_util,
yc_is_charge,
yc_is_rent_core,
yc_is_removable,
yc_realization_capacity,
yc_use_months,
yc_used_years,
yc_touse_months,
yc_acceptor,
yc_invoice_nature,
yc_purchase_time,
yc_is_reply,
yc_is_ref_main,
yc_management_difficulty,
yc_if_installation_gps,
yc_if_stick_label,
yc_memo,
yc_back_rate
)
select distinct
yc.customer_name,
yc.customer_code,
ypi.PROJECT_NAME,
ypi.PROJECT_CODE,
yct.CONT_NAME,
yct.CONT_CODE,
yp.param_name,
yprh.record_code,
yprh.thing_code,
yprh.thing_name,
yprh.brand,
yprh.specification,
yprh.model,
yprh.unit,
yprh.thing_number,
yprh.unit_cost,
yprh.total_cost,
yprh.buy_time,
yprh.original_value,
yprh.net_worth,
yprh.valuation,
yprh.tax_rate,
yprh.value_notax,
yprh.value_tax,
yprh.delivery_date,
yprh.delivery_address,
yprh.use_address,
yprh.invoice_number,
yprh.invoice_util,
yprh.is_charge,
yprh.is_rent_core,
yprh.is_removable,
yprh.realization_capacity,
yprh.use_months,
yprh.used_years,
yprh.touse_months,
yprh.acceptor,
yprh.invoice_nature,
yprh.purchase_time,
yprh.is_reply,
yprh.is_ref_main,
yprh.management_difficulty,
yprh.if_installation_gps,
yprh.if_stick_label,
yprh.memo,
yprh.back_rate
from yls_project_rent_thing_c@testlink yprh
join yls_customer@testlink yc on yprh.pk_consumer=yc.pk_customer
join yls_contract_c@testlink yct on yprh.source_bill=yct.pk_contract
join yls_project_info@testlink ypi on yct.pk_project=pk_project_info
join yls_parameter@testlink yp on yprh.thing_type=yp.pk_parameter;
---租金计划表
create table yls_rent(
yc_CONT_NAME VARCHAR2(256),
yc_CONT_CODE VARCHAR2(256),
yc_lease_time VARCHAR2(128),
yc_plan_date CHAR(10),
yc_inte_date CHAR(10),
yc_direction NUMBER(38),
yc_trans_type CHAR(20),
yc_lease_cash NUMBER(18,2),
yc_lease_corpus NUMBER(18,2),
yc_lease_interest NUMBER(18,2),
yc_lease_cash_fin NUMBER(18,2),
yc_lease_corpus_fin NUMBER(18,2),
yc_lease_interest_fin NUMBER(18,2),
yc_lease_cash_tax_fin NUMBER(18,2),
yc_lease_corpus_tax_fin NUMBER(18,2),
yc_lease_interest_tax_fin NUMBER(18,2),
yc_corpus_balance_fin NUMBER(18,2),
yc_asset_status NUMBER(38)
)
--租金计划表拷贝
delete from yls_rent
insert into yls_rent(
yc_CONT_NAME,
yc_CONT_CODE,
yc_lease_time,
yc_plan_date,
yc_inte_date,
yc_direction,
yc_trans_type,
yc_lease_cash,
yc_lease_corpus,
yc_lease_interest,
yc_lease_cash_fin,
yc_lease_corpus_fin,
yc_lease_interest_fin,
yc_lease_cash_tax_fin,
yc_lease_corpus_tax_fin,
yc_lease_interest_tax_fin,
yc_corpus_balance_fin,
yc_asset_status
)
select distinct
yc.CONT_NAME,
yc.CONT_CODE,
lease_time,
plan_date,
inte_date,
direction,
trans_type,
lease_cash,
lease_corpus,
lease_interest,
lease_cash_fin,
lease_corpus_fin,
lease_interest_fin,
lease_cash_tax_fin,
lease_corpus_tax_fin,
lease_interest_tax_fin,
corpus_balance_fin,
asset_status
from yls_inout_plan_c@testlink yip
join yls_contract@testlink yc on yip.pk_contract=yc.pk_contract;
--报价方案表
create table yls_LeaseCalculator(
yc_cont_code VARCHAR2(256),
yc_active_time VARCHAR2(256),
yc_net_finance_cash NUMBER(18,2),
yc_trade_discount NUMBER(18,2),
yc_plan_cash_loan NUMBER(18,2),
yc_lease_method NUMBER(38),
yc_tax_mode NUMBER(38),
yc_if_corpus_tickets NUMBER(38),
yc_lease_cash NUMBER(18,2),
yc_lease_corpus NUMBER(18,2),
yc_lease_interest NUMBER(18,2),
yc_down_payment NUMBER(18,2),
yc_deposit_cash NUMBER(18,2),
yc_srvfee_cash_in NUMBER(18,2),
yc_srvfee_cash_out NUMBER(18,2),
yc_lease_times NUMBER(38),
yc_start_date CHAR(10),
yc_interrate_level NUMBER(38),
yc_interrate NUMBER(10,6),
yc_final_rate NUMBER(10,6),
yc_cal_digit NUMBER(38),
yc_rent_irr NUMBER(8,6),
yc_project_irr NUMBER(8,6),
yc_lease_irr NUMBER(8,6),
yc_contract_xirr NUMBER(8,6),
yc_contract_notax_xirr NUMBER(8,6),
yc_project_month_irr NUMBER(8,6),
yc_project_month_notax_irr NUMBER(8,6),
yc_year_days_flow NUMBER(38),
yc_pk_currtype VARCHAR2(300),
yc_year_days NUMBER(38)
)
delete from yls_LeaseCalculator
--报价方案数据拷贝
insert into yls_LeaseCalculator(
yc_cont_code,
yc_active_time,
yc_net_finance_cash,
yc_trade_discount,
yc_plan_cash_loan,
yc_lease_method,
yc_tax_mode,
yc_if_corpus_tickets,
yc_lease_cash,
yc_lease_corpus,
yc_lease_interest,
yc_down_payment,
yc_deposit_cash,
yc_srvfee_cash_in,
yc_srvfee_cash_out,
yc_lease_times,
yc_start_date,
yc_interrate_level,
yc_interrate,
yc_final_rate,
yc_cal_digit,
yc_rent_irr,
yc_project_irr,
yc_lease_irr,
yc_contract_xirr,
yc_contract_notax_xirr,
yc_project_month_irr,
yc_project_month_notax_irr,
yc_year_days_flow,
yc_pk_currtype,
yc_year_days
)
select distinct
ycc.cont_code,
'' as active_time,
ylc.net_finance_cash,
ylc.trade_discount,
ylc.plan_cash_loan,
ylc.lease_method,
ylc.tax_mode,
ylc.if_corpus_tickets,
yip.lease_cash,
yip.lease_corpus,
yip.lease_interest,
ylc.down_payment,
ylc.deposit_cash,
ylc.srvfee_cash_in,
ylc.srvfee_cash_out,
ylc.lease_times,
yi.start_date,
ylc.interrate_level,
ylc.interrate,
ylc.final_rate,
ylc.cal_digit,
ylc.rent_irr,
ylc.project_irr,
ylc.lease_irr,
ylc.contract_xirr,
ylc.contract_notax_xirr,
ylc.project_month_irr,
ylc.project_month_notax_irr,
ylc.year_days_flow,
bc.name,
ylc.year_days
from yls_contract_c@testlink ycc
join yls_lease_calculator_c@testlink ylc on ycc.pk_contract=ylc.pk_contract
join yls_inout_plan_c@testlink yip on ylc.pk_lease_calculator=yip.source_bill
join yls_interrate@testlink yi on yi.pk_interrate=ylc.pk_interrate
join bd_currtype@testlink bc on ylc.pk_currtype=bc.pk_currtype
--合同信息表
CREATE TABLE YLS_CONTRACT
(
YC_CONT_CODE VARCHAR2(256 BYTE) ,
YC_CONT_NAME VARCHAR2(256 BYTE) ,
YC_PROJECT_CODE VARCHAR2(32 BYTE) ,
YC_ACCOUNT_NAME VARCHAR2(300 BYTE) ,
YC_ACCOUNT_NO VARCHAR2(40 BYTE) ,
YC_CONT_YEAR VARCHAR2(32 BYTE) ,
YC_LEASE_DATE_PREDICT CHAR(10 BYTE) ,
YC_CONT_SIGNED_DATE CHAR(10 BYTE) ,
YC_CONT_START_DATE CHAR(10 BYTE) ,
YC_CONT_END_DATE CHAR(10 BYTE) ,
YC_PLAN_LOAN_PREDICT CHAR(10 BYTE) ,
YC_VALUE_DATED_PREDICT CHAR(10 BYTE) ,
YC_MACHINE_AMOUNT NUMBER(18, 2) ,
YC_CONT_AMOUNT NUMBER(18, 2) ,
YC_NAME VARCHAR2(300 BYTE) ,
YC_EXCHG_RATE NUMBER(10, 6) ,
YC_SIGN_NAME VARCHAR2(256 BYTE) ,
YC_THING_NAME VARCHAR2(512 BYTE) ,
YC_THING_CODE VARCHAR2(32 BYTE) ,
YC_PRO_NAME VARCHAR2(256 BYTE) ,
YC_PRO_CODE VARCHAR2(32 BYTE) ,
YC_THING_TYPE CHAR(20 BYTE) ,
YC_DEV_COST NUMBER(18, 2) ,
YC_NET_WORTH NUMBER(18, 2) ,
YC_VALUATION NUMBER(18, 2) ,
YC_THING_COST NUMBER(18, 2) ,
YC_DELIVERY_DATE CHAR(10 BYTE) ,
YC_DELIVERY_ADDRESS VARCHAR2(256 BYTE) ,
YC_USE_ADDRESS VARCHAR2(256 BYTE) ,
YC_BILLSTATUS NUMBER(38, 0) ,
YC_OPERATOR_NAME VARCHAR2(256 BYTE) ,
YC_OPERATE_DATE CHAR(10 BYTE) ,
YC_OPERATE_TIME CHAR(19 BYTE) ,
YC_CHECKER_NAME VARCHAR2(256 BYTE) ,
YC_CHECK_DATE CHAR(10 BYTE) ,
YC_CHECK_TIME CHAR(19 BYTE) ,
YC_ORG_NAME VARCHAR2(300 BYTE)
)
--合同信息表数据拷贝
insert into yls_contract(
yc_cont_code,
yc_cont_name,
yc_PROJECT_CODE,
yc_account_name,
yc_account_no,
yc_cont_year,
yc_lease_date_predict,
yc_cont_signed_date,
yc_cont_start_date,
yc_cont_end_date,
yc_plan_loan_predict,
yc_value_dated_predict,
yc_machine_amount,
yc_cont_amount,
yc_name,
yc_exchg_rate,
yc_sign_name,
yc_thing_name,
yc_thing_code,
yc_pro_name,
yc_pro_code,
yc_thing_type,
yc_dev_cost,
yc_net_worth,
yc_valuation,
yc_thing_cost,
yc_DELIVERY_DATE,
yc_DELIVERY_ADDRESS,
yc_USE_ADDRESS,
yc_billstatus,
yc_operator_name,
yc_operate_date,
yc_operate_time,
yc_checker_name,
yc_check_date,
yc_check_time,
yc_org_name
)
select distinct
yco.cont_code,
yco.cont_name,
ypi.PROJECT_CODE,
ysu.customer_name,
ysu.customer_code,
yco.cont_year,
yco.lease_date_predict,
yco.cont_signed_date,
yco.cont_start_date,
yco.cont_end_date,
yco.plan_loan_predict,
yco.value_dated_predict,
yco.machine_amount,
yco.cont_amount,
bc.name,
yco.exchg_rate,
person.s_names,
things.thing_name,
things.thing_code,
things.customer_name,
things.customer_code,
things.thing_type,
things.total_cost,
things.net_worth,
things.valuation,
things.total,
yco.delivery_time,
yco.delivery_address,
yco.install_use_address,
yco.billstatus,
person.o_names,
yco.operate_date,
yco.operate_time,
person.c_names,
yco.check_date,
yco.check_time,
so.org_name
from YLS_CONTRACT@testlink yco
join yls_project_info@testlink ypi on yco.pk_project=ypi.pk_project_info --项目信息
join bd_currtype@testlink bc on yco.pk_currency=bc.pk_currtype--币种
join sm_org@testlink so on so.pk_org=yco.pk_org--部门
join V_CUSTOMER_NCCORP@testlink ysu on yco.pk_customer_lessor=ysu.pk_customer--出租方
join (select distinct
ysb.pk_contract,
s_names,
o_names,
c_names
from YLS_CONTRACT@testlink ysb
join
(select * from YLS_CONTRACT@testlink ybb
join
(select yss.pk_contract pk_s,si.user_name s_names from YLS_CONTRACT@testlink yss
join SM_USER_VIEW_MANYDEPT@testlink si on yss.pk_sign=si.pk_user) siner on siner.pk_s=ybb.pk_contract
join
(select yoo.pk_contract pk_o,op.user_name o_names from YLS_CONTRACT@testlink yoo
join SM_USER_VIEW_MANYDEPT@testlink op on yoo.pk_operator=op.pk_user) oper on oper.pk_o=ybb.pk_contract
join
(select ycc.pk_contract pk_c,ch.user_name c_names from YLS_CONTRACT@testlink ycc
join SM_USER_VIEW_MANYDEPT@testlink ch on ycc.pk_checker=ch.pk_user
) checker on checker.pk_c=ybb.pk_contract
) alluser on ysb.pk_contract=alluser.pk_contract
) person on yco.pk_contract=person.pk_contract
join (
select distinct
ycth.pk_contract,
thing.thing_name,
thing.thing_code,
thing.customer_name,--供应商姓名
thing.customer_code,--供应商编号
thing.thing_type,
thing.total_cost,
thing.net_worth,
thing.valuation,
thing.total
from YLS_CONTRACT@testlink ycth join
(
select distinct
yprt.source_bill as source_bill,
yprt.thing_name as thing_name,
yprt.thing_code as thing_code,
ycu.customer_name as customer_name,--供应商姓名
ycu.customer_code as customer_code,--供应商编号
param.param_name as thing_type,
yprt.total_cost as total_cost,
yprt.net_worth as net_worth,
yprt.valuation as valuation,
tal.total as total
from YLS_PROJECT_RENT_THING@testlink yprt
join yls_customer@testlink ycu on yprt.pk_consumer=ycu.pk_customer
join yls_parameter@testlink param on yprt.thing_type=param.pk_parameter
join (
select
SOURCE_BILL,
SUM(NVL(TOTAL_COST, 0)) total
FROM YLS_PROJECT_RENT_THING@testlink PRT
GROUP BY SOURCE_BILL
) tal on tal.SOURCE_BILL=yprt.source_bill
)thing on thing.source_bill=ycth.pk_contract
) things on yco.pk_contract=things.pk_contract;
--项目信息表
create table yls_project_info(
yc_project_name VARCHAR2(256),
yc_project_code VARCHAR2(32),
yc_customer_name VARCHAR2(256),
yc_customer_code VARCHAR2(32),
yc_project_batch NUMBER(38),
yc_project_status NUMBER(38),
yc_lease_categry NUMBER(38),
yc_project_type NUMBER(38),
yc_project_source NUMBER(38),
yc_lease_type NUMBER(38),
yc_leaseback_type NUMBER(38),
yc_project_tax_type NUMBER(38),
yc_is_insure NUMBER(38),
yc_if_co_lessee NUMBER(38),
yc_plan_release_date CHAR(10),
yc_release_amount NUMBER(18,2) ,
yc_purchase_total_amount NUMBER(18,2),
yc_main__name VARCHAR2(256),
yc_help_name VARCHAR2(256),
yc_limit_class NUMBER(38),
yc_granting_type NUMBER(38),
yc_limit_amt NUMBER(18,2),
yc_granting_start_date CHAR(10),
yc_granting_times NUMBER(38),
yc_granting_end_date CHAR(10) ,
yc_granting_original_limit NUMBER(18,2),
yc_granting_used_limit NUMBER(18,2),
yc_granting_add_limit NUMBER(18,2) ,
yc_granting_surplus_limit NUMBER(18,2),
yc_cu_name VARCHAR2(300),
yc_thing_name VARCHAR2(512),
yc_thing_code VARCHAR2(32),
yc_thing_model VARCHAR2(256),
yc_thing_type CHAR(20),
yc_total_cost NUMBER(18,2),
yc_net_worth NUMBER(18,2),
yc_valuation NUMBER(18,2),
yc_DELIVERY_DATE CHAR(10),
yc_DELIVERY_ADDRESS VARCHAR2(256),
yc_sum_total_cost NUMBER(18,2),
yc_guarantee_method VARCHAR2(256),
yc_corp_cust VARCHAR2(256),
yc_pers_cust VARCHAR2(256),
yc_PLAN_CASH NUMBER(18,2),
yc_PLEDGE_AMOUNT NUMBER(18,2),
yc_PRENDA_AMOUNT NUMBER(18,2),
yc_billstatus NUMBER(38),
yc_operator_name VARCHAR2(256),
yc_operate_date CHAR(10),
yc_operate_time CHAR(19),
yc_checker_name VARCHAR2(256),
yc_check_date CHAR(10),
yc_check_time CHAR(19),
yc_org_name VARCHAR2(300)
);
--项目信息数据拷贝
insert into yls_project_info(
yc_project_name,
yc_project_code,
yc_customer_name,
yc_customer_code,
yc_project_batch,
yc_project_status,
yc_lease_categry,
yc_project_type,
yc_project_source,
yc_lease_type,
yc_leaseback_type,
yc_project_tax_type,
yc_is_insure,
yc_if_co_lessee,
yc_plan_release_date,
yc_release_amount,
yc_purchase_total_amount,
yc_main__name,
yc_help_name,
yc_limit_class,
yc_granting_type,
yc_limit_amt,
yc_granting_start_date,
yc_granting_times,
yc_granting_end_date,
yc_granting_original_limit,
yc_granting_used_limit,
yc_granting_add_limit,
yc_granting_surplus_limit,
yc_cu_name,
yc_thing_name,
yc_thing_code,
yc_thing_model,
yc_thing_type,
yc_total_cost,
yc_net_worth,
yc_valuation,
yc_DELIVERY_DATE,
yc_DELIVERY_ADDRESS,
yc_sum_total_cost,
yc_guarantee_method,
yc_corp_cust,
yc_pers_cust,
yc_PLAN_CASH,
yc_PLEDGE_AMOUNT,
yc_PRENDA_AMOUNT,
yc_billstatus,
yc_operator_name,
yc_operate_date,
yc_operate_time,
yc_checker_name,
yc_check_date,
yc_check_time,
yc_org_name)
select
proj.project_name,
proj.project_code,
yc.customer_name,
yc.customer_code,
proj.project_batch,
proj.project_status,
proj.lease_categry,
proj.project_type,
proj.project_source,
proj.lease_type,
proj.leaseback_type,
proj.project_tax_type,
proj.is_insure,
proj.if_co_lessee,
proj.plan_release_date,
proj.release_amount,
proj.purchase_total_amount,
person.cust_main,
person.cust_help,
proj.limit_class,
proj.granting_type,
proj.limit_amt,
proj.granting_start_date,
proj.granting_times,
proj.granting_end_date,
proj.granting_original_limit,
proj.granting_used_limit,
proj.granting_add_limit,
proj.granting_surplus_limit,
person.current_name,
rent_thing.thing_name,
rent_thing.thing_code,
rent_thing.thing_model,
rent_thing.thing_type,
rent_thing.total_cost,
rent_thing.net_worth,
rent_thing.valuation,
rent_thing.DELIVERY_DATE,
rent_thing.DELIVERY_ADDRESS,
rent_thing.total,
pledge.GUARANTEE_METHOD,
pledge.CORP_CUST,
pledge.PERS_CUST,
pledge.PLAN_CASH,
pledge.PLEDGE_AMOUNT,
pledge.PRENDA_AMOUNT,
proj.billstatus,
person.oprators,
proj.operate_date,
proj.operate_time,
person.checkers,
proj.check_date,
proj.check_time,
person.org_name
from yls_project_info@testlink proj
join yls_customer@testlink yc on yc.pk_customer=proj.pk_consumer
join (
SELECT distinct CP.PK_ASSURE_PROJECT prop,
db.param_name GUARANTEE_METHOD,
DECODE(CUST.CUSTOMER_TYPE, 0, CUST.CUSTOMER_NAME, NULL) CORP_CUST,
DECODE(CUST.CUSTOMER_TYPE, 1, CUST.CUSTOMER_NAME, NULL) PERS_CUST,
CP.PLAN_CASH PLAN_CASH,
CP.PLEDGE_AMOUNT PLEDGE_AMOUNT,
CP.PRENDA_AMOUNT PRENDA_AMOUNT
FROM YLS_CUST_PLEDGE@testlink CP, YLS_CUSTOMER@testlink CUST,
(
select pi.param_name, pi.param_value
from yls_parameter@testlink pi
left join yls_param_type@testlink pm
on pm.pk_param_type = pi.pk_param_type
where pm.param_code = '1000299'
) db
WHERE CP.PK_CUSTOMER = CUST.PK_CUSTOMER
and CP.GUARANTEE_METHOD=db.param_value
) pledge on pledge.prop=proj.pk_project_info
join (
select
ypi.pk_project_info proc,
cust_main.customer_name cust_main,
cust_help.customer_name cust_help,
ope.user_name as oprators,
che.user_name checkers,
so.org_name org_name,
bc.name as current_name
from yls_project_info@testlink ypi
join v_member_contact@testlink cust_main on ypi.pk_cust_main=cust_main.pk_member_contact --pk_cust_main 主办人 v_member_contact(pk_member_contact)/pk_cust_main
join v_member_contact@testlink cust_help on ypi.pk_cust_help=cust_help.pk_member_contact --pk_cust_help 承办人 v_member_contact(pk_member_contact)/pk_cust_help
join SM_USER_VIEW_MANYDEPT@testlink ope on ypi.pk_operator=ope.pk_user --pk_operator 操作人 SM_USER_VIEW_MANYDEPT(pk_operator)/pk_user
join SM_USER_VIEW_MANYDEPT@testlink che on ypi.pk_checker=che.pk_user --pk_checker 审核人 SM_USER_VIEW_MANYDEPT(pk_checker)/pk_user
join sm_org@testlink so on ypi.pk_org=so.pk_org
join bd_currtype@testlink bc on ypi.granting_currency=pk_currtype
) person on person.proc=proj.pk_project_info
join (
select distinct
ypi.pk_project_info pk_prox,
yprt.thing_name thing_name,
yprt.thing_code thing_code,
yprt.model thing_model,
param.param_name thing_type,
yprt.total_cost total_cost,
yprt.net_worth net_worth,
yprt.valuation valuation,
yprt.DELIVERY_DATE DELIVERY_DATE,
yprt.DELIVERY_ADDRESS DELIVERY_ADDRESS,
tal.total total
from YLS_PROJECT_RENT_THING@testlink yprt
join yls_parameter@testlink param on yprt.thing_type=param.pk_parameter
join yls_contract@testlink yco on yprt.source_bill=yco.pk_contract
join yls_project_info@testlink ypi on yco.pk_project= ypi.pk_project_info
join (
select
SOURCE_BILL,
SUM(NVL(TOTAL_COST, 0)) total
FROM YLS_PROJECT_RENT_THING@testlink PRT
GROUP BY SOURCE_BILL
) tal on tal.source_bill=ypi.pk_project_info
) rent_thing on rent_thing.pk_prox=proj.pk_project_info;
drop table yls_customer_info;
--客户信息表
create table yls_customer_info(
yc_customer_name VARCHAR2(256),
yc_customer_code VARCHAR2(256),
yc_customer_property VARCHAR2(256),
yc_economic_type VARCHAR2(256),
yc_industry_type VARCHAR2(256),
yc_cusotmer_class VARCHAR2(512),
yc_reg_address VARCHAR2(256),
yc_industry VARCHAR2(55),
yc_industry1 VARCHAR2(55),
yc_industry2 VARCHAR2(55),
yc_industry3 VARCHAR2(55),
yc_enter_scale_6m NUMBER(38),
yc_enter_scale_pbc NUMBER(38),
yc_enter_scale_inner NUMBER(38),
yc_identity_type NUMBER(38),
yc_identity_no VARCHAR2(32),
yc_start_date_identity VARCHAR2(256),
yc_end_date_identity VARCHAR2(256),
yc_legal_person VARCHAR2(256),
yc_legal_IDENTITY_TYPE NUMBER(38),
yc_legal_IDENTITY_NO VARCHAR2(256),
yc_capital_cur VARCHAR2(256),
yc_capital NUMBER(18,2),
yc_capital_cur_paidin VARCHAR2(256),
yc_capital_paidin NUMBER(18,2),
yc_country VARCHAR2(256),
yc_reg_address_membership NUMBER(38),
yc_province VARCHAR2(256),
yc_city VARCHAR2(256),
yc_district VARCHAR2(256),
yc_office_address VARCHAR2(256),
yc_dept VARCHAR2(256),
yc_customer_manager VARCHAR2(256),
yc_operator VARCHAR2(256),
yc_operate_date VARCHAR2(256),
yc_operator_lst VARCHAR2(256),
yc_operate_date_lst VARCHAR2(256),
yc_org VARCHAR2(256)
);
insert into yls_customer_info(
yc_customer_name,
yc_customer_code,
yc_customer_property,
yc_economic_type,
yc_industry_type,
yc_cusotmer_class,
yc_reg_address,
yc_industry,
yc_industry1,
yc_industry2,
yc_industry3,
yc_enter_scale_6m,
yc_enter_scale_pbc,
yc_enter_scale_inner,
yc_identity_type,
yc_identity_no,
yc_start_date_identity,
yc_end_date_identity,
yc_legal_person,
yc_legal_IDENTITY_TYPE,
yc_legal_IDENTITY_NO,
yc_capital_cur,
yc_capital,
yc_capital_cur_paidin,
yc_capital_paidin,
yc_country,
yc_reg_address_membership,
yc_province,
yc_city,
yc_district,
yc_office_address,
yc_dept,
yc_customer_manager,
yc_operator,
yc_operate_date,
yc_operator_lst,
yc_operate_date_lst,
yc_org)
--客户信息表数据拷贝
select distinct
yc_customer_name,
yc_customer_code,
yc_customer_property,
yc_economic_type,
yc_industry_type,
yc_cusotmer_class,
yc_reg_address,
yc_industry,
yc_industry1,
yc_industry2,
yc_industry3,
yc_enter_scale_6m,
yc_enter_scale_pbc,
yc_enter_scale_inner,
yc_identity_type,
yc_identity_no,
yc_start_date_identity,
yc_end_date_identity,
yc_legal_person,
yc_legal_IDENTITY_TYPE,
yc_legal_IDENTITY_NO,
yc_capital_cur,
yc_capital,
yc_capital_cur_paidin,
yc_capital_paidin,
yc_country,
yc_reg_address_membership,
yc_province,
yc_city,
yc_district,
yc_office_address,
yc_dept,
yc_customer_manager,
yc_operator,
yc_operate_date,
yc_operator_lst,
yc_operate_date_lst,
yc_org
from (--基本信息
select distinct
cuc.pk_customer pln,
cuc.customer_name yc_customer_name,
cuc.customer_code yc_customer_code,
vcu.param_name yc_customer_property,
vet.param_name yc_economic_type,
vit.param_name yc_industry_type,
custome_class yc_cusotmer_class,
reg_address yc_reg_address
from yls_customer@testlink cuc
join YLS_CUSTOMER_CORP@testlink ycs on ycs.PK_CUSTOMER=cuc.PK_CUSTOMER
join v_langlib_parameter@testlink vcu on vcu.pk_parameter=ycs.customer_property
join v_langlib_parameter@testlink vet on vet.pk_parameter=ycs.economic_type
join v_langlib_parameter@testlink vit on vit.pk_parameter=ycs.industry_type
join (
select pk_customer, to_char((
select wm_concat(param_name) from
(SELECT P.*,
PT.PARAM_CODE P_PARAM_CODE
FROM V_LANGLIB_PARAMETER@testlink P
LEFT JOIN YLS_PARAM_TYPE@testlink PT
ON PT.PK_PARAM_TYPE = P.PK_PARAM_TYPE) name
where instr(cusotmer_class, pk_parameter) > 0)) as custome_class
FROM YLS_CUSTOMER_CORP@testlink
) cs_cs
on cs_cs.pk_customer=ycs.PK_CUSTOMER
) cuinfo
join (
select distinct
cuk.pk_customer pp,
v1.param_name yc_industry,
v2.param_name yc_industry1,
v3.param_name yc_industry2,
v4.param_name yc_industry3,
enter_scale_6m yc_enter_scale_6m,
enter_scale_pbc yc_enter_scale_pbc,
enter_scale_inner yc_enter_scale_inner
from yls_customer@testlink cuk
join YLS_CUSTOMER_CORP@testlink ycs on ycs.PK_CUSTOMER=cuk.PK_CUSTOMER
join v_langlib_parameter@testlink v1 on v1.pk_parameter=ycs.industry
join v_langlib_parameter@testlink v2 on v2.pk_parameter=ycs.industry1
join v_langlib_parameter@testlink v3 on v3.pk_parameter=ycs.industry2
join v_langlib_parameter@testlink v4 on v4.pk_parameter=ycs.industry3
) cal on cal.pp=cuinfo.pln
join (
select distinct
cub.pk_customer pcc,
cub.identity_type yc_identity_type ,
cub.identity_no yc_identity_no ,
ycs.start_date_identity yc_start_date_identity ,
ycs.end_date_identity yc_end_date_identity ,
xsa.customer_name yc_legal_person ,
xsa.IDENTITY_TYPE yc_legal_IDENTITY_TYPE ,
xsa.IDENTITY_NO yc_legal_IDENTITY_NO ,
cur.name yc_capital_cur ,
ycs.capital yc_capital ,
cur_paidin.name yc_capital_cur_paidin ,
ycs.capital_paidin yc_capital_paidin
from yls_customer@testlink cub
join YLS_CUSTOMER_CORP@testlink ycs on ycs.PK_CUSTOMER=cub.PK_CUSTOMER
join yls_customer@testlink xsa on xsa.pk_customer=ycs.pk_customer_person
join bd_currtype@testlink cur on ycs.capital_cur=cur.pk_currtype
join bd_currtype@testlink cur_paidin on ycs.capital_cur_paidin=cur_paidin.pk_currtype
) card_info on card_info.pcc=cuinfo.pln
join (
select distinct
cu.pk_customer pm,
country.param_name yc_country,
reg_address_membership yc_reg_address_membership,
province.name yc_province,
city.name yc_city,
district.name yc_district,
office_address yc_office_address
from yls_customer@testlink cu
join YLS_CUSTOMER_CORP@testlink ycc on cu.PK_CUSTOMER=ycc.PK_CUSTOMER
join BD_AREACL@testlink province on province.PK_AREACL=ycc.province and province.dr='0'
join BD_AREACL@testlink city on city.PK_AREACL=ycc.city and city.dr='0'
join BD_AREACL@testlink district on district.PK_AREACL=ycc.district and district.dr='0'
join v_langlib_parameter@testlink country on ycc.country=country.pk_parameter
) dress on dress.pm=cuinfo.pln
join (
select distinct
cus.pk_customer pk_cus,
od.name yc_dept,
sm.user_name yc_customer_manager,
op.user_name yc_operator,
cus.operate_date yc_operate_date,
cus.operate_date_lst yc_operator_lst,
opl.user_name yc_operate_date_lst,
org.org_name yc_org
from yls_customer@testlink cus
join SM_USER_VIEW_MANYDEPT@testlink sm on cus.customer_manager=sm.pk_user
join SM_USER_VIEW_MANYDEPT@testlink op on cus.pk_operator=sm.pk_user
join SM_USER_VIEW_MANYDEPT@testlink opl on cus.pk_operator_lst=sm.pk_user
join org_dept@testlink od on cus.pk_dept=od.pk_dept
join sm_org@testlink org on cus.pk_org=org.pk_org
) person on person.pk_cus=cuinfo.pln; 
5311
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
