[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ ORDERED */ pee.assignment_id
,decode(eev0.screen_entry_value,'YS','Y','YI','Y','YC','Y','NN','N','YN','Y','Y','Y','N','N',Null)
, eev1.SCREEN_ENTRY_VALUE
, DECODE( eev3.screen_entry_value,
'N', 'N',
'Y', 'Y',
'NF','N',
'NP','N',
'NC','N',
'YF','Y',
'YP','Y',
'YC','Y',
'N'
)
, DECODE( eev3.screen_entry_value,
'Y', 'X',
'N', 'X',
'NF','F',
'NP','P',
'NC','C',
'YF','F',
'YP','P',
'YC','C',
'X')
, decode(eev5.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N',Null) hecs_flag
, decode(eev5.SCREEN_ENTRY_VALUE,'YY','Y','NY','Y','N') SFSS_ENTRY_VALUE
, to_char(fnd_date.canonical_to_date(eev6.SCREEN_ENTRY_VALUE),'ddmmyyyy')
, decode(decode(eev8.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N','N'),'Y','Y',decode(eev4.SCREEN_ENTRY_VALUE,'Y','Y','N'))
, eev13.SCREEN_ENTRY_VALUE
, pee.effective_start_date
, decode(sign(nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) - c_report_end_date),
1,null,pps.actual_termination_date) actual_termination_date
, decode(eev0.screen_entry_value,'YS','Y','YI','Y','YC','Y','NN','N','YN','N','Y','N','N','N',Null) /*bug7270073*/
FROM per_people_f pap /* Bug 4925794 */
, per_all_assignments_f paa,/*Bug 3012794*/
hr_soft_coding_keyflex hsc,
per_periods_of_service pps
, pay_element_entries_f pee
, pay_element_types_f pet
, pay_input_values_f piv0
, pay_element_entry_values_f eev0
, pay_input_values_f piv1
, pay_element_entry_values_f eev1
, pay_input_values_f piv3
, pay_element_entry_values_f eev3
, pay_input_values_f piv4
, pay_element_entry_values_f eev4
, pay_input_values_f piv5
, pay_element_entry_values_f eev5
, pay_input_values_f piv6
, pay_element_entry_values_f eev6
, pay_input_values_f piv8
, pay_element_entry_values_f eev8
, pay_input_values_f piv13
, pay_element_entry_values_f eev13
, hr_lookups hrl0
, hr_lookups hrl1
, hr_lookups hrl3
, hr_lookups hrl4
, hr_lookups hrl5
, hr_lookups hrl8
WHERE pap.business_group_id=c_business_group_id
and paa.business_group_id = pap.business_group_id
and pap.person_id=paa.person_id
and pps.person_id=paa.person_id
AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 = c_legal_employer_id
AND pee.assignment_id = paa.assignment_id
AND pps.person_id = paa.person_id
AND pps.date_start= (select max(pps1.date_start)
from per_periods_of_service pps1
where pps1.person_id=pps.person_id
AND pps1.date_start <= c_report_end_date
) /*Bug2751008*/
AND ( pee.entry_information_category = 'AU_TAX DEDUCTIONS' and
(trunc(fnd_date.canonical_to_date(pee.entry_information1)) BETWEEN c_report_end_date - 13 AND c_report_end_date
OR nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) BETWEEN c_report_end_date - 13 AND c_report_end_date
)
) /* Bug#5864230 */
AND paa.effective_start_date = (SELECT max(effective_start_date)
FROM per_assignments_f a
WHERE a.assignment_id = paa.assignment_id)
and pap.effective_start_date=(select max(effective_start_date)
from per_people_f p
where p.person_id=pap.person_id) --Bug 4925794
AND pee.effective_start_date =
(SELECT max(pee1.effective_start_date)
FROM pay_element_types_f pet1
,pay_element_links_f pel1
,pay_element_entries_f pee1
WHERE pet1.element_name = 'Tax Information'
AND pet1.element_type_id = pel1.element_type_id
AND pel1.element_link_id = pee1.element_link_id
AND pee1.assignment_id = paa.assignment_id
AND pee1.entry_information1 is not null /*Bug 5356467*/
AND pee1.effective_start_date <= c_report_end_date
AND pel1.effective_start_date BETWEEN pet1.effective_start_date
AND pet1.effective_end_date
)
and pet.ELEMENT_NAME= 'Tax Information'
and pet.ELEMENT_TYPE_ID = piv0.ELEMENT_TYPE_ID
and eev0.INPUT_VALUE_ID = piv0.INPUT_VALUE_ID
and eev0.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
and (piv0.NAME) = 'Australian Resident'
and hrl0.lookup_type (+) = 'AU_AUST_RES_SENR_AUS'
and hrl0.lookup_code (+) = eev0.SCREEN_ENTRY_VALUE
and hrl0.enabled_flag (+)= 'Y'
and eev1.INPUT_VALUE_ID = piv1.INPUT_VALUE_ID
and eev1.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
and piv1.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
and (piv1.NAME) = 'Tax Free Threshold'
and hrl1.lookup_type (+) = 'YES_NO'
and hrl1.lookup_code (+) = eev1.SCREEN_ENTRY_VALUE
and hrl1.enabled_flag (+)= 'Y'
and eev3.INPUT_VALUE_ID = piv3.INPUT_VALUE_ID
and piv3.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
and eev3.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
and (piv3.NAME) = 'FTA Claim'
and hrl3.lookup_type (+) = 'HR_AU_FTA_PAYMENT_BASIS'
and hrl3.lookup_code (+) = eev3.SCREEN_ENTRY_VALUE
and hrl3.enabled_flag (+) = 'Y'
and eev4.INPUT_VALUE_ID = piv4.INPUT_VALUE_ID
and piv4.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
and eev4.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
and (piv4.NAME) = 'Savings Rebate'
and hrl4.lookup_type(+) = 'YES_NO'
and hrl4.lookup_code(+) = eev4.SCREEN_ENTRY_VALUE
and hrl4.enabled_flag (+) = 'Y'
and eev5.INPUT_VALUE_ID = piv5.INPUT_VALUE_ID
and piv5.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
and eev5.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
and (piv5.NAME) = 'HECS'
and hrl5.lookup_type(+) = 'AU_HECS_SFSS'
and hrl5.lookup_code (+) = eev5.SCREEN_ENTRY_VALUE
and hrl5.enabled_flag (+) = 'Y'
and eev6.INPUT_VALUE_ID = piv6.INPUT_VALUE_ID
and piv6.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
and eev6.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
and (piv6.NAME) = 'Date Declaration Signed'
and eev8.INPUT_VALUE_ID = piv8.INPUT_VALUE_ID
and piv8.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
and eev8.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
and (piv8.NAME) = 'Spouse'
and hrl8.lookup_type (+) = 'AU_SPOUSE_MLS'
and hrl8.lookup_code (+) = eev8.SCREEN_ENTRY_VALUE
and hrl8.enabled_flag (+) = 'Y'
and eev13.INPUT_VALUE_ID = piv13.INPUT_VALUE_ID
and piv13.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
and eev13.ELEMENT_ENTRY_ID= pee.ELEMENT_ENTRY_ID
and (piv13.NAME ) = 'Tax File Number'
and pee.effective_start_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE
and eev0.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
and eev1.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
and eev3.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
and eev4.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
and eev5.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
and eev6.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
and eev8.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
and eev13.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
and eev0.effective_start_date between piv0.EFFECTIVE_START_DATE and piv0.EFFECTIVE_END_DATE
and eev1.effective_start_date between piv1.EFFECTIVE_START_DATE and piv1.EFFECTIVE_END_DATE
and eev3.effective_start_date between piv3.EFFECTIVE_START_DATE and piv3.EFFECTIVE_END_DATE
and eev4.effective_start_date between piv4.EFFECTIVE_START_DATE and piv4.EFFECTIVE_END_DATE
and eev5.effective_start_date between piv5.EFFECTIVE_START_DATE and piv5.EFFECTIVE_END_DATE
and eev6.effective_start_date between piv6.EFFECTIVE_START_DATE and piv6.EFFECTIVE_END_DATE
and eev8.effective_start_date between piv8.EFFECTIVE_START_DATE and piv8.EFFECTIVE_END_DATE
and eev13.effective_start_date between piv13.EFFECTIVE_START_DATE and piv13.EFFECTIVE_END_DATE
;
SELECT pee.assignment_id
,pev.screen_entry_value tfn_for_super_flag
FROM per_assignments_f paa,
hr_soft_coding_keyflex hsc,
pay_element_entry_values_f pev,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entries_f pee,
hr_lookups hrl0
WHERE pet.element_name = 'Superannuation Guarantee Information'
AND pet.element_type_id = piv.element_type_id
AND pev.input_value_id = piv.input_value_id
AND pev.element_entry_id = pee.element_entry_id
AND piv.name = 'TFN for Superannuation'
AND paa.assignment_id = pee.assignment_id
AND paa.business_group_id = c_business_group_id
AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 = c_legal_employer_id
AND hrl0.lookup_type (+) = 'YES_NO'
AND hrl0.lookup_code (+) = pev.screen_entry_value
AND hrl0.enabled_flag (+) = 'Y'
AND pee.effective_start_date = (SELECT max(pee1.effective_start_date)
FROM pay_element_entries_f pee1
WHERE pee1.element_entry_id = pee.element_entry_id
AND pee1.effective_start_date <= c_report_end_date
)
AND paa.effective_start_date = (SELECT max(effective_start_date)
FROM per_assignments_f a
WHERE a.assignment_id = paa.assignment_id
)
AND pev.effective_start_date = (SELECT max(pev1.effective_start_date)
FROM pay_element_entry_values_f pev1
WHERE pev1.element_entry_value_id = pev.element_entry_value_id
AND pev1.effective_start_date <= c_report_end_date
)
AND c_report_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_report_end_date BETWEEN piv.effective_start_date AND piv.effective_end_date;