PAY Deductions
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Deductions Report.
Application: Payroll
Source: Deductions Report (XML)
Short Name: PAYUSDED_XML
DB package: PAY_PAYUSDED_XMLP_PKG
Description: Deductions Report.
Application: Payroll
Source: Deductions Report (XML)
Short Name: PAYUSDED_XML
DB package: PAY_PAYUSDED_XMLP_PKG
select distinct &p_hint Consolidation_set_id , consolidation_set_name , payroll_id , payroll_name , payroll_effective_start_date , payroll_effective_end_date , payroll_action_id , payroll_action_effective_date , payroll_action_date_earned , time_period_id , period_name , period_num , period_type, period_start_date, period_end_date , assignment_action_id , assignment_id , tax_unit_id , GRE , person_id , employee_number , full_name , organization_id , organization_name , location_id , location_code , location_description , assignment_sequence , assignment_number , element_type_id , element_name , element_description , business_group_id , primary_balance_id , hours_balance_id , classification_id , classification_name , primary_balance , not_taken_balance , arrears_balance , accrued_balance , total_owed , decode(:P_SORT_OPTION1_L,'GRE','GRE', 'Organization','Organization', 'Location','Location', null) Sort_option1, decode(:P_SORT_OPTION2,'GRE','GRE', 'Organization','Organization', 'Location','Location', null) Sort_option2, decode(:P_SORT_OPTION3,'GRE','GRE', 'Organization','Organization', 'Location','Location', null) Sort_option3, decode(:P_SORT_OPTION1_L,'GRE',GRE, 'Organization',Organization_name, 'Location',Location_code, null) sort_option1_value, decode(:P_SORT_OPTION2,'GRE',GRE, 'Organization',Organization_name, 'Location',Location_code, null) sort_option2_value, decode(:P_SORT_OPTION3,'GRE',GRE, 'Organization',Organization_name, 'Location',Location_code, null) sort_option3_value, PAY_PAYUSDED_XMLP_PKG.cf_sort1formula(decode ( :P_SORT_OPTION1_L , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null )) CF_Sort1, PAY_PAYUSDED_XMLP_PKG.cf_sort2formula(decode ( :P_SORT_OPTION2 , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null )) CF_Sort2, PAY_PAYUSDED_XMLP_PKG.cf_sort3formula(decode ( :P_SORT_OPTION3 , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null )) CF_Sort3, PAY_PAYUSDED_XMLP_PKG.cf_sort1_valueformula(decode ( :P_SORT_OPTION1_L , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) CF_Sort1_value, PAY_PAYUSDED_XMLP_PKG.cf_sort2_valueformula(decode ( :P_SORT_OPTION2 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) CF_Sort2_value, PAY_PAYUSDED_XMLP_PKG.cf_sort3_valueformula(decode ( :P_SORT_OPTION3 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) CF_Sort3_value, PAY_PAYUSDED_XMLP_PKG.s1_total_textformula(decode ( :P_SORT_OPTION1_L , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) s1_total_text, PAY_PAYUSDED_XMLP_PKG.s2_total_textformula(decode ( :P_SORT_OPTION1_L , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION2 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) s2_total_text, PAY_PAYUSDED_XMLP_PKG.s3_total_textformula(decode ( :P_SORT_OPTION1_L , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION2 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION3 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) s3_total_text, PAY_PAYUSDED_XMLP_PKG.s3_textformula(decode ( :P_SORT_OPTION1_L , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION1_L , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null ), decode ( :P_SORT_OPTION2 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION2 , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null ), decode ( :P_SORT_OPTION3 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION3 , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null )) S3_Text, PAY_PAYUSDED_XMLP_PKG.classification_total_textformu(classification_name) Classification_total_text, PAY_PAYUSDED_XMLP_PKG.element_total_textformula(element_name) Element_total_text, PAY_PAYUSDED_XMLP_PKG.person_total_textformula(full_name) Person_total_text, PAY_PAYUSDED_XMLP_PKG.scheduled_dednformula(primary_balance, not_taken_balance, :arrears_taken) scheduled_dedn, PAY_PAYUSDED_XMLP_PKG.current_arrearsformula(arrears_balance) current_arrears, PAY_PAYUSDED_XMLP_PKG.arrears_takenformula(arrears_balance) arrears_taken, PAY_PAYUSDED_XMLP_PKG.remainingformula(total_owed, PAY_PAYUSDED_XMLP_PKG.cf_accruedformula(accrued_balance, primary_balance, total_owed)) remaining, PAY_PAYUSDED_XMLP_PKG.cf_accruedformula(accrued_balance, primary_balance, total_owed) CF_Accrued from pay_us_deductions_report_v pudr where consolidation_set_id = to_number(:P_CONSOLIDATION_SET_ID) and payroll_action_effective_date between :P_START_DATE and :P_END_DATE and business_group_id = to_number(:P_BUSINESS_GROUP_ID) &p_where_clause order by 2, 33, 1, 4, 5, 6, 46, 43, 47, 44, 48, 45, 36, 37, 31 , 30 , 32, 22, 29, 20, 17, 21, 28, 25, 27, 24, 19, 18, 26, 23, 8, 9, 11, 13, 10, 12, 15, 14, 7, 16, 34, 35, 38, 42, 41, 39, 40 /* order by Consolidation_set_id, element_description, payroll_id,payroll_name,payroll_effective_start_date,decode(:P_SORT_OPTION3,'GRE','GRE', 'Organization','Organization', 'Location','Location', null), total_owed,decode(:P_SORT_OPTION1_L,'GRE',GRE, 'Organization',Organization_name, 'Location',Location_code, null),decode(:P_SORT_OPTION1_L,'GRE','GRE', 'Organization','Organization', 'Location','Location', null),decode(:P_SORT_OPTION2,'GRE',GRE, 'Organization',Organization_name, 'Location',Location_code, null), decode(:P_SORT_OPTION2,'GRE','GRE', 'Organization','Organization', 'Location','Location', null), hours_balance_id, classification_id,element_type_id,assignment_number ,element_name,employee_number,assignment_sequence,GRE,assignment_action_id,person_id, location_description,organization_name,location_code, organization_id,tax_unit_id, assignment_id, location_id,full_name,payroll_action_id,payroll_action_effective_date,time_period_id,period_num,payroll_action_date_earned,period_name,period_start_date,period_type,payroll_effective_end_date, period_end_date,business_group_id,primary_balance_id,classification_name,accrued_balance, arrears_balance,primary_balance,not_taken_balance*/ |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Starting Pay Date |
|
Date | |
PER_DATES |
|
Date | |
Payroll |
|
LOV Oracle | |
Consolidation Set |
|
LOV Oracle | |
Selection Criterion |
|
LOV Oracle | |
Element Set |
|
LOV Oracle | |
Deduction Classification |
|
LOV Oracle | |
Deduction |
|
LOV Oracle | |
GRE |
|
LOV Oracle | |
Organization |
|
LOV Oracle | |
Location |
|
LOV Oracle | |
Employee Name |
|
LOV Oracle | |
Sort Option One |
|
LOV Oracle | |
Sort Option Two |
|
LOV Oracle | |
Sort Option Three |
|
LOV Oracle |