VIEW: APPS.PAY_EMP_PAYSLIP_ACTION_INFO_V

Source

View Text - Preformatted

SELECT pai.action_information14 action_information14, count(*) check_count, pai.action_context_id action_context_id, paf.person_id person_id, pai.effective_date effective_date from pay_action_information pai, pay_action_information pai2, per_all_assignments_f paf, per_all_assignments_f paf2 WHERE pai.action_context_id >= pai2.action_context_id and paf.assignment_id = pai.assignment_id and pai.action_context_type = 'AAP' and pai.action_information_category = 'EMPLOYEE DETAILS' and paf2.assignment_id = pai2.assignment_id and pai2.action_context_type = 'AAP' and pai2.action_information_category = 'EMPLOYEE DETAILS' and pai.effective_date = pai2.effective_date and paf.person_id = paf2.person_id and pai.effective_date between paf.effective_start_date and paf.effective_end_date and pai.effective_date between paf2.effective_start_date and paf2.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 exists (select 'x' from pay_assignment_actions paa2 where paa2.assignment_action_id = pai2.action_context_id and paa2.assignment_id = pai2.assignment_id) and pay_us_employee_payslip_web.check_emp_personal_payment ( paf.assignment_id, paf.payroll_id, pai.action_information16, pai.action_context_id, pai.effective_date) = 'Y' 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 ) group by pai.effective_date, pai.action_context_id , pai.action_information14, paf.person_id

View Text - HTML Formatted

SELECT PAI.ACTION_INFORMATION14 ACTION_INFORMATION14
, COUNT(*) CHECK_COUNT
, PAI.ACTION_CONTEXT_ID ACTION_CONTEXT_ID
, PAF.PERSON_ID PERSON_ID
, PAI.EFFECTIVE_DATE EFFECTIVE_DATE
FROM PAY_ACTION_INFORMATION PAI
, PAY_ACTION_INFORMATION PAI2
, PER_ALL_ASSIGNMENTS_F PAF
, PER_ALL_ASSIGNMENTS_F PAF2
WHERE PAI.ACTION_CONTEXT_ID >= PAI2.ACTION_CONTEXT_ID
AND PAF.ASSIGNMENT_ID = PAI.ASSIGNMENT_ID
AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE DETAILS'
AND PAF2.ASSIGNMENT_ID = PAI2.ASSIGNMENT_ID
AND PAI2.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI2.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE DETAILS'
AND PAI.EFFECTIVE_DATE = PAI2.EFFECTIVE_DATE
AND PAF.PERSON_ID = PAF2.PERSON_ID
AND PAI.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAI.EFFECTIVE_DATE BETWEEN PAF2.EFFECTIVE_START_DATE
AND PAF2.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 EXISTS (SELECT 'X'
FROM PAY_ASSIGNMENT_ACTIONS PAA2
WHERE PAA2.ASSIGNMENT_ACTION_ID = PAI2.ACTION_CONTEXT_ID
AND PAA2.ASSIGNMENT_ID = PAI2.ASSIGNMENT_ID)
AND PAY_US_EMPLOYEE_PAYSLIP_WEB.CHECK_EMP_PERSONAL_PAYMENT ( PAF.ASSIGNMENT_ID
, PAF.PAYROLL_ID
, PAI.ACTION_INFORMATION16
, PAI.ACTION_CONTEXT_ID
, PAI.EFFECTIVE_DATE) = 'Y'
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 ) GROUP BY PAI.EFFECTIVE_DATE
, PAI.ACTION_CONTEXT_ID
, PAI.ACTION_INFORMATION14
, PAF.PERSON_ID