VIEW: APPS.PAY_PERF_PAYSLIP_ACTION_INFO_V

Source

View Text - Preformatted

SELECT pai.action_information14 action_information14, row_number() over (partition by paf.person_id, pai.effective_date order by action_context_id) check_count, pai.action_context_id action_context_id, paf.person_id person_id, pai.effective_date effective_date, paf.payroll_id payroll_id, pai.action_information16 time_period_id, paf.assignment_id assignment_id FROM pay_action_information pai, per_all_assignments_f paf WHERE paf.assignment_id = pai.assignment_id AND pai.action_context_type = 'AAP' AND pai.action_information_category = 'EMPLOYEE DETAILS' AND pai.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND EXISTS ( SELECT 'x' FROM pay_assignment_actions paa WHERE paa.assignment_action_id = pai.action_context_id AND paa.assignment_id = pai.assignment_id ) AND NOT EXISTS ( SELECT 'X' FROM pay_legislative_field_info plf, hr_organization_information hoi, pay_payroll_actions ppa, pay_assignment_actions paa WHERE validation_name = 'ITEM_PROPERTY' AND rule_type = 'PAYSLIP_EMPLOYEE_DETAILS_EXC' AND field_name = 'CHOOSE_PAYSLIP' AND legislation_code = hoi.org_information9 AND org_information_context = 'Business Group Information' AND organization_id = ppa.business_group_id AND ppa.report_type = rule_mode AND ppa.payroll_action_id = paa.payroll_action_id AND paa.assignment_action_id = pai.action_context_id )

View Text - HTML Formatted

SELECT PAI.ACTION_INFORMATION14 ACTION_INFORMATION14
, ROW_NUMBER() OVER (PARTITION BY PAF.PERSON_ID
, PAI.EFFECTIVE_DATE ORDER BY ACTION_CONTEXT_ID) CHECK_COUNT
, PAI.ACTION_CONTEXT_ID ACTION_CONTEXT_ID
, PAF.PERSON_ID PERSON_ID
, PAI.EFFECTIVE_DATE EFFECTIVE_DATE
, PAF.PAYROLL_ID PAYROLL_ID
, PAI.ACTION_INFORMATION16 TIME_PERIOD_ID
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
FROM PAY_ACTION_INFORMATION PAI
, PER_ALL_ASSIGNMENTS_F PAF
WHERE PAF.ASSIGNMENT_ID = PAI.ASSIGNMENT_ID
AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE DETAILS'
AND PAI.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND EXISTS ( SELECT 'X'
FROM PAY_ASSIGNMENT_ACTIONS PAA
WHERE PAA.ASSIGNMENT_ACTION_ID = PAI.ACTION_CONTEXT_ID
AND PAA.ASSIGNMENT_ID = PAI.ASSIGNMENT_ID )
AND NOT EXISTS ( SELECT 'X'
FROM PAY_LEGISLATIVE_FIELD_INFO PLF
, HR_ORGANIZATION_INFORMATION HOI
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE VALIDATION_NAME = 'ITEM_PROPERTY'
AND RULE_TYPE = 'PAYSLIP_EMPLOYEE_DETAILS_EXC'
AND FIELD_NAME = 'CHOOSE_PAYSLIP'
AND LEGISLATION_CODE = HOI.ORG_INFORMATION9
AND ORG_INFORMATION_CONTEXT = 'BUSINESS GROUP INFORMATION'
AND ORGANIZATION_ID = PPA.BUSINESS_GROUP_ID
AND PPA.REPORT_TYPE = RULE_MODE
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PAI.ACTION_CONTEXT_ID )