PAY Deductions

            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*/