Interface -[AR] Customer interface

如下程式
進行Customer資料的轉移

/*
Program No. : TWXIECUST.sql
(Twinhead XIE CUSTOMER.sql)
=======================================================================================
0.0 Data schema created .. as script bellow
0.1 Migration/Modify data and set need_transfer flag to Y
1 Primary Algorithm : Cursor control Customer/Address (and Contact maintained manully)
2 Insert into Customer Interface
2.1 Customer Level : cust-REF
1 for ra_customers_interface_all
n currency credit for ra_customer_profiles_int_all
2.2 Case I(Address both Bill/Ship): addr-REF
2 for ra_customers_interface_all
(Since credit maintain in customer level so do not need insert into ra_customer_profiles_int_all)
2.3 Case II(Address Bill-to only): addr-REF
1 for ra_customers_interface_all
2.4 Case III(Address Ship-to only): addr-REF
1 for ra_customers_interface_all
=======================================================================================
Date Author Status Reason
2006/12/22 Zhxiang Created For Customer Import w/ 3 steps
1. get data into temp tables
2. insert into interface tables level by level
3. Do customer interface import
=======================================================================================
*/

/*---Temp table algorithm script----
drop table TWXIE_CUST_IMPORT
/

create table TWXIE_CUST_IMPORT as
select * from XX_CUST_FOR_IMPORT@XPOWERPROD.TWINHEAD.COM.TW
/

drop table TWXIE_CUST_CREDIT
/

create table TWXIE_CUST_CREDIT as
select rc.customer_number,rc.customer_id,rc.customer_name,acpa.CURRENCY_CODE,acpa.TRX_CREDIT_LIMIT,acpa.overall_credit_limit
from AR_CUSTOMER_PROFILE_AMOUNTS@XPOWERPROD.TWINHEAD.COM.TW acpa,
AR_CUSTOMER_PROFILES@XPOWERPROD.TWINHEAD.COM.TW acp,
RA_CUSTOMERS@XPOWERPROD.TWINHEAD.COM.TW rc,
XX_CUST_FOR_IMPORT@XPOWERPROD.TWINHEAD.COM.TW XX
where xx.customer_number=rc.customer_number
and rc.customer_id=acp.customer_id
and acpa.CUSTOMER_PROFILE_ID=acp.CUSTOMER_PROFILE_ID
and acpa.TRX_CREDIT_LIMIT+acpa.OVERALL_CREDIT_LIMIT>0
/

drop table TWXIE_CUST_DETAIL
/

create table TWXIE_CUST_DETAIL as
select rc.customer_id,
rc.customer_name,
rc.customer_number,
rc.TAX_REFERENCE,
rc.attribute1,
rc.attribute2,
raa.address_id,
raa.country,
raa.address1,
raa.address2,
raa.address3,
raa.address4,
raa.BILL_TO_FLAG,
raa.ship_to_flag,
rsua.site_use_code,
rsua.SITE_USE_ID,
rsua.primary_flag,
rsua.BILL_TO_SITE_USE_ID,
terms.name site_terms,
replace(rsua.tax_code,'%','') tax_code,
rt.LAST_NAME,
rt.first_name,
rt.TITLE,
rt.JOB_TITLE,
xx.sales,
'N' need_transfer
from ra_contacts@XPOWERPROD.TWINHEAD.COM.TW rt,
RA_SITE_USES_ALL@XPOWERPROD.TWINHEAD.COM.TW rsua,
ra_addresses_all@XPOWERPROD.TWINHEAD.COM.TW raa,
ra_customers@XPOWERPROD.TWINHEAD.COM.TW rc,
RA_TERMS@XPOWERPROD.TWINHEAD.COM.TW terms,
XX_CUST_FOR_IMPORT@XPOWERPROD.TWINHEAD.COM.TW xx
where rc.customer_number=xx.customer_number
and rc.customer_id=raa.customer_id
and raa.address_id=rsua.address_id
and rsua.STATUS='A'
and terms.term_id(+)=rsua.payment_term_id
and rsua.contact_id=rt.contact_id(+)
order by rc.customer_name,rc.customer_number,raa.country,raa.address1,rsua.site_use_code
/


--check script
select * from TWXIE_CUST_DETAIL where BILL_TO_SITE_USE_ID not in
(select SITE_USE_ID from TWXIE_CUST_DETAIL)

--final update payment
update ra_site_uses_all
set PAYMENT_TERM_ID=(select TERM_ID from RA_TERMS_TL where name=ATTRIBUTE1)
,ATTRIBUTE1=NULL
where creation_date>=sysdate-1
and ATTRIBUTE1 is not null

----End of script-------------*/


SET serveroutput on size 200000

SET SERVEROUTPUT ON;
Declare
v_cust_ref varchar2(100);
v_addr_ref varchar2(100);
v_bill_addr varchar2(100);
v_time varchar2(20);
v_cust_seq number;
v_addr_seq number;

v_ou_id number;
v_debug varchar2(100);

CURSOR CUR_CUST IS
select distinct
CUSTOMER_ID,
CUSTOMER_NAME,
CUSTOMER_NUMBER,
TAX_REFERENCE,
ATTRIBUTE1,
ATTRIBUTE2
from TWXIE_CUST_DETAIL
where NEED_TRANSFER='Y'
order by CUSTOMER_NAME;

CURSOR CUR_CREDIT(i_cust_id in number) IS
select CURRENCY_CODE,
TRX_CREDIT_LIMIT,
OVERALL_CREDIT_LIMIT
from TWXIE_CUST_CREDIT
where CUSTOMER_ID=i_cust_id;

CURSOR CUR_ADDRESS_DETAIL(i_cust_id in number) IS
select distinct
ADDRESS_ID,
COUNTRY,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4 from
(select ADDRESS_ID,
COUNTRY,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
BILL_TO_SITE_USE_ID
from TWXIE_CUST_DETAIL
where NEED_TRANSFER='Y'
and CUSTOMER_ID=i_cust_id
order by BILL_TO_SITE_USE_ID DESC);

CURSOR CUR_ADDRESS_SITE_BILLTO(i_addr_id in number) IS
select SITE_USE_CODE,
SITE_USE_ID,
PRIMARY_FLAG,
SITE_TERMS,
TAX_CODE
from TWXIE_CUST_DETAIL
where NEED_TRANSFER='Y'
and ADDRESS_ID=i_addr_id
and SITE_USE_CODE='BILL_TO';

CURSOR CUR_ADDRESS_SITE_SHIPTO(i_addr_id in number) IS
select SITE_USE_CODE,
SITE_USE_ID,
PRIMARY_FLAG,
BILL_TO_SITE_USE_ID
from TWXIE_CUST_DETAIL
where NEED_TRANSFER='Y'
and ADDRESS_ID=i_addr_id
and SITE_USE_CODE='SHIP_TO';

/****************************************
* Main Program *
****************************************/
Begin
dbms_output.put_line('Start Progam.');
v_ou_id:=102;
v_debug:='01';
v_cust_seq:=1;
v_addr_seq:=1;

select to_char(sysdate,'YYYYMMDDHH24MISS')
into v_time
from dual;

--------
for rec_cust in CUR_CUST loop
v_debug:='02';
-- insert into ra_customers_interface_all
select 'XC'v_timetrim(to_char(v_cust_seq,'000'))
into v_cust_ref
from dual;

v_debug:='03';
dbms_output.put_line(rec_cust.CUSTOMER_NAME);

insert into ra_customers_interface_all
(ORIG_SYSTEM_CUSTOMER_REF,
INSERT_UPDATE_FLAG,
CUSTOMER_NAME,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
CUSTOMER_STATUS,
ORG_ID,
CUST_TAX_REFERENCE,
CUSTOMER_ATTRIBUTE_CATEGORY,
CUSTOMER_ATTRIBUTE1,
CUSTOMER_ATTRIBUTE2,
CUSTOMER_ATTRIBUTE3,
CUSTOMER_ATTRIBUTE4
)
values
(v_cust_ref,
'I',--INSERT_UPDATE_FLAG,
rec_cust.CUSTOMER_NAME, --CUSTOMER_NAME,
0, --LAST_UPDATED_BY,
sysdate, --LAST_UPDATE_DATE,
0, --CREATED_BY,
sysdate, --CREATION_DATE,
'A', --CUSTOMER_STATUS
v_ou_id,
rec_cust.TAX_REFERENCE,
'客戶簡稱',
rec_cust.ATTRIBUTE1,
rec_cust.ATTRIBUTE2,
rec_cust.CUSTOMER_ID,
rec_cust.CUSTOMER_NUMBER
);
-- insert into ra_customer_profiles_int_all
-----
v_debug:='04';
for rec_credit in CUR_CREDIT(rec_cust.CUSTOMER_ID) loop
dbms_output.put_line('--------profile:'rec_credit.CURRENCY_CODE);
INSERT INTO ra_customer_profiles_int_all
(orig_system_customer_ref,
insert_update_flag,
customer_profile_class_name,
credit_hold,
last_updated_by,
last_update_date,
created_by,
creation_date,
OVERALL_CREDIT_LIMIT,
TRX_CREDIT_LIMIT,
CURRENCY_CODE
)
VALUES
(v_cust_ref,
'I', -- insert_update_flag
'DEFAULT', -- Should be valid profile class
'N', -- This can be 'Y','N' not null.
0, -- last_updated_by
sysdate, -- last_update_date
0, -- created_by
sysdate, -- created_by
rec_credit.OVERALL_CREDIT_LIMIT,
rec_credit.TRX_CREDIT_LIMIT,
rec_credit.CURRENCY_CODE
);
v_debug:='05';
end loop;
------
v_debug:='06';
-- Address Level
v_addr_seq:=1;
dbms_output.put_line('--------custid:'to_char(rec_cust.CUSTOMER_ID));
------
for rec_addr in CUR_ADDRESS_DETAIL(rec_cust.CUSTOMER_ID) loop
v_debug:='07';
select v_cust_ref'_'trim(to_char(v_addr_seq,'00'))
into v_addr_ref
from dual;
v_debug:='08';
dbms_output.put_line('--------addrid:'to_char(rec_addr.ADDRESS_ID));
----
for rec_site_bt in CUR_ADDRESS_SITE_BILLTO(rec_addr.ADDRESS_ID) loop
-- insert into ra_customers_interface_all

insert into ra_customers_interface_all
(
ORIG_SYSTEM_CUSTOMER_REF,
SITE_USE_CODE,
ORIG_SYSTEM_ADDRESS_REF,
PRIMARY_SITE_USE_FLAG,
LOCATION,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
COUNTRY,
INSERT_UPDATE_FLAG,
CUSTOMER_NAME,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
CUSTOMER_STATUS,
ORG_ID,
SITE_USE_TAX_CODE,
SITE_USE_ATTRIBUTE1,
SITE_USE_ATTRIBUTE2,
ADDRESS_ATTRIBUTE2
)
values
(v_cust_ref,
rec_site_bt.SITE_USE_CODE,
v_addr_ref,
rec_site_bt.PRIMARY_FLAG,
rec_site_bt.SITE_USE_CODE'_'v_addr_ref,
rec_addr.ADDRESS1,
rec_addr.ADDRESS2,
rec_addr.ADDRESS3,
rec_addr.ADDRESS4,
rec_addr.COUNTRY,
'I',--INSERT_UPDATE_FLAG,
rec_cust.CUSTOMER_NAME, --CUSTOMER_NAME,
0, --LAST_UPDATED_BY,
sysdate, --LAST_UPDATE_DATE,
0, --CREATED_BY,
sysdate, --CREATION_DATE,
'A', --CUSTOMER_STATUS,
v_ou_id,
rec_site_bt.TAX_CODE,
rec_site_bt.SITE_TERMS,
rec_site_bt.SITE_USE_ID,
rec_addr.ADDRESS_ID
);
end loop;
----
----
for rec_site_st in CUR_ADDRESS_SITE_SHIPTO(rec_addr.ADDRESS_ID) loop

Begin

if rec_site_st.BILL_TO_SITE_USE_ID is not NULL then

select ORIG_SYSTEM_ADDRESS_REF
into v_bill_addr
from ra_customers_interface_all
where SITE_USE_ATTRIBUTE2=rec_site_st.BILL_TO_SITE_USE_ID
and rownum=1;

end if;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('ERROR in the double algorithm.');
END;

insert into ra_customers_interface_all
(ORIG_SYSTEM_CUSTOMER_REF,
SITE_USE_CODE,
ORIG_SYSTEM_ADDRESS_REF,
PRIMARY_SITE_USE_FLAG,
LOCATION,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
COUNTRY,
INSERT_UPDATE_FLAG,
CUSTOMER_NAME,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
CUSTOMER_STATUS,
ORG_ID,
BILL_TO_ORIG_ADDRESS_REF,
SITE_USE_ATTRIBUTE2,
ADDRESS_ATTRIBUTE2
)
values
(v_cust_ref,
rec_site_st.SITE_USE_CODE,
v_addr_ref,
rec_site_st.PRIMARY_FLAG,
rec_site_st.SITE_USE_CODE'_'v_addr_ref,
rec_addr.ADDRESS1,
rec_addr.ADDRESS2,
rec_addr.ADDRESS3,
rec_addr.ADDRESS4,
rec_addr.COUNTRY,
'I',--INSERT_UPDATE_FLAG,
rec_cust.CUSTOMER_NAME, --CUSTOMER_NAME,
0, --LAST_UPDATED_BY,
sysdate, --LAST_UPDATE_DATE,
0, --CREATED_BY,
sysdate, --CREATION_DATE,
'A', --CUSTOMER_STATUS,
v_ou_id,
v_bill_addr,
rec_site_st.SITE_USE_ID,
rec_addr.ADDRESS_ID
);
end loop;
----
v_addr_seq:=v_addr_seq+1;
end loop;
------
v_cust_seq:=v_cust_seq+1;
end loop;
--------
COMMIT;
dbms_output.put_line('End of Program');
Exception When others then
DBMS_OUTPUT.PUT_LINE('ERROR: 'v_debug);
END;
/