[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select round(SUM(nvl(fnd_number.canonical_to_number(fai.value),0)))
into l_bal_amt
from pay_assignment_actions paa,
pay_action_information pai,
ff_archive_items fai,
ff_archive_item_contexts fic,
ff_user_entities fue,
ff_contexts ffc,
pay_payroll_actions ppa
where paa.payroll_action_id = ye_payroll_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.serial_number = ye_person_id
and pai.action_context_id = paa.assignment_action_id
/*and pai.effective_date = ye_effective_date*/
and pai.action_information7 = fnd_date.date_to_canonical(ye_effective_date)
and fai.context1 = paa.assignment_action_id
and fai.archive_item_id = fic.archive_item_id
and fai.user_entity_id = fue.user_entity_id
and fic.context_id = ffc.context_id
and ffc.context_name ='TAX_UNIT_ID'
and ltrim(rtrim(fic.context)) in(
SELECT DISTINCT gre_node.entity_id
FROM per_gen_hierarchy_nodes gre_node,
per_gen_hierarchy_nodes le_node,
per_gen_hierarchy_versions hier_ver,
fnd_lookup_values flv
WHERE gre_node.node_type = 'MX GRE'
and gre_node.entity_id = fic.context
AND gre_node.business_group_id = ppa.business_group_id
--AND pay_mx_yrend_arch.gre_exists (gre_node.entity_id) = 1
AND le_node.node_type = 'MX LEGAL EMPLOYER'
AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
AND status = flv.lookup_code
AND flv.meaning = 'Active'
AND flv.LANGUAGE = 'US'
AND flv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
AND ye_effective_date BETWEEN hier_ver.date_from
AND NVL(hier_ver.date_to,
hr_general.end_of_time))
and fue.user_entity_name = ye_balance_name ;
SELECT DISTINCT
fue_live.user_entity_name
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb_attr,
pay_defined_balances pdb_call,
pay_balance_dimensions pbd,
ff_user_entities fue_live
WHERE pbad.attribute_name = 'Year End Balances'
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb_attr.defined_balance_id = pba.defined_balance_id
AND pdb_attr.balance_type_id = pdb_call.balance_type_id
AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
AND pbd.legislation_code = pbad.legislation_code
AND fue_live.creator_id = pdb_call.defined_balance_id
AND fue_live.creator_type = 'B'
ORDER BY fue_live.user_entity_name;
select nvl(action_information9,'N'),
decode(nvl(action_information9,'N'),'Y', round(nvl(to_number(action_information24),0)),0) ,
decode(nvl(action_information9,'N'),'Y', round(nvl(to_number(action_information25),0)),0) ,
decode(nvl(action_information9,'N'),'Y', round(nvl(to_number(action_information27),0)),0) ,
to_number(action_information8 )
from pay_assignment_actions paa,
pay_action_information pai
where paa.payroll_action_id = p_payroll_action_id
and paa.serial_number = p_person_id
--and pai.effective_date = p_effective_date
and pai.action_information7 = fnd_date.date_to_canonical(p_effective_date) /*Bug 8402505*/
and pai.action_context_id = paa.assignment_action_id
and pai.action_information_category='MX YREND EE DETAILS' ;