PAY P46EXP EDI Process v6 Output

			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