[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT application_id
,object_version_number
,stylesheet_app_id
,stylesheet_code
,integrator_app_id
,integrator_code
,style
,style_class
,reporting_flag
,reporting_interface_app_id
,reporting_interface_code
,created_by
,last_updated_by
,last_update_login
FROM bne_layouts_b
WHERE application_id = 800
AND layout_code = p_base_layout_code;
bne_layouts_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_row.application_id
,x_layout_code => p_layout_code
,x_object_version_number => 1
,x_stylesheet_app_id => l_layout_row.stylesheet_app_id
,x_stylesheet_code => l_layout_row.stylesheet_code
,x_integrator_app_id => l_layout_row.integrator_app_id
,x_integrator_code => l_layout_row.integrator_code
,x_style => l_layout_row.style
,x_style_class => l_layout_row.style_class
,x_reporting_flag => l_layout_row.reporting_flag
,x_reporting_interface_app_id => l_layout_row.reporting_interface_app_id
,x_reporting_interface_code => l_layout_row.reporting_interface_code
,x_user_name => p_user_name
,x_creation_date => SYSDATE
,x_created_by => l_layout_row.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_row.last_updated_by
,x_last_update_login => l_layout_row.last_update_login);
SELECT application_id
,block_id
,parent_id
,layout_element
,style_class
,style
,row_style_class
,row_style
,col_style_class
,col_style
,prompt_displayed_flag
,prompt_style_class
,prompt_style
,hint_displayed_flag
,hint_style_class
,hint_style
,orientation
,layout_control
,display_flag
,BLOCKSIZE
,minsize
,MAXSIZE
,sequence_num
,prompt_colspan
,hint_colspan
,row_colspan
,summary_style_class
,summary_style
,created_by
,last_updated_by
,last_update_login
FROM bne_layout_blocks_b
WHERE application_id = 800
AND layout_code = p_base_layout_code
ORDER BY block_id;
bne_layout_blocks_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_blocks_row.application_id
,x_layout_code => p_layout_code
,x_block_id => l_layout_blocks_row.block_id
,x_object_version_number => 1
,x_parent_id => l_layout_blocks_row.parent_id
,x_layout_element => l_layout_blocks_row.layout_element
,x_style_class => l_layout_blocks_row.style_class
,x_style => l_layout_blocks_row.style
,x_row_style_class => l_layout_blocks_row.row_style_class
,x_row_style => l_layout_blocks_row.row_style
,x_col_style_class => l_layout_blocks_row.col_style_class
,x_col_style => l_layout_blocks_row.col_style
,x_prompt_displayed_flag => l_layout_blocks_row.prompt_displayed_flag
,x_prompt_style_class => l_layout_blocks_row.prompt_style_class
,x_prompt_style => l_layout_blocks_row.prompt_style
,x_hint_displayed_flag => l_layout_blocks_row.hint_displayed_flag
,x_hint_style_class => l_layout_blocks_row.hint_style_class
,x_hint_style => l_layout_blocks_row.hint_style
,x_orientation => l_layout_blocks_row.orientation
,x_layout_control => l_layout_blocks_row.layout_control
,x_display_flag => l_layout_blocks_row.display_flag
,x_blocksize => l_layout_blocks_row.BLOCKSIZE
,x_minsize => l_layout_blocks_row.minsize
,x_maxsize => l_layout_blocks_row.MAXSIZE
,x_sequence_num => l_layout_blocks_row.sequence_num
,x_prompt_colspan => l_layout_blocks_row.prompt_colspan
,x_hint_colspan => l_layout_blocks_row.hint_colspan
,x_row_colspan => l_layout_blocks_row.row_colspan
,x_summary_style_class => l_layout_blocks_row.summary_style_class
,x_summary_style => l_layout_blocks_row.summary_style
,x_user_name => p_user_name
,x_creation_date => SYSDATE
,x_created_by => l_layout_blocks_row.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_blocks_row.last_updated_by
,x_last_update_login => l_layout_blocks_row.last_update_login);
SELECT application_id
,layout_code
,block_id
,interface_app_id
,interface_code
,interface_seq_num
,sequence_num
,style
,style_class
,hint_style
,hint_style_class
,prompt_style
,prompt_style_class
,default_type
,DEFAULT_VALUE
,created_by
,last_updated_by
,last_update_login
,read_only_flag
FROM bne_layout_cols
WHERE application_id = 800
AND layout_code = p_base_layout_code
ORDER BY block_id;
bne_layout_cols_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_cols_row.application_id
,x_layout_code => p_layout_code
,x_block_id => l_layout_cols_row.block_id
,x_sequence_num => l_layout_cols_row.sequence_num
,x_object_version_number => 1
,x_interface_app_id => l_layout_cols_row.interface_app_id
,x_interface_code => l_layout_cols_row.interface_code
,x_interface_seq_num => l_layout_cols_row.interface_seq_num
,x_style_class => l_layout_cols_row.style_class
,x_hint_style => l_layout_cols_row.hint_style
,x_hint_style_class => l_layout_cols_row.hint_style_class
,x_prompt_style => l_layout_cols_row.prompt_style
,x_prompt_style_class => l_layout_cols_row.prompt_style_class
,x_default_type => l_layout_cols_row.default_type
,x_default_value => l_layout_cols_row.DEFAULT_VALUE
,x_style => l_layout_cols_row.style
,x_creation_date => SYSDATE
,x_created_by => l_layout_cols_row.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_cols_row.last_updated_by
,x_last_update_login => l_layout_cols_row.last_update_login
,x_read_only_flag => l_read_only_flag
,x_display_width => l_display_width);
PROCEDURE delete_cwb_layout_cols(p_layout_code IN VARCHAR2
,p_application_id IN NUMBER )
IS
l_proc Varchar2(72):= 'delete_cwb_layout_cols';
SELECT blc.application_id
,blc.layout_code
,blc.block_id
,blc.sequence_num
FROM bne_layout_cols blc
WHERE blc.application_id = p_application_id
AND blc.layout_code = p_layout_code;
bne_layout_cols_pkg.delete_row
(x_application_id => l_layout_col_rec.application_id
,x_layout_code => l_layout_col_rec.layout_code
,x_block_id => l_layout_col_rec.block_id
,x_sequence_num => l_layout_col_rec.sequence_num);
END delete_cwb_layout_cols;
select count(group_oipl_id) IdCount
,max(ws_abr_id) ws_abr_id
,max(elig_sal_abr_id) elig_sal_abr_id
,max(ws_nnmntry_uom) ws_nnmntry_uom
,null ws_sub_acty_typ_cd
from ben_cwb_pl_dsgn
where group_pl_id = p_group_pl_id
and group_pl_id = pl_id
and group_oipl_id = oipl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and group_oipl_id <> -1;
select count(oipl_id) IdCount
,max(ws_abr_id) ws_abr_id
,max(elig_sal_abr_id) elig_sal_abr_id
,max(ws_nnmntry_uom) ws_nnmntry_uom
,null ws_sub_acty_typ_cd
from ben_cwb_pl_dsgn
where group_pl_id = p_group_pl_id
and group_pl_id = pl_id
and group_oipl_id <> oipl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and group_oipl_id <> -1;
select count(group_oipl_id) IdCount
,max(ws_abr_id) ws_abr_id
,max(elig_sal_abr_id) elig_sal_abr_id
,max(ws_nnmntry_uom) ws_nnmntry_uom
,null ws_sub_acty_typ_cd
from ben_cwb_pl_dsgn
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and group_oipl_id <> -1
and oipl_ordr_num = 1;
select count(group_oipl_id) IdCount
,max(ws_abr_id) ws_abr_id
,max(elig_sal_abr_id) elig_sal_abr_id
,max(ws_nnmntry_uom) ws_nnmntry_uom
,null ws_sub_acty_typ_cd
from ben_cwb_pl_dsgn
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and group_oipl_id <> -1
and oipl_ordr_num = 2;
select count(group_oipl_id) IdCount
,max(ws_abr_id) ws_abr_id
,max(elig_sal_abr_id) elig_sal_abr_id
,max(ws_nnmntry_uom) ws_nnmntry_uom
,null ws_sub_acty_typ_cd
from ben_cwb_pl_dsgn
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and group_oipl_id <> -1
and oipl_ordr_num = 3;
select count(group_oipl_id) IdCount
,max(ws_abr_id) ws_abr_id
,max(elig_sal_abr_id) elig_sal_abr_id
,max(ws_nnmntry_uom) ws_nnmntry_uom
,null ws_sub_acty_typ_cd
from ben_cwb_pl_dsgn
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and group_oipl_id <> -1
and oipl_ordr_num = 4;
select count(pl_id) IdCount
,max(ws_abr_id) ws_abr_id
,max(elig_sal_abr_id) elig_sal_abr_id
,max(ws_nnmntry_uom) ws_nnmntry_uom
,max(ws_sub_acty_typ_cd) ws_sub_acty_typ_cd
from ben_cwb_pl_dsgn
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and group_pl_id <> pl_id
and group_oipl_id = -1
and oipl_id = -1;
select count(group_pl_id) IdCount
,max(ws_abr_id) ws_abr_id
,max(elig_sal_abr_id) elig_sal_abr_id
,max(ws_nnmntry_uom) ws_nnmntry_uom
,max(ws_sub_acty_typ_cd) ws_sub_acty_typ_cd
from ben_cwb_pl_dsgn
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and group_pl_id = pl_id
and group_oipl_id = -1
and oipl_id = -1;
Function decide_insert_rec (p_inf_seq IN NUMBER
,p_group_pl_id IN NUMBER
,p_lf_evt_ocrd_dt IN DATE )
Return Varchar IS
Cursor csr_is_options IS
Select '1'
From bne_interface_cols_b
where interface_code ='BEN_CWB_WRK_SHT_INTF'
And INTERFACE_COL_NAME like '%OPT%'
And application_id = 800
And SEQUENCE_NUM = p_inf_seq;
Select '1'
From bne_interface_cols_b
where interface_code ='BEN_CWB_WRK_SHT_INTF'
And INTERFACE_COL_NAME like '%OPT1%'
And application_id = 800
And SEQUENCE_NUM = p_inf_seq;
Select '1'
From bne_interface_cols_b
where interface_code ='BEN_CWB_WRK_SHT_INTF'
And INTERFACE_COL_NAME like '%OPT2%'
And application_id = 800
And SEQUENCE_NUM = p_inf_seq;
Select '1'
From bne_interface_cols_b
where interface_code ='BEN_CWB_WRK_SHT_INTF'
And INTERFACE_COL_NAME like '%OPT3%'
And application_id = 800
And SEQUENCE_NUM = p_inf_seq;
Select '1'
From bne_interface_cols_b
where interface_code ='BEN_CWB_WRK_SHT_INTF'
And INTERFACE_COL_NAME like '%OPT4%'
And application_id = 800
And SEQUENCE_NUM = p_inf_seq;
l_insert_rec Varchar(1):= 'Y';
l_proc Varchar2(72) := g_package||'decide_insert_rec';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'Y';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'Y';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'Y';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'Y';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'Y';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'Y';
l_insert_rec := 'N';
l_insert_rec := 'N';
l_insert_rec := 'N';
return l_insert_rec;
End decide_insert_rec ;
Procedure insert_cwb_layout_cols_row(p_application_id In Number,
p_base_layout_code In Varchar2,
p_act_layout_code In Varchar2,
p_inf_seq In Number,
p_dis_seq In Number,
p_group_pl_id In Number Default Null,
p_lf_evt_ocrd_dt In Date Default Null
)
IS
CURSOR csr_layout_cols_row
IS
SELECT blc.application_id
,blc.layout_code
,blc.block_id
,blc.interface_app_id
,blc.interface_code
,blc.interface_seq_num
,blc.sequence_num
,blc.style
,blc.style_class
,blc.hint_style
,blc.hint_style_class
,blc.prompt_style
,blc.prompt_style_class
,blc.default_type
,blc.DEFAULT_VALUE
,blc.created_by
,blc.last_updated_by
,blc.last_update_login
,blc.read_only_flag
FROM bne_layout_cols blc
WHERE blc.application_id = p_application_id
AND blc.layout_code = p_base_layout_code
AND blc.interface_seq_num = p_inf_seq;
l_insert_rec VARCHAR2(1) := 'Y';
l_insert_rec := 'Y';
l_insert_rec := decide_insert_rec (p_inf_seq => p_inf_seq
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
hr_utility.set_location('p_inf_seq #: '||p_inf_seq||' l_insert_rec #:'||l_insert_rec,60);
l_insert_rec := 'N';
If l_insert_rec = 'Y' then
IF (p_base_layout_code = 'BEN_CWB_WRK_SHT_BASE_LYT') THEN
l_read_only_flag := 'Y';
bne_layout_cols_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_cols_row.application_id
,x_layout_code => p_act_layout_code
,x_block_id => l_layout_cols_row.block_id
,x_sequence_num => p_dis_seq
,x_object_version_number => 1
,x_interface_app_id => l_layout_cols_row.interface_app_id
,x_interface_code => l_layout_cols_row.interface_code
,x_interface_seq_num => l_layout_cols_row.interface_seq_num
,x_style_class => l_layout_cols_row.style_class
,x_hint_style => l_layout_cols_row.hint_style
,x_hint_style_class => l_layout_cols_row.hint_style_class
,x_prompt_style => l_layout_cols_row.prompt_style
,x_prompt_style_class => l_layout_cols_row.prompt_style_class
,x_default_type => l_layout_cols_row.default_type
,x_default_value => l_layout_cols_row.DEFAULT_VALUE
,x_style => l_layout_cols_row.style
,x_creation_date => SYSDATE
,x_created_by => l_layout_cols_row.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_cols_row.last_updated_by
,x_last_update_login => l_layout_cols_row.last_update_login
,x_read_only_flag => l_read_only_flag
,x_display_width => l_display_width);
End insert_cwb_layout_cols_row;
PROCEDURE update_cwb_layout(
p_layout_code IN VARCHAR2
,p_base_layout IN VARCHAR2
,p_interface_seq IN VARCHAR2
,p_rendered_seq IN VARCHAR2
,p_group_pl_id IN NUMBER Default Null
,p_lf_evt_ocrd_dt IN DATE Default Null
,p_download_switch OUT NOCOPY VARCHAR2
) IS
LIST column_list := column_list();
SELECT blc.application_id
,blc.layout_code
,blc.block_id
,blc.sequence_num
FROM bne_layout_cols blc
WHERE blc.application_id = v_application_id
AND blc.layout_code = v_layout_code;
SELECT blc.application_id
,blc.layout_code
,blc.block_id
,blc.interface_app_id
,blc.interface_code
,blc.interface_seq_num
,blc.sequence_num
,blc.style
,blc.style_class
,blc.hint_style
,blc.hint_style_class
,blc.prompt_style
,blc.prompt_style_class
,blc.default_type
,blc.DEFAULT_VALUE
,blc.created_by
,blc.last_updated_by
,blc.last_update_login
FROM bne_layout_cols blc
WHERE blc.application_id = v_application_id
AND blc.layout_code = v_layout_code
AND blc.interface_seq_num = v_interface_seq;
delete_cwb_layout_cols(p_layout_code => p_layout_code
,p_application_id => l_application_id);
bne_layout_cols_pkg.delete_row
(x_application_id => l_layout_col_rec.application_id
,x_layout_code => l_layout_col_rec.layout_code
,x_block_id => l_layout_col_rec.block_id
,x_sequence_num => l_layout_col_rec.sequence_num);
insert_cwb_layout_cols_row( p_application_id => l_application_id
,p_base_layout_code => p_base_layout
,p_act_layout_code => p_layout_code
,p_inf_seq => LIST(k).p_interface_seq
,p_dis_seq => LIST(k).p_sequence
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt );
bne_layout_cols_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_cols_row.application_id
,x_layout_code => p_layout_code
,x_block_id => l_layout_cols_row.block_id
,x_sequence_num => LIST(k).p_sequence
,x_object_version_number => 1
,x_interface_app_id => l_layout_cols_row.interface_app_id
,x_interface_code => l_layout_cols_row.interface_code
,x_interface_seq_num => l_layout_cols_row.interface_seq_num
,x_style_class => l_layout_cols_row.style_class
,x_hint_style => l_layout_cols_row.hint_style
,x_hint_style_class => l_layout_cols_row.hint_style_class
,x_prompt_style => l_layout_cols_row.prompt_style
,x_prompt_style_class => l_layout_cols_row.prompt_style_class
,x_default_type => l_layout_cols_row.default_type
,x_default_value => l_layout_cols_row.DEFAULT_VALUE
,x_style => l_layout_cols_row.style
,x_creation_date => SYSDATE
,x_created_by => l_layout_cols_row.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_cols_row.last_updated_by
,x_last_update_login => l_layout_cols_row.last_update_login);
END update_cwb_layout;
SELECT layout_code
FROM bne_layouts_b
WHERE integrator_code = p_integrator_code
AND integrator_app_id = 800
AND application_id = 800
AND layout_code <> p_base_layout_code
AND layout_code NOT IN (SELECT attribute1
FROM ben_transaction
WHERE transaction_type = 'CWBWEBADI'
AND attribute2 IS NOT NULL
AND attribute1 IS NOT NULL
AND DECODE(transaction_type, 'CWBWEBADI', SYSDATE - (to_number(l_layout_lock_time) /(24 * 60))
- TO_DATE(attribute2, 'yyyy/mm/dd:hh:mi'),0) < 0);
DELETE ben_transaction
WHERE transaction_type = 'CWBWEBADI'
AND attribute1 = l_layout;
INSERT INTO ben_transaction
(transaction_id
,transaction_type
,attribute1
,attribute2)
VALUES (ben_transaction_s.NEXTVAL
,'CWBWEBADI'
,l_layout
,TO_CHAR(SYSDATE, 'yyyy/mm/dd:hh:mi'));
DELETE ben_transaction
WHERE transaction_type = 'CWBWEBADI'
AND attribute1 = p_layout_code;
Select InfColsTl.prompt_left
From bne_interface_cols_b InfCols,
bne_interface_cols_tl InfColsTl
Where InfCols.interface_code = p_interface_code
And InfCols.INTERFACE_COL_NAME = p_interface_col_code
And InfCols.application_id = 800
And InfCols.interface_code = InfColsTl.interface_code
And InfCols.sequence_num = InfColsTl.sequence_num
And InfColsTl.application_id = 800
And InfColsTl.Language = Userenv('LANG');
Insert Into BEN_CUSTOM_REGION_ITEMS
(
REGION_CODE -- task code / integrator code
,CUSTOM_KEY -- mgr_per_in_ler_id
,CUSTOM_TYPE -- integrator code
,ITEM_NAME -- interface_col_name
,DISPLAY_FLAG -- Y/N
,LABEL -- interface_prompt_above
,ORDR_NUM -- Display Order
)
Values
(
p_integrator_code -- task code / integrator code
,p_key -- mgr_per_in_ler_id
,p_integrator_code -- integrator code
,p_interface_col_code -- interface_col_name
,'N'
,l_col_prompt -- interface_prompt_above
,p_display_seq -- Display Order
);
Select '1'
From BEN_CUSTOM_REGION_ITEMS
Where CUSTOM_TYPE = p_integrator_code
And REGION_CODE = p_integrator_code
And CUSTOM_KEY = p_key;
Procedure manipulate_selected_data( p_key IN VARCHAR2
,p_integrator_code IN VARCHAR2
,p_interface_code IN VARCHAR2
,p_interface_col_code IN VARCHAR2
,p_display_seq IN Number )
IS
l_proc Varchar2(72) := g_package||'chk_entry_in_custom_table';
End manipulate_selected_data;
Procedure delete_custom_data(p_key IN VARCHAR2,
p_integrator_code IN VARCHAR2)
IS
l_exist_in_table Varchar2(1);
Delete From BEN_CUSTOM_REGION_ITEMS
Where CUSTOM_KEY = p_key
And REGION_CODE = p_integrator_code
And CUSTOM_TYPE = p_integrator_code;
Procedure update_cwb_custom_layout( p_key IN VARCHAR2
,p_integrator_code IN VARCHAR2
,p_interface_code IN VARCHAR2
,p_act_layout_code IN VARCHAR2
,p_base_layout_code IN VARCHAR2
,p_group_pl_id IN NUMBER Default Null
,p_lf_evt_ocrd_dt IN DATE Default Null
,p_download_switch OUT NOCOPY VARCHAR2
)
IS
l_num NUMBER := 0;
Select infCols.interface_col_name inf_col_name
,infCols.sequence_num inf_Seq_Num
,layCols.SEQUENCE_NUM Dis_Seq_Num
From bne_interfaces_b inf,
bne_interface_cols_b infCols,
bne_layout_cols layCols,
bne_layout_blocks_b layBlk
Where inf.integrator_code = p_integrator_code
And inf.integrator_app_id = 800
And inf.interface_code = p_interface_code
And inf.application_id = 800
And inf.interface_code = infCols.interface_code
And infCols.application_id = 800
And infCols.sequence_num = layCols.INTERFACE_SEQ_NUM
And layCols.layout_code = p_base_layout_code
And layCols.application_id = 800
And layCols.layout_code = layBlk.layout_code
And layCols.block_id = layBlk.block_id
And layBlk.STYLE_CLASS = 'BNE_LINES'
And layBlk.application_id = 800;
Select cust.ITEM_NAME inf_col_name
,infCols.SEQUENCE_NUM inf_Seq_Num
,cust.ORDR_NUM Dis_Seq_Num
From BEN_CUSTOM_REGION_ITEMS cust,
Bne_interface_cols_b infCols
Where cust.CUSTOM_KEY = p_key
And cust.REGION_CODE = p_integrator_code
And cust.CUSTOM_TYPE = p_integrator_code
And cust.ITEM_NAME = infCols.INTERFACE_COL_NAME
And infCols.interface_code = p_interface_code
And infCols.application_id = 800;
select interface_seq_num inf_Seq_Num,
2000+rownum Dis_Seq_Num
from bne_layout_cols
where interface_seq_num in (130,131,132,133,134)
and layout_code = p_base_layout_code
and interface_code = p_interface_code
and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
and application_id = 800;
select decode (col1.interface_seq_num, 7, 1,
159,2,
165,3,
9,4,
5,5,
164,6
),
col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
from bne_layout_cols col1,
bne_layout_cols col2
where col1.interface_seq_num in (7,159,165,9,5,164)
and col1.layout_code = p_base_layout_code
and col1.interface_code = p_interface_code
and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
and col1.application_id = 800
and col1.interface_code = col2.interface_code
and col2.application_id = 800
and col2.interface_seq_num = 12
and col2.layout_code = p_act_layout_code
order by 1;
select decode (col1.interface_seq_num, 25, 1,
160,2,
166,3,
27,4
),
col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
from bne_layout_cols col1,
bne_layout_cols col2
where col1.interface_seq_num in (25,160,166,27)
and col1.layout_code = p_base_layout_code
and col1.interface_code = col2.interface_code
and col2.interface_seq_num = 30
and col2.interface_code = p_interface_code
and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
and col2.layout_code = p_act_layout_code
and col1.application_id = 800
and col2.application_id = 800
order by 1;
select decode (col1.interface_seq_num, 43, 1,
161,2,
167,3,
45,4
),
col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
from bne_layout_cols col1,
bne_layout_cols col2
where col1.interface_seq_num in (43,161,167,45)
and col1.layout_code = p_base_layout_code
and col1.interface_code = col2.interface_code
and col2.interface_seq_num = 48
and col2.interface_code = p_interface_code
and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
and col2.layout_code = p_act_layout_code
and col1.application_id = 800
and col2.application_id = 800
order by 1;
select decode (col1.interface_seq_num, 61, 1,
162,2,
168,3,
63,4
),
col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
from bne_layout_cols col1,
bne_layout_cols col2
where col1.interface_seq_num in (61,162,168,63)
and col1.layout_code = p_base_layout_code
and col1.interface_code = col2.interface_code
and col2.interface_seq_num = 66
and col2.interface_code = p_interface_code
and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
and col2.layout_code = p_act_layout_code
and col1.application_id = 800
and col2.application_id = 800
order by 1;
select decode (col1.interface_seq_num, 79, 1,
163,2,
169,3,
81,4
),
col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
from bne_layout_cols col1,
bne_layout_cols col2
where col1.interface_seq_num in (79,163,169,81)
and col1.layout_code = p_base_layout_code
and col1.interface_code = col2.interface_code
and col2.interface_seq_num = 84
and col2.interface_code = p_interface_code
and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
and col2.layout_code = p_act_layout_code
and col1.application_id = 800
and col2.application_id = 800
order by 1;
select decode (col1.interface_seq_num, 125, 1,
197,2
),
col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
from bne_layout_cols col1,
bne_layout_cols col2
where col1.interface_seq_num in (125,197)
and col1.layout_code = p_base_layout_code
and col1.interface_code = col2.interface_code
and col2.interface_seq_num = 3
and col2.interface_code = p_interface_code
and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
and col2.layout_code = p_act_layout_code
and col1.application_id = 800
and col2.application_id = 800
order by 1;
(Select interface_seq_num inf_Seq_Num
, sequence_num Dis_Seq_Num
, decode (interface_seq_num, 170, 1,
171,2,
172,3,
173,4,
174,5,
175,6,
176,7,
177,8,
178,9,
179,10,
180,11,
181,12,
182,13,
189,14,
158,15,
194,16,
195,17,
196,18,
198,19,
188,20,
190,21
) order_in_layout
From bne_layout_cols
Where interface_code = p_interface_code
And p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
And interface_seq_num in (170,171,172,173,174,175,176,177,178,179,180,181,182,189,158,188,190,194,195,196,198)
And layout_code = p_base_layout_code
And application_id = 800)
union
(Select interface_seq_num inf_Seq_Num
,sequence_num Dis_Seq_Num
, interface_seq_num order_in_layout
From bne_layout_cols
Where interface_code = p_interface_code
And p_interface_code = 'BEN_CWB_BGT_SHT_INTF'
And interface_seq_num in (37,40,41,42,43,44,45,46,47,48,49,50,51,52,53)
And layout_code = p_base_layout_code
And application_id = 800
)
union
(Select interface_seq_num inf_Seq_Num
,sequence_num Dis_Seq_Num
, interface_seq_num order_in_layout
From bne_layout_cols
Where interface_code = p_interface_code
And p_interface_code = 'BEN_CWB_SUMM_DIR_REP_INTF'
And interface_seq_num in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)
And layout_code = p_base_layout_code
And application_id = 800
) Order by 3 ;
l_proc Varchar2(72) := g_package||'update_cwb_custom_layout';
delete_cwb_layout_cols(p_layout_code => p_act_layout_code
,p_application_id => 800);
insert_cwb_layout_cols_row( p_application_id => 800
,p_base_layout_code => p_base_layout_code
,p_act_layout_code => p_act_layout_code
,p_inf_seq => l_bne_row.inf_Seq_Num
,p_dis_seq => l_bne_row.Dis_Seq_Num * 10
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
insert_cwb_layout_cols_row( p_application_id => 800
,p_base_layout_code => p_base_layout_code
,p_act_layout_code => p_act_layout_code
,p_inf_seq => l_cust_row.inf_Seq_Num
,p_dis_seq => l_cust_row.dis_Seq_Num * 10
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
insert_cwb_layout_cols_row( p_application_id => 800
,p_base_layout_code => p_base_layout_code
,p_act_layout_code => p_act_layout_code
,p_inf_seq => l_row.inf_Seq_Num
,p_dis_seq => l_row.dis_Seq_Num + l_num
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
insert_cwb_layout_cols_row( p_application_id => 800
,p_base_layout_code => p_base_layout_code
,p_act_layout_code => p_act_layout_code
,p_inf_seq => l_row.inf_Seq_Num
,p_dis_seq => l_row.dis_Seq_Num + l_num
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt );
insert_cwb_layout_cols_row( p_application_id => 800
,p_base_layout_code => p_base_layout_code
,p_act_layout_code => p_act_layout_code
,p_inf_seq => l_row.inf_Seq_Num
,p_dis_seq => l_row.dis_Seq_Num + l_num
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
insert_cwb_layout_cols_row( p_application_id => 800
,p_base_layout_code => p_base_layout_code
,p_act_layout_code => p_act_layout_code
,p_inf_seq => l_row.inf_Seq_Num
,p_dis_seq => l_row.dis_Seq_Num + l_num
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
insert_cwb_layout_cols_row( p_application_id => 800
,p_base_layout_code => p_base_layout_code
,p_act_layout_code => p_act_layout_code
,p_inf_seq => l_row.inf_Seq_Num
,p_dis_seq => l_row.dis_Seq_Num + l_num
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
insert_cwb_layout_cols_row( p_application_id => 800
,p_base_layout_code => p_base_layout_code
,p_act_layout_code => p_act_layout_code
,p_inf_seq => l_row.inf_Seq_Num
,p_dis_seq => l_row.dis_Seq_Num + l_num
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
insert_cwb_layout_cols_row( p_application_id => 800
,p_base_layout_code => p_base_layout_code
,p_act_layout_code => p_act_layout_code
,p_inf_seq => l_row.inf_Seq_Num
,p_dis_seq => l_num
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
insert_cwb_layout_cols_row( p_application_id => 800
,p_base_layout_code => p_base_layout_code
,p_act_layout_code => p_act_layout_code
,p_inf_seq => l_row.inf_Seq_Num
,p_dis_seq => l_row.dis_Seq_Num
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt );
End update_cwb_custom_layout;