📄 kenan_get_data_pd.pck
字号:
create or replace package kenan_get_data_pd is
procedure GET_SRC_DISCOUNT_KENAN(in_string varchar2); --discount
procedure GET_src_PROD_KENAN(in_string varchar2); --product
procedure GET_SRC_CONTRACT_ASSIGNMENT_HQ(in_string varchar2); --hq
procedure GET_SRC_CAH_EXT_DATA(in_string varchar2); --hq
procedure GET_SRC_INR_BATCH_NRC(in_string varchar2); --nrc
procedure GET_SRC_CAH_EXT_DATA_PATCH(in_string varchar2); ---hq
/* procedure GET_data(in_string varchar2);*/
end;
/
create or replace package body kenan_get_data_pd is
procedure GET_SRC_DISCOUNT_KENAN(in_string varchar2) is
ls_row_id number;
ls_cnt number;
ls_count number;
ls_wrong number;
cursor cur is
select * from SRC_DISCOUNT_KENAN_upload;
begin
execute immediate 'alter session set nls_date_format =''yyyymmddhh24miss''';
IF UPPER(IN_STRING) <> UPPER('SRC_DISCOUNT_KENAN') THEN
insert into get_data_log
(log_key, log_type, log_string, log_date, log_proc, memo)
values
(seq_get_data_log.nextval,
'wrong',
'参数传入错误',
sysdate,
in_string,
'');
commit;
else
select count(*)
into ls_count
from SRC_DISCOUNT_KENAN
where rownum = 1;
if ls_count = 1 then
insert into get_data_log
(log_key, log_type, log_string, log_date, log_proc, memo)
values
(seq_get_data_log.nextval,
'wrong',
in_string || '非空！无法执行insert！',
sysdate,
'get_' || in_string,
'');
commit;
else
ls_wrong := 0;
ls_row_id := 0;
ls_cnt := 0;
for rec in cur loop
begin
ls_row_id := ls_row_id + 1;
ls_cnt := ls_cnt + 1;
insert into SRC_DISCOUNT_KENAN
(CUST_ID,
ACCT_NBR_97,
ACCT_NBR_BILL,
SERV_ID,
SERV_NUM,
group_no,
PROD_REF_NO,
PROD_NAME,
BILLING_TYPE_ID,
BILLING_TYPE_NAME,
discount_id,
discount_type,
discount_name,
disct_method,
x,
y,
w,
z,
v,
START_DT,
END_DT,
SRC_ID,
SRC_TAB,
SRC_KEY1,
SRC_KEY2,
SRC_LAST_UPDATED,
SRC_FILESET,
SRC_UPDATE_COUNT,
SERVER_ID,
SERVER_SID,
SERVER_USER,
IS_PROCESSED,
SIB_PROCESS_STATUS,
KENAN_DISCOUNT_NAME,
KENAN_DISCOUNT_ID,
SIEBEL_PRODUCT_NAME,
SIEBEL_PRODUCT_ID,
ROW_ID,
KENAN_CPT_INST_ID,
DISCOUNT_REF_NUM,
DISCT_TRACKING_ID,
ROW_ID_2,
KENAN_PKG_INST_ID,
X6,
X7,
x8,
x9,
SRC_KEY3,
SIEBEL_PRODUCT_ID_2,
SEQUENCE_ID,
SERV_PRODUCT_ID,
PAR_SEQUENCE_ID,
PROD_SERV_ID,
is_sbu,
sbu_acct_nbr_97,
BILL_FLAG,
DISCOUNT_SOURCE)
values
(trim(rec.CUST_ID),
trim(rec.ACCT_NBR_97),
trim(rec.ACCT_NBR_BILL),
trim(rec.SERV_ID),
trim(rec.SERV_NUM),
trim(rec.group_no),
trim(rec.PROD_REF_NO),
trim(rec.PROD_NAME),
trim(rec.BILLING_TYPE_ID),
trim(rec.BILLING_TYPE_NAME),
trim(rec.discount_id),
trim(rec.discount_type),
trim(rec.discount_name),
trim(rec.disct_method),
trim(rec.x),
trim(rec.y),
trim(rec.w),
trim(rec.z),
trim(rec.v),
trunc(rec.START_DT),
trunc(rec.END_DT),
trim(rec.SRC_ID),
trim(rec.SRC_TAB),
trim(rec.SRC_KEY1),
trim(rec.SRC_KEY2),
trim(rec.SRC_LAST_UPDATED),
trim(rec.SRC_FILESET),
trim(rec.SRC_UPDATE_COUNT),
trim(rec.SERVER_ID),
trim(rec.SERVER_SID),
trim(rec.SERVER_USER),
'N', --IS_PROCESSED
'N', --SIB_PROCESS_STATUS
trim(rec.KENAN_DISCOUNT_NAME),
trim(rec.KENAN_DISCOUNT_ID),
trim(rec.SIEBEL_PRODUCT_NAME),
trim(rec.SIEBEL_PRODUCT_ID),
ls_row_id, --ROW_ID
trim(rec.KENAN_CPT_INST_ID),
trim(rec.DISCOUNT_REF_NUM),
trim(rec.DISCT_TRACKING_ID),
trim(rec.ROW_ID_2),
trim(rec.KENAN_PKG_INST_ID),
trim(rec.X6),
trim(rec.X7),
trim(rec.x8),
trim(rec.x9),
trim(rec.SRC_KEY3),
trim(rec.SIEBEL_PRODUCT_ID_2),
trim(rec.SEQUENCE_ID),
trim(rec.SERV_PRODUCT_ID),
trim(rec.PAR_SEQUENCE_ID),
trim(rec.PROD_SERV_ID),
trim(rec.is_sbu),
trim(rec.sbu_acct_nbr_97),
trim(rec.BILL_FLAG),
trim(rec.DISCOUNT_SOURCE)
);
if mod(ls_cnt, 10000) = 0 then
commit;
end if;
exception
when others then
ls_wrong := ls_wrong + 1;
insert into SRC_DISCOUNT_KENAN_err
(CUST_ID,
ACCT_NBR_97,
ACCT_NBR_BILL,
SERV_ID,
SERV_NUM,
group_no,
PROD_REF_NO,
PROD_NAME,
BILLING_TYPE_ID,
BILLING_TYPE_NAME,
discount_id,
discount_type,
discount_name,
disct_method,
x,
y,
w,
z,
v,
START_DT,
END_DT,
SRC_ID,
SRC_TAB,
SRC_KEY1,
SRC_KEY2,
SRC_LAST_UPDATED,
SRC_FILESET,
SRC_UPDATE_COUNT,
SERVER_ID,
SERVER_SID,
SERVER_USER,
IS_PROCESSED,
SIB_PROCESS_STATUS,
KENAN_DISCOUNT_NAME,
KENAN_DISCOUNT_ID,
SIEBEL_PRODUCT_NAME,
SIEBEL_PRODUCT_ID,
ROW_ID,
KENAN_CPT_INST_ID,
DISCOUNT_REF_NUM,
DISCT_TRACKING_ID,
ROW_ID_2,
KENAN_PKG_INST_ID,
X6,
X7,
x8,
x9,
SRC_KEY3,
SIEBEL_PRODUCT_ID_2,
SEQUENCE_ID,
SERV_PRODUCT_ID,
PAR_SEQUENCE_ID,
PROD_SERV_ID,
is_sbu,
sbu_acct_nbr_97,
BILL_FLAG,
DISCOUNT_SOURCE)
values
(trim(rec.CUST_ID),
trim(rec.ACCT_NBR_97),
trim(rec.ACCT_NBR_BILL),
trim(rec.SERV_ID),
trim(rec.SERV_NUM),
trim(rec.group_no),
trim(rec.PROD_REF_NO),
trim(rec.PROD_NAME),
trim(rec.BILLING_TYPE_ID),
trim(rec.BILLING_TYPE_NAME),
trim(rec.discount_id),
trim(rec.discount_type),
trim(rec.discount_name),
trim(rec.disct_method),
trim(rec.x),
trim(rec.y),
trim(rec.w),
trim(rec.z),
trim(rec.v),
trunc(rec.START_DT),
trunc(rec.END_DT),
trim(rec.SRC_ID),
trim(rec.SRC_TAB),
trim(rec.SRC_KEY1),
trim(rec.SRC_KEY2),
trim(rec.SRC_LAST_UPDATED),
trim(rec.SRC_FILESET),
trim(rec.SRC_UPDATE_COUNT),
trim(rec.SERVER_ID),
trim(rec.SERVER_SID),
trim(rec.SERVER_USER),
'N', --IS_PROCESSED
'N', --SIB_PROCESS_STATUS
trim(rec.KENAN_DISCOUNT_NAME),
trim(rec.KENAN_DISCOUNT_ID),
trim(rec.SIEBEL_PRODUCT_NAME),
trim(rec.SIEBEL_PRODUCT_ID),
ls_row_id, --ROW_ID
trim(rec.KENAN_CPT_INST_ID),
trim(rec.DISCOUNT_REF_NUM),
trim(rec.DISCT_TRACKING_ID),
trim(rec.ROW_ID_2),
trim(rec.KENAN_PKG_INST_ID),
trim(rec.X6),
trim(rec.X7),
trim(rec.x8),
trim(rec.x9),
trim(rec.SRC_KEY3),
trim(rec.SIEBEL_PRODUCT_ID_2),
trim(rec.SEQUENCE_ID),
trim(rec.SERV_PRODUCT_ID),
trim(rec.PAR_SEQUENCE_ID),
trim(rec.PROD_SERV_ID),
trim(rec.is_sbu),
trim(rec.sbu_acct_nbr_97),
trim(rec.BILL_FLAG),
trim(rec.DISCOUNT_SOURCE)
);
end;
end loop;
commit;
insert into get_data_log
(log_key, log_type, log_string, log_date, log_proc, memo)
values
(seq_get_data_log.nextval,
'完成',
'总共完成数量 ' || ls_cnt,
sysdate,
'get_' || in_string,
'错误数量 ' || ls_wrong);
commit;
end if;
end if;
commit;
end;
procedure GET_src_PROD_KENAN(in_string varchar2) is
ls_row_id number;
ls_cnt number;
ls_count number;
ls_wrong number;
v_ErrCode varchar2(20 char);
v_ErrMsg varchar2(1000 char);
cursor cur is
select * from src_PROD_KENAN_upload;
begin
execute immediate 'alter session set nls_date_format =''yyyymmddhh24miss''';
IF UPPER(IN_STRING) <> UPPER('SRC_PROD_KENAN') THEN
insert into get_data_log
(log_key, log_type, log_string, log_date, log_proc, memo)
values
(seq_get_data_log.nextval,
'wrong',
'参数传入错误',
sysdate,
in_string,
'');
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -