[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_attribute_selection_type IN VARCHAR2
)
RETURN BOOLEAN ;
l_attribute_selection_type
psb_account_position_sets.attribute_selection_type%TYPE ;
SELECT account_position_set_id ,
attribute_selection_type
FROM psb_account_position_sets
WHERE account_or_position_type = 'P'
AND data_extract_id = NVL( l_data_extract_id, data_extract_id )
)
LOOP
--
-- Perform initilization. To be done for each position set.
--
Init;
l_set_rec.attribute_selection_type
)
THEN
--
-- The concurrent program is the only one which calls the API
-- without any argument. We need to release lock as soon as an
-- position set is exploded. Committing will also ensure that
-- rollback segments do not go out of bounds.
--
COMMIT WORK;
SELECT attribute_selection_type INTO l_attribute_selection_type
FROM psb_account_position_sets
WHERE account_position_set_id = p_position_set_id ;
l_attribute_selection_type
)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
SELECT data_extract_id ,
business_group_id
INTO
l_data_extract_id ,
l_business_group_id
FROM psb_positions
WHERE position_id = p_position_id ;
SELECT account_position_set_id ,
attribute_selection_type
FROM psb_account_position_sets
WHERE account_or_position_type = 'P'
AND data_extract_id = l_data_extract_id
)
LOOP
pd('The current set ' || l_set_rec.account_position_set_id );
IF l_set_rec.attribute_selection_type = 'A' THEN
l_match_found_in_set_flag := 'Y' ;
ELSIF l_set_rec.attribute_selection_type = 'O' THEN
l_match_found_in_set_flag := 'N' ;
SELECT line_sequence_id, attribute_id
FROM psb_account_position_set_lines
WHERE account_position_set_id = l_set_rec.account_position_set_id
)
LOOP
pd('Attribute id ' || l_set_lines_rec.attribute_id );
SELECT COUNT(*) INTO l_matching_attributes_count
FROM psb_position_assignments asgn ,
psb_position_set_line_values vals
WHERE vals.line_sequence_id = l_set_lines_rec.line_sequence_id
AND asgn.position_id = p_position_id
AND asgn.attribute_id = l_set_lines_rec.attribute_id
AND (
asgn.attribute_value_id = vals.attribute_value_id
OR
asgn.attribute_value = vals.attribute_value
)
/* Bug 4545909 Start */
AND ((worksheet_id IS NULL AND NOT EXISTS
(SELECT 1 FROM psb_position_assignments
WHERE worksheet_id = p_worksheet_id
AND attribute_id = asgn.attribute_id
AND position_id = asgn.position_id))
OR worksheet_id = p_worksheet_id
OR (worksheet_id IS NULL AND p_worksheet_id IS NULL))
AND ROWNUM < 2 ;
IF l_set_rec.attribute_selection_type = 'A' THEN
IF l_matching_attributes_count = 0 THEN
-- The position does not belong to the current position set.
-- ( The position needs to belong to every set line. )
l_match_found_in_set_flag := 'N';
ELSIF l_set_rec.attribute_selection_type = 'O' THEN
IF l_matching_attributes_count > 0 THEN
-- The position belongs to the current position set.
-- ( The position needs to belong to any set line. )
l_match_found_in_set_flag := 'Y';
INSERT INTO psb_budget_positions
(
account_position_set_id ,
position_id ,
data_extract_id ,
business_group_id ,
last_update_date ,
last_update_login ,
last_updated_by ,
created_by ,
creation_date
)
SELECT l_set_rec.account_position_set_id ,
p_position_id ,
l_data_extract_id ,
l_business_group_id ,
g_current_date ,
g_current_login_id ,
g_current_user_id ,
g_current_user_id ,
g_current_date
FROM dual
WHERE NOT EXISTS
( SELECT '1'
FROM psb_budget_positions
WHERE account_position_set_id
= l_set_rec.account_position_set_id
AND position_id = p_position_id
) ;
p_attribute_selection_type IN VARCHAR2
)
RETURN BOOLEAN
--
IS
-- Table to store position_set_lines and positions for a position set.
l_position_set_tbl position_set_tbl_type ;
l_last_update_date DATE;
SELECT data_extract_id ,
business_group_id
INTO
l_data_extract_id ,
l_business_group_id
FROM psb_account_position_sets
WHERE account_position_set_id = p_position_set_id ;
UPDATE psb_account_position_sets
SET maintain_status = 'C'
WHERE account_position_set_id = p_position_set_id ;
DELETE psb_budget_positions
WHERE account_position_set_id = p_position_set_id ;
l_position_set_tbl.DELETE ;
SELECT line_sequence_id ,
attribute_id ,
attribute_value_table_flag
FROM psb_acct_position_set_lines_v lines
WHERE account_position_set_id = p_position_set_id
ORDER BY lines.line_sequence_id
)
LOOP
--
-- Reset variable. This variable stores total number of positions in
-- the current set_line.
--
l_count_set_line_positions := 0 ;
SELECT COUNT(*) INTO l_tmp_count
FROM psb_position_set_line_values
WHERE line_sequence_id = l_set_line_rec.line_sequence_id ;
SELECT position_id
FROM psb_positions
WHERE data_extract_id = l_data_extract_id
MINUS
SELECT position_id
FROM psb_position_assignments
WHERE data_extract_id = l_data_extract_id
AND attribute_id = l_set_line_rec.attribute_id
)
LOOP
pd('Pos without attr assignment ' || l_position_rec.position_id );
SELECT attribute_value_id ,
attribute_value
FROM psb_position_set_line_values vals
WHERE line_sequence_id = l_set_line_rec.line_sequence_id
)
LOOP
l_attribute_value_id := l_val_rec.attribute_value_id ;
SELECT position_id
FROM psb_position_assignments
WHERE attribute_id = l_set_line_rec.attribute_id
AND data_extract_id = l_data_extract_id
AND ( attribute_value_id = l_attribute_value_id
OR
attribute_value = l_attribute_value
)
)
LOOP
pd('Pos ' || l_position_rec.position_id );
IF l_count_set_line_positions = 0 AND p_attribute_selection_type = 'A'
THEN
pd('Found a set_line with no positions');
pd('Sel Type ' || p_attribute_selection_type );
IF p_attribute_selection_type = 'O' THEN
--
-- The p_attribute_selection_type 'O' means pick up all the positions
-- matching at least one criteria. That means take union of positions
-- in l_position_set_tbl table.
--
FOR i IN 1..l_position_set_tbl.COUNT
LOOP
--
INSERT INTO psb_budget_positions
(
account_position_set_id ,
position_id ,
data_extract_id ,
business_group_id ,
last_update_date ,
last_update_login ,
last_updated_by ,
created_by ,
creation_date
)
SELECT g_position_set_id ,
l_position_set_tbl(i).position_id ,
l_data_extract_id ,
l_business_group_id ,
g_current_date ,
g_current_login_id ,
g_current_user_id ,
g_current_user_id ,
g_current_date
FROM dual
WHERE NOT EXISTS
( SELECT '1'
FROM psb_budget_positions
WHERE account_position_set_id = g_position_set_id
AND position_id =
l_position_set_tbl(i).position_id
) ;
ELSIF p_attribute_selection_type = 'A' THEN
--
-- The p_attribute_selection_type 'A' means pick up only those positions
-- matching all the criteria. That means take intersection of positions
-- in l_position_set_tbl table with respect to a set line.
--
--
-- Find all the positions in the first set_line.
--
IF l_position_set_tbl.EXISTS(1) THEN
l_first_line_sequence_id := l_position_set_tbl(1).line_sequence_id ;
l_position_input_tbl.DELETE ;
l_position_output_tbl.DELETE ;
-- have been processed. Update l_position_set_index and exit.
--
l_position_set_index := j + 1 ;
pd('insert output tbl Pos:'||l_current_position_id);
INSERT INTO psb_budget_positions
(
account_position_set_id ,
position_id ,
data_extract_id ,
business_group_id ,
last_update_date ,
last_update_login ,
last_updated_by ,
created_by ,
creation_date
)
SELECT g_position_set_id ,
l_position_output_tbl(i) ,
l_data_extract_id ,
l_business_group_id ,
g_current_date ,
g_current_login_id ,
g_current_user_id ,
g_current_user_id ,
g_current_date
FROM dual
WHERE NOT EXISTS
( SELECT '1'
FROM psb_budget_positions
WHERE account_position_set_id = g_position_set_id
AND position_id =
l_position_output_tbl(i)
) ;
END IF; /* End of p_attribute_selection_type clause */
UPDATE psb_account_position_sets
SET maintain_status = 'C' ,
last_maintained_date = g_current_date
WHERE account_position_set_id = p_position_set_id;