[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
Select attribute_value_id,attribute_id,
attribute_value,hr_value_id,description
from psb_attribute_values
where data_extract_id = p_src_data_extract_id;
Select attribute_value_id
from psb_attribute_values
where attribute_id = lr_attribute_id
and attribute_value = lr_attribute_value
and data_extract_id = p_data_extract_id;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
PSB_ATTRIBUTE_VALUES_PVT.UPDATE_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_attribute_value_id => C_ref_attr_rec.attribute_value_id,
p_attribute_id => C_Attr_Val_Rec.attribute_id,
p_attribute_value => C_Attr_Val_Rec.attribute_value,
p_hr_value_id => C_Attr_Val_Rec.hr_value_id,
p_description => C_Attr_Val_Rec.description,
p_data_extract_id => p_data_extract_id,
p_context => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login
);
select psb_attribute_values_s.nextval into
l_attribute_value_id from dual;
PSB_ATTRIBUTE_VALUES_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_list => null,
p_commit => null,
p_validation_level => null,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_attribute_value_id => l_attribute_value_id,
p_attribute_id => C_Attr_Val_Rec.attribute_id,
p_attribute_value => C_Attr_Val_Rec.attribute_value,
p_hr_value_id => C_Attr_Val_Rec.hr_value_id,
p_description => C_Attr_Val_Rec.description,
p_data_extract_id => p_data_extract_id,
p_context => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null,
p_attribute16 => null,
p_attribute17 => null,
p_attribute18 => null,
p_attribute19 => null,
p_attribute20 => null,
p_attribute21 => null,
p_attribute22 => null,
p_attribute23 => null,
p_attribute24 => null,
p_attribute25 => null,
p_attribute26 => null,
p_attribute27 => null,
p_attribute28 => null,
p_attribute29 => null,
p_attribute30 => null,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date
) ;
debug('Insert Attribute Values Failed');
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
select business_group_id
from PSB_DATA_EXTRACTS
where data_extract_id = p_src_data_extract_id;
Select pay_element_id,
name,
salary_flag
from psb_pay_elements
where data_extract_id = p_src_data_extract_id;
Select pay_element_id
from psb_pay_elements
where name = l_element_name
and data_extract_id = p_data_extract_id;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
l_insert_flag varchar2(1);
Select default_rule_id,
name,
global_default_flag,
business_group_id,
entity_id,
priority,
overwrite -- bug 4179734
from psb_defaults
where data_extract_id = p_src_data_extract_id;
Select aps.name,
effective_start_date,
effective_end_date
from psb_set_relations rels, psb_account_position_sets aps
where rels.account_position_set_id = aps.account_position_set_id
and aps.data_extract_id = p_src_data_extract_id
and rels.default_rule_id = l_src_default_rule_id;
Select account_distribution_id,
chart_of_accounts_id,
code_combination_id,
distribution_percent
from psb_default_account_distrs
where default_rule_id = l_src_default_rule_id;
Select assignment_type,attribute_id,
attribute_value_id,attribute_value,
pay_element_id,pay_element_option_id,
element_value,element_value_type,
currency_code,pay_basis
from psb_default_assignments
where default_rule_id = l_src_default_rule_id;
Select name
from psb_pay_elements
where data_extract_id = p_src_data_extract_id
and pay_element_id = l_src_pay_element_id;
Select pay_element_id
from psb_pay_elements
where data_extract_id = p_data_extract_id
and name = l_src_pay_element_name;
SELECT name, nvl(grade_step, -1) grade_step
FROM psb_pay_element_options
WHERE pay_element_id = l_src_pay_element_id
AND pay_element_option_id = l_src_pay_option_id;
SELECT pay_element_option_id
FROM psb_pay_element_options peo, psb_pay_elements pe
WHERE peo.pay_element_id = pe.pay_element_id
AND peo.pay_element_id = l_dest_pay_element_id
AND peo.name = l_src_pay_option_name
AND DECODE(pe.hr_element_type_id, null, -1, DECODE(pe.salary_type,
'STEP', peo.grade_step, -1)) = l_src_grade_step;
Select attribute_value
from psb_attribute_values
where data_extract_id = p_src_data_extract_id
and attribute_value_id = l_src_attribute_value_id;
Select attribute_value_id
from psb_attribute_values
where data_extract_id = p_data_extract_id
and attribute_value = l_src_attribute_value;
Select allocation_rule_id,
period_num,
monthly,
quarterly,
semi_annual
from psb_allocrule_percents_v
where allocation_rule_id = l_src_entity_id;
Select account_position_set_id
from psb_account_position_sets
where name = l_set_name
and data_extract_id = p_data_extract_id;
SELECT *
FROM psb_entity_set
WHERE data_extract_id = p_src_data_extract_id
AND entity_type = 'DEFAULT_RULE';
SELECT *
FROM psb_entity_assignment
WHERE entity_set_id = l_entity_set_id;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_default_tbl.DELETE;
Select count(*) into l_default_count
from psb_defaults
where name = C_Defaults_Rec.name
and data_extract_id = p_data_extract_id;
SELECT psb_entity_s.nextval
INTO l_entity_id
FROM dual;
INSERT INTO psb_entity
(entity_id,
entity_type,
entity_subtype,
name,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
VALUES( l_entity_id,
'ALLOCRULE',
'POSITION',
l_entity_id,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
Select psb_defaults_s.nextval
into l_default_rule_id
from dual;
PSB_DEFAULTS_PVT.Insert_Row(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
--
p_row_id => l_rowid1,
p_default_rule_id => l_default_rule_id,
p_name => C_Defaults_Rec.name,
p_global_default_flag => C_Defaults_Rec.global_default_flag,
p_data_extract_id => p_data_extract_id,
p_business_group_id => C_Defaults_Rec.business_group_id,
p_entity_id => l_entity_id,
p_priority => C_Defaults_Rec.priority,
p_creation_date => l_creation_date,
p_created_by => l_created_by,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
/* Bug 4179734 Start */
p_overwrite => c_defaults_rec.overwrite
/* Bug 4179734 End*/
);
PSB_Set_Relation_PVT.Insert_Row
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_Row_Id => l_rowid2,
p_Set_Relation_Id => l_set_relation_id,
p_Account_Position_Set_Id => C_Account_Sets_Rec.account_position_set_id,
p_Allocation_Rule_Id => null,
p_Budget_Group_Id => null,
p_Budget_Workflow_Rule_Id => null,
p_Constraint_Id => null,
p_Default_Rule_Id => l_default_rule_id,
p_Parameter_Id => null,
p_Position_Set_Group_Id => null,
/* Budget Revision Rules Enhancement Start */
p_rule_id => null,
p_apply_balance_flag => null,
/* Budget Revision Rules Enhancement End */
p_Effective_Start_Date => C_Position_Sets_Rec.effective_start_date,
p_Effective_End_Date => C_Position_Sets_Rec.effective_end_date,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date
);
l_insert_flag := 'Y';
select psb_default_assignments_s.nextval
into l_default_assignment_id
from dual;
l_insert_flag := 'N';
if (l_insert_flag = 'Y') then
PSB_DEFAULT_ASSIGNMENTS_PVT.Insert_Row
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_Row_Id => l_rowid3,
p_default_assignment_id => l_default_assignment_id,
p_default_rule_id => l_default_rule_id,
p_assignment_type => C_Def_Assign_Rec.assignment_type,
p_attribute_id => C_Def_Assign_Rec.attribute_id,
p_attribute_value_id => l_dest_attribute_value_id,
p_attribute_value => C_Def_Assign_Rec.attribute_value,
p_pay_element_id => l_dest_pay_element_id,
p_pay_basis => C_Def_Assign_Rec.pay_basis,
p_element_value_type => C_Def_Assign_Rec.element_value_type,
p_pay_element_option_id => l_dest_pay_option_id,
p_element_value => C_Def_Assign_Rec.element_value,
p_currency_code => C_Def_Assign_Rec.currency_code,
p_creation_date => l_creation_date,
p_created_by => l_created_by,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login
);
select psb_default_account_distrs_s.nextval
into l_account_distribution_id
from dual;
Insert into Psb_default_account_distrs
(account_distribution_id,
default_rule_id,
chart_of_accounts_id,
code_combination_id,
distribution_percent,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date )
VALUES
(
l_account_distribution_id,
l_default_rule_id,
C_Def_Distr_Rec.chart_of_accounts_id,
C_Def_Distr_Rec.code_combination_id,
C_Def_Distr_Rec.distribution_percent,
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date);
PSB_ALLOCRULE_PERCENTS_PVT.Insert_Row
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_allocation_rule_percent_id => l_alloc_rule_percent_id,
p_allocation_rule_id => l_entity_id,
p_period_num => C_Alloc_Rec.period_num,
p_monthly => C_Alloc_Rec.monthly,
p_quarterly => C_Alloc_Rec.quarterly ,
p_semi_annual => C_Alloc_Rec.semi_annual,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_context => null,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date);
FOR l_new_entity_rec IN (SELECT PSB_ENTITY_SET_S.NEXTVAL entity_set_id
FROM dual)
LOOP
l_new_entity_set_id := l_new_entity_rec.entity_set_id;
FOR l_data_extract_rec IN (SELECT business_group_id,
set_of_books_id
FROM psb_data_extracts
WHERE data_extract_id = p_data_extract_id)
LOOP
l_business_group_id := l_data_extract_rec.business_group_id;
PSB_ENTITY_SET_PVT.Insert_Row
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
P_ROWID => l_rowid4,
P_ENTITY_SET_ID => l_new_entity_set_id,
P_ENTITY_TYPE => 'DEFAULT_RULE',
P_NAME => l_message_name,
P_DESCRIPTION => l_entity_set_rec.description,
P_BUDGET_GROUP_ID => l_business_group_id,
P_SET_OF_BOOKS_ID => l_set_of_books_id,
P_DATA_EXTRACT_ID => p_data_extract_id,
P_CONSTRAINT_THRESHOLD => null,
P_ENABLE_FLAG => null,
-- bug 4273111. set the following in parameter
P_EXECUTABLE_FROM_POSITION => NVL(l_exec_from_position,'N'),
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_CONTEXT => null,
p_Last_Update_Date => l_last_update_date,
p_Last_Updated_By => l_last_updated_by,
p_Last_Update_Login => l_last_update_login,
p_Created_By => l_created_by,
p_Creation_Date => l_creation_date);
PSB_ENTITY_ASSIGNMENT_PVT.Insert_Row (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
P_ROWID => l_rowid5,
P_ENTITY_SET_ID => l_new_entity_set_id,
P_ENTITY_ID
=> l_default_tbl(l_entity_assignment_rec.entity_id),
P_PRIORITY => l_entity_assignment_rec.priority,
P_SEVERITY_LEVEL => l_entity_assignment_rec.severity_level,
P_EFFECTIVE_START_DATE
=> l_entity_assignment_rec.effective_start_date,
P_EFFECTIVE_END_DATE => l_entity_assignment_rec.effective_end_date,
p_Last_Update_Date => l_last_update_date,
p_Last_Updated_By => l_last_updated_by,
p_Last_Update_Login => l_last_update_login,
p_Created_By => l_created_by,
p_Creation_Date => l_creation_date);