Accelera Segments Oracle ETL

create table &&segments_registry_table_name (
	request_id varchar2(255),
	seg_out_tbl varchar2(255),
	seg_id varchar2(255),
	seg_tracking_code number,
	seg_status varchar2(255),
	seg_volume number,
	seg_cg_tracking_code number,
	seg_cg_volume number,
	seg_run_type number,
	seg_subject varchar2(255),
	seg_placeholders varchar2(2000),
	channel_code varchar2(255),
	created_by varchar2(255),
	created_on timestamp with local time zone,
	last_updated_by varchar2(255),
	last_updated_on timestamp with local time zone,
	row_version number
);

alter table &&segments_registry_table_name add constraint seg_reg_pk primary key (seg_tracking_code);
create or replace trigger biu_seg_reg
before insert or update on &&segments_registry_table_name
for each row
begin
	if inserting and :new.request_id is null then
	:new.request_id:=sys_guid();
	end if;
	if inserting then
	:new.created_by:=USER();
	:new.created_on:=localtimestamp;
	:new.row_version:=1;
	end if;
	if updating then
	:new.last_updated_by:=USER();
	:new.last_updated_on:=localtimestamp;
	:new.row_version:=nvl(:new.row_version,0)+1;
	end if;
	:new.seg_status:= trim(upper(:new.seg_status));
	:new.seg_subject:= trim(upper(:new.seg_subject));
	:new.seg_placeholders:= trim(upper(:new.seg_placeholders));
	:new.seg_out_tbl:= trim(upper(:new.seg_out_tbl));
	:new.channel_code:= trim(upper(:new.channel_code));
end;

/

create table &&log_table_name(
	seg_tracking_code number,
	program_name varchar2(255),
	program_type varchar2(255),
	step_name varchar2(255),
	stepn number,
	error_code varchar2(255),
	error_desc varchar2(255),
	row_version number,
	status varchar2(255),
	created_by varchar2(255),
	created_on timestamp with local time zone,
	last_updated_by varchar2(255),
	last_updated_on timestamp with local time zone
);

alter table &&log_table_name
add constraint logs_pn_ck check (upper(program_name) = program_name);
alter table &&log_table_name
add constraint logs_pt_ck check (upper(program_type) = program_type);
alter table &&log_table_name
add constraint logs_sn_ck check (upper(step_name) = step_name);
alter table &&log_table_name
add constraint logs_status_ck check (upper(status) = status);
create or replace trigger biu_logs
before insert or update on &&log_table_name
for each row
begin
	if inserting then
	:new.created_by:=USER();
	:new.created_on:=localtimestamp;
	:new.row_version:=1;
	end if;
	if updating then
	:new.last_updated_by:=USER();
	:new.last_updated_on:=localtimestamp;
	:new.row_version:=nvl(:new.row_version,0)+1;
	end if;
	:new.program_name:= trim(upper(:new.program_name));
	:new.program_type:= trim(upper(:new.program_type));
	:new.step_name:= trim(upper(:new.step_name));
	:new.status:= trim(upper(:new.status));
end;

/

create table &&segments_global_params_table_name (
	param varchar2(255),
	value varchar2(255),
	created_by varchar2(255),
	created_on timestamp with local time zone,
	last_updated_by varchar2(255),
	last_updated_on timestamp with local time zone,
	row_version number
);

alter table &&segments_global_params_table_name add constraint seg_glb_par_pk primary key (param);
create or replace trigger biu_glb_params
before insert or update on &&segments_global_params_table_name
for each row
begin
	if inserting then
	:new.created_by:=USER();
	:new.created_on:=localtimestamp;
	:new.row_version:=1;
	end if;
	if updating then
	:new.last_updated_by:=USER();
	:new.last_updated_on:=localtimestamp;
	:new.row_version:=nvl(:new.row_version,0)+1;
	end if;
	:new.param:= trim(upper(:new.param));
	:new.value:= trim(upper(:new.value));
end;

/

- -Set global parameters
insert into &&segments_global_params_table_name(param, value) values ('SUBJECT', '&&subject');
insert into &&segments_global_params_table_name(param, value) values ('GROUP_TYPE', '&&grouptype');
create table &&segments_log_table_name(
	seg_tracking_code number,
	seg_subject_id number,
	created_by varchar2(255),
	created_on timestamp with local time zone
);

create table &&segments_cg_log_table_name(
	seg_tracking_code number,
	seg_cg_tracking_code number,
	seg_subject_id number,
	created_by varchar2(255),
	created_on timestamp with local time zone
);

select
accelera.accelera_integration_tools.send_segment_to_channel
(
	p_seg_out_tbl => 'tmp_segments_mzbux67243',
	p_seg_id => 'seg_vbhyk234',
	p_seg_run_type => 1,
	p_seg_placeholders => 'FIRSTNAME, FATHERNAME',
	p_channel_code => 'SMS',
)
from
dual