PAY Deductions

Description

Categories: BI Publisher
Application: Payroll
Source: Deductions Report (XML)
Short Name: PAYUSDED_XML
DB package: PAY_PAYUSDED_XMLP_PKG

Imported from BI Publisher
Deductions Report.

SQL 114 lines

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

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

Parameters 15

Parameter	Type	List of values SQL
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

Description

SQL 114 lines

Parameters 15

Find out more

Features

Download

FAQ