PAY STP Archive Exception

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Australian Single Touch Payroll Exception report
Application: Payroll
Source: STP Archive Exception Report
Short Name: PYAUSTPARC_XML
DB package: 
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
				)
		)
Parameter Name SQL text Validation
STP Archive Request ID 
 
 LOV Oracle
Payer Exceptions 
 
 LOV Oracle
Payee Exceptions 
 
 LOV Oracle
Payee Exceptions Grouping 
 
 LOV Oracle
Zero Balance Assignments 
 
 LOV Oracle
Lump Sum D Assignments 
 
 LOV Oracle
Negative YTD Assignments 
 
 LOV Oracle
Exempted Assignments 
 
 LOV Oracle
Exception TFN Assignments 
 
 LOV Oracle
Download
Related Blitz Reports
PAY RTI - Year End P60 Report Process (Multi Threaded) PAY Superannuation Contribution PAY RTI - Year End P60 Report Process INV Item Upload PAY IE Cheque Listing PAY RTI - Earlier Year Update Output 2014/15 PQP Teachers Pension MCR Reconciliation Report (Excel) PAY RTI ? Employer Alignment Submission Output PAY Payroll Reconciliation Detail
Blitz Report™

Blitz Report™ provides multiple benefits: