ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4715551A9E05362FB09050512Y SELECT X_PAYEE_PAYROLL_ID, asg_act_id, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and INSTR(:EXPT_CODE_E,'ADD') <> 0 THEN X_PAYEE_ADDRESS_1 ELSE NULL END AS X_PAYEE_ADDRESS_1, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and INSTR(:EXPT_CODE_E,'ADD') <> 0 THEN X_PAYEE_ADDRESS_2 ELSE NULL END AS X_PAYEE_ADDRESS_2, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and INSTR(:EXPT_CODE_E,'ADD') <> 0 THEN X_PAYEE_ADDR_STATE ELSE NULL END AS X_PAYEE_ADDR_STATE, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and INSTR(:EXPT_CODE_E,'ADD') <> 0 THEN X_PAYEE_ADDR_PCODE ELSE NULL END AS X_PAYEE_ADDR_PCODE, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and INSTR(:EXPT_CODE_E,'ADD') <> 0 THEN X_PAYEE_ADDR_SUBURB ELSE NULL END AS X_PAYEE_ADDR_SUBURB, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and INSTR(:EXPT_CODE_E,'ADD') <> 0 THEN X_PAYEE_ADDR_COUNTRY ELSE NULL END AS X_PAYEE_ADDR_COUNTRY, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and :EXPT_CODE_E = 'TFN' THEN X_PAYEE_TFN ELSE NULL END AS X_PAYEE_TFN, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and :EXPT_CODE_E = 'EMAIL' THEN X_PAYEE_EMAIL_ADDR ELSE NULL END AS X_PAYEE_EMAIL_ADDR, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and :EXPT_CODE_E = 'TFN_FUTURE_DATE' THEN fnd_date.date_to_displaydate(to_date(X_TFN_DECL_DATE,'YYYY-MM-DD')) ELSE NULL END AS X_TFN_DECL_DATE, X_PAYEE_FIRST_NAME, X_PAYEE_OTHER_NAME, X_PAYEE_FAMILY_NAME, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and :EXPT_CODE_E = 'INV_FNAME' THEN PAY_AU_STP_ARCHIVE.RETURN_INVALID_CHARS(X_PAYEE_FIRST_NAME,'1') ELSE NULL END AS X_FIRST_INVALID_CHAR, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and :EXPT_CODE_E = 'INV_MNAME' THEN PAY_AU_STP_ARCHIVE.RETURN_INVALID_CHARS(X_PAYEE_OTHER_NAME,'1') ELSE NULL END AS X_MIDDLE_INVALID_CHAR, CASE WHEN :P_DISPLAY_PAYEE_EXCEPTION='YD' and :EXPT_CODE_E = 'INV_LNAME' THEN PAY_AU_STP_ARCHIVE.RETURN_INVALID_CHARS(X_PAYEE_FAMILY_NAME,'1') ELSE NULL END AS X_LAST_INVALID_CHAR FROM ( SELECT pay_au_stp_archive.get_archive_value('X_STP_PAYEE_PAYROLL_ID', pac.assignment_action_id) x_payee_payroll_id ,pac.assignment_action_id asg_act_id ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_FAMILY_NAME', pac.assignment_action_id) x_payee_family_name ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_FIRST_NAME', pac.assignment_action_id) x_payee_first_name ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_OTHER_NAME', pac.assignment_action_id) x_payee_other_name ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_TFN', pac.assignment_action_id) x_payee_tfn ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_ADDRESS_1', pac.assignment_action_id) x_payee_address_1 ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_ADDRESS_2', pac.assignment_action_id) x_payee_address_2 ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_ADDR_STATE', pac.assignment_action_id) x_payee_addr_state ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_ADDR_PCODE', pac.assignment_action_id) x_payee_addr_pcode ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_ADDR_SUBURB', pac.assignment_action_id) x_payee_addr_suburb ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_ADDR_COUNTRY', pac.assignment_action_id) x_payee_addr_country ,pay_au_stp_archive.get_archive_value('X_STP_TFN_DECL_DATE', pac.assignment_action_id) x_tfn_decl_date ,pay_au_stp_archive.get_archive_value('X_STP_PAYEE_EMAIL_ADDR', pac.assignment_action_id) x_payee_email_addr FROM pay_assignment_actions pac WHERE pac.payroll_action_id = :P_PAYROLL_ACTION_ID AND pay_au_stp_archive.get_archive_value('X_STP_BAL_ZERO_FLAG', pac.assignment_action_id) <> 'YES' AND pay_au_stp_archive.get_archive_value('X_STP_NEGATIVE_YTD_FLAG', pac.assignment_action_id) = 'NO' AND :EXPT_CODE_E IN ( SELECT /*+ RESULT_CACHE */ regexp_substr (pay_au_stp_archive.get_archive_value('X_STP_EXCEPTION_LIST', pac.assignment_action_id),'[^,]+',1,level) asdf FROM DUAL CONNECT BY regexp_substr (pay_au_stp_archive.get_archive_value('X_STP_EXCEPTION_LIST', pac.assignment_action_id),'[^,]+',1,level) IS NOT NULL ) ) PAY PYAUSTPARC_XML US PAY STP Archive Exception Imported from BI Publisher Description: Australian Single Touch Payroll Exception report Application: Payroll Source: STP Archive Exception Report Short Name: PYAUSTPARC_XML DB package: BI Publisher :expt_code_e :p_display_payee_exception :p_payroll_action_id 1 -10 :scalableflag Char Y US ScalableFlag 2 10 :p_payroll_action_id LOV Oracle PY_AU_EXPT_ARCHIVE_ID Y select ppa.payroll_action_id id, ppa.request_id value, 'Archive ID: '||ppa.payroll_action_id description from pay_payroll_actions ppa,hr_organization_units hou,pay_payrolls_f ppf,per_time_periods ptp where pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa.legislative_parameters)=hou.organization_id and ppa.report_type='AU_STP_ARCHIVE' and ppa.report_qualifier='AU' and ppa.action_type='X' and ppa.action_status='C' and decode(pay_core_utils.get_parameter('PAYROLL',ppa.legislative_parameters),null,-999,pay_core_utils.get_parameter('PAYROLL',ppa.legislative_parameters))=ppf.payroll_id (+) and ptp.payroll_id (+)=decode(pay_core_utils.get_parameter('PAYROLL',ppa.legislative_parameters),null,-999,pay_core_utils.get_parameter('PAYROLL',ppa.legislative_parameters)) and ptp.end_date (+)=to_date(nvl(pay_core_utils.get_parameter('PERIOD',ppa.legislative_parameters),'1900/01/01 00:00:00'),'YYYY/MM/DD HH24:MI:SS') order by ppa.request_id desc Y US STP Archive Request ID Archive ID 3 20 :p_display_payer_exception LOV Oracle HR_AU_YES_NO Y select lookup_code id, meaning value, null description from fnd_lookup_values_vl where view_application_id=0 and lookup_type='YES_NO' and enabled_flag='Y' order by meaning Y US Payer Exceptions Display Payer Exception Section 4 30 :p_display_payee_exception LOV Oracle PY_AU_STP_EXPT_YN Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='AU_STP_EXPT_YN' and enabled_flag='Y' order by meaning YS US Payee Exceptions Report Type Detail/Summary 5 40 :p_payee_expt_group LOV Oracle PY_AU_STP_EXPT_GROUP Y select hrl.lookup_code id, hrl.meaning value, null description from hr_lookups hrl where hrl.lookup_type='AU_STP_EXPT_GROUP' and enabled_flag='Y' order by meaning A US Payee Exceptions Grouping Group Payee Exceptions by 6 50 :p_display_zero_bal LOV Oracle HR_AU_YES_NO Y select lookup_code id, meaning value, null description from fnd_lookup_values_vl where view_application_id=0 and lookup_type='YES_NO' and enabled_flag='Y' order by meaning Y US Zero Balance Assignments Display Zero Balance Assignments 7 60 :p_display_lump_sum_d LOV Oracle HR_AU_YES_NO Y select lookup_code id, meaning value, null description from fnd_lookup_values_vl where view_application_id=0 and lookup_type='YES_NO' and enabled_flag='Y' order by meaning Y US Lump Sum D Assignments Display Lump Sum D Balance Assignments 8 70 :p_display_neg_ytd LOV Oracle HR_AU_YES_NO Y select lookup_code id, meaning value, null description from fnd_lookup_values_vl where view_application_id=0 and lookup_type='YES_NO' and enabled_flag='Y' order by meaning Y US Negative YTD Assignments Display Negative YTD Balances 9 80 :p_display_payee_exemption LOV Oracle HR_AU_YES_NO Y select lookup_code id, meaning value, null description from fnd_lookup_values_vl where view_application_id=0 and lookup_type='YES_NO' and enabled_flag='Y' order by meaning Y US Exempted Assignments Display Payee Exemption Section 10 90 :p_display_exception_tfn LOV Oracle HR_AU_YES_NO Y select lookup_code id, meaning value, null description from fnd_lookup_values_vl where view_application_id=0 and lookup_type='YES_NO' and enabled_flag='Y' order by meaning Y US Exception TFN Assignments Display Exception TFN Assignments