PAY STP Archive Exception

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:

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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