[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
25-SEP-2000 ahanda 115.2 1416995 Adding delete for PL/SQL
table.
10-OCT-2000 ahanda 115.3 Added check to pick only the
selected employee if employee
ID is passed and only not null
run results.
02-FEB-2001 ahanda 115.4 1625762 Added check for business group
26-APR-2001 ahanda 115.5 1755126 Changed logic for Elment Sets.
22-MAY-2001 ahanda 115.6 Changed parameter in cursor
c_element_results to
cp_element_set_id from
cp_element_type_id when joining
to element_Set_id
26-JUN-2001 ahanda 115.7 1855697 Changed logic for populate PL/SQL
table with result_value to take
care of multiple element entries.
17-AUG-2001 ahanda 115.8 1918074 Changed cursor c_element_results
for performance.
04-DEC-2002 dsaxby 115.13 2692195 Nocopy changes.
18-DEC-2002 tclewis 115.12 2390994 Modifications to the element_extract
procedure, c_assignments cursor.
Broke out the cursor into 2 querries
to Reduce High Buffer gets.
05-AUG-2003 trugless 115.13 Replaced hardcoded text for
report headings and
gv_title with lookup to
FND_COMMON_LOOKUPS table using
hr_general.decode_fnd_comm_lookup
function
Modified c_element_results,
c_class_elements,c_set_elements,
and c_elements cursors to use the
PAY_ELELMENT_TYPES_F_TL table
instead of
PAY_ELELMENT_TYPES_F for reporting
name so translated value will be
used.
modified the c_element_results
to query the
PAY_INPUT_VALUES_F_TL table for
25-FEB-2003 ssmukher 115.14 2007614 Added a new cursor c_legislation_code
for handling the
changing of SSN to SIN in case
of CA legislation in the procedure
formated_static_header
16-JUN-2004 ahanda 115.15 3433727 Changed code to use ref cursor.
2007614 Changed cursor c_legislation_code to
use base table instead of view.
16-JUL-2004 schauhan 115.16 3731178 Changed cursor c_class_elements,c_set_elements
c_elements and query string
lv_element_result_query.
Now element name shall be shown if reporting
name is null. Also made changes to
lv_element_result_query so that new
garnishment elements are also processed.
19-JUL-2004 schauhan 115.18 3731178 Reverted back to version 115.16
20-JUL-2004 ahanda 115.19 3778025 Changes query lv_element_result_query
to use bind parameters. Also, removed
special login for Invol Calculator element.
10-SEP-2004 schauhan 115.20 3650988 Changed the size of the variable lv_employment_category_code
from Varchar2(10) to per_assignments_f.employment_category%type.
10-MAR-2005 rajeesha 115.21 4214739 Used Status Column in ltr_elements in Extract_element
to avoid the entries which are Replace
24-APR-2006 ppanda 115.23 5167072 Element Register Report was not picking up
any data for Secondary classification.
26-APR-2006 ppanda 115.23 5179163 Element Register Report was not having correct
28-JUN-2006 asasthan 115.24 5231257 Performance tuning added hints
08-AUG-2006 jdevasah 115.25 5229191 Added two parameters to cursors c_class_elements and
c_set_elements and added conditions to filter elements that are not
eligible for the given Element Report period.
13-DEC-2006 saurgupt 115.27 5684493 Changed the union clause to union all in lv_element_result_query.
With this the report will now sum up the values for multiple entries
of same report.
01-AUG-2007 vaprakas 115.29 6075462 Added a distinct clause and selected pay_run_results.run_result_id
in the cursor lv_element_result_query.
28-AUG-2008 keyazawa 115.30 7264010 Fixed lv_element_result_query to work properly
multiple entry, same reporting name, secondary class parameter
29-AUG-2008 keyazawa 115.31 Fixed lv_element_result_query to work properly
additional entry, retro pay entry
Fixed lv_element_status condition to exclude R, O, U
due to work properly override entry
*/
/************************************************************
** Local Package Variables
************************************************************/
gv_title VARCHAR2(100);
** can insert data and the label in this PL/SQL table which will
** be printed at the end of the report.
** The PL/SQL table which needs to be populated is
** LTR_ELEMENT_EXTRACT_DATA. This PL/SQL table is defined in the
** Package pay_element_extract_data_pkg (pyelerpd.pkh/pkb).
*****************************************************************/
PROCEDURE formated_static_header(
p_output_file_type in varchar2
,p_static_label1 out nocopy varchar2
,p_static_label2 out nocopy varchar2
,p_business_group_id in varchar2 -- Bug No : 2007614
)
IS
-- Bug No : 2007614
-- changed call to per_business_groups to the base table
cursor c_legislation_code is
select hoi_bg.org_information9
from hr_organization_information hoi_bg
where organization_id = p_business_group_id
and org_information_context = 'Business Group Information';
** can insert data and the label in this PL/SQL table which will
** be printed at the end of the report.
** The PL/SQL table which needs to be populated is
** LTR_ELEMENT_EXTRACT_DATA. This PL/SQL table is defined in the
** Package pay_element_extract_data_pkg (pyelerpd.pkh/pkb).
*****************************************************************/
PROCEDURE formated_static_data (
p_action_type in varchar2
,p_action_effective_date in date
,p_payroll_name in varchar2
,p_gre_name in varchar2
,p_org_name in varchar2
,p_location_code in varchar2
,p_emp_last_name in varchar2
,p_emp_first_name in varchar2
,p_emp_middle_names in varchar2
,p_emp_employee_number in varchar2
,p_emp_national_identifier in varchar2
,p_emp_date_of_birth in date
,p_gender in varchar2
,p_emp_original_date_of_hire in date
,p_emp_projected_start_date in date
,p_emp_user_person_type in varchar2
,p_assignment_number in varchar2
,p_assignment_status in varchar2
,p_employment_category in varchar2
,p_output_file_type in varchar2
,p_static_data1 out nocopy varchar2
,p_static_data2 out nocopy varchar2
)
IS
lv_format1 VARCHAR2(32000);
select petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = cp_element_set_id
and petr.include_or_exclude = 'I'
union all
select pet1.element_type_id
from pay_element_types_f pet1
where pet1.classification_id in
(select classification_id
from pay_ele_classification_rules
where element_set_id = cp_element_set_id)
and pet1.element_name not like '%Special Features'
and pet1.element_name not like '%Special Inputs'
and pet1.element_name not like '%Withholding'
and pet1.element_name not like '%Verifier'
and pet1.element_name not like '%Fees'
and pet1.element_name not like '%Priority'
minus
select petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = cp_element_set_id
and petr.include_or_exclude = 'E';
,p_selection_criteria in varchar2
,p_is_ele_set in varchar2
,p_element_set_id in number
,p_is_ele_class in varchar2
,p_element_classification_id in number
,p_is_ele in varchar2
,p_element_type_id in number
,p_payroll_id in number
,p_consolidation_set_id in number
,p_tax_unit_id in number
,p_organization_id in number
,p_location_id in number
,p_person_id in number
,p_assignment_set_id in number
,p_output_file_type in varchar2
)
IS
/************************************************************
** Cursor to get all the employee and assignment data.
** This cursor will return one row for each Assignment Action
** for the Selection parameter entered by the user in the SRS.
** the Assignment Action returned by this cursor is used to
** retreive the Elements processed and its Pay Value.
************************************************************/
cursor c_assignments (
cp_start_date in date
,cp_end_date in date
,cp_payroll_id in number
,cp_consolidation_set_id in number
,cp_organization_id in number
,cp_tax_unit_id in number
,cp_location_id in number
,cp_person_id in number
,cp_business_group_id in number
) is
select paa.assignment_action_id
,paa.tax_unit_id
,paf.assignment_id
,ppa.payroll_action_id
,ppf.person_id
,ppa.effective_date
,fcl.meaning
,pf.payroll_name
,ppf.last_name
,ppf.first_name
,ppf.middle_names
,ppf.employee_number
,ppf.national_identifier
,ppf.date_of_birth
,ppf.sex
,ppf.original_date_of_hire
,ppf.projected_start_date
,paf.assignment_number
,paf.employment_category
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf,
per_people_f ppf,
pay_payrolls_f pf,
fnd_common_lookups fcl
where ppa.action_type in ('R', 'Q', 'B', 'I', 'V')
and ppa.business_group_id = cp_business_group_id
--
and pf.payroll_id = ppa.payroll_id
and ppa.effective_date between pf.effective_start_date
and pf.effective_end_date
and pf.payroll_id like nvl(to_char(cp_payroll_id), '%')
and (cp_consolidation_set_id is null
or ppa.consolidation_set_id = cp_consolidation_set_id )
--
and ppa.effective_date between cp_start_date
and cp_end_date
and fcl.lookup_code = ppa.action_type
and fcl.lookup_type = 'ACTION_TYPE'
and fcl.application_id = 800
and fcl.enabled_flag = 'Y'
and paa.payroll_action_id = ppa.payroll_action_id
and (cp_tax_unit_id is null
or paa.tax_unit_id = cp_tax_unit_id)
and paf.assignment_id = paa.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and (cp_organization_id is null
or paf.organization_id = cp_organization_id)
and (cp_location_id is null
or paf.location_id = cp_location_id)
and ppf.person_id = paf.person_id
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and (cp_person_id is null
or ppf.person_id = cp_person_id)
order by ppf.last_name, ppf.first_name,
ppf.middle_names, ppa.effective_date;
select pet.reporting_name,
prrv.result_value
from pay_element_types_f pet,
pay_element_types_f_tl pettl,
pay_input_values_f piv,
pay_run_result_values prrv,
pay_run_results prr,
pay_input_values_f_tl pivtl
where pivtl.name = 'Pay Value'
and pivtl.language = 'US'
and pivtl.input_value_id = piv.input_value_id
and prrv.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and prrv.result_value is not null
and prr.run_result_id = prrv.run_result_id
and prr.assignment_action_id = cp_assignment_action_id
and pet.element_type_id = prr.element_type_id
and pettl.language = userenv('LANG')
and pettl.element_type_id = pet.element_type_id
and cp_effective_date between pet.effective_start_date
and pet.effective_end_date
and pet.classification_id like nvl(to_char(cp_classification_id), '%')
and pet.element_type_id like nvl(to_char(cp_element_type_id), '%')
and (cp_element_set_id is null
or (cp_element_set_id is not null
and prr.element_type_id in
(select petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = cp_element_set_id
and petr.include_or_exclude = 'I'
union all
select pet1.element_type_id
from pay_element_types_f pet1
where pet1.classification_id in
(select classification_id
from pay_ele_classification_rules
where element_set_id = cp_element_set_id)
minus
select petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = cp_element_set_id
and petr.include_or_exclude = 'E'
)
)
);
select pet.reporting_name,
prrv.result_value
from pay_element_types_f pet,
pay_input_values_f piv,
pay_run_result_values prrv,
pay_run_results prr
where piv.name = 'Pay Value'
and prrv.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and prrv.result_value is not null
and prr.run_result_id = prrv.run_result_id
and prr.assignment_action_id = cp_assignment_action_id
and pet.element_type_id = prr.element_type_id
and cp_effective_date between pet.effective_start_date
and pet.effective_end_date
and pet.classification_id like nvl(to_char(cp_classification_id), '%')
and pet.element_type_id like nvl(to_char(cp_element_type_id), '%')
and (cp_element_set_id is null
or (cp_element_set_id is not null
and prr.element_type_id in
(select petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = cp_element_set_id
and petr.include_or_exclude = 'I'
union all
select pet1.element_type_id
from pay_element_types_f pet1
where pet1.classification_id in
(select classification_id
from pay_ele_classification_rules
where element_set_id = cp_element_set_id)
minus
select petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = cp_element_set_id
and petr.include_or_exclude = 'E'
)
)
);
select distinct nvl(pettl.reporting_name,pettl.element_name)
from pay_element_types_f pet,
pay_element_types_f_tl pettl
where pet.classification_id = cp_element_classification_id
and cp_business_group_id = nvl(pet.business_group_id, cp_business_group_id)
and pet.element_name not like '%Special Features'
and pet.element_name not like '%Special Inputs'
and pet.element_name not like '%Withholding'
and pet.element_name not like '%Verifier'
and pet.element_name not like '%Fees'
and pet.element_name not like '%Priority'
and pettl.language = userenv('LANG')
and pettl.element_type_id = pet.element_type_id
and pet.effective_start_date <= cp_end_date --bug # 5229191
and pet.effective_end_date >= cp_start_date
/* Added to fix Bug # 5167072
START */
UNION ALL
select distinct nvl(pettl.reporting_name,pettl.element_name)
from pay_element_types_f pet,
pay_element_types_f_tl pettl,
PAY_SUB_CLASSIFICATION_RULES_F scr,
pay_element_classifications pec
where scr.element_type_id = pet.element_type_id
and pec.classification_id = cp_element_classification_id
and pec.classification_id = scr.classification_id
and cp_business_group_id = nvl(pet.business_group_id,
cp_business_group_id)
and pet.element_name not like '%Special Features'
and pet.element_name not like '%Special Inputs'
and pet.element_name not like '%Withholding'
and pet.element_name not like '%Verifier'
and pet.element_name not like '%Fees'
and pet.element_name not like '%Priority'
and pettl.language = userenv('LANG')
and pettl.element_type_id = pet.element_type_id
and pet.effective_start_date <= cp_end_date --bug # 5229191
and pet.effective_end_date >= cp_start_date
/* END of fix Bug # 5167072 */
order by 1;
select distinct nvl(pettl.reporting_name,pettl.element_name)
from pay_element_type_rules petr,
pay_element_types_f pet,
pay_element_types_f_tl pettl
where pet.element_type_id = petr.element_type_id
and petr.element_set_id = cp_ele_set_id
and petr.include_or_exclude = 'I'
and pettl.language = userenv('LANG')
and pettl.element_type_id = pet.element_type_id
and pet.effective_start_date <= cp_end_date --bug # 5229191
and pet.effective_end_date >= cp_start_date
union all
select distinct nvl(pettl1.reporting_name,pettl1.element_name)
from pay_element_types_f pet1,
pay_element_types_f_tl pettl1
where cp_business_group_id = nvl(pet1.business_group_id, cp_business_group_id)
and pet1.classification_id in
(select classification_id
from pay_ele_classification_rules
where element_set_id = cp_ele_set_id)
and pet1.element_name not like '%Special Features'
and pet1.element_name not like '%Special Inputs'
and pet1.element_name not like '%Withholding'
and pet1.element_name not like '%Verifier'
and pet1.element_name not like '%Fees'
and pet1.element_name not like '%Priority'
and pettl1.language = userenv('LANG')
and pettl1.element_type_id = pet1.element_type_id
and pet1.effective_start_date <= cp_end_date --bug # 5229191
and pet1.effective_end_date >= cp_start_date
minus
select distinct nvl(pettl.reporting_name,pettl.element_name)
from pay_element_type_rules petr,
pay_element_types_f_tl pettl
where pettl.element_type_id = petr.element_type_id
and petr.element_set_id = cp_ele_set_id
and petr.include_or_exclude = 'E'
and pettl.language = userenv('LANG')
order by 1; -- reporting_name;
select distinct nvl(pettl.reporting_name,pettl.element_name)
from pay_element_types_f_tl pettl
where pettl.element_type_id = cp_ele_type_id
and pettl.language = userenv('LANG');
select fcl.meaning
from fnd_common_lookups fcl
where fcl.lookup_type = 'EMP_CAT'
and fcl.lookup_code = cp_lookup_code;
select hou_org.name
,hl.location_code
,ppt.user_person_type
,past.user_status
into lv_org_name
,lv_location_code
,lv_emp_user_person_type
,lv_assignment_status
from per_person_types ppt,
per_people_f ppf,
hr_locations_all hl,
hr_organization_units hou_org,
per_assignment_status_types past,
per_assignments_f paf
where paf.assignment_id = ln_assignment_id
and ld_effective_date between paf.effective_start_date
and paf.effective_end_date
and hou_org.organization_id = paf.organization_id
and past.assignment_status_type_id = paf.assignment_status_type_id
and hl.location_id = paf.location_id
and ppf.person_id = paf.person_id
and ld_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppt.person_type_id = ppf.person_type_id;
select hou_gre.name
into lv_gre_name
from hr_organization_units hou_gre
where hou_gre.organization_id = lv_tax_unit_id;
' select /*+ leading(pet) */
distinct
nvl(pet.reporting_name,pet.element_name),
prrv.result_value,
prr.Status,
decode(prr.entry_type,''E'',decode(pet.multiple_entries_allowed_flag,''Y'',prr.run_result_id,-1),prr.run_result_id) run_result_id
from pay_element_types_f pet,
pay_element_types_f_tl pettl,
pay_input_values_f piv,
pay_run_result_values prrv,
pay_run_results prr,
pay_input_values_f_tl pivtl,
pay_element_classifications pec
where pivtl.name = ''Pay Value''
and pivtl.language = ''US''
and pivtl.input_value_id = piv.input_value_id
and prrv.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and prrv.result_value is not null
and prr.run_result_id = prrv.run_result_id
and prr.assignment_action_id = :cp_assignment_action_id
and pet.classification_id = pec.classification_id
and pet.element_type_id = prr.element_type_id
and pettl.language = userenv(''LANG'')
and pettl.element_type_id = pet.element_type_id
and :cp_effective_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_name not like ''%Special Features''
and pet.element_name not like ''%Special Inputs''
and pet.element_name not like ''%Withholding''
and pet.element_name not like ''%Fees''
and pet.element_name not like ''%Verifier''
and pet.element_name not like ''%Priority''
and '
|| lv_element_cls_where_clause
|| ' and ' || lv_element_id_where_clause
|| ' and ' || lv_element_set_where_clause
|| '
UNION
select /*+ leading(pet) */
distinct
nvl(pet.reporting_name,pet.element_name),
prrv.result_value,
prr.Status,
decode(prr.entry_type,''E'',decode(pet.multiple_entries_allowed_flag,''Y'',prr.run_result_id,-1),prr.run_result_id) run_result_id
from pay_element_types_f pet,
pay_element_types_f_tl pettl,
pay_input_values_f piv,
pay_run_result_values prrv,
pay_run_results prr,
pay_input_values_f_tl pivtl,
pay_element_classifications pec,
pay_sub_classification_rules_f scr
where pivtl.name = ''Pay Value''
and pivtl.language = ''US''
and pivtl.input_value_id = piv.input_value_id
and prrv.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and prrv.result_value is not null
and prr.run_result_id = prrv.run_result_id
and prr.assignment_action_id = :cp_assignment_action_id
and scr.classification_id = pec.classification_id
and scr.element_type_id = pet.element_type_id
and pet.element_type_id = prr.element_type_id
and pettl.language = userenv(''LANG'')
and pettl.element_type_id = pet.element_type_id
and :cp_effective_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_name not like ''%Special Features''
and pet.element_name not like ''%Special Inputs''
and pet.element_name not like ''%Withholding''
and pet.element_name not like ''%Fees''
and pet.element_name not like ''%Verifier''
and pet.element_name not like ''%Priority''
and '
|| lv_element_cls_where_clause2
|| ' and ' || lv_element_id_where_clause
|| ' and ' || lv_element_set_where_clause
;
update fnd_concurrent_requests
set output_file_type = 'HTML'
where request_id = FND_GLOBAL.CONC_REQUEST_ID ;