ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F2DF3869E053B46B63588994Y Select asg.*, substr(asg.E_ADDRESS,6, (instr(asg.E_ADDRESS, ' add2=')-6)) E_ADDRESS1, substr(asg.E_ADDRESS, instr(asg.E_ADDRESS, ' add2=')+6, (instr(asg.E_ADDRESS, ' add3=')-(instr(asg.E_ADDRESS, ' add2=')+6))) E_ADDRESS2, substr(asg.E_ADDRESS, instr(asg.E_ADDRESS, ' add3=')+6, (instr(asg.E_ADDRESS, ' add4=')-(instr(asg.E_ADDRESS, ' add3=')+6))) E_ADDRESS3, substr(asg.E_ADDRESS,instr(asg.E_ADDRESS, ' add4=')+6) E_ADDRESS4 From(select rownum LINE_NUM, pay_gb_p46v6_edi_bip.cp_address(paa.assignment_action_id) E_ADDRESS, nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' ') COUNTY, nvl(upper(substr(ltrim(peo.action_information8),1,35)),' ') LAST_NAME, nvl(upper(substr(trim(peo.action_information6),1,35)),' ') FIRST_NAME, nvl(upper(substr(trim(peo.action_information7),1,35)),' ') MIDDLE_NAME, nvl(substr(trim(peo.action_information14),1,4),' ') TITLE, nvl(addr.action_information12,' ') POSTAL_CODE, nvl(peo.action_information12, '0') NI_NUMBER, nvl(peo.action_information11,'0') ASSIGNMENT_NUMBER, nvl(p46.action_information3,'0') P46_STMT_STU_LOAN, nvl(peo.action_information17,' ') SEX, nvl(p46.action_information4,' ') DEFAULT_P46, nvl(p46.action_information2,' ') P46_STATEMENT, to_char(to_date(nvl(peo.action_information16,' '), 'YYYY/MM/DD HH24:MI:SS'), 'YYYYMMDD') HIRE_DATE, to_char(to_date(nvl(peo.action_information15,' '), 'YYYY/MM/DD HH24:MI:SS'),'YYYYMMDD') DATE_OF_BIRTH, nvl(peo.action_information18,'0') JOB_TITLE, ltrim(nvl(peo.action_information21,' '),'S') TAX_CODE_IN_USE, nvl(peo.action_information22,' ') TAX_BASIS_IN_USE, substr(nvl(peo.action_information21,' '),1,1) SVR_INDICATOR, paa.assignment_action_id ASSIGNMENT_ACTION_ID from pay_payroll_actions pay, pay_assignment_actions paa, pay_action_information addr, pay_action_information peo, pay_action_information p46 where pay.payroll_action_id = :P_PAYROLL_ACTION_ID and pay.payroll_action_id = paa.payroll_action_id and paa.assignment_action_id = peo.action_context_id and peo.action_information_category = 'GB EMPLOYEE DETAILS' and peo.action_context_type = 'AAP' and paa.assignment_action_id = addr.action_context_id and addr.action_information_category = 'ADDRESS DETAILS' and addr.action_context_type = 'AAP' and paa.assignment_action_id = p46.action_context_id and p46.action_information_category = 'GB P46_5 EDI' and p46.action_context_type = 'AAP') asg PAY GB_P46_V6_ETO US PAY P46 EDI Process v6 Output Application: Payroll Source: P46 EDI Process v6 Output Short Name: GB_P46_V6_ETO DB package: PAY_GB_P46V6_EDI_BIP BI Publisher :g_address1 :g_address2 :g_address3 :g_address4 :p_payroll_action_id 1 10 :p_payroll_action_id Char Y US Payroll Action ID 2 20 :debugflag Char N US DebugFlag