📄 pkg_ken_etl.pck
字号:
if v_FailRecord <> 0 then
pkg_ken_util.prc_upd_lps(v_ProcessName,
v_SucceedRecord,
v_FailRecord,
'SP');
else
pkg_ken_util.prc_upd_lps(v_ProcessName,
v_SucceedRecord,
v_FailRecord,
'CM');
end if;
commit;
close c_ACCT;
EXCEPTION
WHEN OTHERS THEN
close c_ACCT;
pkg_ken_util.prc_handle_exception(v_ProcessName, p_MODE, v_RowID);
END PRC_T_BA;
/**************** SI ****************************/
PROCEDURE PRC_T_SI(p_CITY IN VARCHAR2,
p_PROCESSNUM IN NUMBER,
p_CURRENTNUM IN NUMBER,
p_MODE IN VARCHAR2) AS
/*constant variable*/
c_CommitCounter CONSTANT NUMBER := 5000;
c_Domain constant varchar2(3) := 'SI';
/* related to workflow*/
l_ProcessName varchar2(30) := 'PRC_T_' || c_Domain || '_' || p_CITY || '_' ||
p_PROCESSNUM || '_' || p_CURRENTNUM;
l_TotalRecord LOG_PROCESS_STATUS.Total_Rec%Type;
l_SucceedRecord LOG_PROCESS_STATUS.succeed_rec%TYPE;
l_FailRecord LOG_PROCESS_STATUS.fail_rec%TYPE;
l_RecordStart NUMBER := pkg_ken_util.fun_get_start_id(l_ProcessName);
l_RecordEnd NUMBER := pkg_ken_util.fun_get_end_id(l_ProcessName);
v_RowId src_serv.row_id%type;
/* variable related with kenan table */
l_ParertID CMF.parent_id%TYPE;
l_SubscrNo SERVICE.subscr_no%TYPE;
l_SeqServiceViewID SERVICE.view_id%TYPE;
l_ServiceViewID SERVICE.view_id%TYPE;
l_ServiceID2 CUSTOMER_ID_EQUIP_MAP.external_id%TYPE;
l_ServiceNum SRC_SERV_NUM.SERV_NUM%TYPE;
l_ServiceNum972 SRC_SERV_NUM.SERV_NUM%TYPE;
l_UnEncodeServiceNum SRC_SERV_NUM.Serv_Num%TYPE;
l_EncodeServiceNum SRC_SERV_NUM.Serv_Num%TYPE;
l_status SERVICE_STATUS.STATUS_ID%TYPE;
l_StatusReason SERVICE_STATUS.Status_Reason_Id%Type;
l_ClassServiceCode smallint;
l_ECC smallint;
l_ETC smallint;
l_RateClass SERVICE.Rate_Class%TYPE;
l_SuspendDefault2 SRC_serv.Suspend_LOSS%Type;
l_SuspendExtend cfg_lov_map.ken_code%TYPE;
l_ServCustID src_serv.serv_cust_id%TYPE;
l_EncodeOldServNum src_serv_chg_num.old_serv_num%type;
l_CurOldServNum src_serv_chg_num.old_serv_num%type;
l_CurNewServNum src_serv_chg_num.new_serv_num%type;
l_CurChgDate date;
l_ChgNumTime smallint;
l_SeqMapViewID EXTERNAL_ID_EQUIP_MAP_VIEW.VIEW_ID%TYPE;
l_MapViewID CUSTOMER_ID_EQUIP_MAP_VIEW.VIEW_ID%TYPE;
l_ExtActiveDt_Chg date;
l_ExtInActiveDt_Chg date;
l_NextNewServNum src_serv_chg_num.new_serv_num%type;
l_IsCurrent customer_id_equip_map.is_current%type;
l_InactiveDate service_status.inactive_dt%type;
indx number(2);
l_ServiceActiveDt date;
l_ServiceInactiveDt date;
l_NextChgDt date;
l_TempActiveDt date;
l_CountExternalType number;
l_ServId src_serv_num.serv_id%type;
l_ServNumType src_serv_num.serv_num_type%type;
l_ServRevCostCtr number;
/*variable related to MSA*/
l_ServerID EXTERNAL_ID_ACCT_MAP.server_id%TYPE; --server id of customer db
l_DBUSER CFG_KEN_MSA.MIG_SCHEMA%TYPE;
/* declare a cursor for src_serv
*/
CURSOR c_SI IS
SELECT *
FROM SRC_SERV
WHERE is_processed = p_MODE
AND (src_id = p_CITY || '97' or src_id = p_CITY || 'Bl')
AND (ROW_ID >= l_RecordStart AND ROW_ID <= l_RecordEnd);
rec_si c_SI%rowtype;
BEGIN
pkg_ken_util.PRC_STREAM_INFO(l_ProcessName,
l_TotalRecord,
l_SucceedRecord,
l_FailRecord);
OPEN c_SI;
<<NEXTLOOP01555>>
LOOP
BEGIN
FETCH c_SI
INTO rec_si;
EXIT WHEN c_SI%NOTFOUND;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1555 THEN
ROLLBACK;
CLOSE c_SI;
pkg_ken_util.PRC_STREAM_INFO(l_ProcessName,
l_TotalRecord,
l_SucceedRecord,
l_FailRecord);
OPEN c_SI;
GOTO NEXTLOOP01555;
ELSE
RAISE;
END IF;
END;
begin
v_RowId := rec_si.row_id;
pkg_ken_migapi.prc_clear_si;
indx := 1;
l_ServiceActiveDt := trunc(rec_si.START_DT);
l_ServiceInactiveDt := trunc(rec_si.END_DT);
l_Status := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_si.SRC_ID,
'STATUS',
rec_si.STATUS));
l_RateClass := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_si.SRC_ID,
'SERV_AREA',
rec_si.SERV_AREA));
l_ServRevCostCtr := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_si.SRC_ID,
'SERV_REV_COST_CTR',
rec_SI.SERV_REV_COST_CTR));
pkg_ken_util.PRC_PARENT_INFO('SRC_ACCT_STATUS',
rec_si.SRC_ID,
rec_si.ACCT_NBR_97,
l_ParertID,
l_ServerID,
l_DBUSER);
execute immediate 'select ' || PKG_KEN_UTIL.g_MIGCAT_Schema ||
'subscriber_id_seq_num_seq.nextval from DUAL'
into l_SubscrNo;
execute immediate 'select ' || l_DBUSER ||
'service_view_seq.nextval from DUAL'
into l_SeqServiceViewID;
l_ServiceViewID := l_SeqServiceViewID * 1000 + l_ServerID;
/*************** get config information ******************************************/
begin
EXECUTE IMMEDIATE 'SELECT class_of_service_code, equip_class_code, equip_type_code FROM ' ||
PKG_KEN_UTIL.g_MIGADM_Schema ||
'EMF_CONFIGURATION WHERE emf_config_id = :1'
INTO l_ClassServiceCode, l_ECC, l_ETC
USING TO_NUMBER(rec_si.EQUIP_TYPE);
exception
when no_data_found or too_many_rows then
raise_application_error(-20301,
'EMF_CONFIGURATION for SI is not correct');
end;
select DECODE(rec_si.SUSPEND_DEFAULT,
'正常',
'N',
'单向',
'S',
'双向',
'D')
into l_SuspendDefault2
from dual;
l_SuspendExtend := rec_si.SUSPEND_INITIATIVE ||
rec_si.SUSPEND_PECCANCY || rec_si.SUSPEND_LOSS ||
l_SuspendDefault2 || 'N';
if l_status = 1 then
if rec_si.SUSPEND_INITIATIVE = 'Y' then
l_status := 3;
l_StatusReason := 4;
if l_ServiceActiveDt = trunc(rec_si.cr_sus_dt) then
l_InactiveDate := add_months(l_ServiceActiveDt + 1 / 24 / 60,
0);
else
l_InactiveDate := trunc(rec_si.cr_sus_dt);
end if;
else
l_status := 1;
l_StatusReason := 1;
l_InactiveDate := null;
end if;
elsif l_status = 2 then
if l_ServiceActiveDt = l_ServiceInactiveDt then
l_InactiveDate := add_months(l_ServiceInactiveDt + 1 / 24 / 60,
0);
else
l_InactiveDate := l_ServiceInactiveDt;
end if;
if l_SuspendExtend = 'NNNNN' then
l_StatusReason := 6;
else
l_StatusReason := -88;
end if;
end if;
/* numbering service Number*/
l_UnEncodeServiceNum := rec_si.SERV_NUM;
l_EncodeServiceNum := pkg_ken_util.FUN_ENCODE_SERVNUM(rec_si.SERV_NUM,
TO_NUMBER(rec_si.EQUIP_TYPE),
p_CITY);
/****************** SERVICE , VIEW , KEY ***********************************************/
pkg_ken_migapi.t_sv(1).view_id := l_ServiceViewID;
pkg_ken_migapi.t_sv(1).view_status := 2;
pkg_ken_migapi.t_sv(1).view_created_dt := rec_si.START_DT;
pkg_ken_migapi.t_sv(1).view_effective_dt := rec_si.START_DT;
pkg_ken_migapi.t_sv(1).intended_view_effective_dt := rec_si.START_DT;
pkg_ken_migapi.t_sv(1).subscr_no := l_SubScrNo;
pkg_ken_migapi.t_sv(1).subscr_no_resets := 0;
pkg_ken_migapi.t_sv(1).chg_dt := rec_si.START_DT;
pkg_ken_migapi.t_sv(1).chg_who := pkg_ken_util.g_Chg_Who;
pkg_ken_migapi.t_sv(1).currency_code := 14;
pkg_ken_migapi.t_sv(1).display_external_id_type := TO_NUMBER(rec_si.SERV_NUM_TYPE);
pkg_ken_migapi.t_sv(1).emf_config_id := TO_NUMBER(rec_si.EQUIP_TYPE);
pkg_ken_migapi.t_sv(1).exrate_class := 1;
pkg_ken_migapi.t_sv(1).no_bill := 0;
pkg_ken_migapi.t_sv(1).parent_account_no := l_ParertID;
pkg_ken_migapi.t_sv(1).privacy_level := 0;
pkg_ken_migapi.t_sv(1).rate_class := l_RateClass;
pkg_ken_migapi.t_sv(1).rev_rcv_cost_ctr := l_ServRevCostCtr;
pkg_ken_migapi.t_sv(1).service_lname := rec_si.CUST_NAME_1;
pkg_ken_migapi.t_sv(1).service_address1 := rec_si.SERV_ADDR;
pkg_ken_migapi.t_sv(1).service_city := rec_si.SERV_ADDR_CITY;
pkg_ken_migapi.t_sv(1).service_county := rec_si.SERV_ADDR_COUNTY;
pkg_ken_migapi.t_sv(1).service_state := rec_si.SERV_ADDR_STATE;
pkg_ken_migapi.t_sv(1).service_zip := rec_si.SERV_ADDR_ZIPCODE;
pkg_ken_migapi.t_sv(1).service_phone := substrb(rec_si.SERV_PHONE,
1,
20);
pkg_ken_migapi.t_sv(1).service_country_code := 156;
pkg_ken_migapi.t_sv(1).service_geocode := '156001000000000';
pkg_ken_migapi.t_sv(1).is_prepaid := 0;
pkg_ken_migapi.t_sv(1).service_active_dt := l_ServiceActiveDt;
pkg_ken_migapi.t_sv(1).service_inactive_dt := l_ServiceInactiveDt;
pkg_ken_migapi.t_sv(1).b_service_state := rec_si.B_SERV_ADDR_STATE;
pkg_ken_migapi.t_sv(1).b_service_city := rec_si.B_SERV_ADDR_CITY;
pkg_ken_migapi.t_sv(1).b_service_county := rec_si.B_SERV_ADDR_COUNTY;
pkg_ken_migapi.t_sv(1).b_service_lname := NVL(rec_si.B_SERV_NAME,
'未知');
pkg_ken_migapi.t_sv(1).b_service_address1 := nvl(rec_si.B_SERV_ADDR,
'未知');
pkg_ken_migapi.t_sv(1).pop_units := rec_si.POP_UNITS;
pkg_ken_migapi.t_sv(1).service_franchise_tax_code := 1;
/******************** SERVICE_EXT_DATA *********************************************************/
pkg_ken_migapi.t_sed(1).view_id := l_ServiceViewID;
pkg_ken_migapi.t_sed(1).param_id := 2;
pkg_ken_migapi.t_sed(1).param_value := l_SuspendExtend;
pkg_ken_migapi.t_sed(1).param_datatype := 2;
--last updated
pkg_ken_migapi.t_sed(2).view_id := l_Servic⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -