Siebel中的水电费导入EBS程序
--Siebel 水电费导入程序
PROCEDURE cux_opark_ar_fee(errbuf out varchar2,
retcode out varchar2,
p_period_name varchar2) as
cux_year number;
cux_month number;
cux_account_date date;
cux_count number;
cux_count_ebs_ar number;
cux_ebs_ou_count number;
cux_ebs_party_id number;
cux_i int;
cux_cust_number varchar2(20);
cux_cust_name varchar(100);
cux_bill_address_id varchar2(20);
cux_bill_customer_id varchar2(20);
cux_errorflag varchar2(4);
cux_error_descrption varchar(500);
requireDate date;
TYPE cux_ebs_rowids_type IS TABLE OF varchar2(30);
cux_ebs_rowids cux_ebs_rowids_type;
fee_income cux_gl_fee_income_interface%rowtype;
cursor fee_incom_cy_cur is
select *
from cux_gl_fee_income_interface cgfi
where cgfi.X_YEAR = cux_year
and cgfi.X_MONTH = cux_month
and cgfi.X_Description = '智慧大厦'; --智慧大厦
BEGIN
cux_ebs_rowids := cux_ebs_rowids_type(NULL);
cux_errorflag := 'N';
cux_i := 0;
select to_date(p_period_name, 'yyyy-mm') into requireDate from dual;
Dbms_Output.put_line(requireDate);
select to_char(requireDate, 'yyyy') into cux_year from dual;
select to_char(requireDate, 'mm') into cux_month from dual;
select to_date(to_char(last_day(requireDate), 'yyyy-mm-dd'),
'yyyy-mm-dd')
into cux_account_date
from dual;
/*X_OP_TYPE_FLAG = 0 产业公司智慧大厦*/
select count(*)
into cux_count
from cux_gl_fee_mag_interface cgf
where cgf.X_OP_MONTH = cux_month
and cgf.X_OP_YEAR = cux_year
and cgf.X_OP_TYPE_FLAG = 0
--0表示智慧大厦水电费 1表示人才公寓水电费
and cgf.X_OP_STATUS_FLAG >= 1;
if cux_count = 1 then
for fee_income in fee_incom_cy_cur loop
if fee_income.x_op_buding_ready_flag <> 'Y' then
select cust_number, cust_name
into cux_cust_number, cux_cust_name
from cux_cust_info
where cust_id = fee_income.x_accountid;
Dbms_Output.put_line(cux_cust_name || cux_cust_number);
select count(0)
into cux_ebs_ou_count
from hz_parties a,
hz_cust_accounts b,
hz_cust_acct_sites_all c,
hz_cust_site_uses_all d,
hz_party_sites e,
hz_locations f
where a.party_id = b.party_id
and c.cust_account_id = b.cust_account_id
and c.cust_acct_site_id = d.cust_acct_site_id
and a.party_id = e.party_id
and e.location_id = f.location_id
and f.address2 = cux_cust_number
and d.site_use_code = 'BILL_TO'
and c.org_id = 103;
if cux_ebs_ou_count > 0 then
if cux_errorflag = 'Y' then
CONTINUE;
end if;
select c.cust_acct_site_id, b.cust_account_id
into cux_bill_address_id, cux_bill_customer_id
from hz_parties a,
hz_cust_accounts b,
hz_cust_acct_sites_all c,
hz_cust_site_uses_all d,
hz_party_sites e,
hz_locations f
where a.party_id = b.party_id
and c.cust_account_id = b.cust_account_id
and c.cust_acct_site_id = d.cust_acct_site_id
and a.party_id = e.party_id
and e.location_id = f.location_id
and f.address2 = cux_cust_number
and d.site_use_code = 'BILL_TO'
and c.org_id = 103
and rownum <= 1;
if (NVL(fee_income.X_WATER_FEE, 0)) > 0 then
INSERT INTO ra_interface_lines_all
(interface_line_context, --line transaction flexfield
interface_line_attribute1,
interface_line_attribute2,
interface_line_attribute8,
amount,
batch_source_name,
conversion_rate,
conversion_type,
currency_code,
cust_trx_type_id,
description,
gl_date,
line_type,
orig_system_bill_address_id,
orig_system_bill_customer_id,
quantity,
unit_selling_price,
term_id,
taxable_flag,
amount_includes_tax_flag,
set_of_books_id,
org_id)
Values
('TIP',
seq_siebel_lines.nextval,
seq_siebel_lines.nextval,
1,
round(NVL(fee_income.X_WATER_FEE, 0), 2),
'TIP BATCH SOURCE',
1,
'User',
'CNY',
1081,
p_period_name || '的水费',
cux_account_date,
'LINE',
cux_bill_address_id,
cux_bill_customer_id,
1,
round(NVL(fee_income.X_WATER_FEE, 0), 2),
5,
'N',
'N',
2021,
103);
end if;
if NVL(fee_income.X_ELECTRIC_FEE, 0) +
NVL(fee_income.X_AIR_CONDITION, 0) > 0 then
INSERT INTO ra_interface_lines_all
(interface_line_context, --line transaction flexfield
interface_line_attribute1,
interface_line_attribute2,
interface_line_attribute8,
amount,
batch_source_name,
conversion_rate,
conversion_type,
currency_code,
cust_trx_type_id,
description,
gl_date,
line_type,
orig_system_bill_address_id,
orig_system_bill_customer_id,
quantity,
unit_selling_price,
term_id,
taxable_flag,
amount_includes_tax_flag,
set_of_books_id,
org_id)
VALUES
('TIP',
seq_siebel_lines.nextval,
seq_siebel_lines.nextval,
1,
round(NVL(fee_income.X_ELECTRIC_FEE, 0) +
NVL(fee_income.X_AIR_CONDITION, 0),
2),
'TIP BATCH SOURCE',
1,
'User',
'CNY',
1081,
p_period_name || '的电费',
cux_account_date,
'LINE',
cux_bill_address_id,
cux_bill_customer_id,
1,
round(NVL(fee_income.X_ELECTRIC_FEE, 0) +
NVL(fee_income.X_AIR_CONDITION, 0),
2),
5,
'N',
'N',
2021,
103);
end if;
/* 穿过的数据更改标记*/
/* update siebel.cx_fee_income@OPARK2SIEBEL
set X_OP_BUDING_READY_FLAG = 'Y'
WHERE row_id = fee_income.row_id;*/
if cux_i > 0 then
cux_ebs_rowids.extend;
end if;
cux_i := cux_i + 1;
cux_ebs_rowids(cux_i) := fee_income.row_id;
else
if cux_errorflag = 'N' then
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'导入水电费用失败:请在ebs中更新以下指定的客户信息后重新运行此请求！');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '客户编码 客户名称');
end if;
cux_errorflag := 'Y';
cux_error_descrption := '没有对应到客户编码位于EBS客户记录中：ID：' ||
cux_cust_number || '名称：' ||
cux_cust_name;
DBMS_OUTPUT.PUT_LINE('没有对应到客户编码位于EBS客户记录中：ID：' ||
cux_cust_number || '名称：' || cux_cust_name);
fnd_file.PUT_LINE(fnd_file.OUTPUT,
cux_cust_number || ' ' || cux_cust_name);
end if;
end if;
end loop;
if cux_errorflag = 'Y' then
/*for i in 1 .. cux_ebs_rowids.count loop
DBMS_output.put_line('YYYYY:'||cux_ebs_rowids(i));
end loop;*/
errbuf := cux_error_descrption;
rollback;
raise_application_error(-20001, 'Customer information missing!');
else
/*
删除传过去数据金额为0的*/
--FOR i IN 1 .. courses.COUNT LOOP ...
for i in 1 .. cux_ebs_rowids.count loop
DBMS_output.put_line('OKOK:' || cux_ebs_rowids(i));
update siebel.cx_fee_income@OPARK2SIEBEL
set X_OP_BUDING_READY_FLAG = 'Y'
WHERE row_id = cux_ebs_rowids(i);
end loop;
delete ra_interface_lines_all
where amount <= 0
and interface_status is null;
commit;
fnd_file.PUT_LINE(fnd_file.OUTPUT, cux_i || '条记录导入成功。');
end if;
else
fnd_file.PUT_LINE(fnd_file.OUTPUT, '没有需要导入的水电费在siebel中');
end if;
end;
PROCEDURE cux_opark_ar_fee(errbuf out varchar2,
retcode out varchar2,
p_period_name varchar2) as
cux_year number;
cux_month number;
cux_account_date date;
cux_count number;
cux_count_ebs_ar number;
cux_ebs_ou_count number;
cux_ebs_party_id number;
cux_i int;
cux_cust_number varchar2(20);
cux_cust_name varchar(100);
cux_bill_address_id varchar2(20);
cux_bill_customer_id varchar2(20);
cux_errorflag varchar2(4);
cux_error_descrption varchar(500);
requireDate date;
TYPE cux_ebs_rowids_type IS TABLE OF varchar2(30);
cux_ebs_rowids cux_ebs_rowids_type;
fee_income cux_gl_fee_income_interface%rowtype;
cursor fee_incom_cy_cur is
select *
from cux_gl_fee_income_interface cgfi
where cgfi.X_YEAR = cux_year
and cgfi.X_MONTH = cux_month
and cgfi.X_Description = '智慧大厦'; --智慧大厦
BEGIN
cux_ebs_rowids := cux_ebs_rowids_type(NULL);
cux_errorflag := 'N';
cux_i := 0;
select to_date(p_period_name, 'yyyy-mm') into requireDate from dual;
Dbms_Output.put_line(requireDate);
select to_char(requireDate, 'yyyy') into cux_year from dual;
select to_char(requireDate, 'mm') into cux_month from dual;
select to_date(to_char(last_day(requireDate), 'yyyy-mm-dd'),
'yyyy-mm-dd')
into cux_account_date
from dual;
/*X_OP_TYPE_FLAG = 0 产业公司智慧大厦*/
select count(*)
into cux_count
from cux_gl_fee_mag_interface cgf
where cgf.X_OP_MONTH = cux_month
and cgf.X_OP_YEAR = cux_year
and cgf.X_OP_TYPE_FLAG = 0
--0表示智慧大厦水电费 1表示人才公寓水电费
and cgf.X_OP_STATUS_FLAG >= 1;
if cux_count = 1 then
for fee_income in fee_incom_cy_cur loop
if fee_income.x_op_buding_ready_flag <> 'Y' then
select cust_number, cust_name
into cux_cust_number, cux_cust_name
from cux_cust_info
where cust_id = fee_income.x_accountid;
Dbms_Output.put_line(cux_cust_name || cux_cust_number);
select count(0)
into cux_ebs_ou_count
from hz_parties a,
hz_cust_accounts b,
hz_cust_acct_sites_all c,
hz_cust_site_uses_all d,
hz_party_sites e,
hz_locations f
where a.party_id = b.party_id
and c.cust_account_id = b.cust_account_id
and c.cust_acct_site_id = d.cust_acct_site_id
and a.party_id = e.party_id
and e.location_id = f.location_id
and f.address2 = cux_cust_number
and d.site_use_code = 'BILL_TO'
and c.org_id = 103;
if cux_ebs_ou_count > 0 then
if cux_errorflag = 'Y' then
CONTINUE;
end if;
select c.cust_acct_site_id, b.cust_account_id
into cux_bill_address_id, cux_bill_customer_id
from hz_parties a,
hz_cust_accounts b,
hz_cust_acct_sites_all c,
hz_cust_site_uses_all d,
hz_party_sites e,
hz_locations f
where a.party_id = b.party_id
and c.cust_account_id = b.cust_account_id
and c.cust_acct_site_id = d.cust_acct_site_id
and a.party_id = e.party_id
and e.location_id = f.location_id
and f.address2 = cux_cust_number
and d.site_use_code = 'BILL_TO'
and c.org_id = 103
and rownum <= 1;
if (NVL(fee_income.X_WATER_FEE, 0)) > 0 then
INSERT INTO ra_interface_lines_all
(interface_line_context, --line transaction flexfield
interface_line_attribute1,
interface_line_attribute2,
interface_line_attribute8,
amount,
batch_source_name,
conversion_rate,
conversion_type,
currency_code,
cust_trx_type_id,
description,
gl_date,
line_type,
orig_system_bill_address_id,
orig_system_bill_customer_id,
quantity,
unit_selling_price,
term_id,
taxable_flag,
amount_includes_tax_flag,
set_of_books_id,
org_id)
Values
('TIP',
seq_siebel_lines.nextval,
seq_siebel_lines.nextval,
1,
round(NVL(fee_income.X_WATER_FEE, 0), 2),
'TIP BATCH SOURCE',
1,
'User',
'CNY',
1081,
p_period_name || '的水费',
cux_account_date,
'LINE',
cux_bill_address_id,
cux_bill_customer_id,
1,
round(NVL(fee_income.X_WATER_FEE, 0), 2),
5,
'N',
'N',
2021,
103);
end if;
if NVL(fee_income.X_ELECTRIC_FEE, 0) +
NVL(fee_income.X_AIR_CONDITION, 0) > 0 then
INSERT INTO ra_interface_lines_all
(interface_line_context, --line transaction flexfield
interface_line_attribute1,
interface_line_attribute2,
interface_line_attribute8,
amount,
batch_source_name,
conversion_rate,
conversion_type,
currency_code,
cust_trx_type_id,
description,
gl_date,
line_type,
orig_system_bill_address_id,
orig_system_bill_customer_id,
quantity,
unit_selling_price,
term_id,
taxable_flag,
amount_includes_tax_flag,
set_of_books_id,
org_id)
VALUES
('TIP',
seq_siebel_lines.nextval,
seq_siebel_lines.nextval,
1,
round(NVL(fee_income.X_ELECTRIC_FEE, 0) +
NVL(fee_income.X_AIR_CONDITION, 0),
2),
'TIP BATCH SOURCE',
1,
'User',
'CNY',
1081,
p_period_name || '的电费',
cux_account_date,
'LINE',
cux_bill_address_id,
cux_bill_customer_id,
1,
round(NVL(fee_income.X_ELECTRIC_FEE, 0) +
NVL(fee_income.X_AIR_CONDITION, 0),
2),
5,
'N',
'N',
2021,
103);
end if;
/* 穿过的数据更改标记*/
/* update siebel.cx_fee_income@OPARK2SIEBEL
set X_OP_BUDING_READY_FLAG = 'Y'
WHERE row_id = fee_income.row_id;*/
if cux_i > 0 then
cux_ebs_rowids.extend;
end if;
cux_i := cux_i + 1;
cux_ebs_rowids(cux_i) := fee_income.row_id;
else
if cux_errorflag = 'N' then
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'导入水电费用失败:请在ebs中更新以下指定的客户信息后重新运行此请求！');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '客户编码 客户名称');
end if;
cux_errorflag := 'Y';
cux_error_descrption := '没有对应到客户编码位于EBS客户记录中：ID：' ||
cux_cust_number || '名称：' ||
cux_cust_name;
DBMS_OUTPUT.PUT_LINE('没有对应到客户编码位于EBS客户记录中：ID：' ||
cux_cust_number || '名称：' || cux_cust_name);
fnd_file.PUT_LINE(fnd_file.OUTPUT,
cux_cust_number || ' ' || cux_cust_name);
end if;
end if;
end loop;
if cux_errorflag = 'Y' then
/*for i in 1 .. cux_ebs_rowids.count loop
DBMS_output.put_line('YYYYY:'||cux_ebs_rowids(i));
end loop;*/
errbuf := cux_error_descrption;
rollback;
raise_application_error(-20001, 'Customer information missing!');
else
/*
删除传过去数据金额为0的*/
--FOR i IN 1 .. courses.COUNT LOOP ...
for i in 1 .. cux_ebs_rowids.count loop
DBMS_output.put_line('OKOK:' || cux_ebs_rowids(i));
update siebel.cx_fee_income@OPARK2SIEBEL
set X_OP_BUDING_READY_FLAG = 'Y'
WHERE row_id = cux_ebs_rowids(i);
end loop;
delete ra_interface_lines_all
where amount <= 0
and interface_status is null;
commit;
fnd_file.PUT_LINE(fnd_file.OUTPUT, cux_i || '条记录导入成功。');
end if;
else
fnd_file.PUT_LINE(fnd_file.OUTPUT, '没有需要导入的水电费在siebel中');
end if;
end;
