[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** 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_chk_ni_prt in varchar2 -- Bug 4142845
,p_business_group_id in varchar2 -- Bug 2007614
)
IS
--Bug 2007614
cursor c_legislation_code is
select legislation_code
from per_business_groups
where business_group_id = p_business_group_id;
** 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_consolidation_set_name in varchar2
,p_payroll_name in varchar2
,p_gre_name in varchar2
,p_emp_last_name in varchar2
,p_emp_first_name in varchar2
,p_emp_middle_names in varchar2
,p_action_effective_date in date
,p_element_name in varchar2
,p_input_value_name in varchar2
,p_uom in varchar2 -- Bug 3072270
,p_credit_amount in number
,p_debit_amount in number
,p_accrual_type in varchar2 --Bug 3179050
,p_concatenated_segments in varchar2
,p_org_name in varchar2
,p_location_code in varchar2
,p_emp_employee_number in varchar2
,p_emp_national_identifier in varchar2
,p_assignment_number in varchar2
,p_chk_ni_prt in varchar2 --Bug 4142845
,p_output_file_type in varchar2
,p_static_data1 out nocopy varchar2
,p_static_data2 out nocopy varchar2
)
IS
lv_format1 VARCHAR2(32000);
,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_cost_type in varchar2 --Bug 3179050
,p_output_file_type in varchar2
)
IS
/************************************************************
** Added by ssmukher for Bug 4142845
** Cursor to get the Legislation Code for the Business Group.
************************************************************/
cursor c_leg_code(cp_business_group in number) is
select legislation_code
from per_business_groups
where business_group_id = cp_business_group;
select nvl(rule_mode,'Y')
from pay_legislative_field_info
where field_name = 'NATIONAL_IDENTIFIER_PRT'
and rule_type = 'DISPLAY'
and legislation_code = cp_legislation_code;
select org_information7
from hr_organization_information hoi
where organization_id = cp_business_group_id
and org_information_context = 'Business Group Information';
select segment_name, application_column_name
from fnd_id_flex_segments
where id_flex_code = 'COST'
and id_flex_num = cp_id_flex_num
and enabled_flag = 'Y'
and display_flag = 'Y'
order by segment_num;
'select pcd.cost_type
,pcd.consolidation_set_name
,pcd.payroll_name
,pcd.gre_name
,pcd.organization_name
,pcd.location_code
,pcd.last_name
,pcd.first_name
,pcd.middle_names
,pcd.employee_number
,pcd.assignment_number
,nvl(pcd.reporting_name,pcd.element_name)
,pcd.input_value_name
,pcd.uom
,pcd.credit_amount
,pcd.debit_amount
,pcd.national_identifier
,pcd.effective_date
,pcd.concatenated_segments
,pcd.assignment_id
,pcd.segment1
,pcd.segment2
,pcd.segment3
,pcd.segment4
,pcd.segment5
,pcd.segment6
,pcd.segment7
,pcd.segment8
,pcd.segment9
,pcd.segment10
,pcd.segment11
,pcd.segment12
,pcd.segment13
,pcd.segment14
,pcd.segment15
,pcd.segment16
,pcd.segment17
,pcd.segment18
,pcd.segment19
,pcd.segment20
,pcd.segment21
,pcd.segment22
,pcd.segment23
,pcd.segment24
,pcd.segment25
,pcd.segment26
,pcd.segment27
,pcd.segment28
,pcd.segment29
,pcd.segment30
from pay_costing_details_v pcd
where pcd.effective_date between :cp_start_date and :cp_end_date
' || c_clause1 || '
and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
and (:cp_assignment_set_id is NULL
or ( :cp_assignment_set_id is not NULL
and exists (SELECT 1
FROM hr_assignment_sets aset
WHERE aset.assignment_set_id = :cp_assignment_set_id
and nvl(aset.payroll_id,pcd.payroll_id) = pcd.payroll_id
and (not exists
(select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.include_or_exclude = ''I'')
or exists (select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.assignment_id = pcd.assignment_id
and hasa.include_or_exclude = ''I''))
and not exists (select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.assignment_id = pcd.assignment_id
and hasa.include_or_exclude = ''E''))
)
)
and (:cp_element_type_id is null
or (:cp_element_type_id is not null
and pcd.element_type_id = :cp_element_type_id)
)
and ((:cp_cost_type = ''EST_MODE_COST''
and pcd.cost_type in (''COST_TMP'',''EST_COST''))
or
(:cp_cost_type = ''EST_MODE_ALL''
and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
or
(:cp_cost_type is null
and pcd.cost_type = ''COST_TMP'')
)
order by pcd.last_name, pcd.first_name,
pcd.middle_names, pcd.effective_date,pcd.cost_type';
'select pcd.cost_type
,pcd.consolidation_set_name
,pcd.payroll_name
,pcd.gre_name
,pcd.organization_name
,pcd.location_code
,pcd.last_name
,pcd.first_name
,pcd.middle_names
,pcd.employee_number
,pcd.assignment_number
,nvl(pcd.reporting_name,pcd.element_name)
,pcd.input_value_name
,pcd.uom
,pcd.credit_amount
,pcd.debit_amount
,pcd.national_identifier
,pcd.effective_date
,pcd.concatenated_segments
,pcd.assignment_id
,pcd.segment1
,pcd.segment2
,pcd.segment3
,pcd.segment4
,pcd.segment5
,pcd.segment6
,pcd.segment7
,pcd.segment8
,pcd.segment9
,pcd.segment10
,pcd.segment11
,pcd.segment12
,pcd.segment13
,pcd.segment14
,pcd.segment15
,pcd.segment16
,pcd.segment17
,pcd.segment18
,pcd.segment19
,pcd.segment20
,pcd.segment21
,pcd.segment22
,pcd.segment23
,pcd.segment24
,pcd.segment25
,pcd.segment26
,pcd.segment27
,pcd.segment28
,pcd.segment29
,pcd.segment30
from pay_costing_details_v pcd
where pcd.effective_date between :cp_start_date and :cp_end_date
' || c_clause1 || '
and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
and (:cp_assignment_set_id is NULL
or ( :cp_assignment_set_id is not NULL
and exists (SELECT 1
FROM hr_assignment_sets aset
WHERE aset.assignment_set_id = :cp_assignment_set_id
and nvl(aset.payroll_id,pcd.payroll_id) = pcd.payroll_id
and (not exists
(select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.include_or_exclude = ''I'')
or exists (select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.assignment_id = pcd.assignment_id
and hasa.include_or_exclude = ''I''))
and not exists (select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.assignment_id = pcd.assignment_id
and hasa.include_or_exclude = ''E''))
)
)
and (:cp_element_set_id is null
or (:cp_element_set_id is not null
and exists
(select ''x'' from pay_element_type_rules petr
where petr.element_set_id = :cp_element_set_id
and petr.element_type_id = pcd.element_type_id
and petr.include_or_exclude = ''I''
union all
select ''x'' 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_type_id = pcd.element_type_id
minus
select ''x'' from pay_element_type_rules petr
where petr.element_set_id = :cp_element_set_id
and petr.element_type_id = pcd.element_type_id
and petr.include_or_exclude = ''E''
)
)
)
and ((:cp_cost_type = ''EST_MODE_COST''
and pcd.cost_type in (''COST_TMP'',''EST_COST''))
or
(:cp_cost_type = ''EST_MODE_ALL''
and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
or
(:cp_cost_type is null
and pcd.cost_type = ''COST_TMP'')
)
order by pcd.last_name, pcd.first_name,
pcd.middle_names, pcd.effective_date,pcd.cost_type';
'select pcd.cost_type
,pcd.consolidation_set_name
,pcd.payroll_name
,pcd.gre_name
,pcd.organization_name
,pcd.location_code
,pcd.last_name
,pcd.first_name
,pcd.middle_names
,pcd.employee_number
,pcd.assignment_number
,nvl(pcd.reporting_name,pcd.element_name)
,pcd.input_value_name
,pcd.uom
,pcd.credit_amount
,pcd.debit_amount
,pcd.national_identifier
,pcd.effective_date
,pcd.concatenated_segments
,pcd.assignment_id
,pcd.segment1
,pcd.segment2
,pcd.segment3
,pcd.segment4
,pcd.segment5
,pcd.segment6
,pcd.segment7
,pcd.segment8
,pcd.segment9
,pcd.segment10
,pcd.segment11
,pcd.segment12
,pcd.segment13
,pcd.segment14
,pcd.segment15
,pcd.segment16
,pcd.segment17
,pcd.segment18
,pcd.segment19
,pcd.segment20
,pcd.segment21
,pcd.segment22
,pcd.segment23
,pcd.segment24
,pcd.segment25
,pcd.segment26
,pcd.segment27
,pcd.segment28
,pcd.segment29
,pcd.segment30
from pay_costing_details_v pcd
where pcd.effective_date between :cp_start_date and :cp_end_date
' || c_clause1 || '
and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
and (:cp_assignment_set_id is NULL
or ( :cp_assignment_set_id is not NULL
and exists (SELECT 1
FROM hr_assignment_sets aset
WHERE aset.assignment_set_id = :cp_assignment_set_id
and nvl(aset.payroll_id,pcd.payroll_id) = pcd.payroll_id
and (not exists
(select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.include_or_exclude = ''I'')
or exists (select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.assignment_id = pcd.assignment_id
and hasa.include_or_exclude = ''I''))
and not exists (select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.assignment_id = pcd.assignment_id
and hasa.include_or_exclude = ''E''))
)
)
and (:cp_element_classification_id is null
or (:cp_element_classification_id is not null
and pcd.classification_id = :cp_element_classification_id)
)
and ((:cp_cost_type = ''EST_MODE_COST''
and pcd.cost_type in (''COST_TMP'',''EST_COST''))
or
(:cp_cost_type = ''EST_MODE_ALL''
and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
or
(:cp_cost_type is null
and pcd.cost_type = ''COST_TMP'')
)
order by pcd.last_name, pcd.first_name,
pcd.middle_names, pcd.effective_date,pcd.cost_type';
'select pcd.cost_type
,pcd.consolidation_set_name
,pcd.payroll_name
,pcd.gre_name
,pcd.organization_name
,pcd.location_code
,pcd.last_name
,pcd.first_name
,pcd.middle_names
,pcd.employee_number
,pcd.assignment_number
,nvl(pcd.reporting_name,pcd.element_name)
,pcd.input_value_name
,pcd.uom
,pcd.credit_amount
,pcd.debit_amount
,pcd.national_identifier
,pcd.effective_date
,pcd.concatenated_segments
,pcd.assignment_id
,pcd.segment1
,pcd.segment2
,pcd.segment3
,pcd.segment4
,pcd.segment5
,pcd.segment6
,pcd.segment7
,pcd.segment8
,pcd.segment9
,pcd.segment10
,pcd.segment11
,pcd.segment12
,pcd.segment13
,pcd.segment14
,pcd.segment15
,pcd.segment16
,pcd.segment17
,pcd.segment18
,pcd.segment19
,pcd.segment20
,pcd.segment21
,pcd.segment22
,pcd.segment23
,pcd.segment24
,pcd.segment25
,pcd.segment26
,pcd.segment27
,pcd.segment28
,pcd.segment29
,pcd.segment30
from pay_costing_details_v pcd
where pcd.effective_date between :cp_start_date and :cp_end_date
' || c_clause1 || '
and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
and (:cp_assignment_set_id is NULL
or ( :cp_assignment_set_id is not NULL
and exists (SELECT 1
FROM hr_assignment_sets aset
WHERE aset.assignment_set_id = :cp_assignment_set_id
and nvl(aset.payroll_id,pcd.payroll_id) = pcd.payroll_id
and (not exists
(select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.include_or_exclude = ''I'')
or exists (select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.assignment_id = pcd.assignment_id
and hasa.include_or_exclude = ''I''))
and not exists (select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.assignment_id = pcd.assignment_id
and hasa.include_or_exclude = ''E''))
)
)
and ((:cp_cost_type = ''EST_MODE_COST''
and pcd.cost_type in (''COST_TMP'',''EST_COST''))
or
(:cp_cost_type = ''EST_MODE_ALL''
and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
or
(:cp_cost_type is null
and pcd.cost_type = ''COST_TMP'')
)
order by pcd.last_name, pcd.first_name,
pcd.middle_names, pcd.effective_date,pcd.cost_type';
select paa.tax_unit_id
from pay_run_results prr,
PAY_ASSIGNMENT_ACTIONS paa,
pay_action_interlocks pai
where paa.assignment_action_id = prr.assignment_action_id
AND paa.assignment_action_id = pai.LOCKED_ACTION_ID
and pai.locking_action_id = p_assignment_action_id
and prr.element_type_id = p_element_type_id;
select HOU_GRE.NAME
from HR_ORGANIZATION_UNITS HOU_GRE
where HOU_GRE.ORGANIZATION_ID = p_tax_unit_id;