[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT (PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
(legislative_parameters
, 'LE_REQUEST'
)
) LE_REQUEST
,(PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
(legislative_parameters
, 'LEGAL_EMPLOYER_ID'
)
) LEGAL_EMPLOYER_ID
, (PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
(legislative_parameters
, 'LU_REQUEST'
)
) LU_REQUEST
,(PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
(legislative_parameters
, 'LOCAL_UNIT_ID'
)
) LOCAL_UNIT_ID
,(PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
(legislative_parameters
, 'ACCOUNT_DATE'
)
) ACCOUNT_DATE
,(PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
(legislative_parameters
, 'POSTING_DATE'
)
) POSTING_DATE
,(PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
(legislative_parameters
, 'REPORTING_DATE'
)
)REPORTING_DATE
, effective_date effective_date, business_group_id bg_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT o1.NAME legal_employer_name
, hoi2.org_information2 org_number
, hoi1.organization_id legal_id
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id = g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_employer_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 = 'SE_LEGAL_EMPLOYER_DETAILS';
SELECT '1'
FROM pay_payroll_actions appa
, pay_assignment_actions act
, per_all_assignments_f as1
, pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN csr_v_canonical_start_date
AND csr_v_canonical_end_date
AND appa.action_type IN ('R', 'Q')
-- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
AND as1.business_group_id = g_business_group_id
AND act.action_status IN ('C','S') -- 10229507
AND act.tax_unit_id = csr_v_legal_employer_id
AND appa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
AND ppa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
ORDER BY as1.person_id, act.assignment_id;
select o1.NAME LU_NAME,hoi2.ORG_INFORMATION2 CFAR
from HR_ORGANIZATION_UNITS o1
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
WHERE o1.business_group_id = g_business_group_id
and hoi1.organization_id = o1.organization_id
and hoi1.org_information1 = 'SE_LOCAL_UNIT'
and hoi1.org_information_context = 'CLASS'
and o1.organization_id = hoi2.organization_id
and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
and o1.organization_id = csr_local_unit_ID;
SELECT
count(paa.assignment_id) TOTAL
,paa.employment_category EMP_CAT
,pap.SEX
FROM
PER_ALL_ASSIGNMENTS_F paa
,HR_SOFT_CODING_KEYFLEX scl1
,PER_ALL_PEOPLE_F pap
WHERE
paa.person_id = pap.person_id
and paa.business_group_id = g_business_group_id
and scl1.segment2 = TO_CHAR(csr_local_unit_ID)
AND scl1.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
and paa.employment_category in ('FR','FT')
and paa.ASSIGNMENT_STATUS_TYPE_ID = 1
and pap.SEX in('F','M')
AND paa.PRIMARY_FLAG='Y'
and g_account_date between paa.EFFECTIVE_START_DATE and paa.EFFECTIVE_END_DATE
and g_account_date between pap.EFFECTIVE_START_DATE and pap.EFFECTIVE_END_DATE
group by paa.employment_category,pap.SEX
order by paa.employment_category,pap.SEX;
SELECT hoi_le.org_information1 local_unit_id,
hou_lu.NAME local_unit_name,
hoi_lu.org_information2 CFAR
FROM hr_organization_units hou_le,
hr_organization_information hoi_le,
hr_organization_units hou_lu,
hr_organization_information hoi_lu
WHERE hoi_le.organization_id = hou_le.organization_id
AND hou_le.organization_id = csr_legal_employer_ID
AND hoi_le.org_information_context = 'SE_LOCAL_UNITS'
AND hou_lu.organization_id = hoi_le.org_information1
AND hou_lu.organization_id = hoi_lu.organization_id
AND hoi_lu.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
select o1.name legal_employer_name,hoi1.organization_id
from hr_organization_units o1,
hr_organization_information hoi1,
hr_organization_information hoi2
where o1.business_group_id = g_business_group_id
and hoi1.organization_id = o1.organization_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 = 'SE_LEGAL_EMPLOYER_DETAILS';
SELECT DISTINCT paaf.person_id,sex
FROM per_all_assignments_f paaf,
per_all_people_f papf,
hr_soft_coding_keyflex hsck
WHERE paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND hsck.segment2=to_char(p_local_unit_id)
AND papf.person_id=paaf.person_id
and papf.SEX in('F','M')
AND paaf.PRIMARY_FLAG='Y'
AND g_account_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND g_account_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT count(*)
FROM per_absence_attendances paa,
per_absence_attendance_types pat
WHERE paa.person_id = p_person_id
AND g_account_date BETWEEN
paa.date_start AND paa.date_end
/*AND paa.date_start >=p_month_start_date
-- AND least(nvl(paa.date_end,p_abs_end_date),p_abs_end_date)<=p_abs_end_date
AND paa.date_end<=p_month_end_date */
AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND pat.absence_category = p_absence_type_id --'S'
ORDER BY paa.date_end ;
SELECT count(*)
FROM per_absence_attendances paa,
per_absence_attendance_types pat
WHERE paa.person_id = p_person_id
AND g_account_date BETWEEN
paa.date_start AND paa.date_end
/*AND paa.date_start >=p_month_start_date
-- AND least(nvl(paa.date_end,p_abs_end_date),p_abs_end_date)<=p_abs_end_date
AND paa.date_end<=p_month_end_date*/
AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND pat.absence_category NOT IN ('S','V')
ORDER BY paa.date_end ;
SELECT paaf.assignment_id,paaf.EFFECTIVE_START_DATE,paaf.employment_category
FROM PER_ALL_ASSIGNMENTS_F paaf,
hr_soft_coding_keyflex hsck
WHERE paaf.EFFECTIVE_START_DATE BETWEEN p_month_start_date
AND p_month_end_date
AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
AND hsck.segment2=to_char(g_local_unit_id)
and paaf.employment_category in ('FR','FT')
AND paaf.PRIMARY_FLAG='Y'
and paaf.ASSIGNMENT_STATUS_TYPE_ID = 1;
SELECT hsck.segment2
FROM per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
WHERE hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
AND paaf.assignment_id=p_assignment_id
AND paaf.EFFECTIVE_START_DATE=
(SELECT max(EFFECTIVE_START_DATE)
FROM per_all_assignments_f
WHERE assignment_id=p_assignment_id
AND EFFECTIVE_START_DATE < p_effecive_start_date);
SELECT papf.sex FROM
per_all_assignments_f paaf,
per_all_people_f papf
WHERE paaf.person_id=papf.person_id
AND paaf.assignment_id=p_assignment_id
AND g_account_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND g_account_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT paaf.assignment_id,paaf.EFFECTIVE_end_DATE,paaf.employment_category
FROM PER_ALL_ASSIGNMENTS_F paaf,
hr_soft_coding_keyflex hsck
WHERE paaf.effective_end_date BETWEEN p_month_start_date
AND p_month_end_date
AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
AND hsck.segment2=to_char(g_local_unit_id)
and paaf.employment_category in ('FR','FT')
AND paaf.PRIMARY_FLAG='Y'
and paaf.ASSIGNMENT_STATUS_TYPE_ID = 1;
/*SELECT paaf.assignment_id,paaf.EFFECTIVE_START_DATE,paaf.employment_category
FROM PER_ALL_ASSIGNMENTS_F paaf,
hr_soft_coding_keyflex hsck
WHERE paaf.effective_end_date BETWEEN p_month_start_date
AND p_month_end_date
AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
AND hsck.segment2=to_char(g_local_unit_id)
and paaf.employment_category in ('FR','FT')
and paaf.ASSIGNMENT_STATUS_TYPE_ID = 3;*/
SELECT paaf.assignment_id,paaf.EFFECTIVE_START_DATE,paaf.employment_category
FROM
PER_ALL_ASSIGNMENTS_F paaf,
hr_soft_coding_keyflex hsck
WHERE
paaf.effective_start_date BETWEEN p_month_start_date
AND p_month_end_date
AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
AND hsck.segment2=to_char(g_local_unit_id)
and paaf.employment_category in ('FR','FT')
AND paaf.PRIMARY_FLAG='Y'
and paaf.ASSIGNMENT_STATUS_TYPE_ID = 3;
SELECT hsck.segment2
FROM per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
WHERE hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
AND paaf.assignment_id=p_assignment_id
AND paaf.effective_end_date=
(SELECT min(effective_end_date)
FROM per_all_assignments_f
WHERE assignment_id=p_assignment_id
AND effective_end_date > p_effecive_end_date);
SELECT NAME FROM
hr_organization_units WHERE
organization_id=p_local_unit_id;
'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';
IF g_LU_request ='LU_SELECTED'
THEN
/* THis is for Given LOCAL UNIT */
OPEN csr_CFAR_FROM_LU (g_local_unit_id);
'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT u.creator_id
FROM ff_user_entities u, ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code = 'SE')
AND (u.business_group_id IS NULL)
AND u.creator_type = 'B';
SELECT u.creator_id
FROM ff_user_entities u, ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code = 'SE')
AND (u.business_group_id IS NULL)
AND u.creator_type = 'B';
SELECT action_information3, action_information4
, action_information5
FROM pay_action_information
WHERE action_context_type = 'PA'
AND action_context_id = csr_v_pa_id
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEHPDA'
AND action_information2 = 'LE';
SELECT TO_CHAR
(fnd_date.canonical_to_date (action_information5)
, 'YYYYMMDD'
) period_from
, TO_CHAR
(fnd_date.canonical_to_date (action_information6)
, 'YYYYMMDD'
) period_to
FROM pay_action_information
WHERE action_context_type = 'PA'
AND action_context_id = csr_v_pa_id
AND action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYSEHPDA';
SELECT *
FROM pay_action_information
WHERE action_context_type = 'AAP'
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEHPDA'
AND action_information3 = csr_v_pa_id
AND action_information2 = 'PER'
AND action_information15 = csr_v_le_id
ORDER BY action_information30;
SELECT payroll_action_id
INTO l_payroll_action_id
FROM pay_payroll_actions ppa
, fnd_conc_req_summary_v fcrs
, fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
AND fcrs.priority_request_id = fcrs1.priority_request_id
AND ppa.request_id BETWEEN fcrs1.request_id AND fcrs.request_id
AND ppa.request_id = fcrs1.request_id;
SELECT
pai1.action_information3 Legal_Employer,
pai1.action_information4 Local_Unit,
pai.ACTION_INFORMATION9 Report_Date,
--'0000000SCB',
pai1.action_information6 Fiscal_Year,
pai1.action_information7 Fiscal_Quarter,
pai1.action_information8 Fiscal_Month,
pai1.ACTION_INFORMATION5 CFAR_Number,
pai1.action_information9 Regular_Men,
pai1.action_information10 Regular_Women,
pai1.action_information11 Temporary_Men,
pai1.action_information12 Temporary_Women,
pai1.action_information13 Sick_Men,
pai1.action_information14 Sick_Women,
pai1.action_information15 Holiday_Men,
pai1.action_information16 Holiday_Women,
pai1.action_information17 Other_Men,
pai1.action_information18 Other_Women,
pai1.action_information19 New_Regular_Men,
pai1.action_information20 New_Regular_Women,
pai1.action_information21 New_Temporary_Men,
pai1.action_information22 New_Temporary_women,
pai1.action_information23 Terminate_Regular_Men,
pai1.action_information24 Terminate_Regular_Women,
pai1.action_information25 Terminate_Temporary_Men,
pai1.action_information26 Terminate_Temporary_Women
FROM
pay_action_information pai,
pay_payroll_actions ppa,
pay_action_information pai1
WHERE
pai.action_context_id = ppa.payroll_action_id
AND ppa.payroll_action_id =p_payroll_action_id --20162 --20264 --20165
AND pai.action_context_id = pai1.action_context_id
AND pai1.action_context_id= ppa.payroll_action_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'LU'
AND pai1.action_information1 = 'PYSESTEA'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSESTEA'
AND pai.action_information_category = 'EMEA REPORT DETAILS'
order BY pai1.ACTION_INFORMATION3,pai1.ACTION_INFORMATION2;
SELECT SUM(pai1.action_information9) Regular_Men,
sum(pai1.action_information10) Regular_Women,
sum(pai1.action_information11) Temporary_Men,
sum(pai1.action_information12) Temporary_Women
FROM
pay_payroll_actions ppa,
pay_action_information pai1
WHERE
pai1.action_context_id = ppa.payroll_action_id
AND ppa.payroll_action_id =p_payroll_action_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'LU'
AND pai1.action_information1 = 'PYSESTEA'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
AND pai1.action_information3=p_legal_employer;*/
SELECT
count(paa.assignment_id) TOTAL
,paa.employment_category EMP_CAT
,pap.SEX
FROM
hr_all_organization_units hou_le,
hr_organization_information hoi_le,
hr_all_organization_units hou_lu,
hr_organization_information hoi_lu,
PER_ALL_ASSIGNMENTS_F paa
,HR_SOFT_CODING_KEYFLEX scl1
,PER_ALL_PEOPLE_F pap
WHERE
hoi_le.organization_id = hou_le.organization_id
AND hou_le.name =p_legal_employer
AND hoi_le.org_information_context = 'SE_LOCAL_UNITS'
AND hou_lu.organization_id = hoi_le.org_information1
AND hou_lu.organization_id = hoi_lu.organization_id
AND hoi_lu.org_information_context = 'SE_LOCAL_UNIT_DETAILS'
AND paa.person_id = pap.person_id
and paa.business_group_id = g_business_group_id
and scl1.segment2 = TO_CHAR(hoi_le.org_information1)
AND scl1.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
and paa.employment_category in ('FR','FT')
and paa.ASSIGNMENT_STATUS_TYPE_ID = 1
and pap.SEX in('F','M')
and g_account_date between paa.EFFECTIVE_START_DATE and paa.EFFECTIVE_END_DATE
and g_account_date between pap.EFFECTIVE_START_DATE and pap.EFFECTIVE_END_DATE
group by paa.employment_category,pap.SEX
order by paa.employment_category,pap.SEX;
SELECT payroll_action_id
INTO l_payroll_action_id
FROM pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
AND fcrs.priority_request_id = fcrs1.priority_request_id
AND ppa.request_id between fcrs1.request_id and fcrs.request_id
AND ppa.request_id = fcrs1.request_id;