PAY Electronic Tax File

			SELECT 
				outr.*,
				PAY_ZA_ELE_TAX_FILE.EMPLOYEE_CONT_CTRL_TOTAL
				(
					EMP_EMAIL            	=>		outr.EMP_EMAIL,
					HOME_PHONE           	=>		outr.HOME_PHONE,
					BUS_PHONE            	=>		outr.BUS_PHONE,
					FAX_NUMBER           	=>		outr.FAX_NUMBER,
					CELL_NUMBER          	=>		outr.CELL_NUMBER,
					BUS_UNIT_NUMBER      	=>		outr.BUS_UNIT_NUMBER,
					BUS_COMPLEX          	=>		outr.BUS_COMPLEX,
					BUS_STREET_NUM       	=>		outr.BUS_STREET_NUM,
					BUS_FORM             	=>		outr.BUS_FORM,
					BUS_SUBURB           	=>		outr.BUS_SUBURB,
					BUS_CITY             	=>		outr.BUS_CITY,
					BUS_POSTAL_CODE      	=>		outr.BUS_POSTAL_CODE,
					BUS_COUNTRY_CODE     	=>		outr.BUS_COUNTRY_CODE_17,
					RES_UNIT_NUMBER      	=>		outr.RES_UNIT_NUMBER,
					RES_COMPLEX          	=>		outr.RES_COMPLEX,
					RES_STREET_NUM       	=>		outr.RES_STREET_NUM,
					RES_FORM             	=>		outr.RES_FORM,
					RES_SUBURB           	=>		outr.RES_SUBURB,
					RES_CITY             	=>		outr.RES_CITY,
					RES_POSTAL_CODE      	=>		outr.RES_POSTAL_CODE,
					RES_COUNTRY_CODE     	=>		outr.RES_COUNTRY_CODE_17,
					POS_RES_ADD_SAME     	=>		outr.POS_RES_ADD_SAME_16,
					POS_STR_ADD          	=>		outr.POS_STR_ADD_16,
					POS_POB_ADD          	=>		outr.POS_POB_ADD_16,
					POS_PB_ADD           	=>		outr.POS_PB_ADD_16,
					POS_CO_ADD           	=>		NVL(outr.POS_CO_ADD_16,outr.POS_CO_ADD_17),
					POS_CARE_INTER_ADD   	=>		outr.POS_CARE_INTER_ADD_17,
					POS_LINE1            	=>		NVL(outr.POS_LINE1,outr.POS_LINE1_17),
					POS_LINE2            	=>		NVL(outr.POS_LINE2,outr.POS_LINE2_17),
					POS_LINE3            	=>		NVL(outr.POS_LINE3,outr.POS_LINE3_17),
					POS_LINE4            	=>		outr.POS_LINE4_17,
					POS_CODE             	=>		NVL(outr.POS_CODE,outr.POS_CODE_17),
					POS_COUN_CODE        	=>		NVL(outr.POS_COUN_CODE,outr.POS_COUN_CODE_17),
					PSA_UN            		=>		outr.PSA_UN,
					PSA_CO            		=>		outr.PSA_CO,
					PSA_SN            		=>		outr.PSA_SN,
					PSA_SNF           		=>		outr.PSA_SNF,
					PSA_SD            		=>		outr.PSA_SD,
					PSA_CT           	 	=>		outr.PSA_CT,
					PSA_PC            		=>		outr.PSA_PC,
					PSA_CC            		=>		outr.PSA_CC_17,
					P_3288            		=>		outr.P_3288_17,
					P_3249            		=>		outr.P_3249_17,
					P_3280            		=>		NVL(outr.P_3280_16,outr.P_3280_17),
					P_3251            		=>		outr.P_3251,
					P_3262            		=>		NVL(outr.P_3262_16,outr.P_3262_17),
					P_3253            		=>		outr.P_3253,
					P_3254            		=>		outr.P_3254,
					P_3286            		=>		outr.P_3286_17,
					EMP_SIC7_CODE        	=>		outr.EMP_SIC7_CODE,
					EMP_SEZ_CODE         	=>		outr.EMP_SEZ_CODE
				)
			FROM
				(
					SELECT 
						e.EMP_EMAIL																																EMP_EMAIL,
						e.HOME_PHONE																															HOME_PHONE,
						e.BUS_PHONE																																BUS_PHONE,
						e.FAX_NUMBER																															FAX_NUMBER,
						e.CELL_NUMBER																															CELL_NUMBER,
						e.BUS_UNIT_NUMBER																														BUS_UNIT_NUMBER,
						e.BUS_COMPLEX																															BUS_COMPLEX,
						e.BUS_STREET_NUM																														BUS_STREET_NUM,
						e.BUS_FORM																																BUS_FORM,
						e.BUS_SUBURB																															BUS_SUBURB,
						e.BUS_CITY																																BUS_CITY,
						e.BUS_POSTAL_CODE																														BUS_POSTAL_CODE,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.BUS_COUNTRY_CODE) 															BUS_COUNTRY_CODE_17,
						e.RES_UNIT_NUMBER																														RES_UNIT_NUMBER,
						e.RES_COMPLEX																															RES_COMPLEX,
						e.RES_STREET_NUM																														RES_STREET_NUM,
						e.RES_FORM																																RES_FORM,
						e.RES_SUBURB																															RES_SUBURB,
						e.RES_CITY																																RES_CITY,
						e.RES_POSTAL_CODE																														RES_POSTAL_CODE,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.RES_COUNTRY_CODE) 															RES_COUNTRY_CODE_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,e.POS_RES_ADD_SAME,NULL) 															POS_RES_ADD_SAME_16,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2014),-1,NULL,e.POS_STR_ADD),NULL) 	POS_STR_ADD_16,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2014),-1,NULL,e.POS_POB_ADD),NULL) 	POS_POB_ADD_16,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2014),-1,NULL,e.POS_PB_ADD),NULL) 	POS_PB_ADD_16,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2014),-1,NULL,e.POS_CO_ADD),NULL) 	POS_CO_ADD_16,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.POS_CO_ADD) 																POS_CO_ADD_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.POS_CARE_INTER_ADD) 														POS_CARE_INTER_ADD_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.POS_LINE1) 																POS_LINE1_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.POS_LINE2) 																POS_LINE2_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.POS_LINE3) 																POS_LINE3_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.POS_LINE4) 																POS_LINE4_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.POS_CODE) 																	POS_CODE_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.POS_COUN_CODE) 															POS_COUN_CODE_17,
						e.POS_LINE1																																POS_LINE1,
						e.POS_LINE2																																POS_LINE2,
						e.POS_LINE3 																															POS_LINE3,
						e.POS_CODE 																																POS_CODE,
						e.POS_COUN_CODE 																														POS_COUN_CODE,
						e.PSA_UN 																																PSA_UN,
						e.PSA_CO 																																PSA_CO,
						e.PSA_SN 																																PSA_SN,
						e.PSA_SNF 																																PSA_SNF,
						e.PSA_SD 																																PSA_SD,
						e.PSA_CT 																																PSA_CT,
						e.PSA_PC 																																PSA_PC,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.PSA_CC) 																	PSA_CC_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.P_3288) 																	P_3288_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.P_3249) 																	P_3249_17,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2014),-1,NULL,e.P_3280),NULL) 		P_3280_16,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.P_3280) 																	P_3280_17,
						e.P_3251 																																P_3251,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2014),-1,NULL,e.P_3262),NULL) 		P_3262_16,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.P_3262) 																	P_3262_17,
						e.P_3253 																																P_3253,
						e.P_3254 																																P_3254,
						DECODE(SIGN(TO_NUMBER(:YEAR_OF_ASSESSMENT) - 2017),-1,NULL,e.P_3286) 																	P_3286_17,
						e.EMP_SIC7_CODE 																														EMP_SIC7_CODE,
						e.EMP_SEZ_CODE																															EMP_SEZ_CODE
					FROM 
					(
						select 
							pai.action_information2  EMP_EMAIL,
							pai.action_information3  HOME_PHONE,
							pai.action_information4  BUS_PHONE,
							pai.action_information5  FAX_NUMBER,
							pai.action_information6  CELL_NUMBER,
							pai.action_information7  BUS_UNIT_NUMBER,
							pai.action_information8  BUS_COMPLEX,
							pai.action_information9  BUS_STREET_NUM,
							pai.action_information10 BUS_FORM,
							pai.action_information11 BUS_SUBURB,
							pai.action_information12 BUS_CITY,
							trim(substr(pai.action_information13,1,10)) BUS_POSTAL_CODE,
							substr(pai.action_information13,11) BUS_COUNTRY_CODE,
							pai.action_information14 RES_UNIT_NUMBER,
							pai.action_information15 RES_COMPLEX,
							pai.action_information16 RES_STREET_NUM,
							pai.action_information17 RES_FORM,
							pai.action_information18 RES_SUBURB,
							pai.action_information19 RES_CITY,
							trim(substr(pai.action_information20,1,10)) RES_POSTAL_CODE,
							substr(pai.action_information20,11) RES_COUNTRY_CODE,
							decode(substr(pai.action_information21,1,1),'X','X',null) POS_RES_ADD_SAME,
							decode(nvl(substr(pai.action_information21,1,1),'X'),'X',null,'2','Y','N')  POS_STR_ADD,
							decode(substr(pai.action_information21,1,1),'3','X',null) POS_POB_ADD,
							decode(substr(pai.action_information21,1,1),'4','X',null) POS_PB_ADD,
							decode(substr(pai.action_information21,2,1),'Y','Y','N') POS_CO_ADD,
							substr(pai.action_information21,3) POS_CARE_INTER_ADD,
							decode(nvl(substr(pai.action_information21,1,1),'1'),'1',pai.action_information22,null) POS_LINE1,
							decode(nvl(substr(pai.action_information21,1,1),'1'),'1',pai.action_information23,null) POS_LINE2,
							decode(nvl(substr(pai.action_information21,1,1),'1'),'1',trim(substr(pai.action_information24,1,35)),null) POS_LINE3,
							decode(nvl(substr(pai.action_information21,1,1),'1'),'1',trim(substr(pai.action_information24,36)),null) POS_LINE4,
							decode(nvl(substr(pai.action_information21,1,1),'1'),'1',trim(substr(pai.action_information25,1,10)),null) POS_CODE,
							decode(nvl(substr(pai.action_information21,1,1),'1'),'1',trim(substr(pai.action_information25,11)),null) POS_COUN_CODE,
							decode(substr(pai.action_information21,1,1),'2',trim(substr(pai.action_information22,14,8)),  null) PSA_UN,
							decode(substr(pai.action_information21,1,1),'2',trim(substr(pai.action_information22,33,26)), null) PSA_CO,
							decode(substr(pai.action_information21,1,1),'2',trim(substr(pai.action_information23,16,8)),  null) PSA_SN,
							decode(substr(pai.action_information21,1,1),'2',trim(substr(pai.action_information23,50,26)), null) PSA_SNF,
							decode(substr(pai.action_information21,1,1),'2',trim(substr(pai.action_information24,21,33)), null) PSA_SD,
							decode(substr(pai.action_information21,1,1),'2',trim(substr(pai.action_information24,70,21)), null) PSA_CT,
							decode(substr(pai.action_information21,1,1),'2',trim(substr(pai.action_information25,1,10)),null)         PSA_PC,
							decode(substr(pai.action_information21,1,1),'2',substr(pai.action_information25,11),null)           PSA_CC,
							decode(substr(pai.action_information21,1,1),'X','1','1','4','2','3','2') P_3288,
							decode(substr(pai.action_information21,1,1),'3','PO_BOX','4','PRIVATE_BAG',null) P_3249,
							decode(substr(pai.action_information21,1,1),'5',trim(substr(pai.action_information22,27,21)), null)  P_3280,
							decode(substr(pai.action_information21,1,1),'3',trim(substr(pai.action_information22,28,21)),'4',trim(substr(pai.action_information22,28,21)),'5',trim(substr(pai.action_information22,77,21)), null) P_3251,
							decode(substr(pai.action_information21,1,1),'3',trim(substr(pai.action_information23,16,8)),'4',trim(substr(pai.action_information23,21,8)),'5',trim(substr(pai.action_information23,25,8)),null) P_3262,
							decode(substr(pai.action_information21,1,1),'3',trim(substr(pai.action_information24,26,22)),'4',trim(substr(pai.action_information24,26,22)),'5',trim(substr(pai.action_information24,26,22)),null) P_3253,
							decode(substr(pai.action_information21,1,1),'3',trim(substr(pai.action_information25,1,10)),'4',trim(substr(pai.action_information25,1,10)),'5',trim(substr(pai.action_information25,1,10)),null) P_3254,
							decode(substr(pai.action_information21,1,1),'3',substr(pai.action_information25,11),'4',substr(pai.action_information25,11),'5',substr(pai.action_information25,11),null) P_3286,
							pai.action_information28 EMP_SIC7_CODE,
							pai.action_information29 EMP_SEZ_CODE
						from 
							pay_action_information pai	
						where 
							pai.action_context_id = :ACTION_CONTEXT_ID
							and pai.action_context_type = 'AAP'
							and pai.action_information_category = 'ZATYE_EMPLOYEE_CONTACT_INFO'
							and pai.action_information30 = :TEMP_CERTIFICATE_NUMBER 
					) e 
				) outr