VIEW: APPS.PAY_GB_TAX_CREDITS_V

Source

View Text - Preformatted

SELECT distinct ent.element_entry_id ,lnk.element_link_id ,ipv1.input_value_id DAILY_AMOUNT_IPV_ID ,eev1.screen_entry_value DAILY_AMOUNT ,ipv2.input_value_id REFERENCE_IPV_ID ,eev2.screen_entry_value REFERENCE ,ipv3.input_value_id START_DATE_IPV_ID ,eev3.screen_entry_value START_DATE ,substr(cl1.meaning,instr(cl1.meaning,'(')+1,(instr(cl1.meaning,')')-instr(cl1.meaning,'('))-1) IPV_DATE_FORMAT ,ipv4.input_value_id END_DATE_IPV_ID ,eev4.screen_entry_value END_DATE ,ipv5.input_value_id TOTAL_AMOUNT_IPV_ID ,eev5.screen_entry_value TOTAL_AMOUNT ,ipv6.input_value_id STOP_DATE_IPV_ID ,eev6.screen_entry_value STOP_DATE ,ent.assignment_id,ent.effective_start_date ,ent.effective_end_date ,ent.object_version_number FROM pay_element_entry_values_f eev1 , pay_element_entry_values_f eev2 , pay_element_entry_values_f eev3 , pay_element_entry_values_f eev4 , pay_element_entry_values_f eev5 , pay_element_entry_values_f eev6 , hr_lookups cl1 , pay_element_entries_f ent , pay_element_links_f lnk , pay_input_values_f ipv1 , pay_input_values_f ipv2 , pay_input_values_f ipv3 , pay_input_values_f ipv4 , pay_input_values_f ipv5 , pay_input_values_f ipv6 , pay_element_types_f ele , fnd_sessions ses WHERE ele.element_name='Tax Credit' and cl1.lookup_code = ipv3.uom and cl1.lookup_type = 'UNITS' and ses.session_id=userenv('SESSIONID') and ses.effective_date between ent.effective_start_date and ent.effective_end_date and lnk.element_type_id=ele.element_type_id and ent.element_link_id=lnk.element_link_id and ent.element_entry_id=eev1.element_entry_id and ent.element_entry_id=eev2.element_entry_id and ent.element_entry_id=eev3.element_entry_id and ent.element_entry_id=eev4.element_entry_id and ent.element_entry_id=eev5.element_entry_id and ent.element_entry_id=eev6.element_entry_id and eev1.input_value_id=ipv1.input_value_id and ipv1.element_type_id=ele.element_type_id and ipv1.name='Daily Amount' and eev2.input_value_id=ipv2.input_value_id and ipv2.element_type_id=ele.element_type_id and ipv2.name='Reference' and eev3.input_value_id=ipv3.input_value_id and ipv3.element_type_id=ele.element_type_id and ipv3.name='Start Date' and eev4.input_value_id=ipv4.input_value_id and ipv4.element_type_id=ele.element_type_id and ipv4.name='End Date' and eev5.input_value_id=ipv5.input_value_id and ipv5.element_type_id=ele.element_type_id and ipv5.name='Total Amount' and eev6.input_value_id=ipv6.input_value_id and ipv6.element_type_id=ele.element_type_id and ipv6.name='Stop Date' and ses.effective_date between eev1.effective_start_date and eev1.effective_end_date and ses.effective_date between eev2.effective_start_date and eev2.effective_end_date and ses.effective_date between eev3.effective_start_date and eev3.effective_end_date and ses.effective_date between eev4.effective_start_date and eev4.effective_end_date and ses.effective_date between eev5.effective_start_date and eev5.effective_end_date and ses.effective_date between eev6.effective_start_date and eev6.effective_end_date and ses.effective_date between ipv1.effective_start_date and ipv1.effective_end_date and ses.effective_date between ipv2.effective_start_date and ipv2.effective_end_date and ses.effective_date between ipv3.effective_start_date and ipv3.effective_end_date and ses.effective_date between ipv4.effective_start_date and ipv4.effective_end_date and ses.effective_date between ipv5.effective_start_date and ipv5.effective_end_date and ses.effective_date between ipv6.effective_start_date and ipv6.effective_end_date and ses.effective_date between lnk.effective_start_date and lnk.effective_end_date and ses.effective_date between ele.effective_start_date and ele.effective_end_date and not exists( select 'Y' from pay_element_entries_f ent2 where ent2.element_entry_id=ent.element_entry_id and ent2.effective_start_date=(ent.effective_end_date+1) )

View Text - HTML Formatted

SELECT DISTINCT ENT.ELEMENT_ENTRY_ID
, LNK.ELEMENT_LINK_ID
, IPV1.INPUT_VALUE_ID DAILY_AMOUNT_IPV_ID
, EEV1.SCREEN_ENTRY_VALUE DAILY_AMOUNT
, IPV2.INPUT_VALUE_ID REFERENCE_IPV_ID
, EEV2.SCREEN_ENTRY_VALUE REFERENCE
, IPV3.INPUT_VALUE_ID START_DATE_IPV_ID
, EEV3.SCREEN_ENTRY_VALUE START_DATE
, SUBSTR(CL1.MEANING
, INSTR(CL1.MEANING
, '(')+1
, (INSTR(CL1.MEANING
, ')')-INSTR(CL1.MEANING
, '('))-1) IPV_DATE_FORMAT
, IPV4.INPUT_VALUE_ID END_DATE_IPV_ID
, EEV4.SCREEN_ENTRY_VALUE END_DATE
, IPV5.INPUT_VALUE_ID TOTAL_AMOUNT_IPV_ID
, EEV5.SCREEN_ENTRY_VALUE TOTAL_AMOUNT
, IPV6.INPUT_VALUE_ID STOP_DATE_IPV_ID
, EEV6.SCREEN_ENTRY_VALUE STOP_DATE
, ENT.ASSIGNMENT_ID
, ENT.EFFECTIVE_START_DATE
, ENT.EFFECTIVE_END_DATE
, ENT.OBJECT_VERSION_NUMBER
FROM PAY_ELEMENT_ENTRY_VALUES_F EEV1
, PAY_ELEMENT_ENTRY_VALUES_F EEV2
, PAY_ELEMENT_ENTRY_VALUES_F EEV3
, PAY_ELEMENT_ENTRY_VALUES_F EEV4
, PAY_ELEMENT_ENTRY_VALUES_F EEV5
, PAY_ELEMENT_ENTRY_VALUES_F EEV6
, HR_LOOKUPS CL1
, PAY_ELEMENT_ENTRIES_F ENT
, PAY_ELEMENT_LINKS_F LNK
, PAY_INPUT_VALUES_F IPV1
, PAY_INPUT_VALUES_F IPV2
, PAY_INPUT_VALUES_F IPV3
, PAY_INPUT_VALUES_F IPV4
, PAY_INPUT_VALUES_F IPV5
, PAY_INPUT_VALUES_F IPV6
, PAY_ELEMENT_TYPES_F ELE
, FND_SESSIONS SES
WHERE ELE.ELEMENT_NAME='TAX CREDIT'
AND CL1.LOOKUP_CODE = IPV3.UOM
AND CL1.LOOKUP_TYPE = 'UNITS'
AND SES.SESSION_ID=USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN ENT.EFFECTIVE_START_DATE
AND ENT.EFFECTIVE_END_DATE
AND LNK.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND ENT.ELEMENT_LINK_ID=LNK.ELEMENT_LINK_ID
AND ENT.ELEMENT_ENTRY_ID=EEV1.ELEMENT_ENTRY_ID
AND ENT.ELEMENT_ENTRY_ID=EEV2.ELEMENT_ENTRY_ID
AND ENT.ELEMENT_ENTRY_ID=EEV3.ELEMENT_ENTRY_ID
AND ENT.ELEMENT_ENTRY_ID=EEV4.ELEMENT_ENTRY_ID
AND ENT.ELEMENT_ENTRY_ID=EEV5.ELEMENT_ENTRY_ID
AND ENT.ELEMENT_ENTRY_ID=EEV6.ELEMENT_ENTRY_ID
AND EEV1.INPUT_VALUE_ID=IPV1.INPUT_VALUE_ID
AND IPV1.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV1.NAME='DAILY AMOUNT'
AND EEV2.INPUT_VALUE_ID=IPV2.INPUT_VALUE_ID
AND IPV2.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV2.NAME='REFERENCE'
AND EEV3.INPUT_VALUE_ID=IPV3.INPUT_VALUE_ID
AND IPV3.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV3.NAME='START DATE'
AND EEV4.INPUT_VALUE_ID=IPV4.INPUT_VALUE_ID
AND IPV4.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV4.NAME='END DATE'
AND EEV5.INPUT_VALUE_ID=IPV5.INPUT_VALUE_ID
AND IPV5.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV5.NAME='TOTAL AMOUNT'
AND EEV6.INPUT_VALUE_ID=IPV6.INPUT_VALUE_ID
AND IPV6.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV6.NAME='STOP DATE'
AND SES.EFFECTIVE_DATE BETWEEN EEV1.EFFECTIVE_START_DATE
AND EEV1.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN EEV2.EFFECTIVE_START_DATE
AND EEV2.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN EEV3.EFFECTIVE_START_DATE
AND EEV3.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN EEV4.EFFECTIVE_START_DATE
AND EEV4.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN EEV5.EFFECTIVE_START_DATE
AND EEV5.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN EEV6.EFFECTIVE_START_DATE
AND EEV6.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV1.EFFECTIVE_START_DATE
AND IPV1.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV2.EFFECTIVE_START_DATE
AND IPV2.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV3.EFFECTIVE_START_DATE
AND IPV3.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV4.EFFECTIVE_START_DATE
AND IPV4.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV5.EFFECTIVE_START_DATE
AND IPV5.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV6.EFFECTIVE_START_DATE
AND IPV6.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN LNK.EFFECTIVE_START_DATE
AND LNK.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN ELE.EFFECTIVE_START_DATE
AND ELE.EFFECTIVE_END_DATE
AND NOT EXISTS( SELECT 'Y'
FROM PAY_ELEMENT_ENTRIES_F ENT2
WHERE ENT2.ELEMENT_ENTRY_ID=ENT.ELEMENT_ENTRY_ID
AND ENT2.EFFECTIVE_START_DATE=(ENT.EFFECTIVE_END_DATE+1) )