[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'M'
FROM FND_SEGMENT_ATTRIBUTE_VALUES sa2,
FND_ID_FLEX_SEGMENTS fs,
FND_SEGMENT_ATTRIBUTE_VALUES sa1
WHERE sa1.id_flex_num = p_cost_id_flex_num
and sa1.id_flex_code = 'COST'
and sa1.attribute_value = 'Y'
and sa1.segment_attribute_type <> 'BALANCING'
and fs.id_flex_num = p_cost_id_flex_num
and fs.id_flex_code = 'COST'
and fs.required_flag = 'Y'
and fs.enabled_flag = 'Y'
and fs.application_id = 801
and fs.application_column_name =
sa1.application_column_name
and sa2.id_flex_num = p_cost_id_flex_num
and sa2.id_flex_code = 'COST'
and sa2.attribute_value = 'Y'
and sa1.application_id = fs.application_id
and sa2.segment_attribute_type <> 'BALANCING'
and sa2.application_id = fs.application_id
and sa2.application_column_name =
sa1.application_column_name
and sa1.segment_attribute_type <> sa2.segment_attribute_type
and sa1.segment_attribute_type = p_level
UNION ALL
/*
** also return a row if no segments have not been qualified at
** passed in lvl,
** avoids nasty ff error msg
*/
SELECT 'N'
FROM DUAL
WHERE NOT EXISTS
(SELECT 'Y'
FROM FND_SEGMENT_ATTRIBUTE_VALUES sa
WHERE sa.id_flex_num = p_cost_id_flex_num
and sa.id_flex_code = 'COST'
and sa.application_id = 801
and sa.segment_attribute_type = p_level
and sa.attribute_value = 'Y'
)
UNION ALL
/*
** special check for element link form,
** if no balancing segments have been defined at this level
** then return a row
*/
SELECT 'B'
FROM DUAL
WHERE p_level = 'ELEMENT'
and NOT EXISTS
(SELECT 'Y'
FROM FND_SEGMENT_ATTRIBUTE_VALUES sa
WHERE sa.id_flex_num = p_cost_id_flex_num
and sa.id_flex_code = 'COST'
and sa.application_id = 801
and sa.segment_attribute_type = 'BALANCING'
and sa.attribute_value = 'Y'
)
;
SELECT count(*)
INTO l_num_reqs_in_this_level
FROM FND_ID_FLEX_SEGMENTS fs,
FND_SEGMENT_ATTRIBUTE_VALUES sa1
WHERE sa1.id_flex_num = p_cost_id_flex_num
and sa1.id_flex_code = 'COST'
and sa1.application_id = fs.application_id
and sa1.attribute_value = 'Y'
and sa1.segment_attribute_type <> 'BALANCING'
and sa1.segment_attribute_type = p_level
and fs.id_flex_num = p_cost_id_flex_num
and fs.id_flex_code = 'COST'
and fs.required_flag = 'Y'
and fs.enabled_flag = 'Y'
and fs.application_id = 801
and fs.application_column_name =
sa1.application_column_name;
SELECT decode(attribute_value, 'Y', p_segment_value, null)
INTO p_segment_value
FROM FND_SEGMENT_ATTRIBUTE_VALUES sa1
WHERE sa1.id_flex_num = p_cost_id_flex_num
AND sa1.id_flex_code = 'COST'
AND sa1.application_id = 801
AND sa1.application_column_name = p_segment
AND sa1.segment_attribute_type = p_level;
select 'Y'
from fnd_id_flex_segments
where application_id = 801
and id_flex_num = p_cost_id_flex_num
and id_flex_code = 'COST'
and application_column_name = p_segment_name
and enabled_flag = 'Y'
and display_flag = 'Y';
select fnd_flex_val_util.to_display_value (
p_value,
format_type,
flex_value_set_name,
maximum_size,
number_precision,
alphanumeric_allowed_flag,
uppercase_only_flag,
'N',
maximum_value,
minimum_value ) display_value
from fnd_flex_value_sets
where flex_value_set_id = p_value_set_id;
select decode (
p_segment_name
, 'SEGMENT1', nvl(E.segment1, nvl(A.segment1, nvl(O.segment1, nvl(L.segment1, P.segment1))))
, 'SEGMENT2', nvl(E.segment2, nvl(A.segment2, nvl(O.segment2, nvl(L.segment2, P.segment2))))
, 'SEGMENT3', nvl(E.segment3, nvl(A.segment3, nvl(O.segment3, nvl(L.segment3, P.segment3))))
, 'SEGMENT4', nvl(E.segment4, nvl(A.segment4, nvl(O.segment4, nvl(L.segment4, P.segment4))))
, 'SEGMENT5', nvl(E.segment5, nvl(A.segment5, nvl(O.segment5, nvl(L.segment5, P.segment5))))
, 'SEGMENT6', nvl(E.segment6, nvl(A.segment6, nvl(O.segment6, nvl(L.segment6, P.segment6))))
, 'SEGMENT7', nvl(E.segment7, nvl(A.segment7, nvl(O.segment7, nvl(L.segment7, P.segment7))))
, 'SEGMENT8', nvl(E.segment8, nvl(A.segment8, nvl(O.segment8, nvl(L.segment8, P.segment8))))
, 'SEGMENT9', nvl(E.segment9, nvl(A.segment9, nvl(O.segment9, nvl(L.segment9, P.segment9))))
, 'SEGMENT10', nvl(E.segment10, nvl(A.segment10, nvl(O.segment10, nvl(L.segment10, P.segment10))))
, 'SEGMENT11', nvl(E.segment11, nvl(A.segment11, nvl(O.segment11, nvl(L.segment11, P.segment11))))
, 'SEGMENT12', nvl(E.segment12, nvl(A.segment12, nvl(O.segment12, nvl(L.segment12, P.segment12))))
, 'SEGMENT13', nvl(E.segment13, nvl(A.segment13, nvl(O.segment13, nvl(L.segment13, P.segment13))))
, 'SEGMENT14', nvl(E.segment14, nvl(A.segment14, nvl(O.segment14, nvl(L.segment14, P.segment14))))
, 'SEGMENT15', nvl(E.segment15, nvl(A.segment15, nvl(O.segment15, nvl(L.segment15, P.segment15))))
, 'SEGMENT16', nvl(E.segment16, nvl(A.segment16, nvl(O.segment16, nvl(L.segment16, P.segment16))))
, 'SEGMENT17', nvl(E.segment17, nvl(A.segment17, nvl(O.segment17, nvl(L.segment17, P.segment17))))
, 'SEGMENT18', nvl(E.segment18, nvl(A.segment18, nvl(O.segment18, nvl(L.segment18, P.segment18))))
, 'SEGMENT19', nvl(E.segment19, nvl(A.segment19, nvl(O.segment19, nvl(L.segment19, P.segment19))))
, 'SEGMENT20', nvl(E.segment20, nvl(A.segment20, nvl(O.segment20, nvl(L.segment20, P.segment20))))
, 'SEGMENT21', nvl(E.segment21, nvl(A.segment21, nvl(O.segment21, nvl(L.segment21, P.segment21))))
, 'SEGMENT22', nvl(E.segment22, nvl(A.segment22, nvl(O.segment22, nvl(L.segment22, P.segment22))))
, 'SEGMENT23', nvl(E.segment23, nvl(A.segment23, nvl(O.segment23, nvl(L.segment23, P.segment23))))
, 'SEGMENT24', nvl(E.segment24, nvl(A.segment24, nvl(O.segment24, nvl(L.segment24, P.segment24))))
, 'SEGMENT25', nvl(E.segment25, nvl(A.segment25, nvl(O.segment25, nvl(L.segment25, P.segment25))))
, 'SEGMENT26', nvl(E.segment26, nvl(A.segment26, nvl(O.segment26, nvl(L.segment26, P.segment26))))
, 'SEGMENT27', nvl(E.segment27, nvl(A.segment27, nvl(O.segment27, nvl(L.segment27, P.segment27))))
, 'SEGMENT28', nvl(E.segment28, nvl(A.segment28, nvl(O.segment28, nvl(L.segment28, P.segment28))))
, 'SEGMENT29', nvl(E.segment29, nvl(A.segment29, nvl(O.segment29, nvl(L.segment29, P.segment29))))
, 'SEGMENT30', nvl(E.segment30, nvl(A.segment30, nvl(O.segment30, nvl(L.segment30, P.segment30))))
, null ) segment_value
from pay_cost_allocation_keyflex E,
pay_cost_allocation_keyflex A,
pay_cost_allocation_keyflex O,
pay_cost_allocation_keyflex L,
pay_cost_allocation_keyflex P,
pay_element_links_f EL,
hr_all_organization_units OU,
pay_payrolls_f PP,
pay_element_entries_f EE,
(
select ASG1.assignment_id,
ASG1.payroll_id,
ASG1.organization_id,
CA.cost_allocation_keyflex_id
from per_all_assignments_f ASG1,
pay_cost_allocations_f CA
where ASG1.assignment_id = CA.assignment_id (+)
and ASG1.assignment_id = p_assignment_id
and nvl( CA.cost_allocation_id, -1) = nvl( p_cost_allocation_id, -1)
and p_effective_date between ASG1.effective_start_date
and ASG1.effective_end_date
and p_effective_date between nvl(CA.effective_start_date,p_effective_date)
and nvl(CA.effective_end_date,p_effective_date)
union all
select to_number(ASG3.assignment_id) assignment_id,
to_number(ASG3.payroll_id) payroll_id,
to_number(ASG3.organization_id) organization_id,
to_number(null) cost_allocation_keyflex_id
from per_all_assignments_f ASG3
where ASG3.assignment_id = p_assignment_id
and p_effective_date between ASG3.effective_start_date and ASG3.effective_end_date
and not exists (
select 'X'
from pay_cost_allocations_f CA2
where CA2.assignment_id = ASG3.assignment_id
and p_effective_date between CA2.effective_start_date and CA2.effective_end_date
)
) ASG2
where ASG2.assignment_id = EE.assignment_id
and EE.element_link_id = EL.element_link_id
and EE.element_entry_id = p_element_entry_id
and ASG2.payroll_id = PP.payroll_id
and ASG2.organization_id = OU.organization_id
and EE.cost_allocation_keyflex_id = E.cost_allocation_keyflex_id (+)
and ASG2.cost_allocation_keyflex_id = A.cost_allocation_keyflex_id (+)
and OU.cost_allocation_keyflex_id = O.cost_allocation_keyflex_id (+)
and EL.cost_allocation_keyflex_id = L.cost_allocation_keyflex_id (+)
and PP.cost_allocation_keyflex_id = P.cost_allocation_keyflex_id (+)
and p_effective_date between EE.effective_start_date and EE.effective_end_date
and p_effective_date between EL.effective_start_date and EL.effective_end_date
and p_effective_date between PP.effective_start_date and PP.effective_end_date
and EL.costable_type = 'C'
union all
select decode (
p_segment_name
, 'SEGMENT1', nvl(E.segment1, nvl(L.segment1, P.segment1))
, 'SEGMENT2', nvl(E.segment2, nvl(L.segment2, P.segment2))
, 'SEGMENT3', nvl(E.segment3, nvl(L.segment3, P.segment3))
, 'SEGMENT4', nvl(E.segment4, nvl(L.segment4, P.segment4))
, 'SEGMENT5', nvl(E.segment5, nvl(L.segment5, P.segment5))
, 'SEGMENT6', nvl(E.segment6, nvl(L.segment6, P.segment6))
, 'SEGMENT7', nvl(E.segment7, nvl(L.segment7, P.segment7))
, 'SEGMENT8', nvl(E.segment8, nvl(L.segment8, P.segment8))
, 'SEGMENT9', nvl(E.segment9, nvl(L.segment9, P.segment9))
, 'SEGMENT10', nvl(E.segment10, nvl(L.segment10, P.segment10))
, 'SEGMENT11', nvl(E.segment11, nvl(L.segment11, P.segment11))
, 'SEGMENT12', nvl(E.segment12, nvl(L.segment12, P.segment12))
, 'SEGMENT13', nvl(E.segment13, nvl(L.segment13, P.segment13))
, 'SEGMENT14', nvl(E.segment14, nvl(L.segment14, P.segment14))
, 'SEGMENT15', nvl(E.segment15, nvl(L.segment15, P.segment15))
, 'SEGMENT16', nvl(E.segment16, nvl(L.segment16, P.segment16))
, 'SEGMENT17', nvl(E.segment17, nvl(L.segment17, P.segment17))
, 'SEGMENT18', nvl(E.segment18, nvl(L.segment18, P.segment18))
, 'SEGMENT19', nvl(E.segment19, nvl(L.segment19, P.segment19))
, 'SEGMENT20', nvl(E.segment20, nvl(L.segment20, P.segment20))
, 'SEGMENT21', nvl(E.segment21, nvl(L.segment21, P.segment21))
, 'SEGMENT22', nvl(E.segment22, nvl(L.segment22, P.segment22))
, 'SEGMENT23', nvl(E.segment23, nvl(L.segment23, P.segment23))
, 'SEGMENT24', nvl(E.segment24, nvl(L.segment24, P.segment24))
, 'SEGMENT25', nvl(E.segment25, nvl(L.segment25, P.segment25))
, 'SEGMENT26', nvl(E.segment26, nvl(L.segment26, P.segment26))
, 'SEGMENT27', nvl(E.segment27, nvl(L.segment27, P.segment27))
, 'SEGMENT28', nvl(E.segment28, nvl(L.segment28, P.segment28))
, 'SEGMENT29', nvl(E.segment29, nvl(L.segment29, P.segment29))
, 'SEGMENT30', nvl(E.segment30, nvl(L.segment30, P.segment30))
, null) segment_value
from pay_cost_allocation_keyflex E,
pay_cost_allocation_keyflex L,
pay_cost_allocation_keyflex P,
pay_element_links_f EL,
pay_payrolls_f PP,
pay_element_entries_f EE,
per_all_assignments_f ASG
where ASG.assignment_id = EE.assignment_id
and ASG.assignment_id = p_assignment_id
and EE.element_link_id = EL.element_link_id
and EE.element_entry_id = p_element_entry_id
and ASG.payroll_id = PP.payroll_id
and EE.cost_allocation_keyflex_id = E.cost_allocation_keyflex_id (+)
and EL.cost_allocation_keyflex_id = L.cost_allocation_keyflex_id (+)
and PP.cost_allocation_keyflex_id = P.cost_allocation_keyflex_id (+)
and p_effective_date between ASG.effective_start_date and ASG.effective_end_date
and p_effective_date between EE.effective_start_date and EE.effective_end_date
and p_effective_date between EL.effective_start_date and EL.effective_end_date
and p_effective_date between PP.effective_start_date and PP.effective_end_date
and EL.costable_type = 'F';
update pay_cost_allocation_keyflex
set concatenated_segments = p_concat_segments
where cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;