[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
,PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_ID')
,FND_NUMBER.CANONICAL_TO_NUMBER(PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'ADJUSTMENT_WT'))
,FND_NUMBER.CANONICAL_TO_NUMBER(PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'ADJUSTMENT_SS'))
,FND_NUMBER.CANONICAL_TO_NUMBER(PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'ADJUSTMENT_TS'))
,FND_NUMBER.CANONICAL_TO_NUMBER(PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'VAT'))
,PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'MONTH_RPT')
,PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'YEAR_RPT')
,PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,FND_DATE.CANONICAL_TO_DATE(PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'DUE_DATE'))
,PAY_FI_ARCHIVE_MTRA.GET_PARAMETER(legislative_parameters,'REF_NUMBER')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT o1.name , hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION7
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION11
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS' ;
SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
, hr_organization_information hoi3
, hr_organization_information hoi4
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND hoi2.organization_id (+)= o1.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
AND hoi2.org_information1(+)= 'FAX'
AND hoi3.organization_id (+)= o1.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
AND hoi3.org_information1 (+)= 'PHONE'
AND hoi4.organization_id (+)= o1.organization_id
AND hoi4.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
AND hoi4.org_information1 (+)= 'PERSON' ;
SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
, hr_organization_information hoi3
, hr_organization_information hoi4
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND hoi2.organization_id (+)= o1.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
AND hoi2.org_information1(+)= 'FAX'
AND hoi3.organization_id (+)= o1.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
AND hoi3.org_information1 (+)= 'PHONE'
AND hoi4.organization_id (+)= o1.organization_id
AND hoi4.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
AND hoi4.org_information1 (+)= 'PERSON' ;
SELECT hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
hoi1.POSTAL_CODE , hoi1.TOWN_OR_CITY , hoi1.COUNTRY
FROM hr_organization_units o1
, hr_locations hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = l_business_group_id
AND hoi1.location_id = o1.location_id
AND hoi2.organization_id = o1.organization_id
AND hoi2.organization_id = csr_v_local_unit_id
AND hoi2.org_information1 = 'FI_LOCAL_UNIT'
AND hoi2.org_information_context = 'CLASS' ;
SELECT hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
hoi1.POSTAL_CODE , hoi1.TOWN_OR_CITY , hoi1.COUNTRY
FROM hr_organization_units o1
, hr_locations hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = l_business_group_id
AND hoi1.location_id = o1.location_id
AND hoi2.organization_id = o1.organization_id
AND hoi2.organization_id = csr_v_legal_emp_id
AND hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi2.org_information_context = 'CLASS' ;
SELECT o1.name ,hoi2.ORG_INFORMATION1 ,hoi2.ORG_INFORMATION2
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
, hr_organization_information hoi3
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_tax_office_id
AND hoi1.org_information1 = 'PROV_TAX_OFFICE'
AND hoi1.org_information_context = 'CLASS'
AND hoi3.organization_id = o1.organization_id
AND hoi3.org_information1 = 'HR_PAYEE'
AND hoi3.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_THIRD_PARTY_PAYMENT';
SELECT b.segment3
FROM pay_org_payment_methods_f a , pay_external_accounts b
WHERE a.org_payment_method_id = csr_v_payment_method_id
AND a.external_account_id = b.external_account_id
AND a.business_group_id=l_business_group_id
AND a.pmeth_information_category ='FI Third Party Payment';
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'FI'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
select paaf.person_id, act.assignment_id assignment_id, act.assignment_action_id run_action_id,
act1.assignment_action_id
prepaid_action_id, appa.effective_date, appa.payroll_action_id,
appa2.payroll_action_id payactid, hsck.segment2 local_unit_id
from pay_payroll_actions appa,
pay_payroll_actions appa2,
pay_assignment_actions act,
pay_assignment_actions act1,
pay_action_interlocks pai,
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck,
hr_organization_information hoi--,
-- pay_payrolls_f ppa
where appa.action_type in ('R', 'Q')
and act.payroll_action_id = appa.payroll_action_id
and act.source_action_id is null -- Master Action
and act.action_status IN ('C','S') -- 10229501
and act.assignment_action_id = pai.locked_action_id
and act1.assignment_action_id = pai.locking_action_id
and act1.action_status IN ('C','S') -- 10229501
and act1.payroll_action_id = appa2.payroll_action_id
and appa2.action_type in ('P', 'U')
and paaf.assignment_id = act.assignment_id
-- and paaf.assignment_id = p_assignemtn_id
and appa.effective_date between paaf.effective_start_date and paaf.effective_end_date
and appa.effective_date between p_start_date and p_end_date
and paaf.primary_flag = 'Y'
-- and paaf.person_id between p_start_person and p_end_person
-- and ppa.payroll_id = paaf.payroll_id
-- and ppa.payroll_id = nvl (g_payroll_id, ppa.payroll_id)
-- and ppa.period_type = g_payroll_type
-- and g_year_last_date between ppa.effective_start_date and ppa.effective_end_date
and hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
and hsck.segment2 = nvl (to_char (p_local_unit_id), hsck.segment2)
and hoi.organization_id = p_legal_employer_id
and hoi.org_information_context = 'FI_LOCAL_UNITS'
and hoi.org_information1 = hsck.segment2
order by person_id, assignment_id, payroll_action_id, prepaid_action_id;
select max (effective_end_date) effective_date
from per_all_assignments_f paa
where assignment_id = p_asg_id
and paa.effective_start_date <= p_end_date
and paa.effective_end_date > = p_start_date
and assignment_status_type_id in (select assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'ACTIVE_ASSIGN'
and active_flag = 'Y'
and ( (legislation_code is null and business_group_id is null)
or (business_group_id = p_business_group_id)
));
select eev1.screen_entry_value screen_entry_value
from per_all_assignments_f asg1,
per_all_assignments_f asg2,
per_all_people_f per,
pay_element_links_f el,
pay_element_types_f et,
pay_input_values_f iv1,
pay_element_entries_f ee,
pay_element_entry_values_f eev1
where asg1.assignment_id = p_assignment_id
and p_value_date between asg1.effective_start_date and asg1.effective_end_date
and p_value_date between asg2.effective_start_date and asg2.effective_end_date
and p_value_date between per.effective_start_date and per.effective_end_date
and per.person_id = asg1.person_id
and asg2.person_id = per.person_id
and asg2.primary_flag = 'Y'
and et.element_name = 'Tax Card'
and (et.legislation_code = 'FI' or et.business_group_id = g_business_group_id)
and iv1.element_type_id = et.element_type_id
and iv1.name = 'Tax Card Type'
and el.business_group_id = per.business_group_id
and el.element_type_id = et.element_type_id
and ee.assignment_id = asg2.assignment_id
and ee.element_link_id = el.element_link_id
and eev1.element_entry_id = ee.element_entry_id
and eev1.input_value_id = iv1.input_value_id
and p_value_date between ee.effective_start_date and ee.effective_end_date
and p_value_date between eev1.effective_start_date and eev1.effective_end_date;
p_sql := 'SELECT DISTINCT person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
SELECT count(*) INTO l_count
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYFIMTRA'
AND action_context_id = p_payroll_action_id;
SELECT MAX(ASSIGNMENT_ACTION_ID)
INTO l_assignment_action_id
FROM pay_run_balances
WHERE local_unit_id = l_local_unit_id
AND TO_CHAR(effective_date,'MMYYYY')=l_month||l_year ;
SELECT MAX(ASSIGNMENT_ACTION_ID)
INTO l_assignment_action_id
FROM pay_run_balances
WHERE tax_unit_id =l_legal_employer_id
AND TO_CHAR(effective_date,'MMYYYY')=l_month||l_year ;
-- Return cursor that selects no rows
p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';