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 |