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
Run PAY Deductions and other Oracle EBS reports with Blitz Report™ on our demo environment
            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
Blitz Report™