[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PROCEDURE Insert_Row |
+=======================================================================*/
PROCEDURE Insert_Row
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
--
p_row_id IN OUT NOCOPY VARCHAR2,
p_account_position_set_id IN OUT NOCOPY NUMBER,
p_name IN VARCHAR2,
p_set_of_books_id IN NUMBER,
p_use_in_budget_group_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_data_extract_id IN NUMBER,
p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
p_global_or_local_type IN VARCHAR2,
p_account_or_position_type IN VARCHAR2,
p_attribute_selection_type IN VARCHAR2,
p_business_group_id IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_created_by IN NUMBER,
p_creation_date IN DATE
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
SELECT rowid
FROM psb_account_position_sets
WHERE account_position_set_id = p_account_position_set_id;
SELECT psb_account_position_sets_s.nextval
FROM dual;
SAVEPOINT Insert_Row_Pvt ;
INSERT INTO psb_account_position_sets(
account_position_set_id,
name,
set_of_books_id,
use_in_budget_group_flag,
data_extract_id,
budget_group_id,
global_or_local_type,
account_or_position_type,
attribute_selection_type,
business_group_id,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date )
VALUES
(
p_account_position_set_id,
p_name,
p_set_of_books_id,
DECODE(p_use_in_budget_group_flag,
FND_API.G_MISS_CHAR, NULL,
p_use_in_budget_group_flag),
p_data_extract_id,
DECODE(p_budget_group_id,FND_API.G_MISS_NUM,null,p_budget_group_id),
p_global_or_local_Type,
p_account_or_position_Type,
p_attribute_selection_Type,
p_business_group_Id,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_created_by,
p_creation_date
);
ROLLBACK TO Insert_Row_Pvt ;
ROLLBACK TO Insert_Row_Pvt ;
ROLLBACK TO Insert_Row_Pvt ;
END Insert_Row;
p_attribute_selection_type IN VARCHAR2,
p_business_group_id IN NUMBER,
--
p_row_locked OUT NOCOPY VARCHAR2
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
SELECT *
FROM psb_account_position_sets
WHERE rowid = p_row_id
FOR UPDATE of Account_Position_Set_Id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
AND ( (Recinfo.attribute_selection_type = p_attribute_selection_type)
OR ( (Recinfo.attribute_selection_type IS NULL)
AND (p_attribute_selection_type IS NULL)))
AND ( (Recinfo.business_group_id = p_business_group_id)
OR (Recinfo.business_group_id IS NULL)
AND (p_business_group_id IS NULL)))
THEN
Null;
| PROCEDURE Update_Row |
+==========================================================================*/
PROCEDURE Update_Row
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
--
p_row_id IN VARCHAR2,
p_account_position_set_id IN NUMBER,
p_name IN VARCHAR2,
p_set_of_books_id IN NUMBER,
p_use_in_budget_group_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_data_extract_id IN NUMBER,
p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
p_global_or_local_type IN VARCHAR2,
p_account_or_position_type IN VARCHAR2,
p_attribute_selection_type IN VARCHAR2,
p_business_group_id IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
SAVEPOINT Update_Row_Pvt ;
UPDATE psb_account_position_sets
SET
account_position_set_id = p_account_position_set_id,
name = p_name,
set_of_books_id = p_set_of_books_id,
use_in_budget_group_flag = DECODE( p_use_in_budget_group_flag,
FND_API.G_MISS_CHAR, NULL,
p_use_in_budget_group_flag),
data_extract_id = p_data_extract_id,
budget_group_id = DECODE( p_budget_group_id,
FND_API.G_MISS_NUM,null,
p_budget_group_id),
global_or_local_type = p_global_or_local_type,
account_or_position_type = p_account_or_position_type,
attribute_selection_type = p_attribute_selection_type,
business_group_id = p_business_group_id,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE rowid = p_row_id;
ROLLBACK TO Update_Row_Pvt ;
ROLLBACK TO Update_Row_Pvt ;
ROLLBACK TO Update_Row_Pvt ;
END Update_Row;
| PROCEDURE Delete_Row |
+==========================================================================*/
PROCEDURE Delete_Row
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
--
p_row_id IN VARCHAR2
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
SAVEPOINT Delete_Row_Pvt ;
SELECT account_position_set_id INTO l_account_position_set_id
FROM psb_account_position_sets
WHERE rowid = p_row_id ;
SELECT rowid
FROM psb_account_position_set_lines
WHERE account_position_set_id = l_account_position_set_id
)
LOOP
--
PSB_Acct_Position_Set_Line_Pvt.Delete_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_lines_rec.rowid
);
DELETE FROM psb_account_position_sets
WHERE rowid = p_row_id;
ROLLBACK TO Delete_Row_Pvt ;
ROLLBACK TO Delete_Row_Pvt ;
ROLLBACK TO Delete_Row_Pvt ;
END Delete_Row;
SELECT '1'
FROM psb_account_position_sets
WHERE name = p_name
AND account_or_position_type = p_account_or_position_type
AND ( p_data_extract_id IS NULL
OR
data_extract_id = p_data_extract_id
)
AND (
p_row_id IS NULL
OR
rowid <> p_row_id
);
SELECT '1'
FROM psb_set_relations
WHERE account_position_set_id = p_account_position_set_id;
SELECT '1'
FROM psb_budget_groups
WHERE root_budget_group = 'Y'
AND budget_group_type = 'R'
AND ( ps_account_position_set_id = p_account_position_set_id
OR
nps_account_position_set_id = p_account_position_set_id
) ;
SELECT '1'
FROM psb_budget_groups
WHERE root_budget_group = 'Y'
AND budget_group_type = 'R'
AND NVL(freeze_hierarchy_flag, 'N') = 'Y'
AND ( ps_account_position_set_id = p_account_position_set_id
OR
nps_account_position_set_id = p_account_position_set_id
) ;
SELECT NVL(use_in_budget_group_flag, 'N') INTO l_use_in_budget_group_flag
FROM psb_account_position_sets
WHERE account_position_set_id = p_account_position_set_id;
SELECT DECODE( bg.root_budget_group, 'Y', bg.budget_group_id,
bg.root_budget_group_id ) as root_budget_group_id
FROM psb_set_relations rel,
psb_budget_groups bg
WHERE rel.account_position_set_id = p_account_position_set_id
AND bg.budget_group_type = 'R'
AND bg.budget_group_id = rel.budget_group_id
)
LOOP
SELECT NVL(freeze_hierarchy_flag, 'N') into l_freeze_hierarchy_flag
FROM psb_budget_groups
WHERE budget_group_id = l_budget_group_csr.root_budget_group_id;
SELECT business_group_id
FROM psb_data_extracts
WHERE data_extract_id = p_source_data_extract_id ;
SELECT business_group_id
FROM psb_data_extracts
WHERE data_extract_id = p_target_data_extract_id ;
SELECT *
FROM psb_account_position_sets
WHERE account_or_position_type = 'P'
AND global_or_local_type = 'G'
AND data_extract_id = p_source_data_extract_id
)
LOOP
Copy_Position_Set
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
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_source_position_set_id => l_global_sets_rec.account_position_set_id ,
p_source_data_extract_id => p_source_data_extract_id,
p_target_data_extract_id => p_target_data_extract_id,
p_target_business_group_id => l_target_business_group_id,
p_new_position_set_id => l_new_position_set_id
) ;
SELECT sets.account_position_set_id
FROM psb_set_relations rels ,
psb_account_position_sets sets
WHERE sets.account_position_set_id = rels.account_position_set_id
AND sets.account_or_position_type = 'P'
AND sets.global_or_local_type = 'L'
AND sets.data_extract_id = p_source_data_extract_id
AND DECODE( p_entity_table(i) ,
'BWR', budget_workflow_rule_id,
'C', constraint_id,
'DR', default_rule_id,
'P', parameter_id,
'PSG', position_set_group_id
) IS NOT NULL
)
LOOP
--
Copy_Position_Set
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
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_source_position_set_id => l_local_sets_rec.account_position_set_id,
p_source_data_extract_id => p_source_data_extract_id,
p_target_data_extract_id => p_target_data_extract_id,
p_target_business_group_id => l_target_business_group_id,
p_new_position_set_id => l_new_position_set_id
) ;
SELECT *
FROM psb_account_position_sets
WHERE account_position_set_id = p_source_position_set_id ;
SELECT attribute_id
FROM psb_attributes_VL
WHERE business_group_id = p_target_business_group_id
AND name = c_name
AND allow_in_position_set_flag = 'Y' ;
SELECT attribute_value_id
FROM psb_attribute_values
WHERE data_extract_id = p_target_data_extract_id
AND attribute_id = l_target_attribute_id
AND attribute_value = c_attribute_value ;
SELECT count(*) INTO l_count
FROM psb_account_position_sets
WHERE account_position_set_id <> l_sets_rec.account_position_set_id
AND name = l_sets_rec.name
AND data_extract_id = p_target_data_extract_id ;
PSB_Account_Position_Set_Pvt.Insert_Row
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
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_row_id,
p_account_position_set_id => l_account_position_set_id,
p_name => l_sets_rec.name,
p_set_of_books_id => l_sets_rec.set_of_books_id,
p_data_extract_id => p_target_data_extract_id,
p_budget_group_id => l_sets_rec.budget_group_id,
p_global_or_local_type => l_sets_rec.global_or_local_type,
p_account_or_position_type => l_sets_rec.account_or_position_type,
p_attribute_selection_type => l_sets_rec.attribute_selection_type,
p_business_group_id => p_target_business_group_id,
p_last_update_date => l_current_date,
p_last_updated_by => l_current_user_id,
p_last_update_login => l_current_login_id,
p_created_by => l_current_user_id,
p_creation_date => l_current_date
);
SELECT *
FROM psb_acct_position_set_lines_v
WHERE account_position_set_id = p_source_position_set_id
)
LOOP
-- Find the matching attribute in the target data extract.
OPEN l_find_matching_attribute_csr ( l_lines_rec.attribute_name ) ;
PSB_Acct_Position_Set_Line_Pvt.Insert_Row
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
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_row_id,
p_line_sequence_id => l_line_sequence_id,
p_account_position_set_id => l_account_position_set_id,
p_description => l_lines_rec.description,
p_business_group_id => p_target_business_group_id,
p_attribute_id => l_target_attribute_id,
p_include_or_exclude_type => l_lines_rec.include_or_exclude_type,
p_segment1_low => l_lines_rec.segment1_low,
p_segment2_low => l_lines_rec.segment2_low,
p_segment3_low => l_lines_rec.segment3_low,
p_segment4_low => l_lines_rec.segment4_low,
p_segment5_low => l_lines_rec.segment5_low,
p_segment6_low => l_lines_rec.segment6_low,
p_segment7_low => l_lines_rec.segment7_low,
p_segment8_low => l_lines_rec.segment8_low,
p_segment9_low => l_lines_rec.segment9_low,
p_segment10_low => l_lines_rec.segment10_low,
p_segment11_low => l_lines_rec.segment11_low,
p_segment12_low => l_lines_rec.segment12_low,
p_segment13_low => l_lines_rec.segment13_low,
p_segment14_low => l_lines_rec.segment14_low,
p_segment15_low => l_lines_rec.segment15_low,
p_segment16_low => l_lines_rec.segment16_low,
p_segment17_low => l_lines_rec.segment17_low,
p_segment18_low => l_lines_rec.segment18_low,
p_segment19_low => l_lines_rec.segment19_low,
p_segment20_low => l_lines_rec.segment20_low,
p_segment21_low => l_lines_rec.segment21_low,
p_segment22_low => l_lines_rec.segment22_low,
p_segment23_low => l_lines_rec.segment23_low,
p_segment24_low => l_lines_rec.segment24_low,
p_segment25_low => l_lines_rec.segment25_low,
p_segment26_low => l_lines_rec.segment26_low,
p_segment27_low => l_lines_rec.segment27_low,
p_segment28_low => l_lines_rec.segment28_low,
p_segment29_low => l_lines_rec.segment29_low,
p_segment30_low => l_lines_rec.segment30_low,
p_segment1_high => l_lines_rec.segment1_high,
p_segment2_high => l_lines_rec.segment2_high,
p_segment3_high => l_lines_rec.segment3_high,
p_segment4_high => l_lines_rec.segment4_high,
p_segment5_high => l_lines_rec.segment5_high,
p_segment6_high => l_lines_rec.segment6_high,
p_segment7_high => l_lines_rec.segment7_high,
p_segment8_high => l_lines_rec.segment8_high,
p_segment9_high => l_lines_rec.segment9_high,
p_segment10_high => l_lines_rec.segment10_high,
p_segment11_high => l_lines_rec.segment11_high,
p_segment12_high => l_lines_rec.segment12_high,
p_segment13_high => l_lines_rec.segment13_high,
p_segment14_high => l_lines_rec.segment14_high,
p_segment15_high => l_lines_rec.segment15_high,
p_segment16_high => l_lines_rec.segment16_high,
p_segment17_high => l_lines_rec.segment17_high,
p_segment18_high => l_lines_rec.segment18_high,
p_segment19_high => l_lines_rec.segment19_high,
p_segment20_high => l_lines_rec.segment20_high,
p_segment21_high => l_lines_rec.segment21_high,
p_segment22_high => l_lines_rec.segment22_high,
p_segment23_high => l_lines_rec.segment23_high,
p_segment24_high => l_lines_rec.segment24_high,
p_segment25_high => l_lines_rec.segment25_high,
p_segment26_high => l_lines_rec.segment26_high,
p_segment27_high => l_lines_rec.segment27_high,
p_segment28_high => l_lines_rec.segment28_high,
p_segment29_high => l_lines_rec.segment29_high,
p_segment30_high => l_lines_rec.segment30_high,
p_context => l_lines_rec.context,
p_attribute1 => l_lines_rec.attribute1,
p_attribute2 => l_lines_rec.attribute2,
p_attribute3 => l_lines_rec.attribute3,
p_attribute4 => l_lines_rec.attribute4,
p_attribute5 => l_lines_rec.attribute5,
p_attribute6 => l_lines_rec.attribute6,
p_attribute7 => l_lines_rec.attribute7,
p_attribute8 => l_lines_rec.attribute8,
p_attribute9 => l_lines_rec.attribute9,
p_attribute10 => l_lines_rec.attribute10,
p_last_update_date => l_current_date,
p_last_updated_by => l_current_user_id,
p_last_update_login => l_current_login_id,
p_created_by => l_current_user_id,
p_creation_date => l_current_date
);
SELECT *
FROM psb_position_set_line_values_v
WHERE line_sequence_id = l_lines_rec.line_sequence_id
)
LOOP
--
-- We need to find matching attribute_value_id only when value_table
-- flag is 'Y', otherwise every value is good.
--
IF l_values_rec.attribute_value_table_flag = 'Y' THEN
-- Find the matching attribute_value_id.
-- ( The l_values_rec.attribute_value will be null. )
OPEN l_find_matching_value_csr ( l_values_rec.attribute_table_value );
PSB_Pos_Set_Line_Values_Pvt.Insert_Row
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
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_row_id,
p_value_sequence_id => l_value_sequence_id,
p_line_sequence_id => l_line_sequence_id,
p_attribute_value_id => l_target_attribute_value_id,
p_attribute_value => l_values_rec.attribute_value,
p_last_update_date => l_current_date,
p_last_updated_by => l_current_user_id,
p_last_update_login => l_current_login_id,
p_created_by => l_current_user_id,
p_creation_date => l_current_date
);