ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F2DE3869E053B46B63588994 Select asg.*, substr(asg.E_ADDRESS,6, (instr(asg.E_ADDRESS, ' add2=')-6)) ADDRESS_LINE1, substr(asg.E_ADDRESS, instr(asg.E_ADDRESS, ' add2=')+6, (instr(asg.E_ADDRESS, ' add3=')-(instr(asg.E_ADDRESS, ' add2=')+6))) ADDRESS_LINE2, substr(asg.E_ADDRESS, instr(asg.E_ADDRESS, ' add3=')+6, (instr(asg.E_ADDRESS, ' add4=')-(instr(asg.E_ADDRESS, ' add3=')+6))) ADDRESS_LINE3, substr(asg.E_ADDRESS,instr(asg.E_ADDRESS, ' add4=')+6) ADDRESS_LINE4 from(select rownum LINE_NUM, pay_gb_p46v6_edi_bip.cp_address(paa.assignment_action_id) E_ADDRESS, paa.assignment_action_id ASSIGNMENT_ACTION_ID, 'N' CHARS_ALREADY_TESTED, nvl(peo.action_information11,' ') ASSIGNMENT_NUMBER, paa.assignment_id ASSIGNMENT_ID, nvl(p46.action_information2,' ') P46_EXPAT_STATEMENT, nvl(p46.action_information3,' ') STUDENT_LOAN_IND, to_char(to_date(peo.action_information15, 'YYYY/MM/DD HH24:MI:SS'),'YYYYMMDD') DOB, nvl(peo.action_information16,' ') HIRE_DATE, nvl(peo.action_information21,' ') TAX_CODE_USE, nvl(peo.action_information22,' ') TAX_BASIS_IN_USE, nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' ') COUNTY, nvl(upper(substr(peo.action_information6,1,35)),' ') FIRST_NAME, nvl(upper(substr(peo.action_information7,1,35)),' ') SECOND_NAME, nvl(upper(substr(peo.action_information8,1,35)),' ') LAST_NAME, nvl(peo.action_information12,' ') NI_NUMBER, nvl(addr.action_information12,' ') POSTAL_CODE, nvl(substr(peo.action_information14,1,4),' ') TITLE, nvl(upper(addr.action_information8),' ') TOWN_OR_CITY, nvl(peo.action_information17,' ') GENDER, nvl(peo.action_information18,' ') JOB_TITLE, fnd_date.date_to_canonical(pay.effective_date) EFFECTIVE_DATE, nvl(p46.action_information4,' ') EEA, to_char(to_date(p46.action_information5, 'YYYY/MM/DD HH24:MI:SS'), 'YYYYMMDD') P46_EXPAT_START_EMPL_DATE, nvl(p46.action_information6,' ') EPM6_SCHEME 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 P46EXP EDI' and p46.action_context_type = 'AAP') asg Y PAY GB_P46EXP_V6_ETO US PAY P46EXP EDI Process v6 Output Application: Payroll Source: P46EXP EDI Process v6 Output Short Name: GB_P46EXP_V6_ETO DB package: PAY_GB_P46EXP_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