[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 (substr(interface_code,1,15)='BEN_CWB_WS_INTF'
OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
And INTERFACE_COL_NAME like '%OPT%'
AND interface_code = g_interface_code
And application_id = 800
And SEQUENCE_NUM = p_inf_seq;
Select '1'
From bne_interface_cols_b
where (substr(interface_code,1,15) ='BEN_CWB_WS_INTF'
OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
And INTERFACE_COL_NAME like '%OPT1%'
AND interface_code = g_interface_code
And application_id = 800
And SEQUENCE_NUM = p_inf_seq;
Select '1'
From bne_interface_cols_b
where (substr(interface_code,1,15) ='BEN_CWB_WS_INTF'
OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
AND interface_code = g_interface_code
And INTERFACE_COL_NAME like '%OPT2%'
And application_id = 800
And SEQUENCE_NUM = p_inf_seq;
Select '1'
From bne_interface_cols_b
where (substr(interface_code,1,15) ='BEN_CWB_WS_INTF'
OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
AND interface_code = g_interface_code
And INTERFACE_COL_NAME like '%OPT3%'
And application_id = 800
And SEQUENCE_NUM = p_inf_seq;
Select '1'
From bne_interface_cols_b
where (substr(interface_code,1,15) ='BEN_CWB_WS_INTF'
OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
AND interface_code = g_interface_code
And INTERFACE_COL_NAME like '%OPT4%'
And application_id = 800
And SEQUENCE_NUM = p_inf_seq;
select '1'
from ben_cwb_pl_dsgn
where pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and asg_updt_eff_date is not null;
Select '1'
From bne_interface_cols_b
where (substr(interface_code,1,15) ='BEN_CWB_WS_INTF'
OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
AND interface_code = g_interface_code
And SEQUENCE_NUM in (126,152,191,192)
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';
hr_utility.set_location('Assignment Update Effective Date Not defined for the plan :' || p_group_pl_id,785);
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;
select bic.read_only_flag
from bne_interface_cols_b bic,
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
and bic.sequence_num = p_inf_seq
and blc.interface_code = bic.interface_code;*/
select '1'
FROM bne_layout_cols blc
WHERE blc.application_id = p_application_id
AND blc.layout_code = p_act_layout_code
AND blc.interface_seq_num = p_inf_seq
AND blc.interface_seq_num in (152,191,192,105,126);
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';
l_insert_rec := 'N'; -- layout col already inserted
If l_insert_rec = 'Y' then
IF (substr(p_base_layout_code,1,16) = 'BEN_CWB_WS_LYT1_'
OR 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_region_key --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_region_key
And CUSTOM_KEY = p_key;
Procedure manipulate_selected_data( p_key IN VARCHAR2
,p_region_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_region_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_region_key
And CUSTOM_TYPE = p_integrator_code;
Procedure update_cwb_custom_layout( p_key IN VARCHAR2
,p_region_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_region_key --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 (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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 (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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 (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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 (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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 (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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 (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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 (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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,
126,1,
105,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 (105,126)
and col1.layout_code = p_base_layout_code
and col1.interface_code = col2.interface_code
and col2.interface_seq_num = 191
and col2.interface_code = p_interface_code
and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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,
-- 192,1,
126,1,
105,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 (105,126)
and col1.layout_code = p_base_layout_code
and col1.interface_code = col2.interface_code
and col2.interface_seq_num = 152
and col2.interface_code = p_interface_code
and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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,
152,1,
126,2,
105,3
),
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 (105,152,126)
and col1.layout_code = p_base_layout_code
and col1.interface_code = col2.interface_code
and col2.interface_seq_num = 192
and col2.interface_code = p_interface_code
and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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 (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
OR 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_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;
select
nvl(lcl_pl.grade_range_validation,grp_pl.grade_range_validation) grade_range_validation
from
ben_cwb_pl_dsgn grp_pl
,ben_cwb_pl_dsgn lcl_pl
,ben_cwb_person_rates rates
where rates.group_per_in_ler_id = l_group_per_in_ler_id
and rates.group_pl_id = grp_pl.pl_id
and rates.lf_evt_ocrd_dt = grp_pl.lf_evt_ocrd_dt
and grp_pl.oipl_id = -1
and rates.pl_id = lcl_pl.pl_id
and rates.lf_evt_ocrd_dt = lcl_pl.lf_evt_ocrd_dt
and lcl_pl.oipl_id = -1
and rownum = 1;
select
(per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
decode(l_ws_val,'-0.0000000000000001',plRt.ws_val,l_ws_val) ws_val,
(per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
(per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates plRt
,ben_cwb_person_info per
where plRt.group_per_in_ler_id = l_group_per_in_ler_id
and plRt.group_per_in_ler_id = per.group_per_in_ler_id
and plRt.pl_id = pl.pl_id
and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and plRt.oipl_id = pl.oipl_id
and pl.oipl_id = -1
and pl.ws_sub_acty_typ_cd = 'ICM7';
select
(per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
(per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
(per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates plRt
,ben_cwb_person_info per
where plRt.group_per_in_ler_id = l_group_per_in_ler_id
and plRt.group_per_in_ler_id = per.group_per_in_ler_id
and plRt.pl_id = pl.pl_id
and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and plRt.oipl_id = pl.oipl_id
and pl.oipl_id = -1;
select
(per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
decode(l_ws_val,'-0.0000000000000001',optRt.ws_val,l_ws_val) ws_val,
(per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
(per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max,
per.group_per_in_ler_id
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates optRt
,ben_cwb_person_info per
where optRt.person_rate_id = l_rate_id
and optRt.group_per_in_ler_id = per.group_per_in_ler_id
and optRt.pl_id = pl.pl_id
and optRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and optRt.oipl_id = pl.oipl_id
and pl.oipl_id <> -1
and pl.ws_sub_acty_typ_cd in ('ICM7','ICM11');
select
(per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
decode(l_ws_val,'-0.0000000000000001',plRt.ws_val,l_ws_val) ws_val,
(per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
(per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates plRt
,ben_cwb_person_info per
where plRt.group_per_in_ler_id = l_group_per_in_ler_id
and plRt.group_per_in_ler_id = per.group_per_in_ler_id
and plRt.pl_id = pl.pl_id
and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and plRt.oipl_id = pl.oipl_id
and pl.oipl_id = -1
and pl.ws_sub_acty_typ_cd = 'ICM7';
select
(per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
(per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
(per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates plRt
,ben_cwb_person_info per
where plRt.group_per_in_ler_id = l_group_per_in_ler_id
and plRt.group_per_in_ler_id = per.group_per_in_ler_id
and plRt.pl_id = pl.pl_id
and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and plRt.oipl_id = pl.oipl_id
and pl.oipl_id = -1;
select
(per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
decode(l_ws_val,'-0.0000000000000001',optRt.ws_val,l_ws_val) ws_val,
(per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
(per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max,
per.group_per_in_ler_id
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates optRt
,ben_cwb_person_info per
where optRt.person_rate_id = l_rate_id
and optRt.group_per_in_ler_id = per.group_per_in_ler_id
and optRt.pl_id = pl.pl_id
and optRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and optRt.oipl_id = pl.oipl_id
and pl.oipl_id <> -1
and pl.ws_sub_acty_typ_cd in ('ICM7','ICM11');
select
nvl(plRt.ws_val,0) ws_val
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates plRt
,ben_cwb_person_info per
where plRt.group_per_in_ler_id = p_group_per_in_ler_id
and plRt.group_per_in_ler_id = per.group_per_in_ler_id
and plRt.pl_id = pl.pl_id
and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and plRt.oipl_id = pl.oipl_id
and pl.oipl_id = -1
and pl.ws_sub_acty_typ_cd = 'ICM7';
select
(per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
(per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
(per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates plRt
,ben_cwb_person_info per
where plRt.group_per_in_ler_id = p_group_per_in_ler_id
and plRt.group_per_in_ler_id = per.group_per_in_ler_id
and plRt.pl_id = pl.pl_id
and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and plRt.oipl_id = pl.oipl_id
and pl.oipl_id = -1;
select
nvl(optRt.ws_val,0) ws_val
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates optRt
,ben_cwb_person_info per
where optRt.group_per_in_ler_id = p_group_per_in_ler_id
and optRt.group_per_in_ler_id = per.group_per_in_ler_id
and optRt.pl_id = pl.pl_id
and optRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and optRt.oipl_id = pl.oipl_id
and pl.oipl_id <> -1
and pl.oipl_ordr_num = l_oipl_ordr_num
and pl.ws_sub_acty_typ_cd in ('ICM7','ICM11');
select per.group_per_in_ler_id
,nvl(per.grade_annulization_factor/pl.pl_annulization_factor,1)*fnd_number.canonical_to_number(pgr.minimum)
|| ' - ' || nvl(per.grade_annulization_factor/pl.pl_annulization_factor,1)*fnd_number.canonical_to_number(pgr.maximum) proposed_grade_range
,nvl(per.grade_annulization_factor/pl.pl_annulization_factor,1)*fnd_number.canonical_to_number(pgr.mid_value) proposed_grade_mid
,get_new_salary(p_group_per_in_ler_id) * 100 / (nvl(per.grade_annulization_factor/pl.pl_annulization_factor,1)*fnd_number.canonical_to_number(pgr.mid_value)) proposed_grade_comparatio
from ben_cwb_pl_dsgn pl
,pay_grade_rules_f pgr
,ben_transaction atxn
,ben_cwb_person_info per
WHERE per.group_per_in_ler_id = p_group_per_in_ler_id
and atxn.transaction_id = per.assignment_id
AND atxn.transaction_type = 'CWBASG' || p_asg_updt_eff_date
AND TO_NUMBER (atxn.attribute7) = pgr.grade_or_spinal_point_id
and pgr.rate_id = per.pay_rate_id
and per.group_pl_id = pl.pl_id
and per.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and pl.group_oipl_id = -1
and per.lf_evt_ocrd_dt between pgr.effective_start_date and pgr.effective_end_date;
Select group_per_in_ler_id
from ben_cwb_person_rates
where person_rate_id = l_person_rate_id;