ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4714D51A9E05362FB09050512Y 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*/ PAY PAYUSDED_XML US PAY Deductions Imported from BI Publisher Description: Deductions Report. Application: Payroll Source: Deductions Report (XML) Short Name: PAYUSDED_XML DB package: PAY_PAYUSDED_XMLP_PKG BI Publisher &p_hint &p_where_clause :arrears_taken :c_business_group_name :c_classification :c_consolidation_set :c_element_name :c_element_set :c_gre :c_location :c_organization :c_payroll :c_person :c_report_subtitle :p_business_group_id :p_classification_id :p_conc_request_id :p_consolidation_set_id :p_element_set_id :p_element_type_id :p_end_date :p_hint :p_location_id :p_organization_id :p_payroll_id :p_person_id :p_report_title :p_selection_criterion :p_session_date :p_sort_option1 :p_sort_option1_l :p_sort_option2 :p_sort_option3 :p_start_date :p_tax_unit_id :p_where_clause 1 -70 :p_dummy Char select 'SCT'||:$flex$.selection_criterion from sys.dual US Selection_Criteria_Dummy Dummy 2 -40 :is_ele Char select '1' from dual where :$flex$.selection_criterion:NULL='Deduction Element' US PER_CHAR Is Element 3 -50 :is_ele_class Char select '1' from dual where :$flex$.selection_criterion:NULL='Deduction Classification' US PER_CHAR Is Element Classification 4 -60 :is_element_set Char select '1' from dual where :$flex$.selection_criterion:NULL='Element Set' US PER_CHAR Is Element Set 5 -30 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Name Business Group 6 -20 :p_session_date Char SELECT TO_CHAR(effective_date, 'DD-MON-YYYY') FROM fnd_sessions WHERE session_id = USERENV('SESSIONID') Y US Session Date Session Date 7 10 :p_start_date Date Y US Starting Pay Date Payment Start Date 8 20 :p_end_date Date Y US PER_DATES Payment End Date 9 30 :p_payroll_id LOV Oracle PY_SRS_PAYROLL Y select pay.payroll_id id, pay.payroll_name value, null description from pay_payrolls_f pay,fnd_sessions fnd where pay.business_group_id= fnd_global.per_business_group_id and fnd.session_id=userenv('sessionid') and fnd.effective_date between pay.effective_start_date and pay.effective_end_date order by pay.payroll_name US Payroll Payroll 10 40 :p_consolidation_set_id LOV Oracle PY_SRS_CONSOLIDATION_SET Y select con.consolidation_set_id id, con.consolidation_set_name value, null description from pay_consolidation_sets con where con.business_group_id =fnd_global.per_business_group_id order by con.consolidation_set_name select c.consolidation_set_name from pay_consolidation_sets c,pay_payrolls p where p.consolidation_set_id = c.consolidation_set_id and p.payroll_id=:$flex$.payroll Y US Consolidation Set Consolidation Set 11 50 :p_selection_criterion LOV Oracle HR_US_REPORT_SELECTION_CRITERIA Y select fcl.meaning id, fcl.meaning value, null description from fnd_common_lookups fcl where fcl.application_id=800 and fcl.lookup_type='REPORT_SELECT_SORT_CODE' and fcl.enabled_flag='Y' and ( (:work_order.concurrent_program_name:null in ('PAYUSOLT','PAYUSUNB') and fcl.lookup_code in ('04','05') ) or (:work_order.concurrent_program_name:null in('PAYUSDED','PAYUSDED_XML') and fcl.lookup_code in ('09','10','11') ) or (:work_order.concurrent_program_name:null in ('PYUSW2CA','PAYUSW2C','PAYUSW2CPDF','PAYUSW2CDTL','PYUS1099RC','EMP_1099RC_PDF') and fcl.lookup_code in ('01','02','18') ) or (:work_order.concurrent_program_name:null ='PAYERRE' and fcl.lookup_code in ('03','21','25','27','28') ) or (:work_order.concurrent_program_name:null ='PYUSARLV' and fcl.lookup_code in ('03','21','29','30','31') ) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSOLT','PAYUSUNB') and fcl.lookup_code in ('04','05') ) or (substr(:set_requests.descriptive_flexfield_name:null,7) in('PAYUSDED','PAYUSDED_XML') and fcl.lookup_code in ('09','10','11') ) or (substr(:set_requests.descriptive_flexfield_name:null,7) ='PAYUSPW2' and fcl.lookup_code in ('01','02','04','05','18') ) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PYUSW2CA','PAYUSW2C') and fcl.lookup_code in ('01','02','18') ) ) order by fcl.lookup_code US Selection Criterion Selection Criterion 12 60 :p_element_set_id LOV Oracle PAY_REPORT_ELEMENT_SET Y select es.element_set_id id, estl.element_set_name value, null description from pay_element_sets es,pay_element_sets_tl estl where (es.business_group_id=fnd_global.per_business_group_id or (es.business_group_id is null and es.legislation_code=(select legislation_code from per_business_groups where business_group_id= fnd_global.per_business_group_id) )) and es.element_set_type='C' and :$flex$.per_char='1' and estl.element_set_id=es.element_set_id and estl.language=userenv('LANG') order by estl.element_set_name Y US Element Set Element Set 13 70 :p_classification_id LOV Oracle PY_US_DED_SRS_ELEMENT_CLASSIFICATION Y select classification_id id, classification_name value, null description from pay_element_classifications where nvl(business_group_id,fnd_global.per_business_group_id) =fnd_global.per_business_group_id and (legislation_code is null or legislation_code=( select legislation_code from per_business_groups b where b.business_group_id= fnd_global.per_business_group_id)) and classification_name in ('Pre-Tax Deductions', 'Involuntary Deductions', 'Voluntary Deductions', 'Employer Liabilities') and :$flex$.per_char='1' order by value,description Y US Deduction Classification Deduction Classification 14 80 :p_element_type_id LOV Oracle PY_US_DED_SRS_DEDUCTION_ELEMENT Y select pet.element_type_id id, pet.element_name value, null description from pay_element_types_f pet where nvl(business_group_id,fnd_global.per_business_group_id) =fnd_global.per_business_group_id and (legislation_code is null or legislation_code=( select legislation_code from per_business_groups b where b.business_group_id= fnd_global.per_business_group_id)) and classification_id in ( select classification_id from pay_element_classifications where classification_name in ('Voluntary Deductions', 'Involuntary Deductions', 'Pre-Tax Deductions', 'Employer Liabilities')) and fnd_date.canonical_to_date(:$flex$.per_dates) between pet.effective_start_date and pet.effective_end_date and pet.element_name not like '%Special Features' and pet.element_name not like '%Special Inputs' and pet.element_name not like '%Withholding' and pet.element_name not like '%Calculator' and pet.element_name not like '%Verifier' and pet.element_name not like '%Priority' and :$flex$.per_char='1' order by pet.element_name Y US Deduction Deduction Name 15 90 :p_tax_unit_id LOV Oracle HR_US_TAX_UNIT Y select txu.tax_unit_id id, txu.name value, null description from hr_tax_units_v txu where txu.business_group_id= fnd_global.per_business_group_id order by value,description US GRE Government Reporting Entity 16 100 :p_organization_id LOV Oracle HR_US_ORGANIZATIONS Y select hou.organization_id id, hou.name value, null description from hr_organization_units hou,hr_organization_information hoi where hou.business_group_id=fnd_global.per_business_group_id and hou.organization_id=hoi.organization_id and hoi.org_information_context='CLASS' and hoi.org_information1='HR_ORG' order by hou.name US Organization Organization 17 110 :p_location_id LOV Oracle HR_US_LOCATIONS Y select hl.location_id id, hl.location_code value, null description from hr_locations hl where location_use='HR' order by location_code US Location Location 18 120 :p_person_id LOV Oracle HR_US_PERSON Y Y select ppf.person_id id, ppf.full_name||' (Person ID='||ppf.person_id||')' value, null description from per_people_f ppf,per_person_types ppt where ppf.business_group_id+0= fnd_global.per_business_group_id and ppt.person_type_id=ppf.person_type_id+0 and ppt.system_person_type in ('EMP','EMP_APL','EX_EMP','EX_EMP_APL') and ppf.effective_start_date= (select max(effective_start_date) from per_people_f ppf1 where ppf1.person_id=ppf.person_id) order by nvl(ppf.order_name,ppf.full_name) US Employee Name Person 19 130 :p_sort_option1 LOV Oracle HR_SRS_SORT_ONE Y select hlu.meaning id, hlu.meaning value, null description from hr_lookups hlu where hlu.application_id=800 and hlu.lookup_type='REPORT_SELECT_SORT_CODE' and hlu.enabled_flag='Y' and (((:work_order.concurrent_program_name:null in ('PAYRPCSR','PYRPCSR','PYRPCSRUS','PYXMLCSR','PYRPCPSR') and hlu.lookup_code in ('08','12')) or (:work_order.concurrent_program_name:null in ('PAYUSW4R','PAYUSTXA') and hlu.lookup_code in ('01','02','20')) or (:work_order.concurrent_program_name:null in ('PAYUSW2C') and hlu.lookup_code in ('01','02','04','05','06','07')) or (:work_order.concurrent_program_name:null in ('PYUSYRNEG','PYUSW2RG','PYUSW2EX', 'PYUS99RG','PYUS99EX') and hlu.lookup_code in ('01','02','04','05')) or (:work_order.concurrent_program_name:null in ('PAYUSOLT') and hlu.lookup_code in ('01','02','04','05')) or (:work_order.concurrent_program_name:null in ('PAYUSUNB') and hlu.lookup_code in ('01','02','04','05')) or (:work_order.concurrent_program_name:null in ('EMP_1099R','EMP_1099R_PDF','EMP_1099RC_PDF') and hlu.lookup_code in ('01','02','07')) or (:work_order.concurrent_program_name:null in ('PAYUSW2E') and hlu.lookup_code in ('01','02','04','05','06','07')) or (:work_order.concurrent_program_name:null in ('PAYUSDED') and hlu.lookup_code in ('04','05','08')) or (:work_order.concurrent_program_name:null in ('PAYUSEW2','PYUSERW2') and hlu.lookup_code in ('01','02','04','05','06','07')) or (:work_order.concurrent_program_name:null in ('PAYRPENP','PYXMLENP') and hlu.lookup_code in ('01','04','05','08','12','16','17')) or (:work_order.concurrent_program_name:null in ('PYENPGEN') and hlu.lookup_code in ('01','04','05','12','16','17')) or (:work_order.concurrent_program_name:null in ('PAYUSPW2','PAYUSW2CPDF') and hlu.lookup_code in ('01','02','04','05','06','07'))) or ((substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYRPCSR','PYRPCPSR','PYXMLCSR') and hlu.lookup_code in ('08','12')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSW4R','PAYUSTXA') and hlu.lookup_code in ('01','02','20')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSW2C') and hlu.lookup_code in ('01','02','04','05','06','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PYUSYRNEG','PYUSW2RG','PYUSW2EX', 'PYUS99RG','PYUS99EX') and hlu.lookup_code in ('01','02','04','05')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSOLT') and hlu.lookup_code in ('01','02','04','05')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSUNB') and hlu.lookup_code in ('01','02','04','05')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('EMP_1099R','EMP_1099R_PDF','EMP_1099RC_PDF') and hlu.lookup_code in ('01','02','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSW2E') and hlu.lookup_code in ('01','02','04','05','06','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSDED') and hlu.lookup_code in ('04','05','08')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSEW2','PYUSERW2') and hlu.lookup_code in ('01','02','04','05','06','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYRPENP','PYXMLENP') and hlu.lookup_code in ('01','04','05','08','12','16','17')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PYENPGEN') and hlu.lookup_code in ('01','04','05','12','16','17')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSPW2') and hlu.lookup_code in ('01','02','04','05','06','07'))) or (:work_order.concurrent_program_name:null in ('PAYUSW2PDF') and hlu.lookup_code in ('01','02','04','05','06','07')) ) order by hlu.meaning US Sort Option One First Sort Parameter 20 140 :p_sort_option2 LOV Oracle HR_SRS_SORT_TWO Y select hlu.meaning id, hlu.meaning value, null description from hr_lookups hlu where hlu.application_id=800 and hlu.lookup_type='REPORT_SELECT_SORT_CODE' and hlu.enabled_flag='Y' and hlu.meaning<>:$flex$.hr_srs_sort_one and hlu.meaning<> decode(:$flex$.hr_srs_sort_one, 'Social Security Number','Employee Name', 'Employee Number','Employee Name', 'Assignment Number','Employee Name', :$flex$.hr_srs_sort_one) and (((:work_order.concurrent_program_name:null in ('PAYRPCSR','PYRPCSR','PYRPCSRUS','PYXMLCSR') and hlu.lookup_code in ('08','12')) or (:work_order.concurrent_program_name:null in ('PAYUSW4R','PAYUSTXA') and hlu.lookup_code in ('01','02','20')) or (:work_order.concurrent_program_name:null in ('PAYUSW2C') and hlu.lookup_code in ('01','02','04','05','06','07')) or (:work_order.concurrent_program_name:null in ('PYUSYRNEG','PYUSW2RG','PYUSW2EX', 'PYUS99RG','PYUS99EX') and hlu.lookup_code in ('01','02','04','05')) or (:work_order.concurrent_program_name:null in ('PAYUSOLT') and hlu.lookup_code in ('01','02','04','05')) or (:work_order.concurrent_program_name:null in ('PAYUSUNB') and hlu.lookup_code in ('01','02','04','05')) or (:work_order.concurrent_program_name:null in ('EMP_1099R','EMP_1099R_PDF','EMP_1099RC_PDF') and hlu.lookup_code in ('01','02','07')) or (:work_order.concurrent_program_name:null in ('PAYUSW2E') and hlu.lookup_code in ('01','02','04','05','06','07')) or (:work_order.concurrent_program_name:null in ('PAYUSDED') and hlu.lookup_code in ('04','05','08')) or (:work_order.concurrent_program_name:null in ('PAYUSEW2','PYUSERW2') and hlu.lookup_code in ('01','02','04','05','06','07')) or (:work_order.concurrent_program_name:null in ('PAYRPENP','PYXMLENP') and hlu.lookup_code in ('01','04','05','08','12','16','17')) or (:work_order.concurrent_program_name:null in ('PYENPGEN') and hlu.lookup_code in ('01','04','05','12','16','17')) or (:work_order.concurrent_program_name:null in ('PAYUSPW2','PAYUSW2CPDF') and hlu.lookup_code in ('01','02','04','05','06','07'))) or ((substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYRPCSR','PYXMLCSR') and hlu.lookup_code in ('08','12')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSW4R','PAYUSTXA') and hlu.lookup_code in ('01','02','20')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSW2C') and hlu.lookup_code in ('01','02','04','05','06','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PYUSYRNEG','PYUSW2RG','PYUSW2EX', 'PYUS99RG','PYUS99EX') and hlu.lookup_code in ('01','02','04','05')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSOLT') and hlu.lookup_code in ('01','02','04','05')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSUNB') and hlu.lookup_code in ('01','02','04','05')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('EMP_1099R','EMP_1099R_PDF','EMP_1099RC_PDF') and hlu.lookup_code in ('01','02','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSW2E') and hlu.lookup_code in ('01','02','04','05','06','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSDED') and hlu.lookup_code in ('04','05','08')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSEW2','PYUSERW2') and hlu.lookup_code in ('01','02','04','05','06','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYRPENP','PYXMLENP') and hlu.lookup_code in ('01','04','05','08','12','16','17')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PYENPGEN') and hlu.lookup_code in ('01','04','05','12','16','17')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSPW2') and hlu.lookup_code in ('01','02','04','05','06','07'))) or (:work_order.concurrent_program_name:null in ('PAYUSW2PDF') and hlu.lookup_code in ('01','02','04','05','06','07')) ) order by hlu.meaning US Sort Option Two Second Sort Parameter 21 150 :p_sort_option3 LOV Oracle HR_SRS_SORT_THREE Y select hlu.meaning id, hlu.meaning value, null description from hr_lookups hlu where hlu.application_id=800 and hlu.lookup_type='REPORT_SELECT_SORT_CODE' and hlu.enabled_flag='Y' and hlu.meaning<>:$flex$.hr_srs_sort_one and hlu.meaning<>:$flex$.hr_srs_sort_two and hlu.meaning<> decode(:$flex$.hr_srs_sort_one, 'Social Security Number','Employee Name', 'Employee Number','Employee Name', 'Assignment Number','Employee Name', :$flex$.hr_srs_sort_one) and hlu.meaning<> decode(:$flex$.hr_srs_sort_two, 'Social Security Number','Employee Name', 'Employee Number','Employee Name', 'Assignment Number','Employee Name', :$flex$.hr_srs_sort_two) and (((:work_order.concurrent_program_name:null in ('PAYRPCSR','PYXMLCSR') and hlu.lookup_code in ('08','12')) or (:work_order.concurrent_program_name:null in ('PAYUSW4R','PAYUSTXA') and hlu.lookup_code in ('01','02','20')) or (:work_order.concurrent_program_name:null in ('PAYUSW2C') and hlu.lookup_code in ('01','02','04','05','06','07')) or (:work_order.concurrent_program_name:null in ('PYUSYRNEG','PYUSW2RG','PYUSW2EX', 'PYUS99RG','PYUS99EX') and hlu.lookup_code in ('01','02','04','05')) or (:work_order.concurrent_program_name:null in ('PAYUSOLT') and hlu.lookup_code in ('01','02','04','05')) or (:work_order.concurrent_program_name:null in ('PAYUSUNB') and hlu.lookup_code in ('01','02','04','05')) or (:work_order.concurrent_program_name:null in ('EMP_1099R','EMP_1099R_PDF','EMP_1099RC_PDF') and hlu.lookup_code in ('01','02','07')) or (:work_order.concurrent_program_name:null in ('PAYUSW2E') and hlu.lookup_code in ('01','02','04','05','06','07')) or (:work_order.concurrent_program_name:null in ('PAYUSDED') and hlu.lookup_code in ('04','05','08')) or (:work_order.concurrent_program_name:null in ('PAYUSEW2','PYUSERW2') and hlu.lookup_code in ('01','02','04','05','06','07')) or (:work_order.concurrent_program_name:null in ('PAYRPENP','PYXMLENP') and hlu.lookup_code in ('01','04','05','08','12','16','17')) or (:work_order.concurrent_program_name:null in ('PYENPGEN') and hlu.lookup_code in ('01','04','05','12','16','17')) or (:work_order.concurrent_program_name:null in ('PAYUSPW2','PAYUSW2CPDF') and hlu.lookup_code in ('01','02','04','05','06','07'))) or ((substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYRPCSR','PYXMLCSR') and hlu.lookup_code in ('08','12')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSW4R','PAYUSTXA') and hlu.lookup_code in ('01','02','20')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSW2C') and hlu.lookup_code in ('01','02','04','05','06','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PYUSYRNEG','PYUSW2RG','PYUSW2EX', 'PYUS99RG','PYUS99EX') and hlu.lookup_code in ('01','02','04','05')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSOLT') and hlu.lookup_code in ('01','02','04','05')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSUNB') and hlu.lookup_code in ('01','02','04','05')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('EMP_1099R','EMP_1099R_PDF','EMP_1099RC_PDF') and hlu.lookup_code in ('01','02','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSW2E') and hlu.lookup_code in ('01','02','04','05','06','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSDED') and hlu.lookup_code in ('04','05','08')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSEW2','PYUSERW2') and hlu.lookup_code in ('01','02','04','05','06','07')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYRPENP','PYXMLENP') and hlu.lookup_code in ('01','04','05','08','12','16','17')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PYENPGEN') and hlu.lookup_code in ('01','04','05','12','16','17')) or (substr(:set_requests.descriptive_flexfield_name:null,7) in ('PAYUSPW2') and hlu.lookup_code in ('01','02','04','05','06','07'))) or (:work_order.concurrent_program_name:null in ('PAYUSW2PDF') and hlu.lookup_code in ('01','02','04','05','06','07')) ) order by hlu.meaning US Sort Option Three Third Sort Parameter