PAY Void Payments
Description
- Categories
- BI Publisher
- Application
- Payroll
- Source
- Void Payments Report (XML)
- Short Name
- PAYRPVPR_XML
- DB package
- PAY_PAYRPVPR_XMLP_PKG
Imported from BI Publisher
Void Payments Report
Void Payments Report
SQL 88 lines
select ppf.full_name Employee_name , paf.assignment_number Assignment_Number , paa2.serial_number Payment_Number , ppa2.effective_date Payment_Date , ppa1.effective_date Void_Date , ppp.value Amount , ppef.payroll_name Payroll_Name , pcs.consolidation_set_name Consolidation_Set , htu.name GRE_name, DECODE(ppa2.action_type,'M',SUBSTRB(hr_general.decode_lookup('PAY_METHOD_PAYMENT_TYPE','DEPOSIT'),1,8),'Check') payment_type, --&payment_type C_Payment_Type PAY_PAYRPVPR_XMLP_PKG.C_Payment_TypeFormula(DECODE(ppa2.action_type,'M',SUBSTRB(hr_general.decode_lookup('PAY_METHOD_PAYMENT_TYPE','DEPOSIT'),1,8),'Check')) C_Payment_Type from pay_payroll_actions ppa1, pay_assignment_actions paa1, pay_action_interlocks pai1, pay_assignment_actions paa2, pay_payroll_actions ppa2, per_assignments_f paf, per_people_f ppf, pay_action_interlocks pai2, pay_assignment_actions paa3, pay_payroll_actions ppa3, hr_organization_units htu, pay_payrolls_f ppef, pay_pre_payments ppp, pay_consolidation_sets pcs where ppa1.business_group_id + 0 = :p_business_group_id and :p_uk_us = 'US' and ppa1.action_type = 'D' and ppa1.effective_date between :p_start_date and :p_end_date and ppa1.payroll_action_id = paa1.payroll_action_id and paa1.assignment_action_id = pai1.locking_action_id and pai1.locked_action_id = paa2.assignment_action_id and paa2.payroll_action_id = ppa2.payroll_action_id and paa2.assignment_id = paf.assignment_id and paf.person_id = ppf.person_id and ppa2.effective_date between ppf.effective_start_date and ppf.effective_end_date and ppa2.effective_date between paf.effective_start_date and paf.effective_end_date and pai2.locking_action_id = paa2.assignment_action_id and paa3.assignment_action_id = pai2.locked_action_id and paa3.payroll_action_id = ppa3.payroll_action_id and ppa3.payroll_id = ppef.payroll_id and paa2.pre_payment_id = ppp.pre_payment_id and ppa2.effective_date between ppef.effective_start_date and ppef.effective_end_date and htu.organization_id = paa2.tax_unit_id and ppa3.consolidation_set_id = pcs.consolidation_set_id and htu.business_group_id = :p_business_group_id &where_optional union select ppf.full_name Employee_name , paf.assignment_number Assignment_Number , paa2.serial_number Payment_Number , ppa2.effective_date Payment_Date , ppa1.effective_date Void_Date , ppp.value Amount , ppef.payroll_name Payroll_Name , pcs.consolidation_set_name Consolidation_Set , to_char(null) GRE_name, decode(ppa2.action_type,'M',SUBSTRB(hr_general.decode_lookup('PAY_METHOD_PAYMENT_TYPE','DEPOSIT'),1,8),'Cheque') payment_type, --&payment_type C_Payment_Type PAY_PAYRPVPR_XMLP_PKG.C_Payment_TypeFormula(decode(ppa2.action_type,'M',SUBSTRB(hr_general.decode_lookup('PAY_METHOD_PAYMENT_TYPE','DEPOSIT'),1,8),'Cheque')) C_Payment_Type from pay_payroll_actions ppa1, pay_assignment_actions paa1, pay_action_interlocks pai1, pay_assignment_actions paa2, pay_payroll_actions ppa2, per_assignments_f paf, per_people_f ppf, pay_action_interlocks pai2, pay_assignment_actions paa3, pay_payroll_actions ppa3, pay_payrolls_f ppef, pay_pre_payments ppp, pay_consolidation_sets pcs where ppa1.business_group_id + 0 = :p_business_group_id and :p_uk_us <> 'US' and ppa1.action_type = 'D' and ppa1.effective_date between :p_start_date and :p_end_date and ppa1.payroll_action_id = paa1.payroll_action_id and paa1.assignment_action_id = pai1.locking_action_id and pai1.locked_action_id = paa2.assignment_action_id and paa2.payroll_action_id = ppa2.payroll_action_id and paa2.assignment_id = paf.assignment_id and paf.person_id = ppf.person_id and ppa2.effective_date between ppf.effective_start_date and ppf.effective_end_date and ppa2.effective_date between paf.effective_start_date and paf.effective_end_date and pai2.locking_action_id = paa2.assignment_action_id and paa3.assignment_action_id = pai2.locked_action_id and paa3.payroll_action_id = ppa3.payroll_action_id and ppa3.payroll_id = ppef.payroll_id and paa2.pre_payment_id = ppp.pre_payment_id and ppa2.effective_date between ppef.effective_start_date and ppef.effective_end_date and ppa3.consolidation_set_id = pcs.consolidation_set_id &where_optional order by Employee_name &order_optional |
Parameters 4
| Parameter | Type | List of values SQL |
|---|---|---|
| PY_SRS_START_DATE_STANDARD | Date | |
| PY_SRS_EFFECTIVE_DATE_STANDARD | Date | |
| Payroll Name | LOV Oracle | |
| Consolidation Set Name | LOV Oracle |