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 |
