ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4717151A9E05362FB09050512Y 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 PAY PYZAIRPM_XML US PAY Electronic Tax File Imported from BI Publisher Application: Payroll Source: Electronic Tax File Short Name: PYZAIRPM_XML DB package: PAY_ZA_ELE_TAX_FILE BI Publisher :action_context_id :c_action_context_id :c_payroll_action_id :ctrl_total_code :ctrl_total_fomatted_value :ctrl_total_rec :ctrl_total_value :g_flag :p_business_group_id :p_certificate_type :p_legal_entity_id :p_payroll_action_id :p_period_recon :p_tax_year :p_test_run :temp_certificate_number :year_of_assessment 1 -30 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group ID Business Group ID 2 -20 :p_test_run_enable Char SELECT DECODE(:$flex$.certificate_type, '1','E',NULL) FROM DUAL US PAY_ZA_TEST_RUN_YES_NO_ENABLE 3 10 :p_certificate_type LOV Oracle ZA_TAX_CERTIFICATES Y select hl.lookup_code id, hl.meaning value, hl.description description from hr_lookups hl where hl.lookup_type='ZA_TAX_CERTIFICATES' and hl.application_id=800 and hl.enabled_flag='Y' order by value,description select meaning from HR_LOOKUPS where lookup_type='ZA_TAX_CERTIFICATES' and lookup_code='1' Y US Certificate Type Certificate Type 4 20 :p_tax_year Number Y US PAY_ZA_TAX_YEAR Tax Year 5 30 :p_period_recon LOV Oracle PAY_ZA_PERIOD_OF_RECON Y select hl.lookup_code id, hl.meaning value, null description from hr_lookups hl where lookup_type='PAY_ZA_PERIOD_OF_RECON' and enabled_flag='Y' order by value,description Y US Period of Reconciliation Period of Reconciliation 6 40 :p_legal_entity_id LOV Oracle PAY_ZA_TAX_LEGAL_ENTITY Y select haou.organization_id id, haou.name value, null description from fnd_sessions fss,hr_all_organization_units haou,hr_organization_information hoi,hr_organization_information hoi2 where fss.session_id=userenv('sessionid') and fss.effective_date between haou.date_from and nvl(haou.date_to,to_date('31-12-4712','DD-MM-YYYY')) and haou.business_group_id=fnd_global.per_business_group_id and hoi.organization_id=haou.organization_id and hoi.org_information_context='ZA_LEGAL_ENTITY' and hoi2.organization_id=haou.organization_id and hoi2.org_information_context='CLASS' and hoi2.org_information1='HR_LEGAL' and hoi2.org_information2='Y' and exists (select 1 from pay_payroll_actions ppa where ppa.business_group_id+0=fnd_global.per_business_group_id and ppa.action_type='X' and ppa.report_type='ZA_TYE' and ppa.action_status='C' and pay_za_irp5_archive_pkg.get_parameter('TAX_YEAR',ppa.legislative_parameters)=:$flex$.pay_za_tax_year and pay_za_irp5_archive_pkg.get_parameter('CERT_TYPE',ppa.legislative_parameters)=:$flex$.za_tax_certificates and pay_za_irp5_archive_pkg.get_parameter('LEGAL_ENTITY',ppa.legislative_parameters)=haou.organization_id ) order by haou.name Y US Legal Entity Legal Entity 7 50 :p_payroll_action_id LOV Oracle PAY_ZA_TAX_PREPROCESS Y select ppa.payroll_action_id id, to_char(ppa.effective_date,'DD-MON-YYYY') || '(' || to_char(ppa.payroll_action_id)||')' value, null description from pay_payroll_actions ppa where ppa.business_group_id+0=fnd_global.per_business_group_id and ppa.action_type='X' and ppa.report_type='ZA_TYE' and ppa.action_status='C' and pay_za_irp5_archive_pkg.get_parameter('TAX_YEAR',ppa.legislative_parameters)=:$flex$.pay_za_tax_year and nvl(pay_za_irp5_archive_pkg.get_parameter('PERIOD_RECON',ppa.legislative_parameters),'02')=:$flex$.pay_za_period_of_recon and pay_za_irp5_archive_pkg.get_parameter('CERT_TYPE',ppa.legislative_parameters)=:$flex$.za_tax_certificates and pay_za_irp5_archive_pkg.get_parameter('LEGAL_ENTITY',ppa.legislative_parameters)=:$flex$.pay_za_tax_legal_entity order by ppa.request_id desc US Tax Certificate Preprocess Payroll Action ID 8 60 :p_test_run LOV Oracle PAY_ZA_TEST_RUN_YES_NO Y select lookup_code id, meaning value, null description from fnd_common_lookups where lookup_type='YES_NO' and :$flex$.pay_za_test_run_yes_no_enable='E' order by value,description select meaning from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'N' Y US Test Run Test Run